Excel vzorec: Buňka se rovná jedné z mnoha věcí -

Obsah

Obecný vzorec

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

souhrn

Pokud chcete otestovat buňku a zjistit, zda se rovná jedné z několika věcí, můžete tak učinit pomocí vzorce, který používá funkci SUMPRODUCT.

Kontext

Řekněme, že máte seznam textových řetězců v rozsahu B5: B11 a chcete každou buňku otestovat s jiným seznamem věcí v rozsahu E5: E9. Jinými slovy, pro každou buňku v B5: B11 chcete vědět: rovná se tato buňka některé z věcí v E5: E9?

Mohli byste začít vytvářet velký vzorec založený na vnořených příkazech IF, ale vzorec pole založený na SUMPRODUCT je jednodušší a čistší přístup.

Řešení

Řešením je vytvořit vzorec, který otestuje více hodnot a vrátí seznam TRUE / FALSE hodnot. Jakmile to máme, můžeme tento seznam (vlastně pole) zpracovat pomocí SUMPRODUCT.

Vzorec, který používáme, vypadá takto:

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

Vysvětlení

Klíčem je tento úryvek:

--(B5=things)

který jednoduše porovnává hodnotu v B5 s každou hodnotou v pojmenovaném rozsahu „things“. Protože porovnáváme B5 s polem (tj. Pojmenovaný rozsah „things“, E5: E11), výsledkem bude pole TRUE / FALSE hodnot, jako je tato:

(PRAVDA; NEPRAVDA; NEPRAVDA; NEPRAVDA; NEPRAVDA)

Pokud máme v poli co i jen TRUE, víme, že B5 se rovná alespoň jedné věci v seznamu, takže k vynucení hodnot TRUE / FALSE na 1 s a 0 s použijeme dvojitý zápor (-, také nazývaný dvojitý unární). Po tomto nátlaku máme toto:

(1; 0; 0; 0; 0)

Nyní zpracujeme výsledek pomocí SUMPRODUCT, který sčítá prvky v poli. Pokud získáme jakýkoli nenulový výsledek, máme alespoň jednu shodu, takže použijeme> 1 k vynucení konečného výsledku TRUE nebo FALSE.

S pevně zakódovaným seznamem

Neexistuje žádný požadavek, abyste pro svůj seznam věcí použili rozsah. Pokud hledáte jen malý počet věcí, můžete použít seznam ve formátu pole, který se nazývá konstanta pole. Pokud například hledáte pouze červené, modré a zelené barvy, můžete použít („červená“, „modrá“, „zelená“) takto:

--(B5=("red","blue","green"))

Řešení dalších mezer

Pokud buňky, které testujete, obsahují nějaké další místo, nebudou se správně shodovat. Chcete-li odstranit veškerý další prostor, můžete upravit vzorec tak, aby používal funkci TRIM takto: