Как открыть поиск в экселе: Поиск или замена текста и чисел на листе

Поиск или замена текста и чисел на листе

Поиск

Чтобы найти что-либо, нажмите CTRL+Fили перейдите на home >редактирование> Найти & Выберите > Найти.

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

1. В поле «Найти:» введите текст или числа, которые вы хотите найти, или щелкните стрелку в поле «Найти:», а затем выберите последний элемент поиска из списка.

   Советы: В условиях поиска можно использовать подделавные знаки: вопросии(?),звездочки(*), тильды(~).

   • Используйте знак вопроса (?), чтобы найти любой символ — например, если найти «г?д», то будут «ы» и «множество».

   • Звездочка (*) используется для поиска любого количества символов. Например, если в этом примере будут найти «г*д», то будут «грусть» и «начато».

   • Воспользуйтесь тильдом (~), за которым следует ?, * или ~, чтобы найти вопросии, звездочки или другие символы тильды, например fy91~? находит «фг91?».

2. Нажмите кнопку «Найти все»или «Найти далее», чтобы выполнить поиск.

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

3. Нажмите кнопку «>>» для дальнейшего определения поискового запроса.

   • В пределах: Чтобы найти данные на листе или во всей книге, выберите «Лист»или «Книга».

   • Поиск: Вы можете выбрать поиск по строкам (по умолчанию) или по столбцам.

   • Посмотрите в:

     Чтобы найти данные с определенными сведениями, в поле щелкните «Формулы», «Значения», «Заметки»или «Примечания».

     Примечание:   Формулы,значения,заметки и примечания доступны только на вкладке «Найти». На вкладке «Заменить» доступны только формулы.

   • Совмещите

     case ( проверьте это, если вы хотите найти данные с конфиденциальной конфиденциаляцией).

   • Содержимому ячеек соответствует все содержимое: проверьте это, если вы хотите найти ячейки, содержащие только символы, которые вы введите в поле «Найти:».

4. Если вы хотите найти текст или числа с определенным форматированием, нажмите кнопку «Формат» и сделайте выбор в диалоговом окне «Найти формат».

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

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

Заменить

Чтобы заменить текст или числа, нажмите CTRL+Hили перейдите на home >редактирование> Найти & Выберите > Replace.

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

1. В поле «Найти:» введите текст или числа, которые вы хотите найти, или щелкните стрелку в поле «Найти:», а затем выберите последний элемент поиска из списка.

   Советы: В условиях поиска можно использовать подделавные знаки: вопросии(?),звездочки(*), тильды(~).

   • Используйте знак вопроса (?), чтобы найти любой символ — например, если найти «г?д», то будут «ы» и «множество».

   • Звездочка (*) используется для поиска любого количества символов. Например, если в этом примере будут найти «г*д», то будут «грусть» и «начато».

   • Воспользуйтесь тильдом (~), за которым следует ?, * или ~, чтобы найти вопросии, звездочки или другие символы тильды, например fy91~? находит «фг91?».

1. Нажмите Заменить все или Заменить.

   Совет: При нажатиикнопки «Заменить все» все вхождения ищемого условия заменяются, а замена обновляется по одному вхождению за раз.

2. Нажмите кнопку «>>» для дальнейшего определения поискового запроса.

   • В пределах: Чтобы найти данные на листе или во всей книге, выберите «Лист»или «Книга».

   • Поиск: Вы можете выбрать поиск по строкам (по умолчанию) или по столбцам.

   • Посмотрите в: Чтобы найти данные с определенными сведениями, в поле щелкните «Формулы», «Значения»,

     «Заметки»или «Примечания».

     Примечание:   Формулы,значения,заметки и примечания доступны только на вкладке «Найти». На вкладке «Заменить» доступны только формулы.

   • Совмещите case ( проверьте это, если вы хотите найти данные с конфиденциальной конфиденциаляцией).

   • Содержимому ячеек соответствует все содержимое: проверьте это, если вы хотите найти ячейки, содержащие только символы, которые вы введите в поле «Найти:».

3. Если вы хотите найти текст или числа с определенным форматированием, нажмите кнопку «Формат» и сделайте выбор в диалоговом окне «Найти формат».

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

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

Существует два разных способа поиска или замены текста или чисел на компьютере Mac. Первый из них — диалоговое окно «& заменить». Второй вариант — использование панели поиска на ленте.

Диалоговое окно & «Заменить»

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

Чтобы найти что-либо, нажмите CTRL+Fили перейдите на home >Find & Select > Find.

1. В поле «Найти:» введите текст или числа, которые нужно найти.

2. Нажмите кнопку «Найти далее», чтобы выполнить поиск.

3. При необходимости можно дополнительно определить поиск.

   • В пределах: Чтобы найти данные на листе или во всей книге, выберите «Лист»или «Книга».

   • Поиск: Вы можете выбрать поиск по строкам (по умолчанию) или по столбцам.

   • Посмотрите в: Чтобы найти данные с определенными сведениями, в поле щелкните «Формулы», «Значения», «Заметки»или «Примечания».

     Примечание:   Формулы,значения,заметки и примечания доступны только на вкладке «Найти». На вкладке «Заменить» доступны только формулы.

   • Совмещите case ( проверьте это, если вы хотите найти данные с конфиденциальной конфиденциаляцией).

   • Содержимому ячеек соответствует все содержимое: проверьте это, если вы хотите найти ячейки, содержащие только символы, которые вы введите в поле «Найти:».

Советы: В условиях поиска можно использовать подделавные знаки: вопросии(?),звездочки(*), тильды(~).

• Используйте знак вопроса (?), чтобы найти любой символ — например, если найти «г?д», то будут «ы» и «множество».

• Звездочка (*) используется для поиска любого количества символов. Например, если в этом примере будут найти «г*д», то будут «грусть» и «начато».

• Воспользуйтесь тильдом (~), за которым следует ?, * или ~, чтобы найти вопросии, звездочки или другие символы тильды, например fy91~? находит «фг91?».

Чтобы заменить текст или числа, нажмите CTRL+Hили перейдите на home >Find & Select > Replace.

1. В поле «Найти» введите текст или числа, которые нужно найти.

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

3. Нажмите кнопку «Заменить» или «Заменить все».

   Совет: При нажатиикнопки «Заменить все» все вхождения ищемого условия заменяются, а замена обновляется по одному вхождению за раз.

4. При необходимости можно дополнительно определить поиск.

   • В пределах: Чтобы найти данные на листе или во всей книге, выберите «Лист»или «Книга».

   • Поиск: Вы можете выбрать поиск по строкам (по умолчанию) или по столбцам.

   • Совмещите case ( проверьте это, если вы хотите найти данные с конфиденциальной конфиденциаляцией).

   • Содержимому ячеек соответствует все содержимое: проверьте это, если вы хотите найти ячейки, содержащие только символы, которые вы введите в поле «Найти:».

Советы: В условиях поиска можно использовать подделавные знаки: вопросии(?),звездочки(*), тильды(~).

• Используйте знак вопроса (?), чтобы найти любой символ — например, если найти «г?д», то будут «ы» и «множество».

• Звездочка (*) используется для поиска любого количества символов. Например, если в этом примере будут найти «г*д», то будут «грусть» и «начато».

• Воспользуйтесь тильдом (~), за которым следует ?, * или ~, чтобы найти вопросии, звездочки или другие символы тильды, например fy91~? находит «фг91?».

1. Щелкните любую ячейку для поиска на всем листе или выберите определенный диапазон ячеек.

