Omówienie formuł w programie Excel

Omówienie formuł w programie Excel

Nowi użytkownicy programu Excel mogą szybko odkryć, że jest on czymś więcej niż tylko siatką umożliwiającą wprowadzanie liczb w kolumnach i wierszach. Za pomocą programu Excel można obliczać sumy całkowite dla kolumn i wierszy liczb, ale oprócz tego można też obliczać spłatę kredytu hipotecznego, rozwiązywać zadania matematyczne i przeprowadzać obliczenia inżynieryjne lub określać scenariusze najlepszego przypadku na podstawie podłączonych zmiennych.

W programie Excel zadania te są wykonywane za pomocą formuł znajdujących się w komórkach. Formuła umożliwia przeprowadzanie obliczeń lub innych akcji na danych w arkuszu. Formuła zawsze zaczyna się od znaku równości (=), po którym mogą występować liczby, operatory matematyczne (takie jak znaki + lub - służące do dodawania lub odejmowania) oraz wbudowane funkcje programu Excel, które pozwalają w ogromnym stopniu rozszerzyć możliwości formuły.

W poniższym przykładzie formuła mnoży 2 razy 3, a następnie do uzyskanego wyniku dodaje 5, co daje końcowy rezultat równy 11.

=2*3+5

Poniżej przedstawiono kilka przykładowych typów formuł, które można wprowadzać w arkuszu.

  • =A1+A2+A3    Dodaje wartości z komórek A1, A2 i A3.

  • =SUMA(A1:A10)    Zwraca sumę wartości w komórkach od A1 do A10 przy użyciu funkcji SUMA.

  • =DZIŚ()    Zwraca bieżącą datę.

  • =LITERY.WIELKIE("witaj")    Konwertuje tekst „witaj” na „WITAJ” przy użyciu funkcji LITERY.WIELKIE.

  • =JEŻELI(A1>0)    Przy użyciu funkcji JEŻELI sprawdza komórkę A1 w celu określenia, czy zawiera ona wartość większą niż 0.

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   

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; expression 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. Na ogół najlepiej jest umieszczać stałe w osobnych komórkach, aby w razie potrzeby można było je łatwo zmienić, a następnie odwoływać się do tych komórek przy użyciu formuł.

Operatory określają typ obliczeń wykonywanych na elementach formuły. Zgodnie z ogólnymi zasadami matematycznymi program Excel wykonuje obliczenia w następującej kolejności: nawiasy, potęgi, mnożenie i dzielenie oraz dodawanie i odejmowanie. Kolejność wykonywania obliczeń można zmienić przy użyciu nawiasów.

Typy operatorów. Istnieją cztery typy operatorów obliczeniowych: arytmetyczne, porównania, łączenia tekstów i odwołania.

  • Operatory arytmetyczne

    Wymienione niżej operatory arytmetyczne służą do wykonywania podstawowych operacji matematycznych, takich jak dodawanie, odejmowanie, mnożenie lub dzielenie, łączenie liczb i obliczanie wyników liczbowych.

    Operator arytmetyczny

    Znaczenie

    Przykład

    + (znak plus)

    Dodawanie

    =3+3

    – (znak minus)

    Odejmowanie
    Negacja

    =3–3
    =-3

    * (gwiazdka)

    Mnożenie

    =3*3

    / (ukośnik)

    Dzielenie

    =3/3

    % (znak procentu)

    Procent

    30%

    ^ (daszek)

    Potęgowanie

    =3^3

  • Operatory porównania

    Do porównywania dwóch wartości służą następujące operatory. Wynik porównywania dwóch wartości za pomocą tych operatorów jest wartością logiczną PRAWDA albo FAŁSZ.

    Operator porównania

    Znaczenie

    Przykład

    = (znak równości)

    Równa się

    =A1=B1

    > (znak większości)

    Większe niż

    =A1>B1

    < (znak mniejszości)

    Mniejsze niż

    =A1<B1

    >= (znak większe lub równe)

    Większe lub równe

    =A1>=B1

    <= (znak mniejsze lub równe)

    Mniejsze lub równe

    =A1<=B1

    <> (znak nierówności)

    Różne

    =A1<>B1

  • Operator łączenia tekstów

    Operator & (handlowe „i”) służy do łączenia ciągów tekstowych w celu utworzenia jednego tekstu.

    Operator tekstowy

    Znaczenie

    Przykład

    & (handlowe „i”)

    Łączy dwie wartości w celu utworzenia jednej ciągłej wartości tekstowej

    ="Tele"&"wizor" daje „Telewizor”.
    Jeśli komórka A1 zawiera „Nazwisko”, a komórka B1 zawiera „Imię”, =A1&", "&B1 daje „Imię, Nazwisko”.

  • Operatory odwołania

    Zakresy komórek do obliczeń można połączyć za pomocą następujących operatorów.

    Operator odwołania

    Znaczenie

    Przykład

    : (dwukropek)

    Operator zakresu, który tworzy jedno odwołanie do wszystkich komórek między dwoma odwołaniami (włącznie z tymi odwołaniami).

    B5:B15

    ; (średnik)

    Operator składania, który łączy wiele odwołań w jedno odwołanie

    =SUMA(B5:B15;D5:D15)

    (spacja)

    Operator przecięcia, który tworzy jedno odwołanie do komórek wspólnych dla dwóch odwołań

    B7:D7 C6:C8

