为么老外的Excel表格都这么美丽？看这一篇就够了！

m5k1umn

俗话说得好，Excel用得好，下班会很早。做为财务会计，工作中最常用到的工具便是Excel，怎样利用它加强工作效率？这是咱们今天讨论的专题！

你们有无发掘？老外做的Excel表格，通常都很美丽。

再瞧瞧咱们平常的表格，难看度瞬间爆表：

无论是在企业做财务，还是在四大做审计，有一项共通的技能便是：Excel要6到飞起！正所说Excel玩得好，下班会很早。那样，问题是，Excel的技能那样多，做为财务，怎么样才可算完全精通呢？达到什么样的水平才可让Excel表格又好看，又实用呢？Excel技能千千万，其实说到底，做到精通的话，只要学会4+1，4个核心函数为基本篇：SUM、IF、VLOOKUP、SUMIF，1个数据透视表为进阶篇。搞清楚这五个，在工作中需要Excel处理的，基本上问题都不大。6大核心资料速速领取！1、 800个会计最常用Excel表格模板2、 203个会计工作Excel快捷键大全3、 168套财务最高逼格Excel分析图表4、 153个Excel视频教程，1800分钟，入门到精通5、 37套会计做账Excel全套表格6、 2019全年工资表Excel工具包，带公式，数据全自动生成无招数仅有诚意让你的工作效率提高10倍长按识别下方二维码关注回复“0918”就可免费领取

01SUM函数（数学）重要程度：★★★★★学习难度：★★在平常的工作中与学习中，SUM是一款应用极为广泛并且做为入门级的函数来学习的。因此，整体来讲，SUM函数难度不高，应用性却很广泛。然则不可由于简单就小瞧了SUM，它还有有些不为人知的小技巧！1、累计求和如下图，是某企业的每一天的营销业绩，需求计算每日的累计金额。在D2单元格中输入公式：=SUM(C$2:C2)，按Enter键完成后向下填充。重视：此时的C$2必定是要锁定行标的，这般在下拉的过程中才会产生从第二行始终到向下的行的一个引用区域。2、带有合格单元格的求和合并单元格的求和，始终是一个比较让新手头疼的问题。选中D213单元格区域，而后在公式编辑栏里输入公式：=SUM(C2:C13)-SUM(D314)，而后按<Ctrl+Enter>完成，如下图所示：注：必定要重视第二个SUM函数的区域范围要错位，否则就报错。3、带有小计的单元格求和带有小计的单元格到底怎么样求和？在C9单元格里是输入公式：=SUM(C2:C8)/2，按Enter键完成。如下图所示：重视：这儿是自用了小计与求和的过程是重复计算了上面的数据，因此再除以2就能够得到不重复的结果，亦正是想要的结果。4、文本怎样求和在有些比较不规范的表中呢，会有文字与数字的混合的问题，给求和带来了必定了的难度。在C12单元格中输入公式：=SUM(--SUBSTITUTE(C2:C11,"元",""))按组合键<>Ctrl+Shift+Enter>完成。重视：这儿的公式两边的花括号不是的手动输入的，而是在按组合键后系统自动添加上去的。5、多工作表求和下表中是4个月的业绩统计，每一个工作表的里面的张成的位置都是同样的，求张成的1-4月的提成统计。在F5单元格中输入公式：=SUM(1月:4月!C2),按Enter键完成填充。如下图所示：重视：在Excel中是支持上面的这种引用的，然则在WPS里面貌似不支持。并且要重视每一个表中的结构或数字的固定的位置是同样的，才能够运用这个公式。6、多区域联合计算在有些工作表中，咱们并不需要对连续的区域进行求和，可能是对不连续的某几个区域进行求和。如下图所示，计算1-2月，3月以及5-6月的合计。在H2单元格中输入公式，按Enter键完成后向下填充。=SUM(B2:C2,E2,G2)重视：这儿运用逗号将各个不连续的区域连接起来的引用方式叫联合引用。7、要求求和除了SUMIF，SUMIFS之外还能够运用SUM函数来进行要求求和。在G4单元格中输入公式：=SUM((B2:B11="日用")*(E2:E11))按组合键<Ctrl+Shift+Enter>键完成后向下填充。如下图所示：8、要求计数除了上面的求和之外，SUM函数还能够代替COUNTIFS，COUNIF函数进行计数。在G4单元格中输入公式：=SUM(1*(B2:B11="日用"))按组合键<Ctrl+Shift+Enter>键完成后向下填充。如下图所示：02IF函数（规律）重要程度：★★★★学习难度：★★★IF函数是Excel规律家族的扛把子，只要是规律判断就能够说离不开IF函数。IF函数很简单，几乎接触过Excel的人看到都能理解。下面我分享一下财务人员最常用到的IF函数。例如：倘若已付清，则为“关账”，倘若未付清且金额大于3000元，则为“立即摧账”，倘若未付清且少于3000元，则为“状态正常”。在H4单元格中输入公式：=IF(E2="否",IF(D2>=3000,"立即催账","状态正常"),"关账")，按Enter键完成后向下填充。重视：一般状况下，IF函数经常与其他的规律函数一块运用，例如AND，OR，NOT等函数。该函数在运用的时候必定要重视前面的相关的规律。最后，IF函数协同VLOOKUP函数能够实现反向查询，双要求查询。6大核心资料速速领取！1、 800个会计最常用Excel表格模板2、 203个会计工作Excel快捷键大全3、 168套财务最高逼格Excel分析图表4、 153个Excel视频教程，1800分钟，入门到精通5、 37套会计做账Excel全套表格6、 2019全年工资表Excel工具包，带公式，数据全自动生成无招数仅有诚意让你的工作效率提高10倍长按识别下方二维码关注回复“0918”就可免费领取03VLOOKUP函数（筛选）重要程度：★★★★★学习难度：★★★★VLOOKUP被叫作为Excel中的效率之王，然则95%的Excel运用者都不可很好运用VLOOKUP。然则，VLOOKUP函数又是Excel中的公众情人。有平台曾经做过“倘若只能选取学习Excel中的一项功能，你会选取哪个”的调查，VLOOKUP函数竟然高居第二位。在咱们的工作中，基本每日都会遇到这般的场景。例如从总表中，按照姓名匹配身份证号信息，按照考核等级确定奖金比例。这些工作本质上都是匹配调用：匹配一样的数据，调用出咱们需要的数据。要处理这个问题，最常用到的便是VLOOKUP函数。那样VLOOKUP函数到底怎样运用呢？VLOOKUP函数语法结构：VLOOKUP(Lookup_value,Table_array,Col_index_number,Range_lookup),即VLOOKUP（查询值，查询范围，返回的数值所在的列数，精确匹配还是模糊匹配）。1、VLOOKUP函数精确匹配，返回你需要的独一数据例如按照姓名匹配身份证号信息，针对这种匹配调用独一的数据，就要用到VLOOKUP函数的精确匹配了。操作过程：查询范围为绝对引用，可按快捷键F4，精确匹配下参数为0或FALSE。重视事项：查询范围和要返回的数值所在的列数都是要从查询值所在的列起始计算。VLOOKUP查找调用精确匹配2、VLOOKUP函数模糊匹配，返回你需要的区间数据例如按照考核等级确定奖金比例，针对这种在区间范围内匹配调用数据，就要用到VLOOKUP函数的模糊匹配了，这个功能完全能够替代掉IF函数的多层嵌套，再亦不消为写错次序发愁。操作过程：查询范围依然为绝对引用，可按快捷键F4，模糊匹配下参数为1或TRUE。重视事项：等级表的编制要从小到大VLOOKUP查找调用模糊匹配说清楚大方向之后，咱们来分享一下VLOOKUP的几个常规操作办法：1、常规查询查询姓名对应的营销额。在F3单元格中输入公式=VLOOKUP(E3,$A$2C$9,3,0)，按Enter键完成。如下图所示：2、日期查询在查询日期的时候查询的结果一般会是一串数字，为了使日期能够返回相应的格式，那样需要协同TEXT函数才可完成查询需要。在F3单元格中输入公式=TEXT(VLOOKUP(E3,$A$2C$9,2,0),"yyyy/m/d")，按Enter键完成。如下图所示：注：如返回格式为2018/12/03，则TEXT的第二个参数的格式能够设置为“yyyy/mm/dd”就可。3、查询的值为空时在当查询的值为空时，一般状况下会返回结果为0，那样倘若让结果返回空白呢，处理的办法便是在公式后面一个“”。在F3单元格中输入公式=VLOOKUP(E3,$A$2C$9,3,0)&""，按Enter键完成。如下图所示：4、当查询的目的格式不统一时报错怎样处理（1）倘若查询的目的值是文本格式，而数据区域中是数值格式。如下图所示，A列中的员工编号为数值格式，而F3单元格中的员工编号为文本格式。在G3单元格中输入公式：=VLOOKUP(--F3,$A$2D$9,4,0)，按Enter键完成。注：--为两个负号，即减负的意思，能够理解为负负得正，这儿是把文本强制转换为数值，因此问题就很容易被处理了。（2）倘若查询的目的值是数值格式，而数据区域中是文本格式。如下图所示，A列中的员工编号为文本格式，而F3单元格中的员工编号为数值格式。在G3单元格中输入公式：=VLOOKUP(F3&"",$A$2D$9,4,0)，按Enter键完成。注：&""是强制地把数值格式转换成文本格式。5、区域查询有时候需要查询某一个值处在那个区间里。例如查询下列的营销额对应的营销提点为多少。在E2单元格中输入公式：=VLOOKUP(D2,$H$2I$8,2,1)，按Enter键完成。注：这儿运用该函数最后一个参数为1，即模糊查询，来确定查询的值处在给定的那一个区间。6、模糊查询VLOOKUP函数亦是支持模糊查询，即支持通配符查询。查询姓名中带有“冰”字的员工的营销额，在H3单元格中输入公式：=VLOOKUP("*"&G3&"*",$B$2D$9,3,0)，按Enter键完成。注：倘若要查询以“冰”开头的那样公式的第1参数为："*"&G3; 倘若查询以“冰”结尾那样公式的第1个参数为：G3&"*".7、查询次序与数据区域中次序一致的多项时VLOOKUP函数查询次序一致的多项时，能够借助COLUMN函数构建查询序列。在H2单元格中输入公式：=VLOOKUP($G2,$A$2D$9,COLUMN(B1),0)，按Enter键后向右填充。注：COLUMN函数是返回列号。第1个参数必定要锁定列号，这般才可正确的结果。8、十字交叉查找VLOOKUP函数倘若有两个要求是呈现十字交叉时且次序与数据区域中的次序不一致时，能够与MATCH函数完成查找。在H2单元格中输入公式：=VLOOKUP($G2,$A$2D$9,MATCH(H$1,$A$1D$1,0),0)，按Enter键完成后向下向右填充。注：必定要锁定VLOOKUP函数的第1个参数的列号，MATCH函数的第1个参数的行号，这般才可得到正确的结果。9、多要求查找VLOOKUP还能进行多要求查找，这个用法相信有非常多人不晓得吧。在I2单元格中输入公式：{=VLOOKUP(G2&H2,IF({1,0},$A$2:$A$9&$B$2:$B$9,$D$2:$D$9),2,0)}按组合键<Ctrl+Shift+Enter>完成后向下填充。注：公式两边的花括号不是手动输入的，而是按组合键后自动输入的。VLOOKUP的第三个参数为2，第四个参数为0是固定的。04

