Поиск по таблице в excel

Поиск в таблице Excel

Также статьи о работе с таблицами в Экселе:

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

Для осуществления поиска данных в таблице Excel необходимо использовать пункт меню «Найти и выделить» на вкладке «Главная», в котором нужно выбирать вариант «Найти» или воспользоваться для вызова комбинацией клавиш «Ctrl + F».

Для примера попробуем найти необходимое число среди данных нашей таблицы, так как именно при поиске чисел необходимо учитывать некоторые тонкости поиска. Будем искать в таблице Excel число «10».

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

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

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

При поиске слов в таблице Excel следует также учитывать все эти тонкости и к примеру, можно учитывать даже регистр букв.

Ну и на последок рассмотрим, как сделать поиск данных в Экселе только в необходимой области листа. Как видно из нашего примера, искомое значение «10» встречается сразу во всех столбцах данных. Если необходимо это значение найти, допустим, только в первом столбце, необходимо выделить данный столбец или любую область значений, в которой необходимо произвести поиск, а затем уже приступать к поиску.

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

Поиск значений в таблице «Excel» . Макрос.

Как выполнить поиск значений в программе «Excel» .

Далее описаны несколько вариантов поиска и фильтрации данных в таблице «Эксель».

Классический поиск «MS Office».
Условное форматирование (выделение нужных ячеек цветом)
Настройка фильтров по одному или нескольким значениям.
Фрагмент макроса для перебора ячеек в диапазоне и поиска нужного значения.

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

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

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

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

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

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

2) Еще один вариант поиска заключается в выделении всех ячеек содержащих искомое слово каким-нибудь цветом.

Такой поиск реализуется через опцию условное форматирование.

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

Настройка форматирования для выделения искомых слов

В меню условного форматирования выбрать «Правила выделения ячеек» … «Текст содержит…».

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

3) Третий способ поиска слов в таблице «Excel» — это использование фильтров.

Фильтр устанавливается во вкладке «Данные» или сочетанием клавиш ctrl+shift+L. Настройка фильтра для поиска слов

Кликнув по треугольнику фильтра можно в контекстном меню выбрать пункт «Текстовые фильтры», далее «содержит…» и указать искомое слово.

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

4) Способ поиска номер четыре — это макрос VBA для поиска (перебора значений).

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

Sub Poisk()

ruexcel.ru макрос проверки значений (поиска)

Dim keyword As String

keyword = «Искомое слово» ‘присвоить переменной искомое слово

On Error Resume Next ‘при ошибке пропустить

For Each cell In Selection ‘для всх ячеек в выделении (выделенном диапазоне)

If cell.Value = «» Then GoTo Line1 ‘если ячейка пустая перейти на «Line1″

If InStr(StrConv(cell.Value, vbLowerCase), keyword) > 0 Then cell.Interior.Color = vbRed ‘если в ячейке содержится слово окрасить ее в красный цвет (поиск)

4 способа поиска данных в таблице Excel

Добрый день уважаемый читатель!

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

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

Читать еще:  Как в excel в ячейке создать список

Теперь на примерах рассмотрим все 4 способа поиска данных в таблице Excel и комбинаций работы функции ВПР с другими функциями:

Используем функцию СУММПРОИЗВ

Как я уже описывал ранее в своей статье о функции СУММПРОИЗВ, она является одной из мощнейших в арсенале Excel. И именно первый способ мы сделаем с помощью возможностей формулы при использовании функции СУММПРОИЗВ. Для наших целей формула будет выглядеть так:

=СУММПРОИЗВ((C2:C11=G2)*(B2:B11=G3);D2:D11) Принцип работы формулы следующий: создается условная таблица, в которой значения ячеек «G2» сравнивается с диапазоном «C2:C11» и ячейка «G3» с диапазоном «B2:B11». После этого сравниваются и сопоставляются все эти два массива и переводятся в единицы и нули, где значение единицы ставится строке, где все условия формулы выполнены. Следующая операция – это умножения полученного условного массива на диапазон «D2:D11», а поскольку в массиве всего одна единичка то формула получит результат 146.

