這裡的經驗學堂完全是以「專業處理行政事務」的角度來製作的,檔案是要保障我能快速處理行政事務,用簡單的指令,操作的方式,在檔案能負荷的狀態下做到行政簡化並不一定要一個檔案處理一年、兩年的資料量,重點是量力而為,只要能「快、狠、準」處理好自己手上的工作,透過整合,讓不斷重覆的工作,變成只需要做一次,讓一個小時的工作變十分鐘,讓一天的工作變一小時那多出來的時間就是自己賺到的了。

經驗學堂六:將EXCEL做為小型資料庫進行資料查詢

這在第五堂時就有用到的概念,只是在這裡要帶出的是「單筆」資料的所有內容,要做整合,就是要這樣開始漸進式

在第五當中只是進貨、銷貨,然後算出現有的庫存量,那在第六堂就是除了庫存量以外,我們可以單筆資料帶出,比方帶出「XX單號」的內容,「XX產品」的明細,「XX客戶」的明細,或是「XX產品編號」,就可以帶出這個產品的所有資料內容…等等。

那麼,這裡我們使用的範例:進銷存陽春大眾版(http://sumiworkroom.pixnet.net/blog/post/249839456

一、建構所需的「架構」

這是一個概念,就像在第五堂課中所談的「進貨、銷貨、庫存」三個工作表,因為要算出庫存就要有進貨的資料加總去扣除銷貨的資料,所以當只有庫存的需求時,他的工作表相對也只會有進貨和銷貨的資料而已。

那麼在這裡還多了「金額」,甚至是「優惠金額」,那麼可能所需的架構就會不同。

在這裡個範例中的主角是「帶出單項產品的所有內容」,可能有:總進出貨的量、總進出貨的金額、單月的進出貨量、單月的進出貨金額、單月的盈虧、淨利…等等,這些內容會依每個人或每家公司不同而有不同(這也為什麼要量身訂作的主要原因所在)。

 

本檔的主要架構(也就是工作表)分別是:商品資料表、進貨資料庫、銷貨資料庫、商品查詢等四個工作表。

請注意「架構」,因為即使是加了客戶資料,還是廠商資料,都是一樣的做法,我們可以假設所有的商品資料,或客戶資料,甚至是廠商資料都是不同人在作業的,但透過「整合架構」,就算這些工作表都出自不同人的手,最後還是能整合成一張表,也許是表價單,也許是商品庫存狀態,甚至是客戶的消費分析,都是一樣的。

EXCEL經驗學堂六:將EXCEL做為小型資料庫進行資料查詢

二、商品資料表
EXCEL經驗學堂六:將EXCEL做為小型資料庫進行資料查詢

因為這個檔案的主要需求是「了解每個商品的內容」,所以一定要有一張工作表來寫這項商品的所有明細內容(話到內容切記,一定是每個人需求不同,每家公司需求不同,像有些會需求製造商,但有些產業的商品是不需要的)。

三、進貨資料庫
EXCEL經驗學堂六:將EXCEL做為小型資料庫進行資料查詢

支付額、進貨量、單貨,這三欄可以是一個數學運算,有人是以單價乘於數量取得總價,當然也有以總價來除於數量取得單價,看需求,當然大多數會以單價乘於數量來取得總價,只是可能在實際交付款時會有落差,那當然就會有其他處理方式,所以結論:

不管是做哪一種選擇,都一定是透過「數學」及「邏輯」來運算,有時說簡單一點叫「四則運算」,當然也可以當成很深奧的大學數學公式來算。

四、銷貨資料庫
EXCEL經驗學堂六:將EXCEL做為小型資料庫進行資料查詢

這個資料庫特別在價別,又是一個各別需求的欄位,以這個檔來說,只是做商品的管理,但如果是還加上客戶的管理,那麼價別搞不好就會有四、五種都說不定。


五、商品查詢
EXCEL經驗學堂六:將EXCEL做為小型資料庫進行資料查詢

這算這堂課的重頭戲了,所以,我們的公式從這裡開始。

這張表,只有一個欄位是自己輸入的,就是「B1」,其他都是透過公式來帶出的。

那這裡要談的函數叫「VLOOKUP」,在B2的公式「=IF(B1<>"",VLOOKUP(B1,商品資料表!$B:$C,2,),"")」,這個函數的用意在於取得在同一列指向的資料(如果是「HLOOKUP」就變成同一欄,用法兩者相同),要指向什麼?


例如:B1是編號,我要取得指向編號「10012」的商品名稱,這時候就是要用到這個函數。

 

我們來白話讀一下這個公式,先設定我要指向的欄位內容(B1),然後找到我要對應的範圍(商品資料表!$B:$C,商品資料表中的B欄到C欄),然後指定我要的欄數(2,要讀取的欄是C欄,由BC是兩欄,就是第2欄),這樣就可以帶出資料了。

 

這個函數的特點在於一定要「由左向右」讀取,所以如果我要用名稱去帶出編號的內容的話,那麼名稱就要在左,編號在右,因此,設計時一定要注意,需求的是用名稱來帶商品內容,還是編號帶商品內容,例如有人會覺得編號記不住,那就要用名稱來帶,又可能是根本不要有編號,只有名稱,那就一定要把名稱放第一欄來帶後面的資料。

另外,這個函數只會帶出一個資料,也就是說在左欄的編號中,只能出現一次10012這個編號,如果出現兩個10012,那麼就會讀不到第二個,所以一般不會用名稱來帶資料,因為名稱可能會有一樣的,在商品中不顯著,如果這是客戶或學員資料的話,那麼同名同姓的機率就會很大了,那如果遇到同名同姓的話,他只會帶到第一個人的資料,後面那幾同相同的資料就不會讀到。

接著下面其他的欄位就都是數學運算,就不多提了。

在這裡要注意到「這張工作表就是整合」,整合前面三張工作表「進貨、銷貨、商品」的內容,也就是說,前面三張工作表興許是不同人在作業的,倉管人員在做的是商品的基本資料,採購人員做的是進貨登記,業務人員是將商品銷出並記錄,然後商品查詢的工作就是要讓業務員能單就一樣商品,透過商品名稱或編號,知道單一商品的所有情況,商品賣給了誰,又是向誰進了貨,現在的庫存又是如何的,這就是整合。

當在參考這個檔時會發現一件很要命的事:速度好慢!

是的!因為當EXCEL的資料量變大時,就會有這個困擾,所以,這裡的經驗學堂完全是以「專業處理行政事務」的角度來製作的,檔案是要保障我能快速處理行政事務,用簡單的指令,操作的方式,在檔案能負荷的狀態下做到行政簡化並不一定要一個檔案處理一年、兩年的資料量,重點是量力而為,只要能「快、狠、準」處理好自己手上的工作,透過整合,讓不斷重覆的工作,變成只需要做一次,讓一個小時的工作變十分鐘,讓一天的工作變一小時那多出來的時間就是自己賺到的了。

澄邑居/行政魔法屋特點:
◆進銷存、薪資計算、排班表一類設計外,更著重於行政簡化自動運算日常。
◆所謂行政簡化自動運算日常就是一般行政人員在離開ERP、HRM作業後,仍是無法避免的電子化作業。
◆行政同事說並不等於同一個人,基礎是建構在日常行政作業就是會有這些情況,透過範例告訴您,在單純公式下是有解的,公式設計絕不等於程式設計。
◆所有的行政同事說背後都代表著很小的動作,累積後就會是很多的時間,整合前後的差別不在於人力增減,只在於加班狀態增減。
◆一樣還是會有進銷存、薪資計算、排班表一類設計,改變的只是計費上的方案。

於文末附隨檔案一律為試用版,用於功能實體說明,無費用,歡迎下載試用!

檔案下載:
EXCEL經驗學堂六:將EXCEL做為小型資料庫進行資料查詢
下載檔案為壓縮檔,內含一個試用檔以及檔案操作說明。

試用版使用後有任何疑問與想法,包括想了解如何設計,試用版的解鎖,檔案的修改客製,如何計費,歡迎直接來信:

澄邑居/行政魔法屋

arrow
arrow

    JINN 發表在 痞客邦 留言(2) 人氣()