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

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

Для чего seo специалисту нужно уметь работать с Excel

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

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

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

Описание проблемы

Когда в формуле вы указываете ссылку на другую книгу, то Excel образует с ней связь. Эта связь будет прекрасно работать и обновляться автоматически в том случае, когда открыты обе книги.

К сожалению, если книга-источник была удалена/перемещена или переименована, то связь нарушится. Также связь будет потеряна если вы переместите конечный файл (содержащий ссылку). Если вы передадите только конечный файл по почте, то получатель тоже не сможет обновить связи.

Описание проблемы

При нарушении связи, ячейки со ссылками на другие книги будут содержать ошибки #ССЫЛКА.

Функция ПОИСКПОЗ в Excel – синтаксис и использование

Функция ПОИСКПОЗ в Excel ищет заданное значение в диапазоне ячеек и возвращает относительное положение этого значения.

Синтаксис формулы ПОИСКПОЗ выглядит следующим образом:

ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])

Искомое_значение (обязательный аргумент) – значение, которое вы хотите найти. Это может быть числовое, текстовое или логическое значение, а также ссылка на ячейку.

Просматриваемый_массив (обязательный аргумент) – диапазон ячеек для поиска.

Тип_сопоставления (необязательный аргумент) – определяет тип соответствия. Это может быть одно из следующих значений: 1, 0, -1.

  • 1 или отсутствует (по умолчанию) – находит наибольшее значение в массиве, которое меньше или равно значению поиска. Массив поиска должен быть отсортирован в порядке возрастания, от самого маленького до большого или от A до Z.
  • 0 – находит первое значение в массиве, которое в точности равно значению поиска. Сортировка не требуется.
  • -1 – находит наименьшее значение в массиве, которое больше или равно значению поиска. Массив поиска должен быть отсортирован в порядке убывания, от самого большого до самого маленького или от Z до A.

Чтобы лучше разобраться в данной функции, давайте напишем простую формулу ПОИСКПОЗ, основанную на следующих данных: фамилии студентов в столбце A и их баллы по экзаменам в столбце B, отсортированные от самых высоких до самых низких. Чтобы узнать, где среди других находится конкретный студент (например, студентка Виноградова), используйте эту простую формулу:

Функция ПОИСКПОЗ в Excel – синтаксис и использование

=ПОИСКПОЗ(E1; A2:A8; 0)

При желании вы можете поместить значение поиска в ячейку (E1 в данном примере) и сослаться на эту ячейку в формуле ПОИСКПОЗ Excel:

Читайте также:  Почему телефон не видит sd карту памяти или usb-флешку. Что делать?

Функция ПОИСКПОЗ в Excel – Пример использования функции ПОИСКПОЗ в Excel

Как вы видите на изображении выше, фамилии студентов вводятся в произвольном порядке, поэтому мы устанавливаем аргумент тип_сопоставления равным 0 (точное совпадение), поскольку только этот тип соответствия не требует сортировки значений в массиве поиска. Технически формула ПОИСКПОЗ возвращает относительное положение студентки Виноградовой в исследуемом диапазоне. Но поскольку оценки сортируются от самых высоких до самых низких, это также говорит нам о том, что Виноградова пятая по счету среди всех учеников.

Особенности функции ПОИСКПОЗ Excel

Как вы только что видели, использовать ПОИСКПОЗ в Excel легко. Однако, как и у любой функции Excel, у нее есть несколько особенностей, о которых следует знать:

  • Функция ПОИСКПОЗ возвращает относительное положение значения поиска в массиве, а не самого значения.
  • ПОИСКПОЗ не зависит от регистра, что означает, что она не различает строчные и прописные символы при работе с текстовыми значениями.
  • Если массив поиска содержит несколько вхождений искомого значения, возвращается позиция первого значения.
  • Если искомое значение не найдено в просматриваемом массиве, возвращается ошибка #Н/Д.

Формулы Эксель

Из чего состоят формулы Эксель:

  1. Знак равно «=»

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

  1. Операторы

Операторы в Excel бывают четырех видов: арифметические, операторы сравнение, операторы объединения текста, операторы ссылок на ячейки.

  1. Функции

