Đếm số lần xuất hiện trong khoảng,tính tổng của khoảng đó và phần trăm tương ứng (1 người xem)

[thufpts]

Chào các bác !
em có một file trước đó em được các bác GPE giúp đỡ rất ổn, nhưng trong lúc làm việc lại phát sinh thêm 1 vấn đề rất cần sự giúp đỡ vì nó quá phức tạp như sau. trong file đính kèm có 2 sheet là Summary và Percentage.
Tại sheet Percentage cần phải tính toán các cột Order, Qty, MRH và %.

Cột Order là kết quả đếm từ cột G sheet Summary theo 3 điều kiện là theo Buyer và Fty_product_type và cột G sheet có giá trị nằm trong khoảng từ 1 đến 2. bên sheet Percentage em có đặt 6 khoảng là 1-2,
3-6, 7-12, 13-18, 19-24, 25-31. Mỗi 1 tháng bên sheet Summary sẽ được phân tích thành 6 khoảng dựa vào cột G, K,O,S,W,AA,AE,AI,AM,AQ,AU,AY.
Tại ô có giá trị Month em để là 1 có nghĩa là lấy tháng 1 bên sheet Summary phân tích thành 6 khoảng như em đã trình bày, tương tự nếu để 2 sẽ lấy tháng 2 nếu đển 3 sẽ lấy tháng 3 cho đến hết tháng 12.

Cột Qty là kết quả tính tổng từ cột D bên sheet Summary điều kiện cũng là theo khoảng Buyer và Fty_product_type
Cột MRH là kết quả tính tổng từ cột E bên sheet Summary điều kiện cũng là theo khoảng Buyer và Fty_product_type
Cột % là kết phần trăm trả về theo cột Order và Buyer ví dụ buyer Books Total có 54 đơn hàng là 100%
chia nhỏ cho từng Fty_product_type là bottom có 25 đơn hàng thì tỷ lệ % sẽ bằng 25*100%/54.

Cột Total chỉ là sum lại thôi nhưng total sẽ không nằm cố định mà nó sẽ thay đổi dòng.
Hiện tại thì em đang viết công thức cho cột Order nhưng em viết bị sai mà công thức của em nó dài lắm nên chỉ lấy để trình bày cho các bác hiểu, Chối nhất là cột tính tổng vì bên sheet Summary nó chạy từ hệ thống ra bằng table query nên định dạng nó không phải là số đổi nó thành số thì khi chạy lại nó lại ra text em nản vô cùng.
Rất mong các bác giúp em

 

[Ba Tê]

Hiện tại thì em đang viết công thức cho cột Order nhưng em viết bị sai

Công thức bị sai thì ai biết kết quả đúng là sao? Sao bạn không nhập kết quả bạn tính toán bằng thủ công xem kết quả thế nào rồi giải thích cách tính?
Kết quả trong hình có thật là đúng với dữ liệu trong file bạn gởi không?
Tôi chỉ hiểu "mang máng" thế này, bạn kiểm tra lại kết quả xem sao.
Xóa bỏ sheet không liên quan, Dữ liệu đã chuyển thành Value, không còn công thức cho nhẹ file.
Thay đổi ô B1.

 

[quocgiacan]

Chào các bác !
em có một file trước đó em được các bác GPE giúp đỡ rất ổn, nhưng trong lúc làm việc lại phát sinh thêm 1 vấn đề rất cần sự giúp đỡ vì nó quá phức tạp như sau. trong file đính kèm có 2 sheet là Summary và Percentage.
Tại sheet Percentage cần phải tính toán các cột Order, Qty, MRH và %.

Cột Order là kết quả đếm từ cột G sheet Summary theo 3 điều kiện là theo Buyer và Fty_product_type và cột G sheet có giá trị nằm trong khoảng từ 1 đến 2. bên sheet Percentage em có đặt 6 khoảng là 1-2,
3-6, 7-12, 13-18, 19-24, 25-31. Mỗi 1 tháng bên sheet Summary sẽ được phân tích thành 6 khoảng dựa vào cột G, K,O,S,W,AA,AE,AI,AM,AQ,AU,AY.
Tại ô có giá trị Month em để là 1 có nghĩa là lấy tháng 1 bên sheet Summary phân tích thành 6 khoảng như em đã trình bày, tương tự nếu để 2 sẽ lấy tháng 2 nếu đển 3 sẽ lấy tháng 3 cho đến hết tháng 12.

