una son matriz matriciales matricial matrices las hacer funciones ejercicios datos cuáles condicionales como excel excel-formula array-formulas

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



formulas matriciales excel pdf (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

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)))))