excel每间隔10行提取一个数据使用(是文字) excel里有一组数据 如果我要每隔5行或者10行提取一组数...
\u5728Excel\u4e2d\uff0c\u5982\u4f55\u6bcf\u969410\u884c\u63d0\u53d6\u4e00\u4e2a\u6570\u5b57\uff1f\u53ef\u4ee5\u901a\u8fc7\u8f93\u5165\u516c\u5f0f\u7684\u547d\u4ee4\u8fdb\u884c\u63d0\u53d6\uff0c\u5177\u4f53\u64cd\u4f5c\u6b65\u9aa4\u5982\u4e0b\uff1a
\u5de5\u5177/\u539f\u6750\u6599
excel2018\u7248\u3001\u7535\u8111\u3002
1\u3001\u6253\u5f00\u7535\u8111\u627e\u5230\u5e76\u70b9\u51fbexcel2018\u65b0\u5efa\u5de5\u4f5c\u8868\u683c\uff1b
2\u3001\u6253\u5f00excel\u5de5\u4f5c\u8868\u4ee5\u540e\uff0c\u4e3a\u4e86\u793a\u8303\u5148\u5728\u7a7a\u767d\u8868\u683c\u5185\u8f93\u5165\u4efb\u610f\u7684\u6570\u5b57\u5185\u5bb9\uff1b
3\u3001\u9009\u4e2dB\u5217\u6240\u6709\u7684\u5355\u5143\u683c\uff0c\u4e3a\u4e86\u793a\u8303\u7684\u6548\u679c\uff0c\u4ee5\u96945\u884c\u53d6\u4e00\u4e2a\u6570\u5b57\u4e3a\u4f8b\u3002\u7136\u540e\u5728\u7f16\u8f91\u680f\u91cc\u8f93\u5165\u516c\u5f0f\uff1a=OFFSET($A$1,(ROW(A1)-1)*5,0)\uff0c\u82e5\u9700\u969410\u884c\u53ea\u9700\u5c065\u6539\u621010\u5373\u53ef\uff1b
4\u3001\u8f93\u5165\u516c\u5f0f\u4ee5\u540e\uff0c\u6b64\u65f6\u5728\u6309\u201dctrl+\u56de\u8f66\u952e\u201c\u5373\u53ef\u5b8c\u6210A\u5217\u6240\u6709\u6570\u636e\u7684\u63d0\u53d6\u5de5\u4f5c\u3002
\u6709\u4e00\u4e2a\u5f88\u7b80\u5355\u7684\u516c\u5f0f\u6211\u5fd8\u4e86\uff01
\u544a\u8bc9\u522b\u4e00\u4e2a\u529e\u6cd5\u7ed9\u4f60\u5427\uff01
\u4f60\u5148\u53e6\u63d2\u5165\u4e00\u5217\uff0c{=MOD(ROW(A1),5)}\uff0c\u7136\u540e\u5c06\u516c\u5f0f\u5f80\u4e0b\u62d6\uff0c\u6bcf\u5230\u7b2c5\u884c\u7684\u65f6\u5019\u6570\u5b57\u4f1a\u53d8\u6210\u201c0\u201d\uff0c\u7136\u540e\u7b5b\u9009\u201c0\u201d\uff0c\u5c31\u662f\u6bcf\u96945\u884c\u9009\u53d6\u7684\u884c\u3002\u5982\u679c\u662f\u96948\u884c\uff0c\u5c06\u516c\u5f0f\u6539\u4e3a{=MOD(ROW(A1),8)}\u518d\u7b5b\u9009\u5c31\u884c\u4e86\uff01
下拉
绛旓細B2=IF(MOD(ROW(A2),10)=1,"",IF(MOD(ROW(A2),10)=2,A1,B1))
绛旓細姣斿鍙朅鍒楃殑A1锛孉11锛孉21鈥︹︽暟鎹紝B1杈撳叆=OFFSET(A$1,(ROW(A1)-1)*10,)鍚戜笅濉厖銆
绛旓細=INDEX(宸ヤ綔琛2!B:B,ROW(A1)*10-2)涓嬫媺
绛旓細姹傚拰=SUMPRODUCT(N(INDIRECT("K"&ROW($1:$999)*10-1)))缁熻杩欐牱姣忛殧10琛鐨勬暟鍊煎崰澶氬皯鍗曞厓鏍=SUM((N(INDIRECT("K"&ROW($1:$10)*10-1))>0)*1)鏁扮粍鍏紡鍙栧钩鍧囧=SUMPRODUCT(N(INDIRECT("K"&ROW($1:$999)*10-1)))/SUM((N(INDIRECT("K"&ROW($1:$10)*10-1))>0)*1)...
绛旓細鏈変竴涓寰堢畝鍗曠殑鍏紡鎴戝繕浜嗭紒鍛婅瘔鍒竴涓姙娉曠粰浣犲惂锛佷綘鍏堝彟鎻掑叆涓鍒楋紝{=MOD(ROW(A1),5)}锛岀劧鍚庡皢鍏紡寰涓嬫嫋锛屾瘡鍒扮5琛岀殑鏃跺欐暟瀛椾細鍙樻垚鈥0鈥濓紝鐒跺悗绛涢夆0鈥濓紝灏辨槸姣忛殧5琛岄夊彇鐨勮銆傚鏋滄槸闅8琛岋紝灏嗗叕寮忔敼涓簕=MOD(ROW(A1),8)}鍐嶇瓫閫夊氨琛屼簡锛
绛旓細鍋囪鍘熸暟鎹槸鍦╯heet1 鎴戜滑鍙互鍦╯heet2鐨凙1鍗曞厓鏍艰緭鍏ュ叕寮 =index(A:A,row(a1)*10-9)鍚戝彸濉厖,鍚戜笅濉厖鍗冲彲
绛旓細杩欎釜鍏紡鏄互姣10琛屾彁鍙栦竴涓鏁板硷紝濡傛灉鏄100 鍏紡灏辨槸=INDIRECT("C"&ROW()*100)璁板緱鍏紡閲岄潰C涓轰綘鐨勬暟鎹墍鍦ㄥ垪锛屼綘鍙互鑷繁鏀广
绛旓細锛堜负浜嗘柟渚夸笅鎷夛級鍦ㄩ夊畾鐨勫垪鐨勫彸杈癸紝鎻掑杈呭姪鍒 鍦ㄩ渶瑕佷繚鐣欑殑绗竴琛岀殑杈呭姪鍒楀唴濉緭鍏ヤ换鎰忔枃瀛楋紝濡傚瓧姣岮 閫夊彇杈呭姪鍒椾腑A鐩搁偦鐨勫崄涓崟鍏冩牸 鎶婇紶鏍囩Щ鍒伴夋嫨鍚庡尯鍩熺殑鏈鍙充笅瑙 鍙屽嚮锛堜笅濡傛灉鍓嶉潰鐨勫崟鍏冩牸鏈変笉杩炵画鐨勶紝閭g敤涓嬫媺鐨勬柟娉曪級閫夋嫨杈呭姪鍒楋紝CTRL+G瀹氫綅锛岀┖鍊 鍙抽敭鐐瑰叾涓涓涓鍗曞厓鏍硷紝鍒犻櫎琛 纭畾灏...
绛旓細鎻掑叆涓鍒楋紝鐒跺悗鍦ㄨ閫夌殑涓琛屽仛涓鐩稿悓鐨勬爣璁帮紝鍥犱负姣忛殧鍗佽浣涓涓鏍囪鎵浠ュ仛瀹岀浜屼釜鍚庡彲浠ョ敤澶嶅埗鏉ュ仛鏍囪銆傛渶鍚庣敤绛涢夋垨鑰呮帓搴忕殑鍔炴硶鎶婁綘瑕佺殑琛岄夊嚭鏉ャ
绛旓細娌¤鏄庣櫧浣犲叿浣撹姹傛槸浠庣鍑犺寮濮姣忛殧10琛屽彇鏁版嵁锛屽鏋滄槸浠庣9琛屽叕寮忓紑濮嬪叕寮忎负锛=IF(MOD(ROW()+1,10)=0,"鍙","")濡傛灉鏄粠绗10琛屽紑濮嬪叕寮忥細=IF(MOD(ROW(),10)=0,"鍙","")濡傛灉鏄粠绗11琛屽紑濮嬪叕寮忥細=IF(MOD(ROW()-1,10)=0,"鍙","")姣忔鍙鏃跺彧瑕佷綘鍙樹笅ROW()鍚庨潰鐨勬暟鍊硷紝鑷繁...