Excel 技巧总结·笔记·杂记

Excel空间划分

工作薄(Bù)是整个Excel文件，它由一个或多个工作表(sheet)组成，工作表有很多个单元格组成，相邻的单元格就可以组成普通区域，可以将普通区域变成特殊区域——表格，表格有标题区和内容区。

调整列宽

移动光标到标题栏间隔线处，十字箭头变成左右箭头，按住拖动或者双击自动适应内容。

快速填充单元格

移动光标到单元格右下角，出现黑色十字光标 >> 按住拖动来选中要填充的单元格 >> 在最后一个选中的单元格右下角会出现一个工具图标 >> 移动光标到此图标上，弹出填充选项：复制单元格、填充序列、仅填充格式、不带格式填充、快速填充

快速填充单元格可以实现数值的递增填充（一般用来实现有序编号），还可以实现公式的复制填充。

单元格数字格式

这个设置决定了以什么样的形式显示数值。

1. 常规：普通数值。
2. 数值：设置保留的小数位数和千分位分隔符,显示。
3. 货币：设置小数位数，货币符号以及负数的加括号和标红。
4. 会计专用：对一列数值进行货币符号和小数点对齐。
5. 日期：设置日期类型和区域。
6. 时间：设置时间类型和区域。
7. 百分比：以百分比形式（除100）显示单元格数值，可设置小数位数。
8. 分数：以分数形式显示数值。
9. 科学记数：$xE+n$表示$x\times 10^n$，x为绝对值小于10的实数。
10. 文本：表现为文本。
11. 特殊：如邮编和中文的大小写数字。
12. 自定义。

斜线表头

那么怎么使得文字分在斜线的两侧呢？我使用了加空格的方法。

固定首行和首列

行列的移动和复制

选中要移动的行和列里的单元格，将光标移动到选中边框位置，拖动它就是移动，按住Ctrl再移动就是复制。

快捷键

• Alt + Enter：单元格内强制换行。
• Alt + = ：快速求和。

全角和半角

（此为补充内容）

全角指一个字符占用两个标准英文字符位置；半角指一个字符占用一个标准英文字符位置。

一个标准英文字符位置通常是 1 字节。

全角字符是为中文、日文和韩文等文字而设计。

eg：全角的１和半角的1。

查找和替换

公式Formula、值Value和批注Comment的区别

公式是一组用于执行计算的指令或表达式，通常以等号（=）开头，可以包含函数、运算符、单元格引用等。用于动态计算数据（如求和、求平均、逻辑判断等）。可以根据输入数据自动更新结果。

=SUM(A1:A10)      // 计算 A1 到 A10 的和
=IF(B2>90, "优秀", "合格")  // 条件判断

值是通过公式计算得出的结果（公式也是值），或直接手动输入的数据（数字、文本、日期等）。

批注是对单元格内容的附加说明或注释，通常以浮动文本框形式显示，不参与计算。

shift + F2 创建批注。

将一个单元格变为下拉筛选

①启用表格标题栏筛选，点击表格随便一个单元格，再点击数据->筛选：

怎么设置某行为表格标题行呢？选中某单元格区域，点击表设计，勾选标题行，这将确定区域首行为标题行。

②启用表格内容单元格筛选，就是为某单元格提供有限个可选值。选中列->点击数据->点击数据验证->设置允许为序列，取消勾选忽略空值->来源中给出值，以逗号分隔，或引用外部工作表的数据，如=<工作表名称>。

单击单元格下方弹出提示

1. 选择目标单元格：选中需要提示的单元格或区域。
2. 设置数据验证：
   • 点击菜单栏的 数据 → 数据验证。
   • 在“输入信息”选项卡中填写标题和提示信息。

单击单元格跳转到外工作表

右键单击 → 链接

为单元格或表格区域定义名称

1. 选择目标单元格或目标区域
2. 点击菜单栏的 公式 → 定义名称
   
3. 设置名称、范围、批注、引用位置
   • 范围可以选工作薄或者某个工作表
   • 引用位置格式：<表名><引用区域>
4. 该名称可用于公式中

让同一个工作表的两个表格筛选时互不影响

得保证不同表格不能有位于同一行的行。

让创建表格时表格名称的计数正常衔接

暂未发现解决方案

表格行列转置

对于普通区域的表格转置，直接复制，粘贴选项中有转置按钮。

对于标准表格区域的复制，在粘贴选项中并无转置按钮。方法尝试：

• 使用 TRANSPOSE 函数转置。在目标区域左上角输入公式：=TRANSPOSE(原数据区域)（如 =TRANSPOSE(A1:D3)）。旧版Excel按 Ctrl+Shift+Enter 组合键生成数组公式。Excel 365/2021+ 直接按回车，公式结果会自动填充到整个区域。
• 使用Power Query的转置功能。选中数据区域 → 点击菜单栏 “数据”→ “从表格/区域”，- 在Power Query编辑器中，点击 “转换” → “转置”。点击 “文件” → “关闭并上载”，转置后的数据将生成在新工作表。

但是上述两个方法均无法保持样式和公式。

表格边框

绘制边框和擦除边框模式，按ESC退出。

插入行或列

选中一行或一个单元格，右键插入行，再使用F4重复插入操作。

选中多行可以插入多行。

插入列与插入行操作类似。

数据的复制、移动和替换

选中区域，移动光标到边框处，出现带箭头的黑色十字光标：按住Ctrl和鼠标左键拖动数据区域到指定位置粘贴，这是复制粘贴；不按Ctrl会剪切该区域，这个是移动；按住Shift移动表格一行m到另一行n上会替换n。

