Lookup функция в эксель: ПРОСМОТР (функция ПРОСМОТР) — Служба поддержки Майкрософт

Содержание

LOOKUP Formula в Excel — Как использовать формулу LOOKUP в Excel?

  • LOOKUP Формула в Excel

LOOKUP Формула в Excel (Содержание)

  • LOOKUP Формула в Excel
  • Как использовать формулу LOOKUP в Excel?

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

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

Внедрение формулы LOOKUP

Мы можем реализовать формулу LOOKUP двумя способами.

Способ 1

Формула функции LOOKUP:

= LOOKUP (lookup_ value, lookup_ vector, (result_ vector))

Аргументы этой функции объяснены в следующей таблице.

аргументация объяснение
Поиск _ ЗначениеЭто значение поиска, которое находится в 1- м столбце или 1- й строке массива таблицы. Это может быть текст или ссылка на ячейку.
Поиск _ ВекторЭто 1- я строка или 1- й столбец таблицы данных, в которой можно найти значение для поиска
Результат _ ВекторЭто строка результатов или столбец результатов таблицы данных, в которых можно найти результат поиска по значению Lookup _.

Способ 2

Формула функции LOOKUP:

= LOOKUP (поиск_значение, таблица_ массив)

Аргументы этой функции объяснены в следующей таблице.

аргументация Описание
искомое_значениеЭто значение поиска, которое находится в 1- й строке массива таблицы. Это может быть текст или ссылка на ячейку.
таблица_массивЭто таблица или массив, в котором записывается результат, а также значение поиска. Но в этом случае результат всегда будет из последнего столбца или последней строки выбранного диапазона данных на основе вертикальной или горизонтальной таблицы соответственно. 1- й столбец или 1- я строка выбранного диапазона данных на основе вертикальной или горизонтальной таблицы соответственно всегда будут тем столбцом или строкой, в которой содержится значение Lookup _.

Как использовать формулу LOOKUP в Excel?

Мы обсудим 2 примера формулы LOOKUP. 1- й — для данных в вертикальном массиве, а второй — для данных в горизонтальном массиве.

Вы можете скачать этот шаблон поиска формулы поиска здесь — шаблон поиска формулы поиска Excel

Пример № 1

Чтобы узнать о функции LOOKUP, давайте рассмотрим один пример.

У Тони Старка есть цветочный магазин с большим разнообразием цветов. Всякий раз, когда покупатель покупает цветок, Тони должен проверить цену цветка, просматривая каждую запись в прайс-листе на цветы.

Это тратит много времени, так как он должен пройти весь список.

Поэтому он решает использовать MS Excel, чтобы проверить цену цветка.

Тони вводит название всех цветов в 1- й колонке. Во 2- й колонке он вводит цену каждого цветка.

Теперь у него есть список цветов с соответствующими ценами.

Если Тони хочет узнать цену любого конкретного цветка, он может использовать функцию «Поиск».

Он вводит название цветка в клетку D4. Давайте возьмем имя как Дейзи.

Затем в ячейку E4 он добавляет функцию LOOKUP = LOOKUP (D4, A2: B70).

Где,

D4 — это ячейка, которая содержит название цветка.
A1: B70 — диапазон, который должен быть проверен по названию цветка и цене цветка.

Поэтому отображается цена введенного цветка.

Это также может быть сделано другим способом, как упоминалось ранее.

Он вводит название цветка в клетку G4. Давайте возьмем имя как Дейзи.

Затем в ячейке h5 он добавляет функцию LOOKUP,

= LOOKUP (G4, A2: A70, B2: B70).

Где,

G4 — это ячейка, которая содержит название цветка.
A2: A70 — диапазон, который должен быть проверен на предмет названия цветка.
B2: B70 — это диапазон, который должен быть проверен по цене цветка.

Поэтому отображается цена введенного цветка.

Это экономит время и усилия Тони.

Пример № 2

Рассмотрим сценарий РУКОВОДЯЩЕГО РЕШЕНИЯ КОМПАНИИ. В соответствии с политикой компании, ежегодное повышение заработной платы сотрудников будет зависеть от результатов деятельности. Сведения о сотруднике, такие как имя сотрудника, зарплата и класс, записываются в таблицу Excel.

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

Так что теперь HR, Стив Роджерс, хочет найти процент повышения каждого сотрудника с помощью функции HLOOKUP. Кроме того, Стив хочет узнать новую зарплату каждого сотрудника.

Чтобы рассчитать процент повышения, Стив должен добавить функцию LOOKUP в ячейку E2,
= ПРОСМОТР (D2, $ I $ 2: $ L $ 3)
Где,

D2 — это ячейка, которая содержит оценку каждого сотрудника
$ I $ 2: $ L $ 3 — это диапазон, который должен быть проверен на оценку и процент повышения

Или Стив может использовать другой способ, добавив функцию LOOKUP в ячейку E2,
= ПРОСМОТР (D2, $ I $ 2: $ L $ 2, $ I $ 3: $ L $ 3)

Где,

D2 — это ячейка, которая содержит оценку каждого сотрудника.
$ I $ 2: $ L $ 2 — это диапазон, который должен быть проверен для оценки
$ I $ 3: $ L $ 3 — это диапазон, который должен быть проверен на процент повышения.

Таким образом, процент похода будет отображаться. Затем Стив должен опустить формулу, чтобы получить все процентные доли сотрудников в соответствии с их оценками.

Теперь Стиву нужно узнать новую зарплату после похода. Для этого ему нужно добавить требуемую формулу = C2 + C2 * E2.

Где,

С2 — зарплата каждого работника.
E2 — процент похода, основанный на их классе

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

То, что нужно запомнить

  • Если выбираемое значение lookup_value встречается более одного раза, функция LOOKUP возвращает первый экземпляр значения lookup_.
  • Lookup_Vector или 1- й столбец или 1-я строка массива таблицы должны быть в порядке возрастания.

Рекомендуемые статьи

Это было руководство по формуле LOOKUP в Excel. Здесь мы обсудили, как использовать формулу LOOKUP в Excel вместе с примерами и загружаемым шаблоном Excel. Вы также можете посмотреть на эти полезные статьи Excel —

  1. Функция VLOOKUP в Excel
  2. Excel RATE Formula
  3. Excel КОЛОННА в номер
  4. Рассчитать промежуточную сумму в Excel

Категория:

Советы по Excel

Функция ПРОСМОТР() в EXCEL. Примеры и описание


Функция

ПРОСМОТР(

)

, английский вариант LOOKUP(),

похожа на функцию

ВПР()

:

ПРОСМОТР()

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

ПРОСМОТР()

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

Перед использованием функции

ПРОСМОТР()

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

ВПР()

и

ПОИСКПОЗ()

совместно с функцией

ИНДЕКС()

.

Синтаксис функции

Существует 2 формы задания аргументов функции

ПРОСМОТР()

: форма массива и форма вектора.


Форма массива

Форма массива функции

ПРОСМОТР()

просматривает первый (левый) столбец таблицы и, если находит искомое значение, возвращает значение из соответствующей строки самого правого столбца таблицы (массива).


ПРОСМОТР

(

искомое_значение

;

массив

)

Формула

=ПРОСМОТР(«яблоки»; A2:B10)

просматривает диапазон ячеек

А2:А10

. Если, например, в ячейке

А5

содержится искомое значение «яблоки», то формула возвращает значение из ячейки

B5

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

B2:B10

). Внимание! Значения в диапазоне

А2:А10

должны быть отсортированы по возрастанию.

Если функции

ПРОСМОТР()

не удается найти

искомое_значение

, то выбирается наибольшее значение, которое меньше

искомого_значения

или равно ему.

Функция

ПРОСМОТР()

— также имеет

векторную форму

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


ПРОСМОТР

