点击上方蓝字关注我吧！
老孙我越来越喜欢EXCEL了。还是那句老话:“每学一点知识，都要用到实际工作中去”，有道是“学以致用”。这不，厂家发来《全钢服务记录单(全钢理赔单)》EXCEL表格(见表一)， 表一
已对我公司送检的每一条轮胎已检测完毕，凡符合条件的轮胎予以赔偿，不符合条件的轮胎则不予赔偿。由于相同规格、花纹的轮胎很多，需要在此表中对予以赔偿的不予赔偿的轮胎按规格花纹分类汇总其数量和金额，编制记账凭证并登记入账。也就是说，该表中轮胎规格花纹有重复，需要将不重复规格花纹的轮胎即唯一值提取出来，并按规格花纹分别统计其予以赔偿和不予赔偿的轮胎数量和金额。由于本公司在库存的设置上，轮胎的名称是将规格和花纹合并后名称，因此，必须先《全钢服务记录单(全钢理赔单)》的规格(j列规格描述)和花纹(K列花纹描述)合并，我用了一个“=CONCATENATE(J3&" "&K3)”公式。CONCATENATE是个文本函数，其功能是将多个文本字符串合并成一个文本字符串。 函数格式：CONCATENATE(text1,text2,...) 参数解析： text1：必须。要连结的第一个文本项。 text2： 要连结的其他文本项，最多可达255个。项与之间必须用逗号(英文半角状态下)隔开。如要加一段文字，应当将文字用双引号(“”)引住。如CONCATENATE(B2,”[email protected],com.cn”)，项与之间遇有空格，应在双引号(“ ”)前后用两个&连结，如CONCATENATE(J3&" "&K3)，其含意就是将单元J3(12R22.5-18)和单元K3(GL295A)合并为单元格L3(12R22.5-18GL295A)，即规格+花纹。合并后向下填充，将所有的规格和花纹合并，见表二。 表二
接下来我们开始提取一列数据中的不重复数据。 Excel如何提取一列数据中的不重复数据？最简便易行的方法是：选择L2:L83→复制→将光标放在M2单元→单击右键→选择性粘贴→粘贴数值和数字选格式→数据选项卡→删除重复项→单击删除重复项→单击确定，适当调整M列的列宽度完成提取不重复项，详见表三、表四、表五、表六、表七、表八。 表三
表四
表五
表六
表七
表八
但作为好学上进的我，总想通过设置函数公式来提取不重复项。有关提取不重复数据的函数公式很多，我最喜爱的是INDEX($L$2:$L$83,MATCH(0,COUNTIF(N$2:N2,L$2:L$83),0))&""，。首先我在B2单元格输入公式=INDEX($L$2:$L$83,MATCH(0,COUNTIF(N$2:N2,L$2:L$83),0))&""，而后同时按住ctrl+shift+enter，将公式转化为数组：({INDEX($L$2:$L$83,MATCH(0,COUNTIF(N$2:N2,L$2:L$83),0))&""})第一个规格花纹，就出现在眼前(表九)，下拉L3公式，就将唯一值全部提取了出来放到L列中共九项，如果算上标题为10项(见表九)，我们可以通过表七印证。 表九
这是一组数组公式，嵌套了三个函数即INDEX、MATCHT和COUNTIF函数。 1、COUNTIF COUNTIF：统计函数，求满足给定条件的数据个数。 函数功能：计算区域中满足给定条件的单元格个数。格式：countif(range，criteria) 参数解析： range 表示条件区域——对单元格进行计数的区域。 criteria 表示条件——条件的形式可以是数字、表达式或文本，甚至可以使用通配符。通俗的解释就是:Countif(条件区域，条件) 本例中COUNTIF(N$2:N2,L$2:L$83)就是统计L列(区域)中不重复(条件)的单元格个数。COUNTIF函数旨在统计某个项目出现的次数，在N$2:N2,L$2:L$83数组中，12R22.5—18 GL295A，一共出现过次36次，但我们只取首次出现12R22.5—18 GL295A的项，作为为不重复项，两次以上出现的就是重复项。哪么这些首次出现的不重复项在什么位置上，是第几单元格？这就需要由COUNTIF(N$2:N2,L$2:L$83)来计算出来，将鼠标放到N3单元格，而后选中COUNTIF(N$2:N2,L$2:L$83)，按住F9，我们可以看到，这部分的结果为{1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}(见表十) 表十
出现这样的结果的原因是因为第一个单元格的计算为在N2单元格内，先计算L1等于L1的个数，而后是L2，而后是L，这样一直计算到L83，形成82个数字，因为“合并后名称”出现一次，所以只有第一个单元格为1，其他全部为0，而后用match函数查出第一次0出现的位置，就是第二个单元格，从而将符合条件数值提取出来。也就是说，1即第一个单元格对应的是第二行为“合并后名称”，1后面第一个0即第二个单元格对应的是第三行即”12R22.5—18 GL295A“。将鼠标放到L4单元格，而后而后选中COUNTIF(N$2:N3,L$2:L$83)，按住F9，我们可以看到，第一个出现的0为第三个数(表十一)， 表十一
就是第三个单元格，对应的是第行4行即“425/65R22.5-20GL286T”；将鼠标放到L11单元格，而后而后选中COUNTIF(N$2:N3,L$2:L$83)，按住F9，我们可以看到，第一个出现的0第53个数(见表十二)，它对应的是54行即“12R22.5-1 GL263D”。 表十二
关于1和0，我咨询过段振云老师，他解释为”1 为存在，0为不存在“，我理解1是反复出现的数据，0是第一次出现的数据，把第一次出现的数据取出来就是不重复项。 2、INDEX INDEX函数：返回指定行列交叉处引用的单元格。 函数功能：INDEX函数返回表格或区域中的值或值的引用。包括数组形式和引用形式两种。(1)数组形式：INDEX函数形式通常返回数组或数值数组。当函数INDEX的第一个参数为数组常数时，使用数组形式。 函数格式：INDEX(array,row_num,[colnmn_num]) 参数解析： Array：表示单元格或数组常量。 row_num：表示选择数组中某行，函数从该行返回。 colnmn_num：可选。选择从该列返回数值。 函数格式的中文表达式：INDEX(数组区域，行数，列数) 案例1：在A2:L83区域查找62行12列的值 方法：在任意单元格比如O4单元格录入“=INDEX(=A2:L83,62,12) EXCEL显示“1200R20-20GL912A+”
(2)引用形式：INDEX引用形式通常返回引用。 函数格式：INDEX(reference,row_num,[colnmn_num],[area_num]) 参数解析： reference：表示一个或多个单元格区域的引用。 row_num：表示引用中某行的等号，函数从该行返回一个引用。 colnmn_num：可选。引用中某殊列标，函数从该列返回一个引用。 area_num：可选。选择引用中的一个区从中返回域，以从中返回row_num和colnmn_num的交叉区域，选中或输入的第一区域序号为1，第二个为2，以此类推。如省略area_num，则函数INDEX使用区域1。 函数格式的中文表达式：INDEX(引用的一个或多个单元格区域，等号，列数，第几个引用区域]) 3、MATCH MATCH：返回指定方式下与制定匹配的元素的相应位置。 函数功能：MATCH函数用于返回在指定方式下与指定数值匹配的数组中的元素的相应位置。函数格式：MATCH(lookup_value，lookup_array，match_type) 参数解析： lookup_value()：为需要在数制表中查找的数值。 lookup_array：可能包含所要查找数值的连续单元格区域。 match_type：为数字-1、0或1，指明如何在lookup_array中查找lookup_value。当match_type为1或省略时，函数查找小于或等于lookup_value的最大值，lookup_array必须按升序排列；如果match_type为0，函数查找等于lookup_value的第一个值，lookup_array可以按任何顺序排列；如果match_type为-1，函数查找大于或等于lookup_value的最小值，lookup_array必须按降序排列。 通俗的表达就是：MATCH(查找什么，在哪查找，匹配方式) 案例2：查找12R22.5-18GL295A的位置 将光标放在单元格R3上，录入“=MATCH(L3,L3:L83,0)”函数公式，意思是说在L3:L83这个区域查找L3即12R22.5-18GL295A的位置。回车立刻显示1，意思是说L3即12R22.5-18 GL295A在L3:L83区域的第1行。
将函数公式改为：MATCH(L3,L:L,0)，回车则显示：3，意思是L3即12R22.5-18GL295A在L:L区域的第3行。
如果把匹配方式由0改为1或省略时，显示的是L列中“12R22.5-18GL295A”最后一次出现的位置83行即lookup_value的最大值。
将光标放在单元格任意单元格上如R3，录入“=MATCH(L3,A3:L3,0)”函数公式，意思是说在A3:L83这个区域查找L3即12R22.5-18 GL295A的位置。回车立刻显示12，意思是说L3即12R22.5-18 GL295A在A3:L3区域的第12列。
案例2解析： 1、MATCH查找的是要查找区域中所处的位置即查找区域的第几行，而不是工作表的第几行，如果在查找区域中包含有多个查找值(重复值)，MATCH将返回查找值第一次出现的位置； 2、MATCH选择一行区域，返回的是一个列，选择一列区域返回的是一个行。 由于MATCH查找出来的值，恰恰是INDEX的第二的参数，所以MATCH函数和INDEX函数堪称最佳组合。 不重复项提取出来了，接下来就要统计每个型号规格的三包出库数量和“三包”支出以及实际折赔数量和折赔金额。 1、在O3单元格录入“=COUNTIF(L:L,N3)”，单击“√”。就统计出一共出库12R22.5-18GL295A三包轮胎33条。 2、在P3单元格录入“=ROUND(O3*VLOOKUP(N3,库存商品明细账!$A$13:$H$278,8,0),2)”函数公式，单击“√”，计算出33条三包轮胎的成本即“三包”支出金额42994.80元。12R22.5-18 GL295A 3、在Q3单元格录入“=COUNTIFS(L:L,N3,E:E,">0")”函数公式，单击“√”，统计出厂家实际折赔的12R22.5-18GL295A轮胎数量为33条。 4、R3单元格录入“=ROUND(SUMIF(L:L,N3,E:E)/1.13,2)”，计算出12R22.5-18GL295A的实际折赔的金额(不含税)。 5、将光标放在03单元格上按住鼠标左键不放，向右拖拽至R3单元格上，当出现十字星时，向下拖拽至R11行，完成其它规格型号的三包出库数量和“三包”支出以及实际折赔数量和折赔金额。 6、在录入“=SUM(O3:O11)”函数公式，单击“√”，计算出全部轮胎的折赔数量将光标放在03单元格上按住鼠标左键不放，向右拖拽至R3单元格上，当出现十字星时，向下拖拽至R11行，将光标放在03单元格上按住鼠标左键不放，向右拖拽至R3单元格上，完成其它规格型号的三包出库数量和“三包”支出以及实际折赔数量和折赔金额的合计数。
根据《全钢服务记录单(全钢理赔单)》编制会计分录： 1、“三包”出库 借：销售费用——“三包”支出113716.30 贷：库存商品 113716.30 2、厂家予以折赔金额 借：应付账款78078.84 贷：销售费用——“三包”支出78078.84 最后，在文章结束时，再叨叨几句关于“三包”支出的税会处理。 1、所谓：“三包”支出，是指包修、包换和包退。 2、在“三包”期内发生的包修、包换不视同销售，其对应的进项税额允许抵扣，包修、包换支出允许税前扣除，但包换的产品(商品)不是同一货物或不是原型号规格，退回的原货物按退货处理，未跨月的应当作废原发票；如果是跨月的，又分两种情况分别处理： (1)采购方未认证的，由销售方开具红字发票信息单，待审核通过后开具红字发票，冲销原销售记录； (2)采购方已认证的，由采购方开具红字发票信息单，待审核通过后开具红字发票，冲销原销售记录； (3)对包换的新货物或新型号规格应当确认收入。 3、“三包”期包退的产品(商品)参照2处理。 4、超过“三包”期，发生包修业务，应当视同销售，其对应的进项税额允许抵扣，包修支出允许税前扣除。 5、建议经销商对客户的“三包”政策与厂家的“三包”保持一致，厂家对经销折赔，经销商对客户也折赔，厂家不折赔，经销商也可不对客户折赔。为了保持与客户的长期客户关系，也可采取无论厂家是否折赔，对客户都进行折赔或包换已售商品。 附： 新收入准则下“退货”的会计处理 主要处理有三个步聚： 一、按照差额确认收入。对于附有销售退回条款的销售，企业应当在客户取得相关商品控制权时，按照因向客户转让商品而预期有权收取的对价金额确认收入，按照预期可能退还的金额确认负债。 二、结转成本，结转时将确认收入的部分进行结转，未确认收入的部分(即预计将退回的部分)确认“应收退货成本”。 三、在每一资产负债表日，企业应当重新估计未来销售退回情况，如有变化，应当作为会计估计变更进行会计处理。 案例： 太原瑞牛轮胎销售公司为增值税一般纳税人。2020年10月10日，向某物流公司销售200条12R22.5-18 GL295AⅡ轮胎，含税单位售价为1100元，单位成本为840元，已开出的增值税专用发票并已发货，但款项尚未收到。根据协议约定，乙公司应于2020年12月31日之前支付货款，在2021年3月31日之前有权退回轮胎。甲公司根据过去的经验，退货率约为10%。 由于轮胎生产厂家非常重视经销对轮胎质量意见的反馈，努力改进轮胎质量，2020年轮胎质量大大提高。2020年12月31日太原瑞牛轮胎销售公司对退货率进行了重新评估，认为只有5%的轮胎会被退回。 案例解析： (1)2020年10月10日发出健身器材时： 借：应收账款 248600
贷：主营业务收入 (1100×200×90%)198000
预计负债——应付退货款 (1100×200×10%)22000
应交税费——应交增值税(销项税额)(1100×200×13%)28600
月末结转销售成本 借：主营业务成本(840×200×90%)151200
应收退货成本(840×200×10%)16800
贷：库存商品 (840×200)168000 (2)2020年12月31日前收到货款时： 借：银行存款 248600 贷：应收账款 248600 (3)2020年12月31日，太原瑞牛轮胎销售有限公司对退货率进行重新评估： 借：预计负债——应付退货款(1100×200×5%)11000 贷：主营业务收入 11000 同时 借：主营业务成本 (840×200×5%)8400 贷：应收退货成本 8400 (4)2020年3月31日因质量问题实际退货为12件，已由生产厂家鉴定予以在新的采购批次中折赔，假定瑞牛轮胎销售有限公司本次12R22.5-18 GL295AⅡ轮胎采购量为1000条，单位成本为850。 借：库存商品 ((850×1000)-8400)841600 应交税费——应交增值税(进项税额)((850×1000)-8400)×13%)109408 贷：应付账款 951008 根据购销合同有关“三包”的条款，向物流公司发出相同数量(12条)、相同型号规格的新轮胎。 借：预计负债 11000 借：销售费用 7600 贷：库存商品 (850×12)10200 应收退货成本 8400
作者：孙文亮
微信号：RENEW09131025
好看的人才能点