B и C.
В старых версиях Excel не забудьте ввести такую формулу с помощью сочетания клавиш Ctrl + Shift + Enter (это формула массива, и до Excel 2019 включительно такие нужно вводить явным образом).
Если мы ожидаем, что в исходной таблице могут появляться новые строки, можно сделать ее таблицей и ссылаться на столбцы по именам.
Если вы ищете числа, как в данном случае (ставки роялти — числовые значения), можно обойтись вовсе без ВПР или ИНДЕКС + ПОИСКПОЗ.
Ведь есть функция СУММЕСЛИМН / SUMIFS, суммирующая данные по нескольким условиям. Так как мы ищем число, то можно суммировать по условиям — в качестве результата функция будет возвращать сумму одного-единственного (искомого) числа, что нам и нужно. Конечно, если комбинация условий встречается в исходной таблице только один раз.
Плюс в том, что с этой функцией легко работать и при большом количестве условий.
Но и минус есть: если мы ищем текст, а не числа, то подойдут только предыдущие варианты, а не СУММЕСЛИМН.
Файл с примером: ВПР с разных листов.xlsx
Если вам нужно «подтягивать» данные с помощью ВПР / VLOOKUP с разных листов (например, на каждый город/месяц/склад у вас отдельный лист с данными), можно собрать ссылку с помощью функции ДВССЫЛ / INDIRECT.
Обычная ссылка на другой лист выглядит так:
='Название_листа'!A: B
Нам нужно подставлять внутри апострофов названия разных листов.
Сначала берем апостроф (в кавычках), потом к нему добавляем название листа, справа еще один апостроф, восклицательный знак и диапазон:
="'" & ячейка с названием листа & "'!диапазон"
Чтобы превратить полученную текстовую строку в ссылку, используем функцию ДВССЫЛ / INDIRECT — она ровно для этого и используется:
=ДВССЫЛ ("'" & ячейка с названием листа & "'!диапазон")
И отправляем это внутрь ВПР'а как второй аргумент:
=ВПР(значение для поиска; ДВССЫЛ("'" & ячейка с названием листа & "'!диапазон"); номер столбца; 0)
Генерация случайных чисел и дат
В Excel и в Google Таблицах есть функции для генерации случайных чисел: СЛЧИС / RAND и СЛУЧМЕЖДУ / RANDBETWEEN (последняя появилась в Excel 2007).
СЛУЧМЕЖДУ генерирует целые числа — концы интервала задаются в двух аргументах функции. В следующем примере генерируем числа от 1000 до 6000.
СЛЧИС генерирует случайное число от 0 до 1. Аргументов у нее нет — это всегда число в этом диапазоне. В следующем примере к ним применен процентный формат.
Если мы вспомним, что дата в Excel — это целое число, то поймем, что можно сгенерировать и случайную дату.
Ну а раз время — это та часть (доля) дня, что уже прошла, то есть число от нуля до единицы, то случайное время можно генерировать с помощью СЛЧИС.
В обоих случаях нужно поменять формат ячеек, чтобы результаты генерации (числа) выглядели как дата и время.
Файл с примером: Жеребьевка (ИНДЕКС + СЛУЧМЕЖДУ).xlsx
А что, если вы хотите выбрать случайное значение из списка? Выбрать победителя розыгрыша или провести жеребьевку команд?
Вспоминаем, что есть функция ИНДЕКС / INDEX, которая возвращает значение из массива по номеру строки (или номерам строки и столбца, если массив двумерный).
Если этот самый номер сделать случайным (то есть вычислять с помощью функции СЛУЧМЕЖДУ), то это и будет выбором случайного элемента из списка.
В Excel 2021 и Microsoft 365 также появилась функция СЛУЧМАССИВ / RANDARRAY, она позволяет генерировать сразу целый массив.
Обратите внимание, что эти функции входят в число так называемых волатильных. Это значит, что их результат пересчитывается при каждом изменении в книге (если в параметрах Excel не отключен автоматический пересчет). Чтобы случайные значения не менялись, нужно сохранить формулы как значения.
Какие еще функции в Excel являются волатильными:
ТДАТА / NOW;
СЕГОДНЯ / TODAY;
ДВССЫЛ / INDIRECT;
СМЕЩ / OFFSET.
Динамические массивы
Файл с примерами: Динамические массивы.xlsx
В Excel в рамках пакета Microsoft 365 и версии 2021 произошли существенные и принципиальные изменения в работе с формулами массивов: теперь одна формула/функция может выводить результат не только в одной ячейке, но и сразу в нескольких.
Обычные формулы мы вводим в одну ячейку и результат получаем тоже в одной ячейке. Каждая формула независима в том смысле, что мы можем удалить или изменить отдельную формулу в диапазоне.
Теперь (в Microsoft 365, в Excel 2021) достаточно просто ввести в одну ячейку формулу, в которой аргументами будут диапазоны, а не ячейки и которая на выходе будет возвращать целый диапазон, и просто нажать Enter (или иным образом завершить ввод формулы).
Синим контуром выделены размеры диапазона, который возвращает формула. Это цельная неделимая сущность: нельзя выделить какую-либо ячейку, кроме первой (левой верхней, в данном случае D2), и удалить фрагмент этого возвращаемого массива. Можно удалить только всю формулу целиком.
Если выделить одну из ячеек, возвращаемых формулой, мы увидим саму формулу, но она будет выделена в строке формул серым, редактировать ее нельзя, это можно делать только в первой ячейке.
Строго говоря, формулы массива имелись и ранее во всех версиях Excel, но они были менее удобными:
— требовалось заранее выделить диапазон, в котором будет вводиться формула массива, то есть знать заранее размерность результата;
— нажать после Ctrl + Shift + Enter.
С новыми формулами массива появилась и новая ошибка — #ПЕРЕНОС!
Если на пути вывода данных будут другие формулы или значения, то формула будет возвращать ошибку, так как она не может «перезаписать» ваши данные, удалить их, чтобы вывести свой результат. Формула может лишь просигнализировать о том, что пустых ячеек внизу и справа недостаточно, чтобы вывести результат. Эта ошибка называется #ПЕРЕНОС! (#SPILL!).
В Google Таблицах в таких ситуациях отображается ошибка #ССЫЛ! (#REF!).
Появились и новые функции. Они как раз выводят целый массив значений, который может динамически изменяться (если изменились исходные данные, обрабатываемые функцией).
Допустим, если условию, заданному в функции ФИЛЬТР / FILTER, начнут соответствовать не 10 строк, а 11, то она выведет 11 строк. В этом и есть отличие новых динамических массивов: формулы теперь могут возвращать массив разного размера, размерность массива не задается заранее. Благодаря этому появились новые функции, которые мы рассматриваем.
Эти функции (кроме СОРТПО / SORTBY) ранее были доступны только в Google Таблицах (там у этих функций названия на английском при любом языке формул), но теперь есть и в Excel в последнем пакете Microsoft Office.
Функция УНИК / UNIQUE
Функция УНИК / UNIQUE выводит массив уникальных значений (если аргумент — один столбец) или строк (если аргумент — несколько столбцов). Единственный обязательный аргумент — диапазон.
В следующем примере функция УНИК выводит уникальные имена из списка (каждое значение — только по одному разу, в отличие от исходного диапазона).
Если в исходном диапазоне станет меньше значений, функция будет возвращать массив другого размера.
Если аргументом УНИК будет диапазон из нескольких столбцов, то функция будет возвращать уникальные строки.
Функция СОРТ / SORT
Функция СОРТ / SORT сортирует диапазон — по столбцам или строкам:
=СОРТ (массив; [индекс_сортировки]; [порядок_сортировки]; [по_столбцу])
• массив (array) — диапазон данных, который будет возвращаться функцией в отсортированном виде;
• индекс_сортировки (sort_index) — номер столбца (строки), по которому идет сортировка;
• порядок_сортировки (sort_order) — порядок сортировки (1 = по возрастанию, −1 = по убыванию);
• по_столбцу (by_col) — сортировка по столбцам или строкам; если ИСТИНА / TRUE, то по столбцам (то есть будет меняться порядок столбцов); если ЛОЖЬ / FALSE, то по строкам (будет меняться порядок строк).
Применим функцию СОРТ к списку имен, пропустив все необязательные аргументы. В таком случае по умолчанию функция будет сортировать диапазон по возрастанию (по алфавиту для текстовых значений).
Можно объединить ее с функцией УНИК, чтобы получить отсортированный список уникальных значений (без дубликатов).
Если в диапазоне несколько столбцов, то понадобятся и другие аргументы функции СОРТ, чтобы указать, по какому столбцу и в каком порядке сортируем. Второй аргумент — порядковый номер столбца, а третий — порядок сортировки (1 = по возрастанию, −1 = по убыванию).
Функция СОРТПО / SORTBY
Функция СОРТПО / SORTBY возвращает диапазон, отсортированный по другому столбцу/строке (столбцам/строкам):
=СОРТПО (массив; [ключевой_массив1]; [порядок_сортировки1];…)
• массив (array) — диапазон данных, который будет возвращаться функцией в отсортированном виде;
• ключевой_массив1 (by_array1) — номер столбца (строки), по которому идет сортировка;
• порядок_сортировки1 (sort_order1) — порядок сортировки (1 = по возрастанию, −1 = по убыванию).
В функции СОРТ мы обязаны сортировать по одному из столбцов того диапазона, что возвращаем. Нельзя вывести список самых продаваемых книг (то есть список, отсортированный по количеству проданных экземпляров) без столбца с самими продажами — того, по которому идет сортировка.