Countif без преобразования TEXT в NUMBER

Я пытаюсь установить макрос, который будет сравнивать несколько списков, создавать кросс-таблицу с уникальными значениями и отображать, сколько раз значение присутствует в каждом списке.

Я делаю все правильно, за одним исключением. При использовании Countif(s) =COUNTIFS(Source!$A$2:$A$5;[@Values]) , она внутренне преобразует «Текстовые числа» (например, 001, 00000002) в числа (например, 1, 2). Я хотел бы избежать этого поведения и искать ТОЧНО ту же самую ценность, без преобразования.

Пример данных:

 List1 List2 1 0001 0001 2 00000002 

То, что я получаю прямо сейчас (НЕПРАВИЛЬНО):

CompareListsWrong

Что я хочу получить (ОЖИДАЕМ):
CompareListsExpected

Мой вопрос:
Как я могу правильно подсчитать значения в списке, без внутреннего преобразования «Текстовые числа» в числа?

Эта формула массива может быть подходящей для вас:

 =MIN(SUMPRODUCT(IF(LEN($A$2:$A$5)=LEN(Table1[@Values]),1,0)),SUMPRODUCT(IF($A$2:$A$5=Table1[@Values],1,0))) 

Поместите и CTRL + SHIFT + ВВОД . В Table1[@Values] , Table1 – это имя вашей таблицы.

Я решил проблему с простым UDF.

 Function countifsExact(criteria_range As Range, criteria As String) As Long Dim cell As Range For Each cell In criteria_range If cell = criteria Then countifsExact = countifsExact + 1 End If Next cell End Function 

EDIT1 :
Я сделал еще одну версию UDF, используя некоторые рекомендации, приведенные в написании эффективных VBA UDF (часть 1) Чарльза Уильямса и в написании эффективных VBA UDF (часть 2) Чарльза Уильямса .
В основном:

  • Хранение критерия_изменяет один раз в переменной Variant, избегая больших накладных расходов каждый раз, когда программа VBA передает данные из ячейки Excel в переменную VBA
  • Использование свойства Range.Value2 вместо Range.Value
  • Использование функции excel MATCH для получения начальной точки в отсортированном диапазоне с выходом при изменении значения.

EDIT2 :
Однако гораздо лучшим решением является использование формулы SUMPRODUCT как таковой:

 =SUMPRODUCT(--(EXACT(Source!$A$2:$A$5;[@Values]))) 
  • Возвращаемая формула Значение столбца A для строки, содержащей значение MAX диапазона
  • Ячейка содержит строку в другом листе
  • Функция IF: уравнение SQRT как значение, если true
  • Вывод текстовых значений
  • Excel: выполнить SUMIF, где критерии представляют собой список с разделителями-запятыми
  • Извлечение десятичных чисел из строки в Excel
  • Преобразование в 3 десятичных числа при сохранении в качестве формата числа для целей расчета
  • Соответствующий текст с функцией «или» в Excel приводит к #VALUE! ошибка?
  • Использование формулы для условного суммирования в Excel
  • Подсчитайте уникальные элементы в списке, которые соответствуют нескольким критериям
  • В Excel, как вы смотрите на ряд ячеек и оцениваете, где существует значение, а затем даете результат на основе этого?
  • Interesting Posts

    Показывать только поля, у которых не было дубликатов из одного столбца в другом

    График sourcedata несколько диапазонов перестали работать

    Excel vba macro для автоматического скачивания файла, когда он добавлен на сайт Sharepoint

    Включить значение ячейки в массив фильтров, если другое значение ячейки равно «ИСТИНА»,

    Как получить ссылку на ячейку второго наименьшего значения из диапазона данных

    преобразование .xlsx в xml в Perl

    AVG клеток рядом с клетками, используемыми в другой формуле

    Сравните два столбца excel, которые наиболее часто встречаются в конкретную дату

    Импорт данных из Excel и привязка его с помощью таблицы html в Angular 4

    Как использовать код «IF» с несколькими условиями?

    Изменение существующей таблицы xlsx с использованием Apache POI приводит к ошибке нечитаемого содержимого

    Чтение определенных столбцов из файла Excel в C ++

    Вычислить значение на основе условия в другом столбце в Excel

    Соединение между версией Excel и Access db 2007 с использованием C #

    Создавать отличные возможности с различными правами доступа, такими как чтение / запись во время выполнения

    Давайте будем гением компьютера.