Файл: Методичка Excel.docx

Формула массива – это формула, в которой производится несколько вычислений над одним или несколькими наборами значений (аргументами массива), а затем отображающая один или несколько результатов. Формула массива создается по тем же правилам, что и обычная формула, только помещается в фигурные скобки { } и закрепляется сочетанием клавиш Ctrl+Shift+Enter. При этом если ввести данные скобки вручную, то формула будет преобразована в текстовую строку и перестанет работать.

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

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

Одномерный вертикальный массив – когда значения находятся в отдельном столбце.

Двумерный массив – когда значения находятся в нескольких столбцах или строках.

Рассмотрим создание формулы массива с несколькими ячейками на следующем примере: имеются следующие диапазоны данных – А2:А5 и В2:В5. Необходимо умножить значения диапазона А2:А5 на значения В2:В5.

1. В окне открытого листа выделите диапазон ячеек, где будет располагаться формула массива и введите знак (=);

2. В нашем примере пусть это будут ячейки С2:С5;

3. Введите первый нужный диапазон ячеек А2:А5;

4. Введите оператор умножения (*), а затем следующий диапазон ячеек В2:В5;

5. Завершите операцию сочетанием клавиш Ctrl+Shift+Enter;

6. Созданная формула примет вид {=А2:А5*В2:В5}.

Рис. 1.4 Формула массива с несколькими ячейками

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

Имеется следующие диапазоны данных – А2:А6, В2:В6, D2:D6 и Е2:Е6. Необходимо умножить значения диапазона А2:А6 на значения В2:В6, затем значения диапазона D2:D6 на значения Е2:Е6. Полученные результаты следует сложить. Для этого необходимо:

1. В окне открытого листа выделите ячейку, где будет располагаться формула массива и введите знак (=);

2. Пусть в нашем примере это будет ячейка F2;

3. Введите название функции автосуммы СУММ;

4. Откройте круглые скобки и введите первый нужный диапазон ячеек А2:А6;

5. Введите оператор умножения (*), а затем следующий диапазон ячеек В2:В6;

6. Введите оператор перечисления (;), а затем следующий диапазон ячеек D2:D6;

7. Введите оператор умножения (*), а затем последний диапазон ячеек Е2:Е6;

8. Закройте круглые скобки и нажмите сочетание клавиш Ctrl+Shift+Enter;

9. Созданная формула преобразится и примет вид:

{=СУММ(А2:А6*В3:В6;D2:D6*E2:E6)}.

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

Способы изменения формулы массива:

1 способ (изменение формулы с одной ячейкой):

1. В окне открытого листа выделите ячейку с формулой массив;

2. Щелкните курсором в окошке строки формул и внесите нужные изменения;

3. Завершите операцию сочетанием клавиш Ctrl+Shift+Enter;

2 способ (изменение формулы с несколькими ячейками):

1. В окне открытого листа выделите весь диапазон ячеек с формулой массива;

2. Щелкните курсором в окошке строки формул и внесите нужные изменения;

3. Завершите операцию сочетанием клавиш Ctrl+Shift+Enter.

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

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

2. Щелкните курсором в окошке строки формул и внесите нужные изменения в ссылках на ячейки;

3. Завершите операцию сочетанием клавиш Ctrl+Shift+Enter.

Константы массива

Константы массива – это компоненты формул массива, которые создаются путем ввода списка элементов внутри фигурных скобок ({ }). Скобки при этом вводятся вручную, например:

={1;2;3;4;5}.

Константы массива могут содержать числа, текст, логические значения (ИСТИНА и ЛОЖЬ), а также значения ошибок. При этом числа можно использовать в целочисленном, десятичном или экспоненциальном формате. А текст необходимо заключать в двойные кавычки («).

При горизонтальном массиве (строка) элементы списка разделены точкой с запятой.

При вертикальном массиве (столбец) элементы списка разделены двоеточием.

При двумерном массиве элементы строк разделены точкой с запятой запятыми, а столбцы – двоеточием.

Между собой элементы отделяются пробелом.

Горизонтальная константа