SUMIF函数（要求计数）

重要程度：★★★★学习难度：★★★SUM的意思是“加和”，再加上“IF”，意思便是对范围中符合指定要求的值求和，即满足相应的要求才进行计算。在工作中，大部分场景都不是对所有数值进行求和，而是按照必定的要求筛选后在必定范围内进行计算，例如统计某个制品的营销额状况，统计某个分部的人员工资状况。要处理这种要求求和问题就要用到SUMIF函数了。SUMIF函数结构：=SUMIF(要求范围,要求,求和范围)SUMIF函数有一个强化版本，即多要求版本——SUMIFS。例如，下表是一份应付账款的借款明细表。请按右面的要求进行统计。需求：已付清企业的应付款额大于30万的平均值。在G5单元格中输入公式：=AVERAGEIFS(C:C,E:E,"是",C:C,">300000")，按Enter键完成。重视：该函数是求平均值的函数，倘若除数为0的状况下会返回错误值"#DIV/0!"，即无满足要求的值的时候会报出错误值。6大核心资料速速领取！1、 800个会计最常用Excel表格模板2、 203个会计工作Excel快捷键大全3、 168套财务最高逼格Excel分析图表4、 153个Excel视频教程，1800分钟，入门到精通5、 37套会计做账Excel全套表格6、 2019全年工资表Excel工具包，带公式，数据全自动生成无招数仅有诚意让你的工作效率提高10倍长按识别下方二维码关注回复“0918”就可免费领取05

数据透视表

