Excel用什么公式可以把人员信息表按成员关系分类信息向右自动填充？ 在EXCEL工作表中，用什么公式怎么让数据自动填充

\u4e07\u80fd\u7684\u7f51\u53cb\uff0cExcel\u7528\u4ec0\u4e48\u516c\u5f0f\u53ef\u4ee5\u628a\u6237\u7c4d\u4fe1\u606f\u8868\u628a\u6237\u7c4d\u6210\u5458\u4fe1\u606f\u6309\u6237\u53f7\uff0c\u6210\u5458\u5173\u7cfb\u5411\u81ea\u52a8\u586b\u5199\u5230\u53e6\u4e00\u5f20\u8868\uff1f

c3:
=TEXT(IFERROR(INDEX(Sheet1!$B$1:$B$30000,SMALL(IF(Sheet1!$A$1:$A$30000=$B3,ROW($1:$30000),""),INT((COLUMN()-3)/4)+1)),""),"0;0;")
d3:
=TEXT(IFERROR(INDEX(Sheet1!$E$1:$E$30000,SMALL(IF(Sheet1!$A$1:$A$30000=$B3,ROW($1:$30000),""),INT((COLUMN()-3)/4)+1)),""),"0;0;")
e3:
=TEXT(IFERROR(INDEX(Sheet1!$C$1:$C$30000,SMALL(IF(Sheet1!$A$1:$A$30000=$B3,ROW($1:$30000),""),INT((COLUMN()-3)/4)+1)),""),"0;0;")
f3:
=TEXT(IFERROR(INDEX(Sheet1!$G$1:$G$30000,SMALL(IF(Sheet1!$A$1:$A$30000=$B3,ROW($1:$30000),""),INT((COLUMN()-3)/4)+1)),""),"0;0;")
\u6570\u7ec4\u516c\u5f0f\uff0c\u5728\u7f16\u8f91\u72b6\u6001\u4e0b\u540c\u65f6\u6309\u4e0bCtrl+Shift+Enter,\u5f53\u51fa\u73b0\u5982\u56fe\u7bad\u5934\u6240\u6307\u5927\u62ec\u53f7\u5373\u53ef\u3002
\u5982\u56fe\u4fe1\u606f\u6392\u5217\u597d\u4e00\u6761\u516c\u5f0f\u5c31\u53ef\u4ee5\u4e86
\u7136\u540e\u5321\u9009C3:F3\uff0c\u5f80\u53f3\u7c98\uff0c\u5982\u56fe\uff1a

\u7c98\u8d34\u597d\u540e\u53ef\u4ee5\u4e0b\u62c9\uff0c\u5acc\u4e0b\u62c9\u9ebb\u70e6\u53ef\u4ee5\u8fd9\u6837,\u70b9C3\u5355\u5143\u683c\uff0c\u7136\u540e\u7bad\u5934\u6240\u6307\u5730\u65b9\u8f93\u4e0b"\uff1aV30000"
\u6309\u56de\u8f66\uff0c\u6700\u540e\u6309CTRL+D

\u81f3\u4e8eB\u5217\u6237\u53f7\u5728\u4fe1\u606f\u8868\u683c\u53bb\u91cd\u540e\u76f4\u63a5\u7c98\u8fc7\u6765\uff0c\u518d\u590d\u539f\uff0c\u6015\u8bef\u5220\u8bef\u64cd\u4f5c\u53ef\u4ee5\u590d\u5236\u4e2a\u526f\u672c


\u56e0\u4e3a\u6015\u5361\uff0c\u6240\u4ee5\u53ea\u670930000\u884c\uff0c\u53ef\u4ee5\u6839\u636e\u8981\u6c42\u66f4\u6539\u3002

\u9009\u62e9\u51ed\u8bc1\u53f7\u7684\u6240\u5728\u5217\u7684\u6240\u6709\u6570\u636e\u533a\u57df
CTRL+G\u6253\u5f00\u5b9a\u4f4d\u5bf9\u8bdd\u6846,\u5b9a\u4f4d\u6761\u4ef6,\u9009\u62e9"\u7a7a\u503c",\u786e\u5b9a
\u8f93\u5165= \u518d\u6309\u5411\u4e0a\u7684\u65b9\u5411\u7bad\u5934\u7bad.\u6700\u540e\u6309CTRL+ENTER\u7ed3\u675f
\u7136\u540e\u590d\u5236\u8fd9\u5217,\u5355\u51fb\u53f3\u952e, \u9009\u62e9\u6027\u7c98\u8d34,\u9009\u62e9"\u6570\u503c"
\u786e\u5b9a

G1:     =OFFSET($B$1,,MOD(COLUMN(A1)-1,5))

G2:    =OFFSET($B$3,(COLUMN(A1)-1)/5,MOD(COLUMN(A2)-1,5))

两公式右拉

