Excel Forum - Porady, Pomoc,  Excel Help, Excel FAQ Strona Główna
 FAQ  RegulaminRegulamin  Szukaj   Użytkownicy   Grupy   Rejestracja   Profil   Twoje wiadomości   Zaloguj 


Poprzedni temat «» Następny temat
ID tematu: 63397 Skopiuj do schowka Wskaźnik HH - kwerenda parametryczna w PQ
Autor Wiadomość
z-kes 
Stały bywalec Excelforum


Pomógł: 28 razy
Posty: 420
Wysłany: 05-12-2018, 09:28   Wskaźnik HH - kwerenda parametryczna w PQ

Cześć
Chciałem zrobić skoroszyt liczący wskaźnik HH (ocenia koncentrację rynku, wartość zestandaryzowana [0; 1]).

Same obliczenia są bardzo łopatologiczne. Wystarczy dla serii danych obliczyć ich udziały procentowe, podnieść je do kwadratu i zsumować. Da się też to zrobić za pomocą formuł tablicowych, bez pomocniczych kolumn w tabeli.

Problem polega na tym, że dane źródłowe należy filtrować za pomocą różnych kryteriów. W przykładzie jest akurat rok ale można sobie wyobrazić, że będzie ich więcej (np. obszar geograficzny, grupa asortymentowa, typ producenta itd.). Czyli po wybraniu np. roku chcę policzyć WHH dla danych z wybranego roku.

Do tego najbardziej pasuje mi kwerenda parametryczna (stare Query :mrgreen: )
VBA - odpada :angry
Formuły tablicowe - jak znam życie to większa połowa Forumowiczów takie kejsy robi w czasie jaki potrzebuję, aby wpisać zapytanie do googla ale podejrzewam, że formuła (dla np. 3 kryteriów) będzie spora oraz przy dużej liczbie danych będzie to miało spory wpływ na wydajność pracy ze skoroszytem.


I stąd moje pytanie: jak to można zrobić w PQ.
Wiem, że PQ ma coś takiego jak parametry ale nie wiem jak to wykorzystać przy tego rodzaju historiach, nie wiem jak parametr połączyć z komórką arkusza, nie wiem czy można zautomatyzować odświeżenie tabeli po zmianie tego parametru oraz czy można to zrobić nie będąc wyjadaczem kodu M (najchętniej z poziomu interfejsu :tak ).

Pozdrawiam bardzo serdecznie
Dzidek

W HH.xlsx
Pobierz Plik ściągnięto 12 raz(y) 17.1 KB

ID posta: 357702 Skopiuj do schowka
 
 
DwaNiedźwiedzie 
Excel Expert



Pomógł: 206 razy
Posty: 535
Wysłany: 05-12-2018, 10:28   

Najpierw nadaj nazwę rok komórce ocena!B2, a później:
1) Formuła tablicowa (CSE):
Kod:
=SUMA(JEŻELI.BŁĄD((JEŻELI(Tab_rynek[Rok]=rok;Tab_rynek[Obroty '[mln zł']];"")/SUMA.JEŻELI(Tab_rynek[Rok];rok;Tab_rynek[Obroty '[mln zł']]))^2;0))

2) PQ:
Kod:
let
    Źródło = Excel.CurrentWorkbook(){[Name="Tab_rynek"]}[Content],
    filtr = Excel.CurrentWorkbook(){[Name="rok"]}[Content][Column1]{0},
    #"Przefiltrowano wiersze" = Table.SelectRows(Źródło, each ([Rok] = filtr)),
    #"Obliczona suma" = List.Sum(#"Przefiltrowano wiersze"[#"Obroty [mln zł]"]),
    #"Dodano udział" = Table.AddColumn(#"Przefiltrowano wiersze", "udział", each [#"Obroty [mln zł]"]/#"Obliczona suma"),
    #"Dodano kolumnę niestandardową" = Table.AddColumn(#"Dodano udział", "Dodano udział^2", each [udział]*[udział]),
    #"Obliczona suma1" = List.Sum(#"Dodano kolumnę niestandardową"[#"Dodano udział^2"])
in
    #"Obliczona suma1"

Kwerenda przy każdym odświeżeniu pobierze wartość z komórki rok, ale zautomatyzować odświeżania po zmianie kryterium bez VBA chyba się nie da.
ID posta: 357707 Skopiuj do schowka
 
 
z-kes 
Stały bywalec Excelforum


Pomógł: 28 razy
Posty: 420
Wysłany: 05-12-2018, 10:39   

Bardzo fajnie to działa
Co do odświeżania to mówi się trudno. Nie jest to jakaś skomplikowana historia.

Co do M.
Czy możesz napisać:
Kod:
= List.Sum(#"Dodano kolumnę niestandardową"[#"Dodano udział^2"])


można uzyskać wpisując coś z interfejsu? Kolumna niestandardowa już wiem mniej więcej o co chodzi. A to kolumną chyba nie jest...

Bardzo Ci dziękuję.

Dzidek

edit
Już znalazłem tę sumę: Przekształć/statystyka/suma
No to teraz szukam kroku Nawigacja:

Kod:
= Excel.CurrentWorkbook(){[Name="rok"]}[Content][Column1]{0}

Może jakaś mała podpowiedź? Jest to gdzieś na wstążce?
ID posta: 357709 Skopiuj do schowka
 
 
DwaNiedźwiedzie 
Excel Expert



Pomógł: 206 razy
Posty: 535
Wysłany: 05-12-2018, 12:23   

z-kes napisał/a:
No to teraz szukam kroku Nawigacja:

1) Zaznacz komórkę rok i pobierz z niej dane do PQ (opcja "Z tabeli/zakresu").
2) W PQ kliknij prawą myszą na pole z rokiem i wybierz Wyszczególnij.
Otrzymasz poniższy kod:
Kod:
Źródło = Excel.CurrentWorkbook(){[Name="rok"]}[Content],
Column1 = Źródło{0}[Column1]

...który można skrócić do:
Kod:
Excel.CurrentWorkbook(){[Name="rok"]}[Content]{0}[Column1]
ID posta: 357719 Skopiuj do schowka
 
 
z-kes 
Stały bywalec Excelforum


Pomógł: 28 razy
Posty: 420
Wysłany: 05-12-2018, 12:24   

Super.
Dziękuję po raz drugi.
D.
ID posta: 357720 Skopiuj do schowka
 
 
Wyświetl posty z ostatnich:   
Odpowiedz do tematu
Nie możesz pisać nowych tematów
Nie możesz odpowiadać w tematach
Nie możesz zmieniać swoich postów
Nie możesz usuwać swoich postów
Nie możesz głosować w ankietach
Nie możesz załączać plików na tym forum
Możesz ściągać załączniki na tym forum
Dodaj temat do Ulubionych
Wersja do druku

Skocz do:  

Powered by phpBB modified by Przemo © 2003 phpBB Group
Theme xandgreen created by spleen& Programosy modified v0.3 by warna
Opieka techniczna www.marketingNET.pl

Archiwum

Strona używa plików cookies.

Kliknij tutaj, żeby dowiedzieć się jaki jest cel używania cookies oraz jak zmienić ustawienia cookie w przeglądarce.
Korzystając ze strony użytkownik wyraża zgodę na używanie plików cookies, zgodnie z bieżącymi ustawieniami przeglądarki.
Sprawdź, w jaki sposób przetwarzamy dane osobowe