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

Вход

Регистрация

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

 

= Мир MS Excel/Расчет суммы по декадам месяца, по месяцам и по кварталам - Страница 2 - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 2 из 2
  • «
  • 1
  • 2
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Расчет суммы по декадам месяца, по месяцам и по кварталам (Формулы/Formulas)
Расчет суммы по декадам месяца, по месяцам и по кварталам
Woltaren Дата: Воскресенье, 17.02.2019, 21:21 | Сообщение № 21
Группа: Пользователи
Ранг: Новичок
Сообщений: 14
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Светлый, хорошо, отправляю файл.
Надо было сразу так сделать, но мне представлялось, что смогу вставить Ваши готовые формулы.
Извините, что лишний раз морочу Вам голову!
К сообщению приложен файл: __.xlsx(34.9 Kb)
 
Ответить
СообщениеСветлый, хорошо, отправляю файл.
Надо было сразу так сделать, но мне представлялось, что смогу вставить Ваши готовые формулы.
Извините, что лишний раз морочу Вам голову!

Автор - Woltaren
Дата добавления - 17.02.2019 в 21:21
Светлый Дата: Воскресенье, 17.02.2019, 22:47 | Сообщение № 22
Группа: Проверенные
Ранг: Старожил
Сообщений: 1059
Репутация: 272 ±
Замечаний: 0% ±

Excel 2010
Формула:
Код
=$AD10/($AJ10-$AI10+1)*ЕСЛИ(ИЛИ($AJ10<ДАТА(2019;(СТОЛБЕЦ(E1)-ОТБР((СТОЛБЕЦ(E1)-6)/13))/4;ВЫБОР(ОСТАТ((СТОЛБЕЦ(E1)-ОТБР((СТОЛБЕЦ(E1)-5)/13));4);1;11;21));$AI10>ДАТА(2019;(СТОЛБЕЦ(E1)+1-ОТБР((СТОЛБЕЦ(E1)-6)/13))/4;ВЫБОР(ОСТАТ((СТОЛБЕЦ(E1)-ОТБР((СТОЛБЕЦ(E1)-5)/13));4);10;20;)));;МИН($AJ10;ДАТА(2019;(СТОЛБЕЦ(E1)+1-ОТБР((СТОЛБЕЦ(E1)-6)/13))/4;ВЫБОР(ОСТАТ((СТОЛБЕЦ(E1)-ОТБР((СТОЛБЕЦ(E1)-5)/13));4);10;20;)))-МАКС($AI10;ДАТА(2019;(СТОЛБЕЦ(E1)-ОТБР((СТОЛБЕЦ(E1)-6)/13))/4;ВЫБОР(ОСТАТ((СТОЛБЕЦ(E1)-ОТБР((СТОЛБЕЦ(E1)-5)/13));4);1;11;21)))+1)
К сообщению приложен файл: Woltaren.xlsx(36.8 Kb)


Программировать проще, чем писать стихи.
 
Ответить
СообщениеФормула:
Код
=$AD10/($AJ10-$AI10+1)*ЕСЛИ(ИЛИ($AJ10<ДАТА(2019;(СТОЛБЕЦ(E1)-ОТБР((СТОЛБЕЦ(E1)-6)/13))/4;ВЫБОР(ОСТАТ((СТОЛБЕЦ(E1)-ОТБР((СТОЛБЕЦ(E1)-5)/13));4);1;11;21));$AI10>ДАТА(2019;(СТОЛБЕЦ(E1)+1-ОТБР((СТОЛБЕЦ(E1)-6)/13))/4;ВЫБОР(ОСТАТ((СТОЛБЕЦ(E1)-ОТБР((СТОЛБЕЦ(E1)-5)/13));4);10;20;)));;МИН($AJ10;ДАТА(2019;(СТОЛБЕЦ(E1)+1-ОТБР((СТОЛБЕЦ(E1)-6)/13))/4;ВЫБОР(ОСТАТ((СТОЛБЕЦ(E1)-ОТБР((СТОЛБЕЦ(E1)-5)/13));4);10;20;)))-МАКС($AI10;ДАТА(2019;(СТОЛБЕЦ(E1)-ОТБР((СТОЛБЕЦ(E1)-6)/13))/4;ВЫБОР(ОСТАТ((СТОЛБЕЦ(E1)-ОТБР((СТОЛБЕЦ(E1)-5)/13));4);1;11;21)))+1)

