Odwołanie bezwzględne trzyma komórkę na miejscu, gdy kopiujesz formułę. W =B2*$E$1 znaki dolara blokują E1: skopiuj formułę w dół, a każdy wiersz nadal mnoży przez E1, podczas gdy B2 przesuwa się do B3, B4 i dalej. Aby dodać znaki dolara, kliknij odwołanie w formule i naciśnij F4 (na Macu Cmd+T).
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Sales | Commission | Rate | 5% |
| 2 | Ana | $12,000 | $600 | ||
| 3 | Ben | $8,500 | $425 | ||
| 4 | Chen | $15,200 | $760 | ||
| 5 | Dina | $9,800 | $490 | ||
| 6 | Eli | $11,000 | $550 |
=B2*$E$1C2 została wpisana raz i skopiowana w dół. Kliknij C4: jej formuła to =B4*$E$1. Komórka sprzedaży przesunęła się do wiersza 4, a stawka została w E1. Zmień stawkę w E1 na 8%, a każda prowizja się zaktualizuje.
Odwołania względne a bezwzględne
| Odwołanie | Nazwa | Po skopiowaniu o jeden wiersz w dół i jedną kolumnę w prawo |
|---|---|---|
A1 | względne | B2 |
$A$1 | bezwzględne | $A$1 |
A$1 | mieszane: zablokowany wiersz | B$1 |
$A1 | mieszane: zablokowana kolumna | $A2 |
Zwykłe odwołanie, takie jak B2, jest względne: Excel zapisuje je jako „komórkę w tym położeniu względem mnie”, więc kopia o wiersz niżej wskazuje o wiersz niżej. Dokładnie tego chcesz dla danych w każdym wierszu i tak jest domyślnie. Znak $ przed literą kolumny albo numerem wiersza blokuje tę część.
Klasyczny błąd: kopiowanie w dół bez $
Oto znowu arkusz z prowizją, tym razem z =B2*E1 w C2 i bez znaków dolara. Pierwszy wiersz jest poprawny. Reszta to 0.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Sales | Commission | Rate | 5% |
| 2 | Ana | $12,000 | $600 | ||
| 3 | Ben | $8,500 | $0 | ||
| 4 | Chen | $15,200 | $0 | ||
| 5 | Dina | $9,800 | $0 | ||
| 6 | Eli | $11,000 | $0 |
=B3*E2C3 zawiera =B3*E2: odwołanie do stawki przesunęło się w dół do E2, które jest puste, a pusta komórka liczy się jako 0. Napraw to tutaj: kliknij C2, zmień formułę na =B2*$E$1 i naciśnij Enter. Cała kolumna się dostosuje, bo C3:C6 to kopie C2. Gdy stała komórka jest dzielnikiem, jak w =B2/B7 przy udziale w sumie, ten sam błąd pokazuje #DZIEL/0! (po angielsku #DIV/0!) zamiast 0 (typowy przypadek to procent z sumy).
Naciśnij F4, aby dodać znaki dolara
Podczas pisania albo edycji formuły ustaw kursor w odwołaniu (albo tuż za nim) i naciśnij F4. Każde naciśnięcie przechodzi do następnej postaci:
E1 -> $E$1 -> E$1 -> $E1 -> E1
Na wielu laptopach F4 steruje ekranem albo dźwiękiem, więc naciśnij Fn+F4. Na Macu użyj Cmd+T albo Fn+F4. Znaki $ możesz też wpisać samodzielnie.
Odwołania mieszane: zablokuj tylko wiersz albo kolumnę
Odwołanie mieszane ma jeden znak dolara. $A2 zawsze czyta kolumnę A, ale pozwala przesuwać się wierszowi; B$1 zawsze czyta wiersz 1, ale pozwala przesuwać się kolumnie. Gdy oba są w jednej formule, ta jedna formuła skopiowana po siatce tworzy tabliczkę mnożenia:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | x | 1 | 2 | 3 | 4 | 5 |
| 2 | 1 | 1 | 2 | 3 | 4 | 5 |
| 3 | 2 | 2 | 4 | 6 | 8 | 10 |
| 4 | 3 | 3 | 6 | 9 | 12 | 15 |
| 5 | 4 | 4 | 8 | 12 | 16 | 20 |
| 6 | 5 | 5 | 10 | 15 | 20 | 25 |
=$A2*B$1B2 zawiera =$A2*B$1. Kliknij F6: zawiera =$A6*F$1, czyli numer wiersza z kolumny A razy numer kolumny z wiersza 1, więc pokazuje 25. Usuń jeden znak dolara w B2, a tabela się rozpadnie, bo kopie zaczną mnożyć sąsiednie komórki zamiast nagłówków.
Ten sam schemat liczy ceny listy przy kilku rabatach: =$A2*(1-B$1) z cenami w kolumnie A i stawkami rabatu w wierszu 1.
Suma narastająca z zakresem zablokowanym z jednej strony
Zakres można zablokować tylko z jednego końca. =SUMA($B$2:B2) zawsze zaczyna się od B2, a jego koniec przesuwa się w dół przy kopiowaniu formuły, więc każdy wiersz dodaje wszystko aż do siebie. Formuły w tabeli możesz wpisywać także po polsku, ze średnikami.
| A | B | C | |
|---|---|---|---|
| 1 | Month | Sales | Total so far |
| 2 | Jan | 420 | 420 |
| 3 | Feb | 380 | 800 |
| 4 | Mar | 510 | 1310 |
| 5 | Apr | 450 | 1760 |
| 6 | May | 470 | 2230 |
=SUMA($B$2:B2)C6 zawiera =SUM($B$2:B6) i pokazuje 2230, sumę wszystkich pięciu miesięcy. Ten sam zakres zablokowany z jednej strony sprawia, że =LICZ.JEŻELI($A$2:A2;A2) liczy, ile razy wartość pojawiła się do tej pory; tak oznacza się duplikaty po pierwszym wystąpieniu.
Ćwiczenie: jedna formuła dla całej tabeli
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Price | 10% | 20% | 30% |
| 2 | $40.00 | |||
| 3 | $25.00 | |||
| 4 | $60.00 | |||
| 5 | $18.00 |
Twoja kolej: W B2 napisz cenę pierwszego produktu po rabacie z B1. Użyj $ tak, aby ta sama formuła skopiowana w prawo i w dół do D5 dała każdą cenę w tabeli.
Arkusz kopiuje twoją formułę do każdej komórki B2:D5, tak jak zrobiłby to uchwyt wypełniania, a Sprawdź odczytuje wszystkie dwanaście wyników. Bez właściwych znaków dolara kopie w wierszu 3 albo w kolumnie C odczytają złą cenę albo zły rabat.
Odwołania bezwzględne do innego arkusza albo tabeli wyszukiwania
Znaki dolara działają tak samo z nazwą arkusza: =B2*Settings!$B$1. Najbardziej liczą się przy wyszukiwaniu, gdzie tabela musi zostać na miejscu, a szukana wartość się przesuwa: =WYSZUKAJ.PIONOWO(A2;$E$2:$F$10;2;FAŁSZ) skopiowane w dół wciąż przeszukuje E2:F10, a =WYSZUKAJ.PIONOWO(A2;E2:F10;2;FAŁSZ) przesuwa tabelę o wiersz przy każdej kopii i zaczyna gubić pierwsze wiersze (WYSZUKAJ.PIONOWO). Jeśli stała komórka jest używana w wielu formułach, możesz też nadać jej nazwę przez Formuły > Definiuj nazwę i pisać =B2*Rate; tak zdefiniowana nazwa wskazuje tę samą komórkę z każdej formuły, jak $E$1.
Najczęściej zadawane pytania
Co oznacza znak $ w formule Excela?
Blokuje tę część odwołania, która stoi za nim. W $E$1 zablokowane są i kolumna E, i wiersz 1, więc odwołanie zostaje E1, dokądkolwiek skopiujesz formułę. E$1 blokuje tylko wiersz, a $E1 tylko kolumnę.
Jaki jest skrót do odwołania bezwzględnego w Excelu?
Kliknij w odwołanie podczas edycji formuły i naciśnij F4 (na wielu laptopach Fn+F4). Każde naciśnięcie przechodzi kolejno przez $A$1, A$1, $A1 i A1. Na Macu naciśnij Cmd+T albo Fn+F4.
Czym różni się odwołanie względne od bezwzględnego?
Odwołanie względne, takie jak B2, przesuwa się przy kopiowaniu formuły: wiersz niżej zmienia się w B3. Odwołanie bezwzględne, takie jak $B$2, zostaje $B$2. Używaj odwołań bezwzględnych dla jednej komórki, której potrzebuje każdy wiersz, na przykład stawki albo sumy.
Co to jest odwołanie mieszane w Excelu?
Odwołanie z jednym znakiem dolara: $A2 trzyma kolumnę i pozwala przesuwać się wierszowi, B$1 trzyma wiersz i pozwala przesuwać się kolumnie. =$A2*B$1 skopiowane w prawo i w dół po siatce tworzy tabliczkę mnożenia.
Dlaczego po przeciągnięciu formuły w dół widzę 0 albo #DZIEL/0!?
Odwołanie, które miało zostać na miejscu, przesunęło się razem z kopią. Jeśli wiersz 2 ma =B2/B7, wiersz 3 dostaje =B3/B8, a B8 jest puste. Zablokuj sumę formułą =B2/$B$7 i skopiuj jeszcze raz.
