excel大神麻烦来看看这个问题怎么解决？ 麻烦大神解决excel问题
\u8dea\u6c42excel\u8868\u5927\u795e\uff0c\u8fd9\u4e2a\u95ee\u9898\u600e\u4e48\u89e3\u51b3\uff1fCTRL+G\u6216F5\uff0c\u5b9a\u4f4d\u6761\u4ef6\uff0c\u5bf9\u8c61\uff0c\u786e\u5b9a\u3002\u6309\u952e\u76d8\u4e0a\u7684Delete\u952e\u3002
1.\u5229\u7528\u4e24\u6b21\u5206\u5217\u529f\u80fd2.\u9009\u4e2d\u5c0f\u6570\u90a3\u4e00\u5217\uff0c\u7136\u540e\u70b9\u51fb\u6570\u636e\u83dc\u5355\uff5e\uff5e\u5206\u5217\u51fa\u73b0\u5f39\u7a97\u5982\u56fe\uff0c\u9009\u62e9\u5206\u9694\u7b26\u53f7\uff0c\u7136\u540e\u70b9\u51fb\u4e0b\u4e00\u6b65
\u9009\u62e9\u5176\u4ed6\uff0c\u7136\u540e\u8f93\u5165\u4e00\u4e2a\u5c0f\u6570\u70b9\uff0c\u5982\u4e0b\u9762\u5f39\u7a972
\u7136\u540e\u70b9\u51fb\u5b8c\u6210\uff01\u5c31\u4f1a\u5c06\u5c0f\u6570\u70b9\u4e4b\u524d\u7684\u6570\u548c\u5c0f\u6570\u70b9\u4e4b\u540e\u7684\u6570\u5206\u5f00\uff0c\u8fd9\u662f\u7b2c\u4e00\u6b21\u5206\u5217\uff01
3.\u7136\u540e\u9009\u4e2d\u5c0f\u6570\u70b9\u4e4b\u540e\u90a3\u4e00\u5217\uff0c\u518d\u70b9\u51fb\u6570\u636e\u83dc\u5355\uff5e\uff5e\u5206\u5217\uff0c\u8fd9\u6b21\u5f39\u7a97\u9009\u62e9\u56fa\u5b9a\u5bbd\u5ea6\uff01
\u7136\u540e\u5355\u51fb\u4e0b\u4e00\u6b65\uff0c\u8c03\u6574\u5bbd\u5ea6\uff0c\u5728\u90a3\u4e2a\u5c3a\u5b50\u4e0a\u5de6\u952e\u70b9\u51fb\u4f1a\u51fa\u73b0\u5982\u56fe\u7684\u7ad6\u7ebf\uff0c\u600e\u4e48\u6dfb\u52a0\u7ad6\u7ebf\uff0c\u4e0a\u9762\u6709\u63d0\u793a\uff0c\u7136\u540e\u627e\u5230\u6700\u957f\u7684\u90a3\u4e2a\u6570\u636e\uff0c\u628a\u6bcf\u4e00\u4e2a\u6570\u540e\u9762\u90fd\u52a0\u4e00\u6761\u7ad6\u7ebf\uff0c\u6ce8\u610f\u52a0\u7ad6\u7ebf\u8981\u5728\u5c3a\u5b50\u4e0a\u70b9\u51fb\uff0c\u5982\u56fe\uff0c\u7136\u540e\u70b9\u51fb\u5b8c\u6210\uff01
4.\u627e\u5230\u6570\u5b57\u6700\u957f\u7684\u90a3\u4e00\u884c\uff0c\u5728\u4e00\u4e2a\u7a7a\u503c\u90a3\u4e00\u5217\u5f00\u5934\u8f93\u5165\u51fd\u6570sum\uff0c\u4ee5\u6211\u7684\u4e3a\u4f8b\uff0c\u6211\u7684\u51fd\u6570\u5982\u56fe\uff0c\u6ce8\u610f\u9009\u62e9\u8303\u56f4\u65f6\u4e0d\u8981\u9009\u7b2c\u4e00\u5217\uff08\u5c0f\u6570\u70b9\u4e4b\u524d\u7684\u6570\uff09\uff0c\u800c\u4e14\u8981\u9009\u62e9\u8303\u56f4\u8981\u548c\u6700\u957f\u7684\u4e00\u884c\u5bf9\u9f50\u7a7a\u5355\u5143\u683c\u4e5f\u8981\u9009\u4e0a\uff0c\uff0c\u5982\u56fe\uff0c\u7136\u540e\u56de\u8f66\uff0c\u5411\u4e0b\u586b\u5145\u5373\u53ef\uff01
=X4+AC4
下拉填充
复制A:B两列，粘贴到AG1。选择AG:AH，删除重复值
在AI4中输入或复制粘贴下列公式
=INDEX(L:L,MATCH(MAXIFS(AE:AE,A:A,AG4,B:B,AH4),AE:AE,))
下拉填充
如果A列只有当月数据，可用下面公式
=INDIRECT("L"&SUMPRODUCT((DAY(A4:A50)=4)*(B4:B50="00093932")*((X4:X50+AC4:AC50)=MAX((DAY(A4:A50)=4)*(B4:B50="00093932")*(X4:X50+AC4:AC50)))*ROW(4:50)))
有文件才方便做，请提供下文件
绛旓細鍦ˋE4涓緭鍏ユ垨澶嶅埗绮樿创涓嬪垪鍏紡 =X4+AC4 涓嬫媺濉厖 澶嶅埗A:B涓ゅ垪锛岀矘璐村埌AG1銆傞夋嫨AG:AH锛屽垹闄ら噸澶嶅 鍦ˋI4涓緭鍏ユ垨澶嶅埗绮樿创涓嬪垪鍏紡 =INDEX(L:L,MATCH(MAXIFS(AE:AE,A:A,AG4,B:B,AH4),AE:AE,))涓嬫媺濉厖
绛旓細鍋囪鏁版嵁鍦ˋ1:A5涓紝缁熻鍚1鐨勪釜鏁帮細=SUMPRODUCT(LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,"1",)))鍏跺畠绫绘帹銆傝鏄庯細涓涓崟鍏冩牸涓嫢鏈夐噸澶嶆暟瀛楋紝鍚屾椂璁″叆鎬绘暟銆
绛旓細鏂规硶/姝ラ5/5鍒嗘闃呰杩鏄竴浠芥潅涔辨棤绔犵殑鎶ヨ〃锛岃鎴戜滑鎶婂畠鍙樺緱鏇村姞鏈夊簭鐐瑰惂銆傜敤Excel鎵撳紑杩欎唤鎶ヨ〃銆2/5鏈绠鍗曟柟娉曟槸鍦ㄥ伐鍏锋爮涓婄偣鍑诲揩鎹锋寜閽 鍗囧簭鎴栬呴檷搴忥紝濡傛灉浣犳壘涓嶅埌鐨勮瘽锛屽彲浠ョ偣鍑汇愭暟鎹戔斺斻愭帓搴忋戙傛煡鐪嬪墿浣1寮犲浘3/5 鏁堟灉灏辨槸杩欐牱鐨勶紝寰堢畝鍗曞惂锛屼竴涓嬪瓙瑙夊緱涓栫晫寰堝共鍑 4/5濡傛灉浣犺寰楄繖鏍...
绛旓細鐪嬩綘鐨勫叕寮忥紝鐢ㄤ竴涓姞涓涓紝澶垂浜嬩簡銆傞兘鏄紩鐢≧7锛岀敤 =SUM('1:20'!R7)灏辫鍟︺
绛旓細1銆佸崟鍏冩牸A2鐨勫间负閿 2銆佸崟鍏冩牸A3鏄垽鏂懡浠わ紝濡傛灉涓洪敊灏辨墽琛孯UN鍛戒护(RUN鏄繍琛屽懡浠わ紝杩愯浠涔堜綘鐨勫浘鐗囨病鏄剧ず鍑烘潵)3銆佸崟鍏冩牸A4鏄樉绀哄璇濇鍛戒护锛屽憡璇夌敤鎴锋搷浣滅殑缁撴灉鏄滅鐢ㄥ畯锛屽叧銆傘傘傗4銆佸崟鍏冩牸A5鏄嚜鍔ㄨ繍琛屽叧闂粈涔堬紝杩欓噷澶ф鏄寚鍏抽棴EXCEL鏂囨。鍚с5銆佸崟鍏冩牸A6鏄垽鏂粨鏉熷懡浠 6銆佸崟鍏冩牸A7鏄寚...
绛旓細1)澶嶅埗鍗曞厓鏍煎唴瀹硅嚦13琛岀殑鍗曞厓鍗曞厓鏍煎唴锛岋紙浠涔堬紵浣犺瑕佹崲琛岋紵涓嶄細锛燂級璁板緱鐢╝lt+鍥炶溅閿繘琛屽崟鍏冩牸鍐呮崲琛屾搷浣溿2)澶嶅埗鍐呭锛屽悎骞13銆14琛屽啀杩涜杈撳叆銆3)灏嗚竟琛屽簳绾硅缃垚鐧借壊锛堜粈涔堬紵浣犺鏈潵涓嶅氨鏄櫧鑹茬殑鍢涳級锛岃寰椾笉瑕侀夋嫨榛樿鐧借壊锛岃閫夋嫨棰滆壊涓殑鐧借壊銆4)鍙栨秷缃戞牸绾跨嚎鏄剧ず锛岃繖鏍锋墍鏈夌殑缃戞牸绾块兘...
绛旓細INDIRECT("e"&ROW())<>"",INDIRECT("e"&ROW()+1)=""),SUM(E:E),"")浣犲彲浠ユ妸E鍒楃殑鍏紡鎷夊埌寰堝琛岋紝姣斿璇翠綘鐪嬩互寰鏁版嵁锛屼竴鑸竴澶╀細鏈50琛岋紝閭ｄ綘灏辨妸I鍒楃殑鍏紡鎷夊埌70琛屽乏鍙崇殑浣嶇疆锛屽彧瑕丒鍒楃殑瀵瑰簲琛屾湁杈撳叆锛屽氨浼氬緱鍑虹粨鏋溿傚綋鐒讹紝杩欏叕寮忚姹傛槸E鍒楁暟鎹槸杩炵画鐨勶紝涓嶇劧浣嶇疆灏变細鍑洪敊浜嗐
绛旓細鍏堟妸yes,no鏀惧湪涓涓笉甯哥敤鐨勫垪閲岋紝濡傛斁鍦≧鍒楋紝R1閲屽～yes,r2濉玭o 鐜板湪鎶婁綘瑕佺殑鏁堟灉鏀惧湪A1,鎿嶄綔濡備笅 閫変腑A1--宸ュ叿鏍忎腑鐨"鏁版嵁鏈夋晥鎬"---鏁版嵁鏈夋晥鎬у璇濇涓厑璁搁偅涓湴鏂归夋嫨"搴忓垪鈥濓紝鍦ㄨ緭鍏ヤ俊鎭腑杈撳叆鈥滃鏋滄槸閫墆es,涓嶆槸閫塏O"鐒跺悗纭畾 鐒跺悗鎶夾1澶嶅埗涓嬫潵灏辫浜 ...
绛旓細鍚戜綘瀛︿範锛佹垜鏌ヤ簡涓涓婦COUNTA鐨勭敤娉曪紝鍏朵腑鐨勪緥瀛愶紝浣犻偅涓鍝佺被鍒笅闈㈢殑绯栨灉绫伙紝鏄笉鏄簲璇ユ敼涓猴細="=绯栨灉绫烩
绛旓細D1=IF(COUNTIF($A$1:$A$12,B1)>=1,"B鍒楀唴瀹笰鍒椾腑鏈","")琛ュ厖鍥炵瓟锛氭牴鎹綘琛ュ厖鐨勫浘鐗囷紝鎶婂叕寮忎慨鏀规垚锛氳〃涓B2=IF(COUNTIF(SHEET2!$A$2:$A$10000,A1)<1,"绁ㄩ潰涓病鏈","")琛ㄤ簩B2=IF(COUNTIF(SHEET1!$A$2:$A$10000,A1)<1,"11鏈堣璇佷腑娌℃湁","")濡傛灉涓よ〃鍚嶇О涓嶆槸SHEET2鍜...
