Меню

Пример как работать с формулой массива в таблице Excel



Массивы в гугл таблицах (GS14)

Привет, дорогие подписчики и читатели Пикабу.
Продолжая тему мануала по гугл таблицам решил сегодня написать пост про массивы.
Сразу скажу, что я не представляю как эта история реализована в Excel, поэтому не смогу сравнить синтаксис и область применения, да и большая часть формул, которые я покажу — не будут работать в детище чипирователя великой Руси.
Что такое массив (array)?
Прежде всего это объект. Объект, который содержит в себе набор данных. Массивы в ГТ могут быть одномерными (строка или столбец) и многомерными — целиковая таблица (дефакто это одномерный массив состоящий из одномерных массивов).
Сейчас я покажу как они записываются, но нужно отметить нюанс. Для русской и английской версии синтаксис будет отличаться. Я буду использовать английский вариант, т.к. он удобнее как мне кажется.

Формула типа = <1;2>будет являться «вертикальным» массивом, «верхний» элемент которого равен 1, а «нижний» — 2.
Формула типа = <1\2>будет являться «горизонтальным» массивом», «левый» элемент — 1, «правый» — 2. В английской версии эта формула записывается как =<1,2>.
Формула типа = < < 1;2>, <3; 4>> будет выглядеть так:

В своем посте про switch case я показывал, как такого рода массивы могут быть использованы в функции ВПР (VLOOKUP). Ссылка: Swtich case в гугл таблицах или не самый очевидный ВПР (GS8)

И это далеко не единственный способ задать массив.
Второй вариант — arrayformula(). Эта функция не имеет русского эквивалента. arrayformula повторяет формулу, которая в ней записана для каждого элемента массива. На выходе, как правило, она также дает массив. Пример:

Что характерно — в случае arrayformula и еще ряда функция (таких как ВПР (второй аргумент), filter, счётеслимн и т.д.) они принимают аргументы в качестве массива, при этом не требуют от пользователя явной записи в виде массива (через фигурные скобки). Даже простая функция СУММ принимает на вход именно массив. Зная это — мы можем делать вложенные функции.
Приведу повторно пример из поста: Фильтры и ВПРы в ГТ (GS2)
У нас есть следующая таблица:

Давайте с помощью фильтр достанем из нее все слова, внутри которых есть бука «е».
Тут нам поможет функция REGEXMATCH() — соответствие текста определенному регулярному выражению. О самих регулярках мы поговорим в будущем. Пока нам нужно только находить букву Е. Сама функция REGEXMATCH() возвращает 1 или 0, т.е. входит регулярка в текст или не входит. Наглядно это выглядит так:

Н.Б. Формула прописана только в ячейке H1. Благодаря формуле массива она сама протянулась вдоль диапазона G1:G10.
Теперь поместим формулу из ячейки H1 в самый обычный фильтр.

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

Как мы используем arrayformula в работе?
Обычная история — есть пополняемый реестр, в котором нужно постоянно протягивать формулы.
Например, заполняемая форма. Положим у нас есть форма, которую заполняют сотрудники при тратах корп денег и нам нужно по логину почты сотрудника для каждой записи формы протянуть его ФИО. Делается это с помощью обычного ВПР. В excel нам помогла бы умная таблица, которая сама протягивает за нас формулы. Здесь такого нет. Давайте чуть усложним кейс и положим, что мы не знаем сколько сотрудников будут заполнять, а делать справочник заранее нам долго. Тогда нам понадобится список всех НОВЫХ логинов, которых мы еще не внесли в справочник. Приступим.
Первым делом сделаем имитацию формы. Вот такая получилась заготовка:

Первым делом прописываем фильтр, который будет показывать нам новые логины. Нам потребуется комбинация isna(vlookup()). Детально про нее я рассказывал в посте: Фильтры и ВПРы в ГТ (GS2)
Получилось следующим образом:

Внесем один из логинов в справочник и пропишем в столбец М формулу массива, которая будет автоматически для всех строк таблицы подтягивать ФИО.

Косметическим исправлением будет добавление в M2 функции iferror, которая будет убирать записи #N/A.

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

Какие могут быть ошибки связанные с фильтрами или массивами?
Первое — синтаксис. ГТ автоматически закроет для вас обычные скобки, но за фигурными — нужно следить самостоятельно. Если получаете ошибку типа «Formula parse error» — с большой вероятность вы налажали с фигурными скобками.
Второе — если использовать arrayformula по беконечному диапазону (типа А:А), при это расположить ее в ячейке B2 — таблица будет пытаться постоянно достроить саму себя, т.к. формула массива всегда будет обработать на одну строку больше чем есть. Это бесконечный цикл. Нужно удалять формулу, чистить лишние строки и переписывать.
Есть ряд формул, которые очень тяжело ложатся в массивы, т.к. не подразумевают перебор, а хавают в себя все что дают. Например функция join().
Третье — записи внутри массивов нельзя править руками. ЕСли в таблице сверху я попробую вручную внести данные в ячейку М4, то будет следующее:

Источник

Пример как работать с формулой массива в таблице Excel

Как узнать, является ли данная формула формулой массива? Что вообще она означает?

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

Примеры формул массива и отличие от обычных формул в Excel

Некоторые функции Excelя по умолчанию в качестве аргумента принимают диапазон ячеек (массив) и в результате возвращают одно значение. Отличными примерами являются функции СУММ, СЧЕТЕСЛИ, СРЗНАЧ и т.д. Для этих функций не имеет никакого значения, вводите ли вы их как функции массива или нет. Они и так обрабатывают таблицы, и найдут выход (сработают правильно) из любой ситуации. Вот такие маленькие Excel-евские приспособленцы.

К счастью, существуют другие функции, которые работают совершенно иначе, т.е. в зависимости от вашего решения относительно их принадлежности к «функциям массива» (иногда они вообще не хотят работать). Прекрасным примером является функция ЕСЛИ.

Когда формула является формулой массива, а когда обычной?

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

<23;-32;15;7>– это синтаксис массива значений в Excel. Он может быть использован в аргументах функций.

Диапазон ячеек A1:A4 – так же является массивом значений в Excel. Естественно так же используется в аргументах функций. Например сравним результаты вычислений двух формул: =СУММ(A1:A4) и =СУММ(<23;-32;15;7>) – они идентичны:

Массив логических значений.

Визуально формула массива находится так же внутри фигурных скобок, но они не должны быть введены вручную, а только лишь при помощи комбинации клавиш CTRL+SHIFT+Enter. Если ввести вручную фигурные скобки, то формула не будет выполнятся в массиве – это будет синтаксическая ошибка в Excel.

Формула массива (введенная с помощью сочитания CTRL+SHIFT+Enter) будет использоваться везде, где вы хотите, чтобы функция, которая обычно работает с отдельными значениями (ячейками), внезапно повела себя иначе и приняла в качестве аргумента и вернула в качестве результата массив значений (таблицу). Вернемся к уже упомянутой функции ЕСЛИ. В качестве аргумента она принимает логическое значение ИСТИНА или ЛОЖЬ. В классической форме:

ЕСЛИ.

Если значение в ячейке A1 больше нуля, в качестве аргумента функция получит значение ИСТИНА и в качестве результата вернет текстовую строку «больше». Однако, если бы вы хотели проверить несколько ячеек сразу и передать результат такой проверки другой функции, вы должны были бы использовать вышеуказанную формулу как формулу массива. Для этого при вводе нажмем сочитание клавиш CTRL+SHIFT+Enter, а не как обычно (просто Enter):

Читайте также:  Как купить теннисную ракетку и правильно подобрать

Сочетание клавиш формулы массива.

Формула массива в фигурных скобках.

