EXCEL的一些技巧应用4.docx

EXCEL的一些技巧应用4.docx

《EXCEL的一些技巧应用4.docx》由会员分享，可在线阅读，更多相关《EXCEL的一些技巧应用4.docx（14页珍藏版）》请在冰豆网上搜索。

EXCEL的一些技巧应用4

EXCEL的一些技巧应用（4）

1、去掉Excel表格单元格的绿色“三角”

　　在用Excel编制一些人事报表的时候，经常需要在某列输入身份证号、手机号等数据，如果将单元格格式保持默认的“常规”的话，将会以科学计数法显示，而如果将单元格格式设置为“文本”的话就会在左上角出现绿色的“三角”符号，这是一个提示符号，很多朋友觉得碍眼，想要去掉。

方法很简单。

　　依次点击“文件→选项”，在左边选择“公式”选项卡，然后在右边找到“错误检查”，点击“重新设置忽略错误”按钮，然后取消“文本格式的数字或者前面有撇号的数字”的勾选，确定后退出即可。

2、单元格文本轻松换行

　　使用Excel编辑工作表时，如果希望文本在单元格内以多行显示，可以有几种方法。

　　一、设置单元格格式

　　选择所需的单元格区域，在“开始”选项卡上的“对齐”组中，单击“自动换行”。

此时，单元格中的数据会自动换行以适应列宽。

当更改列宽时，数据换行会自动调整。

如果所有换行文本均不可见，则可能是该行被设置为特定高度，请调整行高。

　　二、快捷键换行

　　另外，你也可以在单元格中的特定位置开始新的文本行，方法是：

双击该单元格，单击该单元格中要断行的位置，然后按“Alt+Enter”组合键。

3、表格中设置不可修改单元格

　　有时候我们希望对Excel表格的数据编辑做一些限制：

比如只允许部分单元格的内容可以修改，其余的单元格数据则不允许修改（包括字号、字体等），我们可以这样操作。

　　现在我们有一个Excel表格，想要设置B2、B3、B4单元格为允许修改的部分，其余部分不可修改。

　　1、选择允许修改的区域（B2、B3、B4单元格），点击右键，从右键菜单中选择“设置单元格格式”。

　　在“保护”选项卡中，去掉“锁定”前面的钩。

　　2、再击菜单栏上的“工具”→“保护”→“保护工作表”。

　　勾选“选定未锁定的单元格”，并两次输入密码。

　　现在我们来试验一下。

　　修改B2、B3、B4单元格，没有问题。

　　修改C2单元格，Excel弹出下图提示，提醒正在修改被保护的单元格。

　　如果想要解除C2单元格的修改限制，我们可以从菜单栏中选择“工具”-“保护-“撤销工作表保护”。

　　在弹出的对话框中输入刚才设定的密码。

　　现在，C2单元格数据修改成功。

4、Excel函数实现列数较少表格分栏打印

点击查看更多软件资讯>>

　　本文介绍如何利用Excel函数实现分栏打印，这个技巧适合打印列数比较少的Excel表格，避免出现大量空白。

　　平时偶尔需要打印一些列数特少的表格，例：

单科成绩表、调资清单、施工员年检名单、产品代码表等等。

以施工员年检名单来说就只有序号、姓名和证号3列。

直接打印时由于3列表格远小于纸张宽度会出现大量空白，不仅难看也很浪费纸张。

因此很有必要把表格分栏排满页面再打印，不过Excel并没有分栏功能，要如何实现分栏打印呢？

其实在Excel2007中用函数就可以轻松实现表格的分栏打印。

　1.格式设置

　　用Excel2007打开需要分栏的“年检名单”工作表（图1）。

按住Ctrl键拖动“年检名单”工作表标签复制出一张“年检名单

（2）”工作表，并把它重命名为“分栏”。

在“分栏”工作表中按Ctrl+A键全选并按Delete键删除数据，以得到一张边框、行高、列宽、字体均与原表相同的空表。

　　按所需分栏间距调整第4列宽度，然后选中A1:

D1复制，再选中第1行右击选择“选择性粘贴”，在“选择性粘贴”窗口中选中“列宽”，确定把表格A1:

D1的列宽设置复制到所有列。

　　2.输入公式

　　在A1输入公式=IF（A2=“”，“”，年检名单!

