Bendroji formulė
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))=COUNTA(things)
Santrauka
Jei norite išbandyti langelį, kad pamatytumėte, ar jame yra visi sąrašo elementai, galite tai padaryti naudodami formulę, kurioje naudojama funkcija PAIEŠKA, naudodamasis funkcijomis ISNUMBER, SUMPRODUCT ir COUNTA.
Kontekstas
Tarkime, kad turite teksto eilučių, esančių diapazone B5: B8, sąrašą ir norite sužinoti, ar šiose langeliuose yra visi žodžiai kitame diapazone - E5: E7.
Galite sukurti formulę, kurioje būtų naudojami įdėti IF sakiniai, kad patikrintumėte kiekvieną elementą, tačiau tai nebus gerai, jei turite daug dalykų, kurių reikia ieškoti. Kiekvieną kartą, kai pridedate ieškomą žodį, turėsite pridėti dar vieną įdėtą IF ir pakoreguoti skliaustus.
Sprendimas
Sprendimas yra sukurti formulę, kurioje būtų skaičiuojamos visos rungtynės vienu metu. Kai tai turėsime, paprasčiausiai palyginsime tą skaičių su ieškomų daiktų skaičiumi. Jei jie sutampa, žinome, kad langelyje yra visi elementai.
Parodytame pavyzdyje formulė, kurią naudojame:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))=COUNTA(things)
Paaiškinimas
Svarbiausias yra šis fragmentas:
ISNUMBER(SEARCH(things,B5)
Tai pagrįsta kita formule (išsamiai paaiškinta čia), kuri paprasčiausiai patikrina langelį, ar nėra vieno pakraščio. Jei langelyje yra poskyris, formulė grąžina TRUE. Jei ne, formulė pateikia FALSE.
Tačiau jei mes pateiksime tą pačią formulę dalykų sąrašą (šiuo atveju mes naudojame pavadintą diapazoną, vadinamą „daiktais“, E5: E7), tai mums grąžins TRUE / FALSE reikšmių sąrašą, po vieną kiekvienam elementui. . Rezultatas yra masyvas, kuris atrodo taip:
(TIKRA; TIESA; TIESA)
Kur kiekvienas TRUE reiškia rastą daiktą, o kiekvienas FALSE reiškia nerastą daiktą.
Mes galime priversti TRUE / FALSE reikšmes į 1s ir 0s su dviguba neigiama (-, dar vadinama dviguba unarija):
--ISNUMBER(SEARCH(things,B5))
iš kurio gaunamas toks masyvas:
(1; 1; 1)
Tada mes apdorosime šį masyvą su SUMPRODUCT, kuris suteiks mums bendrą sumą. Jei ši suma yra lygi elementų skaičiui nurodytame diapazone „daiktai“, žinome, kad radome visus dalykus ir galime grąžinti TIKRĄ. Tai darome tiesiogiai lygindami abu skaičius. Naudojant COUNTA gauname ne tuščių langelių skaičių „daiktuose“:
=COUNTA(things)
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:
=SUMPRODUCT(--ISNUMBER(SEARCH(("yellow","green","dog"),B5)))=COUNTA(things)