В качестве аргумента функция принимает целый диапазон $A$1:$A$4. В результате проверки каждой ячейки диапазона в памяти компьютера создается таблица значений в массиве. Схематически таблицу можно отобразить так:

Таблица в памяти компьютера.

А так выглядят эти значения в массиве:

Например, чтобы прочитать этот массив и получить второе значение (сделать выборку значений) воспользуемся функцией:

ИНДЕКС.

Тоже самое что и:

Выборка из массива значений.

Затем создается другая таблица, значения которой зависят непосредственно от значений в первой таблице. Если элемент в первом массиве имеет значение ИСТИНА, во втором массиве он примет значение «больше». Если он имеет значение ЛОЖЬ, элемент во второй таблице примет значение «меньше». После этой операции первая таблица удаляется из памяти компьютера, и в конечном счете, функция возвращает массив <"больше", "меньше", "больше", "больше">. Схематически вторую таблицу можно отобразить так:

Так же ее можно прочитать функцией:

Массив значений.

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

Все значения массива.

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

Примеры как использовать формулу массива в Excel

Это все хорошо, но возникают некоторые вопросы: «Зачем же нужна формула массива?» или «Как или где использовать формулу в массиве?», «Чем она лучше обычной формулы?».

Разумеется, массив, возвращаемый функцией ЕСЛИ, может передаваться далее на «обработку» в качестве аргумента для другой функции.

Пример. Представим, что вы хотели бы найти сумму ячеек B7:B10, но только тех, которые имеют значение больше нуля. Конечно же, вы можете использовать функцию СУММЕСЛИ, однако в нашем примере мы хотим сделать это только с помощью формулы массива. Суммируя значения ячеек нашего диапазона, необходимо будет как-то избавиться от значения «-32». Функции СУММ необходимо передать массив, в котором содержатся только значения больше нуля. Везде там, где значение меньше нуля, мы заменяем его на ноль, что, конечно же, не повлияет на результат. Как вы уже знаете, временную таблицу с соответствующими значениями вы можете получить, используя функцию ЕСЛИ. В конечном итоге соответствующая формула будет выглядеть так:

0;$A$1:$A$4;0))’ > СУММ.

Вводим формулу и не забываем для подтверждения ввода нажать комбинацию клавиш CTRL+SHIFT+Enter. В результате проверки каждой ячейки диапазона $A$1:$A$4 (является ли значение больше нуля) в памяти компьютера создается массив <ИСТИНА; ЛОЖЬ; ИСТИНА; ИСТИНА>. Затем создается очередная таблица. Если элемент в первом массиве имеет значение ИСТИНА, то во второй таблице будет отображаться значение из соответствующей ячейки. Если он имеет значение ЛОЖЬ, то элемент во второй таблице примет значение 0. После этой операции первая таблица удаляется из памяти компьютера, и в конечном итоге функция ЕСЛИ возвращает массив <23; 0; 15; 7>. Затем эта таблица передается в качестве аргумента функции =СУММ(<23; 0; 15; 7>), которая, согласно своему предназначению, возвращает сумму всех элементов в таблице. В нашем примере сумма равна 45. В завершении, посмотрите, что произойдет, если вы скажете Excelю обработать приведенную выше формулу не как формулу массива.

не массив без фигурных скобок.

Ни одна из описанных выше таблиц, в этом случае, не будет создана. Только одна ячейка диапазона будет проверена (ячейка в той же строке, в которой находится функция). В нашем случае 15>0 значит, как первый аргумент функция ЕСЛИ получит логическое значение ИСТИНА. Затем ВЕСЬ диапазон A1:A4 будет передан функции СУММ, и в результате функция возвращает значение равное 13 (23-32+15+7). Если бы в ячейке вместо значения 15 было число меньше нуля, функция ЕСЛИ в качестве аргумента получила бы значение ЛОЖЬ, и, следовательно, только значение нуля было бы передано функции СУММ. Наша функция СУММ в результате также вернет значение равное нулю.

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

