Bài này áp dụng công thức như nào để giải đây ?

  • Thread starter Thread starter FTC
  • Ngày gửi Ngày gửi
Liên hệ QC

FTC

Thành viên mới
Tham gia
14/8/08
Bài viết
11
Được thích
1
Các bác giúp em câu này với.Căn cứ MS Nghiệp vụ và bảng hệ số, tính cột "Điểm TB các môn thi" (Điểm đã nhân hệ số)
 

File đính kèm

Cám ơn bạn đã cho mình 1 cách hay. Nhưng mình muốn giải bài trên bằng các hàm thông dụng như index, match, hlookup..... Có cách nào không ?
Công thức tính điểm TB như sau: Tổng điểm đã nhân hệ số/Tổng hệ số
 
Cám ơn bạn đã cho mình 1 cách hay. Nhưng mình muốn giải bài trên bằng các hàm thông dụng như index, match, hlookup..... Có cách nào không ?
Công thức tính điểm TB như sau: Tổng điểm đã nhân hệ số/Tổng hệ số
Nếu làm cách này tôi nghĩ bạn tự làm được mà... Cứ HLOOKUP từng điểm 1 trong bảng phía dưới rồi cộng, trừ, nhân, chia gì đó với nhau... Thế thôi
Có điều công thức sẽ dài lê thê
 
Đặt con trỏ chuột tại dòng 4, Ctrl + F3 để vào Define name và đặt 1 name tên HS như sau:
Tại G4 gõ công thức:
@anhtuan1066 xem lại name HS
------------------------------
Có thể là Đặt con trỏ chuột tại dòng 4, Ctrl + F3 để vào Define name và đặt 1 name tên HS như sau
PHP:
HS =OFFSET(OFFSET($A$22,MATCH(RIGHT($B4,1),$F$23:$F$31,0),,3),MATCH(LEFT($B4,LEN($B4)-2),OFFSET($A$22,MATCH(RIGHT($B4,1),$F$23:$F$31,0),,3),0)-1,1,1,4)
Hoặc
PHP:
HS =OFFSET(Ma,MATCH(LEFT($B4,LEN($B4)-2),Ma,0)-1,1,1,4)
Thì thêm name
Ma =OFFSET($A$22,MATCH(RIGHT($B4,1),$F$23:$F$31,0),,3)
 
Lần chỉnh sửa cuối:
Cám ơn bạn đã cho mình 1 cách hay. Nhưng mình muốn giải bài trên bằng các hàm thông dụng như index, match, hlookup..... Có cách nào không ?
Công thức tính điểm TB như sau: Tổng điểm đã nhân hệ số/Tổng hệ số
Bạn muốn làm bằng công thức thông thường à? Được, chỉ sợ bạn không có kiên nhẫn thôi.

Này nhé, để tính hệ số cho mỗi môn, bạn dùng công thức này:

=INDEX($B$23:$E$31, MATCH(LEFT($B4, LEN($B4) - 2), $A$23:$A$25, 0) + IF(RIGHT($B4) = "A", 0, IF(RIGHT($B4)= "B", 3, 6)), MATCH(C$3, $B$22:$E$22, 0))

Sau đó, để tính tổng số điểm của các môn, bạn lần lượt lấy C4, D4, E4 và F4 nhân với công thức ở trên, mỗi lần thì thay ký tự mà tôi bôi đỏ ở trên tương ứng. Ví dụ, tại G4:

=C4 * INDEX($B$23:$E$31, MATCH(LEFT($B4, LEN($B4) - 2), $A$23:$A$25, 0) + IF(RIGHT($B4) = "A", 0, IF(RIGHT($B4)= "B", 3, 6)), MATCH(C$3, $B$22:$E$22, 0)) + D4 * INDEX($B$23:$E$31, MATCH(LEFT($B4, LEN($B4) - 2), $A$23:$A$25, 0) + IF(RIGHT($B4) = "A", 0, IF(RIGHT($B4)= "B", 3, 6)), MATCH(D$3, $B$22:$E$22, 0)) + E4 * INDEX($B$23:$E$31, MATCH(LEFT($B4, LEN($B4) - 2), $A$23:$A$25, 0) + IF(RIGHT($B4) = "A", 0, IF(RIGHT($B4)= "B", 3, 6)), MATCH(E$3, $B$22:$E$22, 0)) + F4 * INDEX($B$23:$E$31, MATCH(LEFT($B4, LEN($B4) - 2), $A$23:$A$25, 0) + IF(RIGHT($B4) = "A", 0, IF(RIGHT($B4)= "B", 3, 6)), MATCH(F3, $B$22:$E$22, 0))

