Excel表二日期列A列对应的B列等于表一日期列C列中同一日期对应的D列的值？ 在EXCEL表中一列是日期，怎么用公式算出这列不同日期对应另...
excel\u8868\u4e2dA\u5217\u76f8\u540c\u540d\u79f0,B\u5217\u4e0d\u540c\u65e5\u671f\u5bf9\u5e94C\u5217\u6570\u636e\u7684\u548c,\u6c42\u516c\u5f0fe1=SUMIF(A:A,D1,C:C)
\u628aa\u5217\u7684\u6570\u636e\u590d\u5236\u5728d\u5217-\u6570\u636e-\u91cd\u590d\u9879-\u5220\u9664\u91cd\u590d\u9879\uff0c\u518d\u628a\u8fd9\u4e2a\u516c\u5f0f\u590d\u5236\u5728e1\u4e2d\u4e0b\u62c9\u586b\u5145\u516c\u5f0f\u5c31\u4f1a\u81ea\u52a8\u8ba1\u7b97\u51fa\u4f60\u8981\u7684\u6570\u636e\u4e86\u3002
\u4e3e\u4f8b\u6216\u4e0a\u56fe\u6765\u770b\u4e00\u4e0b\u4f60\u7684\u6570\u636e\u6837\u5f0f\u53ca\u6c42\u548c\u65b9\u5f0f(\u4e0a\u56fe\u65f6\u8bf7\u5e26\u4e0a\u884c\u53f7\u5217\u6807)
\u4f60\u8fd9\u6837\u51fa\u9898\u6ca1\u4eba\u80fd\u5e2e\u4e0a\u4f60\u7684
\u8868\u683c\u7684\u9876\u4e0a\u662f\u4e00\u6392\u5b57\u6bcdA,B,C,.......\u8fd9\u4e2a\u5c31\u662f\u5217\u6807
\u8868\u683c\u7684\u5de6\u8fb9\u662f\u4e00\u7ad6\u5217\u7684\u6570\u5b571,2,3,.......\u8fd9\u4e2a\u5c31\u662f\u884c\u53f7
\u5217\u6807\u52a0\u4e0a\u884c\u53f7\u5c31\u662f\u5c31\u662f\u5355\u5143\u683c\u7684\u540d\u79f0,\u5355\u5143\u683c\u540d\u79f0\u4e5f\u53eb\u505a\u5355\u5143\u683c\u5730\u5740,\u5982A\u5217\u7684\u7b2c\u4e09\u884c,\u4e3aA3\u5355\u5143\u683c,C\u5217\u7684\u7b2c18\u884c\u4e3aC18\u5355\u5143\u683c,\u7b2c\u4e94\u5217\u7684\u7b2c\u4e03\u884c\u5c31\u662fE7\u5355\u5143\u683c,\u8fd9\u6837\u5f62\u6210\u4e86\u4e00\u4e2a\u4e2a\u7684\u5750\u6807,\u6807\u660e\u4e86\u6bcf\u4e2a\u5355\u5143\u683c\u7684\u4f4d\u7f6e.
1、如果你的EXCEL支持TEXTJOIN函数，就可以直接用数组公式计算，如图B列
B2公式：=TEXTJOIN("+",TRUE,IF(表一!$A$2:$A$8=A2,表一!$B$2:$B$8,""))
按Ctrl+Shift+Enter组合键输入。
2、如果不支持以上函数，可以编写自定义函数完成，按ALT+F11组合键，打开VB窗口，执行"插入"-"模块"，复制下面代码进去，回到工作表，像使用普通公式一样使用它，如C2公式：=JOINIF(表一!$A$2:$A$8,A2,表一!$B$2:$B$8)
代码如下：
Function JOINIF(Rng1 As Range, Str, Rng2 As Range)
Dim arr, brr
Dim i As Long
Dim j As Long
Dim MyStr As String
If Rng1.Rows.Count > 65536 Then
arr = Rng1.Resize(65536, Rng1.Columns)
brr = Rng2.Resize(65536, Rng1.Columns)
Else
arr = Rng1
brr = Rng2
End If
For i = 1 To UBound(arr)
For j = 1 To UBound(arr, 2)
If arr(i, j) <> "" Then
If arr(i, j) = Str Then
MyStr = MyStr & brr(i, j) & "+"
End If
Else
Exit For
End If
Next j
Next i
JOINIF = Left(MyStr, Len(MyStr) - 1)
End Function
绛旓細M2:=IFERROR(INDEX($A$2:$J$2,MOD(SMALL(IF(OFFSET($B$3:$C$5,,MATCH(N2,$B$1:$J$1,)-1)>0,ROW(OFFSET($B$3:$C$5,,MATCH(N2,$B$1:$J$1,)-1))*10000+COLUMN(OFFSET($B$3:$C$5,,MATCH(N2,$B$1:$J$1,)-1))),COUNTIF($N$2:N2,N2)),10000)),"")N2:=...
绛旓細vlookup鏈変簺鏃犲姏銆傝屼笖涓嶆檽寰椾綘鐨勫彂璐鏃ユ湡鏄笉鏄贡鐨勩傚亣璁句綘璇村惉B琛ㄦ槸sheet2锛A琛鏄痵heet1锛屽啀鍋囪浣犵殑B琛ㄦ暟鎹槸A1:B100 閭ｄ箞sheet1琛ㄤ腑 B1杈撳叆 =TEXT(MAX(IF(Sheet2!$A$1:$A$100=A1,Sheet2!$B$1:$B$100)),"e-mm-dd")杈撳叆鍚庯紝涓嶈鍥炶溅锛岃屾槸鎸変笅ctrl+shift+鍥炶溅锛屼笁涓敭涓璧风粨鏉...
绛旓細excel 濡備綍鏌ユ壘涓や釜琛ㄧ浉鍚屾暟鎹墍瀵瑰簲鐨鏁版嵁锛 鍦琛2鐨凜4涓緭鍏ユ垨澶嶅埗绮樿创涓嬪垪鍏紡 =INDEX(琛1!A:A,MATCH(B4,琛1!B:B,)) 涓嬫媺濉厖 濡備綍鏌ユ壘浜屽紶琛A鍒鍜孊鍒楃浉鍚岀殑鏁版嵁,骞惰繑鍥濩鍒楃殑鍊 =IF(AND(Sheet1!A1=Sheet2!A1,Sheet1!B1=Sheet2!B1),Sheet1!C1,"")鍦excel琛涓紝姣旇緝B鍒楀拰A...
绛旓細鍙傝冧笅闈慨鏀逛笅 鎵╁睍搴旂敤浜 Excel鎸夎瀹氭彁鍙栧垪鏁版嵁
绛旓細鍏蜂綋鎿嶄綔姝ラ濡備笅锛1銆侀鍏堬紝鎵撳紑Excel锛岃緭鍏ヤ竴缁鏃ユ湡鏁版嵁锛岄渶瑕佽绠楀ぇ浜庝粖澶╃殑鏃ユ湡鏁板笺傚涓嬪浘鎵绀猴紝鐒跺悗杩涘叆涓嬩竴姝ャ2銆佸叾娆★紝閫夋嫨绌虹櫧鍗曞厓鏍硷紝鐒跺悗鍗曞嚮鈥 fx鈥濅互鎻掑叆鈥 COUNTIF鈥濆嚱鏁帮紝濡備笅鍥炬墍绀猴紝鐒跺悗杩涘叆涓嬩竴姝ャ3銆佹帴鐫锛屽湪寮瑰嚭绐楀彛涓紝鎸夌収涓嬪浘杈撳叆鍑芥暟鐩稿叧鐨勬暟鎹紝鐒跺悗鍗曞嚮銆愮‘瀹氥戯紝濡備笅鍥...
绛旓細鏃ユ湡鍦˙鍒楋紝浠ヤ笅鍏紡鏄互BC鍒椾负渚嬶紝璁＄畻B2锛孋2绗﹀悎鏉′欢涓暟 =COUNTIFS(B:B,B2,C:C,C2)
绛旓細浣犲湪琛2鐨B1杈撳叆鍏紡锛=iferror(vlookup(a1,琛1!a:b,2,0),"琛1鏃犳鍚嶇О")涓嬫媺璇曡瘯 璇存槑锛歩ferror浼氬皢Vlookup缁撴灉涓洪敊璇硷紙灏辨槸娌℃壘鍒扮殑锛夎繑鍥炰负锛氳〃1鏃犳鍚嶇О锛堟壘鍒颁簡鐨勫氨杩斿洖鎵惧埌B鍒楀硷級
绛旓細鍙互銆傚亣濡備綘瑕佸湪sheet1琛ㄥ疄鐜帮紝閭ｄ箞浣犲湪sheet2琛鍏堝仛涓涓瀵瑰簲鐨琛ㄤ緥濡傦細A B 1 鏁板 寮犱笁 2 璇枃 鏉庡洓 3 鑻辫 鐜嬩簲 鐒跺悗浣犲湪sheet1涓殑A鍒杈撳叆鏁板銆佽鏂囩瓑 鍦˙鍒楄緭鍏ヤ笅闈㈢殑鍏紡锛=VLOOKUP(A1,sheet2!$A$1:$B$3,2)灏卞彲浠ヤ簡銆傝瘯璇曠湅鍚 ...
绛旓細涓句緥璇存槑濡備笅锛1.AB涓ゅ垪鏁板硷紝B鍒楃浉瀵逛簬A鍒楁棩鏈熸湁缂哄け锛屾壘鍑虹己澶辩殑鏃ユ湡鏉ワ細2.杈撳叆鍏紡濡備笅锛氬叕寮忚В閲婏紝浠嶣鍒椾腑鏌ユ壘A鍒楁瘡涓崟鍏冩牸锛屽鏋滆兘鏌ユ壘鍒帮紝灏辫繑鍥濨鍒楃殑鍊硷紱濡傛灉鍖归厤涓嶄笂锛屽垯浼氬嚭鐜伴敊璇粨鏋滐紝閫氳繃杩欐牱灏卞彲浠ユ壘鍑築鍒椾腑缂哄け鐨勬棩鏈 3.涓嬫媺濉厖濡備笅锛欳鍒楁樉绀#N/A鐨勫搴旂殑A鍒楁棩鏈锛屽嵆涓虹己澶辩殑...
绛旓細鏂规硶涓锛氬墠鍙版搷浣 鍦╯heet2鐨凙1鎴栬匒2鍗曞厓鏍艰緭鍏ョ瓑浜庡彿鈥=鈥濓紝鍒囨崲鍒皊heet1锛岀偣鍑诲搷搴旂殑鍗曞厓鏍硷紝鐒跺悗鎸塃nter閿 杩斿洖sheet2涓紝浣犺緭鍏=鐨勫崟鍏冩牸鍊煎凡缁忔敼鍙樸傜偣鍑昏鍗曞厓鏍硷紝鍙抽敭澶嶅埗锛岀劧鍚庨夋嫨鏁翠釜A鍒锛屽彸閿夋嫨鎬х矘璐达紝閫夋嫨鍏紡锛岀偣鍑荤‘瀹氥俹k锛屼綘鐨勫姛鑳藉疄鐜般傛柟娉曚簩锛歏BA鎵ц浠ｇ爜 dim i as long for ...
