EXCEL操作技巧.docx

EXCEL操作技巧.docx

《EXCEL操作技巧.docx》由会员分享，可在线阅读，更多相关《EXCEL操作技巧.docx（19页珍藏版）》请在冰豆网上搜索。

EXCEL操作技巧

一、用EXCEL分班

1、处理思路

　学校大多根据学习成绩进行分班，一般是按总分名次来划分的：

比如要分8个班，则第1名分到一班、第2名分到二班……第8名分到八班，接着第9名分到八班、第10名分到七班……第16名分到一班。

　　2、划分班级

　　先将学生情况及成绩调入Excel工作表中，按总分降序排列（假设总分在H列，第一名在第三行），在总分列的右一列（即I列）从I3单元格开始向下填充1、2、3……再在其右一列（即J列）用公式算出应分在哪一个班级。

　具体操作如下：

在J3单元格中输入公式

“=IF（MOD（I3,2*8）>8,8-MOD（I3,8）+1,（IF（MOD（I3,2*8）=0,1,MOD（I3,2*8））））”，再在J列按公式将每名学生自动填充上班级号，每个工作表改名为一班、二班……在每个班级工作表中复制和原表一样的表头，在原成绩表中用“自动筛选”按班级号筛出各个班级的学生，并将其复制到相应的各个班级工作表即可。

　　小提示：

以上公式中的“8”是要划分的班级个数，可以根据班级数的变化而修改，若分成n个班级，则总公式为“＝IFMODI32*n　＞nn－MODI3n　＋1IFMODI32*n　＝01MODI32*n　　　　”。

　　3、打印名单

　　分好班级后，要把各班级的学生名单打印出来，一个班最好用一张标准纸，可用分栏打印解决班级人数多的问题。

而Excel没有专门的分栏打印功能，我就利用Excel的公式制作出分栏打印效果。

　　具体操作如下：

先将某一班级名单按自己要求设置好，如页边距、表头、行高、字体等。

在打印预览中，判断出每页的数据行数x（所谓数据行，指表头除外的记录行），回到普通视图下，在表的右边空列中，从第一数据行开始填充自然数序列1、2、3……假设此列在K列，第一数据行为第3行，则在L3单元格中填入公式“=mod（int（（K3-1）/x）,y）”（其中x为每页的数据总行数，y为分栏的栏数，就分班而言，两栏就够用了），并向下填充整个表。

则该列出现了从0到y-1的数，即给每行计算出了该行所在的栏号，复制表头到新的工作表或新建的工作薄中。

在班级工作表中也用“自动筛选”功能分别筛选出第0栏、第1栏……第y-1栏，并分别复制到新工件表中。

　　再对各新工作表进行相应的设置，如页边距、表头、行高、字体等，注意不要逐个工作表进行设置，按住“Ctrl”键，用鼠标单击窗口中需要设置相同内容的各个不同工作表标签，在其中一个工作表中修改设置，另外的工作表中就能自动修改。

所有工作表都设置好后，分栏打印的各班名单就制作好了，用打印机打印出来便大功告成。

此方法都保留了原表，有利于修改，如要改变班级数、打印栏数、每页行数，只需改变n、x、y的值。

另外，此方法用的是“自动筛选”功能，也可以用“高级筛选”来完成。

二、分班

思路：

　　1、各班的男生、女生比例要基本相同。

　　2、按成绩分为8个班，采用名次“轮回法”，如图1所示。

图1

　　具体操作:

　　1、创建“新生分班”工作簿

　　打开Excel，新建一工作簿“新生分班”，将Sheet1改名为“学生数据”。

　　2、插入新生数据到“新生分班”工作簿

　　打开存储学生数据的Word文档，将其中的表格数据“复制、粘贴”到Excel中的“新生分班/学生数据”工作表中。

　　3、调整工作表结构

　　打开“学生数据”工作表，调整工作表结构如图2所示。

图2

