Решаю задачу учёта бланков сверхстрогой отчётности Суть: люди получают номерные бланки и далее их используют по назначению, сдают назад либо самым бессовестным образом теряют или портят (в приложенном файле на странице "Движение"). Факт получения обозначается новой записью в таблице и сопровождается обязательным проставлением даты получения, факт списания - проставлением даты списания и причины (основания) списания. Далее руководство хочет ежемесячно получать отчёт вида (в приложенном файле на странице "Шаблон отчёта"): - на начало месяца у ФИО было столько-то бланков с такими-то номерами, - в течение месяца он получил столько-то бланков с такими-то номерами, - в течение месяца с него списано столько-то бланков с такими-то номерами (при этом необходимо разбивка по причинам (основаниям) списания), - на конец месяца у ФИО было столько-то бланков с такими-то номерами.
Предполагаю следующий алгоритм решения. К таблице "Движение" создаю дополнительные столбцы: "На начало", "Получил", "Израсходовал", "Потерял", "Испортил", "Вернул", "На конец", которые в зависимости от того, фигурировал ли данный бланк в данной категории в заданном периоде, заполняю либо номером бланка либо оставляю пустым. Например, в случае если дата получения сотрудником бланка лежит в периоде отчёта, ставлю в столбце "Получил" номер бланка, если же он получен ранее или позднее, то пусто. Поскольку номеров на самом деле много, прошу обратить внимание в отчёте на конструкции вида 5025 - 5028.
И тут классические муки выбора. Какие инструменты либо их сочетания использовать для решения задачи (VBA, PQ, PP)? Наверно, будет некий симбиоз... В идеале, хотелось бы чтобы пользователь выбрал временной срез (ну, или на крайняк ввёл даты) и отчёт оперативно перезаполнился.
Собственно вопросы, уважаемые форумчане: 1. В какой среде (PP, PQ) лучше сделать добавление вышеперечисленных вычисляемых столбцов? Или задачу можно решить альтернативным способом? 2. В какой среде лучше сделать сбор номеров для организации диапазонов (5025 - 5028). 3. В какой среде лучше сделать заполнение отчёта по форме в файле? VBA и переносить данные с какой-нибудь сводной? Можно ли к отчёту прилепить временной срез?
Ограничение: на рабочих компах стоит EXCEL 2013.
Помогите принять решение. Не прошу решать задачу - это мой крест. Но за экспертное мнение по заданным вопросам буду премного благодарен.
В какой среде лучше сделать сбор номеров для организации диапазонов (5025 - 5028)
Сейчас много различных технологий - если развернут SharePoint, то получил / сдал наверное можно решить QR-код (при печати или стикер) + сканер/телефон + PowerApp
quasarrr: Какие инструменты либо их сочетания использовать для решения задачи (VBA, PQ, PP)?
как говориться, кто на что учился… Я знаю VBA и всё сделал бы на нём. Плюсы - скорость (пока всё, с чем я сталкивался быстрее работает на VBA нежели PP и PQ), гибкость (тут тебе и пользовательские формы, и вызов по событиям, и расчёты внутри кода, и выгрузки) и универсальность (на Excel 2013 Excel нет многих улучшений того же PQ, не говоря уже о 2003 — старина VBA же давно неизменен)
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
quasarrr, Нужно отталкиваться от того что нужно и к этому подбирать средства. тем летом навоял своим на коленке файлик по учету расходников, без причудливого интерфейса на основе умной таблице куда заносится и приход и расход. с зависимым выпадающими списками (помещение, принтер , модель, тип расходника (а их до 10 на принтер). в любой последовательности выбора) Простейшие справочники , MSQuery для выпадающих списков, которые меняются только при изменении справочников , из VBA только обновление этих запросов. Отчет для бухгалтерии - сводная на базе запроса, отчет для утилизации - тоже … . Все просто и работает. Не шедевр, но минимум труда. Можно лучше - да, Можно с формами - да. Вопрос нужно или нет. И не стоит ли ваще от Excel уйти. Например возможности отчетности в access намного превышают excel. Ну и выбор инструмента часто зависит от того кто потом это будет обслуживать и его компетенций в той или иной области.
Это вы просто написали? Или будут и эти столбцы? В примере их нет на листе Шаблон отчёта. Добавьте еще данных на лист Движение, чтобы в итоге были и Утеря, Порча, Возврат
Можно сделать "классическими" средствами. Необходимые поля - в скрытых столбцах листа "Движение". Двойной щелчок по человеку показывает/скрывает его бланки. При изменении листа "Движение" или начала/конца периода необходимо обновлять сводную таблицу.
sokol92, спасибо, хорошее направление, летает. Из недостатков. Появляются строки по человеку (Андреев), который в марте вообще не должен бы фигурировать (все значения равны нулю). Лучше бы его не было. А то таскать всю историю за собой накладно. Второе хуже. Номеров реально много. Строк будут тысячи. В большинстве случаев, в реальных данных они идут по порядку. с небольшими разрывами. Поэтому хотелось и перечисление сделать такого порядка: "5002 - 7854, 7856 - 9341". Подобную задачу сделали на PQ (см. выше), но, возможно, это не оптимально, поскольку при каждом пересчёте нужно гонять функции Table.AddColumn и далее преобразовывать их. С другой стороны в DAX нужно создавать вычисляемые столбцы, что не приветствуется. И Concatenatex не применишь, поскольку эксель 2013-й. Может меры какие-нибудь хитрые можно придумать?
Михаил Л, хорошо, завтра добросаю данные, как Вы просите.
В PQ могу попробовать сделать сам, хотя зная Ваш опыт, конечно, с удовольствием отдам на проверку свои решения, а то и на полное переосмысление. Тем не менее, можно ли при помощи PQ сформировать отчётную форму так, как она показана в примере? Или потребуются дополнительные усилия по трансформации того, что выдаст PQ в отчётную форму?
Обновил вложение в #9. Теперь в своде появляются только те номера бланков (и, соответственно, ФИО), по которым есть движение в периоде. Обратите внимание на дополнительное вычисляемое поле "Пр" в поле значений сводной таблицы (столбец скрыт) и фильтр, установленный в поле "Номер РО". Остаюсь при мнении, что отчет лучше анализировать в свернутом виде, при необходимости "проваливаться" до номеров бланков. Это относится и к руководству - пора переходить на электронный документооборот.
sokol92 написал: Это относится и к руководству - пора переходить на электронный документооборот
К сожалению БигБосс какого-то чёрта влез в этот процесс. И видимо, чтобы паркер не засох, хочет на бумажке сверху надписывать: "Утверждаю, имярек, дата". Но как инструмент дальнейшего анализа каким-нибудь продвинутым менеджером среднего звена Ваше решение вполне жизнеспособно.
sokol92, Михаил Л, появилась мысль, я постараюсь её реализовать и попрошу высказать мнение, если Вы не против.
Михаил Л написал: Что у вас там, мысль сопротивляется?
ужасно сопротивляется. Немного знаний и опыта добавить и у неё не было бы шансов .
Если на концептуальном уровне, то хочу "наколхозить" ((С) не мой, Murderface, кажется) запрос, который бы формировал таблицу со структурой отчёта и выгрузить её под шапкой отчёта.
Но всё идёт с ужасным скрипом... Но идёт... Но медленно... Но интересно
Новичок покувыркался и в принципе задачу решил. Не без помощи добрых людей, конечно.
Если есть желание и время, можно оценить предложенное решение и, может, подсказать пути улучшения. В файле данные за три месяца. Все виды поступления - расходования бланков в примере присутствуют.
По сравнению с постановкой задачи из #1 добавилась ещё одна таблица с начальниками смен, которые получают бланки и раздают водителям и далее солидарно отвечают за их сохранность. Поэтому также фигурируют в отчёте.
let
DateFrom = Date.From(Excel.CurrentWorkbook(){[Name="DateFrom"]}[Content][Column1]{0}),
DateTo = Date.From(Excel.CurrentWorkbook(){[Name="DateTo"]}[Content][Column1]{0}),
ii = (items) => let
addLocalId = Table.AddIndexColumn(Table.FromColumns({items}), "local"),
addDiff = Table.AddColumn(addLocalId, "dif", each [Column1] - [local]),
grouped = Table.Group(addDiff, {"dif"}, {"temp", (sub) =>
if Table.RowCount(sub) > 2
then Text.From(List.First(sub[Column1])) & "-" & Text.From(List.Last(sub[Column1]))
else Text.Combine(List.Transform(sub[Column1], Text.From), ",")})
in Text.Combine(grouped[temp], ","),
i = (abc)=> let d=List.Count(abc[Номер РО]), q=ii(Table.Sort(abc,{{"Номер РО", Order.Ascending}})[Номер РО]) in [b=if 0=d then null else d,p=q],
Источник = Table.SelectRows(Подкл_БД_Водитель, each [Дата получения] <= DateTo and ([Дата списания с водителя] >= DateFrom or [Дата списания с водителя] = null)),
Слияние = Table.NestedJoin(Источник,{"Номер РО"},Подкл_БД_СМНач,{"Номер РО"},"Подкл_БД_СМНач",JoinKind.LeftOuter),
#"Expanded {0}1" = Table.ExpandTableColumn(Слияние, "Подкл_БД_СМНач", {"ФИО"}, {"ФИО"}),
sub = Table.Group(#"Expanded {0}1", {"ФИО водителя", "ФИО"}, {{"a", (sub)=> let
a = i(Table.SelectRows(sub, each [Дата получения]<DateFrom)),
b = i(Table.SelectRows(sub, each [Дата получения]>=DateFrom and [От кого получено] = "Сменный начальник")),
c = i(Table.SelectRows(sub, each [Дата получения]>=DateFrom and [От кого получено] = "Другой водитель")),
d = i(Table.SelectRows(sub, each [Дата списания с водителя]<=DateTo and [Основание списания] = "Заправка")),
e = i(Table.SelectRows(sub, each [Дата списания с водителя]<=DateTo and [Основание списания] = "Порча")),
f = i(Table.SelectRows(sub, each [Дата списания с водителя]<=DateTo and [Основание списания] = "Утеря")),
g = i(Table.SelectRows(sub, each [Дата списания с водителя]<=DateTo and [Основание списания] = "Передача другому водителю")),
h = i(Table.SelectRows(sub, each [Дата списания с водителя]<=DateTo and [Основание списания] = "Возврат")),
j = i(Table.SelectRows(sub, each [Дата списания с водителя]>DateTo or [Дата списания с водителя] = null ))
in #table({"a".."r"},{{a[b],a[p],b[b],b[p],c[b],c[p],d[b],d[p],e[b],e[p],f[b],f[p],g[b],g[p],h[b],h[p],j[b],j[p]}})}}),
#"Expanded {0}" = Table.ExpandTableColumn(sub, "a", {"a".."r"})
in
#"Expanded {0}"
quasarrr написал: идут они у одного водителя, как правило, в порядке возрастания
Заметил что не всегда по возрастанию, поэтому добавил сортировку. Если нужно сохранить имеющийся порядок следования, то удалите сортировку. Оставьте только abc