Новости:

Прикрепить к сообщению можно только файлы xls, gif, jpg, rar, zip,7z, bas, frm, cls, doc размером до 150 Кб.

Главное меню

Автоматизация расчетов с добавлением ячеек

Автор vater, 12.07.2012, 19:36

« назад - далее »

vater

Здравствуйте, столкнулся с проблемой с первичными расчетами. В прикрепленном файле есть 3 листа:


  • "Исходные данные": то как выглядит лист в исходном состоянии, тут ничего примечательно, просто для ознакомления.
  • "Промежуточный": создан для того, чтобы было видно как происходит расчет. Из "формы для расчетов" выбираются два региона соответствующие дате под номером 5, и по нажатию кнопки "расчет" происходит копирование значений из ячеек J3:J5 в F14:H14 (данные не влияют на другие ячейки), после этого заносятся значения из "известные данные" в соответствующую ей строку в ячейки M:N (M14:H14 в данном примере). Далее все повторяется, но уже со следующей строкой.
  • "Конечный результат": то как выглядит лист после ручного расчета.

Проблема состоит в имитации выбора региона, т.к. все остальное можно, вроде, сделать записав макрос. Пробовал сделать соотношение текстового названия региона его номерному значению, но не разобрался как его использовать в макросе, да и может, он вообе не нужен. На все остальное знаний нехватает.

Благодарен за любую помощь, надеюсь суть проблемы понятна.

Шпец Докапыч

"Имитация выбора" = презентация? Т.е. цель - некая демонстрация заполнения таблицы? Тогда рекомендую использовать Сервис → Сценарии. У меня как раз где-то завалялся интересный примерчик. Текст в Раскрывающемся списке меняется с изменением числа в связанной ячейке (B1, B2).

Либо я не смог догадаться какая функциональность требуется (значения в каких ячейках и фигурах менять автоматом). В этом случае рекомендую целевые объекты для пояснения выделить цветом.
Знания недостаточно, необходимо применение. Желания недостаточно, необходимо действие. (с) Брюс Ли

vater

К сожалению сценарии не подходят, хотя я о них мало что знаю. Автоматизация у меня не получается из-за того, что я не знаю как макросу указать на то, какой регион я выбираю. Под имитацией я имел ввиду замену текстовых значений региона на числовые по формуле "СУММЕСЛИ", которые компьютеру более понятны.
Наверно, главная проблема в том, чтобы каждую итерацию, макрос менял значения в ячейках B1:B2 (связующие ячейки в выпадающих списках) на числовые значения L14 и O14 (названия регионов соответствующие дате).
В приложенном файле изменен 2 лист, там добавлен список ручных действий, которые и нужно автоматизировать.

Шпец Докапыч

Цитата: vater от 14.07.2012, 12:40
Наверно, главная проблема в том, чтобы каждую итерацию, макрос менял значения в ячейках B1:B2 (связующие ячейки в выпадающих списках) на числовые значения L14 и O14 (названия регионов соответствующие дате).
Вот пара вариантов для преобразования названия региона в его числовое значение:
Sub Test()
  MsgBox Reg2Num_1("Central")
  MsgBox Reg2Num_2("Central")
End Sub

Function Reg2Num_1(NameReg As String)
'названия регионов указаны в коде
  Reg2Num_1 = InStr("EWC", Left(NameReg, 1))
End Function

Function Reg2Num_2(NameReg As String, Optional rng As Range)
'использование функции ПОИСКПОЗ()
  If rng Is Nothing Then Set rng = [B4:B6]
  Reg2Num_2 = Application.WorksheetFunction.Match(NameReg, rng, 0)
End Function
Знания недостаточно, необходимо применение. Желания недостаточно, необходимо действие. (с) Брюс Ли

vater

После объяснения второго поста, я маленько разобралсяи решил добавить два новых стобца напротив названий регионов, где через формулу растянул их числовые значения. И через "запись макроса" маленько написал новый макрос:
Sub ÌàêðîñÏåðåíîñà()
Range("R11").Select
'Копирует числовое значение Региона1
    Application.CutCopyMode = False
    Selection.Copy
