Excel财务处理中用到的函数的使用方法及实例

Excel财务处理中用到的函数的使用方法及实例

1替换掉单元格内空格

1)保留两个符号中间的一个，去掉前后的所有空格。

因为在trim公式中，两个字符之间有一个单元格的ASCII码值为160，而其他普通空格是32。

=trim()。

2)替换掉单元格内所有空格

需使用substitute函数，先替换出里面的保留空格。

char(32)普通空格

char(160)特殊空格

CLEAN 函数被设计为删除文本中7位ASCII码的前32个非打印字符（值为 0 到 31）。在Unicode字符集（Unicode：Unicode Consortium 开发的一种字符编码标准。该标准采用多（于一）个字节代表每一字符，实现了使用单个字符集代表世界上几乎所有书面语言。）中，有附加的非打印字符（值为 127、129、141、143、144 和 157）。CLEAN函数自身不删除这些附加的非打印字符。

clean可以清除0到31的非打印符号，所以可用以下公式替换单元格中所有的空格变成非打印符号以清除空格。其中的7可以换成0到31中的任意数。

=clean(substitute(a1,char(32),char(7)))。

注：MID函数可以替换具体字符。调出函数，按步骤走，很容易设置。

2将“001”“001-107”数字前后都加上2，变成“003-109”

=IF(ISNUMBER(A1),A1+2,TEXT(LEFT(A1,3)+2,"000")&"-"&TEXT(RIGHT(A1,3 )+2,"000"))

如果有8位数字，则需要进行为数判断，使用find()函数

3excel 转置自动填充的问题

1)A列为a、b、c，b列为1、2、3，c列为11、22、33，d列为111、222、333，e列为

aa、bb、cc

2)A列为a、b、c、d、e、f、g、h、i，b列为1、2、3、4、5、6、7、8、9

4excel计算分别满足多个条件的单元格数

例一：

有四列数据，如A B C D，A与B是对应的，C与D是对应的

要求当C=A时，D=B列中“X”的个数

问题补充：B列有“X”，有的没有，只用统计"X"的个数！不是显示“X”

比如

A B C D

1 X 1

2 X 2

3 3

1 4

2 X 5

1 X

2 X

3 X

现在要D中显示“1”对应的“X”有多少个

方法一: D1 =COUNTIFS(B:B,"=X",A:A,C1)此公式的缺点是只能用在office2007以上的版本。

方法二:D1=SUMPRODUCT(($A$1:$A$100=C1)*($B$1:$B$100="X"))

5Sumif

Sumif是对符合条件地单元格求和，第一个参数为需要符合条件的单元格区域范围，第二个参数是条件，第三个参数是实际需要计算的单元格区域，按与第一参数相等的区域进行

计算。

这里需要注意的是，参数一满足条件后会返回区域里面单元格所在的行数，求和时，按照参数三里面区域对应行数进行。

如参数一区域选的A3:A10，参数三是B4到B11，假如a4，a5符合条件，那么将返回的行数是2、3，而不是4、5，然后，将对b5、b6进行求和，而不是b4、b5。

=SUMIF(B:B,"条件",A:A)

本公式的意义是，当b列中有单元格等于“条件”两个字时，计算对应a列单元格的和。其中条件参数可以使用通配符，*，？，实现模糊查找。

6按条件a列要求在b列中对应的单元格填充自然数,中间会出现空单元格

1)Max函数，在b列当前单元格上面已经得出的数据里面选取最大值加一。本方法适用于按递增或递减填充。

2)row函数，从1开始填充的话，适用row(1:1)，此法适用于连续不间断填充，即中间不会出现空单元格，否则需要引入条件判断。

7多重条件求和

在excel表中，如果A列是日期，B列名称，C列是数量

要求出，A列符合某个日期以前的以及B列符合某个名称的C的数量总和

1)如果是2007版，应用sumifs很好解决。第一个参数是计算范围，第二个参数是条件1范围，第三个是条件1，第四个是条件2范围，第五个是条件2，依次类推。

=SUMIFS(C:C,A:A,"日期",B:B,"名称")

2)如果2003，公式如下：

=SUM(IF(((A:A="日期")*(B:B="名称"))=1,C:C,""))

数组公式

3)Sumif公式不支持数组操作，所以没办法进行多条件选择后求和。

经验之谈：2007版本SUMIFS更好用，使用方法更简单，但是只适合2007版本。

8删除重复数据——公式法

1) 很简单的数据在a列中，有重复项。在b列中将所有多余项变成空单元格。

