Jak używać bezwzględnych odniesień w programie Excel

Większość ludzi zna stosowanie względnych referencji w programie Excel. Wynika to z faktu, że odniesienia do komórek w arkuszach kalkulacyjnych Excel domyślnie do względnej metody odniesienia.

Są jednak chwile, w których referencje względne stają się denerwujące. Zmieni referencje komórkowe w dowolnym momencie kopiowania komórek lub wypełniania kolumn i wierszy. Jeśli nie chcesz, aby referencje się zmieniły, musisz przejść do bezwzględnych odniesień lub odniesień mieszanych (w tym referencje względne i bezwzględne).

W bezwzględnym odniesieniu zarówno odniesienia do kolumny, jak i wiersz są „zablokowane”, więc żadne z tych nie zmienia się podczas kopiowania ani wypełniania z tej komórki.

Jeśli chcesz wykonać obliczenia w następnej komórce na podstawie tej komórki, dodając do niej 1, napiszesz następujący formułę:

= A2+1

Ta formuła wstawi wartość z A2 do wzoru, obliczy ją, a następnie wyprowadzi wynik do komórki, w której jest ten wzór.

Po naciśnięciu Wchodzić, zobaczysz wynik.

Z względnym odwołaniem nie musisz pisać tej formuły do ​​każdej innej komórki. Wszystko, co musisz zrobić, to przeciągnąć narożnik komórki za pomocą oryginalnej formuły w dół, o ile chcesz.

W następnej komórce odniesienie do A2 stanie się A3. W poniższej komórce A3 stanie się A4. Innymi słowy, Excel wie, że chcesz dodać 1 do poprzedniej komórki, więc Excel aktualizuje numer (odniesienie do wiersza) odpowiednio po ciągnięciu w dół.

Działa to w ten sam sposób, jeśli przeciągniesz formułę na kolumny. Zamiast aktualizować liczbę, Excel zaktualizuje ostatnią część odniesienia (kolumna), aby zawsze odwołać się do komórki nad nim.

Kolumna po prawej zawiera B2, po prawej stronie, zawiera C2 i tak dalej.

Jest to prosty przykład działania względnego adresowania zarówno dla referencji kolumnowych, jak i komórkowych.

Jak działają absolutne odniesienia w programie Excel

Absolutne odniesienia w programie Excel pozwalają odwołać się do tej samej komórki, a nie pozwala na automatycznie aktualizowanie referencji wierszy lub kolumnowych dla Ciebie. Odwołuje się „mieszane”, jeśli zablokujesz tylko wiersz lub kolumnę, a „Absolute Reference” ma miejsce, gdy blokujesz oba.

Spójrzmy na kilka przykładów.

Powiedzmy, że twój arkusz kalkulacyjny ma „10” w górnym rzędzie i chcesz, aby każdy wiersz pod nim pomnożył tę liczbę przez liczbę w komórce po lewej stronie.

Aby to zrobić, wprowadzisz formułę, która wygląda tak:

= B 2 $*A3

To blokuje odniesienie „2”, aby nie zmieniło odniesienia do wiersza, jeśli przeciągniesz komórkę tym wzorem do komórek poniżej. Ponieważ A3 pozostaje „odblokowany”, zarówno odniesienie do wiersza, jak i kolumny będzie się nadal zmieniać automatycznie i zawsze odwołuje się do komórki w lewo.

Zauważysz, że to działa tylko dlatego, że ciągniesz się w komórki w tej samej kolumnie. Więc nie musisz blokować kolumny (b), umieszczając przed nim znak dolara ($).

Problem polega na tym, że jeśli chcesz użyć tej samej formuły po prawej stronie oryginalnej formuły, odniesienie „B” zmieni się, a formuła nie będzie już odwoływać się do B2 zgodnie z zamierzeniami.

Przyjrzyjmy się, jak używać bezwzględnych odniesień zamiast mieszanych referencji, aby wypełnienie w obu kierunkach działało poprawnie.

Prawidłowo używając bezwzględnych odniesień w programie Excel

Aby użyć poprawnego odniesienia w tej formule, musisz dokładnie rozważyć to, co próbujesz zrobić.

W takim przypadku chcemy następujących zachowań podczas wypełniania w prawo.

• Zawsze odwołuj się do wartości w komórce B2
• Zawsze odwołać się do wartości w kolumnie A
• Przesuń odniesienie wiersza dla kolumny A do bieżącego wiersza formuły

Patrząc na te zachowania, teraz wiesz, czego potrzebujesz, aby „zablokować”, a czego nie. Zarówno „B”, jak i „2” muszą być zablokowane (niezmienione). Kolumna A należy również zablokować.

Więc twoja formuła w B3 musi wyglądać tak: = $ B $ 2*$ a3

Teraz, gdy ciągniesz tę samą komórkę w dół lub w górę, formuła działa zgodnie z przeznaczeniem.

Prawidłowe użycie bezwzględnego odniesienia może być trudne, dlatego konieczne jest poświęcenie czasu, aby dokładnie zastanowić się, w jaki sposób Excel aktualizował formułę, wypełniając kolumny lub wiersze w dowolnym kierunku.

Cyklowanie przez typy referencyjne w programie Excel

Możesz przyspieszyć proces podczas pisania formuł z bezwzględnymi odniesieniami, naciskając F4 Klucz, który uczyni odniesienie do komórki absolutną.

Kursor może znajdować się po obu stronach odniesienia komórki (lub nawet na środku) po naciśnięciu F4, i nadal będzie konwertować to pojedyncze odniesienie do bezwzględnego.

Jeśli nie chcesz absolutnego (na przykład, zamiast tego zmieszany), stuknij F4, aż odniesienie będzie wyglądało tak, jak chcesz.

Jeśli chcesz dodać dowolne odwołanie do innych komórek w formule, po prostu umieść tam kursor i ponownie zacznij jeździć na rowerze przez F4.

Po skonfigurowaniu formuły po prostu naciśnij Enter i zacznij wypełniać arkusz kalkulacyjny w dowolnym kierunku. Jeśli dobrze skonfigurujesz swoje referencje, wszystko powinno działać zgodnie z oczekiwaniami.