Excel表格自动计算技巧.docx

Excel表格自动计算技巧.docx

《Excel表格自动计算技巧.docx》由会员分享，可在线阅读，更多相关《Excel表格自动计算技巧.docx（5页珍藏版）》请在冰豆网上搜索。

Excel表格自动计算技巧

Excel表格自动计算技巧之迟辟智美创作

一、显示单位格例有计算式的结果的设置方法

首先：

拔出-名称-界说在弹出的对话框“以后工作薄的名称”中输入:

X或“结果”的自界说名称，再在“引用位置”处粘贴＝EVALUATE（SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（计算!

$C$1,"[","（"）,"]","）"）,"×","*"）,"÷","/"））　公式（注意要有“＝”号.再对公式中“计算!

$C$1”选择上，然后再到需要做公式的单位格中点击即可.如要相对引用，则要删除$字符.

已经OK，你在C1输入表达式比如15+5×3，在D1中输入=x看看（应该是30）.

二、如何在Excel中输入计算式后另一单位显示计算结果

菜单--拔出--名称--界说：

输入AA（任意取名），在下面输入公式:

=EVALUATE（$A$1）

然后在B1单位格输入公式：

=AA

但本式不能识别如：

[　、×、÷　等符号进行计算，使用第一种较好.

三、如何在Excel中编写自界说函数，象在表格中调用SUM（）一样？

第一种情况：

单位格A1=2；单位格B1=2；单位格C1=2

单位格D1=（A1+B1）×C1显示结果为8；

那么如何才华在单位格E1中显示（2+2）×2的计算表达式，而且建立关联，当单位格D1酿成=（A1+BA）^C1计算式后，显示结果为16；那么E1也就自动显示为（2+2）^2的计算表达式，也就是说随着单位格D1的计算公式变动，单位格E1显示的计算表达式也随之变动.

第二种情况：

是当计算的单位格任意变动时，怎么办？

比如说计算式E1=A1+B1+C1+D1，也有D2=A2+B2+C2，还有F3=A1+B1+C1+D1+E1时.如何将计算式酿成计算表达式.即计算式可以在任意一个单位格，计算公式所引用数据的单位格可以任意变动，需要显示计算表达式的单位格也是任意的.

针对第一种问题，分两步做：

1，自界说一个函数

PublicFunctionK（xAsRange）

K=Right（x.Formula,Len（x.Formula）-1）

EndFunction

自界说函数把持过程如下：

工具－宏－VisualBasic编纂器－右击－拔出模块－再将上面的函数粘贴进去即可.

2，在E1中写入公式

=SUBSTITUTE（SUBSTITUTE（SUBSTITUTE（k（D1）,"A"&ROW（A1）,A1）,"B"&ROW（B1）,B1）,"C"&ROW（C1）,C1）

其它往下拖拉即可.I当需要增加几个参数应用的单位格时，相应要增加几个：

SUBSTITUTE（　，再相应增加几个如：

"F"&ROW（F1）,F1）　.

工具--》宏--》平安性 里面选择 无（不建议使用），这样加载宏的时候系统不会每次都提示.

**自界说函数也可以换成使用宏表函数GET.CELL（）

第二个问题，请同理照推

四、编纂技巧

1　分数的输入

如果直接输入“1/5”，系统会将其酿成“1月5日”，解决法子是：

先输入“0”，然后输入空格，再输入分数“1/5”.

2　序列“001”的输入

如果直接输入“001”，系统会自动判断001为数据1，解决法子是：

首先输入“'”（西文单引号），然后输入“001”.

3　日期的输入

如果要输入“4月5日”，直接输入“4/5”，再敲回车就行了.如果要输入以后日期，按一下“Ctrl+；”键.

在Excel中填写表格时,怎样在一格中换行填字?

输入一行后按Alt+Enter就可以换行

5　自动切换输入法

在一张工作表中，往往是既有数据，又有文字，这样在输入时就需要来回在中英文之间反复切换输入法，非常麻烦.如果你要输入的工具很有规律性，比如这一列全是单词，下一列全是汉语解释，你可以用以下方法实现自动切换.方法是：

（1）选中要输入英文的列，单击“数据”菜单，选择“有效性...”命令，在弹出的“数据有效性”对话框中，选中“输入法模式”选项卡，在“模式”框中选择“关闭（英文模式）”命令，单击“确定”按钮（如图12）.

（2）选中要输入汉字的列，在“有效数据”对话框中，单击“IME模式”选项卡，在“模式”框中选择“翻开”命令，单击“确定”按钮.这样，当光标在前一列时，可以输入英文，在下一列时，直接可以输入中文，从而实现了中英文输入方式之间的自动切换.

6、把数据完全隐藏起来

工作表部份单位格中的内容不想让浏览者查阅，只好将它隐藏起来了.

1.选中需要隐藏内容的单位格（区域），执行“格式→单位格”命令，翻开“单位格格式”对话框，在“数字”标签的“分类”下面选中“自界说”选项，然后在右边“类型”下面的方框中输入“；；；”（三个英文状态下的分号）.

2.再切换到“呵护”标签下，选中其中的“隐藏”选项，按“确定”按钮退出.

3.执行“工具→呵护→呵护工作表”命令，翻开“呵护工作表”对话框，设置好密码后，“确定”返回.

经过这样的设置以后，上述单位格中的内容不再显示出来，就是使用Excel的透明功能也不能让其现形.

提示：

在“呵护”标签下，请不要清除“锁定”前面复选框中的“∨”号，这样可以防止他人删除你隐藏起来的数据.

7、在Excel中自界说函数Excel函数虽然丰富，但其实不能满足我们的所有需要.我们可以自界说一个函数，来完成一些特定的运算.下面，我们就来自界说一个计算梯形面积的函数：

1.执行“工具→宏→VisualBasic编纂器”菜单命令（或按“Alt+F11”快捷键），翻开VisualBasic编纂窗口.

2.在窗口中，执行“拔出→模块”菜单命令，拔出一个新的模块——模块1.

3.在右边的“代码窗口”中输入以下代码：

FunctionV（a,b,h）V=h*（a+b）/2EndFunction

4.关闭窗口，自界说函数完成.

以后可以像使用内置函数一样使用自界说函数.

提示：

用上面方法自界说的函数通常只能在相应的工作簿中使用.

8、单位格内容的合并

解决法子是：

在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条/公里.

9、条件显示

我们知道，利用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与括号之间也不能有空格.

创立条件格式

可以使用六种逻辑符号来设计一个条件格式：

>（年夜于）、>=（年夜于即是）、<（小于）、<=（小于即是）、=（即是）、<>（不即是），如果你觉得这些符号欠好记，就干脆使用“>”或“>=”号来暗示.

11、在Ｅxcel中小数点无法输入，按小数点，显示的却是逗号，无论怎样设置选项都无济于事，该怎么办？

这是一个比力特殊的问题，我曾为此花了十几个小时的时间，但说白了很简单.在Ｗindows的控制面板中，点击“区域设置”图标，在弹出的“区域设置属性”对话面板上在“区域设置”里选择“中文（中国）”，在“区域设置属性”对话面板上在“数字”属性里把小数点改为“.”（未改前是“，”），按“确定”按钮结束.这样再翻开Excel就一切都正常了.

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

在输入数字前加一个单引号“”’，可以强制地将数字作为文本输入.

13、如何对工作簿进行平安呵护？

如果你不想他人翻开或修改你的工作簿，那么想法加个密码吧.翻开工作薄，选择“文件”菜单中的“另存为”命令，选取“选项”，根据用户的需要分别输入“翻开文件口令”或“修改文件D令”，按“确定”退出.工作簿（表）被呵护之后，还可对工作表中某些单位格区域的重要数据进行呵护，起到双重呵护的功能，此时你可以这样做：

首先，选定需呵护的单位格区域，选取“格式”菜单中的“单位格”命令，选取“呵护”，从对话框中选取“锁定”，单由“确定”按钮退出.然后选取“工具”菜单中的“呵护”命令，选取“呵护工作表”，根据提示两次输入口令后退出.

注意：

不要忘记你设置有“口令”.

14、工作表呵护的口令忘记了怎么办？

如果你想使用一个呵护了的工作表，但口令又忘记了，有法子吗？

有.选定工作表，选择“编纂”＼“复制”、“粘贴”，将其拷贝到一个新的工作薄中（注意：

一定要是新工作簿），即可超越工作表呵护.固然，提醒你最好不用这种方法盗用他人的工作表.

15、如何命名常数？

有时，为常数指定一个名字可以节省在整个工作簿中修改替换此常数的时间.例如，在某个工作表中经常需用利率％来计算利息，可以选择“拔出”＼“名字”＼“界说”，在“以后工作薄的名字”框内输入“利率”，在“引用位置”框中输入“＝”，按“确定”按钮.

16、如何减少重复劳动？

我们在实际应用Excel时，经常遇到有些把持重复应用（如界说上下标等）.为了减少重复劳动，我们可以把一些经常使用到的把持界说成宏.其方法是：

选取“工具”菜单中的“宏”命令，执行“记录新宏”，记录好后按“停止”按钮即可.也可以用VBA编程界说宏.