Vispārēja formula
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))=COUNTA(things)
Kopsavilkums
Ja vēlaties pārbaudīt šūnu, lai redzētu, vai tajā ir visi saraksta vienumi, to var izdarīt ar formulu, kas izmanto funkciju MEKLĒŠANA, izmantojot funkciju ISNUMBER, SUMPRODUCT un COUNTA palīdzību.
Konteksts
Pieņemsim, ka jums ir teksta virkņu saraksts diapazonā B5: B8, un jūs vēlaties uzzināt, vai šajās šūnās ir visi vārdi citā diapazonā E5: E7.
Jūs varētu izveidot formulu, kurā tiek izmantoti ligzdoti IF priekšraksti, lai pārbaudītu katru vienumu, taču tas nebūs labi mērogojams, ja jums ir daudz lietu, ko meklēt. Katru reizi, kad pievienojat meklējamo vārdu, jums būs jāpievieno vēl viens ligzdots IF un jāpielāgo iekavas.
Risinājums
Risinājums ir izveidot formulu, kas uzskaita visas spēles vienlaikus. Kad tas ir izdarīts, mēs vienkārši salīdzinām šo skaitu ar meklēto vienumu skaitu. Ja tie sakrīt, mēs zinām, ka šūna satur visus vienumus.
Parādītajā piemērā mūsu izmantotā formula ir:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))=COUNTA(things)
Paskaidrojums
Galvenais ir šis fragments:
ISNUMBER(SEARCH(things,B5)
Tas ir balstīts uz citu formulu (šeit detalizēti paskaidrots), kas vienkārši pārbauda, vai šūnā nav viena apakšvirsraksta. Ja šūnā ir apakšvirkne, formula atgriež vērtību TRUE. Ja nē, formula atgriež FALSE.
Tomēr, ja mēs tai pašai formulai piešķiram lietu sarakstu (šajā gadījumā mēs izmantojam nosaukto diapazonu, ko sauc par "lietām", E5: E7), tas mums atgriezīs TRUE / FALSE vērtību sarakstu, pa vienam katram . Rezultāts ir masīvs, kas izskatās šādi:
(PATIESA; PATIESA; PATIESA)
Kur katrs TRUE apzīmē atrastu vienumu un katrs FALSE apzīmē neatrastu vienumu.
Mēs varam piespiest TRUE / FALSE vērtības uz 1s un 0s ar dubultu negatīvu (-, sauktu arī par dubultu unāru):
--ISNUMBER(SEARCH(things,B5))
kas dod šādu masīvu:
(1; 1; 1)
Pēc tam mēs apstrādājam šo masīvu ar SUMPRODUCT, kas mums dos kopējo summu. Ja šī summa ir vienāda ar vienumu skaitu nosauktajā diapazonā "lietas", mēs zinām, ka esam atraduši visas lietas un varam atgriezt PATIESI. Tas, kā mēs to darām, ir tieši salīdzināt abus skaitļus. Izmantojot “COUNTA”, “lietās” tiek iegūts tukšo šūnu skaits:
=COUNTA(things)
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:
=SUMPRODUCT(--ISNUMBER(SEARCH(("yellow","green","dog"),B5)))=COUNTA(things)
