☰

操作相对于彼此的多个 Excel 列

tag-icon • tag-icon microsoft-excel-2007
操作相对于彼此的多个 Excel 列

（我最初将其发布为“排序”项目。但数据在开始时就已排序。这实际上更像是在需要的地方插入空白单元格的操作。）

我有多列带时间戳的数据。每列中的数据已按从最旧到最新的顺序排序。如果需要，可以将其反转为从最新到最旧的顺序，我可以在此请求之外执行此操作。一列中的时间戳可能会或可能不会在其他列中重复。我需要操纵列的数据，以便查看一行中的所有列，时间戳将始终随着行向下移动而递增。

因此，某些列中的某些数据必须向下移动，从而在该列中留下空白单元格。例如，某一列（示例中的 B）可能在 处没有数据"10-30 8:41:10"，就像另一列（示例中的 A）一样，但它在"10-30 8:41:08"和 处有数据"10-30 16:51:12"。因此，其他列中有"10-30 8:41:10"数据的行在此列中必须是空白的（例如，在下面的示例中，单元格 B3 和 B4）。

示例图片只是实际数据的一小部分样本。需要处理的真实示例有 21 列。最短的列有 62 行数据，最长的列有 462 行数据。我需要每周至少处理 3 次新数据集。我将这些数据提取为文本文件，然后将其导入 Excel。我可以根据需要分隔日期、时间或任何在处理之前需要的内容，以方便操作。时间始终采用 24 小时HH:MM:SS格式。年份无关紧要。数据始终处于较短的时间范围内。

解决方案的复杂性并不是主要问题，因为替代方法是手动对单元格进行混洗。在下一个周期到来之前，我甚至无法完成一次操作。解决方案不必是实时/连续的。我将数据加载到 Excel 中，然后对其进行一次操作。不会添加或修改其他数据。

我认为 VB 是答案，但我从未使用过它，尝试时陷入了困境。我目前正在寻找是否可以MIN/MAX在每一行上使用，然后使用 VB 在适当的列中插入和下移。我的另一个想法是使用RANK和/或“LARGE/SMALL”为每个单元格分配一个数字，然后使用其他函数将它们复制到另一张表的正确位置以完成操作。不知道这些是否会起作用，但我尝试了我能想到的任何方法。

操作前：
操作前

处理后：
经过处理

答案1

假设有 100 行数据..

放 ：

E1 ---->  =IFERROR(DATE(2018,MID(A1,1,2),MID(A1,4,2))+TIME(MID(A1,7,2),MID(A1,10,2),MID(A1,13,2)),"")

并拖至 G1，然后

I1 ---->  =IFERROR(RANK(E1,$E:$G,1),"")

并拖至 K1，然后

M1 ---->  1 (type manually)
M2 ---->  =IF((ROW()-MATCH($M1,M:M,0)+1)<=MAX(COUNTIF($I$1:$I$100,$M1),COUNTIF($J$1:$J$100,$M1),COUNTIF($K$1:$K$100,$M1)),M1,M1+COUNTIF($I$1:$K$100,$M1))

然后

P1 ---->  =IF(IF(ROW()=1,TRUE,$M1<>OFFSET($M1,-1,0)),IFERROR(INDEX(A$1:A$100,MATCH($M1,I$1:I$100,0)),""),IF($M1>MAX($I$1:$K$100),"",IF(COUNTIF(I$1:I$100,$M1)<=1,"",IF(COUNTIF(INDIRECT("M1:M"&ROW()),$M1)<=COUNTIF(I$1:I$100,$M1),INDEX(A$1:A$100,MATCH($M1,I$1:I$100,0)+COUNTIF(INDIRECT("M1:M"&ROW()),$M1)-1),""))))

并拖至 R1。

然后将 E1:K1 拖至 E1:K1 ，将 M2 拖至 M140 行，将 P1:R1 拖至 P130:R130 。

就这样就可以了。

幕后：

  • 列 EG：使用 mid() 提取日期和时间文本并使用 date()+time 将该文本转换为 Excel 时间/日期值。
  • IK 列：使用 rank() 按顺序获取时间，如果平局，则数字相同。
  • M 列：使用 countif() 保留并列出每行的“按顺序的时间”。
  • 专栏 PR：[好吧... 对此必须诚实... 这有点难... 但如果您不这样做，请告诉我（无论如何您是任务的所有者...）。希望您能更好地理解它，以便您以后可以调整它/更好地使用它。（：]

列 PR：

为了清楚起见：您可以复制公式..将其放入 notepad++ 或记事本中，然后中断（tab/enter）它。

第一步：如果这是 M 列中第一次“按顺序”出现，则在 A:C 列上使用 index()，在 I:K 列上使用 match。或者这部分IFERROR(INDEX(A$1:A$100,MATCH($M4,I$1:I$100,0)),"")

第二：如果这不是 M 列中第一次出现，则在 A:C 的每一列中使用 countif() 检查是否有更多匹配相同“日期/时间”的内容。

如果没有更多，则显示空白。如果在相应列中发现更多相同的“日期/时间”，则使用列 A:C 上的 use index()、列 I:K 上的 match（由当前offset()行列 M 的 countif() 处理）显示它。又名此部分：INDEX(A$1:A$100,MATCH($M1,I$1:I$100,0)+COUNTIF(INDIRECT("M1:M"&ROW()),$M1)-1)

相关内容