Excel работа с большими массивами данных в

5 основных функции для работы с массивами

Доброго времени суток друзья!

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

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

Ну, что же, изучим необходимые функции для работы с массивами:

1. Функция ВЫБОР (CHOOSE)

Позволит вам выбрать значение из общего списка по указанному номеру позиции:

=ВЫБОР(2;”Стул”;”Стол”;”Шкаф”;”Диван”)

2. Функция ИНДЕКС (INDEX)

Эта функция возвращает указанное значение из одно- или двумерного диапазона:

=ИНДЕКС(A1:C6;4;3)

Как видно с примера, полученное значение 37, в указанном диапазоне стоит на пересечении строки №4 и столбика №3 в диапазоне A1:C6 указанном в формуле. В более простом примере показано как в диапазоне С1:С6, на 2 месте находится значение 15:

=ИНДЕКС(С1:С6;2)

3. Функция ПОИСКПОЗ (MATCH)

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

=ПОИСКПОЗ(B3;B2:B5;0)

С примера вы можете видеть что слово «Стол» занимает 2 позицию в указанном диапазоне. Замечу, что третий аргумент в функции не является обязательным. При введенном значении 0, функция вернет ту позицию элемента массива, которое точно совпадает со значением, которое мы ищем. В случае, когда точное совпадение отсутствует, функция выдаст ошибку #Н/Д (#N/A).

4. Функция ГПР (HLOOKUP)

Ищет значение в указанном диапазоне и возвращает значение ячейки, которая находится в указанной строке того же столбца: =ГПР(C1;$B$1:$E$2;1;ЛОЖЬ). Как видите с примера, функция ГПР ищет в указанном диапазоне $B$1:$E$2 (знаком $ я указал абсолютную ссылку) и согласно условию возвращает искомое значение из первой строки, а аргумент «ЛОЖЬ» означает, что-либо, будет найдено нужное значение, либо мы получим ошибку #Н/Д.

5. Функция ВПР (VLOOKUP)

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

=ВПР(B4;$B$2:$C$5;2;ЛОЖЬ)

Как видим, формула идентична предыдущей функции ГПР и так же ищет указанный номер «B4» в диапазоне $B$2:$C$5 со знаком $ (это сделано для создания абсолютной ссылки, что бы при копировании формулы на диапазон, аргумент не будет изменен), в третьем столбце, так как аргумент функции равен 2. Ну и четвёртый аргумент равен значению «ЛОЖЬ», это означает, что-либо будет найдено совпадение значений, либо будет получено сообщение об ошибке #Н/Д. Теперь при необходимости, мы копируем формулу, и она перенесёт все правильные аргументы по всему диапазону вычислений. Это возможно стало из-за абсолютной ссылки на массив значений, а вот первый аргумент на B4, при копировании, должен измениться на B5 и так далее.

А на этом у меня всё! Я очень надеюсь, что описание 5 основных функций для работы с массивами вам стали ближе и понятнее. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!

Не забудьте поблагодарить автора!

Деньги для людей умных составляют средство, для глупцов — цель. А. Декурсель

Источник: topexcel.ru

Управление массивами в Microsoft Excel

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

Операции с массивами

Массив – это группа данных, которая расположена на листе в смежных ячейках. По большому счету, любую таблицу можно считать массивом, но не каждый из них является таблицей, так как он может являться просто диапазоном. По своей сущности такие области могут быть одномерными или двумерными (матрицы). В первом случае все данные располагаются только в одном столбце или строке.

Во втором — в нескольких одновременно.

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

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

Создание формулы

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

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

Координаты массива имеют вид адресов первой её ячейки и последней, разделенные двоеточием. Если диапазон двумерный, то первая и последняя ячейки расположены по диагонали друг от друга. Например, адрес одномерного массива может быть таким: A2:A7.

