Найти ячейку в excel по содержимому: Функция ПОИСКПОЗ — Служба поддержки Майкрософт

Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ

Совет: Попробуйте использовать новые функции ПРОСМОТРX и XMATCH, а также улучшенные версии функций, описанные в этой статье. Эти новые функции работают в любом направлении и возвращают точные совпадения по умолчанию, что упрощает и упрощает работу с ними по сравнению с предшественниками.

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

Функции ВВ., а также ИНДЕКС и ВЫБОРПОЗ — одни из самых полезных функций в Excel.

Примечание: Мастер подметок больше не доступен в Excel.

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

=ВПР(B2;C2:E7,3,ИСТИНА)

В этом примере B2 является первым аргументом —элементом данных, который требуется для работы функции. В случае СРОТ ВЛ.В.ОВ этот первый аргумент является искомой значением. Этот аргумент может быть ссылкой на ячейку или фиксированным значением, таким как «кузьмина» или 21 000. Вторым аргументом является диапазон ячеек C2–:E7, в котором нужно найти и найти значение. Третий аргумент — это столбец в диапазоне ячеек, содержащий ищите значение.

Четвертый аргумент необязателен. Введите истина или ЛОЖЬ. Если ввести ИСТИНА или оставить аргумент пустым, функция возвращает приблизительное совпадение значения, указанного в качестве первого аргумента. Если ввести ЛОЖЬ, функция будет соответствовать значению, заведомо первому аргументу. Другими словами, если оставить четвертый аргумент пустым или ввести ИСТИНА, это обеспечивает большую гибкость.

В этом примере показано, как работает функция. При вводе значения в ячейку B2 (первый аргумент) в результате поиска в ячейках диапазона C2:E7 (2-й аргумент) выполняется поиск в ней и возвращается ближайшее приблизительное совпадение из третьего столбца в диапазоне — столбца E (третий аргумент).

Четвертый аргумент пуст, поэтому функция возвращает приблизительное совпадение. Иначе потребуется ввести одно из значений в столбец C или D, чтобы получить какой-либо результат.

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

Использование индекса и MATCH вместо ВРОТ

При использовании функции ВПРАВО существует ряд ограничений, которые действуют только при использовании функции ВПРАВО. Это означает, что столбец, содержащий и look up, всегда должен быть расположен слева от столбца, содержащего возвращаемого значения. Теперь, если ваша таблица не построена таким образом, не используйте В ПРОСМОТР. Используйте вместо этого сочетание функций ИНДЕКС и MATCH.

В данном примере представлен небольшой список, в котором искомое значение (Воронеж) не находится в крайнем левом столбце. Поэтому мы не можем использовать функцию ВПР. Для поиска значения «Воронеж» в диапазоне B1:B11 будет использоваться функция ПОИСКПОЗ. Оно найдено в строке 4. Затем функция ИНДЕКС использует это значение в качестве аргумента поиска и находит численность населения Воронежа в четвертом столбце (столбец D). Использованная формула показана в ячейке A14.

Дополнительные примеры использования индексов и MATCH вместо В ПРОСМОТР см. в статье билла Https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ Билла Джилена (Bill Jelen), MVP корпорации Майкрософт.

Попробуйте попрактиковаться

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

Пример работы с ВЛОКОНПОМ

Скопируйте следующие данные в пустую таблицу.

Совет: Прежде чем врезать данные в Excel, установите для столбцов A–C ширину в 250 пикселей и нажмите кнопку «Перенос текста» (вкладка «Главная», группа «Выравнивание»).

Пример ГВ.Г.В.В.

Скопируйте всю таблицу и вставьте ее в ячейку A1 пустого листа Excel.

Совет: Прежде чем врезать данные в Excel, установите для столбцов A–C ширину в 250 пикселей и нажмите кнопку «Перенос текста» (вкладка «Главная», группа «Выравнивание»).

Примеры индекса и match

В последнем примере функции ИНДЕКС и MATCH совместно возвращают номер счета с наиболее ранней датой и соответствующую дату для каждого из пяти городов. Так как дата возвращается как число, для ее формата используется функция ТЕКСТ. Функция ИНДЕКС использует результат, возвращенный функцией ПОИСКПОЗ, как аргумент. Сочетание функций ИНДЕКС и ПОИСКПОЗ используется в каждой формуле дважды — сперва для возврата номера счета, а затем для возврата даты.

Скопируйте всю таблицу и вставьте ее в ячейку A1 пустого листа Excel.

Совет: Перед тем как вировать данные в Excel, установите для столбцов A–D ширину в 250 пикселей и нажмите кнопку «Перенос текста» (вкладка «Главная», группа «Выравнивание»).

Поиск и выделение ячеек, соответствующих определенным условиям

Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Access 2007 Еще…Меньше

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

Сделайте следующее:

1. Для начала с одного из следующих начните:

   • org/ListItem»>

     Чтобы найти конкретные ячейки на всем ячеек, щелкните любую из них.

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

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

2. На вкладке Главная нажмите кнопку & выберите > Перейти (в группе Редактирование).

   
   Сочетания клавиш: Нажмите CTRL+G.

3. org/ListItem»>

   Нажмите кнопку Дополнительный.

4. В диалоговом окне Перейти к специальным выберите один из следующих параметров:

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Примеры формул ИНДЕКС и ПОИСКПОЗ для выборки из списка в Excel

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

Как применить формулу ИНДЕКС и ПОИСКПОЗ в Excel

Ниже на рисунке представлены города и области, в которых находятся магазины сети. Допустим, что после выбора пользователем названия области с выпадающего списка в ячейке G1 должны автоматически заполнится поля «Город» G2 и «№ магазина» G3:

Функция ИНДЕКС возвращает значение ячейки, находящееся в определенной строке и столбце указанного диапазона ячеек. В данном случае в аргументе функции как диапазон ячеек указана исходная таблица магазинов. Во втором аргументе указывается сначала номер строки, а в третьем – номер столбца. В формуле для поиска города значения считываются с первого столбца, поэтому последний аргумент содержит число 1.

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

Поэтому последний аргумент в функции ВПР равен числу 4.

Если диапазон ячеек не начинается с ячейки A1 аргументы, определяющие строку и столбец, не соответствуют номерам строк и столбцов рабочего листа Excel. Отсчет номеров производится, начиная от ячейки левого верхнего угла указанного диапазона (в данном примере это A2) в первом аргументе функции ИНДЕКС. Например, формула =ИНДЕКС(G2:P10;2;2) возвращает значение ячейки h4, которая находится во второй строке второго столбца диапазона G2:P10.

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

