МЅ Excel: как использовать функцию ВПР

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

Функция ВПР является на самом деле очень легко использовать как только вы поймете, как это работает!

Синтаксис

Синтаксис функции ВПР в Microsoft Excel-это:

ВПР( значение, Таблица, номер_индекса, [approximate_match] )
Параметры или Аргументы

значение
Значение для поиска в первом столбце таблицы.
Таблица
Два или более столбцов данных, отсортированных по возрастанию.
номер_индекса
Номер столбца в таблице, из которой соответствующее значение должно быть возвращено. Первый столбец 1.
истина или ложь
Необязательно. Введите значение ЛОЖЬ (0), чтобы найти Точное совпадение. Введите ИСТИНА (1), чтобы найти приблизительное совпадение. Если этот параметр опущен, ИСТИНА  по умолчанию.

Примечание

  • Если вы укажите значение ЛОЖЬ для параметра approximate_match и точное соответствие не найдено, то функция ВПР возвращает значение #Н/А.
  • Если вы укажите значение ИСТИНА для параметра approximate_match и точное соответствие не найдено, то следующее меньшее значение возвращается.
  • Если номер_индекса меньше 1, функция ВПР возвращает значение ошибки #знач!.
  • Если номер_индекса больше, чем количество столбцов в таблице, то функция ВПР возвращает ошибку #ссылка!.
  • См. также функция ГПР выполнять горизонтальный поиск.

Пример (как функция)

ВПР как функция листа в Excel.

Nаблицы Excel примеры ВПР:

=ВПР(10251, А1:В6, 2, ложь)
Результат: «значение возвращает груши» в 2-м столбце

=ВПР(10251, А1:С6, 3, ложь)
Результат: возвращает значение $18.60 в 3-й колонке

=ВПР(10251, А1:Д6, 4, ложные)
Результат: 9 ‘возвращает значение в 4-м столбце

=ВПР(10248, А1:В6, 2, ложь)
Результат: возвращает #н/д ‘ошибки #н/д (Точное совпадение)

=ВПР(10248, А1:В6, 2, истина)
Результат: «яблоки» ‘возвращает приблизительное совпадение

Теперь, давайте посмотрим на пример =ВПР(10251, А1:В6, 2, false), которое возвращает значение «груши» и присмотреться, почему.

Первый Параметр

Первый параметр функции ВПР искомое значение в таблице данных.

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

=ВПР(«10251», А1:В6, 2, ложь)
Второй Параметр

Второй параметр в функции ВПР-это Таблица или источник данных, где вертикальный поиск должен быть выполнен.

В данном примере второй параметр А1:В6, которые дает нам два столбца данных для использования в вертикальном поиска — А1:А6 и В1:В6. Первый столбец в диапазоне (А1:А6) используется для поиска значения порядка 10251. Во втором столбце в диапазоне (В1:В6) содержится значение, возвращаемое значение продукта.

Третий Параметр

Третий параметр-номер позиции в таблице, где возвращают данные можно найти. Значение 1 указывает, что первый столбец в таблице. Второй столбец 2, и так далее.

В этом примере третий параметр-2. Это означает, что второй столбец в таблице, где мы найдете значение возвращать. Поскольку диапазон таблицы имеет значение А1:В6, возвращаемое значение будет во второй колонке где-то в диапазоне В1:В6.

Четвертый Параметр

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

В данном примере четвертый параметр имеет значение false. Параметр false означает, что функция ВПР ищет точное соответствие для значения 10251. Параметр True означает, что «закрыть» матч будет возвращена. С ВПР может найти значение 10251 в диапазоне А1:А6, она возвращает соответствующее значение из В1:В6, которые это груши.

Точное соответствие и приблизительное совпадение

Чтобы найти Точное совпадение, использовать ложь в качестве последнего параметра. Чтобы найти приблизительное совпадение, используйте значение True в качестве последнего параметра.

Давайте подстановки значение, которое не существует в наши данные демонстрируют важность этого параметра!

Точное Совпадение

Используйте false, чтобы найти Точное совпадение:

=ВПР(10248, А1:В6, 2, ложь)
Результат: #Н/Д

Если нет точного совпадения, #н/д возвращается.