Cột Qty là kết quả tính tổng từ cột D bên sheet Summary điều kiện cũng là theo khoảng Buyer và Fty_product_type
Cột MRH là kết quả tính tổng từ cột E bên sheet Summary điều kiện cũng là theo khoảng Buyer và Fty_product_type
Cột % là kết phần trăm trả về theo cột Order và Buyer ví dụ buyer Books Total có 54 đơn hàng là 100%
chia nhỏ cho từng Fty_product_type là bottom có 25 đơn hàng thì tỷ lệ % sẽ bằng 25*100%/54.

Cột Total chỉ là sum lại thôi nhưng total sẽ không nằm cố định mà nó sẽ thay đổi dòng.
Hiện tại thì em đang viết công thức cho cột Order nhưng em viết bị sai mà công thức của em nó dài lắm nên chỉ lấy để trình bày cho các bác hiểu, Chối nhất là cột tính tổng vì bên sheet Summary nó chạy từ hệ thống ra bằng table query nên định dạng nó không phải là số đổi nó thành số thì khi chạy lại nó lại ra text em nản vô cùng.
Rất mong các bác giúp em

View attachment 182414

Nhận xét qua công thức bạn thực hiện trong file bạn gửi, tôi thấy bạn tự thân cố gắng không ỷ lại vào người khác, nên tôi rất vui khi được hỗ trợ bạn.
Mặc dù tôi đã chỉnh lại các công thức theo yêu cầu mới của bạn, nhưng do đặc điểm file nguồn cung cấp dữ liệu, như bạn bảo là từ SQL gì gì đó, cấu thành cho các cột Qty, MHR theo định dạng là 'Text' cho nên dùng công thức thường không tính toán được, phải sử dụng bằng công thức 'Mảng'. Nếu dữ liệu in ít thì không sao, nhưng nếu dữ liệu thực tế của bạn nhiều, công thức 'Mảng' sẽ làm ảnh hưởng đến tốc độ xử lý của bảng tính. Vì vậy, một lời khuyên chân thành, bạn nên chọn giải pháp VBA là xử lý tốt nhất cho bảng tính của bạn.
Qua tiếp xúc bảng tính của bạn từ bài trước gần đây, nay với yêu cầu mới, cùng phân tích công thức bạn đặt trong file tôi sẽ trình bày lại để anh em khác 'chuyên' về VBA nếu có lòng sẽ dựa vào đó để nắm tường tận ý, và hoàn thành hỗ trợ bạn. Giải thích dưới đây tập trung vào cách lấy dữ liệu từ bảng 'Summary' mang kết quả cho bảng 'Percentage'

1. Cột Order (C3:C84): Trích lục và Đếm có bao nhiêu đơn hàng thỏa các điều kiện sau:
   • Cùng một 'Buyer': tức so sánh cột A sheet 'Summary' với cột A sheet 'Percentage'
   • Cùng một 'fty_product_type': tức so sánh cột C sheet 'Summary' với cột B sheet 'Percentage'
   • Điều kiện 'Produce_day' sheet 'Summary' phải nằm trong khoảng được chỉ định tiêu chí tại hàng 2 của sheet 'Percentage', Vd: "1-2"--> tức nằm trong khoảng từ 1 đến 2; "3-6"--> từ 3 đến 6; "7-12"--> từ 7 đến 12......, Vd: Nếu Sheet 'Percentage!B1= Tháng 1, thì cột G của sheet 'Summary' sẽ được chọn để xét các tiêu chí: "1-2"; "3-6"; "7-12"....
     
   • Với các dòng có chữ "Total" đằng sau từng 'Buyer' riêng biệt thì cộng lại các kết quả 'Order' phía trên cùng một 'Buyer', Vd: 'Brooks Total' sẽ thể hiện số tổng của các Brooks phía trên (~SUM(C3:C8))
   • Khi Sheet 'Percentage!B1 thay đổi tháng, Vd: 2 (tháng 2), thì trích lục sheet 'Summary' theo cột tương ứng đã báo tại hàng 1: 'Month' 1,2,3,4,5,6,7,8,9,10,11,12, trong Vd là tháng 2 thì nó là cột K, tháng 3 thì cột O.....
