Temat: Użycie funkcjonalności zewnętrznego serwera do danych...

Czy można użyć serwera zewnętrznego (serwer MySQL w Internecie, w moim przypadku oddalony ok. 800 km) do przetworzenia moich własnych danych, które posiadam na dysku lokalnym, tu: w pliku Excela?

Oczywiście, zawsze, pojawia się pytanie "Po co?" Po co wysyłać dane gdzieś tam, żeby je zaraz potem ściągać z powrotem?

Ano po to, że tylko MySQL dysponuje funkcją GROUP_CONCAT, która potrafi łączyć (grupować w jednym polu tekstowym) teksty pola grupowanego według innego pola tabeli. A zrobienie tego innymi metodami to nie lada ekwilibrystyka.

Np.
Moje dane:

ID	F1	F2	F3	F4

c	c1	c2	c3	c4

b	b1	b2	b3	b4

b	b5	b6	b7	b8

a	a1	a2	a3	a4

a	a5	a6	a7	a8

a	a9	a10	a11	a12

Oczekiwany wynik:

ID	GROUP_CONCAT(WG1_Arkusz1_0.F5)

a	a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12

b	b1,b2,b3,b4,b5,b6,b7,b8

c	c1,c2,c3,c4

Pewnie, że można.
Wszystkie dane OTWARTEGO serwera MySQL, specjalnie przeznaczonego do takich prób i zabaw - na filmie. Wszystkie SQLki tamże.

Film:
http://afin.net/webcasts/Demo_UseExternalServerToProce...Wojciech Gardziński edytował(a) ten post dnia 05.03.12 o godzinie 09:45

Temat: Użycie funkcjonalności zewnętrznego serwera do danych...

Pomysł angażowania akurat do takiej sprawy zewnętrznego serwera MySQL wydaje mi się absurdalny. Można to dużo prościej zrobić:

a) po prostemu i Excel'owemu - kilka dodatkowych kolumn z odpowiednimi formułami (na upartego nawet 1 na wynik :) ) + standardowe filtrowanie i wynik gotowy
b) po skomplikowanemu - UDF w VBA i na moje oko powinniśmy spokojnie uzyskać GROUP_CONCAT w Excelu.

"tylko MySQL dysponuje funkcją GROUP_CONCAT" - to jest po prostu nieprawda. Gdzie indziej inaczej się nazywa, ale też jest. To chyba był skrót myślowy, że Excel tego nie ma "by default".

Natomiast pomysł ze stosowaniem zewnętrznych baz danych jak najbardziej ok.

Reasumując - wiem, że się czepiam bo chodziło o przykład. Pomysł do pewnych zastosowań ok, ale przykład nietrafiony.

Temat: Użycie funkcjonalności zewnętrznego serwera do danych...

Przemysław Kantyka:
Pomysł angażowania akurat do takiej sprawy zewnętrznego serwera MySQL wydaje mi się absurdalny. Można to dużo prościej zrobić:

a) po prostemu i Excel'owemu - kilka dodatkowych kolumn z odpowiednimi formułami (na upartego nawet 1 na wynik :) ) + standardowe filtrowanie i wynik gotowy

Bardzo proszę to ZROBIĆ!

b) po skomplikowanemu - UDF w VBA i na moje oko powinniśmy spokojnie uzyskać GROUP_CONCAT w Excelu.

Bardzo proszę to ZROBIĆ!

"tylko MySQL dysponuje funkcją GROUP_CONCAT" - to jest po prostu nieprawda. Gdzie indziej inaczej się nazywa, ale też jest.

Nieprawda. Wiem, że w jakimś tam DODATKU do Oracle'a też jest podobna funkcja WM_CONCAT (podaję za przykładem z grupy Oracle'owej), ale w MS JET SQL na pewno nie ma (Jeżeli się jednak mylę, to proszę, żeby ktoś mnie naprostował!)

To chyba był skrót myślowy, że Excel tego nie ma "by default".

Nie. To nie był skrót myślowy.

Natomiast pomysł ze stosowaniem zewnętrznych baz danych jak najbardziej ok.

Reasumując - wiem, że się czepiam bo chodziło o przykład. Pomysł do pewnych zastosowań ok, ale przykład nietrafiony.

Rozumiem, niech się Pan czepia, ile wlezie. Przyjmę wszelką krytykę, jeśli będzie sprawdzona i treściwa.

Na razie oczekuję od Pana wykonania przykładów a) i b) powyżej.
Jeżeli Pan tego nie zrobi, Pana krytyka (doceniam, że w dobrej wierze i że sympatyczna) będzie, niestety, z tych nie-treściwych. Jak Pan się zapewne domyśla, podpuszczam Pana, bo ja wiem, że nie da się tego zrobić ani formułami, ani funkcją VBA, bo ani formuły, ani funkcje VBA nie mają możliwości zmniejszenia ilości wierszy wyniku względem danych źródłowych.
Ale, może się mylę, czekam(y) więc.Wojciech Gardziński edytował(a) ten post dnia 05.03.12 o godzinie 19:01

