4 функции поиска в excel для эффективного поиска в таблицах

Горячие клавиши Excel с использованием функциональных клавиш (F1-F12)

Комбинация	Описание
F1	Отображение панели помощи Excel. Ctrl+F1 отображение или скрытие ленты функций. Alt+F1 создание встроенного графика из данных выделенного диапазона. Alt+Shift+F1 вставка нового листа.
F2	Редактирование активной ячейки с помещением курсора в конец данных ячейки. Также перемещает курсор в область формул, если режим редактирования в ячейке выключен. Shift+F2 добавление или редактирование комментарий. Ctrl+F2 отображение панели печати с предварительным просмотром.
F3	Отображение диалога вставки имени. Доступно только, если в книге были определены имена (вкладка Формулы на ленте, группа Определенные имена, Задать имя). Shift+F3 отображает диалог вставки функции.
F4	Повторяет последнюю команду или действие, если возможно. Когда в формуле выделена ячейка или область, то осуществляет переключение между различными комбинациями абсолютных и относительных ссылок). Ctrl+F4 закрывает активное окно рабочей книги. Alt+F4 закрывает Excel.
F5	Отображение диалога Перейти к. Ctrl+F5 восстанавливает размер окна выбранной рабочей книги.
F6	Переключение между рабочим листом, лентой функций, панелью задач и элементами масштабирования. На рабочем листе, для которого включено разделение областей (команда меню Вид, Окно, Разделить), F6 также позволяет переключаться между разделенными окнами листа. Shift+F6 обеспечивает переключение между рабочим листом, элементами масштабирования, панелью задач и лентой функций. Ctrl+F6 переключает на следующую рабочую книгу, когда открыто более одного окна с рабочими книгами.
F7	Отображает диалог проверки правописания для активного рабочего листа или выделенного диапазона ячеек. Ctrl+F7 включает режим перемещения окна рабочей книги, если оно не максимизировано (использование клавиш курсора позволяет передвигать окно в нужном направлении; нажатие Enter завершает перемещение; нажатие Esc отменяет перемещение).
F8	Включает или выключает режим расширения выделенного фрагмента. В режиме расширения клавиши курсора позволяют расширить выделение. Shift+F8 позволяет добавлять несмежные ячейки или области к области выделения с использованием клавиш курсора. Ctrl+F8 позволяет с помощью клавиш курсора изменить размер окна рабочей книги, если оно не максимизировано. Alt+F8 отображает диалог Макросы для создания, запуска, изменения или удаления макросов.
F9	Осуществляет вычисления на всех рабочих листах всех открытых рабочих книг. Shift+F9 осуществляет вычисления на активном рабочем листе. Ctrl+Alt+F9 осуществляет вычисления на всех рабочих листах всех открытых рабочих книг, независимо от того, были ли изменения со времени последнего вычисления. Ctrl+Alt+Shift+F9 перепроверяет зависимые формулы и затем выполняет вычисления во всех ячейках всех открытых рабочих книг, включая ячейки, не помеченные как требующие вычислений. Ctrl+F9 сворачивает окно рабочей книги в иконку.
F10	Включает или выключает подсказки горячих клавиш на ленте функций (аналогично клавише Alt). Shift+F10 отображает контекстное меню для выделенного объекта. Alt+Shift+F10 отображает меню или сообщение для кнопки проверки наличия ошибок. Ctrl+F10 максимизирует или восстанавливает размер текущей рабочей книги.
F11	Создает диаграмму с данными из текущего выделенного диапазона в отдельном листе диаграмм. Shift+F11 добавляет новый рабочий лист. Alt+F11 открывает редактор Microsoft Visual Basic For Applications, в котором вы можете создавать макросы с использованием Visual Basic for Applications (VBA).
F12	Отображает диалог Сохранить как.

Как в экселе найти фамилию в списке?

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

Первый способ.

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

В ячейке «А1» появится слева стрелочка, нужно нажать и перед вами откроется меню. Нажмем на строчку «Текстовые фильтры», в появившемся списке выберем строку «Содержит».

В появившемся меню, наберем фамилию Сидоренко, после нажмем на кнопку «ОК».

В итоге таблица отформатируется, и останутся только люди с фамилией Сидоренко.

Второй способ.

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

Как сравнить два столбца в Excel по строкам

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

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

Для того, чтобы сравнить данные в каждой строке двух столбцов в Excel напишем простую формулу ЕСЛИ . Вставлять формулу следует в каждую строку в соседнем столбце, рядом с таблицей, в которой размещены основные данные. Создав формулу для первой строки таблицы, мы сможем ее протянуть/скопировать на остальные строки.

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

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

