Как найти номер ячейки в excel по значению ячейки: Функция ПОИСКПОЗ() в 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? Функция строка и функция столбец в Эксель

Как посчитать количество пустых ячеек Excel? Функция СЧИТАТЬПУСТОТЫ

Стиль ссылок R1C1 в Excel. Когда вместо букв в столбцах цифры

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

Функция СТРОКА() и СТОЛБЕЦ(). Как использовать?

Интересная особенность этих формул, что они могут работать как с реквизитами, так и без них. Т.е. формула =СТРОКА() будет возвращать номер именно этой строки, а =СТРОКА(A3) будет возвращать номер строки ячейки A3, соответственно 3.

Подробнее на примере

Номер строки и столбца для списка или таблицы

Довольно часто функция СТРОКА используется для нумерации данных в списке, достаточно только сопоставить номер строки и номер позиции списка, в данном случае делаем -1

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

Тоже самое можно провернуть для нумерации столбцов.

Как посчитать количество столбцов в выделенном диапазоне столбцов?

Если вы выделяете не диапазон ячеек, а диапазон, к примеру столбцов, необязательно пользоваться какой-то формулой, чтобы посчитать сколько столбцов в диапазоне. При выделение диапазона, количество столбцов (еще раз обращу внимание, если вы выделяете именно диапазон столбцов) автоматически считается и показывается. Буква С означает, что это считаются колонки -Colomn

Это очень удобно, когда вы создаете формулу с функцией ВПР.

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

Функция СТРОКА() в условном форматировании. Как сделать зебру в таблице — чередование цветов заливки?

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

Задайте в окне формул условного форматирования:

=НЕЧЁТ(СТРОКА())=СТРОКА()

Выберете цвет

И получилось удобное оформление

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

Пример файла Номер строки и столбца

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

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

Как посчитать количество пустых ячеек Excel? Функция СЧИТАТЬПУСТОТЫ

Стиль ссылок R1C1 в Excel. Когда вместо букв в столбцах цифры

Поиск позиции элемента в списке с ПОИСКПОЗ (MATCH)

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

Синтаксис этой функции следующий:

=ПОИСКПОЗ(Что_ищем; Где_ищем; Режим_поиска)

где

• Что_ищем — это значение, которое надо найти
• Где_ищем — это одномерный диапазон или массив (строка или столбец), где производится поиск
• Режим_поиска — как мы ищем: точно (0), с округлением в большую строну (-1) или в меньшую сторону (1)

Давайте рассмотрим несколько полезных вариантов ее применения на практике.

Точный поиск

Классический сценарий — поиск точного текстового совпадения для нахождения позиции нужного нам текста или числа в списке:

Поиск первой или последней текстовой ячейки

Если в качестве искомого значения задать звездочку, то функция будет искать первую ячейку с текстом и выдавать её позицию. Для поиска последней текстовой ячейки можно изменить третий аргумент Режим_поиска с нуля на минус 1:

Числа и пустые ячейки в этом случае игнорируются.

Поиск ближайшего числа или даты

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

Например, нам нужно выбрать генератор из прайс-листа для расчетной мощности в 47 кВт. Если последний аргумент задать равным 1 и отсортировать таблицу по возрастанию, то мы найдем ближайшую наименьшую по мощности модель (Зверь):

Если же третий аргумент равен -1 и таблица отсортирована по убыванию, то мы найдем ближайшую более мощную модель (Бомба):

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

Очень часто функция ПОИСКПОЗ используется в связке с другой крайне полезной функцией — ИНДЕКС (INDEX), которая умеет извлекать данные из диапазона по номеру строки-столбца, реализуя, фактически, «левый ВПР».

Так, в предыдущем примере получить не номер, а название модели генератора можно очень легко:

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

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

Ссылки по теме

Нахождение номера позиции ячейки по ее значению.

Добрый день.

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

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

Для примера приведем таблицу с перечнем товаров и стоимости на них.

Товары расположены по увеличению цены на них .

Необходимо узнать на каком месте по стоимости находится товары под названием «Игрушка «Синий трактор»», «Конфеты «Красная книга»» и Набор «Бумажный цветок».

