採購工作必備Excel實用技巧大全（收藏）

1.快速複製表格

直接拖動表格，可以移動到另一個位置，如果按ctrl不松拖動表格，可以快速複製出一個表格。

2.快速翻看工作表

按Ctrl+Pgdn/Pgup可以快速翻看工作表。不用一個個點了。

3.快速輸入序號/快速調整列寬

• 做表時輸入序號是必不可少的，但想更快，記住一個規則：

• 按Ctrl鍵不松可以讓純數字自動遞增，也可以讓含文本+數字中的數字不變。

• 調整列寬有個小技巧：雙擊邊線可以讓列寬自適調整

4.快速插入空列

需要插入幾列就選取幾列然後插入

5.不複製隱藏的行

按Alt+；選取顯示的行，複製-粘貼

6.兩列快速互換

左手按shift鍵不松，右手按滑鼠左鍵不松拖動列邊線，可以快速讓2列換位。

7.一鍵顯示所有表格中公式

按Ctrl+~（波浪線）可以切換公式的顯示或隱藏，但，這個快捷鍵常被輸入法給佔用。所以點擊公式選項中的「顯示公式」命令最靠譜。

8.快速選取單元格區域

快速選取區域的技巧：

按Ctrl+A選取整個連續的表格

按Ctrl+shift+方向鍵可以選取連續的區域

9.快速複製公式

如果相鄰列是連續的區域，雙擊公式所在單元格的右下角。如果鄰列不連續，可以選取後按Ctrl+D複製。

Vlookup常用公式

1.一般查找

如下圖所示，根據姓名查找職位

2.從左至右查找

如下圖，要求根據姓名查找工號

3.查找公式複製

在表二中，根據姓名從表中查詢對應信息

4.區間查找

根據左表的提成比率表，在右表中更具銷售額在G列查找適用的比率

5.模糊查找

查找包含AAA名稱的產品價格

=VLOOKUP("*"&A10&"*",A2:B6,2,0)

6.多項查找

從下表中同時查找「李飛」和「南寧」的年齡 ，並返回最小的。

=MIN(VLOOKUP(T(IF(1,{"李飛","南寧"})),A2:C6,3,0))

7.隔任意列求和

隔1列求和

數組公式：大括弧是按ctrl+shift+enter後自動產生的，非手工輸入

{=SUM(VLOOKUP(A2,A2:K2,ROW(1:6)*2-1,0))}

指定列求和

指定對2,3,5,6,8列求和

公式：

{=SUM(VLOOKUP(A2,A2:K2,{2,3,5,6,8,9},0))}

註：{2,3,5,6,8,9}把要求和的列數放在大括弧內，用逗號分隔。

8、多條件查找

如下圖所示要求，同時根據姓名和工號查找職位。

{=VLOOKUP(B9&C9,IF({1,0},B2:B6&A2:A6,E2:E6),2,0)}

9.多區域查找

根據不同的表從不同的區域查詢

=VLOOKUP(B2,IF(A2="銷售一部",A5:B9,D5:E9),2,0)

10.多工作表查找

從各部門中查找員工的基本工資，在哪一個表中不一定。

方法1

=IFERROR(VLOOKUP(A2,服務!A:G,7,0),IFERROR(VLOOKUP(A2,人事!A:G,7,0),IFERROR(VLOOKUP(A2,綜合!A:G,7,0),IFERROR(VLOOKUP(A2,財務!A:G,7,0),IFERROR(VLOOKUP(A2,銷售!A:G,7,0),"無此人信息")))))

方法2：

=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT({"銷售";"服務";"人事";"綜合";"財務"}&"!a:a"),A2),{"銷售";"服務";"人事";"綜合";"財務"})&"!a:g"),7,0)

11.一對多查找1

根據產品查找相對應的所有供應商

A2 =B2&COUNTIF(B$1:B2,B2)

B11=IFERROR(VLOOKUP($A11&COLUMN(A1),$A:$C,3,0),"")

12.一對多查找2

查找張三的所有消費記錄

數組公式，大括弧是按ctrl+shift+enter後自動產生的，非手工輸入

{=VLOOKUP(B$9&ROW(A1),IF({1,0},$B$2:$B$6&COUNTIF(INDIRECT("b2:b"&ROW($2:$6)),B$9),$C$2:$C$6),2,)}

SUM函數

1.設置求和公式

傳統做法是設置一個SUM公式，然後複製複製。其實只需要按CTRL+G定位空單元格，然後點一下∑（或按住「ALT」和「=」）

2.設置總計公式

在設置總計公式時，不需要用小計1+小2+小3...了，只需要設置公式：

=SUM（B2：B14）/2

3.多表求和

例：合計從1日到31日的所有B2單元格之和。

=SUM(1日：31日！B2）

4.多條件求和

雖然不是SUM函數的強項，但可以給一個小攻是

{=SUM（（a1：a100=「A產品」）b1:b100）}

5.合併單元格求和

合計單元格的求和是一個難點，如果用一般的方法公式很複雜，如果用SUM函數的倒減法，就會變簡單

=SUM（C2：C13）-SUM（D3：D13）

在輸入公式時不能拖動複製，需要選取D2：D13，先在D3輸入公式，然後把游標放在公式結尾處，按ctrl+回車鍵批量輸入

6.任意多表求和

如果N個不相鄰的表的同一個單元格求和，不能直接用SUM，需要用SUM（SUM（結構，這裡等同與SUM(N，提取三維引用的單元格求和）

=SUM（SUM（INDIRECT（{1,2,3}&「！A1」）））

=SUM（N（INDIRECT（{1,2,3}&「！A1」）））

Countif函數

如果評工作中最常用的函數是哪個，Vlookup函數是採購公認的NO.1函數，但它只能用於查找。在Excel中還有一個函數比它更有用，是Excel中最重要的一個函數。那就是Countif函數

1.精確統計

1）返回A列包含值12的單元格數量

=COUNTIF (A:A,12)

2）返回A列部門為「採購部」的數量

=COUNTIF（A:A,「採購部」）

字元在公式中需要加雙引號

3）返回包含值；邏輯值為TRUE的單元格數量

=COUNTIF（A:A,TRUE）

4）返回A1：A10空單元格的數量

=COUNTIF（A1：A10，「=」）

5）返回A列非空單元格的個數（相當於counta函數）

=COUNTIF（A1：A10，「<>」）

6）返回A1：A10區域，真空+假空的數量

=COUNTIF（A1：A10，「」）

7）返回A1：A10區域所有單元格的數量（非空+空值）

=COUNTIF（data，「<>「」」）

8）返回A1：A10假空單元格數量

=COUNTIF（A2：A32，「」）-COUNTIF（A2：A32，「=」）

真空+假空-真空=假空

9）統計A列身份證號410105198504182965出現的數量

=COUNTIF（A:A，「410105498504182965*」）

默認情況下，countif函數對數字只識別前15位，而帶上通配符*後可以識別其為文本型格式。

2.模糊計數

1）返回A列單元格包含「採購部」的數量（如：A公司採購部）

=COUNTIF（A:A，「*採購部*」）

2）返回包含以單詞「AB」（部分大小寫）開頭內容的單元格數量

=COUNTIF（A:A，「AB*」）

通配符*是表示任意多個字元，放在字元後面表示，該字元後可以有任意多個字元

3）返回A列單元格中5個字元的數量

=COUNTIF（A:A，「？？？？？」）

通配符？表示佔用一個字元位置，五個？表示五位字元長度

4）返回包含文本內容的單元格數量

=COUNTIF（A:A，「*」）

3.區間統計

1）返回包含負值的單元格數量

=COUNTIF（A:A，「<0」）

2）返回不等於0的單元格數量

=COUNTIF（A:A，「<>0」）

3）返回大於5的單元格數量

=COUNTIF（A:A，「>5」）

4）返回大於單元格A1中內容的單元格數量

=COUNTIF（DATA，「>」&A1）

在引用單元格值時，一定要用&鏈接，千萬不要表示為：「>A1」

5）返回>10並且<=15的數量

=SUM（COUNTIF（A1：A10，「>」&{10，15}）*{1，-1}）

{}表示常量數組，數組的值可以在公式內分別進行運算，每次運算返回一個值。*{1，-1}的目的是上前面的計算結果一個為正，一個為負，用sum求和時達到相減的目的。

6）返回>=10並且<=15的數量

=SUM（COUNTIF（A1：A10，{「>=10」，「>15」}）*{1，-1}）

7）返回包含值為3或-3的單元格數量

=SUM（COUNTIF（A1：A10，{10，-3}））

4.引用函數結果統計

1）返回包含當前日期的單元格數量

=COUNTIF（A:A，TODAY）

2）返回大於平均值的單元格數量

=COUNTIF（A:A，「>」&AVERAGE（A:A））

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！