Tajemnica Ctr + Shift + Enter

by Marcin

Formuły tablicoweKilka dni temu, w odpowiedzi na prośbę Piotra, przygotowałem formułę, zaznaczając, że jest to formuła tablicowa. Następnego dnia otrzymałem jednak odpowiedź z informacją, że formuła nie działa. Ostatecznie, jak łatwo zgadnąć, okazało się, że Piotr wprowadził ją jako zwykłą formułę. Nie o nieporozumienie jednak tu chodzi, a o fakt, że zostałem zapytany później, w jaki sposób uniknąć takich pomyłek?

Skąd można wiedzieć czy dana formuła jest formułą tablicową? Czy w ogóle można to wiedzieć? Co to w ogóle znaczy „formuła tablicowa”?

Doskonale zdaję sobie oczywiście sprawę, że krótki wpis na blogu na pewno nie wyczerpie tematu. Mam jednak nadzieję, że pomoże Wam przynajmniej trochę w jego zrozumieniu.

Kilka słów o „tablicowości”

Formuła (czy też funkcja) sama w sobie nie jest ani tablicowa ani nietablicowa. To Ty decydujesz, w jaki sposób Excel ma wprowadzoną formułę potraktować. „Tablicowość” jest nie tyle cechą samej formuły, ile raczej sposobem, w jaki program Excel określoną formułę „przerobi”. Akceptacja formuły przy pomocy kombinacji klawiszy Ctr+Shitf+Enter jest dla Excela zleceniem wykonania jej w sposób „tablicowy”, uwzględniając tablice przekazywane jako argumenty i otrzymywane w wynikach.

Excelowi konformiści

Część funkcji Excela domyślnie, jako argument, przyjmuje zakres komórek (tablicę) i w rezultacie zwraca pojedynczą wartość. Doskonałymi przykładami mogą być funkcje SUMA, LICZ.JEŻELI, ŚREDNIA itp. W przypadku tych funkcji nie ma żadnego znaczenia czy wprowadzisz je jako funkcje tablicowe czy nie. One i tak operują na tablicach i odnajdą się (zadziałają prawidłowo) w każdej sytuacji. Ot – tacy mali excelowi konformiści. Na szczęście honoru bronią inne funkcje, które działają zupełnie inaczej w zależności od Twoje decyzji odnośnie ich „tablicowości” (czasami nie chcą w ogóle działać). Doskonałym przykładem może być funkcja JEŻELI.

Kiedy formuła tablicowa, a kiedy zwykła?

Formuły tablicowej (wprowadzonej jako tablicowa) użyjesz wszędzie tam gdzie chcesz, aby funkcja, która „na co dzień” operuje na pojedynczych wartościach (komórkach), nagle zachowała się inaczej i przyjęła jako argument oraz zwróciła w wyniku tablicę wartości. Powróćmy do wspomnianej już funkcji JEŻELI. Jako argument przyjmuje ona wartość logiczną PRAWDA lub FAŁSZ. W klasycznej postaci

=JEŻELI ($B$7>0;”większe”;”mniejsze”)

jeżeli wartość w komórce B7 jest większa od zera, jako argument funkcja otrzyma wartość PRAWDA i w wyniku zwróci ciąg tekstowy „większe”. Gdybyś jednak chciał jednorazowo sprawdzić więcej komórek i wynik takiego sprawdzenia przekazać do jeszcze innej funkcji powinieneś posłużyć się tablicową wersją powyższej formuły.

{=JEŻELI($B$7:$B$10>0;”większe”;”mniejsze”)}

Formuły tablicowe

Jako argument funkcja przyjmuje cały zakres $B$7:$B$10. W wyniku sprawdzenia każdej komórki z zakresu, w pamięci komputera utworzona zostaje tablica

{PRAWDA;FAŁSZ;PRAWDA;PRAWDA}

