Herramienta Buscar Objetivo

En ocasiones necesitamos que una fórmula tenga a un determinado resultado, por lo que optamos por cambiar variable a la fórmula a manera de prueba y error para llegar al resultado deseado.
Para ello Excel dispone de una opción en el menú Herramientas llamada Buscar objetivo, con la que es posible cambiar el valor de una celda de entrada específica para que una fórmula dependiente de dicha celda consiga el resultado deseado.

Gráfico Dinámico a partir de una Tabla Dinámica

Un gráfico dinámico es la representación gráfica de los datos de la tabla dinámica. Los datos que se muestran son aquellos que actualmente se presentan en la tabla dinámica, veamos un ejemplo.

Calculos Personalizados en Tablas Dinámicas

Qué son los cálculos personalizados
Los cálculos personalizados son métodos para resumir valores del área de datos de un informe de tabla dinámica utilizando los valores de otras celdas del área de datos.

Autofiltro en Excel 2003

En Excel existen dos maneras de filtrar una lista:
Auto Filtro: consiste en seleccionar alguno de los datos deplegados en las modalidades de:
Todas: presenta todas las filas.
Las 10 más…: Presentar todas las filas situadas entre los límites superior e inferior que especifique, ya sea por elemento o porcentaje; por ejemplo, los importes hasta el 10% de las ventas.

Agrupar y Resumir una Tablas Dinamicas

Cuando creamos una tabla dinámica, el resumen automático realizado por la tabla dinámica es una suma, si éste es un campo numérico, si no es numérico entonces el cálculo será una cuenta.
Hay otras formas de resumir un campo de datos y se detallan a continuación:

Opciones de la Tabla Dinamica

Las tablas dinámicas en Excel ofrecen varias opciones que nos ayudan a determinar como queremos presentar la operación. Para poder acceder a ellas hacemos clic en el botón de opciones en el paso 3 de 3 o haciendo clic con el botón derecho del Mouse y con el puntero en cualquier parte de la tabla dinámica, seleccionamos opciones.

Elementos de una Tabla Dinamica

Veamos ahora cuales son los elementos de una tabla dinámica.

Página: Los botones que se colocan en esta área son los primeros que se filtran.
Fila: Son los elementos que queremos colocar como filas en la tabla dinámica, que suelen ser también las categorías, por ejemplo Ciudad, nombre del cliente, etc.
Columna: Como en el caso anterior, colocamos los datos que forman parte de la columna de la tabla, puede ser el mes, la sucursal, etc.
Datos: En este caso se colocan los datos que se organizan y se resumen, este tipo de datos suelen ser numéricos o susceptibles de hacer operaciones aritméticas con ellos, en el caso de utilizar datos no numéricos por defecto contará los datos, esto lo veremos mas adelante.
En el asistente para tablas y gráficos dinámicos podemos tomar los diferentes botones de los campos y colocarlos el área que queramos.
Para hacer nuestra tabla dinámica podemos utilizar el Asistente para tablas y gráficos dinámicos o la lista de campos de tabla dinámica.




Campo de página.- Un campo de página es un cuadro de lista desplegable situado en la parte superior de la tabla dinámica, que permite filtrar los datos que se muestran en la tabla dinámica permitiendo seleccionar páginas individuales. Una tabla dinámica puede no tener campos de página, tener uno sólo o muchos.

Creando Una Tabla Dinamica

Ahora vamos a construir una Tabla Dinámica a partir de Datos almacenados en otro libro de Excel
Para la ejecución de la tabla dinámica necesitamos emplear el asistente para tablas y gráficos dinámicos.
1. Clic en Datos > Informe de Tablas y gráficos dinámicos.

En este paso se nos pide seleccionar entre las opciones de la fuente de los datos a utilizar y que tipo de informe deseamos hacer seleccionamos Lista o base de datos de Microsoft Office Excel.
Nota: Primero debe de estar abierto el libro donde se encuentran los datos a consolidar.

2. Seleccionamos el rango de datos a utilizar por medio del Asistente para tablas dinámicas.

