Kopiowanie ze zmiennego zbioru danych wierszy spełniających kryteria
- Autor: Mirosław Zalewski
- 16 marca 2013
- komentarzy 7
LibreOffice Calc oferuje zestaw funkcji do wykonywania podstawowych operacji na tych komórkach zbioru, które spełniają określone warunki. Jednak w sytuacji, gdy mamy do wykonania bardzo dużo obliczeń, konstruowanie odpowiednich formuł jest męczące i czasochłonne. O wiele lepiej jest wyodrębnić interesujące dane w inne miejsce. Artykuł opisuje, jak to zrobić.
Zbiór danych, na którym będę pracował, nie należy do zbyt rozbudowanych. Jednak czynności opisane w dalszej części artykułu odnoszą się także do arkuszy liczących setki wierszy.
Z tego zbioru chciałbym wyodrębnić (do drugiego arkusza) wszystkie te wiersze, których wartość w kolumnie Rodzaj rachunku rozpoczyna się od litery S. Oczekiwany efekt przedstawia poniższa ilustracja.
Dodatkowo zakładam, że moja baza danych jest zmienna. Nie tylko mogą pojawiać się w niej nowe wpisy, ale także już istniejące mogą zostać modyfikowane. Fragment bazy skopiowany do drugiego arkusza powinien uwzględniać te zmiany, najlepiej automatycznie.
Aby osiągnąć taki efekt, wykorzystam filtrowanie oraz zdefiniowane zakresy.
Pierwszym krokiem jest zdefiniowanie zakresu komórek. Zaznaczamy więc cały zbiór danych, z którego chcemy skopiować wiersze i wybieramy z menu Dane → Określ zakres. Pojawi się nowe okno, w którego górnej partii wpisujemy dowolnie wybraną nazwę. Klikamy także przycisk Więcej i upewniamy się, że zaznaczone jest pole Zawiera nagłówki kolumn. Klikamy Dodaj oraz OK.
Teraz czas na wyodrębnienie interesujących nas wierszy. Mając cały czas zaznaczone komórki bazy danych, wybieramy z menu Dane → Filtr → Filtr standardowy....
Pojawi się nowe okno. Przede wszystkim określamy w nim kryteria pozwalające wybrać wiersze do skopiowania. W moim przypadku jest to jeden prosty warunek: pole Rodzaj rachunku musi zaczynać się od litery S.
Następnie klikamy przycisk Więcej opcji. Na samym dole okna powinniśmy zobaczyć informację o zakresie danych, którego dotyczy filtrowanie. Jeżeli jej tam nie ma, to znaczy, że nie utworzyliśmy zakresu lub aktywne komórki znajdują się poza nim. W takim wypadku najlepiej jest powtórzyć całą operację od początku.
Jeżeli wszystko jest w porządku, zaznaczamy pola Kopiuj wyniki do... oraz Zachowaj reguły filtru. W polu w prawym dolnym rogu umieszczamy adres komórki, do której chcemy skopiować wiersze spełniające warunki. Pozostałe opcje zaznaczamy według uznania. Zatwierdzamy zmiany.
W drugim arkuszu powinniśmy zobaczyć efekt zgodny z naszymi początkowymi oczekiwaniami.
Jednym z pierwotnych założeń była zmienność źródłowej bazy danych. Jednak póki co wszelkie jej modyfikacje nie zostają uwzględnione w części skopiowanej do drugiego arkusza.
Aby temu zaradzić, należy umieścić kursor w obrębie źródłowej bazy danych i wybrać z menu Dane → Odśwież zakres. Niestety, o ile mi wiadomo, operacji tej nie można zautomatyzować.
Z pewnością odkryjemy jeszcze jeden problem — nowe wiersze spełniające kryteria nie zostają skopiowane do drugiego arkusza. Wynika to z faktu, że zdefiniowany wcześniej zakres danych podczas filtrowania został ograniczony jedynie do tych komórek, które zawierały jakąkolwiek zawartość. Musimy więc go rozszerzyć.
W tym celu ponownie wybieramy z menu Dane → Określ zakres. Tym razem jednak w nowym oknie zaznaczamy nazwę wcześniej utworzonego zakresu, a w polu na dole okna wpisujemy adresy nowych komórek krańcowych. Klikamy przycisk Modyfikuj i zamykamy okno.
Od teraz odświeżenie zakresu spowoduje skopiowanie do nowego arkusza wierszy spełniających kryteria.
Dziękuję za ten artykuł – wszystko działa super.
A jak urzyć tego sposobu, jeśli nagłówki kolumn znajdują się w szóstym wierszu, a nie w pierwszym?
Cieszę się, że artykuł sprostał oczekiwaniom 🙂 .
Jeżeli baza rozpoczyna się od szóstego wiersza, to zasadniczo nic się nie zmienia — tylko podczas definiowania nazwanego zakresu trzeba wybrać inny zakres komórek (np. od A6 zamiast od A1).
Jeżeli zaś etykiety kolumn znajdują się w szóstym wierszu, zaś w wierszach powyżej znajdują się opisywane przez nie dane, to jest to — z punktu widzenia arkusza kalkulacyjnego — niepoprawnie przygotowana baza danych. Calc po prostu nie jest na taką sytuację przygotowany. Można to próbować obejść rozmaitymi sposobami, ale chyba prościej przenieść etykiety ponad dane.
Etykiety kolumn znajdują się w szóstym wierszu, natomiast dane zaczynają się od siódmego wiersza w dół.
Czy można użyć tego sposobu bez definiowania etykiet kolumn, używając jedynie ich sygnatur, np. kolumna C?
Tak jak pisałem w poprzednim komentarzu — jeżeli baza rozpoczyna się od szóstego wiersza, to nic się nie zmienia. Trzeba tylko odpowiednio dobrać zakres danych źródłowych. Gdy mowa o „pierwszym wierszu”, to chodzi o pierwszy wiersz zakresu, a nie arkusza.
Jakkolwiek nie sprawdzałem, nie widzę powodu, aby opisana technika miała nie działać na zakresie źródłowym bez zdefiniowanych etykiet kolumn. Jedynie podczas tworzenia nazwanego zakresu (a potem filtru) trzeba odznaczyć pole „Zawiera nagłówki kolumn”. Jeżeli opcja ta będzie zaznaczona, to Calc zawsze będzie kopiował pierwszy wiersz z zakresu — niezależnie od tego, czy spełnia on kryteria czy też nie.
Dziękuję za pomoc 🙂
Robiłem wszystko wg pańskich wskazówek, ale nie wyświetlały się informacje o zakresie danych, którego dotyczy filtrowanie.
Tak jak Pan zalecił, w takim wypadku powtórzyłem całą operację od początku, ale bez skutku. Po kilku dniach wróciłem do tematu, w ten sam sposób powtórzyłem całą operację i w końcu się udało 🙂
Mam jeszcze jedno pytanie:
Jeśli źródłowa baza danych jest sformatowana, np. czcionki mają kolor zielony, a tło żółty, to dokładnie w ten sam sposób skopiują się do drugiego arkusza. Jak pozbyć się formatowania tak, aby kopiowała się jedynie zawartość komórek z zachowaniem formatowania drugiego arkusza?
Nie widzę nigdzie opcji do skopiowania samej zawartości, z pominięciem formatowania. Obawiam się, że nie jest możliwe wykonanie tego automatycznie.
Zawsze można usunąć formatowanie nałożone na komórki docelowe przy pomocy menu Format → Wyczyść formatowanie bezpośrednie. Ale to usunie także sposób prezentacji danych (np. liczby jako daty), może więc prowadzić do nieoczekiwanych i niechcianych efektów. No i trzeba to robić po każdym odświeżeniu danych. Nie jest to więc rozwiązanie bez wad.
Owszem, nie jest to rozwiązanie bez wad, ale bardzo ułatwia pracę.
Teraz ta operacja sprowadza się jedynie do kilku kliknięć, zamiast ręcznego przepisywania.
Serdecznie dziękuję Panu za ten temat i wszystkie odpowiedzi na pytania.
Powodzenia w dalszym prowadzeniu poradnika.