第 1 章 Excel基础

1.1 Excel的前世今生

1.1.1 你知道的VBA(Visual Basic Application)

是什么在背后持续推动和维系着Excel的发展、完善和壮大？大多数情况下，人们只是偶尔接触Excel，并不用关心这个问题。对于很多Excel轻量化使用的人，可能偶尔会在头脑中冒出过这样的问题。但对于Excel重度使用者（这类人群的比例或许也并不如你认为的那么高），这就是一个不能逃避的重要问题。

因为发展历史的原因，你大概听说过Excel背后的推手是VBA(Visual Basic Application)。VBA是一种VB(Visual Basic)的宏语言，它应该是最早为Office提供定制化的一种编程解决方案。VBA是VB的一个子集，而其中的VBA for Excel就是专门为Excel开发的一套定制化编程语言。简言之，我们可以认为Excel大厦是用VBA建造的，这对于认识Excel似乎基本就足够了。

Excel用户经常接触到“宏(Macro)”这个概念，而且还知道“宏(Macro)”也是基于VBA构造的。在Excel的世界里，“宏(Macro)”神通广大，用途广泛，它就像游戏玩家的神奇加速靴，能显著提高Excel工作效率；也好似功夫高手的独门兵器，能对Excel流程进行个性定制，哪怕冷僻繁重的数据工作也总能绝地反击。

人们可以想象到“宏(Macro)”可大展拳脚的地方包括（可能不全）：

  • （1）劳动强度大，重复性高的工作，比如每月报，周报等。
  • （2）对Excel本身一些功能的细节控制与完善，如数据透视表（Pivot Table）等。
  • （3）和其它系统的交互，如数据库系统 。
  • （4）……

人们认同VBA对Excel实在居功至伟，但是它的局限性也同样展露无遗：

  • （1）VBA在执行效率方面不如一些编译型解决方案。
  • （2）VBA缺少一些常用的类型，调试起来不方便。
  • （3）VBA是一种脚本类型的解释型的语言，如源代码容易被破解，安全性较差。
  • （4）……

1.1.2 你不知道的其他

.NET平台：2000年微软发布了.NET平台战略之后，推出了一系列运行在.NET上新语言如C#和VB.NET。在这种情况下，开发者能够使用.NET平台上的语言来开发Office。从Office 2003开始，微软推出了基于.NET 平台的VSTO开发方案。

Office App：随着Office 2013 和Office 365的推出，Office更加注重网络化功能，因此引入了新的编程方式——Apps for Office。和传统的运行宏（VBA）和安装插件（VSTO）不同，新版的Office中用户可以到微软的Office App Store中去下载、安装应用程序。Office Apps使用熟悉的Web技术开发如JavaScript，非常容易聚合各种各样的资源。

1.2 快速了解Excel

1.2.1 界面和菜单

Excel界面和功能菜单

图 1.1: Excel界面和功能菜单

熟悉基本术语和界面。

  • 选项卡
  • 功能区
  • 公式显示
  • 列标题/行标题
  • 单元格
  • 工作表
  • 状态栏

功能区最小化和展开功能。

创建新的工作表。

1.2.2 函数使用

利用菜单进行函数操作：

  • Step1：鼠标点击空白单元格。

  • Step2：鼠标点击菜单“公式” ⇒ “插入函数”。

图1.2给出了函数操作的菜单栏。

函数操作菜单

图 1.2: 函数操作菜单

图1.3给出了函数操作的引导步骤。

函数操作引导

图 1.3: 函数操作引导

1.2.3 管理数据格式

1.2.3.1 数值

（1）数值取整

菜单操作：点击菜单“数字”⇒ “增加小数位数”/“减少小数位数”。

函数操作：采用函数=Round()。

（2）数值分隔

菜单操作：点击菜单“数字” ⇒ “千位分隔样式”。

菜单操作：点击菜单“数字” ⇒ “数值” ⇒ 勾选“使用千位分隔符”。

（3）数值重复检查

菜单操作：点击菜单“条件格式” ⇒ “突出显示单元格规则” ⇒ “重复值”。

（4）数值条件着色

菜单操作：点击菜单“条件格式” ⇒ “突出显示单元格规则” ⇒设置着色条件。

1.2.3.2 日期

日期数据的操作

图 1.4: 日期数据的操作

图1.4展示了日期数据的相关操作。

（1）选择日期格式

菜单操作：点击菜单“数字” ⇒ “数字格式” ⇒ “长日期”/“短日期”。

（2）自动填充日期

菜单操作：单元格填写连续日期 ⇒ 单元格下拉操作。

（3）计算时期间隔

函数操作1：单元格相减，如采用函数“= latest date – past date”。

函数操作2：采用函数=DATEDIF(起始日期，终结日期，参数)。

（4）提取年、月、日

函数操作：如单元格“2016/11/7”采用函数公式=YEAR()获取年“2016”，采用函数公式=MONTH()获取月“11”，采用函数公式=DAY()获取日“7”。

1.2.3.3 货币

货币数据的一个常见操作就是确定货币单位格式。

菜单操作：点击菜单“数字” ⇒“ 货币” ⇒ 选择“货币符号(国家/地区)”和“小数位数”。

1.2.3.4 文本

（1）转换英文大小写

函数操作：全部小写，采用函数=LOWER()。

函数操作：全部大写，采用函数=UPPER()。

函数操作：首字母大写，采用函数=PROPER()。

