3.1 一百只青蛙
案例背景 上世纪90年代当本人刚接触Excel时,曾看过微软公司出的一本介绍该公司产品性能的一本名为“21世纪知识工作者完备手册”的宣传手册,其中在“Excel”篇中有一个十分有趣的案例叫“一百只青蛙”。案例中应用函数精妙的设计了公式,使本人从此对Excel产生了浓厚的兴趣。我把它作为第二篇的开篇内容介绍给大家,希望也能够引起学员们对学习Excel的兴趣。案例的内容是这样的: “一只青蛙一张嘴,两只眼睛四条腿,扑通一声跳下水; 两只青蛙两张嘴,四只眼睛八条腿,扑通扑通跳下水; 三只青蛙三张嘴,六只眼睛十二条腿,扑通扑通扑通跳下水; ……. 一百只青蛙一百张嘴,二百只眼睛四百条腿,扑通…..扑通跳下水。 要求用Excel设计公式,通过公式的复制填充完成案例内容。 关键技术点 要实现本案例中的功能,学员应该掌握以下EXCEL技术点。 ●基础知识:数字的特殊格式 条件格式 ●函数应用:Rept函数,Mod函数,Row函数 最终效果展示
3.1.1创建“童谣”工作簿 通过观察上面“最终效果展示”图,可以发现表格中的第2、4、6、8,10列为不变的文本内容,第1、3、5、7列为相互之间关联的数字内容,第9列虽为文本,但内容就是“扑通”两字,只是重复次数就是所在行的行数。 Step 1创建工作簿 新建一个Excel工作簿“Book1“,创建工作簿“童谣.xls”,然后将工作表重命名为“一百只青蛙”并删除多余的工作表。 Step2输入“童谣”第一行内容 ①在A1单元格输入“1”,C1单元格输入“1”,E1单元格输入“2”,G1单元格输入“4”。 ②分别在B1单元格输入“只青蛙”,D1单元格输入“张嘴”,F1单元格输入“眼睛”。H1单元格输入“条腿”,I1单元格输入“扑通一声”,J1单元格输入“跳下水”。
Step3输入“童谣”第二行内容 ①选中单元格区域A1:J1,将鼠标移到J1单元格右下角,当光标变为+形状时向下拖曳填充柄至第二行,然后松开鼠标即可完成公式的填充。 ②将E2单元格中的”3”改为“4”,G2单元格中的“5”改为8,I2单元格中的“扑通一声”改为“扑通扑通”。
Step4在数字单元格中设计公式 观察前两行内容,可以发现以下规律,B列、D列、F列、H列和J列的内容都是文字且内容是不变的,也可以说下一行的内容与上一行相同;A列、C列、E列和G列的内容都是数字,其中,A2单元格的内容比A1单元格的内容增加“1”,C2单元格的内容与A2单元格的内容相等,E2单元格的内容是A2单元格的内容的2倍,G2单元格的内容是A2单元格的内容的4倍,观察出规律后便可以设计公式了。 ①在A2单元格输入公式“=A1+1”。 ②在C2单元格输入公式“=A2”。 ③在E2单元格输入公式“=A2*2” ④在G2单元格输入公式“=A2*4”。 Step5在文字单元格中设计公式 按照案例要求,第I列的内容应该是“扑通”两字的反复重复,重复的次数代表有多少只青蛙跳下水了,而I2单元格的内容是“扑通”重复两次,其规律应该是重复次数该单元格所在的行数。看出了规律下面设计文字单元格的公式。 在B2单元格输入公式“=B1”。 ②在D2单元格输入公式“=D1”。 ③在F2单元格输入公式“=F1”。 ④在H2单元格输入公式“=H1”。 ⑤在J2单元格输入公式“=J1”。 ⑥光标选中I2单元格,单击常用工具栏“插入函数”,弹出对话框,从弹出的对话框中的“或函数类别”选项框中选择“文本”,从“选择函数”选项框中选择“Rept”函数.
⑦单击确定按钮,弹出“函数参数”对话框,在第一个参数框“Text”中输入“扑通”,在第二个参数框“Number-times”中输入“A2”,单击确定按钮或按键确认即可完成公式设计。
Step6复制填充公式 选中单元格区域A2:J2, 将鼠标移到J2单元格右下角,当光标变为+形状时向下拖曳填充柄至第一百行,然后松开鼠标即可完成公式的填充。
Step7设置数字的“特殊”格式 ①光标选中A列,单击菜单“格式”→“单元格”,切换到数字选项卡,在“分类”选项框中选择“特殊”,在“类型”选项框中选择“中文小写数字”,
②单击确定按钮或按键确认即可完成第一列数字的显示形式由“阿拉伯数字”形式到“中文小写数字”形式的转换。
③左键双击常用工具栏“格式刷”,光标有“空心十字”变成““空心十字”和右边一个小刷子时(此时表示可以多次使用此格式刷,将格式传递给目标区域),单击C列、E列和G列标,完成C列、E列和G列数字的显示形式由“阿拉伯数字”形式到“中文小写数字”形式的转换。再单击常用工具栏“格式刷”,撤销对“格式刷“的选择。
3.1.2美化“一百只青蛙“工作表 图8中有些单元格显示出一排符号“######“,这是因为数字单元格的内容超过了单元格的默认宽度8.38厘米,若要显示完整内容,就需要适当调整列宽。 Step1调整列宽 选中A列至H列区域A:H,单击菜单“格式“→”列“→”最合适的列宽”,完成对列宽的调整。由于I列最多有200个字符就不再调整其列宽了。
Step2设置表格边框 选中单元格区域A1:I100,单击菜单“格式“→”单元格“,弹出“单元格格式”对话框。切换到“边框”选项卡在“单元格格式”对话框的“颜色”选项框中选择“海绿”,“外边框”线条选择“双线”,“内部”选择“虚线“,单击确定按钮即可完成边框设置。 Step3设置文字的“字号”、“字体”和“对齐”方式 选中单元格区域A1:I100,单击常用工具栏“字号”选择四号,“字体”,选择”宋体”,对齐方式选择“居中”。 Step4利用条件格式设置表格“底纹” ①选中单元格区域A1:I100,单击菜单“格式“→”条件格式“,弹出对话框。
在弹出的“条件格式“对话框中的”条件1“的下拉列表中选择”公式“,在其右边的公式框中输入” =MOD(ROW(),2)=0 “。 ③然后单击“格式“按钮,弹出”单元格格式“对话框,切换到”图案“选项卡,选择”浅黄“颜色。
④单击确定按钮,返回“条件公式”对话框。
⑤单击“添加“按钮,弹出新的”条件格式“对话框,再在”条件2“的下拉列表中选择”公式“,在其右边的公式框中输入” =MOD(ROW(),2)=1 “,然后单击“格式“按钮,弹出”单元格格式“对话框,选择”浅绿“颜色,单击确定按钮,返回“条件公式”对话框。
⑥单击确定按钮,完成”表格”底纹的设置,这时,奇数行的底纹为浅绿色,偶数行的底纹为浅黄色。
关键知识点讲解 1.REPT函数 函数名称:REPT 主要功能:按照给定的次数重复显示文本。可以通过函数 REPT 来不断地重复显示某一文本字符串,对单元格进行填充。 使用格式:REPT(text,number_times) 参数说明: Text 需要重复显示的文本。 Number_times 是指定文本重复次数的正数。 函数说明: ●如果 number_times 为 0,则 REPT 返回 ""(空文本)。 ●如果 number_times 不是整数,则将被截尾取整。 ●REPT 函数的结果不能大于 32,767 个字符,否则,REPT 将返回错误值 #VALUE!。 应用举例:
2.MOD函数 函数名称:MOD 主要功能:返回两数相除的余数。结果的正负号与除数相同。 使用格式:MOD(number,divisor) 参数说明: Number 为被除数。 Divisor 为除数。 函数说明: ●如果 divisor 为零,函数 MOD 返回错误值 #DIV/0!。 ●函数 MOD 可以借用函数 INT 来表示: ●MOD(n, d) = n - d*INT(n/d) 应用举例:
3.Row函数 函数名称:Row 主要功能:返回引用的行号。 使用格式:ROW(reference) 参数说明:Reference 为需要得到其行号的单元格或单元格区域。 函数说明: ●如果省略 reference,则假定是对函数 ROW 所在单元格的引用 ●如果 reference 为一个单元格区域,并且函数 ROW 作为垂直数组输入,则函数ROW 将 reference 的行号以垂直数组的形式返回。 ●Reference 不能引用多个区域。 应用举例:
|