EXCEL在审计工作中的应用

最新推荐文章于 2024-07-11 15:59:41 发布

翠冠梨

最新推荐文章于 2024-07-11 15:59:41 发布

阅读量977

点赞数 18

文章标签： excel

本文链接：https://blog.csdn.net/weixin_43562968/article/details/135645588

版权

学习目标：

掌握Excel在审计工作中的使用技巧

学习内容：

课程：深注协会计师视频课程——审计中软件的使用技巧
EXCEL基础、技巧及在审计工作中的应用（40小节）
用友CPAS审计信息系统了解并实操（23小节）

第一章 EXCEL部分——基本操作

1、选择性粘贴

行列转换：选择性粘贴—转置
给二月份数据均增加8000元：空白格打8000，复制，粘贴到二月份的数值中，选择性粘贴——选择数值——运算：加。其他运算同
单位从元变成万元：选择性粘贴——除以10000
数字后添加文字单位，万元，
方式一：
另起一列，等号+数值单元格C3+使用文本连接符&“万元”
方式二：
占位符：0.0,“万元”

2、格式刷

小技巧：双击格式刷图标可以所以拖动格式刷，不用反复点

3、查找与选择

日期有断层，无法按照日期筛选分录

首先定位空值：选中列，ctrl+G——定位条件——空值
然后填充：=上一个单元格的值，Ctrl+Enter，全部空值即可被填充

4、分列方法

