Excel高级使用技巧

1、编辑技巧

1 编辑技巧

（1） 分数的输入

如果直接输入“1/5"，系统会将其变为“1月5日"，解决办法是：先输入“0"，然后输入空格，再输入分数“1/5"。

（2） 序列“001"的输入

如果直接输入“001"，系统会自动判断001为数据1，解决办法是：首先输入“'"（西文单引号），然后输入“001"。

（3） 日期的输入　

如果要输入“4月5日"，直接输入“4/5"，再敲回车就行了。如果要输入当前日期，按一下“Ctrl+；"键。

（4） 填充条纹如果想在工作簿中加入漂亮的横条纹，可以利用对齐方式中的填充功能。先在一单元格内填入“*"或“~"等符号，然后单击此单元格，向右拖动鼠标，选中横向若干单元格，单击“格式"菜单，选中“单元格"命令，在弹出的“单元格格式"菜单中，选择“对齐"选项卡，在水平对齐下拉列表中选择“填充"，单击“确定"按钮（如图1）。

图１

（5） 多张工作表中输入相同的内容

几个工作表中同一位置填入同一数据时，可以选中一张工作表，然后按住Ctrl键，再单击窗口左下角的Sheet1、Sheet2......来直接选择需要输入相同内容的多个工作表，接着在其中的任意一个工作表中输入这些相同的数据，此时这些数据会自动出现在选中的其它工作表之中。输入完毕之后，再次按下键盘上的Ctrl键，然后使用鼠标左键单击所选择的多个工作表，解除这些工作表的联系，否则在一张表单中输入的数据会接着出现在选中的其它工作表内。

（6） 不连续单元格填充同一数据

（图和内容无关）

选中一个单元格，按住Ctrl键，用鼠标单击其他单元格，就将这些单元格全部都选中了。在编辑区中输入数据，然后按住Ctrl键，同时敲一下回车，在所有选中的单元格中都出现了这一数据。

（7） 在单元格中显示公式

如果工作表中的数据多数是由公式生成的，想要快速知道每个单元格中的公式形式，以便编辑修改，可以这样做：用鼠标左键单击“工具"菜单，选取“选项"命令，出现“选项"对话框，单击“视图"选项卡，接着设置“窗口选项"栏下的“公式"项有效，单击“确定"按钮（如图2）。这时每个单元格中的分工就显示出来了。如果想恢复公式计算结果的显示，就再设置“窗口选项"栏下的“公式"项失效即可。

图２

（8） 利用Ctrl＋*选取文本

如果一个工作表中有很多数据表格时，可以通过选定表格中某个单元格，然后按下Ctrl＋*键可选定整个表格。Ctrl＋*选定的区域为：根据选定单元格向四周辐射所涉及到的有数据单元格的最大区域。这样我们可以方便准确地选取数据表格，并能有效避免使用拖动鼠标方法选取较大单元格区域时屏幕的乱滚现象。

（9） 快速清除单元格的内容

如果要删除内容的单元格中的内容和它的格式和批注，就不能简单地应用选定该单元格，然后按Delete键的方法了。要彻底清除单元格,可用以下方法：选定想要清除的单元格或单元格范围；单击“编辑"菜单中“清除"项中的“全部"命令，这些单元格就恢复了本来面目。

2、单元格内容的合并

根据需要，有时想把B列与C列的内容进行合并，如果行数较少，可以直接用“剪切"和“粘贴"来完成操作，但如果有几万行，就不能这样办了。

解决办法是：在C行后插入一个空列（如果D列没有内容，就直接在D列操作），在Ｄ1中输入“=B1&C1"，D1列的内容就是B、C两列的和了。选中D1单元格，用鼠标指向单元格右下角的小方块“■"，当光标变成"＋"后，按住鼠标拖动光标向下拖到要合并的结尾行处，就完成了B列和C列的合并。这时先不要忙着把B列和C列删除，先要把D列的结果复制一下，再用“选择性粘贴"命令，将数据粘贴到一个空列上。这时再删掉B、C、D列的数据。