$A1），在A2非空时显示“年检名单”工作表的标题A1否则为空，在B2、C3也输入同样公式，不过要把公式中的$A$1分别改成$B$1、$C$1以引用相应标题。

　　在A2输入公式=IF（INDIRECT（“年检名单!

A”&ROW（）+INT（COLUMN（）/4）*COUNT（$D:

$D））=“”，“”，INDIRECT（“年检名单!

A”&ROW（）+INT（COLUMN（）/4）*COUNT（$D:

$D））），在B2、C2也输入同样的公式并把公式中的A全改成B、C以引用相应列的内容。

　　在D2输入1作为计算行数的依据，并设置字体颜色为白色以让1消失。

选中A2:

D2向下拖动填充柄复制到C20单元格，即可显示首栏的19行数据。

最后选中A1:

D20复制，再选中1:

20行进行粘贴即可生成所有分栏表格（图2）。

　　公式中ROW（）+INT（COLUMN（）/4）*COUNT（$D:

$D）计算出当前单元格数据在原表中的行号。

由INDIRECT函数按行、列号引用单元格，再用IF判断当引用的单元格为空时显示为空。

COUNT（$D:

$D）自动统计每页行数，/4为原表格的列数3+1列分隔用的空列。

　　3.隐藏空表

　　上面的引用公式让超出原表总记录数的分栏表格不显示数据，但表格线还是在的，还得设置条件格式让空表格自动隐藏。

选中1:

20行，单击“开始”选项卡的“条件格式”选择“突出显示单元格规则/其他规则”。

　　在“新建格式规则”窗口的“单元格值”下拉列表中选择“空值”，单击“格式”按钮，在“设置单元格格式”窗口的“边框”选项卡下单击“无”设置为无边框，确定后没有数据的区域就不会显示边框了（图3）。

　　4.最后调整

　　切换到“视图”选项卡单击“页面布局”即可看到每页的分栏情况。

直接拖动水平标尺中的左、右边距，让第1页中打印的最后一列正好是用于分栏的空列（图4），这样才能确保各页的分栏数一致。

　　若想调整分栏间距，直接调整第4列宽度，再复制A1:

D1按宽度“选择性粘贴”到第1行即可，当然也得重新再调整左、右边距以确保分栏数一致。

若想在每页表格上添加一个总标题的话，现在你可以单击页面上侧的页眉区，直接在页眉中添加标题。

　　现在看一下每页打印的行数是否适当，从图中可以看出其实还可以再增加两行。

我们直接在2:

20行间随便选中两行（17:

18行），右击选择复制，再右击选择“插入复制的单元格”，即可增加2行表格。

反过来，如果行数过多超出页面，则只要直接在2:

20行中任选几行右击选择“删除”即可减少行数。

　　现在可以开始打印了。

虽然空分栏表不会显示，但由于有公式在还是会打印出空白页。

因此打印时得选中全部有数据的列，单击“Office”按钮选择“打印”，在打印窗口选中“选定区域”再打印才不会打印空白页。

或者直接指定打印页码范围也行。

以后在年检名单工作表中填写修改数据后，打开“分栏”工作表即可直接进行分栏打印，无需再做修改。

　　将文档保存成模板还可用于对其他表格进行分栏打印。

你只要把表格连标题带数据一起复制粘贴到年检名单工作表的A1单元格，即可分栏打印。

也不一定要3列，少于3列的表格略做调整同样可以分栏。

以2列表格来说，只要在“分栏”工作表中右击第3列列标选择“隐藏”，再复制A1:

D1，按宽度“选择性粘贴”到第1行，然后适当调整左、右边距即可实现2列表格的分栏。

　　当然超过3列的表格就得修改公式中的列数了，因此想做成模板最好一开始就按最多列的表格设置，再按需隐藏多余列会比较方便。

此外，若粘贴的表格有两行标题，只要在分栏工作表中选中第1行复制，再“插入复制的单元格”增加标题行。

然后在A1:

C2标题行中按原表合并、设置格式后选中A1:

D2复制粘贴到1:

2行即可。

5、Excel函数按身高数据快速安排学生座位

　　本文主要介绍如何用Excel按数据借助ROW和IF等函数进行特殊排序，以快速完成各种座次表、企业职工工作安排等数据排序工作。

　　开学差不多一个月了，此时很多学校都会根据身高等因素给学生重新安排座位。