Bạn sợ chưa? Cứ thử xem, công thức ở trên cho ra kết quả là 80, tức là:

= (8*4) + (9*2) + (4*3) + (9*2)


Chưa hết! Đây mới chỉ là tổng số điểm, còn phải chia cho tổng hệ số thì mới ra điểm trung bình. Nghĩa là phải làm sao cho tại G4:

= (8*4 + 9*2 + 4*3 + 9*2) / (4+2+3+2)

Vậy thì bạn phải ghép thêm vào cái công thức dài ngoằng ở trên một cái công thức gần y như vậy:

=(C4 * INDEX($B$23:$E$31, MATCH(LEFT($B4, LEN($B4) - 2), $A$23:$A$25, 0) + IF(RIGHT($B4) = "A", 0, IF(RIGHT($B4)= "B", 3, 6)), MATCH(C$3, $B$22:$E$22, 0)) + D4 * INDEX($B$23:$E$31, MATCH(LEFT($B4, LEN($B4) - 2), $A$23:$A$25, 0) + IF(RIGHT($B4) = "A", 0, IF(RIGHT($B4)= "B", 3, 6)), MATCH(D$3, $B$22:$E$22, 0)) + E4 * INDEX($B$23:$E$31, MATCH(LEFT($B4, LEN($B4) - 2), $A$23:$A$25, 0) + IF(RIGHT($B4) = "A", 0, IF(RIGHT($B4)= "B", 3, 6)), MATCH(E$3, $B$22:$E$22, 0)) + F4 * INDEX($B$23:$E$31, MATCH(LEFT($B4, LEN($B4) - 2), $A$23:$A$25, 0) + IF(RIGHT($B4) = "A", 0, IF(RIGHT($B4)= "B", 3, 6)), MATCH(F3, $B$22:$E$22, 0))) / (INDEX($B$23:$E$31, MATCH(LEFT($B4, LEN($B4) - 2), $A$23:$A$25, 0) + IF(RIGHT($B4) = "A", 0, IF(RIGHT($B4)= "B", 3, 6)), MATCH(C$3, $B$22:$E$22, 0)) + INDEX($B$23:$E$31, MATCH(LEFT($B4, LEN($B4) - 2), $A$23:$A$25, 0) + IF(RIGHT($B4) = "A", 0, IF(RIGHT($B4)= "B", 3, 6)), MATCH(D$3, $B$22:$E$22, 0)) + INDEX($B$23:$E$31, MATCH(LEFT($B4, LEN($B4) - 2), $A$23:$A$25, 0) + IF(RIGHT($B4) = "A", 0, IF(RIGHT($B4)= "B", 3, 6)), MATCH(E$3, $B$22:$E$22, 0)) + INDEX($B$23:$E$31, MATCH(LEFT($B4, LEN($B4) - 2), $A$23:$A$25, 0) + IF(RIGHT($B4) = "A", 0, IF(RIGHT($B4)= "B", 3, 6)), MATCH(F3, $B$22:$E$22, 0)))

Tôi dám bảo đảm rằng cái công thức này chính xác 100%, tuy nhiên chúng ta sẽ không có cơ hội để thử nó, bởi vì công thức này quá dài, mà Excel thì không cho phép nhập một cái công thức quá dài như vậy vào một ô!

Cũng không phải là không có cách giải quyết, nếu như bạn muốn dùng các công thức bình thường, nhưng bạn có đồng ý dùng thêm cột phụ không? Nếu bạn đồng ý dùng thêm cột phụ thì tôi sẽ hướng dẫn tiếp.

Thân.
 
Lần chỉnh sửa cuối:
Cám ơn bạn đã cho mình 1 cách hay. Nhưng mình muốn giải bài trên bằng các hàm thông dụng như index, match, hlookup..... Có cách nào không ?
Công thức tính điểm TB như sau: Tổng điểm đã nhân hệ số/Tổng hệ số

Tại sao không làm cái đơn giản nhỉ. Theo tôi, thì nên làm giống như anhtuan. Vừa đơn giản dễ nhìn. Càng rờm rà càng nhứt mắt bạn ơi
 
Mình biết để giải bài này thì cách của anhtuan là hay nhất,lẹ nhất và mình sẽ áp dụng cách đó. Mình chỉ muốn biết là nếu dùng công thức thông thường thì có công thức nào vừa ngắn lại vừa khả thi không thôi, ai ngờ nó lại dài "khủng" như vậy @_@. Ok, nếu thêm 1 cột phụ thì giải quyết như thế nào, BNTT giải thử cho mình học hỏi kinh nghiệm ^^. Cám ơn trước nha.
 
Mình biết để giải bài này thì cách của anhtuan là hay nhất,lẹ nhất và mình sẽ áp dụng cách đó. Mình chỉ muốn biết là nếu dùng công thức thông thường thì có công thức nào vừa ngắn lại vừa khả thi không thôi, ai ngờ nó lại dài "khủng" như vậy @_@. Ok, nếu thêm 1 cột phụ thì giải quyết như thế nào, BNTT giải thử cho mình học hỏi kinh nghiệm ^^. Cám ơn trước nha.
Này nhé, bạn tạo ra một khối ô có kích thước bằng y như khối ô chứa điểm từng môn, và có bao nhiêu hàng thì cũng dùng bấy nhiêu hàng. Khối ô này sẽ dùng làm nơi chứa hệ số ứng với từng môn và từng mã số nghiệp vụ.
Ví dụ, bạn bắt đầu từ cột K, vì có bốn môn nên ta dùng 4 cột K, L, M và N. Số hàng thì từ hàng 4 đến hàng 18.
Bạn chọn hết khối ô mới này (K4:N18), và nhập công thức đầu tiên mà tôi đã nói ở bài trước, công thức dùng để tìm ra hệ số, xong rồi nhấn Ctrl+Enter (để nhập một phát cho cả K4:N18):
=INDEX($B$23:$E$31, MATCH(LEFT($B4, LEN($B4) - 2), $A$23:$A$25, 0) + IF(RIGHT($B4) = "A", 0, IF(RIGHT($B4)= "B", 3, 6)), MATCH(C$3, $B$22:$E$22, 0))
Nếu bạn nhập đúng, thì trong K4:N18 sẽ cho ra kết quả là những con số hệ số (K4 = 4, L4 = 2, M4 = 3, v.v...

Bây giờ ta sẽ dùng SUMPRODUCT để tính tổng số điểm, và SUM để tính tổng hệ số, rồi lấy hai cái này chia nhau, sẽ có điểm trung bình. Công thức tại G4:G18 là (chọn cả khối, nhập công thức và nhấn Ctrl+Enter):
=SUMPRODUCT(C4:F4, K4:N4) / SUM(K4:N4)

Cũng đâu có phức tạp lắm, phải không bạn?

Thích đẹp hơn tí (và cũng để giấu nghề!) thì sau khi làm xong, bạn ẩn (Hide) hết mấy cột K,L,M,N đi là xong...
 
Mình biết để giải bài này thì cách của anhtuan là hay nhất,lẹ nhất và mình sẽ áp dụng cách đó. Mình chỉ muốn biết là nếu dùng công thức thông thường thì có công thức nào vừa ngắn lại vừa khả thi không thôi
Mình làm cho bạn theo cách bạn muốn dùng công thức và không cần vùng phụ,bạn tham khảo nhé
 

File đính kèm

Mình biết để giải bài này thì cách của anhtuan là hay nhất,lẹ nhất và mình sẽ áp dụng cách đó. Mình chỉ muốn biết là nếu dùng công thức thông thường thì có công thức nào vừa ngắn lại vừa khả thi không thôi, ai ngờ nó lại dài "khủng" như vậy @_@. Ok, nếu thêm 1 cột phụ thì giải quyết như thế nào, BNTT giải thử cho mình học hỏi kinh nghiệm ^^. Cám ơn trước nha.

xem file này có được không?
 

File đính kèm

Có thể là Đặt con trỏ chuột tại dòng 4, Ctrl + F3 để vào Define name và đặt 1 name tên HS như sau
PHP:
HS =OFFSET(OFFSET($A$22,MATCH(RIGHT($B4,1),$F$23:$F$31,0),,3),MATCH(LEFT($B4,LEN($B4)-2),OFFSET($A$22,MATCH(RIGHT($B4,1),$F$23:$F$31,0),,3),0)-1,1,1,4)
Hoặc
PHP:
HS =OFFSET(Ma,MATCH(LEFT($B4,LEN($B4)-2),Ma,0)-1,1,1,4)
Thì thêm name
Ma =OFFSET($A$22,MATCH(RIGHT($B4,1),$F$23:$F$31,0),,3)
To Boyxin
theo mình đặt thế này 1 name thôi
HS=OFFSET(Sheet1!$B$22:$E$22,MATCH(LEFT(Sheet1!$B4,LEN(Sheet1!$B4)-2),Sheet1!$A$23:$A$31,0)+MATCH(RIGHT(Sheet1!$B4,1),Sheet1!$F$23:$F$31,0)-1,)
 
Nói thật, mới đầu tôi tưởng cách làm thủ công của tôi là dài dòng, thế nhưng xem qua các bài các bạn đưa lên, thì mới thấy cách của tôi là chính xác và khá gọn (thao tác nhanh)...

Cho phép tôi nhận xét chút nha.

Bài của bác anhtuan1066 cũng khá gọn, nhưng không chính xác lắm. Ví dụ, kết quả ở G5, nếu làm theo kiểu thủ công, nó phải bằng:
= (C5*B26 + D5*C26 + E5*D26 + F5*E26)/(B26 + C26 + D26 + E26)
= (4*3 + 6*2 + 5*2 + 7*2) / (3+2+2+2) = 48 / 9 = 5.3333333
Nhưng bài của bác anhtuan1066 thì chỉ là 5.18 (sai số hơi nhiều)

Bài của Cao mạnh sơn thì phải đặt quá nhiều name, do đó thao tác lâu, và kết quả tại G5 cũng không đúng, bạn tính chỗ đó ra đến 5.4 (sai số cũng hơi nhiều). Vả lại, nếu đặt Name theo từng môn như bạn (mỗi môn 2 name), thì với vài chục môn thi, bạn cũng phải làm chừng đó cái Name (x 2) à? Mất công quá!

Bài của Boyxin thì cho ra kết quả chính xác, nhưng công thức ở cột điểm trung bình hơi dài (vì phải sử dụng nhiều VLOOKUP). Giả sử như số môn không phải là 4 mà là chừng vài chục môn thì công thức của bạn sẽ không sử dụng được, vì quá dài. Nếu bạn thêm một cột phụ A ở bảng tham chiếu, thì tôi nghĩ bạn thêm một loạt cột phụ như của tôi, sẽ nhanh hơn, và không sợ số lượng môn thi là bao nhiêu. Vì cách thêm một mảng phụ để tính hệ số như của tôi, nếu số lượng môn tăng lên, thì chỉ làm cho mảng phụ tăng lên, chứ công thức thì vẫn là như thế, không dài thêm.


Tóm lại, nếu như cho điểm phương pháp nào nhanh nhất và chính xác nhất, thì tôi chấm cách làm của bác anhtuan1066, nhưng phải thay cái Name HS của bác bằng cái Name của Cao mạnh sơn (ở bài #12):
=OFFSET(Sheet1!$B$22:$E$22,MATCH(LEFT(Sheet1!$B4,LEN(Sheet1!$B4)-2),Sheet1!$A$23:$A$31,0)+MATCH(RIGHT(Sheet1!$B4,1),Sheet1!$F$23:$F$31,0)-1,)​

Đây chỉ là góp ý về cách giải quyết vấn đề. Không có ý nói bài của ai hay hoặc dở. Mong các bạn đừng hiểu lầm.
 
Lần chỉnh sửa cuối:
Ví dụ, bạn bắt đầu từ cột K, vì có bốn môn nên ta dùng 4 cột K, L, M và N. Số hàng thì từ hàng 4 đến hàng 18.

Bạn chọn hết khối ô mới này (K4:N18), và nhập công thức đầu tiên mà tôi đã nói ở bài trước, công thức dùng để tìm ra hệ số, xong rồi nhấn Ctrl+Enter (để nhập một phát cho cả K4:N18):
=INDEX($B$23:$E$31, MATCH(LEFT($B4, LEN($B4) - 2), $A$23:$A$25, 0) + IF(RIGHT($B4) = "A", 0, IF(RIGHT($B4)= "B", 3, 6)), MATCH(C$3, $B$22:$E$22, 0))
Nếu bạn nhập đúng, thì trong K4:N18 sẽ cho ra kết quả là những con số hệ số (K4 = 4, L4 = 2, M4 = 3, v.v...


Bây giờ ta sẽ dùng SUMPRODUCT để tính tổng số điểm, và SUM để tính tổng hệ số, rồi lấy hai cái này chia nhau, sẽ có điểm trung bình. Công thức tại G4:G18 là (chọn cả khối, nhập công thức và nhấn Ctrl+Enter):
=SUMPRODUCT(C4:F4, K4:N4) / SUM(K4:N4)

Hiiii
  1. Phần công thức lấy hệ số vẫn có thể thu gọn tiếp để gọn hơn được
  2. Khi số môn thay đổi thì cũng vẫn phải thay lại công thức (cả phần lấy hệ số và phần tính TB)
    ----------------------------------------
  3. Ngay từ trên boyxin thấy name HS của anhtuan1066 chưa chuẩn nên đã đề nghị xem lại
  4. Giải pháp VLOOKUP là trả lời giúp BNTT (tác giả muốn nhìn thấy cách thêm ít cột) và dùng hàm quen thuộc với tác giả (boyxin đoán nhiều người biết VLOOKUP nên đưa lên 1 phương án)
  5. Nếu là boyxin thì chọn giải pháp đặt Name như bài #12 của caomanhson để thay cho name HS trong cách làm của anhtuan1066 (có chỉnh sửa chút để khi thay đổi số môn vẫn không phải thay lại công thức)
 
To Boyxin
theo mình đặt thế này 1 name thôi
HS=OFFSET(Sheet1!$B$22:$E$22,MATCH(LEFT(Sheet1!$B4,LEN(Sheet1!$B4)-2),Sheet1!$A$23:$A$31,0)+MATCH(RIGHT(Sheet1!$B4,1),Sheet1!$F$23:$F$31,0)-1,)

Với bài này thì cách đặt name như vậy là được (phần đầu của 3 loại mã có đuôi là A, B, C giống nhau)

Nhưng nếu phần đầu của các loại mã đó khác nhau thì cần xem lại

Cách của mình tuy hơi dài chút (muốn ngắn gọn thì cắt thành 2) sẽ chính xác hơn trong trường hợp như thế này
 
Sau vài ngày suy nghĩ, cuối cùng mình cũng tìm ra 1 cách giải cho riêng mình ^^. Đối với bài tập trên, cách đơn giản nhất và nhanh nhất là dùng công thức mảng, như thế nó sẽ không dài và không cần phải thêm cột phụ gì cả. Các bác xem rồi có gì góp ý cho em nha. Tại ô G4 nhập công thức sau:

G4=SUM(INDEX($B$22:$E$31;MATCH(RIGHT(B4:B18);$F$23:$F$31;0)+MATCH(LEFT(B4:B18;LEN(B4:B18)-2);$A$23:$A$25;0);0)*(C4:F4))/SUM(INDEX($B$22:$E$31;MATCH(RIGHT(B4:B18);$F$23:$F$31;0)+MATCH(LEFT(B4:B18;LEN(B4:B18)-2);$A$23:$A$25;0);0))
- Sau đó nhấn Ctrl+Shift+enter
 
Sau vài ngày suy nghĩ, cuối cùng mình cũng tìm ra 1 cách giải cho riêng mình ^^. Đối với bài tập trên, cách đơn giản nhất và nhanh nhất là dùng công thức mảng, như thế nó sẽ không dài và không cần phải thêm cột phụ gì cả. Các bác xem rồi có gì góp ý cho em nha. Tại ô G4 nhập công thức sau:

G4=SUM(INDEX($B$22:$E$31;MATCH(RIGHT(B4:B18);$F$23:$F$31;0)+MATCH(LEFT(B4:B18;LEN(B4:B18)-2);$A$23:$A$25;0);0)*(C4:F4))/SUM(INDEX($B$22:$E$31;MATCH(RIGHT(B4:B18);$F$23:$F$31;0)+MATCH(LEFT(B4:B18;LEN(B4:B18)-2);$A$23:$A$25;0);0))
- Sau đó nhấn Ctrl+Shift+enter
Rất sáng tạo, nhưng thừa một tí, bạn ơi.
Những cái tôi bôi đỏ ở trên, bạn sửa lại thành B4 (bỏ cái B18 đi) là đủ rồi.
 
Hiiii
  1. Phần công thức lấy hệ số vẫn có thể thu gọn tiếp để gọn hơn được
  2. Khi số môn thay đổi thì cũng vẫn phải thay lại công thức (cả phần lấy hệ số và phần tính TB)
    ----------------------------------------
To Boyxin:

1. Thu gọn nữa là làm sao? Mình thấy như vậy là quá gọn rồi (với công thức này). Boyxin có thể làm cho nó ngắn hơn sao?

2. Đồng ý là khi số môn tăng, thì phải thay đổi công thức, nhưng chỉ thay đổi tham chiếu thôi, chứ công thức vẫn là như vậy, không thêm gì hết, nói cách khác là nó vẫn ngắn nhiêu đó, không dài thêm. Còn công thức của Boyxin, nếu thêm số môn thì phải thêm một cái VLOOKP nữa, nên công thức sẽ dài ra. Boyxin đồng ý chứ ?


 
Web KT

Bài viết mới nhất

Back
Top Bottom