下面是一个“&"实际应用的例子。用AutoCAD绘图时，有人喜欢在EXCEL中存储坐标点，在绘制曲线时调用这些参数。存放数据格式为“x，y"的形式，首先在Excel中输入坐标值，将x坐标值放入A列，y坐标值放入到B列，然后利用“&"将A列和B列合并成C列，在C1中输入：=A1&"，"&B1，此时C1中的数据形式就符合要求了，再用鼠标向下拖动C1单元格，完成对A列和B列的所有内容的合并（如图3-4）。

合并不同单元格的内容，还有一种方法是利用CONCATENATE函数，此函数的作用是将若干文字串合并到一个字串中，具体操作为“=CONCATENATE（B1,C1）"。比如，假设在某一河流生态调查工作表中，B2包含“物种"、B3包含“河鳟鱼"，B7包含总数45，那么： 输入“=CONCATENATE（"本次河流生态调查结果："，B2，""，B3，"为"，B7，"条/公里。"）" 计算结果为：本次河流生态调查结果：河鳟鱼物种为45条/公里。

3、条件显示

我们知道，利用If函数，可以实现按照条件显示。一个常用的例子，就是教师在统计学生成绩时，希望输入60以下的分数时，能显示为“不及格"；输入60以上的分数时，显示为“及格"。这样的效果，利用IF函数可以很方便地实现。 假设成绩在A2单元格中，判断结果在A3单元格中。那么在A3单元格中输入公式： =if（A2<60，“不及格"，“及格"） 同时，在IF函数中还可以嵌套IF函数或其它函数。

例如，如果输入： =if（A2<60，“不及格"，if（A2<=90，“及格"，“优秀"）） 就把成绩分成了三个等级。　

如果输入　=if（A2<60，“差"，if（A2<=70，“中"，if（A2<90，“良"，“优"））） 就把成绩分为了四个等级。 　　

再比如，公式： =if（SUM（A1：A5>0，SUM（A1：A5），0） 此式就利用了嵌套函数，意思是，当A1至A5的和大于0时，返回这个值，如果小于0，那么就返回0。 还有一点要提醒你注意：以上的符号均为半角，而且IF与括号之间也不能有空格。

4、自定义格式

Excel中预设了很多有用的数据格式，基本能够满足使用的要求，但对一些特殊的要求，如强调显示某些重要数据或信息、设置显示条件等，就要使用自定义格式功能来完成。 Excel的自定义格式使用下面的通用模型：正数格式，负数格式，零格式，文本格式，在这个通用模型中，包含三个数字段和一个文本段：大于零的数据使用正数格式；小于零的数据使用负数格式；等于零的数据使用零格式；输入单元格的正文使用文本格式。 我们还可以通过使用条件测试，添加描述文本和使用颜色来扩展自定义格式通用模型的应用。

（1）使用颜色 　要在自定义格式的某个段中设置颜色，只需在该段中增加用方括号括住的颜色名或颜色编号。Excel识别的颜色名为：[黑色]、[红色]、[白色]、[蓝色]、[绿色]、[青色]和[洋红]。Excel也识别按[颜色X]指定的颜色，其中X是1至56之间的数字，代表56种颜色.

（2）添加描述文本 　要在输入数字数据之后自动添加文本，使用自定义格式为：＂文本内容＂@；要在输入数字数据之前自动添加文本，使用自定义格式为：@＂文本内容＂。@符号的位置决定了Excel输入的数字数据相对于添加文本的位置。

（3）创建条件格式 　可以使用六种逻辑符号来设计一个条件格式：>（大于）、>=（大于等于）、<（小于）、<=（小于等于）、=（等于）、<>（不等于），如果你觉得这些符号不好记，就干脆使用“>"或“>="号来表示。

由于自定义格式中最多只有3个数字段，Excel规定最多只能在前两个数字段中包括2个条件测试，满足某个测试条件的数字使用相应段中指定的格式，其余数字使用第3段格式。如果仅包含一个条件测试，则要根据不同的情况来具体分析。