通常要求把高个排在后面、矮个排在前面。

以往的做法是让学生按高低排队再顺次排座位，结果排队时身高差不多的学生经常争执。

今年不妨换个做法，先按学生身高随机编出座位图，再让大家按图就座，这样就没什么可争了。

不过手工排座位工作量也挺大，还是让Excel与函数帮忙搞定吧。

　　1.制作学生记录表

　　打开Excel2007，把sheet1工作表重命名为“学生记录”，按需设置好表格（图1）。

在C:

E列输入学号、姓名、身高，或者从已有的表格中复制过来。

在H、I列输入身高与系数对照表，在此H2固定为1，下面的157、168则可自由修改。

也可多增加几条身高和系数，但身高要升序排列、系数降序。

个别严重近视的学生可以在F列输入视力系数1或2让他排前一点。

　　在A2单元格输入公式=ROW（）-1自动生成序号，在B2输入公式=IF（C2，VLOOKUP（E2，H:

I，2）+F2+RAND（），）。

公式中用VLOOKUP提取身高系数+视力系数+RAND（）生成一个有身高视力差异的随机数。

选中A2:

B2拖动其右下角的黑色方块（填充柄）向下填充到B97，通常一班不会超过96人吧？

现在选中B2，单击“开始”选项卡的“排序”选择“降序”，就会按157以下排前面、157-168中间、168以上排后面的前提随机排序，视力系数每增加1则可使其在这3档中排前1档。

　　2.编制座位图

　　以把学生分成6组（列）为例，我们得先建一个“座位表”工作表，在A3、A4分别输入1、7，并对B3、B4设置粗边框。

在B3输入公式=VLOOKUP（A3，学生记录!

$A:

$F，4，FALSE），双击填充柄把公式复制到B4。

选中A3:

C4鼠标指向其填充柄，按住右键拖动到Q4，松开右键在弹出菜单中选择“填充序列”，即可填充出前两排的序号和学生名。

再选中A3:

Q4向下拖动填充柄到Q18，填充出96个座位和序号，学生自动按序号出现在座位图中。

最后适当调整好行高列宽，画一个矩形代表讲台桌即可（图2）。

　　注：

分组数不同，只需开始时改一下A4的数字，例：

分8组就改成9，其他操作都一样。

若用的是双人桌，只要在全部设置好后直接删除两组间的空列使两组合并在一起即可。

　　3.修饰座位图

　　座位图中没学生的单元格会显示错误值#N/A和边框，得让它自动消失。

选中A:

Q列，单击“开始”选项卡的“条件格式”选择“新建规则”，在“新建格式规则”窗口中选择规格类型为“只为包含以下内容的单元格设置格式”，并在“单元格值”下拉列表中选择“错误”（图3）。

再单击“格式”按钮，在弹出窗口中设置字体颜色为白色，在“边框”选项卡中设置边框为无。

一路确定完成设置后，没有学生的边框和错误值都会自动消失。

　　座位左边的序号不需要打印出来，得先隐藏起来。

选中A列，单击“数据”选项卡的“组合”图标进行组合。

同样分别选中D、G、J、M、P列进行组合。

组合后在左上角会显示1、2的按钮，点击1即可隐藏所有序号列（图4），点击2则恢复显示序号。

　　4.自动排座位

　　通常一学期需要多次重排座位，若学生没变，你只要在“学生记录”工作表选中B2单击“开始”选项卡的“排序”选择“降序”，即可随机生成一张新座位图。

即使学生变了或需要为其他班级排座位，也只要在“学生记录”工作表中输入新班级学生的学号、姓名、身高，对个别高度近视的再输入一下近视系数，再选中B2降序排序一下，即可在“座位表”工作表中看到随机排好的座位图。

　　若需要对个别学生座位进行调整，可通过修改座位图的序号实现。

本例中身高174的李丽丽因视力系数被分配到前排正中，这会影响后面学生的视线，得把她调整到左边。

你只要在“座位表”工作表中单击“2”按钮显示序号列，把序号7改成10、10改成7，即可让她与序号7的蔡小森对调座位。

修改后记得再隐藏序号列。

　　现在可以把座位图打印出来贴到讲台上，让学生按图入座了。

虽然操作有点啰嗦，但一旦设置完成，以后就只要重复第4步即可排好座位图，应用起来还是挺简单的。