Następnie, tworzona jest kolejna tablica, której wartości zależą bezpośrednio od wartości w pierwszej tablicy.Jeżeli element w pierwszej tablicy ma wartość PRAWDA, w drugiej tablicy przyjmie wartość „większe”. Jeżeli ma wartość „FAŁSZ”, element w drugiej tablicy przyjmie wartość „mniejsze”. Po tej operacji pierwsza tablica usuwana jest z pamięci komputera i ostatecznie funkcja zwraca w wyniku tablicę {„większe”, „mniejsze”, „większe”, „większe”}.

W moim przykładzie formułę tablicową wpisałem tylko do jednej komórki (jak widać na rysunku powyżej), dlatego w wyniku otrzymałem tylko jedną wartość odpowiadającą pierwszej wartości w tablicy. Wystarczy jednak wprowadzić naszą formułę tablicową do zakresu komórek, aby zobaczyć wszystkie wartości tablicy wynikowej. W przykładzie (rysunek poniżej) widać, że tablica wynikowa zawiera dokładnie cztery elementy, które wspomniałem wyżej.

Formuły tablicowe

Oczywiście tablica zwrócona przez funkcję JEŻELI może zostać przekazana dalej, „do obróbki”, jako argument dla innej funkcji.

Przykład

Wyobraźmy sobie, że chciałbyś zsumować komórki z zakresu B7:B10, ale tylko te, które są większe od zera. Oczywiście możesz użyć funkcji SUMA.JEŻELI jednak w naszym przykładzie chcemy to zrobić tylko przy pomocy formuły tablicowej. Sumując komórki naszego zakresu trzeba się będzie jakoś pozbyć wartości -32. Do funkcji SUMA musisz przekazać tablicę, w której znajdą się tylko wartości większe od zera. Wszędzie tam gdzie wartość jest mniejsza od zera, wstawimy sobie zero, które oczywiście nie wpłynie na wynik. Jak już wiesz, tymczasową tablicę z odpowiednimi wartościami możesz otrzymać przy pomocy funkcji JEŻELI. Ostatecznie, odpowiednia formuła będzie wyglądać następująco:

{=SUMA(JEŻELI($B$7:$B$10>0;$B$7:$B$10;0))}

Formuły tablicowe

W wyniku sprawdzenia każdej komórki zakresu $B$7:$B$10 (czy większa od zera) w pamięci komputera utworzona zostaje tablica {PRAWDA;FAŁSZ;PRAWDA;PRAWDA}. Następnie tworzona jest kolejna tablica. Jeżeli element w pierwszej tablicy ma wartość PRAWDA, w drugiej tablicy pojawi się wartość z odpowiedniej komórki. Jeżeli ma wartość „FAŁSZ”, element w drugiej tablicy przyjmie wartość zero. Po tej operacji pierwsza tablica usuwana jest z pamięci komputera i ostatecznie funkcja JEŻELI zwraca tablicę {23;0;15;7}. Tablica ta przekazywana jest następnie jako argument do funkcji SUMA, która, zgodnie ze swoim przeznaczeniem, zwraca sumę wszystkich elementów w tablicy. W naszym przykładzie 45. Na koniec zobacz jeszcze co się stanie jeżeli nakażesz Excelowi potraktować powyższą formułę w sposób „nietablicowy”.

Formuły tablicowe

Żadna z opisanych wyżej tablic nie zostanie w takim przypadku utworzona. Tylko jedna komórka zakresu zostanie sprawdzona (komórka w wierszu, w którym znajduje się funkcja). W naszym przypadku 15>0, więc jako pierwszy argument funkcja JEŻELI otrzyma wartość logiczną PRAWDA. Następnie do funkcji SUMA przekazany zostanie CAŁY zakres B7:B10 i w rezultacie funkcja zwróci wartość 13 (23-32+15+7). Gdyby w komórce zamiast 15 była liczba mniejsza od zera funkcja JEŻELI jako argument otrzymałaby wartość FAŁSZ i w konsekwencji do funkcji SUMA przekazana zostałaby tylko wartość zero. Nasza SUMA zwróciłaby w rezultacie także wartość zero.

Zbliżamy się do końca

