excel2007使用技巧资料

1让你的表格飞起来ExcelTraining产品管理部-杨庆2Excel2007基本界面3让你的表格飞起来•选择性粘贴•相对地址和绝对地址•数据透视表•一些常用的函数•绘图技巧•简单的数据分析工具•打印表格的小技巧4选择性粘贴5选择性粘贴我们手头有这样一个数据表，行表示学生姓名，列表示学生的各科成绩。如果我们想得到一个以行表示各科成绩，以列表示学生姓名的数据表，应该怎样做呢？其实很简单，EXCEL为我们提供了转置功能。先选中要进行转换的数据区A1：D10，按下Ctrl+C将其复制，再将光标定位在另一个工作表的A1单元格中，然后选择“编辑”菜单中的“选择性粘贴”命令。在“选择性粘贴”对话框中，选中“转置”复选框，单击“确定”。6选择性粘贴姓名性别武功级别老顽童男1郭靖男1杨过男2石破天男2姓名性别武功级别黄蓉女1老顽童男1郭靖男1任盈盈女2杨过男2石破天男2男筛选7选择性粘贴当你在对一组数据进行了筛选的功能，并想将筛选好的数据copy到另一页上，但不想将不符合筛选条件的也同时copy过去时，可以试试选择性粘贴中的跳空项。先选中要进行转换的数据区，按下Ctrl+C将其复制，再将光标定位在另一个工作表的A1单元格中，然后选择“编辑”菜单中的“选择性粘贴”命令。在“选择性粘贴”对话框中，选中“跳空”复选框，单击“确定”。8选择性粘贴如何快速地批量修改数据？假如有一份Excel工作簿，里面有所有职工工资表。现在想将所有职工的补贴增加50（元），当然你可以用公式进行计算，但除此之外还有更简单的批量修改的方法，即使用“选择性粘贴”功能：首先在某个空白单元格中输入50，选定此单元格，选择“编辑”＼“复制”。选取想修改的单元格区域，例如从E2到E150。然后选择“编辑”＼“选择性粘贴”，在“选择性粘贴”对话框“运算”栏中选中“加”运算，按“确定”健即可。最后，要删除开始时在某个空白单元格中输入的50。9相对地址和绝对地址Excel一般使用相对地址来引用单元格的位置，当把一个含有单元格地址的公式拷贝到一个新的位置，公式中的单元格地址会随着改变。你可以在列号或行号前添加符号“$”来冻结单元格地址，使之在拷贝时保持固定不变。=A2相对地址=$A$2绝对地址-行列都绝对=A$2行绝对地址=$A2列绝对地址按一次F4按二次F4按三次F4按四次F410数据透视表如果你有一组销售人员的业绩数据，其中人员和他们每个月，甚至每天的业绩的排列都没有规律，怎样最快地统计出1-12月每个人的业绩呢？你可以通过数据透视表实现。1.点击数据表中的任何一个单元格，选“数据”“数据透视表和数据透视图”。并选中“下一步”。2.确定你要透视的数据表的范围，再选中“下一步”。3.选“布局”。MonthSalesmanSales1Eric772David593Anna734Dysny55Henry386Jack347Edward498Peter909Cindy810Vivian1611Sunny812Porter341Dysny162Henry623Jack74Edward595Peter3311数据透视表4.在右边列出的字段按钮拖放到左边的布局图上就可以构成数据透视表了。5.我们选定了行为Month,列列出Salesman的名字。6.这样如下的表格构成了。7.数据透视表可供选择的数据计算方式有求和，平均值，最大值，计数等等。求和项:SalesSalesmanMonthAnnaCindyDavidEricPeterVivian总计19977161932596243164380921724598614654333766572077779331128831202039331001331027911181166874122684110总计2483362932012812191,57812数据透视表8.已经生成的数据透视表，如果原始数据更改，只要在点击了数据透视表，右边出现了数据透视表的工具栏，点击“更新”按键，即可。9.生成的数据透视表也可以任意通过从工具栏上拖放字段，或将不需要显示的字段隐藏来更改。10.如果要将数值的计算方式从求和更改为均值或其他，只要双击左上角的“求和”，在弹出的选择表中选择所需要的计算方式就可以了。13函数Excel函数一共有11类，分别是数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数以及用户自定义函数。如果你对需要使用的Excel函数的用法不熟悉，可以点击Toolbar上的fx按钮，弹出的提示框，会提示你具体的用法。14基础数学函数最大值Max计算数组或引用中的空白单元格、逻辑值或文本将被忽略,但0值不被忽略Max(Array)最小值MinMin(Array)平均值AverageAverage(Array)第K个最大值LargeLarge(Array,K)第K个最小值SmallSmall(Array,K)中位数Median所谓中位数是指在一组数据中居于中间的数，换句话说，在这组数据中，有一半的数据比它大，有一半的数据比它小。Median(Array)求出现频率最多的数ModeMode(Array)15基础数学函数的应用16If函数17If函数我们知道，利用If函数，可以实现按照条件显示。一个常用的例子，就是教师在统计学生成绩时，希望输入60以下的分数时，能显示为“不及格”；反之显示为“及格”。假设成绩在A2单元格中，判断结果在A3单元格中。那么在A3单元格中输入公式：=if（A260，“不及格”，“及格”）同时，在IF函数中还可以嵌套IF函数或其它函数。如果输入=if（A260，“差，if（A2=70，“中”，if（A290，“良”，“优”）））就把成绩分为了四个等级。再比如，公式：=if（SUM（A1：A5）0，SUM（A1：A5），0）此式就利用了嵌套函数，意思是，当A1至A5的和大于0时，返回这个值，如果小于0，那么就返回0。还有一点要提醒你注意：以上的符号均为半角，而且IF与括号之间也不能有空格18Index函数INDEX用于返回表格或区域中的数值或对数值的引用。INDEX(array,row_num,column_num)返回数组中指定单元格或单元格数组的数值。Array为单元格区域或数组常数。–Row_num为数组中某行的行序号，函数从该行返回数值。–Column_num为数组中某列的列序号，函数从该列返回数值。–需注意的是Row_num和column_num必须指向array中的某一单元格，否则，函数INDEX返回错误值#REF!。比如要返回韦小宝的出现剧作，如果你知道韦小宝在这个数据表中是第三行，出现剧作的是第四列，可以用这样的语句实现＝index(A1:D7,3,4)19Lookup函数LOOKUP函数可以返回向量（单行区域或单列区域）或数组中的数值。此系列函数用于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。当比较值位于数据表的首行，并且要查找下面给定行中的数据时，使用函数HLOOKUP。当比较值位于要进行数据查找的左边一列时，使用函数VLOOKUP。其基本语法形式为HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)VLOOKUP(lookup_value,table_array,col_index_num,range_lookup))比如要返回韦小宝的出现剧作，但你并不知道韦小宝在这个数据表中是第几行，但你知道“出现剧作”的是第四列。可以用这样的语句实现＝vlookup(“韦小宝“,A1:D7,4,false)20巧妙地使用Lookup函数和命名区域在Vlookup或Hlookup语句中有一些小技巧可以提高语句的准确性和易用性：1.查找的首行名称可以不用“韦小宝”这样的输入字段，而可以将“韦小宝”放入某一个单元格，这里放入了A11，这样，VLookup公式可以用Vlookup（A11,A1:D7,4,false)代替原来的Vlookup（“韦小宝”,A1:D7,4,false)使之更方便。2.如果用已经命名好的Range代替原来Vlookup中的A1:D7的数组说明，也可以更快捷。例如，我们定义了A1:D7这个区域的名称叫金庸，这样，原来的vlookup函数可以这样写：vlookup(A11,金庸,4,false).3.命名一个区域的名称可以有多种方法，最直接的方法就是，把要命名的区域选中，直接在ExcelToolbar的左上方的名称框中键入金庸，即可。27常用文本函数Text将数值转换为按指定数字格式表示的文本。TEXT(value,format_text)LenLEN返回文本字符串中的字符数LENB(text)RightRIGHT根据所指定的字符数返回文本字符串中最后一个或多个字符RIGHT(text,num_chars)LEFT基于所指定的字符数返回文本字符串中的第一个或前几个字符。LEFT(text,num_chars)MidMID返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。MID(text,start_num,num_chars)28常用文本函数姚明本次进球36得篮板12=B1&TEXT(C1,0)&&B2&TEXT(C2,0)姚明本次进球36得篮板12Text函数姚明本次进球36得篮板12=LEFT(F1,2)球员=姚明=RIGHT(F1,2)进球数12=MID(F1,7,2)得篮板36Right\left函数29日期函数30用图表说话通常，一个销售人员在表现他的客户数的增加和销售额的增加时，会用两张不同的图表。其实，将这两组紧密相关的数据表达在同一张图上的效果可能更好。但是由于客户数和销售额的数量出现在同一张图上数值差距很大，出现下面的图。-1,0002,0003,0004,0005,0006,0007,00019971998199920002001200231用图表说话Steps:1)点击代表销售额的红色的数组，将图标类型更改为线图2)选中代表销售额的线图，在数据系列格式的坐标轴选项中选中“次坐标轴”3)调整你需要的柱图和线图颜色4)将主坐标轴和次坐标轴的颜色调整到和柱图/线图相同的颜色，使得阅读者可以一目了然。-100200300400500600700800199719981999200020012002-1,0002,0003,0004,0005,0006,0007,000StoreUnitSales(MM)32用图表说话25428431839052730347213717210020030040050060070019981999200020012002ExistingstoreNewstoreMoreandmorenewstoreeveryyrpulltotalstorenumbergrowfast33用图表说话1)调整背景区域的网格线和边框线为“无”3)修改数组颜色，标志的颜色等，使之更醒目。2)选中任意一组数据，右键并选中“数据系列格式”，在“选项”中将“分类间距”调整为0254284318390527303472137172010020030040050060070080019981999200020012002Existingnewstore34运用图表巧妙求回归案例：某分析人员想了解各城市人口和可乐销售的关系，他没有选用复杂的函数，而是用图表得出了简单的：城市人口（万）可乐销售（万听）A16246,545A107428,676A114656,998A1291010,645A136448,230A144916,485A156919,878A162282,795A177148,522A1898511,717A193764,482A23594,237A202781,956A217098,473A224977,777A232183,089A241722,099A253534,185