Excel

Bunka sa rovná jednej z mnohých vecí

Cell Equals One Many Things

Vzorec programu Excel: Bunka je jednou z mnohých vecíGenerický vzorec | _+_ | Zhrnutie

Ak chcete bunku otestovať, aby ste zistili, či sa zhoduje s jednou z niekoľkých vecí, môžete to urobiť pomocou vzorca, ktorý používa funkciu SUMPRODUCT.



priemerný vzorec programu Excel, ak nie je prázdny

Kontext

Povedzme, že máte zoznam textových reťazcov v rozsahu B5: B11 a chcete otestovať každú bunku proti inému zoznamu vecí v rozsahu E5: E9. Inými slovami, pre každú bunku v B5: B11 chcete vedieť: zodpovedá táto bunka niektorej z vecí v E5: E9?

Mohli by ste začať vytvárať veľký vzorec na základe vnorené príkazy IF , ale maticový vzorec založený na SUMPRODUCT je jednoduchší a čistejší prístup.





Riešenie

Riešením je vytvoriť vzorec, ktorý otestuje viacero hodnôt a vráti zoznam hodnôt PRAVDA / NEPRAVDA. Akonáhle to máme, môžeme tento zoznam (vlastne pole) spracovať pomocou SUMPRODUCT.

Vzorec, ktorý používame, vyzerá takto:



ako to rozdeliť v exceli
= SUMPRODUCT (--(A1=things))>0
Vysvetlenie

Kľúčom je tento úryvok:

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

ktorý jednoducho porovnáva hodnotu v B5 s každou hodnotou v pomenovanom rozsahu „vecí“. Pretože porovnávame B5 s poľom (t. J. Pomenovaný rozsah „veci“, E5: E11), výsledkom bude pole hodnôt PRAVDA / NEPRAVDA, ako je toto:

{TRUEFALSEFALSEFALSEFALSE}

Ak máme v poli čo i len jednu PRAVDU, vieme, že B5 sa rovná aspoň jednej veci v zozname, takže na vynútenie hodnôt PRAVDA / NEPRAVDA na 1 s a 0 s použijeme dvojitý zápor (-, tiež nazývaný dvojitý unárny). Po tomto nátlaku máme toto:

{10000}

ako urobiť priemerný vzorec v programe Excel

Teraz spracujeme výsledok pomocou SUMPRODUCT, ktorý sčíta prvky v poli. Ak dostaneme nenulový výsledok, máme aspoň jednu zhodu, takže použijeme> 1 na vynútenie konečného výsledku buď PRAVDIVÉ alebo NEPRAVDIVÉ.

S pevne zakódovaným zoznamom

V zozname vecí nie je potrebné používať 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. Ak hľadáte napríklad farby červenej, modrej a zelenej, môžete použiť {'red', 'blue', 'green'} takto:

 
--(B5=things)

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 priestor navyše, môžete vzorec upraviť tak, aby používal funkciu TRIM takto:

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


^