　　排名排序汇总分班

　　1、在“年级名次”栏填充名次

查看学生名单，在“年级名次”一列中，单击鼠标左键，选择D2单元格（第1位同学名次所在单元格），“照葫芦画瓢”地输入下列公式“=RANK（C2,$C$2:

$C$401,0）”。

提示：

（1）公式中的“C”为成绩所在列的列号，C2、C401分别为第一位同学和最后一位同学的成绩所在的单元格，具体字母、数字根据实际情况而定。

公式中的“$”不可省略，最后一个“0”也不可省略。

（2）注意不要用“=IF（c2=c1,d1,row（c2）-1）”，不信？

试试就知道了。

　　输入完以上公式后回车，即可自动得出该学生的名次如图3所示。

此后，鼠标左键双击（如果你愿意，也可以用“拖拉大法”）D2单元格右下角的控点（是一个小黑方块），即可自动得出全部学生的名次了。

图3

　　2、按性别、名次排序

　　选择菜单“数据→排序”，显示图4所示对话框，按照图中的样子调整参数，单击[确定]按钮即可。

图4

　　3、填充班号

　　在“班号”一栏中，按照如此循环填充班号：

　　1-2-3-4-5-6-7-8→8-7-6-5-4-3

　　-2-1→1-2-3-4-5-6-7-8→8-7-6-5-4-3-2-1……

　　[方法一]：

手工方法

　　虽说是手工，但实际上并不慢，因为只需人工填写一个循环节即可，其余的可以复制、粘贴。

　　[方法二]：

自动填充

（1）选择菜单“工具→选项”，在弹出的对话框中单击“自定义序列”标签，在左侧小窗口中单击“新序列”，在右侧小窗口中输入“b8,b7,b6,b5,b4,b3,b2,b1,b1,b2,b3,b4,b5,b6,b7,b8”（b为班，引号内的每一个逗号都代表一个回车。

注意序列是b8—b1—b1—b8，而不是b1—b8—b8—b1，为什么？

自己试一试就知道了），如图5所示。

输入完毕，单击[确定]按钮退出。

图5

（2）在“E2”（第一名同学“班号”所在的单元格）中输入“b1”后，用鼠标左键双击“E2”单元格右下角的控点，即可自动得出全部学生的班号。

　　提示：

　　自定义序列可以修改，修改时在图5左侧的小窗口中选择自定义的序列后，单击右边的小窗口即可编辑修改。

4、按班号排序

　　选择菜单“数据→排序”，显示新的对话框，调整参数如图6所示，单击[确定]按钮即可。

图6

　　提示：

图中的“次要关键字”、“第三关键字”可根据实际需要进行调整。

　　5、按“班号”分类汇总

图7

（1）选择菜单“数据→分类汇总”，显示如图7所示的对话框，调整参数如图所示（一定要选中“每组数据分页”，否则打印出来班与班之间不分页，那就得用“剪刀大法”了），然后单击[确定]按钮即可。

（2）选择菜单“编辑→替换”，将“计数”替换为“班人数”。

　　6、设置页面，排版打印

图8

　　选择菜单“文件→页面设置”，显示对话框，调整参数如图8所示，单击[确定]按钮即可（图中参数中要注意“缩放比例”，它的大小由内容决定：

内容窄、短，则比例要大；反之，比例要小）。

图9

接着在“页面设置对话框→工作表”中按图9所示调整好参数后单击[确定]按钮即可（要注意“顶端标题行”的设置，假设有N行标题，就是“$1:

$N”）。

三、用Excel编制自动分班（按1000人计算）

1.打开Excel工作簿。

2.在首行各单元格中，从左到右依次输入“男分”、“男余”、“男次”、“男”、“女分”、“女余”、“女次”、“女”、“班次”、“姓名”、“总分”、“性别”和“班数”。

