Excel 2019中为FILTER结果加固定文本列及数据重塑咨询

需求说明 #

你需要完成以下Excel处理任务：

1. 筛选E:N区域中F列（Project A）大于Q1值的行
2. 将筛选后的二维项目数据（每行对应多项目）重塑为长表格式：日期+描述+项目
3. 在结果末尾添加一列固定文本
4. 全程避免使用VBA

现有数据格式 #

|            | Project A            | Project B     | Project C      |
|------------|----------------------|---------------|----------------|
| 11/12/2023 | Remind PC to do this |               |                |
| 12/12/2023 |                      |               | Review meeting |
| 13/12/2023 | Start work on this   | Remember this |                |

期望输出格式 #

| Date       | Description          | Project   | 固定列 |
|------------|----------------------|-----------|--------|
| 11/12/2023 | Remind PC to do this | Project A | 固定文本|
| 12/12/2023 | Review Meeting       | Project C | 固定文本|
| 13/12/2023 | Remember this        | Project B | 固定文本|
| 13/12/2023 | Start work on this   | Project A | 固定文本|

方法一：Power Query（推荐，操作简单） #

Excel 2019自带Power Query，无需写复杂公式，步骤如下：

1. 导入数据到Power Query

   • 选中原数据区域（包含标题行），点击「数据」选项卡→「从表格/区域」
   • 弹出对话框时勾选「我的表格有标题」，点击确定进入Power Query编辑器

2. 执行筛选

   • 在编辑器中找到Project A列，点击列标题旁的筛选箭头→「数字筛选」→「大于」
   • 在弹出的对话框中，点击「值」输入框旁的下拉按钮→「从单元格加载」→选择Q1单元格，点击确定，此时会保留所有Project A列值大于Q1的行

3. 重塑数据为长表

   • 选中日期列（原表格第一列，Power Query会自动命名为Column1）
   • 按住Ctrl选中其他所有项目列（Project A、Project B...）
   • 点击「转换」选项卡→「逆透视列」→「逆透视其他列」
   • 此时会生成三列：Column1（日期）、属性（项目名称）、值（描述），可以右键重命名为Date、Project、Description

4. 添加固定文本列

   • 点击「添加列」选项卡→「自定义列」
   • 在弹出的对话框中，输入公式：= "你的固定文本内容"，点击确定，新列会自动填充固定文本

5. 导出结果到Excel

   • 点击「开始」选项卡→「关闭并上载」，选择将结果加载到新工作表或指定位置

方法二：数组公式（适合熟悉函数的用户） #

注意：Excel 2019无FILTER动态数组函数，需用传统数组公式实现筛选+重塑，所有公式需按Ctrl+Shift+Enter确认（输入公式后按住三键回车）

假设原数据在E1:N100，目标结果从P2开始：

1. 日期列（P2）

   =IFERROR(INDEX($E:$E,SMALL(IF(($F:$N<>"")*($F:$F>$Q$1),ROW($F:$N)),ROW(A1))),"")
   

   下拉填充到出现空值，提取所有符合条件的非空内容对应的日期

2. 描述列（Q2）

   =IFERROR(INDEX($F:$N,SMALL(IF(($F:$N<>"")*($F:$F>$Q$1),ROW($F:$N)),ROW(A1)),SMALL(IF(($F:$N<>"")*($F:$F>$Q$1),COLUMN($F:$N)-COLUMN($F)+1),ROW(A1))),"")
   

   下拉填充，提取对应的描述内容

3. 项目列（R2）

   =IFERROR(INDEX($F$1:$N$1,1,SMALL(IF(($F:$N<>"")*($F:$F>$Q$1),COLUMN($F:$N)-COLUMN($F)+1),ROW(A1))),"")
   

   下拉填充，提取对应的项目名称

4. 固定文本列（S2）
   直接输入你需要的固定文本（比如"备注"），下拉填充即可

内容的提问来源于stack exchange，提问作者Fabio Tozzi