学习Excel技术，关注微信公众号： excelperfect 在使用VLOOKUP函数查找数据时，如果多于一个匹配值，如何获取第一个匹配的值或者最后一个匹配的值。...将VLOOKUP函数的第4个参数忽略或指定为TRUE，即近似匹配，此时返回最后一个找到的匹配值。因为执行近似匹配查找时，Excel将找到大于查找值的值并返回该值的前一个值。...如果要查找的数据没有排序，执行近似匹配查找，将不会得到正确的结果，如下图3所示。 ? 图3 近似匹配查找的一个典型应用是，确定某范围内的值。例如，根据分数范围确定成绩等级，如下图4所示。 ?...图5 在单元格F3中的公式为： =INDEX($C$3:$C$10,MATCH(E3,$B$3:$B$10,1)) 注意，公式中MATCH函数的第3个参数设置为1，执行近似匹配查找。...欢迎到知识星球：完美Excel社群，进行技术交流和提问，获取更多电子资料。 ?
常见的方法有四种： 1) 用Excel制作，导入PowerBI （这是最灵活的方法，尤其在使用定制的日历表时） ?...Addcolumns的含义是在生成日期表基础上添加列，这与使用Excel制表的逻辑是一样的。...利用基本的日期函数Year、Month、Weeknum和算式求得每一个日期的年季度月日，这些函数与Excel的函数相同，对Excel的使用者来讲不难理解。...注意到在建立[年月]列时，我使用的方法是Year([Date])*100+Month([Date])数字计算，而不是利用Format函数生成“年份月份”的文本格式。...这个公式并不难，保存下来可以复制粘贴使用。即使手工输入也不会很费力气。最重要的是你能够理解此公式的含义，根据自己的需要利用Excel类的简单日期函数做调整。 我把公式写在了下面，供直接复制使用。
DAX 包含一些在 Excel 公式中使用的函数，此外还包含其他设计用于处理关系数据和执行动态聚合的函数。...DAX 公式与 Excel 公式非常相似，要创建 DAX 公式，请键入一个等号，后跟函数名或表达式以及所需的任何值或参数，DAX支持常见的4中运算符：算数运算符（+、-、*、/、^）、比较运算符（=、>...除了以上运算符外，DAX 提供多种函数，与 Excel 类似，可用于处理字符串、使用日期和时间执行计算或者创建条件值，像SUM求和函数，这种函数数据量非常多，超过200个，但是常用的函数有几类：日期和时间函数...首先按照之前方式导入四个季度的数据： 图片 然后通过UNION DAX公式创建纵向合并表，公式如下：2022年点播订单表 = UNION('第一季度点播订单表','第二季度点播订单表','第三季度点播订单表...需求：创建一张时间表，包含年份、月份、日期、季度、星期、年份季度、年月、年周、全日期列字段，具体操作如下，新建表，输入DAX公式如下： 日期表2 = ADDCOLUMNS( CALENDAR(DATE
，或者大部分前置工作也是和本篇类似，数据加工处理的能力，还是需要回到Excel环境上才是真方便。...因Excel催化剂的微信导入导出功能，使用的是RPA技术，非破解版的直接底层接口，所以在修改个人微信昵称时，也是模拟人的操作，将昵称搜索名复制粘贴到通讯录管理的搜索框中，点击搜索，对搜索到的记录进行修改...原始的标签是个日期格式或月份格式如YYYYMM。本次作标签清洗，想把它进行一些高维聚合，如太久远的年份，将这个日期标签聚合到半年或季度为单位。...克服这个不区分大小写，在Excel环境，可不容易，大部分的查找函数匹配都是不区分大小写的，这时，当然再次请出Excel催化剂的自定义函数，可以使用=GetMd5String([@微信昵称])，对字符串求...总结 Excel催化剂的每一个功能，并非无用之举，很多都是笔者亲自需要用到，然后花了些成本抽象化，让大家可以更低门槛去使用。
RankX和TopN函数 又是一对好用的函数，特别是对于营销分析，做业绩排名，当然对于学生考试成绩、运动比赛成绩的排名也不在话下，应用场景就根据自己需要来发掘吧。 1 RankX ?...TopN返回的表更改了矩阵表中的初始上下文，所以每一行的结果都为136。也许你会问这样的计算有什么用?传统的Excel方法也可以很容易算出来。...那么我们现在来做一个有意义的事，创建一个度量值[前五名城市销售量]占比。利用学过的All函数使Divide的分母为所有城市的总销售量。做一个折线图，轴为日历表中的年份月份，你就会得到这个占比。 ?...这个时候，如果老板想要看前10名的情况，你只需要把TopN公式里的5改成10；如果想要按季度分析，只要把日历表中的年份月份换成年份季度，如果想要计算销售额而不是销售量，那就把销售量度量值都替换成销售额。...如果你想用传统的Excel来达到这个目的...我懒得写了...还是用度量值来个弯道超车吧。 ?
格式数据操作类似，点击"转换数据"：三、导入文件夹数据现在有2021-2022年销售信息表和第一季度销售表，需要将两年数据导入到Power BI 一个数据文件中，对于这种导入文件夹中的数据有两种方式，分别为合并并转换数据导入文件夹数据和通过...复制粘贴到"2021年销售信息表"文件夹内：然后在Power Query 页面点击"刷新预览"即可将相应的"2022年销售信息表"全部excel数据合并在一起。...2、M函数导入在"资料"中有"第一季度销售信息.zip"数据，将数据解压后可以看到有2021年第一季度销售信息和2022年第一季度销售信息，打开2021年第一季度销售信息文件夹，可以看到有该年前3个月的销售信息...M函数将这些excel数据批量导入，具体操作如下：在打开的页面中过滤掉"~"开头的数据临时数据文件：数据文件过滤后效果如下：后续不需要其他的列，可以删除其他列，然后添加新的列，自动以导入哪些数据列：注意...通过观察导入的数据，看到最后一列为null，这是因为3个excel表中对应的列不匹配导致，"2021年3月销售表.xlsx"文件多了"销售额"这列，所以导致批量导入文件夹数据后看到了一列null值。
几乎所有的报表模型都涉及到日期和时间，因此要创建Power BI报表，日期表就必须得有。虽然最新的Power BI版本已经可以自动为每一个时间列创建日期表。...今天给大家介绍三个创建Power BI日期表的途径，分别对应着一种语言，Excel中的VBA语言，适用于Power BI和PowerPivot的DAX语言，适用于Power BI和PowerQuery的...第一种是VBA语言： 直接用excel中的vba语言编写，通过添加简单的按钮可以实现一键创建日期表，并灵活修改起止日期。...这一段代码中并没有指定起止日期，这就是CALENDARAUTO函数的厉害之处，它可以自动检测模型中其他表中所有日期，然后生成涵盖这些日期的整年日期表。...首先创建两个参数，kaishiDate和jieshuDate来确定起始日期和结束日期，然后在查询编辑器中，新建一个空查询，打开高级编辑器，粘贴以下代码，回车即可。
5）针对“性别”列的数据清洗 其中，1和M表示男生；2和F表示女生。我们这里要做的，就是将所有的1和M变为男生，2和F变为女生。 ? 6）在源数据中增加“单价”这一列 ?...6）针对“地区维度”的处理 ① 数据去重 首先把“源数据”表的地区名字这一列，粘贴到地区这里，然后再选中这一列，点击“删除重复值”。 ? ② 使用sumifs()多条件求和：求出销量 ?...3）另外插入几个文本框，用于制作“订单总数”、“总销售额”、“总销量”和“请插入年份” ?...4）插入每一个需要展示的图形 这一步其实很简单，大家只需要选中“维度汇总”这个SHEET表中的数据源，然后添加你想绘制的图行即可。...③ 接着，关闭“显示公式”的操作 ? ④ 将“维度汇总”中如下三个单元格的公式，都复制粘贴到可视化大屏中 ? 操作如下： ? ⑤ 效果展示 ?
近日，阿迪达斯发布了2020年财报，其中一张报表是店铺数量，如下图所示： 该报表显示了阿迪达斯每个季度的店铺数量，以及到2020年底的店铺数量（注意年底数量=Q4数量），另外，还显示了每个季度的开店数量...如何在Excel/Power BI中仅凭一个完整店铺资料制作类似以上报表（如下透视表所示）？下文以Excel界面进行讲述，Power BI软件操作相同。...准备一个日期表，仅有日期列即可，日期完整覆盖店铺资料表的所有开业日期、关闭日期年份（本例为2017-2021年）。...，拉个透视表/矩阵呈现结果。...在本例中，日期表和店铺明细无需建立关系，使用时间智能函数时等操作时，日期表需要与数据中的日期建立关系。但本例日期表只是用来筛选。
这是RankX的基本使用，我想有了之前对SUMX类函数的学习，理解这三个步骤并不难。注意到表中的江门、马鞍山、唐山、襄阳排名为7，之后的排名就跳到了11，而不是第8名。...TopN返回的表更改了矩阵表中的初始上下文，所以每一行的结果都为136。也许你会问这样的计算有什么用?传统的Excel方法也可以很容易算出来。...那么我们现在来做一个有意义的事，创建一个度量值[前五名城市销售量]占比。利用学过的All函数使Divide的分母为所有城市的总销售量。做一个折线图，轴为日历表中的年份月份，你就会得到这个占比。 ?...这个时候，如果老板想要看前10名的情况，你只需要把TopN公式里的5改成10；如果想要按季度分析，只要把日历表中的年份月份换成年份季度，如果想要计算销售额而不是销售量，那就把销售量度量值都替换成销售额。...如果你想用传统的Excel来达到这个目的...我懒得写了...还是用度量值来个弯道超车吧。 ?
昨天，我发了文章，表明vlookup这样低效的函数其实可以放弃了。今天我要说的是，Excel所有的日期相关的函数公式你也可以放弃了。 在零售行业，恐怕没有人不重视时间。...我们今年的业绩要和去年对比看增长了多少。我们这个月销售任务的完成进度是否跟得上时间进度。我们国庆节的销售目标是多少。这就要求我们考虑各种时间维度去分析零售数据。...然后，问题就来了，Excel有各种时间日期函数，Date, Month, Day, Year, Eomonth,Today, Workday等等，那真是一大堆，眼花缭乱，想起来就晕。...我们需要查看年份、季度、月份、周等各种时间维度的数据。 首先将Excel数据导入Power BI,然后如下视频点击鼠标。...(操作如手机无法看清请使用平板或电脑） 只需要鼠标点击即可进行各种日期维度转换，此处只是列举最简单的几种，更多可自行探索。 后期本公众号可能还会介绍Power BI的相应应用。
虽然更符合我们日常的阅读习惯，信息更浓缩，适合展示分析结果，但作为源数据进行数据分析时，就需要一维表。如下图所示，将二维表导入可视化工具中，字段无法识别。 ?...二维表转化为一维表，当然可以自己手动复制粘贴调整表结构，聪明人直接借助Excel中的Power Query，主要使用逆透视功能，就可以将上图的二维表，转换成下图的一维表。 ? 1....将年度列和季度列合并，生成年度季度列，简化表格结构。选中年度和季度两列，点击转换——合并列。 ? 在弹出的“合并列”弹出框中，可选择用分隔符隔开两个合并字段，也可以不选。 ?...得到如下图所示，年度和季度合并的年度季度列。 ? 5. 点击转换——转置，对表格进行转置处理； ? 6....之前我们没有选择分隔符，可以按字符数进行分割，年份（2015）是4个字符。 ? ? 10. 点击开始——关闭并上载，就完成了一维表的转换。 ?
操作和Excel中操作方式相同，用Year函数创建年份，用Month函数创建月份即可，这里就不做展现了大家可以看销售目标中的公式参考。创建完成后将该表标记为日期表。 ? ...使用M函数List.Dates可以创建指定起始日期和天数的日期表。这种方式不需要使用公式，直接在功能列表中操作提取年份，季度，月份等字段。...切换到添加列选项卡，点击日期，提取年份，季度，月份 ? 添加完成后关闭并应用，进入PowerBI主界面进行下一步操作。 ? ...1、计算业绩的同比 同比时今年和去年同期数据的对比，公式：同比（%）=(本期销售业绩-去年同期销售业绩)/去年同期销售业绩*100% 在计算同比之前，我们需要先计算本期销售业绩和去年同期销售业绩...[同比YTD]<=0,"Red",[同比YTD]<=0.25,"Gray","Green") 然后按照方法一进行设置背景色格式 ?
原始数据来源于沈浩老师的水晶易表教程，奈何做法与思路实在是高深奥妙，令人费解，反正本宝宝看了好久始终没有消化（虽然那个方法看起来很棒），结果一怒之下宝宝就自己操刀改了数据，使用函数嵌套（使用正常套路）给模型搭建完毕...然后通过index函数将参数转化为具体的指标（index只能传递数字序号）。 通过&文本合并函数将三个指标合并。 通过offset+match函数嵌套在源数据表中匹配对应指标的12个月份值。...在D3单元格中使用offset+match函数嵌套实现C3单元格的数据匹配查找。公式如下： D3=OFFSET(D9，MATCH($C$3，C$10，$C$288，0)0，1，1) ?...match函数输出的行号将作为offset函数的第二个参数。 这里offset函数的意思是：从D9单元格开始，向下移动（match返回值）行，向右移动0列，然后选择1行1列（也就是选择该单元格）。...至此，动态仪表盘全部配置完毕，可以通过预览功能查看动态交互性能是否完好，没有问题之后，可以通过水晶易表的主题、颜色以及统计图属性菜单中的外观项目精修字体、配色和背景，也可以在部件中插入专门的背景色块做衬托
输出和复制到excel print(title,'\t',year)，中间的'\t'是制表符，我们可以直接鼠标选择output输出的内容，右键复制，然后打开excel新建空白文件，然后选择合适的表格区域范围...，【右键-选择性粘贴】弹窗中选择Unicode文本，就可以把数据粘贴到excel表格中。...最后把全部250个电影数据反复10遍粘贴到Excel表格就可以了。 当然我们有更好的方法，比如利用for循环自动采集10个页面的数据。...4.生成统计数据 我们把采集到的数据粘贴到Excel文件中，最顶上插入一行【影片名、年份】。 Excel数据 接下来我们利用这些数据研究一下哪些年盛产好电影。 如上图，点击B栏全选这一列。...拖拽到值 然后点击表格里面的【求和项：年份】，再点击【字段设置】，弹窗中选择【计数】，然后确认，就能统计出每个年份上映的电影数量。
这样位置对调了，初学者表示难以操作，很有技术含量的，如果你平时EXCEL用得太少，建 议果断放弃，那还是多做几次剪切粘贴吧，运动有助健康。...6.选择性粘贴 如果A列数据需要更新数据，比如，价格要全部打九折，80%的用户是插入辅助列，输入公式=A1*0.9，然后拖动填充，再复制到A1中，再变为 值，再将辅助列删除。...利用选择性粘贴的运算功能可以快速处理这类问题，在一个空白单元格输入0.9，然后复制，再选择需要更新的列，右击【选择性粘贴】， 在运算组中，选择【乘】，然后确定。...曾经看到有人在日期右边插入一列，用Year计算出年份，然后再插入一 列，用Month计算出月份，然后再一个个筛选，再进行汇总，当时我就震惊了，哎，不会透视表伤不起呀。...推荐使用IFERROR函数，公式短，见效快，还没副作用。如果你以上十项全中，那么别灰心，关注我，就可以从我发 布的信息中了解到有关OFFICE的许多内容！
经常看到一些海报中使用的柱形、条形图是用图画来填充的，生成形象生动的视觉效果。 ? 网上可以搜到使用Excel的方法介绍，如果使用PowerBI怎样完成呢？...我预先下载好了四杯咖啡图片，上传后，再做一些行数和每行单元数的设定，就可以轻松得到下面这张效果。 ? 第二大心得体会是，它有一个非常赞的功能，即使你不用它的图形效果。...在右边的设定栏里你会看到两个选项，Column by列， Row by行。也就是说你可以按照不同的类别把图表分开（下图是按照年份季度来划分的）。为什么赞这个功能呢？...在实际工作中比如你的数据有二十个城市，你想要为每个城市制作一张销售数据表，比较笨的方法是你用复制粘贴出来二十张表，再修改城市。...而且，该表提供了很多深度的自定义功能，比如该图中年份季度的数据颜色是不同的（绿、浅蓝、红、深蓝），你都可以针对不同咖啡种类自定义设定。
使用该咖啡数据的好处，是它非常的简单清晰，便于你对公式的学习和理解，在你掌握了公式的精髓后再把方法结合到各种复杂场景来应用就可以做到融会贯通。...数据使用说明： 案例数据共6张Excel表 1. 销售数据表 – 记录咖啡店每张订单数据信息，日期从2015年1月1日到2016年12月31日，共2万7千多条数据。 ?...2.产品表 – 不同咖啡种类、杯型产品的ID和价格 ? 3.顾客信息表 – 记录每位顾客的最基本信息。 ? 4.门店信息表 – 目前在全国有53家门店，该表有对应店长的姓名和年龄。 ?...5.日历表 – 这是一张标准的日历，从2015年1月1日到2016年12月31日每一天的年份季度、年份月份、星期信息。 ? 6.财务费用汇总表 – 每家门店自开业之日起，财务月份的费用支出情况。 ?...模型的搭建架构如下图所示： ? 祝您有一个愉快的PowerBI学习之旅！
在日期维度上做分析，本质上是给度量值修改日期上下文，在日期维度上实现日期的平移、范围扩大或缩小，生成一个新的日期或日期区间，然后做运算，比如去年同期、YTD累计、滚动3个月平均、期末库存等。...指定时间点OPENINGBALANCEQUARTER当前上下文中该季度第一个日期。指定时间点OPENINGBALANCEYEAR当前上下文中该年份第一个日期，可指定年度结束日期。...《复制粘贴PowerQuery代码，生成多功能日期表》。...公式调用日期表的日期，不要直接调用事实表中的日期。2 将日期表标记日期表。...日期表和事实表建立关系的列，如果日期列是日期类型，可标记也可不标记；如果日期列是数字类型，比如20240101，必须标记，否则返回的结果可能会出现错误。因此，建议标记日期表。
[Date]的情况，比如在写公式时，输完某个日期列的时候，就会自动弹出来.[Date]、.[MonthNo]……一堆选项： 此时，直接回车就会把.[Date]带入公式中，最后写成上面的公式的样子。...如下图所示： 这个时候，在日期表里看到的两个“年”、两个“季度”、两个“月”……但他们的层次是不一样的。...[Date])就是日期表中的最大日期，取年份即都是2022； 所以，上图中的DatesBetween的筛选条件，对于每一个年份，它的范围都是2018年1月1日至2022年6月30日，这时，再叠加“年”...目前，发现很多朋友在用Excel的思路去用DAX里的这些函数，一不小心就出错了，因为，这些函数虽然长得一样，但实际上差异很大！...DAX不难，但一定要沉下心来，好好把基本的原理和基础打好，一心想着看几个函数就匆忙用到工作上，不仅几乎等同于抓虾，而且还是很容易出错的。
领取专属 10元无门槛券
手把手带您无忧上云
云服务器
ICP备案
对象存储
腾讯会议
云直播
