Mình không hiểu phải dùng công thức nào?

[phamtienk49]

Mình gặp bài tập này trong một cuốn sách về Excel nhưng phần công thức lời giải của họ thì mình lại không hiểu được. Mình rất mong các bạn cho mình biết trong trường hợp này thì phải dùng công thức nào và tại sao lại dùng như vậy. Mình thật sự xin lỗi vì mình đã quên gỡ bỏ passwords của bài tập rồi nhưng passwords của nó là 1984 và mình lại để định dạng ở office 2007 nên các bạn lưu ý giùm nghen. Ở trong mỗi ô tính tiền thưởng các tháng thì chỉ dùng một công thức thôi. Mong các bạn giải giúp mình mình xin chân thành cảm ơn!

 

[DOSNET]

Nếu bạn dùng Ex2007 thì trong bài này của bạn dùng hàm SUMIFS (cộng nhiều điều kiện) là được. Nếu bạn dùng Ex2003 thì dùng hàm SUMPRODUCT để thay thế, Hàm SUMIFS thì dễ dùng rồi, bạn tự nghiên cứu, tôi dùng Sumproduc và kết hợp Name để giải bài này cho bạn:
TIEN=SUMPRODUCT((MONTH($A$2:$A$11)=VALUE(RIGHT($C$21)))*1,($C$2:$C$11=A22)*1,$F$2:$F$11)
Bạn nghiên cứu thêm về cách dùng hàm Sumproduct nhé.

 

[BNTT]

Xin bổ sung công thức của Phamnhukhang một chút, để làm một lần cho cả 4 ô tính tiền luôn (chọn C22:F23, gõ công thức, rồi nhấn Ctrl-Enter):

= SUMPRODUCT(($C$2:$C$11 = $A22)*(MONTH($A$2:$A$11) = RIGHT(C$21,1) * 1))*($F$2:$F$11))​

Và xin giải thích cho bạn PhamTienK49 hiểu:

Trong trường hợp cộng theo điều kiện, ta dùng SUMIF.
Còn trong trường hợp cộng theo nhiều điều kiện, thì dùng SUMIFS (của Excel 2007) hoặc dùng SUMPRODUCT. Ở đây chúng tôi giới thiệu SUMPRODUCT vì số người sử dụng Excel 2007 chưa nhiều, mà SUMPRODUCT thì vẫn chạy tốt với Excel 2007.

Hàm SUMPRODUCT() ở trên đây gồm có 3 nhóm, 2 nhóm đầu là điều kiện và nhóm cuối cùng là nơi lấy kết quả. Mỗi nhóm đặt trong một cặp dấu ngoặc đơn và nối với nhau bằng dấu nhân (*)

• $C$2:$C$11=$A22 : Buộc trị trong $C$2:$C$11 = $A22 (Số 1 hoặc Số 2). Dùng $A để giữ cột A lại khi copy công thức cho cả 4 ô.

• MONTH($A$2:$A$11)= RIGHT(C$21,1)*1 : Buộc tháng trong $A$2:$A$11 bằng số cuối cùng của "Thưởng tháng ..." (C21 và E21).
  Ở đây nhân thêm với 1 để bảo đảm con số này là một giá trị số. Bạn PhamNhuKhang ơi, đã nhân với 1 rồi thì không cần dùng VALUE() nữa.

• $F$2:$F$11 : Khối cell dùng để lấy số tiền thưởng

Tương tự, để tính số tiền thưởng theo mặt hàng (bánh, mứt, kẹo), dùng hàm sau cho cả 6 ô (chọn C26:F28, gõ công thức, rồi nhấn Ctrl-Enter):

= SUMPRODUCT(($D$2:$D$11 = $A26)*(MONTH($A$2:$A$11) = RIGHT(C$25,1) * 1)*($F$2:$F$11))
​

Chỉ thay $C$2:$C$11 (cửa hàng) bằng $D$2:$D$11 (sản phẩm)

Đây là kết quả của bài toán này:

 

[DOSNET]

...Bạn PhamNhuKhang ơi, đã nhân với 1 rồi thì không cần dùng VALUE() nữa.

Bình thường chỉ cần *1 sẽ định dạng được thành kiểu số nhưng trong trường hợp này Hàm Sumproduct sẽ thực hiện phép so sánh trước để đưa ra kết quả là mảng {TRUE, FALSE...}sau đó mới *1 để đưa ra các mảng {0,1,0,1...}. Nếu không dùng Value sẽ bị sai ngay từ khi lọc điều kiện, kết quả chỉ trả về mảng {FALSE, FALSE...} do không so sánh được vì khác định dạng giữa kiểu số và kiểu text.
- Hàm RIGHT(C21) sẽ mặc định lấy 01 ký tự bên phải nên không cần RIGHT(C21,1).

 

