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. Konkretne zrozumienie działania odwołań do komórek pozwoli Ci w pełni wykorzystać możliwości arkuszy kalkulacyjnych Excel!
NAWIGACJA SZKOLNAUwaga: 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.
"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.
Pierwszą komórką w arkuszu kalkulacyjnym jest A1, co oznacza kolumnę A, wiersz 1, B3 odnosi się do komórki znajdującej się w drugiej kolumnie, trzecim wierszu i tak dalej.
W celach edukacyjnych dotyczących odwołań do komórek czasami będziemy zapisywać je jako wiersz, kolumnę, nie jest to poprawna notacja w arkuszu kalkulacyjnym i ma na celu tylko uczynienie rzeczy bardziej przejrzystymi.
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ędny - Względne odwołanie oznacza, że adres komórki zmienia się podczas kopiowania lub przenoszenia; tj. odwołanie do komórki odnosi się do jego lokalizacji.
Mieszane - Oznacza to, że możesz zdecydować się na zakotwiczenie wiersza lub kolumny podczas kopiowania lub przesuwania komórki, aby jedna uległa zmianie, a druga nie. Na przykład można zakotwiczyć odniesienie do wiersza, a następnie przenieść komórkę w dół w dwóch wierszach i w czterech kolumnach, a odwołanie do wiersza pozostaje takie samo. Wyjaśnimy to dalej poniżej.
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świetla wartość "A2" w komórce A1.
Załóżmy teraz, że musimy zrobić więcej miejsca 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, po skopiowaniu zawartości komórki A1 dwa po prawej i cztery w dół przeniesiono 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 "I am C6", a teraz C5 wyświetla "I am C6".
Spróbujemy innego przykładu. Pamiętasz z lekcji 2, w której musieliśmy podzielić imię i nazwisko na imię i nazwisko? Co stanie się, gdy skopiujemy tę formułę?
Napisz wzór = PRAWY (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. Komputer nie wie, co masz na myśli.
Nowa formuła na przykład w B2 to: PRAWY (#REF!, LEN (#REF!) - FIND (",", # REF!) - 1), a wynikiem jest # REF:
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. Tak więc jest to 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. 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 C5 komórki do = B5 + C4, i tak dalej:
Odniesienie bezwzględne nie zmienia się po przeniesieniu lub skopiowaniu komórki. Używamy znaku $, aby uzyskać absolutne 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 nie zmienia się kolumny, $ 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, tak jak 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. Formuła 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 wyważ saldo do arkusza kalkulacyjnego, 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 formułę jednej komórki w dół, tj. Zwiększamy wiersz o jeden, nowa formuła to = B5 * $ B $ 1. Oprocentowanie funduszy wspólnego inwestowania jest teraz obliczane poprawnie, 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 bezwzględnym 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 zapis = $ B 1 $ 8 *.
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 kalkulacyjny 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) jest aktualizowane 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.
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 odniesienie kołowe i zamknij arkusz kalkulacyjny i otwórz go ponownie, program Excel poinformuje 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, do którego masz odwołanie cykliczne.
"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 zawiera wiele arkuszy roboczych (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, co może być następujące:
Poniżej znajduje się przykład arkusza kalkulacyjnego "zainteresowanie", w odniesieniu do arkusza kalkulacyjnego "pożyczka", komórka B1.
Jeśli spojrzymy na arkusz "pożyczki", widzimy odniesienie do kwoty pożyczki:
Mamy nadzieję, że teraz masz solidną znajomość odniesień do komórek, w tym względne, bezwzględne i mieszane. 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.
