Funkcja WIERSZ() w formułach tablicowych

by Marcin

Funkcja WIERSZ w formułach tablicowych
Dzisiaj wracam na chwilę do formuł tablicowych. Analizując je (chociażby na tym blogu) bardzo często możesz się spotkać z występującą w nich konstrukcją: WIERSZ(ADR.POŚR()

Cóż to jest za „twór” i do czego służy ?

Funkcja WIERSZ() w formule tablicowej pełni po prostu funkcję licznika, który będzie odliczał dla Ciebie i wstawiał do formuły kolejne liczby całkowite. Wbrew pozorom jest to bardzo przydatne i nawet pobieżna lektura tego, o czym pisałem na blogu powinna przekonać Cię, że znajduje zastosowanie w bardzo wielu sytuacjach.

Przykład pierwszy z brzegu.

Chcesz wyświetlić trzy największe liczby jakiegoś zakresu (na rysunku poniżej są moje, przykładowe dane).

Funkcja WIERSZ w formułach tablicowych

Można bardzo prosto i, w niektórych sytuacjach, skutecznie.

Wykorzystać możesz funkcję MAX.K, która zwraca n-tą największą wartość jakiegoś zakresu. Innymi słowy, zrobi dla Ciebie dokładnie to, czego oczekujesz w tym przypadku.

W „nietablicowej” postaci, jako „n” wstawiłbyś tylko jedną liczbę. Z mojego wcześniejszego wpisu wiesz już jednak, że możesz zmusić funkcję do przyjęcia jako argumentu także tablicy liczb. W tym konkretnym przypadku interesują Cię trzy największe liczby, możesz więc formułę zapisać tak:

{=MAX.K(B4:B15;{1\2\3})}

Wynik działania widoczny jest na rysunku poniżej

Funkcja WIERSZ w formułach tablicowych

W wielu, niezbyt skomplikowanych, przypadkach podane rozwiązanie spokojnie wystarczy.

Można jednak inaczej.

Problem zaczyna się, kiedy nie wiesz dokładnie ile kolejnych liczb powinno znaleźć się w tablicy lub ilość ta ma być pobierana z innej komórki arkusza.

Wyobraź sobie przypadek, w którym „przerabiasz” przy pomocy tablicy kolejne litery jakiegoś wyrazu (przykład taki był już omawiany na blogu, jeżeli więc Cię interesuje poszperaj wśród wcześniejszych wpisów). Oczywiście ilość tych liter nie będzie stała, zależeć będzie od konkretnego wyrazu. Tablica kolejnych liczb wprowadzona „na sztywno” nie zda w takim przypadku egzaminu.Także wtedy, kiedy tych liczb do wpisania jest bardzo dużo do głosu dojść może lenistwo (lub zdrowy rozsądek) i zadać podstępne pytanie: po co robić samemu coś, co może zrobić za nas komputer?

Na przykład tak:

Problem rozwiązuje wykorzystanie funkcji WIERSZ(). Na rysunku poniżej zobaczyć możesz, że formuła

{=MAX.K(B4:B15;WIERSZ(1:3))}

także wygeneruje trzy największe liczby zakresu. Tym razem jednak, już bez ręcznego ich wpisywania. WIERSZ(1:3) jako wynik zwróci tablicę {1\2\3}. Łatwo zauważyć, że w przypadku np. 100 liczb takie podejście wydaje się być o wiele bardziej efektywne.

Funkcja WIERSZ w formułach tablicowych

Kolejny krok to przekonanie Excela, aby informację „do ilu ma liczyć” pobrał z jakiejś komórki. Będziesz musiał co prawda wspomóc się funkcją ADR.POŚR, ale rezultat jest więcej niż zadowalający.

Funkcja WIERSZ w formułach tablicowych

W tym przykładzie formułę tablicową wprowadziłem do zakresu komórek większego niż ilość komórek, które w danej chwili mnie interesują (stąd błędy #N/D!). Chciałem jednak pokazać, że bardzo prosto możesz teraz sterować ilością wyświetlanych elementów, zmieniając po prostu zawartość komórki H2. Szybka zamiana „3” na „6” i oto Twoim oczom ukaże się 6 największych liczbę zakresu.

Funkcja WIERSZ w formułach tablicowych

Na moim blogu znajdziesz przynajmniej kilka różnych przykładów zastosowania funkcji WIERSZ() w formułach tablicowych. Pozostaje Ci tylko poczytać. Mam nadzieję, że jeżeli nawet wcześniej nie do końca rozumiałeś rolę funkcji WIERSZ() w formule, to teraz, bez problemu, będziesz mógł stosować to rozwiązanie we własnych formułach.

Jak zwykle na koniec, zachęcam do kontaktu, krytyki, dzielenia się ze mną Waszymi uwagami i pomysłami.

Marcin

{ 6 comments… read them below or add one }

arek76

Bardzo fajny i pomocny artykuł 🙂 Można też z powodzeniem zastosować to do statystyk gier liczbowych…

dan

Witam
U mnie pojawił się problem z formułą {=MAX.K(B4:B15;{1\2\3})}
Z tego co zrozumiałem wg opisu jej powielenie (przeciągnięcie w dół powinno dać kolejne max wartości, jednak w moim przypadku odwołanie powoduje wyświetlenie cały czas pierwszej maksymalnej co jest równoznaczne z {=MAX.K(B4:B15;{1})}. Oczywiście przy zmianie {1\2\3} na {2\3\4} lub po prostu {2} jest wyświetlana druga wartość ale nie o to chyba chodziło bo po co wtedy formuła tablicowa. Gdzie więc popełniam błąd, że nie mogę uzyskać wartości dla kolejnych elementów tablicy drugiego parametru funkcji max.k () pomimo wykorzystania formuły tablicowej (ctrl+shift+enter po wprowadzeniu całej formuły)?. Będę wdzięczny za jakiekolwiek wskazówki pomocne przy rozwiązaniu problemu.

Marcin

Witam,

@dan

Z tym przeciąganiem formuły to jest nie do końca tak jak piszesz. Inna sprawa, że może nie do końca rozumiem Twój problem. Jeżeli idzie o formuły tablicowe to wprowadzamy je „na zakresie”. Tak więc najpierw zaznaczasz zakres komórek, następnie wpisujesz formułę tablicową i akceptujesz ctrl+shift+enter. W zasadzie o żadnym przeciąganiu komórek nie ma tutaj mowy. Zakładam, że to co próbujesz robić to wpisanie formuły do jednej komórki (np. A2), akceptacja ctrl+shift+enter i przeciągnięcie na pozostałe, interesujące Cię, komórki (A2:A4). To co powinieneś zrobić to najpierw zaznaczyć interesujące Cię komórki (A2:A4), a następnie wpisanie i akceptacja formuły. Powinno być OK.

Marcin

dan

Witam,
Wielkie dzięki za wyjaśnienie, teraz wszystko działa jak należy.
Pozdrawiam

niggazz1

Funkcja wykorzystujaca ADR.POŚR (tablicowa) nie działa mi w przypadku gdy wpiszę 1 – co miałoby zwrocic pojedyncza największa wartość. Mogłby Pan na to zerknac?

Konrad

Witam
Ciekawy pomysł, dynamizuje analizę :-). Jest jeden problem z MAX.K w sytuacji, gdy występują dwa kolejne maksima (np.dla K=3 i K=4) o tej samej wartości (np.50 występujące kolejno na 60 i 120 pozycji). Potrzebuje odnaleźć pozycję występowania danego maksimum za pomocą funkcji WYSZUKAJ.PIONOWO podając jako argument do wyszukania 50. Oczywiście funkcja za każdym razem znajduje pozycję MAX dla K=3 (tę pierwszą pozycję czyli 60). Da się to jakoś sprytnie obejść?

Pozdrawiam
Konrad

Leave a Comment

Previous post:

Next post: