Поиск в экселе — как искать и применение функции с примерами

Таблицы Microsoft Office Excel содержат много текстовой и числовой информации. Среди большого количества данных сложно быстро найти нужный фрагмент текста или формулу. Редактор содержит несколько инструментов, которые позволяют ускорить эту процедуру. Сегодня рассмотрим, как работает поиск в экселе и какие есть разновидности у этой функции.

Поиск

Для начала разберемся с менее популярной функцией – поиск. Использование инструмента позволяет найти положение искомой информации в тексте, выраженное в виде числа. Помимо этого можно искать не только единичные символы, но и целые сочетания букв. Чтобы включить поиск, нужно в строке формул написать одноименную функцию, указав впереди знак равно. Синтаксис следующий:

1. Первый блок используется для записи искомой информации.
2. Вторая часть функции позволяет задать диапазон поиска по части текста.
3. Третий аргумент является необязательным. Его использование оправдано, если известна точка начала поиска внутри ячейки.

Рассмотрим пример: необходимо найти фрукты, которые начинаются на букву А из списка.

Полученные результаты можно дальше использовать для приведения к более удобному виду.

Позиции с единицами показывают, какие из строк содержат фрукты, начинающиеся на букву а. Как видите, остальные цифры также указывают на местоположение искомой буквы в остальных позициях диапазона. Однако, одна ячейка содержит ошибку ЗНАЧ!. Эта проблема возникает в двух случаях, при использовании функции ПОИСК:

В нашем случае фрукт Персик не содержит ни одной а, поэтому программа выдала ошибку.

Найти

Комбинация клавиш Ctrl+F позволяет быстро вызвать окно функции Найти. Также можно активировать инструмент через отдельную кнопку на Панели инструментов.

Расширенное диалоговое окно содержит поле для ввода искомой информации, параметры масштаба, например поиск по всем листам, а также варианты анализа информации по столбцу или строке.

Данная функция реализована и в виде формулы с одноименным названием, которая также позволяет осуществлять поиск по словам. Рассмотрим работу на примере списка фруктов. Вписываете формулу =НАЙТИ(«а»;$B$4:$B$11), применяете автозаполнение и получаете результат:

Различия

Проанализировав результаты, полученные при использовании инструментов ПОИСК и НАЙТИ, можно выделить несколько ключевых отличий:

1. Функция НАЙТИ чувствительна к регистру букв, то есть алгоритм распознает большие и маленькие символы. Это четко видно на примере фрукта Апельсин. ПОИСК вернул 1, а НАЙТИ выдало ошибку, поскольку маленькой буквы а нет в ячейке. Подобная ситуация и для Ананаса с Айвой

1. Дополнительным различием является то, что ПОИСК умеет работать с подстановочными символами типа вопросительный знак (?) и звездочка (*), которые используются для неточного формирования поискового запроса.

На заметку! Чтобы найти именно эти символы в тексте необходимо использовать тильду (~) перед аргументом.

Как видите, excel позволяет осуществлять поиск несколькими способами, каждый из которых обладает своими преимуществами. При помощи одного можно осуществить поиск по нескольким значениям, а другой можно вызвать используя горячие клавиши, что, несомненно, ускоряет процесс работы с документом.

Мнение эксперта

Знайка, самый умный эксперт в Цветочном городе

Если у вас есть вопросы, задавайте их мне!

Задать вопрос эксперту

Поэтому выбрали шкалу красный-желтый-зеленый , которая показывает относительную величину значений с помощью насыщенности оттенков. Если же вы хотите что-то уточнить, я с радостью помогу!

Как в эксель выделить повторяющиеся значения цветом

