alex8891, благодарю, но у Вас мало сообщений на сайте, не внушаете доверия)

Добрый день.

Задание исключительно для 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 строк (если такое возможно), я надеюсь, что можно.

Цена и сроки - готов обговаривать.

МатросНаЗебре, что-то не получилось, ничего не произошло.

Хотелось бы решение на формулах (если такое вообще возможно).

AlienSx, найти по каждой ветке максимальное число - нашел, каким образом потом определить, что это необходимое максимальное число (а идти с конца к началу нужно), не знаю.
Более того, предполагаю, что задача может быть решена совершенно иным путем, чем думаю лично. Отсюда и просьба помочь.

Что касается возложения на "решалкиных" - кто знает данное определение, тот, наверное, уже сталкивался с подобного рода расчетом. И потом всегда можно сообщить "готов помочь за денежку", не против и платной помощи.

Не нужно думать, что тут здесь односторонняя позиция, всегда иду в диалог.

Добрый день.

Имеется плоская таблица с иерархией и датами. По ней нужно показать критический путь (самую длительную последовательность сборки).
Прошу помочь сделать это как формулами, так и с помощью Power Query.

Благодарю.

gling, не понял Вашего комментария. Формула ссылается на столбец С. Данный столбец только для примера, его нельзя использовать в расчете, его же найти нужно. Если диапазон переделать на тот, в который вносится формула, то она зацикливается.

AlienSx, спасибо огромное! То, что нужно.

gling, Ваш метод плох тем, что ссылается на столбец, который и нужно получить (столбец С)

МатросНаЗебре, спасибо за формулу, буду разбираться.

Наверное некорректное описание выдал. Логика была:
- если самый низкий порядок, то 0;
- следующий порядок = Данные + максимальное число из значений порядка ниже;
- и так далее до самой верхушки.

МатросНаЗебре, вроде написал «в рамках одного порядка» (не путать с «веткой»). Не знаю, как по другому это точно объяснить.

МатросНаЗебре, если бы все было так просто)
Некорректно, так как у Вас считает наибольшее по всем порядкам, а нужно в рамках одного порядка. Скорректировал пример, в котором показывается, что у разных веток разное значение получается. И выбрать нужно наибольшее.

Добрый день.

Есть таблица, в которой имеется порядковая раскладка (иерархия). Напротив каждой строчки / позиции есть данные.
Необходимо осуществить расчет (суммирование) этих данных от конца к началу по наибольшему числу в рамках одного порядка иерархии.

Нужно решение как формулой, так и с помощью Power Query.

Благодарю за помощь.

Добрый день.

Есть желание вспомнить (изучить заново) работу в MS Access. Заодно и SQL.
Можете подсказать наиболее лучшие (с хорошей подачей и легкой усваиваемостью, лайфхаками, без «воды», хорошо структурированы) курсы, книги, видеоуроки?
Кто какими источниками знаний пользуется?)

Благодарю)

AlienSx, спасибо!

Добрый день.

Поясните, пожалуйста, такой момент по PQ.

Есть таблица из которой делается выгрузка, потом с ней проводим действий и выгружаем. На выгруженную таблицу добавляем столбец. В нем фиксируем какие-то значения напротив необходимых данных.

Проблема - если изначальные данные поменялись и таблица перевыгрузилась (обновилась), то значения съезжают.

Вопрос - есть ли способ, чтобы данные со значениями перемещались вместе с данными (типа, если строка удаляется, значит и вся строка удаляется, если данные добавляются, то идет сдвиг всей строки - как при вставке всей строки). В рамках одного запроса. Или это особенность PQ?

Благодарю.

Исполнителей нет(
Может кто подскажет, где можно выложить данную работу (форумы, соцсети), чтобы найти исполнителей?

Добрый день.

Имеется Excel-файл, который является рабочим реестром заявок / проектов, где ведется состав изделия и его обеспеченность.

На основе данного Excel-файла формируется задание на производство с QR-кодом (в QR-коде зашит уникальный ключ идентификатор).

Задача:
- на основе выданного задания сформировать программу для рабочего персонала, чтобы в режиме реального времени видеть ход операционных событий (что взято в работу, что закончено и т.д.), фактически - диспетчирование (связь через QR-код, уникальный идентификатор).

Более подробно постарался отразить в прикрепленном ТЗ. Также есть файлы Excel и старая программа (рабочая) - их могу скинуть лично.
Так как функционал старой программы мне понравился, хотел бы сделать аналогичное решение (DevExpress, .NET Framework, SQL). Если вдруг решение еще лучше, я только "за".

Срок - обговариваемый (хотелось бы основу получить до НГ).
Стоимость работы - нюансов много, если все реализуемы, готов обговорить.

Благодарю.

P.S.: скорректировал и дополнил ТЗ.

AlienSx, благодарю!

Можете пояснить принцип действия:Как работает логика?
Вижу, что мы проверяем по каждой строке количество значений в списках, если оно равно 1, то это ставится пометка.
Но что за список и как формируется ("Text.StartsWith(w, x[#"№"])" - вот этот кусок не понятен, возможно не силен в функциях).

AlienSx, для "Шестой"  - пусто, так как под ним нет раскладки.
Кажется, понял вопрос, "Шестой" встречается два раза - по первому совпадению сверху.

Шел следующим путем:
сделал умную таблицу с выбором списка -> загрузил ее в PQ и по ее значению фильтровал таблицу -> вытянул порядковый номер иерархии (предполагается, что здесь всегда одно первое совпадение) -> далее фильтрую всю таблицу с совпадением начала порядкового номера иерархии -> получаю всю необходимую ветку -> далее хотелось бы вытянут в ней все крайние номенклатуры (тут затык) -> далее группировка

Добрый день.

Подскажите, пожалуйста, как можно решить с помощью PowerQuery следующую задачу.

Имеем:
- раскладка какой-либо сборки по иерархии с указанием количества;
- табличку / ячейку, в котором выбираем тот или иной узел.

Необходимо от выбранного узла вытянуть все крайние номенклатуры по данной ветке и сгруппировать их, при этом у нас из привязок есть только структура таблицы и колонка № иерархии. Пример прикрепил.

У меня лично затык с тем, как вытащить именно все крайние ветки (выделил фиолетовым).

Буду благодарен, если поможете.

AlienSx, спасибо большое, буду изучать!

МатросНаЗебре, также огромное спасибо!

Sanja, спасибо, в VBA не силен

txxt, неплохо, попробую применить на 25000 строк)


Может кто-нибудь подскажет, как решить данную проблему через Power Query? (можно даже платно рассмотреть, если задача непростая)

Добрый день.

Не могу решить проблему с автоматическим заполнением вниз до необходимого уровня иерархии.
Если пользоваться стандартными средствами, то заполнение вниз идет на те ячейки, которые не нужно охватывать.
Пример прикрепляю.

Подскажите, пожалуйста, каким методом можно решить проблему (power query, надстройки).

Имеем базу на 25000 строк, протягивать в ручную очень утомительно.

Благодарю.

Разобрался сам, вид группировки нужно было настроить.
Век живи - век учись:)

Добрый день.

На сайте есть интересная статья про многоуровневое группирование строк - https://www.planetaexcel.ru/techniques/9/47/

Решил воспользоваться на своем примере, но возникли проблемы:
- группировка формируется с конца (сам плюсик уходит под строку, а не как в примере - рядом со строкой, скриншотом показал);
- почему-то не группируются некоторые строки (скриншотом показал).

Все, что делала - скопировал Module в новую книгу.
При этом, работа макроса в файле примере по сайте адекватно работает с листом примером, но если добавить лист, скопировать данные из листа примера и вставить в новый, запустить макрос, он все равно отработает некорректно.

Вопрос - почему так происходит и как можно исправить?

Благодарю.

