Usuwanie pustych wierszy w LibreOffice Calc

Użytkownicy wykorzystujący arkusz kalkulacyjny jako prostą bazę danych bardzo często spotykają się z problemem pustych rekordów (wierszy). Artykuł opisuje dwa sposoby na ich usunięcie: sortowanie oraz filtrowanie. Zastosujemy je zarówno na bardzo prostych, jak i bardziej skomplikowanych bazach danych.

Wpierw rozważmy najprostszy możliwy zbiór danych, zawierający zaledwie jedną kolumnę. Przede wszystkim, musimy ją zaznaczyć.

Najprostszym sposobem jest posortowanie zawartości kolumny. Algorytm sortowania działa w ten sposób, że puste komórki umieszcza za komórkami zawierającymi dane. Aby z niego skorzystać, należy z menu wybrać DaneSortuj.... Pojawi się nowe okno, w którym możemy wybrać szczegóły sposobu sortowania. Szczególnie interesować nas powinna karta Opcje, w której można zaznaczyć Zakres zawiera nagłówki kolumn. Jej zaznaczenie spowoduje, że pierwszy wiersz zaznaczonego zakresu nie będzie podlegał sortowaniu.

Drugim sposobem jest skorzystanie z filtrów. Po zaznaczeniu kolumny z danymi z menu wybieramy DaneFiltrFiltr standardowy.... Otworzy się nowe okno, w którym możemy określić kryteria wyboru komórek. Wszystkie komórki, które nie spełniają podanych warunków, zostaną ukryte. Same warunki można łączyć za pomocą operatorów koniunkcji (I) oraz alternatywy (LUB), ale na razie nie musimy z nich korzystać. Zamiast tego w pierwszym wierszu okna klikamy na pole pierwsze od prawej i wybieramy z listy Pusty. Potwierdzamy przyciskiem OK.

Komórki zawierające jakąś treść zostaną tymczasowo ukryte. Dzięki temu możemy w prosty sposób zaznaczyć te wiersze, które w naszym zbiorze danych są puste i usunąć je. Możemy to zrobić na dwa sposoby.

  • Zaznaczyć całe wiersze, trzymając przyciśnięty lewy przycisk myszy na kolumnie zawierającej numery wierszy. Następnie klikamy prawym przyciskiem myszy na któryś z numerów wierszy i z menu wybieramy Usuń wiersze.
  • Zaznaczyć same komórki w arkuszu kalkulacyjnym, kliknąć prawym przyciskiem myszy w ich obrębie i wybrać z menu Usuń komórki. Pojawi się nowe okienko, w którym zaznaczamy Usuń całe wiersze i potwierdzamy.

Po usunięciu wierszy pozostaje już tylko wybrać z menu DaneFiltrUsuń filtr.

Metoda wykorzystująca filtry ma tę zaletę, że jest bardziej skalowalna — w zaprezentowanej formie można ją wykorzystać do usunięcia pustych rekordów także z odrobinę większych baz danych, zawierających kilka kolumn. Wystarczy przy pomocy operatora koniunkcji stworzyć zestaw warunków wyszukujący puste komórki w każdej z kolumn bazy.

Sposób ten jednak nie sprawdzi się w przypadku średnich i dużych baz, liczących od kilkunastu do kilkuset kolumn. Tworzenie zestawu warunków w filtrze byłoby zbyt czasochłonne i męczące. Można temu zaradzić, ale w tym celu będziemy potrzebowali tymczasowej kolumny, w której będziemy przechowywali liczbę reprezentującą liczbę niepustych komórek w danym rekordzie.

W tym celu wykorzystamy formułę ILE.NIEPUSTYCH, której argumentem będzie zakres wszystkich komórek w rekordzie. W przypadku mojej, niezbyt dużej, bazy wygląda ona tak:

=ILE.NIEPUSTYCH(A2:F2)

Po wprowadzeniu formuły do pierwszej komórki kolumny, zaznaczamy wszystkie komórki w kolumnie (od pierwszego do ostatniego wiersza zawierającego dane w zbiorze) i wybieramy z menu EdycjaWypełnienieW dół. W ten sposób formuła zostanie skopiowana dla pozostałych rekordów bazy.

Dzięki zastosowaniu takiej tymczasowej kolumny problem usuwania pustych wierszy z dużego zbioru danych udało nam się sprowadzić do problemu usuwania pustych wierszy ze zbioru danych zawierających tylko jedną kolumnę — czyli zagadnienia, które rozwiązaliśmy we wcześniejszej części wpisu.

Jeżeli zdecydujemy się skorzystać z filtra, tym razem konstruujemy warunek pozwalający znaleźć wszystkie komórki, których wartością jest zero.

W przypadku korzystania z sortowania sprawa jest trochę bardziej skomplikowana. Możemy zaznaczyć cały zbiór danych i posortować go rosnąco według ostatniej kolumny, ale w ten sposób zmienimy kolejność rekordów w bazie. Jeżeli ma to dla nas znaczenie, będzie to spora wada tego rozwiązania.

Aby uniknąć tego problemu, konieczne będzie dodanie jeszcze jednej kolumny tymczasowej. Kolumna ta będzie zawierała unikatowe identyfikatory rekordów — po prostu kolejne liczby. Zawartość tej kolumny tworzymy analogicznie jak w przypadku kopiowania formuły do wszystkich rekordów bazy — w pierwszej komórce wpisujemy liczbę 1, zaznaczamy pozostałe komórki w kolumnie i z menu wybieramy EdycjaWypełnienieSeria.... Pojawi się nowe okno, w którym jedynie klikamy OK.

Gdy nasza baza zawiera kolumnę z identyfikatorami, przystępujemy do sortowania rekordów względem pierwszej tymczasowej kolumny (tej z formułą). Następnie usuwamy wiersze odpowiadające za puste rekordy i jeszcze raz sortujemy bazę — tym razem względem kolumny z identyfikatorami.

Niezależnie od tego, której techniki użyjemy, ostatnim krokiem jest usunięcie tymczasowych kolumn z bazy danych.

Podobał Ci się ten artykuł? Zapisz się na listę subskrybentów i otrzymuj informacje o następnych

komentarzy 19

  • Patryk napisał(a):

    Szukałem sposobu na usunięcie pustych komórek w OpenOffice Calc i na szczęście trafiłem na Twoją stronę, bo już zabierałem się za usuwanie ręcznie. Sposób z sortowaniem działa idealnie, drugiego sposobu nie testowałem. Dzięki wielkie!

    • Mirosław Zalewski napisał(a):

      Cała przyjemność po mojej stronie 🙂 . Mam nadzieję, że na blogu znajdziesz rozwiązanie jeszcze nie jednego problemu, który Cię trapi. A jeżeli nie, zawsze możesz zaproponować artykuł 😉 .

      • Ewelina napisał(a):

        A jak zrobić z dwóch kolumn jedną? tak aby pozostały wiersze.

        • Mirosław Zalewski napisał(a):

          Niestety, Calc nie oferuje dedykowanej funkcji do rozwiązania tego problemu. Potraktuję to pytanie jako przyczynek do osobnego artykułu. Tymczasem wersja skrócona rozwiązania:
          1. Utworzyć kolumnę tymczasową. W pierwszej komórce wpisać formułę (oczywiście dopasowaną do konkretnego układu arkusza)
          =ZŁĄCZ.TEKSTY(A1;” „;B1)
          2. Przeciągnąć formułę na całą kolumnę z danymi.
          3. Zaznaczyć całą kolumnę tymczasową, skopiować jej zawartość.
          4. Przy ciągle zaznaczonej kolumnie, wcisnąć Ctrl + Shift + V. W nowym oknie odznaczyć „Wklej wszystko” oraz „Formuły”. Pozostałe pola wyboru zaznaczyć wedle potrzeb. Potwierdzić wklejanie, potwierdzić nadpisanie istniejących wartości (o ile pojawi się odpowiednie okno).
          5. Usunąć dwie wcześniejsze kolumny.

          • shadowgd napisał(a):

            Problem prościej rozwiązuje się poprzez użycie znaku & w trakcie pisania formuły np. =A1&” „&B2

            Efekt: łączy dwie komórki i stawia między nimi spację.
            Np przy łączeniu imienia i nazwiska do jednej kolumny. 🙂

  • Ewelina napisał(a):

    Dziękuje bardzo, zaraz spróbuję to zrobić… a jeszcze jedno pytanko, czy w Wordzie jest taka opcja o którą pytałam oprócz gumki?

    • Mirosław Zalewski napisał(a):

      Nie ma. Można co najwyżej skonwertować tabelę na tekst (Tabela → Konwertuj → Tabela na tekst), usunąć znak podziału kolumny gdzie trzeba i ponownie zrobić z tekstu tabelę (Tabela → Konwertuj → Tekst na tabelę…).
      Albo przekopiować tabelę do Calca i tam postępować zgodnie z instrukcjami z wcześniejszego komentarza, a poprawioną tabelę przekopiować z powrotem do Writera. Ale wydaje mi się, że ten sposób jest bardziej czasochłonny.

  • Ewelina napisał(a):

    Ok. Dzięki Mistrzu 🙂 Dzięki Tobie będzie piateczka z informatyki 🙂 Pozdrawiam serdecznie 🙂

  • welina napisał(a):

    Hej jesli mozna to mam jeszcze jedno pytańko.
    Jak posortować alfabetycznie nazwiska, ale w sytuacji gdy imię i nazwisko znajduje się w jednej kolumnie a na drugim miejscu stoi nazwisko i to własnie te nazwiska mają być posortowane w koleności alfabetycznj tzn.

    Ewa Zych
    Mateusz Kowalski
    Adam Bator

    Z niecierpliwością czekam na podpoiedź 🙂

    • Mirosław Zalewski napisał(a):

      Jest to problem, któremu mam zamiar poświęcić osobny artykuł w bliżej nieokreślonej przyszłości.
      Na pewno da się poprzez wydzielenie nazwisk (za pomocą zbioru funkcji FRAGMENT.TEKSTU, ZNAJDŹ i ZASTĄP lub sprytnego wyrażenia regularnego) do kolumny tymczasowej i posortowanie całego zbioru względem niej; na pewno da się za pomocą makra. Nie sprawdziłem jednak, czy są jeszcze jakieś inne sposoby. Gdy je zbadam, opiszę to na blogu.

    • subslex napisał(a):

      – zaznaczasz kolumne z danymi
      – korzystasz z zakladki: dane/tekst jako kolumny
      – zaznaczasz opcje separatora: rozdzielony i spacja
      – ok

      po rozdzieleniu sortujesz wg nazwisk

  • Kasia napisał(a):

    Czy jest sposób na usunięcie powtarzających się wierszy?

    • Mirosław Zalewski napisał(a):

      Zależy, co rozumiesz przez „powtarzające się wiersze”.
      Ja bym poszedł w stronę dwóch kolumn tymczasowych — jedna przechowywałaby zawartość wszystkich komórek w danym wierszu, druga sprawdzałaby, czy dany ciąg znaków już wystąpił (tak jak tutaj: Wątek „Część wspólna” na forum polskiej społeczności). Potem przy pomocy filtra można w prosty sposób znaleźć wszystkie powtórzone wiersze i je usunąć.
      Ogólnie: pytania dotyczące używania pakietu proszę zadawać na wspomnianym forum.

      • shadowgd napisał(a):

        Jest jeszcze sposób polegający na wstawieniu dodatkowej kolumny.
        Identyfikatora (zwykły licznik od 1 do n wierszy). i posortowaniu danych po wartościach, które się chce oddublować. Następnie pisze się funkcję w stylu =jeżeli(A$1=A$2;”1″;”0″) – później można całą kolumnę
        z wynikami wkleić jako tekst, aby po ponownym posortowaniu. Tym razem po identyfikatorze pozostały wartości 1 na polu zdublowana komórka. Od excela 2007 w zakładce dane jest funkcja usuń duble :).

  • Szymon napisał(a):

    Witam,

    Czy istnieje funkcja umożliwiająca skopiowanie co trzeciego wiersza i wklejenie w innej kolumnie wartości jeden wiersz pod drugim?

    Pozdrawiam,
    Szymon

    • Mirosław Zalewski napisał(a):

      Nie. Funkcje nie potrafią nic kopiować ani wklejać, więc nie są zdolne do rozwiązania takiego problemu. Za to potrafią to makra (których nie potrafię tworzyć, więc nie pomogę w ich napisaniu).
      Jeżeli już chcemy pozostać przy funkcjach, to można:
      – odwoływać się do komórek przez funkcję ADR.POŚR, której argumentem będzie wynik działania arytmetycznego
      – stworzyć formułę, która zwróci PRAWDA gdy numer wiersza jest podzielny przez trzy i umieścić ją w kolumnie tymczasowej. Następnie wykorzystać ją podczas filtrowania i skopiować jego wynik w inne miejsce.

      We wszystkich przypadkach istnieje dość spore prawdopodobieństwo, że do problemu podchodzi się od złej strony.

  • cyberniki napisał(a):

    Ja mam jedynie uwage by pisac rowniez (jesli mozna oczywiscie) angielska wersje funkcji, poniewaz ILE.NIEPUSTYCH niestety nie dziala i grzebie teraz szukajac odpowiednika wersji polskiej ;-D Poza tym strona super, uklony dla Pana Miroslawa.

    • Mirosław Zalewski napisał(a):

      Niestety, do tej prośby nie jestem w stanie ustosunkować się pozytywnie. Jednym z założeń bloga jest pisanie dla ludzi, którzy nie znają języka angielskiego. Z tego punktu widzenia, podawanie anglojęzycznych nazw funkcji mija się z celem.

      Zawsze można skorzystać z zestawienia nazw funkcji w trzech językach. Co prawda odnosi się ono do Excela, i to w którejś ze starszych wersji (nie obejmuje więc wszystkich funkcji w LibreOffice), ale pokrywa zdecydowaną większość przypadków.

      W razie wątpliwości zawsze można zajrzeć do tłumaczenia pakietu (resource.po, analysis.po, datefunc.po i pricing.po). Anglojęzyczne nazwy znajdują się nad polskojęzycznymi.

  • Daro napisał(a):

    witam. udało mi się znaleźć grafik pracy, jednakże wpisane jest po 8 godzin, a ja potrzebuję po 12 dzień i 12 noc. Jaką formułę trzeba wpisać i gdzie aby to zmienić? Dzięki twoim wpisom udało mi się ukryć wiersze a tu kolejny problem. Za pomoc z góry dziękuję.