rezumat
Pentru a crea un număr de rulare într-un tabel Excel, puteți utiliza funcția INDEX cu o referință structurată pentru a crea un interval de expansiune. În exemplul prezentat, formula din F5 este:
=(@Color)&" - "&SUM(--(INDEX((Color),1):(@Color)=(@Color)))
Când este copiată în coloană, această formulă va returna un număr de rulare pentru fiecare culoare din coloana Culoare.
În unele versiuni de Excel, aceasta este o formulă matrice și trebuie introdusă cu control + shift + enter.
Explicaţie
La bază, această formulă folosește INDEX pentru a crea o referință extinsă ca aceasta:
INDEX((Color),1):(@Color) // expanding range
În partea stângă a colonului (:), funcția INDEX returnează o referință la prima celulă din coloana coloanei.
INDEX((Color),1) // first cell in color
Acest lucru funcționează deoarece funcția INDEX returnează o referință la prima celulă, nu la valoarea reală. În partea dreaptă a colonului, obținem o referință la rândul curent al coloanei de culoare astfel:
(@Color) // current row of Color
Aceasta este sintaxa de referință structurată standard pentru „acest rând”. Împreună cu două puncte, aceste două referințe creează un interval care se extinde pe măsură ce formula este copiată în tabel. Deci, schimbăm aceste referințe în funcția SUM, avem:
SUM(--(B5:B5=(@Color))) // first row SUM(--(B5:B11=(@Color))) // last row
Fiecare dintre expresiile de mai sus generează o serie de valori TRUE / FALSE, iar negativul dublu (-) este utilizat pentru a converti aceste valori în 1s și 0s. Deci, în ultimul rând, vom termina cu:
SUM((0;0;0;1;0;0;0;0;1;0;1)) // returns 3
Restul formulei concatenează pur și simplu culoarea din rândul curent la numărul returnat de SUM:
=(@Color)&" - "&3 ="Gold"&" - "&3 ="Gold - 3"
Gama de expansiune simplă?
De ce să nu folosiți o gamă simplă de expansiune ca aceasta?
SUM(--($B$5:B5=(@Color)))
Din anumite motive, acest tip de referință mixtă este deteriorat într-un tabel Excel pe măsură ce se adaugă rânduri. Utilizarea INDEX cu o referință structurată rezolvă problema.
