Tabele w Excelu - jak zrobić tabelę krok po kroku

Większość ludzi robi w Excelu tabelę tak: wpisuje dane, pogrubia nagłówki, dorysowuje obramowanie i na tym kończy. Wygląda jak tabela, ale dla Excela to wciąż zwykłe komórki. Prawdziwa tabela Excela to jedno naciśnięcie dwóch klawiszy i zupełnie inne zachowanie arkusza.

Pokażę, jak zamienić zakres w tabelę, jak ją nazwać i co się wtedy zmienia: suma pod kolumną, która liczy to, co widać po filtrze, nazwy kolumn zamiast adresów komórek w formułach, jedna formuła na całą kolumnę i nowe wiersze, które tabela obejmuje bez poprawiania zakresów. Na koniec sytuacje, w których Excel odmawia zrobienia tabeli, i co wtedy poprawić w danych.

Największa korzyść nie jest widoczna od razu: wykres, lista rozwijana i tabela przestawna oparte na tabeli nie wymagają już poprawiania zakresu po każdym dopisanym wierszu. Wszystkie przykłady są w pliku do pobrania, każdy dział artykułu na osobnym arkuszu.

W tym poradniku:

1. Czym jest tabela Excela, a czym nie jest
2. Jak zrobić tabelę w Excelu - Ctrl + T
3. Nazwa tabeli - po co ją zmieniać
4. Wiersz sumy
5. Odwołania strukturalne - nazwy kolumn w formułach
6. Kolumna obliczana - jedna formuła na całą kolumnę
7. Nowe wiersze dopisują się same
8. Sortowanie, filtrowanie i fragmentatory
9. Wygląd tabeli i jak ją usunąć
10. Dlaczego Ctrl + T nie działa
11. Podsumowanie

Czym jest tabela Excela, a czym nie jest

Słowo „tabela” znaczy w Excelu trzy różne rzeczy i stąd bierze się większość nieporozumień:

• tabelka, czyli dane z pogrubionym nagłówkiem i obramowaniem - dla Excela to zwykły zakres komórek, na przykład A1:E13. Ładnie wygląda i nic więcej nie potrafi,
• tabela Excela - obiekt, który tworzysz skrótem Ctrl + T. Ma własną nazwę, nagłówki z filtrami, rozszerza się po dopisaniu wiersza i da się do niej odwołać nazwą kolumny,
• tabela przestawna - raport, który podsumowuje dane w kilka kliknięć. To zupełnie inne narzędzie, opisane w poradniku o tabelach przestawnych.

Ten artykuł jest o drugiej z nich. W pliku do pobrania arkusz Zakres ma dane bez tabeli, a arkusz Tabela te same dane już jako tabelę - różnicę najłatwiej zobaczyć, przeskakując między nimi.

Jak zrobić tabelę w Excelu - Ctrl + T

Kliknij dowolną komórkę wewnątrz danych i naciśnij Ctrl + T. Excel sam rozpozna, gdzie dane się zaczynają i kończą, i pokaże okno Utwórz tabelę z proponowanym zakresem.

Zanim klikniesz OK, sprawdź dwie rzeczy:

1. zakres - czy obejmuje wszystkie wiersze. Excel zatrzymuje się na pierwszym pustym wierszu i pustej kolumnie,
2. „Moja tabela ma nagłówki” - zaznaczone, jeśli pierwszy wiersz to nazwy kolumn. Gdy odznaczysz, Excel dopisze nad danymi własny wiersz „Kolumna1″, „Kolumna2″ i tak dalej.

Po zatwierdzeniu dane wyglądają inaczej: nagłówki mają strzałki filtrowania, wiersze są pokolorowane naprzemiennie, a na wstążce pojawia się nowa karta Projekt tabeli.

Ten sam efekt dają dwa polecenia na wstążce: Wstaw → Tabela oraz Narzędzia główne → Formatuj jako tabelę (to drugie pozwala od razu wybrać wygląd). W angielskim Excelu są to Insert → Table i Format as Table, a skrót Ctrl + T działa tam tak samo.

Nazwa tabeli - po co ją zmieniać

Każda tabela dostaje nazwę: pierwsza w pliku to Tabela1, druga Tabela2. Widać to już w oknie „Utwórz tabelę” wyżej, w polu Jaka jest nazwa tabeli?. Później zmienia się ją na karcie Projekt tabeli, w polu Nazwa tabeli po lewej stronie wstążki.

W pliku tabela nazywa się Zamowienia i to nie jest kosmetyka. Nazwa pojawia się w każdej formule, która sięga do tabeli, w źródle wykresu i w źródle tabeli przestawnej. „Zamowienia” czyta się po roku, „Tabela1″ nie mówi nic.

Zasady są trzy: nazwa bez spacji (zamiast nich podkreślenie), bez polskich znaków, jeśli będziesz ją wpisywać w formułach, i nie może wyglądać jak adres komórki. Nazwa nie może się też powtarzać w jednym pliku.

Wiersz sumy

