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

Вход

Регистрация

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

 

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

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

Excel 2016
Добрый день! Прошу помощи в решении сложной для меня задачи:
Необходимо составить формулу, сколько сотрудник получит пропорционально, за период в промежутке дат (с 01 по 10 число, с 11 по 20, с 21 до конца месяца,
а так же за каждый месяц и за каждый квартал), при условии, что общая сумма договора с ним (столбец В), а период действия этого договора (столбцы С и D).
Главное условие - это точность подсчёта формулы, при разных датах договоров. И конечно, суммы должны быть пропорционально распределены по периоду действия договора.
Были попытки вычисления кол-ва дней, месяцев и кварталов (в ед.) в периоде действия договора, успешно, но дальше этого продвинуться не удалось, т.к. не получилось привязать их к датам месяцев внутри срока действия договора.
К сообщению приложен файл: _1.xlsx(9.8 Kb)
 
Ответить
СообщениеДобрый день! Прошу помощи в решении сложной для меня задачи:
Необходимо составить формулу, сколько сотрудник получит пропорционально, за период в промежутке дат (с 01 по 10 число, с 11 по 20, с 21 до конца месяца,
а так же за каждый месяц и за каждый квартал), при условии, что общая сумма договора с ним (столбец В), а период действия этого договора (столбцы С и D).
Главное условие - это точность подсчёта формулы, при разных датах договоров. И конечно, суммы должны быть пропорционально распределены по периоду действия договора.
Были попытки вычисления кол-ва дней, месяцев и кварталов (в ед.) в периоде действия договора, успешно, но дальше этого продвинуться не удалось, т.к. не получилось привязать их к датам месяцев внутри срока действия договора.

Автор - Woltaren
Дата добавления - 10.02.2019 в 15:52
Светлый Дата: Воскресенье, 10.02.2019, 16:43 | Сообщение № 2
Группа: Проверенные
Ранг: Ветеран
Сообщений: 931
Репутация: 227 ±
Замечаний: 0% ±

Excel 2010
Добрый день!
В первом прикиде можно так сделать.
К сообщению приложен файл: _1-1.xlsx(11.0 Kb)


Программировать проще, чем писать стихи.
 
Ответить
СообщениеДобрый день!
В первом прикиде можно так сделать.

Автор - Светлый
Дата добавления - 10.02.2019 в 16:43
Woltaren Дата: Воскресенье, 10.02.2019, 20:20 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Светлый, спасибо за интересный пример!
Можно уточнить, несколько моментов:
С какой целью используется «ЕСЛИОШИБКА», если «Значение_если_ошибка» не используется?
Почему используется «СЧИТАТЬПУСТОТЫ», если пустых ячеек тут нет, а идет 0.
И как правильно прочитать индексы, указанные Вами далее в формулах.
Заранее благодарю за ответы.
 
Ответить
СообщениеСветлый, спасибо за интересный пример!
Можно уточнить, несколько моментов:
С какой целью используется «ЕСЛИОШИБКА», если «Значение_если_ошибка» не используется?
Почему используется «СЧИТАТЬПУСТОТЫ», если пустых ячеек тут нет, а идет 0.
И как правильно прочитать индексы, указанные Вами далее в формулах.
Заранее благодарю за ответы.

Автор - Woltaren
Дата добавления - 10.02.2019 в 20:20
krosav4ig Дата: Воскресенье, 10.02.2019, 20:48 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 1851
Репутация: 795 ±
Замечаний: 0% ±

Excel 2007,2010,2013
Сводная по доп. таблице
К сообщению приложен файл: 1720954.xlsx(78.0 Kb)


email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460
 
Ответить
СообщениеСводная по доп. таблице

Автор - krosav4ig
Дата добавления - 10.02.2019 в 20:48
Светлый Дата: Воскресенье, 10.02.2019, 21:03 | Сообщение № 5
Группа: Проверенные
Ранг: Ветеран
Сообщений: 931
Репутация: 227 ±
Замечаний: 0% ±

