求EXCEL大神帮助！ excel求大神帮助
\u6c42excel\u5927\u795e\u76f8\u52a9\uff5e\u53ef\u4ee5\u8bd5\u4e0b\u8f6c\u7f6e\u7c98\u8d34
\u5728\u6392\u5e8f\u7c98\u8d34\u8fdb\u53bb
\u6bd4\u5982\u88681\u540d\u79f0\u53eb\u201csheet1\u201d\uff0c\u88682\u53eb\u201csheet2\u201d\uff0c\u5728\u5185\u5bb9\u5728A\u5217\u5f00\u59cb\uff0c\u8bf7\u5728\u88681\u7684B2\u8f93\u5165\u516c\u5f0f\uff1a
=INDEX(Sheet2!$B:$B,SMALL(IF(Sheet2!$A2:$A100=$A2,ROW($A2:$A100),10^2),COLUMN(A1)))&""
\u6441SHIFT\uff0bCTRL\uff0b\u56de\u8f66\u7ed3\u675f\u516c\u5f0f\u7684\u8f93\u5165\uff0c\u518d\u5c06\u516c\u5f0f\u5411\u53f3\u3001\u5411\u4e0b\u62c9\u586b\u5145\u5c31\u53ef\u4ee5\u4e86\u3002
不重复单号个数，使用下面公式：
=SUMPRODUCT(IFERROR((B2:B12=TRUE)*(C2:C12=FALSE)/COUNTIFS(A2:A12,A2:A12,B2:B12,TRUE,C2:C12,FALSE),0))
数组公式，同时按下Ctrl+Shift+Enter结束输入。
D列作辅助列，可隐藏
D2=IF(COUNTIF($A$2:A2,A2)>1,"",SUMPRODUCT(($B$2:$B$12=TRUE)*($C$2:$C$12=FALSE)*($A$2:$A$12=A2)))
E2=SUM(D2:D12)
=SUMPRODUCT((B2:B12=TRUE)*(C2:C12=FALSE)/COUNTIFS(A2:A12,A2:A12,B2:B12,B2:B12,C2:C12,C2:C12))
绛旓細=SUMPRODUCT(VLOOKUP(MID(D2,ROW(INDIRECT("1:"&LEN(D2))),1),A:B,2,))娉細鏈叕寮忓湪office2021棰勮鐗堟紨绀猴紝鍏跺畠鐗堟湰鍙兘闇瑕佹寜ctrl+shift+enter浜х敓鏁扮粍鍏紡鐨勮姳鎷彿锛屾墠鑳芥甯歌绠椼
绛旓細鍏紡涓猴細=IF(B1>=0.25,A1+C1+D1,0)濡傚浘鎵绀猴細
绛旓細鍦Excel琛ㄦ牸涓紝$鐨勫惈涔夋槸缁濆寮曠敤鐨勬剰鎬濄俢$1鍜孋1鐨勫尯鍒細1銆佸畾涔変笉鍚岋細c$1鏄粷瀵瑰湴鍧锛孋1鏄浉瀵瑰湴鍧銆2銆佸紩鐢ㄧ殑鍩哄噯涓嶅悓锛歝$1鏄崟鍏冩牸涓殑缁濆鍗曞厓鏍煎紩鐢紝鎬绘槸鍦ㄦ寚瀹氫綅缃紩鐢ㄥ崟鍏冩牸C1锛汣1鏄崟鍏冩牸涓殑鐩稿寮曠敤鍗曞厓鏍硷紝灏辨槸鐩存帴鐢ㄥ垪鏍囧拰琛屽彿琛ㄧず鍗曞厓鏍硷紝杩欐槸榛樿鐨勫紩鐢ㄦ柟寮忋3銆佽寮曠敤鏃舵暟鎹...
绛旓細瑙ｇ瓟:鎴戠紪鍒朵簡鍥涗釜鍗曞厓鏍肩殑鍏紡銆傚彲浠ョ湅涓涓嬬収鐗囥傛槸涓嶆槸瀵逛綘鏈甯姪銆
绛旓細B2鍏紡=OFFSET('9鏈堜唤'!F$3,VLOOKUP(A2,{"鐑熷彴",1;"榫欏彛",16},2,0),MATCH(B$1,'9鏈堜唤'!$G$3:$AK$3),1,1)
绛旓細浜诧紝杩欎釜鍙渶瑕佸缓绔嬪洓椤规潯浠舵牸寮忓嵆鍙傞変腑鏁版嵁鍖哄煙锛屼娇鐢ㄥ叕寮忚缃潯浠舵牸寮忥紝鍥涢」鏉′欢鏍煎紡鐨勫叕寮忛兘涓猴細=AND(A1>0,A2>0,A3>0,A4>0)浣嗘槸锛屽洓椤规潯浠舵牸寮忕殑浣滅敤鍖哄煙涓嶅悓锛岀涓涓槸A1:Bxx锛岀2涓粠A2寮濮婣2:Bxx锛岀3涓槸A3:Bxx锛岀4涓槸A4:Bxx銆傝瑙佷笅鍥撅細...
绛旓細绗竴涓潯浠舵病璇存槑鏄垨鍏崇郴杩樻槸涓庡叧绯伙紝鏍规嵁涓嬮潰鐨勬弿杩帮紝鐚滄祴鏄垨鍏崇郴銆傚叕寮忎负锛=IF(OR(I3<=10,J3<=7),1,IF(OR(I3<=15,J3<=15),0.75,IF(OR(I3<=25,J3<=20),0.5,0)))濡傚浘鎵绀猴細
绛旓細Excel璁剧疆鏁版嵁鏈夋晥鎬у疄鐜板崟鍏冩牸涓嬫媺鑿滃崟鐨3绉嶆柟娉 涓銆佺洿鎺ヨ緭鍏ワ細1.閫夋嫨瑕佽缃殑鍗曞厓鏍硷紝璀A1鍗曞厓鏍硷紱2.閫夋嫨鑿滃崟鏍忕殑鈥滄暟鎹濃啋鈥滄湁鏁堟р濃啋鍑虹幇鈥滄暟鎹湁鏁堟р濆脊鍑虹獥鍙ｏ紱3.鍦ㄢ滆缃濋夐」涓啋鈥滄湁鏁堟ф潯浠垛濃啋鈥滃厑璁糕濅腑閫夋嫨鈥滃簭鍒椻濃啋鍙宠竟鐨勨滃拷鐣ョ┖鍊尖濆拰鈥滄彁渚涗笅鎷夎彍鍗曗濆叏閮ㄦ墦鍕锯啋鍦ㄢ滄潵婧愨...
绛旓細璇风湅鍥撅細鏄笉鏄煡閬撴庝箞鍋氫簡锛屼綘闇瑕佺殑鍏紡鏄細=IF(LEFT(CELL("format",D1),1)="P",C1*D1,"")
绛旓細鐢ㄤ竴涓伐浣滆〃鍒楀嚭姣忎釜鍐滃巻骞村崄浜屼釜鏈堟垨鍗佷笁涓湀锛堟湁娑︽湀鐨勫勾锛夊垵涓瀵瑰簲鐨勫叕鍘嗘棩鏈燂紝杩欐牱鐢╒LOOKUP閰嶅悎鍏朵粬鍑芥暟鏉ユ煡璇紝灏辫兘淇濊瘉鏌ヨ鐨勭粨鏋滃噯纭傚笇鏈涗笂闈㈢殑鍒嗘瀽鑳甯姪浣犳潵瑙ｈ鍏紡銆傚綋鐒朵笂闈㈢殑鍒嗘瀽锛屾槸鍋囪浣犲DATE銆丼MALL銆乀EXT绛夊嚱鏁版湁鏈鍩烘湰鐨勪簡瑙ｏ紝灏辨病鏈変竴姝ヤ竴姝ュ幓璇︾粏鍒嗘瀽DATE绛夊嚱鏁颁簡銆
