Получить первое, последнее или определенное значение или совпадение по определенному критерию

Получить первое не пустое значение в списке

Если вам нужно получить первое не пустое значение (текст или число) в диапазоне в одной колонке вы можете использовать формулу массива на основе функций ИНДЕКС, ПОИСКПОЗ и ЕПУСТО.

В данном примере мы используем эту формулу:

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

Таким образом, нам нужно строить функциональные возможности для нужных нам формул. Способ сделать это состоит в использовании функции массива, которая «тестирует» ячейки и возвращает массив истина/ложь значения, которые мы можем сопрягать с ПОИСКПОЗ.

Работая изнутри, ЕПУСТО оценивает ячейки в диапазоне В3: В11 и возвращает результат и массив, который выглядит следующим образом:

Каждая ЛОЖЬ представляет собой ячейку в диапазоне, который не является пустой.

Далее, ПОИСКПОЗ ищет ЛОЖЬ внутри массива и возвращает позицию первого наденного совпадения, в этом случае 2. На данный момент, формула в примере теперь выглядит следующим образом:

И, наконец, функция ИНДЕКС выводит значение в положении 2 в массиве, в этом случае число 10.

Excel Index Function (Range Format) Examples

Example 1

In the following example, the Index function returns a reference to row 4 and column 2 of the 1st area in the supplied range. This is cell D4, which evaluates to the value 5.

Formula
Result

Example 2

In the following example, the Index function returns a reference to row 3 and column 1 of the 3rd area in the supplied range. This is cell B12, which evaluates to the value 7.

Formula
Result

Example 3

zero In the following example, as the supplied is blank, the Index function returns a reference to all of row 3 of the 3rd area of the supplied range. This is the range B12:D12.

The result of the Index function is then passed into the SUM function, which returns the value 10.

For further details and examples of the Excel Index function, see the Microsoft Office website.

Как сравнить столбцы с эталонными значениями и вычислить степень соответствия

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

Выберите столбцы для сравнения.

Напр., столбцы с данными прототипов.

Выберите Сопоставить с диапазоном эталонных столбцов Выберите столбцы эталонных значений.

Напр., столбцы со стандартами.

Отметьте Показывать процент соответствия , чтобы степень соответствия отображалась в процентах.

В противном случае результат отобразится как 1 (полное соответствие) или 0 (нет соответствия).

Сопоставление столбцов Excel c эталонными значениями и расчет процента соответствия

Прочтение результата: прототип Тип 2 на 99% соответствует Стандарту 2, т.е. 99% их параметров в строках совпадают. Продукт 5 ближе всего к Стандарту 3 – 96% их параметров идентичны. В то же время Продукт 4 далёк от соответствия какому-либо из трёх стандартов. Теперь можно сделать вывод, насколько каждый из прототипов отклоняется от целевых эталонных значений.

Несколько полезных функций Google Spreadsheets: IMPORTRANGE, VLOOKUP, MATCH, INDEX

Основное преимущество Google Docs — возможность совместной работы в режиме «онлайн», просмотра изменений,
сделанных каждым участником, и автоматическим сохранением актуальной версии (не будет больше обидных вылетов и выключений компьютера без сохраненного текста или документа!).

Особенно полезны Google Spreadsheets, или Google таблицы — аналог приложения Excel.

Они могут пригодиться вам для:

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

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

Функция IMPORTRANGE

Для переноса данных из одного файла в другой в Google Таблицах используется функция IMPORTRANGE.

Для чего она может пригодиться?

Например, вам нужны актуальные данные из файла ваших коллег;

или вы хотите обрабатывать данные из файла, к которому у вас есть доступ «Только для просмотра»;

или вы хотите собрать в одном документе таблицы из нескольких и вместе их обрабатывать или просматривать.

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

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

spreadsheet_key (ключ_таблицы) – последовательность символов в атрибуте «key left»>Иначе говоря, ключ таблицы — это последовательность символов в ссылке на таблицы после «spreadsheets/d/»

Вместо ключа таблицы вы можете использовать полную ссылку на документ:

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

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

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


Видео:

IMPORTRANGE как аргумент другой функции

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

Рассмотрим простой пример — среднее значение по продажам из диапазона, находящегося в другом документе.

