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

@sky87 · Регистрация: 02.08.2013

Author24 — интернет-сервис помощи студентам

Всем привет!
Столкнулся с проблемой, имеется таблица в формате excel. В ней есть n-е количетсов заполненных столбцов, как загнать в массив к примеру столбцы A,B,N,Z, при условии что они идут не по порядку и их порядок изменять нельзя.

@Hugo121 · 13.08.2013, 17:59

Если жалко памяти - делайте 3 массива.
Если не жалко - делайте один большой, лишнее в коде игнорируйте.

Добавлено через 12 минут
Или так - взяли всё в один большой, создали под размер массив поменьше, в цикле переложили, большой убили.
Если данных не миллион - потратите секунду времени (или меньше), зато код дальше будет прозрачнее.
Я правда так не делаю.

@sky87 · 13.08.2013, 22:05 **[ТС]**

ДА, сейчас я делаю в 5 массивах) Там в каждом из них по 6000 значений, просто организовать синхронизацию данных по всем 5 тяжеловато, там должно быть четкое соответствие по строкам (по задумке). Вот я и подумал, что если загоню все в один n-мерный массив, то будет проще, однако, уже сомневаюсь.
Все равно спасибо за совет, буду искать себя дальше =)

@Hugo121 · 13.08.2013, 23:19

С листа получаются двумерные массивы.
И в общем нет особой разницы - 1 массив или 5-6 параллельных. Определили первую и последние строки на листе, взяли данные в массивы.
А при обращении к данным просто меняете массивы, а индекс остаётся одинаковый.
Цикл по строке сложнее сделать - т.к. больше кода, вместо одной строки будет столько, сколько массивов.

@Dmitrii · 13.08.2013, 23:32

sky87, если Вас интересуют только значения ячеек (а не формулы), то я бы (по укоренившейся привычке "скриптописателя") поступил бы как-то так:

Visual Basic

Sub Example()
Dim arrColumns, lngNumRows As Long, objDict As Object, i As Integer
arrColumns = Array(1, 2, 14, 26)
Set objDict = CreateObject("Scripting.Dictionary")
For i = 0 To UBound(arrColumns)
    lngNumRows = Cells(65536, arrColumns(i)).End(xlUp).Row
    objDict.Add arrColumns(i), Application.Transpose(Range(Cells(1, arrColumns(i)), Cells(lngNumRows, arrColumns(i))).Value)
Next
For i = 0 To UBound(arrColumns)
    lngNumRows = UBound(objDict.Item(arrColumns(i)))
    MsgBox "Столбец " & arrColumns(i) & " содержит значений: " & lngNumRows & vbNewLine & _
        "Значение первой ячейки = " & objDict.Item(arrColumns(i))(1) & vbNewLine & _
        "Значение последней ячейки = " & objDict.Item(arrColumns(i))(lngNumRows)
Next
Set objDict = Nothing
End Sub

@Hugo121 · 13.08.2013, 23:40

Что-то мне кажется, что это изврат

Чтоб перебрать это добро в цикле - нужно или очень часто лазить в словарь, или всё равно выгружать все итемы во временные массивы. Так проще сразу в эти временные всё и взять

А если нужно значения массивов менять - скорее всего в словаре ничего не поменять, нужно всё выгрузить, поменять, загрузить всё назад. Или я ошибаюсь? Не проверял...

@sky87 · 13.08.2013, 23:53 **[ТС]**

изначально, я сделал через считывание данных непосредственно с листов и собственно в них и производил все просчеты, что вскоре показало очень трагичный финал истории, когда у меня файл сжирал ядро сервера=) Я задумался над массивом, и ускорил работу с 1 часа обработки 60 000 значений до 10 минут) а теперь ищу дальнейшую оптимизацию.
P.S. к сожалению не могу приложить сам файл из-за ком тайны

@Hugo121 · 14.08.2013, 00:04

Похоже, что Вы массив заполняете не так:

Visual Basic

1 2	dim a() a=[a1:c60000].value

@sky87 · 14.08.2013, 00:12 **[ТС]**

я делаю примерно так:

Visual Basic

brand_arr = Worksheets(2).Range(Worksheets(2).Cells(3, 24), Worksheets(2).Cells(z, 24))
import_arr = Worksheets(2).Range(Worksheets(2).Cells(3, 7), Worksheets(2).Cells(z, 7)) 
post_arr = Worksheets(2).Range(Worksheets(2).Cells(3, 9), Worksheets(2).Cells(z, 9)) 
kont_arr = Worksheets(2).Range(Worksheets(2).Cells(3, 6), Worksheets(2).Cells(z, 6)) 
data_arr = Worksheets(2).Range(Worksheets(2).Cells(3, 5), Worksheets(2).Cells(z, 6))

