Większość ludzi jest zaznajomiona z używaniem odwołań względnych w programie Excel^{(in Excel)} . Dzieje się tak, ponieważ odwołania do komórek w arkuszach kalkulacyjnych programu Excel^(Excel) domyślnie stosują metodę odwołań względnych.

Jednak zdarzają się sytuacje, w których odniesienia względne stają się irytują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 odniesienia się zmieniły, musisz zastosować odniesienia bezwzględne lub odniesienia mieszane (w tym odniesienia względne i bezwzględne).

W odwołaniu bezwzględnym zarówno odwołania do kolumn, jak i wierszy są „zablokowane”, więc żadne z nich nie ulega zmianie podczas kopiowania lub wypełniania z tej komórki.

W tym artykule przyjrzymy się, jak używać odwołań bezwzględnych, aby program Excel zachowywał się tak, jak chcesz, z danymi.

Jak działają odniesienia^(References) względne w programie Excel^(Excel)

Kiedy wprowadzasz wartości do arkusza kalkulacyjnego Excel , każda komórka ma przypisaną określoną literę i numer. 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. Zatem „odniesieniem” do tej komórki jest A2 .

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

=A2+1

Ta formuła wstawi wartość z A2 do formuły, obliczy ją, a następnie wyśle ​​wynik do komórki, w której znajduje się ta formuła.

Po naciśnięciu klawisza Enter^(Enter) zobaczysz wynik.

W przypadku odwołań względnych nie trzeba 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ę na 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ół.

Działa to w ten sam sposób, jeśli przeciągasz 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 w przypadku odwołań do kolumn, jak i komórek.

Jak działają odniesienia^(References) bezwzględne w programie Excel^(Excel)

Odwołania bezwzględne^(Absolute) w programie Excel umożliwiają odwoływanie się do tej samej komórki, zamiast pozwalać programowi Excel na automatyczne aktualizowanie^{(Excel automatically update)} odwołań do wierszy lub kolumn. Odwołanie „mieszane” ma miejsce, gdy blokujesz tylko wiersz lub kolumnę, a „odwołanie bezwzględne” oznacza zablokowanie obu.

Spójrzmy na kilka przykładów.

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

Aby to zrobić, wpisz 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 przeciągasz 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 oryginalnej formuły, odwołanie „B” zmieni się, a formuła nie będzie już odwoływać się do B2 zgodnie z zamierzeniami.

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

Prawidłowe używanie odwołań ^(References)bezwzględnych^(Absolute) w Excelu^(Excel)

Aby użyć poprawnego odwoływania się w tej formule, musisz dokładnie rozważyć, co chcesz zrobić.

W tym przypadku chcemy następujących zachowań podczas wypełniania po prawej stronie.

• Zawsze odwołuj się do wartości w komórce B2
• Zawsze odwołuj się do wartości w kolumnie A
• Przenieś odwołanie wiersza dla kolumny A do bieżącego wiersza formuły

Patrząc na te zachowania, wiesz już, co musisz „zablokować”, a czego nie. Zarówno „B”, jak i „2” muszą być zablokowane (niezmienione). Również kolumna A musi być zablokowana.

Twoja formuła w B3 musi wyglądać tak: =$B$2*$A3

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

Prawidłowe korzystanie z odwołań bezwzględnych może być trudne, dlatego ważne jest, aby dokładnie przemyśleć, w jaki sposób program Excel ma aktualizować formułę podczas wypełniania kolumn lub wierszy^{(columns or rows)} w dowolnym kierunku.

Przechodzenie przez typy referencyjne^{(Reference Types)} w programie Excel

Możesz przyspieszyć ten proces podczas wpisywania formuł^{(typing formulas)} z odwołaniami bezwzględnymi, naciskając klawisz F4 , co spowoduje, że odwołanie do komórki będzie bezwzględne.

Po naciśnięciu klawisza F4 kursor może znajdować się po dowolnej stronie odwołania do komórki (lub nawet w jego środku), a mimo to przekonwertuje to pojedyncze odwołanie na wartość bezwzględną.

Jeśli nie chcesz wartości bezwzględnej (na przykład mieszanej), naciskaj F4, aż odwołanie będzie wyglądać tak, jak chcesz.

Jeśli chcesz dodać odniesienia do innych komórek 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 swoje referencje, wszystko powinno działać zgodnie z oczekiwaniami.

How to Use Absolute References in Excel

Most people are fаmiliar with using relative referеnces in Excel. This is because cell references in Excel spreadsheets default to the relative reference method.

However, there are times when relative references get annoying. It will change cell references any time you copy cells or fill columns and rows. If you don’t want references to change, you’ll need to go with absolute references or mixed references (including relative and absolute references).

In an absolute reference, both the column and row references are “locked,” so neither of those change when you copy or fill from that cell.

