Суммирование и подсчет ячеек за определенный месяц

Дата: Четверг, 26.11.2015, 15:31 | Сообщение № 1

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 102

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013, 2016

Добрый день.

Не нашел на форуме решение данного вопроса, хотя наверно плохо искал.
Задача такова: имеется таблица заполненная по месяцам, необходимо чтобы при указании в ячейке A17 определенного месяца в колонках В17 и С17 отображались сумма и количество непустых ячеек в данном месяце, т.е. при изменении месяца в ячейке А17 на другой происходил перерасчет значений.
Кол-во строчек будет постоянно дополняться и статистика будет вестись уже на другом листе.

К сообщению приложен файл: 9319349.xls (33.5 Kb)

SLAVICK

Дата: Четверг, 26.11.2015, 15:40 | Сообщение № 2

Группа: Модераторы

Ранг: Старожил

Сообщений: 2290

Репутация: 766 ±

Замечаний: 0% ±

2019

Если итоговые строки будут то так:

Код

=ГПР(A17;$C$2:$AA$14;ПОИСКПОЗ("должно быть";$B$1:$B$14;0)-1;0)

Код

=ГПР(A17;$C$2:$AA$14;ПОИСКПОЗ("должно быть";$B$1:$B$14;0);0)

К сообщению приложен файл: 5056763.xls (33.5 Kb)

Иногда все проще чем кажется с первого взгляда.

Сообщение отредактировал SLAVICK - Четверг, 26.11.2015, 15:44

Дата: Четверг, 26.11.2015, 15:42 | Сообщение № 3

Группа: Админы

Ранг: Местный житель

Сообщений: 16911

Репутация: 6616 ±

Замечаний: ±

2003; 2007; 2010; 2013 RUS

Такой вариант

Код

=СУММ(ИНДЕКС('1'!C3:AA3;ПОИСКПОЗ(A2;'1'!C2:AA2;)):ИНДЕКС('1'!C:AA;СЧЁТЗ('1'!A:A)+1;ПОИСКПОЗ(A2;'1'!C2:AA2;)))

Цитата Garik007, 26.11.2015 в 15:31, в сообщении № 1

статистика будет вестись уже на другом листе

Перенес всё на другой лист и убрал итоговые строки

