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

Blue Softs Liên hệ QC

hoahuongduong1986

Thành viên thường trực
Tham gia
14/11/18
Bài viết
344
Được thích
37
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: 43

Cá ngừ F1

( ͡° ͜ʖ ͡°)
Thành viên BQT
Moderator
Tham gia
1/1/08
Bài viết
2,492
Được thích
3,606
Donate (Momo)
Donate
Giới tính
Nam
Nghề nghiệp
Quan hệ.. và quan hệ..
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

Cá ngừ F1

( ͡° ͜ʖ ͡°)
Thành viên BQT
Moderator
Tham gia
1/1/08
Bài viết
2,492
Được thích
3,606
Donate (Momo)
Donate
Giới tính
Nam
Nghề nghiệp
Quan hệ.. và quan hệ..
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

le070802

Thành viên mới
Tham gia
14/9/21
Bài viết
12
Được thích
2
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: 5
Upvote 0

concogia

Gội rồi mới Cạo
Tham gia
17/9/09
Bài viết
3,408
Được thích
6,843
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

hoahuongduong1986

Thành viên thường trực
Tham gia
14/11/18
Bài viết
344
Được thích
37
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: 6
Upvote 0

concogia

Gội rồi mới Cạo
Tham gia
17/9/09
Bài viết
3,408
Được thích
6,843
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: 18
Upvote 0

hoahuongduong1986

Thành viên thường trực
Tham gia
14/11/18
Bài viết
344
Được thích
37
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

HUONGHCKT

Thành viên tiêu biểu
Tham gia
30/8/12
Bài viết
455
Được thích
602
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: 14
Upvote 0

HieuCD

Chuyên gia GPE
Tham gia
14/9/10
Bài viết
8,494
Được thích
17,834
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

Cá ngừ F1

( ͡° ͜ʖ ͡°)
Thành viên BQT
Moderator
Tham gia
1/1/08
Bài viết
2,492
Được thích
3,606
Donate (Momo)
Donate
Giới tính
Nam
Nghề nghiệp
Quan hệ.. và quan hệ..
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

Maika8008

Thành viên gắn bó
Tham gia
12/6/20
Bài viết
2,550
Được thích
2,819
Donate (Momo)
Donate
Giới tính
Nam
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

Cá ngừ F1

( ͡° ͜ʖ ͡°)
Thành viên BQT
Moderator
Tham gia
1/1/08
Bài viết
2,492
Được thích
3,606
Donate (Momo)
Donate
Giới tính
Nam
Nghề nghiệp
Quan hệ.. và quan hệ..
Upvote 0

ptm0412

Bad Excel Member
Thành viên BQT
Super Moderator
Tham gia
4/11/07
Bài viết
11,620
Được thích
32,646
Donate (Momo)
Donate
Giới tính
Nam
Nghề nghiệp
Consultant
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: 12
Upvote 0

ptm0412

Bad Excel Member
Thành viên BQT
Super Moderator
Tham gia
4/11/07
Bài viết
11,620
Được thích
32,646
Donate (Momo)
Donate
Giới tính
Nam
Nghề nghiệp
Consultant
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

hoahuongduong1986

Thành viên thường trực
Tham gia
14/11/18
Bài viết
344
Được thích
37
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

ptm0412

Bad Excel Member
Thành viên BQT
Super Moderator
Tham gia
4/11/07
Bài viết
11,620
Được thích
32,646
Donate (Momo)
Donate
Giới tính
Nam
Nghề nghiệp
Consultant
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
Top Bottom