Excel (Polski): Względne i bezwzględne odwołania do komórek

Lesson 15: Względne i bezwzględne odwołania do komórek

/en/tr_pl-excel/tworzenie-zoonych-formu/content/

Wprowadzenie

Istnieją dwa rodzaje odwołań do komórek: względne i bezwzględne. Odwołania względne i bezwzględne zachowują się inaczej po skopiowaniu i wypełnieniu do innych komórek. Odwołania względne zmieniają się, gdy formuła jest kopiowana do innej komórki. Z drugiej strony bezwzględne odwołania pozostają niezmienne bez względu na to, gdzie są kopiowane.

Opcjonalnie: Pobierz nasz skoroszyt ćwiczeń.

Obejrzyj poniższy film, aby dowiedzieć się więcej o odwołaniach do komórek.

Odwołanie względne

Domyślnie wszystkie odwołania do komórek są odwołaniami względnymi. Po skopiowaniu do wielu komórek zmieniają się w zależności od względnej pozycji wiersza i kolumny. Na przykład, jeśli skopiujesz formułę = A1 + B1 z wiersza 1 do wiersza 2, formuła zmieni się na = A2 + B2. Odwołania względne są szczególnie wygodne, gdy trzeba powtórzyć to samo obliczenie w wielu wierszach lub kolumnach.

Aby utworzyć i skopiować formułę przy użyciu odniesień względnych:

W poniższym przykładzie chcemy utworzyć formułę, która pomnoży cenę każdego towaru przez ilość. Zamiast tworzyć nową formułę dla każdego wiersza, możemy utworzyć jedną formułę w komórce D4, a następnie skopiować ją do innych wierszy. Użyjemy odwołań względnych, aby formuła poprawnie obliczała sumę dla każdego elementu.

  1. Wybierz komórkę, która będzie zawierać formułę. W naszym przykładzie wybieramy komórkę D4.
    Wybieranie komórki
  2. Wprowadź wzór, aby obliczyć żądaną wartość. W naszym przykładzie wpiszemy = B4 * C4.
    Wprowadzanie formuły
  3. Naciśnij klawisz Enter na klawiaturze. Formuła zostanie obliczona, a wynik zostanie wyświetlony w komórce.
  4. Znajdź uchwyt wypełniania w prawym dolnym rogu żądanej komórki. W naszym przykładzie zlokalizujemy uchwyt wypełniania dla komórki D4.
    Lokalizowanie uchwytu wypełnienia
  5. Kliknij i przeciągnij uchwyt wypełniania nad komórkami, które chcesz wypełnić. W naszym przykładzie wybieramy komórki D5: D13.
    Przeciąganie uchwytu wypełnienia
  6. Puść mysz. Formuła zostanie skopiowana do wybranych komórek wraz z odniesieniami względnymi, wyświetlając wynik w każdej komórce.
    Wynik formuły

Możesz kliknąć dwukrotnie wypełnione komórki, aby sprawdzić ich formuły pod kątem prawidłowości. Względne odwołania do komórek powinny być różne dla każdej komórki, w zależności od wiersza.

Sprawdzanie formuły

Odwołania bezwzględne

Może się zdarzyć, że nie chcesz, aby odwołanie do komórki zmieniało się po skopiowaniu do innych komórek. W przeciwieństwie do odniesień względnych odniesienia bezwzględne nie zmieniają się po skopiowaniu lub wypełnieniu. Można użyć bezwzględnego odniesienia do zachowania rządu i / lub kolumny w takiej samej postaci.

Odwołanie bezwzględne jest określone we wzorze przez dodanie znaku dolara ($). Może poprzedzać odwołanie do kolumny, odwołanie do wiersza lub jedno i drugie.

$ 2 $, kolumna i wiersz nie zmieniają się po skopiowaniu; 2 USD, rząd się nie zmienia; $ A2, kolumna się nie zmienia

Zasadniczo będziesz używać formatu $ A $ 2 podczas tworzenia formuł zawierających odwołania bezwzględne. Pozostałe dwa formaty są używane znacznie rzadziej.

Podczas pisania formuły możesz nacisnąć klawisz F4 na klawiaturze, aby przełączać się między względnymi i bezwzględnymi odwołaniami do komórek, jak pokazano na poniższym filmie. Jest to prosty sposób na szybkie wstawienie bezwzględnego odwołania.

Aby utworzyć i skopiować formułę za pomocą odwołania bezwzględnego:

W poniższym przykładzie użyjemy komórki E2 (która zawiera stawkę podatku 7,5%), aby obliczyć podatek od sprzedaży dla każdej pozycji w kolumnie D. Aby mieć pewność, że odniesienie do stawki podatkowej pozostanie niezmienne - nawet gdy formuła zostanie skopiowana i wypełniona do innych komórek - musimy uczynić komórkę $ E 2 $  odniesieniem bezwzględnym.

  1. Wybierz komórkę, która będzie zawierać formułę. W naszym przykładzie wybieramy komórkę D4.
    Wybieranie komórki
  2. Wprowadź wzór, aby obliczyć żądaną wartość. W naszym przykładzie wpiszemy = (B4 * C4) * $ E $ 2, co spowoduje, że $ E $ 2 będzie absolutnym odniesieniem.
    Wprowadzanie formuły
  3. Naciśnij klawisz Enter na klawiaturze. Formuła wykona obliczenia, a wynik zostanie wyświetlony w komórce.
  4. Znajdź uchwyt wypełniania w prawym dolnym rogu żądanej komórki. W naszym przykładzie zlokalizujemy uchwyt wypełniania dla komórki D4.
    Lokalizowanie uchwytu wypełnienia
  5. Kliknij i przeciągnij uchwyt wypełniania nad komórkami, które chcesz wypełnić (w naszym przykładzie będą to komórki D5: D13).
    Przeciąganie uchwytu wypełnienia
  6. Puść mysz. Formuła zostanie skopiowana do wybranych komórek wraz z bezwzględnym odwołaniem, a wartości zostaną obliczone w każdej komórce.
    Wynik formuły

