Kokkuvõte
Exceli tabelis jooksva loendi loomiseks saate laieneva vahemiku loomiseks kasutada funktsiooni INDEX koos struktureeritud viitega. Näidatud näites on valem F5-s järgmine:
=(@Color)&" - "&SUM(--(INDEX((Color),1):(@Color)=(@Color)))
Kui veerust alla kopeeritakse, tagastab see valem veerus Värv iga värvi jooksva arvu.
Mõnes Exceli versioonis on see massiivivalem ja see tuleb sisestada juhtklahviga + tõstuklahv + sisestusklahv.
Selgitus
Põhimõtteliselt kasutab see valem laiendava viite loomiseks INDEX-i:
INDEX((Color),1):(@Color) // expanding range
Käärsoole (:) vasakul küljel tagastab funktsioon INDEX viite veeru veeru esimesele lahtrile.
INDEX((Color),1) // first cell in color
See töötab, sest funktsioon INDEX tagastab viite esimesele lahtrile, mitte tegelikule väärtusele. Käärsoole paremal küljel saame viite värviveeru praegusele reale järgmiselt:
(@Color) // current row of Color
See on "selle rea" standardne struktureeritud viite süntaks. Koos kooloniga loovad need kaks viidet vahemiku, mis laieneb valemi tabelisse kopeerimisel. Niisiis, vahetame need viited funktsiooni SUM, meil on:
SUM(--(B5:B5=(@Color))) // first row SUM(--(B5:B11=(@Color))) // last row
Kõik ülaltoodud avaldised genereerivad massiivi TÕENE / VALE väärtusi ja topeltnegatiivi (-) kasutatakse nende väärtuste teisendamiseks 1 ja 0-ks. Nii et viimases reas lõpetame:
SUM((0;0;0;1;0;0;0;0;1;0;1)) // returns 3
Ülejäänud valem ühendab praeguse rea värvi lihtsalt SUM-i tagastatud arvuga:
=(@Color)&" - "&3 ="Gold"&" - "&3 ="Gold - 3"
Lihtne laienev vahemik?
Miks mitte kasutada sellist lihtsat laienevat vahemikku?
SUM(--($B$5:B5=(@Color)))
Millegipärast rikutakse seda tüüpi segaviiteid Exceli tabelis ridade lisamisel. INDEXi kasutamine struktureeritud viitega lahendab probleemi.
