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

Вход

Регистрация

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

 

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

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

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

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

Excel 2013
[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)))
ну его нафиг формулу массива вставлять макросом %)


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)
 
Ответить
Сообщение[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
Группа: Пользователи
Ранг: Новичок
Сообщений: 15
Репутация: 0 ±
Замечаний: 0% ±

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

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

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

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

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


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)
 
Ответить
Сообщение
к сожалению нужно макросом
Ваш вопрос
Как вставить формулу массива
так что не так?
в столбец "Н"
там у Вас как раз таки формулы массива.
так что же Вам нужно?

Автор - Nic70y
Дата добавления - 04.12.2017 в 07:40
machodg Дата: Понедельник, 04.12.2017, 12:49 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 15
Репутация: 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
Группа: Пользователи
Ранг: Новичок
Сообщений: 15
Репутация: 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
Группа: Админы
Ранг: Местный житель
Сообщений: 13167
Репутация: ±
Замечаний: ±

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


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

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

Excel 2010
Есть идеи?

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

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

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

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


Сообщение отредактировал 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
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4238
Репутация: 922 ±
Замечаний: 0% ±

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


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)
 
Ответить
Сообщение
не работают с этой формулой
с какой?

Автор - Nic70y
Дата добавления - 04.12.2017 в 16:07
_Boroda_ Дата: Понедельник, 04.12.2017, 17:59 | Сообщение № 11
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11852
Репутация: 4911 ±
Замечаний: 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
Группа: Пользователи
Ранг: Новичок
Сообщений: 15
Репутация: 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 из 11
Поиск:

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