Rút gọn công thức tính điểm trung bình (1 người xem)

[pns56]

Tôi muốn lập công thức tính điểm trung bình chung của một học kỳ từ các cột điểm học phần, mỗi cột 1 điểm thi lần 1 hoặc 2 cột điểm lần 1, 2. công thức là điểm cao nhất trong 2 lần thi x đơn vị học trình của học phần 1 +...... học phần 2+....tất cả chia cho tổng số đơn vị học trình. Trong file ví dụ: tôi lập công thức: =ROUND(((IF(D7>E7;D7;E7)*$D$4+IF(F7>G7;F7;G7)*$F$4+IF(H7>I7;H7;I7)*$H$4)/$J$4);1), nếu trường hợp là 10 học phần hay nhiều hơn nữa thì công thức sẽ quá dài. Vì vậy nhờ ACE giúp rút ngắn công thức nếu được. Cảm ơn nhiều.

 

[nginh]

tôi muốn lập công thức tính điểm trung bình chung của một học kỳ từ các cột điểm học phần, mỗi cột 1 điểm thi lần 1 hoặc 2 cột điểm lần 1, 2. Công thức là điểm cao nhất trong 2 lần thi x đơn vị học trình của học phần 1 +...... Học phần 2+....tất cả chia cho tổng số đơn vị học trình. Trong file ví dụ: Tôi lập công thức: =round(((if(d7>e7;d7;e7)*$d$4+if(f7>g7;f7;g7)*$f$4+if(h7>i7;h7;i7)*$h$4)/$j$4);1), nếu trường hợp là 10 học phần hay nhiều hơn nữa thì công thức sẽ quá dài. Vì vậy nhờ ace giúp rút ngắn công thức nếu được. Cảm ơn nhiều.

J7=ROUND(SUM(IF(D7:H7>E7:I7,D7:H7,E7:I7)*D$4:H$4)/J$4,1)

Ctrl+Shift+Enter

 

[leonguyenz]

Tôi muốn lập công thức tính điểm trung bình chung của một học kỳ từ các cột điểm học phần, mỗi cột 1 điểm thi lần 1 hoặc 2 cột điểm lần 1, 2. công thức là điểm cao nhất trong 2 lần thi x đơn vị học trình của học phần 1 +...... học phần 2+....tất cả chia cho tổng số đơn vị học trình. Trong file ví dụ: tôi lập công thức: =ROUND(((IF(D7>E7;D7;E7)*$D$4+IF(F7>G7;F7;G7)*$F$4+IF(H7>I7;H7;I7)*$H$4)/$J$4);1), nếu trường hợp là 10 học phần hay nhiều hơn nữa thì công thức sẽ quá dài. Vì vậy nhờ ACE giúp rút ngắn công thức nếu được. Cảm ơn nhiều.

Bài #2 hình như chưa đúng.
Thử công thức này xem:
Đặt Name Col, nếu 10 học phần thì kéo đủ 20 cột, thay cho Sheet1!$D$4:$I$4:

=ROW(INDIRECT("1:"&COLUMNS(Sheet1!$D$4:$I$4)/2))*2-2

Công thức tại tính điểm TB, J7:

=ROUND(SUMPRODUCT(SUBTOTAL(104,OFFSET($D7,,Col,,2)),SUBTOTAL(104,OFFSET($D$4,,Col,,2)))/$J$4,1)

 

[Phuocam]

Tôi muốn lập công thức tính điểm trung bình chung của một học kỳ từ các cột điểm học phần, mỗi cột 1 điểm thi lần 1 hoặc 2 cột điểm lần 1, 2. công thức là điểm cao nhất trong 2 lần thi x đơn vị học trình của học phần 1 +...... học phần 2+....tất cả chia cho tổng số đơn vị học trình. Trong file ví dụ: tôi lập công thức: =ROUND(((IF(D7>E7;D7;E7)*$D$4+IF(F7>G7;F7;G7)*$F$4+IF(H7>I7;H7;I7)*$H$4)/$J$4);1), nếu trường hợp là 10 học phần hay nhiều hơn nữa thì công thức sẽ quá dài. Vì vậy nhờ ACE giúp rút ngắn công thức nếu được. Cảm ơn nhiều.

Thay IF bằng MAX cho gọn:
=ROUND(((MAX(D7,E7)*$D$4+MAX(F7,G7)*$F$4+MAX(H7,I7)*$H$4)/$J$4),1)

 

[Hau151978]