在G1中输入或复制粘贴下列公式之一
=OFFSET($B$1,,MOD(COLUMN(A1)-1,5))
=INDEX($B1:$F1,MOD(COLUMN(A:A)-1,5)+1)
=INDIRECT(ADDRESS(1,MOD(COLUMN(A:A)-1,5)+2))
在G2中输入或复制粘贴下列公式
=OFFSET($B$3,(COLUMN(A1)-1)/5,MOD(COLUMN(A2)-1,5))
=INDEX($B:$F,(COLUMN(A1)-1)/5+3,MOD(COLUMN(A2)-1,5)+1)
=INDIRECT(ADDRESS((COLUMN(A1)-1)/5+3,MOD(COLUMN(A2)-1,5)+2))
选择G1:G2，右拉填充

涓涓Excel 琛ㄦ牸閲岄潰鏈夐噸澶嶇殑鍚嶅瓧,鐢ㄤ粈涔堝叕寮忓彲浠鎻愬彇鎵鏈夊鍚嶄笖涓嶉噸澶...
绛旓細鍚屾椂灏鈥滆浆鎹负宓屽琛ㄦ牸鈥濈殑閽╁幓鎺夛紞纭畾锛嶅鍒讹紞杩斿洖EXCEL宸ヤ綔琛紞鍗曞嚮F1鍗曞厓鏍硷紝绮樿创锛嶈繖鏃跺彲瑙丄銆丅銆丆銆丏鍥涘垪鍚堝苟鍦‵鍒椼2銆佸湪E1鍗曞厓鏍艰緭鍏鍏紡=IFERROR(INDEX($F$1:$F$12,SMALL(IF(MATCH($F$1:$F$12,$F$1:$F$12,0)=ROW($F$1:$F$12),ROW($F$1:$F$12),65536),ROW(A1))...

Excel鐢ㄤ粈涔堝叕寮鏄剧ず姣忓ぉ鐧昏澶氬皯浜哄憳?
绛旓細鏈绠鍗曟湁鏁堢殑鏂规硶鏄細绗5琛屾彃鍏1琛岋紝鎶婃柊鐨7锝36琛岀敤COUNT鍑芥暟鍏紡鍙虫媺濉厖锛屽嵆鍙眰寰楁瘡涓澶╃殑浜烘暟锛岀湅鐪嬫柟渚跨洿瑙傜粷瀵逛笉浼氬嚭閿欙紝鑰屼笖鏁板艰繕鍙互寮曠敤浜庢牳绠楃殑

鎬庝箞鐢鍏紡浣excel鏁版嵁鍖归厤
绛旓細1銆佸湪涓涓伐浣滆杽鏂板缓涓ゅ紶琛紝渚嬪涓嬪浘锛屼笅闈㈤氳繃鍏紡鎶婁汉鍛宸ュ彿鍔犲叆鍒扮涓寮犺〃涓傦紙鍥剧墖閲岄潰涓墍鐢ㄨ韩浠借瘉鍙蜂笉鏄湡瀹炰俊鎭傦級2銆佹垜浠湪鈥滃伐鍙蜂笌韬唤璇佸彿瀵圭収琛ㄢ濅腑鎻掑叆涓鍒椻滃伐鍙封濓紝濡傚浘锛3銆佸湪鍗曞厓鏍间腑杈撳叆鍏紡=vlookup()锛岀劧鍚庣偣鍑诲彟涓寮犺〃锛屽鍥撅細4銆佸叕寮忕殑鍙傛暟濡傚浘鎵绀猴細5銆佸崟鍑荤‘瀹氬畬鎴...

excel鎵句竴鍒楁暟鐨勯噸澶浜哄憳,鏈浠涔堝叕寮,鏈濂芥槸鎷変笅鏉ュ悗閲嶅浜哄憳鍙樉绀轰竴涓...
绛旓細鍋囧畾濮撳悕鍦ˋ鍒楋紝浠庣浜岃寮濮 b2杈撳叆=if(a$2:a2,a2)=1,a2,"")涓嬫媺澶嶅埗濉厖鍏紡

鎬庝箞鐢Excel鎶婁汉鍛鍚嶅崟闅忔満鍒嗘垚鍑犱釜缁?
绛旓細鐢Excel鎶婁汉鍛鍚嶅崟闅忔満鍒嗙粍鎿嶄綔鏂瑰紡濡備笅锛氫竴銆侀鍏堝皢鏁版嵁閮芥寜鐓ф牸寮忕綏鍒楀ソ锛屾澶勪互闅忔満鍒嗕负4缁勪负渚嬶紱浜屻佸皢姣忎釜鏁版嵁鍒嗛厤涓涓殢鏈烘暟 RAND 鍑芥暟鍙互浜х敓涓涓粙浜 0 鍜 1 涔嬮棿鐨勫皬鏁帮紝骞朵笖姣忎釜闅忔満鏁板潎涓嶇浉鍚岋紝鍙互浣滀负鐩爣鏁版嵁瀵瑰簲鐨勫敮涓鍊笺鍏紡濡備笅锛氫笁銆佹寜鐓ч殢鏈烘暟鎺掑簭 涓婁竴姝ヤ骇鐢熺殑鏁板瓧锛屽洜姝ゅ彲浠ュ鍏...

鏈夋病鏈夊ソ鐨鍏紡鎴栨柟娉曞尯鍒excel琛ㄤ腑浜哄憳涓嶅悓骞撮緞娈电殑浜烘暟?
绛旓細鍏堢敤鍏紡锛=text(a1,"#-00-00")灏嗗嚭鐢熸棩鏈熻浆鍖栦负鏃ユ湡鏍煎紡銆傚啀鐢ㄥ叕寮锛=datedif(b1,"2009-12-20","y")绠楀嚭骞撮緞銆傚啀鐢╢requency鍑芥暟涓娆＄粺璁″畬鎴愬嵆鍙 鍏充簬frequency鍑芥暟鐨勭敤娉曡鍙傝冿細FREQUENCY 璇峰弬闃 浠ヤ竴鍒楀瀭鐩存暟缁勮繑鍥炴煇涓尯鍩熶腑鏁版嵁鐨勯鐜囧垎甯冦備緥濡傦紝浣跨敤鍑芥暟 FREQUENCY 鍙互璁＄畻鍦ㄧ粰瀹氱殑鍒嗘暟鑼冨洿...

excel鍏紡涓湁浠涔堝彲浠缁熻浜哄憳骞撮緞娈垫暟鐩?
绛旓細姹傝储鍔20-29宀佷汉鏁=SUMPRODUCT((B2:B5>=20)*(B2:B5<=29))姹傝储鍔30-39 宀佷汉鏁=SUMPRODUCT((B2:B5>=30)*(B2:B5<=39))姹傝储鍔40-49宀佷汉鏁=SUMPRODUCT((B2:B5>=40)*(B2:B5<=49))鍏朵粬鐨勭敤鐩稿悓鐨勬柟娉曟眰鍙栧嵆鍙

excel鍏紡涓湁浠涔堝彲浠缁熻浜哄憳骞撮緞娈垫暟鐩
绛旓細姹傝储鍔20-29宀佷汉鏁=SUMPRODUCT((B2:B5>=20)*(B2:B5<=29))姹傝储鍔30-39 宀佷汉鏁=SUMPRODUCT((B2:B5>=30)*(B2:B5<=39))姹傝储鍔40-49宀佷汉鏁=SUMPRODUCT((B2:B5>=40)*(B2:B5<=49))鍏朵粬鐨勭敤鐩稿悓鐨勬柟娉曟眰鍙栧嵆鍙

excel姹傚鍒楁暟鎹眰鏈楂樺煎搴旂殑鎵鏈変汉鐢ㄤ粈涔堝叕寮
绛旓細鍙傝冨浘绀 锛=INDEX(A1:A30,SUMPRODUCT(ROW(B3:AD30)*(B3:AD30=MAX(B3:AD30)))涓嶈繃鏈変釜闂锛屽氨鏄渶楂樺煎彧鑳芥湁涓涓紝涓嶈兘閲嶅锛屽惁鍒欑粨鏋滀細閿欍傚鏋滄渶楂樺兼湁閲嶅鐨勬儏鍐碉紝灏遍渶瑕佺敤鏁扮粍鍑芥暟浜嗭紝閭ｄ釜鍗犵敤澶珮浜 ...

杩欑鎯呭喌(濡傚浘)鎬庝箞鍦Excel鐢ㄥ叕寮鍒嗗埆缁熻鍑洪攢鍞骇鍝佷竴銆佷骇鍝佷簩銆佷骇鍝...
绛旓細鍦ㄦ渶涓婃柟鎻掑叆涓琛 A1杈撳叆=subtotal(3,A3:A500)杩欐槸浣犵瓫閫夋椂浣跨敤鐨勬煡鐪嬭绠椻︹︿綘鍙互浣跨敤鏁版嵁閫忚琛 濮撳悕浣滀负琛 浜у搧浣滀负鍒 瀵 濮撳悕 杩涜璁℃暟鈥︹﹁繖鏍锋潵鑾峰緱浣犵殑鍊尖︹﹀垪姹囨 琛屾眹鎬 閮藉彇娑堚︹︽渶鍚 璁＄畻 姣忎竴绉嶄骇鍝佸垪涓嬬殑浣跨敤 count 鍑芥暟 璁＄畻鏈夋暟鎹殑鍗曞厓鏍兼暟閲 灏辨槸浣犺姹傜殑浜烘暟鈥︹...

扩展阅读：excel常用公式一览表 ... 会计表格公式大全 ... excel如何把公式一拉到底 ... excel锁定公式$行 ... excel十个常用公式 ... excel公式锁定一列$ ... 100个excel必背公式 ... excel公式锁定固定值 ... excel怎样快速算乘法 ...