Linki w Excelu – bezwzględne, względne i mieszane. Błędy podczas pracy z linkami względnymi w programie Excel

Aby ułatwić obliczenia przy użyciu formuł w trybie automatycznym, używane są odwołania do komórek. W zależności od rodzaju pisania dzieli się je na trzy główne typy:

1. Linki względne. Używany do prostych obliczeń. Kopiowanie formuły wiąże się ze zmianą współrzędnych.
2. Linki absolutne. Jeśli potrzebujesz wykonać bardziej złożone obliczenia, ta opcja jest odpowiednia. Do mocowania użyj symbolu „$”. Przykład: $A$1.
3. mieszane linki. Ten typ adresowania jest stosowany w obliczeniach, gdy konieczne jest oddzielne ustalenie kolumny lub wiersza. Przykład: $A1 lub A$1.

W przypadku konieczności skopiowania danych z wprowadzonej formuły stosuje się referencje z adresowaniem bezwzględnym i mieszanym. Artykuł pokaże na przykładach, w jaki sposób wykonuje się obliczenia przy użyciu różnych typów linków.

Względne odwołanie do komórki w programie Excel

Jest to zestaw znaków definiujących położenie komórki. Linki w programie są automatycznie zapisywane z adresowaniem względnym. Na przykład: A1, A2, B1, B2. Przejście do innego wiersza lub innej kolumny powoduje zmianę znaków w formule. Na przykład pozycja początkowa A1. Poruszanie się w poziomie zmienia literę na B1, C1, D1 itd. W ten sam sposób zmiany zachodzą przy poruszaniu się po linii pionowej, tylko w tym przypadku zmienia się liczba – A2, A3, A4 itd. W przypadku konieczności powielenia obliczenie tego samego typu w sąsiedniej komórce, obliczenie jest wykonywane przy użyciu odniesienia względnego. Aby skorzystać z tej funkcji, wykonaj kilka kroków:

1. Jak tylko dane zostaną wprowadzone do komórki, przesuń kursor i kliknij myszą. Podświetlenie zielonym prostokątem wskazuje na aktywację komórki i gotowość do dalszej pracy.
2. Naciskając kombinację klawiszy Ctrl + C kopiujemy zawartość do schowka.
3. Aktywujemy komórkę, do której chcesz przenieść dane lub wcześniej napisaną formułę.
4. Naciskając kombinację Ctrl+V przenosimy dane zapisane w schowku systemowym.

Porada eksperta! Aby przeprowadzić ten sam rodzaj obliczeń w tabeli, użyj life hack. Wybierz komórkę zawierającą wcześniej wprowadzoną formułę. Najeżdżając kursorem na mały kwadrat, który pojawia się w prawym dolnym rogu i przytrzymując lewy przycisk myszy, przeciągnij do dolnego wiersza lub skrajnej kolumny, w zależności od wykonanej akcji. Po zwolnieniu przycisku myszy obliczenia zostaną wykonane automatycznie. To narzędzie nazywa się znacznikiem automatycznego wypełniania.

Przykład linku względnego

Aby było to jaśniejsze, rozważ przykład obliczenia przy użyciu formuły ze względnym odniesieniem. Załóżmy, że właściciel sklepu sportowego po roku pracy musi obliczyć zysk ze sprzedaży.

Kolejność działań:

1. Przykład pokazuje, że kolumny B i C zostały użyte do wypełnienia ilości sprzedanego towaru i jego ceny. W związku z tym, aby napisać formułę i uzyskać odpowiedź, wybierz kolumnę D. Formuła wygląda tak: = B2*C

Uwaga! Aby ułatwić proces pisania formuły, użyj małej sztuczki. Umieść znak „=”, kliknij ilość sprzedanego towaru, ustaw znak „*” i kliknij cenę produktu. Formuła po znaku równości zostanie zapisana automatycznie.

2. Aby uzyskać ostateczną odpowiedź, naciśnij „Enter”. Następnie musisz obliczyć całkowitą kwotę zysku otrzymanego z innych rodzajów produktów. Cóż, jeśli liczba linii nie jest duża, wszystkie manipulacje można wykonać ręcznie. Aby jednocześnie wypełnić dużą liczbę wierszy w programie Excel, istnieje jedna przydatna funkcja, która umożliwia przeniesienie formuły do ​​innych komórek.
3. Przesuń kursor nad prawy dolny róg prostokąta z formułą lub gotowym wynikiem. Pojawienie się czarnego krzyża jest sygnałem, że można przeciągnąć kursor w dół. W ten sposób dokonywana jest automatyczna kalkulacja zysku otrzymanego dla każdego produktu z osobna.
4. Zwalniając wciśnięty przycisk myszy otrzymujemy poprawne wyniki we wszystkich wierszach.

