Model danych Excel (spis treści)

  • Wprowadzenie do modelu danych w programie Excel
  • Jak utworzyć model danych w programie Excel?

Wprowadzenie do modelu danych w programie Excel

Funkcja modelu danych programu Excel umożliwia łatwe budowanie relacji między łatwym raportowaniem a ich zestawami danych w tle. To znacznie ułatwia analizę danych. Umożliwia integrację danych z wielu tabel rozmieszczonych w wielu arkuszach poprzez proste budowanie relacji między pasującymi kolumnami. Działa całkowicie za sceną i znacznie upraszcza funkcje raportowania, takie jak tabela przestawna itp.

W naszym artykule postaramy się pokazać, jak utworzyć tabelę przestawną z dwóch tabel, wykorzystując funkcję Model danych, ustanawiając w ten sposób relację między dwoma obiektami tabeli, a tym samym tworząc tabelę przestawną.

Jak utworzyć model danych w programie Excel?

Zobaczmy, jak utworzyć model danych w programie Excel z kilkoma przykładami.

Możesz pobrać ten szablon Excel modelu danych tutaj - Szablon Excel modelu danych

Przykład 1

  • Mamy listę produktów i kod półki dla każdego produktu. Potrzebujemy tabeli, w której mamy opis półki wraz z kodami półek. Jak więc włączyć opisy półek do każdego kodu półek? Być może wielu z nas uciekłoby się do korzystania z WYSZUKAJ.PIONOWO tutaj, ale całkowicie wyeliminujemy potrzebę używania WYSZUKAJ.PIONOWO tutaj za pomocą modelu danych Excel.

  • Tabela po lewej to tabela danych, a tabela po prawej to tabela odnośników. Jak widać z danych, możliwe jest utworzenie relacji w oparciu o wspólne kolumny.

  • Teraz model danych jest zgodny tylko z obiektami tabel. Czasami może być konieczne przekształcenie zestawów danych w obiekty tabel. Aby to zrobić, wykonaj poniższe kroki.
  1. Kliknij lewym przyciskiem myszy w dowolnym miejscu zestawu danych.
  2. Kliknij kartę Wstaw i przejdź do tabeli w grupie Tabele lub po prostu naciśnij Ctrl + T.
  3. Usuń zaznaczenie lub zaznacz opcję Moja tabela ma opcję Nagłówek. W naszym przykładzie rzeczywiście ma nagłówek. Kliknij OK.
  4. Nadal skupiając się na nowej tabeli, musimy podać nazwę, która ma znaczenie w polu Nazwa (po lewej stronie paska formuły).

W naszym przykładzie nazwaliśmy tabelę Personel.

  • Teraz musimy wykonać ten sam proces dla tabeli odnośników i nazwać ją Kodem półki.

Tworzenie relacji

Najpierw przejdziemy do zakładki Dane, a następnie wybierz Relacje w podgrupie Narzędzia danych. Po kliknięciu opcji Relacje na początku, ponieważ nie ma relacji, więc nie będziemy mieli nic.

Najpierw kliknij Nowy, aby utworzyć relację. Będziemy teraz musieli podać nazwy tabeli podstawowej i tabeli przeglądowej z listy rozwijanej, a następnie wspomnieć o kolumnie, która jest wspólna dla dwóch tabel, abyśmy mogli ustalić relacje między dwiema tabelami z listy rozwijanej kolumn.

  • Teraz podstawowa tabela jest tabelą zawierającą dane. Jest to główna tabela danych - Tabela 5. Z drugiej strony Powiązana tabela jest tabelą, która zawiera dane odnośników - to nasza tabela odnośników ShelfCodesTable. Tabela podstawowa to taka, która jest analizowana na podstawie tabeli przeglądowej, która zawiera dane przeglądowe, dzięki czemu raportowane dane będą bardziej znaczące.

  • Tak więc wspólną kolumną między dwiema tabelami jest kolumna Kod półki. Właśnie tego użyliśmy do ustalenia relacji między dwiema tabelami. Przechodząc do kolumn, Kolumna (obca) odnosi się do tabeli danych, w której mogą znajdować się zduplikowane wartości. Z drugiej strony Powiązana kolumna (podstawowa) odnosi się do kolumny w tabeli odnośników, w której mamy unikalne wartości. Po prostu konfigurujemy pole do wyszukiwania wartości z tabeli odnośników w tabeli danych.
  • Gdy to skonfigurujemy, Excel utworzy relację między nimi za sceną. Integruje dane i tworzy model danych oparty na wspólnej kolumnie. Jest to nie tylko lekkie wymagania dotyczące pamięci, ale także znacznie szybsze niż użycie WYSZUKAJ.PIONOWO w dużych skoroszytach. Po zdefiniowaniu modelu danych Excel będzie traktował te obiekty jak tabele modelu danych zamiast tabeli arkusza.
  • Teraz, aby zobaczyć, co porabiał Excel, możemy kliknąć Zarządzaj modelami danych w danych -> Narzędzia danych.

  • Możemy również uzyskać schematyczną reprezentację modelu danych, zmieniając widok. Klikamy opcję Widok. Spowoduje to otwarcie opcji widoku. Następnie wybierzemy widok diagramu. Następnie zobaczymy schematyczną reprezentację pokazującą dwie tabele i relacje między nimi, tj. Wspólną kolumnę - Kod półki.

  • Powyższy schemat pokazuje relację jeden do wielu między unikalnymi wartościami tabeli odnośników a tabelą danych ze zduplikowanymi wartościami.
  • Teraz będziemy musieli stworzyć tabelę przestawną. Aby to zrobić, przejdziemy do zakładki Wstaw, a następnie kliknij opcję Tabela przestawna.

