[HỎI] Tìm giá trị lớn nhất + tính tổng có điều kiện

[Mutants Men]

mọi người cho mình hỏi cách tìm giá trị lớn nhất theo điều kiện và tính tổng nhiều điều kiện với
+ tìm giá trị lớn nhất là 1 công thức
+ tính tổng là 1 công thức
2 cái không có gom chung nha mọi người, để mình áp dụng các trường hợp khác nhau
nội dung có trong file đính kèm
CẢM ƠN MỌI NGƯỜI ĐÃ ĐỌC BÀI VÀ GIÚP ĐỠ

 

[Phan Thế Hiệp]

mọi người cho mình hỏi cách tìm giá trị lớn nhất theo điều kiện và tính tổng nhiều điều kiện với
+ tìm giá trị lớn nhất là 1 công thức
+ tính tổng là 1 công thức
2 cái không có gom chung nha mọi người, để mình áp dụng các trường hợp khác nhau
nội dung có trong file đính kèm
CẢM ƠN MỌI NGƯỜI ĐÃ ĐỌC BÀI VÀ GIÚP ĐỠ

Thử:

B26=AGGREGATE(14,6,INDEX($E$6:$J$21,,MATCH($A26,$E$5:$J$5,))/(MMULT(MATCH($C$6:$D$21,CHOOSE({1,2,3},0,B$25,EOMONTH(B$25,0)))*{1,-1},{1;1})<>0),1)
B35=SUMPRODUCT(INDEX($E$6:$J$21,,MATCH($A35,$E$5:$J$5,))*(MMULT(MATCH($C$6:$D$21,CHOOSE({1,2,3},0,B$34,EOMONTH(B$34,0)))*{1,-1},{1;1})<>0))

Enter, fill qua phải, rồi copy cả hàng xuống dưới.
Xem file kèm.

Thân

 

[befaint]

mọi người cho mình hỏi cách tìm giá trị lớn nhất theo điều kiện và tính tổng nhiều điều kiện với
+ tìm giá trị lớn nhất là 1 công thức
+ tính tổng là 1 công thức
2 cái không có gom chung nha mọi người, để mình áp dụng các trường hợp khác nhau
nội dung có trong file đính kèm
CẢM ƠN MỌI NGƯỜI ĐÃ ĐỌC BÀI VÀ GIÚP ĐỠ

Một số thành viên lâu lâu mới vô diễn đàn... thấy nhớ ghê!

 

[Mutants Men]

Thử:

B26=AGGREGATE(14,6,INDEX($E$6:$J$21,,MATCH($A26,$E$5:$J$5,))/(MMULT(MATCH($C$6:$D$21,CHOOSE({1,2,3},0,B$25,EOMONTH(B$25,0)))*{1,-1},{1;1})<>0),1)
B35=SUMPRODUCT(INDEX($E$6:$J$21,,MATCH($A35,$E$5:$J$5,))*(MMULT(MATCH($C$6:$D$21,CHOOSE({1,2,3},0,B$34,EOMONTH(B$34,0)))*{1,-1},{1;1})<>0))

Enter, fill qua phải, rồi copy cả hàng xuống dưới.
Xem file kèm.

Thân

nhìn công thức chả hiểu gì hết trơn :v, hàm lạ quắc, copy kéo qua theo bảng của mình thì lại lỗi #NUM ))

 

[Phan Thế Hiệp]

nhìn công thức chả hiểu gì hết trơn :v, hàm lạ quắc, copy kéo qua theo bảng của mình thì lại lỗi #NUM ))

Đưa file thật bạn bị lỗi lên diễn đàn đi bạn.
Yêu cầu "độc" thì dùng thuốc "độc"
"Lấy độc trị độc"
Thân

 

[Phan Thế Hiệp]

nhìn công thức chả hiểu gì hết trơn :v, hàm lạ quắc, copy kéo qua theo bảng của mình thì lại lỗi #NUM ))

Sau khi kiểm tra lại, tôi điều chỉnh công thức sau:

B26=AGGREGATE(14,6,$E$6:$J$21/($A26=$E$5:$J$5)/(MATCH(MMULT(MATCH($C$6:$D$21,CHOOSE({1,2,3},0,B$25,EDATE(B$25,1))),{1;1}),{0,3,6})=2),1)
B35=SUMPRODUCT($E$6:$J$21*($A35=$E$5:$J$5)*(MATCH(MMULT(MATCH($C$6:$D$21,CHOOSE({1,2,3},0,B$34,EDATE(B$25,1))),{1;1}),{0,3,6})=2))

Enter, fill qua phải, rồi copy cả hàng xuống.
Xem file kèm.

Thân

 

[Mutants Men]