Мы можем уместить проверку на совпадения и различия между двумя столбцами в одной строке в одной формуле:

Пример результата вычислений может выглядеть так:

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

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

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

Точное совпадение значений по вертикали в списке

Для этого можно использовать функцию ВLOOKUP или сочетание функций ИНДЕКС и НАЙТИПОЗ.

Дополнительные сведения см. в этой информации.

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

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

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

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

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

Важно: Убедитесь, что значения в первой строке отсортировали в порядке возрастания

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

Дополнительные сведения см. в этой информации.

Для этого используйте функции СМЕЩЕНИЕ и НАЙТИВМЕСЯК.

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

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

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

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

Точное совпадение значений по горизонтали в списке

Для этого используйте функцию ГГПУ. См. пример ниже.

Г ПРОСМОТР ищет столбец «Продажи» и возвращает значение из строки 5 в указанном диапазоне.

Дополнительные сведения см. в сведениях о функции Г ПРОСМОТР.

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

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

Важно: Убедитесь, что значения в первой строке отсортировали в порядке возрастания

В примере выше ГЛЕБ ищет значение 11000 в строке 3 указанного диапазона. Она не находит 11000, поэтому ищет следующее наибольшее значение меньше 1100 и возвращает значение 10543.

Дополнительные сведения см. в сведениях о функции Г ПРОСМОТР.

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

Примечание: В Excel 2010 больше не будет надстройки #x0. Эта функция была заменена мастером функций и доступными функциями подменю и справки (справка).

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

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

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

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

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

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

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

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

Что делать

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

Способ №1:

• Щелкните «Домой».
• Выберите «Найти» и выберите».

• Щелкните «Найти …».
• Введите символы для поиска.
• Нажмите «Найти далее / все».

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

Способ №2 (по диапазону):

1. Выберите нужный диапазон ячеек в Excel.
2. Нажмите Ctrl + F на клавиатуре.
3. Введите требуемый запрос и действуйте, как описано выше.

Способ №3 (расширенный):

1. Введите «Найти и заменить».
2. Щелкните «Параметры».
3. Выберите инструменты поиска.
4. Нажмите кнопку подтверждения.

Если вы все сделали правильно, но поиск в Excel по-прежнему не работает, попробуйте выполнить следующие действия:

• Убедитесь, что количество введенных символов меньше 255. В противном случае функция не будет работать.
• Снимите защиту с листа Excel. Для этого перейдите в «Файл», затем в «Информация» и «Снять защиту с листа». Если пароль был установлен, вы должны ввести его в диалоговом окне и подтвердить.

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

• Выберите различные параметры поиска в Excel. Если «Найти все» не работает, выберите «Найти далее».
• Снимите флажок «Вся ячейка».

В некоторых случаях поиск в Excel не работает и появляется ошибка #Value !. В таком случае вы можете использовать одно из следующих решений.

Вариант №1

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

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

Вариант №3

Используйте опцию DLSTR, чтобы определить количество символов в текстовой строке. При этом установите правильный параметр поиска.

Устранение сбоя

Распространенная причина, по которой Excel не выполняет поиск и поиск в программе не работает, — это программная ошибка. Это может проявляться в виде замирания, отсутствия реакции или прекращения работы. Если да, попробуйте следующие решения:

1. Запустите Excel в безопасном режиме и убедитесь, что он работает правильно. Для этого удерживайте Ctrl при запуске программы. В этом случае программа пропускает ряд функций и параметров, которые могут привести к неисправности. Если проблема не решается загрузкой в ​​безопасном режиме, переходите к следующему шагу. В противном случае отключите ненужные настройки.
2. Установите последние обновления, чтобы решить проблему.
3. Убедитесь, что в Office Excel не используется другой процесс. Эта информация должна быть внизу окна. Чтобы исправить это, попробуйте закрыть посторонние процессы, а затем еще раз проверьте, работает ли опция поиска.
4. Полностью удалите, а затем повторно установите Excel. Этот метод часто помогает, если Excel по какой-то причине не пытается или не работает.

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

Что еще попробовать

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

• Убедитесь, что у вас правильный макет, и нажмите Ctrl + F.
• Проверьте размер документа. Эта функция иногда дает сбой и не работает, если на вашем ПК / ноутбуке недостаточно оперативной памяти из-за большого объема работы.
• Проверьте свое устройство на вирусы. Проблема может быть связана с вредоносным ПО.

