25 January 2023

Uzupełnianie danych między tabelami

Kontynuując wątek tabel dziś zajmiemy się uzupełnianiem danych tabeli za pomocą danych z innej tabeli.

Uzupełnianie danych między tabelami
theme-w-ico
theme-ico

Opis filmu

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

 

 

 

Pobierz darmowy materiał szkoleniowy:

Dzielimy się
wiedzą

Nasza Firma zapewnia nie tylko najnowsze rozwiązania z dziedziny IT ale również nie pozostaje w miejscu każdego dnia poszerza swoją wiedzę którą bardzo chętnie dzielimy się z innymi. Zapraszamy do zapoznania się z ostatnimi nowinkami.

theme-w-ico
arrows-ico-wh
Imię i Nazwisko łączenie, rozdzielanie, zamiana kolejności

Z doświadczenia wiem, że często powtarzającym się problemem jest obróbka imion i nazwisk. Czy powinny być w dwóch kolumnach czy w jednej i w jakiej kolejności ?

theme-w-ico
arrows-ico-wh
POWER APPS – AUDYT część 4

Zapraszamy na ostatni odcinek instrukcji tworzenia w Power Apps aplikacji umożliwiającej audytowanie dowolnych procesów przedsiębiorstwa.

theme-w-ico
arrows-ico-wh
POWER APPS – AUDYT część 3

Zapraszam na trzeci odcinek instrukcji tworzenia w Power Apps aplikacji umożliwiającej edytowanie dowolnych procesów przedsiębiorstwa.

 

theme-w-ico
theme-ico

Poznajmy się
i porozmawiajmy!

arrows-b

Skontaktuj się z nami i otwórz drzwi do innowacyjnych rozwiązań technologicznych dla Twojej firmy. Nasz zespół ekspertów jest gotów odpowiedzieć na Twoje pytania, dostosować ofertę do Twoich potrzeb i wesprzeć Cię w osiągnięciu sukcesu.

Wykorzystaj potencjał technologii już dziś, by zwiększyć efektywność, rozwijać biznes i zadbać o bezpieczeństwo swoich danych.

zespół
theme-ico