8点40到9点30用公式怎么算
8\u70b940\u52309\u70b930\u7ecf\u8fc7\u4e86\u51e0\u65f6\u51e0\u520650\u5206\u949f\u3002
\u8fd9\u91cc\u53ef\u4ee5\u5206\u6b65\u8ba1\u7b97\uff1a
1\u30018\u70b940\u52309\u70b9\u7ecf\u8fc7\u4e869:00-8:40=20\u5206\u949f\uff1b
2\u30019\u70b9\u52309\u70b930\u7ecf\u8fc7\u4e869:30-9:00=30\u5206\u949f\uff1b
3\u30018\u70b940\u52309\u70b930\u7ecf\u8fc7\u4e86\uff1a20\u5206\u949f+30\u5206\u949f=50\u5206\u949f\u3002
\u6269\u5c55\u8d44\u6599\uff1a
\u6211\u56fd\u53e4\u4ee3\u5e38\u7528\u7684\u65f6\u95f4\u5355\u4f4d\uff1a
1\u3001\u523b\uff1a
\u5927\u7ea6\u897f\u5468\u4e4b\u524d\uff0c\u53e4\u4eba\u5c31\u628a\u4e00\u663c\u591c\u5747\u5206\u4e3a100\u523b\uff0c\u5728\u6f0f\u58f6\u7bad\u6746\u4e0a\u523b100\u683c\u3002\u6298\u5408\u6210\u73b0\u4ee3\u8ba1\u65f6\u5355\u4f4d\uff0c\u52191\u523b\u7b49\u4e8e14\u520624\u79d2\u3002\u201c\u767e\u523b\u5236\u201d\u662f\u4e2d\u56fd\u6700\u53e4\u8001\u3001\u4f7f\u7528\u65f6\u95f4\u6700\u957f\u7684\u8ba1\u65f6\u5236\u3002
2\u3001\u66f4\uff1a
\u6c49\u4ee3\u7687\u5bab\u4e2d\u503c\u73ed\u4eba\u5458\u5206\u4e94\u4e2a\u73ed\u6b21\uff0c\u6309\u65f6\u66f4\u6362\uff0c\u53eb\u201c\u4e94\u66f4\u201d\uff0c\u7531\u6b64\u4fbf\u628a\u4e00\u591c\u5206\u4e3a\u4e94\u66f4\uff0c\u6bcf\u66f4\u4e3a\u4e00\u4e2a\u65f6\u8fb0\u3002\u620c\u65f6\u4e3a\u4e00\u66f4\uff0c\u4ea5\u65f6\u4e3a\u4e8c\u66f4\uff0c\u5b50\u65f6\u4e3a\u4e09\u66f4\uff0c\u4e11\u65f6\u4e3a\u56db\u66f4\uff0c\u5bc5\u65f6\u4e3a\u4e94\u66f4\uff0c\u5176\u5bf9\u5e94\u5982\u4e0b\uff1a
\u4e00\u66f4\u5929\uff1a\u620c\u65f6\u300019:00-21:00
\u4e8c\u66f4\u5929\uff1a\u4ea5\u65f6\u300021:00-23:00
\u4e09\u66f4\u5929\uff1a\u5b50\u65f6\u300023:00-01:00
\u56db\u66f4\u5929\uff1a\u4e11\u65f6\u300001:00-03:00
\u4e94\u66f4\u5929\uff1a\u5bc5\u65f6\u300003:00-05:00
\u201c\u9f13\u89d2\u201d\u3001\u201c\u949f\u9f13\u201d\u90fd\u662f\u53e4\u65f6\u7528\u6765\u6253\u66f4\u7684\u5668\u5177\u3002
\u53c2\u8003\u8d44\u6599\u6765\u6e90\uff1a\u767e\u5ea6\u767e\u79d1-\u65f6\u95f4
10:40-8:30=2\u5c0f\u65f610\u5206\u949f
9点30看做8点90(=30+60)8-8=0
90-40=50
你看这样行吗?
绛旓細闂1锛8鐐圭殑瑙掑害鏄240掳锛屾椂閽堟瘡鍒嗛拡璧0.5掳锛屽垎閽堣蛋6掳锛岃8鐐瑰埌9鐐璧颁簡X鍒嗛挓鍚庨噸鍚堬紝鍏紡涓猴細240掳+0.5X=6X锛孹=43.6鍒嗛挓 闂2锛氬悓涓婂叕寮忎负锛屾儏鍐1鏃堕拡鍦ㄥ墠锛240掳+0.5X-15掳=6X锛孹=40.9鍒嗛挓 鎯呭喌2鏃堕拡鍦ㄥ悗锛240掳+0.5X+15掳=6X锛孹=46.3鍒嗛挓锛岀敱浜庨鐩姹傚湪8鐐瑰埌9鐐逛箣闂达紝...
绛旓細鍙互浣跨敤Frequency鐨勬暟缁勫舰寮忓疄鐜般傚涓嬪浘锛岄変腑0-23鐐瑰搴旂殑鍖哄煙锛岃緭鍏=FREQUENCY(MOD(B2:B52,1),ROW(1:23)/24) 骞舵寜Ctrl+Shift+鍥炶溅缁撴潫锛岄偅涔堟瘡涓椂娈电殑鏁伴噺灏卞悓鏃跺畬鎴愪簡銆
绛旓細绀烘剰鍥惧涓嬶細濡傚垵濮嬫椂闂村湪A3锛岀粨鏉熸椂闂村湪B3锛屽湪D3涓緭鍏鍏紡锛= TEXT(B3-A3,"hh灏忔椂mm鍒嗛挓")
绛旓細B2鍏紡:=IF(A2="","00:00",IF(A2-"9:00">0,TEXT(A2-"09:00","hh:mm"),"09:00")).---鍘熷洖绛旇闅愯棌浜:
绛旓細1銆佸鍥撅紝涓烘垜浠師濮嬬殑鑰冨嫟鏁版嵁锛屼笂鐝椂闂翠负9鐐閽燂紝涓嬬彮鏃堕棿涓18鐐30鍒嗭紝鏅氫簬9鐐规墦鍗″垯涓鸿繜鍒帮紝涓嬬彮鏃堕棿鏅氫簬18鐐30鍒嗗垯涓哄姞鐝傜幇鍦ㄩ渶瑕佽绠楀嚭鍛樺伐鐨勮繜鍒板拰鍔犵彮鏃堕棿宸2銆佸鍥撅紝鍦ㄥ崟鍏冩牸I2閲岃緭鍏ュ叕寮=IF(AND(D2=$D$2,G2>E2),G2-E2,0)銆鍏紡鐨鎰忔濇槸锛屽繀椤绘弧瓒冲簲鍑哄嫟涓轰笂鐝姸鎬佸拰绛惧埌鏃堕棿鏅氫簬...
绛旓細鍦–2涓緭鍏ユ垨澶嶅埗绮樿创涓嬪垪鍏紡 =B2-A2 鎴 =(B2-A2)*24 鍏紡鎵鍦ㄧ殑鍗曞厓鏍煎垎鍒缃椂闂存牸寮忋佸父瑙勩
绛旓細閫氳揪淇¢夎偂鍏紡閫夎偂锛岄渶瑕佷笅杞藉巻鍙叉暟鎹紝鐐瑰嚮涓荤晫闈-绯荤粺-鐩樺悗鏁版嵁涓嬭浇-锛岀涓娆′笅杞芥渶濂借缃竴涓嬫椂闂达紝浠1990骞1鏈1鏃ュ紑濮嬶紝鐩村埌褰撳ぉ鏀剁洏鍖洪棿锛屾勃娣卞垎閽熷浘鍙兘涓嬭浇鏈杩3涓湀鐨勩傚疄璺垫樉绀猴細1銆佹瘡涓紑鐩樹氦鏄撴棩8鐐50鑷9鐐30涔嬮棿锛屽叕寮忛夎偂浼氬け璐ワ紝鏃犺浠涔堝叕寮忛夎偂锛岄夊嚭缁撴灉涓0锛屾墍浠ヨ閬垮紑杩欎釜鏃堕棿娈点2...
绛旓細8鐐瑰埌9鐐鏄竴涓皬鏃讹紝涓涓皬鏃舵槸60鍒嗛挓锛60-40=20 銆傜瓟锛氳繕瑕佺瓑20鍒嗛挓銆傚姞娉曟硶鍒欙細鍦ㄥ姞娉曟垨鑰呭噺娉曚腑浣跨敤鈥滄埅浣嶆硶鈥濇椂锛岀洿鎺ヤ粠宸﹁竟楂樹綅寮濮嬬浉鍔犳垨鑰呯浉鍑忥紙鍚屾椂娉ㄦ剰涓嬩竴浣嶆槸鍚﹂渶瑕佽繘浣嶄笌閿欎綅锛夛紝鐭ラ亾寰楀埌閫夐」瑕佹眰绮惧害鐨勭瓟妗堜负姝傚湪涔樻硶鎴栬呴櫎娉曚腑浣跨敤鈥滄埅浣嶆硶鈥濇椂锛屼负浜嗕娇鎵寰楃粨鏋滃敖鍙兘绮剧‘锛岄渶瑕佹敞鎰...
绛旓細閭d箞杩斿洖"A"锛屽惁鍒欒繑鍥"C"銆傝緭鍏ュ叕寮忓悗锛岀洿鎺ユ寜鍥炶溅閿垨鑰鐐瑰嚮鍏紡鏍忎笂鐨勮绠鎸夐挳锛孊1鍗曞厓鏍煎氨浼氭樉绀哄嚭鍒ゆ柇缁撴灉锛"A"琛ㄧず褰撳墠鏃堕棿鍦5鐐硅嚦9鐐涔嬮棿锛"C"鍒欒〃绀轰笉鍦ㄨ繖涓椂闂存銆傞氳繃杩欎釜鏂规硶锛屼綘鍙互杞绘澗鍦板湪Excel涓疄鐜板褰撳墠鏃堕棿鐐规槸鍚﹀浜庣壒瀹氭椂闂磋寖鍥寸殑鍒ゆ柇銆傝寰楁牴鎹疄闄呴渶瑕佽皟鏁村崟鍏冩牸涓殑鏃堕棿鍊笺
绛旓細鍛靛懙, 涓婁笅鐝椂闂寸畻娉曡繕涓嶄竴鏍峰晩? 杩樺緱鏀逛竴涓--- 鍏紡闀夸簡鐐, D2杈撳叆:=TEXT(FLOOR(C2,"0:30"),"[h]:mm")-TEXT(CEILING(B2,"0:30"),"[h]:mm")-LOOKUP(TEXT(C2,"[h]:mm"),{"12:00","17:30"},{"1:00","1:30"})涓嬫媺 濡傚浘:...