Excel进阶(2)

（一）批量改文件名

原来有4张图片，在Excel中用& 连接符 写代码，将其复制到一个txt中，

  

修改txt 的后缀为bat, 回车，双击这个文件，就会把原来的4张图片自动改名。

     

注意：文件夹及文件夹选项，可以勾选是否显示文件的后缀。win10中，直接上方有菜单可以勾选。

实例：批量创建文件夹

="MD "&A2， 得到代码。任然按照之前的，复制到txt, 改名为bat 后缀，双击运行即可得到5个文件夹。

实例：得到文件夹里所有文件的名字

比如显示D盘下免费视频文件夹下， /b 表示所有文件，显示到 表名.txt中。如果表名.txt没有，则会自动新建。注意有空格！

 可以限定文件的类型.

运行方法：在开始菜单中输入 cmd  ，回车，将上述代码粘贴一下，回车，没有提示就说明成功了。

（二）单列转化为多列（矩阵）

A列有100个名字，想要把它放到10*10中。输入A2，A3，向右拉，第二行A12，A13，再向右拉。

选中这两组，往下拉，得到

然后，ctrl+H ，替换，将A替换为=A， 全部替换，大功告成！

Q：上述反过来，想把多列内容变为一列，如何？

下面有10列*15行的名字。

输入=B2，向右拉。第十列没有，0，不用管。选中这一行，往下拉，拉到151行即可。

其中的逻辑自己想吧...比较easy.

**如果上述10列中，每一列的长短不一，那么按此方法得到中间会有很多0， 可以利用定位删除。

另一种方法，利用数据透视表：插入一列，A列。按下ALt+D+P，三个键要依次一个个按下，弹出对话框，勾选多重合并计算区域--选择显示区域。

   

去掉四个钩子 ，把 值 拉到行标签位置，得到全部人名。

    

（三）插入空行

想要在不同城市之间插入一个空行，选中这一列，复制粘贴，错位粘贴！！

         

选区域（关键），从A3开始一直选到最后，Ctrl+G ,定位。选择行内容差异。得到了差异行，右键-插入-整行。

（四） 打印

实例：一个城市打印在一页，每一页添加标题。

先对城市进行排序--数据：分类汇总，弹出对话框，分类字段选择 城市，汇总字段，勾选每组数据分页。每一页的标题设置之前讲过了，页面布局-打印标题中进行设置。

 

Q：如果我不想要汇总行，怎么处理？

数据-分级显示--取消混合--清除分级显示；选中C列（有空格的列）， Ctrl+G , 定位空值，Ctrl - 删除功能，删除整行。

（五）高级筛选比较数据的差异

列表区域是比较全的（内容多的）区域；条件区域是少的。

得到筛选结果，可以对此标记颜色。

  

清除筛选后，发现，标记颜色的就是在E列出现的城市，没有被标记的就是E列中没有的城市。

*若你想知道条件区域中，Criteria 那个公式，公式--名称管理器中，会存放这个公式！

（六） 字符串函数

1. match+index

实例：想知道某个人属于哪个部门。

match（确定谁的位置，在哪行或者哪列，0），0 表示精确定位。

match(G2, $C$2:$C$10,0)=6。

再用index, 索引查询函数，index(取数区域、行号、列号)， index($B$2:$B$10，match(G2, $C$2:$C$10,0)，1)， 列号也可以不写。

实例： 交叉查询。已知一张城市不同产品的销售额的二维表，想在产品名称、城市处勾选，自动得到销售额。

注意实现勾选功能，数据--数据验证--允许（序列）--来源。

思路：index的行号由 产品名称确定，列号由 城市确定。

行号：match(H2,$A$2:$A$7,0);  列号：match(I2,$B$1:$E$1,0)。

得到 index($B$2:$E$7, match(H2,$A$2:$A$7,0),match(I2,$B$1:$E$1,0))。

小功能： 可以自定义一个区域的名称，选中后，在左上方框框中输入名字，回车。

 

也可以选中区域后，公式--定义名称。

现在定义三个区域：城市、年份、销量。现在依旧要实现上述功能。

**方法一：vlookup+match.

vlookup（根据谁来找，在哪里找，结果是第几列，0）

vlookup(J3,$A$2:$G$12,match(K3,$B$2:$G$2,0)+1,0) 

或者vlookup(J3,$A$2:$G$12,match(K3,年份,0)+1,0) ， vlookup的搜素区域一定要使得第一列是 J3的集合，因此后面的年份列号+1.

**方法二：index+match

 index(销量, match(J3,城市,0),match(K3,年份,0))

2. 字符串的截取：find+mid、replace

想得到*后面的数字。

find(要找的东西，从哪里找，从第一个开始找)，第三个参数可以不写,默认从第一个开始找。

find("*",A2,1) 得到*所在的位置。

再用mid 截取函数，mid(从哪里截取，开始位置，截取几位).

mid(A2,find("*",A2,1) +1,4), 4位，假设*号后面的位数最多是4. 得到的结果是文本形式的数字。如何让它变成数字？mid(A2,find("*",A2,1) +1,4)+0， 原来的公式中+0进行一个运算就行了。或者加两个--，负负得正，--mid(A2,find("*",A2,1) +1,4)。

不用mid, 也可以用replace(旧东西，开始位置，结束位置，新东西)

replace(A2,1,find("*",A2,1),"") 。得到的结果也是文本形式的数字。也可以是replace(A2,1,find("*",A2,1),)， 逗号一定要在的！！ 

实例：search--得到商品名字.

就是想要得到第一个字母前面的文字。

search；serachb , 只找单字节 。

小功能：在一个单元格中输入=search , ctrl+A，得到函数参数。

* 表示任意字符，？ 表示单个字符。

