W przypadku odwoływania się do komórki w formule w programie Excel domyślnym typem odwołania jest odwołanie względne. Odwołania te zmienią się, gdy formuła zostanie skopiowana do innych komórek na podstawie ich względnej kolumny i wiersza. Jeśli chcesz zachować stałą odniesienia, niezależnie od tego, gdzie kopiowana jest formuła, musisz użyć odniesienia bezwzględnego.

• Co to jest absolutne odniesienie
• Jak tworzyć absolutne referencje
• Użyj odniesienia bezwzględnego z przykładami
• Oblicz procent całości
• Poszukaj wartości i wróć do odpowiedniej wartości dopasowania
• 2 kliknięcia, aby wsadowo uczynić odwołania do komórek absolutnymi za pomocą Kutools
• Odniesienie względne i odniesienie mieszane
• Rzeczy do zapamiętania

Pobierz bezpłatnie przykładowy plik

Wideo: Absolutne odniesienie

Co to jest absolutne odniesienie

Odwołanie bezwzględne to rodzaj odwołania do komórki w programie Excel.

W porównaniu z odwołaniem względnym, które zmienia się w zależności od jego względnego położenia, gdy formuła jest kopiowana do innych komórek, odwołanie bezwzględne pozostanie stałe bez względu na to, gdzie formuła zostanie skopiowana lub przeniesiona.

Odwołanie bezwzględne jest tworzone przez dodanie znaku dolara ($) przed odwołaniami do kolumn i wierszy w formule. Na przykład, aby utworzyć odwołanie bezwzględne dla komórki A1, należy ją przedstawić jako $A$1.

Odwołania bezwzględne są przydatne, gdy chcesz odwołać się do ustalonej komórki lub zakresu w formule, która zostanie skopiowana do wielu komórek, ale nie chcesz, aby odwołanie uległo zmianie.

Na przykład zakres A4:C7 zawiera ceny produktów i chcesz uzyskać należny podatek od każdego produktu na podstawie stawki podatku w komórce B2.

Jeśli użyjesz odniesienia względnego w formule, takiej jak „=B5*B2”, niektóre błędne wyniki zostaną zwrócone, gdy przeciągniesz uchwyt automatycznego wypełniania w dół, aby zastosować tę formułę. Ponieważ odwołanie do komórki B2 zmieni się względem komórek w formule. Teraz formuła w komórce C6 to „=B6*B3”, a formuła w komórce C7 to „=B7*B4”

Ale jeśli użyjesz odwołania bezwzględnego do komórki B2 z formułą „=B5*$B$2”, zapewnisz, że stawka podatku pozostanie taka sama dla wszystkich komórek, gdy formuła zostanie przeciągnięta w dół za pomocą uchwytu automatycznego wypełniania, wyniki będą poprawne.

Korzystanie z odniesienia względnego		Korzystanie z odniesienia bezwzględnego

Jak tworzyć absolutne referencje

Aby utworzyć odwołanie bezwzględne w programie Excel, musisz dodać znaki dolara ($) przed odwołaniami do kolumn i wierszy w formule. Odniesienie bezwzględne można utworzyć na dwa sposoby:

Ręcznie dodaj znaki dolara do odwołania do komórki

Możesz ręcznie dodać znaki dolara ($) przed odwołaniami do kolumn i wierszy, które chcesz ustawić jako bezwzględne, podczas wpisywania formuły w komórce.

Na przykład, jeśli chcesz dodać liczby w komórkach A1 i B1 i nadać im wartości bezwzględne, po prostu wpisz formułę jako „=$A$1+$B$1”. Dzięki temu odwołania do komórek pozostaną niezmienne, gdy formuła zostanie skopiowana lub przeniesiona do innych komórek.

Lub jeśli chcesz zmienić odwołania w istniejącej formule w komórce na bezwzględne, możesz zaznaczyć komórkę, a następnie przejść do paska formuły, aby dodać znaki dolara ($).

Używanie skrótu F4 do konwersji odniesienia względnego na bezwzględne

1. Kliknij dwukrotnie komórkę z formułą, aby przejść do trybu edycji;
2. Umieść kursor w odwołaniu do komórki, które chcesz ustawić jako bezwzględne;
3. Naciśnij przycisk F4 klawisz na klawiaturze, aby przełączać typy odwołań, aż znak dolara zostanie dodany przed odwołaniami zarówno do kolumny, jak i do wiersza;
4. Naciśnij przycisk Wchodzę aby wyjść z trybu edycji i zastosować zmiany.

Klawisz F4 może przełączać zadawanie między zadawaniem względnym, zadawaniem bezwzględnym i zadawaniem mieszanym.

