按下不表

按下不表

-

2023年2月20日发(作者：)

excel表格的基本操作实用技巧大全

1、让数据显示不同颜色

让数据显示不同颜色：选中某一列（总分）列→开始→样式→条件格

式→在弹出的列表中选择突出显示单元格规则→选择相应的条件→设置数

值→选择填充颜色；

取消设置的规则：选择数据有效性→清除规则。

在学生成绩分析表中，如果想让总分大于等于500分的分数以蓝

色显示，小于500分的分数以红色显示。操作的步骤如下：第一，选中总

分所在列，执行“格式→条件格式”，在弹出的“条件格式”对话框中，将

第一个框中设为“单元格数值”、第二个框中设为“大于或等于”，然后在

第三个框中输入500，单击[格式]按钮，在“单元格格式”对话框中，将“字

体”的颜色设置为蓝色，然后再单击[添加]按钮，并以同样方法设置小于5

00，字体设置为红色，最后单击[确定]按钮。

这时候，只要你的总分大于或等于500分，就会以蓝色数字显示，否

则以红色显示。

2、将成绩合理排序

将成绩合理排序：选择数据列→数据→排序→要紧关键字→添加条

件→次要关键字……次序列可选择自定义序列

自定义排序列表：excel选项→常用→编辑自定义列表→输入序列中

输入自定义序列→添加→确定

3、分数排行:

如果需要将学生成绩按着学生的总分进行从高到低排序，当遇到总分

一样的则按姓氏排序。操作步骤如下：先选中所有的数据列，选择“数据

→排序”，然后在弹出“排序”窗口的“要紧关键字”下拉列表中选择“总

分”，并选中“递减”单选框，在“次要关键字”下拉列表中选择“姓名”，

最后单击[确定]按钮

4、操纵数据类型

操纵数据类型：选择特定单元格→数据→数据有效性→设置→承诺

下拉选择

在输入工作表的时候，需要在单元格中只输入整数而不能输入小数，

或者只能输入日期型的数据。幸好Excel2003具有自动判定、即时分析并

弹出警告的功能。先选择某些特定单元格，然后选择“数据→有效性”，在

“数据有效性”对话框中，选择“设置”选项卡，然后在“承诺”框中选

择特定的数据类型，因此还要给那个类型加上一些特定的要求，如整数必

须是介于某一数之间等等。另外你能够选择“出错警告”选项卡，设置输

入类型出错后以什么方式显现警告提示信息。如果不设置就会以默认的方

式打开警告窗口。如何样，现在处处有提示了吧，当你输入信息类型错误

或者不符合某些要求时就会警告了。

5、如何在已有的单元格中批量加入一段固定字符？

在已有的单元格前面批量加入一段固定字符：在需要加入数据列（A

列）后右键插入一列（B列）→在B2单元格输入：=需要加入的字符（如

13）+&+A1后回车，即：=13&A2回车，最后填充B列其他单元格。

在已有的单元格后面批量加入一段固定字符：=A2&13回车。

6、如何设置文件下拉窗口的最下面的最近运行的文件名个数？

设置文件下拉窗口的最下面的最近运行的文件名个数：打开“工具”

→“excel选项”→“高级”→“显示”→在“最近使用的文件清单”下面

的文件个数输入框中改变文件数目即可。

7、在EXCEL中输入如“１-１”、“１-２”之类的格式后它即变成１月

１日，１月２日等日期形式，如何办？

EXCEL中输入如“１-１”、“１-２”之类的格式后即变成１月１

日，１月２日：开始→单元格→格式→设置单元格格式→将数字标签下的

分类选为文本→确定。

8、在EXCEL中如何使它象WORD一样的自动定时储存文件？

EXCEL中自动定时储存文件：工具→excel选项→储存→自动储存

复原信息时刻间隔→修改时刻。

9、用Excel做多页的表格时，如何样像Word的表格那样做一个

标题，即每页的第一行（或几行）是一样的。

然而不是用页眉来完成？

在EXCEL的文件菜单－页面设置－工作表－打印标题；可进行顶端或

左端标题设置，通过按下折叠对话框按钮后，用鼠标划定范畴即可。如

此Excel就会自动在各页上加上你划定的部分作为表头。

10、在Excel中如何设置加权平均？

加权平均在财务核算和统计工作中经常用到，并不是一项专门复杂的

运算，关键是要明白得加权平均值事实上确实是总量值（如金额）除以总

数量得出的单位平均值，而不是简单的将各个单位值（如单价）平均后得

到的那个单位值。在Excel中可设置公式解决（事实上确实是一个除法算

式），分母是各个量值之和，分子是相应的各个数量之和，它的结果确实是

