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: 70532 Skopiuj do schowka zwiększenie zakresu danych do analizy
Autor Wiadomość
Kiluk 
Stały bywalec Excelforum



Pomógł: 6 razy
Posty: 448
Wysłany: 27-03-2021, 08:22   zwiększenie zakresu danych do analizy

Witajcie.
Proszę o pomoc w modyfikacji makra z załącznika
Dziś makro wyszukuje unikatowe wartości w kolumnie A i dopisuje jakie wartości występują w kolumnie B dla unikatu z kolumny A.
wszystko działa perfekcyjnie, ale...

potrzebuje zwiększyć ilość danych wejściowych do np 3 par kolumn (3 mln wierszy)

Pomyślałem aby dołożyć kolejne 2 pary kolumn (A i B)
Makro powinno szukać unikatów np. w kolumnie A + D + G i dopisywać wartości występujące w B + E + H
reszta zostaje bez zmian

ewentualnie dane wejściowe mogą być w 3 arkuszach w kolumnach A i B
a podsumowanie w arkuszu pierwszym.


Optymalnie aby nie było warunku całkowitego wypełnienia kol. A i B aby makro szukało w kolejnych parach kolumn
czyli makro powinno działać w całkiem wypełnionych kolumnach jak i np w kolumnach wypełnionych po 100 wierszy


Pozdr.
Kiluk

unikaty i wartości.xlsb
Pobierz Plik ściągnięto 9 raz(y) 187.09 KB

ID posta: 402812 Skopiuj do schowka
 
 
kuma 
Excel Expert


Wersja: Win Office 2010
Pomógł: 1453 razy
Posty: 4171
Wysłany: 27-03-2021, 09:21   

Daj odpowiedni skoroszyt do Twojego opisu, bo załącznik obrazuje tylko to co wykonuje załączone makro.
Nie wiadomo jak wyglądają dane w rozszerzonym zakresie. ;-)
_________________
Pozdrawiam.
ID posta: 402820 Skopiuj do schowka
 
 
Kiluk 
Stały bywalec Excelforum



Pomógł: 6 razy
Posty: 448
Wysłany: 27-03-2021, 12:16   

wrzuciłem 2 wersje które wydają mi się najwygodniejsze.
1. wszystko w jednym arkuszu- dodane 2 pary kolumn
2. dodane 2 arkusze z dodatkowymi parami kolumn z danymi.

Tak na prawde oba rozwiązania mnie usatysfakcjonują.
Kwestia bardziej które jest łatwiejsze do wykonania

unikaty i wartości ver1.xlsb
3 poary kolumn w jednym arkuszu
Pobierz Plik ściągnięto 9 raz(y) 188.01 KB

unikaty i wartości ver2.xlsb
3 arkusze z tabelami
Pobierz Plik ściągnięto 7 raz(y) 192.5 KB

ID posta: 402828 Skopiuj do schowka
 
 
kuma 
Excel Expert


Wersja: Win Office 2010
Pomógł: 1453 razy
Posty: 4171
Wysłany: 29-03-2021, 10:01   

Załącznik jest rozwiązaniem dla wersji 2 - dane w osobnych arkuszach.
Kod dla wersji 1 (dane w jednym arkuszu) jest taki sam.
Należy tylko przypisać odpowiednie zakresy (wartości) do tablic a,b,c, na początku i zmienić miejsce do wpisania obrobionych danych do arkusza.
Myślę, że sobie poradzisz z wersją 1.

unikaty i wartości_v2_kuma.xlsb
Pobierz Plik ściągnięto 10 raz(y) 196.22 KB

_________________
Pozdrawiam.
ID posta: 402895 Skopiuj do schowka
 
 
Kiluk 
Stały bywalec Excelforum



Pomógł: 6 razy
Posty: 448
Wysłany: 29-03-2021, 18:42   

Dziękuję.
Działa tak jak trzeba.
ID posta: 402929 Skopiuj do schowka
 
 
Kiluk 
Stały bywalec Excelforum



Pomógł: 6 razy
Posty: 448
Wysłany: 29-03-2021, 21:16   

Nie mogę edytować już poprzedniego posta więc muszę napisać kolejny.

Potwierdzam informacje z poprzedniego posta ale...

propozycja kuma działa super tylko dla niewielkiej ilości danych.

Jeśli kuma spróbujesz użyć plik który pierwotnie wrzuciłem bez problemów radzi sobie z milionem wierszy (całą kolumną)
Twoja propozycja chodź radzi sobie z 3 arkuszami w których są dane wystarczy już do pierwszego arkusza wrzucić 50000 wierszy i pojawia się błąd "Over flow"

Tak jak pisałem na początku milion było mi mało stąd pomysł na zwiększenie ilości do 3 milionów poprzez zwielokrotnienie tabel wejściowych.

Da się cos z tym zrobić ?

Kiluk
ID posta: 402935 Skopiuj do schowka
 
 
kuma 
Excel Expert


Wersja: Win Office 2010
Pomógł: 1453 razy
Posty: 4171
Wysłany: 29-03-2021, 21:57   

Podejrzewam, że dotyczy to zmiennej 'i'.
Spróbuj zmienić deklarację tej zmiennej
z
Kod:
Dim i As Integer

na
Kod:
Dim i As Long
_________________
Pozdrawiam.
ID posta: 402938 Skopiuj do schowka
 
 
Kiluk 
Stały bywalec Excelforum