• Попробуйте установить более новую версию Excel. В этом случае рекомендуется полностью удалить старую версию и вычистить остатки.
• Убедитесь, что вы ввели правильные варианты расширенного поиска.

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

Возможности расширенного поиска по таблице Эксель Онлайн

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

1. После того как вы открыли окно Найти и заменить, кликните кнопку Параметры;
2. Появилось множество дополнительных инструментов, и, при необходимости, можно выполнить их корректировку;
3. После установки всех необходимых настроек, кликните Найти всёили Найти далее, после чего последует выдача поиска.

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

Связка фукнций ИНДЕКС и ПОИСКПОЗ

Давайте найдем телефон Ивана. Создаем формулу с функцией ИНДЕКС.

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

Получаем верный результат.

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

Как работать с поиском в Excel

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

Далее рассмотрим варианты поиска данных по таблице Excel.

Вариант 1: Быстрый поиск

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

Использование инструмента происходит по данной инструкции:

1. Откройте нужный документ в Excel. В верхнем меню переключитесь на вкладку «Главная». Вообще она открыта всегда по умолчанию.
2. Теперь кликните по блоку «Найти и выделить». В контекстном меню выберите «Найти». Вместо описанных действий можно просто воспользоваться сочетанием клавиш Ctrl+F.

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

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

Вариант 2: поиск в указанном интервале

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

Инструкция к этому типу поиска выглядит следующим образом:

1. Выделите область ячеек, внутри которых нужно провести поиск. Если вам нужно выделить несколько областей, разделенных между собой другими ячейками, то зажмите клавишу Ctrl, чтобы добавить новый блок к выделению.
2. После выделения вызовите окошко поиска, воспользовавшись сочетанием клавиш Ctrl+F.
3. Откроется такое же окно для поиска, как и в предыдущем варианте с той только поправкой, что оно будет искать только в выделенной области. В поле «Найти» введите то, что вам нужно отыскать.
4. Воспользуйтесь кнопкой «Найти все» или «Найти далее». Подробно о том, чем обе кнопки отличаются друг от друга мы писали выше.

Вариант 3: Расширенный поиск

При обычном поиске в результат выдачи будут попадать все ячейки, которые содержат последовательный набор поисковых символов в любом виде не зависимо от регистра. Мало того, в выдачу может попасть не только содержимое конкретной ячейки, но и адрес элемента, на который она ссылается. Например, в ячейке E2 содержится формула, которая представляет собой сумму ячеек A4 и C3. Если в поиск задать цифру 4, то все равно будет показываться ячейка E2, даже если результат сумм A4 и C3 получился совершенно другим.

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

1. Вызовите окно обычного поиска. Это можно сделать через кнопку «Найти» или воспользовавшись сочетанием клавиш Ctrl+F.
2. В открывшемся окне настроек воспользуйтесь кнопкой «Параметры».
3. После этого появится целый ряд параметров, которые можно настроить под себя, например, можно сделать так, чтобы во время поиска учитывался регистр, в поиске выводились элементы, содержащие точное наименование. Также здесь можно задать, что конкретно просматривать, в какой части документа искать и даже формат, в котором должен быть результат.

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

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

Просмотр макроса

• В Excel невозможно использовать в качестве имен диапазонов буквы r и c.
• Единственными символами, которые можно использовать в именах диапазонов, являются точка (.) и подчеркивание (_).
• При использовании инструмента Создать из выделенного пробелы в созданном имени автоматически будут заменены символами подчеркивания (_). Например, имя Product 1 будет создано как Product_1.
• Имена диапазонов не могут начинаться с цифр или выглядеть как ссылка на ячейку. Например, в качестве имен диапазонов невозможно использовать имена 3Q и A4. Кроме того, в Microsoft Excel 2013 имеется более 16 000 столбцов, и такие имена, как cat1, являются недопустимыми, поскольку существует ячейка с именем CAT1. Если попытаться присвоить ячейке имя CAT1, появится сообщение о том, что введено недопустимое имя. В случае необходимости используйте подчеркивание (_) и назовите ячейку cat1_.

Пример 1. Предполагаемый годовой доход вычисляется как кратный прошлогоднему доходу (рис. 1.16). Воспользуемся формулу =(1+прирост)*предыдущий_год (имя диапазона не может содержать пробел). Требуется вычислить доходы за 2012–2018 гг. с приростом 10% в год, начиная с базового уровня 300 млн. долларов в 2011 г.

