图文教程

今天分享一个图表技巧，先看效果：

只要光标滑过不同的商品名称，图表就会自动变化，是不是很炫酷啊。

步骤一：准备数据源

 

步骤二：输入代码

Alt+F11 打开VBE窗口，【插入】→【模块】
复制如下代码到模块中，退出VBE窗口：

Function techart(rng As Range)
Sheet1.[g1] = rng.Value
End Function

 

步骤三：输入公式

在G1单元格中输入任意一个商品名称，如牛仔裤。
G2输入公式，下拉至G13：
=HLOOKUP(G$1,B$1:E2,ROW(),)

选中G1：G13，【插入】→【折线图】

 

步骤四：美化图表

设置折线图为无线条。
设置数据点样式，添加垂直线。
此处省略具体步骤，大家可以根据需要和喜好，设置出不同样式不同风格的图表样式。
最终效果如下：

 

步骤五：输入公式

在图表上方，输入模拟图表标题的公式。
=G1&”2015年销售趋势”

在图表下方，输入模拟坐标轴的公式。
本例以J15:K16单元格区域为例，依次输入以下公式：
=IFERROR(HYPERLINK(techart(B1)),”◆”&B1&”◆”&REPT(” “,99))
=IFERROR(HYPERLINK(techart(C1)),”◆”&C1&”◆”&REPT(” “,99))
=IFERROR(HYPERLINK(techart(D1)),”◆”&D1&”◆”&REPT(” “,99))
=IFERROR(HYPERLINK(techart(E1)),”◆”&E1&”◆”&REPT(” “,99))

输入公式后的效果如下：

公式后面为什么要连接REPT(“”,99)呢？
如果只是单独显示分类的话，鼠标需要停到文字上才会变化，所以使用99个空格占位，鼠标只要停在当前分类单元格任意一个地方，都可以触发代码运行。
将文件保存成启用宏的文件（xls、xlsm），OK啦！

日常工作中大家经常要以图表的形式展示数据，最终汇报给老板或是呈交给客户。这些图表的制作过程，往往会占用很多的时间。

下图中，就是经过老板多次“指导”，改了又改的图表效果：

这只是8月份的数据和图表，接下来还有9月份、10月份、11月份……
是不是还有小伙伴每次都从头开始，日复一日的去设置、去死磕呢？
咱们今天就说说如何快速制作统一样式的图表。

1、图片设置

首先设置出一个让领导满意，让客户放心的图表样式（经老祝调查，这一步的实现难度最高，过程最憋屈）。

2、图片另存

接下来单击选中图表，然后单击鼠标右键→另存为模板：

3、图片套用

保存为模板后，如果再有结构相同的数据，就可以一键生成具有个性化样式的图表啦：

数据还分类型吗？
对啊，就像有些人是内分泌失调型，有些人是老年痴呆型，还有些人是闲的没事找抽型，Excel中的数据也分为不同的类型。将数据保存为正确的类型，后续的统计汇总会方便很多。

接下来咱们就看看不同数据类型有些啥特点。

1、数值

这种类型的基本都是需要计算的数字，比如销售额、考试成绩等等。

2、文本

姓名、性别、商品名称等等这些都是文本型， 另外还有一些不需要进行计算的数字，比如电话号码、银行卡号等等也可以保存为文本型。

3、日期和时间

日期和时间数据，是一个比较特殊的分类，比如在A1单元格中输入“2021-2-14”，Excel就会自动将其保存为日期格式。

如果将这个单元格的数字格式设置成常规（也就是没有任何格式）或数字，单元格中的内容就会变成一个5位数：

这个数字是啥意思呢？
按照官方说法，这个数字叫“日期序列值”。在Excel中，可以识别的日期范围为1900年1月1日到9999年12月31日，每个日期的序列值就是这个日期距离1900年1月1日的天数，一天的日期序列值为1。
你看，日期即可以显示成特有的样子，也可以显示成一个数值，因此也可以像数字一样进行加减。下图中的两个日期，要计算间隔了多少天，只要一个减法就可以了。

如果需要计算“2021/5/1”20天之后的日期，你知道怎么计算吗？
接下来再看看时间类型。
在单元格中输入“8:20”或者“8：20”，Excel都会自动存储为时间格式。虽然数字格式显示的是“自定义”，但是编辑栏中已经露馅儿了。

如果把这个单元格的数字格式设置成常规，看看是啥结果：

竟然变成小数了。
这个小数是咋回事呢？这个就是时间的序列值。
前面说过，日期的序列值是整数，一天的序列值为1。时间的序列值就是小数，一小时的序列值是1除以24（24个小时），一分钟的序列值是1除以24再除以60，一秒的序列值就是1除以24再除以60再除以60。

4、逻辑值

日常生活中，老王和小王见面打招呼：“您是小王吧？”。小王回答“是啊，是啊，您是老王吧？”。
在Excel里，用啥来表示“是”或“不是”呢，就是用逻辑值。逻辑值有两个，TRUE表示“是”，FALSE表示“否”。

5、错误值

错误值通常是在使用公式时，由于种种原因没能返回需要的结果，而是返回了一串不同字符组成的错误值代码。
常见的错误值有以下几种：
#VALUE!
如果公式引用的数字类型不对就会出现这种错误值。比如公式=1+我，Excel不知道“我”算老几，那就计算不了了。
#DIV/0!
这个错误值是说数字被0除了，因为0不能当被除数嘛。比如公式=1/0，那就会返回错误值#DIV/0! 。
#NAME?
Name是名字，后面加上一个问号，就是说Excel不知道公式中的字符是啥意思了：这是啥名啊？
#N/A
出现这种错误，通常表示查找类函数找不到关键内容了。N/A ，难啊。
#REF!
这个错误值表示单元格引用无效了，如果不小心删除了公式引用的行、列，就会出现这种错误值。REF，不要读成二姨夫……
#NUM!
NUM表示Number，错误值#NUM!表示无效数字。比如要计算3个数字中的第4个最小值，那肯定是计算不出来的。

1、条件判断

如下图所示，需要判断D列的成绩是否及格，公式为：
=IF(D2>=60,”及格”,”不及格”)

IF函数专门用于对条件进行判断，并返回指定内容。
用法为：
=IF(判断条件,符合条件时返回的值,不符合条件时返回的值)

 

2、条件求和

如下图所示，要统计不同门店的销售额。F3单元格公式为：
=SUMIF($B$2:$B$12,E3,$C$2:$C$12)

SUMIF函数的作用是按指定条件求和，常规用法为：
=SUMIF(条件区域,求和条件,求和区域)
如果条件区域中的内容符合指定的求和条件，就对对应的求和区域进行汇总。

 

3、计算工作日天数

如下图，要计算两个日期之间的工作日天数，C2单元格公式为：
=NETWORKDAYS.INTL(A2,B2,1,E$2:E$6)
第三参数使用1，表示周六和周日是周末。

NETWORKDAYS.INTL函数能够使用自定义的周末，来计算两个两个日期之间的完整工作日天数。
使用方法为：
=NETWORKDAYS.INTL(起始日期,终止日期,周末日,其他节假日)

 

4、计算年龄

如下图所示，要根据C列的出生年月计算年龄。公式为：
=DATEDIF(C2,TODAY(),”y”)

DATEDIF函数用于计算两个日期之间的间隔。
第一参数是开始日期，第二参数是结束日期，第三参数是返回的数据类型。
使用Y，表示返回整年数。使用M，则表示返回整月数。

 

5、提取出生年月

如下图，要根据A列的身份证号码返回出生日期，公式为：
=–TEXT(MID(A2,7,8),”0-00-00″)

先使用MID函数从A2单元格中的第7位开始，提取表示出生年月的8个字符19720516。然后使用TEXT函数将其变成具有日期样式的文本“1972-05-16”，最后加上两个负号，也就是计算负数的负数，通过这样一个数学计算，把文本型的日期变成了真正的日期序列值。
如果单元格中显示的是五位数值，只要设置成日期格式就好。

 

6、提取性别

如下图，要根据A列的身份证号码，判断性别。公式为：
=IF(MOD(MID(A2,17,1),2),”男”,”女”)

先使用MID函数，从A2单元格的第17位开始提取1个字符，这个字符就是性别码。
然后使用MOD函数，计算这个性别码与2相除的余数。
如果IF函数的第一个参数等于0，IF函数将其按FALSE处理，返回第三参数指定的内容“女”。如果不等于0，则按TRUE处理，返回第二参数指定的内容“男”。

 