2. Cột Qty (D3: D84): tương tự như cột Order, nhưng Trích lục và Cộng dữ liệu thỏa điều kiện (giống như trên) trong sheet 'Summary' và trả kết quả vào cột D sheet 'Percentage'. Cũng thay đổi theo tháng, tức tháng 1 lấy từ cột D; tháng 2: cột H; tháng 3: cột L......
3. Cột MHR (E3:E84): giống cách thức lấy kết quả của cột Qty (Trích lục và Cộng)
4. Cột % (F3:F84): là tỷ lệ % của số Order từng mục so với tổng số Order của mục đó. Vd: Brooks/Zipper Jacket có số Order: 2, tính trên tổng số của Brooks: 3, thì bằng (2:3)%

Các cột từ C đến F, thuộc khoảng điều kiện 'Produce_day' từ 1 đến 2, các cột tương ứng phía sau giống cách thức truy lục và đếm hay cộng như các cột C:F nhưng 'điều kiện' khoảng thay đổi: "3-6"; "7-12".....

Trên đây là tóm tắt cách bạn muốn yêu cầu, nếu có chỗ nào chưa thật đúng ý bạn, bạn nên ghi ra tường tận.

Về mặt công thức, bạn không nên sử dụng Vùng theo kiểu toàn bộ cột, Vd: Sum(A:A,A2,B:B), vì như vậy excel phải chạy hết cả triệu dòng thì làm cho máy bạn trì trệ thêm, nên sử dụng trong khoảng mà dữ liệu tồn tại, Vd: dòng dữ liệu bạn có mới đến dòng 1169, thì dùng rõ như Sum(A3:A1169,A2,B3:B1169). Vì lý do đó, nên tôi có tạo 'name' để: tạo Vùng lấy theo thực tế của bạn, cùng giản lược bớt tránh làm rối mắt khi tra cứu công thức trong bảng, gồm:

1. VungBuyer=OFFSET(Summary!$A$4,,,COUNTA(Summary!$A$3:$A$5000))
2. VungFty=OFFSET(Summary!$C$4,,,COUNTA(Summary!$A$3:$A$5000))
3. VungOrder=OFFSET(Summary!$G$4,,(Percentage!$B$1-1)*4,COUNTA(Summary!$A$3:$A$5000)) ; Vùng này vừa thay đổi theo dòng thực tế, vừa chọn cột phù hợp theo điều kiện tháng thay đổi.
4. VungQty=OFFSET(Summary!$D$4,,(Percentage!$B$1-1)*4,COUNTA(Summary!$A$3:$A$5000)) ; tương tự Vùng Order
5. VungMHR=OFFSET(Summary!$E$4,,(Percentage!$B$1-1)*4,COUNTA(Summary!$A$3:$A$5000)) ; tương tự Vùng Order
6. Dieukien=TRIM(MID(SUBSTITUTE(LOOKUP("zzz",Percentage!$C$2:F$2),"-",REPT(" ",10)),{1,11},10)) --> Nhằm tạo 'Mảng' điều kiện, Vd: ứng với khoảng nằm trong chuỗi "1-2" thì nó sẽ cho ra 'Mảng' {1,2}; khoảng nằm trong chuỗi "3-6" thì nó sẽ cho ra 'Mảng' {3,6}; khoảng nằm trong chuỗi "7-12" thì nó sẽ cho ra 'Mảng' {7,12}....Điều kiện này nằm trong biểu thức câu lệnh: (VungOrder>=DieuKien+{0,0.1}) tức ý hãy lấy Vùng Order so >= với 'Mảng' Điều kiện cộng thêm mức sau 0.01, Vd: {1,2} thì thành 'Mảng' {1,2.01}

Công thức của bạn lấy trực tiếp cột theo điều kiện, nên sẽ lặp đi lặp lại đoạn các điều kiện, như:
Tháng 1 thì: COUNTIFS(Summary!$C:$C,Percentage!$B8,Summary!$G:$G,">=1")-COUNTIFS(Summary!$C:$C,Percentage!$B8,Summary!$G:$G,">2")
Tháng 2 thì: COUNTIFS(Summary!$C:$C,Percentage!$B3,Summary!$K:$K,">=1")-COUNTIFS(Summary!$C:$C,Percentage!$B3,Summary!$K:$K,">2")
Tháng 3 thì: COUNTIFS(Summary!$C:$C,Percentage!$B3,Summary!$O:$O,">=1")-COUNTIFS(Summary!$C:$C,Percentage!$B3,Summary!$O:$O,">2")
Chỉ khác nhau các cột 'điều kiện trong khoảng', chính vì vậy làm cho công thức 'rậm rạp' , Bạn hãy tham khảo công thức tôi gửi, từ từ tìm hiểu thì về sau nếu có trường hợp tương tự bạn cũng có thể tự thân giải quyết.

Bạn tham khảo file kèm.

Chúc bạn ngày vui.

 

[thufpts]

Nhận xét qua công thức bạn thực hiện trong file bạn gửi, tôi thấy bạn tự thân cố gắng không ỷ lại vào người khác, nên tôi rất vui khi được hỗ trợ bạn.
Mặc dù tôi đã chỉnh lại các công thức theo yêu cầu mới của bạn, nhưng do đặc điểm file nguồn cung cấp dữ liệu, như bạn bảo là từ SQL gì gì đó, cấu thành cho các cột Qty, MHR theo định dạng là 'Text' cho nên dùng công thức thường không tính toán được, phải sử dụng bằng công thức 'Mảng'. Nếu dữ liệu in ít thì không sao, nhưng nếu dữ liệu thực tế của bạn nhiều, công thức 'Mảng' sẽ làm ảnh hưởng đến tốc độ xử lý của bảng tính. Vì vậy, một lời khuyên chân thành, bạn nên chọn giải pháp VBA là xử lý tốt nhất cho bảng tính của bạn.
Qua tiếp xúc bảng tính của bạn từ bài trước gần đây, nay với yêu cầu mới, cùng phân tích công thức bạn đặt trong file tôi sẽ trình bày lại để anh em khác 'chuyên' về VBA nếu có lòng sẽ dựa vào đó để nắm tường tận ý, và hoàn thành hỗ trợ bạn. Giải thích dưới đây tập trung vào cách lấy dữ liệu từ bảng 'Summary' mang kết quả cho bảng 'Percentage'

1. Cột Order (C3:C84): Trích lục và Đếm có bao nhiêu đơn hàng thỏa các điều kiện sau:
   • Cùng một 'Buyer': tức so sánh cột A sheet 'Summary' với cột A sheet 'Percentage'
   • Cùng một 'fty_product_type': tức so sánh cột C sheet 'Summary' với cột B sheet 'Percentage'
   • Điều kiện 'Produce_day' sheet 'Summary' phải nằm trong khoảng được chỉ định tiêu chí tại hàng 2 của sheet 'Percentage', Vd: "1-2"--> tức nằm trong khoảng từ 1 đến 2; "3-6"--> từ 3 đến 6; "7-12"--> từ 7 đến 12......, Vd: Nếu Sheet 'Percentage!B1= Tháng 1, thì cột G của sheet 'Summary' sẽ được chọn để xét các tiêu chí: "1-2"; "3-6"; "7-12"....
     
   • Với các dòng có chữ "Total" đằng sau từng 'Buyer' riêng biệt thì cộng lại các kết quả 'Order' phía trên cùng một 'Buyer', Vd: 'Brooks Total' sẽ thể hiện số tổng của các Brooks phía trên (~SUM(C3:C8))
   • Khi Sheet 'Percentage!B1 thay đổi tháng, Vd: 2 (tháng 2), thì trích lục sheet 'Summary' theo cột tương ứng đã báo tại hàng 1: 'Month' 1,2,3,4,5,6,7,8,9,10,11,12, trong Vd là tháng 2 thì nó là cột K, tháng 3 thì cột O.....