这些量值的加权平均值。

11、如果在一个Excel文件中含有多个工作表，如何将多个工作表一次

设置成同样的页眉和页脚？如何才能一次打印多个工作表？

一次操作多个工作表：鼠标移到工作表名称（sheet1、sheet2……）

处→右键在弹出的菜单中选择“选择全部工作表”即可。

12、修改工作表保持序号列不变：在序号列和后面的数据列之间插入

一列，为了美观能够将此新插入的空白列隐藏。

隐藏列：（1）选中隐藏列→右键→选择隐藏；（2）选中隐藏列→开

始→单元格→格式→选择隐藏和取消隐藏。

取消隐藏列：（1）选中隐藏列的相邻两列→右键→选择取消隐藏；（2）

将鼠标移动到隐藏列的相邻两列分界线的右侧，当鼠标变成了一个可移动

的图标时，拖动鼠标向右即可；（3）选中整个工作表→开始→单元格→格

式→选择隐藏和取消隐藏。即取消所有的隐藏列、行。

13、工资表中每个人的工资条都打印显示条头：（1）页面布局→打印

标题→选择顶端标题行→页面布局→分页符→每行都插入分页符；（2）复

制第一行的工资细目数据，从工资表最后一条数据之后的任一行粘贴，并

使用填充柄拖动复制和工资表数据相同行→在工资表第一列前插入两列→

第一列使用填充柄输入序号，从1一直拉到N(N差不多上只要大于工资表

中数据的两倍即可)，要紧用来打印工资条后下次重新做工资时复原表格顺

序→第二列从第二行开始，依次输入1、3、5、7，然后选中这四个单元格，

使用填充柄填充至职员数据终止位置→在下方的工资细目数据区，从第一

行依次输入2、4、6、8，然后选中这四个单元格，使用填充柄填充至终止

→“数据”→“选择”→“自动选择”命令→单击B列的下拉按钮，在弹

出的下拉菜单中选择“升序排列”，工资条已做好，打印时将插入的两列隐

藏，个月需要使用这张工资表格重新运算工资时，只需要将隐藏的A、B

两列取消隐藏，使用“自动选择”，然后按照A列“升序排列”即可还原原

先的顺序；（3）在工资细目的右侧两列中，交叉输入任意数字→选中交叉

的四个单元格，双击右下角的“填充柄”，使这种格式一直填充至工资表的

终止行→执行“开始”→“查找和选择”→“定位条件”→“空值”→“确

定”→“开始”→“插入”→“插入工作表行”→复制表头的工资细目

数据，选中工资表A列的数据区域，执行“开始”→“查找和选择”→“定

位条件”→“空值”→“确定”→“开始”→“粘贴”。

14、在Ｅxcel中小数点无法输入，按小数点，显示的却是逗号，不管

如何样设置选项都无济于事，该如何办？

Ｅxcel中小数点无法输入，按小数点，显示的却是逗号：设置——操

纵面板——区域和语言选项——数字”属性里把小数点改为“.”（未改前是

“，”）——按“确定”按钮终止。

15、如何快速选取特定区域？

Excel中快速选取特定区域：按F5——显现定位窗口——引用位置处输

入需要选取的区域，如：A2：B2000。

16、如何快速返回选中区域？

按Ctr+BacksPae（即退格键）。

17、如何快速定位到单元格？

快速定位到单元格：（1）按F5键，显现“定位”对话框，在引用

栏中输入欲跳到的单元格地址，单击“确定”按钮即可。（2）单击编辑栏

左侧单元格地址框，输入单元格地址即可。

18、“Ctrl＋*”的专门功用

“Ctrl＋shift+*”的专门功用：通过选定表格中某个单元格，然后

按下“Ctrl＋shift+*”组合键可选定整个表格。

19、如何在不同单元格中快速输入同一数内容？

在不同单元格中快速输入同一数内容：选定单元格区域——输入值—

—按Ctrl＋Ener键。

20、只记得函数的名称，但记不清函数的参数了，如何办？

记得函数的名称，但记不清函数的参数：在编辑栏中输入一个等号

其后接函数名——按Ctr＋A键。

21、如何把选定的一个或多个单元格拖放至新的位置？

把选定的一个或多个单元格拖放至新的位置：选定单元格——按下Shi

ft键——移动鼠标指针至单元格边缘，直至显现拖放指针箭头（空心箭头）

——按住鼠标左键进行拖放操作。

22、如何让屏幕上的工作空间变大？

让屏幕上的工作空间变大：（1）视图——全屏显示；（2）将不用的工

具栏隐藏。

23、如何使用快显菜单？

