第1章 换个角度玩 Excel

• 就研究两件事：
  一，设计一个标准、正确的源数据表；
  二，“变”出N个分类汇总表。
  好的源数据表是一切表格工作的基础。
  使用 Excel 的最终目的，是为了得到各式各样用于决策的分类汇总表。

• 源数据表只应有一张，且只应是一维数据格式。

• 标题行代表了每列数据的属性，是筛选和排序的字段依据。
  不要用标题占用工作表首行。
  两种记录标题的方式：命名工作簿与命名工作表。

• 源数据的录入过程，必须与工作顺序保持一致。

• 整行或整列移动：选中整行或整列，鼠标移至边缘出现十字箭头，按住 Shift 键可互换，不按可替换。

• Excel 是依据行和列的连续位置识别数据之间的关联性。对于源数据表，保持数据之间的连续性非常重要。

• 删除空白列：复制所有数据，“选择性粘贴”时勾选“转置”，筛选“空白”并删除，再次“转置”。

• 不要在源数据表中添加合计行。

• 不要在源数据表中添加对识别数据属性没有帮助的标题行。
  但凡是同一种属性的数据，都应该记录在同一列。
  对于源数据表中的明细数据，只要有任何一个属性不同，都应该分别记录。

• 单元格批量录入：选定多个单元格，输入内容，Ctrl+Enter。

• “合并及居中”，仅限于需要打印的表单，在源数据表中全面禁止使用。

• 在设计表格时，数据属性的完整性是第一考虑因素。

• 阿拉伯数字的整数可以通过设置单元格格式显示为中文大写数字。

• 同类型的数据不要分开记录，保证源数据表的数据完整性和连贯性。

• 批注能用，但是不要贪多。如果需要为数据添加新的属性或说明，应该另起一列。

• 不要在源数据表的单元格里记录复合属性。
  不要在单元格里出现符号、短语、句子、中文数值……不可用于筛选、排序等等功能的元素。

• 切开单元格：“数据”的“分列”功能，可以按分隔符，也可以按固定宽度。
  合并数据到同一单元格：=B1&C1。

• 只要源数据的字段足够多，汇总表的角度和层级就可以无限变换。

• “分类汇总”功能是在源数据表里使用的，不是“变”表利器，使用还有前提条件，需要更多的操作，不用也罢。

• 系统数据有三大部分：配置参数、源数据、汇总报表。
  一个完整的工作簿只有三张工作表：参数表、源数据表、分类汇总表。

• 条件格式：选中数据，调用“条件格式”。

• 从信息管理的角度，企业系统更多扮演的是规范前端——信息获取的角色，而在后端——对信息的个性化处理上，无论是便捷性，还是灵活性，Excel 都更占上风。

• 数据透视表正是搞定分类汇总表的专家。

• 调用数据透视表向导：Alt+D，Alt+P，Alt+F。

• 拆分汇总表：选中透视表，将拆分根据的项拖入“报表筛选”。

• 分类多的字段尽量作为行字段，少的作为列字段，不要让汇总表“躺着”，而是“站起来”。

• 按月统计，可以通过 month 函数提取日期中的月份，也可以在填写源数据的时候手工添加。

• 做汇总表时，两个列字段是极限，一个列字段是标准。其余想要汇总的字段，按顺序添加在行字段。

• 按需求设置字段及为字段排序：求，不同省份、不同城市、不同行业在不同机构类型的主营业收入总和。
  一级行字段：所在省份。
  二级行字段：所在地。
  三级行字段：行业。
  一级列字段：主营业务收入。
  汇总方式：求和。

• 对于源数据表，有一个日期列就足够了。在“选项”卡里有“将字段分组”，可以设置按年/季度/月/日/时/分/秒（可多选）汇总。

• 正确的日期录入格式：xxxx-xx-xx，或xxxx/xx/xx。
  检验方法：将单元格格式设置为常规，如果日期变成了一组数字，就是正确的。
  快捷键输入当前日期：Ctrl+; 。
  快捷键输入当前时间：Ctrl+Shift+; 。

• 数据透视表提供的查看明细功能非常简单，想看什么数据的明细，就双击它。
  未经处理的通过透视表生成的汇总表，不可以随意传播。不需要明细时，用“选择性粘贴”把汇总表“粘贴为数值”。

• 虽然一份源数据表可以新建多张汇总表，但每次只能刷新一张汇总表。批量刷新需借助二次开发工具 VBA。逐个刷新未必是件坏事，建议仔细观察每一份汇总表的数据变化。

• 预约空白源数据区域，在该区域新增源数据，自动添加进汇总表。

• 面对大范围的源数据变动，做好备份是有必要的。

• 透视法去重：先将一列数据调用数据透视表功能，把它设为行字段，再设为数据项即可。

• 同一字段被多次添加，在汇总表中并列显示汇总数时，不要设置列字段。

• 数据透视表可以复制，省去了的重复调用的麻烦。

• 如果企业有系统，必须先了解系统能提供什么数据，才能确定表格中应该出现哪些字段。
  了解数据量的多少，是要据此考虑该工作是否适合用 Excel 完成。2003版最大65536行，2007版1048576行，超出了就交给更专业的数据处理软件。
  源数据完全靠手工录入，数据量越大，字段就应该越少。