А пример адреса двумерного диапазона выглядит следующим образом: A2:D7.

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

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

    • Изменение содержимого массива

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

    Если вы закроете, это сообщение, нажав на кнопку «OK», а потом попытаетесь переместить курсор с помощью мышки, или просто нажмете кнопку «Enter», то информационное сообщение появится опять. Не получится также закрыть окно программы или сохранить документ. Все время будет появляться это назойливое сообщение, которое блокирует любые действия. А выход из ситуации есть и он довольно прост

      Закройте информационное окно, нажав на кнопку «OK».

  • Затем нажмете на кнопку «Отмена», которая расположена в группе значков слева от строки формул, и представляет собой пиктограмму в виде крестика. Также можно нажать на кнопку Esc на клавиатуре. После любой из этих операций произойдет отмена действия, и вы сможете работать с листом так, как и прежде.
    • Читайте также:  Выпадающий список в эксель

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

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

  • После того, как изменения внесены, набираем комбинацию Ctrl+Shift+Esc. Формула будет изменена.

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

  • После этого формула будет удалена со всей области. Теперь в неё можно будет вводить любые данные.

    • Функции массивов

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

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

    Правила ввода и редактирования функций, если они выводят результат сразу в несколько ячеек, те же самые, что и для обычных формул массива. То есть, после ввода значения обязательно нужно установить курсор в строку формул и набрать сочетание клавиш Ctrl+Shift+Enter.

    Оператор СУММ

    Одной из наиболее востребованных функций в Экселе является СУММ. Её можно применять, как для суммирования содержимого отдельных ячеек, так и для нахождения суммы целых массивов. Синтаксис этого оператора для массивов выглядит следующим образом:

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

    Оператор ТРАНСП

    Функция ТРАНСП является типичным оператором массивов. Она позволяет переворачивать таблицы или матрицы, то есть, менять строки и столбцы местами. При этом она использует исключительно вывод результата в диапазон ячеек, поэтому после введения данного оператора обязательно нужно применять сочетание Ctrl+Shift+Enter. Также нужно отметить, что перед введением самого выражения нужно выделить на листе область, у которой количество ячеек в столбце будет равно числу ячеек в строке исходной таблицы (матрицы) и, наоборот, количество ячеек в строке должно равняться их числу в столбце исходника. Синтаксис оператора следующий:

    Оператор МОБР

    Функция МОБР позволяет производить вычисление обратной матрицы. Все правила ввода значений у этого оператора точно такие же, как и у предыдущего. Но важно знать, что вычисление обратной матрицы возможно исключительно в том случае, если она содержит равное количество строк и столбцов, и если её определитель не равен нулю. Если применять данную функцию к области с разным количеством строк и столбцов, то вместо корректного результата на выходе отобразится значение «#ЗНАЧ!». Синтаксис у этой формулы такой:

    Для того чтобы рассчитать определитель, применяется функция со следующим синтаксисом:

    Как видим, операции с диапазонами помогают сэкономить время при вычислениях, а также свободное пространство листа, ведь не нужно дополнительно суммировать данные, которые объединены в диапазон, для последующей работы с ними. Все это выполняется «на лету». А для преобразования таблиц и матриц только функции массивов и подходят, так как обычные формулы не в силах справиться с подобными задачами. Но в то же время нужно учесть, что к подобным выражениям применяются дополнительные правила ввода и редактирования.

    Отблагодарите автора, поделитесь статьей в социальных сетях.

    Источник: lumpics.ru

    Описание ограничений для работы с массивами в Excel

    Описание

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

    Дополнительные сведения

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

    доступная память;

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

    Правило “весь столбец”

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

    В столбце в Microsoft Office Excel 2003 и более ранних версиях Excel есть 65 536 ячеек. В столбце в Microsoft Office Excel 2007 есть 1 048 576 ячеек.

    Максимальные формулы массива

    В Excel 2003 и более ранних версиях Excel один лист может содержать не более 65 472 формул массива, ссылающихся на другой лист. Если вы хотите использовать больше формул, разделите их на несколько листов, чтобы на один лист было менее 65 472 ссылок.

    Например, на листе Sheet1 книги можно создать следующие элементы:

    • 65 472 формулы массива, которые ссылаются на Лист2
    • 65 472 формулы массива, ссылающихся на Sheet3
    • 65 472 формулы массива, ссылающихся на Sheet4

    Если вы попытаетесь создать более 65 472 формул массива, ссылающихся на определенный лист, формулы массива, введенные после формулы массива с номером 65 472, могут исчезнуть при вводе.

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

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

    Excel 2007

    A1: = SUM (ЕСЛИ (B1: B1048576 = 0, 1, 0))

    Формула в ячейке a1 возвращает результат 1048576. Это правильный результат.

    A2: = SUM (ЕСЛИ (Б:Б = 0, 1, 0))

    Формула в ячейке A2 возвращает результат 1048576. Это правильный результат.

    A3: = SUM (ЕСЛИ (B1: J1048576 = 0, 1, 0))

    Формула в ячейке A3 возвращает результат 9437184. Это правильный результат.

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

    A4: = SUM (ЕСЛИ (Б:Ж = 0, 1, 0))

    Формула в ячейке A4 возвращает результат 9437184. Это правильный результат.

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

    Читайте также:  Excel выборка данных из таблицы

    A5: = SUM (ЕСЛИ (B1: DD1048576 = 0, 1, 0))

    При вводе этой формулы в ячейку A5 может появиться одно из следующих сообщений об ошибке:

    Excel исчерпал ресурсы при попытке вычислить одну или несколько формул. В результате эти формулы невозможно оценить.

    Чтобы определить уникальный номер, связанный с получаемым сообщением, нажмите клавиши CTRL + SHIFT + I. В правом нижнем углу этого сообщения отображается следующее число:

    101758

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

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

    После пересчета результатов Excel реагирует ожидаемым образом. Формула в ячейке A5 возвращает значение 0 (ноль).

    Excel 2003 и более ранние версии Excel

    A1: = SUM (ЕСЛИ (B1: B65535 = 0, 1, 0))

    Формула в ячейке a1 возвращает результат 65535. Это правильный результат.

    A2: = SUM (ЕСЛИ (Б:Б = 0, 1, 0))

    Формула в ячейке a2 Возвращает #NUM! ошибка, так как формула массива ссылается на целый столбец ячеек.

    A3: = SUM (ЕСЛИ (B1: J65535 = 0, 1, 0))

    Формула в ячейке A3 возвращает результат 589815. Это правильный результат.

    Вычисление формулы может занять много времени, так как формула выполняет проверку почти 600 000 ячеек.

    A4: = SUM (ЕСЛИ (Б:Ж = 0, 1, 0))

    Как и формула в ячейке A2, формула в ячейке A4 Возвращает #NUM! ошибка, так как формула массива ссылается на целый столбец ячеек.

    A5: = SUM (ЕСЛИ (B1: DD65535 = 0, 1, 0))

    При вводе формулы в ячейке A5 может появиться одно из следующих сообщений об ошибке:

    Недостаточно памяти. Продолжить без отмены?

    Недостаточно памяти.

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

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

    После пересчета результатов Excel реагирует ожидаемым образом. Формула в ячейке A5 возвращает значение 0 (ноль).

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

    • ЛИНЕЙН ()
    • МДЕТЕРМ ()
    • МИНВЕРСЕ ()
    • ММУЛТ ()
    • SUM (ЕСЛИ ())
    • SUMPRODUCT ()
    • ТРАНСПОНИРОВАТЬ ()
    • ТЕНДЕНЦИЯ ()

    Следующие факты о функциях удобно запомнить.

    • Если какие бы то ни было ячейки массива пусты или содержат текст, МИНВЕРСЕ возвращает #VALUE! значение ошибки.
    • МИНВЕРСЕ также возвращает #VALUE! значение ошибки, если массив не имеет равное количество строк и столбцов.
    • МИНВЕРСЕ возвращает #VALUE! ошибка, если возвращаемый массив превышает 52 столбцов по 52 строк.
    • Функция ММУЛТ возвращает #VALUE! Если выходные данные превышают 5460 ячеек.
    • Функция МДЕТЕРМ возвращает #VALUE! значение, если размер возвращаемого массива превышает 73 строк по 73 столбцам.

    Источник: docs.microsoft.com

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

    Терминология

    Под массивом обычно понимают набор данных, объединенных в группу. Массивы бывают одномерные (элементы массива образуют строку или столбец) или двумерные (матрица). Легко сообразить, что почти в любой таблице Excel при желании можно найти один или несколько таких массивов:

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

    Пример 1. Классика жанра – товарный чек

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

    1. выделяем ячейку С7
    2. вводим с клавиатуры =СУММ(
    3. выделяем диапазон B2:B5
    4. вводим знак умножения (звездочка)
    5. выделяем диапазон C2:C5 и закрываем скобку функции СУММ – в итоге должно получиться так:

  • чтобы Excel воспринял нашу формулу как формулу массива жмем не Enter, как обычно, а Ctrl + Shift + Enter

    • Т.е. Excel произвел попарное умножение элементов массивов B2:B5 и C2:C5 и образовал новый массив стоимостей (в памяти компьютера), а затем сложил все элементы этого нового массива.

    Обратите внимание на фигурные скобки, появившиеся в формуле – отличительный признак формулы массива. Вводить их вручную с клавиатуры бесполезно – они автоматически появляются при нажатии Ctrl + Shift + Enter.

    Пример 2. Разрешите Вас. транспонировать?

    При работе с таблицами часто возникает необходимость поменять местами строки и столбцы, т.е. развернуть таблицу на бок, чтобы данные, которые раньше шли по строке, теперь располагались в столбцах и наоборот. В математике такая операция называется транспонированием. При помощи формулы массива и функции ТРАНСП (TRANSPOSE) это делается на раз.

    Допустим, имеем двумерный массив ячеек, который хотим транспонировать.

    • Выделяем диапазон ячеек для размещения транспонированной таблицы. Поскольку исходный массив ячеек был 8 строк на 2 столбца, то надо выделить диапазон пустых ячеек размером 2 строки на 8 столбцов.
    • вводим функцию транспонирования =ТРАНСП(
    • в качестве аргумента функции выделяем наш массив ячеек A1:B8

    жмем Ctrl + Shift + Enter и получаем “перевернутый массив” в качестве результата:

    Редактирование формулы массива

    Если формула массива расположена не в одной ячейке (как в Примере 1), а в нескольких ячейках (как в Примере 2), то Excel не позволит редактировать или удалить одну отдельно взятую формулу (например в ячейке D10) и выдаст предупреждающее сообщение Невозможно изменить часть массива.

    Для редактирования формулы массива необходимо выделить весь диапазон (A10:H11 в нашем случае) и изменить формулу в строке формул (или нажав F2). Затем необходимо повторить ввод измененной формулы массива, нажав сочетание клавиш Ctrl + Shift + Enter.

    Excel также не позволит свободно перемещать ячейки, входящие в формулу массива или добавлять новые строки-столбцы-ячейки в диапазон формулы массива (т.е. в диапазон A10:H11 в нашем случае)

    Пример 3. Таблица умножения

    Вспомните детство, школу, свою тетрадку по математике. На обороте тетради на обложке было что? Таблица умножения вот такого вида:

    При помощи формул массива она вся делается в одно движение:

    1. выделяем диапазон B2:K11
    2. вводим формулу =A2:A11*B1:K1
    3. жмем Ctrl + Shift + Enter, чтобы Excel воспринял ее как формулу массива

    и получаем результат:

    Пример 4. Выборочное суммирование

    Посмотрите как при помощи одной формулы массива красиво и легко выбираются данные по определенному товару и заказчику:

    В данном случае формула массива синхронно пробегает по всем элементам диапазонов C3:C21 и B3:B21, проверяя, совпадают ли они с заданными значениями из ячеек G4 и G5. Если совпадения нет, то результат равенства ноль, если совпадение есть, то единица. Таким образом суммы всех сделок, где заказчик не ANTON и товар не Boston Crab Meat умножаются на ноль и суммируются только нужные заказы.

    Читайте также:  Как в excel в примечание вставить фото

    Источник: www.planetaexcel.ru

    Работа с массивами функций в Excel

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

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

    Виды массивов функций Excel

    Массив – данные, объединенные в группу. В данном случае группой является массив функций в Excel. Любую таблицу, которую мы составим и заполним в Excel, можно назвать массивом. Пример:

    В зависимости от расположения элементов различают массивы:

    • одномерные (данные находятся в ОДНОЙ строке или в ОДНОМ столбце);
    • двумерные (НЕСКОЛЬКО строк и столбцов, матрица).

    Одномерные массивы бывают:

    • горизонтальными (данные – в строке);
    • вертикальными (данные – в столбце).

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

    Формула массива – позволяет обработать данные из этого массива. Она может возвращать одно значение либо давать в результате массив (набор) значений.

    С помощью формул массива реально:

    • подсчитать количество знаков в определенном диапазоне;
    • суммировать только те числа, которые соответствуют заданному условию;
    • суммировать все n-ные значения в определенном диапазоне.

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

    Синтаксис формулы массива

    Используем формулу массива с диапазоном ячеек и с отдельной ячейкой. В первом случае найдем промежуточные итоги для столбца «К оплате». Во втором – итоговую сумму коммунальных платежей.

    1. Выделяем диапазон Е3:Е8.
    2. В строку формул вводим следующую формулу: =C3:C8*D3:D8.
    3. Нажимаем одновременно клавиши: Ctrl + Shift + Enter. Промежуточные итоги посчитаны:

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

    Если попытаться изменить данные в какой-либо ячейке столбца «К оплате» – ничего не выйдет. Формула в массиве защищает значения диапазона от изменений. На экране появляется соответствующая запись:

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

    1. Выделяем ячейку Е9 (напротив «Итого»).
    2. Вводим формулу вида: =СУММ(C3:C8*D3:D8).
    3. Нажимаем сочетание клавиш: Ctrl + Shift + Enter. Результат:

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

    Аргументы для функции – одномерные массивы. Формула просматривает каждый из них по отдельности, совершает заданные пользователем операции и генерирует единый результат.

    Рассмотрим ее синтаксис:

    Функции работы с массивами Excel

    Предположим, в следующем месяце планируется увеличение коммунальных платежей на 10%. Если мы введем обычную формулу для итога =СУММ((C3:C8*D3:D8)+10%), то вряд ли получим ожидаемый результат. Нам нужно, чтобы каждый аргумент увеличился на 10%. Чтобы программа поняла это, мы используем функцию как массив.

    1. Посмотрим, как работает оператор «И» в функции массива . Нам нужно узнать, сколько мы платим за воду, горячую и холодную. Функция: . Итого – 346 руб.
    2. Функция «Сортировки» в формуле массива. Отсортируем суммы к оплате в порядке возрастания. Для списка отсортированных данных создадим диапазон. Выделим его. В строке формул вводим . Жмем сочетание Ctrl + Shift + Enter.
    3. Транспонированная матрица. Специальная функция Excel для работы с двумерными массивами. Функция «ТРАНСП» возвращает сразу несколько значений. Преобразует горизонтальную матрицу в вертикальную и наоборот. Выделяем диапазон ячеек, где количество строк = числу столбцов в таблице с исходными данными. А количество столбцов = числу строк в исходном массиве. Вводим формулу: . Получается «перевернутый» массив данных.
    4. Поиск среднего значения без учета нулей. Если мы воспользуемся стандартной функцией «СРЗНАЧ», то получим в результате «0». И это будет правильно. Поэтому вставляем в формулу дополнительное условие: 0;A1:A8))’ >. Получаем:

    Распространенная ошибка при работе с массивами функций – НЕ нажатие кодового сочетания «Ctrl + Shift + Enter» (никогда не забывайте эту комбинацию клавиш). Это самое главное, что нужно запомнить при обработке больших объемов информации. Правильно введенная функция выполняет сложнейшие задачи.

    Источник: exceltable.com

    Массивы в Excel пример использования

    Массивы в Excel пример использования

    Добрый день, уважаемые подписчики и читатели блога! Сегодня чуть больше поговорим о массивах и практике их применения в Excel.

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

    Как обычно мы действуем? Вводим формулу — количество * цену, плюсуем доставку, enter, протягиваем за маркер автозаполнения вниз, получаем результат. Способ классический, рабочий.

    Давайте выполним те же самые действия, только умножать и прибавлять будем не ячейки, а целые диапазоны. Предварительно выделив диапазон от F3 до F11.

    Первый нюанс, когда работаем с формулой массива нельзя нажимать Enter! Нажимаем Ctrl+Shift+Enter! Что это даст в итоге.

    К обычной формуле добавятся фигурные скобки в самом начале и конце. Это говорит о том, что применён массив. То есть — если таблица большая, проще выделить весь столбец и использовать массив, чтобы не протягивать маркером до конца таблицы.

    Комбинации формул массива и обычных формул

    Усложняем задачу — нужно подвести итог (СУММ) по всему столбцу «ИТОГО», высчитать максимальную сумму заказа и узнать среднюю сумму заказа без копеек.

    Аналогично воспользуемся функцией СУММ, только передадим ей опять массив в виде вычислений. Не забываем про Ctrl+Shift+Enter!

    Взглянем в строчку формул.

    Посмотрим, что в этому случае делает программа Excel — пройдём по шагам вычислений. На вкладке «Формулы» нажмём кнопку «Вычислить формулу».

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

    Аналогично подсчитаем максимальную стоимость покупки. Используем формулу МАКС и передадим ей массив.

    Настала очередь средней суммы покупки. Для округления будем использовать ЦЕЛОЕ (чтобы результат был без копеек) и СРЗНАЧ.

    Готово. В этом случае даже получилось «экономнее» нежели в обычных формулах, так как для промежуточного итога нет своего столбца!

    Как видно их примеров — у массивов есть свои плюсы и свои минусы. Не пробуйте удалять массив (если он содержится в диапазоне ячеек). Изменить только одну ячейку массива нельзя! Плюс — улучшаем безопасность, минус — приходится переделывать весь массив, если что-то пошло не так.

    Если остались вопросы — посмотрите новое видео. Ну и по традиции — вам может понравиться статья про функцию ПЛТ. Всем удачи!

    Источник: pcandlife.ru