Xin được giúp đỡ DÒ TÌM lấy danh sách tổng giá các đơn hàng (4 người xem)

LINHCM · 15/12/24

Em xin chào các anh chị quản trị Web: www.giaiphapexcel.com. Em chào các anh chị cộng đồng excel.
Em gặp vấn đề rất khó, xin nhờ được giúp đỡ như thế này:
Em có một danh sách các đơn hàng đồng giá theo ngày cho các mã hàng (qua mỗi ngày các mã hàng này sẽ có giá khác).
Em cần thống kê 2 danh sách:
Danh sách 1: Gồm tất cả các mã đơn hàng liệt kê trong một cột từ trên đổ xuống.
Danh sách 2: Tổng tiền ứng với mỗi mã đơn hàng.
Mong được sự giúp đỡ của anh chị.

SteveNguyen1991 · 15/12/24

Trường hợp này Dùng Power Query xử lý cũng được nhá!

LINHCM · 15/12/24

SteveNguyen1991 đã viết:
Trường hợp này Dùng Power Query xử lý cũng được nhá!

Dạ cảm ơn anh đề xuất cách mới. Nhưng em cần dùng 1 hàm nào đó, để kéo đổ từ trên xuống nó hiện ra các list mã đơn hàng và cột kết bên cũng kéo xuống tương tự nó hiện ra tổng giá.

Thóc Sama · 15/12/24

Dùng thử macro này xem

PHP:

Sub GPE()

Dim Dic As Object, Key, i&, Lr&, Arr(), Tmp
Dim k&, Res(1 To 10000, 1 To 2), Txt, a

Set Dic = CreateObject("Scripting.Dictionary")
With Sheets("Sheet1")
    Lr = .Range("C" & Rows.Count).End(xlUp).Row
    Arr = .Range("C4:D" & Lr).Value
    For i = 1 To UBound(Arr)
        Txt = Replace(Arr(i, 1), " ", ""): Tmp = Split(Txt, ",")
        For a = LBound(Tmp) To UBound(Tmp)
            Key = Tmp(a)
            If Not Dic.exists(Key) Then
                k = k + 1: Dic.Add (Key), k
                Res(k, 1) = Key
                Res(k, 2) = Arr(i, 2)
            Else
                Res(Dic.Item(Key), 2) = Res(Dic.Item(Key), 2) + Arr(i, 2)
            End If
        Next a
    Next i
    .Range("J4:K10000").ClearContents
    If k Then
        .Range("J4").Resize(k, 2).Value = Res
        MsgBox "Done"
    End If
End With

Set Dic = Nothing
End Sub

LINHCM · 15/12/24

Thóc Sama đã viết:

Dùng thử macro này xem

PHP:

Sub GPE()

Dim Dic As Object, Key, i&, Lr&, Arr(), Tmp
Dim k&, Res(1 To 10000, 1 To 2), Txt, a

Set Dic = CreateObject("Scripting.Dictionary")
With Sheets("Sheet1")
    Lr = .Range("C" & Rows.Count).End(xlUp).Row
    Arr = .Range("C4:D" & Lr).Value
    For i = 1 To UBound(Arr)
        Txt = Replace(Arr(i, 1), " ", ""): Tmp = Split(Txt, ",")
        For a = LBound(Tmp) To UBound(Tmp)
            Key = Tmp(a)
            If Not Dic.exists(Key) Then
                k = k + 1: Dic.Add (Key), k
                Res(k, 1) = Key
                Res(k, 2) = Arr(i, 2)
            Else
                Res(Dic.Item(Key), 2) = Res(Dic.Item(Key), 2) + Arr(i, 2)
            End If
        Next a
    Next i
    .Range("J4:K10000").ClearContents
    If k Then
        .Range("J4").Resize(k, 2).Value = Res
        MsgBox "Done"
    End If
End With

Set Dic = Nothing
End Sub

Em cảm ơn anh rất nhiều. Trên cả mong đợi luôn. Có những dòng code thật kì diệu quá.

longtay1111 · 15/12/24

LINHCM đã viết:
Em xin chào các anh chị quản trị Web: www.giaiphapexcel.com. Em chào các anh chị cộng đồng excel.
Em gặp vấn đề rất khó, xin nhờ được giúp đỡ như thế này:
Em có một danh sách các đơn hàng đồng giá theo ngày cho các mã hàng (qua mỗi ngày các mã hàng này sẽ có giá khác).
Em cần thống kê 2 danh sách:
Danh sách 1: Gồm tất cả các mã đơn hàng liệt kê trong một cột từ trên đổ xuống.
Danh sách 2: Tổng tiền ứng với mỗi mã đơn hàng.
Mong được sự giúp đỡ của anh chị.View attachment 306306

Bạn có thể dùng công thức đối với bài này

G4= SORT(UNIQUE(TRIM(MID(SUBSTITUTE(CONCAT($C$4:$C$7&", "),", ",REPT(" ",100)),(ROW($1:$11)-1)*100+1,100))))

H4= SUMPRODUCT((ISNUMBER(SEARCH(G4,$C$4:$C$7)))*($D$4:$D$7))

Thân

LINHCM · 15/12/24

longtay1111 đã viết:
Bạn có thể dùng công thức đối với bài này

G4= SORT(UNIQUE(TRIM(MID(SUBSTITUTE(CONCAT($C$4:$C$7&", "),", ",REPT(" ",100)),(ROW($1:$11)-1)*100+1,100))))

H4= SUMPRODUCT((ISNUMBER(SEARCH(G4,$C$4:$C$7)))*($D$4:$D$7))

Thân

Cảm ơn anh nhiều nhé!

yeudoi · 16/12/24

LINHCM đã viết:
Em xin chào các anh chị quản trị Web: www.giaiphapexcel.com. Em chào các anh chị cộng đồng excel.
Em gặp vấn đề rất khó, xin nhờ được giúp đỡ như thế này:
Em có một danh sách các đơn hàng đồng giá theo ngày cho các mã hàng (qua mỗi ngày các mã hàng này sẽ có giá khác).
Em cần thống kê 2 danh sách:
Danh sách 1: Gồm tất cả các mã đơn hàng liệt kê trong một cột từ trên đổ xuống.
Danh sách 2: Tổng tiền ứng với mỗi mã đơn hàng.
Mong được sự giúp đỡ của anh chị.View attachment 306306

Nếu Bạn dùng Google Sheets thì cũng có cách chỉ 1 công thức ra kết quả cả 2 cột

Nguyễn Duy Tuân · 16/12/24

Tôi cung cấp thêm một cách làm nữa bằng công thức Excel 365. Cách làm này nếu danh sách ở cột C mã có mã lập lại công thức sẽ tính nhân theo số lần lặp,
Ví dụ nếu C4 là 115, 113, 111, 112, 114, 112 ứng với D4 là 500
C5 là 112 ứng với D5 là 200
Nếu tổng cho mã 112 kết quả sẽ là 1200
(Đây là tôi cố tình giả định để công thức tính cả tình huống đó)

Cách làm như sau:
+ Tại G4, công thức là:
=SORT( UNIQUE(TRIM(TEXTSPLIT( TEXTJOIN(",",,C4:C7),,","))))

+ Tại H4 công thức là:
=SUM(INT((LEN($C$4:$C$7)-LEN(SUBSTITUTE($C$4:$C$7,G4,"")))/LEN(G4))*$D$4:$D$7)

hoangminhtien · 16/12/24

longtay1111 đã viết:
Bạn có thể dùng công thức đối với bài này

G4= SORT(UNIQUE(TRIM(MID(SUBSTITUTE(CONCAT($C$4:$C$7&", "),", ",REPT(" ",100)),(ROW($1:$11)-1)*100+1,100))))

H4= SUMPRODUCT((ISNUMBER(SEARCH(G4,$C$4:$C$7)))*($D$4:$D$7))

Thân

Công thức cột H của bạn thì được rồi, nhưng công thức cột G có 2 điểm cần cải thiện 1 chút.
- cái ROW($1:$11) thì cái 11 hiện tại đang phải đếm, nếu tổng các ngày mà nhiều hơn 11 mã hàng thì lại phải thay lại. => cái này thì dễ xử lý vì chỉ cần đếm số dấu cách +1 là xong.
- cái con số 100, ở bài này nếu tổng các mã hàng từ 34 trở lên thì công thức sẽ không còn đúng nữa (đang tính mỗi mã là 3 ký tự, 3*34=102>100).
Con số 100 này sẽ là khó khăn cho chúng ta lựa chọn, vì nếu chọn lớn thì khi có nhiều mã, chúng ta REPT lên thì giá trị sẽ vượt quá 32,767 ký tự, công thức sẽ báo lỗi, còn nếu chọn nhỏ thì sẽ bị tình trạng trên khi có nhiều mã hàng.

NT Ngoc Yen · 17/12/24

hoangminhtien đã viết:
Công thức cột H của bạn thì được rồi, nhưng công thức cột G có 2 điểm cần cải thiện 1 chút.
- cái ROW($1:$11) thì cái 11 hiện tại đang phải đếm, nếu tổng các ngày mà nhiều hơn 11 mã hàng thì lại phải thay lại. => cái này thì dễ xử lý vì chỉ cần đếm số dấu cách +1 là xong.
- cái con số 100, ở bài này nếu tổng các mã hàng từ 34 trở lên thì công thức sẽ không còn đúng nữa (đang tính mỗi mã là 3 ký tự, 3*34=102>100).
Con số 100 này sẽ là khó khăn cho chúng ta lựa chọn, vì nếu chọn lớn thì khi có nhiều mã, chúng ta REPT lên thì giá trị sẽ vượt quá 32,767 ký tự, công thức sẽ báo lỗi, còn nếu chọn nhỏ thì sẽ bị tình trạng trên khi có nhiều mã hàng.

Công thức tại G4:
G4=UNIQUE(SORT(FILTERXML(""&SUBSTITUTE(TEXTJOIN(",",,$C$4:$C$7),",","")&"","//i")))

LINHCM · 18/12/24

Em chân thành cảm ơn các anh chị rất nhiều. Em xin lỗi vì chưa nêu rõ em đang xài bản office 2019. Có cài thêm add in DFX. Nên hiện tại máy em không có hàm SORT. Chỗ H4 em làm như anh chị hướng dẫn thì chạy được. Còn chỗ G4 nó báo lỗi chỗ hàm SORT không có trên máy.

HieuCD · 18/12/24

LINHCM đã viết:
Em xin chào các anh chị quản trị Web: www.giaiphapexcel.com. Em chào các anh chị cộng đồng excel.
Em gặp vấn đề rất khó, xin nhờ được giúp đỡ như thế này:
Em có một danh sách các đơn hàng đồng giá theo ngày cho các mã hàng (qua mỗi ngày các mã hàng này sẽ có giá khác).
Em cần thống kê 2 danh sách:
Danh sách 1: Gồm tất cả các mã đơn hàng liệt kê trong một cột từ trên đổ xuống.
Danh sách 2: Tổng tiền ứng với mỗi mã đơn hàng.
Mong được sự giúp đỡ của anh chị.View attachment 306306

Với mã có 3 số

Mã:

G4 =IFERROR(AGGREGATE(15,6,ROW($111:$999)/MATCH("*"&ROW($111:$999)&"*",$C$4:$C$7,0)^0,ROW(A1)),"")
H4 =IF(G4="","",SUMIF($C$4:$C$7,"*"&G4&"*",$D$4:$D$7))

LINHCM · 18/12/24

HieuCD đã viết:
Với mã có 3 số

Mã:

G4 =IFERROR(AGGREGATE(15,6,ROW($111:$999)/MATCH("*"&ROW($111:$999)&"*",$C$4:$C$7,0),ROW(A1)),"") H4 =IF(G4="","",SUMIF($C$4:$C$7,"*"&G4&"*",$D$4:$D$7))

Em cảm ơn anh ạ! Code của anh chạy rất mượt và đúng như ý em mong đợi. Nhưng nó chỉ đúng cho mã có 3 số. Anh có thể sửa code lại có thể nhận các mã có 1 hoặc 2 chữ số được không ạ? Ví dụ như nhập mã 03, 50, 82 thì nó vẫn nhận diện được.

HieuCD · 18/12/24

LINHCM đã viết:
Em cảm ơn anh ạ! Code của anh chạy rất mượt và đúng như ý em mong đợi. Nhưng nó chỉ đúng cho mã có 3 số. Anh có thể sửa code lại có thể nhận các mã có 1 hoặc 2 chữ số được không ạ? Ví dụ như nhập mã 03, 50, 82 thì nó vẫn nhận diện được.View attachment 306378

Thêm hàm Text lấy 2 chữ số
=IFERROR(TEXT(AGGREGATE(15,6,ROW($1:$999)/MATCH("* "&TEXT(ROW($1:$999),"00")&",*"," "&$C$4:$C$7&",",0)^0,ROW(A1)),"00"),"")
nếu có 1 chữ số thì "2" và 2 chữ số như "02" không xử lý được

