Używanie odwołań strukturalnych w tabelach programu Excel

Podczas tworzenia tabeli programu Excel do tabeli i każdego nagłówka kolumny w tabeli jest przypisywana nazwa. Gdy do tabeli programu Excel dodawane są formuły, te nazwy mogą być wyświetlane automatycznie w trakcie wprowadzania formuły i wybierania odwołań do komórek w tabeli zamiast ich ręcznego wprowadzania. Przykład działania programu Excel:

Zamiast używać jawnych odwołań do komórek	program Excel korzysta z nazw tabel i kolumn
=SUMA(C2:C7)	= SUMA(SprzWDz[Kwota sprzedaży])

Kombinacje nazw tabel i kolumn są nazywane odwołaniami strukturalnymi. Nazwy w odwołaniach strukturalnych są dostosowywane za każdym razem, gdy dane są dodawane lub usuwane z tabeli.

Odwołania strukturalne pojawiają się również, gdy formuła zawierająca odwołanie do danych w tabeli jest tworzona poza tabelą programu Excel. Odwołania ułatwiają zlokalizowanie tabel w dużych skoroszytach.

Aby wstawić odwołania strukturalne w formule, zaznacz komórki tabeli, do których mają zostać utworzone odwołania, zamiast wpisywać odwołania do tych komórek w formule. Użyjmy poniższych danych przykładowych, aby wprowadzić formułę, która automatycznie korzysta z odwołań strukturalnych w celu obliczenia prowizji od sprzedaży.

Sprzedawca	Region	Kwota sprzedaży	% prowizji	Kwota prowizji
Krzysztof	Płn.	260	10%
Tomasz	Płd.	660	15%
Karolina	Wschód	940	15%
Eryk	Zach.	410	12%
Danuta	Płn.	800	15%
Ania	Płd.	900	15%

1. Skopiuj przykładowe dane z powyższej tabeli (wraz z nagłówkami kolumn) i wklej je w komórce A1 nowego arkusza programu Excel.
2. Aby utworzyć tabelę, zaznacz dowolną komórkę w zakresie danych i naciśnij klawisze Ctrl+T.
3. Upewnij się, że pole Moja tabela ma nagłówki jest zaznaczone, a następnie wybierz przycisk OK.
4. W komórce E2 wpisz znak równości (=) i zaznacz komórkę C2.
   Na pasku formuły po znaku równości zostanie wyświetlone odwołanie strukturalne [@[Kwota sprzedaży]].
5. Bezpośrednio po nawiasie zamykającym wpisz gwiazdkę (*) i zaznacz komórkę D2.
   Na pasku formuły po gwiazdce zostanie wyświetlone odwołanie strukturalne [@[% prowizji]].
6. Naciśnij klawisz Enter.
   Program Excel automatycznie tworzy kolumnę obliczeniową i kopiuje formułę w dół całej kolumny, dostosowując ją dla każdego wiersza.

Co się stanie, gdy użyję jawnych odwołań do komórek?

Jeśli wprowadzisz jawne odwołania do komórek w kolumnie obliczeniowej, trudniej będzie rozpoznać dane obliczane przy użyciu formuły.

1. W przykładowym arkuszu zaznacz komórkę E2
2. Na pasku formuły wpisz =C2*D2 i naciśnij klawisz Enter.

Zwróć uwagę, że podczas kopiowania formuły w dół kolumny program Excel nie używa odwołań strukturalnych. Jeśli na przykład dodasz kolumnę między istniejącymi kolumnami C i D, trzeba będzie zmodyfikować formułę.

Jak mogę zmienić nazwę tabeli?

Gdy tworzysz tabelę programu Excel, w programie Excel jest tworzona domyślna nazwa tabeli (Tabela1, Tabela2 itd.), ale możesz zmienić nazwę tabeli, aby była bardziej znacząca.

1. Zaznacz dowolną komórkę w tabeli, aby wyświetlić kartę Projekt tabeli na wstążce.
2. Wpisz odpowiednią nazwę w polu Nazwa tabeli i naciśnij klawisz Enter.

W naszych danych przykładowych użyliśmy nazwy SprzWDz.

Zastosuj następujące zasady dotyczące tworzenia nazw tabel:

• Używaj prawidłowych znaków Nazwa powinna zawsze zaczynać się od litery, znaku podkreślenia (_) lub ukośnika odwrotnego (\). Pozostałe znaki użyte w nazwie mogą być literami, cyframi, kropkami lub znakami podkreślenia. Jako nazwy nie można używać liter "K", "k", "W" lub "w", ponieważ są one używane jako skróty do zaznaczania kolumny lub wiersza aktywnej komórki podczas wpisywania ich w polu Nazwa lub Przechodzenie do .

