Co warto wiedzieć
- Aby obliczyć wiele elementów na podstawie komórek znajdujących się w innym miejscu i zachować stały wiersz lub kolumnę, użyj bezwzględnego odwołania do komórki.
- W tym równaniu bezwzględne odwołanie do komórki to A$12:=+B2+B2+A$12.
- Symbol $ "zatrzymuje" stałą wiersza lub kolumny, nawet podczas kopiowania lub wypełniania kolumny lub wiersza tą samą formułą.
W tym artykule wyjaśniono, jak używać bezwzględnego odwołania do komórki w programie Excel 2010 i nowszych wersjach, aby zapobiec automatycznemu dostosowywaniu się formuł do liczby wypełnionych wierszy lub kolumn.
Co to jest bezwzględne odwołanie do komórki w programie Excel?
Istnieją dwa sposoby odwoływania się do komórki w formule w programie Excel. Możesz użyć względnego odwołania do komórki lub bezwzględnego odwołania do komórki.
- Względne odniesienie do komórki: Adres komórki, który nie zawiera symbolu $ przed współrzędnymi wiersza lub kolumny. To odniesienie automatycznie zaktualizuje kolumnę lub komórkę względem oryginalnej komórki, gdy wypełnisz w dół lub w poprzek.
- Bezwzględne odwołanie do komórki: Adres komórki, który zawiera symbol $ przed współrzędnymi wiersza lub kolumny. To „utrzymuje” stały wiersz lub kolumnę odniesienia, nawet podczas wypełniania kolumny lub wiersza tą samą formułą.
Odwoływanie się do komórek może wydawać się abstrakcyjnym pojęciem dla tych, którzy są w nim nowi.
Jak używać bezwzględnego odwołania do komórki w programie Excel
Istnieje kilka różnych sposobów wykorzystania odwołania bezwzględnego w programie Excel. Użyta metoda zależy od tego, która część odwołania ma być stała: kolumna czy wiersz.
-
Na przykład weź arkusz kalkulacyjny, w którym chcesz obliczyć rzeczywisty podatek od sprzedaży dla wielu pozycji na podstawie komórek odniesienia w innym miejscu arkusza.
Image -
W pierwszej komórce należy wprowadzić formułę przy użyciu standardowych względnych odwołań do komórek dla cen towarów. Aby jednak uwzględnić w obliczeniach poprawny podatek od sprzedaży, użyjesz odwołania bezwzględnego dla wiersza, ale nie dla komórki.
Image -
Ta formuła odwołuje się do dwóch komórek na różne sposoby. B2 to komórka po lewej stronie pierwszej komórki podatku od sprzedaży w stosunku do pozycji tej komórki. Odwołanie A$12 wskazuje na rzeczywisty numer podatku od sprzedaży wymieniony w A12. Symbol $ zachowa stałe odniesienie do wiersza 12, niezależnie od tego, w którym kierunku wypełnisz sąsiednie komórki. Wypełnij wszystkie komórki pod tą pierwszą, aby zobaczyć to w akcji.
Image -
Widać, że po wypełnieniu kolumny komórka w drugiej kolumnie używa względnego odwołania do komórek dla wartości kosztów w kolumnie B, zwiększając wartość kolumny, aby dopasować ją do kolumny podatku. Jednak odniesienie do A12 pozostaje stałe dzięki symbolowi $, który utrzymuje to samo odwołanie do wiersza. Teraz wypełnij kolumnę D zaczynając od komórki C2.
Image -
Teraz widać, że wypełnienie wiersza po prawej stronie powoduje użycie prawidłowego stanowego podatku od sprzedaży (Indiana), ponieważ odwołanie do kolumny jest względne (przesuwa się o jeden w prawo, podobnie jak wypełniana komórka). Jednak względne odniesienie do kosztów przesunęło się teraz również w prawo, co jest błędne. Możesz to naprawić, czyniąc oryginalną formułę w C2 odwołującą się do kolumny B z odwołaniem bezwzględnym.
Image -
Umieszczając symbol $ przed literą „B”, stworzyłeś bezwzględne odniesienie do kolumny. Teraz, gdy wypełnisz po prawej stronie, zobaczysz, że odniesienie do kolumny B dla kosztów pozostaje takie samo.
Image -
Teraz, gdy wypełnisz kolumnę D w dół, zobaczysz, że wszystkie względne i bezwzględne odwołania do komórek działają dokładnie tak, jak zamierzałeś.
Image -
Bezwzględne odwołania do komórek w programie Excel mają kluczowe znaczenie dla kontrolowania, do których komórek odwołują się podczas wypełniania kolumn lub wierszy. Użyj symbolu $, jednak potrzebujesz w odwołaniu do komórki, aby zachować stałe odwołanie do wiersza lub kolumny, aby odwoływać się do poprawnych danych we właściwej komórce. Kiedy łączysz względne i bezwzględne odwołania do komórek w ten sposób, nazywa się to odwołaniem do komórek mieszanych.
Jeśli używasz odwołania mieszanego, ważne jest, aby wyrównać kolumnę lub wiersz danych źródłowych z kolumną lub wierszem, w którym wpisujesz formułę. Jeśli odwołanie do wiersza jest względne, pamiętaj, że podczas wypełniania z boku numer kolumny danych źródłowych będzie się zwiększał wraz z kolumną komórki z formułą. Jest to jedna z rzeczy, które sprawiają, że adresowanie względne i bezwzględne jest skomplikowane dla nowych użytkowników, ale gdy się go nauczysz, będziesz mieć znacznie większą kontrolę nad ściąganiem komórek danych źródłowych do odwołań do komórek formuły.
Korzystanie z bezwzględnych odwołań do komórek w celu przypięcia odwołania do jednej komórki
Innym podejściem do korzystania z bezwzględnego odwoływania się do komórek jest zastosowanie go zarówno do kolumny, jak i do wiersza, aby zasadniczo „przypiąć” formułę tak, aby używała tylko jednej komórki, bez względu na to, gdzie się znajduje.
Korzystając z tego podejścia, możesz wypełnić do boku lub w dół, a odwołanie do komórki zawsze pozostanie takie samo.
Używanie bezwzględnego odwołania do komórki zarówno w kolumnie, jak i wierszu jest skuteczne tylko wtedy, gdy odwołujesz się tylko do pojedynczej komórki we wszystkich wypełnianych komórkach.
-
Korzystając z tego samego przykładowego arkusza kalkulacyjnego powyżej, możesz odwoływać się tylko do stawki podatku jednego stanu, dodając symbol $ zarówno do odwołania do kolumny, jak i do wiersza.
Image -
Sprawia to, że zarówno kolumna „A”, jak i wiersz „12” pozostają stałe, bez względu na kierunek wypełniania komórek. Aby zobaczyć, jak to działa, wypełnij całą kolumnę podatku od sprzedaży ME po zaktualizowaniu formuły bezwzględnym odwołaniem do kolumny i wiersza. Widać, że każda wypełniona komórka zawsze używa odwołania bezwzględnego $A$12. Ani kolumna, ani wiersz się nie zmieniają.
Image -
Użyj tej samej formuły dla kolumny podatku od sprzedaży w stanie Indiana, ale tym razem użyj odwołania bezwzględnego zarówno dla kolumny, jak i wiersza. W tym przypadku jest to $B$12.
Image -
Wypełnij tę kolumnę, a ponownie zobaczysz, że odwołanie do B12 nie zmienia się w żadnej komórce, dzięki odwołaniu bezwzględnemu zarówno dla kolumny, jak i wiersza.
Image -
Jak widać, istnieje wiele sposobów wykorzystania bezwzględnych odwołań do komórek w programie Excel do wykonywania podobnych zadań. Odwołania bezwzględne zapewniają elastyczność w utrzymaniu stałych odwołań do komórek, nawet podczas wypełniania dużej liczby kolumn lub wierszy.
Możesz przechodzić między względnymi lub bezwzględnymi odwołaniami do komórek, podświetlając odwołanie, a następnie naciskając F4 Za każdym razem, gdy naciśniesz F4, odwołanie bezwzględne zostanie zastosowane do jednej z kolumn, wiersz, zarówno kolumnę, jak i komórkę, lub żaden z nich. Jest to prosty sposób modyfikowania formuły bez konieczności wpisywania symbolu $.
Kiedy należy używać bezwzględnych odwołań do komórek
W prawie każdej branży i dziedzinie istnieje wiele powodów, dla których warto używać bezwzględnych odwołań do komórek w programie Excel.
- Korzystanie ze stałych mnożników (takich jak cena za sztukę) na dużej liście przedmiotów.
- Zastosuj pojedynczą wartość procentową na każdy rok podczas prognozowania rocznych celów zysku.
- Podczas tworzenia faktur używaj odniesień bezwzględnych, aby odnieść się do tej samej stawki podatku dla wszystkich pozycji.
- Użyj bezwzględnych odwołań do komórek w zarządzaniu projektami, aby odnieść się do stałych stawek dostępności dla poszczególnych zasobów.
- Użyj względnych odwołań do kolumn i bezwzględnych odwołań do wierszy, aby dopasować obliczenia kolumn w komórkach, do których istnieją odwołania, do wartości kolumn w innej tabeli.
Jeśli używasz kombinacji odwołań względnych i bezwzględnych dla kolumn lub wierszy, musisz tylko upewnić się, że pozycja kolumn lub wierszy danych źródłowych jest zgodna z kolumną lub wierszem komórek docelowych (gdzie ponowne wpisanie formuły).
