本帖最后由 lhm0926 于 2015-3-9 23:38 编辑
在企业管理过程中，我们经常遇到处理大量数据的情况，但并不是每一个企业都有适合自己的配套的信息系统。 EXCEL无疑是非常适合的工具，但绝大多数人只熟悉EXCEL一点点功能。 大部分EXCEL培训教材都是在讲定义，公式的用法，如何使用筛选，数据透视表这些从来没接触过的概念，结果就是学了一堆不知道什么时候该用的东西。等到用的时候，也想不起来该用什么方法来解决。 现在我把自己学习EXCEL的方法与大家分享。 首先我们需要明白的是EXCEL是工具，即然是工具，就一定是为解决某一事情而存在的工具，没有这个事情，工具也就没有存在的意义了。例如大家都在学习英语，学习英语是目的吗？不是，学习英语只是为了与讲英语的人进行沟通。如果全世界的人都讲国语，那就没有人学英语了。所以EXCEL是工具，不是学习的目的。学习的方法无非是你现在面临什么问题，该问题有合适的工具解决吗？如果有，就去学，学会了，就可以解决其他类似的问题。 其次，EXCEL是工具，问题是前提。如果我们不清楚自己的问题，那也就不知道用什么工具来解决了。所以在学习EXCEL前，一定要把自己的问题搞清楚。 本文的重点是讲解决问题的思路，培养正确的数据处理思路，再寻找适合的工具。所以技巧不做为重点。所有案例都基于EXCEL2007来处理。 1. 什么是数据处理？ 数据处理简单的来说就是：数据+规则=结果 数据就是我们要准备的数据，是输入的。规则就是如何处理数据，可以是公式，可以是方法，也可以是一段代码（VBA）。结果也是数据，是输出的。 从上面我们可以得出如下结论： 在遇到问题时我们要先弄清楚哪些数据是要输入的，规则是什么，输入的数据有哪些。这些问题都考虑清楚了再着手进行EXCEL处理，否则就是一团糟。 2. 工资表 工资表是大家都要面临的一件事情，可能很多人只知道自己每个月工资是多少，但是不清楚工资是怎么算出来的，这种人是没有兴趣把每件事情搞清楚的，也不太适合做数据处理的。所以在学习之前，要先把自己的工资表弄清楚，至少不用借助任何工具，你也能用手算出自己每月的工资。用工资表做案例的好处就是每个人都比较容易理解。 3. 一个工资表 这是一张简单的工资表，和大家的工资表没什么太大的区别，无非是一些项目有些差异。 如果你的工作是负责计算工资，当你拿到这张工资表的时候，你先要处理的是什么？ 大多数人都能回答这个问题，就是搞清楚每个工资项目的计算方法，这是正确的，但不够全面。 我们可以按如下方法来考虑： 1） 哪些数据是我们需要输入的； 2） 哪些项目是需要根据输入的数据进行计算的？计算公式是什么？ 3） 输入的结果有哪些？需要格式处理吗？ 规则1： 技能工资、岗位工资、工龄工资、津贴四项内容你可以在财务处拿到数据，不用自己处理； 病事假你可以从负责考期的同事那拿到考期表，再手工计算出应扣的病事假工资； 这样只有工资合计这个项目是需要从其他数据中得出。 公式是：工资合计=技能工资+岗位工资+工龄工资+津贴-病事假 这个公式就是这张工资表中唯一的一个规则。 这张工资表处理非常简单，在前六列输入数据，第七列输入公式。
关于单元格定义，如何输入数据，如何输入公式这里就不去讲了，这是最基本的，大家随便找一个EXCEL教材翻开前几页，基本都是这些内容。 这里涉及一个问题就是，如何填充最后一列的公式，这里只有7行，如果手工录入每行公式的话，估计几分钟内也录入完毕，但是如果有500行怎么办？ 快速填充公式的方法： 方法一： 1） 在G2单元格中输入公式； 2） 选中G2单元格； 3） 按键盘上的Ctrl+C；（点击工具栏上的复制 ） 4） 先把G3到G8单元格； 5） 按键盘上的Ctrl+V（点击工具栏上的粘贴 ）； 方法二： 1） 在G2单元格中输入公式； 2） 选中G2单元格； 3） 移动鼠标至G2单元格右下角，等光标变为黑色实心十字光标（╋）时按住鼠标左键； 4） 按住鼠标左键放，拖动鼠标至G8单元格，松开左键； 方法三： 1） 在G2单元格中输入公式； 2） 选中G2单元格； 3） 移动鼠标至G2单元格右下角，等光标变为黑色实心十字光标（╋）时双击鼠标左键； 4） 这个方法速度最快，不论有多少行，一次性填充，但是这个方法有一个前提条件，就是按此方法填充的行数取决于相邻左侧列有多少连续行，该方法总是自动填充到左侧列最近的一个空行处。 规则二 上面讲了最简单的工资计算，当你面临大量的员工时，要输入的数据可能就会耗费大量的时间。我们再考虑如下的问题： 1） 假设公司规定技能工资按学历划分如下：硕士2500，本科2000，本科以下1800； 2） 岗位工资按岗位划分如下：经理3000，主管1200，员工1000； 3） 工龄工资按入职时间，满1年50元计算 4） 上述三条是我们新增加的三条规则，从上述规则可以看出，只要我们知道每个员工的学历，岗位，入职时间，就可以更容易计算出相应的技能工资、岗位工资、工龄工资，而这些资料很容易从人事部门获取。 先看技能工资和岗位工资，这两项内容是一样的，可以用同样的方法来处理。 要计算技能工资就必须知道该员工的学历是什么，该学历对应的工资是多少，这里就涉及到两块数据，一个是员工的学历，一个是学历的工资。 员工的学历与员工是一一对应的关系，可以在工资表中插入一个新列，命名为学历，在表格其他空白地方输入一个学历和技能工资的对应表，
现在我们要搞清楚的问题是按员工的技能工资按学历匹配起来。这里要注意的是学历列不是我们要的结果，技能工资才是结果，学历列只是为了计算最终结果而使用的中间值。 这样做的好处是什么？ 我们可以设想当公司工资规则有所变动，技能工资调整的话，如果三个学历的工资都进行了调整，那么工资表上所有员工的技能工资我们都要调整，即使按上述工资表，按规则1的方式我们需要调整7个数字，按规则2的方式我们只需要调整三个数字，当员工人数增加时，我们节约的时间是非常可观的。 下面我们要做的就是如果把技能工资同学历匹配起来，这里我们用到的公式是：VLOOKUP，大家可以先在EXCEL帮助中查询一下该函数的用法。 将技能工资的数据更改为公式，变为如下格式： 这里大家会发现，技能工资和工资合计列单元格中显示的是公式，而不是数据，EXCEL有一个切换功能，即在数据和公式之间切换，可以方便的看出哪些单元格设置了公式。 切换方法：按Ctrl+~ 我们把行列位置也显示出来，如下图：
C2单元格的公式为VLOOKUP(B2,J:K,2,0)，什么意思呢？ 第一个参数：B2是我们要匹配的目标； 第二个参数：J:K是我们要匹配的范围； 第三个参数：2是我们匹配后要返回的结果，是匹配范围中所在的列，该例中第2列就是K列， 第四个参数：0是匹配的规则，只有两个选项，1和0，当我们要精确匹配时，即如果没有匹配值的时候，返回的是错误结果，只能用0，后面我们在讲如何计算个人所得税时会涉及到值为1的情况。 该函数所有解决的问题就是在J列中寻找B2单元格的内容，即“硕士”这个字段，找到后返回该字段所在行K列的内容，即2500，如果输入了一个J列没有的内容，例如我们在B2中输入高中，由于找不到该内容，返回错误值，即“#N/A”； 这个函数要注意的问题是 1） 匹配范围可以表示为J:K，也可以表示为J1:K3，我个人习惯用列名直接表示，这样不必考虑该范围有多少行，要注意的是下面的空白部分就不能录入其他无用的数据。 2） 匹配的目标在匹配范围中一定是处于第1列，不然无法得出正确的结果； 3） 该函数对应的是按列查找，如果要按行查找的话，可用HLOOKUP函数 我们用同样的方法可以计算出岗位工资，如下图 小结： 1） 快速填充公式； 2） VLOOKUP函数 后面将继续探讨如何计算工龄工资和如果计算病事假 | 
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2015-3-9 23:36
楼主
