Excel在财务中的实用技巧

Excel在财务中的实用技巧Excel在财务中的实用技巧一、基本概述二、Excel的常用快捷键介绍三、Excel常用公式和函数的使用四、数据透视表的建立和应用一、基本概述1.1Excel的初始设置①Excel选项（精度的设置）②快速访问栏的设置保存、选择性粘贴为数值、选取可见单元格、1.2、工作簿和工作表的运用1、在屏幕上同时看到一个工作簿的多个工作表（比如sheet1和sheet2）,只需打开该工作簿并且显示其中一个工作表sheet1，然后：①选择“视图”︱“新建窗口”②单击新建窗口中的sheet2③然后选择“窗口”︱“重排窗口”④选择“水平平铺”选项2、选取工作范围在工作簿里，如果要选择几片不相连的区域或单元格，可以按住Ctrl键，再选择单个或多个单元格即可选定所需的工作范围。同样适用于工作表间的选定。3、拆分和冻结工作表1.3数据的快速填充及有限性Ctrl+Enter在多个单元格中输入相同的数据，只需要在最后一个单元格输入数据，复制后同时按下Ctrl+Enter键即可。同样也适用于某一区域内的相同的公式填充。F4键重复上一次操作。例如：要将多个不连续的单元格改为填充绿色，可以选择第一个单元格，设置填充绿色，然后依次选择其余的单元格，每次按一下F4.1.3数据的快速填充及有限性数据的有限性①限制数值的可输入位数对于固定位数的数据，例如订单号、身份证号、手机号等，可以设置该行的数据有限性，防止输入的数字少一位或者多一位。选择“数据→有效性”菜单，打开“数据有效性”对话框，在“设置”选项卡的有效条件“允许”处选择“文本长度”，在出现的“数据”下面的方框中输入数字，再选择“出错警告”选项卡并设置一个“出错警告”，确定返回。这样，但输入的号码位数不正确时，系统会给出提示。•②限定输入值的范围•③圈出指定数据•实例描述：想把本公司员工考勤中属于全勤的员工在Excel表格中用红圈突出显示出来，以方便查阅核对。首先，选中表格中考勤天数所在的单元格（如C2:C62），选择“数据→有效性”菜单，在弹出的“数据有效性”对话框中，在“设置”选项卡的有效条件“允许”处选择“小数”，在数据处选择“介于”，然后在下面分别输入最小值（“1”）和最大值（“21.5”）（假设当月全勤是22天），点击确定；然后，单击“圈释无效数据”按钮，这样考勤天数绩在21天以上的就被圈上了一个红圈，起到了突出显示的作用。二、Excel常用快捷键介绍•Shift+F11新建空白的工作表(sheet1)•SHIFT+空格键选定当前单元格所在的整行•Ctrl+N新建工作簿(book1)•Ctrl+1设置单元格格式•Ctrl+2加粗•Alt+enter一个单元格里输入两行数据，换行。•Alt+分号：定位于可见单元格。在筛选的状态下删除数据很方便。•Ctrl+减号：删除某一行或某一列，先选择后按快捷键•Ctrl+”↑↓←→：移动到当前数据区域的边缘•Ctrl+X剪切选定的单元格•Ctrl+Z撤销上一次操作•Ctrl+A选定整张工作表•Ctrl+PageDown移动到工作簿中的下一张工作表•Ctrl+PageUp移动到工作簿中的上一张工作表•Ctrl+shift+L筛选，再用一次取消筛选•其他常用快捷键：•Windows+D显示桌面•Windows+E打开“计算机”•Windows+F在“计算机”里搜索•ALT+Tab切换不同窗口三、Excel常用公式与函数的使用•1、公式的引用（F4键)•（1）相对引用•（2）绝对引用•（3）混合引用•2、运算符•（1）算术运算符(+,-,*,/,%,^)•（2）比较运算符(=,=,)•（3）文本运算符(&)•（4）引用运算符(:,,)•3、函数的使用•3.1数学与三角函数•Sum(number1,number2,…)求和•Round（number，小数位数）将参数引用的数舍入到指定的小数位数•如：round（123.456，-1）•3.2统计函数•Average(number1,number2,…)求平均值•Count（value1，value2，…)返回参数中数字的个数•3.3文本函数•Concatenate（text1,text2,…）返回将给出的几个字符串合并的一个字符串•Value(text)将以文本形式输入的数字转换成数值。•Len(text)返回输入项中的字符个数。（文字或者数值的长度）3.5查找及引用函数•Vlookup：查找所构造的表格中存放的信息•Vlookup(lookup_value,table_array,col_index_num,range_lookup)•lookup_value需要以它为索引的数值•table_array查找范围•col_index_num查找值所在列号•range_lookup查找时是精确匹配还是近似匹配(0：精确匹配，1：近似匹配)3.6财务中常用的公式函数运用•1、个税计算公式•ROUND(MAX((A1-3500)*{0.03,0.1,0.2,0.25,0.3,0.35,0.45}-{0,105,555,1005,2755,5505,13505},0),2)A1为应发工资扣除五险一金后的工资余额2、隐藏身份证信息、电话信息具体方式如下：第一步：先将身份证信息输入到sheet1的单元格A1中,第二步：在sheet2中A1单元格输入下面公式:隐藏中间四位：=CONCATENATE(LEFT(A1,7),REPT(****,1),RIGHT(A1,7))隐藏前四位：=CONCATENATE(REPT(****,1),RIGHT(A1,14))隐藏后四位：=CONCATENATE(LEFT(A1,14),REPT(****,1))第三步：将sheet1隐藏。选中此表点击右键进行操作即可。第四，避免在数据清单中出现合并单元格。第五，避免在单元格的开始和末尾输入空格。第六，尽量避免在一张工作表中建立多个数据清单，每张工作表最好仅使用一个数据清单。第七，工作表的数据清单应与其他数据之间至少留出一个空列和一个空行，以便于检测和选定数据清单。在制作数据透视表之前，应该按照以上7点来检查数据区域，如果不满足上面的要求，需要先进行整理工作表数据从而使之规范。