К сообщению приложен файл: 9319349_1.xls (34.5 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Дата: Пятница, 27.11.2015, 09:19 | Сообщение № 4

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 102

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013, 2016

SLAVICK, с итоговыми строками не совсем то, т.к. нужно следить за суммированием при увеличении кол-ва строк.

_Boroda_, как всегда на высоте, спасибо.

Однако встал еще один вопрос по данной формуле: если в ячейке В18 указать год, после чего в колонке F сцепить месяц с этим годом, т.е. когда в колонке А указывается дата в формате ДД.ММ.ГГГГ то формула работает, а когда в этой колонке берутся значения из колонки F, то почему-то получается #Н/Д. Это первая проблема.
2. Можно ли при решении первой проблемы доделать формулу так, чтобы она считала все колонки за текущий месяц, т.е. если в январе были поступления 1,5,25, а в феврале 4,6 и 10, то в итоге отображалась сумма и количество всех ячеек за один, два, три или более дней месяца.

К сообщению приложен файл: 9319349_1-1-.xls (37.5 Kb)

Дата: Пятница, 27.11.2015, 09:26 | Сообщение № 5

Группа: Админы

Ранг: Местный житель

Сообщений: 16911

Репутация: 6616 ±

Замечаний: ±

2003; 2007; 2010; 2013 RUS

1. Все просто - добавьте 4 минуса

Код

=СУММ(ИНДЕКС('1'!$C$3:$AA$3;ПОИСКПОЗ(--$A2;'1'!$C$2:$AA$2;)):ИНДЕКС('1'!$C:$AA;СЧЁТЗ('1'!$A:$A)+1;ПОИСКПОЗ(--$A2;'1'!$C$2:$AA$2;)))

2. Если я Вас правильно понял, то это кардинально меняет метод решения задачи. Покажите в примере - как у Вас будут расположены 1,5,25 января

Дата: Пятница, 27.11.2015, 09:39 | Сообщение № 6

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 102

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013, 2016

Даты будут добавляться путем добавления колонок, для каждой даты своя колонка.

К сообщению приложен файл: 9774380.xls (37.5 Kb)

Дата: Пятница, 27.11.2015, 09:55 | Сообщение № 7

Группа: Админы

Ранг: Местный житель

Сообщений: 16911

Репутация: 6616 ±

Замечаний: ±

2003; 2007; 2010; 2013 RUS

Тогда так

Код

=СУММПРОИЗВ(('1'!$C$3:ИНДЕКС('1'!C$3:IV$65536;СЧЁТЗ('1'!A:A)+1;СЧЁТЗ('1'!$2:$2)))*(ТЕКСТ('1'!C$2:ИНДЕКС('1'!$2:$2;СЧЁТЗ('1'!$2:$2)+2);"МГ")=ТЕКСТ(--A2;"МГ")))

К сообщению приложен файл: 9774380_1.xls (39.5 Kb)

Дата: Пятница, 27.11.2015, 10:05 | Сообщение № 8

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 102

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013, 2016

То что надо, спасибо, для подсчета количества получается СУММПРОИЗВ нужно просто заменить на СЧЁТ.

Сообщение отредактировал Garik007 - Пятница, 27.11.2015, 10:10

Дата: Пятница, 27.11.2015, 10:18 | Сообщение № 9

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 102

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013, 2016

Замена СУММПРОИЗВ на СЧЁТ не прокатила. Пример в файле.

К сообщению приложен файл: 8675277.xls (39.5 Kb)

Дата: Пятница, 27.11.2015, 10:26 | Сообщение № 10

Группа: Админы

Ранг: Местный житель

Сообщений: 16911

Репутация: 6616 ±

Замечаний: ±

2003; 2007; 2010; 2013 RUS

Код

=СУММПРОИЗВ(--('1'!$C$3:ИНДЕКС('1'!C$3:IV$65536;СЧЁТЗ('1'!A:A)+1;СЧЁТЗ('1'!$2:$2))*(ТЕКСТ('1'!C$2:ИНДЕКС('1'!$2:$2;СЧЁТЗ('1'!$2:$2)+2);"МГ")=ТЕКСТ(--A2;"МГ"))>0))

или массивная формула (вводится одновременным нажатием Контрл Шифт Ентер)

Код

=СЧЁТ(1/('1'!$C$3:ИНДЕКС('1'!C$3:IV$65536;СЧЁТЗ('1'!A:A)+1;СЧЁТЗ('1'!$2:$2)))/(ТЕКСТ('1'!C$2:ИНДЕКС('1'!$2:$2;СЧЁТЗ('1'!$2:$2)+2);"МГ")=ТЕКСТ(--A2;"МГ")))

К сообщению приложен файл: 8675277_1.xls (42.5 Kb)

Дата: Пятница, 27.11.2015, 10:35 | Сообщение № 11

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 102

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013, 2016

_Boroda_, огроменное спасибо.

Дата: Среда, 20.04.2016, 14:05 | Сообщение № 12

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 102

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013, 2016

Добрый день, в продолжении темы.
Желательно без использования массива, хотя можно и с ним.

При подсчете количества заполненных ячеек по приведенной формуле учитываются все непустые ячейки, можно ли учитывать только первую непустую ячейку, т.е. независимо от того сколько было проведено оплат по договору учитывался только сам факт оплаты по нему в текущем месяце.
[moder]Тема закрыта. Причина: нарушение п. 5q Правил форума[/moder]

К сообщению приложен файл: _8675277_1.xls (41.5 Kb)

Сообщение отредактировал Pelena - Среда, 20.04.2016, 16:05