Excel VBA: Получение данных из "умной" таблицы с помощью макроса

[alexey_ava]

Подскажите, пожалуйста, у меня есть "умная" таблица. Мне необходимо получить из неё данные по критериям - имя столбца и имя строки. В таблице Эксель данный вопрос решаю через ИНДЕКС и ПОИСКПОЗ:
{=ИНДЕКС(Таблица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, а для всех остальных случаев не имеет смысла его использовать.

[alexey_ava]

СПАСИБО!!!
А что значит - без использования эксель функций?

[Администратор]

Можно попробовать сделать макрос, в котором не будет эксель-функций. То есть сам VBA может сделать то же самое.

[Администратор]

Если вы что-то не поняли, то можете спросить.