searchb(找什么，从哪里找，开始位置)

searchb("?",A2,1)=7，因为榨汁机占据了6个字节。

截取用leftb, 按照字节算，如果按照字符算则用left.  left（原文本，从左边开始截取几位）.

leftb(A2,searchb("?",A2,1)-1)； 或者left(A2,(searchb("?",A2,1)-1)/2).

（六）Ctrl+E 快速填充

实例：抠出备注中的数字 

2013版本之后的，你在B列输入157，移动到B3单元格，也可以停留在B2单元格, 按下Ctrl+E，自动会填充数字！！Ctrl+E 涉及自动填充中的快速填充。但是会有错误，并非全部正确。

Ctrl +E 很多快速填充的功能。例如，城市加英文，邮箱，产品去掉一个-号，手动输入D2，Ctrl+E即可。

其他字符函数：lenb(C2) 按照字节来数 ; len(C2)按照字符来数。

实例：抠字

mid（要截取的内容，从第几位截取，总共截取几位）。

但是第二个参数 从第几位截取 是在变的，可以增加辅助行写上1234。如何一步到位？用column函数，可以返回单元格的列号, 也可以不带参数=column() 。

上述例子中在B1输入=mid($A1,column(A$1),1)

（七）条件函数

多条件求和：sumifs(求和区域，条件区域1，条件1，条件区域2，条件2，……)；

sumproduct((条件区域1=条件1)*(条件区域2=条件2)*……，求和区域)

求出每个人卖每种裤子的销量。H2中输入。条件 在 条件区域中。

还可以用sumproduct(($B$2:$B$20=$G2)*($C$2:$C$20=H$1), $D$2:$D$20)

实例：统计城市的销量和比销量笔数。

 

已知数据有很多重复的城市，现在需要汇总。

选复制下 第二列，假设复制到G，数据-删除重复项-得到唯一的城市。

sumif(条件区域，条件值，求和区域)。counti(条件区域，条件值)。

统计销售总额： 在H2中，=sumif(B:B，G2，C:C) 。

统计销售笔数：在I2中，=countif(B:B，G2)。

实例：统计每个月的销量

   

属于单条件的统计求和，用sumif，但是sumif必须直接用数据源里已经存在的列作为条件区域。

增加辅助列C，=month(A2)=11 ,取得月份，或者 month(A2)&“月”。然后用sumif($C$2:$C$100,$F3,$B$2:$B$100)。

或者用=SUMPRODUCT((E2=$C$2:$C$100)*1,$B$2:$B$100)   或者=SUMPRODUCT((E2=$C$2:$C$22)*$B$2:$B$22)。

 if 函数

实例：对不同部门、不同项目的报销金额分类，得到E-M的表。

if(如果这是真的，就A，否则B)

E3中输入=if($B3=E$2, $D3,"").

I3中输入=if($C3=I$2, $D3,"").

（八）规划求解

实例：从A列中挑出3个数字，总和等于给定的数字C1=50，如何挑出这三个数字？

 

选项-加载项-转到，勾选规划求解加载项。这样在数据菜单下就有了规划求解。

但是这个功能限制很多，用到的单元格最多200个。B列的数字不是0就是1，最后A与B两列相乘求和如果是2500就行了。假设现在有50个数，事先需要先把公式写好，B1就是B2：B51的和，E1就是A列与B列的sumproduct.

规划求解对话框：目标值所在单元格，可变单元格为B2开始的B列。

两个约束条件： B1=3，B2到B51只能为0或1，x选择二进制bin。

这里用单纯线性规划更好，非线性很慢！

（九）透视表

实例：已知如下数据，区域都是用斜杠分开的，现在想要得到表格（右边）。

   

方法一：普通方法

数据分列，以斜杠为分隔符，得到如下

 

依次按下Alt，D，P，多重合并计算区域，得到透视表，下左图，

   

双击最后总计的58，得到右上图，自行调整一下空格等就行了。

方法二：PowerQUERY，2016以上版本才有。

 PowerQUERY-从表--选全部表格的区域--查询编辑器--拆分列（类似数据分列）

    

选中除了第一列之外的，逆透视，得到结果。

实例：已知左边数据，想要得到透视表如下。

  

插入透视表--拉一些字段到行字段、列字段。右击--数据透视表选项，可以调整布局和格式，比如合并居中。

实例：制作切片器：实现产品的切换。

切片器实现一个产品一个表，切片器必须2010版本以上，需要保存成xlsx后缀。

小功能介绍：随便选一个区域内单元格，插入--表格--会得到智能表格，这样可以通过拖拽增加行、列。

透视表可以回到早期版本的经典模式，直接拖进来，更加直观，右键--透视表选项---显示--经典布局。

   

 将日期转化为月，右键创建组，月。

    

得到结果

制作切片器

随便放在透视表一个单元格中，分析--插入切片器--比如勾选城市、产品。调整下切片器格式（选项-列）。

 

切片器中的选择不同，按住ctrl还可以多选，透视表会变化。可以增加透视图：分析-透视图，更加直观。

（十）图表自由切换--动态图表制作

我们需要开发工具--插入--表单控件的第二个。

拉出两个下拉菜单，右键-设计控件样式。

  

注意这里的数据源区域一定要是列的形式，所以产品需要别处转置一下。

单元格链接随便选一个，用来显示顺序，之后会用到，代替行号。

A13中输入=INDEX(A2:A5,$D$11)，右拉得到

 

现在可以随着下拉菜单变化而变化了。

下面再做产品的表格。=INDEX(B1:E1,,$F$11)

 

分别插入图表，得到柱形图。

 

自己调整下布局即可。两个控件拉到合适位置。

 

转载于:https://www.cnblogs.com/xuying-fall/p/10837893.html