Excel小技巧Handbook.docx
《Excel小技巧Handbook.docx》由会员分享，可在线阅读，更多相关《Excel小技巧Handbook.docx（56页珍藏版）》请在冰豆网上搜索。
Excel小技巧Handbook
目录
目录1
关于超链接2
关于分列4
替换文字或数字7
页面设置9
保护—充许用户编缉区域10
保护工作簿13
处理工作表时的快捷键15
批注15
选择性粘贴18
冻结窗口:
22
复制“所见即所得”:
24
批量修改数据25
在Excel工具栏中添加一个计算器27
ROUND29
COUNTIF30
处理工作表时的快捷键31
subtotal函数32
双击填充柄填充一列：
34
快速输入相同的数据：
35
自动切换输入法36
单元格内容的合并37
HLOOKUP与VLOOKUP38
依条件计算求和SUMIF（）40
IF:
43
高级筛选46
单元格缩位显示47
行列互换48
Round_指标拆分错误！
未定义书签。
关于超链接
问：
如何在一个较大的Excel文件中，快速找到相应的内容？
答：
设定超链接
步骤：
1.选定目标，选插入→超链接（如图一）
2.选择本文本中的位置
3.在窗口中选择相对应的Sheet→确定
4.Index中选定的目标显蓝色（如图二）
5.直接在Index中选择已设定超链接的内容，即可直接进入相对应的Sheet.
例如：
（图一）
（图二）
关于分列
问：
如何将只有一列的文本单元格改为多列？
答：
设置分列
步骤：
1.选择含有文本的单元格区域。
该区域可以有多行，但只能有一列。
2.注意 选中列的右边必须有一个或多个空白列，否则选中列右边的数据将会被覆盖。
（如图一）
3.在“数据”菜单上，单击“分列”。
（如图二）
4.按照“文本分列向导”的指示来设定如何将文本拆分到列中。
（如图三、图四）
例如：
图一
图二
图三
图四
替换文字或数字
问：
如何快速替换表格内的文字或数字
答：
查找替换
步骤：
1.选择要搜索的单元格区域。
如果要搜索整个工作表，请单击该工作表中的任何单元格。
2.在“编辑”菜单上，单击“替换”。
3.在“查找内容”框中，输入要搜索的文本或数字，或从“查找内容”下拉框中选择最近使用过的搜索。
4.如果要指定搜索格式，请单击“格式”，然后在“查找格式”对话框中进行选择。
如果“查找和替换”对话框中的“格式”不可用，请单击“选项”，再单击“格式”。
5.单击“选项”以进一步定义搜索。
例如，可搜索包含同类数据（如公式）的所有单元格。
在“范围”框中，选择“工作表”或“工作簿”以搜索工作表或整个工作簿。
6.在“替换为”框中，输入替换字符，并在需要时输入特定格式。
7.单击“查找下一个”。
8.若要替换找到的字符的突出显示位置或所有出现位置，请单击“替换”或“全部替换”。
例一：
如何替换文字或数字。
如下图表内所示，需要将2004替换为2005
例二：
如何替换公式函数内的数字。
如下图表内所示，因增加一名ASM而需要重新设置排名：
页面设置
单击相应的工作表。
（一）：
在“文件”菜单上，单击“页面设置”，再单击“工作表”选项卡。
（二）：
1.若要在每一页上打印列标志，请在“顶端标题行”框的“打印标题”下，输入列标志所在行的行号，然后单击“打印”。
2.若要在每一页上打印行标志，可在“左端标题列”框的“打印标题”下，输入行标志所在列的列标，然后单击“打印”。
保护—充许用户编缉区域
含义:
在表格中,只允许用户更改部份区域,
例：
这是一份员工入职离职的表格，A列是姓名，B列是入职日期，C列是离职日期，现需要将A.B两列设保护,C列即员工离职日期可随时根据实际情况作更改.
首先在”工具”中选择”保护”中”充许用户编缉区域”,如图
第二步,选择”新建”
第三步,”引用单元格”中,选C这一列,说明这一列充许对方编缉,可不设密码
第四步,”确定”之后,选择”保护工作表”
第五步,输入密码,”确定”后,重复输一次.操作完毕
说明:
该密码是保护A.B两列的.
保护工作簿
目的：
使用户不能对使用的工作簿进行删除、移动、复制。
步骤：
打开excel文档，工具栏----保护----保护工作簿----选中窗口----输入密码----密码确认----此操作完成。
如下图表示：
（一）工具栏：
（二）保护：
（三）保护工作簿：
处理工作表时的快捷键
目的：
方便用户处理工作表的快捷的操作
步骤：
1.插入新的工作表SHIFT+F11
2.删除当前工作表ALT+EL（注意字母先后顺序不可以颠倒，否则无法完成操作）
3.对当前工作表重新命名 ALT+OHR（注意字母先后顺序不可以颠倒，否则无法完成操作）
4.转移到工作簿下一张工作表 CTRL+PAGEDOWN
5.转移到工作簿上一张工作表CTRL+PAGEUP
批注
目的：
附加在单元格中，与其他单元格内容分开的注释。
批注是十分有用的提醒方式，例如注释复杂的公式如何工作，或为其他用户提供反馈。
如何插入批注：
1.单击要加批注的单元格。
2.在“插入”菜单中，单击“批注”。
3.在弹出的批注框中键入批注文本。
4.如果不想在批注中留有您的姓名，请选择并删除姓名。
5.完成文本键入后，请单击批注框外部的工作表区域。
查看工作簿中的所有批注
1.在“视图”菜单上，单击“批注”。
2.若要查看所有批注，请执行下列一项或多项操作：
3.若要按顺序查看，请从所选单元格开始，单击“审阅”工具栏上的“下一批注”。
4.若要以相反顺序查看，请单击“前一批注”。
调整批注大小或移动批注
1.在需要更改批注的单元格上单击鼠标右键，在随后出现的快捷菜单中单击“显示批注”。
2.单击批注框的边框，显示尺寸控点。
3.具有尺寸控点的工作表批注
4.请执行下列操作之一：
5.更改尺寸 拖动批注框四边或角点上的尺寸控点。
6.移动批注 拖动批注框的边框。
7.如果隐藏并重新显示批注，它将返回到其默认位置。
查看批注内容 MicrosoftExcel提供了几种查看批注的方法。
含有批注的单元格的右上角有三角形的批注标识符。
如果指针停在含有标识符的单元格上，就会显示该单元格的批注。
您也可以连续地显示批注（单条批注或是工作表上的所有批注）。
“审阅”工具栏能使您按顺序逐条查看每项批注。
您还可以将批注打印到工作表的相应位置，或在打印输出结果的底部将批注打印成列表。
批注中的用户姓名 按用户的姓名来区分批注，就可以在共享工作簿或传送给几个用户的工作簿中，判断出键入每项批注的操作者。
当合并几个工作簿的更改时，批注也被合并，这样单元格中所有的批注文字就会按顺序显示。
选择性粘贴
用计算结果替换公式
1.通过用计算结果替换公式可永久冻结公式。
如果要永久冻结公式的一部分，可以将这一部分替换为相应的计算值。
2.1.“选中包含公式的单元格。
”
3.“如果公式是数组公式（数组公式：
数组公式对一组或多组值执行多重计算，并返回一个或多个结果。
数组公式括于大括号（{}）中。
按Ctrl+Shift+Enter可以输入数组公式。
），选定包含数组公式的单元格区域。
”
4.“操作方法”
5.单击数组公式（数组公式：
数组公式对一组或多组值执行多重计算，并返回一个或多个结果。
数组公式括于大括号（{}）中。
按Ctrl+Shift+Enter可以输入数组公式。
）中的某个单元格。
””
6.在“编辑”菜单上，单击“定位”。
””
7.单击“定位条件”。
8.单击“当前数组”。
””
9.单击“复制”。
”
10.“单击“常用”工具栏（工具栏：
带有按钮和选项的工具条，使用这些按钮和选项可执行命令。
若要显示工具栏，可使用“自定义”对话框（指向“视图”菜单上的“工具栏”，单击“自定义”）。
若要看到更多按钮，请单击工具栏结尾处的“工具栏选项”。
）上的“粘贴”。
”
11.“单击“粘贴选项”旁的箭头，再单击“只有值”。
”
以计算结果替换公式的一部分
1.单击包含公式的单元格。
2.在编辑栏（编辑栏：
位于Excel窗口顶部的条形区域，用于输入或编辑单元格或图表中的值或公式。
编辑栏中显示了存储于活动单元格中的常量值或公式。
）编辑栏中，选定公式中需要用计算结果替换的部分。
在进行选择时，请确认包含了整个运算对象（操作数：
操作数是公式中运算符任意一侧的项。
在Excel中，操作数可以是值、单元格引用、名称、标签和函数。
）。
例如，如果选择一个函数，则必须选定整个函数名称、左圆括号、参数（参数：
函数中用来执行操作或计算的值。
参数的类型与函数有关。
函数中常用的参数类型包括数字、文本、单元格引用和名称。
）和右圆括号。
3.如果要计算选定的部分，请按F9。
4.如果要用计算结果替换选定的部分，请按Enter。
5.如果公式为数组公式（数组公式：
数组公式对一组或多组值执行多重计算，并返回一个或多个结果。
数组公式括于大括号（{}）中。
按Ctrl+Shift+Enter可以输入数组公式。
），请按Ctrl+Shift+Enter。
复制数据有效性设置
1.单击包含要复制的设置的单元格，再单击“复制”。
2.选定需要重复设置的单元格。
3.在“编辑”菜单上，单击“选择性粘贴”。
4.单击“有效性”选项。
5.提示
6.在“选择性粘贴”对话框中，“全部”和“边框除外”选项也可粘贴数据有效性设置。
冻结窗口:
目的:
可以同时查看工作表的两个部分
冻结顶部水平窗格
1.选中待拆分处的下一行
2.选择窗口 " 冻结窗格
冻结左侧垂直窗格
1.选中待拆分处的右边一列
2.选择窗口 " 冻结窗格
同时冻结顶部和左侧窗格
1.选中待拆分处的右下方的单元格
2.选择窗口
3.冻结窗格
撤销窗口:
1.选择窗口
2.撤销冻结窗格即可
复制“所见即所得”:
目的:
复制后的内容不包括源工作表的隐藏内容 ,使复制后的内容即源工作表中所见的内容
步骤：
如何找到"选定可见单元格"按钮:
添加或删除按钮-->自定义-->编辑-->选择"选定可见单元格
1.选中要复制的区域
2.按"选定可见单元格"按钮
3.按ctrl+c
4.在所要复制的工作表中按ctrl+v
"
批量修改数据
在EXCEL表格数据都已被填好的情况下，如何方便地对任一列（行）的数据进行修改呢？
比如我们做好一个EXCEL表格，填好了数据，现在想修改其中的一列（行），例如：
想在A列原来的数据的基础上加8，有没有这样的公式？
是不是非得手工的一个一个数据地住上加？
对于这个问题我们自然想到了利用公式，当你利用工式输入A1=A1+8时，你会得到EXCEL的一个警告：
“MICROSOFT EXCEL不能计算该公式……”只有我们自己想办法了，这里介绍一种简单的方法：
第一步：
在想要修改的列（假设为A列）的旁边，插入一个临时的新列（为B列），并在B列的第一个单元格（B1）里输入8。
第二步：
把鼠标放在B1的或下角，待其变成十字形后住下拉直到所需的数据长度，此时B列所有的数据都为8。
第三步：
在B列上单击鼠标右键，“复制”B列。
第四步：
在A列单击鼠标的右键，在弹出的对话框中单击“选择性粘贴”，在弹出的对话框中选择“运算”中的你所需要的运算符，在此我们选择“加”，这是本方法的关键所在。
第五步：
将B列删除。
怎么样？
A列中的每个数据是不是都加上了8呢？
同样的办法可以实现对一列（行）的乘，除，减等其它的运算操作。
原表格的格式也没有改变。
此时整个工作结束，使用熟练后，将花费不到十秒钟。
在Excel工具栏中添加一个计算器
您知道吗？
你可以在MicrosoftExcel的工具栏上添加一个计算器。
操作方法如下：
∙点击视图菜单的工具栏，然后点击自定义。
∙
∙
∙点击命令选项卡。
∙
∙在类别列表中，点击工具，在命令列表中，点击自定义（灰色计算器图标）。
∙
∙将所选的命令从命令列表中拖至某工具栏中，当您看到鼠标指针旁出现一个加号（+）时，松开鼠标。
∙点击关闭。
现在，点击刚刚添加的按钮，Excel将启动计算器。
ROUND
目的:
返回某个数字按指定位数取整后的数字。
语法
ROUND（number,num_digits）
Number 需要进行四舍五入的数字。
Num_digits 指定的位数，按此位数进行四舍五入。
说明
如果num_digits大于0，则四舍五入到指定的小数位。
如果num_digits等于0，则四舍五入到最接近的整数。
如果num_digits小于0，则在小数点左侧进行四舍五入。
1
2
3
4
5
A
B
公式
说明（结果）
=ROUND（2.15,1）
将2.15四舍五入到一个小数位（2.2）
=ROUND（2.149,1）
将2.149四舍五入到一个小数位（2.1）
=ROUND（-1.475,2）
将-1.475四舍五入到两小数位（-1.48）
=ROUND（21.5,-1）
将21.5四舍五入到小数点左侧一位（20）
COUNTIF
目的:
计算区域中满足给定条件的单元格的个数。
语法
COUNTIF（range,criteria）
1.Range 为需要计算其中满足条件的单元格数目的单元格区域。
2.Criteria 为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。
例如，条件可以表示为32、"32"、">32"或"apples"。
说明
MicrosoftExcel提供其他函数，可用来基于条件分析数据。
例如，若要计算基于一个文本字符串或某范围内的一个数值的总和，可使用SUMIF工作表函数。
若要使公式返回两个基于条件的值之一，例如某指定销售量的销售红利，可使用IF工作表函数。
A
B
数据
数据
苹果
32
柑桔
54
桃
75
苹果
86
公式
说明（结果）
=COUNTIF（A2:
A5,"apples"）
计算第一列中苹果所在单元格的个数
（2）
=COUNTIF（B2:
B5,">55"）
计算第二列中值大于55的单元格个数
（2）
处理工作表时的快捷键
目的：
方便用户处理工作表的快捷的操作
步骤：
●插入新的工作表SHIFT+F11
●删除当前工作表ALT+EL（注意字母先后顺序不可以颠倒，否则无法完成操作）
●对当前工作表重新命名 ALT+OHR（注意字母先后顺序不可以颠倒，否则无法完成操作）
●转移到工作簿下一张工作表 CTRL+PAGEDOWN
●转移到工作簿上一张工作表CTRL+PAGEUP
●以文本格式存储的数值转换为数字格式
选中需转换的单元格；
点击方框内的“！
”：
选择“转换为数字”
subtotal函数
对需分类汇总的函数进行排序；
点击“数据”里面的“分类汇总”；
在对话框中选择汇总类型及范围；
在已经汇总的表格里，插入嵌套汇总：
在“分类汇总”中选择需要汇总的字段，确定不要选中“替换当前分类汇总”框；
点击“确定”即出现嵌套汇总；
在筛选时，subtotal不会计算筛选掉的数据。
双击填充柄填充一列：
在Excel中，单元格或单元区域的右下角会有一个小方型，这就是填充柄。
在一个工作表中已建立了数据表中，如果某一列要填入相同的数据或公式，可以使有用鼠标拖动填充柄。
在该列的第一个单元格内输入常量或公式（不必选定区域）。
然后将鼠标指向填充柄双击，则该列从输入单元格到数据区的最后一行就都填充了数据（常量或公式）。
快速输入相同的数据：
在Excel中，常常遇到同一工作表的单元格区域输入相同的数据，只要选定要输入的数据区域，随后键入数据。
按住Ctrl键的同时按回车即可。
在多张工作表的相同位置输入相同数据。
实现起来也很容易：
选定要输入相同数据的其他工作表后。
同上面所述的Ctrl键+回车的方法即可。
自动切换输入法
在一张工作表中，往往是既有数据，又有文字，这样在输入时就需要来回在中英文之间反复切换输入法，非常麻烦。
如果你要输入的东西很有规律性，比如这一列全是单词，下一列全是汉语解释，你可以用以下方法实现自动切换。
方法是：
选中要输入英文的列，单击“数据”菜单，选择“有效性...”命令，在弹出的“数据有效性”对话框中，选中“输入法模式”选项卡，在“模式”框中选择“关闭（英文模式）”命令，单击“确定”按钮（如图12）。
图12
单元格内容的合并
根据需要，有时想把B列与C列的内容进行合并，如果行数较少，可以直接用“剪切”和“粘贴”来完成操作，但如果有几万行，就不能这样办了。
解决办法是：
在C行后插入一个空列（如果D列没有内容，就直接在D列操作），在Ｄ1中输入“=B1&C1”，D1列的内容就是B、C两列的和了。
选中D1单元格，用鼠标指向单元格右下角的小方块“■”，当光标变成"＋"后，按住鼠标拖动光标向下拖到要合并的结尾行处，就完成了B列和C列的合并。
这时先不要忙着把B列和C列删除，先要把D列的结果复制一下，再用“选择性粘贴”命令，将数据粘贴到一个空列上。
这时再删掉B、C、D列的数据。
合并不同单元格的内容，还有一种方法是利用CONCATENATE函数，此函数的作用是将若干文字串合并到一个字串中，具体操作为“=CONCATENATE（B1,C1）”。
若要合并的数据需要用“/”隔开的话，应在“/”号前加上双引号，。
如图
HLOOKUP与VLOOKUP
目的：
根据某一行的数据，查找下面给定行中的数据
或者根据某一列的数据，查找右边列中的数据
语法形式：
HLOOKUP（依据某行，查询范围，查询第几行，是否要找到完全相同的值）
VLOOKUP（依据某列，查询范围，查询第几列，是否要找到完全相同的值）
例：
（一）：
HLOOKUP
例：
（二）VLOOKUP
SHEET-TYPE的内容
依条件计算求和SUMIF（）
语法:
SUMIF（准则范围，条件，求和范围）
例子：
需要计算Q1和Q2每位PSR/ASM的指标与实际销量的总和。
（举例中的所有数据非真实情况）
首先，将Q1和Q2每位PSR/ASM的指标和实际销量复制到一份空白表格中，如图，1至17排是Q1的数据，18至33是Q2的数据
第二步，设公式SUMIF（姓名范围,姓名,数据范围）,可将姓名设为座标,有利于复制
这是A2这个代表Q1和Q2的指标总和。
（为方便大家阅读，将7至33排隐藏）
第三步，计算实际销量的总和（同样隐藏了7至33排）
第四步，以下的人员均用复制的方式
第五步，注意检查年中入职和离职的员工（如果每季度的人员数量不同的情况）。
操作完毕。
IF:
IF按条件查询
ISBLANK（）显示所查询的单元格是否为空格
举例:
BUILDING
（87179）成都市妇幼保健院
欧阳,红
许,桂
BUILDING
（91142）成都市疾病预防控制中心
黄,容娜
李,柯
刘,利
吴,学庆
BUILDING
（91151）成都市疾病预防控制中心二办公区
允,仲良
BUILDING
（91143）成都市金牛区疾病预防控制中心
陈,继红
胡,榕
于,彤
余,彤
周,蓓欣
要求：
把上表（原始表）改变成一个新表——下表的格式
姓名
医院
欧阳,红
（87179）成都市妇幼保健院
许,桂
（87179）成都市妇幼保健院
黄,容娜
（91142）成都市疾病预防控制中心
李,柯
（91142）成都市疾病预防控制中心
刘,利
（91142）成都市疾病预防控制中心
吴,学庆
（91142）成都市疾病预防控制中心
允,仲良
（91151）成都市疾病预防控制中心二办公区
陈,继红
（91143）成都市金牛区疾病预防控制中心
胡,榕
（91143）成都市金牛区疾病预防控制中心
于,彤
（91143）成都市金牛区疾病预防控制中心
余,彤
（91143）成都市金牛区疾病预防控制中心
周,蓓欣
（91143）成都市金牛区疾病预防控制中心
步骤一、
在原始表的C列做ISBLANK的公式；例如，C1单元格设置公式“＝ISBLANK（B1）”，结果显示FALSE表示B1为非空格，TRUE表示B1为空格
结果为
BUILDING
（87179）成都市妇幼保健院
FALSE
欧阳,红
TRUE
许,桂
TRUE
BUILDING
（91142）成都市疾病预防控制中心
FALSE
黄,容娜
TRUE
李,柯
TRUE
刘,利
TRUE
吴,学庆
TRUE
BUILDING
（91151）成都市疾病预防控制中心二办公区
FALSE
允,仲良
TRUE
BUILDING
（91143）成都市金牛区疾病预防控制中心
FALSE
陈,继红
TRUE
胡,榕
TRUE
于,彤
TRUE
余,彤
TRUE
周,蓓欣
TRUE
步骤二、
在原始表D列做IF公式，目的――在每个医生姓名的同一行显示他所在医院的名称
例如在D2单元格，设公式“＝IF（C2=TRUE,D1,B2）”表示如果C格显示为TRUE（空格），就在D2显示为D1的医院名称；如果C2不为TRUE，就是FLASE，那么D2显示为B2的医院名称。
或者可以在D2单元格设置公式“＝IF（C2=FALSE,B2,D1）”，也是一样的
结果为
BUILDING
（87179）成都市妇幼保健院
FALSE
（87179）成都市妇幼保健院
欧阳,红
TRUE
（87179）成都市妇幼保健院
许,桂
TRUE
（87179）成都市妇幼保健院
BUILDING
（91142）成都市疾病预防控制中心
FALSE
（91142）成都市疾病预防控制中心
黄,容娜
TRUE
（91142）成都市疾病预防控制中心
李,柯
TRUE
（91142）成都市疾病预防控制中心
刘,利
TRUE
（91142）成都市疾病预防控制中心
吴,学庆
TRUE
（91142）成都市疾病预防控制中心
BUILDING
（91151）成都市疾病预防控制中心二办
升级会员 