Excel 2016 - Lekcja 15: Względne i bezwzględne odwołania do komórek

• Excel 2016 (część 12): Formatuj strony i drukuj arkusze kalkulacyjne
• Excel 2016 (Część 13): Wprowadzenie do formuł
• Excel 2016 (część 14): Twórz złożone formuły

W programie Excel istnieją dwa typy odwołań do komórek: względne i bezwzględne . Odniesienia względne i bezwzględne zachowują się inaczej podczas kopiowania i wypełniania komórek w różny sposób. Odniesienia względne zmieniają się, gdy formuła jest kopiowana do innej komórki. Jednakże odniesienia bezwzględne pozostają niezmienione niezależnie od tego, gdzie zostały skopiowane.

Dołącz do LuckyTemplates w tym artykule z instrukcjami dotyczącymi względnych i bezwzględnych odwołań do komórek w programie Excel 2016 !

Względne i bezwzględne odwołania do komórek w programie Excel

• Odniesienia względne
  • Utwórz i skopiuj formułę korzystającą z odwołań względnych
• Absolutne referencje
  • Utwórz i skopiuj formułę poprzez odwołanie bezwzględne
• Używaj odwołań do komórek w wielu arkuszach
  • Komórki referencyjne w arkuszach

Obejrzyj poniższy film, aby dowiedzieć się więcej na temat odwołań do komórek:

Odniesienia względne

Domyślnie wszystkie odwołania do komórek są odniesieniami względnymi. Po skopiowaniu do wielu komórek zmieniają się one w zależności od względnego położenia wierszy i kolumn. Na przykład, jeśli skopiujesz formułę =A1+B1 z wiersza 1 do wiersza 2, formuła zmieni się na =A2+B2 . Odniesienia względne są szczególnie wygodne, gdy trzeba powtórzyć te same obliczenia w wielu wierszach lub kolumnach.

Utwórz i skopiuj formułę korzystającą z odwołań względnych

W poniższym przykładzie chcemy utworzyć formułę, która pomnoży cenę każdego artykułu przez ilość. Zamiast tworzyć nową formułę dla każdego elementu, możemy utworzyć pojedynczą formułę w komórce D2 , a następnie skopiować ją do pozostałych wierszy. Będziemy używać względnych odniesień, aby dokładnie obliczyć sumę dla każdego przedmiotu.

1. Wybierz komórkę zawierającą formułę. W przykładzie wybierzemy komórkę D4 .

2. Wprowadź wzór, aby obliczyć żądaną wartość. W przykładzie napiszemy =B4*C4 .

3. Naciśnij Enter na klawiaturze. Formuła zostanie obliczona, a wynik wyświetlony w komórce.

4. Znajdź uchwyt wypełniania w prawym dolnym rogu żądanej komórki. W przykładzie zlokalizujemy uchwyt wypełniania komórki D4.

5. Kliknij i przeciągnij uchwyt wypełniania do komórek, które chcesz wypełnić. W przykładzie wybierzemy komórki D5:D13 .

6. Zwolnij mysz. Formuła zostanie skopiowana do wybranych komórek wraz z odniesieniami względnymi, wyświetlając wyniki w każdej komórce.

Możesz dwukrotnie kliknąć wypełnione komórki, aby sprawdzić ich dokładną formułę. Względne odniesienia dla każdej komórki powinny być różne, w zależności od wiersza.

Absolutne referencje

Czasami możesz nie chcieć zmieniać odwołania do komórki podczas kopiowania do innych komórek. W przeciwieństwie do odniesień względnych, odniesienia bezwzględne nie zmieniają się po skopiowaniu lub wypełnieniu. Możesz użyć odwołania bezwzględnego, aby zachować ciągłość wierszy i/lub kolumn.

Odniesienie bezwzględne określa się we wzorze poprzez dodanie znaku dolara ($) . Może poprzedzać odwołanie do kolumny, odwołanie do wiersza lub jedno i drugie.

Podczas tworzenia formuł zawierających odwołania bezwzględne będziesz zazwyczaj używać formatu $A$2 . Pozostałe dwa formaty są używane 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 odniesienia bezwzględnego.

Utwórz i skopiuj formułę poprzez odwołanie bezwzględne

