三、函数的应用3、在Excel中自定义函数例3：去除几个最大和最小值后的平均值：Functionpj(x1,x2,x3,x4,
x5,x6,x7,x8,x9,x10,x11,x12,x13,x14,x15,x16)ma1=
0:ma2=0:ma3=0mi1=100:mi2=100:mi3=100:s=0
Dimx(16)x(1)=x1:x(2)=x2:x(3)=x3:x(4)=x4:x(5)
=x5:x(6)=x6:x(7)=x7:x(8)=x8x(9)=x9:x(10)=x10:x
(11)=x11:x(12)=x12:x(13)=x13:x(14)=x14:x(15)=x15x(
16)=x16Fori=1To16Ifx(i)>ma1Thenma1=x(i
):m=iNextix(m)=x(1):x(1)=ma1Fori=1To
16Ifx(i))=x(16):x(16)=mi1Fori=2To15Ifx(i)>ma2T
henma2=x(i):m=iNextix(m)=x(2):x(2)=ma2三、函
数的应用3、在Excel中自定义函数Fori=2To15Ifx(i)mi2=x(i):n=iNextix(n)=x(15):x(15)=mi2F
ori=3To14Ifx(i)>ma3Thenma3=x(i):m=iNe
xtix(m)=x(3):x(3)=ma3Fori=3To14Ifx(
i))=mi3Fori=4To13s=s+x(i)Nextis=
s/10Ifs=0Thenpj=""Elsepj=sEndFunction四、在Exc
el中编程MicrosoftExcel是一款功能非常强大的电子表格软件。它可以轻松地完成数据的各类数学运算，并用
各种二维或三维图形形象地表示出来，从而大大简化了数据的处理工作。但若仅利用Excel的常用功能来处理较复杂的数据，可能仍需进行大量
的人工操作。但Excel的强大远远超过人们的想象--宏的引入使其具有了无限的扩展性，因而可以很好地解决复杂数据的处理问题。
VBA是从流行的VisualBasic编程语言中派生出来的一种面向应用程序的语言，全称为VisualBasicApp
lication，它适用于各种Windows应用程序，可以解决各应用程序的宏语言不统一的问题。除此之外，使用VBA语言还有如下优点
：1、VBA是一种通用程序语言，通过它不仅可以共享Microsoft相关的各种软件（如Excel、Word、Access）……，而
且随着其它的一些软件（如大名鼎鼎的AutoCAD2000）等对VBA的支持，这些软件也已进入到了VBA的控制范围；2、可以将用VB
A编写的程序复制到VisualBasic中调试并运行，从而实现用VisualBasic来控制有关的应用程序；3、VBA提供的大量内
部函数大大简化了用户的操作。四、在Excel中编程A、通过VisualBasic编辑器编写：可以通过点击“工
具→宏→VisualBasic编辑器”打开的编辑环境，点击“插入→模块”，系统将打开一个代码窗口，就可以在这个窗口编写程序了。
有了编程环境后，我们来开发VBA之旅的第一个程序。在窗口中输入以下代码：SubMyFirstVBAProgr
am()DimstrNameAsStringDimstrHelloAsString
strName=InputBox("请输入你的名字：")strHello="你好，"&strNam
e&"!"MsgBoxstrHelloEndSub运行这段代码。运行结果会显示一个对
话框，输入一些内容后，会显示相应的问候语。1、如何编写VBA程序？四、在Excel中编程SubMyFirstVBAPro
gram()DimstrNameAsStringDimstrHelloAsString
strName=InputBox("请输入你的名字：")strHello="你好，"&strN
ame&"!"MsgBoxstrHelloEndSub1、如何编写VBA程序？我们
简单看一下这段代码的组成，代码第1行表示这是一个新的过程，名称为“MyFirstVBAProgram”，第2、3行定义了
2个变量，其类型为字符串类型，第4行调用InputBox这个内置函数，并将返回值赋给strName这个变量，第5
行将几个字符串组合成一个新的字符串，第6行调用MsgBox这个函数，显示一个对话框，第7行表示过程结束。VBA程序由不同
的模块组成，在模块内部，可以定义不同的变量、过程或函数，由此组成一个完整的程序。四、在Excel中编程B、通过录制宏来获得代码
：绝大多数的Excel操作，都可以录制为宏，而录制的宏可以作为开发的基础来使用，这点也是ExcelVBA开
发的一个特点。打开文本文件的代码录制过程为：(1).选择工具－宏－录制新宏；(2).在录制新宏对话框
中选择将宏保存在当前工作薄；单击确定开始；(3).进行所需要的操作；(4).停止录制；(5).然后就可以在
VBA的代码窗口中找到新录制的宏。1、如何编写VBA程序？四、在Excel中编程VBA究竟能做什么?更确切
地讲，它是一种自动化语言,它可以使常用的程序自动化,可以创建自定义的解决方案。此外，如果你愿意,还可以将EXCEL用做开发平台实
现应用程序。可以利用VBA来实现完成重复性的操作，或者EXCEL基本操作所不能完成的功能，或者通过VBA所编写的程序自动运行你想完
成的工作。下面主要通过一些工作中的实例，说明VBA的一些应用。2、VBA程序的应用谢谢各位！Excel的
使用技巧及高级应用一、Excel的使用技巧1.如何显示多行文本一般情况下，在单元格中输入的文本是不换行的，这
样看起来会很别扭。如果要在同一单元格中显示多行文本，可以这样做：选中要换行的单元格，单击“格式”菜单，选择“单
元格”命令，然后选择“对齐”选项卡，选中“文本控制”标题下的“自动换行”复选框。一、Excel的使用技巧1.如何显示多行文本
如果单元格中还有段落，就要在里面输入硬回车，这时Enter键的功能不是在单元格内换行，而是在单元格间移动。这时我
们可以在编辑栏（或正编辑的单元格）中要换行的地方单击鼠标，按ALT+ENTER键来换行。换行后的效果如下图，在编辑区文字是
换行的。一、Excel的使用技巧2.怎样同时改变多行行高或多列列宽？以改变行高为例，先选中要改变行高的列
，按下Shift键再单击行标题头，可以选定连续的多行（如果要选中多个不连续行，可以按下Ctrl键）。选中多列后，拖动任意一个被选中
的行标题间的分界线，到适当高度释放鼠标，所有被选中的行高都改变了。一、Excel的使用技巧3.如何改变回车键的功能？
一般情况下，我们在一个单元格中输入资料后，按下回车键后活动单元格会转入下一行的同列单元格中。如果要转到
右边的单元格，能不能实现呢？打开“工具”菜单，选择“选项”命令，单击“编辑”选项卡，多后从“方向”下拉列表框中选择“向右”，单击
“确定”。一、Excel的使用技巧4.巧用转置功能粘贴数据我们手头有这样一个数据表，行表示学生姓名，列表示
学生的各科成绩。如果我们想得到一个以行表示各科成绩，以列表示学生姓名的数据表，应该怎样做呢？其实很简单，EXCEL为我们提供了转置
功能。先选中要进行转换的数据区A1：D10，按下Ctrl+C将其复制，再将光标定位在另一个工作表的A1单元格
中，然后选择“编辑”菜单中的“选择性粘贴”命令。在“选择性粘贴”对话框中，选中“转置”复选框，单击“确定”。一、Excel的
使用技巧5.快速输入技巧在Excel的工作表中，如果在多个单元个中输入同一个公式，多次填写某一个字符或某一个有
规律数字，一个一个输入是很麻烦的，掌握一些技巧是可以快速输入的。选定要在多个单元格中输入同一个公式的这一区域，
在某一单元格中输入公式后安组合键Ctrl+Enter，那么所选区域那的所有单元格中就都输入了同一公式。1)、用组合键Ctrl+
Enter在多个单元格中输入同一个公式REPT工作表函数的语法为：REPT(text,number_timer
s)。其中，text为需要重复显示的字符串，不能多能多于255个字符；number_timers为指定文本进行重复的次数，为正数，
否则将截尾取整数。例如，要在某一单元格中重复显示“■”10次，则输入＝REPT("■",10)，回车即可。2
)、用REPT工作表函数在某一单元格中多次填写某一字符一、Excel的使用技巧5.快速输入技巧在Excel工
作表中，如果需要在一行或一列单元格中填入按顺序排列的项目序号，用Excel的自动填充功能可以帮助您快速输入这些数据序列，而且还不容
易出错。其方法是：在第一单元格那输入数据，在下一个单元格那输入第二个数据，选定这两个单元格，将光标指向单元格右下方的填充柄，沿着要
填充的方向拖动填充柄，拖过的单元格中会自动的按Excel内部规定的序列进行填充。如下图，在B3中输入“第1名”，然后拖动控制柄
往下拉，松开鼠标时所拉过的区域就自动填充了“第2名、第3名”。在拖动控制柄的过程中我们也可以在鼠标的右边小说明中看到所填充的内容。
3)、用填充柄自动填充工作表一、Excel的使用技巧5.快速输入技巧如果使用鼠标右键拖动单元格填充柄，
也可以实现自动填充。其方法是：在某单元格内输入数据，按住鼠标右键，沿着要填充的方向拖动填充柄，此时就会弹出包含下列各项的快捷菜单(
如下图)：复制单元格、以序列方式填充、以格式填充、以值填充、以天数填充、以工作日填充、以年填充、等差序列、等比序列。。。。此时，您
可以根据需要进行选择填充方式。显然，这种方法更有灵活性。3)、用填充柄自动填充工作表一、Excel的使用技巧6.快速移动
活动单元格经常要在数据区域的最开始和最末尾处、最左端和最右端处来回折腾吗？其实，用不着多次单击滚动条，也不用多
次滚动鼠标的滚轮的。只要将鼠标指向当前单元格的下边框，当鼠标指针变为四个箭头的十字形时双击，就可以使活动单元格迅速定位于当前数据区
域中当前列的最后一行，如图所示。双击单元格的上边框可以到达最上一行，而双击左边框和右边框则可以分别迅速到达最左端和最右端。快吧？
也可按Ctrl?+上下左右箭头可快速移动到数据清单的顶端、底部、最左边及最右边。一、Excel的使用技巧7
.多个工作表的同时输入和设置我们有时要把一个Excel文件中的多个工作表输入同样数据或设置成同样的格式，分别对一
张张工作表去输入和设置感觉很繁琐。如果用下面的方法就可以一次将多个工作表中的数据和格式同时完成：把鼠标移到工作表的名称处(如果没有
给每张表取名的话，Excel自动设置的名称就是Sheet1、Sheet2、Sheet3等等)，然后点右键，在弹出的菜单中选择“选择
全部工作表”的菜单项，这时再进行输入和格式设置就是针对全部工作表了。一、Excel的使用技巧8.防止误删工作表及数据
在包含多个工作表的工作薄中，为了防止误修改，我们常常采取将行(列)隐藏或者设置编辑区域的方法，但是如果要防止整个工作表的
误修改怎么办呢?单击“格式→工作表→隐藏”，将当前的工作表隐藏，这样操作者连表格都看不到，误操作就无从谈起了。要重新显示该表格，只
须单击“格式→工作表→取消隐藏”。要注意的是：如果设置了工作表保护，则不能进行隐藏操作。为了防止误修改数据，可
以采取数据锁定保护的方法，选定不需要锁定保护的区域，单击“格式→单元格→保护”，将“√”去掉，确定后再选择“工具→保护→保护工作表
”，这时可以选择保护密码，确定后就可以保护锁定区域的数据了。快速隐藏：在英文状态下，按“Ctrl+9”或“Ctr
l+0”组合键，就可以快速隐藏光标所在的行或列，Shift+Ctrl+9或0取消隐藏。一、Excel的使用技巧9.$符号的应用
在工作表的单元引用地址有4种：A1（相对列和相对行）$A1（绝对列和相对行）A$1（相对列和绝对行）$A$1（绝对列和绝
对行）在复制或者拖拽时要保持引用单元的地址不变，就要采用绝对列或绝对行。一、Excel的使用技巧10.条件格式的应用
在学生成绩表中，如果想让小于60分的额以“红色”显示，我们可以这样设置：1）.选中各科成绩单元，执行“
格式→条件格式”命令，打开“条件格式”对话框。单击第二个方框右侧的下拉按钮，选中“小于”选项，在后面的方框中输入数值“60”。单击
“格式”按钮，打开“单元格格式”对话框，将“字体”的“颜色”设置为“红色”。2）.设置完成后，按下“确定”按钮。
一、Excel的使用技巧11.建立分类下拉列表填充项我们常常要将数据输入到表格中，为了保持数据的一致性，利
用“数据有效性”功能建了一个分类下拉列表填充项。1）.在Sheet2中，将企业名称按类别（如“工业企业”、“商业
企业”、“个体企业”等）分别输入不同列中，建立一个企业名称数据库。2）.选中A列（“工业企业”名称所在列），在“
名称”栏内，输入“工业企业”字符后，按“回车”键进行确认。仿照上面的操作，将B、C……列分别命名为“商业企业”、“个体企业”……
3）.切换到Sheet1中，选中需要输入“企业类别”的列（如C列），执行“数据→有效性”命令，打开“数据有效性”
对话框。在“设置”标签中，单击“允许”右侧的下拉按钮，选中“序列”选项，在下面的“来源”方框中，输入“工业企业”，“商业企业”，“
个体企业”……序列（各元素之间用英文逗号隔开），确定退出。再选中需要输入企业名称的列（如D列），再打开“数据有效性”对话框，选中
“序列”选项后，在“来源”方框中输入公式：=INDIRECT（C1），确定退出。4）.选中C列任意单元格（如C4
），单击右侧下拉按钮，选择相应的“企业类别”填入单元格中。然后选中该单元格对应的D列单元格（如D4），单击下拉按钮，即可从相应类别
的企业名称列表中选择需要的企业名称填入该单元格中。一、Excel的使用技巧12.Excel中“摄影”功能这
个功能比较另类，似乎和计算、统计、排序等等“正统”功能格格不入，因为它的作用竟然是——“抓屏”!而且还不是像“PrintScree
n”按钮那样“一把乱抓”，而是允许让你通过鼠标进行选择，“指哪抓哪”。要找到这个功能还不太容易，我们点击Exce
l“工具”菜单的“自定义”选项。在“命令”卡片的“类别”中点“工具”，再在命令栏中找到“摄影”按钮，并将它拖到工具栏的任意位置。如
果我们想要对表格中的某一部分“照相”，只须先选择它们（图23），然后按下“摄影”按钮，这时该选定区域就被“拍”了下来。然后将鼠标移
动到需要显示“照片”的地方（当然，也可以是另一张表格），再次按下“摄影”按钮，这样刚才被“拍摄”的“照片”就立即粘贴过来了。当然，
和“照片”一同出现的还有“图片”工具栏。很显然，Excel是将它作为一幅图片来对待了，我们可随意将它进行旋转、缩放处理。
不过，这可不是一般的照片!你可以试着改动原来被我们“拍摄”下来的表格数据看看——刷新一下数据，结果“照片”中的数据竟然也被
同步更新了!二、Excel图表制作（一）.柱形图EXCEL提供了14类100多种基本的图表，包括柱形图、饼图
、条形图、面积图、折线图、气泡图以及三维图。图表可以用来表现数据间的某种相对关系，在常规状态下我们一般运用柱形图比较数据间的多少关
系;用折线图反映数据间的趋势关系;用饼图表现数据间的比例分配关系。运用Excel的图表制作可以生成多种类型的图表，下面以柱形图、折
线图、饼图三种类型为例，分别介绍其制作方法。1.选取要生成图表的数据区,单击“插入”工具栏中的“图表”，显示图表
向导第一步骤——图表类型对话框，选择图表类型为柱形图，单击“下一步”。2．进入图表向导第二步骤——图表数据源，根据
需要选择系列产生在行或列，单击“下一步”。3．进入图表向导第三步骤——图表选项，此时有一组选项标签，用来确定生成的
图表中需要显示的信息（如图表标题、轴标题、网格线等，可根据个人生成图表的需要选择）。4．通常直接单击“下一步”进入图
表向导第四步骤——图表位置，在默认状态下，程序会将生成的图表嵌入当前工作表单中。如果希望图表与表格工作区分开,选择新工作表项，输入
新表单的名称（本例使用默认状态）。5．单击“完成”按钮，就生成了比较数据的柱形图。二、Excel图表制作（二）折
线图在最终生成的柱形图中，用鼠标右键单击图区域，显示命令列表，从中选择图表类型选项，显示图表类型对话框，从对话框
中选择折线图，单击“确定”按钮后就生成了反映数据趋势的折线图。（三）饼图同折线图的生成步骤基本一样，只要从“图
表类型”对话框中选择“饼图”，单出“确定”按钮，就能生成反映数据比例分配的饼图。说明:在图表的制作过程中、制作完成后均有很多种修
饰项目，可根据自己的爱好和需要，按照提示，选择满意的背景、色彩、子图表、字体等修饰图表。三、函数的应用Exce
l的强大，不仅在于它易于编排表格和计算，而在于它有完善的函数功能，可以进行很多数据处理。1.Excel函数的选择
在用函数处理数据时，常常不知道使用什么函数比较合适。Excel的“搜索函数”功能可以帮你缩小范围，挑选出合适的函数。
执行“插入→函数”命令，打开“插入函数”对话框，在“搜索函数”下面的方框中输入要求（如“计数”），然后单击“转到”按钮，系统
即刻将与“计数”有关的函数挑选出来，并显示在“选择函数”下面的列表框中。再结合查看相关的帮助文件，即可快速确定所需要的函数。三、
函数的应用2.常用函数的应用1）if函数可以使用函数IF对数值和公式进行条件检测。
语法IF(logical_test,value_if_true,value_if_false)
Logical_test表示计算结果为TRUE或FALSE的任意值或表达式。例如，A10=100就是
一个逻辑表达式，如果单元格A10中的值等于100，表达式即为TRUE，否则为FALSE。本参数可使用任何比较运算符。
例：=IF(C2<60,"不及格","及格")=IF(CB2>89,“A”,IF(C
2>79,“B”,IF(C2>69,“C”,IF(C2>59,“D”,“F”))))if函数最多可套7层。
三、函数的应用2.常用函数的应用2）rank函数返回一个数字在数字列表中的排位。数字的排位是
其大小与列表中其他值的比值（如果列表已排过序，则数字的排位就是它当前的位置）。语法RANK(n
umber,ref,order)Number为需要找到排位的数字。Ref为数字列表
数组或对数字列表的引用。Ref中的非数值型参数将被忽略。Order为一数字，指明排位的方式。
如果order为0（零）或省略，MicrosoftExcel对数字的排位是基于ref为按照降序排列的列表。
如果order不为零，MicrosoftExcel对数字的排位是基于ref为按照升序排列的列表。
例：=RANK(C2,C$2:C$5)三、函数的应用2.常用函数的应用3）large函数
返回数据集里第k个最大值。语法LARGE(array,k)A
rray数据集例：=LARGE(C$2:C$5,1)类似还有small()函数。
4）round函数(四舍五入)返回某个数字按指定位数取整后的数字。语法
ROUND(number,num_digits)Number需要进行四舍五入的数字。
Num_digits指定的位数，按此位数进行四舍五入。例：=ROUND(2.149,1)将2.1
49四舍五入到一个小数位(2.1)=ROUND(-1.475,2)将-1.475四舍
五入到两小数位(-1.48)=ROUND(21.5,-1)将21.5四舍五入到小数点左
侧一位(20)三、函数的应用2.常用函数的应用5）VLOOKUP函数Excel表格和Wo
rd中的表格最大的不同就是Excel是将填入表格中的所有内容（包括静态文本）都纳入了数据库的范畴之内。我们可以利用VLOOKUP的
函数查询功能，对目标数据进行精确定位，找到所需要的数据。比如在如图所示的表格中，从A1到F7的单元格中输入了多名
同学的各科成绩。而在A8到A13的单元格中我们则建立了一个“函数查询”区域。我们的设想是，当我们在“输入学生姓名”右边的单元格，也
就是C8格中输入任何一个同学的名字后，其下方的单元格中就会自动显示出该学生的各科成绩。具体实现的方法如下：三、函数的应用2.
常用函数的应用将光标定位到C9单元格中，然后单击“插入”之“函数”选项。在弹出的窗口中，选择“VLOOKUP”
函数，点“确定”。在随即弹出的“函数参数”窗口中我们设置Lookup_value”（指需要在数据表首列中搜索的
值）为“C8”（即搜索我们在C8单元格中填入的人名）；“Table_array”（指数据搜索的范围）为“A2∶B6”（即在所有学生
的“语文”成绩中搜索）；“Col_vindex_num”（指要搜索的数值在表格中的序列号）为“2”（即数值在第2列）；“Range
_lookup”（指是否需要精确匹配）为“FALSE”（表明不是。如果是，就为“TURE”）。设定完毕按“确定”。三、函数的应用
2.常用函数的应用此时回到表格，单击C9单元格，我们看到“fx”区域中显示的命令行为“=VLOOKUP（C8，
A2∶B6，2，FALSE）”。复制该命令行，在C10、C11、C12、C13单元格中分别输入：“=VLOOKUP（C8，A2∶C
6，3，FALSE）”；“=VLOOKUP（C8，A2∶D6，4，FALSE）”；“=VLOOKUP（C8，A2∶E6，5，FAL
SE）”；“=VLOOKUP（C8，A2∶F6，6，FALSE）”。接下来，我们就来检验“VLOOKUP”函数
的功效。试着在“C8”单元格中输入某个学生名，比如“赵耀”，回车之下我们会发现，其下方每一科目的单元格中就自动显示出该生的入学成绩
了。类似的函数还有Offset，Lookup等。三、函数的应用2.常用函数的应用6）文本类函数
文本类函数主要是处理文本类的单元。主要有：=left()：返回左起指定字符数=right
()：返回右起指定字符数=mid()：返回指定字符位置起的指定字符数=len()：返回字符串长
度=lower()：全部小写=upper()：全部大写=proper()
：首字母大写=trim()：清除字符串前后的空格=value()：将文本形式的数字转化为数值
=text()：将数值转化为文本=substitute()：文本替换函数三、函数的应用2.
常用函数的应用6）文本类函数例：有时候，我们可能对某个单元格中字符的数量感兴趣，需要计算单元格
中的总字数。要解决这个问题，除了利用到“SUBSTITUTE”函数的虚拟计算外，还要动用“TRIM”函数来删除空格。比如现在A1单
元格中输入有“howmanywords?”字样，那么我们就可以用如下的表达式来帮忙：=LEN(SUBSTITUTE(TRIM
(A1),"",""))该式的含义是先用“TRIM”函数删除其前后可能的空格，并且利用“SUBSTITUTE”函数将字符间的空
格替换掉，创建一个无空格的新字符串，然后用长度函数就得出单元格中字符的数量了。三、函数的应用3、在Excel中自定义函数
Excel函数虽然丰富，但并不能满足我们的所有需要。我们可以自定义一个函数，来完成一些特定的运算。例1：下面，我们
就来自定义一个计算梯形面积的函数：1).执行“工具→宏→VisualBasic编辑器”菜单命令（或按“Alt+F11”快捷键），打开VisualBasic编辑窗口。2).在窗口中，执行“插入→模块”菜单命令，插入一个新的模块——模块1。3).在右边的“代码窗口”中输入以下代码：FunctionV（a,b,h）V=h（a+b）/2EndFunction4.关闭窗口，自定义函数完成。以后可以像使用内置函数一样使用自定义函数。提示：用上面方法自定义的函数通常只能在相应的工作簿中使用。三、函数的应用3、在Excel中自定义函数三、函数的应用3、在Excel中自定义函数例2：将阿拉伯数字的金额转化为大写金额：Functionvtoc(num)s=Str(Int(num100+0.5))Ifnum=0Thenl=1Elsel=Len(s)dx="零壹贰叁肆伍陆柒捌玖"dw="佰拾万仟佰拾元角分"wz=""Form=1ToLen(dw)-l+1wz=wz+"／"+Mid(dw,m,1)NextmFori=2Tolzz=Mid(s,i,1)sz=Val(zz)+1wz=wz+Mid(dx,sz,1)+Mid(dw,Len(dw)-l+i,1)Nextivtoc=wzEndFunction |
|
