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

Dzięki odwołaniom strukturalnym jest możliwa łatwiejsza i bardziej intuicyjna praca z danymi tabel przy użyciu formuł odwołujących się do tabeli — zarówno do całej, jak i do jej części. Takie odwołania są szczególnie przydatne, ponieważ zakresy danych tabeli często ulegają zmianie, a odwołania do komórek w przypadku odwołań strukturalnych są dopasowywane automatycznie. Eliminuje to potrzebę ponownego pisania formuł podczas dodawania lub usuwania wierszy i kolumn w tabeli, a także podczas odświeżania danych zewnętrznych.

To odwołanie strukturalne jest łatwiejsze do zrozumienia: Niż to odwołanie do komórki:
=SUMA(SprzWDz[KwSprz]) =SUMA(C2:C7)
W tym artykule


Przykład tabeli Sprzedaż w dziale

W tym artykule wykorzystano przykład tabeli opartej na sprzedaży w dziale złożonym z sześciu pracowników, która zawiera informacje o najnowszych kwotach sprzedaży i prowizjach.


Tabela Sprzedaż w dziale

Przykładowa tabela Sprzedaż w dziale

Objaśnienie 1 Cała tabela (A1:E8)
Objaśnienie 2 Dane tabeli (A2:E7)
Objaśnienie 3 Kolumna i nagłówek kolumny (D1:D8)
Objaśnienie 4 Kolumna obliczeniowa (E1:E8)
Objaśnienie 5 Wiersz sum (A8:E8)

Początek strony Początek strony

Składniki odwołania strukturalnego

Aby efektywnie pracować z tabelami i odwołaniami strukturalnymi, należy zrozumieć tworzenie składni odwołań strukturalnych podczas wprowadzania formuł. W poniższym przykładzie formuły sumującej wszystkie kwoty sprzedaży i kwoty prowizji przedstawiono składniki odwołania strukturalnego:


Formuła zawierająca odwołania strukturalne

Objaśnienie 1 Nazwa tabeli jest nazwą znaczącą używaną do odwoływania się do właściwych danych tabeli (z wyjątkiem wiersza nagłówka i wiersza sum, jeśli istnieją).
Objaśnienie 2 Specyfikator kolumny jest tworzony na podstawie nagłówka kolumny i ujęty w nawiasy. Odwołuje się on do danych kolumny (z wyjątkiem nagłówka kolumny i sumy, jeśli istnieją).
Objaśnienie 3 Specyfikator elementu specjalnego pozwala odwołać się do specyficznej części tabeli, takiej jak wiersz sum.
Objaśnienie 4 Specyfikator tabeli stanowi zewnętrzną część odwołania strukturalnego ujętego w nawiasy kwadratowe występujące po nazwie tabeli.
Objaśnienie 5 Odwołanie strukturalne obejmuje cały ciąg, począwszy od nazwy tabeli i skończywszy na specyfikatorze tabeli.

Początek strony Początek strony

Nazwy tabel i specyfikatory kolumn

Za każdym razem, gdy jest wstawiana tabela, w programie Microsoft Office Excel jest tworzona domyślna nazwa tabeli (Tabela1, Tabela2 itd.) na poziomie lub w zakresie całego skoroszytu. Można łatwo zmienić nazwę i uczynić ją bardziej zrozumiałą. Aby na przykład zmienić nazwę Tabela1 na SprzWDz, można użyć okna dialogowego Edytowanie nazwy (na karcie Projektowanie w grupie Właściwości edytuj nazwę tabeli w polu Nazwa tabeli).

Nazwa tabeli dotyczy całego zakresu danych w tabeli z wyjątkiem wierszy nagłówka i sum. W sekcji Przykład tabeli Sprzedaż w dziale nazwa tabeli SprzWDz dotyczy zakresu komórek A2:E7.

Podobnie jak nazwy tabel, specyfikatory kolumn reprezentują odwołania do całej kolumny danych z wyjątkiem nagłówka i sumy kolumny. W sekcji Przykład tabeli Sprzedaż w dziale specyfikator kolumny [Region] dotyczy zakresu komórek B2:B7, a specyfikator kolumny [PctProw] dotyczy zakresu komórek D2:D7.

Początek strony Początek strony

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łanie do: Za pomocą: Co w przykładzie odpowiada zakresowi komórek:
=SprzWDz[[OsobSprz]:[Region]] Wszystkie komórki w dwóch lub kilku sąsiadujących kolumnach : (dwukropek) operator zakresu A2:B7
=SprzWDz[KwSprz];SprzWDz[KwProw] Złożenie dwóch lub kilku kolumn ; (średnik) operator składania C2:C7, E2:E7
=SprzWDz[[OsobSprz]:[KwSprz]] SprzWDz[[Region]:[PctProw]] Część wspólna dwóch lub kilku kolumn  (spacja) operator przecięcia B2:C7

Początek strony Początek strony

Specyfikatory elementów specjalnych