Автор - Светлый
Дата добавления - 17.02.2019 в 22:47
Woltaren Дата: Понедельник, 18.02.2019, 23:00 | Сообщение № 23
Группа: Пользователи
Ранг: Новичок
Сообщений: 14
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Светлый, спасибо большое за ответ, но не подскажете, как должна выглядеть формула для второго варианта, ведь простой заменой букв тут не ограничиться...
 
Ответить
СообщениеСветлый, спасибо большое за ответ, но не подскажете, как должна выглядеть формула для второго варианта, ведь простой заменой букв тут не ограничиться...

Автор - Woltaren
Дата добавления - 18.02.2019 в 23:00
Woltaren Дата: Понедельник, 18.02.2019, 23:14 | Сообщение № 24
Группа: Пользователи
Ранг: Новичок
Сообщений: 14
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Светлый, я имею ввиду 2 вариант внутри того же файла, как он будет выглядеть?
 
Ответить
СообщениеСветлый, я имею ввиду 2 вариант внутри того же файла, как он будет выглядеть?

Автор - Woltaren
Дата добавления - 18.02.2019 в 23:14
krosav4ig Дата: Вторник, 19.02.2019, 03:44 | Сообщение № 25
Группа: Друзья
Ранг: Старожил
Сообщений: 2052
Репутация: 853 ±
Замечаний: 0% ±

Excel 2007,2010,2013
чую, следующий вопрос будет, как будет выглядеть 2018 год внутри того же файла... %)
должен формироваться свод из основной таблицы
Эт как же вы собираетесь из такой каракатицы свод делать? :facepalm:
нарисовал запрос в PowerQuery для всех годов и вариантов, на его основе строится сводная (на листе Лист1), запрос обновляется обновлением сводной или командой Обновить все (Ctrl+Alt+F5). Из сводной формулами данные тянутся в вашу таблицу
К сообщению приложен файл: 6379907.xlsx(89.7 Kb)


email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460
 
Ответить
Сообщениечую, следующий вопрос будет, как будет выглядеть 2018 год внутри того же файла... %)
должен формироваться свод из основной таблицы
Эт как же вы собираетесь из такой каракатицы свод делать? :facepalm:
нарисовал запрос в PowerQuery для всех годов и вариантов, на его основе строится сводная (на листе Лист1), запрос обновляется обновлением сводной или командой Обновить все (Ctrl+Alt+F5). Из сводной формулами данные тянутся в вашу таблицу

Автор - krosav4ig
Дата добавления - 19.02.2019 в 03:44
Светлый Дата: Вторник, 19.02.2019, 08:53 | Сообщение № 26
Группа: Проверенные
Ранг: Старожил
Сообщений: 1059
Репутация: 272 ±
Замечаний: 0% ±

Excel 2010
Формула та же самая:
Код
=$AE10/($AJ10-$AI10+1)*ЕСЛИ(ИЛИ($AJ10<ДАТА(2019;(СТОЛБЕЦ(E1)-ОТБР((СТОЛБЕЦ(E1)-6)/13))/4;ВЫБОР(ОСТАТ((СТОЛБЕЦ(E1)-ОТБР((СТОЛБЕЦ(E1)-5)/13));4);1;11;21));$AI10>ДАТА(2019;(СТОЛБЕЦ(E1)+1-ОТБР((СТОЛБЕЦ(E1)-6)/13))/4;ВЫБОР(ОСТАТ((СТОЛБЕЦ(E1)-ОТБР((СТОЛБЕЦ(E1)-5)/13));4);10;20;)));;МИН($AJ10;ДАТА(2019;(СТОЛБЕЦ(E1)+1-ОТБР((СТОЛБЕЦ(E1)-6)/13))/4;ВЫБОР(ОСТАТ((СТОЛБЕЦ(E1)-ОТБР((СТОЛБЕЦ(E1)-5)/13));4);10;20;)))-МАКС($AI10;ДАТА(2019;(СТОЛБЕЦ(E1)-ОТБР((СТОЛБЕЦ(E1)-6)/13))/4;ВЫБОР(ОСТАТ((СТОЛБЕЦ(E1)-ОТБР((СТОЛБЕЦ(E1)-5)/13));4);1;11;21)))+1)
*Не обратил внимания, что сумма из другой ячейки берётся. Формулу исправил, файл перезалил.
К сообщению приложен файл: Woltaren-1.xlsx(39.0 Kb)


Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Вторник, 19.02.2019, 09:02
 