это все 5 массивов с которыми я работаю

@Hugo121 · 14.08.2013, 00:45

Нормально.
Правда можно чуть короче, ну и я обычно пишу так, как сделано с brand_arr():

Visual Basic

    Dim brand_arr()
    With Worksheets(2)
        brand_arr = .Range(.Cells(3, 24), .Cells(Z, 24)).Value
        import_arr = .Range(.Cells(3, 7), .Cells(Z, 7))
        post_arr = .Range(.Cells(3, 9), .Cells(Z, 9))
        kont_arr = .Range(.Cells(3, 6), .Cells(Z, 6))
        data_arr = .Range(.Cells(3, 5), .Cells(Z, 6))
    End With

И там 2 массива перекрываются - это вероятно описка...
P.S. Если код в стандартном модуле - можно убрать точки перед Range.

Добавлено через 4 минуты
И 10 минут на 60000 строк всё равно долго.

@KoGG · 14.08.2013, 09:40

Сообщение от Hugo121

Visual Basic

    Dim brand_arr()
    With Worksheets(2)
        brand_arr = .Range(.Cells(3, 24), .Cells(Z, 24)).Value
        import_arr = .Range(.Cells(3, 7), .Cells(Z, 7))
        post_arr = .Range(.Cells(3, 9), .Cells(Z, 9))
        kont_arr = .Range(.Cells(3, 6), .Cells(Z, 6))
        data_arr = .Range(.Cells(3, 5), .Cells(Z, 6))
    End With

P.S. Если код в стандартном модуле - можно убрать точки перед Range.

Даже если код в стандартном модуле - не в коем случае не надо удалять точки перед Range, так как Range без точки будет браться не с листа Worksheets(2), а с того листа, который случайно в настоящий момент является активным, несмотря на прописанные позиции .Cells

@sky87 · 14.08.2013, 09:46 **[ТС]**

возможно еще от того, что я в облаке работаю.
краткость кода сейчас не самое важное, сейчас главное быстродействие

@Hugo121 · 14.08.2013, 09:53

Про точки: да и пусть range берётся откуда угодно - главное что данные берутся какие задумано и ошибок нет

В общем конечно точки поставить не сложно, но работает и без них.

Про быстродействие - так что с массивами делаете? То, что файл в облаке - не должно так влиять, массивы ведь в памяти, уже не в облаке.

@sky87 · 14.08.2013, 10:56 **[ТС]**

вся работа, в том числе составление кода веду прямо в облаке, посредством удаленного раб стола. Поверьте, там может и тормозить)
Суть "програмки" заключается в следующем, есть книга в которой есть 13 листов, первые 12 (это индексы месяцев по сути) содержат некоторый набор данных в 24 столбцах (из них используются только 6), а на 13 листе необходимо сделать итоговый отчет, в котором будет произведен подсчет данных по каждому месяцу. Мне нужно исключить совпадающие значения, но важно, чтобы сохранялась синхра построчно. Вот именно из-за этого я подумал о том, что один большой массив будет использовать для синхры проще, как оказалось нет.

@Hugo121 · 14.08.2013, 11:31

Используйте массив и словарь.
Будет быстро.
А в облаке тормозит именно написание кода - сама работа идёт без проблем. Знаю, делаю

@Dmitrii · 14.08.2013, 11:35

Сообщение от Hugo121

... мне кажется, что это изврат...

Отчего так? Задача автора темы, в том виде, как она сформулирована, решается полностью.
Безусловно, словарь по сравнению с набором массивов имеет рад недостатков: меньшее быстродействие, более сложный синтаксис, невозможность изменить значение элемента массива непосредственно в словаре (без использования временного массива). Однако есть и преимущества: возможность "динамизировать" количество обрабатываемых столбцов, универсальный синтаксис обращения к любому элементу вне зависимости от количества и имён массивов (представьте себе, что их не 4-5, а 40-50).

Сообщение от Hugo121

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

А что, для перебора элементов непосредственно в массиве нужно реже обращаться к нему, чем в том случае, когда массив является элементом словаря? Кстати, во временный массив при необходимости можно выгружать только тот массив-элемент словаря, который нуждается в изменении данных, после чего его опять можно вернуть в словарь. Зато можно без проблем перебрать в цикле все элементы заданной строки всех столбцов.
Впрочем, я пока так и не понял, нужно ли изменять что-либо в исходных данных или достаточно только чтения.
Раз автор сказал, что его более всего интересует вопрос быстродействия, то словарь, конечно, проиграет массиву, но приведённый ниже код у меня, например (на достаточно медленном "железе"), работает примерно 108 секунд при совокупном количестве обрабатываемых ячеек 120000.

