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

Podczas tworzenia tabeli programu Excel do tabeli i każdej 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, kliknij 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, pustego arkusza programu Excel.

  2. Aby utworzyć tabelę, zaznacz komórkę A1 i naciśnij klawisze Ctrl+T.

  3. Upewnij się, że jest zaznaczone pole Moja tabela ma nagłówki i kliknij przycisk OK.

  4. W komórce E2 wpisz znak równości (=) i kliknij komórkę C2.

Na pasku formuły po znaku równości zostanie wyświetlone odwołanie strukturalne [@[Kwota sprzedaży]].

  1. Bezpośrednio po nawiasie zamykającym wpisz gwiazdkę (*) i kliknij komórkę D2.

Na pasku formuły po gwiazdce zostanie wyświetlone odwołanie strukturalne [@[% prowizji]].

  1. 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. Aby uzyskać więcej informacji na temat formuł i kolumn obliczeniowych, zobacz Używanie formuł w tabeli programu Excel.

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 w programie Excel kliknij 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 programu Excel, aby wyświetlić kartę Narzędzia tabel na wstążce.

  2. Na karcie Projektowanie w polu Nazwa tabeli wpisz wybraną nazwę 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 Przejdź do.

  • Nie używaj odwołań do komórek Nazwy nie mogą być takie same jak odwołania 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. Możesz wprowadzić nazwę bez spacji lub wprowadzić znaki podkreślenia (_) i kropki (.) jako separatory wyrazów. 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.

Reguły składni odwołań strukturalnych

Można także ręcznie wprowadzać i zmieniać odwołania strukturalne na pasku formuły. W tym celu pomocne będzie zrozumienie składni odwołania strukturalnego. 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.

  • Specyfikator kolumny:   [Kwota sprzedaży] i [Kwota prowizji] to specyfikatory kolumn, które mają takie same nazwy jak kolumny, 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:   [#Sumy] i [#Dane] to specyfikatory elementów specjalnych, które odwołują się do specyficznych części tabeli, takich jak wiersz sum.

  • 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[[#Sumy],[Kwota sprzedaży]] i SprzWDz[[#Dane],[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 (/)

  • Używaj znaku anulowania w przypadku pewnych 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 (')

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 #Ten wiersz, 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 brzmi: 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 samy wierszu, w którym znajduje się wiersz nagłówków lub wiersz sum, zostanie zwrócony błąd #ARG!.

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 będą generowane za pomocą półwyboru    Domyślnie podczas tworzenia formuły kliknięcie 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 sekcji Praca z formułami w kategorii Formuły w oknie dialogowym Opcje programu Excel.

  • 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, „źródłowy” skoroszyt, do którego prowadzi link, musi być otwarty w programie Excel w celu uniknięcia wystąpienia błędów #REF! w „docelowym” skoroszycie, który zawiera linki. Jeśli docelowy skoroszyt zostanie otwarty jako pierwszy i zostaną wyświetlone błędy #REF!, znikną one po otwarciu źródłowego skoroszytu. Jeśli najpierw zostanie otwarty źródłowy skoroszyt, błędy nie powinny wystąpić.

  • 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    Wyłączenie nagłówków kolumn tabeli (na karcie Projektowanie w grupie Opcje stylu tabeli wyczyść pole wyboru Wiersz nagłówka) nie wpływa na odwołania strukturalne używające nazw kolumn i można ich w dalszym ciągu używać w formułach. Skutkiem odwołań strukturalnych odnoszących się bezpośrednio do nagłówków tabel (np. =SprzWDz[[#Nagłówki];[% prowizji]]) będą błędy #ADR.

  • 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. Odpowiednie podsumowanie znajduje się w poniższej tabeli.

Kierunek wypełniania:

Klawisz naciskany podczas wypełniania:

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.

Zobacz też

Omówienie formuł w programie Excel

Rozwijaj swoje umiejętności
Poznaj szkolenia
Uzyskuj nowe funkcje w pierwszej kolejności
Dołącz do niejawnych testerów pakietu Office

Czy te informacje były pomocne?

Dziękujemy za opinię!

Dziękujemy za opinię! Wygląda na to, że połączenie Cię z jednym z naszych agentów pomocy technicznej pakietu Office może być pomocne.

×