Tooprogram.ru

Компьютерный справочник
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как использовать массивы в excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Массивы и формулы массива в Excel.

Для начала следует понимать, что такое массив и какие массивы бывают.

Массивом называют группу данных объединенных (сгруппированных) в одну структуру (группу)

В «Excel» массивы подразделяют на три типа в зависимости от структуры расположения данных в таблице:

Горизонтальный одномерный (линейный) массив – массив, в котором данные расположены горизонтально в одну строку.

Вертикальный одномерный (линейный) массив – массив, в котором данные расположены вертикально в один столбец.

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

Формулы (функции) массивов.

Для работы с массивами в «Эксель» предусмотрены специальные формулы – формулы массивов.

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

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

Рассмотрим в качестве примера смету состоящую из столбцов «Количество», «Трудозатраты на единицу», «Стоимость одного чел часа».

Смета на проведение работ в Excel.
Вид работКоличествоТрудозатраты на единицуСтоимость одного чел часа
Прокладка кабеля, м250,5123
Укладка тротуарной плитки, м²450,125244
Покраска металлических поверхностей, м²40,2233
Монтаж дверного замка, шт.120,840
Итого:3480,4

Чтобы получить полную стоимость работ следует перемножить количество, стоимость и трудозатраты на единицы для каждого вида работ, а потом сложить затраты на каждый вид работ. Сделать это можно в несколько действий по порядку, а можно написать одну единственную функцию массива: и нажать сочетание клавиш «Ctrl + Shift + Enter», чтобы «Excel» распознал формулу массив.

Рассмотрим функцию массива ТРАНСП(). Эта функция полностью относится к функциям массива и производить транспонирование выделенного массива, то есть меняет местами столбцы и строки (переворачивает таблицу). Чтобы использовать данную функцию следует:

  • Выделить диапазон, в который планируете транспонировать таблицу (если в исходной таблице четыре столбца и шесть строк, то выделяем шесть столбцов и четыре строки);
  • В строке функций пишем =ТРАНСП();
  • В скобках указать массив, который вы хотите транспонировать (перевернуть) и нажать клавишу «ENTER».
Читать еще:  Vba excel поле со списком

