我认为您需要重新设计日历. Excel喜欢将原始数据拆分为单独的行，因此让我们开始吧.

I think you need to re-design your calendar. Excel loves raw data to be split out onto separate rows so let's start with that.

这是我的最终目标-这是您的简化版本

This is my end goal - it's a simplified version of yours

---

因此，让我们列出将我们带到那里的数据- 成行 .对于此示例，将数据粘贴到单元格A6:

So let's set out the data that will get us there - in rows. For this example, paste the data into cell A6:

Name    Day      Time In  Time Out  Total
Name 1  09/01/17    08:00   18:00   10:00
Name 1  12/01/17    11:00   15:00   04:00
Name 1  13/01/17    12:00   21:00   09:00
Name 2  10/01/17    08:00   13:00   05:00
Name 2  11/01/17    12:00   20:00   08:00
Name 2  13/01/17    10:00   14:00   04:00

然后在末尾添加另外几列，这将有助于获得总计并提高表格的可读性

And then add another few columns onto the end which will be useful in getting totals and making the table more readable

唯一ID 只需将名称和日期连接起来即可:=A7&B7.而 Time String 使用TEXT函数

Unique ID is created simply by concatenating name and day : =A7&B7. And the Time String uses the TEXT function

=UPPER(TEXT(C7,"hAM/PM")&"/"&TEXT(D7,"hAM/PM"))

---

在A1:F3中创建表格(如下所示).无需手动轮班数据，但可以将您的人员和日期标题放在:

Create your table in A1:F3 (like below). No need for manual shift data but do put your people and dates headings in:

在单元格B2中使用此简单公式，向下拖动以填充班次数据

Use this simple formula in cell B2 and drag down and across to populate shift data

=IFERROR(VLOOKUP($A2&B$1,$F$6:$G$12,2,0),"OFF")

类似地，您可以对total hours

=SUMIF($A$7:$A$12,$A2,$E$7:$E$12)*24

---

最终结果如下，可以方便地为更多的人/约会等操作.

The final result is below, and can be easily manipulated for more people/ dates, etc.

如果您对这些公式/技术有疑问-一定要问