excel - son - No se puede implementar INDIRECT() dentro de una fórmula de matriz

formulas matriciales excel pdf

Question

Tengo una hoja llamada Finder en la que creé una herramienta para buscar en otras hojas (llamadas # 1, # 2, etc.). El problema que encontré es que no puedo usar la función INDIRECTO en algunos casos como se describe a continuación:

 { = IF(Finder!$B$4 = 1,
INDEX(INDIRECT("''#" & (ROW() - 5) & IF(MOD(COLUMN(), 3) = 0, "''!$A$2:$A$100", "''!$B$2:$B$100")),
SMALL(IF(ISERROR(SEARCH(Finder!$F$4, ''#1''!$B$2:$B$100)),
"",
ROW(INDIRECT("''#" & (ROW() - 5) & "''!$B$2:$B$100")) - MIN(ROW(INDIRECT("''#" & (ROW() - 5) & "''!$B$2:$B$100"))) + 1),
INT(COLUMN() / 3)),
1),
IF(Finder!$B$4 = 2,
INDEX(INDIRECT("''#" & (ROW() - 5) & "''!" & IF(MOD(COLUMN(), 3) = 0, "$A$2:$A$100", "$B$2:$B$100")),
SMALL(IF(Finder!$F$4 = ''#1''!$B$2:$B$100,
ROW(INDIRECT("''#" & (ROW() - 5) & "''!$B$2:$B$100")) - MIN(ROW(INDIRECT("''#" & (ROW() - 5) & "''!$B$2:$B$100"))) + 1,
""),
INT(COLUMN() / 3)),
1),... }

Forma conceptual:

 { = IF(Finder!$B$4 = 1,
INDEX(using INDIRECT to address a range in another sheet successfully,
SMALL(IF(ISERROR(SEARCH(cannot use INDIRECT here!)),
"",
Desired value),
Desired item),
1),
IF(Finder!$B$4 = 2,
INDEX(using INDIRECT to address a range in another sheet successfully,
SMALL(IF(cannot use INDIRECT here!,
Desired value,
""),
Desired item),
1),... }

Necesito abordar un rango en otras hojas como a continuación:

SMALL(IF(ISERROR(SEARCH(Finder!$F$4, ''#1''!$B$2:$B$100)),...
SMALL(IF(Finder!$F$4 = ''#1''!$B$2:$B$100,...

está bien, pero no puedo usar INDIRECT para hacer que la fórmula sea dinámica:

SMALL(IF(ISERROR(SEARCH(Finder!$F$4, INDIRECT("''#" & (ROW() - 5) & "''!$B$2:$B$100"))),...
SMALL(IF(Finder!$F$4 = INDIRECT("''#" & (ROW() - 5) & "''!$B$2:$B$100"),...

Gracias por adelantado

ACTUALIZAR:

La herramienta Finder contiene muchas celdas. La imagen muestra por qué necesito obtener un nombre de hoja usando la función ROW() .

ACTUALIZACIÓN 2:

El cuadro combinado en la hoja del Finder tiene cuatro elementos y usa una celda de $ B $ 4:

1- Búsqueda aproximada

2- Búsqueda exacta

3- Índice completo

4- Categoría específica

Aquí está el código completo de la fórmula:

= IF(ISERROR(IF(Finder!$B$4 = 1,
INDEX(INDIRECT("''#" & (ROW() - 5) & IF(MOD(COLUMN(), 3) = 0, "''!$A$2:$A$100", "''!$B$2:$B$100")),
SMALL(IF(ISERROR(SEARCH(Finder!$F$4, ''#1''!$B$2:$B$100)),
"",
ROW(INDIRECT("''#" & (ROW() - 5) & "''!$B$2:$B$100")) - MIN(ROW(INDIRECT("''#" & (ROW() - 5) & "''!$B$2:$B$100"))) + 1),
INT(COLUMN() / 3)),
1),
IF(Finder!$B$4 = 2,
INDEX(INDIRECT("''#" & (ROW() - 5) & "''!" & IF(MOD(COLUMN(), 3) = 0, "$A$2:$A$100", "$B$2:$B$100")),
SMALL(IF(Finder!$F$4 = ''#1''!$B$2:$B$100,
ROW(INDIRECT("''#" & (ROW() - 5) & "''!$B$2:$B$100")) - MIN(ROW(INDIRECT("''#" & (ROW() - 5) & "''!$B$2:$B$100"))) + 1,
""),
INT(COLUMN() / 3)),
1),
IF(Finder!$B$4 = 3,
IF(MOD(COLUMN(), 3) = 0,
20 * (ROW() - 6) + COLUMN() / 3,
INDIRECT(ADDRESS(INT(COLUMN() / 3) + 1, 2, 1, , "#" & (ROW() - 5)))),
INDEX(INDIRECT("''#" & (ROW() - 5) & "''!" & IF(MOD(COLUMN(), 3) = 0, "$A$2:$A$100", "$B$2:$B$100")),
SMALL(IF(Finder!$F$4 = ''#1''!$I$2:$I$100,
ROW(INDIRECT("''#" & (ROW() - 5) & "''!$B$2:$B$100")) - MIN(ROW(INDIRECT("''#" & (ROW() - 5) & "''!$B$2:$B$100"))) + 1,
IF(ISERROR(SEARCH(Finder!$F$4 & ",", ''#1''!$I$2:$I$100)),
IF(ISERROR(SEARCH("," & Finder!$F$4, ''#1''!$I$2:$I$100)),
IF(ISERROR(SEARCH(", " & Finder!$F$4, ''#1''!$I$2:$I$100)),
"",
ROW(INDIRECT("''#" & (ROW() - 5) & "''!$B$2:$B$100")) - MIN(ROW(INDIRECT("''#" & (ROW() - 5) & "''!$B$2:$B$100"))) + 1),
ROW(INDIRECT("''#" & (ROW() - 5) & "''!$B$2:$B$100")) - MIN(ROW(INDIRECT("''#" & (ROW() - 5) & "''!$B$2:$B$100"))) + 1),
ROW(INDIRECT("''#" & (ROW() - 5) & "''!$B$2:$B$100")) - MIN(ROW(INDIRECT("''#" & (ROW() - 5) & "''!$B$2:$B$100"))) + 1)),
INT(COLUMN() / 3)),
1))))),
" ",
IF(Finder!$B$4 = 1,
INDEX(INDIRECT("''#" & (ROW() - 5) & IF(MOD(COLUMN(), 3) = 0, "''!$A$2:$A$100", "''!$B$2:$B$100")),
SMALL(IF(ISERROR(SEARCH(Finder!$F$4, ''#1''!$B$2:$B$100)),
"",
ROW(INDIRECT("''#" & (ROW() - 5) & "''!$B$2:$B$100")) - MIN(ROW(INDIRECT("''#" & (ROW() - 5) & "''!$B$2:$B$100"))) + 1),
INT(COLUMN() / 3)),
1),
IF(Finder!$B$4 = 2,
INDEX(INDIRECT("''#" & (ROW() - 5) & "''!" & IF(MOD(COLUMN(), 3) = 0, "$A$2:$A$100", "$B$2:$B$100")),
SMALL(IF(Finder!$F$4 = ''#1''!$B$2:$B$100,
ROW(INDIRECT("''#" & (ROW() - 5) & "''!$B$2:$B$100")) - MIN(ROW(INDIRECT("''#" & (ROW() - 5) & "''!$B$2:$B$100"))) + 1,
""),
INT(COLUMN() / 3)),
1),
IF(Finder!$B$4 = 3,
IF(MOD(COLUMN(), 3) = 0,
20 * (ROW() - 6) + COLUMN() / 3,
INDIRECT(ADDRESS(INT(COLUMN() / 3) + 1, 2, 1, , "#" & (ROW() - 5)))),
INDEX(INDIRECT("''#" & (ROW() - 5) & "''!" & IF(MOD(COLUMN(), 3) = 0, "$A$2:$A$100", "$B$2:$B$100")),
SMALL(IF(Finder!$F$4 = ''#1''!$I$2:$I$100,
ROW(INDIRECT("''#" & (ROW() - 5) & "''!$B$2:$B$100")) - MIN(ROW(INDIRECT("''#" & (ROW() - 5) & "''!$B$2:$B$100"))) + 1,
IF(ISERROR(SEARCH(Finder!$F$4 & ",", ''#1''!$I$2:$I$100)),
IF(ISERROR(SEARCH("," & Finder!$F$4, ''#1''!$I$2:$I$100)),
IF(ISERROR(SEARCH(", " & Finder!$F$4, ''#1''!$I$2:$I$100)),
"",
ROW(INDIRECT("''#" & (ROW() - 5) & "''!$B$2:$B$100")) - MIN(ROW(INDIRECT("''#" & (ROW() - 5) & "''!$B$2:$B$100"))) + 1),
ROW(INDIRECT("''#" & (ROW() - 5) & "''!$B$2:$B$100")) - MIN(ROW(INDIRECT("''#" & (ROW() - 5) & "''!$B$2:$B$100"))) + 1),
ROW(INDIRECT("''#" & (ROW() - 5) & "''!$B$2:$B$100")) - MIN(ROW(INDIRECT("''#" & (ROW() - 5) & "''!$B$2:$B$100"))) + 1)),
INT(COLUMN() / 3)),
1)))))

Answer 1

El problema es causado por el uso de las funciones ROW y ROW . Estas funciones devuelven "matrices" incluso cuando solo tiene un valor, por ejemplo {1} en lugar de 1. En algunos contextos, eso no es un problema, pero en otros casos (como aquí), Excel tiene un problema al convertir {1} a 1 y las fórmulas no funciona

Una posible solución es encerrar todas las funciones de ROW y ROW en una función como MAX o SUM que se convertirá para usted, por ejemplo, en lugar de

ROW() - 5

utilizar

SUM(ROW()) - 5

pero una mejor manera (y la que yo recomiendo) es usar las funciones ROWS o COLUMNS (con "S" al final), así que si la primera fórmula está en Z6 use

ROWS($Z$6:Z6) o COLUMNS($Z$6:Z6)

esos dos le darán un valor de 1 en Z6, pero a medida que copie o cruce aumentarán en 1 cada vez (y no sufrirán el mismo inconveniente que ROW y COLUMN ). Esta es una solución mejor de todos modos, ya que elimina sus -5 y es menos susceptible al error si elimina o agrega filas o columnas