我有一系列的列。如果任何匹配的代码出现在该范围内,我想将指标设置为1。例如:
CDEFGHM 1 2 3 4 5 6如果范围C1中的任何值:H1位于1-4之间然后1 else 0说C1:H1,我想创建列M1(一个指标)任何代码说(1,2,3,4)出现在范围C1:H1中,其值被设置为1 else 0
解决方案假设单元格A1:A4包含值1,2,3,4,一个数组公式,用于检查这些值是否出现在C1:H1中是
= OR(EXACT(A $ 1:A $ 4,C1:H1))* 1由于这是一个数组公式,您需要按Ctrl-Shift-Enter。请参阅 office.microsoft。 com / en-gb / excel-help / compare-cell-contents-HP003056130.aspx#BMcompare_one_value_to_a_list_of_values 为一个类似的公式。
I have this situation:
I have a range of columns. And I want to set indicator to '1' if any of the matching codes appear in that range. example:
C D E F G H M 1 2 3 4 5 6 if any value in range C1:H1 lie between 1-4 then 1 else 0Say Column C1:H1, I want to create column M1(an indicator) such that any of codes say (1,2,3,4) Appear in range C1:H1 its value is set to 1 else 0
解决方案Assuming cells A1:A4 contain the values 1,2,3,4, an array formula that checks whether any of these values appear in the range C1:H1 is
=OR(EXACT(A$1:A$4, C1:H1))*1Since this is an array formula, you need to press Ctrl-Shift-Enter. See office.microsoft/en-gb/excel-help/compare-cell-contents-HP003056130.aspx#BMcompare_one_value_to_a_list_of_values for a formula that does something similar.
更多推荐
在excel中怎么做?
发布评论