☰

将重复数据导入 ms-excel 的优雅方法

tag-icon • tag-icon microsoft-excel
将重复数据导入 ms-excel 的优雅方法

问题是：包含重复数据专利的文本文件：

Object:1  
value1:abc  
value2:234  
value3:xyz  
value4:date  

Object:2  
value1:def  
value2:567   
value3:asd  
value4:date  

Object:3  
.  
.

所以。
我想将其作为如下所示的表格导入 Excel：

Object  value1 Value2 Value3 Value4  
  1         abc      234   xyz    date  
  2         def      567   asd    date  
.  
.

我发现使用偏移函数的肮脏解决方案，但我感觉有更优雅的解决方案？

答案1

这是一个更强大的选项，但我不确定它是否更优雅。

  1. 将数据拆分成列（数据 - 文本到列）
  2. 添加两个计算列：
    • 目的＃：=IF(A2="Object",B2,C1)
    • ID：=A2&"_"&C2
  3. 使用行和列标题创建目标表的骨架
  4. 输入公式并向下和向内填充 =INDEX($B:$B,MATCH(G$1&"_"&$F2,$D:$D,0))

这里的关键是正确使用绝对/相对引用 在此处输入图片描述

答案2

Máté Juhász 的答案非常可靠。如果您的数据有异常，例如缺少某些元素，他的解决方案仍会将值放到正确的位置。如果您的数据不完美，使用他的解决方案将是一个好主意。

如果您知道您的数据是干净的（没有缺失，一切都可靠地遵循模式），那么您可以使用更简单的方法，即按原样使用数据，而不需要拆分数据或辅助列。这种方法依赖于对数据单元格位置的直接引用，因此数据需要遵循模式并位于其应在的位置。

在此处输入图片描述

它基于两个公式合二为一。第一个公式从任何记录中提取值：

=MID(A1,FIND(":",A1)+1,LEN(A1))

这将查找冒号并将其右侧的所有内容取出。

第二个公式将结果矩阵中的位置与源数据单元格关联起来：

=INDIRECT("A"&(ROWS($C$2:C2)-1)*6+1+COLUMNS($C$2:C2)-1)

INDIRECT 建立单元格引用。该公式适用于六行组。结果矩阵中的第一个单元格 (C2) 指向第一个数据单元格 (A1)。右侧的每一列都会在数据中向下移动一行。结果矩阵中的每一行都会移动到下一组六行。

要根据数据和结果矩阵的实际位置进行调整：

  • 用结果矩阵的起始位置进行替换$C$2:C2，观察 $s 以获得绝对寻址。
  • 将“A”替换为源数据所在的列。
  • 末尾+1的ROWS($C$2:C2)-1)*6+1是第一行数据的行号。我的示例从第 1 行开始。

实际公式用第二个公式（给出数据单元格位置）代替A1第一个公式中的。A1 出现了三次，因此公式有点长：

=MID(INDIRECT("A"&(ROWS($C$2:C2)-1)*6+1+COLUMNS($C$2:C2)-1),FIND(":",INDIRECT("A"&(ROWS($C$2:C2)-1)*6+1+COLUMNS($C$2:C2)-1))+1,LEN(INDIRECT("A"&(ROWS($C$2:C2)-1)*6+1+COLUMNS($C$2:C2)-1)))

您可以通过替换对 A1 的三个引用中的最后一个来缩短公式。最后一个 LEN(A1) 只是为 MID 函数提供要检索的字符数。使用 LEN(A1) 可确保有足够的字符。您可以将其替换为大于您将看到的任何值的任意数字（MID 只是用尽了要检索的字符）。因此，例如，如果您使用99，第一个公式将是：

=MID(A1,FIND(":",A1)+1,99)

合并后的公式为：

=MID(INDIRECT("A"&(ROWS($C$2:C2)-1)*6+1+COLUMNS($C$2:C2)-1),FIND(":",INDIRECT("A"&(ROWS($C$2:C2)-1)*6+1+COLUMNS($C$2:C2)-1))+1,99)

在结果矩阵的第一个单元格中粘贴并调整公式，然后根据需要复制或向下拖动它。

我怀疑您的 OFFSET 公式与此处的 INDIRECT 函数类似，锚定到第一个数据单元，并根据结果矩阵的行和列从那里偏移。

相关内容