☰

如何从 Excel 中的字符串中提取名称/值对？

tag-icon • tag-icon microsoft-excel
如何从 Excel 中的字符串中提取名称/值对？

我在 F48 中以 {year, value} 的形式将数据括在花括号中，如下所示：

在此处输入图片描述

我想编写一个公式，从 2017 年的值中减去 1990 年的值，然后将差额除以 1990 年的值，但我不知道如何做。

我认为最简单的方法是使用 INDEX 和 MATCH 函数。在需要结果的单元格中，输入以下公式：

=(INDEX(karnkraft,MATCH(2017,karnkraft,0),2)-INDEX(karnkraft,MATCH(1990,karnkraft,0),2))/INDEX(karnkraft,MATCH(1990,karnkraft,0),2)

此公式将在 karnkraft 数据中查找 2017 年的值并减去 1990 年的值，然后将该差值除以 1990 年的值。不会吗？

更进一步，如何以以下方式获取形式为 {year, value} 的新列中的值：

  1. 商将与 1990 年和 2017 年的值相乘以获得新的最小值和最大值。
  2. 我们将使用最大值和最小值作为参考，以{年份，值}的形式获取 1990 年至 2017 年期间这些数字之间的随机值。
  3. 我将针对不同的数据进行冲洗并重复。

答案1

问题应修改为：
如何从 Excel 中的字符串中提取名称/值对？

=MID(C1,
      SEARCH("{"&A1, C1)+LEN(A1)+2,
      SEARCH("}", C1, SEARCH("{"&A1, C1))-1-SEARCH("{"&A1, C1)-LEN(A1)-2)

解释

假设单个单元格“C1”中有以下数据/字符串

karnkraft = {{1990, 2000.642591}，{1991, 2096.356868}，{1992, 2112.277946}，{1993, 2185.016841}，{1994, 2226.050783}，{1995, 2641.599256}，{2004, 2757.124087}，{2005, 2746.479825}，{2008, 2737.860822}，{2009, 2575.664304}，{2016, 2612.83283}，{2017, 2322.592422}， {1996, 2407.002623}, {1997, 2390.480054}, {1998, 2431.571247}, {1999, 2524.546817}, {2000, 2580.976669}, {2001, 2653.821898}, {2002, 2696.204132}, {2003, 2769.046942}, {2006, 2803.605088}, {2007, 2699.245242}, {2010, 2767.507814}, {2011, 2651.771616}, {2012, 2472.44864}，{2013，2491.705601}，{2014，2541.027341}，{2015，2635.561104}}；

