Что такое формула cse в Excel

Обновлено: 21.11.2024

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Еще. Меньше

Формулы массива — это мощные формулы, которые позволяют выполнять сложные вычисления, которые часто невозможно выполнить с помощью стандартных функций рабочего листа. Их также называют формулами «Ctrl-Shift-Enter» или «CSE», поскольку для их ввода необходимо нажать Ctrl+Shift+Enter. Вы можете использовать формулы массива, чтобы сделать, казалось бы, невозможное, например

Подсчитать количество символов в диапазоне ячеек.

Суммируйте числа, соответствующие определенным условиям, например наименьшие значения в диапазоне или числа, находящиеся между верхней и нижней границей.

Суммировать каждое n-е значение в диапазоне значений.

Excel предоставляет два типа формул массива: формулы массива, которые выполняют несколько вычислений для получения одного результата, и формулы массива, которые вычисляют несколько результатов. Некоторые функции рабочего листа возвращают массивы значений или требуют массив значений в качестве аргумента. Дополнительные сведения см. в разделе Рекомендации и примеры формул массива.

Примечание. Если у вас установлена текущая версия Microsoft 365, вы можете просто ввести формулу в верхнюю левую ячейку выходного диапазона, а затем нажать клавишу ВВОД, чтобы подтвердить формулу как формулу динамического массива. В противном случае формулу необходимо ввести как устаревшую формулу массива, сначала выбрав выходной диапазон, введя формулу в верхнюю левую ячейку выходного диапазона, а затем нажав CTRL+SHIFT+ENTER для подтверждения. Excel вставляет фигурные скобки в начале и в конце формулы. Дополнительные сведения о формулах массивов см. в разделе Рекомендации и примеры формул массивов.

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

Нажмите ячейку, в которую вы хотите ввести формулу массива.

Введите формулу, которую хотите использовать.

Формулы массива используют стандартный синтаксис формулы. Все они начинаются со знака равенства (=), и в формулах массива можно использовать любые встроенные функции Excel.

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

Формула сначала умножает акции (ячейки B2 – F2) на их цены (ячейки B3 – F3), а затем складывает эти результаты, чтобы получить общее число 35 525. Это пример формулы массива с одной ячейкой, поскольку формула находится только в одной ячейке.

Нажмите Enter (если у вас есть действующая подписка на Microsoft 365); в противном случае нажмите Ctrl+Shift+Enter.

Когда вы нажимаете Ctrl+Shift+Enter, Excel автоматически вставляет формулу между (парой открывающих и закрывающих фигурных скобок).

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

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

Введите формулу, которую хотите использовать.

В следующем примере формула умножает акции на цену в каждом столбце, и формула сохраняется в выбранных ячейках в строке 5.

Нажмите Enter (если у вас есть действующая подписка на Microsoft 365); в противном случае нажмите Ctrl+Shift+Enter.

Примечание. Если у вас установлена текущая версия Microsoft 365, вы можете просто ввести формулу в верхнюю левую ячейку выходного диапазона, а затем нажать клавишу ВВОД, чтобы подтвердить формулу как формулу динамического массива. В противном случае формулу необходимо ввести как устаревшую формулу массива, сначала выбрав выходной диапазон, введя формулу в верхнюю левую ячейку выходного диапазона, а затем нажав CTRL+SHIFT+ENTER для подтверждения.Excel вставляет фигурные скобки в начале и в конце формулы. Дополнительные сведения о формулах массивов см. в разделе Рекомендации и примеры формул массивов.

Если вам нужно включить новые данные в формулу массива, см. раздел Расширение формулы массива. Вы также можете попробовать:

В сентябре 2018 г. мы выпустили формулы динамического массива для Excel для Microsoft 365. Различия между динамическими массивами и устаревшими формулами Ctrl+Shift+Enter (CSE) обсуждаются ниже.

Примечание. В настоящее время эта функция доступна только для части участников программы предварительной оценки Office. Мы продолжим оптимизировать эти функции в течение следующих нескольких месяцев. Когда они будут готовы, мы предоставим их всем участникам программы предварительной оценки Office и подписчикам Microsoft 365.

Формулы динамического массива:

Может "вытекать" за пределы ячейки, в которую вводится формула. В следующем примере показана функция RANDARRAY в D1, которая распределяется по D1: F5 или 5 строк по 3 столбца. Технически формула динамического массива существует только в ячейке D1, тогда как в устаревшей формуле СПП формулу необходимо вводить во всем диапазоне.

Расчет в контексте 1 x 1. Например, функция СЛУЧАЙ вернет один результат, поэтому, если вы хотите, чтобы в сетку возвращалось несколько случайных чисел, вы можете использовать функцию СЛУЧАЙ. Функции ROW и COLUMN возвращают строку и столбец исходной ячейки. SEQUENCE(ROW()) можно использовать для воспроизведения старого поведения ROW в устаревшей формуле массива.

Любые новые формулы, которые возвращают более одного результата, автоматически удаляются. Нет необходимости нажимать Ctrl+Shift+Enter.

Формулы массива СПП сохранены из соображений совместимости. В будущем вместо этого следует использовать формулы динамических массивов.

