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

Вход

Регистрация

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

 

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

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

Excel 2010
Еще раз обращаюсь к уважаемому собранию за помощью.
Понял, что без макросов мне не обойтись, а самостоятельно их писать не умею. :(

Суть проблемы: чтобы не качать регулярно из БД десятки мегабайт, хочу получаемые данные ограничить по условию статус=архив И срок от даты до сегодня < 2мес.
Чтобы архивные данные в Экселе не потеряли значения, планирую регулярно переводить их в текст/число (скопировать и вклеить как значения). Условия для Экселя такие же как и для базы но временной интервал вдвое короче. Регулярность - примерно раз в неделю
Помогите, если это реально. Файл примера в аттаче.
К сообщению приложен файл: 8458514.xlsx(16Kb)
 
Ответить
СообщениеЕще раз обращаюсь к уважаемому собранию за помощью.
Понял, что без макросов мне не обойтись, а самостоятельно их писать не умею. :(

Суть проблемы: чтобы не качать регулярно из БД десятки мегабайт, хочу получаемые данные ограничить по условию статус=архив И срок от даты до сегодня < 2мес.
Чтобы архивные данные в Экселе не потеряли значения, планирую регулярно переводить их в текст/число (скопировать и вклеить как значения). Условия для Экселя такие же как и для базы но временной интервал вдвое короче. Регулярность - примерно раз в неделю
Помогите, если это реально. Файл примера в аттаче.

Автор - Кравченко
Дата добавления - 25.02.2016 в 15:58
Udik Дата: Четверг, 25.02.2016, 16:16 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1204
Репутация: 152 ±
Замечаний: 0% ±

Excel 2013
Цитата Кравченко, 25.02.2016 в 15:58, в сообщении № 1
Понял, что без макросов мне не обойтись

Именно поэтому Вы поместили тему в раздел формул. B)

[moder] hands :hands: hands
К сообщению приложен файл: 6701915.jpg(28Kb)


вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com


Сообщение отредактировал Udik - Четверг, 25.02.2016, 16:29
 
Ответить
Сообщение
Цитата Кравченко, 25.02.2016 в 15:58, в сообщении № 1
Понял, что без макросов мне не обойтись

Именно поэтому Вы поместили тему в раздел формул. B)

[moder] hands :hands: hands

Автор - Udik
Дата добавления - 25.02.2016 в 16:16
Udik Дата: Четверг, 25.02.2016, 16:31 | Сообщение № 3
Группа: Друзья
Ранг: Старожил
Сообщений: 1204
Репутация: 152 ±
Замечаний: 0% ±

Excel 2013
Этот код в лист
[vba]
Код

Public Sub test()
Dim rng1 As Range, rng2 As Range
Dim i As Long, j&
Const deltDay As Byte = 7 'дельта в днях
Const strUslov As String = "архив"

j = Cells(Rows.Count, "D").End(xlUp).Row
For Each rng1 In Range(Cells(9, "D"), Cells(9, "D"))
    If IsNumeric(rng1.Value2) Then
        i = rng1.Row
        If (DateDiff("d", rng1.Value, Now) >= deltDay) And (UCase(Cells(i, "F").Text) = UCase(strUslov)) Then
          i = rng1.Row
          For Each rng2 In Range(Cells(i, "A"), Cells(i, "K"))
           Cells(rng2.Row, rng2.Column).Value = rng2.Value2
          Next
        End If
    End If
Next
End Sub

[/vba]
упс, забыл про условия, сейчас подправлю

подправил

запускать по мере надобности


вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com


Сообщение отредактировал Udik - Четверг, 25.02.2016, 18:31
 
Ответить
СообщениеЭтот код в лист
[vba]
Код

Public Sub test()
Dim rng1 As Range, rng2 As Range
Dim i As Long, j&
Const deltDay As Byte = 7 'дельта в днях
Const strUslov As String = "архив"

j = Cells(Rows.Count, "D").End(xlUp).Row
For Each rng1 In Range(Cells(9, "D"), Cells(9, "D"))
    If IsNumeric(rng1.Value2) Then
        i = rng1.Row
        If (DateDiff("d", rng1.Value, Now) >= deltDay) And (UCase(Cells(i, "F").Text) = UCase(strUslov)) Then
          i = rng1.Row
          For Each rng2 In Range(Cells(i, "A"), Cells(i, "K"))
           Cells(rng2.Row, rng2.Column).Value = rng2.Value2
          Next
        End If
    End If
Next
End Sub

[/vba]
упс, забыл про условия, сейчас подправлю

подправил

запускать по мере надобности