7、逆向查询

如下图所示，要根据G2单元格姓名，在A~E数据区域中查询对应的工号。
=LOOKUP(1,0/(G2=B2:B6),A2:A6)

LOOKUP函数是数据查询中经常用到的函数之一，典型用法为：
=LOOKUP(1,0/(条件区域=指定条件),要返回的区域)
可以根据需要，将公式中的 0/(条件区域=指定条件)，写成：
0/((条件区域1=指定条件1)*(条件区域2=指定条件2)*……)
从而实现任意角度的多条件查询。

 

8、查询万金油

如下图所示，要根据H2单元格姓名，查询所在的部门。公式为：
=INDEX(B1:F1,MATCH(H2,B2:F2,))

先由MATCH函数找到查询值的精确位置，然后由INDEX函数返回指定区域中对应位置的内容。可以实现上下左右任意方向的查询。

说起工资条，小伙伴们比较熟悉的是排序法，咱们先来复习一下：
先从辅助列内输入一组序号，然后复制序号，粘贴到已有序号之下。
然后复制列标题，粘贴到数据区域之后。
再单击任意一个序号，在【数据】选项卡下单击升序按钮即可。

接下来咱们看看今天要分享的公式法，比排序法更快更便捷。

步骤1

复制“工资表”的列标题，粘贴到“工资条”工作表里。

步骤2

在“工资条”工作表的A2单元格输入以下公式：
=INDEX(工资表!A:A,(ROW(A1)-1)/3+2)
同时选中A1:G3下拉，最后删除多余的空白行，OK。

接下来咱们简单说下公式的含义：
先来看“(ROW(A1)-1)/3+2)”部分，ROW(A1)返回A1单元格行号1，计算后的结果相当于(1-1)/3+2，最终结果是2。INDEX函数返回“工资表”A列第二个元素的内容。
这个技巧的关键是同时选中三行下拉，下拉时标题行和空白行是复制，而带有公式的部分则是按照相对引用填充。
以A5单元格为例，原有公式ROW(A1)中的“A1”变成了“A4”，(4-1)/3+2的结果就是3，INDEX函数返回“工资表”A列第3个元素的内容。
到了A8单元格，原有公式ROW(A1)中的“A1”变成了“A7”，(7-1)/3+2的结果就是4，INDEX函数返回“工资表”A列第4个元素的内容。
其他以此类推。

如下图所示，在“测试”文件夹内，包含有两个Word文件和子文件夹“测试2”，在“测试2”文件夹内有Excel文档和另外一个子文件夹。
咱们看看如何快速获取文件夹内的所有文件名。

接下来咱们以Excel 2016为例，说说具体的操作方法：
1、依次单击【数据】→【获取数据】→【自文件】→【自文件夹】
2、点击【浏览】按钮，找到文件夹位置。
3、在数据查询编辑器中，右键单击Name字段，删除其他列。
4、最后单击【关闭并上载】。

使用此方法，可以快速提取出包含子文件夹在内的所有文件列表，如果在文件夹中增加了子文件夹或是新增了文件，只要在汇总表中单击右键→刷新。就可以可获取最新的文件名列表。

如果是低版本的Excel，还可以使用VBA代码的方法来提取：

Sub FileDir()
Dim p$, f$, k&
'获取用户选择文件夹的路径
With Application.FileDialog(msoFileDialogFolderPicker)
'选择文件夹
If .Show Then
p = .SelectedItems(1)
'选择的文件路径赋值变量P
Else
Exit Sub
'如果没有选择保存路径，则退出程序
End If
End With
If Right(p, 1) <> "\" Then p = p & "\"
f = Dir(p & "*.*")
'返回变量P指定路径下带任意扩展名的文件名
'如果有超过一个文件存在，将返回第一个找到的文件名
'如果一个文件都没有，则返回空
[a:a].ClearContents '清空A列数据
[a1] = "目录"
k = 1
Do While f <> ""
'如果文件名不为空，则……
k = k + 1
'累加文件个数
Cells(k, 1) = f
f = Dir
'第二次调用Dir函数，但不带任何参数，则将返回同一目录下的下一个文件。
Loop
MsgBox "OK"
End Sub

有这样一组销售数据，C列是每个月的销售额与销售平均值的比较情况：

