У Эстер есть длинный список ячеек в столбце A, которые содержат ряд кодов мутаций, таких как «AKT 142» или «BRAF 1975». В столбце B указаны значения, связанные с этими кодами мутаций. Ей нужна формула, которая суммирует значения в столбце B, для которых соответствующий код мутации в столбце A начинается с той же последовательности, что и все те, которые начинаются с AKT или BRAF. Эстер подозревает, что это можно сделать с помощью функции СУММЕСЛИ, но она не знает, как заставить ее обращать внимание только на первую часть кода мутации.

Есть много способов подойти к этой проблеме, но в этом совете я сосредоточусь только на трех возможных решениях.

Использование вспомогательного столбца

Если макет вашего рабочего листа позволяет, вы можете добавить вспомогательный столбец, содержащий только первую часть кодов мутации. Поскольку ваши коды мутаций находятся в столбце A, вы можете вставить следующую формулу в первую ячейку вспомогательного столбца:

=LEFT(A1, SEARCH(" ",A1,1)-1)

Скопируйте его для необходимого количества ячеек, и вы получите вспомогательный столбец, содержащий все коды мутаций перед пробелом. Затем вы можете использовать желаемую формулу СУММЕСЛИ для суммирования на основе содержимого вспомогательного столбца.

Использование SUMPRODUCT

Довольно уникальный подход к решению проблемы — использование функции СУММПРОИЗВ. Допустим, вы поместили в ячейку E1 интересующий вас код предисловия. (Так, например, вы можете поместить «AKT» в ячейку E1.) Затем вы можете рассчитать желаемую сумму, используя следующую формулу:

=SUMPRODUCT(--(LEFT(A:A,LEN($E$1))=$E$1) * B:B)

Это работает, потому что СУММПРОИЗВ проверяет, соответствует ли крайняя левая часть ячейки в столбце A тому, что вы поместили в ячейку E1. Если это так, то сравнение вернет 1; в противном случае возвращается 0. Затем результат умножается на соответствующую ячейку в столбце B и суммируется.

Непосредственное использование СУММЕСЛИ

Возможно, самый чистый подход — просто использовать СУММЕСЛИ напрямую. Вы знаете, из использования подхода вспомогательного столбца, что вы можете использовать СУММЕСЛИ, чтобы просмотреть содержимое ячейки, а затем выборочно суммировать другой столбец. Вы делаете это в общем виде:

=SUMIF(Check_Range, Criterion, Sum_Range)

Таким образом, если вы хотите суммировать значения в столбце B на основе того, что находится в столбце A, вы можете сделать следующее:

=SUMIF(A:A, "AKT", B:B)

Это, конечно, будет соответствовать только тем ячейкам в столбце A, которые содержат только AKT. Однако это не ситуация с Эстер — коды мутаций в столбце A содержат не только AKT. Здесь в игру вступает использование подстановочных знаков в спецификации критерия. Все, что нужно сделать Эстер, — это добавить звездочку следующим образом:

=SUMIF(A:A, "AKT*", B:B)

Теперь СУММЕСЛИ возвращает правильную сумму, основанную только на тех ячейках в столбце A, которые начинаются с букв AKT. Не имеет значения, что следует за символами AKT в каждой ячейке, потому что звездочка говорит Excel, что он должен «принимать все, что следует за этими тремя символами».

Вы даже можете сделать этот подход более общим. Предположим, вы указали желаемый код предисловия (тот, по которому вы хотите суммировать)

в ячейку E1. Затем вы можете поместить в ячейку E2 следующее:

=SUMIF(A:A, E1 & "*", B:B)

Теперь, если E1 содержит «AKT», вы получите сумму значений для этого кода предисловия. Если вы измените E1 на «BRAF», вы получите сумму для этого кода предисловия без необходимости изменять формулу в E2.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (13614) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365.