Как в эксель быстро найти нужную фамилию из списка
Выпадающие списки в Excel - это тема, которая интересует многих пользователей программы, ведь с помощью таких списков можно существенно облегчить ввод информации в таблицу или создать удобный интерфейс для доступа к данным. Но если выпадающий список имеет слишком много элементов, то быстро найти нужный из них становится затруднительным. В этой ситуации будет полезен выпадающий список с возможностью поиска.
Например, нужно по имени человека выводить его номер телефона.
В обычном выпадающем списке будут перечислены все имена из телефонной книги (таблицы с данными) и быстро найти нужного человека не так-то просто. Однако если список имеет возможность поиска, то можно ввести какой-то набор символов и в списке отобразятся только те данные, в которых этот набор символов встречается, причем неважно в какой части.
Как и многое в Excel создать такой список можно разными способами. У каждого из них есть свои плюсы и минусы.
Excel постоянно совершенствуется и в нем появляются инструменты, которые позволяют существенно упростить ряд задач, поэтому я расскажу о двух способах создания выпадающего списка с быстрым поиском.
Файлы с примерами можно скачать здесь .
Первый вариант выпадающего списка с поиском (динамические массивы)
Итак, есть исходная таблица в два столбца. Первый содержит список имен, а второй телефоны. В еще одну из ячеек первой строки введем набор символов, который мы хотим найти, то есть смоделируем поисковое поле (введем " сми ").
Ниже этой ячейки мы должны сформировать список только из тех данных исходной таблицы, которые содержат введенные выше символы.
Поможет в этом функция ПОИСК ( SEARCH ), которая ищет заданный набор символов и выдает либо порядковый номер первого символа в тексте, либо ошибку, если символы обнаружены не были.
В качестве искомого текста указываем ссылку на ячейку выше, а искать мы будем в первой ячейке столбца с именами. Последний аргумент функции необязателен и в нашем случае указывать его не будем.
Что мы получили в итоге?
Цифра "1" указывает нам позицию введенных символов в выбранном нами тексте (строка начинается с этих символов).
Если введем « але », то получим цифру 9, которая указывает на девятую позицию. Именно девятым будет первый символ искомого текста в этом имени (пробел также учитывается).
Если же введем « ива », то получим ошибку ЗНАЧ! , которая указывает на то, что такой комбинации символов в тексте не найдено.
Так работает функция ПОИСК и нам лишь остается указать весь диапазон, на который она должна распространяться. Для этого откорректируем формулу и подставим сюда весь диапазон - выбираем первую его ячейку (А2), а затем нажимаем сочетание клавиш Ctrl + Shift + стрелка вниз . Будет выбран весь неразрывный диапазон значений. В моем случае А2:А30. Нажимаем Enter и получаем диапазон значений, который представляет собой динамический массив . На это также указывает синий контур, обрамляющий значения.
Динамические массивы в Excel появились несколько лет назад, но до сих пор они доступны лишь пользователям Microsoft Office 365 и в онлайн-версии офисного пакета. В других версиях и редакциях Excel динамических массивов пока нет и вряд ли они там появится. Скорее всего в коробочной редакции Excel их стоит ждать лишь в следующей версии офисного пакета в 22-ом году.
Тем не менее, динамические массивы и функции с ними связанные очень сильно расширяют возможности Excel и я уверен, что в дальнейшем они получат еще более широкий функционал. По этой причине, хоть данный способ пока и не универсален, но он максимально прост, удобен и в перспективе будет востребован. Поэтому я детально рассмотрю весь процесс создания выпадающего списка с помощью динамических массивов и их функций.
Итак, мы получили динамический массив. Если в его ячейках отражается любая цифра, то это означает, что искомая комбинация символов была найдена, а ошибка свидетельствует об обратном.
Фактически нас интересуют только ячейки с числами, при этом сами значения неважны. Поэтому можем переконвертировать полученные значения в формат ИСТИНА/ЛОЖЬ .
Для этого воспользуемся функцией ЕЧИСЛО ( ISNUMBER ) и обернем ей созданную ранее формулу. В результате если в ячейке будет находиться число, то функция выдаст ИСТИНУ , иначе ЛОЖЬ .
Осталось лишь отфильтровать значения, ведь нас интересует только ИСТИНА . Воспользуемся функцией ФИЛЬТР ( FILTER ), которая позволяет работать с динамическими массивами.
Обернем созданную ранее формулу функцией ФИЛЬТР .
Первый аргумент функции - массив . Укажем диапазон с именами А2:А30. Далее нужно указать то, что нужно включить в новый диапазон, а это вычисляет ранее созданная формула. В качестве третьего аргумента зададим значение, которое будет выводиться в случае отсутствия искомых символов в тексте. Например, напишем « не найдено ».
Все, формула готова!
Вот так просто мы получили список из имен, которые удовлетворяют условиям поиска. Осталось лишь создать выпадающий список. Для этого откорректируем формулу и подставим в функцию ПОИСК ссылку на ячейку, в которой будет выпадающий список. В моем примере эти ячейки находятся на втором листе.
Предположим, что вы хотите найти расширение телефона сотрудника, используя его номер эмблемы или правильную ставку комиссионных за объем продаж. Вы можете искать данные для быстрого и эффективного поиска определенных данных в списке, а также для автоматической проверки правильности данных. После поиска данных можно выполнить вычисления или отобразить результаты с возвращаемой величиной. Существует несколько способов поиска значений в списке данных и отображения результатов.
Что необходимо сделать
Точное совпадение значений по вертикали в списке
Для этого можно использовать функцию ВLOOKUP или сочетание функций ИНДЕКС и НАЙТИПОЗ.
Примеры ВРОТ
Дополнительные сведения см. в этой информации.
Примеры индексов и совпадений
=ИНДЕКС(нужно вернуть значение из C2:C10, которое будет соответствовать ПОИСКПОЗ(первое значение "Капуста" в массиве B2:B10))
Формула ищет в C2:C10 первое значение, соответствующее значению "Ольга" (в B7), и возвращает значение в C7(100),которое является первым значением, которое соответствует значению "Ольга".
Подыыывка значений по вертикали в списке с помощью приблизительного совпадения
Для этого используйте функцию ВЛВП.
Важно: Убедитесь, что значения в первой строке отсортировали в порядке возрастания.
Дополнительные сведения см. в этой информации.
Подстановка значений по вертикали в списке неизвестного размера с использованием точного совпадения
Для этого используйте функции СМЕЩЕНИЕ и НАЙТИВМЕСЯК.
Примечание: Используйте этот подход, если данные в диапазоне внешних данных обновляются каждый день. Вы знаете, что цена находится в столбце B, но вы не знаете, сколько строк данных возвращает сервер, а первый столбец не отсортировали по алфавиту.
C1 — это левые верхние ячейки диапазона (также называемые начальной).
MATCH("Оранжевая";C2:C7;0) ищет "Оранжевые" в диапазоне C2:C7. В диапазон не следует включать запускаемую ячейку.
1 — количество столбцов справа от начальной ячейки, из которых должно быть возвращено значение. В нашем примере возвращается значение из столбца D, Sales.
Точное совпадение значений по горизонтали в списке
Для этого используйте функцию ГГПУ. См. пример ниже.
Г ПРОСМОТР ищет столбец "Продажи" и возвращает значение из строки 5 в указанном диапазоне.
Дополнительные сведения см. в сведениях о функции Г ПРОСМОТР.
Подыыывка значений по горизонтали в списке с использованием приблизительного совпадения
Для этого используйте функцию ГГПУ.
Важно: Убедитесь, что значения в первой строке отсортировали в порядке возрастания.
В примере выше ГЛЕБ ищет значение 11000 в строке 3 указанного диапазона. Она не находит 11000, поэтому ищет следующее наибольшее значение меньше 1100 и возвращает значение 10543.
Дополнительные сведения см. в сведениях о функции Г ПРОСМОТР.
Создание формулы подступа с помощью мастера подметок (толькоExcel 2007 )
В Excel 2007 создается формула подытов на основе данных на основе данных на основе строк и столбцов. Если вы знаете значение в одном столбце и наоборот, мастер под поисков помогает находить другие значения в строке. В формулах, которые он создает, используются индекс и MATCH.
Щелкните ячейку в диапазоне.
На вкладке Формулы в группе Решения нажмите кнопку Под поиск.
Если команда Подытов недоступна, вам необходимо загрузить мастер под надстройка подытогов.
Загрузка надстройки "Мастер подстройок"
В поле Управление выберите элемент Надстройки Excel и нажмите кнопку Перейти.
В диалоговом окне Доступные надстройки щелкните рядом с полем Мастер подстрок инажмите кнопку ОК.
У меня было сотня случаев, когда из списка сотрудников необходимо было достать фамилию или имя. При этом список насчитывал минимум пару сотен человек и делать это в ручную было очень грустно. Хорошо, что в Excel есть все необходимые инструменты, позволяющие оптимизировать данный процесс и решить поставленную задачу за несколько минут. Текстовые формулы Excel, позволяют решать подобные задачи. Сейчас я вам все подробно объясню на примере нескольких актеров любезно предоставленных первой страницей Google.
Текстовые формулы Excel, которые нам понадобятся
Для решения поставленной задачи нам понадобится собственно Excel и следующие стандартные текстовые функции:
- ПОИСК - позволяет найти текст или значение в тексте
- ДЛСТР - подсчитывает количество символов в ячейке с текстом
- ПРАВСИМВ - выдает заданной количество символов с конца текста
Наш особенно сообразительный читатель возможно уже догадался как решить нашу задачу, но я всё же объясню для всех по шагам как это нужно было бы мне 🙂
Для решения нашей задачи нам понадобиться:
- Посчитать длину текста в ФИО с помощью функции ДЛСТР
- Найти номер знака с которого начинается фамилия с помощью функции ПОИСК
- Посчитать длинну фамилии как разницу между результатами пунктов 1 и 2
- Вывести фамилию с помощью функции ПРАВСИМВ и длинны фамилии из третьего пункта.
Звучит как план к действию. Приступим!
Фукнция ДЛСТР
Данная текстовая функция Excel - одна из простейших. Ей просто нужно указать ячейку и она выдает количество символов в ней. Вы и сами можете убедиться на примере:
Функция ПОИСК
Функция поиск ищет для нас номер символа или текста. Учитывая, что все фамилии начинаются с разных букв, мы будем искать пробел пред отчеством и фамилией. Следующий после этого символ и будет началом отчества или фамилии.
Важно отметить, что пробел или искомый текст в данной функции записывается в ковычках. В частности это будет примерно так =ПОИСК(" "; 'ячейка с текстом').
Учитывая, что фамилия находится после второго пробела мы будем использовать два поиска, один из них вложенный для поиска первого пробела, как на примере ниже:
Функция ПРАВСИМВ
У нас все готово, чтобы извлечь фамилию из ФИО и решить нашу задачку. Для этого в аргументах функции указываем ячейку с ФИО актера и количество символов рассчитываем как разницу между первым и вторым действием. В итоге собираем решение вместе:
Мы с вами разобрали как работают лишь несколько из текстовых функций Excel. Подписывайтесь и следите за публикациями. Есть ещё много чего рассказать.
В Microsoft Excel часто работают с большими объёмами информации. В нём создаются огромные таблицы на тысячи строк, столбцов и позиций. Отыскать какие-то конкретные данные в таком массиве бывает трудно. А иногда — вообще невозможно. Эту задачу можно упростить. Разберитесь, как в Экселе найти нужное слово. Так вам легче будет ориентироваться в документе. И вы сможете быстро перейти к искомой информации.
В редакторе таблиц существует несколько способов поиска
Поиск ячеек
Чтобы отобразились адреса всех ячеек, в которых есть то, что вы ищите, сделайте следующее:
- Если у вас Office 2010, перейдите в Меню — Правка — Найти.
- Откроется окно с полем для ввода. Напишите в нём фразу для поиска.
- В Excel 2007 эта кнопка есть в меню «Главная» на панели «Редактирование». Она находится справа.
- Аналогичного результата во всех версиях можно добиться, если нажать клавиши Ctrl+F.
- В поле напечатайте слово, фразу или цифры, которые надо отыскать.
- Нажмите «Найти всё», чтобы запустить поиск по целому документу. Если кликните «Далее», программа будет по одной выделять клетки, которые находятся ниже курсора-ячейки Excel.
- Подождите, пока процесс закончится. Чем больше документ, тем дольше система будет искать.
- Появится список с результатами: именами и адресами клеток, в которых есть совпадения с заданной фразой, и текстом, который в них написан.
- При клике на каждую строчку будет выделяться соответствующая ячейка.
- Для удобства можете «растянуть» окно. Так в нём будет видно больше строчек.
- Чтобы отсортировать данные, кликайте на названия столбцов над результатами поиска. Если нажмёте на «Лист», строчки выстроятся по алфавиту в зависимости от названия листа, если выберите «Значения» — встанут по значению.
- Эти столбцы также «растягиваются».
Параметры поиска
Можете задать свои условия. Например, запустить поиск по нескольким знакам. Вот как в Экселе найти слово, которое вы не помните целиком:
- Введите только часть надписи. Можно хоть одну букву — будут выделены все места, в которых она есть. * (звёздочка) и ? (вопросительный знак). Они замещают пропущенные знаки.
- Вопрос обозначает одну отсутствующую позицию. Если вы напишите, к примеру, «П. », отобразятся ячейки, в которых есть слово из четырёх символов, начинающееся на «П»: «Плуг», «Поле», «Пара» и так далее.
- Звезда (*) замещает любое количество знаков. Чтобы отыскать все значения, в которых содержится корень «раст», начните поиск по ключу «*раст*».
Также вы можете зайти в настройки:
- В окне «Найти» нажмите «Параметры».
- В разделах «Просматривать» и «Область поиска», укажите, где и по каким критериям надо искать совпадения. Можно выбрать формулы, примечания или значения.
- Чтобы система различала строчные и прописные буквы, поставьте галочку в «Учитывать регистр».
- Если вы отметите пункт «Ячейка целиком», в результатах появятся клетки, в которых есть только заданная поисковая фраза и ничего больше.
Параметры формата ячеек
Чтобы отыскать значения с определённой заливкой или начертанием, используйте настройки. Вот как найти в Excel слово, если оно имеет отличный от остального текста вид:
- В окне поиска нажмите «Параметры» и кликните на кнопку «Формат». Откроется меню с несколькими вкладками.
- Можете указать определённый шрифт, вид рамки, цвет фона, формат данных. Система будет просматривать места, которые подходят к заданным критериям.
- Чтобы взять информацию из текущей клетки (выделенной в этот момент), нажмите «Использовать формат этой ячейки». Тогда программа отыщет все значения, у которых тот же размер и вид символов, тот же цвет, те же границы и тому подобное.
Поиск нескольких слов
В Excel можно отыскать клетки по целым фразам. Но если вы ввели ключ «Синий шар», система будет работать именно по этому запросу. В результатах не появятся значения с «Синий хрустальный шар» или «Синий блестящий шар».
Чтобы в Экселе найти не одно слово, а сразу несколько, сделайте следующее:
- Напишите их в строке поиска.
- Поставьте между ними звёздочки. Получится «*Текст* *Текст2* *Текст3*». Так отыщутся все значения, содержащие указанные надписи. Вне зависимости от того, есть ли между ними какие-то символы или нет.
- Этим способом можно задать ключ даже с отдельными буквами.
Фильтр
Вот как искать в Экселе, используя фильтр:
- Выделите какую-нибудь заполненную ячейку.
- Нажмите Главная — Сортировка — Фильтр.
- В верхней строке у клеток появятся стрелочки. Это выпадающее меню. Откройте его.
- В текстовом поле введите запрос и нажмите «OK».
- В столбце будут отображаться только ячейки, содержащие искомую фразу.
- Чтобы сбросить результаты, в выпадающем списке отметьте «Выделить всё».
- Чтобы отключить фильтр, повторно нажмите на него в сортировке.
Этот способ не подойдёт, если вы не знаете, в каком ряду нужное вам значение.
Чтобы найти в Excel какую-то фразу или число используйте встроенные возможности интерфейса. Можно выбрать дополнительные параметры поиска и включить фильтр.
Читайте также: