Excel小技巧Handbook.docx

1、Excel小技巧Handbook目录目录 1关于超链接 2关于分列 4替换文字或数字 7页面设置 9保护 充许用户编缉区域 10保护工作簿 13处理工作表时的快捷键 15批注 15选择性粘贴 18冻结窗口: 22复制“所见即所得”: 24批量修改数据 25在Excel工具栏中添加一个计算器 27ROUND 29COUNTIF 30处理工作表时的快捷键 31subtotal函数 32双击填充柄填充一列： 34快速输入相同的数据： 35自动切换输入法 36单元格内容的合并 37HLOOKUP与VLOOKUP 38依条件计算求和SUMIF() 40IF: 43高级筛选 46单元格缩位显示 47行列互

2、换 48Round_指标拆分 错误！未定义书签。关于超链接问：如何在一个较大的Excel文件中，快速找到相应的内容？答：设定超链接步骤：1. 选定目标，选插入超链接（如图一）2. 选择本文本中的位置3. 在窗口中选择相对应的Sheet 确定4. Index中选定的目标显蓝色（如图二）5. 直接在Index中选择已设定超链接的内容，即可直接进入相对应的Sheet.例如：（图一）(图二)关于分列问：如何将只有一列的文本单元格改为多列？答：设置分列步骤：1. 选择含有文本的单元格区域。该区域可以有多行，但只能有一列。 2. 注意 选中列的右边必须有一个或多个空白列，否则选中列右边的数据将会被覆盖。

3、（如图一）3. 在“数据”菜单上，单击“分列”。（如图二） 4. 按照“文本分列向导”的指示来设定如何将文本拆分到列中。（如图三、图四）例如：图一图二图三图四替换文字或数字问：如何快速替换表格内的文字或数字答：查找替换步骤：1. 选择要搜索的单元格区域。如果要搜索整个工作表，请单击该工作表中的任何单元格。 2. 在“编辑”菜单上，单击“替换”。 3. 在“查找内容”框中，输入要搜索的文本或数字，或从“查找内容”下拉框中选择最近使用过的搜索。 4. 如果要指定搜索格式，请单击“格式”，然后在“查找格式”对话框中进行选择。如果“查找和替换”对话框中的“格式”不可用，请单击“选项”，再单击“格式”。

4、5. 单击“选项”以进一步定义搜索。例如，可搜索包含同类数据（如公式）的所有单元格。在“范围”框中，选择“工作表”或“工作簿”以搜索工作表或整个工作簿。 6. 在“替换为”框中，输入替换字符，并在需要时输入特定格式。7. 单击“查找下一个”。 8. 若要替换找到的字符的突出显示位置或所有出现位置，请单击“替换”或“全部替换”。例一 ：如何替换文字或数字。如下图表内所示，需要将2004替换为2005例二 ：如何替换公式函数内的数字。如下图表内所示，因增加一名ASM而需要重新设置排名：页面设置单击相应的工作表。 (一)：在“文件”菜单上，单击“页面设置”，再单击“工作表”选项卡。 (二)：1. 若

5、要在每一页上打印列标志，请在“顶端标题行”框的“打印标题”下，输入列标志所在行的行号，然后单击“打印”。 2. 若要在每一页上打印行标志，可在“左端标题列”框的“打印标题”下，输入行标志所在列的列标，然后单击“打印”。 保护 充许用户编缉区域含义:在表格中,只允许用户更改部份区域,例：这是一份员工入职离职的表格，A列是姓名，B列是入职日期，C列是离职日期，现需要将A.B两列设保护,C列即员工离职日期可随时根据实际情况作更改.首先 在 ”工具” 中选择 ”保护” 中 ”充许用户编缉区域” , 如图第二步,选择”新建”第三步,”引用单元格”中,选C这一列,说明这一列充许对方编缉,可不设密码第四步,

6、”确定”之后,选择”保护工作表”第五步,输入密码,”确定”后,重复输一次.操作完毕说明: 该密码是保护A.B两列的.保护工作簿目的：使用户不能对使用的工作簿进行删除、移动、复制。步骤：打开excel文档，工具栏-保护-保护工作簿-选中窗口-输入密码-密码确认-此操作完成。如下图表示：(一) 工具栏：（二）保护：（三）保护工作簿：处理工作表时的快捷键目的：方便用户处理工作表的快捷的操作步骤：1. 插入新的工作表 SHIFT + F112. 删除当前工作表 ALT + E L （注意字母先后顺序不可以颠倒，否则无法完成操作）3. 对当前工作表重新命名ALT + O H R（注意字母先后顺序不可以颠

7、倒，否则无法完成操作）4. 转移到工作簿下一张工作表CTRL + PAGEDOWN5. 转移到工作簿上一张工作表 CTRL + PAGEUP批注目的：附加在单元格中，与其他单元格内容分开的注释。批注是十分有用的提醒方式，例如注释复杂的公式如何工作，或为其他用户提供反馈。如何插入批注：1. 单击要加批注的单元格。 2. 在“插入”菜单中，单击“批注”。 3. 在弹出的批注框中键入批注文本。 4. 如果不想在批注中留有您的姓名，请选择并删除姓名。5. 完成文本键入后，请单击批注框外部的工作表区域。 查看工作簿中的所有批注1. 在“视图”菜单上，单击“批注”。 2. 若要查看所有批注，请执行下列一项

8、或多项操作： 3. 若要按顺序查看，请从所选单元格开始，单击“审阅”工具栏上的“下一批注” 。4. 若要以相反顺序查看，请单击“前一批注” 。 调整批注大小或移动批注1. 在需要更改批注的单元格上单击鼠标右键，在随后出现的快捷菜单中单击“显示批注”。 2. 单击批注框的边框，显示尺寸控点。 3. 具有尺寸控点的工作表批注4. 请执行下列操作之一：5. 更改尺寸 拖动批注框四边或角点上的尺寸控点。6. 移动批注 拖动批注框的边框。7. 如果隐藏并重新显示批注，它将返回到其默认位置。查看批注内容 Microsoft Excel 提供了几种查看批注的方法。含有批注的单元格的右上角有三角形的批注标识符

9、。如果指针停在含有标识符的单元格上，就会显示该单元格的批注。您也可以连续地显示批注（单条批注或是工作表上的所有批注）。“审阅”工具栏能使您按顺序逐条查看每项批注。您还可以将批注打印到工作表的相应位置，或在打印输出结果的底部将批注打印成列表。批注中的用户姓名 按用户的姓名来区分批注，就可以在共享工作簿或传送给几个用户的工作簿中，判断出键入每项批注的操作者。当合并几个工作簿的更改时，批注也被合并，这样单元格中所有的批注文字就会按顺序显示。 选择性粘贴用计算结果替换公式1. 通过用计算结果替换公式可永久冻结公式。如果要永久冻结公式的一部分，可以将这一部分替换为相应的计算值。2. 1.“选中包含公式的

10、单元格。 ”3. “如果公式是数组公式（数组公式：数组公式对一组或多组值执行多重计算，并返回一个或多个结果。数组公式括于大括号 ( ) 中。按 Ctrl+Shift+Enter 可以输入数组公式。），选定包含数组公式的单元格区域。 ”4. “操作方法”5. 单击数组公式（数组公式：数组公式对一组或多组值执行多重计算，并返回一个或多个结果。数组公式括于大括号 ( ) 中。按 Ctrl+Shift+Enter 可以输入数组公式。）中的某个单元格。 ”6. 在“编辑”菜单上，单击“定位”。 ”7. 单击“定位条件”。8. 单击“当前数组”。 ”9. 单击“复制” 。 ”10. “单击“常用”工具栏（

11、工具栏：带有按钮和选项的工具条，使用这些按钮和选项可执行命令。若要显示工具栏，可使用“自定义”对话框（指向“视图”菜单上的“工具栏”，单击“自定义”）。若要看到更多按钮，请单击工具栏结尾处的“工具栏选项”。）上的“粘贴” 。 ”11. “单击“粘贴选项” 旁的箭头，再单击“只有值”。 ”以计算结果替换公式的一部分1. 单击包含公式的单元格。 2. 在编辑栏（编辑栏：位于 Excel 窗口顶部的条形区域，用于输入或编辑单元格或图表中的值或公式。编辑栏中显示了存储于活动单元格中的常量值或公式。） 编辑栏 中，选定公式中需要用计算结果替换的部分。在进行选择时，请确认包含了整个运算对象（操作数：操作数