3.在“男”列的D2单元格中输入公式“=IF（L2="男",K2,0）”，向下拖动复制到D1001；在“女”列的H2单元格中输入公式“=IF（L2="女",K2,0）”，向下拖动复制到H1001。

注意：

在输入公式时，除汉字而外，所有字符一律要在英文输入状态下输入，且不要加进空格。

4.在“男余”列的B2单元格中输入公式“=IF（D2=0,"",MOD（C2,2*M$2））”，向下拖动复制到B1001；在“女余”列的F2单元格中输入公式“=IF（H2=0,"",MOD（G2,2*M$2））”，向下拖动复制到F1001。

5.在“男分”列的A2单元格中输入公式

“=IF（D2=0,0,IF（B2=0,M$2,IF（B2<=M$2,M$2+1-B2,B2-M$2）））”，向下拖动复制到A1001；在“女分”列的E2单元格中输入公式

“=IF（H2=0,0,IF（F2=0,1,IF（F2<=M$2,F2,2*M$2+1-F2）））”，向下拖动复制到E1001。

6.在“班次”列的I2单元格中输入公式“=A2+E2”，向下拖动复制到I1001。

在M2中输入“6”（默认的年级总班数，使用时可任意设定）。

7.在“女次”列的G2、G3单元格中分别输入公式“=RANK（H2,H$2:

H$1001）”和“=RANK（H3,H$2:

H$1001）+COUNTIF（H$2:

H2,H3）”,将G3向下拖动复制到G1001；在“男次”列的C2、C3单元格中分别输入公式“=RANK（D2,D$2:

D$1001）”和“=RANK（D3,D$2:

D$1001）+COUNTIF（D$2:

D2,D3）”,将C3向下拖动复制到C1001。

8.选定A1:

H1,点击〖格式〗→〖列〗→〖隐藏〗。

再点击〖工具〗→〖选项〗→〖重新计算〗→〖人工重算〗→〖确定〗。

9.选定I2:

I1001,点击〖格式〗→〖单元格〗→〖保护〗→选中“锁定”、“隐藏”（前面出现“√”）→〖确定〗;选定J2:

M1001,点击〖格式〗→〖单元格〗→〖保护〗→不选中“锁定”、“隐藏”（去掉前面的“√”）→〖确定〗。

再点击〖工具〗→〖保护〗→〖保护工作表〗→输入密码→〖确定〗；再点击〖工具〗→〖保护〗→〖保护工作簿〗→输入密码→〖确定〗。

10.点击〖文件〗→〖保存〗→输入文件名“自动分班”，将“保存类型”选为“模板”→〖保存〗。

退出。

这样，一个自动分班软件就编制成功了！

使用方法如下：

①启动Excel，点击〖文件〗→〖新建〗→〖常用〗→选择“自动分班”→〖确定〗。

②将姓名、总分、性别的有关信息输入到相应位置。

注意：

“性别”项中“男”、“女”前后不得有空格和其它任何字符。

在M2单元格中输入年级总班数（否则按6个班进行自动分班）。

③按F9键，即完成自动分班工作。

④将I1:

L1001复制到新的工作簿上，就可按班排序并编辑（如调整个别特殊学生的班次等）和打印输出。

四、在Excel中通过比较两列中的数据来查找重复项

本文介绍了两种用于比较MicrosoftExcel工作表两列中的数据和查找重复项的方法。

本文介绍了两种用于比较MicrosoftExcel工作表两列中的数据和查找重复项的方法。

方法1：

使用工作表公式若要使用工作表公式比较两列中的数据，请按照下列步骤操作：

启动Excel。

在新的工作表中，输入下面的数据（保留列B为空）：

A1...

方法1：

使用工作表公式

若要使用工作表公式比较两列中的数据，请按照下列步骤操作：

1.启动Excel。

2.在新的工作表中，输入下面的数据（保留列B为空）：

A1:

1B1:

C1:

3

A2:

2B2:

C2:

5

A3:

3B3:

C3:

8

A4:

4B4:

C4:

2

A5:

5B5:

C5:

0

3.在单元格B1中键入以下公式：

=IF（ISERROR（MATCH（A1,$C$1:

$C$5,0））,"",A1）

4.选择单元格B1:

B5。

5.在MicrosoftOfficeExcel2003和Excel的较早版本中，指向“编辑”菜单上的“填充”，然后单击“向下”。

在MicrosoftOfficeExcel2007中，在“编辑”组中单击“填充”，然后单击“向下”。

重复的数字显示在列B中，如下所示：

A1:

1B1:

C1:

3

A2:

2B2:

2C2:

5

A3:

3B3:

3C3:

8

A4:

4B4:

C4:

2

A5:

5B5:

5C5:

0

方法2：

使用VisualBasic宏

Microsoft提供的编程示例只用于说明目的，不附带任何明示或默示的保证。

这包括但不限于对适销性或特定用途适用性的默示保证。

本文假定您熟悉所演示的编程语言和用于创建和调试过程的工具。

Microsoft支持工程师可以帮助解释某个特定过程的功能。

但是，他们将不会修改这些示例以提供额外的功能，也不会构建过程以满足您的特定要求。

要使用VisualBasic宏比较两列中的数据，请按照下列步骤操作：

1.启动Excel。

2.按Alt+F11启动VisualBasic编辑器。

3.在插入菜单上，单击模块。

4.在模块表中输入下面的代码：

SubFind_Matches（）

DimCompareRangeAsVariant,xAsVariant,yAsVariant

'SetCompareRangeequaltotherangetowhichyouwill

'comparetheselection.

SetCompareRange=Range（"C1:

C5"）

'NOTE:

Ifthecomparerangeislocatedonanotherworkbook

'orworksheet,usethefollowingsyntax.

'SetCompareRange=Workbooks（"Book2"）._

'Worksheets（"Sheet2"）.Range（"C1:

C5"）

'

'Loopthrougheachcellintheselectionandcompareitto

'eachcellinCompareRange.

ForEachxInSelection

ForEachyInCompareRange

Ifx=yThenx.Offset（0,1）=x

Nexty

Nextx

EndSub

5.按Alt+F11返回Excel。

6.输入下面的数据（保留B列为空）：

A1:

1B1:

C1:

3

A2:

2B2:

C2:

5

A3:

3B3:

C3:

8

A4:

4B4:

C4:

2

A5:

5B5:

C5:

0

7.选择区域A1:

A5。

8.在Excel2003及较早版本的Excel中，指向“工具”菜单上的“宏”，然后单击“宏”。

在Excel2007中，单击“开发工具”选项卡，然后单击“代码”组中的“宏”。

9.单击Find_Matches，然后单击执行。

重复的数字显示在列B中。

匹配的数字将放在第一列的旁边，如下所示：

A1:

1B1:

C1:

3

A2:

2B2:

2C2:

5

A3:

3B3:

3C3:

8

A4:

4B4:

C4:

2

A5:

5B5:

5C5:

0

五、Excel中快速输入相同特征的数据

在用Excel进行输入数据的过程中，我们经常遇到一些相同特征数据的输入，比如学生的学籍号、准考证号、单位的职称证书号等，都是前面几位相同，只是后面的数字不一样。

如果一个个依次输入，既麻烦又枯燥无味，还容易出错。

是不是有简单的方法，只输后面几位，前面相同的几位让计算机自动填充呢？

下面笔者就介绍两种方便可行的方法。

笔者以我校的学籍号为例，学籍号共10位数字（例如前面都是252303）。

方法一

　　假如要输入的数据放在A列，从A2单元格开始输入学籍号后面几位数字，依次输入A3、A4等，所有的数据输入完毕后，在B2单元格中输入公式“=252303&&A2”然后回车，这样B2单元格的数据在A2的基础上就自动加上了252303。