Ответить
СообщениеФормула та же самая:
Код
=$AE10/($AJ10-$AI10+1)*ЕСЛИ(ИЛИ($AJ10<ДАТА(2019;(СТОЛБЕЦ(E1)-ОТБР((СТОЛБЕЦ(E1)-6)/13))/4;ВЫБОР(ОСТАТ((СТОЛБЕЦ(E1)-ОТБР((СТОЛБЕЦ(E1)-5)/13));4);1;11;21));$AI10>ДАТА(2019;(СТОЛБЕЦ(E1)+1-ОТБР((СТОЛБЕЦ(E1)-6)/13))/4;ВЫБОР(ОСТАТ((СТОЛБЕЦ(E1)-ОТБР((СТОЛБЕЦ(E1)-5)/13));4);10;20;)));;МИН($AJ10;ДАТА(2019;(СТОЛБЕЦ(E1)+1-ОТБР((СТОЛБЕЦ(E1)-6)/13))/4;ВЫБОР(ОСТАТ((СТОЛБЕЦ(E1)-ОТБР((СТОЛБЕЦ(E1)-5)/13));4);10;20;)))-МАКС($AI10;ДАТА(2019;(СТОЛБЕЦ(E1)-ОТБР((СТОЛБЕЦ(E1)-6)/13))/4;ВЫБОР(ОСТАТ((СТОЛБЕЦ(E1)-ОТБР((СТОЛБЕЦ(E1)-5)/13));4);1;11;21)))+1)
*Не обратил внимания, что сумма из другой ячейки берётся. Формулу исправил, файл перезалил.

Автор - Светлый
Дата добавления - 19.02.2019 в 08:53
Woltaren Дата: Среда, 20.02.2019, 21:35 | Сообщение № 27
Группа: Пользователи
Ранг: Новичок
Сообщений: 14
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Светлый, ОГРОМНОЕ СПАСИБО за Ваши ответы!!!
Сегодня мне удалось заполнить свою таблицу и получил результаты за год на 100% совпадающие с исходными столбцами по результату.
Но мне, к великому сожалению (и это правда), не понятен смысл этой формулы, я не могу её прочитать и уж тем более изменить или скорректировать, если вдруг понадобится смещение столбцов, или какая другая неожиданность произойдет.
Светлый, я ОЧЕНЬ прошу Вас, опишите пожалуйста последнюю формулу, откуда там, что берётся и как происходит механика вычисления. Мне раньше не приходилось сталкиваться с этими функциями, поэтому буду признателен за максимально подробное описание. pray
 
Ответить
СообщениеСветлый, ОГРОМНОЕ СПАСИБО за Ваши ответы!!!
Сегодня мне удалось заполнить свою таблицу и получил результаты за год на 100% совпадающие с исходными столбцами по результату.
Но мне, к великому сожалению (и это правда), не понятен смысл этой формулы, я не могу её прочитать и уж тем более изменить или скорректировать, если вдруг понадобится смещение столбцов, или какая другая неожиданность произойдет.
Светлый, я ОЧЕНЬ прошу Вас, опишите пожалуйста последнюю формулу, откуда там, что берётся и как происходит механика вычисления. Мне раньше не приходилось сталкиваться с этими функциями, поэтому буду признателен за максимально подробное описание. pray

Автор - Woltaren
Дата добавления - 20.02.2019 в 21:35
Светлый Дата: Среда, 20.02.2019, 23:59 | Сообщение № 28
Группа: Проверенные
Ранг: Старожил
Сообщений: 1059
Репутация: 272 ±
Замечаний: 0% ±

