14四种excel提取数字的方法

14四种excel提取数字的方法

四种excel提取数字的方法

B2单元格输入下面的公式:

=MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),2*LEN(A2)-LENB(A2))

下拉复制公式,就可以实现excel提取数字。

excel提取数字的另外两种情况

第一,如果需要提取数字的源数据没有字母出现,只是汉字和数字,可以使用这个公式提取数字:

=MIDB(A2,SEARCHB("?",A2),2*LEN(A2)-LENB(A2))

第二,如果需要提取数字的源数据没有字母,并且数字不是0开始的,可以使用这两种方法实现。

1.使用excel数组公式提取数字:

=-LOOKUP(1,-MID(A2,MIN(FIND(ROW($1:$10)-1,A2&1/17)),ROW($1:$15)))

2.通过两端对齐(内容重排)实现excel提取数字

步骤:选择需要提取数字的单元格:A2:A5,复制到E1:E4区域。

将E列调整为只显示一个汉字的宽度,然后单击开始——编辑——填充——两端对齐。(20 03此功能成为内容重排,2010称为两端对齐)。

选择第一个数字开始到最后一个单元格,将文本型数值转换为数字。

单击E1单元格,数据——筛选,筛选大于0,这样就将数字筛选提取出来了。然后将提取出来的数字复制到目标单元格区域就完成了excel提取数字。

excel中取出字符串中的数字

excel中取出字符串中的数字 1.数据- "a12345y" 如何自动撷取其中的数字 2.数据- "12345" 如何自动只撷取其中间三个的数位(即234)放於其它格上 如果你的数据有固定的长度和格式,公式可以简单些,如,前后各有1位字母: A1="a12345y" B1=Mid(A1,2,5) 如果前后字母个数不固定: B1=MID(A1,MA TCH(0,0*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),),MATCH(0,0*MID(A1,ROW(IN DIRECT("1:"&LEN(A1))),1))-MATCH(0,0*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),)+1) 数组公式,按Ctrl+Shift+Enter三键结束。 1、截取字符串中的一部分,用函数MID()。 A1="a12345y" 公式最外层是Mid()函数,该函数的格式是: Mid(文本,开始位置,长度) 对应公式是: MID(A1,MATCH(0,0*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),),MATCH(0,0*MID(A1,ROW(INDIR ECT("1:"&LEN(A1))),1))-MATCH(0,0*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),)+1) 2、长度=末位置-首位置+1。 其中MATCH()公式有不同的两个: 文本A1中第1个数字所在的位置:MATCH(0,0*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),) 文本A1中最后1个数字所在的位置:MATCH(0,0*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)) 两个公式不同之处,在于最后一个逗号。公式什么意思先别管,你只要知道: 长度=最后1个数字的位置-第1个数字的位置+1 本例中:6-2+1=5,数字长度为5。 3、拆散字符串。 下面公式取出字符串中的每一粒字符: MID(A1,1,1)="a" MID(A1,2,1)="1" …… MID(A1,6,1)="5" MID(A1,7,1)="y" 下面公式返回的是拆散后的一串字符(数组): MID(A1,{1;2;3;4;5;6;7},1)={"a";"1";"2";"3";"4";"5";"y"} 其中{1;2;3;4;5;6;7}用ROW(INDIRECT("1:"&LEN(A1)))计算所得。 这是数组公式中的一个基本技巧,如果还不懂,请从固顶的帖子中找到并学习它。 在编辑栏中,请用鼠标选取MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1) 然后按F9可以看到上面的结果。 4、0乘以任何数都得0吗? 你一定不会怀疑。 还是在编辑栏,请继续用鼠标选择: 0*{"a";"1";"2";"3";"4";"5";"y"},或者选择: 0*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),按F9,结果显示为: {#VALUE!;0;0;0;0;0;#VALUE!}。真的不是“都得0”。 里面除了0,还有错误值。个数与原来数组元素的个数相同。

从多行多列区域中提取不重复值到一列

从多行多列区域中提取不重复值到一列 来源:excel格子社区 要将一个多行多列区域中不重复的数据提取到一列,即对于重复的数据只提取一次,最直接的做法是先将该区域的数据转到一列中,再利用Excel2007/2010中的删除重复项功能去掉重复值。另外,还可使用数据透视表、数组公式和VBA的方法来提取,本文以Excel 2010为例分别介绍如下, 假如数据在A1:C11区域,其中还包含一些空单元格,如图所示。 方法一:利用数据透视表 利于数据透视表的多重合并计算功能,将上述数据区域作为数据透视表的多重合并计算数据区域,

把“值”字段放到在数据透视表的行区域中,即可得到不重复值。步骤如下: 1.在区域左侧插入空白列。如果原数据区域不是从A列开始,则可跳过此步骤。在本例中,由于原数据区域包含工作表的第一列(A列),需先在该区域的左侧插入一个空白列,作为数据透视表的行字段。 2.选择数据区域中的某个单元格,按Alt+D,再按P键,打开“数据透视表和数据透视图向导”,选择“多重合并计算数据区域”项,单击“下一步”。

3.再次单击“下一步”,在弹出的对话框中选择并添加数据区域。在选择区域时注意要包含原数据区域的左侧列及标题列。本例为包含上述空白列的A1:D11。

单击“下一步”,在弹出的对话框中选择数据透视表存放的位置,单击“完成”按钮。本例将数据透视表放置在当前工作表F3单元格开始的区域中。 4.在数据透视表的字段列表中取消选择所有字段,然后再次勾选“值”字段,或将“值”字段拖入到“行”标签区域中,即可在F列得到原区域中的不重复值。

方法二:使用数组公式 假如数据在A1:C11区域,在E2单元格中输入数组公式: =INDIRECT(TEXT(MIN(IF(($A$2:$C$11<>"")* (COUNTIF($E$1:E1,$A$2:$C$11)=0),ROW($2:$11)*10 0+COLUMN($A:$C),7^8)),"R0C00"),)&"" 公式输入完毕,按Ctrl+Shift+Enter结束。然后拖动填充柄向下填充公式,直到出现空白为止。

提取excel中的一段数字、文字、符号方法

一、单元格A1中有如下内容:要提取出数字 经测试下列公式好用 提取Excel单元格中连续的数字的函数公式是: =LOOKUP(9E+307,--MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890)),ROW($1:$8))) 或者 =LOOKUP(9E+307,--MID(H2,MIN(FIND({0;1;2;3;4;5;6;7;8;9},H2&1234567890)),ROW(INDIRECT("1:"&LEN(H2))))) 解释一下这个公式 FIND函数——查询文本所在位置 FIND(find_text,within_text,[start_num]) FIND(需查找的文本,包含查找文本的单元格,开始查找单元格的字符位置(可选)) 此处FIND函数是搜索{1,2,3,4,5,6,7,8,9,0}数字在“A1&1234567890”中所在的位置; A1&1234567890的目的是在用FIND函数查询时,不出现错误值,使之后的MIN函数可以正常运行。 即FIND({1,2,3,4,5,6,7,8,9,0},“小王联系电话58670098负责财务1234567890”) 选取划黑后按F9,得出的位置为:{19,20,21,22,7,9,10,8,13,11} MIN函数——返回列表中的最小值 MIN(number1,number2,……) MIN(数字1,数字2,……)

