🧮 数据计算与统计
· 求和 (SUM):最基础,我让 =SUM(A1:A10)。
· 条件求和 (SUMIF/S):更聪明的我,带条件的我,例如对"销售部"业绩单独求和。
· 乘积求和 (SUMPRODUCT):发烧的我,能计算加权平均值如 =SUMPRODUCT(B2:B10, C2:C10)。
· 平均值 (AVERAGE/IF/S):找一群数的平均水平。
· 计数 (COUNT/COUNTA/IF/S):统计单元格里谁有数、谁非空。
· 极值 (MAX/MIN):找出最高分和最差成绩。
· 排名 (RANK.EQ):喜欢比赛,能算出自己在班级里排第几。
🎯 数据查找与引用
· 经典查找 (VLOOKUP):按列垂直查找的老大哥。
· 增强查找 (XLOOKUP):全能查找新秀,比VLOOKUP更灵活、更强。
· 黄金组合 (INDEX+MATCH):完美的搭档,没有VLOOKUP的限制。
· 条件筛选 (FILTER):Excel 365/AI,能一键按条件筛选出所有数据。
· 位置匹配 (MATCH):我的定位器,能帮你找东西在哪个位置。
📝 文本处理
· 文本提取 (LEFT/RIGHT/MID):多功能的剪刀手,想从哪剪就从哪剪。
· 文本长度 (LEN):帮我测测这句话有几个字。
· 文本合并 (TEXTJOIN/CONCAT):能插入分隔符,并自动跳过空单元格的合并专家。
· 文本替换 (SUBSTITUTE):把所有"的"都变成"地"。
· 格式化 (TEXT):我的格式转换器,能把数字变成各种日期格式。
⚖️ 逻辑与错误处理
· 条件判断 (IF/IFS):我的AI大脑,能根据条件做决策,例如判断成绩是否及格。
· 多重条件 (AND/OR):团队协作,AND要求全员同意,OR有一个人点头就行。
· 错误保护 (IFERROR):我的安全网,当公式出错时显示“你想要的”,而不是讨厌的#N/A。
📅 日期与时间
· 当前日期/时间 (TODAY/NOW):时间旅行者,告诉你今天几号、现在几点。
· 年龄计算 (DATEDIF):工龄计算器,能精准算出两个日期相差的年、月、日。
· 年份/月份/日期 (YEAR/MONTH/DAY):能从一串日期里拆出是哪一年、哪一月。
📊 高级数据分析
· 强力汇总 (数据透视表):我的最强神器,点点鼠标就能完成百万数据的多维度汇总。
· 智能标记 (条件格式):我的警报系统,能将超标业绩自动标红。
· 可视化呈现 (图表):用柱状图、饼图等图形让数据一目了然。
· 深度挖掘 (分析工具库):当你需要做回归分析、t检验等专业统计时启用。
· 提问式分析 (分析数据/AI):可以用自然语言提问“按地区汇总销售额”获取分析结果。
· 多维建模 (Power Pivot):大数据量的高级数据建模工具,构建复杂数据模型。
⚙️ 功能模块速查表(菜单功能)
· 数据选项卡:排序和筛选(对数据列表进行顺序调整或条件筛选)、删除重复项(一键清理数据的冗余值)、数据验证(设置下拉菜单限制输入)、分列(智能拆分数据)、Power Query(专业的数据清洗和转换工具)。
· 公式选项卡:名称管理器(为公式定义别名)、公式审核(追踪公式逻辑与错误)。
· 页面布局选项卡:打印标题、分页预览(打印前的专业排版)。
· 审阅选项卡:保护工作表/工作簿(对特定区域加密)、允许编辑区域(设置协作编辑权限)。
· 视图选项卡:冻结窗格(固定首行首列方便阅读)、新建窗口(对比数据)、宏(查看/执行录制的自动化指令)。
📚 Excel内置函数分类全景图
· 逻辑函数 (7个):IF, AND, OR, NOT, TRUE, FALSE, IFERROR。用于条件判断。
· 统计函数 (98个):SUM, AVERAGE, COUNT, MAX, MIN, COUNTIF/S。数据的汇总与分析。
· 查找与引用 (20个):VLOOKUP, HLOOKUP, XLOOKUP, INDEX, MATCH, OFFSET。精确定位所需数据。
· 文本函数 (27个):LEFT, RIGHT, MID, LEN, TRIM, CONCATENATE, TEXTJOIN。处理字符串。
· 日期与时间 (22个):TODAY, NOW, DATE, DATEDIF, YEAR, NETWORKDAYS。日期与时间的计算。
· 财务函数 (53个):PMT(贷款偿还额), PV(现值), FV(未来值), NPV(净现值)。财务分析与计算。
· 数学与三角 (63个):SUM, ROUND, MOD, RAND, ABS。各种数学计算。
· 信息函数 (17个):ISBLANK, ISERROR, TYPE, N。检查单元格的数据类型。
· 工程函数 (41个):CONVERT(度量单位转换), BIN2DEC/BIN2HEX(进制转换)。工程类专业计算。
· 数据库函数 (12个):DSUM, DAVERAGE, DCOUNT。对数据清单进行类似SQL的条件计算。
· 多维数据集函数 (7个):CUBEVALUE, CUBEMEMBER。在OLAP数据库中提取数据。
· Web函数 (5个):ENCODEURL, WEBSERVICE。处理网络数据。
💡 记忆小贴士
要想更好地记住这些函数,可以试试这几招:
1. 家族分类法:别零散地记,把同类函数(如文本家族的 LEFT、RIGHT、MID)当成一个功能组来记。
2. 场景联想法:把函数用到真实场景中。比如 VLOOKUP 就像图书馆的检索系统,输入一本书名(查找值),就能查到它在哪个书架(返回值)。
3. 新旧对比法:了解函数的演进史能帮你更快上手。比如 VLOOKUP -> XLOOKUP,CONCATENATE -> TEXTJOIN,SUM(IF(...)) -> SUMIFS。
万丈高楼平地起,掌握上面这些核心函数和功能,你就拥有了Excel最实用的大部分技能库了。
夜雨聆风