Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
Редактирование формул макросом
 
#1
30.08.2012 14:29:01
Имеется таблица глупо созданная моим предшественником с кучей информации.  
В конце каждого месяца делается баланс с тремя десятками формул такого рода "=' цех произ_'!AH$518+' цех перем_'!AH$249+'НФС '!AH$316"  
Для нового баланса нужно скопировать таблицу, а ссылаться на каждое значение одной строкой ниже. Подскажите как сделать оперативно без ручного перебора формул.
 
 
 
#2
30.08.2012 15:45:31
Проверьте такой код:  
   Set Rng = Range("___НУЖНЫЙ_ДИАПАЗОН___")    
     
   For i = Rng.Rows.Count To 1 Step -1  
       Rng.Rows(i).AutoFill Destination:=Rng.Rows(i).Resize(2, 1), Type:=xlFillDefault  
   Next i  
   Rng.Rows(1).Clear  
________________________  
khafizovtt@gmail.com
Учимся сами и помогаем другим...
 
 
 
#3
30.08.2012 19:29:34
Не оно :(  
Этот макрос смещает сами ячейки вниз, а нужно сместить ссылки С1= А3+В5 копируем в С11 получаем А4+В6.  
Скопировать красное облако в зеленое на 10 строк вниз так чтоб получить те же формулы, что и в красном, но на одну строку ниже.
 
 
 
#4
30.08.2012 19:35:41
И еще, желательно обрабатывать только ячейки из выделеного диапазона.  
 
Примерно как здесь, убираются формулы оставляя только значения.  
Dim change_area As Range  
For Each change_area In Selection  
       change_area.Value = change_area.Value  
Next
 
 
 
#5
30.08.2012 19:51:34
задача не совсем простая, особенно если формулы на самом деле - далеко не только =A+B+C  
 
решение макросом возможно, но для общего случая ковыряться очень долго, а конкретно для фиксированного набоа вариантов формул - просто долго.  
 
имхо, имеет смысл один раз ВРУЧНУЮ переделать формулы на нормальные, ссылающиеся при копировании на правильные диапазоны.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#6
31.08.2012 00:30:57
Формулы только a+b+c. Даже более, Nx+Ny+Nz. Буквы одинаковые, цифры разные, это видно в примере.  
Как бы вручную не переделывал копироваться будут коряво! Разница между одной позицией в разных месяцах составляет 9 сторок,а в исходных данных - 1.  
Перед цифрами поставил "долар" и после копирования ручками прибавляю по единичке к каждой цифре.
 
 
 
#7
31.08.2012 00:39:17
Еще мысль пришла...  
Если скопировать таблицу P7:Y14 в примере не на тот же лист, а на новый, но вставлять в ячейку P8 (долары из формул естественно убрать). Теоретически получится смещение на 1 ячейку. Вопрос Как после такого зафиксировать формулы, чтоб при копировании на нужный лист они не сместились.
 
 
 
#8
31.08.2012 01:35:40
Гм... а мне кажется, не очень-то и сложно)    
Макросом:Счетчик в цикле - это шаг смещения строк в исходной формуле, вставка формулы - строго через 9 строк. Изначально составляется массив из формул, которые затем разбиваются по адресам, корректируются на 1 строку вниз, соединяются и вставляются в новую таблицу.    
Формулами:Использовать функцию СМЕЩ, задавая смещение строк на целую часть (х/9), например =СМЕЩ($N$7;СТРОКА(A1)/9;)+СМЕЩ($N$16;СТРОКА(A1)/9;) и дальше копировать куда надо)  
По Вашей идее - нужно адреса новых формул опять делать абсолютными. Неуниверсально имхо.
 
 
 
#9
31.08.2012 01:39:20
ЗЫ. Хотя... Хотя думаю можно и Вашу идею реализовать - с учетом неизменности столбцов для "абсолютизирования" адресов достаточно будет во всей таблице заменить N на $N$.
 
 
 
#10
31.08.2012 04:49:36
с учетом ограничения на тип формул задача упрощается, но реализовывать макрос всё равно лениво.  
посмотрите вариант формулами, которые можно спокойно копировать.  
(нужно добавить три формулы во вспомогательных столбцах и пару вспомогательных ячеек, а также почистить все нечисловые данные в столбце N).  
 
пс. в принципе, нечисловые заголовки типа "Сумм" можно и не очищать, но формула становится чуть длиннее и требует ввода как формулы массива.  
 
ппс. организация данных - чудовищная!!!
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#11
31.08.2012 17:11:54
Не универсально, но сработало CTRL+H (убрать баксы), CTRL+C, CTRL+V на новый лист, CTRL+X, CTRL+V на прошлый. При вырезании смещения не происходит! :)  
В следюущий раз долары убирать не нужно.  
 
Теперь все таки хочется добить автоматику.  
Получается примерно так:  
 
Dim Formulas As Range  
For Each Formulas In Selection  
  Text_Line = Formulas.FormulaR1C1  
  массив = Split(Text_Line  (что то в этом духе))  
'  Разобрать на массив от начала до числа, число, все что есть до следующего числа, число......, последнее число  
'  Формула от простой ссылки до суммы четырех  
  For j = 0 to UBound (массив)      
     If массив(j) это число(забыл :( как проверить) then массив(j)=массив(j)+1  
'     Если элемент число, то прибавить один  
  Next j  
  Formulas.FormulaR1C1 = Join(массив)  
Next
 
 
 
#12
31.08.2012 22:08:28
Да, вырезать не пробовал, только копировать, поэтому и смотрел в сторону абсолютных адресов) По поводу автоматизации - почти так, только выделять лучше не числа, а адреса диапазонов, потом через range(выделенный_адрес).offset(1).address получать новый и снова их склеивать через символ "+".
 
 
 
Страницы: 1
Читают тему
Наверх