Manual de excel 2010 intermedio

Manual

Microsoft Excel 2010 - Intermedio

Tabla de Contenido

REPASO DE FORMULAS Y FUNCIONES............................................................................................ 3

ANIDAMIENTO DE FUNCIONES .................................................................................................................... 17 CASO PRÁCTICO ......................................................................................................................................... 17

FÓRMULAS AVANZADAS ................................................................................................................... 19

FUNCIONES MATEMÁTICAS Y TRIGONOMÉTRICAS ...................................................................................... 19 FUNCIONES LÓGICAS .................................................................................................................................. 20 FUNCIONES DE FECHA Y HORA ................................................................................................................... 20 FUNCIONES ESTADÍSTICAS ......................................................................................................................... 23 FUNCIONES DE TEXTO ................................................................................................................................. 24 FUNCIONES DE BÚSQUEDA ......................................................................................................................... 27

BASE DE DATOS .................................................................................................................................. 31

FORMULARIOS ............................................................................................................................................. 31 ORDENAR UNA TABLA DE DATOS ............................................................................................................... 36 AUTOFILTRO ................................................................................................................................................ 37 FILTROS AVANZADOS .................................................................................................................................. 39 SUBTOTALES ............................................................................................................................................... 39

TABLA DINÁMICA ................................................................................................................................ 43

CREAR PARTIENDO DE DATOS INTERNOS ................................................................................................. 43 ASISTENTE PARA TABLAS DINÁMICAS ........................................................................................................ 44 DISEÑO, AGREGAR CAMPOS ....................................................................................................................... 45 MODIFICAR UNA TABLA DINÁMICA ............................................................................................................. 46 GRÁFICOS CON TABLAS DINÁMICAS ........................................................................................................... 48 ELIMINAR UNA TABLA DINÁMICA. ................................................................................................................ 49

DATOS EXTERNOS Y AUTOMATIZACIÓN ....................................................................................... 52

IMPORTAR DATOS ........................................................................................................................................ 52 CONSULTA DE UNA BASE DE DATOS .............................................................. ¡ERROR! MARCADOR NO DEFINIDO. CONSULTA WEB .......................................................................................................................................... 54 VALIDACIÓN DE DATOS ............................................................................................................................... 55 PROTEGER HOJA ......................................................................................................................................... 58 PLANTILLAS Y FORMULARIOS ..................................................................................................................... 60 COMO GUARDAR UNA PLANTILLA ............................................................................................................... 61 VINCULO O ENLACES. HIPERVÍNCULOS ...................................................................................................... 61

Capítulo 1

Fórmulas y Funciones Básicas (Repaso)

En esta sesión trataremos:

Repaso de Formulas y funciones

Anidamiento de funciones

Casos prácticos

Microsoft Office Excel 2010 – Nivel Intermedio

Ing. Miguel A. Chalco Flores 3

Repaso de Formulas y funciones

Características Generales

Entre las principales características del nuevo Microsoft Office Excel 2010 se encuentran:

1. Una nueva Interfaz de Usuario, que contiene una Cinta de opciones, que reemplaza a la barra de Menú de las versiones anteriores, y está formada por fichas y grupos donde se encuentran los comandos mas frecuentemente utilizados según la acción que se desea realizar.

2. Mas Filas y Columnas y otros límites nuevos

Cada hoja de cálculo tiene ahora 1’048,576 filas y 16,384 columnas (XFD). Un número ilimitado de tipos de formatos y de referencia de celdas. Cálculos más rápidos en hojas grandes que tengan muchas fórmulas.

3. Fácil escritura de Fórmulas, usando la función autocompletar, las referencias a rangos con nombres, y más fácil acceso a las funciones

Tipos de datos

En cada una de las celdas de la hoja, es posible introducir textos, números, fechas, horas o fórmulas. En todos los casos, los pasos a seguir serán:

VALORES CONSTANTES

1. TEXTO

Digitar el texto deseado, presionamos Enter o flechas de dirección para ingresar el dato en la celda. La barra de fórmulas mostrará el contenido. Si el texto ingresado se ve mutilado, esto se debe a que la celda es muy angosta y se tendrá que aumentar el ancho de la columna para visualizar el dato completo.

2. NÚMEROS



Digitar los valores tal como deban mostrarse, presionamos Enter o flechas de dirección para ingresar el valor. No digitar los separadores de miles (coma o punto). La barra de fórmulas mostrará el número ingresado. Para valores decimales, utilice el punto del teclado numérico. Para que un número se considere texto, utilice un apóstrofe como primer carácter.

2.1. FECHAS Y HORAS

Las fechas y horas son tratados como los números por lo tanto se podrán sumar, restar o añadir a otras. Cualquier fecha y hora se puede introducir directamente en la celda. Por ejemplo: 10/07/1989, 21-2-2008 o 18 Enero 1991 será considerado como fecha y 10:34 o 4:15 PM será considerado como horas. La fecha de inicio en el Microsoft Office Excel 2010 es el 01/01/1900, a partir de esta fecha comienza a contar el Excel.

Fórmula

Una fórmula es una operación de cálculo, referencia, deducción o interpretación de las celdas de una hoja. El verdadero potencial de una hoja de cálculo es el manejo de las fórmulas. Aunque muchas de las operaciones que se hacen con las fórmulas se pueden hacer con una calculadora típica, en una hoja de cálculo se tiene la ventaja de que se dispone de todos los datos al mismo tiempo y que la operación se indica una sola vez, y después se indica que lo realice sobre los datos deseados.

Creación de una fórmula

Procedimiento General para crear una fórmula

1. Siempre para comenzar una fórmula se debe hacer con el signo igual (=), que lo que viene a continuación es el contenido de la fórmula.

Esta ventana nos mostrará el resultado de la fórmula.

2. Después, se debe escribir el contenido de la fórmula, es decir, sus referencias y operaciones. Se puede escribir directamente la celda, pero con la ayuda del Mouse se le puede dar un clic a la celda deseada. La celda escogida tendrá una línea punteada, si hubo un error y esa no era la celda, tan sólo dé un clic en la celda deseada.



3. Después de que la celda ya está escrita, se escribe el operador, que en este caso es *, y se repite el paso 2 para la siguiente celda.

4. La fórmula ya está lista, sólo falta aceptarla. Se puede aceptar con la tecla

<Enter>, con el botón de la barra de fórmulas.

En la celda aparece el resultado de la operación que se indica en la fórmula. Sin embargo, sabemos que es una fórmula porque en la barra de fórmulas sigue apareciendo la fórmula tal y como se escribió.

Esa fórmula que se escribió en el renglón 2, también puede servir para los siguientes renglones sin necesidad de volverla a escribir en cada renglón. Tan sólo tiene que copiarse hacia abajo con cualquiera de los métodos conocidos pero el más rápido y sencillo es con el apuntador del Mouse, y copiando rápidamente el contenido hacia abajo.

Al copiar una fórmula automáticamente van a cambiar los renglones si se copia hacia arriba o hacia abajo y sus columnas, si se copia hacia la izquierda o derecha.



Por lo que en cada renglón o columna se tendría la fórmula correspondiente y sería equivalente a escribir la fórmula celda por celda, sólo que más rápido y más sencillo.

De esta forma, se realizó la operación deseada rápidamente y además se tienen otras ventajas. La primera es que si se hacen cambios en las celdas de PRECIO UNITARIO o CANTIDAD, automáticamente la operación de la fórmula se actualiza.

La otra ventaja, es que las celdas que contienen fórmulas pueden ser usadas como referencias para más operaciones en fórmulas de otras celdas. Por ejemplo, si agregáramos una columna más de DESCUENTO, tendríamos lo siguiente:

Se pueden hacer cuantas combinaciones se deseen simplemente introduciendo la fórmula adecuada.

Autocorrección de fórmulas

En ocasiones escribimos fórmulas con errores. Microsoft Office Excel 2010, cuenta con una herramienta que ayuda a corregir los errores más comunes al escribir una fórmula. Por ejemplo, si escribimos la siguiente fórmula =E3+E3*F3+,



la Autocorrección de fórmulas envía el siguiente mensaje, el cual podemos o no aceptar según sea el caso la mayoría de veces se acepta.

Realizar operaciones aritméticas tomando en cuenta las prioridades de los operadores.

Prioridades de los operadores

Cuando se realizan varias operaciones, el orden en el que serán realizadas es:

1) Las Expresiones en paréntesis 2) Las Potencias 3) Multiplicaciones y Divisiones 4) Sumas y restas

Según el orden, las operaciones se van a agrupar con los paréntesis para obtener el resultado deseado.

Operadores

Los Operadores son utilizados en la creación de fórmulas. Existen cuatro tipos de operadores:

Aritméticos:

Este tipo de operadores sirven para realizar operaciones matemáticas elementales, como son:



Operador Significado Ejemplo Resultado

+ Suma =10+9 19

- Resta y negación =10-2 = -7 8 -7

* Multiplicación =7*3 21

/ División =30/3 10

% Porcentaje =19% 0.19

^ Exponente =2^8 256

Por ejemplo, la fórmula =5^2*10% dará como resultado 2.5 (indica que el número 5 se eleva al cuadrado y el resultado se multiplica por 0.10).

De texto:

Los operadores de este tipo sirven para unir cadenas de texto, creando un solo valor de texto.


& Une texto de celdas distintas A20=

=D20&” “&E20 Excel Intermedio

De Comparación:

Este tipo de operadores sirven para comparar dos valores, devolviendo un resultado de tipo lógico, como VERDADERO si se cumple la comparación y FALSO si no se cumple la comparación). Por ejemplo, si A1 contiene el valor 7 y B1 el valor 5, entonces:


= Igual a =A1=B1 Falso

> Mayor que =A1>B1 Verdadero

< Menor que =A1<B1 Falso

>= Mayor o igual que =A1>=B1 Verdadero

<= Menor o igual que =A1<=B1 Falso

<> Distinto de =A1<>B1 Verdadero

De referencia:

No provocan cambio en los resultados de la celda, sino que controlan la forma en que se agrupan las celdas para calcular una fórmula.

Operador Significado

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

, Coma Operador de unión que combina varias referencias en una sola (SUMA(B5:B15,D5:D15))

espacio Operador de intersección que genera una referencia a celdas comunes a las dos referencias (B7:D7 C6:C8)



Cuando se tienen varios operadores en una fórmula, Excel 2010 sigue las siguientes reglas fijas: Los paréntesis, se realizan primero. Los operadores de igual prioridad se evalúan se izquierda a derecha.

Modificar datos

Se puede modificar el contenido de una celda mientras se está escribiendo o después de ingresado. En este segundo caso, siga los siguientes pasos: 1. Seleccionar la celda que contiene el dato 2. Presionar la tecla F2 o haga clic directamente en la barra de fórmulas 3. Modifique la información.

4. Presionar Enter o haga clic sobre el Botón Introducir. Si se desea reemplazar el contenido de una celda por otro distinto, se selecciona la celda y se escribe el nuevo valor directamente sobre ésta.

Borrar datos

La operación de borrado, permite eliminar el contenido de las celdas, bien por no ser ya necesarios, o para efectos de corregir su contenido. 1. Ubicar puntero en la celda que desea borrar 2. Pulsar la tecla Supr o Botón derecho del mouse Borrar

contenido Si adicionalmente desea eliminar el contenido y/o formato de las celdas, debe utilizar el botón Borrar:

Deshacer una Orden

A veces cometemos un error activando una orden o comando equivocado y nos quedamos sin saber qué hacer. Para solucionar este problema, inmediatamente después de haber cometido el error, realice una de las siguientes órdenes: Presione el botón: Deshacer

Presione las teclas : CTRL + Z

Direccionamiento de celdas

Celda Relativa

Por ejemplo, en la siguiente tabla se desea calcular el cambio de Soles a dólares:

Operador Resultado

=100+16/2 108

=(20+4)/2 12



La operación es muy sencilla, una simple división. Pero ¿qué sucede cuando esa fórmula se copia hacia abajo?

Celda Absoluta

La fórmula que está en la celda D6 está correcta, lo único que debemos hacer es indicarle que no cambie su columna o renglón al momento de copiarse y la forma de hacerlo es anteponiendo el signo $ antes de la referencia de la celda.

Celda Copiando /

Copiando /

C2 B2 / D2 C1 / C3 $C$2 $C$2 / $C$2 $C$2 / $C$2 C$2 B$2 / D$2 C$2 / C$2 $C2 $C2 / $C2 $C1 / $C3

La misma fórmula se puede editar con la tecla <F2> y posteriormente con la tecla <F4> Microsoft Office Excel 2010 automáticamente proporciona las combinaciones de símbolos $ según se desee. Se puede seguir presionando la tecla <F4> hasta obtener la combinación deseada.

Si utilizamos la primera combinación, tendríamos lo siguiente:



de tal forma que el valor del dólar mostrado en la celda C3, no debe de variar. Ahora, vamos a incluir otros datos:

Copiando la fórmula que tenemos en D6, no va a cambiar ni la columna ni el renglón. Se puede hacer otra fórmula más, pero se puede aprovechar la que ya se tiene, simplemente con hacerle un cambio. La fórmula de D6 es =C6/$C$3 que quiere decir que no va a cambiar la columna ni la fila, pero como lo que nos interesa que se mantenga constante es la fila, podemos cambiar la fórmula a =C6/C$3.

Si copiamos esta fórmula hacia abajo y hacia la derecha, los cálculos son correctos y tan sólo utilizamos una fórmula:



Y podríamos seguir añadiendo datos y se va a seguir utilizando la misma fórmula:

Podemos tener referencias absolutas cuando no va a cambiar ni la columna ni la fila (como $C$6), referencias relativas cuando va a cambiar la columna o la fila (como C6 o C6) y referencias mixtas cuando se combinan relativas con absolutas (como $C6*C$7).

Las referencias absolutas, relativas y mixtas ($), se pueden utilizar con cualquier tipo de fórmulas. Esto nos puede ahorrar mucho trabajo y tiempo al momento de realizar operaciones con los datos.

Nombres de Rangos

Como hemos revisado el empleo de celdas absolutas nos ayudan a realizar cálculos de manera más rápida, pero se puede dar nombre a una celda o a un grupo de celdas para poder hacer el trabajo más fácil, puesto que de esta manera es más fácil identificarlas, a veces el empleo de las celdas relativas nos puede marear. Para dar nombre a una o más celdas se usa el siguiente procedimiento:

Seleccione la celda, el rango de celdas o las selecciones no adyacentes a las que desea dar nombre.

Haga clic en el cuadro de nombres situado en el extremo izquierdo de la barra de fórmulas.

Escriba un nombre para la(s) celda(s).

Presionar la tecla <Enter> De esta manera se ha definido un nombre de celda, dicho nombre se puede utilizar en el desarrollo de los cálculos y de esa manera es más fácil y ya se vuelve una referencia absoluta sin necesidad de emplear el símbolo del dólar ($).

Nota: Puede también dar nombre a las celdas con la combinación de teclas <CTRL> + <F3>. De igual forma se puede eliminar un nombre mal definido con la misma combinación de celdas.



Funciones básicas de Microsoft Office Excel 2010

Las funciones incorporadas en Microsoft Excel 2010 realizan cálculos estándar de hoja de cálculo, el empleo de las funciones nos ayudan a llegar a resultados en forma más rápida. Cabe señalar que un usuario en Excel 2010, puede resolver problemas en Excel 2077 sin el empleo de funciones, pero el uso de las mismas acelerara el desarrollo de estos problemas.

ESQUEMA DE UNA FUNCIÓN

Una Función debe tener la siguiente sintaxis:

=<Nombre_Función> (<Argumento1>;<Argumento2>;…<ArgumentosN>)

El número y tipo de argumentos requeridos varía de una función a otra, los cuales pueden ser:

Números

Texto

Valores lógicos

Referencias

Matrices Al utilizar referencias a otras celdas, puede especificar directamente la dirección de una celda o incluso un rango de celdas, utilizando para ello el símbolo de dos puntos (:) para separar la celda inicial y final.

Convenciones en el uso de funciones Para un mejor empleo de las funciones se tendrá que seguir estos convenios

Toda función se compone de una sintaxis que hay que respetar Esta sintaxis comprende un nombre y los argumentos Estos argumentos van entre paréntesis Todo paréntesis que se abre se cierra Los argumentos se separan mediante el separador de listas definido en el

panel de control (;). No dejar espacios en blanco al escribir una función. Una función puede ser parte de otra Toda función si va al inicio de la formula empieza con el signo igual (=). Los textos van entre comillas.

Este es un ejemplo de función

=PROMEDIO(E5:H5)

Este ejemplo de función devuelve EL PROMEDIO de las celdas desde E5 hasta H5.

Además de la función PROMEDIO, existen muchas mas funciones de diferentes categorías y dependiendo de las necesidades específicas del usuario podrá seleccionar funciones de tipo:

Financieras Fecha y hora Matemáticas y Trigonométricas Estadísticas Búsqueda y Referencia



Bases de Datos Texto Lógicas Información Ingeniería Cubo Compatibilidad

Con el empleo de un razonamiento apropiado y la lógica necesaria, se pueden desarrollar muchos problemas con el uso de las funciones.

Ejemplos de combinaciones de funciones válidas:

=SUMA(C1:C5)*5 =SUMA(C6;D6;C7;D7:D9)*SUMA(C6:C7,C9:C10) =SUMA(MAX(A1:C5),MIN(A1:C5))

Normalmente se recomienda que una función se desarrolle en forma manual, es decir, escribiendo el nombre y los argumentos, pero existe en el Excel un asistente para funciones que nos hace el trabajo un poco más sencillo, para acceder al mismo podemos hacerlo de varias formas, mediante la barra de menú en la opción insertar/Función o haciendo clic en el Icono Autosuma.

Aparecerá una ventana como esta:

Con esta ventana se puede desarrollar cualquier función, primero ubicándola en la categoría correspondiente y luego ingresando los argumentos de forma más sencilla.



Funciones Básicas

Funciones Matemáticas

FUNCIÓN SUMA Calcula la suma de los valores especificados. Sintaxis: =SUMA(número1, número2,….) número1, número2… son de 1 a 255 argumentos cuyo suma desea obtener

FUNCIÓN ENTERO Redondea el numero hasta el entero menor mas proximo. Sintaxis: =ENTERO(valor) Valor es el número o una expresión numérica cualquiera.

FUNCIÓN REDONDEAR Redondea un número a una cantidad determinada de decimales. Sintaxis: =REDONDEAR(numero;decimales) Numero y decimales son números o expresiones numéricas cualesquiera FUNCIÓN POTENCIA Eleva un número a una potencia especificada. Sintaxis: =POTENCIA(numero;potencia) numero :es el número base. Puede ser cualquier número real. potencia : es el exponente al que se desea elevar el número base. FUNCIÓN RCUAD Calcula la raiz cuadrada del número especificado Sintaxis: =RCUAD(numero) numero : Puede ser cualquier número real positivo. FUNCIÓN PRODUCTO Calcula la multiplicacion de los valores especificados. Sintaxis: =PRODUCTO(número1, número2,….) número1, número2… son de 1 a 255 argumentos cuyo suma desea obtener

Funciones Estadísticas

FUNCIÓN PROMEDIO Permite calcular el promedio de los valores especificados Sintaxis: =PROMEDIO(número1, número2,….) número1, número2… pueden ser valores o expresiones numéricas o referencias a celdas o rangos numéricos; son de 1 a 255 argumentos numéricos cuyo promedio desea obtener.

FUNCIÓN CONTAR



Cuenta la cantidad de valores numéricos que hay en un rango especificado. Sintaxis: =CONTAR(rango1, rango2,….) rango1, rango2… son rangos de una o mas celdas. La función admite hasta 255 argumentos y no cuenta las celdas vacías o que contengan textos.

FUNCIÓN MAX Devuelve el valor máximo de un conjunto de valores. Sintaxis: =MAX(numero1,numero2,…) número1, número2… son de 1 a 255 argumentos donde se desea encontrar el valor máximo.

FUNCIÓN MIN Devuelve el mínimo valor de un conjunto de valores. Sintaxis: =MIN(numero1,numero2,…) número1, número2… son de 1 a 255 argumentos donde se desea encontrar el valor mínimo.

Funciones de Busqueda y referencia

FUNCIÓN BUSCARV Busca un valor específico en la primer columna de una matriz de tabla y devuelve, en la misma fila, un valor de otra columna de dicha matriz de tabla Sintaxis: =BUSCARV(valor_buscado;Tabla;# de columna a tomar;[ordenado]) Valor_buscado Valor que se va a buscar en la primera columna de la

tabla Tabla Dos o más columnas de datos. Use una referencia a un

rango o un nombre de rango. Los valores de la primera columna de la tabla son los valores que busca valor_buscado. Estos valores pueden ser texto, números o valores lógicos. Las mayúsculas y minúsculas del texto son equivalentes

# de columna a tomar Número de columna de la tabla desde la cual debe devolverse el valor coincidente

Funciones logicas

FUNCIÓN 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_logica;valor_verdadero;valor_falso) Prueba_lógica es cualquier valor o expresión que pueda evaluarse como

VERDADERO o FALSO Valor_verdadero es el valor que se devuelve si el argumento prueba_lógica es

VERDADERO



Valor_falso es el valor que se devuelve si el argumento prueba_lógica es FALSO

Anidamiento de Funciones

En ciertos casos, puede que necesite usar una función determinada dentro de otra,

como parte de uno de los argumentos (recuerde que el argumento es el valor o

conjunto de valores que utiliza una función para llevar a cabo operaciones o cálculos)

de otra función.

En el siguiente ejemplo, la fórmula utiliza una función anidada PROMEDIO y compara

el resultado con el valor 50.

Caso Práctico

Una función lógica puede ser parte del argumento de otra, para entender el concepto

veamos un ejemplo de asignación de códigos a ciertos valores, con las siguientes

condiciones:

Excel también permite que una función se convierta en argumento de otra función, de

esta forma podemos realizar operaciones realmente complejas en una simple celda.

Capítulo 2

Fórmulas Avanzadas


Funciones matemáticas y trigonométricas.

Funciones lógicas.

Funciones de fecha y hora.

Funciones estadísticas.

Funciones de texto.

Funciones de búsqueda.



Fórmulas Avanzadas

Funciones Matemáticas y trigonométricas

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

ALEATORIO.ENTRE

Devuelve un número entero aleatorio entre los números que especifique. Devuelve un nuevo número entero aleatorio cada vez que se calcula la hoja de cálculo.

Sintaxis

=ALEATORIO.ENTRE(inferior, superior)

Inferior es el menor número entero que la función ALEATORIO.ENTRE puede devolver.

Superior es el mayor número entero que la función ALEATORIO.ENTRE puede devolver.

COCIENTE

Devuelve la parte entera de una división. Use esta función cuando desee descartar el residuo de una división.

Sintaxis

=COCIENTE(numerador, denominador)

Numerador es el dividendo.

Denominador es el divisor.

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.

RESTO

Proporciona el residuo después de dividir un número por un divisor.

Sintaxis

