Viena problēma, kas programmā Excel rodas daudz, ir skaitīšana vai summēšana, pamatojoties uz vairākiem VAI nosacījumiem. Piemēram, varbūt jums ir jāanalizē dati un jāuzskaita pasūtījumi Sietlā vai Denverā, ja preces ir sarkanas, zilas vai zaļas? Tas var būt pārsteidzoši grūts, tāpēc, protams, tas ir labs izaicinājums!
Izaicinājums
Tālāk sniegtie dati norāda pasūtījumus, vienu pasūtījumu katrā rindā. Ir trīs atsevišķi izaicinājumi.
Kādas formulas F9, G9 un H9 pareizi uzskaitīs pasūtījumus ar šādiem nosacījumiem:
- F9 - Krekls vai Hoodie
- G9 - (krekls vai Hoodie) un (sarkans, zils vai zaļš)
- H9 - (krekls vai Hoodie) un (sarkans, zils vai zaļš) un (Denvera vai Sietla)
Zaļā ēnošana tiek lietota ar nosacītu formatējumu, un tā norāda atbilstošās vērtības katram OR kritēriju kopai katrā kolonnā.
Jūsu ērtībai ir pieejami šādi nosauktie diapazoni:
prece = B3: B16
krāsa = C3: C16
pilsēta = D3: D16
Darba lapa ir pievienota. Atstājiet savas atbildes zemāk kā komentārus!
Atbilde (noklikšķiniet, lai izvērstu)Mans risinājums izmanto SUMPRODUCT ar ISNUMBER un MATCH šādi:
=SUMPRODUCT( ISNUMBER(MATCH(item,("Tshirt","Hoodie"),0))* ISNUMBER(MATCH(color,("Red","Blue","Green"),0))* ISNUMBER(MATCH(city,("Denver","Seattle"),0)) )
Kurā tiks skaitīti pasūtījumi, kur…
- Vienums ir (krekls vai Hoodie) un
- Krāsa ir (sarkana, zila vai zaļa) un
- Pilsēta ir (Denvera vai Sietla)
Vairāki cilvēki arī ieteica to pašu pieeju. Man patīk šī struktūra, jo tā viegli mērogojas, lai apstrādātu vairāk kritēriju, kā arī darbojas ar šūnu atsaucēm (nevis kodētu vērtību vietā). Izmantojot šūnu atsauces, formula H9 ir:
=SUMPRODUCT( ISNUMBER(MATCH(item,F3:F4,0))* ISNUMBER(MATCH(color,G3:G5,0))* ISNUMBER(MATCH(city,H3:H4,0)) )
Šīs formulas atslēga ir ISNUMBER + MATCH konstrukcija. MATCH ir iestatīts "atpakaļ" - uzmeklēšanas vērtības nāk no datiem, un masīvam tiek izmantoti kritēriji. Rezultāts ir vienas kolonnas masīvs katru reizi, kad tiek izmantota MATCH. Šajā masīvā ir # N / A kļūdas (bez atbilstības) vai skaitļi (atbilstība), tāpēc ISNUMBER tiek izmantots, lai pārvērstu par Būla vērtībām TRUE un FALSE. Masīvu reizināšanas operācija TRUE FALSE vērtības kopā piespiež uz 1s un 0s, un galīgajā masīvā SUMPRODUCT iekšpusē ir 1s, kur rindas atbilst kritērijiem. Pēc tam SUMPRODUCT summē masīvu un atgriež rezultātu.