Bài #2 hình như chưa đúng.

Công thức có thể vẫn đúng do trong công thức mảng tuy có những phần tử là max(L2 của một học phần, L1 học phần kế tiếp) nhưng lại được nhân với 0. Ngoài ra có thể dùng sumproduct, mình chưa test được do onl bằng điện thoại.

 

[leonguyenz]

Thay IF bằng MAX cho gọn:
=ROUND(((MAX(D7,E7)*$D$4+MAX(F7,G7)*$F$4+MAX(H7,I7)*$H$4)/$J$4),1)

Dùng Max chủ yếu là để thay thế cho IF thôi, nếu không phải là 3 học phần, 10 học phần, mà có thể 20 học phần thì dùng tới 20 hàm Max hay sao?
Bài #2 đã test với hàm mảng, J7 cho ra kết quả là 8.
Tác giả đã nói: "nếu trường hợp là 10 học phần hay nhiều hơn nữa ...", vì vậy nên cần 1 công thức mang tính tổng quát.

 

[Hau151978]

Dùng Max chủ yếu là để thay thế cho IF thôi, nếu không phải là 3 học phần, 10 học phần, mà có thể 20 học phần thì dùng tới 20 hàm Max hay sao?
Bài #2 đã test với hàm mảng, J7 cho ra kết quả là 8.
Tác giả đã nói: "nếu trường hợp là 10 học phần hay nhiều hơn nữa ...", vì vậy nên cần 1 công thức mang tính tổng quát.

Trong file của tác giả, các ô E4, G4... không phải rỗng mà chứa công thức nhưng bị tác giả ẩn đi (mình mới tập tành excel nên không biết thủ thuật này). Ở ô E4 có công thức =G4 nên E4=3, G4=2... nên kết quả trong công thức ở bài 2 bị sai. Sau khi clear content các ô E4, G4, I4 thì công thức ở bài 2 {=ROUND(SUM(IF(D7:H7>E7:I7,D7:H7,E7:I7)*$D$4:$H$4)/$J$4,1)} sẽ cho kết quả đúng là 5.2

 

[pns56]

Cảm ơn các ACE đã tham gia góp ý công thức cho mình, mỗi người đều có ý hay mình xin học hỏi. Vâng, mình quên không xóa E 4, G4, I 4.. nên có hậu quả thế, xin lỗi ACE. Xin cảm ơn. Thân chào.

 

[pns56]

nhờ chỉnh công thức cột J

Nhờ ACE giúp thêm lần nữa, trong Sheet2 của file ví dụ, công thức tính điểm trung bình không đúng ở trường hợp HP được miễn học miễn thi ký hiệu là M thì không tính vào công thức tính Điểm trung bình. Mong ACE giúp đỡ.

 

[leonguyenz]

Nhờ ACE giúp thêm lần nữa, trong Sheet2 của file ví dụ, công thức tính điểm trung bình không đúng ở trường hợp HP được miễn học miễn thi ký hiệu là M thì không tính vào công thức tính Điểm trung bình. Mong ACE giúp đỡ.

Nếu áp dụng công thức bài #3, thì sửa lại công thức trong Name (tham chiếu sheet2) và công thức trên sheet2, J7:

=ROUND(SUMPRODUCT(SUBTOTAL(104,OFFSET($D7,,Col,,2)),SUBTOTAL(104,OFFSET($D$4,,Col,,2)))/SUMPRODUCT((SUBTOTAL(104,OFFSET($D7,,Col,,2))>0)*(SUBTOTAL(104,OFFSET($D$4,,Col,,2)))),1)

 

[nginh]

Nhờ ACE giúp thêm lần nữa, trong Sheet2 của file ví dụ, công thức tính điểm trung bình không đúng ở trường hợp HP được miễn học miễn thi ký hiệu là M thì không tính vào công thức tính Điểm trung bình. Mong ACE giúp đỡ.

J7=ROUND(SUM(N(IF(D7:H7>E7:I7,D7:H7,E7:I7))*D$4:H$4)/SUM((D7:H7<>"M")*D$4:H$4),1)

Ctrl+Shift+Enter

 

[Let'GâuGâu]

góp theo cthức (theo cách bạn Leo)

J7=ROUND(SUMPRODUCT({3,2,4},SUBTOTAL(4,OFFSET(D7,,{0,2,4},,2)))/SUMPRODUCT((SUBTOTAL(2,OFFSET(D7,,{0,2,4},,2))>0)*{3,2,4}),1)