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

1）vlookup

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

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

例：

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

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

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

例：

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

實(shí)際的字典表：

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

1）此時(shí)的匹配不是匹配單一的key锰扶，而是3個(gè)

2）字典中的個(gè)別字段献酗，不是明確的，比如rseat表示該card的第幾個(gè)位置坷牛，這時(shí)候沒法精確匹配

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

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

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

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

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

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

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

結(jié)果是這樣的

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

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

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

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

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

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

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

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

解釋一下這個(gè)改動(dòng)。首先橄登，所以公式的復(fù)制黏貼抓歼，隨著你單元格的位移，所有公式中的行列拢锹，都會(huì)一起動(dòng)谣妻。所以如果你不希望動(dòng)的話，需要加上$卒稳。所以這個(gè)操作中蹋半，索引H列不希望動(dòng)，字典表區(qū)域：A到F列不希望動(dòng)充坑，所以都需要加上$减江。然后就是第三個(gè)參數(shù)變?yōu)榱薱olumn(B2)，這是什么原因呢捻爷？因?yàn)楣降膹?fù)制黏貼會(huì)讓沒有加上$的數(shù)據(jù)都一起動(dòng)辈灼，所以column(B2)取的是B2的列數(shù)，當(dāng)計(jì)算I2的時(shí)候也榄，它是2巡莹；當(dāng)復(fù)制到J2的時(shí)候，B2變成了C2，列變?yōu)榱?榕莺，也就是說第三個(gè)參數(shù)的數(shù)值實(shí)現(xiàn)了跟隨列一起遞增的效果俐芯。

最后備注幾條：

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

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

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

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

A）分列

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

這時(shí)候就需要你自己把他們拆開變成表格的樣式岩睁，通過分列的方法钞脂。

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

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

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

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

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

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

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

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

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

B）Left叮称，right，mid

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

通過left谴古，mid质涛，right可以解決。具體方法可以自行百度搜索掰担，這里不展開了汇陆。

而上方的例子操作簡單是因?yàn)樗奈粩?shù)是固定的，而有時(shí)數(shù)據(jù)不是固定長度的带饱，而你會(huì)發(fā)現(xiàn)毡代，開頭的兩位是沒有用的，你就需要去掉開頭的兩位勺疼，這時(shí)候怎么辦呢教寂？

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

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

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

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

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

例：

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

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

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

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

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

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

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

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

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

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

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

4）其他的話

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

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

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

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

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