实践出真知
1. 身份证号提取性别
公式：= IF (MOD (MID (B2,17,1),2)=1,“男”,“女”)
首先通过 MID () 函数提取身份证号中的第 17 位，然后通过 MOD () 函数判断奇偶性，其中奇数为男，偶数为女。最终在 IF () 函数的配合下，即可得到性别值。
▲身份证号自动生成性别
2. 身份证号提取生日
公式：=TEXT (MID (B2,7,8),“0000-00-00”)
首先通过 MID () 函数提取身份证号中的第 7-14 位，然后使用 TEXT () 函数对结果格式化（即转换成“xxxx-xx-xx”的样式），最终便得到了我们平时习惯的生日信息。
▲身份证号自动提取生日
注：身份证号码中第 7-14 位为出生日期。
3. 计算年龄、司龄
公式：=YEAR (TODAY ())-MID (B2,7,4)
首先通过 TODAY () 函数获得当前日期，然后使用 YEAR () 函数提取日期的年份值。接下来通过 MID () 函数获取员工出生年份，两者之差即为年龄。类似的方法也可用在司龄计算上，只要将生日替换成入职日期即可。
▲快速计算年龄、司龄
4. 防止身份证号多输少输
做法：通过“数据有效性”限定单元格长度
选中身份证号列，点击“数据”→“数据有效性”，将“文本长度”指定为“18”位。当录入的身份证号码超过或不足 18 位时，Excel 便会自动提醒，以降低前端录入可能导致的错误。
▲通过数据有效性限定身份证列位数
注：通过将“数据有效性”→“出错警告”中的样式修改为“警告”，可以实现只提醒不禁止效果。
5. 手机号码自动加“-”
做法：通过“单元格格式”，修改显示格式
右击手机号码字段，按下 Ctrl+1 进入单元格格式面板，点击最下方“自定义”，输入“000-0000-0000”。确定之后，手机号码即以“XXX-XXXX-XXXX”格式显示。
▲手机号码自动加“-”
注：这种方法只是修改了字段显示样式，并没有修改实际数据，因此后续仍然可以进行统计，这也是和 Replace () 函数最大一个区别。
6. 合同到期前自动提醒
公式：=IF (G7-TODAY ()<30,“合同应续签”,"")
首先建立“状态”列，输入公式“=IF (G7-TODAY ()<30,“合同应续签”,"")”。当合同到期日低于设定值（本例中为“30”）时，便自动显示“合同应续签”。此外也可将提醒天数单独设成单元格，通过公式调取单元格数值，以方便用户自行修改提醒期限。
7. 避免录入重复值
公式：=COUNTIF (B:B,B1)=1
选中数据列，点击“数据”→“数据工具”→“数据验证”，将验证条件改为“自定义”，然后输入公式“=COUNTIF (B:B,B1)=1”。这里 COUNTIF () 函数的作用，是判断 B 列中是否有重复记录（如果有则值值一定大于 1），如果有中止录入，如果没有继续录入。
▲如果录入时出现重复数值将禁止录入
8. 自动标亮周末
公式：=WEEKDAY (E$6,2)>5
首先建立星期行，选中考勤表里的数值区域，点击“开始”→“样式”→“条件格式”→“新建规则”，然后在选框内输入公式“=WEEKDAY (E6,2)>5”，最后选中 E6，按两下 F4 键锁定行不锁定列（即转换成 E$6），再设置一个特别的颜色即可。这里 WEEKDAY () 函数的作用是判断当前列是否大于 5，当 WEEKDAY () 结果 > 5 时（即周六、周日），条件成立，表格自动为该列刷上颜色，不成立时不处理，从而最终形成周末自动上色效果。
9. 让表格自动拓展
方法：Ctrl+T 转成“超级表”
想让表格自动扩展，可以利用 Excel 里的“超级表”功能。具体方法是：选中数据区域，按下 Ctrl+T，然后修改“表格工具”→“表格样式”为“无”（即不使用超级表默认样式）。右击隐藏新弹出的筛选行，即可实现表格的自动扩展。
10. 隔行换色
公式：=MOD (ROW (),2)=0
隔行换色有两种方法，一是转换成“超级表”（Ctrl+T），二是借助公式与条件格式配合完成。以公式法为例，首先选中数据区域，点击“开始”→“样式”→“条件格式”→“新建规格”→“使用公式确定要设置格式的单元格”，然后在选框内输入公式“=MOD (ROW (),2)=0”。这里 ROW () 函数的作用是获取当前行号，和 2 取余后，便得到了 1、0、1、0、1、0…… 这样的数列。由于公式整体位于条件格式内，因此当行号取余结果 = 0 时，条件成立，表格自动为该行刷上颜色，不成立时不处理，最终形成了隔行换色的效果。
注：与传统的格式刷法相比，超级表和公式法都可以实现周末自动填色。且后期无论如何添删，都不会导致色条出现混乱。
11. 自动标记迟到、早退
公式：=IF (B2>TIME (8,0,0),“迟到”,"") 和 =IF (C2<time (18,0,0)," 早退 “,”")< p="">
首先在表格后建立一个“迟到”列，输入公式“=IF (B2>TIME (8,0,0),“迟到”,"")”，再建立一个“早退”列，输入公式“=IF (C2<time (18,0,0)," 早退 “,”")”。这里简单说明一下，time () 函数主要用于约束最终时间，当上班签到时间晚于约束值，或者下班签到时间早于约束值时，便会触发文字提示。< p="">
▲自动标记迟到、早退
12. 自动打序号
公式：=ROW ()-1
首先点击序号列第一组单元格，输入公式 = ROW ()，此时函数返回的是当前行数，根据实际行数计算（比如本文为“1”），发现两者差值为“1”。接下来对公式进行修改，将计算后的差值减到公式后方（），填充后即可实现自动打序号效果。
尽管自动打序号已经实现，但此时仍然不够智能。可以在此基础上再嵌套一个 IF () 函数，只有当右侧有数据时才会显示序号，没有的话直接显示空白。
13. 去除数据有效性列表里的空值
公式：=OFFSET ($O$6,,,COUNTA ($O$6:$O$19))
如果你经常使用“数据有效性”制作下拉列表，就会发现这样一个尴尬，当我们前期为序列留出大量空白时，下拉列表也会出现大量空白，日常操作很不方便。其实这个问题可以这样解决，首先点击“数据”→“数据验证”→“序列”，在“来源”框中填入公式。这里 COUNTA () 函数的作用，是求出当前数据源中的有效记录数，然后通过 OFFSET () 函数确定取值范围。由于公式限定了下拉列表的取值域，因此我们会得到一个完全没有空值的菜单。同时新记录也将自动添加到列表中，不会影响后续操作。
14. 分级考核统计
公式：=LOOKUP (J6,N6:N9,M6:M9)
传统分级统计是通过 COUNT () 函数与 IF () 函数配合计算，虽然简单，可一旦条件过多，就会导致公式异常复杂。类似情况，其实也能借助 LOOKUP () 函数解决。
首先建立一个分级副表，左侧为等级，右侧为达到该等级的考核线。接下来在评级框内输入公式“=LOOKUP (J6,N6:N9,M6:M9)”，将两组取值域按 F4 键全部转为绝对地址。这里 LOOKUP () 的作用是通过目标值，直接到副表中查找对应等级，相比 IF () 函数显得精简很多。而且这样处理后的表格，也方便用户随时调整考核线。
注：使用 LOOKUP () 函数时，要注意副表考核值必须由小到大排列，否则将导致结果异常。
Excel 表格数据乱七八糟：教你几招轻松搞定
做过行政的小伙伴几乎都遇到这种情况，将一个 Excel 空表发给同事，结果收上来却填写得乱七八糟。除了后期繁重的统计分析外，改错也要浪费很多时间，怎样才能解决掉这个难题呢？
1. 数据有效性
“数据有效性”可以规避掉很多前端录入不规范的问题。以 “年龄”为例，首先选中要处理的单元格，点击 “数据”→“数据工具”→“数据验证”，将 “允许”值修改为 “整数”，再将 “最小值”和 “最大值”分别设为 “0”和 “100”（即考虑年龄永远不会超过这两个数）。点击确定后，该字段就会只接收 0~100 之间的数字，其它数值无法输入。类似的，日期、时间、固定长度文本（如手机号录入，避免用户漏输个别数字）等，也可以利用这项功能来规范。
设置 “数据有效性”
2. 善用 “序列”
如果要输入的内容重复率高，且比较统一（比如部门、职务等），那么就可以考虑 “序列”功能。依旧先选择要处理的单元格，点击 “数据”→“数据工具”→“数据验证”。将 “允许”值修改为 “序列”，再将默认选项依次填写到下方 “来源”区（以英文逗号分隔）。经此处理后，除了能规避掉不规范的输入所带来的种种错误，还能在输入时自动生成下拉菜单，提高用户的录入体验。
通过 “序列”功能，除了降低输入错误，还能生成一个小菜单
3. 预设字段格式
输入日期时，有人会习惯 “2021/02/04”这样的标准格式，当然更多的人会采用看上去更简单的 “2-4”。想让字段看起来更整齐，只要先将单元格类型修改为 “日期”，然后再挑选一个日期样式。后期无论哪种输入习惯，Excel 都会自动统一到预设好的样式。除了日期格式外，我们也会利用这项功能约束金额格式，比如是否添加货币符？小数点后保留几位？便是日常中比较常见的几种做法。
预设字段格式
4. 保护公式
如果没有为公式添加保护，要么公式被覆盖，要么被无意间篡改，进而产生很多奇怪的错误。解决方法是：Ctrl+A 全选整个表格，右击 “设置单元格格式”→“保护”，取消 “锁定”前面的复选框。接下来只选中带公式的单元格，再次右击 “设置单元格格式”→“保护”，勾选 “锁定”前面的复选框。这一取一勾之间，便设好了带公式单元格的只读属性。最后点击 “审阅”→“保护”→“保护工作表”→“确定”，使设置生效，后续就不用担心公式会在录入中被覆盖了。
内置公式的单元格，可通过 “锁定”进行保护
5. 只能修改自己的区域
如果表格需要在多个部门间流转，就要考虑如何避免后填写的内容不会覆盖掉前面的内容，这里推荐大家使用 Excel 的 “区域权限编辑”。该功能位于 “审阅”→“保护”→“允许编辑区域”，作用是为不同的单元格区域设置密码，从而区分不同人（或部门）的编辑权限。使用前还是先选好一个区域，点击进入 “允许编辑区域”面板，为第一个部门（人）建立可编辑权限及密码。然后点击 “新建”，再选择一个新的范围及密码，为第二个部门（人）建立编辑区，以此类推完成其他部门（人）的设置，最后点击左下角 “保护工作表”使设置生效。这样当表格下发后，每个人就只能在自己的区域进行编辑，其他区域由于密码不对而无法编辑，从而避免了将别人信息误删除的尴尬。
只能编辑属于自己的区域
Excel神技巧：居然能用来练字
现在很多家庭都有打印机，家长们也希望课余时间能够打印一些字帖，供小朋友们练练字。其实做字帖，Excel 就是一个很方便的工具，之前我们也曾出过一篇文章，介绍如何通过 Excel 打印字帖正方格。
不过和传统的表格不同，字帖大多是由田字格或米字格组成。这也就意味着做好的字帖，并不能像普通 Excel 表格一样，直接向里面输入文字，只能一个字一个字截图粘贴。当然这截图也并非万能的，后期改字就是一个十分麻烦的操作。那么如何制作出一份可以修改文字的字帖呢？这里以米字格为例，教大家一个非常实用的方法。
1. 制作正方格
制作字帖，正方格是少不了的，当然 Excel 也给我们耍了一个小聪明，如果你觉得把行高和列宽统统设成一致就可以，那就大错特错了。因为它的行高、列宽压根就不是同一个单位，当然这个问题也并非无解，之前文章《鲜为人知的技巧！如何在 Excel 中画出标准正方形》已经给出了具体换算方法，不会的小伙伴可以转回再去看一看。
首先画出正方格
2. 制作 “米字格”
正方格做好后，就可以开始米字格的制作了。首先要明确一点，一个 “米字格 “是由四个相邻正方格组成，区别是不同的正方格内要设置不同方向的斜虚线，此外还有一个边框问题，不同位置的正方格，边框样式也是不同的。
一个 “米字格”是由四种边框样式不同的正方格组成
制作边框还是比较容易的，无非就是右击单元格进入单元格属性，再打开 “边框 “标签即可。麻烦的是，四个单元格要设置四种完全不同的样式，这里只能耐心，没有其他捷径可选。
制作 “米字格”只能耐心，没有捷径可选！
一个 “米字格 “做好后，就可以批量制作其他米字格了。具体方法是：选中已做好的 “米字格”，点击 “开始”→“剪贴板”→“格式刷”，然后直接 “刷”出其他米字格即可。需要注意的是，由于每个米字格原则上还是由四个单元格构成，因此在 “刷格”时，一定要注意别刷出 “半个格”就可以了。
批量 “刷出”米字格
最后点击 “视图”→“显示”→“网格线”，将默认的网格线隐藏，这样一个 “米字格”工作表就做好了。
3. 制作文字区
将做好的 “米字格”工作表复制一份，右击行高和列宽，将原有行列值放大一倍，并且右击进入单元格属性，取消所有边框。这一操作的目的，是将之前的米字格，变成同样尺寸的正方形单元格，以便后续添加文字。
将原行列值放大一倍，目的是建立一组和 “米字格”同样尺寸的正方格
接下来，根据需要在正方格里书写文字，并调整好字体、字号（比如楷体）。这里要注意的是，由于文字区和米字格是一一对应的，因此一定要看好行列数。
最终制作好的 “文字表”
4. 将文字刻 “到字帖上
至此所有准备工作已经完毕，接下来就是最重要一步，将文字 “刻”到字帖里。具体步骤是：首先打开快速访问工具栏的向下箭头，点击 “其他命令”。然后将 “常用命令”修改为 “不在功能区中的命令”，并把列表拉至最下方，双击 “照相机”将其添加到快速访问工具栏。接下来，再用鼠标将文字内容全部选中，点击 “照相机”图标，切换回 “米字格”工作表，将截图粘贴到对应位置，取消 “填充色”和 “边框色”即可。
添加 “照相机”到快速访问工具栏
将文字 “截图”到米字格里
后记
至此我们的字帖就完全做好了，由于 “照相机”生成的截图具有自动更新特性，因此当我们修改 “文字表”内容后，“米字格”也会相应发生变化。当然你也可以再对字帖做一些简单修饰，比如修改一下文字区颜色，添加标题等等。此外使用这个方法也可以快速制作田字格，感兴趣的小伙伴不妨一试！
添加好装饰效果的字帖！
提高办公效率！这里有10组最该学会的Excel小技巧
Excel小技巧在办公室里总是神一样的存在，它是区分菜鸟与高手的重大指标。想让自己的工作快捷有效率，就要学会这些提效小妙招。以下是小编总结的10组你最该学会的Excel小技巧，一起来看吧。
1. 一键求和
遇到一个表格，如何快速求和？很简单，把要求的行和列选中，然后分别向外扩出一行和一列的距离，按下Alt+=就可以了。
一键表格求和
2. 金额转大写
财务工作中，常常要把数字转大写，传统的方法很啰嗦。其实最简单一个办法，就是选中待转换单元格，右击“设置单元格格式”。接下来在弹出窗口中，点击“特殊”>“中文大写数字”就可以了。
一键数字转大写
3. 一键标出不同数据
如何标出表中不一样的地方？最简单的一个办法，就是将要比较的行列全都选中，然后按下快捷键Ctrl+Shift+\，最后修改一下单元格背景色，异常数值就被清晰地展示出来了。
一键找不同
4. 一键数值对齐
表格的最后一列，大多都是合计列，想让数值看起来更加整齐，可以先选择要对齐的列，然后点击“数字格式”>“数字”。Excel默认会为每一组数值添加两位小数（不足者以0补齐），这样就比以前整齐很多。此外你也可以设成“会计专用”，更适合处理那些金额合计列。
一键数值对齐
5. 横表变纵表
有时需要将一个横向表转为纵向表，传统办法是Ctrl+C/V。其实最快的方法，是复制表格后（Ctrl+C），直接右击选择“选择性粘贴”，再从弹出面板中点击“转置”就可以了。
一键横表变纵表
6. 整体移动行或列
如何整体去移动一行或一列呢？Ctrl+C/V是个办法，但明显不是最高效的。按住Shift键，将鼠标停到行或列的边缘，再向目标位拖拽。这时你会发现目标列中间开始出现一条加粗的线条，直接松手即可。
整体移动行或列
7. 重复上一操作
遇到需要重复操作的，首先会想到格式刷，但格式刷只能重复设置格式，对其他操作不生效。其实在Excel里，还有一个F4键也能起到类似的效果，只不过它所重复的不是格式，而是上一步的操作。
举个例子，如果我们上一步执行的是合并单元格，接下来想再重复这一操作时，只要选中新的单元格组，按下F4键，就能一遍遍重复这一操作了。
F4键=命令“格式刷”
8. 一键标出重复值
想要在表格中找出重复值，很简单。选中要对比的行或列，然后点击“开始”>“条件格式”>“突出显示单元格规则”>“重复值”，Excel会用一种特殊的色彩将重复值标出。此外这个方法还有另一个好处，那就是以后再有新数据时，也会自动执行这一规则。
一键标出重复值
9. 一键美化表格
做出的表格太简陋，又不想花时间美化？试一试这招，选中你的表格，然后按下Ctrl+T。如果对默认的效果不满意，还可以借助菜单更换其他主题。
一键美化表格
10. 一键生成“↑↓”箭头
表格中数值不明显怎么办？用这个方法可以快速生成“↑↓”箭头。选中要设置的列，右击“设置单元格格式”。接下来在弹出面板中点击“自定义”，将下列代码 [红色][>0]↑0;[蓝色][<0]↓0粘贴进去，确定后效果就出来了。
一键生成“↑↓”箭头