Klikając komórkę D3, możesz zobaczyć, że współrzędne komórki zostały automatycznie zmienione i teraz wyglądają tak: =B3*C3. Wynika z tego, że powiązania były względne.

Możliwe błędy podczas pracy z linkami względnymi

Niewątpliwie ta funkcja Excela znacznie upraszcza obliczenia, ale w niektórych przypadkach mogą pojawić się trudności. Rozważmy prosty przykład obliczania współczynnika zysku każdego towaru:

1. Utwórz tabelę i wypełnij: A – nazwę produktu; B – sprzedana ilość; C – koszt; D to otrzymana kwota. Załóżmy, że w asortymencie jest tylko 11 pozycji. W związku z tym, biorąc pod uwagę opis kolumn, wypełnia się 12 wierszy, a łączna kwota zysku wynosi D
2. Kliknij komórkę E2 i wprowadź =D2 /D13.
3. Po naciśnięciu przycisku „Enter” pojawia się współczynnik względnego udziału sprzedaży pierwszej pozycji.
4. Rozciągnij kolumnę w dół i poczekaj na wynik. Jednak system wyświetla błąd „#DIV/0!”

Powodem błędu jest użycie względnego odniesienia do obliczeń. W wyniku kopiowania wzoru zmieniają się współrzędne. Oznacza to, że dla E3 wzór będzie wyglądał tak =D3 /D13. Ponieważ komórka D13 nie jest wypełniona i teoretycznie ma wartość zero, program poda błąd z informacją, że dzielenie przez zero jest niemożliwe.

Ważne! Aby poprawić błąd, konieczne jest napisanie wzoru w taki sposób, aby współrzędne D13 były stałe. Adresowanie względne nie ma takiej funkcjonalności. W tym celu istnieje inny rodzaj linków – bezwzględny. 

Jak stworzyć absolutny link w Excelu

Dzięki użyciu symbolu $ stało się możliwe ustalenie współrzędnych komórki. Jak to działa, rozważymy dalej. Ponieważ program domyślnie używa adresowania względnego, aby uczynić go bezwzględnym, będziesz musiał wykonać szereg czynności. Przeanalizujmy rozwiązanie błędu „Jak znaleźć współczynnik ze sprzedaży kilku pozycji towarów”, wykonując obliczenia przy użyciu adresowania bezwzględnego:

1. Kliknij na E2 i wprowadź współrzędne łącza =D2 /D13. Ponieważ łącze jest względne, należy ustawić symbol, aby naprawić dane.
2. Ustal współrzędne komórki D Aby wykonać tę czynność, poprzedź literę oznaczającą kolumnę i numer wiersza, umieszczając znak „$”.

Porada eksperta! Aby ułatwić zadanie wpisywania, wystarczy aktywować komórkę do edycji wzoru i kilkakrotnie nacisnąć klawisz F4. Dopóki nie uzyskasz satysfakcjonujących wartości. Prawidłowa formuła jest następująca: =D2/$D$13.

3. Naciśnij przycisk „Enter”. W wyniku przeprowadzonych działań powinien pojawić się poprawny wynik.
4. Przeciągnij znacznik do dolnego wiersza, aby skopiować formułę.

Ze względu na zastosowanie w obliczeniach adresowania bezwzględnego ostateczne wyniki w pozostałych wierszach będą poprawne.

Jak umieścić mieszany link w programie Excel

Do obliczeń formuł używane są nie tylko odniesienia względne i bezwzględne, ale także mieszane. Ich charakterystyczną cechą jest to, że ustalają jedną ze współrzędnych.

• Na przykład, aby zmienić położenie linii, musisz napisać znak $ przed oznaczeniem literowym.
• Wręcz przeciwnie, jeśli znak dolara zostanie napisany po oznaczeniu litery, wskaźniki w linii pozostaną niezmienione.

