Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Как перевести функцию СРЗНАЧЕСЛИМН в Макрос?, Макрос функции СРЗНАЧЕСЛИМН при 3 критериях
 
#1
01.04.2020 12:31:31
Код
Sub Debitor()
    
    With Worksheets("Reyestr")

  Range("H2") = Application.WorksheetFunction.SumIfs(.Columns("X"), .Columns("M"), Range("D2"), .Columns("AI"), ">=" & Range("$AC$4"), .Columns("AI"), "<=" & Range("$AC$6"))
  Range("J9") = Application.WorksheetFunction.SumIfs(.Columns("N"), .Columns("M"), Range("D2"), .Columns("AI"), ">=" & Range("$AC$4"), .Columns("AI"), "<=" & Range("$AC$6"))
  Range("J10") = Application.WorksheetFunction.SumIfs(.Columns("O"), .Columns("M"), Range("D2"), .Columns("AI"), ">=" & Range("$AC$4"), .Columns("AI"), "<=" & Range("$AC$6"))
  Range("J11") = Application.WorksheetFunction.SumIfs(.Columns("P"), .Columns("M"), Range("D2"), .Columns("AI"), ">=" & Range("$AC$4"), .Columns("AI"), "<=" & Range("$AC$6"))
  Range("J12") = Application.WorksheetFunction.SumIfs(.Columns("Q"), .Columns("M"), Range("D2"), .Columns("AI"), ">=" & Range("$AC$4"), .Columns("AI"), "<=" & Range("$AC$6"))
  Range("J13") = Application.WorksheetFunction.SumIfs(.Columns("R"), .Columns("M"), Range("D2"), .Columns("AI"), ">=" & Range("$AC$4"), .Columns("AI"), "<=" & Range("$AC$6"))
  Range("J14") = Application.WorksheetFunction.SumIfs(.Columns("S"), .Columns("M"), Range("D2"), .Columns("AI"), ">=" & Range("$AC$4"), .Columns("AI"), "<=" & Range("$AC$6"))
  Range("J15") = Application.WorksheetFunction.SumIfs(.Columns("T"), .Columns("M"), Range("D2"), .Columns("AI"), ">=" & Range("$AC$4"), .Columns("AI"), "<=" & Range("$AC$6"))
  Range("J16") = Application.WorksheetFunction.SumIfs(.Columns("U"), .Columns("M"), Range("D2"), .Columns("AI"), ">=" & Range("$AC$4"), .Columns("AI"), "<=" & Range("$AC$6"))
  Range("J17") = Application.WorksheetFunction.SumIfs(.Columns("V"), .Columns("M"), Range("D2"), .Columns("AI"), ">=" & Range("$AC$4"), .Columns("AI"), "<=" & Range("$AC$6"))
  Range("J18") = Application.WorksheetFunction.SumIfs(.Columns("W"), .Columns("M"), Range("D2"), .Columns("AI"), ">=" & Range("$AC$4"), .Columns("AI"), "<=" & Range("$AC$6"))
  Range("H9") = Application.WorksheetFunction.AverageIfs(.Columns("N"), .Columns("M"), Range("D2"), .Columns("AI"), ">=" & Range("$AC$4"), .Columns("AI"), "<=" & Range("$AC$6"))
  'Range("H10") = Application.WorksheetFunction.AverageIfs(.Columns("O"), .Columns("M"), Range("D2"), .Columns("AI"), ">=" & Range("$AC$4"), .Columns("AI"), "<=" & Range("$AC$6"))  
End With
  
    Range("K2").Select
    ActiveCell.FormulaR1C1 = "=NOW()"
    Range("A17").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    MsgBox "Ñïàñèáî çà âíèìàòåëüíîñòü !!!", vbCritical

End Sub
   Здравствуйте уважаемые формучане.
И вновь я обращаюсь к Вам за помощью. В приложении высылаю файл (пример) где основные рабочие страницы Reyestr и  Selective Debit Report. Если просмотрите макрос то заметите что при вычислении среднего значения (страница  Selective Debit Report, ячейка H9)  операция проходит успешно, но стоит повторить операцию и применить ее к ячейке H10, возникает ошибка. Не вразумлю причину. На всякий случай прописываю макрос сюда


С уважением Халил Халилов.
 
 
 
#2
01.04.2020 12:40:20
Халил, WorksheetFunction.AverageIfs - это разве не
Цитата
Халил написал:
Как перевести функцию СРЗНАЧЕСЛИМН в Макрос?,
а можно просто на словах что нужно, как нужно и куда, а не что бы сидеть и разбирать ваш макрос
Изменено: Mershik - 01.04.2020 12:43:38
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#3
01.04.2020 16:36:08
Еще раз добрый день и желаю завершить карантин с меньшими потерями.

      Если Вы ознакомились с файлом, мне необходимо, что бы при прожатии кнопки на листе  "Selective Debit Report" в столбце "H" под заголовком "Average" вычислялись средние значения из листа "Reyestr" , по критериям :
