☰

按月排名前 5 行

tag-icon • tag-icon microsoft-excel worksheet-function
按月排名前 5 行

我在电子表格中有一组 Google Anlaytics 数据，其中按月列出了一段时间内查看的所有页面。它看起来像这样；

Month | Page Title | Page Views | Visitors | Bounce Rate  
Dec       Page1         250         199          20  
Dec       Page2         240         189          10  
Nov       Page1         260         192          30  
Nov       Page2         250         190          10

在“摘要”页面上，我有一个下拉菜单来选择我想要数据的月份，其中有几位数据可以用于此。

对于这个特定的数据集“按月排名前 5 位的热门页面”，我想要选择月份，然后返回该月排名前 5 位的页面。

我意识到这可以通过以月份为过滤器的数据透视表和一些 VBA 来完成，但到目前为止，我已经避免对此电子表格使用 VBA，并且想找到一种不使用代码来完成它的方法，我很乐意使用中间/辅助列/表，它不必是一个巨型公式。

答案1

哎呀，我忘了最后抓取页面名称，也没有提到它是一个数组函数！谢谢 HackSlash。

{=INDEX(B:B,MATCH("dec"&LARGE(IF(A:A="dec",C:C),1),A:A&C:C,0),1)}

这是一个数组函数。您必须按下 cntrl shift enter 才能使 Excel 将括号放在其外部。

这将返回 B 列的值，其中 C 列的值最大，且 A 列包含“dec”。将 1 更改为 2 表示第二，更改为 3 表示第三，等等...

如果愿意，您可以使用单元格引用代替“dec”。

答案2

将以下内容粘贴到第一个结果单元格（顶部结果）的公式栏中。由于换行格式，直接粘贴到单元格可能会将此公式拆分到多个单元格中：

=IFERROR( INDEX( $R$4:$R$13, MATCH(
   MAX( IF( $Q$4:$Q$13=$R$14, IF( NOT( COUNTIF( $Q$14:$Q14, $R$4:$R$13)), $S$4:$S$13, ""), "")),
   IF(  $Q$4:$Q$13=$R$14, IF( NOT( COUNTIF( $Q$14:$Q14, $R$4:$R$13)), $S$4:$S$13, ""), ""),
   0)), "")

调整：

  • $Q$4:$Q$13 = 月份数据列
  • $R$14 = 要显示的月份选择列表单元格
  • $R$4:$R$13 = 页面标题数据列（给定月份的标题
    必须是独特的， 见下文）
  • $Q$14:$Q14 = 顶部结果公式上方的单元格（引用的单元格
    不得包含可能的页面标题）
  • $S$4:$S$13 = 比较数字（页面浏览量或访客），
    来自数据列或计算结果（中间/辅助）列

  • 除了“迄今为止的结果”范围 $Q$14:$Q中的第二个单元格的行号外，所有引用都是绝对的14
  • 以下范围的大小必须相同：
    • 月份数据列$Q$4:$Q$13
    • 页面标题数据列$4雷亚尔:$13雷亚尔
    • 比较数量（页面浏览量）数据列$4新元:$13新元

编辑后输入Ctrl- Shift-Enter因为这是一个 CSE 公式。
然后向下拖动所需的顶部结果行数。（这可以通过另一个选择列表以编程方式设置。）

唯一值：如果两个页面标题（比如来自两个不同站点的“主页”）在给定月份可能相同，那么就需要一个辅助列。

  • 辅助列将包含上述公式和一些
    连接在一起的额外列，比如 <url>&<title>，以唯一地标识给
    定月份内的行（两个以上的列连接也可以）。
  • 公式中使用页面标题的所有地方$R$4:$R$13，都用连接替换
    $U$4:$U$13 & $R$4:$R$13，其中 $U$4:$U$13 是 url 数据列。
  • 甚至 INDEX 的第一个参数也被替换，这将生成一个
    连接的辅助结果列。
  • $Q$14:$Q14 的值现在是对辅助列顶部
    结果单元格上方单元格（包含此公式的单元格上方）的引用。引用的单元格（
    辅助列结果列表上方）不得包含任何可能
    在此辅助列连接结果中找到的值。
  • 显示的结果公式将是
    =IFERROR( INDEX( $R$4:$R$13, MATCH( $H15, $U$4:$U$13 & $R$4:$R$13, 0)), "")
    $H15 作为辅助列中第一个（顶部）结果单元格。
  • 由于数组连接，这个新公式是 CSE。
  • 复制并向下拖动以增加相对行引用 $H15（辅助列的
    第一个单元格），因此行引用会增加以引用以下行。
  • 已测试。

怎么运行的

  • IFERROR 是一个简单的保护，当给定月份的所有数据都已经排序并显示在结果中时。
  • 用于INDEX($R$4:$R$13,从页面标题数据列中获取匹配的值结果。
  • MATCH 和 MAX 使用相同的值列表。Match 会在此列表中查找列表中 MAX 所在的索引。
  • 一系列嵌套的 IF 产生一个值数组。结果数组包含 MAX 要考虑并由 MATCH 搜索的比较值。
  • 第一个过滤器$Q$4:$Q$13=$R$14将月份数据列（数组）的每个元素与选择列表值进行比较。结果是一个真/假数组，取决于月份数据列的行是否与选择列表值匹配。对于每个 True 元素，IF 返回嵌套的 IF 结果，否则返回“”。
  • 嵌套 IF 与外部 IF 在数组的相同索引处工作。所有数组的大小必须相同。If({True,False},{"a","b"},If({True,True},{1,2}) ) 是 IF({True,False},{"a","b"},{1,2})。第 1 行是 If(True,"a",1)，第 2 行是 If(False,"b",2)，结果为 {"a",2}。
  • 第二个过滤器NOT(COUNTIF( $Q$14:$Q14, $R$4:$R$13))也返回一个真/假数组。如果页面标题数据列（COUNTIF 的第二个参数）的行不在之前的顶级结果中，则数组元素为真$Q$14:$Q14。
    • 由于范围的第二个单元格引用是相对的，因此随着公式的复制/向下拖动，先前的顶部结果数组会增长。对于每个后续结果，将有更多页面标题被错误地过滤掉。
    • 第二个过滤器的真/假数组对于页面标题数据列的每一行都有一个元素。
    • COUNTIF 函数用于页面标题数据列中的每个页面标题。COUNTIF 函数计算此特定页面标题（来自页面标题列数据数组）在先前结果中出现的所有次数$Q$14:$Q<grows>。如果未找到任何结果，则计数为零（预期结果为零或一）。
    • COUNTIF 的结果将提供给 NOT。对数字进行布尔测试，如果结果为零则返回 false，否则返回 true。NOT 函数的否定使得此结果在结果为零（在之前的结果中未找到）时为 true，否则为 false（在之前的结果中找到 COUNT 次）。
    • 对于数组中具有 True 值的每一行，IF 返回比较值数组的同​​一行，否则返回“”。
  • 这可以有效地用“”替换不需要的值，同时保留需要考虑的值。
  • 如果所有值都是“”，则 MAX 返回零，否则即使它是负数，也会返回最大值。
  • MATCH 不会将零与空字符串匹配，即使 Excel 有时会将空字符串视为零（这不是其中之一）。当页面的访问次数为零时，这可以防止出现错误结果。
  • 最后一个零是 MATCH 的第三个参数，因此它只会考虑完全匹配（并且在未排序/无序列表中也是必要的）。

相关内容