Мне нужно посчитать количество совпадений между ячейками в двух столбцах. Данные в ячейке являются именами. К сожалению, некоторые имена содержат звездочку в конце, поэтому они не являются точным соответствием. Есть ли способ подсчитать количество строк, которые содержат совпадения между двумя ячейками только первые 3 буквы или около того?
3 ответа
0
в качестве альтернативы вы можете использовать sumproduct для подсчета. Поскольку sumproduct выполняет вычисления, подобные массиву, вы должны избегать использования полных ссылок на столбцы и ограничивать их фактическим диапазоном данных. В ячейке D2 я использовал следующую формулу и скопировал:
=SUMPRODUCT(--(LEFT($A$2:$A$8,LEN(C2))=C2))
Эта формула в основном определяет длину, которую вы хотите сопоставить, и вытягивает только то количество символов слева от данных, которые вы хотите просмотреть. Затем он сравнивает этот список с тем, что вы ищете, и генерирует список ИСТИНА / ЛОЖЬ. Чтобы преобразовать ИСТИНА / ЛОЖЬ в 1 или 0, необходимо выполнить математическую операцию, которая не меняет своего значения. В этом случае - используется, но +0, * 1 можно использовать так же легко.
0
Аналогично предыдущему ответу с использованием массива SUMPRODUCT, но при подсчете сравниваются первые три символа между ячейками в строке, то есть от А1 до В1, от А2 до В2 и т.д.
=SUMPRODUCT(--(LEFT(A1:A5,3)=LEFT(B1:B5,3)))
другое возможное решение проблемы звездочки может быть принято во внимание при сопоставлении всего слова, но при замене звездочки. (звездочка - это символ 42).
=SUMPRODUCT(--(SUBSTITUTE(A1:A5,CHAR(42),"")=SUBSTITUTE(B1:B5,CHAR(42),"")))
Этот метод считает пустые ячейки действительными совпадениями, если только вы не добавите дополнительный массив в SUMPRODUCT для подсчета только в том случае, если ячейка не пустая (меньше или больше пустого).
=SUMPRODUCT(--(SUBSTITUTE(A1:A5,CHAR(42),"")=SUBSTITUTE(B1:B5,CHAR(42),"")),--(A1:A5<>""))
это с небольшим твиком позволит затем вычислить весь столбец
=SUMPRODUCT(--(SUBSTITUTE(A:A,CHAR(42),"")=SUBSTITUTE(B:B,CHAR(42),"")),--(A:A<>""))
0
Подстановочные знаки являются хитрыми в некоторых формулах Excel (и даже не ищут использование регулярных выражений!). Однако для простого подсчета использование функции COUNTIF будет работать просто отлично. Так что, если ваши данные выглядят так:
+----+-----+
| | A |
+----+-----+
| 1 | AAA |
| 2 | AAB |
| 3 | AAC |
| 4 | ABA |
| 5 | ABB |
| 6 | ABC |
| 7 | ACA |
| 8 | ACB |
| 9 | ACC |
| 10 | ADA |
+----+-----+
Затем вы можете посчитать ячейки в диапазоне, начинающемся с "AA", используя формулу
=COUNTIF(A1:A10,"AA*")
Подстановочный знак * - это то, что соответствует нескольким ячейкам и подсчитывает их.