Автор - Udik
Дата добавления - 25.02.2016 в 16:31
Кравченко Дата: Четверг, 25.02.2016, 16:31 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Udik, %) честно говоря, даже не обратил внимания. Увы, отсутствие опыта на этом форуме дает знать.
Модераторы, админы исправьте пожалуйста мою ошибку.
[moder]Месяц без 2-х дней - это отсутствие опыта? Правила форума хоть раз прочитали? Тему перенес, но ...


Сообщение отредактировал _Boroda_ - Четверг, 25.02.2016, 16:49
 
Ответить
СообщениеUdik, %) честно говоря, даже не обратил внимания. Увы, отсутствие опыта на этом форуме дает знать.
Модераторы, админы исправьте пожалуйста мою ошибку.
[moder]Месяц без 2-х дней - это отсутствие опыта? Правила форума хоть раз прочитали? Тему перенес, но ...

Автор - Кравченко
Дата добавления - 25.02.2016 в 16:31
Кравченко Дата: Четверг, 25.02.2016, 16:36 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Udik, спасибо за ответ, но надо не все строки перевести, а только отвечающие условию.
Или я недопонял код?...

Определение массива.
Перевод по циклу значений в текст.
... и всё.
 
Ответить
СообщениеUdik, спасибо за ответ, но надо не все строки перевести, а только отвечающие условию.
Или я недопонял код?...

Определение массива.
Перевод по циклу значений в текст.
... и всё.

Автор - Кравченко
Дата добавления - 25.02.2016 в 16:36
Udik Дата: Четверг, 25.02.2016, 18:03 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 1204
Репутация: 152 ±
Замечаний: 0% ±

Excel 2013
Код выше исправил B) , два столбца форматнул как дату.
Цитата Кравченко, 25.02.2016 в 16:36, в сообщении № 5
Перевод по циклу значений в текст

Не уверен, что это хорошее решение, вставил значения. Формулы преобразуются в статические данные в любом случае.
К сообщению приложен файл: t-t.xlsm(26Kb)


вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com


Сообщение отредактировал Udik - Четверг, 25.02.2016, 18:33
 
Ответить
СообщениеКод выше исправил B) , два столбца форматнул как дату.
Цитата Кравченко, 25.02.2016 в 16:36, в сообщении № 5
Перевод по циклу значений в текст

Не уверен, что это хорошее решение, вставил значения. Формулы преобразуются в статические данные в любом случае.

Автор - Udik
Дата добавления - 25.02.2016 в 18:03
Кравченко Дата: Четверг, 25.02.2016, 21:52 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Udik, спасибо за оперативность.
После изменения (Cells(9, "D"), Cells(9, "D")) на (Cells(2, "D"), Cells(20, "D")) заработало во всем диапазоне. И сработало хорошо.

Есть одно "но".
Можно ли указать динамический диапазон строк, например на высоту таблицы или ограничиться снизу пустой строкой? Дело в том, что таблица будет расти, а под ней планируются статистические данные. И в этом варианте макроса мне придется хотя бы раз в пол года на 3-4 машинах править макрос.

И вопросы. :) Как же без них.
1. Какие два столбца пришлось форматировать?
2. Хотел спросит про переменную j. Что она определяет?

По поводу "...Перевод по циклу значений в текст..." - это я (типа в слух) перечислял команды первого варианта макроса. :) Вы всё правильно поняли.
 
Ответить
СообщениеUdik, спасибо за оперативность.
После изменения (Cells(9, "D"), Cells(9, "D")) на (Cells(2, "D"), Cells(20, "D")) заработало во всем диапазоне. И сработало хорошо.

Есть одно "но".
Можно ли указать динамический диапазон строк, например на высоту таблицы или ограничиться снизу пустой строкой? Дело в том, что таблица будет расти, а под ней планируются статистические данные. И в этом варианте макроса мне придется хотя бы раз в пол года на 3-4 машинах править макрос.

И вопросы. :) Как же без них.
1. Какие два столбца пришлось форматировать?
2. Хотел спросит про переменную j. Что она определяет?

По поводу "...Перевод по циклу значений в текст..." - это я (типа в слух) перечислял команды первого варианта макроса. :) Вы всё правильно поняли.

Автор - Кравченко
Дата добавления - 25.02.2016 в 21:52
Udik Дата: Пятница, 26.02.2016, 13:14 | Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 1204
Репутация: 152 ±
Замечаний: 0% ±

Excel 2013
Цитата Кравченко, 25.02.2016 в 21:52, в сообщении № 7
После изменения (Cells(9, "D"), Cells(9, "D")) на

хы, это я на время отладки указал одну строку, а потом включить весь диапазон забыл yes .

