Excel:如何根据另一个表的值生成动态表

Excel:如何根据另一个表的值生成动态表

在 Excel 中,我想根据其他单元格中的值生成动态表。

我以前从未做过这件事,所以我不知道其中涉及的术语或技术。我也不确定 Excel 是否能够完成我想要的任务。

以下是我想要做的事情:

1 – 我在旁边的单元格中输入一个值Sum(这里是$100)。

2 – 然后我将 100% 的百分比分配给ABCD

[Table 1]

|------|--------------|
| Sum  |     $100     |
|------|--------------|

|------|--------------|    
| Unit | Distribution |
|------|--------------|
| A    |     0.5      |
|------|--------------|
| B    |              |
|------|--------------|
| C    |              |
|------|--------------|
| D    |     0.5      |
|------|--------------|

AD各占 50%,和BC0%。上面的表格结构应该始终相同。

现在,我想根据表 1 中输入的单元格值自动动态生成第二个表,并给出输出:

[Table 2]

|------|--------------|    
| Unit |    Value     |
|------|--------------|
| A    |     $50      |
|------|--------------|
| D    |     $50      |
|------|--------------|

经过动态地生成表格的意思是只生成A和的值,而不生成和,因为它们的分布值为 0%。BCD

另一方面,不同的分布会生成另一个表:

[Table 1]

|------|--------------|
| Sum  |     $100     |
|------|--------------|

|------|--------------|    
| Unit | Distribution |
|------|--------------|
| A    |     0.75     |
|------|--------------|
| B    |              |
|------|--------------|
| C    |     0.125    |
|------|--------------|
| D    |     0.125    |
|------|--------------|

[Table 2]

|------|--------------|    
| Unit |    Value     |
|------|--------------|
| A    |     $75      |
|------|--------------|
| C    |     $12.50   |
|------|--------------|
| D    |     $12.50   |
|------|--------------|

为什么我要动态生成表2:因为实际上我有大量潜在的“单位”需要分配,并且使用一些不同的算法,而我只希望获得一个连贯的表格输出,其值 > $0。我会将这些数字复制/粘贴到我的簿记应用程序中。

问题

1) 对于我想要实现的目标,正确的“术语”和“技术”是什么?我问这个问题是为了能够在网上找到资源,以防没有人能帮助我解决我的特定用例。我是否使用诸如数据透视表、动态范围等?(我对此没有经验). 我将非常感激尽可能具体的说明。

2) 最好的情况是,您能否为我提供上述需求的示例解决方案?如果不完全满足,是否有我应该了解的某些 Excel 工具和/或公式(例如用于值 >0 的动态筛选表)?

答案1

楼主 — 我现在已经能够很好地完成这项工作了,所以我将我的“解决方案”附上以供参考。但是,我不确定这是否是最好的方法。

我创建了两个表,其中表 2 根据表 1 中的相关公式进行计算。

然后我在表 2 中添加了一个过滤器,仅显示具有值的行大于 0根据相应列中的单元格。要添加过滤器,请按要过滤的表格列的向下箭头。

这会过滤掉具有空值的行;但是,当表 1 的数据发生变化时,它不会动态更新过滤。

为了使表 2 根据表 1 中的输入动态更新,我将文件保存为启用宏的 Excel 文件(另存为 > Excel 启用宏的工作簿,.xlsm),并从此答案中添加了 VBA 代码:https://superuser.com/a/501736/455679

要添加 VBA 代码,请右键单击 Excel 底部栏上的工作表(通常工作表1),然后点击查看代码从上下文菜单中。然后粘贴并保存以下代码,其中Table2应该是实际过滤表的名称:

Private Sub Worksheet_Change(ByVal Target As Range)
    With ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table2")
         .AutoFilter.ApplyFilter
    End With
End Sub

未过滤的值:

在此处输入图片描述

动态过滤的值:

在此处输入图片描述

请注意,由于行过滤,表 1 和表 2 不应共享相同的行 - 因为这可能会根据其自身的值过滤掉表 1。

相关内容