Excel如何以Hlookup整理應付帳款 - 會計

Rachel avatar
By Rachel
at 2016-11-28T03:20

Table of Contents


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


--
Tags: 會計

All Comments

有關測試資料法(卡堆法)

Ophelia avatar
By Ophelia
at 2016-11-27T19:48
各位好 請問卡堆法的敘述何者錯誤? a:屬於ㄧ般控制的控制測試程序 a是錯的。但是我不確定他是錯再哪個部分 想請教大家。是應用控制才對嗎? 還是應包含證實測試及控制測試? 謝謝大家幫忙 - ...

林蕙真 中級會計新論8版(上下冊)

Elvira avatar
By Elvira
at 2016-11-27T13:52
[誠徵] 林蕙真_中會新論(8th) 上下冊 [價格] 請站內信詳談 [狀況] 八成以上 - ...

公司法董事轉讓選任時持股逾1/2

Jessica avatar
By Jessica
at 2016-11-27T12:06
自己回文,感謝Arthur1118及schokolade46大大的解釋。 經濟部函令加了一個限制,不得設質的上限為當選時支持股的1/2。 所以不得行使表決權之股數 = Min((30,000 - 10,000*1/2),10,000*1/2) = 5,000 ...

名師會計師/高普考/記帳士 12月新班開課

Hedda avatar
By Hedda
at 2016-11-26T18:05
名師會計師/高普考/記帳士 12月新班開課 精選課程:審計最新公報第57-61號專班 黃吉吉文老師授課 105/12/29-106/1/19上課 名師106會計師-正規班課程表 科目 師資 開課日 證交法 徐祥瑞老師 12/18(日) 商會法 ...

Emily avatar
By Emily
at 2016-11-26T17:34
商品名稱:成管會蕭靖題庫QA 交易地區:全國 商品價格:來信報價 新舊狀況:8成以上 交易方式:郵寄店到店 聯絡方式:站內信 其他說明:越接近2017越好 - ...