Приблизительное Совпадение

Используйте значение True, чтобы найти приблизительное совпадение:

=ВПР(10248, А1:В6, 2, истина)
Результат: «Яблоки»

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

ВПР из другого листа

Вы можете использовать функцию ВПР для поиска значения в случае, когда Таблица находится на другом листе. Давайте изменим наш пример выше, и предположим, что Таблица находится в другой лист под названием Лист2 в диапазоне А1:В6.

Мы можем переписать наш оригинальный пример, в котором мы найти значение 10251 следующим образом:

=ВПР(10251, Лист2!А1:В6, 2, ложь)

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

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

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

Предположим, что Таблица на листе под названием «лист-тест» в диапазоне А1:В6, теперь нам нужно завернуть имя листа в одинарные кавычки, как показано ниже:

=ВПР(10251, ‘лист испытаний’!А1:В6, 2, ложь)

Поместив имя листа в одинарные кавычки, мы можем обрабатывать имя листа с пробелами в функции ВПР.

ВПР из другой книги

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

=ВПР(10251, и ‘C:[data.xlsx]Лист1’!$А$1:$В$6, 2, ЛОЖЬ)

Это будет искать значение 10251 в C:data.xlxs файл в 1 лист, где данные таблицы находится в диапазоне $A$1:$В$6.

Зачем использовать абсолютные ссылки?

Сейчас для нас важно, чтобы покрыть еще одна ошибка, которую часто совершают. Когда люди используют функции ВПР, они часто используют относительные ссылки на таблицы, как мы делали в наших примерах выше. Это вернет правильный ответ, но и что происходит при копировании формулы в другую ячейку? Диапазон Таблица будет корректироваться в Excel и изменения относительно того, где вы вставить новую формулу. Давай объясняй дальше…

Так что если вы имели следующую формулу в ячейку G1:

=ВПР(10251, А1:В6, 2, ложь)

И тогда вы скопировать эту формулу из ячейки G1 в ячейку Н2, он внесет изменения в формулу vlookup для этого:

=ВПР(10251, В2:С7, 2, ложь)

Так как ваша Таблица находится в диапазоне A1:B6 и не В2:С7, ваша формула будет возвращать неверные результаты в ячейке H2. Чтобы убедиться, что ваш выбор не меняется, попробуйте, ссылающиеся на таблицы с использованием абсолютной ссылки следующим образом:

=ВПР(10251, $А$1:$B В 6$, 2, ЛОЖЬ)

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

Как справиться с #Н/ошибки

Далее, давайте посмотрим на то, как обрабатывать случаи, когда функция ВПР не найдет совпадения и возвращает #н/д ошибка. В большинстве случаев, Вы не хотите видеть #Н/Д А дисплей более удобный результат.

Например, если у вас следующие формулы:

=ВПР(10248, $А$1:$B В 6$, 2, ЛОЖЬ)

Вместо отображения ошибки #н/д Если вам не удается найти соответствие, вы можете вернуть значение «не найдено». Для этого, можно изменить формулу ВПР следующим образом:

=Если(ЕОШ(ВПР(10248, $в$1:$B в 6$, 2, ложь)), «не нашли», ВПР(10248, $в$1:$В$6, 2, ложь))

Эта новая формула будет использована функция ИСНА, чтобы проверить, если функция ВПР возвращает значение #н/д ошибка. Если функция ВПР возвращает значение #н/д, тогда формула будет выход «не найдено». В противном случае он будет выполнять функцию ВПР как и раньше.

Это отличный способ, чтобы украсить вашу таблицу, так что Вы не увидите традиционных ошибок в Excel.

 

ВПР без ошибки #н/д

ВПР без ошибки #н/д

=Функции ЕСЛИОШИБКА(ВПР(значение,Таблица поиска,2,ложь),»»)

Если вы хотите, чтобы ВПР не выдовал  #н/д,  когда он не может найти знач

ение, следует использовать функцию ЕСЛИОШИБКА, чтобы поймать эту ошибку и вернуть любое значение или любое Вам нужное.

Как работает формула

