Jak używać funkcji WIERSZE w Microsoft Excel

Wielu użytkowników Excela rezygnuje z funkcji ROWS, ponieważ wydaje im się to techniczną formalnością, którą można pominąć. Jednak aby stworzyć naprawdę funkcjonalny arkusz, potrzebujesz formuł, które dostosowują się do wymiarów danych, a funkcja ROWS jest do tego idealna. Oto cztery sposoby, w jakie jej używam, aby uczynić mój arkusz Excela bardziej inteligentnym.

Jak działa funkcja WIERSZE

Składnia funkcji ROWS jest naprawdę prosta:

=ROWS(array)

Pojedynczy argument (tablica) to zakres komórek lub tabela w Excelu. Jedynym zadaniem funkcji jest dokładne określenie wysokości twojego zbioru danych, niezależnie od tego, czy wiersze są puste, czy wypełnione.

Wszystkie przykłady, które zaraz Ci pokażę, używają sformatowanej tabeli Excela o nazwie T_Orders. Korzystanie z tabel pozwala mi używać odwołań strukturalnych zamiast bezpośrednich odwołań do komórek, co sprawia, że moje formuły są znacznie łatwiejsze do odczytania i utrzymania.

Przypadek użycia 1: Tworzenie indeksu wierszy odpornego na sortowanie

Funkcja ROWS zlicza liczbę wierszy w określonym zakresie. Używając rozszerzającego się zakresu - zaczynającego się od stałego punktu odniesienia, a kończącego na bieżącym wierszu - możesz generować dynamiczną, sekwencyjną listę.

Scenariusz

Chcesz wypełnić kolumnę A numerami wierszy, które pozostaną w idealnym porządku liczbowym, nawet jeśli przetasujesz tabelę według jednej z innych kolumn.

Rozwiązanie ROWS

Wpisz ten wzór w pierwszym wierszu kolumny i naciśnij Enter:

=ROWS(T_Orders[#Headers]:[@OrderID])-1

Ponieważ używasz ustrukturyzowanej tabeli w Excelu, formuła jest automatycznie powielana w dół kolumny.

Ta formuła zaczyna się od nagłówka tabeli (T_Orders[#Headers]) i kończy na bieżącym wierszu ([@OrderID]). W miarę przesuwania się w dół tabeli punkt początkowy pozostaje zakotwiczony na górze, ale punkt końcowy przesuwa się w dół, co pozwala funkcji ROWS zliczać liczbę wierszy w tym rosnącym zakresie. Odejmowanie -1 na końcu wyklucza nagłówek z tego liczenia.

Dlaczego ROWS to właściwy wybór

Standardową alternatywą, =ROW(), jest powiązana z siatką arkusza. Jeśli przeniesiesz tabelę z wiersza 1 na wiersz 10, numery indeksów zmienią się na 11, 12, 13 i tak dalej. Metoda ROWS jest w stosunku do tabeli, co oznacza, że interesuje ją tylko pozycja w samej tabeli.

Przypadek użycia 2: Obliczanie „odpornego na duchy” procentowego wskaźnika KPI

Podczas obliczania procentów w Excelu potrzebujesz mianownika, który reprezentuje całkowitą pojemność twojego zestawu danych. Funkcja ROWS zwraca liczbę wierszy w tabeli, niezależnie od zawartości komórek.

Scenariusz

Musisz dokładnie obliczyć, jaki procent całkowitej liczby zamówień osiągnął status „Wysłane”.

Rozwiązanie ROWS

W obszarze pulpitu nawigacyjnego u góry arkusza wpisz ten wzór:

=COUNTIF(T_Orders[Status],"Shipped")/ROWS(T_Orders)

Część formuły COUNTIF zlicza liczbę wierszy, które zawierają słowo „Shipped”, a następnie dzieli to przez ROWS(T_Orders), co zwraca całkowitą liczbę wierszy w tabeli T_Orders.

Dlaczego ROWS to właściwy wybór

Możesz być kuszony, aby użyć funkcji COUNTA jako mianownika w tym scenariuszu, ale ta funkcja liczy tylko komórki, które nie są puste. Jeśli ktoś doda nowy wiersz do tabeli, ale pozostawi status pusty, lub jeśli komórka zawiera niewidoczny „duchowy” znak, taki jak spacja, COUNTA zwróci nieprawidłową sumę. ROWS mierzy pojemnik, a nie zawartość, więc Twoje wskaźniki KPI opierają się na rzeczywistej wielkości tabeli.

Przypadek użycia 3: Kontrola integralności danych

Ponieważ funkcja ROWS mierzy fizyczny rozmiar zakresu, działa jak automatyczny audytor w porównaniu z funkcjami liczącymi rzeczywiste wartości.

Scenariusz

Chcesz otrzymywać powiadomienie za każdym razem, gdy do tabeli zostanie dodany nowy wiersz, ale krytyczne pole Kwota zostanie pozostawione puste.

Rozwiązanie ROWS

Oto formuła, którą możesz dodać do swojego panelu, aby to się stało:

=IF(ROWS(T_Orders)>SUMPRODUCT(--(LEN(T_Orders[Amount])>0)),"[!] Amount entry missing","All clear")

To porównuje całkowitą liczbę wierszy w tabeli (ROWS) z liczbą komórek w kolumnie Kwota, które faktycznie zawierają znaki. Używając funkcji LEN do sprawdzenia długości znaków oraz SUMPRODUCT do zsumowania wyników, wymuszasz, aby Excel liczył tylko te komórki, w których liczba znaków jest większa od zera. Jeśli całkowita liczba wierszy w tabeli przekracza tę liczbę, formuła zwraca alert „Brak wpisu kwoty”; w przeciwnym razie zwraca „Wszystko w porządku.”

Dlaczego ROWS to właściwy wybór

Popularne metody, takie jak COUNTA i COUNTBLANK, mogą wpaść w pułapkę, interpretując błędnie „fantomowe” dane, takie jak przypadkowe spacje czy puste ciągi zwracane przez formuły. Łącząc funkcję ROWS z kontrolą długości, tworzy się mechaniczny audyt, który jest znacznie bardziej niezawodny.

Przypadek użycia 4: Tworzenie elastycznych raportów „top X”

Łączenie funkcji ROWS z funkcjami rankingowymi, takimi jak LARGE, pozwala tworzyć raporty, które skalują się automatycznie, przekształcając statyczny ranking w dynamiczny licznik.

Scenariusz

Chcesz wyświetlić na pulpicie liderów listę trzech najwyższych zamówień, ale jednocześnie chcesz mieć możliwość przeciągnięcia listy w dół, aby pokazać pięciu najlepszych, bez przepisywania formuły lub ręcznej zmiany rangi dla każdego wiersza.

Rozwiązanie ROWS

W pierwszej komórce swojej tabeli liderów użyj następującej formuły:

=LARGE(T_Orders[Amount],ROWS($1:1))

Następnie kliknij i przeciągnij uchwyt wypełniania tej komórki w dół o dwa wiersze, aby uzupełnić tabelę najlepszej trójki.

Funkcja LARGE wymaga dwóch argumentów: zakresu danych oraz wartości k (rangi). Zamiast wpisywać statyczną liczbę dla rangi, używa się ROWS($1:1), co zwraca 1 w pierwszym wierszu. Jednak ponieważ pierwsza część zakresu jest absolutna ($1), a druga względna (1), zakres rozszerza się w miarę przeciągania formuły w dół, tworząc automatyczny licznik, który dokładnie określa funkcji LARGE, którą rangę pobrać.

Dlaczego ROWS to właściwy wybór

Powszechną alternatywą jest użycie stałej tablicowej ({1;2;3}) do zwrócenia trzech najwyższych wartości. Jednak takie podejście, w którym wartości są wpisane na stałe, oznacza, że trzeba ręcznie wracać do formuły, aby zmienić liczbę zwracanych wartości. Alternatywnie można użyć funkcji SEQUENCE, ale nie jest ona kompatybilna wstecz. Metoda ROWS jest uniwersalna — działa w każdej wersji Excela i pozwala każdemu rozszerzać raport, przeciągając uchwyt, lub zmniejszać go, czyszcząc odpowiednie komórki.

Funkcja ROWS to tylko jeden ze sposobów na sprawdzenie pliku Excel. Możesz również dokładniej przeanalizować stan swojego skoroszytu, korzystając z funkcji Przejdź do specjalnego, aby znaleźć ukryte stałe lub niespójne formuły, a łączenie tych narzędzi z nawykami, które ułatwiają czytanie formuł, zapewni, że Twoje arkusze pozostaną łatwe do utrzymania zarówno dla Ciebie, jak i Twoich współpracowników w dłuższej perspektywie.