Czym są formuły w MS Excel

Formuły w Microsoft Excel to instrukcje używane do wykonywania obliczeń i operacji na danych przechowywanych w arkuszu kalkulacyjnym. Formuły umożliwiają użytkownikom wykonywanie skomplikowanych obliczeń matematycznych, funkcji statystycznych i logicznych oraz manipulowanie danymi w różnych sposobów, takich jak łączenie, dzielenie, sortowanie i filtrowanie.

Formuły w programie Excel są zwykle wprowadzane w komórce arkusza kalkulacyjnego i zaczynają się od znaku równości "=" (np. "=SUM(A1:A10)"). Po wprowadzeniu formuły, Excel wykona obliczenia na podstawie danych zawartych w wyznaczonym zakresie komórek lub w innych komórkach związanych z tą formułą.

Formuły są bardzo użyteczne w Excelu, ponieważ pozwalają na automatyzację wielu rutynowych działań i przetwarzanie dużych zbiorów danych w sposób szybki i skuteczny. Ponadto, formuły w Excelu pozwalają na wykonywanie obliczeń na podstawie zmieniających się danych w czasie rzeczywistym, dzięki czemu można w prosty sposób śledzić i analizować trendy i zmiany w danych.

Tworzenie formuł

Aby utworzyć formułę w programie Microsoft Excel, należy postępować zgodnie z następującymi krokami:

1.     Wybierz komórkę, w której chcesz utworzyć formułę.

2.     Wprowadź znak równości "=" na początku komórki, aby wskazać, że jest to formuła.

3.     Wprowadź formułę w komórce. Formuły składają się z funkcji, operatorów, liczb i odwołań do innych komórek w arkuszu kalkulacyjnym.

Naciśnij klawisz Enter, aby zatwierdzić formułę. Excel automatycznie wykona obliczenia i wyświetli wynik w komórce, w której została wprowadzona formuła.

Przykład:

Aby utworzyć formułę, która dodaje wartości w komórkach A1 i B1, wykonaj następujące kroki:

1.     Wybierz komórkę, w której chcesz wyświetlić wynik formuły.

2.     Wprowadź znak równości "=".

3.     Wprowadź formułę, np. "=A1+B1".

4.     Naciśnij klawisz Enter.

Excel wykona obliczenia i wyświetli wynik w wybranej komórce.

Źródło: Jakub Piskorowski

Ważne jest, aby pamiętać o kolejności operatorów matematycznych w formule, gdyż może to mieć wpływ na wynik obliczeń. Aby zwiększyć czytelność formuł, można również użyć nawiasów w celu określenia kolejności wykonywanych działań.

Przykłady zastosowań formuł

Oto kilka przykładów zastosowań formuł w arkuszach kalkulacyjnych bez użycia funkcji wbudowanych:

• Obliczanie procentu: Aby obliczyć procent, należy pomnożyć wartość przez procent i podzielić wynik przez 100. Na przykład, aby obliczyć 20% z wartości 500, można wpisać formułę =B5*A2/100 w innej komórce.

Źródło: Jakub Piskorowski

• Obliczanie średniej arytmetycznej: Aby obliczyć średnią arytmetyczną, należy dodać wszystkie wartości i podzielić sumę przez liczbę wartości. Na przykład, aby obliczyć średnią wartość w komórkach A10 do A16, można wpisać formułę =(A10+A11+A12+A13+A14+A15+A16)/7 w dowolnej innej komórce. W przypadku tego rodzaju działań pamiętaj o nawiasach w odpowiednim miejscu.

Źródło: Jakub Piskorowski

Formuły z funkcji wbudowanej

Źródło: Jakub Piskorowski

Funkcja wbudowana w programie Microsoft Excel to gotowy zestaw instrukcji, które pozwalają na wykonanie konkretnych operacji matematycznych, statystycznych, logicznych lub tekstowych na danych znajdujących się w arkuszu kalkulacyjnym. Funkcje wbudowane są dostępne w Excelu od razu po uruchomieniu programu i nie wymagają dodatkowej instalacji.

