Як використовувати функцію впр разом із сум або сумісні в excel. Функція бдсумм() - додавання з множинними умовами в ms excel

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

Ось приклад із двома умовами: необхідно дізнатися суму продажу товару М'ясо(зі стовпця C) у регіоні Південний(Зі стовпця A).

Вам знадобиться така формула:

=СУМІСЛІМН(D2:D11,A2:A11,"Південний",C2:C11,"М'ясо")Результат – 14719. Ось як працює ця формула.

СУМІСЛІМН - це арифметична формула. Вона підраховує числа, які в цьому випадку перебувають у стовпці D. Почнемо з того, що вкажемо формулу на розташування чисел:

СУМІСЛІМН(D2:D11,

Іншими словами, ви хочете, щоб формула підсумовувала числа у цьому стовпці, якщо вони відповідають певним умовам. Цей діапазон осередків є першим аргументомабо частиною даних, необхідні запуску функції.

Далі потрібно знайти дані, які відповідають двом умовам, тому ви вводите першу умову, вказуючи функції, де розташовані дані (A2:A11), і назву умови («Південний»). Зверніть увагу, що між аргументами ставиться кома.

СУМІСЛІМН(D2:D11;A2:A11;"Південний";

Лапки навколо слова "Південний" вказують на те, що використовуються текстові дані.

Нарешті, ви вводите аргументи для другої умови - діапазон осередків (C2:C11), які містять слово "М'ясо", а також саме слово (укладене в лапки), щоб Excel зміг їх зіставити. Закінчіть формулу дужкою, що закривається. ) , потім натисніть клавішу ENTER і ви отримаєте результат 14 719.

СУМІСЛІМН(D2:D11;A2:A11,"Південний";C2:C11,"М'ясо")

Якщо ви вводите в Excel функцію Сумісний і не пам'ятаєте аргументів, допомога поруч. Після введення виразу = Сумісність (під формулою з'являється автозавершення формул зі списком аргументів у правильному порядку.

Якщо подивитися на зображення використання функції автозавершення формул і списку аргументів, то в нашому прикладі діапазон_підсумовування - це діапазон D2:D11, що є стовпець з числами, які ви хочете підсумувати; діапазон_умови1 - це діапазон A2.A11, що є стовпець з даними, в якому знаходиться умова1- "Південний".

У міру того, як ви вводите формулу, в автозавершенні формули з'являться решта аргументів (тут вони не показані); діапазон_умови2- це діапазон C2:C11, що є стовпець з даними, в якому знаходиться умова2- "М'ясо".

Якщо ви натиснете СУМЕСЛІМН в автозавершенні формули, відкриється стаття з довідкою.

Спробуйте попрактикуватися

Якщо ви хочете попрактикуватися з функцією СУМІСЛІМН, скористайтеся наданими демонстраційними даними з формулою, де фігурує ця функція.

Ви можете працювати з демонстраційними даними безпосередньо в цій книзі веб-програми Excel Online. Змінюйте значення та формули або додавайте свої власні, щоб побачити, як миттєво зміняться результати.

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

У цьому уроці Ви знайдете кілька цікавих прикладів, які демонструють як використовувати функцію ВВР(VLOOKUP) разом з СУМ(SUM) або СУМІСЛІ(SUMIF) в Excel, щоб виконувати пошук та підсумовування значень за одним або декількома критеріями.

Ви намагаєтеся створити файл-зведення в Excel, який визначить всі екземпляри одного конкретного значення та підсумує інші значення, пов'язані з ним? Або Вам потрібно знайти всі значення в масиві, що задовольняють заданою умовою, а потім підсумувати пов'язані значенняз іншого листа? Чи, можливо, перед Вами постало ще важче завдання, наприклад, переглянути таблицю всіх рахунків-фактур Вашої компанії, знайти серед них рахунки-фактури певного продавця та підсумувати їх?

Завдання можуть відрізнятися, але їх зміст однаковий – необхідно знайти та підсумувати значення за одним або декількома критеріями в Excel. Що це за значення? Будь-які числові. Що це за критерії? Будь-які… Починаючи з числа або посилання на комірку, що містить потрібне значення, і закінчуючи логічними операторами та результатами формул Excel.

Отже, чи є в Microsoft Excelфункціонал, здатний впоратися із описаними завданнями? Звичайно ж да! Рішення криється у комбінуванні функцій ВВР(VLOOKUP) або ПЕРЕГЛЯД(LOOKUP) з функціями СУМ(SUM) або СУМІСЛІ(SUMIF). Приклади формул, наведені далі, допоможуть зрозуміти, як ці функції працюють і як їх використовувати з реальними даними.

Зверніть увагу, наведені приклади розраховані на просунутого користувача, знайомого з основними принципами та синтаксисом функції ВВР. Якщо Вам ще далеко до цього рівня, рекомендуємо приділити увагу першій частині підручника – Функція ВПР у Excel: синтаксис та приклади.

ВПР і СУМ в Excel – обчислюємо суму знайдених значень, що збігаються

Якщо Ви працюєте з числовими даними в Excel, досить часто Вам доводиться не тільки вилучати пов'язані дані з іншої таблиці, але і підсумовувати кілька стовпців або рядків. Для цього Ви можете комбінувати функції СУМі ВВР, як показано нижче.

Припустимо, що ми маємо список товарів з даними про продаж за кілька місяців, з окремим стовпцем для кожного місяця. Джерело даних – лист Monthly Sales:

Тепер нам необхідно зробити таблицю підсумків із сумами продажів по кожному товару.

Вирішення цього завдання – використовувати масив констант в аргументі col_index_num(номер_стовпця) функції ВВР. Ось приклад формули:

SUM(VLOOKUP(lookup value, lookup range, (2,3,4), FALSE))
= СУМ(ВПР(шукане_значення;таблиця;(2;3;4);БРЕХНЯ))

Як бачите, ми використовували масив {2,3,4} для третього аргументу, щоб виконати пошук кілька разів в одній функції ВВР, та отримати суму значень у стовпцях 2 , 3 і 4 .

Тепер давайте застосуємо цю комбінацію ВВРі СУМдо даних у нашій таблиці, щоб знайти загальну суму продажів у стовпцях з Bпо M:

SUM(VLOOKUP(B2,"Monthly sales"!$A$2:$M$9,(2,3,4,5,6,7,8,9,10,11,12,13),FALSE))
=СУМ(ВПР(B2;"Monthly sales"! $A$2:$M$9;(2;3;4;5;6;7;8;9;10;11;12;13);БРЕХНЯ))

Важливо!Якщо Ви вводите формулу масиву, обов'язково натисніть комбінацію Ctrl+Shift+Enterзамість звичайного натискання Enter. Microsoft Excel заключить Вашу формулу у фігурні дужки:

(=SUM(VLOOKUP(B2,"Monthly sales"!$A$2:$M$9,(2,3,4,5,6,7,8,9,10,11,12,13),FALSE)) )
(=СУМ(ВПР(B2;"Monthly sales"!$A$2:$M$9;(2;3;4;5;6;7;8;9;10;11;12;13);БРЕХНЯ))" )

Якщо ж обмежитися простим натисканням Enter, обчислення буде здійснено лише за першим значенням масиву, що призведе до невірного результату.

Можливо, Вам стало цікаво, чому формула на малюнку вище відображає [@Product]як шукане значення. Це відбувається тому, що мої дані були перетворені на таблицю за допомогою команди Table(Таблиця) на вкладці Insert(Вставка). Мені зручніше працювати з повнофункціональними таблицями Excelніж з простими діапазонами. Наприклад, коли Ви вводите формулу в одну із осередків, Excel автоматично копіює її на весь стовпець, що заощаджує кілька дорогоцінних секунд.

Як бачите, використовувати функції ВВРі СУМв Excel досить легко. Однак це далеко не ідеальне рішення, особливо, якщо доводиться працювати з великими таблицями. Справа в тому, що використання формул масиву може уповільнити роботу програми, так як кожне значення масиву робить окремий виклик функції ВВР. Виходить, що більше значень у масиві, тим більше формул масиву в робочій книзі і тим повільніше працює Excel.

Цю проблему можна подолати, використовуючи комбінацію функцій INDEX(ІНДЕКС) та MATCH(ПОШУКПОЗ) замість VLOOKUP(В при SUM(СУМ). Далі у цій статті ви побачите кілька прикладів таких формул.

Виконуємо інші обчислення, використовуючи функцію ВПР в Excel

Щойно ми розібрали приклад, як можна отримати значення з кількох стовпців таблиці та обчислити їхню суму. Так само Ви можете виконати інші математичні операції з результатами, які повертає функція ВВР. Ось кілька прикладів формул:

Обчислюємо середнє:

(=AVERAGE(VLOOKUP(A2,"Lookup Table"!$A$2:$D$10,(2,3,4),FALSE)))
(=СРЗНАЧ(ВПР(A2;"Lookup Table"!$A$2:$D$10;(2;3;4);БРЕХНЯ))))

Lookup tableта обчислює середнє арифметичне значень, що знаходяться на перетині знайденого рядка та стовпців B, C та D.

Знаходимо максимум:

(=MAX(VLOOKUP(A2,"Lookup Table"!$A$2:$D$10,(2,3,4),FALSE)))
(=МАКС(ВПР(A2;"Lookup Table"!$A$2:$D$10;(2;3;4);БРЕХНЯ))))

Формула шукає значення з комірки A2 на аркуші Lookup tableта повертає максимальне із значень, що знаходяться на перетині знайденого рядка та стовпців B, C та D.

Знаходимо мінімум:

(=MIN(VLOOKUP(A2,"Lookup Table"!$A$2:$D$10,(2,3,4),FALSE)))
(=МІН(ВПР(A2;"Lookup Table"!$A$2:$D$10;(2;3;4);БРЕХНЯ))))

Формула шукає значення з комірки A2 на аркуші Lookup tableта повертає мінімальне із значень, що знаходяться на перетині знайденого рядка та стовпців B, C та D.

Обчислюємо % від суми:

(=0.3*SUM(VLOOKUP(A2,"Lookup Table"!$A$2:$D$10,(2,3,4),FALSE)))
(=0.3*СУМ(ВПР(A2;"Lookup Table"!$A$2:$D$10;(2;3;4);БРЕХНЯ))))

Формула шукає значення з комірки A2 на аркуші Lookup tableпотім підсумовує значення, які знаходяться на перетині знайденого рядка і стовпців B, C і D, і лише потім обчислює 30% від суми.

Якщо ми додамо перераховані вище формули в таблицю з результат, виглядатиме так:

ПРОГЛЯД та СУМ – пошук у масиві та сума пов'язаних значень

У випадку, коли Ваше значення - це масив, функція ВВРстає марною, оскільки вона не вміє працювати з масивами даних. У такій ситуації Ви можете використати функцію ПЕРЕГЛЯД(LOOKUP) в Excel, яка схожа на ВВР, До того ж працює з масивами так само, як і з одиночними значеннями.

Давайте розберемо приклад, щоб Вам стало зрозуміліше, про що йдеться. Припустимо, ми маємо таблицю, в якій перераховані імена клієнтів, куплені товари та їх кількість (таблиця Main table). Крім цього є друга таблиця, що містить ціни товарів (таблиця Lookup table). Наше завдання – написати формулу, яка знайде суму всіх замовлень заданого клієнта.

Як Ви пам'ятаєте, не можна використовувати функцію ВВР, якщо потрібне значення зустрічається кілька разів (це масив даних). Використовуйте натомість комбінацію функцій СУМі ПЕРЕГЛЯД:

SUM(LOOKUP($C$2:$C$10,"Lookup table"!$A$2:$A$16,"Lookup table"!$B$2:$B$16)*$D$2:$D$10*($B $2:$B$10=$G$1))
=СУМ(ПРОГЛЯД($C$2:$C$10;"Lookup table"!$A$2:$A$16;"Lookup table"!$B$2:$B$16)*$D$2:$D$10*($ B$2:$B$10=$G$1))

Так як це формула масиву, не забудьте натиснути на комбінацію Ctrl+Shift+Enterпісля завершення введення.

Lookup table– це назва аркуша, де знаходиться діапазон, що переглядається.

Давайте проаналізуємо складові формули, щоб Ви розуміли, як вона працює, і могли налаштувати її під свої потреби. функцію СУМпоки залишимо осторонь, оскільки її мета очевидна.

LOOKUP($C$2:$C$10,"Lookup table"!$A$2:$A$16,"Lookup table"!$B$2:$B$16)
ПЕРЕГЛЯД($C$2:$C$10;"Lookup table"!$A$2:$A$16;"Lookup table"!$B$2:$B$16)

Функція ПЕРЕГЛЯДпереглядає товари, перераховані в стовпці C основної таблиці (Main table), і повертає відповідну ціну зі стовпця B таблиці (Lookup table).

1. $ D$2:$D$10- Кількість товарів, придбаних кожним покупцем, ім'я якого є в стовпці D основної таблиці. Помножуючи кількість товару на ціну, яку повернула функція ПЕРЕГЛЯД, Отримуємо вартість кожного придбаного продукту.
2. $B$2:$B$10=$G$1– формула порівнює імена клієнтів у стовпці B основної таблиці з іменем у осередку G1. Якщо є збіг, повертається 1 , в іншому випадку 0 . Таким чином, відкидаються імена покупців, що відрізняються від вказаного в осередку G1, адже всі ми знаємо – множення на нуль дає нуль.

Оскільки наша формула – це формула масиву, вона повторює описані вище дії кожному за значення у масиві пошуку. На завершення, функція СУМобчислює суму значень, які у результаті множення. Зовсім не складно, Ви згодні?

Зауваження.Щоб функція ПЕРЕГЛЯДпрацювала правильно, стовпець, що переглядається, повинен бути відсортований в порядку зростання.

ВПР і СУМЕСЛІ – знаходимо і підсумовуємо значення, що задовольняють певному критерію

Функція СУМІСЛІ(SUMIF) в Excel схожа на СУМ(SUM), яку ми щойно розбирали, оскільки вона також підсумовує значення. Різниця лише в тому, що СУМІСЛІпідсумовує ті значення, які задовольняють заданому Вами критерію. Наприклад, найпростіша формула з СУМІСЛІ:

SUMIF(A2:A10,">10")
=СУМІСЛІ(A2:A10;">10")

– підсумовує всі значення осередків у діапазоні A2:A10, які більше 10 .

Дуже просто, правда? А тепер давайте розглянемо трохи складніший приклад. Припустимо, що у нас є таблиця, в якій перелічені імена продавців та їх номери ID(Lookup table). Крім цього, є ще одна таблиця, в якій ті самі IDпов'язані з даними про продаж (Main table). Наше завдання – знайти суму продажів для заданого продавця. Тут є 2 обтяжуючі обставини:

• Основна таблиця (Main table) містить множину записів для одного IDу випадковому порядку.
• Ви не можете додати стовпець із іменами продавців до основної таблиці.

Запишемо формулу, яка знайде всі продажі, зроблені заданим продавцем, а також підсумує знайдені значення.

Перед тим, як ми почнемо, дозвольте нагадати синтаксис функції СУМІСЛІ(SUMIF):

SUMIF(range,criteria,)
СУМІСЛИ(діапазон;критерій;[діапазон_підсумовування])

• range(Діапазон) - аргумент говорить сам за себе. Це просто діапазон осередків, які Ви хочете оцінити заданим критерієм.
• criteria(Критерій) - умова, яка говорить формулі, які значення підсумовувати. Може бути числом, посиланням на комірку, виразом або іншою функцією Excel.
• sum_range(Діапазон_підсумовування) – необов'язковий, але дуже важливий для нас аргумент. Він визначає діапазон пов'язаних осередків, які сумуватимуться. Якщо він не вказаний, Excel підсумовує значення осередків у першому аргументі функції.

Зібравши все докупи, давайте визначимо третій аргумент для нашої функції СУМІСЛІ. Як Ви пам'ятаєте, ми хочемо підсумовувати всі продажі, здійснені певним продавцем, ім'я якого задано в осередку F2 (дивіться малюнок, наведений вище).

1. range(діапазон) – тому що ми шукаємо по IDпродавця, значеннями цього аргументу будуть значення у стовпці B основної таблиці (Main table). Можна встановити діапазон B:B(весь стовпець) або, перетворивши дані на таблицю, використовувати ім'я стовпця Main_table.

criteria(критерій) – оскільки імена продавців записані в таблиці (Lookup table), використовуємо функцію ВВРдля пошуку ID, що відповідає заданому продавцю. Ім'я записано в осередку F2, тому для пошуку використовуємо формулу:

VLOOKUP($F$2,Lookup_table,2,FALSE)
ВПР($F$2;Lookup_table;2;БРЕХНЯ)

Звичайно, Ви могли б ввести ім'я як потрібне значення безпосередньо в функцію ВВР, але краще використовувати абсолютне посилання на комірку, оскільки так ми створюємо універсальну формулу, яка буде працювати для будь-якого значення, введеного в цей комірку.

Функція БДСУМ() , англійський варіант DSUM(), підсумовує числа таблиці даних, які задовольняють заданим умовам.

Розглянемо потужну функцію підсумовування БДСУМ (), англійський варіант DSUM (database, field, criteria). Цю функцію можна використовувати, коли необхідно підсумувати значення з урахуванням кількох умов. Детальний аналізцих завдань наводиться у групі статей.

Як показано у вищезгаданих статтях, без функції БДСУММ() можна взагалі обійтися, замінивши її функціями СУММПРОИЗВ() , СУМЕСЛІМН() або . Але, іноді, функція БДСУММ дійсно зручна, особливо при використанні численних або складних критеріїв, наприклад, з .
Спочатку розберемо синтаксис функції, потім розв'яжемо завдання.

Синтаксис функціїБДСУМ()

Для використання цієї функції потрібно:

• вихідна таблиця мала заголовки стовпців;
• критерії повинні були оформлені у вигляді невеликої таблиці із заголовками;
• заголовки таблиці критеріїв збігалися із заголовками вихідної таблиці (якщо критерій не задається формулою).

БДСУМ( база_даних;поле;умови)
База данихє діапазон осередків із даними пов'язаними логічно, тобто. таблиці. Верхній рядоктаблиці має містити заголовки всіх стовпців.
Поле- Заголовок стовпця, яким проводиться підсумовування (тобто. стовпець з числами). Аргумент Полеможна заповнити ввівши:

• текст із заголовком стовпця в подвійних лапках, наприклад "Вік" або "Врожай",
• число (без лапок), що задає положення стовпця в таблиці (зазначеної в аргументі база даних): 1 – для першого стовпця, 2 – для другого і т.д.
• посилання на заголовок стовпця.

Умови- інтервал осередків, який містить умови, що задаються (тобто таблиця критеріїв). Структура таблиці з критеріями відбору БДСУММ() аналогічна структурі для .

Завдання

Припустимо, що в діапазоні A 8:С13 є таблиця продажів, що містить поля (стовпці) Товар, Продавецьі Продажі(Див. малюнок вище і файл прикладу).

Завдання 1 (з одним числовим критерієм).

Підсумуємо всі продажі, які >3000.

Алгоритм наступний:

Альтернативне рішення- =СУМІСЛІ(C9:C13;F3) або = СУМІСЛИ(C9:C13;">3000")

Завдання 2 (з одним текстовим критерієм)

Підсумуємо всі значення продажів продавця Бєлов.

Алгоритм наступний:

• Створимо нову табличку критеріїв, що складається із заголовка Продавець (збігається з назвою заголовка стовпця вихідної таблиці, до якого застосовується критерій) та власне критерію (умови відбору);

• Умова відбору має бути записана в спеціальному форматі: ="=Бєлов" (підсумовуватимуться Продажітільки рядків, у яких у стовпці Продавецьміститься точнослово Бєлов(або Білов, білів, тобто. без). Якщо є рядки з Продавцями « Іван Бєлов», «Бєлов Іван»та ін., то підсумовування щодо них проводитися не буде.
  Примітка: Якщо як критерій вказати не ="= Бєлов " , а просто Бєлов, то, сумуватимуться Продажірядків, у яких у стовпці Продавецьмістяться значення, що починаються зі слова Бєлов (наприклад, « Бєлов Іван», Бєлов, Білов).
  Щоб підсумувати продажі, в тому числі і для продавця. Іван Бєлов», необхідно в якості критерію вказати = "= * Бєлов". Цей критерій враховує значення, закінчуютьсяна Бєлов.Зірочка (*) - це.
  Якщо як критерій вказати *Бєлов(або = "= * Білов *") , то будуть підраховані числа, у відповідних осередках яких міститься слово Бєлов.
• Тепер можна нарешті записати саму формулу = БДСУМ (B8: C13; C8; B2: B3)Припускаючи, що База_данных (початкова таблиця) перебуває у B8: C13 (стовпець А ( Товар) можна у разі не включати у Базу_данных, т.к. він бере участь у формуванні умови і у ньому не проводиться підсумовування). С8 - Це посилання на заголовок стовпця за яким буде проводитися підсумовування (тобто стовпець Продажі). B2:B3 - Посилання на табличку критеріїв.

Альтернативне рішення - =СУМІСЛИ(B9:B13;"білів";C9:C13)

Завдання 3 (Два критерії до різних стовпців рядка, Умова І)

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

Формула для складання: = БДСУМ (B8: C13; C8; F2: G3)

Альтернативне рішення - =Сумісний(C9:C13;B9:B13;G3;C9:C13;F3)або =СУМІСЛІМН(C9:C13;B9:B13;"білів";C9:C13;">3000")

Завдання 4 (Два текстові критерії до одного стовпця, умова відбору АБО)

У цьому випадку критерії відбору повинні розміщуватися під заголовком, назва якого збігається із заголовком стовпця вихідної таблиці, за яким буде здійснюватися пошук (стовпець Продавець). Критерії повинні розташовуватися одна під одною (у різних рядках), т.к. відбираються рядки, які мають у полі Продавець значення Бєлов АБОрядки, які мають у полі Продавець значення Батурін(функція БДСУММ) як би робить 2 проходи по таблиці з різними критеріями для одного поля).

БДСУМ (B8: C13; C8; B2: B4)

Альтернативне рішення - =СУМІСЛІ(B9:B13;"білів";C9:C13)+СУМІСЛІ(B9:B13;"батурин";C9:C13)

Завдання 5 (Два критерії до різних стовпців, умова відбору АБО)

Знайдемо суму продажу БєловаАБО Продажів>6000 Тобто. потрібно відібрати рядки, в яких у стовпці Продавецьзначиться БєловАБО в стовпці Продажіє значення >6000 .

Критерії повинні розташовуватися в різних рядках та в різних стовпцях, т.к. відбираються рядки, які мають у полі Продавець значення Бєлов АБОрядки, які мають у полі Продажу значення >6000 (функція БДСУММ) як би робить 2 проходи по таблиці з різними критеріями для 2-х різних полів).

Записати саму формулу можна так = БДСУМ (B8: C13; C8; G2: H4)

Альтернативне рішення - =СУМІСЛІ(B9:B13;G3;C9:C13)+СУМІСЛІ(C9:C13;H4)-СУМІСЛІМН(C9:C13;B9:B13;G3;C9:C13;H4)або

=СУМІСЛІ(B9: B13; "білів";

Завдання 6 (Два текстові критерії до різних стовпців, умова відбору І)

Знайдемо суму продажу товару Фруктипродавця Бєлов. Тобто. потрібно відібрати рядки, в яких у стовпці Продавецьзначиться БєловІу стовпці Товарзначиться Фрукти.

У цьому випадку критерії відбору повинні розміщуватися під заголовками, назви яких збігаються із заголовками стовпців вихідної таблиці, за якими буде здійснюватися пошук (стовпці Продавецьі Товар).

Записати саму формулу можна так = БДСУМ (A8: C13; C8; A2: B3)

Альтернативне рішення - =СУМІСЛІМН(C9:C13;A9:A13;"фрукти";B9:B13;"білів")

Завдання 7 (Умови відбору, створені внаслідок застосування формули)

Підсумуємо продажі, які вищі за середній.

Як умову відбору можна використовувати значення, яке обчислюється за допомогою формули. Формула повинна повертати результат ІСТИНА або БРЕХНЯ.

Для цього введемо в комірку С3 файлу прикладу формулу =C9>СРЗНАЧ($C$9:$C$13) , а в С2 замість заголовка введемо довільний текст, що пояснює, наприклад, « Більше середнього(заголовок не повинен повторювати заголовки вихідної таблиці).

Зверніть увагу на те, що діапазон знаходження середнього значення введений за допомогою посилань ( $C$9:$C$13 ), а середнє значення всіх продажів таблиці СРЗНАЧ($C$9:$C$13) порівнюється з першим значенням діапазону, посилання на який задане відносною адресацією ( C9 ). При обчисленні функції БДСУМ() EXCEL побачить, що С9 - це відносне посилання, і буде переміщатися діапазоном вниз по одному запису і повертати значення або ІСТИНА, або БРЕХНЯ (більше середнього чи ні). Якщо буде повернуто значення ІСТИНА, то відповідний рядок таблиці буде враховано під час підсумовування. Якщо повернуто значення БРЕХНЯ, то рядок не враховано.

Записати формулу можна так = БДСУМ (C8: C13; C8; C2: C3)

Альтернативне рішення - =СУМІСЛІ(C9:C13;">"&СРЗНАЧ($C$9:$C$13))

Завдання 8 (Три критерії)

Знайдемо суму продажу Бєловаякі вище середнього, а також продажу Батуріна.

Записати формулу можна так = БДСУМ (B8: C13; C8; B2: C4)

Альтернативне рішення - =СУМІСЛІМН(C9:C13;C9:C13;">"&СРЗНАЧ($C$9:$C$13);B9:B13;"Білов")+СУМІСЛІ(B9:B13;"Батурин";C9:C13)

Завдання 9 (Один текстовий критерій, враховується Реєстр)

Сума продажів Товару ФРУКТИ (перші три літери - ВЕЛИКІ (тобто великі))

Записати формулу можна так = БДСУМ (A8: C13; C8; E2: E3)

Альтернативне рішення - =СУМПРОВИЗВ(СПІД("ФРУкти";A9:A13)*C9:C13)