网易云课堂地址：
章节2课时6【开始您美丽学习征程！】空白处补零值，定位还是替换
1） 可以使用Ctrl + H，进行查找替换： 查找格中什么都不用填，替换格中填0，然后全部替换为0
2） Ctrl+G定位到空白 -- 填入0 -- ctrl + 回车（批量填入）
3） 统一数据显示的格式 -- 开始 -- 数字 -- 千位分隔样式
章节2课时7分类求和就几秒，空白批量填充靠这键，帅炸裂！
1） 先定位到空值，然后Alt + =， 对每一列求和
2） 取消合并单元格，并批量填充空白
选择A列，开始，单击合并后居中按钮
定位条件，空值
键盘按=键，向上方向键↑
CTRL+回车（批量填充的快捷键）
如果此时还想去掉每个单元格中的公式？
选择A列，复制，右键，选择性粘贴，数值
（或者选中A列，右键该列的又边框，向右拖然后回位，复制数值）
章节2课时8分列分开内容，激活日期假变真，合一起得靠她
0） 插播小技巧：选中某连续几列，然后对着第一列右边线双击，就能自动调整每一格的宽度
总之，双击就能展开列的宽度
1） 分列
选中要分的列 -- 数据 -- 分列 -- 分隔符号 -- 下一步 -- 分隔符号 -- 其他 -- 填入想要的分隔符 -- 下一步
此时，如果某一列是日期 -- 在窗口中选中该列 -- 列数据格式调整为“日期 YMD” -- 完成
2） 合并多列
合并多列一般使用公式：(&是文本连接符）
=A2&B2&C2&D2
=A2&"+"&B2&"+"&C2&"+"&D2
如果D列是日期，使用text函数 -- 把某个单元格从A格式转换为B格式
=A2&"+"&B2&"+"&C2&"+"&text(D2,"yyyy-mm-dd aaaa") -- 回车
-- 双击该单元格右下角，完成公式批量填充
3） 分列-规范日期格式
利用数据，分列中的激活日期的功能，完成规范日期格式的目的
首先选中要规范的日期列 -- 数据 -- 分列 -- 下一步 -- 下一步 -- 列数据格式 -- 日期（YMD） -- 完成
然后，继续选中该列 -- 右键 -- 设置单元格格式 -- 自定义 -- yyyy/mm/dd -- 确定
或者，继续选中该列 -- 右键 -- 设置单元格格式 -- 自定义 -- yyyy/mm/dd aaaa -- 确定
如果应对外国的月日在前，年在后的日期格式：
首先选中要规范的日期列 -- 数据 -- 分列 -- 下一步 -- 下一步 -- 列数据格式 -- 日期(MDY) -- 完成
章节2课时9系统导出的数据快速分开方法妙，基本功里看端倪
0） Ctrl + -，是“删除”的快捷键
选中多列，双击某两列的中间线，就能自动调整每列的宽度
1） 利用空格分列
需要注意，在其中选中连续空格作为单空格处理，来完成分列
特别注意“发生额”那一列， 利用“-”作为分隔符，把借款方和贷款方分开
2） 省市分开
方法一：
利用“省”，先把一列分割成两列，然后第一列的省份，就都缺少了一个省字
再利用公式：=A2&"省" ，给添加一个省字，然后点击生成的该单元格的右下键，完成批量公式填充
方法二：
利用Ctrl + H，查找“省”，替换成“省-”
再利用“-”作为分隔符，完成分列
章节2课时10空行工资条难倒很多人，随手辅助列三下五除二就搞定（制作工资条）
每行数据都需要加表头的处理方法：
1、在数据旁边另起一列（辅助列），从数据开始的地方编序号（一般为原序号2的位置），重复编两组；
第一个写1，第二个写2，按顺序填充下去。将所有数字再在下面复制一次。
2、对新编序号按照升序进行排序，这时每个数据下面就会自带一空行；此时清空右侧序号列；
鼠标点在辅助列某一格，排序，从小到大，目的是制造空行。
3、定位空值，将空行填充表头。
选中数据表所需数据列，进行定位（ctrl+G），在“定位条件”按钮里面选择“空值”，
在空白单元格里面输入“=”，鼠标点中表头大标题第一格，再左手按住“Ctrl”不松开，
右手按回车键，就会发现空白行表头完成自动添加；
4、此时接着上一步，按住选中的数据单元格，右键插入 -- 可建立多个空白行；
5、此时先选中数据的前三行 -- 双击格式刷 -- 然后用格式化刷剩余的数据，
来达到最终，给表的每行都添加一个表头的目的
章节2课时11排序功能再出手，每个物料下插入两个空白行其实很easy
1、右侧列辅助列，输入1、2，然后批量填充，向下粘贴该组数据两次，将鼠标放置该列有数字的位置
2，点击数据-排序-升序，
3，使用格式刷调整格式
章节2课时12去掉重复只3秒，下拉列表需要她，没听过芳名面羞涩
1、去重复：把需处理的数据项复制一份，到旁边，然后选择“数据”-“去重复”即可；
（行头修改为“指标”）
2、引用去重复后的数据，制作下拉菜单：
选中所需数据列，点击“公式”-“根据所选内容创建”；（公式--名称管理器--查看到刚才创建的东西）
然后点击“数据”-“数据验证”-“设置”-“允许”-选择序列，（忽略空值，提供下拉箭头）
“来源”，即为刚刚制作的公式里面的指标数据，可手输“=指标”。（或快捷键win+F3,调取来源）
从而，完成了让某个单元格，成为一个，唯一的不重复的各个指标的下拉列表
章节2课时13批量改掉文件名随心所欲，这个技巧必须学
批量改名：
1、 通过Excel结合Dos下的批处理命令： ren 旧名 新名 来进行批量改名；
2、具体操作：输入=“ren ”&A2&“.jpg ”&A2&“-”&B2&“.jpg”,做好一个单元格后双击到底；
3、复制重命名部分，新建文本文件1.txt拷贝内容至该文档，改名为1.bat,双击执行即可。
章节2课时14批量创建文件夹或得到文件夹里所有文件的名字，就这么奔逸绝尘
1、创建文件名字的列表-等于号-"MD"&+名字-双击到底-
复制-新建文本文档-粘贴-保存-改后缀名bat-双击执行
2、cmd： dir D:\Excel免费视频 /b > D:\Excel免费视频\表名.txt
/b表示该目录下的所有文件
3、cmd： dir D:\Excel免费视频\*.xlsx /b > D:\Excel免费视频\表名
文件夹的路径，都是用反斜线\
*.xlsx，就是限定只把该文件夹中的所有.xlsx文件的名字取出来
章节2课时15单列内容转多列，就这么简单任性
A列（单列）内容转多列；
1、在多列表格中第一行输入A2，A3，拉到A11；
下一列第一个输入A12，拉直最后，然后选中两行下拉至所需长度；
2、选中该区域，ctrl+H 查找A，替换为 =A ，替换全部，即转换为相应的A列内容
章节2课时16行内容蛛丝马迹的不同，这个绝技也能捕捉，神思妙哉
在一列不同数值的分隔处，插入空白行：
1、选中列内所有数据，标题不用选，然后复制，在旁边一列“错一位”粘贴
（原来序号为2的数据从新列的3号位置开始粘贴）；
2、选中两列中行内共有数据，“Ctrl”+“G”定位，
然后选择“行内容差异单元格”，在选出的单元格右键插入行即可。
章节2课时17指定内容分页打印，指哪打哪，就这个不起眼的功能施展妙手
利用分类汇总实现根据指定内容分页打印
1.先根据指定字段排序（比如“城市”字段） -- 这个也很重要
2.根据指定字段分类汇总，注意勾选每组数据分页（关键步骤）
数据 -- 分级显示 -- 分类汇总 -- 分类字段，选择“城市” -- 汇总方式，求和
-- 选定汇总项 -- 编号（选一个不能计算的字段） -- 注意勾选每组数据分页（关键步骤）
3.通过页面布局，在打印时，为每一页添加标题行（每页都添加表头）
页面布局 -- 打印标题 -- 顶端标题行 -- 选择要给每页添加的标题行
4.消除汇总行：数据》取消组合》清除分级显示；
5.选中在分页处含有空值的某一数据列，定位空值；ctrl-（删除整行）
章节2课时18高级筛选一出手，数据异同立即现真容，拽！
筛选出两个表的不同的数据：
1，点击一个空单元， -- 数据 -- 高级 -- 列表区域：选择内容多的表，条件区域选另一个表格 -- 确定
2，筛选后只显示两表相同部分，-- 开始 -- 标上颜色 -- 数据 -- 清除筛选效果即可看出区别
章节2课时19多列姓名转一列，简单等于就搞定或者多重透视来发功
0，筛选功能：
比如一列中有值，也有一些单元格是0
选中要筛选的内容 -- 数据 -- 筛选（ctrl + shift + L）-- 点第一格的下拉箭头
-- 筛选0 -- 确定 -- 就把所有的0筛选出来了 -- 这时就可以进行删除行，或别的操作
1，多行转单行：
方法一：
比如有第2到16行，10列（共150个数据）的数据；
然后在A17处填入“=B2”回车，在B17处填入“=C2”,再向右拉满此行
然后在A18处填入“=B3”回车，在B18处填入“=C3”,再向右拉满此行
然后选中拉满的这两行，向下一共拉（150 - 15）行，
就能看到第一列就是完整的数据了；
方法二（利用透视表）：
在第一列前面插入一列
然后Alt+D+p(先后分别按）-- 使用多重合并区域-数据透视表-现有工作表-只留值
数据透视表：ALT+ D, P，打开数据透视表， 多重合并计算数据区域
选定区域：整张表前需要先加一列，选定完成，只取“值”，拖入“行”
章节3课时20反向查询逆风飞扬，他和她搭配比翼双飞，天下无双
反向查询：一直第n列的值,反查第n-1列对一个的值是什么？
利用match和index函数
精确定位：MATCH{确定谁的位置，在哪行或哪列，0}
索引查询，交叉查询，INDEX{取数区域，行号，列号}
win + F4可用于锁定选中区域，防止下拉重复公式而改变区域范围（目的：始终固定查找区域）
1，查赵莉若所对应的部门：
=match -- Ctrl+A -- lockup_value -- 点赵莉若 -- lookup_array（查找区域） -- 选择C2:C10
选中lookup_array中的C2:C10 -- win + F4锁定选中区域 -- match_type -- 0 -- 代表精确查询 -- 确定
-- 生成公式：MATCH(G2,$C$2:$C$10,0)
2，剪切MATCH(G2,$C$2:$C$10,0) -- 输入index -- Ctrl+A --选定参数，选第一个，确定 --
Array -- 确定查询区域 -- 选定B2:B10 -- 选中array中的B2:B10 -- win + F4锁定选中区域
-- row_num -- 填入：MATCH(G2,$C$2:$C$10,0) -- column_num -- 填入1 -- 确定
3，双击右下角，完成批量填充
4，本质：=INDEX($B$2:$B$10,MATCH(G2,$C$2:$C$10,0),1)
章节3课时21交叉查询横纵定位锚定，精准无比锁定目标探囊取物
0）下拉框的形式是通过数据有效性（数据验证）来做的
数据验证 -- 设置 -- 允许“序列” -- 在来源处：选择某个序列区域
1）【交叉查询】
数据-数据验证-选中区域，
MATCH（要查的元素1，选中区域1，0），MATCH（要查的元素2，选中区域2）
INDEX(选中区域，Match1，Match2)
=INDEX( $B$2:$E$7, MATCH(I4,$A$2:$A$7,0), MATCH(J4,$B$1:$E$1,0))
章节3课时22再谈INDEX和MATCH索引查询，VLOOKUP和MATCH也很配哦
定义名称（就是选中一片区域，然后给这片区域命一个名字）:
方法1、快捷：选中数据区域，然后在最左上角的格子中键入“名字” -- 回车
方法2、公式 -- 定义名称 -- 在“名称”处：取新的名字 -- 在引用区域中：选中数据区域 -- 确定
数据验证（数据有效性）-- 允许：序列 -- 来源（选定区域）-- 用于制作下拉框
精确查询：
vllookup（根据谁来找，在哪里找，第几列，0/1）
精确定位：
match（定位谁的位置，在哪一行或者哪一列，0）
交叉查询：
index（查询的区域，行号，列号）
进行交叉查询：
方法一：
vllookup + match
最终公式：=VLOOKUP(J3, A2:G12, MATCH(K3,$B$1:$G$1,0)+1 ,0)
注意：
1，第三个参数要对列号 + 1
2，第一个参数：根据谁来找，比如根据J3中的城市来找；
3，第二个参数：在哪里找，需要把数据区域前面的城市列也要添加上；
4，第三个参数，要查找的东西在那一列
方法二：
index + match
最终公式：=INDEX($B$2:$G$12, MATCH(J3,$A$2:$A$12,0), MATCH(K3,$B$1:$G$1,0))
注意：
1，要选择区域时，如果事先定义了“区域名称” -- 可以按下快捷键win + F3，调出各个名称区域
章节3课时23星号确定位置也不难，截取所需内容看花招
方法1、FIND（查找某个东西）+MID（任意位置截取东西出来），需要变成数值后边+0
方法2、FIND+REPLACE（替换函数）
1.find（"查找的内容",A2,1）：在A2中查找所需内容是第几个字符
2.mid(A2,需要截取的长度) -- 这截取的是字符串
mid(A2,需要截取的长度)+0 ：+0才会使得字符串变成真正的数字
3.replace(A2,起始位置,结束位置,"新内容")：把A2中内容替换
方法一：
最终公式：=MID(A5,FIND("*",A5)+1,99)+0
注意：
1，mid的第三个参数：num_chars就是要截取的字符的个数
如果填入的数字，大于了剩余的所有字符串的长度，那么就是剩多少就截取多少
2，注意第二个参数要+1
方法二：
最终公式：=REPLACE(A2, 1, FIND("*",A2), "")
章节3课时24一串文本字符串里，找寻第一个字母的位置再截取，有点小难
1.search与searchb函数
search不区分单字节与双字节；
searchb函数区分单字节与双字节，即返回查找的位置在第几个字节
2.search函数可以使用？和*作为通配符；
* - 通配符 所有字符
？- 单字节字符（比如英文字符）
3.leftb与left的区别同上（单字节与双字节的区别）
比如leftb(6)就是截取前6个字节的字符
比如left(6)就是截取前6个字符
4.最终公式：
=LEFTB(A2,SEARCHB("?",A2)-1)
------- 提取第一个字母之前的内容；
章节3课时25快速填充Ctrl+E用起来欲罢不能，再讲下文本截取函数技法
提取文本中的数字
1.输入一个以后，ctrl+E（“快速填充”）
2.快速填充，本质上是按规律填充数据
这规律就是电脑根据你提供的结果而猜测的规律，所以可能在某些情况下并不全是你要的正确结果；结果不正确时需要人工干预进行修正
快速填充（ctrl + E）:
就是在某一列的第一格，填充自己想要的格式，然后ctrl+e，快速按照自己设定的格式快速填充整列；
1、LEN() ： 判断字符串的长度，按照字符个数来数
2、LENB()：判断字符串的长度，按字节个数来数
3、LEFT()：从左边截取
4、RIGHT()：从右边截取
5、=LENB(C2)-LEN(C2)：用来获得一个字符串中的中文字符的个数
6、=LEFT( C2, LENB(C2)-LEN(C2) )：用来截取最左边的几个长度的中文字符
7、=2*LEN(C2)-LENB(C2)：用来获得一个字符串中的英文字符的个数
8、=RIGHT( C2, 2*LEN(C2)-LENB(C2) )：用来截取最右边的几个长度的英文字符
尤其针对该节资料“产品名称”这一列的填充：
1）可以使用公式LEFT( C2, LENB(C2)-LEN(C2) )
1）可以使用ctrl + e快速填充
章节3课时26截取诗词里每个字出来，MID加COLUMN函数一顺到底
目标：把单元格中的一整句诗歌，每个字截取“填充”到另一个单元格各种；
使用手段：=MID(要截取的内容，从第几位开始截取，一共截取几位)
注意事项：
此函数第一个参数（也就是要截取的内容），要使用win + F4提前锁住列
方法一：
1） 在第一行上插入新行，然后从B列开始往后的列，通过拖拽填入1 2 3 4 ...
2） 在B2处：=MID($A2,B$1,1)
注意事项：
a) 可以看到该函数第一个参数的列都被锁住了；
a) 第二个参数只锁住了行，列可以随着拖拽而变化
3） 拖拽填充第2行，然后双击第2行的最右下角，将截取的内容填充整个区域
方法二：
答案：=MID($A2,COLUMN()-1,1)
1、 MID(要截取的内容，从第几位开始截取，一共截取几位)
3、COLUMN返回选中的单元格对应的列号
章节3课时27函数界最牛双侠，吹灰之力搞定复杂求和，洪荒之力留待将来
目的：
姓名列中取一个姓名，产品列中取一个产品，看着两个东西确定的值是多少；
函数界最牛双侠，吹灰之力搞定复杂求和
公式：
sumifs（求和区域，条件区域1，条件1，条件区域2，条件2，……）
sumproduct（（条件区域1=条件1）*（条件区域2=条件2）*……，求和区域）
方法一（使用多条件求和公式）：
=SUMIFS($D:$D, $B:$B, $G3, $C:$C,H$1)
注意点：
有的参数是全部锁定；有的参数是锁定的行；有的参数是锁定的列；
方法二：
=SUMPRODUCT( ($B$2:$B$21=$G2) * ($C$2:$C$21=H$1) , $D$2:$D$21)
注意点：
锁一整列，会使得速度欠缺；
所以，一般只锁有有效数据的列中哪些单元格
（ctrl + shift + 下箭头：快速选中有有效数据的单元格区间）
章节3课时28根据城市求订单的金额和订单的笔数，SUMIF和COUNTIF来也
目的：
实际就是想实现：“求和”与“统计个数”
单条件求和函数：sumif（条件区域，条件值，求和区域）
而且必须直接用数据源里已经存在的列作为条件区域；
单条件计数函数：countif（条件区域，条件值）
解答：
1） 先把城市列单独拷贝出来 -- 数据 -- 删除重复项
2） 某个城市的销售总金额： =SUMIF(B:B,G2,C:C)
3） 某个城市的总订单笔数： =COUNTIF(B:B,G2)
章节3课时29这个求和函数好牛一步到位一气呵成，SUMIF却步步为营加小心
目的：按照月份，进行分月份的求和汇总
方法一：
使用month()函数来先先创建“辅助列”，再使用单条件求和函数sumif()
1）用month函数提取月份后需要通过&“月”保证与条件区域保证格式上的一致
在C列建立辅助列：=MONTH(A:A)&"月"
2）在销量列：=SUMIF(C:C,F3,B:B)
方法二：
使用SUMPRODUCT((条件区域1=条件值1)*...*求和区域)，一步到位，完成按月份求和；
注意：
这个公式除了可以（条件区域1=条件值1），除了可以用=做“条件判断”，还可以用>, <, <> 等
1）在销量列：=SUMPRODUCT((MONTH($A$2:$A$1000)&"月"=$M3)*$B$2:$B$1000)
或者：=SUMPRODUCT((MONTH($A$2:$A$22)&"月"=$M3)*$B$2:$B$22)
注意：
=SUMPRODUCT((mouth($A$2:$A$22)&"月"=$M4)*$B$2:$B$22)
这种就不能成功，在公式不能写成小写；
章节3课时30数据分门别类各归各家，懂一个如果，就找到心之所属
目的：
费用按照部分，或按照花费性质自动归类
答案：
=IF($B:$B=E$2,$D3,"")
注意点就是： 何处要锁行，何处要锁列， “”代表空值
章节4课时32分列后多重合并透视技术，Power Query逆透视技术，哪家强
将二维表转换为一维表
方法一：
1）先 -- 数据 -- 分列
2）alt→P→D，打开数据透视表向导 -- 多重合并计算数据区域 -- 下一步 -- 下一步
-- 选中数据区域 -- 完成
3）在生成的透视表的最右下角单元格中间 -- 双击 -- 展开表明细表即可得到对应一维表
4）选中第二，第四列，Ctrl + - 删除列，
5）点击剩下的第二列第一格的下箭头 -- 选择“空白” -- 确定 -- 删除空白行
6）点击选中第一行的单元格 -- 数据 -- 筛选（取消筛选）
注意：
调整整张表各个列的宽度到合适的位置：
a）左上角点击选中整张表
b）选中任意两列的中间线，双击
4152
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
