Yleinen kaava
=SUMPRODUCT(--ISNUMBER(SEARCH(keywords,A1)))
Yhteenveto
Voit laskea tietyssä solussa esiintyvien tiettyjen sanojen tai avainsanojen määrän käyttämällä kaavaa, joka perustuu SEARCH-, ISNUMBER- ja SUMPRODUCT-funktioihin. Esitetyssä esimerkissä kaava kohdassa C5 on:
=SUMPRODUCT(--ISNUMBER(SEARCH(keywords,B5)))
missä "avainsanat" on nimetty alue E5: E9.
Selitys
Huomaa: jos avainsana esiintyy useammin kuin kerran tietyssä solussa, se lasketaan vain kerran. Toisin sanoen kaava laskee vain eri avainsanojen esiintymät.
Tämän kaavan ydin on ISNUMBER + SEARCH -lähestymistapa tekstin etsimiseen solusta, joka selitetään tarkemmin täällä. Tässä tapauksessa etsimme kustakin solusta kaikkia nimettyjen alueiden sanoja "avainsanat" (E5: E9). Teemme tämän siirtämällä alueen hakuun argumentti find_text. Koska ohitamme joukon 5 kohdetta:
("green";"orange";"white";"blue";"pink")
saamme joukon 5 tuotetta takaisin seurauksena:
(#VALUE!;#VALUE!;1;#VALUE!;14)
Numerot vastaavat otteluita ja #VALUE! virhe tarkoittaa, että hakua ei löytynyt. Tässä tapauksessa, koska meistä ei ole väliä, mistä teksti löydettiin solusta, muunnetaan taulukko TOSI- ja EPÄTOSI-arvoiksi käyttämällä ISNUMBER:
(FALSE;FALSE;TRUE;FALSE;TRUE)
Ja kaksinkertainen negatiivinen (-) näiden muuttamiseksi yhdeksi ja nollaksi:
(0;0;1;0;1)
SUMPRODUCT-funktio palauttaa sitten yksinkertaisesti taulukon summan, 2 tässä tapauksessa.
Tyhjien avainsanojen käsittely
Jos avainsana-alue sisältää tyhjiä soluja, kaava ei toimi oikein, koska SEARCH-funktio palauttaa nollan, kun etsit tyhjää merkkijonoa (""). Voit suodattaa tyhjät solut avainsanaväliltä käyttämällä alla olevaa muunnelmaa:
(=SUMPRODUCT(--ISNUMBER(SEARCH(IF(keywords"",keywords),B5))))
Huomaa: tämä versio on matriisikaava, ja se on syötettävä painamalla ohjainta + vaihto + enter.
