anotaciones y funciones excel 2003/2007

Download Anotaciones y Funciones Excel 2003/2007

Post on 11-Mar-2016

218 views

Category:

Documents

3 download

Embed Size (px)

DESCRIPTION

Anotaciones sobre Fórmulas matriciales y funciones de Excel

TRANSCRIPT

  • Anotaciones sobre Microsoft Excel 2003

    Por Jess Carranza

  • 2

    Directrices y ejemplos de frmulas de matriz

    Las frmulas de matriz pueden realizar clculos vetados a otros tipos de frmulas. El siguiente artculo se basa en una serie de columnas para usuarios de Excel escritas por Colin Wilcox y adaptadas de los captulos 14 y 15 de Frmulas de Excel 2002, un libro escrito por John Walkenbach, un gran profesional de Excel.

    Obtener informacin acerca de las frmulas de matriz

    En esta seccin se ofrece informacin general acerca de las frmulas de matriz y se explica cmo escribirlas, modificarlas y solucionar los problemas que puedan plantear.

    Por qu utilizar frmulas de matriz?

    Si tiene experiencia en el uso de frmulas de Excel, sabr que es posible realizar algunas operaciones bastante complejas. Por ejemplo, es posible calcular el costo total de un prstamo a lo largo de un nmero concreto de aos. Sin embargo, si realmente desea dominar las frmulas de Excel, tiene que aprender a utilizar frmulas de matriz. stas se pueden emplear para realizar tareas complejas como:

    Contar el nmero de caracteres incluidos en un rango de celdas.

    Sumar nicamente aquellos nmeros que cumplan ciertas condiciones, como los valores ms bajos de un rango o los nmeros comprendidos entre un lmite superior e inferior.

    Sumar cada algo a cada valor de un rango de valores.

    Nota Es posible que descubra que a las frmulas de matriz tambin se las conoce como "frmulas CSE". Esto se debe a que para especificarlas en los libros se presiona CTRL+MAYS+ENTRAR.

    Introduccin rpida a las matrices y las frmulas de matriz

    A efectos de este artculo, una matriz es una coleccin de elementos. En Excel, esos elementos pueden residir en una nica fila (lo que se denomina una matriz horizontal unidimensional), una columna (una matriz vertical unidimensional) o varias filas y columnas (una matriz bidimensional). En Excel no es posible crear matrices ni frmulas de matriz tridimensionales.

    Una frmula de matriz es una frmula que puede realizar varios clculos en uno o varios de los elementos de una matriz. Las frmulas de matriz pueden devolver varios resultados o un nico resultado. Por ejemplo, se puede colocar una frmula de matriz en un rango de celdas y utilizarla para calcular una columna o fila de subtotales. Tambin se puede colocar en una sola celda y calcular una cantidad nica. Una frmula de matriz que reside en varias celdas se denomina frmula de varias celdas, mientras una que reside en una sola celda se denomina frmula de una celda.

    En los ejemplos de la siguiente seccin se muestra cmo crear frmulas de matriz de varias celdas y de una celda.

    Ejercicio

    En este ejercicio se muestra cmo utilizar frmulas de matriz de varias celdas y una celda para calcular un conjunto de cifras de ventas. En el primer conjunto de pasos se emplea una frmula de varias celdas para calcular un conjunto de subtotales. En el segundo se usa una frmula de una celda para calcular un total general.

  • 3

    Crear una frmula de matriz de varias celdas

    Abra un nuevo libro en blanco.

    Copie los datos de la hoja de clculo de ejemplo y, a continuacin, pguelos en el nuevo libro a partir de la celda A1.

    Vendedor Tipo de vehculo Nmero vendido Precio unitario Ventas totales Barnhill Sedn 5 2200 Cup 4 1800 Ingle Sedn 6 2300 Cup 8 1700 Jordan Sedn 3 2000 Cup 1 1600 Pica Sedn 9 2150 Cup 5 1950 Snchez Sedn 6 2250 Cup 8 2000

    Para multiplicar los valores de la matriz (el rango de celdas comprendido entre C2 y D11), seleccione las celdas desde E2 a E11 y, a continuacin, escriba la siguiente frmula en la barra de frmulas:

    =C2:C11*D2:D11

    Presione CTRL+MAYS+ENTRAR.

    Excel incluye la frmula entre llaves ({ }) y coloca una instancia de la misma en cada celda del rango seleccionado. Eso sucede con mucha rapidez, as que lo que ver en la columna E es la cifra de ventas total de cada tipo de vehculo por vendedor.

    Crear una frmula de matriz de una celda

    En la celda A13 del libro, escriba Ventas totales.

    En la celda B13, escriba la siguiente frmula y, a continuacin, presione CTRL+MAYS+ENTRAR:

    =SUMAA(C2:C11*D2:D11)

  • 4

    En este caso, Excel multiplica los valores de la matriz (el rango de celdas entre C2 y D11) y utiliza la funcin SUMAA para agregar los totales. El resultado es un total general de 111.800 $ en ventas. Este ejemplo demuestra lo eficaz que puede resultar este tipo de frmula. Por ejemplo, imagine que tiene 15.000 filas de datos. Puede sumar parte de los datos o la totalidad si crea una frmula de matriz en una sola celda.

    Adems, observe que la frmula de una celda (en la celda B13) es totalmente independiente de la frmula de varias celdas (la frmula de las celdas entre E2 y E11). Eso pone de manifiesto otra ventaja de las frmulas de matriz: la flexibilidad. Es posible realizar innumerables acciones, por ejemplo modificar las frmulas de la columna E o eliminar por completo esa columna, sin que ello afecte a la frmula de una celda.

    Las frmulas de matriz tambin ofrecen estas ventajas:

    Coherencia Si hace clic en cualquiera de las celdas desde E2 hacia abajo, ver la misma frmula. Esa coherencia garantiza una mayor precisin.

    Seguridad No es posible sobrescribir un componente de una frmula de matriz de varias celdas. Por ejemplo, haga clic en la celda E3 y presione SUPR. Tendr que seleccionar todo el rango de celdas (de E2 a E11) y modificar la frmula de la matriz completa o dejar la matriz como est. Como medida de seguridad adicional, tiene que presionar CTRL+MAYS+ENTRAR para confirmar la modificacin de la frmula.

    Tamaos de archivo menores Con frecuencia podr utilizar una frmula de matriz sencilla en lugar de varias frmulas intermedias. Por ejemplo, el libro que ha creado para este ejercicio emplea una frmula de matriz para calcular los resultados de la columna E. Si hubiera utilizado frmulas estndar (como =C2*D2), habra usado 11 frmulas distintas para calcular los mismos resultados.

    Un vistazo a la sintaxis de las frmulas de matriz

    En su mayor parte, las frmulas de matriz usan sintaxis de frmula estndar. Tambin comienzan con un signo igual y se puede utilizar cualquiera de las funciones incorporadas de Excel. La principal diferencia es que al utilizar una frmula de matriz es necesario presionar CTRL+MAYS+ENTRAR para especificarla. Al hacer esto, Excel incluye la frmula de matriz entre llaves; si escribe las llaves manualmente, la frmula se convertir en una cadena de texto y no funcionar.

    Lo siguiente que tiene que entender es que las funciones de matriz son una forma de mtodo abreviado. Por ejemplo, la funcin de varias celdas que ha utilizado anteriormente es el equivalente a: =C2*D2 =C3*D3,

    etc. La frmula de una celda de la celda B13 condensa todas esas operaciones de multiplicacin, ms la aritmtica necesaria para agregar esos subtotales: =E2+E3+E4, etc.

    Reglas para especificar y modificar frmulas de matriz

    La regla principal para crear una frmula de matriz merece repetirse: presione CTRL+MAYS+ENTRAR siempre que sea necesario para especificar o modificar una frmula de matriz. Esa regla se aplica tanto a las frmulas de una celda como de varias.

  • 5

    Siempre que trabaje con frmulas de varias celdas, tambin tendr que seguir estas reglas:

    Tiene que seleccionar el rango de celdas en el que va a incluir los resultados antes de especificar la frmula. Lo hizo en el paso 3 del ejercicio de la frmula de matriz de varias celdas al seleccionar las celdas comprendidas entre E2 y E11.

    No puede modificar el contenido de una celda individual de una frmula de matriz. Para intentarlo, seleccione la celda E3 del libro de ejemplo y presione SUPR.

    Puede mover o eliminar una frmula de matriz completa, pero no parte de la misma. En otras palabras, para reducir una frmula de matriz, primero debe eliminar la frmula existente y comenzar de nuevo.

    Sugerencia Para eliminar una frmula de matriz, seleccione la frmula completa (por ejemplo, =C2:C11*D2:D11), presione SUPR y, a continuacin, CTRL+MAYS+ENTRAR.

    No puede insertar celdas en blanco en una frmula de matriz de varias celdas ni eliminar celdas de la misma.

    Ampliar una frmula de matriz

    A veces es posible que necesite ampliar una frmula de matriz. (Recuerde que no es posible reducirla). El proceso no es complicado, aunque debe recordar las reglas de la seccin anterior.

    En el libro de ejemplo, borre todo el texto y las frmulas de una celda situadas debajo de la tabla principal.

    Pegue estas lneas de datos adicionales en el libro a partir de la celda A12.

    Toth Sedn 6 2500 Cup 7 1900 Solsona Sedn 4 2200 Cup 3 2000 Noriega Sedn 8 2300 Cup 8 2100

    Seleccione el rango de celdas que contiene la frmula de matriz actual (E2:E11) ms las celdas vacas (E12:E17) situadas junto a los nuevos datos. En otras palabras, seleccione las celdas E2:E17.

    Presione F2 para ir al modo de edicin.

    En la barra de frmulas, cambie C11 por C17, D11 por D17 y, a continuacin, presione CTRL+MAYS+ENTRAR. Excel actualiza la frmula de las celdas E2 a E11 y coloca una instancia de la misma en las nuevas celdas, E12 a E17.

    Desventajas de utilizar frmulas de matriz

    Las frmulas de matriz tambin tienen algunas desventajas:

    A veces uno puede olvidarse de presionar CTRL+MAYS+ENTRAR.

    Es posible que otros usuarios no entiendan sus frmulas. Existe relativamente poca documentacin sobre las frmulas de matriz.

    Las frmulas de matriz de gran tamao pueden ralentizar los clculos.

  • 6

    EJERCICIOS:

    Contar los caracteres de un rango de celdas

    En el ejemplo siguiente se muestra cmo co