Range("B1").Select
'Вставляет числовое значение Региона1 в зависимую ячейку списка
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Range("S11").Select
'Копирует числовое значение Региона2
    Application.CutCopyMode = False
    Selection.Copy
Range("B2").Select
'Вставляет числовое значение Региона2 в зависимую ячейку списка
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Dim LastRow As Long
'Выполняет копирование расчетных значений - кнопка "Расчет"
LastRow = Range("F" & Rows.Count).End(xlUp).row
    Cells(LastRow + 1, 6).Value = WorksheetFunction.Round(Range("J3").Value, 2)
    Cells(LastRow + 1, 7).Value = WorksheetFunction.Round(Range("J4").Value, 2)
    Cells(LastRow + 1, 8).Value = WorksheetFunction.Round(Range("J5").Value, 2)
End Sub


Но, я полохо разбираюсь в макросах и, наверно, единственное что осталось, так это сделать так, чтобы копирование ячейки с числовым значением шло вниз, как в макроме "Кнопки расччета" (макрос в конце кода начиная от определения LastRow).

Wasilic

Может и я на что сгожусь ... Если сгодился, можете меня по+благодарить+.

vater

Спасибо, работает!
Подумав, я решил слегка доработать код для больших таблиц, и столкнулся, опять, с непониманием основ. Как обозначить конец цикла не числовыми данными, а например, строкой 19 (читал в интернете про .Row, никак) или же через формулу, которая определяет, когда заканчиваются Регионы? Вот код с числовым вариантом: Sub ÇàïèñàòüÇíà÷åíèÿ()
Dim R As Long
For i = R To 5
    R = Range("F" & Rows.Count).End(xlUp).Row + 1
    Cells(R, 6) = Range("J3")
    Cells(R, 7) = Range("J4")
    Cells(R, 8) = Range("J5")
   
    R = Range("N" & Rows.Count).End(xlUp).Row + 1
    Cells(R, 14) = Cells(R, 18)
    Cells(R, 15) = Cells(R, 19)
   
    Cells(1, 2) = Cells(R + 1, 12)
    Cells(2, 2) = Cells(R + 1, 16)
   
    Cells(13, 2) = Cells(R + 1, 13)
    Cells(13, 4) = Cells(R + 1, 17)
Next i
End Sub

Шпец Докапыч

Цитата: vater от 15.07.2012, 13:10
Как обозначить конец цикла не числовыми данными, а например, строкой 19?..
В решении от Wasilic есть строки, определяющие последние записи в столбцах "F" и "N", достаточно поменять букву на "J". После чего стандартный цикл:
For НомерСтроки = 10 to R
  Cells(НомерСтроки, НомерСтолбца) = ЧтоТо
Next
Знания недостаточно, необходимо применение. Желания недостаточно, необходимо действие. (с) Брюс Ли

vater

Спасибо, но совершенно не понял  :) For НомерСтроки = 10 to R
  Cells(НомерСтроки, НомерСтолбца) = ЧтоТо
Next

Вместо "НомерСтроки" любую переменную ставить? В теле цикла, как я понял, нужно писать любую конечную ячейку, но что ей присвоить?
Думаю. проще считать числовым методом.

Шпец Докапыч

Любую переменную. В качестве конечной ячейки будет, судя по всему, последняя строка (19). В разрезе вышеозвученного решения это будет выглядеть так:
Sub ЗаписатьЗначения()
Dim R As Long
J = Range("J" & Rows.Count).End(xlUp).Row + 1
For i = R To J
    R = Range("F" & Rows.Count).End(xlUp).Row + 1
   '...
Next i
End Sub
Знания недостаточно, необходимо применение. Желания недостаточно, необходимо действие. (с) Брюс Ли

vater

Спасибо за объяснение.
У вас очень хороший форум, вокруг достаточно много полезных тем.

Wasilic

Может и я на что сгожусь ... Если сгодился, можете меня по+благодарить+.