因此，我尝试在 Excel 中为自己制作一个食物计划器，并且我已经设置好了一切。我有一个食物清单，为了简单起见，我使用卡路里来定义食物的名称。
然后，我尝试让它在每周计划中引用该项目 - 例如，我可以把“香蕉”作为周六早餐，它会显示为香蕉，并具有隐藏或指定的 92 卡路里值。然后通过简单的 SUM 计算这些隐藏值。
我想知道是否有一种方法可以让这些值显示为其名称同时保留其价值？
答案1
单单是这个答案的长度就足以让人觉得可怕，但我只是啰嗦了...我相信一点（好吧，很多）细节会很有帮助，告诉你为什么要做某件事会让你更容易理解，也更适用于其他应用程序。这很容易。我在工作时老板跟我谈话时只用了五分钟就搞定了。这很容易。所以忍一下吧？
如果满足以下条件，你就可以毫不费力地完成此操作，甚至不会遇到任何麻烦：
1) 您有权使用 CONCAT() 函数。2
) 您接受必须以某种方式输入食物名称/卡路里信息，要么像大多数人那样在查找表中输入，要么作为命名范围及其值输入。这两种输入方式都不比另一种更令人讨厌，所以为什么不呢？3) 您输入食物的单元格范围不大。我的意思是不超过 800 个项目。即使这个范围实际上可以扩展，但它有点像“分层事物”，这在 Excel 中有时很令人讨厌。根据我对您描述的感觉，我设想了一种有限的“当天”输入需求，或者一周的输入需求，但是……如果这意味着永远运行，或者对一群人来说运行一周或其他什么的，那就很困难了。不过这是可行的，不要误会我的意思，但我将继续假设您预期的范围内可能有 25 或 200 个单元格。
因此...每种食物都需要以命名范围的形式输入，名称中不得有空格或非自然字符。例如：
猪排青豆通心粉和奶酪
但不是“Pork_Chop”等等。只需删除空格即可。
对于“引用”区域，不要使用通常的公式或单元格引用（例如“=Sheet1！A5”），而只需输入卡路里值：例如对于猪排，卡路里值为 =180。
现在，要构建公式，您需要：
您需要使用 INDIRECT() 将食物名称转换为 Excel 可用的参考。例如：
=INDIRECT(PorkChop)
会将“PorkChop”变成 PorkChop（命名范围），在您的 NR 中查找它，并返回您输入的卡路里值。
不幸的是，尽管我发誓它对我来说已经正确地完成了，但现在还不行，使用 INDIRECT() 就像这样：
=INDIRECT(K10:K13)
使用该范围的“左上角” K10 得出单个值。（我将在此处的示例中使用 K10:K13 作为您的输入区域。）也许我通过目标单元格中的直接单元格引用实现了这一点（因为 K10:K13 中有值，而不是 NR），这就是区别所在。无论如何，CONCAT() 使这一点变得不那么重要。
因此，您必须分别输入每个 INDIRECT，而不是像上面那样输入 sweet 和 easy 范围。对于 500 个单元格输入范围，这将需要大量输入！但 CONCAT() 可以解决这个问题。
好吧。如何处理用户在输入的食物名称中输入空格的情况？问题 #2，是吗？在将每个 NR 传递给 INDIRECT() 之前，先将其包装在 SUBSTITUTE() 中：
=INDIRECT(SUBSTITUTE(K10," ",""))
如果没有 CONCAT()，那工作量将非常大。
因此，为了构建您的求和公式，我们将为输入范围中的每个单元格制作一个文本字符串，并插入 ROW() 值以使其唯一。让我们在这里计算范围 K10:K13，尽管您可以使用以下技术拥有更多行并使用多列。
首先，这是基本字符串。选择第 10 行中的一个单元格（与第一个单元格的行匹配，K10 让您更容易操作）。我的示例设置了 G10，但您确实想要 K 列右侧的某个单元格。只是说说而已。在单元格 G10 中输入以下内容：
="INDIRECT(SUBSTITUTE(K"&ROW()&","&""" "","""")),"
（请注意，您必须在其中使用两个“”字符来表示“真正的” SUBSTITUTE() 函数中所需的每个字符...这是一个文本字符串，它将被传递给一个真正的函数，我的意思是公式，此时 Excel 会从每对字符中剥离一个...这就是我所说的“分层”的一个方面，以及它如何变得...有时很有趣。）
输入后，复制单元格并将其粘贴到 G11、G12 和 G13 中。或者，如果您的输入区域有 200 个单元格深，则粘贴到 200 个单元格中。但目前这四个是必需的。
然后转到最后一个，G13，并编辑它以删除最后一个逗号，使其结束：
))" instead of )),"
因为那将是 CONCAT() 函数中字符串的结尾，并且不能以逗号结尾！
好极了。现在转到其他单元格，可能是 G15，然后输入：
=CONCAT(G10:G13)
按下 Enter 后，将单元格复制到剪贴板，然后转到另一个单元格（可能是 G16），然后粘贴|特殊|值，最终创建 SUM() 函数所需的文本字符串。
然后按 F2 编辑单元格内容并将所有内容复制到剪贴板。从单元格内部执行此操作将获得文字文本，而不会获得其他任何内容。复制单元格本身将毫无用处！
转到您想要卡路里总数的单元格并输入“=SUM(”，然后粘贴您的文本字符串，然后按 Enter 键，让 Excel 为您添加结尾的“)”。它看起来会像这样：
=SUM(INDIRECT(SUBSTITUTE(K10," ","")),INDIRECT(SUBSTITUTE(K11," ","")),INDIRECT(SUBSTITUTE(K12," ","")),INDIRECT(SUBSTITUTE(K13," ","")))
完毕。
用户在食物名称中输入的所有空格都会被删除，以便与 NR 匹配。无论如何，他们都会在单元格中看到他们的食物名称条目。然而，您的求和单元格会获得进行实际求和所需的数字。
现在，用户仍然会破坏它，但这些破坏（哦，拼写不正确、打字错误、新的食物名称、错误的名字，你懂的）也会破坏查找表方法。它们都会使求和单元显示错误，这样某人（你……）就会知道要查找错误。
顺便说一句，如果您使用的是多列（不是我使用的那列），只需为第一列设置字符串，然后编辑第一个单元格以设置下一列，依此类推。每次都将额外的文本字符串材料粘贴到求和公式中上一次粘贴的末尾。
如果达到了 Excel 公式字符长度限制（还记得我说过输入范围限制为 800 个单元格吗？），您可以创建一个名为“Sum1”的 NR，并将第一批放入其中，然后将另一个（“Sum2”）放入第二批，依此类推，直到您考虑到您的全部输入范围，并且对于您要使用的求和单元格：
=Sum1 + Sum2 + Sum3
或者其他。不过“分层”的事情... Excel 命名区域中的公式字符长度可能小于实际单元格中的公式字符长度，因此可能会变得复杂。但这是原则，您只需进行实验即可。
但是，通过简单、快速的工作，您只需要在数据输入范围内输入食物名称，而看不到所有卡路里值。这对他们来说将是魔术。