excel一列中每100个数求平均值 怎么在excel的一列中每四个数求平均值
\u5728excel\u4e2d\u5982\u4f55\u81ea\u52a8\u7684\u4ece\u4e00\u5217\u6570\u636e\u4e2d\u6bcf100\u4e2a\u6570\u636e\u6c42\u5e73\u5747\u503c\uff0c\u5e76\u586b\u5145\u5230\u522b\u7684\u5217\u4e0a\u5047\u5982\u6570\u636e\u5728A1:A10000,\u5e73\u5747\u503c\u4eceB1\u5f00\u59cb\uff0c\u516c\u5f0f\u4e3a
=AVERAGE(offset\uff08$A$1\uff0c(ROW(A1)-1)*100\uff0c0\uff0c100\uff0c1\uff09)
\u7136\u540e\u4e0b\u62c9\u516c\u5f0f\uff0c\u5c31\u5f97\u5230\u6bcf\u4e00\u767e\u884c\u6570\u636e\u7684\u5e73\u5747\u503c\u3002
\u4f60\u7684\u95ee\u9898\u4e0d\u592a\u6e05\u695a\uff0c\u6700\u597d\u80fd\u6709\u5b9e\u4f8b\uff01
\u57fa\u4e8e\u5047\u8bbe\uff0c\u7ed9\u4f60\u4e2a\u516c\u5f0f\uff0c\u6839\u636e\u81ea\u5df1\u7684\u60c5\u51b5\u4fee\u6539\u54af\uff01
\u5047\u8bbe\uff1a
a1\u5230a4\u5355\u5143\u683c\u90fd=1\uff0ca5\u5230a8\u5355\u5143\u683c\u90fd=2\uff0ca9\u5230a12\u5355\u5143\u683c\u90fd=3
\u5728b1\u8f93\u5165\u201c=AVERAGE(INDIRECT("A"&(ROW()*4-3)&":A"&(ROW()*4)))\u201d
\u590d\u5236b1\u7684\u516c\u5f0f\u5230b2\uff0cb3\u5355\u5143\u683c
\u5373\u53ef\u5728b1\u6c42\u51faa1\u5230a4\u7684\u5e73\u5747\uff0cb2\u6c42\u51faa5\u5230a8\u7684\u5e73\u5747\uff0cb3\u6c42\u51faa9\u5230a12\u7684\u5e73\u5747
=AVERAGE(OFFSET(A$1,(ROW(A1)-1)*100,,100,))
公式表示：每下隔一行，以A1向下选取100行的方式偏移100行，求平均值。
假如数据在a1:a10000,平均值从b1开始，公式为
=average(offset（$a$1，(row(a1)-1)*100，0，100，1）)
然后下拉公式，就得到每一百行数据的平均值。
b1输入
=AVERAGE(INDIRECT("A" & (ROW()-1)*100+1):INDIRECT("A" & ROW()*100))
往下拉
绛旓細鍋囪a鍒涓哄師鏉ユ暟鎹紝b鍒椾负浣犲姞浠锋暟鎹紝濡俠1=a1+20 鍚戜笅澶嶅埗瀹屽叕寮忥紝寰楀埌瀹屾暣鍔犱环鏁版嵁锛涢変腑b鍒楋紝鈥滃鍒垛濓紝鈥滅紪杈戔濊彍鍗曚腑瀵婚夋嫨鎬х矘璐粹濓紝瀵绘暟鍊尖濆氨鍙互浜嗭紒
绛旓細鍏堣缃竴涓緟鍔╁崟鍏冩牸L1锛岃绠楁诲拰 L1=TEXT(SUM(A2:A11)*10000000+SUM(B2:B11)*1000000+SUM(C2:C11)*100000+SUM(D2:D11)*10000+SUM(E2:E11)*1000+SUM(F2:F11)*100+SUM(G2:G11)*10+SUM(H2:H11)*1+SUM(I2:I11)*0.1+SUM(J2:J11)*0.01,"0.00")灏嗗悎璁″垎鎴愪袱閮ㄥ垎 1.A-H鍒...
绛旓細濡傚浘锛欱1鍗曞厓鏍煎叕寮忎负锛=SUMPRODUCT((A1:A200<=SMALL(A1:A200,ROUND(COUNT(A1:A200)*0.2,0)))*A1:A200)/ROUND(COUNT(A1:A200)*0.2,0)
绛旓細Excel 姣10琛屾眰骞冲潎鍊兼眰鍏紡锛屽涓嬶細E2:=IFERROR(AVERAGEA(INDIRECT("B"&ROW(A1)*10-(10-2)):INDIRECT("B"&ROW(A1)*10+1)),"")涓嬫媺 鎴栬 =IFERROR(AVERAGE(OFFSET(B$1,ROW(A1)*10-(10-1),,10)),"")涓嬫媺 鎯虫敼鎴愭瘡澶氬皯涓钩鍧囷紝鍙浣犳妸鎴戝叕寮忛噷鎵鏈夌殑鈥10鈥滄敼鎴愪綘鎯宠鐨涓暟...
绛旓細鍋囪姝鍒楁暟瀛涓篈鍒楄緭鍏ワ細=SUMPRODUCT((A:A>=0)*(A:A<=100)*1)
绛旓細濡備綍鎻愬彇涓鍒鏁版嵁鐨勫拰绛変簬涓涓浐瀹氬硷紝濡傛灉娌℃湁鐩哥瓑鐨勶紝鎻愬彇鏈鎺ヨ繎杩欎釜鍥哄畾鍊肩殑涓缁勬暟鎹 鍦excel涓濡備綍鑷姩鐨勪粠涓鍒楁暟鎹涓瘡100涓鏁版嵁姹傚钩鍧囧硷紝骞跺～鍏呭埌鍒殑鍒椾笂 鍋囧鏁版嵁鍦ˋ1:A10000,骞冲潎鍊间粠B1寮濮嬶紝鍏紡涓 =AVERAGE(offset锛$A$1锛(ROW(A1)-1)*100锛0锛100锛1锛) 鐒跺悗涓嬫媺鍏紡...
绛旓細姣斿璇村皢鏌涓鍒鐨勬姤浠锋暟閮+100,濡傛灉涓琛岃鍔犳湭鍏嶅お楹荤儲,濡傛灉鐢ㄥ鍔犱竴琛屽啀鐢ㄥ叕寮忔眰鍜岀殑鏂规硶,杩欎釜Excel琛ㄦ牸鍥犱负瑕佸彂缁欏鎴,鎵浠ヨ鏄惧緱涔熶笉鍙銆傚洜涓哄鏋滀綘鍦–鍒楃殑鍩虹涓婂姞涓涓狣鍒(涓存椂鍒),鐒跺悗灏咲鍒楄缃负鍏紡鈥=C+100鈥,鍥虹劧鑳芥眰鍑虹粨鏋,浣嗘槸濡傛灉浣犲皢C鍒楀垹鍘,D鍒楃殑鍊煎氨鍏ㄦ病鏈変簡(鍥犱负鏍规嵁鍏紡D鍒楃殑鍊兼槸渚濊禆...
绛旓細濡傛灉瑕佸湪Excel鑾峰彇鏌鍒椾腑姣忛殧N琛岀殑鍗曞厓鏍煎硷紝渚嬪鏁版嵁鍦ˋ1:A100鍖哄煙涓紝鐜板湪瑕佷粠A1鍗曞厓鏍煎紑濮嬶紝姣忎釜4琛岃幏鍙栧悇鍗曞厓鏍兼暟鎹紝鍗宠幏鍙朅1銆丄5銆丄9銆佲︹﹀崟鍏冩牸涓殑鏁版嵁锛屽苟灏嗗叾杩炵画鍦版斁鍒癊鍒椾腑浠嶦4鍗曞厓鏍煎紑濮嬬殑鍖哄煙涓紝鍙互鐢ㄤ笅闈㈢殑鏁扮粍鍏紡锛氬湪E4鍗曞厓鏍间腑杈撳叆鏁扮粍鍏紡锛=IF((ROW()-ROW($E$4))*4...
绛旓細鎵撳紑Excel锛岄変腑涓鍒鏁版嵁涓嬮潰鐨勪竴涓崟鍏冩牸锛岀劧鍚庣偣鍑诲彸涓婅鐨勮嚜鍔ㄦ眰鍜岋紝鍗冲彲鑷姩鐢熸垚鍚堣銆1銆佺偣鍑绘墦寮Excel琛ㄦ牸锛岄変腑涓鍒楁暟鎹笅闈㈢殑涓涓崟鍏冩牸銆2銆佺偣鍑诲彸涓婅鐨勮嚜鍔ㄦ眰鍜屻3銆佺偣鍑昏嚜鍔ㄦ眰鍜屽悗锛屽氨浼氬湪鍗曞厓鏍间腑鐢熸垚涓涓眰鍜屽叕寮忋4銆佹寜鍥炶溅閿悗锛屽嵆鍙敓鎴愬悎璁°傛敞鎰忎簨椤癸細濡傛灉涓嶆兂鐐瑰嚮鍙充笂瑙掔殑鑷姩姹傚拰锛...
绛旓細浣跨敤large鍑芥暟鍜宻mall鍑芥暟鍗冲彲銆傚湪B1涓緭鍏ュ叕寮忥細=large($a$1:$a$100,row(a1))锛屼笅鎷夊簲鐢ㄥ叕寮忓湪C1涓緭鍏ュ叕寮忥細=small($a$1:$a$100,row(a1))锛屼笅鎷夊簲鐢ㄥ叕寮
