# 常用技巧
# 移动和选择
Key | Meaning |
---|---|
TAB |
跳到本行下一个单元格 |
SHIFT TAB |
跳到本行上一个单元格 |
ENTER |
跳到本列下一个单元格 |
SHIFT ENTER |
跳到本列上一个单元格 |
CTRL + 方向键 |
跳到指定方向的边界 |
SHIFT + 方向键 |
选择指定方向一个单元格 |
CTRL + SHIFT + 方向键 |
选择到指定方向边界 |
# 拖动
鼠标左键栏位边缘呈现十字箭头拖动。
- 拖动复制:CTRL+ 鼠标左键十字箭头。
- 拖动插入:SHIFT + 鼠标左键十字箭头。
# 调整栏宽行高
鼠标放至栏位分隔线处出现 <-|->
-
调整单个栏宽至内容宽度:
<-|->
单击鼠标左键。 -
调整所有栏宽至内容宽度:选择所有栏,
<-|->
单击鼠标左键。如果栏宽没有调整到内容的宽度,需要注意是否开启了自动换行。 -
调整所有栏宽至相同宽度:选择所有栏,
<-|->
鼠标左键拖动。
# 快速插入多个空行
- 右键插入空行,然后 F4 重复上次操作。
- 选择多行,然后右键插入。
# 重复填充
选择需要重复填充的源和待填充的行, 执行 CTRL + D
。
Initial | Result |
---|---|
![]() |
![]() |
# 跨行复制
按住 CTRL
,鼠标选择要复制的行,然后 CTRL + C
复制。
# 单元格内换行
ALT + ENTER
# 批注
右键 => 插入批注 => 右键批注设置格式
# F4 相对应用变绝对引用
A1 => $A$1
# F9 刷新公式
# 从下拉列表中选择
鼠标右键 => 从下拉列表中选择

# 选择性粘贴 转置
复制数据 => 选择性粘贴 => 勾选转置
# 选择性粘贴 运算
所有英文成绩加 5
复制数据 5 => 选择性粘贴 => 选择需要运算区域 => 勾选运算 加
# 模拟分析
已知国语,数学,英文成绩,计算英文达到多少平均值才能达到 60
数据 => 模拟分析 => 单变量求解


# 表格对角线
-
绘图边框
选择绘图边框,沿着对角线绘制
-
右键单元格格式 => 边框
# 向右补充空格到指定长度
LEFT("AAAA"&REPT(" ", 5), 5)
=> "AAAA "
# 数据分列
数据 => 分列
- 先根据空格分列
- 姓名根据宽度分为姓和名


案例:将 students.txt 转换为 excel 格式,并且 001
保留原有的格式。
1 | id|name|age |
-
选择待分列的列 (A 列),点击数据 -> 分列,合适的文件类型中选择分隔符号。
-
选择分隔符号,可以勾选其他,手动填写分隔符号,这里使用
|
。

- 设置列的数据类型,这里将 id,name 设置为文本格式,可以 Shift 点选,需要选择所有列使用 CTRL + A.

- 文件另存为 xlsx.

# 打印预览
默认 Excel 打印不会显示网格綫,勾选打印网格綫,会打印默认的虚綫格式

如果需要其它类型网格綫使用网格綫工具

# 打印设定
视图 => 分页预览 调整分页符到合适的位置

页面布局 => 打印标题 进行页眉页脚,顶端标题行设置



打印预览

# 冻结窗格,分隔视窗
视图 => 冻结窗格
视图 => 分隔视窗
行,列同时冻结需要选择行和列交叉処

# 创建组
数据 => 创建组

# 复制非隐藏内容
- 开始 => 查找 => 定位

- 选择可见单元格 => 定位 然后复制区域

# 排序
数据 => 排序 可以自订清单 (自定义序列)

# 数据筛选
数据 => 自动筛选
自动筛选也可以根据颜色筛选和排序

# 格式化表格 (表格样式)
-
方式一:开始 => 格式化为表格 (表格样式)
-
方式二:插入 => 表格
格式化的表格可以使用汇总行

# 条件格式
开始 => 条件格式

# 合并计算
数据 => 合并计算
可以将分布在不同表格中的季度数据合并

# 数据透视表
数据 => 数据透视表
# 保护工作表
CTRL + 1 设定公式单元格 锁定 (保护工作表后不可更改) + 隐藏 (保护工作表后公式不可见)

取消保护工作表后可以修改单元格的锁定

审阅 => 允许用户编辑区域
保护工作表之后用户可以通过输入区域密码对允许编辑区域进行修改

审阅 => 保护工作表

# 关闭转换为超链接

# IF

# VLOOKUP
VLOOKUP(查找值, 数据表, 列序数, [匹配条件])
成绩: =VLOOKUP(J3,$M$2:$N$7,2,TRUE)
TRUE 表示模糊匹配

姓名: =VLOOKUP($C$3,$E$2:$K$12,2,FALSE)
总平均: =VLOOKUP($C$3,$E$2:$K$12,6,FALSE)
成绩: =VLOOKUP($C$3,$E$2:$K$12,7,FALSE)
FALSE 代表精确匹配,7 代表取第七列数据(从 1 开始)

# HLOOKUP

# IFERROR
姓名 IF(C3="","",IFERROR(VLOOKUP($C$3,$E$2:$K$12,2,FALSE),"查无此人"))
如果学号为空,姓名显示为空;如果学号在列表中不存在,显示 查无此人

