[开发工具]EXCEL常用操作

一、快速填充与拆分

1. 如果需要提取表格中的数据位置具有高度相似，可以先进行两个数据的输入，然后选中数据以及下面的单元格，按ctr+E实现快速填充（在使用快速填充的时候一定要保证被填充附近要有数据，不能有完全空白的一行，否则会因不讲他们看作同一部分找不到数据而报错）实现对数据的拆分合并
2. 拆分位于数据菜单栏下可以实现对一个单元格中的内容按照特定的符号进行拆分或利用特定的长度，内置的符号需要是位于英文状态下的（注意是保存成数字还是文本的形式）
3. 使用Ctrl+Shift+下，实现表中数据全选

二、快速分析：

1、选中数据区域可使用快速分析的命令，快捷键ctr+Q，选择将所选中的部分所含的数据进行图的表示。

（1）格式化：

• 数据条：快速找出数据中的最大值以及最小值，并将数据所在的表格使用带颜色的条状根据数据的大小调整长短进行填充
• 色阶：用绿色表示较大的值，使用红色表示较小的值
• 图标集：绿色向上的箭头表示数值的大小位于前1/3，向下的红色箭头表示位于低1/3，黄色的向右箭头表示位于中间的部分
• 大于：对于含大于设定值的部分的单元格实现颜色的改变
• 文本包含：对于含有所查找的内容的单元格进行颜色的变换

（2）可以实现表格内的迷你图（可以在设计选项中实现对于样式的控制，标记最高点，最低点）

三、数据的录入

1、推荐使用横向录入数据的形式，使用Tab键实现单元格向右侧的移动，Shift+Tab实现左侧的移动，Enter会将光标移动到下一行的开头，Ctrl+Enter实现原位的填充（会将输入的内容填充到所有选中的单元格），而使用方向键进行移动时，按Enter会移动到此单元格的下一位不会跳到开头

2、对于日期的输入方式，年月日中间使用-或/进行分隔，进行筛选时系统只会识别相应的格式Ctrl+;实现对于当前日期的快速录入，Ctrl+Shift+;实现对于当前时间的快速录入

3、使用开始菜单栏上面的百分比符号，会将小数直接转换成对应百分数的表示形式，附近有更改小数位数的选项

4、分数的录入使用0 1/5的形式，直接输入1/5会被识别为日期的格式

5、EXCEL数字的精度最高只有十五位，如果需要进行多位的保存录入，需要进行数字转文本的形式（在开始命令的中间部分的下拉菜单中可以找到）左上角会出现小三角代表数字是文本格式，无法进行计算

6、使用EXCEL从网上进行表格数据的爬取，数据菜单栏有从网站获取数据的选项（可以实现实时的更新，数据菜单栏下的全部刷新，连接属性选项中可以找到定时刷新的选项）可以使用power query进行编辑筛选，实现单元格的拆分，每个步骤单独存在类似于PS的历史记录，可以进行删除但不影响其他的操作

7、Ctrl+T将表转换成超级表，数据中有来自表格的选项可以进行power query操作。还可使用筛选功能对特定的行进行删除操作

四、单元格格式

1、文本默认是左对齐，数字默认是右对齐，逻辑值默认居中对齐

2、对于单元格格式的设置可以在开始选项中，点击字体/对齐方式/数字选项栏的右下角的扩展可以进行设置，快捷键Ctrl+1

3、在对齐方式中可以选择自动换行，避免因数据过长超出单元格的部分被隐藏

4、页面布局中可以找到网格线的查看，实现对网格线显示的开关

5、双击某一列的右边框可以将单元格的长度自动调整为合适的宽度

6、数字转文本前加‘，文本转数字左上角黄色感叹号

7、数字格式的自定义

?8、文本自定义@为重复次数“ ”内部为附加的内容，会将数字自动转化成文本格式

?9、实现对于颜色的控制，以及对于数字范围的判断，判断时一定要包含所有的情况

?

?10、关于日期的格式定义

?五、数据验证

1、位于数据菜单栏中的数据验证，可以规范填入表格中的数据类型

2、对于不同类型的数据加以约束，输入信息部分会在选中单元格的时候显示，提醒使用者应该填入什么样的数据，出错警告给予出错后的弹窗提示

3、允许选项中的数据选项可以提供下拉菜单，提供特定的选项供选择

4、也可以对于之前没有加限制的数据进行筛选，选中单元格加入相应的限制条件，此时数据并不会随之改变，再选中数据验证下的圈释无效数据就可以将数据筛选出来

5、对于复制粘贴可能会修改单元格的格式导致之前的限制条件无效

