зміст
Підсумовування — популярна арифметична операція в Excel. Припустимо, у нас є список товарів у таблиці, і нам потрібно отримати їх загальну вартість. Для цього потрібно скористатися функцією SUM. Або компанія хоче визначити загальне споживання електроенергії за певний період. Знову ж таки, потрібно узагальнити ці дані.
функція SUM може використовуватися не тільки самостійно, але і як компонент інших функцій.
Але в деяких ситуаціях нам потрібно підсумувати лише ті значення, які відповідають певному критерію. Наприклад, додайте один до одного виключно повторюваний вміст клітинок. У цьому випадку вам потрібно скористатися однією з двох функцій, які будуть описані пізніше.
Вибіркове підсумовування в Excel
Вибіркове підсумовування є наступним кроком після вивчення стандартної арифметичної операції додавання кількох значень. Якщо ви навчитеся читати та використовувати його, ви зможете наблизитися до того, щоб бути потужним з Excel. Для цього в списку формул Excel необхідно знайти такі функції.
Функція SUMIF
Припустимо, у нас є такий набір даних.
Такий звіт надав склад овочесховища. На основі цієї інформації нам потрібно зробити наступне:
- Визначте, скільки залишилося на складі певного товару.
- Розрахуйте залишки запасів разом із ціною, яка відповідає правилам, визначеним користувачем.
Використання функції СУММЕСЛІ ми можемо виокремити конкретні значення та узагальнити їх виключно. Перелічимо аргументи цього оператора:
- Діапазон. Це набір комірок, які необхідно проаналізувати на відповідність певному критерію. У цьому діапазоні можуть бути не тільки числові, а й текстові значення.
- Хвороба. Цей аргумент визначає правила, за якими дані будуть відбиратися. Наприклад, тільки значення, які відповідають слову «Груша» або числа більше 50.
- діапазон підсумовування. Якщо не потрібно, ви можете пропустити цю опцію. Його слід використовувати, якщо набір текстових значень використовується як діапазон для перевірки умови. У цьому випадку потрібно вказати додатковий діапазон із числовими даними.
Щоб виконати першу мету, яку ми поставили, потрібно вибрати клітинку, в яку буде записаний результат обчислень, і записати туди наступну формулу: =SUMIF(A2:A9;”Білий виноград”;B2:B9).
Результатом буде значення 42. Якби у нас було кілька комірок зі значенням «Білий виноград», то формула повернула б загальну суму всіх позицій цього плану.
Функція SUM
Тепер спробуємо розібратися з другою проблемою. Його основна складність полягає в тому, що у нас є кілька критеріїв, яким має відповідати асортимент. Щоб її вирішити, потрібно скористатися функцією SUMMESLIMN, синтаксис якого включає такі аргументи:
- діапазон підсумовування. Тут цей аргумент означає те саме, що й у попередньому прикладі.
- Діапазон умов 1 – це набір клітинок, у якому потрібно вибрати ті, що відповідають критеріям, описаним у аргументі нижче.
- Умова 1. Правило для попереднього аргументу. Функція вибере лише ті комірки з діапазону 1, які відповідають умові 1.
- Діапазон умов 2, умова 2 тощо.
Далі аргументи повторюються, просто потрібно послідовно вводити кожен наступний діапазон умови і сам критерій. Тепер приступимо до розв’язування задачі.
Припустимо, нам потрібно визначити, яка загальна вага залишилися на складі яблук, які коштують більше 100 руб. Для цього в клітинку, в якій повинен бути кінцевий результат, запишіть таку формулу: =СУММЕСЛИМН(B2:B9;A2:A9;»яблоки*»;C2:C9;»>100″)
Простіше кажучи, ми залишаємо діапазон підсумовування таким же, як і був. Після цього прописуємо першу умову та діапазон для неї. Після цього ми ставимо вимогу, щоб ціна була більше 100 руб.
Зверніть увагу на зірочку (*) як термін пошуку. Це вказує на те, що за ним можуть слідувати будь-які інші значення.
Як підсумувати повторювані рядки в таблиці за допомогою розумної таблиці
Припустимо, у нас є така таблиця. Його зробили за допомогою інструменту Smart Table. У ньому ми можемо побачити повторювані значення, розміщені в різних комірках.
У третьому стовпчику вказані ціни на ці товари. Скажімо, ми хочемо знати, скільки загалом коштуватимуть повторювані продукти. Що мені потрібно зробити? Спочатку потрібно скопіювати всі повторювані дані в інший стовпець.
Після цього потрібно перейти у вкладку «Дані» і натиснути на кнопку «Видалити дублікати».
Після цього з'явиться діалогове вікно, в якому потрібно підтвердити видалення повторюваних значень.
Перетворення спеціальної вставки
Тоді у нас залишиться список тільки тих значень, які не повторюються.
Нам потрібно скопіювати їх і перейти у вкладку «Головна». Там потрібно відкрити меню, розташоване під кнопкою «Вставити». Для цього натискаємо на стрілку, і в списку знаходимо пункт «Спеціальна вставка». З’явиться таке діалогове вікно.
Транспонування рядка в стовпці
Поставте прапорець поруч із «Транспонування» та натисніть «ОК». Цей елемент міняє місцями стовпці та рядки. Після цього записуємо в довільну клітинку функцію СУММЕСЛІ.
Формула в нашому випадку буде виглядати так.
Потім за допомогою маркера автозаповнення заповніть решту клітинок. Ви також можете скористатися функцією ПРОМІЖНІ ПІДСУМКИ щоб узагальнити табличні значення. Але спочатку потрібно встановити фільтр для розумної таблиці, щоб функція рахувала лише повторювані значення. Для цього натисніть на значок стрілки в заголовку стовпця, а потім поставте галочки біля тих значень, які ви хочете показати.
Після цього підтверджуємо свої дії натисканням кнопки ОК. Якщо ми додамо ще один елемент для відображення, ми побачимо, що загальна сума зміниться.
Як бачите, виконати будь-яке завдання в Excel можна кількома способами. Ви можете вибрати ті, які підходять для конкретної ситуації, або просто використовувати ті інструменти, які вам більше подобаються.