Wynika z tego, że w celu rozwiązania poprzedniego problemu z określeniem współczynnika sprzedaży towarów z wykorzystaniem adresowania mieszanego konieczne jest ustalenie numeru wiersza. Oznacza to, że znak $ jest umieszczany po literze kolumny, ponieważ jego współrzędne nie zmieniają się nawet w odniesieniu względnym. Weźmy przykład:

1. Aby uzyskać dokładne obliczenia, wprowadź =D1/$D$3 i naciśnij „Enter”. Program daje dokładną odpowiedź.
2. Aby przenieść formułę do kolejnych komórek w dół kolumny i uzyskać dokładne wyniki, przeciągnij uchwyt do dolnej komórki.
3. W rezultacie program poda poprawne obliczenia.

Uwaga! Jeśli ustawisz znak $ przed literą, Excel wyświetli błąd „#DIV/0!”, co będzie oznaczać, że tej operacji nie można wykonać.

Adresowanie „SuperAbsolutne”

Na koniec spójrzmy na inny przykład linku absolutnego – adresowanie „SuperAbsolute”. Jakie są jego cechy i różnice. Weź przybliżoną liczbę 30 i wpisz ją w komórce B2. To właśnie ta liczba będzie najważniejsza, konieczne jest wykonanie z nią szeregu działań, na przykład podniesienie jej do potęgi.

1. Aby poprawnie wykonać wszystkie czynności, wprowadź następującą formułę w kolumnie C: =$B$2^$D2. W kolumnie D wpisujemy wartość stopni.

2. Po naciśnięciu przycisku „Enter” i aktywacji formuły rozciągamy znacznik w dół kolumny.
3. Otrzymujemy prawidłowe wyniki.

Najważniejsze jest to, że wszystkie wykonane czynności odnosiły się do jednej stałej komórki B2, dlatego:

• Skopiowanie formuły z komórki C3 do komórki E3, F3 lub H3 nie zmieni wyniku. Pozostanie bez zmian – 900.
• Jeśli musisz wstawić nową kolumnę, zmienią się współrzędne komórki z formułą, ale wynik również pozostanie niezmieniony.

To jest osobliwość linku „SuperAbsolute”: jeśli musisz przenieść, wynik się nie zmieni. Zdarzają się jednak sytuacje, w których dane są wstawiane ze źródeł zewnętrznych. W ten sposób kolumny są przesuwane na bok, a dane są ustawiane w stary sposób w kolumnie B2. Co się dzieje w tym przypadku? Po zmieszaniu formuła zmienia się w zależności od wykonanej akcji, to znaczy nie będzie już wskazywać B2, ale C2. Ale ponieważ wstawienie zostało wykonane w B2, ostateczny wynik będzie błędny.

Odniesienie! Aby móc wstawiać makra ze źródeł zewnętrznych, musisz włączyć ustawienia programisty (są one domyślnie wyłączone). Aby to zrobić, przejdź do Opcji, otwórz ustawienia wstążki i zaznacz pole w prawej kolumnie obok „Programista”. Po tym otworzy się dostęp do wielu funkcji, które wcześniej były ukryte przed oczami przeciętnego użytkownika.

To nasuwa pytanie: czy można zmodyfikować formułę z komórki C2 tak, aby oryginalna liczba była pobierana z komórki B, pomimo wstawienia nowych kolumn danych? Aby zapewnić, że zmiany w tabeli nie wpłyną na określenie sumy podczas instalowania danych ze źródeł zewnętrznych, należy wykonać następujące czynności:

1. Zamiast współrzędnych komórki B2 wprowadź następujące wskaźniki: =ADR.POŚR(„B2”). W efekcie po przesunięciu skład receptury będzie wyglądał tak: =ADR.POŚR(„B2”)^$E2.
2. Dzięki tej funkcji link zawsze wskazuje na kwadrat o współrzędnych B2, niezależnie od tego, czy kolumny są dodawane, czy usuwane w tabeli.

Należy rozumieć, że komórka, która nie zawiera żadnych danych, zawsze pokazuje wartość „0”.

Wnioski

Dzięki zastosowaniu trzech odmian opisanych linków pojawia się wiele możliwości, które ułatwiają pracę z obliczeniami w Excelu. Dlatego zanim zaczniesz pracować z formułami, najpierw przeczytaj linki i zasady ich instalacji.