Trochę się rozpisałem. Miało być tylko o rozpoznawaniu funkcji tablicowych, a skończyło się na opisie sposobu ich działania. Wydaje mi się jednak, że właściwe „rozpoznanie” zależy całkowicie od zrozumienia tego jak działają tablice w formułach. Kiedy to zrozumiesz będziesz umiał stwierdzić, że określona formuła powinna być wprowadzona jako formuła tablicowa.

Oczywiście wprowadzona jako nietablicowa może zwracać JAKIŚ wynik (o czym mogłeś się sam przed chwilą przekonać). Jeżeli jednak zrozumiesz mechanizm, będziesz widział, że taki wynik jest ZŁY i do poprawnego działania konieczne jest jednak Ctr+Shift+Enter. Jak wszystko, zrozumienie i posługiwanie się formułami tablicowymi wymaga praktyki. Warto jednak poświęcić trochę czasu na zrozumienie wszystkiego. Tablice w formułach umożliwiają bowiem rozwiązanie wielu problemów, które na pierwszy rzut oka wydawać się mogą nierozwiązywalne.

Jak zwykle zachęcam do komentowania, zadawania pytań oraz wytykania błędów.

Marcin

SUPLEMENT:

Poniżej kilka uwag, które otrzymałem od Krzysztofa (któremu, przy okazji, bardzo dziękuję), a które wydają sie doskonale uzupełniać to o czym właśnie napisałem. Oddaję więc „głos” Krzysztofowi. W jaki sposób można obejrzeć i sprawdzić wartości pośrednich wyników obliczeń, np. postaci tablic tworzonych w pamięci komputera i służących do wykonania kolejnych działań? Nic trudnego! Przykłady:

1. Uaktywnij zakres komórek z funkcją tablicową JEŻELI:

{=JEŻELI($B$7:$B$10>0;”większe”;”mniejsze”)}

Następnie, na pasku formuły zaznacz argument funkcji (jak poniżej):

{=JEŻELI($B$7:$B$10>0;”większe”;”mniejsze”)}

Wciśnij przycisk F9 (czyli Oblicz), a uzyskasz (na pasku formuły) pobrane do obliczeń wartości argumentów, jak niżej:

=JEŻELI({23\-32\15\7};”większe”;”mniejsze”)

– zapis przy użyciu ukośnika lewego oznacza, że mamy do czynienia z elementami tablicy pionowej (kolumnowej), elementy tablic wierszowych oddzielone są standardowym znakiem ; (średnik).

2. Uaktywnij zakres komórek z funkcją tablicową JEŻELI:

{=JEŻELI($B$7:$B$10>0;”większe”;”mniejsze”)}

Następnie, na pasku formuły zaznacz argument funkcji (jak poniżej, wraz z operatorem większości i wartością kryterium – „0”):

{=JEŻELI($B$7:$B$10>0;”większe”;”mniejsze”)}

Wciśnij przycisk F9, a uzyskasz obliczone wartości argumentów, jak niżej:

=JEŻELI({PRAWDA\FAŁSZ\PRAWDA\PRAWDA};”większe”;”mniejsze”)

czyli utworzoną w pamięci komputera tablicę:

{PRAWDA;FAŁSZ;PRAWDA;PRAWDA}

3. Uaktywnij komórkę z funkcją złożoną: SUMA i tablicową JEŻELI:

{=SUMA(JEŻELI($B$7:$B$10>0;$B$7:$B$10;0))}

Następnie, na pasku formuły zaznacz argument funkcji SUMA (jak poniżej):

{=SUMA(JEŻELI($B$7:$B$10>0;$B$7:$B$10;0))}

Wciśnij przycisk F9, a uzyskasz pobrane do obliczeń (sumowania) wartości argumentów, jak niżej:

=SUMA({23\0\15\7})

czyli utworzoną w pamięci komputera tablicę:

{23\0\15\7}

{ 33 comments… read them below or add one }


Fatal error: Cannot assign by reference to overloaded object in /home/klient.dhosting.pl/antiquus/excelblog.pl/public_html/wp-content/themes/thesis_182/lib/classes/comments.php on line 176