Базовая информация об ИНДЕКС и ПОИСКПОЗ
Поскольку цель данного руководства — показать возможности функций ИНДЕКС и ПОИСК для реализации вертикального поиска в Excel, мы не будем останавливаться на их синтаксисе и применении.
Мы приведем здесь минимум, чтобы понять его суть, а затем подробно рассмотрим примеры формул, которые показывают преимущества использования ИНДЕКС и ПОИСКПОЗ вместо ВПР.
ИНДЕКС – синтаксис и применение функции
Функция ИНДЕКС в Excel возвращает значение из массива с указанными номерами строк и столбцов. Функция имеет следующий синтаксис:
ИНДЕКС (массив; число_строк; )
ИНДЕКС (массив; номер_строки; )
У каждой темы есть очень простое объяснение:
- array — это диапазон ячеек, из которых нужно извлечь значение.
- row_num (row_number) — номер строки в массиве, из которой нужно извлечь значение. Если не указано, необходимо указать column_num).
- column_num (номер_столбца) — это номер столбца в массиве, из которого нужно извлечь значение. Если не указан, требуется аргумент line_number (номер_строки)
Если указаны оба аргумента, функция ИНДЕКС возвращает значение из ячейки на пересечении указанной строки и столбца.
Вот простейший пример функции ИНДЕКС):
= ИНДЕКС (A1: C10,2,3)
= ИНДЕКС (A1: C10,2,3)
Формула ищет диапазон A1: C10 и возвращает значение ячейки во 2-й строке и 3-м столбце, то есть из ячейки C2.
Очень просто, правда? Однако на практике вы не всегда знаете, какая строка и столбец вам нужны, и поэтому вам нужна помощь функции СРАВНЕНИЕ.
ПОИСКПОЗ – синтаксис и применение функции
Функция ПОИСКПОЗ в Excel ищет указанное значение в диапазоне ячеек и возвращает относительное положение этого значения в диапазоне.
Например, если диапазон B1: B3 содержит значения Нью-Йорк, Париж, Лондон, следующая формула вернет число 3, поскольку «Лондон» является третьим элементом в списке.
= ПЕРЕПИСКА («Лондон»; B1: B3,0)
= ПОИСК («Лондон»; B1: B3,0)
Функция ПОИСКПОЗ имеет следующий синтаксис:
ПОИСКПОЗ (искомое_значение, искомое_массив, )
ПОИСКПОЗ (искомое_значение; искомое_массив; )
- lookup_value — это число или текст, который вы ищете. Аргумент может быть значением, включая логическое значение, или ссылкой на ячейку.
- lookup_array (lookup_array) — диапазон ячеек для поиска.
- match_type (match_type) — этот аргумент сообщает функции ПОИСКПОЗ, хотите ли вы найти точное или приблизительное совпадение:
- 1 или не указано — находит максимальное значение, меньшее или равное искомому. Сканируемый массив должен быть в порядке возрастания, то есть от наименьшего к наибольшему.
- 0 — найти первое значение, равное искомому. Комбинация ИНДЕКС / ПОИСКПОЗ всегда требует точного совпадения, поэтому третий аргумент функции ПОИСКПОЗ должен быть равен 0.
- -1 — Найдите наименьшее значение, большее или равное желаемому. Сканируемый массив должен быть в порядке убывания, то есть от наибольшего к наименьшему.
На первый взгляд полезность функции ПОИСКПОЗ сомнительна. Кому нужно знать положение элемента в диапазоне? Мы хотим знать значение этого элемента!
Напомню, что относительное положение искомого значения (т.е номер строки и/или столбца) — это именно то, что нам нужно указать для аргументов row_num (row_num) и / или column_num (column_num) функции ИНДЕКС. Как вы помните, функция ИНДЕКС может возвращать значение, расположенное на пересечении данной строки и столбца, но не может определить, какая строка и столбец нас интересуют.
Как работает функция?
Программа после поиска искомого значения переходит во второй столбец, чтобы извлечь возвращаемый элемент.
С одним условием
Рассмотрим функцию на простом примере поиска сотрудника по присвоенному ему коду. Таблицу нужно отсортировать в порядке возрастания.
- Кликнуть по ячейке справа от таблицы или под ней и вписать искомый номер.
- В ячейке рядом ввести функцию ВПР через знак «=», либо же использовать опцию «Вставить функцию» в разделе «Формулы». Проще использовать именно опцию – там есть подсказки, какие именно данные нужны.
- Задать параметры поиска, заполнив соответствующие поля. Интервальный просмотр в данном случае пропустим.
- Нажать «Enter» – и появится результат поиска.
Когда таблица не отсортирована и данные введены в хаотичном порядке, результат будет неправильный – программа найдет ближайшее соответствие («ИСТИНА»). Но можно не сортировать таблицу, а указать интервальный просмотр «ЛОЖЬ».
Чтобы найти другой элемент, следует просто изменить первый аргумент, и результат автоматически преобразится.
С несколькими условиями
Часто на практике требуется сравнить данные нескольких диапазонов и выбрать значение с учетом 2-х и более критериев. Здесь задействована также функция ЕСЛИ, которая отвечает как раз за условия.
Из таблицы нужно найти показатель выручки по конкретному менеджеру в определенный день:
- В первую выделенную ячейку вписать дату, во вторую – фамилию менеджера. В третьей ячейке будет происходить поиск.
- Кликнуть по пустой ячейке и ввести формулу =ВПР(G1;ЕСЛИ(C2:C12=G2;A2:D12;»»);4;0).
- Для подтверждения действия зажать комбинацию клавиш Ctrl+Shift+Enter. Нажатие на «Enter» не сработает в этом случае, потому что формула должна быть выполнена в массиве (об этом свидетельствуют скобки «{}», в которые взята вся формула).
Поиск по нескольким столбцам
Объем данных расширен, и нужно найти конкретное значение среди нескольких столбцов, просуммировав данные с помощью функции СУММ.
- Кликнуть по ячейке и ввести формулу =СУММ(ВПР(G1;A1:D12;{2;3;4};ЛОЖЬ)). Для третьего аргумента перечисление столбцов происходит в скобках «{}».
- Одновременно зажать клавиши Ctrl+Shift+Enter. В результате формула будет взята в фигурные скобки «{}».
Программа сравнивает данные в таблице и, как только определяет точное совпадение, суммирует их.
Таким же способом можно найти среднее значение с помощью СРЗНАЧ: =СРЗНАЧ(ВПР(G1;A1:D12;{2;3;4};ЛОЖЬ)).
Сравнение двух таблиц
ВПР помогает сопоставить значения в таблицах.
Необходимо сравнить зарплату сотрудников за 2 месяца, для этого:
- В таблице с зарплатой за март добавить еще один столбец.
- Клацнуть по первой ячейке в столбце и написать функцию ВПР со следующими аргументами: =ВПР($A$2:$A$12;ссылка_на_новый_лист!$A$2:$B$12;2;ЛОЖЬ). То есть нужно выделить диапазон с фамилиями менеджеров и сделать ссылки (строки и столбца) неизменными с помощью знака «$», посмотреть его в таблице с новой зарплатой, взять данные из второго столбца новой зарплаты и подставить их в ячейку С2. В результате отобразится первый результат.
- При помощи маркера заполнения протянуть полученное значение вниз.
По желанию теперь можно найти численную и процентную разницу.
Поиск в выпадающем списке
Необходимо настроить функцию так, чтобы при выборе элемента из выпадающего списка отображалось его числовое значение.
Для создания раскрывающегося списка:
- Поставить курсор в ячейку, где он будет располагаться.
- Перейти в раздел книги «Данные» – «Проверка данных».
- В «Типе данных» выбрать «Список», задать диапазон (в нашем случае – фамилии менеджеров).
- Нажать «Ок». Отобразится список.
- В следующую ячейку вписать функцию ВПР. Первый аргумент – ссылка на раскрывающийся список, второй – диапазон таблицы, третий – номер столбца, четвертый – «ЛОЖЬ». В итоге получится следующее: =ВПР(E1;A1:B12;2;ЛОЖЬ). Нажать «Enter».
Меняется фамилия в списке – меняется и зарплата.
Перенос данных
Есть таблица с менеджерами и объемом их продаж. Во второй таблице значится сумма премии за продажу для каждого менеджера. Необходимо перенести данные в левую таблицу, чтобы подсчитать общую выручку (произведение объема продаж и премии за 1 продажу: =ПРОИЗВЕД(C2*D2)).
- Выделить первую ячейку с премией в левой таблице. Написать функцию с аргументами, сделать неизменными значения из второй таблицы, указать в третьем аргументе столбец 2, вместо «ЛОЖЬ» можно вписать 0: =ВПР(B2;$G$2:$H$12;2;0)
- Протянуть формулу вниз, чтобы заполнить все ячейки.
Почему ИНДЕКС/ПОИСКПОЗ лучше, чем ВПР?
Решая, какую формулу использовать для вертикального поиска, большинство гуру Excel обнаруживают, что ИНДЕКС/ПОИСК намного лучше, чем ВПР. Однако многие пользователи Excel по-прежнему используют ВПР, потому что эта функция намного проще. Это потому, что очень немногие люди полностью понимают все преимущества перехода с ВПР на комбинацию ИНДЕКСА и ПОИСКА, и никто не хочет тратить время на изучение более сложной формулы.
Далее я попытаюсь обрисовать основные преимущества использования ПОИСКА и ИНДЕКСА в Excel, и вы решите, следует ли придерживаться ВПР или переключиться на ИНДЕКС/ПОИСК.
4 главных преимущества использования ПОИСКПОЗ/ИНДЕКС в Excel:
1. Ищите справа налево. Как знает любой опытный пользователь Excel, ВПР не может смотреть влево, а это означает, что желаемое значение должно находиться в крайнем левом столбце рассматриваемого диапазона. В случае ПОИСКА/ИНДЕКСА столбец поиска может располагаться слева или справа от диапазона поиска. Пример: Как найти значения, которые находятся слева, покажет эту функцию в действии.
2. Безопасно добавлять или удалять столбцы. Формулы ВПР перестают работать или возвращают неверные значения, если вы удаляете или добавляете столбец в таблицу поиска. Для функции ВПР любые вставленные или удаленные столбцы изменят результат формулы, поскольку синтаксис ВПР требует, чтобы вы указали весь диапазон и конкретный номер столбца, из которого вы хотите извлечь данные.
Например, если у вас есть таблица A1: C10 и вы хотите извлечь данные из столбца B, вам необходимо установить значение 2 для аргумента col_index_num (номер_столбца) функции ВПР, например:
= ВПР («значение поиска»; A1: C10,2)
= ВПР («значение поиска»; A1: C10,2)
Если позже вы вставите новый столбец между столбцами A и B, значение аргумента необходимо изменить с 2 на 3, иначе формула вернет результат только что вставленного столбца.
Используя ПОИСКПОЗ / ИНДЕКС, вы можете удалить или добавить столбцы в рассматриваемый диапазон, не искажая результат, поскольку столбец, содержащий желаемое значение, определяется напрямую. На самом деле это большой плюс, особенно когда приходится работать с большими объемами данных. Вы можете добавлять и удалять столбцы, не беспокоясь о том, чтобы исправить каждую функцию ВПР, которую вы используете.
3. Нет ограничений на размер желаемого значения. При использовании ВПР не забудьте ограничить длину желаемого значения 255 символами, иначе вы рискуете получить #ЗНАЧ! (#ЦЕНИТЬ!). Итак, если таблица содержит длинные строки, единственное жизнеспособное решение — использовать INDEX/SEARCH.
Предположим, вы используете формулу ВПР, подобную этой, которая ищет в ячейках с B5 по D10 значение, указанное в ячейке A2:
= ВПР (A2; B5: D10,3; ЛОЖЬ)
= ВПР (LA2; B5: D10; 3; ЛОЖЬ)
Формула не будет работать, если значение в ячейке A2 превышает 255 символов. Вместо этого вам нужно использовать аналогичную формулу ИНДЕКС/ПОИСК:
= ИНДЕКС (RE5: RE10; СООТВЕТСТВИЕ (ИСТИНА; ИНДЕКС (LA5: SI10 = LA2,0); 0))
= ИНДЕКС (D5: D10; ПОИСК (ИСТИНА; ИНДЕКС (B5: B10 = A2; 0); 0))
4. Более быстрая рабочая скорость. Если вы работаете с небольшими электронными таблицами, разница в производительности Excel, скорее всего, будет незначительной, особенно в последних версиях. Если вы работаете с большими таблицами, содержащими тысячи строк и сотни формул поиска, Excel будет работать намного быстрее, если вы будете использовать ПОИСКПОЗ и ИНДЕКС вместо ВПР. В целом эта замена увеличивает скорость работы Excel на 13%.
Влияние ВПР на производительность Excel особенно заметно, если книга содержит сотни сложных формул массива, таких как ВПР + СУММ. Дело в том, что для проверки каждого значения в массиве требуется отдельный вызов функции ВПР. Следовательно, чем больше значений содержит массив и чем больше формул массива содержит таблица, тем медленнее работает Excel.
С другой стороны, формула с функциями ПОИСК и ИНДЕКС просто выполняет поиск и возвращает результат, выполняя ту же работу намного быстрее.
Структура формулы ВПР в Excel
Первая строка – значение, которое вы хотите найти в базе данных Excel. В примере необходимая информация находится в ячейке B3. Если вы введете ее адрес в первое поле, при помощи выражения можно найти нужные сведения в списке со сведениями о персонале.
Дальше указывается весь диапазон данных (проще говоря, таблица целиком), и нужно учесть, что критерий отбора обязательно должен находиться в первой колонке.
В третьей строке просто нужно указать номер столбца внутри матрицы, из которого с помощью выражения будут извлечены сведения.
Существует два варианта значений для параметра «Порядок сортировки». Введете 0 – тогда результатом вставки будет ошибка #Н/Д, если не сможет найти в базе Экселя результаты по запросу.
Результат отображается только в том случае, если между заданным критерием отбора (Lookup_value) и результатами в крайней первой колонке массива (Table_array) будет обнаружено абсолютное совпадение.
Введете 1 – и формула тоже отобразит результат, если найдет полное совпадение между Lookup_value и содержимым какой-либо строки в первом столбце матрицы (Table_array).
Но если нет соответствия, то будет выбрано значение, приближенное к этому числу. Другими словами, если вы в примере вместо B3 напишете 12, функция вернет ответ «Gwendy», потому что 10 является числом, максимально приближенным к 12. Первая колонка в таблице заранее должна быть отсортирована по возрастанию, если вы вставляете 1 в Range_lookup.
Как перевернуть текст в excel
= Мир MS Excel/Статьи об Excel
- 1
- 2
- 3
| Приёмы работы с книгами, листами, диапазонами, ячейками |
| Приёмы работы с формулами |
| Настройки Excel |
| Инструменты Excel |
| Интеграция Excel с другими приложениями |
| Форматирование |
| Выпадающие списки |
| Примечания |
| Сводные таблицы |
| Гиперссылки |
| Excel и интернет |
| Excel для Windows и Excel для Mac OS |
Инструмент камера возвращает динамический снимок указанного диапазона. Динамический — это значит он изменяется при изменении диапазона, который мы сфотографировали. По умолчанию камера недоступна во всех версиях Excel. Для того что бы ей воспользоваться необходимо сначала вывести её на панель меню (в Excel версий до 2007) или на панель быстрого доступа (в Excel версий после 2003). Как это делается?
Для Excel версий до 2007:
Меню Сервис — пункт Настройка — вкладка Команды — категории Сервис — команды Камера — левой кнопкой мыши перетаскиваем иконку камеры на удобное место в меню.
Для Excel версий после 2003:
Кликаем правой кнопкой мыши по панели быстрого доступа — Настройка панели быстрого доступа — Выбрать команды из Все команды — Камера — Добавить — ОК.
Теперь, когда инструмент камера добавлен в меню/на панель быстрого доступа можно приступить к решению вопроса, озвученного в названии этой статьи: Как перевернуть текст в ячейке?Как я уже писал выше — это невозможно. Формулы и функции не работают с форматированием, ориентацию текста в диалоговом окне Формат ячеек можно изменять только в пределах от 90 до -90 градусов, в VBA нет функций, позволяющих это сделать. Остаётся только перевернуть монитор
Выделяем ячейку, которую надо скопировать. Левой кнопкой мыши кликаем на иконку инструмента камера. Окантовка выделенной ячейки станет активной (как при обычном копировании). Левой кнопкой мыши указываем место на листе, куда необходимо вставить снимок и он автоматически вставляется. Всё, мы получили динамический снимок ячейки. Остаётся только перевернуть его на 180 градусов и готово. На рисунке, в ячейке В2, Вы видите снимок ячейки А1:
Все изменения производимые в ячейке А1 будут отображаться в В2.
ПРИМЕЧАНИЕ: Можно так же «фотографировать» и один диапазон. Одновременно несколько ячеек/диапазонов или несмежные диапазоны инструмент камера не обрабатывает, о чём появится соответствующее сообщение, если Вы попытаетесь применить камеру к ним. С получившимся снимком можно работать как с обычным рисунком, внедрённым на лист, то есть, изменять его размеры, свойства, форму, устанавливать прозрачность, рамки и прочее.
Использование ЕСЛИ с функциями И, ИЛИ и НЕ
Функция ЕСЛИ позволяет выполнять логические сравнения значений и ожидаемых результатов. Она проверяет условие и в зависимости от его истинности возвращает результат.
=ЕСЛИ(это истинно, то сделать это, в противном случае сделать что-то еще)
Но что делать, если необходимо проверить несколько условий, где, допустим, все условия должны иметь значение ИСТИНА или ЛОЖЬ (И), только одно условие должно иметь такое значение (ИЛИ) или вы хотите убедиться, что данные НЕ соответствуют условию? Эти три функции можно использовать самостоятельно, но они намного чаще встречаются в сочетании с функцией ЕСЛИ.
Используйте функцию ЕСЛИ вместе с функциями И, ИЛИ и НЕ, чтобы оценивать несколько условий.
ЕСЛИ(И()): ЕСЛИ(И(лог_выражение1; ; …), значение_если_истина; ))
ЕСЛИ(ИЛИ()): ЕСЛИ(ИЛИ(лог_выражение1; ; …), значение_если_истина; ))
ЕСЛИ(НЕ()): ЕСЛИ(НЕ(лог_выражение1), значение_если_истина; ))
Условие, которое нужно проверить.
Значение, которое должно возвращаться, если лог_выражение имеет значение ИСТИНА.
Значение, которое должно возвращаться, если лог_выражение имеет значение ЛОЖЬ.
Общие сведения об использовании этих функций по отдельности см. в следующих статьях: И, ИЛИ, НЕ. При сочетании с оператором ЕСЛИ они расшифровываются следующим образом:
И: =ЕСЛИ(И(условие; другое условие); значение, если ИСТИНА; значение, если ЛОЖЬ)
ИЛИ: =ЕСЛИ(ИЛИ(условие; другое условие); значение, если ИСТИНА; значение, если ЛОЖЬ)
НЕ: =ЕСЛИ(НЕ(условие); значение, если ИСТИНА; значение, если ЛОЖЬ)
Ниже приведены примеры распространенных случаев использования вложенных операторов ЕСЛИ(И()), ЕСЛИ(ИЛИ()) и ЕСЛИ(НЕ()). Функции И и ИЛИ поддерживают до 255 отдельных условий, но рекомендуется использовать только несколько условий, так как формулы с большой степенью вложенности сложно создавать, тестировать и изменять. У функции НЕ может быть только одно условие.
Ниже приведены формулы с расшифровкой их логики.
=ЕСЛИ(И(A2>0;B2 0;B4 50);ИСТИНА;ЛОЖЬ)
Если A6 (25) НЕ больше 50, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае значение не больше чем 50, поэтому формула возвращает значение ИСТИНА.
Если значение A7 («синий») НЕ равно «красный», возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ.
Обратите внимание, что во всех примерах есть закрывающая скобка после условий. Аргументы ИСТИНА и ЛОЖЬ относятся ко внешнему оператору ЕСЛИ. Кроме того, вы можете использовать текстовые или числовые значения вместо значений ИСТИНА и ЛОЖЬ, которые возвращаются в примерах
Кроме того, вы можете использовать текстовые или числовые значения вместо значений ИСТИНА и ЛОЖЬ, которые возвращаются в примерах.
Вот несколько примеров использования операторов И, ИЛИ и НЕ для оценки дат.
Ниже приведены формулы с расшифровкой их логики.
Если A2 больше B2, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае 12.03.14 больше чем 01.01.14, поэтому формула возвращает значение ИСТИНА.
=ЕСЛИ(И(A3>B2;A3 B2;A4 B2);ИСТИНА;ЛОЖЬ)
Если A5 не больше B2, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае A5 больше B2, поэтому формула возвращает значение ЛОЖЬ.
Почему ИНДЕКС/ПОИСКПОЗ лучше, чем ВПР?
Решая, какую формулу использовать для вертикального поиска, большинство гуру Excel считают, что ИНДЕКС/ПОИСКПОЗ намного лучше, чем ВПР. Однако, многие пользователи Excel по-прежнему прибегают к использованию ВПР, т.к. эта функция гораздо проще. Так происходит, потому что очень немногие люди до конца понимают все преимущества перехода с ВПР на связку ИНДЕКС и ПОИСКПОЗ, а тратить время на изучение более сложной формулы никто не хочет.
Далее я попробую изложить главные преимущества использования ПОИСКПОЗ и ИНДЕКС в Excel, а Вы решите – остаться с ВПР или переключиться на ИНДЕКС/ПОИСКПОЗ.
4 главных преимущества использования ПОИСКПОЗ/ИНДЕКС в Excel:
1. Поиск справа налево. Как известно любому грамотному пользователю Excel, ВПР не может смотреть влево, а это значит, что искомое значение должно обязательно находиться в крайнем левом столбце исследуемого диапазона. В случае с ПОИСКПОЗ/ИНДЕКС, столбец поиска может быть, как в левой, так и в правой части диапазона поиска. Пример: Как находить значения, которые находятся слева покажет эту возможность в действии.
2. Безопасное добавление или удаление столбцов. Формулы с функцией ВПР перестают работать или возвращают ошибочные значения, если удалить или добавить столбец в таблицу поиска. Для функции ВПР любой вставленный или удалённый столбец изменит результат формулы, поскольку синтаксис ВПР требует указывать весь диапазон и конкретный номер столбца, из которого нужно извлечь данные.
Например, если у Вас есть таблица A1:C10, и требуется извлечь данные из столбца B, то нужно задать значение 2 для аргумента col_index_num (номер_столбца) функции ВПР, вот так:
=VLOOKUP(“lookup value”,A1:C10,2)=ВПР(“lookup value”;A1:C10;2)
Если позднее Вы вставите новый столбец между столбцами A и B, то значение аргумента придется изменить с 2 на 3, иначе формула возвратит результат из только что вставленного столбца.
Используя ПОИСКПОЗ/ИНДЕКС, Вы можете удалять или добавлять столбцы к исследуемому диапазону, не искажая результат, так как определен непосредственно столбец, содержащий нужное значение. Действительно, это большое преимущество, особенно когда работать приходится с большими объёмами данных. Вы можете добавлять и удалять столбцы, не беспокоясь о том, что нужно будет исправлять каждую используемую функцию ВПР.
3. Нет ограничения на размер искомого значения. Используя ВПР, помните об ограничении на длину искомого значения в 255 символов, иначе рискуете получить ошибку #VALUE! (#ЗНАЧ!). Итак, если таблица содержит длинные строки, единственное действующее решение – это использовать ИНДЕКС/ПОИСКПОЗ.
Предположим, Вы используете вот такую формулу с ВПР, которая ищет в ячейках от B5 до D10 значение, указанное в ячейке A2:
=VLOOKUP(A2,B5:D10,3,FALSE)=ВПР(A2;B5:D10;3;ЛОЖЬ)
Формула не будет работать, если значение в ячейке A2 длиннее 255 символов. Вместо неё Вам нужно использовать аналогичную формулу ИНДЕКС/ПОИСКПОЗ:
=INDEX(D5:D10,MATCH(TRUE,INDEX(B5:B10=A2,0),0))=ИНДЕКС(D5:D10;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B5:B10=A2;0);0))
4. Более высокая скорость работы. Если Вы работаете с небольшими таблицами, то разница в быстродействии Excel будет, скорее всего, не заметная, особенно в последних версиях. Если же Вы работаете с большими таблицами, которые содержат тысячи строк и сотни формул поиска, Excel будет работать значительно быстрее, при использовании ПОИСКПОЗ и ИНДЕКС вместо ВПР. В целом, такая замена увеличивает скорость работы Excel на 13%.
Влияние ВПР на производительность Excel особенно заметно, если рабочая книга содержит сотни сложных формул массива, таких как ВПР+СУММ. Дело в том, что проверка каждого значения в массиве требует отдельного вызова функции ВПР. Поэтому, чем больше значений содержит массив и чем больше формул массива содержит Ваша таблица, тем медленнее работает Excel.
С другой стороны, формула с функциями ПОИСКПОЗ и ИНДЕКС просто совершает поиск и возвращает результат, выполняя аналогичную работу заметно быстрее.
Поиск характеристики списка
Под характеристикой списка здесь понимается: минимальное/ максимальное значение списка, среднее, первое/ последнее слово по алфавиту. Список может содержать повторяющиеся значения. Хотя условиям поиска могут соответствовать несколько значений (например, два числа равны максимальному), но выводится только одно (максимальное).
Строго говоря, этот тип поиска не относится к «поиску значений в списке», т.к. пользователя не интересует какое-то конкретное значение из списка, а ему нужно найти определенную харастику списка , например, среднее значение, которое в общем случае может и не совпадать ни с одним значением списка. Типичная задача : поиск максимального значения по условию . К этому типу задач можно отнести сложение и подсчет с условиями (например, сумма всех положительных значений списка или количество значений в списке могут считаться такими же важными характеристиками списка как и минимальное/ максимальное значение).
Наиболее часто используемые функции для этого типа задач: МАКС() , МИН() , НАИБОЛЬШИЙ() , СРЗНАЧ() , СУММЕСЛИ() , СЧЁТ() и др.
Списки могут содержать значения любых типов : числа, текстовые значения и даты.
Функция Excel ЕСЛИМН
Функция Эксель ЕСЛИ в целом хорошо справляется со своими задачами. Но вариант, когда нужно записывать длинную цепочку условий не очень приятный, т.к., во-первых, написать с первого раза не всегда получается (то условие укажешь неверно, то скобку не закроешь); во-вторых, разобраться при необходимости в такой формуле может быть непросто, особенно, когда условий много, а сами расчеты сложные.
В MS Excel 2016 появилась функция ЕСЛИМН, ради которой и написана вся эта статья. Это та же ЕСЛИ, только заточенная специально для проверки множества условий. Теперь не нужно сто раз писать ЕСЛИ и считать открытые скобки. Достаточно перечислить условия и в конце закрыть одну скобку.
Работает следующим образом. Возьмем пример выше и воспользуемся новой формулой Excel ЕСЛИМН.
Как видно, запись формулы выглядит гораздо проще и понятнее.
Стоит обратить внимание на следующее. Условия по-прежнему перечисляем в правильном порядке, чтобы не произошло ненужного перекрытия диапазонов. Последнее альтернативное условие, в отличие от обычной ЕСЛИ, также должно быть обязательно указано
В ЕСЛИ задается только альтернативное значение, которое наступает, если не выполняется ни одно из перечисленных условий. Здесь же нужно указать само условие, которое в нашем случае было бы B2>=1. Однако этого можно избежать, если в поле с условием написать ИСТИНА, указывая тем самым, что, если не выполняются ранее перечисленные условия, наступает ИСТИНА и возвращается последнее альтернативное значение
Последнее альтернативное условие, в отличие от обычной ЕСЛИ, также должно быть обязательно указано. В ЕСЛИ задается только альтернативное значение, которое наступает, если не выполняется ни одно из перечисленных условий. Здесь же нужно указать само условие, которое в нашем случае было бы B2>=1. Однако этого можно избежать, если в поле с условием написать ИСТИНА, указывая тем самым, что, если не выполняются ранее перечисленные условия, наступает ИСТИНА и возвращается последнее альтернативное значение.
Теперь вы знаете, как пользоваться функцией ЕСЛИ в Excel, а также ее более современным вариантом для множества условий ЕСЛИМН.