W celu zwiększenia wygody można używać elementów specjalnych, aby odwoływać się do różnych części tabeli, na przykład do wiersza sum. To ułatwia odwoływanie się do tych części tabeli w formułach. Poniżej przedstawiono specyfikatory elementów specjalnych, których można używać w odwołaniach strukturalnych:

Specyfikator elementu specjalnego: Odwołanie do: Co w przykładzie odpowiada zakresowi komórek:
=SprzWDz[#Wszystko] Cała tabela włącznie z nagłówkami kolumn, danymi i sumami (jeśli istnieją). A1:E8
=SprzWDz[#Dane] Tylko dane. A2:E7
=SprzWDz[#Nagłówki] Tylko wiersz nagłówka. A1:E1
=SprzWDz[#Sumy] Tylko wiersz sum. Jeśli wiersz sum nie istnieje, jest zwracana wartość null. A8:E8
=SprzWDz[#Ten wiersz] Tylko część kolumn w bieżącym wierszu. Specyfikatora #Ten wiersz nie można łączyć z żadnymi innymi specyfikatorami elementów specjalnych. Należy użyć 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. Aby poznać więcej przykładów, zobacz Przykłady używania odwołań strukturalnych. A5:E5 (Jeśli bieżącym wierszem jest wiersz 5)

Początek strony Początek strony

Kwalifikowanie odwołań strukturalnych w kolumnach obliczeniowych

Podczas tworzenia kolumny obliczeniowej zazwyczaj jest używane odwołanie strukturalne w celu utworzenia formuły. To odwołanie strukturalne może być niekwalifikowane lub w pełni kwalifikowane. Aby na przykład utworzyć kolumnę obliczeniową o nazwie KwProw, która oblicza kwotę prowizji w złotych, można użyć następujących formuł:

Typ odwołania strukturalnego Przykład Komentarz
Niekwalifikowane =[KwSprz]*[PctProw] Mnożenie odpowiednich wartości z bieżącego wiersza.
W pełni kwalifikowane =SprzWDz[KwSprz]*SprzWDz[PctProw] 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.

Początek strony Początek strony

Przykłady używania odwołań strukturalnych

Istnieje wiele sposobów używania elementów specjalnych oraz łączenia ich z nazwami tabel i odwołań do kolumn tak, jak to przedstawiono poniżej:

Odwołanie strukturalne: Odwołanie do: Co w przykładzie odpowiada zakresowi komórek:
=SprzWDz[[#Wszystko];[KwSprz]] Wszystkie komórki w kolumnie KwSprz. C1:C8
=SprzWDz[[#Nagłówki];[PctProw]] Nagłówek kolumny PctProw. C1
=SprzWDz[[#Sumy];[Region]] Suma kolumny Region. Jeśli nie ma wiersza sum, jest zwracana wartość null. B8
=SprzWDz[[#Wszystko];[KwSprz]:[PctProw]] Wszystkie komórki w kolumnach KwSprz PctProw. C1:D8
=SprzWDz[[#Dane];[PctProw]:[KwProw]] Tylko dane w kolumnach PctProw i KwProw. D2:E7
=SprzWDz[[#Nagłówki];[Region]:[KwProw]] Tylko nagłówki kolumn między kolumnami Region i KwProw. B1:E1
=SprzWDz[[#Sumy];[KwSprz]:[KwProw]] Sumy kolumn od KwSprz do KwProw. Jeśli nie ma wiersza sum, jest zwracana wartość null. C8:E8
=SprzWDz[[#Nagłówki];[#Dane];[PctProw]] Tylko nagłówek i dane kolumny PctProw. D1:D7
=SprzWDz[[#Ten wiersz]; [KwProw]] Komórka na przecięciu bieżącego wiersza i kolumny KwProw. E5 (Jeśli bieżącym wierszem jest wiersz 5)

Początek strony Początek strony

Praca z odwołaniami strukturalnymi

Podczas pracy z odwołaniami strukturalnymi należy rozważyć wymienione poniżej zagadnienia.

Korzystanie z funkcji autouzupełniania formuł    

Funkcja autouzupełniania formuł może okazać się bardzo przydatna podczas wprowadzania odwołań strukturalnych. Zapewnia ona także prawidłową składnię. Aby uzyskać więcej informacji, zobacz Korzystanie z funkcji autouzupełniania 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.

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 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 nazwy tabel i odwołania do kolumn.

Wyłączanie nagłówków kolumn    

Wyłączenie nagłówków kolumn tabeli (na karcie Projektowanie w grupie Opcje stylu tabeli należy wyczyścić pole wyboru Wiersz nagłówka) nie wpływa na odwołania strukturalne używające tych nagłówków i można ich w dalszym ciągu używać w formułach.

Dodawanie oraz usuwanie kolumn i wierszy w tabeli    

Zakresy danych tabeli często ulegają zmianie, więc odwołania do komórek w przypadku odwołań strukturalnych są dopasowywane automatycznie. Jeśli na przykład w formule jest używana nazwa tabeli do zliczania wszystkich komórek danych w tabeli Sprzedaż w dziale (na przykład =ILE.NIEPUSTYCH(SprzWDz) w Przykład tabeli Sprzedaż w dziale), jest zwracana wartość 30, ponieważ zakres danych to A2:E7. Jeśli zostanie dodany wiersz danych, odwołanie do komórki zostanie automatycznie dopasowane do zakresu A2:E8, a nowa wartość wyniesie 35.

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    

Wszystkie odwołania strukturalne pozostają bez mian po skopiowaniu lub przeniesieniu formuły używającej odwołania strukturalnego.

Podczas wypełniania formuły w pełni kwalifikowane odwołania strukturalne mogą dopasowywać specyfikatory kolumn jak w przypadku serii. Zostało to podsumowane w poniższej tabeli.

Kierunek wypełniania: Klawisz naciskany podczas wypełniania: Następnie:
W górę lub w dół Brak Brak dopasowania specyfikatora kolumny.
W górę lub w dół CTRL Specyfikator kolumny jest dostosowywany jak w przypadku serii.
W prawo lub w lewo Brak Specyfikator kolumny jest dostosowywany jak w przypadku serii.
W prawo lub w lewo CTRL Brak dopasowania specyfikatora kolumny.
W górę, w dół, w prawo lub w lewo SHIFT Wartości w bieżących komórkach nie są zastępowane, lecz przenoszone, a następnie są wstawiane specyfikatory kolumn.

Początek strony Początek strony

Reguły składni odwołań strukturalnych

Poniżej przedstawiono listę reguł składni obowiązujących podczas tworzenia i edytowania odwołań strukturalnych.

 Uwaga   Nazwy tabel podlegają tym samym regułom co nazwy zdefiniowane. Aby uzyskać więcej informacji, zobacz Używanie nazw do wyjaśniania formuł.

Używanie nawiasów w specyfikatorach    

Wszystkie specyfikatory tabel, kolumn i elementów specjalnych muszą zostać ujęte w nawiasy ([ ]). Specyfikator zawierający inne specyfikatory wymaga nawiasów zewnętrznych obejmujących nawiasy wewnętrzne innych specyfikatorów.

Przykład     =SprzWDz[[OsobSprz]:[Region]]

Nagłówki kolumn są ciągami tekstowymi    

Wszystkie nagłówki kolumn są ciągami tekstowymi, ale nie trzeba stosować cudzysłowu, jeśli są używane w odwołaniu strukturalnym. Jeśli nagłówek kolumny zawiera liczby lub daty, takie jak 2004 lub 1-1-2004, są one również ciągami tekstowymi. Ponieważ nagłówki kolumn są ciągami tekstowymi, nie można używać wyrażeń w nawiasach.

Przykład     =SprzWDzPodsRoczne[[2004]:[2002]]

Znaki specjalne w nagłówkach kolumn tabeli    

Jeśli nagłówek kolumny tabeli zawiera jeden z poniższych znaków specjalnych, cały nagłówek kolumny musi zostać ujęty w nawiasy. To oznacza, że są wymagane podwójne nawiasy w specyfikatorach kolumn zawierających następujące znaki specjalne: spacja, znak tabulacji, znak nowego wiersza, znak powrotu karetki, średnik (;), dwukropek (:), przecinek (,), lewy nawias ([), prawy nawias (]), znak krzyżyka (#), pojedynczy cudzysłów ('), podwójny cudzysłów ("), lewy nawias klamrowy ({), prawy nawias klamrowy (}), znak dolara ($), znak daszka (^), znak handlowego i (&), gwiazdka (*), znak plus (+), znak równości (=), znak minus (-), znak większości (>), znak mniejszości (<) oraz znak dzielenia (/).

Przykład     =SprzWDzPodsRoczne[[Kwota$całkowita]]

Jedynym wyjątkiem jest sytuacja, w której jedynym użytym znakiem specjalnym jest znak spacji.

Przykład    =SprzWDz[Kwota całkowita]

Znaki specjalne w nagłówkach kolumn wymagające użycia znaku anulowania    

Następujące znaki mają specjalne znaczenie i wymagają użycia znaku pojedynczy cudzysłów (') jako znaku anulowania: lewy nawias ([), prawy nawias (]), znak krzyżyka(#) oraz pojedynczy cudzysłów (').

Przykład     =SprzWDzPodsRoczne['#Elementów]

Używanie znaku spacji w celu poprawienia czytelności odwołania strukturalnego    

W następujący sposób można używać znaków spacji w celu poprawienia czytelności odwołania strukturalnego:

  • Jedna spacja po pierwszym lewym nawiasie ([) i jedna spacja poprzedzająca ostatni prawy nawias (]).
  • Jedna spacja po średniku.

Przykład     =SprzWDz[ [OsobSprz]:[Region] ]

Przykład     =SprzWDz[[#Nagłówki]; [#Dane]; [PctProw]]

Początek strony Początek strony

 
 
Dotyczy:
Excel 2007