(

искомое_значение

;

просматриваемый_вектор; вектор_результатов)

Формула

=ПРОСМОТР(«яблоки»; A2:A10; B2:B10)

просматривает диапазон ячеек

А2:А10

. Если, например, в ячейке

А5

содержится искомое значение «яблоки», то формула возвращает значение из ячейки

B5

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

B2:B10

). Внимание! Значения в диапазоне

А2:А10

должны быть отсортированы по возрастанию. Если функции

ПРОСМОТР()

не удается найти

искомое_значение

, то выбирается наибольшее значение, которое меньше

искомого_значения

или равно ему.

Функция

ПРОСМОТР()

не различает

РеГИстры

при сопоставлении текстов.

Если функция

ПРОСМОТР()

не находит соответствующего значения, то возвращается значение ошибки #Н/Д.

Поиск позиции в массивах с текстовыми значениями

Произведем поиск значения в сортированном списке текстовых значений (диапазон

А8:А15

). Список может содержать повторы. Искомое значение содержится в

А19

.

Формулы для вывода соответствующих значений

Адреса

и

ИНН

(форма массива)

:

=ПРОСМОТР(A19;A8:B15)

и

=ПРОСМОТР(A19;A8:С15)

Формулы для вывода соответствующих значений

Адреса

и

ИНН

(форма вектора)

:

=ПРОСМОТР(A19;A8:A15;B8:B15)

и

=ПРОСМОТР(A19;A8:A15;С8:С15)

В

файле примера

можно найти применение формул при наличии повторов в столбце поиска.

Как видно из картинки выше, в случае повторов в столбце поиска, функция

ПРОСМОТР()

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


Вывод

: функция

ПРОСМОТР()

не имеет преимуществ по сравнению с функцией

ВПР()

.

Поиск позиции в массиве констант

Поиск значения можно производить не только в диапазонах ячеек, но и в

массивах констант

. Например, формула

=ПРОСМОТР(22;{0;60;70;80;90};{«F»;»D»;»C»;»B»;»A»})

вернет значение

F

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

Поиск позиции в массивах с Числами

Произведем поиск значения в сортированном списке числовых значений. Список может содержать повторы (см.

файл примера

).

Если функции

ПРОСМОТР()

не удается найти

искомое_значение

, то выбирается наибольшее значение, которое меньше

искомого_значения

или равно ему.

функций поиска и формул | Компьютерные приложения для менеджеров

Результаты обучения

  • Использование функций поиска и формул

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

ВПР (вертикальный просмотр)

Ищет значение в крайнем левом столбце таблицы и возвращает значение в той же строке из заданного вами вертикального столбца. Искомое значение всегда должно находиться в крайнем левом столбце таблицы.

Формула Excel выглядит следующим образом: =ВПР(искомое_значение,массив_таблиц,номер_индекса_столбца,[диапазон_просмотра])

Эта таблица содержит итоговые данные о продажах для продавцов. В этом случае вы хотите узнать, каковы были продажи для определенных продавцов. Это небольшой пример, но представьте себе длинный список имен и номеров для поиска. Использование функции ВПР позволяет быстро получить желаемые результаты. Обязательно используйте F4 для создания абсолютных ссылок и сохраняйте одинаковый массив таблиц для каждого поиска. Также убедитесь, что col_index_num использует второй столбец в таблице (столбец 2 в таблице) с возвратом для range_lookup как FALSE. При использовании FALSE во всех случаях нет необходимости сортировать таблицы по возрастанию. Вот как выглядят эти результаты.

 

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

HLOOKUP (горизонтальный поиск)

Ищет значение в самой верхней строке таблицы и возвращает значение в той же строке из заданного вами горизонтального столбца. Искомое значение всегда должно находиться в крайнем левом столбце таблицы.

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

Функции MATCH и INDEX

