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

Вход

Регистрация

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

 

= Мир MS Excel/Распарсить данные ячейки таблицы - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Распарсить данные ячейки таблицы (Формулы/Formulas)
Распарсить данные ячейки таблицы
AngelOfLegend Дата: Пятница, 07.10.2016, 19:43 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Необходимо по особенному распарсить таблицу. Что только не пробовал (разделитель по запятым в том числе). Это необходимо для дальнейшей работы в PowerBI. Массив данных очень большой, просто руками не сделать :'(

Буду очень благодарен за идеи!
К сообщению приложен файл: 6411264.xls(26Kb)


Сообщение отредактировал AngelOfLegend - Пятница, 07.10.2016, 20:04
 
Ответить
СообщениеНеобходимо по особенному распарсить таблицу. Что только не пробовал (разделитель по запятым в том числе). Это необходимо для дальнейшей работы в PowerBI. Массив данных очень большой, просто руками не сделать :'(

Буду очень благодарен за идеи!

Автор - AngelOfLegend
Дата добавления - 07.10.2016 в 19:43
Manyasha Дата: Пятница, 07.10.2016, 19:47 | Сообщение № 2
Группа: Модераторы
Ранг: Старожил
Сообщений: 1587
Репутация: 669 ±
Замечаний: 0% ±

Excel 2007, 2010
AngelOfLegend, приложите пример в файле.

Характеристики всегда разделены запятыми или могут быть другие варианты?


marinamorozova_box@mail.ru
ЯД: 410013299366744 WM: R193491431804


Сообщение отредактировал Manyasha - Пятница, 07.10.2016, 19:48
 
Ответить
СообщениеAngelOfLegend, приложите пример в файле.

Характеристики всегда разделены запятыми или могут быть другие варианты?

Автор - Manyasha
Дата добавления - 07.10.2016 в 19:47
AngelOfLegend Дата: Пятница, 07.10.2016, 19:54 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Разделены только запятыми
 
Ответить
СообщениеРазделены только запятыми

Автор - AngelOfLegend
Дата добавления - 07.10.2016 в 19:54
AngelOfLegend Дата: Пятница, 07.10.2016, 19:54 | Сообщение № 4
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Хотя, если это спасет дело, можно попробовать заменить на пробел или точку с запятой :)
 
Ответить
СообщениеХотя, если это спасет дело, можно попробовать заменить на пробел или точку с запятой :)

Автор - AngelOfLegend
Дата добавления - 07.10.2016 в 19:54
Udik Дата: Пятница, 07.10.2016, 19:55 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 1215
Репутация: 153 ±
Замечаний: 0% ±

Excel 2013
лучше всего макросом, но нужен файл


вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com
 
Ответить
Сообщениелучше всего макросом, но нужен файл

Автор - Udik
Дата добавления - 07.10.2016 в 19:55
AngelOfLegend Дата: Пятница, 07.10.2016, 20:02 | Сообщение № 6
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Пример бросил в первом сообщение (скажем так, он детский). Могу показать более детально с чем имею дело другим примером


Сообщение отредактировал AngelOfLegend - Пятница, 07.10.2016, 20:05
 
Ответить
СообщениеПример бросил в первом сообщение (скажем так, он детский). Могу показать более детально с чем имею дело другим примером

Автор - AngelOfLegend
Дата добавления - 07.10.2016 в 20:02
Manyasha Дата: Пятница, 07.10.2016, 20:10 | Сообщение № 7
Группа: Модераторы
Ранг: Старожил
Сообщений: 1587
Репутация: 669 ±
Замечаний: 0% ±

Excel 2007, 2010
AngelOfLegend, вариант макросом:
[vba]
Код
Sub test()
    Dim lr&, data, i&, j&, temp, r&
    [d1].CurrentRegion.Offset(1).ClearContents
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    data = Range("a2:b" & lr).Value
    r = 2
    For i = 1 To UBound(data)
        temp = Split(data(i, 2), ",")
        For j = 0 To UBound(temp)
            Cells(r, "d") = data(i, 1)
            Cells(r, "e") = temp(j)
            r = r + 1
        Next j
    Next i
End Sub
[/vba]
К сообщению приложен файл: primer.xlsm(17Kb)


marinamorozova_box@mail.ru
ЯД: 410013299366744 WM: R193491431804
 
Ответить
СообщениеAngelOfLegend, вариант макросом:
[vba]
Код
Sub test()
    Dim lr&, data, i&, j&, temp, r&
    [d1].CurrentRegion.Offset(1).ClearContents
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    data = Range("a2:b" & lr).Value
    r = 2
    For i = 1 To UBound(data)
        temp = Split(data(i, 2), ",")
        For j = 0 To UBound(temp)
            Cells(r, "d") = data(i, 1)
            Cells(r, "e") = temp(j)
            r = r + 1
        Next j
    Next i
End Sub
[/vba]

Автор - Manyasha
Дата добавления - 07.10.2016 в 20:10
Karataev Дата: Пятница, 07.10.2016, 20:11 | Сообщение № 8
Группа: Проверенные
Ранг: Ветеран
Сообщений: 643
Репутация: 226 ±
Замечаний: 0% ±

Excel
Вариант с макросом. После работы макроса будет создан новый лист.
[vba]
Код
Sub Парсинг()

    Dim shSrc As Worksheet, shRes As Worksheet, arr1(), arr2
    Dim lr As Long, i As Long
    
    Application.ScreenUpdating = False
    
    Set shSrc = ActiveSheet
    Set shRes = Worksheets.Add(After:=shSrc)
    
    lr = shSrc.Columns("A").Find(What:="*", LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False _
        , SearchFormat:=False).Row
    arr1() = shSrc.Range("A1:B" & lr).Value
    
    shRes.Range("A1:B1").Value = shSrc.Range("A1:B1").Value
    
    For i = 2 To UBound(arr1)
        lr = shRes.UsedRange.Row + shRes.UsedRange.Rows.Count
        arr2 = Split(arr1(i, 2), ",")
        With shRes.Rows(lr).Resize(UBound(arr2) + 1)
            .Columns("A").Value = arr1(i, 1)
            .Columns("B").Value = WorksheetFunction.Transpose(arr2)
        End With
    Next
    
    Application.ScreenUpdating = True

End Sub
[/vba]


 
Ответить
СообщениеВариант с макросом. После работы макроса будет создан новый лист.
[vba]
Код
Sub Парсинг()

    Dim shSrc As Worksheet, shRes As Worksheet, arr1(), arr2
    Dim lr As Long, i As Long
    
    Application.ScreenUpdating = False
    
    Set shSrc = ActiveSheet
    Set shRes = Worksheets.Add(After:=shSrc)
    
    lr = shSrc.Columns("A").Find(What:="*", LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False _
        , SearchFormat:=False).Row
    arr1() = shSrc.Range("A1:B" & lr).Value
    
    shRes.Range("A1:B1").Value = shSrc.Range("A1:B1").Value
    
    For i = 2 To UBound(arr1)
        lr = shRes.UsedRange.Row + shRes.UsedRange.Rows.Count
        arr2 = Split(arr1(i, 2), ",")
        With shRes.Rows(lr).Resize(UBound(arr2) + 1)
            .Columns("A").Value = arr1(i, 1)
            .Columns("B").Value = WorksheetFunction.Transpose(arr2)
        End With
    Next
    
    Application.ScreenUpdating = True

End Sub
[/vba]

Автор - Karataev
Дата добавления - 07.10.2016 в 20:11
AngelOfLegend Дата: Пятница, 07.10.2016, 20:15 | Сообщение № 9
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
БЛАГОДАРЮ! Я ДИКО ДОВОЛЕН! ))) Сейчас буду пробовать на рабочем варианте )))) hands
 
Ответить
СообщениеБЛАГОДАРЮ! Я ДИКО ДОВОЛЕН! ))) Сейчас буду пробовать на рабочем варианте )))) hands

Автор - AngelOfLegend
Дата добавления - 07.10.2016 в 20:15
Manyasha Дата: Пятница, 07.10.2016, 20:46 | Сообщение № 10
Группа: Модераторы
Ранг: Старожил
Сообщений: 1587
Репутация: 669 ±
Замечаний: 0% ±

Excel 2007, 2010
AngelOfLegend, вопросы по своей теме задавайте здесь, не нужно в личку писать.

Для того, чтобы перенести макрос в другой файл, нажмите alt+f11 (или вкладка Разработчик-Visual Basic) - Вы в редакторе VBA. В модуле module1 есть макрос test. Его нужно скопировать в модуль рабочего файла. Для этого откройте редактор VBA рабочего файла - щелкните правой кнопкой мыши (пкм) по VB-проекту книги (см. скрин)

нажмите insert-module - 2-м щелчком откройте созданный модуль - вставьте код макроса.

Или вариант попроще: модуль с макросом (все там же, в редакторе VBA) можно перетащить мышкой из одного VB-проекта в другой.

Чтобы привязать макрос к кнопке, щелкаете пкм по кнопке-назначить макрос-выбираете test (ну или как Вы его там назовете).

[p.s.]И вариант Karataevа посмотрите, он тоже старался![/p.s.]
К сообщению приложен файл: 5654046.jpg(18Kb)


marinamorozova_box@mail.ru
ЯД: 410013299366744 WM: R193491431804
 
Ответить
СообщениеAngelOfLegend, вопросы по своей теме задавайте здесь, не нужно в личку писать.

Для того, чтобы перенести макрос в другой файл, нажмите alt+f11 (или вкладка Разработчик-Visual Basic) - Вы в редакторе VBA. В модуле module1 есть макрос test. Его нужно скопировать в модуль рабочего файла. Для этого откройте редактор VBA рабочего файла - щелкните правой кнопкой мыши (пкм) по VB-проекту книги (см. скрин)

нажмите insert-module - 2-м щелчком откройте созданный модуль - вставьте код макроса.

Или вариант попроще: модуль с макросом (все там же, в редакторе VBA) можно перетащить мышкой из одного VB-проекта в другой.

Чтобы привязать макрос к кнопке, щелкаете пкм по кнопке-назначить макрос-выбираете test (ну или как Вы его там назовете).

[p.s.]И вариант Karataevа посмотрите, он тоже старался![/p.s.]

Автор - Manyasha
Дата добавления - 07.10.2016 в 20:46
AngelOfLegend Дата: Пятница, 07.10.2016, 20:55 | Сообщение № 11
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
О Боги, Вы не представляете как мне помогаете! Да, я вариант Karataevа тоже себе сразу скопировал ( что бы не потерять).
Я уже начал изучать работу с макросами) Спасибо и за эту помощь. Не могу уже сидеть на работе (часов 13 просидел). Буду пробовать переносить в понедельник.
Как я могу Вас отблагодарить(очень-очень хочу) ?
 
Ответить
СообщениеО Боги, Вы не представляете как мне помогаете! Да, я вариант Karataevа тоже себе сразу скопировал ( что бы не потерять).
Я уже начал изучать работу с макросами) Спасибо и за эту помощь. Не могу уже сидеть на работе (часов 13 просидел). Буду пробовать переносить в понедельник.
Как я могу Вас отблагодарить(очень-очень хочу) ?

Автор - AngelOfLegend
Дата добавления - 07.10.2016 в 20:55
Manyasha Дата: Пятница, 07.10.2016, 21:01 | Сообщение № 12
Группа: Модераторы
Ранг: Старожил
Сообщений: 1587
Репутация: 669 ±
Замечаний: 0% ±

