Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Сумма за период - Страница 2 - Мир MS Excel

Старая форма входа
  • Страница 2 из 2
  • «
  • 1
  • 2
Модератор форума: китин  
Мир MS Excel » Работа и общение » Мозговой штурм » Сумма за период (Сумма за период, зная суммы по месяцам)
Сумма за период
Понравилась задача?
ZORRO2005 Дата: Четверг, 13.04.2017, 11:55 | Сообщение № 21
Группа: Друзья
Ранг: Обитатель
Сообщений: 382
Репутация: 148 ±
Замечаний: 0% ±

Excel2010
Вскрываюсь...
Ниже 5 вариантов, если в A1 просто номер года.
Массивные:
Код
=СУММ(ГПР(Ч(ИНДЕКС(МЕСЯЦ(СТРОКА(ДВССЫЛ(B6&":"&C6)));));ЕСЛИ({1:0};СТОЛБЕЦ(A:L);A$3:L$3/ДЕНЬ(ДАТА(A$1;СТОЛБЕЦ(B:M);)));2;))

Код
=СУММ(ТРАНСП(ЧАСТОТА(МЕСЯЦ(СТРОКА(ДВССЫЛ(B6&":"&C6)));СТОЛБЕЦ(A:L)))*A$3:M$3/ДЕНЬ(ДАТА(A$1;СТОЛБЕЦ(B:N);)))

Код
=СУММ(ПРОСМОТР(МЕСЯЦ(СТРОКА(ДВССЫЛ(B6&":"&C6)));СТОЛБЕЦ(A:L);A$3:L$3/ДЕНЬ(ДАТА(A$1;СТОЛБЕЦ(B:M);))))

Код
=СУММ(A$3:L$3/ДЕНЬ(ДАТА(A$1;СТОЛБЕЦ(B:M);))*(ТЕКСТ(СТРОКА(ДВССЫЛ(B6&":"&C6));"ММММ")=A$2:L$2))

Немассивная:
Код
=СУММ(ИНДЕКС(A$3:L$3/ДЕНЬ(ДАТА(A$1;СТОЛБЕЦ(B:M);));Ч(ИНДЕКС(МЕСЯЦ(СТРОКА(ДВССЫЛ(B6&":"&C6)));))))
К сообщению приложен файл: 7475127_OK.xls (30.5 Kb)
 
Ответить
СообщениеВскрываюсь...
Ниже 5 вариантов, если в A1 просто номер года.
Массивные:
Код
=СУММ(ГПР(Ч(ИНДЕКС(МЕСЯЦ(СТРОКА(ДВССЫЛ(B6&":"&C6)));));ЕСЛИ({1:0};СТОЛБЕЦ(A:L);A$3:L$3/ДЕНЬ(ДАТА(A$1;СТОЛБЕЦ(B:M);)));2;))

Код
=СУММ(ТРАНСП(ЧАСТОТА(МЕСЯЦ(СТРОКА(ДВССЫЛ(B6&":"&C6)));СТОЛБЕЦ(A:L)))*A$3:M$3/ДЕНЬ(ДАТА(A$1;СТОЛБЕЦ(B:N);)))

Код
=СУММ(ПРОСМОТР(МЕСЯЦ(СТРОКА(ДВССЫЛ(B6&":"&C6)));СТОЛБЕЦ(A:L);A$3:L$3/ДЕНЬ(ДАТА(A$1;СТОЛБЕЦ(B:M);))))

Код
=СУММ(A$3:L$3/ДЕНЬ(ДАТА(A$1;СТОЛБЕЦ(B:M);))*(ТЕКСТ(СТРОКА(ДВССЫЛ(B6&":"&C6));"ММММ")=A$2:L$2))

Немассивная:
Код
=СУММ(ИНДЕКС(A$3:L$3/ДЕНЬ(ДАТА(A$1;СТОЛБЕЦ(B:M);));Ч(ИНДЕКС(МЕСЯЦ(СТРОКА(ДВССЫЛ(B6&":"&C6)));))))
Автор - ZORRO2005
Дата добавления - 13.04.2017 в 11:55
krosav4ig Дата: Четверг, 13.04.2017, 12:16 | Сообщение № 22
Группа: Друзья
Ранг: Старожил
Сообщений: 2347
Репутация: 997 ±
Замечаний: 0% ±