два столбца: D, Q
поставил краткий формат даты (может и без этого будет прекрасно работать)
j переименовал и коммент поставил.

[vba]
Код

Public Sub test()
Dim rng1 As Range, rng2 As Range
Dim i As Long, rowLast&
Const deltDay As Byte = 7 'дельта в днях
Const strUslov As String = "архив"
Const rowStart As Byte = 2 'первая строка с данными

rowLast = Cells(Rows.Count, "D").End(xlUp).Row 'последняя заполненная ячейка в столбце
For Each rng1 In Range(Cells(rowStart, "D"), Cells(rowLast, "D"))
    If IsNumeric(rng1.Value2) Then
        i = rng1.Row
        If (DateDiff("d", rng1.Value, Now) >= deltDay) And (UCase(Cells(i, "F").Text) = UCase(strUslov)) Then
          For Each rng2 In Range(Cells(i, "A"), Cells(i, "K"))
           Cells(rng2.Row, rng2.Column).Value = rng2.Value2
          Next
        End If
    End If
Next
End Sub

[/vba]
К сообщению приложен файл: 0t.xlsm(23Kb)


вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com


Сообщение отредактировал Udik - Пятница, 26.02.2016, 15:36
 
Ответить
Сообщение
Цитата Кравченко, 25.02.2016 в 21:52, в сообщении № 7
После изменения (Cells(9, "D"), Cells(9, "D")) на

хы, это я на время отладки указал одну строку, а потом включить весь диапазон забыл yes .

два столбца: D, Q
поставил краткий формат даты (может и без этого будет прекрасно работать)
j переименовал и коммент поставил.

[vba]
Код

Public Sub test()
Dim rng1 As Range, rng2 As Range
Dim i As Long, rowLast&
Const deltDay As Byte = 7 'дельта в днях
Const strUslov As String = "архив"
Const rowStart As Byte = 2 'первая строка с данными

rowLast = Cells(Rows.Count, "D").End(xlUp).Row 'последняя заполненная ячейка в столбце
For Each rng1 In Range(Cells(rowStart, "D"), Cells(rowLast, "D"))
    If IsNumeric(rng1.Value2) Then
        i = rng1.Row
        If (DateDiff("d", rng1.Value, Now) >= deltDay) And (UCase(Cells(i, "F").Text) = UCase(strUslov)) Then
          For Each rng2 In Range(Cells(i, "A"), Cells(i, "K"))
           Cells(rng2.Row, rng2.Column).Value = rng2.Value2
          Next
        End If
    End If
Next
End Sub

[/vba]

Автор - Udik
Дата добавления - 26.02.2016 в 13:14
Кравченко Дата: Пятница, 26.02.2016, 14:50 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Udik, и ещё раз спасибо! :)
Пока не проверяю, некогда, но похоже всё должно работать.

Пожалуй задам ещё один вопрос. Скорее не по необходимости, а "красоты кода для".
Стоит ли заморачиваться верхним динамическим пределом? Или по другому, для этого макроса будет тяжело обрабатывать 5-10 тыс строк?

По поводу формата Дата, то какая-то странная ситуация. Я их форматировал в короткий вид... и вы тоже форматировали... а после я опять форматировал. Похоже тут какие-то нюансы независящие от нас. :)
 
Ответить
СообщениеUdik, и ещё раз спасибо! :)
Пока не проверяю, некогда, но похоже всё должно работать.

Пожалуй задам ещё один вопрос. Скорее не по необходимости, а "красоты кода для".
Стоит ли заморачиваться верхним динамическим пределом? Или по другому, для этого макроса будет тяжело обрабатывать 5-10 тыс строк?

По поводу формата Дата, то какая-то странная ситуация. Я их форматировал в короткий вид... и вы тоже форматировали... а после я опять форматировал. Похоже тут какие-то нюансы независящие от нас. :)

Автор - Кравченко
Дата добавления - 26.02.2016 в 14:50
Udik Дата: Пятница, 26.02.2016, 14:57 | Сообщение № 10
Группа: Друзья
Ранг: Старожил
Сообщений: 1204
Репутация: 152 ±
Замечаний: 0% ±

Excel 2013
Цитата Кравченко, 26.02.2016 в 14:50, в сообщении № 9
Или по другому, для этого макроса будет тяжело обрабатывать 5-10 тыс строк?

Если будет тормозить, то вначале запускать процедуру по отключению "лишнего", а потом включить всё "взад" :)


вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com
 
Ответить
Сообщение
Цитата Кравченко, 26.02.2016 в 14:50, в сообщении № 9
Или по другому, для этого макроса будет тяжело обрабатывать 5-10 тыс строк?

