manual excel 2007 muestra

Ofimática Avanzada http://capacitateexcel.blogspot.com

ÍndiceÍndice .............................................................................................................. 1 Funciones ....................................................................................................... 3

Insertar una función ................................................................................................ 3

Operadores numéricos ............................................................................................ 5

La sintaxis de una función ...................................................................................... 6

Tipo de argumentos de una función ........................................................................ 7

Funciones Matemáticas .......................................................................................... 7

Funciones de búsqueda ......................................................................................... 10

Funciones lógicas ................................................................................................. 13

Funciones de texto ................................................................................................ 14

Usando nombres en las fórmulas ......................................................................... 17

Como asignar un nombre a una celda o rango ....................................................... 17

Insertando nombres a rangos desde la pestaña fórmulas ....................................... 19

Administrador de nombres ................................................................................... 20

Formato Condicional ................................................................................... 21 Formato condicional en rangos de colores ............................................................ 23

Formato condicional basado en una fórmula ......................................................... 24

Validación de Datos y Filtros ...................................................................... 27 Validación de datos .............................................................................................. 27

Trabajar con Listas ............................................................................................... 28

Ayuda cuando se repiten datos de entrada en una lista .......................................... 29

Filtros ................................................................................................................... 30

Usando Autofiltro ................................................................................................. 30

Autofiltro personalizado ...................................................................................... 32

Filtro “Avanzada” ................................................................................................ 32

Caracteres comodines para filtro avanzado ........................................................... 35

Ejemplos de criterios de filtro avanzado ............................................................... 35

Lenin E. Puga Buenfil [email protected]


Tablas dinámicas ......................................................................................... 37 Creando una tabla dinámica con el asistente ........................................................ 38

Elementos de la tabla dinámica ............................................................................ 41

Cambiar el modo en que se resumen los datos ...................................................... 42

Agrupaciones en tablas dinámicas ........................................................................ 43

Cálculos personalizados ....................................................................................... 43

Creando campos y elementos calculados .............................................................. 45

Creando un campo calculado ................................................................................ 46

Creando un elemento calculado ............................................................................ 47

Crear listas de fórmulas ........................................................................................ 48

Gráfico dinámico .................................................................................................. 50

Creando un gráfico dinámico a partir de una tabla dinámica ya existente ............. 50

Análisis What-if ............................................................................................ 52

Función Buscar Objetivo ............................................................................ 52 Tablas de una y dos variables ................................................................... 55

Qué es una tabla de datos para What-if ................................................................. 55

Tabla de una variable ........................................................................................... 55

Tabla de dos variables .......................................................................................... 59

Macros .......................................................................................................... 62 Archivo para macros ............................................................................................ 62

Habilitar la pestaña Programador .......................................................................... 63

La seguridad de macros ........................................................................................ 63

Grabar una macro ................................................................................................. 64

Ejecutar una macro con el método abreviado ........................................................ 65

Ejecutar una macro ............................................................................................... 66

Asignar una macro a un botón .............................................................................. 68



Funciones

Una función es una fórmula creada con Visual Basic para realizar cálculos complicados. Las

funciones tiene un nombre propio y existen multitud de funciones en Excel.

Existen funciones que realizan cálculos financieros, estadísticos, matemáticos, e incluso

funciones de búsqueda y de texto.

Insertar una función

Barra de Fórmulas

Las funciones pueden ser introducidas usando el teclado o por el asistente de funciones.

Para insertar una función por medio del asistente de fórmulas, necesitamos hacer lo siguiente:

1. En la barra de herramientas hacemos clic en la pestaña de Fórmulas seguido pulsamos

el botón Insertar función y con ello aparece la pantalla de Pegar función la cual muestra

las diversas categorías de las funciones de Excel 2007.

2. Por ejemplo, Si deseamos usar la función de suma seleccionamos SUMA de la

ventana de Nombre de la función.



3. Hacemos clic en el botón de Aceptar y tenemos otra pantalla para insertar los

parámetros de la función.



4. Ahora tenemos la pantalla para la entrada de los datos o el vínculo a los datos a sumar,

en los espacios de texto colocamos los valores a sumar o el vinculo a las celdas a sumar.

Operadores numéricos

Los operadores son una parte importante en las fórmulas pues con ellos podemos establecer

criterios al momento de hacer operaciones.



Operador Nombre+ Suma- Resta* Multiplicación

/ División^ Exponencial& Concatenación= Igual a> Mayor que< Menor que