Excel 2007,2010,2013
у мну вот такой монстр в 153 символа
Код
=СУММ(ПРОСМОТР(("1."&A1)+СТРОКА($1:$366)-1;--(A$2:L$2&A1);A$3:L$3/ДЕНЬ(КОНМЕСЯЦА(A$2:L$2&A1;0)))*МУМНОЖ(-(("1."&A1)+СТРОКА($1:$366)-{0;1}<=B6:C6);{1:-1}))

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460
 
Ответить
Сообщениеу мну вот такой монстр в 153 символа
Код
=СУММ(ПРОСМОТР(("1."&A1)+СТРОКА($1:$366)-1;--(A$2:L$2&A1);A$3:L$3/ДЕНЬ(КОНМЕСЯЦА(A$2:L$2&A1;0)))*МУМНОЖ(-(("1."&A1)+СТРОКА($1:$366)-{0;1}<=B6:C6);{1:-1}))
Автор - krosav4ig
Дата добавления - 13.04.2017 в 12:16
ZORRO2005 Дата: Четверг, 13.04.2017, 13:02 | Сообщение № 23
Группа: Друзья
Ранг: Обитатель
Сообщений: 382
Репутация: 148 ±
Замечаний: 0% ±

Excel2010
krosav4ig, если поменять год в A1, например на 2015, то формула неправильно считает.
Извиняюсь, все правильно.


Сообщение отредактировал ZORRO2005 - Четверг, 13.04.2017, 13:05
 
Ответить
Сообщениеkrosav4ig, если поменять год в A1, например на 2015, то формула неправильно считает.
Извиняюсь, все правильно.
Автор - ZORRO2005
Дата добавления - 13.04.2017 в 13:02
vikttur Дата: Четверг, 13.04.2017, 13:25 | Сообщение № 24
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2941
Репутация: 526 ±
Замечаний: 0% ±

Код
=СУММ(Ч(СМЕЩ(A$3;;МЕСЯЦ(СТРОКА(ДВССЫЛ(B6&":"&C6)))-1))/ДЕНЬ(КОНМЕСЯЦА(СТРОКА(ДВССЫЛ(B6&":"&C6));0)))
 
Ответить
Сообщение
Код
=СУММ(Ч(СМЕЩ(A$3;;МЕСЯЦ(СТРОКА(ДВССЫЛ(B6&":"&C6)))-1))/ДЕНЬ(КОНМЕСЯЦА(СТРОКА(ДВССЫЛ(B6&":"&C6));0)))
Автор - vikttur
Дата добавления - 13.04.2017 в 13:25
MCH Дата: Четверг, 13.04.2017, 14:06 | Сообщение № 25
Группа: Админы
Ранг: Старожил
Сообщений: 2004
Репутация: 752 ±
Замечаний: ±

Код
=СУММ((ТЕКСТ(СТРОКА(ДВССЫЛ(B6&":"&C6));"ММММГГГГ")=A$2:L$2&ЛЕВБ(A$1;4))*A$3:L$3/ДЕНЬ(КОНМЕСЯЦА(A$2:L$2&ЛЕВБ(A$1;4);0)))

Код
=СУММ((КОНМЕСЯЦА(СТРОКА(ДВССЫЛ(B6&":"&C6));-1)=(A$2:L$2&ЛЕВБ(A$1;4))-1)*A$3:L$3/ДЕНЬ(КОНМЕСЯЦА(A$2:L$2&ЛЕВБ(A$1;4);0)))

Код
=СУММ((ТЕКСТ(СТРОКА(ДВССЫЛ(B6&":"&C6));"ММММГГГГ \го\д")=A$2:L$2&A$1)*A$3:L$3/ДЕНЬ(КОНМЕСЯЦА(A$2:L$2&ЛЕВБ(A$1;4);0)))

Даты могут быть любыми, не обязательно в текущем году
 
Ответить
Сообщение
Код
=СУММ((ТЕКСТ(СТРОКА(ДВССЫЛ(B6&":"&C6));"ММММГГГГ")=A$2:L$2&ЛЕВБ(A$1;4))*A$3:L$3/ДЕНЬ(КОНМЕСЯЦА(A$2:L$2&ЛЕВБ(A$1;4);0)))

