Używanie odwołań strukturalnych w tabelach 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 zaznacz komórkę E2

2. Na pasku formuły wprowadź formułę =C2*D2 i naciśnij 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ę Projekttabeli na wstążce.

2. Wpisz odpowiednią nazwę w polu Nazwa tabeli i naciśnij Enter.

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

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

• Używanie prawidłowych znaków  Nazwa zawsze zaczyna się od litery, znaku podkreślenia (_) lub ukośniku 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łanie do komórki, na przykład Z$100 lub R1C1.

• Nie oddzielaj wyrazów za pomocą spacji  W nazwie nie można używać spacji. Możesz użyć znaku 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.

• Używanie identyfikatora obiektu  Jeśli planujesz utworzenie kombinacji tabel, tabel przestawnych i wykresów, warto poprzedzić nazwy typem obiektu. Przykład: tbl_Sales dla tabeli sprzedaży, pt_Sales dla tabeli przestawnej sprzedaży i chrt_Sales dla wykresu sprzedaży lub ptchrt_Sales dla wykresu przestawnego sprzedaży. Dzięki temu wszystkie imiona i nazwiska zostaną wyświetlone na liście uporządkowanej w Menedżerze nazw.

Reguły składni odwołań strukturalnych

Odwołania strukturalne można również wprowadzać lub zmieniać ręcznie w formule, ale w tym celu ułatwi to zrozumienie składni odwołań strukturalnych. Przejdźmy do następującego przykładu 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 to niestandardowa nazwa 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 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 elementów:    [#Totals] i [#Data] to specyfikatory elementów specjalnych odwołujące 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 rozpoczynający się od nazwy tabeli i zakończony 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 przy (@)

• Ukośnik odwrotny (\)

• Wykrzyknik (!)

• Lewy nawias (()

• Prawy nawias ())

• Znak procentu (%)

• Znak zapytania (?)

• Backtick (')

• Średnik (;)

• Tylda (~)

• Podkreślenie (_)

• 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 (')

• Znak przy (@)

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.

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.

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ł:

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.

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 dla tabel w półwyborach mają być generowane    Domyślnie podczas tworzenia formuły zaznaczenie zakresu komórek w tabeli powoduje zaznaczenie komórek i automatyczne wprowadzenie odwołania strukturalnego zamiast zakresu komórek w formule. Takie zachowanie półwyboru ułatwia wprowadzanie odwołań strukturalnych. To zachowanie można włączyć lub wyłączyć, zaznaczając lub czyszcząc pole wyboru Użyj nazw tabel w formułach w oknie dialogowym Opcje > Plik > Formuły > Praca z formułami.

• Używanie skoroszytów z zewnętrznymi linkami 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 otworzysz skoroszyt docelowy i pojawią się błędy #REF! , zostaną one rozwiązane po otwarciu skoroszytu źródłowego. Jeśli najpierw otworzysz skoroszyt źródłowy, nie powinny być wyświetlane żadne kody błędów.

• 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    Nagłówki kolumn tabeli można włączać i wyłączać na karcie Projekt tabeli > Wiersz nagłówka. Wyłączenie nagłówków kolumn tabeli nie wpływa 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]]) spowodują #REF.

• Dodawanie oraz usuwanie kolumn i wierszy w tabeli    Zakresy danych tabeli często się zmieniają, dlatego odwołania do komórek 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: Kopiowanie odwołania strukturalnego i wypełnianie odwołania strukturalnego nie jest tym samym. Podczas kopiowania wszystkie odwołania strukturalne pozostają takie same, natomiast podczas wypełniania formuły w pełni kwalifikowane odwołania strukturalne dostosowują specyfikatory kolumn, takie jak seria, jak podsumowano w poniższej tabeli.

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 ExcelKlip wideo: tworzenie i formatowanie tabeli
programu ExcelSumowanie danych w tabeli
programu ExcelFormatowanie tabeli
programu ExcelZmienianie 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 ExcelEksportowanie tabeli programu Excel do programu SharePoint
Omówienie formuł w programie Excel