Porady

odwołania wieloznaczne w Excel

Symbole i odwołania wieloznaczne

Jest kilka funkcji, które w Excelu są używane nadzwyczaj często. Są to przede wszystkim WYSZUKAJ.PIONOWO, SUMA.JEŻELI, LICZ.JEŻELI. Te funkcje są niezwykle użyteczne i elastyczne. Ale i one czasem zawodzą. Są takie sytuacje, gdy trzeba pokombinować trochę bardziej.

Sprawdź funkcje Excel, które musisz znać!

Chciałbyś/chciałabyś, aby Twoje ulubione funkcje były jeszcze bardziej elastyczne? Chcesz łatwiej rozwiązywać na pozór skomplikowane problemy? Na szczęście jest na to sposób. Poznaj odwołania wieloznaczne oraz symbole wieloznaczne.

Symbole wieloznaczne w Excelu

Są takie trzy znaki w Excelu, które są traktowane przez program jako znaki specjalne.

znaki specjalne w Excel

  • Gwiazdka „*” – zastępuje dowolny ciąg znaków przylegający do tekstu. Na przykład tekst „*a” jest rozumiany przez Excela jako dowolny ciąg znaków kończący się literą „a”. Może to być „gruszka lub „Ala.
  • Znak zapytania „?” – działa podobnie do znaku „*”, z tą różnicą, że zastępuje tylko jeden znak. Na przykład „?a” może odpowiadać „ma”, „1a” lub „la”.
  • Tylda „~” – kasuje efekt działania znaków specjalnych „*” oraz „?”. Dzięki „~” możemy traktować znaki specjalne jako zwykłe znaki w tekście.

Sprawdź konkretne przykłady jak używać odwołania wieloznaczne za pomocą tych trzech magicznych znaków.

Ale najpierw –> Pobierz plik Excel i spróbuj wykonać wszystkie ćwiczenia samodzielnie <–

Rozpoznawanie imion żeńskich w formule

W tym przykładzie mamy prostą tabelę imion i przypisanych im kwot. Mamy za zadanie wyznaczyć sumę kwot odpowiadającą imionom żeńskim i oddzielnie imionom męskim.

tabela Excel

 

Tabela jest mała, więc z łatwością możesz obliczyć szukaną wartość „na palcach” lub używając prostej funkcji SUMA. Takie rozwiązanie nie będzie jednak elastyczne i sprawdzi się tylko w tym konkretnym przykładzie. Jeśli będziemy chcieli przenieść je na większą tabelę, to pojawi się problem.

funkcja suma Excel

Innym sposobem jest dodanie kolumny z oznaczeniem płci K = Kobieta i M = Mężczyzna obok imion. W ten sposób będziesz mógł/mogła użyć standardowej funkcji SUMA.JEŻELI do zsumowania wartości dla konkretnych płci. Jest to dobre rozwiązanie o ile potrafisz szybko i sprawnie dodać oznaczenie płci. W małej tabeli możesz zrobić to „ręcznie”. Jeśli natomiast chciałbyś/chciałabyś użyć do tego formuły, to znów wracamy do punktu wyjścia. Czyli do naszego pierwotnego problemu rozpoznania, które imiona są żeńskie a które męskie.

suma.jeżeli Excel

Porady i przykłady jak używać funkcji SUMA.JEŻELI znajdziesz na naszych kursach on-line Excel Średnio Zaawansowany oraz Excel Zaawansowany. Ale skoro ten poradnik jest o symbolach specjalnych i odwołaniach wieloznacznych, to użyję ich do zbudowania rozwiązania tego problemu 😊

Na początku jednak drobna obserwacja, która pomoże nam zbudować odpowiednią formułę. Polskie imiona żeńskie kończą się literą „a”. Przynajmniej te tradycyjne. Mogą się zdarzyć jakieś wersje imion typu „Nikol” lub temu podobne. Ograniczmy się jednak do stwierdzenia, że polskie imiona żeńskie kończą się literą „a” 😊.

Dzięki temu możemy użyć konstrukcji „*a”, która będzie tłumaczona przez Excel na dowolny ciąg znaków kończący się literą „a”. Więc taka fraza będzie odpowiadała wszystkim imionom żeńskim.

Teraz jesteśmy już gotowi do zbudowania formuły na bazie funkcji SUMA.JEŻELI. Sumujemy wszystkie imiona żeńskie, czyli takie, które kończą się na literę „a”.

=SUMA.JEŻELI(Imiona_1[Imię]; „*a”; Imiona_1[Kwota])

 

Suma.jeżeli z odwołaniem wieloznacznym w Excel

Proste, prawda? Najważniejsze jednak jest to, że formuła jest elastyczna. Dodatkowo nie musimy dodawać żadnych nowych kolumn ani modyfikować naszych danych.

Problem z niepotrzebnymi spacjami

Często dane pobrane z zewnętrznych źródeł nie są najwyższej jakości. Mają wiele błędów i nieprawidłowości, które mogą skutecznie utrudnić stosowanie standardowych formuł. Jednym z takich problemów są zbędne spacje na końcu słowa.

tabela Excel

Na pierwszy rzut oka takie dane wyglądają ok. Dlatego, że znaków spacji nie widać. Jeśli jednak przyjrzymy się tym imionom bliżej, to można zauważyć, że na końcu niektórych faktycznie znajduje się jedna lub więcej spacji.

niepotrzebne spacje Excel

Chcemy policzyć wszystkie wystąpienia imienia Agata oraz imienia Łukasz. Użyjemy oczywiście funkcji LICZ.JEŻELI. Jako kryterium zastosujemy odpowiednie imię.

Licz.jeżeli w Excel

Jednak słowo „Agata” nie jest tym samym co „Agata   „ (z 3 spacjami na końcu). Excel rozpozna jedynie dokładne wystąpienia szukanych słów. Dlatego będzie więc nieprawidłowy.

Rozwiązaniem tego problemu jest dodanie znaku „*” na końcu imienia. W ten sposób zastąpimy wszystkie potencjalne spacje na końcu imienia. Będziemy wyszukiwać

[@Imię]&”*”

licz.jeżeli z odwołaniem wieloznacznym w Excel

To były przykłady z zastosowaniem znaku specjalnego „*” do budowania odwołań wieloznacznych. Przejdźmy teraz do pozostałych dwóch symboli.

Odwołania wieloznaczne w filtrach

Poniższa tabela zawiera proste zestawienie zamówień. Każdy rekord zawiera numer dokumentu sprzedaży oraz kwotę.

tabela Excel

Wiemy, że konwencja tworzenia numerów dokumentów jest następująca:

  • na początku „PAR” lub „VAT” dla paragonów lub faktur VAT odpowiednio.
  • ukośnik „/”
  • kombinacja 6 dowolnych znaków „xxxxxx”. Dopuszczalne są cyfry, litery i znaki specjalne.
  • ukośnik „/”
  • na końcu rok pisany w konwencji czterocyfrowej „rrrr”.

Naszym zadaniem jest zastosowanie filtra, który będzie wyświetlał wszystkie paragony wystawione w 2019 roku. Jak podejść do rozwiązania tego problemu?

Zobaczmy jak wygląda numer każdego z szukanych dokumentów:

PAR/xxxxxx/2019

gdzie „xxxxxx” są zupełnie dowolne.

odwołania wieloznaczne w filtrze Excel

Na szczęście wiemy, że jeden dowolny znak możemy zapisać używając symbolu specjalnego „?”. Przekładając numer szukanych dokumentów na język rozpoznawany przez odwołania wieloznaczne dostajemy:

PAR/??????/2019

Użyjemy dokładnie takiej frazy w filtrze numeru dokumentu. Teraz spróbuj założyć filtr i wyświetlić inne dokumenty. Potrafisz znaleźć faktury VAT z 2018 roku?