Теперь мы изучим две дополнительные функции поиска, Match и Index. Посмотрите это видео, чтобы узнать, как использовать функции поиска Match и Index. Вот описания и формулы для их создания в Excel.

  • ПОИСКПОЗ — Функция, которая возвращает позицию значения в диапазоне, который может быть строкой, столбцом или таблицей. Формула Excel выглядит следующим образом: =ПОИСКПОЗ(искомое_значение,искомый_массив,[тип_совпадения])
  • ИНДЕКС — Функция, которая возвращает позицию значения в диапазоне, который смотрит на пересечение позиции столбца и строки в таблице. Формула Excel записывается =ИНДЕКС(ссылка,номер_строки,[номер_столбца],[номер_области])

По мере того, как вы будете лучше знакомиться с функциями поиска, попробуйте еще несколько, выбрав вкладку «Формулы», кнопку «Поиск и ссылки» и посмотрите, какие другие доступны для вас.

Практические вопросы

Внесите свой вклад!

У вас есть идеи по улучшению этого контента? Мы будем признательны за ваш вклад.

Улучшить эту страницуПодробнее

Функции поиска в Excel (примеры + примеры файлов)

1. АДРЕС Функция

2. ОБЛАСТИ Функция

3. ВЫБЕРИТЕ Функция

4. СТОЛБЦ Функция

5. СТОЛБЦЫ Функция

6. ФОРМУЛАТЕКСТ Функция 9000 9

7. Функция ГПР

8. Функция ГИПЕРССЫЛКИ

9. Функция INDIRECT

11. Функция LOOKUP

12. Функция MATCH

13. Функция OFFSET

14. Функция ROW

15. Функция ROWS

1 6. Функция ТРАНСП

примеры файлов

1. Функция АДРЕС

Функция АДРЕС возвращает действительную ссылку на ячейку в соответствии с адресом столбца и строки. Простыми словами, вы можете создать адрес ячейки, используя номер строки и номер столбца.

Синтаксис

АДРЕС(номер_строки,номер_столбца,номер_абс,A1,текст_листа)

Аргументы

  • row_num:  Число для указания номера строки.
  • номер_столбца:  Число для указания номера столбца.
  • [abs_num]:  Тип ссылки.
  • [A1]:  Справочный стиль.
  • [sheet_text]: Текстовое значение в качестве имени листа.

Примечания

  • По умолчанию функция АДРЕС возвращает в результате абсолютную ссылку.

Пример

В приведенном ниже примере мы использовали разные аргументы для получения всех типов результатов.

С  Стиль ссылки R1C1 :

  • Относительная ссылка.
  • Относительная ссылка на строку и абсолютную ссылку на столбец.
  • Абсолютная ссылка на строку и относительную ссылку на столбец.
  • Абсолютное задание.

Со стилем ссылки A1:

  • Относительная ссылка.
  • Относительная ссылка на строку и абсолютную ссылку на столбец.
  • Абсолютная ссылка на строку и относительную ссылку на столбец.
  • Абсолютное задание.

2. Функция ОБЛАСТИ

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

синтаксис

ОБЛАСТИ(ссылка)

Аргументы

  • ссылка:  A Ссылка на ячейку или диапазон ячеек.

Примечания

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

Пример

В приведенном ниже примере мы использовали функцию площади для получения ссылки на число в именованном диапазоне.

Как видите, в диапазоне есть три столбца, и в результате было возвращено 3.

3. Функция ВЫБОР

Функция ВЫБОР возвращает значение из списка значений на основе указанного номера позиции. Проще говоря, он ищет значение в списке на основе его положения и возвращает его в результате.

Синтаксис

ВЫБОР(номер_индекса,значение1,значение2,…)

Аргументы

  • номер_индекса :  Число для указания позиции значения в списке.
  • значение1:  Диапазон ячеек или входное значение, из которого можно выбрать.
  • [значение2]:  Диапазон ячеек или входное значение, из которого можно выбрать.

Примечания

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

Пример

