Santrauka
Norėdami sukurti dvipusę suvestinę lentelę, kurioje apibendrinami skaitiniai duomenys pagal daugiau nei vieną kriterijų, galite naudoti funkciją SUMIFs. Parodytame pavyzdyje H5 formulė, nukopijuota H5: K7 diapazone, yra:
=SUMIFS(value,name,$G5,stage,H$4)
kur vertė (C5: C15), pavadinimas (B5: B15) ir pakopa (D5: D15) yra įvardijami diapazonai. Rezultatas yra lentelė, kurioje pateikiamos kiekvieno vardo suvestinės pagal etapus.
Paaiškinimas
Funkcija SUMIFS skirta susumuoti skaitines vertes pagal kelis kriterijus.
Pateiktame pavyzdyje B5: E15 diapazono duomenys parodo pardavimo vamzdyną, kuriame kiekviena eilutė yra galimybė, priklausanti pardavėjui, konkrečiame etape. H5 formulė yra:
=SUMIFS(value,name,$G5,stage,H$4)
Pirmoje formulės dalyje pateikiamos pardavėjo galimybės:
=SUMIFS(value,name,$G5 // sum by name
- Sumos diapazonas yra įvardytos diapazono vertės
- 1 kriterijų diapazonas yra pavadintas diapazono pavadinimas
- 1 kriterijus gaunamas iš ląstelės G5
Atkreipkite dėmesį, kad $ G5 yra mišri nuoroda, kai stulpelis užrakintas ir eilutė yra santykinė. Tai leidžia formulę keisti prireikus, kai formulė nukopijuojama visoje lentelėje.
Kitas diapazonas / kriterijų pora SUMIFS, suma pagal etapus:
stage,H$4 // sum by stage
- 2 kriterijų diapazonas yra įvardytas diapazono etapas
- 2 kriterijus yra H $ 4
Vėlgi, H $ 4 yra mišri nuoroda, kai stulpelio santykis ir eilutė užrakinta. Tai leidžia kriterijams pasirinkti etapo vertes 4 eilutėje, kai formulė nukopijuojama lentelėje ir žemyn.
Atsižvelgiant į abu kriterijus, funkcija SUMIFS teisingai apibendrina galimybes pagal pavadinimą ir etapą.
Be vardų diapazonų
Šiame pavyzdyje pavadinti diapazonai naudojami tik patogumui. Be įvardytų diapazonų ekvivalentinė formulė yra:
=SUMIFS($C$5:$C$15,$B$5:$B$15,$G5,$D$5:$D$15,H$4)
Pavadinimo, vertės ir etapo nuorodų nuorodos dabar yra absoliučios nuorodos, kad būtų išvengta pakeitimų, nes formulė nukopijuojama lentelėje ir apačioje.
Pastaba: „Pivot“ lentelė taip pat būtų puikus būdas išspręsti šią problemą.
