Excel Home
全球知名的Excel资源网站之一

Excel也有触摸屏!当光标滑过不同商品名称,图表就会自动变化

ExcelHome阅读(45)

今天分享一个图表技巧,先看效果:

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

步骤一:准备数据源

 

步骤二:输入代码

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啦!

快速制作统一样式的图表

ExcelHome阅读(94)

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

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

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

1、图片设置

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

2、图片另存

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

3、图片套用

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

​Excel中的数据类型,一次搞明白

ExcelHome阅读(123)

数据还分类型吗?
对啊,就像有些人是内分泌失调型,有些人是老年痴呆型,还有些人是闲的没事找抽型,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个最小值,那肯定是计算不出来的。

简单,却能解决大部分问题的八个函数公式

ExcelHome阅读(141)

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函数返回指定区域中对应位置的内容。可以实现上下左右任意方向的查询。

一个公式,快速生成工资条

ExcelHome阅读(150)

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

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

步骤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个元素的内容。
其他以此类推。

快速提取文件夹内的文件名

ExcelHome阅读(180)

如下图所示,在“测试”文件夹内,包含有两个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

用自定义格式展示销售增减,特别棒!

ExcelHome阅读(196)

有这样一组销售数据,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(右箭头的拼音首字母),就可以得到一个 → 。

WPS表格中的这些实用功能,太赞了

ExcelHome阅读(212)

1、按部门合并单元格

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

 

2、填充合并单元格

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

 

3、一键开启聚光灯

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

 

4、拒绝录入重复值

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

 

5、制作下拉菜单

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

 

6、金额大写

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

 

7、个税公式

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

 

8、合并工作表

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

 

9、按厘米设置行高列宽

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

 

10、提取两表重复项

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

一组Excel数据有效性技巧,新手必备

ExcelHome阅读(543)

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

普通青年这样用:

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

文艺青年这样用:

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

强迫症青年这样用:

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

走你青年这样用:

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

牛B青年这样用:

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

关于我们联系我们
本站特聘法律顾问:李志群律师   沪ICP备11019229号-2

沪公网安备 31011702000001号

征信