LINHCM · 18/12/24

HieuCD đã viết:
Thêm hàm Text lấy 2 chữ số
=IFERROR(TEXT(AGGREGATE(15,6,ROW($1:$999)/MATCH("* "&TEXT(ROW($1:$999),"00")&",*"," "&$C$4:$C$7&",",0),ROW(A1)),"00"),"")
nếu có 1 chữ số thì "2" và 2 chữ số như "02" không xử lý được

Dạ em cảm ơn anh. Nếu các mã đơn hàng em thống nhất gồm các số từ 0-999 (có thể là 1 hoặc 2 hoặc 3 số như là: 1,2,3 ...... 20,21,22 ...... 370,380,399) thì có thể có code chạy ra kết quả không anh? Hay là bất khả thi ạ!

HieuCD · 19/12/24

LINHCM đã viết:
Dạ em cảm ơn anh. Nếu các mã đơn hàng em thống nhất gồm các số từ 0-999 (có thể là 1 hoặc 2 hoặc 3 số như là: 1,2,3 ...... 20,21,22 ...... 370,380,399) thì có thể có code chạy ra kết quả không anh? Hay là bất khả thi ạ!

Công thức gọn hơn
=IFERROR(AGGREGATE(15,6,ROW($1:$999)/MATCH("* "&ROW($1:$999)&",*"," "&$C$4:$C$23&",",0)^0,ROW(A1)),"")

longtay1111 · 19/12/24

LINHCM đã viết:
Dạ em cảm ơn anh. Nếu các mã đơn hàng em thống nhất gồm các số từ 0-999 (có thể là 1 hoặc 2 hoặc 3 số như là: 1,2,3 ...... 20,21,22 ...... 370,380,399) thì có thể có code chạy ra kết quả không anh? Hay là bất khả thi ạ! View attachment 306380

Bài này mình khuyên bạn nên dùng Power Quenry là chuẩn bài nhất, hoặc dùng Excel đời cao như 365 chẳng hạn. Chứ bài này mà dùng Excel đời thấp như Excel 2016 chẳng hạn thì vẫn có thể làm được, nhưng phải xử lý công thức mảng rất nặng, nếu dữ liệu nhiều( chắc chắn thực tế dữ liệu sẽ nhiều hơn nhiều so với đầu bài bạn yêu cầu), khi đó sẽ làm máy của bạn đơ luôn đấy.

Thân

LINHCM · 19/12/24

HieuCD đã viết:
Công thức gọn hơn
=IFERROR(AGGREGATE(15,6,ROW($1:$999)/MATCH("* "&ROW($1:$999)&",*"," "&$C$4:$C$23&",",0)^0,ROW(A1)),"")

Dạ em cảm ơn anh ạ. Mà cái cột mã hàng (G4) thì đúng rồi nè anh. Nhưng cái cột tổng giá (H4) nó chưa có khớp dữ liệu. Nếu em nhập các mã hàng có 1 hoặc 2 chữ số thì nó tính không đúng ạ!

HieuCD · 19/12/24

LINHCM đã viết:
Dạ em cảm ơn anh ạ. Mà cái cột mã hàng (G4) thì đúng rồi nè anh. Nhưng cái cột tổng giá (H4) nó chưa có khớp dữ liệu. Nếu em nhập các mã hàng có 1 hoặc 2 chữ số thì nó tính không đúng ạ!

Chỉnh lại . . .
=SUMPRODUCT(IFERROR(SEARCH("* "&G4&",*"," "&$C$4:$C$23&","),0)*$D$4:$D$23)

Xin được giúp đỡ DÒ TÌM lấy danh sách tổng giá các đơn hàng (4 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 hoạt động

File đính kèm

Thành viên mới

＿/＿/＿/＿/＿/＿/＿/

File đính kèm

Thành viên mới

Thành viên hoạt động

Thành viên mới

Thành viên gắn bó

Nghị Hách

Thành viên gắn bó

Thành viên chính thức

Thành viên mới

Chuyên gia GPE

Thành viên mới

File đính kèm

Chuyên gia GPE

Thành viên mới

File đính kèm

Chuyên gia GPE

Thành viên hoạt động

Thành viên mới

Chuyên gia GPE

Đếm ngược thời gian