Ссылка на массив в ячейке

[Alexxey]

Здравствуйте!

Возможно ли в формулах ссылаться на массив, размещенный в одной ячейке? Пример во вложении.

Заранее спасибо за ответ!

[exceleved]

У Вас в ячейке С3 - формула, которая помещает в ячейку одно (первое) значение массива.
Есть варианты с размещением массива в имени и с размещением массива в ячейке КАК ТЕКСТА с преобразованием его в массив с помощью макрофункции (тоже в имени):

[С.М.]

Опоздал 
На всякий случай - с картинкой:

[Alexxey]

Спасибо, однако возможно ли применить данный способ для моего примера (см.вложение)?

[exceleved]

Пишите

Код Выделить Развернуть

=ИНДЕКС(MyUnion2(C10:D10;F10:G10);3)Функция возвращает массив, ИНДЕКС выбирает элемент массива.

[Alexxey]

Спасибо, exceleved.

Однако моя упрощенная постановка вопроса не отражает большой объем вычислений для получения массива и их количество. Вычисление массива ЗАНОВО для каждой из ячеек, ссылающейся на его значения, ресурсоемко.

Оптимально единоразово посчитать массив и брать из него значения.

При этом необходимо массив не разворачивать на листе, а держать в одной ячейке, т.к. 50 массивов по 500 элементов неудобно и нет необходимости разворачивать на листе.

[exceleved]

1. Вам ячеек жалко? Разверните массивы на отдельном листе.
2. Function MyUnion2 - это реальная функция, или упрощенная для примера? Она написана неоптимально. Можно ускорить раз в -дцать. Если надо - скажу как.
3. Нужна ли вообще функция, формирующая массивы?
По индексу результирующего массива можно вычислить индексы исходных массивов и сложить два соответствующих элемента. Это можно и формулой листа сделать.

[Alexxey]

1. "Разверните массивы на отдельном листе".

Этот способ будет работать, однако, возможно ли обойтись без дополнительных затрат?

2. Вероятно, функцию MyUnion2 Вы предлагаете заменить на {=трансп(array1)+array2}?

3.  Формирующая массивы функция необходима, т.к. по индексу элемента результирующего массива невозможно вычислить индексы элементов исходных массивов.

[exceleved]

2. Нет, но этот вариант тоже интересный: в VBA получить значение этой матрицы с помощью Evaluate и переписать в одномерный массив.

3. Да ну? Попробуйте такую функцию:

Код Выделить Развернуть

Function MyIndex(Array1, Array2, ByVal d As Long)
Dim i&, j&
d = d - 1
i = d \ Array2.Columns.Count
j = d Mod Array2.Columns.Count
MyIndex = Array1(1 + i) + Array2(1 + j)
End Function
Она вполне эквивалентна формуле =ИНДЕКС(MyUnion2(Array1, Array2);d)
Свойство Array2.Columns.Count, конечно, лучше не вызывать 2 раза, и переменные можно не использовать - но так нагляднее.

[Alexxey]

3) Попробуйте такую функцию...

UDF MyUnion2 только для примера. Реально используется ДРУГАЯ функция, формирующая массивы разной длины.

Возможно ли переписать матрицу в одномерный массив без UDF?

[exceleved]

UDF MyUnion2 только для примера
Ну вот, опять  Если бы сразу сформулировали задачу полностью, возможно, уже получили бы решение. Возможно, массивы можно хранить в статических переменных, но мне эта тема уже не интересна.
Выкладываю оптимизированные варианты вашей функции MyUnion2. Удалось ускорить в 33 раза. Смотрите, может, найдете полезные для себя приемы.
Мои результаты по скорости работы функций (проц 1.6ГГц, WinXP, Excel2000). Запускайте Sub Test:

Код Выделить Развернуть

Функция       Время         Относительно исходной, раз
MyUnion2       27,06556
MyUnion3       2,136062      12,67077
MyUnion4       1,20525       22,45639
MyUnion5       0,803125      33,70031

[Alexxey]

Exceleved, спасибо Вам большое за помощь! Выкладывая UDF MyUnion я хотел всего лишь показать, что массив формируется именно UDF, что, возможно, влияет на решение вопроса. Я не выкладывал реальную сложную для понимания UDF, считаю, это отвлекает от задачи.

Оптимизированные функции я изучу, спасибо!

Почему "Забудьте про Integer и Single!!!"?

Думаю, макросом назначить 100 именованных массивов.

[exceleved]

> Почему "Забудьте про Integer и Single!!!"?

1. На 32-разрядных системах арифметика Long быстрее, чем Integer.
2. Индексы массивов, свойств типа Cells(i, j), Rows(i) и т.д. имеют тип Long. Если Вы используете в качестве индексов переменные типа Integer, это значит, что каждый раз компилятор вставляет функцию преобразования типа, т.е. фактически получается Cells(CLng(i), CLng(j)).
Попробуйте объявить i,j,n как Integer в MyUnion5 - и увидите небольшое замедление.
3. Тип Integer не годится для нумерации строк листа начиная с Excel 97.

Single не быстрее, чем Double, но сильнее склонен к накоплению погрешностей расчетов в связи с более короткой мантиссой. Попробуйте в окне Immediate:

?0.3-0.2-0.1
?csng(0.3)-csng(0.2)-csng(0.1)

На сколько порядков различается погрешность? 

То есть если Вы не используете массивы в 10 млн элементов и более, то использование типов Integer и Single на более-менее современных компьютерах (с памятью >500МБ) не имеет смысла.

[С.М.]

Exceleved, спасибо Вам большое за помощь! Выкладывая UDF MyUnion я хотел всего лишь показать, что массив формируется именно UDF, что, возможно, влияет на решение вопроса. Я не выкладывал реальную сложную для понимания UDF, считаю, это отвлекает от задачи.
Оптимизированные функции я изучу, спасибо!
Думаю, макросом назначить 100 именованных массивов.

Если бы сразу сформулировали задачу полностью, возможно, уже получили бы решение

Между двумя Алексеями .
Алексей-Alexxey, в Вашем примере диапазоны, из которых Вы делаете объединённый массив, разделены всего лишь
одним столбиком. Может вообще его удалить (перенести в другое место), тогда все упростится до обычных формул и "без дополнительных затрат". Стоит ли зацикливаться на преодолении трудностей ?
И ещё напрягает: "макросом назначить 100 именованных массивов" (?)
----------------
Ух-ты, а в "предохранителях" они тоже быстро бегают:

Код Выделить Развернуть


Функция   Время      Относительно исходной, раз
MyUnion2   36,1245
MyUnion3   2,4835       14,5458
MyUnion4   1,281125      28,19748
MyUnion5   0,796875      45,33271
MyUnion123  0,562       64,27847

-----------
Сорь, тест в файле с функцией MyUnion123() - не корректен.
Но это не жульничество, а последствия встречи НГ .