Excel avanzado: gráficas

Una de las funciones más apropiadas para realizar con una hoja electrónica es la graficación, pues normalmente está asociada a series de datos que pueden representarse de manera muy adeuada de una forma tabular

 

EJEMPLO

Se desea presentar una gráfica relacionada a las ventas mensuales de los empleados de una compañía. En una hoja electrónica los datos se representarían de la siguiente manera:

 En este ejemplo podemos ver que cada serie estará constituida por los datos de cada vendedor (renglones).

Para insertar un gráfico seleccionamos el menú Insertar donde encontraremos las opciones de gráficos que nos permitirán insertar diferentes tipos de gráficos. Debemos tener seleccionado el rango de datos que queremos graficar y seleccionar el tipo de gráfico deseado. En nuestro ejemplo seleccionamos los datos y escogemos el tipo de barra con lo que Excel nos presenta diferentes tipos de gráficos de barras para seleccionar el más adecuado:

 

Una vez seleccionado nuestro tipo (Columna en 2 - D) Excel nos muestra el gráfico:

Si observamos el panel nos daremos cuenta que estamos ubicados en la opción diseño, la cual nos permite modificar el gráfico que Excel acaba de insertar. El perímetro de la gráfica nos muestra los indicadores rectangulares de selección de la gráfica, lo cual está relacionado con el menú diseño en el que nos encontramos. Indicándonos que el menú diseño en el que nos encontramos aplica al gráfico que tenemos seleccionando. Implicando esto, que si realizamos alguna operación que nos mueva del menú diseño, volviendo a seleccionar el gráfico regresaremos al menú diseño.

 

Dentro de las opciones más importantes que tenemos en la barra de herramientas del menú diseño encontramos las operaciones:

 

Agregar elemento de gráfico. – Permite realizar cambios y anexar elementos adicionales de información a la gráfica. Algunas de las opciones con las que cuenta esta operación son:

 

Ejes. – Permite destacar alguna de las leyendas de los ejes, horizontal o vertical, así como definir características de las leyendas de los ejes.

Títulos de ejes. – Permite anexar un título a alguno o ambos ejes.

Título del gráfico. – Permite suprimir o anexar un título al gráfico, manejar su ubicación; así como algunas características.

Etiquetas de datos.- Permiten anexar etiquetas a cada una de las series de datos, así como ubicarlas en alguna área como ninguno, extremo externo, etremo interno, centro, base interior, etc.

 

Diseño rápido.- Muestra una serie de estilos de gráficas predefinidos con elementos predeterminados como leyendas de títulos de gráfico, ejes, etiquetas de datos, etc.

Cambiar colores. – Permite seleccionar un conjunto de colores predefinidos para las series de datos.

Estilos de diseño. - Muestra una serie de estilos de gráficas predefinidos con elementos predeterminados como leyendas de títulos de gráfico, ejes, etiquetas de datos, etc.

Excel avanzado: bases de datos

 

Es una función primordial de Excel, puesto que permite realizar algunas operaciones de proceso de datos, basado sobre todo en la funcionalidad que otorga de generar índices basados en los campos que constituyen la base de datos. Con esto se pueden realizar operaciones como la extracción de información bajo múltiples criterios basados en la información que contenga la base de datos.

En Excel toda información registrada en una hoja electrónica es considerada una base de datos. En ésta las columnas constituirán las columnas de la base de datos y los renglones serán sus registros. Para poder operar la base de datos requerimos que las columnas estén nombradas en el primer renglón de la base de datos.

 

FILTRADO DE INFORMACIÓN

 

El proceso de filtrado consiste en la definición de los filtros, los cuales estarán indicando que campos constituirán los filtros y cuáles serán los valores que contendrán estos campos

 

Dentro del menú Datos encontramos el botón Filtro que nos permitirá definir los filtros para aplicar a la base de datos

La opción Filtro habilita el acceso al cuadro de dialogo en cada uno de los campos anexándoles a cada una de las columnas un botón con una flecha, que permitirá definir los criterios de selección para las columnas que sean especificadas.

 

Cuando abrimos el cuadro de dialogo, encontramos opciones para realizar ordenamientos de los registros basados en el campo que se está indicando, el ordenamiento puede ser ascendente, descendente o bien un ordenamiento por color, que es un ordenamiento personalizado.

 

Para la definición de los valores que constituirán el filtro, encontramos al final del cuadro una lista de los valores que se encuentran en la columna, con un botón para cada uno de ellos que permite incluirlo o excluirlo, se encuentra una opción etiquetada seleccionar todo, la cual al desactivarla no seleccionará ningún valor, resultando de esta manera en una exclusión de todas las columnas.

 

EJEMPLO

Para la hoja de GASTOS DE VIATICOS PROYECTOS ESPECIALES 

extraeremos información de los gastos por viáticos correspondientes al empleado HECTOR VALENCIA. Para hacer esto presionamos el botón de la flecha en la columna EMPLEADO y seleccionamos el valor HECTOR VALENCIA, con lo que obtendremos en la hoja electrónica:

Una vez que presionamos la tecla aceptar EXCEL nos presenta el resultado del filtro

Podemos aplicar múltiples filtros sobre una base de datos resultando en una conjunción de filtros. Para nuestra hoja, sí solicitamos como filtro adicional el PROYECTO MANGLAR

Resultando en la conjunción de criterios EMPLEADO HECTOR VALENCIA Y PROYECTO MANGLAR. El resultado finalmente es:

EXTRACCIÓN Y COPIADO DE INFORMACIÓN

Una de las funciones esenciales en el manejo de las bases de datos y el filtrado de información es el copiado de la información, pues cuando no se realiza el copiado nos encontramos trabajando sobre los datos de la base de datos, lo cual restringe modificar la información que estemos filtrando, pues los cambios serán reflejados en la información de la base de datos. El copiado de información nos permite generar una copia que podemos alterar sin modificar la información que reside en la base de datos.

Para realizar el copiado de la información se realiza la siguiente operación

Accedemos al menú Datos y presionamos el botón Avanzadas dentro de Ordenar y filtrar con lo que aparece el menú

Para realizar copiado de información debemos especificar los criterios dentro de la hoja electrónica, así como especificar físicamente donde se ubicará la copia de los datos. Para especificar los criterios debemos ubicar en celdas el nombre de las columnas y los valores que constituirán el criterio.

 

EJEMPLO

Para indicar que extraeremos los registros para el empleado HECTOR VALENCIA copiamos la columna empleado y el valor de esta forma:

Como se puede ver en el ejemplo en las celdas A26 copiamos el nombre de la columna (EMPLEADO) y en la celda A27 el valor del registro que estaremos extrayendo (HECTOR VALENCIA).

 

CRITERIOS BASADOS EN MULTIPLES COLUMNAS

Para realizar un filtrado que conjunte varias columnas lo único que requerimos hacer es integrar las columnas que constituirán el filtro y sus valores de la misma manera que se realizó en el ejemplo anterior.

EJEMPLO

 

Para indicar que nuestro criterio estará constitudo por el empleado HECTOR VALENCIA y el proyecto MANGLAR copiamos las columnas empleado y proyecto y sus valores, de la siguiente manera

CRITERIOS BASADOS EN MULTIPLES VALORES PARA UNA COLUMNA

Para indicar múltiples valores para una columna tan solo tecleamos los valores en renglones continuos. Si tenemos criterios en múltiples columnas funcionará a la vez como una conjunción.

 

EJEMPLO

 

Para extraer los registros correspondientes al empleado MARIO REYNOSA de los proyectos PARICUTIN Y MANGLAR realizamos el siguiente copiado:

Finalmente, para realizar el copiado de la información también debemos indicar el lugar donde la copia será realizada.

Para poder indicar en el cuadro de dialogo Filtro avanzado el criterio y donde se realizará la copia debemos seleccionar la opción copiar a otro lugar del cuadro de menú

Una vez que presionamos el botón aceptar EXCEL nos genera el resultado:

Excel básico: Fórmulas

 Agregan valor a las hojas electrónicas al permitir a las hojas electrónicas generar información adicional a partir de los datos asentados en la hoja. Excel proporciona elementos para el proceso mediante el uso de las fórmulas. Entre estos elementos se encuentran: manejo de rangos de celdas en fórmulas, uso indistinto y combinado de datos y rangos como argumentos de una fórmula, uso de una fórmula dentro de otra, entre otras.

En Excel 2016 en el menú Fórmulas encontramos los tipos de fórmulas que podemos registrar en una celda, así como algunas otras operaciones auxiliares para el trabajo con fórmulas como es el manejo de rangos.

Entre los tipos de funciones tenemos:

 

MATEMÁTICAS. – Como ejemplo de fórmulas matemáticas tenemos valor absoluto, suma, cociente.

 

ESTADISTICAS. – Desviación estándar, promedio.

 

TEXTO. – Cadena izquierda, concatenar, extraer cadena.

Excel básico: Copiado de información

COPIADO DE INFORMACIÓN

                Excel introduce elementos auxiliares para ejecutar una operación que es recurrente en la elaboración de hojas electrónicas y que es el copiado de fórmulas. Para esto define el concepto de direccionamiento absoluto y relativo.

 

Direccionamiento absoluto. – permite realizar el copiado de una fórmula que contiene como argumento una celda que debe permanecer fija en el copiado. Implicando que, si la fórmula es copiada a un rango de celdas, el argumento de todas las fórmulas copiadas será la misma celda que la que referencia la fórmula original.

 

Direccionamiento relativo. – Permite definir celdas como argumentos de funciones, que permitan que al ser realizada la copia, la nueva fórmula estará referenciando como argumento a una celda que dependa de la posición donde se realizó la copia.

Excel de manera automática considera un direccionamiento relativo, para que al realizar un copiado de celdas (CTRl+C, CTRL+V) los argumentos de la nueva fórmula dependan de su posición en la hoja electrónica.

 

EJEMPLO:

 