Na karcie Projekt tabeli zaznacz Wiersz sumy. Pod tabelą pojawi się wiersz z podsumowaniem ostatniej kolumny, a w każdej jego komórce masz listę do wyboru: suma, średnia, licznik, minimum, maksimum.

W naszym pliku wiersz sumy pokazuje 238 sztuk i 6 502 zł. Excel wpisuje tam nie SUMA, a SUMY.CZĘŚCIOWE z pierwszym argumentem 109:

=SUMY.CZĘŚCIOWE(109;[Kwota])

Numer 109 oznacza „suma, ale tylko z widocznych wierszy”. To jest sedno: po odfiltrowaniu jednego klienta wiersz sumy pokaże kwotę tego klienta, a nie całość. Zwykła SUMA nie zwraca uwagi na filtr i dalej pokazywałaby 6 502 zł.

Wiersz podsumowania można też wpisać ręcznie pod tabelą, ale wtedy nie jest częścią tabeli i po dopisaniu nowego zamówienia znajdzie się w środku danych. Jeśli ma liczyć sumę, niech to będzie wiersz sumy tabeli.

Odwołania strukturalne - nazwy kolumn w formułach

W tabeli zamiast adresów komórek można pisać nazwy kolumn. Taki zapis nazywa się odwołaniem strukturalnym i wygląda tak: nazwa tabeli, a w nawiasie kwadratowym nazwa kolumny.

=SUMA(Zamowienia[Kwota])

Formuła zwraca 6 502 zł, czyli to samo, co =SUMA(E2:E13). Różnica pokazuje się dopiero przy trzynastym zamówieniu: zapis z nazwą kolumny policzy je od razu, a zapis z zakresem E2:E13 zostanie przy dwunastu wierszach i nikt tego nie zauważy.

Tak samo działa to w funkcjach z warunkiem. Suma zamówień jednego klienta:

=SUMA.JEŻELI(Zamowienia[Klient];"Hotel Dworek";Zamowienia[Kwota])

