Hàm if với điều kiện dữ liệu có ô trống!

[forever_7610]

Em xin nhờ các bác cao thủ vào giúp em vấn đề này với ạ.
Em đang lập một công thức để kiểm soát số liệu theo từng ngày với các điều kiện cho trước.. Nếu 4 dòng liên tiếp có chứa số liệu thỏa mãn điều kiện cho trước thì sẽ hiện thông tin báo lỗi ở bên cạnh dòng cuối cùng có dữ liệu..giống như VD e làm đây ạ!

 

[batman1]

Thử xem. Tôi chỉ test 2 lần
Bỏ Merge, dữ liệu từ dòng 2.
Công thức cho C2

=IF(B2<=13,"",IF(COUNTIF(INDEX(B$2:B2,LOOKUP(2,1/(B$2:B2<>"")/(B$2:B2<=13),ROW($1:1))):B2,">13")>=4,"Loi",""))

 

[forever_7610]

Thử xem. Tôi chỉ test 2 lần
Bỏ Merge, dữ liệu từ dòng 2.
Công thức cho C2

=IF(B2<=13,"",IF(COUNTIF(INDEX(B$2:B2,LOOKUP(2,1/(B$2:B2<>"")/(B$2:B2<=13),ROW($1:1))):B2,">13")>=4,"Loi",""))

Tuyệt vời quá..e cảm ơn bác rất nhiều ạ! đúng đk em cần rồi bác ạ

 

[forever_7610]

Tuyệt vời quá..e cảm ơn bác rất nhiều ạ! đúng đk em cần rồi bác ạ

Bác ơi ví dụ em thay điều kiện so sánh với 1 số cố định thành so sánh với giá trị bất kì trong một cột khác chuyển đổi như thế nào ạ.e thử sửa nhưng toàn báo lỗi hoặc không ra. bác xem giúp em với ạ.

 

[batman1]

Bạn nên nói rõ yêu cầu ngay từ đầu và chính xác, đầy đủ. Tôi sẽ chỉ làm cho bạn 1 lần nữa.

Nhưng tôi không hiểu bạn có cột E và F để làm gì. Sao không nhập max và min vao 2 và chỉ 2 ô nào đó mà lại có cột E và F?

 

[forever_7610]

Bạn nên nói rõ yêu cầu ngay từ đầu và chính xác, đầy đủ. Tôi sẽ chỉ làm cho bạn 1 lần nữa.

Nhưng tôi không hiểu bạn có cột E và F để làm gì. Sao không nhập max và min vao 2 và chỉ 2 ô nào đó mà lại có cột E và F?

Hihi. Vì điều kiện nó dài em trình bày ở trong file Excel luôn cho bác dễ đọc ạ!
Thanks Bác nhé!

 

[batman1]

Hihi. Vì điều kiện nó dài em trình bày ở trong file Excel luôn cho bác dễ đọc ạ!
Cảm ơn Bác nhé!

Bài này thì có liên quan gì tới bài #1?
Nếu vấn đề thay đổi thì lập chủ đề mới.

Tôi có thể nhân nhượng là bài #4 là mở rộng chút ít của bài #1. Nếu bạn giải thích bài #4 thì ta đi tiếp, còn nếu là bài #6 thì hoặc lập chủ đề mới hoặc đợi người khác. Tôi không chơi trò chơi "sửa dần code khi yêu cầu thay đổi dần dần".

Tôi hỏi về bài #4 vì hiện tại tôi đoán mò như sau:

1. Thực ra các max và min ở cột E và F là không nhất thiết giống nhau. Hiện tại chúng giống nhau chẳng qua là bạn lười nhập dữ liệu nên bạn copy các dữ liệu xuống dưới.

2. Nếu hiểu theo cách 1, tức mỗi ô ở cột B có max và min của riêng mình ở cột E và F thì tôi đoán mò tiếp như sau:

a. Ta định nghĩa khái niệm
Nếu dòng nào ở B có giá trị nằm trong khoảng min-max "của mình" thì ô đó gọi là "tốt". Nếu ô là "tốt" hoặc trống thì ở cột C là trống.

Nếu dòng nào ở cột B có giá trị < min "của mình" thì ô đó gọi là ô "xấu1", ngược lại thì là ô "xấu2" (vì chắc chắn > max "của mình")

b. Cách tính.
Nếu có ít nhất 4 ô "xấu1" liên tiếp (với nghĩa là giữa chúng không có ô "tốt" và ô "xấu2") hoặc ít nhất 4 ô "xấu2" liên tiếp (với nghĩa là giữa chúng không có ô "tốt" và ô "xấu1") thì báo Lỗi ở cột C kể từ lần xuất hiện thứ 4 trở đi

Nếu có 2 ô "xấu" liên tiếp mà một ô là "xấu1" còn ô kia là "xấu2" thì báo lỗi ở cột D ứng với dòng có "xấu" thứ 2 (ở dưới)

Tôi đoán mò thế có đúng không? Nếu không đúng thì mô tả lại. Nhưng chỉ bàn về bài #4, không bàn về các bài khác với Mean, SD, hic hic, hi hi gì cả.

 

[forever_7610]

Bài này thì có liên quan gì tới bài #1?
Nếu vấn đề thay đổi thì lập chủ đề mới.

Tôi có thể nhân nhượng là bài #4 là mở rộng chút ít của bài #1. Nếu bạn giải thích bài #4 thì ta đi tiếp, còn nếu là bài #6 thì hoặc lập chủ đề mới hoặc đợi người khác. Tôi không chơi trò chơi "sửa dần code khi yêu cầu thay đổi dần dần".

Tôi hỏi về bài #4 vì hiện tại tôi đoán mò như sau:

1. Thực ra các max và min ở cột E và F là không nhất thiết giống nhau. Hiện tại chúng giống nhau chẳng qua là bạn lười nhập dữ liệu nên bạn copy các dữ liệu xuống dưới.

2. Nếu hiểu theo cách 1, tức mỗi ô ở cột B có max và min của riêng mình ở cột E và F thì tôi đoán mò tiếp như sau:

a. Ta định nghĩa khái niệm
Nếu dòng nào ở B có giá trị nằm trong khoảng min-max "của mình" thì ô đó gọi là "tốt". Nếu ô là "tốt" hoặc trống thì ở cột C là trống.

Nếu dòng nào ở cột B có giá trị < min "của mình" thì ô đó gọi là ô "xấu1", ngược lại thì là ô "xấu2" (vì chắc chắn > max "của mình")

b. Cách tính.
Nếu có ít nhất 4 ô "xấu1" liên tiếp (với nghĩa là giữa chúng không có ô "tốt" và ô "xấu2") hoặc ít nhất 4 ô "xấu2" liên tiếp (với nghĩa là giữa chúng không có ô "tốt" và ô "xấu1") thì báo Lỗi ở cột C kể từ lần xuất hiện thứ 4 trở đi

Nếu có 2 ô "xấu" liên tiếp mà một ô là "xấu1" còn ô kia là "xấu2" thì báo lỗi ở cột D ứng với dòng có "xấu" thứ 2 (ở dưới)

Tôi đoán mò thế có đúng không? Nếu không đúng thì mô tả lại. Nhưng chỉ bàn về bài #4, không bàn về các bài khác với Mean, SD, hic hic, hi hi gì cả.

E cảm ơn bác đã chỉ bảo ạ. Vì cái em hỏi bác là e đã tối giản đi theo ngôn ngữ phổ thông để bác đọc cho đỡ lằng nhằng bác ạ.khi đọc hướng dẫn của bác e sẽ tự sửa vào bài sau. Tại bác hỏi e là cột E,D để làm gì nên em phải trình bày đầy đủ ra. Ý của em là giá trị của cột min,max nó có thể thay đổi theo ngày nên em ko để thành một giá trị duy nhất. Giá trị min, max là giá trị -2sd với +2sd em đổi tên để dễ hình dung bác ạ.

 

[batman1]

E cảm ơn bác đã chỉ bảo ạ. Vì cái em hỏi bác là e đã tối giản đi theo ngôn ngữ phổ thông để bác đọc cho đỡ lằng nhằng bác ạ.khi đọc hướng dẫn của bác e sẽ tự sửa vào bài sau. Tại bác hỏi e là cột E,D để làm gì nên em phải trình bày đầy đủ ra. Ý của em là giá trị của cột min,max nó có thể thay đổi theo ngày nên em ko để thành một giá trị duy nhất. Giá trị min, max là giá trị -2sd với +2sd em đổi tên để dễ hình dung bác ạ.