skais675, необходимо, чтобы пользователь кликнул («встал») на ячейку, в ячейке начал вбивать текст и ему тутже показывался выпадающим список по совпадениям (после каждой введенной буквы список обновлялся), а следующим действием пользователь выбрал необходимое (аналогичный принцип - когда ищешь по фильтру в столбце)
Почему это важно - список будет следующего плана:
00000.000101.000
00000.000101.001
00000.000101.002
00000.000101.003
00000.000101.004
00000.000102.000
00000.000102.001
00000.000102.002
00000.000102.003
00000.000102.004
00000.000103.000
00000.000103.001
00000.000103.002
00000.000103.003
00000.000103.004
и т.д.
Пользователь будет впивать 3 цифры, например, 102, и ему сразу же должен быть предложен раскрывающий список, чтобы из совпадений он выбрал необходимый.

Такой принцип реализуем?
Вижу, что excel показывает списки, если вводить начало слова, но если вводить середину слова - не показывает, вот этот список и нужно выводить без дополнительных кликов. Встал в ячейку - набил, что ищешь - выбрал из предложенного.

Задание в силе.

Чуть прокомментирую - хотелось бы реализовать быстрый поиск по частичному совпадению. Чтобы пользователь - встал на ячейку, начал вводить текст, ему показался список совпадений и следующий действием он выбрал нужное (стрелкой или мышкой), без лишних дополнительных действий / кликов.
В прикрепленных примерах наглядно попытался показать что нужно.

МатросНаЗебре, вопрос в реализации быстрого поиска - тут способа нет насколько знаю для excel 2010, но если знаете я только за.

Добрый день.

Есть умная таблица (лист "База"), в которой идет перечисление Рабочих с привязкой к участку.
Есть вторая умная таблица (лист "Данные"), в которой нужно реализовать следующие правила:
1. По столбцу ФИО:
- выпадающий список с быстрым поиском по частичному совпадению, который автоматически раскрывается при наборе текста (если еще стрелками можно выбрать человека из списка - отлично). Быстрый поиск без привязки к регистру;
- выбирать строго из списка листа "Базы" (он может пополняться, изменяться), следовательно, проверка должна быть, иначе ошибка (как в стандартном функционале);
- если ячейка в колонке "Участок" (не привязываться именно к левому столбцу, лучше по наименованию) пустая, то в выпадающем списке столбца "ФИО" весь список. Если в нем стоит участок, то автоматически менять список на тех, кто присутствует на этом участке;
- если ячейка в колонке "Участок" пуста, выбирается человек из полного списка, то в колонке "Участок" автоматически вставлять наименование участка, которому принадлежит данный человек.
2. По столбцу Участок:
- список участков, который автоматически создается из листа "База" колонки "Участок" (фактически берутся уникальные значения и превращаются в список).
Все это нужно, чтобы работало в Excel 2010.
Реализация, как я понимаю, только через VBA.
Реализовать нужно так, чтобы потом человек автоматически смог подстроить данные правила под другие файлы, других таблиц (это к вопросу - к чему привязываться в коде), то есть нужно понимать в коде переменные (рассказать о них).

По оплате - в расценках не разбираюсь, кажется, что задача не сложная, предлагаю до 2 тыс. руб. Если речь о большей сумме, напишите в личку с пояснениями почему больше, обговорим.

По срокам - хотелось бы за ближайшие выходные.

БМВ, разобрался, значительно лучше стало, огромное спасибо!
Со второй частью - мой вариант оптимален, если данный расчет необходим (колонки "Долг от сдачи" и "Долг от назначения") или есть еще варианты оптимизировать?

Ребята, всем спасибо за ответы.

У меня вопрос - кажется, что все думают, проблема в нумерации иерархии.
Однако у себя провел эксперимент:
- если протянуть формулу нумерации иерархии на 25000 строк, то все быстро считается, фильтры работают как обычно, без задержек;
- если протянуть столбцы с расчетом количества по иерархии на 25000 строк, то сразу идет прогрузка.
Мне кажется, что проблема именно по второму пункту. Поправьте, если я ошибаюсь.

Мне бы пояснение, правильно или нет рассуждаю, правильно ли тесты показали проблему, какая функция или код формулы ведет к долгому расчету и каким способом (формулами) можно это устранить.