快显菜单中包括了一些操作中最常用的命令，利用它们能够大大提升

操作效率。第一选定一个区域，然后单击鼠标右健即可调出快显菜单，按

照操作需要选择不同命令。

24、如何防止Excel自动打开太多文件？

当Excel启动时，它会自动打开Xlstart名目下的所有文件。当该

名目下的文件过多时，Excel加载太多文件不但费时而且还有可能出错。解

决方法是将不该位于Xlstart名目下的文件移走。另外，还要防止EXcel打

开替补启动名目下的文件：选择“工具”＼“选项”＼“一般”，将“替补

启动名目”一栏中的所有内容删除。

25、输入大量数据时自动插入小数点：（1）excel选项——高级——勾

选自动插入小数点勾选框——修改位数，位数为正数表示减小相应的倍数，

位数为负表示增大相应的倍数（此方法适用于整个工作表）；（2）在工作表

空白处任意单元格输入100（处理数据所需的数据）——复制该单元格——

选择需要处理的数据——右键选择性粘贴——运算条目下选择合适的运

算，该方法适用于选定的特定的区域。

26、如何去掉网格线？

去掉网格线：视图——网格线——取消勾选。

除去打印时的未定义表格线：页面布局——网格线——取消勾选。

27、如何快速格式化报表？

为了制作出美观的报表，需要对报表进行格式化。有快捷方法，

即自动套用Excel预设的表格样式。方法是：选定操作区域，选取“格式”

菜单中的“自动套用格式”命令，在格式列表框中选取一款你中意的格式

样式，按“确定”按钮即可。要注意的是，格式列表框下面有包括“数字”、

“边框线”、“字体”等6个“应用格式种类”选项，若某项前面的“x”不

显现，则在套用表格样式时就可不能用该项。

28、如何快速地复制单元格的格式？

要将某一格式化操作复制到另一部分数据上，可使用“格式刷”

按钮。选择含有所需源格式的单元格，单击工具条上的“格式刷”按钮，

现在鼠标变成了刷子形状，然后单击要格式化的单元格即可将格式拷贝过

去。

29、如何为表格添加斜线？

为表格添加斜线：（1）一条斜线表头：选中一单元格，输入字段1——

ALT+ENTER——输入字段2——在字段1前面通过空格键使数据靠右——

右键——设置单元格格式——边框——选择斜线表头；或者插入——直线；

（2）双斜线表头：和两栏斜线表头的第一步类似，输入三个字段、换行并

加适量空格——【插入】-【形状】-【直线】，自单元格左上角画出两条分

隔线就完成了三栏斜线表头的制作；

30、如何快速地将数字作为文本输入？

快速地将数字作为文本输入：在输入数字前加一个单引号“‘”，能

够强制地将数字作为文本输入。

31、在Excel中自定义函数：

自定义一个运算梯形面积的函数：

开发工具——VisualBasic——插入——模块——插入一个新的模块—

—模块1——在代码窗口”中输入以下代码：FunctionV(a,b,

h)

V=h*(a+b)/2

EndFunction——关闭窗口，自定义函数完成。

以后能够像使用内置函数一样使用自定义函数，提示：用上面方法自

定义的函数通常只能在相应的工作簿中使用。

32、如何在一个与自定义函数驻留工作簿不同的工作簿内的工作表公

式中调用自定义函数？

可在包含自定义函数的工作薄打开的前提下，采纳链接的方法(也

确实是在调用函数时加上该函数所在的工作簿名)。假设上例中的自定义函

数Zm所在工作薄为，现要在另一不同工作簿中的工作表公式

中调用Zm函数，应第一确保被打开，然后使用下述链接的

方法：=！ZM(b2)

33、如何快速输入数据序列？

自定义序列：EXCEL选项——常用——编辑自定义列表——输入序列

中输入自定义新序列，注意在新序列各项之间要输入半角符号的逗号加以

分隔列表——添加——确定。

34、使用鼠标右键拖动单元格填充柄

使用鼠标右键拖动单元格填充柄：在某单元格内输入数据，按住

鼠标右键沿着要填充序列的方向拖动填充柄，将会显现包含下列各项的菜

单：复制单元格、以序列方式填充、以格式填充、以值填充；以天数填充、

以工作日该充、以月该充、以年填充；序列……现在，能够按照需要选择

一种填充方式。

35．将工作表中已有的某个序列定义成自动填充序列以备后用：选定

包含序列项的单元格区域，EXCEL选项——常用——编辑自定义列表—

—点击“导入”按钮将选定区域的序列项添加至“自定义序列”对话框，

按“确定”按钮返回工作表。

36、已有序列项中含有许多重复项，处理使其没有重复项，以便使用

