Yleinen kaava
(=INDEX(rng1,MATCH(1,MMULT(--(rng2=critera),TRANSPOSE(COLUMN(rng2)^0)),0)))
Yhteenveto
Voit hakea arvoa yhdistämällä useita sarakkeita käyttämällä taulukon kaavaa, joka perustuu MMULT-, TRANSPOSE-, COLUMN- ja INDEX-arvoihin. Esitetyssä esimerkissä H4: n kaava on:
(=INDEX(groups,MATCH(1,MMULT(--(names=G4),TRANSPOSE(COLUMN(names)^0)),0)))
missä "nimet" on nimetty alue C4: E7 ja "ryhmät" on nimetty alue B4: B7. Kaava palauttaa ryhmän, johon kukin nimi kuuluu.
Huomaa: tämä on matriisikaava, ja se on syötettävä ohjaussiirtonäppäimellä.
Selitys
Työskentelemällä sisältä ulospäin tässä kaavassa käytetyt loogiset kriteerit ovat:
--(names=G4)
missä nimet ovat nimetty alue C4: E7. Tämä tuottaa TOSI / EPÄTOSI-tuloksen jokaiselle datan arvolle, ja kaksoisnegatiivi pakottaa TOSI-EPÄTOSI-arvot arvoon 1 ja 0, jolloin saadaan tällainen taulukko:
(0,0,0;1,0,0;0,0,0;0,0,0)
Tämä taulukko on 4 riviä 3 sarakkeella, mikä vastaa "nimien" rakennetta.
Toinen taulukko luodaan tällä lausekkeella:
TRANSPOSE(COLUMN(names)^0))
COLUMN-funktiolla luodaan numeerinen taulukko, jossa on 3 saraketta ja 1 rivi, ja TRANSPOSE muuntaa tämän taulukon yhdeksi sarakkeeksi ja 3 riviksi. Nousu nollan tehoon yksinkertaisesti muuntaa kaikki matriisin numerot luvuksi 1. MMULT-funktiota käytetään sitten matriisikertoimen suorittamiseen:
MMULT((0,0,0;1,0,0;0,0,0;0,0,0),(1;1;1))
ja tuloksena on MATCH-funktio matriisina, jossa hakuarvo on 1:
MATCH(1,(0;1;0;0),0)
MATCH-funktio palauttaa ensimmäisen ottelun sijainnin, joka vastaa ensimmäisen vastaavan rivin riviä, joka täyttää toimitetut ehdot. Tämä syötetään INDEX: iin rivinumerona, ja taulukko nimetty alue "ryhmät":
=INDEX(groups,2)
Lopuksi INDEX palauttaa "Karhu", ryhmän, johon Adam kuuluu.
Kirjaimellinen sisältää kriteerit
Voit tarkistaa tietyt tekstiarvot tarkan haun sijasta käyttämällä ISNUMBER- ja SEARCH-toimintoja yhdessä. Esimerkiksi vastaamaan soluja, jotka sisältävät omenaa, voit käyttää:
=ISNUMBER(SEARCH("apple",data))
Tämä kaava on selitetty tässä.
