Anotaciones sobre Microsoft Excel 2003

Por Jesús Carranza

2

Directrices y ejemplos de fórmulas de matriz

Las fórmulas de matriz pueden realizar cálculos vetados a otros tipos de fórmulas. El siguiente artículo se basa en una serie de columnas para usuarios de Excel escritas por Colin Wilcox y adaptadas de los capítulos 14 y 15 de Fórmulas de Excel 2002, un libro escrito por John Walkenbach, un gran profesional de Excel.

Obtener información acerca de las fórmulas de matriz

En esta sección se ofrece información general acerca de las fórmulas de matriz y se explica cómo escribirlas, modificarlas y solucionar los problemas que puedan plantear.

¿Por qué utilizar fórmulas de matriz?

Si tiene experiencia en el uso de fórmulas de Excel, sabrá que es posible realizar algunas operaciones bastante complejas. Por ejemplo, es posible calcular el costo total de un préstamo a lo largo de un número concreto de años. Sin embargo, si realmente desea dominar las fórmulas de Excel, tiene que aprender a utilizar fórmulas de matriz. Éstas se pueden emplear para realizar tareas complejas como:

Contar el número de caracteres incluidos en un rango de celdas.

Sumar únicamente aquellos números que cumplan ciertas condiciones, como los valores más bajos de un rango o los números comprendidos entre un límite superior e inferior.

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

Nota Es posible que descubra que a las fórmulas de matriz también se las conoce como "fórmulas CSE". Esto se debe a que para especificarlas en los libros se presiona CTRL+MAYÚS+ENTRAR.

Introducción rápida a las matrices y las fórmulas de matriz

A efectos de este artículo, una matriz es una colección 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 fórmulas de matriz tridimensionales.

Una fórmula de matriz es una fórmula que puede realizar varios cálculos en uno o varios de los elementos de una matriz. Las fórmulas de matriz pueden devolver varios resultados o un único resultado. Por ejemplo, se puede colocar una fórmula de matriz en un rango de celdas y utilizarla para calcular una columna o fila de subtotales. También se puede colocar en una sola celda y calcular una cantidad única. Una fórmula de matriz que reside en varias celdas se denomina fórmula de varias celdas, mientras una que reside en una sola celda se denomina fórmula de una celda.

En los ejemplos de la siguiente sección se muestra cómo crear fórmulas de matriz de varias celdas y de una celda.

Ejercicio

En este ejercicio se muestra cómo utilizar fórmulas 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 fórmula de varias celdas para calcular un conjunto de subtotales. En el segundo se usa una fórmula de una celda para calcular un total general.

3

Crear una fórmula de matriz de varias celdas

Abra un nuevo libro en blanco.

Copie los datos de la hoja de cálculo de ejemplo y, a continuación, péguelos en el nuevo libro a partir de la celda A1.

Vendedor Tipo de vehículo Número vendido Precio unitario Ventas totales Barnhill Sedán 5 2200 Cupé 4 1800 Ingle Sedán 6 2300 Cupé 8 1700 Jordan Sedán 3 2000 Cupé 1 1600 Pica Sedán 9 2150 Cupé 5 1950 Sánchez Sedán 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 continuación, escriba la siguiente fórmula en la barra de fórmulas:

=C2:C11*D2:D11

Presione CTRL+MAYÚS+ENTRAR.

Excel incluye la fórmula 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 vehículo por vendedor.

Crear una fórmula de matriz de una celda

En la celda A13 del libro, escriba Ventas totales.

En la celda B13, escriba la siguiente fórmula y, a continuación, presione CTRL+MAYÚS+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 función 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 fórmula. Por ejemplo, imagine que tiene 15.000 filas de datos. Puede sumar parte de los datos o la totalidad si crea una fórmula de matriz en una sola celda.

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

Las fórmulas de matriz también ofrecen estas ventajas:

Coherencia Si hace clic en cualquiera de las celdas desde E2 hacia abajo, verá la misma fórmula. Esa coherencia garantiza una mayor precisión.

Seguridad No es posible sobrescribir un componente de una fórmula 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 fórmula de la matriz completa o dejar la matriz como está. Como medida de seguridad adicional, tiene que presionar CTRL+MAYÚS+ENTRAR para confirmar la modificación de la fórmula.

Tamaños de archivo menores Con frecuencia podrá utilizar una fórmula de matriz sencilla en lugar de varias fórmulas intermedias. Por ejemplo, el libro que ha creado para este ejercicio emplea una fórmula de matriz para calcular los resultados de la columna E. Si hubiera utilizado fórmulas estándar (como =C2*D2), habría usado 11 fórmulas distintas para calcular los mismos resultados.

Un vistazo a la sintaxis de las fórmulas de matriz

En su mayor parte, las fórmulas de matriz usan sintaxis de fórmula estándar. También comienzan con un signo igual y se puede utilizar cualquiera de las funciones incorporadas de Excel. La principal diferencia es que al utilizar una fórmula de matriz es necesario presionar CTRL+MAYÚS+ENTRAR para especificarla. Al hacer esto, Excel incluye la fórmula de matriz entre llaves; si escribe las llaves manualmente, la fórmula 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 método abreviado. Por ejemplo, la función de varias celdas que ha utilizado anteriormente es el equivalente a: =C2*D2 =C3*D3,

etc. La fórmula de una celda de la celda B13 condensa todas esas operaciones de multiplicación, más la aritmética necesaria para agregar esos subtotales: =E2+E3+E4, etc.

Reglas para especificar y modificar fórmulas de matriz

La regla principal para crear una fórmula de matriz merece repetirse: presione CTRL+MAYÚS+ENTRAR siempre que sea necesario para especificar o modificar una fórmula de matriz. Esa regla se aplica tanto a las fórmulas de una celda como de varias.

5

Siempre que trabaje con fórmulas de varias celdas, también tendrá que seguir estas reglas:

Tiene que seleccionar el rango de celdas en el que va a incluir los resultados antes de especificar la fórmula. Lo hizo en el paso 3 del ejercicio de la fórmula 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 fórmula de matriz. Para intentarlo, seleccione la celda E3 del libro de ejemplo y presione SUPR.

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

Sugerencia Para eliminar una fórmula de matriz, seleccione la fórmula completa (por ejemplo, =C2:C11*D2:D11), presione SUPR y, a continuación, CTRL+MAYÚS+ENTRAR.

No puede insertar celdas en blanco en una fórmula de matriz de varias celdas ni eliminar celdas de la misma.

Ampliar una fórmula de matriz

A veces es posible que necesite ampliar una fórmula de matriz. (Recuerde que no es posible reducirla). El proceso no es complicado, aunque debe recordar las reglas de la sección anterior.

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

Pegue estas líneas de datos adicionales en el libro a partir de la celda A12.

Toth Sedán 6 2500 Cupé 7 1900 Solsona Sedán 4 2200 Cupé 3 2000 Noriega Sedán 8 2300 Cupé 8 2100

Seleccione el rango de celdas que contiene la fórmula de matriz actual (E2:E11) más las celdas vacías (E12:E17) situadas junto a los nuevos datos. En otras palabras, seleccione las celdas E2:E17.

Presione F2 para ir al modo de edición.

En la barra de fórmulas, cambie C11 por C17, D11 por D17 y, a continuación, presione CTRL+MAYÚS+ENTRAR. Excel actualiza la fórmula de las celdas E2 a E11 y coloca una instancia de la misma en las nuevas celdas, E12 a E17.

Desventajas de utilizar fórmulas de matriz

Las fórmulas de matriz también tienen algunas desventajas:

A veces uno puede olvidarse de presionar CTRL+MAYÚS+ENTRAR.

Es posible que otros usuarios no entiendan sus fórmulas. Existe relativamente poca documentación sobre las fórmulas de matriz.

Las fórmulas de matriz de gran tamaño pueden ralentizar los cálculos.

6

EJERCICIOS:

Contar los caracteres de un rango de celdas

En el ejemplo siguiente se muestra cómo contar el número de caracteres, incluidos los espacios, de un rango de celdas.

En la hoja de cálculo Datos, escriba la siguiente fórmula en la celda C7 y presione CTRL+MAYÚS+ENTRAR:

=SUMAA(LARGO(C1:C5))

En la celda C7 aparece el valor 47.

En este caso, la función LARGO devuelve la longitud de cada cadena de texto de cada una de las celdas del rango. A continuación, la función SUMAA agrega esos valores y muestra el resultado en la celda que contiene la fórmula, C7.

Sumar un rango que contiene valores de error

La función SUMAA de Excel no funciona cuando se intenta sumar un rango que contiene un valor de error, como #N/A. En este ejemplo se muestra cómo sumar los valores de un rango con el nombre Datos que contiene errores.

=SUMAA(SI(ESERROR(Datos),"",Datos))

La fórmula crea una nueva matriz que contiene los valores originales menos los valores de error. A partir de las funciones interiores y hacia fuera, la función ESERROR busca errores en el rango de celdas (Datos). La función SI devuelve un valor concreto si una condición especificada se evalúa en TRUE y otro valor si se evalúa en FALSE. En este caso, devuelve cadenas vacías ("") para todos los valores de error, ya que se evalúan en TRUE, y devuelve los valores restantes del rango (Datos), dado que se evalúan en FALSE, lo que significa que no contienen valores de error. A continuación la función SUMAA calcula el total de la matriz filtrada.

Contar el número de valores de error de un rango

Este ejemplo es similar a la fórmula anterior, pero devuelve el número de valores de error de un rango de nombre Datos en lugar de filtrarlos:

=SUMAA(SI(ESERROR(Datos),1,0))

Esta fórmula crea una matriz que contiene el valor 1 para las celdas que contienen errores y el valor 0 para las que no contienen errores. Puede simplificar la fórmula y conseguir el mismo resultado si quita el tercer argumento de la función SI, de este modo:

=SUMAA(Si(ESERROR(Datos),1))

Si no especifica el argumento, la función SI devuelve FALSE cuando una celda no contiene un valor de error. Puede simplificarla aún más:

=SUMAA(SI(ESERROR(Datos)*1))

Esta versión funciona porque TRUE*1=1 y FALSE*1=0.

7

Sumar valores basados en condiciones

Es posible que necesite sumar valores basados en condiciones. Por ejemplo, esta fórmula de matriz suma únicamente los enteros positivos de un rango de nombre Ventas:

=SUMAA(SI(Ventas>0,Ventas))

La función SI crea una matriz de valores positivos y valores falsos. La función SUMAA básicamente omite los valores falsos, dado que 0+0=0. El rango de celdas que se utiliza en esta fórmula puede estar compuesto por cualquier número de filas y columnas.

También es posible sumar valores que cumplan más de una condición. Por ejemplo, esta fórmula de matriz calcula los valores mayores que 0 y menores o iguales que 5:

=SUMAA((Ventas>0)*(Ventas<=5)*(Ventas))

Tenga en cuenta que esta fórmula devuelve un error cuando el rango contiene una o más celdas no numéricas.

También es posible crear fórmulas de matriz que utilicen un tipo de condición O. Por ejemplo, puede sumar valores que sean menores que 5 y mayores que 15:

=SUMAA(SI((Ventas<5)+(Ventas>15),Ventas))

La función SI busca todos los valores menores que 5 y mayores que 15 y se los pasa a la función SUMAA.

Importante No es posible utilizar las funciones Y y O directamente en las fórmulas de matriz, ya que esas funciones devuelven un único valor, ya sea TRUE o FALSE, y las funciones de matriz necesitan matrices de resultados. Puede solucionar este problema si usa la lógica de la fórmula anterior. En otras palabras, puede realizar operaciones de coincidencia, como suma o multiplicación, en valores que cumplan la condición O o Y.

Calcular una media que excluya los ceros

En este ejemplo se muestra cómo quitar los ceros de un rango cuando necesite calcular la media de los valores de dicho rango. La fórmula utiliza un rango de datos de nombre Ventas:

=PROMEDIO(SI(Ventas<>0,Ventas))

La función SI crea una matriz de valores que no son iguales a 0 y, a continuación, pasa dichos valores a la función PROMEDIO.

Contar el número de diferencias entre dos rangos de celdas

Esta fórmula de matriz compara los valores de dos rangos de celdas denominados MisDatos y TusDatos y devuelve el número de diferencias entre ellos. Si el contenido de los dos rangos es idéntico, la fórmula devuelve 0. Para utilizar esta fórmula, los rangos de celdas deben ser del mismo tamaño y de la misma dimensión:

=SUMAA(SI(MisDatos=TusDatos,0,1))

La fórmula crea una nueva matriz del mismo tamaño que los rangos que se están comparando. La función SI rellena la matriz con el valor 0 y el valor 1 (0 para no coincidencias y 1 para celdas idénticas). A continuación, la función SUMAA devuelve la suma de los valores de la matriz.

8

Puede simplificar la fórmula de esta manera:

=SUMAA(1*(MisDatos<>TusDatos))

Al igual que la fórmula que cuenta los valores de error de un rango, esta fórmula funciona porque TRUE*1=1 y FALSE*1=0.

Buscar la ubicación del valor máximo de un rango

Esta fórmula de matriz devuelve el número de fila del valor máximo de un rango de una columna denominado Datos:

=MIN(SI(Datos=MAX(Datos),FILA(Datos),""))

La función SI crea una nueva matriz que corresponde al rango Datos. Si una celda correspondiente contiene el valor máximo del rango, la matriz contiene el número de fila. De lo contrario, contiene una cadena vacía (""). La función MIN usa la nueva matriz como su segundo argumento y devuelve el valor más pequeño, que corresponde al número de fila del valor máximo de Datos. Si el rango Datos contiene valores máximos idénticos, la fórmula devuelve la fila del primer valor.

Si desea devolver la dirección de celda real de un valor máximo, use esta fórmula:

=DIRECCION(MIN(SI(Datos=MAX(Datos),FILA(Datos),"")),COLUMNA(Datos))

9

Formato de número personalizado

Para crear un formato de número personalizado, lo primero que tiene que hacer es seleccionar uno de los formatos de número integrados como punto de partida. A continuación, puede cambiar alguna de las secciones de código de este formato para crear su propio formato de número personalizado.

Un formato de número puede tener hasta cuatro secciones de código, separadas por caracteres de punto y coma. Estas secciones de código definen el formato de los números positivos, los números negativos, los valores de cero y el texto, en ese orden.

<POSITIVO>;<NEGATIVO>;<CERO>;<TEXTO>

Por ejemplo, puede usar estas secciones de código para crear el siguiente formato personalizado:

[Azul]#.##0,00_);[Rojo](#.##0,00);0,00;"ventas "@

No tiene que incluir todas las secciones de código en el formato de número personalizado. Si sólo especifica dos secciones de código para el formato de número personalizado, la primera sección se utiliza para los números positivos y los ceros, y la segunda sección se utiliza para los números negativos. Si sólo especifica una sección de código, ésta se utiliza para todos los números. Si desea omitir una sección de código e incluir la siguiente, debe insertar el carácter de punto y coma de la sección que va a omitir.