Excel 2007, 2010
Как я могу Вас отблагодарить(очень-очень хочу) ?

реквизиты в подписи :)


marinamorozova_box@mail.ru
ЯД: 410013299366744 WM: R193491431804
 
Ответить
Сообщение
Как я могу Вас отблагодарить(очень-очень хочу) ?

реквизиты в подписи :)

Автор - Manyasha
Дата добавления - 07.10.2016 в 21:01
krosav4ig Дата: Суббота, 08.10.2016, 00:36 | Сообщение № 13
Группа: Друзья
Ранг: Старожил
Сообщений: 1349
Репутация: 547 ±
Замечаний: 0% ±

Excel 2007, 2013
для разнообразия, UDF в Power Query
SplitAndExpand
[vba]
Код
(Таблица as table,НомСтолб as number, Разделитель as text) as table =>
let
    Столбец = List.Range(Table.ColumnNames(Таблица),НомСтолб,1){0},
    fn = Splitter.SplitTextByDelimiter(Разделитель, QuoteStyle.None),
    Разделить = Table.TransformColumns(Таблица,{Столбец, fn}),
    Результат = Table.ExpandListColumn(Разделить,Столбец)
in
    Результат
[/vba]
Использование в запросе
[vba]
Код
let
    Источник = SplitAndExpand(Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],1,",")
in
    Источник
[/vba]
К сообщению приложен файл: 2025973.xls(41Kb)


(_)Õvõ(_)
 
Ответить
Сообщениедля разнообразия, UDF в Power Query
SplitAndExpand
[vba]
Код
(Таблица as table,НомСтолб as number, Разделитель as text) as table =>
let
    Столбец = List.Range(Table.ColumnNames(Таблица),НомСтолб,1){0},
    fn = Splitter.SplitTextByDelimiter(Разделитель, QuoteStyle.None),
    Разделить = Table.TransformColumns(Таблица,{Столбец, fn}),
    Результат = Table.ExpandListColumn(Разделить,Столбец)
in
    Результат
[/vba]
Использование в запросе
[vba]
Код
let
    Источник = SplitAndExpand(Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],1,",")
in
    Источник
[/vba]

Автор - krosav4ig
Дата добавления - 08.10.2016 в 00:36
Nic70y Дата: Суббота, 08.10.2016, 11:47 | Сообщение № 14
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3483
Репутация: 722 ±
Замечаний: 0% ±

Excel 2013
лучше всего макросом
, формулами смешнее!
К сообщению приложен файл: 5419096.xls(30Kb)


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)
 
Ответить
Сообщение
лучше всего макросом
, формулами смешнее!