Код
=СУММ((КОНМЕСЯЦА(СТРОКА(ДВССЫЛ(B6&":"&C6));-1)=(A$2:L$2&ЛЕВБ(A$1;4))-1)*A$3:L$3/ДЕНЬ(КОНМЕСЯЦА(A$2:L$2&ЛЕВБ(A$1;4);0)))

Код
=СУММ((ТЕКСТ(СТРОКА(ДВССЫЛ(B6&":"&C6));"ММММГГГГ \го\д")=A$2:L$2&A$1)*A$3:L$3/ДЕНЬ(КОНМЕСЯЦА(A$2:L$2&ЛЕВБ(A$1;4);0)))

Даты могут быть любыми, не обязательно в текущем году
Автор - MCH
Дата добавления - 13.04.2017 в 14:06
AlexM Дата: Четверг, 13.04.2017, 14:10 | Сообщение № 26
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1129 ±
Замечаний: 0% ±

Excel 2003
Формулы массива.
Если диапазон в пределах года из А1
В А1 - 2016 год - 99 символов
Код
=СУММ(A$3:L$3*(МЕСЯЦ(СТРОКА(ДВССЫЛ(B6&":"&C6)))=СТОЛБЕЦ(A:L))/ДЕНЬ(ДАТА(ЛЕВБ(A$1;4);СТОЛБЕЦ(B:M);)))
В А1 - 2016 - 91 символ
Код
=СУММ(A$3:L$3*(МЕСЯЦ(СТРОКА(ДВССЫЛ(B6&":"&C6)))=СТОЛБЕЦ(A:L))/ДЕНЬ(ДАТА(A$1;СТОЛБЕЦ(B:M);)))
Если диапазон любой, а счет только за год в А1
В А1 - 2016 год - 118 символов
Код
=СУММ(A$3:L$3*(ТЕКСТ(СТРОКА(ДВССЫЛ(B6&":"&C6));"МГГГ")=СТОЛБЕЦ(A:L)&ЛЕВБ(A$1;4))/ДЕНЬ(ДАТА(ЛЕВБ(A$1;4);СТОЛБЕЦ(B:M);)))
по наводке МСН - 114 символов
Код
=СУММ(A$3:L$3*(ТЕКСТ(СТРОКА(ДВССЫЛ(B6&":"&C6));"ММММГГГ \го\д")=A$2:L$2&A$1)/ДЕНЬ(ДАТА(ЛЕВБ(A$1;4);СТОЛБЕЦ(B:M);)))
В А1 - 2016 - 100 символов
Код
=СУММ(A$3:L$3*(ТЕКСТ(СТРОКА(ДВССЫЛ(B6&":"&C6));"ММММГГГ")=A$2:L$2&A$1)/ДЕНЬ(ДАТА(A$1;СТОЛБЕЦ(B:M);)))
Немассивный вариант получаем заменой функции СУММ() на СУММПРОИЗВ(), на 6 символов длинее.
Приложил файл с самыми короткими формулами из показанных выше.
К сообщению приложен файл: 3929065.xls (45.0 Kb)


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Сообщение отредактировал AlexM - Четверг, 13.04.2017, 14:21
 
