EXCEL的一些技巧应用(4)

EXCEL的一些技巧应用（4）

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

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

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

2、单元格文本轻松换行

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

一、设置单元格格式

选择所需的单元格区域，在“开始”选项卡上的“对齐”组中，单击“自动换行”。此时，单元格中的数据会自动换行以适应列宽。当更改列宽时，数据换行会自动调整。如果所有换行文本均不可见，则可能是该行被设置为特定高度，请调整行高。

二、快捷键换行

另外，你也可以在单元格中的特定位置开始新的文本行，方法是：双击该单元格，单击该单元格中要断行的位置，然后按“Alt+Enter”组合键。

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

有时候我们希望对Excel表格的数据编辑做一些限制：比如只允许部分单元格的内容可以修改，其余的单元格数据则不允许修改(包括字号、字体等)，我们可以这样操作。

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

1、选择允许修改的区域（B

2、B

3、B4单元格），点击右键，从右键菜单中选择“设置单元格格式”。

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

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

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

现在我们来试验一下。

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

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

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

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

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

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

点击查看更多软件资讯>>本文介绍如何利用Excel函数实现分栏打印，这个技巧适合打印列数比较少的Excel表格，避免出现大量空白。

平时偶尔需要打印一些列数特少的表格，例：单科成绩表、调资清单、施工员年检名单、产品代码表等等。以施工员年检名单来说就只有序号、姓名和证号3列。直接打印时由于3列表格远小于纸张宽度会出现大量空白，不仅难看也很浪费纸张。因此很有必要把表格分栏排满页面再打印，不过Excel并没有分栏功能，要如何实现分栏打印呢？其实在Excel 2007中用函数就可以轻松实现表格的分栏打印。

1.格式设置

用Excel 2007打开需要分栏的“年检名单”工作表(图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步即可排好座位图，应用起来还是挺简单的。