Как сделать строку поиска в Excel?
Найти диапазон в Excel можно через кнопку в строке адреса ячейки. Второй вариант, как найти диапазон в Excel- на закладке Формулы» в разделе «Определенные имена» нажимаем на кнопку «Диспетчер имен». В появившемся окне нажимаем на нужный диапазон.

CTRL +F

Другие примеры использования

Найти первую цифру в ячейке:

=МИН(ЕСЛИОШИБКА(ПОИСК({0:9:8:7:6:5:4:3:2:1},A1),1000))

Найти первую цифру в ячейке и вернуть все, что перед ней:

=ЛЕВСИМВ(A1,МИН(ЕСЛИОШИБКА(ПОИСК({0:9:8:7:6:5:4:3:2:1},A1),1000))-1)

Узнать, содержит ли ячейка латиницу. Формула вернет «ИСТИНА» или «ЛОЖЬ»:

=СЧЁТ(ПОИСК({«a»:»b»:»c»:»d»:»e»:»f»:»g»:»h»:»i»:»j»:»k»:»l»:»m»:»n»:»o»:»p»:»q»:»r»:»s»:»t»:»u»:»v»:»w»:»x»:»y»:»z»};A1))>0

Найти кириллицу в тексте аналогичным путем:

=СЧЁТ(ПОИСК({«а»:»б»:»в»:»г»:»д»:»е»:»ё»:»ж»:»з»:»и»:»й»:»к»:»л»:»м»:»н»:»о»:»п»:»р»:»с»:»т»:»у»:»ф»:»х»:»ц»:»ч»:»ш»:»щ»:»ъ»:»ы»:»ь»:»э»:»ю»:»я»};A1))>0

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

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

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

Для удобства также приводим
ссылку на оригинал (на английском языке)
.

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

1. На вкладке Главная в группе Редактирование нажмите кнопку Найти и выделить.

2. Выполните одно из указанных ниже действий.

   • Чтобы найти текст или числа, выберите пункт Найти.

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

3. В поле Найти введите текст или числа, которые нужно искать, или щелкните стрелку в поле Найти и нажмите кнопку последнего поиска в списке.

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

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

     

     

     

     

     

     

     

     

     

     

   • Вопросительный знак заменяет один любой знак. Например, если ввести г?д, то будут найдены слова «гад», «гид» и «год».

     Совет: Звездочки, знак вопроса и символы тильда (~) можно найти в данных листа перед их с тильда в поле Найти. Например, чтобы найти данные, которые содержат «?», вы введите ~? как критерии поиска.

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

   • Для поиска данных на листе или во всей книге выберите в поле Искать вариант на листе или в книге.

   • Для поиска данных в строках или столбцах выберите в поле Просматривать вариант по строкам или по столбцам.

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

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

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

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

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

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

6. Выполните одно из указанных ниже действий.

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

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

   • Чтобы заменить текст или числа, введите знаки для замены в поле Заменить на (или оставьте это поле пустым, чтобы ничем не заменять знаки), а затем нажмите кнопку Найти далее или Найти все.

     Примечание: Если поле Заменить на недоступно, откройте вкладку Заменить.

     При необходимости поиск можно отменить, нажав клавишу ESC.

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

Советы

• Microsoft Excel сохраняет параметры форматирования, которые можно определить. Если вы еще раз выполнить поиск на листе данные и не удается найти символы, которые вы знаете содержал сведения, может потребоваться снимите нужные параметры форматирования из предыдущего поиска. В диалоговом окне Поиск и замена перейдите на вкладку Поиск и нажмите кнопку Параметры, чтобы открыть параметры форматирования. Щелкните стрелку рядом с полем Формат и нажмите кнопку Очистить.

• Для поиска текста или чисел на листе также можно использовать функции ПОИСК и НАЙТИ.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Функция СУММЕСЛИ и подстановочные знаки в Excel

Функция СУММЕСЛИ, пожалуй, одна из самых полезных и часто используемых в Excel. У этой функции есть недостаток. Для суммирования значений с помощью этой функции критерий должен совпадать символ в символ.

Например, представим, что вас зовут Иван Петров и у нас есть таблица с данными продаж, а также фамилиями и именами продавцов. Если мы будем знать только ваше имя, то мы не сможем суммировать данные из этой таблицы с помощью функции СУММЕСЛИ только по имени. Нам потребуется знать вашу фамилию.

Это проблема. Но у нас есть решение в виде подстановочных знаков.

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

