Двусторонний поиск с индекс и поискпоз ( ПОИСКПОЗ и ИНДЕКС )

Двусторонний поиск с индекс и поискпоз ( ПОИСКПОЗ и ИНДЕКС )

=ИНДЕКС(данные ПОИСКПОЗ(критерий 1;столбец;1);ПОИСКПОЗ(критерий 2;строк;1))

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

Двусторонний поиск с ВПР

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

В показанном примере, формула k6 является:

=ИНДЕКС($D$6:$H$10;ПОИСКПОЗ($K$4;$D$5:$H$5);ПОИСКПОЗ($K$5;$C$6:$C$10))

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

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

Ядром этой формулы является индекс, который является просто получение значения от $D$6:$H$10 («данные») на основе номера строки и номера столбца.

=ИНДЕКС($D$6:$H$10;столбец;строка)

Чтобы получить номера строк и столбцов, мы используем ПОИСКПОЗ, настроенные на приблизительное соответствие, установив 3 аргумент 1 (истина):

ПОИСКПОЗ($K$5;$C$6:$C$10) // получаем количество строк
ПОИСКПОЗ($K$4;$D$5:$H$5) // получаем номер столбца

В примере, ПОИСКПОЗ вернет 60, Когда Столбц С = «3», а, строка d «D» = «4» .

В итоге, формула сводится к:

=Индекс($D$6:$H$10, 3, 4) = 60

 

 

Файл Двусторонний поиск с индекс и поискпоз ( ПОИСКПОЗ и ИНДЕКС ) DEVSAP

МЅ 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

=Функция СУММПРОИЗВ(СУММЕСЛИ(диапазон критерия,критерий,диапазон скмирования))

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

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

=Функция СУММПРОИЗВ(СУММЕСЛИ(Код,C5:I5,Значение))

=СУММПРОИЗВ(СУММЕСЛИ(Код;C6:I6;Значение))

Где коды-это именованный диапазон «Код» L5:L6 и «Значение» именованный диапазон M5:M6.

Контексте

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

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

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

Хитрость в данном случае в том, что критерии СУММЕСЛИ не одно значение, а массив значений в диапазон C5:I5 с:

=Функция СУММПРОИЗВ(СУММЕСЛИ(Код,C5:I5,Значение))

Потому что мы даем СУММЕСЛИ более чем одному критерию, СУММЕСЛИ будет возвращать более одного результата. В показанном примере результат СУММЕСЛИ есть следующий массив:

{1,0,0,0.5,0,0.5,0}

Обратите внимание, что мы правильно получите 1 Для каждого «Д» И 0,5 за каждую «Н»., и пустые значения в неделю генерировать ноль.

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

Файл Excel Поиск значений суммы с помощью СУММЕСЛИ и именованными диапазонами devsap

 

СЧЕТЕСЛИ поиск в диапазоне конкретных значений и совпадений + ЕСЛИ + "*"&

СЧЕТЕСЛИ поиск в диапазоне конкретных значений и совпадений + ЕСЛИ + «*»&

=СЧЕТЕСЛИ(диапазон; значение)>0

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

Диапазон содержит определенный текст
Спектр содержит одну из многих значений
Спектр содержит один из множества подстрок
Ячейка содержит определенный текст
Ячейка содержит одну из многих вещей

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

В показанном примере, формула в Д5:

=СЧЕТЕСЛИ(гсч,B5)>0

Функция СЧЕТЕСЛИ подсчитывает Количество ячеек, которые удовлетворяют заданным условиям возвращает число найденных вхождений. Если нет клетки, отвечающие критериям, СЧЕТЕСЛИ возвращает ноль. Любое положительное число означает значение было найдено. Это означает, что вы можете использовать оператор «больше» ( > ):

=СЧЕТЕСЛИ(диапазон; значения)>0

Вы также можете обернуть формулы внутри оператора ЕСЛИ, чтобы получить конкретный результат. Например, чтобы получить «да» или «нет», используйте:

=Если(СЧЕТЕСЛИ(диапазон,значение)>0,»Да»,»Нет»)

Это работает, поскольку если считает любое число > 0 как истина, когда она появляется как логический аргумент теста.

Частичное совпадение

Если вы хотите проверить диапазон, чтобы увидеть, если он содержит определенный текст (подстрока или частичный текст), вы можете добавить подстановочные знаки в Формуле. Например, если у вас есть значение поискать в ячейке B5, а вы хотите, чтобы проверить диапазон $F$5:$F$11, вы можете настроить СЧЕТЕСЛИ искать это значение в любом месте в клетке, объединяя звездочки:

=ЕСЛИ(СЧЁТЕСЛИ($F$5:$F$11;»*»&B5&»*»);»ок»;»ПРОВЕРИТЬ»)

Звездочка (*) является подстановочным знаком для одного или нескольких персонажей. Объединяя звездочки перед и после значения в B5 формула будет подсчитывать подстроки в B5 везде, где он появляется в каждой ячейке диапазона.

Файл СЧЕТЕСЛИ поиск в диапазоне конкретных значений и совпадений + ЕСЛИ + «*»& xlsx

 