This article will look at how you can use absolute references to make Excel behave the way you want with data.

How Relative References Work in Excel

When you enter values into an Excel spreadsheet, each cell has a specific letter and number assigned to it. This represents the column and row of that cell.

For example, the value of “1” in the spreadsheet below is in column A and row 2. So the “reference” to this cell is A2.

If you want to perform a calculation in the next cell based on this cell, by adding 1 to it, you will write the following formula:

=A2+1

This formula will insert the value from A2 into the formula, calculate it, and then output the result into the cell where this formula is.

When you press Enter, you’ll see the result.

With relative referencing, you don’t have to type this formula into every other cell. All you have to do is drag the corner of the cell with the original formula down as far as you’d like. 

In the next cell, the reference to A2 will become A3. In the cell below that, A3 will become A4. In other words, Excel knows that you want to add 1 to the previous cell, so Excel updates the number (the row reference) accordingly as you drag down.

This works in the same way if you drag the formula across columns. Instead of updating the number, Excel will update the latter part of the reference (the column) to always reference the cell above it.

The column to the right contains B2, to the right of that contains C2, and so on.

This is a simple example of how relative addressing works for both column and cell references.

How Absolute References Work in Excel

Absolute references in Excel let you reference the same cell, rather than letting Excel automatically update the row or column references for you. “Mixed” referencing is if you lock only the row or the column, and “absolute referencing” is when you lock both.

Let’s look at some examples.

Let’s say your spreadsheet has a “10” in a top row, and you want every row beneath it to multiply that number by the number in the cell to the left. 

To do this, you’d enter a formula that looks like this:

=B$2*A3

This locks the “2” reference so it won’t change the row reference if you drag the cell with this formula down to the cells below it. Since A3 remains “unlocked” both the row and column reference will still change automatically and always reference the cell to the left.

You will notice that this only works because you’re dragging down into cells in the same column. So you don’t have to lock column (B) by placing a dollar sign ($) in front of it.

The problem with this is that if you want to use the same formula to the right of the original formula, the “B” reference will change, and the formula will no longer reference B2 as intended.

Let’s take a look at how to use absolute references instead of mixed references to make filling in both directions work properly.

Correctly Using Absolute References in Excel

To use the correct referencing in this formula, you must consider precisely what you’re trying to do.

In this case, we want the following behaviors when filling to the right.

• Always reference the value in cell B2
• Always reference the value in column A
• Shift the reference of the row for column A to the current row of the formula

Looking at these behaviors, you now know what you need to “lock” and what you don’t. Both “B” and “2” need to be locked (unchanged). Also, column A needs to be locked.

So your formula in B3 needs to look like this: =$B$2*$A3

Now when you drag this same cell either down or up, the formula works as intended.

Correctly using absolute referencing can get tricky, so it’s essential to take the time to carefully consider how you want Excel to update the formula as you fill columns or rows in either direction.

Cycling Through Reference Types in Excel

You can speed along the process when you’re typing formulas with absolute references by pressing the F4 key, which will make the cell reference absolute.

The cursor can be on either side of the cell reference (or even in the middle of it) when you press F4, and it’ll still convert that single reference to absolute.

If you don’t want absolute (for example, mixed instead), keep tapping F4 until the reference looks the way you want.

If you want to add any kind of referencing to other cells in the formula, just place your cursor there and start cycling through F4 again.

Once you have configured your formula, just press Enter and begin filling your spreadsheet in any direction you like. If you set up your references right, everything should work just as expected.

Related posts

• Zachowaj odwołania do komórek podczas kopiowania formuły w programie Excel

• Jak usunąć puste linie w Excelu?

• Jak korzystać z funkcji Speak Cells programu Excel

• Jak wstawić arkusz programu Excel do dokumentu programu Word?

• Jak korzystać z analizy warunkowej programu Excel

• Wyśrodkuj dane arkusza roboczego w programie Excel do drukowania

• Użyj klawiatury, aby zmienić wysokość wiersza i szerokość kolumny w programie Excel

• Jak wstawić CSV lub TSV do arkusza programu Excel?

• Jak usunąć zduplikowane wiersze w programie Excel?

• Jak korzystać z funkcji PMT w programie Excel?

• Jak napisać formułę/oświadczenie IF w programie Excel?

• 4 sposoby użycia znacznika wyboru w programie Excel

• Jak przenosić kolumny w programie Excel

• Jak usunąć linie siatki w programie Excel

• Arkusze Google a Microsoft Excel – jakie są różnice?

• Jak używać instrukcji If i zagnieżdżonych instrukcji If w programie Excel?

• 5 sposobów na konwersję tekstu na liczby w programie Excel

• Szybko przeliteruj liczby w Wordzie i Excelu

• Jak alfabetycznie w programie Excel

• Zaawansowany przewodnik VBA dla MS Excel