河北搜恒不给做网站,编程工具,软件定制开发公司官网,文化传媒建设网站一、简介
Excel中的透视表适合小规模数据#xff1b;如果想在稍微大一些的数据中进行高性能透视表分析#xff0c;就要使用Power Pivot#xff1b;再大一些数据#xff0c;可能就需要大数据分析服务来进行分析。
Power Pivot#xff0c;可以让没有技术背景的企业业务人员…一、简介
Excel中的透视表适合小规模数据如果想在稍微大一些的数据中进行高性能透视表分析就要使用Power Pivot再大一些数据可能就需要大数据分析服务来进行分析。
Power Pivot可以让没有技术背景的企业业务人员快速通过Excel进行数据建模执行复杂数据分析制作可自动更新的企业级数据报告。
传统Excel对比Power Pivot Mac版本的excel没有power pivot 功能所以同样用不了power bi。 二、创建一个简单的多表数据模型
第一种方法 VLOOKUP VLOOKUP(a表匹配字段b表查询字段 需匹配字段作为第一被选择的列b表匹配结果列0或1表示是否精确匹配) 功能在表格的首列查找指定的数值并返回表格当前行中指定列处的数值。 结构VLOOKUP(查找值,查找区域,列序数,匹配条件) 解释VLOOKUP(找谁,在哪里找,第几列,0或1) 说明 1、第一参数查找值找谁比如说根据【姓名】来查找【成绩】【姓名】就是查找值 2、第二参数查找区域在哪里找查找的数据区域也就是所选择的区域注意所选择的区域要根据查找值位于第一列开始选择比如说根据【姓名】来查找【成绩】那数据表所选的区域要从【姓名】列开始选择。建议设置为绝对引用在选定区域后按F4键就可以快速切换就是在行和列的前面添加$符号拖动公式时区域就不会发生改变 3、第三参数列序数第几列也就是返回的结果在数据表中位于第几列包含隐藏的列 4、第四参数匹配条件0或1若为0或FALSE代表精确匹配1或TRUE代表近似匹配 注查找值在数据表中多次出现导致有多个结果函数仅仅会返回第一个找到的结果。 VLOOKUP函数16种经典用法史上最全记得收藏 - 知乎 第二种方法Power Query power query的合并查询 第三种方法 Power Pivot多表查询 1.导入数据 管理数据模型进入power pivot界面 主页--从其他源--选择Excel导入表 导入后界面分为两部分数据预览区域和计算区域。 计算区域可以对预览区域的数据进行一些计算比如求和等。 可以重复上述步骤导入多张数据表。 Excel求和只会有加和数字但是Power Pivot会有文字求和数字这是Excel函数和Power Pivot DAX函数的区别。 点击求和数字可以修改显示文字和数字格式。 2.两表连线建立关系 两张表能够连线建立关系必须满足如下 1两张表的连线字段必须一张表是唯一值另一张表唯一值或重复值即1N。不能够NN 导入多张数据表点击“关系图视图”会进入多表界面显示每张表的所有字段。鼠标左键将关联字段连线。就会出现联系线段线段三个内容 1 * 箭头。1和* 表示连线字段哪边是唯一值哪边是重复值箭头代表箭尾的表可以查询箭头指向表的数据。反之不可以调用。只有唯一值的表可以调用*值的表。详述见下连线建立后点击数据透视表就会带着此数据模型回到Excel透视表界面。 Power Pivot中连线成功的表使用方法 1连线成功后会自动分配1端表和*端表1端表所有字段可以作为“行”同时让 *端表的字段作为 “值”反之*端表作为“行”同时让1端表作为“值”会报错但是单独*端表作为“行”同时作为“值”没问题。 如下图”行“中为两个1端的字段“值”为*端的字段。 3.数据透视表 两张表建立关系后点击数据透视图回到Excel数据透视表界面。 4.数据透视图 数据透视表和数据透视图如果原始表格数据发生变化即使未保存此时点击Power Pivot菜单中的刷新也会同步变化统计值。 刷新下拉箭头全部刷新是指会将所有表格都进行比对刷新。 1.给关心的字段添加切片器。 左键--添加切片器 2.添加透视图 选中数据透视表中某行数据--点击数据透视图--选择类型--插入到制定位置 5.Power View Power Pivot数据模型确定以后在Excel界面点击插入--Power View即可进入view界面。然后选择字段--选择图标类型即可生成统计图。 如下虽然两个图标单独建立但是之间也会有交互。比如选择第一图的北区那么第二图也会显示北区的各产品分类的数值。 三、Power Pivot学习
1.Power Pivot界面认知 菜单栏 粘贴 可以将数据粘贴到Pivot适用于数据不变化的情况。比如省份区域对应表。 显示隐藏 可以右键列选择将某列隐藏此时关系图界面、数据透表界面等的字段也会隐藏可以通过此功能显示出隐藏的字段。常用与隐藏辅助列。 设计栏 创建关系等同于主页下面的关系图视图用来连线管理关系既看到有哪些关系。不常用关系图视图更直观。表属性。可以显示隐藏某些字段。想要时勾选不想要时去勾选。 一般在导入数据时有些字段不想要会去掉勾选导入后如果需要去掉的字段此时可通过表属性再次勾选去勾选的字段如果要对某列数据进行筛选也在表属性设置这样导入的数据就不会包含筛选掉的数据。如果导入后通过下拉列筛选数据只是隐藏透视表还是会进行汇总统计。 高级 默认字符集、表行为常在和view交互时使用同义词 给字段起个别名 。view可以实现交互式问答此时问答可以使用别名。比如字段英文、字段不同地方的方言等。 2.Power Pivot 导入数据 导入数据三个方法。被导入数据需要关闭才能导入成功。 1从其他源导入 2复制粘贴 适用于数据不变化的情况。比如省份区域对应表 3链接表的形式 链接表 添加到数据模型既是链接表形式。 链接表形式常用作改动较多的表此时透视表也在此sheet画出如果改动直接刷新透视表即可更新。如果不是链接表形似改动数据需要回到pivot界面点击菜单栏更新才能将数据更新到透视表。 如果多张表中有链接表形式上传的sheet表数据先打开此链接表sheet选择添加到数据模型然后在打开的数据模型中导入其他数据表此时多表在一个数据模型pivot界面如果先打开空白表点击管理数据模型进入pivot界面导入非链接形式的表形成一个数据模型再打开链接表点击添加到数据模型形成另一个数据模型此时会有两个pivot界面数据模型因此建议第一种方式。 DAX函数不同于excel函数开始开头调用公式而是以以“计算值的名称: ”开头 ‘’单引号作为引用表中某单元格或某行汇总数据的智能感知。 3.计算列和计算字段 计算列为新增列对某行不同列的数据进行计算选中新增列单元格写DAX公式此公式会自动运用到整个新增列自动进行所有计算不同于excel第一个单元格写公式然后拖动选中整个列才能计算。 计算字段或度量值为对某列所有行汇总或者某些单元格值进行计算 。计算结果会自动根据切片器或者分组而变化。 度量值比如计算订单平均金额就适合使用度量值。 先汇总求一个总订单数再汇总求一个总金额数再新建一个度量值计算两者的商。 度量值和计算列都可以实现某个需求时优先推荐使用度量值节省内存和空间。 除了可以在power pivot界面计算区域新建度量值也可以在excel界面新建度量值。 4.按列排序 如果透视表中 “行” 名称没有按照想要的顺序排列可以通过按列排序进行处理。 1增加一列 产品分类--筛选指定类别--给每个类别添加一个大小数字 2选中产品分类---按列排序---选择排列辅助字段 3 透视表刷新后发现按照指定辅助列数值大小进行了排序 此时可以隐藏辅助排序列。 5.Power Pivot的关系 两张表能够连线建立关系必须满足如下 1两张表的连线字段必须一张表是唯一值另一张表唯一值或重复值即1N。不能够NN。比如上图的省份区域表的省份字段每个值都只有一个销售记录表的客户省份会有很多客户是同一个省份有重复值。身份和客户省份建立连接后1端为省份区域表*端为销售记录表。 2建立连接后1端可以作为透视表的行*作为列值反之不可以即1端表的所有字段可以查询*端表的所有字段的汇总值。 3可以按照箭头跨表控制 按照箭头走向可以跨表控制。 箭头走向连在一起的表不需要再手动建立联系建立的话会使用虚线表示没必要建立联系。 6.DAX函数
使用规则 1注意使用英文字符 2点击计算列单元格 输入框输入 鼠标选择其他单元格进行计算点击计算区域 输入框输入 名称: 进行计算 3点击计算列单元格 输入框输入或[ 单引号会提示所有表的字段[ 只提示当前表的字段 4DAX运算符 [金额]“元” 文本连接 AND OR等 下图为excel中用法DAX中and or只能两个参数如果需要多个判断可以嵌套and。 DAX中常用 if来多条件判断而不是and 。 if中可以使用表示and||表示或。 常见函数 在输入框输入函数名会自动提示。 related、relatedtable 用在已经建立关系的表中。relatedtable是将建立联系的字段作为分类字段然后获取到多段的数据进行处理。 calculate特性不受自身切片器控制是指calculate函数内部的筛选条件作为切片器时此切片器改变不影响calculate计算结果。比如下图中 是否订单作为透视表中的切片器时选择0或1calculate的计算结果都一样。 数据分析示例
增长率 1如下 差异百分比会计算出同比、环比增长率。需要表格里面有每月数据和两年数据多年数据才能计算同比多月数据才能计算每月的同比。 2总计百分比可以计算出每个单元格占某列总计的百分比。 3父行汇总的百分比。 会计算每个分组中每个单元格占本分组的百分比而不是占全部的百分比 设计--分类汇总--在组的底部显示所有分类汇总 会为每个分组生成一个汇总值 产品分析 KPI 在power pivot界面中的度量值右键--kpi 如下图。 1如果目标值选度量值。度量值为分母选定的基本字段为分子。会根据计算结果显示不同颜色 2如果目标值为绝对值。需要选定的基本字段为百分比。绝对值修改为1. 此时增加了kpi的度量值可以在透视表增加一列状态。
