Šajā rakstā mēs uzzināsim, kā programmā Excel saskaitīt rindas ar kritērijiem SUMPRODUCT.
Scenārijs:
Vienkārši sakot, strādājot ar datiem, dažreiz mums ir jāsaskaita šūnas, kurās 2 diapazoni atbilst kritērijiem. Nosacīto skaitīšanu var veikt, izmantojot funkciju COUNTIF, taču šī nav vienīgā funkcija, kas to var izdarīt. Funkcija SUMPRODUCT ir daudzpusīga funkcija, ko var izmantot, lai skaitītu ar kritērijiem, dažreiz pat labāk nekā skaitīšanas funkcija.
Kā atrisināt problēmu?
Šajā rakstā mums būs jāizmanto funkcija SUMPRODUCT. Tagad mēs izveidosim formulu no šīm funkcijām. Šeit mums tiek doti divu diapazonu dati, un mums ir jāskaita vērtības, kurās formula atbilst kritērijiem.
Vispārējā formula:
= SUMPRODUCT ( - (diapazons1 operators diapazons2))
diapazons1 : 1. diapazons
diapazons2 : 2. diapazons
operators: kritēriji, nosacījums, kas norādīts kā operators starp 2 diapazoniem
Piemērs:
To visu var būt mulsinoši saprast. Tātad, pārbaudīsim šo formulu, palaižot to zemāk redzamajā piemērā.
Šeit mums ir ieraksts par pēdējām 7 lielajām Premjerlīgas fanu klubu spēlēm.
Mūsu rīcībā ir pēdējo 7 spēļu rekords, kurā spēlē Mājas komanda, Pret komandu, Mājas un pretinieks.
Pirmkārt, mums ir jāatrod spēļu skaits, ko mājās ir uzvarējusi jebkura komanda.
Tagad mēs izmantosim šādu formulu, lai iegūtu to spēļu skaitu, kuras uzvarēja mājās.
Izmantojiet formulu:
= SUMPRODUCT ( - (E5: E11> F5: F11))
E5: E11 : 1. diapazons, mājās gūti vārti
F5: F11 : 2. diapazons, pretinieks guva vārtus
>: kritēriji, nosacījums norādīts kā lielāks nekā operators
Paskaidrojums:
- Šeit jāizpilda nosacījums, ka mājās gūtajiem vārtiem jābūt lielākiem par pretinieku gūtajiem.
- E5: E11 (diapazons1) jābūt lielākam par F5: F11 (diapazons2).
- -- operators izmantoja, lai pārvērstu patieso vērtību uz 1 un nepatiesu vērtību uz 0.
- Funkcija SUMPRODUCT atgrieztā masīvā iegūst 1s summu, kas būs nepieciešamo uzvaru skaits.
Šeit diapazons ir norādīts kā šūnu atsauce. Nospiediet Enter, lai iegūtu skaitli.
Kā redzat, kopējie mači, kuros mājiniekiem bija pārsvars un uzvarēja, ir 4 mači.
Iepriekš minētais piemērs bija vienkāršs. Tāpēc, lai būtu interesanti, mēs visos datos saskaitīsim, cik mačus komanda uzvarēja.
Pirmkārt, mums ir jāatrod spēļu skaits, ko mājās ir uzvarējusi jebkura komanda.
Tagad mēs izmantosim šādu formulu, lai iegūtu spēļu skaitu, Man. United uzvarēja.
Izmantojiet formulu:
= SUMPRODUCT ( - (C5: C11 = G5) * (E5: E11> F5: F11) + (D5: D11 = G5) * (E5: E11 <F5: F11))
Paskaidrojums:
- (C5: C11 = G5) * (E5: E11> F5: F11) pārbauda mājinieku kā vīrieti. United un guva vairāk vārtu nekā pretinieku komanda.
- (D5: D11 = G5) * (E5: E11 <F5: F11) pārbauda komandu pret Man. United un guva vairāk vārtu nekā mājinieki.
- Funkcija SUMPRODUCT atgrieztajā masīvā iegūst 1s summu, kas būs Man nepieciešamo uzvaru skaits. United.
Šeit diapazons ir norādīts kā šūnu atsauce. Nospiediet Enter, lai iegūtu skaitli.
Kā jūs varat redzēt kopējos mačus, kur Vīrietis. Apvienotā komanda uzvarēja 2 mačos.
Tālāk ir norādītas dažas novērošanas piezīmes.
Piezīmes:
- Formula darbojas tikai ar skaitļiem.
- Formula darbojas tikai tad, ja uzmeklēšanas tabulā nav dublikātu
- Funkcija SUMPRODUCT skaitliskas vērtības uzskata par 0.
- Funkcija SUMPRODUCT loģisko vērtību TRUE uzskata par 1 un False par 0.
- Argumentu masīvam jābūt tādā pašā garumā kā funkcijai
Ceru, ka šis raksts par to, kā atgriezt skaitu, ja ar SUMPRODUCT programmā Excel ir paskaidrojošs. Vairāk rakstu par SUMPRODUCT funkcijām meklējiet šeit. Lūdzu, kopīgojiet savu jautājumu zemāk komentāru lodziņā. Mēs jums palīdzēsim.
Funkcijas SUMPRODUCT izmantošana programmā Excel: Atgriež SUM pēc vērtību reizināšanas vairākos Excel masīvos.
COUNTIFS ar dinamisko kritēriju diapazonu : Saskaitiet šūnas, kas ir atkarīgas no citām šūnu vērtībām programmā Excel.
COUNTIFS atbilst diviem kritērijiem: Saskaitiet šūnas, kas atbilst diviem dažādiem kritērijiem Excel sarakstā.
COUNTIFS ar vai vairākiem kritērijiem : Saskaitiet šūnas, kurām ir vairāki kritēriji, kas atbilst, izmantojot funkciju VAI.
Funkcija COUNTIFS programmā Excel : Saskaitiet šūnas, kas ir atkarīgas no citām šūnu vērtībām.
Kā lietot Countif VBA programmā Microsoft Excel : Saskaitiet šūnas, izmantojot Visual Basic for Applications kodu.
Kā programmā Excel izmantot aizstājējzīmes : Saskaitiet šūnas, kas atbilst frāzēm, izmantojot Excel aizstājējzīmes
Populāri raksti
50 Excel saīsnes, lai palielinātu produktivitāti
Rediģējiet nolaižamo sarakstu
Absolūta atsauce programmā Excel
Ja ar nosacītu formatējumu
Ja ar aizstājējzīmēm
Vlookup pēc datuma
Pārvērst collas par pēdām un collas programmā Excel 2016
Pievienojieties Excel un uzvārdiem
Saskaitiet šūnas, kas atbilst A vai B