Страницы: 1
RSS
Формула массива через VBA
 
Уважаемые гуру форума, подскажите как можно решить данную проблему. На листе "Остатки" формулой массива:
Код
=ЕСЛИОШИБКА(ИНДЕКС(Приход!$C$2:$C$11;НАИМЕНЬШИЙ(ЕСЛИ(Приход!$D$2:$D$11>0;ЕСЛИ(Приход!$A$2:$A$11<=$A$1;СТРОКА(Приход!$C$2:$C$11)-1));СТРОКА()-3));"")
правильно вычисляются данные. Но проблема в том, что при попытке отсортировать эти данные Excel ругается на невозможность изменения массива. Копировать эти данные, а потом вставлять их как число и затем уже сортировать как-то неправильно, ведь формулы потом придется восстанавливать. Так вот вопрос: как вычислить эти же данные (ну скажем в диапазоне "В4:В17") через VBA? Вариант  
Код
Range ("B4:B17").FormulaArray = "=IFERROR(INDEX(Приход!R2C3:R11C3,SMALL(IF(Приход!R2C4:R11C4>0,IF(Приход!R2C1:R11C1<=R1C1,ROW(Приход!R2C3:R11C3)-1)),ROW()-3)),"""")"
не подходит, т.е. данные то высчитываются правильно, но они по-прежнему находятся в массиве и не полежат изменениям (в частности - сортировке). Нужно как то так все это вычислить, чтобы в ячейки попадал только результат, а вот как это сделать я не знаю. Заранее спасибо всем откликнувшимся.
Изменено: plex - 23.04.2015 10:32:35
 
вы что ли пытаетесь отсортировать значения полученные формулой? 8-0 Сортируйте базу.
 
А без макросов пойдет?
 
V, с сортировкой базы тоже не все так просто. В реале на этот лист вносятся данные, по задумке в порядке очередности дат. Не сортировать же всякий раз после внесения данных :cry:. Вот если бы перед работой формулы массива формулой отсортировать исходные данные, обработать, а затем вернуть исходные данные как было, тогда наверное да. Только можно ли так сдлелать и как, если можно?
Михаил, спасибо за Ваш вариант, будуи зучать принцип. А все-таки предложенный мною вариант совсем неработоспособный?
 
Не понятно, что вам нужно...
 
1. Сводная таблица
2. Расширенный автофильтр
3. Задача-то в чем? получить список позиций с ненулевым остатком с фильтром по дате? а сами остатки нужны?
F1 творит чудеса
 
Уважаемые Михаил, Максим. Я имел ввиду следующее: хотел получить в ячейках тот же результат который дает формула массива, при этом иметь возможность изменять полученные данные (в частности сортировать). Я тут сам попытался сваять нечто и вот что получилось (кнопка на листе Остатки):
Код
Private Sub CommandButton1_Click()
Dim FirstRow As Long, LastRow As Long, i As Long
FirstRow = 4
    i = 2
    Range("D4:D17").ClearContents
    If Sheets("Приход").Cells(i, 4) > 0 And Sheets("Приход").Cells(i, 1) < Sheets("Остатки").Cells(1, 1) Then
    For i = 2 To 100
        Sheets("Приход").Cells(FirstRow, 4) = Sheets("Остатки").Cells(i, 3)
        FirstRow = FirstRow + 1
    Next
    End If
    Range("D4:D17").SortSpecial
End Sub
в принципе все правильно делает, НО не работает мой "фильтр"
Код
 If Sheets("Приход").Cells(i, 4) > 0 And Sheets("Приход").Cells(i, 1) < Sheets("Остатки").Cells(1, 1) Then
и данные показываются все, несмотря на то, что у части позиций нулевой остаток, а у 1 позиции дата превышает контрольную. И, по какой то причине, если у первой позиции остаток = 0, данные не выводятся совсем. Что в этом убогом коде я написал не так, что надо изменить?
 
Код
Private Sub CommandButton1_Click()
Dim FirstRow As Long, LastRow As Long, i As Long
FirstRow = 4
Range("D4:D17").ClearContents
With Sheets("Приход")
 For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
    If .Cells(i, 4) > 0 And .Cells(i, 1) < Cells(1, 1) Then
      Cells(FirstRow, 4) = .Cells(i, 3)
      FirstRow = FirstRow + 1
    End If
Next
End With
    Range("D4:D" & Cells(Rows.Count, 4).End(xlUp).Row).SortSpecial
End Sub
Изменено: V - 23.04.2015 13:33:46
 
Спасибо большое, V. Ваш вариант работает как мне и предполагалось. Буду проверять на реальном файле.
Страницы: 1
Наверх