Omówienie formuł w programie Excel

Formuła może także zawierać dowolne lub wszystkie z następujących składników: funkcje, odwołania, operatory i stałe.

Składniki formuły   

1. Funkcje: funkcja PI() zwraca wartość liczby pi: 3,142...

2. Odwołania: A2 zwraca wartość w komórce A2.

3. Stałe: wartości liczbowe lub tekstowe wprowadzane bezpośrednio w formule, na przykład liczba 2.

4. Operatory: operator ^ (daszek) podnosi liczbę do potęgi, a operator * (gwiazdka) mnoży liczby.

Stała to wartość, która nie jest obliczana i jest zawsze taka sama. Na przykład data 2008-10-09, liczba 210 i tekst „Zyski kwartalne” są stałymi. wyrażenie lub wartość wynikająca z wyrażenia nie jest stałą. Jeśli w formule zamiast odwołań do komórek są używane stałe (na przykład =30+70+110), to wynik zmieni się tylko wtedy, gdy zostanie zmodyfikowana formuła. Najlepiej jest umieścić stałe w oddzielnych komórkach, gdzie mogą być łatwo zmieniane według potrzeb, a następnie użyć odwołań do tych komórek w formułach.

Odwołanie identyfikuje komórkę lub zakres komórek w arkuszu i instruuje program Excel, gdzie ma szukać wartości lub danych, które mają zostać użyte w formule. Korzystając z odwołań, można używać w jednej formule danych znajdujących się w różnych częściach arkusza lub używać wartości z jednej komórki w wielu formułach. Można też odwoływać się do komórek w innych arkuszach tego samego skoroszytu i do innych skoroszytów. Odwołania do komórek w innych skoroszytach są określane jako linki lub odwołania zewnętrzne.

• Styl odwołania A1

  Domyślnie program Excel używa stylu odwołania A1, w którym odwołania do kolumn wyraża się za pomocą liter (od A do XFD — razem 16 384 kolumny), a do wierszy za pomocą cyfr (od 1 do 1 048 576). Te litery i liczby są określane jako nagłówki wierszy i kolumn. Aby odwołać się do komórki, należy wprowadzić literę kolumny, po której następuje numer wiersza. Na przykład odwołanie B2 oznacza komórkę na przecięciu kolumny B i wiersza 2.

  Aby odwołać się do	Zapis
  Komórki w kolumnie A i w wierszu 10	A10
  Zakresu komórek w kolumnie A i w wierszach od 10 do 20	A10:A20
  Zakresu komórek w wierszu 15 i w kolumnach od B do E	B15:E15
  Wszystkich komórek w wierszu 5	5:5
  Wszystkich komórek w wierszach od 5 do 10	5:10
  Wszystkich komórek w kolumnie H	H:H
  Wszystkich komórek w kolumnach od H do J	H:J
  Zakresu komórek w kolumnach od A do E i w wierszach od 10 do 20	A10:E20

• Odwoływanie się do komórki lub zakresu komórek z innego arkusza tego samego skoroszytu

  W poniższym przykładzie funkcja ŚREDNIA została użyta do obliczenia wartości średniej dla zakresu B1:B10 w arkuszu o nazwie Marketing w tym samym skoroszycie.

  

  1. Odwołanie do arkusza o nazwie Marketing

  2. Odwołanie do zakresu komórek od B1 do B10

  3. Wykrzyknik (!) oddziela odwołanie do arkusza od odwołania do zakresu komórek

  Uwaga: Jeśli arkusz, do którego prowadzi odwołanie, zawiera spacje lub liczby, należy dodać apostrofy (') przed nazwą arkusza i po niej, na przykład ='123'! A1 lub ='Przychody ze stycznia'! A1.

• Różnice między odwołaniami względnymi, bezwzględnymi i mieszanymi

  1. Odwołania względne    Odwołanie względne do komórki w formule, takie jak A1, ma za podstawę względne pozycje komórki zawierającej formułę i komórki, do której następuje odwołanie. Gdy zmienia się pozycja komórki zawierającej formułę, zmienia się też odwołanie. Gdy formuła jest kopiowana w wierszach lub kolumnach, odwołanie jest automatycznie dopasowywane. Domyślnie nowe formuły używają odwołań względnych. Na przykład odwołanie względne po skopiowaniu z komórki B2 do komórki B3 automatycznie zmieni się z =A1 na =A2.

     Kopiowana formuła z odwołaniem względnym   

     

  2. Odwołania bezwzględne    Odwołanie bezwzględne w formule, takie jak $A$1, zawsze odwołuje się do komórki w określonej lokalizacji. Gdy zmienia się pozycja komórki zawierającej formułę, odwołanie bezwzględne pozostaje niezmienione. Gdy formuła jest kopiowana w wierszach lub kolumnach, odwołanie bezwzględne nie jest dopasowywane. Domyślnie nowe formuły używają odwołań względnych, aby więc stały się one odwołaniami bezwzględnymi, trzeba je odpowiednio zmienić. Jeśli na przykład odwołanie bezwzględne zostanie skopiowane z komórki B2 do komórki B3, obie komórki będą zawierały to samo odwołanie: =$A$1.

     Kopiowana formuła z odwołaniem bezwzględnym   

     

  3. Odwołania mieszane    Odwołanie mieszane ma albo bezwzględne odwołanie do kolumny i względne do wiersza, albo bezwzględne do wiersza i względne do kolumny. Bezwzględne odwołanie do kolumny przybiera postać $A1, $B1 i tak dalej. Bezwzględne odwołanie do wiersza przybiera postać A$1, B$1 i tak dalej. Gdy zmieni się pozycja komórki zawierającej formułę, zmieni się odwołanie względne, odwołanie bezwzględne zaś nie zmieni się. Gdy formuła zostanie skopiowana w wierszach lub w kolumnach, odwołanie względne zostanie automatycznie dopasowane, odwołanie bezwzględne zaś nie zostanie dopasowane. Na przykład po skopiowaniu odwołania mieszanego z komórki A2 do komórki B3 nastąpi dopasowanie odwołania z =A$1 na =B$1.

     Kopiowana formuła z odwołaniem mieszanym   

     

• Styl odwołania 3-W

  Wygodne odwoływanie się do wielu arkuszy    Aby analizować dane w tej samej komórce lub zakresie komórek w wielu arkuszach w skoroszycie, należy użyć odwołania 3-W. Odwołanie 3-W zawiera odwołanie do komórki lub zakresu poprzedzone zakresem nazw arkuszy. Program Excel użyje wszystkich arkuszy przechowywanych między początkową a końcową nazwą odwołania. Na przykład formuła =SUMA(Arkusz2:Arkusz13!B5) dodaje wszystkie wartości zawarte w komórce B5 we wszystkich arkuszach między Arkuszem 2 a Arkuszem 13, z tymi dwoma arkuszami włącznie.

  • Odwołań 3-W można używać w celu odwoływania się do komórek w innych arkuszach, definiowania nazw, a także tworzenia formuł za pomocą następujących funkcji: SUMA, ŚREDNIA, ŚREDNIA.A, ILE.LICZB, ILE.NIEPUSTYCH, MAX, MAX.A, MIN, MIN.A, ILOCZYN, ODCH.STAND.POPUL, ODCH.STANDARD.PRÓBKI, ODCH.STANDARDOWE.A, ODCH.STANDARD.POPUL.A, WARIANCJA.POP, WARIANCJA.PRÓBKI, WARIANCJA.A oraz WARIANCJA.POPUL.A.

  • Odwołań 3-W nie można używać w formułach tablicowych.

  • Odwołania 3-W nie mogą być używane z elementem operator przecięcia (spacją) ani w formułach, w których zastosowano przecięcie pośrednie.

  Co się stanie po przeniesieniu, skopiowaniu, wstawieniu lub usunięciu arkuszy    W poniższych przykładach wyjaśniono, co się dzieje w przypadku przenoszenia, kopiowania, wstawiania lub usuwania arkuszy objętych odwołaniem 3-W. W przykładach jest używana formuła =SUMA(Arkusz2:Arkusz6!A2:A5) w celu sumowania komórek od A2 do A5 w arkuszach od 2 do 6.

  • Wstawianie lub kopiowanie    Jeśli w skoroszycie między arkuszami Arkusz2 i Arkusz6 (punkty końcowe w tym przykładzie) są wstawiane lub kopiowane arkusze, program Excel obejmuje obliczeniami wszystkie wartości z komórek od A2 do A5 znajdujących się w dodanych arkuszach.

  • Usuwanie     Jeśli między arkuszami Arkusz2 i Arkusz6 w skoroszycie zostaną usunięte jakieś arkusze, program Microsoft Excel usunie ich wartości z obliczeń.

  • Przenoszenie    Jeśli arkusze znajdujące się między arkuszami Arkusz2 i Arkusz6 zostaną przeniesione do lokalizacji poza zakresem arkuszy objętym odwołaniem, program Microsoft Excel usunie z obliczeń wartości pochodzące z tych arkuszy.

  • Przenoszenie punktu końcowego    Jeśli arkusze Arkusz2 lub Arkusz6 zostaną przeniesione do innej lokalizacji w tym samym skoroszycie, program Microsoft Excel skoryguje obliczenia stosownie do nowego zakresu arkuszy zawartego między nimi.

  • Usuwanie punktu końcowego    Jeśli arkusze Arkusz2 lub Arkusz6 zostaną usunięte, program Microsoft Excel skoryguje obliczenia stosownie do zakresu arkuszy zawartego między nimi.

• Styl odwołania W1K1

  Można również używać stylu odwołania, w którym są numerowane zarówno wiersze, jak i kolumny w arkuszu. Styl odwołania W1K1 jest przydatny do obliczania pozycji w wierszach i w kolumnach w makrach. W stylu W1K1 program Excel wskazuje lokalizację komórki za pomocą litery „W”, po której następuje numer wiersza, oraz litery „K”, po której następuje numer kolumny.

  Odwołanie	Znaczenie
  W[-2]K	odwołanie względne do komórki dwa wiersze wyżej w tej samej kolumnie
  W[2]K[2]	Odwołanie względne do komórki dwa wiersze niżej i dwie kolumny w prawo
  W2K2	Odwołanie bezwzględne do komórki w drugim wierszu i drugiej kolumnie
  W[-1]	Odwołanie względne do całego wiersza powyżej aktywnej komórki
  W	Odwołanie bezwzględne do bieżącego wiersza

  Podczas rejestrowania makra program Excel rejestruje niektóre polecenia za pomocą stylu odwołania W1K1. Jeśli na przykład nagrasz polecenie, takie jak wybranie przycisku Autosumowanie w celu wstawienia formuły, która doda zakres komórek, program Excel zarejestruje formułę przy użyciu stylu W1K1, a nie stylu A1, odwołań.

  Aby włączyć lub wyłączyć styl odwołania W1K1, należy zaznaczyć lub wyczyścić pole wyboru Styl odwołania W1K1 w sekcji Praca z formułami w kategorii Formuły w oknie dialogowym Opcje. Aby wyświetlić to okno dialogowe, wybierz kartę Plik .

  Początek strony