在 Excel 中,我想根据其他单元格中的值生成动态表。
我以前从未做过这件事,所以我不知道其中涉及的术语或技术。我也不确定 Excel 是否能够完成我想要的任务。
以下是我想要做的事情:
1 – 我在旁边的单元格中输入一个值Sum
(这里是$100
)。
2 – 然后我将 100% 的百分比分配给A
,B
,C
,D
:
[Table 1]
|------|--------------|
| Sum | $100 |
|------|--------------|
|------|--------------|
| Unit | Distribution |
|------|--------------|
| A | 0.5 |
|------|--------------|
| B | |
|------|--------------|
| C | |
|------|--------------|
| D | 0.5 |
|------|--------------|
A
和D
各占 50%,和B
占C
0%。上面的表格结构应该始终相同。
现在,我想根据表 1 中输入的单元格值自动动态生成第二个表,并给出输出:
[Table 2]
|------|--------------|
| Unit | Value |
|------|--------------|
| A | $50 |
|------|--------------|
| D | $50 |
|------|--------------|
经过动态地生成表格的意思是只生成A
和的值,而不生成和,因为它们的分布值为 0%。B
C
D
另一方面,不同的分布会生成另一个表:
[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。