Excel 2010
Принцип действия формулы такой:
1. формируем нижний и верхний диапазон декады.
2. проверяем, выпал ли диапазон декады из диапазона договора: нижняя дата любого из двух диапазонов больше верхней даты другого диапазона.
3. если выпал, берём 0
4. если есть пересечение, находим минимальное значение пересечения верхней границы и максимальное значение пересечения нижней границы. Берём их разность.
Таким образом получаем число дней в декаде, которое пересекается с датами внутри договора.

теперь, как формируем нижний диапазон декады:
1. остаток от деления откорректированного номера столбца на 4 помогает нам выбрать функцией ВЫБОР() день для функции ДАТА() 1,11,21 - для начала декады и 10,20,0 для конца декады.
2. целая часть от деления откорректированного номера столбца на 4 задаёт нам номер месяца в функции ДАТА(). Корректировка подобрана так, чтобы для начальной даты все три месяца в трёх декадах были одинаковые например: 2;1 2;11 2;21. А для конечной даты номер месяца для последней декады на 1 больше, чем для двух первых декад. Например: 2;10 2;20 3;0. Нулевой день означает предыдущий день, а это ПОСЛЕДНИЙ день предыдущего месяца независимо от его длины. Таким образом третья декада от 21 числа месяца до последнего числа месяца.
Каждый квартал вставляется дополнительный столбец суммы за квартал. Из-за него съезжает правильный номер столбца. Чтобы его вернуть обратно, из номера столбца вычитаем целую часть от деления номера столбца на 13=(3декады+1сумма за месяц)*3месяца в квартале+1сумма за квартал.
Код
СТОЛБЕЦ(E1)-ОТБР((СТОЛБЕЦ(E1)-6)/13))
Таким образом, получаем откорректированный номер столбца.
Ну вот как-то так.


Программировать проще, чем писать стихи.
 
Ответить
СообщениеПринцип действия формулы такой:
1. формируем нижний и верхний диапазон декады.
2. проверяем, выпал ли диапазон декады из диапазона договора: нижняя дата любого из двух диапазонов больше верхней даты другого диапазона.
3. если выпал, берём 0
4. если есть пересечение, находим минимальное значение пересечения верхней границы и максимальное значение пересечения нижней границы. Берём их разность.
Таким образом получаем число дней в декаде, которое пересекается с датами внутри договора.

теперь, как формируем нижний диапазон декады:
1. остаток от деления откорректированного номера столбца на 4 помогает нам выбрать функцией ВЫБОР() день для функции ДАТА() 1,11,21 - для начала декады и 10,20,0 для конца декады.
2. целая часть от деления откорректированного номера столбца на 4 задаёт нам номер месяца в функции ДАТА(). Корректировка подобрана так, чтобы для начальной даты все три месяца в трёх декадах были одинаковые например: 2;1 2;11 2;21. А для конечной даты номер месяца для последней декады на 1 больше, чем для двух первых декад. Например: 2;10 2;20 3;0. Нулевой день означает предыдущий день, а это ПОСЛЕДНИЙ день предыдущего месяца независимо от его длины. Таким образом третья декада от 21 числа месяца до последнего числа месяца.
Каждый квартал вставляется дополнительный столбец суммы за квартал. Из-за него съезжает правильный номер столбца. Чтобы его вернуть обратно, из номера столбца вычитаем целую часть от деления номера столбца на 13=(3декады+1сумма за месяц)*3месяца в квартале+1сумма за квартал.
Код
СТОЛБЕЦ(E1)-ОТБР((СТОЛБЕЦ(E1)-6)/13))
Таким образом, получаем откорректированный номер столбца.
Ну вот как-то так.