3. En este paso existe la opción de colocar la tabla en una hoja nueva, en la hoja actual o en otra creada.

Concluido este paso pulsar Diseño para presentar el asistente en el modo de diseño como consecuencia, surge una nueva barra de herramientas, que contiene una serie de botones con los nombres de los encabezados de columna de datos.

Introduccion a las Tablas Dinamicas

Una tabla dinámica es un reportador robusto el cual nos permiten resumir grandes volúmenes de datos y presentarlos de una manera elegante y ordenada. Con la tabla dinámica podemos hacer operaciones y presentaciones sin arriesgar la base de datos fuente.

Consolidación de Datos

La consolidación de datos se refiere a hacer operaciones con más de una hoja al mismo tiempo, esto puede hacerse vía vínculos entre hojas o utilizando el comando de consolidación para obtener una sola hoja a partir de varias.

Filtro Avanzado

Con el filtro avanzado Excel permite que filtremos datos con muchos más criterios que auto filtro o el filtro personalizado. Por ejemplo, con el filtro avanzado uno puede filtrar datos combinando criterios de las columnas que conforman nuestra base de datos, veamos un ejemplo:

Autofiltro Personalizado

Con esta opción se gana mas flexibilidad filtrando pues se toma en consideración mas un criterio. Con ello podemos filtrar valores con el criterio igual a, valores entre un rango, valores discrecionales (uno u otro) y aproximaciones usando los símbolos ? y *.

Los 10 más

La Herramienta los 10 mas nos sirve para crear filtros como su nombre lo indica los 10 mas altos, los 10 mas bajo o el porcentaje que represente el 10%.
Pero el concepto de los "10 mas" solo es genérico pues realmente podemos poner un número entero más alto, por ejemplo, podemos pedir que se desplieguen los 25 deudores más altos o los 11 clientes más bajos en valor y en porcentaje.

Listas de datos en Excel 2003

Una lista es una colección de información organizada que consiste en filas y columnas las cuales pueden ser valores, texto o la combinación de ambos. Podemos ver una lista también como una base de datos plasmada en una hoja de cálculo.

---

Ayuda cuando se repiten datos de entrada en una lista
Auto completado: Cuando empezamos a teclear en una celda, Excel escanéa de arriba hacia abajo de manera automática buscando si antes ya se había tecleado el mismo dato, de haberse hecho esto, Excel nos ofrece la opción hallada para poder darle entrada o seguir tecleando.

Validación de datos

La herramienta validación de datos es una aplicación para evaluar si el dato a introducir en una celda cumple con el criterios establecidos por el usuario para dicha celda.

1. Seleccionamos la celda o rango a validar.
2. En la barra de menú hacemos clic en Datos >Validación.
3. En la pestaña de Configuración hacemos clic el cuadro de lista y seleccionamos una de las opciones.

Mensaje entrante.- En ocasiones se necesita dar un mensaje al usuario informándole el tipo de dato que puede insertar en la celda, para ello vamos a la pestaña de Mensaje entrante donde pondremos el Título y el Mensaje de entrada.
Mensaje de salida.- Como en el caso del mensaje de entrada, existe una pestaña de Mensaje de error, en la cual puede ser configurado para que arroje un mensaje de error cuando el usuario intente entrar un dato no valido para dicha celda.

Validación de datos con otras hojas del mismo libro
En el video vamos a ver como podemos hacer la validación de datos con listas tomando los datos de otra hoja del mismo libro, para ello necesitamos nombrar el rango de datos, en nuestro caso lo llamaremos ListaDatos .

Nota: No es posible la validación con origen de libros externos.
Descargar Videos
Validación de Datos 1
Validación de Datos 2

Formato Condicional Basado en una Fórmula

En Excel 2003 podemos aplicar un formato condicional a una celda o rango de celdas basandonos en el resultado de otra celda o rango de celdas, esto es posible utilizando una fórmula la cual debe dar un resultado, verdadero.