Обращаю ваше внимание, если в диапазоне «D2:D11» будут найдены текстовые значения, формула откажется работать. Для более углублённого ознакомления с функцией СУММПРОИЗВ советую почитать мою статью.

Применение функции ВЫБОР

Я описывал уже функцию ВЫБОР, но в таком исполнении еще не упоминал. В нашем случае нужно создать новую таблицу, в которой будут совместными столбики «Период» и «Месяц», всё это виртуально создаст функция ВЫБОР. Формула для работы будет выглядеть так:

<=ВПР(G2&G3;ВЫБОР(<1;2>;C2:C11&B2:B11;D2:D11);2;0)> Основная работа, которую проделывает функция ВЫБОР в своей части «ВЫБОР(<1;2>;C2:C11&B2:B11;D2:D11)» это объединение значений столбиков «Период» и «Город» в общий массив, значения в котором будут прописаны как: «МоскваЯнварь», «БрянскФевраль», …. и т.д. Получив такое объединённое значения столбиков мы сможем легко сделать просмотр и отбор нужного значения, вот теперь я думаю, формула стала ближе.

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

Создаем дополнительные столбики

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

Рассмотрим на стандартном примере, когда необходимо определить продажи по двум показателям: «Период» и «Город». В этом случае обыкновенное использование функции ВПР не будет нам подходить, так как функция может возвращать значение по одному условию. В таком случае нам необходимо создать дополнительный столбик, в котором произойдёт объединение двух критериев в один, поэтому в созданном столбике приписываем формулу слияния значений: =B2&C2. А вот теперь результат из столбика D, мы сможем использовать в ячейке H4 нашу формулу:

=ВПР(H2&H3;D2:E11;2;0)

Как видите, наши отдельные условия отбора значений также объединяются аргументом H2&H3 в один критерий. После поиска в указанном диапазоне D2:E11, формула вернёт найденное значение со столбика 2.

Совмещаем функции ПОИСКПОЗ и ИНДЕКС для работы

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

А для нашего поиска данных в таблице Excel будем использовать такую формулу:

Что же она делает, такая большая и непонятная…. Рассмотрим ее в разрезе нескольких блоков или этапов. Формула для функции имеет такой вид ПОИСКПОЗ (1;(B2:B11=G3)*(C2:C11=G2);0) и происходит следующее, со значением в ячейке G3, последовательно сравниваются значения из диапазона B2:B11 и в случае совпадения условий получаем результат ИСТИНА, а если есть отличия получаем ЛОЖЬ. Такой же процесс происходит для значения G2 и диапазона C2:C11. После сравнения этих массивов, которые состоят из аргументов ИСТИНА и ЛОЖЬ, производится сравнения на соответствие значению 1, это ИСТИНА*ИСТИНА, все остальные комбинации будут проигнорированы.

Теперь, когда функция ПОИСКПОЗ нашла в массиве значение, которое соответствует «1» и указала его позицию в шестой строке, а значит, в функцию ИНДЕКС был передан аргумент «6» для диапазона D2:D11.

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

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

Поиск в программе Microsoft Excel

В документах Microsoft Excel, которые состоят из большого количества полей, часто требуется найти определенные данные, наименование строки, и т.д. Очень неудобно, когда приходится просматривать огромное количество строк, чтобы найти нужное слово или выражение. Сэкономить время и нервы поможет встроенный поиск Microsoft Excel. Давайте разберемся, как он работает, и как им пользоваться.

Поисковая функция в Excel

Поисковая функция в программе Microsoft Excel предлагает возможность найти нужные текстовые или числовые значения через окно «Найти и заменить». Кроме того, в приложении имеется возможность расширенного поиска данных.

Способ 1: простой поиск

Простой поиск данных в программе Excel позволяет найти все ячейки, в которых содержится введенный в поисковое окно набор символов (буквы, цифры, слова, и т.д.) без учета регистра.

  1. Находясь во вкладке «Главная», кликаем по кнопке «Найти и выделить», которая расположена на ленте в блоке инструментов «Редактирование». В появившемся меню выбираем пункт «Найти…». Вместо этих действий можно просто набрать на клавиатуре сочетание клавиш Ctrl+F.

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

