Tablas Dinámicas | Capitulo 2

Para esta ocasión, veremos una utilidad de la tabla dinámica: Buscar el porcentaje que representa un valor sobre un valor total.

Seguiremos  trabajando con nuestro archivo del Capitulo 1. Descárguelo aquí:

Es decir, para nuestro informe dinámico donde tenemos en el campo de fila la lista de las líneas y en campo de valor la cantidad vendida, vamos a hallar el  porcentaje que representa esta venta frente al valor total.

CARACTERISTICAS DEL CAMPO DATO  DE LA TABLA DINAMICA

Etiqueta "Campo Activo"

Con el archivo que acaba de descargar, realice una tabla dinámica llevando a los campos de fila, el campo "Nombre línea" y el campo "precio" al área Valor dos veces

 

•  Ubíquese en cualquier celda del campo de Valor, ejemplo "B6"
  

•  Haga clic a la pestaña de la pestaña "Opciones"

•  Haga clic en la etiqueta “Campo Activo” 

• Haga clic a la opción "Configuración de Campo"

• Haga clic a la pestaña “Mostrar valores como”
  

• Seleccione la opción mostrar valores como: “% del total”

• Haga clic en Aceptar

• Podrá verificar que el campo Precio cambiara por el % que representa ese valor sobre las ventas  totales. 

• Para una mejor presentación ordene por valor descendente.

Si te gustó, suscríbete al Feed RSS de Ideas de Excel y recibe nuestras actualizaciones

Leer más »

Tablas Dinámicas | Capitulo 1

A partir de hoy, estaremos publicando una serie continua de posts referentes a las tablas dinámicas.  Éste será nuestro primer capítulo. Si tiene dudas, déjenos un comentario.

DEFINICION:  Las tablas dinámicas permiten realizar un resumen de datos agrupados, que puntualizan en forma concreta datos de una información. Es decir con una Tabla Dinámica se pueden hacer resúmenes de una base de datos, utilizándose para promediar, o totalizar datos.

Invoque el asistente de Tablas Dinámicas.

1. Haga clic en la pestaña Insertar

2. Ubique la etiqueta Tabla

3. Haga clic en la opción Tablas Dinámicas

DEFINICION | Base de datos: Una base de datos es un conjunto de información estructurada en registros y almacenada en un soporte electrónico legible desde un ordenador.
Cada registro constituye una unidad autónoma de información que puede estar a su vez estructurada en diferentes campos o tipos de datos que se recogen en dicha base de datos.

COMPONENTES DE UNA TABLA DINÁMICA

Una tabla dinámica contiene DIMENSIONES, que corresponden a los campos pertenecientes a la BD, cada uno de los cuales resume múltiples filas de información de los datos originales. A su vez, resume los datos utilizando las funciones matemáticas que se especifiquen como SUMA, CUENTA, PROMEDIO, etc. Se podrían incluir automáticamente subtotales y totales generales o utilizar sus propias fórmulas agregando campos y elementos calculados.

PASOS PARA CREAR UNA TABLA DINÁMICA

Para crear una tabla dinámica debemos partir de una base de datos bien sea en un programa de almacenamiento de Información como Access, SQL entre otros; o bien sea de un listado en Excel, para nuestro ejemplo utilizaremos un listado con datos ficticio de un sistema de Facturación (este listado guárdelo en su equipo, nos servirá para los siguientes capítulos.)

Descárguelo desde el siguiente botón

Ubíquese en un lugar de la base de datos.

Invoque el asistente de Tablas Dinámicas

1. Haga clic en la pestaña Insertar

2. Ubique la etiqueta Tablas.

3. Haga clic en la opción Tablas Dinámicas

4. Automáticamente Excel selecciona el rango de datos

5. Clic en Aceptar

Aparecerá en una hoja diferente,  la tabla dinámica lista para que usted inicie el proceso de consulta dinámica. Para ello arrastre cada uno de los datos que desea analizar y suelte el dato solo en el campo deseado. Para nuestro ejemplo analicemos las líneas de productos que más se vendieron por cantidad.
Para ello haga lo siguiente.

1. Ubique en la lista de campos de la tabla dinámica, el campo "Nombre Línea", arrástrelo hasta el área rótulos de fila.

2. Ubique en la lista de campos de la tabla dinámica, el campo cantidad y arrástrelo hasta el área rótulos de valores

Verá como ya tiene un informe rápido y dinámico.

Adicionalmente Excel agrega un pestaña nueva “Herramientas de Tabla dinámica” y ésta a su vez agrega dos pestañas más: “Opciones” y “Diseño” que permiten la administración, configuración y personalización de las tablas dinámicas

NOTA

Si te gustó, suscríbete al Feed RSS de Ideas de Excel y recibe nuestras actualizaciones

Leer más »

Gráficos intercambiables en una misma hoja

Para esta ocasión, veremos varias funciones avanzadas.

Digitaremos en una hoja unos datos para generar un gráfico pero que a su vez este tenga la posibilidad de seleccionar el tipo de gráfico.

1. Abra un libro nuevo en Excel.

2. Cree una lista con los siguientes datos:

3. Cree el primer gráfico de tipo Columnas Agrupadas.

4. Seleccione el gráfico y córtelo (CTRL + X)

5. Péguelo en la Hoja2 (CTRL + V). Asegúrese que el área del gráfico coincida exactamente con un rango de celdas, para este caso A1:F16.

6. Copie el gráfico y péguelo en otro rango de la Hoja2, por ejemplo A18:F33.

7. Repita el mismo procedimiento

NOTA: En la Hoja2 debe tener 3 gráficos, Por favor cambie el estilo de gráficos: El primero en columnas, el segundo en líneas y el tercero en áreas. Para cambiar el tipo de gráfico: Haga clic en el gráfico y haga clic en cambiar tipo de gráfico

8. Cree un rango para cada una de las áreas de los gráficos así:

Columnas = Hoja2!$A$1:$F$16
Líneas = Hoja2!$A$18:$F$33
Area = Hoja2!$A$35:$F$50

9. En la Hoja2 cree una lista en el rango I1:I3 escribiendo en cada celda: Columnas, Lineas, Areas. Esta lista nos servirá más adelante para crear la lista desplegable.

10. Haga clic en la Hoja1, ubíquese en la celda H1  y cópiela (Ctrl+C). Ahora ubíquese en la celda D1 y presione la tecla MAYUSCULA, haga clic en la Ficha "Inicio", despliegue la opción "Pegar" - "Pegar como Imagen" y posteriormente seleccione la opción "Pegar vínculos de imagen"

11. Aparecerá una imagen vacía y en la barra de fórmulas verá la referencia a la celda que hemos copiado ($H$1)

12. Reemplace $H$1 en la barra de las fórmulas por el nombre Columnas que había creado sobre el rango que contiene ese tipo de gráfico en la Hoja2. 

Note que el gráfico de columnas aparece en la Hoja1. Si cambia la referencia Columnas a Líneas, verá aparecer el gráfico tipo Líneas.

Ahora vamos a incluir un método para elegir el tipo de gráfico de una lista desplegable.

13. Cree una lista desplegable usando el control  "Cuadro combinado" de la Ficha "Programador" (no controles ActiveX).

14. Dibuje el control y ubíquese sobre la celda  A12.

El rango de entrada es de la Hoja2 I1:I3

Vincule con la celda A12.

15. Cree el nombre "Tipo3" que contenga la siguiente fórmula:
=ELEGIR(Hoja1!$A$12;Columnas;Líneas;Areas)

16. Seleccione la celda D1 y en la barra de Fórmulas escriba =Tipo3

A partir de este momento el tipo de gráfico cambiará de acuerdo a la selección que usted haga en la lista desplegable

Puede descargar el archivo del ejercicio en este link (archivo comprimido)



Si te gustó, suscríbete al Feed RSS de Ideas de Excel y recibe nuestras actualizaciones

Leer más »

Validación de datos

La validación de datos en Excel permite definir el dato que desea registrar en una celda y limitar en algunos casos a algún usuario a registrar un valor NO valido, esto permite que nuestros listados o informes sean más
genéricos y con menos errores.

Veamos un ejemplo claro de Validación.

Para nuestro ejemplo, tenemos un cuadro en Excel que es necesario sea manipulado por varios usuarios y que cada uno de ellos digite la información requerida para analizar las cantidades vendidas de acuerdo al nombre del vendedor y al día de la semana.

Este es el libro que será diligenciado por varios usuarios:

1. Ubíquese en la celda E1 e iniciamos el registro de los nombres de los vendedores tipo listado y los días de la semana.

2. Ubíquese en la celda A3.

3. Haga clic en la etiqueta "Datos" y posteriormente a la opción "Validación de datos".

4. En la pestaña "Configuración" y en la opción "Permitir valor", haga clic en "Lista". Inmediatamente se activará un campo ORIGEN, donde seleccionaremos nuestra lista de nombre vendedores.

5. Seleccione la lista de nombres

6. Para retornar al cuadro de dialogo de Validación de datos haga clic en el botón

7. Haga clic a la pestaña "Mensaje de error"

8. Active la casilla de verificación:

9. Seleccione el estilo de mensaje que usted desee.

10. Escriba un título al mensaje ejemplo:

11. Si lo desea puede escribir aún más detallado el mensaje de error en el espacio habilitado “Mensaje de error"

12. Haga clic en aceptar.

13. En nuestro libro aparecerá una pestaña para seleccionar.

NOTA: Se recomienda copiar hacia abajo la Validación de datos

14. Para validar los mensajes de error escriba un nombre de vendedor que no se encuentre en la lista ejemplo: Natalia Arias

Note que inmediatamente aparecerá un mensaje de error, este fue el mensaje que se configuro anteriormente, asi el usuario se verá obligado a escoger de la lista su nombre sin necesidad de dañar nuestro formato.

Repita el mismo procedimiento para el Día.

Si desea conocer más acerca de validación de datos visite Microsoft Office

Si te gustó, suscríbete al Feed RSS de Ideas de Excel y recibe nuestras actualizaciones

Leer más »

Función BUSCARV

Definición La función BUSCARV permite hallar un valor en una primera columna de una matriz (o rango de datos).

La V significa vertical, asi que entonces la función BUSCARV significa Buscar valor Vertical.

SINTAXIS:  =BUSCARV(valor_buscado;matriz_buscar_en;indicador_columnas;ordenado)


valor_buscado es el valor que se va a buscar en la primera columna de la matriz de tabla. Puede ser un valor o una referencia.

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 .

indicador_columnas: Este campo hace referencia al 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:

Ordenado: 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.

• 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.

VEAMOS UN EJEMPLO PRÁCTICO

Abra una hoja de Excel en blanco donde según el nombre de vendedor nos devolverá la ciudad del vendedor dependiendo de una lista (matriz o rango de datos)

Lo que pretende la función BUSCARV es que al momento de un usuario digitar en la HOJA2 en la celda A1 el nombre del vendedor, automáticamente en la celda B2 aparezca la Ciudad que depende del nombre.

1. Haga clic a la etiqueta HOJA2.

2. Ubíquese Celda B1

3. Haga clic sobre el botón insertar función de Excel,  en categorías de funciones, seleccione "búsqueda y referencia". En nombre de la función, selecciona BUSCARV

En el argumento valor_buscado, selecciona la celda A1.

En el argumento matriz_buscar_en, selecciona el rango Hoja1!A1:B10

En el argumento indicador_columnas, escribe 2 (es decir, la segunda columna de la matriz)

En el argumento ordenado, introduzca FALSO

Pulse Enter

Escriba en la HOJA2 el nombre del vendedor que desea buscar e inmediatamente devolverá el valor de la ciudad

Usted puede añadir complejidad a la fórmula de acuerdo al tamaño de matriz: agregar selección por listas, etc


Si te gustó, suscríbete al Feed RSS de Ideas de Excel y recibe nuestras actualizaciones

Leer más »