Para establecer un formato condicional basado en una fórmula tenemos que hacer los siguiente, despues de seleccionar la celda a las cual se le aplicará el formato condicional nos vamos a Formato > Formato condicional para desplegar la herramienta, en el cuadro de condición seleccionamos Formula, en el siguiente cuadro podemos especificar una fórmula, ya sea seleccionando una celda con una fórmula o tecleando la fórmula directamente en el cuadro de texto. La fórmula tecleada en el cuadro de Formula debe de empezar con el signo de igual (=).

---

Nota: Es importante que la fórmula introducida retorne Verdadero o Falso. Si la fórmula evaluada es verdadera la condición es satisfecha y el formato condicional es aplicado, en caso contrario sería de resultar una solución falsa.

Formato Condicional Excel 2003

Como usar el Formato Condicional
En Excel 2003 usando 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. Veamos un ejemplo.
Deseamos poner color a aquellas celdas de la columna unidades, que sean mayores a 5.

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

• Seleccionar la celda o rango de celdas.
• Desde el menú seleccionar Formato>Formato Condicional. Excel despliega el cuadro de formato condicional.

(ver figura).

Descargar Videos

Formato Condicional

Rangos con nombre en Excel 2003

Usando nombres en las fórmulas
En Excel 2003 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:

Funciones de Texto en Excel

Las funciones de Texto en software Microsoft Excel 2003 son aquellas que nos permiten trabajar con texto ingresado en la celda o con texto directamente aplicado dentro de la fórmula.

En esta ocasión trabajaremos con fórmulas para obtener los caracteres de un texto y para unir otros.

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.

Funciones Lógicas en Excel 2003

Las funciones lógicas en Excel 2003 son aquellas fórmulas que evaluan un arguemento y en función a un resultado que debe de ser verdadero o falso realizan una acción, estas formulas son la versión en hoja de cálculo del IF en Visual Basic Application o VBA, el lenguaje para programar las macros.


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","si")
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 si es verdadero y 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.

Las Funciones BUSCAR( ) y BUSCARV( )

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.

COINCIDIR (Valor a buscar, Matriz, Tipo de coincidencia)
Devuelve la posición relativa de un elemento que coincide con un valor determinado en una matriz.



Los tipos de coincidencias pueden ser:


1. 1, devuelve el valor igual o menor más cercano al valor buscado cuando no encuentra el exacto. Para que esta fórmula funcione es necesario que la matriz esté ordenada.
2. 0, devuelve el primer valor exacto buscado.
3. -1, devuelve el valor igual o mayor más cercano al valor buscado cuando no encuentra el exacto, para que esta fórmula funcione es necesario que la matriz esté ordenada. Cuando este valor se omite por defecto es 0

Descargar Videos
BUSCAR( )
BUSCARV( )
BUSCAR.SI( )

Funciones Matemáticas en Excel

En esta ocasión veremos el funcionamiento y estructura de algunas de las fórmulas matemáticas en Excel 2003, veremos el funcionamiento de la fórmula de SUMAR con la opción de criterios.

SUMAR.SI (rango;criterio,rango_suma)

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

Nota: El criterio_rango puede ser un vínculo a una celda con el criterio a evaluar.

CONTAR.SI (rango;condición)
Cuenta el número de celdas no vacías que cumplan con la condición dada.

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

Descargar Video 1

Descargar Video 2

Operadores Aritméticos

Los operadores son una parte importante en las fórmulas del software Excel pues con ellos podemos establecer criterios de cálculo al momento de hacer operaciones.

La sintaxis de una función

Las funciones tienen reglas de sintaxis para un correcto funcionamiento, veamos el siguiente ejemplo:

Qué es una función en Excel

Una función en Excel 2003 es una fórmula escrita y preparada para realizar cálculos y simplificar el uso de fórmulas extensas que devolverán valores de manera automática. Las funciones tiene un nombre propio y existen multitud de funciones en Excel 2003. Existen funciones que realizan cálculos financieros, estadísticos, matemáticos, entre otras que permiten ahorrar trabajo y tiempo en la escritura de una fórmula. Las funciones pueden ser introducidas mediante el teclado o por el asistente de funciones.