Для нахождения порядкового номера перечисленных позиций следует воспользоваться функцией «ПОИСКПОЗ()»

Для этого:

• Устанавливаем курсор в ячейку которой присваивается значение;
• Выбираем функцию «ПОИСКПОЗ()»;
• Указываем искомое значение или ссылку на него;
• Указываем диапазон поиска;
• Нажимаем «ОК»

Как найти номер строки всех вхождений текстового значения в столбце excel

Я действительно совсем не знаком с excel, так что я очень ценю вашу помощь.

Здесь я хочу найти номера строк первого вхождения текстового значения «dll_ctrl0», индекс второй строки и т. д…

Например, на рисунке ниже мне нужно выражение, которое возвращало бы первое вхождение (35), другое выражение, которое возвращало бы второе (36), и так далее…

До сих пор мне удавалось найти индекс строки вхождения FIRST, используя выражение:
=MATCH("dll_ctrl0",'random_sheet_name'!C:C,0) .
Это возвращает 35. Мне также нужно выражение, которое вернет 36, то, которое вернет 37 (и т. д.), Все это соответствует изображению ниже.

excel

Поделиться

Источник

StarLlama    

31 марта 2018 в 01:11

2 ответа

• как найти номер строки значения

  Я импортирую значения (value1, Value2, Value3 и Value4) 4 значения из файла CSV. Затем я открываю файл Excel, в котором уже заполнено много других параметров. Теперь я пытаюсь найти совпадающее значение value1 в столбце 7 и найти номер строки. Затем я заполняю остальные 3 значения до cloumn 12,13…

• Подсчитайте количество вхождений каждой строки в столбце

  У меня есть таблица excel, которая содержит информацию об ошибках, содержащих такие столбцы , как Product , Owner и т. д. Столбец Product содержит значения (строки), такие как PTS , SDE и т. д. Как найти количество вхождений каждого из продуктов (PTS или SDE)? Например, следующая таблица -…

2

Скажем, у нас есть такие данные, как:

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

 Springfield

В D2 введите:

=MATCH($D$1,A:A,0)

В D3 введите:

=MATCH($D$1,INDEX(A:A,D2+1):INDEX(A:A,9999),0)+D2

и скопируйте D3 вниз:

Как только мы попадем в ошибку, мы сможем остановиться. Каждая формула опирается на приведенную выше формулу. Поскольку в формулах используются только функции MATCH() и INDEX() , этот подход можно использовать как с более старыми, так и с более новыми версиями Excel.

Поделиться

Gary’s Student    

31 марта 2018 в 01:54

0

Попробуй,

=aggregate(15, 6, row($1:$999)/('random_sheet_name'!C$1:C$999="dll_ctrl0"), row(1:1))

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

Поделиться

Unknown    

31 марта 2018 в 01:46

Похожие вопросы:

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

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

Как найти индексы всех вхождений одной строки в другую в JavaScript?

Я пытаюсь найти позиции всех вхождений строки в другую строку, не чувствительную к регистру. Например, учитывая строку: I learned to play the Ukulele in Lebanon. и строка поиска le , я хочу получить…

В Excel как найти значение в столбце и получить все значения в этой строке с помощью C#