Какие подстановочные знаки наиболее часто встречаются?

• * – звездочка
• ? – вопросительный знак

Итак, давайте теперь рассмотрим как подстановочные знаки помогут нам с суммированием данных при использовании функции СУММЕСЛИ.

Подстановочный знак “звездочка” и функция СУММЕСЛИ

“Звездочка” это самый популярный подстановочный знак. Она используется для поиска любой строки знаков.

Например, если в запросе будет указано “г*д”, то поиску будут соответствовать результаты “год”, “город”, “грейд” и т.д.

Если “звездочка” будет стоять в конце запроса, например “Иван*”, то результату поиска будут соответствовать значения “Иван Маслов”, “Иван Мельников”, “Иван Петров”, “Иванвадпловапло” и т.д.

Рассмотрим работу подстановочного знака и функции СУММЕСЛИ на примере. Представим, что у нас есть таблица со счетами по трем товарам с суммами выплат. Наша задача посчитать сумму поступлений по каждому продукту, на основе оплаченных счетов.

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

Для решения этой задачи мы выпишем в отдельную таблицу перечень продуктов, а в соседнем столбце пропишем формулу с функцией СУММЕСЛИ и подстановочным знаком “звездочка”:

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

При этом не важно, какие символы будут указаны в таблице после названия продукта

Вопросительный знак и функция СУММЕСЛИ

Вопросительный знак также может эффективно использоваться с функцией СУММЕСЛИ для поиска данных.

Вопросительный знак используется для подстановки любого одного символа. Например, если поисковый запрос будет “г?д”, то результатом могут быть слова “гид”, “гад”, “год”.

Рассмотрим работу вопросительного знака совместно с функцией СУММЕСЛИ. Представим, что у нас есть таблица со счетами по трем товарам с суммами выплат. Названия по Товару А отличаются одним символом. Как раз с помощью вопросительного знака мы можем задать настройку поиска, учитывающую тот фактор, что один символ у нас может быть абсолютно любым.

Формула с функцией СУММЕСЛИ будет выглядеть так:

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

Итак, подстановочные знаки “звездочка” и вопросительный знак эффективно использовать вместе с функциями СУММЕСЛИ и СУММЕСЛИМН:

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

Описание

Функции ПОИСК И ПОИСКБ находят одну текстовую строку в другой и возвращают начальную позицию первой текстовой строки (считая от первого символа второй текстовой строки). Например, чтобы найти позицию буквы “n” в слове “printer”, можно использовать следующую функцию:

=ПОИСК(“н”;”принтер”)

Эта функция возвращает 4, так как “н” является четвертым символом в слове “принтер”.

Можно также находить слова в других словах. Например, функция

=ПОИСК(“base”;”database”)

возвращает 5, так как слово “base” начинается с пятого символа слова “database”. Можно использовать функции ПОИСК и ПОИСКБ для определения положения символа или текстовой строки в другой текстовой строке, а затем вернуть текст с помощью функций ПСТР и ПСТРБ или заменить его с помощью функций ЗАМЕНИТЬ и ЗАМЕНИТЬБ. Эти функции показаны в примере 1 данной статьи.

Важно: 

• Эти функции могут быть доступны не на всех языках.

• Функция ПОИСКБ отсчитывает по два байта на каждый символ, только если языком по умолчанию является язык с поддержкой БДЦС. В противном случае функция ПОИСКБ работает так же, как функция ПОИСК, и отсчитывает по одному байту на каждый символ.

  

  

  

  

  

  

  

  

К языкам, поддерживающим БДЦС, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский.

6 Надстройка Inquire

Начиная с версий MS Excel 2013 табличный процессор позволяет воспользоваться еще одной методикой — специальной надстройкой Inquire. Она предназначена для того, чтобы сравнивать не колонки, а два файла .XLS или .XLSX в поисках не только совпадений, но и другой полезной информации. 

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

1. Перейти к параметрам электронной таблицы. 
2. Выбрать сначала надстройки, а затем управление надстройками COM. 
3. Отметить пункт Inquire и нажать «ОК». 
4. Перейти к вкладке Inquire. 
5. Нажать на кнопку Compare Files, указать, какие именно файлы будут сравниваться, и выбрать Compare. 
6. В открывшемся окне провести сравнения, используя показанные совпадения и различия между данными в столбцах. 

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

• 5 программ для совместной работы с документами

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

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

Sub Poisk()

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

Dim keyword As String

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

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

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

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

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

Line1:

Next cell

End Sub

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

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

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

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

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

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