这种带上下箭头的样式，是使用了Excel的自定义格式，可以随数据变化自动改变箭头朝向和字体颜色。
要实现这样的效果并不难。

选中C2:C10单元格区域，按Ctrl+1，弹出【设置单元格格式】对话框。
在【数字】选项卡下单击【自定义】，在格式框中输入以下格式代码：
[蓝色]↑0.0%;[红色]↓0.0%;0.0%

OK，完成了，就这么简单。

说说格式代码的意思：
[蓝色]↑0.0%;[红色]↓0.0%;0.0%
格式分为三部分，用分号隔开。
第一部分是对大于0的值设置格式：
[蓝色]↑0.0%
表示字体颜色为蓝色，显示↑，百分数保留一位小数位。

第二部分是对小于0的值设置格式：
[红色]↓0.0%
表示字体颜色为红色，显示↓，百分数保留一位小数位。

第三部分是对等于0的值设置格式：0.0%
表示百分数保留一位小数位。
注意，自定义格式中的颜色必须使用Excel内置的标准颜色，不要去尝试使用土豪金哈～
自定义格式代码中也可以写成[颜色n]的形式，其中的N是1~56的数字，各种数字对应的颜色如下图所示。

有小伙伴说了：
怎么输入上涨或者下降的箭头啊，网上查了半天都没有找到呢？
其实很简单哈：
依次点击【插入】→【符号】

在弹出的【符号】对话框中，点击【子集】右侧的下拉按钮，选择【箭头】，这样就可以看到不同方向的箭头啦。

插入箭头后复制下来，就可以在自定义格式中使用了。
另外，某些输入法也可以打出箭头形状，比如搜狗拼音输入 yjt（右箭头的拼音首字母），就可以得到一个 → 。

1、按部门合并单元格

按照某一列中的项目来合并单元格，只要在【开始】→【合并居中】下，选择【合并相同单元格】即可。

 

2、填充合并单元格

对于已经合并的单元格，也可以一键快速拆分并填充，使其恢复到未合并之前的状态。

 

3、一键开启聚光灯

点击右下角的【阅读模式】按钮，就可以一键开启聚光灯功能，查阅数据比较多的表格时，这个功能真是很方便。

 

4、拒绝录入重复值

有事秘书干，没事看看书。一位不花钱的小秘书，不用白不用哦。

 

5、制作下拉菜单

简单快捷的下拉菜单，原来如此简单。

 

6、金额大写

财务人员的福音，金额大写再也不用找那些巨长的公式了，设置单元格格式就OK。

 

7、个税公式

计算个人所得税，公式已经为你准备好了：

 

8、合并工作表

合并多个工作表、合并多个工作簿，从此再也不头疼。

 

9、按厘米设置行高列宽

对于一些有特殊要求的表格，可以按指定的单位来设置行高列宽。毫米、厘米、英寸，都不在话下。

 

10、提取两表重复项

两表数据核对是日常工作中经常遇到的问题，使用数据对比功能，可以很方便的提取出两表中的不同项或是共同项。

数据有效性不仅能够对单元格的输入数据进行条件限制，还可以在单元格中创建下拉列表菜单方便用户选择输入。今天和大家分享一组数据有效性的应用技巧，希望能对你有所帮助。

普通青年这样用：

步骤简要说明：
选中区域，设置数据验证，允许条件选择序列，输入要在下拉菜单中显示的内容：
男,女
注意不同选项要使用半角的逗号隔开。

文艺青年这样用：

步骤简要说明：
选中数据区域，设置数据验证，在【输入信息】选项卡下输入提示内容。

强迫症青年这样用：

步骤简要说明：
选中数据区域，设置数据验证，自定义公式为：
=COUNTA(E$3:E3)=ROW(A1)
注意：公式中的E3是选中数据区域的首个单元格。

走你青年这样用：

步骤简要说明：
选中数据区域，设置数据验证，自定义公式为：
=COUNTIF(E:E,E3)=1
注意：公式中的E3是实际选中数据区域的首个单元格。

牛B青年这样用：

步骤简要说明：
任意单元格（如H3）输入公式=NOW()
选中数据区域，设置数据验证，序列来源为：
=H3
注意：要预先设置所选数据区域的数字格式为：H:MM:SS

不同青年的跨工作表求和，有点意思。