Критерий № 1 - Под заголовком "Type of spend" в соответствующем порядке в диапазоне D9:D18
Критерий № 2.- Временной промежуток указанный перед терминами "Start date" ячейка "D4" и "Final date" ячейка "D6"
Критерий № 3 - Переменная в ячейке "D2"
Как видите в ячейке "H9" средняя подсчитана. Тем же методом прописываю и на последующие ниже ячейки ( H10:H18) ,но выявляет ошибку , которую не могу определить.
Поиск Критерия № 1.1 ячейка ("D9")  - на листе "Reyestr" в столбце "N"
Поиск Критерия №1.2 ячейка ("D10") - на листе "Reyestr" в столбце "О"
( И так далее ..... отступ вниз на 1 ячейку на листе "Selective Debit Report" = отступу на 1 столбец вправо на листе "Reyestr")
Поиск Критерия № 2  - столбец "С" на листе "Reyestr"
Поиск Критерия № 3 - столбец "М" на листе "Reyestr"
С уважением Халил Халилов.
Изменено: Халил - 01.04.2020 16:57:52
 
 
 
#4
01.04.2020 16:42:04
Цитата
Халил написал:
На всякий случай прописываю макрос сюда
вы бы на всякий случай описали задачу
среднее значение чего вы пытаетесь найти
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#5
01.04.2020 16:55:42
Средние значения столбцов N,...., W по двум условиям"X" и "Y"
X = D2
D4 <= Y => D6

P.S. К моему сожалению политика форума, кажется,  запрещает непосредственный обмен контактами, так как словами я мог бы доступно объяснить проблему.
Сложно парой написать свою мысль, проживая не в России и меть ограниченные возможность в использовании русского языка в письменном виде.  
Изменено: Халил - 01.04.2020 17:10:00
 
 
 
#6
01.04.2020 17:37:59
Код
Function AvrForPeriod#(d1 As Date, d2 As Date)
  Dim d, v, c&, r&, rg As Range, s#, n&
  With Worksheets(2)
  Set rg = Range(.Cells(2, 3), .Cells(Rows.Count, 3).End(xlUp))
  d = rg
  v = Intersect(rg.EntireRow, .Range("N:W"))
  End With
  For r = 1 To UBound(d)
    If d(r, 1) >= d1 And d(r, 1) <= d2 Then
      For c = 1 To UBound(v, 2)
        If Not IsEmpty(v(r, c)) Then
          s = s + v(r, c): n = n + 1
        End If
      Next
    End If
  Next
  If n > 0 Then AvrForPeriod = s / n
End Function
см. лист макрос
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#7
01.04.2020 18:07:36
Это следовало ожидать. Я толком не смог изложить свою мысль, за что прошу прощения и благодарен за скорейший отклик. Вы нашли среднюю всех чисел столбцов N:W по критерию указываемого промежутка . Но задача состояла в том что бы в ячейке H9 листа Reyestr подсчитывалась средняя по столбцу N , в H10 листа Reyestr по столбцу О листа  Selective Debit Report ,в H11 листа Reyestr по столбцу Р листа  Selective Debit Report , в ячейке H12 листа Reyestr по столбцу Q листа  Selective Debit Report , в ячейке H13 листа Reyestr по столбцу R листа  Selective Debit Report , в ячейке H14 листа Reyestr  по столбцу S листа  Selective Debit Report, в ячейке H15 листа Reyestr по столбцу T листа  Selective Debit Report и так еще до H18. Во всех случаях условия одни и те же :
Условие 1 - Ячейка D2 диапазон условия столбец М
Условие 2 - промежуток между датами ячейки D4 и D6 , диапазон условия столбец С
Хотелось бы заметить что, в том макросе что я посылал выше,проблема в ниже приведенной части. На основе первой строки вычисления проходят, а вот вторая  (выделенная апострофом) строка ошибочная. В написание она идентична первой, я лишь изменил H9 на H10 (ячейка куда будет вписано среднее значение) и столбец N на столбец O (диапазон суммирования). Но не смотря на идентичность выдает ошибку.
Код
Range("H9") = Application.WorksheetFunction.AverageIfs(.Columns("N"), .Columns("M"), Range("D2"), .Columns("AI"), ">=" & Range("$AC$4"), .Columns("AI"), "<=" & Range("$AC$6"))
  'Range("H10") = Application.WorksheetFunction.AverageIfs(.Columns("O"), .Columns("M"), Range("D2"), .Columns("AI"), ">=" & Range("$AC$4"), .Columns("AI"), "<=" & Range("$AC$6"))  
Ваши знания VBA  на неизмеримую часть превышают мои и если искренне признаться, я смотря на ваш макрос не могу прочесть его, соответственно самолично адаптировать под свои нужды не смогу (((
С уважением Халил Халилов
Изменено: Халил - 01.04.2020 18:18:12
 
 
 
#8
01.04.2020 19:03:32
внесите данные на лист Условия
выполните AvrForPeriod
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#9
01.04.2020 21:16:07
Добрый вечер.
Еще раз спасибо за содействие. Я нашел причину почему подсчет не шел. В столбцах где не было информации , даже нуля, он выдавал ошибку. Стоило мне заменить пустоты на ноль, как мой макрос заработал. Еще раз большое спасибо.
С уважением Халил Халилов.
 
 
 
Страницы: 1
Наверх