W tym poście
zajmiemy się bardzo skomplikowanym rozwiązaniem wykorzystującym funkcje ZEZWALAJ (angielskie LET). Zadanie to omówimy na podstawie rozwiązania
Bila Szysza, który jest znany z rozwiązywania bardzo skomplikowanych formuł,
zajmujących niekiedy kilka linijek i wykorzystujących wiele funkcji.
Zadanie
polega na łączeniu różnych zakresów, a dokładniej różnych list danych w jedną.
Jest to bardzo skomplikowane rozwiązanie (rys. nr 1).
rys. nr 1 — skomplikowane rozwiązanie
Zachęcam do obejrzenia filmu w celu poznania szczegółów tego rozwiązania. W tym filmie przetłumaczyłem wykorzystane formuły i wyjaśniam dokładnie ich mechanizm oraz zależności między poszczególnymi funkcjami. Pojedynczo stosowane funkcje są raczej proste, cała trudność polega na odpowiednim połączeniu wielu funkcji w jedną spójną całość.
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie. Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
W tym poście poznamy wszystko, co musicie wiedzieć o funkcji ILE.LICZB, aby jej swobodnie używać i mieć pewność, że zwraca nam poprawne wyniki. Temat ten omówimy na podstawie przykładowych danych z rys. nr 1.
Rys. nr 1 – przykładowe dane
Wydawało by się, że to prosta funkcja do obliczania ilości konkretnych liczb w komórkach, ale w Excelu z różnymi liczbami zdarzają się problemy. Na przykład jeśli zapiszemy liczbę z kropką (.), to Excel nie rozpozna tej liczby i potraktuje ją jako tekst. Prawidłowy zapis liczb w polskim Excelu używa przecinka do oddzielenia miejsc dziesiętnych (,). Jeśli za pomocą funkcji PODSTAW zamienimy w tej liczbie kropkę na przecinek, to Excel zapisze tę liczbę prawidłowo, ale nadal będzie traktował ją jako tekst (rys. nr 2). To, że Excel rozpoznaje liczbę jako tekst możemy łatwo rozpoznać, ponieważ wyrównuje ją do lewej strony komórki.
Rys. nr 2 – zamiana kropki na przecinek przy użyciu funkcji PODSTAW
Jeśli zapiszemy funkcję ILE.LICZB dla tego zakresu, nie zwróci ona żadnej wartości (rys. nr 3). Zapis takiej funkcji powinien wyglądać następująco: ILE.LICZB(A2:B2).
Rys. nr 3 – funkcja ILE.LICZB dla tekstu zwraca wartość 0 (zero)
Istotne
jest, że wszystkie funkcje tekstowe zwracają tekst, a nie liczbę. Aby uzyskać z
tekstu wartość liczbową, musimy wykonać na nim dowolne działanie matematyczne, np.
do funkcji PODSTAW dodać wartość zero. Zapis formuły powinien wyglądać
następująco:
=PODSTAW(A2:B2)+0
Po
zatwierdzeniu takiej formuły tekst zostanie zamieniony na liczbę i wtedy
funkcja ILE.LICZB zadziała prawidłowo (rys. nr 4).
Rys. nr 4 – działanie funkcji ILE.LICZB po zamianie tekstu na liczbę
Interesujące
jest, że jeśli w argumencie funkcji ILE.LICZB zapiszemy wartość "1" (w
podwójnych cudzysłowach), to Excel ją znajdzie, zostanie ona zliczona. Zapis formuły
powinien wyglądać następująco:
=ILE.LICZB(A2:B2;"1")
Po
zatwierdzeniu formuły zwróci nam ona kolejną wartość (rys. nr 5).
Rys. nr 5 – działanie formuły z zapisem wartości liczbowej jako tekst
W przykładowych danych w wierszu nr 4, mamy zapisane liczby jako tekst (w podwójnych cudzysłowach lub poprzedzone pojedynczym cudzysłowem). Sprawia to, że funkcja ILE.LICZB nie wykrywa żadnych wartości liczbowych i zwraca wartość zero (0). Widać to na rys. nr 6.
Rys. nr 6 – działanie funkcji ILE.LICZB w stosunku do liczb zapisanych jako tekst
Funkcja ILE.LICZB jest w stanie policzyć daty i czas (wiersz nr 6) w przykładowych danych). Dla Excela data i czas to nic innego jak liczby, co widać na rys. nr 7 po zmianie formatowania z Daty na Ogólne.
Rys. nr 7 – data i czas w postaci liczb i działanie funkcji ILE.LICZB
Kolejny
omawiany przykład dotyczy wartości logicznych PRAWDA i FAŁSZ. Funkcja ILE.LICZB
nie liczy takich wartości, nie interpretuje tych wartości jako 1 (PRAWDA) i 0
(FAŁSZ). Ale jeżeli wpiszemy wartość logiczną np. PRAWDA, jako jeden z
argumentów funkcji, to funkcja go zliczy. Zapis formuły powinien wyglądać
następująco:
=ILE.LICZB(A8:B8;PRAWDA)
Po
zatwierdzeniu takiej formuły funkcja ILE.LICZB zwróci nam wartość 1 (rys. nr 8).
Rys. nr 8 – działanie funkcji ILE.LICZB w odniesieniu do wartości logicznych
Jest to dla
nas istotne, ponieważ mamy często do czynienia z funkcjami logicznymi, np. funkcją
JEŻELI, która zwraca wartości logiczne (PRAWDA lub FAŁSZ). Zapiszmy jako kolejny
argument funkcji ILE.LICZB funkcję JEŻELI dla prostego testu czy 1>2. Jeżeli
warunek jest spełniony chcemy aby funkcja zwróciła wartość logiczną Prawda, a
jeżeli nie – Fałsz. Zapis formuły powinien wyglądać następująco:
Po
zatwierdzeniu powyższej formuły funkcja ILE.LICZB zliczy kolejną wartość (rys.
nr 9).
Rys. nr 9 – zliczanie wartości logicznej z funkcji JEŻELI
Istotne
jest, że z np. funkcji JEŻELI musimy otrzymać pojedynczą wartość, ponieważ
funkcja ILE.LICZB nie zlicza tablic. Czyli funkcja liczb zliczy nam wyniki
testów logicznych, ale jeżeli zrobimy operację która zamieni zwykły test
logiczny na tablicę, to funkcja ILE.LICZB nie zliczy tego wyniku. Zapis takiej
formuły powinien wyglądać następująco:
=ILE.LICZB(A2:B2;{2}>3)
Po
zatwierdzeniu takiej formuły otrzymamy dane przedstawione na rys. nr 10, w
których funkcja ILE.LICZB nie zliczyła wyniku testu.
Rys. nr 10 – nie zliczanie tablic przez funkcję ILE.LICZB
Standardowe liczby są zliczanie bezproblemowo przez funkcję ILE.LICZB, co widać na rys. nr 11.
Rys. nr 11 – zliczanie standardowych liczb
W sytuacji, kiedy w danych mamy błędy, to w wyniku funkcji ILE.LICZB są one ignorowane. (rys. nr 12). Ważne jest, że funkcja ta zwraca wartość 0, a nie błąd.
Rys. nr 12 – ignorowanie błędów przez funkcję ILE.LICZB
Analogicznie wygląda sytuacja, kiedy w danych mamy puste komórki. Wtedy funkcja ILE.LICZB je ignoruje i zwraca wartość 0 (rys. nr 13).
Rys. nr 13 – ignorowanie pustych komórek przez funkcję ILE.LICZB
Tak samo
zostanie zignorowany pusty ciąg tekstowy zapisany "" (dwoma
podwójnymi cudzysłowami). Ale może się zdarzyć sytuacja, że zapiszemy kolejny
argument funkcji ILE.LICZB i postawimy znak ; (średnik), a następni8e zamkniemy
nawias formuły. Zapis takiej funkcji wyglądałby następująco:
=ILE.LICZB(A14:B14;"";)
Po
zatwierdzeniu takiej formuły okaże się, że funkcja ILE.LICZB zliczy nam jedną
pustą komórkę, ponieważ zinterpretuje pusty argument jako wartość zero (rys. nr 14).
Rys. nr 14 – zliczenie pustego argumentu przez funkcję ILE.LICZB
Co istotne,
funkcja ILE.LICZB może pracować na wielu zakresach. Jeżeli będziemy chcieli
zliczyć wszystkie występujące w danych liczby, wystarczy że w argumentach funkcji
podamy poszczególne zakresy. Funkcja ta może pracować na 255 zakresach, podobnie
jak funkcja SUMA. Zapis takiej formuły powinien wyglądać następująco:
=ILE.LICZB(A2:B2;A4:B4;A6:B6;A8:B16)
Po
zatwierdzeniu powyższej formuły otrzymamy wynik przedstawiony na rys. nr 15.
Rys. nr 15 – zliczanie liczb z wielu zakresów
Funkcja ILE.LICZB nie ma skrótu klawiszowego jak funkcja SUMA, aby policzyć od razu całe zakresy (kolumny i wiersze w tabeli), ale w karcie Narzędzia główne w grupie poleceń autosumowania mamy do niej dostęp. Zaznaczamy zakres tabeli, jaki chcemy zliczyć, rozwijamy polecenie Autosumowanie (punkt 2 na rys. nr 16) z karty Narzędzia główne (punkt 1), a następnie wybieramy polecenie Zliczanie (punkt 3).
Rys. nr 16 – ścieżka dostępu do polecenia Zliczanie
Otrzymamy zliczone liczby w zaznaczonym zakresie danych przedstawione na rys. nr 17.
Rys. nr 17 – zliczone dane
Dzięki
wykorzystaniu polecenia Zliczanie, możemy podobnie jak w funkcji SUMA, zliczyć
wartości w komórkach, w poszczególnych wierszach i kolumnach. Według naszych
danych z rys. nr 17 możemy łatwo sprawdzić ile miesięcy pracuje dla nas np. Ewa
(zliczone wiersze) lub ile osób pracowało dla nas w Marcu (zliczone kolumny).
Musimy
zwrócić uwagę, na komórkę K14, w której nie zliczamy wszystkich danych, tak jak
to robiła funkcja SUMA. Tutaj funkcja
ILE.LICZB patrzy tylko na ostatni wiersz, dlatego mamy wynik 4. Zapis funkcji w
tej komórce wygląda następująco:
=ILE.LICZB(G14:J14)
Z tego zapisu
wynika, że funkcja podlicza ilość liczb w ostatnim wierszu a nie w całym
zakresie. Aby uzyskać informację o ilości liczb w całym zakresie musimy ręcznie
zmienić zakres w argumencie funkcji. Zapis formuły powinien wyglądać następująco:
=ILE.LICZB(G2:J13)
Po
zatwierdzeniu takiej formuły otrzymamy sumaryczną ilość liczb w całym zakresie
danych przedstawioną na rys. nr 18.
Rys. nr 18 – zliczona ilość liczb w całym zakresie danych
Na rys. nr 19 mamy przedstawione podsumowanie funkcji ILE.LICZB. Funkcja ta liczy ilość komórek z liczbami w zakresach danych. Potrafi zliczyć liczby, daty, czas. Pomija tekst, liczby zapisane jako tekst, wartości logiczne, błędy oraz puste komórki. Funkcja ta nie ma skrótu klawiszowego, ale można ją znaleźć w grupie poleceń Autosumowanie na karcie Narzędzia główne.
Rys. nr 19 – najważniejsze informacje o funkcji ILE.LICZB
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie. Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
W tym poście omówimy dokładnie działanie funkcji SUMA. Poznamy ciekawostki na jej temat i różne przykłady jej zastosowania. Zadanie to wykonamy na podstawie przykładowych danych z rys. nr 1.
Rys. nr 1 – przykładowe dane
W pierwszym
zestawie danych mamy przedstawiony dochód z podziałem na poszczególne miesiące.
Naszym celem jest zsumowanie dochodu ze wszystkich miesięcy. Użyjemy tutaj
funkcji SUMA. Argumentami funkcji są liczba1, [liczba2], [liczba3…].
Argumenty wypisane po przecinkach mogą sugerować, że musimy tutaj podawać
pojedyncze komórki. Jednak nie jest to konieczne, mianowicie prawidłowo
zadziała zaznaczenie lub wpisanie ręcznie odpowiedniego zakresu. Zapis funkcji
powinien wyglądać następująco:
=SUMA(B2,B3,B4:B13)
Po
zatwierdzeniu formuły otrzymamy wynik przedstawiony na rys. nr 2.
Rys. nr 2 – suma dla podanego zakresu danych
Ze względu na to, że funkcja SUMA jest jedną z najczęściej wykorzystywanych w Excelu, doczekała się skrótu klawiszowego Alt+= (Alt+ znak równa się). Jeśli ustawimy aktywną komórkę, gdzie chcemy wstawić funkcję SUMA, a następnie skorzystamy ze skrótu klawiszowego Alt+=, Excel nie tylko wstawi funkcję SUMA, ale też sam ustawi odpowiedni zakres (rys. nr 3).
Rys. nr 3 – Automatyczne wstawienie zakresu do funkcji SUMA
W drugiej tabelce mamy zbliżone dane, ale mamy niepełne informacje. Mianowicie brakuje danych z trzech miesięcy (rys. nr 4).
Rys. nr 4 – niepełne dane
Jeśli ustawimy
aktywną komórkę pod danymi i wciśniemy skrót klawiszowy Alt+=, to Excel automatycznie
zaznaczy cały zakres danych, włącznie z komórkami z tekstem brak danych. Zapis
funkcji będzie wyglądał następująco:
=SUMA(E2:E13)
Moglibyśmy
ręcznie zaznaczyć odpowiedni zakres, który nie uwzględniałby komórek z brakiem
danych. Jednak wtedy po wpisaniu brakujących danych, nie zostały by one uwzględnione
w obliczeniach. Dlatego zostawiamy domyślny zakres. Excel bez problemu poradzi
sobie z komórkami z tekstem w zakresie, po prostu ich nie doda. Po
zatwierdzeniu formuły otrzymamy wynik przedstawiony na rys. nr 5. Dzięki zaznaczeniu całego zakresu Excel bez
problemu poradzi sobie z przeliczeniem sumy, kiedy dane z pozostałych miesięcy
zostaną dopisane.
Rys. nr 5 – wynik sumy dla danych z komórkami tekstowymi
Dla nas
istotne jest, że funkcja SUMA zignoruje komórki z tekstem czy z wartościami
logicznymi PRAWDA lub FAŁSZ.
W trzeciej
tabeli z danymi mamy tablicę, którą chcemy podsumować zarówno po wierszach jak
i po kolumnach. W takiej sytuacji wystarczy najpierw zaznaczyć wypełniony
zakres danych, dodatkowo kolumnę z prawej strony danych i wiersz pod spodem, a następnie
skorzystać ze skrótu klawiszowego Alt+=. Excel automatycznie obliczy sumy dla
poszczególnych wierszy i kolumn oraz sumę końcową dla tych wszystkich danych.
Wyniku możemy obejrzeć na rys. nr 6.
Rys. nr 6 – sumy dla poszczególnych kolumn i wierszy oraz suma końcowa
W takiej sytuacji Excel wstawi w każdą komórkę tak jakby osobną formułę dla odpowiedniego dla danej komórki zakresu. I tak np. dla wyniku sumy z wiersza czwartego, mamy formułę =SUMA(H4:K4), co widać na rys. nr 7.
Rys. nr 7 – formuła SUMY dla czwartego wiersza
Analogicznie
Excel zadziała dla kolumn, czyli dla każdej kolumny wpisze formułę z odpowiednim
zakresem. I tak np. dla kolumny H, zapis formuły będzie wyglądał następująco:
=SUMA(H2:H13).
Warto
wspomnieć, że możemy zrobić zwykłe dodawanie. Wtedy nie używamy nazwy funkcji
SUMA, a dodajemy poszczególne komórki łącząc je znakiem plus (+). W tej
sytuacji możemy dodawać kilka liczb, ale jeśli w zwykłym dodawaniu spróbujemy
dodać komórkę z tekstem, to Excel zwróci nam błąd (rys. nr 8). Zwykłe dodawanie
zadziała prawidłowo, jeśli będziemy chcieli dodać wartości logiczne PRAWDA lub
FAŁSZ. Wartości tekstowych Excel nie jest w stanie przekształcić, a wartości logiczne
tak. Często w Excelu wartość logiczna FAŁSZ jest utożsamiana z wartością
liczbową 0 (zero), a PRAWDA z wartością 1.
Rys. nr 8 – błąd dodawania
Jeśli
zrobimy zwykle dodawanie z wartością logiczną PRAWDA otrzymamy wynik powiększony
o 1. Natomiast jeśli użyjemy funkcji SUMA (z uwzględnieniem wartości logicznej
PRAWDA), wartość ta zostanie pominięta.
Kolejną
zaletą funkcji SUMA, jest sytuacja kiedy do zaznaczonego zakresu, chcemy dodać
jakąś konkretną liczbę, to Excel bez problemu wykona takie działanie. W takie
sytuacji zapis formuły wyglądał by następująco:
=SUMA(B2:B13;2000)
Ponadto
dodatkowy dodawany argument może być tablicą. Wtedy też Excel poradzi sobie z
takim obliczeniem. Zapis formuły może wtedy wyglądać następująco:
=SUMA(B2:B13;{2000;3000}
W obu
omówionych powyżej otrzymamy prawidłowe wyniki dodawania.
Podsumowując,
funkcja SUMA sumuje zakresy danych, ale w ograniczonej ilości (maksymalnie 255
zakresów). Jako jedyna z funkcji Excela ma przypisany skrót klawiszowy ALT+=. Ponadto ignoruje wartości logiczne (PRAWDA i
FAŁSZ) oraz tekst. Kolejnym atutem funkcji SUMA jest to, że dodaje do zakresów
stałe wartości wpisane ręczne jako argumenty oraz tablice danych (rys. nr 9).
Rys. nr 9 – zalety funkcji SUMA
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie. Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
W tym poście nauczymy się jak przygotować dane do tabeli przestawnej. Zadanie to wykonamy na podstawie przykładowych danych z rys. nr 1.
Rys. nr 1 – przykładowe dane
W przygotowaniu danych do tabeli przestawnej przede wszystkim musimy zadbać o to, aby wszystkie kolumny miały nagłówki. Jak widać na rysunku powyżej kolumna E nie ma takiego nagłówka. Spróbujemy teraz wstawić tabelę przestawną, zaznaczamy cały zakres danych i wybieramy polecenie Tabela przestawna z karty Wstawianie (rys. nr 2).
Rys. nr 2 – polecenie Tabela przestawna
Otworzy nam się okno Tworzenia tabeli przestawnej, w którym wybieramy miejsce jej wstawienia – Nowy arkusz i zatwierdzamy przyciskiem OK (rys. nr 3).
Rys. nr 3 – okno Tworzenia tabeli przestawnej
Excel wyświetli nam komunikat z informacją, że nazwa pola jest nieprawidłowa. Wynika to z faktu, że jedna z kolumn nie ma nagłówka (rys. nr 4).
Rys. nr 4 – komunikat Excela
Musimy zadbać, aby wszystkie kolumny miały wypełniony nagłówek. W nagłówku kolumny E wpisujemy nazwę Ilość. Po tej zmianie nagłówki zostały przedstawione na rys. nr 5.
Rys. nr 5 – prawidłowo wypełnione nagłówki kolumn
Dodatkowo,
jeśli w naszych danych będziemy mieli jakąś pustą kolumnę, to ponownie Excel
nie pozwoli nam wstawić tabeli przestawnej.
Istotne
jest, że możemy wstawiać puste wiersze. Z tym Excel nie robi problemów. Problem
pojawi się jednak przy zaznaczeniu całych danych, ponieważ jeśli użyjemy sobie
standardowego skrótu klawiszowego za zaznaczania całych danych Ctrl+A,
to Excel zaznaczy dane tylko do pierwszego pustego wiersza (rys. nr 6). Jeśli pustych
wierszy było by dużo więcej, ciężko takie dane byłoby nam zaznaczyć.
Rys. nr 6 – zaznaczenie danych tylko do pierwszego pustego wiersza
W takim
przypadku możemy zaznaczyć całość danych (do ostatniej komórki) za pomocą
skrótu klawiszowego Ctrl+Shift+End, ale wtedy Excel zaznaczy dane o
jedną kolumnę więcej, czyli do kolumny H, a kolumna H jest pusta. Możemy przesunąć
takie zaznaczenie za pomocą kolejnego skrótu klawiszowego – Shift+strzałka w
lewo.
Po takim
zaznaczeniu danych (z pustym wierszem), wybieramy polecenie Tabela przestawna z
karty Wstawianie (jak na rys. nr 2). Otworzy nam się okno Tworzenia tabeli
przestawnej, gdzie określamy miejsce wstawienia tabeli – Nowy arkusz.
Zatwierdzamy Tworzenie tabeli przestawnej za pomocą przycisku OK (jak na rys.
nr 3).
Otworzy nam
się nowy arkusz Excela, gdzie możemy zająć się tworzeniem tabeli. Pole
Sprzedawca przeciągamy do obszaru etykiet wierszy (zaznaczone strzałką
na rys. nr 7).
Rys. nr 7 – przeciąganie pola Sprzedawca do obszaru etykiet wierszy
Otrzymamy tabelę przestawną, gdzie jak widać mamy uwzględnione dane z pustego wiersza (rys. nr 8).
Rys. nr 8 – pusty wiersz uwzględniony w tabeli przestawnej
Z tego
względu przy tworzeniu tabeli przestawnej musimy unikać pustych wierszy.
Najlepiej żeby dane do tabeli przestawnej nie zawierały pustych komórek.
Usuwamy pusty wiersz z danych – zaznaczamy cały wiersz, następnie klikamy prawym przyciskiem myszy i z podręcznego menu wybieramy polecenie Usuń (rys. nr 9).
Rys. nr 9 – polecenie Usuń
Teraz przechodzimy na arkusz z tabelą przestawną, klikamy w jej obszarze prawym przyciskiem myszy i z podręcznego menu wybieramy polecenie Odśwież (rys. nr 10).
Rys. nr 10 – polecenie Odśwież
Teraz możemy dokończyć naszą tabelę przestawną. Przeciągamy pole Wartość do obszaru Podsumowań wartości (zaznaczone strzałką na rys. nr 11).
Rys. nr 11 – przeciąganie pola Wartość do obszaru podsumowań
Otrzymamy prostą tabele przestawną podsumowującą wyniki sprzedaży poszczególnych sprzedawców przedstawiona na rys. nr 12.
Rys. nr 12 – tabela przestawna
Podsumowując, do stworzenia tabeli przestawnej potrzebujemy spójnych danych, bez pustych komórek, wierszy czy kolumn. Istotne jeszcze jest, że w każdej kolumnie musi być odrębny typ danych, aby wyniki dało się łatwo zinterpretować.
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie. Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
