Software de Productividad 2015: enero 2016

martes, 19 de enero de 2016

Tabla Dinámica - Excel

TABLA DINÁMICA

DEFINICIÓN

Una tabla dinámica es una de las herramientas más poderosas de Excel, pero también es una de las características que más usuarios de Excel se sienten intimidados a utilizar. Si eres uno de ellos te estás perdiendo de utilizar una gran herramienta de Excel.

Las tablas dinámicas te permiten resumir y analizar fácilmente grandes cantidades de información con tan sólo arrastrar y soltar las diferentes columnas que formarán el reporte.

Una tabla dinámica sirve para resumir los datos que hay en una hoja de cálculo. Lo mejor de todo es que puedes cambiarla fácil y rápidamente para ver los datos de una manera diferente, haciendo de ésta una herramienta muy poderosa.

CREAR Y ACTUALIZAR TABLAS DINÁMICAS

Las tablas dinámicas en Excel reciben su nombre por su capacidad de cambiar dinámicamente la información agrupada con tan solo rotar las columnas o filas de la tabla. En esta ocasión veremos un ejemplo claro de cómo crearlas.

Haz clic sobre cualquier celda de la tabla de datos que se desea considerar en la nueva tabla dinámica.

Ahora selecciona el comando Tabla dinámica que se encuentra dentro del grupo Tablas de la ficha Insertar.

Se mostrará el cuadro de diálogo Crear tabla dinámica. Si es necesario podrás ajustar el rango de datos que se considerará en la tabla dinámica.

En este mismo cuadro de diálogo se puede elegir si se desea colocar la tabla dinámica en una nueva hoja de Excel o en una ya existente. Haz clic en el botón Aceptar y se creará la nueva tabla dinámica.

Excel agregará en la parte izquierda del libro la tabla dinámica y en la parte derecha la lista de campos. Esta lista de campos está dividida en dos secciones, primero la lista de todos los campos de los cuales podremos elegir y por debajo una zona a donde arrastraremos los campos que darán forma al reporte ya sea como columna, fila, valor o como un filtro.

Para completar la tabla dinámica debemos arrastrar los campos al área correspondiente. Siguiendo el ejemplo propuesto del artículo anterior, colocaré como columna el campo Producto y como fila al campo Ciudad. Finalmente como valores colocaré el campo Ventas.

De manera predeterminada Excel aplica la función SUMA a los valores y la tabla dinámica que resulta después de hacer esta configuración es la siguiente:

Utilizando una tabla dinámica fue posible crear un reporte de una manera fácil y sin la necesidad de utilizar fórmulas. Pronto veremos cómo se pueden elaborar tablas dinámicas más complejas que permitirán realizar un análisis profundo de la información.

Actualizar una tabla dinámica

Con esta breve explicación podrías comenzar a entender porque las tablas dinámicas no se actualizan automáticamente cuando cambiamos los datos de origen o cuando oprimimos la tecla F9. Para poder actualizar la información que despliega la tabla dinámica siempre debes pulsar el botón Actualizar que se encuentra dentro de la ficha Opciones que pertenece a las Herramientas de tabla dinámica.

Esto hará que Excel lea de nuevo los datos origen de la hoja de Excel y actualice la memoria caché provocando así también una actualización de la tabla dinámica. Debes tener siempre en mente este concepto para evitar pensar que una tabla dinámica se actualizará automáticamente al editar los datos fuente.

CREAR UN GRÁFICO DINÁMICO

Un gráfico dinámico es un gráfico que está basado en los datos de una tabla dinámica y que se ajusta automáticamente al aplicar cualquier filtro sobre la información. Con este tipo de gráfico podrás ayudar en la comprensión de los datos de una tabla dinámica.

Cómo crear un gráfico dinámico en Excel

Para crear un gráfico dinámico debes hacer clic sobre cualquier celda de la tabla dinámica que servirá como base del gráfico y posteriormente hacer clic sobre el comando Gráfico dinámico que se encuentra dentro del grupo Herramientas de la ficha Opciones.

