章节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)选中任意两列的中间线,双击