Чтобы получить номер соответствующей строки для функции ИНДЕКС необходимо использовать функцию ПОИСКПОЗ, которая возвращает позицию найденного значения в таблице. Функция ПОИСКПОЗ использует 3 аргумента в своем синтаксисе:

1. Искомое значение – то что следует найти.
2. Просматриваемый массив – одномерный массив или диапазон ячеек из одной строки или из одного столбца где должно быть найдено искомое значение.
3. Тип сопоставления – точность соответствия (необязательный аргумент для заполнения), при точном соответствии указывается значение ЛОЖЬ или 0. Если требуется неточное совпадение значений, тогда ИСТИНА или 1.

В данном примере искомым значением выступает название области указана в ячейке G1. Данное значение формула ищет в списке областей диапазона C2:C11. Функция ПОИСКПОЗ поочередно проверяет все ячейки, пока не найдет строку «Свердловская», находящееся на 5-ой позиции. После чего данная функция возвращает значение 5, которое будет использовано как второй аргумент с номером строки для функции ИНДЕКС.

После возвращения результата через функцию ПОИСКПОЗ функция ИНДЕКС будет иметь все необходимые данные для отображения соответственного значения. Перейдет на 5-тую строку диапазона ячеек и выберет значение из первого столбца «Город» или из четвертого «№ магазина».

Внимание! Если в аргументах функции ИНДЕКС указать номер строки больше чем количество строк в диапазоне ячеек или номер столбца больше чем количество столбцов, тогда функция возвращает ошибку #ССЫЛКА!



Как сделать выборку из списка формулой ПРОСМОТР в Excel

Формула из комбинации функций ИНДЕКС и ПОИСКПОЗ – это самая популярный тип формул для поиска значений в таблице Excel. Значительно реже используется функция ПРОСМОТР. В ее синтаксисе используется 3 аргумента:

1. Искомое значение – значение позицию которого необходимо найти.
2. Просматриваемый вектор – одномерный массив или диапазон ячеек из одной строки или из одного столбца где будет вестись поиск искомого значения.
3. Вектор результатов — одномерный массив или диапазон ячеек из одной строки или из одного столбца из которого следует вернуть результат (необязательный для заполнения).

Внимание! Сразу же отметим недостатки функции ПРОСМОТР, а они весьма существенные:

1. Нет возможности установить точное совпадение как в функциях ВПР, ГПР и ПОИСКПОЗ.
2. Просматриваемый одномерный массив вектора должен быть отсортирован по возрастанию иначе функция будет возвращать ошибочные результаты как оказано на рисунке:

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

И соответственного номера магазина:

Таблица пересортирована по диапазону C2:C11 по возрастанию и теперь все работает как надо.

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

Чтобы найти необходимый город, функция ПРОСМОТР сначала определяет, что строка «Свердловская» находится на 10-ой позиции (после сортировки таблицы) просматриваемого вектора по диапазону C2:C11. После формула чего возвращает содержимое 10-ой ячейки, но уже по вектору в диапазоне A2:A11. Аналогичным принципом функция ищет номер магазина, но там результирующий вектор – это уже диапазон D2:D11.

Excel 4. Содержимое ячеек – Эффективная работа в MS Office

По окончании этого урока вы сможете:

1. Делать первые рабочие операции по форматированию таблицы
2. Находить нужную ячейку
3. Вводить данные в ячейки и редактировать содержимое ячеек
4. Делать поиск и замену текста.

Скачайте таблицу Excel тут. Это простенькая табличка, но для начала сойдёт.

1. Первые операции по форматированию таблицы

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

Но в большой таблице вы замучаетесь это делать.

Поэтому первое, что надо делать…

Шаг 1. Выделяем всю таблицу (подводим курсор к пересечению имён строчек и столбцов и щёлкаем по зелёному треугольнику):

Шаг 2. Вызываем диалоговое окно «Формат ячеек» (лента Главная → группа команд Ячейки → команда Формат → команда Формат ячеек из выпадающего меню):

Шаг 3. Задаём режим «Переносить текст»:

ОК.

Шаг 4. Не снимая выделения таблицы, вызываем ПМ контекстное меню (Внимание! Курсор находится на строчке названия столбцов) и выбираем команду «Ширина столбцов»:

Шаг 5. Устанавливаем ширину столбца заведомо большую ширины содержимого ячейки:

ОК.

Шаг 6. Устанавливаем автоподбор высоты и ширины ячеек :

1. лента Главная → группа команд Ячейки → команда Формат → команда Автоподбор высоты строки и …
2. лента Главная → группа команд Ячейки → команда Формат → команда Автоподбор ширины столбца

А вот результат:

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

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

1. Выделите таблицу
2. Лента Главная → группа команд Ячейки → команда Формат → команда Автоподбор высоты строки

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

Шаг 7. Задаём режим «Переносить текст» (лента Главная → группа команд Выравнивание→ команда «Переносить текст»):

Как видите, автоподбор высоты произошёл автоматически. Но автоподбор по ширине придётся задавать, как на Шаге 6.

При дальнейшей работе с таблицей Excel может возникнуть необходимость добавления текста в определённую ячейку (и не в одну).

2. Поиск нужной ячейки

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

Но можно сделать обратную операцию: ввести в поле «Имя Ячейки» идентификатор ячейки и курсор выделит ячейку с этим именем (вводим в поле «Имя ячейки» букву в английском регистре и цифру, например B7 и нажимаем Enter):

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

3.

Ввод и редактирование содержимого ячеек

Все, что вы набираете на клавиатуре, появляется и в текущей ячейке, и в строке формул:

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

Содержимое ячейки можно редактировать двумя способами.

1. Выделяем ячейку и, щёлкнув на строке формул, редактируем содержимое в строке формул.
2. Дважды щелкаем на ячейку, а затем помещаем текстовой курсор на место, куда хотите внести изменения.

Вводим понятия «Текстовый режим» и «Табличный режим».

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

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

Если во время ввода данных вы передумали продолжать ввод, то его можно отменить, нажав на клавишу . Если во время набора текста вы допустили ошибку, то исправьте ее с помощью клавиши Backspace или Delete.

4. Поиск и замена данных

Шаг 1. Вызываем диалоговое окно «Найти и заменить» (лента Главная → группа команд Редактирование → команда Найти и выделить → команда Заменить):

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

Укажите подходящие режимы.

