我有多个商品,它们在多个配送中心有售(即多对多关系)。目前每个商品占一行,每个配送中心占一列。商品行中的单元格 X以及配送中心栏 是标有配送中心代码是如果项目 X在那里可用,否则为空白。具有多个配送中心的项目将具有多个配送中心代码(在各自的列中)。因此,当前工作表如下所示:
| A | B |*| S-AJ |
1 | ID # | Description |…| Distribution Centers |
2 | 17 | Ginkgo Biloba |…| | | | | | | SE |
3 | 42 | Ginseng |…| | MP | MS | | NW | | |
︙
C
到 的列R
包含商品的其他属性,例如 UPC 代码、成本和价格,这些属性与此问题无关。我的实际工作表有 18 个配送中心,跨越到 的列S
;AJ
我减少了这些列以使示例适合 Stack Exchange 的窗口。
我需要有一个单独的配送中心列,每行只有一个配送代码,然后根据需要复制当前包含多个代码的商品的行。结果应如下所示:
| A | B |*| S |
1 | ID # | Description |…| DC |
2 | 17 | Ginkgo Biloba |…| SE |
3 | 42 | Ginseng |…| MP |
4 | 42 | Ginseng |…| MS |
5 | 42 | Ginseng |…| NW |
︙
其中单元格A3:R3
、A4:R4
和A5:R5
包含相同的信息。
我能想到的唯一方法是将商品编号复制到多行中,这会很耗时;在包含配送代码的列中,我将更改每个配送中心可用的商品的代码。我将对 900 件商品执行此操作。有没有更简单的方法可以做到这一点?
答案1
- 创建新工作表。复制标题行、列宽和格式,但不要复制列
T
-AJ
。最简单的方法是复制整个工作表,然后删除除 1 之外的所有行并取消合并列S
-AJ
。 首先,我们要将 中的每个项目行复制到 18 次 — 每个配送中心一次。输入。将 上的第 2-19 行映射到
Sheet1
上的第 2 行,将 上的第 20-37 行映射到 上的第 3 行,等等。这会导致 Excel 在引用 中的空白单元格时显示空白。如果 中没有任何空白,则可以将其关闭。如果您不喜欢这个特定的解决方案,您可以使用 中的其他解决方案之一Sheet2
=INDEX(Sheet1!A:A, INT((ROW()-2)/18)+2, 1) & ""
Sheet2!A2
INT((ROW()-2)/18)+2
Sheet2
Sheet1
Sheet2
Sheet1
& ""
Sheet1
Sheet1
在 Excel 中引用空白单元格时显示空白。将其拖拽/填充至右侧单元格
R2
。- 输入
=INDEX(Sheet1!$S:$AJ, INT((ROW()-2)/18)+2, MOD(ROW()-2, 18)+1)
。这将引用与上述公式Sheet2!S2
中的同一行,但从中获取值, 从 中获取值, 从 中获取值,等等。这将显示s 表示空白。Sheet1
Sheet2!S2
Sheet1!S2
Sheet2!S3
Sheet1!T2
Sheet2!S4
Sheet1!U2
0
- 选择整行
A2:S2
并向下拖动/填充以获取所有数据。这将需要是行数的 18 倍Sheet1
;即 18×900=16200。 - 复制所有
Sheet2
并粘贴值。 - 过滤列
S
。仅显示零。删除所有行(第 1 行除外)。删除过滤器。
完毕。
答案2
更容易解决,但需要更多的手动工作:每次迭代时复制唯一 ID x 次数(其中 x 是要转换为行的列数),每次使用要转换为行的列应用 vlookup