Temat: Użycie funkcjonalności zewnętrznego serwera do danych...

Bycie pewnym siebie to dobra cecha, o ile faktycznie ma się rację i nie przekracza się granicy:

a) Zakładając, że dane mamy wstawione do zakresu A1:E7 sortujemy je po pierwszej kolumnie. Poniżej podaję wartości formuł do wstawienia:
w komórce A1: =A2
w komórce F2: =JEŻELI(A2=A1;F1&JEŻELI(F1<>"";F1&",";"")&B2&","&C2&","&D2&","&E2;B2&","&C2&","&D2&","&E2)

Formułę przeciągamy do końca zestawu danych - komórki F7

Formuła ta wstawia do komórki wiersz poprzedni i bieżący pod warunkiem, że cały czas jesteśmy w tej samej "grupie" ID.

w komórce G2: JEŻELI(A2<>A3;F2;"")

Formułę przeciągamy do końca zestawu danych - komórki G7

Formuła ta wstawia do komórki wartość "posumowanego" tekstu na granicy grup ID, oraz wartość pustą w środku "grupy".

"Jak Pan się zapewne domyśla, podpuszczam Pana, bo ja wiem, że nie da się tego zrobić ani formułami, ani funkcją VBA, bo ani formuły, ani funkcje VBA nie mają możliwości zmniejszenia ilości wierszy wyniku względem danych źródłowych." - no to proszę - definiujemy standardowy filtr danych i wywalamy te wiersze dla których wartość w kolumnie G jest niepusta.

Efekt do pobrania z http://blog.dataconsulting.pl/wp-content/uploads/2012/... Można sobie ładnie wynik np. skopiować.

b) "ani funkcje VBA nie mają możliwości zmniejszenia ilości wierszy wyniku względem danych źródłowych' - a funkcje otrzymujące w argumencie zakres danych (Range) + zwracające tablice?

"Nieprawda. Wiem, że w jakimś tam DODATKU do Oracle'a też jest podobna funkcja WM_CONCAT (podaję za przykładem z grupy Oracle'owej), ale w MS JET SQL na pewno nie ma (Jeżeli się jednak mylę, to proszę, żeby ktoś mnie naprostował!)" - z całym szacunkiem ale na MS JET SQL świat się nie kończy (osobiście nie specjalizuję się w tym dialekcie). Ale w innych bazach niż MySQL:
- Oracle: WM_CONCAT jest i owszem, ale jest też LISTAGG oraz kilka innych metod uzyskania tego wyniku - do poczytania na http://blog.dataconsulting.pl/2012/03/implementacja-li...
- MS SQL Server pozwala uzyskać ten wynik poprzez FOR XML: http://stackoverflow.com/questions/1621747/concat-fiel...
- PostgreSQL: Wprost dostępna funkcja string_aggPrzemysław Kantyka edytował(a) ten post dnia 05.03.12 o godzinie 20:21

Temat: Użycie funkcjonalności zewnętrznego serwera do danych...

Przemysław Kantyka:
Bycie pewnym siebie to dobra cecha, o ile faktycznie ma się rację i nie przekracza się granicy:

a) Zakładając, że dane mamy wstawione do zakresu A1:E7 sortujemy je po pierwszej kolumnie. Poniżej podaję wartości formuł do wstawienia:
w komórce A1: =A2
w komórce F2: =JEŻELI(A2=A1;F1&JEŻELI(F1<>"";F1&",";"")&B2&","&C2&","&D2&","&E2;B2&","&C2&","&D2&","&E2)

Formułę przeciągamy do końca zestawu danych - komórki F7

Formuła ta wstawia do komórki wiersz poprzedni i bieżący pod warunkiem, że cały czas jesteśmy w tej samej "grupie" ID.

w komórce G2: JEŻELI(A2<>A3;F2;"")

Formułę przeciągamy do końca zestawu danych - komórki G7

Formuła ta wstawia do komórki wartość "posumowanego" tekstu na granicy grup ID, oraz wartość pustą w środku "grupy".

"Jak Pan się zapewne domyśla, podpuszczam Pana, bo ja wiem, że nie da się tego zrobić ani formułami, ani funkcją VBA, bo ani formuły, ani funkcje VBA nie mają możliwości zmniejszenia ilości wierszy wyniku względem danych źródłowych." - no to proszę - definiujemy standardowy filtr danych i wywalamy te wiersze dla których wartość w kolumnie G jest niepusta.

Efekt do pobrania z http://blog.dataconsulting.pl/wp-content/uploads/2012/... Można sobie ładnie wynik np. skopiować.

Niech się Pan nie gniewa...
...ale, czy to żart?

Zrobił Pan rozwiązanie:

1. Bardzo SKOMPLIKOWANE
Wykopiowuję formułki z Pana arkusza tylko dla tylu wierszy, ile było w przykładzie. Dla 1.000 wierszy, proponuje Pan wpisać w arkusz 2.000 funkcji. Niezła efektywność i prostota ;).