Если ВПР не может найти значение в таблице подстановки, он возвращает #н/д ошибка. Функцию ЕСЛИОШИБКА позволяет отлавливать ошибки и вернуть свои собственные значения, когда есть ошибка. Обычно, если функция ВПР возвращает значение ошибки нет и значение возвращается. Если функция ВПР возвращает значение ошибки #н/д, функции ЕСЛИОШИБКА затем принимает и возвращает значение.

Если у вас есть подстановки значений в ячейки А1 и поиск значений в диапазон с именем таблицы, и вы хотите, чтобы ячейки были пустыми, если не поиска, не найдено, вы можете использовать:

=Функции ЕСЛИОШИБКА(ВПР(I13;$E$8:$F$15;2;0);»нет»)

Если вы хотите вернуть сообщение «нет», когда нет совпадения, используйте:

=Функции ЕСЛИОШИБКА(ВПР(I13;$E$8:$F$15;2;0);»нет»)

В предыдущих версиях Excel, отсутствие функцию ЕСЛИОШИБКА, вам потребуется повторить ВПР внутри функции ЕСЛИ:

=ЕСЛИ(ЕОШ(ВПР(I13;$E$8:$F$15;2;0));ВПР(I13;$E$8:$F$15;2;0);»»)

 

Файл ВПР_без_ошибки_Н/Д

 

Если сумма заканчивается или частичное совпадение СУММЕСЛИ и СУММЕСЛИМН devsap

Если сумма заканчивается или частичное совпадение СУММЕСЛИ и СУММЕСЛИМН devsap

=СУММЕСЛИ(диапазон критерия ,»*критерий*»,диапазон суммирования)

Связанные формулы

Если сумма начинается с
Сумма, если ячейки содержат определенный текст
Сумма если не пустой
Сумма, если ячейки равны
Сумма, если ячейки не равны
Если сумма равна либо x или Y

В сумме, если клетки конца с определенным текстом, можно использовать функцию СУММЕСЛИ.

В примере, в ячейке g7 имеет следующую формулу:

=СУММЕСЛИ(Товар;»*Футб*»;Сумма)

Эта формула суммирует ячейки в указанном диапазоне Сумма  только если ячеек именованного диапазона Товар  оканчиваются «*Футб*».

Обратите внимание, что СУММЕСЛИ не чувствителен к регистру. Критерии «*Футб*» соответствует любой текст, который содержит Футб.

Как работает формула

Функция СУММЕСЛИ поддерживает подстановочные знаки. Звездочка (*) означает «один или более символов», а вопросительный знак (?) означает «любой один символ».

Эти шаблоны позволяют создавать такие критерии, как «начинается с», «заканчивается», «содержит 3 символов» и так далее.

Чтобы соответствовать все предметы, которые начинаются на «Футб» место звездочки (*) перед текстом и после:

пункт «*Футб*»

Обратите внимание, что вы должны заключить текст и подстановочные знаки в двойные кавычки («»).

Альтернатива с СУММЕСЛИМН

Вы также можете использовать функцию СУММЕСЛИМН в сумме если клетки начала. СУММЕСЛИМН может обрабатывать несколько критериев, и порядок аргументов отличается от СУММЕСЛИ. Эквивалентная формула СУММЕСЛИМН-это:

=СУММЕСЛИМН(Диапазон Сумма,диапазон Товар ,Условие)

Обратите внимание, что диапазон суммы всегда приходит первым в функции СУММЕСЛИМН.

Файл Excel Если сумма заканчивается или частичное совпадение СУММЕСЛИ и СУММЕСЛИМН devsap

 

Если сумма равна либо x или Y СУММЕСЛИ СУММ СУММПРОИЗВ именованный диапазон devsap

Если сумма равна либо x или Y СУММЕСЛИ СУММ СУММПРОИЗВ именованный диапазон devsap

Связанные формулы

Сумма, если ячейки равны
Если сумма начинается с
Сумма если не пустой
Сумма, если ячейки не равны
Сумма, если ячейки содержат определенный текст
СУММЕСЛИМН с несколькими критериями и / или логики

Если вам нужно сложить числа на основе других ячеек равным либо одно значение, либо другое (либо x или Y), можно использовать функцию СУММЕСЛИ.

