Как сделать плоскую таблицу из сгруппированных блоков? - Мир MS Excel

Регистрация

Напомнить пароль

= Мир MS Excel/Как сделать плоскую таблицу из сгруппированных блоков? - Мир MS Excel

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Как сделать плоскую таблицу из сгруппированных блоков? (Формулы/Formulas)

Как сделать плоскую таблицу из сгруппированных блоков?

Дата: Среда, 13.02.2019, 20:07 | Сообщение № 1

Группа: Пользователи

Ранг: Новичок

Сообщений: 12

Репутация: 0 ±

Замечаний: 0% ±

Excel 2010

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

ПРОИЗВОДИТЕЛЬ 1
Модель 1
Размер 1 модели 1
Размер 2 модели 1
...
Размер N модели 1
Модель 2
Размер 1 модели 2
...
Размер N модели 2

ПРОИЗВОДИТЕЛЬ 2
и т.д.

Всю голову сломал как сделать макрос, который из этой таблицы делал бы обычную плоскую вида:
ПРОИЗВОДИТЕЛЬ 1 Модель 1Размер 1 модели 1
ПРОИЗВОДИТЕЛЬ 1 Модель 1Размер 2 модели 1
...
ПРОИЗВОДИТЕЛЬ 1 Модель 1Размер N модели 1
ПРОИЗВОДИТЕЛЬ 1 Модель 2Размер 1 модели 2
...
ПРОИЗВОДИТЕЛЬ 1 Модель 2Размер N модели 2
ПРОИЗВОДИТЕЛЬ 2 Модель 1Размер 1 модели 1
и т.д.

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

К сообщению приложен файл: 3305669.xls (30.0 Kb)

Сообщение отредактировал Neuro75 - Четверг, 14.02.2019, 00:59

Ответить

Дата: Четверг, 14.02.2019, 07:54 | Сообщение № 2

Группа: Друзья

Ранг: Ветеран

Сообщений: 648

Репутация: 96 ±

Замечаний: 0% ±

Excel 2010, 365

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

Разработчик Битрикс24 php, Google Apps Script, VBA Excel Windows/Mac

Ответить

Дата: Четверг, 14.02.2019, 08:26 | Сообщение № 3

Группа: Друзья

Ранг: Старожил

Сообщений: 1394

Репутация: 320 ±

Замечаний: 0% ±

Excel 2010

Доброе утро!

Вариант обработки формулами (см. желтые столбцы).

К сообщению приложен файл: 2895862.xls (42.0 Kb)

QIWI 9173973973

Ответить

Дата: Четверг, 14.02.2019, 12:06 | Сообщение № 4

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2941

Репутация: 526 ±

Замечаний: 0% ±

Ответить

Дата: Четверг, 14.02.2019, 13:35 | Сообщение № 5

Группа: Пользователи

Ранг: Новичок

Сообщений: 12

Репутация: 0 ±

Замечаний: 0% ±

Excel 2010

Огромное спасибо, что отозвались, буду пробовать предложенные варианты.

Ответить

Дата: Пятница, 15.02.2019, 00:47 | Сообщение № 6

Группа: Друзья

Ранг: Старожил

Сообщений: 2347

Репутация: 989 ±

Замечаний: 0% ±

Excel 2007,2010,2013

еще один вариант в PowerQuery с допстолбцом

Код

получить.ячейку(28)

[vba]

Код