1. В окне открытого листа выделите горизонтальный ряд ячеек с числами. Например, ячейки А1_С1 со значениями 1,2,3;

2. В окошке строки формул введите знак (=) и откройте фигурную скобку;

3. Введите числа, содержащиеся в выделенном ряде ячеек, разделяя их точкой с запятой: ={1;2;3}

4. Закройте фигурные скобки и нажмите сочетание клавиш Ctrl+Shift+Enter.

Формула примет следующий вид: {={1;2;3}}.

Рис. 1.6 Горизонтальная константа

Вертикальная константа

1. В окне открытого листа выделите вертикальный ряд ячеек с числами. Например, ячейки А2_А4 со значениями 4,5,6;

2. В окошке строки формул введите знак (=) и откройте фигурную скобку;

3. Введите числа, содержащиеся в выделенном ряде ячеек, разделяя их двоеточиями:

={4:5:6}

4. Закройте фигурные скобки и нажмите сочетание клавиш Ctrl+Shift+Enter.

Формула примет следующий вид: {={4:5:6}}.

Рис. 1.7 Вертикальная константа

Двумерная константа

1. В окне открытого листа выделите прямоугольный диапазон ячеек с числами. Например, ячейки А1_С3 со значениями 1,2,3,4,5,6,7,8,9;

2. В окошке строки формул введите знак (=) и откройте фигурную скобку;

3. Введите числа, содержащиеся в выделенном диапазоне ячеек, разделяя горизонтальные константы точками с запятыми, а вертикальные – двоеточиями. Между собой горизонтальные и вертикальные константы отделяются пробелом:

={1;2;3: 4;5;6: 7,8,9}

4. Закройте фигурные скобки и нажмите сочетание клавиш Ctrl+Shift+Enter.

{={1;2;3: 4;5;6: 7,8,9}}

Рис. 1.8 Двумерная константа

Использование константы массива

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

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

2. Введите нужную формулу. В нашем случае это: =СУММА(A1:А3*{1;2;3}),

где:

• СУММА – функция автосуммирования;

• A1:А3 – диапазон ячеек с исходными значениями 10,20,30;

• * – оператор умножения;

• {1;2;3} – константа массива.

3. Используйте сочетание клавиш Ctrl+Shift+Enter.

Формула примет следующий вид: {=СУММА(A1:А3*{1;2;3})}

В ячейке В1 отобразится значение 360.

Данная формула соответствует следующей формуле: =СУММА(A1*1,А2*2,А3*3).

Рис. 1.9 Использование константы массива

1.2 Работа с функциями

Функции – это заранее созданные формулы, которые выполняют вычисления по введенным величинам и в указанном порядке. Функция включает следующие элементы:

• знак равенства (=);

• собственное имя, например СУММ;

• открывающая скобка, внутри которой находится список аргументов, разделенных точкой с запятой;

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

• закрывающая скобка.

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

В Excel существуют математические, финансовые, статистические и другие функции. Имя функции можно вводить вручную с клавиатуры, а можно выбирать в окне «Мастер функций».

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

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

1 способ:

1. В окне открытого листа выделите ячейку, где будет располагаться функция;

2. Перейдите к вкладке «Формулы» и в группе «Библиотека функций» щелкните по кнопке «Вставить функцию»;

3. В окне «Мастер функций – шаг 1 из 2» в графе «Категории» раскройте список существующих типов функций и выберите нужную категорию: например, «Математические»;

4. В группе «Выберите функцию» просмотрите список функций заданной категории и выберите нужную, например, «ОКРУГЛВВЕРХ»;

5. Перейдите к следующему шагу щелчком по кнопке «ОК»;

6. В окне «Аргументы функции» введите в поля аргументов адреса ячеек для вычисления.

Примечание. Для каждой функции окно «Аргументы функции» имеет собственный вид. Например, в окне «Аргументы функции ОКРУГЛВВЕРХ» необходимо в графе «Число» ввести адрес ячейки с числом, которое необходимо округлить, а в графе «Число разрядов» задать количество разрядов после запятой, до которых необходимо округлить число.