首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >如何提取带有数据的月份,并在Excel查询中找到n个值作为起点,n个值作为结束点,也许是VBA

问如何提取带有数据的月份,并在Excel查询中找到n个值作为起点,n个值作为结束点,也许是VBA
EN

Stack Overflow用户
提问于 2022-01-19 10:52:36
回答 1查看 179关注 0票数 -1

我有一个数据集,包括日期/时间、压力和自定义列。这代表了时间数据的压力,我想知道我的起点(5分钟后)和-before最后值的结束点(行)在一个月内。为了给你一点帮助,通常测量的时间大约是30-40分钟,你可以在这个例子中看到。因此,这意味着数据量可能会有所不同。

时间列使用以下方法计算:

代码语言:javascript
复制
=([@[Date/Time]]-I5)*1440+L5

这个数据集代表整个数据和所有有值的月份,我需要与屏幕截图中的这些起始点/结束点分隔(过滤)月份。我经常使用Power来处理数据,但可能还有另一种方法来获取这些values...and,使它们在可能的情况下成为未来数据的动态查询。

​

​

我也会上传我的虚拟工作簿与整个数据集(所有的月份),过滤表与月份,如果需要为您的信息和测试。

https://docs.google.com/spreadsheets/d/1LGl-eri6ewCni2NJ2wGeoYIf-40KO2Lr/edit?usp=sharing&ouid=101738555398870704584&rtpof=true&sd=true

EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2022-01-19 20:13:06

Power查询中的:

根据您的共享工作簿和您所写的内容,似乎在任何一个月,您

编辑:算法中的小改动

  • 在排除了一个月中的第一个条目后开始计时分钟。如果这是错误/错误,只需删除删除第一个line

的函数即可。

  • 的第二个条目=分钟0,返回中的第一个条目或分钟5之后的第一个条目,以及表中最后一个条目。
  • 注意到,我从Date和压力E 221列H 222f 223开始。

算法

monthYear

  • GroupBy
  • 向
    • 添加一列monthYear
      • 自定义聚合,删除表
      • 的第一行和最后一行,创建与月份第一次相比,每次以分钟为单位的持续时间列表。这将是一分钟+一分钟的分数,将该列表作为列添加到原始的table
      • Determine (中的第一个条目)或之后的第五个条目(),最后一个条目
      • 筛选返回这两个entries.

的月份子表。

如果只想查看给定月份的结果,可以在结果Excel表中筛选结果。

M码

请阅读评论并检查应用步骤,以更好地理解算法

代码语言:javascript
复制
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date/Time", type datetime}, {"P7 [mbar]", Int64.Type}}),

//add month/year column for grouping
    #"Added Custom" = Table.AddColumn(#"Changed Type", "month Year", 
        each Number.ToText(Date.Month([#"Date/Time"]),"00") & Number.ToText(Date.Year([#"Date/Time"]),"0000")),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"month Year"}, {
        //elapsed minutes column
        {"Elapsed Minutes", (x)=> let

        //remove first and last rows from table  
            t=Table.RemoveColumns(Table.RemoveFirstN(Table.RemoveLastN(x)),"month Year"),

        //add a column with the elapsed minutes
            TableToFilter = Table.FromColumns(
                Table.ToColumns(t) 
                & {List.Generate(
                    ()=>[em=null, idx=0],
                    each [idx]< Table.RowCount(t),
                    each [em=Duration.TotalMinutes(t[#"Date/Time"]{[idx]+1} - t[#"Date/Time"]{0}), idx=[idx]+1],
                    each [em])}, type table[#"Date/Time"=datetime, #"P7 [mbar]"=number, elapsed=number]),

        //filter for last entry (which would be next to last in the month
            maxMinute = List.Max(TableToFilter[elapsed]),

        //filter for first entry in the 5th minute
            fifthMinute = List.Select(TableToFilter[elapsed], each Number.IntegerDivide(_,1)>=5){0},

        //select the 5th minute and the last row
            FilteredTable = Table.SelectRows(TableToFilter, each [elapsed]=fifthMinute or [elapsed]=maxMinute)
            in FilteredTable,type table[#"Date/Time"=datetime, #"P7 [mbar]"=number, elapsed=number]}
            }),

    //remove uneeded column and expand the others
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"month Year"}),
    #"Expanded Elapsed Minutes" = Table.ExpandTableColumn(#"Removed Columns", "Elapsed Minutes", {"Date/Time", "P7 [mbar]"}, {"Date/Time", "P7 [mbar]"})
in
    #"Expanded Elapsed Minutes"

来自共享工作簿数据的结果

​

In Office/Excel365

过滤柱(如2020年1月)

代码语言:javascript
复制
E4: 1/1/2020
E5: 1/1/2020

结果

代码语言:javascript
复制
F4 (date/time 5th minute): =IF(COUNTIFS(Table1[Date/Time],">="&E4,Table1[Date/Time],"<" & EDATE(E4,1))=0,"",
LET(x,FILTER(Table1[Date/Time],(Table1[Date/Time]>=E4)*(Table1[Date/Time]<EDATE(E4,1))),
          y, (x-INDEX(x,2))*1440,
          z, XMATCH(5,y,1),
          INDEX(x,z,1)))

G4: (Pressure 5th minute): =IF(F4="","",
LET(x,FILTER(Table1,(Table1[Date/Time]>=E4)*(Table1[Date/Time]<EDATE(E4,1))),
          y, (INDEX(x,0,1)-INDEX(x,2,1))*1440,
          z, XMATCH(5,y,1),
          INDEX(x,z,2)))

F5: (Date next to last): =IF(COUNTIFS(Table1[Date/Time],">="&E5,Table1[Date/Time],"<" & EDATE(E5,1))=0,"",
LET(x,FILTER(Table1[Date/Time],(Table1[Date/Time]>=E5)*(Table1[Date/Time]<EDATE(E5,1))),
          INDEX(x,COUNT(x)-1)))

G5: (Pressure next to last):=IF(F5="","",
LET(x,FILTER(Table1,(Table1[Date/Time]>=E5)*(Table1[Date/Time]<EDATE(E5,1))),
          INDEX(x,COUNT(INDEX(x,0,1))-1,2)))

​

​

票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/70769294

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档