Jak utworzyć odwołania strukturalne w programie Excel?
Odnośniki strukturalne rozpoczynają się od tabel programu Excel. Po utworzeniu tabel w programie Excel automatycznie tworzy dla Ciebie uporządkowane odniesienia.
Teraz spójrz na poniższy obrazek.
- Krok 1: Podałem link do komórki B3, zamiast wyświetlać link jako B2, jest on wyświetlany jako Tabela1 [@Sprzedaż]. Tutaj Table1 to nazwa tabeli, a @Sales to kolumna, do której się odnosimy. Do wszystkich komórek w tej kolumnie odnosi się nazwa tabeli, po której następuje nazwa nagłówka kolumny.
- Krok 2: Teraz zmienię nazwę tabeli na Data_Table i zmienię nagłówek kolumny na Kwota .
- Krok 3: Aby zmienić nazwę tabeli, umieść kursor wewnątrz tabeli> przejdź do Projekt> Nazwa tabeli.
- Krok 4: Wymień nazwę tabeli jako Data_Table.
- Krok 5: Teraz zmień, podaj odniesienie do komórki B3.
Zrozumieliśmy więc, że odwołanie strukturalne składa się z dwóch części: Nazwa tabeli i Nazwa kolumny.
Przykłady
Możesz pobrać szablon Excela odwołań strukturalnych tutaj - Szablon Excela odwołań strukturalnychPrzykład 1
Używając odwołań strukturalnych, możesz uczynić formułę dynamiczną. W przeciwieństwie do zwykłych odwołań do komórek, pozwala na to, aby formuła była aktywna w przypadku dodawania i usuwania w zakresie danych.
Pozwólcie, że zastosuję formułę SUMA zarówno dla tabeli normalnego zakresu, jak i tabeli programu Excel.
SUMA Wzór dla normalnego zakresu.
SUMA Formuła dla tabeli programu Excel.
Dodam kilka wierszy do danych tabeli normalnej i Excela. Dodałem 2 pozycje do danych, teraz zobacz różnicę.
Strukturalne odniesienie w tabeli programu Excel pokazuje zaktualizowaną wartość, ale normalny zakres danych nie pokazuje zaktualizowanych wartości, chyba że ręcznie wprowadzisz pewne zmiany we wzorze.
Przykład nr 2
Spójrzmy teraz na jeszcze jeden przykład. Mam nazwę produktu, ilość i informacje o cenie. Korzystając z tych informacji, potrzebuję określić wartość sprzedaży.
Aby uzyskać wartość sprzedaży, formuła jest następująca: Ilość * Cena . Zastosujmy tę formułę w tabeli.
Formuła mówi [@QTY] * [@PRICE]. Jest to bardziej zrozumiałe niż normalne odniesienie do B2 * C2. Nie otrzymamy nazwy tabeli, jeśli umieścimy formułę wewnątrz tabeli.
Problemy z odwołaniami strukturalnymi programu Excel
Podczas korzystania z odwołań strukturalnych napotykamy na kilka problemów, które wymieniono poniżej.
Problem nr 1
Strukturalne odwołania również mają swoje własne problemy. Wszyscy znamy stosowanie formuły programu Excel i kopiowanie lub przeciąganie jej do innych pozostałych komórek. To nie jest ten sam proces w przypadku odwołań strukturalnych, działa on nieco inaczej.
Teraz spójrz na poniższy przykład. Zastosowałem formułę SUMA w programie Excel dla normalnego zakresu.
Jeśli chcę zsumować cenę i wartość sprzedaży, po prostu skopiuję i wkleję lub przeciągnę bieżącą formułę do pozostałych dwóch komórek, a otrzymam SUMA wartości ceny i wartości sprzedaży.
Teraz zastosuj tę samą formułę dla tabeli programu Excel w kolumnie Ilość.
Teraz mamy sumę kolumny Qty. Podobnie jak w przypadku normalnego zakresu, formuła skopiuj bieżącą formułę i wklej ją do kolumny Cena, aby uzyskać łączną cenę.
O mój Boże!!! Nie pokazuje sumy w kolumnie Cena, ale nadal pokazuje tylko sumę w kolumnie Ilość. Dlatego nie możemy skopiować i wkleić tej formuły do sąsiedniej komórki ani żadnej innej komórki, aby odnieść się do względnej kolumny lub wiersza.
Przeciągnij formułę, aby zmienić odniesienie
Teraz, gdy znamy jego ograniczenia, nie możemy już wykonywać zadania kopiuj-wklej z odwołaniami strukturalnymi. Jak więc przezwyciężyć to ograniczenie?
Rozwiązanie jest bardzo proste, wystarczy przeciągnąć wzór zamiast kopiować. Wybierz komórkę z formułą i użyj uchwytu wypełniania i przeciągnij ją do pozostałych dwóch komórek, aby zmienić odwołanie do kolumny na Cena i Wartość sprzedaży.
Teraz zaktualizowaliśmy formuły, aby uzyskać odpowiednie sumy.
Problem nr 2
Widzieliśmy jeden problem z odniesieniami do struktury i również znaleźliśmy rozwiązanie, ale mamy tu jeszcze jeden problem, nie możemy wykonać wywołania jako odniesienia bezwzględnego, jeśli przeciągamy formułę do innych komórek.
Spójrzmy teraz na poniższy przykład. Mam tabelę sprzedaży z wieloma wpisami i chcę skonsolidować dane za pomocą funkcji SUMA.JEŻELI w programie Excel.
Teraz zastosuję funkcję SUMA.JEŻELI, aby uzyskać skonsolidowane wartości sprzedaży dla każdego produktu.
Zastosowałem formułę dla miesiąca stycznia, ponieważ jest to odwołanie strukturalne, którego nie możemy skopiować i wkleić do pozostałych dwóch kolumn, nie zmieni to odniesienia na luty i marzec, więc przeciągnę formułę.
O!! Nie otrzymałem żadnych wartości w kolumnie luty i marzec. Jaki byłby problem ??? Przyjrzyj się dokładnie formule.
Wyciągnęliśmy formułę z miesiąca stycznia. W funkcji SUMA.JEŻELI pierwszym argumentem jest Zakres kryteriów Tabela_Sprzedaży [Produkt], ponieważ przeciągnęliśmy formułę, zmieniła się ona na Tabela _Sprzedaż [Jan].
Jak więc sobie z tym radzimy? Musimy uczynić pierwszy argument, tj. Kolumnę Product jako bezwzględną, a pozostałe kolumny jako odniesienie względne. W przeciwieństwie do zwykłych odwołań nie mamy luksusu używania klawisza F4 do zmiany typu odniesienia.
Rozwiązanie polega na tym, że musimy zduplikować kolumnę odniesienia, jak pokazano na poniższym obrazku.
Teraz możemy przeciągnąć formułę do innych rozwiercając dwie kolumny. Zakres kryteriów będzie stały, a odniesienia do innych kolumn odpowiednio się zmienią.
Wskazówka dla profesjonalistów: aby uczynić WIERSZ absolutnym odniesieniem, musimy zrobić podwójny wpis WIERSZ, ale musimy umieścić symbol @ przed nazwą ROW.
= Sales_Table [@ [Product]: [Product]]
Jak wyłączyć strukturalne odniesienie w programie Excel?
Jeśli nie jesteś fanem odwołań strukturalnych, możesz je wyłączyć, wykonując poniższe czynności.
- Krok 1: Przejdź do PLIK> Opcje.
- Krok 2: Formuły> Odznacz Użyj nazw tabel w formułach.
Rzeczy do zapamiętania
- Aby utworzyć odwołanie bezwzględne w odwołaniu strukturalnym, musimy podwoić nazwę kolumny.
- Nie możemy skopiować formuły odwołania strukturalnego, zamiast tego musimy przeciągnąć formułę.
- Nie możemy dokładnie zobaczyć, do której komórki odnosimy się w odwołaniach strukturalnych.
- Jeśli nie interesują Cię odwołania strukturalne, możesz je wyłączyć.
