Bác ấy chưa trả lời thì mình nói theo cách hiểu của mình nhé:
"Giải thích" những gì mình hiểu tức là học, hiểu và nhớ "Gấp đôi".
Em giải thích rất đúng, khen em giỏi và chịu khó! Cảm ơn em.
Theo đánh giá qua các bài em làm, em cũng thuộc diện nhạy bén và sẽ "khó nhằn" khi "đụng độ" sau này.
TEXT(MOD(A3:B3,1)*24-6,"[<0]\0;[<16];\16")
Nhờ Anh giải thích em đoạn này! Em đọc chưa hiểu lắm.
Anh rất yêu thích tinh thần học hỏi của em! cái gì không biết thì cứ mạnh dạn hỏi han, đừng vì ngại ngùng mà đánh mất cơ hội học hỏi. Anh sẽ cố gắng giải thích chi tiết, nhằm không chỉ riêng cho em mà còn cho các anh em khác, những ai cần tham khảo và cần đến công thức để sử dụng cho chính xác.
| GIẢI THUẬT CHO BÀI TOÁN TÍNH GIỜ VẬN HÀNH BẰNG CÔNG THỨC |
Giải thuật: Dồn 'Giờ Bắt đầu' và 'Giờ kết thúc' xét vào chung 1 ngày. Giải thuật gồm có 2 phần:
Phần 1: Dồn 'Giờ Bắt đầu' và 'Giờ kết thúc'
(anh đặt nôm na ý nghĩa của nó )
+ Lấy ngày của 'Giờ Bắt đầu vận hành thực tế' ('BĐ') trừ ngày của 'Giờ kết thúc vận hành thực tế' ('KT'), hiệu số của nó là các ngày có Trọn số giờ ('TSG') muốn tính.
+ Số chênh lệch = '1 ngày' coi như để dành đó, đem 2 giờ 'BĐ' và 'KT' xét chung trong ngày này.
Công thức: = ( INT('KT') - INT('BĐ') )* 'TSG'
+ Ví dụ 1: Theo bài cho 'Giờ vận hành ban ngày' quy định là từ 6h00 đến 22h00, tức 'TSG' bằng 16 tiếng.
Giả sử, máy có 'BĐ' từ 6h00 sáng ngày 19/09/2022 và 'KT' vào lúc 22h00 ngày 23/09/2022
- Từ 19/09/2022 là ngày thứ nhất, đến ngày 23/09/2022 là 5 ngày
- Theo công thức ta có: '23/09/2022 - 19/09/2022 = 4 ngày, 4 ngày này có 'TSG' 16 tiếng, tức '4 x 16 = 64 tiếng (KQ1.1)
- Chênh lệch 1 ngày (5 ngày - 4 ngày) để dành tính cho phần 2, cứ coi như từ ngày 19/09 đến ngày 22/09 thì thời gian vận hành đủ 16 tiếng, còn giờ 'BĐ': 6h00 và 'KT': 22h00 coi như nó phát sinh ngay trong ngày 23/09.
+ Ví dụ 2: Giả sử, máy có 'BĐ' từ 8h00 sáng ngày 19/09/2022 và 'KT' vào lúc 4h00 sáng ngày 23/09/2022
- Từ 19/09/2022 là ngày thứ nhất, đến ngày 23/09/2022 là 5 ngày
- Theo công thức ta có: '23/09/2022 - 19/09/2022 = 4 ngày, 4 ngày này có 'TSG' 16 tiếng, tức '4 x 16 = 64 tiếng (KQ1.2)
- Chênh lệch 1 ngày (5 ngày - 4 ngày) để dành tính cho phần 2, cứ coi như từ ngày 19/09 đến ngày 22/09 thì thời gian vận hành đủ 16 tiếng, còn giờ 'BĐ': 8h00 và 'KT': 4h00 coi như nó phát sinh ngay trong ngày 23/09.
Phần 2: Cộng thêm hoặc Giảm trừ số giờ 'BĐ' và 'KT'
Tính khoảng thời gian của giờ 'BĐ' và 'KT' so với Mốc giờ quy định, lấy 16 tiếng (của ngày chừa trên) gia giảm khoảng thời gian vừa tính.
+ Cộng thêm: khoảng thời gian giờ 'KT' so với 'Mốc giờ quy định bắt đầu'
+ Giảm trừ: khoảng thời gian giờ 'BĐ' so với 'Mốc giờ quy định bắt đầu'
Cả hai kết quả Gia(+) / Giảm(-) trên được lọc theo điều kiện:
1. Nếu kết quả âm (<0) thì trả về 0*
2. Nếu kết quả dương (>=0) và nhỏ hơn 16** (<16) thì hiện số chính nó
3. Nếu kết quả lớn hơn hoặc bằng 16 (>=16) thì trả về 16***
(*): trả về 0 tiếng tức giờ bắt đầu hoặc kết thúc vận hành thực tế < Mốc 6h quy định. Thời gian trước 6h chính là giờ vận hành ban đêm không cần tính đến.
(**): 16 là số tiếng của giờ vận hành ban ngày quy định từ 6h đến 22h cùng ngày.
(***): trả về 16 tiếng tức giờ bắt đầu hoặc kết thúc vận hành thực tế > Mốc 22h quy định,Vd: 22h30, 23h .v.v. Thời gian sau 22h chính là giờ vận hành ban đêm không cần tính đến.
Công thức: =SUMPRODUCT( TEXT( MOD(A3:B3,1)*24-6 , "[<0]\0;[<16];\16" ) * {-1,1} )
+ Ví dụ 1: Theo Vd.1 trên, coi như máy có 'BĐ' từ 6h00 sáng ngày 23/09 đến 22h00 ngày 23/09
- Cộng thêm: lấy 22h (23/9) - 6h (quy định) = (+) 16 tiếng, theo điều kiện 3 nó trả về 16 tiếng (KQ2.1a)
- Giảm trừ: lấy 6h (23/9) - 6h (quy định) = 0 tiếng, theo điều kiện 2 nó giữ giá trị 0 tiếng (KQ2.1b)
- Tổng số giờ Vận hành ban ngày = KQ1.1 + KQ2.1a - KQ2.1b = 64 + 16 - 0 = 80 tiếng
+ Ví dụ 2: Theo Vd.2 trên, coi như máy có 'BĐ' từ 8h00 sáng và 'KT' 4h00 sáng trong ngày 23/09
- Cộng thêm: 4h - 6h (quy định) = (-) 2 tiếng, theo điều kiện 1 nó sẽ trả về 0 tiếng (KQ2.2a)
- Giảm trừ: 8h - 6h (quy định) = (+) 2 tiếng, theo điều kiện 2 nó giữ giá trị 2 tiếng (KQ2.2b)
- Tổng số giờ Vận hành ban ngày = KQ1.2 + KQ2.2a - KQ2.2b = 64 + 0 - 2 = 62 tiếng
+ Ví dụ 3: Giả sử, máy có 'BĐ' từ 5h15 sáng ngày 19/09/2022 và 'KT' vào lúc 21h30 cùng ngày.
- (19/09/2022 - 19/09/2022) * 16 tiếng = 0 tiếng (KQ1)
- Cộng thêm: 21h30 - 6h (quy định) = (+) 15.50 tiếng, theo điều kiện 2 nó giữ giá trị 15.5 tiếng (KQ2)
- Giảm trừ: 5h15 - 6h (quy định) = (-) 0 tiếng 45 phút, theo điều kiện 1 nó trả về 0 tiếng (KQ3)
- Tổng số giờ Vận hành ban ngày = KQ1 + KQ2 - KQ3 = 0 + 15.5 - 0 = 15.5 tiếng
Giải thích điều kiện trong hàm TEXT()
Đúng như anh
@VetMini hướng dẫn, nó dùng để tránh lặp lại giá trị đang xét khi cần lọc với điều kiện:
--TEXT( MOD(A3:B3,1)*24-6 , "[<0]\0;[<16];\16" ) = IF( MOD(A3:B3,1)*24-6 < 0, 0, MIN( MOD(A3:B3,1)*24-6 , 16))
Vế trái chỉ có 1 lần hiện hàm MOD(), vế phải cần hiện 2 lần.
Cụm điều kiện: "[<0]\0;[<16];\16" mang ý nghĩa như các điều kiện 1, 2, 3 ở Phần 2 trên đã nói, tức nó xét theo trình tự:
1) x < 0
2) 0 <= x < 16
3) x >= 16
Để xét 1 giá trị bất kỳ trong 1 đoạn giá trị biết trước, hàm TEXT() quy định:
+ Nếu không ghi điều kiện nào (được thể hiện bằng hai dấu ngoặc "[ ]"), thì mặc định sẽ là: "Số dương ; Số âm ; Số 0"
+ Nếu có thể hiện điều kiện bằng hai dấu ngoặc "[ ]", thì nên tuần tự: " < Mốc đầu ; Mốc đầu <= và < Mốc cuối ; >= Mốc cuối". Nó cũng tương tự như cách dò theo mốc giá trị của (V-H)Lookup, Match(,,1): như dùng các hàm này với mảng dò {0,16} vậy và sắp xếp giá trị từ nhỏ đến lớn.
Riêng ký tự "\" nhằm quy định giá trị trả về khi thỏa điều kiện nằm trong hai ngoặc "[ ]"
Vd:
\0 : --> trả về ký tự "0"
\16 : --> trả về ký tự "16"
\. : --> trả về ký tự " . "
Một số ích lợi khi dùng hàm TEXT(), em xem thêm các bài theo link:
1/ Theo link bài #8 anh đưa trên.
2/ Thay vì dùng công thức:
=SUM(IF(A1-{0,5,10,18,32,52,80}*10^6>0,A1-{0,5,10,18,32,52,80}*10^6>0,0)*5%)
Hoặc:
=SUMPRODUCT((A1-{0,5,10,18,32,52,80}*10^6>0)*(A1-{0,5,10,18,32,52,80}*10^6))*5%
phải xét giá trị (A1-{0,5,10,18,32,52,80}*10^6 đến 2 lần, ta chỉ dùng 1 lần:
=SUM(TEXT(A1-{0,5,10,18,32,52,80}*10^6,"0.00;\0")*5%)
Chào cả nhà, mình có công thức tính thuế TNCN. Nhưng nếu mức lương trên 32 triệu thì bị lệch, dưới 32tr thì cả 2 công thức đều như nhau. Mình không biết sai chõ nào. Các bạn giúp mình tạo công thức đúng cho mọi tình huống lương cao hay thấp
www.giaiphapexcel.com
3/ Thay vì dùng công thức:
=IF(AND(B2>0,COUNTIF($B$2:$B$10000,B2)>2),COUNTIF($B$2:$B$10000,B2),0)
phải xét COUNTIF() dùng cả vùng B2:B10000 đến 2 lần, ta chỉ dùng 1 lần:
=IF(B2>0,TEXT(COUNTIF($B$2:$B$10000,B2),"[>2];;\0"),)
Em chào anh chị ! Giúp hộ em công thức tính tổng sản phẩm theo kho, ko dùng hàm Countif, như ảnh mô tả ạ. Em cảm ơn nhiều ạ !
www.giaiphapexcel.com
Còn nhiều bài nữa mà
@dazkangel hay xài và em cũng đã đọc qua.
Chúc em 1 ngày học và làm việc vui.
