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