[算表] Excel如何以Hlookup整理應付帳款

看板Office作者moodyblue時間7年前 (2016/11/28 03:22)推噓0(0推 0噓 0→)

留言0則, 0人參與討論串1/1

Excel如何以Hlookup整理應付帳款網誌圖文版： http://www.b88104069.com/archives/4135 在《會計人的Excel小教室》裡，提到Vlookup和樞紐分析表是會計Excel實務的左右手，該書第三章和第四章，並特別針對這兩項作深入的應用探討。其中Vlookup函數，拆開來是「V」＋「lookup」，「lookup」英文意思是查找，名符其實，不用再多說，而這個「V」，是「vertical」垂直的簡寫，熟悉 Vlookup的讀者，應該很能理解該函數以欄為基準執行查找。今天要介紹的 Hlookup函數，和Vlookup函數如同拜把兄弟，顧名思義是以列為基準執行查找。以下分享具體範例：一、應付帳款明細表，每一列是各個廠商各個日期的應付金額。二、會計人老習慣，有數字的地方就把它加總，橫的加總（各廠商應付總金額）、豎的加總（各日期應付總金額）、各式各樣的加總（詳後敍）。三、接下來是本篇文章。首先在「H2」儲存格，以日期函數帶出今天：「＝ TODAY（）」，計算結果是「27－Nov」（寫文章當日），接著在「I3」儲存格，以Hlookup函數帶出當日應付金額，這部份是新介紹的函數，在下一步驟詳細說明。四、遇到不熟的函數，有個很實用的小功能，輸入函數名稱「＝HLOOKUP（」之後，點擊資料編輯列的「fx」（插入函數），excel會貼心地跳出參數說明及填寫視窗。完整的公式為：「＝HLOOKUP（H2,B1：G7,7,0）」，意思是以儲存格「H2」為指定值（儲存格實際內容為公式「＝TODAY（）」，也就是「 27－Nov」），然後在表格「B1：G7」第一列「B1：G7」尋找指定值「27－ Nov」，尋找結果是「D1」，所以傳回第7列「D7」，得到了當日應付金額是「11,750」。五、上一步驟公式計算結果是應付總金額，基於管理上需要，可能必須呈現各個廠商明細，公式補充修改成「＝HLOOKUP（TODAY（）,＄B$＄1：＄ G4,H4,0），這裡用到了三個小技巧，第一是把TODAY函數內建到HLOOKUP函數裡面，第二是利用「＄」固定行位欄位，避免拉公式時跟著浮動，第三是新增H欄「＝ROW（H4）」作為輔助，用意是取得每個儲存格所在的列數，作為希望傳回內容的參考值。六、會計上的應付帳款，除了當日應付金額，累積應付未付的餘額也是個重點，因為它是資產負債表上的負債，同時也是科目餘額。在Excel公式設計上，必須將累積的概念帶進來，所以先修改第七列的公式：「＝F7＋SUM（G2： G6）」，結果從各日期的應付總額變成是各日期的累積應付金額。七、既然是會計上的餘額，當天的日期比較不重要，因為會計都是截至期末的概念，所以在日期公式做些修改：「＝DATE（2016,11,30）」，從系統當天日期改成可以任意設定的某一天。另外，查找的函數公式也要修改：「＝ G7－HLOOKUP（H2,B1：G7,7,1）」等於是在假設都是如期支付的條件下，配合上一步驟累積已付金額，總應付減掉累積已付，便得到了期末（30－Nov）應付餘額（9,750）。特別注意無論是Hlookup函數或者是Vlookup函數，第四個參數一般填入「0」，表示查找值必須完全相同，如果省略不寫，Excel預值設也是零（邏輯值為假）。不過這裡寫填入的是「1」，表示如果沒有和指定值（30－Nov）完全相同的、那就傳回小於指定值最接近的值（27－Nov），因此在這裡得到結果是9,750（75,000－65,250）。透過這篇文章範例，應該可以熟悉Hlookup函數的使用。一般資料報表的格式，通常類型屬性會放在第一列，第二列開始便是明細資料，實務上會發現很常用到Vlookup，Hlookup用武之地相對少很多，但它仍然是個很方便的查找函數，最好還是要知道這個函數怎麼用，真的需要時候可是很管用。另外這篇文範例所渉及到日期設定和累積金額的概念，都是會計帳務處理上的實務作法，倘能熟稔運用，對於工作有很大的幫助。延伸閱讀（vlookup妙用）： Excel如何將欄位合併進行vlookup比對 http://www.b88104069.com/archives/4089 Excel如何vlookup兩套帳本傳票核對 http://www.b88104069.com/archives/1706 Excel如何vlookup合併欄位排序，搜尋應收帳款最晚收款日 http://www.b88104069.com/archives/4048 -- 周末，我們繼續Excel：精華區=>21.心得=>5.其他=>3.office 會計人的Excel小教室： https://www.facebook.com/acctexcel -- ※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 36.149.135.115 ※ 文章網址: https://www.ptt.cc/bbs/Office/M.1480274523.A.49A.html