Temat: Pomoc w napisaniu Makra lub formuł

Łukasz N.:
Dziękuję za tak ciepłe słowa. Nie ukrywam, że kod byłby dużo prostszy/czytelniejszy gdyby nie brak w ADO funkcji agregującej tekst rodem LISTAGG() z Oracle 11g czy GROUP_CONCAT() z MySQL.
Ale niestety, jak się nie ma co się lubi... to o konkatenacje wierszy należy samemu zadbać.

Group By ?

Pozdrawiam.

Temat: Pomoc w napisaniu Makra lub formuł

Nad VBA głowię się od niedawna, ale też udało mi się napisać kod ("na około"), chociaż Panowie byliście szybszy:)
Skoro napisałem to wklejam, może komuś się kiedyś ta prosta metodologia przyda.
Raport tworzy się w 2 arkuszu.

pozdrawiam,

Sub Kopiowanie_warunkowe()

Application.ScreenUpdating = False

Sheets(1).Range("a1").CurrentRegion.Columns(7).Offset(1, 0).Copy _
Sheets(2).Range("p1")
Sheets(2).Range("p1").CurrentRegion.RemoveDuplicates Columns:=1, Header:=xlNo
Sheets(2).Range("p1").CurrentRegion.Copy
Range("m1").PasteSpecial Transpose:=True
Columns(16).ClearContents


Sheets(1).Range("a1:l1").Copy Sheets(2).Range("a1")
Sheets(1).Columns("B:B").Copy Sheets(2).Range("B1")
Sheets(2).Range("a1").CurrentRegion.RemoveDuplicates Columns:=Array(2), _
Header:=xlYes



Dim i As Integer
For i = 2 To Range("a1").CurrentRegion.Rows.Count

Cells(i, 8).Value = WorksheetFunction.CountIf(Sheets(1).Columns(2), Cells(i, 2).Value)

Cells(i, 9).Value = WorksheetFunction.CountIfs(Sheets(1).Columns(2), Cells(i, 2).Value, Sheets(1).Columns(7), Sheets(2).Cells(1, 13))

Cells(i, 10).Value = WorksheetFunction.CountIfs(Sheets(1).Columns(2), Cells(i, 2).Value, Sheets(1).Columns(7), Sheets(2).Cells(1, 14))

Cells(i, 11).Value = WorksheetFunction.CountIfs(Sheets(1).Columns(2), Cells(i, 2).Value, Sheets(1).Columns(7), Sheets(2).Cells(1, 15))

Cells(i, 12).Value = WorksheetFunction.Sum(Cells(i, 9) * Cells(1, 13), Cells(i, 10) * Cells(1, 14), Cells(i, 11) * Cells(1, 15))

Sheets(1).Range("a1").CurrentRegion.AutoFilter Field:=2, Criteria1:=Sheets(2).Cells(i, 2)

Dim j As Integer
For j = 13 To 15

Sheets(1).Range("a1").CurrentRegion.AutoFilter Field:=7, Criteria1:=Sheets(2).Cells(1, j).Text
Sheets(1).Range("a1").CurrentRegion.Columns(5).Offset(1, 0).Copy Sheets(2).Cells(2, j)

Dim k As Integer
k = 2

Dim w As String
w = ""

If Len(Cells(k, j)) > 0 Then
Do
If k = 2 Then
w = Cells(k, j).Value
Else
w = w & "," & Cells(k, j).Value
End If
k = k + 1
Loop Until Cells(k, j) = ""

w = "NazwaUsługi" & " " & Cells(1, j - 4) & " " & Cells(i, j - 4) & " " & "x" & " " & Cells(1, j) & " " & "(" & w & ")"
'odfiltrowanie ceny z 1 arkusza
Sheets(1).Range("a1").CurrentRegion.AutoFilter Field:=7

Cells(j, 16).Value = w

End If

Next j


Sheets(1).Range("a1").CurrentRegion.AutoFilter Field:=2
Sheets(1).Range("a1").CurrentRegion.AutoFilter Field:=7

Sheets(2).Cells(i, 4) = Cells(13, 16).Value + vbNewLine + Cells(14, 16).Value + vbNewLine + Cells(15, 16).Value

Sheets(2).Range("a1").CurrentRegion.Columns("m:o").Offset(1, 0).ClearContents
Sheets(2).Range("p13:p15").ClearContents

Next i

Sheets(2).Columns("M:P").Delete
Columns("D:D").ColumnWidth = 40
Cells.EntireRow.AutoFit
Columns.EntireColumn.AutoFit

Application.ScreenUpdating = True

End Sub

Temat: Pomoc w napisaniu Makra lub formuł

Sławomir B.:

Group By ?

Niestety jak zapewne Pan wie, GROUP BY służy tylko (albo i aż) do grupowania tj. wyznaczania grup krotek na podstawie wartości atrybutu grupującego. Nic poza tym.
Do wszelakich operacji na grupach służą funkcje grupowe, często zwane agregującymi.
Tworzą one podsumowania dla grup krotek lub też całej relacji, która jakby nie patrzeć również jest grupą.
Należą do nich m.in. COUNT(), AVG(), SUM(), MAX(), MIN().

Dlatego też samym GROUP BY nie rozwiąże się problemu Pani Ilony.
Dzieje się tak, ponieważ w wyniku końcowym należy połączyć ze sobą X krotek, w taki sposób aby utworzyły one listę elementów rozdzieloną przecinkiem. Innymi słowy, należy dokonać konkatenacji/złączenia wielu rekordów w jeden.
Taką funkcjonalność dają wspomniane przeze mnie dwie funkcje (GROUP_CONCAT() oraz LISTAGG()), które to określane są mianem tekstowych funkcji agregujących.

Niestety ADO dla połączeń z excelo-wymi plikami, czy też z bazą access-ową nie oferuje podobnego rozwiązania. Stąd też wzmianka o tym, że trzeba samemu znaleźć odpowiednie rozwiązanie z wykorzystaniem pętli po rekordsecie.

Proszę, zerknąć TUTAJ.
Główne zapytanie to zapytanie rozwiązujące problem Pani Ilony, dolne zaś jest po to aby pokazać strukturę wyniku podzapytania.

Żeby było ciekawiej, pozwoliłem sobie na wprowadzenie różnych nazw usług i różnych cen :-)

-------

Panie Jakubie,

Nie ważne czy rozwiązał Pan coś "na około" czy nie. Na samym początku liczy się to, że rozwiązanie działa.
Za jakiś czas może Pan wrócić do tego problemu i spróbować innej metody. Poniżej znajdzie Pan krótki opis jak ja bym się zabrał za to, gdyby nie było SQL'a.

Proponuję przy tego typu rozwiązaniach operować na tablicach. Tj. zakres interesujących nas danych wrzucić do tablicy np.

Dim Arr() As Variant

Arr = Range("A1:A10") 

A następnie iterować po elementach tejże tablicy. Jest to szybsze niż przeczesywanie zakresu.
Przy tym konkretnym problemie, gdyby nie istniał SQL sam zastosowałbym tą metodę, dodatkowo wykorzystując obiekt "Scripting.Dictionary". Do tego obiektu wstawiałbym dane w postaci:

NrKlienta => Struktura z danymi

Co ułatwiłoby wyłuskanie unikalnych numerów klienta.
Sama struktura to zbiór tablic i wartości liczbowych:

Tablica z produktami z Pakietu A

Tablica z produktami z Pakietu B

Tablica z produktami z Pakietu C

Kwota pakietu A

Kwota pakietu B

Kwota pakietu C

Wyciągania końcowych danych to pętla po obiekcie "Scripting.Dictionary" (ilość elementów to ilość unikalnych Nr Klienta) i wyciąganie poszczególnych danych ze struktury.

Pozdrawiam.

Temat: Pomoc w napisaniu Makra lub formuł

Panie Łukaszu,

bardzo dziękuję za miłe słowa dla vba-nowicjusza;) oraz za wskazówki do alternatywnego rozwiązania - na pewno spróbuję bardziej zaawansowanej metody.

pozdrawiam i życzę miłego dnia.

Temat: Pomoc w napisaniu Makra lub formuł

Łukasz N.:

Sławomir B.:

Group By ?

Niestety jak zapewne Pan wie, GROUP BY służy tylko (albo i aż) do grupowania tj. wyznaczania grup krotek na podstawie wartości atrybutu grupującego. Nic poza tym.

Nie doczytałem problemu :).