=A2	F1	F2	F3	F4	c	c1	c2	c3	c4	=JEŻELI(A2=A1;F1&JEŻELI(F1<>"";F1&",";"")&B2&","&C2&","&D2&","&E2;B2&","&C2&","&D2&","&E2)	=JEŻELI(A2<>A3;F2;"")

b	b1	b2	b3	b4	=JEŻELI(A3=A2;F2&JEŻELI(F2<>"";F2&",";"")&B3&","&C3&","&D3&","&E3;B3&","&C3&","&D3&","&E3)	=JEŻELI(A3<>A4;F3;"")

b	b5	b6	b7	b8	=JEŻELI(A4=A3;F3&JEŻELI(F3<>"";F3&",";"")&B4&","&C4&","&D4&","&E4;B4&","&C4&","&D4&","&E4)	=JEŻELI(A4<>A5;F4;"")

a	a1	a2	a3	a4	=JEŻELI(A5=A4;F4&JEŻELI(F4<>"";F4&",";"")&B5&","&C5&","&D5&","&E5;B5&","&C5&","&D5&","&E5)	=JEŻELI(A5<>A6;F5;"")

a	a5	a6	a7	a8	=JEŻELI(A6=A5;F5&JEŻELI(F5<>"";F5&",";"")&B6&","&C6&","&D6&","&E6;B6&","&C6&","&D6&","&E6)	=JEŻELI(A6<>A7;F6;"")

a	a9	a10	a11	a12	=JEŻELI(A7=A6;F6&JEŻELI(F6<>"";F6&",";"")&B7&","&C7&","&D7&","&E7;B7&","&C7&","&D7&","&E7)	=JEŻELI(A7<>A8;F7;"")

Ale Pan pojechał... Funkcja JEŻELI() rządzi. I będzie się baaaardzo długo liczyć.

2. OGRANICZONE ilością wierszy Excela

3. WYMAGAJĄCE każdorazowego użycia autofiltra

b) "ani funkcje VBA nie mają możliwości zmniejszenia ilości wierszy wyniku względem danych źródłowych' - a funkcje otrzymujące w argumencie zakres danych (Range) + zwracające tablice?

"Nieprawda. Wiem, że w jakimś tam DODATKU do Oracle'a też jest podobna funkcja WM_CONCAT (podaję za przykładem z grupy Oracle'owej), ale w MS JET SQL na pewno nie ma (Jeżeli się jednak mylę, to proszę, żeby ktoś mnie naprostował!)" - z całym szacunkiem ale na MS JET SQL świat się nie kończy (osobiście nie specjalizuję się w tym dialekcie). Ale w innych bazach niż MySQL:
- Oracle: WM_CONCAT jest i owszem, ale jest też LISTAGG oraz kilka innych metod uzyskania tego wyniku - do poczytania na http://blog.dataconsulting.pl/2012/03/implementacja-li...

OK. Przecież napisałem, że Oracle ma. Też przyjmuję na wiarę. Ale TO PAN napisał, że nie jest wspierana, takie tam...

- MS SQL Server pozwala uzyskać ten wynik poprzez FOR XML: http://stackoverflow.com/questions/1621747/concat-fiel...

SELECT

   SUBSTRING(buzz, 2, 2000000000)

FROM

    (

    SELECT 

        firstname

    FROM 

        employee

    WHERE

        State = 'CA'

    FOR XML PATH (',')

    ) fizz(buzz)

Tego nie rozumiem, ale sprawdzę. Nie znałem, więc, jeśli zadziała, punkcik dla Pana Szanownego.

- PostgreSQL: Wprost dostępna funkcja string_agg

Tu - być może. Nie znam na tyle PostgreSQL, przyjmuję na wiarę i kiedyś sprawdzę.

Reasumując
1. Rozwiązanie Excelowe - zrobione dla 7 wierszy mojego przykładu, ale, że tak powiem bardzo nieelegancko. Ponieważ jednak wymaga każdorazowej ingerencji użytkownika w proces - 0 punktów
2. Rozwiązania VBA się nie (jeszcze) doczekaliśmy
3. ad mojej uwagi, że tylko MySQL - ok., sprawdzę, na razie punkt dla Pana, bo nie znam w tej chwili odpowiedzi. Na pewno jednak nie zadziała ani w Excelu, ani w Accessie.
Ale, nawet gdyby zadziałało, przyjmijmy nawet, że tak jest!... to w niczym to nie umniejsza sensu mojego przykładu. Mając dane w Excelku, WARTO, wysłać je do innej bazy, by zrobić takie agregacje.

No to 1:1. :)
Czekam na rozwiązanie viżualbejzikowe.

P.S.
Ad rozwiązania excelowego:
Przy nieposortowanej tablicy, oczom mym ukazała się taka kaszanka

c	F1	F2	F3	F4	c	c1	c2	c3	c4	c1,c2,c3,c4	c1,c2,c3,c4

a	a5	a6	a7	a8	a5,a6,a7,a8	a5,a6,a7,a8

