Excel教程 零基础到应用实操

Microsoft Excel是Microsoft为使用Windows和Apple Macintosh操作系统的电脑编写的一款电子表格软件。直观的界面、出色的计算功能和图表工具，再加上成功的市场营销，使Excel成为最流行的个人计算机数据处理软件。

一、入门

• Excel软件历史版本
• 文件选项功能介绍

二、基础设置

1.文件保护、共享

• 用密码进行加密需要用密码才能打开工作簿
• 保护工作表后将无法更改单元格或图表
• 保护工作簿结构后无法插入、删除、重命名、移动
• 共享常以附件或PDF形式发送

2.常规选项

• 手动重算往下拉无更改效果，按F9可刷新，只对部分数据运算来加快运行速度
• R1C1引用样式、公式记忆式键入
• 错误检查规则，当有文本格式的数字或者前面有撇号的数字等错误时，会显示错误标志符
• 保存自动恢复信息时间间隔设置为1分钟，电脑死机或卡住之后尽可能保存原文件
• 在自动恢复文件位置中复制路径，在文件资源管理器中查找就能恢复最近操作的文件

3.高级选项

• 粘贴内容时显示粘贴选项、显示编辑栏、显示水平滚动条、显示垂直滚动条、显示工作表标签、显示行和列标题都应打钩
• 具有零值显示零、显示网格线、显示分页符

4.功能区、快速工具栏

• 自定功能区新建选项卡和组可以把我们自己日常比较常用的添加进来
• 配置完成后可以导入到其它电脑，也可以把其它电脑上的配置导入到这个电脑上
• 自定义快速访问工具栏开始下即可直接使用，增加个性化和特质化，提高办公效率

5.加载项、宏

• Excel加载项转到分析工具库，点击数据产生数据分析库，存在直方图、回归、抽样、移动平均等应用功能
• COM加载项会以一个新的功能区的形式进行加载
• 当Excel公式过大导致文件关闭，重新打开后会显示在被禁用项目里，点击启用关闭确认后被禁用项目就可以重新启用了
• 信任中心选择禁用所有宏并发出通知，若选择不发出通知将无法启用
• 受保护视图下取消设置，将不会再出现启用编辑的提醒
• 宏编程文件不同于普通Excel文件是图标有感叹号且文件后缀名是.xlsm

6.格式兼容、简繁转换

• 2003版Excel文件无法打开高级版本Excel生成的文件，更改保存格式为Excel 97-2003工作簿（*.xls），去掉保存此工作簿时检查兼容性后将不再提醒功能损失等项目
• 外资、台资、港资公司用繁体比较多，转交给大陆客户则需转化为简体，选择COM加载项里中文转化加载项就会出现中文简繁转化

三、操作

1.行高列宽

• 批量调整第一行和第二行的高度，第一列和第二列的宽度，鼠标放在行列交叉处
• 快速调整可选择自动调整行高和列宽，美化时将字体调整为微软雅黑，行高设置往往不同
• 格式刷调整行高和列宽，从左到右选中整个表格调整列宽，从上往下选中表格调整行高

2.工作簿、工作表

• 右键点击小三角激活可快速选择工作表，区分不同工作表可通过修改名称或者设置工作表标签颜色
• 常见不同类型工作表，源数据、参数表、透视表、结果
• 新建窗口、重排窗口
• 创建工作表添加OIIO效率专家插件，效率专家插件在COM加载项里目录管理建立导航工作表，选择是否包含空白工作表，更改工作表名字后需更新工作表标签

3.单元格

• 数字、日期一般靠右对齐，能明显看出数据大小
• 备注的自动换行可以拉伸达单元格宽度或高度，将文字放进一行

4.信息录入

• 单元格、编辑栏 、外部导入，编辑栏录入能调整编辑框大小，而当单元格录入信息过多时会遮盖点后面单元格内容，外部导入数据来源自Access、自网站、自文本或自其它来源
• 判断单元格文本、数值、日期分别在编辑栏输入函数 =ISTEXT(C12)、=ISNUMBER(C12)，回车满足显示True，否则为False
• 逻辑值只包括TRUE和FALSE
• 单元格文本类型包括 文字和’2334单引号+数字，也可以通过开始数字下选择文本后再向单元格输入内容
• 错误类型包括DIV错误分母不为0，输入函数=C11/D11，NA错误查找不到的错误值，输入函数=MATCH(C11,E3:E7,0)、REF错误引用错误，原有单元格被删除，输入=B9*#REF!等
• 日期值是一种特殊的数值，可通过自定义格式变换，开始数字选择常规后会自动切换为某个数字，将该日期减去1900/1/1所得差值
• 非日期格式需用函数处理或数据分类处理

5.单元格格式

• 常规 数值货币可设置小数点位数和负数格式
• 会计专用 按照会计准则法规要求去做
• 日期 区域设置和类型
• 先选中区域改为文本，再输入内容，颠倒顺序后无用
• 输入超过15位数值身份证和货号，多出来的位数后面变为0，可输入单引号强制使其变为文本，或直接选择单元格格式为文本

区分单元格本质和表象的区别

• 输入三个分号"""，单元格内容被隐藏
• 文本求和为0，可自定义G/通用格式件等得出销量汇总

6.文本数值转换

文本转数值

• 法一：数据分列选择列数据格式为常规即可将文本转化为数值
• 法二：使用=value(E4)函数将一个代表数值的文本字符串转换成数值
• 当运用公式计算没出现运算结果而只显示公式，需要在开始下选择清除格式，需针对第一个单元格做回车动作，然后下拉该列单元格出现运算结果

数值转文本

• 法一：分列选择格式为文本
• 法二：使用=TEXT(E4,"0.00")函数或者=TEXT(E4,"0")表示去掉小数，即可将数值转化为文本

日期标准化

• 法一：分列选择格式为日期
• 法二：使用函数=--TEXT(E4,"0000-00-00")相当于强制转换

7.条件格式

• 筛选数据可通过突出显示单元格规则从而让结果看起来更智能和更高的可视化
• 使用公式确定要设置格式的单元格，需要辅助单元格，如=A5>$J$3
• 使用数据条先选中区域，调整填充、颜色、边框，确定是否仅显示数据条
• 色阶可通过数字自定义格式使用"""来隐藏掉数据
• 比较大小的图标集里，数据差异大的适合进度条，增长率适合上下箭头

8. 单元格序列填充

• 填充序列 123456，复制单元格以所选区域为最小单位来进行复制
• 右键序列，选择等差等比，设置步长终止值
• 自定义序列手动输入或单元格导入
• 特定连续区域填充，先选中编辑区域，编辑栏输入内容后ctrl+enter或者在单元格内输入E6:E14自动选中编辑区域
• 特定不连续区域使用ctrl键一个一个连接起来，批量处理技巧见下
• 隔行插行F5或插ctrl+G定位空值，选中空白单元格右击插入整行

9.不连续单元格批量填充

• 开始查找和选择下定位条件，选择空值并在空的单元格输入=A4，按ctrl+enter，刚刚选中区域通通填充为上一个单元格的内容
• 填充完毕后，为防止数据随着上行单元格的修改而修改且减少运算压力，将差值数据切成为数值，选择性粘贴为数值运算无或者复制粘贴为数值，完成后按esc退出选中状态
• 和并单元格拆分后再填充，两列和一列的处理操作相同

10.数据基础处理

• 添加整行、整列、活动单元格右移、活动单元格下移
• 删除整行、整列、右侧单元格左移、下方单元格上移
• 隐藏后恢复，选择整个单元表格右击取消隐藏，在首行点击取消行的隐藏，在首列点击取消列的隐藏

11.定位

• 选中区域，快捷键F5、Fn+F5或ctrl+G，选择定位条件空值
• 定位对象，属于符号之类的
• 定位公式，去掉错误，即错误公式不会被选中，

12.数据分列

• 文本导入确定分隔符号、固定宽度、导入起始行、数据是否包含标题
• 日期标准化列数据格式选择日期
• 身份证提取出生日期，选择固定宽度，身份证分为3段，出生日期被提取出来
• 文本转化为数值将列数据格式改为常规，或者使用函数=VALUE(D4)
• 文本和数字连在一起没有分隔符的情况直接分列没法解决，可以先使用函数=RIGHT(A11,LENB(A11)-LEN(A11))分列出右侧文字，再使用函数=LEFT(A11,LEN(A11)-LEN(B11))分列出左侧数字

13.选择性粘贴

• 粘贴：完全复制 粘贴值：只复制结果而不带任何格式 转置：行列颠倒 格式：只复制格式 值和原格式：数值和格式都复制
• 链接：公式格式也可直接使用格式刷 图片：只是一张图片可剪切、移动、旋转 带链接的图片：可剪切移动且随原数据的修改而修改，但不可复制到其他单元格