Шаг 2. Заменяем «5» на «6» (в поле «Найти» введите текст, который нужно найти, → в поле «Заменить» на задается текст, на который нужно заменить

Для замены ячеек с полностью совпадающих текстом в поле «Найти» отметьте режим «Ячейка целиком»

Нажимаем «Заменить все»:

ОК. Результат:

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

1. Выделите ячейки, в которых необходимо заменить все данные или только их часть, либо щёлкните на ячейке А1, если требуется произвести изменения во всем рабочем листе.
2. В поле «Найти» введите текст, который нужно заменить.
3. В поле «Заменить на» ведите текст, на который должна быть заменяемый текст.

Теперь вы сможете:

1. Делать первые рабочие операции по форматированию таблицы
2. Находить нужную ячейку
3. Вводить данные в ячейки и редактировать содержимое ячеек
4. Делать поиск и замену текста.

Поиск на листе Excel, примеры использования Find на VBA

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

Поиск перебором значений

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

Sheets("Данные").Select
For y = 1 To Cells.SpecialCells(xlLastCell).Row
    If Cells(y, 1) = "123" Then
        Exit For
    End If
Next y
MsgBox "Нашел в строке: " + CStr(y)

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

Поиск функцией Find

Гораздо быстрее обычного перебора и при этом довольно гибкий. В простейшем случае, чтобы найти в колонке A ячейку, содержащую «123» достаточно такого кода:

Sheets("Данные"). Select
Set fcell = Columns("A:A").Find("123")
If Not fcell Is Nothing Then
    MsgBox "Нашел в строке: " + CStr(fcell.Row)
End If

Вкратце опишу что делают строчки данного кода:

1-я строка: Выбираем в книге лист «Данные»;

2-я строка: Осуществляем поиск значения «123» в колонке «A», результат поиска будет в fcell;

3-я строка: Если удалось найти значение, то fcell будет содержать Range-объект, в противном случае — будет пустой, т.е. Nothing.

Полностью синтаксис оператора поиска выглядит так:

Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

What — Строка с текстом, который ищем или любой другой тип данных Excel

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

LookIn — Тип искомых данных. Может принимать одно из значений: xlFormulas (формулы), xlValues (значения), или xlNotes (примечания).

LookAt — Одно из значений: xlWhole (полное совпадение) или xlPart (частичное совпадение).

SearchOrder — Одно из значений: xlByRows (просматривать по строкам) или xlByColumns (просматривать по столбцам)

SearchDirection — Одно из значений: xlNext (поиск вперед) или xlPrevious (поиск назад)

MatchCase — Одно из значений: True (поиск чувствительный к регистру) или False (поиск без учета регистра)

MatchByte — Применяется при использовании мультибайтных кодировок: True (найденный мультибайтный символ должен соответствовать только мультибайтному символу) или False (найденный мультибайтный символ может соответствовать однобайтному символу)

SearchFormat — Используется вместе с FindFormat. Сначала задается значение FindFormat (например, для поиска ячеек с курсивным шрифтом так: Application. FindFormat.Font.Italic = True), а потом при использовании метода Find указываем параметр SearchFormat = True. Если при поиске не нужно учитывать формат ячеек, то нужно указать SearchFormat = False.

Чтобы продолжить поиск, можно использовать FindNext (искать «далее») или FindPrevious (искать «назад»).

Примеры поиска функцией Find

Пример 1: Найти в диапазоне «A1:A50» все ячейки с текстом «asd» и поменять их все на «qwe»

With Worksheets(1).Range("A1:A50")
  Set c = .Find("asd", LookIn:=xlValues)
  Do While Not c Is Nothing
    c.Value = "qwe"
    Set c = .FindNext(c)
  Loop
End With

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

Пример 2: Правильный поиск значения с использованием FindNext, не приводящий к зацикливанию.

With Worksheets(1).Range("A1:A50")
  Set c = .Find("asd", lookin:=xlValues)
  If Not c Is Nothing Then
    firstResult = c.Address
    Do
      c.Font.Bold = True
      Set c = .FindNext(c)
      If c Is Nothing Then Exit Do
    Loop While c.Address <> firstResult
  End If
End With

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

Пример 3: Продолжение поиска с использованием Find с параметром After.

With Worksheets(1).Range("A1:A50")
  Set c = .Find("asd", lookin:=xlValues)
  If Not c Is Nothing Then
    firstResult = c. Address
    Do
      c.Font.Bold = True
      Set c = .Find("asd", After:=c, lookin:=xlValues)
      If c Is Nothing Then Exit Do
    Loop While c.Address <> firstResult
  End If
End With

Следующий пример демонстрирует применение SearchFormat для поиска по формату ячейки. Для указания формата необходимо задать свойство FindFormat.

Пример 4: Найти все ячейки с шрифтом «курсив» и поменять их формат на обычный (не «курсив»)

lLastRow = Cells.SpecialCells(xlLastCell).Row
lLastCol = Cells.SpecialCells(xlLastCell).Column
Application.FindFormat.Font.Italic = True
With Worksheets(1).Range(Cells(1, 1), Cells(lLastRow, lLastCol))
  Set c = .Find("", SearchFormat:=True)
  Do While Not c Is Nothing
    c.Font.Italic = False
    Set c = .Find("", After:=c, SearchFormat:=True)
  Loop
End With

Примечание: В данном примере намеренно не используется FindNext для поиска следующей ячейки, т. к. он не учитывает формат (статья об этом: https://support.microsoft.com/ru-ru/kb/282151)

Коротко опишу алгоритм поиска Примера 4. Первые две строки определяют последнюю строку (lLastRow) на листе и последний столбец (lLastCol). 3-я строка задает формат поиска, в данном случае, будем искать ячейки с шрифтом Italic. 4-я строка определяет область ячеек с которой будет работать программа (с ячейки A1 и до последней строки и последнего столбца). 5-я строка осуществляет поиск с использованием SearchFormat. 6-я строка — цикл пока результат поиска не будет пустым. 7-я строка — меняем шрифт на обычный (не курсив), 8-я строка продолжаем поиск после найденной ячейки.

Хочу обратить внимание на то, что в этом примере я не стал использовать «защиту от зацикливания», как в Примерах 2 и 3, т.к. шрифт меняется и после «прохождения» по всем ячейкам, больше не останется ни одной ячейки с курсивом.

Свойство FindFormat можно задавать разными способами, например, так:

With Application. FindFormat.Font 
  .Name = "Arial" 
  .FontStyle = "Regular" 
  .Size = 10 
End With

Поиск последней заполненной ячейки с помощью Find

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

Пример 5: Найти последнюю колонку и столбец, заполненные данными

Set c = Worksheets(1).UsedRange.Find("*", SearchDirection:=xlPrevious)
If Not c Is Nothing Then
  lLastRow = c.Row: lLastCol = c.Column 
Else
  lLastRow = 1: lLastCol = 1
End If
MsgBox "lLastRow=" & lLastRow & " lLastCol=" & lLastCol

В этом примере используется UsedRange, который так же как и SpecialCells возвращает все используемые ячейки, в т.ч. и те, что были использованы ранее, а сейчас пустые. Функция Find ищет ячейку с любым значением с конца диапазона.

Поиск по шаблону (маске)

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

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

With Worksheets(1).Cells
  Set c = .Find("т??т*", LookIn:=xlValues, LookAt:=xlWhole)
  If Not c Is Nothing Then
    firstResult = c.Address
    Do
      c.Font.Color = RGB(255, 0, 0)
      Set c = .FindNext(c)
      If c Is Nothing Then Exit Do
    Loop While c.Address <> firstResult
  End If
End With

Для поиска функцией Find по маске (шаблону) можно применять символы:

* — для обозначения любого количества любых символов;

? — для обозначения одного любого символа;

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

Поиск в скрытых строках и столбцах

Для поиска в скрытых ячейках нужно учитывать лишь один нюанс: поиск нужно осуществлять в формулах, а не в значениях, т. е. нужно использовать LookIn:=xlFormulas

Поиск даты с помощью Find

Если необходимо найти текущую дату или какую-то другую дату на листе Excel или в диапазоне с помощью Find, необходимо учитывать несколько нюансов:

• Тип данных Date в VBA представляется в виде #[месяц]/[день]/[год]#, соответственно, если необходимо найти фиксированную дату, например, 01 марта 2018 года, необходимо искать #3/1/2018#, а не «01.03.2018»
• В зависимости от формата ячеек, дата может выглядеть по-разному, поэтому, чтобы искать дату независимо от формата, поиск нужно делать не в значениях, а в формулах, т.е. использовать LookIn:=xlFormulas

Приведу несколько примеров поиска даты.

Пример 7: Найти текущую дату на листе независимо от формата отображения даты.

d = Date
Set c = Cells.Find(d, LookIn:=xlFormulas, LookAt:=xlWhole)
If Not c Is Nothing Then
  MsgBox "Нашел"
Else
  MsgBox "Не нашел"
End If

Пример 8: Найти 1 марта 2018 г.

d = #3/1/2018#
Set c = Cells.Find(d, LookIn:=xlFormulas, LookAt:=xlWhole)
If Not c Is Nothing Then
  MsgBox "Нашел"
Else
  MsgBox "Не нашел"
End If

Искать часть даты — сложнее. Например, чтобы найти все ячейки, где месяц «март», недостаточно искать «03» или «3». Не работает с датами так же и поиск по шаблону. Единственный вариант, который я нашел — это выбрать формат в котором месяц прописью для ячеек с датами и искать слово «март» в xlValues.

Тем не менее, можно найти, например, 1 марта независимо от года.

Пример 9: Найти 1 марта любого года.

d = #3/1/1900#
Set c = Cells.Find(Format(d, "m\/d\/"), LookIn:=xlFormulas, LookAt:=xlPart)
If Not c Is Nothing Then
  MsgBox "Нашел"
Else
  MsgBox "Не нашел"
End If

Найти и заменить в Excel

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

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

Далее вызываем Главная → Редактирование → Найти и выделить → Найти (кнопка с рисунка выше). Поиск также можно включить с клавиатуры комбинацией клавиш Сtrl+F. Откроется диалоговое окно под названием Найти и заменить.

В единственном поле указывается информация (комбинация символов), которую требуется найти. Если не использовать подстановочные символы или т.н. джокеры (см. ниже), то Excel будет искать строгое совпадение заданных символов. Для вывода результатов поиска предлагается два варианта: выводить все результаты сразу – кнопка Найти все; либо выводить по одному найденному значению – кнопка Найти далее.

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

Если в нижней части окна выделить любое значение и затем нажать Ctrl+A, то в диапазоне поиска будут выделены все соответствующие ячейки.

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

Поиск нестрогого соответствия символов

Иногда пользователь не знает точного сочетания искомых символов что существенно затрудняет поиск. Данные также могут содержать различные опечатки, лишние пробелы, сокращения и пр., что еще больше вносит путаницы и делает поиск практически невозможным. А может случиться и обратная ситуация: заданной комбинации соответствует слишком много ячеек и цель поиска снова не достигается (кому нужны 100500+ найденных ячеек?).

Для решения этих проблем очень хорошо подходят джокеры (подстановочные символы), которые сообщают Excel о сомнительных местах. Под джокерами могут скрываться различные символы, и Excel видит лишь их относительное расположение в поисковой фразе. Таких джокеров два: звездочка «*» (любое количество неизвестных символов) и вопросительный знак «?» (один «?» – один неизвестный символ).

Так, если в большой базе клиентов нужно найти человека по фамилии Иванов, то поиск может выдать несколько десятков значений. Это явно не то, что вам нужно. К поиску можно добавить имя, но оно может быть внесено самым разным способом: И.Иванов, И. Иванов, Иван Иванов, И. И. Иванов и т.д. Используя джокеры, можно задать известную последовательно символов независимо от того, что находится между. В нашем примере достаточно ввести и*иванов и Excel отыщет все выше перечисленные варианты записи имени данного человека, проигнорировав всех П. Ивановых, А. Ивановых и проч. Секрет в том, что символ «*» сообщает Экселю, что под ним могут скрываться любые символы в любом количестве, но искать нужно то, что соответствует символам «и» + что-еще + «иванов». Этот прием значительно повышает эффективность поиска, т.к. позволяет оперировать не точными критериями.

Если с пониманием искомой информации совсем туго, то можно использовать сразу несколько звездочек. Так, в списке из 1000 позиций по поисковой фразе мол*с*м*уход я быстро нахожу позицию «Мол-ко д/сн мак. ГАРНЬЕР Осн.уход д/сух/чув.к. 200мл» (это сокращенное название от «Молочко для снятия макияжа Гараньер Основной уход….»). При этом очевидно, что по фразе «молочко» или «снятие макияжа» поиск ничего бы не дал. Часто достаточно ввести первые буквы искомых слов (которые наверняка присутствуют), разделяя их звездочками, чтобы Excel показал чудеса поиска. Главное, чтобы последовательность символов была правильной.

Есть еще один джокер – знак «?». Под ним может скрываться только один неизвестный символ. К примеру, указав для поиска критерий 1?6, Excel найдет все ячейки содержащие последовательность 106, 116, 126, 136 и т.д. А если указать 1??6, то будут найдены ячейки, содержащие 1006, 1016, 1106, 1236, 1486 и т.д. Таким образом, джокер «?» накладывает более жесткие ограничения на поиск, который учитывает количество пропущенных знаков (равный количеству проставленных вопросиков «?»).

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

Продвинутый поиск

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

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

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

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

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

Первый выпадающий список Искать предлагает ограничить поиск одним листом или расширить его до целой книги.

По умолчанию (если не лезть в параметры) поиск происходит только на активном листе. Для повторения поиска на другом листе все действия нужно проделать еще раз. А если таких листов много, то поиск данных может отнять немало времени. Однако если выбрать пункт Книга, то поиск произойдет сразу по всем листам активной книги. Выгода очевидна.

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

В следующем выпадающем списке находится замечательная возможность поиска по формулам, значениям, а также примечаниям. По умолчанию Excel производит поиск в формулах либо, если их нет, в содержимом ячейки. Например, если искать фамилию Иванов, а фамилия эта есть результат формулы (копируется из соседнего листа), то поиск нечего не даст, т.к. в ячейке нет искомого перечня символов. По той же причине не удастся отыскать число, являющееся результатом работы какой-либо функции. Поэтому бывает смотришь в упор на ячейку, видишь искомое значение, а Excel его почему-то не видит. Это не глюк, это настройка поиска. Измените данный параметр на Значения и поиск будет осуществляться по тому, что отражено в ячейке, независимо от содержимого. Например, если в ячейке содержится результат вычисления 1/6 (как значение, а не формула) и при этом формат отражает только 3 знака после запятой (т.е 0,167), то поиск символов «167» при выборе параметра Формулы эту ячейку не обнаружит (реальное содержимое ячейки — это 0,166666…), а при выборе Значения поиск увенчается успехом (искомые символы совпадают с тем, что отражается в ячейке). И последний пункт в данном списке – Примечания. Поиск осуществляется только в примечаниях. Очень может помочь, т.к. примечания часто скрыты.

В диалоговом окне поиска есть еще две галочки Учитывать регистр и Ячейка целиком. По умолчанию Excel игнорирует регистр, но можно сделать так, чтобы «иванов» и «Иванов» отличались. Галочка Ячейка целиком также может оказаться весьма полезной, если ищется ячейка не с указанным фрагментом, а полностью состоящая из искомых символов. К примеру, как найти ячейки, содержащие только 0? Обычный поиск не подойдет, т.к. будут выдаваться и 10, и 100. Зато, если установить галочку Ячейка целиком, то все пойдет, как по маслу.

Поиск и замена данных

Данные обычно ищутся не просто так, а для каких-то целей. Такой целью часто является замена искомой комбинации (или формата) на другую. Чтобы найти и заменить в выделенном диапазоне Excel одни значения на другие, в окне Найти и заменить необходимо выбрать вкладку Замена. Либо сразу выбрать на ленте команду Главная → Редактирование → Найти и выделить → Заменить.

Еще удобнее применить сочетание горячих клавиш найти и заменить в Excel – Ctrl+H.

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

По аналогии с простым поиском, менять можно и формат.

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

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

А как быстро удалить все разрывы строк? Обычно это делают вручную. Однако ловкое использование поиска и замены сэкономит много времени. Вызываем команду поиска и замены с помощью комбинации Ctrl+H. Теперь в строке поиска нажимаем Ctrl+J — это символ разрыва строки — на экране появится точка. В строке замены указываем, например, пробел.

Жмем Ok. Все переносы строк заменились пробелами.

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

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

Поделиться в социальных сетях:

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

Офис для бизнеса Больше…Меньше

Резюме

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

Создайте образец рабочего листа

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

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

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

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

Функции

ПРОСМОТР()

Функция  ПРОСМОТР  находит значение в одной строке или столбце и сопоставляет его со значением в той же позиции в другой строке или столбце.

Ниже приведен пример синтаксиса формулы ПРОСМОТР:

    =ПРОСМОТР(Искомое_Значение,Искомый_Вектор,Результативный_Вектор)

Следующая формула определяет возраст Марии в образце рабочего листа:

.

    =ПРОСМОТР(E2,A2:A5,C2:C5)

Формула использует значение «Мария» в ячейке E2 и находит «Мария» в векторе поиска (столбец A). Затем формула сопоставляется со значением в той же строке результирующего вектора (столбец C). Поскольку «Мария» находится в строке 4, функция ПРОСМОТР возвращает значение из строки 4 в столбце C (22).

ПРИМЕЧАНИЕ. Функция ПРОСМОТР требует, чтобы таблица была отсортирована.

Для получения дополнительных сведений о функции ПРОСМОТР щелкните следующий номер статьи базы знаний Майкрософт:

Как использовать функцию ПРОСМОТР в Excel

ВПР()

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

Ниже приведен пример синтаксиса формулы VLOOKUP  :

    =ВПР(Искомое_Значение,Таблица_Массив,Количество_индекса_столбца,Диапазон_просмотра)

Следующая формула определяет возраст Марии в образце рабочего листа:

    =ВПР(E2,A2:C5,3,ЛОЖЬ)

Формула использует значение «Мария» в ячейке E2 и находит «Мария» в крайнем левом столбце (столбец A). Затем формула сопоставляется со значением в той же строке в Column_Index. В этом примере используется «3» в качестве Column_Index (столбец C). Поскольку «Мэри» находится в строке 4, ВПР возвращает значение из строки 4 в столбце C (22).

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

Как использовать ВПР или ГПР, чтобы найти точное совпадение

ИНДЕКС() и ПОИСКПОЗ()

Вы можете использовать функции ИНДЕКС и ПОИСКПОЗ вместе, чтобы получить те же результаты, что и при использовании ПРОСМОТР или ВПР .

Ниже приведен пример синтаксиса, который объединяет ИНДЕКС и ПОИСКПОЗ для получения тех же результатов, что и ПРОСМОТР и ВПР в предыдущих примерах:

    =ИНДЕКС(Таблица_Массив,ПОИСКПОЗ(Искомое_Значение,Искомый_Массив,0),Количество_Индекса_Столбца)

Следующая формула определяет возраст Марии в образце рабочего листа:

    =ИНДЕКС(A2:C5,ПОИСКПОЗ(E2,A2:A5,0),3)

Формула использует значение «Мария» в ячейке E2 и находит «Мария» в столбце A. Затем она соответствует значению в той же строке в столбце C. Поскольку «Мария» находится в строке 4, формула возвращает значение из строки 4 в колонке С (22).

ПРИМЕЧАНИЕ.  Если ни одна из ячеек в Lookup_Array не соответствует Lookup_Value («Mary»), эта формула вернет #N/A.
Для получения дополнительных сведений о функции ИНДЕКС щелкните следующий номер статьи базы знаний Майкрософт:

.

Как использовать функцию ИНДЕКС для поиска данных в таблице

СМЕЩ() и ПОИСКПОЗ()

Вы можете использовать функции OFFSET и MATCH вместе, чтобы получить те же результаты, что и функции в предыдущем примере.

Ниже приведен пример синтаксиса, который объединяет OFFSET и MATCH для получения тех же результатов, что и LOOKUP  и VLOOKUP :

    = OFFSET(top_cell,MATCH(Lookup_Value, Lookup_Array,0),Offset_Col)

Эта формула находит возраст Мэри в образце рабочего листа:

    =СМЕЩЕНИЕ(A1,ПОИСКПОЗ(E2,A2:A5,0),2)

Формула использует значение «Мария» в ячейке E2 и находит «Мария» в столбце A. Затем формула сопоставляет значение в той же строке, но на два столбца правее (столбец C). Поскольку «Мария» находится в столбце A, формула возвращает значение в строке 4 столбца C (22).

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

Как использовать функцию СМЕЩЕНИЕ

Поиск значений с помощью ВПР, ИНДЕКС или ПОИСКПОЗ

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

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

Функции ВПР и ГПР вместе с ИНДЕКС и ПОИСКПОЗ являются одними из самых полезных функций в Excel.

Примечание. Мастер поиска больше не доступен в Excel.

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

=ВПР(B2,C2:E7,3,ИСТИНА)

В этом примере B2 — это первый аргумент — элемент данных, необходимый для работы функции. Для ВПР этот первый аргумент — это значение, которое вы хотите найти. Этот аргумент может быть ссылкой на ячейку или фиксированным значением, например «кузнец» или 21 000. Второй аргумент — это диапазон ячеек C2-:E7, в котором нужно искать значение, которое вы хотите найти. Третий аргумент — это столбец в том диапазоне ячеек, который содержит искомое значение.

Четвертый аргумент является необязательным. Введите ИСТИНА или ЛОЖЬ. Если вы введете TRUE или оставите аргумент пустым, функция вернет приблизительное соответствие значению, указанному вами в первом аргументе. Если вы введете FALSE, функция будет соответствовать значению, предоставленному первым аргументом. Другими словами, оставляя четвертый аргумент пустым или вводя значение TRUE, вы получаете больше гибкости.

В этом примере показано, как работает функция. Когда вы вводите значение в ячейку B2 (первый аргумент), функция ВПР ищет ячейки в диапазоне C2:E7 (2-й аргумент) и возвращает наиболее близкое приближенное совпадение из третьего столбца диапазона, столбца E (3-й аргумент).

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

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

Использование ИНДЕКС и ПОИСКПОЗ вместо ВПР

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

В этом примере показан небольшой список, в котором значение, которое мы хотим найти, Чикаго, отсутствует в крайнем левом столбце. Итак, мы не можем использовать функцию ВПР. Вместо этого мы будем использовать функцию ПОИСКПОЗ, чтобы найти Чикаго в диапазоне B1:B11. Оно находится в строке 4. Затем ИНДЕКС использует это значение в качестве аргумента поиска и находит население Чикаго в 4-м столбце (столбец D). Используемая формула показана в ячейке A14.

Дополнительные примеры использования ИНДЕКС и ПОИСКПОЗ вместо ВПР см. в статье https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ Билла Джелена, Microsoft MVP.

Попробуйте

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

ВПР Пример работы

Скопируйте следующие данные в пустую электронную таблицу.

Совет:  Перед вставкой данных в Excel установите ширину столбцов от A до C до 250 пикселей и нажмите Перенос текста (вкладка Главная , группа Выравнивание ).

HLOOKUP Пример

Скопируйте все ячейки этой таблицы и вставьте их в ячейку A1 на пустом листе Excel.

Совет:  Перед вставкой данных в Excel установите ширину столбцов от A до C до 250 пикселей и нажмите Перенос текста ( Главная вкладка, Группа выравнивания ).

Примеры ИНДЕКС и ПОИСКПОЗ

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

Скопируйте все ячейки этой таблицы и вставьте их в ячейку A1 на пустом листе Excel.

Совет: Перед вставкой данных в Excel установите ширину столбцов от A до D до 250 пикселей и нажмите Перенос текста (вкладка Главная , группа Выравнивание ).

Поиск и выбор ячеек, соответствующих определенным условиям

Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Access 2007 Больше…Меньше

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

Выполните следующие действия:

1. Начните с одного из следующих действий:

   • org/ListItem»>

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

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

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

2. В Доме , нажмите Найти и выбрать > Перейти к (в группе Редактирование ).

   Сочетание клавиш:   Нажмите CTRL+G.

3. org/ListItem»>

   Нажмите Специальный .

4. В диалоговом окне Перейти к специальному выберите один из следующих параметров.

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

Excel 2013: основы Cell

Урок 7: основы Cell

/en/excel2013/saving-and-share-workbooks/content/

Введение

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

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

Понимание ячеек

Каждый рабочий лист состоит из тысяч прямоугольников, которые называются ячейками . Ячейка представляет собой пересечение строки и столбца . Столбцы обозначаются цифрой буквы (A, B, C) , а строки обозначаются цифрами (1, 2, 3) .

Ячейка

Каждая ячейка имеет собственное имя — или адрес ячейки — в зависимости от ее столбца и строки. В этом примере выбранная ячейка пересекает столбец C и строку 5 , поэтому адрес ячейки равен C5 . Адрес ячейки также появится в поле Имя . Обратите внимание, что столбца и заголовков строк ячейки — это , выделенные при выборе ячейки.

Ячейка C5

Вы также можете выбрать несколько ячеек одновременно. Группа ячеек известна как диапазон ячеек . Вместо одного адреса ячейки вы будете ссылаться на диапазон ячеек, используя адреса ячеек первых и последних ячеек в диапазоне ячеек, разделенных двоеточием . Например, диапазон ячеек, включающий ячейки A1, A2, A3, A4 и A5, будет записан как 9.1764 А1:А5 .

На изображениях ниже выбираются два различных клеточных диапазона:

• Диапазон ячейки A1: A8

  Диапазон ячейки A1: A8

• Диапазон ячейки A1: B8

  95. 1838.

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

Чтобы выбрать ячейку:

Чтобы ввести или отредактировать содержимое ячейки, сначала необходимо выбрать ячейку.

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

   Выбор одной ячейки

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

Чтобы выбрать диапазон ячеек:

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

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

   Выбор диапазона ячеек

Содержимое ячейки

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

• Текст
  Ячейки могут содержать текст , например буквы, цифры и даты.

  Текст ячейки

• Атрибуты форматирования
  Ячейки могут содержать атрибуты форматирования, которые изменяют способ отображения букв, цифр и дат, . Например, проценты могут отображаться как 0,15 или 15%. Вы даже можете изменить цвет фона ячейки .

  Форматирование ячеек

• Формулы и функции
  Ячейки могут содержать формулы и функции , которые вычисляют значения ячеек. В нашем примере СУММ(B2:B8) складывает значение каждой ячейки в диапазоне ячеек B2:B8 и отображает итог в ячейке B9..

  Формулы ячеек

Чтобы вставить содержимое:

1. Щелкните ячейку , чтобы выбрать ее.

   Выбор ячейки A1

2. Введите content в выбранную ячейку, затем нажмите Введите на клавиатуре. Содержимое появится в ячейке и формуле bar . Вы также можете вводить и редактировать содержимое ячейки в строке формул.

   Вставка содержимого ячейки

Чтобы удалить содержимое ячейки:

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

   Выбор ячейки

2. Нажмите клавишу Delete или Backspace на клавиатуре. Содержимое ячейки будет удалено.

   Удаление содержимого ячейки

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

Для удаления ячеек:

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

1. Выберите ячейки , которые вы хотите удалить.

   Выбор ячейки для удаления

2. Выберите команду Удалить на вкладке Главная на ленте .

   Нажатие команды «Удалить»

3. Ячейки ниже сместятся вверх .

   Ячейки смещены, чтобы заменить удаленную ячейку

Чтобы скопировать и вставить содержимое ячейки:

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

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

   Выбор ячейки для копирования

2. Щелкните команду Копировать на вкладке Главная или нажмите Ctrl+C на клавиатуре.

   Нажатие команды «Копировать»

3. Выберите ячейку (ячейки), куда вы хотите вставить содержимое. Скопированные ячейки теперь будут иметь пунктирную рамку вокруг них.

   Вставка ячеек

4. Щелкните команду Вставить на вкладке Главная или нажмите Ctrl+V на клавиатуре.

   Щелчок по команде «Вставить»

5. Содержимое будет вставлено в выбранные ячейки.

   Вставленное содержимое ячейки

Чтобы вырезать и вставить содержимое ячейки:

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

1. Выберите ячейки , которые вы хотите вырезать .

   Выбор диапазона ячеек для вырезания

2. Щелкните команду Cut на вкладке Home или нажмите Ctrl+X на клавиатуре.

   Щелчок по команде «Вырезать»

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

   Вставка ячеек

4. Щелкните команду Вставить на вкладке Главная или нажмите Ctrl+V на клавиатуре.

   Выбор команды «Вставить»

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

Доступ к дополнительным параметрам вставки:

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

• Чтобы получить доступ к дополнительным параметрам вставки, щелкните стрелку раскрывающегося списка по команде Вставить .

Дополнительные параметры вставки

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

Щелчок правой кнопкой мыши для доступа к параметрам форматирования

Для перетаскивания ячеек:

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

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

   Наведение курсора на границу ячейки

3. Щелкните, удерживайте и перетащите ячейки на желаемое местоположение .

   Перетаскивание выбранных ячеек

4. Отпустите кнопку мыши, и ячейки будут перетащены в выбранное место.

   Отброшенные ячейки

Чтобы использовать дескриптор заполнения:

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

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

   Поиск маркера заполнения

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

   Перетаскивание маркера заполнения

3. Отпустите кнопку мыши, чтобы заполнить выбранные ячейки.

   Заполненные ячейки

Для продолжения ряда с ручкой заполнения:

Ручку заполнения также можно использовать для продолжения ряда . Всякий раз, когда содержимое строки или столбца следует в последовательном порядке, например числа (1, 2, 3) или дня (понедельник, вторник, среда) , дескриптор заполнения может угадать, что должно быть дальше в сериал. Во многих случаях может потребоваться выбрать несколько ячеек перед использованием дескриптора заполнения, чтобы помочь Excel определить порядок ряда. В нашем примере ниже дескриптор заполнения используется для расширения ряда из дат в столбце.

Использование маркера заполнения для расширения серии

Расширенная серия

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

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

1. Откройте существующую книгу Excel 2013. Если вы хотите, вы можете использовать нашу рабочую тетрадь.
2. Выберите ячейку D3. Обратите внимание, как ячейка адрес появляется в поле имени , а его содержимое появляется как в ячейке, так и в строке Formula .
3. Выберите ячейку и попробуйте вставить текст и числа .
4. Удалите ячейку и обратите внимание, как ячейки ниже сдвигаются вверх по , чтобы заполнить его место.
5. Вырежьте ячеек и вставьте их в другое место. Если вы используете пример, вырежьте ячейки D4:D6 и вставьте их в E4:E6 .
6. Попробуйте перетащить и перетащите некоторые ячейки в другие части рабочего листа.
7. Используйте маркер заполнения для заполнения данными соседних ячеек как по вертикали, так и по горизонтали. Если вы используете пример, используйте дескриптор заполнения для , чтобы продолжить серию дат в строке 3.
8. Используйте функцию Найти , чтобы найти содержимое в вашей книге. Если вы используете пример, введите имя Льюис в поле Найдите что: поле.

Продолжать

Предыдущая: Сохранение и совместное использование книг

Next:Изменение столбцов, строк и ячеек

/en/excel2013/modification-columns-rows-and-cells/content/

Определить, содержат ли ячейки определенное значение в Excel

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

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

Разделы:

Найти, содержится ли значение в определенной ячейке

Найти, находится ли значение в диапазоне ячеек

Найти значение, которое отделено от другого текста в ячейке

Примечания

Значение содержится в определенной ячейке

Давайте выясним, находится ли слово red в ячейке A1.

1. Перейдите в пустую ячейку и введите =СЧЁТЕСЛИ(

2. Выберите ячейку A1, ячейку с текстом, а затем введите запятую, чтобы мы могли перейти к следующему аргументу на шаге 3.
3. Введите «*red*»
   
   Обратите внимание на символ * вокруг текста. Это то, что говорит функции искать слово red где-нибудь внутри ячейки. Вы должны поставить этот символ в начале и в конце значения, которое вы ищете.
   Также обратите внимание, что все заключено в двойные кавычки «» . Вы должны заключить все, включая символ * , в двойные кавычки, как в примере.
4. Введите закрывающую скобку ) и нажмите Enter.

   Вы видите 1 в ячейке, потому что функция СЧЁТЕСЛИ подсчитывает, сколько раз она нашла значение в ячейке, и она нашла значение красный один раз в ячейке A1. Если он не найдет значение, он вернет ноль 0 .
   Мы используем этот метод, чтобы определить, находится ли значение в ячейке. В нашем примере здесь не имеет значения, сколько раз значение находится в ячейке, важно только то, что оно там есть.

5. Вот оно!

Вы можете остановиться здесь и использовать эти выходные данные как угодно, или вы можете добавить более наглядное представление этих данных, заключив функцию СЧЁТЕСЛИ с оператором ЕСЛИ и выполнив что-то вроде этого:

Вывод будет таким это:

Вот окончательный код с включенным оператором IF:

=ЕСЛИ(СЧЁТЕСЛИ(A1,"*красный*")>0,"Значение найдено", "Значение не найдено")

Вот код без оператора IF:

= СЧЁТЕСЛИ(A1,"*красный*")

Найти, находится ли значение в диапазоне ячеек

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

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

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

Это будет выглядеть так:

С результатом будет так:

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

Функция СЧЁТЕСЛИ не сообщит вам расположение ячеек с текстом; он только скажет вам, что текст присутствует в диапазоне и сколько раз он появляется.

Более подробное объяснение того, как это работает, см. в предыдущем примере (первом примере) в этом руководстве.

Поиск значения, которое отличается от другого текста в ячейке

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

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

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

=СЧЁТЕСЛИ(A6;"* красный *")

Обратите внимание на пробелы между символами * и значением красного цвета . Это означает, что слово red будет найдено/подсчитано только в том случае, если слева и справа от него внутри ячейки есть пробел, что делает его словом, а не частью слова.

В этом примере он не найдет красный в ячейке A6:

Исходная формула подсчитала бы красный в красный mond как 1.

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

Примечания

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

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

Этот метод не чувствителен к регистру.

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

Поиск определенного текста в Excel

При работе с Excel мы сталкиваемся со многими необычными ситуациями. Одной из таких ситуаций является поиск определенного текста в ячейке. Первое, что приходит на ум, когда мы говорим, что хотим найти конкретный текст на листе, это Метод «Найти и заменить» в Excel, который является самым популярным. Но Ctrl + F может найти нужный текст, но не может выйти за его пределы. Так, например, если ячейка содержит определенные слова, вы можете захотеть, чтобы результат в следующей ячейке был «ИСТИНА» или «ЛОЖЬ». Итак, Ctrl+F останавливается на достигнутом.

Содержание

• Как искать текст в Excel?
  • Какая формула может сказать нам, что ячейка содержит определенный текст?
  • Альтернативы функции НАЙТИ
    • Альтернатива № 1 — функция поиска Excel
    • Альтернатива № 2 — функция Excel Countif
  • Выделение ячейки с определенным текстовым значением
  • Рекомендуемые статьи

, шаблоны и т. д. Пожалуйста, предоставьте нам ссылку на авторство. Как указать авторство?0002 Здесь мы рассмотрим формулы для поиска определенного текста в значении ячейки и получения результата.

Вы можете скачать этот шаблон Excel для поиска текста здесь — Шаблон Excel для поиска текста

Какая формула может сказать нам, что ячейка содержит определенный текст?

Этот вопрос мы много раз видели на форумах Excel. Первой пришедшей на ум формулой была функция «НАЙТИ».

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

1. Например, посмотрите на данные ниже.

2. В приведенных выше данных у нас есть три предложения в трех разных строках. Теперь в каждой ячейке нам нужно найти текст «Лучший». Итак, применяем функцию НАЙТИ.

3. Аргумент «find_text» упоминает текст, который нам нужно найти.

4. Для «внутри_текста» выберите полное предложение, т. е. ссылку на ячейку.

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

   Итак, в двух предложениях у нас есть слово «лучший». Мы видим значение ошибки #ЗНАЧ! в ячейке B2, что показывает, что ячейка A2 не имеет текстового значения «лучшее».

6. Вместо чисел мы также можем ввести результат своими словами. Для этого нам нужно использовать условие ЕСЛИ.

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

Но здесь у нас есть проблема, хотя мы предоставили результат как «Не найдено», если текст все еще не найден, мы получаем значение ошибки как #ЗНАЧ!.

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

Функция ISNUMERIC оценивает, возвращает ли функция FIND число или нет. Если функция НАЙТИ возвращает число, она поставит ИСТИНА для условия ЕСЛИ или ЛОЖЬ для условия. На основе результата, предоставленного функцией ISNUMERIC, условие ЕСЛИ вернет соответствующий результат.

Мы также можем использовать функцию ЕСЛИОШИБКА в excelФункция ЕСЛИОШИБКА в ExcelФункция ЕСЛИОШИБКА в Excel проверяет формулу (или ячейку) на наличие ошибок и возвращает указанное значение вместо ошибки. Узнайте больше, чтобы иметь дело со значениями ошибки вместо ISNUMERIC. Например, приведенная ниже формула также вернет «Не найдено», если функция НАЙТИ возвращает значение ошибки.

Альтернативы функции НАЙТИ

Альтернатива № 1 — функция поиска в Excel

Вместо функции НАЙТИ мы также можем использовать функцию ПОИСК в excel Функция ПОИСК В Excel функция поиска дает позицию подстроки в заданной строке, когда мы даем параметр позиции для поиска. В результате эта формула требует трех аргументов. Первая — это подстрока, вторая — сама строка, а последняя — позиция для начала поиска. Подробнее для поиска определенного текста в строке. Синтаксис функции ПОИСК аналогичен функции НАЙТИ.

Укажите «find_text» как «Лучший».

«within_text» — это ссылка на нашу ячейку.

Даже функция ПОИСК возвращает значение ошибки в виде #ЗНАЧ! Если текст поиска «лучший» не найден. Как мы видели выше, нам нужно заключить формулу с функциями ISNUMERIC или IFERROR.

Альтернатива № 2 — Функция подсчета в Excel

Другой способ поиска определенного текста — использование функции СЧЁТЕСЛИ. Использование функции СЧЁТЕСЛИФункция СЧЁТЕСЛИ в Excel подсчитывает количество ячеек в диапазоне на основе заранее заданных критериев. Он используется для подсчета ячеек, содержащих даты, числа или текст. Например, СЧЁТЕСЛИ(A1:A10,»Козырь») подсчитает количество ячеек в диапазоне A1:A10, содержащих текст «Козырь».
читать далее. Эта функция работает без ошибок.

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

В столбце критериев нам нужно использовать подстановочный знак в excelWildcard В ExcelIn Excel подстановочные знаки — это три специальных символа: звездочка, вопросительный знак и тильда. Звездочка обозначает несколько символов, знак вопроса обозначает один символ, а тильда обозначает идентификацию подстановочного знака. Подробнее, поскольку мы просто находим часть строкового значения, поэтому заключите слово «лучший» в звездочку ( *) подстановочный знак.

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

Мы можем применить только условие ЕСЛИ, чтобы получить результат без ошибок.

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

Если вы не любите формулы, вы можете выделить ячейку с определенным словом. Например, чтобы выделить ячейку со словом «лучший», нам нужно использовать условное форматирование в excel. Условное форматирование в ExcelУсловное форматирование — это метод в Excel, который позволяет нам форматировать ячейки на листе на основе определенных условий. Его можно найти в разделе стилей на вкладке «Главная».