使用Excel将每小时的时间表分钟分钟

Question

我有一个巨大的数据转储，其中每行如下：

|hour|number of items|destination|

有24小时（整天）和9个不同的目的地。面临的挑战是将其拆分，以便我按项目分解。例如：

|1|5|Kitchen|
|1|2|Bedroom|

应该成为这个：

|1:10|1|Kitchen
|1:20|1|Kitchen
|1:20|1|Bedroom
|1:30|1|Kitchen
|1:40|1|Kitchen
|1:40|1|Bedroom
|1:50|1|Kitchen

我正在使用Excel绘制空白，以最好地做到这一点。如果推动推动，我也可以使用Python来转换数据，尽管不是我的首选解决方案。

Answer 1

您可以使用一个用于循环浏览所有数据，并为所有9个目的地具有计数器变量。使用拆分从数据中获取正确的数据。这是一些可以帮助您的伪代码：

HourVariable = 0
For i = 1 to endOfdata
  dHour = split(split(dataline(i),"|")(1),":")(0) 'Get the hour from the dataline
  If Hourvariable <> dHour then 'Check if the hour changed. In that case the data for that hour needs to be written and all counters go to 0
     If cKitchen > 0 then NewData = NewData & "|" & dHour & "|" & cKitchen & "|Kitchen|" & vbNewLine
     If cBedroom > 0 etc...    
     cKitchen = 0
     cBedroom = 0
     HourVariable = dHour
     Select case split(dataline(i),"|")(3) 'Check which destination is in the current dataline
       Case "Kitchen": cKitchen = cKitchen + 1
       Case "Bedroom": etc...
     End Select
  Else 'In other cases, just add to the counters
     Select case split(dataline(i),"|")(3) 'Check which destination is in the current dataline
       Case "Kitchen": cKitchen = cKitchen + 1
       Case "Bedroom": etc...
     End Select
  End if
next i