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

Вход

Регистрация

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

 

= Мир MS Excel/Суммирование значений согласно периода дат с листа месяца - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Суммирование значений согласно периода дат с листа месяца (Формулы/Formulas)
Суммирование значений согласно периода дат с листа месяца
lebensvoll Дата: Пятница, 06.07.2018, 13:00 | Сообщение № 1
Группа: Проверенные
Ранг: Старожил
Сообщений: 1002
Репутация: 30 ±
Замечаний: 0% ±

Excel 2010
Добрый день, многоуважаемые форумчане!!!
Прошу Вас вновь откликнуться на помощь. В ходе работы столкнулись с проблемой.
Имеется файл с месячными отчетами по ГСМ. Месяц разбивается на два полумесяца (ПРИМЕРНО период дат с 1.01.2018 по 15.01.2018), после чего производится расчет коэффициента на этот период.
Все шло нормально. Пока не столкнулись с проблемой а если в январе период заканчивается 28.01.2018 а в феврале месяце он должен начаться будет начаться с 28.02.2018 по 15.02.2018
В файле на Листе ЯНВАРЬ; ФЕВРАЛЬ столбец V1 и W1 выделено желтым цветом
1. В начале мы считаем количество выданного топлива за период
Код
=СУММЕСЛИМН(Таблица17[Выдано топливо, литр];Таблица17[Дата];">="&$T$1;Таблица17[Дата];"<="&$U$1)

2. Потом мы начинаем рассчитывать поправочный коэффициент за этот период с суммой выданного топлива
Код
=ЕСЛИ(V1=0;0;(СУММЕСЛИМН($N$6:$N$37;$A$6:$A$37;">="&$T$1;$A$6:$A$37;"<="&$U$1))/(V1-500+G6))

В январе месяце мы (к примеру) закрываем период 28.01.2018 как можно было бы просчитать в ФЕВРАЛЕ (Лист февраль пример) месяце (с учетом указанного периода 28.01.2018 по 15.02.2018)
1. Что выдано топлива не 490 а 942!?
2. А также при расчете коэффициента учитывалось (сумма расхода топлива с 28.01.2018 по 31.01.2018)!?

Я лишь сейчас вижу так вот, но может есть более конструктивнее подход
Код
=ЕСЛИ(МЕСЯЦ(T1)=1;СУММЕСЛИМН(Таблица17[Выдано топливо, литр];Таблица17[Дата];">="&$T$1;Таблица17[Дата];"<="&$U$1)+СУММЕСЛИМН(Таблица173[Выдано топливо, литр];Таблица173[Дата];">="&$T$1;Таблица173[Дата];"<="&$U$1);СУММЕСЛИМН(Таблица173[Выдано топливо, литр];Таблица173[Дата];">="&$T$1;Таблица173[Дата];"<="&$U$1))


Заранее СПАСИБО ВАМ ОГРОМНОЕ ЗА ПОМОЩЬ и ваши пояснения...
К сообщению приложен файл: 7562994.xlsx (72.3 Kb)

Кто бы ты ни был, мир в твоих руках

Сообщение отредактировал lebensvoll - Пятница, 06.07.2018, 13:11
 
Ответить
СообщениеДобрый день, многоуважаемые форумчане!!!
Прошу Вас вновь откликнуться на помощь. В ходе работы столкнулись с проблемой.
Имеется файл с месячными отчетами по ГСМ. Месяц разбивается на два полумесяца (ПРИМЕРНО период дат с 1.01.2018 по 15.01.2018), после чего производится расчет коэффициента на этот период.
Все шло нормально. Пока не столкнулись с проблемой а если в январе период заканчивается 28.01.2018 а в феврале месяце он должен начаться будет начаться с 28.02.2018 по 15.02.2018
В файле на Листе ЯНВАРЬ; ФЕВРАЛЬ столбец V1 и W1 выделено желтым цветом
1. В начале мы считаем количество выданного топлива за период
Код
=СУММЕСЛИМН(Таблица17[Выдано топливо, литр];Таблица17[Дата];">="&$T$1;Таблица17[Дата];"<="&$U$1)

2. Потом мы начинаем рассчитывать поправочный коэффициент за этот период с суммой выданного топлива
Код
=ЕСЛИ(V1=0;0;(СУММЕСЛИМН($N$6:$N$37;$A$6:$A$37;">="&$T$1;$A$6:$A$37;"<="&$U$1))/(V1-500+G6))

В январе месяце мы (к примеру) закрываем период 28.01.2018 как можно было бы просчитать в ФЕВРАЛЕ (Лист февраль пример) месяце (с учетом указанного периода 28.01.2018 по 15.02.2018)
1. Что выдано топлива не 490 а 942!?
2. А также при расчете коэффициента учитывалось (сумма расхода топлива с 28.01.2018 по 31.01.2018)!?

