Poradnik – 20 najprzydatniejsze Excel formuły do robienia i porządkowania tabel – pobierz arkusz kalkulacyjny za darmo

Zanim przystąpi się do analizy danych przy pomocy Excela, w pierwszej kolejności należy odpowiednio przygotować do tego dane. W tym celu: zmienia się formatowanie dat i cyfr, dodaje się kolumny pomocnicze z dodatkowymi obliczeniami lub znacznikami, usuwa się duplikaty, filtruje i sortuje się dane.

W tym wpisie chciałbym przedstawić najprzydatniejsze formuły Excel, które najczęściej wykorzystuję do robienia tabel pod późniejsze analizy.

Są to zwykle bardzo proste formuły, które jednak nie są powszechnie znane, a przy tym często wypadają z głowy. Dlatego, aby można było mieć je zawsze pod ręką załączam także mini poradnik – arkusz kalkulacyjny do pobrania za darmo – wystarczy kliknąć w poniższy link. Znajdziecie tam omówione poniżej formuły wraz z przykładami. (jeżeli używasz np. przeglądarki Firefox to kliknij prawy przycisk myszy i wybierz: „Zapisz element docelowy jako”)

Kliknij, aby pobrać: Poradnik – pobierz arkusz kalkulacyjny za darmo – formuły Excel do robienia i porządkowania tabel

Wszystkich zainteresowanych poznaniem większej liczby funkcji Excel, służących szybkim obliczeniom, odsyłam do mojego ebooka 'Excel. Nauka na przykładach’. Przedstawione tam zostały niezwykle praktyczne funkcje, takie jak: WYSZUKAJ.PIONOWO(), JEŻELI(), funkcje tablicowe, SUMA.WARUNKÓW(), a także kilkadziesiąt sztuczek ułatwiających pracę w Excelu oraz mnóstwo praktycznych zadań z rozwiązaniami.

Teraz jednak przyjrzyjmy się różnym sposobom, dzięki którym możemy przekształcać dane i przystosowywać je pod nasze wymagania analityczne. Jak wiadomo, często punktem wyjścia do analiz są surowe dane wyciągnięte z bazy danych. Nierzadko zawierają one „dziwne” kody, zbędne rozszerzenia lub nadmiarowe rekordy. Aby można było na nich pracować, najpierw trzeba je uporządkować. Można to robić m.in. przy pomocy następujących formuł i technik…

Jak zamienić liczbę w komórce Excel na wyrażoną w tysiącach bez użycia funkcji?

Jeżeli nie chcemy tworzyć dodatkowych kolumn z funkcją dzielenia przez 1000, można w dowolnej komórce wpisać liczbę 1000, następnie skopiować ją. W następnym kroku zaznaczamy cały zakres danych, który zamierzamy zamienić, klikamy prawy przycisk myszy, wybieramy „Wklej specjalnie”, a potem „Podziel”. W rezultacie otrzymujemy liczby podzielone przez 1000.

Jak usunąć kilka ostatnich cyfr z danej komórki?

Wystarczy wykorzystać funkcję =ZAOKR.DO.CAŁK(A1/10000), gdzie jako argument wstawiamy adres komórki podzielony przez odpowiedni dzielnik. Na przykład jeżeli chcemy pozbyć się 4 ostatnich cyfr to należy podzielić przez 10 000.

Jak zaokrąglić w excel liczbę do pełnych dziesiątek lub setek?

Jak wiemy do zaokrąglania do pierwszego miesiąca po przecinku służy funkcja =ZAOKR(A1;1). Gdy chcemy zaokrąglić do liczby całkowitej, jako drugi argument wstawiamy 0. Ta metodyka obowiązuje też w drugą stronę, tylko że drugi argument musi być wtedy poprzedzany znakiem minus. Zaokrąglenie do dziesiątek to =ZAOKR(A1;-1), a do pełnych setek =ZAOKR(A1;-2).

Jak pobrać część tekstu z komórki excel?

Służy do tego formuła =FRAGMENT.TEKSTU(A1;5;4), gdzie pierwszy argument to adres interesującej nas komórki z tekstem, drugi argument to numer znaku (w przykładzie 5), licząc od lewej strony, od którego zaczniemy pobierać liczbę kolejnych znaków określonych przez wartość określoną, jako trzeci argument (w przykładzie 4).

Jak umieścić liczbę oraz procenty w jednej komórce Excel?