Это исходный документ. Пусть данные будут добавляться и нам нужно среднее по продажам 2016 (то есть от ячейки D2 и до упора вниз)

Сначала импортируем этот диапазон:

А потом используем это как аргумент функции СРЗНАЧ (AVERAGE):

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

Функция ВПР (VLOOKUP)

Это функция — аналог функции ВПР в Excel. Она необходима для переноса данных из одной таблицы в другую.

У нее следующие аргументы:

VLOOKUP (искомое значение; таблица; номер столбца; интервальный_просмотр)

искомое значение — это то значение, которое мы будем искать в другой таблице.

Например, в отдельном файле у вас есть стандартная ставка перевода книг с английского — по трем категориям сложности.

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

Таблица — это диапазон данных, из которого вы подтягиваете информацию.

Аргумент «номер столбца» определяет , из какого столбца ТАБЛИЦЫ (а не листа! это важно) вы будете брать данные.
Интервальный_просмотр обычно равен нулю — в таком случае будет вестись точный, а не приблизительный поиск.

Функция ПОИСКПОЗ (MATCH)

Функция ПОИСКЗПОЗ (в английской версии Excel и Google Таблиц она называется MATCH) позволяет определить порядковый номер элемента (обычно — текста, записанного в ячейке) в определенном списке.

Читайте также:  Расширенный сравнительный анализ продаж товаров скачать в Excel

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

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

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

MATCH (искомое_значение; список; точный поиск)

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

В примере ошибка #N/A возникает только в тех случаях, в которых соответствующего элемента нет во втором списке.

Сочетание функций ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH)

К сожалению, функция ВПР (VLOOKUP) не работает, когда искомые значения в исходной таблице находятся не в первом столбце.

Но вы можете воспользоваться сочетанием функций ПОИСКПОЗ (MATCH — обсуждалась ранее) и ИНДЕКС (INDEX).

Функция ИНДЕКС возвращает элемент из списка по его порядковому номеру.

А порядковый номер вы определяете с помощью MATCH.

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

Функция «QUERY»