b	b1	b2	b3	b4	b1,b2,b3,b4	b1,b2,b3,b4

b	b5	b6	b7	b8	b1,b2,b3,b4b1,b2,b3,b4,b5,b6,b7,b8	b1,b2,b3,b4b1,b2,b3,b4,b5,b6,b7,b8

a	a1	a2	a3	a4	a1,a2,a3,a4	a1,a2,a3,a4

a	a9	a10	a11	a12	a1,a2,a3,a4a1,a2,a3,a4,a9,a10,a11,a12	a1,a2,a3,a4a1,a2,a3,a4,a9,a10,a11,a12

Wię, jednym słowem, każe Pan użytkownikowi najpierw posortować, potem wpisać funkcje, potem użyć autofiltra. Automatyzacja, że hej!Wojciech Gardziński edytował(a) ten post dnia 05.03.12 o godzinie 21:06

Temat: Użycie funkcjonalności zewnętrznego serwera do danych...

Wojciech Gardziński:

Reasumując
1. Rozwiązanie Excelowe - zrobione dla 7 wierszy mojego przykładu, ale, że tak powiem bardzo nieelegancko. Ponieważ jednak wymaga każdorazowej ingerencji użytkownika w proces - 0 punktów
Zrobił Pan rozwiązanie:

1. Bardzo SKOMPLIKOWANE

Ooo. Mamy progres. Już nie, że się nie da, tylko da się, ale nieelegancko... Zwracam uwagę, że:

Funkcje JEŻELI można ograniczyć. Napisałem to rozwlekle dla większej czytelności.

Do tego Pana rozwiązanie:

1. Również wymaga co najmniej kilku kliknięć i interakcji z użytkownikiem, wcześniejszego sformatowania danych w odpowiedni zakres komórek i przesyłania danych do bazy i z powrotem. Baza po drodze musi te dane przejrzeć dla każdego wiersza i na poziomie każdego z nich zrealizować algorytm, który ja wykonałem tylko i wyłącznie z użyciem funkcjonalności dostępnej w Excel'u (tu uwaga - ja wykonałem grupowanie z użyciem sortowania, a np. Oracle zrobi podczas grupowania tylko haszowanie). Nie twierdzę, że Excel (i funkcja JEŻELI) będzie szybszy (chociaż MySQL bez indeksów też się będzie musiał namęczyć, dodatkowo dochodzi tutaj przesyłanie danych protokołem sieciowym). Jak zależy nam na szybkości i chcemy koniecznie mieć bazę SQL to zamiast MySQL na odległym serwerze lepiej użyć np. jakiejś bazy typu SQLite + sterownik ODBC na naszym komputerze. SQLite ma też (w przeciwieństwie do tego co Pan twierdzi) funkcję group_concat - zachęcam do poczytania.

Pisze Pan, że rozwiązanie jest skomplikowane. Jeśli użycie sortowania, kilku formuł i filtra nazywa Pan skomplikowanym to chyba Pan żartuje.

Wię, jednym słowem, każe Pan użytkownikowi najpierw posortować, potem wpisać funkcje, potem użyć autofiltra. Automatyzacja, że hej!

Oczywiście można całość "zwinąć" w jedno kliknięcie przy użyciu makra. I niech Pan nie udaje, że AFIN.NET to coś innego niż właśnie takie (jeszcze bardziej opasłe) makro.

2. OGRANICZONE ilością wierszy Excela

Czyli Pan przed kliknięciem magicznego przycisku wysyłającego dane do bazy i z powrotem ma opracowany sposób na obejście ograniczenia ilości wierszy przez Excela?

3. WYMAGAJĄCE każdorazowego użycia autofiltra

Natomiast Pana rozwiązanie każdorazowo wymaga użycia makra i wtyczki, którą trzeba wcześniej zainstalować. Do tego wymaga uruchomionej i skonfigurowanej bazy danych.

2. Rozwiązania VBA się nie (jeszcze) doczekaliśmy

Nie wypada chyba, żebym uczył "Trenera MS Excel" pisać funkcji w VBA. Proszę wskazać jaką przeszkodę techniczną widzi Pan w zaproponowanym przeze mnie rozwiązaniu to będziemy dyskutować merytorycznie.

3. ad mojej uwagi, że tylko MySQL - ok., sprawdzę, na razie punkt dla Pana, bo nie znam w tej chwili odpowiedzi. Na pewno jednak nie zadziała ani w Excelu, ani w Accessie.
Ale, nawet gdyby zadziałało, przyjmijmy nawet, że tak jest!... to w niczym to nie umniejsza sensu mojego przykładu. Mając dane w Excelku, WARTO, wysłać je do innej bazy, by zrobić takie agregacje.

Szanowny Panie. Proszę czytać uważnie i bez uprzedzeń (bo mam wrażenie, że poczuł się Pan dotknięty moją krytyką). Ja napisałem, że pomysł przetwarzania danych przy użyciu bazy jest dobry (polecam do tego w szczególności Oracle;)), ale napisanie, że nie da się zadania, które Pan przedstawił, zrealizować inaczej, wywołało uzasadnioną krytykę tego arbitralnego (i fałszywego) twierdzenia.

