Tổng hợp dữ liệu từ nhiều sheet (1 người xem)

maixuanvuong276 · 10/9/21

Nhờ các cao nhân giúp mình gộp dữ liệu từ sheet 1 sang sheet 2, các SP trùng nhau thì cộng dồn lại, kq mong muốn như sheet2 trong file. Chân thành cảmơnạ!

befaint · 10/9/21

maixuanvuong276 đã viết:
gộp dữ liệu từ sheet 1 sang sheet 2

Cách đơn giản nhất là mình chép nối hai bảng lại với nhau rồi dùng Pivot Table là được.
Nếu không gộp bảng thì dùng UnPivot Table.

snow25 · 10/9/21

maixuanvuong276 đã viết:
Nhờ các cao nhân giúp mình gộp dữ liệu từ sheet 1 sang sheet 2, các SP trùng nhau thì cộng dồn lại, kq mong muốn như sheet2 trong file. Chân thành cảmơnạ!

thử cái code này.

Mã:

Sub gop()
Dim i As Long, lr As Long, dic As Object, a As Long, arr, kq(1 To 1000, 1 To 6), b As Long, dk As String, j As Long
Set dic = CreateObject("scripting.dictionary")
With Sheets("sheet1")
    lr = .Range("C" & Rows.Count).End(xlUp).Row
    If lr > 2 Then
       arr = .Range("B3:G" & lr).Value
       For i = 1 To UBound(arr)
           dk = arr(i, 1)
           If Not dic.exists(dk) Then
              a = a + 1
              dic.Add dk, a
              For j = 1 To 6
                  kq(a, j) = arr(i, j)
              Next j
           Else
              b = dic.Item(dk)
              kq(b, 4) = kq(b, 4) + arr(i, 4)
              kq(b, 6) = kq(b, 6) + arr(i, 6)
           End If
    Next i
  End If
End With
With Sheets("sheet2")
    lr = .Range("C" & Rows.Count).End(xlUp).Row
    If lr > 2 Then
       arr = .Range("B3:G" & lr).Value
       For i = 1 To UBound(arr)
           dk = arr(i, 1)
           If Not dic.exists(dk) Then
              a = a + 1
              dic.Add dk, a
              For j = 1 To 6
                  kq(a, j) = arr(i, j)
              Next j
           Else
              b = dic.Item(dk)
              kq(b, 4) = kq(b, 4) + arr(i, 4)
              kq(b, 6) = kq(b, 6) + arr(i, 6)
           End If
    Next i
  End If
   lr = .Range("j" & Rows.Count).End(xlUp).Row
   If lr > 2 Then .Range("J3:O" & lr).ClearContents
   If a Then .Range("j3:o3").Resize(a).Value = kq
End With
Set dic = Nothing
End Sub

maixuanvuong276 · 10/9/21

snow25 đã viết:

thử cái code này.

Mã:

Sub gop()
Dim i As Long, lr As Long, dic As Object, a As Long, arr, kq(1 To 1000, 1 To 6), b As Long, dk As String, j As Long
Set dic = CreateObject("scripting.dictionary")
With Sheets("sheet1")
    lr = .Range("C" & Rows.Count).End(xlUp).Row
    If lr > 2 Then
       arr = .Range("B3:G" & lr).Value
       For i = 1 To UBound(arr)
           dk = arr(i, 1)
           If Not dic.exists(dk) Then
              a = a + 1
              dic.Add dk, a
              For j = 1 To 6
                  kq(a, j) = arr(i, j)
              Next j
           Else
              b = dic.Item(dk)
              kq(b, 4) = kq(b, 4) + arr(i, 4)
              kq(b, 6) = kq(b, 6) + arr(i, 6)
           End If
    Next i
  End If
End With
With Sheets("sheet2")
    lr = .Range("C" & Rows.Count).End(xlUp).Row
    If lr > 2 Then
       arr = .Range("B3:G" & lr).Value
       For i = 1 To UBound(arr)
           dk = arr(i, 1)
           If Not dic.exists(dk) Then
              a = a + 1
              dic.Add dk, a
              For j = 1 To 6
                  kq(a, j) = arr(i, j)
              Next j
           Else
              b = dic.Item(dk)
              kq(b, 4) = kq(b, 4) + arr(i, 4)
              kq(b, 6) = kq(b, 6) + arr(i, 6)
           End If
    Next i
  End If
   lr = .Range("j" & Rows.Count).End(xlUp).Row
   If lr > 2 Then .Range("J3:O" & lr).ClearContents
   If a Then .Range("j3:o3").Resize(a).Value = kq
End With
Set dic = Nothing
End Sub

Tks bạn nhiều, bạn ơi còn Thiếu hàng tính tổng cộng, bạn giúp mình luôn đc hk! cảm ơn bạn trc ạ!

Cá ngừ F1 · 10/9/21

Thử dùng Power Query nhé.
Kết quả ở sheet PowerQuery.
Click phải chuột vào dùng dữ liệu chọn refresh.

snow25 · 10/9/21

maixuanvuong276 đã viết:
Tks bạn nhiều, bạn ơi còn Thiếu hàng tính tổng cộng, bạn giúp mình luôn đc hk! cảm ơn bạn trc ạ!

Cái đấy viết công thức vào là được mà.

maixuanvuong276 · 10/9/21

snow25 đã viết:
Cái đấy viết công thức vào là được mà.

được rồi bạn! tks bạn nhiều nhé!

HUONGHCKT · 10/9/21

snow25 đã viết:

thử cái code này.

Mã:

Sub gop()
Dim i As Long, lr As Long, dic As Object, a As Long, arr, kq(1 To 1000, 1 To 6), b As Long, dk As String, j As Long
Set dic = CreateObject("scripting.dictionary")
With Sheets("sheet1")
    lr = .Range("C" & Rows.Count).End(xlUp).Row
    If lr > 2 Then
       arr = .Range("B3:G" & lr).Value
       For i = 1 To UBound(arr)
           dk = arr(i, 1)
           If Not dic.exists(dk) Then
              a = a + 1
              dic.Add dk, a
              For j = 1 To 6
                  kq(a, j) = arr(i, j)
              Next j
           Else
              b = dic.Item(dk)
              kq(b, 4) = kq(b, 4) + arr(i, 4)
              kq(b, 6) = kq(b, 6) + arr(i, 6)
           End If
    Next i
  End If
End With
With Sheets("sheet2")
    lr = .Range("C" & Rows.Count).End(xlUp).Row
    If lr > 2 Then
       arr = .Range("B3:G" & lr).Value
       For i = 1 To UBound(arr)
           dk = arr(i, 1)
           If Not dic.exists(dk) Then
              a = a + 1
              dic.Add dk, a
              For j = 1 To 6
                  kq(a, j) = arr(i, j)
              Next j
           Else
              b = dic.Item(dk)
              kq(b, 4) = kq(b, 4) + arr(i, 4)
              kq(b, 6) = kq(b, 6) + arr(i, 6)
           End If
    Next i
  End If
   lr = .Range("j" & Rows.Count).End(xlUp).Row
   If lr > 2 Then .Range("J3:O" & lr).ClearContents
   If a Then .Range("j3:o3").Resize(a).Value = kq
End With
Set dic = Nothing
End Sub

Sao bạn không làm một vòng lặp duyệt qua các Sh để lấy dữ liệu vào mảng.
Xin phép bạn tôi sửa lại code như sau:

Mã:

Sub gop()
Dim i As Long, lr As Long, dic As Object, a As Long, C&, D&, tong&
Dim arr, kq(1 To 1000, 1 To 6), b As Long, dk As String, j As Long
Dim Sh As Worksheet, rng As Range
Set dic = CreateObject("scripting.dictionary")
For Each Sh In Worksheets
    lr = Sh.Range("C" & Rows.Count).End(xlUp).Row
    Set rng = Sh.Range("A1:G" & lr)
        Set Item = rng.Find("Tên SP")
            If Not Item Is Nothing Then
                D = Item.Row + 1
                    arr = Sh.Range("B" & D, "G" & lr).Value
                        For i = 1 To UBound(arr)
                            dk = arr(i, 1)
                                If Not dic.exists(dk) Then
                                   a = a + 1
                                   dic.Add dk, a
                                   For j = 1 To 6
                                       kq(a, j) = arr(i, j)
                                   Next j
                                Else
                                   b = dic.Item(dk)
                                   kq(b, 4) = kq(b, 4) + arr(i, 4)
                                   kq(b, 6) = kq(b, 6) + arr(i, 6)
                                End If
                                   tong = tong + arr(i, 6)
                                   kq(a + 1, 1) = "TÔNG CÔNG"
                                   kq(a + 1, 6) = tong
                        Next i
            End If
Next Sh

With Sheets("sheet2")
    .Range("J16").Resize(1000, 6).ClearContents
   If a Then .Range("J16").Resize(a + 1, 6).Value = kq
   .Range("J16").Resize(a + 1, 6).Borders.LineStyle = 1
End With
Set dic = Nothing
MsgBox "xong"
End Sub

Kiều Mạnh · 10/9/21

Cá ngừ F1 đã viết:
Thử dùng Power Query nhé.
Kết quả ở sheet PowerQuery.
Click phải chuột vào dùng dữ liệu chọn refresh.

cái này hay đấy ... mà nó có tổng hợp được data ở file bất kỳ đang đóng mà ko biết tên Sheet ko bạn
có nghĩa lấy dữ liệu Files đang đóng mà ko biết trước tên Sheet mà có nhiêu lấy hết

VD vầy: D:\Data.xlsx ... ko biết trong đó có nhiêu Sheet ... xong tổng hợp nó lại

Maika8008 · 10/9/21

HUONGHCKT đã viết:
Sao bạn không làm một vòng lặp duyệt qua các Sh để lấy dữ liệu vào mảng.
Xin phép bạn tôi sửa lại code như sau:

Bạn thụt đầu dòng bị lố. Chắc là chưa biết rõ vì sao cần phải thụt đầu dòng nhỉ? Bên trong With... End With, If...End If và các vòng lặp For...Next, Do... Loop,... thì mới thụt 1 tab để cho dễ dò code, biết được code bên trong chúng đến đâu là kết thúc.

maixuanvuong276 · 10/9/21

HUONGHCKT đã viết:

Sao bạn không làm một vòng lặp duyệt qua các Sh để lấy dữ liệu vào mảng.
Xin phép bạn tôi sửa lại code như sau:

Mã:

Sub gop()
Dim i As Long, lr As Long, dic As Object, a As Long, C&, D&, tong&
Dim arr, kq(1 To 1000, 1 To 6), b As Long, dk As String, j As Long
Dim Sh As Worksheet, rng As Range
Set dic = CreateObject("scripting.dictionary")
For Each Sh In Worksheets
    lr = Sh.Range("C" & Rows.Count).End(xlUp).Row
    Set rng = Sh.Range("A1:G" & lr)
        Set Item = rng.Find("Tên SP")
            If Not Item Is Nothing Then
                D = Item.Row + 1
                    arr = Sh.Range("B" & D, "G" & lr).Value
                        For i = 1 To UBound(arr)
                            dk = arr(i, 1)
                                If Not dic.exists(dk) Then
                                   a = a + 1
                                   dic.Add dk, a
                                   For j = 1 To 6
                                       kq(a, j) = arr(i, j)
                                   Next j
                                Else
                                   b = dic.Item(dk)
                                   kq(b, 4) = kq(b, 4) + arr(i, 4)
                                   kq(b, 6) = kq(b, 6) + arr(i, 6)
                                End If
                                   tong = tong + arr(i, 6)
                                   kq(a + 1, 1) = "TÔNG CÔNG"
                                   kq(a + 1, 6) = tong
                        Next i
            End If
Next Sh

With Sheets("sheet2")
    .Range("J16").Resize(1000, 6).ClearContents
   If a Then .Range("J16").Resize(a + 1, 6).Value = kq
   .Range("J16").Resize(a + 1, 6).Borders.LineStyle = 1
End With
Set dic = Nothing
MsgBox "xong"
End Sub

bị lỗi chỗ item rồi bạn ơi

Cá ngừ F1 · 10/9/21

Kiều Mạnh đã viết:
cái này hay đấy ... mà nó có tổng hợp được data ở file bất kỳ đang đóng mà ko biết tên Sheet ko bạn
có nghĩa lấy dữ liệu Files đang đóng mà ko biết trước tên Sheet mà có nhiêu lấy hết

VD vầy: D:\Data.xlsx ... ko biết trong đó có nhiêu Sheet ... xong tổng hợp nó lại

Vâng anh, chắc chắn là được

Kiều Mạnh · 10/9/21

Cá ngừ F1 đã viết:
Vâng anh, chắc chắn là được

Thử làm 1 VD cho xem đi

maixuanvuong276 · 10/9/21

bạn nào sử giúp chỗ lỗi này với ạ

Cá ngừ F1 · 10/9/21

Kiều Mạnh đã viết:
Thử làm 1 VD cho xem đi

Sư phụ @ptm0412 có riêng một Topic về vấn đề này rồi anh ạ, quan trọng nhất là cấu trúc các sheet phải giống nhau. Hoặc giả sử dữ liệu là các Table nữa thì nối chúng lại với nhau trong PQ cũng sẽ dễ dàng hơn.

Tổng hợp (gộp) nhiều sheet của 1 file Excel, nhiều file trong folder bằng Power Query nâng cao

I. Tổng hợp (gộp) nhiều sheet trong 1 file Thông thường và với trình độ căn bản, khi muốn gộp (tổng hợp) nhiều sheet trên cùng 1 file Excel bằng Power query, các bạn tạo mỗi sheet 1 query con, sau đó Append chúng lại với nhau. Như vậy sẽ có nhiều query con mất công quản lý chúng. Các cách làm...

www.giaiphapexcel.com

Kiều Mạnh · 10/9/21

Cá ngừ F1 đã viết:
Sư phụ @ptm0412 có riêng một Topic về vấn đề này rồi anh ạ, quan trọng nhất là cấu trúc các sheet phải giống nhau. Hoặc giả sử dữ liệu là các Table nữa thì nối chúng lại với nhau trong PQ cũng sẽ dễ dàng hơn.

Tổng hợp (gộp) nhiều sheet của 1 file Excel, nhiều file trong folder bằng Power Query nâng cao

I. Tổng hợp (gộp) nhiều sheet trong 1 file Thông thường và với trình độ căn bản, khi muốn gộp (tổng hợp) nhiều sheet trên cùng 1 file Excel bằng Power query, các bạn tạo mỗi sheet 1 query con, sau đó Append chúng lại với nhau. Như vậy sẽ có nhiều query con mất công quản lý chúng. Các cách làm...

www.giaiphapexcel.com

tổng hợp được data ở file bất kỳ đang đóng mà ko biết tên Sheet

dòng trên của Bài số 9