En este ejemplo como se puede observar en la línea de comando se introdujo la función suma con argumentos relativos (D6:D8). Al ser el direccionamiento relativo se puede interpretar la función como calcula en esta celda la suma de las celdas que se encuentran en la misma columna desde cuatro renglones menos, hasta dos renglones menos (D6,D7,D8). Dando como resultado 500.

Al realizar el copiado de esta fórmula utilizando combinaciones de teclas (CTRL+C, CTRL+V) para copiarla en el mes FEBRERO obtenemos en la celda E10:

 

Como se puede ver en la línea de comando los argumentos de la función se han cambiado ajustándose a la columna donde se realizó la copia. De igual manera sucede para la columna MARZO

 

Excel utiliza el signo $ para indicar que un elemento de la celda será absoluto, la columna o el renglón. Aunque si se prefijan ambos elementos en la fórmula los dos serán absolutos.

 

EJEMPLO

Se observa en la línea de comando como se hace fija la celda D10 prefijando el renglón y la columna. Al momento de realizar la copia tendremos:

Como se puede observar en la copia la fórmula se mantiene referenciando la celda D10

Excel básico: rango de celdas

 

RANGOS DE CELDAS

Lo constituye un conjunto de celdas contiguo que forman  un rectángulo dentro de la hoja. Puede denotarse mediante la notación Renglón1.Columna1..Renglón2.Columna2. por ejemplo A1..C2. Tiene el valor de permitirnos asignarle un nombre al rango de celdas, para después utilizar el nombre para realizar alguna función u otra operación que actúe sobre el referido rango. Al permitirnos utilizar el nombre dentro de la función u operación nos está independizando de recordar físicamente las celdas donde se ubica el rango.

 

Para definir un rango de celda accedemos al menú Fórmulas y ahí tenemos la sección de nombres definidos, desde donde podemos acceder a la función  Asignar nombre que abrirá el siguiente cuadro de diálogo

Donde definimos los valores:

 

Nombre: como será denominado el rango

Ámbito: El área o alcance donde podrá ser utilizado el rango y son libro y hoja

 

Se refiere a: indica las celdas que constituirán el rango

 

 

Dentro de la barra de herramientas Nombres definidos se encuentran las funciones Administrador de nombres, utilizar en la fórmula y crear desde la selección que operan sobre rangos de celdas y realizan las siguientes funciones:

 

Administrador de nombres.- permite modificar rangos creados, borrar existentes o crear nuevos rangos

 

Utilizar en nombre.- permite registrar en una celda que se está editando con una fórmula, un rango que será un parámetro para la fórmula.

 

Crear desde selección.- Es una función bastante interesante que permite registrar los rangos de celdas directamente de las celdas que los constituyen. Puede definirse un área tabular donde cada renglón o columna constituirá un rango. Para indicar el nombre de cada rango se puede utilizar el renglón o columna más alto(bajo) o a la derecha(izquierda) para indicar el nombre de cada uno de los rangos.

Excel básico: La hoja electrónica

 

HOJA ELECTRÓNICA

Da la posibilidad al usuario de generar reportes que pueden incluir fórmulas que relacionen la información que se registra en la hoja. La característica fundamental de la hoja es tener la forma de un conjunto de celdas tabuladas en renglones y columnas. La intersección entre las columnas y los renglones definirá las celdas, que es el lugar donde se ingresará la información. Adicionalmente se les dará a las celdas la presentación que se requiera para formar el reporte.

En Excel 2016 el número de renglones de una hoja puede ser de hasta 1,048,576 y de columnas de hasta 16,384.

 

CELDA

 En este objeto registraremos la información que contendrá el reporte, es el elemento que nos permite darle formato y definir características de la información.

 

Formato de celdas: Dependiendo del tipo de información (etiquetas, números) podrá requerir atributos como

Alineación: Justificada, Izquierda, Derecha o centrada

Presentación: número de cifras decimales, formato porcentual, crédito o débito entre otras.

Otras características de la celda como alto, ancho y color de relleno de la celda.

 

Para acceder al menú de formato de celdas, necesitamos seleccionar las celdas sobre las que trabajaremos y accionar el menú contextual:

 

Accediendo a la opción de Formato de celdas… podemos encontrar todos los elementos que podemos formatear

Contenido de las celdas.- define el tipo de información que tendrá la celda. Esto a su vez determinará las operaciones que se pueden realizar sobre él. Los contenidos pueden ser de tipo númerico, etiquetas o fórmulas.

Númerica.- permite realizar operaciones como suma, resta, división, acumulado de un conjunto de celdas númericas, entre otras. Algunos de los caracteres válidos para para iniciar este tipo de contenido son el signo =, + y -

Etiquetas.- Constituye el texto que nos permitirá identificar la información que contiene la hoja electrónica. Los caracteres validos para iniciar una entrada de este tipo son: las letras de la a a la z, el apostrofe ‘ y el espacio.

Fórmulas.- permite efectuar una operación sobre algún valor, un conjunto de celdas o sobre otras funciones.