Kontynuując wątek tabel dziś zajmiemy się uzupełnianiem danych tabeli za pomocą danych z innej tabeli. Często zdarza się, że dostajemy dwie tabele których początkowe kolumny się zgadzają ale kolejne zawierają różne informacje, zaś naszym zadaniem jest uzupełnienie danych w jednej z tabel za pomocą danych z drugiej tabeli. Jeżeli tabele mają dużą i różną ilość wierszy zadanie nie wydaje się takie proste. W naszym przykładzie wykorzystamy tabelę zawierającą dane finansowe projektów oraz drugą zawierającą informację o handlowcu który dany projekt wygrał
Aby nasze zadanie wykonać skutecznie po pierwsze musimy zidentyfikować kolumny po których będziemy poszukiwali informacji pomiędzy oboma tabelami. W naszym przypadku jest to kolumna Nr z Tabeli 1 oraz Projekt z Tabeli 2 gdyż obie one zawierają numery naszych projektów.
Drugim niezmiernie ważnym elementem jest ustalenie czy wybrana przez nas kolumna w tabeli z której będziemy wczytywali dane czyli Tabeli 2 ma unikatowe wartości. Gdyby tak nie było a dane zawarte w zdublowanych rekordach różniły się od siebie w kolejnych kolumnach moglibyśmy uzyskać zaburzony wynik gdyż tylko pierwszy napotkany rekord zostanie przepisany do Tabeli 1. Aby upewnić się czy taka sytuacja może mieć miejsce zgodnie z tym co pokazywałem już w poprzednim odcinku zaznaczymy kolumnę Projekt i wybierzemy Menu Narzędzia główne -> Formatowanie warunkowe -> Reguły wyróżniania komórek -> Duplikowanie się wartości. Jeżeli żadna z komórek nie zostanie zaznaczona na czerwono możemy dalej działać bez obaw jeżeli natomiast znajdą się takie rekordy musimy zdecydować co z nimi zrobić.
Teraz aby przenieść nasze dane z Tabeli2 do Tabeli1 w pierwszym wierszu Tabeli1 kolumny Oddział wpiszemy funkcję =X.WYSZUKAJ([@Nr];Tabela2[Projekt];Tabela2[Oddział]) która wyszukuje wskazaną wartość w określonej kolumnie i zwraca wartość z innej kolumny wiersza w którym dana wartość została znaleziona. W naszym przypadku uzyskamy następujący efekt
Jak widać Excel znalazł 4 rekordy zdublowane w obu tabelach. Teraz posortujmy tabele tak aby zdublowane rekordy znalazły się na początku tabel. W obu tabelach wykonajmy następujące czynności Rozwijamy menu w kolumnie Nr1 i Nr2 oraz wybieramy Sortuj od najmniejszych do największych
Analogicznie w kolumnie MPK wpiszemy =X.WYSZUKAJ([@Nr];Tabela2[Projekt];Tabela2[MPK]) oraz w kolumnie Handlowiec =X.WYSZUKAJ([@Nr];Tabela2[Projekt];Tabela2[Handlowiec])
Dzięki wykorzystaniu trzy razy tej prostej funkcji otrzymamy szybko wypełnioną całą Tabelę 1 niezależnie od ilości wierszy które posiada.
Teraz odwróćmy nieco nasze zadanie i wyobraźmy sobie, że jesteśmy odpowiedzialni za wypełnianie Tabeli 1 łącznie z Handlowcem i Oddziałem . Za każdym razem gdy dodamy projekt musimy wpisać handlowca a następnie związany z nim oddział oraz MPK. Wiemy jednak, że handlowiec jest jednoznacznie przypisany do oddziału i MPK więc ich każdorazowe dopisywanie jest stratą czasu. Chcielibyśmy aby wybór handlowca spowodował automatyczny wybór oddziału i MPK.
W tym celu na bazie Tabeli 1 utwórzmy Tabelę 3 w której będziemy ręcznie uzupełniać Handlowca oraz krótką Tabelę 4 która zawiera przypisania Handlowca do Oddziału i MPK zgodnie z poniższym przykładem
Teraz tak jak pokazywałem w odcinku 2 stwórzmy w kolumnie Handlowiec Tabeli 3 menu wyboru naszego Handlowca . Zaznaczamy kolumnę Handlowiec i wybieramy Menu Dane -> Poprawność danych -> Lista
A następnie w Źródło wprowadzamy kolumnę Handlowiec Tabeli 4. W efekcie uzyskujemy w Tabeli 3 kolumnie Handlowiec możliwość wyboru Handlowca spośród tych wpisanych do Tabeli 4.
Teraz jak w poprzednim przypadku potrzebujemy uzupełnić dane w Tabeli 3 danymi z Tabeli 4 na podstawie kolumny klucza którą jest tym razem Handlowiec. Analogicznie jak poprzednio wpiszmy w pierwszy wiersz kolumny MPK =X.WYSZUKAJ([@Handlowiec];Tabela4[Handlowiec];Tabela4[MPK]) co da nam następujący efekt
Po wyborze handlowca pojawi się automatycznie jego MPK ale w miejscach gdzie jeszcze nie wpisaliśmy handlowca mamy mało elegancki błąd #N/D co oznacza, że wartość nie została znaleziona w Tabeli 4.
Mamy 3 drogi aby tego uniknąć. Albo dodamy do naszej formuły element mówiący co powinno pojawić się gdy formuła napotka błąd czyli zamiast poprzedniej formuły wpiszemy =JEŻELI.BŁĄD(X.WYSZUKAJ([@Handlowiec];Tabela4[Handlowiec];Tabela4[MPK]);””) w tym przypadku “” oznacza nie wyświetlaj niczego ale można między apostrofy wpisać dowolne słowo.
Albo dodamy do naszej Tabeli 4 wiersz który w kolumnie Handlowiec będzie pusty a w kolumnach MPK i Oddział będzie zawierał dowolnie wybrane przez nas teksty np. „Brak” lub będzie pusty (należy wpisać pojedynczy apostrof ‘).
Albo wykorzystamy natywną możliwość funkcji x.wyszukaj co się ma stać gdy szukana wartość nie zostanie znaleziona i zmodyfikujemy naszą formułę następująco: X.WYSZUKAJ([@Handlowiec];Tabela4[Handlowiec];Tabela4[MPK];””)
Teraz analogicznie jak w opcji trzeciej dla kolumny Oddział wpiszemy =X.WYSZUKAJ([@Handlowiec];Tabela4[Handlowiec];Tabela4[Oddział];”brak handlowca”) co da nam następujący efekt automatycznie uzupełnianych kolumn
Ta bardzo prosta „automatyzacja” w przypadku konieczności wyboru wielu zależnych od siebie kolumn potrafi naprawdę zaoszczędzić mnóstwo czasu i błędów które przy ręcznym uzupełnianiu są bardzo prawdopodobne.
Zapraszam Was do korespondencji na temat tabel i wszystkiego co się wiąże z Excelem a co sprawia wam jakieś problemy na adres konrad.pogodz@itch.pl
Dzięki darmowym materiałom szkoleniowym będziesz mógł przećwiczyć nabytą wiedzę i sprawdzić ją w praktyce. Pobierz i ćwicz natychmiast.