其中，在具体例子中，可以将INDIRECT("a"&ROW())变成a2，a3等等实际的单元格，可以实现一样的功能，可以不必排序。

=IF(COUNTIF($a$1:INDIRECT("a"&ROW()),INDIRECT("a"&ROW()))>1,"",INDIRECT("a"&ROW())) 选出的通过筛选删除。

2) 在a列中，有重复项。在b列中剔除所有重复项，顺序列出所有不重复值，自动剔除空格项。

=INDEX(a:a,SMALL(IF(countif((A$1:A$200),(A$1:A$200))>1,65536,ROW(a$1:a$200)),ROW(1:1)))&””

数组公式

3) 在a列中，有重复项。在b列中剔除所有多余项，顺序列出所有不重复值，自动剔除空格项。

=INDEX(A:A,SMALL(IF(FREQUENCY(MATCH($A$1:$A$10,$A$1:$A$10,),MATCH($A$1:$A$10,$ A$1:$A$10,))>0,ROW($A$1:$A$10),65536),ROW(1:1)))&""

数组公式

4) 在a列中，有重复项，b、c有相对应的数据。在d、e、f列中，剔除不重复，列出所有重复的项，自动剔除空格项。

=INDEX(A:A,SMALL(IF(COUNTIF($A$1:$A$10,$A$1:$A$10)>1,ROW($1:$10),65536),ROW(1:1))) &""

数组公式，可以向右拖拉以便得到其他各列的相应数据。

5) A、b两列数据，相应数据有重复（即a1和b1对比，a2和b2对比），在c列列出b列中不重复的选项，自动剔除空格项。

这个问题如果不剔除空格，其实很简单，左右对比，if语句，如果相等即显示空格，如想剔除空格，则相对复杂，要用到small函数来从数组里取数。

=INDEX(b:b,SMALL(IF((A$1:A$200)<>(B$1:B$200),ROW(B$1:B$200),65536),ROW(1:1)))&""

6) 数据和要求都比较复杂。

有两种数据，一种是TSDG112A,TSDG112B,TSDG113C类型，要求去掉所有的最后一个字母，然后筛选重复数据，还有一种是TSMIN08类型，后面没有字母，要求保留整个数据。数据从a2开始，公式输入到b2，如下：

=IF(COUNTIF($A$2:A2,IF(CODE(RIGHT(A2,1))<58,A2,LEFT(A2,6)&"?"))>1,"",LEFT(A2,6))

其中利用code函数判断最后一位是不是字母，数字的代码是48-57，大写字母是65-90，小写字母是97-122。

利用countif函数可以使用通配符，模糊查找数据。

9在列表中查找值的方法有：

1)使用精确匹配在列表中垂直查找值

=INDEX(A2:B5,MATCH("梨",A2:A5,0),2)

此法可以在查找区域内任意范围内查找。

2)使用精确匹配在未知大小的列表中垂直查找值

=OFFSET(A1,MATCH("梨",A2:A5, 0),1)

此法可以在查找区域内任意范围内查找。

MATCH，返回在指定方式下与指定数值匹配的数组中元素的相应位置。如果需要找出匹配元素的位置而不是匹配元素本身，则应该使用MATCH 函数而不是LOOKUP 函数。

3)Lookup，从单行或单列区域查找指定值，返回指定一列或一行中的数据。如果是数组形式，在数组第一行或第一列中查找指定的值，并返回数组最后一行或最后一列内同一位置的值。

4)Vlookup，只能在区域内第一列查找，可以返回所有区域内的任意列的数据。如果查找值有两个以上，则返回最后一个数据。

5)Hlookup，只能在区域内第一行查找，可以返回所有区域内的任意行的数据。

6)使用vlookup函数如果想实现在选择区域的第二列查找第一列的值，可以利用if

或者choose函数实现转置，再查找。

=VLOOKUP(c3,IF({1,0},a2:a7,b2:b7),2,0)

=VLOOKUP(c3,choose({1,2},a2:a7,b2:b7),2,0)

10A、B两列数据中如果有a、b数据分别都相等时，合并c列数据

将下述公式输入到d1,不符合条件的会显示空白

=IF(OR(MATCH(A1,A$1:A1)<>MATCH(B1,B$1:B1),MATCH(A1,A$1:A1)=ROW()),"",I NDEX($C:$C,MATCH(A1,A$1:A1))&INDEX($C:$C,ROW()))

本公式只适合于有两个重复值

11将单元格日期自动转换成星期

=CHOOSE(WEEKDAY(A1,2),"星期一","星期二","星期三","星期四","星期五","星期六","星期日")