Я лишь сейчас вижу так вот, но может есть более конструктивнее подход
Код
=ЕСЛИ(МЕСЯЦ(T1)=1;СУММЕСЛИМН(Таблица17[Выдано топливо, литр];Таблица17[Дата];">="&$T$1;Таблица17[Дата];"<="&$U$1)+СУММЕСЛИМН(Таблица173[Выдано топливо, литр];Таблица173[Дата];">="&$T$1;Таблица173[Дата];"<="&$U$1);СУММЕСЛИМН(Таблица173[Выдано топливо, литр];Таблица173[Дата];">="&$T$1;Таблица173[Дата];"<="&$U$1))


Заранее СПАСИБО ВАМ ОГРОМНОЕ ЗА ПОМОЩЬ и ваши пояснения...
Автор - lebensvoll
Дата добавления - 06.07.2018 в 13:00
_Boroda_ Дата: Пятница, 06.07.2018, 13:33 | Сообщение № 2
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
А в марте Вы будете переписывать эту формулу заново? У Вас же название предыдущей таблицы поменяется
Предлагаю так

ячейка Т1
Код
=ДВССЫЛ(ТЕКСТ(A6-1;"ММММ")&"!U2")+1

ячейка V1
Код
=СУММЕСЛИМН(Таблица1734[Выдано топливо, литр];Таблица1734[Дата];">="&$T$1;Таблица1734[Дата];"<="&$U$1)+ЕСЛИ(МЕСЯЦ(U1)<>МЕСЯЦ(T1);ДВССЫЛ(ТЕКСТ(A6-1;"ММММ")&"!F38")-СУММ(ДВССЫЛ(ТЕКСТ(A6-1;"ММММ")&"!V1:V2")))

ячейка W1
Код
=ЕСЛИ(V1=0;0;(СУММЕСЛИМН(N6:N37;A6:A37;">="&T1;A6:A37;"<="&U1)+СУММЕСЛИМН(ДВССЫЛ(ТЕКСТ(A6-1;"ММММ")&"!N6:N37");ДВССЫЛ(ТЕКСТ(A6-1;"ММММ")&"!A6:A37");">="&T1))/(V1-500+ЕСЛИ(МЕСЯЦ(U1)=МЕСЯЦ(T1);G6;ИНДЕКС(ДВССЫЛ(ТЕКСТ(A6-1;"ММММ")&"!G6:G37");ДЕНЬ(T1)))))

