Tính toán và đếm hàng hóa xuất nhập khẩu dùng Dictionary

Liên hệ QC

hoahuongduong1986

Thành viên thường trực
Tham gia
14/11/18
Bài viết
346
Được thích
40
Kính gửi anh chị,
Em mới tham khảo và làm được code lọc cột C, D, E tại sheet DIC.
=> Nhờ a chị và các bạn hỗ trợ tính, đếm thêm cột F và G như ví dụ em có làm cho mã WLD với điều kiện trường Reporter ISO tại sheet Data bỏ toàn bộ các khách mã có ký tự bắt đầu bằng chữ A và kết quả xuất ra theo thứ tự lớn giảm dần của tổng Trade Value (US$). Em cảm ơn ạ !
 

File đính kèm

  • Tinh toan XNK - DICTIONARY.xlsm
    1.7 MB · Đọc: 46
Sao không thử dùng Power Query bạn nhỉ? Có đếm, có tính tổng, có cả sắp xếp luôn.
 
Upvote 0
Sao bạn không làm báo cáo kiểu như thế này? sẽ dễ dàng cho việc thống kê, tính toán (và khả năng bạn cũng sẽ tự code được luôn)

Snag_31917.png
 
Upvote 0
mọi người cho em hỏi bài 2 sheet Index và 3 cột cuối của sheet bài 3 với ạ. Em mới tham gia nên chưa biết đăng bài mà giờ em cần gấp huhu
 

File đính kèm

  • LT1_30_Lê Thị Thảo.xlsx
    70.1 KB · Đọc: 6
Upvote 0
Sao bạn không làm báo cáo kiểu như thế này? sẽ dễ dàng cho việc thống kê, tính toán (và khả năng bạn cũng sẽ tự code được luôn)

View attachment 266143
Theo bạn í, hình như còn điều kiện trong cột Reporter ISO bỏ toàn bộ các khách mã có ký tự bắt đầu bằng chữ A nữa mà, nếu vậy cột tổng Trade Value (US$) ra đâu đúng phải không em
Thí dụ của bạn í hình như cũng không đúng
 
Upvote 0
Theo bạn í, hình như còn điều kiện trong cột Reporter ISO bỏ toàn bộ các khách mã có ký tự bắt đầu bằng chữ A nữa mà, nếu vậy cột tổng Trade Value (US$) ra đâu đúng phải không em
Thí dụ của bạn í hình như cũng không đúng
Ví dụ của em là em mới làm được code tính cho tất cả thôi ạ. Em chưa biết loại bỏ nếu dùng DIC ạ.
Bài đã được tự động gộp:

mọi người cho em hỏi bài 2 sheet Index và 3 cột cuối của sheet bài 3 với ạ. Em mới tham gia nên chưa biết đăng bài mà giờ em cần gấp huhu
Của bạn đây nhé. Lần sau đăng bài mới nhé, đừng xen vào bài tớ như thế.he
 

File đính kèm

  • LT1_30_Lê Thị Thảo (2).xlsx
    71 KB · Đọc: 8
Upvote 0
Kính gửi anh chị,
Em mới tham khảo và làm được code lọc cột C, D, E tại sheet DIC.
=> Nhờ a chị và các bạn hỗ trợ tính, đếm thêm cột F và G như ví dụ em có làm cho mã WLD với điều kiện trường Reporter ISO tại sheet Data bỏ toàn bộ các khách mã có ký tự bắt đầu bằng chữ A và kết quả xuất ra theo thứ tự lớn giảm dần của tổng Trade Value (US$). Em cảm ơn ạ !
Bài này làm xong mà kiểm tra được mình...chết liền, riêng số tiền thằng đầu tiên thì hoàn toàn chính xác, mấy thằng sau không biết. Bài gì mà ghê quá
Bạn kiểm tra nhé, có gì chưa ưng ý thì tự sửa
Trời, đang hỏi bài tranh thủ trả lời luôn, quá dữ
Thân, còn cái sắp xếp thì tự Sort đi nhé bạn
 

File đính kèm

  • Tinh toan XNK - DICTIONARY.xlsm
    1.8 MB · Đọc: 20
Upvote 0
Bài này làm xong mà kiểm tra được mình...chết liền, riêng số tiền thằng đầu tiên thì hoàn toàn chính xác, mấy thằng sau không biết. Bài gì mà ghê quá
Bạn kiểm tra nhé, có gì chưa ưng ý thì tự sửa
Trời, đang hỏi bài tranh thủ trả lời luôn, quá dữ
Thân, còn cái sắp xếp thì tự Sort đi nhé bạn
hi. Em cảm ơn Anh nhiều ạ !!! Em sẽ tự vọc vạch phần còn lại ạ.
 