При нажатии клавиш CTRL+SHIFT+Enter для подтверждения ввода в строке формул будут отображены фигурные скобки по краям. Значит данная формула выполняется в массиве. Но что если еще на этапе создания неизвестно какой тип формул следует применять?

Правильное «распознавание», когда следует нажимать CTRL+SHIFT+Enter, а когда просто Enter полностью зависит от понимания того, как работают массивы в формулах. Когда вы это поймете, сможете сказать, что конкретную формулу следует вводить (подтверждать) сочетанием клавиш – CTRL+SHIFT+Enter.

Конечно же, не подтвержденная, а просто как формула также может возвращать КАКОЙ-ТО результат (в чем вы могли только что убедиться сами). Однако, если вы сможете прочитать формулу и понять механизм, то вы заметите, что такой результат является ОШИБОЧНЫМ. И поэтому для правильной работы формулы вам необходимо ее подтвердить «Ctr+Shift+Enter». Как и все, понимание и использование формул массива требует практики. Тем не менее, стоит некоторое время посвятить тому, чтобы во всем разобраться. Потому что формулы массива позволяют решить многие проблемы, которые на первый взгляд могут казаться неразрешимыми.

Примеры вычислений и анализа формул массива

Каким образом можно просматривать и проверять значения промежуточных результатов расчета, например, содержание массивов, созданных в памяти компьютера и используемых для выполнения последующих действий? Ничего сложного! Пример 1:

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

ссылка на диапазон ячеек.

Нажмите клавишу F9 (или «Пересчет» в правом верхнем углу меню «Формулы»), и вы получите (в строке формулы) значения аргументов, которые используются для вычислений, как показано ниже:

Содержимое массива.

— запись с использованием двоеточий означает, что мы имеем дело с элементами вертикального (столбикового) массива, элементы горизонтального (строкового) разделены стандартным символом — «;» (точкой с запятой).

Пример 2: Снова перейдите на ячейку с формулой массива, но на этот раз выделите первый аргумент функции целиком вместе со знаком сравнения «>» и значением критерия – «0»).

вместе с оператором сравнения и аргументом.

Нажмите кнопку F9, и вы получите массив результатов вычислений, как показано ниже:

массив результатов вычислений.

То есть, созданный в памяти компьютера массив:

Пример 3: Выделите ячейку формулой массива где в функцию СУММ вложена функция ЕСЛИ. Затем в строке формул выберите весь аргумент функции СУММ (вместе с функцией ЕСЛИ):

аргумент целиком с функцией и ссылками.

Нажмите клавишу F9 и вы получите массив итоговых результатов вычисления, которые используются для суммирования, как показано ниже:

Массив итоговых результатов вычисления.

То есть, созданный в памяти компьютера массив:

Пример 4: Просто перейдите на ячейку с формулой B1 и выберите инструмент «ФОРМУЛЫ»-«Зависимости формул»-«Вычислить формулу»

Вычислить формулу.

После чего нажмите на кнопку «Вычислить»:

Кнопка Вычислить.

В результате ссылка на диапазон ячеек в аргументе вложенной функции ЕСЛИ разложилась на массив значений. Снова нажмите на кнопку «Вычислить»:

Готово.

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

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

Источник

Массивы с помощью таблицы

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

Что такое массив

Представление таблицы в языках программирования называется массивом. Вот, например, таблица, содержащая среднемесячные значения температуры в Перми в 2000 году:

Месяц 1 2 3 4 5 6 7 8 9 10 11 12
Температура -21 -18 -7,5 5,6 10 18 22,2 24 17 5,4 -7 -18

Такую таблицу называют линейной. Она представляет собой последовательность упорядоченных чисел. Для обозначения этих чисел используют индексированные имена. Например, через Т[1] обозначается температура в январе (первом месяце года), Т[5] — температура в мае и т. д.