Функция – это предопределенная формула, выполняющая определенный тип вычислений. Например, функция СУММ выполняет суммирование определенных ячеек. Благодаря функциям сокращается и упрощается формула в Excel.

Как ввести формулу в Excel

Основным элементом программы Excel являются формулы. Формулы Эксель позволяют получать мгновенный результат её вычислений. При этом формула сразу делает перерасчет при изменении исходных значений.

Рассмотрим следующий пример:

В ячейки A1 и B1 поместим любые числа, например 8 и 5 соответственно. А в ячейку C1 введем формулу:

=A1*B1

Чтобы ввести эту формулу в таблице Excel необходимо выполнить строгую последовательность действий:

  1. Кликните по ячейке С1;
  2. Введите следующую формулу: =A1*B1
  3. В завершении нажмите Enter.

Можно поступить и по-другому.

  1. Кликните по ячейке С1;
  2. С помощью клавиатуры введите знак равно «=»;
  3. Кликните по ячейке A1

При этом в ячейке C1 появится ссылка на ячейку A1

  1. На клавиатуре нажмите символ звездочки «*»;

В Excel в качестве оператора умножения используется символ звездочки «*».

  1. Далее кликните мышкой по ячейке B1;

При этом в ячейке после звездочки появится ссылка на ячейку B1.

  1. В завершении нажмите Enter.

В ячейке C1 отобразится результат умножения ячеек A1 и B1.

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

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

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

Найти

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

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

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

Определения терминов

В этой статье для описания встроенных функций Excel используются указанные ниже условия.

Вся таблица подстановки

Значение, которое будет найдено в первом столбце аргумента «инфо_таблица».

Просматриваемый_массив -или- Лукуп_вектор

Диапазон ячеек, которые содержат возможные значения подстановки.

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

3 (третий столбец в инфо_таблица)

Ресулт_аррай -или- Ресулт_вектор

Диапазон, содержащий только одну строку или один столбец. Он должен быть такого же размера, что и просматриваемый_массив или Лукуп_вектор.

Логическое значение (истина или ложь). Если указано значение истина или опущено, возвращается приближенное соответствие. Если задано значение FALSE, оно будет искать точное совпадение.

Это ссылка, на основе которой вы хотите основать смещение. Топ_целл должен ссылаться на ячейку или диапазон смежных ячеек. В противном случае функция СМЕЩ возвращает #VALUE! значение ошибки #ИМЯ?.

Число столбцов, находящегося слева или справа от которых должна указываться верхняя левая ячейка результата. Например, значение "5" в качестве аргумента Оффсет_кол указывает на то, что верхняя левая ячейка ссылки состоит из пяти столбцов справа от ссылки. Оффсет_кол может быть положительным (то есть справа от начальной ссылки) или отрицательным (то есть слева от начальной ссылки).

Простые примеры

При использовании метода в VBA Excel необходимо учитывать следующие нюансы:

  1. Так как этот метод возвращает объект Range (в виде одной ячейки), присвоить его можно только объектной переменной, объявленной как Variant, Object или Range, при помощи оператора Set.
  2. Если поисковая фраза в заданном диапазоне найдена не будет, метод возвратит значение Nothing. Обращение к свойствам несуществующей ячейки будет генерировать ошибки. Поэтому, перед использованием результатов поиска, необходимо проверить объектную переменную на содержание в ней значения Nothing.

В примерах используются переменные:

  • myPhrase – переменная для записи поисковой фразы;
  • myCell – переменная, которой присваивается первая найденная ячейка, содержащая поисковую фразу, или значение Nothing, если поисковая фраза не найдена.

Пример 1

12345678910111213 Sub primer1()Dim myPhrase As Variant, myCell As RangemyPhrase = «стакан»Set myCell = Range(«A1:L30»).Find(myPhrase)If Not myCell Is Nothing ThenMsgBox «Значение найденной ячейки: « & myCellMsgBox «Строка найденной ячейки: « & myCell.RowMsgBox «Столбец найденной ячейки: « & myCell.ColumnMsgBox «Адрес найденной ячейки: « & myCell.AddressElseMsgBox «Искомая фраза не найдена»End IfEnd Sub