Pomógł: 6 razy
Posty: 448
Wysłany: 30-03-2021, 09:16   

Pomogło, natomiast wydajność nie jest zadowalająca niestety
tak jak na pliku który wrzuciłem przetworzenie miliona wierszy trwa ze 20 minut.
Tak na Twoim pliku 200 tys. wierszy leci już prawie godzinę i wciąż nie skończyło się.
ID posta: 402947 Skopiuj do schowka
 
 
DwaNiedźwiedzie 
Excel Expert



Wersja: Win Office 2016
Pomógł: 284 razy
Posty: 705
Wysłany: 30-03-2021, 10:12   

A nie możesz do tego użyć Power Query, jak na XXI wiek przystało? :)

Nie rozumiem tylko, skąd ostatnia wartość w tabelach wynikowych (155 adfdf) i dlaczego w ostatniej kolumnie wyniki masz przemnożone przez trzy, ale to już kosmetyka.

unikaty i wartości_PQ_DN.xlsb
Pobierz Plik ściągnięto 5 raz(y) 204.21 KB

ID posta: 402950 Skopiuj do schowka
 
 
kuma 
Excel Expert


Wersja: Win Office 2010
Pomógł: 1453 razy
Posty: 4171
Wysłany: 30-03-2021, 11:35   

To nie kosmetyka :-)
Gdyby Twoje PQ obrobiło dane ze wszystkich arkuszy to otrzymałbyś taki sam wynik ;-)
_________________
Pozdrawiam.
ID posta: 402954 Skopiuj do schowka
 
 
DwaNiedźwiedzie 
Excel Expert



Wersja: Win Office 2016
Pomógł: 284 razy
Posty: 705
Wysłany: 30-03-2021, 13:58   

A co za problem zaczytać dane z wielu arkuszy? Kosmetyka :) W parametrze "plik" w PQ trzeba oczywiście podać własną ścieżkę do pliku - źródło to musi być osobnym plikiem, tą metodą nie zaczyta się danych z bieżącego skoroszytu.

unikaty i wartości_PQ_DN v2.xlsb
Pobierz Plik ściągnięto 9 raz(y) 206.4 KB

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



Pomógł: 6 razy
Posty: 448
Wysłany: 30-03-2021, 15:30   

zerknąłem na plik unikaty i wartości_PQ_DN.xlsb od DwaNiedźwiedzie.
z ciekawości wpisałem daty do kolumny B
tabela wynikowa nie pokazała tych dat

Musze też uczciwie przyznać że PQ to czarna magia dlatego rozwiązanie makrem w pełnie mnie satysfakcjonuje gdyby tylko wydajność była lepsza.

czekanie godzinami nie jest chyba optymalnym rozwiązaniem zwłaszcza że makro w pierwotnym pliku działa bardzo szybko dla dużej ilości danych wejściowych
ID posta: 402971 Skopiuj do schowka
 
 
DwaNiedźwiedzie 
Excel Expert



Wersja: Win Office 2016
Pomógł: 284 razy
Posty: 705
Wysłany: 31-03-2021, 11:16   

Kiluk napisał/a:
zerknąłem na plik unikaty i wartości_PQ_DN.xlsb od DwaNiedźwiedzie.
z ciekawości wpisałem daty do kolumny B
tabela wynikowa nie pokazała tych dat

Sprawdź plik v2 z mojego późniejszego wpisu, tam z datami nie będzie problemu. Po otwarciu kliknij Dane -> Zapytania i połączenia, po prawej stronie arkusza powinno pokazać się okno z kwerendami. Kliknij dwukrotnie na ostatnią na dole ("plik (C:\excelforum\...") i po otwarciu edytora w polu Wartość bieżąca podaj pełną ścieżkę do swojego pliku źródłowego. Kliknij Zapisz i zamknij i odśwież tabele (prawa mysz i Odśwież lub po zaznaczeniu dowolnej komórki w tabeli alt+F5).
Kiluk napisał/a:
Musze też uczciwie przyznać że PQ to czarna magia dlatego rozwiązanie makrem w pełnie mnie satysfakcjonuje gdyby tylko wydajność była lepsza.

PQ do transformacji danych jest lepszym i bardziej przyjaznym narzędziem niż VBA, sporo rzeczy możesz tam wyklikać z menu. Twój przykład wymagał trochę pogrzebania w formułach, ale według mnie jest to i tak mniej skomplikowane niż obróbka makrem. Oczywiście czasem trzeba użyć konkretnej metody z przyczyn wyższych, ale jeżeli można wybierać, to moim zdaniem opcja jest tylko jedna :)
ID posta: 403008 Skopiuj do schowka
 
 
Kiluk 
Stały bywalec Excelforum



Pomógł: 6 razy
Posty: 448
Wysłany: 31-03-2021, 15:31   

już coraz bliżej finału. spróbowałem daty w formacie rr.mm.dddd a po zadziałaniu PQ widać
31.01.2020 00:00:00,29.02.2020 00:00:00,31.03.2020 00:00:00

czyli "nadmiarowe" godziny

i dalej.
dałem dane wejściowe do nowego pliku xlsb

po odświeżeniu PQ widzę komunikat
]Data.Format.error] zewnętrzna tabela nie ma oczekiwanego formatu
ID posta: 403028 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