Bendroji formulė
=SUMPRODUCT(data*(range1=criteria1)*(range2=criteria2))
Santrauka
Norėdami susumuoti vertes atitinkančiuose stulpeliuose ir eilutėse, galite naudoti funkciją SUMPRODUCT. Parodytame pavyzdyje formulė J6 yra:
=SUMPRODUCT(data*(codes=J4)*(days=J5))
kur duomenys (C5: G14), dienos (B5: B14) ir kodai (C4: G4) yra pavadinti diapazonais.
Paaiškinimas
SUMPRODUCT funkcija gali valdyti masyvus savaime, nereikalaujant valdymo poslinkio įvedimo.
Šiuo atveju visas nurodyto diapazono duomenų vertes padauginame iš dviejų išraiškų, kurios filtruoja reikšmes, kurios nėra įdomios. Pirmoji išraiška taiko filtrą, pagrįstą kodais:
(codes=J4)
Kadangi J4 yra „A002“, išraiška sukuria tokių TRUE FALSE reikšmių masyvą:
(FALSE,TRUE,FALSE,FALSE,FALSE)
Antrasis išraiškos filtras dieną:
(days=J5)
Kadangi J4 yra „Trečiadienis“, išraiška sukuria tokių TRUE FALSE reikšmių masyvą:
(FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Programoje „Excel“ TRUE FALSE reikšmės automatiškai priverčiamos 1 ir 0 reikšmėmis bet kuria matematikos operacija, todėl daugybos operacija aukščiau pateiktus masyvus priverčia į vienetus ir nulius ir sukuria 2D masyvą, kurio matmenys yra tokie patys kaip ir pradinių duomenų. Procesą galima vizualizuoti taip, kaip parodyta žemiau:
Galiausiai „SUMPRODUCT“ pateikia visų galutinio masyvo elementų sumą 9.
Skaičiuokite, o ne sumą
Jei norite suskaičiuoti atitinkamas reikšmes, o ne susumuoti, formulę galite sutrumpinti taip:
=SUMPRODUCT((codes=J4)*(days=J5)) // count only
Atminkite, kad į šį skaičių bus įtraukti tušti langeliai.
Pastabos
- Nors pavyzdyje rodomas tik vienas suderintas stulpelis, ši formulė teisingai sujungs kelis suderintus stulpelius.
- Jei reikia suderinti tik stulpelius (ne eilutes), galite naudoti tokią formulę.
- Norėdami suderinti tik eilutes, galite naudoti funkciją COUNTIFS.
