根据数据透视表中另一列的 MAX 返回索引值（按月）-解网

问：

我有一个按月划分的工单数据集，以及在整个工单过程中发生的一些操作的持续时间（以分钟为单位）。

我创建了一个数据透视表，并添加了自定义度量，以找到第50个和第90个百分位数，以及每个月的最大值。

理想情况下，我想做的是在数据透视表中显示每个持续时间的最大值（就像我能够做到的那样），但将显示的值设置为基于相应票证 ID 的超链接。我无法在不中断表中其他持续时间列和/或月份字段的情况下对（或我无法）进行切片/过滤（或我无法）。

我昨天问过这个问题，但忽略了创建一个最小的、可重复的示例，而是链接到电子表格。昨晚深夜想到这里，我想这里的一些人可能不喜欢下载流氓 .xlsx 文档。我在下面的代码块中包含了一个数据示例，以及我的数据透视表的屏幕截图，我现有公式的示例，但是我保留了驻留在我的Google驱动器中的.xlsx文档的链接，供那些可能愿意查看它的人使用。

我已经手动编写了公式来显示我想要实现的目标，但我希望找到一种方法来使其在数据透视表中动态填充。

我对数据透视表以外的其他选项持开放态度，因为我不需要数据透视表的向下钻取粒度，我需要的是能够随时使用每个月的数据更新 RAWData 工作表，并且能够“刷新数据透视表”并包含新的月份。我已经能够使用一个函数在某种程度上让事情在数据透视表之外工作，但我似乎无法在不手动定义一个月结束和下一个月以某种方式开始的情况下获得它。但理想情况下，id 就像动态表中的所有结果/计算（在页面上）一样，当我添加额外月份的数据时，该表将更新。INDEXcalc

此外，我想使用 REPLACE 函数（with ）来删除 co/ with，而不是使用该函数。将来，我希望更新 RAW 数据的数据源并省略（从长远来看，对于多个用例，我更喜欢这样做），如果我使用 LEN，我将不得不遍历并更新我的所有公式或丢失来自字段的字段，这将有效地破坏超链接。IF(ISERROR)&Right(xx,LEN(xx)-3)co/TICID

工作簿/.xlsx 文件包含两个工作表。一个标题为“calcs”，其中包含数据透视表和我对所需结果的模型，另一个标题为“RAWData”，其中包含我正在使用的数据集。我将数据格式化为标题为“RAW”的表格。

"Count of ID“只是使用数据透视表的本机功能。数据透视表中的“p50 of”和“p90 of”自定义度量值分别使用公式：或 .09，其中根据我们正在查看的列而变化。我单独设置了其中的每一个。 “最大持续时间”只是使用数据透视表的本机功能，其中会根据我们正在查看的列进行更改。Count of ID=PERCENTILE.INC([DurationX],0.5)[DurationX]MAX of [DurationX][DurationX]

主数据透视表下方的“Ideal Max of DurationX”是我希望如何返回其上方“Max of DurationX”列中的结果的示例。它显示最大值，就像上面的数据透视表一样，但会根据该最大值的相应 ID 创建一个超链接。我手动创建了这个公式来实现这一点，但我必须经历：

=HYPERLINK("https://someurl.com/"&RIGHT(RAWData!A9,LEN(RAWData!A9)-3),RAWData!B9)

我找到了一个使用示例并执行此操作，但它要求我在数据透视表之外设置一个完全独立的表，如果它是唯一的解决方案，它将起作用，但并不理想。这个公式目前是：INDEXMATCH(MAXIFS

=INDEX(RAW[ID],MATCH(MAXIFS(RAW[PreDetection],RAW[Date/Time(in UTC)],">="&A3,RAW[Date/Time(in UTC)],"<"&A14),RAW[PreDetection],0))其中 A3：A14 是月份 1/1/2023：12/1/2023（格式化mmm)

以下是RAWData工作表的内容（CSV格式）

