Друзья, приветствую, необходима помощь вот в каком вопросе: Есть файл (более 100 листов) данные однотипные. Необходимо макросом перенести их на один лист и добавить столбец, который будет содержать дату из названия листа. Есть один момент: названия листов имеют названия типа: 15,02 -это означает 15 февраля. Год для всех 2017.
Буду благодарен за любую помощь. Прикрепляю пример с желаемым результатом и структурой данных
Илья Демид, Начинать можно с ячейки A4 шапка не нужна. В идеале VBA чтобы потом я под другую структуру подкорректировать смог. в PowerQuery вообще не знаю ничего
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
Сори VBA не смогу быстро сделать, пока вот решение через PQ На вкладке данные жмем - Создать запрос - Из других источников - пустой запрос. В новом окне влкадка Просмотр расширенный редактор, удалить что там есть и написать. Во 2 строке вставить путь где лежит файл
Код
let
Источник = Excel.Workbook(File.Contents("СЮДА ВСТАВЬТЕ ПУТЬ К ФАЙЛУ"), null, true),
#"Удаленные нижние строки" = Table.RemoveLastN(Источник,1),
#"Удаленные столбцы" = Table.RemoveColumns(#"Удаленные нижние строки",{"Item", "Kind", "Hidden"}),
#"Добавлен пользовательский объект" = Table.AddColumn(#"Удаленные столбцы", "Пользовательская", each 2017),
#"Замененное значение" = Table.ReplaceValue(#"Добавлен пользовательский объект",",","-",Replacer.ReplaceText,{"Name"}),
#"Измененный тип" = Table.TransformColumnTypes(#"Замененное значение",{{"Пользовательская", type text}}),
#"Добавлен пользовательский объект1" = Table.AddColumn(#"Измененный тип", "Пользовательская.1", each [Name]&"-"&[Пользовательская]),
#"Измененный тип1" = Table.TransformColumnTypes(#"Добавлен пользовательский объект1",{{"Пользовательская.1", type date}}),
#"Переименованные столбцы" = Table.RenameColumns(#"Измененный тип1",{{"Пользовательская.1", "Period"}}),
#"Удаленные столбцы1" = Table.RemoveColumns(#"Переименованные столбцы",{"Name", "Пользовательская"}),
#"Развернутый элемент Data" = Table.ExpandTableColumn(#"Удаленные столбцы1", "Data", {"Column1", "Column2"}, {"Data.Column1", "Data.Column2"}),
#"Переименованные столбцы1" = Table.RenameColumns(#"Развернутый элемент Data",{{"Data.Column1", "Index"}, {"Data.Column2", "Name"}}),
#"Строки с примененным фильтром" = Table.SelectRows(#"Переименованные столбцы1", each ([Index] <> null and [Index] <> " Итого выдано: " and [Index] <> " не выдано :" and [Index] <> "индексы"))
in
#"Строки с примененным фильтром"
Z, PLEX не установлен, подсказки в ссылках не помогли, в приемах нет статьи описывающей решение данного вопроса на VBA. Не получается даже близко придумать алгоритм того, как составить полноценную дату из такого названия листа и заставить все это работать.
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
Z, Я, конечно, благодарен Вам за то, что уделяете время моему вопросу. Но как выглядел весь остальной путь до процедуры поиска и замены? Если Вы просто решили ткнуть меня носом в даты- ок, у Вас получилось.
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
Много лишнего правда, после заменить "-" на "-" чтобы формат взялся. из примера удалить последний лист
Код
Sub test()
Dim lastrow, lastrows As Long
Dim sN As Worksheet
Dim a As String
Sheets(1).Activate
ActiveWorkbook.Sheets.Add
Sheets(1).Name = "Final"
Set sN = Sheets("Final")
For i = 2 To Worksheets.Count
Sheets(1).Activate
lastrow = Cells(sN.Rows.Count, 1).End(xlUp).Row + 1
Sheets(i).Activate
a = ActiveSheet.Name
lastrows = Cells(Rows.Count, 1).End(xlUp).Row
For j = 1 To lastrows
Cells(j, 3) = a
Next
Range(Cells(4, 1), Cells(lastrows, 3)).Copy
sN.Cells(lastrow, 1).PasteSpecial (xlPasteAll)
Next
sN.Activate
sN.Range("C:C").Replace ",", "-"
lastrow = Cells(sN.Rows.Count, 3).End(xlUp).Row
For i = 2 To lastrow
sN.Cells(i, 3) = Cells(i, 3) & "-2017"
Next
End Sub
Коллеги, в продолжение решение PowerQuery. После только от мусора отфильтровать. Хм.... Я вот делал запрос прямо в файле примере, на который же в запросе ссылаюсь, а вот не будет ли перебоев при такой работе ?
Код
let
Source = Excel.Workbook(File.Contents("ПУТЬ К ФАЙЛУ" ), null, true),
#"Строки с примененным фильтром" = Table.SelectRows(Source, each ([Name] <> "желаемый результат")),
RemovedOtherColumns = Table.SelectColumns(#"Строки с примененным фильтром",{"Data", "Name","Kind"}),
OnlySheets = Table.SelectRows(RemovedOtherColumns, each Text.Contains([Kind], "Sheet")),
Rename = Table.ReplaceValue(OnlySheets, ",","-", Replacer.ReplaceText,{"Name"}),
AddCol = Table.AddColumn(Rename, "Date",each[Name]&"-2017"),
DeleteColumns = Table.SelectColumns(AddCol,{"Data","Date"}),
ColumnsName = Table.ColumnNames(DeleteColumns{0}[Data]),
ExpandedTable = Table.ExpandTableColumn(DeleteColumns, "Data", ColumnsName)
in
ExpandedTable