Как найти значение из одного столбца в Excel

Обновлено: 21.11.2024

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

Что вы хотите сделать?

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

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

Примеры ВПР

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

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

На простом английском языке это означает:

=INDEX(Мне нужно возвращаемое значение из C2:C10, которое будет MATCH(Kale, которое находится где-то в массиве B2:B10, где возвращаемое значение является первым значением, соответствующим Kale))

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

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

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

Важно! Убедитесь, что значения в первой строке отсортированы по возрастанию.

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

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

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

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

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

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

MATCH("Oranges",C2:C7,0) ищет апельсины в диапазоне C2:C7. Вы не должны включать начальную ячейку в диапазон.

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

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

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

HLOOKUP ищет столбец Продажи и возвращает значение из строки 5 в указанном диапазоне.

Дополнительную информацию см. в разделе Функция ГПР.

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

Для выполнения этой задачи используйте функцию ГПР.

Важно! Убедитесь, что значения в первой строке отсортированы по возрастанию.

В приведенном выше примере функция ГПР ищет значение 11000 в строке 3 в указанном диапазоне. Он не находит 11 000 и, следовательно, ищет следующее наибольшее значение, меньшее 1100, и возвращает 10 543.

Дополнительную информацию см. в разделе Функция ГПР.

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

Примечание. Надстройка «Мастер поиска» больше не поддерживается в Excel 2010.Эта функция была заменена мастером функций и доступными функциями поиска и ссылок (ссылка).

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

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

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

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

Как загрузить программу надстройки Lookup Wizard

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

В поле "Управление" нажмите "Надстройки Excel", а затем нажмите "Перейти".

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

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

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

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

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

Проверка наличия значения в столбце с помощью ПОИСКПОЗ

ПОИСКПОЗ (значение, массив, [тип_сопоставления])

=ЕСЛИ(ПОИСКПОЗ(D3,$A$2:$A$17,0),"Да")

=ЕСЛИОШИБКА(ЕСЛИ(ПОИСКПОЗ(D3,$A$2:$A$17,0),"Да"),"Нет")

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

=VLOOKUP (искомое_значение, массив_таблиц, номер_индекса_столбца, [диапазон-поиск])

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

=ВПР(D3,$A$2:$B$17,2,ЛОЖЬ)

=ЕСЛИОШИБКА(ВПР(D3,$A$2:$B$17,2,ЛОЖЬ),"")

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

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

=ЕСЛИОШИБКА(ЕСЛИ(ВПР(D3,$A$2:$B$17,2,ЛОЖЬ),"Да"),"Нет")

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

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

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

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

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

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

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

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

<р>1. Во-первых, вы можете скопировать два столбца данных и вставить их в столбец A и столбец C отдельно на новом листе, оставьте столбец B пустым, чтобы ввести следующую формулу.

<р>2. Введите эту формулу: = ЕСЛИ (ЕОШИБКА (ВПР (A4, $ C $ 4: $ C $ 14, 1, ЛОЖЬ)), «Не существует», «Существует») в ячейку B2 и нажмите клавишу Enter, см. снимок экрана:

Примечание. В приведенной выше формуле A4 — это ячейка первого столбца, которую вы хотите проверить, находится ли она в другом столбце, и $C$4:$C$14 это столбец, с которым вы хотите сравнить.

<р>3. Затем выберите ячейку B4 и перетащите дескриптор заполнения в ячейки, которые вам нужны для применения этой формулы, вы получите следующий результат: Exist означает значение в списке A, которое также существует в списке B, а Not Exist указывает значение нет в списке Б.

Kutools для Excel Функция «Выбор одинаковых и разных ячеек» может помочь вам выбрать и выделить одинаковые и разные значения ячеек между двумя столбцами по мере необходимости. Нажмите, чтобы скачать Kutools for Excel!

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

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

<р>1. Выберите ячейки в списке A, которые вы хотите выделить, а также ячейки, в которых значения выходят из списка B.