Vậy Trong D:\MyFolder \ ... có 10 file trong đó hãy làm cho Mạnh 1 Button để chọn lấy 1 File bất kỳ ... có cấu trúc dữ liệu như nhau chỉ khác tên Sheet và ko biết trước tên Sheet + số lượng Sheet có trong 1 File

kelacloi · 10/9/21

Kiều Mạnh đã viết:
tổng hợp được data ở file bất kỳ đang đóng mà ko biết tên Sheet

dòng trên của Bài số 9

Vậy Trong D:\MyFolder \ ... có 10 file trong đó hãy làm cho Mạnh 1 Button để chọn lấy 1 File bất kỳ ... có cấu trúc dữ liệu như nhau chỉ khác tên Sheet và ko biết trước tên Sheet + số lượng Sheet có trong 1 File

Anh tải mấy file demo đó về rồi đổi đường dẫn, đổi tên tùy thích là được a. Sau đó chọn Data-> Refresh all là được a.

Kiều Mạnh · 10/9/21

kelacloi đã viết:
Anh tải mấy file demo đó về rồi đổi đường dẫn, đổi tên tùy thích là được a. Sau đó chọn Data-> Refresh all là được a.

đang xem ... hình như nó hơi khó sử dụng + chạy chậm thì phải

HUONGHCKT · 10/9/21

maixuanvuong276 đã viết:
bị lỗi chỗ item rồi bạn ơi

Bạn xem Có phải là Tên SP hay là gì khác. Tốt nhất bạn Copy ten SP trên dong tiêu đề của sh1 rồi dán vào code chỗ "" ấy.
tôi gửi lại file trên may tôi.

Kiều Mạnh · 10/9/21

hpkhuong đã viết:
Thằng này làm được nhiều thứ đó. Nhưng dữ liệu phải chuẩn chứ dữ liệu mà linh ta linh tinh thì vứt.

khó sử dụng lắm + chạy chậm
ADODB viết cho nó 1 hàm chọn File bất kỳ là lấy hết thôi
chọn file bất kỳ ko biết trước tên Sheet tổng hợp lại là xong

Maika8008 · 10/9/21

Kiều Mạnh đã viết:
khó sử dụng lắm + chạy chậm
ADODB viết cho nó 1 hàm chọn File bất kỳ là lấy hết thôi
chọn file bất kỳ ko biết trước tên Sheet tổng hợp lại là xong

Đâu phải công cụ mạnh là chạy nhanh đâu bạn nhỉ!

ngocphuong0612 · 10/9/21

Nhờ các cao nhân giúp mình gộp dữ liệu từ File 1 – Sheet “File tổng” sang File 2 – Sheet “BC chi tiết” – “CN VBTT”:

Dữ liệu ở file 2 – sheet “BC chi tiết”: Luôn cập nhật dữ liệu mới nhất từ File 1
Dữ liệu ở File 2 – Sheet “ CN VBTT”: Lấy toàn bộ dữ liệu của sheet “BC chi tiết” nếu có phát sinh số lần CN

Em làm công thức mảng nhưng mỗi lần lưu thì mất rất nhiều thời gian ạ

Kiều Mạnh · 10/9/21

Maika8008 đã viết:
Đâu phải công cụ mạnh là chạy nhanh đâu bạn nhỉ!

rảnh đang hỏi sơ bộ vài thứ xem nó siêu việt cỡ nào đó mà

Ít hôm nữa rảnh xem tình hình sao viết thêm 1 Hàm phụ là lấy 3 CSDL : Access + Excel + SQLite thôi ... còn Ms Server + xx cũng thế có điều tách riêng nó ra vì liên quan tới Ip + Port còn lại nó giống như nhau cả vì làm biếng thêm Optional cho dài dòng + rối ra đó mà

cơ bản hàm đã có sẳn trong Cái API mới úp GPE đó ... chỉ viết thêm Hàm Phụ duyệt Sheetname tới đâu gán nó vào tới đó là Xong

Cá ngừ F1 · 10/9/21

Kiều Mạnh đã viết:
đang xem ... hình như nó hơi khó sử dụng + chạy chậm thì phải

View attachment 265787

Ưu nhược điểm thì cũng phân tích nhiều rồi anh. Với dữ liệu lớn thì dùng ADO còn theo mệt, chưa kể còn tính toán, tổng hợp, sắp sếp, thiết lập mối quan hệ giữa các bảng

Bài đã được tự động gộp: 10/9/21

hpkhuong đã viết:
Làm sao để lấy được đường dẫn Full File Name của Workbook đang chạy code M vậy bạn? Tôi tìm hoài không thấy lệnh.

Mục đích: Gửi File này cho bất cứ ai cũng chạy được. Không phải thay lại Source (Source đang lấy là Full File Name của File excel đang chạy PQ)

Cảm ơn!

Có thể đặt name đường dẫn file
Bạn đọc topic có trong phần chữ ký của tôi (M function). Bài #8 thì có tham chiếu bằng name.

Kiều Mạnh · 10/9/21

Không hiểu lắm trong List có vài Files mà trên Sheet nó lặp lại nhiều thế
Mở lên thôi nó laod cũng chậm ... nó load cả .Net nữa hèn chi chậm

Cá ngừ F1 · 10/9/21

hpkhuong đã viết:
Cụ thể tôi cần thay cụm này:

Source = Excel.Workbook(File.Contents("D:\Excel\Query\File Test PowerQuery.xlsx"), null, true),

Cụm: "D:\Excel\Query\File Test PowerQuery.xlsx" này chính là Full Name của File Excel đang chạy PQ

Nếu đặt name trong File thì có thể xài công thức để lấy ra cụm trên: nhưng không biết kết hợp thay thế vào như nào

Name là: TenFile =SUBSTITUTE(LEFT(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1),1)-1),"[","")

Không hiểu có phải bạn hỏi ý này.
Tôi gửi bạn 1 File và 1 Folder Source, bạn giải nén ra 1 folder.
Mở File Data, phần Sheet Setting đã có đường dẫn, bạn có thể đổi tên File để thay nguồn. Nguồn tôi đã đặt bằng 1 name. Sang phần Sheet1 rồi refresh. Nó sẽ lấy dữ liệu theo đúng Source.

Bài đã được tự động gộp: 10/9/21

Kiều Mạnh đã viết:
Không hiểu lắm trong List có vài Files mà trên Sheet nó lặp lại nhiều thế
Mở lên thôi nó laod cũng chậm ... nó load cả .Net nữa hèn chi chậm

View attachment 265806

thêm trường đó để biết dữ liệu lấy từ File nào đó anh.

ptm0412 · 10/9/21

Kiều Mạnh đã viết:
Không hiểu lắm trong List có vài Files mà trên Sheet nó lặp lại nhiều thế
Mở lên thôi nó laod cũng chậm ... nó load cả .Net nữa hèn chi chậm

Trong hình của Kiều Mạnh có 6 files, trong đó 2 file gốc và 4 file copy.
Folder gốc có 2 file:

Step FileNameList cũng chỉ thấy 2:

Đặc điểm là nếu thêm file cùng cấu trúc như hình của Mạnh, khi refresh cũng lấy hết.

Hình thứ 2 kết quả nhiều dòng Data4Sheet.xls và DT là vì file Data4Sheet.xlsx, sheet DT có nhiều dòng dữ liệu. 2 cột này thêm vào là để phân biệt dữ liệu của file nào, sheet nào. Nếu không cần thì trong step DataF, bỏ 2 dòng AddColumn đi.

ptm0412 · 10/9/21

Nói thêm: Trong file ở chủ đề này: https://www.giaiphapexcel.com/diendan/threads/tổng-hợp-nhiều-file-thành-1-file-bằng-power-query.156768/post-1040037
Tên file là dữ liệu của tháng nào đó, và của cửa hàng/ chi nhánh nào đó. Tên sheet là dữ liệu của nhóm Sale nào đó, thì cần có 2 cột tên file và tên sheet để lập báo cáo phân tích các kiểu. Chứ dữ liệu tổng hợp lại để đó thì vô nghĩa.

Ngoài ra, sử dụng Power query còn có cái lợi là khi dữ liệu tổng hợp nhiều file nhiều sheet lại mà lớn hơn 1 triệu dòng (thậm chí vài triệu, vài chục triệu) thì có thể không gán xuống sheet mà gán vào data model. VBA, ADO các kiểu không làm được chuyện này.

tranhungdao12a3 · 11/9/21

Nếu bạn chập dữ liệu xong, nếu dữ liệu hàng triệu dòng trở lên, bạn dùng Power Query chập dữ liệu rồi tạo connection sau đó nạp nó vào Data Model. Khi này sử dụng Power Pivot để phân tích thì bạn sẽ thấy giá trị phân tích của nó, sẽ hiểu được vì sao microsoft họ đẻ ra cái công cụ này. Chứ mất công chập Power query mà không làm việc gì đến phân tích thì cần gì Power Query, các Tool hay code VBA tổng hợp các anh viết đầy trên diễn đàn đó.

Kiều Mạnh · 11/9/21

ptm0412 đã viết:
Nói thêm: Trong file ở chủ đề này: https://www.giaiphapexcel.com/diendan/threads/tổng-hợp-nhiều-file-thành-1-file-bằng-power-query.156768/post-1040037
Tên file là dữ liệu của tháng nào đó, và của cửa hàng/ chi nhánh nào đó. Tên sheet là dữ liệu của nhóm Sale nào đó, thì cần có 2 cột tên file và tên sheet để lập báo cáo phân tích các kiểu. Chứ dữ liệu tổng hợp lại để đó thì vô nghĩa.

View attachment 265827

Ngoài ra, sử dụng Power query còn có cái lợi là khi dữ liệu tổng hợp nhiều file nhiều sheet lại mà lớn hơn 1 triệu dòng (thậm chí vài triệu, vài chục triệu) thì có thể không gán xuống sheet mà gán vào data model. VBA, ADO các kiểu không làm được chuyện này.

có lẻ sức mạnh của nó là nạp dữ liệu vào Data model và khi cần thì phân tích nó thôi ... còn lấy dữ liệu thông thường VD như tổng hợp Sheet + Files mà ko biết tên Sheet thì ADOB làm ok hết + tốc độ sẻ nhanh hơn đấy

VD: tạo 1 Button nhấn nút chọn 1 File bất kỳ ... thì code đơn gian lắm nó lấy lên hết + rất nhanh
Còn Power Query sẻ mất công chỉnh sửa + vvv xong mới thực hiện được

ptm0412 · 11/9/21

Kiều Mạnh đã viết:
có lẻ sức mạnh của nó là nạp dữ liệu vào Data model và khi cần thì phân tích nó thôi ... còn lấy dữ liệu thông thường VD như tổng hợp Sheet + Files mà ko biết tên Sheet thì ADOB làm ok hết + tốc độ sẻ nhanh hơn đấy

Đồng ý về điểm tốc độ. Lần đầu tiên mở file sẽ phải load .Net, các lần sau refresh sẽ nhanh hơn 1 chút, nhưng có lẽ vẫn thua ADO.
Power query còn thêm 1 đặc điểm nữa là sau khi load dữ liệu tương đương với ADO xong, các hàm M của PQ cũng còn có thể làm thêm 1 số việc nữa ví dụ như unpivot, thêm cột conditional column, cột luỹ kế, cột đếm có điều kiện, đếm luỹ kế riêng rẽ từng nhóm, ... Nếu ADO phải rất rành rẽ câu lệnh SQL mới làm được, hoặc không làm được luôn.

Kiều Mạnh · 11/9/21

ptm0412 đã viết:
Đồng ý về điểm tốc độ. Lần đầu tiên mở file sẽ phải load .Net, các lần sau refresh sẽ nhanh hơn 1 chút, nhưng có lẽ vẫn thua ADO.
Power query còn thêm 1 đặc điểm nữa là sau khi load dữ liệu tương đương với ADO xong, các hàm M của PQ cũng còn có thể làm thêm 1 số việc nữa ví dụ như unpivot, thêm cột conditional column, cột luỹ kế, cột đếm có điều kiện, đếm luỹ kế riêng rẽ từng nhóm, ... Nếu ADO phải rất rành rẽ câu lệnh SQL mới làm được, hoặc không làm được luôn.

đồng ý với anh cái khúc Em tô đậm thôi
còn chỉnh sửa các hàm M em thấy chưa phổ biến lắm ít nhất là thời điểm hiện tại trên GPE này
và người dùng phổ thông rất khó khi vận dụng nó

Còn ADODB câu SQL nó vô cùng lắm ... trên GPE này e rằng chưa có ai dám khẳng định là mình làm được tất cả thuần SQL cho ADODB

Cá ngừ F1 · 11/9/21

Kiều Mạnh đã viết:
đồng ý với anh cái khúc Em tô đậm thôi
còn chỉnh sửa các hàm M em thấy chưa phổ biến lắm ít nhất là thời điểm hiện tại trên GPE này
và người dùng phổ thông rất khó khi vận dụng nó

Còn ADODB câu SQL nó vô cùng lắm ... trên GPE này e rằng chưa có ai dám khẳng định là mình làm được tất cả thuần SQL cho ADODB

Em không rành về ADO, chỉ áp dụng mấy hàm mà các tiền bối đã xây dựng. Nên thấy cấu trúc của ADO hơi khó hiểu, làm trên Power Query thấy đơn giản hơn.

tranhungdao12a3 · 11/9/21

Kiều Mạnh đã viết:
đồng ý với anh cái khúc Em tô đậm thôi
còn chỉnh sửa các hàm M em thấy chưa phổ biến lắm ít nhất là thời điểm hiện tại trên GPE này
và người dùng phổ thông rất khó khi vận dụng nó

Còn ADODB câu SQL nó vô cùng lắm ... trên GPE này e rằng chưa có ai dám khẳng định là mình làm được tất cả thuần SQL cho ADODB

Nhiều người dùng phân tích trong đó có tôi thì cũng không thạo nhiều về M mà chỉ biết các tính năng cơ bản có trên các tab của Power Query. Chủ yếu dùng chập dữ liệu từ các file hoặc folder hoặc get data từ các nguồn khác, chứ việc phân tích thì phân tích trên Power Pivot hoặc Power Bi(+R+Python). Cho nên tính năng của Power Query phần lớn người sử dụng nó để chập dữ liệu, chuẩn hoá dữ liệu thôi. Diễn đàn chủ yếu toàn lên hỏi ăn xổi luôn thì toàn hỏi VBA hoặc công thức Excel chứ mấy người hỏi những thứ khác đâu. Cho nên mấy mục này bắt đầu ở diễn đàn thì mới là sơ khai giai đoạn đầu thôi.

Kiều Mạnh · 11/9/21

Cá ngừ F1 đã viết:
Em không rành về ADO, chỉ áp dụng mấy hàm mà các tiền bối đã xây dựng. Nên thấy cấu trúc của ADO hơi khó hiểu, làm trên Power Query thấy đơn giản hơn.

ADODB chỉ khó nhất cái câu lệnh SQL thôi nó vô cùng lắm
còn lại không khó lắm chỉ có cái Conn và Rs thôi ... cơ bản mọi cái trên GPE này có hết rồi
quan trọng là có biết vận dụng nó hay không thôi

Rảnh mạnh sẻ viết thêm 1 hàm API chọn 1 Files bất kỳ là nó tổng hợp hết dữ liệu có trong File đó gán nối xuống trên 1 Sheet ... mọi cái có hết rồi chỉ là thêm vào chút thôi
Vì xét thấy nhu cầu thực tế trên GPE này lâu lâu có người hỏi bài kiểu đó

Sẻ áp dụng cho 3 CSDL là Access + Excel + SQLite chung vào 1 hàm duy nhất

Maika8008 · 11/9/21

Kiều Mạnh đã viết:
ADODB chỉ khó nhất cái câu lệnh SQL thôi nó vô cùng lắm
còn lại không khó lắm chỉ có cái Conn và Rs thôi ... cơ bản mọi cái trên GPE này có hết rồi
quan trọng là có biết vận dụng nó hay không thôi

Câu SLQ khó mà cứ chịu khó mày mò tí rồi cũng viết được. Mà đã viết được 1 lần thì lần sau thấy dễ hơn. Cái khó và dễ sai nhất của nó là lồng biến vào câu lệnh phức tạp, thêm bớt các dấu nháy đơn, nháy kép muốn phát khùng mà vẫn bị báo lỗi.

Kiều Mạnh · 11/9/21

@Cá ngừ F1 cái bộ hàm đó rất đơn giản và tiện dụng đấy
Áp dụng cho 3 trong 1 Hàm duy nhất truy xuất 3 CSDL khác nhau
trên GPE này tới thời điểm hiện tại đó là Hàm đầu tiên xuất hiện đấy ... mong là sau này có đầy ra

Chia sẻ thư viện API truy xuất CSDL SQLite - Access - Excel và Ms Server

1/ Rảnh tôi đang thử nghiệm tự tay viết lấy thư viện API cho mình sử dụng khi cần thiết ... Truy xuất CSDL như tiêu đề nêu trên 2/ Úp lên đây cho bạn nào cần thì sử dụng... ko cá nhân hóa hay bất cứ bản quyền gì ... ai muốn sử dụng nó vào mục đích thương mai hay gì đó Mình ko quan tâm Cho đi là...

www.giaiphapexcel.com

ptm0412 · 11/9/21

Hơi lạc ra khỏi chủ đề này (tổng hợp dữ liệu từ nhiều sheet) 1 chút:
- Power query nếu muốn tạo quan hệ 2 bảng thì phải query 2 bảng đơn trước, rồi mới dùng câu lệnh Merge. Nhiều bảng quan hệ với nhau cũng phải lấy hết xuống rồi merge từng cặp. Còn ADO dùng câu lệnh SQL với cú pháp join ... on (left, right hay join tuỳ hoàn cảnh). Một câu lệnh SQL có thể join hết từng ấy bảng, và kết quả chỉ là 1 bảng kết quả cuối cùng.
- Power query dùng Table.Combine đơn thuần cũng phải lấy hết các bảng xuống rồi mới combine, SQL chỉ cần lệnh Union và chỉ kết quả cuối mới đưa xuống. (Hàm tôi viết tổng quát ở những ảnh chụp và trích dẫn bên trên thì không dùng combine, lại phải ứng dụng hàm M theo cách khác.
- SQL nói thật là "vô cùng", thậm chí có thể tạo các bảng ảo và Select trên đó và nhiều thứ khác mà tôi không biết.

Nói chung là người dùng ở 1 trình độ nào đó sẽ thích cái này hơn cái kia, ở trình độ khác thì ngược lại. Thích cái gì cũng nên biết trước ưu nhược điểm của cái đó mà áp dụng cho phù hợp vấn đề đang làm.

ongke0711 · 11/9/21

Theo tôi thì không so sánh ADO và Power Query (PQ) được vì cách thức hoạt động, chức năng nó khác nhau mặc dù có công đoạn nào đó giống nhau như: kết nối CSDL.
- PQ thì lấy dữ liệu và tích hợp sẳn công cụ phân tích xử lý rất mạnh. Nói chung là làm trọn gói. Tôi nghĩ có thể nói PQ, PBI nó giống như một cái ứng dụng (như Ms Access chẳng hạn) hơn là một thư viện ADODB.
- Còn đối với ADO (như cách tôi thường làm) là một lớp trung gian để kết nối với các loại CSDL, thực thi các câu lệnh SQL. Vấn đề là ở việc xử lý các câu lệnh SQL: nếu thực thi hoàn toàn các câu lệnh SQL từ ADO thì tôi chắc chắn không đủ để tổng hợp, truy vấn, phân tích dữ liệu đáp ứng yêu cầu được. Đối với CSDL Access, tôi phải tận dụng sức mạnh Query của nó để tổng hợp ra dữ liệu mình cần, đối với CSDL SQL Server thì viết các store proc, hàm trực tiếp trên SQL Server (dùng ngôn ngữ của nó) và ADO chỉ làm cầu nối, gửi các tham số, yêu cầu SQL Server thực thi rồi lấy kết quả về thôi.
Nói tóm lại để kết nối dữ liệu, thực hiện các truy vấn cơ bản thì ADO làm được nhưng chuyên sâu xử lý thì không. Ngược lại thì PQ nó làm được điều này. Nói thực là tôi chỉ biết PQ ở mức cơ bản nhưng chỉ cần đọc tài liệu là có thể hiểu được khả năng của nó.

excel_lv1.5 · 11/9/21

ptm0412 đã viết:
Hơi lạc ra khỏi chủ đề này (tổng hợp dữ liệu từ nhiều sheet) 1 chút:
- Power query nếu muốn tạo quan hệ 2 bảng thì phải query 2 bảng đơn trước, rồi mới dùng câu lệnh Merge. Nhiều bảng quan hệ với nhau cũng phải lấy hết xuống rồi merge từng cặp. Còn ADO dùng câu lệnh SQL với cú pháp join ... on (left, right hay join tuỳ hoàn cảnh). Một câu lệnh SQL có thể join hết từng ấy bảng, và kết quả chỉ là 1 bảng kết quả cuối cùng.
- Power query dùng Table.Combine đơn thuần cũng phải lấy hết các bảng xuống rồi mới combine, SQL chỉ cần lệnh Union và chỉ kết quả cuối mới đưa xuống. (Hàm tôi viết tổng quát ở những ảnh chụp và trích dẫn bên trên thì không dùng combine, lại phải ứng dụng hàm M theo cách khác.
- SQL nói thật là "vô cùng", thậm chí có thể tạo các bảng ảo và Select trên đó và nhiều thứ khác mà tôi không biết.

Nói chung là người dùng ở 1 trình độ nào đó sẽ thích cái này hơn cái kia, ở trình độ khác thì ngược lại. Thích cái gì cũng nên biết trước ưu nhược điểm của cái đó mà áp dụng cho phù hợp vấn đề đang làm.

2 môi trường khác nhau, nếu viết full câu lệnh SQL (giả sử request tới SQL server) thì nó xử lý ở trên SQL server, còn đẩy raw về rồi ETL ở power query thì nó là môi trường của power query kết quả trả về là kết quả cuối cùng mặc dù gọi nhiều bảng nhưng chỉ là query chứ không lưu, còn việc xử lý thì chắc chắn môi trường ở sql server sẽ nhanh hơn power query rồi, còn so với môi trường xử lý khác như Access, ODBC tới database của Foxpro thì không chắc Power query thua đâu khi cần clean data ở dạng nhiều,với SQL server thì connect Power query full SLQ sẽ ngon hơn mà thực tế là vậy người ta sẽ viết thẳng cái code rồi gửi request connect luôn, PQ cho phép điều gửi code lập trình SQL thẳng để request, không biết ADO có làm được điều này không tôi đã thử sử dụng tạo bảng tạm và tính toán trên bảng tạm đó để lấy dữ liệu mong muốn bằng ADO nhưng không connect được chưa kể đến loop, ví dụ với câu đơn giản như vầy ADO sẽ báo lỗi, nếu mà không request được dạng này mà chỉ là mấy câu select bình thường ADO thì không thể so ETL được với power query

Cá ngừ F1 · 11/9/21

ongke0711 đã viết:
Theo tôi thì không so sánh ADO và Power Query (PQ) được vì cách thức hoạt động, chức năng nó khác nhau mặc dù có công đoạn nào đó giống nhau như: kết nối CSDL.
- PQ thì lấy dữ liệu và tích hợp sẳn công cụ phân tích xử lý rất mạnh. Nói chung là làm trọn gói.
- Còn đối với ADO (như cách tôi thường làm) là một lớp trung gian để kết nối với các loại CSDL, thực thi các câu lệnh SQL. Vấn đề là ở việc xử lý các câu lệnh SQL: nếu thực thi hoàn toàn các câu lệnh SQL từ ADO thì tôi chắc chắn không đủ để tổng hợp, truy vấn, phân tích dữ liệu đáp ứng yêu cầu được. Đối với CSDL Access, tôi phải tận dụng sức mạnh Query của nó để tổng hợp ra dữ liệu mình cần, đối với CSDL SQL Server thì viết các store proc, hàm để chạy truy vấn trực tiếp trên Server và ADO chỉ làm cầu nối, gửi các tham số, yêu cầu SQL Server thực thi rồi lấy kết quả về thôi.
Nói tóm lại để kết nối dữ liệu, thực hiện các truy vấn cơ bản thì ADO làm được nhưng chuyên sâu xử lý thì không. Ngược lại thì PQ nó làm được điều này. Nói thực là tôi chỉ biết PQ ở mức cơ bản nhưng chỉ cần đọc tài liệu là có thể hiểu được khả năng của nó.

Như trên em có nói là em không rành về ADO. Nên em hỏi anh chút. Giả sử dữ liệu 15 ngày đã > 1.2 tr record. Thì ADO có thể collect và group by lại theo các tiêu chí được không anh?

Bài đã được tự động gộp: 11/9/21

hpkhuong đã viết:
Rồi tôi hiểu tại Sao rồi. Name tôi đặt là trực tiếp trong Name Manager của Excel. Không phải Name đặt tại 1 ô trên Sheet.
Power Query nó không chịu lấy name đặt trực tiếp trong Name Manager mà không thông qua trên Range

Name đặt ở đâu cũng được chứ anh. Bản chất như nhau. Kể cả name động đi chăng nữa

ongke0711 · 11/9/21

excel_lv1.5 đã viết:
2 môi trường khác nhau, nếu viết full câu lệnh SQL (giả sử request tới SQL server) thì nó xử lý ở trên SQL server, còn đẩy raw về rồi ETL ở power query thì nó là môi trường của power query kết quả trả về là kết quả cuối cùng mặc dù gọi nhiều bảng nhưng chỉ là query chứ không lưu, còn việc xử lý thì chắc chắn môi trường ở sql server sẽ nhanh hơn power query rồi, còn so với môi trường xử lý khác như Access, ODBC tới database của Foxpro thì không chắc Power query thua đâu khi cần clean data ở dạng nhiều,với SQL server thì connect Power query full SLQ sẽ ngon hơn mà thực tế là vậy người ta sẽ viết thẳng cái code rồi gửi request connect luôn, PQ cho phép điều gửi code lập trình SQL thẳng để request, không biết ADO có làm được điều này không tôi đã thử sử dụng tạo bảng tạm và tính toán trên bảng tạm đó để lấy dữ liệu mong muốn bằng ADO nhưng không connect được chưa kể đến loop, ví dụ với câu đơn giản như vầy ADO sẽ báo lỗi, nếu mà không request được dạng này mà chỉ là mấy câu select bình thường ADO thì không thể so ETL được với power queryView attachment 265871

Theo như hình của bạn thì thấy PQ nó dùng luôn cú pháp của SQL Server để viết và gửi lệnh luôn, vậy thì tiện nhỉ.
Đối với ADO, muốn thực hiện câu lệnh trên (câu lệnh SQL cơ bản) thì dùng ADO Command và cung cấp parameter @date, @Year thì cũng lấy dữ liệu về được nhé.

excel_lv1.5 · 11/9/21

ongke0711 đã viết:
Theo như hình của bạn thì thấy PQ nó dùng luôn cú pháp của SQL Server để viết và gửi lệnh luôn, vậy thì tiện nhỉ.
Đối với ADO, muốn thực hiện câu lệnh trên (câu lệnh SQL cơ bản) thì dùng ADO Command và cung cấp parameter @date, @Year thì cũng lấy dữ liệu về được nhé.

Ah, ý tôi hỏi là câu lệnh trên nó có lưu vào bảng tạm #a1 sau đó dùng một câu select khác để gọi cái bảng tạm đó không tôi thử với ADO thì nó lỗi, ADO có thể tạo bảng tạm và gọi lại bảng tạm trong 1 lần connect không, lý do tạo bảng tạm là để tránh việc một câu select có subquery được gọi quá nhiều lần , rồi có thể gửi đoạn loop được không như while begin...end chẳng hạn?

Cá ngừ F1 · 11/9/21

hpkhuong đã viết:
Bạn thử lại đi. Theo câu M của bạn thì chỉ áp dụng được Name từ Range, hay tôi không biết cách nhỉ?

fname= Excel.CurrentWorkbook(){[Name="TenFile"]}[Content]{0}[Column1] ,

Trong File có 1 Name là Source đặt trực tiếp trong Name Manager, bạn thử được không nhé

Công nhận lỗi, em cũng không hiểu? phải nhờ các tiền bối @excel_lv1.5, @ptm0412 giải thích thêm.
Như nhìn bảng NameManager thì nó đang chưa hiểu Value của cái hàm này trả về kết quả gì.

ptm0412 · 11/9/21

excel_lv1.5 đã viết:
2 môi trường khác nhau, nếu viết full câu lệnh SQL (giả sử request tới SQL server) thì nó xử lý ở trên SQL server, còn đẩy raw về rồi ETL ở power query thì nó là môi trường của power query kết quả trả về là kết quả cuối cùng mặc dù gọi nhiều bảng nhưng chỉ là query chứ không lưu,

Tôi hiểu điều này. PQ (Power query) làm đơn thuần bằng các step lệnh trên giao diện thì phải lấy về đủ các bảng sau đó thiết lập quan hệ và merge, hoặc combine. Và tôi cũng nói thêm rằng nếu không dùng combine thì dùng hàm M khác để thực hiện. Lúc này phải tự viết trong query advanced editor.
Về câu lệnh SQL lấy trực tiếp trên server, thì do tôi không có môi trường SQL server, mà chỉ có môi trường PostGreSQL nên thực hành trên đó để viết tài liệu căn bản. Trong đó tôi chỉ gõ câu lệnh SQL chuẩn bao gồm select lồng, join, groupby, thêm cột đơn giản. Thế là lấy được dữ liệu. Câu SQL đó là câu mà tôi thực hiện trên cửa sổ PostGreSQL thành công, chỉ là cần lấy về Excel để phân tích.
Ảnh sau là trang 132 của tài liệu, câu SQL không phức tạp và không nhiều bảng.

excel_lv1.5 · 11/9/21

Cá ngừ F1 đã viết:
Công nhận lỗi, em cũng không hiểu? phải nhờ các tiền bối @excel_lv1.5, @ptm0412 giải thích thêm.
Như nhìn bảng NameManager thì nó đang chưa hiểu Value của cái hàm này trả về kết quả gì.

View attachment 265877

Tôi ít sài Name để gọi giá trị ngoài worksheet, thông thường tôi sẽ convert nó thành table rồi đẩy thẳng vào power query, đỡ bước tạo Name. Còn lỗi theo tôi nghĩ là do nó không nhận là value như string hay number mà bắt buộc phải là dạng table, record (range, cell)

ptm0412 · 11/9/21

excel_lv1.5 đã viết:
Tôi ít sài Name để gọi giá trị ngoài worksheet, thông thường tôi sẽ convert nó thành table rồi đẩy thẳng vào power query, đỡ bước tạo Name. Còn lỗi theo tôi nghĩ là do nó không nhận là value như string hay number mà bắt buộc phải là dạng table, record (range, cell)

Anh convert thành table thì bản chất nó sinh ra 1 name "Table1", có tiêu đề. Còn đặt name như @Cá ngừ F1 (là làm theo tôi), không có tiêu đề. Chính vì vậy truy xuất sẽ khác nhau: Name không tiêu đề phải truy xuất "Column1" thay vì tiêu đề.

Name định nghĩa cứng trong Name manager, name động bằng hàm offset (hoặc hàm khác) sẽ không được PQ nhận dạng.

excel_lv1.5 · 11/9/21

ptm0412 đã viết:
Tôi hiểu điều này. PQ (Power query) làm đơn thuần bằng các step lệnh trên giao diện thì phải lấy về đủ các bảng sau đó thiết lập quan hệ và merge, hoặc combine. Và tôi cũng nói thêm rằng nếu không dùng combine thì dùng hàm M khác để thực hiện. Lúc này phải tự viết trong query advanced editor.
Về câu lệnh SQL lấy trực tiếp trên server, thì do tôi không có môi trường SQL server, mà chỉ có môi trường PostGreSQL nên thực hành trên đó để viết tài liệu căn bản. Trong đó tôi chỉ gõ câu lệnh SQL chuẩn bao gồm select lồng, join, groupby, thêm cột đơn giản. Thế là lấy được dữ liệu. Câu SQL đó là câu mà tôi thực hiện trên cửa sổ PostGreSQL thành công, chỉ là cần lấy về Excel để phân tích.
Ảnh sau là trang 132 của tài liệu, câu SQL không phức tạp và không nhiều bảng.

View attachment 265879

Có thể xem power query (PQ) như cái trình biên dịch, SQL server có thể code thẳng trên giao diện của power query, tôi nghĩ PostGreSQL chắc cũng vậy, việc sử dụng join cũng như merge trong PQ, thì cái này mình phải lựa chọn thôi, tôi ví dụ tôi thường làm SQL server và tôi chắc rằng là SQLserver nó sẽ xử lý nhanh hơn, nên tôi sẽ code full SQL rồi code đưa vào PQ để lấy kết quả cuối luôn, tôi cũng thường connect vào database của foxpro thông qua ODBC để lấy dữ liệu cũ và tôi cũng biết rằng nếu viết full code fox thì sẽ chậm hơn khi load data raw về PQ để transform nên tôi sẽ chọn load về rồi xử lý bằng PQ, vì vậy tôi nghĩ tốc độ xử lý của cái nào ngon hơn thì chọn cái đó thôi, cả 2 đề thực hiện được trên cả PQ, nếu không tính M code thì việc transform bằng những thứ có sẵn trên thanh công cụ PQ thực tế là đơn giản hơn việc ngồi viết full code connect

Bài đã được tự động gộp: 11/9/21

ptm0412 đã viết:
Anh convert thành table thì bản chất nó sinh ra 1 name "Table1", có tiêu đề. Còn đặt name như @Cá ngừ F1 (là làm theo tôi), không có tiêu đề. Chính vì vậy truy xuất sẽ khác nhau: Name không tiêu đề phải truy xuất "Column1" thay vì tiêu đề.

Name định nghĩa cứng trong Name manager, name động bằng hàm offset (hoặc hàm khác) sẽ không được PQ nhận dạng.

Không phải bạn, nó là table đó bạn tại vì cái bước đó nó bỏ qua bước trả về table mà nó trả về giá trị cuối của table thôi, offset trả về range vẫn nhận, chỉ có dạng value là không

ongke0711 · 11/9/21

Cá ngừ F1 đã viết:
Như trên em có nói là em không rành về ADO. Nên em hỏi anh chút. Giả sử dữ liệu 15 ngày đã > 1.2 tr record. Thì ADO có thể collect và group by lại theo các tiêu chí được không anh?

- Group by là do cách viết câu lệnh SQL thôi bạn. Tính toán Sum, Count, Average, Min, Max là nhưng cái mặc định trong Group by (Total Query).
- Tổng hợp dữ liệu 1,2 triệu dòng thì ADO làm được nhưng nhanh chậm thì lại đi vào việc so sánh tốc độ lấy dữ liệu. Vừa rồi có bài đề cập rồi đó: ADODB, PQ, Python...
Theo cách tôi làm, không bao giờ dính chết vào một loại công cụ nào đó, qui mô dữ liệu tới đâu mình lựa chọn công cụ phù hợp với nó, nếu ADO chạy ì ạch quá thì tìm cái khác xem có tối ưu hơn không và phù hợp với hệ sinh thái thiết kế của bạn. Vd: Excel giới hạn dòng thì dùng Access làm database; Khi Ms Access database hết 2G dữ liệu thì mình dùng SQL Server làm Back end, dùng Access để thiết kế giao diện Font end thôi. Và đối với tôi ADO chỉ làm cầu nối thôi chứ không dùng thuần ADO để truy vấn dữ liệu (chỉ dùng cho các câu lệnh SQL đơn giản). Dùng tài nguyên máy chủ SQL Server để tổng hợp dữ liệu, dùng tài nguyên máy khách để thực hiện kết nối ADO, tải dữ liệu về.
Các ngôn ngữ xuất hiện để đáp ứng một nhu cầu nào đó và tất nhiên nó sẽ có điểm mạnh ở một phân khúc nào đó, nếu mình có khả năng và cũng đang lập trình ở phân khúc đó thì nên học hỏi thêm để cải tiến. Như tôi hiện giờ, thì khả năng học thêm nữa không cao nên chỉ làm những cái ứng dụng phù hợp với ngôn ngữ mà mình biết mà nó vẫn còn chạy tốt, chứ còn nói tới Big Data, Data Science mà cứ VBA là chết toi.

Bài đã được tự động gộp: 11/9/21

excel_lv1.5 đã viết:
Ah, ý tôi hỏi là câu lệnh trên nó có lưu vào bảng tạm #a1 sau đó dùng một câu select khác để gọi cái bảng tạm đó không tôi thử với ADO thì nó lỗi, ADO có thể tạo bảng tạm và gọi lại bảng tạm trong 1 lần connect không, lý do tạo bảng tạm là để tránh việc một câu select có subquery được gọi quá nhiều lần , rồi có thể gửi đoạn loop được không như while begin...end chẳng hạn?

ADO thì không tự tạo bảng tạm #tempTable trên SQLServer rồi bạn. Như tôi đã nói ADO chạy câu lệnh SQL cơ bản, việc tạo bảng tạm thì ADO chỉ có thể gọi thực thi hàm, thủ tục nội tại trên SQL Server thực hiện thôi.

ptm0412 · 11/9/21

excel_lv1.5 đã viết:
Không phải bạn, nó là table đó bạn tại vì cái bước đó nó bỏ qua bước trả về table mà nó trả về giá trị cuối của table thôi, offset trả về range vẫn nhận, chỉ có dạng value là không

Khi Name là 1 giá trị đơn ở 1 ô trên sheet, tôi biết rằng đó là table, tôi chỉ nói rằng nó là table không tiêu đề, và bị gán tiêu đề là Column1. Khi truy xuất nó là cú pháp truy xuất table, tên field là Column1, record 0
Và tôi mới test, name động trả về Range vẫn được nhận dạng như bạn bảo. Chắc tôi nhớ nhầm trong 1 trường hợp đạc biệt nào đó.

Cá ngừ F1 · 11/9/21

excel_lv1.5 đã viết:
Tôi ít sài Name để gọi giá trị ngoài worksheet, thông thường tôi sẽ convert nó thành table rồi đẩy thẳng vào power query, đỡ bước tạo Name. Còn lỗi theo tôi nghĩ là do nó không nhận là value như string hay number mà bắt buộc phải là dạng table, record (range, cell)

Hi anh,
Nếu anh convert nguồn thành table và đẩy vào query, cụ thể như file ở bài #46 của @hpkhuong, thì để lấy dữ liệu thì phải tạo parameter và invoke function hả anh?

ptm0412 · 11/9/21

Cá ngừ F1 đã viết:
Hi anh,
Nếu anh convert nguồn thành table và đẩy vào query, cụ thể như file ở bài #46 của @hpkhuong, thì để lấy dữ liệu thì phải tạo parameter và invoke function hả anh?

Giả sử tên file tạo thành 1 table 1 dòng 1 cột ở I1:I2, tiêu đề là "tên file", thì cũng truy xuất record 0, field là [Tên file]

Nếu bảng 2 cột, 3 cột thì truy xuất theo tên field
Nếu bảng 2 dòng, 3 dòng, thì truy xuất theo record 1, record 2 (do bắt đầu từ 0)

Cá ngừ F1 · 12/9/21

ptm0412 đã viết:
Giả sử tên file tạo thành 1 table 1 dòng 1 cột ở I1:I2, tiêu đề là "tên file", thì cũng truy xuất record 0, field là [Tên file]

View attachment 265911

Nếu bảng 2 cột, 3 cột thì truy xuất theo tên field
Nếu bảng 2 dòng, 3 dòng, thì truy xuất theo record 1, record 2 (do bắt đầu từ 0)

Ổn rồi ạ.
Mà có thể anh @excel_lv1.5 có thêm cách khác. Vì anh có đẩy cái table vào query để làm tham chiếu.

excel_lv1.5 · 12/9/21

Cá ngừ F1 đã viết:
Ổn rồi ạ.
Mà có thể anh @excel_lv1.5 có thêm cách khác. Vì anh có đẩy cái table vào query để làm tham chiếu.

Ý tôi là vầy, chắc cũng giống bạn @ptm0412 , ví dụ có dữ liệu from date, to date, tôi sẽ convert nó thành table bấm Ctrl+T , rồi trong M tôi gọi nó ra thôi

Cá ngừ F1 · 12/9/21

Rất xin lỗi thành viên @maixuanvuong276 vì có những trao đổi hơi nhiều về Topic này, thực tế cũng muốn có một giải pháp tốt hơn về việc tổng hợp này của bạn.
Tôi thử làm lại bằng Power Query.
Bản giải nén File đính kèm về máy (để File TongHop và folder Source tại cùng 1 thư mục).
Giả sử các dữ liệu bạn cần phải tổng hợp ở trong Source (tôi có tạo 4 file Data như cấu trúc bạn gửi ở bài #1), tôi nghĩ rằng các file dữ liệu này cần tách hẳn ra thành File riêng, còn lúc tổng hợp sẽ làm ở 1 File khác.
1. Bạn mở File TongHop
2. Ở sheet Setting, ô bôi đỏ là để chọn tương ứng các File bạn cần tổng hợp.
3. Sau khi chọn File nguồn, sang sheet TongHop, click phải chuột chọn Refresh.

maixuanvuong276 · 13/9/21

Cá ngừ F1 đã viết:
Rất xin lỗi thành viên @maixuanvuong276 vì có những trao đổi hơi nhiều về Topic này, thực tế cũng muốn có một giải pháp tốt hơn về việc tổng hợp này của bạn.
Tôi thử làm lại bằng Power Query.
Bản giải nén File đính kèm về máy (để File TongHop và folder Source tại cùng 1 thư mục).
Giả sử các dữ liệu bạn cần phải tổng hợp ở trong Source (tôi có tạo 4 file Data như cấu trúc bạn gửi ở bài #1), tôi nghĩ rằng các file dữ liệu này cần tách hẳn ra thành File riêng, còn lúc tổng hợp sẽ làm ở 1 File khác.
1. Bạn mở File TongHop
2. Ở sheet Setting, ô bôi đỏ là để chọn tương ứng các File bạn cần tổng hợp.
3. Sau khi chọn File nguồn, sang sheet TongHop, click phải chuột chọn Refresh.

tks bạn rất nhiều!

Tổng hợp dữ liệu từ nhiều sheet (1 người xem)

Người dùng đang xem chủ đề này

Thành viên mới

File đính kèm

|||||||||||||

Thành viên gạo cội

Thành viên mới

( ͡° ͜ʖ ͡°)

File đính kèm

Thành viên gạo cội

Thành viên mới

Zalo 0986997214

I don't program, I beat code into submission!!!

Thành viên gạo cội

Thành viên mới

File đính kèm

( ͡° ͜ʖ ͡°)

I don't program, I beat code into submission!!!

Thành viên mới

File đính kèm

( ͡° ͜ʖ ͡°)

I don't program, I beat code into submission!!!

Thành viên thường trực

I don't program, I beat code into submission!!!

Zalo 0986997214

File đính kèm

I don't program, I beat code into submission!!!

Thành viên gạo cội

Thành viên mới

File đính kèm

I don't program, I beat code into submission!!!

( ͡° ͜ʖ ͡°)

I don't program, I beat code into submission!!!

( ͡° ͜ʖ ͡°)

File đính kèm

Bad Excel Member

Bad Excel Member

Thành viên tiêu biểu

I don't program, I beat code into submission!!!

Bad Excel Member

I don't program, I beat code into submission!!!

( ͡° ͜ʖ ͡°)

Thành viên tiêu biểu

I don't program, I beat code into submission!!!

Thành viên gạo cội

I don't program, I beat code into submission!!!

Bad Excel Member

Thành viên gắn bó

Thành viên tích cực

( ͡° ͜ʖ ͡°)

Thành viên gắn bó

Thành viên tích cực

( ͡° ͜ʖ ͡°)

Bad Excel Member

Thành viên tích cực

Bad Excel Member

Thành viên tích cực

Thành viên gắn bó

Bad Excel Member

( ͡° ͜ʖ ͡°)

Bad Excel Member

( ͡° ͜ʖ ͡°)

Thành viên tích cực

( ͡° ͜ʖ ͡°)

File đính kèm

Thành viên mới

Thời gian đếm ngược.