В этом примере мы присваиваем переменной myPhrase значение для поиска – «стакан». Затем проводим поиск этой фразы в диапазоне «A1:L30» с присвоением результата поиска переменной myCell. Далее проверяем переменную myCell, не содержит ли она значение Nothing, и выводим соответствующие сообщения.

Ознакомьтесь с работой кода VBA в случаях, когда в диапазоне «A1:L30» есть ячейка со строкой, содержащей подстроку «стакан», и когда такой ячейки нет.

Пример 2

Теперь посмотрим, как метод отреагирует на поиск числа. В качестве диапазона поиска будем использовать первую строку активного листа Excel.

123456789 Sub primer2()Dim myPhrase As Variant, myCell As RangemyPhrase = Set myCell = Rows(1).Find(myPhrase)If Not myCell Is Nothing ThenMsgBox «Значение найденной ячейки: « & myCellElse: MsgBox «Искомая фраза не найдена»End IfEnd Sub

Несмотря на то, что мы присвоили переменной числовое значение, метод найдет ячейку со значением и 526,15, и 129526,15, и 526,15254. То есть, как и в предыдущем примере, поиск идет по подстроке.

Чтобы найти ячейку с точным соответствием значения поисковой фразе, используйте константу xlWhole параметра LookAt:

1 Set myCell = Rows(1).Find(myPhrase, , , xlWhole)

Аналогично используются и другие необязательные параметры. Количество «лишних» запятых перед необязательным параметром должно соответствовать количеству пропущенных компонентов, предусмотренных синтаксисом метода

Пример 3

Метод Find объекта Range работает намного быстрее циклов VBA, поэтому именно его следует использовать для поиска необходимой информации в большой таблице.

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

12345678910 Sub primer3()Dim myPhrase As Variant, myCell As RangemyPhrase = «»myPhrase = CDate(myPhrase)Set myCell = Range(«A:A»).Find(myPhrase)If Not myCell Is Nothing ThenMsgBox «Номер начальной строки: « & myCell.RowElse: MsgBox «Даты « & myPhrase & » в таблице нет»End IfEnd Sub

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

Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.

Видеоурок по теме

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

Есть мнение?Оставьте комментарий

Понравился материал?Хотите прочитать позже?Сохраните на своей стене иподелитесь с друзьями

Видеоурок по теме

Вы можете разместить на своём сайте анонс статьи со ссылкой на её полный текст

Программа Excel ориентирована на ускоренные расчеты. Зачастую документы здесь состоят из большого ко.

Программа Excel ориентирована на ускоренные расчеты. Зачастую документы здесь состоят из большого количества листов, на которых представлены длинные таблицы с числами, формулами или текстом. Для удобного нахождения нужных ячеек существует специальный автоматизированный поиск. Ознакомившись с особенностями его использования, можно сократить время работы в документах. О том, как искать в Экселе слова, фразы или ячейки, подробно написано ниже.

Поиск значений в таблице Excel

Для решения данной задачи проиллюстрируем пример на схематической таблице, которая соответствует выше описанным условиям.

Лист с таблицей для поиска значений по вертикали и горизонтали:

Над самой таблицей расположена строка с результатами. В ячейку B1 водим критерий для поискового запроса, то есть заголовок столбца или название строки. А в ячейке D1 формула поиска должна возвращать результат вычисления соответствующего значения. После чего в ячейке F1 сработает вторая формула, которая уже будет использовать значения ячеек B1 и D1 в качестве критериев для поиска соответствующего месяца.

Классический поиск (обыкновенный).

Вызвать панель (меню) поиска можно сочетанием горячих клавиш ctrl+F. (Легко запомнить: F- Found).

Окно поиска состоит из поля, в которое вводится искомый фрагмент текста или искомое число, вкладки с дополнительными настройками («Параметры») и кнопки «Найти».

Классический поиск в Excel

Классический поиск (обыкновенный).

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

Условное форматирование для искомых ячеек.