У меня есть таблица данных 100x100 с двумя переменными в excel.
Мне нужна функция, которая возвращает все возможные наборы переменных, которые дают заданное целевое значение. Я смотрю на какую-то рекурсивную двумерную функцию поиска. Может ли кто-нибудь указать мне в правильном направлении?
Это можно сделать без VBA, достаточно компактно, вот так.
Предположим, ваша таблица 100x100 находится в B2:CW101, и мы поместили список чисел от 1 до 100 слева от A2 до A101, и снова от 1 до 100 сверху от B1 до CW1.
Создайте столбец ячеек внизу, начиная (скажем) с B104.
B104=MAX(($A$2:$A$101*100+$B$1:$CW$1<B103)*($B$2:$CW$101=TargetValue)*($A$2:$A$101*100+$B$1:$CW$1))
Это формула массива, поэтому нажмите Ctrl-Shift-Enter вместо Enter и фигурные скобки {} должен появиться вокруг формулы.
Затем скопируйте столько строк, сколько вам может понадобиться. Вам также нужно поставить большое число над вашей первой формулой, т.е. в B103, например. 999999.
Что делает формула, так это вычисляет Rowx100+Column, но только для каждой успешной ячейки, и функция MAX находит наибольший результат, исключая все предыдущие найденные результаты, т.е. она находит целевые результаты по одному, начиная с нижнего правого и работая вверху слева. (Приложив немного усилий, вы можете заставить его искать другой путь).
Это даст вам такие результаты, как 9922, то есть строку 99, столбец 22, и вы можете легко извлечь эти значения из числа.
Нет встроенной функции, которая будет делать то, что вы хотите, я в этом уверен на 99%.
Функция VBA, которая возвращает массив, может быть построена по аналогии с уже показанной быстрой и грязной подпрограммой. Создайте Variant для хранения вывода, возможно, Redimed до максимально возможного количества результатов и Redim Preserve-d до фактического числа в конце. Затем верните это как результат функции, которую затем нужно вызвать как функцию массива (Control-Shift-Enter).
Единственным недостатком является то, что вам нужно убедиться, что целевой диапазон достаточно велик, чтобы вместить весь результат: Excel не сделает этого автоматически.
Солвер подойдет?
http://office.microsoft.com/en-us/excel/HA011118641033.aspx
Я много пробовал это без использования VBA, но без него это невозможно. Чтобы решить эту проблему, мне нужно было перебрать весь массив и найти ближайшие значения. Затем эти значения были разыменованы с использованием вызовов и свойств диапазона, и выходные данные были сгенерированы в диапазоне, который увеличивался при каждом допустимом совпадении.
Быстрая и грязная реализация выглядит следующим образом:
Dim arr As Range
Dim tempval As Range
Dim op As Integer
Set arr = Worksheets("sheet1").Range("b2:ao41")
op = 1
Range("B53:D153").ClearContents
For Each tempval In arr
If Round(tempval.Value, 0) = Round(Range("b50").Value, 0) Then
Range("b52").Offset(op, 0).Value = Range("a" & tempval.Row).Value
Range("b52").Offset(op, 1).Value = Cells(tempval.Column, 1).Value
Range("b52").Offset(op, 2).Value = tempval.Value
op = op + 1
End If
Next
Range("b50").Select
Я все еще ищу подход без VBA.
У меня есть решение, которое не использует VBA, но оно довольно грязное. Это включает в себя создание еще одной одномерной таблицы в Excel и поиск по ней. Для таблицы данных 100x100 новой таблице потребуется 10 000 строк.
Извините, если это не соответствует вашим потребностям.
Резюме ниже - дайте мне знать, если вам нужно больше деталей. N = размерность данных, например. 100 в вашем примере.
Сначала создайте новую таблицу с пятью столбцами и строками NxN. В каждом случае замените мои имена столбцов соответствующей ссылкой Excel.
В первом столбце (назовем его ИНДЕКС) просто перечислены 1, 2... NxN.
Второй столбец (DATAROW) содержит формулу для перебора 1, 2... N, 1, 2... N... Это можно сделать, используя что-то вроде =MOD(INDEX-1, N)+1.
Третий столбец (DATACOL) содержит 1, 1, 1... 2, 2, 2... (N раз каждый). Это можно сделать с помощью =INT((INDEX-1)/N)+1
Четвертый столбец (VALUE) содержит значение из вашей таблицы данных, используя что-то вроде: =OFFSET($A$1, DATAROW, DATACOL), предполагая, что ваша таблица данных начинается с $A$1.
Теперь у нас есть одномерная таблица, содержащая все ваши данные.
Пятый столбец (ПРОСМОТР) содержит формулу: =ПОИСКПОЗ(цель, СМЕЩ(ЗНАЧЕНИЕ, [ПРОСМОТР-1], 0),0)+ [ПРОСМОТР-1]
где [ПРОСМОТР-1] относится к ячейке непосредственно выше (например, в ячейке F4 это относится к F3). Вам понадобится 0 над первой ячейкой в столбце ПРОСМОТР.
VALUERANGE должен быть фиксированной (именованной или с использованием знаков $) ссылкой на весь столбец VALUE.
Столбец LOOKUP затем содержит числа INDEX, которые можно использовать для поиска DATAROW и DATACOL, чтобы найти положение совпадения в данных.
Это работает путем поиска совпадений в VALUERANGE, а затем поиска совпадений в скорректированном диапазоне, начиная с предыдущего совпадения.
В электронной таблице это намного проще, чем с помощью объяснения выше, но это лучшее, что я могу сделать на данный момент...