14.格式刷、超链接

• 双击格式刷可批量处理文件
• 插入超链接，输入要显示的文字和键入引用的单元格，也可链接到现有文件或网页
• 批量删除超链接，在名称框里输入K3:K5，然后再删除，防止直接选中单元格造成误点

15.冻结窗口、拆分、排序

• 视图窗口下冻结首行、冻结首列，还可冻结F4单元格使得A-E和1-3列表都能一直被看到
• 拆分使得案列1个变为4个，可同时查看多项内容
• 排序应先选中全部表格，若前面含有没有数据的空白行则不能直接排序，否则会出现错误，勾选数据表含标题，可按照数值、单元格颜色排序

16.数据筛选

• 清除后所有的筛选全部被清除
• 当筛选内容很多，选择高级，选择列表区域和条件区域，并将筛选结果复制到其他位置
• 若筛选条件需同时满足两项要求时应注意条件区域的书写格式
  
• 输入函数=IF(AND(ISNUMBER(FIND("合作",B4)),E4>1000),"符合","不符合") 给原列表增加辅助列，也可筛选出符合的数据行

17.数据合并

• 连接符=A5&B5&C5可合并单元格，但没有分隔符导致之后的分裂操作不方便进行，编辑栏输入格式=A5&"-"&B5&"-"&C5可将其按照分隔符号-分裂
• 引入函数=CONCATENATE(A5,"-",B5,"-",C5,"-")，也可将H3替代-，并更改为绝对引用维持锁定状态=CONCATENATE(A5,$H$3,B5,$H$3,C5,$H$3)，更改H3分隔符也会随之改变

18.数据保护

