Hàm if kết hợp hàm or nhiều điều kiện (1 người xem)

caoquy80 · 3/6/13

Nhờ các anh chị Pro giúp mình giải quyết cách tính tổng thời gian và tách riêng thời gian thời gian từ 21h đến 5h sáng hôm sau:
VD thời gian bắt đầu 18h30', thời gian kết thúc 4h20' sáng hôm sau:
dùng hàm tính ra được 9,83 giờ, và 4,33h ca đêm(21h-:-5h).
C

Các Pro giúp mình lập lại công thức trong file có dung lượng 90.5KB tại Cột đánh dấu mầu vàng. Cột (N) và cột (O)

Hoàng Trọng Nghĩa · 3/6/13

caoquy80 đã viết:
Nhờ các anh chị Pro giúp mình giải quyết cách tính tổng thời gian và tách riêng thời gian thời gian từ 21h đến 5h sáng hôm sau:
VD thời gian bắt đầu 18h30', thời gian kết thúc 4h20' sáng hôm sau:
dùng hàm tính ra được 9,83 giờ, và 4,33h ca đêm(21h-:-5h).

Đọc hỏng hiểu gì cả???

1) Trong file của bạn cột 9 và cột 10 mỗi cột chia làm 2 cột, có phải 1 cột ghi giờ và một cột ghi phút?

2) Có phải bạn yêu cầu tính tổng số giờ bắt đầu đến giờ kết thúc bằng cách lấy giờ kết thúc trừ giờ bắt đầu?

ndu96081631 · 4/6/13

caoquy80 đã viết:
Nhờ các anh chị Pro giúp mình giải quyết cách tính tổng thời gian và tách riêng thời gian thời gian từ 21h đến 5h sáng hôm sau:
VD thời gian bắt đầu 18h30', thời gian kết thúc 4h20' sáng hôm sau:
dùng hàm tính ra được 9,83 giờ, và 4,33h ca đêm(21h-:-5h).

Công thức cho N6:

Mã:

=MOD((L6&":"&M6)+1-(J6&":"&K6),1)

Phần còn lại để... suy nghĩ tiếp xem (có cách nào ngắn gọn không?)
----------------
Thời gian thì người ta ghi chung vào 1 cell, chẳng hiểu sao bạn lại tách giờ riêng và phút riêng thế không biết?

Phuocam · 4/6/13

caoquy80 đã viết:
Nhờ các anh chị Pro giúp mình giải quyết cách tính tổng thời gian và tách riêng thời gian thời gian từ 21h đến 5h sáng hôm sau:
VD thời gian bắt đầu 18h30', thời gian kết thúc 4h20' sáng hôm sau:
dùng hàm tính ra được 9,83 giờ, và 4,33h ca đêm(21h-:-5h).

Bài của bạn mình xóa cột phút định dạng lại hh:mm nhưng cũng
chỉ làm được một cột, mong các thầy, cô giúp đỡ!

ndu96081631 · 4/6/13

phuocam đã viết:
Bài của bạn mình xóa cột phút định dạng lại hh:mm nhưng cũng
chỉ làm được một cột, mong các thầy, cô giúp đỡ!

Công thức của bạn:

Mã:

=IF(K6<J6,K6[COLOR=#ff0000][SIZE=4]+24[/SIZE][/COLOR]-J6,K6-J6)

+24 là sai nhé.
Lý ra phải +1 mới đúng

caoquy80 · 4/6/13

Đúng rồi ạ, giờ kết thúc trừ giờ bắt đầu ra tổng số giờ, cột kế tiếp là tách riêng số giờ từ 21h đến 5h sáng hôm sau

Phuocam · 4/6/13

ndu96081631 đã viết:
Công thức của bạn:

Mã:

=IF(K6<J6,K6[COLOR=#ff0000][SIZE=4]+24[/SIZE][/COLOR]-J6,K6-J6)

+24 là sai nhé.
Lý ra phải +1 mới đúng

Em cám ơn Thầy ! Theo hướng dẫn của thầy em đã làm xong, nhờ thầy kiểm tra giùm !
Công thức: =IF(OR(K6>(21/24),J6<(5/24),K6<J6),MIN(IF(OR(K6<J6,J6<(5/24)),K6+1,K6),29/24)-MAX(IF(J6<5/24,J6+1,J6),21/24),0)

siwtom · 4/6/13

caoquy80 đã viết:
Đúng rồi ạ, giờ kết thúc trừ giờ bắt đầu ra tổng số giờ, cột kế tiếp là tách riêng số giờ từ 21h đến 5h sáng hôm sau

Bạn gộp J và K thành J, L và M thành K. Tức J6 = 7:10, K6 = 22:15
Công thức cho M6

Mã:

=IF(J6>K6;MIN(K6;5/24)+1-MAX(J6;21/24);(5/24-J6)*(J6<=5/24)+(1-K6)*(K6>=21/24))

Phuocam · 4/6/13

siwtom đã viết:
Bạn gộp J và K thành J, L và M thành K. Tức J6 = 7:10, K6 = 22:15
Công thức cho M6

Mã:

=IF(J6>K6;MIN(K6;5/24)+1-MAX(J6;21/24);(5/24-J6)*(J6<=5/24)+(1-K6)*(K6>=21/24))

Cám ơn Bạn, công thức của bạn rất gọn. Nhưng mình coppy về thì kết quả có sự khác biệt ở dòng đầu tiên.
Nhờ bạn kiểm tra lại giùm !

siwtom · 4/6/13

phuocam đã viết:
Cám ơn Bạn, công thức của bạn rất gọn. Nhưng mình coppy về thì kết quả có sự khác biệt ở dòng đầu tiên.
Nhờ bạn kiểm tra lại giùm !

Xin lỗi. Nhầm.
2 phần có lôgíc như nhau: đk J6 <= 5/24 hay K6 >=21/24 thỏa thì lấy CÁI LỚN - CÁI BÉ
Có

Mã:

(5/24-J6)*(J6 <= 5/24)

Và phải là

Mã:

([B][COLOR=#ff0000]K6-21/24[/COLOR][/B])*(K6 >=21/24))

Công thức sửa thành

Mã:

=IF(J6>K6;MIN(K6;5/24)+1-MAX(J6;21/24);(5/24-J6)*(J6<=5/24)+([B][COLOR=#ff0000]K6-21/24[/COLOR][/B])*(K6>=21/24))

Chỗ đỏ đỏ là sửa lại.

Phuocam · 4/6/13

Mình đã coppy đoạn code của bạn siwtom nhưng chạy không đúng:
Nhờ bạn xem lại.
Xem dòng 7: bắt đầu 2:00 kết thúc 4:15 ->kết quả đúng là 2:15, code của bạn cho 3:00

siwtom · 4/6/13

phuocam đã viết:
Mình đã coppy đoạn code của bạn siwtom nhưng chạy không đúng:
Nhờ bạn xem lại.
Xem dòng 7: bắt đầu 2:00 kết thúc 4:15 ->kết quả đúng là 2:15, code của bạn cho 3:00

Chết thật. Đúng là hôm nay làm sai. Cám ơn bạn đã góp ý.
Để tôi xem lại
----------
Tôi nghĩ bây giờ công thức đúng - cho M6

Mã:

=IF(J6>K6;MIN(K6;5/24)+1-MAX(J6;21/24);(MIN(K6;5/24)-J6)*(J6<5/24)+(K6-MAX(21/24;J6))*(K6>21/24))

À, công thức cho L6

Mã:

=K6-J6+(K6<J6)

Để tôi xem có rút gọn được không
--------------
Nếu đặt 2 name (trước tiên chọn ô ở dòng 6)
max

Mã:

=MAX(21/24;$J6)

min

Mã:

=MIN($K6;5/24)

thì công thức cho M6

Mã:

=IF(J6>K6;min+1-max;(min-J6)*(J6<5/24)+(K6-max)*(K6>21/24))

-------------
Đúng là hơi chủ quan nên phạm lỗi te tua. Tới chỗ này cà chua thối trứng thối bay vù vù lên sân khấu về phía siwtom.
Âu cũng là một bài học.

Phuocam · 5/6/13

Code của bạn chạy rất tốt, tuy nhiên có một vấn đề nhỏ là: Khi 2 cell bắt đầu và kết thúc nhận giá trị rỗng (xóa hay không ghi gì) thì kết quả trả về 5.

siwtom · 5/6/13

phuocam đã viết:
Code của bạn chạy rất tốt, tuy nhiên có một vấn đề nhỏ là: Khi 2 cell bắt đầu và kết thúc nhận giá trị rỗng (xóa hay không ghi gì) thì kết quả trả về 5.

Cái này thì tôi biết nhưng cứ để thế.

Nhiều khi ta viết công thức để kéo tới khi nào có lỗi thì thôi. Thực ra có thể thêm chút để biến chúng thành ô trống. Không viết thêm có thể vì lười hoặc cho là không cần thiết làm công thức rắc rối thêm.

Nhưng cũng có thể biến thành ô = 0:00. Các ô 0:00 như thế chắc chắn là không nhiều. Và định dạng để giấu chúng thì cũng không khó.

vd. sửa như sau thì sẽ biến thành ô 0:00 khi giờ đầu và cuối không có hoặc đã bị xóa

Mã:

=IF(J6>K6;MIN(K6;5/24)+1-MAX(21/24;J6);(MIN([SIZE=4][B][COLOR=#ff0000]--[/COLOR][/B][/SIZE]K6;5/24)-J6)*(J6<5/24)+(K6-MAX(21/24;J6))*(K6>21/24))

Chỗ đỏ đỏ là mới thêm vào.

Cũng cần nói thêm là nếu ta chỉ có giờ bắt đầu mà không có giờ kết thúc hoặc ngược lại thì công thức vẫn trả về kết quả, có thể <> 0 nhưng kết quả đó không có giá trị. Vì rõ ràng với dữ liệu chỉ có giờ bắt đầu mà không có giờ kết thúc hoặc ngược lại là dữ liệu sai, dữ liệu vô giá trị.

Nếu bạn cần thì việc biến các ô lỗi thành trống chắc chắn bạn làm được. Nhưng tôi tin là bạn không cần.
Tôi viết code chơi thôi chứ không định thi thố gì. Ngắn hơn chút hay dài hơn chút đâu có quan trọng gì?

ptm0412 · 5/6/13

Trước tiên tính cột tổng thời gian:

L6 =K6+(K6<J6)-J6

Chia tổng thời gian thành 3 giai đoạn: Trước 21:00, từ 21:00 đến 5:00, và sau 5:00. Vậy chỉ cần tính 2 số rồi lấy tổng trừ 2 số đó sẽ ra số thứ 3.

trước 21:00 =MAX(21/24-J6-(J6<K6),0)

sau 05:00 =MAX(K6-5/24,0)

Khoảng ở giữa:

=L6 - MAX(21/24-J6-(J6<K6),0) - MAX(K6-5/24,0)

Phuocam · 5/6/13

ptm0412 đã viết:
Trước tiên tính cột tổng thời gian:

L6 =K6+(K6<J6)-J6

Chia tổng thời gian thành 3 giai đoạn: Trước 21:00, từ 21:00 đến 5:00, và sau 5:00. Vậy chỉ cần tính 2 số rồi lấy tổng trừ 2 số đó sẽ ra số thứ 3.

trước 21:00 =MAX(21/24-J6-(J6<K6),0)

sau 05:00 =MAX(K6-5/24,0)

Khoảng ở giữa:

=L6 - MAX(21/24-J6-(J6<K6),0) - MAX(K6-5/24,0)

Trong công thức của bạn tận dụng luôn L6 nên rất gọn. Cám ơn bạn !

siwtom · 5/6/13

ptm0412 đã viết:
Trước tiên tính cột tổng thời gian:

L6 =K6+(K6<J6)-J6

Chia tổng thời gian thành 3 giai đoạn: Trước 21:00, từ 21:00 đến 5:00, và sau 5:00. Vậy chỉ cần tính 2 số rồi lấy tổng trừ 2 số đó sẽ ra số thứ 3.

trước 21:00 =MAX(21/24-J6-(J6<K6),0)

sau 05:00 =MAX(K6-5/24,0)

Khoảng ở giữa:

=L6 - MAX(21/24-J6-(J6<K6),0) - MAX(K6-5/24,0)

Với mục đích test bạn chỉ cần lấy code của tôi ở bài #14 là đủ. Không hiểu bạn lấy tận 3 code của tôi: siwtom, siwtom2, siwtom3 để làm gì khi mà tôi đã viết rất rõ là tôi đã sai, khi mà tôi đã xin lỗi đàng hoàng, cám ơn người góp ý. Mặc dù nhiều người đã gán cho tôi một cái mác nhưng tôi không phải là người khăng khăng cãi chầy cối. Tôi biết nhìn ra cái sai của mình, biết tự cười mình (cà chua thối, trứng thối) biết thừa nhận cái lý của người khác. Tôi là người biết cám ơn và xin lỗi - tôi đã xin lỗi khi phạm lỗi, và cám ơn khi được người khác chỉ ra lỗi, xin lỗi và cám ơn bằng lời ít nhất là vài lần.
-------------
Bạn lại đi theo vết xe đổ của tôi rồi. Lỗi của chúng ta là quá chủ quan nên không suy nghĩ kỹ. Vấn đề không khó. Công thức cuối cùng của tôi ở bài #14 chắc chắn là đúng. Chỉ cần chú trọng một chút là được. Tất nhiên tôi không dự thi với chủ đề: "code ngắn nhất".

Bạn có thể kiểm tra code củac mình như tôi liệt kê sau đây. Các cột lần lượt là: giờ bắt đầu, giờ kết thúc, kết quả chuẩn, kết quả của bạn

Mã:

5:30    13:30    0    -0,02
13:30    21:30    0:30    -0,35
21:30    23:30    2:00    -0,69
5:00    21:30    0:30    0:00

Kết quả số âm bạn sẽ thấy khi định dạng lại ô kết quả là Number.

ptm0412 · 5/6/13

siwtom đã viết:
Với mục đích test bạn chỉ cần lấy code của tôi ở bài #14 là đủ. Không hiểu bạn lấy tận 3 code của tôi: siwtom, siwtom2, siwtom3 để làm gì khi mà tôi đã viết rất rõ là tôi đã sai, khi mà tôi đã xin lỗi đàng hoàng, cám ơn người góp ý. Mặc dù nhiều người đã gán cho tôi một cái mác nhưng tôi không phải là người khăng khăng cãi chầy cối. Tôi biết nhìn ra cái sai của mình, biết tự cười mình (cà chua thối, trứng thối) biết thừa nhận cái lý của người khác. Tôi là người biết cám ơn và xin lỗi - tôi đã xin lỗi khi phạm lỗi, và cám ơn khi được người khác chỉ ra lỗi, xin lỗi và cám ơn bằng lời ít nhất là vài lần.
-------------
Bạn lại đi theo vết xe đổ của tôi rồi. Lỗi của chúng ta là quá chủ quan nên không suy nghĩ kỹ. Vấn đề không khó. Công thức cuối cùng của tôi ở bài #14 chắc chắn là đúng. Chỉ cần chú trọng một chút là được. Tất nhiên tôi không dự thi với chủ đề: "code ngắn nhất".

Bạn có thể kiểm tra code củac mình như tôi liệt kê sau đây. Các cột lần lượt là: giờ bắt đầu, giờ kết thúc, kết quả chuẩn, kết quả của bạn

Mã:

5:30 13:30 0 -0,02 13:30 21:30 0:30 -0,35 21:30 23:30 2:00 -0,69 5:00 21:30 0:30 0:00

Kết quả số âm bạn sẽ thấy khi định dạng lại ô kết quả là Number.

Anh siwtom hiểu nhầm rồi, tôi lấy file minh họa cho công thức của tôi, chứ không nhằm vạch ra cái sai của người khác, nhất là anh. Tôi cũng sai chứ có toàn vẹn đâu, khi sai tôi cũng có xin lỗi nhưng còn không xin lỗi chân thành như anh.
Tôi có thử lấy file gốc của tác giả, nhưng file đó giờ và phút nằm riêng cột, tôi lại làm biếng nên tìm file ghép sẵn thành giờ phút đúng chuẩn. Tình cờ mà tôi lấy nhằm file trong đó có 3 cột của anh. Tôi rất tiếc vì đã gây hiểu lầm thế này.

Còn công thức tôi đưa ra không nhằm mục đích đua tranh ngắn dài, tôi muốn giới thiệu 1 cách lập luận khác, đơn giản hơn. Tôi đã test với 1 số trường hợp, nhưng có thể là bỏ sót và bị sai, tôi sẽ test lại. Cám ơn anh.

siwtom · 5/6/13

ptm0412 đã viết:
Anh siwtom hiểu nhầm rồi, tôi lấy file minh họa cho công thức của tôi, chứ không nhằm vạch ra cái sai của người khác, nhất là anh. Tôi cũng sai chứ có toàn vẹn đâu, khi sai tôi cũng có xin lỗi nhưng còn không xin lỗi chân thành như anh.
Tôi có thử lấy file gốc của tác giả, nhưng file đó giờ và phút nằm riêng cột, tôi lại làm biếng nên tìm file ghép sẵn thành giờ phút đúng chuẩn. Tình cờ mà tôi lấy nhằm file trong đó có 3 cột của anh. Tôi rất tiếc vì đã gây hiểu lầm thế này.

Chuyện so sánh thì là quá bình thường. Tôi chỉ thấy lạ là 3 code dùng để so sánh thì lại là 3 code tôi đã công nhận là sai trong khi code đúng thì lại không có. Bây giờ thì tôi đã hiểu được vì sao có tình trạng đó. Hóa ra là anh lười nên lấy tập tin có sẵn ở bài #13 của phuocam. Mà trong đó thì dĩ nhiên chỉ có 3 code sai mà không có code đúng vì code đúng ở bài #14.
Bây giờ sau bài viết của anh tôi mới dò lại các tập tin của phuocam thì mới thấy. Bởi vì trước đó tôi không tải tập tin nào của phuocam. Chỉ cần biết là với dữ liệu đã nêu trong bài thì code cho kết quả sai thì mình tự test trong tập tin của mình chứ cần gì phải tải tập tin của người khác.
Kể cũng lạ là nếu tôi đã đưa ra code siwtom3 thì trước đó tôi đã công nhận là các code trước đó là sai - vì nếu có ít nhất 1 code đúng thì đưa code khác làm gì. Vậy thì trong bài #13 chỉ cần có siwtom3 (cũng sai) chứ đâu cần siwtom, siwtom2 nhỉ. Chắc tác giả lười nên không xóa siwtom, siwtom2 chăng?

Dù sao anh cũng đã giúp tôi hiểu được từ đâu có tình trạng khó hiểu đối với tôi. Xin cám ơn anh.

Còn công thức tôi đưa ra không nhằm mục đích đua tranh ngắn dài, tôi muốn giới thiệu 1 cách lập luận khác, đơn giản hơn.

Cái "ngắn dài" là tôi muốn hướng tới bạn phuocam vì trong mấy bài bạn ấy có đề cập tới "ngắn dài" và "gọn". Vì thế tôi nghĩ bạn đó chú trọng "ngắn, dài, gọn ..."

Phuocam · 8/6/13

Bạn caoquy80! mình gửi bạn file này xài thử xem sao nhé! nếu có lỗi gì cú phản ảnh cho mình biết nhé!

siwtom · 8/6/13

phuocam đã viết:
Bạn caoquy80! mình gửi bạn file này xài thử xem sao nhé! nếu có lỗi gì cú phản ảnh cho mình biết nhé!

Chưa test kỹ, mới thử vài giá trị đầu thì:

1. Tập tin ở trên
View attachment 103641

rõ ràng cả 3 ô M6, M7, M8 đều sai

2. Tập tin dưới
View attachment 103642

rõ ràng cả 2 ô M7, M8 đều sai

xuanthuhcm · 29/4/15

các bác giúp em vụ này với:
ghep giúp em hai cái này thành 1:
[TABLE="class: cms_table, width: 133"]
[TR]
[TD]-CT1:
IF(OR(D5="HCM"),IF(B5<2.1,19500,IF(B5<10.1,((B5-2)*2530)+19500,IF(B5<30.1,(B5*2381),IF(B5<50.1,(B5 *2330),IF(B5<100.1,(B5*1900),IF(B5<500.1,(B5*1862) ,IF(B5<1000.1,(B5*1010),B5*760))))))))+IF(OR(D5="D NI",D5="BDG",D5="LAN",D5="TGG",D5="BTE",D5="TNH"), IF(B5<2.1,19500,IF(B5<10.1,((B5-2)*3160)+19500,IF(B5<30.1,(B5*3097),IF(B5<50.1,(B5 *2970),IF(B5<100.1,(B5*2400),IF(B5<500.1,(B5*2352) ,IF(B5<1000.1,(B5*2360),B5*2310))))))))+IF(OR(D5=" VTU",D5="BTN",D5="BPC",D5="DTP",D5="VLG",D5="TVH", D5="AGG",D5="KGG",D5="CTO",D5="HUG",D5="STG",D5="B LU"),IF(B5<2.1,19500,IF(B5<10.1,((B5-2)*3670)+19500,IF(B5<30.1,(B5*3597),IF(B5<50.1,(B5 *3450),IF(B5<100.1,(B5*3160),IF(B5<500.1,(B5*3097) ,IF(B5<1000.1,(B5*3100),B5*3040))))))))+IF(OR(D5=" HNI",D5="DNG"),IF(B5<2.1,19500,IF(B5<10.1,((B5-2)*5190)+19500,IF(B5<30.1,(B5*5086),IF(B5<50.1,(B5 *4880),IF(B5<100.1,(B5*4430),IF(B5<500.1,(B5*4341) ,IF(B5<1000.1,(B5*4330),B5*4210))))))))
- CT2:[TABLE="class: cms_table, width: 133"]
[TR]
[TD="width: 133"]IF(OR(D5="NAN",D5="HTH",D5="LSN",D5="HPG",D5="HDG" ,D5="HYN",D5="HNM",D5="NDH",D5="TBH",D5="BNH",D5=" BGG",D5="YBN",D5="BKN",D5="PHO",D5="DKG",D5="LDG", D5="NTN",D5="HUE",D5="QTI",D5="GLI",D5="PYN",D5="K HA",D5="QBH",D5="QNM",D5="QNI",D5="BDH",D5="YBI",D 5="LCU",D5="BCN"),IF(B5<2.1,19500,IF(B5<10.1,((B 5-2)*6260)+19500,IF(B5<30.1,(B5*5831),IF(B5<50.1,(B5 *5540),IF(B5<100.1,(B5*5200),IF(B5<500.1,(B5*5096) ,IF(B5<1000.1,(B5*5070),B5*4810))))))))+(IF(OR(D5= "CBG",D5="TNN",D5="DBN",D5="TQG",D5="SLA",D5="LCI" ,D5="HGG",D5="VPC",D5="PHO",D5="HBH",D5="NBH",D5=" THA",D5="CMU",D5="QNH",D5="LDG",D5="DLK",D5="KTM") ,IF(B5<2.1,19500,IF(B5<10.1,((B5-2)*6260)+19500,IF(B5<30.1,(B5*5831),IF(B5<50.1,(B5 *5540),IF(B5<100.1,(B5*5200),IF(B5<500.1,(B5*5096) ,IF(B5<1000.1,(B5*5070),B5*4810)))))))))[/TD]
[/TR]
[/TABLE]

[/TD]
[/TR]
[/TABLE]

Hàm if kết hợp hàm or nhiều điều kiện (1 người xem)

Người dùng đang xem chủ đề này

Thành viên mới

File đính kèm

Chuyên gia GPE

Huyền thoại GPE

Thành viên mới

File đính kèm

Huyền thoại GPE

Thành viên mới

Thành viên mới

File đính kèm

Thành viên gắn bó

Thành viên mới

Thành viên gắn bó

Thành viên mới

Thành viên gắn bó

Thành viên mới

Thành viên gắn bó

Bad Excel Member

File đính kèm

Thành viên mới

Thành viên gắn bó

Bad Excel Member

Thành viên gắn bó

Thành viên mới

File đính kèm

Thành viên gắn bó

Thành viên mới

Thời gian đếm ngược.