Excel表格使用技巧大全.doc

《Excel表格使用技巧大全.doc》由会员分享，可在线阅读，更多相关《Excel表格使用技巧大全.doc（6页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Excel表格自动计算技巧 1、点D3，插入名称定义2、定义的名称可取JX,公式栏里写EVALUATE（C3）3、D3中的公式为：ABS（JX）一、显示单元格例有计算式的结果的设置方法首先：插入-名称-定义在弹出的对话框“当前工作薄的名称”中输入: X或“结果”的自定义名称，再在“引用位置”处粘贴EVALUATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(计算!$C$1,(),),*),/)公式(注意要有“”号。再对公式中“计算!$C$1”选择上，然后再到需要做公式的单元格中点击即可。如要相对引用，则要删除$字符。已经OK，你在 C1输入表达式 比

2、如 15+53 ，在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

3、计算式后，显示结果为16；那么E1也就自动显示为 (2+2)2的计算表达式，也就是说随着单元格D1的计算公式变化，单元格E1显示的计算表达式也随之变化。第二种情况：是当计算的单元格任意变化时，怎么办？比如说计算式E1=A1+B1+C1+D1，也有D2=A2+B2+C2，还有F3=A1+B1+C1+D1+E1时。如何将计算式变为计算表达式。即计算式可以在任意一个单元格，计算公式所引用数据的单元格可以任意变化，需要显示计算表达式的单元格也是任意的。针对第一种问题，分两步做：1，自定义一个函数Public Function K(x As Range)K = Right(x.Formula, Len(

4、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)。工具-宏-安全性 里面选择 无（不建议使用），这样加载宏的时候系统不会每次都提示。*自定义函数也可以换成使用宏表函数 GE

5、T.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自动切换输入法在一张工作表中，往往是既有数据，又有文字，这样在输入时就需

6、要来回在中英文之间反复切换输入法，非常麻烦。 如果你要输入的东西很有规律性，比如这一列全是单词，下一列全是汉语解释，你可以用以下方法实现自动切换。方法是：（1）选中要输入英文的列，单击“数据”菜单，选择“有效性.”命令，在弹出的“数据有效性”对话框中，选中“输入法模式”选项卡，在“模式”框中选择“关闭（英文模式）”命令，单击“确定”按钮（如图12）。（2）选中要输入汉字的列，在“有效数据”对话框中，单击“IME模式”选项卡，在“模式”框中选择“打开”命令，单击“确定”按钮。这样，当光标在前一列时，可以输入英文，在下一列时，直接可以输入中文，从而实现了中英文输入方式之间的自动切换。6、把数据彻底

7、隐藏起来工作表部分单元格中的内容不想让浏览者查阅，只好将它隐藏起来了。1.选中需要隐藏内容的单元格(区域)，执行“格式单元格”命令，打开“单元格格式”对话框，在“数字”标签的“分类”下面选中“自定义”选项，然后在右边“类型”下面的方框中输入“；”(三个英文状态下的分号)。2.再切换到“保护”标签下，选中其中的“隐藏”选项，按“确定”按钮退出。3.执行“工具保护保护工作表”命令，打开“保护工作表”对话框，设置好密码后，“确定”返回。经过这样的设置以后，上述单元格中的内容不再显示出来，就是使用Excel的透明功能也不能让其现形。提示：在“保护”标签下，请不要清除“锁定”前面复选框中的“”号，这样可

8、以防止别人删除你隐藏起来的数据。7、在Excel中自定义函数Excel函数虽然丰富，但并不能满足我们的所有需要。我们可以自定义一个函数，来完成一些特定的运算。下面，我们就来自定义一个计算梯形面积的函数：1.执行“工具宏Visual Basic编辑器”菜单命令(或按“Alt+F11”快捷键)，打开Visual Basic编辑窗口。2.在窗口中，执行“插入模块”菜单命令，插入一个新的模块模块1。3.在右边的“代码窗口”中输入以下代码：Function V(a,b,h)V = h*(a+b)/2End Function4.关闭窗口，自定义函数完成。以后可以像使用内置函数一样使用自定义函数。提示：用上

9、面方法自定义的函数通常只能在相应的工作簿中使用。8、单元格内容的合并解决办法是：在C行后插入一个空列（如果D列没有内容，就直接在D列操作），在1中输入“=B1&C1”，D1列的内容就是B、C两列的和了。选中D1单元格，用鼠标指向单元格右下角的小方块“”，当光标变成后，按住鼠标拖动光标向下拖到要合并的结尾行处，就完成了B列和C列的合并。这时先不要忙着把B列和C列删除，先要把D列的结果复制一下，再用“选择性粘贴”命令，将数据粘贴到一个空列上。这时再删掉B、C、D列的数据。下面是一个“&”实际应用的例子。用AutoCAD绘图时，有人喜欢在EXCEL中存储坐标点，在绘制曲线时调用这些参数。存放数据格式

10、为“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，

11、B3，为，B7，条/公里。）” 计算结果为：本次河流生态调查结果：河鳟鱼物种为45条/公里。9、条件显示我们知道，利用If函数，可以实现按照条件显示。一个常用的例子，就是教师在统计学生成绩时，希望输入60以下的分数时，能显示为“不及格”；输入60以上的分数时，显示为“及格。这样的效果，利用IF函数可以很方便地实现。 假设成绩在A2单元格中，判断结果在A3单元格中。那么在A3单元格中输入公式： =if（A260，“不及格”，“及格”） 同时，在IF函数中还可以嵌套IF函数或其它函数。例如，如果输入： =if（A260，“不及格”，if（A2=90，“及格”，“优秀） 就把成绩分成了三个等级。如果

12、输入=if（A260，“差，if（A2=70，“中”，if（A20，SUM（A1：A5），0） 此式就利用了嵌套函数，意思是，当A1至A5的和大于0时，返回这个值，如果小于0，那么就返回0。 还有一点要提醒你注意：以上的符号均为半角，而且IF与括号之间也不能有空格。创建条件格式可以使用六种逻辑符号来设计一个条件格式：（大于）、=（大于等于）、（小于）、=（小于等于）、=（等于）、（不等于），如果你觉得这些符号不好记，就干脆使用“”或“=”号来表示。11、在xcel中小数点无法输入，按小数点，显示的却是逗号，无论怎样设置选项都无济于事，该怎么办？这是一个比较特殊的问题，我曾为此花了十几个小时的时

13、间，但说白了很简单。在indows的控制面板中，点击“区域设置”图标，在弹出的“区域设置属性”对话面板上在“区域设置”里选择“中文（中国）”，在“区域设置属性”对话面板上在“数字”属性里把小数点改为“.”（未改前是“，”），按“确定”按钮结束。这样再打开Excel就一切都正常了。12、如何快速地将数字作为文本输入？在输入数字前加一个单引号“”，可以强制地将数字作为文本输入。13、如何对工作簿进行安全保护？如果你不想别人打开或修改你的工作簿，那么想法加个密码吧。打开工作薄，选择“文件”菜单中的“另存为”命令，选取“选项”，根据用户的需要分别输入“打开文件口令”或“修改文件D令”，按“确定”退出。

14、工作簿（表）被保护之后，还可对工作表中某些单元格区域的重要数据进行保护，起到双重保护的功能，此时你可以这样做：首先，选定需保护的单元格区域，选取“格式”菜单中的“单元格”命令，选取“保护”，从对话框中选取“锁定”，单由“确定”按钮退出。然后选取“工具”菜单中的“保护”命令，选取“保护工作表”，根据提示两次输入口令后退出。注意：不要忘记你设置有“口令”。14、工作表保护的口令忘记了怎么办？如果你想使用一个保护了的工作表，但口令又忘记了，有办法吗？有。选定工作表，选择“编辑”“复制”、“粘贴”，将其拷贝到一个新的工作薄中（注意：一定要是新工作簿），即可超越工作表保护。当然，提醒你最好不用这种方法盗

15、用他人的工作表。15、如何命名常数？有时，为常数指定一个名字可以节省在整个工作簿中修改替换此常数的时间。例如，在某个工作表中经常需用利率4.9来计算利息，可以选择“插入”“名字”“定 义”，在“当前工作薄的名字”框内输入“利率”，在“引用位置”框中输入“ 0.04.9”，按“确定”按钮。16、如何减少重复劳动？我们在实际应用Excel时，经常遇到有些操作重复应用（如定义上下标等）。为了减少重复劳动，我们可以把一些常用到的操作定义成宏。其方法是：选取“工具”菜单中的“宏”命令，执行“记录新宏”，记录好后按“停止”按钮即可。也可以用VBA编程定义宏。6自 宏定算 用巧可即钮“好，新记执”的单工选法

16、其义操用一以，复少。标义如复操到常时 应动劳少如钮钮确” .“输置用“利入框的作“， ”“入“以，算 利常表工在。的此改中个在可字指为数数如表作他用方好醒，。作越可作新一：中的一到其粘、制辑择表定有法，记口表作护一办办忘口护工”令置记不出后令次据”表“，令“中菜取后出”“单锁选中，护取命格“菜”选区元保选首样可时功保到护进据域格某作可后之）作工定按” 修或件打别需用根项“，存的菜文选薄开吧加法簿的改打想不护保进作何入本文将制可”引个数入入为数快何了常都 再。钮定“”“未”为小性字“板”属区，”中“里设在面”属设出，”域击，板的 。简说间小十此我题特个办怎事无选设论逗的示点入法数 示来”或”脆干

17、不些觉如于（、等=于（=小等大、）：式个计号种格格条格有也间与且半号上：你提有0就么于如这返于和至 思数函利此0） （ （ 式公级个了绩就）优，“ “= ，“ 级个了成 ）秀”“0 ”不 输数它其 嵌中 ， ”及格，（=：输元 那中单果断格 成 地以数用，样。“为时的 ；及为能时下 入时绩统师是的常示条按可， 道显显条里里条物河结调河本结 。 为 ，结态生 入 ，数包，鱼含包种含 中作生河在如” “体中个并字干用的，函 用方种，格同）-如（内的和对，元 向用了要就据的此 & ：输在列并列将“后然到放， 标将标坐 在形 “为放数这调曲在点存 在人， 子用应”个据的、 再时空到粘，令性“用制果 把

18、删 把忙先这合列成就行的到下动标，成当”块角格向标格单中和的、容的， 入中，作 就内列果列入后在合的元用使工应只数义自面数函用一函使像成成义，闭 , 码下输口码的右 块块的入，单块插执口口辑 开键” “(菜辑 具行数数面计一自我面算的成来数义以。有所我并，虽函 数自 据数藏除别防这，的选前定除请下”在形其不能的 就来显内格元，设样回回“码置设”工“，”工护保“出钮”按选隐的中签护到切)的文三；“框的型边在项义“面类“标数框”格“开命格格行域格单藏需中了来隐将查浏不的格部起藏据换切间方文英而，输接，一，入，时在，。按“，”“选式“，选 “，对数“在的要选） （”“击令式（“中”“卡式法中中对性数

19、的在令.效“单数击列入中是是换动方以可，是一，是一比律很入你如麻，输复反中来要输样字，数既中工入入换换 后字行格在时写填键” “下期前要了行再”“输”月“输期日”0入输号引（先法决 断自统，0接输”0“”/ 输格入后0输：决日月变其系/入输输的分技技照照，( 函用换也义示都会系的宏，）议无择面 全- )( 几增再 几增，元的应几要 拉下 , ) ) ( 公写 可去进的面插右辑 宏下程过函 . ( 函义做做，问的意格的式示需变意元据数公，格意在算即达为式将。 + ， +=有 算比么时变格的况情化之式算的显单化公的元着说，算 ) 自就那 为显算 )+(成 单联建并达计+显 元能何 果示 单 单 格元 元况情样)(用中，义写编 何好较一算行号如能 =入格 $( 式入下，名(输义-结算显另算入 何)是( 输 如式表 ，符符要用相。击格的公要然，”$计中对”意式)*,)( 计 ( ( 贴置用再义自结 :输的作前话弹义称插方设结算例元 （ 为公 （ 里栏,名定定，