No to 1:1. :)
Czekam na rozwiązanie viżualbejzikowe.

Ja nie gram na punkty. Podałem metodę jaką ja bym to napisał. Range jako argument funkcji, która przejrzy ten zakres i zwróci tablicę po pogrupowaniu - proszę spróbować. Wierzę, że VBA jest Panu nieobcy, ale jak się nie uda, proszę napisać gdzie jest bariera nie do przeskoczenia. Coś poradzimy ;)

PS. Kaszanka o której Pan pisze, wynika z (celowego jak mniemam) pominięcia sortowania danych na początku. Sortowanie (jak Pan zapewne wie) to w Excel'u funkcjonalność wbudowana i dostępna pod jednym kliknięciem.Przemysław Kantyka edytował(a) ten post dnia 05.03.12 o godzinie 22:04

Temat: Użycie funkcjonalności zewnętrznego serwera do danych...

Przemysław Kantyka:

Ooo. Mamy progres. Już nie, że się nie da, tylko da się, ale nieelegancko... Zwracam uwagę, że:

Proszę Szanownego.
Napisał Pan, że to EXCEL ZROBI, a nie Excel+Użytkownik
Więc nie ma Pan racji. Bo, jeśli iść tym trybem, mógł Pan równie dobrze napisać:
"Siada U. przed kartką papieru i pisze. I niech ktoś stwierdzi, że wpisywanie do E. jest skomplikowane."

Do tego Pana rozwiązanie:

1. Również wymaga co najmniej kilku kliknięć i interakcji z użytkownikiem

Nieprawda. Zapuszczony SQL to robi i już. Powtarzalnie i superelegancko.

, wcześniejszego sformatowania danych w odpowiedni zakres komórek i przesyłania danych do bazy i z powrotem. Baza po drodze musi te dane przejrzeć dla każdego wiersza i na poziomie każdego z nich zrealizować algorytm

CO robi baza, to mnie nie interesuje. Robi i już.

, który ja wykonałem tylko i wyłącznie z użyciem funkcjonalności dostępnej w Excel'u (tu uwaga - ja wykonałem grupowanie z użyciem sortowania, a np. Oracle zrobi podczas grupowania tylko haszowanie). Nie twierdzę, że Excel (i funkcja JEŻELI) będzie szybszy (chociaż MySQL bez indeksów też się będzie musiał namęczyć, dodatkowo dochodzi tutaj przesyłanie danych protokołem sieciowym). Jak zależy nam na szybkości i chcemy koniecznie mieć bazę SQL to zamiast MySQL na odległym serwerze lepiej użyć np. jakiejś bazy typu SQLite + sterownik ODBC na naszym komputerze. SQLite ma też (w przeciwieństwie do tego co Pan twierdzi) funkcję group_concat - zachęcam do poczytania.

Super, że jakaś tam baza lokalna robi to samo. To woda na mój młyn - BAZA TO ROBI. MySQL też może być lokalny.

Pisze Pan, że rozwiązanie jest skomplikowane. Jeśli użycie sortowania, kilku formuł i filtra nazywa Pan skomplikowanym to chyba Pan żartuje.

Jak wyżej. Jakby wpisał ręcznie, byłoby to jeszcze MNIEJ SKOMPLIKOWANE.

Wię, jednym słowem, każe Pan użytkownikowi najpierw posortować, potem wpisać funkcje, potem użyć autofiltra. Automatyzacja, że hej!

Oczywiście można całość "zwinąć" w jedno kliknięcie przy użyciu makra. I niech Pan nie udaje, że AFIN.NET to coś innego niż właśnie takie (jeszcze bardziej opasłe) makro.

To nie jest wtedy ani Excel, ani UDF. Niech Pan poczyta, co to UDF. (Pan użył tego terminu - przypominam) - USER DEFINED FUNCTION - podkreślam: FUNCTION. Nie procedura - funkcja ma być! F-U-N-K-C-J-A!

2. OGRANICZONE ilością wierszy Excela

Czyli Pan przed kliknięciem magicznego przycisku wysyłającego dane do bazy i z powrotem ma opracowany sposób na obejście ograniczenia ilości wierszy przez Excela?

Oczywiście. Odczytuję dane, skąd chcę, może być milion wierszy, wysyłam do bazy i otrzymuję efekt, który też mogę wysłać do bazy, jakiej chcę. Dane w Excelu w ogóle w mojej metodzie NIE MUSZĄ SIĘ POJAWIĆ ani na chwilę.

3. WYMAGAJĄCE każdorazowego użycia autofiltra

Natomiast Pana rozwiązanie każdorazowo wymaga użycia makra i wtyczki, którą trzeba wcześniej zainstalować. Do tego wymaga uruchomionej i skonfigurowanej bazy danych.

