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

430
Geza Kerecsenyi

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

Cells on top of each other

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

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

1a10 1a1 11a1 11a10

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

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

Благодарю.

0
Ваш вопрос действительно ясен. «Я также хотел бы расширить его для работы с двузначными числами до а», так что вам нужно искать, чтобы начать с 1а или с 1а? Máté Juhász 6 лет назад 1

3 ответа на вопрос

3
Forward Ed

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

=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

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

POC

ОБНОВИТЬ

Если вы хотите сделать формулу немного более устойчивой, чтобы иметь дело с более длинными строками поиска, такими как 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)))

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

POC2

небольшое обновление, изменилось -RIGHTк --RIGHT... но, видимо, - не может быть даже необходимо.

Работает отлично; именно то, что мне нужно, и работает безупречно с любой длиной идентификатора или номера! Я удивлен, как вы смогли сделать формулу такой компактной, особенно, когда имели дело с чем-то таким сложным. Geza Kerecsenyi 6 лет назад 0
0
user2800

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

Есть способы, которыми вы можете сделать это с помощью одной или двух больших, длинных формул, но вы можете разбить их на несколько формул в отдельных столбцах, чтобы их было легче понять. Например, в 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 в свой собственный столбец. Затем вы можете сделать любую алгебру, которую вы хотите на полученных числах.

И как бы я их подвел? Мне понадобится 3-мерная структура, так как у меня есть 20 различных чисел, идущих перед `a`, что будет очень сложной структурой столбцов-помощников ... Geza Kerecsenyi 6 лет назад 0
«Я не хочу делать вашу работу за вас, но я могу дать вам несколько советов», этот тип подсказок не подходит для этого сайта. Если вы действительно не хотите предлагать решение, пишите только комментарий. Если вы пишете ответ, то пишите его хорошо, теперь вы потратили больше времени на подсказки, чем на написание решения. Написание нескольких формул не означает «делать работу других» Máté Juhász 6 лет назад 0
@ Máté Juhász - Спасибо за советы, я все еще новичок на сайте. Не было достаточно репутации, чтобы оставлять комментарии, когда я помещал свое оригинальное сообщение. Мой подход к ОП заключался в том, чтобы по существу выполнять все необходимые вычисления / формулы в виде отдельного столбца, чтобы было проще следовать логике, чем приведенные выше формулы из однострочного массива. user2800 6 лет назад 0
@Geza Kerecsenyi Учитывая ваш комментарий выше, тогда да, другие ответы будут работать намного лучше, чем мой. user2800 6 лет назад 0
0
Máté Juhász

Вот еще один подход с 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 после ее ввода.

enter image description here

Похожие вопросы