Массивы в excel: что такое, формула, как сделать / skillbox media

Функция ВПР

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

Синтаксис: =ВПР(ключ; диапазон; номер_столбца; ), где

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

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

Пример использования:

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

Для цены необходимо использовать функцию ВПР с точным совпадением (интервальный просмотр ЛОЖЬ), так как данный параметр определен для всех товаров и не предусматривает использование цены другого товара, если вдруг она по случайности еще не определена.

Для определения расположения товара используется ВПР с приблизительным совпадением (интервальный просмотр ИСТИНА), так как распределение товара осуществляется по категориям. Из-за того, что наименование товара состоит из названия категории плюс дополнительный текст, по условиям сортировки от А до Я наименования товаров будут идти сразу после наименования категории, поэтому когда в таблице не обнаруживается совпадений с ключом подтягивается первое значение сверху.

В принципе, данный подход устраивал бы, если для товаров, для которых отсутствует категория, не подтягивалось расположение

Обратите внимание на товар «Лук Подмосковье». Для него определено расположение «Стелаж №2», хотя в первой таблице нет категории «Лук». Это происходит все по тем же причинам, когда функцией не обнаруживается равенств, то она определяет для значения значение меньшего самого близкого по сортировке ключа, а для «Лук Подмосковье» это категория «Картофель»

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

Он подобного эффекта можно избавиться путем определения категории из наименования товара используя текстовые функции ЛЕВСИМВ(C11;ПОИСК(» «;C11)-1), которые вернут все символы до первого пробела, а также изменить интервальный просмотр на точный.

Помимо всего описанного, функция ВПР позволяет применять для текстовых значений подстановочные символы – * (звездочка – любое количество любых символов) и ? (один любой символ). Например, для искомого значения «*» & «иван» & «*» могут подойти строки Иван, Иванов, диван и т.д.

Также данная функция может искать значения в массивах – =ВПР(1;{2;»Два»:1;»Один»};2;ЛОЖЬ) – результат выполнения строка «Два».

Примеры

Пример #1. Можно ли утвержадать, что число 6 — делитель числа 55?

Решение:

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

Значит, чтобы число 6 было делителем числа 55, нужно, чтобы число 55 делилось на число 6 без остатка.

В данно случае деление получается с остатком, т. к. число 55 не делится нацело на число 6, т.е. число 6 не является делителем числа 55.

Ответ: нет.

Пример #2. Назови все двузначные числа, кратные числу 48.Решение: По определению кратным натуральному числу a называют натуральное число, которое делится без остатка на число a.

Значит, чтобы двузначное число было бы кратным числу 48, оно должно делиться на число 48 без остатка.

Таких двузначных чисел, делящихся на число 48 без остатка, два: 48; 96.

Ответ: 48;96.

Пример #3. Не выполняя вычислений, определи, какому числу из предложенных в ответе (3, 2 или 7) кратно данное произведение 29 ⋅ 27.Решение: Известно, что кратным натуральному числу a называют натуральное число, которое делится без остатка на число a.

Также знаем, что если хотя бы один из множителей делится на некоторое число, то и произведение делится на это число.

В данном произведении 29 ⋅ 27 множитель 27 делится без остатка на число 3, значит, и произведение 29 ⋅ 27 делится без остатка на число 3, т.е. кратно числу 3.

Ответ: 3.

Пример #4. В каждой коробке лежат 8 чайных ложек. Возможно ли, взять определённое количество коробок, чтобы в них лежало ровно 13 ложки(-ек)? Решение: Анализируя условие задачи, можно сделать вывод, что утвердительный ответ возможен, если число ложек, которое мы хотим взять, кратно числу ложек, находящихся в каждой коробке.

Это следует из определения: кратным натуральному числу a называют натуральное число, которое делится без остатка на число a. Значит, нельзя, не вскрывая коробок, взять 13 шт. ложек, т. к. число 13 не делится на число 8 без остатка, т. е. 13 : 8 ≠ целому числу.

Ответ: нет.

Пример #5. Сократи дробь:

Решение:

Для сокращения дроби заметим, что один из множителей в числителе дроби и один из множителей в знаменателе дроби делится на число 5, значит, и произведения в числителе и знаменателе делятся на число 5.

Поэтому, если сократим эти множители на 5, останется дробь с такими множителями в числителе и знаменателе.

Ответ:

Пример #6. В одном букете было 16 роз, а в другом — 49. Можно ли эти розы поставить в 6 ваз так, чтобы в каждой вазе цветов было поровну?Решение: 1. Определим общее количество роз в двух букетах вместе: 16 + 49 = 65 шт.