2. Нажмите +F или щелкните значок, чтобы развернуть область поиска, а затем в поле поиска введите текст или число, которое нужно найти.

   Советы: В условиях поиска можно использовать подделавные знаки: вопросии(?),звездочки(*), тильды(~).

   • Используйте знак вопроса (?), чтобы найти любой символ — например, если найти «г?д», то будут «ы» и «множество».

   • Звездочка (*) используется для поиска любого количества символов. Например, если в этом примере будут найти «г*д», то будут «грусть» и «начато».

   • Воспользуйтесь тильдом (~), за которым следует ?, * или ~, чтобы найти вопросии, звездочки или другие символы тильды, например fy91~? находит «фг91?».

3. Нажмите клавишу RETURN.

4. Чтобы найти следующий экземпляр ищите элемент, щелкните в поле поиска и нажмите кнопку RETURNили в диалоговом окне «Найти» нажмите кнопку «Найти далее».

   Совет: Поиск можно отменить, нажав клавишу ESC.

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

1. Щелкните любую ячейку для поиска на всем листе или выберите определенный диапазон ячеек.

2. Нажмите +F, чтобы развернуть область поиска, а затем в поле поиска введите текст или число, которое нужно найти.

   Советы: В условиях поиска можно использовать подделавные знаки: вопросии(?),звездочки(*), тильды(~).

   • Используйте знак вопроса (?), чтобы найти любой символ — например, если найти «г?д», то будут «ы» и «множество».

   • Звездочка (*) используется для поиска любого количества символов. Например, если в этом примере будут найти «г*д», то будут «грусть» и «начато».

   • Воспользуйтесь тильдом (~), за которым следует ?, * или ~, чтобы найти вопросии, звездочки или другие символы тильды, например fy91~? находит «фг91?».

3. В поле поиска щелкните значок лупы и выберите команду Заменить.

4. В поле Заменить на введите замещающие символы.

   Чтобы заменить символы в поле «Найти» пустыми, оставьте поле «Заменить на» пустым.

5. Нажмите кнопку Найти далее.

6. Чтобы заменить выделенное вхождение, нажмите кнопку Заменить.

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

   Совет: Отмените поиск, просто нажав клавишу ESC.

7. Чтобы задать дополнительные параметры поиска, щелкните увеличительное стекло и выберите «Поиск на листе» или «Поиск в книге». Вы также можете выбрать параметр «Дополнительные параметры», чтобы запустить диалоговое окно «Замена».

Поиск

Чтобы найти что-либо, нажмите CTRL+Fили перейдите на home >редактирование> Найти & Выберите > Найти.

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

1. В поле «Найти:» введите текст или числа, которые нужно найти.

   Советы: В условиях поиска можно использовать подделавные знаки: вопросии(?),звездочки(*), тильды(~).

   • Используйте знак вопроса (?), чтобы найти любой символ — например, если найти «г?д», то будут «ы» и «множество».

   • Звездочка (*) используется для поиска любого количества символов. Например, если в этом примере будут найти «г*д», то будут «грусть» и «начато».

   • Воспользуйтесь тильдом (~), за которым следует ?, * или ~, чтобы найти вопросии, звездочки или другие символы тильды, например fy91~? находит «фг91?».

2. Нажмите кнопку «Найти далее»или «Найти все», чтобы выполнить поиск.

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

3. Щелкните > параметры поиска, чтобы при необходимости определить дополнительные параметры поиска.

   • В пределах: Чтобы найти данные в пределах определенного выделения, выберите «Выделение». Чтобы найти данные на листе или во всей книге, выберите «Лист»или «Книга».

   • Направление: Вы можете выбрать поиск в области «Вниз» (по умолчанию) или «Вверх».

   • Совмещите case ( проверьте это, если вы хотите найти данные с конфиденциальной конфиденциаляцией).

   • Содержимому ячеек соответствует все содержимое: проверьте это, если вы хотите найти ячейки, содержащие только символы, которые вы введите в поле «Найти».

Заменить

Чтобы заменить текст или числа, нажмите CTRL+Hили перейдите на home >редактирование> Найти & Выберите > Replace.

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

1. В поле «Найти:» введите текст или числа, которые нужно найти.

   Советы: В условиях поиска можно использовать подделавные знаки: вопросии(?),звездочки(*), тильды(~).

   • Используйте знак вопроса (?), чтобы найти любой символ — например, если найти «г?д», то будут «ы» и «множество».

   • Звездочка (*) используется для поиска любого количества символов. Например, если в этом примере будут найти «г*д», то будут «грусть» и «начато».

   • Воспользуйтесь тильдом (~), за которым следует ?, * или ~, чтобы найти вопросии, звездочки или другие символы тильды, например fy91~? находит «фг91?».

2. В поле «Заменить на:» введите текст или числа, которые нужно использовать для замены текста поиска.

3. Нажмите кнопку «Заменить» или «Заменить все».

   Совет: При нажатиикнопки «Заменить все» все вхождения ищемого условия заменяются, а замена обновляется по одному вхождению за раз.

4. Щелкните > параметры поиска, чтобы при необходимости определить дополнительные параметры поиска.

   • В пределах: Чтобы найти данные в пределах определенного выделения, выберите «Выделение». Чтобы найти данные на листе или во всей книге, выберите «Лист»или «Книга».

   • Направление: Вы можете выбрать поиск в области «Вниз» (по умолчанию) или «Вверх».

   • Совмещите case ( проверьте это, если вы хотите найти данные с конфиденциальной конфиденциаляцией).

   • Содержимому ячеек соответствует все содержимое: проверьте это, если вы хотите найти ячейки, содержащие только символы, которые вы введите в поле «Найти».

Строка поиска в Excel — Информационные технологии

Уроки MS Excel

Работая с таблицами Excel, иногда возникает необходимость в распределении информации из одного столбца по

Уроки MS Excel

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

Уроки MS Excel

Нередко пользователям приходится перенести часть информации с документа Microsoft Word в Excel формат, чтобы

Уроки MS Excel

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

Уроки MS Excel

Пользователю Excel нередко приходится сталкиваться с тем, чтобы определять, сколько строк содержит таблица. Чтобы

Уроки MS Excel

Excel – одна из лучших программ для аналитика данных. А почти каждому человеку на

Уроки MS Excel

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

Уроки MS Excel

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

Уроки MS Excel

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

Уроки MS Excel

Определение процента от числа – довольно частая задача, с которой приходится сталкиваться пользователю Ecxel,

Уроки MS Excel

Excel – невероятно функциональная программа. Она может использоваться и в качестве некого подобия среды

Уроки MS Excel

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

Уроки MS Excel

Стандартное обозначение строк в Excel – цифровое. Если же речь идет о столбцах, то

Уроки MS Excel

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

Уроки MS Excel

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

Уроки MS Excel

Важно понимать, что Excel – это не только программа для создания баз данных, но

Как правильно искать в таблицах Excel — Трюки и приемы в Microsoft Excel

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

Выполните команду Главная ► Редактирование ► Найти и выделить ► Найти (или нажмите Ctrl+F), чтобы открыть диалоговое окно Найти и заменить. Если вам нужно заменить данные, то выберите команду Главная ► Редактирование ► Найти и выделить ► Заменить (или нажмите Ctrl+H). От того, какую именно команду вы выполните, зависит, на какой из двух вкладок откроется диалоговое окно.

Если в открывшемся диалоговом окне Найти и заменить нажать кнопку Параметры, то отобразятся дополнительные параметры поиска информации (рис. 21.1).

Рис. 21.1. Вкладка Найти диалогового окна Найти и заменить

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

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

При поиске с помощью диалогового окна Найти и заменить можно использовать два подстановочных знака:

• ? — соответствует любому символу;
• * — соответствует любому количеству символов.

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

Для поиска вопросительного знака или звездочки поставьте перед ними символ тильды (~).
Например, следующая строка поиска находит текст *NONE*: -*N0NE~*
Чтобы найти символ тильды, поставьте в строке поиска две тильды.

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