• адрес_ссылки – обязательный аргумент. Строка, представляющая адрес ссылки на ячейку или диапазон. Например, «C3», «R3C3» или «D8:D9».
• стиль_ссылки – необязательный аргумент. Логическое значение, определяющее стиль ссылки:
  • ИСТИНА – стиль A1. Является значением по умолчанию;
  • ЛОЖЬ – стиль R1C1.

  Строка в ячейке A1 содержит текст, в котором имеются 2 подстроки «старый». Нам необходимо подставить на место первого вхождения строку «новый». В результате часть текста «…старый-старый…», заменяется на «…новый-старый…».

  8 полезных функций и возможностей Excel, о которых не помнят

  

  Научитесь использовать все прикладные инструменты из функционала MS Excel.

  1. Условное форматирование

  Условное форматирование – обобщающее название для целого набора способов обработки данных. Все эти методы основаны на цветовом выделении ячеек в зависимости от разных критериев и правилах закрашивания.

  Например, сравнение с константой; ранжирование по диапазону значений с помощью гистограмм, цветовых шкал и различных значков.

  Что очень важно: условное форматирование – это динамическая функция, корректирующая результаты при каждом изменении данных.

  Существует множество комбинаций для форматирования, поэтому каждый пользователь быстро найдет подходящий способ для своей задачи из уже существующих методов или создаст свое собственное правило.

  

  Раздел «Условное форматирование» очень легко найти на главной панели быстрого доступа. Как только мы наводим курсор, сразу появляется выпадающий список правил выделения и средств форматирования (диаграммы, шкалы, значки).

  При наведении курсора на каждый вы увидите несколько готовых вариантов, которые можно выбрать. Если ни один способ вам не подходит, можно создать свой.

  Создадим произвольную таблицу, к которой будем применять форматирование:

  

  Возьмем таблицу, отражающую выручку продавцов за один месяц. Попробуем «раскрасить» ячейки.

  Выделим ячейки, которые нужно форматировать. В разделе «Условное форматирование» находим «Правила выделения ячеек» и выбираем «Повторяющиеся значения».

  

  Появится диалоговое окно, где вы сможете выбрать – выделять повторяющиеся или уникальные значения, также цвет выделения:

  

  

  Аналогичным способом откроем «Правила выделения ячеек», но на этот раз закрасим те ячейки, значения которых больше 25 000:

  

  

  Можно форматировать таблицы сразу по нескольким условиям. Выделим уже отформатированные ячейки С1:С7 и применим к ним правило: меньше 22 800 и выберем отличный от прошлого дизайн выделения.

  

  Так будет выглядеть наша таблица после применения двойного форматирования:

  

  При необходимости изменения параметров форматирования, дизайна ячеек в разделе «Условное форматирование» выбираем «Управление правилами» и в открывшемся диалоговом окне модифицируем правило так, как нам нужно:

  

  В этом же разделе можно создавать произвольные правила для конкретных случаев и удалять ненужные.

  Выберем «Создать правило». В появившемся окне выделим подходящий тип правила (у нас – форматировать ячейки, которые содержат), указываем необходимые параметры (у нас – значения между 22 000 и 25 000) и задаем формат выделений (у нас – жирный шрифт синего цвета):

  

  После всех преобразований таблица будет выглядеть следующим образом:

  

  Замечание 1. Следует аккуратно применять условное форматирование с большим числом правил к одним и тем же данным, особенно, когда много значений удовлетворяют сразу нескольким условиям.

  Ведь, как вы уже успели заметить, применяя к данным новое правило, старое тоже сохраняется. Поэтому будет не очень наглядная таблица, в которой сложно ориентироваться, а из-за нагромождения стилей, можно легко запутаться в том, какое форматирование к какому правилу относится!

  Замечание 2. Условное форматирование применимо не только к числовым данным, но и к текстовым. Для строк есть встроенное правило «Текст содержит», где указывается фраза и выделяются все ячейки, содержащие её.

  В данном разделе очень много полезных приемов для аналитиков, экономистов, бухгалтеров и представителей смежных профессий, помогающих не тратить много времени на простейшие, подготовительные этапы анализа данных.

  Например, необходимо выделить 10 лучших позиций в столбце или значения, выше среднего, или первые 10% значений, сохранив при этом исходный порядок (то есть сортировать от большего к меньшему и выделить вручную – не получится). Число 10 можно изменить на другое!

  В таком случае приходят на помощь «Правила отбора первых и последних значений»:

  

  Задаем количество первых (по величине значения) ячеек и цвет заливки:

  

  Получаем таблицу с выделенными четырьмя наибольшими значениями:

  

  2. Форматирование с помощью гистограмм

  До этого мы рассматривали форматирование с помощью простого выделения ячеек. Теперь познакомимся с более интересным способом.

  В Excel можно форматировать с помощью гистограмм. В этом случае в каждой ячейке будет отображаться линия, соответствующая ее длине относительно других ячеек.

  Чтобы это сделать, нужно в «Условном форматировании» найти «Гистограммы» и выбрать градиентную или сплошную заливку:

  

  В итоге мы получим таблицу, в которой сразу видно отношение данных друг к другу:

  

  3. Использование значков в сортировке

  Порой очень удобно использовать в качестве выделения – значки. То есть каждому знаку можно присвоить свое значение (или интервал значений) и ставить их рядом с теми данными, которые попадают в диапазон.

  В «Условном форматировании» есть разные наборы значков, которые можно использовать:

  

  Выбрав подходящий набор значков, каждому нужно присвоить правило, по которому они будут расставляться в ячейках.

  Можно ранжировать по процентным соотношениям, расчетам формул или, как у нас, по значениям: галочка, если больше 26 000; восклицательный знак, если больше 22 000, но меньше 26 000; крестик, если меньше 22 000:

  

  4. Форматирование с помощью цветовых шкал

  Последний прием, о котором мы поговорим в разделе «Условного форматирование», это использование цветовых шкал.

  Для этого выделим данные таблицы, зайдем в «Условное форматирование» → «Цветовые шкалы», где можно выбрать самые разные дизайны раскрашивания:

  

  Мы в таблице хотели сравнить максимальные и минимальные значения. Поэтому выбрали шкалу «красный-желтый-зеленый», которая показывает относительную величину значений с помощью насыщенности оттенков.

  Вот какая красивая картинка у нас получилась (максимальной температуре соответствуют темно-красные ячейки, минимальной – темно-зеленые):

  

  Согласитесь, что условное форматирование сильно сокращает время анализа данных, не прибегая к сложным методам.

  Конечно, сортировку и визуализацию данных можно сделать и в RStudio, например, с помощью библиотек dplyr и ggplot2. Но для этого требуются знания языка R, недюжинные навыки и опыт в программировании. Если хотите изучить язык R, то советуем начать с нашего открытого онлайн-курса «Аналитика с R и SQL».

  А Excel предлагает обработку данных с помощью встроенных конструкций, что делает его очень популярным.

  Рассмотрим еще несколько функций, которые смогут пригодиться в работе!

  5. Перехват ошибок

  Смоделируем еще одну ситуацию. Вы долго делали сложный расчет, создавали новые, объемные формулы, а в результат не получили, потому что вдруг возникла какая-то ошибка.

  При этом непонятно: это вы неправильно ввели формулу, отсутствуют какие-то данные или еще что-то. Понадобится много времени на то, чтобы разобраться с проблемой.

  Например, нужно было посчитать средний доход с каждого клиента при наличии суммарного дохода и количества клиентов. Это простая формула: дели одно на другое.

  

  Но возникла ошибка «Деление на 0» в ячейке D7. Из-за этого перестала работать формула СРЕДНЕЕ(D2:D10), использующая в расчетах значение этой ячейки.

  На помощь в данном случае к нам придет функция ЕСЛИОШИБКА(значение; значение_если_ошибка). Она проверяет, есть ли в формуле или ячейке ошибка, и если есть, то выводит заданное значение (0, пустую строчку – “ ”, число или другое).

  § «значение» – то, что проверяем;
  § «значение_если_ошибка» – то, что вставляем вместо ошибки.

  

  После применения формулы ко всем строкам ошибка исчезнет, и мы получим итоговый ответ.

  Замечание. Функция ЕСЛИОШИБКА() появилась лишь в MS Excel 2007. В более ранних версиях была функция ЕОШИБКА(). Они выводили только значения ИСТИНА/ЛОЖЬ и не могла обрабатывать ошибки. Поэтому её использовали в связке с формулой ЕСЛИ().

  Для нашего примера это выглядело бы так: =ЕСЛИ(ЕОШИБКА(С2/B2);0), то есть, если ошибка возникает, то выводим в ячейке «0».

  Есть еще функция ЕОШ(), которая отличается от ЕОШИБКА() только тем, что не считает #Н/Д за ошибку.

  6. Функция СЧЁТЕСЛИ(). Обязательно через Ё!

  Функция СЧЁТЕСЛИ (диапазон; критерий) считает в заданном диапазоне количество непустых ячеек, значения которых удовлетворяют введенному критерию.

  § Диапазон – область, в которой происходит поиск. Она может содержать числовые, текстовые значения, ссылки и массивы.

  § Критерий – условие, которому должны удовлетворять ячейки.

  В качестве критериев могут выступать выражения со знаками ,=, числа, текстовые строки, ссылки и так далее.

  Замечание. Обязательно критерий нужно заключать в кавычки! Или писать условие в отдельной ячейке без кавычек, а в функцию передавать ссылку на ячейку.

  На примерах рассмотрим, какие еще особенности есть у функции СЧЁТЕСЛИ().

  

  Найдем количество наименований, количество которых больше 110:

  

  Аналогичным образом можно использовать знаки «меньше» и «равно».

  Можно писать критерии со знаком “” (не равно), но тогда нужно добавлять оператор & (амперсанд), который будет объединять два условия.

  Например, найдем, сколько строк в диапазоне отличны от «столы»:

  

  Найдем, сколько раз в столбце «Наименования» встречаются «табуреты»:

  

  Если нужно найти все ячейки, содержащие, например, однокоренные слова или формы слов, нужно использовать значок «*». Если нам нужно совпадение первой части слова, тогда после нее ставится *, если важен только конец слова, то тогда «*» ставится перед сочетанием букв.

  

  

  Часто нужно считать ячейки по нескольким критериям.

  Например, посчитать «стулья» и «кровати». Для этого нужно сложить две функции СЧЁТЕСЛИ():

  

  Соответственно, сколько условий будет, столько функций СЧЁТЕСЛИ() и нужно сложить.

  Сделаем следующий запрос: каково количество ячеек, значение которых «>=20», но «

  

  Можно придумать еще много разных примеров с функцией СЧЁТЕСЛИ(), но мы пойдем дальше.

  7. Функция СЧЁТЕСЛИМН()

  Функция СЧЁТЕСЛИМН() тоже выводит число ячеек, удовлетворяющих условию, только здесь можно указывать до 127 диапазонов и условий.

  Например, найдем скольких наименований мебели, начинающихся на «с*», на складе больше 100 штук. Для этого в качестве первого диапазона выберем А2:А11, в качестве второго – В2:В11.

  

  8. Перенос формул

  Небольшое замечание по поводу переноса формул мы решили оставить здесь, в этой статье, потому что об этой полезной уловке знают не все.

  Как перенести большие формулы, которые содержат ссылки на ячейки, так, чтобы при переносе ссылки не изменялись, чтобы не пришлось менять их вручную и перепроверять правильность формул?

  Все очень просто: замените знак «=» в формуле на любой другой, например, «!». Тогда это уже будет не формула, а символьная строка, которая будет перемещаться без изменений!

  После того, как вы перенесете ее в нужную ячейку, сделайте обратную замену на «=». В этом случае вы можете быть полностью уверены в том, что формула не изменится.

  Заключение

  Существует еще очень много функций, о которых мы редко вспоминаем, но которые должны быть постоянно в нашем арсенале, например:

  • СУММЕСЛИ(), СУММЕСЛИМН() – суммируют значения ячеек, удовлетворяющих условиям;
  • ПСТР() – возвращает заданное количество знаков, начиная с указанного;
  • СЦЕПИТЬ() – объединяет несколько строк и другие. Они простые и не требуют долгого изучения.

  Попробуйте познакомиться с ними самостоятельно, а если возникнут какие-то сложности, то пишите нам в комментариях, и мы поможем вам со всем разобраться!

  

  Научитесь использовать все прикладные инструменты из функционала MS Excel.

  Excel поиск значения в диапазоне по условию

  =АДРЕС(1;1) – возвращает $A200.
  =АДРЕС(1;1;4) – возвращает A1.
  =АДРЕС(1;1;4;ЛОЖЬ) – результат R[1]C[1].
  =АДРЕС(1;1;4;ЛОЖЬ;»Лист1″) – результат выполнения функции Лист1!R[1]C[1].
  

  Мнение эксперта

  Знайка, самый умный эксперт в Цветочном городе

  Если у вас есть вопросы, задавайте их мне!

  Задать вопрос эксперту

  Ищет совпадение по ключу в первой строке определенного диапазона и возвращает значение из указанной строки этого диапазона в совпавшем с ключом столбце. Если же вы хотите что-то уточнить, я с радостью помогу!
  Когда у вас открыто несколько книг в Excel, между ними можно легко перемещаться с помощью комбинации клавиш Ctrl + Tab. Эта функция также доступна по всей системе Windows, и ее можно использовать во многих приложениях. К примеру, для переключения вкладок в браузере.
  

  Текстовые функции Excel

  • текст – строка либо ссылка на ячейку, содержащую текст, из которого необходимо вернуть подстроку;
  • количество_знаков – необязательный аргумент. Целое число, указывающее, какое количество символов необходимо вернуть из текста. По умолчанию принимает значение 1.

  ​ заливка укажите зеленый​ ​Список с выделенным цветом​Введите следующую формулу:​Условное форматирование​ значений. Вообще, если​ Excel».​ Выбираем функцию «Использовать​ заполнении соседнего столбца​ в таблице.​ одинаковыми данными. Первый​
  

  

  Мнение эксперта

  Знайка, самый умный эксперт в Цветочном городе

  Если у вас есть вопросы, задавайте их мне!

  Задать вопрос эксперту

  Предположим, что у нас есть таблица стоимости продуктов питания за 1 кг л , и задача заключается в том, чтобы разбить товары на две группы в зависимости от цены. Если же вы хотите что-то уточнить, я с радостью помогу!
  Функция позволяет найти минимальное числовое значение в указанном списке аргументов. Часто используется при построении финансовой отчетности, когда нужно определить дату начала периода отчета, минимальный чек покупки и другие параметры.

  20 секретов Excel, которые помогут упростить работу — Лайфхакер

  1. Первый блок используется для записи искомой информации.
  2. Вторая часть функции позволяет задать диапазон поиска по части текста.
  3. Третий аргумент является необязательным. Его использование оправдано, если известна точка начала поиска внутри ячейки.

  Порой очень удобно использовать в качестве выделения – значки. То есть каждому знаку можно присвоить свое значение (или интервал значений) и ставить их рядом с теми данными, которые попадают в диапазон.
  

  Поиск в экселе: как пользоваться, примеры поиска

  Если вам нужно объединить текст из нескольких ячеек в одну, необязательно использовать сложные формулы. Достаточно выбрать ячейку, в которой будет соединен текст, нажать «=» и последовательно выбрать ячейки, ставя перед каждой символ «&».
  

  Мнение эксперта

  Знайка, самый умный эксперт в Цветочном городе

  Если у вас есть вопросы, задавайте их мне!

  Задать вопрос эксперту

  Если же вам нужно найти вопросительный знак или астериск и вы не хотите, чтобы вместо них Excel искал неизвестный символ, то поставьте перед ними. Если же вы хотите что-то уточнить, я с радостью помогу!
  Как мы видим, в нашем списке аргументов диапазон из пяти значений, но функция вернет три, ведь числовые значения содержатся только в столбцах A, B, C. В столбце D — текстовое выражение, а Е — незаполненная ячейка.

  10 базовых функций Excel для начинающих | Бизнес-школа LABA (ЛАБА)

  Нажав Ctrl + F, мы попадаем в меню поиска, с помощью которого можно искать любые данные в Excel. Однако его функциональность можно расширить, используя символы «?» и «*». Знак вопроса отвечает за один неизвестный символ, а астериск — за несколько. Их стоит использовать, если вы не уверены, как выглядит искомый запрос.
  

  

  Мнение эксперта

  Знайка, самый умный эксперт в Цветочном городе

  Если у вас есть вопросы, задавайте их мне!

  Задать вопрос эксперту

  МАКС число1; число2 ; находит максимальное значение в списке аргументов, при этом текстовые и пустые выражения игнорируются. Если же вы хотите что-то уточнить, я с радостью помогу!
  Функция дает возможность найти сумму отдельных числовых значений, диапазонов, ссылок на ячейки с числовыми значениями или сумму всех этих 3-х видов. Часто используется в подбивании итоговых значений строк или столбцов при формировании отчетов.

  8 полезных функций и возможностей Excel, о которых не помнят — Блог SF Education

  В данном разделе очень много полезных приемов для аналитиков, экономистов, бухгалтеров и представителей смежных профессий, помогающих не тратить много времени на простейшие, подготовительные этапы анализа данных.

  Технические характеристики → Полезное видео → Общие данные → Таблица размеров → Полезные советы → Как правильно рассчитать → Сыпучие продукты → Натяжные потолки и технологии→ Мера жидких продуктов