Советы по Excel: обновление 13 новых функций Excel

Пользователи, у которых есть версия Excel по подписке, регулярно получают обновления с новыми функциями и возможностями. Политика обновлений организации может влиять на частоту обновлений для бизнес-пользователей.

Несколько новых функций Excel подробно описаны в наших недавних статьях с советами по Excel. Ссылки предоставлены.

1. Динамические матрицы

Динамические матрицы — это новый способ вычисления формул, являющийся частью обновления Excel.

Этот новый метод вычисления формулы позволяет пользователям работать с диапазоном ячеек так же, как они привыкли работать с отдельной ячейкой. Это означает, что для заполнения диапазона можно создать только одну формулу. Этот диапазон называется диапазоном разлива.

Формула отображается в верхней левой ячейке диапазона разливов. Он может простираться вниз, вправо или в обоих направлениях, создавая двумерную полосу разлива. Чтобы указать диапазон сброса, добавьте символ # в конец ссылки на ячейку в верхнем левом углу, например A1#.

Динамические массивы обсуждались в трех предыдущих статьях, в которых содержался обзор, обсуждались уникальные функции и функции фильтрации, а также объяснялось, как их можно использовать для сортировки списков.

2. Функция ExceloCup

Функция XLOOKUP заменяет функции VLOOKUP и HLOOKUP. В некоторой степени она заменяет группу ИНДЕКС ПОИСКПОЗ, которая использовалась в прошлом как более гибкий вариант поиска. XLOOKUP теперь моя любимая функция поиска.

Нажмите здесь, чтобы прочитать мою подробную статью о функции XLOOKUP.

3. Отпустите работу

Функция LET позволяет пользователям создавать переменные в формулах. Это может сократить длинные и сложные формулы и сделать их более эффективными.

Функция LET также хорошо работает с новой функцией LAMBDA, показанной ниже.

Нажмите здесь, чтобы прочитать мою подробную статью о функции LET.

4. Текстовые функции

Появились три новые текстовые функции, которые упрощают извлечение и обработку текста.

Нажмите здесь, чтобы прочитать мою подробную статью об этих трех новых профессиях.

5. лямбда-функция

Функция LAMBDA позволяет пользователям создавать собственные функции в Excel без макросов VBA. Недавно были добавлены дополнительные функции, поэтому функция LAMBDA может включать дополнительные аргументы при создании пользовательских функций.

Функция LAMBDA также требуется для некоторых других новых функций как часть их функциональности.

Нажмите здесь, чтобы прочитать мою подробную статью о функции LAMBDA.

6. Изолированная функция

Функция ISOMITTED работает с функцией LAMBDA. Он указывает, опущены ли необязательные аргументы пользовательской функции.

Это позволяет пользователям устанавливать значения по умолчанию для необязательных аргументов, как и встроенные функции Excel.

7. Функции BYROW/BYCOL

При первом выпуске возникла проблема с динамическими массивами, которые могли растягиваться вверх и вниз. Трудно было указать строки и столбцы диапазона разлива 2D.

Функции BYROW и BYCOL устранили эту проблему. Оба требуют функциональности LAMBDA.

В этой статье описывается, как использовать обе функции.

8. Функция фото

Новая функция IMAGE позволяет формату отображать изображение путем извлечения изображения из веб-адреса. Для этой функции требуется адрес с префиксом HTTPS, поэтому пользователи не могут использовать изображения на личных жестких дисках или локальных серверах.

Организации могут загружать фотографии продуктов на свой веб-сайт. Бизнес-пользователи могут затем использовать функцию ИЗОБРАЖЕНИЯ для отображения этих изображений на основе номера партии.

Видео, сопровождающее эту статью, демонстрирует эту функциональность с помощью изображений, которые я разместил на своем веб-сайте.

9. Функции VSTACK/HSTACK

Функции VSTACK и HSTACK предоставляют несколько решений распространенных проблем, возникающих при попытке манипулирования данными и создания отчетов.

VSTACK объединяет строки — V означает вертикаль. Это похоже на добавление данных, когда одна таблица добавляется в конец другой — см. Рисунок 1 Например.