Początek strony

Kolejność wykonywania obliczeń może mieć wpływ na wartość zwracaną przez formułę, dlatego ważne jest zrozumienie, w jaki sposób jest określana kolejność i jak można ją zmienić, aby otrzymać żądane wyniki.

  • Kolejność obliczeń

    Formuły obliczają wartości w określonej kolejności. Formuła w programie Excel zawsze rozpoczyna się od znaku równości (=). Program Excel interpretuje następujące po nim znaki jako formułę. Za znakiem równości następują elementy, które będą obliczane (argumenty), takie jak stałe i odwołania do komórek. Są one oddzielone operatorami obliczeń. Program Excel oblicza formułę od lewej do prawej strony, zgodnie z określoną kolejnością operatorów w formule.

  • Pierwszeństwo operatorów w formułach programu Excel

    Jeśli w jednej formule zostanie użytych wiele operatorów, program Excel wykona operacje w kolejności przedstawionej w poniższej tabeli. Jeśli formuła zawiera operatory o tym samym pierwszeństwie — na przykład wtedy, gdy formuła zawiera zarówno operator mnożenia, jak i dzielenia — program Excel będzie obliczać operatory od lewej do prawej strony.

    Operator

    Opis

    : (dwukropek)

    (pojedyncza spacja)

    ; (średnik)

    Operatory odwołania

    Negacja (tak jak w –1)

    %

    Procent

    ^

    Potęgowanie

    * i /

    Mnożenie i dzielenie

    + i –

    Dodawanie i odejmowanie

    &

    Łączy dwa ciągi tekstu (złączanie)

    =
    < >
    <=
    >=
    <>

    Porównanie

  • Korzystanie z nawiasów w formułach programu Excel

    Aby zmienić kolejność obliczania, część formuły, która ma zostać obliczona w pierwszej kolejności, należy ująć w nawiasy. Na przykład poniższa formuła daje w wyniku 11, ponieważ program Excel wykonuje mnożenie przed dodawaniem. Formuła mnoży liczby 2 i 3, a następnie do wyniku dodaje liczbę 5.

    =5+2*3

    Jeśli zostanie użyty nawias w celu zmiany składni, program Microsoft Excel wykona dodawanie liczb 5 i 2, a następnie pomnoży wynik przez 3, co da wynik 21.

    =(5+2)*3

    W poniższym przykładzie nawiasy otaczające pierwszą część formuły wymuszają, aby program Excel najpierw obliczył wyrażenie B4+25, a następnie podzielił wynik przez sumę wartości w komórkach D5, E5 i F5.

    =(B4+25)/SUMA(D5:F5)

    Początek strony

Funkcje to wstępnie zdefiniowane formuły wykonujące obliczenia z wykorzystaniem określonych wartości, zwanych argumentami, w określonej kolejności, czyli strukturze. Funkcje mogą służyć do wykonywania prostych lub złożonych obliczeń. Wszystkie funkcje programu Excel można znaleźć na karcie Formuły na Wstążce:

