Yleinen kaava
=SUMPRODUCT((rng1=crit1)*ISNA(MATCH(rng2,crit2,0)))
Yhteenveto
Jos haluat laskea useilla ehdoilla, mukaan lukien logiikka EI yhdelle monista asioista, voit käyttää SUMPRODUCT-funktiota yhdessä MATCH- ja ISNA-funktioiden kanssa.
Esitetyssä esimerkissä kaava G8: ssa on:
=SUMPRODUCT((gender=F4)*ISNA(MATCH(group,G4:G5,0)))
Missä "sukupuoli" on nimetty alue C4: C12 ja "ryhmä" on nimetty alue D4: D12.
Huomaa: MATCH ja ISNA sallivat kaavan skaalautumisen helposti käsittelemään enemmän poissulkemisia, koska voit helposti laajentaa aluetta sisällyttämällä muita "EI" -arvoja.
Selitys
SUMPRODUCTS: n sisällä oleva ensimmäinen lauseke testaa sarakkeessa C olevat sukupuoliarvot F4: n arvoon "Uros" verrattuna:
(gender=F4)
Tuloksena on joukko TOSI EPÄTOSI arvoja, kuten tämä:
(TOSI; EPÄTOSI; TOSI; EPÄTOSI; TOSI; TOSI; TOSI; TOSI; EPÄTOSI)
Missä TOSI vastaa "Mies".
SUMPRODUCTS: n sisällä oleva toinen lauseke testaa sarakkeessa D, Group olevat arvot G4: n arvoihin nähden: G5, "A" ja "B". Tämä testi hoidetaan MATCH: lla ja ISNA: lla näin:
ISNA(MATCH(group,G4:G5,0))
MATCH-funktiota käytetään sovittamaan kaikki nimetyn alueen "ryhmä" arvot G4: n arvoihin: G5, "A" ja "B". Jos ottelu onnistuu, MATCH palauttaa numeron. Jos MATCH epäonnistuu, MATCH palauttaa # N / A. Tuloksena on tällainen taulukko:
(1; 2; # N / A; 1; 2; # N / A; 1; 2; # N / A)
Koska # N / A-arvot vastaavat arvoa "ei A tai B", ISNA: ta käytetään ryhmän "kääntämiseen":
(EPÄTOSI; EPÄTOSI; TOSI; EPÄTOSI; EPÄTOSI; TOSI; EPÄTOSI; EPÄTOSI; TOSI)
TOSI vastaa nyt "ei A tai B".
SUMPRODUCTin sisällä nämä kaksi matriisitulosta kerrotaan yhdessä, mikä luo yhden numeerisen taulukon SUMPRODUCTin sisälle:
SUMPRODUCT((0;0;1;0;0;1;0;0;0))
SUMPRODUCT palauttaa sitten summan, 2, joka edustaa "2 urosta, jotka eivät kuulu ryhmään A tai B".