Przykłady funkcji wbudowanych w programie Excel to funkcja SUM, która dodaje wartości w wyznaczonym zakresie komórek, funkcja AVERAGE, która oblicza średnią arytmetyczną wartości w wyznaczonym zakresie komórek, funkcja IF, która wykonuje określoną operację w zależności od spełnienia określonego warunku, oraz wiele innych.

Funkcje wbudowane w Excelu są bardzo użyteczne, ponieważ pozwalają użytkownikom na szybkie i łatwe wykonywanie skomplikowanych operacji na danych, które zwykle wymagałyby pisania długich i skomplikowanych formuł. Funkcje wbudowane są łatwe w użyciu, ponieważ użytkownik może wybrać funkcję z listy dostępnych funkcji lub wpisać jej nazwę ręcznie w formule. Ponadto, funkcje wbudowane w Excelu są zwykle dobrze udokumentowane i mają szereg opcji konfiguracyjnych, dzięki czemu użytkownicy mogą dostosować je do swoich konkretnych potrzeb.

Oto niektóre przykładowe formuły, które można użyć w programie Microsoft Excel:

• SUMA - dodaje wartości w wybranej komórce lub zakresie komórek. Na przykład, "=SUMA(A1:A10)" dodaje wartości w komórkach A1 do A10.

• ŚREDNIA - oblicza średnią wartość dla wybranej komórki lub zakresu komórek. Na przykład, "=ŚREDNIA(A1:A10)" oblicza średnią wartość dla komórek A1 do A10.

• MIN - zwraca najmniejszą wartość w wybranej komórce lub zakresie komórek. Na przykład, "=MIN(A1:A10)" zwraca najmniejszą wartość w komórkach A1 do A10.

• MAX - zwraca największą wartość w wybranej komórce lub zakresie komórek. Na przykład, "=MAX(A1:A10)" zwraca największą wartość w komórkach A1 do A10.

• JEŻELI - umożliwia wykonywanie warunkowych działań na podstawie określonych kryteriów. Na przykład, "=JEŻELI(A1>5,"Tak","Nie")" zwraca "Tak", jeśli wartość w komórce A1 jest większa niż 5 i "Nie", jeśli nie.

• WYSZUKAJ.PIONOWO - umożliwia wyszukiwanie wartości w tabeli na podstawie określonego kryterium. Na przykład, "=WYSZUKAJ.PIONOWO(A1,A:B,2,FALSE)" wyszukuje wartość w kolumnie B, która odpowiada wartości w komórce A1.

• POŁĄCZ.TEKSTY - łączy dwa lub więcej ciągów tekstowych w jedną komórkę. Na przykład, "=POŁĄCZ.TEKSTY(A1," ",B1)" łączy wartość w komórce A1 z wartością w komórce B1, oddzielając je spacją.

• TERAZ - zwraca bieżącą datę i czas. Na przykład, "=TERAZ()" zwraca bieżącą datę i czas.

• LOS - zwraca losową liczbę z przedziału od 0 do 1. Na przykład, "=LOS()" zwraca losową liczbę.

• LICZ.JEŻELI - zlicza liczbę komórek w zakresie, które spełniają określone kryteria. Na przykład, "=LICZ.JEŻELI(A1:A10,">5")" zlicza liczbę komórek w zakresie A1 do A10, które zawierają wartość większą niż 5.

To tylko kilka przykładów formuł w programie Microsoft Excel. Jest wiele innych, które można wykorzystać do różnych celów, w zależności od potrzeb i wymagań użytkownika.

Przykłady zastosowań funkcji wbudowanych

Oto kilka przykładów zastosowań prostych formuł w arkuszach kalkulacyjnych z użyciem funkcji wbudowanych:

• SUMA: Funkcja SUMA służy do sumowania wartości w wybranym zakresie komórek. Na przykład, aby zsumować wartości w komórkach A2 do A11, można wpisać formułę =SUMA(A2:A11) w innej komórce.

Źródło: Jakub Piskorowski

