Większość osób jest zaznajomiona z używaniem odniesień względnych w Excelu. Dzieje się tak, ponieważ odwołania do komórek w arkuszach kalkulacyjnych Excela są domyślnie oparte na metodzie odniesienia względnego.
Jednak zdarza się, że odniesienia względne stają się denerwujące. Zmieni odwołania do komórek za każdym razem, gdy kopiujesz komórki lub wypełniasz kolumny i wiersze. Jeśli nie chcesz, aby odwołania się zmieniały, musisz zastosować odwołania bezwzględne lub odwołania mieszane (w tym odwołania względne i bezwzględne).
W odwołaniu bezwzględnym zarówno odwołania do kolumn, jak i wierszy to „ zablokowane”, więc żadne z tych nie zmienia się podczas kopiowania lub wypełniania z tej komórki.
W tym artykule przyjrzymy się, jak można użyć odwołań bezwzględnych, aby program Excel zachowywał się tak, jak ty chcesz z danymi.
Jak działają odniesienia względne w programie Excel
Gdy wprowadzasz wartości do arkusza kalkulacyjnego programu Excel, każda komórka ma przypisaną do niej określoną literę i cyfrę. To reprezentuje kolumnę i wiersz tej komórki.
Na przykład wartość „1” w poniższym arkuszu kalkulacyjnym znajduje się w kolumnie A i wierszu 2. Tak więc „odniesieniem” do tej komórki jest A2.
Jeśli chcesz wykonać obliczenia w następnej komórce na podstawie w tej komórce, dodając do niej 1, napiszesz następującą formułę:
=A2+1
Ta formuła wstawi wartość z A2 do formułę, oblicz ją, a następnie wyślij wynik do komórki, w której znajduje się ta formuła.
Gdy naciśniesz Enter, zobaczysz wynik.
W przypadku odwołań względnych nie musisz wpisywać tej formuły do każdej innej komórki. Wszystko, co musisz zrobić, to przeciągnąć róg komórki z oryginalną formułą w dół tak daleko, jak chcesz.
W następnej komórce odwołanie do A2 zmieni się w A3. W komórce poniżej A3 stanie się A4. Innymi słowy, Excel wie, że chcesz dodać 1 do poprzedniej komórki, więc Excel odpowiednio aktualizuje liczbę (odwołanie do wiersza) podczas przeciągania w dół.
To działa w ten sam sposób, jeśli przeciągniesz formułę między kolumnami. Zamiast aktualizować numer, Excel zaktualizuje drugą część odwołania (kolumnę), aby zawsze odwoływać się do komórki nad nią.
Kolumna po prawej zawiera B2, po prawej stronie zawiera C2 i tak dalej.
Jest to prosty przykład działania adresowania względnego zarówno dla odwołań do kolumn, jak i komórek.
Jak działają odwołania bezwzględne w programie Excel
Odwołania bezwzględne w programie Excel pozwalają odwoływać się do tej samej komórki, zamiast zezwalać na Excel automatycznie aktualizuje odwołania do wierszy lub kolumn. Odwoływanie się „mieszane” ma miejsce, gdy blokujesz tylko wiersz lub kolumnę, a „odwoływanie bezwzględne” występuje, 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ć, wprowadź formułę, która wygląda tak:
=B$2*A3
Spowoduje to zablokowanie odwołania „2”, więc nie zmieni odwołania do wiersza, jeśli przeciągniesz komórkę z tą formułą w dół do komórek poniżej. Ponieważ A3 pozostaje „odblokowany”, zarówno odwołanie do wiersza, jak i do kolumny nadal będzie się zmieniać automatycznie i zawsze będzie odnosić się do komórki po lewej stronie.
Zauważysz, że działa to tylko dlatego, że ponowne przeciągnięcie w dół do komórek w tej samej kolumnie. Nie musisz więc blokować kolumny (B), umieszczając przed nią znak dolara ($).
Problem polega na tym, że jeśli chcesz użyć tej samej formuły po prawej stronie oryginalna formuła, odwołanie „B” zmieni się, a formuła nie będzie już odwoływać się do B2 zgodnie z przeznaczeniem.
Przyjrzyjmy się, jak używać odwołań bezwzględnych zamiast mieszane odniesienia, aby wypełnianie w obu kierunkach działało poprawnie.
Właściwe używanie odwołań bezwzględnych w programie Excel
Aby użyć prawidłowego odniesienia w tej formule, musisz dokładnie rozważyć, co próbujesz zrobić.
W tym przypadku chcemy następujących zachowań podczas wypełniania po prawej stronie.
Patrząc na te zachowania, wiesz już, czego potrzebujesz do „zablokowania” i tego, czego nie robisz. Zarówno „B”, jak i „2” muszą być zablokowane (niezmienione). Ponadto kolumna A musi być zablokowana.
Więc formuła w B3 musi wyglądać tak: =$B$2*$A3
Teraz, kiedy przeciągasz tę samą komórkę w dół lub w górę, formuła działa zgodnie z przeznaczeniem.
Prawidłowe użycie odwołań bezwzględnych może być trudne, dlatego ważne jest, aby poświęcić trochę czasu na dokładne rozważenie, w jaki sposób chcesz, aby program Excel aktualizował formułę podczas wypełniania kolumny lub wiersze w dowolnym kierunku.
Przechodzenie przez typy odwołań w programie Excel
Możesz przyspieszyć proces, gdy 'są formuły pisania z odwołaniami bezwzględnymi, naciskając klawisz F4, co spowoduje, że odwołanie do komórki będzie bezwzględne.
kursor może znajdować się po dowolnej stronie odwołania do komórki (lub nawet w jego środku), gdy naciśniesz F4, i nadal przekonwertuje to pojedyncze odwołanie na bezwzględne.
Jeśli nie chcesz bezwzględne (na przykład zamiast tego mieszany), naciskaj klawisz F4, aż odwołanie będzie wyglądać tak, jak chcesz.
Jeśli chcesz dodać jakikolwiek rodzaj odniesienia do innych c w formule, po prostu umieść tam kursor i zacznij ponownie przechodzić przez klawisz F4.
Po skonfigurowaniu formuły po prostu naciśnij Enter i zacznij wypełniać arkusz kalkulacyjny w dowolnym kierunku. Jeśli prawidłowo skonfigurujesz referencje, wszystko powinno działać zgodnie z oczekiwaniami.