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

Ważne : Ten artykuł został przetłumaczony maszynowo, zobacz zastrzeżenie. Angielskojęzyczną wersję tego artykułu można znaleźć tutaj .

Gdy Tworzenie tabeli programu Excel, program Excel przypisuje nazwę, do tabeli i do każdego nagłówka kolumny w tabeli. Po dodaniu formuły do tabeli programu Excel nazw mogą być wyświetlane automatycznie podczas wprowadzania formuły i wybrać odwołania do komórek w tabeli zamiast ręcznego wprowadzania ich. Oto przykład działanie 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.

Sprzedaż Osoby

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 w 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, a następnie naciśnij Klawisze Ctrl + T.

  3. Upewnij się, że jest zaznaczone pole Moja tabela ma nagłówki, a następnie 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]].

  5. Bezpośrednio po nawiasie zamykającym wpisz gwiazdkę (*), a następnie kliknij komórkę D2.

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

  6. Naciśnij klawisz Enter.

    Program Excel automatycznie utworzy kolumnę obliczeniową i skopiuje formułę w dół całej kolumny, dostosowując ją do 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 kliknij komórkę E2

  2. Na pasku formuły wprowadź = 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ę Narzędzia tabel > Projektowanie na Wstążce.

  2. Wpisz odpowiednią nazwę w polu Nazwa tabeli, a następnie 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  Zawsze zaczynają się od litery nazwy znak podkreślenia (_) lub ukośnik odwrotny (\). Używanie liter, cyfr, okresów i podkreślenia znaków pozostałą część nazwy. Nie można użyć "C"; "c", "R" lub "r", nazwy, ponieważ są one już wskazany jako skrót do zaznaczania kolumny lub wiersza dla aktywnej komórki podczas wpisywania ich w polu Nazwa lub Przejdź do .

  • Nie używaj odwołań do komórek  Nazwy nie mogą być taka sama, jak odwołanie do komórki, na przykład Z$ 100 lub W1K1.

  • Nie używaj spacji do oddzielania wyrazów  Nie można używać spacji w nazwie. Znak podkreślenia (_) i kropki (.) służy jako separatorów wyrazów. Na przykład Sprzwdz, pierwszy.Kwartał albo Podatek_od_sprzedaży.

  • 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żyj identyfikatora obiektu  Jeśli planujesz o różnych tabele i wykresy przestawne, jest dobrym pomysłem jest prefiks nazwy typu obiektu. Na przykład: tbl_Sales dla tabeli sprzedaży, pt_Sales sprzedaży tabeli przestawnej i chrt_Sales na wykresie sprzedaży lub ptchrt_Sales na wykresie przestawnym sprzedaży. Dzięki temu wszystkie nazwy w uporządkowana lista w Menedżer nazw.

Reguły składni odwołań strukturalnych

Można także wprowadzanie lub zmienianie odwołania strukturalne ręcznie w formule, ale w tym celu ułatwić zrozumienie składni odwołań strukturalnych. Przyjrzyjmy się w poniższym przykładzie formuła:

=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 nazwą tabeli niestandardowe. Odwołuje się do danych w tabeli, bez żadnych wierszy Nagłówek lub Suma. Można użyć domyślna nazwa tabeli, takich jak Tabela1, lub zmienić go, aby użyć niestandardowej nazwy.

  • Specyfikator kolumny:   [Kwota sprzedaży]oraz[Kwota prowizji] są specyfikatorów kolumn, korzystające z nazw kolumn reprezentują. Odwołują dane kolumny bez wiersza nagłówka lub Suma dowolnej kolumny. Zawsze umieszcza Specyfikatory w nawiasach, jak pokazano.

  • Specyfikator elementu:   [#Totals] i [#Data] 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 [[#Totals], [kwota sprzedaży]] i Sprzwdz [[#Data], [Kwota prowizji]] są 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:

  • Użyj Specyfikatory w nawiasach kwadratowych    Wszystkich tabel, kolumn i specyfikatory elementów specjalnych muszą być ujęte w dopasowywanie nawiasów kwadratowych ([]). Specyfikator, zawierającego inne specyfikatory wymaga zewnętrzne nawiasy pasujące do załączanie wewnętrzne dopasowywanie nawiasów 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 samym wierszu jako nagłówek lub wiersz sumy, zostaną zwrócone #VALUE! błędu.

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

  • Zdecyduj, czy do generowania odwołania strukturalne tabel w półokręgu zaznaczeń    Domyślnie podczas tworzenia formuły, klikając komórkę zakresu w tabeli wybiera półokręgu komórki i jest automatycznie wprowadzana odwołania strukturalnego zamiast zakresu komórek w formule. To zachowanie podczas zaznaczania półokręgu ułatwia podobnie do wprowadzania odwołań strukturalnych. Możesz wyłączyć to zachowanie lub wyłączyć przez zaznaczenie lub wyczyszczenie pola wyboru Użyj tabeli nazw w formułach w polu plik > Opcje > formuły > okno dialogowe Praca z formułami.

  • Używanie skoroszytów zawierających łącza zewnętrzne do tabel programu Excel w innych skoroszytach    Jeśli skoroszyt zawiera łącze zewnętrzne do tabeli programu Excel w innym skoroszycie, ten skoroszyt połączone źródło musi być otwarty w programie Excel, aby uniknąć #REF! błędy w skoroszycie docelowym, który zawiera łącza. Jeśli Otwórz docelowy skoroszyt i #REF! występują błędy będą rozwiązany następnie otwórz skoroszyt źródłowy. Jeśli najpierw otwórz źródłowy skoroszyt powinien zostać wyświetlony nie kodów 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 Włączanie i wyłączanie funkcji z poziomu karty Projekt tabeli, można przełączyć > Wiersz nagłówka. Jeśli wyłączysz nagłówki kolumn tabeli nie ma wpływu na odwołania strukturalne, które używają nazw kolumn i nadal można używać ich w formułach. Strukturalne odwołaniami, które odwołują się bezpośrednio do nagłówków tabeli (np. = Sprzwdz [[#Headers]; [% prowizji]]) spowoduje #REF.

  • Dodawanie lub usuwanie kolumn i wierszy w tabeli    Ponieważ zakresy danych tabeli często zmieniać, odwołań do komórek dla odwołania strukturalne są automatycznie dostosowywane. Na przykład jeśli używasz nazwy tabeli w formule do zliczania komórek danych w tabeli, a następnie dodaj wiersz danych, odwołanie do komórki jest automatycznie dostosowywany tak.

  • 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.

Potrzebujesz dodatkowej pomocy?

Zawsze możesz zadać pytanie ekspertowi w społeczności technicznej programu Excel, uzyskać pomoc techniczną w społeczności witryny Answers bądź zasugerować nową funkcję lub ulepszenie w witrynie UserVoice dotyczącej programu Excel.

Tematy pokrewne

Omówienie tabel programu Excel
klip wideo: tworzenie i formatowanie tabeli programu Excel
Sumowanie danych w tabeli programu Excel
Formatowanie tabeli programu Excel
Zmienianie rozmiaru tabeli przez dodanie lub usunięcie wierszy i kolumn
Filtrowanie danych w zakresie lub tabeli
przekonwertować tabelę na zakres
problemy ze zgodnością tabel programu Excel
Eksportowanie tabeli programu Excel w programie SharePoint
omówieniaformuły Program Excel

Uwaga : Zrzeczenie dotyczące tłumaczenia maszynowego: Ten artykuł został przetłumaczony przez system komputerowy bez interwencji człowieka. Firma Microsoft udostępnia te tłumaczenia maszynowe, aby ułatwić użytkownikom, którzy nie znają języka angielskiego, korzystanie z zawartości dotyczącej produktów, usług i technologii firmy Microsoft. Ponieważ ten artykuł został przetłumaczony maszynowo, może zawierać błędy w słownictwie, składniowe lub gramatyczne.

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.

×