Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах)

СЛУЧМЕЖДУ генерирует целые числа — концы интервала задаются в двух аргументах функции. В следующем примере генерируем числа от 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 строк. В этом и есть отличие новых динамических массивов: формулы теперь могут возвращать массив разного размера, размерность массива не задается заранее. Благодаря этому появились новые функции, которые мы рассматриваем.