Добрый день.
Задание исключительно для PowerQuery (так как нужно вытягивать данные с разных источников, консолидировать их и потом производить расчеты).
Предполагаю, что понадобятся какие-то сложные функции, а я в них плаваю.
Прикрепляю картинку. По ней расскажу основные детали. Сам файл большой.
Дано:
- таблица на 200000 строк (это максимальные значения, средние - 120-150 тыс. строк);
- то, что без заливки и синими наименованиями колонок - это данные, которые вносят вручную;
- то, что имеет зеленую заливку - хотелось бы автоматизировать на основе данных от колонок без заливки.
Колонки, которые хотелось бы реализовать с помощью PowerQuery:
1. Иерархия - порядковые номер раскладки с точкой на конце. Работает по колонкам от 1 до 10. Этот параметр не постоянный, может быть увеличен до 15. Здесь хотелось бы иметь эту гибкость. По строке - в этих колонках строго одна запись только.
2. В какую номенклатуру входит - формулами реализовано так: по строке идет 1.2.4.10., убираем последний параметр и получаем 1.2.4. и после ВПР ищем по номенклатуре. Входимость в ближайший узел.
3. Количество на 1 комплект, ед. изм. - есть колонка "Количество по спецификации на 1 ед. изм.", через нее необходимо пересчитать все ветки по входимости. Например. в 1 шт. 1. входит 1.1. 2 шт., а в 1 шт. 1.1. входит 1.1.1. 3 шт., следовательно, после всего пересчета получаем, что на 1 шт. 1. нужно 2 шт. 1.1. и 6 шт. 1.1.1. (на картинке показано, думаю, логика понятна будет).
4. Сдано, автоматическое заполнение - есть отдельная таблица "Наличие" по номенкклатуре должно заполнять колонку сверху вниз (по Иерархии, например), на то количество, которое необходимо. Если наличия больше, то в последней строке этой номенклатуры показывается остаток весь (в файле примере это показано, на картинке не видно).
5. Расчетная дата План от конечной даты - есть какая то дата (Плановая, столбце слева), от нее обратных расчетом через трудоемкость расчитать день, когда нужно начинать. Отдельно нужна гибкость на расчетов часов: часы расчитывать только рабочие, сменны - это 8 или 12 и также пропускать праздники (отдельная таблица праздников есть). Например, 1. - 31.03, трудоемкость 8 ч., значит для 1.1.,1.2. и т.д. расчет = 31.03-(8/8) = получаем 27.08 (так как 8 / 8 - 8 часовая рабочая смена, а 30 и 29 числа - выходные), далее для 1.1.1., 1.1.2. и т.д. также берется получившая дата 1.1. - 27.03 и вытичается трудоемкость ее. Если необходимо, можно созвониться, объясню чуть подробнее на листочке.
6. Расчетная дата План от сегодня - принцип обратный предыдущему расчету. Все, что не выполнено и является последним элементом ветки всегда = сегодня + его трудоемкость. Например, это будет 1.1.1.1. Далее, нам нужно посчитать 1.1.1. Мы берем максимальную дату по 1.1.1.1., 1.1.1.2., 1.1.1.3. и т.д. и прибавляем к ней трудоемкость. И далее до верхушки.
7. Критический путь - те точки, где нельзя никак просаживать сроки. То есть показывается максимальные даты по ветке от конца к началу. Сначала выбирается максмиальная дата из 1.1., 1.2., 1.3., 1.4. и т.д. Например это будет 1.3. Далее в ее ветке выбирается максимальная из 1.3.1., 1.3.2., 1.3.3. и т.д. до конца ветки. Если максимальных несколько, значит дата показывается напротив каждой. Если позиция не хводит в критический путь - пусто.
8. Дельта, дн. - это простое вычитание отставания.
9. Сигнал о назначении - в числовом виде. Показывает, сколько еще можно назначить данной позици, из расчета - сколько сдано по каждой позиции, которая входит в нее, и сколько нужно на 1 комплект. Берется минимальное число. Например, для 1 шт. позиции 1. нужно 3 шт. 1.1. и 4 шт. 1.2. Сдано 6 шт. 1.1. и 12 шт. 1.2. Следовательно, мы можем назначить только 2 шт. 1. (6/3 = 2 и 12/4 = 3, меньшее - 2), но также нужно учесть, что уже могло быть назначение 1 шт., следовательно можно назначить только 1 шт.
Вроде бы все.
Конечно хотелось бы адекватное быстродействие всей это выгрузки на 200000 строк (если такое возможно), я надеюсь, что можно.
Цена и сроки - готов обговаривать.
Задание исключительно для PowerQuery (так как нужно вытягивать данные с разных источников, консолидировать их и потом производить расчеты).
Предполагаю, что понадобятся какие-то сложные функции, а я в них плаваю.
Прикрепляю картинку. По ней расскажу основные детали. Сам файл большой.
Дано:
- таблица на 200000 строк (это максимальные значения, средние - 120-150 тыс. строк);
- то, что без заливки и синими наименованиями колонок - это данные, которые вносят вручную;
- то, что имеет зеленую заливку - хотелось бы автоматизировать на основе данных от колонок без заливки.
Колонки, которые хотелось бы реализовать с помощью PowerQuery:
1. Иерархия - порядковые номер раскладки с точкой на конце. Работает по колонкам от 1 до 10. Этот параметр не постоянный, может быть увеличен до 15. Здесь хотелось бы иметь эту гибкость. По строке - в этих колонках строго одна запись только.
2. В какую номенклатуру входит - формулами реализовано так: по строке идет 1.2.4.10., убираем последний параметр и получаем 1.2.4. и после ВПР ищем по номенклатуре. Входимость в ближайший узел.
3. Количество на 1 комплект, ед. изм. - есть колонка "Количество по спецификации на 1 ед. изм.", через нее необходимо пересчитать все ветки по входимости. Например. в 1 шт. 1. входит 1.1. 2 шт., а в 1 шт. 1.1. входит 1.1.1. 3 шт., следовательно, после всего пересчета получаем, что на 1 шт. 1. нужно 2 шт. 1.1. и 6 шт. 1.1.1. (на картинке показано, думаю, логика понятна будет).
4. Сдано, автоматическое заполнение - есть отдельная таблица "Наличие" по номенкклатуре должно заполнять колонку сверху вниз (по Иерархии, например), на то количество, которое необходимо. Если наличия больше, то в последней строке этой номенклатуры показывается остаток весь (в файле примере это показано, на картинке не видно).
5. Расчетная дата План от конечной даты - есть какая то дата (Плановая, столбце слева), от нее обратных расчетом через трудоемкость расчитать день, когда нужно начинать. Отдельно нужна гибкость на расчетов часов: часы расчитывать только рабочие, сменны - это 8 или 12 и также пропускать праздники (отдельная таблица праздников есть). Например, 1. - 31.03, трудоемкость 8 ч., значит для 1.1.,1.2. и т.д. расчет = 31.03-(8/8) = получаем 27.08 (так как 8 / 8 - 8 часовая рабочая смена, а 30 и 29 числа - выходные), далее для 1.1.1., 1.1.2. и т.д. также берется получившая дата 1.1. - 27.03 и вытичается трудоемкость ее. Если необходимо, можно созвониться, объясню чуть подробнее на листочке.
6. Расчетная дата План от сегодня - принцип обратный предыдущему расчету. Все, что не выполнено и является последним элементом ветки всегда = сегодня + его трудоемкость. Например, это будет 1.1.1.1. Далее, нам нужно посчитать 1.1.1. Мы берем максимальную дату по 1.1.1.1., 1.1.1.2., 1.1.1.3. и т.д. и прибавляем к ней трудоемкость. И далее до верхушки.
7. Критический путь - те точки, где нельзя никак просаживать сроки. То есть показывается максимальные даты по ветке от конца к началу. Сначала выбирается максмиальная дата из 1.1., 1.2., 1.3., 1.4. и т.д. Например это будет 1.3. Далее в ее ветке выбирается максимальная из 1.3.1., 1.3.2., 1.3.3. и т.д. до конца ветки. Если максимальных несколько, значит дата показывается напротив каждой. Если позиция не хводит в критический путь - пусто.
8. Дельта, дн. - это простое вычитание отставания.
9. Сигнал о назначении - в числовом виде. Показывает, сколько еще можно назначить данной позици, из расчета - сколько сдано по каждой позиции, которая входит в нее, и сколько нужно на 1 комплект. Берется минимальное число. Например, для 1 шт. позиции 1. нужно 3 шт. 1.1. и 4 шт. 1.2. Сдано 6 шт. 1.1. и 12 шт. 1.2. Следовательно, мы можем назначить только 2 шт. 1. (6/3 = 2 и 12/4 = 3, меньшее - 2), но также нужно учесть, что уже могло быть назначение 1 шт., следовательно можно назначить только 1 шт.
Вроде бы все.
Конечно хотелось бы адекватное быстродействие всей это выгрузки на 200000 строк (если такое возможно), я надеюсь, что можно.
Цена и сроки - готов обговаривать.