В программировании линейная таблица называется одномерным массивом. В нашем примере Т — это имя массива. Элементы массива пронумерованы. Порядковый номер элемента называется его индексом. Каждый элемент массива обозначается индексированным именем в следующей форме:

Читайте также:  Франция в первой половине XIX века от Реставрации к Империи

Индекс записывается в квадратных скобках: Т[2], Т[10], Т[12]. Индексы могут представляться не только в виде констант, но и в виде целых переменных и даже выражений целого типа: T[i], T[k], T[i+k], T[2*k]. Важно следить, чтобы значения индексов не выходили за допустимые границы. В примере с температурами они должны лежать в диапазоне от 1 до 12.

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

Массив — это пронумерованная конечная последовательность однотипных величин.

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

Описание и ввод значений в массив на Алгоритмическом языке

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

алг Ввод массива, вариант 1
вещ таб Т[1:12]
цел I
нач I:=1
пока I

Цикл с параметром в АЯ

В первом варианте алгоритма используется уже знакомая вам алгоритмическая структура цикла с предусловием. Переменная I играет роль параметра цикла, изменяющегося от 1 до 12 с шагом 1. Внутри цикла она используется в качестве индекса в обозначении элементов цикла: Т[I].

Ввод организован в режиме диалога. Вы уже знаете, что это обязательное условие дружественности интерфейса программы. Перед вводом каждого очередного элемента таблицы на экран будет выводиться его имя. Это результат выполнения команды вывод «Т[«, I , «]=». После этого программист должен ввести с клавиатуры соответствующее число (команда ввод Т[I]):

Во втором варианте используется алгоритмическая структура, которая называется «цикл с параметром». Ее общая форма такая:

для от до шаг повторять
нц

кц

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

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

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

Эту задачу легко решить с помощью электронных таблиц. На рис. 3.13 показана такая таблица. В ячейки В2:В13 заносятся значения температур. В ячейку В14 помещается формула: =СРЗНАЧ(В2:В13). Результат вы видите в ячейке В14.

A B
1 Месяц Температура
2 1 -21
3 2 -18
4 3 -7,5
5 4 5,6
6 5 10
7 6 18
8 7 22,2
9 8 24
10 9 17
11 10 5,4
12 11 -7
13 12 -18
14 Среднее: 2,56

Табличный процессор — это программа, составленная программистами на некотором языке программирования. Вот мы и разберемся, как программируется вычисление среднего значения числового массива, реализованное в функции СРЗНАЧ.

Запишем алгоритм в полном виде (с вводом, вычислениями и выводом), используя в нем для организации циклов структуру цикла с параметром.

