Excel教程

1.把度分秒格式的经度值转换为对应的十进制度数

=IF(ISBLANK(I1), "", IF(ISNUMBER(I1), I1, IF(ISNUMBER(FIND("°", I1)), VALUE(LEFT(I1, FIND("°", I1)-1)) + VALUE(MID(I1, FIND("°", I1)+1, FIND("′", I1)-FIND("°", I1)-1))/60 + VALUE(MID(I1, FIND("′", I1)+1, FIND("″", I1)-FIND("′", I1)-1))/3600, I1)))

I1代表需要转换第I列的第一个单元格。

2.冻结首行或多行

只需要选中首行，点击「开始」选项卡-「冻结窗格」，选择「冻结首行」。

这样向下滚动表格的时候首行就会被固定住了。

假如我们想要固定前四列，只需要选中前四列。（行亦如此）

点击「冻结窗格」，选择「冻结至第D列」，这样向右滚动表格的时候前四列就会被固定住了。

3.填充柄填充法

若要在此列都输入”WPS学院“，双击填充柄搞定！

4.智能填充 Ctrl+E实用填充快捷键

提取字符

如果要把所有人的生日提取出来，我们先在第一个人生日列输入其正确生日。

然后依次点击「开始」选项卡 - 「填充」按钮 - 「智能填充」按钮即可。

它的快捷键为「Ctrl+E」。

5.如何在表格中 使用通配符

￭以此表格为例，问号（?）代表任意单个字符，即有多少个？就匹配多少个字符。

若我们想查看以“可乐”开头的四个字符。

使用查找快捷键Ctrl+F，此时弹出查找对话框，在查找内容中输入“可乐？？”。

点击「选项」，勾选「单元格匹配」，结果我们可以看到为“可乐雪碧”。

￭星号（*）代表任意多个字符，包括0个字符，若我们想查看以“可”开头的字符。

使用查找快捷键Ctrl+F，此时弹出查找对话框，在查找内容中输入“可*”。

点击「选项」，勾选「单元格匹配」，结果我们可以看到为“可乐酒”、“可乐雪碧”、“可乐鸡尾酒”。

波形符（~）代表波浪符右侧的通配符转换为普通字符（即不是作为通配符使用）。

以“~”为例，将转化为普通字符*，使用查找快捷键Ctrl+F。

此时弹出查找对话框，在查找内容中输入“~*”。

结果我们可以看到为“可口可乐”、“柠檬可乐”

6.文字按首字母排序

如果需要按照学生名单的，姓氏首字母A到Z排序该表格，应该如何操作？

单击「确定」按钮，也可以选择「自定义排序」，框选需要排序的数据，单击「排序」下拉选择「自定义排序」，在弹出的自定义排序编辑框中，主要关键字选择C列排序依据选择数值次序选择升序，也可以单击「选项」按钮，可以选择方向和方式两个板块，选择好后单击「确定」按钮即可。

7.巧用重复项 快速去重

▪第一步：选中表格，点击「数据」选项卡下「重复项」的下拉菜单，点击设置重复高亮项，此时，重复的数据已被颜色填充。

▪第二步：再次选择「重复项」下拉菜单中的「删除重复项」按钮，在弹出的「删除重复项」对话框中选择产品名称，点击「删除重复项」按钮即可删除。

8.超快捷的数据核对法 找差异项

￭ 如何快速核对这两列数据是否相同？

使用Ctrl+A全选表格

Ctrl+G打开定位，选择-行内容差异单元格，两列中的差异项马上找出来啦！

9.快速分列 数据秒整理

￭ 首先CTRL+A全选表格内容。

点击“数据”-“分列”，选择分隔符号分列，点击下一步，选择分隔符号。

需要注意的是，此处是默认选项逗号，是半角输入法下的逗号，就是英文字符中的逗号。

如果录入数据时，使用的是全角逗号，也叫中文逗号，要勾选“其他”输入中文逗号。

点击完成，数据马上分列了。

10.巧用条件格式 让数据更直观

￭以此表格为例，假如我们想筛选数量大于100并且标记颜色。

选中数量区域，点击“开始”-“条件格式”-“突出显示单元格规则”，在弹窗中可以选择大于、小于、介于、等于等等规则。

点击大于，在条件中输入100设置为浅红色填充深红色。

这样就能筛选出数量大于100并且标记为红色了。