W oknie dialogowym Utwórz tabelę przestawną w oknie dialogowym Utwórz tabelę przestawną wybierzemy źródło jako „Użyj modelu danych tego skoroszytu”.

  • Spowoduje to utworzenie tabeli przestawnej i możemy zobaczyć, że obie tabele źródłowe są dostępne w sekcji źródłowej.

  • Teraz stworzymy tabelę przestawną pokazującą liczbę osób, które mają półki.

  • Wybierzemy Personel w sekcji Wiersze z Tabeli 5 (tabela danych), a następnie Opis (tabela odnośników).

  • Teraz przeciągniemy kod półki z tabeli 5 do sekcji Wartości.

  • Teraz dodamy Miesiące z Tabeli 5 do sekcji Rzędy.

  • Lub możemy dodać miesiące jako filtr i dodać go do sekcji Filtry.

Przykład nr 2

  • Teraz pan Basu prowadzi fabrykę o nazwie Basu Corporation. Pan Basu próbuje oszacować przychody na 2019 r. Na podstawie danych z 2018 r.
  • Mamy tabelę, w której mamy przychody za 2018 r., A następnie przychody na różnych poziomach przyrostowych.

  • Mamy więc przychody na 2018 r. - 1, 5 mln USD, a minimalny wzrost oczekiwany w następnym roku wynosi 12%. Pan Basu chce tabeli, która pokaże przychody na różnych poziomach przyrostowych.
  • Stworzymy następującą tabelę dla prognoz na różnych poziomach przyrostowych na 2019 r.

  • Teraz podamy w pierwszym wierszu Przychody odniesienie do szacowanego minimalnego przychodu na 2019 r., Tj. 1, 68 mln USD

  • Po użyciu formuły odpowiedź pokazano poniżej.

  • Teraz wybierzemy całą tabelę, tj. D2: E12, a następnie przejdź do Dane -> Prognoza -> Analiza „co jeśli” -> Tabela danych.

  • Otworzy się okno dialogowe Tabela danych. Tutaj wprowadzimy minimalny procent przyrostu z komórki B4 w komórce wprowadzania kolumny. Powodem tego jest to, że nasze przewidywane procenty wzrostu w tabeli są ułożone kolumnowo.

  • Po kliknięciu przycisku OK analiza „co-jeśli” automatycznie zapełni tabelę prognozowanymi przychodami o różnych przyrostowych wartościach procentowych.

Przykład nr 3

  • Załóżmy teraz, że mamy taki sam scenariusz jak powyżej, tyle że teraz mamy także inną oś do rozważenia. Załóżmy, że oprócz pokazania prognozowanych przychodów w 2019 r. Na podstawie danych z 2018 r. I minimalnej oczekiwanej stopy wzrostu, mamy teraz również szacunkową stopę dyskontową.

  • Najpierw przygotujemy tabelę pokazaną poniżej.

  • Teraz odniesiemy się do minimalnego prognozowanego przychodu na 2019 r., Tj. Od komórki B5 do komórki D8.

  • Teraz wybierzemy całą tabelę, tj. D8: J18, a następnie przejdź do Dane -> Prognoza -> Analiza „co jeśli” -> Tabela danych.

  • Otworzy się okno dialogowe Tabela danych. Tutaj wprowadzimy minimalny procent przyrostu z komórki B3 w komórce wprowadzania kolumny. Powodem tego jest to, że nasze przewidywane procenty wzrostu w tabeli są ułożone kolumnowo. Teraz dodatkowo dodatkowo wprowadzimy procent rabatu minimalnego z komórki B4 w komórce Input Row. Powodem tego jest to, że nasze przewidywane procenty rabatów w tabeli są ułożone w rzędy.

  • Kliknij OK. Spowoduje to, że analiza „co-jeśli” automatycznie zapełni tabelę prognozowanymi przychodami przy różnych przyrostowych wartościach procentowych według wartości procentowych rabatów.

O czym należy pamiętać o modelu danych w programie Excel

  • Po pomyślnym obliczeniu wartości z tabeli danych proste Cofnij, tj. Ctrl + Z, nie będzie działać. Możliwe jest jednak ręczne usunięcie wartości z tabeli.
  • Nie można usunąć pojedynczej komórki z tabeli. W Excelu jest opisany jako tablica, dlatego będziemy musieli usunąć wszystkie wartości.
  • Musimy właściwie wybrać komórkę wejściową wiersza i komórkę wejściową kolumny.
  • Tabela danych, w przeciwieństwie do tabeli przestawnej, nie musi być odświeżana za każdym razem.
  • Korzystając z modelu danych w programie Excel, możemy nie tylko poprawić wydajność, ale także zmniejszyć wymagania dotyczące pamięci w dużych arkuszach.
  • Modele danych znacznie upraszczają naszą analizę w porównaniu do korzystania z wielu skomplikowanych formuł w całym skoroszycie.

Polecane artykuły

Jest to przewodnik po modelu danych w programie Excel. Tutaj omawiamy sposób tworzenia modelu danych w programie Excel wraz z praktycznymi przykładami i szablonem programu Excel do pobrania. Możesz także przejrzeć nasze inne sugerowane artykuły -

  1. Pasek formuły w programie Excel
  2. Drukuj linie siatki w programie Excel
  3. Obejrzyj okno w programie Excel
  4. Excel SUMIFS z datami

Kategoria:

Porady Excel