A1 → $A$1 → A$1 → $A1 → A1

Jeśli chcesz, aby wszystkie odniesienia w formule były bezwzględne, zaznacz całą formułę na pasku formuły i naciśnij F4 , aby przełączać typy odwołań, aż znaki dolara zostaną dodane przed odwołaniami do kolumn i wierszy.

A1+B1 → $A$1+$B$1 → A$1+B$1 → $A1+$B1 → A1+B1

Użyj odniesienia bezwzględnego z przykładami

Ta część zawiera 2 przykłady pokazujące, kiedy i jak używać odwołań bezwzględnych w formule programu Excel.

Przykład 1 Oblicz procent całości

Załóżmy, że masz zakres danych (A3:B7) zawierający sprzedaż każdego owocu, a komórka B8 zawiera łączną kwotę sprzedaży tych owoców, teraz chcesz obliczyć procent sprzedaży każdego owocu w sumie.

Ogólny wzór do obliczania procentu całości:

Percentage = Sale/Amount

Użyj odniesienia względnego we wzorze, aby uzyskać procent pierwszego owocu w następujący sposób:

=B4/B8

Podczas przeciągania uchwytu automatycznego wypełniania w dół, aby obliczyć procent innych owoców, #DIV/0! błędy zostaną zwrócone.

Ponieważ po przeciągnięciu uchwytu automatycznego wypełniania w celu skopiowania formuły do ​​komórek poniżej, względne odwołanie B8 jest automatycznie dostosowywane do innych odwołań do komórek (B9, B10, B11) na podstawie ich względnych pozycji. A komórki B9, B10 i B11 są puste (zera), gdy dzielnik wynosi zero, formuła powraca do błędu.

Aby naprawić błędy, w tym przypadku musisz ustawić odwołanie do komórki B8 jako bezwzględne ($B$8) w formule, aby zapobiec jego zmianie podczas przenoszenia lub kopiowania formuły w dowolne miejsce. Teraz formuła jest aktualizowana do:

=B4/$B$8

Następnie przeciągnij uchwyt automatycznego wypełniania w dół, aby obliczyć procent innych owoców.

Przykład 2 Wyszukaj wartość i wróć do odpowiedniej wartości dopasowania

Zakładając, że chcesz wyszukać listę nazwisk w D4:D5 i zwrócić odpowiadające im pensje na podstawie nazwisk pracowników i odpowiedniego rocznego wynagrodzenia podanego w zakresie (A4:B8).

Ogólna formuła wyszukiwania to:

=VLOOKUP(lookup_value, table_range, column_index, logical)

Jeśli użyjesz odniesienia względnego w formule do wyszukania wartości i zwrócenia odpowiedniej wartości dopasowania w następujący sposób:

=VLOOKUP(D4,A4:B8,2,FALSE)

Następnie przeciągnij uchwyt automatycznego wypełniania w dół, aby wyszukać wartość poniżej, zostanie zwrócony błąd.

Gdy przeciągniesz uchwyt wypełniania w dół, aby skopiować formułę do komórki poniżej, odwołania w formule automatycznie dostosują się w dół o jeden wiersz. W rezultacie odwołanie do zakresu tabeli A4:B8 zmieni się na A5:B9. Ponieważ „Lisa: nie można znaleźć w zakresie A5:B9, formuła zwraca błąd.

Aby uniknąć błędów, użyj odwołania bezwzględnego $A$4:$B$8 zamiast odwołania względnego A4:B8 w formule:

=VLOOKUP(D4,$A$4:$B$8,2,FALSE)

Następnie przeciągnij uchwyt automatycznego wypełniania w dół, aby uzyskać wynagrodzenie Lisy.

2 kliknięcia, aby wsadowo uczynić odwołania do komórek absolutnymi za pomocą Kutools

Niezależnie od tego, czy wybierzesz ręczne wpisywanie, czy użyjesz skrótu F4, w programie Excel możesz zmieniać tylko jedną formułę naraz. Jeśli chcesz, aby odwołania do komórek w setkach formuł były bezwzględne w programie Excel, funkcja Konwertuj odniesienia narzędzie Kutools for Excel może pomóc Ci obsłużyć zadanie za pomocą 2 kliknięć.

Zaznacz komórki z formułą, dla których chcesz ustawić odwołania do komórek jako bezwzględne, i kliknij Kutools > Więcej (efekt) > Konwertuj odniesienia. Następnie wybierz Absolutnie opcję i kliknij Ok or Zastosuj. Teraz wszystkie odwołania do komórek wybranych formuł zostały przekonwertowane na wartości bezwzględne.

