眾所周知，Excel是微軟office系列中最優(yōu)秀的軟件。很多人說excel啥都能干，這話沒錯，但實際操作起來，終有順不順手一說。這里我列舉幾個自己日常工作中經(jīng)常用到的操作或功能。

1）vlookup

幾乎是公式中最著名的一個。vlookup的核心是匹配。這里列舉以下幾個應(yīng)用場景：

A）最常見的操作，根據(jù)不重復(fù)的索引key來匹配另一個表中的其他字段

例：

已知表一數(shù)據(jù)，求表二中黃色的列。這幾乎是教科書中的例題。

但實際上我們幾乎從未遇到過如此標(biāo)準(zhǔn)的例題。我們遇到的要么就是key不唯一，要么就是不是這么簡單的求一個值。于是有了下面幾個場景

B）根據(jù)投遞的參數(shù)匹配中文描述

例：

導(dǎo)出的點(diǎn)擊投遞數(shù)據(jù)表：

實際的字典表：

這就出現(xiàn)了問題：

1）此時的匹配不是匹配單一的key，而是3個

2）字典中的個別字段，不是明確的，比如rseat表示該card的第幾個位置，這時候沒法精確匹配

問題1解決方法：通過&操作，把三個key值合并成一個，這樣再進(jìn)行vlookup匹配的時候，就自動實現(xiàn)了三個都一致才能匹配成功的效果

問題2解決方法：再增加一列只合并key1和key2的數(shù)據(jù)列，利用函數(shù)：iferror來實現(xiàn)，假設(shè)三個key值連在一起無法精確匹配的話，嘗試匹配前兩個key值是否能找到

這樣操作之后就可以盡可能將匹配失敗的比例降到最低。Iferror函數(shù)若不會使用的話，可以百度一下，很方便。同樣的操作還可以用if(iserror(),,)來實現(xiàn)

同樣可以用這個方法來解決的，還有日期，id等數(shù)據(jù)匹配。我們?nèi)绻枰瑫r精細(xì)到某天某人有沒有做什么行為，同樣可以把日期列和id列合并，然后作為新的key進(jìn)行匹配。

C）查詢兩列數(shù)據(jù)中的重復(fù)項

例：有兩列數(shù)據(jù)，需要看兩列數(shù)據(jù)中有多少是共有的

此時通過vlookup操作進(jìn)行匹配

結(jié)果是這樣的

是不是可以看出，凡是不是#N/A的都是查到（共有）的，而#N/A則都是不共有的。這時候如果需要算一下有幾個共有的，有幾個不共有的怎么算呢？千萬別傻算，總是傻算你就永遠(yuǎn)脫離不了excel小白。開始想把那法用公式算是你脫離小白的第一步。

我們把公式修改一下變?yōu)椋?IF(ISERROR(VLOOKUP(A2,C:C,1,FALSE)),0,1)

解釋一下這行函數(shù)：對這個vlookup的操作進(jìn)行判斷，如果出錯了，那么這個單元格值賦0，如果沒出錯，就賦值1。拉下來效果是這樣的：

有沒有發(fā)現(xiàn)，這時候把B列中的數(shù)據(jù)求和(sum)，就是總的共有的單元格數(shù)；而計數(shù)(count)，就是數(shù)列1的總條數(shù)。而求和/條數(shù)就是數(shù)列1中與數(shù)列2共有條數(shù)在數(shù)列1中的占比。是不是很方便。

D）需要查詢的字段很多，但又希望通過公式直接拖動

例：已知上表，需要填補(bǔ)下表中的數(shù)據(jù)

按照常規(guī)的操作，我們會在Jack字段1中填寫：=VLOOKUP(H2,A:F,2,FALSE)。沒錯，這個是可以查到，同時真?zhèn)€I列的數(shù)據(jù)還都可以查到。但是這時候如果你把同樣的公式拖動到J列的時候，你會發(fā)現(xiàn)J列的公式會是：=VLOOKUP(I2,B:G,2,FALSE)。如果有一定基礎(chǔ)的話，你會說，可以把原本的H2前面加上$，這樣就可以鎖定列并且不會隨著列的拖動而改動了。沒錯，可是第三個參數(shù)呢？第三個參數(shù)永遠(yuǎn)會是2，而你后面的列需要的是遞增的3,4,5。

這時候你需要把公式中的2替換一下，升級為：=VLOOKUP($H2,$A:$F,COLUMN(B2),FALSE)

解釋一下這個改動。首先，所以公式的復(fù)制黏貼，隨著你單元格的位移，所有公式中的行列，都會一起動。所以如果你不希望動的話，需要加上$。所以這個操作中，索引H列不希望動，字典表區(qū)域：A到F列不希望動，所以都需要加上$。然后就是第三個參數(shù)變?yōu)榱薱olumn(B2)，這是什么原因呢？因為公式的復(fù)制黏貼會讓沒有加上$的數(shù)據(jù)都一起動，所以column(B2)取的是B2的列數(shù)，當(dāng)計算I2的時候，它是2；當(dāng)復(fù)制到J2的時候，B2變成了C2，列變?yōu)榱?，也就是說第三個參數(shù)的數(shù)值實現(xiàn)了跟隨列一起遞增的效果。

最后備注幾條：

1）vlookup只能從左邊匹配右邊的數(shù)據(jù)，所以盡可能把key放在最左邊

2）網(wǎng)上會有直接匹配左邊的教學(xué)，個人基本不會用到，你直接剪切到左邊就行了

3）新的office excel版本除了xlookup函數(shù)，可以更方便匹配，如果更新了的話可以使用

2）數(shù)據(jù)格式整理

A）分列

理想中的數(shù)據(jù)源是已經(jīng)很整齊的數(shù)據(jù)表，但現(xiàn)實有時會更復(fù)雜。比如

這時候就需要你自己把他們拆開變成表格的樣式，通過分列的方法。

1）選中目標(biāo)列

2）點(diǎn)擊“數(shù)據(jù)”tab中的“分列”按鈕

3）點(diǎn)擊下一步

4）在分隔符號中選擇or輸入你這個數(shù)據(jù)列中的分隔符號，此時你可以通過下方的預(yù)覽看到實際的變化，再點(diǎn)擊下一步

5）根據(jù)你的實際情況選擇格式，通常為常規(guī)（后面將什么時候選文本或其他），點(diǎn)擊完成

6）根據(jù)你的實際情況選擇格式，通常為常規(guī)（后面將什么時候選文本或其他），點(diǎn)擊完成

同樣可以用到分列操作的還有超長數(shù)字。有時候數(shù)字超過11位時，就會自動變?yōu)榭茖W(xué)計數(shù)法的表達(dá)方式。

但是如果你的這個數(shù)字實際是一個id，或就是一個很大的數(shù)字而你又希望他就顯示數(shù)字的話，怎么辦呢？用分列。一樣的操作，在分隔符號時不需要做任何改動，一路下一步到列數(shù)據(jù)格式，選擇文本。這時候數(shù)據(jù)會變成這樣

左上角有一個綠色的三角，這個三角就表示你的數(shù)值正在以文本的形式存在。同樣奏效的還有一個方法是，直接把單元格的格式變?yōu)槲谋荆缓箅p擊該單元格。這個操作針對數(shù)據(jù)少的時候可以用，但是缺點(diǎn)是必須每個單元格都一一雙擊。所以數(shù)據(jù)多的時候建議用分列。

B）Left，right，mid

例：身份證的不同位數(shù)表示的是不同的含義。假設(shè)某人的身份證是310108123412111234（杜撰的），而你需要拆分成省份，行政區(qū)，生日，尾號四個字段，怎么辦呢？

通過left，mid，right可以解決。具體方法可以自行百度搜索，這里不展開了。

而上方的例子操作簡單是因為他的位數(shù)是固定的，而有時數(shù)據(jù)不是固定長度的，而你會發(fā)現(xiàn)，開頭的兩位是沒有用的，你就需要去掉開頭的兩位，這時候怎么辦呢？

例：AD1234123124，CD1231241，你只需要后面的數(shù)字部分。

解法：=right(A1,len(A1)-2)。原理是：從右邊取比整個字符串長度少兩個的字符串，其實就是等同于去掉最左邊的兩個字符

Mid的操作這里就不贅述了，可以自行拓展一下

3）數(shù)據(jù)透視表

數(shù)據(jù)透視表的原理是針對數(shù)據(jù)進(jìn)行不同維度的統(tǒng)計。而維度指的是不同列中的值。

例：

對于這個數(shù)據(jù)而言，如果你要看每個rpage字段的次數(shù)總和，或者條數(shù)；或者是新用戶的點(diǎn)擊次數(shù)總和，那么你可以用數(shù)據(jù)透視表的方法來進(jìn)行統(tǒng)計。這個是最常規(guī)的使用方法。

所以了解了這個機(jī)制之后，大家就會知道，實際上有了源數(shù)據(jù)之后，你能實現(xiàn)的統(tǒng)計維度是很多樣的。所以如果你需要的數(shù)據(jù)分析不是非常明確的統(tǒng)計維度的話，建議問開發(fā)導(dǎo)出的數(shù)據(jù)不要太聚合。

我個人在設(shè)計BI報表的時候，也會有針對性地設(shè)計一些專門用來數(shù)據(jù)分析的報表，即盡可能將數(shù)據(jù)的維度平鋪開展示，而不是通過篩選項來實現(xiàn)。當(dāng)然這樣的表一張就夠。比如渠道以及對應(yīng)的數(shù)據(jù)。

除此之外，去重計數(shù)是一個數(shù)據(jù)透視表的痛點(diǎn)。但實際上他是能實現(xiàn)的。

例：這是一張用戶點(diǎn)擊特定位置次數(shù)的明細(xì)表

如果我們需要統(tǒng)計點(diǎn)擊各個位置的去重人數(shù)有幾個，通過透視表怎么統(tǒng)計呢？

首先先創(chuàng)建一個普通的透視表

然后在這里點(diǎn)擊更多表格，會生成一個新的透視表

在新的透視表內(nèi)，把位置和id勾選上

然后右擊數(shù)據(jù)，在匯總依據(jù)中選擇其他方式，然后會挑出一個彈窗

在里面選擇非重復(fù)計數(shù)即可

4）其他的話

在這里再分享幾條自己的想法：

1）excel的熟練程度其實只要掌握了基本的幾個函數(shù)的應(yīng)用，就已經(jīng)達(dá)到入門級別了。具體有哪些函數(shù)，自行百度

2）希望excel能力要提升，最最關(guān)鍵的是平時處理數(shù)據(jù)的時候要有通過公式來替代人工解決問題的習(xí)慣。同一個問題你今天不解決，明天不解決就永遠(yuǎn)不會；相反你查了一次，就多了一個技能。你可能之后會忘記，但是你知道有這個方法，下次就能想到

3）平時可以學(xué)習(xí)一些快捷鍵，比如選中可以通過ctrl+A，也可以連帶shift來實現(xiàn)快速選中一整塊；然后建立數(shù)據(jù)透視表可以用alt+n+v；建立篩選可以通過alt+d+f+f。這些東西都是日積月累起來的。最終會讓別人用半小時處理的事情，你5分鐘就搞定

4）沒有必要所有的事情都用excel來處理。Excel最終始終是工具，工具就要順手。如果別的問題有其他工具你更順手，那就用其他工具