Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Создание структурированного массива
 
#1
13.06.2017 14:44:50
Уважаемые форумчане, прошу помощи.

Есть выгрузка в excel из CRM-системы, выгрузка в очень неудобном виде, данных много, а нужно постоянно иметь актуальный аналитический инструмент на их основе.

В приложенном файле-примере слева данные в том виде, в котором они выгружаются, справа - как хотелось бы их видеть.
На форуме ответа не нашел, так как не могу понять, как правильно сформулировать задачу. Заранее спасибо.
 
 
 
#2
13.06.2017 16:22:42
Можно сделать на трёх словарях (и паре массивов, если данных много, но не настолько, что не помещаются в массив):
1. цикл по исходным данным - собираем словарь (или как вариант коллекцию) из данных первого столбца, словарь (или как вариант коллекцию) из данных второго столбца, и словарь из составных ключей типа "tablet|M03_Specification" с значением "closed".
2. два словаря выгружаем как шапки слева и сверху, затем двумя вложенными циклами по шапкам/словарям получаем ключ, по нему извлекаем данные из третьего словаря в итоговую таблицу.
Можно всё это провернуть через массивы, а можно прямо так на листе данные перебирать.
 
 
 
#3
13.06.2017 16:30:07
Power Query
Неизлечимых болезней нет, есть неизлечимые люди.
 
 
 
#4
13.06.2017 16:38:40
Uarabey какой объем данных? Сколько строк в день получается?
 
 
 
#5
13.06.2017 18:37:14
Дмитрий Тарковский, около 1000 строк. По сути, нужна сводная таблица, но, насколько мне известно, сводная таблица не умеет значения показывать.

И еще у меня Excel 2007
Изменено: Uarabey - 13.06.2017 18:49:43
 
 
 
#6
13.06.2017 19:10:19
Цитата
Uarabey написал:
И еще у меня Excel 2007
Переходите на следующую версию, ведь проще работать будет.
Неизлечимых болезней нет, есть неизлечимые люди.
 
 
 
#7
13.06.2017 19:49:44
Для любой версии, но не для Мака:
Код
Sub tt()
    Dim a, lL&, i&, ii&, t$, d1 As Object, d2 As Object, d3 As Object, el1, el2

    Set d1 = CreateObject("scripting.dictionary"): d1.comparemode = 1
    Set d2 = CreateObject("scripting.dictionary"): d2.comparemode = 1
    Set d3 = CreateObject("scripting.dictionary"): d3.comparemode = 1

    lL = Range("B" & Rows.Count).End(xlUp).Row
    a = Range(Range("A2"), Range("C" & lL&)).Value

    For i = 1 To UBound(a)
        If Len(a(i, 1)) Then t = a(i, 1)
        d1.Item(t) = 0&
        d2.Item(a(i, 2)) = 0&
        d3.Item(t & "|" & a(i, 2)) = a(i, 3)
    Next

    ReDim a(1 To d1.Count + 1, 1 To d2.Count + 1)
    i = 1
    For Each el1 In d1.keys
        ii = 1: i = i + 1
        a(i, 1) = el1
        For Each el2 In d2.keys
            ii = ii + 1
            If i = 2 Then a(1, ii) = el2
            a(i, ii) = d3.Item(el1 & "|" & el2)
        Next
    Next

    [g1].Resize(UBound(a), UBound(a, 2)) = a
End Sub

В данном варианте запомнится только один последний статус для каждой пары значений el1 & "|" & el2, по логике задачи вроде как этого достаточно, и это правильно :)
Но если что - легко собирать всё.
Изменено: Hugo - 13.06.2017 19:56:23
 
 
 
#8
13.06.2017 21:35:54
Uarabey Есть сводная таблица для текста, делается с помощью SQL. Но там вроде ограничение до 65 000 строк. Можно попробовать прикрутить к Вашему файлу.
Не получилось в две строчки сделать, надеюсь гуру внесут свою лепту, а то код не мой - нарыл где то тут на форуме.
Код такой:
Код
Dim cn As ADODB.Connection, rs As ADODB.Recordset
Dim sCon As String, n As Long, sSql As String
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
    sCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 8.0;HDR=No"";"
    cn.Open sCon
    If Not cn.State = 1 Then Exit Sub
  sSql = "TRANSFORM F3 " & _
    "SELECT F1 FROM [" & "Лист1" & "$a2:c65] " & _
    "GROUP BY F1, F3 ORDER BY F1 " & _
    "PIVOT F2;"
    rs.Open sSql, cn, adOpenStatic, adLockReadOnly
    
For y = 1 To rs.Fields.Count - 1
Cells(1, 5 + y) = rs.Fields(y).Name
Next
       n = 1
Do While Not rs.EOF

For i = 0 To rs.Fields.Count - 1
Cells(1 + n, 5 + i) = rs.Fields(i).Value
Next i
n = n + 1 <- Вот в этом месте надо сверять F1 и если пошло изменение увеличивать n - тогда будет 2 строчки) (по-моему :))
rs.MoveNext

Loop
rs.Close:   cn.Close
Set cn = Nothing:   Set rs = Nothing
Изменено: Дмитрий Тарковский - 13.06.2017 22:35:11
 
 
 
#9
13.06.2017 23:27:58
Большое спасибо, но я не силен в VBA, думал есть более простой способ с помощью формул.
 
 
 
#10
14.06.2017 08:23:01
С помощью формул будет сложнее если сделать такое) а так ничего сложного, вставлять данные из отчета и нажимать кнопку
 
 
 
#11
14.06.2017 08:36:14
Мой вариант будет в десятки раз быстрее, и не нужно ничего подстраивать. Ну разве что можно поменять "точку" выгрузки, например выгружать в новую книгу или на другой лист.
Изменено: Hugo - 14.06.2017 09:19:29
 
 
 
#12
14.06.2017 08:50:39
Hugo словари еще не осваивал, просто хотел предложить и свой вариант (ну как свой, чуть переделанный вариант с форума)
 
 
 
Страницы: 1
Наверх