“导入”的方法快速创建所需的自定义序列：选定单元格区域，选择“数

据”——“选择”——“高级选择”，选定“选择不重复的记录”选项，按

“确定”按钮即可。

37、对工作簿进行加密：office按钮——预备——加密文档——输入密

码；或者使用加密软件。

工作簿（表）被爱护之后，还可对工作表中某些单元格区域的重

要数据进行爱护，起到双重爱护的功能：选定需爱护的单元格区域，——

“开始”——“单元格”——“格式”——选取“锁定单元格”——“开

始”——“单元格”——“格式”——“爱护工作表”，按照提示两次输

入口令后退出。

38、如何使单元格中的颜色和底纹不打印出来？

对加了颜色和底纹的单元格，在打印时不显示出底纹：页面布局

——打印标题——工作表——勾选单色打印。

39、建立分类下拉列表填充项

建立分类下拉列表填充项：选中需要设置分类下拉的列（如A列）—

—数据——数据有效性——设置——承诺下拉选择序列——来源手动输

入，各元素之间用英文逗号分开，或者点击右侧按钮选择——确定；选择

某列（如B列）——数据——数据有效性——设置——承诺下拉选择序列

——来源处输入：=indirect（A1）——确定——选中A列任意单元格(如A

4)，单击右侧下拉按钮，选择相应的类不填入单元格中——选中该单元格

对应的B列单元格(如B4)，单击下拉按钮，即可从相应类不列表中选择需

要的名称填入该单元格中。

4O、建立“常用文档”新菜单

在菜单栏上新建一个“常用文档”菜单，将常用的工作簿文档添

加到其中，方便随时调用。

1.在工具栏空白处右击鼠标，选“自定义”选项，打开“自定义”对话

框。在“命令”标签中，选中“类不”下的“新菜单”项，再将“命令”

下面的“新菜单”拖到菜单栏。按“更换所选内容”按钮，在弹出菜单的

“命名”框中输入一个名称(如“常用文档”)。

2.再在“类不”下面任选一项(如“插入”选项)，在右边“命令”下面

任选一项(如“超链接”选项)，将它拖到新菜单(常用文档)中，并仿照上面

的操作对它进行命名(如“工资表”等)，建立第一个工作簿文档列表名

称。重复上面的操作，多添加几个文档列表名称。

3.选中“常用文档”菜单中某个菜单项(如“工资表”等)，右击鼠标，

在弹出的快捷菜单中，选“分配超链接→打开”选项，打开“分配超链接”

对话框。通过按“查找范畴”右侧的下拉按钮，定位到相应的工作簿(如“工

资.xls”等)文件夹，并选中该工作簿文档。重复上面的操作，将菜单项和与

它对应的工作簿文档超链接起来。

4.以后需要打开“常用文档”菜单中的某个工作簿文档时，只要展开“常

用文档”菜单，单击其中的相应选项即可。

提示：尽管我们将“超链接”选项拖到了“常用文档”菜单中，但并

不阻碍“插入”菜单中“超链接”菜单项和“常用”工具栏上的“插入超

链接”按钮的功能。

41、在编辑专业表格时，常常需要输入一些专门的专业符号，为了方

便输入，能够制作一个属于自己的“专业符号”工具栏。

（1）视图——宏——录制新宏，输入宏名，如：符号1，并将宏储存

在“个人宏工作簿”中——确定，开始录制，——选中宏——使用相对引

用——将需要的专门符号输入到某个单元格中——单击宏——停止录制，

完成宏的录制。

（2）打开“自定义”对话框，在“工具栏”标签中，单击“新建”

按钮，弹出“新建工具栏”对话框，输入名称——“专业符号”，确定后，

即在工作区中显现一个工具条。切换到“命令”标签中，选中“类不”下

面的“宏”，将“命令”下面的“自定义按钮”项拖到“专业符号”栏上(有

多少个专门符号就拖多少个按钮)。

（3）选中其中一个“自定义按钮”，仿照第2个秘技的第1点对它们进

行命名。

（4）右击某个命名后的按钮，在随后弹出的快捷菜单中，选“指定宏”

选项，打开“指定宏”对话框，选中相应的宏(如fuhao1等)，确定退出。

重复此步操作，将按钮与相应的宏链接起来。

（5）关闭“自定义”对话框，以后能够像使用一般工具栏一样，使用“专

业符号”工具栏，向单元格中快速输入专业符号了。

42、用“视面治理器”储存多个打印页面

用“视面治理器”储存多个打印页面：

（1）打开需要打印的工作表，选中不需要打印的行(或列)——右击鼠

标——隐藏，将不需要打印的行(或列)隐藏起来；

