Резюме
- Оператор разлитого диапазона Excel (#) позволяет формулам автоматически подстраиваться под изменения размера разлитого диапазона.
- Чтобы воспользоваться этим удобным инструментом, вам необходимо использовать Excel для Microsoft 365 на Windows или Mac.
- Такие известные функции, как UNIQUE, COUNTIF и SORTBY, можно использовать с оператором разлитого диапазона для создания динамических сортируемых списков.
Использование символа решетки (#) — также известного как оператор пролитого диапазона — в формуле Excel — это способ указать программе, что нужно учитывать все результаты в пролитом диапазоне. В результате, даже если пролитый диапазон увеличивается или уменьшается, формула, содержащая #, автоматически отразит это изменение.
Вы можете воспользоваться оператором расширения диапазона Excel только в том случае, если используете Excel для Microsoft 365 на Windows или Mac.
Представьте, что вы управляете приютом для животных, и ваша электронная таблица содержит отформатированную таблицу Excel под названием «Допущенные_животные», в которой указаны животные, находящиеся в настоящее время под вашей опекой.
Чтобы вы могли максимально эффективно использовать пространство, имеющееся в вашем заповеднике, вам необходимо знать, сколько у вас особей каждого вида животных и сколько всего у вас разных видов животных.
Поскольку функции, которые вы собираетесь использовать, создают разрозненные массивы, а разрозненные массивы не работают в форматированных таблицах Excel, вам необходимо ввести формулы в те области вашей электронной таблицы, которые отформатирован в виде таблицы Excel.
Чтобы узнать, сколько особей каждого вида животных в настоящее время находится в вашем заповеднике, в ячейках D1 и E1 (ячейки выше, где будут располагаться ваши первые разлитые массивы) введите заголовки столбцов Животное и Количество, Соответственно.
Теперь в ячейке D2 введите:
=UNIQUE(Допущенные_Животные[Животное])
в котором УНИКАЛЬНЫЙ это функция Excel, которая выводит список уникальных элементов в диапазоне, Животные_Допущены — это имя таблицы, в которой находятся исходные данные, и [Животное] — имя, присвоенное третьему столбцу этой таблицы.
Когда вы нажимаете Enter, вы получаете выброшенный массив, который перечисляет каждый уникальный элемент в столбце Animal. Вы знаете, что это выброшенный массив, потому что синяя линия окружает результат всякий раз, когда вы выбираете одну из затронутых ячеек.
Результат функции UNIQUE сортируется в соответствии с порядком, в котором каждый элемент впервые появился в исходных данных.
Теперь пришло время заставить Excel подсчитать, сколько особей каждого животного содержится в вашей исходной таблице, и вот здесь-то и используется хэш Знак значительно облегчает жизнь.
Для этого вам нужно будет использовать функцию СЧЁТЕСЛИ. Однако, поскольку вы хотите, чтобы Excel подсчитал ВСЕ животных, возвращаемых функцией UNIQUE в столбце D, — даже если некоторые животные будут добавлены или удалены из этого списка позже, — необходимо добавить знак решетки после ссылки на критерий.
Итак, в ячейке E2 введите:
=СЧЁТЕСЛИ(Допущено_животных[Животное],D2#)
в котором COUNTIF это функция, которая подсчитывает количество вхождений, Животные_Допущены[Животное] — это диапазон, содержащий каждое животное в исходной таблице, и D2 # сообщает Excel, что критерием для функции СЧЁТЕСЛИ является разрозненный массив, начинающийся с ячейки D2, и, таким образом, может измениться его размер.
Вместо того чтобы вводить формулу вручную, если вы используете мышь для выбора ячеек для каждого аргумента, формула автоматически примет имена столбцов (также известные как структурированные ссылки) и, где применимо, добавит знак решетки.
Теперь представим, что в ваш заповедник принесли ежа, и этот еж будет единственным, кто в данный момент находится под вашей опекой.
Чтобы добавить дополнительную строку в отформатированную таблицу, щелкните и перетащите маркер в правом нижнем углу вниз.
Поскольку вы сослались на отформатированный заголовок таблицы в столбце D и использовали оператор разлитого диапазона в формуле СЧЁТЕСЛИ в столбце E, ёж автоматически добавляется в список в столбце D, а количество ежей в столбце E правильно отображается как «1».
Теперь вам нужно создать список, сортирующий животных по количеству.
После ввода указанных выше заголовков в ячейки F1 и G1, в ячейке F2 введите:
=СОРТПО(D2#:E2#,E2#,-1)
в котором СОРТИРОВАТЬ ПО это функция Excel, которая сортирует диапазон в соответствии со значениями в другом диапазоне или массиве, Д2#:Е2# сообщает Excel, что массив, занимающий столбцы D и E, содержит два столбца разрозненных массивов, начинающихся с ячеек D2 и E2, Е2# это пролитый массив для сортировки, и -1 сообщает Excel о необходимости сортировки данных в порядке убывания.
Результат функции SORTBY сначала сортируется в соответствии с порядком, который вы указали в формуле SORTBY. Однако, если какие-либо переменные имеют одинаковые значения после этой начальной сортировки, они затем будут отсортированы в соответствии с порядком, в котором каждый элемент впервые появился в исходных данных.
Поскольку вы использовали эти знаки решетки в формуле, вы можете быть уверены, что ваши списки будут динамически расширяться и сокращаться в соответствии с данными в исходной таблице.
Наконец, вам также необходимо провести базовый подсчет общего количества видов животных в вашем заповеднике.
Для этого после ввода соответствующего заголовка в ячейку H1, в ячейке H2 введите
=СЧЁТA(D2#)
в котором COUNTA — это формула Excel, которая подсчитывает количество ячеек в диапазоне, которые не являются пустыми, а D2# сообщает Excel, что диапазон — это пролитый массив. Другими словами, мы говорим Excel подсчитать количество животных, которое функция UNIQUE вернула в пролитом массиве, начиная с ячейки D2, и что это число может измениться.
В качестве последнего теста предположим, что черепаха Лондон выпущена. После удаления Лондона из исходных данных, поскольку он был единственной черепахой в заповеднике, ячейка H2 правильно сообщает вам, что теперь под вашей опекой находится только 18 уникальных животных, а «Черепаха» больше не появляется в других созданных вами списках.
Операторы разлитого диапазона (#): Окончательные указатели
Прежде чем начать, следует отметить несколько дополнительных моментов.
Во-первых, помимо ссылки на пролитый диапазон на том же листе, вы также можете ссылаться на пролитый диапазон на другом листе. Например, чтобы сослаться на пролитый массив, начинающийся в ячейке A2 на листе 2, вы можете ввести:
=Лист2!A1#
Во-вторых, вы можете ссылаться на разлитый диапазон в другой книге. Хотя Microsoft утверждает, что ссылаемая книга должна быть открыта, чтобы не возвращать ошибку #REF!, когда я тестировал ее с закрытой книгой, ссылка продолжала работать. Просто убедитесь, что вы сохранили обе книги и подключили их к одной учетной записи OneDrive, чтобы не потерять данные.
Наконец, чтобы избежать возврата формулами ошибки #SPILL!, убедитесь, что все ячейки, в которых массив должен быть переполнен, очищены от других данных и не объединены.
Теперь, независимо от того, хотите ли вы просто расширить свои знания формул и функций Excel или претендуете на должность, требующую знаний Excel, вы можете с уверенностью сказать, что знаете, что делает знак «решетка» в формулах Microsoft Excel!
