Odtwórz wideo

Uzupełnianie danych między tabelami

Udostępnij ten film:

Ocena tego filmu:

5/5 - (6 ocen)

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 MPKOddział 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

Odcinek z którego dowiecie się jak uzupełniać dane tabeli za pomocą danych z innej tabeli

Film otagowano jako:

Poznaj autora wideo
Konrad Pogódź
Konrad Pogódź
Wiceprezes Zarządu
Pobierz darmowy materiał szkoleniowy

Dzięki darmowym materiałom szkoleniowym będziesz mógł przećwiczyć nabytą wiedzę i sprawdzić ją w praktyce. Pobierz i ćwicz natychmiast.

Odkryj podobne filmy

Zgłoszenie serwisowe

Dane firmy

Masz pytanie? Napisz do nas