Yleinen kaava
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
Yhteenveto
Voit laskea solut, joissa ei ole paljon erilaisia merkkijonoja, käyttämällä melko monimutkaista kaavaa, joka perustuu MMULT-funktioon. Esitetyssä esimerkissä kaava kohdassa F5 on:
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
missä "data" on nimetty alue B5: B14 ja "poissulkeminen" on nimetty alue D5: D7.
Huomaa: tämä on matriisikaava ja se on syötettävä näppäimillä + + shift + enter
Esipuhe
Tätä kaavaa vaikeuttaa "sisältää" -vaatimus. Jos tarvitset kaavan vain sellaisten solujen laskemiseksi, jotka eivät * ole * yhtä monta asiaa, voit käyttää yksinkertaisempaa kaavaa, joka perustuu MATCH-toimintoon. Lisäksi, jos sinulla on rajoitettu määrä merkkijonoja suljettavaksi, voit käyttää COUNTIFS-funktiota seuraavasti:
=COUNTIFS(data,"*pink*",data,"*orange*",data,"*black*")
Tässä lähestymistavassa sinun on kuitenkin annettava uusi pari alue- / ehtoargumentteja jokaiselle suljettavalle merkkijonolle. Sen sijaan alla selitetty kaava pystyy käsittelemään suuren määrän merkkijonoja, jotka voidaan sulkea pois suoraan syötettyyn taulukkoon.
Lopuksi tämä kaava on monimutkainen. Kerro minulle, jos sinulla on yksinkertaisempi kaava ehdottaa :)
Selitys
Tämän kaavan ydin on ISNUMBER ja SEARCH:
ISNUMBER(SEARCH(TRANSPOSE(exclude),data))
Tässä siirretään nimettyyn alueeseen kuuluvat kohteet "poissulkea", syötetään sitten tulos hakuun "etsi teksti" ja "data" tekstin sisällä. HAKU-funktio palauttaa 2D-arvon TOSI- ja EPÄTOSI-arvot, 10 riviä 3 sarakkeella, kuten tämä:
(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)
Jokaisella "data" -arvolla on 3 tulosta (yksi kutakin hakumerkkijonoa kohden), jotka ovat joko #VALUE-virheitä tai numeroita. Numerot edustavat löydetyn tekstimerkkijonon sijaintia ja virheet tekstimerkkijonoja, joita ei löydy. Muuten, TRANSPOSE-toimintoa tarvitaan 10 x 3 -kokoonpanon tuottamiseksi täydellisistä tuloksista.
Tämä taulukko syötetään ISNUMBER: iin saadaksesi TOSI EPÄTOSI-arvot, jotka muunnetaan arvoiksi 1 ja 0 kaksinkertaisen negatiivisen (-) operaattorin avulla. Tuloksena on tällainen taulukko:
(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)
joka menee MMULT-funktioon matriisina1. Seuraavia sääntöjä matriisitulo, useita sarakkeita matriisi1 on sama numero rivien matriisi2. Taulukon2 luomiseen käytämme ROW-toimintoa seuraavasti:
ROW(exclude)^0
Tällöin saadaan joukko 1s, 3 riviä 1 sarakkeella:
(1;1;1)
joka menee MMULT: iin matriisina2 . Taulukon kertomisen jälkeen meillä on matriisi, joka on mitoitettu vastaamaan alkuperäisiä tietoja:
(2;1;0;0;1;1;0;0;0;2)
Tässä taulukossa mikä tahansa nollasta poikkeava luku edustaa arvoa, josta ainakin yksi poissuljetuista merkkijonoista on löydetty. Nollat osoittavat, ettei poissuljettuja merkkijonoja löytynyt. Pakottaaksemme kaikki nollasta poikkeavat arvot arvoon 1 käytämme suurempaa kuin nolla:
(2;1;0;0;1;1;0;0;0;2)>0
joka luo vielä yhden matriisin tai TOSI- ja EPÄTOSI-arvot:
(TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)
Viimeinen tavoitteemme on laskea vain tekstiarvot, joista ei löytynyt poissuljettuja merkkijonoja, joten meidän on muutettava nämä arvot. Teemme tämän vähentämällä taulukon arvosta 1. Tämä on esimerkki loogisesta logiikasta. Matemaattinen operaatio pakottaa TOSI- ja EPÄTOSI-arvot automaattisesti arvoksi 1 ja 0, ja lopulta meillä on taulukko palata SUM-toimintoon:
=SUM((0;0;1;1;0;0;1;1;1;0))
SUM-funktio palauttaa lopputuloksen 5.
