Подскажите, пожалуйста, у меня есть "умная" таблица. Мне необходимо получить из неё данные по критериям - имя столбца и имя строки. В таблице Эксель данный вопрос решаю через ИНДЕКС и ПОИСКПОЗ:
{=ИНДЕКС(Таблица1[Значение на начало часа];ПОИСКПОЗ(C$2&$A3&$B3;Таблица1[[Тег ]]&Таблица1[Дата]&Таблица1[Час];0))}.
Попытался аналогично прописать в макросе - не получается:
[1] Range("c3").value = INDEX(Таблица1[Значение на начало часа],MATCH(R2C&RC1&RC2,Таблица1[Тег ]&Таблица1[Дата]&Таблица1[Час],0)) - не работает
[2] Range("c3") = application.INDEX(Таблица1,application.MATCH(Cells(2, 3),Таблица1[Тег],0),application.MATCH(Cells(3, 1)&Cells(3, 2),Таблица1[Дата]&Таблица1[Час],0)) - не работает
Я понимаю, что [2] - скорее более правильная формула, но не могу добиться нормальной ссылки на столбец "умной" таблицы.
Просто вставлять формулу в ячейку нет смысла, она очень тормозит процесс.
Не могу нигде найти по этому поводу информацию.
[вложение удалено администратором]
Эксель-функции вида "application.INDEX" в данном случае не получится использовать в макросе, т.к. у вас формула массива. Макросу как-то надо указать, что нужно использовать эксель-функции так, как если бы используется формула массива, но такое сделать нельзя.
Остаётся два варианта:
1) Вставка формулы в ячейку, а затем превращение её в значение. В этом случае можно попробовать использовать:
ActiveCell.FormulaArray
2) Делать те же действия, что делает ваша формула, но без использования формул, а какими-нибудь другими способами: в программировании очень часто одно и то же можно делать несколькими способами.
То есть второй способ отпадает. Остаётся первый с использованием "ActiveCell.FormulaArray" (вместо "ActiveCell" нужная ячейка).
Или вариант вообще без использования эксель-функций.
Использование вашего первого способа. Вашу формулу оставил без изменений, только заключил в кавычки - в VBA формулу надо заключать в кавычки, а спереди нужно ставить знак равно:
Этот макрос вставляет формулу на активный лист в C3, а затем превращает формулу в значение, чтобы формулы не нагружали файл.
Sub макрос()
With Range("c3")
.FormulaArray = "=INDEX(Таблица1[Значение на начало часа],MATCH(R2C&RC1&RC2,Таблица1[Тег ]&Таблица1[Дата]&Таблица1[Час],0))"
.Value = .Value
End With
End Sub
Кроме того. при работе с большим кол-вом формул или данных используйте формат "xlsb", он уменьшает размер файла и ускоряет работу формул. Формат xlsm нужен для взаимодействия с другими программами с помощью xml, а для всех остальных случаев не имеет смысла его использовать.
СПАСИБО!!!
А что значит - без использования эксель функций?
Можно попробовать сделать макрос, в котором не будет эксель-функций. То есть сам VBA может сделать то же самое.
Если вы что-то не поняли, то можете спросить.