Формулы массива в 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. Некоторые примеры использования формул массива

    Если ранее вы не сталкивались с формулами массива, рекомендую прочитать:

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

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

    Читать еще:  Блокировка видео рекламы в браузере

    Если вы уже постигли азы, предлагаю вам продолжить знакомство с формулами массива вместе с Джоном Уокенбахом и его книгой MS Excel 2007. Библия пользователя. – М.: Издательский дом «Вильямс», 2008. – 816 с.

    Cкачать заметку в формате Word, примеры в формате Excel

    Создание массивов на основе значений ячеек диапазона

    На рис. 1 показан рабочий лист, содержащий данные в диапазоне A1:C4. Диапазон D8:F11 содержит массив, созданный на основе этих данных с помощью формулы

    Рис. 1. Создание массива на основе значений ячеек диапазона

    Массив в диапазоне D8:F11 связан со значениями диапазона A1:C4. Если изменить какое-либо значение в последнем диапазоне, то автоматически изменится соответствующее значение в массиве D8:F11.

    Создание массива констант на основе значений диапазона ячеек

    В предыдущем примере формула массива в ячейках D8:F11, по существу, являлась ссылкой на диапазон A1:C4. Чтобы «разорвать» эту связь и создать массив констант, выполните ряд действий.

    1. Выделите диапазон, содержащий формулу массива (в этом примере – D8:F11).
    2. Нажмите клавишу , чтобы перейти в режим редактирования формулы.
    3. Нажмите клавишу , чтобы преобразовать ссылки на ячейки в соответствующие значения.
    4. Нажмите комбинацию клавиш .

    Теперь в диапазоне D8:F11 находится следующий массив: <1; " кот " ;3:4;5; " собака " :7;89;9,12: " обезьяна " ;11;44>. На рис. 2 показано, как этот массив выглядит в строке формул.

    Рис. 2. После нажатия клавиши в строке формул отобразится массив констант

    Выполнение операций над массивами

    Следующая формула массива создает прямоугольный массив и умножает каждый его элемент на 2: <=<1;2;3;4:5;6;7;8:9;10;11;12>*2> (рис. 3)

    Рис. 3. Результат умножения массива на 2

    Следующая формула умножает каждый элемент массива на самого себя: <=<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>. Если массив хранится в диапазоне ячеек (допустим, А1:С4), то подобная формула возвратит квадрат каждого элемента этого массива: <=А1:С4^2>(рис. 4).

    Рис. 4. Возведение значений массива в квадрат

    Применение функций к массивам

    В операциях над массивами можно использовать функции. Следующая формула массива, которую нужно ввести в вертикальный диапазон, состоящий из 10 ячеек, вычисляет квадратные корни каждого элемента массива: <=КОРЕНЬ(<1:2:3:4:5:6:7:8:9:10))>. Если массив хранится в диапазоне ячеек (например, А1:А10), подобная формула выполнит эти же вычисления для каждого элемента массива: <=КОРЕНЬ(А1:А10)>(рис. 5).

    Рис. 5. Извлечение квадратного корня из элементов массива

    Транспонирование массивов

    При транспонировании массива его строки становятся столбцами, а столбцы — строками. Если массив одномерный, то при транспонировании горизонтальный массив становится вертикальным и наоборот. Для выполнения операции транспонирования используется функция Excel ТРАНСП. Допустим, имеется одномерный горизонтальный массив <1; 2; 3; 4; 5>, расположенный в диапазоне А1:Е1. С помощью функции ТРАНСП можно преобразовать его в вертикальный массив. Для этого выделите вертикальный диапазон, состоящий из пяти ячеек, введите формулу =ТРАНСП(А1:Е1) и нажмите комбинацию клавиш (рис. 6).

    Рис. 6. Транспонирование одномерного массива

    Транспонирование двухмерных массивов выполняется подобным образом. На рис. 7 показан двухмерный массив в диапазоне A1:D3. Формула <=TPAHCП(A1:D3)>создает в диапазоне F1:H4 транспонированный массив.

    Рис. 7. Транспонирование двумерного массива

    Генерирование последовательности натуральных чисел

    С помощью формул массивов легко генерировать последовательности натуральных чисел. Для этого идеально подходит функция СТРОКА, возвращающая номер строки. Рассмотрим формулу массива, введенную в вертикальный диапазон, состоящий из 12 ячеек: <=СТРОКА(1:12)>. Эта формула возвращает массив из 12 элементов, содержащий числа от 1 до 12 (рис. 8). Отметим, что данная формула возвращает правильный результат, независимо от того, где расположен диапазон, в который вводится эта формула.

    Рис. 8. Последовательности натуральных чисел на основе функции СТРОКА

    Если вы поэкспериментируете с приведенной формулой, то обнаружите присущий ей недостаток: при добавлении новой строки выше диапазона, где расположена эта формула, Excel изменит номера строк, и формула автоматически преобразуется в формулу, возвращающую значения от 2 до 13: <=СТРОКА(2:13)>(рис. 9).

    Рис. 9. Добавление строки над массивом изменяет ряд чисел

    Приведем формулу массива, которая лишена этого недостатка: <=СТРОКА(ДВССЫЛ( " 1:12 " ))>. Эта формула использует функцию ДВССЫЛ, аргументом которой является текстовая строка. Excel не изменяет этот аргумент при вставке или удалении строк на рабочем листе, поэтому данная формула всегда возвращает правильный результат, состоящий из чисел от 1 до 12 (рис. 10).

    Рис. 10. Последовательность натуральных чисел на основе функции ДВССЫЛ не меняется при добавлении строк над массивом

    Функции, возвращающие массив. Результатом выполнения некоторых функций Excel является массив. Чтобы такие функции возвращали правильный результат, выделите диапазон, введите формулу, содержащую одну из этих функций, как формулу массива. Вот несколько функций, которые возвращают массив: ПРЕДСКАЗ, ЧАСТОТА, РОСТ, ЛИНЕЙН, ЛГРФПРИБЛ, МОБР, МУМНОЖ и ТЕНДЕНЦИЯ. Дополнительную информацию об этих функциях можно найти в справочной системе Excel.

    Читать еще:  Создание именованного диапазона в excel

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

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

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

    Если необходимо подсчитать общее количество символов в определенном диапазоне, то традиционный метод выполнения этой задачи состоит в том, чтобы найти количество символов в каждой ячейке диапазона, например, с помощью формулы =ДЛСТР(А1), и затем суммировать эти значения (рис. 11). Формула массива <=СУММ(ДЛСТР(А1:А10))>выполняет эти вычисления без промежуточных формул. Эта формула с помощью функции ДЛСТР создает виртуальный массив в памяти компьютера, содержащий количество символов каждой ячейки диапазона, а затем суммирует элементы виртуального массива.

    Рис. 11. Одна формула массива заменяет целый ряд отдельных формул

    Суммирование трех наименьших значений диапазона

    Предположим, что имеется диапазон чисел, названный Данные, и вы хотите узнать сумму трех наименьших чисел этого диапазона. Формула массива решает эту задачу: <=СУММ(НАИМЕНЬШИЙ(Данные;<1;2;3>))> (рис. 12). Здесь в качестве второго аргумента в функции НАИМЕНЬШИЙ используется массив констант. Это вынуждает Excel сгенерировать виртуальный массив, содержащий три наименьших значения из диапазона Данные. Затем к этому виртуальному массиву применяется функция СУММ, которая суммирует его элементы и возвращает искомый результат.

    Рис. 12. Формула массива возвращает сумму трех наименьших значений поименованного диапазона Данные (A1:А10)

    Подсчет количества ячеек, содержащих текст

    В следующей формуле функция ЕСЛИ используется для проверки содержимого каждой ячейки диапазона. Затем создается виртуальный массив (того же размера, что и исходный), в котором содержатся числа 1 (если в ячейке находится текст) и 0 (в противном случае). Новый массив передается в функцию СУММ, которая возвращает сумму элементов массива. В итоге получаем количество ячеек, содержащих текст: <=СУММ(ЕСЛИ(ЕТЕКСТ(А1:D5);1;0))>. На рис. 13 показан пример использования этой формулы в ячейке С8. В результате выполнения функции ЕСЛИ создается виртуальным массив <0;1;1;1:1;0;0;0:1;0;0;0:1;0;0;0:1;0;0;0>. Обратите внимание на то, что виртуальный массив состоит из пяти строк, содержащих по четыре элемента (т.е. тот же размер, что и исходный массив). Функция СУММ суммирует значения этого виртуального массива.

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

    Следующая формула выполняет туже работу, но записана в более компактном виде <=СУММ(ЕТЕКСТ(A1:D5)*1)>. Здесь вместо функции ЕСЛИ используется тот факт, что ИСТИНА*1=1, а ЛОЖЬ*1=0

    Исключение промежуточных формул

    Одно из достоинств формул массивов заключается в том, что они позволяют исключать промежуточные формулы (и соответственно результаты промежуточных вычислений) с рабочих листов. Это делает таблицы на рабочих листах более компактными и понятными. На рис. 14 показан рабочий лист, содержащий баллы студентов до и после выполнения тестов. В столбце D содержатся формулы, вычисляющие разность между этими баллами, в ячейке D12 – формула, вычисляющая среднее значение этих разностей: =CPЗHAЧ(D2:D11).

    Рис. 14. Вычисление среднего с помощью промежуточных формул и с помощью одной формулы массива

    С помощью следующей формулы массива, записанной в ячейке С15, можно исключить промежуточные вычисления в столбце D: <=СРЗНАЧ(С2:С11-В2:В11)>. В этой формуле используются два массива, содержащиеся в диапазонах С2:С11 и В2:В11. Формула создает виртуальный массив, состоящий из разностей значений этих двух массивов. Этот виртуальный массив хранится в памяти компьютера, а не в рабочем листе Excel. Функция СРЗНАЧ использует этот виртуальный массив в качестве своего аргумента и возвращает среднее его элементов. Виртуальный массив имеет вид <27;18;18;10;3;8;0;17;8;-8>. Поэтому в данном случае предыдущая формула принимает вид =СРЗНАЧ(<27;18;18;10;3;8;0;17;8;-8>)

    Аналогично создаются и работают формулы массивов, вычисляющие различные характеристики диапазонов данных. Например, следующая формула находит максимальное число среди разностей значений диапазонов С2:С11 и В2:В11: <=МАКС(C2:C11-B2:B11)>. Эта формула вернет число 27. А эта формула <=МИН(C2:C11-B2:B11)>возвращает наименьшее число разностей значений двух диапазонов. Формула вернет число -8.

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

    Если в формулах используются ссылки на диапазоны, то их можно заменить на массивы констант. Это очень удобно, если значения в диапазонах, на которые указывают ссылки, не будут изменяться. [1]

    На рис. 15 представлена таблица, в которой производится поиск названия заданного числа. Например, если 9 – искомое значение, то формула вернет слово Девять. В ячейку С1 введена следующая формула: =ВПР(В1;D1:Е10;2;ЛОЖЬ). Вместо ссылки на таблицу можно использовать двухмерный массив. Формула, приведенная ниже, работает аналогично предыдущей, но не использует таблицу, которая находится в диапазоне D1:E10.

    Рис. 15. Таблицу, в которой производится поиск, можно заменить массивом констант

    [1] К сожалению, в функциях, работающих с базами данных (например, БДСУММ), нельзя заменить ссылку на диапазон, содержащий критерии, массивом констант.

    Ссылка на основную публикацию
    Adblock
    detector