Se mostrará el cuadro de diálogo Insertar gráfico de donde podrás seleccionar el tipo de gráfico que deseas utilizar.

Una vez que has seleccionado el gráfico adecuado Excel lo insertará en la hoja de trabajo. Observa cómo el gráfico se modifica al momento de aplicar algún filtro sobre la tabla dinámica:

De igual manera puedes filtrar la información utilizando los botones de filtrado que aparecen dentro del gráfico dinámico:

Para eliminar un gráfico dinámico solamente debes seleccionarlo y pulsar la tecla Suprimir.

EJEMPLO PRÁCTICO

Partiendo de una hoja de cálculo que contiene las estadísticas de ventas para una empresa ficticia, supongamos que queremos responder a la pregunta: ¿Cuánto es el total de ventas por cada vendedor? Esto puede llevar mucho tiempo porque cada uno de ellos aparece en varias filas al igual que su venta mensual. Aunque podríamos utilizar la función Subtotal todavía tendríamos un montón de datos por analizar.

Archivo inicial

Por suerte, una tabla dinámica puede hacer todas las operaciones matemáticas al instante y resumir los datos de una manera que no sólo es fácil de leer sino también, de manipular.

Para crear una tabla dinámica que responda a la pregunta, sigue estos pasos:

Paso 1:

Selecciona la tabla o celdas (incluyendo los encabezados de columna) que contienen los datos que vas a utilizar.

Paso 2:

En la ficha Insertar, haz clic en el comando Tabla dinámica.

Paso 3:

Aparecerá el cuadro de diálogo Crear tabla dinámica. Asegúrate de que la configuración sea correcta y haz clic en Aceptar.

Paso 4:

Una tabla dinámica en blanco aparecerá al lado izquierdo y la lista de campos, al derecho.

RESUMEN

TABLA DINÁMICA

CREAR Y ACTUALIZAR TABLAS DINÁMICAS

Las tablas dinámicas en Excel reciben su nombre por su capacidad de cambiar dinámicamente la información agrupada con tan solo rotar las columnas o filas de la tabla.

CREAR UN GRÁFICO DINÁMICO

RECOMENDACIONES

Puedes aplicar formatos a tus tablas dinámicas. Cambiar colores y tipos de letra, por ejemplo, para hacerlos concordar con la imagen corporativa de tu empresa o la de tus clientes.
Actualizar la tabla dinámica es un juego de niños. Una vez añadido o modificados datos de nuestra lista de origen, podremos actualizar nuestra tabla haciendo click sobre el botón “Actualizar”.
Cuando deseas comparar totales relacionados, sobre todo si tienes una lista larga de números para resumir y deseas realizar comparaciones distintas con cada número.
En el informe que mostramos, puedes ver fácilmente cómo se comparan las ventas de golf del tercer trimestre en la celda F3 con las ventas de cualquier otro deporte o trimestre, o con los totales generales.

CONCLUSIONES

Por lo general, las tablas dinámicas es algo que la gente promedio no conoce, pero si las llegar a conocer y a utilizar bien se darían cuanta que les ahorrar el tiempo en su trabajo.
Tabla dinámica sirve para obtener informes resumidos a partir de los datos que se tienen en una hoja de cálculo. Sin importar el tamaño de la base de datos, esta herramienta facilita su análisis e interpretación.

GLOSARIO DE TÉRMINOS

Poco Flexibles: Si bien hay muchas posibilidades, las Tablas Dinámicas no son tan flexibles como uno quisiera.
Procesamiento y Cálculos: Si bien los elementos y campos calculados son una herramienta muy potente, no queda tan claro (por lo menos a primera vista) de cómo operan y muchas veces pueden llevar a confusión a usuarios no tan expertos.
Dinámicas: justamente haciendo referencia a su nombre; el hecho de que sea tan fácil agregar y quitar dimensiones hacen que sea la herramienta ideal.

BIBLIOGRAFÍA O LINKOGRAFÍA

martes, 12 de enero de 2016