Upvote 0
Kính gửi anh chị,
Em mới tham khảo và làm được code lọc cột C, D, E tại sheet DIC.
=> Nhờ a chị và các bạn hỗ trợ tính, đếm thêm cột F và G như ví dụ em có làm cho mã WLD với điều kiện trường Reporter ISO tại sheet Data bỏ toàn bộ các khách mã có ký tự bắt đầu bằng chữ A và kết quả xuất ra theo thứ tự lớn giảm dần của tổng Trade Value (US$). Em cảm ơn ạ !
Góp vui chống dịch. Xem thử xem.
 

File đính kèm

  • Tinh toan XNK - DICTIONARY (1).xlsm
    1.7 MB · Đọc: 17
Upvote 0
Kính gửi anh chị,
Em mới tham khảo và làm được code lọc cột C, D, E tại sheet DIC.
=> Nhờ a chị và các bạn hỗ trợ tính, đếm thêm cột F và G như ví dụ em có làm cho mã WLD với điều kiện trường Reporter ISO tại sheet Data bỏ toàn bộ các khách mã có ký tự bắt đầu bằng chữ A và kết quả xuất ra theo thứ tự lớn giảm dần của tổng Trade Value (US$). Em cảm ơn ạ !
Góp vui
Mã:
Sub XYZ()
  Dim dic As Scripting.Dictionary, sArr(), Res()
  Dim iKey$, sRow&, i&, k&, ik&, c&
 
  With Sheets("Data")
    sArr = .Range("D2", .Range("J" & Rows.Count).End(xlUp)).Value
  End With
  sRow = UBound(sArr)
  ReDim Res(1 To sRow, 1 To 11)
  Set dic = CreateObject("Scripting.Dictionary")
  dic.Add "Export", 4:        dic.Add "Import", 5
  dic.Add "Re-Export", 6:    dic.Add "Re-Import", 7
  For i = 1 To UBound(sArr)
    iKey = sArr(i, 5)
    If Not IsEmpty(iKey) Then
      If Mid(sArr(i, 3), 1, 1) <> "A" Then 'Loai Khach Hang "A.."
        If Not dic.Exists(iKey) Then
          k = k + 1
          dic.Add iKey, k
          Res(k, 1) = k
          Res(k, 2) = iKey
        End If
        ik = dic.Item(iKey)
        Res(ik, 3) = Res(ik, 3) + sArr(i, 7)
        c = dic.Item(sArr(i, 1))
        Res(ik, c) = Res(ik, c) + 1 'Dem So Luong
        Res(ik, c + 4) = Res(ik, c + 4) + sArr(i, 7) 'Tong gia tri
      End If
    End If
  Next i
  For i = 1 To k
    Res(i, 4) = "Export: " & Res(i, 4) & " /Import: " & Res(i, 5) & _
                "/Re-Export: " & Res(i, 6) & " /Re-Import: " & Res(i, 7)
    Res(i, 5) = "Export: " & Replace(Format(Res(i, 8), "#,###"), ",", ".") & _
                " /Import: " & Replace(Format(Res(i, 9), "#,###"), ",", ".") & _
                "/Re-Export: " & Replace(Format(Res(i, 10), "#,###"), ",", ".") & _
                " /Re-Import: " & Replace(Format(Res(i, 11), "#,###"), ",", ".")
  Next i
  Sheets("Dic").Range("C2").Resize(k, 5).Value = Res
End Sub
 
