Kategorie szkoleń | Egzaminy | Kontakt

Pytanie

Pytanie o możliwości MS Excel z dodatkami PowerQuery i PowerPivot

  • 3
  • 3
  • 423

Dzień dobry.

Drodzy koledzy, czy w dobie dodatków do Excela typu PowerQuery, PowerPivot, PowerView itp. możliwe jest zrobienie w MS Excel 2013 rzeczy następującej:

 

  1. Przyjmijmy, że mamy dwa arkusze („Osoby” oraz „Zdarzenia”), w których znajdują się następujące dane:


 

 

 

Dane w tych arkuszach będą się zmieniać.

W arkuszu „Osoby” danych może przybyć, ale też może się zmienić np. numer telefonu.

W arkuszu „Zdarzenia”  rekordów będzie systematycznie przybywać (każdy z nowym E_ID), jak również (uwaga!) istniejące rekordy mogą ulec modyfikacji. Przykładowo rekord:

 

19   

Dodano

342

10 gru 14

10

 

Może zostać zmieniony na:

 

19   

Zmodyfikowano

795

23 gru 14

4

 

lub też:

 

19   

Zmodyfikowano

12

10 gru 14

10

 

Czyli mamy relacyjną bazę danych, P_ID i U_ID są kluczami unikatowymi.

 

W nowym arkuszu o nazwie „Kontrola operacyjna” (który odwoływać się będzie do arkuszy „osoby” i „zdarzenia” jako do źródeł danych) chciałbym móc:

 

  1. Zobrazować dane z obu arkuszy przedstawione w formie tabeli poniższego typu  z dodatkowymi polami (kolumnami), których nie ma w tabelach źródłowych (potwierdzono i kod):


 

  1. Ale to nie wszystko – modyfikacja, a w szczególności dodanie wiersza, powodowałoby dodanie lub modyfikację wierszy w tabelach źródłowych:


 

Czyli  jednym słowem: chciałbym potraktować Excela jako małą bazę danych. Wiem, że do tego służy Access, niemniej dodatki typu PowerPivot zaciekawiły mnie co do swojej funkcjonalności.

Pozdrawiam.

Załączniki

  • xlsx

    Przykładowe dane.xlsx ( 23K )
Uczestnik szkolenia
  • Zapytał
  • @ Uczestnik szkolenia | 03.12.2014
Zaloguj się aby zadać pytanie
Pokrewne

Odpowiedzi (3)

  • 11

Cześć.

Jak rozumiem masz 2 tabele i chcesz by ich wynik - złączenie - został przedstawiony w 1 tabeli. Dodatkowo do tego wykres i wszystko ma się aktualizować wraz z dopisaniem pozycji.

Zrobisz coś podobnego bez PowerPivot czy VBA , ale co do automatycznej zmiany, to nie jestem pewny, bo wraz z dopisaniem pozycji zmienisz zakres danych źródłowych. Inaczej mówiąc, wykres czy tabelka nie pokażą nowych pozycji.

Dwie tabelki złączysz najszybciej za pomocą wyszukaj.pionowo.

Zadbaj, aby zakres źródła danych przewidywał dopisywanie pozycji.

PowerPivot w wersji 2013 - jak najbardziej, ale na tym etapie bez tego powinno wystarczyć.

Jeśli chodzi o automatyczne aktualizacje, wykresy czy tabelki, o nowe wartości z poza źródła,  to w PowerPivot chyba można to zrobić; nie robiłem tego, ale pamiętam, że chyba dało radę zrobić. Pytanie, czy do tak małej bazy chcesz używać PP.

W VBA jest to też dość proste i chyba "na dzień dobry" dla mnie najłatwiejsze, wstępnie daje najzwyklejszą tabelę w celu zobrazowania mechanizmu działania. Kasuje wykres, wstawia nowy z nowymi danymi.

Sub RefreshCHart()



ActiveSheet.ChartObjects.Delete
    
    
' A1 oznacza 1 komórkę w tabeli
ZakrAdr = Range("A1").CurrentRegion.Address

    
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Range(ZakrAdr)

End Sub
Łukasz_Bogucki
  • Odpowiedział
  • @ Łukasz_Bogucki | 10.12.2014
    • 2
    • 2
    • 4
  • 2

Dziękuję za odpowiedź. ;)

Nie do końca mam: "2 tabele i chcę by ich wynik - złączenie - został przedstawiony w 1 tabeli. " To jest proste i rzeczywiście nie wymaga PowerPivota.

Mam 3 relacje połączone więzami integralności.Wykres tutaj nie ma znaczenia.

Mówiąc językiem arkusza kalkulacyjnego: mam 3 tabele (relacje) danych związane ze sobą więzami.

Właściwie moje pytanie jest takie: Czy więzy integralności pomiędzy tabelami (relacjami) w PowerPivot zapewnią mi odpowiedni mechanizm transakcyjny, umożliwiający dodanie, modyfikację lub usunięcie danych z dowolnej tablicy, zachowując integralność danych w schemacie (modelu)?

Czyli bazując na powyższym przykładzie: zdarzenia, osoby, kontrola operacyjna.

Zmieniam ze zdarzenia -> zmieni mi się odpowiednio w powiązanych tablicach.

Zmieniam w osoby -> zmieni mi się odpowiednio w powiązanych tablicach.

Zmieniam w kontrolę operacyjną -> zmieni mi się odpowiednio w powiązanych tablicach (tutaj osoby i zdarzenia).

Wyszukaj pionowo znam, ale ta funkcja zakłada, że jest coś takiego, jak dane źródłowe i arkusz wynikowy, czyli działam tylko w jedną stronę - robię raport na bazie danych. A ja chcę we wszystkie strony. ;)

Być może niesłusznie założyłem, że jeżeli w PowerPivot tworzymy model danych i więzy integralności pomiędzy relacjami (czyt. tablicami), to jest to mechanizm przypominający bazę danych i zapewni mi wcześniej wspomniane mechanizmy transakcyjne, aby móc zmieniać, dodawać lub usuwać gdziekolwiek zechcę.

Z tym założeniem spróbowałem użyć PowerPivot i jakoś mi nie wyszło - stąd moje pytanie. ;)

Pozdrawiam.
Paweł.

 

PS. Jest trochę zamieszanie pojęciowe: ja, posługując się terminologią modelu doktora E. F. Codd'a (relacyjnych baz danych), na tabelę mówię relacja (bo dane są łączone w relacje w postaci tabel, chociaż ta relacja nie musi być wcale dwuwymiarowa), zaś powiązania pomiędzy relacjami w schemacie nazywam więzami integralności.

Excel na relację mówi tabela, na więzy integralności  mówi relacja (co właściwie też jest poprawne), zaś na schemat - model danych.

Ale jak go zwał, tak go zwał - chyba się rozumiemy, co do pojęć.

 

  • Odpowiedział
  • @ | 11.12.2014
  • TRENER MODERATOR ALTKOM AKADEMII
  • 1

PowerPivot jest narzędziem raportowym, arkusz wynikowy nie ma możliwości zmiany danych wejściowych. Musisz edytować dane wejściowe.

  • Odpowiedział
  • @ | 07.01.2015
  • TRENER ALTKOM AKADEMII

POWIĄZANE
w bazie wiedzy

quiz

11 trudnych pytań z Excela

Sprawdź czy jesteś mistrzem Excela

  • Oprogramowanie wspierające
  • Excel
blog

Znowu ten wszędobylski Excel

Choć w zakładach produkcyjnych korzystamy ze specjalistycznych systemów finansowo-księgowych, kadrowych, do planowania produkcji i zarządzania magazynem, to nadal jednym z najpopularniejszych programów jest MS Excel. Skoro tak, to warto lepiej poznać możliwości tej aplikacji oraz wykorzystywać je w codziennej pracy z danymi.
  • Excel
  • formularze
  • szablony
webinarium

Power BI Desktop Wykres punktowy

  • Excel