[adace-ad >Функция QUERY позволяет сделать выборку нужных строк из таблицы с помощью SQL-запроса и отсортировать их. Сложность в том, что нужно знать синтаксис SQL, но самую базовую выборку делать очень легко. В сети много примеров бездарного использования функции QUERY, мы же сейчас сделаем крутой выпадающий список с фильтрацией данных

Подготовка. Парсим данные. Делаем выпадающий список.

Итак, у нас есть обычная таблица с несколькими столбцами данных. Лист так и называется — «Данные».

На листе «Проверка» мы с помощью функции UNIQUE отбираем уникальные значения для для столбца А и столбца B

Выбираем нужный диапазон для каждого столбца из листа «Данные»

В итоге, в листе «Проверка» у нас будут два столбца с уникальными значениями. Добавим к ним функцию SORT для, ожидаемо, сортировки. В итоге формулы у нас будут следующие: = SORT ( UNIQUE ( ‘Данные’!A2:A ) ) и =SORT(UNIQUE( ‘Данные’!B2:B ))

Переходим на лист «Отчет». Подписываем наши два столбца, как Партнеры и Пиццерии. После чего, заходим в меню «Данные» и выбираем «Настроить проверку данных».

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

Сам диапазон мы берем из листа «Проверка», где у нас собраны и отсортирована выборка из уникальных значений основной таблицы. Соответственно, для партнеров мы выбираем «Столбец А». Аналогичную операцию проделываем с пиццериями («Столбцом B»)

Не забудьте поставить пункт «Запрещать ввод данных» после выбора диапазона и нажимайте «Сохранить»:

После того, как вы это сделали, у нас будет готов шаблон для выпадающих списков в листе «Отчет»:

Основная часть. Делаем запросы. Фильтруем данные.

Теперь мы воспользуемся функцией Query, чтобы выцепить из основной таблицы (лист «Данные») нужные нам элементы. Пусть это будет «Партнёр» и «Город». Пишем такую формулу: =QUERY(‘Данные’!A1:D24;»SELECT * WHERE A = ‘Партнер1’ AND D = ‘Москва’»;1) . И получаем отфильтрованные данные

С помощью формулы IF сделаем заготовку для нашего фильтра.

В итоге у нас получится выбор данных при изменении нашего выпадающего списка для каждого значения

А сейчас объединим эти две формулы, чтобы был один полноценный запрос:

Теперь нужно это всё перенести в функцию query, чтобы выпадающий список фильтровал запросы. Для этого нужно удалить часть запроса в query (на скрине как раз выделена эта часть):

Итоговая формула будет выглядеть вот так: = QUERY ( ‘Данные’!A1:D24 ; «SELECT * WHERE 1=1» & IF ( A2 = «Все партнеры» ; «» ; » AND LOWER(A) = LOWER(‘» & A2 & «‘) « ) & IF ( B2 = «Все пиццерии» ; «» ; » AND LOWER(B) = LOWER(‘» & B2 & «‘) « ) ; 1 )

Результатом станет то, что при выбора Партнера и пиццерии (можно оставить все), функция query будет показывать нам отфильтрованные данные из основной таблицы:

Симптомы вегето-сосудистой дистонии

ВСД характеризуется проявлением симпатических, парасимпатических или смешанных симптомокомплексов [1] . Преобладание тонуса симпатической части ВСН (симпатикотония) выражается в тахикардии, бледности кожи, повышении артериального давления, ослаблении сокращений стенок кишечника (перистальтике), расширении зрачка, ознобе, ощущении страха и тревоги [2] . Гиперфункция парасимпатического отдела (ваготония) сопровождается замедлением сердцебиения (брадикардией), затруднением дыхания, покраснением кожи лица, потливостью, повышенным слюноотделением, снижением артериального давления, раздражением (дискинезией) кишечника [2] .

Взаимодействие с другими операторами

Теперь рассмотрим пример использования функции ПОИСКПОЗ с оператором ИНДЕКС. На практике ее можно комбинировать со многими другими функциями, если знать, по каким принципам она работает.

ИНДЕКС – функция поиска совпадений по двум параметрам. Данная формула является аналогом вышеописанного примера, когда необходимо вернуть значение по нескольким условиям. ИНДЕКС используется только в случае двумерного поиска, в то время, когда ВПР, ГПР или ПРОСМОТР предназначены для одномерного.

Допустим, у нас есть таблица со статистикой продаж сети автосалонов по месяцам. Попробуем найти количество проданных автомобилей марки Audi в сентябре. Начинаем составление формулы с ИНДЕКС и указания диапазона всего массива.

Вторым аргументом будет функция ПОИСКПОЗ с указанием ссылки на ячейку с названием месяца и диапазона столбца массива:

Последний аргумент – еще одна ПОИСКПОЗ, но теперь с ссылками на название марки и строкой с автомобилями:

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

Теперь можно менять значения в нашей таблице справа и получать новые значения:

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

По представленному примеру описываемый оператор может использоваться во многих формулах. Например:

  • ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ));
  • ЕСЛИ(ЕНД(ПОИСКПОЗ(ИСТИНА;(СОВПАД))));
  • ИНДЕКС(ПОИСКПОЗ(МИН(ЕСЛИ))) и другие.

Если вы знакомы с функционалом VBA для Microsoft Excel, то сможете создать надстройку для автоматического вычисления и поиска необходимых значений.

Как найти абсолютное максимальное значение в Excel

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

Первая идея, которая приходит в голову, — получить абсолютные значения всех чисел в диапазоне с помощью функции ABS и передать их в МАКС:

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

Не нравится это решение? Тогда давайте построим что-нибудь более жизнеспособное 🙂

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

С исходными данными в A1: B5 расчёты выглядят следующим образом.

Читайте также:  Как найти одинаковые строки в Excel и выделить их цветом

Формула массива (заполняется с помощью Ctrl + Shift + Enter):

Обычная формула (заполняется клавишей Enter):

На скриншоте ниже показаны результаты:

2. СУММПРОИЗВ

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

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

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

С помощью функции СУММПРОИЗВ мы можем легко посчитать этот процент.

СЧЁТЕСЛИ и СЧЁТЕСЛИМН