（2）视图——自定义视图，打开“视面治理器——单击“添加”按钮，

弹出“添加视面”对话框——输入一个名称(如“上报表”)后——确定；

（3）将隐藏的行(或列)显示出来，并重复上述操作，“添加”好其它的

打印视面；

（4）以后需要打印某种表格时，打开“视面治理器”，选中需要打印

的表格名称，单击“显示”按钮，工作表即刻按事先设定好的界面显示出

来。

43、让数据按需排序

自定义序列排序：Excel选项——常用——编辑自定义列表—输入序

列中输入自定义序列—添加—确定；—选中排序列—数据—排序—在弹出

的的次序列选择自定义序列—确定。

44、隐藏工作表部分单元格中的内容

隐藏工作表部分单元格中的内容：选中需要隐藏内容的单元格(区域)

—单元格—格式——设置单元格格式—在“数字”标签的“分类”下面选

中“自定义”选项—右边“类型”下面的方框中输入“；；；”(三个英文状态

下的分号)—设置单元格格式—爱护—勾选隐藏—确定—单元格—格式—

爱护工作表——设置密码——确定。

45、让中、英文输入法智能化地显现

选中需要输入中文的单元格区域—数据—数据有效性—输入法模式—

模式下拉选中打开——确定。

以后当选中需要输入中文的单元格区域中任意一个单元格时，中文输

入法(输入法列表中的第1个中文输入法)自动打开，当选中其它单元格时，

中文输入法自动关闭。

46、让“自动更正”输入统一的文本

Excel选项→校对→自动更正选项→替换下面的方框中输入特定小写

字符（如：tsht）→替换为下面的方框中输入需要替换的特定字符（如：天

水华天科技有限公司）→确定。以后需要输入上述文本时，只要输入tsht

回车确认。

47、为工作表添加的背景，是衬在整个工作表下面的，能不能只衬在

表头下面呢?

页面布局→背景→选中需要作为背景的图片后→插入→在按住Ctrl键

的同时选中不需要衬图片的单元格(区域)→右键→设置单元格格式→

填充→背景色填充白色。

48、用连字符“&”来合并文本

将多列内容合并到一列：在D列后插入两列(E、F列)→E1单元格中输

入公式：=B1&C1&D1→用“填充柄”将上述公式复制到E列→选中E列，

执行“复制”操作→选中F列→右键，选择性粘贴→数值→确定→删除B,

C,D列。

49、Excel帮你选函数

Excel的“搜索函数”功能：公式→插入函数→搜索函数”下面的方框

中输入要求(如“计数”)→转到，系统即刻将与“计数”有关的函数选择出

来，并显示在“选择函数”下面的列表框中。

50、编辑某个工作表(Sheet1)时，查看其它工作表中(Sheet2、Sheet3……)

某个单元格的内容，能够利用Excel的“监视窗口”功能来实现。

Excel的“监视窗口”查看当前工作表之外的其他工作表内容：公式→

监视窗口→添加监视，展开“添加监视点”对话框，用鼠标选中需要查看

的单元格→添加，重复前述操作，添加其它“监视点”。查看时只要打

开“监视窗口”，即可。

51、为单元格快速画边框

开始→字体→边框右侧的下拉按钮→绘图边框→展开“边框”

工具栏。①画错了边框，选中“擦除边框”按钮，然后在错误的边框上拖

拉一下，就能够清除掉错误的边框。②如果需要画出不同颜色的边框，能

够先按“线条颜色”按钮，在随后弹出的调色板中选中需要的颜色后，再

画边框即可。

52、操纵特定单元格输入文本的长度

选中需要设置的单元格区域→数据→数据有效性→设置→承诺下拉选

择文本长度→数据中选择数据范畴→输入最大值与最小值→确定，同时，

出错警告中，将“输入无效数据时显示的出错警告设为“停止”→“标题”

和“错误信息”栏中分不填入相应的信息。（讲明：能够自定义专门的数据

类型）。

53、同时在多张工作表相同单元格中输入同样的内容。

单击第一个工作表的标签名“Sheet1”→按住Shift键或Ctrl键选择需

要关联的多个工作表→现在，Excel的标题栏上的名称显现了“工作组”字

样，（或者单击第一个工作表的标签名“Sheet1”→右键→选定全部工作表）

现在就能够对工作组进行编辑工作。如改变多张表格中相同位置的数据格

式，第一改变第一张表格的数据格式→开始→填充→成组工作表→Excel会

弹出“填充成组工作表”的对话框→格式→确定，同组中所有表格该位置

的数据格式都改变了。

54、改变文本的大小写

有关改变文本大小写的函数：（1）=UPPER(源数据格)，将文本全

部转换为大写；（2）=LOWER(源数据格)，将文本全部转换成小写；（3）=

PROPER(源数据格)，将文本转换成“适当”的大小写，如让每个单词的首

字母为大写等。

55、提取字符串中的特定字符

提取字符串中特定字符函数：（1）=RIGHT(源数据格，提取的字符

数)，它表示“从特定单元格最右侧的字符开始提取特定个字符”输入到此

位置；（2）=LEFT(源数据格，提取的字符数)因此，它表示“从特定单元格

最左侧的字符开始提取特定个字符”输入到此位置；（3）=MID(源数据格A

5,从第几个字符开始提取4,提取的字符数2)，表示：在A5单元格中从第4

个字符开始提取2个字符，也确实是第4和第5两个字。

56、把基数词转换成序数词

将英文的基数词转换成序数词：=C3&IF(OR(VALUE(RIGHT(C3,2))={1

1,12,13}),\"th\",IF(OR(VALUE(RIGHT(C3))={1,2,3}),CHOOSE(RIGHT(C3),\"st

\",\"nd\",\"rd\"),\"th\"))。

①如果数字是以“11”、“12”、“13”结尾的，则加上“th”后缀；②如

果第1原则无效，则检查最后一个数字，以“1”结尾使用“st”、以“2”

结尾使用“nd”、以“3”结尾使用“rd”；③如果第1、2原则都无效，那么

就用“th。

57、Excel中专门符号填充“REPT”函数：它的差不多格式是=REPT

(“专门符号”，填充位数)。

例如，（1）在A2单元格里的数字结尾处用“#”号填充至16位：=A2

&REPT(″#″,16-LEN(A2))；（2）将A3单元格中的数字从左侧用“#”号

填充至16位：=REPT(″#″,16-LEN(A3))&A3；（3）用“#”号将A4中的

数值从两侧填充，则需要改为：=REPT(″#″,8-LEN(A4)/2)&A4&REPT(″

#″,8-LEN(A4)/2)”；（4）要在A5单元格数字的顶头加上“$”符号的话，

那就改为：=(TEXT(A5,″$#,##0.00″(&REPT(″#″,16-LEN(TEXT(A5,″

$#,##0.00″))))”。

57、创建文本直方图

创建文本直方图：（1）打开文件，假定用“*”表示人气指数。现设定

100票为一个人气，即每增加100票，该候选人的人气指数就增加一个“*”。

单击J3单元格，在J3单元格中输入如下公式：=REPT(“*”,I3/100)。

（2）选中“总分”列的单元格，在“开始”选项卡中选择→条件格式

→数据条→蓝色数据条”命令。

58、运算单元格中的总字数

运算字符串字符数：=len()，括号中输入要运算的单元格，运算出

后填充其他单元格。

利用“SUBSTITUTE”函数和“TRIM”函数（删除空格）运算单

元格中字符数。例如现在A1单元格中输入有“howmanywords?”字样，

那么我们就能够用如下的表达式来帮忙：=IF(LEN(A1)=0,0,LEN(TRIM(A1))

-LEN(SUBSTITUTE(TRIM(A1),″,″,″″))+1)”

该式的含义是先用“SUBSTITUTE”函数创建一个新字符串，同时利

用“TRIM”函数删除其中字符间的空格，然后运算此字符串和原字符串的

数位差，从而得出“空格”的数量，最后将空格数+1，就得出单元格中字

符的数量了。

59、关于欧元的转换

Office按钮→Excel选项→加载项→excel加载项→转到→加载宏→勾选

欧元工具→确定→系统会自行安装；安装完成后再次打开Office按钮→欧

元转换，即可进行币不转换。

60、数据透视表中数据更换后随时刷新：

（1）右键点击数据透视表任意单元格—点击刷新；

（2）、右键点击数据透视表任意单元格—点击数据透视表选项——数

据—勾选“打开文件时刷新数据”—确定；

讲明：打开此功能，数据更换后，正在使用的表，只有储存关闭，重

新打开之后才能自动更新。

（3）、数据随时随地更新：打开开发工具—录制宏—保持默认不变—

确定—数据—全部刷新—开发工具—停止录制—宏——找到刚才录制的宏

—编辑—复制hAll——单击VisualBasic的工程资源

治理器——选择ThisWorkbook——对象窗口中选择WorkBook——过程窗

口中选择SheetActivate——粘贴hAll，删掉余外的代

码——返回工作表；

61、VLOOKUP函数：

VLOOKUP(查找目标，查找范畴，查找列数，精确匹配或者近似匹

配）;

讲明：（1）查找目标必须是查找范畴的首列；

（2）查找列数为查找值在查找范畴的列数；

（3）精确匹配参数为false/0，近似匹配参数为true/1；

例1：查找型号为iPhone5在7.29的产量，查找值为E2，查找范畴为

A1：C14（型号为查找范畴的第一列），查找列为3，精确查找为0，该区域

中一定要包含要返回值所在的列，该例中返回值为7.29的产量。

例2、下例中需要同时查找性不，年龄，身高，体重。

公式：=VLOOKUP($A13,$B$2:$F$8,COLUMN(B1),0)公式讲明：

那个地点确实是使用COLUMN（B1）转化成能够自动递增的数字。

62、column函数：

（1）column函数返回所选择的某一个单元格的列数；

（2）column函数的语法格式

=column（reference)

如果省略reference，则默认返回函数column所在单元格的列数；如果

reference为一个单元格区域，返回引用中的第一列的列号。

63、IFERROR函数：

（1）IFERROR函数用于判定表达式的运算结果是否有效，当有效时

会返回表达式的值，而当表达式运算结果无效时将返回事先设定的字符串

或其它内容；

（2）差不多语法：=IFERROR(value,value_if_error)

Value：指通过IFERROR函数来检查是否存在错误的参数。

Value_if_error：指Value参数运算错误时要返回的值。

在此需要讲明一点：Value运算得到的错误类型包括#N/A、#VALUE!、

#REF!、#DIV/0!、#NUM!、#NAME?或#NULL!。

例如：当在C2单元格输入公式“=IFERROR(A2/B2,\"除数不能为0\")”

并按回车即得结果“除数不能为0”；接着在C3单元格输入如图所示的数

据，复制到C2单元格公式得到结果3。

64、if函数：

（1）IF函数含义：假设条件性的函数，即执行真假值的判定，按照

逻辑运算的真假值，返回不同的结果；

（2）if（logical_test,value_if_true,value_if_false)

IF是条件判定函数：=IF(测试条件,结果1,结果2)，即如果满足“测试

条件”则显示“结果1”，如果不满足“测试条件”则显示“结果2”。

65、sumif函数：

（1）SUMIF函数：按照指定的条件对若干单元格、表单求和，即对满

足条件的单元格或跨表单求和；

（2）sumif函数语法：=sumif(range,criteria,[sum_range])

讲明：range必须项，表示条件范畴，用于条件判定的单元格区域或数

列。；

criteria必须项，表示条件；

sum_range可选项，表示求和范畴；

例1、求数学成绩超过95分的成绩之和：=sumif（D2：D8，”>=95”）,

没有第三个参数，表示无可选项;

例2：求数学成绩超过95分的同学的总分之和：=sumif（D2：D8，”>

=95”，F2：F8），那个公式含可选项，表示求D2到D8单元格中数值超过9

5分对应的F2到F8的数值之和。

65、数据中有单位时如何样求和：

（1）如下表数据所示，求销售额的总和：数据加了单位（汉子）之后

为文本，而sum函数只对数值能进行求和，因此需将文本转换为数值方可

求和，转换公式如下：=sum（value（substitute（B2:B9,”元”，“”）））或=s

um（--substitute（B2:B9,”元”，“”））；注意：sum的组合公式必须用Ctrl+

shift+enter终止得出结果；

66、substitute函数：

（1）用新字符串替换文本字符串中固定的字符使用substitute函数，如

果在某一文本字符串替换固定位置任意文本使用replace函数；

（2）substitute函数语法：=substitute（text，old-text，new-text，[insta

nce-num]）

讲明：文本，需要替换其中字符的文本或多含有文本（需要替换其中

字符）的单元格的引用；

old-text，需要替换的文本；

new-text，用于替换old-text的文本；

instance-num，可选，指定要用new-text替换old-text的事件，如果制

定了instance-num，则只有满足要求的old-text被替换，否则文本中显现的

所有old-text都会更换为new-text。

67、value函数：

substitute函数为文本函数，因此替换得到的数字也属于文本，需要将

文本转换为数值使用函数value或--，value函数能够将文本型转换成数值型；

语法：=VALUE(text)，Text必需。带引号的文本，或对包含要转换文

本的单元格的引用。

68、关于有附加值的求和：

（1）如下表中的数据，求所有人员的总分：=sum（if（isnumber

（B2:B9）,B2:B9,--(B2:B9&”/1”)））;

（2）Isnumber函数判定单元格的值是不是数值，=isnumber（值），如

果是数字就显示true，否则显示false。

69、表中数据插入一行实现自动汇总：

按Ctrl+F3——弹出“编辑名称”——名称“上一行”——引用位

置“当前表格的最后一行数据单元格”。

70、创建数据透视表中数据区域多选了一空行处理方式：

（1）选择任意一个日期——右键“组合”——同时选中月、季度、年

——确定；

（2）显示所有月份：单击任意月份——字段设置——布局和打印——

显示许多据选项——确定——取消对一些没用日期的勾选——数据透视表

选项——布局和格式——关于空单元格，显示后输入0——确定。

71、手工组合，实现客户分级：

创建一个按“金额”降序的客户销售数据透视表，选择金额大于1

0000的客户——右键——组合——确定——将”数据组1”拉倒最上面；

将剩下客户选中——右键——组合——确定，得到数据组2，重命名项

目即可。

73、借助辅助列。实现客户实际销售额分析：

重新创建数据透视表：

新创建

之前创建

76、数据透视图：

单击数据头时报任意单元格——选项——数据透视图——茶如图

表中选择需要的图表类型——设置图表。

77、excel中使用sql语句实现精确查找：

如下两个表格：

（1）统计表2中名单上的人在表1中的培训记录：

为表命名：选中表格后单击右键——选择“命名单元格区域”——弹

出“新建名称”——名称处输入table1、table2——确定；数据——自其他

来源——来自MicrosoftQuery选；在弹出的对话框中选择ExcelFiles*那

一项，同时把对话框下面的“使用“查询向导”创建/编辑查询”勾掉——

确定——显现“选择工作簿”的对话框——选择包含table1和table2项——

确定——弹出添加表的对话框——将Table1和Table2都添加一遍——单击

图五中输入SQL语句的按钮，弹出输入SQL语句的对话框——输入查询语

句：

SELECTTable1.姓名,Table1.时刻,Table1.培训内容,Table2.姓名

FROMTable1,Table2

WHERETable1.姓名=Table2.姓名，

将选择出来的数据表再返回至Excel工作表当中，选择菜单中的“文件”

——“将数据返回MicrosoftExcel”，如下所示：

图一

图二

图三

图四

图五

图六

图七

图八

图九

78、Excel中图表如何设置动态数据源（图表和数据自动更新）：

使用Excel表：选择图表的数据源区域（如：A1:B10）中的任意单

元格，在功能区中选择“插入”——单击“表”按钮，弹出“创建表”对

话框，单击“确定”按钮，Excel将图表数据源区域转换为Excel表，以后

在该区域下一行中添加数据，如本例中的第11行，图表将自动更新。

修改前图表

修改后图表

79、sumproduct函数：

例如：（1）下表是公司人员，所属工段，年龄，工资等信息。现在要

统计每个工段所有人员的工资总和：

从拆解工段开始。输入=SUMPRODUCT(($B$2:$B$9=A12)*($D$2:$D

$9));

$B$2:$B$9=A12，表示B2:B9区域中和A12单元格“拆解”一致的单

元格，

$D$2:$D$9确实是对应的工资区域，$表示绝对引用，那个专门重要，

保证公式复制到其他单元格时，查找区域可不能发生变化。

（2）统计拆解工段年龄＜30岁的人员的工资总和那么在公式中，就添

加($C$2:$C$9<30)，用乘号*与前面的相连。

整个公式为=SUMPRODUCT(($B$2:$B$9=A12)*($D$2:$D$9)*($C$2:

$C$9<30))

80、Text函数：

（1）Text函数：将数值转换为按指定数字格式表示的文本。

（2）语法：TEXT(value,format_text)；

Value：为数值、运算结果为数字值的公式，或对包含数字值的单元格

的引用；Format_text：为“单元格格式”对话框中“数字”选项卡上“分类”

框中的文本形式的数字格式（讲明：使用函数TEXT能够将数值转换为带

格式的文本，而其结果将不再作为数字参与运算）。

81、ifna函数（和iferror函数类似：如果value正确则返回正确结果，

否则返回value_if_error）：

（1）如果公式返回错误值#N/A，则结果返回您指定的值；否则返回

公式的结果。

（2）IFNA(value,value_if_na)I：

Value必需。用于检查错误值#N/A的参数。

Value_if_na必需。公式运算结果为错误值#N/A时要返回的值。

82、通过出生日期(A1)运算有关：

（1）运算星期：=text（A1，”aaaa”）;

（2）运算第几周：=weeknum(A1，2);

（3）运算今年几岁：=datedif(A1,now(),”y”);

（4）运算生肖：=mid(“鼠牛虎兔龙蛇马羊猴鸡狗猪”，mod(text(A1,”

[$-130000]e”)-4,12)+1,1);

（5）运算还有几天生日：=text（datedif(A1,now(),”yd”,”还有9天

生日;;今天生日”)）；