Google表格-将多个IF函数合并到一个单元格中 [英] Google Sheets - Combine multiple IF Functions into one cell
问题描述
我正在尝试使用三个变体(标题，颜色和大小)
I'm trying to produce a SKU in Google Sheets for a product using the values of three variants (Title, Colour and Size)
该产品为轻便运动鞋"，颜色有红色"和蓝色"两种，尺寸范围为5-12.
The product is 'Lightweight trainers' with colour variants of 'Red' and 'Blue', and the sizes range from 5 - 12.
链接到电子表格
目标
我希望有一个SKU，可以显示产品，颜色变化和鞋子的尺码.例如: LW-1-8 (轻型运动鞋，红色，尺寸8)
I'm hoping to have a SKU that displays the product, the colour variant and the shoes size.
Example: LW-1-8 (Lightweight trainer, colour Red, size 8)
产品是轻量化运动鞋，价值为LW.
Product is Lightweight Trainers with a value of LW.
颜色变体红色"的值为1，蓝色"的值为2.
Colour variant 'Red' with a value of 1 and 'Blue' with a value of 2.
鞋子的尺码= 5到12之间的数字.
Shoe size variant = number ranging from 5 to 12.
这是我到目前为止所拥有的，加入了颜色和尺寸的变体.
Here's what I have so far, joining the colour and size variants.
=IFS(I2="Red",1,I2="Blue",2)&"-"& IFS(K2="5",5,K2="6",6,K2="7",7,K2="8",8,K2="9",9,K2="10",10,K2="11",11,K2="12",12)
但是，我无法通过此功能将B列中的数据连接起来.
However, I'm getting stuck in joining the data in column B with this function.
对于将多个单元格中的数据合并为一个的任何帮助，将不胜感激.
Any help with combining this data from multiple cells into one would be greatly appreciated.
推荐答案
TL; DR
=ARRAYFORMULA(IF(B2:B<>"", IFS(B2:B="Lightweight Trainers", "LW")&"-"&IFS(I2:I="Blue", 1, I2:I="Red", 2)&"-"&K2:K,))
答案
您想要的基本上是:
Answer
What you want is basically:
<title>-<color number>-<shoe size>
要将其转换为函数，我们可以将其拆分为每个部分，并逐步进行:
To convert this to a function we can split it into each part and take it step by step:
步骤1:标题对于第一部分(标题)，我们需要将值与速记进行匹配.在 IFS 中添加一个简单列表即可.
Step 1: Title
For the first part -the title- we need to match the value with the shorthand. A simple list in an IFS is enough.
IFS(B2="Lightweight Trainers", "LW")
很明显，目前它只有一个值(轻量级教练)，但您可以添加更多值:
Obviously for now it only has a single value (Lightweight Trainers) but you could add more:
IFS(B2="Lightweight Trainers", "LW", B2="Heavyweight Trainers", "HW")
第2步:色号与上一步类似，它是使用ifs的映射:
Step 2: color number Similar to the previous step, it’s a mapping using ifs:
IFS(I2="Blue", "-1", I2="Red", "-2")
添加了破折号，因此在添加所有内容时，只有在出现破折号时才会提供
The dash is added so when adding everything it will only have it if
第3步:鞋子的尺寸在这种情况下，我们可以简单地获取值:
Step 3: shoe size In this case we can simply get the value:
K2
第4步:将所有内容添加在一起我们只需要添加它们之间的破折号即可:
Step 4: Adding everything together We only need to add it with the dashes in between:
=IFS(B2="Lightweight Trainers", "LW")&"-"&IFS(I2="Blue", 1, I2="Red", 2)&"-"&K2
第5步:自动扩展整列
我们将使用 ARRAYFORMULA 向第一个单元格添加一个公式，并将其自动扩展到整个列.我们首先将其添加到我们已有的公式中，然后将范围扩展到整个列:
We will use ARRAYFORMULA to add a single formula to the first cell and get it automatically extended to the entire column. We first add it to the formula we already have, and then extend the ranges to the entire column:
=ARRAYFORMULA(IFS(B2:B="Lightweight Trainers", "LW")&"-"&IFS(I2:I="Blue", 1, I2:I="Red", 2)&"-"&K2:K)
请记住要删除列中的所有值，以使数组公式不会覆盖它们(这会产生错误).
Remember to remove all the values in the column so array formula doesn’t override them (it would generate an error).
如您所见，公式为没有值的行生成错误.处理这种情况的一种好方法是过滤不带标题的行.在单行中将是:
As you can see the formula generates errors for the rows that have no values. A good way of handling this case is to filter the rows without a title. In a single row would be:
=IF(B2<>"", [the entire formula],)
注意最后一个逗号.
因此，将所有内容放在一起并将其范围扩展到该列，是:
So putting everything together and extending its range to the column, is:
=ARRAYFORMULA(IF(B2:B<>"", IFS(B2:B="Lightweight Trainers", "LW")&"-"&IFS(I2:I="Blue", 1, I2:I="Red", 2)&"-"&K2:K,))
将其添加到 N2 应该可以.
最后的笔记
当大小不是整数时，您似乎使用了150.如果您想保留该功能，可以使用:
It seems that you use 150 when the size it’s not a whole number. If you want to keep that functionality you may use:
IF(K2-int(K2)=0, K2, 150)
在最后一个组件上，以相同的方式进行扩展.
On the last component and expand it the same way.
当缺少值时，您可能还希望防止两个破折号( LW-5 而不是 LW--5 ).为此，建议您将其添加到每个组件中，而不要使用将它们添加在一起的公式.
You may also want to prevent having two dashes when a value is missing (LW-5 instead of LW--5). To do so, I’d recommend adding it to each component instead of the formula that adds them together.
这篇关于Google表格-将多个IF函数合并到一个单元格中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