Если ячейки в устаревшей формуле массива СПП зависели друг от друга, Excel попытается вычислить каждую по отдельности, а не как массив. Такое поведение было названо «распадом CSE», и при определенных обстоятельствах формулы, рассчитанные таким образом, могли давать противоречивые результаты. Чтобы избежать возможных противоречивых результатов, мы рекомендуем использовать отдельные формулы вместо того, чтобы полагаться на разбиение CSE. Формулы динамического массива не будут распадаться. Вместо этого они сообщат о циклической ссылке, чтобы вы могли решить ее.

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

Вставка/удаление столбцов и строк запрещена в активном диапазоне формул массива СПП. Сначала необходимо удалить все существующие формулы массива, которые мешают.

Нужна дополнительная помощь?

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

Создавайте формулы массива, часто называемые формулами Ctrl+Shift+Enter или CSE, для выполнения вычислений, которые генерируют один или несколько результатов. Посмотрите это видео, чтобы узнать больше.

Зачем использовать формулы массива?

Формулы массива часто называют формулами CSE (Ctrl + Shift + Enter), потому что вместо того, чтобы просто нажимать Enter, вы нажимаете Ctrl + Shift + Enter, чтобы завершить формулу.

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

Подсчитать количество символов, содержащихся в диапазоне ячеек.

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

Суммировать каждое n-е значение в диапазоне значений.

Введите формулу массива

Выберите ячейки, в которых вы хотите увидеть результаты.

Введите формулу.

Нажмите Ctrl+Shift+Enter. Excel заполнит каждую выбранную вами ячейку результатом.

Хотите еще?

Вы хотите вычислить сумму большого диапазона ячеек, например стоимость этих Акций.

Вы можете ввести знак =, B2 (количество акций Contoso), звездочку для умножения, B3, цену акции, знак + для добавления, C2, звездочку, C3 и т. д.

Но с большой серией это заняло бы целую вечность, и было бы легко сделать ошибку и не заметить ее.

Проще? Используйте формулу массива. Массив – это последовательность данных в строке, столбце или комбинации строк и столбцов.

Возможно, вы использовали их раньше. B2:F2 – это массив, также часто называемый диапазоном ячеек.

Формула массива выполняет вычисления данных в одном или нескольких массивах, возвращая один или несколько результатов.

В формуле массива вы вводите = знак, СУММ, открывающую скобку, выбираете ячейки в строке "Акции" (это первый массив в формуле массива), затем умножаете звездочку, выбираете ячейки в строке "Цена" (это второй массив в формуле массива).

И это ключевое отличие: когда вы вводите формулу массива, вы нажимаете Ctrl+Shift+Enter, а не просто Enter.

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

Я не вводил закрывающую скобку в формулу, нажатие Ctrl+Shift+Enter делает это за вас.

Формула массива умножает каждую ячейку в строке "Акции" на соответствующую ячейку в строке "Цена", а затем суммирует или складывает их и возвращает общую сумму 14 421,87 долларов США.

В строке формул формула заключена в фигурные скобки < >, что означает, что это формула массива.

Это делается автоматически при нажатии Ctrl+Shift+Enter. Не вводите эти фигурные скобки. Если вы это сделаете, Excel будет интерпретировать вашу формулу как текст, а не как формулу.

Значения, указанные в этих формулах, могут отображаться в виде значений в строке, столбце или матрице (строках и столбцах). Чтобы более подробно объяснить формулу массива, проще показать несколько примеров.

Без формулы массива

В приведенном ниже примере вы найдете разные фрукты в (A2:A9), количество проданных за день (B2:B9) и цену за продукт (C2:C9). Расчет дохода на плод теперь выполняется в каждой ячейке столбца дохода (D2:D9). Это можно сделать быстрее, используя формулу массива с несколькими ячейками.

Формула многоячеистого массива

Формула массива с несколькими ячейками возвращает несколько результатов в строке или столбце с использованием одной формулы. Чтобы рассчитать сразу все значения столбца «Доход на плод» в приведенном выше примере, мы начинаем с выбора диапазона ячеек, в котором мы хотим опубликовать результаты, и нажимаем F2, чтобы выбрать первую ячейку в диапазоне: /p>

Затем мы вводим формулу массива в выделенную ячейку D2:

НО ПРИДЕРЖИВАЙТЕ ЛОШАДЕЙ! Когда вы нажимаете «Ввод» после написания формулы, Excel будет вычислять результат только текущей строки! Этот тип формулы также называется формулой CSE, поскольку он будет правильно выполняться только при использовании CTRL+SHIFT+ENTER.

Как видите, формула вычислила сразу все значения массива. Excel добавил фигурные скобки < >в формулу, чтобы указать, что это функция массива.

Формула массива из одной ячейки

Формула массива с одной ячейкой вернет один результат в одной ячейке при выполнении одного или нескольких вычислений.

В нашем примере общий доход от фруктов, конечно же, можно рассчитать с помощью функции СУММ на значениях в столбце «Доход от одного фрукта»:

Но при использовании функции массива нам не нужны промежуточные результаты для вычисления общей суммы. Мы просто выбираем ячейку, в которой хотим опубликовать общий доход от фруктов, и вводим формулу «=СУММ(B2:B9*C2:C9)»:

Читайте также: