Podsumowanie
Aby utworzyć licznik bieżący w tabeli programu Excel, możesz użyć funkcji INDEKS z odwołaniem strukturalnym, aby utworzyć rozszerzający się zakres. W pokazanym przykładzie formuła w F5 to:
=(@Color)&" - "&SUM(--(INDEX((Color),1):(@Color)=(@Color)))
Po skopiowaniu kolumny w dół ta formuła zwróci bieżącą liczbę dla każdego koloru w kolumnie Kolor.
W niektórych wersjach programu Excel jest to formuła tablicowa i należy ją wprowadzić za pomocą klawiszy Ctrl + Shift + Enter.
Wyjaśnienie
Zasadniczo ta formuła używa INDEKSU do tworzenia rozszerzającego się odniesienia w następujący sposób:
INDEX((Color),1):(@Color) // expanding range
Po lewej stronie dwukropka (:) funkcja INDEKS zwraca odwołanie do pierwszej komórki w kolumnie kolumny.
INDEX((Color),1) // first cell in color
Działa to, ponieważ funkcja INDEKS zwraca odwołanie do pierwszej komórki, a nie rzeczywistą wartość. Po prawej stronie dwukropka otrzymujemy odniesienie do bieżącego wiersza kolumny kolorów w następujący sposób:
(@Color) // current row of Color
To jest standardowa składnia odwołania strukturalnego dla „tego wiersza”. Połączone dwukropkiem te dwa odwołania tworzą zakres, który rozszerza się w miarę kopiowania formuły w dół tabeli. Więc zamieniamy te odwołania na funkcję SUMA, mamy:
SUM(--(B5:B5=(@Color))) // first row SUM(--(B5:B11=(@Color))) // last row
Każde z powyższych wyrażeń generuje tablicę wartości PRAWDA / FAŁSZ, a podwójny ujemny (-) jest używany do konwersji tych wartości na 1 i 0. Tak więc w ostatnim rzędzie otrzymujemy:
SUM((0;0;0;1;0;0;0;0;1;0;1)) // returns 3
Reszta formuły po prostu łączy kolor z bieżącego wiersza z liczbą zwracaną przez SUMA:
=(@Color)&" - "&3 ="Gold"&" - "&3 ="Gold - 3"
Prosty rozszerzany zakres?
Dlaczego nie skorzystać z tak prostego, rozszerzającego się zakresu?
SUM(--($B$5:B5=(@Color)))
Z jakiegoś powodu tego rodzaju mieszane odwołania ulegają uszkodzeniu w tabeli programu Excel po dodaniu wierszy. Użycie INDEKSU z odwołaniem strukturalnym rozwiązuje problem.