>= Mayor e igual a <= Menor e igual a<> diferente a

Operadores utilizados en las fórmulas

La sintaxis de una función

Las funciones tienen reglas de sintaxis para un correcto funcionamiento, ver el siguiente

ejemplo:

=SUMA(A1:B12;100)

En esta fórmula se suman los valores del rango A1:B12 y el número 100. Entonces podemos

decir que esta función tiene dos argumentos a sumar, el rango y el valor de 100.


Signo igual Nombre de lafunción

Argumentos separados porpunto y coma, yencerrados entre

paréntesis

6


Tipo de argumentos de una función

Los argumentos de una función pueden ser:

Números: con o sin separador decimal, aceptando hasta una precisión de 15

decimales.

Texto: los argumentos en formato de texto deberán ir obligatoriamente entre

comillas dobles, pudiendo contener hasta 256 caracteres.

Valores Lógicos: éstos son Verdadero o Falso.

Referencias: pueden ser referencias a celdas individuales o a rangos de celdas.

Funciones Matemáticas

SUMAR.SI (rango;criterio,rango_suma)

Suma los valores de rango_suma que cumplan con el criterio indicado en la columna de rango.



SUMAR.SI.CONJUNTO (Rango_suma,rango_criterios1,criterio1, rango_criterios2,criterio2)

Suma los valores de un rango que cumplan múltiples criterios

CONTAR.SI (rango;condición)

Cuenta el número de celdas que cumplan con la condición dada.



CONTAR.SI.CONJUNTO (rango_criterios1,criterio1, rango_criterios2,criterio2)

Cuenta un rango basado en múltiples criterios.

El resultado es 2 porque solo hay dos datos de Depto. que cumplen con el criterio de ser

Blancos con Unidades >2

CONTAR (rango)

Cuenta cuantos números hay en el rango.

MAX (número1, número2)

Devuelve el valor máximo de los valores en la sección de argumento.

MIN (número1, número2)

Devuelve el valor mínimo de los valores en la sección de argumento.

ENTERO (número)



Redondea un número dado como argumento hasta el entero más próximo.

=ENTERO(4.5) es 4

REDONDEAR (número, decimales)

Redondea un número dado como argumento al número de decimales especificado.

=REDONDEAR(4.5,0) es 5

Funciones de búsqueda

BUSCAR (valor buscado, vector de comparación, vector resultado)

Devuelve un valor del vector resultado (una columna del rango) que se corresponden en

posición al valor buscado dentro del vector de comparación, que debe ser del mismo tamaño.



BUSCARV (valor buscado, matriz de comparación, indicador columna, ordenado)

Encuentra el valor buscado dentro de una vector de comparación (serie de columnas) y

devuelve el valor que se encuentra en la celda que se muestra en el indicador de columna.

Ordenado, es una indicación de que la primera columna de la matriz esta ordenada.



Nota: Si queremos que la fórmula nos devuelva un dato exacto en vez del más cercano,

debemos de agregarle a la fórmula el criterio de “falso” al final de ésta, obligando a la misma

a hallar el dato real y no una aproximación.

BUSCARH (valor buscado, matriz de comparación, indicador fila, ordenado)

Encuentra el valor buscado dentro de una matriz de comparación (serie de filas) y devuelve el

valor que se encuentra en la celda que se indica en el indicador de fila. Ordenado, es una

indicación de que la primera fila de la matriz esta ordenada.



Funciones lógicas

SI (prueba lógica, valor si es verdadero, valor si es falso)

La función SI, evalúa una condición establecida como argumento (prueba lógica), si el resultado

de ésta es verdadero, la celda presenta el segundo de los argumentos, si es falso presentará el

tercero.

Ejemplo 1. Deseamos evaluar si la calificación de un alumno es de aprobado o no aprobado.

=SI(Nota<5,”No Aprobado,”Aprobado”)

Ejemplo 2. Se compara el presupuesto vs. Real.

Y(valor lógico1, valor lógico2…, es_verdadero, es_falso)



La función Y evalúa una o más condiciones establecidas como argumento. Si todas son

verdaderas, la función devuelve el segundo argumento o el tercero en su defecto.

O (valor lógico1, valor lógico2… es_verdadero, es_falso)

La función O, evalúa una o más condiciones establecidas como argumento. Si el resultado de al

menos una de éstas es verdadero, la celda presenta el segundo de los argumentos y si es falso,

el tercero.

Ejemplo; Se evalúa si al empleado le corresponde bono anual o permiso.

Funciones de texto