ID,Duration1,Duration2,Duration3,Duration4,Duration5,Duration6,Duration7,Date/Time(in UTC)
co/TICKET-0001,2.22,0.63,0.63,37,122,147.49,267.27,1/4/23 7:41
co/TICKET-0002,3.23,2.27,2.27,28,121.17,23.61,141.55,1/5/23 20:17
co/TICKET-0003,5.21,0.78,0.78,10,14.13,39.86,48.78,1/7/23 1:50
co/TICKET-0004,2.23,1.95,1.95,12,58.17,40.71,96.64,1/9/23 5:16
co/TICKET-0005,5.23,0.53,0.53,18,25.47,9.84,30.08,1/10/23 18:30
co/TICKET-0006,5.21,0.97,0.97,4,8,56.24,59.04,1/13/23 9:40
co/TICKET-0007,2.23,2.18,2.18,10,13,197.36,208.14,1/13/23 18:58
co/TICKET-0008,7.22,0.43,0.43,8,21,138.62,152.4,1/18/23 16:40
co/TICKET-0009,4.6,0.37,0.37,3,5,101.77,102.17,1/18/23 22:05
co/TICKET-0010,3.2,1.2,1.2,14,29,18.67,44.47,1/19/23 11:31
co/TICKET-0011,5.24,0.6,0.6,11,65,37.91,97.66,1/23/23 10:19
co/TICKET-0012,4.23,0.73,0.73,8,11.15,243.75,250.67,1/24/23 2:10
co/TICKET-0013,3.25,1.3,1.3,4,6,72.55,75.3,1/24/23 10:07
co/TICKET-0014,6.24,2.43,2.43,11,12.22,37.65,43.62,1/24/23 23:55
co/TICKET-0015,3.23,0.83,0.83,6,8,40.12,44.9,1/25/23 0:38
co/TICKET-0016,6.22,0.35,0.35,6,49,25.25,68.03,1/26/23 16:33
co/TICKET-0017,6.24,1.85,1.85,2,12.82,94.3,100.87,1/30/23 6:53
co/TICKET-0018,3.23,0.62,0.62,5,9,111.63,117.41,1/30/23 16:33
co/TICKET-0019,4.22,0.68,0.68,8,42,62.41,100.19,2/1/23 20:35
co/TICKET-0020,5.21,1.77,1.77,2,1,28.13,28.92,2/2/23 19:03
co/TICKET-0021,5.22,0.68,0.68,6,10,35.47,40.26,2/3/23 13:04
co/TICKET-0022,3.22,0.48,0.48,10,15.22,66.48,78.48,2/3/23 15:02
co/TICKET-0023,11.21,0.67,0.67,8,53.35,193.27,235.41,2/4/23 6:28
co/TICKET-0024,11.21,0.78,0.78,6,15.18,130.07,134.04,2/4/23 15:07
co/TICKET-0025,3.26,0.42,0.42,6,12,31.07,39.8,2/6/23 2:36
co/TICKET-0026,5.24,0.7,0.7,7,17.57,52.11,64.44,2/6/23 15:05
co/TICKET-0027,3.2,1.1,1.1,3,5,212.66,214.46,2/6/23 20:22
co/TICKET-0028,5.21,0.58,0.58,3,8,170.49,173.28,2/7/23 8:30
co/TICKET-0029,4.22,0.62,0.62,6,14,43.71,53.49,2/8/23 21:46
co/TICKET-0030,2.21,4.62,4.62,61,71,1261.76,1330.55,2/9/23 12:11
co/TICKET-0031,5.25,0.32,0.32,7,49,52.24,96,2/9/23 17:05
co/TICKET-0032,2.21,0.78,0.78,11,14.23,48.62,60.64,2/10/23 17:16
co/TICKET-0033,3.2,0.45,0.45,4,7,35.72,39.52,2/12/23 0:42
co/TICKET-0034,3.22,0.8,0.8,4,9,35.88,41.66,2/12/23 2:20
co/TICKET-0035,3.18,0.65,0.65,6,9.8,54.13,60.76,2/12/23 10:32
co/TICKET-0036,3.22,2.03,2.03,4,6,32.7,35.48,2/13/23 1:18
co/TICKET-0037,2.22,0.5,0.5,9,22,58.28,78.05,2/13/23 9:56
co/TICKET-0038,3.22,0.75,0.75,4,6,25.61,28.39,2/13/23 14:20
co/TICKET-0039,3.22,0.23,0.23,12,21.48,28.57,46.84,2/13/23 19:00
co/TICKET-0040,5.23,0.6,0.6,5,62.25,32.57,89.59,2/13/23 21:54
co/TICKET-0041,2.25,0.62,0.62,6,9,37.78,44.53,2/14/23 6:13
co/TICKET-0042,5.23,0.28,0.28,7,70.87,54.22,119.86,2/16/23 23:01
co/TICKET-0043,2.23,4.72,4.72,11,31,59.25,88.02,2/21/23 1:19
co/TICKET-0044,6.28,0.67,0.67,2,7,83.02,83.74,2/22/23 5:18
co/TICKET-0045,5.23,1.2,1.2,7,14,218.17,226.94,2/22/23 19:55
co/TICKET-0046,5.24,0.75,0.75,14,79.88,35.44,110.08,2/23/23 0:36
co/TICKET-0047,2.25,2.43,2.43,5,6,74.14,77.89,2/23/23 18:43
co/TICKET-0048,5.25,0.8,0.8,6,11,291.39,297.15,2/25/23 8:39
co/TICKET-0049,6.24,0.72,0.72,6,12.47,22.96,29.18,2/25/23 22:13
co/TICKET-0050,3.23,0.88,0.88,3,5,68.32,70.08,2/27/23 5:44
co/TICKET-0051,2.25,2.02,2.02,8,10.4,103.58,111.73,2/27/23 13:43
co/TICKET-0052,2.26,0.65,0.65,6,7.2,24.74,29.68,2/27/23 17:40
co/TICKET-0053,5.24,0.7,0.7,3,27,61.96,88.73,2/27/23 17:53
co/TICKET-0054,5.23,0.6,-1,4,8,59.32,62.09,3/1/23 3:28
co/TICKET-0055,7.22,1,117,6,132,32.69,157.47,3/3/23 2:33
co/TICKET-0056,5.33,0.27,1,6,13,94.13,101.8,3/3/23 22:24
co/TICKET-0057,4.28,0.72,-1,9,13.2,209.34,218.26,3/4/23 11:01
co/TICKET-0058,9.27,0.13,-4,7,12.4,63.18,66.31,3/6/23 16:57
co/TICKET-0059,7.26,0.75,-2,6,12,11.35,16.09,3/6/23 23:16
co/TICKET-0060,3.24,0.57,10,5,19,136.46,152.22,3/8/23 17:40
co/TICKET-0061,5.27,0.9,0,5,12.48,21.7,28.91,3/9/23 1:29
co/TICKET-0062,5.24,0.82,53,13,73.17,34.96,102.89,3/9/23 17:40
co/TICKET-0063,2.24,1.75,8,57,69,208.75,1160.54,3/10/23 3:23
co/TICKET-0064,5.23,0.4,48,5,59.23,8.5,62.51,3/10/23 12:47
co/TICKET-0065,15.17,0.43,24,25,65.22,59.6,109.65,3/14/23 14:00
co/TICKET-0066,5.25,0.53,49,2,57,100.92,152.68,3/16/23 20:24
co/TICKET-0067,2.26,3.53,25,48,75.03,198.11,270.88,3/17/23 5:53
co/TICKET-0068,5.24,0.85,-2,9,13.45,174.52,182.73,3/17/23 10:03
co/TICKET-0069,3.27,0.58,0,3,6,54.35,57.08,3/18/23 8:30
co/TICKET-0070,8.25,1.33,1,3,14,34.05,514.26,3/19/23 16:51
co/TICKET-0071,3.27,2.02,-2,8,11,227.61,235.33,3/19/23 17:03
co/TICKET-0072,5.22,0.65,-1,4,8,235.54,238.32,3/22/23 8:24
co/TICKET-0073,5.25,0.52,96,9,111.15,82.76,188.66,3/23/23 17:04
co/TICKET-0074,7.25,0.68,0,5,82,15.04,89.79,3/24/23 18:50
co/TICKET-0075,2.27,0.35,8,4,15,133.32,146.05,3/25/23 7:28
co/TICKET-0076,5.24,0.65,0,7,13,41.66,49.41,3/29/23 19:20
co/TICKET-0077,3.24,0.95,-1,34,37,107.77,141.53,3/30/23 6:18
co/TICKET-0078,6.27,0.85,-1,5,11.65,47.14,52.53,3/30/23 7:44
co/TICKET-0079,10.26,0.58,0,8,37.17,38.77,65.68,3/31/23 15:20
co/TICKET-0080,3.25,1.13,-2,3,5,182.74,184.5,4/1/23 1:19
co/TICKET-0081,2.26,0.42,-1,3,4,121.27,123.01,4/3/23 11:28
co/TICKET-0082,3.29,0.97,-1,3,6,111.73,114.44,4/4/23 1:13
co/TICKET-0083,5.23,0.7,31,2,39,43.28,77.05,4/4/23 5:12
co/TICKET-0084,4.29,0.88,0,130,151.43,4557.99,4705.13,4/7/23 7:13
co/TICKET-0085,2.25,0.47,4,8,15,51.25,64,4/10/23 21:11
co/TICKET-0086,7.24,0.32,0,4,14.77,94.28,101.81,4/12/23 19:16
co/TICKET-0087,7.24,0.23,-2,8,13.68,25.69,32.13,4/16/23 4:07
co/TICKET-0088,5.26,0.47,4,9,19.17,70.67,84.58,4/20/23 18:10
co/TICKET-0089,2.24,0.27,14,8,25,18.61,41.37,4/20/23 22:03
co/TICKET-0090,5.24,0.63,87,8,101,30.94,126.7,4/23/23 0:06
co/TICKET-0091,7.25,1.03,-3,8,13.13,96.34,102.22,4/23/23 0:38
co/TICKET-0092,2.29,0.4,15,5,23,47.03,67.74,4/24/23 10:04
co/TICKET-0093,5.28,0.2,6,7,19,13.37,27.09,4/24/23 17:13
co/TICKET-0094,6.3,0.58,-2,8,12.87,36.04,42.61,4/24/23 20:20
co/TICKET-0095,3.28,0.7,3,6,13,41.54,51.26,4/24/23 21:02
co/TICKET-0096,3.25,0.8,0,6,9,159.06,164.81,4/25/23 13:23
co/TICKET-0097,2.26,0.38,1,9,11,235.2,245.94,5/1/23 23:17
co/TICKET-0098,5.25,1.08,258,7,272.25,37.2,304.2,5/3/23 8:19
co/TICKET-0099,8.25,0.78,-1,3,11.17,54.25,57.17,5/3/23 9:56
co/TICKET-0100,11.27,0.22,-2,2,11.9,42.65,43.28,5/4/23 19:11
co/TICKET-0101,3.25,0.95,-2,44,47,227.03,270.78,5/8/23 13:43
co/TICKET-0102,3.24,2.2,12,9,27,100.09,123.85,5/9/23 17:08
co/TICKET-0103,3.28,2.07,1,8,13,36.55,46.27,5/11/23 7:26
co/TICKET-0104,6.25,0.5,95,54,156.48,0.67,150.9,5/12/23 21:50
co/TICKET-0105,6.25,0.6,108,56,171.23,33.39,198.38,5/18/23 2:21
co/TICKET-0106,4.29,0.53,0,18,23,66.02,84.73,5/18/23 16:32
co/TICKET-0107,5.28,0.55,45,9,60.15,13.98,68.85,5/18/23 20:32
co/TICKET-0108,5.28,0.58,34,25,65.18,39.03,98.93,5/18/23 21:12
co/TICKET-0109,5.23,0.73,14,4,24,191.51,210.28,5/20/23 15:52
co/TICKET-0110,3.3,0.33,-1,8,10,55.35,62.05,5/22/23 18:09
co/TICKET-0111,4.25,0.3,28,70,103.25,22.55,121.54,5/24/23 4:53
co/TICKET-0112,3.45,0.47,8,10,22.22,53.57,72.33,5/24/23 16:50
co/TICKET-0113,7.26,1.45,0,3,11,128.54,132.28,5/25/23 9:49
co/TICKET-0114,7.27,0.9,0,4,12,96,100.73,5/25/23 13:10
co/TICKET-0115,6.27,0.98,1,3,12.2,121.81,127.74,5/27/23 8:54
co/TICKET-0116,2.26,0.82,0,9,12.27,89.95,99.96,5/29/23 7:22
co/TICKET-0117,7.28,0.65,29,5,42,19.42,54.14,5/30/23 22:27
co/TICKET-0118,6.24,0.82,-2,7,12.85,120.09,126.7,6/1/23 1:26
co/TICKET-0119,5.31,0.63,44,55,105,37.02,136.71,6/2/23 9:00
co/TICKET-0120,5.25,0.98,14,8,29.18,74.25,98.18,6/3/23 0:38
co/TICKET-0121,7.25,0.42,84,8,100,99.4,192.15,6/5/23 3:22
co/TICKET-0122,6.26,0.33,13,9,29,39.84,62.58,6/5/23 14:13
co/TICKET-0123,5.27,0.35,2,9,17.22,53.5,65.45,6/6/23 3:16
co/TICKET-0124,5.24,0.83,24,17,48.9,115.13,158.78,6/6/23 12:11
co/TICKET-0125,5.27,0.33,54,24,84.18,24.29,103.2,6/6/23 12:32
co/TICKET-0126,2.29,0.27,2,9,12,152.69,162.4,6/10/23 4:46
co/TICKET-0127,4.27,0.63,1,6,12,44.14,51.87,6/14/23 21:29
co/TICKET-0128,4.31,0.42,0,77,81.77,195.99,273.44,6/16/23 21:28
co/TICKET-0129,5.31,0.68,35,3,45,56.54,96.23,6/19/23 19:27
co/TICKET-0130,3.25,0.47,11,34,49.43,246.07,292.24,6/20/23 3:52
co/TICKET-0131,3.28,0.25,1,2,7,63.57,67.29,6/21/23 17:37
co/TICKET-0132,5.27,0.42,4,6,16.35,16.76,27.84,6/22/23 23:21
co/TICKET-0133,3.27,0.57,22,12,38,67.21,101.94,6/23/23 22:08
co/TICKET-0134,3.28,0.52,0,7,11,89.47,97.18,6/25/23 13:59
co/TICKET-0135,6.27,1.53,-1,6,12.97,10.96,17.65,6/29/23 17:35
co/TICKET-0136,6.27,1.43,1,3,12.35,123.7,129.78,6/30/23 0:16
co/TICKET-0137,5.26,1.07,8,14,29.25,22.19,46.19,6/30/23 12:12
co/TICKET-0138,2.31,2.93,50,140,195.25,401.02,593.96,6/30/23 15:26

