首页 学习资料网络教程正文

8.快速复制表格直接拖动区域快速选取区域的选取技巧

8.快速复制表格直接拖动区域快速选取区域的选取技巧

专注于采购和供应链学习

行业新闻 | 技术前沿 | 采购信息

更多话题和福利等你“拿”!

1.快速复制表格

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

2.快速翻阅工作

按 Ctrl+Pgdn/Pgup 快速翻阅工作表。不一一。

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

4.快速插入空列

如果您需要插入几列excel提取当前工作表名称,请选择几列并插入

5.不复制隐藏线

按 Alt+;选择显示的行excel提取当前工作表名称,复制粘贴

6.两列之间的快速交换

左手按住shift键,右手按住鼠标左键拖动列边,可以快速改变两列的位置。

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

按 Ctrl+~(波浪号)切换公式的显示或隐藏,但这个快捷键经常被输入法占用。因此,单击公式选项中的“显示公式”命令是最可靠的。

8.快速选择单元格范围

快速选择区域的提示:

按 Ctrl+A 选择整个连续表

按 Ctrl+shift+箭头键选择连续区域

9.快速复制公式

如果相邻列是连续范围,双击公式所在单元格的右下角。如果相邻的列不连续,您可以选择它们并按 Ctrl+D 复制它们。

Vlookup常用公式

1.常规查找

按名称查找工作,如下图所示

2.从左到右搜索

如下图,需要根据名字找到工号

3.查找公式副本

表2中,根据名称从表中查询到对应信息

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.多个工作表查找

求各个部门员工的基本工资,不一定在哪个表。

方法

=IFERROR(VLOOKUP(A2,Service!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,Sales!A:G,7,0) “没有关于此人的信息”)))))

方法二:

=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT({"Sales";"Services";"Personnel";"General";"Finance"}&"!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,)}

求和函数

1.设置求和公式

传统的方法是设置一个 SUM 公式,然后复制它。其实只要按CTRL+G定位空单元格,然后点击∑(或者按住“ALT”和“=”)

2.设置合计公式

设置合计公式时,不需要使用小计1+small2+small3...,设置公式即可:

=SUM(B2:B14)/2

3.多表求和

示例:将第 1 到第 31 个 B2 单元格的总和相加。

=SUM(1st:31st! B2)

4.多重条件求和

虽然不是 SUM 函数的强项,但可以给小攻击

{=SUM((a1:a100="A 产品")b1:b100)}

5.合并单元格总和

总细胞的总和是一个难点。如果公式用一般方法很复杂,如果用sum函数的逆方法会变得更简单

=SUM(C2:C13)-SUM(D3:D13)

输入公式时不能拖拽复制,需要选择D2:D13,先在D3中输入公式,然后将光标放在公式末尾,按ctrl+Enter批量输入

6.任意多表求和

如果对N个非相邻表的同一个单元格求和,则不能直接使用SUM,而SUM(SUM(结构,这里相当于SUM(N,提取三维引用单元格的和))

=SUM(SUM(间接({1,2,3}&"!A1")))

=SUM(N(间接({1,2,3}&"!A1")))

计数函数

如果评价工作中最常用的函数是which,vlookup函数是公认的NO.1函数,但只能用于lookup。Excel中有一个函数比它更有用,是Excel中最重要的函数。这就是 Countif 函数

1.准确统计

1)返回 A 列中包含值 12 的单元格的数量

=COUNTIF (A:A,12)

2)返回部门为“采购部门”的A列中的数量

=COUNTIF(A:A,"采购部")

公式中的字符需要用双引号括起来

3)返回包含值的单元格数;逻辑

=COUNTIF(A:A,真)

4)返回 A1:A10 中空单元格的数量

=COUNTIF(A1:A10, "=")

5)返回A列非空单元格的个数(相当于counta函数)

=COUNTIF(A1:A10,"")

6)返回A1:A10区,真空数+假人

=COUNTIF(A1:A10,"")

7)返回A1:A10区域内所有单元格的个数(非空+空)

=COUNTIF(数据, """")

8)返回 A1:A10 虚拟单元的数量

=COUNTIF(A2:A32,"")-COUNTIF(A2:A32,"=")

真空+真空-真空=真空

9)统计A列中ID号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,"

2)返回不等于0的单元格数

=COUNTIF(A:A, "0")

3)返回大于5的单元格数

=COUNTIF(A:A, ">5")

4)返回大于单元格A1内容的单元格数

=COUNTIF(DATA, ">" &A1)

引用单元格值时,一定要用&链接,千万不要表示为:">A1"

5)返回 >10 和 "&{10, 15})*{1,-1})

{}表示一个常量数组,数组的值可以在公式中单独操作,每次操作返回一个值。*{1,-1}的目的是前面的计算结果一个为正一个为负,用sum求和时达到减法的目的。

6)返回 >=10and=10",">15"})*{1,-1})

7)返回包含值 3 或 -3 的单元格数

=SUM(COUNTIF(A1:A10,{10,-3}))

4.参考函数结果统计

1)返回包含当前日期的单元格数

=COUNTIF(A:A, 今天())

2)返回大于平均值的单元格数

随机文章