Santrauka
Norėdami sukurti bėgimo skaičių „Excel“ lentelėje, galite naudoti funkciją INDEX su struktūrine nuoroda, kad sukurtumėte besiplečiantį diapazoną. Parodytame pavyzdyje formulė F5 yra:
=(@Color)&" - "&SUM(--(INDEX((Color),1):(@Color)=(@Color)))
Nukopijavus stulpelį žemyn, ši formulė grąžins kiekvienos spalvos stulpelio Spalva skaičiavimą.
Kai kuriose „Excel“ versijose tai yra masyvo formulė, kurią reikia įvesti su „Control“ + „Shift“ + „Enter“.
Paaiškinimas
Iš esmės ši formulė naudoja INDEX, kad sukurtų tokią besiplečiančią nuorodą:
INDEX((Color),1):(@Color) // expanding range
Kairėje dvitaškio (:) pusėje funkcija INDEX grąžina nuorodą į pirmąjį stulpelio stulpelio langelį.
INDEX((Color),1) // first cell in color
Tai veikia, nes funkcija INDEX grąžina nuorodą į pirmąjį langelį, o ne faktinę vertę. Dešinėje dvitaškio pusėje gauname nuorodą į dabartinę spalvų stulpelio eilutę taip:
(@Color) // current row of Color
Tai yra standartinė „šios eilutės“ struktūrinė nuorodos sintaksė. Kartu su dvitaškiu šios dvi nuorodos sukuria diapazoną, kuris išsiplečia, kai formulė nukopijuojama lentelėje. Taigi, mes keičiame šias nuorodas į SUM funkciją, turime:
SUM(--(B5:B5=(@Color))) // first row SUM(--(B5:B11=(@Color))) // last row
Kiekviena iš aukščiau pateiktų išraiškų sukuria TRUE / FALSE reikšmių masyvą, o dvigubas neigiamas (-) naudojamas šioms reikšmėms konvertuoti į 1s ir 0s. Taigi paskutinėje eilutėje mes baigiame:
SUM((0;0;0;1;0;0;0;0;1;0;1)) // returns 3
Likusi formulės spalva tiesiog sujungia dabartinės eilutės spalvą su SUM grąžintu skaičiumi:
=(@Color)&" - "&3 ="Gold"&" - "&3 ="Gold - 3"
Paprastas besiplečiantis asortimentas?
Kodėl gi nenaudojant tokio paprasto besiplečiančio asortimento?
SUM(--($B$5:B5=(@Color)))
Kažkodėl šios mišrios nuorodos „Excel“ lentelėje sugadinamos pridedant eilučių. INDEX naudojimas su struktūrine nuoroda išsprendžia problemą.