2. Cột Qty (D3: D84): tương tự như cột Order, nhưng Trích lục và Cộng dữ liệu thỏa điều kiện (giống như trên) trong sheet 'Summary' và trả kết quả vào cột D sheet 'Percentage'. Cũng thay đổi theo tháng, tức tháng 1 lấy từ cột D; tháng 2: cột H; tháng 3: cột L......
3. Cột MHR (E3:E84): giống cách thức lấy kết quả của cột Qty (Trích lục và Cộng)
4. Cột % (F3:F84): là tỷ lệ % của số Order từng mục so với tổng số Order của mục đó. Vd: Brooks/Zipper Jacket có số Order: 2, tính trên tổng số của Brooks: 3, thì bằng (2:3)%

Các cột từ C đến F, thuộc khoảng điều kiện 'Produce_day' từ 1 đến 2, các cột tương ứng phía sau giống cách thức truy lục và đếm hay cộng như các cột C:F nhưng 'điều kiện' khoảng thay đổi: "3-6"; "7-12".....

Trên đây là tóm tắt cách bạn muốn yêu cầu, nếu có chỗ nào chưa thật đúng ý bạn, bạn nên ghi ra tường tận.

Về mặt công thức, bạn không nên sử dụng Vùng theo kiểu toàn bộ cột, Vd: Sum(A:A,A2,B:B), vì như vậy excel phải chạy hết cả triệu dòng thì làm cho máy bạn trì trệ thêm, nên sử dụng trong khoảng mà dữ liệu tồn tại, Vd: dòng dữ liệu bạn có mới đến dòng 1169, thì dùng rõ như Sum(A3:A1169,A2,B3:B1169). Vì lý do đó, nên tôi có tạo 'name' để: tạo Vùng lấy theo thực tế của bạn, cùng giản lược bớt tránh làm rối mắt khi tra cứu công thức trong bảng, gồm:

1. VungBuyer=OFFSET(Summary!$A$4,,,COUNTA(Summary!$A$3:$A$5000))
2. VungFty=OFFSET(Summary!$C$4,,,COUNTA(Summary!$A$3:$A$5000))
3. VungOrder=OFFSET(Summary!$G$4,,(Percentage!$B$1-1)*4,COUNTA(Summary!$A$3:$A$5000)) ; Vùng này vừa thay đổi theo dòng thực tế, vừa chọn cột phù hợp theo điều kiện tháng thay đổi.
4. VungQty=OFFSET(Summary!$D$4,,(Percentage!$B$1-1)*4,COUNTA(Summary!$A$3:$A$5000)) ; tương tự Vùng Order
5. VungMHR=OFFSET(Summary!$E$4,,(Percentage!$B$1-1)*4,COUNTA(Summary!$A$3:$A$5000)) ; tương tự Vùng Order
6. Dieukien=TRIM(MID(SUBSTITUTE(LOOKUP("zzz",Percentage!$C$2:F$2),"-",REPT(" ",10)),{1,11},10)) --> Nhằm tạo 'Mảng' điều kiện, Vd: ứng với khoảng nằm trong chuỗi "1-2" thì nó sẽ cho ra 'Mảng' {1,2}; khoảng nằm trong chuỗi "3-6" thì nó sẽ cho ra 'Mảng' {3,6}; khoảng nằm trong chuỗi "7-12" thì nó sẽ cho ra 'Mảng' {7,12}....Điều kiện này nằm trong biểu thức câu lệnh: (VungOrder>=DieuKien+{0,0.1}) tức ý hãy lấy Vùng Order so >= với 'Mảng' Điều kiện cộng thêm mức sau 0.01, Vd: {1,2} thì thành 'Mảng' {1,2.01}

Công thức của bạn lấy trực tiếp cột theo điều kiện, nên sẽ lặp đi lặp lại đoạn các điều kiện, như:
Tháng 1 thì: COUNTIFS(Summary!$C:$C,Percentage!$B8,Summary!$G:$G,">=1")-COUNTIFS(Summary!$C:$C,Percentage!$B8,Summary!$G:$G,">2")
Tháng 2 thì: COUNTIFS(Summary!$C:$C,Percentage!$B3,Summary!$K:$K,">=1")-COUNTIFS(Summary!$C:$C,Percentage!$B3,Summary!$K:$K,">2")
Tháng 3 thì: COUNTIFS(Summary!$C:$C,Percentage!$B3,Summary!$O:$O,">=1")-COUNTIFS(Summary!$C:$C,Percentage!$B3,Summary!$O:$O,">2")
Chỉ khác nhau các cột 'điều kiện trong khoảng', chính vì vậy làm cho công thức 'rậm rạp' , Bạn hãy tham khảo công thức tôi gửi, từ từ tìm hiểu thì về sau nếu có trường hợp tương tự bạn cũng có thể tự thân giải quyết.

Bạn tham khảo file kèm.

Chúc bạn ngày vui.

Rất cám ơn bác đã bỏ thời gian để phân tích công thức rất chi tiết và phức tạp như thế này, hiện tại thì em chưa thấy sự cố nào, em muốn chuyển đổi % sẽ như sau lấy cột Mrh bên sheet percentage chia cho tổng mrh sheet summary theo từng tháng ,vì trước đó em cũng bị loạn do dữ liệu quá nhiều nên chưa thế thấy hết được, chỉ vì nó quá khó thực sự là quá khó đối với em. vì em chỉ quanh đi quẩn lại vài hàm tính tổng tìm kiếm em đã tìm kiếm giải pháp nhưng không thể làm được nên em mới post bài viết chứ thực sự đi nhờ em cũng ngại lắm. nhờ 1 lần 2 lần nhưng lần thứ 3 dù có nhận được sự trợ giúp thì vẫn sẽ bị phê phán dù bất cứ lý do gì. Em nhận được sự giúp đỡ rất nhiệt tình nên em cám ơn cám ơn bác rất nhiều.

 

[thufpts]

Công thức bị sai thì ai biết kết quả đúng là sao? Sao bạn không nhập kết quả bạn tính toán bằng thủ công xem kết quả thế nào rồi giải thích cách tính?
Kết quả trong hình có thật là đúng với dữ liệu trong file bạn gởi không?
Tôi chỉ hiểu "mang máng" thế này, bạn kiểm tra lại kết quả xem sao.
Xóa bỏ sheet không liên quan, Dữ liệu đã chuyển thành Value, không còn công thức cho nhẹ file.
Thay đổi ô B1.

Đúng như bac nói em chưa mô tả chính xác khiến bác phân vân. em xin gửi lại file đính kèm để bác dễ hiểu hơn để viết code

 

[Ba Tê]

Đúng như bac nói em chưa mô tả chính xác khiến bác phân vân. em xin gửi lại file đính kèm để bác dễ hiểu hơn để viết code

Câu này mà hiểu "chết liền":

=nếu D9 có books total mà bằng Books total bên sheet summary thì lấy D9/Sum( cột E sheet summary) *100%. Nếu D9 có books total nhỏ hơn books total tại sheet sumary thì lấy D9/Sum( cột E sheet summary theo Buyer và fty_product_type) *100%

 

[thufpts]

Câu này mà hiểu "chết liền":

Dạ nó là thế này. % cho từng loại thì em đã giải thích cách chia cụ thể trong file rồi. trước đó em nói cũng chưa rõ ràng lắm.
lần này bác vào file là hiểu hết.

 

[Ba Tê]

....
lần này bác vào file là hiểu hết.

Tôi thua rồi. Bạn chờ người nào đọc hiểu với công việc của bạn đi.
Tôi đúng là "ngu" thiệt.

 

[thufpts]

Tôi thua rồi. Bạn chờ người nào đọc hiểu với công việc của bạn đi.
Tôi đúng là "ngu" thiệt.

không phải bác ngu bác nói thế làm em hơi buồn chút, chỉ là em không có nền tảng về code, diễn giải bằng văn xuôi khác nào đánh đố, em cũng không dám hi vọng nhiều.
bác rảnh giúp được là tốt rồi.