В приведенном ниже примере мы использовали функцию ВЫБОР с раскрывающимся списком для вычисления четырех (сумма, среднее, максимальное и смешанное) различных значений. Итак, мы использовали приведенную ниже формулу для расчета всех четырех вещей:

=ВЫБОР(ВПР(K2,Q1:R4,2,ЛОЖЬ),СУММ(O2:O9),СРЗНАЧ(O2:O9),МАКС(O2:O9),МИН(O2:O9))

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

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

И вместо значений мы использовали четыре формулы для 4 разных вычислений.

4. Функция COLUMN

Функция COLUMN возвращает номер столбца для данной ссылки на ячейку. Как вы знаете, каждая ссылка на ячейку состоит из номера столбца и номера строки. Таким образом, он берет номер столбца и возвращает его в результате.

Синтаксис

COLUMN([ссылка])

Аргументы

  • ссылка:  Ссылка на ячейку, для которой вы хотите получить номер столбца.

Примечания

  • Нельзя ссылаться на несколько ссылок.
  • Если вы ссылаетесь на массив, функция столбца также возвращает номера столбцов в массиве.
  • Если вы ссылаетесь на диапазон из более чем одной ячейки, будет возвращен номер столбца самой левой ячейки. Например, если вы обратитесь к диапазону A1:C10, он вернет номер столбца ячейки A1.
  • Если пропустить указание ссылки, будет возвращен номер столбца текущей ячейки.

Пример

В приведенном ниже примере мы использовали СТОЛБЦ, чтобы получить номер столбца ячейки A1.

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

5. Функция СТОЛБЦЫ

Функция СТОЛБЦЫ возвращает количество столбцов, указанных в данной ссылке. Простыми словами, подсчитывает количество столбцов в указанном диапазоне и возвращает это количество.

Синтаксис

СТОЛБЦЫ(массив)

Аргументы

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

Примечания

  • Вы также можете использовать именованный диапазон.
  • Функция COLUMNS не имеет отношения к значениям в ячейках, она просто возвращает количество столбцов в ссылке.

Пример

В приведенном ниже примере мы использовали COLUMN для получения количества столбцов из диапазона A1:F1.

6. Функция ФОРМУЛАТЕКСТ

Функция ФОРМУЛАТЕКСТ возвращает формулу из указанной ячейки. И если в упомянутой ячейке нет формулы, значения или пробела, он вернет #N/A.

Синтаксис

FORMULATEXT(ссылка)

Аргументы

  • ссылка:  Ссылка на ячейку, из которой вы хотите вывести формулу в виде текста.

Примечания

  • Если вы ссылаетесь на другую книгу, эта книга должна быть открыта, иначе формула не будет отображаться.
  • Если вы ссылаетесь на диапазон более чем на одну ячейку, будет возвращена формула из верхней левой ячейки данного диапазона.
  • Он вернет значение ошибки «#Н/Д», если ячейка, которую вы используете в качестве ссылки, не содержит никакой формулы, имеет формулу с более чем 8192 символами, ячейка защищена или внешняя книга не открыта. .
  • Если вы сошлетесь на две ячейки в циклической ссылке, будут возвращены результаты из обеих.

Пример

В приведенном ниже примере мы использовали текст формулы с разными типами ссылок. Когда вы ссылаетесь на ячейку, в которой нет формулы, она возвращает значение ошибки «#Н/Д».

7. Функция ГПР

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

Синтаксис

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

Аргументы

  • взгляд up_value:  Значение, которое вы хотите найти.
  • table_array:  Таблица данных или массив, из которого вы хотите получить значение поиска.
  • row_index_num:  Числовое значение, представляющее количество строк ниже верхней строки, из которой вы хотите получить значение. Например, если вы укажете 2 и искомое значение находится в A10 в таблице данных, оно вернет значение из ячейки B10.
  • [range_lookup]:  Логическое значение для указания типа поиска. Если вы хотите выполнить поиск с точным соответствием, используйте FALSE, а если вы хотите выполнить неточное совпадение, используйте TRUE (по умолчанию).