Możesz kliknąć dwukrotnie wypełnione komórki, aby sprawdzić formuły pod kątem poprawności. Odwołanie bezwzględne powinno być takie samo dla każdej komórki, podczas gdy inne odniesienia są zależne od wiersza komórki.

Sprawdzanie formuły

Pamiętaj o dołączaniu znaku dolara ($) za każdym razem, gdy podajesz bezwzględne odniesienie w wielu komórkach. Znaki dolara zostały pominięte w poniższym przykładzie. Spowodowało to, że Excel zinterpretował to jako odniesienie względne, dostarczając niepoprawny wynik po skopiowaniu do innych komórek.

Niepoprawnie skopiowana formuła

Używanie odwołań do komórek w wielu arkuszach

Excel pozwala odwoływać się do dowolnej komórki w dowolnym arkuszu, co może być szczególnie pomocne, jeśli chcesz odwołać się do określonej wartości z jednego arkusza do drugiego. Aby to zrobić, to po prostu trzeba rozpocząć odwołanie do komórki od nazwy arkusza, a następnie wstawić wykrzyknik (!). Na przykład, jeśli chcesz odwoływać się do komórki A1 w Arkusz1, jej odwołaniem do komórki będzie Arkusz1! A1.

Pamiętaj, że jeśli nazwa arkusza zawiera spację, wokół nazwy musisz umieścić pojedyncze znaki cudzysłowu (' '). Na przykład, jeśli chcesz odwoływać się do komórki A1 w arkuszu o nazwie Lipcowy budżet, jej odwołaniem do komórki będzie 'Lipcowy budżet'!A1.

Aby odwołać się do komórek w arkuszach:

W naszym przykładzie poniżej odniesiemy się do komórki z obliczoną wartością między dwoma arkuszami. Umożliwi nam to użycie dokładnie tej samej wartości w dwóch różnych arkuszach roboczych bez przepisywania formuły lub kopiowania danych.

  1. Znajdź komórkę, do której chcesz się odwołać, i zanotuj jej arkusz roboczy. W naszym przykładzie chcemy odwołać się do komórki E14 w arkuszu Kolejność menu.
    Lokalizowanie komórki
  2. Przejdź do żądanego arkusza. W naszym przykładzie wybieramy arkusz faktury cateringowej.
    Wybieranie arkusza roboczego
  3. Znajdź i wybierz komórkę, w której ma być wyświetlona wartość. W naszym przykładzie wybieramy komórkę C4.
    Wybieranie komórki
  4. Wpisz znak równości (=), nazwę arkusza, wykrzyknik (!) i adres komórki. W naszym przykładzie wpiszemy = 'Kolejność menu'! E14.
    Wprowadzanie formuły
  5. Naciśnij klawisz Enter na klawiaturze. Pojawi się wartość znajdująca się w komórce, do której się odnieśliśmy. Jeśli wartość komórki E14 w arkuszu Kolejność menu ulegnie zmianie, to wartość w arkuszu faktura cateringowa zostanie automatycznie zaktualizowana.
    Wynik formuły

Jeśli później zmienisz nazwę arkusza, odwołanie do komórki zostanie zaktualizowane automatycznie, aby odzwierciedlić nazwę nowego arkusza.

W przypadku nieprawidłowego wpisania nazwy arkusza w komórce pojawi się błąd #REF!. W poniższym przykładzie błędnie wpisaliśmy nazwę arkusza roboczego. Aby edytować, zignorować lub zbadać błąd, kliknij przycisk Błąd obok komórki i wybierz opcję z menu.

Poprawianie błędu

Wyzwanie!

  1. Otwórz nasz zeszyt ćwiczeń.
  2. Kliknij kartę Paper Goods w lewym dolnym rogu skoroszytu.
  3. W komórce D4 wprowadź formułę, która pomnoży cenę jednostkową w komórce B4, ilość w komórce C4 i stawkę podatku w komórce E2. Upewnij się, że używasz bezwzględnego odwołania dla komórki stawki podatkowej, ponieważ będzie ona taka sama w każdej komórce.
  4. Użyj uchwytu wypełniania, aby skopiować właśnie utworzoną formułę do komórek D5: D12.
  5. Zmień stawkę podatku w komórce E2 na 6,5%. Zauważ, że wszystkie twoje komórki zostały zaktualizowane. Po zakończeniu skoroszyt powinien wyglądać następująco:
    Zakładka Artykuły papiernicze
  6. Kliknij kartę Catering Invoice.
  7. Usuń wartość w komórce C5 i zastąp ją odwołaniem do całkowitego kosztu towarów papierowych. Wskazówka: Koszt towarów papierowych znajduje się w komórce E13 w arkuszu Paper Goods.
  8. Wykonaj te same kroki od góry, aby obliczyć podatek od sprzedaży dla każdego elementu w arkuszu Menu Order. Całkowity koszt w komórce E14 powinien się zaktualizować. Następnie w komórce C4 arkusza Catering Invoice utwórz odwołanie do komórki, którą właśnie obliczyłeś. Uwaga: jeśli korzystałeś z naszego skoroszytu ćwiczeń podczas lekcji, być może już ukończyłeś ten krok.
  9. Kiedy skończysz, arkusz Catering Invoice powinien wyglądać mniej więcej tak:
    cell_references_challenge

/en/tr_pl-excel/funkcje/content/