Excel 2010
В моём прикидочном решении принцип определения совпадения интервалов двух событий (договора и периода расчёта) заключается в пересечении двух массивов. Массивы созданы функцией ИНДЕКС от начала одной даты до конца другой даты. Между ними пробел, который выполняет функцию пересечения этих массивов. Если есть несколько дней пересечения, то будет столько же ячеек пересечения, иначе пересечение будет пустое. Эта ошибка устраняется функцией ЕСЛИОШИБКА. Когда функция заканчивается ";)" это равнозначно ";0)". Значения дат больше 43000, поэтому ячейки пересечения лежат за 43000 строкой, а там пусто. Считаем пустые ячейки, это количество дней пересечения. Его умножаем на среднее значение договора. Среднее значение рассчитываем поделив сумму договора на число дней этого договора.
А "прикидочный" - это без обдумывания и оптимизации формул. Если бы были в ячейках номера месяца, можно было бы сделать формулу немного сложнее, но только одну - протягиваемую. Сейчас в каждой ячейке своя формула. Или можно по другому принципу количество дней подсчитывать.


Программировать проще, чем писать стихи.
 
Ответить
СообщениеВ моём прикидочном решении принцип определения совпадения интервалов двух событий (договора и периода расчёта) заключается в пересечении двух массивов. Массивы созданы функцией ИНДЕКС от начала одной даты до конца другой даты. Между ними пробел, который выполняет функцию пересечения этих массивов. Если есть несколько дней пересечения, то будет столько же ячеек пересечения, иначе пересечение будет пустое. Эта ошибка устраняется функцией ЕСЛИОШИБКА. Когда функция заканчивается ";)" это равнозначно ";0)". Значения дат больше 43000, поэтому ячейки пересечения лежат за 43000 строкой, а там пусто. Считаем пустые ячейки, это количество дней пересечения. Его умножаем на среднее значение договора. Среднее значение рассчитываем поделив сумму договора на число дней этого договора.
А "прикидочный" - это без обдумывания и оптимизации формул. Если бы были в ячейках номера месяца, можно было бы сделать формулу немного сложнее, но только одну - протягиваемую. Сейчас в каждой ячейке своя формула. Или можно по другому принципу количество дней подсчитывать.

Автор - Светлый
Дата добавления - 10.02.2019 в 21:03
Woltaren Дата: Воскресенье, 10.02.2019, 21:38 | Сообщение № 6
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

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

Автор - Woltaren
Дата добавления - 10.02.2019 в 21:38
Светлый Дата: Воскресенье, 10.02.2019, 23:21 | Сообщение № 7
Группа: Проверенные
Ранг: Ветеран
Сообщений: 931
Репутация: 227 ±
Замечаний: 0% ±

Excel 2010
Сделал одну универсальную формулу, которая автоматически рассчитывает интервалы дат. Массивная:
Код
=ЕСЛИОШИБКА($B2/($D2-$C2+1)*СЧИТАТЬПУСТОТЫ(ИНДЕКС(A:A;ДАТА(2019;(СТОЛБЕЦ()-ОТБР((СТОЛБЕЦ()-6)/13))/4;ВЫБОР(ОСТАТ((СТОЛБЕЦ()-ОТБР((СТОЛБЕЦ()-5)/13));4);1;11;21))):ИНДЕКС(A:A;ДАТА(2019;(СТОЛБЕЦ()+1-ОТБР((СТОЛБЕЦ()-6)/13))/4;ВЫБОР(ОСТАТ((СТОЛБЕЦ()-ОТБР((СТОЛБЕЦ()-5)/13));4);10;20;))) ИНДЕКС(A:A;$C2):ИНДЕКС(A:A;$D2));)
*Строки с формулами внизу это рабочие формулы. Они не нужны.
К сообщению приложен файл: _1-2.xlsx(17.7 Kb)


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

Сообщение отредактировал Светлый - Воскресенье, 10.02.2019, 23:27
 
