Excel怎么按照时间段0:00-1:00,1:00-2:00,2:00-3:00排序？ excel中怎么自动排序时间段？例如 8:00-9:00 9...
\u5982\u4f55\u5728EXCEL\u8868\u91cc\u628a\u65f6\u95f4\u8fd9\u4e00\u5217\u6bd4\u5982\u8bf41:00-2:00,2:00-3:00,3:00-4:00\u8fd9\u6837\u4f9d\u6b21\u586b\u5145\uff0c\u8c22\u8c22\u8f93\u5165
=MOD(ROW(A1),12)&":00-"&MOD(ROW(A2),12)&":00"
\u516c\u5f0f\u4e0b\u62c9
\u5728\u5355\u5143\u683c\u4e2d\u8f93\u5165\u4ee5\u4e0b\u516c\u5f0f\uff0c\u7136\u540e\u5411\u4e0b\u586b\u5145
=ROW(A8)&":00-"&ROW(A9)&":00"
\u8be6\u89c1\u9644\u56fe
你可以加一个辅助列，完事后，把辅助列删除或隐藏。B列加公式提取时间，对这一列排序。
B1公式：=MID(A1,1,FIND(":",A1)-1)*1
b1=--left(a1,find(":",a1)-1)下拉，选中ab2列数据，以b列为关键字升序排序即可
绛旓細鈶犻鍏堬紝灏嗘椂闂存鎸夌収缁熶竴鐨勬牸寮忔暣鐞嗗ソ锛屼緥濡傗滃皬鏃:鍒嗛挓鈥濄傗憽鐒跺悗锛鍦‥xcel涓娇鐢 COUNTIF 鍑芥暟鏉ュ婊¤冻鏉′欢鐨勬椂闂存杩涜璁℃暟銆傚叿浣撴搷浣滃涓嬶細鍦‥xcel琛ㄦ牸涓変腑闇瑕佽鏁扮殑鍗曞厓鏍;鍦ㄧ紪杈戞爮涓緭鍏 COUNTIF 鍑芥暟锛屽苟璁剧疆濂芥椂闂存鎵鍦ㄧ殑鍖哄煙鍜屾潯浠;鎸変笅鍥炶溅閿嵆鍙緱鍒拌鏁扮粨鏋溿備緥濡傦紝鍋囪鏃堕棿娈靛湪A1鑷矨10...
绛旓細浣犲彲浠ュ姞涓涓緟鍔╁垪锛屽畬浜嬪悗锛屾妸杈呭姪鍒楀垹闄ゆ垨闅愯棌銆侭鍒楀姞鍏紡鎻愬彇鏃堕棿锛屽杩欎竴鍒楁帓搴忋侭1鍏紡锛=MID(A1,1,FIND(":",A1)-1)*1
绛旓細鍦–1鍗曞厓鏍间腑杈撳叆浠ヤ笅鍏紡璁＄畻鏃堕棿娈鐨勫紑濮嬫椂闂达細=IF(A1>B1,"",A1)鍦―1鍗曞厓鏍间腑杈撳叆浠ヤ笅鍏紡璁＄畻鏃堕棿娈电殑缁撴潫鏃堕棿锛=IF(B1>A1,"",B1)濡傛灉鏃堕棿娈垫湁閲嶅彔锛屽彲浠ュ湪E1鍗曞厓鏍间腑杈撳叆浠ヤ笅鍏紡璁＄畻閲嶅彔鐨勫皬鏃舵暟锛=IF(AND(A1>B1,B1-A1=1),0,B1-A1)濡傛灉涓嶉渶瑕佽绠楅噸鍙犳椂闂达紝鍙互璺宠繃E1鍗曞厓鏍笺傚畬鎴愪互...
绛旓細鍋囧畾C鍒楃殑鏃堕棿閮芥槸鍚屼竴澶╃殑锛屼互鈥0000-0300鈥濅负渚嬭璁″叕寮忥細=COUNTIFS(C:C,">=2018-7-18 0:0:0",C:C,"<=2018-7-18 3:0:0")濡傛灉璁＄畻鈥滃洓宸濈渷鈥濈殑锛屽叕寮忥細=COUNTIFS(B:B,"鍥涘窛鐪",C:C,">=2018-7-18 0:0:0",C:C,"<=2018-7-18 3:0:0")
绛旓細棣栧厛锛屼綘闇瑕佸湪Excel涓垱寤轰袱涓緟鍔╁崟鍏冩牸锛屼緥濡侭2鍜孊3锛屽垎鍒緭鍏鏃堕棿娈鐨勫紑濮嬪拰缁撴潫鏃堕棿锛屽5:00鍜9:00銆傛帴涓嬫潵锛屼綘闇瑕佸湪鐩爣鍗曞厓鏍硷紙濡侭1锛変腑杈撳叆鍒ゆ柇鍏紡銆傚湪杩欎釜渚嬪瓙涓紝鍏紡濡備笅锛=IF(AND(TIME(HOUR(NOW()),MINUTE(NOW()),0)>B2,TIME(HOUR(NOW()),MINUTE(NOW()),0)<B3),"A","C...
绛旓細涓銆佸涓嬪浘锛岃鍦˙1鍗曞厓鏍间腑鍒ゆ柇褰撳墠鏃堕棿锛堝綋鍓嶆椂闂存樉绀哄湪浜咥3鍗曞厓鏍硷級鏄惁鍦ㄤ笂鍗5鐐瑰埌9鐐逛箣闂达紝閭ｄ箞棣栧厛闇瑕佽緭鍏ヤ袱涓緟鍔╁崟鍏冩牸锛屽湪杩欎袱涓崟鍏冩牸鍐呭垎鍒緭鍏ヨ繖涓鏃堕棿娈鐨勪袱涓椂闂达紙杩欎釜鏃堕棿娈电殑涓や釜鏃堕棿鐐瑰彲浠ヨ緭鍏ュ湪浠绘剰涓や釜绌虹櫧鍗曞厓鏍煎唴锛屽彧闇鍚庨潰鏇存敼鍏紡涓殑鍗曞厓鏍煎弬鏁板嵆鍙級銆備簩銆佽繖鏃跺湪B1鍗曞厓鏍间腑...
绛旓細鐜板湪锛屽綋鎮ㄥ湪鎸囧畾鐨鏃堕棿娈鍐呮墦寮Excel鏂囦欢鏃讹紝绗﹀悎鏉′欢鐨勫崟鍏冩牸灏嗚嚜鍔ㄦ樉绀烘偍璁剧疆鐨勭壒瀹氬唴瀹广傚湪Excel涓彲浠ヤ娇鐢↖F銆丯OW鍜孴IME鍑芥暟缁撳悎鏉′欢寮忔潵瀹炵幇鍦ㄧ壒瀹氱殑鏃堕棿鑷姩鏄剧ず鐗瑰畾鐨勫唴瀹广傚叿浣撴楠ゅ涓嬶細1.鍦ㄩ渶瑕佽缃壒瀹氭椂闂磋嚜鍔ㄦ樉绀虹壒瀹氬唴瀹圭殑鍗曞厓鏍间腑杈撳叆浠ヤ笅鍏紡锛=IF(NOW()>TIME(9,0,0),"鏃╀笂濂","鏅氫笂...
绛旓細鐩存帴鐢ㄨ嚜瀹氫箟绛涢夊氨鍙互浜嗭紝浠嬩簬0:00鍜6:00鐨勫氨鍙互浜嗐
绛旓細1銆佸弻鍑诲浘鏍囨墦寮Excel銆2銆侀夋嫨闇瑕佸揩閫熼夋嫨鐨勫尯鍩燂紝鍙抽敭鐐瑰嚮閫夋嫨鈥滃鍒垛濄3銆佺劧鍚庢墦寮Word銆4銆佹寜鈥渃trl+v鈥濆揩閫熺矘璐淬5銆佸湪鍙充笂瑙掓壘鍒板苟鐐瑰嚮鈥滄浛鎹⑩濄6銆佸湪鏌ユ壘鍐呭閲岃緭鍏モ淸0-9]鈥濓紝鐒跺悗鐐瑰嚮宸︿笅瑙掔殑鈥滄洿澶氣濄7銆佸嬀閫夆滀娇鐢ㄩ氶厤绗︹濓紝鐒跺悗鐐瑰嚮鈥滃叏閮ㄦ浛鎹⑩濄8銆佸叏閫夋浛鎹㈠ソ鐨勫唴瀹癸紝鎸夆渃trl+...
绛旓細鍙互鐢ㄥ叕寮忥細=MROUND(A1,1/24)浣嗚繖鏄洓鑸嶄簲鍏ワ紝瓒呰繃30鍒嗛挓鐨勪細鍒颁笅涓涓皬鏃讹紝濡傛灉闇瑕佷笅鍙栨暣浣跨敤INT鍏紡锛=INT(A1*24)/24