Автор - Светлый
Дата добавления - 20.02.2019 в 23:59
Woltaren Дата: Воскресенье, 14.04.2019, 19:17 | Сообщение № 29
Группа: Пользователи
Ранг: Новичок
Сообщений: 14
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Светлый, здравствуйте!
Обращаюсь Вам в продолжение той же темы
Мне изменили условия задачи, и пришлось полностью перестраивать отчет. Получилось его сделать своими руками, но прекрасно понимаю, что это не самое лучшее решение, а возможно, и с ошибками.
Большая просьба, если есть такая возможность, выразить своё мнение относительно готового отчёта, о его корректной работоспособности, и возможности оптимизации.
Измененные условия для отчёта следующие:
Есть две суммы (Данные № 1 и 2);
Данные в отчет (он сгруппирован) собираются построчно, в зависимости от срока действия договоров;
Общая сумма по договору делится так: Количество месяцев действия договора делится на сумму договора. Если договор продолжается все три декады какого-то месяца, то сумма за месяц делится на три декады. Если действие договора в данном месяце продолжается не весь месяц, а начинается, например, со второй, то сумма за месяц делится на количество декад, в течение которых действует договор в этом месяце. Например:
Если договор действует с 27.01.2019г. по 11.03.2019г., а сумма по договору равна 90 000 руб., то срок действия равен 3 месяца, а сумма в месяц будет равна 30 000 руб.
Но в январе задействована только одна декада (с 27.01.2019 по 31.01.2019), поэтому все 30 000 руб. пойдут на 3 декаду.
Второй месяц задействован полностью, поэтому в каждой декаде будет сумма по 10 000 руб. (всего так же, 30 000 руб.).
В третьем месяце задействованы две декады (1 и 2), поэтому 30 000 руб. делится на две ячейки, по 15 000 руб. (всего так же, 30 000 руб.).
Итогом этого отчёта будет являться сводная таблица, где на новом листе будут отображены данные за каждый месяц, за каждый квартал и за год. Просмотр данных будет именно в своде, поэтому количество столбцов для сбора данных принципиального значения не имеет.
Заранее благодарю за любой ответ на мою просьбу!!!
К сообщению приложен файл: 14.04.2019.xlsx(44.6 Kb)
 
Ответить
СообщениеСветлый, здравствуйте!
Обращаюсь Вам в продолжение той же темы
Мне изменили условия задачи, и пришлось полностью перестраивать отчет. Получилось его сделать своими руками, но прекрасно понимаю, что это не самое лучшее решение, а возможно, и с ошибками.
Большая просьба, если есть такая возможность, выразить своё мнение относительно готового отчёта, о его корректной работоспособности, и возможности оптимизации.
Измененные условия для отчёта следующие:
Есть две суммы (Данные № 1 и 2);
Данные в отчет (он сгруппирован) собираются построчно, в зависимости от срока действия договоров;
Общая сумма по договору делится так: Количество месяцев действия договора делится на сумму договора. Если договор продолжается все три декады какого-то месяца, то сумма за месяц делится на три декады. Если действие договора в данном месяце продолжается не весь месяц, а начинается, например, со второй, то сумма за месяц делится на количество декад, в течение которых действует договор в этом месяце. Например:
Если договор действует с 27.01.2019г. по 11.03.2019г., а сумма по договору равна 90 000 руб., то срок действия равен 3 месяца, а сумма в месяц будет равна 30 000 руб.
Но в январе задействована только одна декада (с 27.01.2019 по 31.01.2019), поэтому все 30 000 руб. пойдут на 3 декаду.
Второй месяц задействован полностью, поэтому в каждой декаде будет сумма по 10 000 руб. (всего так же, 30 000 руб.).
В третьем месяце задействованы две декады (1 и 2), поэтому 30 000 руб. делится на две ячейки, по 15 000 руб. (всего так же, 30 000 руб.).
Итогом этого отчёта будет являться сводная таблица, где на новом листе будут отображены данные за каждый месяц, за каждый квартал и за год. Просмотр данных будет именно в своде, поэтому количество столбцов для сбора данных принципиального значения не имеет.
Заранее благодарю за любой ответ на мою просьбу!!!

Автор - Woltaren
Дата добавления - 14.04.2019 в 19:17
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Расчет суммы по декадам месяца, по месяцам и по кварталам (Формулы/Formulas)
  • Страница 2 из 2
  • «
  • 1
  • 2
Поиск:

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