W tym celu możemy przykładowo złączyć tekst w następujący sposób
=A1&” / „& ZAOKR(A1/B$1;3)*100&”%”
Na początku podany jest adres komórki zawierającej liczbę, potem jest odstęp ze znakiem /, następnie zaokrąglamy iloraz dwu interesujących nas komórek. Pomnożenie go przez 1000 oraz dodanie na końcu znaku % daje w rezultacie wartość procentową.

Jak zmienić wielkość liter w Excelu?

Służą do tego 3 formuły: LITERY.WIELKIE(A1), LITERY.MALE(A1), Z.WIELKIEJ.LITERY(A1). Dwie pierwsze formuły zamieniają wszystkie znaki odpowiednio na wielkie i małe litery, z kolei ostatnia działa w ten sposób, że pierwszy znak zostaje z wielkiej litery, a wszystkie pozostałe są z małej.

Jak usunąć nadmiarowe spacje z tekstu w komórce Excel?

Zdarza się często, że w tabeli danych niektóre komórki zawierają tekst, w którym występują dwie lub więcej spacji pod rząd, pojawia się jedna spacja na początku tekstu lub też niepotrzebna spacja na końcu tekstu. Aby się tego pozbyć i zostawić tylko pojedynczą spację rozdzielającą wyrazy można skorzystać z funkcji =USUŃ.ZBĘDNE.ODSTĘPY(A1).

Jak zamienić ciąg cyfr, zapisanych w komórce jako tekst, na liczbę?

Jest to częsty problem, który pojawia się przy importowaniu danych z innych plików lub przyklejaniu danych z edytorów tekstu. W rezultacie tak zapisanych danych, nie można wykonywać na tych liczbach żadnych obliczeń, gdyż każda zastosowana funkcja matematyczna zwraca błąd.

Aby przekształcić tekst na cyfry najlepiej zaznaczyć cały zakres danych, a następnie w pierwszej komórce rozwinąć mały trójkącik w lewym górnym rogu komórki. Z menu, które w następstwie kliknięcia się rozwinie, wystarczy wybrać opcję „Konwertuj na liczbę”.

Drugim sposobem jest wstawienie w komórce pomocniczej liczby 1, skopiowanie następnie tej komórki, wybranie zakres danych z liczbami zapisanymi jako tekst, kliknięcie prawym przyciskiem myszy, wybranie „Wklej specjalnie”, a następnie „Pomnóż”.

Jak znaleźć komórki w Excelu, które zawierają określony tekst?

W tym celu warto dodać kolumnę pomocniczą, w której wstawimy formułę =CZY.LICZBA(SZUKAJ.TEKST(„H”;A1)). Pierwszy argument to poszukiwany znak lub dowolny ciąg znaków, drugi argument to adres komórki, który będzie przeszukiwany. Jeżeli komórka zawiera szukaną wartość, wtedy zwraca parametr „PRAWDA”, w innym przypadku pojawia się „FAŁSZ”.

Jak wstawić określoną wartość w komórce, która jest uzależniona od poziomu wartości innej komórki?

Najczęściej stosuje się tutaj zagnieżdżenie funkcji JEŻELI(). Jednakże, aby uniknąć wielokrotnego powtarzania wspomnianej funkcji warunkowej, kiedy to bardzo łatwo o błąd, można skorzystać z dwu alternatywnych sposobów.

Pierwszy to funkcja:
=SUMA(CZĘSTOŚĆ(A1;{100;1000;10000})*{1;2;3;4})
Drugi sposób to tworzymy tabelę pomocniczą i korzystamy z funkcji
=WYSZUKAJ.PIONOWO(A1;$B$1:$C$10;2;PRAWDA), gdzie bardzo ważny jest 4 argument, który w tym przypadku musi być ustawiony jako „PRAWDA” – oznaczający przybliżone dopasowanie. Przykłady w załączonym excelu.

Jak wyfiltrować tylko unikatowe wartości z kolumny Excel?
Jak wyszukiwać komórki na podstawie więcej niż jednego kryterium?
Jak zamienić kolumnę na wiersze przy pomocy funkcji?
Jak sprawdzić i oznaczyć, czy dana wartość z jednej kolumny występuje w drugiej kolumnie?
Po odpowiedzi na te i inne pytania odsyłam do opracowanego poradnika. A także do kompendium wiedzy o Excel w postaci książki 'Excel. Nauka na przykładach’.