用excel分析数据时可采用的公式技巧

1、1. 两列相同数据标记颜色用条件格式，假设数据在a1： b100中，选中a1： a100,格式，条件格式，公式，框中 输入=countif(b$1:b$10000,a1)>0下面挑选一种条件成立之后返回的颜色，确定。1.1两列中相同的数据筛选到第三列excel表中有a,b两列数据，找出a,b列屮有重复的数据，并输入到c列公式 “ =vlookup($a$l:$a$10000,$b$ 1:$b$ 10000,1,0)”1.2 一列数据中找出相同的数(不同的数)并标记颜色例如，excel表格中有如下内容a12353417选定a列中的数据，格式条件格式选择公式输入=countif(a$ 1:a

2、$8,a1 )> 1 (不同数vl) 一格式图案一一选择“红色”，重复项将显示红色。此方法可以找出一列屮独一无二的数。1.3两列数据中相同数据自动对齐假设 a 列数据区域是 al： a1000,在 c1 输入公式：=if(countif(b$ 1:b$ 1000,a1a1),然后把公式下拉复制到c1000不同数据自动对齐=if(if(countif(c$2:c$ 10000,a2)>0,”,a2)=0, ”，if(countif(c$2:c$ 10000,a2)>0,“”,a2)1.3.2 b、d两列各有不同数据，将b列屮没有出现的d列数据统计到f列(数据从b2、d2 开始)

3、在f1处输入公式=index(b:b,small(if(countif($d$ 1:$ds 1000,$b$ 1:$bs 1000)=0row($a$ 1:$a$ 1000),4 人 8)r ow(1:1)相同算法=index($a$2:$a$1000,small(if(countif($b$2:$b$500,$a$2:$a$1000)>0,4a8,row(sa $2:$a$1000)-l) row()1)1.3.1多列数据屮找相同数据，不同数据显示为空比如abcd1122324723837634变为abcd2 23在el处输入：“ =if(and(countif(b$ 1:b1000,

4、a1 ),countif(c$ 1:c1000,a1 ),countif(ds 1:ds1000, a1 ),ai,y，再填充即可得后面的值。2. 四舍六入五不变比如a1为124. 3为124a2 126.8 为 127a3 122.5 不变公式 “=if(right(a1,1 )=h5n,a 1 ,round(a1,0)”四舍五入(整数0,小数点1位1)：公式“=round(a1,0)”3. 筛选岀带小数或整数的数值某列有n条记录，其中有整数和小数，如何把带小数的数据筛选出来？公式 “=if(mod(b2,1),b2,”)” 或者 “=if(int(b2)=b2,”,b2)”筛选整数数值:公

5、式“=if(int(b1)=b1,b1,”)”4. 选择同列或行相同颜色的值使用快捷键ctrl+f,出现查找对话框，点击格式按钮，选择字体对话框，选择颜色，选出 你需要筛选的字体颜色，然后点击确定即可。点击“查找全部”，按钮，在查找结果的窗口 中按ctrl+a按钮，excel表中相应的项会变成选中状况，然后再复制即可。5. 查找两列中的的相同数据(该数据不一定同行)并得出数据的位置例如，excel表格中有以下内容abc13(无)24(无)35(1)46(2)在c列中显示与a列相同数字在b列的位置公式 “=if(countif(b:b,a1),match(a1,b:b),” 无”)”6. 删除a

6、列中相同的数，b列保持不变=if(countif($ a$ 1: a1, a1 )> 1，”“,offset© as 1 ,r0w()-1 ,)7. a列中去除相同数值，且相同数值后面紧跟的单元格转换到同一行例如：ab13431536472146变为abcd13 52 13 64 3 7 6方法1：在c2处输入=if(if(and(countif($c$ 1:c1 ,$a$ 1 :$a$2000),“”,index(a: a,m atch(,countif($c$ 1:c l,$a$2:$a$2000),)+l)=0,“”,if(and(countif($c$ 1:cl ,$a

7、$ 1 :$a$2000),”,index(a: a,match(,countif($c$ 1:c1,$ a$2:$a$2000),)+l),按ctrl+shift+enter键(数组公式的输入方法)结朿，再填充即可得a列唯一值；再在d2处输入=if(index($b:$b,small(if($c2=$a$ 1:$a$2000,row($ 1:$2000),300),column(a:a)=0, index($b:$b,small(if($c2=$a$1 :$a$2000,row($1 :$2000),300),column(a:a) 按ctrl+shift+enter键结束，再填充即可得后面

8、的值。方法2：在c2处输入=if(index(a:a,match(,countif(c$ 1:c 1 ,a$2:a$2000),)+1 )=0,”,index( a: a,match(,countif(c$ 1:c 1, a$2: a$2000),)+1)按ctrl+shift+enter键(数组公式的输入方法)结束，再填充即可得a列唯一值；再在d2处输入=if(index($b:$b,small($a$l:$a2000o-$c2)/l%+r0w(sl:2000),c0lumn(a2)=0, “”，index($b:$b,small($a$1 :$a2000o$c2)/l%+row($ 1:2

9、000),column(a2)=if(iserr(if(index($b:$b,small($a$ 1 :$a2000<>-$c2)/1%+row($ 1:2000),colum n(a2)<>0,index($b:$b,small($a$l:sa2000o-$c2)/l%+r0w($l:2000),c0lumn( a2)if(index($b:$b,small($a$1:$a2000o-$c2)/1%+r0w($1:2000)9c0lumn(a2)<>0, index($b:$b,small($a$ 1:$a2000o-$c2)/l%+r0w($ 1:20

10、00),column(a2)，”)按ctrl+shift+enter键结束，再填充即可得后面的值。&当c列=人列的其中一个数值时，d列自动等于a列所对应的b列的值(没有时为空) =if(isna(lookup( 1,0/($as 1:sa$1000=c 1 ),$b$ 1:$b$ 1000),m,',lookup(1,0/($ a$ 1:$a$100 0=cl),sb$l:$b$1000)9. 多行变为一列将要转变的所有单元格复制粘贴到word中，在word中合并单元格，这样所有的数据 就进入了一个单元格中，把word中的数据复制粘贴到excel中。9.1多列变为一列=if(r

11、ow()>counta($a$ 1:$1$ 10),“”,index($a$ 1:$i$10,mod(small(if($a$ 1:$is 10<>n,', row($ a$ 1:$i$10)+column($as 1 :$i$ 10)* 100000),row(), 100000),int(small(if($a$ 1 :$ 1$ 10<>”“,ro w($a$ 1:$i$10)+column($a$ l:$i$10)*l 00000),row()/100000)a:c列转换到a列=index($a$ 1 :$c$3,int(row(a 1 )-1 )/

12、3)+1 ,mod(row(a 1)-1,3)+1)10. 某列非空数值加减一个值=if(aa1+1，”)10在前一格数值基础上随机加减4假设 al 为“前一格”，在 bl 输入公式=if(rand()>o.5,a1+int(rand()*5), a1-int(rand()*5)注：公式里的int是为了把用roan得到的随机数収整，如果不需要整数(保留小数)，可 以改公式为=if(rand()>o.5,a1+irand()*4,a1rand()*4)11. 选择性加值例如有这么一个数值8765467293597想实现十位数如果是9,则返冋空，如果不是9,则数 值加10公式“ =if

13、(-(mid(a 1 ,len(a 1 )-1,1 )=9,”, a1 + 10)” 或"=if(mid(a 1 ,len(a 1 )-1,1 )=”9“,”,a 1 + 10)”12. 使计算结果为0的单元格b2变为空格，同时这个空格还可以被其他的函数继续引用 公式：=if (b2=0, ”,b2)13. 两列排到一列公式：=if(indirect(,a,'&row(a 1 )-(row(a 1)-1 )/2)=0,”“,indirect a”&row(a 1)-(row(a 1 )-1 )/2)=if(indirect(” b”&row( a1 )

14、-(row(a 1)-1 )/2)=0,”,indirect(“b”&row(a 1)-(row(a l)-l)/2)14. 整列自动复制到指定列如a列值复制到b列公式：=if(b1=”,”“,b1)ctrl+shift+enter 键结束,再填充即可。15. 从一列中提取非空单元格值 =if(iserror(index($d$2:$d$2000,small(if($ds2:$d$2000=,n7h,row($d$2:sd$2000) ro w($d$2)+1 ),ro w($d2)ro w($d$2)+1),index($d$2:$d$2000,small(if($d$2:$d$20

15、00=”t”,row(sd$2:$d$2000)row($d$2)+1 ),row($d2)row($d$2)+1)相同公式：=if(index(a:a,small(if($a$ 1:$a$10000=,m,4a8,row($ 1 :$ 10000),row(1:1 )=0,”,(in dex( a: a,sm all(if($a$ 1 :$a$ 10000=,h',4a8,row($ 1:$10000),row(1:1)16. 两列数据按顺序排列并删除重复项只留一个从 c2 输入公式：=if(max(c$1:c1)=max(a:b)；m,small(a:b,countif(a:b,&

16、lt;=,&c1)+1)17. 统计多列中有相同数字的个数假设数据在a1:d4=sumproduct(countif(offset(a 1, 0; 1 ;2;3, 1,2,3,4), a1 :d4)-1)18. excel统计非零非空非字符个数，也就是只统计数值个数但除零外 =sum(a1:a100)/countif(a1:a100,”>0”) 统计 负的呀.用这 个试. =sum(a 1:a100)/(countif(a1:a100,h>0m) +countif(a 1:a100,n<0n)补充 2:从里往外分 析.(al:al00>0)表示 al:a100 中>0,如果大于 0 传回 true,否则 false isnumber(al:a100) 表示al:a100中是数字传回true,否则false在excel里true相当于1 false相当于0.