导图社区 Excel之光
- 726
- 123
- 16
- 举报
Excel之光
结合日常工作梳理部分EXCEL操作，提升工作效率
编辑于2020-04-14 21:08:38
- 相似推荐
- 大纲
《Excel之光》 读书笔记
第一章 搞定你的小问题
1.1 快速分离数字、文本
1.1.1 分列 选取数据-单击“数据”选项卡-“分列”-分隔符号（根据情况也可以选固定长度）-单击“下一步”-确定分隔符-确定表格格式-完成
1.1.2 快速填充 复制想要提取的内容到目标单元格-将光标移动到填充单元格-单击“快速填充”（快捷键Ctrl+E）-完成提取（如果单次不成功，可以再手动复制一格后再次快速填充）
1.2 号码中的0怎么消失了
1.2.1 单元格设置为文本格式
1.2.2 设置自定格式，根据号码尾数输入相同尾数的0，当输入数字不足时会自动在前面补足0
知识补充：第一种方法将内容转换为了文本，不能用于数字的计算；第二种不受影响。文本格式默认左对齐，数字格式默认右对齐
1.3 完整输入身份证号码
快速输入文本 输入内容前加上一个单引号（英文输入法或半角状态下）
知识补充：Excel默认数值显示11位，超过将变成科学计数法；通常单元格默认为常规格式，输入数字前面带0会自动忽略
1.4 数字不能计算
1.4.1 常规法：将单元格格式调整为“数字”，表格内容转变为数值
1.4.2 直接修改法：选中数据后，点击左侧“黄色叹号”，选择转换为数字
1.4.3 选择性粘贴法：选择任意一个单元格-复制-选择需要转换的文本区域-右击单元格-选择性粘贴-选择“加”或“减”-确定即可。 原理：通过对区域文本进行一次数值不变的运算（加减0或者乘1都可），强制变成数字格式。
1.4.4 分列法：通过分列操作最后一步数据类型选择完成转换
1.5 快速输入性别、单位
1.5.1 自定义格式法：右击性别所在列-设置单元格格式-数字-自定义-输入[=1]"男";[=0]"女";"其他"（效果是单元格输入1自动显示男，输入0显示女，其他数字都显示其他）
1.5.2 数据验证法：数据选项卡-数据验证-设置-允许下拉列表中选择序列-来源中输入规定的内容，中间用,分开（效果是直接下拉选取即可）
1.5.3 用自定义格式批量输入单位：设置单元格格式-数字-自定义-格式改成0.0000"cm"(效果是输入的数字均保留4位小数，后续会加上单位cm，双引号均为英文或者半角情况下的)
1.6 纠正错误日期格式
1.6.1 查找替代法：通过替代工具将"."替代为"/"，使内容转换为Excel认可的日期格式
1.6.2 分列法:20200101这类日期，可以采用分列法，前期都不用改，只要在最后一步选择格式的时候选择日期即可
1.6.3 日期变文本：将常规日期格式变成文本，且保留日期的显示形式，用=TEXT（A，B），其中A为需要修改的单元格，B为想要转换的格式
1.7 固定表头
1.7.1 冻结窗格：视图-冻结窗格-冻结首行
1.7.2 冻结拆分窗格：选中某个单元格后，这个单元格上面的行和左侧的列会被固定
1.8 制作下拉菜单
第一步：给原始表格定义名称，选取表格区域-单击公式-根据所选内容创建-只勾选首行-单击确定。效果是用首行数据关联下面行的数据，汇总时自动跳出来
第二步：制作一级下拉菜单，对首行内容进行下拉菜单制作，用数据验证方法
第三步：制作二级下拉菜单，其他不变，来源文本框输入函数=indirect（$单元格），将两个单元格联系起来，同时因为首行数据包含二级数据，所以实现对应关系
1.9 批量制作工资条
目标：第一行为说明行，第二行为员工工资信息，确保每一个人都有说明行，即每隔一个人插入一行说明
第一步：在表格右侧插入辅助列，按数字填充
第二步：复制与名字相同数量的说明行，同样添加辅助列并填充，且在同一列上
第三步：选取辅助列，升序，扩展选定区域，单击排序即可实现
1.10 一键对比数据
1.10.1 两张表对比的数据顺序一致，同时选取两列数据，按快捷键Ctrl+\即可
1.10.2 两张表数据顺序不一致，在辅助列输入公式=COUNTIF（B:B,A2），函数的含义是将A列数据依次和B列数据对比，如果包含在B列中就显示1，没有显示0
1.10.3 大量数据的对比，选择表格1的数据区域进行复制，右击右表数据区域-选择性粘贴-选择“减”-单击确定，如果不为0即有差异。 注意事项：操作前先备份；名字顺序要一致；数据应当能运算
第二章 小函数解决大烦恼
2.1 =MID（截取的字符，左起第几位开始截取，截取的位数）
举例：=MID（C2，7,8），对C2单元格数据进行提取，从数据左边开始第7位开始截取8位数字
常见应用：提取身份证上的出生日期，结合DATE函数实现日期的完整体现
2.2 =DATEIF（起始日期，结束日期，信息的返回类型）
举例：=DATEIF（C2，TADOY(),"Y"），C2为出生日期，TADOY函数提取当前日期，Y返回年数据，Y需要用英文双引号括起来
常见应用：计算员工周岁
2.3 合同到期自动提醒
第一步：选择合同到期日所在的单元格，不要选择标题-单击开始-条件格式-新建规则
第二步：选择“使用公式确定要设置格式的单元格”，在文本框中输入“=C2-TADOY（）<=7”（C2为合同到期日，7为提前提醒天数） DATEIF函数也可以设置合同到期提醒
第三步：设置提醒颜色，单击对话框右下角的“格式”按钮-设置单元格格式-选择填充-在背景色里选择提醒颜色-单击确定
2.4 =COUNTIF(计数区域，计数条件)
举例：=COUNTIF(B2:B20，"大毛")，统计在表格B2:B20范围内“大毛”出现的次数
常见应用：统计出现次数
2.5 =SUM(数据区域)，该函数可以实现跨表计算
2.6 =INDEX（数据区域，行号，列号）
举例：=INDEX（A:B，RANDBETWEEN(1,8),RANDBETWEEN(1,2)），数据区域AB两列，RANDBETWEEN()函数用于随机选取行和列，从而实现数据的随机定位
常见应用：随机抽奖
第三章 制作规范的表格
3.1 搞懂数字格式
数值：常规数值、日期、时间
文本：中文字符
逻辑值：真假
错误值：都以#开头
常量和公式值：公式值是一切由公式、函数生成的数值，其他数值均为常量
3.2 设置数字格式
方法： 1.右击“设置单元格格式” 2.按快捷键Ctrl+1 3.找到“开始”选项卡下的“数字”命令组，单击右下角的小按钮（命令启动器）
特殊格式的录入： 分数：0/整数+空格+分数，例如一又三分之一可以输入“1+空格+1/3” ☒和☑的输入：将字体设置为Wingdings2，输入T可得到☒，输入R可得到☑
3.3 日期时间
3.3.1 简单的日期计算 计算天数间隔：=B2-B1，同样的日期格式可以直接相减 提取年、月、日等：=YEAR（数据单元格），相同用法还有MONTH(月)，DAY(日)，HOUR(时)，MINUTE(分)，SECOND(秒) 组合日期：=DATE（年，月，日），将三个数据组合成日期 输入当前日期和时间：=TODAY(),=NOW()
3.4 自定义格式
应用实例：以万元为单位显示；手机号码分段；“0”显示为“-”；快速输入“√”“×”；文本添加统一前后缀
自定义格式的“四区段规则”
基础占位符介绍
3.5 利用TEXT函数转换格式
=TEXT（数值，格式），转换后均为文本
1、将数值转化为文本 2、转化日期时间格式 3、转换其他特定格式，判断正负 4、特殊判定，判断分数等
3.6 确保输入内容格式正确
限制输入内容； 限制长度； 限制格式和区间； 圈视无效数据； 限制输入空格；
3.7 规范表格的建议
表格字段分类不重叠、不遗漏
尽量少用合并单元格，不利于数据统计分析
减少多余空行，可以用COUNTA函数，筛选出空行后删除
数据格式统一
第四章 数据录入整理的技巧
4.1 选择性粘贴的用法
4.1.1 表格行列互换
4.1.2 将公式转化为数值
4.1.3 批量数值运算
4.1.4 将文本转化为数值
4.1.5 将表格转化为图片
4.2 填充
4.2.1 填充的操作方式：鼠标拖拽；填充命令
4.2.2 连续等差或等比填充：选中第一个单元格输入第一个数字-开始选项卡-编辑组的填充按钮-选择序列对话框
4.2.3 单行变多行：选择单元格-将单元格调整到每个文本的宽度-单击“开始”选项卡下的“填充”-选择“内容重排”-弹出提示，点击确定
4.2.4 快速填充（先手动输入内容，然后使用快速填充功能会自动识别规则进行后续表格数据的操作） 应用1：快速分离数字和文本 应用2：批量添加符号 应用3：大小写转换 应用4
4.3 定位
4.3.1 批量删除空行 第一步：全选表格 第二步：打开“定位”对话框（Ctrl+G或者F5）-单击左下角“定位条件”-选择“空值”-单击确定 第三步：在任一空行上右击-“删除”-整行
4.3.2 批量删图 定位条件-对象-删除
4.3.3 只复制可见区域 选择原表格-定位条件-可见单元格-确定（快捷键：Alt+；）
4.3.4 批量增加空行 第一步：插入辅助列-复制、粘贴一列相邻的文本列-两列形成一行的错位 第二步：选取两列数据到末尾-定位条件-行内容差异单元格-单击确定 第三步：在任意一个错位单元格上右击-插入-选择“整行”-单击确定 第四部：删除辅助列
4.4 查找和替代
4.4.1 批量删除单元格内空行 选取目标数据区域-“替换”功能-将“换行符”替代为空（换行符输入方法：ctrl+Enter）
4.4.2 按单元格颜色查找和替换 第一步：通过条件格式将目标数据所在单元格填充为红色 第二步：“替换”-单击“选项”按钮-单击“格式”下拉按钮-选择“从单元格选择格式”-用“小吸管”选取其中一个目标单元格 第三步：输入替换的内容-单击“全部替换”即可
4.4.3 精准匹配查找和模糊查找 1、精确匹配查找：查找时点击“单元格匹配”，就可以实现精确查找（范围可以将查找的区域扩大到整个工作簿；查找范围可以选择公式、值、批注等等） 2、模糊查找：一般通过通配符“*”和“？”实现（虚伪英文或半角状态下输入），其中“*”可以代替任意个字符，“？”可以代替一个字符 补充：“查找和替换”对话框底部可显示搜索结果，按住Shift键可以批量选择被搜到的单元格。
4.5 排序
4.5.1 多关键字排序 第一步：选择表内任意一个单元格-【数据】-【排序】-【主要关键字】设置好参数 第二步：单击左上角【添加条件】-【次要关键字】-设置好参数-单击【确定】
4.5.2 笔画排序（按笔画数进行排序） 第一步：选择表内任意一个单元格-【数据】-【排序】-【主要关键字】设置好参数-第二步：单击右上角【选项】-选择【笔画排序】-单击【确定】
4.5.3 按字符数排序 第一步：插入辅助列-输入函数=LEN（数据区域）计算字符长度，填充整列 第二步：对辅助列进行排序-删除辅助列
4.5.4 按制定序列排序 第一步：【数据】-【排序】-【主要关键字】选择对应标题-【次序】选择【自定义序列】 第二步：在【自定义】对话框中输入自己指定的序列（注意个格式，无标点）-单击【添加】按钮-单击确定即可
4.6 筛选
4.6.1 基本筛选 按日期时间筛选；按文本特征筛选；按数字筛选（按排名筛选、按平均值筛选）；按颜色筛选；模糊筛选；按所选单元格筛选
4.6.2 多条件筛选
“与”：同时满足多个条件 第一步：列好筛选条件，并列关系的条件应置于同一行，且第一行 第二步：选择表格内任意一个单元格，【数据】-【排序和筛选：高级】-【列表区域】一般默认 第三步：【条件区域】选取原先设置好的条件单元格范围-单击【确定】
“或”：满足多个条件中的任意一个条件 第一步：列好筛选条件，不同条件要置于不同行 第二步：选择表格内任意一个单元格，【数据】-【排序和筛选：高级】-【列表区域】一般默认 第三步：【条件区域】选取原先设置好的条件单元格范围-单击【确定】
4.6.3 筛选重复值 单击【数据】-【排序和筛选：高级】-【高级筛选】-【方式】选择【将筛选结果复制到其他位置】-【列表区域】选择第一张表-【条件区域】选择第二张表-【复制到】选择目标区域-单击确定 筛选结果可在【开始】选项卡找到【全部消除】命令进行清除
4.6.4 隐藏重复值 单击【数据】-【排序和筛选：高级】-【高级筛选】-【列表区域】框选整张表格-勾选【选择不重复记录】-单击确定
4.7 多表合并
4.7.1 合并计算 第一步：选择汇总表中任意一个单元格-【数据】-【合并计算】-【函数】默认为【求和】-鼠标定位于【引用位置】下的方框-选择需要引用的工作表，框选数据区域-单击【添加】按钮，重复上面的步骤，直到将所有数据都添加进去 第二步：勾选【标签】位置的【首行】和【最左列】，单击确定 补充：使用按类别合并计算时，原表格必须包含行或列标题，合并后会缺失第一列的列标题，且合并计算后的表格是不带格式的
4.7.2 同一个工作簿内的多表合并 第一步：选取工作簿，新建空白工作簿-【数据】选项卡-【获取数据】-【自文件】-选择【从工作簿】 第二步：在弹出的【导入数据】对话框中选择工作表所在工作簿，单击【导入】按钮 第三步：导入后弹出【导航器】对话框-勾选【选择多项】-勾选要合并的表格-单击【编辑】或【转换数据】按钮。进入【Power Query】编辑器 第三步：在【Power Query】编辑器中，单击【追加查询】按钮-弹出【追加】对话框 第四步：在【追加】对话框中选择【三个或更多表】-选择需要添加的表-单击【添加】按钮将其添加到右侧-单击【确定】按钮 第五步：在【Power Query】编辑器中，单击【开始】-单击【关闭并上载至】-在弹出的【导入数据】对话框选择默认即可-单击确定 原数据变动后在汇总表单击【数据】选项卡的【全部刷新】即可进行数据的更新
4.7.3 多个工作簿的多表合并 第一步：获取数据路径修改为【从文件夹】，选择表格所在的文件夹，单击确定 第二步：将不必要的信息列删除，只保留“Content”列，方法是选择该列，单击【删除列】-【删除其他列】 第三步：【添加列】-【自定义列】-【新列名】输入“汇总表”-【自定义列公式】输入函数=ExcelWorkbook([Content],true)，单击确定即可添加“汇总表”（=ExcelWorkbook([Content],true)含义是从Content列中提取数据，true表示默认将第一行作为标题生成新表） 第四步：展开自定义列（列右侧的扩展按钮），单击【确定】扩展全部字段 第五步：选择“汇总表.Data”这一列，重复第二步操作，删除其他列；重复第四步操作，展开“汇总表.Data”这一列，单击确定即可 第六步：合并后单击【关闭并上载至】即可
第五章 函数
5.1 函数基础
参数：函数必须的内容
填充：函数高效的基础
运算：函数的分析计算的方式（文本连接运算符&）
补充：$用于绝对引用，很重要，可以直接按F4
5.2 快速求和
5.2.1 快捷键：Alt+=
5.2.2 多区域同时求和： 选择数据区域-按下快捷键Ctrl+G-【定位条件】-【空值】-单击确定选取空白单元格-快速求和（Alt+=）
5.2.3 单条件求和 使用=SUMIF（条件区域,条件,求和区域）函数，条件区域是用于判断的数据所在区域，条件是你需要统计的判断条件，求和区域是你需要求和的数据所在
5.2.4 多条件求和 使用=SUMIFS(求和区域,条件区域1,条件1,条件区域2,条件2,……)
5.2.5 先乘积后求和 =SUMPRODUCT（区域1，区域2，区域3，……）数据区域要一致
5.3 随心提取文本
5.3.1 LEFT函数 =LEFT（文本，提取长度），从左起提取多少个字符
5.3.2 MID函数 =MID（文本，开始提取的位置，提取长度），从文本的第几个字符开始提取多少个字符
5.3.3 RIGHT函数 =RIGHT（文本，提取长度），从右起提取多少个字符
5.4 逻辑函数
5.4.1 基础逻辑函数 =AND（逻辑1，逻辑2，逻辑3，……）当所有条件都满足时才返回TRUE =OR（逻辑1，逻辑2，逻辑3，……）有一个条件满足时就返回TRUE =NOT（逻辑）取反
5.4.2 IF函数 =IF（逻辑，真值，假值）
5.5 轻松输入多重嵌套函数
5.5.1 利用编辑栏，鼠标点击右侧箭头可以拉高，通过Alt+Enter实现换行，条件一清二楚
5.5.2 插入函数工具，即【fx】工具，选择需要输入的函数后点确定就能跳转到对应函数输入界面，有提醒和纠错功能；IF函数多重嵌套时，在输入第三个参数时在左上角选择IF就可以重新开始新的条件输入了
5.6 工作日函数
5.6.1 计算工作日 =NETWORKDAYS(起始日期，结束日期，节假日)，需要提前将计算时间段内的所有法定节假日都列出来 =NETWORLDAYS.INTL(起始日期，结束日期，放假模式，节假日)，可以根据公司放假情况设定参数3，从而适用各类情况（同时也支持自定义放假模式）
5.6.2 推算日期 =WORKDAY(起始日期，间隔日期，节假日)，通过起始日期，对间隔天数和节假日进行累加计算，返回结束日期 =WORLDAYS.INTL(起始日期，间隔日期，放假模式，节假日)，可以根据公司放假情况设定参数3，从而适用各类情况（同时也支持自定义放假模式）
5.7 数据匹配函数
5.7.1 VLOOKUP函数（精确匹配） =VLOOKUP(查找值，查找区域，查找列数，匹配方式) 反向查询：将查找区域改成如下数组公式：IF({1,0},后面的区域1，前面的区域2)
5.7.2 VLOOKUP函数（模糊匹配） =VLOOKUP(查找值，条件区域，查找列数，匹配方式)，通常用于下限值，分数区间这些情况
5.8 锁定位置
5.8.1 INDEX函数 =INDEX（区域/数组，第几行，第几列） 选中整行或整列，输入函数，此时如果函数的第2个或第3个参数为0，同时按下快捷键Ctrl+Shift+Enter，则返回整行或整列
5.8.2 MATCH函数 =MATCH（查找值，查找区域，查找方式），返回指定数值在指定数组区域中的位置 查找方式有0，-1，1三种，0位精确查找，-1查找区域中大于或等于查找值的最接近值，且查找区域必须降序排列，1与-1相反
5.8.3 INDEX和MATCH函数的组合 利用MATCH函数返回行，作为INDEX的参数之一，就能完成匹配，且不受区域方向影响
第六章 数据透视表
6.1 认识透视表
6.1.1 创建数据透视表
6.1.2 数据透视表使用规范 1、标题行必须完整 2、表格格式不能有合并单元格，数字、日期等格式需规范 3、数据刷新，数据区域变化需要手动调整，否则只需要点击【刷新】即可
6.2 数据透视表字段说明
6.2.1 行区域 1、字段不能重复 2、行区域可以加入多个不同字段 3、行区域字段顺序会影响表格结构
6.2.2 列区域 和行区域相同
6.2.3 值区域 1、允许重复字段 2、改变值的汇总方式（值字段设置） 3、改变值的显示方式（值字段设置）
6.2.4 筛选区域 该功能可以将二维表变成三维表格，通过筛选得到不同的数据透视表
6.3 数据透视表美化
6.3.1 套用样式，【设计】栏目
6.3.2 修改布局，【设计】栏目
6.3.3 其他显示方式的修改 1、空单元格补0，鼠标右键-【数据透视表选项】-【布局和格式】中勾选【对于空单元格，显示】-方框内填“0”-单击确定
6.4 四个技巧
6.4.1 一表拆成多表 第一步：将想要拆分的字段拖入筛选区域 第二步：单击【分析】选项卡-【选项】下拉按钮-【显示报表筛选页】-弹出的对话框中默认为筛选区域的字段-单击【确定】按钮
6.4.2 多表合并 第一步：按住Alt+D+P弹出【数据透视表和数据透视图向导】对话框-选择【多重区域计算数据区域】-报表类型选【数据透视表】-单击下一步-选择【自定义页字段】-单击下一步 第二步：选择数据区域-全选表格-单击【添加】按钮 第三步：添加页字段标签-【请先指定要建立在数据透视表中的页字段数目】选择【1】，单击上方表格区域，在字段中输入相应的标识-单击下一步 第四步：弹出的【数据透视表和数据透视图向导】对话框选择【新工作表】，单击【确定】按钮
6.4.3 组合功能 1、按日期组合，在日期单元格上右击-选择【组合】功能-填写相应参数-单击确定 2、按数量组合，方式同上，选择步长即可
6.4.4 切片器 第一步：创建一张透视表 第二步：选择任意一个单元格-【分析】-【插入切片器】-选择所需的字段-单击确定 第三步：在切片器中选择需要统计的方式
第七章 可视化图表
7.1 图表类型介绍
柱状图、折线图、饼图等
7.2 图标的组成元素
图表区、绘图区、水平轴、垂直轴、图表标题、图例、数据系列、数据标签（图表右上角加号可以调出） 系列填充为爱心：单击数据系列-右键-【设置数据系列格式】-【填充】-【图片或纹理填充】-【插入】-选择图片-填充方式选择【层叠】（边框无实线）
7.3 组合图表制作
7.3.1 簇状柱形图-折线图 第一步：【插入】-【推荐的图表】-【所有图表】-【组合】-单击确定 第二步：美化细节。套用内置的图表样式；修改图表标题；增加数据标签；只保留一个均价的数据标签 第三步：检查坐标轴是否合理，可以采用次坐标轴，同时将数据颜色改成与数据系列一致，便于观察
7.3.2 半圆饼图 第一步：将数据按降序排列，并增加“总计”行 第二步：插入饼图，选择第一种类型，在【设计】选项卡下更改配色 第三步：选择饼图-右击-【设置数据系列格式】-在弹出的设置框中将【第一扇区起始角度】改为270，将其转到饼图底部 第四步：选择半圆扇区-【格式】选项卡-【形状填充】设置为【无填充】，轮廓线也设置为【无填充】 第五步：细节调整，将半圆拉大并居中；删除图例中的“总计”；调整图例位置等
7.3.3 非闭合圆环图 第一步：设置辅助列，数据列和辅助列构成整个圆环 第二步：选择第一个数据区域-插入圆环图-选择其他数据区域-Ctrl+C复制-选中圆环-Ctrl+V粘贴-完成所有数据的制作 第三步：选择圆环-右击-【设置数据系列格式】-在弹出的设置选项中，将【圆环内径大小】设置为40%（视数据情况而定） 第四步：将辅助数据的环状图隐藏起来，填充设置为【无填充】即可（圆环无法全部选中，完成一个圆环的操作后，可以选择下一个，然后按快捷键F4，重复上一操作） 第五步：细节调整，修改配色；增加数据标签；删除图例等
第八章 效率倍增的五个操作
8.1 一步调用功能
第一步：调出快速访问工具栏，【开始】-右键-【自定义功能区】-【快速访问工具栏】-勾选【在功能区下方显示快速访问工具栏】 第二步：自定义快速访问工具栏，根据情况添加，批量添加方式同上 第三步：保存自定义设置，【导出所有自定义设置】
8.2 行/列的批量操作
8.3 快速浏览的秘技
8.4 美化表格
8.5 快速打印
补充：快捷键及函数
快捷键
替换工具：Ctrl+H
输入当前时间：Ctrl+Shift+;
输入当前日期：Ctrl+;
快速填充：Ctrl+E
全选：Ctrl+A
选取区域：Shift+鼠标操作
扩展选取选定区域：按住Shift+方向键 自动扩展到周围非空单元格：Ctrl+Shift+8
单元格跳转（跳转到有数据行列的两端）：Ctrl+方向键
删除空行：Ctrl+-
批量输入/复制：Ctrl+Enter
快速求和：Alt+=
定位可见区域：快捷键：Alt+；
函数
返回当前日期：=today（）
引用A2单元格=indirect（$A2）
数据提取函数：=MID（截取的字符，左起第几位开始截取，截取的位数）
将数字转化为文本：=TEXT（数值或单元格引用，想要转换的文本格式）
统计非空单元格个数：=COUNTA（数据区域）
计算字符长度：=LEN（数据区域）