Google Docs 链接到.xlsx文件：https://docs.google.com/spreadsheets/d/1F919nbp8GucZaYfs45lVnN7hZtcuRciD/edit?usp=sharing&ouid=110428622428089304236&rtpof=true&sd=true

Excel-公式透视表数据操作

0赞 FlexYourData 7/15/2023

我很确定您不能在数据透视表中返回超链接。

答：

2赞 FlexYourData 7/15/2023 #1

您使用了一个表，但为工作表“RawData”上的整个列 A 到 I 定义了它。

相反，请仅在使用的行上定义它。当您将行粘贴到表格底部时，表格范围将自动展开。

我很确定你不可能在数据透视表中获得超链接。

让我们尝试使用 Power Query 的不同方式：

let
    Source = Excel.CurrentWorkbook(){[Name="RAW"]}[Content],
    UseStartOfMonth = Table.TransformColumns(Source,{{"Date/Time(in UTC)", Date.StartOfMonth, type datetime}}),
    RenameToStartOfMonth = Table.RenameColumns(UseStartOfMonth,{{"Date/Time(in UTC)", "StartOfMonth"}}),
    ConvertToDate = Table.TransformColumnTypes(RenameToStartOfMonth,{{"StartOfMonth", type date}}),
    UnpivotDuration = Table.UnpivotOtherColumns(ConvertToDate, {"ID", "StartOfMonth"}, "Attribute", "Value"),
    Groups = Table.Group(
        UnpivotDuration, 
        {"StartOfMonth", "Attribute"}, 
        {
            {"Count", each Table.RowCount(_), Int64.Type},
            {"P50", each List.Percentile([Value],0.5), type number}, 
            {"P90", each List.Percentile([Value],0.9), type number}, 
            {"Max", each List.Max([Value]), type number}
        }
    ),
    JoinCols = {"StartOfMonth","Attribute"},
    Joined = Table.Join(Groups,JoinCols & {"Max"},UnpivotDuration,JoinCols & {"Value"}),
    AddTicketLink = Table.AddColumn(Joined, "TicketLink", each "'=HYPERLINK(""https://some_url.com/" & [ID] & """," & Text.From([Max]) & ")"),
    RemoveValue = Table.RemoveColumns(AddTicketLink,{"Value","Max","ID"}),
    RenameTicketLinkToMax = Table.RenameColumns(RemoveValue,{{"TicketLink", "Max"}}),
    Sort = Table.Sort(RenameTicketLinkToMax,{{"Attribute", Order.Ascending},{"StartOfMonth",Order.Ascending}}),
    UnpivotMeasures = Table.UnpivotOtherColumns(Sort, {"StartOfMonth", "Attribute"}, "Attribute.1", "Value"),
    MergeDurationMeasure = Table.CombineColumns(UnpivotMeasures,{"Attribute", "Attribute.1"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Measure Name"),
    PivotDurationMeasure = Table.Pivot(MergeDurationMeasure, List.Distinct(MergeDurationMeasure[#"Measure Name"]), "Measure Name", "Value", List.First)
in
    PivotDurationMeasure

这会产生以下结果：

使用 Power Query 的问题在于，无法将公式插入到具有查询刷新的单元格中。因此，按照这里的指导，我在每个链接之前都加了一个撇号。

现在，您可以在表上执行查找/替换操作（如果您选择，可以自动执行此操作）：

这将删除撇号并有效地激活超链接。

然后，您可以使用“超链接”预设设置列的格式：

并应用您想要的任何其他格式。

编辑：

修改了 AddTicketLink 行，以从 ID 字段中删除“co/”：

AddTicketLink = Table.AddColumn(Joined, "TicketLink", each "'=HYPERLINK(""https://some_url.com/" & Text.Middle([ID],3)) & """," & Text.From([Max]) & ")"),

