Üldine valem
=SUMPRODUCT(--(A1=things))>0
Kokkuvõte
Kui soovite lahtrit testida, et näha, kas see võrdub ühe mitme asjaga, saate seda teha valemiga, mis kasutab funktsiooni SUMPRODUCT.
Sisu
Oletame, et teil on nimekiri tekstistringidest vahemikus B5: B11 ja soovite testida iga lahtrit teise nimekirjaga asjadest vahemikus E5: E9. Teisisõnu, iga lahtris B5: B11 soovite teada: kas see lahter võrdub mõne punktiga E5: E9 kuuluva asjaga?
Võite alustada sisestatud IF-i lausete põhjal suure valemi koostamist, kuid SUMPRODUCT-il põhinev massiivivalem on lihtsam ja puhtam lähenemine.
Lahendus
Lahendus on luua valem, mis testib mitut väärtust ja tagastab TÕENE / VALE väärtuste loendi. Kui see on olemas, saame selle loendi (tegelikult massiivi) töödelda SUMPRODUCT-iga.
Valem, mida kasutame, näeb välja selline:
=SUMPRODUCT(--(B5=things))>0
Selgitus
Peamine on see koodilõik:
--(B5=things)
mis lihtsalt võrdleb väärtust B5-s iga nimetatud vahemiku "asjad" väärtusega. Kuna me võrdleme B5 massiiviga (st nimega vahemik "asjad", E5: E11), siis saab tulemuseks massiivi TRUE / FALSE väärtusi järgmiselt:
(TÕENE; VALE; VÄÄR; VÄÄR; VALE)
Kui meil on massiivis kasvõi üks TRUE, siis teame, et B5 võrdub loendis vähemalt ühe asjaga, nii et TRUE / FALSE väärtuste sundimiseks 1s ja 0s kasutame topeltnegatiivi (-, mida nimetatakse ka double unaarne). Pärast seda sundi on meil järgmine:
(1; 0; 0; 0; 0)
Nüüd töötleme tulemust SUMPRODUCTiga, mis liidab massiivi elemendid. Kui saame nullist erineva tulemuse, on meil vähemalt üks matš, seega kasutame> 1, et sundida kas TÕENE või VÄÄR lõpptulemus.
Kõvakodeeritud loendiga
Asjade loendi jaoks ei ole nõutav vahemiku kasutamine. Kui otsite vaid vähest arvu asju, võite kasutada massiivivormingus loendit, mida nimetatakse massiivikonstandiks. Näiteks kui otsite ainult punaseid, siniseid ja rohelisi värve, saate kasutada (punane, sinine, roheline) järgmiselt:
--(B5=("red","blue","green"))
Lisaruumidega tegelemine
Kui testitavad lahtrid sisaldavad lisaruumi, ei sobi need õigesti. Kogu lisaruumi eemaldamiseks saate valemit muuta funktsiooni TRIM kasutamiseks nii:
=SUMPRODUCT(--(TRIM(A1)=things))>0