В примере показано, мы  подводим продажи из Шатуры или Рошаль . Формула в ячейке H6 составляет:

=СУММЕСЛИ(Город;$C$8;Сумма)+СУММЕСЛИ(Город;$C$7;Сумма)

Где город-это именованный диапазон С7:С15  и сумма именованный диапазон D7:15.

Каждый экземпляр СУММЕСЛИ обеспечивает промежуточный итог, один для продаж В Шатуре, один для продаж В Рошаль . Формула просто соединяет эти два результата вместе.

СУММЕСЛИ с аргументом массив

Более элегантное решение-дать функцию СУММЕСЛИ более одного значения для критериев, используя массив константу. Для этого построить нормальный СУММЕСЛИ, но пакет критерии в синтаксисе массива фигурные скобки, с отдельных элементов, разделенных запятыми. Наконец, обернуть все функции СУММЕСЛИ в функции СУММ. Это необходимо, потому что СУММЕСЛИ будет возвращать один результат для каждого элемента в массиве критериев. Они должны быть добавлены вместе, чтобы получить единый результат.

Формула в ячейке Н7 является:

=СУММ(СУММЕСЛИ(Город;{«Шатура»;»Рошаль»};Сумма))

Функция СУММПРОИЗВ альтернатива

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

= СУММПРОИЗВ(сумма *((Город=»Шатура») + (Город=»Рошаль»)))

Это может также быть написано как:

=СУММПРОИЗВ(Сумма*(Город={«Шатура»;»Рошаль»}))

Функция СУММПРОИЗВ не так быстро, как СУММЕСЛИ, но разница в скорости не заметно с небольших наборов данных.

Файл Excel Если сумма равна либо x или Y СУММЕСЛИ СУММ СУММПРОИЗВ именованный диапазон devsap

 

Если сумма превышает devsap

Если сумма превышает devsap

=СУММЕСЛИ(диапазон,»>50″)

Связанные формулы

Если сумма меньше, чем
Сумма если между
Если сумма начинается с
Сумма если не пустой
Сумма, если ячейки равны
Сумма, если ячейки не равны
Если сумма равна либо x или Y
Сумма, если ячейки содержат определенный текст

В сумме если больше, вы можете использовать функцию СУММЕСЛИ.

В приведенном примере ячейка Н7 содержит такую формулу:

=СУММЕСЛИ($D$7:$D$14;»>50″;D7:D15)

 

Эта формула суммирует суммы в столбце D когда их более 50.

Как работает формула

Функция СУММЕСЛИ поддерживает логические операторы Excel (т. е. «=»,»>»,»>=», и др.), так что вы можете использовать их как вам нравится в ваших критериях.

В данном случае, мы хотим, чтобы соответствовать суммам больше 50 и ассортимент «критерии» это же как «диапазон суммы», поэтому нет необходимости, чтобы войти в диапазон суммы в качестве последнего аргумента.

Функция СУММЕСЛИ просто суммирует все суммы свыше 50.

Обратите внимание, что оба оператора (>) и пороговой суммы заключены в двойные кавычки («»).

Если вы хотите включить порог Кол-во сумма, используйте больше или равно (>=), вот так:

=СУММЕСЛИ($D$7:$D$14;,»>=»)

Используя ссылку на ячейку

Если вы хотите выставить пороговое значение на листе так, чтобы ее легко можно изменить, используйте следующую формулу:

=СУММЕСЛИ(диапазон,»>»&А1)

Где A1-ссылка на ячейку, содержащую число пороговое значение.

Альтернатива с СУММЕСЛИМН

Вы также можете использовать функцию СУММЕСЛИМН. СУММЕСЛИМН может обрабатывать несколько критериев, но порядок аргументов отличается от СУММЕСЛИ. Эквивалентная формула СУММЕСЛИМН-это:

=СУММЕСЛИМН(сумма,сумма,»>50″)

Обратите внимание, что диапазон суммы всегда приходит первым в функции СУММЕСЛИМН. Также обратите внимание, что критерии СУММЕСЛИМН нужно вводить в  (/диапазон критериев), что означает именованный диапазон «сумма» должны быть введены дважды: один раз как диапазон суммы, и еще как диапазон критериев.

Файл Excel Если сумма превышает devsap

Если сумма меньше, чем Devsap

Если сумма меньше, чем Devsap

=СУММЕСЛИ(диапазон,»<50″)

Связанные формулы

Если сумма превышает
Сумма если между
Если сумма начинается с
Сумма если не пустой
Сумма, если ячейки равны
Сумма, если ячейки не равны
Если сумма равна либо x или Y
Сумма, если ячейки содержат определенный текст

В сумму, если менее, вы можете использовать функцию СУММЕСЛИ.

В приведенном примере ячейка Н7 содержит такую формулу:

=СУММЕСЛИ($D$7:$D$14;»<50″;D7:D15)

 

Эта формула суммирует суммы в столбце D, когда они находятся менее чем в 50.

Как работает формула

Функция СУММЕСЛИ поддерживает логические операторы Excel (т. е. «=»,»>»,»>=», и др.), так что вы можете использовать их как вам нравится в ваших критериях.

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

Функция СУММЕСЛИ просто суммирует все суммы меньше 50.

Файл Excel Если сумма меньше, чем devsap

 

Если сумма нескольких критериев devsap

Если сумма нескольких критериев devsap

=СУММЕСЛИМН(диапазон сумирования, диапазон критерий 1, критерий, диапазон критерий 2, критерий 2)

Связанные формулы

Если сумма равна либо x или Y
так же можно использовать с датами находящимися в разных диапазонах и получать по ним суммы.

Если вам нужно сложить числа на основе нескольких критериев, вы можете использовать функцию СУММЕСЛИМН.

В показанном примере, формула в H8 это:

=СУММЕСЛИМН($D$7:$D$14;$B$7:$B$14;»1″;$C$7:$C$14;»>10″)

Как эта формула работает

Первый диапазон ($D$7:$D$14) клетки из которых забираются суммы при совпадении всех критериев (условий)

Критерии поставляются в парах…(/диапазон критериев).

Первая пара критериев ($B$7:$B$14 / «1»). Это означает ячеек $B$7:$B$14 должно содержать «1».

Вторая пара критериев ($C$7:$C$14;»>10″). В ячейки $C$7:$C$14; должен *любой* быть > 10.

Эти ячейки в диапазоне суммы будут суммироваться когда все критерии совпадут.

 

Файл Excel Если сумма нескольких критериев devsap

 

Сумма если не пустой критерий не равно равно и не равно DEVSAP

Сумма если не пустой критерий не равно равно и не равно DEVSAP

=СУММЕСЛИ(диапазон,»<>»,диапазон суммирования)

Связанные формулы

Сумма, если ячейки равны
Если сумма равна либо x или Y

Чтобы сумма ячеек при определенных значениях не пустой, можно использовать функцию СУММЕСЛИ.

В примере, в ячейке H6 имеет следующую формулу:

=СУММЕСЛИ(C7:C14;»<>»;D7:D14)

Эта формула суммирует  в столбце C только когда значение в столбце c не пусто

Как работает формула

Функция СУММЕСЛИ поддерживает все стандартные операторы Excel, включая неравенство, которое является входным сигналом .

При использовании оператора в критериях для такой функции, как СУММЕСЛИ, нужно заключать в двойные кавычки («»). При использовании только «» в критерии можно придумать смысл, как «не равно пусто» или «не пустые».

Альтернатива с СУММЕСЛИМН

Вы также можете использовать функцию СУММЕСЛИМН сумма, если ячейки не пустые. СУММЕСЛИМН может обрабатывать несколько критериев, и порядок аргументов отличается от СУММЕСЛИ. Это Суммеслимн формула эквивалентна формуле выше СУММЕСЛИ:

=СУММЕСЛИМН(D7:D14;C7:C14;»<>»)

С Суммеслимн, диапазон сумма всегда приходит первым.

 

 

Файл Excel Сумма если не пустой критерий не равно равно и не равно devsap

СУММПРОИЗВ по ДЕНЬНЕД дням недели с именованными диапазонами DEVSAP

СУММПРОИЗВ по ДЕНЬНЕД дням недели с именованными диапазонами DEVSAP

