Excel

Metody adresowania bezwzględnego w Microsoft Excel

Jak wiadomo, w tabelach Excela występują dwa typy adresowania: względny i bezwzględny. W pierwszym przypadku łącze zmienia się w kierunku kopiowania o względną wartość przesunięcia, podczas gdy w drugim przypadku jest ono stałe i pozostaje niezmienione podczas kopiowania. Ale domyślnie wszystkie adresy w Excelu są bezwzględne. W tym samym czasie dość często występuje potrzeba stosowania bezwzględnego (stałego) adresowania. Dowiedzmy się, w jaki sposób można to zrobić.

Zastosowanie adresowania bezwzględnego

Adresowanie bezwzględne może być potrzebne, na przykład, gdy kopiujemy formułę, której jedna część składa się z zmiennej wyświetlanej w szeregu liczb, a druga ma stałą wartość. Oznacza to, że liczba ta odgrywa rolę niezmienionego współczynnika, z którym należy wykonać pewną operację (mnożenie, dzielenie itp.) Do całej serii liczb zmiennych.

W programie Excel istnieją dwa sposoby określenia stałego adresowania: poprzez utworzenie bezwzględnej referencji i użycie funkcji INDIRECT. Przyjrzyjmy się każdej z tych metod w szczegółach.

Metoda 1: odniesienie bezwzględne

Oczywiście najbardziej znanym i często stosowanym sposobem tworzenia adresowania bezwzględnego jest użycie bezwzględnych odniesień. Łącza bezwzględne mają znaczenie nie tylko funkcjonalne, ale także syntaktyczne. Adres względny ma następującą składnię:

=A1

Przy ustalonym adresie znak dolara jest ustawiany przed wartością współrzędnych:

=$A$1

Bezwzględne odniesienie w programie Microsoft Excel

Znak dolara można wprowadzić ręcznie. Aby to zrobić, należy umieścić kursor przed pierwszą wartością współrzędnych adresu (poziomo) w komórce lub pasku formuły. Ponadto, w układzie klawiatury angielskiej, kliknij przycisk "4" wielkimi literami (z wciśniętym klawiszem "Shift" ). Tam znajduje się symbol dolara. Następnie musisz wykonać tę samą procedurę z pionowymi współrzędnymi.

Jest szybszy sposób. Musisz umieścić kursor w komórce, w której znajduje się adres, i kliknąć przycisk funkcyjny F4. Następnie znak dolara natychmiast pojawi się jednocześnie przed poziomymi i pionowymi współrzędnymi podanego adresu.

Teraz przyjrzyjmy się, w jaki sposób bezwzględne adresowanie jest używane w praktyce, używając bezwzględnych odniesień.

Weź tabelę, która oblicza płace pracowników. Obliczenia dokonuje się poprzez pomnożenie wartości ich osobistych wynagrodzeń przez ustalony stosunek, który jest taki sam dla wszystkich pracowników. Sam współczynnik znajduje się w osobnej komórce arkusza. Mamy do czynienia z zadaniem obliczania wynagrodzeń wszystkich pracowników w najszybszy możliwy sposób.