2. Можно ли эти розы поставить в 6 ваз так, чтобы в каждой вазе цветов было поровну? Для ответа на этот вопрос нужно проверить делимость полученной суммы на число 6.

Получим, что розы поставить в 6 ваз так, чтобы в каждой вазе цветов было поровну нельзя, потому что числа не делятся нацело, т. к. 65 : 6 ≠ целому числу.

Ответ: разделить поровну нельзя, потому что числа не делятся нацело.

Пример #7. Укажи натуральное значение x, чтобы сумма 42 + x не делилась на 7. Выбери из следующих вариантов: 5, 21, 7.

Решение: Для того, чтобы сумма 42 + x не делилась на 7, выбираем значение x = 5, т.к. известно, что если каждое слагаемое делится на некоторое число, то и сумма делится на это число.

Имеем, что число 42 делится на 7, значит, число x не должно делиться на 7, чтобы сумма не делилась на 7.

Значение x = 5 не делится на 7.

Ответ: 5.

Пример #8. Известно, что c и d — натуральные числа и 5c + d = 42. Каким может быть число c?

Решение: Известно, что c и d — натуральные числа и 5c + d = 42. Выражение 5c = 42 − d должно быть кратно числу 5.

Поэтому число c может быть равно 1;2;3;4;5;6;7;8, тогда 5c = 5; 10; 15; 20; 25; 30; 35; 40, а d = 37; 32; 27; 22; 17; 12; 7; 2, т. е. сумма будет равна 5c + d = 42.Ответ: 1;2;3;4;5;6;7;8.

Пример #9. Определи натуральные значения, которые может принимать выражение

если 0 < x < 40, x — натуральное число.

Решение: Для того, чтобы выражение приняло натуральные значения, необходимо вместо переменной x подставить числа, кратные числу 8, принадлежащие промежутку 0 < x < 40. Это будут числа 8; 16; 24; 32.

Разделив эти числа на число 8 и умножив полученные частные на 3, имеем в результате 3;6;9;12.

Ответ: 3;6;9;12.

Пример #10. Выполни деление: (39b + 24) : 3.

Решение: Известно, что если каждое слагаемое делится на некоторое число, то и вся сумма делится на это число.

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

Получим: (39b + 24) : 3 = 39b : 3 + 24 : 3 = 13b + 8.Ответ: 13b + 8.

Пример #11. Выполни деление: 6xy : 3x.

Решение: Выполняя деление (6xy):(3x), разделим сначала числовые множители, затем одинаковые буквенные множители, полученные результаты перемножим.

Получим: 6xy : 3x = 6:3 ⋅ (x:x) ⋅ y = 2 ⋅ 1 ⋅ y = 2y.

В результате деления получаем ответ: 2y.Ответ: 2y.

Меняем местами столбцы и строки

Иногда при работе в Excel нужно поменять положение столбцов или строк — транспортировать их. Например, перевести шапку таблицы из горизонтального положения в вертикальное. Делать это вручную долго — особенно, когда ячеек очень много. Ускорить процесс помогут массивы и оператор ТРАНСП:

Шаг 1. Выделяем ячейку, в которой хотим получить результат операции. В строке ссылок вводим знак равенства и оператор ТРАНСП и открываем скобку.

Используем оператор ТРАНСП, чтобы поменять положение шапки таблицы с горизонтального на вертикальноеСкриншот: Excel / Skillbox Media

Шаг 2. Выделяем шапку таблицы и закрываем скобку. Строка ссылок принимает вид: fx=ТРАНСП(A1:E1).

Выделяем диапазон, который нужно транспортироватьСкриншот: Excel / Skillbox Media

Шаг 3. Нажимаем Enter — функция меняет положение шапки таблицы на вертикальное.

За несколько секунд шапка таблицы превратилась из горизонтальной в вертикальнуюСкриншот: Excel / Skillbox Media

Как создать формулу массива в «Google Таблицах»? Всё точно так же, как в Excel, но нужно добавить оператор ARRAYFORMULA. Его ставят перед всей формулой массива в строке ссылок. Например, если вы хотите перемножить данные в двух столбцах, формула в готовом виде будет выглядеть так: fx=ARRAYFORMULA(B2:B9*D2:D9).

Так выглядит работа формулы массива в «Google Таблицах»Скриншот: Google Таблицы / Skillbox Media

Почему ИНДЕКС/ПОИСКПОЗ лучше, чем ВПР?

Решая, какую формулу использовать для вертикального поиска, большинство гуру Excel считают, что ИНДЕКС/ПОИСКПОЗ намного лучше, чем ВПР. Однако, многие пользователи Excel по-прежнему прибегают к использованию ВПР, т.к. эта функция гораздо проще. Так происходит, потому что очень немногие люди до конца понимают все преимущества перехода с ВПР на связку ИНДЕКС и ПОИСКПОЗ, а тратить время на изучение более сложной формулы никто не хочет.

Далее я попробую изложить главные преимущества использования ПОИСКПОЗ и ИНДЕКС в Excel, а Вы решите – остаться с ВПР или переключиться на ИНДЕКС/ПОИСКПОЗ.

4 главных преимущества использования ПОИСКПОЗ/ИНДЕКС в Excel:

1. Поиск справа налево. Как известно любому грамотному пользователю Excel, ВПР не может смотреть влево, а это значит, что искомое значение должно обязательно находиться в крайнем левом столбце исследуемого диапазона. В случае с ПОИСКПОЗ/ИНДЕКС, столбец поиска может быть, как в левой, так и в правой части диапазона поиска. Пример: покажет эту возможность в действии.

2. Безопасное добавление или удаление столбцов. Формулы с функцией ВПР перестают работать или возвращают ошибочные значения, если удалить или добавить столбец в таблицу поиска. Для функции ВПР любой вставленный или удалённый столбец изменит результат формулы, поскольку требует указывать весь диапазон и конкретный номер столбца, из которого нужно извлечь данные.

Например, если у Вас есть таблица A1:C10, и требуется извлечь данные из столбца B, то нужно задать значение 2 для аргумента col_index_num (номер_столбца) функции ВПР, вот так:

Если позднее Вы вставите новый столбец между столбцами A и B, то значение аргумента придется изменить с 2 на 3, иначе формула возвратит результат из только что вставленного столбца.

Используя ПОИСКПОЗ/ИНДЕКС, Вы можете удалять или добавлять столбцы к исследуемому диапазону, не искажая результат, так как определен непосредственно столбец, содержащий нужное значение. Действительно, это большое преимущество, особенно когда работать приходится с большими объёмами данных. Вы можете добавлять и удалять столбцы, не беспокоясь о том, что нужно будет исправлять каждую используемую функцию ВПР.

3. Нет ограничения на размер искомого значения. Используя ВПР, помните об ограничении на длину искомого значения в 255 символов, иначе рискуете получить ошибку #VALUE! (#ЗНАЧ!). Итак, если таблица содержит длинные строки, единственное действующее решение – это использовать ИНДЕКС/ПОИСКПОЗ.

Предположим, Вы используете вот такую формулу с ВПР, которая ищет в ячейках от B5 до D10 значение, указанное в ячейке A2:

Формула не будет работать, если значение в ячейке A2 длиннее 255 символов. Вместо неё Вам нужно использовать аналогичную формулу ИНДЕКС/ПОИСКПОЗ:

4. Более высокая скорость работы. Если Вы работаете с небольшими таблицами, то разница в быстродействии Excel будет, скорее всего, не заметная, особенно в последних версиях. Если же Вы работаете с большими таблицами, которые содержат тысячи строк и сотни формул поиска, Excel будет работать значительно быстрее, при использовании ПОИСКПОЗ и ИНДЕКС вместо ВПР. В целом, такая замена увеличивает скорость работы Excel на 13%.

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

С другой стороны, формула с функциями ПОИСКПОЗ и ИНДЕКС просто совершает поиск и возвращает результат, выполняя аналогичную работу заметно быстрее.

Один числовой критерий (Выбрать те Товары, у которых цена выше минимальной)

Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист Один критерий — число ).

Необходимо отобразить в отдельной таблице только те записи (строки) из Исходной таблицы, у которых цена выше 25.

Решить эту и последующие задачи можно легко с помощью стандартного фильтра. Для этого выделите заголовки Исходной таблицы и нажмите CTRL+SHIFT+L. Через выпадающий список у заголовка Цены выберите Числовые фильтры. , затем задайте необходимые условия фильтрации и нажмите ОК.

Будут отображены записи удовлетворяющие условиям отбора.

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

Критерий (минимальную цену) разместим в ячейке Е6, таблицу для отфильтрованных данных — в диапазоне D10:E19.

Теперь выделим диапазон D11:D19 (столбец Товар) и в Строке формул введем формулу массива:

Вместо ENTER нажмите сочетание клавиш CTRL+SHIFT+ENTER.

Те же манипуляции произведем с диапазоном E11:E19 куда и введем аналогичную формулу массива:

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

Чтобы показать динамизм полученного Отчета (Запроса на выборку) введем в Е6 значение 65. В новую таблицу будет добавлена еще одна запись из Исходной таблицы, удовлетворяющая новому критерию.

Если в Исходную таблицу добавить новый товар с Ценой в диапазоне от 25 до 65, то в новую таблицу будет добавлена новая запись.

В файле примера также содержатся формулы массива с обработкой ошибок, когда в столбце Цена содержится значение ошибки, например #ДЕЛ/0! (см. лист Обработка ошибок).

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

Как вычислить среднее арифметическое значение чисел

В Excel вычисление среднего значения происходит при помощи функции СРЗНАЧ (англ. AVERAGE). Эта функция считает среднее арифметическое числовых значений.

Синтаксис функции:

=СРЗНАЧ (Число 1; Число 2;…;Число n)

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

Давайте рассмотрим пример вычисления среднего арифметического значения в Excel.

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

Есть 3 способа воспользоваться функцией СРЗНАЧ:

Способ 1. При помощи встроенного инструмента. Ставим курсор под набором чисел, и переходим на вкладку Главная — блок Редактирование — нажимаем стрелочку возле значка Автосумма — Среднее.

На картинке видно, что Excel самостоятельно определил диапазон, который нужно усреднить. Он выделен бегущим выделением. Если этот диапазон выделен неверно, то, до нажатия Enter выделите правильный диапазон и только после этого нажмите Enter.

Способ 2. При помощи мастера функций.

Также выделите ячейку, где нужно получить среднее арифметическое. После нажмите кнопку Fx на панели инструментов (рядом со строкой формул), появится окно Вызов функции. Здесь в категории Статистические можно найти функцию СРЗНАЧ и вызвать мастер формул. И далее, установив курсор в поле Число 1, выделить диапазон ячеек и нажать Ок.

Способ 3. Выделить ячейку и ввести в строке формул:

=СРЗНАЧ(C2:C8)

Формулы массива в MS EXCEL. Знакомство

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

Без формул массива (array formulas) можно обойтись, т.к. это просто сокращенная запись группы однотипных формул. Однако, у формул массива есть серьезное преимущество: одна такая формула может заменить один или несколько столбцов с обычными формулами.

Например, можно найти сумму квадратов значений из диапазона А2:A12, просто записав в ячейке B14 формулу =СУММ(A2:A12^2) . Для сравнения: чтобы найти сумму квадратов, используя обычные формулы, нам потребуется дополнительный столбец для вычисления квадратов значений и одна ячейка для их суммирования (см. файл примера или диапазон B2:B13 на рисунке ниже).

В отличие от ввода обычных формул, после ввода формулы массива нужно нажать вместо ENTER комбинацию клавиш CTRL+SHIFT+ENTER (поэтому, иногда, формулы массива также называются формулами CSE — это первые буквы от названия клавиш, используемых для ввода Ctrl, Shift, Enter). После этого формула будет обрамлена в фигурные скобки (их не вводят с клавиатуры, они автоматически появляются после нажатия CTRL+SHIFT+ENTER). Это обрамление показано на рисунке выше (см. Строку формул).

Если бы мы нажали просто ENTER, то получили бы сообщение об ошибке #ЗНАЧ!, возникающую при использовании неверного типа аргумента функции, т.к. функция СУММ() принимает в качестве аргумента только диапазон ячеек (или формулу, результатом вычисления которой является диапазон, или константы). В нашем случае мы в качестве аргумента ввели не диапазон, а некое выражение, которое еще нужно вычислить перед суммированием, поэтому и получили ошибку.

Чтобы глубже понять формулы массива проведем эксперимент:

выделим ячейку B13, содержащую обычную формулу =СУММ($B$2:$B$12) ;
в Cтроке формул выделим аргумент функции СУММ() , т.е. $B$2:$B$12;
нажмем клавишу F9, т.е. вычислим, выделенную часть формулы;
получим – массив квадратов значений из столбца В. Массив – это просто набор неких элементов (значений).

Т.е. обычная функция СУММ() в качестве аргумента получила некий массив (или точнее ссылку на него).Теперь проведем тот же эксперимент с формулой массива:

выделим ячейку, содержащую формулу массива =СУММ($A$2:$A$12^2) ;
в строке формул выделим аргумент функции СУММ() , т.е. $A$2:$A$12^2 ;
нажмем клавишу F9, т.е. вычислим, выделенную часть формулы;
получим – тот же массив, что и в первом случае.

Т.е. нажатие CTRL+SHIFT+ENTER заставило EXCEL перед суммированием произвести промежуточные вычисления с диапазоном ячеек (с массивом содержащихся в нем значений). Для самой функции СУММ() ничего не изменилось – она получила тот же массив, только предварительно вычисленный, а не прямо из диапазона ячеек, как в случае с обычной формулой. Понятно, что вместо функции СУММ() в формуле массива может быть использована любая другая функция MS EXCEL: СРЗНАЧ() , МАКС() , НАИБОЛЬШИЙ() и т.п.

Вышеприведенный пример иллюстрирует использование функции массива возвращающей единственное значение, т.е. результат может быть выведен в одной ячейке. Это достигается использованием функций способных «свернуть» вычисленный массив до одного значения ( СУММ() , СРЗНАЧ() , МАКС() ). Примеры таких функций массива приведены в статье Формулы массива, возвращающие одно значение.

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

Преимущества и недостатки формул массива рассмотрены в одноименной статье Формулы массива. Преимущества и недостатки.

В файле примера также приведено решение данной задачи функцией СУММПРОИЗВ() , которая зачастую не требует введения ее как формулы массива: =СУММПРОИЗВ($A$2:$A$12^2)

Здесь, при вводе формулы СУММПРОИЗВ() нажимать CTRL+SHIFT+ENTER необязательно.

Ссылки на статьи о формулах массива на сайте Microsoft:

2. Основы формул массива (на английском) из книги авторов Colin Wilcox и John Walkenbach

ПРИМЕЧАНИЕ При создании Именованных формул и правил Условного форматирования формулы массива нельзя ввести нажимая CTRL+SHIFT+ENTER. Эти формулы вводятся только в ячейки листа. Однако, если формуле массива присвоить Имя, то EXCEL «сообразит», что нужно с ней нужно делать. Например, если формуле =СУММ($A$2:$A$12^2) присвоить имя Сумма_квадратов, а затем в ячейке указать =Сумма_квадратов , то получим правильный результат.

Использование функции СУММЕСЛИМН

Последний пример – функция СУММЕСЛИМН, которая похожа на предыдущую, но позволяет работать со множеством аргументов. Возьмем таблицу, где указано наименование продукции с некоторыми одинаковыми названиями, есть разная цена и количество.

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

Объявите функцию СУММЕСЛИМН и сначала запишите тот диапазон, который будете считать. В моем случае это количество груш.
Далее нужно вычленить из списка продукции только груши, для чего используйте критерий текста точно так же, как это уже было показано в предыдущем примере.
Второй критерий – цена, которая должна превышать 10 за единицу. Соответственно, впишите блок с неравенством A1:A10;»>10″, где A1:A10 – диапазон ячеек, а >10 – критерий.
Нажмите клавишу Enter и ознакомьтесь с результатом. На следующем скриншоте вы видите, что груш с ценой больше 10 довольно много, все их количество суммировалось и отображается в отдельной ячейке.

При первой записи у вас могут возникнуть трудности с правильным написанием функции, ведь она содержит много условий. Я оставлю вам ее отдельно, чтобы вы могли скопировать ее и вставить, подставив вместо текущих диапазонов ячеек свои: =СУММЕСЛИМН(B2:B25;A2:A25;»Груши»;C2:C25;»>10″). Не забывайте о том, что первый диапазон – то, что вы считаете, далее идет первый критерий – диапазон с названием столбца, потом второй – диапазон с неравенством.

Это лишь несколько примеров использования функций СУММЕСЛИ и СУММЕСЛИМН в Excel. Полученные знания вы можете использовать в своих целях, выполняя необходимые расчеты и упрощая процесс взаимодействия с электронной таблицей.

Простой пример использования массива для получения общего итога.

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

Необходимо найти итоговую сумму

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

Все это не будет ошибкой, и именно такие действия применят большинство из сотрудников организации.

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

А теперь подумаем вот о чем. Диапазон с ценами – это массив? Безусловно, там же только числа. Аналогично и диапазон с количеством также является массивом. А если это массивы, то вместо того, чтобы попарно перемножать значения цены и находящегося в той же строки количества, можно применить Функцию «СУММ» непосредственно к произведению этих массивов!

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

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

Для ее ввода одновременно нажимаем комбинацию клавиш «Ctrl + Shift + Enter». Если просто нажать»Enter» то программа решит, что мы вводим обычную формулу. Она просто не поймет, как можно в ней ОДНОВРЕМЕННО перемножать несколько ячеек и даст ошибку. Поэтому будьте внимательны!