12、是公式中运算符任意一侧的项。在 Excel 中，操作数可以是值、单元格引用、名称、标签和函数。）。例如，如果选择一个函数，则必须选定整个函数名称、左圆括号、参数（参数：函数中用来执行操作或计算的值。参数的类型与函数有关。函数中常用的参数类型包括数字、文本、单元格引用和名称。）和右圆括号。 3. 如果要计算选定的部分，请按 F9 。 4. 如果要用计算结果替换选定的部分，请按 Enter。 5. 如果公式为数组公式（数组公式：数组公式对一组或多组值执行多重计算，并返回一个或多个结果。数组公式括于大括号 ( ) 中。按 Ctrl+Shift+Enter 可以输入数组公式。），请按 Ctrl+Shi

13、ft+Enter。 复制数据有效性设置1. 单击包含要复制的设置的单元格，再单击“复制” 。 2. 选定需要重复设置的单元格。 3. 在“编辑”菜单上，单击“选择性粘贴”。 4. 单击“有效性”选项。 5. 提示6. 在“选择性粘贴”对话框中，“全部”和“边框除外”选项也可粘贴数据有效性设置。冻结窗口:目的:可以同时查看工作表的两个部分冻结顶部水平窗格1. 选中待拆分处的下一行2. 选择窗口冻结窗格冻结左侧垂直窗格1. 选中待拆分处的右边一列2. 选择窗口冻结窗格同时冻结顶部和左侧窗格1. 选中待拆分处的右下方的单元格2. 选择窗口3. 冻结窗格撤销窗口:1. 选择窗口2. 撤销冻结窗格即可复

14、制“所见即所得”:目的:复制后的内容不包括源工作表的隐藏内容,使复制后的内容即源工作表中所见的内容步骤：如何找到选定可见单元格按钮:添加或删除按钮-自定义-编辑-选择选定可见单元格1. 选中要复制的区域2. 按选定可见单元格按钮3. 按ctrl+c4. 在所要复制的工作表中按ctrl+v批量修改数据在EXCEL表格数据都已被填好的情况下，如何方便地对任一列（行）的数据进行修改呢？比如我们做好一个EXCEL表格，填好了数据，现在想修改其中的一列（行），例如：想在A列原来的数据的基础上加8，有没有这样的公式？是不是非得手工的一个一个数据地住上加？对于这个问题我们自然想到了利用公式，当你利用工式输入

15、A1=A1+8时，你会得到EXCEL的一个警告：“MICROSOFTEXCEL不能计算该公式”只有我们自己想办法了，这里介绍一种简单的方法：第一步：在想要修改的列（假设为A列）的旁边，插入一个临时的新列（为B列），并在B列的第一个单元格（B1）里输入8。第二步：把鼠标放在B1的或下角，待其变成十字形后住下拉直到所需的数据长度，此时B列所有的数据都为8。第三步：在B列上单击鼠标右键，“复制” B列。第四步：在A列单击鼠标的右键，在弹出的对话框中单击“选择性粘贴”，在弹出的对话框中选择“运算”中的你所需要的运算符，在此我们选择“加”，这是本方法的关键所在。第五步：将B列删除。怎么样？A列中的每个数

16、据是不是都加上了8呢？同样的办法可以实现对一列（行）的乘，除，减等其它的运算操作。原表格的格式也没有改变。此时整个工作结束，使用熟练后，将花费不到十秒钟。在Excel工具栏中添加一个计算器您知道吗？你可以在Microsoft Excel的工具栏上添加一个计算器。操作方法如下： 点击视图菜单的工具栏，然后点击自定义。 点击命令选项卡。 在类别列表中，点击工具，在命令列表中，点击自定义（灰色计算器图标）。 将所选的命令从命令列表中拖至某工具栏中，当您看到鼠标指针旁出现一个加号（+）时，松开鼠标。 点击关闭。 现在，点击刚刚添加的按钮，Excel将启动计算器。ROUND目的:返回某个数字按指定位数取

