Я хотел бы проверить, что ячейка содержит одинаковое количество каждой буквы от az и числа от 0 до 9, как ячейка рядом с ней. Порядок не имеет значения, но имеет значение количество символов. Например, если ячейка содержит «квартира 1, 32 пробная дорога», а ячейка рядом с ней содержит «32, квартира 1, пробная дорога», они будут совпадать, так как они обе содержат 3 t, 2 a, 1 l, так далее.).
Пример ожидаемого результата приведен ниже, а формула заполняется в столбце C:
В настоящее время у меня это работает, сначала преобразуя содержимое ячейки в нижний регистр, а затем индивидуально проверяя каждую букву от az и каждое число от 0 до 9 в выражении IF(AND)
. Он работает, но выглядит ужасно и, несомненно, невероятно неэффективен и требует больших ресурсов. Код выглядит следующим образом:
=IF(AND(LEN(A1)-LEN(SUBSTITUTE(A1,"a",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"a","")),LEN(A1)-LEN(SUBSTITUTE(A1,"b",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"b","")),LEN(A1)-LEN(SUBSTITUTE(A1,"c",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"c","")),LEN(A1)-LEN(SUBSTITUTE(A1,"d",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"d","")),LEN(A1)-LEN(SUBSTITUTE(A1,"e",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"e","")),LEN(A1)-LEN(SUBSTITUTE(A1,"f",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"f","")),LEN(A1)-LEN(SUBSTITUTE(A1,"g",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"g","")),LEN(A1)-LEN(SUBSTITUTE(A1,"h",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"h","")),LEN(A1)-LEN(SUBSTITUTE(A1,"i",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"i","")),LEN(A1)-LEN(SUBSTITUTE(A1,"j",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"j","")),LEN(A1)-LEN(SUBSTITUTE(A1,"k",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"k","")),LEN(A1)-LEN(SUBSTITUTE(A1,"l",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"l","")),LEN(A1)-LEN(SUBSTITUTE(A1,"m",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"m","")),LEN(A1)-LEN(SUBSTITUTE(A1,"n",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"n","")),LEN(A1)-LEN(SUBSTITUTE(A1,"o",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"o","")),LEN(A1)-LEN(SUBSTITUTE(A1,"p",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"p","")),LEN(A1)-LEN(SUBSTITUTE(A1,"q",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"q","")),LEN(A1)-LEN(SUBSTITUTE(A1,"r",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"r","")),LEN(A1)-LEN(SUBSTITUTE(A1,"s",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"s","")),LEN(A1)-LEN(SUBSTITUTE(A1,"t",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"t","")),LEN(A1)-LEN(SUBSTITUTE(A1,"u",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"u","")),LEN(A1)-LEN(SUBSTITUTE(A1,"v",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"v","")),LEN(A1)-LEN(SUBSTITUTE(A1,"w",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"w","")),LEN(A1)-LEN(SUBSTITUTE(A1,"x",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"x","")),LEN(A1)-LEN(SUBSTITUTE(A1,"y",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"y","")),LEN(A1)-LEN(SUBSTITUTE(A1,"z",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"z","")),LEN(A1)-LEN(SUBSTITUTE(A1,"0",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"0","")),LEN(A1)-LEN(SUBSTITUTE(A1,"1",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"1","")),LEN(A1)-LEN(SUBSTITUTE(A1,"2",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"2","")),LEN(A1)-LEN(SUBSTITUTE(A1,"3",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"3","")),LEN(A1)-LEN(SUBSTITUTE(A1,"4",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"4","")),LEN(A1)-LEN(SUBSTITUTE(A1,"5",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"5","")),LEN(A1)-LEN(SUBSTITUTE(A1,"6",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"6","")),LEN(A1)-LEN(SUBSTITUTE(A1,"7",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"7","")),LEN(A1)-LEN(SUBSTITUTE(A1,"8",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"8","")),LEN(A1)-LEN(SUBSTITUTE(A1,"9",""))=LEN(B1)-LEN(SUBSTITUTE(B1,"9",""))),TRUE,FALSE)
Как видите, это бельмо на глазу. Это работает, но мне нужно будет применить это как минимум к 100 000 строк данных, и я считаю, что это будет слишком интенсивно для надежной работы. Текущее решение состоит в том, чтобы просмотреть каждую букву и убедиться, что их количество совпадает, и вернуть TRUE, если они все совпадают.
У меня есть рабочее решение, которое дает пример того, что требуется, но оно неуклюже и ненадежно. Я надеюсь, что есть лучший способ использовать Excel для более эффективного выполнения этой задачи. Спасибо заранее за ваше время!
Я использую MSO 365, Excel версии 2202.
Спасибо за ответ. Желательно, чтобы это не было чувствительным к регистру, пожалуйста. Я просто сделал все строчными, так как формула замены чувствительна к регистру и не соответствовала бы, если бы одна была написана с заглавной буквы, а другая - нет.
Очень хороший вопрос для работы. Вот один из вариантов, который приведет к разливу результатов:
Формула в C2
:
=BYROW(A2:B6,LAMBDA(x,COUNTA(UNIQUE(MAP(x,LAMBDA(a,CONCAT(LET(b,MID(a,SEQUENCE(LEN(a)),1),c,CODE(UPPER(b)),SORT(IFERROR(--b,IF((c>64)*(c<91),b,""))))))),1))))=1
Суммируя:
BYROW()
- Перебрать каждую строку в наборе данных;MAP()
- Каждая итерация вышеуказанного цикла будет передавать оба значения через эту функцию, чтобы разделить каждый элемент на символы, проверить, являются ли они числами, если нет, то свериться с таблицей ASCII CODE()
, если ни одно из них не возвращает пустую строку. SORT()
эти символы и CONCAT()
результат снова вместе;COUNTA(UNIQUE())
— Комбинация для проверки того, вернула ли вышеуказанная итерация два одинаковых значения (без учета регистра).Я ценю ответ, хотя, к сожалению (если я не делаю что-то не так), =BYROW
недоступен в MSO 365 версии 2202. У меня нет возможности использовать более новую версию. Есть ли альтернативное решение, которое может работать с версией 2202? imgur.com/a/FjXKoLo Также - еще раз спасибо JvdV. Ты всегда очень быстро отвечаешь на мои странные и замечательные вопросы!
@Wisp, тебе придется разобрать мое решение и протестировать =CONCAT(LET(a,MID(A2,SEQUENCE(LEN(A2)),1),b,CODE(UPPER(a)),SORT(IFERROR(--a,IF((b>64)*(b<91),a,"")))))=CONCAT(LET(a,MID(B2,SEQUENCE(LEN(B2)),1),b,CODE(UPPER(a)),SORT(IFERROR(--a,IF((b>64)*(b<91),a,"")))))
. К сожалению, формулу, которую вам придется перетаскивать вниз.
Удивительно. Большое спасибо за Вашу помощь. Я обязательно перейду на это, как только моя версия Excel будет поддерживать =BYROW
!
Хороший ответ от @JvdV.
Я решил написать лямбду CharFreq, чтобы разбить строки на символы, и использовать Frequency для генерации частот символов и чисел. Моя лямбда выглядела так в Advanced Formula Environment:
=LAMBDA(string,
LET(
codes, SEARCH(
MID(
string,
SEQUENCE(
LEN(
string
)
),
1
),
"0123456789abcdefghijklmnopqrstuvxyz"
),
fcodes, FILTER(
codes,
ISNUMBER(codes)
),
freq, FREQUENCY(
fcodes,
SEQUENCE(36)
),
freq
)
)
Затем я мог бы просто сравнить частоты, чтобы выявить любые расхождения:
=IF(SUM(--(CharFreq(A2)<>CharFreq(B2)))=0,"Match","No match")
Примечание
Это может привести к неправильному результату, если подстановочный знак, такой как «*» или «?» произошло в одной из строк - можно было исправить либо с помощью поиска с более низким вместо поиска, либо путем замены этих символов.
Альтернативное использование LEN
:
=AND(MMULT(LEN(A2:B2)-LEN(SUBSTITUTE(UPPER(A2:B2),MID("0123456789ABCDEFGHIJKLMNOPQRSTUVXYZ",SEQUENCE(36),1),"")),{-1;1})=0)
и скопировал.
Спасибо за ответ. Это работало отлично и поддерживалось моей версией Excel. Я ценю помощь!
Это должно быть чувствительным к регистру? Например: соответствует ли
ddD
DDd
?