Tabela obliczeń wynagrodzeń pracowniczych w programie Microsoft Excel

  1. Tak więc w pierwszej komórce kolumny "Wynagrodzenia" wprowadzamy wzór do mnożenia stawki odpowiedniego pracownika przez współczynnik. W naszym przypadku ta formuła ma następującą postać:

    =C4*G3

    2. Wzór do obliczania płac w programie Microsoft Excel

  2. Aby obliczyć wynik końcowy, kliknij przycisk Enter na klawiaturze. Wynik jest wyświetlany w komórce zawierającej formułę.

    3. Wynik obliczenia wynagrodzenia dla pierwszego pracownika w programie Microsoft Excel

  3. Obliczyliśmy wynagrodzenie dla pierwszego pracownika. Teraz musimy to zrobić dla wszystkich innych linii. Oczywiście operację można zapisać ręcznie w każdej komórce kolumny "Wages" , wprowadzając tę ​​samą formułę z poprawką do offsetu, ale mamy zadanie wykonywania obliczeń tak szybko, jak to możliwe, a ręczne wprowadzanie danych zajmie dużo czasu. I po co marnować wysiłek na ręczne wprowadzanie, jeśli formuła może być po prostu skopiowana do innych komórek?

    Aby skopiować formułę, używamy narzędzia, takiego jak znacznik wypełnienia. Stań się kursorem w prawym dolnym rogu komórki, w której się znajduje. W tym przypadku sam kursor powinien zostać przekształcony w ten sam znacznik wypełnienia w postaci krzyża. Zacisnąć lewy przycisk myszy i przeciągnąć kursor w dół na koniec stołu.

    4. Wypełniacz w programie Microsoft Excel

  4. Ale, jak widzimy, zamiast poprawnego wyliczenia płac dla innych pracowników, otrzymaliśmy jedno zera.

    5. Zero do obliczania płac w programie Microsoft Excel

  5. Patrzymy, w jaki powód takiego wyniku. Aby to zrobić, wybierz drugą komórkę w kolumnie "Wynagrodzenia" . Wyrażenie odpowiadające komórce jest wyświetlane na pasku formuły. Jak widać, pierwszy mnożnik ( C5 ) odpowiada stawce pracownika, którego oczekujemy. Przesunięcie współrzędnych w porównaniu z poprzednią komórką wynikało z właściwości względności. Jednak w tym konkretnym przypadku tego właśnie potrzebujemy. Dzięki temu pierwszym mnożnikiem była stawka wymaganego pracownika. Ale przesunięcie współrzędnych nastąpiło również z drugim czynnikiem. A teraz jego adres nie odnosi się do współczynnika ( 1,28 ), ale do pustej komórki poniżej.

    Skopiowana formuła w programie Microsoft Excel

    To właśnie spowodowało, że wyliczenie wynagrodzeń dla kolejnych pracowników z listy okazało się nieprawidłowe.

  6. Aby poprawić sytuację, musimy zmienić adresowanie drugiego czynnika względem względnego na stały. Aby to zrobić, wróć do pierwszej komórki w kolumnie "Wynagrodzenia" , zaznaczając ją. Następnie przechodzimy do paska formuły, gdzie wyświetlane jest wyrażenie, którego potrzebujemy. Wybierz drugi czynnik ( G3 ) za pomocą kursora i naciśnij klawisz funkcyjny na klawiaturze.

    7. Transformacja drugiego łącza mnożnika od względnego do bezwzględnego w Microsoft Excel

  7. Jak widać, wokół współrzędnych drugiego mnożnika pojawił się znak dolara, a to, jak pamiętamy, jest atrybutem adresowania absolutnego. Aby wyświetlić wynik na ekranie, naciśnij klawisz Enter .

    8. Drugi mnożnik ma adresowanie bezwzględne w programie Microsoft Excel

  8. Teraz, tak jak poprzednio, wywołujemy znacznik wypełnienia, umieszczając kursor w prawym dolnym rogu pierwszego elementu kolumny "Wynagrodzenia" . Zacisnąć lewy przycisk myszy i przeciągnąć go w dół.

    9. Wywoływanie uchwytu wypełnienia w programie Microsoft Excel

  9. Jak widać, w tym przypadku obliczenia zostały przeprowadzone prawidłowo, a kwota płac dla wszystkich pracowników przedsiębiorstwa została obliczona poprawnie.

    10. Wynagrodzenie jest obliczane poprawnie w programie Microsoft Excel

  10. Sprawdźmy, w jaki sposób została skopiowana formuła. Aby to zrobić, wybierz drugi element kolumny "Wages" . Spójrz na wyrażenie na pasku formuły. Jak widać, współrzędne pierwszego czynnika ( C5 ), który jest względny, przesunęły jeden pionowy punkt pionowy w porównaniu z poprzednią komórką. Ale drugi czynnik ( $ G $ 3 ), którego adresowanie poprawiliśmy, pozostał niezmieniony.

Skopiowana formuła w programie Microsoft Excel

W Excelu używa się tak zwanego mieszanego adresowania. W takim przypadku kolumna lub wiersz są ustalane w adresie elementu. Osiąga się to w ten sposób, że znak dolara jest umieszczony tuż przed jedną ze współrzędnych adresu. Oto przykład typowego linku mieszanego:

=A$1

Łączony link w Microsoft Excel

Ten adres jest również uważany za mieszany:

=$A1

Łączony link w Microsoft Excel

Oznacza to, że adresowanie bezwzględne w mieszanym łączu jest używane tylko dla jednej z wartości współrzędnych tych dwóch.

Zobaczmy, jak taki mieszany związek może być stosowany w praktyce na przykładzie tej samej tabeli wynagrodzeń dla pracowników przedsiębiorstwa.

  1. Jak widać, wcześniej zrobiliśmy tak, że wszystkie współrzędne drugiego mnożnika mają adresowanie absolutne. Ale zobaczmy, czy w tym przypadku konieczne jest ustalenie obu wartości? Jak widać, podczas kopiowania występuje przesunięcie pionowe, a w poziomie współrzędne pozostają niezmienione. Dlatego możliwe jest zastosowanie adresowania bezwzględnego tylko do współrzędnych ciągu i pozostawienie współrzędnych kolumny, ponieważ są one domyślnie - względne.

    Wybierz pierwszy element kolumny "Wynagrodzenia" i wykonaj powyższą operację w linii formuły. Otrzymujemy następującą formułę:

    =C4*G$3

    Jak widać, stałe adresowanie w drugim mnożniku jest stosowane tylko w odniesieniu do współrzędnych ciągu. Aby wyświetlić wynik w komórce, kliknij przycisk Enter .

    2. Adresowanie bezwzględne dotyczy tylko współrzędnych linii w programie Microsoft Excel

  2. Następnie za pomocą znacznika wypełnienia skopiuj tę formułę do zakresu komórek znajdujących się poniżej. Jak widać, lista płac dla wszystkich pracowników jest poprawnie wykonana.

    3. Obliczanie wynagrodzeń pracowników odbywało się prawidłowo przy użyciu mieszanego odniesienia w programie Microsoft Excel

  3. Zobacz, jak skopiowana formuła jest wyświetlana w drugiej komórce kolumny, powyżej której przeprowadziliśmy manipulację. Jak widać w linii wzoru, po wybraniu tego elementu arkusza, pomimo faktu, że adresowanie bezwzględne drugiego mnożnika ma tylko współrzędne linii, przesunięcie współrzędnych kolumny nie wystąpiło. Wynika to z tego, że kopiowaliśmy nie w poziomie, ale w pionie. Gdybyśmy skopiowali poziomo, w podobnym przypadku, musielibyśmy zrobić stałe adresowanie współrzędnych kolumny, a dla łańcuchów ta procedura byłaby opcjonalna.

Skopiowana formuła z mieszanym łączem w programie Microsoft Excel

Lekcja: Odniesienia bezwzględne i względne w Excelu

Metoda 2: Funkcja Alpha

Drugim sposobem organizowania adresowania bezwzględnego w arkuszu kalkulacyjnym Excel jest użycie operatora INDEX . Określona funkcja należy do grupy wbudowanych operatorów "References and arrays" . Jego zadaniem jest utworzenie odniesienia do określonej komórki z wynikiem wyniku do tego elementu arkusza, w którym znajduje się operator. W tym przypadku link jest dołączony do współrzędnych jeszcze silniejszy niż przy korzystaniu ze znaku dolara. Dlatego czasami zwyczajowo odnosić się do linków używających DVSSYL jako "superabsolute". To oświadczenie ma następującą składnię:

=ДВССЫЛ(ссылка_на_ячейку;[a1])

Funkcja ma dwa argumenty, z których pierwszy jest obowiązkowy, a drugi nie.

Argument "Link do komórki" jest linkiem do elementu arkusza Excel w formie tekstowej. Oznacza to, że jest to zwykłe odniesienie, ale ujęte w cudzysłów. Dzięki temu możliwe jest zapewnienie właściwości adresowania bezwzględnego.

