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

Вход

Регистрация

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

 

= Мир MS Excel/Вычисления внутри кода не используя вставки формул в ячейки - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Вычисления внутри кода не используя вставки формул в ячейки (Макросы/Sub)
Вычисления внутри кода не используя вставки формул в ячейки
master-dd Дата: Суббота, 01.12.2018, 18:51 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 78
Репутация: 1 ±
Замечаний: 0% ±

2016
Добрый день многоуважаемые форумчане.
Наверняка подобные вопросы уже были, но не поверите - ни в гугле не здесь я не нашёл ничего подобного (возможно не так спрашивал). Итак, у меня есть очень-очень большой код, который работает хорошо. Но долго. Я хочу его оптимизировать. В этом коде есть очень много похожих действий, а именно: с помощью кода определённые формулы вставляются в ячейки, затем эти формулы превращаются в значения. Вот пример:
[vba]
Код
Sheets("Temp1").Range("A2:AX1000").FormulaArray = "=IFERROR(MATCH(csv!R1C24&csv!RC[23]:R[998]C[72],Temp2!RC[46]:R[48]C[46],0),"""")"
Sheets("Temp1").Range("A2:AX1000").Value = Sheets("Temp1").Range("A2:AX1000").Value
[/vba]
Собственно, вопрос. Можно ли как-то избегать того, чтобы формулы вставлялись в ячейки, затем Excel пересчитывал, а затем заменять всё на значения? И сразу, вместо этих лишних шагов вставлять в нужный диапазон только значения, рассчитанные VBA? Вопрос касается не конкретно этого кода, а ВООБЩЕ!
Во вложении пример с вышеуказанным кодом.

Заранее всем спасибо, кто откликнется.
К сообщению приложен файл: Case1.xlsb (72.0 Kb)


Сообщение отредактировал master-dd - Суббота, 01.12.2018, 18:58
 
Ответить
СообщениеДобрый день многоуважаемые форумчане.
Наверняка подобные вопросы уже были, но не поверите - ни в гугле не здесь я не нашёл ничего подобного (возможно не так спрашивал). Итак, у меня есть очень-очень большой код, который работает хорошо. Но долго. Я хочу его оптимизировать. В этом коде есть очень много похожих действий, а именно: с помощью кода определённые формулы вставляются в ячейки, затем эти формулы превращаются в значения. Вот пример:
[vba]
Код
Sheets("Temp1").Range("A2:AX1000").FormulaArray = "=IFERROR(MATCH(csv!R1C24&csv!RC[23]:R[998]C[72],Temp2!RC[46]:R[48]C[46],0),"""")"
Sheets("Temp1").Range("A2:AX1000").Value = Sheets("Temp1").Range("A2:AX1000").Value
[/vba]
Собственно, вопрос. Можно ли как-то избегать того, чтобы формулы вставлялись в ячейки, затем Excel пересчитывал, а затем заменять всё на значения? И сразу, вместо этих лишних шагов вставлять в нужный диапазон только значения, рассчитанные VBA? Вопрос касается не конкретно этого кода, а ВООБЩЕ!
Во вложении пример с вышеуказанным кодом.

Заранее всем спасибо, кто откликнется.

Автор - master-dd
Дата добавления - 01.12.2018 в 18:51
StoTisteg Дата: Суббота, 01.12.2018, 22:01 | Сообщение № 2
Группа: Авторы
Ранг: Старожил
Сообщений: 1161
Репутация: 103 ±
Замечаний: 0% ±

Excel 2010
Ну тогда и ответ будет вообще...[vba]
Код
Application.WorksheetFunction
[/vba]


Интуитивно понятный код - это когда интуитивно понятно, что это код.

Сообщение отредактировал StoTisteg - Суббота, 01.12.2018, 22:02
 
Ответить
СообщениеНу тогда и ответ будет вообще...[vba]
Код
Application.WorksheetFunction
[/vba]

Автор - StoTisteg
Дата добавления - 01.12.2018 в 22:01
_Boroda_ Дата: Суббота, 01.12.2018, 22:02 | Сообщение № 3
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Можно ли как-то избегать того, чтобы формулы вставлялись в ячейки
Не можно, а НУЖНО
Конкретно для ПОИСКПОЗ в VBA можно написать так
[vba]
Код
WorksheetFunction.Match
[/vba]Но в данном раскладе Вам это не поможет.
Вы бы написали конечную цель вывода на лист Temp1 результатов ПОИСКПОЗа. Зачем? Что дальше с этим делать?
А так можно, например, вот таким образом написать
[vba]
Код
Sub test()
    With Sheets("csv") 'для листа csv
        Set s_ = .Cells(1).SpecialCells(xlLastCell) 'нижняя правая ячейка используемого диапазона (аналог нажатия Контрл+Енд на листа)
        c0_ = 24 'первый столбец
        nc_ = s_.Column - c0_ + 1 'кол-во столбцов
        r0_ = 2 'первая строка
        nr_ = s_.Row - r0_ + 1 'кол-во строк
        z_ = .Cells(1, c0_) 'постоянная часть
        ar = .Cells(r0_, c0_).Resize(nr_, nc_) 'все данные в массив
        ReDim ar1(1 To nr_, 1 To nc_) 'пустой массив для результатов
    End With
    With Sheets("Temp2") 'для листа Temp2
        nr1_ = .Range("AU" & .Rows.Count).End(3).Row 'последняя строка
        arskl = .Range("AU2").Resize(nr1_).Value 'данные из AU в массив (последнюю пустую ячейку тоже захватываем, это не ошибка)
    End With
    Set slov = CreateObject("Scripting.Dictionary") 'словарь
    With slov 'для него
        .CompareMode = 1 'регистр букв неважен
        For i = 1 To nr1_ - 1 'цикл по массиву arskl кроме последней пустой
            .Item(arskl(i, 1)) = i 'в словаре значения из arskl - ключи, а порядковые номера - элементы
        Next i
        For i = 1 To nr_ 'цикл по строкам массива ar
            For j = 1 To nc_ 'цикл по столбцам массива ar
                If ar(i, j) = "" Then 'если пусто, то
                    Exit For 'по столбцу дальше не проверяем - выход из цикла j
                End If
                zz_ = z_ & ar(i, j) 'значение для проверки
                If .Exists(zz_) Then 'если оно есть в словаре
                    ar1(i, j) = .Item(zz_) 'в массив ar1 пишем порядковый номер (элемент в словаре)
                End If
            Next j
        Next i
    End With
    With Sheets("Temp1") 'для листа Temp1
        .Range(.Cells(2, 1), .Cells(2, 1).SpecialCells(xlLastCell)).ClearContents 'стираем всё, начиная с А2
        .Cells(2, 1).Resize(nr_, nc_) = ar1 'вставляем массив ar1
    End With