鼠标放到B2位置，双击单元格的填充柄（或者向下拉填充柄），瞬间B列全部加上了252303，至此所有的数据都改好了。

　　方法二

　　1.选定要输入共同特征数据的单元格区域，单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”命令，打开“单元格格式”对话框（也可依次选择“格式→单元格”菜单命令打开）。

图1

图2

2.选择“数字”选项卡，选中“分类”下面的“自定义”选项，然后在“类型”下面的文本框中输入2523030000（注意：

后面有几位不同的数据就补几个0），单击〔确定〕按钮即可。

　　3.在单元格中只需输入后几位数字，如“2523034589”只要输入“4589”，系统就会自动在数据前面添加“252303”。

另外也可以先输入数字，再选中单元格区域设定数据格式，可以得到相同的效果。

六、如何让excel表格中的两列数据相同的排列在一行

例如:

姓名分数姓名分数

张三10王五20

李四11周六18

郑七20张三19

王五12

周六13李四17

如何变成分数和姓名还是要对应在一起的

姓名分数姓名分数

张三10张三19

李四11李四17

郑七20

王五12王五20

周六13周六18

具体操作：

E2输入公式=IF（COUNTIF（$C:

$C,$A2）=0,"",INDEX（$C:

$D,MATCH（$A2,$C:

$C,）,COLUMN（A2））），右拉至F2,同时下拉至A列数据末尾，最后复制E、F列，选择性粘贴-数值，即可删除C、D列。

七、excel中查找两列中的相同数据

有AB两列数据,量比较大,而且不一定相同的数据在EXCEL中是在同一排,怎样才能在两列中找出相同的数据？

具体操作：

有A,B两列数据,假设A列数据从第2行到21000行,B列数据比A列的多,从第2行到22000行,在空白列如C列的C2输入:

=IF（ISERROR（VLOOKUP（B2,A$2:

A$21000,1,0））,"不重复","重复"）

将上述公式复制或填充到A22000,

再通过自动筛选功能筛选出"重复"的数据.（编辑栏>数据>筛选>自动筛选>在下拉框中选择"重复">将筛选出的"重复"数据复制到其他空白表中.）

八、巧用Excel函数查找重复项

在Excel中，我们经常面对数据重复的问题，如何快速的标识重复项成了令我们头疼的问题，有人说了，Excel里有删除重复项的功能，是的，但是删除重复项不会告诉你到底是哪几条数据重复了，而且如果你并不是想要删除这个重复项呢？

我们经常会遇到同名同姓的人，这在学校和一些大企业中很常见，我们总不能只保留一个人的名字吧。

其实，利用Excel函数就可以轻松帮助我们解决此问题。

如下图：

如何快速找到重复项呢？

利用函数公式吧。

在B2单元格中输入=IF（COUNTIF（A:

A,A2）>1,"重复",""），再向下拖拽即可。

公式什么意思呢？

在A列中寻找和A2单元格名字相同的姓名，如果有相同姓名，countif函数就会进行计数，当没有出现重复姓名时，if的判断条件不成立，此时不输入任何字符。

当出现重复姓名时，if判断成立，输出“重复”两字。

当然，当我们向下拖拽（这个操作你应该会吧？

选中单元格，将鼠标移动到单元格右下角的边缘，待到鼠标图标变成了黑色十字，则点住不放向下拉动）时，函数中的A2会自动变成A3、A4、A5……

九、用Excel函数核对录入成绩

很多老师都有一个头疼的问题，当遇到大型考试时，成绩录入的核对是一项烦琐的工作。

在今年的中招考试中我就遇到了这样的事情。

我把自己的方法与大家共享，希望对你有所帮助。

领导为了尽量减少录入错误，要求每一科必须由不同的人录入三次，如果录入的三次成绩有一次不相同就需要重新找出试卷，核查成绩。

一万多个考生，六个科目，怎么核对呢？