Argument "a1" jest opcjonalny i jest używany w rzadkich przypadkach. Jego użycie jest konieczne tylko wtedy, gdy użytkownik wybierze alternatywną opcję adresowania, zamiast zwykłego używania współrzędnych typu "A1" (kolumny mają oznaczenie literowe, a linie mają numer cyfrowy). Alternatywą jest użycie stylu R1C1 , w którym kolumny, podobnie jak wiersze, są oznaczone liczbami. Przejdź do tego trybu pracy w oknie opcji programu Excel. Następnie, używając operatora INDEX , użyj wartości "FALSE" jako argumentu "a1 " . Jeśli pracujesz w normalnym trybie wyświetlania linków, tak jak większość innych użytkowników, możesz określić wartość "TRUE" jako argument "a1 " . Jednak ta wartość domyślnie jest domniemana, więc znacznie łatwiej jest nie określać w tym przypadku argumentu "a1" .

Przyjrzyjmy się, jak adresowanie bezwzględne, zorganizowane za pomocą funkcji DVSSYL , będzie działać na przykładzie naszej tabeli płac.

  1. Wybieramy pierwszy element kolumny "Wages" . Umieść znak "=" . Jak pamiętacie, pierwszy czynnik w powyższym wzorze obliczania wynagrodzenia musi być reprezentowany przez adres względny. Dlatego wystarczy kliknąć komórkę zawierającą odpowiednią wartość wynagrodzenia ( C4 ). Po wyświetleniu jego adresu w elemencie dla wyniku wyniku, kliknij przycisk "mnożenie" ( * ) na klawiaturze. Następnie musimy przejść do korzystania z operatora INDYWIDUALNEGO . Kliknij ikonę "Wstaw funkcję" .

    2. Przejdź do Kreatora funkcji w programie Microsoft Excel

  2. W otwartym oknie Wizards funkcji przejdź do kategorii "References and arrays" . Wśród przedstawionych nazwisk wyróżniamy nazwę "DVSSYL" . Następnie kliknij przycisk "OK" .

    3. Przejście do okna argumentów funkcji INDIRECT w programie Microsoft Excel

  3. Aktywacja okna argumentu operatora DVSSYL jest wykonywana . Składa się z dwóch pól odpowiadających argumentom tej funkcji.

    Umieść kursor w polu "Cell reference" . Wystarczy kliknąć ten element arkusza, w którym znajduje się współczynnik do obliczenia wynagrodzenia ( G3 ). Adres pojawi się natychmiast w polu argumentów. Gdybyśmy mieli do czynienia ze zwykłą funkcją, wówczas wprowadzenie adresu można uznać za kompletne, ale używamy funkcji " ALGORYTM" . Jak pamiętamy, adresy w nim muszą mieć formę tekstu. Dlatego też zawijamy współrzędne, które znajdują się w polu okna, w cudzysłowach.

    Ponieważ pracujemy w standardowym trybie wyświetlania współrzędnych, pole "A1" pozostaje puste. Klikamy przycisk "OK" .

    4. Okno argumentu funkcji INDIRECT w Microsoft Excel

  4. Aplikacja oblicza i wyświetla wynik w elemencie arkusza zawierającym formułę.

    5. Wynik obliczenia formuły za pomocą funkcji Alpha w programie Microsoft Excel

  5. Teraz kopiujemy tę formułę do wszystkich pozostałych komórek w kolumnie "Wages", używając znacznika wypełnienia, tak jak to robiliśmy wcześniej. Jak widać, wszystkie wyniki zostały obliczone poprawnie.

    6. Wynik całej kolumny oblicza się za pomocą formuły z funkcją Alpha w programie Microsoft Excel

  6. Zobaczmy, jak formuła jest wyświetlana w jednej z komórek, do których została skopiowana. Wybierz drugi element kolumny i spójrz na wiersz formuły. Jak widać, pierwszy mnożnik, który jest względnym odniesieniem, zmienił swoje współrzędne. W tym samym czasie argument drugiego czynnika, który jest reprezentowany przez funkcję ALGORYTMU , pozostał niezmieniony. W tym przypadku użyto techniki stałego adresowania.

Wyświetlanie skopiowanej formuły z funkcją INDIRECT w programie Microsoft Excel

Lekcja: Operator PRZEMYSŁU w Excelu

Adresowanie bezwzględne w tabelach Excela można zapewnić na dwa sposoby: używając funkcji INDEX i używając bezwzględnych odniesień. W tym przypadku funkcja zapewnia bardziej sztywne powiązanie z adresem. Częściowe adresowanie bezwzględne może być również używane w przypadku używania łączy mieszanych.