Arkusz w programie Excel składa się z komórek. Do tych komórek można się odwoływać, określając wartość wiersza i wartość kolumny.
Na przykład A1 odwołuje się do pierwszego wiersza (określonego jako 1) i pierwszej kolumny (określonego jako A). Podobnie B3 byłby trzecim wierszem i drugą kolumną.
Siła programu Excel polega na tym, że możesz używać tych odwołań do komórek w innych komórkach podczas tworzenia formuł.
Teraz istnieją trzy rodzaje odwołań do komórek, których można używać w programie Excel:
- Względne odniesienia do komórek
- Bezwzględne odniesienia do komórek
- Mieszane odniesienia do komórek
Zrozumienie tych różnych typów odwołań do komórek pomoże Ci pracować z formułami i zaoszczędzić czas (zwłaszcza podczas kopiowania i wklejania formuł).
Co to są względne odwołania do komórek w programie Excel?
Pozwólcie, że wezmę prosty przykład, aby wyjaśnić koncepcję względnych odwołań do komórek w programie Excel.
Załóżmy, że mam zestaw danych pokazany poniżej:
Aby obliczyć sumę dla każdego artykułu, musimy pomnożyć cenę każdego artykułu przez ilość tego artykułu.
W przypadku pierwszego elementu formuła w komórce D2 będzie miała postać B2*C2 (jak pokazano poniżej):
Teraz zamiast wprowadzać formułę dla wszystkich komórek jedna po drugiej, możesz po prostu skopiować komórkę D2 i wkleić ją do wszystkich pozostałych komórek (D3:D8). Kiedy to zrobisz, zauważysz, że odwołanie do komórki automatycznie dostosowuje się do odpowiedniego wiersza. Na przykład formuła w komórce D3 to B3*C3, a formuła w D4 to B4*C4.
Te odwołania do komórek, które dostosowują się, gdy komórka jest kopiowana, są nazywane względne odwołania do komórek w programie Excel.
Kiedy używać względnych odwołań do komórek w programie Excel?
Względne odwołania do komórek są przydatne, gdy trzeba utworzyć formułę dla zakresu komórek, a formuła musi odwoływać się do względnego odwołania do komórki.
W takich przypadkach możesz utworzyć formułę dla jednej komórki i skopiować ją i wkleić do wszystkich komórek.
Co to są bezwzględne odwołania do komórek w programie Excel?
W przeciwieństwie do względnych odwołań do komórek, bezwzględne odwołania do komórek nie zmieniają się podczas kopiowania formuły do innych komórek.
Załóżmy na przykład, że masz zestaw danych pokazany poniżej, w którym musisz obliczyć prowizję za całkowitą sprzedaż każdego przedmiotu.
Prowizja wynosi 20% i jest wymieniona w komórce G1.
Aby uzyskać kwotę prowizji za każdą sprzedaż przedmiotu, użyj następującej formuły w komórce E2 i skopiuj dla wszystkich komórek:
=D2*$G$1
Zwróć uwagę, że w odwołaniu do komórki, która zawiera prowizję, znajdują się dwa znaki dolara ($). $g$2.
Co robi znak dolara ($)?
Symbol dolara, dodany przed numerem wiersza i kolumny, czyni go bezwzględnym (tj. zatrzymuje zmianę numeru wiersza i kolumny podczas kopiowania do innych komórek).
Na przykład w powyższym przypadku, gdy kopiuję formułę z komórki E2 do E3, zmienia się ona z =D2*$G$1 na =D3*$G$1.
Zauważ, że podczas gdy D2 zmienia się na D3, 1 $ G$ nie zmienia się.
Ponieważ dodaliśmy symbol dolara przed „G” i „1” w G1, nie pozwoliłoby to na zmianę odwołania do komórki podczas kopiowania.
To sprawia, że odwołanie do komórki jest absolutne.
Kiedy używać bezwzględnych odwołań do komórek w programie Excel?
Bezwzględne odwołania do komórek są przydatne, gdy nie chcesz, aby odwołanie do komórki zmieniało się podczas kopiowania formuł. Może tak być w przypadku, gdy masz stałą wartość, którą musisz użyć w formule (np. stawka podatku, stawka prowizji, liczba miesięcy itp.)
Chociaż możesz również na stałe zakodować tę wartość w formule (tj. Użyj 20% zamiast $ G $ 2), posiadanie jej w komórce, a następnie użycie odwołania do komórki pozwala zmienić ją w przyszłości.
Na przykład, jeśli zmieni się struktura prowizji i teraz płacisz 25% zamiast 20%, możesz po prostu zmienić wartość w komórce G2, a wszystkie formuły zostaną automatycznie zaktualizowane.
Co to są mieszane odwołania do komórek w programie Excel?
Mieszane odwołania do komórek są nieco bardziej skomplikowane niż bezwzględne i względne odwołania do komórek.
Mogą istnieć dwa typy mieszanych odwołań do komórek:
- Wiersz jest zablokowany, podczas gdy kolumna zmienia się podczas kopiowania formuły.
- Kolumna jest zablokowana, podczas gdy wiersz zmienia się podczas kopiowania formuły.
Zobaczmy, jak to działa na przykładzie.
Poniżej znajduje się zestaw danych, w którym należy obliczyć trzy poziomy prowizji na podstawie wartości procentowej w komórkach E2, F2 i G2.
Teraz możesz wykorzystać moc mieszanych referencji, aby obliczyć wszystkie te prowizje za pomocą tylko jednej formuły.
Wprowadź poniższą formułę w komórce E4 i skopiuj dla wszystkich komórek.
=$B4*$C4*E$2
Powyższa formuła wykorzystuje oba rodzaje mieszanych odwołań do komórek (jeden, w którym zablokowany jest wiersz, a drugi, w którym zablokowana jest kolumna).
Przeanalizujmy każde odwołanie do komórki i zrozummy, jak to działa:
- $B4 (i $C4) - W tym odwołaniu znak dolara znajduje się tuż przed notacją kolumny, ale nie przed numerem wiersza. Oznacza to, że po skopiowaniu formuły do komórek po prawej stronie odwołanie pozostanie takie samo, jak kolumna jest stała. Na przykład, jeśli skopiujesz formułę z E4 do F4, to odwołanie nie zmieni się. Jednak po skopiowaniu numer wiersza zmieni się, ponieważ nie jest zablokowany.
- 2€ - W tym odwołaniu znak dolara znajduje się tuż przed numerem wiersza, a notacja kolumny nie zawiera znaku dolara. Oznacza to, że po skopiowaniu formuły do komórek odwołanie nie zmieni się, ponieważ numer wiersza jest zablokowany. Jeśli jednak skopiujesz formułę w prawo, alfabet kolumn zmieni się, ponieważ nie jest zablokowany.
Jak zmienić odniesienie z względnego na bezwzględne (lub mieszane)?
Aby zmienić odwołanie z względnego na bezwzględne, musisz dodać znak dolara przed notacją kolumny i numerem wiersza.
Na przykład A1 jest względnym odwołaniem do komórki i stanie się bezwzględne, gdy zmienisz ją na $A$1.
Jeśli masz tylko kilka odniesień do zmiany, możesz łatwo zmienić te odniesienia ręcznie. Możesz więc przejść do paska formuły i edytować formułę (lub zaznaczyć komórkę, nacisnąć F2, a następnie ją zmienić).
Jednak szybszym sposobem na to jest użycie skrótu klawiaturowego - F4.
Po wybraniu odwołania do komórki (na pasku formuły lub w komórce w trybie edycji) i naciśnięciu klawisza F4 odwołanie się zmienia.
Załóżmy, że w komórce znajduje się odwołanie =A1.
Oto, co się dzieje, gdy wybierzesz odwołanie i naciśniesz klawisz F4.
- Naciśnij klawisz F4 raz: Odwołanie do komórki zmienia się z A1 na $A$1 (staje się „bezwzględne” z „względne”).
- Naciśnij klawisz F4 dwa razy: Odwołanie do komórki zmieni się z A1 na A$1 (zmienia się na odwołanie mieszane, gdy wiersz jest zablokowany).
- Naciśnij klawisz F4 trzy razy: Odwołanie do komórki zmieni się z A1 na $A1 (zmienia się na odwołanie mieszane, gdy kolumna jest zablokowana).
- Naciśnij klawisz F4 cztery razy: Odwołanie do komórki ponownie staje się A1.