Las siguientes directrices pueden ayudarle a personalizar cualquiera de estas secciones de código de formato de número.

Directrices para incluir texto y agregar espacio

Mostrar texto y números Para mostrar texto y números en una celda, incluya los caracteres de texto entre dobles comillas (" ") o inserte una barra diagonal inversa (\) delante de un carácter. Incluya los caracteres en la sección correspondiente de los códigos de formato. Por ejemplo, escriba el formato $0,00" Superávit";$-0,00" Déficit" para mostrar un importe positivo como "$125,74 Superávit" y un importe negativo como "$-125,74 Déficit". Observe que hay un espacio delante de "Superávit" y "Déficit" en cada sección de código.

Los siguientes caracteres se muestran sin el uso de comillas.

$ Signo de dólar + Signo más ( Paréntesis de apertura : Dos puntos ^ Acento circunflejo ' Apóstrofo { Llave de apertura < Signo menor que = Signo de igualdad

- Signo menos / Barra diagonal ) Paréntesis de cierre ! Signo de exclamación & Y comercial ~ Tilde } Llave de cierre > Signo mayor que Carácter de espacio

Incluir una sección de entrada de texto Si se incluye, la sección de texto siempre es la última sección del formato de número. Incluya un carácter de arroba (@) en la sección en la que desea mostrar el texto que escribe en la celda. Si el carácter @ se omite en la sección de texto, no se mostrará el texto que escriba. Si desea mostrar siempre caracteres de texto específicos con el

10

texto escrito, incluya el texto adicional entre un carácter de dobles comillas (" "). Por ejemplo, "recibos brutos de "@

Repetir caracteres Para repetir el siguiente carácter del formato hasta rellenar el ancho de la columna, incluya un asterisco (*) en el formato de número. Por ejemplo, escriba 0*- para incluir los guiones que hagan falta detrás de un número para rellenar la celda, o escriba *0 delante del formato para incluir ceros iniciales.

Directrices para usar posiciones decimales, espacios, colores y condiciones

Incluir posiciones decimales y dígitos significativos Para aplicar formato a fracciones o números que contienen comas decimales, incluya los siguientes marcadores de posición de dígitos, comas decimales y separadores de millar en una sección.

0 (cero)

Este marcador de posición de dígitos muestra los ceros no significativos si un número tiene menos dígitos que los ceros especificados en el formato. Por ejemplo, si escribe 8,9 y desea que se muestre como 8,90, use el formato #,00.

# Este marcador de posición de dígitos sigue las mismas reglas que el 0 (cero). Sin embargo, Excel no muestra ceros adicionales cuando el número que escribe tiene menos dígitos a ambos lados de la coma decimal que el número de símbolos # especificados en el formato. Por ejemplo, si el formato personalizado es #,## y escribe 8,9 en la celda, se muestra el número 8,9.

? Este marcador de posición de dígitos sigue las mismas reglas que el 0 (cero). Sin embargo, Excel agrega un espacio para los ceros no significativos a ambos lados de la coma decimal para que las comas decimales queden alineadas en la columna. Por ejemplo, el formato personalizado 0,0? alinea las posiciones decimales de los números 8,9 y 88,99 en una columna.

, (coma)

Este marcador de posición de dígitos muestra la coma decimal en un número.

Si un número tiene más dígitos a la derecha de la coma decimal que marcadores de posición en el formato, el número se redondea a tantas posiciones decimales como marcadores de posición haya. Si hay más dígitos a la izquierda de la coma decimal que marcadores de posición, se muestran los dígitos adicionales. Si el formato sólo contiene signos de almohadilla (#) a la izquierda de la coma decimal, los números menores que 1 comienzan con una coma decimal; por ejemplo, ",47".

Mostrar un separador de millar Para mostrar un punto como separador de millar o para ajustar un número a un múltiplo de 1.000, incluya el siguiente separador en el formato de número.

. (punto)

Muestra el separador de millar en un número. Excel separa los millares con puntos si el formato contiene un punto incluido entre signos de almohadilla (#) o ceros. Un punto detrás de un marcador de posición de dígitos ajusta el número a 1.000. Por ejemplo, si el formato personalizado es #,0. y escribe 12.200.000 en la celda, se muestra el número 12,200,0.

Para mostrar Como Utilice este código 12000 12.000 #.### 12000 12 #. 12200000 12,2 0,0..

11

Especificar colores Para especificar el color de una sección del formato, escriba el nombre de uno de estos ocho colores entre corchetes en la sección. El código de color debe ser el primer elemento de la sección.

[Negro] [Verde] [Blanco] [Azul] [Magenta] [Amarillo] [Aguamarina] [Rojo]

Especificar condiciones Para especificar formatos de número que sólo se apliquen si un número cumple una condición especificada, incluya la condición entre corchetes. La condición consta de un operador de comparación (operador de comparación: signo que se utiliza en criterios de comparación para comparar dos valores. Entre los operadores se incluyen: = Igual a, > Mayor que, < Menor que, >= Mayor o igual que, <= Menor o igual que y <> No igual a.) y un valor. Por ejemplo, el siguiente formato muestra los números menores o iguales a 100 en una fuente roja y los números mayores que 100 en una fuente azul.

[Rojo][<=100];[Azul][>100]

Para aplicar formatos condicionales (formato condicional: formato (por ejemplo, un sombreado de celda o un color de fuente) que Excel aplica automáticamente a las celdas si la condición que se especifica es cierta.) a las celdas (por ejemplo, un color de sombra en función del valor de una celda), en la ficha Inicio, en el grupo Estilos, haga clic en Formato condicional.

Directrices para formatos de fecha y hora

Mostrar los días, meses y años Para mostrar los números como formatos de fecha (como días, meses y años), utilice los siguientes códigos en una sección.

Para mostrar Como Utilice este código Meses 1–12 m Meses 01–12 mm Meses Ene-Dic mmm Meses Enero–Diciembre mmmm Meses E–D mmmmm Días 1–31 d Días 01–31 dd Días Dom–Sáb ddd Días Domingo-Sábado dddd Años 00–99 aa Años 1900–9999 aaaa

12

Mostrar horas, minutos y segundos Para mostrar los formatos de hora (como horas, minutos y segundos), utilice los siguientes códigos en una sección.

h Muestra la hora como un número sin un cero inicial. [h] Muestra el tiempo transcurrido en horas. Si trabaja con un fórmula que devuelve un

período de tiempo en el que el número de horas es mayor que 24, utilice un formato de número similar a [h]:mm:ss.

hh Muestra la hora como un número con un cero inicial si procede. Si el formato contiene a.m. o p.m., la hora se basa en un reloj de 12 horas. En caso contrario, la hora se basa en un reloj de 24 horas.

m Muestra el minuto como un número sin un cero inicial. Nota El código m o mm debe aparecer inmediatamente detrás del código h o hh o inmediatamente delante del código ss; en caso contrario, Excel muestra el mes en lugar de los minutos.

[m] Muestra el tiempo transcurrido en minutos. Si trabaja con un fórmula que devuelve un período de tiempo en el que el número de minutos es mayor que 60, utilice un formato de número similar a [mm]:ss.

mm Muestra el minuto como un número con un cero inicial si procede. Nota El código m o mm debe aparecer inmediatamente detrás del código h o hh o inmediatamente delante del código ss; en caso contrario, Excel muestra el mes en lugar de los minutos.

s Muestra el segundo como un número sin un cero inicial. [s] Muestra el tiempo transcurrido en segundos. Si trabaja con una fórmula que

devuelve un período de tiempo en el que el número de segundos es mayor que 60, utilice un formato de número similar a [ss].

ss Muestra el segundo como un número con un cero inicial si procede. Si desea mostrar fracciones de un segundo, utilice un formato de número similar a h:mm:ss.00.

AM/PM, a.m./p.m., A/P, a/p

Muestra la hora con un reloj de 12 horas. Excel muestra AM, a.m., A o a para los horas desde las 12 de la noche a las 12 del mediodía, y PM, p.m., P o p para las horas desde las 12 del mediodía hasta las 12 de la noche.

13

Operadores

Tipos de operadores

Existen cuatro tipos de operadores de cálculo: aritmético, comparación, concatenación de texto y referencia.

Operadores aritméticos

Para ejecutar las operaciones matemáticas básicas como suma, resta o multiplicación, combinar números y generar resultados numéricos, utilice los siguientes operadores aritméticos.

Operador aritmético Significado Ejemplo

+ (signo más) Suma 3+3

– (signo menos) Resta Negación

3–1 –1

* (asterisco) Multiplicación 3*3

/ (barra oblicua) División 3/3

% (signo de porcentaje) Porcentaje 20%

^ (acento circunflejo) Exponenciación 3^2

Operadores de comparación

Se pueden comparar dos valores con los siguientes operadores. Cuando se comparan dos valores utilizando estos operadores, el resultado es un valor lógico: VERDADERO o FALSO.

Operador de comparación Significado Ejemplo

= (signo igual) Igual a A1=B1

> (signo mayor que) Mayor que A1>B1

< (signo menor que) Menor que A1<B1

>= (signo mayor o igual que) Mayor o igual que A1>=B1

<= (signo menor o igual que) Menor o igual que A1<=B1

<> (signo distinto de) Distinto de A1<>B1

Operador de concatenación de texto

Utilice el signo (&) para unir o concatenar una o varias cadenas de texto con el fin de generar un solo elemento de texto.

14

Operadores de referencia

Combine rangos de celdas para los cálculos con los siguientes operadores.

Operador de referencia

Significado Ejemplo

: (dos puntos) Operador de rango, que genera una referencia a todas las celdas entre dos referencias, éstas incluidas.

B5:B15

; (punto y coma) Operador de unión, que combina varias referencias en una sola SUMAA(B5:B15;D5:D15)

(espacio) Operador de intersección, que genera una referencia a las celdas comunes a las dos referencias

B7:D7 C6:C8

Prioridad de operadores

Si se combinan varios operadores en una única fórmula, Excel ejecutará las operaciones en el orden que se indica en la siguiente tabla. Si una fórmula contiene operadores con la misma prioridad (por ejemplo, si una fórmula contiene un operador de multiplicación y otro de división), Excel evaluará los operadores de izquierda a derecha.

Operador Descripción

: (dos puntos)

(un solo espacio)

; (punto y coma)

Operadores de referencia

– Negación (como en –1)

% Porcentaje

^ Exponenciación

* y / Multiplicación y división

+ y - Suma y resta

& Conecta dos cadenas de texto (concatenación)

= < > <= >= <>

Comparación

Uso de paréntesis

Para cambiar el orden de evaluación, escriba entre paréntesis la parte de la fórmula que se calculará en primer lugar.

15

Principales Funciones

MATEMÁTICAS Y TRIGONOMÉTRICAS

MULTIPLO.SUPERIOR

Redondea un número al próximo múltiplo del argumento cifra_significativa, hacia arriba. Por ejemplo, si desea que los decimales de los precios de un producto sean siempre múltiplo de 5 y el precio del producto es 4,42 $, utilice la fórmula =MULTIPLO.SUPERIOR(4,42;0,05) para redondear los precios al múltiplo de 5 más próximo.

Sintaxis

MULTIPLO.SUPERIOR(número;cifra_significativa)

Número es el valor que se desea redondear.

Cifra_significativa es el múltiplo al que se desea redondear.

Observaciones

Si uno de los argumentos es un valor no numérico, devuelve el valor de error #¡VALOR! Independientemente del signo de número, un valor se redondea hacia arriba. Si el argumento número es

un múltiplo exacto del argumento cifra_significativa, no se redondea. Si los argumentos número y cifra_significativa tienen signos diferentes, MULTIPLO.SUPERIOR devuelve el

valor de error #¡NUM!

Ejemplos

A B

Fórmula Descripción (resultado)

=MULTIPLO.SUPERIOR(2,5;1) Redondea 2,5 al múltiplo superior de 1 más próximo (3)

=MULTIPLO.SUPERIOR(-2,5;-2) Redondea -2,5 al múltiplo superior de -2 más próximo (-4)

=MULTIPLO.SUPERIOR(-2,5;2) Devuelve un error porque -2,5 y 2 tienen signos diferentes (#¡NUM!).

=MULTIPLO.SUPERIOR(0,234;0,01) Redondea 0,234 al múltiplo superior de 0,01 más próximo (0,24).

MULTIPLO.INFERIOR

Redondea un número al próximo múltiplo del argumento cifra_significativa, hacia arriba. Por ejemplo, si desea que los decimales de los precios de un producto sean siempre múltiplo de 5 y el precio del producto es 4,42 $, utilice la fórmula =MULTIPLO.INFERIOR(4,42;0,05) para redondear los precios al múltiplo de 5 más próximo.

Sintaxis

MULTIPLO.INFERIOR(número;cifra_significativa)

Número es el valor que se desea redondear.

Cifra_significativa es el múltiplo al que se desea redondear.

16

Observaciones

Si uno de los argumentos es un valor no numérico, MULTIPLO.INFERIOR devuelve el valor de error #¡VALOR!

Independientemente del signo de número, un valor se redondea hacia arriba. Si el argumento número es un múltiplo exacto del argumento cifra_significativa, no se redondea.

Si los argumentos número y cifra_significativa tienen signos diferentes, MULTIPLO.INFERIOR devuelve el valor de error #¡NUM!

Ejemplos

A B

Fórmula Descripción (resultado)

=MULTIPLO.INFERIOR(2,5;1) Redondea 2,5 al múltiplo superior de 1 más próximo (3)

=MULTIPLO.INFERIOR(-2,5;-2) Redondea -2,5 al múltiplo superior de -2 más próximo (-4)

=MULTIPLO.INFERIOR(-2,5;2) Devuelve un error porque -2,5 y 2 tienen signos diferentes (#¡NUM!).

=MULTIPLO.INFERIOR(1,5;0,1) Redondea 1,5 al múltiplo superior de 0,1 más próximo (1,5).

=MULTIPLO.INFERIOR(0,234;0,01) Redondea 0,234 al múltiplo superior de 0,01 más próximo (0,24).

ENTERO

Redondea un número hasta el entero inferior más próximo.

Sintaxis

ENTERO(número)

Número es el número real que se desea redondear al entero inferior más próximo.

ABS

Devuelve el valor absoluto de un número. El valor absoluto de un número es el número sin su signo.

Sintaxis

ABS(número)

Número es el número real cuyo valor absoluto desea obtener.

17

REDONDEAR

La función REDONDEAR redondea un número a un número de decimales especificado. Por ejemplo, si la celda A1 contiene 23,7825 y desea REDONDEAR ese valor a dos posiciones decimales, puede usar la siguiente fórmula:

=REDONDEAR(A1; 2)

El resultado de esta función es 23,78.

Sintaxis

REDONDEAR(número; núm_decimales)

número Obligatorio. Número que desea REDONDEAR.

núm_decimales Obligatorio. Número de decimales al que desea REDONDEAR el argumento de número.

Observaciones

Si el argumento núm_decimales es mayor que 0 (cero), el número se redondea al número de lugares decimales especificado.

Si el núm_decimales es 0, el número se redondea al número entero más próximo.

Si núm_decimales es menor que 0, el número se redondea hacia la izquierda del separador decimal.

Para REDONDEAR al alza (lejos de cero), use la función REDONDEARUP.

Para REDONDEAR a la baja (hacia cero), use la función REDONDEARDOWN.

TRUNCAR

TRUNCARa un número a un entero, suprimiendo la parte fraccionaria de dicho número.

Sintaxis

TRUNCAR(número; núm_decimales)

Número es el número que se desea TRUNCAR.

Núm_decimales es un número que especifica la precisión del TRUNCARamiento. El valor predeterminado del argumento núm_decimales es 0.

Observaciones

TRUNCAR y INT son similares, ya que ambos devuelven enteros. TRUNCAR suprime la parte fraccionaria del número. INT redondea los números al entero menor más próximo, según el valor de la porción fraccionaria del número. INT y TRUNCAR son diferentes solamente cuando se usan números negativos: TRUNCAR(-4,3) devuelve -4, pero INT(-4,3) devuelve -5, ya que -5 es el número entero menor más cercano.

18

RESIDUO

Devuelve el RESIDUO o resto de la división entre número y núm_divisor. El resultado tiene el mismo signo que núm_divisor.

Sintaxis

RESIDUO(número;núm_divisor)

Número es el número que se desea dividir y cuyo RESIDUO o resto desea obtener.

Núm_divisor es el número por el cual se desea dividir el argumento número.

Observaciones

Si núm_divisor es 0, RESIDUO devuelve el valor de error #¡DIV/0!

La función RESIDUO se puede expresar utilizando la función ENTERO:

RESIDUO(n, d) = n - d*ENTERO(n/d)

ALEATORIO

Devuelve un número real aleatorio mayor o igual a 0 y menor que 1, distribuido uniformemente. Cada vez que se calcula la hoja de cálculo, se devuelve un número real aleatorio nuevo.

Sintaxis

ALEATORIO( )

Observaciones

Para generar un número real aleatorio entre a y b, use:

ALEATORIO()*(b-a)+a

Ejemplos

A B

Fórmula Descripción (resultado)

=ALEATORIO() Un número aleatorio entre 0 y 1 (varía)

=ALEATORIO()*100 Un número aleatorio igual o superior a 0 pero menor que 100 (varía)

NUMERO.ROMANO

Convierte un número arábigo en número romano con formato de texto.

Sintaxis

NUMERO.ROMANO(número;forma)

Número es el número arábigo que se desea convertir.

19

Forma es un número que especifica el tipo de número romano que se desea obtener. El estilo de número romano varía entre clásico y simplificado; cuanto más aumenta el valor del argumento forma, más conciso es el estilo devuelto. Vea el ejemplo que sigue a NUMERO.NUMERO.ROMANOO(499;0).

Observaciones

Si el argumento número es negativo, la función devuelve el valor de error #¡VALOR!

Si el argumento número es mayor que 3999, la función devuelve el valor de error #¡VALOR!

Ejemplo

A B

Fórmula Descripción (resultado)

=NUMERO.NUMERO.ROMANOO(499;0) Estilo de número romano clásico para 499 (CDXCIX)

=NUMERO.NUMERO.ROMANOO(499;1) Versión más concisa para 499 (LDVLIV)

=NUMERO.NUMERO.ROMANOO(499;2) Versión más concisa para 499 (XDIX)

=NUMERO.NUMERO.ROMANOO(499;3) Versión más concisa para 499 (VDIV)

=NUMERO.NUMERO.ROMANOO(499;4) Versión más concisa para 499 (ID)

=NUMERO.NUMERO.ROMANOO(2013;0) Estilo de número romano clásico para 2013 (MMXIII)

SUMA

Suma todos los números de un rango.

Sintaxis

SUMA(número1;número2; ...)

Número1;número2;... son de 1 a 30 argumentos cuyo valor total o suma desea obtener.

Observaciones

Si un argumento es una matriz o una referencia, sólo se considerarán los números de esa matriz o referencia. Se pasarán por alto las celdas vacías, los valores lógicos o el texto contenidos en la matriz o en la referencia.

Los argumentos que sean valores de error o texto que no se pueda traducir a números u obviar provocan errores.

SUMAR.SI

La función SUMAR.SI sirve para sumar los valores en un rango que cumple los criterios que se especifican. Por ejemplo, supongamos que en una columna que contiene números, desea sumar sólo los valores que son mayores que 5. Puede usar la siguiente fórmula:

=SUMAR.SI(B2:B25,">5")

20

En este ejemplo, los criterios se aplican a los mismos valores que se están sumando. Si lo desea, puede aplicar los criterios a un rango y sumar los valores correspondientes en un rango distinto. Por ejemplo, la fórmula =SUMAR.SI(B2:B5, "Juan", C2:C5) suma sólo los valores del rango C2:C5, donde las celdas correspondientes al rango B2:B5 son iguales a "Juan."

Sintaxis

SUMAR.SI(range, criteria, [sum_range])

La sintaxis de la función SUMAR.SI cuenta con los argumentos siguientes:

rango Obligatorio. El rango de celdas que se desea evaluar según los criterios especificados. Las celdas de cada rango deben ser números, o bien nombres, matrices o referencias que contengan números. Los valores en blanco y los de texto no se tienen en cuenta.

criterios Obligatorio. El criterio en forma de número, expresión o texto, que determina las celdas que se van a sumar. Por ejemplo, los criterios pueden expresarse como 32, ">32", B5, 32, "32", "manzanas" u HOY().

Importante Cualquier criterio de texto o cualquier criterio que incluya los símbolos lógicos o matemáticos debe estar entre comillas dobles (").

rango_suma Opcional. Las celdas reales para agregar, si desea agregar celdas a las ya especificadas en el argumento rango. Si se omite el argumento rango_suma, Excel agrega las celdas que están especificadas en el argumento rango (las mismas celdas a las que se aplica los criterios).

SUBTOTALES

Devuelve un subtotal en una lista o base de datos. Generalmente es más fácil crear una lista con subtotales utilizando el comando Subtotales del menú Datos. Una vez creada la lista de subtotales, puede cambiarse modificando la fórmula SUBTOTALES

Sintaxis

SUBTOTALES(núm_función;valor1, valor2, ...)

Núm_función es un número de 1 a 11 (incluye los valores ocultos) o de 101 a 111 (pasa por alto valores ocultos) que indica qué función debe utilizarse para calcular los subtotales dentro de una lista.

Núm_función (incluye valores ocultos)

Núm_función (pasa por alto valores ocultos)

Función

1 101 PROMEDIO

2 102 CONTAR

3 103 CONTARA

4 104 MAX

5 105 MIN

6 106 PRODUCTO

7 107 DESVEST

21

8 108 DESVESTP

9 109 SUMAA

10 110 VAR

11 111 VARP

Valor1, valor2 son de 1 a 29 rangos o referencias para los cuales desea calcular el subtotal.

Observaciones

Si hay otros subtotales dentro de valor1 (o subtotales anidados), estos subtotales anidados se pasarán por alto para no repetir los cálculos.

Para las constantes núm_función de 1 a 11, la función SUBTOTALESES incluye los valores de las filas ocultas por el comando Ocultar filas, situado bajo el submenú Ocultar y mostrar del comando Formato del grupo Celdas de la ficha Inicio. Utilice estas constantes cuando desee calcular subtotales de los números ocultos y visibles de una lista. Para las constantes núm_función de 101 a 111, la función SUBTOTALESES pasa por alto los valores de filas ocultos por el comando Ocultar filas. Utilice estas constantes cuando sólo desee calcular subtotales de los números visibles de una lista.

La función SUBTOTALESES pasa por alto las filas que no se hayan incluido en el resultado de un filtro, independientemente del valor de núm_función que utilice.

La función SUBTOTALESES está diseñada para columnas de datos o rangos verticales. No está diseñada para filas de datos ni para rangos horizontales. Por ejemplo, cuando desea calcular el subtotal de un rango horizontal mediante un núm_función de 101 o superior, como SUBTOTALESES(109,B2:G2), si oculta una columna no afecta al subtotal. Sin embargo, si oculta una fila en un subtotal de un rango vertical, sí afectará al subtotal.

Si alguna de las referencias es una referencia 3D, SUBTOTALESES devolverá el valor de error #¡VALOR!.

FECHA Y HORA

FECHA

La función FECHA devuelve el número de serie secuencial que representa una fecha determinada. Por ejemplo, la fórmula

=FECHA(2008;7;8) devuelve 39637, el número de serie que representa la fecha 8/7/2008.

TIME hace lo mismo pero para horas.

FECHANUMERO

La función FECHANUMERO devuelve el número de serie secuencial que representa una fecha dada en formato de texto. Por ejemplo, la fórmula

=datevalue(“22-AUG-08”) devuelve 39637, el número de serie que representa la fecha 22/8/2008.

TIMEVALUE hace lo mismo pero para horas

AÑO / MES / DIA / HORA /MINUTO / SEGUNDO

Reciben como argumento una fecha y devuelven el año mes … respectivamente.

22

ESTADÍSTICAS

CONTAR / CONTARA / CONTARBLANK

La función CONTAR cuenta la cantidad de celdas que contienen números y cuenta los números dentro de la lista de argumentos. Use la función CONTAR para obtener la cantidad de entradas en un campo de número de un rango o matriz de números. Por ejemplo, puede escribir la siguiente fórmula para contar los números en el rango A1:A20:

=CONTAR(A1:A20)

En este ejemplo, si cinco de las celdas del rango contienen números, el resultado es 5.

Sintaxis

CONTAR(valor1; [valor2],...)

valor1 Obligatorio. Primer elemento, referencia de celda o rango en el que desea contar números.

valor2, ... Opcional. Hasta 255 elementos, celdas de referencia o rangos adicionales en los que desea contar números.

Nota Los argumentos pueden contener o hacer referencia a una variedad de diferentes tipos de datos, pero sólo se cuentan los números.

Observaciones

Se cuentan argumentos que son números, fechas o una representación de texto de los números (por ejemplo, un número entre comillas, como "1").

Se tienen en cuenta los valores lógicos y las representaciones textuales de números escritos directamente en la lista de argumentos.

No se cuentan los argumentos que sean valores de error o texto que no se puedan traducir a números.

Si un argumento es una matriz o una referencia, sólo se considerarán los números de esa matriz o referencia. No se cuentan celdas vacías, valores lógicos, texto o valores de error de la matriz o de la referencia.

Si desea contar valores lógicos, texto o valores de error, use la función CONTARA.

Para contar celdas en vacías use la función CONTARBLANK.

CONTAR.SI

La función CONTAR.SI cuenta el número de celdas dentro de un rango que cumplen un solo criterio especificado por el usuario. Por ejemplo, puede contar todas las celdas que contienen un número mayor o menor que un número especificado por el usuario. Por ejemplo, suponga que tiene una hoja de cálculo que contiene una lista de tareas en la columna A y el primer nombre de la persona asignada a cada tarea en la columna B. Puede usar la función CONTAR.SI para contar cuántas veces aparece el nombre de una persona en la columna B y, de esa forma, determinar cuántas tareas tiene asignadas. Por ejemplo:

=CONTAR.SI(B2:B25,"Nancy")

Sintaxis

CONTAR.SI(range, criteria)

23

rango Obligatorio. Una o más celdas que se van a contar, incluidos números o nombres, matrices o referencias que contengan números. Los valores en blanco y los de texto no se tienen en cuenta.

criterios Obligatorio. Número, expresión, referencia de celda o cadena de texto que determina las celdas que se van a contar. Por ejemplo, los criterios pueden expresarse como 32, ">32", B4, "manzanas" o "32".

Los criterios no distinguen entre mayúsculas y minúsculas; por ejemplo, la cadena "manzanas" y la cadena "MANZANAS" corresponden a las mismas celdas.

PROMEDIO/ AVERAGEA

La función PROMEDIOcalcula la media aritmética de los argumentos numéricos, teniendo en cuenta los valores cero, AVERAGEA hace la misma operación teniendo en cuenta que también las celdas con texto o valores lógicos FALSE se toman como cero y valores lógicos TRUE como 1.

MEDIANA

Devuelve la mediana de los números dados. La mediana es el número que se encuentra en medio de un conjunto de números.

Sintaxis

MEDIANAA(número1;número2; ...)

Número1, número2... son de 1 a 255 números cuya mediana desea obtener.

Observaciones

Si la cantidad de números en el conjunto es par, MEDIANAA calcula el promedio de los números centrales. Vea la segunda fórmula del ejemplo.

Los argumentos pueden ser números, o nombres, matrices o referencias que contengan números.

Se tienen en cuenta los valores lógicos y las representaciones textuales de números escritos directamente en la lista de argumentos.

Si el argumento matricial o de referencia contiene texto, valores lógicos o celdas vacías, estos valores se pasan por alto; sin embargo, se incluirán las celdas con el valor cero.

Los argumentos que sean valores de error o texto que no se pueda traducir a números provocan errores.

A Datos 1 2 3 4 5 6 Fórmula Descripción (resultado) =MEDIANAA(A2:A6) La mediana de los 5 primeros números de la lista anterior (3) =MEDIANAA(A2:A7) La mediana de todos los números anteriores, o el promedio de 3 y 4 (3,5)

24

MODA

Devuelve el valor que se repite con más frecuencia en una matriz o rango de datos.

Sintaxis

MODA(número1;número2; ...)

Número1, número2... son de 1 a 255 argumentos cuya moda desea calcular. También puede utilizar una matriz única o una referencia matricial en lugar de argumentos separados con punto y coma.

Observaciones

Los argumentos pueden ser números, o nombres, matrices o referencias que contengan números.

Si el argumento matricial o de referencia contiene texto, valores lógicos o celdas vacías, estos valores se pasan por alto; sin embargo, se incluirán las celdas con el valor cero.

Los argumentos que sean valores de error o texto que no se pueda traducir a números provocan errores.

Si el conjunto de datos no contiene puntos de datos duplicados, MODA devuelve el valor de error #N/A.

A Datos 5,6 4 4 3 2 4 Fórmula Descripción (resultado) =MODA(A2:A7) Moda, es decir, número que se repite con más frecuencia (4)

JERARQUIA

Devuelve la jerarquía de un número en una lista de números. La jerarquía de un número es su tamaño en comparación con otros valores de la lista. (Si ordenara la lista, la jerarquía del número sería su posición.)

Sintaxis

JERARQUIA(número;referencia;orden)

Número es el número cuya jerarquía (clasificación) desea conocer.

Referencia es una matriz de una lista de números o una referencia a una lista de números. Los valores no numéricos se pasan por alto.

Orden es un número que especifica cómo clasificar el argumento número.

Si el argumento orden es 0 (cero) o se omite, Microsoft Excel determina la jerarquía de un número como si la lista definida por el argumento referencia fuese ordenada en forma descendente.

Si el argumento orden es diferente de cero, Microsoft Excel determina la jerarquía de un número como si la lista definida por el argumento referencia se ordenara en forma ascendente.

Observaciones

25

La función JERARQUIA asigna la misma jerarquía a los números duplicados. Sin embargo, la presencia de números duplicados afecta la jerarquía de los números subsiguientes. Por ejemplo, en una lista de números enteros ordenados en orden ascendente, si el número 10 aparece dos veces y tiene una jerarquía de 5, entonces el número 11 tendría una jerarquía de 7 (ningún número tendría jerarquía de 6).

Ejemplo

A Datos 7 3,5 3,5 1 2 Fórmula Descripción (resultado) =JERARQUIA(A3;A2:A6;1) Jerarquía de 3,5 en la lista anterior (3) =JERARQUIA(A2;A2:A6;1) Una jerarquía de 7 en la lista anterior (5)

MIN / MINA / MAX / MAXA

La función MIN devuelve el valor mínimo de los argumentos solo tiene en cuenta los valores numéricos, mientras que MINA tiene en cuenta textos y valores lógicos, TRUE se valora como 1.

La función MAX devuelve el valor máximo de los argumentos solo tiene en cuenta los valores numéricos, mientras que MAX tiene en cuenta textos y valores lógicos, TRUE se valora como 1.

K.ESIMO.MAYOR / K.ESIMO.MENOR

Devuelve el k-ésimo mayor valor (K.ESIMO.MAYOR) o k-ésimo menor valor (K.ESIMO.MENOR) de un conjunto de datos. Esta función puede usarse para seleccionar un valor basándose en su posición relativa. Por ejemplo, se puede utilizar K.ESIMO.MAYOR para devolver el mayor valor de un resultado, el segundo resultado o el tercero.

Sintaxis

K.ESIMO.MAYOR / K.ESIMO.MENOR(matriz;k)

Matriz es la matriz o rango de datos cuyo k-ésimo mayor valor desea determinar.

K representa la posición (a partir del mayor valor), dentro de la matriz o rango de celdas, de los datos que se van a devolver.

Observaciones

Si la matriz está vacía K.ESIMO.MAYOR Y K.ESIMO.MENOR devuelven el valor de error #¡NUM!

Si el argumento ≤ 0 o si k es mayor o menor según la función usada que el número de puntos de datos, K.ESIMO.MAYOR y K.ESIMO.MENOR devuelven el valor de error #¡NUM!

Ejemplo

A B Datos Datos

26

3 4 5 2 3 4 5 6 4 7 Fórmula Descripción (resultado) =K.ESIMO.MAYOR(A2:B6;3) Tercer número más alto de los anteriores (5) =K.ESIMO.MAYOR(A2:B6;7) 7º número más alto de los anteriores (4)

FRECUENCIA

Calcula la frecuencia con que se repiten los valores de un rango y devuelve un matriz vertical de números. Por ejemplo, utilice FRECUENCIA para contar el número de los resultados que se encuentran dentro de un rango. Debe especificarse como una fórmula de matrices debido a que devuelve una matriz.

Sintaxis

FRECUENCIA(datos;grupos)

Datos es una matriz de un conjunto de valores o una referencia a un conjunto de valores cuyas frecuencias se desea contar. Si datos no contiene ningún valor, FRECUENCIA devuelve una matriz de ceros.

Grupos es una matriz de intervalos o una referencia a intervalos dentro de los cuales se desea agrupar los valores del argumento datos. Si grupos no contiene ningún valor, FRECUENCIA devuelve el número de elementos contenidos en datos.

Observaciones

FRECUENCIA se especifica como una fórmula de matriz.El número de elementos de la matriz devuelta supera en una unidad el número de elementos de grupos. El elemento adicional de la matriz devuelta devuelve la suma de todos los valores superiores al mayor intervalo. Por ejemplo, al sumar tres rangos de valores (intervalos) especificados en tres celdas, asegúrese de especificar FRECUENCIA en cuatro celdas para los resultados. La celda adicional devuelve el número de valores en grupos que sean superiores al valor del tercer intervalo.

Ejemplo

A B Resultados Bandejas 79 70 85 79 78 89 85 50 81 95 88 97 Fórmula Descripción (resultado) =FRECUENCIA(A2:A10;B2:B4) Número de puntuaciones menores o iguales que 70 (1) Número de puntuaciones en la bandeja 71-79 (2) Número de puntuaciones en la bandeja 80-89 (4) Número de puntuaciones mayores o iguales que 90 (2)

27

BÚSQUEDA Y REFERENCIA

DIRECCION

Puede usar la función DIRECCIONpara obtener la dirección de una celda en una hoja de cálculo, con los números específicos de fila y columna. Por ejemplo, ADDRESS(2;3) devuelve $C$2. Otro ejemplo, DIRECCION(77;300) devuelve $KN$77. Puede usar otras funciones, como FILA y COLUMNA, para proporcionar los argumentos de número de fila y columna para la función ADDRESS.

Sintaxis

ADDRESS(fila; columna; [abs]; [a1]; [nombre_hoja])

La sintaxis de la función DIRECCIONtiene los siguientes argumentos:

fila Obligatorio. Valor numérico que especifica el número de fila que se va a usar en la referencia de celda.

columna Obligatorio. Valor numérico que especifica el número de columna que se va a usar en la referencia de celda.

abs Opcional. Valor numérico que especifica el tipo de referencia que se devolverá.

abs Devuelve este tipo de referencia 1 u omitido Absoluta

2 Fila absoluta, columna relativa 3 Fila relativa, columna absoluta 4 Relativa

A1 Opcional. Valor lógico que especifica el estilo de referencia A1 o R1C1. En el estilo A1, las columnas se rotulan en orden alfabético y las filas se rotulan en orden numérico. En el estilo de referencia R1C1, las columnas y filas se rotulan numéricamente. Si el argumento A1 es VERDADERO o se omite, la función DIRECCIONdevuelve una referencia de estilo A1; si es FALSO, la función DIRECCIONdevuelve una referencia de estilo R1C1.

Nota Para cambiar el estilo de referencia que usa Excel, haga clic en el botón de Microsoft Office , en Opciones de Excel y, a continuación, haga clic en Fórmulas. En Trabajando con fórmulas, active o desactive la casilla Estilo de referencia R1C1.

hoja Opcional. Valor de texto que especifica el nombre de la hoja de cálculo que se usará como referencia externa. Por ejemplo, la fórmula =ADDRESS(1;1;;;"Hoja2") devuelve Hoja2!$A$1. Si se omite el argumento hoja, no se usa ningún nombre de hoja y la dirección que devuelve la función hace referencia a una celda de la hoja actual.

ELEGIR

Utiliza el argumento núm_índice para devolver un valor de una lista de argumentos de valores. Utilice ELEGIR para seleccionar uno de los 254 valores posibles a partir del rango del argumento índice. Por ejemplo, si valor1 a valor7 son los días de la semana, ELEGIR devuelve uno de los días cuando se utiliza un número entre 1 y 7 como argumento núm_índice.

28

Sintaxis

ELEGIR(núm_índice;valor1;valor2;...)

Núm_índice especifica el argumento de valor que se selecciona. El argumento núm_índice debe ser un número entre 1 y 254, o bien, una fórmula o referencia a una celda que contenga un número entre 1 y 254.

Si núm_índice es 1, ELEGIR devuelve valor1; si es 2, ELEGIR devuelve valor2 y así sucesivamente.

Si núm_índice es menor que 1 o mayor que el número del último valor de la lista, ELEGIR devuelve el valor de error #¡VALOR!.

Si núm_índice es una fracción, se TRUNCARa al entero inferior antes de ser utilizada.

Valor1,valor2,... son de 1 a 254 argumentos de valores entre los cuales la función ELEGIR selecciona un valor o acción que se ejecuta basándose en el argumento núm_índice. Los argumentos pueden ser números, referencias a celdas, nombres definidos, fórmulas, funciones o texto.

Observaciones

Si núm_índice es una matriz, cada valor se evaluará cuando se evalúe ELEGIR.

Los argumentos de valor para ELEGIR pueden ser referencias de rango, así como valores individuales.

Por ejemplo, la fórmula:

=SUMAA(ELEGIR(2;A1:A10;B1:B10;C1:C10))

evalúa:

=SUMAA(B1:B10)

que después devuelve un valor basado en los valores del rango B1:B10.

La función ELEGIR se evalúa primero, devolviendo la referencia B1:B10. La función SUMAA se evalúa a continuación utilizando B1:B10, que es el resultado de la función ELEGIR, como su argumento.

A B Datos Datos 1º Clavos 2º Tornillos 3º Tuercas Terminado Pernos Fórmula Descripción (resultado) =ELEGIR(2;A2;A3;A4;A5) Valor del segundo argumento A3 (2º) =ELEGIR(4;B2;B3;B4;B5) Valor del cuarto argumento B5 (Pernos)

COLUMNA / COLUMNAS / FILA / FILAS

Devuelven respectivamente la el número que representa la columna de la referencia, la cantidad de columnas que hay en una referencia, el número que representa la fila de una referencia, la cantidad de filas dentro de una referencia.

29

BUSCARH / BUSCARV / BUSCAR

Busca un valor en la fila superior de una tabla o una matriz de valores y, a continuación, devuelve un valor en la misma columna de una fila especificada en la tabla o matriz. Use BUSCARH cuando los valores de comparación se encuentren en una fila en la parte superior de una tabla de datos y desee encontrar información que se encuentre dentro de un número especificado de filas. Use BUSCARV cuando los valores de comparación se encuentren en una columna a la izquierda o de los datos que desee encontrar.

Sintaxis

BUSCARH / BUSCARV(valor_buscado;matriz_buscar_en;indicador_filas; ordenado)

Valor_buscado es el valor que se busca en la primera fila de la tabla. Valor_buscado puede ser un valor, una referencia o una cadena de texto.

Matriz_buscar_en es una tabla de información en la que se buscan los datos. Utilice una referencia a un rango o el nombre de un rango.

Los valores de la primera fila del argumento matriz_buscar_en pueden ser texto, números o valores lógicos.

Si ordenado es VERDADERO, los valores de la primera fila de matriz_buscar_en deben colocarse en orden ascendente: ...-2, -1, 0, 1, 2, ..., A-Z, FALSO, VERDADERO; de lo contrario, BUSCARH / BUSCARV puede devolver un valor incorrecto. Si ordenado es FALSO, no es necesario ordenar matriz_buscar_en.

El texto en mayúsculas y en minúsculas es equivalente.

Indicador_filas es el número de fila en matriz_buscar_en desde el cual debe devolverse el valor coincidente. Si indicador_filas es 1, devuelve el valor de la primera fila en matriz_buscar_en; si indicador_filas es 2, devuelve el valor de la segunda fila en matriz_buscar_en y así sucesivamente. Si indicador_filas es menor que 1, BUSCARH / BUSCARV devuelve el valor de error #¡VALOR!; si indicador_filas es mayor que el número de filas en matriz_buscar_en, BUSCARH / BUSCARV devuelve el valor de error #¡REF!

Ordenado es un valor lógico que especifica si BUSCARH / BUSCARV debe localizar una coincidencia exacta o aproximada. Si es VERDADERO o se omite, devolverá una coincidencia aproximada. Es decir, si no encuentra ninguna coincidencia exacta, devolverá el siguiente valor mayor que sea inferior a valor_buscado. Si es FALSO, BUSCARH / BUSCARV encontrará una coincidencia exacta. Si no encuentra ninguna, devolverá el valor de error #N/A.

Observaciones

Si BUSCARH / BUSCARV no logra encontrar valor_buscado, utiliza el mayor valor que sea menor que valor_buscado.

Si valor_buscado es menor que el menor valor de la primera fila de matriz_buscar_en, BUSCARH / BUSCARV devuelve el valor de error #N/A.

La función BUSCAR devuelve un valor de un rango de una fila o una columna o de una matriz. La función BUSCAR tiene dos formas de sintaxis: la forma vectorial y la matricial.

Forma vectorial

Un vector es un rango de una sola fila o columna. La forma vectorial de BUSCAR busca un valor en un rango de una columna o una fila (denominado vector) y devuelve un valor desde la misma posición en un segundo rango de una

30

columna o una fila. Use esta forma de la función BUSCAR cuando desee especificar el rango que incluya los valores que desea BUSCAR. La otra forma de la función BUSCAR busca automáticamente en la primera columna o fila.

Sintaxis

BUSCAR(valor_buscado; vector_de_comparación; vector_resultado)

valor_buscado Obligatorio. Valor que busca la función BUSCAR en el primer vector. Valor_buscado puede ser un número, texto, un valor lógico o un nombre de referencia que se refiere a un valor.

vector_de_comparación Obligatorio. Rango que sólo contiene una fila o una columna. Los valores del vector_de_comparación pueden ser texto, números o valores lógicos.

Importante Los valores del vector_de_comparación se deben colocar en orden ascendente: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSO, VERDADERO; de lo contrario, BUSCAR puede devolver un valor incorrecto. El texto en mayúsculas y en minúsculas es equivalente.

vector_resultado Obligatorio. Rango que sólo incluye una fila o una columna. El argumento vector_resultado debe tener el mismo tamaño que vector_de_comparación.

Observaciones

Si la función BUSCAR no puede encontrar el valor_buscado, la función muestra el valor más grande en vector_de_comparación que es menor o igual al valor_buscado.

Si el valor_buscado es menor que el menor valor del vector_de_comparación, BUSCAR devuelve el valor de error #N/A.

Forma matricial

La forma matricial de BUSCAR busca el valor especificado en la primera fila o columna de una matriz y devuelve un valor de la misma posición en la última fila o columna de la matriz. Use esta forma de BUSCAR cuando los valores que desea BUSCAR están en la primera fila o columna de la matriz. Use la otra forma de BUSCAR cuando desea especificar la ubicación de la columna o fila.

Sugerencia En general, es mejor usar las funciones BUSCARH o BUSCARV en lugar de la forma matricial de BUSCAR. Esta forma de BUSCAR se proporciona por cuestiones de compatibilidad con otros programas de hojas de cálculo.

Sintaxis

BUSCAR(valor_buscado; matriz)

La sintaxis de la forma matricial de la función BUSCAR tiene estos argumentos:

valor_buscado Obligatorio. Valor que busca la función BUSCAR en una matriz. El argumento valor_buscado puede ser un número, texto, un valor lógico o un nombre de referencia que se refiere a un valor.

Si BUSCAR no puede encontrar el valor de valor_buscado, usa el mayor valor de la matriz que es menor o igual que el valor_buscado.

Si el valor de valor_buscado es menor que el menor valor de la primera fila o columna (según las dimensiones de la matriz), BUSCAR devuelve el valor de error #N/A.

matriz Obligatorio. Rango de celdas que contiene texto, números o valores lógicos que desea comparar con valor_buscado.

31

La forma matricial de BUSCAR es muy similar a las funciones BUSCARH y BUSCARV. La diferencia es que BUSCARH busca el valor de valor_buscado en la primera fila, BUSCARV busca en la primera columna y BUSCAR realiza la búsqueda según las dimensiones de la matriz.

Si la matriz cubre un área que es más ancha que alta (más columnas que filas), BUSCAR busca el valor de valor_buscado en la primera fila.

Si una matriz es cuadrada o más alta que ancha (tiene más filas que columnas), BUSCAR busca en la primera columna.

Con las funciones BUSCARH y BUSCARV, puede especificar un índice en vertical o en horizontal, pero BUSCAR siempre selecciona el último valor de la fila o columna.

Importante Los valores de la matriz se deben colocar en orden ascendente: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSO, VERDADERO; de lo contrario, BUSCAR puede devolver un valor incorrecto. El texto en mayúsculas y en minúsculas es equivalente.

COINCIDIR

La función COINCIDIR busca un elemento especificado en un rango de celdas y, a continuación, devuelve la posición relativa de ese elemento en el rango. Por ejemplo, si el rango A1:A3 contiene los valores 5, 25 y 38, la fórmula

=COINCIDIR(25;A1:A3;0)

devuelve el número 2, porque 25 es el segundo elemento en el rango.

Use COINCIDIR en lugar de una de las funciones BUSCAR cuando necesite conocer la posición de un elemento en un rango en lugar del elemento en sí. Por ejemplo, puede usar la función COINCIDIR para proporcionar un valor para el argumento fila de la función INDICE.

Sintaxis

COINCIDIR(valor_buscado; matriz_buscada; tipo_de_coincidencia])

La sintaxis de la función COINCIDIR tiene los siguientes argumentos:

valor_buscado Obligatorio. Valor que desea BUSCAR en matriz_buscada. Por ejemplo, cuando busca un número en la guía telefónica, usa el nombre de la persona como valor de búsqueda, pero el valor que desea es el número de teléfono.

El argumento valor_buscado puede ser un valor (número, texto o valor lógico) o una referencia de celda a un número, texto o valor lógico.

matriz_buscada Obligatorio. Rango de celdas en el que se realiza la búsqueda.

tipo_de_coincidencia Opcional. Puede ser el número -1, 0 o 1. El argumento tipo_de_coincidencia especifica cómo Excel hace COINCIDIR el valor_buscado con los valores de matriz_buscada. El valor predeterminado de este argumento es 1.

En la siguiente tabla se describe cómo la función busca valores según la configuración del argumento tipo_de_coincidencia.

Tipo_de_coincidencia Comportamiento 1 u omitido COINCIDIR encuentra el mayor valor que es menor o igual que el valor_buscado. Los

valores del argumento matriz_buscada se deben colocar en orden ascendente, por ejemplo: ...-2, -1, 0, 1, 2, ..., A-Z, FALSO, VERDADERO.

0 COINCIDIR encuentra el primer valor que es exactamente igual que el valor_buscado. Los

32

valores del argumento matriz_buscada pueden estar en cualquier orden. -1 COINCIDIR encuentra el menor valor que es mayor o igual que el valor_buscado. Los

valores del argumento matriz_buscada se deben colocar en orden descendente, por ejemplo: VERDADERO, FALSO, Z-A, ...2, 1, 0, -1, -2, ..., etc.

Notas

COINCIDIR devuelve la posición del valor coincidente dentro de la matriz_buscada, no el valor en sí.

COINCIDIR no distingue entre mayúsculas y minúsculas cuando busca valores de texto.

Si COINCIDIR no puede encontrar una coincidencia, devuelve el valor de error #N/A.

TRANSPONER

La función TRANSPONER devuelve un rango de celdas vertical como un rango horizontal o viceversa. La función TRANSPONER debe especificarse como una fórmula de matriz en un rango que tenga el mismo número de filas y columnas, respectivamente, que el rango de origen. Use TRANSPONER para cambiar la orientación vertical y horizontal de una matriz o un rango de una hoja de cálculo.

Sintaxis

TRANSPONER(array)

La sintaxis de la función TRANSPONER tiene el argumento siguiente:

matriz Obligatorio. Una matriz o un rango de celdas de una hoja de cálculo que desea TRANSPONER. La transposición de una matriz se crea usando la primera fila de la matriz como primera columna de la nueva matriz, la segunda fila de la matriz como segunda columna de la nueva matriz, etc.

A B C Datos Datos Datos 1 2 3 Fórmula Descripción Resultado =TRANSPONER($A$2:$C$2) Valor de la primera

columna 1

Valor de la segunda columna

2

Valor de la tercera columna 3

Importante La fórmula que se muestra en el ejemplo debe escribirse como una fórmula de matriz para que la función TRANSPONER funcione según lo previsto. Después de copiar el ejemplo en una hoja de cálculo en blanco, seleccione el rango A4:A6 comenzando por la celda de la fórmula. Presione F2 y, a continuación, CTRL+MAYÚS+ENTRAR. Si la fórmula no se escribe como fórmula de matriz, el resultado único es 1.

DESREF

Devuelve una referencia a un rango que es un número de filas y de columnas de una celda o rango de celdas. La referencia devuelta puede ser una celda o un rango de celdas. Puede especificar el número de filas y el número de columnas a devolver.

Sintaxis

DESREF(ref;filas;columnas;alto;ancho)

33

Ref es la referencia en la que se desea basar la desviación. Ref debe referirse a una celda o rango de celdas adyacentes; en caso contrario, DESREF devuelve el valor de error #¡VALOR!

Filas es el número de filas, hacia arriba o hacia abajo, al que se desea que haga referencia la celda superior izquierda. Si el argumento filas es 5, la celda superior izquierda de la referencia pasa a estar cinco filas más abajo que la referencia. Filas puede ser positivo (lo que significa que está por debajo de la referencia de inicio) o negativo (por encima).

Columnas es el número de columnas, hacia la derecha o izquierda, al que se desea que haga referencia la celda superior izquierda del resultado. Si el argumento columnas es 5, la celda superior izquierda de la referencia pasa a estar cinco columnas hacia la derecha de la referencia. Columnas puede ser positivo (lo que significa a la derecha de la referencia de inicio) o negativo (a la izquierda).

Alto es el alto, en número de filas, que se desea que tenga la referencia devuelta. El alto debe ser un número positivo.

Ancho es el ancho, en número de columnas, que se desea que tenga la referencia devuelta. El argumento ancho debe ser un número positivo.

Observaciones

Si los argumentos filas y columnas colocan la referencia más allá del borde de la hoja de cálculo, DESREF devuelve el valor de error #¡REF!

Si los argumentos alto o ancho se omiten, los valores predeterminados serán los del argumento ref.

DESREF en realidad no desplaza celdas ni modifica la selección, simplemente devuelve una referencia. Se puede utilizar la función DESREF con cualquier función que necesite una referencia como argumento. Por ejemplo, la fórmula SUMAA(DESREF(C2;1;2;3;1)) calcula el valor total de un rango de tres filas por una columna que se encuentra por debajo una fila y dos columnas a la derecha de la celda C2.

Ejemplo

A B Fórmula Descripción (resultado) =DESREF(C3;2;3;1;1) Muestra el valor en la celda F5 (0) =SUMAA(DESREF(C3:E5;-1;0;3;3))

Suma el rango C2:E4 (0)

=DESREF(C3:E5;0;-3;3;3) Devuelve un error porque la referencia no está en la hoja de cálculo (#¡REF!)

INDIRECTO

Devuelve la referencia especificada por una cadena de texto. Las referencias se evalúan de inmediato para presentar su contenido. Use INDIRECTO cuando desee cambiar la referencia a una celda en una fórmula sin cambiar la propia fórmula.

Sintaxis

INDIRECTO(ref;a1)

Ref es una referencia a una celda que contiene una referencia de tipo A1 o F1C1, un nombre definido como referencia o una referencia a una celda como cadena de texto. Si ref no es una referencia de celda válida, INDIRECTO devuelve el valor de error #¡REF!

34

Si ref hace referencia a otro libro (una referencia externa), el otro libro debe estar abierto. Si el libro de origen no está abierto, INDIRECTO devolverá el valor de error #¡REF!

A1 es un valor lógico que especifica el tipo de referencia que contiene la celda ref.

Si a1 es VERDADERO o se omite, ref se interpreta como una referencia estilo A1.

Si a1 es FALSO o se omite, ref se interpreta como una referencia estilo F1C1.

Ejemplo

1.

A B Datos Datos B2 1.333 B3 45 Marina 10 5 62 Fórmula Descripción (resultado) =INDIRECTO($A$2) Valor de la referencia en la celda A2 (1,333) =INDIRECTO($A$3) Valor de la referencia en la celda A3 (45) =INDIRECTO($A$4) Si la celda B4 tiene definido el nombre "Marina," se devuelve el valor del nombre

definido (10). =INDIRECTO("B"&$A$5) Valor de la celda B5 (62)

Cuando cree una fórmula que haga referencia a una celda, la referencia a la celda se actualizará si: (1) la celda se desplaza utilizando el comando Cortar para eliminarla, o (2) si la celda se desplaza porque se insertan o eliminan filas o columnas. Si desea que la fórmula siempre haga referencia a la misma fórmula sin tener en cuenta si se elimina o desplaza la fila sobre la celda, utilice la función de hoja de cálculo INDIRECTO. Por ejemplo, si desea que siempre se haga referencia a la celda A10, utilice la sintaxis siguiente:

=INDIRECTO("A10")

INDICE

Devuelve un valor o la referencia a un valor en una tabla o rango. La función INDICE presenta dos formas: matricial y de referencia.

Forma matricial

Devuelve el valor de un elemento de una tabla o matriz seleccionado por los índices de número de fila y de columna.

Use la forma matricial si el primer argumento de INDICE es una constante matricial.

INDICE(matriz;núm_fila;núm_columna)

Matriz es un rango de celdas o una constante de matriz.

Si matriz contiene sólo una fila o columna, el argumento núm_fila o núm_columna correspondiente es opcional.

35

Si matriz tiene más de una fila y más de una columna y sólo utiliza núm_fila o núm_columna, INDICE devuelve una matriz con toda una fila o columna.

Núm_fila selecciona, en el rango matriz, la fila desde la cual se devolverá un valor. Si se omite núm_fila, se requiere el argumento núm_columna.

Núm_columna selecciona, en el rango matriz, la columna desde la cual se devolverá un valor. Si se omite núm_columna, se requiere el argumento núm_fila.

Observaciones

Si se utilizan los argumentos núm_fila y núm_columna, INDICE devuelve el valor de la celda donde se produce la intersección de los argumentos.

Si se define núm_fila o núm_columna como 0 (cero), INDICE devuelve la matriz de valores de toda la columna o fila, respectivamente. Para utilizar valores devueltos como una matriz, escriba la función INDICE como una fórmula de matriz en un rango horizontal de celdas para una fila y en un rango vertical de celdas para una columna. Para especificar una fórmula de matriz, presione CTRL+MAYÚS+ENTRAR.

Los argumentos núm_fila y núm_columna deben indicar una celda incluida en matriz; de lo contrario, INDICE devuelve el valor de error #¡REF!

Forma de referencia

Devuelve la referencia de la celda ubicada en la intersección de una fila y de una columna determinadas. Si el argumento ref es una selección múltiple, se podrá elegir la selección en la que se buscará la referencia.

INDICE(ref;núm_fila;núm_columna;núm_área)

Ref es una referencia a uno o varios rangos de celdas.

Si especifica un rango no adyacente como argumento ref, escríbalo entre paréntesis.

Si cada área del argumento ref contiene una sola fila o columna, el argumento núm_fila o núm_columna respectivamente, es opcional. Por ejemplo, utilice INDICE(ref;;núm_columna) para un argumento ref con una sola fila.

Núm_fila es el número de la fila en el argumento ref desde la que se devolverá una referencia.

Núm_columna es el número de la columna en el argumento ref desde la que se devolverá una referencia.

Núm_área selecciona un rango en el argumento ref desde el cual se devolverá la intersección de núm_fila y núm_columna. La primera área seleccionada o especificada se numera con 1, la segunda con 2 y así sucesivamente. Si se omite núm_área, INDICE usa área 1.

Por ejemplo, si ref describe las celdas (A1:B4;D1:E4;G1:H4), entonces núm_área 1 es el rango A1:B4, núm_área 2 será el rango D1:E4, y núm_área 3 es el rango G1:H4.

Observaciones

Después de que ref y núm_área hayan seleccionado un rango determinado, núm_fila y núm_columna seleccionan una celda específica: núm_fila 1 es la primera fila del rango, núm_columna 1 es la primera columna y así sucesivamente. La referencia devuelta por INDICE es la intersección entre núm_fila y núm_columna.

Si se define núm_fila o núm_columna como 0 (cero), INDICE devuelve la referencia de toda la fila o columna, según corresponda.

36

Núm_fila, núm_columna y núm_área deberán dirigirse a una celda en ref, de lo contrario la función INDICE devuelve el valor de error #¡REF! Si núm_fila y núm_columna se omiten, INDICE devuelve el área del argumento ref definido por núm_área.

El resultado de la función INDICE es una referencia y será interpretada como tal por otras fórmulas.

HIPERVINCULO

Crea un acceso directo o un salto que abre un documento almacenado en un servidor de red, en una intranet o en Internet. Cuando haga clic en la celda que contenga la función HIPERVINCULO, Microsoft Excel abrirá el archivo almacenado en ubicación_del_vínculo.

Sintaxis

HIPERVINCULO(ubicación_del_vínculo;nombre_descriptivo)

Ubicación_del_vínculo es la ruta y el nombre de archivo del documento que se desea abrir como texto. Ubicación_del_vínculo puede hacer referencia a un lugar de un documento, como por ejemplo una celda específica o un rango con nombre de una hoja de cálculo o un libro de Microsoft Excel, o a un marcador incluido en un documento de Microsoft Word. La ruta puede apuntar a un archivo almacenado en una unidad de disco duro, o bien a una ruta UNC (Universal Naming Convention, convención de nomenclatura universal) de un servidor, en Microsoft Excel para Windows, o una ruta URL (Uniform Resource Locator, localizador uniforme de recursos) de Internet o de una intranet.

Ubicación_del_vínculo puede ser una cadena de texto encerrada entre comillas o una celda que contiene el vínculo como cadena de texto.

Si el salto especificado en ubicación_del_vínculo no existe o no está permito desplazarse por él, aparecerá un error cuando se haga clic en la celda.

Nombre_descriptivo es el texto o valor numérico del vínculo o que se muestra en la celda. El nombre_descriptivo se muestra en azul y está subrayado. Si se omite contenido_de_celda, la celda muestra ubicación_del_vínculo como texto del vínculo.

Nombre_descriptivo puede ser un valor, una cadena de texto, un nombre o una celda que contiene el texto o valor al que se salta.

Si nombre_descriptivo devuelve un valor de error (por ejemplo, #¡VALOR!), la celda mostrará el error en lugar del texto de salto.

Observación

Para seleccionar una celda que contiene un hipervínculo sin ir al destino del hipervínculo, haga clic en la celda y

mantenga presionado el botón del mouse hasta que el cursor se convierta en una cruz y, a continuación, suelte el botón.

Ejemplos

En el siguiente ejemplo se abre una hoja de cálculo denominada Informe presupuestario.xls que se almacena en Internet en el sitio ejemplo.microsoft.com/informe y muestra el texto "Haga clic para obtener un informe":

=HIPERVINCULO("http://ejemplo.microsoft.com/informe/Informe presupuestario.xls", "Haga clic para obtener un informe")

37

En el siguiente ejemplo se crea un hipervínculo a la celda F10 de la hoja de cálculo denominada Anual en el libro Informe presupuestario.xls, que está almacenado en Internet en el sitio ejemplo.microsoft.com/informe. La celda en la hoja de cálculo que contiene el hipervínculo muestra el contenido de la celda D1 como el texto al que se salta:

=HIPERVINCULO("[http://ejemplo.microsoft.com/informe/Informe presupuestario.xls]Anual!F10", D1)

En el ejemplo siguiente se crea un hipervínculo al rango TotalDept de la hoja de cálculo Primer trimestre del libro Informe presupuestario.xls, que está almacenado en Internet en el sitio ejemplo.microsoft.com/informe. La celda en la hoja de cálculo que contiene el hipervínculo muestra el texto "Haga clic para ver el Total del primer trimestre del departamento":

=HIPERVINCULO("[http://ejemplo.microsoft.com/informe/Informe presupuestario.xls]Primer trimestre!TotalDept", "Haga clic para ver el Total del primer trimestre del departamento")

Para crear un hipervínculo a una ubicación específica en Microsoft Word, debe utilizar un marcador para definir la ubicación a la que desea saltar en el documento. En el siguiente ejemplo se crea un hipervínculo al marcador denominado BenefTrim en el documento denominado Informe anual.doc en el sitio ejemplo.microsoft.com:

=HIPERVINCULO("[http://ejemplo.microsoft.com/Informe anual.doc]BenefTrim", "Informe de beneficios trimestral")

En Excel para Windows, en el ejemplo siguiente se muestra el contenido de la celda D5 como el texto que va a saltarse en la celda y abre el archivo denominado 1trim.xls que se almacena en el servidor denominado FINANZAS en la carpeta compartida Facturas. En este ejemplo se utiliza la ruta CUN:

=HIPERVINCULO("\\FINANZAS\Facturas\1trim.xls", D5)

En el ejemplo siguiente se abre el archivo 1trim.xls en Excel para Windows que se almacena en un directorio denominado Finanzas en la unidad D y se muestra el valor numérico almacenado en la celda H10:

=HIPERVINCULO("D:\FINANZAS\1trim.xls", H10)

En Excel para Windows, el siguiente ejemplo crea un hipervínculo al área denominada Totales en otro libro (externo), Milibro.xls:

=HIPERVINCULO("[C:\Mis documentos\Milibro.xls]Totales")

En Microsoft Excel para Macintosh, el siguiente ejemplo muestra "Haga clic aquí" en la celda y abre el archivo denominado Primer trimestre almacenado en una carpeta denominada Informes presupuestarios en el disco duro denominado Macintosh HD:

=HIPERVINCULO("Macintosh HD:Informes presupuestarios:Primer trimestre", "Haga clic aquí")

Se pueden crear hipervínculos dentro de una hoja de cálculo para saltar de una celda a otra. Por ejemplo, si la hoja de cálculo activa es la hoja denominada Junio en el libro denominado Presupuesto, la siguiente fórmula crea un hipervínculo a la celda E56. El texto del vínculo es el valor de la celda E56.

=HIPERVINCULO("[Presupuesto]Junio!E56", E56)

Para saltar a otra hoja del mismo libro, cambie el nombre de la hoja en el vínculo. En el ejemplo anterior, para crear un vínculo a la celda E56 en la hoja septiembre, cambie la palabra "Junio" por "Septiembre".

38

BASE DE DATOS

BDPROMEDIO/ BDCONTAR / BDMAX / BDMIN / BDSUMA / BDPRODUCTO / BDEXTRAER

Antes de trabajar con ellas un poco a cerca de los filtros y cálculo de subtotales. El siguiente apartado está confeccionado a partir de capturas de pantalla por lo que las traducciones de los nombre de función no se han realizado, pero son bastante intuitivas y como estaba estupendo merece la pena aun con los nombre de función en español.

39

40

41

42

43

44

TEXTOS

CONCATENAR

Concatena dos o más cadenas en una cadena de texto.

Sintaxis

CONCATENAR (texto1;texto2; ...)

Texto1, texto2... son de 2 a 255 elementos de texto que se unirán en un elemento de texto único. Los elementos de texto pueden ser cadenas de texto, números o referencias a celdas únicas.

Observaciones

También puede utilizar el operador de cálculo símbolo de "y" comercial (&) en lugar de la función CONCATENAR para unir elementos de texto. Por ejemplo =A1&B1 devuelve el mismo valor que =CONCATENAR(A1;B1).

MAYUSC / MINUSC / NOMPROPIO

Ponen, respectivamente, el texto en mayúsculas, minúsculas o tipo de título.

Sintaxis

MAYUSC / MINUSC / NOMPROPIO(texto)

Texto es el texto que se desea pasar a mayúsculas. El argumento texto puede ser una referencia o una cadena de texto.

LARGO

Devuelve la longitud, en caracteres de una cadena de texto.

Sintaxis

LARGO(texto)

Texto es el texto cuya longitud desea saber. Los espacios se cuentan como caracteres.

ESPACIOS / LIMPIAR

ESPACIOS Elimina los espacios del texto, excepto el espacio normal que se deja entre palabras. Use ESPACIOS en texto procedente de otras aplicaciones que pueda contener un espaciado irregular.

LIMPIAR Elimina caracteres que no se pueden imprimir. Use LIMPIAR en un texto importado de otras aplicaciones que contenga caracteres que posiblemente no se puedan imprimir en su sistema operativo.

Sintaxis

ESPACIOS / LIMPIAR (texto) Texto es el texto del que desea quitar espacios.

45

DERECHA / IZQUIERDA / EXTRAE

IZQUIERDA devuelve el primer carácter o caracteres de una cadena de texto, según el número de caracteres que especifique el usuario.

DERECHA devuelve el último carácter o caracteres de una cadena de texto, según el número de caracteres que el usuario especifica.

IZQUIERDA / DERECHA(texto;núm_de_caracteres)

Texto Es la cadena de texto que contiene los caracteres que se desea extraer.

Núm_de_caracteres Especifica el número de caracteres que se desea extraer con la función IZQUIERDA.

Núm_de_caracteres debe ser mayor o igual a 1.

Si núm_de_caracteres es mayor que la longitud del texto, IZQUIERDA devolverá todo el texto.

Si núm_de_caracteres se omite, se calculará como 1.

EXTRAE devuelve un número específico de caracteres de una cadena de texto, comenzando en la posición que especifique y en función del número de caracteres que especifique.

Sintaxis

EXTRAE(texto;posición_inicial;núm_de_caracteres)

Texto Cadena de texto que contiene los caracteres que se desea extraer.

Posición_inicial Posición del primer carácter que se desea extraer del texto. La posición_inicial del primer carácter de texto es 1, y así sucesivamente.

Núm_de_caracteres Especifica el número de caracteres que se desea que EXTRAE devuelva del argumento texto.

Núm_bytes Especifica el número de caracteres de texto que se desea que EXTRAEB devuelva, en bytes.

Observaciones

Si posición_inicial es mayor que la longitud de texto, EXTRAE devuelve "" (texto vacío).

Si posición_inicial es menor que la longitud de texto, pero posición_inicial más núm_de_caracteres excede la longitud de texto, EXTRAE devuelve los caracteres hasta el final de texto.

Si posición_inicial es menor que 1, EXTRAE devuelve el valor de error #¡VALOR!. O Si núm_de_caracteres es negativo, EXTRAE devuelve el valor de error #¡VALOR!.

Ejemplo EXTRAE

A Datos Flujo de líquido Fórmula Descripción (resultado) =EXTRAE(A2;1;5) Cinco caracteres de la cadena anterior, a partir del primero (Flujo) =EXTRAE(A2;7;20) Veinte caracteres de la cadena anterior, a partir del séptimo (de líquido)

46

HALLAR / ENCONTRAR

Las funciones HALLAR y ENCONTRAR buscan una cadena de texto dentro de una segunda cadena de texto y devuelven el número de la posición inicial de la primera cadena de texto desde el primer carácter de la segunda cadena de texto. Por ejemplo, para buscar la ubicación de la letra "p" en la palabra "impresora", puede usar la siguiente función:

=HALLAR / ENCONTRAR("p","impresora")

Esta función devuelve 3 porque "p" es el tercer carácter en la palabra "impresora."

Además, puede buscar por palabras dentro de otras palabras. Por ejemplo, la función

=HALLAR / ENCONTRAR ("medio","promedio")

devuelve 4, porque la palabra "medio" comienza en el cuarto carácter de la palabra "promedio". Puede usar las funciones HALLAR y ENCONTRAR para determinar la ubicación de un carácter o cadena de texto dentro de otra cadena de texto y, a continuación, usar las función EXTRAE para volver al texto o usar las funciones para cambiar el texto.

Sintaxis

HALLAR / ENCONTRAR(find_text, within_text, [start_num])

La sintaxis de las funciones HALLAR y ENCONTRAR cuenta con los siguientes argumentos:

texto_buscado Obligatorio. El texto que desea buscar.

dentro_del_texto Obligatorio. El texto en el que desea encontrar el valor del argumento texto_buscado.

núm_inicial Opcional. El número de carácter en el argumento dentro_del_texto donde desea iniciar la búsqueda.

Observaciones

Las función HALLAR no distinguen mayúsculas de minúsculas. Si desea realizar una búsqueda que distinga mayúsculas de minúsculas, utilice ENCONTRAR.

Si no se puede hallar el argumento texto_buscado, la función devuelve el valor de error #¡VALOR!.

Si el argumento núm_inicial se omite, el valor predeterminado es 1.

Si el valor del argumento núm_inicial es mayor que 0 (cero), o si es mayor que la longitud del argumento dentro_del_texto, se devuelve el valor de error #¡VALOR!.

Use núm_inicial para omitir un número específico de caracteres. Por ejemplo, suponga que usa la cadena de texto "AYF0093.AtuendoParaJóvenes" con la función. Para encontrar la ubicación de la primera "A" en la parte descriptiva de la cadena de texto, establezca núm_inicial en 8, de modo que la función no busque en la parte correspondiente al número de serie (en este caso, "AYF0093"). La función comienza la operación de búsqueda en la octava posición del carácter, encuentra el carácter que está especificado en el argumento texto_buscado en la próxima posición, y devuelve el número 9. La función siempre devuelve el número de caracteres desde el principio del argumento dentro_del_texto y cuenta los caracteres omitidos si el argumento núm_inicial es mayor que 1.

47

REEMPLAZAR

REEMPLAZAR reemplaza parte de una cadena de texto, en función del número de caracteres que se especifique, por una cadena de texto diferente.

Sintaxis

REEMPLAZAR(texto_original;núm_inicial;núm_de_caracteres;texto_nuevo)

Texto_original es el texto en el que se desea reemplazar algunos caracteres.

Núm_inicial es la posición del carácter dentro de texto_original que se desea reemplazar por texto_nuevo.

Núm_de_caracteres es el número de caracteres de texto_original que se desea que REEMPLAZAR reemplace por texto_nuevo.

Texto_nuevo es el texto que reemplazará los caracteres de texto_original.

Ejemplo REEMPLAZAR

A Datos abcdefghijk 2009 123456 Fórmula Descripción (resultado) =REEMPLAZAR(A2;6;5;"*") Reemplaza cinco caracteres empezando por el sexto (abcde*k) =REEMPLAZAR(A3;3;2;"10") Reemplaza los últimos dos dígitos de 2009 por 10 (2010) =REEMPLAZAR(A4;1;3;"@") Reemplaza los tres primeros caracteres por @ (@456)

LÓGICAS

SI

Devuelve un valor si la condición especificada es VERDADERO y otro valor si dicho argumento es FALSO.

Utilice SI para realizar pruebas condicionales en valores y fórmulas.

Sintaxis

SI(prueba_lógica;valor_si_verdadero;valor_si_falso)

Prueba_lógica es cualquier valor o expresión que pueda evaluarse como VERDADERO o FALSO. Por ejemplo, A10=100 es una expresión lógica; si el valor de la celda A10 es igual a 100, la expresión se evalúa como VERDADERO. De lo contrario, se evaluará como FALSO. Este argumento puede utilizar cualquier operador de comparación.

Valor_si_verdadero es el valor que se devuelve si el argumento prueba_lógica es VERDADERO. Por ejemplo, si este argumento es la cadena de texto "Dentro de presupuesto" y el argumento prueba_lógica se evalúa como VERDADERO, la función SI muestra el texto "Dentro de presupuesto". Si el argumento prueba_lógica es VERDADERO y el argumento valor_si_verdadero está en blanco, este argumento devuelve 0 (cero). Para mostrar la palabra VERDADERO, utilice el valor lógico VERDADERO para este argumento. Valor_si_verdadero puede ser otra fórmula.

48

Valor_si_falso es el valor que se devuelve si el argumento prueba_lógica es FALSO. Por ejemplo, si este argumento es la cadena de texto "Presupuesto excedido" y el argumento prueba_lógica se evalúa como FALSO, la función SI muestra el texto "Presupuesto excedido". Si el argumento prueba_lógica es FALSO y se omite valor_si_falso, (es decir, después de valor_si_verdadero no hay ninguna coma), se devuelve el valor lógico FALSO. Si prueba_lógica es FALSO y valor_si_falso está en blanco (es decir, después de valor_si_verdadero hay una coma seguida por el paréntesis de cierre), se devuelve el valor 0 (cero). Valor_si_falso puede ser otra fórmula.

Observaciones

Cuando los argumentos valor_si_verdadero y valor_si_falso se evalúan, la función SI devuelve el valor devuelto por la ejecución de las instrucciones.

Si uno de los argumentos de la función SI es una matriz, cada elemento de la matriz se evaluará cuando se ejecute la instrucción SI.

Ejemplo 1

A Datos 50 Fórmula Descripción (resultado) =SI(A2<=100;"Dentro de presupuesto";"Presupuesto excedido")

Si el número anterior es igual o menor que 100, la fórmula muestra "Dentro de presupuesto". De lo contrario, la función mostrará "Presupuesto excedido" (Dentro de presupuesto)

=SI(A2=100;SUMAA(B5:B15);"") Si el número anterior es 100, se calcula el rango B5:B15. En caso contrario, se devuelve texto vacío ("") ()

Ejemplo 2

A Puntuación 45 90 78 Fórmula Descripción (resultado) =SI(A2>89,"A",SI(A2>79;"B";SI(A2>69;"C";SI(A2>59;"D";"F")))) Asigna una puntuación de una letra al

primer resultado (F) =SI(A3>89;"A";SI(A3>79;"B";SI(A3>69;"C";SI(A3>59;"D";"F")))) Asigna una puntuación de una letra al

segundo resultado (A) =SI(A4>89;"A";SI(A4>79;"B";SI(A4>69;"C";SI(A4>59;"D";"F")))) Asigna una puntuación de una letra al tercer

resultado (C)

En el ejemplo anterior, la segunda instrucción SI representa también el argumento valor_si_falso de la primera instrucción SI. De manera similar, la tercera instrucción SI es el argumento valor_si_falso de la segunda instrucción SI. Por ejemplo, si el primer argumento prueba_lógica (Promedio>89) es VERDADERO, se devuelve el valor "A". Si el primer argumento prueba_lógica es FALSO, se evalúa la segunda instrucción SI y así sucesivamente.

49

Y

Devuelve VERDADERO si todos los argumentos se evalúan como VERDADERO; devuelve FALSO si uno o más argumentos se evalúan como FALSO.

Un uso común de la función Y es expandir la utilidad de otras funciones que realizan pruebas lógicas. Por ejemplo, la función SI realiza una prueba lógica y, luego, devuelve un valor si la prueba se evalúa como VERDADERO y otro valor si la prueba se evalúa como FALSO. Con la función Y como argumento prueba_lógica de la función SI, puede probar varias condiciones diferentes en lugar de sólo una.

Sintaxis

Y(valor_lógico1; [valor_lógico2]; ...)

La sintaxis de la función Y tiene los siguientes argumentos:

Valor_lógico1, Valor_lógico2, ... son de 1 a 255 condiciones que se desea comprobar y que pueden tener el resultado de VERDADERO o FALSO.

O

Devolverá VERDADERO si alguno de los argumentos es VERDADERO; devolverá FALSO si todos los argumentos son FALSO.

Sintaxis

O(valor_lógico1;valor_lógico2; ...)

Valor_lógico1, Valor_lógico2, ... son de 1 a 255 condiciones que se desea comprobar y que pueden tener el resultado de VERDADERO o FALSO.

Observaciones

Al igual que la función Y se usa comúnmente para expandir la utilidad de otras funciones que realizan pruebas lógicas como la función SI.

50

INFORMACION

ESERROR / ESERR / ESBLANCO / ESLÓGICO / ESNOD / ESNOTEXTO / ESTEXTO / ESNUMERO / ESREF

La sintaxis de las funciones tiene un argumento:

valor Obligatorio. El valor que desea probar. El argumento de valor puede ser una celda vacía (en blanco), un error, un valor lógico, texto, un número o un valor de referencia, o un nombre que haga referencia a alguno de los anteriores.

Función Devuelve VERDADERO si ESBLANCO Valor se refiere a una celda vacía. ESERR Valor se refiere a cualquier valor de error con excepción de #N/A. ESERROR Valor se refiere a uno de los valores de error (#N/A, #¡VALOR!, #¡REF!, #¡DIV/0!, #¡NUM!,

#¿NOMBRE? o #¡NULO!). ESLOGICO Valor se refiere a un valor lógico. ESNOD Valor se refiere al valor de error #N/A (el valor no está disponible). ESNOTEXTO Valor se refiere a cualquier elemento que no sea texto. (Tenga en cuenta que esta función devuelve

VERDADERO si el valor hace referencia a una celda vacía.) ESNUMERO Valor se refiere a un número. ESREF Valor se refiere a una referencia. ESTEXTO Valor se refiere a texto.

Observaciones

Los argumentos de valor de las funciones no se convierten. Cualquier valor numérico que se encuentre entre comillas se trata como texto. Por ejemplo, en la mayoría de las funciones en las que se requiere un número, el valor de texto "19" se convierte en el número 19. Sin embargo, en la fórmula ESNUMERO("19"), "19" no se convierte y ESNUMERO devuelve FALSO.

Las funciones son útiles en fórmulas cuando se desea comprobar el resultado de un cálculo. Combinadas con la función SI, estas funciones proporcionan un método para localizar errores en fórmulas.

CELDA

La función CELDA devuelve información acerca del formato, la ubicación o el contenido de una celda. Por ejemplo, si desea comprobar que una celda contiene un valor numérico en lugar de texto antes de realizar un cálculo en ella, puede usar la siguiente fórmula:

=SI(CELDA("tipo", A1) = "v", A1 * 2, 0)

Esta fórmula calcula A1*2 sólo si la celda A1 contiene un valor numérico y devuelve 0 si A1 contiene texto o está en blanco.

Sintaxis

CELL(info_type, [reference])

La sintaxis de la función CELDA tiene los siguientes argumentos:

51

tipo_de_info Obligatorio. Es un valor de texto que especifica el tipo de información de la celda que se desea obtener. La siguiente lista muestra los posibles valores del argumento de tipo_de_info y los correspondientes resultados.

tipo_de_info Devuelve "ANCHO" El ancho de columna de la celda redondeado a un entero. Cada unidad del ancho de columna es

igual al ancho de un carácter en el tamaño de fuente predeterminado. "ARCHIVO" Nombre del archivo (incluida la ruta de acceso completa) que contiene la referencia, en forma de

texto. Devuelve texto vacío ("") si todavía no se ha guardado la hoja de cálculo que contiene la referencia.

"COLOR" Valor 1 si la celda tiene formato de color para los valores negativos; de lo contrario, devuelve 0 (cero).

"COLUMNA" El número de columna de la celda del argumento ref. "CONTENIDO" Valor de la celda superior izquierda de la referencia, no una fórmula. "DIRECCION" la referencia, en forma de texto, de la primera celda del argumento ref. "FILA" El número de fila de la celda del argumento ref. "FORMATO" Un valor de texto correspondiente al formato numérico de la celda. Los valores de texto para los

distintos formatos se muestran en la ayuda del programa. Si la celda tiene formato de color para los números negativos, devuelve "-" al final del valor de texto. Si la celda está definida para mostrar todos los valores o los valores positivos entre paréntesis, devuelve "()" al final del valor de texto.

"PARENTESIS" Valor 1 si la celda tiene formato con paréntesis para los valores positivos o para todos los valores; de lo contrario, devuelve 0 (cero).

"PREFIJO" Un valor de texto que corresponde al "prefijo de rótulo" de la celda. Devuelve un apóstrofo (') si la celda contiene texto alineado a la izquierda, comillas (") si la celda contiene texto alineado a la derecha, un acento circunflejo (^) si el texto de la celda está centrado, una barra inversa (\) si la celda contiene texto con alineación de relleno y devolverá texto vacío ("") si la celda contiene otro valor.

"PROTEGER" Valor 0 (cero) si la celda no está bloqueada; de lo contrario, devuelve 1 si la celda está bloqueada. "TIPO" Un valor de texto que corresponde al tipo de datos de la celda. Devolverá "b" (para blanco) si la

celda está vacía, "r" (para rótulo) si la celda contiene una constante de texto y "v" (para valor) si la celda contiene otro valor.

referencia Opcional. La celda sobre la que desea información. Si se omite, se devuelve la información especificada en el argumento tipo_de_info para la última celda cambiada. Si el argumento de referencia es un rango de celdas, la función CELDA devuelve la información sólo para la celda superior izquierda del rango.

52

Tablas Dinámicas

TABLAS DINÁMICAS

Las tablas dinámicas son una herramienta relacionada con las listas o bases de datos creadas en hojas de cálculo. Sirven para organizar de manera sencilla y rápida grandes cantidades de datos procedentes de dichas listas. Permiten agrupar y filtrar la información por cualquiera de los campos de la lista, creando una tabla-resumen.

Veremos el procedimiento de creación de una tabla dinámica a partir de un ejemplo:

Disponemos de una hoja de cálculo con las horas trabajadas durante una semana por los programadores de una empresa en unos determinados proyectos, tal como aparece en la siguiente tabla:

Empleado Proyecto Fecha Horas

Rodríguez Contaplus Elite 22/06/2005 9,5

Pérez Contaplus Elite 22/06/2005 3,6

Roig Nómina Plus 23/06/2005 5,5

Pérez Factplus 24/06/2005 8

Rodríguez Nómina Plus 23/06/2005 4

Rodríguez Factplus 26/06/2005 10

Roig Contaplus Elite 25/06/2005 4

Pérez Factplus 26/06/2005 7

Roig Contaplus Elite 25/06/2005 6,5

Queremos crear una tabla dinámica que muestre, para cada programador empleado de la empresa, en páginas individuales, el total de horas trabajadas en cada uno de los proyectos durante la última semana.

Procedimiento:

Sitúate en una de las celdas que contienen los datos (rango A1:D10) y ve a Datos (menú principal), Asistente para tablas dinámicas (o Informe de Tablas o gráficos Dinámicos). Se iniciará el Asistente, que consta de cuatro cuadros de diálogo consecutivos.

En el primer cuadro de diálogo (paso 1 de 4) se solicita el origen de los datos a organizar en forma de tabla dinámica. En este caso, dejamos la opción preseleccionada (Lista o base de datos de Microsoft Excel) y pulsamos Siguiente.

El siguiente cuadro de diálogo (paso 2 de 4) permite seleccionar el rango de celdas en el que están situados los datos a organizar. Si el propio asistente indica el rango correcto ($A$1:$D$10), pulsa Siguiente.

Nota: también es posible realizar tablas dinámicas con datos existentes en otros libros de Excel. En tales casos, pulsaríamos el botón Examinar... y buscaríamos el libro en el disco y la carpeta correspondiente.

53

En el tercer cuadro de diálogo (paso 3 de 4) se diseñará la distribución de los campos en la tabla a crear.

En la parte derecha de la ventana se muestra un botón para cada campo de la lista y en la parte izquierda aparece el área en donde se diseñará la tabla, que está dividida en cuatro secciones (PÁGINA, FILA, COLUMNA y DATOS), en las que se pueden colocar los distintos campos, pulsando sobre el botón del campo y arrastrándolo a una sección.

A la hora de organizar los datos en nuestro ejemplo deberá tenerse en cuenta lo siguiente:

El campo que se coloque en la sección PÁGINA aparecerá en forma de una lista desplegable desde la que se podrá seleccionar aquel elemento del que se desee mostrar el resumen. Existirá además la posibilidad de mostrar el resumen correspondiente a cada empleado en una hoja diferente.

El campo que se coloque en la sección FILA mostrará sus elementos como encabezados o títulos de las filas en la tabla

El campo que se coloque en la sección COLUMNA, mostrará sus elementos como encabezados de las columnas de la tabla

En cuanto al campo que se coloque en la sección DATOS, sus datos se someterán a una determinada operación de cálculo: Suma (es la que se ofrece por defecto cuando los datos de este campo son todos numéricos), Contar (la que se ofrece por defecto en los demás casos), Promedio, Mínimo, Máximo, Producto, etc.

54

En nuestro ejemplo, por tanto, colocaremos los campos del siguiente modo:

El campo Empleado en la sección PÁGINA

El campo Proyecto en la sección FILA

El campo Fecha en la sección COLUMNA

El campo Horas (que contiene los valores que queremos sumar) en la sección DATOS, aceptando la función de SUMAA que Excel propone por defecto.

Una vez diseñada la tabla, pulsa Aceptar.

El último cuadro de diálogo (paso 4 de 4) te ofrece situar la tabla dinámica en una hoja nueva o en la misma hoja de cálculo donde están los datos. Selecciona la segunda opción e indica la posición de destino de la tabla dinámica tecleando (o seleccionando con el ratón) la dirección de la celda superior izquierda a partir de la cual se colocará la tabla.

Para acabar pulsa el botón Terminar y Excel creará la tabla dinámica.

a) La forma elegida aquí para organizar los datos sólo es una de entre todas las posibles. Cabe organizar los datos de otra manera; no obstante, hemos de procurar que la forma elegida sea la más clara y fácil de interpretar.

Notas:

b) Aunque los datos de una tabla dinámica tienen el mismo aspecto que cualquier hoja de cálculo, no se pueden introducir ni editar los datos directamente en ella. Para modificar sus resultados deberán modificarse forzosamente los datos a partir de los cuales se ha creado.

c) No obstante, las tablas dinámicas no se actualizan automáticamente cuando los datos de origen cambian, sino que, cambiados los datos fuente es necesario seleccionar con el botón derecho del ratón una celda cualquiera de la tabla y elegir la opción Actualizar datos del menú contextual correspondiente.

d) Una vez creada la tabla dinámica, se puede cambiar fácilmente su diseño arrastrando los botones sombreados con los nombres de los campos a otras posiciones de la tabla (por esta razón se llaman “dinámicas”, precisamente)

55

Teclas de método abreviado

Teclas de método abreviado combinadas con CTRL

Tecla Descripción

CTRL+MAYÚS+( Muestra las filas ocultas de la selección.

CTRL+MAYÚS+) Muestra las columnas ocultas de la selección.

CTRL+MAYÚS+& Aplica el contorno a las celdas seleccionadas.

CTRL+MAYÚS_ Quita el contorno de las celdas seleccionadas.

CTRL+E Aplica el formato de número General.

CTRL+MAYÚS+$ Aplica el formato Moneda con dos decimales (los números negativos aparecen entre paréntesis).

CTRL+MAYÚS+% Aplica el formato Porcentaje sin decimales.

CTRL+MAYÚS+^ Aplica el formato numérico Exponencial con dos decimales.

CTRL+MAYÚS+# Aplica el formato Fecha con el día, mes y año.

CTRL+MAYÚS+@ Aplica el formato Hora con la hora y los minutos e indica a.m. o p.m.

CTRL+MAYÚS+! Aplica el formato Número con dos decimales, separador de miles y signo menos (-) para los valores negativos.

CTRL+MAYÚS+* Selecciona el área actual alrededor de la celda activa (el área de datos delimitada por filas en blanco y columnas en blanco).

En una tabla dinámica, selecciona todo el informe de tabla dinámica.

CTRL+MAYÚS+: Inserta la hora actual.

CTRL+MAYÚS+" Copia el valor de la celda situada sobre la celda activa en la celda o en la barra de fórmulas.

CTRL+MAYÚS+Signo más( +)

Muestra el cuadro de diálogo Insertar para insertar celdas en blanco.

CTRL+Signo menos (-) Muestra el cuadro de diálogo Eliminar para eliminar las celdas seleccionadas.

CTRL+; Inserta la fecha actual.

ALT+º Cambia entre mostrar valores de celda y mostrar fórmulas de la hoja de cálculo.

CTRL+' Copia en la celda o en la barra de fórmulas una fórmula de la celda situada sobre la celda activa.

CTRL+1 Muestra el cuadro de diálogo Formato de celdas.

CTRL+2 Aplica o quita el formato de negrita.

56

CTRL+3 Aplica o quita el formato de cursiva.

CTRL+4 Aplica o quita el formato de subrayado.

CTRL+5 Aplica o quita el formato de tachado.

CTRL+6 Cambia entre ocultar objetos, mostrarlos o mostrar marcadores de los objetos.

CTRL+8 Muestra u oculta símbolos de esquema.

CTRL+9 Oculta filas seleccionadas.

CTRL+0 Oculta columnas seleccionadas.

CTRL+E Selecciona toda la hoja de cálculo.

Si la hoja de cálculo contiene datos, CTRL+E selecciona la región actual. Si presiona CTRL+E una segunda vez, se selecciona la región actual y sus filas de resumen. Presionando CTRL+E por tercera vez, se selecciona toda la hoja de cálculo.

Cuando el punto de inserción está a la derecha de un nombre de función en una fórmula, muestra el cuadro de diálogo Argumentos de función.

CTRL+MAYÚS+A inserta los paréntesis y nombres de argumento cuando el punto de inserción está a la derecha de un nombre de función en una fórmula.

CTRL+N Aplica o quita el formato de negrita.

CTRL+C Copia las celdas seleccionadas.

CTRL+C seguido de otro CTRL+C muestra el Portapapeles.

CTRL+J Utiliza el comando Rellenar hacia abajo para copiar el contenido y el formato de la celda situada más arriba de un rango seleccionado a las celdas de abajo.

CTRL+B Muestra el cuadro de diálogo BUSCAR y reemplazar con la ficha BUSCAR seleccionada.

MAYÚS+F5 también muestra esta ficha, mientras que MAYÚS+F4 repite la última acción de BUSCAR.

CTRL+MAYÚS+F abre el cuadro de diálogo Formato de celdas con la ficha Fuente seleccionada.

CTRL+I Muestra el cuadro de diálogo Ir a.

F5 también muestra este cuadro de diálogo.

CTRL+L Muestra el cuadro de diálogo BUSCAR y reemplazar con la ficha Reemplazar seleccionada.

CTRL+K Aplica o quita el formato de cursiva.

CTRL+ALT+K Muestra el cuadro de diálogo Insertar hipervínculo para hipervínculos nuevos o el cuadro de diálogo Modificar hipervínculo para hipervínculos existentes seleccionados.

CTRL+U Crea un nuevo libro en blanco.

CTRL+A Muestra el cuadro de diálogo Abrir para abrir o BUSCAR un archivo.

57

CTRL+MAYÚS+O selecciona todas las celdas que contienen comentarios.

CTRL+P Muestra el cuadro de diálogo Imprimir.

CTRL+MAYÚS+F abre el cuadro de diálogo Formato de celdas con la ficha Fuente seleccionada.

CTRL+D Utiliza el comando Rellenar hacia la derecha para copiar el contenido y el formato de la celda situada más a la izquierda de un rango seleccionado a las celdas de la derecha.

CTRL+G Guarda el archivo activo con el nombre de archivo, la ubicación y el formato de archivo actuales.

CTRL+F Muestra el cuadro de diálogo Crear tabla.

CTRL+S Aplica o quita el formato de subrayado.

CTRL+MAYÚS+U cambia entre expandir y contraer de la barra de fórmulas.

CTRL+V Inserta el contenido del Portapapeles en el punto de inserción y reemplaza cualquier selección. Disponible solamente después de haber cortado o copiado un objeto, texto o el contenido de una celda.

CTRL+ALT+V muestra el cuadro de diálogo Pegado especial. Disponible solamente después de haber cortado o copiado un objeto, texto o el contenido de una celda en una hoja de cálculo o en otro programa.

CTRL+R Cierra la ventana del libro seleccionado.

CTRL+X Corta las celdas seleccionadas.

CTRL+Y Repite el último comando o acción, si es posible.

CTRL+Z Utiliza el comando Deshacer para invertir el último comando o eliminar la última entrada que escribió.

CTRL+MAYÚS+Z utiliza los comandos Deshacer o Rehacer para invertir o restaurar la última corrección automática cuando se muestran las etiquetas inteligentes de Autocorrección.

Teclas de función

Tecla Descripción

F1 Muestra el panel de tareas Ayuda de Microsoft Office Excel.

CTRL+F1 muestra u oculta la cinta de opciones, que es un componente de la Interfaz de usuario Microsoft Office Fluent.

ALT+F1 crea un gráfico a partir de los datos del rango actual.

ALT+MAYÚS+F1 inserta una hoja de cálculo nueva.

F2 Modifica la celda activa y coloca el punto de inserción al final del contenido de la celda. También mueve el punto de inserción a la barra de fórmulas cuando la edición en una celda está desactivada.

58

MAYÚS+F2 agrega o modifica un comentario de celda.

CTRL+F2 muestra la ventana Vista preliminar.

F3 Muestra el cuadro de diálogo Pegar nombre.

MAYÚS+F3 muestra el cuadro de diálogo Insertar función.

F4 Repite el último comando o acción, si es posible.

CTRL+F4 cierra la ventana del libro seleccionado.

F5 Muestra el cuadro de diálogo Ir a.

CTRL+F5 restaura el tamaño de ventana de la ventana del libro seleccionado.

F6 Cambia entre la hoja de cálculo, la cinta de opciones, el panel de tareas y los controles de Zoom. En una hoja de cálculo que se ha dividido (menú Ver, Administrar esta ventana, Inmovilizar paneles, comando Dividir ventana), F6 incluye los paneles divididos cuando se alterna entre los paneles y el área de la cinta de opciones.

MAYÚS+F6 cambia entre la hoja de cálculo, los controles de Zoom, el panel de tareas y la cinta de opciones.

CTRL+F6 cambia a la ventana del libro siguiente cuando hay más de una ventana del libro abierta.

F7 Muestra el cuadro de diálogo Ortografía para revisar la ortografía de la hoja de cálculo activa o del rango seleccionado.

CTRL+F7 ejecuta el comando Mover en la ventana del libro cuando no está maximizada. Utilice las teclas de dirección para mover la ventana y, cuando haya acabado, presione ENTRAR o ESC para cancelar.

F8 Activa o desactiva el modo extendido. En el modo extendido aparece Selección extendida en la línea de estado y las teclas de dirección extienden la selección.

MAYÚS+F8 le permite agregar una celda o rango de celdas no adyacentes a una selección de celdas utilizando las teclas de dirección.

CTRL+F8 ejecuta el comando Tamaño (en el menú Control de la ventana del libro) cuando una ventana del libro no está maximizada.

ALT+F8 muestra el cuadro de diálogo Macro para crear, ejecutar, modificar o eliminar una macro.

F9 Calcula todas las hojas de cálculo de todos los libros abiertos.

MAYÚS+F9 calcula la hoja de cálculo activa.

CTRL+ALT+F9 calcula todas las hojas de cálculo de todos los libros abiertos, independientemente de si han cambiado desde el último cálculo.

CTRL+ALT+MAYÚS+F9 vuelve a comprobar fórmulas dependientes y calcula todas las celdas de todos los libros abiertos, incluidas las celdas que no tienen marcado que sea necesario calcularlas.

CTRL+F9 minimiza la ventana del libro hasta convertirla en un icono.

F10 Activa o desactiva la información de los métodos abreviado de teclado.

MAYÚS+F10 muestra el menú contextual de un elemento seleccionado.

ALT+MAYÚS+F10 muestra el menú o mensaje de una etiqueta inteligente. Si hay más de una etiqueta

59

inteligente, cambia a la siguiente y muestra su menú o mensaje.

CTRL+F10 maximiza o restaura la ventana de libro seleccionada.

F11 Crea un gráfico a partir de los datos del rango actual.

MAYÚS+F11 inserta una hoja de cálculo nueva.

ALT+F11 abre el Editor de Microsoft Visual Basic, donde puede crear una macro utilizando Visual Basic para Aplicaciones (VBA).

F12 Muestra el cuadro de diálogo Guardar como.

Otras teclas de método abreviado útiles

Tecla Descripción

TECLAS DE DIRECCIÓN

Mueve el cursor una celda hacia arriba, hacia abajo, hacia la izquierda o hacia la derecha.

CTRL+TECLA DE DIRECCIÓN va hasta el extremo de la región de datos en una hoja de cálculo.

MAYÚS+TECLA DE DIRECCIÓN extiende en una celda la selección de celdas.

CTRL+MAYÚS+TECLA DE DIRECCIÓN extiende la selección de celdas a la última celda no vacía de la misma columna o fila que la celda activa, o si la siguiente celda está en blanco, extiende la selección a la siguiente celda que no esté en blanco.

FLECHA IZQUIERDA o FLECHA DERECHA selecciona la ficha de la izquierda o de la derecha cuando la cinta de opciones está seleccionada. Cuando un submenú está abierto o seleccionado, estas teclas de dirección alternan entre el menú principal y el submenú. Cuando una ficha de la cinta de opciones está seleccionada, estas teclas permiten desplazarse por los botones de ficha.

FLECHA ARRIBA o FLECHA ABAJO selecciona el siguiente comando o el anterior cuando hay abierto un menú o un submenú. Cuando una ficha de la cinta de opciones está seleccionada, estas teclas permiten desplazarse hacia arriba y abajo en el grupo de fichas.

En un cuadro de diálogo, las teclas de dirección permiten desplazarse entre opciones en un cuadro de lista desplegable abierto o entre algunas opciones de un grupo de opciones.

FLECHA ABAJO o ALT+FLECHA ABAJO abren una lista desplegable seleccionada.

RETROCESO Elimina un carácter a la izquierda en la barra de fórmulas.

También borra el contenido de la celda activa.

En el modo de edición de celdas, elimina el carácter situado a la izquierda del punto de inserción.

SUPR Quita el contenido de la celda (datos y fórmulas) de las celdas seleccionadas sin afectar a los formatos de celda ni a los comentarios.

En el modo de edición de celdas, elimina el carácter situado a la derecha del punto de inserción.

FIN Permite desplazarse hasta la celda de la esquina inferior derecha de la ventana cuando la tecla BLOQ DESPL está activada.

También selecciona el último comando del menú cuando un menú o un submenú están visibles.

60

CTRL+FIN permite desplazarse hasta la última celda de una hoja de cálculo, en la última fila utilizada de la última columna utilizada. Si el cursor está en la barra de fórmulas, CTRL+FIN mueve el cursor al final del texto.

CTRL+MAYÚS+FIN extiende la selección de celdas hasta la última celda utilizada de la hoja de cálculo (esquina inferior derecha). Si el cursor está en la barra de fórmulas, CTRL+MAYÚS+FIN selecciona todo el texto de la barra de fórmulas desde la posición del cursor hasta el final; esto no afecta al alto de la barra de fórmulas.

ENTRAR Completa una entrada de celda desde la celda o desde la barra de fórmulas, y selecciona la celda situada debajo (de forma predeterminada).

En un formulario de datos, va al primer campo del registro siguiente.

Abre un menú seleccionado (presione F10 para activar la barra de menús) o realiza la acción de un comando seleccionado.

En un cuadro de diálogo, ejecuta la acción asignada al botón de comando predeterminado del cuadro de diálogo (el botón en negrita que suele ser el botón Aceptar).

ALT+ENTRAR comienza una nueva línea en la misma celda.

CTRL+ENTRAR rellena el rango de celdas seleccionado con la entrada actual.

MAYÚS+ENTRAR completa una entrada de celda y selecciona la celda situada por encima.

ESC Cancela una entrada en la celda o en la barra de fórmulas.

Cierra un menú o submenú, un cuadro de diálogo o una ventana de mensaje abiertos.

También cierra modo de pantalla completa cuando se ha aplicado este modo y vuelve a modo de pantalla normal para mostrar de nuevo la cinta de opciones y la barra de estado.

INICIO Va al principio de una fila de una hoja de cálculo.

Permite desplazarse hasta la celda de la esquina superior derecha de la ventana cuando la tecla BLOQ DESPL está activada.

Selecciona el primer comando del menú cuando un menú o un submenú están visibles.

CTRL+INICIO va al inicio de una hoja de cálculo.

CTRL+MAYÚS+INICIO amplia la selección de celdas hasta el comienzo de la hoja de cálculo.

AV PÁG Baja una pantalla en una hoja de cálculo.

ALT+AV PÁG desplaza una pantalla a la derecha en una hoja de cálculo.

CTRL+AV PÁG va a la hoja siguiente de un libro.

CTRL+MAYÚS+AV PÁG selecciona la hoja actual y la siguiente de un libro.

RE PÁG Sube una pantalla en una hoja de cálculo.

ALT+RE PÁG desplaza una pantalla a la izquierda en una hoja de cálculo.

CTRL+RE PÁG va a la hoja anterior de un libro.

CTRL+MAYÚS+RE PÁG selecciona la hoja actual y la anterior de un libro.

61

BARRA ESPACIADORA

En un cuadro de diálogo, ejecuta la acción del botón seleccionado, o activa o desactiva una casilla de verificación.

CTRL+BARRA ESPACIADORA selecciona una columna completa de una hoja de cálculo.

MAYÚS+BARRA ESPACIADORA selecciona una fila completa de una hoja de cálculo.

CTRL+MAYÚS+BARRA ESPACIADORA selecciona toda la hoja de cálculo.

Si la hoja de cálculo contiene datos, CTRL+MAYÚS+BARRA ESPACIADORA selecciona la región actual. Si presiona CTRL+MAYÚS+BARRA ESPACIADORA una segunda vez se selecciona la región actual y sus filas de resumen. Presionando CTRL+MAYÚS+BARRA ESPACIADORA por tercera vez, se selecciona toda la hoja de cálculo.

Cuando hay un objeto seleccionado, CTRL+MAYÚS+BARRA ESPACIADORA selecciona todos los objetos de una hoja de cálculo.

ALT+BARRA ESPACIADORA muestra el menú Control de la ventana de Microsoft Office Excel.

TAB Mueve una celda a la derecha en una hoja de cálculo.

Permite desplazarse entre celdas desbloqueadas en una hoja de cálculo protegida.

Mueve a la opción o grupo de opciones siguientes de un cuadro de diálogo.

MAYÚS+TAB permite moverse a la celda anterior de una hoja de cálculo o a la opción anterior de un cuadro de diálogo.

CTRL+TAB pasa a la ficha siguiente en un cuadro de diálogo.

CTRL+MAYÚS+TAB pasa a la ficha anterior en un cuadro de diálogo.

62

63

64

65