При нажатии на кнопку «Найти далее» мы перемещаемся к первой же ячейке, где содержатся введенные группы символов. Сама ячейка становится активной.

Читать еще:  Поиск в excel по столбцу

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

Поисковые символы не обязательно должны быть самостоятельными элементами. Так, если в качестве запроса будет задано выражение «прав», то в выдаче будут представлены все ячейки, которые содержат данный последовательный набор символов даже внутри слова. Например, релевантным запросу в этом случае будет считаться слово «Направо». Если вы зададите в поисковике цифру «1», то в ответ попадут ячейки, которые содержат, например, число «516».

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

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

  • В случае, если при запуске поисковой процедуры вы нажмете на кнопку «Найти все», все результаты выдачи будут представлены в виде списка в нижней части поискового окна. В этом списке находятся информация о содержимом ячеек с данными, удовлетворяющими запросу поиска, указан их адрес расположения, а также лист и книга, к которым они относятся. Для того, чтобы перейти к любому из результатов выдачи, достаточно просто кликнуть по нему левой кнопкой мыши. После этого курсор перейдет на ту ячейку Excel, по записи которой пользователь сделал щелчок.
  • Способ 2: поиск по указанному интервалу ячеек

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

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

  • Набираем на клавиатуре комбинацию клавиш Ctrl+F, после чего запуститься знакомое нам уже окно «Найти и заменить». Дальнейшие действия точно такие же, что и при предыдущем способе. Единственное отличие будет состоять в том, что поиск выполняется только в указанном интервале ячеек.
  • Способ 3: Расширенный поиск

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

    К тому же, в выдачу может попасть не только содержимое конкретной ячейки, но и адрес элемента, на который она ссылается. Например, в ячейке E2 содержится формула, которая представляет собой сумму ячеек A4 и C3. Эта сумма равна 10, и именно это число отображается в ячейке E2. Но, если мы зададим в поиске цифру «4», то среди результатов выдачи будет все та же ячейка E2. Как такое могло получиться? Просто в ячейке E2 в качестве формулы содержится адрес на ячейку A4, который как раз включает в себя искомую цифру 4.

    Но, как отсечь такие, и другие заведомо неприемлемые результаты выдачи поиска? Именно для этих целей существует расширенный поиск Excel.

      После открытия окна «Найти и заменить» любым вышеописанным способом, жмем на кнопку «Параметры».

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

    По умолчанию, функции «Учитывать регистр» и «Ячейки целиком» отключены, но, если мы поставим галочки около соответствующих пунктов, то в таком случае, при формировании результата будет учитываться введенный регистр, и точное совпадение. Если вы введете слово с маленькой буквы, то в поисковую выдачу, ячейки содержащие написание этого слова с большой буквы, как это было бы по умолчанию, уже не попадут. Кроме того, если включена функция «Ячейки целиком», то в выдачу будут добавляться только элементы, содержащие точное наименование. Например, если вы зададите поисковый запрос «Николаев», то ячейки, содержащие текст «Николаев А. Д.», в выдачу уже добавлены не будут.

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

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

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

    Ещё более точно поиск можно задать, нажав на кнопку «Формат».

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

    Если вы хотите использовать формат какой-то конкретной ячейки, то в нижней части окна нажмите на кнопку «Использовать формат этой ячейки…».

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

    Читать еще:  Как вставить таблицу из excel в word

    После того, как формат поиска настроен, жмем на кнопку «OK».

    Бывают случаи, когда нужно произвести поиск не по конкретному словосочетанию, а найти ячейки, в которых находятся поисковые слова в любом порядке, даже, если их разделяют другие слова и символы. Тогда данные слова нужно выделить с обеих сторон знаком «*». Теперь в поисковой выдаче будут отображены все ячейки, в которых находятся данные слова в любом порядке.

  • Как только настройки поиска установлены, следует нажать на кнопку «Найти всё» или «Найти далее», чтобы перейти к поисковой выдаче.
  • Как видим, программа Excel представляет собой довольно простой, но вместе с тем очень функциональный набор инструментов поиска. Для того, чтобы произвести простейший писк, достаточно вызвать поисковое окно, ввести в него запрос, и нажать на кнопку. Но, в то же время, существует возможность настройки индивидуального поиска с большим количеством различных параметров и дополнительных настроек.

    Отблагодарите автора, поделитесь статьей в социальных сетях.

    Поиск значений в списке данных

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

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

    В этой статье

    Поиск значений в списке по вертикали по точному совпадению

    Для выполнения этой задачи можно использовать функцию ВПР или сочетание функций индекс и ПОИСКПОЗ.

    Примеры использования функции ВПР

    Дополнительные сведения можно найти в разделе функция ВПР.

    Примеры ИНДЕКСов и СОВПАДЕНИй

    =ИНДЕКС(нужно вернуть значение из C2:C10, которое будет соответствовать ПОИСКПОЗ(первое значение «Капуста» в массиве B2:B10))

    Формула ищет первое значение в ячейке C2: C10, соответствующее капусты (в B7), и возвращает значение в C7 (100) — первое значение, соответствующее капусты.

    Дополнительные сведения можно найти в разделе Функция индекс и функция ПОИСКПОЗ.

    Поиск значений в списке по вертикали по приблизительному совпадению

    Для этого используйте функцию ВПР.

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

    В приведенном выше примере функция ВПР ищет имя учащегося, у которого есть 6 тардиес в диапазоне A2: B7. В таблице нет записи для 6 тардиес, поэтому функция ВПР ищет следующее самое высокое соответствие ниже 6 и находит значение 5, связанное с первым именем Дэйв, и, следовательно, возвращает Дэйв.

    Дополнительные сведения можно найти в разделе функция ВПР.

    Поиск значений по вертикали в списке неизвестного размера с точным соответствием

    Для выполнения этой задачи используйте функции СМЕЩ и ПОИСКПОЗ.

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

    C1 — это верхняя левая ячейка диапазона (также называемая начальной ячейкой).

    Match («апельсины»; C2: C7; 0) ищет оранжевый цвет в диапазоне C2: C7. Не следует включать начальную ячейку в диапазон.

    1 — количество столбцов справа от начальной ячейки, для которых должно быть возвращено возвращаемое значение. В нашем примере возвращаемое значение находится в столбце D, Sales.

    Поиск значений в списке по горизонтали по точному совпадению

    Для выполнения этой задачи используется функция ГПР. Ниже приведен пример.

    Функция ГПР выполняет поиск по столбцу Sales и возвращает значение из строки 5 в указанном диапазоне.

    Дополнительные сведения можно найти в разделе функции ГПР.

    Поиск значений в списке по горизонтали с использованием приблизительного совпадения

    Для выполнения этой задачи используется функция ГПР.

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

    В приведенном выше примере функция ГПР ищет значение 11000 в строке 3 в указанном диапазоне. Он не находит 11000 и, следовательно, ищет следующее наибольшее значение, которое меньше 1100 и возвращает число 10543.

    Дополнительные сведения можно найти в разделе функции ГПР.

    Создание формулы подстановки с помощью мастера подстановок (толькоExcel 2007 )

    Примечание: Надстройка «Мастер подстановок» прекращена в Excel 2010. Эти функциональные возможности заменены мастером функций и доступными функциями поиска и работы со ссылками (ссылками).

    В Excel 2007 мастер подстановок создает формулу подстановки на основе данных листа, имеющих заголовки строк и столбцов. Мастер подстановок помогает находить другие значения в строке, когда вы знаете значение в одном столбце, и наоборот. Мастер подстановок использует индекс и СОВПАДЕНИе в создаваемых формулах.

    Щелкните ячейку в диапазоне.

    На вкладке формулы в группе решения нажмите кнопку Подстановка .

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

    Загрузка программы-надстройки «Мастер подстановок»

    Нажмите кнопку Microsoft Office , щелкните Параметры Excelи выберите категорию надстройки.

    В поле Управление выберите элемент Надстройки Excel и нажмите кнопку Перейти.

    В диалоговом окне надстройки установите флажок Мастер подстановоки нажмите кнопку ОК.

    Похожие статьи

    Ссылка на основную публикацию
    Похожие публикации
    Adblock
    detector