Excel

Celica je ena izmed mnogih stvari

Cell Equals One Many Things

Formula Excel: Celica je ena od mnogih stvariSplošna formula | _+_ | Povzetek

Če želite celico preizkusiti, če je enaka eni od več stvari, lahko to storite s formulo, ki uporablja funkcijo SUMPRODUCT.





z uporabo več stavkov if v Excelu

Kontekst

Recimo, da imate seznam besedilnih nizov v območju B5: B11 in želite vsako celico preizkusiti na drugem seznamu stvari v območju E5: E9. Z drugimi besedami, za vsako celico v B5: B11 želite vedeti: ali je ta celica enaka kateri koli stvari v E5: E9?

Na podlagi tega bi lahko začeli graditi veliko formulo ugnezdeni stavki IF , vendar je matrična formula, ki temelji na SUMPRODUCT, enostavnejši in čistejši pristop.





Rešitev

Rešitev je ustvariti formulo, ki bo preizkusila več vrednosti in vrnila seznam vrednosti TRUE / FALSE. Ko to imamo, lahko ta seznam (dejansko matriko) obdelamo s SUMPRODUCT.

Formula, ki jo uporabljamo, izgleda takole:



kako narediti grafe stolpcev v
= SUMPRODUCT (--(A1=things))>0
Pojasnilo

Ključ je ta delček:

 
= SUMPRODUCT (--(B5=things))>0

ki preprosto primerja vrednost v B5 z vsako vrednostjo v imenovanem obsegu 'stvari'. Ker primerjamo B5 z matriko (tj. Imenovano območje 'stvari', E5: E11), bo rezultat niz vrednosti TRUE / FALSE, kot je ta:

{TRUEFALSEFALSEFALSEFALSE}

Če imamo v matriki celo eno TRUE, vemo, da je B5 enaka vsaj eni stvari na seznamu, zato za vsiljevanje vrednosti TRUE / FALSE na 1s in 0s uporabimo dvojni minus (-, imenovan tudi dvojni enotno). Po tej prisili imamo naslednje:

{10000}

primerjajte tri stolpce v Excelu in vrnite vrednost

Zdaj obdelamo rezultat s SUMPRODUCT, ki bo sešteval elemente v matriki. Če dobimo rezultat, ki ni nič, imamo vsaj eno ujemanje, zato uporabimo> 1, da vsilimo končni rezultat TRUE ali FALSE.

S težko kodiranim seznamom

Za vaš seznam stvari ni zahteve, da uporabite obseg. Če iščete le majhno število stvari, lahko uporabite seznam v obliki matrike, ki se imenuje konstanta matrike. Če na primer iščete samo rdečo, modro in zeleno barvo, lahko uporabite {'rdečo', 'modro', 'zeleno'} tako:

 
--(B5=things)

Ukvarjanje z dodatnimi prostori

Če celice, ki jih testirate, vsebujejo dodaten prostor, se ne bodo pravilno ujemale. Če želite odstraniti ves dodatni prostor, lahko formulo spremenite tako, da uporabite funkcijo TRIM tako:

 
--(B5={'red','blue','green'})
Avtor Dave Bruns


^