17、整后的数字。语法ROUND(number,num_digits)Number 需要进行四舍五入的数字。Num_digits 指定的位数，按此位数进行四舍五入。 说明 如果 num_digits 大于 0，则四舍五入到指定的小数位。 如果 num_digits 等于 0，则四舍五入到最接近的整数。 如果 num_digits 小于 0，则在小数点左侧进行四舍五入。12345AB公式说明（结果）=ROUND(2.15, 1)将 2.15 四舍五入到一个小数位 (2.2)=ROUND(2.149, 1)将 2.149 四舍五入到一个小数位 (2.1)=ROUND(-1.475, 2)将 -1.475

18、 四舍五入到两小数位 (-1.48)=ROUND(21.5, -1)将 21.5 四舍五入到小数点左侧一位 (20)COUNTIF目的: 计算区域中满足给定条件的单元格的个数。语法COUNTIF(range,criteria)1. Range 为需要计算其中满足条件的单元格数目的单元格区域。2. Criteria 为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、32、32 或 apples。说明Microsoft Excel 提供其他函数，可用来基于条件分析数据。例如，若要计算基于一个文本字符串或某范围内的一个数值的总和，可使用 SUMIF 工作

19、表函数。若要使公式返回两个基于条件的值之一，例如某指定销售量的销售红利，可使用 IF 工作表函数。AB数据数据苹果32柑桔54桃75苹果86公式说明（结果）=COUNTIF(A2:A5,apples)计算第一列中苹果所在单元格的个数 (2)=COUNTIF(B2:B5,55)计算第二列中值大于 55 的单元格个数 (2)处理工作表时的快捷键目的：方便用户处理工作表的快捷的操作步骤： 插入新的工作表 SHIFT + F11 删除当前工作表 ALT + E L （注意字母先后顺序不可以颠倒，否则无法完成操作） 对当前工作表重新命名ALT + O H R（注意字母先后顺序不可以颠倒，否则无法完成操作

20、） 转移到工作簿下一张工作表CTRL + PAGEDOWN 转移到工作簿上一张工作表 CTRL + PAGEUP 以文本格式存储的数值转换为数字格式选中需转换的单元格；点击方框内的“！”：选择“转换为数字”subtotal函数对需分类汇总的函数进行排序；点击“数据”里面的“分类汇总”；在对话框中选择汇总类型及范围；在已经汇总的表格里，插入嵌套汇总：在“分类汇总”中选择需要汇总的字段，确定不要选中“替换当前分类汇总”框；点击“确定”即出现嵌套汇总；在筛选时，subtotal不会计算筛选掉的数据。双击填充柄填充一列：在Excel中，单元格或单元区域的右下角会有一个小方型，这就是填充柄。在一个工作表

21、中已建立了数据表中，如果某一列要填入相同的数据或公式，可以使有用鼠标拖动填充柄。在该列的第一个单元格内输入常量或公式（不必选定区域）。然后将鼠标指向填充柄双击，则该列从输入单元格到数据区的最后一行就都填充了数据（常量或公式）。快速输入相同的数据：在Excel中，常常遇到同一工作表的单元格区域输入相同的数据，只要选定要输入的数据区域，随后键入数据。按住Ctrl键的同时按回车即可。在多张工作表的相同位置输入相同数据。实现起来也很容易：选定要输入相同数据的其他工作表后。同上面所述的Ctrl键+回车的方法即可。自动切换输入法 在一张工作表中，往往是既有数据，又有文字，这样在输入时就需要来回在中英文之间

22、反复切换输入法，非常麻烦。 如果你要输入的东西很有规律性，比如这一列全是单词，下一列全是汉语解释，你可以用以下方法实现自动切换。方法是：选中要输入英文的列，单击“数据”菜单，选择“有效性.”命令，在弹出的“数据有效性”对话框中，选中“输入法模式”选项卡，在“模式”框中选择“关闭（英文模式）”命令，单击“确定”按钮（如图12）。图12单元格内容的合并根据需要，有时想把B列与C列的内容进行合并，如果行数较少，可以直接用“剪切”和“粘贴”来完成操作，但如果有几万行，就不能这样办了。 解决办法是：在C行后插入一个空列（如果D列没有内容，就直接在D列操作），在1中输入“=B1&C1”，D1列的内容就是B

23、、C两列的和了。选中D1单元格，用鼠标指向单元格右下角的小方块“”，当光标变成后，按住鼠标拖动光标向下拖到要合并的结尾行处，就完成了B列和C列的合并。这时先不要忙着把B列和C列删除，先要把D列的结果复制一下，再用“选择性粘贴”命令，将数据粘贴到一个空列上。这时再删掉B、C、D列的数据。合并不同单元格的内容，还有一种方法是利用CONCATENATE函数，此函数的作用是将若干文字串合并到一个字串中，具体操作为“=CONCATENATE（B1,C1）”。若要合并的数据需要用“/”隔开的话，应在“/”号前加上双引号，。如图HLOOKUP与VLOOKUP目的：根据某一行的数据，查找下面给定行中的数据或者

24、根据某一列的数据，查找右边列中的数据语法形式：HLOOKUP(依据某行，查询范围，查询第几行，是否要找到完全相同的值)VLOOKUP(依据某列，查询范围，查询第几列，是否要找到完全相同的值)例：（一）：HLOOKUP 例：（二）VLOOKUPSHEET-TYPE的内容依条件计算求和SUMIF()语法: SUMIF(准则范围，条件，求和范围）例子：需要计算Q1和Q2每位PSR/ASM的指标与实际销量的总和。（举例中的所有数据非真实情况）首先，将Q1和Q2每位PSR/ASM的指标和实际销量复制到一份空白表格中，如图，1至17排是Q1的数据，18至33是Q2的数据第二步，设公式SUMIF(姓名范围,

25、姓名,数据范围),可将姓名设为座标,有利于复制 这是A2这个代表Q1和Q2的指标总和。（为方便大家阅读，将7至33排隐藏）第三步，计算实际销量的总和（同样隐藏了7至33排）第四步，以下的人员均用复制的方式第五步，注意检查年中入职和离职的员工(如果每季度的人员数量不同的情况)。 操作完毕。IF:IF 按条件查询ISBLANK() 显示所查询的单元格是否为空格举例:BUILDING (87179) 成都市妇幼保健院欧阳, 红许, 桂BUILDING (91142) 成都市疾病预防控制中心黄, 容娜李, 柯刘, 利吴, 学庆BUILDING (91151) 成都市疾病预防控制中心二办公区允, 仲良B

26、UILDING (91143) 成都市金牛区疾病预防控制中心陈, 继红胡, 榕于, 彤余, 彤周, 蓓欣要求：把上表（原始表）改变成一个新表下表的格式姓名医院欧阳, 红 (87179) 成都市妇幼保健院许, 桂 (87179) 成都市妇幼保健院黄, 容娜 (91142) 成都市疾病预防控制中心李, 柯 (91142) 成都市疾病预防控制中心刘, 利 (91142) 成都市疾病预防控制中心吴, 学庆 (91142) 成都市疾病预防控制中心允, 仲良 (91151) 成都市疾病预防控制中心二办公区陈, 继红 (91143) 成都市金牛区疾病预防控制中心胡, 榕 (91143) 成都市金牛区疾病预防

27、控制中心于, 彤 (91143) 成都市金牛区疾病预防控制中心余, 彤 (91143) 成都市金牛区疾病预防控制中心周, 蓓欣 (91143) 成都市金牛区疾病预防控制中心步骤一、在原始表的C列做ISBLANK的公式；例如，C1单元格设置公式“ISBLANK(B1)”，结果显示FALSE表示B1为非空格，TRUE表示B1为空格结果为BUILDING (87179) 成都市妇幼保健院FALSE欧阳, 红TRUE许, 桂TRUEBUILDING (91142) 成都市疾病预防控制中心FALSE黄, 容娜TRUE李, 柯TRUE刘, 利TRUE吴, 学庆TRUEBUILDING (91151) 成都

28、市疾病预防控制中心二办公区FALSE允, 仲良TRUEBUILDING (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) 成都市疾病预防控制中心二办