采购工作必备Excel实用技巧大全(收藏)

1.快速复制表格

直接拖动表格,可以移动到另一个位置,如果按ctrl不松拖动表格,可以快速复制出一个表格。

采购工作必备Excel实用技巧大全(收藏)

2.快速翻看工作表

Ctrl+Pgdn/Pgup可以快速翻看工作表。不用一个个点了。

采购工作必备Excel实用技巧大全(收藏)

3.快速输入序号/快速调整列宽

  • 做表时输入序号是必不可少的,但想更快,记住一个规则:

  • Ctrl键不松可以让纯数字自动递增,也可以让含文本+数字中的数字不变。

  • 调整列宽有个小技巧:双击边线可以让列宽自适调整

采购工作必备Excel实用技巧大全(收藏)

4.快速插入空列

需要插入几列就选取几列然后插入

采购工作必备Excel实用技巧大全(收藏)

5.不复制隐藏的行

Alt+选取显示的行,复制-粘贴

采购工作必备Excel实用技巧大全(收藏)

6.两列快速互换

左手按shift键不松,右手按鼠标左键不松拖动列边线,可以快速让2列换位。

采购工作必备Excel实用技巧大全(收藏)

7.一键显示所有表格中公式

Ctrl+~(波浪线)可以切换公式的显示或隐藏,但,这个快捷键常被输入法给占用。所以点击公式选项中的“显示公式”命令最靠谱。

采购工作必备Excel实用技巧大全(收藏)

8.快速选取单元格区域

快速选取区域的技巧:

Ctrl+A选取整个连续的表格

Ctrl+shift+方向键可以选取连续的区域

采购工作必备Excel实用技巧大全(收藏)

9.快速复制公式

如果相邻列是连续的区域,双击公式所在单元格的右下角。如果邻列不连续,可以选取后按Ctrl+D复制。

采购工作必备Excel实用技巧大全(收藏)

Vlookup常用公式

1.一般查找

如下图所示,根据姓名查找职位

采购工作必备Excel实用技巧大全(收藏)

2.从左至右查找

如下图,要求根据姓名查找工号

采购工作必备Excel实用技巧大全(收藏)

3.查找公式复制

在表二中,根据姓名从表中查询对应信息

采购工作必备Excel实用技巧大全(收藏)

4.区间查找

根据左表的提成比率表,在右表中更具销售额在G列查找适用的比率

采购工作必备Excel实用技巧大全(收藏)

5.模糊查找

查找包含AAA名称的产品价格

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

采购工作必备Excel实用技巧大全(收藏)

6.多项查找

从下表中同时查找“李飞”和“南宁”的年龄 ,并返回最小的。

=MIN(VLOOKUP(T(IF(1,{“李飞”,”南宁”})),A2:C6,3,0))

采购工作必备Excel实用技巧大全(收藏)

7.隔任意列求和

隔1列求和

数组公式:大括号是按ctrl+shift+enter后自动产生的,非手工输入

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

采购工作必备Excel实用技巧大全(收藏)

指定列求和

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

公式:

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

注:{2,3,5,6,8,9}把要求和的列数放在大括号内,用逗号分隔。

采购工作必备Excel实用技巧大全(收藏)

8、多条件查找

如下图所示要求,同时根据姓名和工号查找职位。

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

采购工作必备Excel实用技巧大全(收藏)

9.多区域查找

根据不同的表从不同的区域查询

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

采购工作必备Excel实用技巧大全(收藏)

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),”无此人信息”)))))

采购工作必备Excel实用技巧大全(收藏)

方法2:

=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT({“销售”;”服务”;”人事”;”综合”;”财务”}&”!a:a”),A2),{“销售”;”服务”;”人事”;”综合”;”财务”})&”!a:g”),7,0)

采购工作必备Excel实用技巧大全(收藏)

11.一对多查找1

根据产品查找相对应的所有供应商

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

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

采购工作必备Excel实用技巧大全(收藏)

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,)}

采购工作必备Excel实用技巧大全(收藏)

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))


来源:国际CPPM注册采购经理报考,本文观点不代表自营销立场,网址:https://www.zyxiao.com/p/76203

发表评论

登录后才能评论
侵权联系
返回顶部