Próbuję sobie jakoś radzić, np. znalazłem ciekawe info tutaj:
http://www.experts-exchange.com/Database/MS_Access/Q_28570259.html
http://www.experts-exchange.com/articles/2529/Median-Mode-Skewness-and-Kurtosis-in-MS-Access.html
Do doczytania i zapytania na forach (czy funkcje agregujące w VBA Accessa można użyć w ACE SQL w VBA Excela?)
czwartek, 14 maja 2015
środa, 13 maja 2015
Union, Resize ListObjectTable - Połączenie kilu zakresów komórek Tabeli (listobject Table)w VBA.
Np. w celu zmiany formatowania, patrz użycie Union:
'Thanks: https://quorum.akademiq.pl/discussion/comment/7324#Comment_7324
Dim oListObj As ListObject
Set oListObj = Worksheets("Oferta").ListObjects("Table3") 'change the sheet and table names accordingly
Dim k1 As Range
Dim k3 As Range
Dim k5 As Range
Dim kom As Range
Set k1 = oListObj.ListColumns("Cena netto").DataBodyRange
Set k3 = oListObj.ListColumns("Cena brutto").DataBodyRange
Set k5 = oListObj.("Tabela1").ListColumns("Wartość Netto").DataBodyRange
For Each kom In Union(k1, k3, k5)
Debug.Print kom.Address
Next
End Sub
Jeżeli przylegają do siebie
Dim cell As Range
For Each cell In oListObj.ListColumns("Cena netto").DataBodyRange.Resize(, 4)
piątek, 8 maja 2015
Autoincrement - pole automatycznie zliczające.
Jak to zrobić w Accesie/ACE SQL? Nie wiem... W Excelu odpowiedź znalazłem np. tutaj:
http://www.4gaccounts.com/5-ways-to-increment-the-cell-values-in-excel/
Funkcja
=IF (B3=””,””, COUNTA ($A$2:A2) +1-COUNTBLANK ($A$2:A2)) albo =IF(B3=””,””,COUNTA($B$3:B3)) albo
=IF(C4=”name”, COUNTA($B$2:B3)+1-COUNTBLANK($B$2:B3),””) etc...
Co jednak, gdy kolumnę zliczającą chcę mieć w Excelu w tabeli, której wszystkie rzędy podczas uaktualniania z zapytania ACE SQL są za każdym razem usuwane przez VBA?
Jeśli jest to tabela (ListObject Table), okazuje się, że wystarczy wkleić makrem formułę do pierwszej komórki kolumny L.p. a reszta komórek w kolumnie zostanie automatycznie wypełniona formułą:
Ostatecznie pojawiła się nowa potrzeba: Muszę policzyć tak, by licznik resetował się, zaczynał od nowa, gdy rozpocznie się nowa grupa:
=IF(G2<>"";COUNTIF($G$2:G2;G2);"")
Funkcja
=IF (B3=””,””, COUNTA ($A$2:A2) +1-COUNTBLANK ($A$2:A2)) albo =IF(B3=””,””,COUNTA($B$3:B3)) albo
=IF(C4=”name”, COUNTA($B$2:B3)+1-COUNTBLANK($B$2:B3),””) etc...
Co jednak, gdy kolumnę zliczającą chcę mieć w Excelu w tabeli, której wszystkie rzędy podczas uaktualniania z zapytania ACE SQL są za każdym razem usuwane przez VBA?
Jeśli jest to tabela (ListObject Table), okazuje się, że wystarczy wkleić makrem formułę do pierwszej komórki kolumny L.p. a reszta komórek w kolumnie zostanie automatycznie wypełniona formułą:
'Add autoincrement to first column
Worksheets("Oferta").Range("A7").Formula = "=IF(B7="""","""",COUNTA($B$7:B7))"
Może to nie jest najlepsze rozwiązanie, ale działa. Muszę poczytać więcej o:
- podzapytaniach, które pozwoliłyby na wykonanie autonumeracji w SQL,
- odnoszeniach się do elementów tabeli raczej (ListObject table) niż do adresów komórek.
Re1: do przeczytania: ACC2000: How to Rank Records Within a Query https://support.microsoft.com/pl-pl/kb/208946
do przestudiowania, przykład od Pana Jacka Zajadacza, autonumerowanie grup, tutaj płci:
SELECT p1.ID, p1.[Nazwisko], p1.[Imie], p1.plec, count(*) as NUMER FROM Pracownicy AS p1 INNER JOIN Pracownicy AS p2 ON p1.ID > p2.ID and p1.plec= p2.plec GROUP BY p1.ID, p1.[Nazwisko], p1.[Imie], p1.plec ORDER BY p1.plecRe2: np.
'thanks http://stackoverflow.com/questions/18811431/refer-to-excel-cell-in-table-by-header-name-and-row-number-vba
Worksheets("Oferta").ListObjects("Table3").DataBodyRange.Cells(1, ActiveSheet.ListObjects("Table3").ListColumns("Lp").Index).Formula = "=IF(B7="""","""",COUNTA($B$7:B7))"
'or more readable
Dim tb As ListObject
'assumes Table is the first one on the ActiveSheet
Set tb = Worksheets("Oferta").ListObjects("Table3") ' or less precisely ActiveSheet.ListObjects(1)
tb.DataBodyRange.Cells(1, tb.ListColumns("Lp").Index.Formula = "=IF(B7="""","""",COUNTA($B$7:B7))"
EDIT:Ostatecznie pojawiła się nowa potrzeba: Muszę policzyć tak, by licznik resetował się, zaczynał od nowa, gdy rozpocznie się nowa grupa:
=IF(G2<>"";COUNTIF($G$2:G2;G2);"")
SQL w Excelu Cz.4: Odpowiednik MS SQL ROLLUP'a ciąg dalszy.
W poprzednim poście sumowałem grupy produktów, w których zgadzały się pierwsze trzy litery kodu. Teraz inne zadanie - mam pogrupować grupy produktów na podstawie wyrażeń występujących w ich nazwach. Przy czym w osobnej grupie ma się znaleźć produkt xyz a w osobnej xyz pro. Nie mogą zsumować się w jednej grupie produkty xyz i xyz pro!. Poniżej kod i pliki przykładowe.
Przetestowane w MS Access query (kluczowe jest tutaj użycie
- w SELECT: max(TableB.Descr)
- połączenia ON TableA.Descr LIKE (TableB.Descr+"*")
Uwaga, podczas edycji otrzymałem komunikat, że string SQL był za długi, więc musiałem go podzielić na kilka stringów. Dzięki temu łańcuch SQL stał się jeszcze bardziej czytelny, pojawiło się miejsce na komentarze między łączonymi tabelami.
Pozostało formatowanie. Uwaga, odkryłem, że funkcja val nie jest dobra do konwersji liczb w postaci tekstowej do liczb w formacie currency, powoduje utratę miejsc po przecinku, w jej miejsce zastosowałem CDbl
Przetestowane w MS Access query (kluczowe jest tutaj użycie
- w SELECT: max(TableB.Descr)
- połączenia ON TableA.Descr LIKE (TableB.Descr+"*")
SELECT opis, wartosc FROM (SELECT DISTINCT "0" as col_order, opisszczegolowy as opis, "" as wartosc, majorID FROM (SELECT TableA.descr as opis, max(TableB.Descr) AS major, b, first(TableB.ID) as majorID, first(TableB.Details) as opisszczegolowy FROM TableB RIGHT JOIN TableA ON TableA.Descr LIKE (TableB.Descr+"*") GROUP BY TableA.descr,b ) UNION ALL SELECT "1" as col_order, opis, b as wartosc, majorID FROM (SELECT TableA.descr as opis, max(TableB.Descr) AS major, b, first(TableB.ID) as majorID FROM TableB RIGHT JOIN TableA ON TableA.Descr LIKE (TableB.Descr+"*") GROUP BY TableA.descr,b ) UNION ALL SELECT "2" as col_order, major+" Total" as opis, sum(b) as wartosc, first(majorID) FROM (SELECT TableA.descr as opis, max(TableB.Descr) AS major, b, first(TableB.ID) as majorID FROM TableB RIGHT JOIN TableA ON TableA.Descr LIKE (TableB.Descr+"*") GROUP BY TableA.descr,b ) GROUP BY major UNION ALL SELECT "3" as col_order, "Grand Total" as opis, sum(b) as wartosc, "999999" as majorID FROM TableA ) AS [Rollup_Result] ORDER BY majorID, col_order;A teraz trzeba rozwiązanie przekształcić na string SQL ACE OLEDB. W pierwszym kroku użyłem do tego celu narzędzia SQLinFormpro do formatowania łańcuchów SQL w VB (do 100 linii za darmo). Bardzo pomocne narzędzie, dzięki sensownym wcięciom pomogło mi odnaleźć się w gąszczu kodu:
Public Sub SporzadzOferte()
Dim Myconnection As Connection
Dim Myrecordset As Recordset
Dim Myworkbook As String
Dim strSQL As String
Dim i As Integer
'Deal with input data range so that it refers to a Table and not to a Sheet!
'It will allow writing notes and puting a header above the table
Dim rngTableA As String
rngTableA = Replace(Sheets("TableA").Range("TableA[#All]").Address, "$", "")
rngTableA = "[TableA$" & rngTableA & "]"
'Debug.Print rngTableA
'--------------------
Set Myconnection = New Connection
Set Myrecordset = New Recordset
'Identify the workbook you are referencing
Myworkbook = Application.ThisWorkbook.FullName
'Open connection to the workbook
Myconnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & Myworkbook & ";" & _
"Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"
strSQL = "" & _
"SELECT opis, wartosc " & _
"FROM (SELECT DISTINCT '0' AS col_order," & _
" [opisszczegolowy] AS [opis], " & _
" '' AS [wartosc]," & _
" majorID " & _
" FROM (SELECT " & rngTableA & ".descr AS opis , " & _
" MAX([TableB$].Descr) AS [major] , " & _
" b , " & _
" first([TableB$].ID) AS [majorID], " & _
" first([TableB$].[Details]) AS opisszczegolowy " & _
" FROM [TableB$] " & _
" RIGHT JOIN " & rngTableA & " " & _
" ON " & rngTableA & ".Descr LIKE ([TableB$].Descr+'%') " & _
" GROUP BY " & rngTableA & ".descr, b) " & _
" UNION ALL "
strSQL = strSQL + " " & _
" SELECT '1' AS col_order, " & _
" opis , " & _
" b AS wartosc , " & _
" majorID " & _
" FROM (SELECT " & rngTableA & ".descr AS opis , " & _
" MAX([TableB$].Descr) AS major, " & _
" b , " & _
" first([TableB$].ID) AS majorID " & _
" FROM [TableB$] " & _
" RIGHT JOIN " & rngTableA & " " & _
" ON " & rngTableA & ".Descr LIKE ([TableB$].Descr+'%') " & _
" GROUP BY " & rngTableA & ".descr, b) " & _
" UNION ALL "
strSQL = strSQL + " " & _
" SELECT '2' AS col_order, " & _
" major+' Total' AS opis , " & _
" SUM(b) AS wartosc , " & _
" first(majorID) " & _
" FROM (SELECT " & rngTableA & ".descr AS opis , " & _
" MAX([TableB$].Descr) AS major, " & _
" b , " & _
" first([TableB$].ID) AS majorID " & _
" FROM [TableB$] " & _
" RIGHT JOIN " & rngTableA & " " & _
" ON " & rngTableA & ".Descr LIKE ([TableB$].Descr+'%') " & _
" GROUP BY " & rngTableA & ".descr, b ) " & _
" GROUP BY major " & _
" UNION ALL "
strSQL = strSQL + " " & _
" SELECT '3' AS col_order, " & _
" 'Grand Total' AS opis , " & _
" SUM(b) AS wartosc , " & _
" '999' AS majorID " & _
" FROM " & rngTableA & " " & _
" ) " & _
"ORDER BY [majorID], [col_order]"
'Load the Query into a Recordset
Myrecordset.Open strSQL, Myconnection, adOpenStatic
'Place the Recordset onto SheetX
With Sheets("Oferta")
.Activate
.ListObjects("Table3").DataBodyRange.Rows.Delete
.Range("A5").CopyFromRecordset Myrecordset
'Add column heading names
For i = 1 To Myrecordset.Fields.Count
.Cells(4, i).Value = Myrecordset.Fields(i - 1).Name
Next i
End With
Set Myrecordset = Nothing
Myconnection.Close
Call BoldAndInsertEmptyRow
End Sub
Uwaga, podczas edycji otrzymałem komunikat, że string SQL był za długi, więc musiałem go podzielić na kilka stringów. Dzięki temu łańcuch SQL stał się jeszcze bardziej czytelny, pojawiło się miejsce na komentarze między łączonymi tabelami.
Pozostało formatowanie. Uwaga, odkryłem, że funkcja val nie jest dobra do konwersji liczb w postaci tekstowej do liczb w formacie currency, powoduje utratę miejsc po przecinku, w jej miejsce zastosowałem CDbl
Option Explicit
Sub BoldAndInsertEmptyRow()
Dim oListObj As ListObject
Dim RowCnt As Long
Dim r As Long
Set oListObj = Worksheets("Oferta").ListObjects("Table3") 'change the sheet and table names accordingly
Application.ScreenUpdating = False
' Solve the problem with values not showing up in proper (currency) format but in text format.
Dim Cell As Range
For Each Cell In oListObj.ListColumns("wartosc").DataBodyRange
With Cell
If .Value <> "" Then
.NumberFormat = "#,##0.00 $"
.Value = CDbl(.Value) 'not CCur(Val(.Value)) because you are loosing fractions.
End If
End With
Next Cell
'==========
'Format rows with totals: add empty row above, frame.
RowCnt = oListObj.ListRows.Count
For r = RowCnt To 1 Step -1
With oListObj.ListRows(r).Range
'If contains "TOTAL" - bold ent.row and add empty row below.
If InStr(UCase((.Cells(1, 1).Value)), "TOTAL") > 0 Then
.Font.Bold = True
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlDouble
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThick
End With
If r < RowCnt Then
oListObj.ListRows.Add Position:=r + 1, alwaysinsert:=True
End If
End If
' format rows with description
If .Cells(1, 2).Value = "" Then
.Cells(1, 1).Font.Bold = True
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
End If
End With
Next r
Application.ScreenUpdating = True
End Sub
Pliki przykładowe: test zapytania SQL w MSAccesie działająca formatka z zapytaniem ACE SQL w Excelu.
wtorek, 5 maja 2015
Bletchley Park's Lost Heroes - zapomnieni superbohaterowie epoki IT.
Tommy Flowers i Bill Tutte. Skrócili wojnę o kilka lat, wygrali bitwy, uratowali miliony ludzi, położyli podwaliny pod dzisiejszy postęp w dziedzinie IT. Po wojnie nałożono na nich obowiązek milczenia aż po grób, stworzono alternatywną narrację, przykryto innymi nazwiskami... . Inni zebrali laury, nagrody, na cześć innych nakręcono pochwalne bardzo nieprawdziwe filmy dotyczące sukcesów kryptograficznych Bletchley Park,budowy pierwszego elektronicznego komputera. Zostali skazani na zapomnienie, jak nasi matematycy, którzy złamali Enigmę. Historia przypomina mi losy bohaterów dywizjonu 303.
Linki:
Obejrzałem w TV: https://www.youtube.com/watch?v=bMu8UiHJHgs
Tommy Flowers http://en.m.wikipedia.org/wiki/Tommy_Flowers
Bill Tutte http://en.m.wikipedia.org/wiki/W._T._Tutte
Inni liczni bohaterowie (również dźwięk generowany przez teleprinter/maszynę szyfrującą (RTTY): https://www.youtube.com/watch?v=b4WBINgRMTY
Co dziś usłyszymy w muzeum: https://www.youtube.com/watch?v=GBsfWSQVtYA
Ciekawy blog nt. zaawansowanych wykresów w Excelu..
1. Heat maps
Wykorzystanie formatowania warunkowego do tworzenia "heat maps". Może być przydatne do identyfikowania klastrów. http://excelgraphs.blogspot.com/2013/03/heat-map-plot-in-excel-using.html
A oto moja wersja (na G_Drive):
- Za pomocą SUMPRODUCT zliczam ilość wystąpień dla poszczególnych zakresów cen i rabatów.
- Za pomocą prostego makra kopiuję z osi wykresu zakresy cen i rabatów dla klikniętej komórki wykresu i wstawiam do formuły wyszukującej rekordy, których dotyczy kliknięta komórka.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim CheckRange As Range
Dim r As Integer
Dim c As Integer
'Set the plot's range
Set CheckRange = Range(Cells(5, 6), Cells(16, 13))
'If I click in the plot's range
If Not Intersect(Target, CheckRange) Is Nothing Then
'Detect row and column where I clicked
With ActiveCell
r = .Row
c = .Column
End With
'Copy from respective axis to cells with search conditions.
'min
Cells(21, 8).Value = Cells(r + 1, 5).Value
'max
Cells(22, 8).Value = Cells(r, 5).Value
'disc min
Cells(23, 8).Value = Cells(17, c - 1).Value
'disc max
Cells(24, 8).Value = Cells(17, c).Value
End If
End Sub
- Jako uzupełnienie polecam stronę http://fiveminutelessons.com/learn-microsoft-excel/use-index-lookup-multiple-values-list opisującą formułę, która pozwala wyszukać kilka recordów spełniających określone kryteria.
2. "rysowanie drzew" :-) z wykorzystaniem pakietu stat. R i dodatku RExcel http://excelgraphs.blogspot.com/2013/04/plotting-dendogram-of-cluster-analysis.html
Scatterplot matrix: http://excelgraphs.blogspot.com/2013/04/scatter-plot-matrix-in-excel-using.html
Opis instalacji RExcel dla opornych: http://excelgraphs.blogspot.com/2013/04/extend-your-excel-graphics-capabilities.html
poniedziałek, 4 maja 2015
Budowanie łańcucha SQL w VBA, ciekawe rozwiązania.
Niezwykle użyteczne narzędzie do formatowania łańcucha SQL do użycia w kodzie VBA. http://www.sqlinform.com/. Poprawia czytelność kodu i wyświetla komunikaty o błędach w poszczególnych liniach. Do 100 linii za darmo.
Polecam również ciekawy przykład budowania łańcucha zapytania SQL w VBA, w tym przypadku obsługującego formularze. http://www.baldyweb.com/BuildSQL2.htm
EDIT: Uwaga, w VBA zamiast "*" wildcard'u używa się "%". Straciłem dużo czasu zastanawiając się dlaczego pewne query działa w Accesie a nie w SQL. Na StackOverflow znalazłem odpowiedź.
Subskrybuj:
Posty (Atom)