MANEJO DE DATOS - EXCEL

MANEJO DE DATOS

Una base datos es un conjunto de datos que ha sido organizado bajo un mismo contexto y cuya información está almacenada y lista para ser utilizada en cualquier momento. Las bases de datos pueden almacenar información sobre personas, productos, ventas o cualquier otra cosa.

Una base de datos organiza la información relacionada en tablas las cuales están compuestas por columnas y filas. Una tabla tendrá un número específico de columnas, pero tendrá cualquier número de filas.

Diseño de bases de datos en Excel

Si diseñas y organizas adecuadamente la información dentro de tu libro de Excel podrás consultar tus datos de una manera fácil y eficiente, así que en esta ocasión hablaremos sobre las mejores prácticas para crear una base de datos en Excel que te permitirán organizar y estructurar adecuadamente la información.

Crear la base de datos en Excel

Una vez que tenemos el diseño de la base de datos podemos crearla fácilmente en Excel siguiendo dos recomendaciones:

Cada tabla del diseño será una tabla de Excel.
Colocar cada tabla en su propia hoja de Excel.

El motivo para utilizar tablas de Excel es que existen múltiples beneficios de utilizar tablas entre los cuales están los filtros y el reconocimiento automático de nuevas filas y columnas. Por otro lado, la razón por la cual colocar cada tabla en su propia hoja de Excel es para facilitar el crecimiento de los datos ya que si colocas varias tablas en una misma hoja probablemente tendrás que moverlas o redefinirlas cada vez que alguna de ellas aumente de tamaño y se sobrepongan entre sí.

Consultar la base de datos en Excel

Una vez que se ha creado la base de datos en Excel y comiencen a crecer los datos, seguramente te encontrarás con la necesidad de relacionar la información de diferentes tablas para obtener datos complementarios. Por ejemplo, considera las siguientes dos tablas:

La Hoja. Ordenes contiene una tabla llamada Ordenes y la Hoja. Clientes contiene la tabla Clientes. Si al consultar la tabla Ordenes deseo conocer el Email del cliente que ha efectuado dicha compra, puedo utilizar funciones de Excel para obtener dicha información. Ya que estoy utilizando tablas de Excel, puedo hacer uso de las referencias estructuradas, por ejemplo:

=BUSCARV (Ordenes [IDCliente], Clientes, 5, FALSO)

Esta fórmula buscará el valor de la columna IDCliente, que pertenece a la tabla Ordenes, dentro de la tabla Clientes y devolverá la columna 5 de esta última tabla. Observa el resultado de la fórmula:

El resultado de la fórmula es correcto ya que la orden número 1 fue hecha por el cliente con un IDCliente igual a 5 y que corresponde a Luis cuyo Email es el mostrado en la celda F2. De esta manera puedes relacionar la información de las diferentes tablas en tu base de datos en Excel, ya sea utilizando la función BUSCARV o la combinación de funciones INDICE/COINCIDIR ya que ambas nos permite crear fórmulas para buscar datos.

ORDENAMIENTO

Ordenar los datos es una parte esencial del análisis de datos. Puede que desee poner una lista de nombres en orden alfabético, compilar una lista de niveles de inventario de productos de mayor a menor u ordenar filas por colores o por iconos. Ordenar los datos ayuda a verlos y a comprenderlos mejor, así como a organizarlos y encontrarlos más fácilmente y a tomar decisiones más eficaces.

Al pulsar el botón de filtro en el encabezado de una columna veremos algunas opciones de ordenación de datos. Al aplicar un criterio de ordenación a una tabla de Excel las filas se reorganizarán de acuerdo al contenido de la columna por la cual estamos ordenando. Si la columna contiene datos de tipo texto, entonces podremos ordenar los valores de A a Z (alfabéticamente) o de Z a A.

Por el contrario, si la columna contiene datos numéricos, entonces podremos ordenar de menor a mayor o también podremos hacerlo de mayor a menor. En el caso de que tengamos fechas podremos ordenar de más antiguo a más reciente o viceversa.