Visual Basic

Sub Example()
Dim arrColumns, lngNumRows As Long, objDict As Object
Dim i As Integer, j As Long, intTemp As Integer, arrTemp
Dim startTime As Single, endTime As Single
 
arrColumns = Array(1, 2, 14, 26)
Set objDict = CreateObject("Scripting.Dictionary")
Application.ScreenUpdating = False
startTime = Timer
For i = 0 To UBound(arrColumns)
    lngNumRows = Cells(65536, arrColumns(i)).End(xlUp).Row
    objDict.Add arrColumns(i), Application.Transpose(Range(Cells(1, arrColumns(i)), Cells(lngNumRows, arrColumns(i))).Value)
Next
For i = 0 To UBound(arrColumns)
    lngNumRows = UBound(objDict.Item(arrColumns(i)))
    intTemp = arrColumns(i)
    arrTemp = objDict.Item(intTemp)
    For j = 1 To lngNumRows
        arrTemp(j) = arrTemp(j) & "_"
    Next
    objDict.Item(intTemp) = arrTemp
    Range(Cells(1, intTemp), Cells(lngNumRows, intTemp)).Value = Application.Transpose(objDict.Item(intTemp))
Next
endTime = Timer
Application.ScreenUpdating = True
Set objDict = Nothing
MsgBox endTime - startTime
End Sub

@Hugo121 · 14.08.2013, 11:52

Ну да, словарь массивов компактнее и динамичнее (легко добавить/удалить, обратиться к массиву по "имени").
А зачем Application.Transpose? Время, ограничения... Двумерный разве нельзя хранить? Не проверял...

@sky87 · 14.08.2013, 13:02 **[ТС]**

Dmitrii, безусловно, так как я занимаюсь vba всего 1,5 недели, то про понятие словаря еще не слышал, это как минимум полезный совет для развития,возможно, в будущем я применю ваш совет в деле и посмотрю как он будет работать =)

@Hugo121 · 14.08.2013, 14:15

sky87, словарь это нужная штука, возможно даже понужнее массивов

Т.е. есть смысл освоить.
В словаре хранится ключ, и к каждому ключу значение, строка или объект (например массив как в примере Дмитрия, или коллекция, или другой словарь).
В простом случае можно хранить строку, или собирать сумму значений по уникальному ключу.
Напимер, в цикле по массиву анализируем первый столбец как ключ, а значение второго столбца (число) суммируем в Item этого ключа.
Или часто можно применять так - сперва одним проходом по сводному массиву запоминаем в словаре ключи и их позицию в массиве, затем одним проходом по другим массивам (откуда вытягиваем данные в сводный) по ключу получаем позицию, по позиции обновляем данные в сводном массиве.
Думаю Вашу задачу можно решать как-то так - будет быстро.
Хотя саму задачу Вы нам ещё не рассказали

@sky87 · 15.08.2013, 12:17 **[ТС]**

как определить последний элемент массива и записать его в переменную?
Чего-то у меня не выходит:

Visual Basic

1	last = UBound(array_1)

возвращает ошибку: Type missmatch

@sky87 0 / 0 / 0 Регистрация: 02.08.2013 Сообщений: 14
	14.08.2013, 13:02 [ТС]	18
	Dmitrii, безусловно, так как я занимаюсь vba всего 1,5 недели, то про понятие словаря еще не слышал, это как минимум полезный совет для развития,возможно, в будущем я применю ваш совет в деле и посмотрю как он будет работать =) 0

@Hugo121 6937 / 2843 / 547 Регистрация: 19.10.2012 Сообщений: 8,700
	13.08.2013, 17:59	2
	Если жалко памяти - делайте 3 массива. Если не жалко - делайте один большой, лишнее в коде игнорируйте. Добавлено через 12 минут Или так - взяли всё в один большой, создали под размер массив поменьше, в цикле переложили, большой убили. Если данных не миллион - потратите секунду времени (или меньше), зато код дальше будет прозрачнее. Я правда так не делаю. 1

@sky87 0 / 0 / 0 Регистрация: 02.08.2013 Сообщений: 14
	13.08.2013, 22:05 [ТС]	3
	ДА, сейчас я делаю в 5 массивах) Там в каждом из них по 6000 значений, просто организовать синхронизацию данных по всем 5 тяжеловато, там должно быть четкое соответствие по строкам (по задумке). Вот я и подумал, что если загоню все в один n-мерный массив, то будет проще, однако, уже сомневаюсь. Все равно спасибо за совет, буду искать себя дальше =) 0

