Splošna formula
=SUMPRODUCT(--(A1=things))>0
Povzetek
Če želite preizkusiti celico, da vidite, ali je enaka več stvari, lahko to storite s formulo, ki uporablja funkcijo SUMPRODUCT.
Kontekst
Recimo, da imate seznam besedilnih nizov v obsegu B5: B11 in želite preizkusiti vsako celico na drugem seznamu stvari v obsegu E5: E9. Z drugimi besedami, za vsako celico v B5: B11 želite vedeti: ali je ta celica enaka kateremu koli elementu v E5: E9?
Lahko začnete graditi veliko formulo, ki temelji na ugnezdenih stavkih IF, vendar je matrična formula, ki temelji na SUMPRODUCT, preprostejši in čistejši pristop.
Rešitev
Rešitev je ustvariti formulo, ki bo preizkusila več vrednosti in vrnila seznam TRUE / FALSE vrednosti. Ko imamo to, lahko ta seznam (dejansko matriko) obdelamo s SUMPRODUCT.
Formula, ki jo uporabljamo, je videti takole:
=SUMPRODUCT(--(B5=things))>0
Pojasnilo
Ključ je ta delček:
--(B5=things)
ki preprosto primerja vrednost v B5 z vsako vrednostjo v imenovanem obsegu "stvari". Ker B5 primerjamo z matriko (tj. Imenovani obseg "stvari", E5: E11), bo rezultat matrika TRUE / FALSE vrednosti, kot je ta:
(TRUE; FALSE; FALSE; FALSE; FALSE)
Če imamo v matriki celo eno TRUE, vemo, da je B5 enaka vsaj eni stvari na seznamu, zato za prisilitev vrednosti TRUE / FALSE na 1s in 0s uporabimo dvojni negativ (-, imenovan tudi dvojni unarno). Po tej prisili imamo naslednje:
(1; 0; 0; 0; 0)
Zdaj rezultat obdelamo s SUMPRODUCT, ki bo sestavil elemente v matriki. Če dobimo rezultat, ki ni nič, imamo vsaj eno ujemanje, zato uporabimo> 1 za vsiljevanje končnega rezultata TRUE ali FALSE.
S težko kodiranim seznamom
Ni zahteve, da za seznam stvari 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ča", "modra", "zelena") takole:
--(B5=("red","blue","green"))
Ukvarjanje z dodatnimi presledki
Če celice, ki jih preizkušate, vsebujejo dodaten prostor, se ne bodo pravilno ujemale. Če želite odstraniti ves odvečni prostor, lahko formulo spremenite tako, da bo uporabila funkcijo TRIM tako:
=SUMPRODUCT(--(TRIM(A1)=things))>0
