☰

Excel - 根据列条件仅保留不超过限制的值

tag-icon • tag-icon microsoft-excel
Excel - 根据列条件仅保留不超过限制的值

在 Excel 中，我有 100,000 行数据，其中一列表示鸟类种类，另一列表示发现鸟类的城市。

在此处输入图片描述

大约有 150 个城市。有些城市有数千行，有些只有几行。我希望最终得到一个新列表，该列表仅保留 B 列中最多 1,000 行的城市。假设纽约有 5,000 个目击记录，波士顿有 4,000 个，明尼阿波利斯有 300 个。我希望新列表仅包含 1,000 行纽约、1,000 行波士顿和所有明尼阿波利斯行，因为明尼阿波利斯的行数少于 1,000。

Excel 能做到这一点吗？我尝试过使用 COUNTIF、RAND 和 RANK 进行一些操作，但失败了，但我不是每天都使用 Excel 的人。我通常会在这里使用 SQL 或 .py，但需要定期执行此操作的可爱人士会在 Excel 中执行此操作。有没有简单的方法，对 B 列出现的值进行计数，然后按顺序编号、排序，然后删除任何超过 1,000 的值？

答案1

首先，将您的数据集声明为“表”（插入表）。将其命名为Dataset。名称必须与以下查询一致。

然后，数据/获取数据/从其他来源/空查询。

您位于 PowerQuery 编辑器中。

使用高级查询，然后输入：

let
    Source = Excel.CurrentWorkbook(){[Name="Dataset"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"bird", type text}, {"city", type text}}),
    Grouped = Table.Group(Typed, {"city"}, {{"Group", each _, type table [bird=nullable text, city=nullable text]}}),
    FirstN = Table.AddColumn(Grouped, "FirstN", each Table.FirstN([Group],1000)),
    Combined = Table.Combine(FirstN[FirstN])
in
    Combined

好的。然后 PowerQuery 显示预览。关闭并加载（左上角的按钮），查询结果将放在新选项卡中。

如果您更改了数据集，请转到结果，右键单击“Actualise”。

您会在线上看到FirstN =1000 的阈值，以及Source =表格的名称。

注意：查询按城市对线路进行分组。稍微复杂一点的查询会保留初始顺序：

let
    Source = Excel.CurrentWorkbook(){[Name="Dataset"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"bird", type text}, {"city", type text}}),
    Indexed = Table.AddIndexColumn(Typed, "Index", 0, 1, Int64.Type),
    Grouped = Table.Group(Indexed, {"city"}, {{"Group", each _, type table [bird=nullable text, city=nullable text]}}),
    FirstN = Table.AddColumn(Grouped, "FirstN", each Table.FirstN([Group],1000)),
    Combined = Table.Combine(FirstN[FirstN]),
    Sorted = Table.Sort(Combined,{{"Index", Order.Ascending}}),
    NoIndex = Table.RemoveColumns(Sorted,{"Index"})
in
    NoIndex

相关内容