Karta Formuły na wstążce w programie Excel
  • Składnia funkcji programu Excel

    Poniższy przykład, w którym użyto funkcji ZAOKR do zaokrąglenia liczby w komórce A10, ilustruje składnię funkcji.

    Struktura funkcji

    1. Struktura. Struktura funkcji zaczyna się od znaku równości (=), po którym następuje nazwa funkcji, nawias otwierający, argumenty funkcji oddzielone średnikami i nawias zamykający.

    2. Nazwa funkcji. Aby wyświetlić listę dostępnych funkcji, kliknij komórkę i naciśnij klawisze SHIFT+F3, co spowoduje otwarcie okna dialogowego Wstawianie funkcji.

    Formuły programu Excel — okno dialogowe Wstawianie funkcji

    3. Argumenty. Mogą to być liczby, tekst, wartości logiczne, takie jak PRAWDA lub FAŁSZ, tablice, wartości błędów (takie jak #N/D!) lub odwołania do komórek. Zdefiniowany argument musi gwarantować uzyskanie prawidłowej wartości. Argumentami mogą także być stałe, formuły lub inne funkcje.

    4. Etykietka argumentu. Etykietka ze składnią i argumentami pojawia się podczas wprowadzania funkcji. Pojawia się na przykład po wpisaniu tekstu =ZAOKR(. Etykietki są wyświetlane tylko w przypadku funkcji wbudowanych.

    Uwaga : Nie trzeba wpisywać funkcji wersalikami, na przykład =ZAOKR, ponieważ program Excel automatycznie zmieni litery nazwy funkcji na wielkie po naciśnięciu klawisza Enter. Jeśli błędnie wpiszesz nazwę funkcji, na przykład =SUME(A1:A10) zamiast =SUMA(A1:A10), program Excel zwróci błąd #NAZWA?.

  • Wprowadzanie funkcji programu Excel

    Podczas tworzenia formuły zawierającej funkcję można użyć okna dialogowego Wstawianie funkcji, które ułatwia wprowadzanie funkcji arkusza. Po wybraniu funkcji w oknie dialogowym Wstawianie funkcji program Excel uruchomi kreatora funkcji, który wyświetla nazwę funkcji i każdy z jej argumentów, opis funkcji i argumentów, bieżący wynik funkcji i bieżący wynik całej formuły.

    Kreator funkcji programu Excel

    Funkcja Autouzupełnianie formuł upraszcza tworzenie i edytowanie formuł, a także pozwala ograniczyć do minimum występujące przy tym błędy pisowni i składni. Po wpisaniu znaku równości (=) i pierwszych liter funkcji program Excel wyświetli dynamiczną listę rozwijaną zawierającą prawidłowe funkcje, argumenty i nazwy odpowiadające wpisanym literom. Możesz wtedy wybrać funkcję z listy rozwijanej, a program Excel ją wprowadzi.

    Autouzupełnianie formuł w programie Excel

  • Zagnieżdżanie funkcji programu Excel

    W niektórych przypadkach może zajść potrzeba użycia funkcji jako jednego z argumentów innej funkcji. Na przykład następująca formuła korzysta z zagnieżdżonej funkcji ŚREDNIA i porównuje wynik z wartością 50.

    Funkcje zagnieżdżone

    1. Funkcje ŚREDNIA i SUMA są zagnieżdżone w funkcji JEŻELI.

    Prawidłowość zwracanych wartości    Jeśli jako argument zostaje użyta funkcja zagnieżdżona, musi ona zwracać wartość tego samego typu co typ używany przez ten argument. Jeśli na przykład argument zwraca wartość PRAWDA lub FAŁSZ, to zagnieżdżona funkcja musi zwrócić wartość PRAWDA lub FAŁSZ. Jeśli tak się nie dzieje, program Excel wyświetla wartość błędu #ARG!.

    Ograniczenia poziomu zagnieżdżania    Formuła może zawierać najwyżej siedem poziomów funkcji zagnieżdżonych. Gdy jedna funkcja (nazwijmy ją Funkcja B) jest używana jako argument w innej funkcji (Funkcji A), Funkcja B działa jako funkcja drugiego poziomu. Na przykład funkcja ŚREDNIA i funkcja SUMA są funkcjami drugiego poziomu, jeśli są używane jako argumenty funkcji JEŻELI. Funkcja zagnieżdżona w funkcji ŚREDNIA jest wtedy funkcją trzeciego poziomu i tak dalej.

    Początek strony

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.

    Przykład odwołania do arkusza

    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 nazwa arkusza docelowego zawiera spacje lub liczby, należy dodać apostrofy (') przed nazwą arkusza i po niej, na przykład ='123'!A1 lub ='Zysk w styczniu'!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   

      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   

      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   

      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 implicit intersection.

    Co się stanie po przeniesieniu, skopiowaniu, wstawieniu lub usunięciu arkusza    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

    relative reference 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. Na przykład w sytuacji, gdy jest rejestrowane polecenie, takie jak kliknięcie przycisku Autosumowanie w celu wstawienia formuły sumującej zakres komórek, program Excel zarejestruje formułę w stylu odwołania W1K1, a nie w stylu odwołania A1.

    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, kliknij kartę Plik.

    Początek strony

Komórki, zakresy komórek, formuły, stałe lub tabele programu Excel można reprezentować przy użyciu zdefiniowanych nazw. Nazwa jest skrótem znaczącym umożliwiającym łatwiejsze zrozumienie przeznaczenia odwołania do komórki, stałej, formuły lub tabeli, które na pierwszy rzut oka może być trudne do zrozumienia. Poniżej przedstawiono typowe przykłady nazw, których używanie w formułach może poprawić przejrzystość i ułatwić zrozumienie formuł.

Przykład 1

Typ przykładu

Przykład, w którym są używane zakresy zamiast nazw

Przykład, w którym są używane nazwy

Odwołanie

=SUMA(A16:A20)

=SUMA(Sprzedaż)

Stała

=ILOCZYN(A12;9,5%)

=ILOCZYN(Cena;Stawka_podatkowa)

Formuła

=TEKST(WYSZUKAJ.PIONOWO(MAX(A16;A20);A16:B20;2;FAŁSZ);"rrrr-mm-dd")

=TEKST(WYSZUKAJ.PIONOWO(MAX(Sprzedaż);Informacje_o_sprzedaży;2;FAŁSZ);"rrrr-mm-dd")

Tabela

A22:B25

=ILOCZYN(Cena;Tabela1[@Stawka podatkowa])

Przykład 2

Skopiuj przykładowe dane z poniższej tabeli i wklej je w komórce A1 nowego arkusza programu Excel. Aby formuły wyświetlały wyniki, zaznacz je, naciśnij klawisz F2, a następnie naciśnij klawisz Enter. Jeśli to konieczne, możesz dostosować szerokości kolumn, aby wyświetlić pełne dane.

Uwaga :  W formułach w kolumnach C i D nazwa zdefiniowana „Sprzedaż” zastępuje odwołanie do zakresu A9:A13, a nazwa „Informacje_sprzedaży” zastępuje zakres A9:B13. Jeśli nie utworzysz tych nazw w skoroszycie testowym, formuły w zakresie D2:D3 zwrócą błąd #NAZWA?.

Typ przykładu

Przykład, bez użycia nazwy

Przykład, z użyciem nazwy

Formuła i wynik, z użyciem nazwy

Odwołanie

'=SUMA(A9:A13)

'=SUMA(Sprzedaż)

=SUMA(Sprzedaż)

Formuła

'=TEKST(WYSZUKAJ.PIONOWO(MAX(A9:13);A9:B13;2;FAŁSZ);"rrrr-mm-dd")

'=TEKST(WYSZUKAJ.PIONOWO(MAX(Sprzedaż);Informacje_sprzedaży;2;FAŁSZ);"rrrr-mm-dd")

=TEKST(WYSZUKAJ.PIONOWO(MAX(Sprzedaż);Informacje_sprzedaży;2;FAŁSZ);"rrrr-mm-dd")

Cena

995 PLN

Sprzedaż

Data sprzedaży

249 PLN

2011-03-17

399 PLN

2011-04-02

643 PLN

2011-04-23

275 PLN

2011-04-30

447 PLN

2011-05-04

  • Typy nazw

    Istnieje kilka typów nazw, które można tworzyć i których można używać.

    • Zdefiniowana nazwa    Nazwa reprezentująca komórkę, zakres komórek, formułę lub wartość stałą. Można utworzyć własną zdefiniowaną nazwę. Ponadto niekiedy zdefiniowana nazwa jest tworzona w programie Excel automatycznie, na przykład po ustawieniu obszaru wydruku.

    • Nazwa tabeli    Nazwa tabeli programu Excel, która jest kolekcją danych dotyczących określonego tematu zorganizowanych w rekordy (wiersze) i pola (kolumny). Domyślna nazwa tabeli w programie Excel to „Tabela1”, „Tabela2” i tak dalej dla każdej wstawionej tabeli programu Excel. Te nazwy można jednak zmienić, aby nadać im więcej znaczenia.

      Aby uzyskać więcej informacji o tabelach programu Excel, zobacz Używanie odwołań strukturalnych w tabelach programu Excel.

  • Tworzenie i wprowadzanie nazw

    Sposoby tworzenia nazwy:

    • Pole nazwy na pasku formuły     To najlepszy sposób utworzenia nazwy dla zaznaczonego zakresu na poziomie skoroszytu.

    • Tworzenie nazwy z zaznaczenia     Można w wygodny sposób tworzyć nazwy z istniejących etykiet wierszy i kolumn, zaznaczając komórki w arkuszu.

    • Okno dialogowe Nowa nazwa    Ten sposób tworzenia nazw daje największą elastyczność, umożliwiając określanie zakresu na poziomie arkusza lokalnego lub tworzenie komentarza do nazwy.

    Uwaga :  Domyślnie w nazwach są używane bezwzględne odwołania do komórek.

    Sposoby wprowadzania nazw:

    • Wpisywanie    Można na przykład wpisać nazwę jako argument w formule.

    • Korzystanie z funkcji autouzupełniania formuł    Można użyć listy rozwijanej funkcji autouzupełniania formuł, która zawiera utworzony automatycznie spis prawidłowych nazw.

    • Wybieranie nazwy z polecenia Użyj w formule    Można wybrać zdefiniowaną nazwę z listy dostępnej z poziomu polecenia Użyj w formule w grupie Nazwy zdefiniowane na karcie Formuła.

Aby uzyskać więcej informacji, zobacz Definiowanie nazw i używanie ich w formułach.

Początek strony

Formuła tablicowa może wykonać wiele obliczeń, a następnie zwrócić albo jeden wynik, albo wiele wyników. Formuły tablicowe działają na dwóch lub więcej zbiorach wartości, określanych jako argumenty tablicowe. Każdy argument tablicowy musi mieć taką samą liczbę wierszy i kolumn. Formuły tablicowe można tworzyć podobnie jak inne formuły, ale należy przy tym nacisnąć klawisze CTRL+SHIFT+ENTER, aby wprowadzić formułę. Niektóre z wbudowanych funkcji są formułami tablicowymi i trzeba je wprowadzać jako tablice, aby uzyskać poprawne wyniki.

Stałych tablicowych można używać zamiast odwołań, aby uniknąć wprowadzania wartości poszczególnych stałych w różnych komórkach arkusza.

Obliczanie poszczególnych wyników i wielu wyników przy użyciu formuły tablicowej

Uwaga : Gdy zostanie wprowadzona array formula, program Excel automatycznie wstawi formułę między nawiasami klamrowymi { }. Jeśli spróbujesz wprowadzić nawiasy samodzielnie, program Excel wyświetli formułę jako tekst.

  • Formuła tablicowa obliczająca jeden wynik    Ten typ formuły tablicowej może uprościć model arkusza, zamieniając wiele różnych formuł na jedną formułę tablicową.

    Na przykład następująca formuła tablicowa służy do obliczania wartości całkowitej tablicy cen udziałów i akcji bez używania wierszy komórek do obliczania i wyświetlania poszczególnych wartości każdej akcji.

    Formuła tablicowa dająca pojedynczy wynik

    Gdy zostanie wprowadzona formuła ={SUMA(B2:D2*B3:D3)} jako formuła tablicowa, pomnożone zostaną Udział i Cena każdej akcji, a następnie wyniki tych obliczeń zostaną do siebie dodane.

  • Formuła tablicowa obliczająca wiele wyników    Niektóre funkcje arkusza zwracają tablice wartości albo wymagają tablicy wartości jako argumentu. Aby obliczyć wiele wyników za pomocą formuły tablicowej, trzeba wprowadzić tablicę w zakresie komórek, który ma tę samą liczbę wierszy i kolumn co argumenty tablicowe.

    Na przykład mając serię trzech wyników sprzedaży (kolumna B) dla serii trzech miesięcy (kolumna A), funkcja REGLINW określa wartości trendu prostoliniowego wyników sprzedaży. Aby zostały wyświetlone wszystkie wyniki formuły, formuła jest wprowadzana w trzech komórkach w kolumnie C (C1:C3).

    Formuła tablicowa dająca wiele wyników

    Formuła =REGLINW(B1:B3;A1:A3) wprowadzona jako formuła tablicowa daje w wyniku trzy oddzielne rezultaty (22196, 17079, i 11962) na podstawie trzech wyników sprzedaży i trzech miesięcy.

Korzystanie ze stałych tablicowych

W zwykłej formule można wprowadzić odwołanie do komórki zawierającej wartość albo samą wartość, zwaną również stałą. Podobnie w formule tablicowej można wprowadzić odwołanie do tablicy albo wprowadzić tablicę wartości zawartych w komórkach, zwaną również stałą tablicową. Formuły tablicowe akceptują stałe tak samo jak formuły nietablicowe, ale stałe tablicowe należy wprowadzić w określonym formacie.

Stałe tablicowe mogą zawierać liczby, tekst, wartości logiczne PRAWDA i FAŁSZ lub wartości błędów, takie jak #N/D. W tej samej stałej tablicowej mogą występować wartości różnego typu — na przykład {1;3;4\PRAWDA;FAŁSZ;PRAWDA}. Liczby w stałych tablicowych mogą być wartościami całkowitymi, ułamkowymi lub mogą być zapisane w notacji naukowej. Tekst musi być ujęty w cudzysłów — na przykład "wtorek".

Stałe tablicowe nie mogą zawierać odwołań do komórek, kolumn ani wierszy o innych długościach, formuł ani znaków specjalnych $ (dolar), nawiasów ani znaków % (procent).

Podczas formatowania stałych tablicowych należy pamiętać o następujących zasadach:

  • Stałe tablicowe należy umieścić w nawiasach klamrowych ( { } ).

  • Wartości w różnych kolumnach rozdziela się średnikami (;). Na przykład chcąc przedstawić wartości 10, 20, 30 i 40, należy wprowadzić ciąg {10;20;30;40}. Taka stała tablicowa jest znana jako tablica 1 na 4 i odpowiada odwołaniu do zakresu o jednym wierszu i czterech kolumnach.

  • Wartości w różnych wierszach rozdziela się za pomocą kresek ułamkowych odwróconych (\). Na przykład aby przedstawić wartości 10, 20, 30 i 40 w jednym wierszu, a wartości 50, 60, 70 i 80 w następnym, należy wprowadzić stałą tablicową o rozmiarach 2 na 4: {10;20;30;40\50;60;70;80}.

Początek strony

W przypadku usunięcia formuły usuwane są także jej wartości wynikowe. Możesz jednak usunąć tylko formułę i pozostawić jej wartość wynikową wyświetlaną w komórce.

  • Aby usunąć formuły wraz z ich wartościami wynikowymi, wykonaj następujące czynności:

    1. Zaznacz komórkę lub zakres komórek zawierający formułę.

    2. Naciśnij klawisz DELETE.

  • Aby usunąć formuły bez ich wartości wynikowych, wykonaj następujące czynności:

    1. Zaznacz komórkę lub zakres komórek zawierający formułę.

      Jeśli formuła jest formułą tablicową, zaznacz zakres komórek zawierający formułę tablicową.

      Jak zaznaczyć zakres komórek zawierający formułę tablicową

      1. Kliknij komórkę w obrębie formuły tablicowej.

      2. Na karcie Narzędzia główne w grupie Edytowanie kliknij przycisk Znajdź i zaznacz, a następnie kliknij polecenie Przejdź do.

      3. Kliknij przycisk Specjalnie.

      4. Kliknij opcję Bieżąca tablica.

    2. Na karcie Narzędzia główne w grupie Schowek kliknij pozycję Kopiuj Obraz przycisku .
      Obraz Wstążki programu Excel

      Skrót klawiaturowy    Możesz także nacisnąć klawisze CTRL+C.

    3. Na karcie Narzędzia główne w grupie Schowek kliknij strzałkę pod przyciskiem Wklej Obraz przycisku , a następnie kliknij polecenie Wklej wartości.

Początek strony

W poniższej tabeli opisano typowe błędy, które można popełnić podczas wprowadzania formuły, oraz jak unikać błędów w formułach:

Upewnij się, że...

Więcej informacji

Liczba nawiasów otwierających i zamykających w formule jest taka sama   

Wszystkie nawiasy otwierające używane w formułach mają swoje odpowiedniki zamykające. Gdy tworzysz formułę, wprowadzane nawiasy są w programie Excel wyświetlane w kolorze.

Użyto dwukropka do oznaczenia zakresu wprowadzanego do formuły   

Dwukropki (:) służą do oddzielania odwołań do pierwszej i ostatniej komórki w zakresie. Na przykład A1:A5.

Wprowadzono wszystkie wymagane argumenty    

Funkcje mogą mieć argumenty wymagane i opcjonalne (oznaczane w składni nawiasami kwadratowymi). Wszystkie argumenty wymagane muszą zostać wprowadzone. Ponadto należy się upewnić, że nie wprowadzono zbyt wielu argumentów.

Nie zagnieżdżono w formule więcej niż 64 funkcje   

Zagnieżdżanie funkcji w formule jest ograniczone do 64 poziomów.

Nazwy skoroszytów i arkuszy są ujęte w pojedynczy cudzysłów    

W przypadku odwoływania się do wartości lub komórek w innych arkuszach albo skoroszytach, których nazwy zawierają znaki niealfabetyczne, nazwy te muszą być ujęte w pojedynczy cudzysłów ( ' ).

Dołączono ścieżkę dostępu do skoroszytów zewnętrznych    

Odwołania zewnętrzne muszą zawierać nazwę skoroszytu i ścieżkę do niego.

Wprowadzono liczby bez formatowania    

Liczby wprowadzone w formule nie powinny być sformatowane przy użyciu znaków dolara ($), ponieważ służą one do oznaczania odwołań bezwzględnych. Na przykład zamiast $1000 wprowadź w formule 1000.

Początek strony

Ważne : Obliczone wyniki formuł i niektórych funkcji arkusza programu Excel mogą się nieco różnić w przypadku komputera z systemem Windows i architekturą x86 lub x86-64 oraz komputera z systemem Windows RT i architekturą ARM. Dowiedz się więcej o różnicach.

Masz pytanie dotyczące konkretnej funkcji?

Zadaj pytanie na forum społeczności programu Excel

Pomóż w ulepszaniu programu Excel

Masz sugestie dotyczące możliwości ulepszenia następnej wersji programu Excel? Jeśli tak, zapoznaj się z tematami w witrynie UserVoice dotyczącej programu Excel

Zobacz też

Omówienie formuł w programie Excel

Jak unikać niepoprawnych formuł

Znajdowanie i poprawianie błędów w formułach

Skróty klawiaturowe i klawisze funkcyjne w programie Excel

Funkcje programu Excel (lista alfabetyczna)

Funkcje programu Excel (według kategorii)

Rozwijaj swoje umiejętności
Poznaj szkolenia
Uzyskuj nowe funkcje w pierwszej kolejności
Dołącz do niejawnych testerów pakietu Office

Czy te informacje były pomocne?

Dziękujemy za opinię!

Dziękujemy za opinię! Wygląda na to, że połączenie Cię z jednym z naszych agentów pomocy technicznej pakietu Office może być pomocne.

×