Wyszukiwanie tekstu zawierającego symbol wieloznaczny

W tym przykładzie mamy tabelę zawierającą słowa klucze oraz przypisane do nich kwoty. Tekst-klucz może zawierać również liczby i znaki specjalne. W tym symbole wieloznaczne.

Naszym zadaniem jest wyszukanie kwoty odpowiadającej słowu „Aga*”.

tabela Excel

Naturalnie próbujemy użyć standardowej funkcji WYSZUKAJ.PIONOWO. Jednak wtedy Excel potraktuje symbol „*” jako odwołanie wieloznaczne. Dlatego potraktuje słowo „Agata” jako pierwsze na liście spełniające kryteria wyszukiwania „Aga*” i zwróci nieprawidłową wartość.

wyszukaj pionowo w Excel

Jak sobie z tym poradzić? Na szczęście jest symbol „~”, który kasuje wieloznaczność symboli „*” i „?”. Możesz więc dodać na końcu „~*”, aby wymusić, by funkcja szukała słowa z symbolem „*” na końcu.

wyszukaj pionowo z odwołaniem wieloznacznym w Excel

Trudniejszym przykładem będzie wyszukanie wartości dla słowa „Aga?a”. Wierzę, że sobie poradzisz. W razie problemów lub pytań zaprasza na naszą grupę Facebook. Rozwiązujemy tam takie i podobne zadania.

 

Elastyczne Znajdź/Zamień a odwołania wieloznaczne

Symbole specjalne mogą Ci też pomóc, jeśli chcesz użyć polecenia Znajdź/Zamień. To bardzo popularne i użyteczne polecenie ma też niestety swoje ograniczenia. W standardowym zastosowaniu wyszukujemy konkretnego ciągu znaków i zastępujemy go innym konkretnym ciągiem znaków. A co zrobić w sytuacji opisanej poniższym przykładem?

Niektórzy ludzie nie do końca wiedzą jak zapisać nazwę marki Turbo Excel. Niektórzy używają łączników takich jak myślnik, kropka, podkreślenie, znak zapytania lub inne. Chcemy szybko i łatwo zamienić wszystkie sposoby pisowni na jeden poprawny Turbo Excel (ze spacją).

TurboExcel

W tym celu użyjmy polecenia Znajdź/Zamień (skrót CTRL + H). Ale zamiast zastępować oddzielnie każdy ze znaków „-„, „.”, „_”, „?” spacją zastąpimy je wszystkie za jednym razem.

Znajdź i zamień z odwołaniem wieloznacznym w Excel

Wystarczy znaleźć frazę „Turbo?Excel” i zamienić ją na „Turbo Excel”. Wtedy Excel automatycznie potraktuje „?” jako znak specjalny i wstawi w jego miejsce dowolny znak.

Odwołania wieloznaczne w formatowaniu warunkowym

W tym przykładzie zobaczysz jak za pomocą formatowania warunkowego wyróżnić jedynie imiona żeńskie na liście imion.

formatowanie warunkowe w Excel

Funkcjonalność formatowania warunkowego jest szczegółowo omówiona na naszych kursach on-line Excel Średnio Zaawansowany oraz Excel Zaawansowany. Możesz rozbudować lub odświeżyć swoją wiedzę na bazie licznych przykładów i ćwiczeń.

Jak się zapewne domyślasz, w tym przykładzie użyjemy symbolu specjalnego i odwołania wieloznaczne, aby zbudować odpowiednią formułę.

=JEŻELI(LICZ.JEŻELI(B4; „*a”); PRAWDA; FAŁSZ)

Zwróć uwagę, że wewnętrzna funkcja LICZ.JEŻELI(B4; „*a”) zwraca 1 jeśli imię kończy się na „a” oraz 0 w przeciwnym przypadku.

formatowanie warunkowe z odwołaniem wieloznacznym w Excel

Na koniec wystarczy użyć malarza formatów, aby skopiować formatowanie na całą kolumnę w tabeli.

malarz formatów Excel

–> Na koniec pobierz plik z kompletnym rozwiązaniem i przeanalizuj je jeszcze raz. <–

Jeśli szukasz więcej praktycznych przykładów i porad, to zapraszam na kursy online. Szczególnie przydatne będą kurs Excel Średnio Zaawansowany oraz Excel Zaawansowany. To kompleksowe kursy wideo naszpikowane praktycznymi przykładami. Wynieś swoją wiedzę z Excela na zupełnie inny poziom!

interaktywny wykres Excel

Jak wstawić interaktywny wykres Excel?

W tym poradniku pokażę Ci jak zbudować interaktywny wykres Excel. Co to właściwie znaczy „wykres interaktywny”? Możemy przyjąć, że wykres jest interaktywny, gdy możemy nim w pewien sposób sterować. Na przykład za pomocą dodanych kontrolek formularza takich jak przyciski pokrętła:

interaktywny wykres - przycisk pokrętła

lub pola wyboru:

interaktywny wykres - pole wyboru

Oczywiście można zbudować wykres, który będzie się zmieniał w zależności od innych kontrolek takich jak paski przewijania czy pola opcji. Możliwości jest bardzo dużo. Teraz zobacz jak po kolei taki wykres przygotować.

Rozwiązanie tego problemu było zadaniem jednego z turbo-wyzwań Excel. Dołącz do kolejnych wyzwań i zgarnij atrakcyjne nagrody. Wystarczy, że dołączysz do naszej turbo-grupy na facebooku.

 

 

Stan wyjściowy – wykres typu kombi

Zacznijmy od tabelki z danymi i wykresu typu kombi wstawionego na ich podstawie.

Wykres typu kombi Excel

Jest to standardowy wykres, który wyświetla wszystkie wybrane serie bez możliwości sterowania przez użytkownika.

Pobierz plik Excel z powyższymi danymi i spróbuj wykonać ćwiczenie samodzielnie.

Kopia tabeli danych

Pierwszym krokiem jest zrobienie kopii tabeli danych.

tabela danych Excel

Nowy wykres interaktywny wstawimy na tej właśnie kopii. Dzięki temu nie będziemy modyfikować oryginalnych danych.

Wstawianie kontrolki formularza

Wybraną kontrolkę formularza wstawisz z zakładki Deweloper na wstążce. Ja wybrałem przycisk pokrętła.

wstaw kontrolkę formularza Excel

To dopiero pierwszy krok. Teraz musimy połączyć kontrolkę z odpowiednimi wartościami i na koniec z wykresem.

Atrybuty kontrolki formularza

Każda kontrolka formularza ma swoje atrybuty. Aby je wyświetlić kliknij prawym przyciskiem myszy i wybierz polecenie formatuj formant.

atrybuty kontrolki formularza

W przypadku przycisku pokrętła będziemy modyfikować Wartość minimalną, maksymalną, zmianę przyrostową oraz łącze komórki. Dzięki temu będziemy mogli zarządzać formantem i ostatecznie podłączyć go do naszego wykresu.

atrybuty przycisku pokrętła Excel

Łącze komórki kontrolki formularza

Na naszym wykresie chcemy wyświetlać jedną z trzech serii (lub grup serii) w jednym czasie. Dlatego stwórzmy prostą listę trzech wartości. Następnie ustawmy atrybuty kontrolki jak poniżej:

ustawienia atrybutów przycisku pokrętła Excel

Dzięki temu nasz przycisk pokrętła będzie przyjmował wartości 1-3, które odpowiadają kolejnym pozycjom listy. Aktualny wybór widzisz w komórce, którą wybrałeś jako łącze komórki.

aktualna wartość przycisku pokrętła

Podłączanie danych do przycisku pokrętła

Jesteśmy już gotowi, aby podłączyć nasze dane do przycisku pokrętła. Zrobimy to w skopiowanej tabeli danych. Nowa tabela będzie wyświetlała wartość z oryginalnej tabeli tylko wtedy, gdy przycisk pokrętła będzie ustawiony w odpowiedniej pozycji.

zmiana serii danych w zależności od przycisku pokrętła

Do tego użyjemy formuły:

=JEŻELI($N$3=1; C3; BRAK())

Użycie funkcji BRAK() gwarantuje nam wyświetlenie (konkretnego) błędu dla serii, których w danej chwili nie chcemy wyświetlać.

Wstawianie interaktywnego wykresu

Jesteśmy już gotowi do wstawienia wykresu na podstawie naszej zmodyfikowanej tabeli danych, która zmienia się w zależności od ustawienia przycisku pokrętła.

interaktywny wykres Excel

Ukryj błędne serie

Na koniec pokażę Ci jak ustawić wykres, aby nie wyświetlał błędnych serii. Chcemy, aby takie serie w ogóle nie były widoczne na wykresie. Standardowo, mogą one być dalej wyświetlane jako zera. Wtedy widzimy niepotrzebne linie na dole wykresu.

ukryj błędne dane na wykresie

Można to łatwo zmienić. Wystarczy przejść do Zaznacz dane –> Ukryte i puste komórki. Następnie zaznaczyć opcję Pokaż błąd #N/A jako pustą komórkę oraz pokaż puste komórki jako odstępy.

Gratulacje! Twój interaktywny wykres Excel jest już gotowy 😊

Pobierz plik z kompletnym rozwiązaniem i przeanalizuj je jeszcze raz.

 

Jeśli szukasz więcej przykładów, to zapraszam na kursy online. Szczególnie przydatne będą kurs Excel Średnio Zaawansowany oraz Excel Zaawansowany. To kompleksowe kursy wideo naszpikowane praktycznymi przykładami. Wynieś swoją wiedzę z Excela na zupełnie inny poziom!

automatyzacja Excel z VBA

5 sposobów na automatyzację Excela z VBA

Pracujesz w biurze i co dzień, co tydzień lub co miesiąc powtarzasz te same czynności? Kopiuj-wklej dane. Załóż, zdejmij filtr. Przeciągnij dobrze znaną formułę po raz setny lub nawet tysięczny. Znasz to z własnych doświadczeń?

Czasy, gdy wszystko trzeba było robić ręcznie już dawno minęły. Dzisiaj czas jest na wagę złota. Dlatego każdy szuka sposobów, aby swoją codzienną pracę wykonywać sprawniej, szybciej i efektywniej. Również tą pracę w Excelu.

Narzędzie MS Excel oferuje mnóstwo funkcjonalności i opcji do automatyzacji Twojej pracy. Wystarczy, że wyciągniesz po nie rękę i zaoszczędzisz trochę swojego cennego czasu. Tym razem pokażę Ci kilka sposobów na taką automatyzację przy pomocy VBA.

1. Tworzenie własnych funkcji

Korzystasz często z kombinacji tych samych funkcji? Napisanie prostej funkcji w VBA to chyba jeden z najprostszych sposobów na ułatwienie swojej pracy w Excelu. Spójrz na przykład jednej z najczęściej używanej kombinacji funkcji INDEKS i PODAJ.POZYCJĘ. Już w najprostszym zastosowaniu formuła może przysparzać o ciarki. Jest długa i skomplikowana.

własna funkcja w Excel

Sprawdź najbardziej popularne funkcje w Excel, które musisz znać.

 

Dlatego polecam zamienić ją na własną funkcję. Teraz wygląda to dużo prościej, prawda? 😊

własna funkcja w VBA

Najprostszy kod VBA tej funkcji znajdziejsz poniżej.

Function IdxMatch(tabela As Range, idx_wierszy, idx_kolumn)
   Dim wartosc, pozycja_w As Integer, pozycja_k As Integer
   pozycja_w = Application.WorksheetFunction.Match(idx_wierszy, tabela.Columns(1), 0)  'indeks wiersza
   pozycja_k = Application.WorksheetFunction.Match(idx_kolumn, tabela.Rows(1), 0)      'indeks kolumny
   wartosc = Application.WorksheetFunction.Index(tabela, pozycja_w, pozycja_k)         'wynik funkcji indeks
   IdxMatch = wartosc
End Function

Pamiętaj, że to tylko jeden z wielu przykładów na ułatwienie sobie życia za pomocą własnej funkcji w VBA. Masz jakieś swoje?

2. Rejestrowanie powtarzalnych procesów

Czas na kolejny przykład, który aż się prosi o automatyzację. Wyobraź sobie, że raz na miesiąc lub kwartał dostajesz zestawienie danych. Jakiś standardowy zrzut z systemu (sprzedażowego, raportowego, …). Twoim pierwszym zadaniem jest założenie filtra, lub kilku filtrów na te dane i wybranie błędnych rekordów. Trafiają one do innej osoby do wyjaśnienia.

rejestrator makr w Excel

Niby nic specjalnego. Zadanie jak każde. Jednak jeśli takich zadań nazbiera się 10 lub 20, to stracisz kilka godzin na bezsensowne czynności, które można łatwo zautomatyzować.

– Ale ja nigdy nie napisałem żadnego makra w VBA! Jak ma to zrobić?

Okazuje się, że do automatyzacji takich czynności wcale nie potrzebujesz znajomości języka VBA. Wystarczy, że skorzystasz z rejestratora makr.

rejestrator makr Excel

Wybierz polecenie Zarejestruj makro i wymyśl nazwę dla swojego makra. Od teraz każda Twoja czynność jest rejestrowana. Po wykonaniu wszystkich kroków wybierz Zatrzymaj rejestrowanie.

rejestrator makr Excel

Następnym razem jak będziesz musiał powtórzyć te same czynności wystarczy, że uruchomisz swoje makro.

uruchom makro Excel

Zaoszczędzony czas możesz przeznaczyć na coś dużo ciekawszego niż przeklejanie danych 😊

Dodawanie przycisków i skrótów klawiszowych

Napisałeś(aś) lub zarejestrowałeś(aś) swoje makro. Teraz wystarczy, że przejdziesz na listę makr, odnajdziesz to właściwe i uruchomisz je. Proste, prawda? Ale może być jeszcze prościej 😊

Dodaj przycisk i przypisz do niego wybrane makro. Dzięki temu nie będziesz musiał szukać go na liście (jeśli takich makr masz kilka). Nie będziesz musiał(a) nawet pamiętać jak je nazwałeś(aś).

przypisz makro do przycisku Excel

Tworzenie inteligentnych formularzy

Wchodzimy w opcję dla trochę bardziej zaawansowanych. Ale spokojnie, w końcu po to uczyć się Excela, prawda? 😉

Rozwiązanie oparte na tworzeniu własnych inteligentnych formularzy jest skierowane głównie do tych, którzy chcą mieć większą kontrolę nad skoroszytem. Dzięki takim formularzom można ograniczyć użytkownikom dostęp do konkretnych komórek minimalizując jednocześnie ryzyko błędu lub niepożądanej zmiany. Zamiast tego, użytkownik pliku wprowadza swoje parametry w formularzu, który jest mu wyświetlany.

tworzenie formularzy w Excel

Poszczególne kontrolki (przyciski, pola tekstowe, …) trzeba odpowiednio zaprogramować. Jednak jest to na tyle proste, że poradzisz sobie z tym nawet z minimalną znajomością VBA.

Dodawanie treści, które same się odświeżają

Bardzo przydatne w pracy z dużymi plikami są spisy treści. Dzięki nim wiemy jaka jest zawartość każdego arkusza. Jeśli dodatkowo mamy do dyspozycji hiper linki, to z łatwością możemy poruszać się po pliku.

tworzenie spisu treści w Excel

Stworzenie takiego spisu treści jest stosunkowo proste. Trudności zaczynają się, jeśli chcemy, aby taki spis aktualizował się za każdym razem, gdy wyświetlamy ten arkusz.

Wtedy niezbędne jest napisanie procedury, która tworzy taki spis treści. Następnie dodanie prywatnej procedury w tym konkretnym arkuszu, która będzie wywoływać procedurą tworzącą 😊

Private Sub Worksheet_Activate()
'Procedura uruchamia procedurę tworzenia spisu treści przy każdorazowym aktywowaniu tego arkusza

Call SpisTresci

End Sub

Pamiętaj. Jeśli masz dość powtarzalnych czynności w Excelu i chcesz zaoszczędzić trochę czasu, to próbuj automatyzować. Przecież nie chodzi o to, aby się na pracować. Tylko o to, aby zadanie zostało wykonane. Dlatego pracuj mądrze.

Jeśli szukasz więcej przykładów, to zapraszam na kursy online. Szczególnie przydatne będą kurs Excel Średnio Zaawansowany oraz Excel Zaawansowany. To kompleksowe kursy wideo naszpikowane praktycznymi przykładami. Wynieś swoją wiedzę z Excela na zupełnie inny poziom!

 

wyzwanie Excel TurboExcel formuły i funkcje

Jak sumować tylko N największych wartości

Użyteczność standardowej funkcji SUMA kończy się wtedy, gdy potrzebujemy sumować jedynie wybrane wartości spełniające pewne kryteria. Dokładnie tak jest w naszym przykładzie.

Mamy za zadanie obliczyć łączną liczbę pasażerów podróżujących londyńskim metrem w godzinach szczytu. Ustaliliśmy, że za godziny szczytu przyjmiemy siedem godzin z największym ruchem. Musimy zatem wyznaczyć sumę jedynie siedmiu największych wartości z każdej kolumny dla dni tygodnia poniedziałek – piątek. Wartości te zostały zaznaczone na zielono za pomocą formatowania warunkowego.

wyzwanie Excel - funkcje i formuły

Zastosowanie funkcji SUMA oraz ręczne wybranie komórek nie wchodzi w grę. Takie rozwiązanie jest po prostu za mało elastyczne. Musi więc znaleźć lepsze rozwiązanie. Okazuje się, że takich rozwiązań jest kilka. Sprawdź szczegóły poniżej.

Pobierz plik Excel z rozwiązaniem tego przykładu.

1. JEŻELI oraz MAX.K

W pierwszym rozwiązaniu zbudujemy formułę złożoną z dwóch funkcji: SUMA.JEŻELI oraz MAX.K.

=SUMA.JEŻELI(pon;”>=” & MAX.K(pon; 7))

gdzie „pon” jest nazwą obszaru „B5:B:28”.

Wewnętrzna funkcja MAX.K(pon; 7) zwraca siódmą największą wartość we wskazanym zakresie. Natomiast funkcja SUMA.JEŻELI sumuje jedynie te wartości, które są od niej większe lub równe. Czyli dokładnie o to nam chodziło.

Możemy jeszcze bardziej uelastycznić tą formułę ustawiając 7 jako dodatkowy parametr

Wyzwanie turboExcel rozwiązanie 1

2. SUMA.ILOCZYNÓW oraz POZYCJA.NAJW

Znów użyjemy kombinacji dwóch funkcji, ale tym razem będą to: SUMA.ILOCZYNÓW oraz POZYCJA.NAJW.

=SUMA.ILOCZYNÓW(pon*(POZYCJA.NAJW(pon; pon)<=7))

Funkcja POZYCJA.NAJW zwraca pozycję danej liczby na liście liczb. Jeśli zastosujemy tą funkcję do całego wektora „pon”, to każdej liczbie z tego zakresu będzie przypisana jej pozycja. Wystarczy wybrać jedynie te, które są na co najmniej siódmej pozycji. Postępując w ten sposób otrzymamy wektor logiczny: {FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;PRAWDA;…

Wyzwanie turboExcel rozwiązanie 2

Na koniec użyjemy funkcji SUMA.ILOCZYNÓW, aby zsumować wektor powstały z przemnożenia naszego wektora danych „pon” oraz wektora logicznego. Dzięki temu dostaniemy szukaną wartość.

3. Inne kombinacje funkcji

Możesz też użyć innych kombinacji funkcji z dwóch opisanych rozwiązań np. SUMA.ILOCZYNÓW oraz MAX.K. Spróbuj pokombinować. Sprawdź, które rozwiązanie jest dla Ciebie najbardziej intuicyjne.

4. Własna funkcja napisana w VBA

Jeśli często używasz kombinacji tych samych funkcji, np. takich jak opisane wcześniej, możesz napisać własną funkcję w VBA. Dzięki temu będziesz mógł łatwiej i szybciej znaleźć lub obliczyć szukaną wartość.

Sprawdź przykład takiej funkcji poniżej

Function GetXLargest_Vec(Ref_Tab As Range, Ref_Num As Integer) As Long
   'Ta funkcja działa poprawnie jeśli wejściowa tablica Ref_Tab jest wektorem danych/kolumną

    Dim X_Large As Long
    Dim Temp_Cell As Range
    Dim Result As Long

    'Określ wartość numer X (Ref_Num) w zbiorze danych Ref_Tab
    X_Large = Application.WorksheetFunction.Large(Ref_Tab, Ref_Num)
    'funkcja LARGE jest angielską wersją funkcji MAX.K

    Result = 0
    For Each Temp_Cell In Ref_Tab               'Pętla przechodzi przez wszystkie komórki w zakresie Ref_Tab
        If Temp_Cell >= X_Large Then            
            Result = Result + Temp_Cell         'i sumuje te komórki, które są co najmniej takie jak X_Large
        End If
    Next Temp_Cell
        
    GetXLargest_Vec = Result

End Function

Oczywiście taką funkcję możesz poprawić, aby była jeszcze bardziej elastyczna. Dobrze by było, gdyby potrafiła zwrócić od razu sumę N największych wartości dla każdej kolumny z zakresu. Potrafisz to zrobić?

Pobierz plik Excel z rozwiązaniem tego przykładu.

Jeśli szukasz więcej przykładów, to zapraszam na kurs online. Podczas kursu Excel Średnio Zaawansowany lub Excel Zaawansowany dowiesz się jak rozwiązywać podobne problemy za pomocą skomplikowanych i zagnieżdżonych formuł..

5 przydatnych wykresów w Excelu

Większość osób korzysta tylko z wykresów słupkowych lub liniowych. Niektórzy od czasu do czasu wstawią wykres kołowy lub punktowy. Na tym znajomość wykresów w Excelu się kończy.

Warto wiedzieć, że ten program oferuje na prawdę dużo więcej. Każdy z typów wykresów ma za zadanie pomóc w prezentacji innych zestawów danych. Dlatego warto wiedzieć z jakich typów wykresów możemy korzystać i kiedy stosować każdy z nich.

Wykresy typu KOMBI

To tak na prawdę nic innego jak dwa różne typy wykresów w jednym. Na przykład część serii danych zaprezentowana za pomocą wykresu słupkowego a inna za pomocą liniowego lub punktowego. 

Poniższy wykres jest przykładem zastosowania wykresu typu kombi, gdy chcemy dodatkowo na naszym wykresie zaprezentować punkt odniesienia. W naszym przypadku jest to zestawienie sprzedaży w kolejnych miesiącach (dane bazowe; niebieski wykres słupkowy) oraz zakładany plan sprzedaży (dane odniesienia; czerwony wykres punktowy).

Sprawdź inne typy wykresów w kursie Średnio Zaawansowanym lub Zaawansowanym.

wykres kombi TurboExcel

Wstawianie wykresów typu kombi nie odbywa się automatycznie i wymaga pewnej wiedzy. Najprostszym sposobem jest wstawienie zwykłego wykresu, w naszym przypadku słupkowego. Następnie należy przejść do zmiany typu wykresu.

wykres kombi TurboExcel

Teraz trzeba wybrać oś pomocniczą dla jednej z serii, w naszym przypadku jest to plan sprzedaży. Wtedy możemy zmienić typ wykresu dla wybranej serii. Możesz ustawić np. wykres kolumnowy dla miesięcznych danych sprzedaży, a punktowy dla planu sprzedaży.

Innym przykładem zastosowania wykresu typu kombi jest dodanie podsumowania dla prezentowanych danych. Jest to przydatne, gdy chcemy zaprezentować zarówno procentowy rozkład danej wielkości (w naszym przypadku sprzedaż poszczególnych produktów w kolejnych latach) jak i podsumowanie dla tych wartości (w naszym przypadku łączną wartość sprzedaży w kolejnych latach). Zobacz jak to wygląda na wykresie.

wykres kombi TurboExcel

Tak samo jak w poprzednim przykładzie, aby otrzymać wykres kombi należy dodać oś pomocniczą i wybrać inny typ wykresu dla wybranej serii danych (z podsumowaniem).

wykres kombi TurboExcel

Wykresy Bąbelkowe

Ten typ wykresu jest uznawany za trudny w użyciu i dlatego jest rzadko stosowany.  Szkoda, bo jest on bardzo skuteczny do analizy porównawczej. Na wykresie bąbelkowym można zaprezentować dane w zależności od trzech wybranych cech. Dwie z nich wyznaczają osie, a trzecia wielkość bąbelka. 

Poniższy przykład to prosta analiza rynku. Na wykresie porównaliśmy zysk netto konkurencyjnych firm (szerokość bąbelka) w zależności od liczby sprzedanych produktów (oś pozioma) oraz średniej ceny produktu (oś pionowa). Dzięki takiej prezentacji danych możemy sprawdzić jak nasza firma plasuje się na tle konkurencji. Dodatkowo możemy przeanalizować wysokość sprzedaży i średnią cenę produktów dla poszczególnych firm. 

wykres bąbelkowy TurboExcel

Wykresy Kaskadowe

To kolejny bardzo przydatny wykres. Warto go używać w dwóch sytuacjach:

  • do prezentacji struktury danych lub
  • do prezentacji zmiany wartości pomiędzy dwoma okresami.

Pierwszy przykład to przedstawienie struktury zysków/strat w rozbiciu na poszczególne miesiące kalendarzowe. Wartości z kolejnych kroków łącznie składają się na zysk z całego roku 2018.

wykres kaskadowy TurboExcel

Drugi przykład to analiza zmiany wysokości naszych oszczędności na przestrzeni roku 2018. W ten sposób możemy sprawdzić od jakiego poziomu wychodziliśmy, jakie wartości zwiększyły nasze oszczędności (wpływy) oraz które je uszczupliły (wydatki).

wykres kaskadowy TurboExcel

Sumy zaznaczone kolorem szarym nie wyświetlają się automatycznie w ten sposób. Należy zmienić sposób wyświetlania ręcznie. W tym celu należy kliknąć prawym przyciskiem myszy na wybrany punkt danych (nie na całą serię tylko jeden punkt) i wybrać polecenie Ustaw jako sumę.

dodaj sumę w wykresie kaskadowym TurboExcel

Wykresy Radarowe

Kolejny trudny, a przez to rzadko używany wykres. Wykres radarowy może być pomocny w analizie porównawczej, gdy mamy co najmniej 3 cechy porównywanych obiektów.

W poniższym przykładzie porównujemy kandydatów do pracy w zależności od czterech wybranych cech: komunikatywność, umiejętności techniczne, wykształcenie oraz doświadczenie. Wykres radarowy jest swojego rodzaju mapą umiejętności dla każdego kandydata. Jednocześnie stanowi dobre porównanie wszystkich kandydatów jednocześnie.

wykres radarowy TurboExcel

Wykresy w komórkach?

Jeśli potrzebujesz prostych wykresów tylko na potrzeby szybkiej analizy lub porównania danych w jednej kolumnie Twojej tabeli, to nie musisz wstawiać "standardowego" wykresu. Możesz skorzystać z wykresów przebiegu w czasie.

wykres przebiegu w czasie Excel

Polecenie jest dostępne w zakładce Wstawianie na wstążce. Możesz wybierać pomiędzy liniowym, słupkowym lub wykresem typu zysk/strata. Jedyne co musisz zrobić, to wskazać zakres danych, dla których chcesz wstawić wykres oraz komórkę, w której będzie on dodany.

wykres przebiegu w czasie Excel

Używałeś już tych typów wykresów? Jeśli nie, to nie trać czasu i spróbuj je wstawić w swoim arkuszu.

Jeśli szukasz więcej zaawansowanych wykresów Excel, to zapraszam na kurs Excel Średnio Zaawansowany lub Excel Zaawansowany.

5 przykładów użycia tabel przestawnych

Wiele osób twierdzi, że nie ma bardziej potężnej i elastycznej funkcjonalności w Excelu niż tabele przestawne. Ja również się z tym zgadzam. Tabele przestawne dają dużo możliwości, pozwalają przygotować podsumowania i przeprowadzić analizy danych za pomocą kilku kliknięć myszką. 

To właśnie ta z funkcjonalności, które zdecydowanie musisz znać. Dlatego też lekcje na temat tabel przestawnych pojawiają się w kilku naszych kursach Excel. Poniższa lista zawiera 5 przykładów zastosowania tabel przestawnych. Sprawdź czy już z nich korzystasz w swojej pracy.

Zliczanie rekordów

Prostym zastosowaniem tabeli przestawnej jest zliczanie rekordów z tabeli. W tym przykładzie zliczamy ilość zamówień dla poszczególnych linii produktowych. Ale możesz w ten sposób podsumować dowolne inne dane. W tym celu wystarczy

  • stworzyć nową tabelę przestawną na podstawie tabeli danych
  • dodać indeks wierszy (w naszym przypadku linia produktowa)
  • dodać dowolne pole tekstowe do obszaru wartości, wtedy Excel automatycznie wyświetli liczbę elementów zamiast sumy
  • możesz też użyć pola liczbowego, ale w takim przypadku musisz zmienić rodzaj podsumowania z sumy na liczbę.
Zliczanie rekordów tabela przestawna

Wyświetlanie wartości jako udziału procentowego w całości 

W wielu podsumowaniach możesz potrzebować informacji o procentowym udziale danej wielkości w wybranym rozbiciu. Podobnie jak w poprzednim przykładzie pokazujemy podsumowanie liczby zamówień dla poszczególnych linii produktowych. Natomiast tym razem prezentujemy je jako udział procentowy w całkowitej liczbie zamówień.

W tym celu możesz zmodyfikować poprzednią tabelę zmieniając jedynie opcję wyświetlania wartości jako procent.

podsumowanie jako % sumy końcowej tabela przestawna

Podsumowanie liczby rekordów i udziału procentowego

W bardzo prosty sposób możesz zbudować podsumowanie zarówno liczby rekordów jak i udziału procentowego w całości. Możesz po prostu dodać to samo pole kilkakrotnie do obszaru wartość.

podwójne podsumowanie tabela przestawna

Lista unikatowych wartości

Możesz użyć tabeli przestawnej do szybkiego przygotowania listy unikatowych wartości. Wystarczy dodać jedynie wybrane pole do obszaru/indeksu wierszy. W ten sposób Excel przygotuje listę unikatowych wartości dla tego pola. 

Co więcej, możesz w ten sposób zidentyfikować potencjalne błędy lub literówki w opisach pola w tabeli. Będą one pokazane obok poprawnych wartości.

Lista unikatowych wartości tabela przestawna Excel

Grupuj zmienne liczbowe w  czytelne zakresy

Często chcemy podsumować dane ze względu na wybrane zakresy zmiennej liczbowej. Grupując wiek w przedziały co 10 lat: 0-10 lat, 11-20 lat, 21-30 lat, 31-40 lat ... lub wartość sprzedaży w określone przedziały np. co 1000 zł.

  • Ustaw pole liczbowe jako indeks wierszy
  • Kliknij prawym przyciskiem myszy i wybierz opcję grupuj
  • Określ parametry grupowania: początek, koniec i krok.
grupowanie zmiennych liczbowych w tabeli przestawnej Excel

Znałeś te zastosowania tabel przestawnych? Jeśli nie, to przejdź do kursu Excel - Tabele Przestawne. Kurs bazuje na praktycznych przykładach i zawiera mnóstwo ćwiczeń. Dzięki temu kursowi będziesz płynnie posługiwać się tym potężnym narzędziem Excela.

Jeśli szukasz innych zaawansowanych funkcjonalności Excel, to zapraszam na kurs Excel Średnio Zaawansowany lub Excel Zaawansowany.

błędy w formułach Excel

5 funkcji, dzięki którym pozbędziesz się błędów w formułach

Codzienna praca z Excelem to przede wszystkim formuły. W swoich arkuszach używasz różnego rodzaju formuł: obliczeniowych, informacyjnych, wyszukiwania. Excel bez formuł byłby kolejną wersją notatnika do przechowywania danych w formie trochę bardziej czytelnej niż tekstowa.

Może się zdarzyć, że Twoja formuła, nad którą pracowałeś/aś tak mozolnie przez dłuższy czas nie działa tak jak powinna. Jeszcze gorzej jeśli zwraca błąd. Co wtedy zrobić? Jak sobie z tym poradzić?

Najlepiej zacząć systematycznie od odpowiedniego przygotowania danych, z którymi będziemy pracować. Kilka wskazówek odnośnie poprawnego przygotowania danych możesz znaleźć na naszej grupie Facebook. Jeśli jeszcze nie dołączyłeś/aś, to zrób to teraz.

Poznaj zasady poprawnego przygotowania danych w kursie Excel Podstawowy oraz Excel Średnio Zaawansowany.

Jednak czasem pomimo wielu starań i przygotowania danych formuły wciąż zwracają błędy. Może to być dzielenie przez zero, brak wyszukiwanej wartości w przeszukiwanym zakresie lub wiele innych. Na szczęście jest kilka funkcji, które pozwolą nam zapanować nad błędami w formułach Excela.

=CZY.BŁĄD

Jest to jedna z podstawowych funkcji informacyjnych do pracy z formułami. Bardzo przydatna szczególnie, gdy wykonujemy szereg formuł w kolejnych komórkach/kolumnach. Jeśli dana formuła korzysta z wyniku innej możesz użyć funkcji CZY.BŁĄD(wartość) w połączeniu z funkcją JEŻELI. W ten sposób możesz zapewnić, że formuła nie będzie wykonana w przypadku błędu.

Przykład użycia funkcji maximum Excel

=CZY.LICZBA

Zdecydowanie najczęściej budujemy formuły obliczeniowe.

Sprawdź 15 funkcji Excel, które po prostu musisz znać.

W przypadku takich formuł chcemy mieć pewność, że argumenty są liczbami. Z pomocą przychodzi nam kolejna funkcja informacyjna CZY.LICZBA(wartość). Funkcja zwraca wartość logiczną PRAWDA lub FAŁSZ w zależności od tego czy argument jest liczbą czy nie.

Funkcja jest szczególnie przydatna w przypadku, gdy pobieramy dane z zewnętrznego źródła. Często w takich sytuacjach liczby są przechowywane jako tekst. Może się też pojawić konflikt znaku separatora dziesiętnego kropka czy przecinek.

Przykład użycia funkcji minimum Excel

 

Pamiętaj, że funkcja CZY.LICZBA nie dokonuje konwersji liczb przechowywanych jako tekst na liczbę. To znaczy, że CZY.LICZBA(”123”) zwróci fałsz ponieważ argumentem jest tekst.

Sprawdź jak poradzić sobie, gdy liczba jest przechowywana jako tekst w kursie Excel Podstawowy.

=JEŻELI.BŁĄD

Najpotężniejsza i chyba najczęściej używana funkcja z całej listy. Przede wszystkim dlatego, że funkcja JEŻELI.BŁĄD(wartość; wartość_jeżeli_błąd) jest kompaktowa. Pozwala w łatwy sposób sprawdzić czy formuła zwraca błąd i jednocześnie wykonać inną czynność jeśli tak jest.

Przykład użycia funkcji data Excel

=CZY.BŁ

Funkcja CZY.BŁ(wartość) jest analogiczna w działaniu do funkcji CZY.BŁĄD(wartość). Z tym, że CZY.BŁ sprawdza jedynie czy wystąpił błąd #N/D (wartość niedostępna). Taki błąd może się pojawić przy wyszukiwaniu liczby lub tekstu, który nie występuje w przeszukiwanej tabeli.

=CZY.PUSTA

Kolejną przydatną funkcją jest CZY.PUSTA(wartość). Jak się pewnie domyślasz, działa ona analogicznie do innych funkcji z rodziny CZY.*. Funkcja CZY.PUSTA sprawdza czy komórka, do której się odwołujemy jest pusta.

Zachęcamy do używania funkcji z tej listy w formułach. Z pewnością pomogą Ci one uporać się z wieloma niespodziewanymi błędami generowanymi przez formuły. Dobrze napisana formuła, która nie zwraca błędów, znacznie poprawia komfort pracy w Excelu.

Jeśli szukasz więcej przykładów, to zapraszamy na kurs Excel Średnio Zaawansowany lub Excel Zaawansowany.

5 powodów, dla których powinieneś używać funkcji tablicowych

Niektórzy twierdzą, że znajomość funkcji i formuł tablicowych jest, zaraz przed makropoleceniami i VBA, najbardziej zaawansowaną funkcjonalnością programu MS Excel. Pewnie poznałeś już podstawowe i zaawansowane  funkcjonalności takie jak funkcje, wykresy czy tabele przestawne.

Sprawdź 15 funkcji Excel, które musisz znać.

Być może ta wiedza i umiejętności w zupełności wystarczają Ci w Twojej pracy. A może wciąż czujesz, że mógłbyś pewne formuły zapisać sprawniej i prościej. Zastanawiasz się czy dałoby się napisać jedną sprytną formułę zamiast dodawać kolejne kolumny do przeprowadzenia prostej analizy? Okazuje się, że z pomocą przychodzą nam tablice i formuły tablicowe. 

Formuły tablicowe to nie jest odrębna grupa funkcji Excel. Jest to zupełnie inne podejście do użycia funkcji programu. Bazujemy na nowym obiekcie - tablicy. Stosując wybraną funkcję, lub kombinację funkcji do całej tablicy danych możemy wykonać wiele operacji przy użyciu zwięzłych i zrozumiałych formuł.

Formuł tablicowych można użyć do wielu z pozoru skomplikowanych zadań, takich jak:

  • Zliczanie znaków znajdujących się w zakresie komórek.
  • Sumowanie tylko liczb spełniających określone kryteria, takich jak większe od zera lub różne od zera.
  • Sumowanie co n-tej wartości w zakresie wartości.

Najważniejsze w całej zabawie jest to, że nie musimy w żaden sposób modyfikować naszych danych, dodawać nowych kolumn z dodatkowymi obliczeniami. Wystarczy sprytna formuła w jednej komórce.

Prosty przykład formuły tablicowej

Załóżmy, że naszym zadaniem jest obliczenie maksimum z wielkości powstałej po przemnożeniu jednej kolumny przez drugą. Wtedy możemy użyć zwykłej funkcji MAX. Jako argumenty podamy wprost mnożenie dwóch kolumn. 

funkcja tablicowa MAX Excel

W takiej wersji ta formuła zwróci błąd. Jednak, gdy zatwierdzimy ją kombinacją klawiszy CTRL + SHIFT + ENTER, to formuła zadziała już poprawnie. Możesz sprawdzić wykonując ręcznie mnożenie poszczególnych wartości 🙂 Dla której pozycji wartość sprzedaży jest największa?

funkcja tablicowa MAX Excel

Spójność formuł

Pierwszym powodem, dla którego warto stosować formuły tablicowe jest dodatkowa spójność. Możesz stosować funkcję SUMA.JEŻELI wskazując pojedyncze kryterium. Ale możesz też wskazać od razu cały zakres kryteriów dla wszystkich rekordów/wierszy, w których zamierzasz umieścić funkcję. W ten sposób w każdej komórce jest dokładnie taka sama formuła. Jest ona przez to dużo prostsza w analizie dla całego zakresu.

Pamiętaj! Taka formuła zadziała dopiero po zatwierdzeniu kombinacją klawiszy CTRL + SHIFT + ENTER.

funkcja suma.jeżeli jako funkcja tablicowa Excel

Bezpieczeństwo

Używając formuły tablicowej do całego wybranego zakresu masz ograniczone możliwości modyfikacji formuły w poszczególnych komórkach. Każdą zmianę musisz ponownie zatwierdzić kombinacją klawiszy CTRL + SHIFT + ENTER. W ten sposób zmiany mają zastosowanie do całego zakresu, do którego odnosi się tablica i formuła tablicowa. Twoja formuła jest dodatkowo zabezpieczona przed nieporządanymi zmianami w wybranych komórkach.

Przejrzystość formuł

Spójrz jeszcze raz na pierwszy przykład z funkcją MAX zastosowaną do operacji mnożenia na kolumnach. Dzięki takiemu zabiegowi od razu widać co się dzieje w formule. Nie potrzebujemy żadnych dodatkowych kolumn, w których mnożylibyśmy przez siebie dwie inne kolumny. Wystarczy taką operację wykonać bezpośrednio w formule.

Innym przykładem jest użycie warunków, funkcji JEŻELI, zagnieżdżonej w funkcji MIN. W poniższym przykładzie wyznaczamy minimalną wartość z kolumny z pominięciem wartości równych zero. Warunek stosujemy do całej kolumny. Proste i zrozumiałe, prawda? 😉

funkcja tablicowa MIN Excel

Mniejszy rozmiar plików

Na końcu wisienka na torcie. Używanie formuł tablicowych może znacząco zmniejszyć rozmiar Twojego pliku! To prawda. Jest tak dlatego, że nie potrzebujemy tworzyć żadnych dodatkowych kolumn z obliczeniami pomocniczymi. Wystarczy jedna formuła. Excel wykonuje obliczenia pomocnicze na tablicach w pamięci. Nie zapisuje ich w postaci wartości i formuł w komórkach arkusza.

Ponadto, plik, w którym używasz formuł tablicowych szybciej się otwiera i odświeża. Jest to szczególnie ważne w pracy z dużymi zbiorami danych. Zastąpienie klasycznych formuł i części obliczeń pomocniczych za pomocą formuł tablicowych przynosi duże korzyści. Oczywiście tam gdzie takie zastąpienie ma sens i jest właściwe.

Jestem ciekawy czy używasz w swojej pracy formuł tablicowych. A może jest to pierwszy raz, gdy się z nimi spotykasz. Te i wiele innych przykładów zastosowania formuł tablicowych znajdziesz w kursie Excel Formuły Tablicowe.

Jeśli szukasz więcej zaawansowanych funkcjonalności, to zapraszam na kurs Excel Średnio Zaawansowany lub Excel Zaawansowany.

15 funkcji Excel, które musisz znać

Zaczynając pracę z Excelem zazwyczaj posługujemy się bardzo prostymi formułami przypisania wartości do innej komórki lub uzupełniamy je o podstawowe operatory działań takie jak mnożenie czy dodawanie. W miarę zdobywania doświadczenia zaczynamy sięgać po bardziej wyrafinowane narzędzia. Po pewnym czasie zaczynamy biegle posługiwać się szerszą gamą funkcji dostępnych w bibliotece Excela. Poznajemy coraz bardziej i bardziej skomplikowane funkcje.

Jednak jest kilka funkcji, bez których praca z Excelem nie była by taka sama. Niektórzy w ogóle nie wyobrażają sobie bez nich życia.

Przeanalizowałem wszystkie ćwiczenia w naszych kursach Excel i postanowiłem podsumować listę takich funkcji w Excelu, które każdy musi znać.

=SUMA

Na początek funkcja SUMA(liczba1; [liczba2], ....), która wykonuje proste sumowanie arytmetyczne wskazanego zakresu. Funkcja przyjmuje jako argument(y) adresy pojedynczych komórek, całe zakresy lub nawet tablice. Zwraca jedną wartość, czyli wynik sumowania wskazanego zakresu.

Przykład użycia funkcji suma Excel

=ŚREDNIA

To kolejna funkcja, która wykonuje proste działanie arytmetyczne. Funkcja zwraca średnią arytmetyczną dla podanych argumentów. ŚREDNIA(liczba1; [liczba2], ...).

Przykład użycia funkcji średnia Excel

=MAX

Prosta, ale wręcz niezastąpiona funkcja. Wyobraź sobie, że musisz znaleźć największe zamówienie lub klienta, który dokonał największej liczby zakupów w Twoim sklepie. Wtedy funkcja MAX(liczba1; [liczba2]; ...) przychodzi z pomocą i pozwala rozwiązać problem w bardzo prosty sposób.

Przykład użycia funkcji maximum Excel

=MIN

Funkcja MIN(liczba1; [liczba2]; ...) to funkcja-siostra dla funkcji MAX 😊 Jeśli potrzebujesz informacji o najstarszym zamówieniu, które oczekuje na realizację lub najstarszej niezapłaconej fakturze, to możesz użyć właśnie funkcji MIN(). Obie funkcje MIN i MAX możesz stosować zarówno do liczb jak i dat.

Przykład użycia funkcji minimum Excel

=DATA

Jeśli w Twoich danych nie ma pola z datą, ale za to jest informacja o roku, miesiącu i dniu, to możesz je wykorzystać. Używając funkcji DATA(rok; miesiąc; dzień), stworzysz datę samodzielnie na podstawie tych danych.

Przykład użycia funkcji data Excel

=ROK

Funkcja niejako odwrotna do funkcji DATA. Dzięki niej możesz wyciągnąć informację o samym tylko roku z komórki przechowującej datę. Użycie ROK(liczba oznaczająca rok).

Przykład użycia funkcji rok Excel

=MIESIĄC

Kolejna z serii funkcji dat i czasu. Dzięki niej uzyskasz informację o miesiącu z komórki przechowującej datę. Użycie MIESIĄC(liczba oznaczająca rok).

Przykład użycia funkcji miesiac Excel

=DZIEŃ

Na koniec serii funkcji daty i czasu funkcja DZIEŃ(liczba oznaczająca rok). Jej działanie jest analogiczne do funkcji ROK i MIESIĄC.

Przykład użycia funkcji dzień Excel

=ILE.WIERSZY

Ile razy dostawałeś jakiś plik z tabelą danych i musiałeś sprawdzić najpierw z jak dużą tabelą masz do czynienia? Zamiast liczyć ręcznie wiersze/kolumny z danymi możesz użyć funkcji ILE.WIERSZY(tablica). Informacji zwracanej przez funkcję możesz też przekazać do innej funkcji i wykorzystać w obliczeniach.

Przykład użycia funkcji ile.wierszy Excel

=ILE.NIEPUSTYCH

Dzięki funkcji ILE.NIEPUSTYCH(wartość1; [wartość2];...) możesz zliczyć komórki z zakresu, które są niepuste. Jest to szczególnie przydatne, gdy potrzebujesz informacji o tylko poprawnie wypełnionych rekordach. Ta funkcja jest też często wykorzystywana jako funkcja pomocnicza do budowania bardziej zaawansowanych formuł. Z jej pomocą można stworzyć dynamiczne obszary nazwane. Więcej na ten temat znajdziesz w kursie Excel Zaawansowany.

Przykład użycia funkcji ile.niepustych Excel

=LEWY

Funkcje do pracy z tekstem są równie często wykorzystywane jak te do pracy z liczbami i datą. Funkcja LEWY(tekst; [liczba_znaków]) wycina wskazaną liczbę znaków od lewej strony z tekstu w komórce.

Przykład użycia funkcji lewy Excel

=PRAWY

Funkcja-siostra do funkcji LEWY. Z tym, że PRAWY(tekst; [liczba_znaków]) wycina wskazaną liczbę znaków od prawej strony tekstu w komórce.

Przykład użycia funkcji prawy Excel

=DŁ

Często funkcje LEWY i PRAWY łączy się w jednej formule z kolejną funkcją tekstową DŁ(tekst). Ta funkcja zwraca długość ciągu tekstowego w komórce.

Przykład użycia funkcji długość Excel

=ZAOKR

Na koniec podstawowa funkcja dla wszystkich księgowych i pracowników urzędów skarbowych. Czyli funkcja ZAOKR(liczba; liczba_cyfr). Ta funkcja zaokrągla liczbę we wskazany sposób. Możemy jej używać między innymi do wyliczeń walutowych, gdy posługujemy się liczbami zaokrąglonymi do dwóch miejsc dziesiętnych.

Przykład użycia funkcji zaokrąglij Excel

Znałeś wszystkie funkcje z tej listy? Jeśli nie, to nie trać czasu i spróbuj ich użyć w swoim arkuszu. Te i wiele innych podstawowych funkcji Excela znajdziesz też w kursie Excel Podstawowy.

Jeśli szukasz bardziej zaawansowanych funkcjonalności, to zapraszam na kurs Excel Średnio Zaawansowany lub Excel Zaawansowany.

Sprawdź ofertę naszych kursów On-Line

Image
Excel Podstawowy
Image
Excel Średnio Zaawansowany
Image
Excel Zaawansowany
raport excel na ekranie komputera

Dlaczego warto znać Excela?

Pierwsza wersja programu Microsoft Excel powstała w 1987 roku. Więc Excel ma już ponad 30 lat! Dzisiaj niewiele osób wyobraża sobie pracę biurową bez poczciwego Excela. Sam program jest jednym z najchętniej używanych do zadań związanych z przetwarzaniem i analizą danych. Po co znać Excela? Znajomość obsługi arkusza kalkulacyjnego jest niemal niezbędna na rynku pracy. Natomiast zaawansowana znajomość programu daje znaczącą przewagę nad konkurencją. Do czego można wykorzystać Excela i dlaczego jego znajomość jest tak pożądana?

Excel - standard w pracy biurowej

Microsoft Excel jest obecnie niezaprzeczalnie najpopularniejszym programem w swojej kategorii. Od chwili wypuszczenia pierwszej edycji, program jest wciąż udoskonalany i uzupełniany o nowe funkcjonalności. Dzięki temu narzędzie staje się jeszcze bardziej intuicyjne i przyjazne użytkownikowi. Ze względu na dużą popularność, MS Excel stanowi obecnie uniwersalny format i standard przetwarzania i przechowywania danych. Dlatego też znajomość Excela jest już standardowym i niemal podstawowym wymogiem stawianym kandydatom do pracy w większości firm i przedsiębiorstw.

Wystarczy przejrzeć ogłoszenia o pracę, w większości których znajomość MS Excel jest wymagana przynajmniej na poziomie podstawowym. Pracodawcy oczekują praktycznej znajomości Excela m.in. na stanowiska takie jak analityk biznesowy, księgowy, przedstawiciel handlowy, logistyk, menedżer, pracownik administracji, pracownik sektora bankowego lub ubezpieczeniowego. Portal pracuj.pl wymienia znajomość pakietu MS Office wśród 7 niezbędnych umiejętności w pracy biurowej.

Excel niezastąpiony w domu

Coraz więcej ludzi sięga po  Excela nie tylko w pracy, ale również w domu. Dzięki intuicyjnej obsłudze i ogromnym możliwościom, Excel doskonale sprawdza się jako narzędzie do:

✅ planowania domowego budżetu,

✅ porównywania ofert kredytów,

✅ planowania posiłków (kalkulator kalorii),

✅ przechowywania listy kontaktów znajomych i rodziny,

✅ i wiele innych.

A ty korzystasz już z Excela w domu? Do czego wykorzystujesz arkusze kalkulacyjne? Podziel się z nami swoimi pomysłami na facebooku.

Sprawdź ofertę naszych kursów On-Line

Image
Excel Podstawowy
Image
Excel Średnio Zaawansowany
Image
Excel Zaawansowany

Wizualizuj dane z MS Excel

W biznesie same dane to za mało. Szczególnie w dzisiejszych czasach, gdy zbierane i przetwarzane dane zajmują coraz więcej gigabajtów. Managerowie i pracownicy wyższego szczebla oczekują gotowych wniosków przedstawionych w jasny i klarowny sposób. Najlepszym rozwiązaniem jest prezentacja graficzna i wizualna. MS Excel daje nam dużo możliwości do przygotowania wykresów, infografik i tabel z podsumowaniami na podstawie zebranych danych.

Pracując z Excelem możesz wspomagać się formatowaniem warunkowym, tabelami i wykresami przestawnymi, różnymi typami wykresów (kołowe, kolumnowe, słupkowe) również w 3D. Każda kolejna wersja programu MS Excel dostarcza nowych funkcjonalności również w zakresie prezentacji graficznej. Wersja Excel 2019 została wzbogacona między innymi o wykres lejkowy oraz kilka przydatnych usprawnień tabel i wykresów przestawnych.

Niewykorzystane możliwości

Program Microsoft Excel oferuje ogromne możliwości w zakresie dokonywania obliczeń, przetwarzania i analizy danych. Jednak w dalszym ciągu jego użytkownicy ograniczają się głównie do podstawowych funkcjonalności i funkcji takich jak SUMA czy ŚREDNIA. Na szczęście świadomość potęgi Excela, wciąż rośnie zarówno wśród pracodawców jak i pracowników oraz kandydatów do pracy.

Sprawdź listę 15 funkcji Excel, które musisz znać!

Pamiętaj, że znajomość zaawansowanych funkcjonalności Excela pozwala na sprawniejsze przeprowadzanie analiz oraz przygotowywanie raportów. Wyższa efektywność pracy to oszczędność czasu i pieniędzy dla pracownika i firmy. Po co znać Excela w domu? Excel może znacząco ułatwić Ci planowanie i codziennych obowiązków i budżetu. 

Warto znać Excela

Mam nadzieję, że nie masz już wątpliwości po co znać Excela i jego funkcjonalności. Jeśli chcesz rozpocząć swoją przygodę z Excelem, zacznij od kursu Excel Podstawowy. Jeśli pracujesz już z programem, ale chcesz poznać bardziej zaawansowane funkcjonalności i praktyczne zastosowania, polecamy kurs Excel Średnio Zaawansowany lub Excel Zaawansowany.

Zapytaj o ofertę dla Swojej Firmy