Как использовать функции поиска и ссылки в Excel 2016

Отредактировано 2 Несколько недель назад от ExtremeHow Редакционная команда

Microsoft OfficeExcelПоискФункции сопоставленияДанныеЭлектронная таблицаВычислениеФормулыWindowsMacПродуктивность

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

Понимание функции Lookup

Функции поиска в Excel предназначены для того, чтобы помочь вам быстро найти информацию, хранящуюся в рабочем листе. Эти функции полезны при работе с большими объемами данных, поскольку позволяют вам находить конкретную информацию без необходимости вручную искать её. Наиболее распространённые функции поиска — это VLOOKUP, HLOOKUP, LOOKUP, а также INDEX и MATCH.

Функция VLOOKUP

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

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

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value: Значение, которое вы хотите найти.
• table_array: Диапазон ячеек, составляющих массив таблицы.
• col_index_num: Номер столбца в массиве таблицы, из которого вы хотите извлечь данные.
• range_lookup: Логическое значение, определяющее, следует ли искать приблизительное совпадение (true) или точное совпадение (false).

Рассмотрим следующий пример:

=VLOOKUP("Banana", A1:C10, 2, FALSE)

В этом примере Excel ищет слово "Banana" в первом столбце диапазона A1:C10 и возвращает значение из второго столбца найденной строки. Если точное совпадение для "Banana" не найдено, Excel возвращает значение ошибки.

Функция HLOOKUP

Функция HLOOKUP работает так же, как VLOOKUP, но она ищет строку вместо столбца, выполняя "горизонтальный поиск". Она особенно полезна для данных, представленных горизонтально.

Синтаксис функции HLOOKUP таков:

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

• lookup_value: Значение, которое вы хотите найти.
• table_array: Диапазон ячеек, содержащий данные.
• row_index_num: Номер строки в массиве таблицы, из которой должно быть возвращено значение.
• range_lookup: Логическое значение, указывающее должно ли искать точное совпадение (FALSE) или приблизительное совпадение (TRUE).

Пример:

=HLOOKUP(1020, A1:F2, 2, FALSE)

Этот пример будет искать значение 1020 в первой строке диапазона A1:F2 и возвращать значение, найденное в том же столбце во второй строке.

Функция Lookup

Функция LOOKUP в Excel может выполнять как горизонтальные, так и вертикальные поиски, однако она не так часто используется из-за своих ограничений по сравнению с VLOOKUP и HLOOKUP. У функции LOOKUP есть две формы синтаксиса: векторная и массивная.

Векторная форма:

=LOOKUP(lookup_value, lookup_vector, [result_vector])

• lookup_value: Значение, которое вы хотите сопоставить в векторе.
• lookup_vector: Вектор поиска, состоящий из одной строки или одного столбца.
• result_vector: Необязательно. Вектор результата из одной строки или столбца, из которого будет получен результат.

Массивная форма:

=LOOKUP(lookup_value, array)

• lookup_value: Значение, которое вы хотите соответствовать в таблице.
• Массив: Диапазон значений, состоящий не менее чем из двух столбцов.

Пример:

=LOOKUP("dog", A1:A3, B1:B3)

Этот пример находит слово "dog" в диапазоне A1:A3 и возвращает значение из соответствующей ячейки в B1:B3.

Понимание работы со ссылками

Функции ссылок в Excel помогают эффективно работать с ссылками на ячейки и диапазоны. Эти функции особенно полезны в сложных таблицах, где вы ссылаетесь на несколько источников данных. Наиболее важные функции ссылок включают в себя INDEX, MATCH, ADDRESS, INDIRECT, OFFSET и другие.

Функция Index

Функция INDEX возвращает значение или ссылку на ячейку на пересечении строки и столбца в заданном диапазоне или массиве. Функция может использоваться по-разному, особенно в комбинации с функцией MATCH для достижения более сложных поисковых задач.

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

=INDEX(array, row_num, [column_num])