End Sub
[/vba]
К сообщению приложен файл: Case1_1.xlsb (68.6 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
Можно ли как-то избегать того, чтобы формулы вставлялись в ячейки
Не можно, а НУЖНО
Конкретно для ПОИСКПОЗ в VBA можно написать так
[vba]
Код
WorksheetFunction.Match
[/vba]Но в данном раскладе Вам это не поможет.
Вы бы написали конечную цель вывода на лист Temp1 результатов ПОИСКПОЗа. Зачем? Что дальше с этим делать?
А так можно, например, вот таким образом написать
[vba]
Код
Sub test()
    With Sheets("csv") 'для листа csv
        Set s_ = .Cells(1).SpecialCells(xlLastCell) 'нижняя правая ячейка используемого диапазона (аналог нажатия Контрл+Енд на листа)
        c0_ = 24 'первый столбец
        nc_ = s_.Column - c0_ + 1 'кол-во столбцов
        r0_ = 2 'первая строка
        nr_ = s_.Row - r0_ + 1 'кол-во строк
        z_ = .Cells(1, c0_) 'постоянная часть
        ar = .Cells(r0_, c0_).Resize(nr_, nc_) 'все данные в массив
        ReDim ar1(1 To nr_, 1 To nc_) 'пустой массив для результатов
    End With
    With Sheets("Temp2") 'для листа Temp2
        nr1_ = .Range("AU" & .Rows.Count).End(3).Row 'последняя строка
        arskl = .Range("AU2").Resize(nr1_).Value 'данные из AU в массив (последнюю пустую ячейку тоже захватываем, это не ошибка)
    End With
    Set slov = CreateObject("Scripting.Dictionary") 'словарь
    With slov 'для него
        .CompareMode = 1 'регистр букв неважен
        For i = 1 To nr1_ - 1 'цикл по массиву arskl кроме последней пустой
            .Item(arskl(i, 1)) = i 'в словаре значения из arskl - ключи, а порядковые номера - элементы
        Next i
        For i = 1 To nr_ 'цикл по строкам массива ar
            For j = 1 To nc_ 'цикл по столбцам массива ar
                If ar(i, j) = "" Then 'если пусто, то
                    Exit For 'по столбцу дальше не проверяем - выход из цикла j
                End If
                zz_ = z_ & ar(i, j) 'значение для проверки
                If .Exists(zz_) Then 'если оно есть в словаре
                    ar1(i, j) = .Item(zz_) 'в массив ar1 пишем порядковый номер (элемент в словаре)
                End If
            Next j
        Next i
    End With
    With Sheets("Temp1") 'для листа Temp1
        .Range(.Cells(2, 1), .Cells(2, 1).SpecialCells(xlLastCell)).ClearContents 'стираем всё, начиная с А2
        .Cells(2, 1).Resize(nr_, nc_) = ar1 'вставляем массив ar1
    End With
End Sub
[/vba]

Автор - _Boroda_
Дата добавления - 01.12.2018 в 22:02
master-dd Дата: Воскресенье, 02.12.2018, 14:28 | Сообщение № 4
Группа: Пользователи
Ранг: Участник
Сообщений: 78
Репутация: 1 ±
Замечаний: 0% ±

2016
StoTisteg, спасибо большое за ответ!!! Именно об этом объекте я не знал (я вообще только учусь VBA). Теперь буду осваивать!!!
 
Ответить
СообщениеStoTisteg, спасибо большое за ответ!!! Именно об этом объекте я не знал (я вообще только учусь VBA). Теперь буду осваивать!!!

Автор - master-dd
Дата добавления - 02.12.2018 в 14:28
master-dd Дата: Воскресенье, 02.12.2018, 14:32 | Сообщение № 5
Группа: Пользователи
Ранг: Участник
Сообщений: 78
Репутация: 1 ±
Замечаний: 0% ±

2016
_Boroda_, в очередной раз Вам большое спасибо! hands Буду изучать этот объект.
Особенно спасибо за пояснения "для чайников" в каждой строке - это облегчит обучение!!!
А что касается пояснений, зачем именно так это работает - там очень сложный код, в примере был его кусок. Всю логику быстро не пояснишь...
 
Ответить
Сообщение _Boroda_, в очередной раз Вам большое спасибо! hands Буду изучать этот объект.
Особенно спасибо за пояснения "для чайников" в каждой строке - это облегчит обучение!!!
А что касается пояснений, зачем именно так это работает - там очень сложный код, в примере был его кусок. Всю логику быстро не пояснишь...

Автор - master-dd
Дата добавления - 02.12.2018 в 14:32
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Вычисления внутри кода не используя вставки формул в ячейки (Макросы/Sub)
  • Страница 1 из 1
  • 1
Поиск:

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