Кратко
Символ решётки (#), также известный как оператор разлившегося диапазона, позволяет Excel автоматически охватывать все значения в динамическом массиве. С помощью # ваша формула всегда будет показывать актуальные данные — даже если диапазон сам меняется: строки добавляются или удаляются.
Это работает только в Excel для Microsoft 365 на Windows и Mac.
Например, представьте, что я руковожу приютом для животных и веду в Excel список с именем Animals_Admitted — здесь я записываю всех обитателей нашего приюта.
Чтобы понимать, на сколько мест рассчитан приют и кто живёт у меня сейчас, мне нужно выяснить — какие животные у меня есть и сколько всего разных видов.
Внимание: функции с разлившимися массивами не работают внутри официально отформатированных таблиц Excel, так что все формулы придётся писать вне таких таблиц — просто в обычных ячейках листа.
Сначала в ячейках D1 и E1 я пишу названия столбцов Животное и Количество — здесь и будут результаты по видам и их числу.
Дальше в D2 ввожу формулу:
Здесь UNIQUE — это функция, которая выводит список уникальных значений из указанного диапазона. Animals_Admitted — имя вашей таблицы, а [Animal] — заголовок столбца с типами животных.
Как вывести уникальные значения и быстро отсортировать их в Excel
Одной формулой получайте список уникальных имён, дат или других данных в Excel!
Когда нажимаете Enter, получаете “разлившийся” массив — список всех уникальных животных из столбца Animal. Excel автоматически обводит такие данные синим прямоугольником при выборе ячейки, чтобы было видно, что массив “разлился” вниз.
При этом порядок будет таким же, как в исходном списке — животные выводятся так, как были впервые добавлены.
Всё, что нужно знать о разлившихся массивах в Excel
Почему массивы “разливаются” и как работать с ними на практике — всё просто!
Теперь хочу узнать, сколько у меня каждого вида животных. И тут вступает в игру магия #!
Для этого мне нужна функция COUNTIF. Я хочу, чтобы Excel считал количество по каждому виду, полученному через функцию UNIQUE в столбце D. При этом если список видов изменится, Excel сам всё подхватит: достаточно после критерия поставить решётку.
В E2 пишу формулу:
Здесь COUNTIF определяет, сколько раз встречается каждое животное, Animals_Admitted[Animal] — диапазон с исходными данными, а D2# — наш динамический список видов, который начинается c D2. Благодаря символу # Excel расширяет диапазон формулы новым значением сам!
Если вы выделяете диапазоны мышкой, Excel сам добавит # там, где нужно.
Допустим, я принимаю нового жильца — ежика, он пока в приюте один.
Чтобы добавить строку, перетащите маркер в правом нижнем углу таблицы вниз.
Так как наши формулы ссылаются на столбцы таблицы и используют оператор разлившегося диапазона, ежик мгновенно появится в списке видов, а в столбце "Количество" сразу добавится "1".
Теперь хочется отсортировать животных по убыванию количества.
Ввожу заголовки в F1 и G1. В ячейку F2 пишу:
Функция SORTBY сортирует диапазон по значениям из другого массива. D2#:E2# — объединённый массив “вид-количество”; E2# — диапазон сортировки (по количеству), а -1 говорит Excel сортировать по убыванию.
В результате данные сортируются по количеству, а если количество одинаковое — учитывается, кто появился первым.
Благодаря значку "#", мои списки всегда автоматически уходят под любые изменения в исходных данных.
Осталось узнать, сколько теперь у меня видов животных.
В H1 пишу заголовок, в H2 — формулу:
COUNTA считает, сколько непустых ячеек вернула функция UNIQUE — то есть сколько уникальных видов животных сейчас в разлившемся массиве из D2#. Если виды добавляются или убираются, итоговое число меняется автоматически!
Как раз и навсегда научиться считать ячейки в Excel
Хотите посчитать текст, цифры или просто пустые ячейки? Всё про подсчёт в Excel — на одной странице.
И наконец, если вдруг я отпускаю черепаху по имени London на волю и убираю её из данных, в H2 сразу станет на один вид меньше, а черепаха исчезнет из всех связанных списков.
Оператор разлившегося диапазона (#): к чему быть готовым
Перед тем как пользоваться этим оператором самостоятельно, вот пара практических советов.
Подпишитесь на свежие советы по Excel для настоящих профи
Во-первых, на разлившийся диапазон можно ссылаться и с другого листа — например, так:
И даже из другого файла. Microsoft утверждает, что если нужный файл будет закрыт, появится ошибка #REF! Но на практике иногда всё работает — главное, чтобы оба документа лежали в одной учётной записи OneDrive и связь между ними не пропадала.
Чтобы не столкнуться с ошибкой #SPILL! при расчётах, убедитесь, что весь диапазон для массива свободен, и ячейки не объединены.
Теперь вы точно знаете, для чего нужен # (решётка) в формулах Microsoft Excel — будь вы увлечённым самоучкой или кандидатом на карьеру мечты!
Если вам понравилась эта статья, подпишитесь, чтобы не пропустить еще много полезных статей!
Премиум подписка - это доступ к эксклюзивным материалам, чтение канала без рекламы, возможность предлагать темы для статей и даже заказывать индивидуальные обзоры/исследования по своим запросам!Подробнее о том, какие преимущества вы получите с премиум подпиской, можно узнать здесь
Также подписывайтесь на нас в:
- Telegram: https://t.me/gergenshin
- Youtube: https://www.youtube.com/@gergenshin
- Яндекс Дзен: https://dzen.ru/gergen
- Официальный сайт: https://www-genshin.ru