将FIND所得结果{19,20,21,22,7,9,10,8,13,11}带入MIN函数,最小值所得为7,正是第一个数值出现的位置; 也正是之前FIND函数中使用A1&1234567890的原因。 MID函数——返回文本字符串从指定位置开始特定数目的字符,即提取某段字符。 MID(text,start_num,num_chars) MID(被提取的文本或单元格,开始提取的字符位置,提取的字符个数) 将上述MIN函数所得带入MID(A1,7,ROW($1:$8); ROW($1:$8)使用row函数不是用来计算行,而是借用其作为常量。当然1:8是可以修改的,只要大于数值个数就不影响结果。 --MID(……)中“--”的作用是将MID得出的结果变为数值,使其可以被LOOKUP函数查询到。 MID(……)选取划黑后按F9{"5";"58";"586";"5867";"58670";"586700";"5867009";"58670098"} --MID(……)选取划黑后按F9{5;58;586;5867;58670;586700;5867009;58670098} LOOKUP函数——从返回某个查询的值(数组形式) LOOKUP(lookup_value,array) LOOKUP(在数组中查找的值,数组范围区域)

Excel提取重复值、不重复值、全部值的函数

Excel提取重复值、不重复值、全部值的函数 1、从某行中提取 定义名称“数量1”:=COUNTA(Sheet2!$B$6:$IV$6) “区域1”:=OFFSET(Sheet2!$B$6,0,0,1,数量1) 除定义的名称外,以下(1)至(3)中的公式均为数组公式。 (1)提取有重复的数据 【思路】把区域中重复数据的第1个提取出来。 为避免拖动公式超出区域范围时出现的错误,前面加了一个IF函数判断是否超出区域范围。 {=IF(COLUMN(A1)>数量1,"",INDEX(6:6,SMALL(IF((COUNTIF(区域1,区域1)>1)*(MATCH(区域1,区域1,0)=COLUMN(区域1)-1),COLUMN(区域 1),256),COLUMN(A1))) & "")}

(2)提取没有重复的数据 {=IF(COLUMN(A1)>数量1,"",INDEX(6:6,SMALL(IF(COUNTIF(区域1,区域1)=1,COLUMN(区域1),256),COLUMN(A1))) & "")} (3)提取全部数据,重复的只提取一次 {=IF(COLUMN(A1)>数量1,"",INDEX(6:6,SMALL(IF(MATCH(区域1,区域1,0)=COLUMN(区域1)-1,COLUMN(区域1),256),COLUMN(A1))) & "")} 2、从某列中提取 定义名称“数量2”:=COUNTA(Sheet3!$B$7:$B$65536) “区域2”:=OFFSET(Sheet3!$B$7,0,0,数量2) (1)提取有重复的数据

{=IF(ROW(A1)>数量2,"",INDEX(B:B,SMALL(IF((COUNTIF(区域2,区域2)>1)*(MATCH(区域2,区域2,0)=ROW(区域2)-6),ROW(区域 2),65536),ROW(A1))) & "")} (2)提取没有重复的数据 {=IF(ROW(A1)>数量2,"",INDEX(B:B,SMALL(IF(COUNTIF(区域2,区域2)=1,ROW(区域2),65536),ROW(A1))) & "")} (3)提取全部数据,重复的只提取一次 {=IF(ROW(A1)>数量2,"",INDEX(B:B,SMALL(IF(MATCH(区域2,区域2,0)=ROW(区域2)-6,ROW(区域2),65536),ROW(A1))) & "")}

如何提取excel中的一段数字、文字、符号

一、单元格中有如下内容:要提取出数字 提取单元格中连续地数字地函数公式是: (((({}))($:$))) 或者 (((({}))((":"())))) 解释一下这个公式 函数——查询文本所在位置 (,,[]) (需查找地文本,包含查找文本地单元格,开始查找单元格地字符位置(可选)) 此处函数是搜索{}数字在“”中所在地位置; 地目地是在用函数查询时,不出现错误值,使之后地函数可以正常运行. 即({},“小王联系电话负责财务”) 选取划黑后按,得出地位置为:{} 函数——返回列表中地最小值 (,……) (数字,数字,……) 将所得结果{}带入函数,最小值所得为,正是第一个数值出现地位置; 也正是之前函数中使用地原因. 函数——返回文本字符串从指定位置开始特定数目地字符,即提取某段字符. () (被提取地文本或单元格,开始提取地字符位置,提取地字符个数) 将上述函数所得带入(($:$); ($:$)使用函数不是用来计算行,而是借用其作为常量.当然是可以修改地,只要大于数值个数就不影响结果. (……)中“”地作用是将得出地结果变为数值,使其可以被函数查询到.

(……)选取划黑后按{"";"";"";"";"";"";"";""} (……)选取划黑后按{} 函数——从返回某个查询地值(数组形式) () (在数组中查找地值,数组范围区域) 很重要地一点: 如果找不到地值,它会使用数组中小于或等于地最大值. (,……)其中指地是一个不会出现地超大值,后面有个, 又可以写成*^;那么此处搜索这个超大值,肯定无法找到,因此返回地内容将是小于或等于{}地最大值,即. 二、如果内容中除字汉子字母外只有连续地位数字 可以用一下地公式手机号都是开头地(函数从第一个字开始)假如数据在((""))或者 (("?")) 函数 实例:如果学习地革命,则公式“("地",)”返回,("地",)返回. 假设数据在列. 输入公式 ((),"")下拉复制公式 追问 我地数据在列应该在输入公式如下((),"")吗?如果是这样,没反应 回答 ((),"") 如何取出一段文字中地数字 这要分好几情况: .要取前几位,或者后几位,如果都是数值,那么可以用( )和( )函数. .要取地是中间地几位,那么可以用()函数.

如何使用电子表格excel中的提取数字常用计算公式

如何使用电子表格excel中的提取数字常用计算公式 一、年龄计算公式: =IF((YEAR(E2)=2008)*(MONTH(E2)>8),0,2008-YEAR(E2) IF(MONTH(E2)<=8,0,-1)) 其中e2为单元格,2008为当前年份 二、身份证号中提取出生年月= MID(J11, 7, 4) & "年" & MID(J11, 11, 2) & "月" & MID(J11, 13, 2)&"日" 三、班级平均分公式 =IF(COUNTIF($C$2:$C$24,J26)=0,"",SUMIF($C$2:$C$24,J26,$D$2)/COUNTIF($C$2:$C$24,J26)) 四、Excel表中身份证号码提取出生年月、性别、年龄的使用技巧 如何从Excel表中的身份证号码中提取出生年月、性别、年龄呢?看了本文就知道了。 方法一: 1.Excel表中用身份证号码中取其中的号码用:MID(文本,开始字符,所取字符数); 2.15位身份证号从第7位到第12位是出生年月日,年份用的是2位数。

18位身份证号从第7位到第14位是出生的年月日,年份用的是4位数。 从身份证号码中提取出表示出生年、月、日的数字,用文本函数MID()可以达到目的。MID()——从指定位置开始提取指定个数的字符(从左向右)。 对一个身份证号码是15位或是18位进行判断,用逻辑判断函数IF()和字符个数计算函数LEN()辅助使用可以完成。综合上述分析,可以通过下述操作,完成形如1978-12-24样式的出生年月日自动提取: 假如身份证号数据在A1单元格,在B1单元格中编辑公式 =IF(LEN(A1)=15,MID(A1,7,2)&"-"&MID(A1,9,2)&"-"&MID(A1,11,2),MID(A1,7,4)&"-"&MID(A1,11,2)&"-"& MID(A1,13,2)) 回车确认即可。 如果只要“年-月”格式,公式可以修改为 =IF(LEN(A1)=15,MID(A1,7,2)&"-"&MID(A1,9,2),MID(A1,7,4)&"-"&MID(A1,11,2)) 3.这是根据身份证号码(15位和18位通用)自动提取性别的自编公式,供需要的朋友参考: 说明:公式中的B2是身份证号

excel提取文本中数字公式

Excel提取文本中数字公式 第一种,如图,如果数字在中文的后面,那么使用公式:"=--RIGHT(A2,LEN(A2) *2-LENB(A2))"解释下:因为中文字符是占两个字节。而数字占一个字节。len 返回值是字符的个数。而lenb返回值是这个字符串占多少个字节。所以,这两个的差值就是一个有几个中文。LENB(A2)-LEN(A2) = 中文实际个数字符总数-中文个数=数字个数LEN(A2)-(LENB(A2)-LEN(A2) )=数字个数开括号数字个数=LEN(A2)*2-LENB(A2)。因为数字在右边,所以,利用RIGHT,获取从右边第一个开始到最后一个数字,就得到这个数值。 第二种:数字在中文的前面。实际上和第一种的方法是一样的,这儿只是取数字的时候,从左边开始。公式: "=--LEFT(A2,LEN(A2)*2-LENB(A2))" 第三种:混搭。这个其实就是加个判断,判断第一个字符是数字还是中文。如果是数字,那么就用上面的第二个公式,中文就用第一个公式。公式:“=IF(ISN UMBER(--LEFT(A2,1)),--RIGHT(A2,LEN(A2)*2-LENB(A2)),--LEFT(A2,LEN(A2)* 2-LENB(A2)))” ============================================= excel从特殊字符串提取指定数据

在日常工作中,常常会遇到以下的参数: 如何把直径单独提取出来,以便进行直径比较或按直径汇总。 步骤/方法 选定直径的提取单元格,输入如图所示的公式,回车即可。 下拉表格,将公式复制至其他表格,这样就完美的提取出规格数据中的直径数值,之后就可以进行数据分类和汇总了。

Excel使用筛选计算唯一值的个数

EXCEL中如何使用筛选计算唯一值的个数 可以使用“高级筛选”对话框从数据列中提取唯一值,并将其粘贴到新的位置。然后可以使用 ROWS 函数计算此新区域中项的个数。 1.选择单元格区域,或确保活动单元格在一个表中。 请确保单元格区域包含列标题。 2.在“数据”选项卡上的“排序和筛选”组中,单击“高级”。 显示“高级筛选”对话框。 3.单击“将筛选结果复制到其他位置”。 4.在“复制到”框中,输入单元格引用。 也可以单击“压缩对话框” 以临时隐藏对话框,在工作表上选择单元格以后,再按“展开对话框” 。

5.选中“选择不重复的记录”复选框,然后单击“确定”。 所选区域中的唯一值将复制到新位置,此位置以在“复制到”框中指定的单元格为开头。 6.在该区域最后一个单元格下面的空白单元格中,输入 ROWS 函数。使用刚才复制的唯一值区域(不包括列标题)作为参数。例如,如果唯一值的区域为 B2:B45,那么可以输入: =ROWS(B2:B45) 返回页首 使用函数计算唯一值的个数 可以使用 IF、SUM、FREQUENCY、MATCH 和 LEN 函数执行此任务: 使用 IF 函数为每个 True 条件指定值 1。 使用 SUM 函数求和。 使用 FREQUENCY 函数计算唯一值的个数。FREQUENCY 函数忽略文本和零值。当某个值第一次出现时,此函数返回该值的出现次数。对于该值在第一次出现后的每次出现,此函数返回零。 使用 MATCH 函数返回区域中文本值的位置。返回的此值随后被用作 FREQUENCY 函数的参数,以便计算相应的文本值。 使用 LEN 函数查找空白单元格。空白单元格长度为 0。 示例 如果将示例复制到一个空白工作表中,可能会更容易理解该示例。

EXCEL提取重复值不重复值全部值的函数

E X C E L提取重复值不 重复值全部值的函数 Document serial number【LGGKGB-LGG98YT-LGGT8CB-LGUT-

Excel提取重复值、不重复值、全部值的函数1、从某行中提取 定义名称“数量1”:=COUNTA(Sheet2!$B$6:$IV$6) “区域1”:=OFFSET(Sheet2!$B$6,0,0,1,数量1) 除定义的名称外,以下(1)至(3)中的公式均为数组公式。 (1)提取有重复的数据 【思路】把区域中重复数据的第1个提取出来。 为避免拖动公式超出区域范围时出现的错误,前面加了一个IF函数判断是否超出区域范围。 {=IF(COLUMN(A1)>数量1,"",INDEX(6:6,SMALL(IF((COUNTIF(区域1,区域 1)>1)*(MATCH(区域1,区域1,0)=COLUMN(区域1)-1),COLUMN(区域 1),256),COLUMN(A1))) & "")} (2)提取没有重复的数据 {=IF(COLUMN(A1)>数量1,"",INDEX(6:6,SMALL(IF(COUNTIF(区域1,区域 1)=1,COLUMN(区域1),256),COLUMN(A1))) & "")} (3)提取全部数据,重复的只提取一次 {=IF(COLUMN(A1)>数量1,"",INDEX(6:6,SMALL(IF(MATCH(区域1,区域 1,0)=COLUMN(区域1)-1,COLUMN(区域1),256),COLUMN(A1))) & "")} 2、从某列中提取 定义名称“数量2”:=COUNTA(Sheet3!$B$7:$B$65536) “区域2”:=OFFSET(Sheet3!$B$7,0,0,数量2)

查找excel中不重复数据

Sub GetDistinctData() '定义一个遍历范围cell Dim cell As Range, res '定义Dictonary对象dataCollection Set dataCollection = CreateObject("Scripting.Dictionary") '遍历A1到A500所有单元格 For Each cell In Range("A1:A500") '假如单元格值不为空且dataCollection中未收录,则添加 If cell <> "" And (Not dataCollection.Exists(cell.Value)) Then dataCollection.Addcell.Value, CStr(cell.Value) End If Next '从dataCollection中获取刚才过滤之后的数据 res = dataCollection.Items '把数据不重复的写入B列 For i = 1 To dataCollection.Count Cells(i, 2) = res(i - 1) Next i End Sub 不重复值的自定义函数方案 (转自网络) 提取不重复值的自定义函数方案, Function MergerRepeat(Index As Integer, ParamArrayarglist() As Variant) '--------------------------------------------- ’ ’ ’功能:获得指定单元格区域或数组中的不重复集合或值 ’参数说明: ’Index:整型,当值小于1时,函数返回一个集合; ’大于1且小于不重复项的时,返回一个不重复的值 ’大于不重复项时,返回空。 ’a rglist():可为单元格区域或数组常量。 '---------------------------------------------- Dim NotRepeat As Object, tStr As String Set NotRepeat = CreateObject("Scripting.Dictionary") For Each arg In arglist For Each rRan In arg If TypeName(rRan) = "Range" Then If rRan.Value<> "" Then NotRepeat(rRan.Value) = 0 Else NotRepeat(rRan) = 0

两张excel表格,都只有一列姓名,但不完全一样,怎么找出重复的部

竭诚为您提供优质文档/双击可除 两张excel表格,都只有一列姓名,但不完全一样,怎么找出重复的部 篇一:在excel表格里找出重复的名字 在excel表格里找出重复的名字 一、只要重复,都填充颜色 假设你输入的姓名在a列,选择a列,格式-条件格式,选择公式,输入公式:=countiF(a:a,a1)>1点开下面的“格式”的“图案”,设置一个填充颜色,(如果只是字体颜色的变化,就选择“格式”的“字体”)如图: 在a列里有 重复的姓名就自动的填充颜色了上面的设置是只要重复,都填充颜色效果: 二、如果重复,第一个数据不提示,第二个重复出现的才填充颜色 公式为=countiF(a$1:a1,a1)>1 效果: 三、“提取”所有出重复的姓名, 如图,b2的输入数组公式

=iF(iseRRoR(index($a$2:$a$15,small(iF(countiF($a$2: $a$15,$a$2:$a$15)>1,Row($a$2:$a$15)-1,4^8),Row(1:1) ))),"",index($a$2:$a$15,small(iF(countiF($a$2:$a$15 ,$a$2:$a$15)>1,Row($a$2:$a$15)-1,4^8),Row(1:1)))) 注:1、注意公式里的“$15”,此公式里的单元格引用区域请根据实际区域修改 2、公式输入后按ctRl+shiFt+enteR三键结束 向下复制公式,即可。 四、设数据 如图: 将公式=countiF(a:a,a2)复制后粘贴到“b2”单元硌,对准右下角,光标成“+”状时双击,如图: 筛选结果大于1的为重复,如图: 如果第二个才显示重复(二个只显示一个重复),则公式为:=countiF($a$1:a2,a2)如图: 篇二:excel两个表中人员姓名大致相同,如何查找姓名不同的记录 这里可用countiF()函数 第一步:把表一粘贴到一个新建表的sheet1中,在第一列的后面插入一空列,标题为“是否重复”,如图:第二步:把表二粘贴到sheet2,在第一列的后面插入一

用函数在Excel中从文本字符串提取数字

用函数在Excel中从文本字符串提取数字 Excel输入数据过程中,经常出现在单元格中输入这样的字符串:GH0012JI、ACVB908、华升12-58JK、五香12.56元、0001#、010258等。在进行数据处理时,又需要把其中的数字0012、908、12-58、12.56、0001提取出来。 如何通过使用Excel的工作表函数,提取出字符串中的数字? 一、问题分析 对于已经输入单元格中的字符串,每一个字符在字符串中都有自己固定的位置,这个固定位置都可以用序列数(1、2、3、……)来表示,用这些序列数可以构成一个可用的常数数组。 以字符串“五香12.56元”为例:序列数1、2、3、4、5、6、7、8分别对应着字符串“五香12.56元”中字符“五”、“香”、“1”、“2”、“.”、“5”、“6”、“元”。由序列数组成一个保存在内存中的新数组{1;2;3;4;5;6;7;8}(用列的形式保存),对应字符串中的字符构成的数组{“五”;“香”;“1”;“2”;“.”;“5”;“6”;“元”}。因此解决问题可以从数组着手思考。 二、思路框架 问题的关键是,如何用序列数重点描述出字符串中的数字部分的起始位置和终止位置,从而用MID函数从指定位置开始提取出指定个数的字符(数字)。 不难看出,两个保存在内存中的新数组: {“五”;“香”;“ 1”;“2”;“.”;“5”;“6”;“元”} {1;2;3;4;5;6;7;8} 数组具有相同大小的数据范围,而后一个数组中的每一个数值可以准确地描述出字符串中字符位置。 字符与序列数的对应关系如下表所示: 字符字符位置

五—— 1 香—— 2 1 —— 3 2 —— 4 . —— 5 5 —— 6 6 —— 7 元—— 8 所以解决问题的基本框架是: 用MID函数从字符串的第一个数字位置起提取到最后一个数字止的字符个数。即{=MID(字符串,第一个数字位置,最后一个字符位置-第一个字符位置+1}。其中“+1”是补上最后一个数字位置减去第一个数字位置而减少的一个数字位。 三、解决方案及步骤 假定字符串输入在A2单元格。 ⑴确定A2中字符串的长度。 即用LEN函数计算出A2中字符串中字符的个数,这个字符个数值就是字符串中最后一个字符在字符串中的位置:=LEN(A2)。 ⑵确认字符串中的每一个字符位置序列数组成的新数组。 用INDIRECT函数返回一个由文本字符串指定的引用:

Excel轻松提取网上数据

网上信息采集工作,最头疼的便是从网页上一次次很枯燥地进行数据表格的复制,而且在复制过来之后还要进行很多修改,不但麻烦而且也很浪费时间,工作效率大打折扣。这时我们不妨用功能强大的Excel来试着解决一下问题。 对于比较规范的表格数据,我们完全可以在表格页面上点击右键,选择"导出到Microsoft Office Excel"(图1),此时系统会自动打开Office Excel,进行数据加载。这个过程仅需要几秒钟就把数据加载进来(图2)。如果你认为数据比较适合你的编辑要求,那么就可以直接保存了。否则,也可以做一下适当的修改,因为在表格处理方面,Excel要大大优于word。 图1 在网上表格或数据采集这一点上,Excel往往是较为智能化的,它在进行数据采集与加载时,只加载表格固定区域内的数据,而不是把整个网页都加载进来。这一点我试过很多次,都是很听话的,请看图片2的效果。

图2 当然,在网页中也有一些不太规范的数据与表格,这样的数据,Excel处理起来,稍稍有一点难度,不过只要熟悉Excel 的操作功能的话,还是可以轻松搞定的。先看一下这个页面(图3), 图3 图3这种不规范的页面数据,如果让Excel处理起来就会出现这样的结果(图4),看着是不是感觉很乱,所有的东西都错位了,一般的人会感到速手无策的。主要原因就是多出了文件数据的开头与结尾。

图4 不过,只要我们删除文件上下两头的不规范的区域,剩下的这些数据不就变得好处理了吗?这时,我们再执行一下菜单:"数据――分列――下一步",这样就可以把不规范的数据变得规范了。这是处理之后的结果(图5) 图5 对于那些本来就比较规范的表格,就简单得多了,只要执行"导出加载――略修――保存"就可以了。

EXCEL单元格中的唯一数字如何提取出来

单元格中的唯一数字如何提取出来? 一个数值和文字混排的单元格,如何计提数值参与计算?发哥gvntw给出了一个巧妙的公式: =LOOKUP(9E+307,--MID(A1,MIN(FIND({0;1;2;3;4;5;6;7;8;9 },A1&1234567890)),ROW(INDIRECT("1:"&LEN(A1))))) 1、FIND()告诉我,0-9在文本中第一次出现的位置。 用Find()在指定的文本中查找0-9,会返回第一次出现的位置,如果没有会返回一个错误值,聪明的gvntw,在文本后面连接上1234567890,让Find()不再有借口返回错误值,请放心,它不会影响后面的结果。在编辑栏中,像下面一样,用鼠标将这一段文本抹黑,然后按F9。 =LOOKUP(9E+307,--MID(A1,MIN(FIND({0;1;2;3;4;5;6;7;8;9 },A1&1234567890)),ROW(INDIRECT("1:"&LEN(A1))))) 2、对着Min(),王小丫说:恭喜你,答对了,第一个数字在第3位。 如果你还怀疑,可以这再次抹黑,并按F9。 =LOOKUP(9E+307,--MID(A1,MIN({7;8;5;12;13;3;4;16;17;18 }),ROW(INDIRECT("1:"&LEN(A1))))) 3、神奇魔术师Mid() ,一个文本变出许许多多。 让事实说话,继续抹抹黑,按F9。开始位置不变,长度依次

增1,形成一个数组,数组的个数等于文本长度(Row()的结果)。后面的会出现相同的情况,没关系,还是不影响结果的。=LOOKUP(9E+307,--MID(A1,3,ROW(INDIRECT("1:"&LEN( A1))))) 4、如果忽略了--,还是功亏一篑。 看看,它的结果会是什么呢? =LOOKUP(9E+307,--{"5";"56";"562";"562.";"562.0";"562.00"; "562.00元";"562.00元";"562.00元"}) 5、Lookup() 总是那么沉着,他闭着眼睛说,我就要最后面那个数字。 =LOOKUP(9E+307,{5;56;562;562;562;562.00;#V ALUE!;#V A LUE!;#V ALUE!}) 9E+307 是一个非常大的数,在任何时候都适用,如果你知道你的文本中最大数的范围,比如1000,文本中的数字不会超过1000的,那么,这个数字你用1000,也是行的,即:Lookup(1000,……) 9E+307是什么概念? 9E+307是科学记数,用普通记数法写是9后面有307个0, 9E+307就是9*10^307 在构思解题思路时,就有,把那串文本从第一个数字开始,

Excel提取重复值、不重复值、全部值的函数.doc

4 5 6 7 8 9 V 全部数据, 源数据 1、 2 O 3S 重复的只提取一次 丑庚 p A I ■- 寅 辛 壬 我 丙子戊丑 丑丨寅丨丨 函数判断是否超 Excel 提取重复值、不重复值、全部值的函 数 1、从某行中提取 B8 ▼ 严[二IF(COL 呷N(*)>数革ir ;IHpEX(6:6 SMA^L(I£((C A | B | C 丨 D 丨 E I F | G | H | I | J I K 「L 丨 M I N 丨 0 [厂 1斗利用函数,分三种情况提取这一行的: F1.有重复的数I I I I I 3 2、没有重复的数振 定义名称“数量 T : =COUNTA(Sheet2!$B$6:$IV$6) “区域 V : =OFFSET(Sheet2!$B$6,0,0,1,数量 1) 除定义的名称外,以下(1)至(3)中的公式均为数组公式。 (1) 提取自重复的数据 【思路】把区域屮重复数据的第1个提取出来。 为避免拖动公式超出区域范围吋出现的错误,前面加了一个IF 出区域范围。 {=IF(C0LUMN(A1)> 数量 1,,,M ,INDEX(6:6,SMALL(IF((COUNTIF(区域 1,区域 1)>1)*(MATCH(区域 1,区域 1,0)=COLUMN(区域 1)-1),C0LUMN(区域 庚辛壬乙我 子

1),256),COLUMN(A1))) & :}

3 1 6 8 12 3 4 2 2 甲 乙 丙 丁 戊 己 庚 辛 壬 乙 我 丙 子 戊 丑 庚 寅 辛 丙戊 庚辛 甲丁 己壬 我手 丑 寅 甲乙 丙丁 戊己 庚 辛 壬我 子 (2)提取没有重复的数据{=IF(C0LUMN(A1 )> 数量 1 ,,n,,INDEX(6:6,SMALL(IF(COUNTIF(区域1,区域1)=1,C0LUMN(区域 1 ),256),C0LUMN(A1))) & ”“)} (3)提取全部数据,重复的只提取一次 {=IF(C0LUMN(A1)> 数量1,,,M,INDEX(6:6,SMALL(IF(MATCH(区域1,区域1,0)=COLUMN(区域1 )-1 ,COLUMN(区域1 ),256),C0LUMN(A1))) & ””)} 2、从某列中提取 D7▼朮=IF(ROW(A1)> 数量乙INDEX(B:B, SMALL (IF ((COW A B c D E F G H 1利用函分三种情况提取这一列的; 2K有重复的数据 3 2.没有重复的数据 4 3.全部数据,重复的只提取一次 5 6源数据结果123 定义名称“数量2?,: =COUNTA(SheeU3!$B$7:$B$65536) “区域2”: =OFFSET(SheeU3!$B$7,0,0,数量2) (1)提取有重复的数据 {=IF(R0W(A1 )>数量2,:INDEX(B:B,SMALL(IF((C0UNTIF(区域2,区域

excel中一个单元格中有文字字母字符和数字如何能把数字单独提取出来提取数字

excel中一个单元格中有文字字母字符和数字如何能把数字单独提取出来?2011-04-13 11:25匿名?|?分类:办公软件?|?浏览1375次 如A1中A本月电费收入(美兰供电所)(对帐标志: 想在B中表示为 最好能教一个方法十分感谢 我有更好的答案 按默认排序|按时间排序 2条回答 检举|2011-04-13 11:32Mrpine|十五级 在EXECL理,按ALT+F11,插入-模块,复制下列语句 Function SplitNumEng(str As String, sty As Byte) Dim StrA As String Dim StrB As String Dim StrC As String Dim i As Integer Dim SigS As String For i = 1 To Len(str) SigS = Mid(str, i, 1) If SigS Like "[a-zA-Z]" Then StrA = StrA & SigS ElseIf SigS Like "#" Then StrB = StrB & SigS Else StrC = StrC & SigS End If Next i Select Case sty

Case 1 SplitNumEng = StrA Case 2 SplitNumEng = StrB Case Else SplitNumEng = StrC End Select End Function 比如你的数据在A1 BI输入=SplitNumEng(A1,1) 表示提取字母 =SplitNumEng(A1,2) 表示提取数字 =SplitNumEng(A1,3)表示提取中文 追问 这个方法网上看过几次,但是个人水平也有限还是不太会用能否详 细解释下和给出操作过程 回答 打开你的EXECL,按ALT+F11,点上面菜单里的插入-模块,复制 下列语句后,关闭模块,返回到你的EXECL里,比如你的数据在A 1 在BI输入=SplitNumEng(A1,1) 回车表示提取字母 =SplitNumEng(A1,2) 表示提取数字 =SplitNumEng(A1,3) 表示提取中文

excel2019表格数据透视表如何筛选重复

excel2019表格数据透视表如何筛选重复 篇一:EXCEL表格怎样把重复的名称和数据找出来 EXCEL表格怎样把重复的名称和数据找出来? 用了几个函数,不是重复的它显示是重复的,(显示重复的是数字相差10)盘头十字4*8 与盘头十字4*18它说重复,第一个不重复,第二个是重复了,这是什么原因?多谢高手 =IF(COUNTIF($A$1:A1,A1)>1,"重复","") 用单元格 =COUNTIF($A$1:A1,A1)>1 条件格式 不知道是不是要这样的,有问题在hi我。 追问 首先谢谢你,出现这样的情况,可能是数字原因,有办法不? 盘头十字4*18 盘头十字4*8 重复 盘头十字4*25 盘头十字4*5 重复 沉头十字3*14 沉头十字3*4 重复 因为你的*被excel当通配符处理了。所以你得先所里面的*给换掉。可以用excel的查找替换处理,也可以用公式 =IF(COUNTIF($A$1:A1,SUBSTITUTE(A1,"*",))>1,"重复","")

得说一下,你里面每个数据里都有*吗? 没有的话,就把上面公式里substitute的第三参数改一下,最好能改成数据里没有的。现在的公式是把所有的*给处删掉处理。但如果你的数据里有的没有*的话就会出错,比如“盘头4*18”和“盘头418”就一样了。 不是我想出来了,是经老师指点才知道 用的2019吧,用2019直接就有一个检查重复的工具,不用你去写啥函数.如图,选择重复值 我想把相同数据找出来然后都删掉,只保留不重复的数据应该怎么做 举个例子,比如 1 2 2 3 3 3 4 4 5 最后只保留 1

excel中一个单元格中有文字字母字符和数字如何能把数字单独提取出来提取数字

e x c e l中一个单元格中有文字字母字符和数字如何能把数字单独提取出来提取数字 公司内部编号:(GOOD-TMMT-MMUT-UUPTY-UUYY-DTTI-

excel中一个单元格中有文字字母字符和数字如何能把数字单独提取出来?2011-04-13 11:25匿名?|?分类:?|?浏览1375次 如A1中 A本月电费收入(美兰供电所)(对帐标志: 想在B中表示为 最好能教一个方法十分感谢 我有更好的答案 按默认排序| 2条回答 |2011-04-13 11:32|十五级 在EXECL理,按ALT+F11,插入-模块,复制下列语句 Function SplitNumEng(str As String, sty As Byte) Dim StrA As String Dim StrB As String Dim StrC As String Dim i As Integer Dim SigS As String For i = 1 To Len(str) SigS = Mid(str, i, 1)

If SigS Like "[a-zA-Z]" Then StrA = StrA & SigS ElseIf SigS Like "#" Then StrB = StrB & SigS Else StrC = StrC & SigS End If Next i Select Case sty Case 1 SplitNumEng = StrA Case 2 SplitNumEng = StrB Case Else SplitNumEng = StrC End Select End Function 比如你的数据在A1

Excel中最值得收藏的12个函数公式(精选)

Excel中最值得收藏的12个函数公式(精选) 文/赵志东(来自excel精英培训微信平台)兰色今天精选出12个excel函数公式,虽然它们不并常用,但需要用时你还真不好搜到,所以赶紧收藏起来吧。 1、动态获取A列最后一个数字 =LOOKUP(1,0/(A2:A100>0),A2:A100) 2、不重复个数公式 =SUMPRODUCT(1/COUNTIF(A2:A7,A2:A7)) 3、提取唯一值公式 =IFERROR(INDEX(A:A,SMALL(IF(MATCH(A$2:A$7,A$2: A$7,0)=ROW(A$1:A$6),ROW(A$2:A$7)),ROW(A1))),'') 4、中国式排名公式 =SUMPRODUCT(($D$4:$D$9>=D4)*(1/COUNTIF(D$4:D $9,D$4:D$9))) 5、提取字符串任一位中的数字A1的值为ABC123.45FE ,截取结果:123.45数组公式: {=LOOKUP(9^9,MID(A1,MATCH(1,MID(A1,ROW(1:9),1)^ 0,0),ROW(1:9))*1)} 6、金额大写转换公式 =TEXT(LEFT(RMB(A2),LEN(RMB(A2))-3),'[>0][dbnum2]G /通用格式元;[

7、个人所得税计算公式假如A2中是应税工资,则计算个税公式为: =5*MAX(A2*{0.6,2,4,5,6,7,9}%-{21,91,251,376,761,1346,3 016},) 8、一对多查找包含公式=COUNT(FIND({'AAA花苑','CCC 龙廷'},A2)) 9、Vlookup多表查找公式 工资表模板中,每个部门一个表。 在查询表中,要求根据提供的姓名,从销售~综合5个工作表中查询该员工的基本工资。方法1: =IFERROR(VLOOKUP(A2,服 务!A:G,7,0),IFERROR(VLOOKUP(A2,人 事!A:G,7,0),IFERROR(VLOOKUP(A2,综 合!A:G,7,0),IFERROR(VLOOKUP(A2,财 务!A:G,7,0),IFERROR(VLOOKUP(A2,销售!A:G,7,0),'无此人信息')))))方法 2=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDI RECT({'销售';'服务';'人事';'综合';'财务'}&'!a:a'),A2),{'销售';'服务';'人事';'综合';'财务'})&'!a:g'),7,0)10、Sumif多表求和公式工作表名称常常是不规则的,如下图所示 分析:首先我们需要用宏表函数取得表名公式- 名称管理器- 新建名称- 在新建名称中输入名称“sh”,然后“引用位置”

相关文档
最新文档