?六、条件格式的应用

1、开始菜单栏中的条件格式可以对选中的区域进行操作，按数值的范围，改变背景颜色，添加标记，绘制表内图等，可以使用管理规则对标记符号的添加规则进行设置。可以适应动态的变化

2、可以配合筛选选择出有标记或修改颜色需要的部分

七、查找和选择

1. 在EXCEL中图片等信息会被认作为对象，可以使用查找与选择模块中的选择对象进行框选操作，不影响单元格的内容。或使用定位条件实现对于所有同类目标的选取
2. 使用快捷键Ctrl+F调出查找与匹配的窗口
3. 查找内容只要含所需内容的单元格都会被筛选出来，可以使用选项中的操作更加精确的规定单元格中的内容，以及对于格式的筛选

八、视图

1. Ctrl+N快速新建工作簿，一个工作簿可以含有多个工作表，在视图菜单栏的切换窗口选项可以进行工作簿的选取
2. 使用新建窗口和全部重排实现影子工作簿的创建，并可以同时观看多个工作表中的内容，对于工作簿的修改也是相通的
3. 使用Ctrl点击下方的多个工作表可以创建工作表组，对于一个的修改其他关联表的相同位置也会发生相同的变化，点击其他的工作表实现退出
4. 选中一个单元格双击上边框就会跳转到最上方，双击下边框会跳转到最后，左右也会实现对应的跳转。还可以使用Ctrl+相应的方向键实现相同的操作
5. 使用冻结窗格中冻结首行可以将标题保持悬停上方，不会因向下滚动消失
6. 冻结窗格可以实现向下移动选中单元格上方不变，向右移动左方单元格保持不变

九、打印的小技巧

1. 先在文件中选定需要打印的区域，然后在打印时选择打印选定区域，实现特定区域的打印
2. 当列数多余一张纸可以包括的区域，选择缩放，将所有的列调整为一页
3. 使用页面设置页边距的选项实现表格的居中打印
4. 打印标题可在页面布局中找到

十、引用

1. 绝对引用和相对引用
2. 在单元格输入数据对应的行列号，敲击回车就会将单元格中的数据替换成目标单元格中的数据，此时拖拽右下角的小方块就会基于那个单元格的位置对后面数据进行填充
3. 在行号或列号前面加入$符号就会将行或列实现固定，在进行函数操作的时候可以选中引用的单元格的行列号搭配F4实现快捷的锁定

十一、函数

1. 使用=函数名称来实现函数的调用，使用Tab键进入函数（推荐不熟悉的函数）
2. 函数之间也可以实现嵌套
3. 使用<>号来代表不等于
4. 文本连接符&可以使用"<"&F8保证坐标会被被解释称对应的数值，因为<号默认是文本类型会将后面的也带成文本，无法参与比较
5. 通配符的使用

• *任意通配符，张*代表姓张的人
• ?代表一个字符
• ~转义字符，假如想要搜索*号，需要加~进行转义

十二、计数求和的实现

1. 常用的统计函数（大多直接进入函数，选择统计区域，敲击回车就可实现）

• COUNT可以统计列表中含有数字的单元格的个数
• COUNTA可以返回非空单元格的个数
• COUNTBLANK返回空单元格的个数
• COUNTIF(选中区域,"筛选条件")选中区域中的符合筛选值的数目会返回，直接输入数值就是等于，使用>，<，>=，<=，<>来进行范围条件的筛选。
• COUNTIFS()和上面的用法几乎一样，但可以同时设置多个限制条件

2、选中所有的数据包含需要求和的格子，使用alt+=实现对于行或列的求和

• sum函数，使用Tab进入函数后单击单元格就可以选中数据加,进行分隔数据，也可以进行连选。区域中遇到文本内容会直接跳过
• average在求平均值的时候，对于空白选项不会计入总数个数，对于0是有效数据会将总数值加一
• =A+B+C直接相加，ABC代表对应的单元格
• sumif()函数实现筛选，并对另一或同一区域内的数据进行求和，需要三个参数，进行判断的区域，判断条件，进行求和的区域。判断和求和的区域应对应在同一行。限定条件也可以是引用单元格内部的内容
• sumifs()函数使用方法类似于sumif但可以进行多个条件的限制
• averageif()与上述的参数配置一致
• sumxproduct()函数将选中的两个数组，对应位置数据进行相乘，最后再将乘得的多个结果进行求和输出最后的结果

1. 报错：#VALUE使用sum的时候，目标数据如果含有文本型数字会发生报错
2. #REF因单元格被删除，直接相加操作找不到对应的数据，使用sum可以应对动态的改变