Tìm công thức tính tổng công có điều kiện (1 người xem)

[xx123tcv]

Chào anh chị,
Em muốn tính tổng công có điều kiện như sau:
1. Nếu ngày có call>=50 và talktime >100 thì tính 1 công
2. Nếu ngày chỉ đạt 1 trong 2 điều kiện trên thì tính 0.5 công
3. Nếu không đạt cả 2 đk trên thì không tính công ngày đó
Từ đó tính tổng công trong tháng.
Em có gửi dữ liệu đính kèm, anh chị giúp em đặt công thức với ạ. Em cảm ơn nhiều.

 

[quocgiacan]

Chào anh chị,
Em muốn tính tổng công có điều kiện như sau:
1. Nếu ngày có call>=50 và talktime >100 thì tính 1 công
2. Nếu ngày chỉ đạt 1 trong 2 điều kiện trên thì tính 0.5 công
3. Nếu không đạt cả 2 đk trên thì không tính công ngày đó
Từ đó tính tổng công trong tháng.
Em có gửi dữ liệu đính kèm, anh chị giúp em đặt công thức với ạ. Em cảm ơn nhiều.

Chào xx123tcv,

Bạn thử xem sao nha:

P2=SUMPRODUCT((--C2:O2>=50)+(C3:O3>100))*0.5

Enter.

Chúc bạn ngày vui.

 

[xx123tcv]

Cảm ơn anh. Anh có thể cho em xem lại công thức chưa rút gọn được không ạ?

 

[quocgiacan]

Cảm ơn anh. Anh có thể cho em xem lại công thức chưa rút gọn được không ạ?

Khà khà khà!

Tôi thấy nó dài quá, đêm khuya không ngũ được lăn qua lộn lại, dậy sửa công thức lúc 12:28h, và "quăng" cái cũ mất tiêu rồi! thông cảm nha.

Công thức trên có chỗ nào bạn còn gút mắc không! tôi sẽ giải đáp cho bạn?

Chúc bạn ngày vui.

 

[nguyen6571gpex]

Bạn quocgiacan cho hỏi: dấu -- trước C2 có ý nghĩa gì? cảm ơn!

 

[xx123tcv]

Cái dấu -- có nghĩa là gì ạ, nếu em xóa dấu đó thì nó ra kết quả là 7.

 

[quocgiacan]

Cái dấu -- có nghĩa là gì ạ, nếu em xóa dấu đó thì nó ra kết quả là 7.

À, dấu -- tương đương với hàm VALUE(). Do dữ liệu của hàng "call" của bạn là dạng ký tự chuỗi, nên mình phải đổi về dạng "giá trị số" mới so sánh đúng được.

Bạn thử thay dấu -- bằng VALUE() đi nó cũng ra kết quả giống nhau.
P2=SUMPRODUCT((VALUE(C2:O2)>=50)+(C3:O3>100))*0.5


Chúc bạn ngày vui.

 

[VetMini]

Đó không phải là 1 dấu, mà nó là chính xác 2 dấu trừ.
Đây là một xảo thuật lợi dụng phép ép kiểu của con toán Excel. Theo nguên tắc, Excel sẽ tự ép kiểu khi cần thiết để làm việc, ví dụ nếu cần cộng trừ nhân chia 2 chuỗi "1" + "2" với nhau thì nó tự biết ép kiểu từ chuỗi thành số để ra kết quả là 3 (*)

-- có nghĩa là đổi dấu 2 lần (nhân cho -1 hai lần, trở lại số cũ). Mục đích là lúc đổi dấu, Excel tự dộng ép kiểu biểu thức thành số. Và ngừoi ta thường dùng để ép kiểu TRUE thành 1 và FALSE thành 0.
Có vài cách ép kiểu khác, ví dụ +0. Nhưng -- là cách gọn và hiệu quả nhất.

(*) chính vì vậy mà Excel phải phân biệt giữa phép cộng (+) và phép nối chuỗi (&). Ngược lại nếu bạn dùng phép nối chuỗi để cộng 2 số 1 & 2 thì Excel sẽ tự ép thành chuỗi để ra kết quả "12"

Lưu ý: VALUE() là hàm đổi kiểu. Kết quả ra cũng vậy.

 

[quocgiacan]

Đó không phải là 1 dấu, mà nó là chính xác 2 dấu trừ.
Đây là một xảo thuật lợi dụng phép ép kiểu của con toán Excel. Theo nguên tắc, Excel sẽ tự ép kiểu khi cần thiết để làm việc, ví dụ nếu cần cộng trừ nhân chia 2 chuỗi "1" + "2" với nhau thì nó tự biết ép kiểu từ chuỗi thành số để ra kết quả là 3 (*)

-- có nghĩa là đổi dấu 2 lần (nhân cho -1 hai lần, trở lại số cũ). Mục đích là lúc đổi dấu, Excel tự dộng ép kiểu biểu thức thành số. Và ngừoi ta thường dùng để ép kiểu TRUE thành 1 và FALSE thành 0.
Có vài cách ép kiểu khác, ví dụ +0. Nhưng -- là cách gọn và hiệu quả nhất.

(*) chính vì vậy mà Excel phải phân biệt giữa phép cộng (+) và phép nối chuỗi (&). Ngược lại nếu bạn dùng phép nối chuỗi để cộng 2 số 1 & 2 thì Excel sẽ tự ép thành chuỗi để ra kết quả "12"

Lưu ý: VALUE() là hàm đổi kiểu. Kết quả ra cũng vậy.

Chào anh VetMini,

Rõ ràng, khúc chiết và ngọn ngành.

Chúc anh ngày thiệt vui.

 

[leoheocon]

chào xx123tcv,

bạn thử xem sao nha:

p2=sumproduct((--c2:o2>=50)+(c3:o3>100))*0.5

enter


Chúc bạn ngày vui.

Viết như vầy được không anh:

=sumproduct((c2:o3*1>={100.1;50})/2)

 

[quocgiacan]

Viết như vầy được không anh:

=sumproduct((c2:o3*1>={100.1;50})/2)

Có một chút gì đó chưa ổn, hình như nó chú trọng hàng dưới (talktime) hơn hàng trên (call).
Em xem lại file của anh thể hiện xem.

Chúc em ngày thiệt vui.

 

[leoheocon]

Có một chút gì đó chưa ổn, hình như nó chú trọng hàng dưới (talktime) hơn hàng trên (call).
Em xem lại file của anh thể hiện xem.

Chúc em ngày thiệt vui.

Haha,em để ngược thôi, chứ CT của anh và em là 1 mà:

=SUMPRODUCT((C2:O3*1>{49.5;100})/2)

Chúc anh ngày vui!!!

 

[quocgiacan]

Haha,em để ngược thôi, chứ CT của anh và em là 1 mà:

=SUMPRODUCT((C2:O3*1>{50.1;100})/2)

Chúc anh ngày vui!!!

À! anh hiểu rồi,

khà khà khà! cái 0.1 còn tùy chủ thớt có dữ liệu phù hợp, anh em mình cũng gặp dạng bài này rồi, không thì phải mà thêm 10^-n đằng sau 50 (đùa với em thôi).

Chúc em ngày vui

 

[leoheocon]

À! anh hiểu rồi,

khà khà khà! cái 0.1 còn tùy chủ thớt có dữ liệu phù hợp, anh em mình cũng gặp dạng bài này rồi, không thì phải mà thêm 10^-n đằng sau 50 (đùa với em thôi).

Chúc em ngày vui

Haha,cái này thì không sợ anh, đã Call (số lần gọi) thì không thể lẻ được, nên chỉ cần vậy là đủ!!!

 

[leoheocon]

À! anh hiểu rồi,

khà khà khà! cái 0.1 còn tùy chủ thớt có dữ liệu phù hợp, anh em mình cũng gặp dạng bài này rồi, không thì phải mà thêm 10^-n đằng sau 50 (đùa với em thôi).

Chúc em ngày vui

Đố anh, bài này nếu đổi điều kiện Call<50, thì có thể dùng CT của em được không anh!!!

 

[HieuCD]

À! anh hiểu rồi,
khà khà khà! cái 0.1 còn tùy chủ thớt có dữ liệu phù hợp, anh em mình cũng gặp dạng bài này rồi, không thì phải mà thêm 10^-n đằng sau 50 (đùa với em thôi).
Chúc em ngày vui

Haha,cái này thì không sợ anh, đã Call (số lần gọi) thì không thể lẻ được, nên chỉ cần vậy là đủ!!!

nhìn mấy con số nhảy múa quá hay, muốn chen vào phá đám nhưng không tìm ra cách
chúc các bạn một ngày vui

 

[HieuCD]

Đố anh, bài này nếu đổi điều kiện Call<50, thì có thể dùng CT của em được không anh!!!

còn 100 là <100 hay >100?

 

[leoheocon]

còn 100 là <100 hay >100?

Cái kia thì y cũ talktime>100 và Call<50

 

[Nguyễn Hồng Quang]

Mình có theo dõi topic này từ đầu, thấy khá hay. Vậy nếu giả sử ở đây cho thêm 1 điều kiện (tức là có 3 điều kiện) để chấm công thì công thức sẽ ra sao ạ
Ví dụ thêm điều kiện mức độ hài lòng
Giả sử Nguyên tắc chấm công mới: có Call>=50 và talktime >=100 và mức độ hài lòng >50% thì chấm 1 công; nếu thỏa mãn 2 trong 3 điều kiện thì tính 0,5 công nếu không thỏa mãn cả 3 điều kiện hoặc chỉ được 1 trong 3 điều kiện thì tính 0 công

 

[leoheocon]

Mình có theo dõi topic này từ đầu, thấy khá hay. Vậy nếu giả sử ở đây cho thêm 1 điều kiện (tức là có 3 điều kiện) để chấm công thì công thức sẽ ra sao ạ
Ví dụ thêm điều kiện mức độ hài lòng
Giả sử Nguyên tắc chấm công mới: có Call>=50 và talktime >=100 và mức độ hài lòng >50% thì chấm 1 công; nếu thỏa mãn 2 trong 3 điều kiện thì tính 0,5 công nếu không thỏa mãn cả 3 điều kiện hoặc chỉ được 1 trong 3 điều kiện thì tính 0 công

Giả sử thêm 1 dòng hài lòng nữa thì theo đk của bạn thì dùng CT này:

=SUM((IFERROR(1/(1/MMULT(TRANSPOSE($C$2:$O$4*1>={50;100;0.5})*1,{1;1;1})),1)-1)*0.5)

Ctrl+Shift+Enter!!!
Bài này mà xét từng trường hợp thì công thức dài lắm!!!

 

[quocgiacan]

Mình có theo dõi topic này từ đầu, thấy khá hay. Vậy nếu giả sử ở đây cho thêm 1 điều kiện (tức là có 3 điều kiện) để chấm công thì công thức sẽ ra sao ạ
Ví dụ thêm điều kiện mức độ hài lòng
Giả sử Nguyên tắc chấm công mới: có Call>=50 và talktime >=100 và mức độ hài lòng >50% thì chấm 1 công; nếu thỏa mãn 2 trong 3 điều kiện thì tính 0,5 công nếu không thỏa mãn cả 3 điều kiện hoặc chỉ được 1 trong 3 điều kiện thì tính 0 công

Tham gia vào cuộc vui:

=SUM(IF((--C2:O2>=50)+(C3:O3>100)+(C4:O4>50%)>1,((--C2:O2>=50)+(C3:O3>100)+(C4:O4>50%))-1)*0.5)

Ctrl+Shift+Enter

@leoheocon
Bài của em đố anh nghĩ không ra, tính lấy 2500/C2:O2 nữa kìa, khà khà khà. Chịu thua!

Chúc em ngày thiệt vui.
p/s: còn chờ một người em và bạn hiền nhập cuộc nữa là thành "tứ quái"

 

[HieuCD]

Cái kia thì y cũ talktime>100 và Call<50

không biết như thế nầy được không

=SUMPRODUCT((C2:O3*{-1;1}>{-49.5;100})/2)

 

[quocgiacan]

không biết như thế nầy được không

=SUMPRODUCT((C2:O3*{-1;1}>{-49.5;100})/2)

Khà khà khà!

Chịu thua bạn hiền rồi!

 

[leoheocon]

không biết như thế nầy được không

=SUMPRODUCT((C2:O3*{-1;1}>{-49.5;100})/2)

Hehe,chính xác rồi đó anh!!!

 

[leoheocon]

Tham gia vào cuộc vui:

=SUM(IF((--C2:O2>=50)+(C3:O3>100)+(C4:O4>50%)>1,((--C2:O2>=50)+(C3:O3>100)+(C4:O4>50%))-1)*0.5)

Ctrl+Shift+Enter

@leoheocon
Bài của em đố anh nghĩ không ra, tính lấy 2500/C2:O2 nữa kìa, khà khà khà. Chịu thua!

Chúc em ngày thiệt vui.
p/s: còn chờ một người em và bạn hiền nhập cuộc nữa là thành "tứ quái"

Oh,em tưởng đâu dùng IF phải xét từng trường hợp nhỉ, quên mất là có thể cộng được, nhìn dễ hiểu!!!
Chúc các anh ngày vui!!!
Ch

 

[HieuCD]

Tham gia vào cuộc vui:

=SUM(IF((--C2:O2>=50)+(C3:O3>100)+(C4:O4>50%)>1,((--C2:O2>=50)+(C3:O3>100)+(C4:O4>50%))-1)*0.5)

Ctrl+Shift+Enter
p/s: còn chờ một người em và bạn hiền nhập cuộc nữa là thành "tứ quái"

hình như vẫn còn lệch kết quả

góp thêm công thức kiểu nông dân, không phải nghĩ nhiều nhức đầu

=SUMPRODUCT(((C2:O2>=50)*(C3:O3>=100)+(C2:O2>=50)*(C4:O4>=0.5)+(C3:O3>=100)*(C4:O4>=0.5))-(C2:O2>=50)*(C3:O3>=100)*(C4:O4>=0.5))/2

chúc các bạn 1 ngày vui

 

[quocgiacan]

hình như vẫn còn lệch kết quả
..........

=SUMPRODUCT(((C2:O2>=50)*(C3:O3>=100)+(C2:O2>=50)*(C4:O4>=0.5)+(C3:O3>=100)*(C4:O4>=0.5))-(C2:O2>=50)*(C3:O3>=100)*(C4:O4>=0.5))/2

.....

Đúng rồi bạn hiền chẳng qua là bám theo yêu cầu của "trợ lý" chủ thớt (tức là phó thớt ) yêu cầu chỉ >50%, chứ mình cũng khoái cái vụ: >=50%

Dòm các công thức bắt đầu giống hình thù của "tứ quái" rồi, khà khà khà!

=SUMPRODUCT(((C2:O2>=50)*(C3:O3>=100)+(C2:O2>=50)*(C4:O4>=0.5)+(C3:O3>=100)*(C4:O4>=0.5))-(C2:O2>=50)*(C3:O3>=100)*(C4:O4>=0.5))/2 (Enter)
{=SUM(IF((--C2:O2>=50)+(C3:O3>100)+(C4:O4>=50%)>1,((--C2:O2>=50)+(C3:O3>100)+(C4:O4>=50%))-1)*0.5)} (Ctrl+Shift+Enter)
{=SUM((IFERROR(1/(1/MMULT(TRANSPOSE($C$2:$O$4*1>={50;100;0.5})*1,{1;1;1})),1)-1)*0.5)} (Ctrl+Shift+Enter)

Chúc anh em có ngày thiệt vui.