Страницы: 1
RSS
Преобразование табеля рабочего времени из xls через Power Query
 
Добрый день, прошу помочь с преобразованием табеля через power query в нужный формат для дальнейшей обработки и анализа.
Во вложении пример:
1 лист "исх." - то как выглядит табель при выгрузке из 1 С.
2 лист "Надо" - так  было бы здорово если можно преобразовать его в такой формат через PQ
Это возможно?
Я пыталась - затык получается  на переносе строк в ряд и сцепка значений.
 
Добрый
Код
let
    a = Excel.Workbook(Web.Contents("https://www.planetaexcel.ru/bitrix/components/bitrix/forum.interface/show_file.php?fid=537996&action=download")){[Item="исх.",Kind="Sheet"]}[Data],
    b = Table.PromoteHeaders(Table.Skip(a,17)),
    c = Table.Skip(b,2),
    d = Table.FirstN(c,12),
    e = Table.SelectColumns(d, {"Column3"} & List.Select(Table.ColumnNames(d), (x)=> try Number.From(x) is number otherwise false)),
    g = Table.SplitColumn(e, "Column3", (x)=> List.Transform(Splitter.SplitTextByEachDelimiter({"("}, QuoteStyle.Csv, true)(x), (q)=> Text.Remove(q, ")")), {"q", "w"}),
    h = Table.TransformColumns(g, {}, (x)=> x??""),
    k = List.Split(Table.ToRows(h), 2),
    l = List.Transform(k, (x)=> List.Transform(List.Zip({x{0}, x{1}}) , (q)=> Text.Combine({q{1} & q{0}}))),
    m = List.Transform(List.Split(l, 2), (x)=> List.RemoveLastN(x{0}, 1) & List.Skip(x{1}, 2)),
    n = Table.FromList(m, (x)=> x, {"фио"} & {"долж"} & List.Transform({1..31}, Text.From))
in
    n
 
Антон, здравствуйте, а почему осталось только 3 фамилии?  в исходнике 6 фамилий и нужно по всем аналогичную  обработку.
Проблема в том что у кого то 4 строки - а у кого то 2 или 3 строки может быть
И код у вас интересный  не видела такого в PQ  
 
ИЛариса, добрый день!
Насколько использование PQ обязательно?
А то я по-простому попробовал, без PQ.
 
andypetr, Здравствуйте!
Необязательно PQ ..
Ваш подход "простой" сработал. Формулы мне не понятны пока, но обязательно разберусь!
Спасибо Огромное!!!
Беру в работу - готова отблагодарить $
 
ИЛариса, добрый день.

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

Самое сложное было подобрать формулу к переменной высоте строк данных, которую "экономная" 1С варьирует от 2 до 4.
И я до сих пор сомневаюсь в её корректности - нужно проверить на большем объёме данных и на разных случаях.

Вот кратко по формулам (если нужно, подробнее опишу непонятные моменты).

1 лист "Исх", вспомогательные столбцы с формулами:
  • A) Нпп - порядковый номер сотрудника или 0.
  • B) Размер - накапливаю снизу вверх кол-во строк с данными для одного сотрудника. Соответственно, нормальный результат будет в первой строке (к ней обращаемся при поиске с листа Надо).
  • C) ФИО - если F заполнен, то берём часть строки до "(", иначе - дублируем с предыдущей строки.
  • D) Должность - если F заполнен, то берём часть строки между "(...)".
2 лист "Надо":
  • Строка №1 (столбец в исх.) - на листе Исх в строках 15 (1-15 число месяца) и 18 (16-31 число) ищется номер столбца, соответствующего дню месяца из строки №2.
  • A) Индекс - номер сроки в Исх с началом данных сотрудника. Например, для A3 ищется 1 (СТРОКА()-2) в столбце исх.!$A:$A, поиск вернёт 21 (№ первой строки первого сотрудника).
  • B) Размер, C) ФИО, D) Должность - данные из столбцов листа Исх для строки с номером из столбца A (21 = Бернацкий и т.д.).
  • Наконец, формула в самой таблице Надо (ячейка E3 и ниже/правее неё), в которой я сомневаюсь:
Код
="" & ЕСЛИ((E$2>15)*($B3<3); ""; ЕСЛИ(
ИНДЕКС(исх.!$1:$1048576; $A3+(E$2>15)*(2+($B3-4)); E$1) = "В"; "";
ИНДЕКС(исх.!$1:$1048576; $A3+(E$2>15)*(2+($B3-4)) + 1; E$1)) &
ИНДЕКС(исх.!$1:$1048576; $A3+(E$2>15)*(2+($B3-4)); E$1))

Если число > 15 и размерность < 3 - вернёт "".
Умножение двух условий означает логическое И (для краткости):
Код
="" & ЕСЛИ((E$2>15)*($B3<3); ""; 

Если собираемся выводить "В", то ничего не дописываем к текущей ячейке табеля, иначе дописываем данные из строки ниже (например, для "Я" - допишем часы: "8Я". Казалось бы, можно и на "В" не проверять, но такая проверка нужна для случая Захаровой (3 строки вместо 4):
Код
ЕСЛИ(
ИНДЕКС(исх.!$1:$1048576; $A3+(E$2>15)*(2+($B3-4)); E$1) = "В"; "";
ИНДЕКС(исх.!$1:$1048576; $A3+(E$2>15)*(2+($B3-4)) + 1; E$1)) &

Выводим текущую ячейку табеля. Для ячейки E3: весь лист Исх (исх.!$1:$1048576) адресуется по строке = $A3+(E$2>15)*(2+($B3-4)) = 21 + (2 или 1 для чисел месяца > 15) = 21 (т.к. 1-е число) и по столбцу E$1 (12).
Получается, это срока 21 и столбец 12 на листе Исх (исх.!L21) :
Код
ИНДЕКС(исх.!$1:$1048576; $A3+(E$2>15)*(2+($B3-4)); E$1))

PS. Если у вас подобная задача (конвертации того или иного отчёта 1С) возникает достаточно часто, то для упрощения обновления можно воспользоваться моей программой "TXT-XLS конвертация". Правда, мне нужно будет сформировать файл-шаблон для заполнения. Вот мои объяснения и ссылка:
https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=162115&a...
Страницы: 1
Наверх