Часто бывает нужно посчитать количество строк или заполненных ячеек по каким-то параметрам, например узнать количество работников старше 40 лет, или посчитать все строки, где есть слово «переменная». Для этого используют функцию СЧЁТЕСЛИ:

Диапазон — это группа ячеек, в которых мы будем что-то искать, а условие — это по какому параметру мы решим, что ячейка нам подходит.

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

Теперь посложнее. Если нам нужно посчитать количество ячеек по нескольким условиям одновременно, то используют функцию СЧЁТЕСЛИМН:

То, что в прямоугольных скобках — дополнительные условия, их может быть максимум 127, а сами скобки при этом ставить не нужно. Обратите внимание что для каждого нового условия можно задать свой диапазон. Функция проверит все условия, каждое в своём диапазоне, и если все условия одновременно выполняется — посчитает количество таких ячеек.

Для примера найдём все траты больше 5000 в одном чеке, за которые отвечает Миша:

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

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

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

SORTN

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

  1. Диапазон для сортировки и вывода. (Впрочем, столбцы, по которому данные сортируются, можно не включать в этот диапазон, указать их отдельно в четвертом параметре.)
  2. Количество возвращаемых элементов. (Можно сделать топ-3, топ-5 и т. д.)
  3. Режим показа совпадений. (По умолчанию ноль. Единица, например, будет выводить дополнительные строки, — больше, чем указано во втором параметре — если в столбце для сортировки найдутся повторяющиеся значения.)
  4. Столбец для сортировки. (Может быть вне диапазона, указанного в первом параметре.)
  5. Способ сортировки столбца. ИСТИНА (TRUE) сортирует данные по возрастанию (от меньшего к большему), а ЛОЖЬ (FALSE) – по убыванию (от большего к меньшему).

(Если нужно, дальше можно также задать дополнительные столбцы и варианты сортировки.)

Допустим, есть таблица с показателями трафика за несколько месяцев. И нужно подготовить топ лучших или худших страниц по динамике за последние два. Для этого как раз хорошо подходит функция SORTN.

Пример: =SORTN(A2:F7;3;0;6;TRUE), которая выводит данные из A2:F7, но только первые три строки, отсортированные по шестому столбцу (F) по возрастанию:

20+ продвинутых функций Google Таблиц (Spreadsheets)

Если столбец для сортировки не входит в первый диапазон, нужно передать его четвертым параметром (главное условие — такое же количество элементов, как у первого). Пример: =SORTN(A2:E7;3;0;F2:F7;TRUE)

20+ продвинутых функций Google Таблиц (Spreadsheets)

=AVERAGE(number1, [number2], …) – английская версия

=СРЗНАЧ(число1;[число2];…) – русская версия

Функция СРЗНАЧ относится к группе «Статистические». Поэтому для вызова данной функции в Excel необходимо выбрать инструмент: «Формулы»-«Библиотека функций»-«Другие функции»-«Статистические»-«СРЗНАЧ». Или вызвать диалоговое окно «Вставка функции» (SHIFT+F3) и выбрать из выпадающего списка «Категория:» опцию «Статистические». После чего в поле «Выберите функцию:» будет доступен список категории со статистическими функциями где и находится СРЗНАЧ, а также СРЗНАЧА.

Если есть диапазон ячеек B2:B8 с числами, то формула =СРЗНАЧ(B2:B8) будет возвращать среднее значение заданных чисел в данном диапазоне:

Синтаксис использования следующий: =СРЗНАЧ(число1; [число2]; …), где первое число обязательный аргумент, а все последующие аргументы (вплоть до числа 255) необязательны для заполнена. То есть количество выбранных исходных диапазонов не может превышать больше чем 255:

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

Аргументы функции СРЗНАЧ могут быть представлены не только числами, но и именами или ссылками на конкретный диапазон (ячейку), содержащий число. Учитывается логическое значение и текстовое представление числа, которое непосредственно введено в список аргументов.

Если аргумент представлен ссылкой на диапазон (ячейку), то его текст или логическое значение (ссылка на пустую ячейку) игнорируется. При этом ячейки, которые содержат нуль, учитываются. Если аргумент содержит ошибки или текст, который не может быть преобразован в число, то это приводит к общей ошибке. Для учета логических значений и текстового представления чисел необходимо в расчетах использовать функцию СРЗНАЧА, о которой пойдет разговор дальше.