Nhưng cuối cùng là thế nào? Trong bài #7 tôi có câu hỏi mà.

Hỏi lại: tôi đoán mò như ở bài #7 có đúng không?

Tất nhiên là tôi đang nói tới bài #4, mà trong đó chả có sd nào cả. Nếu bạn muốn bàn tới bài #6 thì đợi người khác vì lý do tôi đã đưa ra.

 

[forever_7610]

Dạ vâng. Bác đoán ở bài #7 là đúng rồi bác ạ. Bác giúp em bài #4 nhé.

 

[forever_7610]

Nhưng cuối cùng là thế nào? Trong bài #7 tôi có câu hỏi mà.

Hỏi lại: tôi đoán mò như ở bài #7 có đúng không?
Tất nhiên là tôi đang nói tới bài #4, mà trong đó chả có sd nào cả. Nếu bạn muốn bàn tới bài #6 thì đợi người khác vì lý do tôi đã đưa ra.

Dạ vâng. Như bác đoán ở bài #7 là đúng rồi bác ạ. Bác ơi bác giúp em nốt bài #4 nhé.
Em cảm ơn bác!

 

[batman1]

Dạ vâng. Như bác đoán ở bài #7 là đúng rồi bác ạ. Bác ơi bác giúp em nốt bài #4 nhé.

Nếu thế thì ...
Để đơn giản công thức thì dùng cột phụ L
Công thức cho L2

=IF(B2="","",IF(B2<F2,1,IF(B2>E2,2,0)))

Công thức cho C2

=IF(OR(L2="",L2=0),"",IF(COUNT(INDEX(L$2:L2,IFERROR(LOOKUP(2,1/(L$2:L2<>"")/(L$2:L2<>L2),ROW($1:1)),0)+1):L2)>=4,"Loi",""))

Công thức cho D2

=IFERROR(IF(OR(L2="",L2=0),"",IF(LOOKUP(3,L$1:L1)+L2=3,"Loi","")),"")

Copy các công thức xuống dưới

 

[forever_7610]

Nếu thế thì ...
Để đơn giản công thức thì dùng cột phụ L
Công thức cho L2

=IF(B2="","",IF(B2<F2,1,IF(B2>E2,2,0)))

Công thức cho C2

=IF(OR(L2="",L2=0),"",IF(COUNT(INDEX(L$2:L2,IFERROR(LOOKUP(2,1/(L$2:L2<>"")/(L$2:L2<>L2),ROW($1:1)),0)+1):L2)>=4,"Loi",""))

Công thức cho D2

=IFERROR(IF(OR(L2="",L2=0),"",IF(LOOKUP(3,L$1:L1)+L2=3,"Loi","")),"")

Copy các công thức xuống dưới

Ôi em cảm ơn "sư phụ" rất rất nhiều ạ.. Do lần đầu đăng bài nhờ các bác giải đáp nên nếu trình bày có chỗ nào chưa hiểu hay chưa đúng ý bác bỏ qua cho em nhé!

 

[donganhnguyen]

Ad mình có trường hợp này giúp mình nhé:
B1 (tiền chưa VAT): 10.000
C1 (tiền bao gồm VAT): 11.000
D1 (Thành Tiền): Nếu giá trị ô D1 lớn hơn 0 thì lấy giá trị ô C1 (11.000), Nếu nhỏ hơn 0 thì lấy giá trị ô B1 (10.000), Nếu 1 trong 2 giá trị B1, D1 không có thì trả về 0
Mình cảm ơn. Mong phản hồi sớm

 

[Phan Thế Hiệp]

D1 (Thành Tiền): Nếu giá trị ô D1 lớn hơn 0 thì lấy giá trị ô C1 (11.000), Nếu nhỏ hơn 0 thì lấy giá trị ô B1 (10.000), Nếu 1 trong 2 giá trị B1, D1 không có thì trả về 0

1/ D1 là ô chứa giá trị "Thành tiền", tức chí ít phải có số lượng nhân với đơn giá, trường hợp >0 thì chấp nhận, còn trường hợp "nhỏ" hơn 0 bạn có thể cho ví dụ trong trường hợp nào được không?
2/ Nếu bạn muốn kết quả sẽ trả ra ô D1, mà với điều kiện lại đi lấy giá trị của nó để làm so sánh, thì sẽ bị lỗi vòng lặp. Bạn có thể mường tượng như thể: con trăn đói đang tự nuốt đuôi của nó vậy.

