Yleinen kaava
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1
Yhteenveto
Voit etsiä arvon sijainnin 2D-taulukosta SUMPRODUCT-toiminnolla. Esitetyssä esimerkissä taulukon enimmäisarvon rivien ja sarakkeiden numeroiden etsimiseen käytetyt kaavat ovat:
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1 =SUMPRODUCT((data=MAX(data))*COLUMN(data))-COLUMN(data)+1
missä "data" on nimetty alue C5: G14.
Huomaa: tässä esimerkissä löydämme mielivaltaisesti maksimiarvon sijainnin tiedoista, mutta voit korvata data = MAX (data) millä tahansa muulla loogisella testillä, joka eristää tietyn arvon. Huomaa myös, että nämä kaavat epäonnistuvat, jos taulukossa on päällekkäisiä arvoja.
Selitys
Rivinumeron saamiseksi tietoja verrataan enimmäisarvoon, joka tuottaa matriisin TOSI EPÄTOSI -tuloksia. Nämä kerrotaan ROW (data) -tuloksella, joka luo nimiryhmään "data" liittyvät rivinumeroiden taulukot ja taulukot:
=SUMPRODUCT((FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,TRUE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE)*(5;6;7;8;9;10;11))
Laskutoiminto saa Excelin pakottamaan TOSI EPÄTOSI-arvot ensimmäisessä taulukossa arvoksi 1 ja 0, joten voimme visualisoida välivaiheen seuraavasti:
=SUMPRODUCT((0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,1,0,0;0,0,0,0,0;0,0,0,0,0)*(5;6;7;8;9;10;11))
SUMPRODUCT palauttaa tuloksen 9, joka vastaa laskentataulukon 9. riviä. Indeksin saamiseksi suhteessa nimettyyn alueeseen "data" käytämme:
-ROW(data)+1
Lopputuloksena on taulukko (5; 4; 3; 2; 1; 0; -1), josta näytetään vain ensimmäinen arvo (5).
Kaava sarakkeen sijainnin määrittämiseksi toimii samalla tavalla.
Huomaa: törmäsin tähän lähestymistapaan Mike Ericksonin kommentissa MrExcel.com-sivustolla. Myös tässä säikeessä on joitain muita hyviä ideoita, mukaan lukien taulukon kaavan vaihtoehto.
