Všeobecný vzorec
=SUMPRODUCT(--(A1=things))>0
Zhrnutie
Ak chcete bunku otestovať, či sa rovná jednej z niekoľkých vecí, môžete to urobiť pomocou vzorca, ktorý používa funkciu SUMPRODUCT.
Kontext
Povedzme, že máte zoznam textových reťazcov v rozsahu B5: B11 a chcete každú bunku otestovať s iným zoznamom vecí v rozsahu E5: E9. Inými slovami, pre každú bunku v B5: B11 chcete vedieť: rovná sa táto bunka niektorej z vecí v E5: E9?
Mohli by ste začať vytvárať veľký vzorec založený na vnorených príkazoch IF, ale vzorec poľa založený na SUMPRODUCT je jednoduchší a čistejší prístup.
Riešenie
Riešením je vytvorenie vzorca, ktorý otestuje viac hodnôt a vráti zoznam TRUE / FALSE hodnôt. Keď to máme, môžeme tento zoznam (vlastne pole) spracovať pomocou SUMPRODUCT.
Vzorec, ktorý používame, vyzerá takto:
=SUMPRODUCT(--(B5=things))>0
Vysvetlenie
Kľúčom je tento úryvok:
--(B5=things)
ktorá jednoducho porovnáva hodnotu v B5 s každou hodnotou v pomenovanom rozsahu „things“. Pretože porovnávame B5 s poľom (tj. Pomenovaný rozsah „things“, E5: E11), výsledkom bude pole TRUE / FALSE hodnôt, ako je tento:
(PRAVDA; NEPRAVDA; NEPRAVDA; NEPRAVDA; NEPRAVDA)
Ak máme v poli čo i len jednu hodnotu TRUE, vieme, že B5 sa rovná aspoň jednej veci v zozname, takže na vynútenie hodnôt TRUE / FALSE na 1 s a 0 s použijeme dvojitý zápor (-, tiež nazývaný dvojitý unárny). Po tomto nátlaku máme toto:
(1; 0; 0; 0; 0)
Teraz spracujeme výsledok pomocou SUMPRODUCT, ktorý sčíta prvky v poli. Ak dostaneme akýkoľvek nenulový výsledok, máme aspoň jednu zhodu, takže pomocou> 1 vynútime konečný výsledok TRUE alebo FALSE.
S pevne zakódovaným zoznamom
Nie je potrebné, aby ste pre svoj zoznam vecí používali rozsah. Ak hľadáte iba malý počet vecí, môžete použiť zoznam vo formáte poľa, ktorý sa nazýva konštanta poľa. Napríklad, ak hľadáte iba červenú, modrú a zelenú farbu, môžete použiť farbu („červená“, „modrá“, „zelená“) takto:
--(B5=("red","blue","green"))
Riešenie ďalších priestorov
Ak bunky, ktoré testujete, obsahujú nejaké miesto navyše, nebudú sa správne zhodovať. Ak chcete odstrániť všetok extra priestor, môžete upraviť vzorec tak, aby používal funkciu TRIM, napríklad takto:
=SUMPRODUCT(--(TRIM(A1)=things))>0
