Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Отображать в ячейках диапазона формулы
 
#1
18.01.2020 23:26:42
Добрый вечер! Подскажите пожалуйста, как так преобразовать мне выражение, чтобы в конечном итоге в ячейках диапазона "Сумма" были указаны не конечные значения, а формула со ссылками на ячейки.

Попытка написать сумма.FormulaLocal , сумма.Formula не приводят к желаемому результату.
Код
Sub Вставка(Область, a As Range, b As Range, x, y)
Dim Объемные_величины As Worksheet
Dim сумма As Range

ThisWorkbook.Worksheets("объемные величины").Activate
Set Объемные_величины = ActiveSheet
Область.Copy Объемные_величины.Cells(2, 1)
Set сумма = Объемные_величины.Range(Cells(2, 7), Cells(x, y))
сумма = Evaluate(a.Address(, , , True) & "+" & b.Address(, , , True))

End Sub
 
 
 
#2
19.01.2020 00:41:44
Добрый вечер,

Можете ли вы привести какой-либо пример данных с желаемым результатом ?
 
 
 
#3
19.01.2020 01:17:35
Я тогда лучше сам файл загружу для наглядности.
Есть две исходные вкладки Гвс и Гвс_Одн и вкладка "Объемные величины", в которую выгружаются обработанные данные.
Обработка заключается в том, что суммируются начисления по ГВС и ГВС_ОДН по каждому лицевому счету по каждому месяцу и полученная сумма делится на Тариф (указан как константа в модуле). На какой тариф делится зависит от того, для какого месяца производится вычисления.

Я уже немного переписала код, добавила операцию деления и вообще суммирую уже данные не в диапазонах, а в массиве.
Хочу, чтобы например в январе 2019 по первому лицевому счету в ячейке была формула, например, "=(598,4+30,99)/3000" или ссылками на ячейки.
Как это сделать когда операции на данными выполняются в массиве и потом помещаются в диапазон или как в первом варианте кода, когда применятся Evaluate? И можно ли вообще?
 
 
 
#4
19.01.2020 11:32:32
maria_gug,
На вкладках Гвс и Гвс_Одн в столбце F № лицевого счета встречается один раз?
На вкладке ГВС в ячейке Н1 видимо должен быть месяц январь?
Почему все лицевые счета 9-ти значные, а два счета 0 и 1 (ячейки F2 и F66) ?
Зачем в ячейки прописывать формулы, если значения можно прописать макросом?
Правильно ли я понимаю, что до "01.07.2019" применяется Тариф1=3000, а после 3200 ?
Зачем в файле вкладка "отопление" ?
 
 
 
#5
19.01.2020 13:11:40
Итак по порядку:
1. На вкладках Гвс и Гвс_Одн в столбце F № лицевого счета встречается один раз?
Если имеется ввиду уникальны ли номера лицевых счетов, то да, они не повторяются. Вы, наверное, спрашиваете к тому, что если, они уникальны, зачем тогда делать составной "ключ" , сцепляя все данные по лицевому счету? Просто данный макрос будет в дальнейшем использоваться для лицевых счетов, выгруженных из двух разных баз данных и, вполне возможно, что номера лицевых могут совпасть. Составной "ключ" будет всегда уникальным.

2. На вкладке ГВС в ячейке Н1 видимо должен быть месяц январь?
Да, должен быть январь)

3. Почему все лицевые счета 9-ти значные, а два счета 0 и 1 (ячейки F2 и F66) ?
Это я так проверяла работает ли макрос если данные в ячейках не совпадают. В одной поставила 1-цу в другой оставила нормальный лицевой, все срабатывает и я просто заменила их оба на 1, потом поменяю на нормальные лицевые.

4. Зачем в ячейки прописывать формулы, если значения можно прописать макросом?
Этот файл будет направятся в контролирующие органы, и все вычисляемые ячейки, должны содержать формулы (желательно со ссылками на ячейки, или хотя бы со значениями). Если там будут просто конечные значения, как сейчас, то органы будут очень недовольны, потому что нет наглядности. А макрос смотреть они не будут, конечно же.


5.Правильно ли я понимаю, что до "01.07.2019" применяется Тариф1=3000, а после 3200 ?
Да, правильно.

6.Зачем в файле вкладка "отопление" ?

Данные по отоплению нужны для дальнейших расчетов. Будет также вычисляться, сколько Гкал пошло на отопление, путем деления данных на тарифы соответствующие.
 
 
 
#6
19.01.2020 13:25:20
maria_gug,
Желательно придерживаться одной размерности отображения месяцев,
сделайте на вкладках Гвс и Гвс_Одн строку с месяцами как и на вкладке "объемные величины" 01.01.2019 и т.д.
Попробуйте такой макрос, запускать при активном листе "объемные величины"
Код
Sub iSumma()
Dim i As Long
Dim iLastRow As Long
Dim j As Integer
Dim n As Integer
Dim FoundCell As Range
Dim FoundMonth As Range
Dim Tarif As Double
Dim ArrList
Application.ScreenUpdating = False
   ArrList = Array("ГВС", "ГВС ОДН")
   iLastRow = Cells(Rows.Count, "F").End(xlUp).Row
   Range("G2:R" & iLastRow).ClearContents    'очищаем область данных
  For j = 7 To 18                                           'цикл по месяцам
    For i = 2 To iLastRow                                'цикл по лицевым счетам
      If Month(Cells(1, j)) < 7 Then
        Tarif = 3000
      Else
        Tarif = 3200
      End If
     For n = 0 To UBound(ArrList)                  'цикл по листам
      With Worksheets(ArrList(n))
       Set FoundCell = .Columns(6).Find(Cells(i, "F"), , xlValues, xlWhole)
       If Not FoundCell Is Nothing Then
         Set FoundMonth = .Rows(1).Find(Cells(1, j), , xlFormulas, xlWhole)
          Cells(i, j) = Cells(i, j) + .Cells(FoundCell.Row, FoundMonth.Column)
       End If
      End With
     Next
      Cells(i, j) = Cells(i, j) / Tarif
    Next
  Next