重要程度：★★★★★学习难度：★★★★数据透视表是数据分析的神器，咱们平常工作中要统计的各样报表都能够经过这一功能来实现。做为一种交互式的图表，它准许用户按照需要对各类数据维度进行划分，经过快捷地拖动各类数据维度，将她们进行区别的重组，实现咱们想要的结果。1、拖拖拽拽”，快速制作统计报表，完成数据统计按照你需要统计的数据维度和表格结构，“拖拖拽拽”，快速制作出你需要的统计报表，完成相应的数据统计。操作过程：选中原始数据表中的任意单元格—【插进】—【数据透视表】—【数据透视表字段及区间】—按照报表行列呈现需要，在字段列表中选定该字段并按住鼠标左键拖放到下方的矩阵窗口中，数据透视表布局即完成。数据透视表创建2、多种数值统计方式，容易完成数据透视表供给了求和、计数、最值、平均值、标准差、百分比等多种数值统计方式，你想要的结果它都能够呈现操作过程：需要几种统计方式就拖入几次计算【值字段设置】—【值表示方式】—【百分比】。数据透视：都数值计算3、按照时间变化创建组，报表多元表示不只是日期，数据根据月份、季度、年度或它们的组合展示，统统都能够。操作过程：选中任一日期数据，右键创建组，选中月份，按住CTRL,再选中年，能够随意组合的。这个亦能够进行年龄分段统计等问题。数据透视-创建组-时间4、城市组合成区域，只要手动创建一下北京、天津、沈阳，这些城市怎样组合成【华北区】？老板就要的大区级的数据统计，我该怎么办？不要担心，手动创建一下，瞬间完成操作过程：选中要组合的标签（CTRL进行多选）—右键创建组—修改数据标签。数据透视-创建组-区域组合5、数据透视表下，数据排序依然有效在数据透视表下，将数据升序、降序或你自己定义的次序排序？操作过程：选中要排序的任一一数据—右键选取排序—选取升序或降序。倘若是自定义排序，先经过【选项】嵌入自定义排序，而后再选取升序或降序操作。数据透视表：排序6、数据亦可筛选，想要什么找出什么找出营销量TOP3的明星营销员？筛选一下，便是这么简单操作过程：选中任一一数据标签—右键筛选—【前10个】—修改为根据营销额最大的3个。数据透视表：筛选7、数据变化了，刷新一下，数据透视表随之而动按照统计的维度，咱们就能够制作数据透视表模板了。数据一有变化，咱们就更新一下，统计结果马上出来，连“拖拖拽拽”的功夫都省了，这便是自动化！操作过程：选中数据透视表中任一数据—右键点击刷新。这个刷新操作是没法自动完成，手动一下，手动一下就好。数据透视表：手动刷新8、总表分多表，利用筛选器，告别复制粘贴从系统内导出的总表数据，怎样按照咱们的需要，例如营销城市、营销分部等标签分成多个分表呢？数据透视表中的筛选器瞬间实现操作过程：将分表的数据标签拖入数据透视表中的筛选器—数据透视表选项—表示报表筛选页—确定。双击各个报表的汇总值，符合需求的原始数据就显现了！数据透视表-筛选器-分页9、数据根据时间轴滚动，日程表来了让重要数据根据时间轴展现？怎么可能实现得了。插进一个日程表，就足够了。操作过程：选中数据透视表任一单元格—插进日程表，拖拉一下日程表下方的滚动轴，想看哪个月就看哪个月，想看哪几个月就看哪几个月。数据透视表—插进日程表数据透视表的功能是不是很强大，倘若再让你完成100张数据统计报表，是不是工作效率瞬间倍增。但在这儿，还是要给大众一个小贴士：数据透视表好用，但原始数据必定要规范：数据标签行仅有一行、数据完整、不要汇总统计、不要合并单元格、数据格式规范。千万要记住！06让Excel变得更加商务以上，是今天给大众分享的Excel的的基本技能和进阶技能。完成为了基本的底子，咱们再来思虑怎么让Excel变得好看，以及让咱们的效率变得更高！此刻咱们再回到文案起始的问题，怎么让Excel变得更加商务，像老外的Excel那样美丽！咱们对老外的表格好看的原由进行了归纳，下面咱们按老外的思路改造咱们的表格。第1步：更换和弱化表格线，明显表示数据。

去掉表格背景网络线

除表头和表尾外，数据部分用浅灰色表格线。

第2步：设置隔行背景色，能够选浅灰或浅蓝色填充

填允一行后，用格式刷向下刷

稍增多行高

第3步：修改字体类型。

标题用黑体

数字用Arial

汉字用微软雅黑

合计行字体加粗

第4步：用条形图增多的年合计的可视性。选择G5:G11  -  要求格式 - 数据条完工！

倘若表格不需要打印，咱们还能够换另一一种风格：

表头深色背景，白色字体

中间用浅色填充，表格线用白色细线

表尾灰色背景

或

亦有朋友说，为何不直接套用表格？套用后你会发掘，结果并不是你想要的。

另：老外还有3个常用法宝

填充色用同一个色系，让数据和背景一体。

控件的运用方便筛选数据，又加强了商务感。（经过定义名叫作引用控件数据生成动态数据源）

小照片的装饰。

其实Excel美丽并不寓意着花梢，表格设计便是要明显和展示数据，达到这个目的，又能看上去很舒服。便是完美又好看的Excel表格。6大核心资料速速领取！1、 800个会计最常用Excel表格模板2、 203个会计工作Excel快捷键大全3、 168套财务最高逼格Excel分析图表4、 153个Excel视频教程，1800分钟，入门到精通5、 37套会计做账Excel全套表格6、 2019全年工资表Excel工具包，带公式，数据全自动生成无招数仅有诚意让你的工作效率提高10倍长按识别下方二维码关注回复“0918”就可免费领取▎本文内容源自：24财务excel，Excel不加班，Excel聚焦，Excel精英培训，LinkedIn、注册会计师等。