點選上方藍字關注我吧！
老孫我越來越喜歡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
好看的人才能點