• Nie używaj odwołań do komórek Nazwy nie mogą być identyczne z odwołaniami do komórek, na przykład Z$100 lub W1K1.
• Nie używaj spacji do oddzielania wyrazów W nazwie nie można używać spacji. Jako separatory wyrazów można używać znaku podkreślenia (_) i kropki (.). Na przykład SprzWDz, Podatek_od_sprzedaży lub Pierwszy.Kwartał.
• Nie więcej niż 255 znaków Nazwa tabeli może zawierać maksymalnie 255 znaków.
• Używaj unikatowych nazw tabel Zduplikowane nazwy są niedozwolone. Program Excel nie rozróżnia małych i wielkich liter w nazwach, dlatego w przypadku wprowadzenia nazwy "Sprzedaż", gdy w tym samym skoroszycie istnieje już nazwa "SPRZEDAŻ", zostanie wyświetlony monit o wprowadzenie unikatowej nazwy.
• Używanie identyfikatora obiektu Jeśli planujesz połączenie tabel, tabel przestawnych i wykresów, dobrym pomysłem jest poprzedzenie nazw typem obiektu. Na przykład: tbl_Sales dla tabeli sprzedaży, pt_Sales dla tabeli przestawnej sprzedaży, chrt_Sales dla wykresu sprzedaży lub ptchrt_Sales dla wykresu przestawnego sprzedaży. Dzięki temu wszystkie imiona i nazwiska będą przechowywane na liście uporządkowanej w Menedżerze nazw.

Reguły składni odwołań strukturalnych

Można także ręcznie wprowadzać i zmieniać odwołania strukturalne w formule, ale w tym celu pomocne będzie zrozumienie składni odwołań strukturalnych. Omówmy następujący przykład formuły:

=SUMA(SprzWDz[[#Sumy],[Kwota sprzedaży]],SprzWDz[[#Dane],[Kwota prowizji]])

Ta formuła zawiera następujące składniki odwołania strukturalnego:

• **Nazwa tabeli:**SprzWDz jest niestandardową nazwą tabeli. Odwołuje się ona do danych w tabeli, bez żadnych nagłówków ani wierszy sumy. Możesz użyć domyślnej nazwy tabeli, takiej jak Tabela1, lub zmienić ją, aby użyć niestandardowej nazwy.
• Specyfikatory kolumn:[Kwota sprzedaży] i [Kwota prowizji] to specyfikatory kolumn, które używają nazw kolumn, które reprezentują. Odwołują się one do danych kolumny, bez żadnych nagłówków ani wierszy sumy. Specyfikatory zawsze należy umieszczać w nawiasach kwadratowych, tak jak pokazano.
• Specyfikator elementu:[#Totals] i [#Data] to specyfikatory elementów specjalnych, które odwołują się do określonych części tabeli, takich jak wiersz sumy.
• Specyfikator tabeli:[[#Sumy];[Kwota sprzedaży]] i [[#Dane];[Kwota prowizji]] to specyfikatory tabel, które reprezentują zewnętrzne części odwołania strukturalnego. Zewnętrzne części odwołań następują po nazwie tabeli i należy je ujmować w nawiasy kwadratowe.
• Odwołanie strukturalne:(SprzWDz[[#Totals],[Kwota sprzedaży]] i SprzWDz[[#Data],[Kwota prowizji]] to odwołania strukturalne reprezentowane przez ciąg, który zaczyna się od nazwy tabeli i kończy specyfikatorem kolumny.

Aby ręcznie utworzyć lub edytować odwołania strukturalne, przestrzegaj następujących reguł składni:

• Umieszczaj specyfikatory w nawiasach kwadratowych Wszystkie specyfikatory tabel, kolumn i elementów specjalnych muszą zostać ujęte w nawiasy kwadratowe ([ ]). Specyfikator zawierający inne specyfikatory wymaga nawiasów zewnętrznych obejmujących nawiasy wewnętrzne innych specyfikatorów. Na przykład: =SprzWDz[[Sprzedawca]:[Region]]
• Wszystkie nagłówki kolumn są ciągami tekstowymi Nie trzeba jednak stosować cudzysłowu, jeśli są one używane w odwołaniu strukturalnym. Liczby lub daty, takie jak 2014 lub 2014-01-01, także są ciągami tekstowymi. W nagłówkach kolumn nie można używać wyrażeń. Na przykład wyrażenie SprzWDzPodsRoczne[[2014]:[2012]] nie będzie działać.

Nagłówki kolumn zawierające znaki specjalne umieszczaj w nawiasach kwadratowych Jeśli istnieją znaki specjalne, cały nagłówek kolumny musi zostać ujęty w nawiasy, co oznacza, że specyfikator kolumny wymaga podwójnego nawiasu. Na przykład: =SprzWDzPodsRoczne[[Kwota $ całkowita]]

Oto lista znaków specjalnych, które wymagają dodatkowych nawiasów w formule:

• Tabulator
• Nowy wiersz
• Powrót karetki
• Przecinek (,)
• Dwukropek (:)
• Kropka (.)
• Lewy nawias kwadratowy ([)
• Prawy nawias kwadratowy (])
• Znak numeru (#)
• Pojedynczy cudzysłów (')
• Podwójny cudzysłów (")
• Lewy nawias klamrowy ({)
• Prawy nawias klamrowy (})
• Znak dolara ($)
• Daszek (^)
• Handlowe „i” (&)
• Gwiazdka (*)
• Znak plus (+)
• Znak równości (=)
• Znak minus (-)
• Symbol większości (>)
• Symbol mniejszości (<)
• Znak dzielenia (/)
• Znak @
• Ukośnik odwrotny (\)
• Wykrzyknik (!)
• Lewy nawias okrągły (()
• Prawy nawias okrągły ())
• Znak procentu (%)
• Znak zapytania (?)
• Backtick (')
• Średnik (;)
• Tylda (~)
• Znak podkreślenia (_)

• Używaj znaku anulowania w przypadku niektórych znaków specjalnych w nagłówkach kolumn Niektóre znaki mają specjalne znaczenie i wymagają użycia znaku pojedynczego cudzysłowu (') jako znaku anulowania. Na przykład: =SprzWDzPodsRoczne['#Elementów]

Oto lista znaków specjalnych, które wymagają znaku anulowania (') w formule:

• Lewy nawias kwadratowy ([)
• Prawy nawias kwadratowy (])
• Znak numeru (#)
• Pojedynczy cudzysłów (')
• Znak @

Używaj znaku spacji, aby poprawić czytelność odwołania strukturalnego Aby poprawić czytelność odwołania strukturalnego, możesz używać znaków spacji. Na przykład: =SprzWDz[ [Sprzedawca]:[Region]] lub =SprzWDz[[#Nagłówki]; [#Dane]; [% prowizji]]

Zalecamy użycie pojedynczej spacji:

• po pierwszym lewym nawiasie kwadratowym ([),
• przed ostatnim prawym nawiasem kwadratowym (]),
• po średniku.

Operatory odwołania

Aby bardziej elastycznie określać zakresy komórek, można używać poniższych operatorów odwołania w celu łączenia specyfikatorów kolumn.

Odwołanie strukturalne:	Odwołuje się do:	Za pomocą:	Co odpowiada zakresowi komórek:
=SprzWDz[[Sprzedawca]:[Region]]	Wszystkie komórki w dwóch lub kilku sąsiadujących kolumnach	: (dwukropek) operator zakresu	A2:B7
=SprzWDz[Kwota sprzedaży];SprzWDz[Kwota prowizji]	Złożenie dwóch lub kilku kolumn	; (średnik) operator składania	C2:C7; E2:E7
=SprzWDz[[Sprzedawca]:[Kwota sprzedaży]] SprzWDz[[Region]:[% prowizji]]	Część wspólna dwóch lub kilku kolumn	(spacja) operator przecięcia	B2:C7

Specyfikatory elementów specjalnych

Aby odwołać się do specyficznej części tabeli, na przykład tylko do wiersza sum, możesz użyć w odwołaniu strukturalnym dowolnego z poniższych specyfikatorów elementów specjalnych.

Specyfikator elementu specjalnego:	Odwołuje się do:
#Wszystko	Cała tabela łącznie z nagłówkami kolumn, danymi i sumami (jeśli istnieją).
#Dane	Tylko wiersze danych.
#Nagłówki	Tylko wiersz nagłówka.
#Sumy	Tylko wiersz sum. Jeśli wiersz sum nie istnieje, jest zwracana wartość null.
#Ten wiersz lub @ lub @[Nazwa kolumny]	Tylko komórki w tym samym wierszu, co formuła. Tych specyfikatorów nie można łączyć z żadnymi innymi specyfikatorami elementów specjalnych. Używaj tego specyfikatora, aby wymusić przecięcie pośrednie w zachowaniu odwołania lub aby zastąpić zachowanie przecięcia pośredniego i odwołać się do pojedynczych wartości w kolumnie. Program Excel automatycznie zamienia specyfikatory #Ten wiersz na krótszy specyfikator @ w tabelach zawierających więcej niż jeden wiersz danych. Jeśli jednak tabela zawiera tylko jeden wiersz, program Excel nie zastępuje specyfikatora wiersza #This, co może powodować nieoczekiwane wyniki obliczeń w przypadku dodania kolejnych wierszy. Aby uniknąć problemów z obliczeniami, wprowadź wiele wierszy w tabeli zanim wprowadzisz jakiekolwiek formuły odwołań strukturalnych.

Kwalifikowanie odwołań strukturalnych w kolumnach obliczeniowych

Podczas tworzenia kolumny obliczeniowej do utworzenia formuły często używa się odwołania strukturalnego. To odwołanie strukturalne może być niekwalifikowane lub w pełni kwalifikowane. Aby na przykład utworzyć kolumnę obliczeniową o nazwie Kwota prowizji, która oblicza kwotę prowizji w złotych, można użyć następujących formuł:

Typ odwołania strukturalnego	Przykład	Komentarz
Niekwalifikowane	=[Kwota sprzedaży]*[% prowizji]	Mnożenie odpowiednich wartości z bieżącego wiersza.
W pełni kwalifikowane	=SprzWDz[Kwota sprzedaży]*SprzWDz[% prowizji]	Mnożenie odpowiednich wartości dla każdego wiersza i obu kolumn.

Ogólna reguła, której należy przestrzegać, jest następująca: jeśli w tabeli są używane odwołania strukturalne, na przykład podczas tworzenia kolumny obliczeniowej, można używać niekwalifikowanego odwołania strukturalnego, ale jeśli odwołanie strukturalne jest używane poza tabelą, należy użyć w pełni kwalifikowanego odwołania strukturalnego.

Przykłady używania odwołań strukturalnych

Oto kilka sposobów używania odwołań strukturalnych.

Odwołanie strukturalne:	Odwołuje się do:	Co odpowiada zakresowi komórek:
=SprzWDz[[#Wszystko];[Kwota sprzedaży]]	Wszystkie komórki w kolumnie Kwota sprzedaży.	C1:C8
=SprzWDz[[#Nagłówki];[% prowizji]]	Nagłówek kolumny % prowizji.	D1
=SprzWDz[[#Sumy];[Region]]	Suma kolumny Region. Jeśli nie ma wiersza sum, jest zwracana wartość null.	B8
=SprzWDz[[#Wszystko];[Kwota sprzedaży]:[% prowizji]]	Wszystkie komórki w kolumnach Kwota sprzedaży i % prowizji.	C1:D8
=SprzWDz[[#Dane];[% prowizji]:[Kwota prowizji]]	Tylko dane w kolumnach % prowizji i Kwota prowizji.	D2:E7
=SprzWDz[[#Nagłówki];[Region]:[Kwota prowizji]]	Tylko nagłówki kolumn między kolumnami Region i Kwota prowizji.	B1:E1
=SprzWDz[[#Sumy];[Kwota sprzedaży]:[Kwota prowizji]]	Sumy kolumn od Kwota sprzedaży do Kwota prowizji. Jeśli nie ma wiersza sum, jest zwracana wartość null.	C8:E8
=SprzWDz[[#Nagłówki];[#Dane];[% prowizji]]	Tylko nagłówek i dane kolumny % prowizji.	D1:D7
=SprzWDz[[#Ten wiersz];[Kwota prowizji]] lub =SprzWDz[@Kwota prowizji]	Komórka na przecięciu bieżącego wiersza i kolumny Kwota prowizji. W przypadku użycia w tym samym wierszu, w którym znajduje się wiersz nagłówka lub wiersz sumy, zostanie zwrócony błąd #VALUE! . Jeśli wpiszesz to odwołanie strukturalne w dłuższej formie (#Ten wiersz) w tabeli z wieloma wierszami danych, program Excel automatycznie zamieni je na krótszą formę (@). Obydwa działają tak samo.	E5 (jeśli bieżącym wierszem jest wiersz 5)

Strategie dotyczące pracy z odwołaniami strukturalnymi

Podczas pracy z odwołaniami strukturalnymi należy uwzględnić poniższe zagadnienia.

• Korzystanie z funkcji Autouzupełnianie formuł Korzystanie z funkcji autouzupełniania formuł może się okazać bardzo przydatne podczas wprowadzania odwołań strukturalnych i w celu zapewnienia poprawności używanej składni. Aby uzyskać więcej informacji, zobacz Korzystanie z funkcji Autouzupełnianie formuł.

• Określanie, czy odwołania strukturalne tabel mają być generowane w półwyborach Domyślnie podczas tworzenia formuły zaznaczenie zakresu komórek w tabeli powoduje użycie półwyboru w komórkach i automatyczne wprowadzenie odwołania strukturalnego zamiast zakresu komórek w formule. Takie zachowanie półwyboru ułatwia wprowadzanie odwołań strukturalnych. Może ono być włączane i wyłączane przez zaznaczenie lub wyczyszczenie pola wyboru Użyj nazw tabel w formułach w oknie dialogowymOpcje>pliku>Formuły>Praca z formułami.

• Używanie skoroszytów zawierających linki zewnętrzne do tabel programu Excel w innych skoroszytach Jeśli skoroszyt zawiera link zewnętrzny do tabeli programu Excel w innym skoroszycie, ten połączony skoroszyt źródłowy musi być otwarty w programie Excel, aby uniknąć błędów #REF! w skoroszycie docelowym zawierającym linki. Jeśli najpierw zostanie otwarty skoroszyt docelowy i zostaną wyświetlone #REF! , to zostaną one rozwiązane po otwarciu skoroszytu źródłowego. Jeśli najpierw otworzysz źródłowy skoroszyt, błędy nie powinny zostać wyświetlone.

• Konwertowanie zakresu na tabelę i tabeli na zakres Podczas konwertowania tabeli na zakres wszystkie odwołania do komórek są zamieniane na równoważne im odwołania bezwzględne typu A1. Podczas konwertowania zakresu na tabelę żadne odwołania do komórek w tym zakresie nie są automatycznie zamieniane na równoważne im odwołania strukturalne.

• Wyłączanie nagłówków kolumn Możesz włączać i wyłączać nagłówki kolumn tabeli z poziomu wiersza nagłówka na karcie >Projekt tabeli. Wyłączenie nagłówków kolumn tabeli nie ma wpływu na odwołania strukturalne używające nazw kolumn i nadal można ich używać w formułach. Odwołania strukturalne odwołujące się bezpośrednio do nagłówków tabeli (np. =SprzWDz[[#Headers],[%prowizja]]) zwrócą #REF.

• Dodawanie oraz usuwanie kolumn i wierszy w tabeli Zakresy danych tabeli często ulegają zmianie, dlatego odwołania do komórek w przypadku odwołań strukturalnych są dostosowywane automatycznie. Jeśli na przykład w formule jest używana nazwa tabeli do zliczania wszystkich komórek danych w tabeli i zostanie dodany wiersz danych, odwołanie do komórki zostanie automatycznie dopasowane.

• Zmienianie nazwy tabeli lub kolumny Jeśli zostanie zmieniona nazwa tabeli lub kolumny, to we wszystkich używających tej nazwy odwołaniach strukturalnych w skoroszycie zostaną automatycznie wprowadzone zmiany.

• Przenoszenie, kopiowanie i wypełnianie odwołań strukturalnych Po skopiowaniu lub przeniesieniu formuły używającej odwołania strukturalnego wszystkie odwołania strukturalne pozostają bez zmian.

  Uwaga

  Skopiowanie odwołania strukturalnego i wypełnienie odwołania strukturalnego to nie to samo. Podczas kopiowania wszystkie odwołania strukturalne pozostają bez zmian, natomiast podczas wypełniania formuły w pełni kwalifikowane odwołania strukturalne dopasowują specyfikatory kolumn jak w przypadku serii, co podsumowano w poniższej tabeli.

Kierunek wypełniania:	Podczas napełniania naciskasz:	Efekt:
W górę lub w dół	Nic	Brak dopasowania specyfikatora kolumny.
W górę lub w dół	Ctrl	Specyfikatory kolumn są dostosowywane jak w przypadku serii.
W prawo lub w lewo	Brak	Specyfikatory kolumn są dostosowywane jak w przypadku serii.
W górę, w dół, w prawo lub w lewo	Shift	Wartości w bieżących komórkach nie są zastępowane, tylko przenoszone, a następnie są wstawiane specyfikatory kolumn.

Potrzebujesz dodatkowej pomocy?

Zawsze możesz zadać pytanie ekspertowi w społeczności technicznej programu Excel lub uzyskać pomoc techniczną w społecznościach.

Tematy pokrewne

Omówienie tabel programu Excel
Tworzenie i formatowanie tabel
Sumowanie danych w tabeli programu Excel
Formatowanie tabeli programu Excel
Zmienianie rozmiaru tabeli przez dodawanie lub usuwanie wierszy i kolumn
Filtrowanie danych w zakresie lub tabeli
Konwertowanie tabeli na zakres
Problemy ze zgodnością tabel programu Excel
Eksportowanie tabeli programu Excel do programu SharePoint
Omówienie formuł w programie Excel