Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

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

 

= Мир MS Excel/Преобразование (транспонирование) массива Объединение строк - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Преобразование (транспонирование) массива Объединение строк (Сводные таблицы/Pivot Table)
Преобразование (транспонирование) массива Объединение строк
Flanker70 Дата: Вторник, 02.08.2022, 18:06 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Есть массив где в одном столбце названия в другом столбце коды. Необходимо строки с одинаковым название объединить в одну строку, а коды при этом транспонировать в столбцы. Пример в приложенном файле
К сообщению приложен файл: 6218866.xlsx(9.6 Kb)
 
Ответить
СообщениеЕсть массив где в одном столбце названия в другом столбце коды. Необходимо строки с одинаковым название объединить в одну строку, а коды при этом транспонировать в столбцы. Пример в приложенном файле

Автор - Flanker70
Дата добавления - 02.08.2022 в 18:06
msi2102 Дата: Вторник, 02.08.2022, 18:48 | Сообщение № 2
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 171
Репутация: 75 ±
Замечаний: 0% ±

Excel 2007
Можно Power Query, вот кнопочный вариант:
[vba]
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    Индекс = Table.AddIndexColumn(Источник, "Индекс", 0, 1, Int64.Type),
    Остаток = Table.TransformColumns(Индекс, {{"Индекс", each Number.Mod(_, 4), type number}}),
    Свод = Table.Pivot(Table.TransformColumnTypes(Остаток, {{"Индекс", type text}}, "ru-RU"), List.Distinct(Table.TransformColumnTypes(Остаток, {{"Индекс", type text}}, "ru-RU")[Индекс]), "Индекс", "КОД"),
    Имя = Table.RenameColumns(Свод,{{"0", "Код_1"}, {"1", "Код_2"}, {"2", "Код_3"}, {"3", "Код_4"}})
in
    Имя
[/vba]

или так:
[vba]
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    Группа = Table.Group(
        Источник,
        {"Наименование", "ИНН"},
        {{
            "Количество",
            (t) =>
            [filter = Table.Transpose(Table.LastN(Table.SelectColumns(t, "КОД"), 4))][filter]
        }}),
    Результат = Table.ExpandTableColumn(Группа, "Количество", {"Column1", "Column2", "Column3", "Column4"}, {"Код_1", "Код_2", "Код_3", "Код_4"})
in
    Результат
[/vba]

Если у Вас офис 2019 и выше то такими формулами:
Наименование
Код
=УНИК(A4:A15)

ИНН
Код
=ВПР(E9;$A$4:$C$15;2;0)

Код
Код
=ТРАНСП(ФИЛЬТР($C$4:$C$15;$A$4:$A$15=E9))
К сообщению приложен файл: 8206567.xlsx(26.2 Kb)


Сообщение отредактировал msi2102 - Вторник, 02.08.2022, 19:06
 
Ответить
СообщениеМожно Power Query, вот кнопочный вариант:
[vba]
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    Индекс = Table.AddIndexColumn(Источник, "Индекс", 0, 1, Int64.Type),
    Остаток = Table.TransformColumns(Индекс, {{"Индекс", each Number.Mod(_, 4), type number}}),
    Свод = Table.Pivot(Table.TransformColumnTypes(Остаток, {{"Индекс", type text}}, "ru-RU"), List.Distinct(Table.TransformColumnTypes(Остаток, {{"Индекс", type text}}, "ru-RU")[Индекс]), "Индекс", "КОД"),
    Имя = Table.RenameColumns(Свод,{{"0", "Код_1"}, {"1", "Код_2"}, {"2", "Код_3"}, {"3", "Код_4"}})
in
    Имя
[/vba]

или так:
[vba]
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    Группа = Table.Group(
        Источник,
        {"Наименование", "ИНН"},
        {{
            "Количество",
            (t) =>
            [filter = Table.Transpose(Table.LastN(Table.SelectColumns(t, "КОД"), 4))][filter]
        }}),
    Результат = Table.ExpandTableColumn(Группа, "Количество", {"Column1", "Column2", "Column3", "Column4"}, {"Код_1", "Код_2", "Код_3", "Код_4"})
in
    Результат
[/vba]

Если у Вас офис 2019 и выше то такими формулами:
Наименование
Код
=УНИК(A4:A15)

ИНН
Код
=ВПР(E9;$A$4:$C$15;2;0)

Код
Код
=ТРАНСП(ФИЛЬТР($C$4:$C$15;$A$4:$A$15=E9))

Автор - msi2102
Дата добавления - 02.08.2022 в 18:48
Flanker70 Дата: Среда, 03.08.2022, 09:18 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

msi2102, Спасибо! Буду пробовать. Я правильно понял, что так как у меня MS Excel 2010 то это решение только с применением макросов? Беда в том, что у меня на работе стоит запрет на применение макросов. Попробую в домашних условиях исправить файл. :)
 
Ответить
Сообщениеmsi2102, Спасибо! Буду пробовать. Я правильно понял, что так как у меня MS Excel 2010 то это решение только с применением макросов? Беда в том, что у меня на работе стоит запрет на применение макросов. Попробую в домашних условиях исправить файл. :)

Автор - Flanker70
Дата добавления - 03.08.2022 в 09:18
msi2102 Дата: Среда, 03.08.2022, 09:24 | Сообщение № 4
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 171
Репутация: 75 ±
Замечаний: 0% ±

Excel 2007
Макросом, так макросом:
[vba]
Код
Sub Макрос1()
    Dim arr, arr1, n As Long, m As Integer
    Set dic = CreateObject("Scripting.Dictionary")
    Set dic1 = CreateObject("Scripting.Dictionary")
    arr = Range("A4:C" & Cells(Rows.Count, 1).End(xlUp).Row).Value
    m = 0
    For n = 1 To UBound(arr)
        If Not dic.exists(arr(n, 1) & "|" & arr(n, 2)) Then Set dic(arr(n, 1) & "|" & arr(n, 2)) = CreateObject("Scripting.Dictionary")
        dic(arr(n, 1) & "|" & arr(n, 2))(dic(arr(n, 1) & "|" & arr(n, 2)).Count) = (arr(n, 3))
        If m < dic(arr(n, 1) & "|" & arr(n, 2)).Count Then m = dic(arr(n, 1) & "|" & arr(n, 2)).Count
    Next
    dic1.Add "Наименование", "Наименование"
    dic1.Add "ИНН", "ИНН"
    For n = 1 To m
        dic1.Add "Код_" & n, "Код_" & n
    Next
    ReDim arr1(1 To dic.Count, 1 To 2 + m)
    n = 0
    For Each y In dic
        n = n + 1
        arr1(n, 1) = Split(y, "|")(0)
        arr1(n, 2) = Split(y, "|")(1)
        m = 2
        For Each x In dic(y)
            m = m + 1
            arr1(n, m) = dic(y).Item(x)
        Next
    Next
    Cells(13, 5).Resize(1, dic1.Count) = dic1.Items
    Cells(14, 5).Resize(UBound(arr1), UBound(arr1, 2)) = arr1
End Sub
[/vba]
К сообщению приложен файл: 8206567.xlsm(38.4 Kb)
 
Ответить
СообщениеМакросом, так макросом:
[vba]
Код
Sub Макрос1()
    Dim arr, arr1, n As Long, m As Integer
    Set dic = CreateObject("Scripting.Dictionary")
    Set dic1 = CreateObject("Scripting.Dictionary")
    arr = Range("A4:C" & Cells(Rows.Count, 1).End(xlUp).Row).Value
    m = 0
    For n = 1 To UBound(arr)
        If Not dic.exists(arr(n, 1) & "|" & arr(n, 2)) Then Set dic(arr(n, 1) & "|" & arr(n, 2)) = CreateObject("Scripting.Dictionary")
        dic(arr(n, 1) & "|" & arr(n, 2))(dic(arr(n, 1) & "|" & arr(n, 2)).Count) = (arr(n, 3))
        If m < dic(arr(n, 1) & "|" & arr(n, 2)).Count Then m = dic(arr(n, 1) & "|" & arr(n, 2)).Count
    Next
    dic1.Add "Наименование", "Наименование"
    dic1.Add "ИНН", "ИНН"
    For n = 1 To m
        dic1.Add "Код_" & n, "Код_" & n
    Next
    ReDim arr1(1 To dic.Count, 1 To 2 + m)
    n = 0
    For Each y In dic
        n = n + 1
        arr1(n, 1) = Split(y, "|")(0)
        arr1(n, 2) = Split(y, "|")(1)
        m = 2
        For Each x In dic(y)
            m = m + 1
            arr1(n, m) = dic(y).Item(x)
        Next
    Next
    Cells(13, 5).Resize(1, dic1.Count) = dic1.Items
    Cells(14, 5).Resize(UBound(arr1), UBound(arr1, 2)) = arr1
