最近幫朋友解決了比較棘手的一個excel問題，用到的方法和函式在處理大量關聯資料時挺有用，我覺得很能體現excel的強大功能。
應用場景大致是這樣：
朋友公司有大量裝置租賃業務，他們需要兩個報表，一個是裝置的臺帳，要來記錄資產的整體資訊，還有一個明細表頁，用來登記回款資訊。兩個產業間的關聯則是透過編碼，然後藉助vlookup函式關聯關鍵資訊。
到這，其實都是常規的excel處理，並沒有出彩之處。特殊的是裝置的類別管理。因為經營的是同類型的裝置，裝置的品牌、規則比較固定，為了讓在excel檔案中錄入時不因為輸入疏忽出錯，需要將單元格設定為下拉框選擇，而且，後一列規格應該根據前一列選擇的品牌來確定下拉列表的值，當然品牌和規格都是需要可以靈活再增加。
excel的下拉框是用資料檢驗裡的序列來實現，這一點相信很多小夥伴都知道。但是，序列裡的下拉值通常是直接輸入，或者指定到sheet中的一個區域，在sheet中維護下拉值。這樣，固定的下拉值滿足不了之前說的規格要根據品牌更改下拉值的需求。
為了解決這個問題，這裡我們需要用到excel中的變數（或者叫做名稱），然後將變數與品牌對應起來，就可以透過變數來定義包含的規格了，用快捷鍵crtl+f3可以維護一個excel檔案的名稱變數。
（為了更好理解，用汽車品牌和型號舉例，我們可以用下圖所示的一個sheet 定義基礎資料，以後，若需要增加新的品牌和型號，只需要在這個表頁修改，同時更新下名稱管理器中的引用位置即可。）
接下來就是要將名稱關聯到資料校驗的序列了，這裡需要用到excel中一個非常有用的函式：INDIRECT，它是做什麼的呢？標準的解釋是，將一個字串轉化為一個地址引用 。有點拗口，很不理解是吧。
結合這個案例來看，現在我們的需求是：C列【型號】的下拉值要根據B列的品牌的變化而變化，在上一步驟中，我們用名稱定義了每個品牌包含的型號，那現在需要做的就是，根據B列中選擇的品牌，把這個品牌對應的名稱作為C列資料校驗裡的值列表，INDIRECT函式就相當於把B列裡填的值"雪鐵龍"，轉換成了我們之前定義的變數名稱"雪鐵龍"，相當於告訴資料檢驗功能，你要用"雪鐵龍"這個名字去找系統裡定位的名稱，然後把這個名稱下包含的明顯列表顯示出來。
這個函式還可以根據單元格的值，去定位到檔案或者表頁。比如財務資料是分月存放在不同sheet中的，那在彙總的羅列表裡，如果月份填1月，就取1月這個表頁的資料，類似在Excel中實現了簡單的查詢功能。 公式大概是這樣：=INDIRECT(D2&"!F9") ，這裡D2是單元格，用來輸入sheet名稱，後邊的“!F9”字串則是指定對應sheet 中哪個單元格的值。
因為資料量很大，表頁裡的資料轉換成了excel中的表格儲存，會定期儲存到access中，也便於後續做篩選和組合查詢。
總之，excel 在資料處理方面還是蠻強大的，如果再結合VBA和BI分析工具的話，更是如虎添翼了。
最近幫朋友解決了比較棘手的一個excel問題，用到的方法和函式在處理大量關聯資料時挺有用，我覺得很能體現excel的強大功能。
應用場景大致是這樣：
朋友公司有大量裝置租賃業務，他們需要兩個報表，一個是裝置的臺帳，要來記錄資產的整體資訊，還有一個明細表頁，用來登記回款資訊。兩個產業間的關聯則是透過編碼，然後藉助vlookup函式關聯關鍵資訊。
到這，其實都是常規的excel處理，並沒有出彩之處。特殊的是裝置的類別管理。因為經營的是同類型的裝置，裝置的品牌、規則比較固定，為了讓在excel檔案中錄入時不因為輸入疏忽出錯，需要將單元格設定為下拉框選擇，而且，後一列規格應該根據前一列選擇的品牌來確定下拉列表的值，當然品牌和規格都是需要可以靈活再增加。
excel的下拉框是用資料檢驗裡的序列來實現，這一點相信很多小夥伴都知道。但是，序列裡的下拉值通常是直接輸入，或者指定到sheet中的一個區域，在sheet中維護下拉值。這樣，固定的下拉值滿足不了之前說的規格要根據品牌更改下拉值的需求。
為了解決這個問題，這裡我們需要用到excel中的變數（或者叫做名稱），然後將變數與品牌對應起來，就可以透過變數來定義包含的規格了，用快捷鍵crtl+f3可以維護一個excel檔案的名稱變數。
（為了更好理解，用汽車品牌和型號舉例，我們可以用下圖所示的一個sheet 定義基礎資料，以後，若需要增加新的品牌和型號，只需要在這個表頁修改，同時更新下名稱管理器中的引用位置即可。）
接下來就是要將名稱關聯到資料校驗的序列了，這裡需要用到excel中一個非常有用的函式：INDIRECT，它是做什麼的呢？標準的解釋是，將一個字串轉化為一個地址引用 。有點拗口，很不理解是吧。
結合這個案例來看，現在我們的需求是：C列【型號】的下拉值要根據B列的品牌的變化而變化，在上一步驟中，我們用名稱定義了每個品牌包含的型號，那現在需要做的就是，根據B列中選擇的品牌，把這個品牌對應的名稱作為C列資料校驗裡的值列表，INDIRECT函式就相當於把B列裡填的值"雪鐵龍"，轉換成了我們之前定義的變數名稱"雪鐵龍"，相當於告訴資料檢驗功能，你要用"雪鐵龍"這個名字去找系統裡定位的名稱，然後把這個名稱下包含的明顯列表顯示出來。
這個函式還可以根據單元格的值，去定位到檔案或者表頁。比如財務資料是分月存放在不同sheet中的，那在彙總的羅列表裡，如果月份填1月，就取1月這個表頁的資料，類似在Excel中實現了簡單的查詢功能。 公式大概是這樣：=INDIRECT(D2&"!F9") ，這裡D2是單元格，用來輸入sheet名稱，後邊的“!F9”字串則是指定對應sheet 中哪個單元格的值。
因為資料量很大，表頁裡的資料轉換成了excel中的表格儲存，會定期儲存到access中，也便於後續做篩選和組合查詢。
總之，excel 在資料處理方面還是蠻強大的，如果再結合VBA和BI分析工具的話，更是如虎添翼了。