Excel数据分析必备技能

Excel数据分析必备技能Excel简单基础Excel函数3套常用函数日期函数逻辑函数零件函数3套必会函数1. vlookup函数2. sumifs函数3. countifs函数引申函数-lookup、xlookuplookup函数xlookup函数其他函数concat函数FIND函数Excel透视表Excel商务智能插件Power BIExcel简单基础相对绝对引用 $F$2 快捷键fnf4替换中通配符简单介绍1“ *”代表任意长度的字符串2“ ”代表任意长度为1的字符Excel函数3套常用函数日期函数函数功能today()返回现在日期now()返回现在日期和时间year()返回年month返回月day()返回日hour()返回小时weeknum返回本年中第几个星期weekday返回星期几text选中引用单元格“yyyy年mm月dd日”返回到xxxx年xx月xx日逻辑函数逻辑函数true/false逻辑值excel中表示数值乘积和真true112假false000if函数表达方式IF(条件, 条件为真时返回的值, 条件为假时返回的值)介绍名称第一参数第二参数第三参数公式ifA100“负数”“0或正数”翻译如果A100条件成立则返回负数条件不成立则返回正数例子IF(A1100, “完成”, “未完成”)表示如果单元格A1的值等于100则返回“完成”否则返回“未完成”and和or函数名称翻译引申and和且全部or或其中一个OR(条件1, 条件2, 条件3, ...)例如IFORA28, B22, 正常, 迟到或早退iferror函数它是Excel中的一个非常实用的工具用于处理公式中可能出现的错误。当公式计算结果为错误时该函数允许你指定一个替代值从而避免显示错误信息使结果更加友好和可读IFERROR函数语法和参数如下 IFERROR(value, value_if_error)‌value‌这是你要检查是否包含错误的值或表达式 ‌value_if_error‌当value为错误时返回的自定义替代值零件函数函数功能意义len()返回单元格字符长度字符串长度函数find()在单元格查找某个字符串的位置找不到返回错误查找字符函数截取函数函数公式返回内容rightrighttext文本参数中提取的字符数文本右侧返回一定数量的字符leftlefttext文本参数中提取的字符数文本左侧返回一定数量的字符midmid字符串开始位置字符个数文本返回包含其中的字符替换函数语法 SUBSTITUTE(text, old_text, new_text, [instance_num])text‌需要替换其中字符的文本或对含有文本的单元格的引用 old_text‌需要替换的旧文本 new_text‌用于替换old_text 的新文本 instance_num‌可选指定要将第几个 old_text替换为new_text。如果省略则所有出现的 old_text 都会被替换3套必会函数1. vlookup函数‌VLOOKUP函数‌是Excel中一个非常强大的查找与引用函数主要用于在表格或数据集中查找特定值并返回同一行中其他列的数据。其基本语法如下 VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])‌lookup_value‌要查找的值‌table_array‌用于查找的范围查找值所在的列必须为范围的第一列‌col_index_num‌要返回的值的列索引即在“范围”中的列数‌[range_lookup]‌可选参数TRUE表示近似匹配FALSE表示精确匹配省略时默认为精确匹配2. sumifs函数定义多个指定条件对若干单元格求和 SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)1sum_range要求和的单元格区域。2criteria_range1使用Criteria1测试的区域。criteria_range1和criteria1设置用于搜索某个区域是否符合特定条件的搜索对。 一旦在该区域中找到了项将计算Sum_range中的相应值的和。3criteria1定义将计算Criteria_range1中的哪些单元格的和的条件。 例如可以将条件输入为32、“32”、B4、“苹果或32”。4criteria_range2, criteria2, … (optional)附加的区域及其关联条件。 最多可以输入 127 个区域/条件对。3. countifs函数定义用于计算多个区域中满足给定条件的单元格数量 COUNTIFS(range1, criteria1, [criteria2], ...)criteria_range1 必需 计算关联条件的第一个区域criteria1 必需 条件的形式为数字、表达式、单元格引用或文本它定义了要计数的单元格范围。 例如条件可以表示为 32、“32”、B4、apples或 “32”criteria_range2, criteria2, … 可选 附加的区域及其关联条件。 最多允许 127 个区域/条件对。引申函数-lookup、xlookuplookup函数‌LOOKUP函数是Excel中用于查找和返回数据的一种函数它有两种主要形式向量形式和数组形式‌。LOOKUP函数要求查找值所在区域必须按升序排序否则可能无法返回正确的结果‌LOOKUP函数的基本语法和参数如下向量形式 LOOKUP(lookup_value, lookup_vector, [result_vector])lookup_value要查找的值可是数字、文本、逻辑值等lookup_vector包含查找值的区域须按升序排列result_vector返回结果的区域其大小必须与lookup_vector相同‌数组形式 LOOKUP(lookup_value,array)lookup_value要查找的值可是数字、文本、逻辑值等array包含文本、数字或逻辑值的单元格区域或数组它的值用于与 lookup_value 进行比较LOOKUP(1, 0/((条件1)*(条件2)), 返回区域)xlookup函数 XLOOKUP(要查找的值查找范围返回范围[未找到匹配项时的返回值][匹配方式])要查找的值需要在查找范围中进行查找的值。查找范围包含要查找的值的数据表范围。返回范围与查找范围相对应的数据表范围。未找到匹配项时的返回值可选如果未找到匹配项可以指定一个默认返回值。匹配方式可选指定查找的方式如精确匹配、近似匹配等。其他函数concat函数用来拼接不同单元格里的文本内容具体实现如下CONCAT(值1, [值2, ...])例如 CONCAT(A2, )FIND函数FIND(find_text,within_text,start_num)Find_text 是要查找的字符串。 Within_text 是包含要查找关键字的单元格。就是说要在这个单元格内查找关键字。Start_num 指定开始进行查找的字符数。比如Start_num为1则从单元格内第一个字符开始查找关键字。如果忽略start_num则假设其为 1。Excel透视表数据透视表原始要求格式的基本要求无合并双表头等标准的日期格式文本型数值字段原子化避免复合数据数据无残缺不用汇总总计Excel商务智能插件Power BI