Excel技巧必知必會

• 一、 常用函
• • 1. vlookup()
  • • （1）正向查找
    • （２）多條件查找
    • （3）反向查找
    • （4）多重條件查找
  • 2. sumprduct()
  • 3. 時間函數(shù)
• 二、 常用功能
• • 1. 數(shù)據(jù)透視表
  • • （1）合并匯總表
    • （2）動態(tài)交互圖表
    • （3）計算字段
  • 2. 分列
• 三、 快捷鍵
• • 1. ctrl+e
  • 2. ctrl+home
  • 3. ctrl+向下箭頭
  • 4. ctrl+D

一、 常用函

圍繞中運營工作中經(jīng)常遇到的數(shù)據(jù)處理場景，從函數(shù)和功能兩個方面，講解幾個excel必知必會的小技巧。

1. vlookup()

首先給大家介紹一下excel的萬金油函數(shù)——VLOOKUP(查找值，查找區(qū)域，列序數(shù)，[匹配條件])，它既可以正向查找、逆向查找，還可以多條件查找，它的語法如下：
vlookup函數(shù)共有四個參數(shù)：
查找值：即用誰找，想要看什么條件下的值
查找區(qū)域：在哪里找，查找值在哪個數(shù)據(jù)表范圍內(nèi)可以找到目標值
列序數(shù)：目標值在數(shù)據(jù)表的第幾列，是大于等于1的正整數(shù)
匹配條件：精準匹配和模糊匹配。0：精準匹配，1：模糊匹配。

（1）正向查找

場景一：已知上海公司9月份和10月份各客戶經(jīng)理的收入，如下圖1，現(xiàn)在想知道10月份每個客戶經(jīng)理收入漲跌幅是多少？
　　　　　　　　　　　　　　　　　　表1 上海公司九、十月客戶經(jīng)理收入情況
　　　　　　　　　　　注：簽單人名稱和收入均為杜撰
這時候你可能會說，就這么七八個人，我一個個復(fù)制粘貼過來就好了，那如果有七八百個人呢？vlookup()就派上用場了！在單元格H2輸入【=VLOOKUP(E2,$A$2:$C$8,3,0))】，即可得到孫尚香同學9月的收入，下拉得到所有銷售9月的收入。然后計算增幅即可（如表3）
需要注意的問題：①查找值一定要在查找區(qū)域的第一列；②在選中查找區(qū)域后（第二個參數(shù)），按下F4鍵，固定查找區(qū)域
　　　　　　　　　　　　　　　　　　　　　　　表2 vlookup正向查找

　　　　　　　　　　　　　　　　　　　　　　　表3 vlookup正向查找結(jié)果

（２）多條件查找

場景二：需要匹配上海公司9月和10月，銷售收入和下單數(shù)量
當然可以用兩次vlookup，但是本次介紹一個更為簡便的方式，只需要在單條件查找上稍微改動一下即可。
多條件查找公式：

需要注意的是：①第一個參數(shù)：用美元符號（$）固定查找值的列；②第三個參數(shù)：列序數(shù)由固定的數(shù)字改成相對引用column(B31),當我們往右拖動填充時，里面的B31變成了C31。另外，column函數(shù)只與列有關(guān)，與行無關(guān)。

（3）反向查找

場景三：想知道9月成交訂單為22單的客戶經(jīng)理是誰？
增加一個輔助簽單人列，后續(xù)操作和正向查找一致。

（4）多重條件查找

場景四：整個集團的收入數(shù)據(jù)混在了一張表里，現(xiàn)在要查找北京的宋江同學9月的收入。
當然可以篩選出北京公司，然后在查找到宋江同學，但是今天介紹一個可以批量處理大量數(shù)據(jù)的方式。還是使用vlookup函數(shù)。
公式如下：

需要注意的是：
①查找條件，將條件1和條件2用【&】連接起來；
②查找范圍：需要重新構(gòu)造查找區(qū)域。查找區(qū)域公式 IF({1,0}, 條件1所在區(qū)域&條件2所在區(qū)域, 結(jié)果所在區(qū)域)
③列序數(shù)：由之前的實際所在列，改成2。因為次數(shù)兩個查找條件合并成一個，且結(jié)果所在列是直接選擇出來的；
④出結(jié)果的方式：因為含有數(shù)組運算，所以需要按【ctrl+shift+enter】才能出結(jié)果

2. sumprduct()

函數(shù)功能：將數(shù)組間對應(yīng)的元素相乘，并返回乘積之和
語法：sumproduct(數(shù)組1，數(shù)組2，數(shù)組2, …)

3. 時間函數(shù)

