灵活运用Excel公式计算个人所得税

一、灵活运用Excel公式计算个人所得税························1二、Excel编辑窍门·····································································2三、Excel数据排序功能集锦··················································3四、在同一Excel单元格中混用文本与数值数据············7一、灵活运用Excel公式计算个人所得税作者：佚名来源：网络2009年4月2日发表评论进入社区个人所得税的计算看起来比较复杂，似乎不用VBA宏编程而只用公式来计算是一件不可能的事。其实，Excel提供的函数公式不但可以计算个人所得税，而且还有很大的灵活：可以随意改变不扣税基数，随意改变各扣税分段界限值及其扣税税率（说不定以后调整个人所得税时就可以用到。）不管是编程还是使用公式，都得将个人所得税的方法转化为数学公式，并且最好将这个公式化简，为以后工作减少困难。以X代表你的应缴税（减去免税基数）的工薪收入（这里的个人所得税仅以工薪为例），Tax代表应缴所得税，那么：当500＜X≤2000则TAX=（X-500）*10+500*5=TAX=X*10-25当2000＜X≤5000则TAX=（X-2000）*15+2000*10=TAX=X*15-125……依此类推，通用公式为：个人所得税=应缴税工薪收入*该范围税率-扣除数在此，扣除数=应缴税工薪收入上一范围上限*该范围税率-上一范围扣除数其实只有四个公式，即绿色背景处。黄色背景处则为计算时输入数据的地方。各处公式设置即说明如下：E3：=C3*D3-C3*D2+E2E4-E10：根据E3填充得到，或者拷贝E3粘贴得到C15：=IF（B15$B$12,B15-$B$12,0）如果所得工薪大于不扣税基数，则应纳税工薪为工薪减去为零不扣税基数，否则，应纳税工薪零。D15：=VLOOKUP（C15,$C$2:$C$10,1）查阅应纳税工薪属于哪个扣税范围。E15：=C15*VLOOKUP（D15,$C$2:$E$10,2）-VLOOKUP（D15,$C$2:$E$10,3）查阅该扣税范围扣税税率和应减的扣除数。这里主要用到VLOOKUP函数，可查阅帮助获取更多信息。C15，D15的公式可以合并到E15中，那样可读性会差很多，但表格会清晰一些。合并后公式：=IF（B15$B$12,B15-$B$12,0）*VLOOKUP（VLOOKUP（IF（B15$B$12,B15-$B$12,0）,$C$2:$C$10,1）,$C$2:$E$10,2）-VLOOKUP（VLOOKUP（IF（B15$B$12,B15-$B$12,0）,$C$2:$C$10,1）,$C$2:$E$10,3）实际上是将公式中出现的C15，D15用其公式替代即可（如图1）。在这里我们只是通过几个常用的公式，就轻松的自动计算出个人所得税的缴纳情况。其实，类似的应用在Excel中还有很多，只要大家运用好公式，并进行合理的组合，那么一定会有更多的收获等着你。二、Excel编辑窍门作者：希赛网来源：希赛新手教程2006年11月10日发表评论进入社区在使用Excel时，经常遇见一些小小的问题，既耽误我们宝贵的工作时间，又影响了工作的质量。下面是我在网上搜集到的一些资料的总结,希望对大家的工作和学习有些帮助！同时对原创者表示感谢！（1）分数的输入如果直接输入“1/5”，系统会将其变为“1月5日”，解决办法是：先输入“0”，然后输入空格，再输入分数“1/5”。（2）序列“001”的输入如果直接输入“001”，系统会自动判断001为数据1，解决办法是：首先输入“'”（西文单引号），然后输入“001”。（3）日期的输入如果要输入“4月5日”，举一反三，像例（1）直接输入“4/5”，再敲回车就行了。如果要输入当前日期，按一下“Ctrl+；”键。（4）填充条纹如果想在工作簿中加入漂亮的横条纹，可以利用对齐方式中的填充功能。先在一单元格内填入“*”或“~”等符号，然后单击此单元格，向右拖动鼠标，选中横向若干单元格，单击“格式”菜单，选中“单元格”命令，在弹出的“单元格格式”菜单中，选择“对齐”选项卡，在水平对齐下拉列表中选择“填充”，单击“确定”按钮。（5）多张工作表中输入相同的内容几个工作表中同一位置填入同一数据时，可以选中一张工作表，然后按住Ctrl键，再单击窗口左下角的Sheet1、Sheet2......来直接选择需要输入相同内容的多个工作表，接着在其中的任意一个工作表中输入这些相同的数据，此时这些数据会自动出现在选中的其它工作表之中。输入完毕之后，再次按下键盘上的Ctrl键，然后使用鼠标左键单击所选择的多个工作表，解除这些工作表的联系，否则在一张表单中输入的数据会接着出现在选中的其它工作表内。（6）不连续单元格填充同一数据选中一个单元格，按住Ctrl键，用鼠标单击其他单元格，就将这些单元格全部都选中了。在编辑区中输入数据，然后按住Ctrl键，同时敲一下回车，在所有选中的单元格中都出现了这一数据。（7）利用Ctrl＋*选取文本如果一个工作表中有很多数据表格时，可以通过选定表格中某个单元格，然后按下Ctrl＋*键可选定整个表格。Ctrl＋*选定的区域为：根据选定单元格向四周辐射所涉及到的有数据单元格的最大区域。这样我们可以方便准确地选取数据表格，并能有效避免使用拖动鼠标方法选取较大单元格区域时屏幕的乱滚现象。（8）快速清除单元格的内容如果要删除内容的单元格中的内容和它的格式和批注，就不能简单地应用选定该单元格，然后按Delete键的方法了。要彻底清除单元格,可用以下方法：选定想要清除的单元格或单元格范围；单击“编辑”菜单中“清除”项中的“全部”命令，这些单元格就恢复了本来面目。三、Excel数据排序功能集锦作者：希赛来源：希赛网2006年11月13日发表评论进入社区在用Excel制作相关的数据表格时，我们可以利用其强大的排序功能，浏览、查询、统计相关的数字。下面，我们以图1所示的“员工基本情况登记表”为例，来全面体验一番Excel的排序功能。一、快速排序如果我们希望对员工资料按某列属性（如“工龄”由长到短）进行排列，可以这样操作：选中“工龄”列任意一个单元格（如I3），然后按一下“常用”工具栏上的“降序排序”按钮即可（参见图1）。小提示：①如果按“常用”工具栏上的“升序排序”按钮，则将“工龄”由短到长进行排序。②如果排序的对象是中文字符，则按“汉语拼音”顺序排序。③如果排序的对象是西文字符，则按“西文字母”顺序排序。图1二、多条件排序如果我们需要按“学历、工龄、职称”对数据进行排序，可以这样操作：选中数据表格中任意一个单元格，执行“数据→排序”命令，打开“排序”对话框（图2），将“主要关键词、次要关键词、第三关键词”分别设置为“学历、工龄、职称”，并设置好排序方式（“升序”或“降序”），再按下“确定”按钮就行了。图2三、按笔划排序对“姓名”进行排序时，国人喜欢按“姓氏笔划”来进行：选中姓名列任意一个单元格，执行“数据→排序”命令，打开“排序”对话框（参见图2），单击其中的“选项”按钮，打开“排序选项”对话框（图3），选中其中的“笔划排序”选项，确定返回到“排序”对话框，再按下“确定”按钮即可。图3小提示：如果需要按某行属性对数据进行排序，我们只要在上述“排序选项”对话框中选中“按行排序”选项即可。四、自定义排序当我们对“职称”列进行排序时，无论是按“拼音”还是“笔划”，都不符合我们的要求。对于这个问题，我们可以通过自定义序列来进行排序：先把相应的职称序列按需要排序的顺序输入到相应的单元格区域（如N2至N18）中（图4）；执行“工具→选项”命令，打开“选项”对话框，切换到“自定义序列”标签下，在“从单元格中导入序列”右侧的方框中输入“$N$2:$N$18”（也可以用鼠标选择输入），然后单击“导入”按钮，将相应的序列导入到系统中，确定返回。小提示：序列导入后，原来N2至N18区域中输入的数据可以删除，导入的序列在其他Excel文档中均可直接使用。选中“职称”列任意一个单元格，执行“数据→排序”命令，打开“排序”对话框，单击其中的“选项”按钮，打开“排序选项”对话框（参见图3），按“自定义排序次序”选项右侧的下拉按钮，在随后弹出的下拉列表中，选中上述“导入”的序列，确定返回到“排序”对话框，再按下“确定”按钮即可。五、用函数进行排序有时，我们对某些数值列（如“工龄、工资”等）进行排序时，不希望打乱表格原有数据的顺序，而只需要得到一个排列名次。对于这个问题，我们可以用函数来实现（以“工龄”为例）：在“工龄”右侧插入一个空白列（J列），用于保存次序（图5），然后选中J2单元格，输入公式：=RANK(I2,$I$2:$I$101)，然后再次选中J2单元格，将鼠标移至该单元格右下角成“细十字线状”时（这种状态，我们通常称之为“填充柄”状态），按住左键向下拖拉至最后一条数据为止，次序即刻显示出来（图5）。图5小提示：若要升序排序，可在公式最后增加一个“非零”参数，如将上述公式改为：=RANK(I2,$I$2:$I$101,1)。六、让序号不参与排序当我们对数据表进行排序操作后，通常位于第一列的序号也被打乱了，如何不让这个“序号”列参与排序呢？我们在“序号”列右侧插入一个空白列（B列），将“序号”列与数据表隔开。用上述方法对右侧的数据区域进行排序时，“序号”列就不参与排序了。小提示：插入的空列会影响表格的打印效果，我们可以将其隐藏起来：选中B列（即插入的空列），右击鼠标，再选择“隐藏”选项即可。四、在同一Excel单元格中混用文本与数值数据作者：李东博来源：天极软件2006年11月13日发表评论进入社区在Excel中，有时我们需要在同一单元格中既显示文本，又显示数值。可以通过以下这些公式技巧来将文本与数字混合显示在同一单元格中。技巧之一例如，假设A6单元格包含数值1234，我们可以在另一个单元格(如D5)中输入以下公式：=总数:&A6则在D5单元格中就会显示出：“总数：1234”，如图1所示。图1在本例中，符号&所起的作用是将文本“总数”与A6单元格中的内容连接在一起。对这样一个包含公式的单元格应用数值格式是不起作用的，因为单元格中包含文本而不是数值。技巧之二如果在公式中巧妙地使用TEXT函数，也可以实现文本与数值同时显示在一个单元格中。例如，我们可以在另一个单元格(如D6)中输入以下公式：=总数:&TEXT(A6,$#,##0.00)这样在D6中就会显示为：“总数:$1,234.00”，如图2所示。图2技巧之三下面是一个使用NOW函数实现同一单元格同时显示文本与日期时间型数值的例子。=本报告打印于&TEXT(NOW(),yyyy-mm-dh:mmAM/PM)则输入完成后显示为：“本报告打印于2006-04-194:21PM”，如图3所示。图3