Два отдельных списка состояний в столбцах A и B объединяются в один список с помощью функции VSTACK в ячейке D1. Функция VSTACK создает диапазон вертикального разлива.

Функция HSTACK объединяет столбцы данных и создает таблицу с несколькими столбцами. Буква H означает горизонтальность. в фигура 2список штатов находится в столбце A, а список их столиц — в столбце C. Функция HSTACK в ячейке E1 объединяет два отдельных списка в таблицу из двух столбцов.

Функция HSTACK создает 2D-диапазон разлива.

10. Функции взятия и удаления

Функции TAKE и DROP позволяют извлекать записи из диапазонов сброса. Функция TAKE извлекает первое или последнее количество записей. в Рисунок 3Продажи по клиентам перечислены в алфавитном порядке в столбцах A и B. Функция СОРТИРОВКА в ячейке D2 создает диапазон распределения, в котором перечислены все клиенты в порядке убывания на основе их стоимости продаж.

Мы хотим вернуть нескольких клиентов с самыми высокими продажами на основе значения, введенного в желтую ячейку K1. Функция TAKE в ячейке G2 возвращает первые пять строк отсортированного диапазона сброса. Примечание. D2# обозначает диапазон разлива, созданный функцией СОРТИРОВКА в ячейке D2.

Если во втором аргументе функции TAKE используется положительное число, она возвращает первые записи. Если используется отрицательное число, возвращается последние записи. быть увиденным Рисунок 4, где -4 введено в ячейку K1. Это возвращает последние четыре строки отсортированного диапазона сброса.

Функция DROP определяет количество записей, которые необходимо удалить. Список клиентов из предыдущего примера содержит 105 клиентов. в Рисунок 5функция DROP в ячейке G2 удаляет 95 клиентов сверху, оставляя 10 снизу.

в Рисунок 6использование -95 в ячейке K1 удаляет нижние 95, оставляя только 10 самых высоких продаж.

TAKE и DROP могут работать как со столбцами, так и со строками.

11. ФУНКЦИИ ВЫБОРА И ВЫБОРА

Динамические массивы могут создавать двумерный диапазон разливов. Затем вам может потребоваться извлечь определенные строки или столбцы из этого диапазона сброса. Функции CHOOSEROWS и CHOOSECOLS упрощают этот процесс.

в Рисунок 7, ячейка B2 создает двумерный диапазон разливов, в котором суммируются данные о продажах по продуктам и месяцам из электронной таблицы на отдельном листе. Ячейка A7 содержит раскрывающийся список, который позволяет пользователю выбрать продукт для отображения на диаграмме.

Функция ВЫБОР в ячейке B7 извлекает данные из диапазона утечки #B2. Число во втором аргументе CHOOSEROWS используется для указания номера строки, которую нужно извлечь из диапазона сброса. Функция ПОИСКПОЗ предоставляет это число на основе продукта, указанного в ячейке A7.

CHOOSEROWS может извлекать более одной строки, что показано в видеоролике, сопровождающем эту статью. Функция CHOOSECOLS работает аналогично, но извлекает столбцы из диапазона сброса.

12. Другие новые функции

Есть и другие новые функции более сложного назначения, которые также относятся к динамическим массивам. Вот они:

• Торо/Токуль
• Обложки/Конверты
• карта
• снижаться
• делать обзор
• делать

13. Новые функции DAX

Функция PowerPivot в Excel позволяет использовать модель данных Excel для создания отчетов на основе реляционных баз данных. В рамках этой функциональности функции DAX можно использовать для создания мер и вычисляемых столбцов.

Функции DAX похожи на функции на основе ячеек в Excel, но они более мощные и гибкие, чем функции Excel. Это также сложно освоить.

Функциональность PowerPivot DAX для Excel остается неизменной на протяжении многих лет. Ситуация изменилась в конце 2022 года, когда было запущено 92 новых задания DAX.

Эти функции DAX аналогичны функциям DAX, доступным в Power BI.

Обратите внимание, что Power BI часто обновляется и имеет больше функций DAX, чем Excel. Знания, полученные при использовании функций DAX в Excel, можно напрямую применить в Power BI.

Нажмите здесь, чтобы просмотреть мою вводную статью, объясняющую и демонстрирующую основные функции DAX.