Yleinen kaava
=SUMPRODUCT(--(A1=things))>0
Yhteenveto
Jos haluat testata solun nähdäksesi, onko se yhtä monista asioista, voit tehdä sen kaavalla, joka käyttää SUMPRODUCT-funktiota.
Asiayhteys
Oletetaan, että sinulla on luettelo tekstimerkkijonoja alueella B5: B11, ja haluat testata kutakin solua toisen luettelon asioista alueella E5: E9. Toisin sanoen jokaisen solun kohdassa B5: B11 haluat tietää: tasaako tämä solu mitä tahansa kohdassa E5: E9 mainittua?
Voit aloittaa suuren kaavan rakentamisen sisäkkäisiin IF-lauseisiin, mutta SUMPRODUCT-pohjainen taulukko on yksinkertaisempi, puhtaampi lähestymistapa.
Ratkaisu
Ratkaisu on luoda kaava, joka testaa useita arvoja ja palauttaa luettelon TOSI / EPÄTOSI arvoista. Kun se on saatu, voimme käsitellä luetteloa (itse asiassa matriisi) SUMPRODUCT-sovelluksella.
Käyttämämme kaava näyttää tältä:
=SUMPRODUCT(--(B5=things))>0
Selitys
Avain on tämä katkelma:
--(B5=things)
joka yksinkertaisesti vertaa B5: n arvoa kaikkiin nimettyjen alueiden "asiat" arvoihin. Koska verrataan B5: tä matriisiin (ts. Nimettyyn alueeseen "asiat", E5: E11), tuloksena on joukko TOSI / EPÄTOSI-arvoja, kuten tämä:
(TOSI; FALSE; FALSE; FALSE; FALSE)
Jos taulukossa on jopa yksi TOSI, tiedämme, että B5 on yhtä kuin vähintään yksi asia luettelossa, joten pakottaaksemme TOSI / EPÄTOSI-arvot 1: ksi ja 0: ksi, käytämme kaksinkertaista negatiivista (-, jota kutsutaan myös kaksinkertaiseksi unary). Tämän pakon jälkeen meillä on tämä:
(1; 0; 0; 0; 0)
Käsittelemme tulosta nyt SUMPRODUCT-toiminnolla, joka lisää elementit matriisiin. Jos saamme tuloksen, joka ei ole nolla, meillä on vähintään yksi ottelu, joten käytämme> 1 pakottaaksemme joko TOSI tai EPÄTOSI lopputuloksen.
Koodatulla luettelolla
Ei ole vaatimusta, että käytät alueita luetteloasi asioista. Jos etsit vain pientä määrää asioita, voit käyttää taulukon muodossa olevaa luetteloa, jota kutsutaan taulukon vakiona. Esimerkiksi, jos etsit vain punaisia, sinisiä ja vihreitä värejä, voit käyttää ("punainen", "sininen", "vihreä") näin:
--(B5=("red","blue","green"))
Ylimääräisten tilojen käsittely
Jos testattavat solut sisältävät ylimääräistä tilaa, ne eivät sovi oikein. Jos haluat poistaa kaiken ylimääräisen tilan, voit muokata kaavaa käyttämään TRIM-toimintoa seuraavasti:
=SUMPRODUCT(--(TRIM(A1)=things))>0