Đưa file thật bạn bị lỗi lên diễn đàn đi bạn.
Yêu cầu "độc" thì dùng thuốc "độc"
"Lấy độc trị độc"
Thân

Công thức bên trên áp dụng được rồi bác. mà có chỗ ra kết quả sai, mong bác sửa giúp, vì công thức chưa dùng qua nên tự sửa không được.
điển hình Vật Liệu 1 của Công việc 5, tức ô E10 bác nhập số 10.... đại loại gì đấy. thì ở tháng 9, dòng vật liệu 1 không nhảy đúng số vừa nhập.

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

Sau khi kiểm tra lại, tôi điều chỉnh công thức sau:

B26=AGGREGATE(14,6,$E$6:$J$21/($A26=$E$5:$J$5)/(MATCH(MMULT(MATCH($C$6:$D$21,CHOOSE({1,2,3},0,B$25,EDATE(B$25,1))),{1;1}),{0,3,6})=2),1)
B35=SUMPRODUCT($E$6:$J$21*($A35=$E$5:$J$5)*(MATCH(MMULT(MATCH($C$6:$D$21,CHOOSE({1,2,3},0,B$34,EDATE(B$25,1))),{1;1}),{0,3,6})=2))

Enter, fill qua phải, rồi copy cả hàng xuống.
Xem file kèm.

Thân

trả lời bên trên rồi mới tải file mới, file mới này ra kết quả đúng rồi bác. cảm ơn bác nhiều nhé. để sửa lại vào bảng của mình, có gì phát sinh mình hỏi tiếp
nếu được có thể giải thích giúp mình công thức luôn nhé.

 

[Phan Thế Hiệp]

nếu được có thể giải thích giúp mình công thức luôn nhé.

1. Yêu cầu của bạn đưa ra chỉ khó ở đoạn làm sao biết được: Thời gian bạn muốn tổng kết (tìm số lớn hoặc cộng tổng), vd: tháng 8/2018, có nằm trong các khoảng thời gian "bắt đầu" và "kết thúc", tức là chỉ chọn ra trong các hàng từ C6: D21, hàng nào có chứa khoảng thời gian bạn cần, thì chọn lấy giá trị của cột "Vật liệu n" tương ứng.
   • Giả sử, tôi lấy mẫu bạn cần tổng kết trong tháng 8/2018, tức thời gian cho phép là 01/08 đến 31/08, rồi lấy thời gian "bắt đầu" và "kết thúc" của Vùng C6: D21 đem so với thời gian đó, dòng nào thỏa ghi nhận 1, không thỏa ghi nhận 0. Sau đó chọn Mảng tương ứng với Vùng vật liệu tại dòng A26: A31 mà trích ra giá trị có dòng nào thỏa điều kiện đã ghi nhận là 1 như trên. (Nếu làm mà đỡ tốn bộ nhớ máy tôi sẽ chọn riêng ra "Cột Vật liệu" tương ứng để xét, ví dụ: nếu đang xét dòng A26="Vật liệu 1" thì tôi chỉ chọn cột E6: E21 để xét mà thôi, không cần xét hết cả Vùng E6: J21).
2. Để thực hiện ý định như vậy, dùng hàm MATCH( 'Vùng thời gian cần so' , 'Mảng Mốc thời gian so' , 1 ) hay viết ngắn gọn bạn có thể giản lược đối số ",1" sau cùng --> MATCH( 'Vùng thời gian cần so' , 'Mảng Mốc thời gian so' ). Cụ thể:
   • 2.1: (MATCH( MMULT( MATCH( $C$6: $D$21 , CHOOSE({1,2,3},0,B$25,EDATE(B$25,1) )) , {1;1}) , {0,3,6})=2)
     • MATCH( $C$6: $D$21 , CHOOSE({1,2,3},0,B$25,EDATE(B$25,1) ):
       • CHOOSE({1,2,3},0,B$25,EDATE(B$25,1) --> sẽ tạo Mảng 1 chiều ngang chứa giá trị kết quả {0, ngày 1/8, ngày 1/9} hay {0, 43313, 43344} làm 'Mảng Mốc thời gian so'.
       • MATCH( $C$6: $D$21 , CHOOSE({1,2,3},0,B$25,EDATE(B$25,1) ) sẽ đem các thời gian "Bắt đầu" và "Kết thúc" so với Mảng {0, 43313, 43344}, nó sẽ tạo 1 Mảng hai chiều có số dòng bằng C6: C21 (16 dòng) và có 2 cột, chứa các kết quả là giá trị khoảng thứ mấy của khoảng {0, 43313, 43344}, tức chỉ có kết quả là: 1 hoặc 2 hoặc 3 tương ứng với vị trí nằm trong 3 khoảng: từ 0->43312, hoặc từ 43313->43343, hoặc từ >=43344. (Xem hình)
     • Dùng MMULT( 'Kết quả Mảng 2 chiều trên' , {1;1} )--> nhằm cộng lại từng dòng của Mảng kết quả trên, ví dụ: {1,2}= 1+2 = 3.... Nó sẽ tạo ra kết quả là Mảng 1 chiều dọc là các kết quả vừa được cộng với giá trị trong khoảng từ 2 đến 6.
     • Như hình minh họa trên, ta thấy chỉ có các giá trị từ 3->5 là nằm trong khoảng thời gian cần lấy theo yêu cầu, hai khoảng {1,1}=2 và {3,3}=6 thì nằm ngoài khoảng thời gian yêu cầu, cần phải loại bỏ. Dùng công thức:
       • =(MATCH( 'Kết quả từ MMULT' , {0,3,6} ) = 2) -->sẽ tạo ra Mảng 1 chiều dọc chứa các dòng thỏa ghi nhận là TRUE (hay 1), không thỏa là FALSE (hay 0).
   • 2.2: $E$6: $J$21 / ($A26=$E$5:$J$5) / (MATCH(MMULT(MATCH($C$6:$D$21,CHOOSE({1,2,3},0,B$25,EDATE(B$25,1))),{1;1}),{0,3,6})=2) hay ngắn gọn là: $E$6: $J$21 / ($A26=$E$5:$J$5) / 'Đoạn công thức 2.1 đã giải thích ở trên', với:
     • $E$6: $J$21: là các dữ liệu giá trị trong bảng bạn cung cấp.
     • ($A26=$E$5:$J$5): chọn cột nào có Vật liệu tương ứng vớ A26, ví dụ lấy cột E: "Vật liệu 1" thỏa A26.
3. Sau cùng, dùng:
   • AGGREGATE( 14, 6, 'Công thức 2.2' , 1) --> Trích lục giá trị lớn nhất (đối số: 14) trong các giá trị kết quả do Công thức 2.2 mang về.
   • SUMPRODUCT( 'Công thức 2.2' ) --> (thay các dấu chia "/" thành dấu nhân "*" để phù hợp với chức năng của hàm) Cộng tổng các giá trị kết quả do Công thức 2.2 mang về.

Bạn xem thêm file đính kèm có ví dụ 1 trường hợp tháng 08/2018 "Vật liệu 1" để bạn tường tận thêm.

Chúc bạn ngày vui.

Thấy em trai tôi có vẽ mến bạn nên tôi giải thích chi tiết nhiều hơn để bạn dễ nắm bắt giải thuật bài toán.

 

[Mutants Men]

1. Yêu cầu của bạn đưa ra chỉ khó ở đoạn làm sao biết được: Thời gian bạn muốn tổng kết (tìm số lớn hoặc cộng tổng), vd: tháng 8/2018, có nằm trong các khoảng thời gian "bắt đầu" và "kết thúc", tức là chỉ chọn ra trong các hàng từ C6: D21, hàng nào có chứa khoảng thời gian bạn cần, thì chọn lấy giá trị của cột "Vật liệu n" tương ứng.
   • Giả sử, tôi lấy mẫu bạn cần tổng kết trong tháng 8/2018, tức thời gian cho phép là 01/08 đến 31/08, rồi lấy thời gian "bắt đầu" và "kết thúc" của Vùng C6: D21 đem so với thời gian đó, dòng nào thỏa ghi nhận 1, không thỏa ghi nhận 0. Sau đó chọn Mảng tương ứng với Vùng vật liệu tại dòng A26: A31 mà trích ra giá trị có dòng nào thỏa điều kiện đã ghi nhận là 1 như trên. (Nếu làm mà đỡ tốn bộ nhớ máy tôi sẽ chọn riêng ra "Cột Vật liệu" tương ứng để xét, ví dụ: nếu đang xét dòng A26="Vật liệu 1" thì tôi chỉ chọn cột E6: E21 để xét mà thôi, không cần xét hết cả Vùng E6: J21).
2. Để thực hiện ý định như vậy, dùng hàm MATCH( 'Vùng thời gian cần so' , 'Mảng Mốc thời gian so' , 1 ) hay viết ngắn gọn bạn có thể giản lược đối số ",1" sau cùng --> MATCH( 'Vùng thời gian cần so' , 'Mảng Mốc thời gian so' ). Cụ thể:
   • 2.1: (MATCH( MMULT( MATCH( $C$6: $D$21 , CHOOSE({1,2,3},0,B$25,EDATE(B$25,1) )) , {1;1}) , {0,3,6})=2)
     • MATCH( $C$6: $D$21 , CHOOSE({1,2,3},0,B$25,EDATE(B$25,1) ):
       • CHOOSE({1,2,3},0,B$25,EDATE(B$25,1) --> sẽ tạo Mảng 1 chiều ngang chứa giá trị kết quả {0, ngày 1/8, ngày 1/9} hay {0, 43313, 43344} làm 'Mảng Mốc thời gian so'.
       • MATCH( $C$6: $D$21 , CHOOSE({1,2,3},0,B$25,EDATE(B$25,1) ) sẽ đem các thời gian "Bắt đầu" và "Kết thúc" so với Mảng {0, 43313, 43344}, nó sẽ tạo 1 Mảng hai chiều có số dòng bằng C6: C21 (16 dòng) và có 2 cột, chứa các kết quả là giá trị khoảng thứ mấy của khoảng {0, 43313, 43344}, tức chỉ có kết quả là: 1 hoặc 2 hoặc 3 tương ứng với vị trí nằm trong 3 khoảng: từ 0->43312, hoặc từ 43313->43343, hoặc từ >=43344. (Xem hình) View attachment 202306
     • Dùng MMULT( 'Kết quả Mảng 2 chiều trên' , {1;1} )--> nhằm cộng lại từng dòng của Mảng kết quả trên, ví dụ: {1,2}= 1+2 = 3.... Nó sẽ tạo ra kết quả là Mảng 1 chiều dọc là các kết quả vừa được cộng với giá trị trong khoảng từ 2 đến 6.
     • Như hình minh họa trên, ta thấy chỉ có các giá trị từ 3->5 là nằm trong khoảng thời gian cần lấy theo yêu cầu, hai khoảng {1,1}=2 và {3,3}=6 thì nằm ngoài khoảng thời gian yêu cầu, cần phải loại bỏ. Dùng công thức:
       • =(MATCH( 'Kết quả từ MMULT' , {0,3,6} ) = 2) -->sẽ tạo ra Mảng 1 chiều dọc chứa các dòng thỏa ghi nhận là TRUE (hay 1), không thỏa là FALSE (hay 0).
   • 2.2: $E$6: $J$21 / ($A26=$E$5:$J$5) / (MATCH(MMULT(MATCH($C$6:$D$21,CHOOSE({1,2,3},0,B$25,EDATE(B$25,1))),{1;1}),{0,3,6})=2) hay ngắn gọn là: $E$6: $J$21 / ($A26=$E$5:$J$5) / 'Đoạn công thức 2.1 đã giải thích ở trên', với:
     • $E$6: $J$21: là các dữ liệu giá trị trong bảng bạn cung cấp.
     • ($A26=$E$5:$J$5): chọn cột nào có Vật liệu tương ứng vớ A26, ví dụ lấy cột E: "Vật liệu 1" thỏa A26.
3. Sau cùng, dùng:
   • AGGREGATE( 14, 6, 'Công thức 2.2' , 1) --> Trích lục giá trị lớn nhất (đối số: 14) trong các giá trị kết quả do Công thức 2.2 mang về.
   • SUMPRODUCT( 'Công thức 2.2' ) --> (thay các dấu chia "/" thành dấu nhân "*" để phù hợp với chức năng của hàm) Cộng tổng các giá trị kết quả do Công thức 2.2 mang về.

Bạn xem thêm file đính kèm có ví dụ 1 trường hợp tháng 08/2018 "Vật liệu 1" để bạn tường tận thêm.

Chúc bạn ngày vui.

Thấy em trai tôi có vẽ mến bạn nên tôi giải thích chi tiết nhiều hơn để bạn dễ nắm bắt giải thuật bài toán.

Vâng! xin cảm ơn bác đã giải thích và hướng dẫn cụ thể.
để mình nghiêm cứu thêm, có mấy hàm lạ quá (do không biết đến) nếu có gì không hiểu được thì chịu.

 

[Phan Thế Hiệp]

Vâng! xin cảm ơn bác đã giải thích và hướng dẫn cụ thể.
để mình nghiêm cứu thêm, có mấy hàm lạ quá (do không biết đến) nếu có gì không hiểu được thì chịu.

Xem thêm các giải thích cú pháp của hàm:

1. Aggregate(): https://support.office.com/vi-vn/ar...ggregate-43b9278e-6aa7-4f17-92b6-e19993fa26df
2. Mmult(): https://support.office.com/vi-vn/article/mmult-hàm-mmult-40593ed7-a3cd-4b6b-b9a3-e4ad3c7245eb
3. Sumproduct(): https://support.office.com/vi-vn/ar...mproduct-16753e75-9f68-4874-94ac-4d2145a2fd2e
4. Choose(): https://support.office.com/vi-vn/article/choose-hàm-choose-fc5c184f-cb62-4ec7-a46e-38653b98f5bc
5. Match(): https://support.office.com/vi-vn/article/match-hàm-match-e8dffd45-c762-47d6-bf89-533f4a37673a

Chúc bạn học tập vui với GPE.
Thân