Względne i bezwzględne odwołanie do komórki i formatowanie

W tej lekcji omawiamy odwołania do komórek, jak kopiować lub przenosić formułę i formatować komórki. Na początek wyjaśnijmy, co rozumiemy przez odwołania do komórek, które stanowią podstawę dużej mocy i wszechstronności formuł i funkcji. Dokładne zrozumienie działania odwołań do komórek pozwoli Ci w pełni wykorzystać możliwości arkuszy kalkulacyjnych Excel!

NAWIGACJA SZKOLNA

1. Dlaczego potrzebujesz formuł i funkcji??
2. Definiowanie i tworzenie formuły
3. Względne i bezwzględne odwołanie do komórki i formatowanie
4. Przydatne funkcje, które powinieneś poznać
5. Odnośniki, wykresy, statystyki i tabele przestawne

Uwaga: zakładamy, że już wiesz, że komórka jest jednym z kwadratów w arkuszu kalkulacyjnym, ułożonymi w kolumny i wiersze, do których odnoszą się litery i liczby biegnące poziomo i pionowo.

Co to jest odwołanie do komórki?

"Odniesienie do komórki" oznacza komórkę, do której odnosi się inna komórka. Na przykład, jeśli w komórce A1 masz = A2. Następnie A1 oznacza A2.

Przyjrzyjmy się temu, co powiedzieliśmy w lekcji 2 o wierszach i kolumnach, abyśmy mogli dalej badać odwołania do komórek.

Komórki w arkuszu kalkulacyjnym są określane wierszami i kolumnami. Kolumny są pionowe i oznaczone literami. Wiersze są poziome i oznaczone numerami.

Pierwsza komórka w arkuszu kalkulacyjnym to A1, co oznacza kolumnę A, wiersz 1, B3 odnosi się do komórki znajdującej się w drugiej kolumnie, trzecim wierszu itd..

W celach edukacyjnych dotyczących odwołań do komórek, czasami będziemy zapisywać je jako wiersz, kolumnę, to nie jest poprawna notacja w arkuszu kalkulacyjnym i ma ona po prostu na celu uczynienie rzeczy bardziej przejrzystymi.

Rodzaje odniesień do komórek

Istnieją trzy typy odwołań do komórek.

Bezwzględny - Oznacza to, że odwołanie do komórki pozostaje takie samo, jeśli skopiujesz lub przeniesiesz komórkę do dowolnej innej komórki. Odbywa się to poprzez zakotwiczenie wiersza i kolumny, więc nie zmienia się po skopiowaniu lub przeniesieniu.

Względna - Względne odwołanie oznacza, że ​​adres komórki zmienia się podczas kopiowania lub przenoszenia; tzn. odwołanie do komórki odnosi się do jego lokalizacji.

Mieszane - Oznacza to, że możesz zakotwiczyć wiersz lub kolumnę podczas kopiowania lub przesuwania komórki, aby jedna z nich uległa zmianie, a druga nie. Na przykład można zakotwiczyć odniesienie do wiersza, a następnie przenieść komórkę w dół o dwa wiersze i cztery kolumny, a odwołanie do wiersza pozostaje takie samo. Wyjaśnimy to dalej poniżej.

Relatywne odniesienia

Odwołajmy się do tego wcześniejszego przykładu - przypuśćmy, że w komórce A1 mamy formułę, która po prostu mówi = A2. Oznacza to, że wynik Excela w komórce A1 jest taki, jaki jest wprowadzany do komórki A2. W komórce A2 wpisaliśmy "A2", więc Excel wyświetli wartość "A2" w komórce A1.

Załóżmy teraz, że musimy zrobić miejsce w naszym arkuszu kalkulacyjnym, aby uzyskać więcej danych. Musimy dodać kolumny powyżej i wiersze po lewej stronie, więc musimy przenieść komórkę w dół i w prawo, aby zrobić miejsce.

W miarę przesuwania komórki w prawo wzrasta liczba kolumn. Po przesunięciu w dół liczba wierszy wzrasta. Zmienia się także komórka, na którą wskazuje punkt odniesienia komórki. Zilustrowano to poniżej:

Kontynuując nasz przykład i patrząc na grafikę poniżej, jeśli skopiujesz zawartość komórki A1 dwa na prawo i cztery na dół, przeniesiesz ją do komórki C5.

Skopiowaliśmy komórkę dwie kolumny w prawo i cztery w dół. Oznacza to, że zmieniliśmy komórkę, która odnosi się do dwóch i czterech do dołu. A1 = A2 to teraz C5 = C6. Zamiast odnosić się do A2, teraz komórka C5 odnosi się do komórki C6.

Wyświetlana wartość to 0, ponieważ komórka C6 jest pusta. W komórce C6 wpisujemy "Jestem C6", a teraz C5 wyświetla "Jestem C6".

Przykład: formuła tekstu

Spróbujemy innego przykładu. Pamiętasz z lekcji 2, w której musieliśmy podzielić imię i nazwisko na imię i nazwisko? Co się stanie, gdy skopiujemy tę formułę?

Napisz wzór = PRAWO (A3, LEN (A3) - ZNAJDŹ (",", A3) - 1) lub skopiuj tekst do komórki C3. Nie kopiuj rzeczywistej komórki, tylko tekst, skopiuj tekst, w przeciwnym razie zaktualizuje odniesienie.

Możesz edytować zawartość komórki na górze arkusza kalkulacyjnego w polu obok, gdzie jest napisane "fx". To pole jest dłuższe niż szerokość komórki, więc łatwiej jest edytować.

Teraz mamy:

Nic skomplikowanego, właśnie napisaliśmy nową formułę w komórce C3. Teraz skopiuj C3 do komórek C2 i C4. Obserwuj wyniki poniżej:

Teraz mamy imiona Alexandra Hamiltona i Thomasa Jeffersona.

Użyj kursora, aby podświetlić komórki C2, C3 i C4. Ustaw kursor na komórce B2 i wklej zawartość. Zobacz, co się stało - pojawia się błąd: "#REF". Dlaczego tak jest?

Kiedy skopiowaliśmy komórki z kolumny C do kolumny B, zaktualizowano pierwszą kolumnę odniesienia w lewo = PRAWO (A2, LEN (A2) - FIND (",", A2) - 1).

Zmienił on każde odwołanie do A2 na kolumnę po lewej stronie A, ale nie ma kolumny po lewej stronie kolumny A. Więc komputer nie wie, co masz na myśli.

Nowa formuła w B2 na przykład to = PRAWO (#REF!, LEN (#REF!) - FIND (",", # REF!) - 1), a wynikiem jest # REF:

Kopiowanie formuły do ​​zakresu komórek

Kopiowanie komórek jest bardzo przydatne, ponieważ można napisać jedną formułę i skopiować ją do dużego obszaru, a odniesienie jest aktualizowane. Pozwala to uniknąć konieczności edytowania każdej komórki, aby upewnić się, że wskazuje właściwe miejsce.

Przez "zasięg" rozumiemy więcej niż jedną komórkę. Na przykład (C1: C10) oznacza wszystkie komórki od komórki C1 do komórki C10. Jest to więc kolumna komórek. Inny przykład (A1: AZ1) to górny wiersz z kolumny A do kolumny AZ.

Jeśli zakres przekracza pięć kolumn i dziesięć wierszy, możesz wskazać zakres, pisząc lewą górną i dolną prawą, np. A1: E10. Jest to kwadratowy obszar, który przecina wiersze i kolumny, a nie tylko część kolumny lub część wiersza.

Oto przykład ilustrujący sposób kopiowania jednej komórki do wielu lokalizacji. Załóżmy, że chcemy wyświetlić nasze przewidywane wydatki za miesiąc w arkuszu kalkulacyjnym, abyśmy mogli wykonać budżet. Tworzymy taki arkusz kalkulacyjny:

Teraz skopiuj formułę w komórce C3 (= B3 + C2) do reszty kolumny, aby uzyskać bilans bieżący dla naszego budżetu. Program Excel aktualizuje odwołanie do komórki podczas jej kopiowania. Wynik pokazano poniżej:

Jak widać, każda nowa komórka jest aktualizowana krewny do nowej lokalizacji, więc komórka C4 aktualizuje swoją formułę do = B4 + C3:

Aktualizacja komórki C5 = B5 + C4, i tak dalej:

Absolutne odniesienia

Odniesienie bezwzględne nie zmienia się po przeniesieniu lub skopiowaniu komórki. Używamy znaku $, aby uzyskać bezwzględne odniesienie - aby to pamiętać, pomyśl o znaku dolara jako kotwicy.

Na przykład wprowadź formułę = $ A $ 1 w dowolnej komórce. Wartość $ przed kolumną A oznacza, że ​​kolumna nie zmienia się, znak $ przed wierszem 1 oznacza, że ​​nie zmienia się kolumny podczas kopiowania lub przenoszenia komórki do innej komórki.

Jak widać w przykładzie poniżej, w komórce B1 mamy względne odniesienie = A1. Kiedy skopiujemy B1 do czterech komórek poniżej, względne odniesienie = A1 zmienia się w komórkę po lewej, więc B2 staje się A2, B3 stają się A3 itd. Te komórki oczywiście nie mają wprowadzonej wartości, więc wynik wynosi zero.

Jeśli jednak użyjemy = $ A1 $ 1, np. W C1 i skopiujemy go do czterech komórek poniżej, odniesienie jest bezwzględne, więc nigdy się nie zmienia, a wynik jest zawsze równy wartości w komórce A1.

Załóżmy, że śledzisz swoje zainteresowanie, na przykład w poniższym przykładzie. Wzór w C4 = B4 * B1 to "stopa procentowa" * "bilans" = "odsetki rocznie".

Teraz zmieniłeś budżet i zaoszczędziłeś dodatkowe 2 000 $ na zakup funduszu inwestycyjnego. Załóżmy, że jest to fundusz o stałym oprocentowaniu i płaci tę samą stopę procentową. Wprowadź nowe konto i zrównoważyć w arkuszu kalkulacyjnym, a następnie skopiuj formułę = B4 * B1 z komórki C4 do komórki C5.

Nowy budżet wygląda następująco:

Nowy fundusz inwestycyjny zarabia 0 dolarów rocznie, co nie może być właściwe, ponieważ stopa procentowa wynosi wyraźnie 5 procent.

Excel podświetla komórki, do których odwołuje się formuła. Powyżej widać, że odniesienie do stopy procentowej (B1) zostaje przeniesione do pustej komórki B2. Powinniśmy odnieść się do absolutu B1, pisząc $ B $ 1 za pomocą znaku dolarów, aby zakotwiczyć odniesienie do wiersza i kolumny.

Przepisz pierwsze obliczenia w C4, aby odczytać = B4 * $ B $ 1, jak pokazano poniżej:

Następnie skopiuj tę formułę z C4 do C5. Arkusz kalkulacyjny wygląda teraz tak:

Ponieważ skopiowaliśmy komórkę z formułą 1, tzn. Zwiększamy wiersz o jeden, nowa formuła to = B5 * $ B $ 1. Stopa procentowa funduszu inwestycyjnego jest teraz obliczana prawidłowo, ponieważ stopa procentowa jest zakotwiczona w komórce B1.

Jest to dobry przykład tego, kiedy można użyć "nazwy" w odniesieniu do komórki. Nazwa jest absolutnym odniesieniem. Na przykład, aby przypisać nazwę "stopa procentowa" do komórki B1, kliknij komórkę prawym przyciskiem myszy, a następnie wybierz "Zdefiniuj nazwę".

Nazwy mogą odnosić się do jednej komórki lub zakresu i możesz użyć nazwy w formule, na przykład = interest_rate * 8 to to samo, co pisanie = $ B $ 1 * 8.

Mieszane odniesienia

Mieszane odniesienia to czas zarówno rząd lub kolumna jest zakotwiczona.

Załóżmy na przykład, że jesteś rolnikiem, który tworzy budżet. Jesteś właścicielem sklepu z artykułami paszowymi i sprzedajesz nasiona. Zamierzasz zasadzić kukurydzę, soję i lucerny. Poniższy arkusz pokazuje koszt na akr. "Koszt za akr" = "cena za funt" * "funty nasion na akr" - tyle kosztuje sadzenie akra.

Wprowadź koszt na akr jako = B2 B2 * C2 w komórce D2. Mówisz, że chcesz zakotwiczyć cenę za kolumnę funta. Następnie skopiuj tę formułę do innych wierszy w tej samej kolumnie:

Teraz chcesz poznać wartość swojego ekwipunku nasion. Potrzebujesz ceny za funt i liczbę funtów w ekwipunku, aby poznać wartość zapasów.

Dodajemy dwie kolumny: "funt nasion w ekwipunku", a następnie "wartość zapasów". Teraz skopiuj komórkę D2 do F4 i zauważ, że odwołanie do wiersza w pierwszej części oryginalnej formuły ($ B2) zostało zaktualizowane do wiersza 4 ale kolumna pozostaje stała, ponieważ $ zakotwicza ją do "B."

Jest to mieszane odniesienie, ponieważ kolumna jest bezwzględna, a wiersz jest względny.

Circular References

Cykliczne odniesienie występuje wtedy, gdy formuła odnosi się do samej siebie.

Na przykład nie można pisać c3 = c3 + 1. Ten rodzaj obliczeń nazywa się "iteracją", co oznacza, że ​​się powtarza. Excel nie obsługuje iteracji, ponieważ oblicza wszystko tylko jeden raz.

Jeśli spróbujesz to zrobić, wpisując SUM (B1: B5) w komórce B5:

Pojawi się ekran ostrzegawczy:

Program Excel mówi tylko, że w dolnej części ekranu znajduje się okólnik, więc możesz tego nie zauważyć. Jeśli masz odwołanie do okólnika i zamknij arkusz kalkulacyjny i otwórz go ponownie, program Excel powie Ci w wyskakującym okienku, że masz odwołanie cykliczne.

Jeśli masz odwołanie cykliczne, przy każdym otwarciu arkusza kalkulacyjnego program Excel poinformuje cię o tym wyskakującym okienku z okólnikiem.

Odniesienia do innych arkuszy roboczych

"Zeszyt ćwiczeń" to zbiór "arkuszy roboczych". Po prostu oznacza to, że możesz mieć wiele arkuszy kalkulacyjnych (arkuszy roboczych) w tym samym pliku Excel (skoroszycie). Jak widać w poniższym przykładzie, nasz przykładowy skoroszyt ma wiele arkuszy (na czerwono).

Arkusze robocze są domyślnie nazwane Arkusz1, Arkusz2 i tak dalej. Utworzysz nowy klikając "+" u dołu ekranu Excel.

Możesz zmienić nazwę arkusza roboczego na coś użytecznego, np. "Pożyczkę" lub "budżet", klikając prawym przyciskiem myszy kartę arkusza roboczego u dołu ekranu programu Excel, wybierając zmianę nazwy i wpisując nową nazwę.

Możesz też dwukrotnie kliknąć zakładkę i zmienić jej nazwę.

Składnia odwołania do arkusza roboczego to = workheet! Cell. Tego rodzaju referencji można używać, gdy ta sama wartość jest używana w dwóch arkuszach roboczych, na przykład:

• Dzisiejsza data
• Kurs wymiany walut z dolarów na euro
• Wszystko, co dotyczy wszystkich arkuszy w skoroszycie

Poniżej znajduje się przykład arkusza kalkulacyjnego "zainteresowanie", w odniesieniu do arkusza roboczego "pożyczka", komórka B1.

Jeśli spojrzymy na arkusz "pożyczki", widzimy odniesienie do kwoty pożyczki:

Już idę dalej ...

Mamy nadzieję, że masz teraz mocną znajomość odniesień do komórek, w tym względną, absolutną i mieszaną. Na pewno jest dużo.

To wszystko na dzisiejszą lekcję, w Lekcji 4 omówimy kilka przydatnych funkcji, które możesz chcieć poznać w codziennym korzystaniu z Excela.