@Hugo121 6937 / 2843 / 547 Регистрация: 19.10.2012 Сообщений: 8,700
	13.08.2013, 23:19	4
	С листа получаются двумерные массивы. И в общем нет особой разницы - 1 массив или 5-6 параллельных. Определили первую и последние строки на листе, взяли данные в массивы. А при обращении к данным просто меняете массивы, а индекс остаётся одинаковый. Цикл по строке сложнее сделать - т.к. больше кода, вместо одной строки будет столько, сколько массивов. 0

@Hugo121 6937 / 2843 / 547 Регистрация: 19.10.2012 Сообщений: 8,700
	13.08.2013, 23:40	6
	Что-то мне кажется, что это изврат Чтоб перебрать это добро в цикле - нужно или очень часто лазить в словарь, или всё равно выгружать все итемы во временные массивы. Так проще сразу в эти временные всё и взять А если нужно значения массивов менять - скорее всего в словаре ничего не поменять, нужно всё выгрузить, поменять, загрузить всё назад. Или я ошибаюсь? Не проверял... 0

@sky87 0 / 0 / 0 Регистрация: 02.08.2013 Сообщений: 14
	13.08.2013, 23:53 [ТС]	7
	изначально, я сделал через считывание данных непосредственно с листов и собственно в них и производил все просчеты, что вскоре показало очень трагичный финал истории, когда у меня файл сжирал ядро сервера=) Я задумался над массивом, и ускорил работу с 1 часа обработки 60 000 значений до 10 минут) а теперь ищу дальнейшую оптимизацию. P.S. к сожалению не могу приложить сам файл из-за ком тайны 0

@sky87 0 / 0 / 0 Регистрация: 02.08.2013 Сообщений: 14
	14.08.2013, 09:46 [ТС]	12
	возможно еще от того, что я в облаке работаю. краткость кода сейчас не самое важное, сейчас главное быстродействие 0

@Hugo121 6937 / 2843 / 547 Регистрация: 19.10.2012 Сообщений: 8,700
	14.08.2013, 09:53	13
	Про точки: да и пусть range берётся откуда угодно - главное что данные берутся какие задумано и ошибок нет В общем конечно точки поставить не сложно, но работает и без них. Про быстродействие - так что с массивами делаете? То, что файл в облаке - не должно так влиять, массивы ведь в памяти, уже не в облаке. 0

@sky87 0 / 0 / 0 Регистрация: 02.08.2013 Сообщений: 14
	14.08.2013, 10:56 [ТС]	14
	вся работа, в том числе составление кода веду прямо в облаке, посредством удаленного раб стола. Поверьте, там может и тормозить) Суть "програмки" заключается в следующем, есть книга в которой есть 13 листов, первые 12 (это индексы месяцев по сути) содержат некоторый набор данных в 24 столбцах (из них используются только 6), а на 13 листе необходимо сделать итоговый отчет, в котором будет произведен подсчет данных по каждому месяцу. Мне нужно исключить совпадающие значения, но важно, чтобы сохранялась синхра построчно. Вот именно из-за этого я подумал о том, что один большой массив будет использовать для синхры проще, как оказалось нет. 0

@Hugo121 6937 / 2843 / 547 Регистрация: 19.10.2012 Сообщений: 8,700
	14.08.2013, 11:31	15
	Используйте массив и словарь. Будет быстро. А в облаке тормозит именно написание кода - сама работа идёт без проблем. Знаю, делаю 0

@Hugo121 6937 / 2843 / 547 Регистрация: 19.10.2012 Сообщений: 8,700
	14.08.2013, 11:52	17
	Ну да, словарь массивов компактнее и динамичнее (легко добавить/удалить, обратиться к массиву по "имени"). А зачем Application.Transpose? Время, ограничения... Двумерный разве нельзя хранить? Не проверял... 0

@Hugo121 6937 / 2843 / 547 Регистрация: 19.10.2012 Сообщений: 8,700
	14.08.2013, 14:15	19
	sky87, словарь это нужная штука, возможно даже понужнее массивов Т.е. есть смысл освоить. В словаре хранится ключ, и к каждому ключу значение, строка или объект (например массив как в примере Дмитрия, или коллекция, или другой словарь). В простом случае можно хранить строку, или собирать сумму значений по уникальному ключу. Напимер, в цикле по массиву анализируем первый столбец как ключ, а значение второго столбца (число) суммируем в Item этого ключа. Или часто можно применять так - сперва одним проходом по сводному массиву запоминаем в словаре ключи и их позицию в массиве, затем одним проходом по другим массивам (откуда вытягиваем данные в сводный) по ключу получаем позицию, по позиции обновляем данные в сводном массиве. Думаю Вашу задачу можно решать как-то так - будет быстро. Хотя саму задачу Вы нам ещё не рассказали 0