Ответить
СообщениеСделал одну универсальную формулу, которая автоматически рассчитывает интервалы дат. Массивная:
Код
=ЕСЛИОШИБКА($B2/($D2-$C2+1)*СЧИТАТЬПУСТОТЫ(ИНДЕКС(A:A;ДАТА(2019;(СТОЛБЕЦ()-ОТБР((СТОЛБЕЦ()-6)/13))/4;ВЫБОР(ОСТАТ((СТОЛБЕЦ()-ОТБР((СТОЛБЕЦ()-5)/13));4);1;11;21))):ИНДЕКС(A:A;ДАТА(2019;(СТОЛБЕЦ()+1-ОТБР((СТОЛБЕЦ()-6)/13))/4;ВЫБОР(ОСТАТ((СТОЛБЕЦ()-ОТБР((СТОЛБЕЦ()-5)/13));4);10;20;))) ИНДЕКС(A:A;$C2):ИНДЕКС(A:A;$D2));)
*Строки с формулами внизу это рабочие формулы. Они не нужны.

Автор - Светлый
Дата добавления - 10.02.2019 в 23:21
Светлый Дата: Понедельник, 11.02.2019, 06:35 | Сообщение № 8
Группа: Проверенные
Ранг: Ветеран
Сообщений: 931
Репутация: 227 ±
Замечаний: 0% ±

Excel 2010
А вот эта формула немассивная и тоже протягиваемая по интервалам декад:
Код
=$B2/($D2-$C2+1)*ЕСЛИ(ИЛИ($D2<ДАТА(2019;(СТОЛБЕЦ()-ОТБР((СТОЛБЕЦ()-6)/13))/4;ВЫБОР(ОСТАТ((СТОЛБЕЦ()-ОТБР((СТОЛБЕЦ()-5)/13));4);1;11;21));$C2>ДАТА(2019;(СТОЛБЕЦ()+1-ОТБР((СТОЛБЕЦ()-6)/13))/4;ВЫБОР(ОСТАТ((СТОЛБЕЦ()-ОТБР((СТОЛБЕЦ()-5)/13));4);10;20;)));;МИН($D2;ДАТА(2019;(СТОЛБЕЦ()+1-ОТБР((СТОЛБЕЦ()-6)/13))/4;ВЫБОР(ОСТАТ((СТОЛБЕЦ()-ОТБР((СТОЛБЕЦ()-5)/13));4);10;20;)))-МАКС($C2;ДАТА(2019;(СТОЛБЕЦ()-ОТБР((СТОЛБЕЦ()-6)/13))/4;ВЫБОР(ОСТАТ((СТОЛБЕЦ()-ОТБР((СТОЛБЕЦ()-5)/13));4);1;11;21)))+1)
Можно сделать, чтобы формула протягивалась и по столбцам суммирования по месяцам и кварталам, но это ещё больший монстр получится.


Программировать проще, чем писать стихи.
 
Ответить
СообщениеА вот эта формула немассивная и тоже протягиваемая по интервалам декад:
Код
=$B2/($D2-$C2+1)*ЕСЛИ(ИЛИ($D2<ДАТА(2019;(СТОЛБЕЦ()-ОТБР((СТОЛБЕЦ()-6)/13))/4;ВЫБОР(ОСТАТ((СТОЛБЕЦ()-ОТБР((СТОЛБЕЦ()-5)/13));4);1;11;21));$C2>ДАТА(2019;(СТОЛБЕЦ()+1-ОТБР((СТОЛБЕЦ()-6)/13))/4;ВЫБОР(ОСТАТ((СТОЛБЕЦ()-ОТБР((СТОЛБЕЦ()-5)/13));4);10;20;)));;МИН($D2;ДАТА(2019;(СТОЛБЕЦ()+1-ОТБР((СТОЛБЕЦ()-6)/13))/4;ВЫБОР(ОСТАТ((СТОЛБЕЦ()-ОТБР((СТОЛБЕЦ()-5)/13));4);10;20;)))-МАКС($C2;ДАТА(2019;(СТОЛБЕЦ()-ОТБР((СТОЛБЕЦ()-6)/13))/4;ВЫБОР(ОСТАТ((СТОЛБЕЦ()-ОТБР((СТОЛБЕЦ()-5)/13));4);1;11;21)))+1)
Можно сделать, чтобы формула протягивалась и по столбцам суммирования по месяцам и кварталам, но это ещё больший монстр получится.