алг Средняя температура
вещ таб Т[1:12]
цел I, вещ Tsred
нач
<Цикл ввода>
для I от 1 до 12 шаг 1 повторять
нц
вывод «Т[I», I, «]=»
ввод Т[I]
кц
<Цикл суммирования>
Tsred:=0
для I от 1 до 12 шаг 1 повторять
нц
Tsred:=Tsred+T[I]
кц
<Вычисление среднего>
Tsred:=Tsred/12
вывод(«Среднегодовая температура=», Tsred
кон

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

Tsred=(T[1] + T Г[2] + T [3] + . + Т[12])/12.

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

1. Что такое массив?

2. Самостоятельно придумайте примеры данных, которые можно организовать в виде массива. В каждом примере отметьте: каким именем можно обозначить массив, как пронумеровать его элементы, какой тип будет иметь массив? Опишите массивы по правилам Алгоритмического языка.

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

4. Вы посетили магазин и купили 10 видов товара. В таблицу Т[1:10] вы записали количество купленного товара каждого вида. В таблицу С[1:10] записали цены единиц каждого вида товара соответственно. Составьте алгоритм вычисления общей стоимости всех покупок.

Источник

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Разрешите Вас… транспонировать?

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

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

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

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

Функция СТРОКА

Определяет и возвращает номер строки указанной ссылкой ячейки.

Синтаксис: =СТРОКА([ссылка]), где аргумент «ссылка» не является обязательным. Если он опущен, но возвращается номер текущей строки.

=СТРОКА(D4) – результат 4.
=СТРОКА() – функция вернет номер строки, в которой она расположена.

Функция СТОЛБЕЦ

Возвращает номер столбца ячейки, указанной ссылкой.

Синтаксис: =СТОЛБЕЦ([ссылка]), где «ссылка» не обязательный аргумент. По умолчанию возвращается номер текущего столбца.

=СТОЛБЕЦ(C4) – формула вернет значение 3.
=СТОЛБЕЦ() – функция возвращает номер столбца, в котором расположена.

Функция АДРЕС

Возвращает текст, представляющий адрес ячейки, заданной номерами строки и столбца.

Синтаксис: =АДРЕС(строка; столбец; [тип_закрепления]; [стиль_ссылки]; [имя_листа]), где:

  • Строка – обязательный аргумент. Число, представляющая номер строки, для которой необходимо вернуть адрес;
  • Столбец – обязательный аргумент. Число, представляющее номер столбца целевой ячейки.
  • тип_закрепления – необязательный аргумент. Число от 1 до 4, обозначающее закрепление индексов ссылки:
    • 1 – значение по умолчанию, когда закреплены все индексы;
    • 2 – закрепление индекса строки;
    • 3 – закрепление индекса столбца;
    • 4 – адрес без закреплений.
  • стиль_ссылки – необязательный аргумент. Логическое значение:
    • ИСТИНА – формат ссылок «A1»;
    • ЛОЖЬ – формат ссылок «R1C1».
  • имя_листа – необязательный аргумент. Строка, представляющая имя листа, который необходимо добавить к тексту адреса ячейки.
Читайте также:  Какой должен быть уклон канализационной трубы на 1 метр

=АДРЕС(1;1)>=АДРЕС(1;1;4)>=АДРЕС(1;1;4;ЛОЖЬ)>=АДРЕС(1;1;4;ЛОЖЬ;»Лист1″)>

Функция ДВССЫЛ

Преобразует адрес ссылки, заданный текстовой строкой, в ссылку на данный адрес.

Синтаксис: =ДВССЫЛ(адрес_ссылки; [стиль_ссылки]), где

  • адрес_ссылки – обязательный аргумент. Строка, представляющая адрес ссылки на ячейку или диапазон. Например, “C3”, “R3C3” или “D8:D9”.
  • стиль_ссылки – необязательный аргумент. Логическое значение, определяющее стиль ссылки:
    • ИСТИНА – стиль A1. Является значением по умолчанию;
    • ЛОЖЬ – стиль R1C1.

=ДВССЫЛ(“a3”) – возвращает ссылку на ячейку A3.
=ДВССЫЛ(“r3c3”) – вернет ошибку #ССЫЛКА!, так как текст для ссылки в формате R1C1, а второй аргумент имеет значение по умолчанию.
=ДВССЫЛ(“r3c3”; ЛОЖЬ) – возвращает ссылку на ячейку C3.
=ДВССЫЛ(АДРЕС(СТРОКА(C3);СТОЛБЕЦ(C3))) – функция вернет аналогичный предыдущему примеру результат.
Вложение функции ДВССЫЛ со ссылкой на диапазон:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Описание категории «Ссылки и массивы»

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

Ярким представителем функций данной категории является самая популярная функция ВПР, которая позволяет найти значение из ячейки диапазона, выполнив поиск в первом столбце этого же диапазона

Принцип работы функции ВПР из категории «Ссылки и массивы»

Функция ВПР (VLOOKUP) ищет значение в крайнем левом столбце таблицы и возвращает значение ячейки, находящейся в указанном столбце той же строки.

    Вставляем функцию ВПР:

  • Функция ВПР ищет значение ID (104) в крайнем левом столбце диапазона $E$4:$G$7 и возвращает значение из третьего столбца той же строки (так как третий аргумент функции имеет значение 3).
  • Четвёртый аргумент функции равен ЛОЖЬ (FALSE) – это значит, что либо будет найдено точное совпадение, либо будет показано сообщение об ошибке #Н/Д (#N/A).
  • Потащите мышью, чтобы скопировать функцию ВПР из ячейки B2 вниз по столбцу до ячейки B11.Пояснение: Когда мы копируем функцию ВПР вниз, абсолютная ссылка $E$4:$G$7 остаётся неизменной, в то время как относительная ссылка A2 изменяется на A3, A4, A5 и так далее.
  • Пример применения формулы массива

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

    Как бы мы решали данную задачу стандартным образом?

    Во-первых, мы бы получили итоговую сумму по каждому товару, перемножив количество товара на его цену.

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

    Ту же самую задачу можно решить с помощью простейшей формулы массива.

    Нам нужно получить сумму, поэтому воспользуемся соответствующей функцией СУММ . А вот суммировать мы должны произведение цены товара на его количество, что мы и сделаем – выбираем диапазон значений из столица B и умножаем его на аналогичный диапазон значений столбца C.

    Если сейчас нажать Enter , то появится ошибка.

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

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

    Итак, при вычислении формулы массива Excel произвел попарное умножение значений диапазонов B2:B5 и C2:C5. В результате получился массив значений который был просуммирован соответствующей функцией. Мы получили только одно значение итоговой суммы без вспомогательных вычислений.

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

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

    Похожим образом работает и функция ГПР (HLOOKUP):

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

    В Excel имеется ряд функций для обработки ссылок и массивов: вычисление номеров строки или столбца таблицы по имени ссылки, определение количества столбцов (строк) ссылки или массива, выбор значения по номеру индекса и т. д.

    Таблица 4.10. Функции для работы со ссылками и массивами.

    АДРЕС Создает адрес ячейки в виде текста, используя номер строки и номер столбца.
    ВПР Просматривает левый столбец массива в поисках определенного значения и возвращает значение из указанной ячейки.
    ВЫБОР Использует номер индекса, чтобы выбрать и вернуть значение из списка аргументов-значений.
    ГИПЕРССЫЛКА Создание ссылки, открывающей документ, находящийся на жестком диске, сервере сети или Internet.
    ГПР Просматривает верхнюю строку массива в поисках определенного значения и возвращает значение из указанной ячейки.
    двссыл Возвращает ссылку, заданную аргументом ссылка на ячейку.
    ИНДЕКС Выбирает по индексу значение из ссылки или массива.
    ОБЛАСТИ Возвращает количество областей в ссылке. Область – это интервал смежных ячеек или отдельная ячейка.
    ПОИСКПОЗ Возвращает относительную позицию элемента массива.
    ПОЛУЧИТЬ.ДАННЫЕ. СВОДНОЙ.ТАБЛИЦЫ Получение данных сводной таблицы
    ПРОСМОТР Ищет значения в векторе или массиве.
    СМЕЩ Возвращает ссылку заданной высоты и ширины, отстоящую от другой ссылки на заданное количество строки и столбцов.
    СТОЛБЕЦ Возвращает номер столбца по заданной ссылке.
    СТРОКА Возвращает номер строки, определяемой ссылкой.
    ТРАНШ Возвращает транспонированный массив.
    ЧИСЛСТОЛБ Возвращает количество столбцов в ссылке или массиве.
    ЧСТРОК Возвращает количество строк в ссылке или массиве.

    Функция ГПР

    Горизонтальное первое равенство. Ищет совпадение по ключу в первой строке определенного диапазона и возвращает значение из указанной строки этого диапазона в совпавшем с ключом столбце.

    Синтаксис: =ГПР(ключ; диапазон; номер_строки; [интервальный_просмотр]).

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

    Источник