• array: Диапазон или константа массива.
• row_num: Номер строки в таблице, из которой будет извлечено значение.
• column_num: Необязательно. Номер столбца для возврата, когда в массиве более одного столбца.

Пример:

=INDEX(A1:C3, 2, 3)

Этот пример вернет значение во второй строке и третьем столбце указанного диапазона A1:C3.

Функция Match

Функция MATCH ищет указанный элемент в диапазоне ячеек и затем возвращает относительное положение этого элемента в пределах диапазона. Она в основном используется с INDEX для выполнения расширенных поисковых операций.

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

=MATCH(lookup_value, lookup_array, [match_type])

• lookup_value: Значение, которое вы хотите найти.
• lookup_array: Диапазон ячеек, в котором нужно найти значение.
• match_type: Необязательно. Число -1, 0 или 1. Это зависит от того, как вы хотите искать.

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

Пример:

=MATCH(39, A1:A5, 0)

Этот пример вернет позицию значения 39 в пределах диапазона A1:A5. Если 39 находится в третьей ячейке, функция вернет номер 3.

Использование функций INDEX и MATCH вместе

Комбинация функций INDEX и MATCH является мощной альтернативой VLOOKUP или HLOOKUP из-за своей гибкости. В отличие от VLOOKUP, который может искать только в крайнем левом столбце, INDEX и MATCH могут искать по любому столбцу или строке.

Пример:

=INDEX(B1:B10, MATCH("Orange", A1:A10, 0))

В этом примере Excel использует MATCH для поиска "orange" в диапазоне A1:A10, находит его позицию, затем использует INDEX для возврата соответствующего значения из B1:B10.

Функция Address

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

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

=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

• row_num: Номер строки, используемый в ссылке на ячейку.
• column_num: Номер столбца, используемый в ссылке на ячейку.
• abs_num: Необязательно. Число (от 1 до 4), указывающее тип ссылки.
• a1: Необязательно. Логическое значение, определяющее стиль ссылки.
• sheet_text: Необязательно. Имя рабочего листа для использования.

Пример:

=ADDRESS(2, 3)

Этот пример вернет $C$2, что является абсолютной ссылкой на ячейку, расположенную во второй строке и третьем столбце.

Функции Indirect

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

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

=INDIRECT(ref_text, [a1])

• ref_text: Ссылка на ячейку, которая включает ссылку в стиле A1, ссылку в стиле R1C1, имя, определённое как ссылка, или ссылку на ячейку в виде строки текста.
• a1: Необязательно. Логическое значение, которое определяет тип ссылки, который содержится в аргументе ссылки на ячейку.

Пример:

=INDIRECT("A" & 1)

Этот пример использует функцию INDIRECT для возвращения значения, расположенного в ячейке A1.

Функция Offset

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

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

=OFFSET(reference, rows, cols, [height], [width])

• Ссылка: Ссылка, на которую вы хотите основывать смещение. Это должно относиться к ячейке или диапазону смежных ячеек.
• Строки: Количество строк выше или ниже, которые вы хотите указать для верхней левой ячейки.
• Столбцы: Количество столбцов слева или справа, которые вы хотите, чтобы верхняя левая ячейка ссылки относилась к ним.
• высота: Необязательно. Высота, в количестве строк, которую вы хотите для возвращения по ссылке иметь.
• ширина: Необязательно. Ширина, в количестве столбцов, которые вы хотите получить в возвращаемой ссылке.

Пример:

=OFFSET(A1, 3, 2)

Этот пример вернет ссылку на ячейку, находящуюся на 3 строки вниз и на 2 столбца направо от ячейки A1.

Заключение

Функции VLOOKUP, HLOOKUP и другие функции поиска и ссылок превращают Excel 2016 в невероятно мощный инструмент для анализа и манипулирования данными. Используя возможности этих функций, вы можете эффективно управлять и ссылаться на данные в листе или книге, выполнять расширенный поиск данных и автоматизировать сложные расчеты.

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

  Поделиться этой статьей

Если вы найдете что-то неправильное в содержании статьи, вы можете запросить обновление