• 选中 B：D 列所有非空单元格：选中 B1：D1，同时按住 Ctrl+Shift 键，按方向键“下”。
  Ctrl+方向键：跳转到连续数据区域的边缘。
  Shift+方向键：连续选中。

• 快捷键调用一级菜单：Alt+字母。
  调用二、三级菜单：仅字母。

• 灵活运用“数据有效性”功能，能最大限度的保证数据录入的准确性。注意三点：
  一，对于已有数据的单元格，设置数据有效性后不会自动判定。
  二，不是只能下拉列表选择，输入也可以。
  三，“序列”来源不能直接引用跨工作表的数据区域，可以借助“名称”。

• 保护工作表：选中允许录入的单元格区域，设置单元格格式，取消“保护”标签中的“锁定”；调用“保护工作表”功能，取消“选定锁定单元格”。

• 输入“=vlookup”（注意无括号）后按 Ctrl+A，就能打开该函数的参数面板。
  使用公示链接调用关联数据：=if(len(E2,'Sheet2'!D, column(B1),0))。含义为如果源数据表的 E2 不为空，则在 Sheet2 的 A 列中寻找匹配项，如找到则提取 Sheet2 中 B 列到 D 列的相关数据。

• 手工录入、复制粘贴、公示链接的数据区域要用不同的填充色区分，以告知使用者。
  需要录入和经常查看的单元格字体稍大，公示链接生成的明细数据字体可以调小。
  以不同的工作表标签颜色区分汇总表、源数据表及参数表。

• 常用的长文本在“自动更正选项”里设置。

• 自动求和：F5 调用“定位”，“定位条件”选择“空值”，Alt+= 自动求和。

• 选中新增的源数据，拖入图表，即可完成添加。
  可以通过修改图表来修改源数据。

• 小于六个对比数，柱形图的表现力比折线图更好。
  概念图由“条形图”而来，需要在源数据中刻意制造负数。

• 粘贴图表到 Word 和 PPT 里时，使用“选择性粘贴”，谨防数据泄露。

• 工作表切换快捷键：Ctrl+PgDn，Ctrl+PgUp。

• 源数据表只允许有一个主角，需要认真分析工作流程和目的，以便准确找到那个关键的角色。

• 函数参数为文本时，必须包含于半角的双引号内。

• & 用于连接文本。
  $ 用于锁定引用。F4 键用于切换引用类型。

• Excel 排序规则：数值<文本<逻辑值。
  注意：单元格显示为数字，但是左上角有小三角，说明该数字为文本。减负运算（=－－C2）可一劳永逸的将文本型数字变成数值。
  单列数据转换：Alt+D，E，F。

• 公式向下复制：鼠标移至选中边框的右下角变成黑色小十字，双击鼠标左键。
  注意：如果左侧相邻列有空单元格，复制动作将停止。

• 公式相关快捷键：
  F2，使单元格进入编辑状态。
  F9，查看引用数据（选中），查看公式中的部分结果（选中），触发所有公式。
  Ctrl+`，在显示公式结果与显示公式之间切换。

• 所有单元格设置为文本，公式无效，改为常规后再用“=”替换“=”，此时 F9 无效。

• 用 D2 在 D2：D23 中进行降序排名：=rank(D2, 2:23, 0)。

• 确保源数据不空格、跳行：选中待录入区域，调出“数据有效性”设置面板，在“允许”中选择“自定义”，在“公式”中输入 =len(A1)<>0（A2 为当前激活单元格），取消勾选“忽略空值”，即只有上一个单元格有数据时才允许录入。
  左侧也要有数据：=and(len(A2)<>0, len(B1)<>0)。

• 当一组单元格用一个名称代替时，Excel 允许数据有效性跨表引用。

• 定义名称：Ctrl+F3。
  在数据有效性中使用名称，或者在表格中粘贴名称列表：F3。
  批量定义名称：Ctrl+Shift+F3。
  选择不规则的区域：F5，定位条件为“常量”。

• 非连续数据区的选择：如，在名称框中输入 A1:B3, C4:D8。
  单元格直达：如，在名称框中输入 K27834。

• 自动筛选：Alt+D，F，F。
  选中筛选后的可见单元格：Alt+; 。
  再复制粘贴即可分离数据。
  这种方法比较麻烦，建议用数据透视表，在双击查看明细，自动生成新的工作表。

• 看透数据的下一层，用 indirect 函数。类似 Linux 里的变量前加 $。

• 按条件筛选数据求和：sumif 函数。
  关键字筛选：*关键字*。

• 多条件求和：sumifs 函数。

• 最快的选择性粘贴：选中区域，鼠标移至区域边缘变为十字箭头，点击右键拖动。

• 引用除当前工作表以外的该工作簿中的其它所有工作表：'*'!。

• 单元格内换行：Alt+Enter。

• 向右切换单元格：Tab。
  向左切换单元格：Shift+Tab。
  向下切换单元格：Enter。
  向上切换单元格：Shift+Enter。

• 复制工作表：按住 Ctrl 拖动。

• 日期函数 datadif(起始日期, 终止日期, 计算方式)。计算方式包括：
  y，相差年数，不足一年不计；
  m，相差月数，不足一月不计；
  d，相差天数，与小时无关；
  ym，与年无关的相差月数；
  yd，与年无关的相差天数；
  md，与月无关的相差天数，以终止日期前一个月的总天数为计算标准。