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, a dla każdej 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.

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 utworzy kolumnę obliczeniową i skopiuje formułę w dół całej kolumny, dostosowując ją do 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 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 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żywanie v znaki alid 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.

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 c odwołania do komórki Nazwy nie mogą być taka sama, jak odwołanie do komórki, na przykład Z$ 100 lub W1K1.

  • Nie używaj s tempie do oddzielania wyrazów Nie można używać spacji w nazwie. Możesz użyć bez spacji lub wpisz znak podkreślenia (_) i kropki (.) 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.

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    Ale nie wymagają ofert, gdy są używane w odwołań strukturalnych. Liczb lub dat, takich jak 2014 lub 2014-1-1, również są traktowane jako ciągi tekstowe. Nie można używać wyrażeń z nagłówkami kolumn. 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łniania formuł    Może się okazać, że przy użyciu funkcji Autouzupełnianie formuł jest 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ł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.

  • 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    Możesz przekonwertować tabelę na zakres wszystkich odwołań do komórek zmienia na ich równoważne bezwzględne odwołania w stylu A1. Po przekonwertowaniu zakresu do tabeli programu Excel automatycznie nie powoduje zmiany dowolnego odwołań do komórek tego zakresu do ich równoważne odwołań strukturalnych.

  • 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    Wszystkie odwołania strukturalne zmieniają się po skopiowaniu lub przeniesieniu formuły używającej odwołania strukturalnego.

    Uwaga : Kopiowanie odwołań strukturalnych i wykonując wypełnienia odwołania strukturalnego nie są te same kroki. Po skopiowaniu, wszystkie odwołania strukturalne pozostają bez zmian, podczas gdy wypełnianie formułą, w pełni kwalifikowana odwołania strukturalne dostosować specyfikatorów kolumn, jak w przypadku serii, jak opisano 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.

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.

Zobacz też

Omówienie formuł w programie Excel

Udostępnij Facebook Facebook Twitter Twitter E-mail E-mail

Czy te informacje były pomocne?

Doskonale! Czy chcesz przesłać inne opinie?

Jak możemy to poprawić?

Dziękujemy za opinię!

×