Excel-手动资料剖析(MID,ROW,COLUMN,阵列公式)
参考下图,在 Excel 中,若是要将贮存格内容「877/13/3214/6481/643/4486」的文字,依其分隔符号『/』掏出『877、13、3214、6481、643、4486』,该若何处置?注重其分隔符号之间的内容的文字长度其实不一致。
凡是,我们会利用 Excel 中的「资料部析」东西来处置,手动操纵也很便利,可是若是想要以公式来处置,或像下图中要把掏出的资料放在统一栏中。(资料分解东西只能将资料部析成果放在统一列中)
【公式设计与解析】
参考上图,假定:本例的资猜中有五个分隔符号『/』,将资料分成六组。
1. 资料分解成果置于统一列
计较分隔符号『/』的位置,贮存格C7:
{=SMALL(IF(MID($C2,ROW($1:$30),1)="/",ROW($1:$30),999),COLUMN(A:A))}
这是阵列公式,输入完成要按 Ctrl+Shift+Enter 键,Excel 会主动插手「{}」。
複製贮存格C7,贴至贮存格C7:G7。
(1) MID($C2,ROW($1:$30),1)
假定贮存格中的文字长度不跨越 30 个字,在阵列公式中 ROW(1:30) 代表{1,2,3, ... 29, ,30} 阵列。
MID($C2,ROW($1:$30),1) 可以掏出贮存格C2中第 1, 2, 3, ..., 29, 30 个字。
(2) IF(MID($C2,ROW($1:$30),1)="/",ROW($1:$30),999)
MID($C2,ROW($1:$30),1)="/" 用以判定第(1)式掏出的每一个字是不是为分隔符号『/』。
若是判定成果为真则传回所有位置(1~30),不然就传回『999』,该数只是一个很年夜的数,且比贮存格中的文字长度还要年夜。
(3) SMALL(IF(MID($C2,ROW($1:$30),1)="/",ROW($1:$30),999),COLUMN(A:A))
操纵 SMALL 函数掏出第 1, 2, 3, 4, 5 个分隔符号(/)的位置。此中 COLUMN(A:A)=1,当公式向右複製时,COLUMN(A:A)=1→COLUMN(B:B)=2→ ... →COLUMN(E:E)=5。
(4) 掏出第1组文字
贮存格C11:=MID(C2,1,C7-1)
(5) 掏出第2~5组文字
贮存格D11:=MID($C2,C7+1,D7-C7-1)
複製贮存格D11,贴至贮存格D11:G11。
(6) 掏出第6组文字(最后一组)
贮存格H11:=MID($C2,G7+1,99)
此中参数『99』,只是一个很年夜的数字,只要比贮存格内文字总数年夜便可。
2. 资料分解成果置于统一栏
计较分隔符号『/』的位置,贮存格D16:
{=SMALL(IF(MID($C$2,ROW($1:$30),1)="/",ROW($1:$30),999),ROW(1:1))}
这是阵列公式,输入完成要按 Ctrl+Shift+Enter 键,Excel 会主动插手「{}」。
複製贮存格D16,贴至贮存格D16:D20。
贮存格G16:=MID(C$2,1,D16-1)
贮存格G17:=MID(C$2,D16+1,D17-D16-1)
複製贮存格G17,贴至贮存格G17:G20。
贮存格G21:=MID(C$2,D20+1,99)
公式的道理和「1. 资料分解成果置于统一列」完全不异,此中 ROW(1:1)=1,当公式向右複製时,ROW(1:1)=1→ROW(2:2)=2→ ... →COLUMN(5:5)=5。 创作者先容 vincent
- 全站分类:不设分类
- 小我分类:课本资料
- 此分类上一篇: Excel-删去资料前几码(MID,RIGHT,LEN)
- 此分类下一篇: Excel-矩阵资料内容转换为一栏(OFFSET,MOD,INT,ROW)
- 上一篇: Pokémon GO的é若何输入?
- 下一篇: Excel-矩阵资料内容转换为一栏(OFFSET,MOD,INT,ROW)
汗青上的今天
- 2016: Pokémon GO的é若何输入?
- 2013: Excel-标示礼拜六日的年曆
- 2012: Excel-限制贮存格输入内容(设定格局化的前提)
- 2012: Excel-依月份计较总和(SUMPRODUCT)
- 2011: Excel-计较年休沐日数(VLOOKUP)
- 2011: Excel-用公式转换全型字和半型字北京拓展公司(ASC和BIG5)
- 2011: Excel-转换日期和时候的格局