Nieprawda. To, że pokazuję to w AFINIE, nie stanowi, że tylko tu to działa - przypominam, że mówimy O METODZIE, nie o narzędziu. Mogę to zrobić z dowolnego edytora SQL - z Accessa, MySQLa, Oracle'a,... dowolnego.

2. Rozwiązania VBA się nie (jeszcze) doczekaliśmy

Nie wypada chyba, żebym uczył "Trenera MS Excel" pisać funkcji w VBA. Proszę wskazać jaką przeszkodę techniczną widzi Pan w zaproponowanym przeze mnie rozwiązaniu to będziemy dyskutować merytorycznie.

Ależ, zbytek skromności!
Pan powiedział, że to proste! Nie śmiem Panu przeszkadzać. Czekam...

Szanowny Panie. Proszę czytać uważnie i bez uprzedzeń (bo mam wrażenie, że poczuł się Pan dotknięty moją krytyką).
Ja napisałem, że pomysł przetwarzania danych przy użyciu bazy jest dobry

Słowo "absurd" ma w języku polskim nieco inne znaczenie, niż potwierdzenie racji adwersarza. "Niech Pan poczyta" ;)

(polecam do tego w szczególności Oracle;))

To oczywiste. Ale, jakoś, wolę darmoszki.

, ale napisanie, że nie da się zadania, które Pan przedstawił, zrealizować inaczej, wywołało uzasadnioną krytykę tego arbitralnego (i fałszywego) twierdzenia.

Ups. No nie będę się tu z Panem Szanownym licytował na arbitralność... doceniając siłę przeciwnika w tych zawodach.

No to 1:1. :)
Czekam na rozwiązanie viżualbejzikowe.

Ja nie gram na punkty. Podałem metodę jaką ja bym to napisał. Range jako argument funkcji, która przejrzy ten zakres i zwróci tablicę po pogrupowaniu - proszę spróbować. Wierzę, że VBA jest Panu nieobcy, ale jak się nie uda, proszę napisać gdzie jest bariera nie do przeskoczenia. Coś poradzimy ;)

NO TO PORADŹCIE! PLIZ! UDF ma być - Pana słowa!

PS. Kaszanka o której Pan pisze, wynika z (celowego jak mniemam) pominięcia sortowania danych na początku. Sortowanie (jak Pan zapewne wie) to w Excel'u funkcjonalność wbudowana i dostępna pod jednym kliknięciem.

Kurczę, muszę się tego Excela trochę poduczyć...
No to ja się uczę sortować, a Pan pisze instrukcję dwustronicową dla użytkownika, jak używa się Pana fantastycznej, łatwej i fajowej funkcjonalności.

************************
No dobra, daliśmy sobie po razie, a teraz rozwiążmy problem:
Problem jest, jak to zrobić powtarzalnie, łatwo i jak najmniejszą ilością pracy - ja do tego celu użyłem MySQLa. Pan, słusznie, zauważył, że tu, zbyt arbitralnie, użyłem słowa "tylko" - inne bazy też mają tę funkcjonalność - OK (nie wiedziałem i co prawda dalej nie wiem, bo Pana przykładu na razie nie umiem uruchomić, ale walczę ;) ). Można to zrobić lokalnie, jak się tę bazę ma na lokalu, ja to zrobiłem via Internet.
Czyli - gra muzyka, jeśli chodzi o merituma.
Nie zgadzam się z Panem, że to "zbyt naokoło", że ŁATWIEJ to Excelkiem lub VBA. Funkcjami można wszystko, ale to prowadzi do kosmosu excelowego - i ja to wiem, i Pan to wie. Dla 7 wierszy i jeden raz - ok. Ale nie-powtarzalnie, nie-łatwo, nie-szybko.
************************

Czekam na Pana kodzik VBA. Ze utensknieniem.
(Doceniając elegancję dyskusji i dobrą wolę adwersarza.)Wojciech Gardziński edytował(a) ten post dnia 05.03.12 o godzinie 23:21

Temat: Użycie funkcjonalności zewnętrznego serwera do danych...

No nie, Panie Szanowny.

Spróbował mnie Pan zaczarować jakimś linkiem, ale ja, wie Pan, upierdliwy jestem i sprawdzam.

Ten przykład z z tym XML Path to ROBI XMLa, więc nijak ma się do naszego problemu.
Szacuneczek dla próby kiwnięcia mnie, ale prośba - prosze sprawdzać linki, zanim Pan mi je poda.
To brak szacunku dla mojego czasu.

A ja dalej nie wiem, jak to zrobić SQL Serverem.

Temat: Użycie funkcjonalności zewnętrznego serwera do danych...

Poniżej treść funkcji, która realizuje grupowanie w przekazanym zakresie po pierwszej kolumnie, jednocześnie realizując funkcjonalność WM_CONCAT na kolumnie, której numer przekazano w drugim argumencie. Nie wymaga sortowania wstępnego rekordów (wykorzystuje hashowanie elementów). Na pewno rozwiązanie to jest nietrywialne i funkcję da się zoptymalizować, jednak moim celem było w miarę przejrzyste pokazanie, że to się da zrobić:

Function InCollection(col As Collection, key As String) As Boolean

  Dim var As Variant

  Dim errNumber As Long



  InCollection = False

  Set var = Nothing



  Err.Clear

  On Error Resume Next

    var = col.Item(key)

    errNumber = CLng(Err.Number)

  On Error GoTo 0



  If errNumber = 5 Then

    InCollection = False

  Else

    InCollection = True

  End If



End Function



Function GroupBy(rRange As Range, iCol As Long) As Variant

        Dim RowNdx, ColNdx As Long

        Dim Result() As Variant

        Dim Data As Collection

        Dim Keys As Collection

        Dim tmp As Variant

        

        ReDim Result(rRange.Rows.Count, 1)

        Set Data = New Collection

        Set Keys = New Collection

        

        lCount = 0

        

        For RowNdx = 1 To rRange.Rows.Count

                If InCollection(Data, rRange(RowNdx, 1)) Then

                    tmp = Data(rRange(RowNdx, 1))

                    Data.Remove rRange(RowNdx, 1)

                    Data.Add tmp & "," & rRange(RowNdx, iCol), rRange(RowNdx, 1)

                Else

                    Keys.Add rRange(RowNdx, 1)

                    Data.Add rRange(RowNdx, iCol), rRange(RowNdx, 1)

                End If

        Next RowNdx

        

        RowNdx = 0

        

        For Each tmp In Keys

                Result(RowNdx, 0) = tmp

                Result(RowNdx, 1) = Data(tmp)

                RowNdx = RowNdx + 1

        Next tmp

        

        For RowNdx = RowNdx To Application.Caller.Rows.Count

            Result(RowNdx, 0) = ""

            Result(RowNdx, 1) = ""

        Next RowNdx

        

        GroupBy = Result

End Function

Wywołanie: jako funkcję zwracającą tablicę =GroupBy(zakres, nr_kolumny)

Skleja jedną kolumnę (dla uproszczenia) - aby skleić ich kilka trzeba jeszcze wykonać dodatkową konkatenację.

A więc da się funkcjonalność WM_COCNAT uzyskać zarówno z użyciem filtrów, sortowania i formuł jak i UDF w VBA. Jak Pan widzi funkcja zmniejsza ilości wierszy wyniku względem danych źródłowych.

Temat uważam za zamknięty

PozdrawiamPrzemysław Kantyka edytował(a) ten post dnia 06.03.12 o godzinie 13:47

Temat: Użycie funkcjonalności zewnętrznego serwera do danych...

:(





Zapomniał Pan dodać deklaracji zmiennej lCount - ale dodałem i to nie problem.

1. Nie działa. Proszę o poprawkę(*).

2. Ale jestem pod wrażeniem STYLU programowania! Szacuneczek.

P.S.
(*) ...lub informację, co ja zrobiłem źle w wywołaniu funkcji.

P.S.2.
Na jednej i dwóch kolumnach też NIE DZIAŁA
=groupby(A1:A7;1)
=groupby(A1:B7;1)
(i to niezależnie, czy wpisane tablicowo, czy normalnie)Wojciech Gardziński edytował(a) ten post dnia 06.03.12 o godzinie 14:02

Temat: Użycie funkcjonalności zewnętrznego serwera do danych...

Witam:

Jeśli chodzi o konieczność deklaracji zmiennej lCount - mamy różne ustawienie. U mnie opcja Explicit jest wyłączona. Pan zapewne ma Option Explicit.

Co do błędu wywołania - w poprzedniej wersji kodu znajdował się bug, który wymagał, aby zaznaczył Pan obszar wynikowy o ilości kolumn nie większej jak zakres danych źródłowych. Pod takim warunkiem funkcja działała poprawnie (proszę starą wersję wywołać zaznaczając obszar G1:H7. Dziękuję bardzo za wskazanie tego błędu.

Zamiat

ReDim Result(rRange.Rows.Count, 1)

powinno być

ReDim Result(Application.Caller.Rows.Count, 1)

Poprawna treść kodu:

Function InCollection(col As Collection, key As String) As Boolean

  Dim var As Variant

  Dim errNumber As Long



  InCollection = False

  Set var = Nothing



  Err.Clear

  On Error Resume Next

    var = col.Item(key)

    errNumber = CLng(Err.Number)

  On Error GoTo 0



  If errNumber = 5 Then

    InCollection = False

  Else

    InCollection = True

  End If



End Function



Function GroupBy(rRange As Range, iCol As Long) As Variant

        Dim RowNdx, ColNdx As Long

        Dim Result() As Variant

        Dim Data As Collection

        Dim Keys As Collection

        Dim tmp As Variant

        

        ReDim Result(Application.Caller.Rows.Count, 1)

        Set Data = New Collection

        Set Keys = New Collection

        

        lCount = 0

        

        For RowNdx = 1 To rRange.Rows.Count

                If InCollection(Data, rRange(RowNdx, 1)) Then

                    tmp = Data(rRange(RowNdx, 1))

                    Data.Remove rRange(RowNdx, 1)

                    Data.Add tmp & "," & rRange(RowNdx, iCol), rRange(RowNdx, 1)

                Else

                    Keys.Add rRange(RowNdx, 1)

                    Data.Add rRange(RowNdx, iCol), rRange(RowNdx, 1)

                End If

        Next RowNdx

        

        RowNdx = 0

        

        For Each tmp In Keys

                Result(RowNdx, 0) = tmp

                Result(RowNdx, 1) = Data(tmp)

                RowNdx = RowNdx + 1

        Next tmp

        

        For RowNdx = RowNdx To Application.Caller.Rows.Count

            Result(RowNdx, 0) = ""

            Result(RowNdx, 1) = ""

        Next RowNdx

        

        GroupBy = Result

End Function

Wywołanie funkcji wymaga przeznaczenia 2 kolumn i dowolnej liczby wierszy na wynik.

Wynik:




Optymalizację tej funkcji proponuję przedyskutować kiedyś przy piwie :)

Pozdrawiam

Temat: Użycie funkcjonalności zewnętrznego serwera do danych...

No dobra, poprawiłem sobie Pana funkcję i zadziałała

Efekt:

ID	F1

c	c1

b	b1,b5

a	a1,a5,a9

Po 1. Szacun. (za efekt)

Po 2. Szacun. (za styl)

Po 3. Szacun. (za walkę)

Teraz troszkę krytyczki:
Zupełnie niepraktyczna - ale to wiedziałem już, zanim Pan zaczął to robić, więc nie jest to wada Pana rozwiązania, tylko metody ogólnie. Zastosowanie funkcji tablicowych jest małe, nie mówiąc już o ich ergonomii.
Funkcja tablicowa ma bardzo ograniczone pole działania - w Pana rozwiązaniu TRZEBA koniecznie wpisać tyle funkcji, ile jest wierszy - i tylko tyle. Jedna więcej - wywala, jedna mniej - zawęża dane. Ale można to poprawić w kodzie - to szczegół.

Tak więc - zaś Szacun! - Pana rozwiązanie jest już nie skomplikowane, tylko horrendalnie skomplikowane. Ograniczone do jednej kolumny itp. Funkcji tablicowej też nie można też wpisać w zbyt wiele komórek - tablica w Excelu ma dość małe rozmiary - kiedyś tylko 8000 wartości, a teraz nawet nie wiem, bo przestałem tego używać jakieś 10 lat temu ze względu na powyższe wady.

Reasumując...
Chylę czoła przed umiejętnościami, ale częściowo pozostaję jednak przy swoim zdaniu - przypomnę:
"tylko MySQL dysponuje funkcją GROUP_CONCAT, która potrafi łączyć (grupować w jednym polu tekstowym) teksty pola grupowanego według innego pola tabeli. A zrobienie tego innymi metodami to nie lada ekwilibrystyka."

1. zdanie w powyższym tekście - "tylko MySQL" - ma Pan rację - można to zrobić innymi metodami. Kupa ograniczeń i nieergonomiczne, ale się da, OK.
2. zdanie w powyższym tekście - "to nie lada ekwilibrystyka" - AKTUALNE.

Pozdrawiam.

P.S. (po przeczytaniu Pana poprawki)
Już wiem :)
A piwko - łoj, chętnie!!! Rzadko, naprawdę rzadko, zdarza mi się tutaj trafić na profesjonalistę.

P.S.2.
Dla możliwości pełnego porównania metod, odwzorowałem 1:1 powyższe zadanie moją metodą - tym razem na lokalnym MySQLu:

[AFIN.NET.DB]
DROP TABLE IF EXISTS TabelaDanych

Driver={Microsoft Excel Driver (*.xls)};DBQ=C:\AFINTEMP\Dane.xls
SELECT * FROM [Arkusz1$A1:B7]
W moim rozwiązaniu taki zapis od razu robi "INTO TabelaDanych"

[AFIN.NET.DB]
SELECT ID, GROUP_CONCAT(F1) FROM TabelaDanych GROUP BY ID

gdzie:
[AFIN.NET.DB]=
DRIVER={MySQL ODBC 5.1 Driver}; SERVER=localhost; DATABASE=test; OPTION=0; PORT=3306; UID=...; PWD=...

Efekt identyczny.Wojciech Gardziński edytował(a) ten post dnia 06.03.12 o godzinie 16:43

Temat: Użycie funkcjonalności zewnętrznego serwera do danych...

No to, idąc za ciosem, może Szanowny Pan ma jakieś pomysły na rozwinięcie tematu z tytułu wątku:
Do czego JESZCZE można użyć zewnętrznej bazy danych w ramach wykorzystania jej unikalnej (lub mającej jakąś tam przewagę użytkową) funkcjonalności do przetworzenia naszych danych na coś lepszego, ze względu na jakąś tam naszą potrzebę użytkową?

Temat: Użycie funkcjonalności zewnętrznego serwera do danych...

Ciekawa "sprzeczka" , można się czegoś nauczyć...