Как проверить список фамилий в excel
Перейти к содержимому

Как проверить список фамилий в excel

  • автор:

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

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

В этой статье

  • Поиск значений по вертикали в списке с использованием точного соответствия
  • Поиск значений по вертикали в списке с использованием приблизительного совпадения
  • Поиск значений по вертикали в списке неизвестного размера с использованием точного соответствия
  • Поиск значений по горизонтали в списке с использованием точного соответствия
  • Поиск значений по горизонтали в списке с использованием приблизительного совпадения
  • Создание формулы подстановки с помощью мастера подстановки (только для Excel 2007)

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

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

Примеры ВПР

=ВПР (B3,B2:E7,2,ЛОЖЬ) ВПР ищет

=ВПР (102,A2:C7,2,ЛОЖЬ) ВПР ищет точное совпадение (ЛОЖЬ) фамилии для 102 (искомое_значение) во втором столбце (столбец B) в диапазоне A2:C7 и возвращает

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

Примеры INDEX и MATCH

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

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

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

Дополнительные сведения см. в разделах Функция ИНДЕКС и ФУНКЦИЯ MATCH.

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

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

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

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

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

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

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

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

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

Пример функций OFFSET и MATCH

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

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

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

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

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

Пример формулы HLOOKUP для поиска точного соответствия

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

Дополнительные сведения см. в разделе Функция HLOOKUP.

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

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

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

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

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

Дополнительные сведения см. в разделе Функция HLOOKUP.

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

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

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

  1. Щелкните ячейку в диапазоне.
  2. На вкладке Формулы в группе Решения щелкните Поиск.
  3. Если команда Подстановка недоступна, необходимо загрузить мастер подстановки надстройка программу. Загрузка надстройки мастера подстановок
  4. Нажмите кнопку Microsoft Office

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

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

Описание

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

Создание образца листа

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

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

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

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

Определение

Вся таблица подстановки

Значение, которое будет найдено в первом столбце аргумента «инфо_таблица».

Просматриваемый_массив
-или-
Лукуп_вектор

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

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

3 (третий столбец в инфо_таблица)

Ресулт_аррай
-или-
Ресулт_вектор

Диапазон, содержащий только одну строку или один столбец. Он должен быть такого же размера, что и просматриваемый_массив или Лукуп_вектор.

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

Это ссылка, на основе которой вы хотите основать смещение. Топ_целл должен ссылаться на ячейку или диапазон смежных ячеек. В противном случае функция СМЕЩ возвращает #VALUE! значение ошибки #ИМЯ?.

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

Функции

LOOKUP ()

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

Ниже приведен пример синтаксиса формулы подСТАНОВКи.

= Просмотр (искомое_значение; Лукуп_вектор; Ресулт_вектор)

Следующая формула находит возраст Марии на листе «образец».

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

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

Примечание. Для функции Просмотр необходимо, чтобы таблица была отсортирована.

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

ВПР ()

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

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

= ВПР (искомое_значение; инфо_таблица; номер_столбца; интервальный_просмотр)

Следующая формула находит возраст Марии на листе «образец».

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

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

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

INDEX () и MATCH ()

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

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

= Индекс (инфо_таблица; MATCH (искомое_значение; просматриваемый_массив; 0); номер_столбца)

Следующая формула находит возраст Марии на листе «образец».

= ИНДЕКС (A2: C5; MATCH (E2; A2: A5; 0); 3)

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

Обратите внимание Если ни одна из ячеек в аргументе «число» не соответствует искомому значению («Мария»), эта формула будет возвращать #N/А.
Чтобы получить дополнительные сведения о функции индекс , щелкните следующий номер статьи базы знаний Майкрософт:

СМЕЩ () и MATCH ()

Функции СМЕЩ и ПОИСКПОЗ можно использовать вместе, чтобы получить те же результаты, что и функции в предыдущем примере.

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

= СМЕЩЕНИЕ (топ_целл, MATCH (искомое_значение; просматриваемый_массив; 0); Оффсет_кол)

Эта формула находит возраст Марии на листе «образец».

= СМЕЩЕНИЕ (A1; MATCH (E2; A2: A5; 0); 2)

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

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

Как проверить список фамилий в excel

Хотелось бы задать вопрос. Имеется ли возможность у excel поиск по нескольким значений. Например имеется список из 50 фамилий но мне необходимо найти эти фамилии в другом столбце где фамилий больше 500.
Искать по одному это долго и нудно можно ли в экселе найти сразу эти 50 фамилий

