Jak utworzyć odwołania strukturalne w programie Excel?
Odniesienia strukturalne zaczynają się od tabel programu Excel. Gdy tylko tabele zostaną utworzone 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 pokazywać link jako B2, wyświetla się 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
Przykład 1
Używając odwołań strukturalnych, możesz uczynić formułę dynamiczną. W przeciwieństwie do zwykłych odwołań do komórek umożliwia 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.
Pozwólcie, że dodam kilka wierszy do danych zarówno tabel normalnych, jak i Excel. Dodałem 2 pozycje do danych, teraz zobacz różnicę.
Strukturalne odwołanie 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, muszę określić wartość sprzedaży.
Aby uzyskać wartość sprzedaży, formuła jest następująca: Ilość * Cena . Zastosujmy tę formułę do 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
Korzystając z odwołań strukturalnych, napotykamy pewne problemy, 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 trochę 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, co da mi 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; raczej 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 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. Musimy po prostu przeciągnąć formułę zamiast kopiować. Wybierz komórkę formuły 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 tutaj 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, nie możemy skopiować i wkleić formuły do pozostałych dwóch kolumn. Nie zmieni to odniesienia do lutego i marca, 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ł się na Sales _Table (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 odniesień 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ą WIERSZA.
= Tabela_sprzedaży (@ (Produkt) :( Produkt))
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ć wzoru strukturalnego odniesienia; 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ć.
