Excel常用小技巧

Excel有严格的数据格式，如文本、数值、日期、时间型等格式，纯粹输入0-9之间的数据，会被认为是在输入数值型数据。

数据录入的原则

为了避免“垃圾进，垃圾出”，同时也是为了快速准确地录入数据，有关Excel的数据录入有如下原则：

• 尽量减少手工录入劳动量。人不是机器，“干得越多，出错概率越高”。

• 数据录入一次性原则。同样的数据，只录入一次，其它地方可以引用数据所在单元格。这样做的好处是，如果需要改变原始数据，只需在最初的单元格改变即可，无需考虑引用该单元格数据的其它单元格。

• 数据录入限制。比如数据的类型、数据的大小、文本型数据的长度等。还可以对单元格设置保护及访问权限，防止员工误操作。

• 避免重复性劳动。可以用菜单或者查找引用函数使用已有数据。

• 分数的录入方法：

• 直接录入1/3，单元格会显示“1月3日”。

• 分数的录入方式为：整数位+空格+分数。

• 要输入分数1/3，正确的方法是“0 1/3”。

• 身份证号码等长数字串的录入

• 对于身份证号码、银行账（卡）号、手机号码等长数字串数据，Excel无法辨识其数据属性，会默认其为数值型数据。直接录入，Excel按科学计数法处理。如输入身份证号码，结果显示为“3.10102E+17”。

• 对于以0开头的数字串，如学号、工号、产品编号等，Excel默认其为数值型数据，0就显示不出来。如输入学号“0812312”，结果只显示“812312”。

• 对于长数字串、以0开头的数字串，可以先将其单元格的格式设置为“文本”，这样就不会出现上述数据录入的现象了。

• 选中单元格（或区域），“开始”——“单元格”——“格式”——“设置单元格格式”，“数字”选项卡——选择“文本”。

• 大量重复值数据的录入方法

• 输入产品编号、员工编号、银行卡号时，每一个编号中都有大量重复值，如果逐一输入，工作量大不说，而且容易出错。正可谓“干的越多，出错越多！”

• 对于此类问题，我们的方法是：通过自定义格式，给重复值定义好，这样只需输入不一样的值即可。此可谓“好的方法一定是简单的！”

• 操作方法是：

• 选中单元格（或区域），“开始”——“单元格”——“格式”——“设置单元格格式”，“数字”选项卡——自定义。

• 1.产品编码格式设置及快速录入方法

• 针对产品编码，前面“4010209”为重复值部分，自定义格式为："4010209"000。后面的3个“0”，代表占位符。

• 输入1位数“1”，因为占位符为3个“0”，前面自动加2个“0”，显示为“001”，再加上前面的"4010209"，单元格就显示值“4010209001”。

• 输入2位数“12”，前面自动加1个“0”，单元格显示值“4010209012”。

• 输入3位数“123”，单元格显示值“4010209123”。

• 2.产品型号格式设置及快速录入方法

• 产品型号与产品编码类似，都有大量重复值。所不同的是：产品编码统一都是10位，而产品编码不同，有的是19位，如“SRMM0-160/3300(16A)”，有的是20位，如“SRMM0-160/3300(100A)”。

• 对于不固定长度的编码，可以启用占位符“#”。（输入什么就显示什么，不用补齐数据位）

• 针对产品编码，可自定义格式"SRMM0-160/3300("###"A)"。注意：这里的占位符放在中间。

• 输入2位数”16“，就显示为”16“，单元格显示值“SRMM0-160/3300(16A)”。

• 输入3位数”100“，就显示为”100“，单元格显示值“SRMM0-160/3300(100A)”。

• 单个单元格命名

• 如果一次只对一个单元格命名，方法是：

• 选择单元格C3，单击【公式】—【定义的名称】—【定义名称】。Excel会自动将C3单元格左边B3单元格的值（“价格”）作为C3单元格的名称。当然用户也可以另起他名。注意：名称有作用范围（工作簿、单元格或区域）。

• 批量单元格命名

• 也可以一次性同时命名多个单元格。操作方法是：

• 选择批量单元格区域（B3:C9），单击【公式】—【定义的名称】—【根据所选内容创建】。这样，就一次性完成了7个单元格的命名（以左边单元格值作为名称）。

• 然后，根据公式提示分别计算需求量、固定成本、销售收入、总成本及利润等。分别点击C3-C9单元格，就可以看到单元格名称及公式，可以说是一目了然，非常清楚。

• 单元格（区域）的编辑删除与筛选

• 查看已经命名的单元格（区域），点击名称框下拉箭头，选择其中一个名称，就可以查看对应的单元格或区域。

• 通过名称管理器，可以新建、编辑、删除、筛选以及命名的单元格（或区域）。

• 这是一个讲究颜值时代。如果制作的工作表格式美观统一，层次鲜明，会让您在职场上增光添彩。

• 美颜也是职场最基本的需求，所以Excel为大家准备了很多美颜的工具（样式），用户直接调用即可。这犹如手机上的美图功能一样，无需什么技巧，任何人都能制作漂亮的表格。

• 当然，用户也可以自定义样式，满足其个性化需求。

• 单元格样式

• 单元格样式是指一组特定单元格格式（字体、字号、颜色、背景等）的组合。应用单元格样式功能，用户就可以快速对单元格（或单元格区域）进行格式化，大大提高工作效率，且使工作表格式规范统一。

• 单元格样式是美化单元格最常用的技巧和手段。熟练使用样式，就能理解外国人做的表格漂亮的原因了。

• 内置样式使用

• Excel有很多内置的经典样式，如：好、差和适中；数据和模型的计算、输入、输出等；标题及汇总等；主题单元格样式；数字格式等。这些样式可以满足用户大多数的需求。

• 单击【开始】—【样式】—【单元格样式】，将鼠标移至列表库中的某一样式，目标单元格会立即显示应用此样式的效果，单击所需的样式即可确认应用此样式。

• 套用表格样式

• 针对一个表格，Excel也有很多内置格式，选择其一套用，整个工作表就格式化好了。常用的格式浅色、中等深浅、深色等。

• 选择表格区域，单击【开始】—【样式】—【套用表格样式】，将鼠标移至列表库中的某一表格样式，单击就完成了整个表格的格式化。

• 在套用表格样式后，公式计算也会更简单高效。

• 将单元格中重复值标识出来

• 选中所在列（B2:B11），单击【开始】—【条件格式】—【突出显示单元格规则】—【重复值】。在弹出的对话框中，可以进行唯一值（重复值）选择，然后选择显示的格式。除了系统推送的几种格式可供选择外，用户还可以可以自定义显示格式（数字、字体字号、边框、填充色等）。

• 将名字中含Eric标识出来

• 同样的方法，我们可以将文本中含Eric的单元格标识出来。操作方法是：

• 选中所在列（C2:C11），单击【开始】—【条件格式】—【突出显示单元格规则】—【文本包含】。

• 标识上月（或本周）数据

• 我们也可以按日期（上月、上周）标识数据单元格。操作方法是：

• 选中所在列（D2:D11），单击【开始】—【条件格式】—【突出显示单元格规则】—【发生日期】。

• 数据条标识数据

• Excel有丰富的数据条格式：渐变填充、实心填充及自定义格式（其他规则）。操作方法是：选中所在列（C3:C76），单击【开始】—【条件格式】—【数据条】。

• 图标集标识数据

• Excel还有丰富的图标集格式：方向、形状、标记、等级自定义格式（其他规则）。操作方法是：选中所在列（D3:D76），单击【开始】—【条件格式】—【图标集】。

• 色阶标识数据

• 关于色阶，Excel也有很多色阶集，用户也可以选择其他规则进行自定义设置：双色刻度、三色刻度。

• 以三色刻度为例，用户可选择最小值、中间值、最大值的格式。最小值中可按最低值、数字、百分百、公式、百分点值选择。

• 操作方法是：选中所在列（E3:E76），单击【开始】—【条件格式】—【色阶】。

• 面对大的表格，不少朋友都有用颜色隔行（隔列）显示Excel表格数据的需求。这里介绍两种方法：

• 一种方法是条件格式（公式），另一种方法是套用表格格式。

• 用颜色隔行区分数据

• 隔行显示的基本思路是：用Row()函数求出不同行行号，用MOD(ROW(),2)判断行号的奇偶，偶数行（或奇数行）用颜色显示。

• 操作方法是：选择数据区域（B3:E76），单击【开始】—【条件格式】—【新建规则】—【使用公式确定要设置格式的单元格】。

• 如果要偶数行颜色显示，就在公式栏里输入“=MOD(ROW(),2)=0”（奇数行公式“=MOD(ROW(),2)=1”）。点击【格式】选择颜色，最后【确定】。

• 用颜色隔列区分数据

• 隔列显示的基本思路是：用COLUMN()函数求出不同列列号，用MOD(COLUMN(),2)判断列号的奇偶，偶数行（或奇数行）用颜色显示。

• 操作方法是：选择数据区域（B3:E76），单击【开始】—【条件格式】—【新建规则】—【使用公式确定要设置格式的单元格】。

• 如果要偶数行颜色显示，就在公式栏里输入“=MOD(COLUMN(),2)=0”（奇数列公式“=MOD(COLUMN(),2)=1”）。点击【格式】选择颜色，最后【确定】。

• 套用表格格式

• 另外，用套用表格格式方法，也能快速实现用颜色区分行数据。

• 操作方法是：选择数据区域（B3:E76），单击【开始】—【样式】—【套用表格样式】。

• 根据我国法律规定，个人所得税起征点为5000元，超过5000元按累进税率3%-45%征收。

• 这里介绍个人所得税两种便捷的计算方法，一是公式方法，二是自定义函数方法。

• 应税收入=个人全部收入-扣除数

• 计税基数＝全部收入-社会保险及职业年金、住房公积金-5000

• 个人所得税=计税基数*税率-速算扣除数

• Excel中，共有5个计数函数，它们是：

• count()函数——统计数值型数据的个数

• counta()函数——统计文本及数值型数据的个数

• countif()函数——统计符合条件单元格个数

• countifs()函数——统计符合多个条件单元格个数

• countblank()函数——统计空格的个数