Пользователь
Сообщений: 3289 Регистрация: 22.12.2012
Win7 MSO 2013
01.03.2013 21:09:27

Что значит сразу? Напротив каждой фио — есть/ нет или например 49 есть, 1 нет — значит не совпало? Или как?

Пользователь
Сообщений: 27 Регистрация: 01.03.2013
02.03.2013 13:45:53

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

Сообщений: 60996 Регистрация: 14.09.2012
Контакты см. в профиле
02.03.2013 13:47:36
2007 и выше — автофильтр.
Пользователь
Сообщений: 27 Регистрация: 01.03.2013
02.03.2013 14:03:06
Тоже вариант но он не всегда устраивает имеется ли готовая формула?
Сообщений: 60996 Регистрация: 14.09.2012
Контакты см. в профиле
02.03.2013 14:06:41
Пользователь
Сообщений: 3289 Регистрация: 22.12.2012
Win7 MSO 2013
02.03.2013 15:03:42
ГИПЕРССЫЛКА — на всякий случай:
Прикрепленные файлы

  • гс.xls (28 КБ)
  • гс.xlsx (8.61 КБ)

Пользователь
Сообщений: 5 Регистрация: 04.07.2017
04.07.2017 14:31:53

У меня аналогичная потребность!
Ситуация следующая:
К примеру, в таблице имеется 5000 уникальных телефонных номеров. Через некоторое время из этого списка мне нужно удалить, ну допустим 100 определенных номеров. Искать каждый номер «ручками» с помощью функции «Найти» CTRL+F, по времени слишком затратно, тем более проделывать это понадобится с несколькими таблицами периодически.
Возможно ли как-то задать в поиске либо выделить требуемые ячейки с нужными значениями для удаления из таблицы или хотя бы выделения искомых значений одновременно?

Поиск отличий в двух списках

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

Вариант 1. Синхронные списки

Если списки синхронизированы (отсортированы), то все делается весьма несложно, т.к. надо, по сути, сравнить значения в соседних ячейках каждой строки. Как самый простой вариант — используем формулу для сравнения значений, выдающую на выходе логические значения ИСТИНА (TRUE) или ЛОЖЬ (FALSE) : difference1.pngЧисло несовпадений можно посчитать формулой:
=СУММПРОИЗВ(—(A2:A20<>B2:B20))
или в английском варианте =SUMPRODUCT(—(A2:A20<>B2:B20)) Если в результате получаем ноль — списки идентичны. В противном случае — в них есть различия. Формулу надо вводить как формулу массива, т.е. после ввода формулы в ячейку жать не на Enter, а на Ctrl+Shift+Enter. Если с отличающимися ячейками надо что сделать, то подойдет другой быстрый способ: выделите оба столбца и нажмите клавишу F5, затем в открывшемся окне кнопку Выделить (Special)Отличия по строкам (Row differences) . В последних версиях Excel 2007/2010 можно также воспользоваться кнопкой Найти и выделить (Find & Select) — Выделение группы ячеек (Go to Special) на вкладке Главная (Home)difference2.pngExcel выделит ячейки, отличающиеся содержанием (по строкам). Затем их можно обработать, например:

  • залить цветом или как-то еще визуально отформатировать
  • очистить клавишей Delete
  • заполнить сразу все одинаковым значением, введя его и нажав Ctrl+Enter
  • удалить все строки с выделенными ячейками, используя команду Главная — Удалить — Удалить строки с листа (Home — Delete — Delete Rows)
  • и т.д.

Вариант 2. Перемешанные списки

Если списки разного размера и не отсортированы (элементы идут в разном порядке), то придется идти другим путем.

Самое простое и быстрое решение: включить цветовое выделение отличий, используя условное форматирование. Выделите оба диапазона с данными и выберите на вкладке Главная — Условное форматирование — Правила выделения ячеек — Повторяющиеся значения (Home — Conditional formatting — Highlight cell rules — Duplicate Values):

difference5.png

Если выбрать опцию Повторяющиеся, то Excel выделит цветом совпадения в наших списках, если опцию Уникальные — различия.

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

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

difference4.png

Полученный в результате ноль и говорит об отличиях.

И, наконец, «высший пилотаж» — можно вывести отличия отдельным списком. Для этого придется использовать формулу массива:

difference6.png

Выглядит страшновато, но свою работу выполняет отлично 😉

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

  • Выделение дубликатов в списке цветом
  • Сравнение двух диапазонов с помощью надстройки PLEX
  • Запрет ввода повторяющихся значений

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *