excel表格日期年日不变改变月 excel表格公式日期下拉只改变月份和年日期不变
excel\u4e2d\u5982\u4f55\u6279\u91cf\u4fee\u6539\u65e5\u671f\u7684\u5e74\u4efd\uff0c\u6708\u65e5\u4fdd\u6301\u4e0d\u53d8excel\u4e2d\u6279\u91cf\u4fee\u6539\u65e5\u671f\u7684\u5e74\u4efd\uff0c\u6708\u65e5\u4fdd\u6301\u4e0d\u53d8\u7684\u5177\u4f53\u6b65\u9aa4\u5982\u4e0b\uff1a
1\u3001\u9996\u5148\u6253\u5f00\u9700\u8981\u7f16\u8f91\u7684Excel\u8868\u683c\uff0c\u8fdb\u5165\u5230\u7f16\u8f91\u9875\u9762\u4e2d\u3002
2\u3001\u7136\u540e\u5728\u65e5\u671f\u540e\u9762\u7684\u5355\u5143\u683c\u4e2d\u8f93\u5165\u516c\u5f0f\u201c=REPLACE(REPLACE(F2,1,2,"13"),10,2,"14")\u201d\uff0c\u56de\u8f66\u786e\u5b9a\u3002
3\u3001\u7136\u540e\u5c31\u53ef\u4ee5\u5c0612\u5e74\u6539\u4e3a13\u5e74\uff0c13\u5e74\u6539\u4e3a14\u5e74\uff0c\u6708\u4efd\u548c\u65e5\u671f\u4fdd\u6301\u4e0d\u53d8\u3002
4\u3001\u7136\u540e\u9f20\u6807\u5de6\u952e\u70b9\u51fb\u4e0b\u62c9\u586b\u5145\u4e0b\u9762\u7684\u5355\u5143\u683c\u5373\u53ef\u3002
\u7528\u51fd\u6570\u8bbe\u7f6e\uff0c\u627e\u5230TODAY\u8fd9\u4e2a\u51fd\u6570\u2192\u56de\u8f66\u786e\u5b9a\u2192\u5c31\u51fa\u73b0\u5f53\u5929\u7684\u65e5\u671f\uff0c\u5982\u679c\u660e\u5929\u6253\u5f00\u5c31\u662f\u660e\u5929\u7684\u65e5\u671f\uff0c\u5982\u679c\u4f60\u8fd8\u8981\u540e\u9762\u5e26\u6709\u5e74\u3001\u6708\u3001\u65e5\uff0c\u90a3\u4f60\u53ef\u4ee5\u70b9\u51fb\u8be5\u5355\u5143\u683c\uff0c\u683c\u5f0f\u2192\u5355\u5143\u683c\u683c\u5f0f\u2192\u65e5\u671f\u2192\u8fdb\u884c\u8bbe\u7f6e\u3002\u5c31\u80fd\u8fbe\u5230\u4f60\u60f3\u8981\u7684\u6548\u679c\u3002
ctrl+h查找框输入 年9月
替换框输入 年10月
点击 全部替换
绛旓細鍙﹀锛屽鏋滄偍鍙戠幇鏌愪簺鏃ユ湡鏁版嵁鏃犳硶姝ｇ‘鏄剧ず锛屽嵆浣挎偍宸茬粡鏇存敼浜鍗曞厓鏍鐨勬棩鏈熸牸寮忥紝鍙兘鏄洜涓鸿繖浜涙棩鏈熸暟鎹湰韬瓨鍦ㄩ棶棰樸備緥濡傦紝濡傛灉鏃ユ湡鏁版嵁浠ヤ笉姝ｇ‘鐨勬牸寮忚緭鍏ワ紙渚嬪鈥滄湀/鏃/骞粹濊屼笉鏄滃勾/鏈/鏃モ濓級锛屽垯Excel鍙兘鏃犳硶姝ｇ‘璇嗗埆杩欎簺鏃ユ湡銆傚湪杩欑鎯呭喌涓嬶紝鎮ㄩ渶瑕佹墜鍔ㄦ洿姝ｈ繖浜涙棩鏈熸暟鎹紝鎴栬呬娇鐢ㄦ潯浠舵牸寮忓寲鏉ユ爣璇...
绛旓細锛堟湁鐨勭増鏈繖閲屾槸銆愯凯浠ｈ繍绠椼戯級锛岀劧鍚庣偣鍑诲彸涓嬭鐨勭‘瀹氬叧闂獥鍙ｃ4.鍥炲埌excel琛ㄦ牸锛岃瘯璇曞湪B鍒楀湪涓嶅悓鐨勬椂闂磋緭鍏ラ攢閲忔暟鎹紝鍦ˋ鍒楄嚜鍔ㄧ敓鎴愪簡杈撳叆鏁版嵁鐨勬椂闂达紝褰撲綘杩囧嚑鍒嗛挓鍥炲幓淇敼鍒氳緭鍏ョ殑鏁版嵁鐨勬椂鍊欙紝鐢熸垚鐨勬椂闂翠笉浼氬洜涓轰慨鏀规暟鎹鍙樺寲銆傝繖鏍峰氨瑙ｅ喅浜唀xcel 鑷姩璁惧畾鏃ユ湡锛屼笖涔嬪悗鏃ユ湡涓嶅彉鐨勯棶棰樹簡銆
绛旓細6. 闄ゆ涔嬪锛岃嫢姝ラ暱鍊兼槸鈥2鈥濅究浼氬嚭鐜板鍥炬墍绀虹殑椤甸潰 excel涓壒閲忎慨鏀鏃ユ湡鐨勫勾浠斤紝鏈堟棩淇濇寔涓嶅彉鐨勫叿浣撴楠ゅ涓嬶細1銆侀鍏堟墦寮闇瑕佺紪杈戠殑Excel琛ㄦ牸锛岃繘鍏ュ埌缂栬緫椤甸潰涓2銆佺劧鍚庡湪鏃ユ湡鍚庨潰鐨勫崟鍏冩牸涓緭鍏ュ叕寮忊=REPLACE(REPLACE(F2,1,2,"13"),10,2,"14")鈥濓紝鍥炶溅纭畾銆3銆佺劧鍚庡氨鍙互灏12骞存敼涓...
绛旓細excel鍔熻兘鍖洪夋嫨鈥滃叕寮忊濋」锛屽崟鍑烩滆绠椻濅笅鎷夐夋嫨鈥滄墜鍔ㄨ绠椻濓紝淇濆瓨鍚庯紝闇瑕佹寜鈥淔9鈥濇墠浼氶噸鏂拌绠楋紝鍙互缂撹В杩欎釜闂锛屽鍥撅細
绛旓細ctrl+h 鏌ユ壘妗嗚緭鍏 骞9鏈 鏇挎崲妗嗚緭鍏 骞10鏈 鐐瑰嚮 鍏ㄩ儴鏇挎崲
绛旓細濡傛灉浣犵敤浜嗗嚱鏁扮殑璇濓紝鐨勭‘鏄瘡澶╄嚜鍔ㄦ洿鏂般傛暀浣犱竴涓揩鎹烽敭鑾峰緱褰撳墠鏃ユ湡锛歝trl+;
绛旓細鏂规硶鍥涳細浣跨敤鍑芥暟杈撳叆鏃ユ湡 1. 鎵撳紑Excel琛ㄦ牸锛岄夋嫨瑕佽緭鍏ユ棩鏈熺殑鍗曞厓鏍笺2. 鍦ㄥ崟鍏冩牸涓緭鍏モ=TODAY()鈥濇垨鈥=NOW()鈥濆嚱鏁帮紝鎸変笅鍥炶溅閿嵆鍙樉绀哄綋鍓嶆棩鏈熸垨鏃堕棿銆3. 濡傛灉闇瑕佽嚜瀹氫箟鏃ユ湡鏍煎紡锛屽彲浠ュ湪鍑芥暟鍚庨潰鍔犱笂鈥&鈥濈鍙峰拰鏍煎紡浠ｇ爜锛屼緥濡傗=TODAY()&"骞"&B1&"鏈"&C1&"鏃"鈥濄備互涓婃槸鍑犵甯歌鐨...
绛旓細鍙互浣跨敤寰幆杩唬鍔熻兘瀹炵幇锛岄鍏堟墦寮寰幆杩唬锛氱劧鍚庝娇鐢ㄤ笅闈㈠叕寮忥紙浠4杈撳叆鏁版嵁锛孒4杈撳嚭鏃ユ湡涓轰緥锛夛細=IF(D4="","",IF(H4="",TODAY(),H4))鏁堟灉濡傚浘鎵绀猴紙鏃ユ湡涓鏃﹀嚭鐜帮紝浠ュ悗涓嶄細鍐嶅彉锛岄櫎闈炰綘娓呯┖D4閲嶆柊杈撳叆锛夛細
绛旓細銆3銆佸湪鍙宠竟鐨勭獥鍙ｄ腑杈撳叆涓嬭竟浠ｇ爜锛歅rivate Sub Worksheet_Change(ByVal Target As Range)If Target.Column = 2 And Target.Value <> "" Then Target.Offset(0, -1) = Format(Date, "dd鏃")End If End Sub 4銆佽繖鏍蜂綘鍥炲埌宸ヤ綔琛锛屽湪B鍒楄緭鍏ユ暟鎹侫鍒楀氨浼氬嚭鐜板綋鍓鏃ユ湡銆
绛旓細浣犺繖涓鍒楁槸鏂囨湰鍨嬬殑浜嗭紝姣斿鏄湪A鍒楋紝鍒欏湪鏃佽竟涓鍒楋紝=VALUE(A1)锛屽叾瀹冨彲寰涓嬫嫋鎷夊叕寮忋傚緱鍑虹粨鏋滃悗锛屾妸杩欏垪璁剧疆鎴愪綘闇瑕佺殑鏃ユ湡鍨嬶紝鍐嶆暟鍊肩矘璐翠竴涓嬪幓鎺夊叕寮忋