Результат выполнения функции в примере на картинке ниже – это число 4, т.к. логические и текстовые объекты игнорируются. Поэтому:

(5 + 7 + 0 + 4) / 4 = 4

При вычислениях средних значений необходимо учитывать разницу между пустой ячейкой и ячейкой, содержащей нулевое значение, особенно если в диалоговом окне Excel снят флажок «Показывать нули в ячейках, которые содержат нулевые значения». Когда флажок установлен, пустые ячейки игнорируются, а нулевые значения нет. Чтобы снять или установить этот флаг необходимо открыть вкладку «Файл», далее нажать на «Параметры» и выбрать в категории «Дополнительно» группу «Показать параметры для следующего листа», где возможно установить флажок:

Результаты еще 4-х задач сведены в таблицу ниже:

Как видно на примере, в ячейке A9 функция СРЗНАЧ имеет 2 аргумента: 1 – диапазон ячеек, 2 – дополнительное число 5. Так же могут быть указаны в аргументах и дополнительные диапазоны ячеек с числами. Например, как в ячейке A11.

Описание функции ИНДЕКС

ИНДЕКС входит в категорию операторов “Ссылки и массивы”, причем имеет два разных набора аргументов:

1. Для массивов

Формула функции в данном случае выглядит так:

Может быть заполнен один из двух аргументов: “Номер_строки” или “Номер_столбца”. Все зависит от того, с каким массивом приходиться иметь дело. Если, например, массив занимает только одну строку, то аргумент “Номер_строки” не актуален, и заполняется только “Номер_столбца”. Для массива, занимающего всего лишь один столбец все аналогично. Здесь есть один нюанс – указывать нужно номера строк и столбцов, ориентируясь не на общие обозначения на панелях координат программы, а на порядковые номера внутри заданного массива.

2. Для ссылок

Формула функции выглядит следующим образом:

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

Читайте также:  Построение линейной диаграммы в Excel

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

Как используется функция ПОИСКПОЗ в Excel

Добрый день!

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

Итак, знакомство начнём прямо сейчас, и первым шагом будет, знакомство с орфографией функции ПОИСКПОЗ. Эта функция ищет значение указанное вами и возвращает позицию полученного значения в диапазоне. К примеру, у нас есть диапазон B1:B3 в котором прописаны значения: Январь, Февраль, Март и последующая формула должна вернуть цифру 2, поскольку значение «Февраль», является вторым элементом в перечне.

=ПОИСКПОЗ(«Февраль»;B2:B13;0)

Синтаксис функции ПОИСКПОЗ

Как видно из примера функция ПОИСКПОЗ имеет следующий синтаксис, который будем рассматривать более, подробнее:

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

  • «Искомое_значение» — этот аргумент отвечает за данные, которые вы ищите. Этими данными могут быть чиста, текст, любое логическое значение или просто ссылка на ячейку.
  • «Просматриваемый_массив» — это аргумент показывает диапазон ячеек, где будет производиться поиск;
  • «Тип_сопоставления» — этот аргумент позволяет узнать функции ПОИСКПОЗ, о том, какое совпадение искать: приблизительное или точное:

  • 1 или же без аргумента – будет искать максимальное значение, которое равно или же меньше искомого. В обязательном порядке массив, который просматривает функция ПОИСКПОЗ, вы должны упорядочить по возрастанию, от меньшего к большему.
  • – возвращает первое же значение, которое соответствует искомому. Этот аргумент позволяет произвести точный поиск.
  • -1 – этот аргумент найдет самое наименьшее значение, которое равняется или больше, нежели значение, которое ищете. В этом случае данные нужно упорядочить по убыванию от большого к малому.

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

Преимущества функции ПОИСКПОЗ

Первым преимуществом при использовании функции ПОИСКПОЗ и ее комбинации с другими функции (например, функция ИНДЕКС), это возможность поиска справа налево. Это отличительная черта от функции ВПР, так как она не может просматривать влево.

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