Примечания

  • Можно использовать подстановочные знаки.
  • Вы можете выполнить точное совпадение и приблизительное совпадение.
  • При выполнении приблизительного сопоставления обязательно сортируйте данные в порядке возрастания слева направо, и если данные не в порядке возрастания, результат будет неточным.
  • Если значение range_lookup равно true или опущено, выполняется неточное совпадение, но возвращается точное совпадение, если искомое значение существует в диапазоне поиска.
  • Если range_lookup имеет значение true или опущено, а искомое значение не находится в искомом диапазоне, будет возвращено ближайшее значение, которое меньше искомого значения.
  • Если range_lookup равен false, то нет необходимости сортировать диапазон данных.

Пример

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

Имя зоны из ячейки C7 используется в качестве значения поиска. Диапазон B1: F5 в качестве массива таблиц, а для row_index_num мы использовали функцию сопоставления, чтобы получить номер строки.

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

8. Функция HYPERLINK

Функция HYPERLINK возвращает строку с прикрепленной к ней гиперссылкой. Проще говоря, как и опция ГИПЕРССЫЛКА в Excel, функция ГИПЕРССЫЛКА помогает вам создать гиперссылку.

Синтаксис

HYPERLINK(link_location,[friendly_name])

Аргументы

  • Link_Location:  Местоположение, для которого вы хотите добавить ГИПЕРССЫЛКУ. Далее его можно разделить на два термина.
    1. ссылка:  Это может быть адрес ячейки или диапазон ячеек на том же листе, на любом другом листе или в любой другой книге. Мы также можем связать закладку из документа Word.
    2. местоположение:  Это может быть ссылка на жесткий диск, сервер с использованием пути UNC или любой URL-адрес из Интернета или интрасети. (В Excel онлайн вы можете использовать веб-адрес только для функции ГИПЕРССЫЛКА). Вы можете вставить ссылку на функцию, вставив ее как текст с кавычками или сославшись на ячейку, содержащую ссылку, как на текст. Обязательно используйте «HTTPS://» перед веб-адресом.
  • [friendly_name]:  Это необязательная часть этой функции. Он действует как лицевая сторона соединительного звена.
    1. Вы можете использовать любой тип текста, числа или и то, и другое.
    2. Вы также ссылаетесь на ячейку, содержащую дружественное_имя.
    3. Если вы пропустите его, функция будет использовать адрес ссылки для отображения.
    4. Если friendly_name возвращает ошибку, функция отобразит ошибку.

Примечания

  • Ссылка на файл, сохраненный на веб-адресе:  Вы можете использовать файл, сохраненный на веб-адресе. Это помогает нам эффективно делиться файлом.
  • Ссылка на файл, сохраненный на жестком диске:  Эту функцию также можно использовать при работе в автономном режиме. Вы можете связать файл, который хранится на вашем жестком диске, и получить к нему доступ через один лист Excel, не нужно переходить в каждую папку, чтобы открыть их.
  • Ссылка на файл документа Word:  Это также замечательная функция функции ГИПЕРССЫЛКА. Вы можете связать файл документа Word или определенное место в файле документа Word, используя закладку.
  • Ссылка на файл без понятного имени:  Если вы хотите показать пользователю фактическую ссылку на файл или место. В этой ситуации вам просто нужно пропустить объявление понятного имени в функции HYPERLINK.

9. Функция ДВССЫЛ

Функция ДВССЫЛ возвращает действительную ссылку из текстовой строки, представляющей ссылку на ячейку. Простыми словами, вы можете ссылаться на диапазон ячеек, используя адрес ячейки в качестве текстового значения.

Синтаксис

INDIRECT(ref_text, [a1])