Автор - Светлый
Дата добавления - 11.02.2019 в 06:35
Woltaren Дата: Понедельник, 11.02.2019, 22:19 | Сообщение № 9
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Светлый, спасибо Вам!
Оч. крутые формулы получились! Но в чём принципиальное отличие "массива" и "не массива" (Ваших двух вариантов) в конкретном случае, если, например таблица из 2-3 тыс. строк? Что предпочтительнее использовать? Что будет корректнее, логичнее, надёжнее, стабильнее, кроме личных предпочтений пользователя?
 
Ответить
СообщениеСветлый, спасибо Вам!
Оч. крутые формулы получились! Но в чём принципиальное отличие "массива" и "не массива" (Ваших двух вариантов) в конкретном случае, если, например таблица из 2-3 тыс. строк? Что предпочтительнее использовать? Что будет корректнее, логичнее, надёжнее, стабильнее, кроме личных предпочтений пользователя?

Автор - Woltaren
Дата добавления - 11.02.2019 в 22:19
Eegorkka Дата: Понедельник, 11.02.2019, 22:29 | Сообщение № 10
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Светлый, Светлый, здравствуйте!
Помогите, пожалуйста, решить задачу, которая находится в теме, переда данной - "Выбор по двум значениям, одно из которых интерполируется!".
 
Ответить
СообщениеСветлый, Светлый, здравствуйте!
Помогите, пожалуйста, решить задачу, которая находится в теме, переда данной - "Выбор по двум значениям, одно из которых интерполируется!".

Автор - Eegorkka
Дата добавления - 11.02.2019 в 22:29
Pelena Дата: Понедельник, 11.02.2019, 22:36 | Сообщение № 11
Группа: Админы
Ранг: Местный житель
Сообщений: 13626
Репутация: 2998 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
Eegorkka, не засоряйте чужую тему. Наберитесь терпения и ждите ответов в своей


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеEegorkka, не засоряйте чужую тему. Наберитесь терпения и ждите ответов в своей

Автор - Pelena
Дата добавления - 11.02.2019 в 22:36
Eegorkka Дата: Понедельник, 11.02.2019, 22:39 | Сообщение № 12
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Pelena, Хорошо, извините меня!
 
Ответить
СообщениеPelena, Хорошо, извините меня!

Автор - Eegorkka
Дата добавления - 11.02.2019 в 22:39
Светлый Дата: Вторник, 12.02.2019, 06:56 | Сообщение № 13
Группа: Проверенные
Ранг: Ветеран
Сообщений: 931
Репутация: 227 ±
Замечаний: 0% ±

Excel 2010
в чём принципиальное отличие "массива" и "не массива"
Отличие в некотором неудобстве ввода - для массивных формул надо одновременно нажать три клавиши Ctrl+Shift+Enter. Если нажать просто Enter, формула перестаёт работать. У массивных формул проблемы с объединёнными ячейками. Их не удаётся записать. Приходится снимать объединение, вписывать формулу и снова объединять.
Что предпочтительнее использовать?
Если предполагается частое изменение формул, то проще изменить одну формулу и скопировать её во все остальные ячейки. Формула должна быть протягиваемой. Если Вы один раз создали удовлетворяющую все потребности форму или таблицу, то можно обойтись более простыми и быстродействующими формулами. Но при правке таких формул может возникнуть ошибка, которую трудно будет обнаружить.


Программировать проще, чем писать стихи.
 
Ответить
Сообщение
в чём принципиальное отличие "массива" и "не массива"
Отличие в некотором неудобстве ввода - для массивных формул надо одновременно нажать три клавиши Ctrl+Shift+Enter. Если нажать просто Enter, формула перестаёт работать. У массивных формул проблемы с объединёнными ячейками. Их не удаётся записать. Приходится снимать объединение, вписывать формулу и снова объединять.
Что предпочтительнее использовать?
Если предполагается частое изменение формул, то проще изменить одну формулу и скопировать её во все остальные ячейки. Формула должна быть протягиваемой. Если Вы один раз создали удовлетворяющую все потребности форму или таблицу, то можно обойтись более простыми и быстродействующими формулами. Но при правке таких формул может возникнуть ошибка, которую трудно будет обнаружить.

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

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