=RESTO(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.



SUMAR.SI

Suma las celdas en el rango que coinciden con el argumento criterio.

Sintaxis

=SUMAR.SI(rango;criterio;rango_suma)

rango es 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.

criterio es 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" ">32" o "manzanas".

rango_suma son las celdas reales que se deben sumar si las correspondientes celdas incluidas en el rango cumplen los criterios. Si se omite el argumento rango_suma, las celdas del rango se evaluarán según los criterios y se sumarán si cumplen los criterios.

TRUNCAR

Convierte un número decimal a uno entero al quitar la parte decimal o de fracción.

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 truncamiento. El valor predeterminado del argumento núm_decimales es 0.

Funciones lógica

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.

Y

Devuelve VERDADERO si todos los argumentos son VERDADERO; devuelve FALSO si uno o más argumentos son FALSO.

Sintaxis

=Y(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.

Funciones de fecha y hora

HOY



Devuelve el número de serie de la fecha actual. El número de serie es el código de fecha-hora que Microsoft Excel usa para los cálculos de fecha y hora. Si el formato de celda era General antes de escribir la función, el resultado tendrá formato de fecha.

Sintaxis

=HOY( )

AHORA

Devuelve el número de serie de la fecha y hora actuales. Si el formato de celda era General antes de escribir la función, el resultado tendrá formato de fecha.

Sintaxis

=AHORA( )

HORA

Devuelve la hora de un valor de hora. La hora se expresa como número entero, comprendido entre 0 (12:00 a.m.) y 23 (11:00 p.m.).

Sintaxis

=HORA(núm_de_serie)

Núm_de_serie es el valor de hora que contiene la hora que se desea obtener.

MINUTO

Devuelve los minutos de un valor de hora. Los minutos se expresan como números enteros comprendidos entre 0 y 59.

Sintaxis

=MINUTO(núm_de_serie)

Núm_de_serie es la hora que contiene el valor de minutos que se desea buscar.

SEGUNDO

Devuelve los segundos de un valor de hora. El segundo se expresa como número entero comprendido entre 0 (cero) y 59.

Sintaxis

=SEGUNDO(núm_de_serie)

Núm_de_serie es la hora que contiene los segundos que se desea buscar.

AÑO

Devuelve el año correspondiente a una fecha. El año se devuelve como número entero comprendido entre 1900 y 9999.

Sintaxis

=AÑO(núm_de_serie)

Núm_de_serie es la fecha del año que se desea buscar.

MES

Devuelve el mes de una fecha representada por un número de serie. El mes se expresa como número entero comprendido entre 1 (enero) y 12 (diciembre).



Sintaxis

=MES(núm_de_serie)

Núm_de_serie es la fecha del mes que intenta buscar.

DIA

Devuelve el día de una fecha, representada por un número de serie. El día se expresa como un número entero comprendido entre 1 y 31.

Sintaxis

=DIA(núm_de_serie)

Núm_de_serie es la fecha del día que intenta buscar.

DIASEM

Devuelve el día de la semana correspondiente al argumento núm_de_serie. El día se devuelve como un número entero entre 1 (domingo) y 7 (sábado).

Sintaxis

=DIASEM(núm_de_serie;tipo)

Núm_de_serie es un número secuencial que representa la fecha del día que intenta buscar.

TIPO es un número que determina el tipo de valor que debe devolverse

Tipo Número devuelto

1 u omitido Números del 1 (domingo) al 7 (sábado). Igual a en versiones anteriores de Microsoft Excel.

2 Números del 1 (lunes) al 7 (domingo).

3 Números del 0 (lunes) al 6 (domingo).

DIA.LAB

Devuelve un número que representa una fecha que es el número de días laborables antes o después de una fecha (la fecha inicial). Los días laborables excluyen los días de fin de semana y cualquier fecha identificada en el argumento festivos. Use DIA.LAB para excluir fines de semana o días festivos cuando calcule fechas de vencimiento de facturas, las fechas de entrega esperadas o el número de días de trabajo realizado.

Sintaxis

=DIA.LAB(fecha_inicial, días_lab, festivos)

Fecha_inicial es una fecha que representa la fecha inicial.

Días_lab es el número de días laborables (días que no sean fines de semana ni días festivos) anteriores o posteriores al argumento fecha_inicial. Un valor positivo para el argumento días_lab produce una fecha futura; un número negativo produce una fecha pasada.

Festivos es una lista opcional de una o varias fechas que deben excluirse del calendario laboral, como los días festivos nacionales y locales. La lista puede ser un rango de celdas que contengan las fechas.

DIAS.LAB



Devuelve el número de días laborables entre fecha_inicial y fecha_final. Los días laborables no incluyen los fines de semana ni otras fechas que se identifiquen en el argumento festivos. Utilice DIAS.LAB para calcular el incremento de los beneficios acumulados de los empleados basándose en el número de días trabajados durante un período específico.

Sintaxis

=DIAS.LAB(fecha_inicial, fecha_final, festivos)

Fecha_inicial es una fecha que representa la fecha inicial.

Fecha_final es una fecha que representa la fecha final.

Festivos es un rango opcional de una o varias fechas que deben excluirse del calendario laboral, como los días festivos nacionales y locales. La lista puede ser un rango de celdas que contengan las fechas.

Funciones Estadísticas

CONTAR.BLANCO

Cuenta el número de celdas en blanco dentro de un rango.

Sintaxis

=CONTAR.BLANCO(rango)

Rango es el rango dentro del cual desea contar el número de celdas en blanco.

Observación

También se cuentan las celdas que incluyen fórmulas que devuelven "" (texto vacío). Las celdas que contienen valores cero no se cuentan.

CONTAR.SI

Cuenta las celdas, dentro del rango, que no están en blanco y que cumplen los criterios especificados.

Sintaxis

=CONTAR.SI(rango;criterio)

Rango es la celda o las celdas que se van a contar; deben contener números, o nombres, matrices o referencias que contengan números. Los valores en blanco y los de texto no se tienen en cuenta.

Criterio es el criterio en forma de número, expresión, referencia a celda o texto, que determina las celdas que se van a contar. Por ejemplo, los criterios pueden expresarse como 32, "32", ">32", "manzanas" o B4.

CONTARA

Cuenta el número de celdas que no están vacías y los valores que hay en la lista de argumentos. Use CONTARA para contar el número de celdas que contienen datos en un rango o matriz.

Sintaxis

=CONTARA(valor1;valor2;...)

Valor1, valor2... son de 1 a 255 argumentos que representan los valores que se desea contar.

K.ESIMO.MAYOR



Devuelve el k-ésimo mayor valor 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(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.

K.ESIMO.MENOR

Devuelve el k-ésimo menor valor de un conjunto de datos. Utilice esta función para devolver valores con una posición relativa específica dentro de un conjunto de datos.

Sintaxis

=K.ESIMO.MENOR(matriz;k)

Matriz es una matriz o un rango de datos numéricos cuyo k-ésimo menor valor desea determinar.

K es la posición, dentro de la matriz o del rango de datos, de los datos que se van a devolver, determinada a partir del menor de los valores.

PROMEDIO.SI

Devuelve el promedio (media aritmética) de todas las celdas de un rango que cumplen unos criterios determinados.

Sintaxis

=PROMEDIO.SI(rango;criterio;rango_promedio)

Rango es la celda o las celdas cuyo promedio se desea obtener; deben contener números, o nombres, matrices o referencias que contengan números.

Criterio es el criterio en forma de número, expresión, referencia de celda o texto, que determina las celdas cuyo promedio se va a obtener. Por ejemplo, los criterios pueden expresarse como 32, "32", ">32", "manzanas" o B4.

Rango_promedio es el conjunto real de celdas de las que se va a obtener el promedio. Si se omite, se utiliza el rango.

Funciones de texto

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.

DERECHA



Devuelve el último carácter o caracteres de una cadena de texto, según el número de caracteres que el usuario especifica de derecha a izquierda.

Sintaxis

=DERECHA(texto;núm_de_caracteres)

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

Núm_de_caracteres especifica el número de caracteres que desea extraer.

IZQUIERDA

Devuelve el primer carácter o caracteres de una cadena de texto, según el número de caracteres que especifique el usuario de izquierda a derecha.

Sintaxis

=IZQUIERDA(texto;núm_de_caracteres)



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, de izquierda a derecha.

Sintaxis

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


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.


ENCONTRAR

Busca en un texto un carácter y devuelven el número de la posición del carácter dentro del texto contando a partir del primer carácter del texto (de izquierda a derecha), se le indica la posición inicial a partir de la cual debe contar en caso de omitir es 1.

Sintaxis

=ENCONTRAR(texto_buscado;dentro_del_texto;núm_inicial)

Texto_buscado es el texto que desea encontrar.

Dentro_del_texto es el texto que a su vez contiene el texto que desea encontrar.

Núm_inicial especifica el carácter a partir del cual comenzará la búsqueda. El primer carácter de dentro_del_texto es el carácter número 1. Si omite núm_inicial, se supone que es 1.

HALLAR

Busca en un texto un carácter y devuelven el número de la posición del carácter dentro del texto contando a partir del primer carácter del texto (de izquierda a derecha), se le indica la posición inicial a partir de la cual debe contar en caso de omitir es 1.



Sintaxis

=HALLAR(texto_buscado;dentro_del_texto;núm_inicial)

Texto_buscado es el texto que desea encontrar.

Dentro_del_texto es el texto que a su vez contiene el texto que desea encontrar.

Núm_inicial especifica el carácter a partir del cual comenzará la búsqueda. El primer carácter de dentro_del_texto es el carácter número 1. Si omite núm_inicial, se supone que es 1.

LARGO

Devuelve el número de caracteres de una cadena de texto

Sintaxis

LARGO(texto)

MAYUSC

Pone el texto en mayúsculas.

Sintaxis

=MAYUSC(texto)

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

MINUSC

Convierte todas las mayúsculas de una cadena de texto en minúsculas.

Sintaxis

=MINUSC(texto)

Texto es el texto que se desea convertir en minúsculas. MINUSC no cambia los caracteres de texto que no sean letras.

NOMPROPIO

Cambia a mayúscula la primera letra del argumento texto y cualquiera de las otras letras de texto que se encuentren después de un carácter que no sea una letra. Convierte todas las demás letras a minúsculas.

Sintaxis

=NOMPROPIO(texto)

Texto es el texto entre comillas, una fórmula que devuelve texto o una referencia a una celda que contiene el texto al que se desea agregar mayúsculas.

IGUAL

Compara dos cadenas de texto y devuelve VERDADERO si son exactamente iguales y FALSO si no lo son. IGUAL reconoce mayúsculas y minúsculas, pero pasa por alto las diferencias de formato. Use IGUAL para comprobar el texto que especifica en un documento.

Sintaxis

=IGUAL(texto1;texto2)

Texto1 es la primera cadena de texto.



Texto2 es la segunda cadena de texto.

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.

Sintaxis

=ESPACIOS(texto)

Texto es el texto del que desea quitar espacios.

VALOR

Convierte una cadena de texto que representa un número en un número.

Sintaxis

=VALOR(texto)

Texto es el texto entre comillas o una referencia a una celda que contiene el texto que se desea convertir.

Funciones de Búsqueda y referencia

CONSULTAV

Busca un valor específico en la primera columna de una matriz de tabla y devuelve, en la misma fila, un valor de otra columna de dicha matriz de tabla.

La V de CONSULTAV significa vertical.

Sintaxis

CONSULTAV(valor_buscado, matriz_buscar_en, indicador_columnas, ordenado)

Valor_buscado Valor que se va a buscar en la primera columna de la matriz (matriz: utilizada para crear fórmulas sencillas que producen varios resultados o que funcionan en un grupo de argumentos que se organizan en filas y columnas. Un rango de matriz comparte una fórmula común; una constante de matriz es un grupo de constantes utilizadas como un argumento.) de tabla. Valor_buscado puede ser un valor o una referencia. Si valor_buscado es inferior al menor de los valores de la primera columna de matriz_buscar_en, BUSCARV devuelve al valor de error #N/A.

Matriz_buscar_en Dos o más columnas de datos. Use una referencia a un rango o un nombre de rango. Los valores de la primera columna de matriz_buscar_en son los valores que busca valor_buscado. Estos valores pueden ser texto, números o valores lógicos

Indicador_columnas Número de columna de matriz_buscar_en desde la cual debe devolverse el valor coincidente. Si el argumento indicador_columnas es igual a 1, la función devuelve el valor de la primera columna del argumento matriz_buscar_en; si el argumento indicador_columnas es igual a 2, devuelve el valor de la segunda columna de matriz_buscar_en y así sucesivamente. Si indicador_columnas es:

Si es inferior a 1, BUSCARV devuelve al valor de error #VALUE!

Si es superior al número de columnas de matriz_buscar_en, BUSCARV devuelve el valor de error #REF!



Ordenado Valor lógico que especifica si BUSCARV va a buscar una coincidencia exacta o aproximada:

Si se omite o es VERDADERO, se devolverá una coincidencia exacta o aproximada. Si no localiza ninguna coincidencia exacta, devolverá el siguiente valor más alto inferior a valor_buscado.

Los valores de la primera columna de matriz_buscar_en deben estar clasificados según un criterio de ordenación ascendente; en caso contrario, es posible que BUSCARV no devuelva el valor correcto.

Si es FALSO, BUSCARV sólo buscará una coincidencia exacta. En este caso, no es necesario ordenar los valores de la primera columna de matriz_buscar_en. Si hay dos o más valores en la primera columna de matriz_buscar_en, se utilizará el primer valor encontrado. Si no se encuentra una coincidencia exacta, se devolverá el valor de error #N/A.

CONSULTAH

Busca un valor en la fila superior de una tabla o una matriz (matriz: utilizada para crear fórmulas sencillas que producen varios resultados o que funcionan en un grupo de argumentos que se organizan en filas y columnas. La H de BUSCARH significa "Horizontal".

Sintaxis

=CONSULTAH(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.

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 devuelve el valor de error #¡VALOR!; si indicador_filas es mayor que el número de filas en matriz_buscar_en, BUSCARH devuelve el valor de error #¡REF!

Ordenado es un valor lógico que especifica si BUSCARH 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 encontrará una coincidencia exacta. Si no encuentra ninguna, devolverá el valor de error #N/A.

Capítulo 3

Base de Datos


Formularios.

Ordenar una base de datos.

Autofiltros.

Filtros avanzados.

Subtotales.



Base de datos

Formularios

Tablas de Excel

Una tabla en Excel 2010 es un conjunto de datos organizados en filas o registros, en la que la primera fila contiene las cabeceras de las columnas (los nombres de los campos), y las demás filas contienen los datos almacenados. Es como una tabla de base de datos, de hecho también se denominan listas de base de datos. Cada fila es un registro de entrada, por tanto podremos componer como máximo una lista con 16,384 campos y 1,048,574 registros.

Las tablas son muy útiles porque además de almacenar información, incluyen una serie de operaciones que permiten analizar y administrar esos datos de forma muy cómoda.

Entre las operaciones más interesantes que podemos realizar con las listas tenemos:

Ordenar la los registros.

Filtrar el contenido de la tabla por algún criterio.

Utilizar fórmulas para la lista añadiendo algún tipo de filtrado.

Crear un resumen de los datos.

Aplicar formatos a todos los datos.

Las tablas de datos, ya se usaban en versiones anteriores de Excel, pero bajo el término Listas de datos. Incluso encontrarás, que en algunos cuadros de diálogo, se refiere a las tablas como listas.

Crear una tabla

Para crear una lista tenemos que seguir los siguientes pasos:

- Seleccionar el rango de celdas (con datos o vacías) que queremos incluir en la lista.

- Seleccionar del Tabla en la pestaña Insertar.

Aparecerá a continuación el cuadro de diálogo Crear tabla.



Si nos hemos saltado el paso de seleccionar previamente las celdas, lo podemos hacer ahora.

- Si en el rango seleccionado hemos incluido la fila de cabeceras (recomendado), activaremos la casilla de verificación La lista tiene encabezados.

- Al final hacer clic en Aceptar.

Al cerrarse el cuadro de diálogo, podemos ver que en la banda de opciones aparece la pestaña Diseño, correspondiente a las Herramientas de tabla:

Y en la hoja de cálculo aparece en rango seleccionado con el formato propio de la tabla.

Modificar los datos de una tabla

Para modificar o introducir nuevos datos en la tabla podemos teclear directamente los nuevos valores sobre la ella, o bien podemos utilizar un formulario de datos. Esta segunda opción viene muy bien sobre todo si la lista es muy grande.

Veamos un ejemplo, tenemos la siguiente lista con información de nuestros amig@s.



Un formulario de datos es un cuadro de diálogo que permite al usuario escribir o mostrar con facilidad una fila entera de datos (un registro).

Para abrir el formulario de datos, tenemos que posicionarnos en la lista

para que esté activa, y damos clic en el icono Formulario .

NOTA: Como esta opción no está directamente disponible en la Cinta de opciones, podemos añadirla a la Barra de acceso rápido, de la forma que ya vimos. Damos clic en la Ficha Archivo > Opciones de Excel > Personalizar, y Agregar el icono Formulario..., en la sección de Comandos que no están en la cinta de opciones.

Al crear el formulario, disponemos de siguientes botones:

Nuevo: Sirve para introducir un nuevo registro.

Eliminar: Eliminar el registro que está activo.

Restaurar: Deshace los cambios efectuados.

Buscar anterior: Se desplaza al registro anterior.

Buscar siguiente: Se desplaza al siguiente registro.

Criterios: Sirve para aplicar un filtro de búsqueda.

Cerrar: Cierra el formulario.

Para cambiar los datos de un registro, primero nos posicionamos sobre el registro, luego rectificamos los datos que queramos (para desplazarnos por los campos podemos utilizar las teclas de tabulación), si nos hemos



equivocado y no queremos guardar los cambios hacemos clic en el botón Restaurar, si queremos guardar los cambios presionamos la tecla Enter.

Para crear un nuevo registro, hacemos clic en el botón Nuevo, Excel se posicionará en un registro vacío, sólo nos quedará rellenarlo y presionamos Enter o Restaurar para aceptar o cancelar respectivamente.

Después de aceptar Excel se posiciona en un nuevo registro en blanco por si queremos insertar varios registros, una vez agregados los registros, hacer clic en Cerrar.

Para buscar un registro y posicionarnos en él podemos utilizar los botones Buscar anterior y Buscar siguiente o ir directamente a un registro concreto introduciendo un criterio de búsqueda. Pulsamos en el botón Criterios con lo cual pasamos al formulario para introducir el criterio de búsqueda, es similar al formulario de datos pero encima de la columna de botones aparece la palabra Criterios.

Por ejemplo, si buscamos un registro con el valor Ana en el campo Nombre, escribimos Ana en Nombre y damos clic el botón Buscar Siguiente, Excel vuelve al formulario de datos y nos posiciona en el registro de nombre Ana.

Modificar la estructura de la tabla

Damos clic en el icono Cambiar tamaño de la tabla, podemos seleccionar un nuevo rango de datos. Pero si la tabla contiene encabezados, estos deben permanecer en la misma posición, así que sólo podremos aumentar y disminuir filas.

Podemos modificar directamente el rango de filas y columnas, estirando o

encogiendo la tabla desde su esquina inferior derecha.

Cuando necesitemos añadir una fila al final de la tabla para continuar introduciendo datos, sólo tendremos que presionamos la tecla TAB desde la última celda y aparecerá una fila nueva.

Si necesitamos insertar filas y columnas entre las filas existentes de la tabla, nos posicionaremos en la primera celda que queremos desplazar y elegiremos la opción correspondiente desde el menú Insertar, en la pestaña de Inicio o en el menú contextual de la tabla.



Para eliminar filas o columnas, deberemos posicionarnos sobre una celda, y elegiremos Filas o Columnas de la tabla en el menú Eliminar, disponible en la pestaña de Inicio y en el menú contextual de la tabla.

Seleccionando una celda, fila o columna, y presionamos la tecla la tecla SUPR, eliminamos los datos seleccionados, pero no la estructura de la tabla.

Para eliminar la tabla completa, seleccionamos toda la tabla y presionamos la tecla SUPR. Si deseamos eliminar la estructura de la tabla,

pero conservar los datos en la hoja, entonces damos clic en la pestaña de Diseño de la tabla.

Estilo de la tabla

Una forma fácil de dar una combinación de colores a la tabla que resulte elegante, es escogiendo uno de los estilos predefinidos, disponibles en la pestaña Diseño de la tabla.

En Opciones de estilo de la tabla, podemos marcar o desmarcar otros aspectos, como que las columnas o filas aparezcan remarcadas con bandas, o se muestre un resaltado especial en la primera o última columna.

Las bandas y resaltados dependerán del estilo de la tabla.

Por lo demás, a cada celda se le podrán aplicar los colores de fuente y fondo, fondo condicional, etc. que a cualquier celda de la hoja de cálculo.

En está tabla, se ha cambiado el estilo, y se han marcado las opciones



Primera y Última columna.

Ordenar una tabla de datos

A la hora de ordenar una tabla, Excel puede hacerlo de forma simple, es decir, ordenar por un único campo u ordenar la lista por diferentes campos a la vez.

Para hacer una ordenación simple, por ejemplo ordenar la lista anterior por el primer apellido, debemos posicionarnos en la columna del primer apellido, después podemos acceder a la pestaña Datos y damos clic sobre Ordenar... y escoger el criterio de ordenación o bien damos clic sobre uno de

los botones de la sección Ordenar y filtrar para que la ordenación sea ascendente o descendente respectivamente. Estos botones también están disponibles al desplegar la lista que aparece dando clic en la pestaña

junto al encabezado de la columna.

Para ordenar la lista por más de un criterio de ordenación, por ejemplo ordenar la lista por el primer apellido más la fecha de nacimiento, en la pestaña Datos, damos clic sobre Ordenar... nos aparece el cuadro de diálogo Ordenar donde podemos seleccionar

los campos por los que queremos ordenar (damos clic en Agregar Nivel para añadir un campo), si ordenamos según el valor de la celda, o por su color o icono (en Ordenar Según), y el Criterio de ordenación, donde elegimos si el orden es alfabético (A a Z o Z a A) o sigue el orden de una Lista personalizada. Por ejemplo, si en la columna de la tabla se guardan los nombres de días de la semana o meses, la ordenación alfabética no sería correcta, y podemos escoger una lista donde se guarden los valores posibles, ordenados de la forma que creamos conveniente, y así el criterio de ordenación seguirá el mismo patrón.

Seleccionando un nivel, y presionamos las flechas hacia arriba o hacia abajo, aumentamos o disminuimos la prioridad de ordenación de este nivel.



Los datos se ordenarán, primero, por el primer nivel de la lista, y sucesivamente por los demás niveles en orden descendente.

En la parte superior derecha tenemos un botón Opciones..., este botón sirve para abrir el cuadro Opciones de ordenación dónde podremos especificar más opciones en el criterio de la ordenación.

Filtrar el contenido de la tabla

Filtrar una lista no es ni más ni menos que de todos los registros almacenados en la tabla, seleccionar aquellos que se correspondan con algún criterio fijado por nosotros.

Excel nos ofrece dos formas de filtrar una lista.

- Utilizando el Filtro (autofiltro).

- Utilizando filtros avanzados.

Autofiltro

Utilizar el Filtro

Para utilizar el Filtro nos servimos de las listas desplegables asociadas a las cabeceras de campos (podemos mostrar u ocultar el autofiltro en la pestaña Datos marcando o desmarcando el botón Filtro).

Si damos clic, por ejemplo, sobre la flecha del campo Nombres, nos aparece un menú desplegable como este, donde nos ofrece una serie de opciones para realizar el filtro.

Por ejemplo, si sólo marcamos Ana Cecilia, Excel filtrará todos los registros que tengan Ana Cecilia en el 1er apellido y las demás filas 'desaparecerán' de la lista.



Otra opción, es usar los Filtros de texto, donde se despliegan una serie de opciones:

En cualquier opción, accedemos a una ventana donde podemos elegir dos condiciones de filtro de texto, y exigir que se cumpla una condición o las dos. Excel evaluará la condición elegida con el texto que escribamos, y si se cumple, mostrará la fila. Usaremos el carácter ? para determinar que en esa posición habrá un carácter, sea cual sea, y el asterisco * para indicar que puede haber o no un grupo de caracteres.

En el ejemplo de la imagen, solo se podría mostrar los registros cuyo Artefacto tenga una i en el segundo carácter.

Para indicarnos que hay un filtro activo, la flecha de la lista desplegable cambia de icono.

Para quitar el filtro, volvemos a desplegar la lista y elegimos la opción (Seleccionar Todo), reaparecerán todos los registros de la lista. También

podemos quitar el filtro damos clic en Borrar filtro en la pestaña Datos.



Filtros avanzados

Utilizar Filtros avanzados

Si queremos filtrar los registros de la lista por una condición más compleja, utilizaremos el cuadro de diálogo Filtro avanzado. Previamente deberemos tener en la hoja de cálculo, unas filas donde indicaremos los criterios del filtrado.

Para abrir el cuadro de diálogo Filtro

avanzado, damos clic en en la sección Ordenar y filtrar.

Rango de la lista: Aquí especificamos los registros de la lista a los que queremos aplicar el filtro.

Rango de criterios: Aquí seleccionamos la fila donde se encuentran los criterios de filtrado (la zona de criterios).

También podemos optar por guardar el resultado del filtrado en otro lugar, seleccionando la opción Copiar a otro lugar, en este caso rellenaremos el campo Copiar a: con el rango de celdas que recibirán el resultado del filtrado.

Si marcamos la casilla Sólo registros únicos, las repeticiones de registros (filas con exactamente los mismos valores) desaparecerán.

Para volver a visualizar todos los registros de la lista, acceder al menú Datos - Filtro - Mostrar todo

Subtotales

Crear un resumen de datos

Cuando hablamos de crear un resumen de los datos de una tabla nos estamos refiriendo a crear subtotales agrupando los registros por alguno de los campos de la lista.

Por ejemplo si tenemos una lista de niños con los campos nombre, dirección, localidad y edad; podemos obtener un resumen de la edad media de los niños por localidad.

Otro ejemplo, el que te enseñamos abajo, disponemos de una lista de vehículos clasificados por marca y modelo; y queremos averiguar el coste total de cada marca.

Para agregar los subtotales automáticamente debemos situarnos sobre una celda cualquiera de la lista y marcar la opción Fila de totales en las Opciones de estilo de tabla, en la pestaña Diseño.



Al seleccionar una celda de la fila de totales, aparece una pestaña con una lista de las funciones que podemos usar para calcular el total de esa columna.

Se pueden ingresar una serie de funciones al final como se muestra en la imagen.

Se puede mejorar el resumen y los subtotales de la tabla utilizando las tablas dinámicas, que se desarrollara en el Capitulo siguiente.

Capítulo 4

Tabla Dinámica


Tabla Dinámicas - Defunción

Crear partiendo de datos internos.

Asistente para tablas dinámicas.

Diseño, agregar campos.

Modificar una tabla Dinámica.

Gráficos Dinámicos.

Eliminar una tabla dinámica.



TABLA DINÁMICA

Una tabla dinámica consiste en el resumen de un conjunto de datos, atendiendo a varios criterios de agrupación, representado como una tabla de doble entrada que nos facilita la interpretación de dichos datos.

Es dinámica porque nos presentar los datos de una forma simple y con un formato y presentación flexible. Podrá girar filas y columnas para ver diferentes resúmenes de los datos originales, filtrar los datos mostrando diferentes páginas y ver los datos específicos de algún área de interés.

CREAR PARTIENDO DE DATOS INTERNOS

Para la creación de Tablas Dinámicas el Excel 2010 nos proporciona la opción de Tablas Dinámicas en la cuales podemos crear tablas dinámicas y gráficos dinámicos.

Supongamos que tenemos una colección de datos de los vendedores por regiones y productos que venden y el mes de la venta, además sabemos la cantidad vendida, el importe y el si fue vendido al crédito o al contado (observación).

Vamos a crear una tabla dinámica a partir de estos datos para poder examinar las ventas de cada vendedor por producto en cada mes

Para ello vamos a la pestaña Insertar y hacemos clic en Tabla dinámica (también podemos desplegar el menú haciendo clic en la flecha al pie del botón para crear un gráfico dinámico).



Aparece el cuadro de diálogo de creación de tablas dinámicas. Desde aquí podremos indicar el lugar donde se encuentran los datos que queremos analizar y el lugar donde queremos ubicarla

Asistente para tablas dinámicas

En nuestro caso indicamos que vamos a seleccionar los datos de un rango de celdas y que queremos ubicarla en una hoja de cálculo nueva.

Podríamos crear una conexión con otra aplicación para obtener los datos desde otra fuente diferente a Excel.



En el caso de seleccionar la opción Selecciona una tabla o rango debemos seleccionar todas las celdas que vayan a participar, incluyendo las cabeceras.

Damos clic en Aceptar para seguir.

Se abrirá un nuevo panel en la derecha de la pantalla:

Diseño, agregar campos

Desde este panel podemos personalizar la forma en que van a verse los datos en la tabla dinámica.

Con esta herramienta podríamos construir una tabla dinámica con la siguiente estructura:

Una fila para cada una de los Vendedores de la tabla.

Una columna para cada uno de los Mes de la tabla.

En el resto de la tabla incluiremos el total del Importe para cada Vendedor en cada Mes.

Para ello simplemente tendremos que arrastrar los elementos que vemos listados a su lugar correspondiente al pie del panel.

Tras realizar la tabla dinámica este sería el resultado obtenido.



Podemos ver que la estructura es la que hemos definido anteriormente, en el campo fila tenemos los vendedores, en el campo columnas tenemos los meses y en el centro de la tabla las sumas de los importes (totales $).

Con esta estructura es mucho más fácil analizar los resultados.

Una vez creada la tabla dinámica nos aparece la pestaña Opciones:

Modificar una tabla Dinámica

El panel lateral seguirá pudiéndose utilizar, así que en cualquier momento podremos quitar un campo de un zona arrastrándolo fuera.

Con esto vemos que en un segundo podemos variar la estructura de la tabla y obtener otros resultados sin casi esfuerzos.

Si arrastrásemos a la zona de datos los campos cantidad y total, obtendríamos la siguiente tabla, más compleja pero con más información:



Puede que no visualices la tabla de la misma forma, al añadir varios campos en la

sección Valores el rótulo aparecerá en una las secciones de rótulos, si te aparece en Rótulos de columna despliega la lista asociada a él y selecciona la opción Mover a rótulos de fila.

Aplicar filtros a una tabla dinámica

Otra característica útil de las tablas dinámicas es permitir filtrar los resultados y así visualizar únicamente los que nos interesen en un momento determinado. Esto se emplea sobre todo cuando el volumen de datos es importante.

Los campos principales en el panel y los rótulos en la tabla están acompañados, en su parte derecha, de una flecha indicando una lista desplegable.

Por ejemplo, si damos clic sobre la flecha del rótulo Rótulos de columna nos aparece una lista como vemos en la imagen con los distintos meses disponibles en la tabla con una casilla de verificación en cada uno de ellos para indicar si los queremos ver o no, más una opción para marcar todas las opciones en este caso todos los meses.

Si dejamos marcados los artefactos cocina, licuadora microondas, desaparecerá de la tabla, pero no se pierden, en cualquier momento podemos visualizarlos volviendo a desplegar la lista y marcando la casilla (Seleccionar todo).

Para cerrar este cuadro debemos damos clic en Aceptar o sobre Cancelar para cerrar y dejarlo como estaba.

Aplicando el filtro a varios campos podemos formar condiciones de filtrado más complejas.



Obtener promedios en una tabla dinámica

Por defecto, al crear una tabla dinámica, Excel nos genera unos totales con sumatorio, puede interesarnos modificar esas fórmulas por otras como pueden ser sacar el máximo o el mínimo, el promedio, etc.

Para hacer esto debemos situarnos en cualquier celda de la zona que queremos rectificar y hacer clic con el botón secundario del Mouse, nos aparece un menú emergente con diferentes opciones, debemos escoger la opción Configuración de campo de valor... y nos aparece un cuadro de diálogo como el que vemos en la imagen.

En este cuadro de diálogo podemos escoger cómo queremos hacer el resumen, mediante Suma, Cuenta, Promedio, etc.

También podemos abrir el cuadro de diálogo con el botón de la pestaña Opciones.

Gráficos con tablas dinámicas

Para crear una gráfica de nuestra tabla dinámica deberemos hacer clic en el botón Gráfico dinámico de la pestaña Opciones.

Para cambiar el formato del gráfico a otro tipo de gráfico que nos agrade más o nos convenga más según los datos que tenemos.

Al dar este botón se abrirá el cuadro de diálogo de Insertar gráfico, allí deberemos escoger el gráfico que más nos interese se vea gráficamente nuestra información.

Luego, la mecánica a seguir para trabajar con el gráfico es la misma que se realiza para crear un grafico en Excel 2010.



Eliminar una tabla dinámica.

Para eliminar una tabla dinámica simplemente debemos seleccionar la tabla en su totalidad y presionar la tecla Supr (en caso que la tabla dinámica se encuentre en la misma hoja de los datos) o eliminar la hoja donde está la tabla dinámica.

En le caso que se deseara borrar todo los cálculos realizados en la tabla dinámica para hacer otra con los mismos datos solo elegiríamos la Opción Borrar Todo como se muestra en la figura siguiente:

Y quedara la tabla para ser trabajada.





Capítulo 5

Datos externos y automatización


Importar datos

Consulta de una base de datos

Consulta web

Validación de rangos

Proteger hoja

Plantilla y formularios

Como guardar una plantilla

Vínculos o enlaces. Hipervínculos



Datos externos y automatización

La necesidad de trabajar en Microsoft Office Excel 2010 con datos procedentes de otras aplicaciones. Tenemos dos alternativas:

Introducir de nuevo los datos en un libro de trabajo, con el consumo de tiempo que ello implica más el riesgo de introducir erróneamente los datos al introducirlos manualmente.

Utilizar algunas de las herramientas disponibles en Excel para importar datos. Para importar datos externos a Excel disponemos básicamente de dos opciones:

Utilizar el portapapeles de Windows, es decir, copiar los datos de la aplicación externa y pegarlos en una hoja de Excel.

Importar datos de otro archivo que no tiene que ser necesariamente del formato Excel.

La primera opción es la más directa, pero tiene el contrapunto de ser más laboriosa y tediosa.

La segunda opción es más rápida, pero pueden surgir problemas de compatibilidad dependiendo del formato del archivo a importar.

El problema fundamental de la importación de datos externos, hemos dicho que es debido al formato de los propios archivos, esto quiere decir que cada aplicación genera un archivo con un formato propio para identificar mejor el contenido de los datos, por ejemplo Excel al generar un archivo .xlsx no solamente guarda los datos que hemos introducido sino que lo guarda con un formato especial para interpretar el documento en su plenitud, de esta manera sabe exactamente dónde están las fórmulas, qué formato estético tiene el documento, etc.

Además de esto, al importar datos de una aplicación externa debemos tener en cuenta que pueden surgir los siguientes fallos:

Algunas de las fórmulas no ajustan correctamente.

El formato no se ajusta al original.

Las fórmulas y funciones que no entiende no las copia.

Importar datos

Cuando decimos archivos de texto nos estamos refiriendo a archivos que no tienen formato, los conocidos como archivos de texto plano (ASCII); los archivos de texto con formato como los de Word (.doc) o los (.rtf) tienen otra forma de importarse a Excel que veremos más adelante.

Asistente para importar datos texto

Pero para importar archivos de texto con el asistente podemos hacerlo de dos formas distintas:

Podemos acceder a la pestaña Datos y seleccionar uno de los tipos de orígenes de datos que podemos encontrar en esta sección.



Nos aparece un cuadro de diálogo para seleccionar el archivo a importar.

Una vez seleccionado el archivo de texto aparece

.



Nos aparece un cuadro de diálogo preguntándonos dónde deseamos Importar los datos.

Damos sobre Aceptar y aparecerán los datos importados en la hoja.

Consulta web

Otra herramienta para importar datos, nos permite obtener datos Desde Web. Pulsando sobre ella, se abrirá una ventana del navegador, donde se marcan con flechas amarillas las tablas existentes en la página web.

Para elegir las tablas, basta con pulsar sobre las flechas.



En el botón Opciones... de esta ventana, podremos elegir por ejemplo, si importar o no el formato de la tabla. Ten en cuenta que aunque no se vean los bordes, la mayoría de las webs están estructuradas con tablas. Una vez finalizada la elección, pulsamos Importar. Veremos entonces la misma ventana que con Access, donde elegir si importar en la hoja o en una nueva, y donde podremos modificar en Preferencias las opciones de actualización de la conexión. Otra forma rápida de hacer esto, es directamente seleccionar la tabla en nuestro navegador, copiarla con Ctrl + C y pegarla en la hoja de Excel. Después podremos modificar el formato y si queremos que haya actualización.

Validación de datos

La Validación de datos es una herramienta extremadamente útil especialmente cuando compartimos un archivo de Excel con diferentes usuarios y necesitamos limitar las opciones de datos que se deben ingresar en las celdas.



Por ejemplo, podemos necesitar que los usuarios completen el campo “Producto”, nos muestre una lista de los productos a elegir. O también se busca limitar la posibilidad de cifras incorrectas. Por ejemplo, que Excel nos muestre un mensaje de error cuando el número ingresado está fuera de los parámetros deseados. En estos casos, Validación es la herramienta que resuelve nuestros problemas. Vamos a utilizar el ejemplo de la derecha. Tenemos solamente dos columnas: Productos y Ventas. Necesitamos limitar que solo puedan seleccionarse determinados productos (que pusimos en la columna D) y que las ventas sean valores enteros mayores que cero. En Excel 2010 se hace desde la pestaña “Datos” y el menú “Validación de datos”.

PASOS

Para seguir los pasos, va a ser más fácil si descargas el archivo que acompaña este tutorial (el link está al inicio del post).

A. Validación de Productos.

1. Seleccionamos las celdas a las cuales queremos limitar la selección y que muestre el menú con las opciones disponibles. Seleccionamos entonces las celdas A2:A14

2. En la pestaña de Datos, al oprimir “Validación de datos” se despliega un nuevo



menú con tres opciones:

Validación de datos: Es donde ingresamos los criterios y restricciones.

Rodear con un círculo datos no válidos: Rodea con un círculo rojo los

datos de las celdas a las cuales les asignamos una restricción pero que

por alguna razón tienen datos que están fuera de estos rangos.

Borrar círculos de validación: Simplemente deja de mostrar los círculos

del punto anterior.

3. Seleccionamos Validación de datos. Se despliega un menú como el de la siguiente figura.

Tenemos 3 pestañas:

Configuración: En la cual

configuramos qué valores están

permitidos.

Mensaje de entrada (opcional): si

queremos que nos muestre un

mensaje cuando se selecciona la

celda con la validación.

Mensaje de error (opcional): Mensaje cuando se ingresa un valor fuera de

los permitidos. A su vez tenemos tres Estilos para manejar los errores.

o Grave: Directamente NO deja ingresar datos fuera de los indicados.

o Advertencia: Cuando se ingresa un valor fuera de los establecidos,

nos avisa de esta condición, pero da la posibilidad de ingresarlo o

rechazar.

o Información: Simplemente avisa que el valor no corresponde a los

asignados.

Vamos pestaña por pestaña.

4. Configuración.

Por defecto nos indica permitir “Cualquier valor”. En nuestro ejemplo, tenemos una lista específica con los productos que necesitamos que estén disponibles.

Seleccionamos la opción “Lista” lo cual nos habilita a mostrar la lista desplegable (como la que se ve en la primera imagen de este tutorial) y el origen donde se encuentran los datos disponibles.

En este caso, el Origen son las celdas =$D$2:$D$6 (debemos tener las referencias fijas si no tendremos problemas al querer copiar

http://trucosdeexcel.com/wp-content/uploads/2009/11/validacion6.JPG






la validación en otras celdas).

5. Mensajes de error

Para no extenderme mucho más, simplemente pondré un Mensaje de Error en el cual indico:

Estilo: Advertencia (como se menciona en el punto 3)

Título (lo que se mostrará en la barra azul del mensaje): ingreso “Error de

datos”

Mensaje de error: “El producto no está dado de alta”.

ACEPTAR

B. Validación de Ventas.

Los pasos son idénticos al anterior. Solo cambia el criterio. En lugar de seleccionar de una lista, debemos ingresar un rango de valores aceptables.

Seleccionadas las celdas de la columna B (B2:B14), selecciono “Número entero” (si quisiéramos un rango que permita cualquier tipo de número debemos seleccionar “Decimal”), y fijo el mínimo en cero y el máximo en cualquier número muy grande (no estamos validando en este ejemplo un máximo, así que puede ser 99999999999999).

Más adelante, publicaré la versión Avanzada en la cual definiremos una lista que se pueda ajustar automáticamente cada vez que se agrega un producto nuevo sin necesidad de cambiar el Origen de las celdas (mencionado en el punto 4).

Proteger hoja

Para proteger una hoja seleccionamos la opción Formato del Grupo Celdad de la Ficha Inicio, elegimos Formato de Celdas y la ficha proteger y se muestra como la siguiente imagen



Debe aparecer como se muestra y damos clic en Aceptar. Luego elegimos Proteger Hoja del grupo Cambios de la ficha Revisar, como se muestra a continuación: Luego aparece



Donde colocamos una contraseña y luego nos pide confirmar

Plantillas y formularios

El Excel 2010 presenta una serie de nuevas plantillas y formularios, los podemos obtener desde la opción Botón de Office – Nuevo y aparecen una serie de plantillas y formularios como aparece en la siguiente imagen.



Como guardar una plantilla

Podemos crear nuestras propias plantilla para optimizar nuestro trabajo, para crearlas realizamos la siguiente secuencia Botón de office – Guardar e cambiamos el tipo por Plantilla

Vinculo o enlaces. Hipervínculos

Podemos crear hiperenlaces en nuestra hoja de cálculo Excel ya sean a nuestro propio libro u hoja actual o bien a otros libros e inclusive a páginas de Internet.

Un hiperenlace o hipervínculo no es más que un acceso directo a un punto en concreto del mismo documento o a otro documento y también, a una página en Internet.

Si en una celda de Excel escribimos una dirección web, por ejemplo www.unmsm.edu.pe, Excel por defecto nos crea el hiperenlace y lo deja resaltado como enlace. Un hiperenlace puede estar hecho sobre texto, sobre un gráfico o una imagen. Para hacer esto debemos seguir los siguientes pasos:

Seleccionar el objeto (texto, gráfico, imagen) sobre el cual vamos a crear el enlace.

Dar clic sobre el Hipervínculo del Grupo Vinculos de la opción de cinta Insertar... Ctrl+Alt+K.

Nos aparece el cuadro de diálogo Insertar hipervínculo que vemos en la imagen de abajo.



En la columna Vincular a: podemos seleccionar el tipo de enlace que vamos a crear, si es un enlace a página Web existente, si es a un punto dentro del mismo documento, si es a un documento nuevo o a una dirección de correo electrónico. En el recuadro Dirección: escribimos la dirección del hiperenlace o si está en nuestro disco duro podemos buscarla como cuando buscamos un fichero para abrirlo ayudándonos del recuadro Buscar en:. Finalmente damos clic en Aceptar.

Documents

Manual de excel 2010 intermedio