excel如果条件或VBA [英] excel if condition or vba

问题描述

我在A，B，C 3列的Excel工作表中获得了数据

我想要的是B被自动填充.

如果要满足以下条件，我希望填充B，
第1部分
如果A = 2012且C = 1，则B = 1
如果A = 2011且C = 1，则B = 2
如果A = 2010且C = 1，则B = 3
第2部分
如果A = 2011且C = 2，则B = 1
如果A = 2010且C = 2，则B = 2
如果A = 2019且C = 2，则B = 3
第3部分
如果A = 2010且C = 3，则B = 1
如果A = 2009且C = 3，则B = 2
如果A = 2008且C = 3，则B = 3
第4部分
如果A = 2009且C = 4，则B = 1
如果A = 2008且C = 4，则B = 2
如果A = 2007且C = 4，则B = 3

第5部分
如果A = 2008且C = 5，则B = 1
如果A = 2009且C = 5，则B = 2
如果A = 2006且C = 5，则B = 3

请帮助我，是否可以将所有部分组合在一个公式中

i have got data in an excel sheet in 3 columns, A,B,C

What i want is B to be populated automaticaly.

I want B to be populated if the following conditions are met,
PART 1
if A = 2012 and C = 1 then B = 1
if A=2011 and C =1 then B =2
if A =2010 and C= 1 then B=3
PART 2
if A = 2011 and C = 2 then B = 1
if A=2010 and C =2 then B =2
if A =2019 and C= 2 then B=3
PART 3
if A = 2010 and C = 3 then B = 1
if A=2009 and C =3 then B =2
if A =2008 and C= 3 then B=3
PART 4
if A = 2009 and C = 4 then B = 1
if A=2008 and C =4 then B =2
if A =2007 and C= 4 then B=3

PART 5
if A = 2008 and C = 5 then B = 1
if A=2009 and C =5 then B =2
if A =2006 and C= 5 then B=3

Please help me, can all the parts be combined in one formula

推荐答案

假设当A列包含您的A值，C包含您的C值和B包含时的电子表格公式(从第二行开始)，则第二行的公式可以写为:

Assuming a spread sheet when column A holds your A values, C holds your C values and B holds the formula (starting at row two), then the formula for row 2 can be written as;

=IF(C2<5,2012-A2+1-(C2-1),IF(A2=2008,1,IF(A2=2009,2,3)))

然后复制到其他行也应填充这些行.

希望这会有所帮助，
Fredrik

Then copied to the other rows should fill those as well.

Hope this helps,
Fredrik

Fredrik Bornander 给出的解决方案1很好.
我要补充一点，从A，C列的值模式观察，连续的三行中每个部分下的A+C总数为2013, 2012, 2011，除了第2部分中的第3行和第2部分中的第2行之外5.我认为通过遵循该模式，这些行中可能会出现键入错误. 如果是这种情况，则正确的值可以是第A部分第2部分第3行中的2009年和第5部分第2行中的2007年中的，还可以使用以下表达式 .

The Solution 1 given by Fredrik Bornander is very good.
I want to add that, as observed from the pattern of values in columns A, C, the total of A+C is 2013, 2012, 2011 under each part in the consecutive three rows, except for row 3 in Part 2 and row 2 in Part 5. I think by following the pattern there may be a typing error in these rows. If that is the case then the correct values could be 2009 in row3 of Part 2 and 2007 in row 2 of Part 5 in Column A and the following expression can also be used.

=IF( AND(A2 <= 2012, A2 >= 2006, C2 >= 1, C2 <= 5),1-(A2+C2-2013),0)

这篇关于excel如果条件或VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！