Ответить
СообщениеФормулы массива.
Если диапазон в пределах года из А1
В А1 - 2016 год - 99 символов
Код
=СУММ(A$3:L$3*(МЕСЯЦ(СТРОКА(ДВССЫЛ(B6&":"&C6)))=СТОЛБЕЦ(A:L))/ДЕНЬ(ДАТА(ЛЕВБ(A$1;4);СТОЛБЕЦ(B:M);)))
В А1 - 2016 - 91 символ
Код
=СУММ(A$3:L$3*(МЕСЯЦ(СТРОКА(ДВССЫЛ(B6&":"&C6)))=СТОЛБЕЦ(A:L))/ДЕНЬ(ДАТА(A$1;СТОЛБЕЦ(B:M);)))
Если диапазон любой, а счет только за год в А1
В А1 - 2016 год - 118 символов
Код
=СУММ(A$3:L$3*(ТЕКСТ(СТРОКА(ДВССЫЛ(B6&":"&C6));"МГГГ")=СТОЛБЕЦ(A:L)&ЛЕВБ(A$1;4))/ДЕНЬ(ДАТА(ЛЕВБ(A$1;4);СТОЛБЕЦ(B:M);)))
по наводке МСН - 114 символов
Код
=СУММ(A$3:L$3*(ТЕКСТ(СТРОКА(ДВССЫЛ(B6&":"&C6));"ММММГГГ \го\д")=A$2:L$2&A$1)/ДЕНЬ(ДАТА(ЛЕВБ(A$1;4);СТОЛБЕЦ(B:M);)))
В А1 - 2016 - 100 символов
Код
=СУММ(A$3:L$3*(ТЕКСТ(СТРОКА(ДВССЫЛ(B6&":"&C6));"ММММГГГ")=A$2:L$2&A$1)/ДЕНЬ(ДАТА(A$1;СТОЛБЕЦ(B:M);)))
Немассивный вариант получаем заменой функции СУММ() на СУММПРОИЗВ(), на 6 символов длинее.
Приложил файл с самыми короткими формулами из показанных выше.
Автор - AlexM
Дата добавления - 13.04.2017 в 14:10
Светлый Дата: Суббота, 15.04.2017, 13:02 | Сообщение № 27
Группа: Друзья
Ранг: Старожил
Сообщений: 1851
Репутация: 523 ±
Замечаний: 0% ±

Excel 2013, 2016
У меня такой вариант:
Код
=СУММ(ИНДЕКС(A$3:L$3/ДЕНЬ(ДАТА(A$1;СТОЛБЕЦ(B:M);));;Ч(ИНДЕКС(МЕСЯЦ(B6-1+СТРОКА(СМЕЩ(A$1;;;C6-B6+1)));))))

Программировать проще, чем писать стихи.
 
Ответить
СообщениеУ меня такой вариант:
Код
=СУММ(ИНДЕКС(A$3:L$3/ДЕНЬ(ДАТА(A$1;СТОЛБЕЦ(B:M);));;Ч(ИНДЕКС(МЕСЯЦ(B6-1+СТРОКА(СМЕЩ(A$1;;;C6-B6+1)));))))
Автор - Светлый
Дата добавления - 15.04.2017 в 13:02
AlexM Дата: Суббота, 15.04.2017, 21:00 | Сообщение № 28
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1129 ±
Замечаний: 0% ±

Excel 2003
Еще одна формула массива.
Если диапазон в пределах года из А1 и в А1 число года
Код
=СУММ(ВПР(9;A$3:L$3/ДЕНЬ(ДАТА(A$1;СТОЛБЕЦ(B:M);));МЕСЯЦ(СТРОКА(ДВССЫЛ(B6&":"&C6)))))

83 символа


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеЕще одна формула массива.
Если диапазон в пределах года из А1 и в А1 число года
Код
=СУММ(ВПР(9;A$3:L$3/ДЕНЬ(ДАТА(A$1;СТОЛБЕЦ(B:M);));МЕСЯЦ(СТРОКА(ДВССЫЛ(B6&":"&C6)))))

83 символа
Автор - AlexM
Дата добавления - 15.04.2017 в 21:00
ZORRO2005 Дата: Суббота, 15.04.2017, 23:48 | Сообщение № 29
Группа: Друзья
Ранг: Обитатель
Сообщений: 382
Репутация: 148 ±
Замечаний: 0% ±

Excel2010
AlexM, последняя формула выдает ошибку если в А3 больше 279(9*31)
9^99 наверно хотел написать?
 
Ответить
СообщениеAlexM, последняя формула выдает ошибку если в А3 больше 279(9*31)
9^99 наверно хотел написать?
Автор - ZORRO2005
Дата добавления - 15.04.2017 в 23:48
AlexM Дата: Суббота, 15.04.2017, 23:56 | Сообщение № 30
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1129 ±
Замечаний: 0% ±

Excel 2003
Сергей, я специально так написал, забыл только пояснить, что искомое значение зависит от значения января. 99 или 999 думаю достаточно.


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеСергей, я специально так написал, забыл только пояснить, что искомое значение зависит от значения января. 99 или 999 думаю достаточно.
Автор - AlexM
Дата добавления - 15.04.2017 в 23:56
Мир MS Excel » Работа и общение » Мозговой штурм » Сумма за период (Сумма за период, зная суммы по месяцам)
  • Страница 2 из 2
  • «
  • 1
  • 2
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!