Excel技巧笔记

Sean1014

已于 2023-03-01 15:58:41 修改

阅读量251

点赞数

分类专栏： Office三件套文章标签：矩阵

于 2022-07-30 05:08:27 首次发布

本文链接：https://blog.csdn.net/fgh544568/article/details/126067058

版权

Office三件套专栏收录该内容

5 篇文章 2 订阅

订阅专栏

本文汇总了Excel中的实用技巧，包括如何防止数据重复输入的数据验证方法，以及一系列快捷键操作，如全选、查找、替换等。此外，还介绍了各种函数的用法，如财务、日期时间、数学统计等函数，并展示了如何进行文本处理和日期格式化。同时，讲解了逻辑和信息函数的应用，帮助提升Excel工作效率。

摘要由CSDN通过智能技术生成

使用技巧

1.同列数据防止重复输入

选择应用区域（例如A列）——数据-数据工具——数据验证-数据验证——设置-验证条件-自定义-=COUNTIF(A:A,A1)=1，A2单元格的验证式自动设置为=COUNTIF(A:A,A2)=1，以此类推，可以实现A列数据重复输入报错的功能。

快捷键

Ctrl

Ctrl+A 全选

Ctrl+E 智能拆分

Ctrl+F 快速查找

Ctrl+G 快速定位

Ctrl+H 快速替换

Ctrl+Q 快速分析

Ctrl+T 快速美化表格

Ctrl+Z/Y 撤销与恢复

Ctrl+方向键快速移动

Ctrl+减号快速删除行/列

Ctrl+回车批量填充

Shift

Shift+F2 插入批注

Ctrl+Shift

Ctrl+Shift+L 快速筛选

Ctrl+Shift+方向键快速框选

Ctrl+Shift+加号快速插入行/列

Alt

Alt+方向下自动生成下拉列表，包含该列已经输入过的内容

Alt+等号快速求和

F

F2 编辑

函数

财务

日期与时间

Today()

Date(year,month,day) 返回年月日

Eomonth(start_date,months) 返回指定月最后一天

Year(serial_number)

Month(serial_number)

Day(serial_number)

Weekday(serial_number, [return_type]) 1：星期日1~星期六7；2：星期一1~星期日

Weeknum((serial_number), [return_type]) 返回日期是是一年中的第几周

数学与三角函数

Degrees(angel)弧度转化成角度

统计

Count(value1, [value2],…) 统计区域中包含数字的单元格个数。统计区域中包含指定数值的单元格个数。

Counta(value1, [value2],…) 统计区域中不为空的单元格个数。

Countblank(range) 统计区域中为空值的单元格个数。

Countif(range, criteria) 统计区域中满足指定条件的单元格个数。

Countifs(criteria_range1, criteria1, [criteria_range2, criteria2], …) 统计区域中满足指定条件的单元格个数。例如条件为大于500，写法为">"&500，条件为字符串格式。

Sum(range)

Sumif(range, criteria1, [sum_range])

Sumifs(sum_range, criteria_range1, criteria1, …)

Sumproduct(array1, [array2], [array3],…)

Max(number1, [number2],…)

Min(number1, [number2],…)

Average(value, [value2],…)

Mod(number, divisor) 求余

Rank(number, ref, [order]) 返回指定数值在指定数据中的排位。0：降序，1：升序。

Round(number, num_digits) 保留指定位数小数，四舍五入

Int(number) 向下取整

Floor(number, significance) 向下舍入到最接近的指定基数的倍数

Rand() 返回0~1之间的随机数

Randbetween(bottom, top) 返回指定值[bottom, top)之间的随机整数

查找与引用

Vlookup(lookup_value, table_array, col_index,num, [range_lookup]) 1：近似匹配；2：精确匹配

Hlookup(lookup_value, table_array, row_index,num, [range_lookup])

Lookup(lookup_value, lookup_vector, [result_vector]) 从单行单列或数组中查找指定值，向后兼容

Index(array, row_num, [column_num]) 返回数组中指定行列的值

Indirect(ref_text, [a1]) 返回文本字符串所指定的引用。TRUE：A1样式；FALSE：R1C1样式

Match(lookup_value, lookup_array, [match_type]) 返回单行单列中指定值的位置。0：等于（不要求array列排序方式）；1：小于（要求array列升序排列）；-1：小于（要求array列降序排列）

Offset(reference, rows, col, [height], [width]) 以指定单元格为参照系，通过给定偏移量得到新的引用

Row([reference])

Column([reference])

数据库

文本

Left(text, [num_chars])

Right(text, [num_chars])

Mid(text, start_num, num_chars)

Len(text)

Lenb(text) 返回字节个数，中文占2字节，英文1字节

Concatenate(text1, [text2], …) 连接字符文本

Text(value, format_text) 数值按指定格式转换为文本

格式

基础知识

0：数字占位符，数量不足需用0补足

#：数字占位符，数量不足不补足

@：文本占位符，连续使用表示重复显示文本

！或\：强制显示符

1.数值格式

原始数据	处理方法	处理后数据	说明（保留时四舍五入）
12.34	=TEXT(A1,"0")	12	取整
12.34	=TEXT(A2,"0.0")	12.3	保留一位小数
1234.56	=TEXT(A3,"#,##0")	1,235	加千分位分隔符并取整
1234.56	=TEXT(A4,"#,##0.0")	1,234.6	加千分位分隔符并保留一位小数
12.34	=TEXT(A5,"0000")	0012	显示固定位数整数
12345.67	=TEXT(A5,"000#,##0.0")	0,012,345.7	显示固定位数整数、千分位分隔符、保留一位小数
12.34	=TEXT(A6,"0%")	1234%	加百分号
12.34	=TEXT(A7,"$0")	$12	加货币符号
12.34	=TEXT(A8,"!aaa0")	aaa12	强制加字母aaa
12.34	=TEXT(A9,"!10.00")	112.34	强制加数字1
12345	=TEXT(A10,"0!.0,万")	1.2万	显示格式为万、保留一位小数

2.字符格式

原始数据	处理方法	处理后数据	说明
abc	=TEXT(A24,"@@")	abcabc	重复显示文本两次

3.日期格式

原始数据	处理方法	处理后数据	说明
2022/1/2	=TEXT(A1,"YY")	22	两位年份
2022/1/2	=TEXT(A2,"YYYY")	2022	四位年份
2022/1/2	=TEXT(A3,"M")	1	一位月份
2022/1/2	=TEXT(A4,"MM")	01	两位月份
2022/1/2	=TEXT(A5,"MMM")	Jan	英文月份简写
2022/1/2	=TEXT(A6,"MMMM")	January	英文月份全称
2022/1/2	=TEXT(A7,"D")	2	一位日期
2022/1/2	=TEXT(A8,"DD")	02	两位日期
2022/1/2	=TEXT(A9,"DDD")	Sun	英文星期简写
2022/1/2	=TEXT(A10,"DDDD")	Sunday	英文星期全称
2022/1/2	=TEXT(A11,"AAA")	日	中文星期简写
2022/1/2	=TEXT(A12,"AAAA")	星期日	中文星期全称
2022/1/2	=TEXT(A13,"YY-MM-DD")	22-01-02
2022/1/2	=TEXT(A14,"YY年MM月DD日AAAA")	22年01月02日星期日