End Sub
[/vba]

Автор - msi2102
Дата добавления - 03.08.2022 в 09:24
Flanker70 Дата: Среда, 03.08.2022, 13:31 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

msi2102, Еще раз спасибо! До домашнего компа не добрался. Проверить работу макроса не могу.
А на работе нашел Майкрософт Стандарт 2019 но там этих функций нет. Есть только ФИЛЬТР.XML. :(
 
Ответить
Сообщениеmsi2102, Еще раз спасибо! До домашнего компа не добрался. Проверить работу макроса не могу.
А на работе нашел Майкрософт Стандарт 2019 но там этих функций нет. Есть только ФИЛЬТР.XML. :(

Автор - Flanker70
Дата добавления - 03.08.2022 в 13:31
Nic70y Дата: Среда, 03.08.2022, 14:01 | Сообщение № 6
Группа: Друзья
Ранг: Экселист
Сообщений: 7646
Репутация: 1827 ±
Замечаний: 0% ±

Excel 2010
вариант с доп.столбцом
К сообщению приложен файл: 18.xlsx(12.1 Kb)


ЮMoney 41001841029809
 
Ответить
Сообщениевариант с доп.столбцом

Автор - Nic70y
Дата добавления - 03.08.2022 в 14:01
msi2102 Дата: Среда, 03.08.2022, 14:02 | Сообщение № 7
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 171
Репутация: 75 ±
Замечаний: 0% ±

Excel 2007
А на работе нашел Майкрософт Стандарт 2019
Значит Power Query точно должен быть
 
Ответить
Сообщение
А на работе нашел Майкрософт Стандарт 2019
Значит Power Query точно должен быть

Автор - msi2102
Дата добавления - 03.08.2022 в 14:02
Egyptian Дата: Среда, 03.08.2022, 14:11 | Сообщение № 8
Группа: Проверенные
Ранг: Обитатель
Сообщений: 372
Репутация: 126 ±
Замечаний: 0% ±

Excel 2013/2016
на работе нашел Майкрософт Стандарт 2019 но там этих функций нет

И не может быть. UNIQUE, FILTER и др. - функции динамических массивов, которые поддерживаются только в 365-ом офисе (по подписке) и 2021. С другой стороны, если у вас 2010, то вы можете скачать и установить бесплатную надстройку Power Query.Очень полезная вещь. Варианты решения в PQ Сергей выложил тремя постами выше. Также во вложении мой вариант со старыми функциями.
К сообщению приложен файл: 8809947.xlsx(12.2 Kb)


Сообщение отредактировал Egyptian - Среда, 03.08.2022, 14:13
 
Ответить
Сообщение
на работе нашел Майкрософт Стандарт 2019 но там этих функций нет

И не может быть. UNIQUE, FILTER и др. - функции динамических массивов, которые поддерживаются только в 365-ом офисе (по подписке) и 2021. С другой стороны, если у вас 2010, то вы можете скачать и установить бесплатную надстройку Power Query.Очень полезная вещь. Варианты решения в PQ Сергей выложил тремя постами выше. Также во вложении мой вариант со старыми функциями.

Автор - Egyptian
Дата добавления - 03.08.2022 в 14:11
Flanker70 Дата: Среда, 03.08.2022, 16:07 | Сообщение № 9
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

msi2102, Спасибо огромное!!! Все получилось! Макрос работает!!!
 
Ответить
Сообщениеmsi2102, Спасибо огромное!!! Все получилось! Макрос работает!!!

Автор - Flanker70
Дата добавления - 03.08.2022 в 16:07
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Преобразование (транспонирование) массива Объединение строк (Сводные таблицы/Pivot Table)
  • Страница 1 из 1
  • 1
Поиск:

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