是否有任何Excel公式用于比较两列以包含数据并计算它们的出现? [英] Is there any Excel Formula for comparing 2 columns for containing data and counting their occurrence?

问题描述

大家好.
在 Excel/G-Sheets 中的列中比较数据时，我遇到了一些问题.
我尝试使用标准公式" = countif(A:A，B2)"比较带有公司名称的2列，其中"A" 列>包含我要查找的公司的名称(可接受)，列"B" 包含应与列中的公司名称匹配的公司名称"一个" .
例如，在"A" 列中有一个单元格，名称为公司"Apple" ，如果在列中"B" 没有"Apple" 的单元格，它应该返回"0" ，但是相反，它应该返回"1"，"2"等.但是问题是，如果带有" Apple Inc."的单元格出现在"B" 列中，它将返回"0" ，尽管这是同一家公司.因此，我试图找到一个公式，在该公式中我可以计算出不完全匹配的单元格，甚至可以包含部分值.
例如，如果在A列中有一个值为"Apple" 的单元格，如果在B列中有"Apple Inc." ，"GTX Apple"，苹果" 值公式必须返回"3" .
我在countif公式中发现了一些使用"*"符号的方法，但是它并不能正常工作.有人可以帮我解决这个问题吗?

Hello everybody.
I ran into some problems when comparing data in columns in Excel / G-Sheets.
I tried to compare 2 columns with company names using the standard formula "=countif (A:A, B2)", where column "A" containing the names of the companies I am looking for (acceptable) and column "B" containing the names of companies that should match with company names in column "A".
For example, in column "A" there is a cell with the name of the company "Apple", and if in column "B" there is no cell with "Apple", it should return "0", but if on the contrary, it should return "1", "2", etc. But the problem is if a cell with "Apple Inc." appears in column "B" it will return "0", although this is the same company. Therefore, I am trying to find a formula where I can count cells not with an exact match, but if it even contains part of the value.
For example, if in column A we have a cell with value "Apple", if in column B we have "Apple Inc.", "GTX Apple", "The Apple" values formula must return "3".
I found some using "*" sign in countif formula, but it does not work as I want. Can someone help me with this issue?

推荐答案

考虑:

=COUNTIF(B:B,"*" & A2 & "*")

这篇关于是否有任何Excel公式用于比较两列以包含数据并计算它们的出现?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！