Третьим преимуществом – это отсутствие ограничение на длину значения, которое ищет функции ПОИСКПОЗ, в 255 символов. Ограничение для нее нет. А в функции ВПР, значение, которое вы ищете, и если оно превышает указанную величину, то вы получите ошибку #ЗНАЧ.

Четвёртое преимущество – это увеличенная скорость работы. В принципе для тех, кто работает с небольшими таблицами, ускоренная производительность незаметна, а вот уже при тысячах строк и сотнях формул, работа функции ПОИСКПОЗ самостоятельно или в тандеме с функцией ИНДЕКС будет работать, судя по данным в авторитетных изданиях, относительно функции ВПР на 13-15% быстрее. Так как проверка каждого значения в диапазоне значений вызывает отдельно функцию ВПР, а это значит что чем больше данных и формул в массиве, тем более неторопливо работает Excel.

Примеры работы функции ПОИСКПОЗ

Перейдем от теории к практике и рассмотрим несколько примеров, как работает функция ПОИСКПОЗ:

Найти СРЗНАЧ, МАКС, МИН с помощью комбинации функций ПОИСКПОЗ и ИНДЕКС

Функция ПОИСКПОЗ позволяет вкладывать в себя другие функции, и с помощью этой возможности вы можете найти максимальное, минимальное или самое ближайшее к среднему значению (статистические функции), примеры:

  1. Функция МИН: =ИНДЕКС($C$2:$C$9;ПОИСКПОЗ(МИН($I$2:I$9);$I$2:I$9;0));
  2. Функция МАКС: =ИНДЕКС($C$2:$C$9;ПОИСКПОЗ(МАКС($I$2:I$9);$I$2:I$9;0));
  3. Функция СРЗНАЧ: =ИНДЕКС($C$2:$C$9;ПОИСКПОЗ(СРЗНАЧ($I$2:I$9);$I$2:I$9;1))

Каждая функция возвращает нужное нам значение.

Поиск функцией ПОИСКПОЗ с левой стороны

Ранее я говорил, что функция ВПР не дружит с левой стороной, а значит, будем использовать функцию ПОИСКПОЗ в тандеме с функцией ИНДЕКС, так как они более гибкие и им не играет роли место расположения столбца. Рассмотрим на примере выбора, на каком месте по продажам стоит «Беларусь», и будем использовать для этого следующую формулу:

Рассмотрим формулу, более, подробнее. Во-первых, формула ПОИСКПОЗ(«Беларусь»;$D$2:$D$9;0) находит положение страны в списке. Во-вторых, функцией ИНДЕКС диапазона $I$2:$I$9 с которого будет производиться извлечение значения. В-третьих, соединим все две части формулы и получим нужный нам результат.

Внимание! При указании диапазонов в функции ПОИСКПОЗ, для улучшения и надёжности формулы желательно всегда использовать абсолютные ссылки.

Производим поиск по нескольким критериям

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

Следующая формула решит нашу проблему:

Если разобрать детально, эту более сложную формулу, то описать можно так: начнем с функции ПОИСКПОЗ, ищем мы значение 1, а массив нашего поиска, является результатом умножения, а именно, берется значение в первом столбике «Страна» в Таблица1 и сравниваем с именами всех стран в Таблице2. Если было найдено совпадение, формула возвращает 1 или ИСТИНА, а если нет, то или ЛОЖЬ. После делаем то же самое для столбика «Продукты». После всего этого перемножаем результаты и если совпадения найдены результат будет 1, если же нет, то получим – . Итак, функция ПОИСКПОЗ возвращает позицию в случае выполнения обоих критериев. Обязательно используем в формуле третий аргумент «3», так как была указана вся таблица и нужно уточнение, из какого же столбика нужно извлечь значения, в нашем случае столбик был третьим. Еще замечу, что это формула массива, поэтому мы заключаем ее в фигурные скобки и вводим «горячей» комбинацией Ctrl+Shift+Enter. Я думаю, что эти 3 примера, как используется функция ПОИСКПОЗ, поможет вам лучше понять эту функцию и научитесь ее использовать в своей работе.

Если у вас есть предложения, пишите комментарии, буду рад помочь!

Кто-то может сидеть в тени сегодня только потому, что кто-то в свое время посадил дерево.
Уоррен Баффетт

Ссылка на основную публикацию