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

Вход

Регистрация

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

 

= Мир MS Excel/Как вставить формулу массива в видимые ячейки фильтра? - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Как вставить формулу массива в видимые ячейки фильтра? (Макросы/Sub)
Как вставить формулу массива в видимые ячейки фильтра?
machodg Дата: Воскресенье, 03.12.2017, 17:33 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Доброго всем вечера!
Во вложении пример, в котором используется формула массива для расчетов в столбце "Н". При ручном введении этой формулы в ячейку столбца, она работает корректно и без ошибок.
При ручном распространении вниз методом CTRL+D при включенном фильтре, формула корректно вставляется в только видимые ячейки выделенного диапазона, как и положено. Но та же формула выдает ошибку при вставке через код VBA.
Я смог релизовать данный алгоритм в VBA и он работает, но код получился слишком "топорным" и остались вопросы. Почему формула выдет ошибку при вставлении в ячейку через код?
И как распрстранить эту формулу массива по столбцу с включенным фильтром только в видимые ячейки? Вырианты кода которые я использовал для этого - не работают (приведены в примере 'зеленым цветом примечаний
Макрос вставлен в модуль листа и при клике на ячейку с надписью "Заказ", срабатывает фильтр и производятся расчеты.
Может кто подскажет, как оптимизировать код и в чем моя ошибка?
Зранее благодарен за ответ.
С Увжением
К сообщению приложен файл: Primer.xlsb (28.5 Kb)
 
Ответить
СообщениеДоброго всем вечера!
Во вложении пример, в котором используется формула массива для расчетов в столбце "Н". При ручном введении этой формулы в ячейку столбца, она работает корректно и без ошибок.
При ручном распространении вниз методом CTRL+D при включенном фильтре, формула корректно вставляется в только видимые ячейки выделенного диапазона, как и положено. Но та же формула выдает ошибку при вставке через код VBA.
Я смог релизовать данный алгоритм в VBA и он работает, но код получился слишком "топорным" и остались вопросы. Почему формула выдет ошибку при вставлении в ячейку через код?
И как распрстранить эту формулу массива по столбцу с включенным фильтром только в видимые ячейки? Вырианты кода которые я использовал для этого - не работают (приведены в примере 'зеленым цветом примечаний
Макрос вставлен в модуль листа и при клике на ячейку с надписью "Заказ", срабатывает фильтр и производятся расчеты.
Может кто подскажет, как оптимизировать код и в чем моя ошибка?
Зранее благодарен за ответ.
С Увжением

Автор - machodg
Дата добавления - 03.12.2017 в 17:33
Nic70y Дата: Воскресенье, 03.12.2017, 20:55 | Сообщение № 2
Группа: Друзья
Ранг: Экселист
Сообщений: 8709
Репутация: 2260 ±
Замечаний: 0% ±

Excel 2010
[vba]
Код
        ActiveCell.FormulaArray = _
            "=SUM(IF(R3C[-5]:R2186C[-5]=RC[-5],R3C[-2]:R2186C[-2]/R3C[-3]:R2186C[-3]*R3C[-1]:R2186C[-1]))/(SUM(IF(R3C[-5]:R2186C[-5]<>"""",R3C[-2]:R2186C[-2]/R3C[-3]:R2186C[-3]*R3C[-1]:R2186C[-1])))"
[/vba]
Код
=СУММ(ЕСЛИ(C$3:C$2186=C4;F$3:F$2186/E$3:E$2186*G$3:G$2186))/(СУММ(ЕСЛИ(C$3:C$2186<>"";F$3:F$2186/E$3:E$2186*G$3:G$2186)))
ну его нафиг формулу массива вставлять макросом %)


ЮMoney 41001841029809
 
Ответить
Сообщение[vba]
Код
        ActiveCell.FormulaArray = _
            "=SUM(IF(R3C[-5]:R2186C[-5]=RC[-5],R3C[-2]:R2186C[-2]/R3C[-3]:R2186C[-3]*R3C[-1]:R2186C[-1]))/(SUM(IF(R3C[-5]:R2186C[-5]<>"""",R3C[-2]:R2186C[-2]/R3C[-3]:R2186C[-3]*R3C[-1]:R2186C[-1])))"
[/vba]
Код
=СУММ(ЕСЛИ(C$3:C$2186=C4;F$3:F$2186/E$3:E$2186*G$3:G$2186))/(СУММ(ЕСЛИ(C$3:C$2186<>"";F$3:F$2186/E$3:E$2186*G$3:G$2186)))
ну его нафиг формулу массива вставлять макросом %)

Автор - Nic70y
Дата добавления - 03.12.2017 в 20:55
machodg Дата: Воскресенье, 03.12.2017, 21:35 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Спасибо,
но к сожалению нужно макросом, т.к. этот фаил пересылается "чайникам", которые должны лишь в столбец "Н" вписать количества и дальше посмотреть на результат путем всего лишь клика на ячейку "Заказ". (на самом деле этот фаил - небольшой фрагмент от большой статистической системы)

Желаю доброй ночи.
 
Ответить
СообщениеСпасибо,
но к сожалению нужно макросом, т.к. этот фаил пересылается "чайникам", которые должны лишь в столбец "Н" вписать количества и дальше посмотреть на результат путем всего лишь клика на ячейку "Заказ". (на самом деле этот фаил - небольшой фрагмент от большой статистической системы)

Желаю доброй ночи.

Автор - machodg
Дата добавления - 03.12.2017 в 21:35
Nic70y Дата: Понедельник, 04.12.2017, 07:40 | Сообщение № 4
Группа: Друзья
Ранг: Экселист
Сообщений: 8709
Репутация: 2260 ±
Замечаний: 0% ±

Excel 2010
к сожалению нужно макросом
Ваш вопрос
Как вставить формулу массива
так что не так?
в столбец "Н"
там у Вас как раз таки формулы массива.
так что же Вам нужно?
К сообщению приложен файл: 4845956.xlsb (25.8 Kb)


ЮMoney 41001841029809
 
Ответить
Сообщение
к сожалению нужно макросом
Ваш вопрос
Как вставить формулу массива
так что не так?
в столбец "Н"
там у Вас как раз таки формулы массива.
так что же Вам нужно?

Автор - Nic70y
Дата добавления - 04.12.2017 в 07:40
machodg Дата: Понедельник, 04.12.2017, 12:49 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Спасибо, что заинтересовались моим вопросом.
Да, действительно, формула в моем примере и макрос при клике на ячейке "Заказ " - работают корректно.
Но я хотел повысить свои знания VBA с помощью знатоков эксела.
Я хотел оптимизировать код и выяснить как составить грамотный код, вставляющий формулу массива в видимые ячейки фключенного фильтра.
Для этого я через код вставил ту же формулу, но в простом виде (без FormulaArray =) в первую ячейку диапазона, распространил на весь доапазон методом Selection.SpecialCells(xlCellTypeVisible).FillDown и далее "топорно" использовал в цикле: [SendKeys "{F2}^+~"], т.е. SHIFT+CTRL+ENTER для перевода постой формулы в формулу массива. (посмотрите код в примере):

[vba]
Код
Set Myrange = Application.Range(Cells(FirstRow, 8), Cells(LastRow, 8))
                
        For Each c In Myrange.SpecialCells(xlVisible)
            c.Select
            With c
                SendKeys "{F2}^+~"
                DoEvents
            End With
        Next c
[/vba]
Хочу правильно это сделать. На форумах нашел конструкции типа:
[vba]
Код
[Range("H" & FirstRow + 1 & ":H" & LastRow).FormulaArray = Range("H" & FirstRow).FormulaArray]
[/vba]
или
[vba]
Код
Selection.SpecialCells(xlCellTypeVisible).FillDown
[/vba]
- не работают с этой формулой.
В чем проблема не могу понять?
Есть идеи?
Заранее благодарен
Доброго дня.


Сообщение отредактировал machodg - Вторник, 05.12.2017, 01:26
 
Ответить
СообщениеСпасибо, что заинтересовались моим вопросом.
Да, действительно, формула в моем примере и макрос при клике на ячейке "Заказ " - работают корректно.
Но я хотел повысить свои знания VBA с помощью знатоков эксела.
Я хотел оптимизировать код и выяснить как составить грамотный код, вставляющий формулу массива в видимые ячейки фключенного фильтра.
Для этого я через код вставил ту же формулу, но в простом виде (без FormulaArray =) в первую ячейку диапазона, распространил на весь доапазон методом Selection.SpecialCells(xlCellTypeVisible).FillDown и далее "топорно" использовал в цикле: [SendKeys "{F2}^+~"], т.е. SHIFT+CTRL+ENTER для перевода постой формулы в формулу массива. (посмотрите код в примере):

[vba]
Код
Set Myrange = Application.Range(Cells(FirstRow, 8), Cells(LastRow, 8))
                
        For Each c In Myrange.SpecialCells(xlVisible)
            c.Select
            With c
                SendKeys "{F2}^+~"
                DoEvents
            End With
        Next c
[/vba]
Хочу правильно это сделать. На форумах нашел конструкции типа:
[vba]
Код
[Range("H" & FirstRow + 1 & ":H" & LastRow).FormulaArray = Range("H" & FirstRow).FormulaArray]
[/vba]
или
[vba]
Код
Selection.SpecialCells(xlCellTypeVisible).FillDown
[/vba]
- не работают с этой формулой.
В чем проблема не могу понять?
Есть идеи?
Заранее благодарен
Доброго дня.

Автор - machodg
Дата добавления - 04.12.2017 в 12:49
machodg Дата: Понедельник, 04.12.2017, 13:03 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Кастати, на предварительном просмотре моего ответа, код. вставленный в тег "Код VBA" выглядел нормально, а на форуме - в одну сторчку.
В идимо неправильно вставляю.
Вот в эту конструкцию тега- [vba][code][/code][/vba], куда именно нужно вставлять код, чтобы он выглядел нормально?
Подскажите пожалуйста
С уважением
 
Ответить
СообщениеКастати, на предварительном просмотре моего ответа, код. вставленный в тег "Код VBA" выглядел нормально, а на форуме - в одну сторчку.
В идимо неправильно вставляю.
Вот в эту конструкцию тега- [vba][code][/code][/vba], куда именно нужно вставлять код, чтобы он выглядел нормально?
Подскажите пожалуйста
С уважением

Автор - machodg
Дата добавления - 04.12.2017 в 13:03
Serge_007 Дата: Понедельник, 04.12.2017, 14:15 | Сообщение № 7
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2748 ±
Замечаний: ±

Excel 2016
Вы использовали теги [code][/code] вместо [vba][code][/code][/vba]
Поправил


ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
СообщениеВы использовали теги [code][/code] вместо [vba][code][/code][/vba]
Поправил

Автор - Serge_007
Дата добавления - 04.12.2017 в 14:15
sboy Дата: Понедельник, 04.12.2017, 14:23 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2566
Репутация: 724 ±
Замечаний: 0% ±

Excel 2010
Есть идеи?

Доброго дня.
Как вариант, организуйте цикл по ячейкам. Если ячейка видима, то FormulaArray.


Яндекс: 410016850021169
 
Ответить
Сообщение
Есть идеи?

Доброго дня.
Как вариант, организуйте цикл по ячейкам. Если ячейка видима, то FormulaArray.

Автор - sboy
Дата добавления - 04.12.2017 в 14:23
bmv98rus Дата: Понедельник, 04.12.2017, 15:46 | Сообщение № 9
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4099
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
machodg,
Вы используете ФООРМУЛУ В R1C1 стиле и это правильно с точки зрения относительных ссылок, но FormulaArray ждет формулу в A1 стиле. возможно вам следует преобразовать её сперва, а потом уже вставлять, после чего и Fill Down пройдет
для получения адресов можно использовать range.address получить адрес из номера строки и столбца с учетом стиля и абсолютных адресов,


Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Понедельник, 04.12.2017, 20:16
 
Ответить
Сообщениеmachodg,
Вы используете ФООРМУЛУ В R1C1 стиле и это правильно с точки зрения относительных ссылок, но FormulaArray ждет формулу в A1 стиле. возможно вам следует преобразовать её сперва, а потом уже вставлять, после чего и Fill Down пройдет
для получения адресов можно использовать range.address получить адрес из номера строки и столбца с учетом стиля и абсолютных адресов,

Автор - bmv98rus
Дата добавления - 04.12.2017 в 15:46
Nic70y Дата: Понедельник, 04.12.2017, 16:07 | Сообщение № 10
Группа: Друзья
Ранг: Экселист
Сообщений: 8709
Репутация: 2260 ±
Замечаний: 0% ±

Excel 2010
не работают с этой формулой
с какой?


ЮMoney 41001841029809
 
Ответить
Сообщение
не работают с этой формулой
с какой?

Автор - Nic70y
Дата добавления - 04.12.2017 в 16:07
_Boroda_ Дата: Понедельник, 04.12.2017, 17:59 | Сообщение № 11
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Так нужно?
В свой макрос сами вставьте
[vba]
Код
Sub tt()
    Application.ScreenUpdating = 0
    Application.Calculation = xlCalculationManual
    With ActiveSheet.AutoFilter.Range
        For Each r In .Rows(2).Columns(8).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Cells
            r.FormulaArray = _
                "=SUM(IF(R3C[-5]:R2186C[-5]=RC[-5],R3C[-2]:R2186C[-2]/R3C[-3]:R2186C[-3]*R3C[-1]:R2186C[-1]))/(SUM(IF(R3C[-5]:R2186C[-5]<>"""",R3C[-2]:R2186C[-2]/R3C[-3]:R2186C[-3]*R3C[-1]:R2186C[-1])))"
        Next
    End With
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = 1
End Sub
[/vba]


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеТак нужно?
В свой макрос сами вставьте
[vba]
Код
Sub tt()
    Application.ScreenUpdating = 0
    Application.Calculation = xlCalculationManual
    With ActiveSheet.AutoFilter.Range
        For Each r In .Rows(2).Columns(8).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Cells
            r.FormulaArray = _
                "=SUM(IF(R3C[-5]:R2186C[-5]=RC[-5],R3C[-2]:R2186C[-2]/R3C[-3]:R2186C[-3]*R3C[-1]:R2186C[-1]))/(SUM(IF(R3C[-5]:R2186C[-5]<>"""",R3C[-2]:R2186C[-2]/R3C[-3]:R2186C[-3]*R3C[-1]:R2186C[-1])))"
        Next
    End With
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = 1
End Sub
[/vba]

Автор - _Boroda_
Дата добавления - 04.12.2017 в 17:59
machodg Дата: Вторник, 05.12.2017, 02:04 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Огромное всем спасибо!!!
Особенно Вам Nic70y, и _Boroda_. Вся проблема видимо была в моей формуле. Формула оказывается не полностью удовлетворяла правилам написания формулы массива. Благодаря Вашему варианту, окончательный код получился очень шустрый. Чуть допилил и сработала процедура:
[vba]
Код
Selection.SpecialCells(xlCellTypeVisible).FillDown
[/vba]
Соответственно и цикл по заполнению ячеек не понадобился:

[vba]
Код
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim FirstRow, LastRow As Integer

If Target.Address = "$G$2" And Cells(1, 1) = 1 Then
        Cells(1, 1) = 0
        Application.ScreenUpdating = 0
        With Application
            .Calculation = xlAutomatic
            .MaxChange = 0.001
        End With
       
            If ActiveSheet.FilterMode = True Then
                 ActiveSheet.ShowAllData
            End If
        
            If Cells(3, 7) = 0 Or Cells(3, 7) = "" Then
                FirstRow = Range("G2").End(xlDown).Row
                Else
                FirstRow = 3
            End If
                LastRow = Columns(7).Rows(1040000).End(xlUp).Row
            
'1) Ustanovka AutoFiltra
                 Selection.AutoFilter Field:=7, Criteria1:="<>"
            
'2)Vstavka formul v vidimie iacheiki
           
           Cells(FirstRow, 9) = _
"=IF(RC7<>0,IF(RC3=""A"",SUMIF(R3C3:R2186C3,""A"",R3C7:R2186C7),IF(RC3=""B"",SUMIF(R3C3:R2186C3,""B"",R3C7:R2186C7),SUMIF(R3C3:R2186C3,""C"",R3C7:R2186C7)))/SUM(R3C7:R2186C7),""-"")"
    
            Range(Cells(FirstRow, 9), Cells(LastRow, 9)).Select
            Selection.SpecialCells(xlCellTypeVisible).FillDown
        
        Cells(FirstRow, 8).FormulaArray = _
"=SUM(IF(R3C[-5]:R2186C[-5]=RC[-5],R3C[-2]:R2186C[-2]/R3C[-3]:R2186C[-3]*R3C[-1]:R2186C[-1]))/(SUM(IF(R3C[-5]:R2186C[-5]<>"""",R3C[-2]:R2186C[-2]/R3C[-3]:R2186C[-3]*R3C[-1]:R2186C[-1])))"
        
        Range(Cells(FirstRow, 8), Cells(LastRow, 8)).Select
        Selection.SpecialCells(xlCellTypeVisible).FillDown
        
    End If
Cells(1, 1) = 1
Application.ScreenUpdating = 1
End Sub
[/vba]

Еще раз большое спасибо.
Всем желаю успехов.


Сообщение отредактировал machodg - Вторник, 05.12.2017, 02:12
 
Ответить
СообщениеОгромное всем спасибо!!!
Особенно Вам Nic70y, и _Boroda_. Вся проблема видимо была в моей формуле. Формула оказывается не полностью удовлетворяла правилам написания формулы массива. Благодаря Вашему варианту, окончательный код получился очень шустрый. Чуть допилил и сработала процедура:
[vba]
Код
Selection.SpecialCells(xlCellTypeVisible).FillDown
[/vba]
Соответственно и цикл по заполнению ячеек не понадобился:

[vba]
Код
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim FirstRow, LastRow As Integer

If Target.Address = "$G$2" And Cells(1, 1) = 1 Then
        Cells(1, 1) = 0
        Application.ScreenUpdating = 0
        With Application
            .Calculation = xlAutomatic
            .MaxChange = 0.001
        End With
       
            If ActiveSheet.FilterMode = True Then
                 ActiveSheet.ShowAllData
            End If
        
            If Cells(3, 7) = 0 Or Cells(3, 7) = "" Then
                FirstRow = Range("G2").End(xlDown).Row
                Else
                FirstRow = 3
            End If
                LastRow = Columns(7).Rows(1040000).End(xlUp).Row
            
'1) Ustanovka AutoFiltra
                 Selection.AutoFilter Field:=7, Criteria1:="<>"
            
'2)Vstavka formul v vidimie iacheiki
           
           Cells(FirstRow, 9) = _
"=IF(RC7<>0,IF(RC3=""A"",SUMIF(R3C3:R2186C3,""A"",R3C7:R2186C7),IF(RC3=""B"",SUMIF(R3C3:R2186C3,""B"",R3C7:R2186C7),SUMIF(R3C3:R2186C3,""C"",R3C7:R2186C7)))/SUM(R3C7:R2186C7),""-"")"
    
            Range(Cells(FirstRow, 9), Cells(LastRow, 9)).Select
            Selection.SpecialCells(xlCellTypeVisible).FillDown
        
        Cells(FirstRow, 8).FormulaArray = _
"=SUM(IF(R3C[-5]:R2186C[-5]=RC[-5],R3C[-2]:R2186C[-2]/R3C[-3]:R2186C[-3]*R3C[-1]:R2186C[-1]))/(SUM(IF(R3C[-5]:R2186C[-5]<>"""",R3C[-2]:R2186C[-2]/R3C[-3]:R2186C[-3]*R3C[-1]:R2186C[-1])))"
        
        Range(Cells(FirstRow, 8), Cells(LastRow, 8)).Select
        Selection.SpecialCells(xlCellTypeVisible).FillDown
        
    End If
Cells(1, 1) = 1
Application.ScreenUpdating = 1
End Sub
[/vba]

Еще раз большое спасибо.
Всем желаю успехов.

Автор - machodg
Дата добавления - 05.12.2017 в 02:04
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Как вставить формулу массива в видимые ячейки фильтра? (Макросы/Sub)
  • Страница 1 из 1
  • 1
Поиск:

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