• 除了填充白色文本框，还可以自定义格式"""并隐藏编辑栏
• 选中整个工作表，自定义格式设置单元格格式保护下锁定，公式和汇总的运算法则将无法改变，最后输入密码保护工作表
• 当很难规范下面人或同事的操作行为，只能通过技术防范他们出现错误
• 保护工作簿后无法取消隐藏，若发给客户不想让对方看到的数据最好选择删除该工作簿，防止客户水平够高还原你的数据

19.创建组、分类汇总

• 分组1代表收起来2代表展开，清除分级无法撤回操作
• 分类字段不能按照销售额等指标去做，全部删除可清除分类汇总格式

视频链接：解读组及分类汇总

20.查找替换的高级应用

• 查找值代表结果是1000，查找公式代表公式里有1000
• 通配符?江可查找出浙江、黑龙江，通配符*江可查找出江苏、浙江、黑龙江，？代表1个字符，*代表0个或多个字符
• 强制查找*，需设置查找内容为~*，真正带有*才能被查找出来
• 查找内容复制空格，替换栏清除格式后保证为空，选中全部区域后需点击全部替换

21.快捷键

• F1、F2、F4、F5、F9分别代表帮助，双击进入单元格、重复上一步操作、应用于嵌套函数
• ctrl+F3弹出一个新建名称对话框，alt+方向键向下重复录入同列前面的内容
• alt+shift+方向键可选中整片区域并且跳过空行，ctrl+A选中区域时无法跳过空行
• WIN+D切回桌面，ctrl+home跳回单元格A1，ctrl+H、ctrl+F替换查找，ctrl+S死机前保存

四、函数

1.相对、绝对、混合引用

• 输入方式 编辑栏、单元格、公式-插入函数，编辑栏空间大且可以拉伸，而插入函数对于嵌套函数的输入稍显不便
• 总的函数超过300多种，常用函数20多种，以下将普及60多种
• 9*9乘法表使用混合引用=B$20*$A21

=B3，行列都不锁定，拖动行列都会变化
=$B$3，行列都锁定，拖动无变化
=B$3，行锁定列不锁定，拖动列变化
=$B3，行不锁定列锁定，拖动行变化

2.IF、AND、OR

• ctrl+z撤销会跳出公式，而左上方撤销快捷键能修改部分公式
• 公式求值步步操作进行验证

语法结构 
=if(logical_test,value_if_true,value_if_false)
=or(logical1,logical2,...)
=and(logical1,logical2,...)

3.多IF嵌套、Lookup数据分层

• =IF(B7>=90,“优秀”,IF(B7>=80,“良好”,IF(B7>=60,“及格”,“不及格”)))
• lookup返回小于等于查找值对应的最大值（查找区域的最后一列）=lookup(lookup_value,array)

4.IS函数

ISNA、ISTEXT、ISNUMBER、ISNONTEXT、ISLOGICAL、ISBLANK判断是否为NA错误、文本、数值、非文本（错误、文本）、逻辑值、空白
ISERR判断是否为非NA错误
IS返回计算值，返回错误时显示的值，应用于如何处理错误

• 追踪从属单元格可检验出函数所处理的单元格
• F9抹黑 =A1+B1*C1,如果你想查看B1*C1的结果，就可以在编辑栏中选取公式的B1*C1部分，按F9就可以查看这部分的运算结果

5.智能标记

• 编辑栏都是英文状态的符号
• 先函数判断做一次标记=IF(AND(B23>3000,C23>5%),"优质","其他")，再通过条件格式找出对应行=$D23="优质"
• 直接条件格式编辑格式规则=AND($G6>3000,$H6>5%)

6.常见错误和引用错误

• DIV错误、NAME错误、VALUE错误、REF错误、NA错误分别对应分母为0、函数名错误、数值+文本、原先所引用单元格消失、VLOOKUP中不存在或者区域变化等导致的操作错误
• 注意在SUM函数中文本是当做0处理的，因此当文本和数值相加时不会报错
• 循环引用是指所要求结果的单元格被包含在计算的单元格中，错误检查循环引用会提示出错的单元格区域，当重新打开文件会提示有循环引用错误的出现
• 当单元格应显示运算结果时却出现公式，选择开始清除格式，去掉所使用的函数公式

7.文本转化

• –TEXT(A6,“0000-00-00”)、TEXT(A6,“0000-00-00”)*1都可以，EXT(A6,“0000-00-00”)+1虽然能转化但日期会自动加1
• =VALUE(TEXT)把文本转化为数值 =TEXT(B9,"0.###") 0是占位符，#不占位
• =LOWER(text)把字母都变成小写 =UPPER(text)把字母都变成大写 =PROPER(text)首字母大写后面都是小写
• =FIND(find_text,with_text,start_num)返回字符所在位置 =OR(ISNUMBER(FIND("联想",F10)),ISNUMBER(FIND("lenovo",F10))) 查找出含有联想或者lenovo字段的单元格

8.截取

• =LEFT(text,num_chars) 向左截取字符=RIGHT(text,num_chars)向右截取字符 =MID(text,start_num,num_chars)从第几位开始截取几位字符 =LEN(text)返回文本字串的长度 =LENB(text)返回文本字节的长度
• LEN(text)-LENB(text)表示文本中汉字个数，汉字字节为2，数字和字符字节都为1
• =RIGHT(A18,LEN(A18)-FIND("/",A18)) 返回字符/之后的文本

9.条件计数

=COUNTA(value1,value2,...)非空单元格的个数
=COUNT(value1,value2,...)数值的个数
=COUNTIF(range,criteria)单条件计数
=COUNTIFS(criteria_range1,criteria1,criteria_range2,criteria2,...)多条件计数
=COUNTBLANK(range)计算空单元格的数量

10.其他

• =WEEKNUM(A2,2)2代表日期格式，从星期一开始算第一天，一共有多少周
• sum(F3:G4)直接区域内所有数字求和；sumif\sumifs条件求和，条件区域和求和区域大小相对应
• 隐藏函数 =DATEIF(开始日期，结束日期，CODE) Y-返回整年数，M-返回正月数，D-返回天数
• =SUM(OFFEST(B10,1,2,1,2))参数分别代表单元格或区域，偏移行，偏移列，返回的行数-1向上、返回的列数-1向左，返回1表示返回自己，若是多行多列则使用sum相加
• =SUM(OFFSET(B4,COUNTA(B:B)-1,0,-6,1)) COUNTA是非空计数，可灵活应用于不同区域
• =REPT("*",4)重复输入4个
• =REPLACE(old_text,start_num,numm_chars,new_text)把指定位置的值替换掉，
• =SUBSTITUTE(text,old_text,new_text,instance_num)把指定值替换掉，最后一个参数表示替换第几次出现的旧字符，省略 表示全部替换
• =INDEX(reference,row_num,column_num,area_num)返回特定区域交叉的行列的值，最后一个参数默认为1也可省略
• =SUBTOTAL(101,$C$3:$C$22)计算隐藏后的结果，不包含隐藏值
• =SUMPRODUCT(array1,array2,...)多个区域相乘的结果，或者=SUMPRODUCT(条件1*条件2*条件3)来判断数据录入是否重复
• 数值位数处理函数，当第二个参数等于0，四舍五入取最接近的整数；小于0，表示在小数点左侧前几位四舍五入；大于0表示四舍五入到指定的小数位

 1. =ROUND(number,num_digits)
 2. =ROUNDUP(number,num_digits)向上舍入
 3. =ROUNDDOWN(number,num_digits)向下舍入
 4. =FLOOR(number,significance)向下舍入最接近基数的倍数
 5. =CEILING(number,significance)向下舍入最接近基数的倍数

11.数据有效性

• 数据根据所选内容创建，公式名称管理器下可更改引用位置，数据验证允许序列来源=indirect($D5)/("A2")此时D5被定义为单元格名称而不是单元格，因此可以直接选中
• 选中录入数据的区域，数据验证设置数据格式和错误提醒
• =VLOOKUP("*"&A5&"*",$E$5:$F$8,2,0)实现对A5单元格的正向模糊匹配查询，已知手机2填充手机
• =LOOKUP(1,0/(find($E$12:$E$16,A12),$F$12:$F$16)逆向模糊查询，已知手机填充手机2
• 开始查找定位条件，找出具有数据有效性的区域，可快速清除实现重新设置，数据验证可实现数据的快速选择性输入更改
• 开始条件格式突出显示单元格规则，选择重复值设为有颜色的文本；若想实现数据有效整数的约束可在数据验证设置允许，然后圈释无效数据；之后输入中若有不符合规则的数据录入会有自动提示
• 条件格式的规则应用级别高于表格背景色，选中保护区域，数字设置单元格格式锁定，审阅保护工作表设定保护区域

如何锁定特定的单元格：
①、全选工作表Ctrl+A，点鼠标右键，设置单元格格式/保护，将“锁定”项勾选去掉（Excel默认是勾选的）。
②、再选定需要的单元格，设置单元格格式/保护，将“锁定”项勾选。
③、审阅/保护工作表，打开“保护工作表”对话框，可按默认勾选选项，也可根据需要勾选其它选项，设置密码，即可。
简而言之，“锁定”只在受保护的工作表中才起作用，平时使用勾不勾选都没有影响。

12.身份证信息提取

• 籍贯：=VLOOKUP(--LEFT(H2,2),辅助信息!$O$2:$P$100,2,0)LEFT表示取出H2单元格的前两位数字
• 出生日期：=--TEXT(MID(H2,7,8),"0000-00-00")，再通过条件格式来自定义日期格式
• 性别：=IF(MOD(MID(H2,17,1),2)=1,"男","女")是否被2整除，男单女双
• 规范输入格式：=AND(LEN(H4)=18,COUNTIF($H$2:$H$200,H4&"*")=1)出错警告设置错误信息类别，设置输入信息请输入正确的18位身份证号码，注意星号的连接可取任意值，因为EXCEL自动识别的文本只有15位，后面自动判别为0

视频链接：规范身份证号码输入18位不能重复

13.多项目维度乘积

• SUMPRODUCT(B4:B6,C4:C5)，返回相应的数组或区域乘积的和
• VLOOKUP(look_value,table_array,col_index_num,[range_lookup])参数分别表示，所查找的对象，查找区域，第几列查找，匹配条件为精确false\0或模糊true\1，需注意 查找对象必须在该区域的第一列，查找区域锁定防止下拉时出错
• 可将col_index_num换为COLUMN(B1)形式实现快速查找替换，CTRL+D填充一列，CTRL+回车填充全部
• VLOOKUP使用结果出现错误，编辑栏查看原数据是否错误，=TRIM(text删除字符串中的首尾多余空格
• 当需要数据列交换位置，使用函数逆序查找=VLOOKUP(A17,IF({1,0},$F$16:$F$32,$E$16:$E$32),2,0) 交换E列和F列
• 类比，按列查找=HLOOKUP(A43,$A$37:$J$38,2,0)
• =VLOOKUP($A3,基本工资表!$B$2:$100,5,0)*部门奖金对照表!G$3TRUE是近似匹配，FALSE0是精确匹配，5代表返回所查区域的对应的第5列

14.个税计算

• =ROUND(G4*H4-I4,2)按照指定位数四舍五入
• 可分步计算，也可以使用一个公式，减少区域的引用=ROUND(MAX(F4-3500)*(3;10;20;25;30;35;45)%-5*(0;21;111;201;551;1101;2701),0),2)
• =INT(E3)向下取整
  =RANK(F2,$F$2:$F$50)排名
• =LEFT(H2,2)

15.按员工编号查询

=VLOOKUP($B1,个人所得税税率表！$B$3:$H$18,COLUMN(B2),0)进行列的偏移，适用于一个条件的查找

16.按部门查询

 =INDEX(A3:C5,1,2)返回行列交叉的结果值
 =COUNTIF(A3:C3,1)返回所选区域1出现的次数
 =MATCH(1,A3:C3,0)目标区域只能选择一行或一列，0表示精确查询，1表示小于，-1表示大于，返回查找位置，而VLOOKUP返回查找值

• 新建名称管理器可以实现区域的快速锁定
• 数据验证选择序列，MATCH连接两个或两个单元格即以数组结尾，查找时必须按ctrl+shift+回车，适用于多个条件的查找
• {=IFERROR(INDEX(个人所得税税率表!=$B$3:$I$1000,MATCH(部门工资查询!$K$1&ROW(A1),个人所得税税率表!$D$3:$D$1000&个人所得税税率表!$I$3:$I$1000,0),1)," ")} 所查找的项必须和查找的区域相对应，向上、下、左、右拉动需要考虑公式里变化的项，$I$1000是增加区域的拓展性

17.数组嵌套

• =IF($F$6=B6,C6," ")将其他数据和目标数据相比，满足要求返回原数据不满足就返回空，由此引入数组=MIN(IF(F6=B6:B15,C6:C15," "))
• 也可以插入数透表选择值汇总依据为最小值，批量处理数据更快速

五、数据透视表

1.规范源数据

• 数据透视表的定义：分类汇总+动态交互
• 缺字段时补充，存在合并单元格时拆分，同一字段类型不统一修改数据格式、
• 修改完成后，不是直接刷新，而是点击分析更改数据源，更改表/区域和求和项

2.基础操作及多维布局

• alt+D+P快捷键打开数据透视表和数据透视图向导

4.分类汇总原理

• 使用函数 =SUMIF($C$2:$C$45,"饮料",$D$2:$D$45或者透视表都可实现分类汇总，条件求和一个条件用SUMIF二个条件用SUMIFS 求和区域、条件区域一条件一、条件区域二条件二
• 分类汇总功能都被透视表包含，选择合适的位置透视表可被固定到Excel表格里

5.选项

• 值字段设置选择计算类型和值汇总依据里的选项功能一致，数字格式可设置显示千分位符和保留的小数位数
• 数据透视表选项去掉更新时自动调整列宽，防止批量调整数据时造成视觉影响
• 输入计算字段，当有相同项或字段将不能计算，可通过在字段前添加空格区分，报表筛选区域显示字段设为水平并排
• 合并并居中排列带标签的单元格，选择重复/不重复所有的项目标签，去除选项生成GetPivotDate，否则数据处理结果都保持一样，不会随行的改变而改变

6.日期文本组合

• 将标签以表格格式形式化，右击创建组，步长选择年，日的天数改为7就可按周组合，用函数判断某一天是星期几=TEXT(D4,"AAAA")，然后更改起始时间
• 文本创建组最多三层类别，可用函数进行处理=TEXT(A2,"YYYY-MM-DD") 得到文本格式的假日期，没法实现自动创建组，只能每次选中部分区域

7.分析卡

• 当一个工作表中出现多个数透表时，可以选择更改数据透视表名称，方便VBA等的运算
• 若数透表取消组合后以日的形式展开，表格与表格之间空格间隙发生辩护，可能会遮盖住其他数透表，可以选择复制数透表到另一个工作表，然后行标签按年展开，之后增加求和项将不会影响原数透表
• +按钮存在点击即可展开折叠隐藏
• 报表布局以压缩、大纲、表格形式显示

8.显示无数据行/隐藏明细

• 当有两个维度出现才可显示无数据行，字段设置布局和打印，显示无数据的项目可设置对于空单元格，显示
• 数据透视表选项数据，取消启用显示明细数据将无法看到数据源，但再次勾选数据源将重现显现

9.计算值百分比汇总

• 推迟布局更新，多次拖动很多字段后再点击统一刷新
• 计数和求和区别，计数就是看有多少条记录出现，求和是求总值
• 总计、列汇总、行汇总的百分比分别表示全部、每行、每列相加为100%，百分比是以某行数据为单位1，进行比较转换

10.父类汇总百分比

• 父行行标签和父列列标签至少要有两个，父级汇总百分比关键要看父级是哪一级，确定值显示方式

11.排名排序

• 排名常使用函数=RANK(B4,$B$4:$B$17)，但当区域扩展或收缩时增加筛选器，容易出错；透视表升序降序的区域是自己动态控制的选择值显示方式为升序排列
• 升序、降序、手动排序、自定义列表排序文件高级编辑自定义列表从单元格导入序列，右击排序其他排序选项，选择升序排序

12.字段计算

• 分许计算字段，输入计算字段的名称和公式
• 创建组比计算项更有意义，计算项可能会导致重复计算

13.区间汇总动态透视图

• 值的字段设置，数字格式数值选择两位小数，进行数据的区间详细划分
• 根据最大值、最小值、平均值设置起始、终止值和步长，分析数据透视图选择合适的统计图
• 更新完数据后刷新，统计图将会自动变化

14.二维数据转一维

• 一维数据是指一个访问数三条记录，而二维数据是三个访问数一条记录，即多个字段表示一个意义
• 快捷键ALT+D+P插入数透表，而在插入项选择数透表是直接转到插入的第二步
• 请指定待分析数据的数据源类型，选择多重合并计算数据区域，创建单页字段，选择总计和总计交叉的单元格得到明细数据

15.多重合并计算透视

• 月分析计算百分比时，考虑到每月天数差异，值汇总依据选择平均值，可增加差异百分比增加数据可读性

16.切片器

• 分析插入切片器，选择报表链接把多表连接起来，针对一级类下的二级分类的数据进行详细分析

视频链接：切片器多表联动应用

17.透视数据动态更新

• 当数透表字段名无效时，需注意数透表第一行不能是空行
• 创建表，选定表格数据来源，公式名称管理器编辑名称
• 使用数透表动态填充后，没有空白行

18.外部导入数据联系

• 自Acess导入可分别导入表和数透表，数透表会减小Excel文件
• 现在大多数网站不支持自网站导入
• 自文本导入，选择数据包含标题、分隔符、列数据格式
• 补充商品货号位数，使用函数=TEXT(J2,"0000000000)将29367补充为0000029367，也可在选择列数据格式时将数值改为文本，数值前面是不会有0补充位数的
• 导入CSV文件选择现有链接浏览更多，或者自其它来源

19.入门SQL

• 作为数据分析师最基础的技能就是Excel+SQL，SQL的使用能提高数据处理效率，SQL语言的主要功能是查询更新添加删除，非数据库维护人员常用Select查询，无权利去更新添加删除
• 透视表和SQL的使用是通过现有连接进行关联的，浏览更多打开表格
• 安装Notepad++，一个具有编程语言的记事本，选择语言为SQL，符合语法的地方会显示标记，判断输入语法是否正确，避免符号错误等问题
• 固定字段选择 select 渠道一级分类，访问数 from[教程案例$A1:G918]
• 条件选择 Select 渠道一级分类，访问数 from [教程案例$a1:G918]where 渠道一级分类=“KAl流量”
• 全部字段 Select *from[教程案例$A1:G918]
• TOP N选择Select TOP 5 渠道一级分类,访问数 from [教程案例$A1:G919]

20.数据跨表合并透视分析

• 把不同工作表数据汇总到一个工作表中，设计刷新连接属性或者数据全部刷新链接属性，注意不是属性
• select "PC" as 平台，渠道一级分类，访客数 from [PC$A1：F484] union all PC代表工作表名称，union all将表格连接到一起。渠道一级分类跟访问数、取串、贴签之前要给其加一个别名平台，若是3个表最后一个表不需要union all，按住shift键拖动该列调换列的位置
• 把同一工作表的杂乱摆放的数据汇总到一个工作表中，select "3月” as 月份，渠道二级分类，访问数 from [月份汇总$A1：F176] union all 3月是该表内的其中一个月份

21.透视表典型应用

• 为使得数据更清楚，将小数位数调整为0并且使用千位分隔符，设计总计仅对列使用
• 选择合适的值汇总依据，如比较促销前和促销日可设置为平均值才能看出真实性
• 分析哪种渠道对变化带来的影响，当数据透视表出现逻辑运算错误时，可使用公式自行计算

视频链接：透视表典型应用案例

22.SQL典型应用

• 分析访客变化趋势、过程占比
• 确定表格导入数据选择属性，circle里4.2在命令文本里为4#2$，生成文本
• 设计刷新连接属性，贴代码select "2015-04-01" as 日期，来源，来源明细，访客数 from [工作表名称]到命令文本里，把多个表连在一起，避免汇总项的粘贴可在from和union all之间添加where 来源明细<>'汇总'
• 开始创建数据透视表，当求和项为0时，使用函数=ISNUMBER([@访客数])判断源数据是否出错，若出错数据分列选择列数据格式为常规
• 可选中空白区域删除或右键首行自主访问，选择删除重新刷新数据透视图表
• 当数据过多，用circle写会比较长，考虑使用VBA，保存相同表头对下列数据进行辨定，每一次做循环把下条记录放在固定位置，全部表并立循环

点这儿，了解什么是 SQL语言

23.数据多重合并与拆分

• 合并单一工作表、指定列、合并选取工作表记录
• 拆分成不同工作表、选择拆分的字段；选中多个工作表拆分成不同的工作簿，选择工作簿保存位置
• 下载地址：效率专家，也可以微博@郭安定获取插件资源

24.高效实现透视结果更新

• 默认行标签可取消组合，由月改为日，若出现空白行可在值筛选中取消空白行设置，若增加新的行数据，刷新后仍需勾上新增加的数据行，结果才能跟上，但是执行速度过慢
• 选中区域插入表格，在表格中增加两列新的数据，可通过函数=vlookup(C4,参数表!$A$1:$C$500,2,0) 2指科目名称所在列，C4是科目编码所在单元格会将只输入一次公式，@科目编码代替了工作簿 =vlookup([@科目编码],参数表!$A$1:$C$500,2,0)，在表格里填充一列，其他都会自动填充
• 智能表格实现透视表引用数据自动更新结果，观察公式下名称管理器，发现通过表格出来的名称定义是自动改变的，不允许我们进行编辑
• 刷新只刷新所选工作表，而全部刷新是刷新工作簿中所有工作表

25.条件格式

• 使用公式确定要设置格式的单元格，给某些字体加粗加红等其他标签
• 管理规则新建格式规则，使用公式确定要设置格式的单元格，选择将格式规则应用于所有为‘商品“显示”求和项：销售量’值的单元格，避免总计也被设置格式
• 确定负值填充颜色和边框颜色，坐标轴设置选择单元格中点值，不管负值多小，最多占单元格的1/2，还可适当调整最大值、最小值，也可仅显示数据条
• 值字段设置的数字格式，修改单元格数值类型，增加千分位符使数据更易读，单元格格式，管理规则编辑规则修改颜色规则增强色差对比，常应用于降雨或气温分布
• 特殊字符使用"""来隐藏数据值，通过条件格式的运用增强数据报表的可视化和美观度

