Новости:

Теперь на форум можно залогиниться / зарегистрироваться с помощью ВКонтакте. Уже существующие пользователи могут связать свою учетную запись с аккаунтом ВКонтакте одним кликом в профиле пользователя http://forum.msexcel.ru/index.php?action=profile;area=account

Главное меню

Суммирование ячеек, пока выполняется условие - формулой

Автор AlWin, 22.07.2013, 11:56

« назад - далее »

AlWin

Уважаемые знатоки.
Помогите, пожалуйста, решить следующую проблему с помощью формул.

Колонка «А» содержит численные значения.
Колонка «В» - определители в виде текста. Ячейки В1, В5, В12 и т.д. содержат определитель "param".

Требуется для ячеек A1, А5, А12 и т.д. составить формулу суммирования диапазона от нижележащей (от А2, А6, А13) и до тех пор, пока в соседней ячейке в колонке «В» не встретится следующий "param" (до А4, А11 и тп)
Т.е. суммировать ячейки по «А», пока в ячейках по «В» выполняется условие, что они <> "param" до первого встретившегося нижележащего  "param".

Пробовал решить: An=СУММ(An+1:Am-1) c помощью ПОИСКПОЗ по колонке В, но у меня не получается:
1.   Вместо "n" вставить в тело формулы значение, возвращаемое функцией СТРОКА()
2.   Вместо "m" вставить в тело формулы значение, возвращаемое функцией ПОИСКПОЗ("param";Bn:B1000;0) , да и условный конец диапазона «В1000» в последней формуле также хотелось бы заменить на что-то более серьезное

iron priest


AlWin

в упрощенном виде как-то так...

iron priest

=СУММПРОИЗВ((B2:$B$18<>$B$1)*A2:$A$18)-СУММПРОИЗВ((B2:$B$18=$B$1)*A2:$A$18)

AlWin

Видно не очень у меня получается объяснить, что хочу.
Мне не нужна формула для A19. Мне нужна формула для ячеек А1, А5, А12, которая бы сама определяла необходимый (напр. А13:А18 для ячейки А12) диапазон для суммирования и подставляла его в функцию суммирования (напр. как в колонке Е).
Упсс! Исправляю глупости в колонке Е и прикладываю повторный файл примера

iron priest


Serge 007

Вариант в отдельном столбце
Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Ю-money: 41001419691823 | WMR:126292472390

AlWin

Большое спасибо за участие и iron priest и Serge 007.
Попробую вникнуть и в формат записи и (если получится) в смысл предложенных формул.
Однако в обоих случаях формулы не универсальны для разных ячеек и не могут самостоятельно определять диапазон суммирования (требуется вручную указывать диапазон для каждой вычисляемой ячейки).
Хотя нижнюю границу диапазона вполне можно закрепить ячейками $A$18 и $B$18, а параметр «param» закрепить за ячейкой $B$1 (как у iron priest).
Вот если б и текущая строка ( для ячеек В1,B2, В5, В6, В12, В13) в формуле задавалась выражением типа «В&СТРОКА()» или «В+СТРОКА()» (как у Serge 007), но такая запись не работает.

Serge 007

Цитата: AlWin от 22.07.2013, 15:15...требуется вручную указывать диапазон для каждой вычисляемой ячейки...
На основании чего сделан такой неожиданный вывод?

Ещё вариант:
=ЕСЛИ(B1="param";СУММ(ИНДЕКС(A1:A$19;ПОИСКПОЗ("param";B1:B$19;)):ИНДЕКС(A1:A$19;ПОИСКПОЗ("param";B2:B$19;)));"")
Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Ю-money: 41001419691823 | WMR:126292472390

AlWin

Уважаемый Serge 007.
Действительно, нужно было попробовать копи-паст.
Однако меня сильно не устраивала сползающая вниз нижняя граница диапазона. Поэтому в ячейке Н1 я заменил B18 на $B$18. Затем скопировал и вставил формулу в Н12. Получил #Н/Д. Правда, методом тыка заменил $B$18 на $B$19 и проблема как-бы решилась.
Спасибо за вторую формулу. Кажется гораздо более понятной.
Однако ни первая, ни вторая формула не хотят работать в требуемых по изначальному условию ячейках - А1, А5, А12.
При копировании формул получаю циклическую ссылку...


AlWin

iron priest
Ну конечно же, формула копируется и работает для всех ячеек. Еще раз большое Вам спасибо (ну рабочий день у меня сегодня тяжелый)

Serge 007
Ну да, нельзя заставить работать формулы для ячеек, если они сами находятся в указанных в формулах диапазонах.
А ваши формулы - как варианты для решения в примерном виде An=СУММ(An+1:Am-1).
Тогда, если можно, помогите понять стилистику составления подобных конструкций в формулах (или помогите найти, где об этом можно почитать - чесслово я искал, но не нашел). Напр. замена В1 в Вашей первой формуле на "B"&Строка() результата не дает.

Serge 007

Цитата: AlWin от 22.07.2013, 17:16Ну да, нельзя заставить работать формулы для ячеек, если они сами находятся в указанных в формулах диапазонах.
Можно, если включить итерации

Цитата: AlWin от 22.07.2013, 17:16Напр. замена В1 в Вашей первой формуле на "B"&Строка() результата не дает.
Смотря какого результата Вы ожидаете :)
Если вставить формулу
="B"&СТРОКА()например в ячейку А1, то Вы получите вполне конкретный результат: "В1". Вот только что Вы с ним собираетесь делать? Если использовать в формуле как адрес конкретной ячейки - то необходимо использовать ДВССЫЛ(), так как ссылку, заданную текстовой строкой, в формулах можно использовать только с помощью этой функции

ЗЫ Где почитать не знаю, я формулы писать на форумах учился

Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Ю-money: 41001419691823 | WMR:126292472390

AlWin

Еще раз огромное спасибо за конкретные ответы. Всего Вам доброго.

Sarytai

Цитата: Serge 007 от 22.07.2013, 14:18
Вариант в отдельном столбце

простите, а в формуле
=СУММ(ДВССЫЛ("a"&ПОИСКПОЗ("param";B1:B18;)+СТРОКА()-1&":a"&ПОИСКПОЗ("param";B2:B18;)+СТРОКА()-1))*(B1="param")
нет ошибки? ведь если в первой части убрать единицу
=СУММ(ДВССЫЛ("a"&ПОИСКПОЗ("param";B5:B22;)+СТРОКА()&":a"&ПОИСКПОЗ("param";B6:B22;)+СТРОКА()-1))*(B5="param")

формула подойдет и для А столбца?

Serge 007

Цитата: Sarytai от 23.07.2013, 19:36...в формуле нет ошибки?..
А Вы в файл, что я выкладывал, загляните. Есть там ошибка?
Цитата: Sarytai от 23.07.2013, 19:36...формула подойдет и для А столбца?..
Попробуйте. Если подойдёт - выкладывайте рабочий файл
Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Ю-money: 41001419691823 | WMR:126292472390