最基礎的excel常用函數與excel技巧分享

一、索引&索引函數——配置和計算的基礎擴展

為什么覺得索引那么重要？

因為在配置和計算中，常常遇到如下問題：

1、在做某幾個表格的計算或配置時，常常會用到另一個表格的內容，手動查找太費時了。

2、當需要調整某個數據時，往往牽一發而動全身，需要修改與其相關的很多配置或計算表格，如果是手動修改效率很低，且沒辦法保證準確性。

而若在數值計算或配置中用到索引函數，可以保證數值來源的一致性和可靠性，也可以提高配置的準確性，當然也帶來了很大的便利（修改某個數值可使相關單元格隨之而更新）。

索引表格的一般用法

1、數值計算

第一步：在excel里用一個單獨的頁簽或區域作為“數據庫”存放原始數據。即一些最原始、需要手填的數據表，如物品ID和物品名稱的對應表、英雄偏向性、英雄ID和其他資料的對應表等等。

1)、格式注意：每個表格的首列/行一般是用唯一標識如ID或者名稱，這些都是用于索引的關鍵詞。

2)、當然某些策劃因為需要常常看到“數據庫”中的內容，選擇把數據庫放在計算表格旁邊，這種做法也是可以的，最好加個顏色標注。

第二步：在其他頁簽中進行計算，在計算過程中凡是要用到原始數據的部分，都必須用索引函數從“數據庫”中取出來，這也之后凡是有要改動的地方，只要改最原始的數據庫就OK啦。

1)、如英雄屬性的計算，如果要改動一個英雄的偏向性，或者游戲中總的屬性上限，只需要改數據庫中對應英雄的偏向性系數，其他的升級、進化屬性的計算表和配置表會隨之而變，可直接觀察結果并復制配置。

2)、 如圖中就是一個最簡單的例子，計算區域的數值全部是由兩個基礎的表格來的，只需要填入關鍵詞（即需要計算的是什么類型、幾星、什么品質的英雄），即可索引出對應數據并計算。而且我只要修改上方數據源某個數據，下方計算區域也會隨之而改變。

3)、該表格為粗糙的示例表格，正式使用時可變化方式。

2、 配置方面

1)、配置方面運用到索引函數就更多了，而且比較繁雜，一般是從計算表中索引最終數值到對應配置位置，或批量更名等小配置的修改，不是很有統一的步驟，但是用熟了索引函數之后會發現那些都不是事兒。

2)、此處以批量更名為例：

• 首先圖中用vlookup根據配置表中左側原本的名稱，在更名表中索引出更改后的名稱。
• 然后用iferror處理更名表中沒有的內容，即不更名的那部分英雄，名稱=原本配置的名稱。
• 最后將B列更名后的名稱直接復制，以”選擇性粘貼”中的數值粘貼到A列，再把B列的輔助列刪除即可。當然為了影響最小，你可以選擇把輔助列放到表格最后面那列。

常用的索引函數

叨叨了那么多，終于進入正題了，其實索引的方式有很多種，但是為了避免混亂，以下只介紹3種。

1、 vlookup/hlookup——最常見的索引函數

１)、用途：

根據首列（或行）的信息查找后面特定列（或行）中指定的內容。

2)、用法：

• Vlookup(索引標志/條件，索引的數據庫范圍，返回列數，精確（fault）/模糊（ture）查找))
• Hlookup(索引標志/條件，索引的數據庫范圍，返回行數，精確（fault）/模糊（ture）查找))

３)、 特殊用法：雙重索引

• 在單元格中輸入=vlookup(條件1&條件2, if({1,0}, 條件1所在列&條件2所在列, 返回內容所在列), 2, false)
• 輸入完后記得要按ctrl shift enter組合鍵才會出來結果。

4)、優缺點

• 優點在于單一條件索引時簡單直觀，且可索引各種類型的內容。
• 缺點在于不方便做多條件索引，且限制了原始表格的表頭只能都在列上或都在行上。

2、 Sumproduct——多少條件都不怕的數值索引函數

１) 用途：

• 將符合索引條件的單元格輸出，若滿足條件的對象大于等于2個，則會輸出所有對象的加和結果，這是個雙刃劍。
• 多重條件索引的利器，但無奈限制于只能輸出數值類型的結果。

2) 用法：

• sumproduct((條件1=查找范圍1行/列)*(條件2=查找范圍2行/列)*（條件3=查找范圍3行/列)，查找范圍(表、行、列都行))
• 如果想快速記憶的話，可以考慮用矩陣相乘的方法理解，以D16為例。

(1)$A16=$A$1:A$10這句是指在A1:A10范圍內匹配出與”1星”相同的位置，匹配成功記為“1”，否則記為“0”，最終得出一個列矩陣。

(2)$A$1:D$10這句是指在A1:10范圍內匹配出與”血量”相同的位置，匹配成功記為“1”，否則記為“0”，最終得出一個行矩陣。

(3) 用之前所得的列、行矩陣相乘，得出一個最終的矩陣圖。

(4)用該矩陣圖去匹配所選的區域范圍A1：10，選出矩陣中為“1”的點的數值，若有多個，則將其加和后輸出。

3) 優缺點：

• 優點在于可N重條件索引，凡是數值類型的索引都強烈推薦此函數。
• 缺點在于索引結果只能為數值。

3、Match、index函數共用——雞胸肉一般的索引函數

1)、用途：

• 可同時對行、列進行索引，即對一般同時帶有行、列的表頭的表格進行雙重索引。
• 可對最常見類型的表格進行行列的雙重索引，彌補了前兩個函數的一些不方便的地方。

2)、用法：

• Index(查找區域，match(目標值1,對應行)， match(目標值2,對應列), 0)
• match(匹配值，匹配區域，查找模式)：尋找特定值在某個表頭中的相對位置，查找模式一般用 0（精確查找），另外1=小于、-1=大于。
• index(行數，列數)：根據特定行數、列數查找到制定位置的信息。
• 這個就直接上圖好了，不明白的可以看看附件。

3)、優缺點

• 優點在于可索引所有類型的數值。
• 缺點在于只能用于指定格式的數據源，但是這種格式也是最常見的了

４)、使以上用索引函數時的一些注意點：

• 要注意使用絕對/相對引用

(1)寫索引函數選擇“數據源”區域后，記得用絕對引用。

(2)在寫索引函數用于索引的條件時，也需要根據當前表格結構選擇相對或絕對引用。

• 不要隨便使用剪切功能

(1)寧愿使用復制和刪除，剪切和移動會導致引用的公式出現錯誤。

(2)這其實是基本常識，即使不是索引函數，使用其他excel函數時也需要注意。

• 如何擴展“數據源”表格？——插入單元格

(1)經常會遇到數據源需要增加內容的情況，如物品價值表中又加入了幾種道具啥的，此時需要做的是：將需要添加的新內容插入到倒數第一行/列的前面，這樣原來引用該區域的單元格會自動擴展其引用區域。

P.S.可在擴展完數據庫后再調整表格內容結構，同樣注意使用復制和刪除，而不是剪切！

• 每個表格的第一列/行，一般是用來做索引的關鍵詞如ID、名稱或屬性標志，盡量避免根據第2行去索引第1行數據的情況。