<р>2. Затем нажмите «Главная» > «Условное форматирование» > «Новое правило», в диалоговом окне «Новое правило форматирования» нажмите «Использовать формулу», чтобы определить, какие ячейки следует форматировать в разделе «Выбор типа правила», а затем введите эту формулу =ПОИСКПОЗ(A4,$C$4 :$C$14,0) в текстовое поле Формат значений, где эта формула является истинной, см. снимок экрана:

<р>3. Затем нажмите кнопку «Формат», чтобы перейти в диалоговое окно «Формат ячеек», и выберите нужный цвет на вкладке «Заливка», см. снимок экрана:

<р>4. Затем нажмите OK > OK, чтобы закрыть диалоговые окна, и теперь вы можете видеть, что значения как в списке A, так и в списке B выделены из списка A, см. снимок экрана:

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

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

После установки Kutools for Excel сделайте следующее:

<р>1. Нажмите Kutools > Выбрать > Выбрать одинаковые и разные ячейки, см. снимок экрана:

<р>2. В диалоговом окне «Выбор одинаковых и разных ячеек» выполните следующие операции:

A: Нажмите кнопку в разделе «Найти значения в: (диапазон A)», чтобы выбрать столбец данных, который вы хотите сравнить с другим столбцом. И нажмите кнопку в разделе Согласно: (Диапазон B), чтобы выбрать ячейки, с которыми вы хотите сравнить.

B: выберите "Каждая строка" в разделе "На основе".

C: укажите одинаковые значения в разделе "Найти".

D: Вы можете заполнить цвет фона или цвет шрифта для тех же значений, которые вам нужны, в разделе «Обработка результатов». Выберите цвет, который вам нравится.

<р>3. После завершения настроек нажмите кнопку «ОК». Все значения в столбцах A и C будут затенены в столбце A, как показано на следующем снимке экрана:

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

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

Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно бесплатно попробовать без ограничений в течение 30 дней. Скачать и бесплатно попробовать прямо сейчас!

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

В этом руководстве мы увидим использование ВПР и ИНДЕКС/ПОИСКПОЗ для проверки существования одного значения из одного столбца в другом столбце.

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

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

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

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

Пример использования ПОИСКПОЗ

Чтобы решить проблему в предыдущем примере с ПОИСКПОЗ(), необходимо выполнить следующие шаги:

  1. Выберите ячейку C2, нажав на нее.
  2. Вставьте формулу в строку формул "=НЕ(ЕОШИБКА(ПОИСКПОЗ(A2,$B$2:$B$1001,0)))".
  3. Нажмите Enter, чтобы назначить формулу C2.

Excel сопоставит записи в столбце A с записями в столбце B. Если совпадение есть, он вернет номер строки. Функции НЕ() и ЕОШИБКА() проверяют наличие ошибки, и в столбце C будет указано "Истина" для совпадения и "Ложь", если совпадения нет.

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

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

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

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

  1. Выберите ячейку C2, нажав на нее.
  2. Вставьте формулу в строку формул "=ЕСЛИ(ЕОШИБКА(ВПР(A2,$B$2:$B$1001,1,ЛОЖЬ)),ЛОЖЬ,ИСТИНА)".
  3. Нажмите Enter, чтобы назначить формулу C2.

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

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

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

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

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

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

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

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

Это сообщение не ответило на ваш вопрос? Получите решение, связавшись с экспертом.

Я пытаюсь выполнить формулу vlookup в столбце N, чтобы сопоставить значения в столбце L со значениями в столбце B и вернуть значение из столбца E. Данные в столбцах A:F обновляются с помощью макроса. Данные в формате J:N используются в отчете. Я знаю, что проблема в том, что дата и время в столбце B имеют дополнительный пробел между ними, поэтому vlookup не работает. Но я не знаю, как это решить? Данные, которые макрос берет с другого рабочего листа, не содержат этого дополнительного места.

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

У меня есть столбец данных, скажем, в P:P, я использовал формулу =ПРОСМОТР(2,1/(НЕ(ПУСТО(P:P))),P:P), чтобы вернуть последнее значение в этом данные. Однако теперь мне нужна другая формула для возврата другого значения из того же выбора, который не является результатом приведенной выше формулы. Вы не знаете, могу ли я сделать это с помощью формул?

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

Читайте также: