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
Przesunięty przez: Artik
24-02-2019, 01:04
Unikaty w wielu kolumnach
Autor Wiadomość
Kiluk 
Stały bywalec Excelforum



Pomógł: 6 razy
Posty: 420
Wysłany: 23-02-2019, 21:08   Unikaty w wielu kolumnach

Witajcie.
Szukam rozwiązania szybszego niż tabela przestawna którą dzis wykorzystuję.
Czy takie istnieje, czy jednak tabela przestawna jest najszybsza ?

Problem jest dość prosty ale ale jednocześnie czasochłonny z uwagi na ilość danych.
potrzebuje wyznaczyć unikaty i zsumować wartości dla około miliona wierszy.

w załączniku dodałem przykład danych.
potrzebuje zsumować wartości w poszczególnych kolumnach G do AC (i kolejnych w następnych miesiącach)
Sumowanie powinno wystąpić dla identycznych wartości w kolumnach A-F

chciałbym otrzymać nowy arkusz w którym są tylko unikatowe wystąpienia A-F i zsumowane poszczególne miesiące (kolumny od G).

Kiluk

012017-112018.xlsx
Pobierz Plik ściągnięto 31 raz(y) 450.17 KB

ID posta: 363166 Skopiuj do schowka
 
 
ąćęłńóś
Excel Expert


Pomógł: 198 razy
Posty: 938
Wysłany: 25-02-2019, 02:57   

... hmm ... :roll: ... a gdybyś tak ...:

1) dodał nowy arkusz w tym swoim zeszycie
2) ze źródeł danych wybrał Ms Query
3) pliki programu excel
4) wskazał ten swój plik jako bazę
5) wskazał 'Arkusz6' jako źródło danych
6) zakończył wskazaniem otwarcia danych w Ms Query
7) odkliknął automatyczne wykonywanie zapytań (bo k...cy można dostać od ciągłego czekania po każdym ruchu)
8) z właściwości zapytania wybrał 'Tylko unikaty'
9) następnie przeleciał po nagłówkach kolumn z wartościami numerycznymi i dla każdej zaznaczył 'Suma'
10) następnie depnął 'krzykliwego' (!)
11) poczekał
12) następnie zapisał zapytanie na dysku
13) na wszelki wypadek skopiował zapytanie sql do notatnika
14) zwrócił dane (najlepiej tabela) do excela, do arkusza dodanego na początku
15) wybałuszył gałki oczne na te 'krzaki' co się pojawią
16) usunął dodany arkusz
17) usunął zbędne połączenia (niekoniecznie związane z tym zeszytem)
18) usunął zbędne nazwy zdefiniowane (jeśli istnieją)
19) zmienił nazwy nagłówków kolumn numerycznych (bo te "datowe" to o kant ...)
20) poprawił dane w kolumnach dla listopada i grudnia 2017, wiersz 263
21) i zaczął wszystko od początku, od pkt 1) do 14)

, to nie byłoby to wcale takie wolno-działające znowuż ... :-> ... bo tak między Buddą a Światowidem, to zabawa z makrami w unikaty sumacyjne dla miliona wierszy, gdy w sql mamy 'Distinct' i 'Distinctrow', to chyba lekka przesada ... tym bardziej, że już coś z tym plikiem w accessie, wydaje się, robiłeś ... (?) ... czy to jednak ten Ms Query sobie "nie poradził" ... :-> ... (?)
ID posta: 363197 Skopiuj do schowka
 
 
Kiluk 
Stały bywalec Excelforum



Pomógł: 6 razy
Posty: 420
Wysłany: 25-02-2019, 18:47   

robiłem to pierwszy raz ale wydaje mi sie, że to trwa znacznie dłużej niż budowa tabeli przestawnej.
Może to kwestia mojej nieudolności - robię to zgodnie z instrukcja ale cos mi i tak nie wychodzi jak nalezy
Kiluk
ID posta: 363239 Skopiuj do schowka
 
 
ąćęłńóś
Excel Expert


Pomógł: 198 razy
Posty: 938
Wysłany: 26-02-2019, 01:18   

Kiluk napisał/a:
wydaje mi sie, że to trwa znacznie dłużej niż budowa tabeli przestawnej

Pewnie w tych nowych 'xls' "nabogacili" tak, że nie wiadomo czego używać ...

To się robi "jednorazowo" dla narastających wierszy, dla narastających kolumn trzeba już korygować zapytanie, ale to "prościzna". Dla 65,5 tys. wierszy zapytanie wykonywało się u mnie ok. 10 s.
Swoją drogą, to ile planujesz tych kolumn ... dziesiąt ... set ... tysiąc ... (?) ... w Ms tego chyba nie przewidzieli ... :-> ...
Może trzeba ci, żebyś machnął jakieś makro z QueryTables (?) .. najprostsza opcja, żeby się nie zamordować na tablicach, słownikach, czy arraylist ...
U mnie, sql pod tę twoją tabelkę, tak wygląda:

Zap_Arkusz6.1.txt
Pobierz Plik ściągnięto 19 raz(y) 1.16 KB

ID posta: 363249 Skopiuj do schowka
 
 
Kiluk 
Stały bywalec Excelforum



Pomógł: 6 razy
Posty: 420
Wysłany: 27-02-2019, 21:12   

kolumn chyba max 50
trzeba będzie w kolejnym miesiącu edytowac zapytanie dopisując kolejna kolumne
ID posta: 363375 Skopiuj do schowka
 
 
ąćęłńóś
Excel Expert


Pomógł: 198 razy
Posty: 938
Wysłany: 27-02-2019, 23:47   

Kiluk napisał/a:
chyba max 50

... :-> ... ma x 50 = będzie ... (?) ... :-> ... noo ... to niedużo ... :->

Zatem trzeba zrobić jakąś pętelkę, która przeleci po kolumnach i wbije je w formę sqla.
Schemat:
Cytat:
Select Distinct `a`, `b`, `c` " & vbCrLf & "FROM `xxx$` " & vbCrLf & "GROUP BY `a`;
względnie:
Cytat:
Select Distinct [a], [b], [c] " & vbCrLf & "FROM [xxx$] " & vbCrLf & "GROUP BY [a];

Na przykład dla 'For ... Next':
Kod:
    strSql = "SELECT DISTINCT "
    For k = 1 To 6
        strSql = strSql & "[" & Cells(1, k).Value & "], "
    Next
    For k = 7 To kol
        strSql = strSql & "Sum([" & Cells(1, k).Value & "]) AS 'Suma z " & Cells(1, k).Value & "', "
    Next
    strSql = Left(strSql, Len(strSql) - 2) & " " & vbCrLf
    strSql = strSql & "FROM [" & arkusz & "$] " & vbCrLf
    strSql = strSql & "GROUP BY "
    For k = 1 To 6
        strSql = strSql & "[" & Cells(1, k).Value & "], "
    Next
    strSql = RTrim(strSql)
    strSql = Left(strSql, Len(strSql) - 1) & ";"

A potem wsadzić to w QueryTables, dołożyć wspomagacze i ozdobniki, i powinno ruszyć, o ile nie zbuntuje się ze względu na ilość kolumn.
Tylko jeszcze pytanie, na jak skonfigurowanym połączeniu (?), u mnie pracuje na tym niezakomentowanym, ale ja mam xlsa starowinę, u siebie musisz sprawdzić, teoretycznie powinno na wszystkich poniższych:
Kod:
'Która zadziała (?):
    strCon = "ODBC;DSN=Pliki programu Excel;DBQ=" & dbPath & ";"
   
    'strCon = "ODBC;DSN=Pliki programu Excel;DBQ=" & dbPath & ";" & "DefaultDir=" & dbDefaultDir & ";DriverId=790;MaxBufferSize=2048;PageTimeout=5;"
   
    'strCon = "ODBC;DSN=Excel Files;DBQ=" & dbPath & ";" & "DefaultDir=" & dbDefaultDir & ";DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"
ID posta: 363379 Skopiuj do schowka
 
 
Bill Szysz 
Excel Expert


Wersja: Win Office 365
Pomógł: 794 razy
Posty: 3302
Wysłany: 28-02-2019, 01:18   

Kiluk, rozwiązanie w PQ powinno sobie z tym spokojnie poradzić.
Przykład w załączniku.... karmisz tylko zapytanie świeżymi danymi i je refresh'ujesz.
Rozwiązanie uznaje za stałe tylko pierwsze 6 kolumn - pozostałe kolumny mogą występować w dowolnej ilości.
Powinno dość szybko chodzić.

012017-112018_BS_PQ.xlsx
Pobierz Plik ściągnięto 26 raz(y) 864.46 KB

_________________
Szkolenia z Power Query!!!

Pozdrawiam, były szbill62 aktualnie Bill Szysz
ID posta: 363385 Skopiuj do schowka
 
 
Kiluk 
Stały bywalec Excelforum



Pomógł: 6 razy
Posty: 420
Wysłany: 28-02-2019, 21:19   

Genialne to rozwiązanie z PQ
dzieki
ID posta: 363453 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