Формулы массивов

Формулы массивов Массив – это набор данных, объединенных в группу. Массивы бывают одномерными (строка, столбец) или двумерными (таблица или матрица). Формула массива – это формула, в которой производится вычисления над одним или несколькими наборами данных (аргументами массива), а затем отображающая один или несколько результатов. Одна формула массива может заменить собой несколько формул, расположенных на листе, помогая выполнять сложные расчеты или выполнять вычисления, недоступные при помощи других формул. Признаком формулы массивы являются фигурные скобки, в которые заключена формула. Формулы массива часто называют "формулами CSE", поскольку для их ввода используется сочетание клавиш CTRL+SHIFT+ВВОД, а не простое нажатие клавиши ВВОД. 3 Сведения о константах массива  Константы массива являются компонентами формул массива. Константы массива создаются путем ввода списка элементов с последующей вставкой фигурных скобок ({ }) вокруг списка, следующим образом:  ={1;2;3;4;5}  Если элементы списка разделяются точками с запятой, будет создан горизонтальный массив (строка). Если элементы разделяются двоеточиями, будет создан вертикальный массив (столбец). Чтобы создать двумерный массив, следует разделить элементы строк точками с запятой, а строки — двоеточиями.  Так выглядит массив из одной строки: {1;2;3;4}. Массив из одного столбца: {1:2:3:4}. А вот массив из двух строк и четырех столбцов: {1;2;3;4:5;6;7;8}. В двухстрочном массиве первая строка представлена 1, 2, 3 и 4, а вторая строка — 5, 6, 7 и 8. Строки разделены двоеточием между записями 4 и 5. 4 Создание одномерны х или двумерны х констант Создание горизонтальной константы Создание вертикальной константы {={1:2:3:4}} {={1;2;3;4}} 5 Создание двумерной константы {={1;2;3;4:5;6;7;8:9;10;11;12}} 4 Использование констант в формулах {=A1:E1*{1;2;3;4;5}} {=СУММ(A1:E1*{1;2;3;4;5})} {=СУММ(A1*1;B1*2;C1*3;D1*4;E1*5)} 5 Элементы , которы е можно использовать в константах  Константы массива могут содержать числа, текст, логические значения (такие как ИСТИНА и ЛОЖЬ), а также значения ошибок (например, #Н/Д). Можно использовать числа в целочисленном, десятичном или экспоненциальном формате. При добавлении текста требуется заключить его в двойные кавычки (" ).  Константы массива не могут содержать дополнительные массивы, формулы или функции. Другими словами, они могут содержать только текст или числа, разделенные запятыми или точками с запятой. При вводе такой формулы, как {1;2;A1:D4} или {1;2;СУММ(Q2:Z8)}, выводится предупреждение. Кроме того, числовые значения не могут содержать знаки процента, знаки валюты, запятые или кавычки. 6 Именование констант массива 7 Использование констант массива Умножение каждого из элементов массива {={1;2;3;4:5;6;7;8:9;10;11;12}*2} 8 Использование констант массива Возведение в квадрат элементов массива {={1;2;3;4:5;6;7;8:9;10;11;12}*{1;2;3;4:5;6;7;8:9;10;11;12}} {={1;2;3;4:5;6;7;8:9;10;11;12}^2} 9 Транспонирование {=ТРАНСП({1;2;3;4;5})} {=ТРАНСП({1:2:3:4:5})} 10 Создание массивов и констант массива на основе существующих значений {=C8:E10} {={10;20;30:40;50;60:70;80;90}} 11 Создание константы массива на основе существующих значений F2 F9 14 Подсчет знаков в диапазоне ячеек {=СУММ(ДЛСТР(A1:A5))} 13 Формулы массивов Задача: Сформировать таблицу умножения Решение: Горизонтальный одномерный массив B1:K1 умножить на вертикальный одномерный массив A2:A11. результат разместить в двумерный массив B2:K11 14 Формулы массивов • Выделяется диапазон для вставки • Записывается формула при выделенном диапазоне умножения одного массива на другой • Нажимается Ctrl+Shift+Enter (это признак операций с массивом – добавляются фигурные скобки) 17 Изменение формулы массива В диапазоне массиванельзя изменять или удалять формулы в отдельных ячейках. Это можно сделать только для всего массива. 1. Выделить весь массив: 1. вручную 2. выделить ячейку с формулоймассива, нажать клавишу F5, выбрать Вы делит ь [Special], затем Текущий массив [Current array]. 2. Изменить формулу в строке формул или нажать клавишу F2 для изменения в ячейке (во время редактирования фигурные скобки пропадают). 3. Завершить формулу нажатием Ctrl+Shift+Enter. 18 Изменение формулы массива F5 • Редактирование формулы • Ctrl+Shift+Ent er F2 19 Использование формулы массивов и функций Задача: Рассчитать стоимость каждого товара и итоговую стоимость заказа • Выделить диапазон • Написать формулу • Ctrl+Shift+Enter {=B2:B11*C2:C11*(1-E2:E11)+D2:D11} Чем лучше? • Формула пишется один раз • Нет ошибок ввода • Нет ошибок определения диапазонов • Невозможно изменить значение или формулу в итоговой ячейке 20 Использование формулы массивов и функций Задача: Рассчитать стоимость каждого товара и итоговую стоимость заказа =СУММ(B2:B11*C2:C11*(1-E2:E11)+D2:D11) 21 Поиск Функции ИНДЕКС() и ПОИСКПОЗ() 20 Функция ИНДЕКС Функция ИНДЕКС возвращает значение или ссылку на значение из таблицы или диапазона. Синтаксис ИНДЕКС(массив; номер_строки; [номер_столбца]) 21 Аргументы функции ИНДЕКС Массив — обязательный аргумент. Диапазон ячеек или константа массива. Если массив содержит только одну строку или один столбец, соответствующий аргумент "номер_строки" или "номер_столбца" не является обязательным. Если массив содержит больше одной строки и одного столбца, а из аргументов "номер_строки" и "номер_столбца" задан только один, функция ИНДЕКС возвращает массив, состоящий из целой строки или целого столбца аргумента "массив". Номер_строки — обязательный аргумент. Выбирает строку в массиве, из которой требуется возвратить значение. Если аргумент "номер_строки" опущен, аргумент "номер_столбца" является обязательным. Номер_столбца — необязательный аргумент. Выбирает столбец в массиве, из которого требуется возвратить значение. Если аргумент "номер_столбца" опущен, аргумент "номер_строки" является обязательным. 22 Функция ИНДЕКС. Пример 1 23 Функция ИНДЕКС. Пример 2 24 Функция ИНДЕКС. Пример 3 25 Функция ПОИСКПОЗ Функция ПОИСКПОЗ выполняет поиск указанного элемента в диапазоне ячеек и возвращает относительную позицию этого элемента в диапазоне. Возвращает не само значение, а его позицию в аргументе просматриваемый_массив. Синтаксис ПОИСКПОЗ( искомое_значение; просматриваемый_массив; [тип_сопоставления] ) 26 Аргументы функции ПОИСКПОЗ Искомое_значение. Обязательный аргумент. Значение, которое сопоставляется со значениями в аргументе просматриваемый_массив. Например, при поиске номера в телефонной книге имя абонента указывается в качестве искомого значения, а нужным значением будет номер телефона. Аргумент искомое_значение может быть значением (числом, текстом или логическим значением) или ссылкой на ячейку, содержащую такое значение. Просматриваемы й_массив Обязательный аргумент. Диапазон ячеек, в которых производится поиск. Тип_сопоставления. Необязательный аргумент. Число -1, 0 или 1. Аргумент тип_сопоставления указывает, каким образом в Microsoft Excel искомое_значение сопоставляется со значениями в аргументе просматриваемый_массив. По умолчанию в качестве этого аргумента используется значение 1. 27 Тип_сопоставления 1 или опущен Функция ПОИСКПОЗ находит наибольшее значение, которое меньше или равно значению аргумента искомое_значение. Просматриваемый_массив должен быть упорядочен по возрастанию: ..., -2, -1, 0, 1, 2, ..., A-Z, ЛОЖЬ, ИСТИНА. Функция ПОИСКПОЗ находит первое значение, равное аргументу искомое_значение. Просматриваемый_массив может быть не упорядочен. -1 Функция ПОИСКПОЗ находит наименьшее значение, которое больше или равно значению аргумента искомое_значение. Просматриваемый_массив должен быть упорядочен по убыванию: ИСТИНА, ЛОЖЬ, Z-A, ..., 2, 1, 0, -1, 2, ... и т. д. 28 Пример функции ПОИСКПОЗ 29 Пример поиска. ИНДЕКС + ПОИСКПОЗ =ИНДЕКС(data4!$A$2:$J$1276;ПОИСКПОЗ(C2;data4!$C$2:$C$1276;0);1) 32 Использование более сложны х формул массива 31 Подсчет знаков в диапазоне ячеек {=СУММ(ДЛСТР(A1:A5))} Задача: Найти содержимое ячейки с наибольшим количеством символов 1. Максимальная длина строки {=МАКС(ДЛСТР(A1:A5))} 2. Номер позиции элемента с максимальной длиной строки {=ПОИСКПОЗ(МАКС(ДЛСТР(A1:A5));ДЛСТР(A1:A5);0)} 3. Вывод элемента списка с максимальной длиной строки {=ИНДЕКС(A1:A5;ПОИСКПОЗ(МАКС(ДЛСТР(A1:A5));ДЛСТР(A1:A5);0);1)} 32 Поиск n наименьших значений в диапазоне НАИМЕНЬШИЙ(массив;k) Возвращает k-ое наименьшее значение в множестве данных. • Массив Обязательный. Массив или диапазон числовых данных, для которого определяется kое наименьшее значение. • k Обязательный. Позиция (начиная с наименьшего числа) в массиве или диапазоне данных. {=НАИМЕНЬШИЙ(A1:A10;{1:2:3})} Поскольку работаем с вертикальным диапазоном, используем в качестве разделителя элементов массива 35 двоеточие Подсчет среднего значения с исключением нулей {=СРЗНАЧ(ЕСЛИ(A1:A10<>0;A1:A10))} Описание работы формулы: • При помощи функции ЕСЛИ() формируется новый массив, не содержащий нулевые значения. Для этого нового массива вычисляется среднее значение • В ячейке B3 рассчитано среднее значение по формуле =СРЗНАЧ(A1:A10) 36 Суммирование значений с учетом условий {=СУММ(ЕСЛИ(A1:A10>B1;A1:A10))} Описание работы формулы: • При помощи функции ЕСЛИ() формируется новый массив, содержащий значения, отвечающие поставленным условиям • Для полученного нового массива применяется функция СУММ() Замечание Для объединения нескольких условия можно использовать знаки * (операция И) и + (операция ИЛИ), например • {=СУММ(ЕСЛИ((A1:A10>B1)*(A1:A10B1)+(A1:A10