Bendroji formulė
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
Santrauka
Norėdami suskaičiuoti langelius, kuriuose nėra daug skirtingų eilučių, galite naudoti gana sudėtingą formulę, pagrįstą funkcija MMULT. Parodytame pavyzdyje formulė F5 yra:
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
kur „duomenys“ yra pavadintas diapazonas B5: B14, o „neįtraukti“ - pavadintas diapazonas D5: D7.
Pastaba: tai yra masyvo formulė, kurią reikia įvesti su „Control“ + „Shift“ + „Enter“
Įvadas
Šią formulę apsunkina reikalavimas „yra“. Jei jums tiesiog reikia formulės, kad suskaičiuotumėte langelius, kurie daugeliui dalykų nėra * lygūs, galite naudoti paprastesnę formulę, pagrįstą funkcija MATCH. Be to, jei turite ribotą eilučių skaičių, kurias galite išskirti, galite naudoti funkciją COUNTIFS taip:
=COUNTIFS(data,"*pink*",data,"*orange*",data,"*black*")
Tačiau taikydami šį metodą, kiekvienai eilutei, kurią norite išskirti, turite įvesti naują diapazono / kriterijų argumentų porą. Priešingai, toliau paaiškinta formulė gali apdoroti daugybę eilučių, kurias reikia išskirti, įvestas tiesiai į darbalapį.
Pagaliau ši formulė yra sudėtinga. Praneškite, jei turite paprastesnę formulę, kurią galite pasiūlyti :)
Paaiškinimas
Šios formulės esmė yra ISNUMBER ir SEARCH:
ISNUMBER(SEARCH(TRANSPOSE(exclude),data))
Čia mes perkeliame pavadinto diapazono „neįtraukti“ elementus, tada pateikiame rezultatą „SEARCH“ kaip „rasti tekstą“, o „duomenys“ yra „tekste“. Funkcija PAIEŠKA pateikia 2d TRUE ir FALSE reikšmių masyvą, 10 eilučių po 3 stulpelius, taip:
(3,#VALUE!,12;#VALUE!,4,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,3;14,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;3,#VALUE!,12)
Kiekvienai „duomenų“ reikšmei turime 3 rezultatus (po vieną kiekvienoje paieškos eilutėje), kurie yra #VALUE klaidos arba skaičiai. Skaičiai nurodo rastos teksto eilutės padėtį, o klaidos - nerastas teksto eilutes. Beje, TRANSPOSE funkcija reikalinga norint sukurti 10 x 3 masyvą visų rezultatų.
Šis masyvas įvedamas į ISNUMBER, kad gautume TRUE FALSE reikšmes, kurias konvertuojame į 1s ir 0s su dvigubai neigiamu (-) operatoriumi. Rezultatas yra toks masyvas:
(1,0,1;0,1,0;0,0,0;0,0,0;0,0,1;1,0,0;0,0,0;0,0,0;0,0,0;1,0,1)
kuris eina į MMULT funkciją kaip masyvas1. Laikantis matricos dauginimo taisyklių, masyvo1 stulpelių skaičius turi būti lygus masyvo2 eilučių skaičiui. Norėdami sukurti masyvą2 , mes naudojame funkciją ROW taip:
ROW(exclude)^0
Gaunamas 1s masyvas, 3 eilutės po 1 stulpelį:
(1;1;1)
kurie eina į MMULT kaip 2 masyvas . Po masyvo padauginimo turime masyvą, kurio dydis atitinka pirminius duomenis:
(2;1;0;0;1;1;0;0;0;2)
Šiame masyve bet kuris ne nulis skaičius reiškia vertę, kurioje buvo rasta bent viena iš neįtrauktų eilučių. Nuliai rodo, kad nerasta pašalintų eilučių. Norėdami priversti visas ne nulines reikšmes iki 1, naudojame didesnę nei nulis:
(2;1;0;0;1;1;0;0;0;2)>0
sukuria dar vieną masyvą arba TRUE ir FALSE reikšmes:
(TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)
Mūsų galutinis tikslas yra suskaičiuoti tik tas teksto reikšmes, kuriose nerasta pašalintų eilučių, todėl turime šias reikšmes pakeisti. Tai darome atimdami masyvą iš 1. Tai yra loginės logikos pavyzdys. Matematikos operacija TRUE ir FALSE reikšmes automatiškai priverčia į 1s ir 0s, ir galiausiai turime masyvą, kad galėtume grįžti į funkciją SUM:
=SUM((0;0;1;1;0;0;1;1;1;0))
Funkcija SUM pateikia galutinį rezultatą 5.
