Funkcja SEKWENCJA w Excelu pozwala na efektywne tworzenie ciągów liczbowych i dat bez konieczności ręcznego wprowadzania każdej wartości. Dzięki tej funkcji możemy generować sekwencje wartości o określonym kroku, liczbie wierszy i kolumn, co znacząco przyspiesza pracę przy tworzeniu rozbudowanych arkuszy i wykresów.
W tym artykule przedstawiam dokładne objaśnienie działania funkcji SEKWENCJA wraz z praktycznymi przykładami zastosowań.
Czym jest funkcja SEKWENCJA w Excelu?
Funkcja SEKWENCJA to jedna z nowszych funkcji wprowadzonych przez Microsoft do Excela, która umożliwia generowanie sekwencji liczbowych w postaci tablicy. Jest to niezwykle użyteczne narzędzie, które pozwala na szybkie tworzenie ciągów wartości bez konieczności ręcznego wprowadzania każdej liczby. Funkcja ta jest szczególnie pomocna przy tworzeniu wykresów, tabel danych czy przy wykonywaniu obliczeń wymagających regularnych odstępów między wartościami.
Składnia funkcji SEKWENCJA jest prosta i intuicyjna. Wymaga ona podania kilku parametrów, które determinują, jak będzie wyglądać wygenerowana sekwencja. Podstawowe parametry to:
Liczba wierszy — określa, ile wierszy będzie zawierać wygenerowana tablica
Liczba kolumn — określa, ile kolumn będzie zawierać wygenerowana tablica
Wartość początkowa — liczba, od której rozpocznie się sekwencja (domyślnie 1)
Krok — różnica między kolejnymi wartościami w sekwencji (domyślnie 1)
Proste przykłady użycia funkcji SEKWENCJA
Zacznijmy od najprostszego przykładu użycia funkcji SEKWENCJA. Gdy wpiszemy formułę =SEKWENCJA(5), Excel wygeneruje pięciowierszową sekwencję liczb, zaczynając od 1 i zwiększając wartość o 1 w każdym kolejnym wierszu. Otrzymamy więc wartości: 1, 2, 3, 4, 5.
Możemy jednak budować znacznie bardziej złożone sekwencje, definiując wszystkie parametry funkcji. Na przykład, formuła =SEKWENCJA(4;3;-2;-,5) wygeneruje tablicę składającą się z 4 wierszy i 3 kolumn, rozpoczynającą się od wartości ‑2 i zmniejszającą się o 0,5 przy każdej kolejnej komórce. Warto zauważyć, że Excel pozwala na pominięcie zera przed przecinkiem przy liczbach dziesiętnych, więc zamiast ‑0,5 możemy napisać -,5.
Ta funkcja staje się niezwykle użyteczna, gdy potrzebujemy utworzyć sekwencję z małym krokiem, na przykład do generowania danych do wykresów. Przykładowo, formuła =SEKWENCJA(31;1;-3;,1) utworzy ciąg 31 liczb, zaczynający od ‑3 i zwiększający się o 0,1 z każdą kolejną wartością. Taka sekwencja może służyć jako oś x dla wykresu przedstawiającego funkcję matematyczną.
Problem dokładności liczb zmiennoprzecinkowych
Podczas pracy z funkcją SEKWENCJA należy pamiętać o pewnym ważnym aspekcie związanym z reprezentacją liczb zmiennoprzecinkowych w komputerach. Ze względu na sposób, w jaki komputery przechowują liczby dziesiętne (w systemie binarnym), mogą pojawić się drobne niedokładności w odległych miejscach po przecinku.
Ten problem można zaobserwować, gdy tworzymy sekwencję przechodzącą przez zero z niewielkim krokiem. Na przykład, gdy generujemy sekwencję od ‑3 do 3 z krokiem 0,1, wartość, która powinna być dokładnie równa 0, może być reprezentowana jako bardzo mała liczba, np. ‑1,5e-15 (co oznacza ‑1,5 × 10^(-15)).
Takie drobne różnice mogą mieć wpływ na wykresy, ponieważ Excel traktuje te wartości jako różne od zera. Aby rozwiązać ten problem, możemy zastosować funkcję zaokrąglającą do całej sekwencji. Na przykład, formuła =ZAOKR(SEKWENCJA(31;1;-3;,1);1) zaokrągli wszystkie wartości w sekwencji do jednego miejsca po przecinku, eliminując niepożądane niedokładności.
Wykorzystanie funkcji SEKWENCJA do tworzenia wykresów
Funkcja SEKWENCJA jest niezwykle przydatna przy tworzeniu wykresów, zwłaszcza tych przedstawiających funkcje matematyczne lub rozkłady statystyczne. Gdy mamy już utworzoną sekwencję liczb (po zaokrągleniu, jeśli to konieczne), możemy wykorzystać ją jako dane wejściowe do innych funkcji.
Na przykład, aby utworzyć wykres rozkładu normalnego, możemy użyć funkcji ROZK.NORMALNY w połączeniu z funkcją SEKWENCJA. Formuła =ROZK.NORMALNY(SEKWENCJA(31;1;-3;,1);;1;FAŁSZ) oblicza wartości funkcji gęstości prawdopodobieństwa rozkładu normalnego dla każdej wartości w naszej sekwencji.
Następnie, wybierając obie sekwencje (wartości x i odpowiadające im wartości y) i tworząc wykres punktowy, otrzymujemy elegancką wizualizację rozkładu normalnego. Jest to znacznie bardziej efektywne niż ręczne wprowadzanie wszystkich tych wartości.
Sekwencje dat za pomocą funkcji SEKWENCJA
Funkcja SEKWENCJA może być również użyta do generowania sekwencji dat, co jest niezwykle przydatne przy tworzeniu harmonogramów, raportów miesięcznych czy rocznych. Możemy to osiągnąć, łącząc funkcję SEKWENCJA z funkcją DATA.
Na przykład, aby utworzyć sekwencję dat, które wszystkie przypadają na 15. dzień każdego miesiąca w roku 2023, możemy użyć formuły =DATA(2023;SEKWENCJA(12);15). Ta formuła generuje 12 dat, od 15 stycznia 2023 do 15 grudnia 2023. Jest to znacznie szybsze niż ręczne wprowadzanie każdej daty osobno.
Możemy również tworzyć bardziej złożone sekwencje dat, manipulując parametrami funkcji SEKWENCJA. Na przykład, aby utworzyć sekwencję dat w odstępach tygodniowych, moglibyśmy użyć funkcji SEKWENCJA do generowania liczb, które następnie posłużyłyby jako offset dni od daty początkowej.
Inne praktyczne zastosowania funkcji SEKWENCJA
Funkcja SEKWENCJA ma wiele innych praktycznych zastosowań w codziennej pracy z Excelem. Może być wykorzystana do:
Tworzenia numerowanych list lub indeksów
Generowania tablic do obliczeń macierzowych
Tworzenia sekwencji wartości jako podstawy do funkcji warunkowych
Generowania wartości do testowania formularzy czy makr
Szczególnie przydatne jest połączenie funkcji SEKWENCJA z innymi funkcjami tablicowymi, co pozwala na wykonywanie złożonych operacji na całych zakresach danych jednocześnie, bez konieczności tworzenia pośrednich kolumn lub wierszy.
Porady dotyczące efektywnego korzystania z funkcji SEKWENCJA
Aby w pełni wykorzystać możliwości funkcji SEKWENCJA, warto pamiętać o kilku praktycznych poradach:
Zawsze sprawdzaj, czy nie potrzebujesz zaokrąglić wartości, zwłaszcza gdy pracujesz z małymi krokami
Pamiętaj, że funkcja SEKWENCJA zawsze wypełnia komórki w kolejności od lewej do prawej, od góry do dołu
Jeśli potrzebujesz tylko części sekwencji, możesz użyć funkcji WYBIERZ.PRZEDZIAŁ, aby wyodrębnić potrzebne wartości
Dla bardzo dużych sekwencji uważaj na wydajność — Excel ma ograniczenia co do rozmiaru tablic
Funkcja SEKWENCJA to potężne narzędzie, które znacznie upraszcza wiele zadań w Excelu. Dzięki jej zrozumieniu i umiejętnemu stosowaniu możesz zaoszczędzić dużo czasu i zwiększyć swoją produktywność podczas pracy z arkuszami kalkulacyjnymi.
Oglądając film z kanału YT Goodly o wykresie lejkowym, który formatuje się warunkowo w zależności od komórki danych, w którą klikniemy stwierdziłem, że też chcę stworzyć taki wykres, ale prościej 😉 Przede wszystkim mój lejek miał się znajdować w komórkach Excela, a nie na wykresie. Pomogły mi informacje, jakie zdobyłem tworząc kurs o wizualizacji i Dashboaradach w Excelu. Uzyskałem taki efekt.
Oczywiście powyższy wykres podświetla na czerwono wiersz, w który klikniemy. Jak uzyskać taki efekt?
Przede wszystkim musimy stworzyć poziomy „lejka” w komórkach. Robimy to za pomocą odpowiedniej formuły i zmiany czcionki.
Zaczniemy od formuły – potrzebujemy powtórzyć pewną ilość razy znak pionowej kreski "|". Robimy to za pomocą funkcji POWT.
Musimy tylko tak dopasować ilość powtórzeń, żeby każdy kolejny poziom lejka miał odpowiednią szerokość, czyli normalizujemy liczbę powtórzeń. Załóżmy, że maksymalnie chcemy powtórzyć 60 razy znak "|" – dla najwyższego poziomu. Każdy kolejny poziom ma mieć proporcjonalnie mniej znaków. Czyli wystarczy, że wartość z aktualnego poziomu podzielimy przez maksymalną wartość ze wszystkich poziomów (domyślnie przez wartość z najwyższego/pierwszego poziomu). Oczywiście musimy jeszcze przemnożyć przez 60:
=POWT("|";B4*60/MAX($B$4:$B$8))
Zwróć uwagę, że czcionka formuły wygląda nietypowo. To dlatego, że standardowa czcionka by się tutaj nie sprawdziła – byłyby za duże odstępy pomiędzy poszczególnymi pionowymi liniami. Dlatego w przykładzie wykorzystana jest czcionka Haettenschweiler (możesz m.in. skorzystać też z czcionek: Britannic Bold, Nyala, Playbill, Stencil)
Mamy paski, ale nie przypominają one lejka. Rozwiązanie jest proste – wystarczy wyrównać tekst do środka.
Zanim zaczniemy malować wiersze dołóżmy jeszcze proste formuły, które będą obliczały procentowy spadek do pierwszego i poprzedniego kroku. Są one bardzo podobne:
=1‑B5/$B$4 i =1‑B5/B4
Różnica polega tylko na tym czy w pierwszej formule zablokujemy bezwzględnie odwołanie do komórki B4 czy nie.
Teraz możemy zająć się krokami potrzebnymi do tego, żeby nasz mały Dashboard formatował się na czerwono, gdy klikniemy w komórkę danego wiersza. Niestety będziemy potrzebowali skorzystać z kodu VBA. Na szczęście to bardzo prosty fragment kodu, który musimy umieścić w module arkusza, na którym znajduje się nasz „lejek”.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A1").Value = ActiveCell.Row
End Sub
Powyższy kod VBA uruchamia się za każdym razem, gdy zmienimy zaznaczenie na arkuszu i wstawia do komórki A1 numer wiersza aktywnej komórki.
Może Ci się wydawać, że kod ten nie działa na załączonym pliku, ale wynika to tylko z zastosowanej sztuczki – zmianie formatowania liczbowego komórki A1.
Jeśli zaznaczysz komórkę A1 i naciśniesz skrót Ctrl + 1 zobaczysz, że liczby w komórce zostały sformatowane niestandardowo tak, żeby zawsze pokazywać tekst "Lejek sprzedażowy w komórkach".
Bazując na wartości w komórce A1 tworzymy formatowanie warunkowe (na zakresie naszego lejka – A4:E8) korzystające z formuły:
=$A$1=WIERSZ()
Formuła jest bardzo prosta sprawdza czy aktualny wiersz komórki jest równy wartości przechowywanej w komórce A1 jeśli tak, to nakłada formatowanie. I już gotowe — główne zadanie zostało zrealizowane – lejek zmienia kolor.
My jednak chcemy dołożyć jeszcze parę dodatkowym informacji na naszym mini Dashboardzie. Chcemy podawać liczbę klientów straconych do pierwszego i poprzedniego kroku.
Najczęściej podobne informacje podaje się w polach tekstowych – najpierw w jakiejś odległej (niewidocznej na dashboardzie) komórce wykonuje obliczenia, a potem wstawie pole tekstowe i wpisuje się w pasku formuły, że równa się ono komórce z formułą (nie można wpisać formuły bezpośrednio do pola tekstowego).
W naszym przykładzie robimy ciut inną rzecz – w komórce D2 mamy już jedną z potrzebnych formuł:
ale dodatkowo pod nią (a niejako w niej) znajduje się pole tekstowe z tekstem. Komórce zmieniliśmy kolor czcionki na czerwoną i wyrównaliśmy tekst do góry, a w polu tekstowym zostawiliśmy domyślny kolor czcionki.
W tym przykładzie wygląda to w miarę OK, ale korzystanie z komórki ogranicza położenie wyświetlanych wartości, dlatego częściej stosuje się pole tekstowe, które odwołuje się do komórki z odpowiednią formułą/obliczeniem oraz drugiego pola tekstowego, które funkcjonuje jako podpis.
Na koniec jeszcze jedna czynność często wykorzystywana przy Dashboardach – wyłączanie linii siatki (pole wyboru na karcie Widok).
Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP
Od Excela 2013 pojawia się bardzo ciekawa funkcjonalność jeśli chodzi o etykiety danych na wykresie, a mianowicie możliwość ich pobrania z komórek arkusza. Pokażemy tą możliwość na przykładzie wykresu słupkowego.
W Excelu 2013, żeby dodać etykiety danych, należy kliknąć w zielony plus, a następnie wybrać odpowiedni element wykresu, który chcemy dodać. W naszym przykładzie będą to etykiety na końcu zewnętrznym.
Po ich dodaniu pokażemy jeszcze jedną sztuczkę dostępną od Excela 2013 — zaznaczymy pojedynczą etykietę danych i przytrzymując klawisz Shift (żeby pozostała na tej samej wysokości) przesuniemy ją myszką w bok. Następnie zmienimy parę parametrów tej etykiety, żeby było widoczne, że jest inna od pozostałych etykiet danych.
Teraz naciskamy Ctrl + 1 (wciąż zaznaczona jest zmieniana przez nas etykieta danych) i w zakładce Opcje etykiet klikamy w przycisk Sklonuj bieżącą etykietę danych.
Po tej operacji wszystkie etykiety będą miały takie samo formatowanie jak nasza zmieniona etykieta.
Teraz chcemy dodać dane do etykiet z zakresu komórek. Żeby to zrobić musimy mieć zaznaczone wszystkie etykiety i wtedy w zakładce Opcje etykiet będzie się wyświetlała opcja Wartości z komórek. Wystarczy, że oknie, które się pojawi, zaznaczysz zakres komórek, z których chcesz pobrać wartości do etykiet danych.
Ewentualnie powinniśmy odznaczyć informacje, których nie chcemy, żeby etykiety pokazywały. Teraz mamy etykiety pokazujące wartości z komórek, które są tak samo sformatowane jak te komórki, poza drobnym wyjątkiem, że nie kopiują koloru czcionki.
Jeśli chcesz pokazać wartości na osi wykresu w tysiącach lub milionach potrzebujesz zmienić odpowiednią opcję we właściwościach osi wykresu.
Najpierw zaznacz oś , a następnie upewnij się, że jesteś na zakładce Opcje osi. Następnie musisz odnaleźć listę rozwijaną Jednostki wyświetlania i wybrać z niej jak chcesz zmodyfikować sposób wyświetlania liczb na osi, czyli czy np: chcesz je pokazać w tysiącach czy milionach.
Możesz ewentualnie odznaczyć pole wyboru (checkbox) Pokaż jednostki wyświetlania na wykresie, jeśli nie chcesz, żeby obok osi pokazywała się informacja, że pokazywane liczby to miliony, jeśli zmodyfikowałeś inaczej ich formatowanie.
Dzięki tym wartościom łatwo obliczymy straty pod osią y:
=JEŻELI(C4<0;C4+G4;0)
oraz zyski nad osią y:
=JEŻELI(C4>0;C4+H4;0)
Później musisz wstawić wykres kolumnowy skumulowany na podstawie tych obliczeń.
Pomalować podstawy na brak wypełnienia,a pozostałe serie na taki kolor jaki chcesz.
Na koniec możesz jeszcze usunąć zbędne serie z legendy klikając na nie i klikając Delete.
P.S.
Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić to napisz do mnie. Ja w miarę możliwości odpowiem na Twoje pytanie.
