Светлый, хорошо, отправляю файл. Надо было сразу так сделать, но мне представлялось, что смогу вставить Ваши готовые формулы. Извините, что лишний раз морочу Вам голову!
Светлый, хорошо, отправляю файл. Надо было сразу так сделать, но мне представлялось, что смогу вставить Ваши готовые формулы. Извините, что лишний раз морочу Вам голову!Woltaren
Светлый, спасибо большое за ответ, но не подскажете, как должна выглядеть формула для второго варианта, ведь простой заменой букв тут не ограничиться...
Светлый, спасибо большое за ответ, но не подскажете, как должна выглядеть формула для второго варианта, ведь простой заменой букв тут не ограничиться...Woltaren
Эт как же вы собираетесь из такой каракатицы свод делать? :facepalm: нарисовал запрос в PowerQuery для всех годов и вариантов, на его основе строится сводная (на листе Лист1), запрос обновляется обновлением сводной или командой Обновить все (Ctrl+Alt+F5). Из сводной формулами данные тянутся в вашу таблицу
[vba]
Код
let Источник = Excel.CurrentWorkbook(){[Name="Данные"]}[Content], #"Повышенные заголовки" = Table.PromoteHeaders(Источник, [PromoteAllScalars=true]), Пользовательская1 = Table.FromRecords(Table.TransformRows(#"Повышенные заголовки",each let n=Number.From([#"Дата конец"]-[#"Дата начало"])+1 in [#"№ работника"=[#"№"],сумма=#table({"Вариант","Сумма"},{{"Вариант 1",[#"Этот столбец для Варианта № 1"]/n},{"Вариант 2",[#"Этот столбец для Варианта № 2"]/n}}),#"Дата"=List.Transform({Number.From([#"Дата начало"])..Number.From([#"Дата конец"])},Date.From)])), #"Развернутый элемент сумма" = Table.ExpandTableColumn(Пользовательская1, "сумма", {"Вариант", "Сумма"}, {"Вариант", "Сумма"}), #"Развернутый элемент дата" = Table.ExpandListColumn(#"Развернутый элемент сумма", "Дата"), #"Добавлен пользовательский объект" = Table.AddColumn(#"Развернутый элемент дата", "Год", each Date.Year([Дата])), #"Добавлен пользовательский объект1" = Table.AddColumn(#"Добавлен пользовательский объект", "Квартал", each Text.Replace(Text.Repeat("I",Date.QuarterOfYear([Дата])),"IIII","IV")&" квартал"), #"Добавлен пользовательский объект2" = Table.AddColumn(#"Добавлен пользовательский объект1", "Месяц", each Date.MonthName([Дата],"ru-ru")), #"Добавлен пользовательский объект3" = Table.AddColumn(#"Добавлен пользовательский объект2", "Декада", each Text.Repeat("I",List.Min({3,Number.RoundDown((Date.Day([Дата])+9)/10)}))&" декада"), #"Сгруппированные строки" = Table.Group(#"Добавлен пользовательский объект3", {"№ работника", "Вариант", "Год", "Квартал", "Месяц", "Декада"}, {{"Сумма", each List.Sum([Сумма]), type number}}) in #"Сгруппированные строки"
[/vba]
чую, следующий вопрос будет, как будет выглядеть 2018 год внутри того же файла...
Эт как же вы собираетесь из такой каракатицы свод делать? :facepalm: нарисовал запрос в PowerQuery для всех годов и вариантов, на его основе строится сводная (на листе Лист1), запрос обновляется обновлением сводной или командой Обновить все (Ctrl+Alt+F5). Из сводной формулами данные тянутся в вашу таблицу
[vba]
Код
let Источник = Excel.CurrentWorkbook(){[Name="Данные"]}[Content], #"Повышенные заголовки" = Table.PromoteHeaders(Источник, [PromoteAllScalars=true]), Пользовательская1 = Table.FromRecords(Table.TransformRows(#"Повышенные заголовки",each let n=Number.From([#"Дата конец"]-[#"Дата начало"])+1 in [#"№ работника"=[#"№"],сумма=#table({"Вариант","Сумма"},{{"Вариант 1",[#"Этот столбец для Варианта № 1"]/n},{"Вариант 2",[#"Этот столбец для Варианта № 2"]/n}}),#"Дата"=List.Transform({Number.From([#"Дата начало"])..Number.From([#"Дата конец"])},Date.From)])), #"Развернутый элемент сумма" = Table.ExpandTableColumn(Пользовательская1, "сумма", {"Вариант", "Сумма"}, {"Вариант", "Сумма"}), #"Развернутый элемент дата" = Table.ExpandListColumn(#"Развернутый элемент сумма", "Дата"), #"Добавлен пользовательский объект" = Table.AddColumn(#"Развернутый элемент дата", "Год", each Date.Year([Дата])), #"Добавлен пользовательский объект1" = Table.AddColumn(#"Добавлен пользовательский объект", "Квартал", each Text.Replace(Text.Repeat("I",Date.QuarterOfYear([Дата])),"IIII","IV")&" квартал"), #"Добавлен пользовательский объект2" = Table.AddColumn(#"Добавлен пользовательский объект1", "Месяц", each Date.MonthName([Дата],"ru-ru")), #"Добавлен пользовательский объект3" = Table.AddColumn(#"Добавлен пользовательский объект2", "Декада", each Text.Repeat("I",List.Min({3,Number.RoundDown((Date.Day([Дата])+9)/10)}))&" декада"), #"Сгруппированные строки" = Table.Group(#"Добавлен пользовательский объект3", {"№ работника", "Вариант", "Год", "Квартал", "Месяц", "Декада"}, {{"Сумма", each List.Sum([Сумма]), type number}}) in #"Сгруппированные строки"
Светлый, ОГРОМНОЕ СПАСИБО за Ваши ответы!!! Сегодня мне удалось заполнить свою таблицу и получил результаты за год на 100% совпадающие с исходными столбцами по результату. Но мне, к великому сожалению (и это правда), не понятен смысл этой формулы, я не могу её прочитать и уж тем более изменить или скорректировать, если вдруг понадобится смещение столбцов, или какая другая неожиданность произойдет. Светлый, я ОЧЕНЬ прошу Вас, опишите пожалуйста последнюю формулу, откуда там, что берётся и как происходит механика вычисления. Мне раньше не приходилось сталкиваться с этими функциями, поэтому буду признателен за максимально подробное описание.
Светлый, ОГРОМНОЕ СПАСИБО за Ваши ответы!!! Сегодня мне удалось заполнить свою таблицу и получил результаты за год на 100% совпадающие с исходными столбцами по результату. Но мне, к великому сожалению (и это правда), не понятен смысл этой формулы, я не могу её прочитать и уж тем более изменить или скорректировать, если вдруг понадобится смещение столбцов, или какая другая неожиданность произойдет. Светлый, я ОЧЕНЬ прошу Вас, опишите пожалуйста последнюю формулу, откуда там, что берётся и как происходит механика вычисления. Мне раньше не приходилось сталкиваться с этими функциями, поэтому буду признателен за максимально подробное описание. Woltaren
Принцип действия формулы такой: 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))
Таким образом, получаем откорректированный номер столбца. Ну вот как-то так.Светлый
Светлый, здравствуйте! Обращаюсь Вам в продолжение той же темы Мне изменили условия задачи, и пришлось полностью перестраивать отчет. Получилось его сделать своими руками, но прекрасно понимаю, что это не самое лучшее решение, а возможно, и с ошибками. Большая просьба, если есть такая возможность, выразить своё мнение относительно готового отчёта, о его корректной работоспособности, и возможности оптимизации. Измененные условия для отчёта следующие: Есть две суммы (Данные № 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 руб.). Итогом этого отчёта будет являться сводная таблица, где на новом листе будут отображены данные за каждый месяц, за каждый квартал и за год. Просмотр данных будет именно в своде, поэтому количество столбцов для сбора данных принципиального значения не имеет. Заранее благодарю за любой ответ на мою просьбу!!!
Светлый, здравствуйте! Обращаюсь Вам в продолжение той же темы Мне изменили условия задачи, и пришлось полностью перестраивать отчет. Получилось его сделать своими руками, но прекрасно понимаю, что это не самое лучшее решение, а возможно, и с ошибками. Большая просьба, если есть такая возможность, выразить своё мнение относительно готового отчёта, о его корректной работоспособности, и возможности оптимизации. Измененные условия для отчёта следующие: Есть две суммы (Данные № 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
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