正当我愁眉不展之时，Excel中的if函数在脑海中浮现了出来，如果A=B,B=C,A=C不就三次成绩都一样了吗？

我在A列B列C列随意输入了10个数，在D列输入函数“=IF（A8=B8,IF（A8=C8,IF（B8=C8,）））”，结果就出现了两个值“0和FLASE”。

三个数都相同的出现的值是“0”，三个数中有一个不相同的出现的值就是“FLASE”。

对D列数据进行“自动筛选”不就可以把不一样的数值选出来了吗？

我在成绩录入完后，用这种方法在不到两个小时的时间内就对一万多名考生、六个科目的试卷录入不合格的考生名单全部打印出来了，而且考号还是按原来顺序排列，查找起来也很方便。

用了不到四个小时的时间就全部核对完毕，又快又准确！

不信你试一试，简单实用。

十、让Word和Excel表格中的数据同步更新技巧

在Word文档编辑时，需要用到Excel工作表中的数据资料，但是当word和Excel中某一个数据发生改变后，我们就不得不寻找对应的部分来修改，如果涉及的数据量较大，那工作的强度也会大大提升。

其实，Word和Excel提供了能让两者数据同步的方法，并且简单易行。

　　Step1：

复制段落

　　打开Word文档和要引用该Word数据的Excel工作表，然后在word中回个车，这时显示两个段落标记。

选取这两个段落标记，然后复制。

　　Step2：

粘贴链接

　　切换到Excel编辑窗口，鼠标右键单击数据栏，选择选择性粘贴，在弹出的窗口中员粘贴链接后，再选择MicrosoftOfficeword文档对象，然后点确定退出。

　　Step3：

同步输入

　　回到Word文档，输入内容，包括图片、表格均可。

几秒种后，Excel中就会显示更改后的Word数据，这样就达到同步的目的了。

如果你想让同步更快，那么在输入内容后点击Word工具栏上的保存按钮就要中以了。

　　需要的是，在Word中输入内容时，不能在最后一个段落标记前输入，否则是不会正常同步变理我的，因此必须是最后一个段浇标记之上输入或回车再输入才有效。

十一、请问Excel中如何查找含有汉字的单元格

一张表格中绝大多数均为字母和数字，只有个别单元格中夹杂有汉字，如何把这些单元格找出来？

具体操作：

先点中一个单元格（设为D5），格式→条件格式→公式：

=LENB（D5）>LEN（D5），自己设置格式，确定后退出，再把该单元格的格式用格式刷刷所有单元格。

有汉字的出现所设格式。

十二、何在已有的单元格中批量加入一段固定字符？

例如：

在单位的人事资料，在excel中输入后，由于上级要求在原来的职称证书的号码全部再加两位，即要在每个人的证书号码前再添上两位数13，如果一个一个改的话实在太麻烦了，那么我们可以用下面的办法，省时又省力：

1）假设证书号在A列，在A列后点击鼠标右键，插入一列，为B列；

2）在B2单元格写入：

="13"&A2后回车；

3）看到结果为13xxxxxxxxxxxxx了吗？

鼠标放到B2位置，单元格的下方不是有一个小方点吗，按着鼠标左键往下拖动直到结束。

当你放开鼠标左键时就全部都改好了。

若是在原证书号后面加13则在B2单元格中写入：

=A2&“13”后回车。

十三、要计算非空单元格的个数，请使用COUNTA函数。

示例：

1

2

3

4

5

6

A

数据

销售额

19

TRUE

公式

说明（结果）

=COUNTA（A2:

A6）

计算上列数据中非空白单元格的个数（3）

=COUNTA（A2:

A3,A6）

计算上列数据中前两个单元格与最后一个单元格中非空白单元格的个数

（1）

十四、RANK函数

返回一个数字在数字列表中的排位。

数字的排位是其大小与列表中其他值的比值（如果列表已排过序，则数字的排位就是它当前的位置）。

语法

RAN