=Функция СУММПРОИЗВ((ДЕНЬНЕД(Дата)=номер дня недели)*сумм)

Связанные формулы

Счет если по дням недели

Для суммирования данных по дням недели (т. е. сумма по понедельникам, вторникам, средам, и др.), вы можете использовать функция СУММПРОИЗВ вместе с функция ДЕНЬНЕД.

В показанном примере, формула на I7-это:

=СУММПРОИЗВ((ДЕНЬНЕД(День;2)=H7)*Сумм)

Как эта формула работает

Вы можете спросить, почему мы не используем СУММЕСЛИ или СУММЕСЛИМН функция? Они, кажется очевидны, способ получить суммs по дням недели. Однако нет никакого способа, чтобы создать критерии для СУММЕСЛИ что учитывает день недели.

Вместо этого мы используем удобные функции функция СУММПРОИЗВ, который обрабатывает изящно массивы без необходимости использовать Ctr + Shift + ввод.

Мы используем функцию СУММПРОИЗВ и только один аргумент, который состоит из этого выражения:

(ДЕНЬНЕД(День;2)=H7)*Сумм

День недели функция настроена с дополнительным аргументом 2, который возвращает  число дня недели 1-7 дней с понедельника по воскресенье, соответственно.

Ежедневные вычисляет каждое значение в именованном диапазоне «день» и возвращает число. Результатом является массив такой:

{7;5;6;7;1;4;4;1;5;5;2;1;4}

Цифры вернулись на будний день, то по сравнению со значением в H6, который 1.

{7;5;6;7;1;4;4;1;5;5;2;1;4}=1

Результатом является массив значений True или false.

{ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ}

Далее этот массив умножается на значения именованного диапазона «Сумм». Функция СУММПРОИЗВ работает только с цифрами (не текстовые или логические значения), но математические операции автоматически выполнятся истинные/ложные  единицы и нулей, так что мы имеем:

{0;0;0;0;1;0;0;1;0;0;0;1;0}*{100;50;321;87;9889;2138;388;873;12;345;43;3457;12}

Который дает:

{0;0;0;0;9889;0;0;873;0;0;0;3457;0}

Всего один массив для обработки, функция СУММПРОИЗВ суммы элементов и возвращает результат.

Файл EXCEL СУММПРОИЗВ по ДЕНЬНЕД дням недели с именованными диапазонами devsap

 

Динамические таблицы подстановки ВПР, СУММЕСЛИ и ДВССЫЛ с именованными диапазонами devsap

Динамические таблицы подстановки ВПР, СУММЕСЛИ и ДВССЫЛ с именованными диапазонами devsap

Динамические таблицы подстановки ВПР, СУММЕСЛИ и ДВССЫЛ  с именованными диапазонами

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

СУММЕСЛИ      Суммирует ячейки, удовлетворяющие заданному условию.

ДВССЫЛ   Определяет ссылку, заданную текстовым значением.

 

=ВПР(критерий,таблица,столбец,истина)

таблица  = ДВССЫЛ(текст, значение ,имя таблицы именованного диапазона)

Связанные формулы

СУММЕСЛИ для нескольких листов

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

В примере, приведенном в Формуле G6-это:

=ВПР(F6;ДВССЫЛ(E6);2;0)

 

Цель этой формулы в том, чтобы  удобно переключать диапазоны в таблице внутри функции поиска. Один способ решает это, чтобы создать именованный диапазон для каждой таблицы, и ссылаться на именованный диапазон внутри ВПР. Однако, если вы просто попробуйте дать ВПР массив таблиц в виде текста (например, «таблица1») формулу не будет работать. Косвенная функция необходима для разрешения текста действительной ссылки.

Как эта формула работает

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

В показанном примере два именованных диапазона были созданы: «таблица1» относится к В5:С9, и «таблица2» относится к В11:В15*.

ДВССЫЛ текст в е6 («таблица1») и решает это именованный диапазон Таблица1, которая решает В5:В9, который возвращается в ВПР.

ВПР осуществляет поиск и возвращает значение из таблицы

 

Файл EXCEL Динамические таблицы подстановки ВПР, СУММЕСЛИ и ДВССЫЛ с именованными диапазонами devsap