Excel Range Count в ячейке

Question

Я пытаюсь сравнить кол-во и количество записей в таблице Excel.

Например,

QTY RefDes      # of Entries    QTY Match RefDes?
3   R1,R5,R22   3               MATCH
3   C1,C2,C3    3               MATCH
3   U1-U3       1               MISMATCH
4   U1-U3, U5   2               MISMATCH

Используя =LEN(TRIM(B2))-LEN(SUBSTITUTE(TRIM(B2),",",""))+1 для столбца, перечисленного # записей, я могу легко сравнить перечисленные QTY и ReDes, когда значения по отдельности разделяются запятыми, как в R1, R5, R22.

(QTY Match RefDes? просто сравнивать, если столбец QTY соответствует столбцу # of Entries.)

Однако у меня также есть случаи, когда U1-U3 будет обозначать диапазон от U1 до U3 или U1, U2, U3. Фактическое количество равно 3, но приведенная выше формула возвращает значение 1, потому что разделение запятыми отсутствует. Как я могу объяснить этот случай?

Answer 1

Это МОЖЕТ быть сделано с формулой, но есть предостережение.

В Excel есть удобная функция EVALUATE(), которая оценивает текстовые строки как уравнения и выдает числовой результат. Предостережение связано с ограничениями того, как его можно использовать.

Я догадывался, что ваши диапазоны в столбце U могут быть любого размера, поэтому хорошим способом для подсчета будет вычитание. то есть для U1-U3, извлеките «1-3» и сделайте математику. Эта формула делает это:

 =IFERROR(ABS(EVALUATE((REPLACE(MID(B2,FIND("-",B2)-1,4),3,1,"")))),0)

Используя B5 в качестве примера, часть MID() получает символы по обе стороны от «-» и выдает 1-U3. REPLACE() удаляет U, а EVALUATE() выполняет вычитание, давая -2. ABS() дает 2, а IFERROR() обрабатывает строки без диапазона U. Вот данные с вышеприведенным уравнением в столбце E и сумма этого столбца плюс ваше уравнение выше в столбце F:

Теперь о предостережении. По какой-либо причине EVALUATE() доступна ТОЛЬКО если она используется в определении именованного диапазона. Таким образом, чтобы использовать приведенное выше уравнение, нажмите на любую ячейку, дайте ей имя (я использовал здесь "Funk") и вставьте уравнение в поле «Относится к:».

Теперь имя можно использовать как функцию. Введите "= Funk" (без кавычек) в E2 и заполните.

Если у вас нет вспомогательного столбца, вы можете объединить два уравнения в определении имени. Но я предупреждаю вас, что формулы устранения неполадок в маленькой коробке - это PITA. Жаль, что эта функция недоступна без прыжков через эти обручи.

Надеюсь, это поможет.