IZQUIERDA (texto, número de caracteres)

Devuelve a partir de la izquierda de un texto, el número de caracteres especificados en el

argumento número de caracteres.



DERECHA (texto, número de caracteres)

Devuelve a partir de la derecha de un texto, el texto que se especifica con el argumento

número de caracteres.

EXTRAE (texto, posición inicial, no. de caracteres)

Devuelve el número de caracteres especificados un texto fuente a partir de una posición

determinada por el argumento posición inicial (cuenta de izquierda a derecha).



CONCATENAR(texto1,texto2)

Concatena o une dos textos.

Nota: la concatenación de dos textos puede hacerse con el símbolo de &, usándolo de la

siguiente manera: =”Auto”&”Rojo” resulta en AutoRojo.



Usando nombres en las fórmulas

Las celdas son identificadas por la posición que ocupan en la hoja de cálculo considerando la

columna y renglón que la conforman, es decir, si está en la columna A y en la fila 6 la celda se le

denomina A6. A las celdas también se les puede asignar un nombre para identificarlas con el

dato que contiene, como por ejemplo, TAREAS, PRECIOS, etc. Esta asignación tiene sus

ventajas:

a) Teniendo nombre una celda o un rango es más fácil averiguar el origen ya que al poner el

cursor en el rango o hacer clic en F2 al mismo tiempo que está seleccionada, mostrará los

nombres de las celdas que intervinieron en la operación.

b) Se puede localizar de manera más rápida, por ejemplo, si se busca la celda con el nombre

asignado PRECIO, sólo se tendrá que buscar por medio del comando IR A (F5) y este comando

nos llevará a la celda o rango así denominada.

c) Se pueden realizar operaciones con mayor lógica, por ejemplo, si en la celda A4 se encuentra

el PRECIO y en la celda A6 se encuentra el DESCUENTO, en vez de que la fórmula sea =A4*A6

ésta será =PRECIO*DESCUENTO.

Como asignar un nombre a una celda o rango

1. Para crear un nombre de celda seleccionamos la celda o celdas a nombrar.



2. Hacemos clic en el cuadro de nombre de la barra de fórmulas.

3. Escribimos el nombre sin espacios y luego hacemos clic en Enter.

4. Para insertar un nombre de celda en la barra de herramientas hacemos clic en al

pestaña Fórmulas > Asignar a un rango > Definir nombre.

5. Seleccionamos el nombre a insertar, en este caso es Precio_de_Venta y en la sección

que dice Se refiere a: seleccionamos el rango o celda.



Nombre.- Nombre con el cual se designará a la celda o rango.

Ámbito.- El nombre del rango podrá ser usado a nivel libro o a nivel hoja, esto quiere decir que

si seleccionamos hoja1 o el nombre de la hoja en ámbito, el nombre solo podrá ser usado como

variable en esa hoja por lo tanto si deseamos que el nombre sea usado en todo el libro

debemos seleccionar Libro en Ámbito.

Comentarios.- Se pueden poner comentarios que ayuden a tener una idea mas clara del

nombre.

Hacer referencia a: En esta sección se puede ver a que celda o rango se refiere el nombre

creado.

Insertando nombres a rangos desde la pestaña fórmulas

Si queremos insertar nombre a una lista de datos en base a sus títulos debemos hacer lo

siguiente.

Seleccionamos el rango a nombrar, clic en la pestaña Fórmulas > Crear desde selección.



En el cuadro deseleccionamos Columna Izquierda con lo que solo se utilizará los títulos.

Administrador de nombres

El Excel 2007 se incluye el administrador de nombre el cual nos permite crear uno nuevo, editar

los existentes o eliminarlos. El botón de Filtro nos permite mostrar solo aquellos nombres

según el ámbito.


Desmarcamos “Columna izquierda”

20


Formato Condicional

En Excel 2007 Microsoft agregó más opciones de formato condicional tales como escala de

colores, grupo de iconos y mas de tres condiciones para una sola celda.

Con el cuadro de dialogo “Formato Condicional” podemos aplicar un formato a una celda o

rango que cumplan con una condición definida por el usuario.

Para utilizar la herramienta “Formato Condicional” hacemos lo siguiente:

1. Seleccionar al pestaña de Inicio luego el botón de Estilos y con ello el botón de Formato

Condicional.

2. Desde el botón Formato Condicional Excel despliega las opciones de formato

condicional de “Resaltar reglas de celdas” las cuales son:


manual excel 2007 muestra

Documents