Примечание. Мы стараемся предоставлять вам самые последние справочные материалы на вашем языке как можно быстрее. Эта страница переведена автоматически, поэтому может содержать неточности и грамматические ошибки. Нам важно, чтобы эта статья была вам полезна. Пожалуйста, уделите несколько секунд и сообщите нам, помогло ли это вам, используя кнопки внизу страницы. Для вашего удобства мы также предоставляем ссылку на оригинал).
Описание
В этой статье приведены пошаговые инструкции о том, как найти данные в таблице (или диапазоне ячеек) с помощью различных встроенных функций Microsoft Excel. Вы можете использовать разные формулы для получения одного и того же результата.
Создание образца листа
В этой статье используется пример листа для иллюстрации встроенных функций Excel. Рассмотрим пример ссылки на имя из столбца A и возврата возраста этого человека из столбца C. Чтобы создать эту электронную таблицу, введите следующие данные в пустую электронную таблицу Excel.
Введите значение, которое вы хотите найти, в ячейку E2. Вы можете ввести формулу в любую пустую ячейку на том же листе.
Определения терминов
В этой статье используются следующие термины для описания встроенных функций в Excel.
Вся таблица поиска
Значение найдено в первом столбце аргумента «info_table».
Lookup_array
-или-
Search_vector
Диапазон ячеек, содержащий возможные значения поиска.
Номер столбца в аргументе info_table, для которого будет возвращено совпадающее значение.
3 (третий столбец в info_table)
Массив_результат
-или-
Вектор_результата
Диапазон, содержащий только одну строку или один столбец. Он должен быть того же размера, что и lookup_array или lookup_vector.
Логическое значение (истина или ложь). Если значение true или опущено, возвращается приблизительное совпадение. Если установлено значение FALSE, будет выполнен поиск точного совпадения.
Это ссылка, на которой вы хотите основывать прокрутку. Top_cell должен ссылаться на соседнюю ячейку или диапазон ячеек. В противном случае функция СМЕЩ возвращает #ЗНАЧ! значение ошибки #ИМЯ?.
Количество столбцов, слева или справа от которых должна быть указана верхняя левая ячейка результата. Например, значение «5» для аргумента offset_column указывает, что верхняя левая ячейка ссылки имеет пять столбцов справа от ссылки. Offset_count может быть положительным (т е справа от начальной ссылки) или отрицательным (т е слева от начальной ссылки).
Функции
LOOKUP ()
Функция поиска находит значение в одной строке или столбце и сравнивает его со значением в той же позиции в другой строке или столбце.
Ниже приведен пример синтаксиса формулы ПРОСМОТР.
= Поиск (искомое_значение, искомый_вектор, результирующий_вектор)
Следующая формула определяет возраст Мэри на листе «образца».
= ПРОСМОТР(E2, A2: A5, C2: C5)
Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в векторе поиска (столбец A). Затем формула сопоставляется со значением в той же строке результирующего вектора (столбец C). Поскольку «Мэри» находится в строке 4, поиск возвращает значение из строки 4 в столбце C (22).
См также: Мбр что это такое
Примечание. Функция Browse требует, чтобы таблица была отсортирована.
Для получения дополнительных сведений о функции предварительного просмотра щелкните следующий номер статьи базы знаний Майкрософт:
Функция ВПР или вертикального поиска используется, когда данные находятся в столбцах. Эта функция находит значение в левом столбце и сравнивает его с данными в указанном столбце в той же строке. Вы можете использовать функцию ВПР для поиска данных в отсортированных или несортированных таблицах. В следующем примере используется таблица с несортированными данными.
Ниже приведен пример синтаксиса формулы ВПР :
= ВПР(искомое_значение, информационная_таблица, номер_столбца, диапазонный_поиск)
Следующая формула определяет возраст Мэри на листе «образца».
=ВПР(E2,A2:C5,3,ЛОЖЬ)
Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в левом столбце (столбец A). Затем формула будет соответствовать значению в той же строке в Column_Index. В этом примере используется «3» в качестве Column_Index (столбец C). Поскольку «Мария» находится в строке 4, функция ВПР возвращает значение из строки 4 в столбце C (22).
Для получения дополнительных сведений о функции ВПР щелкните следующий номер статьи базы знаний Майкрософт:
INDEX () и MATCH ()
Вы можете использовать функции ИНДЕКС и ПОИСКПОЗ вместе, чтобы получить те же результаты, что и при поиске или ВПР.
Ниже приведен пример синтаксиса, который объединяет индекс и совпадение для получения тех же результатов поиска и ВПР, что и в предыдущих примерах:
=ИНДЕКС(информационная_таблица;ПОИСКПОЗ(искомое_значение;искомый_массив;0);номер_столбца)
Следующая формула определяет возраст Мэри на листе «образца».
=ИНДЕКС(A2:C5,ПОИСКПОЗ(E2,A2:A5,0),3)
Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в столбце A. Затем оно будет соответствовать значению в той же строке в столбце C. Поскольку «Мария» находится в строке 4, формула возвращает значение строки 4 в столбце C(22).
Примечание. Если ни одна из ячеек в числовом аргументе не соответствует искомому значению («Мэри»), эта формула вернет #Н/Д.
Для получения дополнительных сведений о функции индексирования щелкните следующий номер статьи базы знаний Майкрософт:
СМЕЩ () и MATCH ()
Функции OFFSET и MATCH можно использовать вместе, чтобы получить те же результаты, что и функции в предыдущем примере.
Ниже приведен пример синтаксиса, который сочетает смещение и сопоставление для достижения того же результата, что и функция просмотра и ВПР.
=OFFSET(top_cell, MATCH(lookup_value, lookup_array, 0), offset_count)
Эта формула определяет возраст Мэри на листе «образца».
=СМЕЩЕНИЕ(A1,СООТВЕТСТВИЕ(E2,A2:A5,0),2)
Формула использует значение «Мария» в ячейке E2 и ищет слово «Мария» в столбце A. Затем формула сопоставляет значение в той же строке, но на два столбца правее (столбец C). Поскольку «Мария» находится в столбце A, формула возвращает значение из строки 4 столбца C (22).
Для получения дополнительных сведений о функции СМЕЩ щелкните следующий номер статьи базы знаний Майкрософт:
Нахождение в MS EXCEL позиции n-го вхождения символа в слове
Найти позицию третьего, четвертого и т д появления символа в слове (в текстовой строке).
Поиск позиции последнего повтора MS EXCEL
Найдите позицию последнего повторения в списке повторяющихся числовых или текстовых значений.
Выделение ячеек c ТЕКСТом с применением Условного форматирования в MS EXCEL
Выберите ячейки, содержащие искомый текст. Рассмотрим разные варианты: выделение ячеек, содержащих значения, точно совпадающие с искомым текстом; выбор ячеек, содержащих искомый текст в начале, конце или середине строки. Мы также узнаем, как выбрать ячейку, если ее значение соответствует одному из нескольких заданных значений.
Есть ли слово в списке MS EXCEL
Давайте искать слово в диапазоне ячеек, которое удовлетворяет критериям: точное соответствие критериям, совпадение с учетом регистра, совпадение только части символов в слове и т д
Поиск ТЕКСТовых значений в MS EXCEL с выводом их в отдельный список. Часть2. Подстановочные знаки
Найдем текстовые значения, удовлетворяющие заданным пользователем критериям. Критерии задаются с помощью подстановочных знаков. Поиск будет производиться в диапазоне с повторяющимися значениями. При наличии повторений можно ожидать, что критерий будет соответствовать нескольким значениям. Для их вывода в отдельный диапазон удобно использовать формулы массива.
Поиск ТЕКСТовых значений в MS EXCEL с выводом их в отдельный список. Часть1. Обычный поиск
Найдем текстовые значения, удовлетворяющие заданным пользователем критериям. Поиск будет производиться в диапазоне с повторяющимися значениями. При наличии повторений можно ожидать, что критерий будет соответствовать нескольким значениям. Для их вывода в отдельный диапазон удобно использовать формулы массива.
Поиск позиции ТЕКСТа в MS EXCEL с выводом значения из соседнего столбца
Чтобы найти положение значения в столбце, а затем отобразить соответствующее значение в соседнем столбце в EXCEL, существует специальная функция ВПР(), но для ее решения можно использовать и другие функции. Рассмотрим проблему в случае текстовых значений.
Выделение ячеек c ТЕКСТом с применением Условного форматирования (с учетом РЕгиСТра) в MS EXCEL
Выберите ячейки, содержащие искомый текст, с учетом регистра. Рассмотрим разные варианты: выделение ячеек, содержащих значения, точно совпадающие с искомым текстом; выбор ячеек, содержащих искомый текст в начале, конце или середине строки.
Поиск ТЕКСТовых значений в MS EXCEL с выводом их в отдельный список. Часть3. Поиск с учетом РЕГИСТРА
Поиск текстовых значений, удовлетворяющих заданным пользователем критериям, чувствителен к регистру. Поиск будет производиться в диапазоне с повторяющимися значениями. При наличии повторений можно ожидать, что критерий будет соответствовать нескольким значениям. Для их вывода в отдельный диапазон удобно использовать формулы массива.
Поиск позиции ТЕКСТа с учетом РЕгиСТра в MS EXCEL и выводом значения из соседнего столбца
Именно здесь развиваются идеи статьи Нахождение позиции значения ТЕКСТ с выводом соответствующего значения соседнего столбца. Для нахождения положения значения с учетом регистра с последующим выводом соответствующего значения из соседнего столбца нельзя применить функцию ВПР(), поскольку она не чувствительна к регистру. Задачу можно решить с помощью функций БИЗВЕТ(), НАЙТИ() или ПОИСКПОЗ() .
Поиск в MS EXCEL текстового значения первого или последнего по алфавиту
Найдем текстовое значение, которое будет отображаться первым при сортировке диапазона по возрастанию, т.е первым по алфавиту. Также находим последнее значение по алфавиту.
Разнесение в MS EXCEL ТЕКСТовых значений и ЧИСЕЛ по разным спискам
Если исходный список содержит и текст, и числа, то с помощью формул массива можно выделить все текстовые значения в одном списке и числовые значения в другом.
Поиск в MS EXCEL третьего (n-го) повтора в списках с ТЕКСТом
Рассмотрим поиск текстовых значений в списке с повторениями. Задав в качестве критериев поиска нужное текстовое значение и количество его повторов в списке, мы найдем номер строки, содержащей этот повтор, а также выделим его и другие повторы с помощью условного форматирования.
О классификации задач Поиска в EXCEL
В данной статье описаны попытки классификации задач по поиску значений в MS EXCEL. Статья может быть интересна продвинутым пользователям EXCEL.
Поиск нескольких значений с максимальной длиной строки в MS EXCEL
Поищем в исходном списке текстовые значения, имеющие максимальную длину строки, и выведем их в отдельный диапазон.
Поиск значения в MS EXCEL с максимальной или минимальной длиной строки
Найдем строку с максимальной/минимальной длиной в списке текстовых значений.
Предположим, вы получили полную таблицу со списком товаров от продавца/покупателя/покупателя:
и эту таблицу следует сравнить с номерами позиций/кодами продуктов в вашем каталоге продукции:
Как видите, в нашем каталоге есть только товары без названия. У заказчика, помимо артикулов, есть еще и наименование товара, то есть много лишнего И ему нужно понимать, какие товары есть в его каталоге, а какие нет:
В Excel нет стандартных формул для такого поиска и сравнения. Конечно, можно попробовать применить подстановочный знак ВПР сначала к одной таблице, а затем к другой. Но если такую операцию приходится выполнять снова и снова, прописывать несколько формул для каждой таблицы — откровенно не комильфо.
Поэтому я решил сегодня продемонстрировать формулу, которая без дополнительных манипуляций поможет сделать такое сравнение. Чтобы убедиться в этом, рекомендую скачать файл:
Загрузить файл:
Читайте также: Характеристики Asus hd 7770 1gb gddr5
Tips_All_AnyoneOfArray.xls (49,5 КБ, 14 496 загрузок)
В листе «Заказ» этого файла находится таблица, полученная от клиента, а в листе «Каталог» наши товары.
Сама формула в файле примера будет выглядеть так:
=ПРОСМОТР(2,1/ПРОСМОТР(Каталог!$A$2:$A$11, A2),Каталог!$A$2:$A$11)
=ПРОСМОТР(2,1/ПРОСМОТР(Каталог!$A$2:$A$11,A2),Каталог!$A$2:$A$11)
эта формула вернет имя элемента, если текст содержит хотя бы один элемент каталога, и #N/A (#N/A), если элемента нет в каталоге.
Прежде чем мы облагородим эту формулу всевозможными дополнениями (вроде удаления ненужных #N/A), давайте выясним, как она работает.
Функция ПРОСМОТР ищет заданное значение (2) в указанном диапазоне (вторым аргументом является массив). Диапазон обычно представляет собой массив ячеек, но функция ПРОСМОТР имеет первое необходимое нам свойство: она пытается преобразовать любое выражение, записанное в качестве второго аргумента, непосредственно в массив. Другими словами, он вычисляет выражение в этом аргументе, которое мы используем, подставляя выражение в качестве второго аргумента: 1/SEARCH(Directory!$A$2:$A$11;A2) . Часть SEARCH(Catalog!$A$2:$A$11,A2) по очереди ищет каждое значение в списке Каталог в ячейке A2 (имя таблицы Customer). Если значение найдено, возвращается номер позиции первого символа найденного значения. Если значение не найдено, возвращается значение ошибки #ЗНАЧ!. (#СТОИМОСТЬ!). Теперь вторая особенность: функция требует, чтобы данные располагались в массиве в порядке возрастания. Если данные расположены по-другому, то функция будет искать в массиве до тех пор, пока не найдет значение большее, чем искомое, но максимально близкое к нему (хотя, если данные это позволяют, то лучше отсортировать список по возрастанию порядок) для более точного поиска). Поэтому сначала мы делим 1 на выражение ПРОСМОТР(Каталог!$A$2:$A$11;A2), чтобы получить массив вида: список лучше отсортировать по возрастанию) для более точного поиска). Поэтому сначала мы делим 1 на выражение ПРОСМОТР(Каталог!$A$2:$A$11;A2), чтобы получить массив вида: список лучше отсортировать по возрастанию) для более точного поиска). Поэтому сначала мы делим 1 на выражение ПРОСМОТР(Каталог!$A$2:$A$11;A2), чтобы получить массив вида:
Ну и в качестве искомого значения подсовываем в функцию число 2, заведомо большее число, чем обычно может встречаться в массиве (потому что единица, деленная на любое число, будет меньше двух). И в результате мы получим позицию в массиве, где происходит последнее совпадение каталога. После этого функция ПРОСМОТР запомнит эту позицию и вернет значение массива Directory!$A$2:$A$11 (третий аргумент), записанного в этот массив для данной позиции.
Вы можете посмотреть шаги по расчету функции самостоятельно для каждой ячейки, я просто приведу шаги здесь в немного расширенном виде для вашего понимания:
Читайте также: 5151 Что такое подписка
Теперь давайте немного улучшим функцию и сделаем еще пару реализаций
Реализация 1:
Вместо SKU и #N/A мы будем отображать «Да» для найденных позиций и «Не найдено в каталоге» для отсутствующих позиций» :
=ЕСЛИ(КОНЕЦ(ПОИСК(2,1/ПОИСК(Каталог!$A$2:$A$11,A2))),»Не найдено в каталоге»,»Да»)
=ЕСЛИ(ISNA(ПОИСК(2,1/ПОИСК(Каталог!$A$2:$A$11,A2))),»Не найдено в каталоге»,»Да»)
работа функции проста: мы открыли ПОИСК, поэтому остались только UND и IF.
ISNA возвращает TRUE, если содержащее выражение возвращает значение ошибки #Н/Д, и FALSE, если содержащее выражение не возвращает значение ошибки.
ЕСЛИ возвращает значение второго аргумента, если выражение первого аргумента равно ИСТИНА (TRUE), и значение третьего аргумента, если выражение первого аргумента равно ЛОЖЬ (FALSE) .
Реализация 2:
Вместо #Н/Д мы покажем «Не найдено в каталоге», но если элементы найдены, мы покажем названия этих элементов:
=ЕСЛИ.ОШИБКА(ПРОСМОТР(2,1/ПОИСК(Каталог!$A$2:$A$11,A2),Каталог!$A$2:$A$11,»Нет в каталоге»)
=ЕСЛИ.ОШИБКА(ПРОСМОТР(2,1/ПРОСМОТР(Каталог!$A$2:$A$11,A2),Каталог!$A$2:$A$11,»Нет в каталоге»)
Подробно я рассматривал функцию ЕСЛИОШИБКА в этой статье: Как вывести 0 вместо ошибки в ячейке с формулой.
Короче говоря, если выражение, заданное первым аргументом функции, возвращает значение какой-либо ошибки, то функция вернет то, что написано во втором аргументе (в нашем случае это текст «Не найдено в каталоге»). Если выражение не возвращает ошибку, то функция ЕСЛИОШИБКА запишет значение, которое выражение получило в первом аргументе (в нашем случае это будет название статьи).
Реализация 3
Нужно не только определить, какому артикулу он соответствует, но и вывести цену артикула на этот артикул (сами цены должны располагаться в колонке B листа Каталога):
=ЕСЛИ.ОШИБКА(ПРОСМОТР(2,1/ПРОСМОТР(Каталог!$A$2:$A$11,A2),Каталог!$B$2:$B$11);»»)
=ЕСЛИ.ОШИБКА(ПРОСМОТР(2,1/ПРОСМОТР(Каталог!$A$2:$A$11,A2),Каталог!$B$2:$B$11),»»)
Пара важных замечаний:
- данные файла элемента данных не должны содержать пустых ячеек. В противном случае с большой долей вероятности формула вернет значение пустой ячейки, а не той, которая соответствует условиям поиска
- формула ищет таким образом, что ищется любое совпадение. Например, цифра 1 пишется как артикул, а в строке имени кроме полной 1 могут быть еще 123, 651123, FG1412NM и т д. Для всех этих статей можно выбрать статью 1, поскольку она включена во все заголовки. Как правило, это может произойти, если пункт 1 находится в конце списка
Поэтому удобно перед использованием формулы отсортировать список по возрастанию (от меньшего к большему, от А до Я).