日期分列：年-月-日
固定宽度分列：先插入适当的列(重复插入列按Fn+F4），数据—分列—固定宽度分列—删除不需要的：不导入此列—格式改成常规
不规则：分隔符分列
函数：month(日期），day（日期）
日期20230907，中间没有符号
函数：left(A3,4),middle,right
字符和字节：数字汉字分分列
一个汉字=两个字节，1个字符
一个数字=一个字节，1个字符
leftB（格，2），返回左边的两个字节，可能只有一个汉字
len（2011年）=5，lenB(2011年） =6，返回字节，len(20090821)=8
利用len和lenB分列：

LENB(A3）-LEN(A3)=汉字个数
RIGHT（A3，LENB(A3）-LEN(A3)）=文字部分
科目代码=字符数-汉字字符数=len(A3)-(lenB(A3)-len(A3))=2LEN(A3)-LENB(A3)
LEFT(A3,2LEN(A3)-LENB(A3))=数字部分
LEFT(B3,LEN(B3)+LEN(B3)-LENB(B3))

一秒钟数字文字分列：快捷键Crtl+E
查找与替换：*1：数字1之前的全部替换消失掉，1是大部分科目代码的最后一位数，多替换几次即可完成

5、数字过长被科学计数

点击“，”逗号，变成千分符的形式
设置单元格格式——数字——自定义——0
EXCEL有效统计15位数字，超过15位后面为0，除非文本格式

6、Ctrl快捷键

ctrl1：设置单元格格式
快速输入：选中区域——输入——crtl+Enter
ctrlD：复制上一个单元格的内容
ctrlF：查找：按格式查找——从单元格选择格式
ctrlH：替换
ctrlG：定位条件——空值、对象
ctrlZ：撤退
ctrlX：剪切
ctrlB：加粗
ctrlN：新建
ctrlU：下划线
ctrlI：斜体
ctrl+；：当天日期

7、条件格式

场景：多头(双边)挂账：应收账款挂A企业500万,预收账款(没有及时核销)A企业100万,资产负债表两边全部需要调减100万，应收预收的核减，需要先筛选出多头挂账的公司（重复值）
复制粘贴到一张表上（ctrl+shift+方向箭头、选中第一行，拖到最后一行，按住shift可全部选中）——开始——条件格式——突出显示重复值

8、删除重复值（3操作）

仅删除重复行：数据——数据工具——删除重复值——扩展区域——以”某一列“为基准
删除并合并重复行：
插入数据透视表——值——值字段设置——计数（每一个企业出现过几次）
值字段设置——求和（自动将重复出现的企业求和）
数据选项卡——筛选和排序——高级——选择不重复的记录

9、复制的应用

公式可直接复制
计算比例，分母不变，在分母之前添加绝对索引符号$ ：可使用快捷键F4：按一次：B10$ ;按两次：$B$10；按三次：B$10，再按取消

10、合并报表

遇到问题：

解决办法：启用宏：选项——信任中心——信任中心设置——宏设置——启用
或者：用WPS打开
将五家子公司的报告合并到一张表（集团公司）
数据要用公式链接，而不是简单粘贴
粘贴之后需要在其他行进行同样的操作，记得取消绝对索引，先选中要复制的操作行，再按F4
Excel一个工作薄中的表相互复制粘贴不会加绝对索引，但是跨工作簿复制粘贴会加行列绝对索引号

11、数据区域的选中方式

Ctrl+Shift+箭头，快速选中

12、排序

添加条件排序：排序——添加条件——首要关键词、次要关键词
有空行不参与排序：Ctrl+G定位空值，删除
删除期末余额为0的行：容易误删隐藏的单元格，所以首先筛选——空值——查找定位——可见单元格（Alt+分号键）
合并单元格的排序（见后）

相同内容的快速添加

业务场景：应收账款明细表中为期末、期初余额标注类别：

筛选：按照金额数目筛选出A类——快速填充A：首先选中，再定位“可见单元格”避免把隐藏的单元格也标注了（Alt+分号）——Ctrl+Enter

13、制作下拉菜单

在这里插入图片描述

数据——数据验证（限制该单元格输入数据的类型、范围等）——序列——选择来源
出错警告：可以限制停止、警告信息

14、占位符的了解

作用：通过自定义对单元格格式进行任意的调整，比如日期、千分符的设置
场景：设置可以自动填充的函证编号：

设置单元格格式（或者菜单栏“对齐方式”下面的扩展箭头）——自定义——“HA-SE-88”@（含义是：在输入数字数据之后自动添加文本，使用自定义格式为：”文本内容”@）
其他占位符
1、#　数字占位符
只显有意义的零而不显示无意义的零。小数点后数字如大于”#”的数量，则按”#”的位数四舍五入。

2、0　数字占位符
如果单元格的内容大于占位符，则显示实际数字，如果小于点位符的数量，则用0补足。

3、@　文本占位符
如果只使用单个@，作用是引用原始文本，要在输入数字数据之后自动添加文本，使用自定义格式为：”文本内容”@；要在输入数字数据之前自动添加文本，使用自定义格式为：@”文本内容”。@符号的位置决定了Excel输入的数字数据相对于添加文本的位置。

4、*　重复下一次字符，直到充满列宽
例：代码：”@* -”。”dzwebs”显示为”dzwebs-------------------”，可就用于仿真密码保护：代码”;;;”，123显示为：************

5、，　千位分隔符
例：代码” #,###“，33000显示为：33,000

6、:用这种格式显示下一个字符
“文本”，显示双引号里面的文本。
“\”：显示下一个字符。和“”””用途相同都是显示输入的文本，且输入后会自动转变为双引号表达。
例：代码"人民币 "#,##0,“百万”，与\人民币 #,##0,\百万，

7、？　数字占位符
在小数点两边为无意义的零添加空格，以便当按固定宽度时，小数点可对齐，另外还用于对不等到长数字的分数

15、if函数基础应用

必须要会函数预览：if、sumif、sumifs、vlookup、countif、countifs
if函数的结构：

=if(条件，返回真，返回假）
可以嵌套：=if（条件1，返回“是”，if（条件2，返回“是”））
eg：
= if（scor<=60，“不及格”，if(scor<=80,“良好”,"优秀”））
= if科目方向是借方，那么执行期初加借方减贷方，否则执行期初加贷方减借方
固定资产测算折旧（平均年限法），=if（已经提足折旧，…)
应用一：验证会计恒等式：资产=负债+所有者权益
验证=IF（资产=负债+所有者权益，“平”,“不平”）
并求出差异：=IF（资产=负债+所有者权益，“平”,资产-（负债+所有者权益））
应用二：计算本期应计提折旧月数

首先判断是否已经提足折旧:=IF(使用年限乘以12>=已使用月数,“未提足折旧”,“提足折旧”)
思路：如果本期已经提足折旧，返回0，如果没有提足折旧，则分为两种情况，第一种是剩余这就月数大于12，则返回12；第二种是剩余折旧月数小于12，则返回剩余折旧月数
公式;
=IF(已使用月数>=使用年限12,0,IF(使用年限12-E3>=12,12,使用年限*12-E3))
应用三：用IF函数对齐两个字和三个字的名字
=IF(LEN(B3)=2,LEFT(B3,1)&" "&RIGHT(B3,1),B3)
如果是两个字,则返回左边第一个字+&文本连接符+“空格”+&+右边第一个字,否则返回B3
快速：对齐方式——水平对齐——分散对齐——缩进2字符
应用四：按照级数计算个税

=IF(G3<0,0,IF(G3<=3000,G33%,IF(G3<=12000,G310%-210,IF(G3<=25000,G3* 20%-1410,IF(G3<=35000,G325%-2660,IF(G3<=55000,G330%-4410,IF(G3<=80000,G3*35%-7160)))))))
注意税率如果用公式链接要加上绝对索引$

16、按照颜色求和

首先Ctrl+F（查找）找出所有需要求和的单元格（同一个颜色）：格式下拉箭头——从单元格选择格式——光标变成取色笔——查找全部——全选
然后将选中的单元格单独定义一个名称：右键——定义名称“红色”
SUM（“红色”）

17、IF、SUMIF、SUMIFS

按条件求和：一个条件sunif，多个条件sumifs
场景：按照固定资产类别计算金额：

Range :条件范围
Criteria:条件
Sum_Range：求和范围
需要添加绝对索引

18、Vlookup函数

应用一：已有应收账款发函明细表，根据公司名称查找该公司的欠款

Vlookup四个参数：
查找值：公司名称
查找范围：在“应收账款发函明细表”中选择，第一列必须与查找值相同
输出值在第几列：在范围内，要查找的值属于第几列
精确查找还是模糊查找：一般0就行
=VLOOKUP(C3,函证明细!B2:C14,2,1)
应用二
两张固定资产卡片中，一张包含设备类型列，一张不包含，需将两表合并，资产编码是唯一的，资产顺序不一样，不能简单粘贴
即：根据资产编码去另一张表中查找设备类型
=VLOOKUP(A4,‘固定资产卡片（上）’!A4:K2227,3,0)
A4是资产编码，3指的是“设备类型”在查找范围的第三列
提醒：两张表的资产编码格式一致

19、Vlookup进阶

深注协课程内容缺失，转而投向B站"晒书包的Excel"视频

- 近似匹配（区间查找）
场景：某个数字区间（分数）内属于某个等级（差、良、优），根据具体数字查找等级
首先：另起一列，列出区间的最小值，按从小到大排列，假设为B1：B5）
然后：Vlookup(具体分数，B1：B5，返回的列（等级）属于第几列，0）

- 反向查找
方法一：要查找的列位于“查找条件列”的左边，把条件列复制过去，构造辅助列
方法二：当原始表格不能动时，通过数组进行反向查找：IF{1，0}的含义是：既包含真，又包含假。构建数组区域：IF{1，0}，返回真，返回假，会返回真假两列

- 多条件查找
场景：不仅要根据水果类型，还要根据产地查找
方法一：首先，构造辅助列：用&链接两个条件单元格：设备类型&产地
VLOOKUP（水果类型&产地，范围：第一列是辅助列，与查找条件一致），第几列，0精确查找）
方法二：构建数组IF{1,0},水果列&产地列，市场价列，构成查找区域
- 屏蔽错误值
与（IFERROR）函数嵌套：IFERROR（返回值，如果错误则返回的值）
IFERROR(VLOOKUP，“”），如果有值返回VLOOKUP的值,没有则返回空值

**- 使用通配符进行关键字查找：“星号”&F17&“星号 "
在这里插入图片描述
- 文本数值混合查找
两张表格的查找条件格式不一致：一个是数值，一个是文本
将数值变成文本的方式：格式转换或者在公式中的F17后添加&”"
=VLOOKUP(F27&“”,$ B$ 26:$ C$ 31,2,0)
将文本型数字变成数值的方式：在F17后添加"星号+1"
=VLOOKUP(B27*1,$ F$ 26:$G$31,2,0)

- 去除空格查找
两张表的查找条件列格式不同

使用SUBSTITUTE去除字符之间的空格
用SUB函数替代查找条件的位置
=VLOOKUP(SUBSTITUTE(F37," “,”"),$ B$ 36:$D$41,3,0)
在这里插入图片描述

若是查找区域有空格，则
=VLOOKUP(F45,SUBSTITUTE($ B$ 44:$D$49," “,”"),3,0)

- 多列批量查找
情况一:两表之间的列排列顺序一致
需要指定的”第几列“是动态的，添加COLUM函数（某一个单元格所在列数的返回值），可以使得“行”根据数量自动变化，即用COLUMN（A1)、COLUMN（B1）来表示第一列、第二列，可以变化
=VLOOKUP($ G65,$ B$ 64:$E$69,COLUMN(B1),0)
同时第一个参数的”行“没有添加绝对索引
情况二：两表之间的列排列不一样
需要使用MATCH函数对返回列数进行定位
MATCH（查找值，查找区域）替代”第几列“的位置

- 一对多查找
COUNTIF函数：=COUNTIF（区域，查找条件）在查找范围内计算个数
场景:查询条件（市场部）在查询区域内不是唯一的
在这里插入图片描述
构造一个唯一的编号：用countif函数累计计算个数，如第一个单元格计算第一行出现了几个市场部，第二个单元格计算一到二行出现了几个营销部，第三个单元格计算一到三行出现了几个运营部，再链接文本，这样能保证每一行都是唯一的
再使用辅助列作为查找区域的第一列：
=VLOOKUP($ G85&COLUMN(A1),$ B$ 84:$D$92,3,0)

多表混合查找

VLOOKUP嵌套IF函数
=VLOOKUP(H111,IF(G111=“是”,$ B$ 111:$ C$ 114,$ B$ 117:$C$120),2,TRUE)
TRUE是模糊匹配
解读：（H111：根据消费金额查找赠品，如果是会员找第一张表；非会员找第二张表，第二列，模糊匹配）
- 跨多张表查找
场景：每一张表记录了不同月份的销售情况，讲每个月的销售总量汇总到一张表

1月：=VLOOKUP(笔记本,第一张表的区域,金额所在的列,1),向下拖拽可以实现,但是向右拖拽不行,因为表格不同,在"表!“之后链接COLUM9A1)函数实现横向夸列:“表”&column（A1）&”！具体区域"——这里的写法我不懂
由于引号表示文本，需要使用INDERECT函数将文本变成可以查询的地址：

=VLOOKUP($B126,INDIRECT(“表”&COLUMN(A1)&“! $ A$3: $F$8”),6,0)

20、画组织架构图

SmartArt：先输文字再画图

21、给EXCEL加密

文件——另存为——工具下拉——打开权限的密码、修改的密码

22、把工资表变成工资条的操作

在这里插入图片描述
把下图变成上图
方法一：
1、处理表头:19个人，共需要插入19个表头，每个工资条的顺序是表头+工资条+空白行
2、为19个表头赋值1-19，为19个工资条赋值1.1-19.1，为空白条复制1.2-19.2
3、排序
4、添加边框，空白行不要边框：Ctrl+G定位
插入列的操作技巧：下拉复制不覆盖原有内容：选中要复制的行，按住shift，下拉，Ctrl+D填充
一次性插入很多行：先选中很多行，再点插入/复制一行——选中19行——右键——插入复制单元格
方法二：使用VLOOKUP函数制作第一行
VLOOKUP(序号,范围,COLUMN（B1），0）