Если будет тормозить, то вначале запускать процедуру по отключению "лишнего", а потом включить всё "взад" :)

Автор - Udik
Дата добавления - 26.02.2016 в 14:57
Кравченко Дата: Пятница, 26.02.2016, 15:17 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
%) :D "не понял, налей..."
Пытаюсь расшифровать :)
Если будет тормозить, то проблема не только в одном макросе. Такой макрос выполнит пол миллиона операций за... учитывая процессор... переводя в машинные коды... учитывая текущую загрузку компа и сети... учитывая... В общем, если говорить абсолютно точно, то достаточно быстро. Я правильно понял? :D
 
Ответить
Сообщение%) :D "не понял, налей..."
Пытаюсь расшифровать :)
Если будет тормозить, то проблема не только в одном макросе. Такой макрос выполнит пол миллиона операций за... учитывая процессор... переводя в машинные коды... учитывая текущую загрузку компа и сети... учитывая... В общем, если говорить абсолютно точно, то достаточно быстро. Я правильно понял? :D

Автор - Кравченко
Дата добавления - 26.02.2016 в 15:17
Udik Дата: Пятница, 26.02.2016, 15:29 | Сообщение № 12
Группа: Друзья
Ранг: Старожил
Сообщений: 1204
Репутация: 152 ±
Замечаний: 0% ±

Excel 2013
Там от многих вещей зависит, просто во время работы макроса сам эксель по-умолчанию много неактуального делает, вот это и отключают :D типа такого:
[vba]
Код

Public Sub uskorRadio(flag As Boolean)
'обновл. страницы после каждого действия
  Application.ScreenUpdating = flag

  'ручной режим расчётов
  If flag Then
      Application.Calculation = xlCalculationAutomatic
  Else
      Application.Calculation = xlCalculationManual
  End If

  'события
  Application.EnableEvents = flag

  'показ разрывов страниц
  If Workbooks.Count Then
      ActiveWorkbook.ActiveSheet.DisplayPageBreaks = flag
  End If

  'статусная строка
  Application.DisplayStatusBar = flag

  'сообщения Excel
  Application.DisplayAlerts = flag

End Sub
[/vba]
На всякий случай дополнительно повесил включение на открытие книги (на случай нештатного завершения макроса).
Но само отключение/включение с обновлением может отнять несколько секнд, поэтому если и без этого время обработки маленькое, можно не отключать.
К сообщению приложен файл: 9723332.xlsm(29Kb)


вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com


Сообщение отредактировал Udik - Пятница, 26.02.2016, 15:59
 
Ответить
СообщениеТам от многих вещей зависит, просто во время работы макроса сам эксель по-умолчанию много неактуального делает, вот это и отключают :D типа такого:
[vba]
Код

Public Sub uskorRadio(flag As Boolean)
'обновл. страницы после каждого действия
  Application.ScreenUpdating = flag

  'ручной режим расчётов
  If flag Then
      Application.Calculation = xlCalculationAutomatic
  Else
      Application.Calculation = xlCalculationManual
  End If

  'события
  Application.EnableEvents = flag

  'показ разрывов страниц
  If Workbooks.Count Then
      ActiveWorkbook.ActiveSheet.DisplayPageBreaks = flag
  End If

  'статусная строка
  Application.DisplayStatusBar = flag

  'сообщения Excel
  Application.DisplayAlerts = flag

End Sub
[/vba]
На всякий случай дополнительно повесил включение на открытие книги (на случай нештатного завершения макроса).
Но само отключение/включение с обновлением может отнять несколько секнд, поэтому если и без этого время обработки маленькое, можно не отключать.

Автор - Udik
Дата добавления - 26.02.2016 в 15:29
Кравченко Дата: Пятница, 26.02.2016, 15:42 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
%) Спросил на свою голову :) сейчас разбирайся как влияет на быстродействие разные "фишки" типа разрыва страниц.
В общем заморачиваться верхним пределом пока не стоит. А будущее покажет.
Еще раз спасибо за помощь.


Сообщение отредактировал Кравченко - Пятница, 26.02.2016, 22:56
 
Ответить
Сообщение%) Спросил на свою голову :) сейчас разбирайся как влияет на быстродействие разные "фишки" типа разрыва страниц.
В общем заморачиваться верхним пределом пока не стоит. А будущее покажет.
Еще раз спасибо за помощь.

Автор - Кравченко
Дата добавления - 26.02.2016 в 15:42
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Периодически заменять формулы их значениями (Формулы/Formulas)
Страница 1 из 11
Поиск:

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