excel常用小操作

excel小操作

ctrl+H:查找和替换

ctrl+G：定位
ctrl+enter：批量填充

alt+=：求和

数据-分列：分开内容/也可以改格式
合并：&
格式：text(目标位置，“yyyy/mm/dd aaaa”)日期

ctrl+1：自定义单元格格式

ctrl+-：删除

每行加空格：123123升序排列

去重复：数据-删除重复项
制作下拉框：公式-根据所需内容创建-首行
数据-数据有效性(数据验证)-允许-序列

="ren "&A2&".jpg "&A2&"-"&B2&".jpg"
DOS系统批处理文件：ren 旧名 新名
批量改名 bat文件

批量创建文件夹：MD 名字
dir D:\xxx /b > D:\yyy\xx.txt
显示D盘下xxx的所有文件到xx.txt中
dir D:\xxx\*.xlsx /b > D:\yyy\xx.xls
显示D盘下xxx所有的xlsx文件到xx.xls中

单列转多列：顺序写位置在替换为=位置

不同间插入空白行：复制错位黏贴，定位行内容差异，全选中后插入整行

指定内容分页打印：先指定内容分类，每组数据分页
页面布局-打印标题：加上标题
汇总行取消：数据-取消组合-取消分级组合
            定位-空值-删除整行

比较两个表数据异同：VLOOKUP函数
数据-高级-高级筛选 再数据-清除-排序 公式-名称管理器

多列转一列：第一列最后=第二列第一个，右-下拉开到全在第一列
数据-筛选-删除-再取消筛选
透视表方法：插空列：alt-d-p分别按下-多重合并目标区域-放的位置-透视表字段-值拉到行

逆向查询：MATCH(确定谁的位置，在哪行或哪列，0)
F4：锁定
ctrl+A：打开函数
索引查询，交叉查询：INDEX(取数区域，行号，列号)

交叉查询
index+match

命名：左上角或公式-定义名称
精确查找：VLOOKUP(根据谁来找，在哪里查找，结果是第几列，0)-列用一个match
MATCH(确定谁的位置，在哪行或哪列，0)--确定VLOOKUP和INDEX中的行/列
交叉查询：INDEX(取数区域，行号，列号)-行列用2个match

(Fn)F3:打开名称

截取名称：FIND(标志，位置，开始位置(一般不写))-找到的是标值所在的位置
MID(位置，开始位置(由find确定)+1，截几位(看情况保证全截到))-截取标志后面的内容，是text形式+0变为数字
REPLACE(原有的，开始替换位置，取代的内容长度，新的取代(去掉则为""空，不写也行))-得到也为文本

找第一个字母的位置截取
SEARCH-不区分单双字节
SEARCHB-只找单字节
SEARCH()--支持？和*作为通配符
英文字母都是单字节，汉字算2个字节用SEARCHB
SEARCHB("?"(英文?表示找单字节),查找位置)
LEFTB(截取位置，截取几位)
LEFT不区分单双字节

文本截取
ctrl+E：快速填充2013版本以上(电脑猜测，可能出错)改原始数据后不会自动更改，有问题人为修正一部分后能自动修正
LEN():按字符数，不区分单双字节
LENB():汉字算双字节
2个一起用可得出汉字个数，和LEFT，RIGHT截取前后汉字字母

截取每个字：MID函数
MID(要截取的内容，从第几位开始截取，一共截取几位)
COLUMN()返回当前的列号

复杂(多条件)求和：
SUMIFS(求和区域，条件区域1,条件1,条件区域2,条件2，...)
SUMPRODUCT((条件区域1=条件1)*(条件区域2=条件2)*...,求和区域)

根据城市求订单金额和笔数(单条件)
先从数据-删除重复项
单条件求和：SUMIF(条件区域，条件值，求和区域)
单条件计数：COUNTIF(条件区域，条件值)
开始-千分位间隔

求和函数：
单条件：SUMIF(条件区域，条件值，求和区域)，必须直接用数据源里已经存在的列作为条件区域
不直接可以做辅助列，如日期求月份：=MONTH(日期位置)&"月"
SUMPRODUCT((条件区域1=条件1)*(条件区域2=条件2)*...,求和区域)--可以一步到位

数据分类：
IF(如果这是真的，就这么办，否则这么办)

规划求解：
数据-规划求解
在文件-选项-加载项中调出
200个变量
目标 可变单元格 约束 单纯线性规划

分列后多重合并透视技术：
power query插件
先数据-分列
顺序按下alt+d+p-多重合并目标区域-选定区域-双击总计再调整
筛选-删除-取消筛选
power query-从表-查询编辑器-分列-逆透视-调整-应用并关闭

汇总框：
插入-数据透视表-将对应的拉到对应位置
右键单击-数据透视表选项-布局和格式调整

动态图表(切片器)：
数据透视表-右键-创建组
2010版本以上xlsx文件保存
数据透视表工具-插入切片器
数据透视表工具-分析-数据透视图

图表切换：
文件-选项-自定义功能区-调出开发工具
开发工具-插入-表单控件
-设置控件格式
插入-图表