巧用快捷键轻松设置excel单元格格式

巧用快捷键轻松设置 Excel 单元格格式 Ctrl + Shift + ：应用“ 常规” 数字格式 “常规”格式不包含特定的数字格式，相当于在“ 单元格格式”对话框中的“数字”选项卡中选择“ 常规”。 比如原来的数字格式为“货币” 或“百分比”形式，使用该快捷键可以将这些格式清除掉，恢复到常规的格式。 我们可以对比一下，使用普通的方法需要在单元格上单击右键，从弹出菜单中选择命令“设置单元格 格式”，然后对话框中选择“ 常规”。与使用快捷键相比，肯定是麻烦了许多。 Ctrl + Shift + $：应用带两个小数位的 “货币”数字格式，(负数在括号中) Ctrl + Shift + %：应用不带小数位的“百分比” 格式 Ctrl + Shift + ：应用带两位小数位的“科学记数” 数字格式 Ctrl + Shift + !：应用带两位小数位、使用千位分隔符且负数用负号 (-) 表示的“数字” 格式 Ctrl + Shift + ;”（如图 1），再切换到“保护” 选项卡下，单击选中“隐藏” 复选项，确定后单元格内容就被隐藏了。 再单击菜单“保护/保护工作表 ”，设置密码保护工作表，即可彻底隐藏单元格内容。要取消隐藏得先 单击菜单“保护/撤消保护工作表”。然后再从“设置单元格格式” 中选择相应数值格式，或者选中单元格区域 单击菜单“编辑/清除/格式”即可恢复显示。 值得大家注意的是，保护工作表后单元格是无法修改的。若希望保护后仍可修改单元格内容，可先选 中需要输入的单元格，右击选择“设置单元格格式” ，从“保护” 选项卡中单击取消“ 锁定”复选项前的“”，再 进行保护工作表设置。这样被隐藏的单元格照样可以输入或修改，但是输入后单元格内容会被隐藏。 隐藏行列 有一些特殊表格像“工资单”、“人员记录” 等可能就经常需要在打印前隐藏工作表中“ 等级”、“出生年月”、 “扣费标准” 等行列，在编辑时又要显示出，这样重复切换是比较麻烦的。对此有一个简单的方法可快速隐 藏、显示指定行列。以隐藏“C:D”、“G”、“J”列为例： 选中“C:D”列，单击菜单“ 数据/组及分级显示/组合” ，此时工作表上会显示分级的 1、2 按钮（如图 2）。 重复这一步，分别选中 G 列、J 列进行组合。现在只要单击 1 按钮即可隐藏“C:D”、“G”、“J” 列，单击 2 按 钮则恢复显示。对于行的设置也是一样，只是行的 1、2 按钮是左侧。 隐藏的行或列在通过拖动选择或按 Shift 键选中时会同时被选中，复制粘贴后会发现隐藏内容也同时 被复制过来了。对此可在拖动或按 Shift 键选中区域后，再按下“Alt+;”键，这样就会只选中可见单元格， 而不包括那些被隐藏的行或列。 提示：一般对象或图形默认会随所在的行列一起被隐藏，若不想同时隐藏图形，可双击图形打开“设 置图片格式”窗口，在“属性” 选项卡中选中“ 大小、位置固定”选项。 隐藏工作表 单击“工具/宏/Visual Basic 编辑器”，在弹出窗口左侧选中要隐藏的工作表名，如：“Sheet4” ，在属性 窗口中把“Visible”项的值设置为“2-xlsheetveryHidden” （如图 3），若没有属性窗口可按 F4 显示。然后右 击“VBAProject(XXXX.xls)”选择“VBAProject 属性”，在“ 保护”选项卡中选中“ 查看时锁定工程”，并输入密 码，保存文档退出。以后想展开 VBAProject(XXXX.xls)时会弹出一个要求输入密码的对话框，没密码就 别想查看或修改了。用这方法隐藏的工作表，不能通过单击“格式/工作表/取消隐藏” 取消隐藏，也不会影 响其它工作表操作。 Excel 函数应用实例：按性别统计职工数 (1)函数分解 COUNTIF 函数计算区域中满足给定条件的单元格的个数。 语法：COUNTIF(range,criteria) Range 为需要计算其中满足条件的单元格数目的单元格区域;Criteria 为确定哪些单元格将被计算在内 的条件，其形式可以为数字、表达式或文本。 (2)实例分析 假设上面使用的人事管理工作表中有 599 条记录，统计职工中男性和女性人数的方法是:选中单元格 D601(或其他用不上的空白单元格)，统计男性职工人数可以在其中输入公式“=“男“接着选中单元格 D602，在其中输入公式“=“女“ 选中上面一行及下面一行或多行单元格区域，按下“Ctrl+D”组合键，即可将上面一行数据复制到下 面一行或多行对应的单元格区域中; 选中右侧单元格，按下“Ctrl+R”组合键，即可将左侧单元格中的内容复制到右侧的单元格中来; 选中左侧一列及右侧一列或多列单元格区域，按下“Ctrl+R”组合键，即可将左侧一列数据复制到右 侧一列或多列对应的单元格区域中。 在 Excel 中，系统会默认把输入的“6-2”变成日期“6 月 2 日” ，有时觉得非常不方便。以下方法可以解决这 个问题： 方法一： 选定单元格(可以是多个单元格 )后选择菜单“格式单元格” （或直接在单元格上单击鼠标右键后选择 “设置单元格格式”）在打开的“ 单元格格式” 窗口中单击“数字”选项卡，在“分类”中选定“ 文本”并“确定” 即可。 方法二： 在输入内容的前面加上一个英文半角的单引号，就是告诉 Excel 将单引号后的内容作为文本处理（单 引号本身并不会被打印出来）。 以上方法的扩展应用： 除了输入“6-2”、“2005-6-2” 这样可转换为对应日期的内容会被 Excel 自动理解为日期外，常见的电话 号码尤其是 11 位的手机号码以及不含字母“x”的身份证号码输入时也会被 Excel 自动理解为数字。由于位 数多，会被自动变为科学记数法的形式（如 1.26584E+17 ），要想保持正确的格式，上面的两种方法都 是解决之道。 另外，在单元格中文本默认是左对齐的，而包括日期在内的数字都默认是右对齐的，从这一点可以直 观地看出差别。 注意:单元格的内容在强制变回文本时其值可能会发生变化，如日期变成相对于 1900 年 1 月 1 日的天 数、身份证号码的后几位被科学记数法舍为 0 等，需要留意加以订正，以免产生差错。 不让 Excel 图表随源数据改变而改变 在默认情况下，Excel 的图表在一个区域中存放数据，如果改变该区域中的数据，图表就会自动更新。 有时我们希望将图表与数据区域的链接断开，这样就可以得到一个静态的图表，它不会随数据的改变而改 变。 例如，假设在一个工作表中创建两个图表，一个是静态的，不会随数据改变而改变，另一个是动态的， 当数据改变时，图表自动更新，这样可以将数据改变前后的图表进行比较，这时静态的图表就成为一个很 好的参考。 创建静态图表的方法之一是将图表复制并粘贴为图片。选中图表后，按住 Shift 键的同时选择菜单命 令“编辑复制图片”(注意：一定要按住 Shift 键才能够从“ 编辑”菜单中看到“复制图片” 命令)，这时会出现如 图 1 所示的 “复制图片”对话框。保持默认的设置不变，然后单击“ 确定”按钮，在工作表的其它任意位置单 击，然后选择菜单命令“编辑粘贴 ”，这样就得到了一个图表的图片，如图 2 中右侧所示。 图 1 图 2 另一种创建一个静态图表的方法是改变数据系列的公式参数，将数据引用转换为具体的值。单击图表 中的一个数据系列，然后单击公式栏，这时在公式栏中显示出如图 3 所示的公式。 图 3 按一下 F9 键，可以看到如图 4 所示的结果。用同样的方法转换另一数据系列的公式。如此一来，图 表的数据系列就不再会随数据区域的数值改变而改变了。(文 李东博) 图 4 Excel 有一个小小的缺陷，那就是无法自动识别重复的记录。为了清除这些重复记录，有的朋友是一个一 个手工删，既费时又费力。 虽说 Excel 中并没有提供给我们清除重复记录这样的功能，但我们还可以利用它的高级筛选功能来达 到相同的目的。今天，笔者就来向大家介绍一个如何利用 Excel 的“高级筛选”巧妙删除重复记录的小技巧。 (注:本文所述技巧已于微软 Excel 2003 环境下测试通过) 具体操作步骤如下: 1. 打开一份带有重复记录的 Excel 文档。如图 1 所示 (注:本图已用 Photoshop 处理，其中彩色部分 为重复的记录) 图 1 2. 选中图表中的所有记录(注意，此时应将每列的标题行也选择上，否则筛选完的数据表中将不再包 含有该标题行)，执行“数据” 菜单“ 筛选高级筛选”命令 3. 在弹出的“高级筛选”对话框中选择“将筛选结果复制到其他位置” 一项，并点击“复制到”选择框后面 的范围按钮来选择一块区域以存放筛选后的数据(注意:此处千万不要与原数据所处的单元格相重合，否则 数据表将会混乱)，最后，勾选“选择不重复的记录”复选框后，点击“确定” 按钮即可。如图 2 所示 图 2 4. 此时，Excel 便会将所有的重复记录自动删除掉，确认无误后，您就可以把新生成的数据清单拷贝 到新的工作表中继续使用了。如图 3 所示 图 3 刚考完试，领导心血来潮，要求统计一下男女比例、平均分、及格人数等各项信息。学校一直使用专用软 件登记考生资料，但该软件竟没有这些统计功能，暴汗。幸好发现这软件能把基本数据导出为文本文件， 我想，拿到 Word 或 Exce 里或许有办法解决。下面是导出的文本，如图 1。 要统计各项数据，首先要把连在一起的各列数据分开，首先想到的是用 Word 的文本转换为表格功能， 但尝试后发现，在 Word 中要将文本转为表格，必须具有文字分隔符，但从图 1 可见，几列数据是连在一 起的，没有 Word 能够识别的符号分隔，所以此路不通。 只能再试试 Excel 了，终于在“ 数据”菜单中找到了 Excel 的“ 分列 ”命令，以下就是解决问题的全过程。 1.复制文本，在工作表中的 A1 单元格中执行“粘贴”，数据已自动分行，如图 2。 2.选中 A 列，在“ 数据”菜单中，单击 “分列”命令，如图 3。 3.在打开的“文本分列向导”对话框中，根据基本数据的特点，选择“固定宽度” 。如图 4。 4.单击“ 下一步”，设置字段宽度。单击建立分列线，按住左键可拖动分列线的位置，双击可取消分列 线。如图 5。 4.分列完毕后，单击“下一步”，设置每列的数据类型。如:第一列要设置为“文本” ，否则分列后，考号 最前面的“0”将被舍去;而第三列设置为“常规” 即可，Excel 将按单元格中的内容自动判断并设置数据类型。 如图 6。 5.单击“ 完成”。眨眼工夫分列完毕，然后使用 Excel 的筛选、排序等功能就可以统计所需要的数据了。 如图 7。 巧借 Excel 快速把 Word 表格行列互换 有时我们需要将 Word 表格的行与列进行交换(也称为表格转置)，但 Word 本身并没有提供现成的功 能可供使用，传统的办法要不就是手工转换，要不就是通过单元格合并再拆分的方法来实现(具体方法可 参考天极网文章 /20030116/1648673.shtml )。但这两种方法的操作步骤都过于 烦琐，而且效率也都很低。 其实，我们经常用到的另一款办公软件 Excel 中就包含了表格转置的功能，使得我们可以直接借助 Excel 来转置 Word 表格。 本文以如图 1 所示的一个 Word 表格为例，向大家介绍具体的转置方法。 图 1 1. 首先要在 Word 中右击表格左上角的十字标全选整个表格，然后执行右键菜单中的“复制”命令 2. 接下来打开 Excel，在任意单元格处点击鼠标右键，选择“选择性粘贴文本”命令，将 Word 表格 粘贴到 Excel 中。如图 2 所示 图 2 3. 右击并复制图 2 中这些带有数据的单元格(记住，此步不可缺少，至关重要!) 4. 然后切换到另一空白工作表中，右击并执行“ 选择性粘贴”命令。最后在弹出的如图 3 所示对话框中 勾选“转置” 复选框后点击确定即可 图 3 5. 此时，您便会发现 Excel 表格中的行列已经按照我们的要求互换了，而且各个单元格的数据也分 毫不差，这时再将转置好的表格拷贝回 Word 就行了。如图 4 所示就是已经转换好的 Word 表格，怎么样?效 果不错吧。 图 4 巧妙处理有多个合并单元格的复杂 Excel 表格 Excel 是功能非常强大的表格处理软件，特别是其表格计算能力，方便、快捷和自动化运算，使越来 越多的人选择采用 EXCEL 来处理日常办公表格。但是，当碰到一些“ 不规则”的表格时，许多初学者感到 十分棘手。表格“不规则”，主要表现为合并单元格太多，单元格或上下不对齐，或左右不对齐，复杂的甚 至多行、多列不对齐。例如图 1 中的中美 MBA 报名表： 图 1 细看上表，发现“职务”单元格和“性别” 单元格上下不对齐、“手机”和“行业”不对齐、“ 企业性质”右边线 和“E-mail” 左边线不对齐等等。许多初学者做这样的表格时，总习惯于“性别” 、“职务”、“ 手机”、“行业” 、 “邮编”、“家庭电话”、“办公电话”、“ 企业性质”、“E-mail”都按 1 行 1 列算，“照片”按 4 行 1 列来算，做出来 的表格结果就是:“性别”与“ 职务”上下对齐，“手机” 、“行业” 、“邮编” 上下对齐，“ 家庭电话”、“办公电话”、 “企业性质” 、“E-mail”上下对齐，无论我们再怎么调整列宽，上述单元格都是对齐的，达不到我们想要的 表格效果。见下图: 图 2 那么，到底怎样才能做成上下不对齐的效果呢? 其实并不难，我们现在以调整“性别” 和“职务” 为例，当“性别”和“职务”对齐的时候，这两个单元格所占 的空间是 1 行 1 列(见图 2)，当它们不对齐的时候，细心的人会很快发现，它们的行列已然发生了变化， 为了方便读者观察，我们将图 1 中的这两个单元格的左右竖线分别用虚线画出延长线，效果如下: 图 3 当“性别” 与“职务”不对齐时，“性别”前空格、“ 性别”、“ 职务”都是占 1 行 2 列，“ 职务”后空格占 1 行 3 列。 因此，我们可以在图 2 表格中的“性别”单元格前后分别各插入 1 列，效果如下; 图 4 然后，将“性别”前两个空格合并，“性别” 往右合并 1 格，“ 职务”往左合并 1 格，“职务” 后两个空格合并， 效果如下图: 图 5 将上图中相应的列调到合适的宽度，就可以实现“性别” 和“职务”的上下不对齐效果了。见下图: 图 6 运用相同的办法，将“行业”与“手机” 、“企业性质”与“E-mail”都调成上下不对齐，就可完成整个表格的 制作了。效果如下图; 轻松调整 Excel 奇数行行高 朋友在使用 Excel 中遇到了难题，他想把数据区域中所有的奇数行设为相同的行高。当然，按住 Ctrl 键可以一一点选不连续的奇数行，但对于他的数千行数据来说，显然这是不明智的，而且稍有差池，前功 尽弃。苦思冥想，俺终于帮他找到了解决办法。 第一步，在工作表数据区域右侧选一空白列，如本例的 F 列，在第一个奇数行的单元格中输入“=1/0” ， 回车，如您所料，它将返回一个错误结果“#DIV/0!”，而这正是需要的。选中此单元格及下面一个空白单元 格，将鼠标移至选择区域的填充柄处(此时指针变为十字形) ，按住左键向下拖动，直到此列需要更改行高 的最后一个奇数行单元格处。这样，鼠标所拖过的区域凡奇数行的单元格都被填充了“#DIV/0!”(如图 1)。 第二步，打开“编辑”菜单，选择“定位” 命令，在“定位”对话框中单击“定位条件”按钮，确认“ 公式”下的 “错误”项被勾选，取消“ 数字”、“文本”、“ 逻辑值”的选择( 如图 2)。 单击“确定” 关闭对话框，可见此列凡奇数行的单元格均被选中了(如图 3)。 第三步，连续执行“格式”、“ 行” 、“行高”命令，打开“ 行高”对话框，在文本框中输入要更改的行高值( 如 图 4)。 单击“确定” 按钮，所有要求更改高度的奇数行行高已更改完毕(如图 5)。 最后，清空定位条件列(即 F 列) 的数据或将其删除。 注意:本例选择了“公式” 的“错误”项作为定位条件，因为这一种情况在实际情况中出现的几率很小，当 然还可以根据工作表中的数据类别定制和选择其他的定位条件。 朋友顺利解决了问题，但又提出了新的要求：如果要对数据区域中所有的奇数行进行格式设置，又该 如何办呢?显然，上面的方法选中的仅仅是参考列中的奇数行单元格，无法直接达到要求，只能另辟蹊径。 突然想起了 Excel 的筛选功能，何不试试呢?! 首先，还是在工作表数据区域右侧选一空白列作为参考列，在第一个单元格内输入“0”，第二个单元 格内输入“1”。 第二步，选中“0”、“1”两个单元格，按住 Ctrl 键，利用填充柄向下填充数据，直到数据区域的末行。 选中参考列数据中的某一个单元格。 第三步，打开“数据”菜单，执行“筛选” 、“自动筛选”。 第四步，单击参考列首行的按钮，选择筛选条件“0”，数据区域中，偶数列自动隐藏。下面就可以直 接对奇数列的单元格进行操作了，不仅可以统一调整行高，还可以对单元格进行字体设置、颜色填充等各 种格式操作。 第五步，打开“数据”菜单，执行“筛选” 、“自动筛选”，还原数据区域。 最后，删除参考列。 轻松快捷复制转移整张 Excel 工作表 如果我们想把整张 Excel 工作表复制到另一个 Excel 工作簿，有个很轻松快捷的方法。 打开目标工作簿，切换到包含要复制工作表的源工作簿。 鼠标右击该工作表的标签(如 :sheet1)，并在出现的快捷菜单中选择“移动或复制工作表” 。 将选定工作表移至下拉菜单中，选择将接收该工作表的工作簿(如要将选定工作表复制到新工作簿中， 请单击下拉菜单中的新工作簿)。 选中建立副本复选框(如果没有选中该复选框，该工作表将被移动走，而不是复制) 。 然后单击“确定”就可以了。这样一来是不是快多了呢? 隐藏指定 Excel 行 不让它们被打印出来 在有些情况下，我们并不需要打印 Excel 工作表的某些行。例如，包含机密信息的行，或者包含中间 计算结果的行。 这时，我们可以将这样的行隐藏起来，在打印完工作表之后再恢复其显示。如果工作表中包含许多不 需要打印的行，隐藏与恢复显示的工作就会比较费时费力了。 下面将向大家介绍一种快速切换行的隐藏与显示状态的技巧，使用这一技巧，会大大提高隐藏与显示 非打印行的工作效率。该技巧主要用到 Excel 的“ 组与分级显示”功能。 如图 1 所示，我们将以该工作表为例来进行练习，其中第 5、10、15 行是不希望打印的行。点击此 处下载例子工作表。 图 1 具体操作步骤如下。 1.选中第 5 行，选择菜单命令“数据|组和分级显示| 组合”( 或者按快捷键 Alt+Shift+向右方向键)，如图 2 所示。 图 2 这时，在第 5 行左侧出现如图 3 所示的 标记，并且在工作表的左上方出现 1 和 2，这是分级显示 符号。单击某级别的显示符号，可以隐藏或显示下一级的明细数据。我们要利用的正是分级显示符号的这 一功能。 图 3 2.选择第 10 行，按 F4 键。F4 键的作用是重复最近的一次命令。这里也可以按 Alt+Shift+向右方向键， 只不过不如 F4 来得快捷。 3.选择第 15 行，按 F4 键。现在的工作表如图 4 所示。 图 4 4.单击左上角的分级显示符号 1，将第 5、10 、15 行隐藏，现在的工作表应如图 5 所示。 图 5 经过以上操作，我们可以快速地将不需要打印的行隐藏起来，等打印完了可以再单击分级显示符号 2 将其恢复显示。 小提示：Alt+Shift+ 向右方向键实际上是将某行降了一级，如果想让某行升一个级别，可以按快捷键 Alt+Shift+向左方向键，或者选择菜单命令“ 数据|组和分级显示|取消组合 ”。 制作数据图示半透明的漂亮 Excel 图表 Exc