Excel高手技巧Word下载.docx
《Excel高手技巧Word下载.docx》由会员分享,可在线阅读,更多相关《Excel高手技巧Word下载.docx(37页珍藏版)》请在冰豆网上搜索。
“班”——Enter
注:
MID(A2,4,1)表示从A2单元格的字符串中的第四位开始,截取一个字符。
&
为连接运算符,可以将两个文本字符串连接在一起。
10、【数据的分类汇总】
通过分类汇总功能求出每个班各科的平均成绩,并将每组结果分页显示。
①对班级进行数据排序。
②分类汇总:
③
11、【创建图表】
以分类汇总结果为基础,创建一个族状柱形图,对每个班各科平均成绩进行比较,并将该图表放置在一个名为“柱状分析图”新工作表中。
①
②
002
1、计算一二季度销售总额。
——ENTER
2、【PANK函数的应用】
在不改变原有数据顺序的情况下,按一二季度销售总额给出销售额排名。
在E2单元格中输入:
=RANK(D2,$D$2:
$D$21,0)——Enter
RANK是排位函数,
“=RANK(D2,$D$2:
$D$21,0)”是指求取D2单元格中的数值在单元格区域D2:
D21中的降序排列。
3、【数据透视表的建立】
✓选择产品销售汇总图表内A1:
E21单元格区域内容,建立数据透视表,行标签为产品型号,列标签为产品类别代码,求和计算一二季度销售额的总计,将表置于现工作表G1为起点的单元格区域内。
4、【数据导入法之<
自网站>
导入】
将网页“第五次全国人口普查”中的数据导入导入excel中。
5、【合并计算功能】
将两个工作表内容合并,合并后的工作表放置在新工作表“比较数据”中(自A1单元格开始),且保持最左列仍为地区名称、A1单元格中的列标题为“地区”。
以“地区”为关键字对工作表“比较数据”进行升序排列。
●A1中输入【地区】。
●数据——数据工具——合并计算
●开始——单元格——格式——行高——列宽
●开始——排序和筛选——排序——自定义排序——地区、数值、升序
6、在合并的工作表“比较数据”中的数据区域最右边依次增加“人口增长数”和“比重变化”,计算这两列的值,并设置合适的格式。
其中人口增长数=2010年人口数-2000年人口数;
比重变化=2010年比重-2000年比重。
●F2单元格中输入=B2-D2ENTER
●G2单元格中输入=C2-E2ENTER
003
1、【日期格式的设定】
●设置单元格格式——数字——自定义——输入“yyyy年m月d日aaaa”。
2、【IF函数和WEEKDAY函数的用法】
如果日期列中的日期为星期六或星期日,则在“是否加班”列的单元格中显示“是”,否则显示“否”。
●在H3单元格中输入:
=IF(WEEKDAY(A3,2)>
5,"
是"
"
否"
ENTER
3、【left函数的应用】
使用公式统计每个活动地点所在的省份或直辖市,并将其填写在“地区”列所对的单元格中。
●在D3单元格中输入:
=LEFT(C3,3),表示取当前文字左侧前三个字符。
4、【SUMIFS函数的应用】
在差旅成本分析报告工作表B3单元格中,统计2013年第二季度发生在北京市的差旅费用总金额。
①
SUMIFS函数的语法为:
SUMIFS(求和区域,条件区域1,条件1,[条件区域2,条件2],…)
SUMIFS函数为多条件求和函数。
例二:
在差旅成本分析报告中,统计2013年员工钱顺卓报销的火车票费用总额。
5、【SUMIF函数和SUM函数的组合用法】
在差旅成本分析报告工作表中,统计2013年差旅费用中,飞机票费用占所有报销费用的比例,保留2位小数。
●在B5中输入:
=SUMIF(费用报销管理!
F3:
F401,"
飞机票"
费用报销管理!
G3:
G401)/SUM(费用报销管理!
G401)
●Enter
004
1、序号的连续输入:
先输入前2行,然后拖动鼠标。
2、单元格的命名:
鼠标右键——定义名称。
3、运用公式计算工作表“销售情况”中F列的销售额,在平均单价表中查找相关商品的单价,并在公式中引用所定义的名称为“商品均价”。
●在F4单元格中输入:
=VLOOKUP(D4,商品均价,2,0)*E4enter
005
1、连续序号的输入:
在第一个空格处填入1,然后按住ctrl键向下拉。
2、【IF函数的应用】
参考“工资薪金所得税率”,利用IF函数计算“应交个人所得税”列。
(应交个人所得税=应纳税所得额*对应税率-对应速算扣除数)
●在L3中输入:
=IF(K3<
=1500,K3*3/100,IF(K3<
=4500,K3*10/100-105,IF(K3<
=9000,K3*20/100-555,IF(K3<
=35000,K3*25%-1005,IF(K3<
=55000,K3*30%-2755,IF(K3<
=80000,K3*35%-5505,IF(K3>
80000,K3*45%-13505)))))))
3、分类汇总时,应该先排序。
一般选择升序。
006
1、总分数:
公式:
=SUM(D3:
L3)
平均分:
=AVERAGE(M3/9)
年级排名:
=RANK(M3,M$3:
M$102,0)
2、【班级序号的填写】
根据公式将班级填入:
=IF(MID(B3,3,2)="
01"
法律一班"
IF(MID(B3,3,2)="
02"
法律二班"
03"
法律三班"
法律四班"
)))
007
1、颜色重复类排列至顶端:
2、【书的月销售量与汇总】:
=SUMIFS(表1[销量(本)],表1[图书名称],[@图书名称],表1[日期],"
>
=2013-1-1"
表1[日期],"
<
2013-2-1"
)
公式——自动求和
3、【迷你图的使用】
●插入——迷你图——折线图
●
●高低值的显示
4、【数据透视表的设置】
数据透视表的存储:
选项——操作——移动数据操作表
在数据透视表中显示2012年期间各书店每季度的销量情况。
●分组——将所选内容分组
008
1、学号和姓名位于同一单元格内,如何拆分?
●将光标至于学号和姓名中间。
●选中A列——数据——数据工具——分列——固定宽度。
●套用表格格式——表包含标题
●设计——属性——更名为档案
2、【依次判断性别、出生日期和年龄】,其中身份证号的倒数第二位用于判断性别,奇数为男性,偶数为女性;
身份证号第7~14位代表出生年月日;
年龄需要按周岁计算,满1年才计1岁。
“男”or“女”=IF(MOD(MID(C2,17,1),2)=1,"
男"
女"
出生日期=MID(C2,7,4)&
"
年"
MID(C2,11,2)&
月"
MID(C2,13,2)&
日"
年龄=INT((TODAY()-E2)/365)
3、按照平时、期中、期末成绩各占30%、30%、40%的比例计算每个学生的“学期成绩”并填入相应单元格中。
●在E2单元格中填入:
=SUM(C2*30%)+(D2*30%)+(E3*40%)
4、按成绩由高到低的顺序统计每个学生的“学期成绩”排名并按“第N名”的形式填入“班级名次”列中。
●在排名2处输入="
第"
RANK(F2,$F$2:
$F$45)&
名"
●期末总评:
=IF(F2>
=102,"
优秀"
IF(F2>
=84,"
良好"
=72,"
及格"
72,"
不及格"
))))
009
1、【COUNTIFS函数的应用】
利用“成绩单”、“小分统计”和“分值表”工作表中的数据,完成”按班级汇总”和“按学校汇总”工作表中相应空白列的数值计算。
●考试学生数=COUNTIFS(成绩单!
$A$2:
$A$950,按班级汇总!
$A2,成绩单!
$B$2:
$B$950,按班级汇总!
$B2)
●最高分
=MAX((成绩单!
$A$950=按班级汇总!
$A2)*(成绩单!
$B$950=按班级汇总!
$B2)*成绩单!
$D$2:
$D$950)
●最低分:
公式=MIN(IF((成绩单!
$B2),成绩单!
$D$950))
●平均分公式=AVERAGEIF(成绩单!
$D$950,成绩单!
1、快速输入大量含小数点的数字
如果我们需要在ExcelXP工作表中输入大量的带有小数位的数字时,按照普通的输入方
法,我们可能按照数字原样大小直接输入,例如现在要在单元格中输入0.05这个数字
时,我们会把“0.05”原样输入到表格中。
不过如果需要输入若干个带有小数点的数字
时,我们再按照上面的方法输入的话,每次输入数字时都需要重复输入小数点,这样工
作量会变大,输入效率会降低。
其实,我们可以使用ExcelXP中的小数点自动定位功
能,让所有数字的小数点自动定位,从而快速提高输入速度。
在使用小数点自动定位功
能时,我们可以先在ExcelXP的编辑界面中,用鼠标依次单击“工具”/“选项”/“编
辑”标签,在弹出的对话框中选中“自动设置小数点”复选框,然后在“位数”微调编
辑框中键入需要显示在小数点右面的位数就可以了。
以后我们再输入带有小数点的数字
时,直接输入数字,而小数点将在回车键后自动进行定位。
例如,我们要在某单元格中
键入0.06的话,可以在上面的设置中,让“位数”选项为2,然后直接在指定单元格中
输入6,回车以后,该单元格的数字自动变为“0.06”,怎么样简单吧?
2、快速录入文本文件中的内容
现在您手边假如有一些以纯文本格式储存的文件,如果此时您需要将这些数据制作成
ExcelXP的工作表,那该怎么办呢?
重新输入一遍,大概只有头脑有毛病的人才会这样
做;
将菜单上的数据一个个复制/粘贴到工作表中,也需花很多时间。
没关系!
您只要
在ExcelXP中巧妙使用其中的文本文件导入功能,就可以大大减轻需要重新输入或者需
要不断复制、粘贴的巨大工作量了。
使用该功能时,您只要在ExcelXP编辑区中,依次
用鼠标单击菜单栏中的“数据/获取外部数据/导入文本文件”命令,然后在导入文本会
话窗口选择要导入的文本文件,再按下“导入”钮以后,程序会弹出一个文本导入向导
对话框,您只要按照向导的提示进行操作,就可以把以文本格式的数据转换成工作表的
格式了。
3、快速输入大量相同数据
如果你希望在不同的单元格中输入大量相同的数据信息,那么你不必逐个单元格一
个一个地输入,那样需要花费好长时间,而且还比较容易出错。
你可以通过下面的操作
方法在多个相邻或不相邻的单元格中快速填充同一个数据,具体方法为:
首先同时选中
需要填充数据的单元格。
若某些单元格不相邻,可在按住Ctrl键的同时,点击鼠标左
键,逐个选中;
其次输入要填充的某个数据。
按住Ctrl键的同时,按回车键,则刚才选
中的所有单元格同时填入该数据。
4、快速进行中英文输入法切换
一张工作表常常会既包含有数字信息,又包含有文字信息,要录入这样一种工作表
就需要我们不断地在中英文之间反复切换输入法,非常麻烦,为了方便操作,我们可以
用以下方法实现自动切换:
首先用鼠标选中需要输入中文的单元格区域,然后在输入法
菜单中选择一个合适的中文输入法;
接着打开“有效数据”对话框,选中“IME模式”
标签,在“模式”框中选择打开,单击“确定”按钮;
然后再选中输入数字的单元格区
域,在“有效数据”对话框中,单击“IME模式”选项卡,在“模式”框中选择关闭
(英文模式);
最后单击“确定”按钮,这样用鼠标分别在刚才设定的两列中选中单元
格,五笔和英文输入方式就可以相互切换了。
5、快速删除工作表中空行
删除ExcelXP工作表中的空行,一般的方法是需要将空行都找出来,然后逐行删
除,但这样做操作量非常大,很不方便。
那么如何才能减轻删除工作表中空行的工作量
呢?
您可以使用下面的操作方法来进行删除:
首先打开要删除空行的工作表,在打开的
工作表中用鼠标单击菜单栏中的“插入”菜单项,并从下拉菜单中选择“列”,从而
插入一新的列X,在X列中顺序填入整数;
然后根据其他任何一列将表中的行排序,使
所有空行都集中到表的底部。
删去所有空行中X列的数据,以X列重新排序,然后删去
X列。
按照这样的删除方法,无论工作表中包含多少空行,您就可以很快地删除了。
6、快速对不同单元格中字号进行调整
在使用ExcelXP编辑文件时,常常需要将某一列的宽度固定,但由于该列各单元格中的
字符数目不等,致使有的单元格中的内容不能完全显示在屏幕上,为了让这些单元格中
的数据都显示在屏幕上,就不得不对这些单元格重新定义较小的字号。
如果依次对这些
单元格中的字号调整的话,工作量将会变得很大。
其实,您可以采用下面的方法来减轻
字号调整的工作量:
首先新建或打开一个工作簿,并选中需要ExcelXP根据单元格的宽
度调整字号的单元格区域;
其次单击用鼠标依次单击菜单栏中的“格式”/“单元格”/
“对齐”标签,在“文本控制”下选中“缩小字体填充”复选框,并单击“确定”按钮
;
此后,当你在这些单元格中输入数据时,如果输入的数据长度超过了单元格的宽度,
ExcelXP能够自动缩小字符的大小把数据调整到与列宽一致,以使数据全部显示在单元
格中。
如果你对这些单元格的列宽进行了更改,则字符可自动增大或缩小字号,以适应
新的单元格列宽,但是对这些单元格原设置的字体字号大小则保持不变。
7、快速输入多个重复数据
在使用ExcelXP工作表的过程中,我们经常要输入大量重复的数据,如果依次输入,无
疑工作量是巨大的。
现在我们可以借助ExcelXP的“宏”功能,来记录首次输入需要重
复输入的数据的命令和过程,然后将这些命令和过程赋值到一个组合键或工具栏的按钮
上,当按下组合键时,计算机就会重复所记录的操作。
使用宏功能时,我们可以按照以下
步骤进行操作:
首先打开工作表,在工作表中选中要进行操作的单元格;
接着再用鼠标
单击菜单栏中的“工具”菜单项,并从弹出的下拉菜单中选择“宏”子菜单项,并从随
后弹出的下级菜单中选择“录制新宏”命令;
设定好宏后,我们就可以对指定的单元
格,进行各种操作,程序将自动对所进行的各方面操作记录复制。
8、快速处理多个工作表
有时我们需要在ExcelXP中打开多个工作表来进行编辑,但无论打开多少工作表,
在某一时刻我们只能对一个工作表进行编辑,编辑好了以后再依次编辑下一个工作表,
如果真是这样操作的话,我们倒没有这个必要同时打开多个工作表了,因为我们同时打
开多个工作表的目的就是要减轻处理多个工作表的工作量的,那么我们该如何实现这样
的操作呢?
您可采用以下方法:
首先按住“Shift"
键或“Ctrl"
键并配以鼠标操作,在
工作簿底部选择多个彼此相邻或不相邻的工作表标签,然后就可以对其实行多方面的批
量处理;
接着在选中的工作表标签上按右键弹出快捷菜单,进行插入和删除多个工作表
的操作;
然后在“文件”菜单中选择“页面设置……”,将选中的多个工作表设成相同
的页面模式;
再通过“编辑”菜单中的有关选项,在多个工作表范围内进行查找、替
换、定位操作;
通过“格式”菜单中的有关选项,将选中的多个工作表的行、列、单元
格设成相同的样式以及进行一次性全部隐藏操作;
接着在“工具”菜单中选择“选
项……”,在弹出的菜单中选择“视窗”和“编辑”按钮,将选中的工作表设成相同的
视窗样式和单元格编辑属性;
最后选中上述工作表集合中任何一个工作表,并在其上完
成我们所需要的表格,则其它工作表在相同的位置也同时生成了格式完全相同的表格。
高效办公Excel排序方法"
集中营"
排序是数据处理中的经常性工作,Excel排序有序数计算(类似成绩统计中的名
次)和数据重排两类。
本文以几个车间的产值和名称为例,介绍Excel2000/XP的数据
排序方法。
一、数值排序
1.RANK函数
RANK函数是Excel计算序数的主要工具,它的语法为:
RANK(number,ref,
order),其中number为参与计算的数字或含有数字的单元格,ref是对参与计算的数字
单元格区域的绝对引用,order是用来说明排序方式的数字(如果order为零或省略,则
以降序方式给出结果,反之按升序方式)。
例如图1中E2、E3、E4单元格存放一季度的总产值,计算各车间产值排名的方法是:
在F2单元格内输入公式“=RANK(E2,$E$2:
$E$4)”,敲回车即可计算出铸造车间的
产值排名是2。
再将F2中的公式复制到剪贴板,选中F3、F4单元格按Ctrl+V,就能计算
出其余两个车间的产值排名为3和1。
如果B1单元格中输入的公式为“=RANK(E2,
$E$2:
$E$4,1)”,则计算出的序数按升序方式排列,即2、1和3。
需要注意的是:
相同数值用RANK函数计算得到的序数(名次)相同,但会导致后续
数字的序数空缺。
假如上例中F2单元格存放的数值与F3相同,则按本法计算出的排名
分别是3、3和1(降序时)。
2.COUNTIF函数
COUNTIF函数可以统计某一区域中符合条件的单元格数目,它的语法为COUNTIF
(range,criteria)。
其中range为参与统计的单元格区域,criteria是以数字、表达
式或文本形式定义的条件。
其中数字可以直接写入,表达式和文本必须加引号。
仍以图1为例,F2单元格内输入的公式为“=COUNTIF($E$2:
$E$4,"
E2)
+1”。
计算各车间产值排名的方法同上,结果也完全相同,2、1和3。
此公式的计算过程是这样的:
首先根据E2单元格内的数值,在连接符&
的作用下产生
一个逻辑表达式,即“>
176.7”、“>
167.3”等。
COUNTIF函数计算出引用区域内符合
条件的单元格数量,该结果加一即可得到该数值的名次。
很显然,利用上述方法得到的
是降序排列的名次,对重复数据计算得到的结果与RANK函数相同。
3.IF函数
Excel自身带有排序功能,可使数据以降序或升序方式重新排列。
如果将它与IF函
数结合,可以计算出没有空缺的排名。
以图1中E2、E3、E4单元格的产值排序为例,具
体做法是:
选中E2单元格,根据排序需要,单击Excel工具栏中的“降序排序”或“升序
排序”按钮,即可使工作表中的所有数据按要求重新排列。
假如数据是按产值由大到小(降序)排列的,而您又想赋予每个车间从1到n(n为
自然数)的排名。
可以在G2单元格中输入1,然后在G3单元格中输入公式“=IF
(E3=E2,G3,G3+1)”,只要将公式复制到G4等单元格,就可以计算出其他车间的产
值排名。
二、文本排序
选举等场合需要按姓氏笔划为文本排序,Excel提供了比较好的解决办法。
如果您
要将图1数据表按车间名称的笔划排序,可以使用以下方法:
选中排序关键字所在列
(或行)的首个单元格(如图1中的A1),单击Excel“数据”菜单下的“排序”命令,
再单击其中的“选项”按钮。
选中“排序选项”对话框“方法”下的“笔画排序”,再
根据数据排列方向选择“按行排序”或“按列排序”,“确定”后回到“排序”对话框
(图2)。
如果您的数据带有标题行(如图1中的“单位”之类),则应选中“有标题
行”(反之不选),然后打开“主要关键字”下拉列表,选择其中的“单位”,选中排
序方式(“升序”或“降序”)后“确定”,表中的所有数据就会据此重新排列。
此法稍加变通即可用于“第一名”、“第二名”等文本排序,请读者自行摸索。
三、自定义排序
如果您要求Excel按照“金工车间”、“铸造车间”和“维修车间”的特定顺序重
排工作表数据,前面介绍的几种方法就无能为力了。
这类问题可以用定义排序规则的方
法解决:
首先单击Excel“工具”菜单下的“选项”命令,打开“选项”对话框中的“自
定义序列”选项卡(图3)。
选中左边“自定义序列”下的“新序列”,光标就会在右
边的“输入序列”框内闪动,您就可以输入“金工车间”、“铸造车间”等自定义序
列了,输入的每个序列之
间要用英文逗号分隔,或者每输入一个序列就敲回车。
如果序列已经存在于工作表
中,可以选中序列所在的单元格区域单击“导入”,这些序列就会被自动加入“输入序
列”框。
无论采用以上哪种方法,单击“添加”按钮即可将序列放入“自定义序列”中
备用(图3)。
使用排序规则排序的具体方法与笔划排序很相似,只是您要打开“排序选项”对话
框中的“自定义排序次序”下拉列表,选中前面定义的排序规则,其他选项保持不动。
回到“排序”对话框后根据需要选择“升序”或“降序”,“确定”后即可