Автор - Nic70y
Дата добавления - 08.10.2016 в 11:47
AngelOfLegend Дата: Понедельник, 10.10.2016, 15:02 | Сообщение № 15
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
В общем, когда я начал подставлять все варианты в рабочий afqk, начались проблемы? ничего и никаким вариантом (макрос, формула, Query) =(
Не учел что бывают пустые колонки, и пробелы между словами в одной из характеристик. =(
К сообщению приложен файл: primer_macros.xlsm(17Kb)
 
Ответить
СообщениеВ общем, когда я начал подставлять все варианты в рабочий afqk, начались проблемы? ничего и никаким вариантом (макрос, формула, Query) =(
Не учел что бывают пустые колонки, и пробелы между словами в одной из характеристик. =(

Автор - AngelOfLegend
Дата добавления - 10.10.2016 в 15:02
Manyasha Дата: Понедельник, 10.10.2016, 19:14 | Сообщение № 16
Группа: Модераторы
Ранг: Старожил
Сообщений: 1587
Репутация: 669 ±
Замечаний: 0% ±

Excel 2007, 2010
AngelOfLegend, а что должно быть там, где пустые ячейки?
С пробелами тоже не поняла, нарисуйте в файле, какой должен быть результат для Вашего примера.


marinamorozova_box@mail.ru
ЯД: 410013299366744 WM: R193491431804
 
Ответить
СообщениеAngelOfLegend, а что должно быть там, где пустые ячейки?
С пробелами тоже не поняла, нарисуйте в файле, какой должен быть результат для Вашего примера.

Автор - Manyasha
Дата добавления - 10.10.2016 в 19:14
AngelOfLegend Дата: Вторник, 11.10.2016, 09:36 | Сообщение № 17
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Нужно что бы получился такой результат:
:'(
К сообщению приложен файл: primer_macros_n.xlsm(17Kb)
 
Ответить
СообщениеНужно что бы получился такой результат:
:'(

Автор - AngelOfLegend
Дата добавления - 11.10.2016 в 09:36
Manyasha Дата: Вторник, 11.10.2016, 10:26 | Сообщение № 18
Группа: Модераторы
Ранг: Старожил
Сообщений: 1587
Репутация: 669 ±
Замечаний: 0% ±

Excel 2007, 2010
AngelOfLegend, добавьте условие для пустой характеристики:
[vba]
Код
Sub test()
    Dim lr&, data, i&, j&, temp, r&
    [d1].CurrentRegion.Offset(1).ClearContents
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    data = Range("a2:b" & lr).Value
    r = 2
    For i = 1 To UBound(data)
        If data(i, 2) = "" Then
            Cells(r, "d") = data(i, 1)
            r = r + 1
        Else
            temp = Split(data(i, 2), ",")
            For j = 0 To UBound(temp)
                Cells(r, "d") = data(i, 1)
                Cells(r, "e") = temp(j)
                r = r + 1
            Next j
        End If
    Next i
End Sub
[/vba]
К сообщению приложен файл: 7805015.xlsm(17Kb)


marinamorozova_box@mail.ru
ЯД: 410013299366744 WM: R193491431804
 
Ответить
СообщениеAngelOfLegend, добавьте условие для пустой характеристики:
[vba]
Код
Sub test()
    Dim lr&, data, i&, j&, temp, r&
    [d1].CurrentRegion.Offset(1).ClearContents
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    data = Range("a2:b" & lr).Value
    r = 2
    For i = 1 To UBound(data)
        If data(i, 2) = "" Then
            Cells(r, "d") = data(i, 1)
            r = r + 1
        Else
            temp = Split(data(i, 2), ",")
            For j = 0 To UBound(temp)
                Cells(r, "d") = data(i, 1)
                Cells(r, "e") = temp(j)
                r = r + 1
            Next j
        End If
    Next i
End Sub
[/vba]

Автор - Manyasha
Дата добавления - 11.10.2016 в 10:26
krosav4ig Дата: Вторник, 11.10.2016, 17:54 | Сообщение № 19
Группа: Друзья
Ранг: Старожил
Сообщений: 1349
Репутация: 547 ±
Замечаний: 0% ±

Excel 2007, 2013
Нужно что бы получился такой результат

AngelOfLegend, ну дык если в ваш последний файл перенести UDF и запрос из файла отсюда и отформатировать исходные данные умной таблицей с заголовками (у нее должно быть название Таблица1), то на выходе получится именно такой результат
К сообщению приложен файл: 2162194.xlsm(27Kb)


(_)Õvõ(_)

Сообщение отредактировал krosav4ig - Вторник, 11.10.2016, 22:23
 
Ответить
Сообщение
Нужно что бы получился такой результат

AngelOfLegend, ну дык если в ваш последний файл перенести UDF и запрос из файла отсюда и отформатировать исходные данные умной таблицей с заголовками (у нее должно быть название Таблица1), то на выходе получится именно такой результат

Автор - krosav4ig
Дата добавления - 11.10.2016 в 17:54
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Распарсить данные ячейки таблицы (Формулы/Formulas)
Страница 1 из 11
Поиск:

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