Pracując z bazami danych o klientach spotykamy się z sytuacją, gdy chcemy dokonać wpisu przy danym kliencie np. o godzinie preferowanego odbioru towaru. W takiej sytuacji ważne, jest, żeby wpis ten automatycznie przekopiował się do wszystkich komórek powiązanych z danym klientem. Zadanie to przećwiczymy używając warunkowego wypełniania danych w dół. Sprawdź jak to działa!
Z lekcji dowiesz się:
Co to jest warunkowe wypełnianie?
Jak wstawić uwagi tylko dla jednego klienta na liście?
Przy jakich poleceniach można użyć wypełniania?
Z lekcji dowiesz się:
Co to jest warunkowe wypełnianie?
Jak wstawić uwagi tylko dla jednego klienta na liście?
Przy jakich poleceniach można użyć wypełniania?
W tym wideo zajmiemy się zagadnieniem warunkowego wypełniania danych w dół. Zadanie to wykonamy na podstawie przykładowych danych z rys. 1.
(Rys. 1) Przykładowe dane
Zadanie będzie polegało na wypełnieniu danych w kolumnie Uwagi, ale tylko dla tego samego klienta, dla którego mamy wypełnioną pierwszą uwagę. Natomiast nie będziemy chcieli aby wypełniły się pola z uwagami dla pozostałych klientów.
Zaczniemy od pokazania standardowego wypełniania w dół, czyli wypełnimy kolumnę Sprzedawca dla poszczególnych klientów. W tym celu zaznaczamy zakres danych, który nas interesuje (kolumna Sprzedawca, ale bez nazwy kolumny) i rozwijamy polecenie Znajdź i zaznacz (punkt 2 na rys. 2) z karty Narzędzia główne (punkt 1), następnie wybieramy polecenie Przejdź do – specjalnie (punkt 3).
(Rys. 2) Ścieżka dostępu do polecenia Przejdź do – specjalnie
Otworzy się okno Przechodzenia do – specjalnie, gdzie na liście elementów do zaznaczenia musimy zaznaczyć checkbox przy typie komórek Puste. Wybrany parametr zatwierdzamy przyciskiem OK (rys. 3).
(Rys. 3) Okno Przechodzenia do - specjalnie
Otrzymamy zaznaczone tylko puste komórki w kolumnie Sprzedawca przedstawione na rys. 4.
(Rys. 4) Zaznaczone puste komórki w kolumnie
Kiedy mamy już zaznaczone odpowiednie komórki, musimy uważać, aby nie odznaczyć tych komórek, czyli np. nie klikać myszką. Następnie naciskamy znak równa się (=) oraz strzałkę w górę. Sprawi to, że każda zaznaczona komórka będzie „patrzeć” na komórkę powyżej, czyli pierwszy przedział zostanie wypełniony wartościami z komórki A2, drugi z komórki A9, a trzeci z komórki A19. Formułę tą musimy zatwierdzić skrótem klawiszowym Ctrl+Enter, aby dane wypełniły się tymi samymi wartościami (rys. 5).
(Rys. 5) Wypełniona kolumna Sprzedawca
Należy zwrócić uwagę, aby nie sortować tak wypełnionych danych, ponieważ wtedy formuła, której użyliśmy może pobrać dane ze złej komórki jak pokazano na rys. 6.
(Rys. 6) Błędnie wypełnione dane, wynikające z posortowania danych
Cofamy nasze sortowanie za pomocą skrótu klawiszowego Ctrl+Z. Aby uniknąć takich błędów, zaznaczamy całą kolumnę A, kopiujemy ją za pomocą skrótu klawiszowego Ctrl+C i klikamy na jej obszarze prawym przyciskiem myszy. Następnie z podręcznego menu wybieramy polecenie Wartość (W) z grupy poleceń Opcje wklejania – od Excela 2010 (rys. 7).
(Rys. 7) Polecenie Wartość (W) z grupy poleceń Opcje wklejania
Otrzymamy wklejone wartości do poszczególnych komórek, a nie formułę z odwołaniem do komórki powyżej, czyli nie pojawią się błędy przy sortowaniu danych.
Przy warunkowym wypełnianiu w dół przebiega to podobnie. Tym razem użyjemy więcej skrótów klawiszowych. Zaznaczamy zakres kolumny Uwagi bez nazwy kolumny, następnie przy użyciu skrótu klawiszowego Ctrl+G otwieramy okno Przechodzenia do. W oknie tym klikamy przycisk Specjalnie, aby przejść do okna Przechodzenia do – specjalnie (rys. 8).
(Rys. 8) Okno Przechodzenia do
Otworzy się okno Przechodzenia do – specjalnie, gdzie zaznaczamy checkbox przy typie komórek Puste jak na rys. 3 i zaznaczenie tego parametru zatwierdzamy przyciskiem OK. Otrzymamy zaznaczone tylko puste komórki w kolumnie Uwagi przedstawione na rys. 9.
(Rys. 9) Zaznaczone puste komórki w kolumnie Uwagi
Teraz po wpisaniu znaku Równa się, musimy stworzyć bardziej skomplikowaną formułę niż w przypadku kolumny Sprzedawca, która będzie sprawdzać, czy nie zmienił się klient. Skorzystamy tutaj z funkcji JEŻELI i odpowiedniego testu logicznego.
Pierwszy argument Funkcji to test_logiczny, czyli będziemy sprawdzać czy klient powyżej jest taki sam jak aktualny (rozpatrywany). Zapis tego argumentu powinien wyglądać następująco: B2=B3. Jeśli warunek ten zostanie spełniony (argument wartość_jeżeli_prawda), to chcemy otrzymać wartość z komórki powyżej z kolumny Uwagi (D2). Jeśli natomiast warunek nie zostanie spełniony (argument wartość_jeżeli_fałsz), to chcemy otrzymać pusty ciąg tekstowy (zapis ""). Zapis całej formuły powinien wyglądać następująco:
=JEŻELI(B2=B3;D2;"")
Powyższą formułę zatwierdzamy skrótem klawiszowym Ctrl+Enter. Otrzymamy wypełnioną tabelę tylko w komórkach, spełniających warunek z funkcji JEŻELI przedstawioną na rys. 10.
(Rys. 10) Warunkowo wypełniona kolumna Uwagi
Po raz kolejny musimy zwrócić uwagę na fakt, że komórki te są wypełnione formułami, co widać na rys. 11.
(Rys. 11) Komórki wypełnione formułami
Pamiętamy, że komórki wypełnione kolumnami przy sortowaniu zwrócą błędne dane. Z tego względu musimy zaznaczyć całą kolumnę Uwagi i skopiować za pomocą skrótu klawiszowego Ctrl+C,. Następnie rozwijamy polecenie Wklej (punkt 2 na rys. 12) z karty Narzędzia główne (punkt 1) i wybieramy polecenie Wartość (W) – punkt 3.
(Rys. 12) Ścieżka dostępu do polecenia Wartość (W)
Otrzymamy kolumnę Uwagi z wklejonymi wartościami zamiast formuł, dzięki czemu przy np. sortowaniu danych, nie otrzymamy błędów (rys. 13).
(Rys. 13) Dane w kolumnie Uwagi wklejone jako wartości
Możemy teraz przetestować, czy dane będą prawidłowe po sortowaniu, w tym celu ustawiamy aktywną komórkę w kolumnie Sprzedaż i wybieramy polecenie Sortuj od A do Z z karty Dane (rys. 14).
(Rys. 14) Polecenie Sortuj od A do Z z karty Dane
Otrzymamy posortowane dane z prawidłowymi danymi w kolumnie Uwagi przedstawione na rys. 15.
(Rys. 15) Dane posortowane po kolumnie Sprzedaż
Możemy zauważyć, że np. przy każdym wystąpieniu klienta Rozsądny Robert w kolumnie Uwagi mamy informację, że odbiera on zamówienie po godzinie 17.00, mimo iż dane zmieniły kolejność.
Podsumowując, dzięki zamianie formuł na wartości przy sortowaniu - Excel zachowuje spójność między danymi.
Polecamy także:
© Sprytny Excel