☰

将单列中以逗号分隔的键值对转换为不同的列

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010 microsoft-excel-2007 csv
将单列中以逗号分隔的键值对转换为不同的列

我在 Excel 表中有一列包含如下格式的值：

"Key1":"Value1","Key2":"Value2","Key3":"Value3"..

我有许多行这样的数据。我想将这些键分解成各自的列，并在相应的行中设置相应的值。如下所示：

Key1  | Key2 | Key3
------|------|------
Value1|Value2|Value3

我如何在 Excel 中最好地完成此操作？

笔记：

  • 行与行之间的键不会发生很大变化，但值会有所不同
  • 这必须在 Excel 中完成，因为数据是预定报告，我们无法控制来源如何提供数据

答案1

如果接受 excel 之外的解决方案，您只需将所有内容复制到 txt 文件，将条目分隔符 (,) 替换为换行符 (crlf)，然后将其粘贴回 excel 并使用内置的文本到列选项作为键值分隔符 (:)，
那么您就有两列键和值。标记列并复制，使用选择性粘贴选项并选择转置

图片1

图片2

图片3

图片4

答案2

在 Excel 中，您可以执行以下操作：

=TRANSPOSE(FILTERXML("<Group><colon>"&SUBSTITUTE(SUBSTITUTE(INDIRECT("$A"&(ROUNDUP(ROW()/2,0))),":","</colon><comma>"),",","</comma><colon>")&"</comma></Group>",IF(MOD(ROW(),2)=1,"/Group/colon","/Group/comma")))

这会将值分为两行，因此源数据中的每一行都需要两个输出行。我想这看起来有点奇怪，但这似乎是您所设想的。因此，第 1 行的数据出现在第 1 行和第 2 行中，第 2 行的数据出现在第 3 行和第 4 行中，依此类推。

如果您确实希望所有数据行都显示在非常宽的两行中，您可以将所有数据行连接成一个数据行。 这样做效果最好，TEXTJOIN()因为它允许您设置一个仅位于元素之间的分隔符，而CONCAT()可以将分隔符附加到末尾（CONCAT(A1:A14)&","可能是方法），但随后您必须以某种简单但额外的方式删除末尾的分隔符。

那么，公式中有什么？两个SUBSTITUTE()函数被链接在一起（一个环绕另一个）。一个将冒号更改为 HTML 标记集，另一个将逗号更改为 HTML 标记集。这现在是一种相当常见的东西，用于FILTERXML()拆分字符串（或数组常量，如使用 创建的TEXTJOIN()），但这与常规用法有点不同。

通常，您使用替换过程为分隔符插入结束标记和开始标记，然后在字符串前添加开始标记，在字符串后添加结束标记。因此，字符串前的标记打开围绕第一个元素的标记集，然后替换会在那里放置结束标记和下一个元素的新开始标记。整个字符串都是这样，直到最后，有一个开始标记，您在末尾添加的内容将其关闭。因此，如果您粘贴值，它可能看起来像这样：

`<element>SOMETHING_IN_THE_STRING</element>` (and then more of these)

无论如何，要实现这一点，您必须拥有至少两个“级别”的 HTML 标记结构。就像目录结构一样，因此我将其用于Group总体、顶级标记集和Element字符串元素。合理合乎逻辑。其他人则简单地使用a和b来减少输入，其他人则有对他们或他们的材料有意义的想法。无论如何，该两级结构被Path函数的 Intellisense 帮助称为，这是一个很好的类比。

因此，您构建了要输入的字符串FILTERXML()，并且告诉它Path，然后它就会像魔法一样工作。您甚至可以在每次使用公式时添加一点点来获取特定元素，可能是字符串中的第 7 个元素。这超出了本文的范围，但了解后会很方便。

问题是，为什么？为什么需要Path？从字符串的构造可以明显看出路径是什么！高级元素是开头和结尾的标签，低级元素是中间的标签（更可能是标签）。这有多难？

好吧，Excel 的本意是将其用于实际的网站类型的 HTML，其中可能会使用一百个标签集。不是为了这个目的。现在它有意义了，但它也表明你可以同时做两件事，而不仅仅是一件事。

而且您确实需要同时做两件事。

因此，您会看到高级标记集：组。以及两个低级标记集：冒号和逗号。这意味着，如果您可以适当地标记事物，则可以使用一个字符串构建部分来提取两个完全独立的字符串元素数组。

通常，您会看到内部替换将逗号之类的内容替换为</Element><Element>（结束标记，开始标记，但标记相同）。在这里，您需要相反的对，如您在公式中看到的，来关闭一个元素的标记并打开其他元素的标记，来回反复。您所做的其他一切都需要与此保持一致。如果您查看公式，您就会明白，没问题。

因此，现在您有一个总体高级标记集和两个低级标记集，您可以使用将其中一个或另一个IF()传递给。它测试公式是否位于对的奇数行（如果是，则给出字符串中所有对的前半部分）或对的偶数行（如果是，则给出字符串中所有对的后半部分）。因此，如果字符串是，则对的第一行给出，第二行给出。PathFILTERXML()1:2,3:41,32,4

FILTERXML()喜欢输出一列，所以TRANSPOSE()将其变成您想要的行。

SPILL功能可让您在每行中得到完整的集合，而无需{CSE}输入，耶！这样就可以填充行。但是，我尝试过的各种方法SPILL都失败了，因为它们都拒绝处理内部数组，因此我尝试让它自动填充所需的行数。解决这个问题的方法通常是使用另一个与所需输出兼容的函数来强制它，但我没有时间了。有些方法可以工作，但它要么给出这个答案，要么不给出答案，所以...

无论如何，请根据需要将公式复制到输出区域中的多个单元格中，源数据中每行两行。其中的公式将为您提供所需的输出，然后您可以使用这些输出。

相关内容