根据Excel中单元格中的数据制作句子 [英] Make sentence from data in cells in Excel

问题描述

我有一个带有三个单元格的表格，例如A1-苹果，A2-香蕉和A3-橘子，B列是用户在其中输入所需水果数的数据

I have a form with three cells like A1 - Apple, A2 - Banana and A3 - Oranges and the column B is where user inputs there data as how many each fruits they want

如果用户填写表格，例如Apple = 2 Banana = 0和Oranges = 3

If the user fills the form as Apple = 2 Banana = 0 and Oranges =3

我希望代码将其输出为:我想要2个苹果和3个橙子."在我的代码中，如果用户不希望所有这三个结果都令人满意，那么在放置"And"时会遇到问题.

I want the code to output it as: "I want 2 apples and 3 oranges." With my code, I am having issues in placing "And" if user doesn't want all of these three fruits.

我的公式是:=我要& B1&"& A1&"，& B2&"& A2&"和& B3&"& A3"并输出是我要2个苹果，0个香蕉和3个橙子".我想摆脱没有任何顺序或值的单元格

my formula is: ="I want "&B1&" "&A1 &", "&B2&" "&A2&" and "&B3&" "&A3 and output is "I want 2 Apple, 0 Banana and 3 Oranges". I want to get rid of the cell which doesn't have any order or Value

推荐答案

您可以使用if

="I want "&IF(AND(B1 <>"",A1 <> ""),B1&" "&A1&",","") &IF(AND(B2 <>"",A2 <> ""),B2&" "&A2&",","")&" and "&IF(AND(B3 <>"",A3 <> ""),B3&" "&A3&",","")

或者，您可以使用一组公式，在其中每对都编写if，然后将结果串在一起. 例如，如果您在C列的第1行中写了

Alternatively you could use a set of formulae where for each pair you write your if and then you string the results together. For instance if in column C line 1 you wrote

=IF(AND(B1 <>"",A1 <> ""),B1&" "&A1&",","")

然后，您可以使用自动填充手柄(所选单元格右下角的黑色正方形)为所有其他行填充相同的公式(通过单击自动填充手柄并向下拖动)，然后您的C列条目将显示"N of FRUIT"或" 然后只需将所有列C值串联在一起即可:

then you could use the auto-fill handle (the black square on the bottom right of the selected cell) to populate the same formula for all the other rows (by clicking on the auto-fill handle and dragging down), and your column C entries would either show "N of FRUIT," or "" Then just string together all of your column C values:

="I want "&C1&C2&C3

我发现第二种方法不那么累人. Excel进行复制(和变量替换).

I find the second approach less tiresome. Excel does the copying (and variable replacement).

这两种方法都不够聪明，无法处理最后一个条目为空的情况，但它甚至都不是一种适当的脚本语言，因此您必须忍受其限制或使用条件语法.

Neither approach is smart enough to handle the case where the last entry is empty, but it's not even a proper scripting language so you have to live within its limits or really go overboard with the conditional syntax.

这篇关于根据Excel中单元格中的数据制作句子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！