（2）提取部分字符

函数操作：如提取姓名“张三丰”的姓“张”，采用函数=LEFT(,1)。

函数操作：如提取姓名“张三丰”的名“三丰”，采用函数=RIGHT(,2)。

函数操作：如提取姓名足球明星“罗纳尔多·路易斯·纳萨里奥·德·利马”全名中的“路易斯”，采用函数=MID(,6,3)。

（3）文本条件着色

菜单操作：点击菜单“条件格式” ⇒ “突出显示单元格规则” ⇒设置着色条件。

1.2.4 管理数据视窗

1.2.4.1 管理窗口

（1）冻结首行或首列

菜单操作：点击菜单“视图”⇒“窗口”⇒“冻结窗格”⇒“冻结首行”。

（2）拆分窗格

菜单操作：点击菜单“视图”⇒“拆分”。

（3）冻结指定表格区域

菜单操作： - Step1：点击菜单“视图”⇒“拆分”，拉选指定的表格区域。

  • Step2：点击菜单“视图”⇒“窗口”⇒“冻结拆分窗格”。

（4）隐藏/取消隐藏某些行或列

菜单操作：

  • Step1：鼠标左键选择要隐藏的行（多个行可按“Ctrl”再点选行）。

  • Step2：点击鼠标右键，选择“隐藏”。

1.2.4.2 表单排序

（1）排序：有标题行排序

菜单操作：

  • Step1：鼠标拉选表格区域，确保标题行在第一行。

  • Step2：点击菜单“开始”⇒“编辑”⇒“排序和筛选”⇒ 选择“升序”/“降序”/“自定义排序”

（2）排序：无标题行排序

菜单操作：

  • Step1：鼠标拉选表格区域。

  • Step2：点击菜单“开始”⇒“编辑”⇒“排序和筛选”⇒ 选择 “自定义排序”。

1.2.4.3 表单筛选

菜单操作：

  • Step1：鼠标拉选标题行，作为筛选标志。

  • Step2：点击菜单“开始”⇒“编辑”⇒“排序和筛选”⇒ 选择“筛选”。

  • Step3：点击筛选标志单元格，勾选筛选标准。

1.3 提升Excel使用效率

1.3.1 表格转置

菜单操作：

  • Step1：鼠标拉选表格区域⇒右键点击“复制”。

  • Step2：点击空白单元格⇒鼠标右键点击“选择性粘贴”⇒点击“转置”。

1.3.2 单元格内容合并

（1）使用连接函数Concatenate

函数操作1：如把“旱区”和“北京”，合并为一个单元格“旱区北京”。使用函数=CONCATENATE(text1, text2)

函数操作2：如把“旱区”和“北京”，合并为一个单元格“旱区-北京”。使用函数=CONCATENATE(text1, “-,” text2)

（2）连接符&

函数操作1：如把“旱区”和“北京”，合并为一个单元格“旱区北京”。使用函数=text1 & text2。

函数操作2：如把“旱区”和“北京”，合并为一个单元格“旱区-北京”。使用函数=text1 & “-” & text2。

1.3.3 Excel快捷操作

（1）单元格自定义格式。我们可以妙用自定义格式，让数据输入轻松惬意，规范准确。

操作方法：设置单元格格式-自定义-根据数据规律写入统一的数据格式

书写规则：

  • 键入#号：数字占位符，一个符号代表一个数字占位。

  • 键入@号：文本占符。

  • 键入" "号：特定内容（内容确定）。

应用场景：输入邮箱、手机号、身份证号等信息

（2）快速选定单元格区域

快速操作方法：

  • 选定当前单元格和A1单元格所确定的矩形区域：Ctrl + Shift + Home。

  • 名称框中直接输入区域范围选取。

  • 选定非连续区域：区域A + Ctrl + 区域B。

1.3.4 快捷键组合

（1）用Ctrl + PgDn / PgUp转换工作表。

（2）用 Ctrl + 方向键快速转换单元格。当然，你也可以只按方向键，然后一格一格地移。但如果你想快速转换单元格，在按方向键之前长按Ctrl，瞬间能达到“键步如飞”的效果。

（3）巧用Shift键选择数据。用Ctrl + Shift 方向键能够纵横跨选单元格，从而选中你想要的区域。

（4）双击自动填充数据。要用函数自动填充同列数据，不要去长按鼠标然后拼命往下拉到最后一栏——如果你的表格有几百行呢?双击算好单元格的右下小十字，轻松填完整列数据。

（5）让这3个快捷键迅速统一数据格式。

键盘快捷操作：

  • 要让数据呈现两位小数？ 用Ctrl + Shift。

  • 单位要美元？ 用 Ctrl + Shift + $。

  • 要百分比？ 用 Ctrl + Shift + %。

鼠标点那么多次不累嘛?这三个快捷键光速实现格式统一，而且后面两个应该特别好记。

（6）用键盘F4锁定单元格。在Excel里根据函数填充数据的时候，有时候你希望你引用的单元格下拉时随之变化，也有时候你并不这么想。当你要“绝对引用”——也就是锁定单元格的时候，你必须在行列前加$符号。想手动去打这些美元符号？简直是疯了…这要浪费多少时间你知道吗？其实有一个简单的技巧，就是在你选定单元格之后，按F4键输入美元符号并锁定；如果你继续按F4，则会向后挨个循环：锁定单元格、锁定数字、锁定大写字母、解除锁定。