let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Транспонированная таблица" = Table.Transpose(Источник),
    #"Заполнение вниз" = Table.FillDown(#"Транспонированная таблица",{"Column1"}),
    #"Объединенные столбцы" = Table.CombineColumns(#"Заполнение вниз",{"Column1", "Column2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None)," "),
    #"Очищенный текст" = Table.TransformColumns(#"Объединенные столбцы",{{" ", Text.Trim, type text}}),
    #"Транспонированная таблица1" = Table.Transpose(#"Очищенный текст"),
    #"Повышенные заголовки" = Table.PromoteHeaders(#"Транспонированная таблица1", [PromoteAllScalars=true]),
    #"Добавлен индекс" = Table.AddIndexColumn(#"Повышенные заголовки", "Индекс", 0, 1),
    #"Сгруппированные строки1" = Table.Group(#"Добавлен индекс", {"Level"}, {{"tab", each _, type table}}),
    #"Сведенный столбец" = Table.Pivot(Table.TransformColumnTypes(#"Сгруппированные строки1", {{"Level", type text}}, "ru-RU"), List.Distinct(Table.TransformColumnTypes(#"Сгруппированные строки1", {{"Level", type text}}, "ru-RU")[Level]), "Level", "tab"),
    fn=(a as table, b as any,c as text,d as text, f as any,optional e as list) as table =>let
        g = Table.AddIndexColumn(a, "Индекс", 0, 1),
        h=Table.AddColumn(g, b&"+1",each try Record.Field(g{[Индекс]+1},b) otherwise null) ,
        i=Table.ColumnNames(Record.Field(g{0},d)){0},
        e=if e is null then {i,"Индекс"} else e,
        f=List.ReplaceValue(List.ReplaceValue(e, i, f, Replacer.ReplaceText), "Индекс", c, Replacer.ReplaceText),
        j=Table.ExpandTableColumn(h, d, e, f),
        k=b&"+1",
        l=try Table.SelectRows(j, each (Record.Field(_,c) > Record.Field(_,b) ) and (if Record.Field(_,k)  is null then true else Record.Field(_,k)>Record.Field(_,c))) otherwise j
    in Table.RemoveColumns(if b<>"" then l else j,List.RemoveItems({b,"Индекс",k},{""})),
    expand=fn(fn(fn(#"Сведенный столбец","","i1","2","Категория"),"i1","i2","3","Производитель"),"i2","i3","4","Модель"),
    expand1=fn(expand,"i3","i4","5","Размер",Table.ColumnNames(#"Сведенный столбец"{0}[1])),
    result=Table.RemoveColumns(expand1,{"1","Level","i4"})
in
    result

[/vba]

еще один вариант в PowerQuery с допстолбцом

Код

получить.ячейку(28)

[vba]

Код

let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Транспонированная таблица" = Table.Transpose(Источник),
    #"Заполнение вниз" = Table.FillDown(#"Транспонированная таблица",{"Column1"}),
    #"Объединенные столбцы" = Table.CombineColumns(#"Заполнение вниз",{"Column1", "Column2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None)," "),
    #"Очищенный текст" = Table.TransformColumns(#"Объединенные столбцы",{{" ", Text.Trim, type text}}),
    #"Транспонированная таблица1" = Table.Transpose(#"Очищенный текст"),
    #"Повышенные заголовки" = Table.PromoteHeaders(#"Транспонированная таблица1", [PromoteAllScalars=true]),
    #"Добавлен индекс" = Table.AddIndexColumn(#"Повышенные заголовки", "Индекс", 0, 1),
    #"Сгруппированные строки1" = Table.Group(#"Добавлен индекс", {"Level"}, {{"tab", each _, type table}}),
    #"Сведенный столбец" = Table.Pivot(Table.TransformColumnTypes(#"Сгруппированные строки1", {{"Level", type text}}, "ru-RU"), List.Distinct(Table.TransformColumnTypes(#"Сгруппированные строки1", {{"Level", type text}}, "ru-RU")[Level]), "Level", "tab"),
    fn=(a as table, b as any,c as text,d as text, f as any,optional e as list) as table =>let
        g = Table.AddIndexColumn(a, "Индекс", 0, 1),
        h=Table.AddColumn(g, b&"+1",each try Record.Field(g{[Индекс]+1},b) otherwise null) ,
        i=Table.ColumnNames(Record.Field(g{0},d)){0},
        e=if e is null then {i,"Индекс"} else e,
        f=List.ReplaceValue(List.ReplaceValue(e, i, f, Replacer.ReplaceText), "Индекс", c, Replacer.ReplaceText),
        j=Table.ExpandTableColumn(h, d, e, f),
        k=b&"+1",
        l=try Table.SelectRows(j, each (Record.Field(_,c) > Record.Field(_,b) ) and (if Record.Field(_,k)  is null then true else Record.Field(_,k)>Record.Field(_,c))) otherwise j
    in Table.RemoveColumns(if b<>"" then l else j,List.RemoveItems({b,"Индекс",k},{""})),
    expand=fn(fn(fn(#"Сведенный столбец","","i1","2","Категория"),"i1","i2","3","Производитель"),"i2","i3","4","Модель"),
    expand1=fn(expand,"i3","i4","5","Размер",Table.ColumnNames(#"Сведенный столбец"{0}[1])),
    result=Table.RemoveColumns(expand1,{"1","Level","i4"})
in
    result

[/vba]krosav4ig

К сообщению приложен файл: 3305669.xlsm (28.9 Kb)

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Ответить

Сообщениееще один вариант в PowerQuery с допстолбцом

Код

получить.ячейку(28)

[vba]

Код

let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Транспонированная таблица" = Table.Transpose(Источник),
    #"Заполнение вниз" = Table.FillDown(#"Транспонированная таблица",{"Column1"}),
    #"Объединенные столбцы" = Table.CombineColumns(#"Заполнение вниз",{"Column1", "Column2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None)," "),
    #"Очищенный текст" = Table.TransformColumns(#"Объединенные столбцы",{{" ", Text.Trim, type text}}),
    #"Транспонированная таблица1" = Table.Transpose(#"Очищенный текст"),
    #"Повышенные заголовки" = Table.PromoteHeaders(#"Транспонированная таблица1", [PromoteAllScalars=true]),
    #"Добавлен индекс" = Table.AddIndexColumn(#"Повышенные заголовки", "Индекс", 0, 1),
    #"Сгруппированные строки1" = Table.Group(#"Добавлен индекс", {"Level"}, {{"tab", each _, type table}}),
    #"Сведенный столбец" = Table.Pivot(Table.TransformColumnTypes(#"Сгруппированные строки1", {{"Level", type text}}, "ru-RU"), List.Distinct(Table.TransformColumnTypes(#"Сгруппированные строки1", {{"Level", type text}}, "ru-RU")[Level]), "Level", "tab"),
    fn=(a as table, b as any,c as text,d as text, f as any,optional e as list) as table =>let
        g = Table.AddIndexColumn(a, "Индекс", 0, 1),
        h=Table.AddColumn(g, b&"+1",each try Record.Field(g{[Индекс]+1},b) otherwise null) ,
        i=Table.ColumnNames(Record.Field(g{0},d)){0},
        e=if e is null then {i,"Индекс"} else e,
        f=List.ReplaceValue(List.ReplaceValue(e, i, f, Replacer.ReplaceText), "Индекс", c, Replacer.ReplaceText),
        j=Table.ExpandTableColumn(h, d, e, f),
        k=b&"+1",
        l=try Table.SelectRows(j, each (Record.Field(_,c) > Record.Field(_,b) ) and (if Record.Field(_,k)  is null then true else Record.Field(_,k)>Record.Field(_,c))) otherwise j
    in Table.RemoveColumns(if b<>"" then l else j,List.RemoveItems({b,"Индекс",k},{""})),
    expand=fn(fn(fn(#"Сведенный столбец","","i1","2","Категория"),"i1","i2","3","Производитель"),"i2","i3","4","Модель"),
    expand1=fn(expand,"i3","i4","5","Размер",Table.ColumnNames(#"Сведенный столбец"{0}[1])),
    result=Table.RemoveColumns(expand1,{"1","Level","i4"})
in
    result

[/vba]

Автор - krosav4ig
Дата добавления - 15.02.2019 в 00:47

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Как сделать плоскую таблицу из сгруппированных блоков? (Формулы/Formulas)

Страница 1 из 1
1

© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!