您想要从 NAME VALUE 对中提取一个 VALUE，其中 NAME 是年份。

  1. 在单元格“A1”中，我们将输入要查找的名称（年份），在本例中为“1991”

  2. 然后，我们要找到 NAME 放在“A1”中的位置（本例中为“1991”）。为了找到 NAME '1991'，我们搜索“{1991”。我们包括“{”，因为 VALUES 也是数字，我们不希望出现误报。没有 VALUES 以“{”开头。

 =SEARCH("{"&A1, C1) 
 =SEARCH("{1991",C1)
 =33
  1. 为了确定我们的 VALUE 最左边的位置，我们使用步骤 #2 (33) 中 NAME 的位置，并将该数字增加其长度 (LEN) 以及任何额外的空格、标点符号等。
 =33 + length of 'A1' + 2   // + 2 char for ", "
 =33 + len(A1) + 2
 =33 + 4 + 2
 =39

  
Position:  33   39
String: ...{1991, 2096.356868}... 
Count:      123456
  1. 为了获取 VALUE 最右边的位置。我们搜索紧跟步骤 #2 中 NAME 位置的“}”的位置 (33)。
 =SEARCH("}", C1, 33)
 =51

  
Position:  33               51 // next "}"
String: ...{1991, 2096.356868}...
  1. 我们现在可以通过从步骤 #4 的最右边位置 (51) - 1 中减去步骤 #3 的最左边位置 (39) 来确定 VALUE 的长度 (LEN) （因为我们不想要跟在 VALUE 后面的“}”：
 =51-1-39   // left-most - 1 - right-most
 =11        // LEN of VALUE

  
Position:  33                           51 // next "}"
String: ...{1991, 2 0 9 6 . 3 5 6 8 6  8 }... 
                  1 2 3 4 5 6 7 8 9 10 11
  1. 剩下的就是将结果插入 MID 并提取我们的值：
 =MID(text, start_num, num_chars)
 =MID(C1, 39, 11)
 =2096.356868
  1. 您可以使用辅助单元格来重复公式的部分，但是没有任何辅助单元格的完整公式是：
 =MID(text,
      start_num,
      num_chars)
 =MID(C1,
      SEARCH("{"&A1, C1)+LEN(A1)+2,
      SEARCH("}", C1, SEARCH("{"&A1, C1))-1-SEARCH("{"&A1, C1)-LEN(A1)-2)
 =MID(C1,
      33+LEN("1991")+2,
      SEARCH("}", C1, SEARCH("{1991", C1))-1-SEARCH("{1991", C1)-LEN("1991")-2)
 =MID(C1,
      33+4+2,
      51-1-33-4-2)
 =MID(C1,
      39,
      51-1-33-4-2)
 =2096.35686

在此处输入图片描述

更好的数据存储方式

鉴于这是一个电子表格，将数据对存储在表中更有意义，并且有助于提取和其他计算。

以下是我推测的两个单元格中一半的数据的示例，应用了 3 种不同的排序。数据是可访问的，您可以执行大量计算（包括索引/匹配）。 样本数据图像

答案2

据我了解，数据以“karnkraft”开头，然后是一个空格，“=”和括号值前的另一个空格，如果您有新的文本函数，以下内容将起作用：

=LET(YearEnd,  2017,  YearStart,  1990,
     String,  SUBSTITUTE(LEFT(RIGHT(F48,LEN(F48)-14),LEN(RIGHT(F48,LEN(F48)-14))-2),"}, {","ϣ"),
     Table,  VALUE(TEXTSPLIT(String,", ","ϣ",TRUE,1)),
     End,  VLOOKUP(YearEnd,Table,2,FALSE),  Start,  VLOOKUP(YearStart,Table,2,FALSE),

     End - Start
     )

有人可能会想知道为什么VLOOKUP使用而不是（通常）更好的XLOOKUP（或替换的函数对和它有与此处相同的问题INDEX/MATCH）。原因是Table公式内部虚拟创建的是单个项目，因此引用它XLOOKUP的查找数组很容易，但引用它的返回数组却不是。VLOOKUP让您考虑整个实体，因为您返回其中的一列，而不是引用的列。

当然，关键在于TEXTSPLIT让字符串更美观。不过这需要准备，所以字符串是通过用 删除前导字符（包括“{{”）形成的RIGHT。（并且，如果删除的“14”不是 而是 ，则需要对其进行更改karnkraft = {{。karnkraft={{）然后用 删除结尾的“}}” LEFT。

最后，由于将其拆分成两列的行所需的分隔符都包含,(逗号空格)（一个是逗号空格，另一个是用“}{”括起来的），因此其中一个需要替换为一些唯一的且不包含 的分隔符,。因此是SUBSTITUTE。

所有这些都是LET为了便于阅读。如果一个人也没有，那么只需要在变量出现的地方替换变量的公式即可。这看起来会很复杂，很麻烦，但其实是一样的。（我通常会说“大多数”，因为LET允许进行一次计算，然后结果在公式中使用多次，这可以导致更快的公式，在大量使用时，可以使电子表格的响应速度更快。但在这种情况下，实际上没有什么会被使用超过一次，所以它的主要优点是可读性。）

如果我误解了数据源，它就无法工作。但很可能您可以对其进行小范围的修改，并使其在实际数据集上工作。

相关内容