打印表格

文件 >> 打印或者Ctrl + P

公式和函数(formulas and functions)

公式和函数 - Microsoft 支持

例子：

Excel 使用字母排序列，使用数字排序行。

E1 表示 E 列 的第 1 行的单元格。

基础

Excel 的公式和函数可以作为你的计算器来实现加减乘除（使用操作符+ - * /）。

公式能包括单元格引用（cell reference，如A2）、单元格引用范围（如A1:B2表示一个顶点为A1和B2的正方形、B:B 表示B列）、操作符、函数以及常量。

函数能执行数学计算、查值甚至是计算日期和时间。有函数，就会设计到参数，参数可以是单元格引用、常量甚至是函数等。

带函数的公式以=开头。

常量不建议在公式里使用，因为它不可见。当然可以手动查看，选中单元格按F2或者直接Ctrl+~。

SUM 求和

(1)=SUM(number1, [number2], ...)

eg：

=SUM(A1:A10)：计算 A1 到 A10 的和。

参数输入可以手动打，当然也可以在输入SUM(后直接使用鼠标选定。

[]意味着可以省略，后同。

=SUM(A1:A3, B1:B1)：计算 A1 到 A3，加上 B1 的和。

=SUM(A1:A3, B:B)：计算 A1 到 A3，加上 B 列所有数的和。

可以手动设置求值范围，也可以在输入SUM(后直接使用鼠标选定。

(2)=SUMIF(range, criteria, [sum_range])：条件求和。

range, criteria 为一组条件，[sum_range] 为求和范围。

eg：

=SUMIF(B1:B10, "优秀", A1:A10)，计算 B1 到 B10 中 “优秀” 对应 A1 到 A10 的和。

(3)SUMIFS()：多重条件求和。

eg：

=SUMIFS(A1:A10, B1:B10, "优秀", C1:C10, "合格")，计算 B1 到 B10 中 “优秀” 且 C1 到 C10 中 “合格” 对应 A1 到 A10 的和。

平均值

(1)=AVERAGE(number1, [number2], ...)

eg：=AVERAGE(A1:A10)，计算 A1 到 A10 的平均值。

(2)AVERAGEIF：条件求平均值。

eg：=AVERAGEIF(B1:B10, "优秀", A1:A10)，计算 B1 到 B10 中 “优秀” 在 A1 到 A10 对应值的平均值。

(3)AVERAGEIFS：多重条件求平均值。

eg：=AVERAGEIFS(A1:A10, B1:B10, "优秀", C1:C10, "合格")，计算 B1 到 B10 中 “优秀” 且 C1 到 C10 中 “合格” 对应 A1 到 A10 的平均值。

(4)AVERAGEA：相比较AVERAGE，它可以处理逻辑值和文本形式的数值。

最大最小值

(1)=MIN(number1, [number2], ...)

(2)=MAX(number1, [number2], ...)

计数

(1)=COUNT(number1, [number2],...): 统计值为数字、日期或文本形式数字的单元格数量。

(2)=COUNTIF(range, criteria)：条件计数。

(3)=COUNTIFS(range1, criteria1, [range2], [criteria2],...)：多重条件计数。

(4)=COUNTA(value1, [value2], ...)：统计所有非空单元格数量。

日期和时间

(1)=TODAY()：返回当前日期。

(2)=NOW()：返回当前日期和时间。

(3)=DATE(year, month, day)：返回指定的日期。

合并文本和数字

(1)=TEXT(number, format_text)：将数字转换为文本。

(2)=VALUE(text)：将文本转换为数字。

(3)&：合并文本。

eg:

=TEXT(123, "000")：将数字 123 转换为文本，格式为 “000”，结果为 “123”。

=VALUE("123")：将文本 “123” 转换为数字，结果为 123。

="Hello" & "World"：将文本 “Hello” 和 “World” 合并，结果为 “HelloWorld”。

=A10 & " " & B10：将 A10 和 B10 合并为一个文本，并在中间添加一个空格。

条件判断

(1)=IF(logical_test, value_if_true, value_if_false)：条件判断。

eg：

=IF(A1>90, "优秀", "合格")，如果 A1 的值大于 90，则返回 “优秀”，否则返回 “合格”。

(2)=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)：多重条件判断。

eg：

=IFS(A1>90, "优秀", A1<60, "不合格", TRUE, "合格")，如果 A1 的值大于 90，则返回 “优秀”，如果小于 60，则返回 “不合格”，否则返回 “合格”。

(3)=SWITCH(value, value1, result1, [value2, result2], ...)：如果value与value1相等，则返回result1，如果value与value2相等，则返回result2，以此类推。

eg：

=SWITCH(A1, "优秀", "优秀", "合格", "合格", "不合格", "不合格")，如果 A1 的值等于 “优秀”，则返回 “优秀”，如果等于 “合格”，则返回 “合格”，否则返回 “不合格”。

(4)IFERROR(value, value_if_error)：如果 value 是错误值，则返回 value_if_error，否则返回 value。

VLOOKUP

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

过程:

1. 找到 lookup_value 在 table_array 中对应的行。
2. 返回对应行的 col_index_num 列的值。
3. 如果 range_lookup 为 TRUE，则查找范围是整个 table_array，否则查找范围是当前行。

使用函数向导（插入函数）

1.选择要插入函数的单元格。

2.公式 -> 插入函数

3.搜索函数

4.输入参数，点击确定。

错误检查

当查询的值不存在时，VLOOKUP 会返回错误值。可以使用 IFERROR 函数来处理错误值。

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