excel公式问题，请求大神指点！！！谢谢 请求高手EXCEL公式，急！！！，具体内容请看问题补充部分，...
\u56f0\u6270\u6211\u591a\u65f6\u7684EXCEL\u516c\u5f0f\uff0c\u8bf7\u6c42\u5927\u795e\u6307\u70b9\u554a\u554a\u554a\u554a\u516c\u5f0f\u4e3a\uff1a
=COUNTIF(Sheet1!A:A,A1)
\u5982\u56fe\u6240\u793a\uff1a
\u7ecf\u8fc7\u6574\u7406\uff0c\u4f60\u8981\u8868\u8fbe\u7684\u903b\u8f91\u5173\u7cfb\uff0c\u5e94\u8be5\u662f\u4e0a\u8868(\u7b2c5\u30016\u7ea7\u53ef\u4ee5\u5408\u5e76\u4e00\u8d77)\u3002\u6839\u636e\u8fd9\u4e2a\u8868\uff0c\u4f7f\u7528if\u51fd\u6570\u5c31\u53ef\u4ee5\u5f88\u5bb9\u6613\u5b9e\u73b0\u3002
\u5047\u5982A\u5217\u662f\u5b9e\u9645\u5229\u7387\uff0cB\u5217\u7528\u6765\u7b97\u85aa\u8d44\u3002
B2=IF(A2<25%,0\uff0cIF(A2<80\uff0c1400\uff0cIF(A2<90%,2000\uff0cIF(A2<100%,4000\uff0c2000+5000+6000*INT(A2-100%)/20%))))
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)
下拉公式
J3=SUMPRODUCT(($A$2:$A$500=H3)*($C$2:$C$500=1))
下拉公式
K3=SUMPRODUCT(($A$2:$A$500=$H3)*($D$2:$D$500=4))&"/"&SUMPRODUCT(($A$2:$A$500=$H3)*($D$2:$D$500=5))
下拉公式
绛旓細鍏紡濡傚浘锛孉H2=SUM(IF(FREQUENCY(IF($A2:$AE2="A",COLUMN($A:$AE)),IF($A2:$AE2<>"A",COLUMN($A:$AE)))=AH1,1,0))
绛旓細sumifs鏈夋ā绯婂尮閰嶇殑鍔熻兘 *鍙锋槸閫氶厤绗︼紝鍙互浠ｈ〃0涓垨澶氫釜瀛楃锛122*88鍙互鍖归厤鍒122*188 鎵浠ュ缓璁潯浠朵腑鐨*濡傛灉鏄彧浣滀负鏄熷彿浣跨敤鐨勮瘽瑕佸仛涓涓嬪鐞 =SUMIFS(C:C,A:A,SUBSTITUTE(A3,"*","~*"),B:B,"鏄")鍗冲湪*鍙峰墠闈㈠姞涓婅浆涔夊瓧绗 灏辫兘鎶婃槦鍙峰綋鎴愭槦鍙锋湰韬簡锛屼笉浼氬彉鎴愰氶厤绗 ...
绛旓細鍋囪1鍦ˋ鍒楋紝闇瑕佹眰鍜岀殑鍖哄煙鏄疊鍒 =SUMIF(A:A,1,B:B)鐢ㄨ繖涓鍏紡灏卞彲浠ユ眰鍑篈鍒楅噷鏄1鐨勮瀵瑰簲鐨凚鍒楀崟鍏冩牸鍐呭姹傚拰銆俿umif鍑芥暟涓撻棬灏辨槸鍋氳繖涓殑銆
绛旓細浣犱篃鏄細鐢鍏紡鐨勶紝灏变笉澶氳В閲婁簡锛欵2鍏紡锛=(SUMIF(A:A,">="&LEFT(D2,LEN(D2)-2)&VLOOKUP(RIGHT(D2,2),{"涓婃棳","1鏃";"涓棳","11鏃";"涓嬫棳","21鏃"},2,),B:B)-SUMIF(A:A,">="&IF(RIGHT(D2,2)="涓嬫棳",DATE(YEAR(LEFT(D2,LEN(D2)-2)),MONTH(LEFT(D2,LEN(D...
绛旓細sumifs鏈夋ā绯婂尮閰嶇殑鍔熻兘 鍙锋槸閫氶厤绗︼紝鍙互浠ｈ〃0涓垨澶氫釜瀛楃锛122*88鍙互鍖归厤鍒122*188 鎵浠ュ缓璁潯浠朵腑鐨*濡傛灉鏄彧浣滀负鏄熷彿浣跨敤鐨勮瘽瑕佸仛涓涓嬪鐞 =SUMIFS(C:C,A:A,SUBSTITUTE(A3,"*","~*"),B:B,"鏄")鍗冲湪*鍙峰墠闈㈠姞涓婅浆涔夊瓧绗灏辫兘鎶婃槦鍙峰綋鎴愭槦鍙锋湰韬簡锛屼笉浼氬彉鎴愰氶厤绗 ...
绛旓細鍏紡涓猴細=F4*20+G4*25 濡傚浘鎵绀猴細鍏紡涓猴細=OFFSET(Sheet1!$F$4,,COLUMN(A1)*2-2)*20+OFFSET(Sheet1!$F$4,,COLUMN(A1)*2-1)*25 濡傚浘鎵绀猴細
绛旓細鍏紡涓猴細=IFERROR(LEFT(A2,FIND("/",A2)-1),"鏃犲彲鐢ㄦ暟鎹!")濡傚浘鎵绀猴細
绛旓細1)):INDIRECT("c"&SMALL(IF($A$2:$A$100000=A2,ROW($A$2:$A$100000),""),COUNTIF($A$2:$A$100000,A2))),"")浣犵殑鍏紡涓 鎷彿鐨勪綅缃嚭鐜颁簡闂鈥︹︹︿綘杩欎釜鍏紡 浼间箮鏄 姹傦細A鍒 绗竴涓瓑浜嶢2鍊肩殑C鍒椾篃灏辨槸C2寮濮嬪埌鏈鍚庝竴涓瓑浜嶢2 鍊 澶氬搴旂殑C鍒楀 杩欎釜鍖洪棿鐨凜鍒楀 涓 瀹冧滑...
绛旓細鍦Excel涓紝浣犲彲浠ヤ娇鐢–OUNTIF鍑芥暟鏉ユ瘡闅6鍒楄繘琛岃鏁般傝鍑芥暟鍙互缁熻鎸囧畾鍖哄煙鍐呮弧瓒崇壒瀹氭潯浠剁殑鍗曞厓鏍肩殑鏁伴噺銆傚亣璁句綘瑕佽绠楃殑鍖哄煙鏄粠A1鍒癦1鐨勮寖鍥达紝浣犳兂姣忛殧6鍒楄繘琛岃鏁般備綘鍙互浣跨敤浠ヤ笅鍏紡锛氬鍒朵唬鐮 =COUNTIF($A1:$Z1, "*")杩欏皢缁熻璇ヨ寖鍥村唴闈炵┖鍗曞厓鏍肩殑鏁伴噺锛屽苟涓旀瘡闅6鍒楄繘琛岃鏁颁竴娆°傝В閲婁竴...
绛旓細棰橈細鐢excel鍏紡锛灏咥鍒椾腑鐨勫崟鍏冩牸A1,A3,A5鎻愬彇涓鍒癇鍒楃殑B1,B2,B3,浣欑被鎺ㄣ傚嵆灏咥鍒楅殧琛屾彁鍙栧埌B鍒椼侫鍒 B鍒 123 123 124 125 125 236 235 345 236 237 345 绛旓細鍦˙1涓緭鍏ュ叕寮忥細=INDIRECT("A" & (ROW()*2-1))鐒跺悗閫変腑B1鍜屼笅闈㈢殑鍒楋紝鎸塩trl-D鍚戜笅澶嶅埗鍏紡銆傝繕鍙互鐢...
