Blog

Porady z excela

Porządkowanie danych w Excelu za pomocą formuł

Artykuł opublikowany: 2021-01-27 | W kategorii: Funkcje i formuły w Excelu

 

Poprawna, kompletnie wypełniona tabela danych w Excelu to wymarzona baza do wszelkich obliczeń i analiz. Gdy w arkuszach kalkulacyjnych występują zarówno wartości liczbowe, jak i tekstowe, przeprowadzenie obliczeń oraz funkcjonowanie niektórych formuł może być bardzo problematyczne. Skorzystaj z metody, dzięki której za pomocą stosownych formuł automatycznie oddzielisz od zestawienia te wiersze, które mogą zaburzyć obliczenia.


 

Definiowanie zawartości kolumn

 

Niestety nie zawsze masz do czynienia z idealnymi zestawieniami – często zdarzają się pewne braki, jak w przykładowej tabeli z rysunku 1.

 

Rys. 1.

 

Jak zauważyłeś, w niektórych komórkach wprowadzono tymczasowe informacje, które nie pasują do pozostałych wartości w obrębie kolumny. Chciałbyś teraz utworzyć na bazie tej listy dwa odrębne zestawienia – pierwsze nie będzie zawierać żadnej niepasującej wartości, a w drugim znajdą się jedynie niepoprawne wpisy.

 

W pierwszym kroku powinieneś zdefiniować, jakiego rodzaju wartości powinny być przechowywane w każdej z kolumn. Jeśli kolejno sprawdzane wartości spełnią zadane kryterium, pozostaną w pierwszej czystej liście.

 

W tym celu:

1. Do komórek A2, C2 i D2 wprowadź tekst Liczba, ponieważ w tych kolumnach oczekujesz właśnie wartości liczbowych.

2. Do komórki B2 wpisz Tekst, ponieważ nazwa miasta zawsze będzie wartością tekstową. Twój arkusz powinien wyglądać jak na rysunku 2.

 

Rys. 2.

 

Jeśli w Twojej tabeli są kolumny, których zawartość jest nieokreślona (zmienna), nie wprowadzaj dla nich żadnego opisu. Ich zawartość nie będzie wówczas miała wpływu na to, w której z dynamicznych list cząstkowych znajdą się dane z zestawienia głównego.

 

Sprawdzenie wartości liczbowych

 

Aby utworzyć 2 dynamiczne listy, musisz przeprowadzić test w obrębie każdego wiersza swojej tabeli. Ma to na celu sprawdzenie, czy zawartość komórek każdej kolumny odpowiada zdefiniowanemu wcześniej typowi danych. Wykonasz to za pomocą formuły, która powinna się znaleźć w kolumnie pomocniczej. Ze względu złożoność tej analizy formułę zbuduj etapami.

 

W tym celu:

1. W komórce E4 wpisz następującą formułę:

 

=SUMA(JEŻELI($A$2:$D$2="Liczba";CZY.LICZBA($A4:$D4)*1;0))

 

2. Zatwierdź ją kombinacją klawiszy Ctrl + Shift + Enter, ponieważ jest to formuła tablicowa.

3. Skopiuj ją następnie w dół, aby otrzymać wyniki dla wszystkich wierszy zestawienia.

 

Rys. 3.

 

Zauważ, że w trzech wierszach (5, 8 i 11) formuła zwróciła w wyniku wartość 2, a we wszystkich pozostałych – 3. Liczba 3 w wyniku jest wartością poprawną. Oznacza, że w każdej z trzech kolumn oznaczonych typem Liczba znajdują się wartości liczbowe. Przy wierszach z wynikiem 2 w jednej z kolumn nie ma wartości liczbowej.

 

Wyjaśnienie działania formuły:

  • Wewnątrz funkcji JEŻELI następuje sprawdzenie zakresu A2:D2 pod kątem występowania słowa Liczba, definiującego typ dla kolumn.
  • Zawartość kolumn, w których poprzedni test dał wynik pozytywny, będzie sprawdzana za pomocą funkcji CZY.LICZBA.
  • Funkcja CZY.LICZBA zwróci w wyniku wartość PRAWDA dla każdej komórki, w której występuje wartość liczbowa.
  • Dzięki przemnożeniu wartości logicznej PRAWDA przez 1 do funkcji SUMA przekazywana jest liczba 1.
  • Jeśli funkcja CZY.LICZBA nie znajdzie w komórce wartości liczbowej, do funkcji SUMA przekazane zostanie zero.
  • Dzięki zastosowaniu adresowania bezwzględnego dla zakresu A2:D2 możesz swobodnie skopiować formułę w dół i formuła zostanie odpowiednio dopasowana.

 

Sprawdzenie wartości tekstowych

 

Oprócz sprawdzenia poprawności kolumn z wartościami liczbowymi, powinieneś przetestować także kolumny, dla których zdefiniowałeś zawartość tekstową. Wykonasz to poprzez rozbudowę poprzedniej formuły w następujący sposób:

 

=SUMA(JEŻELI($A$2:$D$2="Liczba";CZY.LICZBA($A4:$D4)*1;0))

+SUMA(JEŻELI($A$2:$D$2="Tekst";NIE(CZY.LICZBA($A4:$D4))*1;0))

 

Teraz również masz do czynienia z formułą tablicową, więc zatwierdź ją wspomnianą wcześniej kombinacją 3 klawiszy. Po skopiowaniu formuły na wymagany zakres komórek otrzymasz nowe wyniki informujące o sprawdzeniu zgodności wpisów w komórkach względem założonego typu dla każdej z kolumn.

 

Rys. 4.

 

Zasada działania nowego członu formuły jest identyczna jak opisywanej poprzednio. Jedyna różnica polega na odwróceniu działania funkcji CZY.LICZBA poprzez zastosowanie dodatkowo funkcji NIE. Dzięki temu możliwe stało się sprawdzenie wszystkich wartości niebędących liczbowymi.

 

Interpretacja wyników jest oczywista: w wierszach z wynikiem 4 następuje zgodność wszystkich czterech sprawdzanych kolumn z założonym typem wartości. Liczba 3 wskazuje natomiast, że w jednej z kolumn wprowadzono wartość inną, niż oczekiwano. Gdyby w którymś z wierszy formuła odszukała więcej nieprawidłowości, wynik byłby odpowiednio mniejszy aż do wartości 0 (w przypadku braku zgodności w każdej sprawdzanej kolumnie).

 

Porównanie wyniku z liczbą sprawdzanych zależności

 

Otrzymałeś wyniki, których zrozumienie wymaga wgłębienia się w budowę formuły. Chciałbyś teraz tak ją zmodyfikować, aby jednoznacznie wskazywała, które wiersze są w całości poprawne, a w których występują nieprawidłowości. W pierwszym przypadku powinien zostać wyświetlony wpis OK, a w drugim – błędy. W tym celu porównaj po prostu otrzymany wynik z liczbą sprawdzanych kolumn. Formuła przyjmie zatem następującą postać:

 

=JEŻELI(SUMA(JEŻELI($A$2:$D$2="Liczba";CZY.LICZBA(A4:D4)*1;0))+SUMA(JEŻELI($A$2:$D$2="Tekst";NIE(CZY.LICZBA(A4:D4))*1;0))=ILE.NIEPUSTYCH($A$2:$D$2);"OK";"błędy")

 

Po odpowiednim zatwierdzeniu formuły tablicowej i skopiowaniu jej w dół otrzymasz tabelę, w której zamiast wartości liczbowych wyświetlony będzie właściwy tekst:

 

Rys. 5.

 

Wyjaśnienie działania formuły:

  • Dzięki zastosowaniu funkcji ILE.NIEPUSTYCH dla zakresu A2:D2 określisz, dla ilu kolumn zdefiniowałeś typ danych i przeprowadzałeś sprawdzenia.
  • Liczbę kolumn porównujesz następnie z wynikiem uzyskanym dzięki kombinacji funkcji SUMA i JEŻELI w opisanej wcześniej formule.
  • Jeśli obie wielkości są takie same, funkcja JEŻELI wprowadzi do komórki tekst OK.
  • W przypadku braku zgodności wyświetlony zostanie napis błędy.
  • Jeśli zrezygnujesz ze sprawdzenia zgodności w którejkolwiek z kolumn, usuwając z zakresu A2:D2 definicję typu, zmiana zostanie uwzględniona zarówno w wyniku funkcji ILE.NIEPUSTYCH, jak i w wyniku formuły sumującej poprawne wartości. Dzięki temu formuła będzie cały czas funkcjonowała poprawnie.

 

Dynamicznie tworzona lista z poprawnymi wartościami

 

Dzięki poprzednim działaniom masz już informację, które wiersze powinny zostać uwzględnione w zestawieniu poprawnych wartości, a które powinny trafić do listy z niewłaściwymi danymi. Aby jasno i czytelnie przedstawić działanie formuł, zbudujesz dwa nowe zestawienia w tym samym arkuszu co lista wyjściowa. Wszystkie trzy listy (bazowa i dwie nowo tworzone) będą przedstawione obok siebie.

 

Na początku utwórz zestawienie zawierające tylko poprawne wpisy z tabeli wejściowej.

1. Skopiuj nagłówek kolumn z zakresu A3:D3 do zakresu G3:J3.

2. Wprowadź w komórce G1 tekst: Wartości poprawne.

3. Wpisz do komórki G4 następującą formułę tablicową:

 

=MIN.K(JEŻELI($E$4:$E$21="OK";WIERSZ($E$4:$E$21);"");WIERSZ(G1))

 

4. Zatwierdź ją znaną Ci już kombinacją klawiszy.

5. Skopiuj formułę w dół na cały zakres tabeli.

 

Rys. 6.

 

Formuła wyświetliła w wyniku tylko te numery wierszy, dla których poprzednia formuła wskazała w kolumnie Typ wynik poprawny w postaci tekstu OK. Komunikaty błędu rozpoczynające się od komórki G18 wynikają z faktu, że liczba wierszy poprawnych jest mniejsza od całkowitej liczby wierszy w tabeli. Komunikaty te ukryjesz na dalszym etapie rozbudowywania formuły.

 

Wyjaśnienie działania formuły:

  • Funkcja JEŻELI sprawdza każdą komórkę zakresu E4:E21, czy występuje w niej tekst OK.
  • Jeśli tak, funkcja WIERSZ zwraca numer odpowiedniego wiersza.
  • Ze wszystkich odszukanych numerów wierszy funkcja MIN.K buduje listę w kolejności rosnącej.
  • Podając do funkcji MIN.K drugi argument w postaci względnego odniesienia do komórki G1 osiągniesz taki efekt, że każdy kolejny (k-ty) wiersz będzie właśnie k-tym najmniejszym, gdyż odniesienie do komórki G1 będzie automatycznie dopasowywane do kolejnych wierszy.

 

Wyodrębnianie poprawnych wartości do nowej listy

 

Masz już numery wierszy z poprawnymi wartościami, więc dzieli Cię tylko mały krok od przedstawienia właściwych danych w nowo budowanym zestawieniu. Rozszerz poprzednią formułę z komórki G4 o funkcję INDEKS w następujący sposób:

 

=INDEKS(A$1:A$21;MIN.K(JEŻELI($E$4:$E$21="OK";WIERSZ($E$4:$E$21);"");WIERSZ(G1)))

 

Pamiętaj, że jest to również formuła tablicowa i wymaga zatwierdzenia za pomocą kombinacji klawiszy Ctrl + Shift + Enter. Skopiuj teraz formułę na cały zakres G4:J21, aby wypełnić wszystkie komórki tworzonego zestawienia.

 

Rys. 7.

 

Tworzenie listy z niepoprawnymi wartościami

 

Aby utworzyć listę zawierającą jedynie wpisy niespełniające zadane kryteria, wystarczy, że wykonasz kilka prostych czynności:

1. Skopiuj zakres komórek G1:J3 i wklej do zakresu L1:O3, a następnie wprowadź w komórce L1 tekst Wartości niepoprawne.

2. Skopiuj formułę z komórki G4 (skorzystaj z klawisza F2 i zaznacz składnię formuły) i wklej do komórki L4 za pomocą skrótu klawiaturowego Ctrl + V.

3. Użyj klawisza F2 i zmień we wklejonej formule tekst OK na błędy.

4. Tablicowo zatwierdź formułę i skopiuj na cały zakres komórek L1:O21.

 

Dzięki tym czynnościom masz już obok siebie w arkuszu dwie tabele, z których każda zawiera część danych pobranych z tabeli bazowej.

 

Rys. 8.

 

Ukrywanie komunikatów o błędach

 

Tabele są praktycznie gotowe. Pierwsza zawiera tylko poprawne wartości wyodrębnione dynamicznie z zestawienia wejściowego, natomiast druga przedstawia tylko te wartości, które nie spełniają kryteriów określonych dla kolumn. Pozostaje jeszcze pozbycie się nieestetycznie wyglądających komunikatów o błędach.

 

Zastosuj w tym celu standardowo używaną kombinację funkcji JEŻELI i CZY.BŁĄD. Funkcja CZY.BŁĄD sprawdza wystąpienie błędu w formule i jeśli to nastąpi, funkcja JEŻELI wyświetli w komórce pusty ciąg znaków (""). W przypadku braku błędu wyświetlony zostanie właściwy wynik działania formuły. Dla komórki G4 formuła obsługująca komunikat błędu przyjmie taką postać:

 

=JEŻELI(CZY.BŁĄD(INDEKS(A$1:A$21;MIN.K(JEŻELI($E$4:$E$21="OK";WIERSZ($E$4:$E$21);"");WIERSZ(G1))));"";INDEKS(A$1:A$21;MIN.K(JEŻELI($E$4:$E$21="OK";WIERSZ($E$4:$E$21);"");WIERSZ(G1))))

 

Po skopiowaniu formuły na właściwe zakresy (z korektą: OK na błędy) otrzymasz dwa zestawienia, z których pierwsze będziesz mógł poddać dowolnym analizom, natomiast w drugim wyświetlone będą te wiersze, które wymagają aktualizacji. Po wprowadzeniu jakiejkolwiek korekty w tabeli bazowej nowo utworzone zestawienia zostaną automatycznie zaktualizowane.

 

Rys. 9.

 

 


 

Komentarze (1)

Autor: LarryMef Dodano: 2021-01-28 08:36:34

Komentarz usunięty przez moderatora

Dodaj komentarz
Autor
Będzie widoczne przy wpisie
Email
Nie będzie publikowany
Treść