Šajā rakstā mēs iemācīsimies saskaitīt vairākus diapazonus ar vienu kritēriju programmā Microsoft Excel.
Scenārijs:
Vienkārši sakot, strādājot ar datu tabulām, dažreiz mums ir jāsaskaita šūnas, kurās kritērijiem atbilst vairāk nekā divi diapazoni. Programmā Excel varat veikt tādus uzdevumus kā darbības vairākos diapazonos, izmantojot tālāk izskaidroto formulu. Kritērijus var piemērot tekstam, skaitlim vai jebkurai daļējai atbilstībai programmā Excel. Kritēriji formulā, kas izpildīti, izmantojot operatorus. Operatoriem patīk vienāds ar ( = ), kas ir mazāks par ( <= ), lielāks nekā ( > ) vai nav vienāds ar ().
Kā atrisināt problēmu?
Lai atrisinātu šo problēmu, mums būs jāizmanto funkcija SUMPRODUCT. Tagad mēs izveidosim formulu no funkcijas. Šeit mums ir doti divi datu diapazoni, un mums ir jāsaskaita rindas, kas atbilst 3 kritērijiem. Funkcija SUMPRODUCT atgriež atbilstošo TRUE vērtību SUM (kā 1) un ignorē vērtības, kas atbilst FALSE vērtībām (kā 0) atgrieztajā masīvā
Vispārējā formula:
| = SUMPRODUCT ((rng_1 op_1 crit_1) + 0, (rng_2 op_2 crit_1) + 0, rng_2 op_2 kritiķis_1) + 0) |
rng: meklējamais diapazons
crit: piemērojamie kritēriji
op: kritēriju operators, nosacījums, kas norādīts kā operators starp diapazonu un kritērijiem
+0: pārvērš Būla vērtības binārajās (0 un 1).
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 jāatrod to rindu skaits, kas uzskaitītas diapazonā ar 3 nosacījumiem. Šeit ir saraksts ar diplomātu sanāksmēm, kas notika starp Indiju un ASV no 2014. gada. Tabulā parādīts prezidents / premjerministrs ar valsts marķējumu un gadu. Tabula ir arī sadalīta daļās, kas pārstāv mītnes valsti un viesu valstu sarakstu.
Tālāk uzskaitītie nosacījumi:
ASV prezidents "Baraks Obama apmeklēja Indiju ar problēmām, kas ir mazākas par 2.
Izmantojiet formulu:
| = SUMPRODUCT ((C4: C10 = "Baraks Obama") + 0, (F4: F10 = "Indija") + 0, (G4: G10 <2) + 0)) |
C4: C10 = "Baraks Obama": prezidents atbilst "Barack Obama" apmeklētāju sarakstā.
F4: F10 = "Indija": uzņēmējvalsts atbilst "Indijai".
G4: G10 <2: izdod mazāk par diviem.
+0: pārvērš Būla vērtības binārajās (0 un 1).
Šeit diapazons ir norādīts kā šūnu atsauce. Nospiediet Enter, lai iegūtu skaitli.
Kā redzat, tikai vienu reizi ASV prezidents Baraks Obama apmeklēja Indiju, kas notika 2015. gadā. Tas parāda, ka formula izraksta atbilstošā masīva reižu skaitu. Tā kā ir viens un tas pats gadījums, kad ASV prezidents Baraks Obama apmeklēja Indiju, kur arī jautājumi ir vienādi ar 1, kas ir mazāks par 2.
Ar vienādu ar kritērijiem:
Iepriekš minētais piemērs bija vienkāršs. Tāpēc, lai būtu interesanti, mēs pēc datiem saskaitīsim, cik reizes ASV, sākot ar 2014. gadu, uzņēma Indiju.
Tālāk uzskaitītie nosacījumi:
ASV uzņemtā Indija, kurai ir problēmas, ir 2.
Izmantojiet formulu:
| = SUMPRODUCT ((F4: F10 = "US") + 0, (D4: D10 = "India") + 0, (G4: G10 = 2) + 0) |
F4: F10 = "ASV": uzņēmējvalsts atbilst "ASV".
D4: D10 = "Indija": apmeklējuma valsts, kas atbilst "Indija".
G4: G10 = 2: jautājumi ir vienādi ar diviem.
+0: pārvērš Būla vērtības binārajās (0 un 1).
Šeit diapazons ir norādīts kā šūnu atsauce. Nospiediet Enter, lai iegūtu skaitli.
Kā redzat, ir 2 reizes, kad ASV uzņēma Indiju, un emisija ir vienāda ar divām. Tas parāda, ka formula iegūst atbilstošajā masīvā saskaņoto reižu skaitu. Tā kā ir piecas reizes, kad ASV uzņēma Indiju, bet problēmas bija 1 vai 3, bet šeit mums ir jāsaskaņo ar 2.
Ar lielākiem kritērijiem:
Šeit, lai būtu interesanti, mēs pēc datiem saskaitīsim, cik reizes ASV prezidents Donalds Tramps uzņēma Indijas premjerministru, sākot no 2014. gada.
Tālāk uzskaitītie nosacījumi:
ASV prezidents Donalds Tramps uzņēma Indiju, kuras problēmas ir lielākas par 1.
Izmantojiet formulu:
| = SUMPRODUCT ((C4: C10 = "Donald Trump") + 0, (F4: F10 = "India") + 0, (G4: G10> 1) + 0) |
F4: F10 = "ASV": uzņēmējs prezidents atbilst "Donald Trump".
D4: D10 = "Indija": apmeklējuma valsts, kas atbilst "Indija".
G4: G10 = 2: jautājumi ir vienādi ar diviem.
+0: pārvērš Būla vērtības binārajās (0 un 1).
Šeit diapazons ir norādīts kā šūnu atsauce. Nospiediet Enter, lai iegūtu skaitli.
Kā redzat, vienu reizi, kad ASV prezidents Donalds Tramps uzņēma Indiju, un jautājumi bija lielāki par diviem. Tas parāda, ka formula iegūst atbilstošajā masīvā saskaņoto reižu skaitu. Tā kā ir divas reizes, kad ASV prezidents Donalds Tramps uzņēma Indiju, bet jautājumi bija 1 vai 3, bet šeit mums ir nepieciešams, lai jautājumi būtu lielāki par 1, kas ir 3 meli 2019.
Ar jautājumiem, kas nav aplūkoti kritērijos:
Šeit, lai to būtu viegli un ērti saprast, mēs pēc datiem saskaitīsim, cik reizes ASV prezidents apmeklēja Indiju, sākot no 2014. gada.
Tālāk uzskaitītie nosacījumi:
ASV prezidents Indiju kopumā apmeklēja no 2014.
Izmantojiet formulu:
| = SUMPRODUCT ((F4: F10 = "Indija")+0, (D4: D10 = "ASV")+0) |
F4: F10 = "ASV": uzņēmējvalsts atbilst "ASV".
D4: D10 = "Indija": apmeklējuma valsts, kas atbilst "Indija".
G4: G10 = 2: jautājumi ir vienādi ar diviem.
+0: pārvērš Būla vērtības binārajās (0 un 1).
Šeit diapazons ir norādīts kā šūnu atsauce. Nospiediet Enter, lai iegūtu skaitli.
Kā redzat, 2 reizes, kur ASV apmeklēja Indiju, un jautājumi ir lielāki par diviem. Tas parāda, ka formula iegūst atbilstošajā masīvā saskaņoto reižu skaitu. Tā kā bija viena reize, kad ASV prezidents "Baraks Obama" 2015. gadā apmeklēja Indiju, un viena reize, kad ASV prezidents "Donald Trump" apmeklēja Indiju 2020.
Varat arī veikt diapazonus kā kritērijus. Saskaitiet šūnas, kurās 2 diapazoni atbilst kritērijiem. Uzziniet vairāk par Countif ar SUMPRODUCT programmā Excel šeit.
Tālāk ir norādītas dažas novērošanas piezīmes.
Piezīmes:
- Formula darbojas tikai ar skaitļiem.
- Formulas masīviem jābūt vienādiem garumā, jo formula atgriež kļūdu, ja tā nav.
- Funkcija SUMPRODUCT neskaitliskas 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 vienāda izmēra, pretējā gadījumā funkcija atgriež kļūdu.
- Funkcija SUMPRODUCT atgriež summu pēc atsevišķu produktu uzņemšanas attiecīgajā masīvā.
Ceru, ka šis raksts par vairāku diapazonu saskaitīšanu ar vienu kritēriju programmā Microsoft Excel ir paskaidrojošs. Vairāk rakstu par vērtību un saistīto Excel formulu aprēķināšanu meklējiet šeit. Ja jums patika mūsu emuāri, kopīgojiet to ar saviem draugiem Facebook. Un arī jūs varat sekot mums Twitter un Facebook. Mēs labprāt uzklausītu jūsu viedokli, dariet mums zināmu, kā mēs varam uzlabot, papildināt vai ieviest jauninājumus mūsu darbā un uzlabot to jūsu labā. Rakstiet mums e -pasta vietnē.
Kā lietot funkciju COUNTIF programmā Excel : Saskaitiet vērtības ar nosacījumiem, izmantojot šo apbrīnojamo funkciju. Jums nav jāfiltrē dati, lai saskaitītu noteiktas vērtības. Skaitītāja funkcija ir būtiska, lai sagatavotu informācijas paneli.
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, atlasot kritērijus no opciju saraksta Excel kritēriju šūnā, izmantojot datu validācijas rīku.
COUNTIFS divu kritēriju atbilstība : vairāki kritēriji atbilst dažādiem tabulas sarakstiem, izmantojot funkciju COUNTIFS programmā Excel
COUNTIFS ar vai vairākiem kritērijiem : atbilst diviem vai vairākiem nosaukumiem vienā sarakstā, izmantojot programmā Excel lietotos VAI kritērijus.
Kā lietot Countif VBA programmā Microsoft Excel : Saskaitiet šūnas ar kritērijiem, izmantojot Visual Basic for Applications kodu Excel makro.
Kā programmā Excel izmantot aizstājējzīmes : Saskaitiet šūnas, kas atbilst frāzēm teksta sarakstos, izmantojot aizstājējzīmes ( * , ? , ~ ) programmā Excel
Populāri raksti:
Kā lietot funkciju IF programmā Excel : IF priekšraksts programmā Excel pārbauda nosacījumu un atgriež noteiktu vērtību, ja nosacījums ir TRUE, vai atgriež citu noteiktu vērtību, ja FALSE.
Kā lietot funkciju VLOOKUP programmā Excel : Šī ir viena no visbiežāk izmantotajām un populārākajām Excel funkcijām, kas tiek izmantota, lai meklētu vērtību no dažādiem diapazoniem un lapām.
Kā lietot SUMIF funkciju programmā Excel : Šī ir vēl viena būtiska informācijas paneļa funkcija. Tas palīdz apkopot vērtības noteiktos apstākļos.
Kā lietot funkciju COUNTIF programmā Excel : Saskaitiet vērtības ar nosacījumiem, izmantojot šo apbrīnojamo funkciju. Jums nav jāfiltrē dati, lai saskaitītu noteiktas vērtības. Skaitītāja funkcija ir būtiska, lai sagatavotu informācijas paneli.