Descripción de las limitaciones para trabajar con matrices en Excel

Resumen

En las versiones de Microsoft Excel que aparecen en la sección "Se aplica a", el tema de ayuda "Especificaciones de cálculo" enumera las limitaciones para trabajar con una matriz. En este artículo se describen las limitaciones de las matrices en Excel.

Más información

En Excel, las matrices de las hojas de cálculo están limitadas por la memoria de acceso aleatorio disponible, por el número total de fórmulas de matriz y por la regla "toda la columna".

Memoria disponible

Las versiones de Excel que aparecen en la sección "Se aplica a" no imponen un límite en el tamaño de las matrices de hojas de cálculo. En su lugar, solo está limitado por la memoria disponible en el equipo. Por lo tanto, puede crear matrices muy grandes que contengan cientos de miles de celdas.

Regla de "columna completa"

Aunque puede crear matrices muy grandes en Excel, no puede crear una matriz que use una columna completa o varias columnas de celdas. Dado que recalcular una fórmula de matriz que usa una columna completa de celdas lleva mucho tiempo, Excel no permite crear este tipo de matriz en una fórmula.

Fórmulas de matriz máximas

En Excel 2003 y en versiones anteriores de Excel, una sola hoja de cálculo puede contener un máximo de 65 472 fórmulas de matriz que hacen referencia a otra hoja de cálculo. Si desea usar más fórmulas, divida los datos en varias hojas de cálculo para que haya menos de 65 472 referencias a una sola hoja de cálculo.

Por ejemplo, en Sheet1 de un libro, puede crear los siguientes elementos:

• 65 472 fórmulas de matriz que hacen referencia a Sheet2
• 65 472 fórmulas de matriz que hacen referencia a Sheet3
• 65 472 fórmulas de matriz que hacen referencia a Sheet4

Si intenta crear más de 65 472 fórmulas de matriz que hacen referencia a una hoja de cálculo específica, las fórmulas de matriz que escriba después del número de fórmula de matriz 65 472 pueden desaparecer al escribirlas.

Ejemplos de fórmulas de matriz

A continuación se muestra una lista de ejemplos de fórmulas de matriz. Para usar estos ejemplos, cree un nuevo libro y escriba cada fórmula como una fórmula de matriz. Para ello, escriba la fórmula en la barra de fórmulas y presione CTRL+MAYÚS+ENTRAR para escribir la fórmula.

Excel 2007

• A1: =SUM(IF(B1:B1048576=0,1,0))

  La fórmula de la celda A1 devuelve el resultado 1048576. Este resultado es correcto.

• A2: =SUM(IF(B:B=0,1,0))

  La fórmula de la celda A2 devuelve el resultado 1048576. Este resultado es correcto.

• A3: =SUM(IF(B1:J1048576=0,1,0))

  La fórmula de la celda A3 devuelve el resultado 9437184. Este resultado es correcto.

  Nota:

  La fórmula puede tardar mucho tiempo en calcular el resultado porque la fórmula está comprobando más de 1 millón de celdas.

• A4: =SUM(IF(B:J=0,1,0))

  La fórmula de la celda A4 devuelve el resultado 9437184. Este resultado es correcto.

  Nota:

  La fórmula puede tardar mucho tiempo en calcular el resultado porque la fórmula está comprobando más de 1 millón de celdas.

• A5: =SUM(IF(B1:DD1048576=0,1,0))

  Al escribir esta fórmula en la celda A5, puede recibir uno de los siguientes mensajes de error:

  Excel se quedó sin recursos al intentar calcular una o varias fórmulas. Como resultado, estas fórmulas no se pueden evaluar.

  Para determinar el número único asociado al mensaje que ve, presione CTRL+MAYÚS+I. El número siguiente aparece en la esquina inferior derecha de este mensaje:

  101758

  En este caso, el tamaño de la matriz de hoja de cálculo es demasiado grande para la memoria disponible. Por lo tanto, no se puede calcular la fórmula.

  Además, es posible que Excel parezca dejar de responder durante unos minutos. Esto se debe a que las otras fórmulas que escribió deben recalcular sus resultados.

  Una vez actualizados los resultados, Excel responde según lo esperado. La fórmula de la celda A5 devuelve el valor 0 (cero).

Excel 2003 y versiones anteriores de Excel

• A1: =SUM(IF(B1:B65535=0,1,0))

  La fórmula de la celda A1 devuelve el resultado 65535. Este resultado es correcto.

• A2: =SUM(IF(B:B=0,1,0))

  La fórmula de la celda A2 devuelve un #NUM! error porque la fórmula de matriz hace referencia a una columna completa de celdas.

• A3: =SUM(IF(B1:J65535=0,1,0))

  La fórmula de la celda A3 devuelve el resultado 589815. Este resultado es correcto.

  Nota:

  La fórmula puede tardar mucho tiempo en calcular el resultado porque la fórmula está comprobando casi 600 000 celdas.

• A4: =SUM(IF(B:J=0,1,0))

  Al igual que la fórmula de la celda A2, la fórmula de la celda A4 devuelve un #NUM. error porque la fórmula de matriz hace referencia a una columna completa de celdas.

• A5: =SUM(IF(B1:DD65535=0,1,0))

  Al escribir la fórmula en la celda A5, puede recibir uno de los siguientes mensajes de error:

  No hay suficiente memoria. ¿Continuar sin deshacer?

  No hay suficiente memoria.

  En este caso, el tamaño de la matriz de hoja de cálculo es demasiado grande para la memoria disponible. Por lo tanto, no se puede calcular la fórmula.

  Además, es posible que Excel parezca dejar de responder durante unos minutos. Esto se debe a que las otras fórmulas que escribió deben recalcular sus resultados.

  Una vez actualizados los resultados, Excel responde según lo esperado. La fórmula de la celda A5 devuelve el valor 0 (cero).

Tenga en cuenta que ninguna de estas fórmulas funciona en versiones anteriores de Excel. Esto se debe a que las matrices de hojas de cálculo creadas por las fórmulas son todas mayores que los límites máximos en versiones anteriores de Excel. A continuación se muestra una lista de algunas de las funciones de Excel que usan matrices:

• LINEST()
• MDETERM()
• MINVERSE()
• MMULT()
• SUM(IF())
• SUMPRODUCT()
• TRANSPOSE()
• TREND()