Yleinen kaava
=MMULT(--(data>TRANSPOSE(data)),ROW(data)^0)
Yhteenveto
Voit lajitella ja poimia yksilöllisiä arvoja dynaamisesti tietoluettelosta käyttämällä matriisikaavaa sijoittaaksesi sijoituksen auttajasarakkeeseen ja poimimalla sitten ainutlaatuisten arvojen avulla erityisesti rakennettua INDEX- ja MATCH-kaavaa. Esitetyssä esimerkissä kaava sijoituksen määrittämiseksi ryhmässä C5: C13 on:
=IF(data="",ROWS(data),MMULT(--(data>TRANSPOSE(data)),ROW(data)^0))
missä "data" on nimetty alue B5: B13.
Huomaa: tämä on monisoluinen matriisikaava, joka syötetään ohjaimella + shift + enter.
Selitys
Huomaa: Tämän kaavan ydinidea on mukautettu esimerkistä Mike Girvinin erinomaisesta Control + Shift + Enter -kirjasta.
Esitetyssä esimerkissä käytetään useita kaavoja, jotka kuvataan alla. Korkealla tasolla MMULT-funktiota käytetään laskemaan numeerinen sijoitus auttajasarakkeessa (sarake C), ja sitten INDEX- ja MATCH-kaava sarakkeessa G käyttää tätä arvoa ainutlaatuisten arvojen poimimiseksi.
Ranking-arvot
MMULT-funktio suorittaa matriisikertomuksen ja sitä käytetään määrittelemään numeerinen sijoitus kullekin arvolle. Ensimmäinen taulukko luodaan seuraavalla lausekkeella:
--(data>TRANSPOSE(data))
Tässä käytämme TRANSPOSE-funktiota horisontaalisen tietojoukon luomiseen , ja kaikkia arvoja verrataan toisiinsa. Pohjimmiltaan kutakin arvoa verrataan kaikkiin muihin arvoihin vastaamaan kysymykseen "onko tämä arvo suurempi kuin kaikki muut arvot". Tuloksena on kaksiulotteinen taulukko, 9 saraketta x 9 riviä, täynnä TOSI- ja EPÄTOSI-arvoja. Kaksinkertaista negatiivista (-) käytetään TOSI EPÄTOSI -arvojen pakottamiseen 1: een ja nolliin. Voit visualisoida tuloksena olevan taulukon seuraavasti:
Yllä olevasta 1: n ja nollan matriisista tulee matriisi1 MMULT-funktion sisällä. Taulukko2 luodaan tällä lausekkeella:
ROW(data)^0
Tällöin kukin "datan" rivinumero nousee nollan voimaan luodakseen yksiulotteisen taulukon, 1 sarake x 9 riviä, täynnä numeroa 1. MMULT palauttaa sitten kahden matriisin matriisituotteen, joista tulee arvot näkyvät sijoitus sarakkeessa.
Saamme takaisin kaikki 9 sijoitusta samanaikaisesti taulukossa, joten meidän on laitettava tulokset kerralla eri soluihin. Muuten kukin solu näyttää vain palautetun taulukon ensimmäisen sijoitusarvon.
Huomaa: tämä on monisoluinen matriisikaava, joka syötetään ohjaimella + shift + enter alueella C5: C13.
Tyhjien solujen käsittely
Tyhjät solut käsitellään tällä sijoituskaavan osalla:
=IF(data="",ROWS(data)
Täällä, ennen kuin suoritamme MMULT: n, tarkistamme, onko "data" -kohdan nykyinen solu tyhjä. Jos näin on, annamme sijoitusarvon, joka on sama kuin rivien lukumäärä tiedoissa. Tämä tehdään pakottaen tyhjät solut luettelon loppuun, missä ne voidaan helposti sulkea pois myöhemmin, kun yksilölliset arvot puretaan (selitetään alla).
Yksilöllisten arvojen laskeminen
Yksilöllisten arvojen laskemiseksi tietoihin kaava kohdassa E5 on:
=SUM(--(FREQUENCY(rank,rank)>0))-(blank>0)
Koska yllä oleva kaava määrittää kullekin arvolle numeerisen sijoituksen, voimme käyttää FREQUENCY-funktiota SUM: n kanssa yksilöllisten arvojen laskemiseen. Tämä kaava on selitetty yksityiskohtaisesti tässä. Vähennämme sitten 1 tuloksesta, jos tiedoissa on tyhjiä soluja:
-(blank>0)
missä "tyhjä" on nimetty alue E8 ja sisältää tämän kaavan:
=COUNTBLANK(data)
Pohjimmiltaan pienennämme yksilöllistä määrää yhdellä, jos tiedoissa on tyhjiä soluja, koska emme sisällytä niitä tuloksiin. Yksilöllinen määrä solussa E5 on nimeltään "ainutlaatuinen" (yksilölliselle laskennalle), ja INDEX- ja MATCH-kaavat käyttävät sitä tyhjien solujen suodattamiseen (kuvattu alla).
Poimitaan ainutlaatuiset arvot
Ainutlaatuisten arvojen purkamiseksi G5 sisältää seuraavan kaavan, joka on kopioitu alaspäin:
=IF(ROWS($G$5:G5)>unique,"",INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0)))
Ennen kuin suoritamme INDEX- ja MATCH-kaavat, tarkistamme ensin, onko nykyinen rivimäärä louhinta-alueella suurempi kuin yksilöllinen määrä nimetty alue "ainutlaatuinen" (E5):
=IF(ROWS($G$5:G5)>unique,"",
Jos näin on, olemme poistaneet ainutlaatuiset arvot ja palautamme tyhjän merkkijonon (""). Jos ei, suoritamme uuttokaavan:
INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0))
Huomaa, että tässä on kaksi MATCH-toimintoa, yksi toisen sisällä. Sisäinen MATCH käyttää laajenevaa aluetta matriisiin ja nimettyä aluetta "data" hakuarvoon:
MATCH(data,$G$4:G4,0)
Huomaa, että laajeneva alue alkaa esimerkin rivin 4 yläpuolella olevasta rivistä. Sisäisen MATCH-tuloksen tulos on taulukko, joka sisältää jokaiselle datan arvolle joko numeerisen sijainnin (arvo on jo purettu) tai # N / A -virheen (arvoa ei ole vielä purettu). Sitten suodatamme nämä tulokset IF: n ja ISNA: n avulla ja palautamme kaikkien arvojen sijoitusarvon "tiedoissa", joita ei ole vielä purettu:
IF(ISNA(results),rank))
Tämän toiminnon tuloksena on matriisi, joka syötetään MIN-toimintoon, jotta saadaan "vähimmäisarvoluku" data-arvoille, joita ei ole vielä purettu. MIN-funktio palauttaa tämän arvon ulommalle MATCH: lle hakuarvona ja nimetty alue "rank" matriisina:
MATCH(min_not_extracted,rank)),rank,0)
Lopuksi MATCH palauttaa alimman sijoitusarvon sijainnin INDEX: lle rivinumerona ja INDEX palauttaa data-arvon nykyisen rivin uuttoalueelle.
