Вот как правильно используется функция Excel
У вас есть большой объем данных и вам срочно нужна информация об определенном значении, которое введено в вашу таблицу? На этом этапе имеет смысл использовать справочную функцию Excel. Это связано с тем, что он позволяет определять требуемую информацию с использованием критерия поиска и областей ячеек, в которых необходимо искать требуемое значение. Это работает автоматически и позволяет быстро запрашивать информацию. Если вы хотите выполнить более точный поиск, вам следует использовать улучшенные справочные функции Vreference, Wreference или XVreference. Excel предлагает дополнительные альтернативы для организации и поиска данных.
Как используется справочная функция Excel
Справочная функция Excel полезна для поиска значения в столбце или строке, которое находится в той же позиции в другой строке или столбце. Ссылка может использоваться, с одной стороны, в векторной версии, а с другой - в матричной версии. Векторная версия используется, когда необходимо выполнить поиск столбца или строки по определенному критерию поиска. В матричной версии можно искать несколько столбцов и строк или весь рабочий лист. Однако для этой цели есть улучшенные функции Vreference и Wreference. Программа позволяет использовать матричную версию только из соображений совместимости с другими программами для работы с электронными таблицами.
Как структурирована справочная функция Excel в векторной версии?
Справочная функция Excel состоит из трех аргументов и имеет следующую структуру: Ссылка (критерий поиска, вектор поиска, [вектор результатов]).
- Критерий поиска является обязательным аргументом. Здесь вводится значение для поиска. Значение может быть числом, текстом, логическим значением, именем или второй ссылочной функцией.
- Вектор поиска - второй обязательный аргумент формулы. Здесь указывается область столбца или строки, в которой следует искать значение поиска. Это также может быть текст, числа или значения истинности.
- Вектор результата: Квадратные скобки вокруг третьего аргумента, вектора результата, указывают на то, что этот аргумент является необязательным. Здесь можно указать другую область, которая должна содержать столько же элементов, сколько второй аргумент.
Поиск по столбцам - пример
У вас есть список номеров артикулов в столбце A с соответствующими предметами одежды в столбце B. Вы ищете конкретный номер артикула и хотите узнать, к какому предмету одежды подходит этот номер.
Выберите пустую строку.
Вставьте формулу = ссылка ("ART7492031"; A2: A6; B2: B6).
Таким образом, функция ищет номер артикула в столбце A и выводит результат из столбца B, например, «Всего».
Ссылочный синтаксис формулы в матричной версии имеет меньше аргументов.
В матричной функции справочная функция Excel состоит из справочника (критерия поиска, матрицы). Он не содержит каких-либо необязательных аргументов, а содержит два обязательных.
- Критерий поиска является первым обязательным аргументом и может состоять из числа, текста или логических значений, имени или какой-либо другой ссылочной функции.
- матрица - это диапазон ячеек, используемый для сравнения. Здесь также можно найти числа, строки или логические значения.
Как уже упоминалось, рекомендуется использовать другие справочные функции Excel Vreference и Wreference, поскольку они обладают большей функциональностью. Например, вы можете указать номер столбца или индекс столбца, который содержит возвращаемое значение.
Поиск по столбцам - векторная версия: пример
Если в векторной версии предполагается та же ситуация, что и в приведенном выше примере, действуйте аналогичным образом.
Выберите пустую строку.
Вставьте формулу = ссылка («ART7492031»; A2: B6).
Таким образом, функция ищет номер артикула в столбцах от A до B и в качестве результата выводит соответствующий предмет одежды («общий»).
Это следует учитывать при использовании эталонной функции.
Ценности в Вектор поиска обе версии должны быть расположены в порядке возрастания. Это означает, например, что для чисел -1, 0, 1, 2, 3, и т. д. для текстовых значений А, Б, В, и т. д. и для значений истинности неправильно с последующим правда. В тексте регистр не учитывается. Если значение не может быть найдено в соответствии с критерием поиска, справочная функция Excel принимает значение Вектор поисканаиболее близкое к значению критерия поиска. Если значение критерия поиска меньше, чем значение в области поиска, Excel возвращает значение ошибки #NV.
Быстрый поиск с помощью этих трех справочных функций Excel
Пользователи, которые не используют функцию ссылки Excel по причинам совместимости, должны использовать Sreference, Wreference или улучшенную версию XLOOKUP. Они позволяют более точный поиск значений с использованием, например, подстановочных знаков.
Поиск в столбцах или таблице по ссылке
Справочная функция Excel ВПР ищет значение в строках, используя матрицу и индекс столбца. Здесь важно, чтобы данные были организованы таким образом, чтобы значение поиска было рядом с возвращаемым значением. Однако значения также могут быть присвоены таким образом, чтобы они принадлежали определенным категориям или группам. Эта процедура экономит время при оценке таблицы.
Vreference может не только выполнять поиск по целым таблицам, но также может сочетаться с другими функциями. Например, можно искать одновременно по нескольким критериям. Если вы хотите использовать справочную функцию Excel для нескольких связанных листов, вы можете получать сообщения об ошибках, но их можно быстро решить с помощью настроек. Однако значение ошибки будет #NV отображается, это ошибка приложения. Это сообщение об ошибке Excel можно перехватить с другими формулами.
Используйте ссылку в качестве справочной функции Excel и выполняйте поиск по строкам
В отличие от VLOOKUP, HLOOKUP выполняет поиск по горизонтали. Таким образом, эта справочная функция Excel ищет столбец за столбцом в определенной строке для критерия поиска на листе. Функция HLOOKUP имеет следующие особенности:
- 3 обязательных аргумента (Критерий поиска; Матрица; Индекс строки)
- 1 необязательный аргумент (Area_reference)
- Конкретный поиск в таблице уровней
- Использование подстановочных знаков в качестве критериев поиска
Поиск по всем направлениям позволяет XVreference
Microsoft 365 имеет улучшенную справочную функцию Excel XLOOKUP, которая сочетает в себе функции ВПР и ГПР. С помощью формулы можно выполнять поиск на листе по критерию поиска независимо от того, где расположен столбец результатов. Эта справочная функция немного сложнее с 6 аргументами, но она позволяет пользователям Excel выполнять подробный и динамический поиск во всех направлениях.
Обзор других матричных формул для оценки таблицы
Уместно ли учитывать прописные и строчные буквы при оценке данных? В этом случае может помочь более сложная формула массива, которую не могут выполнять ссылочные функции. Справочные функции Excel - не единственное средство запроса информации в столбцах или строках, которые программа имеет в своем репертуаре. Например, индексная функция может выводить значения или ссылки из ячеек. Требуемые данные можно считать, указав переменную позицию ячейки или используя номера столбцов и строк.С помощью ссылки функции сравнения пользователи Excel также могут извлекать значения из пересечений строк и столбцов. Диапазоны ячеек также можно использовать для определения последней записи в списке Excel.
Другие функции Excel, которые могут помочь с организацией и оценкой информации:
- Row () - это функция Excel, которую можно использовать для установки номеров строк на листе. Например, вы можете нумеровать списки Excel пошагово по два. Эта формула может быть даже встроена в другую формулу и может автоматически продолжить нумерацию строк, как только пустая ячейка получит новое значение.
- Indirect () помогает вам разделять и упорядочивать многострочные данные в столбцы или считывать содержимое через адреса ячеек.
- Address () - это еще одна матричная формула, которую можно использовать для определения адреса ячейки определенного значения. Интегрируя другие функции, вы можете расширить функцию и, например, определить позицию наибольшего числа в списке Excel.
- В сочетании с другими формулами Hyperlink () может не только выводить точную позицию значения, но и одновременно сохранять ссылку на позицию.
Справочная функция Excel позволяет быстро запрашивать информацию
Независимо от того, должны ли данные определяться из определенного столбца, из найденных строк или выводится значение результата для связанного поискового значения, ссылочные функции в Excel можно использовать различными способами. Пользователи могут выбирать между справочной функцией Excel, которая совместима с другими программами для работы с электронными таблицами, или более обширными функциями Vreference или Wreference. Для опытных пользователей XVerweis подходит для более целенаправленного поиска, который сочетает в себе функции SVerweis и Wreweis. Другие функции Excel можно использовать для организации данных, например для автоматической нумерации строк или возврата позиции значения в ячейке.