Аргументы

  • ref_text:  Текст, представляющий адрес ячейки, адрес диапазона ячеек, именованного диапазона или имя таблицы. Например, A1, B10:B20 или MyRange.
  • [a1]:  Число или логическое значение для представления типа ссылки на ячейку, которую вы указываете в ref_text. Например,  если вы хотите использовать стиль ссылок A1, используйте TRUE или 1, а если вы хотите использовать стиль ссылок R1C1, используйте FALSE или 0 для стиля ссылок R1C. И если вы не укажете тип ссылки на ячейку, по умолчанию будет использоваться стиль A1.

Примечания

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

Пример

1. Ссылка на другой рабочий лист

Вы также можете сослаться на другой рабочий лист, используя ДВССЫЛ, и вы должны вставить в него имя рабочего листа. В приведенном ниже примере мы использовали косвенную функцию для ссылки на другой рабочий лист и имеем имя листа в ячейке A2 и ссылку на ячейку в ячейке B2.

В ячейке C2 мы использовали следующую формулу для объединения текста.

=ДВССЫЛ(«‘»&A2&»‘!»&B2)

Эта комбинация создает текст, который используется функцией ДВССЫЛ для ссылки на ячейку A1 на листе1, и лучшая часть — это когда вы меняете имя рабочего листа или ячейки адрес ссылки автоматически изменится.

Ячейка A1 в «Листе 1» имеет значение «Да», поэтому непрямой метод возвращает значение «Да».

2. Ссылка на другую рабочую книгу

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

В приведенном выше примере мы использовали следующую формулу для получения значения из ячейки A1 книги «Book1».

= ДВССЫЛ («[«&A2&»]»&B2&»!»&C2)

Поскольку у нас есть имя рабочей книги в ячейке «A2», имя рабочего листа в ячейке «B2» и имя ячейки в ячейке «C2». Мы объединили их для использования в качестве входного текста в косвенной функции.

Примечание: При объединении ссылки на ячейку в виде текста следите за правильной структурой ссылки.

3. Использование с именованными диапазонами

Да, вы также можете ссылаться на именованный диапазон, используя косвенную функцию. Это просто. Как только вы создадите именованный диапазон, вы должны ввести этот именованный диапазон как текст в ДВССЫЛ.

В приведенном выше примере у нас есть раскрывающийся список в ячейке E1 со списком именованных диапазонов, а в ячейке E2 мы использовали это имя. Поскольку диапазон B2: B5 называется «Количество», а диапазон C2: C5 называется «Сумма».

При выборе количества из выпадающего списка косвенная функция мгновенно обращается к именованному диапазону. И когда вы выберете сумму из раскрывающегося списка, у вас будет сумма диапазона ячеек C2: C5.

11. Функция ПРОСМОТР

Функция ПРОСМОТР возвращает значение (которое вы ищете) из строки, столбца или массива. Простыми словами, вы можете найти значение, и ПРОСМОТР вернет это значение, если оно есть в этой строке, столбце или массиве.

Синтаксис

ПРОСМОТР(значение, искомый_диапазон, [результативный_диапазон])

Существует два типа функций ПРОСМОТР.

  • Векторная форма
  • Массивная форма

Аргументы

  • значение:  Значение, которое вы хотите найти в столбце или строке.
  • диапазон поиска:  Столбец или строка, в которой вы хотите найти значение.
  • [result_range]:  Столбец или строка, из которой вы хотите получить значение. Это необязательный аргумент.

Примечания

  • Вместо использования формы массива лучше использовать ВПР или ГПР.

12. Функция ПОИСКПОЗ

Функция ПОИСКПОЗ возвращает порядковый номер значения из массива. Проще говоря, функция ПОИСКПОЗ ищет значение в списке и возвращает номер позиции этого значения в списке.

Синтаксис

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