我们也可以筛选数量前十项并标记颜色，点击项目选取规则。

在弹窗中我们可见有前10、后10、前10%、后10%等等，选择前10项。

在对话中将10设置为浅红色填充深红色，点击确定，就可以筛选数量前十项并标记红色了。

11.函数基础知识 相对绝对混合引用

引用符号在哪里就表示固定哪里。

D2 表示没有固定，这是刚才我们讲到的相对引用。

$D$2 行号列标前都添加了符号，表示行和列都被固定了，这是绝对引用。

D$2 符号只添加在行号前，表示只固定了行。

$D2 符号只添加在列号前，表示只固定了列。

这两种情况就是混合引用。

12.常用求和函数 SUM函数

计算多个单元格区域之和

▪以此表格为例，若我们想计算小红和小丽的6月总销量。

单击「公式」选项卡-「插入函数」按钮，找到SUM函数。

在「数值1」中输入B2:G2，在「数值2」中输入B3:G3。

点击确定，即可计算出小红和小丽的6月总销量。

13.实用统计函数 实现高效统计

COUNT 、COUNTIF 是使用率很高的两个表格函数。

需要注意的是：
COUNT只能统计值为数值型数字的单元格个数，文字和其他格式的都不能统计。

￭ 那么要统计符合得分大于40的学生人数，该如何计算呢？答案是使用COUNTIF。

COUNTIF是按一定条件统计单元格个数。

输入=COUNTIF（）

区域输入数据区域，条件输入大于40，记得加上英文双引号。

这个公式的意思是在这个数据区域中，统计大于40的数据个数。

14.实用文本函数 高效整理数据

本期和大家介绍三个文本函数——RIGHT、LEFT、MID函数。
RIGHT函数是从右往左计算字符个数来截取字符。
￭ 例如要截取手机号码后4位，输入=RIGHT（）。

字符串填入手机号码的数据区域，字符个数填入4。

这个公式表示截取这个数据区域的后4位字符，下拉填充柄复制公式 批量提取完成。

其他函数一样

15.制作倒数日历 实时日期时间表

￭ 若要在表格中快速插入当前日期，输入=TODAY ( ) 。

按回车键 可以输入当前日期。

每次表格重新打开，都会刷新当前日期，也可按F9刷新。

￭ 若要输入当前日期和时间，输入=NOW( ) ，按回车即可。

每次表格重新打开，都会刷新当前时间，也可按F9刷新。
同上

输入=DATEDIF ( )

填入开始日期、终止日期、比较单位可选择三项：Y代表年，M代表月，D代表天。

比较单位输入D，记得加上英文双引号。

16.工作中常用的三个函数 VLOOKUP/IF/SUMIF

VLOOKUP函数是一个查找函数，可以找到指定区域内的值。

它的语法结构是=VLOOKUP(查找值,数据表,列序数,匹配条件)
￭ 例如查找“张小敬”选择题得分。

查找值点击选中“张小敬”，数据表是整个表格区域，列序数是成绩所在的列序数。

匹配条件，精确查找为FALSE，模糊查找为TRUE，填入FALSE

￭ 最后是一个关键步骤，选中数据表区域，按下F4添加绝对引用。

双击填充柄复制公式，快速查找匹配数据啦！

IF函数是一个判断函数，可以判断值是否满足给定条件。

它的语法结构是=IF(测试条件,真值,假值)

￭ 例如判断成绩是否合格，测试条件为成绩>=60。

真值填入合格，假值填入不合格，记得加上英文双引号 合格 不合格。

这个公式表示当满足测试条件时，显示合格，不满足显示不合格。

SUMIF函数是一个求和函数，可以根据条件来求和。

它的语法结构是=SUMIF（区域,条件,求和区域）。
￭ 例如求“张小敬”获得的总勋章数量。

区域是我们找“张小敬”这个条件的区域，填入姓名列。

条件是我们定义的条件，求的是张小敬的勋章数，条件为张小敬。

求和区域填入勋章列。最后选中区域、求和区域，按下按下F4添加绝对引用。

这个公式的意思是在姓名列找到“张小敬”，进行勋章求和。

双击填充柄复制公式即可快速批量计算。

详细请查看WPS学院

17.EXCEL取消科学计数法

全选右击鼠标，选择自定义，在类型中输入0，点击确定就好了。