Построить гиперссылку с ВПР

Построить гиперссылку с ВПР

=Гиперссылка(ВПР(название,Таблица,столбец 0),название)

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

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

=Гиперссылка(ВПР(E5,диапазон для поиска,2,0),E5)

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

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

Работая изнутри, ВПР ищет и возвращает значение по ссылке из столбца 2 именованного диапазона «таблица ссылок» (В5:В8). Искомое значение исходит из столбца E, и ВПР настроен на Точное «0» совпадение.

Результат поступает в гиперссылку как ссылка, и текст в столбце e используется для наименование ссылки.

Гиперссылка возвращает рабочую ссылку.

Файл Построить гиперссылку с ВПР xlsx

 

Вычислить-рейтинг-с-неполным-совпадением-ВПР-DEVSAP

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

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

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

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

Эта Таблица должна быть отсортирована по возрастанию, и ВПР должен быть настроен сделать «приблизительное соответствие».

В показанном примере, формула ВПР выглядит так:

=ВПР($F$6;$C$6:$D$17;2;1)

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

В данном случае $F$6-результат преобразовать в  (34 в примере) $C$6:$D$17-это таблица с оценкой и рейтингом, состоящий из 2-столбец таблицы 2 говорит ВПР, чтобы получить данные из колонки 2 (рейтинг), и верно говорит ВПР сделать «приблизительное соответствие».

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

Иными словами, ВПР  поиск последнего значения, которое меньше или равно значению поиска.

Если первое значение в таблице меньше значения, ВПР вернет #н/д ошибка.

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

 

 

Файл EXCEL Вычислить-рейтинг-с-неполным-совпадением-ВПР-devsap

СЧЕТЕСЛИ/СЧЕТЗ процентное соотношение

СЧЕТЕСЛИ/СЧЕТЗ процентное соотношение

=СЧЕТЕСЛИ(диапазон,критерий)/СЧЕТЗ(диапазон)

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

СЧЕТЕСЛИ

Для вычисления процентов ,можно использовать СЧЕТЕСЛИ , вместе с СЧЕТЗ.

В примере, показанаг в Формуле Н9:

=СЧЕТЕСЛИ(Диапазон,F9)/СЧЕТЗ(Диапазон)

=СЧЁТЕСЛИ($B$5:$B$10;F9)/СЧЁТЗ($B$5:$B$10)

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

СЧЕТЕСЛИ настроена на подсчет клеток в именованный диапазон «Статус», которая относится к $B$5:$B$10. Критерии  F9.

функция СЧЕТЗ просто подсчитывает все непустые ячейки в указанном диапазоне Статус ($B$5:$B$10) для получения общего количества.

Результат СЧЕТЕСЛИ делится на СЧЕТЗ результат процент. Все значения в столбце H  отформатированы «процент» процентный Формат.

 

Файл СЧЕТЕСЛИ/СЧЕТЗ процентное соотношение excel

СчЁтесли не равно Excel

СчЁтесли не равно Excel

=СЧЕТЕСЛИ («диапазон»,»критерий <>х»)

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

СЧЕТЕСЛИ

Если вам нужно подсчитать Количество ячеек, которые содержат значения, не равные определенному значению, можно использовать функцию СЧЕТЕСЛИ. В общем виде формула  диапазон ячеек, и X представляет значение (критерий). Суммируется все значения которые не попали под критерий.

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

=СЧЁТЕСЛИ( C5:C10;»<>100″)

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

СЧЕТЕСЛИ подсчитывает количество ячеек в диапазоне, которые удовлетворяют критериям.

В этом примере мы используем «» (логический оператор «не равно») и количество ячеек в диапазоне C5:C10, что не равно «100». СЧЕТЕСЛИ возвращает в результате суму значений ячеек не соответствующие критерию.

СЧЕТЕСЛИ является не чувствительным к регистру.

Если вы хотите использовать значение из другой ячейки как часть критерия, использование амперсанда ( & ), чтобы объединить так:

=СЧЕТЕСЛИ («диапазон»,»<>»&А1)

Если значение в ячейке А1 является «100», то критерии будут «100» после объединения, и СЧЕТЕСЛИ подсчет ячеек не будет равна 100.

Файл Подсчет значений <> не равно и СЧЁТЕСЛИ Excel

Общее число столбцов и строк в диапазоне Excel

Общее число столбцов и строк в диапазоне Excel

=ЧИСЛСТОЛБ(диапазон) Колонка

=ЧСТРОК(диапазон) Строка

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

Всего строк в диапазоне
Общее количество ячеек в диапазоне

Если вам нужно подсчитать количество столбцов в диапазоне, используйте функцию ЧИСЛСТОЛБ. В общем виде формулу ЧИСЛСТОЛБ представляет собой диапазон ячеек.

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

=ЧИСЛСТОЛБ(D5:E12)

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

ЧИСЛСТОЛБ подсчитывает количество столбцов и возвращает результат в виде числа.

 

Файл Общее число столбцов и строк в диапазоне xls