Excel妙用-.pdf
《Excel妙用-.pdf》由会员分享,可在线阅读,更多相关《Excel妙用-.pdf(30页珍藏版)》请在得力文库 - 分享文档赚钱的网站上搜索。
1、Excel 最重要的应用就是利用公式进行计算。无论输入是纯粹的数字运算,还是引用其他单元格计算,只要在一个单元格中输入公式,就能得到结果。这个直接显示结果的设计对于绝大多数场合来说都是适用的,但某些情况下就不那么让人满意了。比如说在做工程施工的预结算编写,使用Excel,既要写出工程量的计算式,也要看到它的结果,于是这样相同的公式在 Excel 里面要填两次,一次在文本格式的单元格中输入公式,一次是在数据格式的单元格中输入公式让Excel 计算结果。如何既能看到公式又能看到结果呢?这个问题笔者认为可以从两个方面考虑:一种方法是所谓“已知结果,显示公式”,先在数据格式单元格中输入公式让 Exce
2、l 计算结果,然后在相邻的单元格中看到公式;另一种方法所谓“已知公式,显示结果”,就是先在一个文本格式的单元格中输入公式,在相邻的单元格中看到结果。已知结果,显示公式假设 C列为通过公式计算得到的结果(假设C1为“=A1+B1”,或者直接是数字运算“=2+3”),而相邻的 D列是你需要显示公式的地方(即D1应该显示为“=A1+B1”或者“=2+3”)。1.打开“工具”菜单选择“选项”命令,出现“选项”对话框。2.在“常规”选项卡中,选中“R1C1引用方式”选项。3.定义名称,将“引用位置”由“=GET.CELL(6,Sheet1!RC-1)”即可。这里的 RC-1 含义是如果在当前单元格的同行
3、前一列单元格中有公式结果,则在当前单元格中得到公式内容,即在含公式结果单元格的同行后一列单元格显示公式内容;如果将 RC-1 改为 RC1,则在公式结果的同行前一列单元格显示公式内容。4.如果“引用位置”中含有“RC-1”,则在含公式结果单元格的同行后一列单元格中输入“=FormulaofResult”即可得到公式;如果“引用位置”中含有“RC1”,则在含公式结果单元格的同行前一列单元格中输入“=FormulaofResult”即可得到公式。提示:如果想要在含公式结果单元格的同行后数第2 列中显示公式内容,则需要把“引用位置”中的“RC 12已知公式,显示结果假设 C列为输入的没有等号公式(假
4、设C1为“A1+B1”),而相邻的 D列是你需要存放公式计算结果的地方(即D1显示 A1和 B1单元格相加的结果)。1.选中 D1,然后打开“插入”菜单选择“名称”命令中的“定义”子命令,出现“定义名称”对话框。2.在“在当前工作表中的名称”输入栏中输入定义的名称“ResultofFomula”,在下方的“引用位置”编辑栏中输入“=EVALUATE(Sheet1!C1)”,单击 确认 按钮退出。3.在 D1 中输入“=ResultofFomula”,然后选中按住右下角的填充柄向下拉动填充即可。提示:EVALUATEEexcel 4 0 版的宏表函数,Excel 2000 和 Excel 200
5、2中还支持,但只可用于名称定义中。4.填充后要按 F9 进行重算,如果 C列的公式有改动,也需要及时按F9进行重算。巧施妙计,就能让公式和结果在Excel 中和平共处了,你也试试吧实习一 Excel的基本用法 实习目的 掌握 Execl 的基本用法 实习步骤 一、基本功能Excel 是一种通用的电子表格软件,其功能强大、技术先进、使用方便,广泛应用于个人事务、商务以及统计和分析等领域。它有三大基本功能:1、电子表格:它以行和列的形式组织数据信息,表格中的数据可以进行计算、处理和分析。2、图表:它将数据信息以饼图、面积图、柱状图、散点图等形式来表示,利用这些图可以更直观地观察和分析数据。3、数据
6、库:数据库信息是行列结构的相关数据信息的集合,其中的数据可以进行排序、筛选,建立数据透视表,进行分类汇总和数据分析等。二、基本操作1 选定一个单元格:鼠标单击要输入数据的单元格,输入数据,回车。2 选定单元格区域:有时要选定几个单元格,即单元区域,单元区域的表示方法是区域中左上角的单元格地址:区域中右下脚的单元格地址。1).选定一个连续单元格区域:用鼠标拖或者用Shift键。2).选定正酣单元格:单击行号。3).选定整列单元格:单击列号。4).选定连续行和列:鼠标拖或者用Shift键。5).选定不连续的行和列:选定一行或一列后,按下Shift键的同时单击其它行或列。6).选定全部单元格:单击左
7、上角行号和列标相交处。7).选定不连续区域:使用Ctrl键。即选定一个单元格区域,按着Ctrl键的同时选定其它单元格或单元格区域。3.取消区域的选定:在选定一个单元格或单元格区域后,单击任何一各单元格都会取消前面的选定。4.数据的主要类型有四类:数字数据、文本数据、时间和日期。输入数字时,首先应定位单元格,然后直接用键盘输入即可。在默认时,所有数字在单元格中均右对齐。输入文本时,首先选取一个活动的单元格,然后用键盘直接输入即可。如果输入数字字符,则默认将其解释为数字。如果要将输入的数字解释为文本,则需要在输入数字之前加一个撇号(对应于键盘上的单引号)。该撇号只是作为数字的文本标识符,而并不是文
8、本的一部分。如果要输入日期和时间,首先选定单元格,然后直接输入即可。如果用户想在单元格中插入当前日期,可以按“Ctrl+;”键;如果想插入当前时间,那么可以按“Ctrl+Shift+;”键。5.插入单元格、行、列和工作表。1).插入单元格:选择菜单栏中的插入,选单元格。2).插入行:选择菜单栏中的插入,选行。3).插入列:选择菜单栏中的插入,选列。4).插入工作表:选择菜单栏中的插入,选工作表。以上插入都可以通过单击鼠标右键,利用快捷菜单中选项进行。6.合并单元格:选择菜单栏中的格式-单元格-对齐-合并单元格。7.数据的复制、剪切、和删除操作与一般常规操作一样,复制、剪切可以在不同的工作表和工
9、作簿中进行。三、运算符算术运算符含义示例+(加号)加3+3-(减号)减;负号3-2;-2(乘号)乘33/(斜杠)除3/3(百分号)百分比50(脱字符)乘幂32(与 3 3 相同)比较运算符含义示例=(等号)等于A1=B1(大于号)大于A1B1(小于号)小于A1=(大于等于号)大于等于A1=B1=(小于等于号)小于等于A1=B1(不等于)不等于A1B1 文本运算符含义示例&(连字符)将两个文本值连接起来产生一个连续的文本值“North”&“west”产生“Northwest”引用运算符含义示例:(冒号)区域运算符,对两个引用之间,包括两个引用在内的所有单元格进行引用B5:B15,(逗号)联合运算
10、符,将多个引用合并为一个引用SUM(B5:B15,D5:D15)相当于SUM(B5:B15)+SUM(D5:D15)(空格)交叉运算符,产生对同时隶属于两个引用的单元格区域的引用,类似于对单元格引用进行逻辑“交”运算SUM(B5:B15 A7:D7)计算同时隶属于两个区域的单元格(B7)的值的和四、一般数据处理和分析:排序、筛选、分类汇总、数据透视表1.排序利用 Excel 的排序工具,可以把工作表中的数据按一定的顺序要求重新排列,可以按行排列和按列排列。排列依据的主要列(或行)称为主关键字,在遇到主关键字相同时,可按关键字排序,依次类推。排序时数字型数据按数字大小,日期、时间先后,文本型可以
11、以字母顺序、拼音字母顺序或笔划顺序。可对下表数据排序。1).简单排序:利用常用工具栏的 升序 和 降序 两个按钮,可以进行简单的排序。排序时要选定需要排序的某一行或某一列的任一单元格。学号姓名英语数学语文物理990323 王小周87 88 98 78 990321 周平58 88 69 47 990322 李静71 63 64 86 990324 赵春飞96 69 78 96 2).按多序列排序:其步骤是:(1).选择数据清单中的任意单元格;(2).单击 数据 菜单的 排序 命令,弹出 排序 对话框。(3).在主关键字 下拉列表框中选择首先要排序的关键字,如“数学”(4).选次关键字 ,如“英
12、语”,然后单击确定。2.筛选利用筛选工具可以查看工作表中的所需要的部分数据1).自动筛选:使用自动筛选可以用某一列的条件进行快速筛选。据体操作是:(1).在数据清单中选定任意单元格、单击 数据 菜单中的 筛选 命令,然后从其级连菜单中选择 自动筛选 命令。(2).单击 自动筛选 命令后,数据清单中的每一字段名旁边显示向下箭头。单击所需筛选的字段名旁的箭头,在出现的下拉列表框中选择 筛选条件 。(3).如果在该下拉列表框种选择 自定义 选项,系统将给出 自定义筛选方式 对话框。可根据需要确定筛选条件(与、或条件)。2).高级筛选:高级筛选 命令能查找需要匹配包含复杂的“与”和“或”条件的数据,还
13、能从数据清单中提取筛选数据副本。高级筛选 命令要求在一个 数据清单 的不同工作区域里指定筛选条件,这个区域称为“条件区域”。因为当执行筛选程序时整行被隐藏,因此在放置条件区域时最好不要沿着数据清单(既不要在数据清单的左右两旁放置),而把它放在整个数据清单之上或之下,并且条件区域与数据清单区域之间最好空一行。可以在条件区域输入任意多的条件。Excel 根据以下规则解释:(1).同一行的条件被认为使用“与”进行筛选;(2).不同行的条件被认为使用“或”进行筛选。一个条件区域必须至少包含两行。在首行输入一个或多个列标题,在第二行和随后的行里输入筛选条件。除了计算的条件外,条件区域里的标题必须与数据清
14、单中的标题相匹配。使用 高级筛选 选项功能的具体操作如下:1).在条件区域输入筛选条件,如上表“英语”“70”(大于 70 分),“数学”“80”(小于 80 分)的学生;2).在数据清单中任选一单元格;3).选择 数据 菜单中的 筛选 项中的 高级筛选 命令,弹出 高级筛选 对话框;4).检查 数据区域 并在 条件区域 键入或者导入其位置;5).在“方式”下面选择一种筛选方式,然后单击确定;6).取消筛选:数据筛选全部显示。3.分类汇总使用 数据 菜单的 分类汇总 命令可以进行多种分类汇总。Excel 提供十一种汇总类型,他们是:求和、记数、均值、最大值、最小值、乘积、记数值、偏差、标准差、
15、公差和标准公差。系统默认的汇总方式是求和。如以下表为例进行分类汇总操作:姓名工资奖金部门张三1500 800 教务处李四1400 600 人事处王五1800 711 教务处赵六900 360 人事处刘七600 854 人事处吴八680 340 教务处 1).对需要分类汇总的字段进行排序,如按部门排序;2).在数据清单例选定任意单元格;3).选择 数据 菜单中的 分类汇总 对话框;4).在 分类汇总 的对话框中的 字段分类 、汇总方式 、选定会纵向 等下拉框中确定选项。如在 分类字段 中选择“部门”,在 汇总方式 中选择“求和”,在 选定汇总项 中选择“工资”。设置完毕后单击确定,完成分类汇总。
16、在分类汇总完成后的工作表左侧,有一个明显的分级控制版,最上方有1、2、3 三个按钮。单击 1 按钮,只显示第一级汇总总值,即全表总计;单击2 按钮,只显示部门汇总的情况;单击3 按钮才显示全部数据。4.数据透视表数据透视表是一种对现有工作表的数据进行重新组织,换一个角度对数据进行汇总、筛选和分析的工具。以下标为例说明创建数据透视表的具体操作:1).首先按表格所给的格式输入数据,然后单击数据区的一个单元格。2).选择数据菜单中的 数据透视表 命令,弹出 数据透视表向导 对话框。3).在 数据透视表向导 对话框中选择数据来源,单击 下一步 按钮、弹出 数据透视表向导步骤 2 对话框,在 选定区域
17、对话框中输入选定建立数据表格的数据区域(一般情况下,向导以经为用户选定了整个工作区域)。日期产品名称销售地区销售量7 月 16 日塑料东北1434.85 7 月 15 日钢材华南1540.5 7 月 22 日木材华北1200 7 月 21 日木材华南1324 7 月 20 日钢材西南678 7 月 23 日塑料西北222.2 7 月 17 日钢材东北951 7 月 25 日塑料西北1355.4 7 月 30 日塑料东北2324 7 月 29 日木材西南2183.2 4).单击 下一步 按钮,在弹出的 数据透视表向导步骤3 对话框中,分别对行、列和页字段及数据选项进行设置。设置时只要将有册子段按
18、钮中的内容用鼠标左键拖到左侧草图的响应位置即可。5).单击 下一步 按钮,在弹出的步骤4 对话框种选择数据透视表的位置。实习二绘制直方图和累积频数图 实习目的 掌握直方图和累积频数图的绘制方法 实习步骤 在绘制直方图之前先检查你的Excel 工具菜单中有没有“数据分析”一项。若没有,请单击这个菜单中的“加载宏”,然后在“加载宏”显示框中选定“分析工具库”,在这一项前边的方框中单击,出现对号即可。下表是 50 个湖泊溶解氧的数据。溶解氧是表示水质的一个重要指标,常温(20)下天然纯净下的水中溶解氧的含量是约为9.17MG/L。当污水排入湖泊,水里面的溶解氧会下降。所以溶解氧数据的大小可表明水的污
19、染程度。50 个湖泊中的溶解氧5.1 5.64.45.65.65.6 6.85.16.76.25.3 6.95.65.44.25.7 4.85.84.85.25.8 5.65.76.45.86.4 6.44.95.86.14.3 5.96.65.35.15.9 6.05.75.75.95.4 5.55.46.35.54.7 5.45.94.54.7绘制直方图操作步骤如下:1.选择工具菜单中的数据分析,在出现的对话框中选直方图,出现直方图对话框。在 输入区域 中导入数据区域,在 接受区域 中要你输入把数据分组的上边界值,不过不管它,则是默认的分组方法。如果你想按自己的想法分组,则需要首先在工作表
20、中选定一个连续区域,输入各组的上边界值,针对我们的数据,可将其分为6 组,他们的上边界是:4.49,4.99,5.49,5.99,6.49。然后在接受区域中把这几个数据引入。(在引入之前要先把光标移入接受区域)。在输出选线中选“新工作组”(或其他),点击此项前的小圆孔即可。选择累计百分率和图表输出,最后单击“确定”。2.修饰直方图:直方图分成两个区域,图表区域(黑框线内)和绘图区(灰色背景区域,有直方图和累计频数图)。1).扩大图表区域:先激活图表,图表边框上出现黑方块。鼠标箭头对准左(或右)下角方块,按住左键往左(或右)下方拉动即可。2).激活绘图区,双击灰色背景,可以改变背景颜色,双击任一
21、柱,可以改变柱的颜色。3).修饰标题:单击“直方图”三个字,鼠标箭头变成书写符号(光标),即可编辑。4).其他编辑:你可激活图表区域,然后单击鼠标右键,出现快捷菜单,可以选择有关各项进行编辑,例如“图表选项”和“图表类型”等。5).使直方图的长条没有间隔:在通常情况下用Excel 绘制的直方图长条间有间隔。为了消除直方图长条间的间隔,可在原直方图的任一长条上按右键,选择数据系列格式,打开 数据系列格式 对话框,点击右上边的选项 选项卡,是 间隔宽度 为 0,然后点击确定。直方图长条间的间隔就没有了。直方图05101520254.494.995.495.996.49其他频率.00%20.00%4
22、0.00%60.00%80.00%100.00%120.00%频率累积%实习三计算地理数据分布特征的代表值 实习目的 计算一组数据的均值、中位数、众数、离差、方差、标准差、最大值、最小值、总和、变差系数等。实习步骤 例如,对某一职工的日平均收入(元)所作的抽样调查数据是(已从高到低排列):69.0,63.5,59.6,59.5,55.3,52.6,52.0,51.6,49.5,49.4,48.7,48.0,47.4,45.4,40.6,38.7,36.8,34.4,33.4,33.0,32.4,30.6,26.2,25.3,22.7,22.0,20.1,13.4,9.2 一、利用函数计算其步骤
23、是:1.打开 Excel,新建一个工作簿:文件-新建-确定。2.输入数据(按行或列输入均可,也可分为几行几列)3.在适当的位置输入要计算的项目名称,即算术平均数(AVERAGE),几何均数(GEOMEAN),中位数(MEDIAN),众数(MODE),方差(VAR),最大值(MAX),最小值(MIN),总和(SUM)。4平均数的计算公式:平均数主要用于反映一组观察值的平均水平,描述计量资料的集中趋势,亦称中心位置指标。根据资料的分布及对称性不同分为算术平均数(AVERAGE)、几何均数(GEOMEAN)及中位数(MEDIAN)。标准差是描述一组资料的离散(或变异)程度的指标,与平均数形成对应,并
24、与平均数结合使用才能全面了解资料的分布情况。它包括样本标准差(Excel中用 STDEV 表示)及总体标准差(STDEVP)。操作步骤:单击常用工具按钮fx 图标(在中部偏右),出现粘贴函数框,在对话框左栏中选“统计”一项(也可选“全部”),然后在右栏中找AVERAGE,(如果框中没有,可移动滚动块去找,函数名称是按字母顺序排列的)。找到 AVERAGE 后选定,单击“确定”出现均值对话框,在 Number1栏中键入或导入数据区域,然后单击“确定”。5.求其他各项,步骤同4。6.当输入原始数据到工作表时或之后,可以直接使用由Excel 提供的公式,也可根据使用者的需要自己编制公式。求变差系数:
25、Excel 中没有直接计算变差系数的函数,可通过以下公式计算:变差系数=标准差/平均值二、利用描述统计计算我们也可以用Excel 软件一次性的把数据的分布特征的主要指标给计算出来。我们使用下表的数据,求悉尼和斯坦利。穆尔两地降水量的数据分布特征的有关指标。1933 年至 1952 年悉尼(澳大利亚)和斯坦利穆尔(英格兰德比郡)的年降水量(CM)年份悉尼斯坦利穆尔1935791251936771031937132123193899132193985118194010011719416812019421231141943129130194479104194518014419469210819471
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel 妙用
限制150内