求各位excel公式大神帮助！
excel \u516c\u5f0f\uff0c\u6c42\u5927\u795e\uff01\uff01\uff01\u5355\u4ef7\u5728A\u5217\uff0c\u9012\u5728B\u5217\uff0c\u6570\u91cf\u5728C\u5217\uff0cD\u5217\u4e3a\u8ba1\u7b97\u7ed3\u679c\uff0c\u6570\u636e\u4ece\u7b2c\u4e8c\u884c\u5f00\u59cb\uff01\u5728D2\u5355\u5143\u683c\u4e2d\u5199\u516c\u5f0f\u201c=(A2+B2)*C2\u201d\uff08\u4e0d\u8981\u52a0\u53cc\u5f15\u53f7\uff09\uff0c\u7136\u540e\u56de\u8f66\u786e\u8ba4\u3002D\u5217\u5176\u4ed6\u5355\u5143\u683c\u590d\u5236\u5373\u53ef\uff01
\u56e0\u4e3a\u4e0d\u786e\u5b9a\u4f60\u5728\u5b57\u6bcd\u524d\u7684\u6570\u5b57\u6709\u591a\u5c11\u4e2a\uff0c\u6240\u4ee5\u516c\u5f0f\u4f1a\u6bd4\u8f83\u590d\u6742\uff1a
\u5047\u5982\u6570\u636e\u5728A6\uff1a
=IF(ISERROR(FIND("A",A6,1)),IF(ISERROR(FIND("K",A6,1)),IF(ISERROR(FIND("S",A6,1)),IF(ISERROR(FIND("F",A6,1)),IF(ISERROR(FIND("Y",A6,1)),IF(ISERROR(FIND("H",A6,1)),"",LEFT(A6,FIND("H",A6,1)-1)*10000+IF(RIGHT(A6,1)="H",0,RIGHT(A6,LEN(A6)-FIND("H",A6,1))/10^(LEN(RIGHT(A6,LEN(A6)-FIND("H",A6,1)))-4))),LEFT(A6,FIND("Y",A6,1)-1)*1000+IF(RIGHT(A6,1)="Y",0,RIGHT(A6,LEN(A6)-FIND("Y",A6,1))/10^(LEN(RIGHT(A6,LEN(A6)-FIND("Y",A6,1)))-3))),LEFT(A6,FIND("F",A6,1)-1)*100+IF(RIGHT(A6,1)="F",0,RIGHT(A6,LEN(A6)-FIND("F",A6,1))/10^(LEN(RIGHT(A6,LEN(A6)-FIND("F",A6,1)))-2))),LEFT(A6,FIND("S",A6,1)-1)*10+IF(RIGHT(A6,1)="S",0,RIGHT(A6,LEN(A6)-FIND("S",A6,1))/10^(LEN(RIGHT(A6,LEN(A6)-FIND("S",A6,1)))-1))),LEFT(A6,FIND("K",A6,1)-1)*1+IF(RIGHT(A6,1)="K",0,RIGHT(A6,LEN(A6)-FIND("K",A6,1))/10^LEN(RIGHT(A6,LEN(A6)-FIND("K",A6,1))))),LEFT(A6,FIND("A",A6,1)-1)*0.1+IF(RIGHT(A6,1)="A",0,RIGHT(A6,LEN(A6)-FIND("A",A6,1))/10^(LEN(RIGHT(A6,LEN(A6)-FIND("A",A6,1)))+1)))
如上图所示：
8元金额汇总公式：=SUM((A:A=D2)*(B:B=-8)*(-8))
4元金额汇总公式：=SUM((A:A=D2)*(B:B=-4)*(-4))
公式输入完成后同时按Ctrl+Shift+Enter
=SUMIF(G:G,"-8",G:G)
看图!
绛旓細=COUNTBLANK(OFFSET($C4,0,MAX(1,$AH$3-6),1,6))澶嶅埗骞朵笅鎷夛紝寰楀埌鏈鍚6澶╃浉搴旂殑绌虹櫧澶╂暟鍊硷紝鏈鍚庯紝鍦 AJ3 鍗曞厓鏍艰緭鍏鍏紡锛=IF(AI4="","",TEXT(COUNTIFS(B:B,AI4,AH:AH,6),"鍙互;;涓嶈兘"))澶嶅埗骞朵笅鎷夛紝鍗冲彲銆傚悗缁彧瑕佸湪鏁板 10 澶勮皟鏁翠慨鏀癸紝鍗冲彲寰楀埌瀵瑰簲鏃ユ湡鐨勬晥鏋滀簡銆
绛旓細1銆侀鍏堥変腑Excel琛ㄦ牸锛屽弻鍑绘墦寮銆2銆佺劧鍚庡湪璇ョ晫闈腑锛岄変腑瑕佹樉绀篤LOOKUP鍏紡缁撴灉鐨勫崟鍏冩牸銆3銆佸啀鑰呭湪璇ョ晫闈腑锛屾樉绀哄叕绀烘爮閲岀殑VLOOKUP鍏紡鐨勬煡鎵剧殑瀵硅薄6鏈湪鏌ユ壘鍖哄煙A1鍒癆5涓4銆佸叾娆″湪璇ョ晫闈腑锛屼慨鏀瑰叕绀烘爮閲岀殑VLOOKUP鍏紡鐨勬煡鎵剧殑瀵硅薄6鍦ㄦ煡鎵惧尯鍩烝1鍒癆6涓5銆佹渶鍚庡湪璇ョ晫闈腑锛屽崟鍏冩牸閲屾垚鍔熸樉绀篤LOOK...
绛旓細鍏紡=TEXTJOIN(" ",TRUE,TRIM(TEXT(MID(A2,ROW($1:$100),1),)))锛屾寜涓夐敭纭鏁扮粍鍏紡銆傚叿浣撳涓嬶細
绛旓細鍥句笂G2鍗曞厓鏍鍏紡锛=MINIFS($C$2:$C$17,$A$2:$A$17,F2)H2鍗曞厓鏍煎叕寮忥紙杈撳叆鍚嶤trl+Shift+Enter鎴愪负鏁扮粍褰㈠紡锛岃嚜鍔ㄧ敓鎴愬ぇ鎷彿锛夛細=INDEX(B2:B17,MATCH(F2&G2,A2:A17&C2:C17,0))I2鍗曞厓鏍煎叕寮忥紙杈撳叆鍚嶤trl+Shift+Enter鎴愪负鏁扮粍褰㈠紡锛岃嚜鍔ㄧ敓鎴愬ぇ鎷彿锛夛細=INDEX(D2:D17,MATCH(F2&G2,A2:...
绛旓細涓銆Excel鍑芥暟鍏紡锛氳嚜鍔ㄨ绠楀勾榫勩傜洰鐨勶細鏍规嵁鍛樺伐鐨勩愬嚭鐢熸棩鏈熴戣嚜鍔ㄨ绠楀勾榫勶紝骞朵繚鎸佹洿鏂般傛柟娉曪細鍦ㄧ洰鏍囧崟鍏冩牸涓緭鍏ュ叕寮忥細=DATEDIF(C3,TODAY(),"y")銆傝В璇伙細1銆丏atedif鍑芥暟鏄竴涓郴缁熼殣钘忓嚱鏁帮紝鍏朵綔鐢ㄤ负锛氫互鎸囧畾鐨勬柟寮忚绠椾袱涓棩鏈熶箣闂寸殑宸硷紝璇硶缁撴瀯涓猴細=Datedif(寮濮嬫棩鏈,缁撴潫鏃ユ湡,缁熻鏂瑰紡)銆傚父鐢...
绛旓細鍏蜂綋鎿嶄綔姝ラ濡備笅锛 1. 鍦ㄦ昏〃鏍间腑锛屼负姣忎釜灏忓尯鍒涘缓涓涓柊鐨勫伐浣滆〃銆 2. 鍦ㄦ瘡涓伐浣滆〃涓紝浣跨敤`=INDEX(灏栧嘲骞宠胺鍜屾湇鍔¤垂琛!A:A,MATCH(A2,灏栧嘲骞宠胺鍜屾湇鍔¤垂琛!A:A,0))`鍏紡锛屽皢灏忓尯鍚嶇О鍖归厤鍒板搴旂殑鏁版嵁琛屻 3. 浣跨敤`=INDEX(灏栧嘲骞宠胺鍜屾湇鍔¤垂琛!B:B,MATCH(A2,灏栧嘲骞宠胺鍜屾湇鍔¤垂琛!A:A,...
绛旓細))鐒跺悗澶嶅埗鎴栬呭～鍏呭埌I9:K23鍖哄煙 濡傛灉浣跨敤sumifs鍑芥暟锛岄偅涔圚3=SUMIFS(OFFSET($A:$A,,MATCH(LOOKUP("搴",$H$1:H$1),$A$1:$E$1,)-1),$A:$A,$G3,$B:$B,H$2)I9=SUMIFS(OFFSET($A:$A,,MATCH(LOOKUP("搴",$G$9:$G9),$A$1:$E$1,)-1),$A:$A,I$8,$B:$B,$H9)
绛旓細涓嶉噸澶嶅崟鍙蜂釜鏁帮紝浣跨敤涓嬮潰鍏紡锛=SUMPRODUCT(IFERROR((B2:B12=TRUE)*(C2:C12=FALSE)/COUNTIFS(A2:A12,A2:A12,B2:B12,TRUE,C2:C12,FALSE),0))鏁扮粍鍏紡锛屽悓鏃舵寜涓婥trl+Shift+Enter缁撴潫杈撳叆銆
绛旓細I4鎬讳簨浠舵暟閲忚緭鍏 =COUNTIF(A:A,H4)J4鏈夋晥浜嬩欢鏁伴噺杈撳叆 =SUMIF(A:A,H4,C:C)K4 4鍒嗗強浠ヤ笂璇勫垎鏁伴噺杈撳叆 =COUNTIFS(A:A,H4,D:D,">3")L4 鏈夋晥鐨4鍒嗗強浠ヤ笂璇勫垎鏁伴噺杈撳叆 =COUNTIFS(A:A,H4,D:D,">3",C:C,1)涓嬫媺鍏紡
绛旓細鍥剧ず锛屽彲浠ュ湪C2鍗曞厓鏍煎畾涔夊嚱鏁鍏紡=IF(A2=1,B2*0.8,IF(A2>1,B2*(0.8-(A2-1)*0.1),""))璇蜂豢鐓ф垜鍙戠殑鎴浘淇敼鍏紡锛屽彉鎴愰傚悎浣犻渶瑕佺殑銆