26.动态透视图VS传统图表

• 透视图可随着透视表的变化而变化，动态交互性很强，而传统图表可能需要多个函数控件才能实现相同的功能
• 数据删除重复项，函数条件求和=SUMIF(案例!$C$3:$C$1189,A3,案例!$F$3:$F$1189)选中第一行单元格，ctrl+shift+方向键向下
• 插入柱状图，右击选择数据，图例项添加系列名称和系列值，编辑水平轴标签，传统图表完成，若部分数据相近而无法区分可进行升序降序处理
• 分析数据透视图，图表类型少，但筛选销售人员、商品时会出现对应的销售额柱状图
• 计算字段插入计算字段在数据透视表中得出单价

27.切片器

• 创建2个透视表，其中一个值显示方式改为列汇总的百分比
• 分析插入两个切片器，选择可筛选类别，转化为图表
• 修改切片器样式和列数后，分析数据透视图，取消多余，分别进行报表关联
• 透视图表美化，调整行列单元格的宽度，并填充颜色，整个表应包含框架、大标题、大概的描述、切片器、分析结论，添加数据标签也可看到具体值
• 更改源数据分析全部刷新透视图表

视频链接：切片器多表数据联动

28.美化与输出

• 方便自己理解时可修改透视表字段名，为防止相同的数据透视表字段存在需要将求和项替换为空格
• 透视表汇总行标记、透视表样式选择
• 取消组合重新创建组，以表格形式显示，重复所有项目标签
• 打印特定区域可选择性打印，打印将无缩放改为将所有行列调整为1页
• 多页表格都需打印表头，选择页面布局，确定表头区域，分析+/-按钮可使得表格内容整齐排列
• 只打印汇总结果，但若只选择汇总行列会造成日期等重要信息流失，推荐在广区域下使用文本筛选包含汇总

视频链接：透视表的美化与输出