Excel操作技巧与实例(大全)
Excel 操作技巧
Skills About Excel
版本：A Rev.20120820 （2012-8-20 初版）
Email ：jungwongc
目录
1 操作技巧 ....................... 错误！未定义书签。
1.1 关于换行A LT+E NTER ........................................ 错误！未定义书签。
1.2 快速在多上合计行中求和 .............. 错误！未定义书签。
1.3 不复制隐藏的行或列 ................ 错误！未定义书签。
1.4 查找通配符 .................... 错误！未定义书签。
1.5 文本处理 ...................... 错误！未定义书签。
1.5.1 等长文本的分割. ............................. 错误！未定义书签。
1.5.2 不等长文本的分割. .......................... 错误！未定义书签。
1.5.3 文本的合并. ................................. 错误！未定义书签。
1.5.4 文本转数值的10种办法. ..................... 错误！未定义书签。
1.5.5 把引号中的字符串“ .Trends in Genetics 23 (4): 192 –9“中的前半部
分
英文提取出来..................... 错误！未定义书签。
1.5.6 使用分列更改文本为数值. .................... 错误！未定义书签。
1.6 高级筛选 ...................... 错误！未定义书签。
1.6.1 如何将一列数据中在另外一列出现过的项目筛选出来错误！未定义书签。
1.7 条件格式 ...................... 错误！未定义书签。
1.8 数据透视表 .................... 错误！未定义书签。
1.8.1 应用数据透视表进行各类数据分析. .......... 错误！未定义书签。
1.8.2 同时显示数据与百分比：........... . 错误！未定义书签。
1.8.3 筛选数据：. ............................... 错误！未定义书签。
1.8.4 显示明细数据：. ........................... 错误！未定义书签。
1.8.5 自动更新：. ............................... 错误！未定义书签。
1.8.6 透视图. .................................. 错误！未定义书签。
1.8.7 多重合并计算数据. ....................... 错误！未定义书签。
1.9 名称的引用 .................... 错误！未定义书签。
1.10 自定义格式语法： ................. 错误！未定义书签。
1.11 动态表头： ..................... 错误！未定义书签。
1.12 行与列的合计有时出现不相等的情况。........ 错误！未定义书签。
1.13 表格的几种类型及其处理技巧............ 错误！未定义书签。
1.14 快速删除工作表中所有的文本框.......... 错误！未定义书签。
2 函数数组 ....................... 错误！未定义书签。
2.1 函数宝典 ...................... 错误！未定义书签。
2.2 自定义函数157个.................................. 错误！未定义书签
2.3 V L OOKUP ........................................................ 错误！未定义书签
2.4 O FFSET .......................................................... 错误！未定义书签
2.5 IS 函数....................... 错误！未定义书签
2.6 数组公式特征.................... 错误！未定义书签
2.7 SQL语句在EXCEL中的典型用法 ........ 错误！未定义书签
3 图表......................... 错误！未定义书签3.1 复合饼图 ..................... 错误！未定义书签
3.2 图表组合..................... 错误！未定义书签
3.3 甘特图....................... 错误！未定义书签
3.4 带滚动条的图表................... 错误！未定义书签
3.5 动态图表的制作................... 错误！未定义书签
4 数据分析....................... 错误！未定义书签
4.1 数据有效性..................... 错误！未定义书签
4.1.1 防止重复录入. ............................ 错误！未定义书签
4.1.2 只能输入日期. ............................ 错误！未定义书签
4.1.3 输入的内容中必须包括某字符. ................ 错误！未定义书签
4.1.4 制作二级选项菜单：. ....................... 错误！未定义书签
4.2 整合数据表..................... 错误！未定义书签4.3 自定义数字格式的实用案例............... 错误！未定义书签
4.4 批量取消合并单元格并使用C TRL+E NTER填充数据 ... 错误！未定义书签
4.5 隐藏小于3000 的数值................ 错误！未定义书签
4.6 将一列数据转化为多列数据 .............. 错误！未定义书签
4.7 将多列数据转化为一列 ............... 错误！未定义书签
4.8 合并计算汇总多表数据 ............... 错误！未定义书签4.9 利用合并计算核对数值型数据 ............ 错误！未定义书签
5 实例技巧....................... 错误！未定义书签
5.1 各种各样的排名汇总 ................ 错误！未定义书签
5.2 资产折旧 ..................... 错误！未定义书签
5.3 巧设单元格格式打印席卡 .............. 错误！未定义书签
操作技巧
关于换行Alt+Enter
同一单元格内，有些长数据或条列式内容必须强行换行才能对齐。方法是光标移到在需要换行的位置上同时按下Alt+Enter 键（使用强行换行时，系统会同时选择自动换行功能）。
1．输入数据随时换行
用户若要在输入数据时换行，只要通过Alt+Enter 组合键即可轻松实现。此方法同样可使已输入内容的单元格在光标所在处换行。
2．单元格区域内换行
将某个长行转成段落并在指定区域内换行。例如：A10 内容很长，欲将其显示在A列至C列之内，步骤是：选定区域A10:C12（先选A10），选择“编辑→填充→内容重排”，A10内容就会分布在A10:C12 区域中。此法特别适合用于表格内的注释。
3．调整单元格格式换行
选定单元格，选择“格式→单元格”，在弹出的对话框中单击“对齐”，选中“自动换行”复选框，单击[ 确定] 按钮即可。
4. 录制一个宏, 定义快捷键
快速在多上合计行中求和
选择区域--- 按F5--- 选择“空值” --- 按“ Alt+=" 快捷键。
快速在多上合计行中求和.gif
不复制隐藏的行或列
a 首先选中需要复制的被隐藏了一些行或列的表格区域；
b 然后点击“编辑” - “定位” - “定位条件”，在其中选择“可见单元格”；
c 复制表格区域，粘贴即可。
查找通配符
在Excel 中，如何替换单元格中的* 、? 号呢？由于二者在“查找和替换”对话框中扮演通配符的角色，代表多个、单个任意字符，而要替换这两个符号本身，需要在前面增加一个~符号，如附图所示。同理要替换“ ~”符号时，也需要在其前面增加一个“ ~”符号。
文本处理
等长文本的分割
从字符串的左边取字符：=Left （字符串，文本长度）
从字符串的右边取字符：=Right （字符串，文本长度）
从字符串的中间取字符：=Mid （字符串，文本起始位置，文本长度）不等长文本的分割
第1 步：选中要进行分割的字符串区域。
第2 步：点击“数据”－“分列”，在文本分列向导中选择适合
的分隔符
第3 步：设置需要导入的列以及放置该列的位置
文本的合并
=Concatenate（文本1，文本2，?）
使用连接符&：
文本转数值的10 种办法
Excel 操作技巧与实例(大全)
把引号中的字符串“ .Trends in Genetics 23 (4): 192 –9“中
的前半部分英文提取出来
使用数组公式=LEFT(A1,MIN(FIND(ROW(1:10)-1,A1&1/17))-1)
也可使用公式=LEFT(A1,MIN(FIND(ROW(1:10)-
1,A1&"0123456789"))-1)
说明：1/17 是一个包含了0 到9 的数字小数。目的是在原数据的后面连接上“ 0123456789"，以避免find 时出错。这是简化公式的常用手法，与此类似作用的还有5^19。
使用分列更改文本为数值
如果你发现在Excel 中输入的公式计算结果不正确，也许是单元格中保存的数字被保存为文本类型的原因。这时需要将文本类型的数这转为数值，方法如下：