比较和计数值 [英] Comparing and counting values
问题描述
我有一个表格，我们称之为个人表格，与下面的表格非常类似，包含个人列以及相邻单元格中列出的相应代码。每个人的代码都列在个人姓名旁边的同一个相邻单元格中，并用回车符分隔。
我想做的是：
-
运行每个人的代码单元
-
对于个人代码单元格中的每个代码，请检查此代码是否存在于单独的代码表 li>
-
如果代码存在于代码表中，请将n + 1添加到相邻单元格中的该代码的总计数中，并将个人姓名添加到
-
如果代码不存在于代码表中，请将代码添加到代码表中，将n + 1添加到相邻单元格中该代码的总计数，并将个人姓名添加到另一个相邻单元格中具有相同代码的个人列表中。
我试着给你一个可能的解决方案，通过最小化VBA代码的使用。
作为开始，我会做的是重新排列代码为每个人。在单个单元格中保留更多代码（由返回分隔），因此不像每个单元格具有单个代码那样容易管理。当然我会保持每个代码与每个人相关联。一种方法是使用公式替换并用分号替换返回的字符。公式工作原理：
= SUBSTITUTE（B2，CHAR（13），CHAR（59））
B2是要将返回转换为分号的单元格。
一旦用分号替换返回，复制并粘贴值，然后使用文本到列功能在数据选项卡中，您将将每个单元格转换为一系列列（取决于您在工作表的原始单元格中列出的代码数量）。现在你将处于第一列你有个人的名字，然后在后面的列的同一行你有所有相关的代码，如下图所示：
为了创建所有代码的完整列表，您可以轻松地将所有列。将代码粘贴在合适的空间（我建议在一个新的工作表），然后与一些复制和粘贴作业将所有代码放在同一列。选择所有代码，并在数据标签中始终使用删除重复项按钮，您将拥有原始表格中包含的所有唯一代码的列表。
并使用您在代码列下创建的所有唯一代码粘贴列。现在，您可以使用以下公式计算转换表中的代码：
= COUNTIF（$ B $ 1：$ C $ 4，D2 ）
其中COUNTIF的第一个参数是指转换表中的代码，第二个参数是代码
现在据我所知，Excel中没有函数来创建一个名称列表，用逗号分隔但我会很高兴地发现，如果有人知道!!!存在）。因此，我创建了一个自定义一个VBA代码名为List Individuals：
函数ListIndividuals（refCode，NameRange As Range，CodesRange As Range）As String
'检查NameRange和CodesRange的行号中的大小是否相同，否则给出错误
如果NameRange.Rows.Count<> CodesRange.Rows.Count Then
ListIndividuals = CVErr（xlErrRef）
退出函数
结束如果
result =
= 1到CodesRange.Columns.Count
对于n = 1到CodesRange.Rows.Count
如果CodesRange.Cells（n，Col）.Value = refCode then
如果CodesRange.Cells（n ，Col）。 then
如果result =then
result = NameRange（n）
Else
result = result& ，& NameRange（n）
结束如果
结束如果
结束如果
接下来
接下来
ListIndividuals =结果
$ b b结束函数
因此，最后一步是在Individuals单元格下使用此公式，如下所示： / p>
= ListIndividuals（D2，$ A $ 13：$ A $ 16，$ D $ 13：$ E $ 16）
其中第一个参数是代码，第二个参数是转换表中的个体列表（它应该是第一列） ，则第三个是具有转换表中的代码的列。作为这个自定义公式的结果，您将有以逗号分隔的个人列表。
以上都适用于我的计算机，但如果您需要更多信息，请不要犹豫与我联系。
I have a table, let's call it my Individuals Table, much like the one below, containing a column of individuals along with their corresponding codes listed in an adjacent cell. Codes for each individual are all listed within the same adjacent cell next to the individual's name, and separated by a carriage return.
What I'd like to do is the following:
Run through the code cell for each individual
For each code in the individual's code cell, check if this code exists in a separate Codes Table
If the code exists in the Codes Table, add n+1 to the total count for that code in an adjacent cell and add the individual's name to the list of individuals with that same code in another adjacent cell.
If the code does not exist in the Codes Table, add the code to the Codes Table, add n+1 to the total count for that code in an adjacent cell and add the individual's name to the list of individuals with that same code in another adjacent cell.
Result of running the algorithm on the example table
If a similar program can achieve the same results, then I'm open to that too.
I try to give you a possible solution, by minimizing the use of VBA code.
As starting point I would do is rearranging codes for every individuals. Keeping more codes in a single cell separed by a return it is not as easy to manage like having a single code for each cell. Of course I will keep each code associated with each individual. A way to do it is with your data is by using the formula substitute and replace the returns characters with a semicolon. The formula works this:
=SUBSTITUTE(B2,CHAR(13),CHAR(59))
B2 is the cell where you are converting returns to semicolon. You will then use this formula for all values in your B column.
Once you have replaced returns with semicolon, copy and paste values and then with the function "Text to Columns" in Data tab you will convert each cell to a series of columns (depending on how many codes you had listed in the original cell of your sheet). Now you will be in a situation where the first column you have the names of individuals, and then on the same row in the subsequent columns you have all associated codes, like in the picture here below:
In order to create a complete list of all codes you can easily copy all columns with codes. Paste the codes in a suitable space (I suggest in a new worksheet), and then with some copy and paste jobs put all codes under the same column. Select all codes and with the button "Remove Duplicates" always in the Data tab you will have a list of all unique codes included in your original table.
Then you can copy and paste the column with all unique codes you created under your "Codes" column. Now you can count the codes in the converted table with this formula:
=COUNTIF($B$1:$C$4, D2)
Where first argument of COUNTIF refers to the codes in the converted table and the second argument is a code in your column "Codes" where you pasted the unique codes.
Now as far as I know there is no function in Excel to create a list of names separated by commas (but I would be glad to discover that it exists if anybody knows!!!). Therefore I created a custom one with some VBA code with the name List Individuals:
Function ListIndividuals(refCode, NameRange As Range, CodesRange As Range) As String
'Check size in row number of NameRange and CodesRange is same, otherwise give error
If NameRange.Rows.Count <> CodesRange.Rows.Count Then
ListIndividuals = CVErr(xlErrRef)
Exit Function
End If
result = ""
For Col = 1 To CodesRange.Columns.Count
For n = 1 To CodesRange.Rows.Count
If CodesRange.Cells(n, Col).Value = refCode Then
If CodesRange.Cells(n, Col).Value <> "" Then
If result = "" Then
result = NameRange(n)
Else
result = result & ", " & NameRange(n)
End If
End If
End If
Next
Next
ListIndividuals = result
End Function
So last step is to use this formula under your "Individuals" cells as follows:
=ListIndividuals(D2,$A$13:$A$16,$D$13:$E$16)
Where first argument is the Code, the second one is the list of individuals in the converted table (it should be the first column), then the third one are the columns with the codes in the converted table. As a result of this custom formula you will have the list of individuals separated by commas.
All above works on my computer, but if you need more information, please do not hesitate to contact me.
这篇关于比较和计数值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