Аргументы

  • искомое_значение : значение, позицию которого вы хотите получить из списка значений.
  • lookup_array : Диапазон ячеек или массив содержит значения.
  • [match_type] : число (-1, 0 и 1), чтобы указать, как Excel ищет значение из списка значений.
    1. Если вы укажете 1, будет возвращено наибольшее значение, равное или меньшее искомого значения. Значения в списке должны быть отсортированы в порядке возрастания.
    2. Если вы используете -1, будет возвращено наименьшее значение, равное или превышающее искомое значение. Значения в списке должны быть отсортированы в порядке возрастания.
    3. Если вы укажете 0, будет возвращено точное совпадение из списка.

Примечания

  • Можно использовать подстановочные знаки.
  • Если в списке нет подходящего значения, if вернет #N/A.
  • Функция совпадения не чувствительна к регистру.

Пример

В приведенном ниже примере мы использовали 1 в качестве типа соответствия и ищем значение 5.

Как я уже упоминал, если вы используете 1 в качестве типа соответствия, возвращается наибольшее значение, равное или меньшее, чем значение поиска. Во всем списке есть 3 значения меньше 5 и 4 самое высокое из них.

Вот почему в результате она вернула 3, что является позицией значения 4.

13. Функция OFFSET

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

Синтаксис

СМЕЩ(ссылка, строки, столбцы, [высота], [ширина])

Аргументы

  • ссылка : Ссылка, с которой вы хотите начать смещение. Это может быть ячейка или диапазон соседних ячеек.
  • строк : Количество строк, которые указывают OFFSET двигаться вверх или вниз от ссылки. Чтобы идти вниз, вам нужно положительное число, а для движения вверх вам нужно отрицательное число.
  • cols : Количество столбцов указывает OFFSET двигаться влево или вправо от ссылки. Чтобы пойти направо, вам нужно положительное число, а чтобы пойти налево, вам нужно отрицательное число.
  • [высота] : число для указания строк, которые необходимо включить в ссылку.
  • [ширина]: Число для указания столбцов, которые необходимо включить в ссылку.

Примечания

  • СМЕЩ — это «изменчивая» функция, она пересчитывается всякий раз, когда в рабочем листе происходят какие-либо изменения.
  • Отображает #ССЫЛКА! значение ошибки, если смещение находится за пределами края рабочего листа.
  • Если высота или ширина опущены, используются высота и ширина ссылки.

Пример

В приведенном ниже примере мы использовали СУММ со СМЕЩЕНИЕМ для создания динамического диапазона, который суммирует значения всех месяцев для определенного продукта.

14. Функция СТРОКА

Функция СТРОКА возвращает номер строки указанной ячейки. Проще говоря, с помощью функции СТРОКА вы можете получить номер строки ячейки, и если вы не ссылаетесь ни на одну ячейку, она возвращает номер строки для ячейки, в которую вы ее вставили.

Синтаксис

ROW([reference])

Аргументы

  • ссылка: Ссылка на ячейку или диапазон ячеек, для которых вы хотите проверить строку номер.

Примечания

  • Он будет включать все типы листов (лист диаграммы, рабочий лист или лист макросов).
  • Вы можете ссылаться на листы, даже если они видны, скрыты или очень скрыты.
  • Если вы пропустите указание какого-либо значения в функции, она даст вам номер листа, на котором вы применили функцию.
  • Если вы укажете недопустимое имя листа, он вернет #N/A.
  • Если вы укажете недопустимую ссылку на лист, он вернет #ССЫЛКА!.

Пример

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

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

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

15. Функция СТРОКИ

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

Синтаксис

ROWS(массив)

Аргументы

  • массив:  Ссылка на ячейку или массив для проверки количества строк.

Примечания

  • Вы также можете использовать именованный диапазон.
  • Он не касается значений в ячейках, он просто возвращает количество строк в ссылке.

Пример

В приведенном ниже примере мы обратились к вертикальному диапазону из 10 ячеек, и в результате было возвращено 10, поскольку диапазон включает 10 строк.

16. Функция ТРАНСП

Функция ТРАНСП изменяет ориентацию диапазона. Проще говоря, с помощью этой функции вы можете преобразовать данные из строки в столбец и из столбца в строку.

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