0赞 Scott McCune 7/15/2023

您只看到最低使用量。此共享数据以及链接的电子表格使用虚拟数据，并且仅使用数据透视表中结果的子集。我向你保证，在整张纸中，我正在使用所有列 A-I。我只知道，如果我能找到 Duration1、2 和/或 3 的解决方案，我就可以为其他人复制它。此外，我完全不熟悉Power Query，并且更喜欢使用本机Excel解决方案，即使这意味着创建单独的表和从数据透视表返回的引用数据。这也意味着我不必在公司的笔记本电脑上安装任何东西

0赞 Scott McCune 7/15/2023

我研究了Power Query，似乎Excel版本已经包含它，但重命名了它。我将进一步研究您的解决方案

0赞 Scott McCune 7/15/2023

好吧，我让它起作用了，但是......它没有删除“co/”，所以我需要在某处的某个地方使用或（首选）A，但我被这样一个事实所困扰，即 #REF 需要是 A 列中与适当的 MAX 值相对应的文本。$RIGHT (#REF,LEN(#REF)-3)REPLACE(#REF,1,IF(ISERROR(FIND("co/",#REF)),0,3),"")

0赞 Scott McCune 7/15/2023

我尝试在电源查询中添加一行：

1赞 Scott McCune 7/15/2023

哦，这有效，但是我尝试了 powerquery 并找到了 Replace.Value 函数，并能够想出这个：作为查询中的第二行。这效果很好。谢谢你，不仅是解决方案，而且是推动我走向Power Query。= Table.ReplaceValue(Source,"co/","",Replacer.ReplaceText,{"ID"})

上一个：如何从mysql thrugh pandas读取读取json数据？

下一个：在 pandas 中创建自当前月份列以来的月份

根据数据透视表中另一列的 MAX 返回索引值（按月）

Return index value based on MAX of another column, by month, within pivot table

评论

评论