Nawiasów kwadratowych nie musisz wpisywać z pamięci. Wpisz =SUMA( i zaznacz myszą komórki w kolumnie Kwota - Excel sam zamieni zaznaczenie na Zamowienia[Kwota]. Po wpisaniu nazwy tabeli i nawiasu kwadratowego pokazuje się też lista kolumn do wyboru.

Kolumna obliczana - jedna formuła na całą kolumnę

Dopisz nagłówek nowej kolumny obok tabeli i wpisz w pierwszej komórce formułę. Excel skopiuje ją na wszystkie wiersze tabeli sam, bez przeciągania i bez dwukliku.

W arkuszu Kolumna obliczana kolumna Rabat 5% ma w pierwszym wierszu:

=[@Kwota]*0,05

Zapis [@Kwota] znaczy „komórka z kolumny Kwota w tym samym wierszu”. Pierwsze zamówienie daje 17,40 zł rabatu, ostatnie 52,20 zł, a formuła jest jedna dla całej kolumny.

Dzięki temu nie ma formuł „do połowy kolumny”, czyli najczęstszej cichej usterki w arkuszach. Po dopisaniu wiersza tabela wstawi formułę także tam.

Tabele, obliczenia i odwołania, które nie rozjeżdżają się przy kopiowaniu

✓Tworzenie i edycja tabel, wiersz sumy i style

✓Sortowanie, filtrowanie i fragmentatory

✓Obliczenia w tabeli i poza nią

✓40 lekcji · zadania z rozwiązaniami: 52 · gwarancja zwrotu 30 dni

Zobacz kurs

Nowe wiersze dopisują się same

Wpisz coś w pierwszym wierszu pod tabelą - tabela obejmie go od razu: dostanie kolor, filtry i formuły z kolumn obliczanych, a wiersz sumy zjedzie o jeden w dół. Tak samo jest z nową kolumną dopisaną z prawej strony.

Ta jedna rzecz zmienia najwięcej, bo z tabeli żyje wszystko, co na niej zbudujesz:

• wykres oparty na tabeli sam pokazuje nowe wiersze, bez poprawiania zakresu danych,
• lista rozwijana z kolumny tabeli rośnie razem z nią,
• tabela przestawna ze źródłem w tabeli po dopisaniu danych wymaga tylko odświeżenia, a nie zmiany źródła,
• WYSZUKAJ.PIONOWO i inne funkcje wyszukujące dostają pełny zakres bez poprawek.

Jeden wyjątek: gdy między tabelą a wpisywanym wierszem jest pusty wiersz, tabela go nie obejmie. Wpisuj w pierwszym wierszu bezpośrednio pod tabelą albo naciśnij Tab w ostatniej komórce tabeli - wtedy Excel sam doda nowy wiersz.

Sortowanie, filtrowanie i fragmentatory

Strzałka w nagłówku otwiera listę z sortowaniem i filtrem. Przy sortowaniu w tabeli nie trzeba zaznaczać danych - Excel wie, gdzie tabela się kończy, więc wiersze nie rozjadą się na „posortowaną jedną kolumnę i resztę po staremu”. To najczęstszy sposób, w jaki ludzie psują sobie dane w zwykłym zakresie.

Filtr pokazuje część wierszy, a wiersz sumy liczy to, co widać. Po wybraniu w kolumnie Klient pozycji Hotel Dworek suma spadnie z 6 502 zł na 3 765 zł.

Zamiast listy filtrów można wstawić fragmentator (Projekt tabeli → Wstaw fragmentator) - kafelki z wartościami kolumny, które klika się jak przyciski. Wygodne, gdy arkusz ma oglądać ktoś inny.

Wygląd tabeli i jak ją usunąć

Styl zmienia się na karcie Projekt tabeli w galerii Styl tabeli. Obok są przełączniki: wiersz nagłówka, wiersz sumy, naprzemienne wiersze i naprzemienne kolumny. Naprzemienne kolorowanie nie jest ozdobą - przy szerokiej tabeli trzyma wzrok w jednym wierszu.

Tabelę da się rozwiązać bez utraty danych: Projekt tabeli → Konwertuj na zakres. Zostaje formatowanie, znikają filtry, nazwa tabeli, wiersz sumy i odwołania strukturalne - formuły z nazwami kolumn zamienią się na zwykłe adresy komórek.

Jeśli chcesz zdjąć tylko kolory, wybierz w galerii styl Brak (pierwszy, pusty) albo Narzędzia główne → Wyczyść → Wyczyść formaty. Tabela zostanie tabelą, straci tylko wygląd.

Naciśnięcie Delete kasuje dane, nie tabelę. Do rozwiązania tabeli służy wyłącznie „Konwertuj na zakres”.

Dlaczego Ctrl + T nie działa

Arkusz Co nie działa w pliku ma trzy najczęstsze powody w jednym miejscu:

• scalone komórki - Excel zgłosi błąd albo rozbije scalenie. Tabela nie przyjmuje scalonych komórek. Zamiast scalania użyj wyrównania do środka albo zostaw podsumowanie poza tabelą,
• pusty nagłówek - kolumna bez nazwy dostanie „Kolumna1″. Nie jest to błąd, ale widzisz to potem w każdej formule i w tabeli przestawnej,
• wiersz podsumowania wewnątrz danych - wejdzie do tabeli jako zwykły wiersz i będzie liczony razem z zamówieniami. Usuwamy go przed utworzeniem tabeli, a jego rolę przejmuje wiersz sumy.

Do tego trzy sytuacje, w których tabeli nie zrobisz w ogóle albo zachowa się inaczej, niż oczekujesz:

• arkusz jest chroniony - najpierw Recenzja → Nie chroń arkusza,
• plik jest w starym formacie .xls albo w trybie zgodności - zapisz jako .xlsx,
• tabela nie rozszerza się po dopisaniu wiersza - wyłączona jest opcja autoformatowania. Włącz ją: Plik → Opcje → Sprawdzanie → Opcje Autokorekty → Autoformatowanie podczas wpisywania → Dołącz nowe wiersze i kolumny do tabeli.

Osobny przypadek to liczby zapisane jako tekst - w arkuszu Co nie działa dwie kwoty są tekstem. Tabela powstanie bez problemu, ale wiersz sumy je pominie i suma wyjdzie za mała, bez żadnego ostrzeżenia. Jak to rozpoznać i naprawić, opisuję w poradniku o sumie w Excelu.

Podsumowanie

Tabela Excela to nie wygląd, a zachowanie: dane mają nazwę, same się rozszerzają i dają się opisać w formułach nazwami kolumn. Zanim przejdziesz dalej, sprawdź na własnym arkuszu:

• dane mają jeden wiersz nagłówków i żadnych scalonych komórek,
• w danych nie ma pustych wierszy ani wiersza podsumowania,
• zakres zamieniony w tabelę skrótem Ctrl + T,
• tabela ma nazwę, która mówi, co w niej jest,
• wiersz sumy włączony tam, gdzie liczysz kwoty,
• formuły sięgają do tabeli nazwami kolumn, nie adresami komórek,
• wykresy, listy rozwijane i tabele przestawne mają źródło w tabeli.

Plik z wszystkimi przykładami pobierzesz poniżej. Każdy dział ma w nim osobny arkusz, więc możesz sprawdzić formuły i od razu spróbować samodzielnie.

Dalej naturalnie wychodzi się na raporty: skoro dane są już tabelą, tabela przestawna podsumuje je w kilka kliknięć i odświeży jednym przyciskiem.

Nowy kurs

Dane są już tabelą. Teraz raport, który odświeżasz jednym kliknięciem

✓Od pierwszej tabeli przestawnej po dashboard

✓Odświeżanie raportu zamiast przeklejania danych

✓Zestawy ćwiczeń: 9 · certyfikat

✓40 lekcji · gwarancja zwrotu 30 dni

Zobacz kurs

Gwarancja zwrotu 30 dni · wszystkie lekcje dostępne od razu