这里也可以对学号进行数据有效性验证,不在验证范围内的学号提示错误



# COUNTIF、COUNTIFS
COUNTIFS 可以指定多个条件
SUMIFS 需要先指定一个求和区域

# COUNTIF 标识重复

# 名称设定

# INDIRECT

# INDIRECT 实现下拉选单

公式 => 指定 => 根据范围指定名称

定位到餐点名称 数据 => 数据有效性

# 数值格式
CTRL + 1 设置单元格格式 |
---|
# 一个位数的预留位置,无意义的 0 会被省略 82.56 #.# => 82.6 |
?一个位数的预留位置,无意义的 0 会被空格代替 “90” #.? => "90." |
0 强制显示每一个指定的位数,90 #.00 => 90.00 |
@ 单元格内的文字,甲 @“等” => 甲等 |
_ 重复指定的符号直到填满储存格 简介 @_. => 简介… wps not valid? |
, 千分位 1000000 0,“M” => 1M |
_ 以后面的符号的宽度增加留白 |
特殊语法:正值格式;负值格式;零值格式;文本格式 0.00;(0.00);0.00;@ |
0.00 [红色];(0.00)[绿色];0.00 [蓝色];@[黄色] |
# DATEDIFF

# NETWORKDAYS
NETWORKDAYS(开始日期, 终止日期, [指定假期])
NETWORKDAYS.INTL(开始日期, 终止日期, [周末], [指定假期])1

# TODAY、NOW

# RANK.EQ
RANK.EQ (数值,引用区域,[排位方式])
排位方式: 1 模糊排位,0 精确匹配 (默认)

# LEFT、MID、RIGHT

# INDEX、MATCH

# 等第

# 考绩

# RANDBETWEEN

# CHOOSE

# RAND

# RANK

# OR、AND

# SUMPRODUCT


# PARETO CHART
按住 CTRL 键,选择投诉内容、投诉次数、累计百分比 => 图表 => 组合图 => 折线图使用次坐标轴

设置坐标轴相关属性

# ROW 快速填充序号
- 选择填充区域

-
输入函数
ROW()
然后按 Ctrl + Enter.
# 学生成绩表
学号 | 姓名 | 国文 | 数学 | 英文 | 总平均 | 成绩 |
---|---|---|---|---|---|---|
101 | 刘明哲 | 65 | 85 | 72 | 74 | C |
102 | 黄雅婷 | 78 | 30 | 66 | 58 | E |
103 | 蔡宜芳 | 84 | 61 | 50 | 65 | D |
104 | 陈翰松 | 100 | 60 | 59 | 73 | C |
105 | 戴育如 | 98 | 74 | 80 | 84 | B |
106 | 汪贞仪 | 35 | 64 | 27 | 42 | E |
107 | 李承航 | 86 | 37 | 30 | 51 | E |
108 | 林淑慈 | 82 | 69 | 50 | 67 | D |
109 | 张淑卿 | 99 | 100 | 86 | 95 | A |
110 | 吴芊菱 | 90 | 85 | 80 | 85 | B |
# 宏
开发工具 => 录制新宏

宏 => 选择要执行的宏
单步执行可以进行调试

# VBA
# Variable、Const
1 | Option Explicit |
# Select Case
1 | Sub SelectCase() |
# Loop
1 | Sub ForLoop() |
# Array
1 | Sub ArrayTest() |
# Object Hirerarchy
1 | Sub ObjectHierarchy() |
# Cell Reference
1 | Sub CellRefTest() |
# Function
1 | Function Concat(rng As Range, Optional sep As String = "") As String |
# Hide Column
1 | Sub HideColumn() |
# Combine Sheet
1 | Sub CombineSheet() |
# Debug
- 在需要天使程式关注点左侧打上断点
- 点击运行
- 点击 Debug 选择调试方式。


# Other
# CSV
以 ,
分隔的 .csv
文件可以通过 excel 直接打开;以 tab
分隔的 csv 将文件名重命名为 .xls/.xlsx
可以通过 WPS 直接打开。
以 ,
分隔的 .csv
虽然可以直接使用 excel 开启,不过对一些特殊的符号需要进行简单的处理,例如 ="007", =007, 007
在 excel 中的显示分别是 007, =007, 7
reason | csv | xlsx |
---|---|---|
显示逗号 | “Taipei, Taiwan” | Taipei, Taiwan |
显示双引号 | “12’30"”" | 12’30" |
显示数字或字符串前面的 00 | =“007” | =“007” (显示 007) |
使用 excel 公式 | =SUM(B2:B5)/"=SUM(B2:B5)" | =SUM (B2:B5) (显示和) |
双引号前有空格 | A, "B,C",D |
![]() |
双引号后有空格 | A,"B,C" ,D |
![]() |
科学计数法 (only numbers) | 646772001824 | 6.46772E+11 |
科学计数法 (include E) | 646772002E95 | 6.47E+103 |
# Excel edit CSV with large number
test.csv
1 | a,b |
使用 Excel 直接打开,大数字会变成科学计数法表示,编辑数据需要将数字都调成文本。否则再次保存 CSV 时资料是以科学计数法的形式表示。如果资料比较多将数字都调成文本很麻烦。

使用导入数据的方式导入 CSV,数据列都设置为文本。这样就不会以科学计数法的方式表示大数字。修改完成后再保存为 CSV 即可。




# Keep image size