Odniesienie względne i odniesienie mieszane

Oprócz odniesienia bezwzględnego istnieją dwa inne typy referencji: referencja względna i referencja mieszana.

Odniesienie względne jest domyślnym typem odwołania w programie Excel, który nie zawiera znaków dolara ($) przed odwołaniami do wierszy i kolumn. A kiedy formuła z odniesieniami względnymi zostanie skopiowana lub przeniesiona do innych komórek, odniesienia zmienią się automatycznie na podstawie ich względnego położenia.

Na przykład, gdy wpiszesz formułę w komórce, taką jak „=A1+1”, a następnie przeciągniesz uchwyt autouzupełniania w dół, aby wypełnić tę formułę do następnej komórki, formuła automatycznie zmieni się na „=A2+1”.

Odniesienie mieszane składa się zarówno z odniesienia bezwzględnego, jak i odniesienia względnego. Innymi słowy, odwołanie mieszane używa znaku dolara ($) do naprawienia wiersza lub kolumny podczas kopiowania lub wypełniania formuły.

Weźmy jako przykład tabliczkę mnożenia, wiersze i kolumny zawierają liczby od 1 do 9, które będziecie mnożyć.

Na początek możesz użyć formuły „=B3*C2” w komórce C3, aby pomnożyć 1 w komórce B3 przez liczbę (1) w pierwszej kolumnie. Jednak gdy przeciągniesz uchwyt autouzupełniania w prawo, aby wypełnić pozostałe komórki, zauważysz, że wszystkie wyniki są nieprawidłowe, z wyjątkiem pierwszego.

Dzieje się tak, ponieważ podczas kopiowania formuły w prawo pozycja wiersza nie ulegnie zmianie, ale pozycja kolumny zmieni się z B3 na C3, D3 itd. W rezultacie formuły w odpowiednich komórkach (D3, E3, itd.) zmień na „=C3*D2”, „=D3*E2” i tak dalej, kiedy faktycznie chcesz, aby były to „=B3*D2”, „=B3*E2” i tak dalej.

W takim przypadku musisz dodać znak dolara ($), aby zablokować odwołanie do kolumny „B3”. Skorzystaj ze wzoru jak poniżej:

=$B3*C2

Teraz, gdy przeciągniesz formułę w prawo, wyniki będą poprawne.

Następnie musisz pomnożyć liczbę 1 w komórce C2 przez liczby w wierszach poniżej.

Gdy skopiujesz formułę w dół, pozycja kolumny komórki C2 nie zmieni się, ale pozycja wiersza zmieni się z C2 na C3, C4 itd. W rezultacie formuły w komórkach poniżej zmienią się na „=$B4C3”, „=$B5C4” itp., co spowoduje nieprawidłowe wyniki.

Aby rozwiązać ten problem, zmień „C2” na „C $ 2”, aby odwołanie do wiersza nie zmieniało się podczas przeciągania uchwytu automatycznego wypełniania w dół w celu wypełnienia formuł.

=$B3*C$2

Teraz możesz przeciągnąć uchwyt automatycznego wypełniania w prawo lub w dół, aby uzyskać wszystkie wyniki.

Rzeczy do zapamiętania

• Podsumowanie odwołań do komórek

  Typ	Przykład	Podsumowanie
  Bezwzględne odniesienie	1 $ A $ XNUMX	Nigdy nie zmieniaj, gdy formuła jest kopiowana do innych komórek
  Względne odniesienie	A1	Zarówno odwołanie do wiersza, jak i kolumny zmienia się na podstawie względnej pozycji, gdy formuła jest kopiowana do innych komórek
  Odniesienie mieszane	$A1/A$1	Odwołanie do wiersza zmienia się, gdy formuła jest kopiowana do innych komórek, ale odwołanie do kolumny jest stałe/Odwołanie do kolumny zmienia się, gdy formuła jest kopiowana do innych komórek, ale odwołanie do wiersza jest stałe;

• Ogólnie rzecz biorąc, odwołania bezwzględne nigdy nie zmieniają się, gdy formuła jest przenoszona. Jednak odniesienia bezwzględne zostaną automatycznie dostosowane, gdy wiersz lub kolumna zostanie dodana lub usunięta z góry lub z lewej strony arkusza. Na przykład w formule „=$A$1+1” po wstawieniu wiersza na górze arkusza formuła automatycznie zmieni się na „=$A$2+1”.

  

• Połączenia F4 klawisz może przełączać pomiędzy odniesieniem względnym, odniesieniem bezwzględnym i odniesieniem mieszanym.