Otra opción de ordenación que nos da Excel es la de ordenar por color y eso hará que las celdas se ordenen de acuerdo al color de fondo de las celdas. Esta opción es relevante solamente cuando hemos aplicado algún formato condicional a las celdas para modificar su color de fondo o el color de la fuente.

Múltiples criterios de ordenación

Es posible ordenar una tabla indicando diferentes criterios de ordenación. Por ejemplo, es posible ordenar los datos primero por fecha, después por país y posteriormente por región. Para lograr este tipo de ordenación haremos uso del comando Inicio > Ordenar y filtrar > Orden personalizado lo cual mostrará el siguiente cuadro de diálogo:

La parte superior del cuadro de diálogo Ordenar contiene botones que nos permiten establecer múltiples niveles de ordenación. A continuación explico cada uno de esos botones:

Agregar nivel: Nos permite agregar un nuevo nivel de ordenación.
Eliminar nivel: Si ya no deseamos que se aplique un criterio de ordenación podemos eliminarlo de la lista.
Copiar nivel: Hace una copia del nivel seleccionado.
Flechas arriba y abajo: Nos permiten mover los niveles de ordenación hacia arriba o hacia abajo para establecer un orden preferido.
Opciones: Con este botón podemos indicar si la ordenación de datos va a distinguir entre mayúsculas y minúsculas.

Normalmente Excel ordena los datos numérica o alfabéticamente, pero pueden existir ocasiones en las que deseemos ordenar por meses o días de la semana.

Dentro del cuadro Opciones de ordenación existe esa posibilidad, desplegando el cuadro combinado Primer criterio de ordenación: podemos escoger qué tipo de ordenación queremos.

Por defecto está seleccionada Normal (que es la correspondiente a orden numérico y alfanumérico).

Una lista se puede ordenar de la siguiente manera:

Cronológicamente-----Fechas

Alfabéticamente-------Rótulos

Numéricamente-------Valores

FILTRO AVANZADO

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, acceder al menú Datos - Filtro - Filtro Avanzado....

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.

VALIDACIÓN DE DATOS

La validación de datos en Excel o lista desplegable en Excel como algunos le llaman permite restringir el tipo de dato que se debe ingresar en una celda, dichos datos son aceptados o rechazados con base a una configuración previa realizada por el usuario.

La validación de datos en Excel es útil cuando se requiere evitar errores de escritura o valores fuera de un rango determinado, esto se hace comparando el dato ingresado con determinada condición, que puede ser:

El dato ingresado debe encontrarse incluido en una lista de Excel
El dato ingresado debe ser un número
El dato ingresado debe ser una fecha y encontrarse en determinado rango
La cantidad de caracteres ingresados no debe superar determinada cantidad.

En el caso que no se cumpla la condición que se implementó en la validación de datos en Excel se puede configurar un mensaje para que presente el error, al mismo tiempo se puede configurar un mensaje para que aparezca en el momento en el que se está posicionado en la celda, a este se le llama mensaje de entrada.

Pasos para implementar la validación de datos en Excel:

Establezca el tipo de validación que aplicará, si requiere una lista debe crearla y tenerla lista para el siguiente:

Haga Clic en la celda que desea aplicar la validación de datos en Excel
Ir a Menú Datos y seleccionar la opción “Validación de datos”
Configure el criterio de validación
Configure el mensaje de entrada y mensaje de error
Haga Clic en botón “Aceptar”.

EJEMPLO PRACTICO

RESUMEN

MANEJO DE DATOS

ORDENAMIENTO

FILTRO AVANZADO

VALIDACIÓN DE DATOS

RECOMENDACIONES

Las capacidades de Excel han sido hasta ahora suficientes para ti, entonces te recomiendo seguir los pasos descritos en este artículo para hacer uso de las mejores prácticas en el diseño y construcción de bases de datos en Excel y hacer más eficiente el trabajo y organización de la información.
Nos ayuda a tener un ordenamiento mas seguro y un suceso mas complejo a la información.

CONCLUSIONES

