EXCEL高階知識offset函數用法詳解 excel offset函數 _生活經驗

前文提到，會offset函數是面試要求“EXCEL操作能力較強”這類崗位的加分項。文章發出后已經有人詢問這個函數的用法，本來學習EXCEL函數是要循序漸進的，不應該開始就講，但是為了真的能為大家的面試加分，今天就來說說offset函數的用法。
首先看offset函數語法：
offset(reference, rows, cols, [height], [width])
再看EXCEL中給出的該函數解釋：
以指定的引用為參考系，通過給定偏移量返回新的引用。
這個解釋太過簡單，估計很多人還沒有理解，那我再把EXCEL中關于該函數的幫助文件（學習EXCEL很好的一個途徑，就是查閱幫助文件！）放出來，見下圖：

文章插圖
看完了幫助文件，應該都有了大致的了解，下面咱們開始實操！offset函數和其他函數一樣，都有語法和解釋，但是offset和其他函數不一樣的地方在于：不通過實操即使你把語法和解釋都背會了都沒用，因為只有通過實操你才能發現offset的應用場景，而善于發現函數的應用場景（解決問題的思路）才是最重要的！見下圖：

文章插圖
上圖的練習算是熱身，幫助大家理解offset函數語法含義的，接下來才進入到應用場景。
1、對前**名的數據名單進行公示
相信大家都會網絡購物，應該碰到過商家類似的宣傳：活動開始后前**名支付的訂單額外贈送**
說點題外話：在2016年之前，這類的活動還基本都是真的（活動結束后，商家會公布名單），你去搶的話，如果搶到了，商家會兌現承諾的；2016年后，這類的活動則基本都是假的了，因為你無法自己證明你是多少名，名單掌握在商家手中，是否公布？如何公布？都是商家說的算了，所以這類的活動就不要參加了，那種前**分鐘有優惠的才是可以參加的，因為你有支付成交時間，商家抵賴不了。
好了，回到正題，見下圖：

文章插圖
上圖的左側是系統中導出的含有用戶ID和支付時間的數據源，并對數據源進行了初步加工（隱藏用戶ID中間的字符，加上支付時間），最終要實現的效果是上圖的右側部分，即這個問題的理論表述是：如何把單列數據轉成多列？
【EXCEL高階知識offset函數用法詳解 excel offset函數】大家注意觀察，上圖的右側部分不僅僅是把單列數據轉成了多列，還把原來豎向的排序方式變成了橫向的排序，這樣做的目的當然是照顧用戶的閱讀習慣（以用戶思維設計數據分析產品，是一個數據分析從業者的職業素養）。
如何把單列數據轉成多列？能難倒一大批人，當然你非要一個個復制過來另當別論，但是學會了offset函數，這個問題可以說在分分鐘內就可以解決！
我們看最終寫好的offset函數（上圖中G2單元格位置）：
=OFFSET($D$1,COLUMN(A1) 6*(ROW(A1)-1),0,1,1)
這里額外用到了兩個函數：column（主要作用是返回當前單元格在第幾列，比如COLUMN(A1)返回的數字就是1）、row(主要作用是返回當前單元格在第幾行，比如ROW(A1)返回的數字就是1) 。此外還用到了單元格絕對引用知識點，即$D$1，在列或者行前面加上$符號，表示把列或者行固定住了，當拖動公式時相應的地方會保持不動，這里把D1的列和行都加上了$，是因為我們的函數寫好后既要向右拖、又要向下拖，而我們的目是在不管怎么拖，都要保證offset函數的參考系不動。
對函數中的COLUMN(A1) 6*(ROW(A1)-1)需要解釋下，他的功能就是當向右拖動函數時，把原來的豎向排列變成橫向的，當函數向下拖動時，就每隔6行（因為之前的單列數據現在被我們變成了6列數據）開始取數據。COLUMN(A1) 6*(ROW(A1)-1)其實是用了等差數列知識點。
有沒有人對“不用函數就解決單列數據轉多列”感興趣？不管有沒有，”數據分析小哥哥”額外贈送一段，請看下圖：

文章插圖
具體步驟：
（1）在G2單元格輸入D2，然后向右拖動鼠標到L2，你會發現后面依次變成了D3、D4、…D7；
（2）回到G2，向下拖動鼠標到G3，你會發現G3位置上是D3，這時手動給它改成D8，然后繼續向右拖動鼠標到L3，后面會依次變成D9、D10、…D13；
（3）同時選中G2到L3，然后向下拖動鼠標，做出上圖的右側部分效果來；
（4）見證奇跡的時候到了：你按下CTRL H，調出查找替換對話框，在“查找內容”那里輸入D，在“替換為”那里輸入=D，然后點“全部替換”，發生什么了？我的天吶！這么神奇嗎？！！！（此處應有掌聲，哦，不對，是轉發和點贊）
2、快速查詢**業務員**月的銷售業績
問題1只是offset函數的最基本用法，雖然也能驚艷一部分人，但是offset的真正神奇之處還沒有體現出來，請看下圖：