（1） datediff(起始日期，結(jié)束日期，返回類型)：用指定的單位計算起始日和結(jié)束日之間的間隔。
間隔天數(shù)：DATEDIF(“2021-11-11”,“2021-12-12”,“d”) – 結(jié)果值：31
間隔月份：DATEDIF(“2021-11-11”,“2021-12-12”,“m”) – 結(jié)果值：1
（2） weekday（日期，返回類型）：返回指定日期對應(yīng)的星期數(shù)。返回類型為1或省略時，則1-7表示星期天到星期六；當返回類型為2時，則1-7表示星期一到星期天。
示例1：WEEKDAY(“2021-12-12”,1) – 結(jié)果值：1
示例2：WEEKDAY(“2021-12-12”,2) --結(jié)果值： 7
（3） weeknum（日期，類型）：返回日期對應(yīng)1年中的第幾周；類型=1，則一周從周日開始，類型=2，則一周從周一開始。
示例1：WEEKNUM(“2021-12-12”,1) – 結(jié)果值：51
示例2：WEEKNUM(“2021-12-12”,2) – 結(jié)果值：50
（4） workday：獲取間隔若干工作日后的日期
示例：WORKDAY(“2021-12-10”,3） – 返回值：2021-12-15

二、 常用功能

1. 數(shù)據(jù)透視表

數(shù)據(jù)透視表是excel最基礎(chǔ)，功能也非常強大的一個功能，它可以匯總表格、處理不規(guī)范數(shù)據(jù)、制作動態(tài)交互圖表、代替復(fù)雜公式等等功能。

（1）合并匯總表

場景一：需要統(tǒng)計上海公司9~12月，各簽單人的收入總和。
如果用普通的函數(shù)或者手動匯總的話，需要一張張表去統(tǒng)計，耗費大量時間。利用數(shù)據(jù)透視表的多重合并計算，可以快速將這四張表合并起來，并且提供可視化的數(shù)據(jù)篩選。

第一步：插入選項卡，選擇插入數(shù)據(jù)透視表，再選擇【使用多重合并計算區(qū)域】（途中紅框）；

第二步：點擊選擇區(qū)域–>創(chuàng)建單頁字段–>下一步–>選擇sheet1表所有數(shù)據(jù)–>添加–>選擇sheet2表所有數(shù)據(jù)–>添加（依次類推，將所需計算的sheet都添加進去）–>點擊完成；

第三步：得到下表，按需統(tǒng)計數(shù)據(jù)

（2）動態(tài)交互圖表

利用數(shù)據(jù)透視表的切片器功能，可以實現(xiàn)圖表的交互效果：
光標放在透視表任一地方，選擇分許工具欄下【插入切片器】，選擇簽單人和類型兩個字段，得出結(jié)果如下：
場景一：選擇所有的簽單人，以及所有的訂單類型，得到圖表如下如所示：

場景二：選擇部分簽單人，只看收入的情況下，得到圖表如下圖所示：

（3）計算字段

場景三：除了知道各簽單人的收入和簽單數(shù)量，還想知道客單價。
這時候可以通過添加計算字段來實現(xiàn)：分析選項卡下，選擇【字段、項目】–> 計算字段。再填入計算公式，點擊添加、確認即可。

2. 分列

按照固定長度和字符，對字符串進行拆分。
例：從記賬流水中提取花費金額，計算本周消費多少？
第一步：從【數(shù)據(jù)】選項卡，選擇分列功能，使用【分割符號】方式進行分列，分割符號選擇其他，具體字符選擇【費】，如下圖所示：

第二步：得到花費數(shù)據(jù)，在此進行分列，本次分割符號填寫【元】，如下圖所示：

第三步：得到下圖最右邊的結(jié)果

三、 快捷鍵

1. ctrl+e

智能填充：如下圖所示：要給每一個年份后面增加一個【年】字樣。只需在右邊空白處寫上增加后的樣式（1999年），然后將鼠標放在下一單元格，鍵盤按下Ctrl + E，剩余單元格即可完成填充

智能提取：下表是一個流水賬單，現(xiàn)在需要計算本周共花費多少錢？由于流水賬記得比較隨意，沒有標準化，若要計算總花費，需要將藍色部分的花費金額提取出來，然后求和。同樣地，在右邊對應(yīng)空格輸入需要提取的金額【20】，然后在下方單元格按下Ctrl+E，即可完成智能提取。

2. ctrl+home

無論你在表格的何處，只要按下【ctrl+home】，光標1秒鐘回到A1單元格；如果只按【home】，則回到本行A列。

3. ctrl+向下箭頭

光標會滾到最下面一行，同理ctrl和向上、向左、向右依次可到達最上一行，最左一列，最右一列。

4. ctrl+D

如果相鄰列是連續(xù)的區(qū)域，雙擊公式所在單元格的右下角即可復(fù)制公式。如果相鄰列不連續(xù)，可以選取公式區(qū)域后，按下【ctrl+D】快速復(fù)制公式。

總結(jié)

以上是生活随笔為你收集整理的【Excel技巧必知必会】的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。