La realidad es que hay millones de usuarios de Excel que utilizamos la herramienta como un repositorio de datos y aunque no es formalmente un sistema de gestión de bases de datos, es posible utilizarlo para guardar información útil.
A menudo nosotros recurrimos a la creación de listas, pero nosotros pensamos que estás con el simple hecho de introducir los datos en las columnas y filas ya tendremos una súper maravillosa lista, pero la realidad es que no es así, porque por ejemplo Excel nos permite no solo hacer una lista sino modificarla a nuestro antojo, filtrarla, ordenarla como nosotros queramos y todo para que tengamos un trabajo más placentero.

GLOSARIO DE TÉRMINOS

Ordenar: Reordenar las filas de datos en un orden. Este puede ser alfabético, numérico, o basado en una lista personalizada.

BIBLIOGRÁFICA O LINKOGRÁFIA

miércoles, 6 de enero de 2016

Gráfico - Excel

GRÁFICO

Un gráfico es una representación por medio de líneas y aquello perteneciente o relativo a la escritura y a la imprenta. Se denomina gráfico a una representación visual de una serie de datos, representación que tiene como finalidad dar una rápida noción acerca de volúmenes, cantidades y porcentajes, estableciendo comparaciones. Un gráfico también puede aludir a las imágenes que se muestran en una computadora, ya sean fotos o dibujos. En cualquier caso, un gráfico remitirá a una expresión visual distinta de los caracteres del alfabeto o de los números.

TIPOS DE GRÁFICOS

Gráficos de columna. Este tipo de gráfico hace un énfasis especial en las variaciones de los datos a través del tiempo. Las categorías de datos aparecerán en el eje horizontal y los valores en el eje vertical. Frecuentemente se compara este tipo de gráfico con los gráficos de barra, donde la diferencia principal es que en los gráficos de barra las categorías aparecen en el eje vertical.

Gráficos de línea. Un gráfico de línea muestra las relaciones de los cambios en los datos en un período de tiempo. Este gráfico es comparado con los gráficos de área, pero los gráficos de línea hacen un énfasis especial en las tendencias de los datos más que en las cantidades de cambio como lo hacen los gráficos de área.

Gráficos circulares. También conocidos como gráficos de pie (en inglés) o gráficos de pastel. Estos gráficos pueden contener una sola serie de datos ya que muestran los porcentajes de cada una de las partes respecto al total.

OPCIONES DE GRÁFICOS

- Título Del Gráfico: Texto descriptivo del gráfico que se coloca en la parte superior.

- Ejes: Un eje es la línea que sirve como referencia de medida.

- Eje horizontal: también datos conocidos como el eje x, es la parte horizontal de la gráfica.

- Eje vertical: también conocido como el eje y, es la parte vertical de la gráfica.

- Líneas De División: Son líneas opcionales que extienden los valores de los ejes de manera que faciliten su lectura e interpretación.

- Tabla De Datos: Una tabla de datos muestra los valores que se presentan en el gráfico dentro de una cuadrícula en la parte inferior del gráfico. Una tabla de datos también puede incluir las claves de leyenda.

- Rótulos De Datos: Son los títulos de cada eje de un gráfico. Son muy útiles para que podamos ver a simple vista lo que se representa en cada eje.

- Leyenda: Identifica el color que representa a cada serie de datos. Para muchos gráficos es crucial pero para otros, puede que no sea necesario y se puede eliminar.

EJEMPLO PRACTICO

GRÁFICO DE COLUMNAS

El gráfico de columnas es uno de los tipos de gráficos en Excel más utilizados y despliega una columna para cada uno de los datos. La altura de la columna dependerá del valor numérico asociado y la escala de dichos valores se mostrará en el eje vertical.

En el ejemplo anterior se muestra el gráfico de columnas para una sola serie de datos y en la siguiente imagen podrás observar un gráfico de columnas para dos series de datos donde cada una de ellas tiene un color diferente:

El gráfico de columnas es útil para comparar valores entre diferentes categorías y Excel nos permite elegir entre 7 variantes que se encuentran en el menú mostrado en Insertar > Gráficos > Gráfico de columnas.

