Excel常用技巧

已于 2024-04-11 01:19:11 修改
阅读量2.5k 收藏 14
点赞数 24
分类专栏： 游戏基础知识 文章标签： excel
于 2024-04-07 00:07:55 首次发布
本文为博主原创文章，未经博主允许禁止转载。如有问题，欢迎指正。
本文链接：https://blog.youkuaiyun.com/weixin_42717928/article/details/137412985
版权

个人笔记（整理不易，有帮助，收藏+点赞+评论，爱你们！！！你的支持是我写作的动力）

笔记目录：学习笔记目录_pytest和unittest、airtest_weixin_42717928的博客-优快云博客

个人随笔：工作总结随笔_8、以前工作中都接触过哪些类型的测试文档-优快云博客

目录

一：绝对引用和相对引用

二：锁定

（1）行锁定

（2）列锁定

三：查找&匹配

（1）列查找：vlookup(查找值,索引范围,列数,匹配规则）

1）用精确查找

2）近似查找

（2）行查找：hlookup(查找值,索引范围,列数,匹配规则）

（3）列引用：column(引用单元格）

（4）列引用：row(引用单元格）

（5）查找匹配：MATCH(查找值,索引行or列,匹配规则)

1）精确查找

2）近似查找

3）vlookup搭配match，返回列数索引

4）hlookup搭配match，返回行数索引

（6）值引用：INDEX(索引范围,匹配行数,匹配列数)

四：计算相关

（1）乘积求和

SUMPRODUCT(数组1,数组2,…）

（2）条件求和

SUMIFS(求和范围，条件1范围，条件1，条件2范围，条件2,...）

（3）条件计数

COUNTIFS(计数范围1，条件1，计数范围2，条件2,...）

（4）矩阵乘积

MMULT(列数组，行数组）

（5）取整相关

1）ROUND(number,num_digits)

2）CEILING(舍入值，舍入倍数）

3)FLOOR(舍入值，舍入倍数）

五：配置相关

（1）SUBSTITUTE(要替换的单元格,需替换旧文本,新文本）

（2）CONCATENATE(文本1,文本2,…）

（3）SUBSTITUTE+CONCATENATE组合使用

一：绝对引用和相对引用

（1）excel中$A$1的意思是“绝对引用A1单元格”，即A1单元格的行和列都被绝对引用，不会因向其他位置的填充变化，而改变行列号

在A列往右边插入一行，可以看到这里都是变化的

举个例子，这里对B列和C列进行乘积和，如果这样写$B$2（绝对）*C2（相对），则下拉填充的时候。左边的都是使用$B$2（绝对）

二：锁定

等级玩家数钻石数
110100
220200
330300

（1）行锁定

K$61
等级玩家数钻石数
110100
120200
130300

（2）列锁定

$B6
等级玩家数钻石数
111
220200
330300

三：查找&匹配

（1）列查找：vlookup(查找值,索引范围,列数,匹配规则）

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

参数

简单说明

输入数据类型

lookup_value

要查找的值

数值、引用或文本字符串

table_array

要查找的区域

数据表区域

col_index_num

返回数据在查找区域的第几列数

正整数

range_lookup

精确匹配/近似匹配

FALSE（0、空格或不填（但是要有','占位)）/TRUE（1或不填(无逗号占位)）

1）用精确查找

备注：这里写错了，不是大本，是等级

其中：

第一个参数，填写需要被查找的数字，我这里就查找1，即K82

第二个参数，这里输入我们要返回数据的区域(绝对引用)，即“$K$76:$M$79”；查找时只会用K82与$K列的内容匹配

第三个参数，“玩家数”是区域的第2列，所以这里输入“2”

（注意：这里的列数不是EXCEL默认的列数，而是查找范围的第几列）

第四个参数，因为我们要精确查找等级，所以输入“FALSE”或者“0”。模糊查找输入“TRUE”或“1”

2）近似查找

如果找不到精确匹配值，则返回小于 lookup_value 的最大数值

比如这里，找不到1.5，就找小于1.5的1

（2）行查找：hlookup(查找值,索引范围,列数,匹配规则）

（3）列引用：column(引用单元格）

可以搭配vlookup使用，返回列数索引

通常在vlookup索引范围很大，数第几列数到你晕的时候，用这个

先用column得到玩家数在哪一列，然后减去10即可

（4）列引用：row(引用单元格）

可以搭配hlookup使用，返回行数索引

通常在hlookup索引范围很大，数第几行数到你晕的时候，用这个

（5）查找匹配：MATCH(查找值,索引行or列,匹配规则)

MATCH(lookup_value, lookup_array, [match_type])

  • Match_type：1 或省略，表示MATCH 函数会查找小于或等于 lookup_value 的最大值
  • Match_type：0，表示MATCH 函数会查找等于 lookup_value 的第一个值
  • Match_type：-1，表示MATCH 函数会查找大于或等于 lookup_value 的最小值

1）精确查找

2）近似查找

3）vlookup搭配match，返回列数索引

通常在vlookup索引范围内你有确切的匹配目标时用（日常配表中最实用的公式）

比如找等级是2的玩家数

4）hlookup搭配match，返回行数索引

通常在hlookup索引范围内你有确切的匹配目标时用

找非R对应的总元宝数

（6）值引用：INDEX(索引范围,匹配行数,匹配列数)

vlookup、hlookup的孪生兄弟，通常在lookup不能用的情况下作他俩的替代

vlookup、hlookup是从左到右、从上到下匹配，而index可以实现从右到左，从下到上的匹配

四：计算相关

（1）乘积求和

SUMPRODUCT(数组1,数组2,…）

序号玩家数钻石数元宝数乘积
110100100100000
220200200800000
3303003002700000
3600000

要计算三组序号的乘积和，选择10后，往下拖动到30，然后逗号，以此类推

（2）条件求和

SUMIFS(求和范围，条件1范围，条件1，条件2范围，条件2,...）

钻石数和元宝数都>100的玩家数总和
等级玩家数钻石数元宝数
110100100
220200200
330300300
SUMIFS(K41:K43,L41:L43,">100",M41:M43,">100")
50

这个函数，也可以用于查询

当查找值在索引范围内不是唯一时，是用不了lookup函数的

比如这个表格，当你用等级=1来索引，会有2个匹配项

等级玩家数钻石数元宝数
110100100
120200200
330300300

如果你有额外的可用匹配条件，且条件重合后的值确认唯一，可用sumifs来实现查找

求等级=1且钻石数=100的玩家数
等级玩家数钻石数元宝数
110100100
120200200
330300300
等级钻石数玩家数量
110010
SUMIFS(R40:R42,Q40:Q42,Q45,S40:S42,R45)

（3）条件计数

COUNTIFS(计数范围1，条件1，计数范围2，条件2,...）

钻石数和元宝数都>100的数据有几条
等级玩家数钻石数元宝数
110100100
220200200
330300300
COUNTIFS(L41:L43,">100",M41:M43,">100")
2

（4）矩阵乘积

MMULT(列数组，行数组）

当你需要乘积求和,但分别是一行和一列时，是用不了sumproduct函数的，此时用mmult替代

（5）取整相关

1）ROUND(number,num_digits)

round函数就是返回一个数值，该数值是按照指定的小数位数进行四舍五入运算的结果

Number：需要进行四舍五入的数字。

Num_digits：指定的位数，按此位数进行四舍五入

2）CEILING(舍入值，舍入倍数）

向上舍入：将 17向上舍入到最接近的 4的倍数

舍入数倍数舍入后值
17420

3)FLOOR(舍入值，舍入倍数）

向下舍入：将 17向下舍入到最接近的 4的倍数

五：配置相关

（1）SUBSTITUTE(要替换的单元格,需替换旧文本,新文本）

当有固定的配置格式时，用它来做批量替换

替换前替换后
你哈哈哈我哈哈哈
你嘿嘿嘿我嘿嘿嘿
你呵呵呵我呵呵呵

例子：当你有个配置

配置模板{"~":#}
道具id1数量1道具id2数量2
6元coin20speedup1h10
30元coin30speedup3h20
68元coin60speedup5h30

将模板中的~替换成coin

再将#替换成对应的数值

结果

道具1配置道具2配置
6元{"coin":20}{"speedup1h":10}
30元{"coin":30}{"speedup3h":20}
68元{"coin":60}{"speedup5h":30}

（2）CONCATENATE(文本1,文本2,…）

“&”的加强版，在有很多项要拼接的时候用

比如现在有一个表格

道具1配置道具2配置道具3配置
6元{"coin":20}{"speedup1h":10}{"hero1":1}
30元{"coin":30}{"speedup3h":20}{"hero2":2}
68元{"coin":60}{"speedup5h":30}{"hero3":3}

将道具拼接起来

道具1~3配置
6元{"coin":20}{"speedup1h":10}{"hero1":1}
30元{"coin":30}{"speedup3h":20}{"hero2":2}
68元{"coin":60}{"speedup5h":30}{"hero3":3}

（3）SUBSTITUTE+CONCATENATE组合使用

SUBSTITUTE(CONCATENATE(K16,L16,M16),"}{",",")

先拼接，比如这样后{"coin":20}{"speedup1h":10}{"hero1":1}，再替换，将}{替换成，

道具1~3配置
6元{"coin":20,"speedup1h":10,"hero1":1}
30元{"coin":30,"speedup3h":20,"hero2":2}
68元{"coin":60,"speedup5h":30,"hero3":3}

fastexcel_使用FastExcel工具修复慢的Excel文件
culiao2169的博客
08-20 720
fastexcel 使用FastExcel工具修复慢的Excel文件 (Fix Slow Excel Files with FastExcel Tools) If we're sitting at home, with little to do but eat, watch TV and sleep, we might get a bit slow and bloated, which isn't...
【Excel经验】VLOOKUP函数做垂直查找vs“绝对引用“实现2组数据的对比
自然
08-11 2838
vloopup函数垂直查询实现对比分析数据
vlookup的绝对引用和按列选中（Excel入门）
蛋肠加蛋不加香菜的博客
10-23 4824
vlookup的简易应用
excel进阶常用函数（查找、排序、定位）
一名数据分析师
03-13 3048
函数说明：COUNTIFS(分组1第一个值固定:分组1所在值,分组1所在值,分组2第一个值固定:分组2所在值,分组2所在值,...,分组n第一个值固定:分组n所在值,分组n所在值)案例：单月表现值最好的人是谁，INDEX(A2:A7,MATCH(MAX($C$2:$C$7),$C$2:$C$7,))案例：单月表现值最好的都有谁，FILTER($A$2:$A$7,$C$2:$C$7=MAX($C$2:$C$7))函数说明：INDEX(反馈值范围,MATCH(求最大/最小函数(值范围),值范围,))
Excel如何快速的定位到某一列和快速知道当前列
qq_46089163的博客
08-24 2986
由于某一次做excel数据太大需要快速知道某一列是多少列和快速定位到某一列对此写了这个。找到某个单元格进行输入，替换对应列数即可。之后进入excel查找输入YJ1即可。点击COLUMN，点击确定即可。点击单元格，公式，查找与引用。
查找Excel包含关键字的行（の几种简单快速方法）
TravelLight92的博客
03-01 2407
情况：数据在xlsx的sheet1中且量比较大，比如几十万行几百列；想查找一个关键字,比如"全网首发"Excel实现和python实现；数据大概这样子，这纯粹是我瞎编的演示数据，商品全称、简称、特点列会造成查找重复：1.如果知道关键字在哪一行比如关键字在这一列！
EXCEL如何实现多行查找、匹配，如何让一张表按另一张表的的顺序排序
weixin_54246744的博客
03-19 3808
EXCEL如何实现多行查找、匹配，如何让一张表按另一张表的的顺序排序
绩效加速器EXCEL常用技巧5例2022优秀文档.ppt
11-14
以下五个技巧是从"绩效加速器EXCEL常用技巧5例2022优秀文档.ppt"中提取的关键知识点： 1. **单元格内换行编辑** 在Excel单元格中，如果需要在同一单元格内换行显示内容，可以使用快捷键`Alt + Enter`。例如，整理...
2021年Excel常用技巧归纳.doc
12-13
本篇文章将总结2021年Excel的一些常用技巧，帮助用户提高工作效率。 一、基本操作技巧 1. 迅速选中所有工作表：只需按下Ctrl+Shift+PageDown键，可以同时选中所有工作表。 2. 快速启动Excel：可以通过创建桌面快捷...
excel常用技巧课件完整版资料.ppt
11-14
本课程旨在帮助用户从新手到专家逐步提升Excel技能，掌握常用的Excel操作、函数运用、图表创建以及数据分析和VBA编程。 首先，了解Excel的目的至关重要。Excel不仅用于存储和配置大量的统计数据，而且能通过内置的...
EXCEL常用技巧及操作.pptx
07-30
【Excel 2007 常用技巧】 在Excel 2007中，用户可以体验到一系列方便快捷的技巧，这些技巧极大地提升了工作效率，使数据管理和分析变得更加容易。 **Tip 1：灵活添加页眉和页脚** 在Excel 2007中，添加页眉和页脚...
Excel使用VLOOKUP时注意绝对引用和相对引用区别
xiaozaq的博客
01-03 1960
一般我们使用VLOOKUP时，函数中参数选择范围一般是希望使用绝对引用，但是如果直接通过鼠标拖动选取的范围默认是相对引用。这样在复制粘贴到下一个单元格会导致选择范围变小，部分数据无法通过VLOOKUP匹配上。所以此时需要手动修改，将相对引用改为绝对引用。由于使用Excel比较少，每次使用VLOOKUP都遇到该问题，只记得有这么个东西，但就是忘记要怎么处理了。A1:K31 变为了 Sheet4!实际上我们是不希望该范围改变的。所以此时我们要使用绝对引用。
【css】相对定位，绝对定位和固定定位
小秀的博客
05-20 1464
相对定位不会从文档流中移除元素，因此元素原本所占的空间依然存在。通过将元素设置为固定定位，可以将它固定在页面的某个位置上，例如固定在屏幕的顶部或底部。进行定位（如果没有已定位的祖先元素，则相对于文档的初始位置进行定位）。需要注意的是，定位的效果还会受到其他 CSS 属性和元素的影响，比如盒模型、浮动和 z-index 属性等。绝对定位会将元素从文档流中移除，并且它不会对其他元素产生任何影响。总的来说，父级和子级之间的定位方式会影响它们的相对位置，而同级元素之间的定位方式相互独立。固定定位是指元素相对于。
绝对引用计算机几级,计算机一级必考的14个Excel函数，让我来告诉你
weixin_33773996的博客
06-15 4155
有同学问：计算机一级Excel要考哪些函数啊？，下图的14个函数就是一级必考的。001.sum求和函数定义：对指定参数进行求和。书写规则：=sum(数据区域)002.average求平均函数定义：对指定参数进行求平均值。书写规则：=average(数据区域)003.max求最大值函数定义：求指定区域中的最大值。书写规则：=max(数据区域)004.min求最小值函数定义：求指定区域中的最小值。书写...
Speedoffice（excel）如何利用AVERAGE函数求平均数
F915277的博客
10-08 437
Speedoffice（excel）如何利用AVERAGE函数求平均数
excel查找并返回多行数据
weixin_33690963的博客
03-22 6932
表一 A B C张三 15 25李四 11 24王二 50 80张三 10 20表二 A B C 在表二中输入“张三”自动显示表一中的2行数据张三 15 2510 20 为叙述方便，假设两个表如下： Sheet1: Sheet2: A B C A B C D 1 2 张三 15 ...
Excel 查找某列中的数值有没有在另一列中出现
清风古韵
02-11 9868
=IFERROR(VLOOKUP(B106,D:D,1,0),"没出现过")
Excel 查找某列中的数据在另一列是否存在并输出其他列的数据
热门推荐
amyzhang1234的博客
10-31 1万+
最近在操作Excel文件数据导入数据库时，经常需要检查Excel中哪些数据数据库中已经存在，哪些不存在，然后再将不存在数据库中的Excel数据导入；在此过程中，经常需要操作Excel中的数据，所以、也就使用了个别Excel函数，现在介绍各函数如下： VLOOKUP函数： 定义： VLOOKUP(lookup_value, table_array,col_index_num, [r...
excel常用技巧
最新发布
03-08
<think>好的，用户想要获取Excel的常用技巧和快捷方式。首先，我需要从系统提供的引用中提取有用的信息。引用[1]提到有100多个快捷键，适合所有版本，用户可能想知道最常用的一些。比如Ctrl+C、Ctrl+V这些基本操作，以及Ctrl+Shift+L用于筛选，这些都很实用。另外，引用[2]提到了合并单元格内容使用公式，比如=B1&C1&D1，还有计算年龄的公式=(TODAY()-A2)/365，这些对处理数据应该有帮助。 接下来，我需要整理这些信息，确保结构清晰。可能需要分点列出快捷键和公式技巧。同时，用户可能对如何高效处理数据、提高输入速度等感兴趣。例如，快速填充功能Ctrl+E，或者快速跳转单元格的快捷键，这些都能提升效率。 另外，需要注意用户可能不太熟悉复杂的公式，所以需要简单解释每个技巧的用途。比如，合并单元格的公式，可能还要提醒他们调整单元格引用。计算年龄的公式可能需要四舍五入，用ROUND函数会更准确。 还要考虑用户的实际应用场景，比如数据整理、日常办公，所以提供相关的技巧会更实用。例如，快速插入当前日期和时间的快捷键，或者冻结窗格方便查看大数据表。 最后，生成相关问题时要围绕Excel的使用，可能涉及高级功能、数据清洗或模板创建等，帮助用户进一步学习。需要确保问题有针对性，能够引导用户深入探索Excel的功能。</think>### Excel常用技巧与快捷方式指南 #### 一、基础操作快捷方式 1. **数据快速处理** - 复制/粘贴：$Ctrl+C$ 和 $Ctrl+V$（兼容大多数软件操作）[^1] - 撤销/恢复：$Ctrl+Z$ 和 $Ctrl+Y$ - 快速填充：输入示例后按 $Ctrl+E$（自动识别填充模式） - 筛选数据：$Ctrl+Shift+L$（快速启用或关闭筛选）[^1] 2. **单元格跳转与选择** - 跳转到表格末尾：$Ctrl+方向键$（如$Ctrl+\downarrow$跳至最后一行） - 全选连续数据区域：$Ctrl+Shift+方向键$ - 选中整行/整列：$Shift+空格$ 或 $Ctrl+空格$ #### 二、公式与函数技巧 1. **合并多列内容** 在新单元格输入公式：$$=B1\&C1\&D1$$ 可通过添加分隔符优化：$$=B1\&"-"\&C1\&"-"\&D1$$[^2] 2. **计算年龄与日期差** - 计算年龄：$$=(TODAY()-A2)/365$$（需用$ROUND$函数四舍五入） - 计算工作日差：$$=NETWORKDAYS(开始日期,结束日期)$$ 3. **快速求和与统计** - 自动求和：选中区域后按 $Alt+=$ - 条件统计：$$=COUNTIF(范围,条件)$$ 或 $$=SUMIF(范围,条件,求和列)$$ #### 三、高效输入与格式调整 1. **快速输入当前时间/日期** - 当前日期：$Ctrl+;$ - 当前时间：$Ctrl+Shift+;$ 2. **格式统一化** - 清除格式：$Ctrl+\backslash$ - 调整数字格式：$Ctrl+1$（打开格式设置窗口） 3. **冻结窗格** 选中需固定的行/列下方单元格 → 点击「视图」→「冻结窗格」 **应用场景**：查看大型表格时固定标题行。 #### 四、数据清洗与整理 1. **删除重复值** 选中数据区域 →「数据」→「删除重复值」 **注意**：需提前备份原始数据。 2. **分列功能** 使用「数据」→「分列」将一列文本按分隔符拆分为多列（如拆分姓名与电话）。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

小chen小陈

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值