Поиск n наименьших значений в диапазоне

В разделе Использование констант в формулах далее в этой статье демонстрируются преимущества использования констант массива. Примеры, приведенные в следующем разделе, иллюстрируют создание формул массива с одной или несколькими ячейками.

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

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

Поиск n наименьших значений в диапазоне

Примечание : Формулы массива иногда называют формулами CSE, так как для их ввода в книгах используется сочетание клавиш CTRL+SHIFT+ВВОД (ENTER). Формула массива представляет собой формулу, при помощи которой можно выполнять различные вычисления с одним или несколькими элементами в массиве. Например, можно поместить формулу массива в диапазон ячеек и воспользоваться этой формулой для вычисления столбца или строки промежуточных итогов.

Создание массивов и констант массива на основе существующих значений

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

Excel заключит формулу в фигурные скобки ({ }) и поместит ее экземпляр в каждую ячейку выделенного диапазона. В этом случае будет выполнено перемножение значений массива (диапазон ячеек C2 — D11), а затем — при помощи функции СУММ — сложение полученных результатов. В данном примере демонстрируется, какие широкие возможности предоставляет данный тип формул.

Ниже перечислены дополнительные преимущества, которые обеспечивает использование формул массива. Согласованность Если щелкнуть несколько ячеек ниже ячейки E2, вы увидите, что все они содержат одну и ту же формулу. Безопасность. Компонент формулы массива с несколькими ячейками нельзя переписать. Вам придется выделить весь диапазон ячеек (E2 — E11) и изменить формулу для всех ячеек либо оставить массив без изменений.

Зачастую вместо нескольких промежуточных формул можно использовать одну формулу массива. Например, в книге, которую вы создали для этого упражнения, для вычисления результатов в столбце E применяется всего лишь одна формула массива. При этом Excel заключит формулу массива в фигурные скобки. Если подставить скобки вручную, формула будет преобразована в текстовую строку и перестанет работать. Следующая особенность формул массива, которую требуется понять, состоит в том, что функции массива являются разновидностью сокращенной записи.

Применение основных формул массива

Главное правило при создании формулы массива стоит повторить еще раз: нажимайте сочетание клавиш CTRL+SHIFT+ВВОД, когда хотите ввести или изменить формулу массива. Необходимо выделить диапазон ячеек, который будет содержать результаты, прежде чем вводить формулу. Содержимое отдельной ячейки в формуле массива изменить нельзя.

Сведения о константах массива

Формулу массива можно переместить или удалить только целиком. Другими словами, чтобы уменьшить формулу массива, сначала нужно удалить существующую формулу, а затем создать новую формулу с самого начала. Совет : Чтобы удалить формулу массива, выделите ее целиком (например, =C2:C11*D2:D11), нажмите клавишу DELETE, а затем — клавиши CTRL+SHIFT+ВВОД.

Увеличение диапазона формулы массива

В книге примера удалите текст и формулы с одной ячейкой, которые расположены ниже основной таблицы. Выделите диапазон ячеек, содержащий текущую формулу массива (E2:E11), а также пустые ячейки (E12:E17), расположенные рядом с новыми данными.

Не забывайте делать это каждый раз при вводе или изменении формулы массива. Другие пользователи могут не понять ваши формулы. Большие формулы массива могут замедлять вычисления на компьютерах с невысокой скоростью процессора или небольшим объемом памяти.

Краткий обзор синтаксиса формулы массива

Ранее в этой статье мы подчеркивали необходимость нажимать клавиши CTRL+SHIFT+ВВОД при создании формул массива. Если элементы списка разделяются точками с запятой, будет создан горизонтальный массив (строка).

В ячейке A3 появится значение 85. В следующем разделе поясняется работа этой формулы. Использованная формула состоит из нескольких частей. Последний элемент внутри скобок — это константа массива: {1;2;3;4;5}. Поскольку в первую очередь выполняются операции с элементами, заключенными в скобки, далее будут использоваться значения, хранящиеся в книге (диапазон A1:E1), и соответствующий оператор.

В результате при выполнении функции СТРОКА всегда будет создаваться нужный массив целочисленных значений. В строке формул измените значение C11 на C17, измените значение D11 на D17, а затем нажмите клавиши CTRL+SHIFT+ВВОД. Получится тот же результат, что и в предыдущем упражнении, где использовалась формула массива: =СУММ(A1:E1*{1;2;3;4;5}).