Резюме
Для побудови двосторонньої зведеної таблиці, яка підсумовує числові дані з кількома критеріями, можна скористатися функцією SUMIF. У наведеному прикладі формула в H5, скопійована в діапазоні H5: K7, має вигляд:
=SUMIFS(value,name,$G5,stage,H$4)
де значення (C5: C15), ім'я (B5: B15) і етап (D5: D15) називаються діапазонами. У результаті виходить таблиця, яка показує підсумкові підсумки для кожного імені за етапом.
Пояснення
Функція SUMIFS призначена для підсумовування числових значень за допомогою кількох критеріїв.
У наведеному прикладі дані в діапазоні B5: E15 показують конвеєр продажів, де кожен рядок - це можливість, що належить продавцю на певному етапі. Формула в H5 така:
=SUMIFS(value,name,$G5,stage,H$4)
Перша частина формули підсумовує можливості продавця:
=SUMIFS(value,name,$G5 // sum by name
- Діапазон суми - це названі значення діапазону
- Критерії в діапазоні 1 іменований діапазон ім'я
- Критерій 1 походить від комірки G5
Зверніть увагу, що $ G5 є змішаним посиланням, із заблокованим стовпцем та відносним рядком. Це дозволяє формулі змінюватись за необхідності, коли формула копіюється по всій таблиці.
Наступна пара діапазонів / критеріїв у SUMIFS, суми за етапами:
stage,H$4 // sum by stage
- Діапазон критеріїв 2 - це названий етап діапазону
- Критерій 2 - H $ 4
Знову ж, H $ 4 є змішаним посиланням, із відносним стовпцем і рядком, заблокованим. Це дозволяє критеріям вибирати значення етапу в рядку 4, коли формула копіюється по таблиці та вниз.
З обома критеріями разом, функція SUMIFS правильно підсумовує можливості за іменем та за стадією.
Без діапазонів імен
Цей приклад використовує іменовані діапазони лише для зручності. Без іменованих діапазонів еквівалентною формулою є:
=SUMIFS($C$5:$C$15,$B$5:$B$15,$G5,$D$5:$D$15,H$4)
Посилання на повідомлення, що стосуються імені, значення та етапу, тепер є абсолютними посиланнями, щоб запобігти змінам, оскільки формула копіюється по таблиці та вниз.
Примітка: зведена таблиця також буде чудовим способом вирішення цієї проблеми.
