一個公式做出媲美ERP下拉拖拽功能的信息表

職場老手說用上ERP就像用了機(jī)械鍵盤，回不來了，因為ERP通過簡單的拖拽就能快速定制信息表。畢竟ERP是塊老奶酪，需要點學(xué)習(xí)成本。今天咱們就試試用一個公式證明excel中的下拉拖拽也能想用就用。先看效果：

1. 自動檢測是否填入信息，啟用標(biāo)題欄樣式

2. 標(biāo)題欄信息可根據(jù)基礎(chǔ)信息表下拉選擇

3. 填入姓名關(guān)鍵字，自動生成對應(yīng)序號及人員信息關(guān)鍵字，并填充表格樣式

4. 行信息可以自動拖動排序，拖動后對應(yīng)行序號重新按照排序后位置更新，人員信息跟隨關(guān)鍵字變化

5. 列信息支可以自由刪減，增加，移動 ，編輯，編輯后的列信息不會出現(xiàn)錯誤

6. 關(guān)鍵字刪除后，其他信息和表格樣式，列標(biāo)簽刪除后，自動刪除顏色填充樣式，重新填入信息后，以上功能依然有效

功能雖多，操作卻很簡單

上述功能雖然說得天花亂墜，其實的重點在于信息的自動生成，至于表格自動填充邊框和樣式，簡單的一個條件格式就完成了。信息自動生成僅僅依賴一個index符合match函數(shù)就能實現(xiàn)，而且這個公式僅需要在第一個單元格內(nèi)編寫一次就完成了，剩下的就是放縱雙擊或者拖拽了

index+match 函數(shù)

要理解這個公式的終極奧義，必須先知道這個公式講的是啥

index，插入函數(shù)，將某個范圍內(nèi)某行某列的值插入到當(dāng)前位置

書寫規(guī)范:

index（被選插入數(shù)據(jù)所屬于范圍，數(shù)據(jù)所在范圍內(nèi)的行，數(shù)據(jù)所在范圍內(nèi)的列）

動畫演示：

match，匹配函數(shù)，將被選關(guān)鍵字 在 關(guān)鍵字所屬范圍內(nèi)的序值 填寫到當(dāng)前位置

書寫規(guī)范：

match（關(guān)鍵字，關(guān)鍵字所在范圍，匹配度）

動畫演示：

這兩個函數(shù)看起來都很簡單，但是如果將這兩個函復(fù)合到一起，將產(chǎn)生能夠完全取代并超越vlookuop的強(qiáng)大函數(shù)，能同時在行和列間以多條件匹配的形式精確找到你要的數(shù)據(jù)信息

書寫規(guī)范

index(被選插入數(shù)據(jù)所屬于范圍,match(匹配行),match(匹配列）

動畫演示：

函數(shù)輸入完成。剩下的就是雙擊或者拖拽了，但在你放肆拖拽之前，必須要是正確的設(shè)置地址鎖定，才能讓生成的數(shù)據(jù)準(zhǔn)確無誤

關(guān)于地址鎖定

地址指的是代表單元格位置或者范圍的字符，如B1，D5:D8 這樣的，ABC....Z 表示列地址，1234....100表示行地址，在沒有設(shè)置地址鎖定（$表示鎖定）的情況下在我們拖動句柄的時候，地址符會根據(jù)拖動的方向變化，

橫向拖動變化列地址（A1→Z1），縱向拖動的時候變化行地址（A1→A100）：

在設(shè)置鎖定符$后（快捷鍵F4），會讓鎖定的行列地址不隨句柄的拖動而變化，$符號在誰前面表示鎖定誰

• $A$1,表示行列均鎖定，拖動行列地址均不變化

• A$1，表示列行鎖定，僅在橫向拖動時，列地址會按照A$1→Z$1變化，縱向拖動時，A$1不會發(fā)生變化

• ￥A1，表示列鎖定，僅在縱向拖動時，行地址會按照$A1→$A100變化，橫向拖動時，$A1不會發(fā)生變化

說了那么多，不知你看懂沒，要是沒看懂我也不知道咋描述了，可能度娘那有更好的答案。

接著我們看我們的公式：

index(被選插入數(shù)據(jù)所屬于范圍,match(匹配行),match(匹配列）

首先，范圍一定不能變化，設(shè)置范圍均為行列鎖定；

對于行匹配，關(guān)鍵字姓名 一直存在于B列，不能變化；向下拖動，需要縱向向下檢索關(guān)鍵字，所以鎖定列，不鎖定行；

對于列匹配，標(biāo)題標(biāo)簽一直存在于第一行，不能變化，而需要根據(jù)所選關(guān)鍵字生成關(guān)鍵字所在范圍內(nèi)行的序數(shù)，所以鎖定行，不鎖定列；完整的公式是這樣的：

=INDEX(Sheet2!$C$2:$P$24,MATCH(Sheet1!$B6,Sheet2!$B$2:$B$24,0),MATCH(Sheet1!E$1,Sheet2!$C$1:$P$1,0))

演示如下：

完成上面的地址鎖定設(shè)置后，放肆的自由拖動吧。最終效果請上翻第1-2圖

關(guān)于無數(shù)據(jù)區(qū)顯示 #N/A

這個可以使用IFNA函數(shù)解決，使用IFNA函數(shù)包圍上面的公式即可，當(dāng)返回值為空時，公式所在的單元格不輸入任何數(shù)據(jù)：=IFNA(公式，'''')

=IFNA(INDEX(Sheet2!$C$2:$P$24,MATCH(Sheet1!$B30,Sheet2!$B$2:$B$25,0),MATCH(Sheet1!E$1,Sheet2!$A$1:$P$1,0)),'''')

關(guān)于標(biāo)題欄的下拉菜單

下拉菜單依然是使用 數(shù)據(jù)驗證 的形式完成的,在我前面的文章中已經(jīng)用到過多次，再演示一遍：

關(guān)于填充邊框及底色的表格樣式

填充表格樣式是利用條件各式完成的，excel2013版本以上的數(shù)據(jù)驗證使用說明寫的十分清楚易懂，一目了然，簡單演示如下：

aoskil