• ŚREDNIA: Funkcja ŚREDNIA służy do obliczania średniej arytmetycznej wartości w wybranym zakresie komórek. Na przykład, aby obliczyć średnią wartość w komórkach A1 do A10, można wpisać formułę =ŚREDNIA(A2:A11) w dowolnej innej komórce.

Źródło: Jakub Piskorowski

• MAX: Funkcja MAX służy do znajdowania maksymalnej wartości w wybranym zakresie komórek. Na przykład, aby znaleźć maksymalną wartość w komórkach A1 do A10, można wpisać formułę =MAX(A2:A11) w innej komórce.

Źródło: Jakub Piskorowski

• MIN: Funkcja MIN służy do znajdowania minimalnej wartości w wybranym zakresie komórek. Na przykład, aby znaleźć minimalną wartość w komórkach A1 do A10, można wpisać formułę =MIN(A2:A11) w dowolnej innej komórce.

Źródło: Jakub Piskorowski

• ILE.LICZB: Funkcja ILE.LICZB służy do zliczania ilości komórek w wybranym zakresie, które zawierają liczby. Na przykład, aby zliczyć ilość komórek w zakresie A1:A10, które zawierają liczby, można wpisać formułę =ILE.LICZB(A2:A11) w innej komórce.

Źródło: Jakub Piskorowski

• JEŻELI: Funkcja JEŻELI służy do wykonania konkretnej instrukcji w zależności od tego czy warunek zostanie spełniony czy nie. Przykładem prostego zastosowania funkcji JEŻELI może być obliczenie rabatu w zależności od wartości zamówienia. Załóżmy, że w komórce G1 znajduje się wartość zamówienia, a w komórce G2 chcesz wyświetlić wartość rabatu. Jeśli wartość zamówienia przekracza 1000 zł, to chcesz zastosować rabat w wysokości 10%, w przeciwnym razie nie ma rabatu. W takim przypadku można użyć funkcji JEŻELI z następującą formułą: =JEŻELI(G1>1000;G1*0,1;0)

Źródło: Jakub Piskorowski

Ta formuła działa w ten sposób:
Sprawdza, czy wartość w komórce G1 jest większa niż 1000 zł. Jeśli tak, to funkcja IF zwraca 10% wartości zamówienia (obliczonej przez pomnożenie wartości w komórce G1 przez 0,1). Jeśli wartość zamówienia jest mniejsza lub równa 1000 zł, to funkcja IF zwraca 0, co oznacza, że nie ma rabatu.

Przykłady zastosowań funkcji w funkcji

Dane są trzy kolumny z danymi: "Imię i nazwisko", "Wiek" oraz "Wynagrodzenie". Należy obliczyć średnie wynagrodzenie dla osób powyżej 30 roku życia.

W celu rozwiązania tego zadania można skorzystać z funkcji w arkuszu kalkulacyjnym, takich jak funkcja ŚREDNIA oraz funkcja JEŻELI.

• Aby wyfiltrować osoby powyżej 30 roku życia, należy skorzystać z funkcji JEŻELI. Funkcja ta pozwala na wybór tylko tych wierszy, dla których określone kryterium jest prawdziwe. W tym przypadku, kryterium to wiek powyżej 30 roku życia.

• Aby obliczyć średnie wynagrodzenie dla tych osób, które spełniają kryterium, należy skorzystać z funkcji ŚREDNIA. Funkcja ta oblicza średnią arytmetyczną z wybranych wartości.

Ostatecznie, arkusz kalkulacyjny będzie wyglądał w następujący sposób:

Źródło: Jakub Piskorowski

W tym przykładzie, funkcja JEŻELI jest używana do wyboru tylko tych wierszy, dla których wiek jest powyżej 30 roku życia, a funkcja ŚREDNIA jest używana do obliczenia średniego wynagrodzenia dla tych osób.

Automatyczne uzupełnianie

Automatyczne uzupełnianie to funkcja w programie MS Excel, która pozwala na szybkie i łatwe uzupełnienie komórek lub ciągów komórek na podstawie wzorca.

Aby skorzystać z automatycznego uzupełniania w Excelu, należy wpisać wartość lub wzorzec w jednej lub kilku komórkach, a następnie przeciągnąć w dół lub w bok za pomocą kursora (zaznaczyć wzorzec i złapać za krawędź, zielony punkt w rogu). Excel automatycznie rozpozna wzorzec i uzupełni pozostałe komórki zgodnie z nim.

Na przykład, jeśli wpiszesz "Styczeń" w komórce A1 i "Luty" w komórce A2, możesz przeciągnąć kursor z A2 w dół, a Excel automatycznie uzupełni resztę komórek kolejnymi miesiącami.

Źródło: Jakub Piskorowski

Źródło: Jakub Piskorowski

Automatyczne uzupełnianie może być również używane do uzupełnienia danych liczbowych lub ciągów znaków, na przykład adresów e-mail lub kodów pocztowych. Jeśli na przykład wpiszesz "1" w komórce C1 i "2" w komórce C2, możesz przeciągnąć kursor w dół, a Excel automatycznie uzupełni resztę komórek kolejnymi liczbami.

Automatyczne uzupełnianie może znacznie przyspieszyć proces wprowadzania danych i jest szczególnie przydatne, gdy trzeba wpisać te same lub podobne dane w wielu komórkach.

Źródło: Jakub Piskorowski

Źródło: Jakub Piskorowski

Automatyczne uzupełnianie formuł na podstawie wzorca to funkcja, która pozwala na szybkie i łatwe uzupełnianie komórek arkusza kalkulacyjnego formułami, na podstawie wzorca formuły. Dzięki temu użytkownik nie musi wpisywać tej samej formuły dla każdej komórki oddzielnie.

Funkcja ta polega na przeciągnięciu uchwytu dolnej prawej części zaznaczonej komórki do innych komórek w kolumnie lub wierszu, które mają zostać wypełnione automatycznie. Program Excel automatycznie dostosuje wzorzec formuły do kolejnych komórek, uwzględniając zmiany w adresach komórek.

Automatyczne uzupełnianie formuł na podstawie wzorca w programie Excel jest przydatne zwłaszcza w przypadku dużych arkuszy kalkulacyjnych, gdzie konieczne jest szybkie i skuteczne wypełnienie wielu komórek tym samym wzorcem formuły.

Aby skorzystać z tej funkcji, należy najpierw utworzyć formułę, którą chcemy skopiować. Następnie należy kliknąć komórkę z formułą, a następnie kliknąć na mały kwadrat znajdujący się w prawym dolnym rogu komórki (tzw. "uchwyt do automatycznego wypełniania"). Przytrzymując lewy przycisk myszy, należy przeciągnąć uchwyt w dół lub w prawo, w zależności od tego, w którym kierunku chcemy skopiować wzorzec. Po zwolnieniu przycisku myszy program automatycznie wypełni pozostałe komórki wzorcem formuły.

Można wygenerować losowe liczby z przedziału od 0 do 30 w programie Microsoft Excel przy użyciu funkcji LOS(). Aby to zrobić, należy:

1. Wybrać komórkę, w której chcemy wyświetlić pierwszą losową liczbę.

2. Wpisać formułę: =LOS()*30

3. Nacisnąć klawisz Enter na klawiaturze.

4. Skopiować formułę do pozostałych komórek, korzystając z funkcji automatycznego uzupełniania, jak opisano wyżej.

W ten sposób, Excel wygeneruje 15 losowych liczb z przedziału od 0 do 30. Warto jednak pamiętać, że każde uruchomienie funkcji LOS() zwraca inne wyniki, więc w kolejnych uruchomieniach komórki będą wypełnione innymi losowymi liczbami.

Źródło: Jakub Piskorowski

Źródło: Jakub Piskorowski

Źródło: Jakub Piskorowski

Źródło: Jakub Piskorowski

Źródło: Jakub Piskorowski

Aby zmienić ilość liczb po przecinku w komórkach, można skorzystać z opcji formatowania komórek.

1. Wybierz komórki, których chcesz zmienić formatowanie (wszystkie komórki zawierające liczby).

2. Kliknij prawym przyciskiem myszy na wybrane komórki i wybierz opcję "Formatuj komórki" lub wciśnij skrót klawiszowy Ctrl+1.

3. W otwartym oknie dialogowym "Formatowanie komórek" przejdź do zakładki "Liczby".

4. W sekcji "Kategoria" wybierz odpowiedni format liczb, np. "Liczbowe" lub "Walutowe".

5. W sekcji "Liczby dziesiętne" wybierz ilość miejsc po przecinku.

Kliknij przycisk "OK", aby zastosować formatowanie do wybranych komórek.

Od teraz wartości wpisywane do wybranych komórek będą automatycznie zaokrąglane do wybranej ilości miejsc po przecinku. Jeśli chcesz zmienić formatowanie komórek na stałe, należy skopiować wartości do innych komórek, po czym usunąć wartości w pierwotnych komórkach, a następnie wkleić skopiowane wartości z powrotem do tych samych komórek. W ten sposób zmiany zostaną zastosowane do wartości bez utraty ich zawartości.

Źródło: Jakub Piskorowski

Źródło: Jakub Piskorowski

Zadania Microsoft Excel 2

Zadanie 1

W programie MS Excel obliczyć sumę, średnią oraz wartość maksymalną i minimalną dla następujących liczb: 14, 27, 18, 9, 22, 15, 9, 18. Plik „Zadania.xlsx” zakładka „Zadanie-1”.

W przypadku braku pliku zrekonstruować zawartość z poniższego obrazka i umieścić w odpowiedniej zakładce.

Źródło: Jakub Piskorowski

Zadanie 2

W arkuszu kalkulacyjnym excel znajduje się tabela z wynikami egzaminu z matematyki. W kolumnach A, B i C znajdują się odpowiednio nazwiska uczniów, liczba punktów za egzamin oraz oceny od 2 do 5. W kolumnie D chcemy obliczyć procentowy wynik każdego ucznia na egzaminie, a w kolumnie E wyświetlić informację, czy uczeń zdał egzamin (jeśli procentowy wynik jest większy lub równy 40%, to uczeń zdał egzamin, w przeciwnym razie nie zdał). Aby rozwiązać to zadanie, możemy skorzystać z kilku podstawowych funkcji wbudowanych w Excel: SUMA, ŚREDNIA, JEŻELI, Funkcja ZAOKR - do zaokrąglania wyniku procentowego do dwóch miejsc po przecinku. Plik „Zadania.xlsx” zakładka „Zadanie-2”.

W przypadku braku pliku zrekonstruować zawartość z poniższego obrazka i umieścić w odpowiedniej zakładce.

Źródło: Jakub Piskorowski

Zadanie 3

Poniżej znajduje się lista zakupów, w kolumnie A znajdują się nazwy produktów, a w kolumnie B ilość zakupionych sztuk. W kolumnie C znajduje się cena jednostkowa produktu. Napisz formułę, która obliczy wartość każdego zakupu (ilość sztuk razy cena jednostkowa) i umieści wynik w kolumnie D. Następnie napisz formułę, która obliczy sumę wartości wszystkich zakupów i umieści ją w komórce D11. Możesz założyć, że lista zakupów znajduje się w zakresie A1:C10. Pamiętać o poprawnym sformatowaniu tabeli. Zadanie umieść w pliku „Zadania.xlsx” zakładka „Zadanie-3”.

Zadanie 4

Stwórz kosztorys złożenia komputera stacjonarnego. W tabeli powinny znaleźć się następujące kolumny: L.p (liczba porządkowa), nazwa podzespołu, ilość sztuk, cena za sztukę, cena całkowita oraz link do przedmiotu w sklepie online. Uwzględnij również koszt złożenia komputera oraz system operacyjny. Na końcu tabeli należy obliczyć również sumę za cały zestaw i poprawnie sformatować tabelę, aby była estetyczna i czytelna. Zadanie umieść w pliku „Zadania.xlsx” zakładka „Zadanie-4”.