Application.ScreenUpdating = True
End Sub

Затем посмотрим как сделать с формулами или вы сами это сделаете.
Удачи!
 
 
 
#7
19.01.2020 14:23:03
Спасибо, большое! Попробую,потом отпишусь.
 
 
 
#8
19.01.2020 16:45:28
Kuzmich,
добавила ваш макрос все работает.
У меня правда несколько вопросов по нему.6
1. Зачем принципиально использовать метод Find, когда присваиваем значение переменным  FoundCell и FoundMonth.
2. И почему при определении переменной FoundMonth в методе find параметр LookIn=xlFormula?


Как сделать формулами, пока не поняла.
 
 
 
#9
19.01.2020 17:04:35
maria_gug,
Метод Find возвращает объект Range, у которого можно выделить и строку, и столбец.
При дате типа 01.01.2019 в методе Find использую LookIn=xlFormulas,
т.к. при LookIn=xlValues метод Find выдает Nothing. Почему - не знаю.
 
 
 
#10
21.01.2020 17:25:41
Kuzmich,
Дошла до того, что попытке прописать формулу при выполнении макроса в ячейке появляется "#ЗНАЧ!" :

Может, подскажЕте, в какую сторону думать, чтоб в конечном итоге появились человеческие формулы?
Код
For n = 0 To UBound(ArrList)                  &#39;цикл по листам
      With Worksheets(ArrList(n))
       Set FoundCell = .Columns(6).Find(Cells(i, "F"), , xlValues, xlWhole)
       If Not FoundCell Is Nothing Then
         Set FoundMonth = .Rows(1).Find(Cells(1, j), , xlFormulas, xlWhole)
          Cells(i, j) = Evaluate(Cells(i, j).Address + .Cells(FoundCell.Row, FoundMonth.Column).Address)
       End If
      End With
     Next
 
 
 
#11
21.01.2020 17:30:34
maria_gug,
Цитата
чтоб в конечном итоге появились человеческие формулы?
Пробуйте
Код
Sub iSumma()
Dim i As Long
Dim iLastRow As Long
Dim j As Integer
Dim n As Integer
Dim FoundCell As Range
Dim FoundMonth As Range
Dim Tarif As Double
Dim ArrList
Dim iFormula As String
Application.ScreenUpdating = False
   ArrList = Array("ГВС", "ГВС ОДН")
   iLastRow = Cells(Rows.Count, "F").End(xlUp).Row
   Range("G2:R" & iLastRow).ClearContents    'очищаем область данных
  For j = 7 To 18                            'цикл по месяцам
    For i = 2 To iLastRow                    'цикл по лицевым счетам
      If Month(Cells(1, j)) < 7 Then
        Tarif = 3000
      Else
        Tarif = 3200
      End If
     For n = 0 To UBound(ArrList)            'цикл по листам
      With Worksheets(ArrList(n))
       Set FoundCell = .Columns(6).Find(Cells(i, "F"), , xlValues, xlWhole)
       If Not FoundCell Is Nothing Then
         Set FoundMonth = .Rows(1).Find(Cells(1, j), , xlFormulas, xlWhole)
          'Cells(i, j) = Cells(i, j) + .Cells(FoundCell.Row, FoundMonth.Column)
iFormula = iFormula & "'" & ArrList(n) & "'!" & .Cells(FoundCell.Row, FoundMonth.Column).Address(0, 0) & "+"
       End If
      End With
     Next
      'Cells(i, j) = Cells(i, j) / Tarif
      Cells(i, j).Formula = "=(" & Left(iFormula, Len(iFormula) - 1) & ")/" & Tarif
      iFormula = ""
    Next
  Next
Application.ScreenUpdating = True
End Sub
 
 
 
#12
21.01.2020 17:32:00
Цитата
maria_gug написал:
чтоб в конечном итоге появились человеческие формулы?
так надо их записывать хотя бы. Хоть как-то. А Вы их внутри кода вычисляете через Evaluate, да еще криво. Вот так будет формула:
Код
Cells(i, j).Formula = "=" & Cells(i, j).Address & "+" & .Cells(FoundCell.Row, FoundMonth.Column).Address
но правильная или нет - даже вникнуть не пытался.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#13
21.01.2020 17:45:28
Kuzmich,
Работает, только теперь в ячейках вставлены формулы, а надо что бы были значения, а щелкаешь по ней и на формулу показывает, что из чего получилось.

А вообще конечно у меня нет слов.
Вы так быстро ответили и такую красоту написали. Спасибо большое!
 
 
 
#14
21.01.2020 17:53:05
maria_gug,
Цитата
только теперь в ячейках вставлены формулы, а надо что бы были значения
На листе "объемные величины" в ячейках отображаются значения, но если выделить какую-либо
ячейку, то в строке формул мы видим формулу.
 
 
 
#15
21.01.2020 18:12:56
Kuzmich,

Все заработало, у меня в Excel просто включена была кнопка "Показать формулы".
Спасибо огромное за помощь!
Изменено: maria_gug - 21.01.2020 18:25:07
 
 
 
Страницы: 1
Наверх