Una de las variantes mostradas es el gráfico de columna 100% apilada que mostrará las columnas superpuestas dejándonos ver el porcentaje de cada una de las categorías respecto al 100%.

2. RESUMEN

GRÁFICO

TIPOS DE GRÁFICOS

Gráficos de línea. Un gráfico de línea muestra las relaciones de los cambios en los datos en un período de tiempo.

Gráficos circulares. También conocidos como gráficos de pie (en inglés) o gráficos de pastel.

OPCIONES DE GRÁFICOS

Título Del Gráfico: Texto descriptivo del gráfico que se coloca en la parte superior.
Ejes: Un eje es la línea que sirve como referencia de medida. Dos principales ejes: Eje horizontal y Eje vertical.
Líneas De División: Son líneas opcionales que extienden los valores de los ejes de manera que faciliten su lectura e interpretación.
Tabla De Datos: Una tabla de datos muestra los valores que se presentan en el gráfico dentro de una cuadrícula en la parte inferior del gráfico.
Rótulos De Datos: Son los títulos de cada eje de un gráfico. Son muy útiles para que podamos ver a simple vista lo que se representa en cada eje.
Leyenda: Identifica el color que representa a cada serie de datos. Para muchos gráficos es crucial pero para otros, puede que no sea necesario y se puede eliminar.

3. RECOMENDACIONES

No poner fondos oscuros al Área del gráfico. Si es posible, transparentes o blancos o como mucho, degradados muy suaves.
Utilizar líneas de división horizontales sólo cuando se utiliza el Eje vertical y a la Serie no le hemos añadido etiquetas con los valores. Si se utilizan, deben ser de color gris claro u otro muy suave.
No utilizar más de 5 series de forma simultánea, puesto que de otra forma generarían demasiada confusión al usuario.
En gráficos de líneas, utilizar la opción "líneas suavizadas" y evitar los Marcadores.
Evitarlas si sólo hay una serie en el gráfico o si no lo consideramos necesario.
Los nombres que aparecen en el Eje horizontal (normalmente representan tiempo: días, meses, trimestres, años, etc.) deben estar (si es posible) en horizontal, NO en vertical o diagonal.
Si hay mucha variación entre los valores de dos series, se deberían utilizar escalas logarítmicas o bien utilizar dos ejes (el eje primario y el secundario).
No utilizar decimales en la escala, excepto si se manejan datos muy pequeños y cuya precisión en el análisis es vital.

4. CONCLUSIONES

La representación de datos de forma gráfica ofrece mensajes más claros donde las conclusiones son fáciles de entender.
Usando Excel se pueden obtener gráficos decentes (pero hay que modificar lo que nos sale por defecto).
Los gráficos sirven para dar una imagen más amplia sobre los datos, para visualizarlos y comprenderlos fácilmente, pero también es importante y básico saber los pasos para crear una gráfica, y hacer esta de acuerdo a las facilidades que ofrece.
Hay muchas maneras de representar un gráfico en Excel, ya seas en forma de columnas, de barras, circular, lineal, de anillos, etc.

5. GLOSARIO DE TÉRMINOS

Hoja de gráfico: Cuando llevamos un gráfico a una hoja separada del libro de Excel.
Iconos de gráfico: Botones que aparecen cuando se crea una gráfica. Sirven para realizar cambios en el formato o el los datos.
Insertar gráfico: Incluir un gráfico en la hoja en que tienes los datos.
Controlador de tamaño: Cambia el tamaño de los gráficos y sus componentes.
Leyenda de gráfico: Área de referencia donde se muestra con diferentes colores a cada serie de datos.
Serie de datos: Conjunto de valores numéricos relacionados entre sí.
Puntos de datos: Éstos son los valores de las celdas de las hojas de cálculo, que se muestran como barras, líneas, columnas, sectores circulares u otras formas en el gráfico.

6. BIBLIOGRAFIA O LINKOGRAFIA