Yhteenveto
Voit luoda juoksevan määrän Excel-taulukkoon käyttämällä INDEX-funktiota strukturoidun viitteen avulla laajentuvan alueen luomiseen. Esitetyssä esimerkissä kaava kohdassa F5 on:
=(@Color)&" - "&SUM(--(INDEX((Color),1):(@Color)=(@Color)))
Kun kopioidaan sarakkeesta alaspäin, tämä kaava palauttaa jokaisen Väri-sarakkeen värin juoksevan määrän.
Joissakin Excel-versioissa tämä on matriisikaava ja se on syötettävä näppäimillä control + shift + enter.
Selitys
Ytimessä tämä kaava on käyttänyt INDEX: ää luodakseen näin laajenevan viitteen:
INDEX((Color),1):(@Color) // expanding range
Paksusuolen (:) vasemmalla puolella INDEX-funktio palauttaa viitteen sarakkeen sarakkeen ensimmäiseen soluun.
INDEX((Color),1) // first cell in color
Tämä toimii, koska INDEX-funktio palauttaa viitteen ensimmäiseen soluun, ei todelliseen arvoon. Paksusuolen oikealla puolella saadaan viittaus värisarakkeen nykyiseen riviin seuraavasti:
(@Color) // current row of Color
Tämä on tavallinen jäsennelty viitesyntaksia "tälle riville". Kaksoispisteeseen yhdistettynä nämä kaksi viittausta luovat alueen, joka laajenee, kun kaava kopioidaan taulukkoon. Joten vaihdamme nämä viitteet SUM-funktioon, meillä on:
SUM(--(B5:B5=(@Color))) // first row SUM(--(B5:B11=(@Color))) // last row
Kukin yllä olevista lausekkeista muodostaa joukon TOSI / EPÄTOSI-arvoja, ja kaksoisnegatiivista (-) käytetään näiden arvojen muuntamiseen 1: ksi ja 0: ksi. Joten viimeisellä rivillä päädytään:
SUM((0;0;0;1;0;0;0;0;1;0;1)) // returns 3
Kaavan loppuosa yksinkertaisesti yhdistää nykyisen rivin värin SUM: n palauttamaan määrään:
=(@Color)&" - "&3 ="Gold"&" - "&3 ="Gold - 3"
Yksinkertainen laajeneva alue?
Miksi et käyttäisi tällaista yksinkertaista laajenevaa aluetta?
SUM(--($B$5:B5=(@Color)))
Jostain syystä tällainen sekaviite vioittuu Excel-taulukossa, kun rivejä lisätään. INDEXin käyttö jäsennellyn viitteen kanssa ratkaisee ongelman.
