У меня есть коллекция текстовых строк, каждая из которых имеет связанное числовое значение. Мне нужно просуммировать связанные числовые значения для тех записей, которые «подходят». Текстовая запись подпадает под определение, если содержит одну или несколько указанных целевых строк. Запись может потенциально содержать несколько целевых строк или целевую строку более одного раза. Однако я хочу просуммировать связанное значение только один раз для записи, если запись подпадает под определение, если содержит любое совпадение с любой из целей или комбинаций целей.
Например, скажем, ячейки A1:A3 соответственно содержат apple, banana, pear, и каждая из ячеек B1:B3 содержит число 1. Моими целями поиска являются aи p. Все три текстовые записи соответствуют требованиям, поскольку каждая из них содержит по крайней мере один экземпляр по крайней мере одного из целей. Суммирование связанных значений в столбце B должно вернуть результат 3.
Я попытался сделать это с помощью SUMIF и wildcard-целей. Моя формула для этого примера:
=SUM(SUMIF(A:A, {"*a*", "*p*"}, B:B))
Однако это дважды учитывает записи, соответствующие более чем одной цели. В этом случае все три содержат aи два также содержат p, поэтому получается сумма 5.
Как это сделать без двойного учета записей?
решение1
Более гибко размещать критерии в реальных ячейках рабочего листа, а не жестко запрограммировать их в формуле.
Если вы используетевертикальный, непрерывный ряд ячеек (например H1:H2) для этой цели, и предполагая, чтоДиапазонэтовертикальныйдиапазон, вы можете использовать этоформула массива**:
=SUM(IF(MMULT(0+ISNUMBER(SEARCH(TRANSPOSE(Criteria),Range)),ROW(Criteria)^0)>0,Values))
Если вы настаиваете на наличии критериев в формуле, то:
=SUM(IF(MMULT(0+ISNUMBER(SEARCH({"p","a"},Range)),{1;1})>0,Values))
С уважением
решение2
Вот относительно простое решение. При всех ассоциированных значениях 1оно дает желаемый результат 3, но я назначил другие значения, чтобы продемонстрировать, что выбраны правильные значения, и включил несоответствующую запись для верности.
Список записей находится в столбце C, а их соответствующие значения — в столбце D. Результат находится в E1.
Только определенные функции могут использовать подстановочные знаки, поэтому здесь используется ПОИСК для целевой строки.
Типичный метод обработки нескольких критериев OR — это сложение результатов каждого теста. Однако это приводит к двойному счету, когда элементы могут соответствовать нескольким критериям. Чтобы решить эту проблему, агрегированные тесты критериев проверяются, чтобы увидеть, больше ли сумма нуля, и это то, что используется с соответствующим значением.
Обработка целевых строк поиска как массива усложняется, поскольку общие функции, используемые для таких формул, вычисляют результат для всего массива, прежде чем применять его к следующему термину. Поэтому я обрабатывал каждый критерий отдельно. Для большего количества критериев просто добавьте еще один ISNUMBER(SEARCH("target",range))для каждого в скобках перед >0тестом.
СУММПРОИЗВ выполняет вычисления в стиле массива с помощью обычной формулы, не являющейся формулой массива.
Формула в E1:
=SUMPRODUCT(((ISNUMBER(SEARCH("p",C1:C4))+ISNUMBER(SEARCH("a",C1:C4)))>0)*D1:D4)
решение3
Я действительно думал, что это будет SUMPRODUCT(--формула, но я не могу заставить ее работать. Это должно работать, хотя -
=SUM(IF(FREQUENCY(IF(NOT(ISERR(SEARCH({"d","g"},A2:A10))),ROW(A2:A10)),ROW(A2:A10))>0,1))
Где {"d","g"}будет ваш массив строк поиска.
Это формула массива, поэтому после ее ввода нужно нажать ctrl+ shft+ entr, и в строке формул вокруг всей функции появятся фигурные скобки.
Обратите внимание, что это сработает только в том случае, если вы ищете по одному столбцу.
Возможно, это не сработает, учитывая подстановочные знаки. Может быть, regex — это то, что вам нужно?