文章插圖
現實工作中，領導總喜歡問這問那的，而我們不可能記得每一個數據，那么我們該如何快速的回答領導的問題呢？很顯然，建立一套“數據查詢模板”能很好的解決這個問題。
上圖中：左邊是基礎的數據源，右邊是查詢框，其中“業務員姓名”和“銷售月份”那里都設置了下拉菜單（對這個知識點感興趣的可留言），可以靈活的選擇業務員和月份，“業績查詢”那里用offset函數預先設置好了，于是：不管領導問哪個業務員、哪個月的銷售業績，都能迅速的查詢出來。這里用到的函數是：
=OFFSET(B2,MATCH(Q3,B3:B10,0),MATCH(R3,C2:O2,0),1,1)
這里面嵌套了match函數，它的功能是查找特定的項（Q3）在某個區域（B3:B10）中的位置，參數“0”表示精確查找。于是上述這個函數的含義是：以B2單元格為參考系，向下的偏移量是“某業務員在業務員區域內的位置”，向右的偏移量是“某月份在月份區域內的位置”，返回的數據區域高度和寬度都是1 。
這個問題的解決，可能有人會認為offset函數也不過如此，這么想你就錯了，因為我為了方便解釋，舉的例子比較簡單，如果我為上述問題多加幾個條件呢？比如加入部門、加入某幾個月累計、加入同比、環比對比…你還會認為簡單嗎？這些條件如果都加入，那么這個查詢框將徹底變成一個查詢系統！整個報表的設計應該是這樣的：
一個工作表里專門存儲數據源（最好直接來自ERP）、一個工作表里專門存儲按不同的業務邏輯進行初步運算的結果數據、一個工作表里專門設計一個復雜的查詢系統，然后再美化下，加入說明、LOGO、各種可視化圖表，一套《**公司**數據查詢系統》就徹底上線了！這套系統將是模型化的、可視化的、動態化的，每天僅僅需要到ERP里把前一天的數據導出來放到“存儲數據源”的那個工作表里即可，而這些功能實現的前提就是必須有offset函數和其他函數的各種復雜嵌套！
3、設計動態圖表
請大家體諒”數據分析小哥哥”碼字的辛苦，就不換其他數據了，還用問題2的數據：當我選不同的業務員時，實現下圖的效果：

文章插圖
上圖中，選不同的業務員，下方的折線圖會隨著變化，變成“對應業務員的年度銷售曲線圖” 。
具體步驟：
（1）在EXCEL中依次點擊：公式-名稱管理器-新建，跳出如下對話框：

文章插圖
在“名稱”那里隨便取個名字，我這里取的是“趨勢線”，在引用位置那里輸入如下函數：=OFFSET(業績查詢!$B$2,MATCH(業績查詢!$Q$3,業績查詢!$B$3:$B$9,0),1,1,12)
注意：“業績查詢”是我這里的工作表名稱，大家練習時可以更換。另：關于名稱管理器，有想深入學習的可以留言，我根據留言人數再決定是否專門寫文章講這個知識點。
（2）選擇B2到N3數據，插入曲線圖
（3）選中曲線-鼠標右鍵-選擇數據，跳出如下對話框：

文章插圖
（4）點上圖紅色方框的編輯，跳出如下對話框：

文章插圖
把上圖中“系列名稱”那里的位置換成業務員姓名下拉框那里的位置，我這里是Q3位置；把“系列值”那里換成剛才定義的名稱，我這里是“趨勢線”，然后點擊確定即可。
（5）這時返回工作表，你再在業務員姓名下拉框那里選擇看看，是不是曲線圖跟著變化了？神奇吧！
offset函數在動態圖表中的應用遠不止此，舉例只是小試牛刀。
今天關于offset函數的應用其實還沒講完，只能說是才講了常見的應用，算是告一段落，看完了沒學會的麻煩多看幾遍，還學不會的…呃…那就跟著”數據分析小哥哥”繼續前行吧，今后可能會繼續碰到這個函數的，總會學會的~~
“數據分析小哥哥”頭條號，以后將不定時更新我在數據分析領域的見解，可能會有數據思維訓練、數據分析過程解讀、數據報告撰寫、分析工具使用等方面的文章不斷出現，如果你想學點數據分析方面的知識，想提升自己的數據分析能力，那么請跟著”數據分析小哥哥”一起前行吧，喜歡”數據分析小哥哥”的話，別忘了分享給你的同事、同學和朋友哦~
,