Убедитесь, что ячейка содержит то же количество каждой буквы и цифры, что и соседняя ячейка

Резюме

Я хотел бы проверить, что ячейка содержит одинаковое количество каждой буквы от az и числа от 0 до 9, как ячейка рядом с ней. Порядок не имеет значения, но имеет значение количество символов. Например, если ячейка содержит «квартира 1, 32 пробная дорога», а ячейка рядом с ней содержит «32, квартира 1, пробная дорога», они будут совпадать, так как они обе содержат 3 t, 2 a, 1 l, так далее.).

Пример таблицы

Пример ожидаемого результата приведен ниже, а формула заполняется в столбце C:

Данные 1 Данные 2 Совпадение? 123 тестовая дорога тестроуд123 СОВПАДЕНИЕ ФУБАР 34 FOO,/,34БАР СОВПАДЕНИЕ ХЕЛЛОУОРЛД1 ПРИВЕТСТВУЮЩИЙ МИР2 НЕ СОВПАДАЕТ НАЗВАНИЕ УЛИЦЫ, КВАРТИРА 4, 33 НАЗВАНИЕ УЛИЦЫ 33, КВАРТИРА 4 СОВПАДЕНИЕ 12345 12345 Дорога НЕ СОВПАДАЕТ

Рабочий код

В настоящее время у меня это работает, сначала преобразуя содержимое ячейки в нижний регистр, а затем индивидуально проверяя каждую букву от 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.

Это должно быть чувствительным к регистру? Например: соответствует ли ddDDDd?

JvdV 17.11.2022 13:08

Спасибо за ответ. Желательно, чтобы это не было чувствительным к регистру, пожалуйста. Я просто сделал все строчными, так как формула замены чувствительна к регистру и не соответствовала бы, если бы одна была написана с заглавной буквы, а другая - нет.

Wisp 17.11.2022 13:10
Преобразование HTML-таблицы в профессиональный документ Excel
Преобразование HTML-таблицы в профессиональный документ Excel
Это самый простой способ создания Excel из HTML-таблицы.
Импорт excel в laravel в базу данных
Импорт excel в laravel в базу данных
Здравствуйте, дорогой читатель, в этой статье я расскажу практическим и быстрым способом, как импортировать файл Excel в вашу базу данных с помощью...
2
2
60
3
Перейти к ответу Данный вопрос помечен как решенный

Ответы 3

Очень хороший вопрос для работы. Вот один из вариантов, который приведет к разливу результатов:

Формула в 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 17.11.2022 13:29

@Wisp, тебе придется разобрать мое решение и протестировать =CONCAT(LET(a,MID(A2,SEQUENCE(LEN(A2)),1),b,CODE(UPPER(a)),S‌​ORT(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,"")‌​)))). К сожалению, формулу, которую вам придется перетаскивать вниз.

JvdV 17.11.2022 13:37

Удивительно. Большое спасибо за Вашу помощь. Я обязательно перейду на это, как только моя версия Excel будет поддерживать =BYROW!

Wisp 18.11.2022 10:14

Хороший ответ от @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. Я ценю помощь!

Wisp 18.11.2022 10:15

Другие вопросы по теме