自定义格式的通用模型相当于下式：[>；0]正数格式；[<；0]负数格式；零格式；文本格式。

下面给出一个例子：选中一列，然后单击“格式"菜单中的“单元格"命令，在弹出的对话框中选择“数字"选项卡，在“分类"列表中选择“自定义"，然后在“类型"文本框中输入“"正数:"($#,##0.00);"负数:"($ #,##0.00);"零";"文本:"@"，单击“确定"按钮，完成格式设置。这时如果我们输入“12"，就会在单元格中显示“正数：（$12.00）"，如果输入“-0.3"，就会在单元格中显示“负数：（$0.30）"，如果输入“0"，就会在单元格中显示“零"，如果输入文本“this is a book"，就会在单元格中显示“文本：this is a book"。 如果改变自定义格式的内容，“[红色]"正数:"($#,##0.00);[蓝色]"负数:"($ #,##0.00);[黄色]"零";"文本:"@"，那么正数、负数、零将显示为不同的颜色。如果输入“[Blue];[Red];[Yellow];[Green]"，那么正数、负数、零和文本将分别显示上面的颜色。

再举一个例子，假设正在进行帐目的结算，想要用蓝色显示结余超过$50，000的帐目，负数值用红色显示在括号中，其余的值用缺省颜色显示，可以创建如下的格式： “[蓝色][>50000] $#,##0.00_);[红色][<0]( $#,##0.00); $#,##0.00_)" 使用条件运算符也可以作为缩放数值的强有力的辅助方式，例如，如果所在单位生产几种产品，每个产品中只要几克某化合物，而一天生产几千个此产品，那么在编制使用预算时，需要从克转为千克、吨，这时可以定义下面的格式： “[>999999]#,##0,,_m"吨"";[>999]##,_k_m"千克";#_k"克"" 可以看到，使用条件格式，千分符和均匀间隔指示符的组合，不用增加公式的数目就可以改进工作表的可读性和效率。

另外，我们还可以运用自定义格式来达到隐藏输入数据的目的，比如格式＂；＃＃；0＂只显示负数和零，输入的正数则不显示；格式“；；；"则隐藏所有的输入值。 自定义格式只改变数据的显示外观，并不改变数据的值，也就是说不影响数据的计算。灵活运用好自定义格式功能，将会给实际工作带来很大的方便。

5、绘制函数图象

做教学工作的朋友们一定会遇到画函数曲线的问题吧！如果想快速准确地绘制一条函数曲线，可以借助EXCEL的图表功能，它能使你画的曲线既标准又漂亮。你一定会问，是不是很难学呀？其实这一点儿也不难，可以说非常简便，不信你就跟我试一试。

以绘制y=|lg（6+x^3）|的曲线为例，其方法如下： 在某张空白的工作表中，先输入函数的自变量：在A列的A1格输入"X="，表明这是自变量，再在A列的A2及以后的格内逐次从小到大输入自变量的各个值；实际输入的时候，通常应用等差数列输入法，先输入前二个值，定出自变量中数与数之间的步长，然后选中A2和A3两个单元格，使这二项变成一个带黑色边框的矩形，再用鼠标指向这黑色矩形的右下角的小方块“■"，当光标变成"＋"后，按住鼠标拖动光标到适当的位置，就完成自变量的输入。

输入函数式：在B列的B1格输入函数式的一般书面表达形式，y=|lg（6+x^3）|；在B2格输入“=ABS（LOG10（6+A2^3））"，B2格内马上得出了计算的结果。这时，再选中B2格，让光标指向B2矩形右下角的“■"，当光标变成"＋"时按住光标沿B列拖动到适当的位置即完成函数值的计算。

绘制曲线：点击工具栏上的“图表向导"按钮，选择“X，Y散点图"（如图7），然后在出现的“X，Y散点图"类型中选择“无数据点平滑线散点图"；此时可察看即将绘制的函数图像，发现并不是我们所要的函数曲线，单击“下一步"按钮，选中“数据产生在列"项，给出数据区域，这时曲线就在我们面前了

需要注意：如何确定自变量的初始值，数据点之间的步长是多少，这是要根据函数的具体特点来判断，这也是对使用者能力的检验。如果想很快查到函数的极值或看出其发展趋势，给出的数据点也不一定非得是等差的，可以根据需要任意给定。

从简单的三角函数到复杂的对数、指数函数，都可以用EXCEL画出曲线。如果用得到，你还可以利用EXCEL来完成行列式、矩阵的各种计算，进行简单的积分运算，利用迭代求函数值（如x^2=x^7+4，可用迭代方法求x值），等等，凡是涉及计算方面的事，找EXCEL来帮忙，它一定会给你一个满意的答案。

6、自定义函数

虽然Excel中已有大量的内置函数，但有时可能还会碰到一些计算无函数可用的情况。假如某公司采用一个特殊的数学公式计算产品购买者的折扣，如果有一个函数来计算岂不更方便？下面就说一下如何创建这样的自定义函数。

自定义函数，也叫用户定义函数，是Excel最富有创意和吸引力的功能之一，下面我们在Visual Basic模块中创建一个函数。 在下面的例子中，我们要给每个人的金额乘一个系数，如果是上班时的工作餐，就打六折；如果是加班时的工作餐，就打五折；如果是休息日来就餐，就打九折。首先打开“工具"菜单，单击“宏"命令中的“Visual Basic编辑器"，进入Visual Basic编辑环境，在“工程－VBAobject"栏中的当前表上单击鼠标右键�...

在使用Excel制作表格的时候，都会设置一个标题栏。不过这个标题栏会在Excel表格向下滚动的时候消失掉，那么该怎么固定标题栏，即使表格怎么滚动都不会消失？针对这个问题，小编整理了Excel表格固定标题栏的方法，有兴趣的朋友一起来看看吧！

Excel中固定标题栏的方法：

1、打开Excel，找到页面设置，并点击左下角，如图。

2、最上方找到工作表选项，点击顶端标题行鼠标点击一下它对应后边的哪个红红的斜向上的箭头，如图。

3、点击了之后，就变成了一个可以随处移动的小窄条（自己可以移动一下试试哦），而且鼠标也变成了一个向右指向的黑色粗箭头，选择想要每页显示的标题行，此时会有虚线显示，选择好以后，再次点击小窄条右边的红色斜箭头。

4、此时会返回到“页面设置”对话框位置，观察一下，发现顶端标题行处有了内容，然后点击确定，便完成设置，如图。

以上就是Excel中固定标题栏的方法，你学会了吗！

很多时候我们做好的表格，特别是里面有很多函数公式的时候，供别人使用的话难免被误删或修改，导致数据出现错误，但是如果对文件加密设为只读别人就没法使用表格，那么如何设置需要填写单元格可以随便修改，其他单元格无法修改呢？今天就教大家对文件进行保护设置。

大家先看一下 效果图

表格中空白的地方可以修改数据，有颜色的地方就无法修改数据。具体操作如下：

一、选中整个单元格（也可单独选中需要锁定的单元格），右键选中【设置单元格格式】【保护】勾上【锁定】

二、选中可以修改的单元格，右键选中【设置单元格格式】【保护】去掉【锁定】前面的对号。

三、点击【保护工作表】选中【选定未锁定的单元格】输入密码保存即可。

制作粗劣但为原创，动图也是自己制作，不足之处望大家指正，需要制作表格请留言，将为大家免费制作并提供教程。欢迎大家留言、转发、收藏。更希望与大家共同学习。

在数据量大的Excel表格中，查看数据的时候就会不方便，通常找不到行或列的标题。今天跟大家分享Excel表格中关于固定标题的操作小技巧。

操作方法一：冻结窗口（工作中最常用的一种方法）；

通过【视图】功能选项下的窗口菜单中“冻结窗口”，选择你所需要冻结窗口中的选项。比如：我们现在需要冻结标题行，就选择冻结首行。

选择Excel表格中的冻结窗口

冻结Excel表格标题行设置完成，我们看下效果。

操作方法二：将数据区域转换成表

将鼠标放在表格中的任意一个单元格，选择【插入】功能选项下表格菜单中的“表格”，在弹出的“创建表”对话框中不需要做任何修改，默认情况下在表包含标题前面的框中都已经勾选。

将数据区域转换成表

设置完成后将鼠标放在表格中的任意一个单元格往下拖拉，标题行都会在上端出现，方便查看数据。表格的标题行设置固定。

今天跟大家分享的Excel表格中标题行设置固定不动的操作方法，操作方法二在做动态的数据源时用到会比较多，也比较方便。希望今天的分享对大家在工作中有所帮助！

通过保护工作表可以实现。

举例说明。

例如有表格如下：

要求C列的公式不可修改。

第一步：选择A:B两列。

第二步：点击格式-单元格格式。如图：

第三步：点击保护-去除锁定的√。如图：

第四步：点击确定后，点击审阅-保护工作表-输入密码。如图：

第五步：点击确定后，选择C2单元格。按删除键，结果如图：

第六步：选择B2，将主管修改为部门经理。结果如图：

（本文内容由百度知道网友pepe3399贡献）

最近收到很多学员咨询如何针对指定的单元格区域进行锁定，禁止编辑。大家都熟悉保护工作表是对整表进行锁定的，局限性较大，无法进行有针对性的操作。

今天龚老师跟大家分享一下如何针对指定单元格区域进行锁定。

如上图我们需要讲有数据部分单元格进行锁定，其他区域可以进行编辑等操作。

操作步骤

第一步：单击【审阅】中的【允许用户编辑区域】功能。

在【新区域】对话框中设置标题、可以编辑的区域、密码后单击】确定。

单击【应用】

第二步：右击工作表后选择【保护工作表】后单击确定。

完成操作后我们测试一下：

有数据区域

无数据区域只要输入密码就可以编辑。

这么好的小技巧看完记得分享一下哦！

用 Excel 编辑表格过程中，如果要求某些单元格不能修改，需要用 Excel 锁定单元格的功能实现。Excel 锁定单元格比较灵活，一次可以锁定一个，也可以锁定多个（包括连续的与不连续的），还可以锁定表头、一行一列、多行多列和只锁定有公式的所有单元格。然后，单用锁定单元格不能实现锁定，还必须启用保护工作表功能，启用时可以设置密码和允许修改的项。以下就是 Excel 锁定单元格的具体操作实例，操作中所用 Excel 版本为 2016。

一、Excel 锁定单元格

（一）锁定一个单元格

1、按 Ctrl + A 组合键选中整个表格（或单击表格左上角的小三角选中），右键，在弹出的菜单中选择“设置单元格格式”，在打开的窗口中选择“保护”选项卡，单击“锁定”把其前的钩去掉，单击“确定”；选中要锁定单元格（B2），再次打开“设置单元格格式”窗口并选择“保护”选项卡，勾选“锁定”，单击“确定”；然后，单击“开始”选项卡下的“格式”，在弹出的选项中选择“保护工作表”，单击“确定”，则 B2 单元格被锁定，此时，双击 B2 会弹出一个提示窗口提示 B2 已经被保护而不能修改，再双击其它单元格，则还可以修改；操作过程步骤，如图1所示：

2、说明：在默认情况下，Excel 给所有单元格勾选“锁定”，所以锁定一个单元格前需要把所有单元格取消勾选“锁定”，然后再锁定要锁定的单元格，最后开启“保护工作表”，则成功锁定一个单元格。在打开“保护工作表”窗口中，可以设置密码，这可根据安全需求确定是否设置。

（二）锁定多个单元格

1、像“锁定一个单元格”一样，首先取消对所有单元格的锁定，操作步骤为：Ctrl + A 全选 → 右键 → 设置单元格格式 → “保护”选项卡 → 不勾选“锁定”。然后再选择要锁定的单元格，例如先选中一片连续的单元格，再按住 Alt，点击要选中的不连续的单元格，选中后，如图2所示：

2、在选中的单元格上右键，选择“设置单元格格式”，在打开的窗口中选择“保护”选项卡，勾选“锁定”，如图3所示：

3、单击“确定”后，再单击“格式”，在弹出的选项中选择“保护工作表”，在打开的窗口中输入密码；另外，还有一个锁定单元格后仍允许修改的列表，可根据需要取舍，如图4所示：

4、单击“确定”，再次输入密码，则所有选中单元被锁定，双击它们修改时，同样会弹出一个不允许改的提示窗口。

(三）锁定行列

方法跟锁定多个单元格一样，锁定行时，只需选中要锁定的行（可以是表头、任意一行或多行）；同样锁定列时，选中要锁定的列即可。

二、Excel 锁定有公式的单元格

1、像锁定单元格一样，首先取消勾选所有单元格的“锁定”；接着选中所有有公式的单元格，方法为：按 Ctrl + G 组合键，打开“定位”窗口，单击“定位条件”，打开该窗口，勾选“公式”，单击“确定”，则选中全部有公式的单元格；再给选中单元格勾选“锁定”；最后，启用“保护工作表”，方法为：单击“格式”，在弹出的选项中选择“保护工作表”，在打开的窗口中单击“确定”，则全部有公式的单元格被锁定，单击它们时同样会弹出已受保护提示窗口；操作过程步骤，如图5所示：

2、这里关键是用“定位条件”来选中全部有公式的单格，其它操作与锁定单元格一样。

三、Excel 锁定整个表格

只需启用“保护工作表”，则整个表格的所有单元格都被锁定，这只是以上操作中最后一步，如果不清楚，看上面的操作演示即可。

　　1、按“ctrl”+“A”或点击行首和列首交叉处的第一个单元格，选中全部单元格，点击右键，点击快捷菜单中的“设置单元格格式”；

　　2．点击弹出的“单元格格式”中“保护”标签，点击“锁定”，去掉前面的“√”符号，点击“确定”，先解除所有单元格的锁定状态；

　　3、选择需要锁定的部分单元格，在上面单击右键，点击快捷菜单中的“设置单元格格式”，点击弹出的“单元格格式”中“保护”标签，点击“锁定”，使前面打上“√”符号；

　　4、点击“工具”，将光标移至下拉菜单“保护”，点击子菜单下的“保护工作表”；

　　5、在“保护工作表”可输入保护的密码，也可以不输入，最后点击“确定”即可完成对部分单元格的锁定。

　　说明：如果需要撤销锁定，点击“工具”，将光标移至下拉菜单“保护”，点击子菜单下的“撤销工作表保护”即可。不过如果设置有密码的话，需要输入密码才可撤销。

（本文内容由百度知道网友xuxu九转贡献）

1.如图，一个没有经过固定操作的电子表格，当随着表格被拖动或滚动，需要被固定的首行或首列的信息也跟着滚动消失了。

2.首先打开excel文档，假设我们需要让表格的前两行内容在表格滚动时固定在表格顶部，即第3行与第4行不变，如图所示。

3.首先将鼠标移动到O5单元格（要固定的行的下一行最旁边一个单元格内），并单击，使该单元格处于被选中状态，如图所示。

4.保持光标停留位置处于O5单元格，依次选择工具栏中的“视图”-“冻结窗格”-单击“冻结拆分窗格”，如图所示。

5.回到文档，继续拖动或滚动表格，会发现第3、4行已经被固定在此位置。至此所有操作完成，过程很简单，如图所示。

6.如果要恢复原来的状态则重复上面2-5步骤即可。

（本文内容由百度知道网友mzw_ok贡献）

在Excel中，如果数据太多，当表格滚动到后面时，标题行就不见了，这对于查看数据来说极为不便，如何使Excel某一行固定不动？具体方法如下。

打开对应的Excel表格文件。

选中要固定不动的行的下一行。

单击“窗口”菜单中的“冻结窗格”按钮。

可以看到，冻结行下面的线比不冻结前变黑了。

这时，再拖动表格的滚动条，冻结行就不会消失了，始终在表格上方显示。

如果想取消冻结，单击“窗口”菜单中的“取消冻结窗格”按钮即可。