„Excel“ formulė: langelis yra lygus vienam iš daugelio dalykų

Turinys

Bendroji formulė

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

Santrauka

Jei norite patikrinti langelį, kad pamatytumėte, ar jis lygus vienam iš kelių dalykų, galite tai padaryti naudodami formulę, kurioje naudojama funkcija SUMPRODUCT.

Kontekstas

Tarkime, kad turite B5: B11 diapazono tekstinių eilučių sąrašą ir norite išbandyti kiekvieną langelį pagal kitą E5: E9 diapazone esančių dalykų sąrašą. Kitaip tariant, kiekvienai B5: B11 ląstelei norite žinoti: ar ši ląstelė prilygsta bet kuriam iš E5: E9 dalykų?

Galite pradėti kurti didelę formulę, pagrįstą įdėtais IF teiginiais, tačiau masyvo formulė, pagrįsta SUMPRODUCT, yra paprastesnis, švaresnis būdas.

Sprendimas

Sprendimas yra sukurti formulę, kuri patikrins kelias vertes ir pateiks TRUE / FALSE reikšmių sąrašą. Kai tai turėsime, galime apdoroti tą sąrašą (iš tikrųjų masyvą) naudodami „SUMPRODUCT“.

Mūsų naudojama formulė atrodo taip:

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

Paaiškinimas

Svarbiausias yra šis fragmentas:

--(B5=things)

kuris paprasčiausiai palygina B5 reikšmę su kiekviena įvardyto diapazono „daiktų“ verte. Kadangi mes lyginame B5 su masyvu (ty pavadintu diapazonu „dalykai“, E5: E11), rezultatas bus toks: TRUE / FALSE reikšmių masyvas:

(TIESA; NETIESA; NETIESA; NETIESA; NETIESA)

Jei masyve turime nors vieną TIESĄ, žinome, kad B5 yra lygus bent vienam dalykui sąraše, todėl norėdami priversti TIKRO / NETIESA reikšmes į 1s ir 0s, naudojame dvigubą neigiamą (-, dar vadinamą dviguba vienarūšis). Po šios prievartos turime tai:

(1; 0; 0; 0; 0)

Dabar mes apdorojame rezultatą naudodami SUMPRODUCT, kuris sujungs masyvo elementus. Jei gauname bet kokį rezultatą, kuris nėra nulis, turime bent vieną mačą, todėl mes naudojame> 1, kad priverstume galutinį rezultatą iš TRUE arba FALSE.

Su sunkiai užkoduotu sąrašu

Nereikalaujama, kad daiktų sąraše naudotumėte diapazoną. Jei ieškote tik nedaugelio dalykų, galite naudoti masyvo formato sąrašą, kuris vadinamas masyvo konstanta. Pvz., Jei ieškote tik raudonos, mėlynos ir žalios spalvos, galite naudoti („raudona“, „mėlyna“, „žalia“) taip:

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

Darbas su papildomomis erdvėmis

Jei bandomose ląstelėse yra papildomos vietos, jos nesutaps tinkamai. Norėdami ištrinti visą papildomą vietą, galite modifikuoti formulę taip, kad būtų naudojama TRIM funkcija: