Vispārēja formula
=SUMPRODUCT(--(A1=things))>0
Kopsavilkums
Ja vēlaties pārbaudīt šūnu, lai pārliecinātos, vai tā ir vienāda no vairākām lietām, to varat izdarīt ar formulu, kurā tiek izmantota funkcija SUMPRODUCT.
Konteksts
Pieņemsim, ka jums ir teksta virkņu saraksts diapazonā B5: B11, un jūs vēlaties pārbaudīt katru šūnu salīdzinājumā ar citu lietu sarakstu diapazonā E5: E9. Citiem vārdiem sakot, par katru šūnu B5: B11 vēlaties uzzināt: vai šī šūna ir vienāda ar kādu no E5: E9 lietām?
Jūs varētu sākt veidot lielu formulu, kuras pamatā ir ligzdoti IF priekšraksti, bet masīva formula, kuras pamatā ir SUMPRODUCT, ir vienkāršāka, tīrāka pieeja.
Risinājums
Risinājums ir izveidot formulu, kas pārbaudīs vairākas vērtības un atgriezīs TRUE / FALSE vērtību sarakstu. Kad tas būs pieejams, mēs varēsim apstrādāt šo sarakstu (faktiski masīvu) ar SUMPRODUCT.
Mūsu izmantotā formula izskatās šādi:
=SUMPRODUCT(--(B5=things))>0
Paskaidrojums
Galvenais ir šis fragments:
--(B5=things)
kas vienkārši salīdzina vērtību B5 ar katru vērtību nosauktajā diapazonā "lietas". Tā kā mēs salīdzinām B5 ar masīvu (ti, nosaukto diapazonu "lietas", E5: E11), rezultāts būs šāds: TRUE / FALSE masīvs:
(PATIESA; FALSE; FALSE; FALSE; FALSE)
Ja masīvā mums ir pat viena PATIESĪBA, mēs zinām, ka B5 ir vienāds ar vismaz vienu lietu sarakstā, tāpēc, lai TRUE / FALSE vērtības piespiestu 1s un 0s, mēs izmantojam dubultnegatīvu (-, sauktu arī par dubultu unāra). Pēc šīs piespiešanas mums ir šāds:
(1; 0; 0; 0; 0)
Tagad mēs apstrādājam rezultātu ar SUMPRODUCT, kas summēs elementus masīvā. Ja mēs iegūstam rezultātu, kas nav nulle, mums ir vismaz viena spēle, tāpēc mēs izmantojam> 1, lai piespiestu gala rezultātu vai nu TRUE, vai FALSE.
Ar grūti kodētu sarakstu
Nav prasību, lai lietotu diapazonu lietu sarakstam. Ja meklējat tikai nelielu skaitu lietu, varat izmantot masīva formāta sarakstu, ko sauc par masīva konstanti. Piemēram, ja jūs meklējat tikai sarkanās, zilās un zaļās krāsas, varat izmantot (“sarkans”, “zils”, “zaļš”) šādi:
--(B5=("red","blue","green"))
Darbs ar papildu atstarpēm
Ja pārbaudāmajās šūnās ir papildu vieta, tās nederēs pareizi. Lai noņemtu visu papildu vietu, varat modificēt formulu, lai izmantotu funkciju TRIM, piemēram:
=SUMPRODUCT(--(TRIM(A1)=things))>0