[BNTT]

Bình thường chỉ cần *1 sẽ định dạng được thành kiểu số nhưng trong trường hợp này Hàm Sumproduct sẽ thực hiện phép so sánh trước để đưa ra kết quả là mảng {TRUE, FALSE...}sau đó mới *1 để đưa ra các mảng {0,1,0,1...}. Nếu không dùng Value sẽ bị sai ngay từ khi lọc điều kiện, kết quả chỉ trả về mảng {FALSE, FALSE...} do không so sánh được vì khác định dạng giữa kiểu số và kiểu text.

Nhưng trong trường hợp này là trường hợp nào bạn nhỉ? Trong đề bài này à? Thì bạn cứ coi cái công thức của tôi đấy, đâu có dùng VALUE(), nó vẫn chạy phà phà đấy mà.
Lúc đầu, tôi làm = VALUE(RIGHT(C$21,1), nhưng nó chạy trật lất, sau tôi sửa lại giống bạn, là = VALUE(RIGHT(C$21,1)) * 1, thì chạy ngon, sau tôi thử bỏ VALUE() đi, chỉ còn = RIGHT(C$21,1) * 1 thôi, vẫn tốt bạn à. Nên tôi mới nói thế chứ. Bạn thử lại xem.
Tôi chỉ đơn giản rằng, bất cứ một con số nào dạng text mà đem nhân với 1 thì nó thành dạng số liền.

 

[DOSNET]

Oh có lẽ cấu trúc hai công thức của bác và của em khác nhau do bác đã rút gọn nên có việc ưu tiên phép tính *1 trước...nên mới bỏ được Value() trong công thức của bác. Trong công thức của em không thể bỏ được Value vì nguyên nhân như em đã nói.

 

[minhlev]

Bác phamnhukhang ơi, vẫn có thể thay VALUE bằng '*1' nhưng bác phải thêm '*1' vào ngay chỗ hàm RIGHT cơ. Cụ thể:

TIEN=SUMPRODUCT((MONTH($A$2:$A$11)=RIGHT($C$ 21)*1)*1,($C$2:$C$11=A22)*1,$F$2:$F$11)

Bởi vì theo tôi nhận thấy, 'RIGHT($C$ 21)*1)*1' cho giá trị tương đương với 'VALUE(RIGHT($C$ 21))'. Bác xem lại xem

 

[BNTT]

Tôi hiểu rồi...
PhamNhuKhang làm là dùng công thức để đặt tên cho name...
Rồi mới dùng name đó để làm công thức tính tiền.
Tôi không nói cách làm này là hay hay là dở, tôi chỉ thấy rằng nó... hơi mắc công. Và sai.

Bạn xem nhé:
Sau khi nhập công thức, dù nó không báo lỗi, nhưng cũng phải liếc liếc tí xem nó đúng không chứ.
Đáp số của bạn sai đấy. Bạn so với hình chụp trên bài của tôi post lúc nãy là thấy ngay thôi.

Ví dụ, trong bài của bạn, kết quả thưởng Kẹo của tháng 9 = 0 (ô E28). Bạn nhìn lên đề bài, hàng 10, ngày 3/9/2004, Kẹo được thưởng 22.5 cơ mà !

Có cách khác để thử nữa: Tổng số tiền thưởng theo cửa hàng và tổng số tiền thưởng theo sản phẩm phải bằng nhau, và phải bằng tổng số trong cột tiền thưởng trên đề bài, nghĩa là:

SUM(C22:F23) = SUM(C26:F28) = SUM(F2:F11) = 113​

Bài của bạn, SUM(C26:F28) = 121 ! vừa dư (=121) vừa thiếu (kẹo tháng 9 = 0)

 

[DOSNET]

Vâng đúng như Minhlev nói, công thức của tôi nếu bỏ Value thì phải thêm *1 trước RIGHT, và cũng ko thể bỏ *1 sau hàm RIGHT. Đúng là đặt name thì hơi mắc công một chút do ban đầu tôi đã tính đến chuyện không cần dùng đến cột phụ tính % tiền thưởng, kết hợp giữa hàm IF và Name để tính luôn số tiền thưởng. Quay lại đầu bài đã tính sẵn rồi nên cũng để nguyên không sửa lại. Chính xác như bác BNTT đã check, do không cẩn thận khóa công thức trong name TIEN3 nên khi kéo công thức, dữ liệu bị chạy dẫn đến sai số.