Потом просто копируете лист, называете его следующим месяцем и вводите там новые даты в столбце А
К сообщению приложен файл: 7562994_1.xlsb (64.6 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеА в марте Вы будете переписывать эту формулу заново? У Вас же название предыдущей таблицы поменяется
Предлагаю так

ячейка Т1
Код
=ДВССЫЛ(ТЕКСТ(A6-1;"ММММ")&"!U2")+1

ячейка V1
Код
=СУММЕСЛИМН(Таблица1734[Выдано топливо, литр];Таблица1734[Дата];">="&$T$1;Таблица1734[Дата];"<="&$U$1)+ЕСЛИ(МЕСЯЦ(U1)<>МЕСЯЦ(T1);ДВССЫЛ(ТЕКСТ(A6-1;"ММММ")&"!F38")-СУММ(ДВССЫЛ(ТЕКСТ(A6-1;"ММММ")&"!V1:V2")))

ячейка W1
Код
=ЕСЛИ(V1=0;0;(СУММЕСЛИМН(N6:N37;A6:A37;">="&T1;A6:A37;"<="&U1)+СУММЕСЛИМН(ДВССЫЛ(ТЕКСТ(A6-1;"ММММ")&"!N6:N37");ДВССЫЛ(ТЕКСТ(A6-1;"ММММ")&"!A6:A37");">="&T1))/(V1-500+ЕСЛИ(МЕСЯЦ(U1)=МЕСЯЦ(T1);G6;ИНДЕКС(ДВССЫЛ(ТЕКСТ(A6-1;"ММММ")&"!G6:G37");ДЕНЬ(T1)))))

Потом просто копируете лист, называете его следующим месяцем и вводите там новые даты в столбце А
Автор - _Boroda_
Дата добавления - 06.07.2018 в 13:33
lebensvoll Дата: Пятница, 06.07.2018, 14:40 | Сообщение № 3
Группа: Проверенные
Ранг: Старожил
Сообщений: 1002
Репутация: 30 ±
Замечаний: 0% ±

Excel 2010
_Boroda_, hands hands
Александр это же профессиональный подход к решению.
Просто СУПЕР!!!
Спасибо ВАМ ОГРОМНЕЙШЕЕ!!!
Единственный вопрос
Почему если я хочу третью формулу переменить сразу на листе Январь она ругается и #ССЫЛКА
Это как то связано с тем что я не применил первую формулу в ячейке Т1
Просто период дат (окончание и начала) оператор вносит в ручную.
И вот если я начинаю использовать в ячейке Т1 формулу то ТРЕТЬЯ формула срабатывает сразу на ура

Получается к листу Январь ее ни как не пременить. Остается лишь создать копию листа ЯНВАРЬ, потом его переименовать в Февраль и лишь потом начать использовать третью форму для расчета. И потом уже аналогично создавать листы других месяцев по образу февраля

Кто бы ты ни был, мир в твоих руках

Сообщение отредактировал lebensvoll - Пятница, 06.07.2018, 14:43
 
Ответить
Сообщение_Boroda_, hands hands
Александр это же профессиональный подход к решению.
Просто СУПЕР!!!
Спасибо ВАМ ОГРОМНЕЙШЕЕ!!!
Единственный вопрос
Почему если я хочу третью формулу переменить сразу на листе Январь она ругается и #ССЫЛКА
Это как то связано с тем что я не применил первую формулу в ячейке Т1
Просто период дат (окончание и начала) оператор вносит в ручную.
И вот если я начинаю использовать в ячейке Т1 формулу то ТРЕТЬЯ формула срабатывает сразу на ура

Получается к листу Январь ее ни как не пременить. Остается лишь создать копию листа ЯНВАРЬ, потом его переименовать в Февраль и лишь потом начать использовать третью форму для расчета. И потом уже аналогично создавать листы других месяцев по образу февраля
Автор - lebensvoll
Дата добавления - 06.07.2018 в 14:40
_Boroda_ Дата: Пятница, 06.07.2018, 14:59 | Сообщение № 4
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
У Вас же нет листа "Декабрь", правильно? Вот январю и некуда ссылаться.
Поэтому новую книгу нужно создавать сразу с двумя листами - Январь и Февраль. В Январе старые формулы, в Феврале - новые. Для следующих месяцев размножаем лист Февраль. А в Январе нельзя писать эти формулы не только потому, что листа Декабрь нет, но и потому, что когда лист Декабрь появится, то Январь НЕ ДОЛЖЕН на него ссылаться - это же декабрь текущего года, а не прошедшего
Ну или в формуле можно условие прописать типа если месяц А6 = январь, то не считаем ДВССЫЛ. Но это уже лишнее, проще 2 листа делать

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеУ Вас же нет листа "Декабрь", правильно? Вот январю и некуда ссылаться.
Поэтому новую книгу нужно создавать сразу с двумя листами - Январь и Февраль. В Январе старые формулы, в Феврале - новые. Для следующих месяцев размножаем лист Февраль. А в Январе нельзя писать эти формулы не только потому, что листа Декабрь нет, но и потому, что когда лист Декабрь появится, то Январь НЕ ДОЛЖЕН на него ссылаться - это же декабрь текущего года, а не прошедшего
Ну или в формуле можно условие прописать типа если месяц А6 = январь, то не считаем ДВССЫЛ. Но это уже лишнее, проще 2 листа делать
Автор - _Boroda_
Дата добавления - 06.07.2018 в 14:59
lebensvoll Дата: Пятница, 06.07.2018, 15:06 | Сообщение № 5
Группа: Проверенные
Ранг: Старожил
Сообщений: 1002
Репутация: 30 ±
Замечаний: 0% ±

Excel 2010
_Boroda_,
Еще раз огромнейшее спасибо за пояснения и помощь...

Кто бы ты ни был, мир в твоих руках
 
Ответить
Сообщение_Boroda_,
Еще раз огромнейшее спасибо за пояснения и помощь...
Автор - lebensvoll
Дата добавления - 06.07.2018 в 15:06
lebensvoll Дата: Пятница, 06.07.2018, 18:19 | Сообщение № 6
Группа: Проверенные
Ранг: Старожил
Сообщений: 1002
Репутация: 30 ±
Замечаний: 0% ±

Excel 2010
_Boroda_,
Александр, прошу прощение!!! А также прошу помощи в решение Форумчан...
А как отыграть в обратную сторону
Вот смотрите к примеру 2-я половина месяца указана дата (с 19.01.2018 по 29.01.2018) в столбце "Фактический расход топлива, литр" О7:О37 производится расчет (с учетом найденного поправочного коэффициента ячейка W3 = 1,444) а должно быть иначе он должен делить учитывать коэффициент найденный в феврале (в период с 30.01.2018 по 15.02.2018) :'( :'(
Если выше был рассмотрен пример когда мы рассчитывали коэффициент с учетом периодов дат и прыгали с февраля в январь и суммировали
То в этом примере практически тоже самое но нужно учесть в январе на 30.01.2018 который рассчитывается в феврале
В примере ячейку окрасил в желтый цвет а зеленным как должно
А с другой стороны не могу понять почему же он цепляет значение 1,444 раз дата указана 29.01.2018
Код
=ОКРУГЛ(Январь[[#Эта строка];[Расход топлива по норме, литр]]/ВПР([Дата];S$2:W$3;5);0)+T36
К сообщению приложен файл: -056-.xlsm (63.0 Kb)

Кто бы ты ни был, мир в твоих руках

Сообщение отредактировал lebensvoll - Пятница, 06.07.2018, 18:21
 
Ответить
Сообщение_Boroda_,
Александр, прошу прощение!!! А также прошу помощи в решение Форумчан...
А как отыграть в обратную сторону
Вот смотрите к примеру 2-я половина месяца указана дата (с 19.01.2018 по 29.01.2018) в столбце "Фактический расход топлива, литр" О7:О37 производится расчет (с учетом найденного поправочного коэффициента ячейка W3 = 1,444) а должно быть иначе он должен делить учитывать коэффициент найденный в феврале (в период с 30.01.2018 по 15.02.2018) :'( :'(
Если выше был рассмотрен пример когда мы рассчитывали коэффициент с учетом периодов дат и прыгали с февраля в январь и суммировали
То в этом примере практически тоже самое но нужно учесть в январе на 30.01.2018 который рассчитывается в феврале
В примере ячейку окрасил в желтый цвет а зеленным как должно
А с другой стороны не могу понять почему же он цепляет значение 1,444 раз дата указана 29.01.2018
Код
=ОКРУГЛ(Январь[[#Эта строка];[Расход топлива по норме, литр]]/ВПР([Дата];S$2:W$3;5);0)+T36
Автор - lebensvoll
Дата добавления - 06.07.2018 в 18:19
_Boroda_ Дата: Пятница, 06.07.2018, 19:13 | Сообщение № 7
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Потому, что ВПР без четвертого аргумента ищет значение, которое не больше искомого. Все нормально, так и должно быть
Предлагаю с формулой не морочить себе голову, а просто перенести 2 ячейки с будущего листа (покрасил желтым)
И зачем Вы стерли формулу с первой датой в феврале? Тоже покрасил желтым

* Только обратите внимание, что перенос там тоже формулами. И начинает он действовать при создании следующего по порядку листа
** Да, 4-й аргумент - ЛОЖЬ / ИСТИНА = 0 / ЛюбоеНенулевоеЧисло = ВПР(1;2;3;) / ВПР(1;2;3)
К сообщению приложен файл: -056-1.xlsm (61.5 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеПотому, что ВПР без четвертого аргумента ищет значение, которое не больше искомого. Все нормально, так и должно быть
Предлагаю с формулой не морочить себе голову, а просто перенести 2 ячейки с будущего листа (покрасил желтым)
И зачем Вы стерли формулу с первой датой в феврале? Тоже покрасил желтым

* Только обратите внимание, что перенос там тоже формулами. И начинает он действовать при создании следующего по порядку листа
** Да, 4-й аргумент - ЛОЖЬ / ИСТИНА = 0 / ЛюбоеНенулевоеЧисло = ВПР(1;2;3;) / ВПР(1;2;3)
Автор - _Boroda_
Дата добавления - 06.07.2018 в 19:13
lebensvoll Дата: Пятница, 06.07.2018, 21:33 | Сообщение № 8
Группа: Проверенные
Ранг: Старожил
Сообщений: 1002
Репутация: 30 ±
Замечаний: 0% ±

Excel 2010
_Boroda_, Алесандр. в домашней обстановке я понял ваш посыл.
Цитата
Потому, что ВПР без четвертого аргумента ищет значение, которое не больше искомого

А 4-м аргументом является ИСТИНА или ЛОЖЬ... ВЕРНО!?
В понедельник обязательно сяду за обсуждение с бухгалтерией за найденное решение.
Ранее они сами ВСЕ ЭТО ДЕЛАЛИ просто методом правок в формулах (человеческий фактор вмешательства). Попросили меня исправить потому как навел до этого отчеты по отчетам на бетонном заводе и лабораторном анализе (не без помощи ФОРУМА)
Взялся...
Но как только начал углубляться (а потом когда решил задачи "автоматизации" начались дальнейшие а вот тут вот так а вот тут вот этак)
Но я не так силен в запросах ((((
МНЕ ОЧЕНЬ НРАВИТСЯ ВНИКАТЬ РЕШАТЬ И ОБЛЕГЧАТЬ РАБОТУ (с эксель, по мере своей возможности), но иной раз....
БЕЗ ПОДДЕРЖКИ ФОРУМА я ни ЧТО ((((
СПАСИБО ВАМ ОГРОМНЕЙШЕЕ...
Я вам в понедельник обязательно отпишусь

Кто бы ты ни был, мир в твоих руках

Сообщение отредактировал lebensvoll - Пятница, 06.07.2018, 21:41
 
Ответить
Сообщение_Boroda_, Алесандр. в домашней обстановке я понял ваш посыл.
Цитата
Потому, что ВПР без четвертого аргумента ищет значение, которое не больше искомого

А 4-м аргументом является ИСТИНА или ЛОЖЬ... ВЕРНО!?
В понедельник обязательно сяду за обсуждение с бухгалтерией за найденное решение.
Ранее они сами ВСЕ ЭТО ДЕЛАЛИ просто методом правок в формулах (человеческий фактор вмешательства). Попросили меня исправить потому как навел до этого отчеты по отчетам на бетонном заводе и лабораторном анализе (не без помощи ФОРУМА)
Взялся...
Но как только начал углубляться (а потом когда решил задачи "автоматизации" начались дальнейшие а вот тут вот так а вот тут вот этак)
Но я не так силен в запросах ((((
МНЕ ОЧЕНЬ НРАВИТСЯ ВНИКАТЬ РЕШАТЬ И ОБЛЕГЧАТЬ РАБОТУ (с эксель, по мере своей возможности), но иной раз....
БЕЗ ПОДДЕРЖКИ ФОРУМА я ни ЧТО ((((
СПАСИБО ВАМ ОГРОМНЕЙШЕЕ...
Я вам в понедельник обязательно отпишусь
Автор - lebensvoll
Дата добавления - 06.07.2018 в 21:33
lebensvoll Дата: Понедельник, 16.07.2018, 13:46 | Сообщение № 9
Группа: Проверенные
Ранг: Старожил
Сообщений: 1002
Репутация: 30 ±
Замечаний: 0% ±

Excel 2010
_Boroda_, прошу простить ВЕЛИКОДУШНО!!!
Столкнулся в ходе заполнения данных с проблемой...
А если же в данном примере будет рассматриваться так вот:
на вкладке листа "Январь" периоды указаны и считает все верно.
на вкладке листа "Февраль" указаны периоды с 30.01.2018 по 15.02.2018 и в столбце V2 значение должно быть равное 620 а сейчас 1372 :( (выделил красным)
а теперь со столкнувшейся делемой на вкладке листа "Февраль" указаны периоды с 16.02.2018 по 3.03.2018 и в столбце V3 должно быть значение 450 а сейчас 1052 :'( (также выделил красным цветом).
Как сделать так чтоб формула учитывала периоды как назад так и вперед
сейчас стоит формула
Код
=СУММЕСЛИМН(Февраль[Выдано топливо, литр];Февраль[Дата];">="&$S$2;Февраль[Дата];"<="&$T$2)+ЕСЛИ(МЕСЯЦ(T2)<>МЕСЯЦ(S2);ДВССЫЛ(ТЕКСТ(A7-1;"ММММ")&"!F38")-СУММ(ДВССЫЛ(ТЕКСТ(A7-1;"ММММ")&"!V1:V2")))
К сообщению приложен файл: 8441858.xlsm (75.9 Kb)

Кто бы ты ни был, мир в твоих руках
 
Ответить
Сообщение_Boroda_, прошу простить ВЕЛИКОДУШНО!!!
Столкнулся в ходе заполнения данных с проблемой...
А если же в данном примере будет рассматриваться так вот:
на вкладке листа "Январь" периоды указаны и считает все верно.
на вкладке листа "Февраль" указаны периоды с 30.01.2018 по 15.02.2018 и в столбце V2 значение должно быть равное 620 а сейчас 1372 :( (выделил красным)
а теперь со столкнувшейся делемой на вкладке листа "Февраль" указаны периоды с 16.02.2018 по 3.03.2018 и в столбце V3 должно быть значение 450 а сейчас 1052 :'( (также выделил красным цветом).
Как сделать так чтоб формула учитывала периоды как назад так и вперед
сейчас стоит формула
Код
=СУММЕСЛИМН(Февраль[Выдано топливо, литр];Февраль[Дата];">="&$S$2;Февраль[Дата];"<="&$T$2)+ЕСЛИ(МЕСЯЦ(T2)<>МЕСЯЦ(S2);ДВССЫЛ(ТЕКСТ(A7-1;"ММММ")&"!F38")-СУММ(ДВССЫЛ(ТЕКСТ(A7-1;"ММММ")&"!V1:V2")))
Автор - lebensvoll
Дата добавления - 16.07.2018 в 13:46
_Boroda_ Дата: Понедельник, 16.07.2018, 14:30 | Сообщение № 10
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
1. Почему 620? А январь посчитать? И если Вы сдвинули ячейки V1:V2 вниз на одну строку, то формулу ДВССЫЛ в V2 тоже переписать нужно
Код
=СУММЕСЛИМН(Февраль[Выдано топливо, литр];Февраль[Дата];">="&$S$2;Февраль[Дата];"<="&$T$2)+ЕСЛИ(МЕСЯЦ(T2)<>МЕСЯЦ(S2);ДВССЫЛ(ТЕКСТ(A7-1;"ММММ")&"!F38")-СУММ(ДВССЫЛ(ТЕКСТ(A7-1;"ММММ")&"!V3:V2")))

2.
Код
=СУММЕСЛИМН(Февраль[Выдано топливо, литр];Февраль[Дата];">="&$S$3;Февраль[Дата];"<="&$T$3)+ЕСЛИ(МЕСЯЦ(T3)<>МЕСЯЦ(S3);СУММЕСЛИМН(ДВССЫЛ(ТЕКСТ(T3;"ММММ")&"[Выдано топливо, литр]");ДВССЫЛ(ТЕКСТ(T3;"ММММ")&"[Дата]");">="&$S$3;ДВССЫЛ(ТЕКСТ(T3;"ММММ")&"[Дата]");"<="&$T$3))

В файле сделал только для февраля
К сообщению приложен файл: 8441858_1.xlsm (73.6 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение1. Почему 620? А январь посчитать? И если Вы сдвинули ячейки V1:V2 вниз на одну строку, то формулу ДВССЫЛ в V2 тоже переписать нужно
Код
=СУММЕСЛИМН(Февраль[Выдано топливо, литр];Февраль[Дата];">="&$S$2;Февраль[Дата];"<="&$T$2)+ЕСЛИ(МЕСЯЦ(T2)<>МЕСЯЦ(S2);ДВССЫЛ(ТЕКСТ(A7-1;"ММММ")&"!F38")-СУММ(ДВССЫЛ(ТЕКСТ(A7-1;"ММММ")&"!V3:V2")))

2.
Код
=СУММЕСЛИМН(Февраль[Выдано топливо, литр];Февраль[Дата];">="&$S$3;Февраль[Дата];"<="&$T$3)+ЕСЛИ(МЕСЯЦ(T3)<>МЕСЯЦ(S3);СУММЕСЛИМН(ДВССЫЛ(ТЕКСТ(T3;"ММММ")&"[Выдано топливо, литр]");ДВССЫЛ(ТЕКСТ(T3;"ММММ")&"[Дата]");">="&$S$3;ДВССЫЛ(ТЕКСТ(T3;"ММММ")&"[Дата]");"<="&$T$3))

В файле сделал только для февраля
Автор - _Boroda_
Дата добавления - 16.07.2018 в 14:30
lebensvoll Дата: Понедельник, 30.07.2018, 13:24 | Сообщение № 11
Группа: Проверенные
Ранг: Старожил
Сообщений: 1002
Репутация: 30 ±
Замечаний: 0% ±

Excel 2010
Добрый день, многоуважаемые форумчане!!! Я вновь прошу помощи. :'(
Как при "Суммирование значений согласно периода дат" (а даты теперь чередуются через одну пустую ячейку) просуммировать значение выданного топлива!?
Во Вложение файл, лист Январь ячека V2 (выделил желтым цветом).
Сейчас формула:
Код
=СУММЕСЛИМН(Январь[Выдано топливо, литр];Январь[Дата];">="&$S$2;Январь[Дата];"<="&$T$2)+ЕСЛИ(МЕСЯЦ(T2)<>МЕСЯЦ(S2);ДВССЫЛ(ТЕКСТ(A7-1;"ММММ")&"!F38")-СУММ(ДВССЫЛ(ТЕКСТ(A7-1;"ММММ")&"!V1:V2")))+СУММЕСЛИМН($U$7:$U$68;Январь[Дата];">="&$S$2;Январь[Дата];"<="&$T$2)+ЕСЛИ(МЕСЯЦ(T2)<>МЕСЯЦ(S2);ДВССЫЛ(ТЕКСТ(A7-1;"ММММ")&"!F38")-СУММ(ДВССЫЛ(ТЕКСТ(A7-1;"ММММ")&"!V1:V2")))

Если бы даты шли по порядку (в каждой ячейке) то данная формула работает на ура!!!
Спасибо за помощь Boroda,
Но сейчас же данная формула стала капризничать (((( и выводит ответ #ЗНАЧ! %) :'( а как учесть при СУММЕСЛИ пустые значения даты ума не приложу.
Искренно, спасибо за отзывчивость и помощь!!!
К сообщению приложен файл: _350_.xlsm (76.5 Kb)

Кто бы ты ни был, мир в твоих руках

Сообщение отредактировал lebensvoll - Понедельник, 30.07.2018, 13:25
 
Ответить
СообщениеДобрый день, многоуважаемые форумчане!!! Я вновь прошу помощи. :'(
Как при "Суммирование значений согласно периода дат" (а даты теперь чередуются через одну пустую ячейку) просуммировать значение выданного топлива!?
Во Вложение файл, лист Январь ячека V2 (выделил желтым цветом).
Сейчас формула:
Код
=СУММЕСЛИМН(Январь[Выдано топливо, литр];Январь[Дата];">="&$S$2;Январь[Дата];"<="&$T$2)+ЕСЛИ(МЕСЯЦ(T2)<>МЕСЯЦ(S2);ДВССЫЛ(ТЕКСТ(A7-1;"ММММ")&"!F38")-СУММ(ДВССЫЛ(ТЕКСТ(A7-1;"ММММ")&"!V1:V2")))+СУММЕСЛИМН($U$7:$U$68;Январь[Дата];">="&$S$2;Январь[Дата];"<="&$T$2)+ЕСЛИ(МЕСЯЦ(T2)<>МЕСЯЦ(S2);ДВССЫЛ(ТЕКСТ(A7-1;"ММММ")&"!F38")-СУММ(ДВССЫЛ(ТЕКСТ(A7-1;"ММММ")&"!V1:V2")))

Если бы даты шли по порядку (в каждой ячейке) то данная формула работает на ура!!!
Спасибо за помощь Boroda,
Но сейчас же данная формула стала капризничать (((( и выводит ответ #ЗНАЧ! %) :'( а как учесть при СУММЕСЛИ пустые значения даты ума не приложу.
Искренно, спасибо за отзывчивость и помощь!!!
Автор - lebensvoll
Дата добавления - 30.07.2018 в 13:24
lebensvoll Дата: Понедельник, 30.07.2018, 13:43 | Сообщение № 12
Группа: Проверенные
Ранг: Старожил
Сообщений: 1002
Репутация: 30 ±
Замечаний: 0% ±

Excel 2010
Прошу прощение!!!
Разобрался
Если долго, долго мучатся все равно получится
Спасибо огромное

Кто бы ты ни был, мир в твоих руках
 
Ответить
СообщениеПрошу прощение!!!
Разобрался
Если долго, долго мучатся все равно получится
Спасибо огромное
Автор - lebensvoll
Дата добавления - 30.07.2018 в 13:43
_Boroda_ Дата: Понедельник, 30.07.2018, 13:57 | Сообщение № 13
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Цитата lebensvoll, 30.07.2018 в 13:43, в сообщении № 12 ()
Разобрался

Нет уж держите
Код
=СУММЕСЛИМН(Январь[Выдано топливо, литр];Январь[Дата];">="&$S$2;Январь[Дата];"<="&$T$2)+ЕСЛИ(МЕСЯЦ(T2)<>МЕСЯЦ(S2);ДВССЫЛ(ТЕКСТ(A7-1;"ММММ")&"!F69")-СУММ(ДВССЫЛ(ТЕКСТ(A7-1;"ММММ")&"!V1:V2")))+СУММЕСЛИМН($U$7:$U$69;Январь[Дата];">="&$S$2;Январь[Дата];"<="&$T$2)+ЕСЛИ(МЕСЯЦ(T2)<>МЕСЯЦ(S2);ДВССЫЛ(ТЕКСТ(A7-1;"ММММ")&"!F69")-СУММ(ДВССЫЛ(ТЕКСТ(A7-1;"ММММ")&"!V1:V2")))
К сообщению приложен файл: _350-1-1.xlsm (73.1 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
Цитата lebensvoll, 30.07.2018 в 13:43, в сообщении № 12 ()
Разобрался

Нет уж держите
Код
=СУММЕСЛИМН(Январь[Выдано топливо, литр];Январь[Дата];">="&$S$2;Январь[Дата];"<="&$T$2)+ЕСЛИ(МЕСЯЦ(T2)<>МЕСЯЦ(S2);ДВССЫЛ(ТЕКСТ(A7-1;"ММММ")&"!F69")-СУММ(ДВССЫЛ(ТЕКСТ(A7-1;"ММММ")&"!V1:V2")))+СУММЕСЛИМН($U$7:$U$69;Январь[Дата];">="&$S$2;Январь[Дата];"<="&$T$2)+ЕСЛИ(МЕСЯЦ(T2)<>МЕСЯЦ(S2);ДВССЫЛ(ТЕКСТ(A7-1;"ММММ")&"!F69")-СУММ(ДВССЫЛ(ТЕКСТ(A7-1;"ММММ")&"!V1:V2")))
Автор - _Boroda_
Дата добавления - 30.07.2018 в 13:57
lebensvoll Дата: Вторник, 31.07.2018, 08:32 | Сообщение № 14
Группа: Проверенные
Ранг: Старожил
Сообщений: 1002
Репутация: 30 ±
Замечаний: 0% ±

Excel 2010
_Boroda_, спасибо огромное!!!
Но я не могу понять почему иной раз они считают (((
Тот же самый пример но посчитать нужно СУММУ "Расход топлива по норме, литр" в ячейке W2 %) применяю туже самую формулу (кусок):
Код
=ЕСЛИ(V2=0;0;(СУММЕСЛИМН($N$7:$N$68;$A$7:$A$68;">="&$S$2;$A$7:$A$68;"<="&$T$2))/(V2-U2+G7))

Должно быть СУММА = 2105,4 а ответ НУЛЬ (даже если считать еще и этот кусок формулы
Код
/(V2-U2+G7))

Почему так!?
К сообщению приложен файл: 4415349.xlsm (74.9 Kb)

Кто бы ты ни был, мир в твоих руках

Сообщение отредактировал lebensvoll - Вторник, 31.07.2018, 08:55
 
Ответить
Сообщение_Boroda_, спасибо огромное!!!
Но я не могу понять почему иной раз они считают (((
Тот же самый пример но посчитать нужно СУММУ "Расход топлива по норме, литр" в ячейке W2 %) применяю туже самую формулу (кусок):
Код
=ЕСЛИ(V2=0;0;(СУММЕСЛИМН($N$7:$N$68;$A$7:$A$68;">="&$S$2;$A$7:$A$68;"<="&$T$2))/(V2-U2+G7))

Должно быть СУММА = 2105,4 а ответ НУЛЬ (даже если считать еще и этот кусок формулы
Код
/(V2-U2+G7))

Почему так!?
Автор - lebensvoll
Дата добавления - 31.07.2018 в 08:32
_Boroda_ Дата: Вторник, 31.07.2018, 09:09 | Сообщение № 15
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Потому, что числа в столбце N находятся на 1 строку ниже дат в столбце А. Нужно сместить диапазон на одну строку относительно другого - N7:N68 и A8:A69
Код
=СУММЕСЛИМН($N$8:$N$68;$A$7:$A$67;">="&$S$2;$A$7:$A$67;"<="&$T$2)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеПотому, что числа в столбце N находятся на 1 строку ниже дат в столбце А. Нужно сместить диапазон на одну строку относительно другого - N7:N68 и A8:A69
Код
=СУММЕСЛИМН($N$8:$N$68;$A$7:$A$67;">="&$S$2;$A$7:$A$67;"<="&$T$2)
Автор - _Boroda_
Дата добавления - 31.07.2018 в 09:09
lebensvoll Дата: Вторник, 31.07.2018, 09:25 | Сообщение № 16
Группа: Проверенные
Ранг: Старожил
Сообщений: 1002
Репутация: 30 ±
Замечаний: 0% ±

Excel 2010
_Boroda_, да работает, СПАСИБО!!!
Но вот вопрос
Просто почему даты сделаны именно через одну пустую ячейку. Связано с тем что в одной дате (якобы, две смены). И вот если в ячейке N7 будет какое либо значение, тогда оно не войдет в расчет!?
Верно!?
Как этого избежать то!?
Ведь при суммирование выданного топлива
Цитата
Дата: Понедельник, 30.07.2018, 13:57 | Сообщение № 13
мы ведь не указывали диапазон
Код
$N$8:$N$68

а указали
Код
$U$7:$U$69

И все сработала (просуммировало) ни как не пойму честно

Я поступлю иначе (((( не хочется конечно. Но не буду я формулу применять для столбца ДАТ ((((( чтоб они не прописывали через пустую ячейку. А сам в ручную забью даты 1.01.2018; 1.01.2018; 2.01.2018; 2.01.2018 и т.д
Тем самым избегу обращений своих нелепых на форум....
Досадно конечно (((( но просто в других столбцах также приходится сталкиваться с подобными вопросами при расчете значений ((((

Кто бы ты ни был, мир в твоих руках

Сообщение отредактировал lebensvoll - Вторник, 31.07.2018, 09:41
 
Ответить
Сообщение_Boroda_, да работает, СПАСИБО!!!
Но вот вопрос
Просто почему даты сделаны именно через одну пустую ячейку. Связано с тем что в одной дате (якобы, две смены). И вот если в ячейке N7 будет какое либо значение, тогда оно не войдет в расчет!?
Верно!?
Как этого избежать то!?
Ведь при суммирование выданного топлива
Цитата
Дата: Понедельник, 30.07.2018, 13:57 | Сообщение № 13
мы ведь не указывали диапазон
Код
$N$8:$N$68

а указали
Код
$U$7:$U$69

И все сработала (просуммировало) ни как не пойму честно

Я поступлю иначе (((( не хочется конечно. Но не буду я формулу применять для столбца ДАТ ((((( чтоб они не прописывали через пустую ячейку. А сам в ручную забью даты 1.01.2018; 1.01.2018; 2.01.2018; 2.01.2018 и т.д
Тем самым избегу обращений своих нелепых на форум....
Досадно конечно (((( но просто в других столбцах также приходится сталкиваться с подобными вопросами при расчете значений ((((
Автор - lebensvoll
Дата добавления - 31.07.2018 в 09:25
_Boroda_ Дата: Вторник, 31.07.2018, 09:32 | Сообщение № 17
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Цитата lebensvoll, 31.07.2018 в 09:25, в сообщении № 16 ()
если в ячейке N7 будет какое либо значение, тогда оно не войдет в расчет!?
Верно!?

Верно.
А теперь снова читаем здесь http://www.excelworld.ru/forum/2-20-1
Цитата
старайтесь сохранить структуру, расположение таблиц, имена листов - аналогично оригиналу

Какой вопрос - такой ответ.
Верно!?

Вот так можно
Код
=СУММПРОИЗВ((N7:N67+N8:N68)*(A7:A67>=S2)*(A7:A67<=T2))

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
Цитата lebensvoll, 31.07.2018 в 09:25, в сообщении № 16 ()
если в ячейке N7 будет какое либо значение, тогда оно не войдет в расчет!?
Верно!?

Верно.
А теперь снова читаем здесь http://www.excelworld.ru/forum/2-20-1
Цитата
старайтесь сохранить структуру, расположение таблиц, имена листов - аналогично оригиналу

Какой вопрос - такой ответ.
Верно!?

Вот так можно
Код
=СУММПРОИЗВ((N7:N67+N8:N68)*(A7:A67>=S2)*(A7:A67<=T2))
Автор - _Boroda_
Дата добавления - 31.07.2018 в 09:32
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Суммирование значений согласно периода дат с листа месяца (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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