• Флажок Учитывать регистр — установите его, чтобы регистр искомого текста совпадал с регистром заданного текста. Например, если вы зададите в поиске слово иван и установите указанный флажок, то слово Иван в результатах поиска не отобразится.
• Флажок Ячейка целиком — установите его, чтобы найти ячейку, которая содержит в точности тот текст, который указан в строке поиска. Например, набрав в строке поиска слово Excel и установив указанный флажок, вы не найдете ячейку, содержащую словосочетание Microsoft Excel.
• Раскрывающийся список Область поиска — список содержит три пункта: значения, формулы и примечания. Например, если в строке поиска вы зададите число 900 и в раскрывающемся списке Область поиска выберете пункт значения, то в результатах поиска вы не увидите ячейку, содержащую значение 900, если оно получено при использовании формулы.

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

Кроме того, учтите, что с помощью окна Найти и заменить нельзя найти отформатированные числовые значения. Например, если в строку поиска вы введете $5*, то значение, к которому применено денежное форматирование и которое выглядит как $54.00, не будет найдено.

Работа с датами может оказаться непростой, поскольку Excel поддерживает очень много форматов дат. Если вы ищете дату, к которой применено форматирование по умолчанию, Excel находит даты, даже если они отформатированы различными способами. Например, если ваша система использует формат даты m/d/y, строка поиска 10/*/2010 находит все даты в октябре 2010 года, независимо от того, как они отформатированы.

Используйте пустое поле Заменить на, чтобы быстро удалить какую-нибудь информацию на рабочем листе. Например, введите — * в поле Найти и оставьте поле Заменить на пустым. Затем нажмите кнопку Заменить все, чтобы Excel нашел и убрал все звездочки на листе.

Поиск в Эксель: способы нахождения нужного слова

На чтение 5 мин. Опубликовано 25/07/2017

Основное назначение офисной программы Excel – осуществление расчётов. Документ этой программы (Книга) может содержать много листов с длинными таблицами, заполненными числами, текстом или формулами. Автоматизированный быстрый поиск позволяет найти в них необходимые ячейки.

Простой поиск

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

В простейшем случае в появившемся окне «Найти и заменить» надо ввести искомое значение и щёлкнуть «Найти всё».

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

Аналогично производится поиск текста. В этом случае в строке поиска набирается искомый текст.

Если данные или текст ищется не во всей экселевской таблице, то область поиска предварительно должна быть выделена.

Расширенный поиск

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

Анализируя результаты произведённого поиска, можно отметить, что, наряду с правильными 9 результатами, программа также выдала неожиданные, подчёркнутые красным. Они связаны с наличием в ячейке или формуле цифры 3.

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

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

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

Для того чтобы избавиться от единственного оставшегося неправильного результата на первой строчке, в окне расширенного поиска нужно выбрать пункт «Ячейка целиком». После этого результат поиска становимся точным на 100%.

Такой результат можно было бы обеспечить, сразу выбрав пункт «Ячейка целиком» (даже оставив в «Области поиска» значение «Формулы»).

Теперь обратимся к пункту «Искать».

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

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

При поиске в документах Microsoft Excel, можно использовать и другой подстановочный знак – «*». Если рассмотренный «?» означал любой символ, то «*» заменяет собой не один, а любое количество символов. Ниже представлен скриншот поиска по слову Louisiana.

Иногда при поиске необходимо учитывать регистр символов. Если слово louisiana будет написано с маленькой буквы, то результаты поиска не изменятся. Но если в окне расширенного поиска выбрать «Учитывать регистр», то поиск окажется безуспешным. Программа станет считать слова Louisiana и louisiana разными, и, естественно, не найдёт первое из них.

Разновидности поиска

Поиск совпадений

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

Результат представлен на скриншоте ниже.

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

Фильтрация

Другая разновидность поиска – фильтрация. Предположим, что пользователь хочет в столбце B найти числовые значения в диапазоне от 3000 до 4000.

1. Выделить первый столбец с заголовком.
2. На той же вкладке «Главная» в разделе «Редактирование» открыть инструмент «Сортировка и фильтр», и щёлкнуть пункт «Фильтр».
3. В верхней строчке столбца B появляется треугольник – условный знак списка. После его открытия в списке «Числовые фильтры» щёлкнуть пункт «между».
4. В окне «Пользовательский автофильтр» следует ввести начальное и конечное значение плюс OK.

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

Различные варианты поиска были рассмотрены на примере Excel 2010. Как сделать поиск в эксель других версий? Разница в переходе к фильтрации есть в версии 2003. В меню «Данные» следует последовательно выбрать команды «Фильтр», «Автофильтр», «Условие» и «Пользовательский автофильтр».

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

Как в Excel найти нужное слово

В Microsoft Excel часто работают с большими объёмами информации. В нём создаются огромные таблицы на тысячи строк, столбцов и позиций. Отыскать какие-то конкретные данные в таком массиве бывает трудно. А иногда — вообще невозможно. Эту задачу можно упростить. Разберитесь, как в Экселе найти нужное слово. Так вам легче будет ориентироваться в документе. И вы сможете быстро перейти к искомой информации.

В редакторе таблиц существует несколько способов поиска

Поиск ячеек

Чтобы отобразились адреса всех ячеек, в которых есть то, что вы ищите, сделайте следующее:

1. Если у вас Office 2010, перейдите в Меню — Правка — Найти.
2. Откроется окно с полем для ввода. Напишите в нём фразу для поиска.
3. В Excel 2007 эта кнопка есть в меню «Главная» на панели «Редактирование». Она находится справа.
4. Аналогичного результата во всех версиях можно добиться, если нажать клавиши Ctrl+F.
5. В поле напечатайте слово, фразу или цифры, которые надо отыскать.
6. Нажмите «Найти всё», чтобы запустить поиск по целому документу. Если кликните «Далее», программа будет по одной выделять клетки, которые находятся ниже курсора-ячейки Excel.
7. Подождите, пока процесс закончится. Чем больше документ, тем дольше система будет искать.
8. Появится список с результатами: именами и адресами клеток, в которых есть совпадения с заданной фразой, и текстом, который в них написан.
9. При клике на каждую строчку будет выделяться соответствующая ячейка.
10. Для удобства можете «растянуть» окно. Так в нём будет видно больше строчек.
11. Чтобы отсортировать данные, кликайте на названия столбцов над результатами поиска. Если нажмёте на «Лист», строчки выстроятся по алфавиту в зависимости от названия листа, если выберите «Значения» — встанут по значению.
12. Эти столбцы также «растягиваются».

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

Можете задать свои условия. Например, запустить поиск по нескольким знакам. Вот как в Экселе найти слово, которое вы не помните целиком:

1. Введите только часть надписи. Можно хоть одну букву — будут выделены все места, в которых она есть.
2. Используйте символы * (звёздочка) и ? (вопросительный знак). Они замещают пропущенные знаки.
3. Вопрос обозначает одну отсутствующую позицию. Если вы напишите, к примеру, «П???», отобразятся ячейки, в которых есть слово из четырёх символов, начинающееся на «П»: «Плуг», «Поле», «Пара» и так далее.
4. Звезда (*) замещает любое количество знаков. Чтобы отыскать все значения, в которых содержится корень «раст», начните поиск по ключу «*раст*».

Также вы можете зайти в настройки:

1. В окне «Найти» нажмите «Параметры».
2. В разделах «Просматривать» и «Область поиска», укажите, где и по каким критериям надо искать совпадения. Можно выбрать формулы, примечания или значения.
3. Чтобы система различала строчные и прописные буквы, поставьте галочку в «Учитывать регистр».
4. Если вы отметите пункт «Ячейка целиком», в результатах появятся клетки, в которых есть только заданная поисковая фраза и ничего больше.

Параметры формата ячеек

Чтобы отыскать значения с определённой заливкой или начертанием, используйте настройки. Вот как найти в Excel слово, если оно имеет отличный от остального текста вид:

1. В окне поиска нажмите «Параметры» и кликните на кнопку «Формат». Откроется меню с несколькими вкладками.
2. Можете указать определённый шрифт, вид рамки, цвет фона, формат данных. Система будет просматривать места, которые подходят к заданным критериям.
3. Чтобы взять информацию из текущей клетки (выделенной в этот момент), нажмите «Использовать формат этой ячейки». Тогда программа отыщет все значения, у которых тот же размер и вид символов, тот же цвет, те же границы и тому подобное.

Поиск нескольких слов

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

Чтобы в Экселе найти не одно слово, а сразу несколько, сделайте следующее:

1. Напишите их в строке поиска.
2. Поставьте между ними звёздочки. Получится «*Текст* *Текст2* *Текст3*». Так отыщутся все значения, содержащие указанные надписи. Вне зависимости от того, есть ли между ними какие-то символы или нет.
3. Этим способом можно задать ключ даже с отдельными буквами.

Фильтр

Вот как искать в Экселе, используя фильтр:

1. Выделите какую-нибудь заполненную ячейку.
2. Нажмите Главная — Сортировка — Фильтр.
3. В верхней строке у клеток появятся стрелочки. Это выпадающее меню. Откройте его.
4. В текстовом поле введите запрос и нажмите «OK».
5. В столбце будут отображаться только ячейки, содержащие искомую фразу.
6. Чтобы сбросить результаты, в выпадающем списке отметьте «Выделить всё».
7. Чтобы отключить фильтр, повторно нажмите на него в сортировке.

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

Чтобы найти в Excel какую-то фразу или число используйте встроенные возможности интерфейса. Можно выбрать дополнительные параметры поиска и включить фильтр.

Горячие клавиши Microsoft Excel для MacOS

F2	Редактировать выбранную ячейку
⌘ + T или Control + T	Создать таблицу
⌘ + Option + Return или Contol + Option + Enter	Вставить разрыв строки в ячейке
Control + Пробел	Вставить специальные символы
⌘ + Shift + >	Увеличить размер шрифта
⌘ + Shift +	Уменьшить размер шрифта
⌘ + E	Выровнять по центру
⌘ + L	Выровнять по левому краю
⌘ + Shift + L	Отобразить диалоговое окно «Изменить стиль ячейки»
⌘ + 1	Отобразить диалоговое окно «Формат ячеек»
Control + Shift + ~	Применить общий числовой формат
Control + Shift + $	Применить формат валюты с двумя десятичными знаками
Control + Shift + %	Применить формат в процентах без десятичных знаков
Control + Shift + ^	Применить экспоненциальный числовой формат с двумя десятичными знаками
Control + Shift + #	Применить формат даты с указанием дня, месяца и года
Control + Shift + @	Применить формат времени с часами и минутами
⌘ + Option + 0	Применить контурную границу вокруг выделенных ячеек
⌘ + Option + Стрелка вправо	Добавить контурную границу справа от выделения
⌘ + Option + Стрелка влево	Добавить контурную границу слева от выделения
⌘ + Option + Стрелка вверх	Добавить контурную границу в верхнюю часть выделения
⌘ + Option + Стрелка вниз	Добавить контурную границу внизу выделения
⌘ + Option + —	Удалить границы контура
⌘ + B	Применить или удалить жирный формат
⌘ + I	Применить или удалить курсивный формат
⌘ + U	Применить или удалить подчеркивание
⌘ + Shift + X	Применить или удалить зачеркнутый формат
⌘ + ) или Control + )	Скрыть столбец
⌘ + Shift + ) или Control + Shift + )	Показать столбец
⌘ + ( или Control + (	Скрыть строку
⌘ + Shift + ( или Control + Shift + (	Показать строку
Control + U	Редактировать активную ячейку
⌘ + V	Вставить текст в активную ячейку
Enter	Вставить запись в ячейку

Приемы :: Планета Excel

Распределяем список по наборам

Как разделить список по наборам неодинакового размера — разбор 3 способов: вручную через сводную, запросом в Power Query и функциями динамических массивов.

Вафельная диаграмма в Excel

2 способа построить в Excel вафельную диаграмму (waffle-chart) для наглядной визуализации прогресса по проекту или любых других KPI.

Массовая замена текста формулами

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

Регулярные выражения (RegExp) в Power Query

Как добавить поддержку регулярных выражений (RegExp) в Power Query для реализации поиска и извлечения фрагментов текста по гибким шаблонам и маскам.

Производственный календарь в Excel

Как при помощи Power Query сделать в Excel «вечный» производственный календарь — автоматически обновляющийся список нерабочих дней за все годы для использования в своих расчетах.

ВПР и числа-как-текст

Как научить функцию ВПР (VLOOKUP) искать значения, когда в исходных данных встречаются «числа-как-текст», что приводит к ошибкам #Н/Д.

Ад Условного Форматирования

Что такое «Ад Условного Форматирования», когда и почему он возникает? Как с ним бороться вручную и при помощи специального макроса?

Самый быстрый ВПР

Тест скорости 7 разных вариантов реализации поиска и подстановки данных из одной таблицы в другую: ВПР, ИНДЕКС+ПОИСКПОЗ, ПРОСМОТРХ, СУММЕСЛИ и т.д. Кто будет самым быстрым?

Выпадающий список с быстрым поиском

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

Динамические массивы в Excel

Подробный разбор революционно нового инструмента Excel — динамических массивов (Dynamic Arrays). Логика их работы, нюансы, плюсы и минусы, совместимость со старыми версиями.

Как открыть новый Excel в отдельном окне

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

Дубликаты внутри ячейки

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

Суперсила Мгновенного заполнения (Flash Fill)

Подробный разбор вариантов применения одного из самых удивительных, но малоизвестных инструментов — Мгновенного заполнения (Flash Fill). Поможет для обработки текста: нарезки, склейки, исправления регистра, добавления или удаления слов, извлечения чисел из текста и многого другого.

Генератор фраз из заданных фрагментов

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

Параметризация путей к данным в Power Query

При пересылке файлов с запросами Power Query они очень часто перестают работать, т.к. путь к исходным данным на другом ПК меняется. Как решить эту проблему и превратить путь в параметр (переменную), чтобы наш запрос работал на любом компьютере?

Запуск макроса по времени

Как запускать нужные вам макросы в определенное время, с определенной частотой или по расписанию? Например, автоматически обновлять тяжелый и медленный отчет каждое утро в 5:00?

Новые типы данных в Excel 2016

Подробный разбор особенностей использования двух новых типов данных География (Geography) и Акции (Stocks), появившихся в Microsoft Excel 2016.

Нечеткий текстовый поиск с Fuzzy Lookup в Excel

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

Последнее слово

Как извлечь из текста последнее слово или фрагмент по заданному символу-разделителю. Разбор нескольких способов (формулы, макросы, Power Query).

Как использовать Личную Книгу Макросов

Личная Книга Макросов (Personal Macro Workbook) — что это такое, как ее создать и использовать в качестве личной библиотеки для хранения и запуска самых нужных вам в повседневной работе макросов и функций.

Диаграмма Ганта в Power Query

Как с помощью Power Query быстро «размазать» бюджет по дням в списке проектов, построив упрощенный вариант диаграммы Ганта в Excel.

Анализ текста регулярными выражениями (RegExp) в Excel

Как с помощью регулярных выражений (Regular Expression = RegExp) находить и извлекать из текста фрагменты по заданным сложным шаблонам. Например, вытащить номер счета из описания платежа, город из адреса, телефон из контактов клиента и т. п.

Расчет средневзвешенного значения в Excel

Что такое средневзвешенное значение и чем оно отличается от обычного среднего арифметического. Как вычислить средневзвешенное в Excel с помощью формул и в сводной таблице (в том числе с помощью DAX и Power Pivot).

Наглядность в сложных формулах

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

Сравнение двух таблиц

Как сравнить между собой две таблицы (прайс-листа) и наглядно отобразить отличия между ними — добавленные и удаленные товары, изменение цены и т.д. Разбор 3 способов: с помощью функции ВПР (VLOOKUP), сводной таблицей и через надстройку Power Query.

Трансформация столбца в таблицу

Три способа (формулы, Power Query и макросы) для трансформирования длинного столбца с чередующимися данными в полноценную двумерную таблицу, с которой можно работать.

Самая длинная победная серия

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

Как искать в Excel

Есть несколько способов поиска в Excel. Первые варианты — если у вас есть большая электронная таблица данных и вам нужно найти определенный фрагмент данных в ячейке или группе ячеек.

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

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

Использование функции «Найти в Excel»

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

1. Выберите меню Home . В группе «Редактирование» на ленте выберите Найти и выделить , затем выберите Найти .

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

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

• Найдите то, что : введите текст или число, которое вы хотите найти
• Формат : нажмите эту кнопку, чтобы искать только текст с форматированием, определенным в окне «Найти формат».
• В пределах : выберите Лист или Рабочая книга для поиска внутри текущего листа или по всей книге
• Поиск : выберите По строкам или По столбцам , чтобы выполнять поиск по строкам или по столбцам (и остановитесь на этой строке или столбце, когда найдет результат).
• Искать в : Вы можете углубить поиск в другие области вашей электронной таблицы, указав поиску искать в Формулы , Значения , Примечания или Комментарии
• Соответствие регистру : Это приведет к поиск с использованием совпадения текста с учетом регистра
• Соответствие всему содержимому ячейки : будет выполняться поиск ячеек, которые содержат только вводимый вами текст и ничего больше

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

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

Подстановочные знаки поиска в Excel

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

• Знак вопроса (?) : Замените любой отдельный символ или цифру. Например, введите c? T , чтобы найти любое трехбуквенное слово, которое начинается с c и заканчивается на t, например cat, cut или cot.
• Звездочка (*) : Заменить части слов. Например, введите c * t , чтобы найти слово любой длины из корзины и привести к счету и суду.
• Тильда (~) : Используйте этот символ, чтобы переопределить специальные символы, указанные выше. Например, если вы действительно хотите найти слово «пользователь?» (включая вопросительный знак) вы можете ввести пользователя ~? и это будет отменять спец? символ для поиска, включая вопросительный знак.

Использование функции «Заменить в Excel

»

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

Это окно также можно открыть на вкладке «Заменить», выбрав меню Home , выбрав Найти и заменить в группе «Редактирование» на ленте, а затем выбрав Заменить . Как и в предыдущем разделе, вам нужно будет выбрать Параметры , чтобы увидеть все функции расширенного поиска.

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

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

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

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

Использование функций для поиска в Excel

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

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

• VLookup: поиск данных в одном столбце таблицы для возврата значения другого столбца в той же строке.
• Index-Match: Выполняет то же самое, что и VLookup, но данные поиска не обязательно должны быть крайним левым столбцом в таблице поиска.
• Фильтры: вы можете использовать фильтры, чтобы отфильтровать данные, которые вам не нужны, и отображать только те данные, которые вы ищете.
• Найти совпадающие значения. В Excel есть несколько функций, которые позволяют находить повторяющиеся значения в Excel, например «Точное и совпадение».

Поиск данных в Excel не должен быть сложным. Если вы знаете, что ищете и что хотите делать с данными, есть возможность выполнить поиск.

Найти или заменить текст и числа на листе

Найдите

Чтобы что-то найти, нажмите Ctrl + F или перейдите на страницу Домашняя страница > Редактирование > Найдите и выберите > Найдите .

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

1. В поле Найти что: введите текст или числа, которые нужно найти, или щелкните стрелку в поле Найти что: , а затем выберите в списке элемент недавнего поиска.

   Советы: Вы можете использовать подстановочных знаков — вопросительный знак (? ), звездочку ( * ), тильду ( ~ ) — в критериях поиска.

   • Используйте вопросительный знак (?), Чтобы найти любой одиночный символ — например, s? T находит «sat» и «set».

   • Используйте звездочку (*), чтобы найти любое количество символов — например, s * d находит «грустный» и «начатый».

   • Используйте тильду (~), за которой следует?, * Или ~, чтобы найти вопросительные знаки, звездочки или другие символы тильды — например, fy91 ~? находит «fy91?».

2. Щелкните Найти все или Найти далее , чтобы начать поиск.

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

3. Нажмите Параметры >> , чтобы при необходимости определить параметры поиска:

   • Внутри: Для поиска данных на листе или во всей книге выберите Лист или Рабочая книга .

   • Поиск: Вы можете выбрать поиск: По строкам (по умолчанию) или По столбцам .

   • Искать в: Для поиска данных с конкретными подробностями в поле щелкните Формулы , Значения , Примечания или Комментарии .

     Примечание: Формулы , Значения , Примечания и Комментарии доступны только на вкладке Найти ; только Формулы доступны на вкладке Заменить .

   • Учитывать регистр — Установите этот флажок, если хотите искать данные с учетом регистра.

   • Соответствовать всему содержимому ячейки — установите этот флажок, если хотите искать ячейки, содержащие только символы, введенные в поле Найти: .

4. Если вы хотите найти текст или числа с определенным форматированием, щелкните Формат , а затем сделайте выбор в диалоговом окне Найти формат .

   Совет: Если вы хотите найти ячейки, которые просто соответствуют определенному формату, вы можете удалить любые критерии в поле Найти , а затем выбрать конкретный формат ячейки в качестве примера. Щелкните стрелку рядом с полем Формат , нажмите Выбрать формат из ячейки , а затем щелкните ячейку с форматированием, которое вы хотите найти.

Заменить

Чтобы заменить текст или числа, нажмите Ctrl + H или перейдите на Домашняя страница > Редактирование > Найти и выбрать > Заменить .

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

1. В поле Найти что: введите текст или числа, которые нужно найти, или щелкните стрелку в поле Найти что: , а затем выберите в списке элемент недавнего поиска.

   Советы: Вы можете использовать подстановочных знаков — вопросительный знак (? ), звездочку ( * ), тильду ( ~ ) — в критериях поиска.

   • Используйте вопросительный знак (?), Чтобы найти любой одиночный символ — например, s? T находит «sat» и «set».

   • Используйте звездочку (*), чтобы найти любое количество символов — например, s * d находит «грустный» и «начатый».

   • Используйте тильду (~), за которой следует?, * Или ~, чтобы найти вопросительные знаки, звездочки или другие символы тильды — например, fy91 ~? находит «fy91?».

org/ItemList»>

1. Нажмите Заменить все или Заменить .

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

2. Нажмите Параметры >> , чтобы при необходимости определить параметры поиска:

   • Внутри: Для поиска данных на листе или во всей книге выберите Лист или Рабочая книга .

   • Поиск: Вы можете выбрать поиск: По строкам (по умолчанию) или По столбцам .

   • Искать в: Для поиска данных с конкретными подробностями в поле щелкните Формулы , Значения , Примечания или Комментарии .

     Примечание: Формулы , Значения , Примечания и Комментарии доступны только на вкладке Найти ; только Формулы доступны на вкладке Заменить .

   • Учитывать регистр — Установите этот флажок, если хотите искать данные с учетом регистра.

   • Соответствовать всему содержимому ячейки — установите этот флажок, если хотите искать ячейки, содержащие только символы, введенные в поле Найти: .

3. Если вы хотите найти текст или числа с определенным форматированием, щелкните Формат , а затем сделайте выбор в диалоговом окне Найти формат .

   Совет: Если вы хотите найти ячейки, которые просто соответствуют определенному формату, вы можете удалить любые критерии в поле Найти , а затем выбрать конкретный формат ячейки в качестве примера.Щелкните стрелку рядом с полем Формат , нажмите Выбрать формат из ячейки , а затем щелкните ячейку с форматированием, которое вы хотите найти.

Существует два различных метода поиска или замены текста или чисел на Mac. Первый — использовать диалоговое окно «Найти и заменить ». Второй — использовать строку поиска на ленте.

Чтобы что-то найти, нажмите Ctrl + F или перейдите на страницу Home > Найдите и выберите > Найдите .

1. В поле Find what: введите текст или числа, которые вы хотите найти.

2. Нажмите Найти далее , чтобы начать поиск.

3. org/ListItem»>

   При необходимости вы можете определить дальнейший поиск:

   • Внутри: Для поиска данных на листе или во всей книге выберите Лист или Рабочая книга .

   • Поиск: Вы можете выбрать поиск: По строкам (по умолчанию) или По столбцам .

   • Искать в: Для поиска данных с конкретными подробностями в поле щелкните Формулы , Значения , Примечания или Комментарии .

     Примечание: Формулы , Значения , Примечания и Комментарии доступны только на вкладке Найти ; только Формулы доступны на вкладке Заменить .

   • Учитывать регистр — Установите этот флажок, если хотите искать данные с учетом регистра.

   • Соответствовать всему содержимому ячейки — установите этот флажок, если хотите искать ячейки, содержащие только символы, введенные в поле Найти: .

Советы: Вы можете использовать подстановочных знаков — вопросительный знак (? ), звездочку ( * ), тильду ( ~ ) — в критериях поиска.

• Используйте вопросительный знак (?), Чтобы найти любой одиночный символ — например, s? T находит «sat» и «set».

• Используйте звездочку (*), чтобы найти любое количество символов — например, s * d находит «грустный» и «начатый».

• Используйте тильду (~), за которой следует?, * Или ~, чтобы найти вопросительные знаки, звездочки или другие символы тильды — например, fy91 ~? находит «fy91?».

Чтобы заменить текст или числа, нажмите Ctrl + H или перейдите на Домашняя страница > Найти и выбрать > Заменить .

1. В поле Найдите то, что введите текст или числа, которые вы хотите найти.

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

3. Нажмите Заменить или Заменить все .

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

4. При необходимости вы можете определить дальнейший поиск:

   • Внутри: Для поиска данных на листе или во всей книге выберите Лист или Рабочая книга .

   • Поиск: Вы можете выбрать поиск: По строкам (по умолчанию) или По столбцам .

   • Учитывать регистр — Установите этот флажок, если хотите искать данные с учетом регистра.

   • Соответствовать всему содержимому ячейки — установите этот флажок, если хотите искать ячейки, содержащие только символы, введенные в поле Найти: .

Советы: Вы можете использовать подстановочных знаков — вопросительный знак (? ), звездочку ( * ), тильду ( ~ ) — в критериях поиска.

• Используйте вопросительный знак (?), Чтобы найти любой одиночный символ — например, s? T находит «sat» и «set».

• Используйте звездочку (*), чтобы найти любое количество символов — например, s * d находит «грустный» и «начатый».

• Используйте тильду (~), за которой следует?, * Или ~, чтобы найти вопросительные знаки, звездочки или другие символы тильды — например, fy91 ~? находит «fy91?».

1. Щелкните любую ячейку, чтобы выполнить поиск по всему листу, или выберите определенный диапазон ячеек для поиска.

2. Пресс + F или щелкните значок, чтобы развернуть панель поиска, затем в поле поиска , введите текст или число, которое хотите найти.

   Советы: Вы можете использовать подстановочных знаков — вопросительный знак (? ), звездочку ( * ), тильду ( ~ ) — в критериях поиска.

   • Используйте вопросительный знак (?), Чтобы найти любой одиночный символ — например, s? T находит «sat» и «set».

   • Используйте звездочку (*), чтобы найти любое количество символов — например, s * d находит «грустный» и «начатый».

   • Используйте тильду (~), за которой следует?, * Или ~, чтобы найти вопросительные знаки, звездочки или другие символы тильды — например, fy91 ~? находит «fy91?».

3. Нажмите кнопку Возврат .

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

   Совет: Вы можете отменить текущий поиск, нажав ESC .

5. Чтобы указать дополнительные параметры поиска, щелкните увеличительное стекло, а затем щелкните Search in Sheet или Search in Workbook . Вы также можете выбрать опцию Advanced , которая запустит диалоговое окно Find .

1. Щелкните любую ячейку, чтобы выполнить поиск по всему листу, или выберите определенный диапазон ячеек для поиска.

2. Пресс + F, чтобы развернуть панель поиска, затем в поле поиска , введите текст или число, которое хотите найти.

   Советы: Вы можете использовать подстановочных знаков — вопросительный знак (? ), звездочку ( * ), тильду ( ~ ) — в критериях поиска.

   • Используйте вопросительный знак (?), Чтобы найти любой одиночный символ — например, s? T находит «sat» и «set».

   • Используйте звездочку (*), чтобы найти любое количество символов — например, s * d находит «грустный» и «начатый».

   • Используйте тильду (~), за которой следует?, * Или ~, чтобы найти вопросительные знаки, звездочки или другие символы тильды — например, fy91 ~? находит «fy91?».

3. В поле поиска щелкните увеличительное стекло, а затем щелкните Заменить .

4. org/ListItem»>

   В поле Заменить на введите заменяющие символы.

   Чтобы заменить символы в поле Найдите то, что поле пустым, оставьте поле Заменить на пустым.

5. Нажмите Найти далее .

6. Чтобы заменить выделенное вхождение, нажмите Заменить .

   Чтобы заменить все вхождения символов на листе без предварительного просмотра, нажмите Заменить все .

   Совет: Отмените текущий поиск, просто нажав ESC .

7. org/ListItem»>

   Чтобы указать дополнительные параметры поиска, щелкните увеличительное стекло, а затем щелкните Search in Sheet или Search in Workbook .Вы также можете выбрать опцию Advanced , которая запустит диалоговое окно Replace .

Найдите

Чтобы что-то найти, нажмите Ctrl + F или перейдите на страницу Домашняя страница > Редактирование > Найдите и выберите > Найдите .

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

1. org/ListItem»>

   В поле Find what: введите текст или числа, которые вы хотите найти.

   Советы: Вы можете использовать подстановочных знаков — вопросительный знак (? ), звездочку ( * ), тильду ( ~ ) — в критериях поиска.

   • Используйте вопросительный знак (?), Чтобы найти любой одиночный символ — например, s? T находит «sat» и «set».

   • Используйте звездочку (*), чтобы найти любое количество символов — например, s * d находит «грустный» и «начатый».

   • Используйте тильду (~), за которой следует?, * Или ~, чтобы найти вопросительные знаки, звездочки или другие символы тильды — например, fy91 ~? находит «fy91?».

2. org/ListItem»>

   Нажмите Найти далее или Найти все , чтобы начать поиск.

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

3. Нажмите > Параметры поиска , чтобы при необходимости определить параметры поиска:

   • Внутри: Для поиска данных в определенном выделении выберите Выбор . Чтобы найти данные на листе или во всей книге, выберите Лист или Рабочая книга .

   • Направление: Вы можете выбрать для поиска Вниз (по умолчанию) или Вверх .

   • Учитывать регистр — Установите этот флажок, если хотите искать данные с учетом регистра.

   • Соответствовать всему содержимому ячейки — Установите этот флажок, если хотите искать ячейки, содержащие только символы, введенные в поле Найти .

Заменить

Чтобы заменить текст или числа, нажмите Ctrl + H или перейдите на Домашняя страница > Редактирование > Найти и выбрать > Заменить .

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

1. В поле Find what: введите текст или числа, которые вы хотите найти.

   Советы: Вы можете использовать подстановочных знаков — вопросительный знак (? ), звездочку ( * ), тильду ( ~ ) — в критериях поиска.

   • Используйте вопросительный знак (?), Чтобы найти любой одиночный символ — например, s? T находит «sat» и «set».

   • Используйте звездочку (*), чтобы найти любое количество символов — например, s * d находит «грустный» и «начатый».

   • Используйте тильду (~), за которой следует?, * Или ~, чтобы найти вопросительные знаки, звездочки или другие символы тильды — например, fy91 ~? находит «fy91?».

2. В поле Заменить на: введите текст или числа, которые вы хотите использовать для замены текста поиска.

3. Нажмите Заменить или Заменить все .

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

4. Нажмите > Параметры поиска , чтобы при необходимости определить параметры поиска:

   • Внутри: Для поиска данных в определенном выделении выберите Выбор . Чтобы найти данные на листе или во всей книге, выберите Лист или Рабочая книга .

   • Направление: Вы можете выбрать для поиска Вниз (по умолчанию) или Вверх .

   • Учитывать регистр — Установите этот флажок, если хотите искать данные с учетом регистра.

   • Соответствовать всему содержимому ячейки — Установите этот флажок, если хотите искать ячейки, содержащие только символы, введенные в поле Найти .

Как найти или найти текст на нескольких листах Excel

Обновлено: 01.02.2021, Computer Hope

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

Искать в трудовой книжке

В диалоговом окне «Найти и заменить» Microsoft Excel вы можете указать, где вы хотите найти текст. Как показано на следующем изображении, вы можете изменить параметр «Внутри» с Sheet на Workbook для поиска по всей книге, а не только по текущему активному листу.

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

Кончик

Вы можете использовать сочетание клавиш Ctrl + F , чтобы открыть поле «Найти и заменить».

Поиск по выбранному листу

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

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

Например, предположим, что имена ваших рабочих листов — это значения по умолчанию: «Лист1», «Лист2» ​​и «Лист3». У вас есть информация на каждом листе, и вы хотите найти «компьютер» в Sheet1 и Sheet3. Для этого выполните следующие действия.

1. Выберите вкладку листа Sheet1 , если она еще не выбрана.
2. Нажмите клавишу Ctrl на клавиатуре.
3. Продолжая удерживать нажатой клавишу Ctrl , щелкните вкладку Sheet3 .
4. После выделения Sheet1 и Sheet3 отпустите клавишу Ctrl и нажмите Ctrl + F , чтобы открыть поле Найти и заменить .
5. Убедитесь, что в поле Найти и заменить выбрано значение Лист в поле Внутри .Затем нажмите кнопку Найти далее или Найти все .

Как искать термины или значения в электронной таблице Excel

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

Таблицы

Excel для многих — непростая задача.

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

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

Вот как выполнять поиск в электронной таблице Excel.

Ознакомьтесь с продуктами, упомянутыми в этой статье:

Microsoft Office (от 139,99 долларов США при лучшей покупке)

MacBook Pro (от 1299,99 долларов США при лучшей покупке)

Lenovo IdeaPad 130 (от 299,99 долларов США при лучшей покупке)

Как выполнять поиск в Excel с помощью панели «Поисковый лист»

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

Щелкните в поле поиска. Стивен Джон / Business Insider

2. Нажмите «Enter» на ПК (или «Return», если вы используете Mac), чтобы найти первый экземпляр, а затем используйте стрелки рядом с поисковым запросом, чтобы перейти к следующему или предыдущему экземпляру.

3. Вы также можете быстро получить доступ к этой строке поиска с помощью сочетания клавиш «команда + F» на Mac и сочетания клавиш «Control + F» на ПК.

Если вы не можете найти искомые термины или числа, обязательно дважды проверьте, как вы их вводили в строке поиска; Excel не ищет похожие термины, поэтому опечатка сводит на нет усилия. Стивен Джон / Business Insider

Как использовать параметр «Заменить» для замены новых терминов

Если вам нужно заменить несколько экземпляров термина или цифры на листе Excel, щелкните значок увеличительного стекла в правом верхнем углу экрана и щелкните «Заменить…»

Выберите «Заменить» под увеличительным стеклом.Стивен Джон / Business Insider

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

Вы можете заменить все такие термины или переключаться между ними в электронной таблице. Стивен Джон / Business Insider

Insider Inc.получает комиссию при покупке по нашим ссылкам.

Поле поиска

в Excel — Easy Excel Tutorial

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

Вот как выглядит электронная таблица. Если вы введете поисковый запрос в ячейку B2, Excel выполнит поиск в столбце E, а результаты появятся в столбце B.

Чтобы создать это поле поиска, выполните следующие действия.

1. Выберите ячейку D4 и вставьте функцию ПОИСК, показанную ниже. Создайте абсолютную ссылку на ячейку B2.

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

Объяснение: функция ПОИСК находит позицию подстроки в строке. В функции ПОИСК регистр не учитывается. Для Туниса строка «uni» находится в позиции 2. Для США строка «uni» находится в позиции 1.Чем меньше число, тем выше его рейтинг.

3. И США, и Великобритания возвращают значение 1. Чтобы вернуть уникальные значения, которые помогут нам, когда мы сразу же воспользуемся функцией RANK, немного измените формулу в ячейке D4, как показано ниже.

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

Объяснение: функция СТРОКА возвращает номер строки ячейки.Если мы разделим номер строки на большое число и добавим его к результату функции ПОИСК, у нас всегда будут уникальные значения. Однако эти небольшие приращения не повлияют на поисковый рейтинг. Сейчас Соединенные Штаты имеют значение 1,00006, а Великобритания — 1,00009. Мы также добавили функцию ЕСЛИОШИБКА. Если ячейка содержит ошибку (когда строку не удается найти), отображается пустая строка («»).

5. Выберите ячейку C4 и вставьте функцию РАНГ, как показано ниже.

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

Объяснение: функция РАНГ возвращает ранг числа в списке чисел. Если третий аргумент равен 1, Excel ранжирует наименьшее число первым, второе наименьшее число — вторым и т. Д. Поскольку мы добавили функцию СТРОКА, все значения в столбце D уникальны. В результате ранги в столбце C тоже уникальны (нет связей).

7. Мы почти у цели. Мы будем использовать функцию VLOOKUP для возврата найденных стран (сначала самый низкий рейтинг, второй самый низкий рейтинг, второй и т. Д.)) Выберите ячейку B4 и вставьте функцию ВПР, показанную ниже.

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

9. Измените цвет текста чисел в столбце A на белый и скройте столбцы C и D.

Результат. Ваше собственное поле поиска в Excel.

Excel 2016: Использование функции поиска и замены

Урок 10: Использование функции поиска и замены

/ ru / excel2016 / работа с несколькими-листами / содержание /

Введение

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

Необязательно: загрузите нашу рабочую тетрадь.

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

Для поиска содержания:

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

1. На вкладке Home щелкните команду Find and Select , затем выберите Find из раскрывающегося меню.
2. Откроется диалоговое окно «Найти и заменить ». Введите содержимое , которое вы хотите найти. В нашем примере мы введем название отдела.
3. Щелкните Найти далее . Если содержимое найдено, ячейка, содержащая это содержимое, будет выбрана .
4. Щелкните Найти далее , чтобы найти другие экземпляры, или Найти все , чтобы просмотреть все экземпляры поискового запроса.
5. Когда вы закончите, нажмите Закрыть , чтобы закрыть диалоговое окно «Найти и заменить».

Вы также можете получить доступ к команде «Найти», нажав Ctrl + F на клавиатуре.

Щелкните Параметры , чтобы просмотреть критерии расширенного поиска в диалоговом окне «Найти и заменить».

Для замены содержимого ячейки:

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

1. На вкладке Home щелкните команду Найти и выбрать , затем выберите Заменить в раскрывающемся меню.
2. Откроется диалоговое окно «Найти и заменить ». Введите текст, который хотите найти, в поле Find what: .
3. Введите текст, которым хотите заменить его, в поле Заменить на: , затем щелкните Найти далее .
4. Если содержимое найдено, ячейка, содержащая это содержимое, будет выбрана .
5. Просмотрите текст, чтобы убедиться, что вы хотите его заменить.
6. Если вы хотите заменить его, выберите один из вариантов replace . Выбор Заменить заменит отдельные экземпляры , а Заменить все заменит все экземпляры текста во всей книге. В нашем примере мы выберем этот вариант, чтобы сэкономить время.

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

8. Выбранное содержимое ячейки будет заменено на .

9. По завершении нажмите «Закрыть», чтобы закрыть диалоговое окно «Найти и заменить».

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

Вызов!

1. Откройте нашу рабочую тетрадь.
2. Щелкните вкладку Challenge в нижнем левом углу книги.
3. Кристал Льюис вышла замуж и сменила фамилию на Тейлор. Используйте Find and Replace , чтобы изменить фамилию Кристал с Lewis на Taylor . Будьте осторожны, только поменять фамилию Кристалла!
4. Найти и заменить Bio на Biology . Будьте осторожны, , а не , чтобы изменить главную биомедицинскую инженерию!
5. Используйте Найти и заменить все для замены специализации Physics на Physical Science .
6. Когда вы закончите, ваш рабочий лист должен выглядеть так:

/ ru / excel2016 / проверка орфографии / содержание /

Динамическое окно поиска с фильтром Excel (извлечение данных по мере ввода)

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

Примерно как показано ниже:

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

Скачать файл примера и следовать

Посмотреть видео — Создание окна поиска с динамическим фильтром Excel

Создание окна поиска с динамическим фильтром Excel

Этот динамический фильтр Excel можно создать за 3 шага:

1. Получение уникального списка товаров (в данном случае стран). Это будет использоваться при создании раскрывающегося списка.
2. Создание поля поиска. Здесь я использовал поле со списком (элемент управления ActiveX).
3. Установка данных. Здесь я бы использовал три вспомогательных столбца с формулами для извлечения совпадающих данных.

Вот как выглядят необработанные данные:

ПОЛЕЗНЫЙ СОВЕТ. Почти всегда полезно преобразовать данные в таблицу Excel. Вы можете сделать это, выбрав любую ячейку в наборе данных и используя сочетание клавиш Control + T.

Шаг 1. Получение уникального списка элементов

1. Выберите все страны и вставьте его в новый лист.
2. Выберите список стран -> Перейти к данным -> Удалить дубликаты.
3. В диалоговом окне «Удалить дубликаты» выберите столбец, в котором находится список, и нажмите «ОК». Это удалит дубликаты и предоставит вам уникальный список, как показано ниже:
4. Еще один шаг — создать именованный диапазон для этого уникального списка. Для этого:
   • Перейдите на вкладку «Формула» -> Определить имя
   • В диалоговом окне «Определить имя»:
     • Имя: CountryList
     • Область: Рабочая книга
     • Относится к: = UniqueList! $ A $ 2: $ A $ 9 (у меня есть список на отдельной вкладке с именем UniqueList в A2: A9.Вы можете ссылаться на него, где бы ни находился ваш уникальный список)

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

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

Шаг 2. Создание окна поиска с динамическим фильтром Excel

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

Мы можем использовать поле со списком в Excel, чтобы создать этот фильтр поля поиска. Таким образом, всякий раз, когда вы вводите что-либо в поле со списком, это также будет отражаться в ячейке в реальном времени (как показано ниже).

Вот шаги, чтобы сделать это:

1. Перейдите на вкладку разработчика -> Элементы управления -> Вставить -> Элементы управления ActiveX -> Поле со списком (элементы управления ActiveX).
2. Щелкните в любом месте листа. Он вставит поле со списком.
3. Щелкните правой кнопкой мыши поле со списком и выберите «Свойства».
4. В окне «Свойства» внесите следующие изменения:
   • Связанная ячейка: K2 (вы можете выбрать любую ячейку, в которой должны отображаться входные значения. Мы будем использовать эту ячейку при настройке данных).
   • ListFillRange: CountryList (это именованный диапазон, который мы создали на шаге 1. Это покажет все страны в раскрывающемся списке).
   • MatchEntry: 2-fmMatchEntryNone (это гарантирует, что слово не будет автоматически заполнено при вводе)
5. Выбрав поле со списком, перейдите на вкладку разработчика -> Элементы управления -> щелкните режим разработки (это выведет вас из режим дизайна, и теперь вы можете вводить что угодно в поле со списком. Теперь все, что вы набираете, будет отражено в ячейке K2 в реальном времени)

Шаг 3 — Установка данных

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

Вспомогательный столбец 1: Введите порядковые номера для всех записей (в данном случае 20). Для этого вы можете использовать формулу ROWS ().

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

Это можно сделать с помощью комбинации функций ЕСЛИ, ЕЧИСЛО и ПОИСК.

Вот формула:

 = ЕСЛИ (ЕЧИСЛО (ПОИСК ($ K $ 2, D4)), E4, "") 

Эта формула будет искать содержимое в поле поиска (которое связано с ячейкой K2) в ячейке с названием страны.

Если есть совпадение, эта формула возвращает номер строки, в противном случае возвращается пустое значение. Например, если поле со списком имеет значение «США», все записи с указанием страны как «США» будут иметь номер строки, а все остальные будут пустыми («»).

Вспомогательный столбец 3: В вспомогательный столбец 3, нам нужно собрать все номера строк из вспомогательного столбца 2 вместе.Для этого мы можем использовать комбинацию формул IFERROR и SMALL. Вот формула:

 = ЕСЛИОШИБКА (МАЛЕНЬКАЯ ($ F $ 4: $ F $ 23, E4), "") 

Эта формула складывает вместе все совпадающие номера строк. Например, если поле со списком имеет значение US, все номера строк, в которых есть «US», складываются вместе.

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

 = ЕСЛИОШИБКА (ИНДЕКС ($ B $ 4: $ D $ 23, $ G4, COLUMNS ($ I $ 3: I3)), "") 

Эта формула состоит из 2 частей:
ИНДЕКС — извлекаются данные в зависимости от номера строки.
ЕСЛИ ОШИБКА — возвращает пустое значение при отсутствии данных.

Вот снимок того, что вы наконец получили:

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

Загрузите файл примера динамического фильтра Excel

Проявите творческий подход! Попробуйте некоторые варианты

Вы можете попробовать и настроить его в соответствии с вашими требованиями. Вы можете создать несколько фильтров Excel вместо одного. Например, вы можете отфильтровать записи, в которых торговый представитель — Майк, а страна — Япония. Это можно сделать точно так же, как и с некоторыми изменениями в формуле во вспомогательных столбцах.

Другой вариант — фильтрация данных, которые начинаются с символов, которые вы вводите в поле со списком.