Firma Aktualności Produkty Projekty Teksty Muzeum Pobierz Kontakt Mapa RSS English
YAC Software Teksty Excel Odwołania do komórek w Excelu Spelunka Trybików
  Wróć

Spis

Dane

Excel

Badania mediów

Badania rynku

Kwoty respondentów

SPSS

VBA

YAC Data Language

Odwołania do komórek w Excelu
Dziś chciałbym przedstawić dwa ćwiczenia z odwołań do komórek w formułach Excela.

Ćwiczenie 1: oblicz procenty mając dane liczebności

No dobra, załóżmy, że mamy listę odpowiedzi, tekstów, itd. (kolumna A) z odpowiadającymi im liczbami wystąpień (kolumna B):

AB
1elem112
2elem210
3elem3 2
4elem4 3
5elem515

I chcemy policzyć, dla każdego elementu listy, odpowiadający mu procent całkowitej liczby wystąpień:

ABC
1elem11229%
2elem21024%
3elem3 2 5%
4elem4 3 7%
5elem51536%

Pierwsza część jest prosta - do obliczenia procentów potrzebujemy sumy liczby wystąpień. Formuła =SUMA(B1:B5) obliczy tę sumę dla wszystkich elementów. Następnie, można skorzystać z tej formuły, w kolumnie C, do obliczenia procentów (dzieląc kolumnę B przez sumę)... lub prawie:

ABC
1elem112 29%
2elem210 33%
3elem3 2 10%
4elem4 3 17%
5elem515100%

Gdy wpiszemy odpowiednią formułę w komórce C1 i następnie skorzystamy z kopiowania i wklejania, lub przeciągnimy wzór aby wypełnił wszystkie 5 celek w kolumnie C (lub skorzystamy ze skrótu Ctrl+D), Excel automatycznie zwiększy referencje w nowo wygenerowanych formułach. Zatem, formułą faktycznie zdefiniowaną w wierszu 5 będzie =C5/SUMA(B5:B9) - a nie tego chcemy.

Tego typu referencje do komórek nazwiemy referencjami względnymi, tj. referencjami, które będą aktualizowane przy kopiowaniu formuł z jednej komórki do drugiej. Z drugiej strony, Excel pozwala też na definiowanie referencji bezwzględnych, tj. takich, które nie są zmieniane podczas kopiowania. Zwróćmy jednak uwagę, że w formule powyżej, licznik powinien być odwołaniem względnym (w wierszu 2 chcemy mieć C2), ale w mianowniku bezwzględnym - B1:B5 we wszystkich wierszach.

Odwołania względne piszemy po prostu jako ID kolumny + numer wiersza (np. C3). W takiej referencji można jednak zamienić odwołanie kolumny na odwołanie absolutne, odwołanie wiersza na odwołanie absolutne, lub oba odwołania na odwołania absolutne. Ten wymiar, który ma być referencją absolutną trzeba poprzedzić znakiem dolara. I tak, C$3 działa w ten sposób, że przy kopiowaniu, odwołanie do kolumny (C) będzie aktualizowane, a odwołanie do wiersza (3) pozostanie bez zmian.

W tym momencie rozwiązanie problemu procentów powinno być już jasne. Przed kopiowanie formuł, formułę =C1/SUMA(B1:B5) należy zmienić na =C1/SUMA(B$1:B$5), zmieniając referencje w sumach na referencje absolutne - w wierszu 5 otrzymamy teraz formułę =C5/SUMA(B$1:B$5). W sumie można byłoby też zmienić referencje do kolumn na referencje absolutne i nadal wzory byłyby definiowane poprawnie.

Jednak można bardzo łatwo popełnić błąd definiując tego typu formuły (a ta jest szczególnie prosta). Dobrze jest więc wprowadzić dodatkowe testy sprawdzające poprawność naszych obliczeń, nawet w tak prostych przypadkach jak ten. Tutaj, dobrym testem byłaby suma wszystkich procentów - dodajmy formułę =SUMA(C1:C5) w wierszu 6 - powinniśmy otrzymać 100%:

ABC
1elem112 29%
2elem210 24%
3elem3 2 5%
4elem4 3 7%
5elem515 36%
6suma100%



Ćwiczenie 2: tabliczka mnożenia

Bardzo fajnym ćwiczeniem do przetrenowania odwołań względnych i bezwzględnych jest tabliczka mnożenia:

A B C D E F
1   1 2 3 4 5
21     
32     
43     
54     
65     

Przed wpisaniem formuł zauważ, że:
  • zawsze odwołujesz się do pierwszego wiersza w kolejnych wierszach,
  • zawsze odwołujesz się do pierwszej kolumny w kolejnych kolumnach.
Mając powyższe na uwadze, potrzebna formuła powinna przyjść w miarę łatwo. Dla tych, którzy nie cierpią ćwiczeń bez rozwiązań (i ja do nich należę), formuła, którą należy wprowadzić do pierwszej komórki (B2) i skopiować do innych to... =$A2*B$1.

I voila!

A B C D E F
1   1 2 3 4 5
211 2 3 4 5
322 4 6 810
433 6 91215
544 8121620
65510152025

Miłej zabawy!



Notka na temat terminologii

W przykładach powyżej odwołania do komórek nazywaliśmy bezwzględnymi i względnymi. Ale C3 przecież wygląda na odwołanie bezwzględne - wskazuje konkretną komórkę niezależnie, w której komórce jesteśmy, więc dlaczego nazywamy to odwołaniem względnym?

Tak naprawdę, odwołania do komórek trzymane są przez Excela w nieco innym formacie: WnKm (wiersz n, kolumna m). (Można nawet korzystać z takich odwołań w Excelu o ile przestawi się odpowiednią opcję w oknie dialogowym opcji: na zakładce Ogóle należy zaznaczyć opcję Styl odwołania W1K1).

Są dwa formaty odwołań do wierszy / kolumn za pomocą indeksów
  • Wn (lub Kn) to odwołanie do numeru (bezwzględnego) wiersza / kolumny; tutaj, n musi być większe od 0.
  • W[n] (lub K[n]) to odwołanie do numeru wiersza / kolumny względem aktualnego wiersza / kolumny; tutaj, n może być i większe, i mniejsze od 0. Może też być równe 0 (lub po prostu W lub K) w celu odwołania się do aktualnego wiersza / kolumny.
Zatem, odwołanie $C$3 jest zamieniane wewnętrznie na W3K3 i jest to odwołanie bezwzględne.

Natomiast C3 zostanie zamienione na... Właśnie, nie wiemy na co - to zależy od komórki, w której ta formuła jest. Jeżeli jesteśmy w $C$3, wtedy C3 zostaje zamienione na W[0]K[0] (a w zasadzie na WK). Jeżeli jesteśmy w $D$2, wtedy C3 zostaje zamienione na R[-1]C[1].

Wtedy, gdy skopiujemy C3 z komórki $D$2 do komórki $F$1, nadal jest to rozumiane jako W[-1]K[1], stąd odwołanie względne zostaje zamienione z C3 na E2.

Góra

Komentarze
Kurczę!
Na razie brak komentarzy...

Góra

Dodaj komentarz (pola z gwiazdką są obowiązkowe)
Imię / ksywa *
Mail (pozostanie ukryty) *
Twoja strona
Komentarz (bez tagów) *
Wpisz tekst wyświetlony poniżej *
 

Góra

Tagi

Excel


Podobne strony

Usuwanie labelek małych wartości z wykresów Excela

Wyszukiwanie wartości w tablicach 2D

Otwórz wszystkie i połącz