查找列中具有多个值的行 [英] Finding rows with multiple values in the columns

问题描述

我试图找到一种搜索行并找出哪些行中具有多个值的方法.例如，如果我有这个:

I am attempting to find a way to search rows and find out which ones have multiple values in the rows. For example If I have this:

81  IRENE L MOTZ                        
82  BRITTNEY SUMMERVILLE                        
83  NICK SIRK   33  120             1,859                        
86  DOROTHY TARR                        32    
87  DAVID BRYANT                        
89  WAYNEDALE VAULT                     
90  APRIL HOWARD                        
95  DONA KAHL                       
96  ASHLEY FISHER                       
98  TERRI SULESKI                       
99  SCOTTSVILLE VAULT                       
104 CHRISTINE WIECHART      564             
105 KAYLENE DUNCAN      7               
106 LINDA NETHERTON                     
107 DIANNA MAY              336     
108 DIXIE HETTINGER     596             
110 JIM MELVIN      26              
111 LASHAWNYA BREWER        181             
112 LOIS COLEMAN        283             
113 KRISTIN FRIEND      827             
115 BRANDI QUDUS        2,106

您可以看到以83开头的行在名称之后的列中具有三个值.而在此示例中，其他仅具有一个值.我需要能够找到具有多个值的行，然后对列中的值执行一些操作(主要是将它们加在一起，然后从先前计算出的数字中减去总数).我目前没有任何代码，如果有人可以向我指出正确的方向，那将是很好的.我虽然可能尝试使用 IF ELSEIF语句来做到这一点，但对我而言似乎太草率了.

You can see that the row starting with 83 has three values in the columns that follow the name. While the others in this example only have one value. I need to be able to find the rows that have multiple values, then I'm going to perform some actions on the values in the columns (mostly just adding them together and then subtracting the total from a previously calculated number). I don't have any code at the moment, if some one could point me in the right direction that would be great. I though maybe trying to do it with IF ELSEIF statements, but that just seemed sloppy to me.

推荐答案

假设您的数据范围从B1开始，则在A1中输入公式

Assuming your data range starts at B1, you enter in A1 the formula

=COUNT(B1:I1)-1

(您提到的数据将到达H列，我将其移至右侧的一列)或

(you mentioned data would go up to column H, I moved it one column to the right) or

=COUNT(C1:I1)

按预期，第一行给出 0 .向下复制和粘贴公式会为每行提供您要查找的数字(所有值< = 1，第3行除外).然后，您可以过滤或执行其他操作.

This gives 0 for the first row, as expected. Copying and pasting the formula downwards gives the number you are looking for, for each row (all values <=1, except for row 3). Then you can filter, or perform other actions.

例如，要获取该行中所有数字的总和(如您所述)，您将使用

For instance, to get the sum of all numbers in the row (as you mentioned), you would use

=SUMPRODUCT(--(ISNUMBER(C1:I1)),C1:I1)

如果只想对具有多个数字的行进行此计算，则可以将此公式嵌套在 IF(B1> 1，...

If you want to calculate this only for rows having more than one number, you can nest this formula inside an IF(B1>1,...

注意:

1. 这避免了使用VBA.

1. This avoids using VBA.

我已经复制了您发布的数据，然后将其粘贴到新工作表的单元格B1中.然后是数据"->文本到列"->定界"->(刻度线).这种情况将数据显示在单独的列中，就像您要处理的情况一样.

I have copied the data you posted, then pasted in cell B1 of a new sheet. Then Data -> Text to Columns -> Delimited -> (Tick Space). This rendered data in separate columns, as it appears you be your case.

应修改引用的范围，具体取决于数据的组织和位置.

The ranges referred to should be modified, depending on the organization and location of your data.

这篇关于查找列中具有多个值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！