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+D 1，也有 D2=A2+B2+C2 ，还有 F3=A1+B1+C1+D1+E1 时。如何将计算式变为计算表达式。即计算式可以在任意一个单元格， 计算公式所引用数据的单元格可以任意变化，需要显示计算表达式的单元格也是任意的。针对第一种问题，分两步做：11，自定义一个函数Public FunctionK(x As Range)K = Right(x.Formula,Len(x.Formula)- 1)End Function自定义函数操作过程如下：工具宏 Visual Basic 编辑器右击插入模块再将上面的函数粘贴进去即可。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 中填写表格时 ,怎样在一格中换行填字 ?2输入一行后按 Alt+Enter 就可以换行5 自动切换输入法在一张工作表中， 往往是既有数据， 又有文字，这样在输入时就需要来回在中英文之间反复切换输入法，非常麻烦。 如果你要输入的东西很有规律性，比如这一列全是单词， 下一列全是汉语解释， 你可以用以下方法实现自动切换。 方法是：（1）选中要输入英文的列，单击 “数据 ”菜单，选择 “有效性 . 命”令，在弹出的 “数据有效性 ”对话框中， 选中 “输入法模式 ”选项卡，在 “模式 ”框中选择 “关闭（英文模式） ”命令，单击 “确定 ”按钮（如图 12）。（2）选中要输入汉字的列， 在“有效数据 ”对话框中，单击 “IME模式 ”选项卡，在 “模式 ”框中选择 “打开 ”命令，单击 “确定 ”按钮。这样，当光标在前一列时，可以输入英文，在下一列时， 直接可以输入中文， 从而实现了中英文输入方式之间的自动切换。6、把数据彻底隐藏起来工作表部分单元格中的内容不想让浏览者查阅，只好将它隐藏起来了。1.选中需要隐藏内容的单元格 (区域 )，执行 “格式 单元格 ”命令，打开 “单元格格式 ”对话框， 在“数字 ”标签的 “分类 ”下面选中 “自定义 ”选项，然后在右边 “类型 ” 下面的方框中输入 “； ”(三个英文状态下的分号 )。2.再切换到 “保护 ”标签下，选中其中的 “隐藏 ”选项，按 “确定 ”按钮退出。3.执行 “工具 保护 保护工作表 ”命令，打开 “保护工作表 ”对话框，设置好密码后， “确定 ”返回。经过这样的设置以后，上述单元格中的内容不再显示出来，就是使用 Excel 的透明功能也不能让其现形。提示：在“保护 ”标签下， 请不要清除 “锁定 ”前面复选框中的 “”号，这样可以防止别人删除你隐藏起来的数据。7、在 Excel 中自定义函数Excel 函数虽然丰富，但并不能满足我们的所有需要。我们可以自定义一个函数，来完成一些特定的运算。 下面，我们就来自定义一个计算梯形面积的函数：1.执行 “工具 宏 Visual Basic 编辑器 ”菜单命令 (或按 “ Alt+F11快”捷键 )，打开 Visual Basic 编辑窗口。2.在窗口中，执行 “插入 模块 ”菜单命令，插入一个新的模块 模块 1。33.在右边的 “代码窗口 ”中输入以下代码：FunctionV(a,b,h)V= h*(a+b)/2EndFunction4.关闭窗口，自定义函数完成。以后可以像使用内置函数一样使用自定义函数。提示：用上面方法自定义的函数通常只能在相应的工作簿中使用。8、单元格内容的合并解决办法是：在 C 行后插入一个空列（如果 D 列没有内容，就直接在 D 列操作），在 1 中输入 “=B1&C1”，D1 列的内容就是 B、 C 两列的和了。选中 D 1 单元格，用鼠标指向单元格右下角的小方块 “”，当光标变成 后，按住鼠标拖动光标向下拖到要合并的结尾行处，就完成了 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（ B 1,C1 ）”。比如，假设在某一河流生态调查工作表中， B2 包含 “物种 ”、B3 包含 “河鳟鱼 ”， B7 包含总数 45 ，那么： 输入 “=CONCATENATE（ 本次河流生态调查结果： ，B2 ，B3 ，为， B7， 条/公里。 ）”计算结果为：本次河流生态调查结果：河鳟鱼物种为 45 条/公里。9、条件显示我们知道，利用 If 函数，可以实现按照条件显示。一个常用的例子，就是教师在统计学生成绩时，希望输入 60 以下的分数时，能显示为 “不及格 ”；输入 60 以上的分数时，显示为 “及格 。这样的效果，利用 IF 函数可以很方便地实现。假设成绩在 A2 单元格中，判断结果在 A3 单元格中。那么在 A3 单元格中输入公式： =if（ A260 ， “不及格 ”，“及格 ”） 同时，在 IF 函数中还可以嵌套 IF 函数或其它函数。例如，如果输入： =if（ A260 ，“不及格 ”，if（ A2=90 ，“及格 ”，“优秀 ）就把成绩分成了三个等级。4如果输入 =if（A260 ，“差 ，if（A2=70 ，“中”，if（ A20 ，SUM （A1 ：A5 ）， 0） 此式就利用了嵌套函数，意思是，当A1 至 A5 的和大于 0 时，返回这个值，如果小于0，那么就返回 0。 还有一点要提醒你注意：以上的符号均为半角，而且 IF 与括号之间也不能有空格。创建条件格式可以使用六种逻辑符号来设计一个条件格式： （大于）、 =（大于等于）、（小于）、 =（小于等于）、 =（等于）、 （不等于），如果你觉得这些符号不好记，就干脆使用 “或”“=号”来表示。11、在 xcel 中小数点无法输入，按小数点，显示的却是逗号，无论怎样设置选项都无济于事，该怎么办？这是一个比较特殊的问题， 我曾为此花了十几个小时的时间， 但说白了很简单。在 indows 的控制面板中，点击“区域设置”图标，在弹出的“区域设置属性”对话面板上在“区域设置”里选择“中文（中国）”，在“区域设置属性”对话面板上在“数字”属性里把小数点改为“ .”（未改前是“，”），按“确定”按钮结束。这样再打开 Excel 就一切都正常了。12、如何快速地将数字作为文本输入？在输入数字前加一个单引号“”，可以强制地将数字作为文本输入。13、如何对工作簿进行安全保护？如果你不想别人打开或修改你的工作簿， 那么想法加个密码吧。打开工作薄，选择“文件”菜单中的“另存为”命令，选取“选项”，根据用户的需要分别输入“打开文件口令”或“修改文件 D 令”，按“确定”退出。工作簿（表）被保护之后，还可对工作表中某些单元格区域的重要数据进行保护， 起到双重保护的功能，此时你可以这样做：首先，选定需保护的单元格区域，选取“格式”菜单中的“单元格”命令，选取“保护”，从对话框中选取“锁定”，单由“确定”按钮退出。然后选取“工具”菜单中的“保护”命令，选取“保护工作表”，根据提示两次输入口令后退出。注意：不要忘记你设置有“口令”。14、工作表保护的口令忘记了怎么办？如果你想使用一个保护了的工作表， 但口令又忘记了， 有办法吗？有。 选定工作表，选择“编辑” “复制” 、“粘贴” ，将其拷贝到一个新的工作薄中 （注5意：一定要是新工作簿），即可超越工作表保护。当然，提醒你最好不用这种方法盗用他人的工作表。15、如何命名常数？有时，为常数指定一个名字可以节省在整个工作簿中修改替换此常数的时间。例如，在某个工作表中经常需用利率 4.9 来计算利息，可以选择“插入”“名字”“定 义”，在“当前工作薄的名字”框内输入“利率”，在“引用位置”框中输入“ ，按“确定”按钮。16、如何减少重复劳动？我们在实际应用 Excel 时，经常遇到有些操作重复应用 （如定义上下标等） 。为了减少重复劳动， 我们可以把一些常用到的操作定义成宏。 其方法是：选取“工具”菜单中的“宏”命令，执行“记录新宏”，记录好后按“停止”按钮即可。也可以用 VBA 编程定义宏。6