我有一张表格，其中包含按商店划分的每周工作时间数据集。它们都在一张表格中，按商店分隔（参见第一行第三列中的第一个站点：“ID”编号）。目前，在我的文件中，第一列是空白的。我怎样才能用与行相对应的站点：“ID”编号（即当前商店的站点编号）填充它，并跳过具有站点：“ID”、期间：“NUM”和年份：“YEAR”的商店之间的行。
我已经提供了我希望工作表在转换之后的样子。
什么公式可以让我“填充”插入站点：“ID”的每一行，然后跳过两行，插入文本“站点”，然后插入相应的站点：“ID”。
以下是数据集的一个示例：
+-------+-----------+-----------+--------------+----------------+----------+-------------+---------------+--------------+
| Site: | 05309 | | Period: | 3 | | Year: | 2019 | |
+-------+-----------+-----------+--------------+----------------+----------+-------------+---------------+--------------+
| | | | | | | | | |
| Week | Date | Day | WeekOriginal | WeekForecasted | DailyAvg | DayOriginal | DayForecasted | DayScheduled |
| 1 | 4/7/2019 | Sunday | 252.61 | 357.00 | 10.60% | 26.78 | 37.84 | 37.00 |
| 1 | 4/8/2019 | Monday | 252.61 | 357.00 | 13.40% | 33.84 | 47.82 | 58.50 |
| 1 | 4/9/2019 | Tuesday | 252.61 | 357.00 | 13.60% | 34.35 | 48.54 | 52.00 |
| 1 | 4/10/2019 | Wednesday | 252.61 | 357.00 | 15.16% | 38.30 | 54.12 | 63.00 |
| 1 | 4/11/2019 | Thursday | 252.61 | 357.00 | 15.31% | 38.69 | 54.67 | 50.00 |
| 1 | 4/12/2019 | Friday | 252.61 | 357.00 | 17.87% | 45.15 | 63.80 | 52.00 |
| 1 | 4/13/2019 | Saturday | 252.61 | 357.00 | 14.06% | 35.52 | 50.19 | 39.00 |
| 2 | 4/14/2019 | Sunday | 252.71 | 357.00 | 10.60% | 26.79 | 37.84 | 30.00 |
| 2 | 4/15/2019 | Monday | 252.71 | 357.00 | 13.40% | 33.85 | 47.82 | 52.50 |
| 2 | 4/16/2019 | Tuesday | 252.71 | 357.00 | 13.60% | 34.36 | 48.54 | 54.50 |
| 2 | 4/17/2019 | Wednesday | 252.71 | 357.00 | 15.16% | 38.31 | 54.12 | 64.00 |
| 2 | 4/18/2019 | Thursday | 252.71 | 357.00 | 15.31% | 38.70 | 54.67 | 63.00 |
| 2 | 4/19/2019 | Friday | 252.71 | 357.00 | 17.87% | 45.16 | 63.80 | 65.00 |
| 2 | 4/20/2019 | Saturday | 252.71 | 357.00 | 14.06% | 35.53 | 50.19 | 31.00 |
| 3 | 4/21/2019 | Sunday | 269.28 | 357.00 | 10.60% | 28.54 | 37.84 | 29.00 |
| 3 | 4/22/2019 | Monday | 269.28 | 357.00 | 13.40% | 36.07 | 47.82 | 60.50 |
| 3 | 4/23/2019 | Tuesday | 269.28 | 357.00 | 13.60% | 36.62 | 48.54 | 59.50 |
| 3 | 4/24/2019 | Wednesday | 269.28 | 357.00 | 15.16% | 40.82 | 54.12 | 63.25 |
| 3 | 4/25/2019 | Thursday | 269.28 | 357.00 | 15.31% | 41.24 | 54.67 | 58.50 |
| 3 | 4/26/2019 | Friday | 269.28 | 357.00 | 17.87% | 48.12 | 63.80 | 63.00 |
| 3 | 4/27/2019 | Saturday | 269.28 | 357.00 | 14.06% | 37.86 | 50.19 | 35.50 |
| 4 | 4/28/2019 | Sunday | 274.62 | 357.00 | 10.60% | 29.11 | 37.84 | - |
| 4 | 4/29/2019 | Monday | 274.62 | 357.00 | 13.40% | 36.79 | 47.82 | - |
| 4 | 4/30/2019 | Tuesday | 274.62 | 357.00 | 13.60% | 37.34 | 48.54 | - |
| 4 | 5/1/2019 | Wednesday | 274.62 | 357.00 | 15.16% | 41.63 | 54.12 | - |
| 4 | 5/2/2019 | Thursday | 274.62 | 357.00 | 15.31% | 42.06 | 54.67 | - |
| 4 | 5/3/2019 | Friday | 274.62 | 357.00 | 17.87% | 49.08 | 63.80 | - |
| 4 | 5/4/2019 | Saturday | 274.62 | 357.00 | 14.06% | 38.61 | 50.19 | - |
| 5 | | | | | | | | |
| 5 | | | | | | | | |
| 5 | | | | | | | | |
| 5 | | | | | | | | |
| 5 | | | | | | | | |
| 5 | | | | | | | | |
| 5 | | | | | | | | |
| Site: | 05309 | | Period: | 2 | | Year: | 2019 | |
| | | | | | | | | |
| Week | Date | Day | WeekOriginal | WeekForecasted | DailyAvg | DayOriginal | DayForecasted | DayScheduled |
| 1 | 3/3/2019 | Sunday | 342.21 | 357.00 | 10.66% | 36.49 | 38.06 | 27.00 |
| 1 | 3/4/2019 | Monday | 342.21 | 357.00 | 14.27% | 48.82 | 50.93 | 59.50 |
| 1 | 3/5/2019 | Tuesday | 342.21 | 357.00 | 14.41% | 49.30 | 51.43 | 51.00 |
| 1 | 3/6/2019 | Wednesday | 342.21 | 357.00 | 14.82% | 50.71 | 52.91 | 48.00 |
| 1 | 3/7/2019 | Thursday | 342.21 | 357.00 | 15.67% | 53.64 | 55.96 | 44.00 |
| 1 | 3/8/2019 | Friday | 342.21 | 357.00 | 17.26% | 59.07 | 61.62 | 47.00 |
| 1 | 3/9/2019 | Saturday | 342.21 | 357.00 | 12.91% | 44.18 | 46.09 | 31.00 |
| 2 | 3/10/2019 | Sunday | 347.79 | 357.00 | 10.66% | 37.08 | 38.06 | 27.00 |
| 2 | 3/11/2019 | Monday | 347.79 | 357.00 | 14.27% | 49.61 | 50.93 | 65.00 |
| 2 | 3/12/2019 | Tuesday | 347.79 | 357.00 | 14.41% | 50.11 | 51.43 | 59.00 |
| 2 | 3/13/2019 | Wednesday | 347.79 | 357.00 | 14.82% | 51.54 | 52.91 | 59.00 |
| 2 | 3/14/2019 | Thursday | 347.79 | 357.00 | 15.67% | 54.51 | 55.96 | 51.00 |
| 2 | 3/15/2019 | Friday | 347.79 | 357.00 | 17.26% | 60.03 | 61.62 | 57.00 |
| 2 | 3/16/2019 | Saturday | 347.79 | 357.00 | 12.91% | 44.90 | 46.09 | 31.00 |
| 3 | 3/17/2019 | Sunday | 328.22 | 357.00 | 10.66% | 34.99 | 38.06 | 27.00 |
| 3 | 3/18/2019 | Monday | 328.22 | 357.00 | 14.27% | 46.82 | 50.93 | 60.50 |
| 3 | 3/19/2019 | Tuesday | 328.22 | 357.00 | 14.41% | 47.29 | 51.43 | 55.00 |
| 3 | 3/20/2019 | Wednesday | 328.22 | 357.00 | 14.82% | 48.64 | 52.91 | 53.00 |
| 3 | 3/21/2019 | Thursday | 328.22 | 357.00 | 15.67% | 51.45 | 55.96 | 53.50 |
| 3 | 3/22/2019 | Friday | 328.22 | 357.00 | 17.26% | 56.65 | 61.62 | 61.00 |
| 3 | 3/23/2019 | Saturday | 328.22 | 357.00 | 12.91% | 42.38 | 46.09 | 37.00 |
| 4 | 3/24/2019 | Sunday | 384.47 | 357.00 | 10.66% | 40.99 | 38.06 | 34.00 |
| 4 | 3/25/2019 | Monday | 384.47 | 357.00 | 14.27% | 54.85 | 50.93 | 61.50 |
| 4 | 3/26/2019 | Tuesday | 384.47 | 357.00 | 14.41% | 55.39 | 51.43 | 64.00 |
| 4 | 3/27/2019 | Wednesday | 384.47 | 357.00 | 14.82% | 56.98 | 52.91 | 72.00 |
| 4 | 3/28/2019 | Thursday | 384.47 | 357.00 | 15.67% | 60.26 | 55.96 | 62.50 |
| 4 | 3/29/2019 | Friday | 384.47 | 357.00 | 17.26% | 66.36 | 61.62 | 54.50 |
| 4 | 3/30/2019 | Saturday | 384.47 | 357.00 | 12.91% | 49.64 | 46.09 | 40.00 |
| 5 | 3/31/2019 | Sunday | 364.38 | 357.00 | 10.66% | 38.85 | 38.06 | 16.00 |
| 5 | 4/1/2019 | Monday | 364.38 | 357.00 | 14.27% | 51.98 | 50.93 | 3.00 |
| 5 | 4/2/2019 | Tuesday | 364.38 | 357.00 | 14.41% | 52.50 | 51.43 | 3.00 |
| 5 | 4/3/2019 | Wednesday | 364.38 | 357.00 | 14.82% | 54.00 | 52.91 | 3.00 |
| 5 | 4/4/2019 | Thursday | 364.38 | 357.00 | 15.67% | 57.11 | 55.96 | 3.00 |
| 5 | 4/5/2019 | Friday | 364.38 | 357.00 | 17.26% | 62.89 | 61.62 | 3.00 |
| 5 | 4/6/2019 | Saturday | 364.38 | 357.00 | 12.91% | 47.05 | 46.09 | 15.00 |
| Site: | 05309 | | Period: | 1 | | Year: | 2019 | |
| | | | | | | | | |
| Week | Date | Day | WeekOriginal | WeekForecasted | DailyAvg | DayOriginal | DayForecasted | DayScheduled |
| 1 | 2/3/2019 | Sunday | 255.95 | 357.00 | 11.38% | 29.12 | 40.61 | 14.00 |
| 1 | 2/4/2019 | Monday | 255.95 | 357.00 | 13.10% | 33.53 | 46.76 | 43.50 |
| 1 | 2/5/2019 | Tuesday | 255.95 | 357.00 | 14.98% | 38.35 | 53.50 | 54.00 |
| 1 | 2/6/2019 | Wednesday | 255.95 | 357.00 | 15.85% | 40.57 | 56.59 | 48.00 |
| 1 | 2/7/2019 | Thursday | 255.95 | 357.00 | 15.22% | 38.96 | 54.34 | 38.00 |
| 1 | 2/8/2019 | Friday | 255.95 | 357.00 | 16.58% | 42.43 | 59.18 | 47.00 |
| 1 | 2/9/2019 | Saturday | 255.95 | 357.00 | 12.89% | 32.99 | 46.01 | 31.00 |
| 2 | 2/10/2019 | Sunday | 267.99 | 357.00 | 11.38% | 30.49 | 40.61 | 29.00 |
| 2 | 2/11/2019 | Monday | 267.99 | 357.00 | 13.10% | 35.10 | 46.76 | 58.50 |
| 2 | 2/12/2019 | Tuesday | 267.99 | 357.00 | 14.98% | 40.16 | 53.50 | 47.50 |
| 2 | 2/13/2019 | Wednesday | 267.99 | 357.00 | 15.85% | 42.48 | 56.59 | 66.00 |
| 2 | 2/14/2019 | Thursday | 267.99 | 357.00 | 15.22% | 40.79 | 54.34 | 46.50 |
| 2 | 2/15/2019 | Friday | 267.99 | 357.00 | 16.58% | 44.43 | 59.18 | 54.50 |
| 2 | 2/16/2019 | Saturday | 267.99 | 357.00 | 12.89% | 34.54 | 46.01 | 31.00 |
| 3 | 2/17/2019 | Sunday | 256.49 | 357.00 | 11.38% | 29.18 | 40.61 | 26.50 |
| 3 | 2/18/2019 | Monday | 256.49 | 357.00 | 13.10% | 33.60 | 46.76 | 26.50 |
| 3 | 2/19/2019 | Tuesday | 256.49 | 357.00 | 14.98% | 38.43 | 53.50 | 46.50 |
| 3 | 2/20/2019 | Wednesday | 256.49 | 357.00 | 15.85% | 40.66 | 56.59 | 52.00 |
| 3 | 2/21/2019 | Thursday | 256.49 | 357.00 | 15.22% | 39.04 | 54.34 | 54.50 |
| 3 | 2/22/2019 | Friday | 256.49 | 357.00 | 16.58% | 42.52 | 59.18 | 42.50 |
| 3 | 2/23/2019 | Saturday | 256.49 | 357.00 | 12.89% | 33.06 | 46.01 | 30.50 |
| 4 | 2/24/2019 | Sunday | 266.41 | 357.00 | 11.38% | 30.31 | 40.61 | 27.00 |
| 4 | 2/25/2019 | Monday | 266.41 | 357.00 | 13.10% | 34.90 | 46.76 | 56.00 |
| 4 | 2/26/2019 | Tuesday | 266.41 | 357.00 | 14.98% | 39.92 | 53.50 | 50.00 |
| 4 | 2/27/2019 | Wednesday | 266.41 | 357.00 | 15.85% | 42.23 | 56.59 | 55.00 |
| 4 | 2/28/2019 | Thursday | 266.41 | 357.00 | 15.22% | 40.55 | 54.34 | 64.00 |
| 4 | 3/1/2019 | Friday | 266.41 | 357.00 | 16.58% | 44.17 | 59.18 | 61.00 |
| 4 | 3/2/2019 | Saturday | 266.41 | 357.00 | 12.89% | 34.34 | 46.01 | 39.00 |
| 5 | | | | | | | | |
| 5 | | | | | | | | |
| 5 | | | | | | | | |
| 5 | | | | | | | | |
| 5 | | | | | | | | |
| 5 | | | | | | | | |
| 5 | | | | | | | | |
| Site: | 05305 | | Period: | 3 | | Year: | 2019 | |
| | | | | | | | | |
| Week | Date | Day | WeekOriginal | WeekForecasted | DailyAvg | DayOriginal | DayForecasted | DayScheduled |
| 1 | 4/7/2019 | Sunday | 50.18 | 80.00 | 0.00% | - | - | - |
| 1 | 4/8/2019 | Monday | 50.18 | 80.00 | 19.77% | 9.92 | 15.82 | 16.00 |
| 1 | 4/9/2019 | Tuesday | 50.18 | 80.00 | 19.80% | 9.93 | 15.84 | 16.00 |
| 1 | 4/10/2019 | Wednesday | 50.18 | 80.00 | 20.67% | 10.37 | 16.53 | 16.00 |
| 1 | 4/11/2019 | Thursday | 50.18 | 80.00 | 25.40% | 12.74 | 20.32 | 16.00 |
| 1 | 4/12/2019 | Friday | 50.18 | 80.00 | 14.36% | 7.21 | 11.49 | 16.00 |
| 1 | 4/13/2019 | Saturday | 50.18 | 80.00 | 0.00% | - | - | - |
| 2 | 4/14/2019 | Sunday | 55.70 | 80.00 | 0.00% | - | - | - |
| 2 | 4/15/2019 | Monday | 55.70 | 80.00 | 19.77% | 11.01 | 15.82 | 16.00 |
| 2 | 4/16/2019 | Tuesday | 55.70 | 80.00 | 19.80% | 11.03 | 15.84 | 16.00 |
| 2 | 4/17/2019 | Wednesday | 55.70 | 80.00 | 20.67% | 11.51 | 16.53 | 16.00 |
| 2 | 4/18/2019 | Thursday | 55.70 | 80.00 | 25.40% | 14.15 | 20.32 | 16.00 |
| 2 | 4/19/2019 | Friday | 55.70 | 80.00 | 14.36% | 8.00 | 11.49 | 16.00 |
| 2 | 4/20/2019 | Saturday | 55.70 | 80.00 | 0.00% | - | - | - |
| 3 | 4/21/2019 | Sunday | 52.49 | 80.00 | 0.00% | - | - | - |
| 3 | 4/22/2019 | Monday | 52.49 | 80.00 | 19.77% | 10.38 | 15.82 | 16.00 |
| 3 | 4/23/2019 | Tuesday | 52.49 | 80.00 | 19.80% | 10.39 | 15.84 | 16.00 |
| 3 | 4/24/2019 | Wednesday | 52.49 | 80.00 | 20.67% | 10.85 | 16.53 | 16.00 |
| 3 | 4/25/2019 | Thursday | 52.49 | 80.00 | 25.40% | 13.33 | 20.32 | 16.00 |
| 3 | 4/26/2019 | Friday | 52.49 | 80.00 | 14.36% | 7.54 | 11.49 | 16.00 |
| 3 | 4/27/2019 | Saturday | 52.49 | 80.00 | 0.00% | - | - | - |
| 4 | 4/28/2019 | Sunday | 61.41 | 80.00 | 0.00% | - | - | - |
| 4 | 4/29/2019 | Monday | 61.41 | 80.00 | 19.77% | 12.14 | 15.82 | 16.00 |
| 4 | 4/30/2019 | Tuesday | 61.41 | 80.00 | 19.80% | 12.16 | 15.84 | 16.00 |
| 4 | 5/1/2019 | Wednesday | 61.41 | 80.00 | 20.67% | 12.69 | 16.53 | 16.00 |
| 4 | 5/2/2019 | Thursday | 61.41 | 80.00 | 25.40% | 15.60 | 20.32 | 16.00 |
| 4 | 5/3/2019 | Friday | 61.41 | 80.00 | 14.36% | 8.82 | 11.49 | 16.00 |
| 4 | 5/4/2019 | Saturday | 61.41 | 80.00 | 0.00% | - | - | - |
| 5 | | | | | | | | |
| 5 | | | | | | | | |
| 5 | | | | | | | | |
| 5 | | | | | | | | |
| 5 | | | | | | | | |
| 5 | | | | | | | | |
| 5 | | | | | | | | |
| Site: | 05305 | | Period: | 2 | | Year: | 2019 | |
| | | | | | | | | |
| Week | Date | Day | WeekOriginal | WeekForecasted | DailyAvg | DayOriginal | DayForecasted | DayScheduled |
| 1 | 3/3/2019 | Sunday | 81.38 | 80.00 | 0.00% | - | - | - |
| 1 | 3/4/2019 | Monday | 81.38 | 80.00 | 20.24% | 16.47 | 16.19 | 16.00 |
+-------+-----------+-----------+--------------+----------------+----------+-------------+---------------+--------------+```
答案1
进入=IF(OR(B4="Site:",B4=""), "", IF(B4="Week", "Site", IF(B1="Site:",C1,A3)&""))并向A4下拖动/填充。
这说明，
- 如果我们位于其中一个预标题行（即，提供“站点”，“期间”和“年份”的行，或完全空白行；即，“周”/“日期”/“天”...标题行之前的行），则显示空白。
- 如果我们位于标题行（显示“周”/“日期”/“天”），则显示“站点”。
- 如果我们位于该商店本月的第一行数据（即第三行在列中显示“站点：”
B），则显示相邻单元格中的站点值（即C1），否则 A3根据上一个项目符号，显示上面一行 ( ) 中的值，该值是此商店的站点编号。- 强制
&""将站点号码视为字符串而不是数字，以阻止 Excel 丢弃前导零。
答案2
看起来这个日期是以页面为单位的——所以我将使用术语“页面”来表示与网站相关的每组数据。
我假设这些页面的布局总是相同的（页眉中的站点编号始终位于第二行和第三列）。这意味着我们可以使用MOD()R1C1 符号来查找每个站点的站点值单元格。
为了简洁起见，我缩短了您的数据集。
在解答之前，您需要了解以下部分。（注意：由于我的屏幕截图中 C2 中的值，这三个示例均返回“1”）：
该INDIRECT()公式允许以编程方式构建带有单元格引用的字符串。例如，我们可以取字符串“C”并将其与字符串“2”连接起来以获取对单元格 C2 的引用：
=INDIRECT("C" & "2")
我们可以将其更改为使用 R1C1 表示法（通过传递 FALSE）。在 R1C1 表示法中，以下公式引用单元格 C2（R2C3 表示行：2，列：3）
=INDIRECT("R2C3", FALSE)
R1C1 符号还允许我们通过相对引用来引用单元格。例如，使用下面的示例屏幕截图，我们可以使用以下公式来引用单元格 C2，因为它距离单元格 A5 有 -2 行和 +2 列：
=INDIRECT("R[-3]C[2]",FALSE)
这是您将“填写”以获取站点值的最终公式：
=INDIRECT("R["& 2 - MOD(ROW(),17) &"]C3",FALSE)
最终公式计算的细目如下：
以下是一个更简单的数据源的概念验证。我添加了额外的列来向您展示公式各部分的计算方式：
答案3
我只是假设您想要一个“整洁数据”样式的结果。
对于 Power Query 解决方案，我首先会添加一个计算列站点.1使用这个表达式：
if [Column1] = "Site:" then [Column1] = "Site:" then [Column2] else null
然后我会转换站点.1到文本并使用向下填充。
然后我将添加另一个计算列地点使用这个表达式：
if [Column1] = "Week" then "Site" else [Site.1]
然后我会删除站点.1列，然后过滤列 1删除“站点：”并将其设为空。
然后我会提升标题 - 现在列将被命名周、日期等等，以及地点列出现在每一行上。
最后一步是过滤星期删除“周”（剩余的标题行）。