Thân

 

[donganhnguyen]

1/ D1 là ô chứa giá trị "Thành tiền", tức chí ít phải có số lượng nhân với đơn giá, trường hợp >0 thì chấp nhận, còn trường hợp "nhỏ" hơn 0 bạn có thể cho ví dụ trong trường hợp nào được không?
2/ Nếu bạn muốn kết quả sẽ trả ra ô D1, mà với điều kiện lại đi lấy giá trị của nó để làm so sánh, thì sẽ bị lỗi vòng lặp. Bạn có thể mường tượng như thể: con trăn đói đang tự nuốt đuôi của nó vậy.

Thân

D1 là ô chứa giá trị "thành tiền" của 1 trong 2 ô B1 hoặc C1 ạ ad, vì:
- B1: thành tiền chưa bao gồm VAT -> thao tác NHẬP TAY (k có đơn giá hay số lượng)
- C1: thành tiền bao gồm VAT -> cũng thao tác NHẬP TAY (cũng k có đơn giá hay số lượng, nếu có cũng là = C1*1.1 = THÀNH TIỀN BAO GỒM VAT)
VD: B1: 10.000 -> C1: 10.000 * 1.1 = 11.000
-> 1.1 là điều kiện ở C1
Kết quả cuối: D1 = B1 (mình luôn để CT này):
Vì B1 đa số NHẬP TAY & đơn hàng giành cho loại chưa bao gồm VAT đa số nhiều hơn loại C1 -> cột thanh toán luôn mặc định D1 = B1, nên khi nào thanh toán gồm VAT -> D1 xóa CT -> NHẬP TAY = GIÁ TRỊ C1
Trân Trọng!

 

[Phan Thế Hiệp]

D1 là ô chứa giá trị "thành tiền" của 1 trong 2 ô B1 hoặc C1 ạ ad, vì:
- B1: thành tiền chưa bao gồm VAT -> thao tác NHẬP TAY (k có đơn giá hay số lượng)
- C1: thành tiền bao gồm VAT -> cũng thao tác NHẬP TAY (cũng k có đơn giá hay số lượng, nếu có cũng là = C1*1.1 = THÀNH TIỀN BAO GỒM VAT)
VD: B1: 10.000 -> C1: 10.000 * 1.1 = 11.000
-> 1.1 là điều kiện ở C1
Kết quả cuối: D1 = B1 (mình luôn để CT này):
Vì B1 đa số NHẬP TAY & đơn hàng giành cho loại chưa bao gồm VAT đa số nhiều hơn loại C1 -> cột thanh toán luôn mặc định D1 = B1, nên khi nào thanh toán gồm VAT -> D1 xóa CT -> NHẬP TAY = GIÁ TRỊ C1
Trân Trọng!

Bạn càng giải thích, thì tui càng bị quẩn

Nên đoán mò vậy:

D1=IF(C1,C1,B1)

Enter.

Thân

 

[donganhnguyen]

Hì. Nhờ bị quẩn mà đúng toàn tập ạ
Cảm ơn AD nhiều lắm.

 

[Phan Thế Hiệp]

Cảm ơn AD nhiều lắm

Không có chi!
Mà tui cũng là "dân trơn" chả phải là: "ad; ed ; od; id; ud; ức...." gì ráo, nên bạn nên tập: cứ xưng hô tiếng Việt bình dị với mọi người trên đây, bất kể là ai, kêu "bạn" là được rồi!

Thân

 

[donganhnguyen]

Mọi người ơi, giúp em với....
Tình hình là em muốn theo dõi cấp đồng phục chi tiết theo từng nhân viên:
Trong sheet cúi "CHI TIET NV":
- Ngày 19/08/2019 Xuất: áo bảo vệ... : 1
- Ngày 20/08/2019 Thu hồi: áo bảo vệ... : 1
Còn nợ = 0
Em muốn nó như xnt hàng hóa chi tiết vậy ạ, hàng đi - hàng về, còn nợ hay hết
Mong các bác giúp em vs ạ, e cần gấp, sắp nộp bc rồi, huhu
Em cảm ơn