Использовать ВЛЕВО в параметре sum_range функции SUMIF (Excel)

Question

У меня есть много столбцов друг на друга, вот так:

Я хотел бы найти все ячейки в этом диапазоне, которые начинаются с 1a , а затем подводить итоги. Итак, в этом примере мы находим 1a10 и 1a20 , поэтому наш результат будет 10+20 или 30 .

Сложная вещь , что после a там может быть одна или две цифры, и я также хотел бы расширить его для работы с двузначными числами перед , а также. Таким образом, мы могли бы иметь:

1a10 1a1 11a1 11a10

Я попытался поэкспериментировать с тем, что я узнал о SUMIF , SUMIFS и SEARCH , но в то время как я смог (в некоторой степени) определить, какие ячейки выбрать, отрезав начало этого, особенно когда длина конца и начало может варьироваться, оставив меня с кучей сложенных операторов IF и нефункциональной формулы.

К сожалению, я не могу использовать макросы, и моя школа работает только до Excel 2013, но я надеюсь, что это все еще возможно.

Благодарю.

Answer 1

Вы можете сделать это с помощью формулы массива

=SUM((LEFT($A$2:$A$8,2)="1a")*(IF(ISNUMBER(--RIGHT($A$2:$A$8,LEN($A$2:$A$8)-2)),RIGHT($A$2:$A$8,LEN($A$2:$A$8)-2),0)))

Поскольку это формула массива, вам нужно использовать Control+Shift+Enter вместо просто Enter.

Кроме того, поскольку это формула массива, вы должны избегать полных ссылок на столбцы, таких как A:A

Вы будете знать, что ввели его правильно, когда увидите {} вокруг своей формулы. Обратите внимание, что {} нельзя добавлять вручную.

ОБНОВИТЬ

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

=SUM((LEFT($A$1:$A$8,LEN(TRIM(C2)))=C2)*(IF(ISNUMBER(--RIGHT($A$1:$A$8,LEN($A$1:$A$8)-LEN(C2))),RIGHT($A$1:$A$8,LEN($A$1:$A$8)-LEN(C2)),0)))

Обратите внимание, что это все еще формула массива. Также был расширен диапазон, включающий значение данных одной цифры после ключа, который вы ищете.

незначительное обновление, изменил -RIGHT на --RIGHT ... но, видимо, - может даже не понадобиться.

Answer 2

Вот еще один подход с SUBSTITUTE:

=SUM(IFERROR(SUBSTITUTE(A1:A7,C1,"")*1,0))

Как это устроено:

• =SUM(IFERROR(SUBSTITUTE(A1:A7,C1,"")*1,0))
• SUBSTITUTE(A1:A7,C1,"") - удаляет 1a из всех строк
• SUBSTITUTE(...)*1 - преобразует записи в число, где это возможно (только строки, в которых ранее был удален 1a ), другие вызовут ошибку
• IFERROR(...,0) - преобразует ошибки в 0

Также это формула массива, поэтому нужно нажать CTRL+SHIFT+ENTER после ее ввода.

Answer 3

Я не хочу делать вашу работу за вас, но я могу дать вам несколько советов ...

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

=IF(LEFT(A2,2)="1a",1,0)) Это вернет 1 для ваших клеток, которые начинаются с "1a", и 0 для тех, которые этого не делают.

В C1 вы могли бы использовать

=LEN(A2) Это вернет количество символов в A2.

Оттуда начните писать операторы IF с формулами (псевдокод ниже, а не фактические формулы).

"If A2=1 AND C1=4, return RIGHT(A2,2)"

"If A2=1 AND C1=3, return RIGHT(A2,1)"

Все, что я делаю, это настраиваю лист, чтобы получить "10" из 1a10 в свой собственный столбец. Затем вы можете сделать любую алгебру, которую вы хотите на полученных числах.