Upvote 0
Góp vui
Mã:
Sub XYZ()
  Dim dic As Scripting.Dictionary, sArr(), Res()
  Dim iKey$, sRow&, i&, k&, ik&, c&
 
  With Sheets("Data")
    sArr = .Range("D2", .Range("J" & Rows.Count).End(xlUp)).Value
  End With
  sRow = UBound(sArr)
  ReDim Res(1 To sRow, 1 To 11)
  Set dic = CreateObject("Scripting.Dictionary")
  dic.Add "Export", 4:        dic.Add "Import", 5
  dic.Add "Re-Export", 6:    dic.Add "Re-Import", 7
  For i = 1 To UBound(sArr)
    iKey = sArr(i, 5)
    If Not IsEmpty(iKey) Then
      If Mid(sArr(i, 3), 1, 1) <> "A" Then 'Loai Khach Hang "A.."
        If Not dic.Exists(iKey) Then
          k = k + 1
          dic.Add iKey, k
          Res(k, 1) = k
          Res(k, 2) = iKey
        End If
        ik = dic.Item(iKey)
        Res(ik, 3) = Res(ik, 3) + sArr(i, 7)
        c = dic.Item(sArr(i, 1))
        Res(ik, c) = Res(ik, c) + 1 'Dem So Luong
        Res(ik, c + 4) = Res(ik, c + 4) + sArr(i, 7) 'Tong gia tri
      End If
    End If
  Next i
  For i = 1 To k
    Res(i, 4) = "Export: " & Res(i, 4) & " /Import: " & Res(i, 5) & _
                "/Re-Export: " & Res(i, 6) & " /Re-Import: " & Res(i, 7)
    Res(i, 5) = "Export: " & Replace(Format(Res(i, 8), "#,###"), ",", ".") & _
                " /Import: " & Replace(Format(Res(i, 9), "#,###"), ",", ".") & _
                "/Re-Export: " & Replace(Format(Res(i, 10), "#,###"), ",", ".") & _
                " /Re-Import: " & Replace(Format(Res(i, 11), "#,###"), ",", ".")
  Next i
  Sheets("Dic").Range("C2").Resize(k, 5).Value = Res
End Sub
Vẫn chỉ là 1 đít sần. Quá hay ạ !!!
 
Upvote 0
Hoa hướng dương nên được mặt trời chiếu --=0

Hic, mấy bữa nay cái laptop bị loạn phím, phải dùng On-screen Keyboard, nên chẳng làm được gì cả
 
Upvote 0
Upvote 0
Bài này dùng Pivot table đơn giản cũng được, mà Power query cũng được. Nhờ dùng công cụ có sẵn nên phát hiện vô số dòng bị bỏ trống cột Partner ISO, trong khi code VBA nếu không cẩn thận sẽ bỏ qua
 

File đính kèm

  • Tinh toan XNK-PQ-Pivot.xlsm
    1.9 MB · Đọc: 15
Upvote 0
Power query thêm 2 cột nối chuỗi thì y hệt kết quả mong muốn
PHP:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source1= Table.SelectRows(Source, each Text.Start([Reporter ISO],1) <> "A"),
    Source2 = Table.SelectColumns(Source1,{"Partner ISO","Trade Flow","Trade Value (US$)"}),
    GroupBy1 = Table.Group(Source2, {"Partner ISO", "Trade Flow"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Total", each
 List.Sum([#"Trade Value (US$)"]), type number}}),
    AddTradeFlow = Table.AddColumn(GroupBy1, "TradeFlow2", each [Trade Flow] & "C"),
    PivotTable = Table.Pivot(AddTradeFlow,{"Import","Export","Re-Import","Re-Export"},"Trade Flow","Total"),
    PivotTable2 =  Table.Pivot(PivotTable,{"ImportC","ExportC","Re-ImportC","Re-ExportC"},"TradeFlow2","Count"),
    GroupBy2 = Table.Group(PivotTable2, {"Partner ISO"}, {{"SumImport", each List.Sum([Import]), type nullable number}, {"SumExport", each
 List.Sum([Export]), type nullable number}, {"SumReImport", each List.Sum([#"Re-Import"]), type nullable number}, {"SumReExport", each
 List.Sum([#"Re-Export"]), type nullable number}, {"CountImport", each List.Sum([ImportC]), type nullable number}, {"CountExport", each
 List.Sum([ExportC]), type nullable number}, {"CountReImport", each List.Sum([#"Re-ImportC"]), type nullable number}, {"CountReExport", 
each List.Sum([#"Re-ExportC"]), type nullable number}}),
    ReplaceNull = Table.ReplaceValue(GroupBy2,null,0,Replacer.ReplaceValue,{"SumImport", "SumExport", "SumReImport", "SumReExport",
 "CountImport", "CountExport", "CountReImport", "CountReExport"}),
    ExtraColumn1 = Table.AddColumn(ReplaceNull, "CountTradeFlow", each "Export: " & Text.From([CountExport]) & "; Import: " &
        Text.From([CountImport]) & "; Re-Export: " &     Text.From([CountReExport]) & "; Re-Import: " & Text.From([CountReImport])),
    ExtraColumn2 = Table.AddColumn(ExtraColumn1, "Total US$", each "Export: " & Text.From([SumExport]) & "; Import: " &
        Text.From([SumImport]) & "; Re-Export: " &     Text.From([SumReExport]) & "; Re-Import: " & Text.From([SumReImport])),
   SelectColumn = Table.SelectColumns(ExtraColumn2,{"Partner ISO","CountTradeFlow","Total US$"}),
    AddIndex = Table.AddIndexColumn(SelectColumn, "Index", 1, 1, Int64.Type),
    ReOrder = Table.ReorderColumns(AddIndex,{"Index", "Partner ISO", "CountTradeFlow", "Total US$"})
in
    ReOrder
1631764617633.png
 
Lần chỉnh sửa cuối:
Upvote 0
Hoa hướng dương nên được mặt trời chiếu --=0

Hic, mấy bữa nay cái laptop bị loạn phím, phải dùng On-screen Keyboard, nên chẳng làm được gì cả
Công nhận ạ, em không thấy anh Maika lại nghĩ chắc anh bận gì đó nên không thấy
Power query thêm 2 cột nối chuỗi thì y hệt kết quả mong muốn
PHP:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source1= Table.SelectRows(Source, each Text.Start([Reporter ISO],1) <> "A"),
    Source2 = Table.SelectColumns(Source1,{"Partner ISO","Trade Flow","Trade Value (US$)"}),
    GroupBy1 = Table.Group(Source2, {"Partner ISO", "Trade Flow"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Total", each
 List.Sum([#"Trade Value (US$)"]), type number}}),
    AddTradeFlow = Table.AddColumn(GroupBy1, "TradeFlow2", each [Trade Flow] & "C"),
    PivotTable = Table.Pivot(AddTradeFlow,{"Import","Export","Re-Import","Re-Export"},"Trade Flow","Total"),
    PivotTable2 =  Table.Pivot(PivotTable,{"ImportC","ExportC","Re-ImportC","Re-ExportC"},"TradeFlow2","Count"),
    GroupBy2 = Table.Group(PivotTable2, {"Partner ISO"}, {{"SumImport", each List.Sum([Import]), type nullable number}, {"SumExport", each
 List.Sum([Export]), type nullable number}, {"SumReImport", each List.Sum([#"Re-Import"]), type nullable number}, {"SumReExport", each
 List.Sum([#"Re-Export"]), type nullable number}, {"CountImport", each List.Sum([ImportC]), type nullable number}, {"CountExport", each
 List.Sum([ExportC]), type nullable number}, {"CountReImport", each List.Sum([#"Re-ImportC"]), type nullable number}, {"CountReExport",
each List.Sum([#"Re-ExportC"]), type nullable number}}),
    ReplaceNull = Table.ReplaceValue(GroupBy2,null,0,Replacer.ReplaceValue,{"SumImport", "SumExport", "SumReImport", "SumReExport",
 "CountImport", "CountExport", "CountReImport", "CountReExport"}),
    ExtraColumn1 = Table.AddColumn(ReplaceNull, "CountTradeFlow", each "Export: " & Text.From([CountExport]) & "; Import: " &
        Text.From([CountImport]) & "; Re-Export: " &     Text.From([CountReExport]) & "; Re-Import: " & Text.From([CountReImport])),
    ExtraColumn2 = Table.AddColumn(ExtraColumn1, "Total US$", each "Export: " & Text.From([SumExport]) & "; Import: " &
        Text.From([SumImport]) & "; Re-Export: " &     Text.From([SumReExport]) & "; Re-Import: " & Text.From([SumReImport])),
   SelectColumn = Table.SelectColumns(ExtraColumn2,{"Partner ISO","CountTradeFlow","Total US$"}),
    AddIndex = Table.AddIndexColumn(SelectColumn, "Index", 1, 1, Int64.Type),
    ReOrder = Table.ReorderColumns(AddIndex,{"Index", "Partner ISO", "CountTradeFlow", "Total US$"})
in
    ReOrder
View attachment 266187
hay quá anh ạ. Thế làm bằng ADO có được không ạ, kiểu như SQL ấy ạ. Em thấy một số anh chị cũng hay dùng ADO ạ.
 
Upvote 0
hay quá anh ạ. Thế làm bằng ADO có được không ạ, kiểu như SQL ấy ạ. Em thấy một số anh chị cũng hay dùng ADO ạ.
Chắc là làm được. Riêng bài này xài ADO không hay lắm, câu SQL sẽ khá phức tạp. Power query thì xé nhỏ sự phức tạp thành những steps.
 
Upvote 0
Web KT
Back
Top Bottom