W poniższym przykładzie użyjemy komórki E2 (w której stawka podatku wynosi 7,5%) do obliczenia podatku od sprzedaży dla każdej pozycji w kolumnie D. Aby mieć pewność, że odniesienie do stawki podatku pozostanie stałe – nawet jeśli formuła zostanie skopiowana i wypełnione innymi komórkami - musimy uczynić komórkę $E$2 bezwzględnym odniesieniem.

1. Wybierz komórkę zawierającą formułę. W przykładzie wybierzemy komórkę D4 .

2. Wprowadź wzór, aby obliczyć żądaną wartość. W przykładzie wprowadzimy =(B4*C4)*$E$2 , dzięki czemu $E$2 będzie absolutnym odniesieniem.

3. Naciśnij 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 przykładzie zlokalizujemy uchwyt wypełniania komórki D4.

5. Kliknij i przeciągnij uchwyt wypełniania do komórek, które chcesz wypełnić (w przykładzie komórki D5:D13).

6. Zwolnij mysz. Formuła zostanie skopiowana do wybranych komórek z odwołaniem bezwzględnym, a wartości zostaną przeliczone w każdej komórce.

Możesz dwukrotnie kliknąć wypełnione komórki, aby sprawdzić ich dokładną formułę. Odwołanie bezwzględne w każdej komórce powinno być takie samo, natomiast pozostałe odniesienia odnoszą się do wiersza komórki.

Pamiętaj o dołączeniu znaku dolara ($) za każdym razem, gdy tworzysz odniesienia bezwzględne w wielu komórkach. W poniższym przykładzie pominięto znaki dolara. To powoduje, że Excel interpretuje to jako odniesienie względne, co daje nieprawidłowe wyniki podczas kopiowania do innych komórek.

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

Excel umożliwia odwoływanie się do dowolnej komórki w arkuszu, co może być szczególnie przydatne, jeśli chcesz odwołać się do określonej wartości z innego arkusza. Aby to zrobić, wystarczy rozpocząć odwołanie do komórki nazwą arkusza, po której następuje wykrzyknik (!) . Na przykład, jeśli chcesz odwołać się do komórki A1 w Arkuszu1 , jej odwołanie do komórki będzie brzmiało Arkusz1!A1 .

Pamiętaj, że jeśli nazwa arkusza zawiera spacje , wokół nazwy należy umieścić nawiasy („”) . Na przykład, jeśli chcesz odwołać się do komórki A1 w arkuszu o nazwie Budżet lipcowy , odwołanie do komórki będzie brzmieć „Budżet lipcowy”!A1 .

Komórki referencyjne w arkuszach

W poniższym przykładzie odniesiemy się do komórki z obliczoną wartością znajdującą się pomiędzy dwiema tabelami. Umożliwi nam to użycie dokładnej wartości w dwóch różnych arkuszach kalkulacyjnych bez przepisywania formuł i powielania danych.

1. Znajdź komórkę, do której chcesz się odwołać i zanotuj jej arkusz. W naszym przykładzie chcemy odwołać się do komórki E14 w arkuszu Zamówienia menu .

2. Przejdź do żądanego arkusza kalkulacyjnego. W naszym przykładzie wybierzemy arkusz faktury za catering .

3. Znajdź i wybierz komórkę, w której ma się pojawić wartość. W przykładzie wybierzemy komórkę C4 .

4. Wpisz znak równości (=) , nazwę tabeli, po której następuje wykrzyknik (!) i adres komórki. W przykładzie napiszemy ='MenuOrder'!E14 .

5. Naciśnij Enter na klawiaturze. Pojawi się wartość komórki, do której następuje odwołanie. Teraz, jeśli wartość komórki E14 zmieni się w arkuszu Zamówienie menu, zostanie ona automatycznie zaktualizowana w arkuszu Faktura za catering.

Jeśli później zmienisz nazwę arkusza, odwołanie do komórki zostanie automatycznie zaktualizowane, aby odzwierciedlało nową nazwę arkusza.

Jeśli wpiszesz niepoprawną nazwę tabeli, #REF! pojawi się w polu. W przykładzie pomyliliśmy nazwę arkusza. Aby edytować, zignorować lub zbadać błąd, kliknij przycisk Błąd obok komórki i wybierz opcję z menu.

Zobacz więcej artykułów:

• Excel 2016 (część 3): Jak tworzyć nowe i otwierać istniejące arkusze kalkulacyjne
• Excel 2016 (część 4): Jak przechowywać i udostępniać arkusze kalkulacyjne
• Excel 2016 (Część 5): Podstawy komórek i zakresów

Bawić się!