Я пытаюсь найти значение в столбце C и получить совпадающее имя ячейки, например C14, теперь как я могу выбрать значения в строке 14. Я попробовал как : private static MyObject GetRowValue(int…

как найти номер строки значения

Я импортирую значения (value1, Value2, Value3 и Value4) 4 значения из файла CSV. Затем я открываю файл Excel, в котором уже заполнено много других параметров. Теперь я пытаюсь найти совпадающее…

Подсчитайте количество вхождений каждой строки в столбце

У меня есть таблица excel, которая содержит информацию об ошибках, содержащих такие столбцы , как Product , Owner и т. д. Столбец Product содержит значения (строки), такие как PTS , SDE и т. д. Как…

как получить текущий номер строки или индекс ячейки в laravel excel maatwebsite

Теперь я пытаюсь найти, как получить текущий номер строки или идентификатор ячейки в laravel excel , но я не могу его найти , мне нужно получить текущий номер строки или идентификатор ячейки, потому…

Excel найти максимальное значение в столбце, если задан номер столбца

Я пытаюсь найти максимальное значение в данном столбце, используя формулы excel, если номер столбца (например, 2, 3, 4) был указан в ячейке A2. Например, если номер столбца в A2 равен 2, то формула…

Подсчет количества вхождений каждого значения в столбце excel

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

Как найти последний номер строки в столбце VBA?

Я пытаюсь найти последний номер строки в столбце на определенном листе VBA Excel. Номер строки должен быть помещен в var для последующего использования. Заранее спасибо! Dim i As Integer Dim x As…

Как найти столбец для всех текстовых совпадений в столбце A и sum все соответствующие значения в столбце B Excel

Я уверен, что это возможно, но я пробовал различные способы в течение последних 2 часов и не могу найти решение. Мне нужно найти все экземпляры текстового имени в столбце А, найти все числовые…

Поиск на листе 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

10 формул Excel, которые пригодятся каждому / TeachMePlease

Не нужно тратить время на подсчёты в Microsoft Excel вручную, ведь существует множество формул, которые помогут быстро справиться с поставленными задачами и повысить точность ваших отчётов. Мы собрали 10 наиболее полезных формул, которые вы сможете выучить за один день.

СУММ

Формула:

=СУММ(число1; число2)

=СУММ(адрес_ячейки1; адрес_ячейки2)

=СУММ(адрес_ячейки1:адрес_ячейки6)

Англоязычный вариант: =SUM(5; 5) или =SUM(A1; B1) или =SUM(A1:B5)

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

С помощью формулы вы можете:

• посчитать сумму двух чисел c помощью формулы: =СУММ(5; 5)
• посчитать сумму содержимого ячеек, сссылаясь на их названия: =СУММ(A1; B1)
• посчитать сумму в указанном диапазоне ячеек, в примере во всех ячейках с A1 по B6: =СУММ(A1:B6)

СЧЁТ

Формула: =СЧЁТ(адрес_ячейки1:адрес_ячейки2)

Англоязычный вариант: =COUNT(A1:A10)

Данная формула подсчитывает количество ячеек с числами в одном ряду. Если вам необходимо узнать, сколько ячеек с числами находятся в диапазоне c A1 по A30, нужно использовать следующую формулу: =СЧЁТ(A1:A30).

СЧЁТЗ

Формула: =СЧЁТЗ(адрес_ячейки1:адрес_ячейки2)

Англоязычный вариант: =COUNTA(A1:A10)

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

ДЛСТР

Формула: =ДЛСТР(адрес_ячейки)

Англоязычный вариант: =LEN(A1)

Функция ДЛСТР подсчитывает количество знаков в ячейке. Однако, будьте внимательны – пробел также учитывается как знак.

СЖПРОБЕЛЫ

Формула: =СЖПРОБЕЛЫ(адрес_ячейки)

Англоязычный вариант: =TRIM(A1)

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

Мы добавили лишний пробел после фразы “Я люблю Excel”. Формула СЖПРОБЕЛЫ убрала его, в этом вы можете убедиться, взглянув на количество знаков с использованием формулы и без.

ЛЕВСИМВ, ПСТР и ПРАВСИМВ

Формула:

=ЛЕВСИМВ(адрес_ячейки; количество знаков)

=ПРАВСИМВ(адрес_ячейки; количество знаков)

=ПСТР(адрес_ячейки; начальное число; число знаков)

Англоязычный вариант: =RIGHT(адрес_ячейки; число знаков), =LEFT(адрес_ячейки; число знаков), =MID(адрес_ячейки; начальное число; число знаков).

Эти формулы возвращают заданное количество знаков текстовой строки. ЛЕВСИМВ возвращает заданное количество знаков из указанной строки слева, ПРАВСИМВ возвращает заданное количество знаков из указанной строки справа, а ПСТР возвращает заданное число знаков из текстовой строки, начиная с указанной позиции.

Мы использовали ЛЕВСИМВ, чтобы получить первое слово. Для этого мы ввели A1 и число 1 – таким образом, мы получили «Я».

Мы использовали ПСТР, чтобы получить слово посередине. Для этого мы ввели А1, поставили 3 как начальное число и затем ввели число 6 – таким образом, мы получили «люблю» из фразы «Я люблю Excel».

Мы использовали ПРАВСИМВ, чтобы получить последнее слово. Для этого мы ввели А1 и число 6 – таким образом, мы получили слово «Excel» из фразы «Я люблю Excel».

ВПР

Формула: =ВПР(искомое_значение; таблица; номер_столбца; тип_совпадения)

Англоязычный вариант: =VLOOKUP (искомое_значение; таблица; номер_столбца; тип_совпадения)

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

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

1. В первом списке данные записаны с А1 по В13, во втором – с D1 по Е13.
2. В ячейке B17 поставим формулу: =ВПР(B16; A1:B13; 2; ЛОЖЬ)

• B16 = искомое значение, то есть паспортные данные. Они имеются в обоих списках.
• A1:B13 = таблица, в которой находится искомое значение.
• 2 – номер столбца, где находится искомое значение.
• ЛОЖЬ – логическое значение, которое означает то, что вам требуется точное совпадение возвращаемого значения. Если вам достаточно приблизительного совпадения, указываете ИСТИНА, оно также является значением по умолчанию.

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

ЕСЛИ

Формула: =ЕСЛИ(логическое_выражение; «текст, если логическое выражение истинно; «текст, если логическое выражение ложно»)

Англоязычный вариант: =IF(логическое_выражение; «текст, если логическое выражение истинно; «текст, если логическое выражение ложно»)

Когда вы проводите анализ большого объёма данных в Excel, есть множество сценариев для взаимодействия с ними. В зависимости от каждого из них появляется необходимость по‑разному воздействовать на данные. Функция «ЕСЛИ» позволяет выполнять логические сравнения значений: если что‑то истинно, то необходимо сделать это, в противном случае сделать что‑то ещё.

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

В примере с ВПР у нас был доход в столбце B и имя человека в столбце E. Мы можем поместить квоту в столбце C, а следующую формулу – в ячейку D1:

=ЕСЛИ(B1>C1; «Норма выполнена»; «Норма не выполнена»)

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

СУММЕСЛИ, СЧЁТЕСЛИ, СРЗНАЧЕСЛИ

Формула: =СУММЕСЛИ(диапазон; условие; диапазон_суммирования) =СЧЁТЕСЛИ(диапазон; условие)

=СРЗНАЧЕСЛИ(диапазон; условие; диапазон_усреднения)

Англоязычный вариант: =SUMIF(диапазон; условие; диапазон_суммирования), =COUNTIF(диапазон; условие), =AVERAGEIF(диапазон; условие; диапазон_усреднения)

Эти формулы выполняют соответствующие функции – СУММ, СЧЁТ, СРЗНАЧ, если выполнено заданное условие.

Формулы с несколькими условиями – СУММЕСЛИМН, СЧЁТЕСЛИМН, СРЗНАЧЕСЛИМН – выполняют соответствующие функции, если все указанные критерии соответствуют истине.

Используя функции на предыдущем примере, мы можем узнать:

Формула «СУММЕСЛИ»

СУММЕСЛИ – общий доход только для продавцов, выполнивших норму.

Формула «СРЗНАЧЕСЛИ»

СРЗНАЧЕСЛИ – средний доход продавца, если он выполнил норму.

Формула «СЧЁТЕСЛИ»

СЧЁТЕСЛИ – количество продавцов, выполнивших норму.

Конкатенация

Формула: =(ячейка1&» «&ячейка2)

=ОБЪЕДИНИТЬ(ячейка1;» «;ячейка2)

За этим причудливым словом скрывается объединение данных из двух и более ячеек в одной. Сделать объединение можно с помощью формулы конкатенации или просто вставив символ & между адресами двух ячеек. Если в ячейке A1 находится имя «Иван», в ячейке B1 – фамилия «Петров», их можно объединить с помощью формулы =A1&» «&B1. Результат – «Иван Петров» в ячейке, где была введена формула. Обязательно оставьте пробел между » «, чтобы между объединёнными данными появился пробел.

Формула конкатенации даёт аналогичный эффект и выглядит так: =ОБЪЕДИНИТЬ(A1;» «; B1) или в англоязычном варианте =concatenate(A1;» «; B1).

Кстати, все перечисленные формулы можно применять и в Google‑таблицах.

Эта статья является лишь верхушкой айсберга в изучении Excel. Для профессионального использования программы рекомендуем учится у профессионалов на курсах по Microsoft Excel.

Найдите значения с помощью VLOOKUP, INDEX или MATCH

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

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

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

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

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

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

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

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

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

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

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

Использование INDEX и MATCH вместо VLOOKUP

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

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

Дополнительные примеры использования INDEX и MATCH вместо VLOOKUP см. В статье https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ Билла Джелен, MVP Microsoft.

Попробуйте

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

VLOOKUP Пример в работе

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

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

HLOOKUP Пример

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

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

Примеры ИНДЕКСА и СООТВЕТСТВИЯ

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

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

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

Краткая справочная карта: обновление ВПР

Функции поиска и ссылки (ссылка)

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

Поиск местоположения данных с помощью функции ПОИСКПОЗ в Excel

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

Инструкции в этой статье относятся к Excel 2019, 2016, 2013, 2010 и 2007; Excel для Microsoft 365; Excel Online; Excel для Mac; Excel для iPad; Excel для iPhone; и Excel для Android.

Синтаксис функции ПОИСКПОЗ

Синтаксис функции относится к структуре функции и включает имя функции, скобки, разделители запятых и аргументы. Синтаксис функции ПОИСКПОЗ:

= ПОИСКПОЗ ( Lookup_value ,  Lookup_array ,  Match_type )
 

data-type = «code»>

Аргументы функции MATCH

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

Это аргументы функции ПОИСКПОЗ:

Lookup_value

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

Lookup_array

Lookup_array (обязательно) — это диапазон ячеек, в которых выполняется поиск.

Match_type

Match_type (необязательно) сообщает Excel, как сопоставить Lookup_value со значениями в Lookup_array.Значение по умолчанию для этого аргумента — 1. Возможные варианты: -1, 0 или 1.

• Если Match_type равно 1 или опущено, MATCH находит наибольшее значение, которое меньше или равно Lookup_value. Данные Lookup_array должны быть отсортированы в порядке возрастания.
• Если Match_type равно 0, MATCH находит первое значение, в точности равное Lookup_value. Данные Lookup_array можно сортировать в любом порядке.
• Если Match_type равно -1, MATCH находит наименьшее значение, которое больше или равно Lookup_value.Данные Lookup_array должны быть отсортированы в порядке убывания.

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

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

Чтобы ввести функцию ПОИСКПОЗ и аргументы:

1. Откройте пустой лист Excel и введите данные в столбцы C, D и E, как показано на изображении ниже.Оставьте ячейку D2 пустой, так как эта конкретная ячейка будет размещать функцию.

2. Выберите ячейку D2 , чтобы сделать ее активной.

3. Выберите вкладку Формулы в меню ленты .

4. Выберите Поиск и ссылка , чтобы открыть раскрывающийся список Функция .

5. Выберите ПОИСКПОЗ в списке, чтобы открыть диалоговое окно Аргументы функции .(В Excel для Mac открывается построитель формул .)

6. Поместите курсор в текстовое поле Lookup_value .

7. Выберите ячейку C2 на листе, чтобы ввести ссылку на ячейку.

8. Поместите курсор в текстовое поле Lookup_array .

9. Выделите ячеек от E2 до E7 на листе, чтобы ввести диапазон.

10. Поместите курсор в текстовое поле Match_type .

11. Введите в эту строку число 0 , чтобы найти точное совпадение с данными в ячейке D3.

12. Выберите OK для завершения функции. (В Excel для Mac выберите Готово .)

13. Число 5 отображается в ячейке D3, поскольку термин Gizmos является пятым элементом сверху в инвентарном списке.

14. Когда вы выбираете ячейку D3, полная функция отображается в строке формул над листом.

    = ПОИСКПОЗ (C2; E2: E7,0) 
     

Объединить MATCH с другими функциями Excel

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

• Аргумент col_index_num для ВПР.
• Аргумент row_num для функции ИНДЕКС.

Спасибо, что сообщили нам!

Расскажите, почему!

Другой

Недостаточно подробностей

Трудно понять

Проверить, существует ли одно значение столбца в другом столбце

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

В этом руководстве мы увидим использование VLOOKUP и INDEX / MATCH, чтобы проверить, существуют ли значения из одного столбца в другом столбце.

Проверить, существует ли одно значение столбца в другом столбце

В следующем примере вы будете работать с набором данных инвентаризации автомобильных запчастей. Столбец A содержит доступные детали, а столбец B содержит все необходимые детали. В столбце A 115 записей , а в столбце B 1001 записей . Мы обсудим несколько способов сопоставления записей в столбце A с записями в столбце B. В столбце C будет выведено «True», если совпадение есть, и «False», если нет.

Проверьте, существует ли одно значение столбца в другом столбце, используя MATCH

Вы можете использовать функцию ПОИСКПОЗ () , чтобы проверить, существуют ли значения в столбце A также в столбце B.MATCH () возвращает позицию ячейки в строке или столбце. Синтаксис MATCH (): = MATCH (lookup_value, lookup_array, [match_type]) . Используя ПОИСКПОЗ, вы можете искать значение как по горизонтали, так и по вертикали.

Пример использования MATCH

Чтобы решить проблему в предыдущем примере с MATCH (), вам необходимо выполнить следующие шаги:

1. Выберите ячейку C2, щелкнув по ней.
2. Вставьте формулу в " = NOT (ISERROR (MATCH (A2, $ B $ 2: $ B $ 1001,0))) " строку формул.
3. Нажмите Enter, чтобы присвоить формулу C2.

4. Перетащите формулу вниз в другие ячейки в столбце C, щелкнув и перетащив маленький значок «+» в правом нижнем углу C2.

Excel сопоставит записи в столбце A с записями в столбце B. Если есть совпадение, он вернет номер строки. Функции NOT () и ISERROR () проверяют наличие ошибки, и в столбце C будет отображаться «True» для совпадения и «False», если совпадение не является совпадением.

Проверить, существует ли одно значение столбца в другом столбце с помощью ВПР

ВПР — это одна из функций поиска и ссылок в Excel и Google Таблицах, используемых для поиска значений в указанном диапазоне с помощью «row.«Он сравнивает их по строкам, пока не найдет совпадение. В этом руководстве мы рассмотрим, как использовать ВПР для нескольких столбцов с несколькими критериями. Синтаксис VLOOKUP: = VLOOKUP (значение, table_array, col_index, [range_lookup]) .

Пример использования ВПР

Вы можете проверить, существуют ли значения в столбце A в столбце B, с помощью ВПР.

1. Выберите ячейку C2, щелкнув по ней.
2. Вставьте формулу в « = IF (ISERROR (VLOOKUP (A2; $ B $ 2: $ B $ 1001,1; FALSE)), FALSE, TRUE) » в строке формул.
3. Нажмите Enter, чтобы присвоить формулу C2.

4. Перетащите формулу вниз в другие ячейки в столбце C, щелкнув и перетащив маленький значок « + » в правом нижнем углу C2.

Это позволит Excel найти все значения в столбце A и сопоставить их со значениями столбца B. В столбце C теперь будет отображаться «True», если соответствующая ячейка в столбце A имеет совпадение в столбце B, «False» если совпадения нет.

MATCH против VLOOKUP — что лучше?

Между ПОИСКПОЗ и ВПР лучший вариант — ПОИСКПОЗ.Не только формула проще, но и ПОИСКПОЗ быстрее, чем ВПР, когда дело доходит до производительности.

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

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

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

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

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

Вам все еще нужна помощь с функцией ВПР? Ознакомьтесь с нашим исчерпывающим обзором руководств по функциям ВПР здесь.

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

Build Professional — Нерушимый — Формы в Excel

45 учебных пособий — 5+ часов — загружаемые файлы Excel

Мгновенный доступ! — Пожизненный доступ!

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

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

Разделов:

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

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

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

Банкноты

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

Выясним, есть ли слово красный в ячейке A1.

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

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

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

5. Вот и все!

Вы можете остановиться здесь и использовать этот вывод как хотите, или вы можете добавить более наглядное представление этих данных, окружив функцию COUNTIF оператором IF и выполнив что-то вроде этого:

Результат будет таким:

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

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

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

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

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

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

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

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

Получится такой результат:

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

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

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

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

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

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

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

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

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

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

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

Банкноты

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

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

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

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

Отображение содержимого ячейки в другой ячейке в Excel

Ссылка на диапазон (несколько ячеек)

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

Получение значений из нескольких ячеек в Excel

Часто мы относим несколько ячеек к единой ячейке. Мы можем ссылаться на несколько ячеек и диапазонов в Excel, чтобы объединить текст или выполнить вычисления.

Получение текста из нескольких ячеек

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

= E2 и F2

Объединить текст из нескольких ячеек:

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

= E2 & ”” & F2

Получение значений из нескольких ячеек:

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

= B2 * C2

Объединение значений нескольких ячеек:

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

= «Общая стоимость» & B2 * C2

Обращение к данным из диапазона ячеек

В следующем примере показано обращение к диапазону и выполнение математических вычислений. Он будет ссылаться на диапазон от D1 до E5 и поместит сумму данных в диапазон A1

.

= СУММ (D1: E5)

Ссылка на ячейки с другого листа

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

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

= Данные! A1

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

= ’Технические данные’! A1

Ссылки и объединение ячеек

Мы можем объединить ячейки, используя оператор Concatenate или функцию CONCATENATE в Excel.Давайте посмотрим на оба метода объединения ячеек в Excel.

Использование оператора объединения : Вы можете ссылаться на ячейки с помощью адреса ячейки и использовать оператор & (амперсанд) для объединения ячеек. Например, следующая формула объединит ячейки (D1 и E1).

= D1 и E1

‘OR

= D1 & ”“ & E1

Использование функции CONCATENATE : мы можем использовать функцию Excel для ссылки и объединения нескольких ячеек и диапазонов в Excel.Вы используете, (запятую) t0, чтобы указать ссылку на каждую ячейку в формуле.

= СЦЕПИТЬ (D1, E1)

Ссылка на именованный диапазон

Мы можем ссылаться на именованные диапазоны в Excel с помощью формулы. Имя диапазона должно следовать за оператором =, чтобы ссылаться на именованный диапазон в Excel. Следующая формула относится к именованному диапазону, называемому «TotalSales».

= Всего продаж

Будет отображено значение ячейки с именем «TotalSales».

В следующих примерах показано, как ссылаться на диапазон ячеек в Excel.Диапазон («Столбец продаж») используется в функции СУММ для возврата итогового значения именованного диапазона «Столбец продаж».

= СУММ («Столбец продаж»)

Отображение значений ячеек на фигуре

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

• Выберите форму
• Щелкните панель формул
• Введите оператор = (присвоение)
• И введите требуемый адрес ячейки (например: = $ A $ 3)

Отображение значения ячейки в заголовке диаграммы

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

• Выберите диаграмму, на которой вы хотите отобразить значение ячейки
• Перейдите на вкладку «Дизайн диаграммы» на ленте.
• И отобразить заголовок диаграммы из элементов диаграммы
• Щелкните заголовок выбранной диаграммы
• Щелкните панель формул
• Введите оператор = (присвоение)
• И введите требуемый адрес ячейки (например: = Sheet2! $ K $ 8)

Excel: почему Excel не может найти число?

Psst, Excel! Попробуйте заглянуть под указатель мыши.Стратегия

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

За кнопкой «Параметры» скрываются важные настройки. Эти настройки часто приводят к сбою поиска. Предположим, у вас есть расчет налога с продаж в столбце D.Ячейка D3 показывает 70,81 как результат формулы. По умолчанию Excel ищет формулы, а не значения. Если вы попробовали «Найти», не меняя формулы на значения, то не найдете 70,81 доллара.

Поиск по тексту формул немного утомляет. Как часто вы говорите себе: «Ого, интересно, в какой ячейке я использовал функцию SQRTPI?» Но еще больше раздражают другие настройки, такие как Match Case и Match Complete Cell Contents. Эти настройки могут быть полезны, но если вы изменили их в 8:04 a.м. сегодня и с тех пор не закрывали Excel, даже если вы открыли и закрыли 40 других книг и работаете над чем-то совершенно другим, Excel запомнит эту предыдущую настройку. Вы часто будете уязвлены странными настройками, оставленными ранее днем, или даже настройкой, измененной, когда макрос пытался использовать команду «Найти» с включенным «Сопоставить содержимое всей ячейки».

Так почему же Excel не видит значение 1354,80 на рис. 95? Excel отображает ячейку C16 с символом валюты и запятой, и чтобы найти ячейку, вам нужно найти 1354 доллара.80! Поскольку сильная сторона Excel — это числа, довольно неприятно, что Excel работает именно так. Но когда вы это поймете, вы сможете обойти это.

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

Дополнительные сведения. Удивительно, но Excel может находить ячейки, которые отображаются в виде числовых знаков (#) вместо чисел. Допустим, у вас есть столбец, в котором 5% чисел отображаются как #####.

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

1. Выберите диапазон чисел. Нажмите Ctrl + F, чтобы открыть диалоговое окно «Найти».
2. Введите ### в диалоговом окне «Найти».
3. Если диалоговое окно не отображает параметры, нажмите кнопку «Параметры».
4. Убедитесь, что для параметра «Поиск» задано значение «Значения» и не отмечен флажок «Соответствовать содержимому всей ячейки».
5. Вместо того, чтобы нажимать «Найти», нажмите «Найти все».Excel добавляет в диалоговое окно новый раздел со списком всех ячеек, содержащих ###.
6. Когда фокус все еще находится в диалоговом окне, нажмите Ctrl + A. Это выберет все ячейки в нижней части диалогового окна «Найти все».

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

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

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

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

Функция ИНДЕКС может использоваться для СОВПАДЕНИЯ поискового значения в диапазоне ячеек.

Функцию

ИНДЕКС можно использовать двумя способами: ИНДЕКС, ссылка и массив ИНДЕКС

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

Форма массива возвращает более одного значения за раз. При использовании индексного массива нажмите ctrl + shift + enter вместо клавиши ввода.

Синтаксис

= ИНДЕКС (массив; номер_строки; [номер_столбца)

= ИНДЕКС (ссылка; номер_строки; [номер_столбца]; [номер_площади])

Пример 1:

= ИНДЕКС ((A2: A7); 2,3)

A2: A7 — это диапазон ячеек для поиска значения.

2 — номер строки в диапазоне ячеек, в котором находится значение.

3 — номер столбца в диапазоне значений. В таблице

3 столбца

Функция ИНДЕКС возвращает число 26, которое является точкой пересечения второй строки и третьего столбца.

При использовании массива INDEX нажмите клавиши CRTL + SHIFT + ENTER.

Пример 2: Из приведенной выше таблицы используйте данные для поиска текста в столбце A и возврата относительной абсолютной ссылки на ячейку.

Перейдите в ячейку A10 и введите Мэри

.

В ячейке B10 введите формулу = ЯЧЕЙКА («АДРЕС», ИНДЕКС (($ A $ 2: $ A $ 7, MATCH (A10, $ A $ 2: $ A $ 7,0)))

Нажмите клавишу ввода

Из приведенного выше примера ячейки B10 возвращает ссылку на ячейку с текстом Mary.

Ячейка A2: A7 — это диапазон столбцов с текстом подстановки, A10 — это текст подстановки.

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

Пример 3:
Формула для возврата номера строки значения ячейки в таблице.

Найдите сотрудника по имени Джон и верните адрес ячейки.

В ячейке B10 введите формулу = МАЛЕНЬКИЙ (ЕСЛИ ($ A $ 10 = $ A $ 2: $ A $ 8, ROW ($ A $ 2: $ A $ 8) -ROW ($ A $ 2) +1), ROW (1: 1) )) и нажмите Shift + Ctrl + Enter ключей

Перетащите маркер автозаполнения вниз, чтобы скопировать формулу до #NUM! Появляется .

Нравится:

Нравится Загрузка…

.