Manual curso de excel avanzado vmbe

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Dr. Víctor Manuel Barraza Espinoza Culiacán,Dr. Sinaloa, México, 25 de noviembre de 2017 Facilitador: Víctor Manuel Barraza Espinoza

0

Objetivo del curso: Proporcionar a los participantes los conocimientos necesarios para su desarrollo profesional y laboral sobre temas selectos de Microsoft Excel, a través de las herramientas prácticas que coadyuven al logro de la eficiencia y eficacia en el uso y manejo del tratamiento de datos acorde a sus necesidades personales, escolares o laborales, para la resolución de problemas o toma de decisiones.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Al término del curso el participante construirá hojas de cálculo integrando formatos de celda, numéricos, lista de datos, formatos condicionales, funciones lógicas, búsqueda de datos, gráficos, minigráficos, filtros, filtros avanzados, organigramas, auditoría de fórmulas, tablas dinámicas, búsqueda de objetivos, sólver, enlace entre hojas, funciones financieras y contables, así como la protección de celdas y hojas, que le permitan automatizar sus tareas diarias.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Temario: Contenido Introducción ......................................................................................................................................... 1 Formato de Celdas Fuente .................................................................................................................. 2 Formatos numéricos ............................................................................................................................ 4 Tipos de Operadores en Excel ............................................................................................................ 6 Asignar nombres a celdas o rangos .................................................................................................... 9 Formato Condicional ......................................................................................................................... 11 Funciones en Excel ........................................................................................................................... 13 Funciones lógicas .............................................................................................................................. 16 Función SI ..................................................................................................................................... 16 Función SI anidada ....................................................................................................................... 18 Función SUMAR:SI ....................................................................................................................... 19 Función SUMAR.SI.CONJUNTO .................................................................................................. 21 Múltiples criterios con la función SUMAR.SI.CONJUNTO ........................................................... 23 Fórmulas Condicionales en Excel ..................................................................................................... 24 Función condicional “SI” ............................................................................................................... 24 Fórmula condicional con función Y ............................................................................................... 25 Fórmula condicional con Función O ............................................................................................. 26 Gráficos en Excel .............................................................................................................................. 27 Tipos de gráficos en Excel ............................................................................................................ 27 Tipos de gráficos más utilizados ................................................................................................... 27 Cómo crear un gráfico en Excel.................................................................................................... 29 Gráficos combinados en Excel: ......................................................................................................... 31 Crear gráficos combinados en Excel ............................................................................................ 31 Búsqueda de Datos ........................................................................................................................... 35 Buscar datos en Excel con BUSCARV ......................................................................................... 35 Desventaja de la función BUSCARV ............................................................................................ 36 Función COINCIDIR .......................................................................................................................... 37 Función INDICE ............................................................................................................................ 38 Ventaja de INDICE y COINCIDIR sobre BUSCARV .................................................................... 39 Buscar un valor y regresar múltiples resultados ............................................................................... 40 Corrección de errores ................................................................................................................... 43 Combinar BUSCARV y COINCIDIR ............................................................................................. 44 Crear una lista de validación ......................................................................................................... 45 Tip’s de seguridad ......................................................................................................................... 50 Protegiendo los datos ................................................................................................................... 51 Gestión Base de datos en Excel ....................................................................................................... 53 Crear la base de datos .................................................................................................................. 58 Consultar la base de datos ........................................................................................................... 58 Gestión de tablas de datos ................................................................................................................ 61 Organizar la tabla de datos: .......................................................................................................... 61 Filtros en Excel .................................................................................................................................. 62 Cómo crear filtros en Excel ........................................................................................................... 62 Cómo usar los filtros en Excel ...................................................................................................... 63 Filtrar por varias columnas ............................................................................................................ 64 Cómo quitar un filtro en Excel ....................................................................................................... 64 Filtrar en Excel buscando valores ................................................................................................. 65 Filtros de texto en Excel ................................................................................................................ 65 Filtros de número en Excel ........................................................................................................... 66 Filtros de fecha en Excel ............................................................................................................... 67 Filtrar por color en Excel ............................................................................................................... 67 Filtro avanzado en Excel ................................................................................................................... 69 Aplicar un filtro avanzado a los datos ........................................................................................... 70 Tablas Dinámicas en Excel ............................................................................................................... 72

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Facilitador: Dr. Víctor Manuel Barraza Espinoza

1

Crear una tabla dinámica en Excel ............................................................................................... 72 Automatizar procesos y datos en Excel ............................................................................................ 75 Autocompletar en Excel ................................................................................................................ 75 Autocompletar datos previamente capturados ............................................................................. 76 Otros Autocompletar: .................................................................................................................... 76 Construcción de base de datos para automatizar procesos de cálculo, fórmulas y protección de datos en la empresa .......................................................................................................................... 77 Minigráficos en Excel ........................................................................................................................ 83 Tipos de Minigráficos en Excel ..................................................................................................... 83 Cómo crear un Minigráficos .......................................................................................................... 84 Cambiar el tipo de minigráfico ...................................................................................................... 85 Borrar un minigráfico ..................................................................................................................... 86 Organigramas .................................................................................................................................... 87 Modificar Organigramas ................................................................................................................ 89 Auditoría de fórmulas en Excel ......................................................................................................... 92 Evaluar fórmulas ........................................................................................................................... 95 Evaluar fórmulas en Excel paso a paso........................................................................................ 95 Funciones Financieras en Excel ....................................................................................................... 99 Principales funciones financieras comúnmente utilizadas en Excel ................................................. 99 Función VF .................................................................................................................................. 100 Función PAGO ............................................................................................................................ 101 Función VA .................................................................................................................................. 104 Sintaxis ................................................................................................................................... 104 Referencias: .................................................................................................................................... 105

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Facilitador: Dr. Víctor Manuel Barraza Espinoza

2

Introducción ¿Qué es Excel?

El nombre Excel viene de la palabra en inglés que se refiere a algo o alguien que "se destaca entre los demás” o “es mejor que los otros”.

Excel es un programa informático desarrollado por la Empresa Microsoft y forma parte de Office que es una suite ofimática la cual incluye otros programas como Word y PowerPoint.

Excel se distingue de los demás programas porque nos permite trabajar con datos numéricos, es decir, podemos realizar cálculos, crear tablas o gráficos y también podemos analizar los datos con herramientas tan avanzadas como las Tablas Dinámicas, búsqueda de objetivos, Solver, entre otras.

Excel es una hoja de cálculo Excel es un programa computacional que es conocido como hoja electrónica de cálculo. Las hojas de cálculo fueron desarrolladas desde la década de 1960 para simular las hojas de trabajo contables de papel y de esa manera ayudar en la automatización del trabajo contable. ¿Por qué aprender a utilizar Excel?

La gran cantidad de herramientas y comandos disponibles dentro de Excel podría intimidar a cualquier usuario que sea nuevo en el uso de la aplicación, pero la realidad es que hoy en día Excel es una de las aplicaciones más utilizadas en el ámbito laboral. Excel no es solamente una aplicación para contadores y financieros.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Excel se ha convertido en una herramienta de tratamiento de datos que trasciende mucho más allá de la contabilidad ya que facilita la manipulación y tratamiento de datos.

Por esta razón es que es casi inevitable que, sin importar la profesión a la que te dediques, será muy probable que en algún momento tendrás la necesidad de utilizar Excel. Puedes comprobarlo por ti mismo y buscar en las bolsas de trabajo online y verás que el dominio de Excel es una de las habilidades más solicitadas.

Así que, ahora que ya sabes qué es Excel, no esperes más y decide aprender a utilizar esta fabulosa herramienta.

Te invito a seguir preparándote e invertir tu tiempo en algo tan productivo como lo es la educación, capacítate cada día más, y en la medida que seas cada día mejor vas a tener mucho más oportunidades de sobresalir en cualquier ámbito laboral, profesional o académico.

1

Formato de Celdas Fuente ¿Qué es y para qué sirve?

Excel nos da muchas opciones de configurar un documento, una de ellas es cambiar la fuente de escritura, cambiar el color y seleccionar el tamaño. De esta manera, seleccionamos una(s) celda(s) y presionamos el botón derecho del mouse, luego seleccionamos la opción formato celdas.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Facilitador: Dr. Víctor Manuel Barraza Espinoza

2

Formato En el cuadro formato de celdas, existen muchas opciones para cambiar un documento, como fuente, estilo y tamaño, también podemos subrayar un texto, el color, el tipo de fuente y los efectos.

Facilitador: Dr. Víctor Manuel Barraza Espinoza Mientras realizamos los cambios, en la vista previa se observa los cambios realizados de manera previa.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

3

Formatos numéricos El formato números en Excel es el proceso a través del cual podemos cambiar la apariencia de un número contenido en una celda. Es importante remarcar que dicho formato no modifica el valor numérico de la celda sino solo su apariencia.

Cuando ingresamos valores numéricos en nuestra hoja de Excel, dichos valores no tienen formato alguno, pero podemos modificar la cantidad de decimales mostrados, agregar el símbolo de moneda o el separador de miles. Todos estos cambios en la apariencia de un número los podemos lograr aplicando un formato especial a la celda.

Formato de números automático Excel puede identificar ciertos formatos de número al momento de ingresar los datos y los despliega de manera adecuada mientras asigna el valor numérico correcto a la celda. Por ejemplo, si ingresamos el valor 12.50% en una celda, Excel mantendrá la apariencia de porcentaje, pero el valor de la celda no contendrá el símbolo de porcentaje sino solamente el valor numérico 0.125 y lo podremos saber si hacemos una operación con dicha celda:

Facilitador: Dr. Víctor Manuel Barraza Espinoza Ya que la celda D2 no contiene formato alguno se muestra el valor de la celda D1 multiplicado por dos. De esta manera podemos saber que al incluir a la celda D1 en cualquier operación, Excel considerará su valor numérico. Así como Excel aplica un formato automático a valores de porcentaje, también lo hace si utilizamos el separador de miles o si utilizamos el símbolo de moneda. En realidad, el valor de la celda no contendrá el separador de miles ni el símbolo de moneda sino solamente el valor numérico.

Formato de números desde la Cinta de opciones Hay ocasiones en las que importamos datos a Excel o simplemente queremos modificar la manera en que se despliegan los números de una celda después de haberlos ingresado y para ello podemos utilizar los comandos de la Cinta de opciones que se encuentran dentro del grupo Número en la ficha Inicio. Cualquiera de estos comandos aplicará el formato seleccionado a la celda activa o a una selección de celdas.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

4

Facilitador: Dr. Víctor Manuel Barraza Espinoza 1. Podemos elegir rápidamente un formato de la lista de formatos más utilizados entre los cuales encontramos los formatos Número, Moneda, Contabilidad, Fecha corta, Fecha larga, Hora, Porcentaje, Fracción, Científica y Texto.

2. El menú desplegable del botón Formatos de contabilidad nos permite elegir entre varias opciones de formatos específicos de contabilidad y especificar si deseamos agregar el símbolo de dólares americanos o el símbolo de euros.

3. El botón Estilo porcentual aplicará un estilo de porcentaje al valor de la celda.

4. El comando Estilo millares agregará el separador de miles al valor numérico de la celda.

5. Podemos Aumentar decimales mostrados dentro de la celda siempre y cuando el valor numérico contenga dicha cantidad de decimales.

6. También podemos mostrar menos decimales utilizando el botón Disminuir decimales.

7. Si queremos tener más opciones de formato podemos pulsar el indicador de cuadro de diálogo del grupo Número el cual abrirá una nueva ventana que nos dará más opciones de formato.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

5

Tipos de Operadores en Excel Podemos clasificar las fórmulas de Excel en cuatro grupos principales: 1. Fórmulas aritméticas 2. Fórmulas de comparación 3. Fórmulas de texto 4. Fórmulas de referencia Cada uno de estos grupos tiene su propio conjunto de operadores que permiten realizar los cálculos y operaciones específicas de cada grupo. A continuación, enlistaré los operadores de cada grupo.

1. Operadores aritméticos Las fórmulas aritméticas son las más comunes y combinan números, referencias de celda, funciones y operadores aritméticos para realizar cálculos matemáticos. La siguiente tabla muestra los operadores aritméticos de Excel:

Facilitador: Dr. Víctor Manuel Barraza Espinoza Aunque el porcentaje no es un operador aritmético, Excel le da un tratamiento como operador ya que al ingresar un símbolo de porcentaje después de un número provocará que Excel realice una división entre 100 de manera automática.

2. Operadores de comparación Los operadores de comparación nos permiten comparar dos o más números o cadenas de texto. Si el resultado de la comparación es positivo, obtendremos como resultado en valor lógico VERDADERO. De lo contrario obtendremos como resultado el valor FALSO. A continuación, tenemos la lista de operadores de comparación:

Los operadores de comparación son muy utilizados con las funciones lógicas de Excel que nos permiten ejecutar una acción al cumplirse la condición establecida.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

6

3. Operadores de texto Las fórmulas de Excel también pueden manipular texto y pueden hacer uso del operador de concatenación para unir el valor de dos cadenas de texto.

4. Operadores de referencia Los operadores de referencia nos ayudan a combinar dos o más referencias de celda para crear una sola referencia.

Recuerda que, si tu sistema tiene la configuración regional de México, entonces el operador de unión será la coma (,).

Precedencia de operadores en Excel Cuando creamos fórmulas que contienen más de un operador, será necesario conocer el orden en que dichas operaciones serán calculadas por Excel. Por esta razón existe un orden de precedencia que determina la manera en que Excel hace los cálculos:

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Si tenemos una fórmula con varios operadores con la misma precedencia, entonces Excel realizará los cálculos de izquierda a derecha.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

7

Cómo controlar el orden de precedencia La única manera en que podemos influir en el orden de precedencia de operadores en Excel es utilizando paréntesis. Al colocar paréntesis alrededor de un cálculo estaremos diciendo a Excel que deseamos otorgar una alta prioridad a dicho cálculo y por lo tanto se realizará primero. En la siguiente imagen puedes observar cómo se modifica el resultado de una fórmula al incluir paréntesis.

Cuando existen paréntesis dentro de una fórmula, Excel comenzará los cálculos con el paréntesis que tenga el mayor nivel de anidación. Y si dentro de un mismo paréntesis existen varios operadores, entonces se aplicarán las reglas de precedencia antes vistas. De esta manera los paréntesis nos permiten controlar el orden de precedencia de los cálculos es una fórmula de Excel.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Facilitador: Dr. Víctor Manuel Barraza Espinoza

8

Asignar nombres a celdas o rangos Hasta ahora he utilizado el estilo de referencia A1 para referirme tanto a una celda como a un rango, pero también existe la posibilidad de crear un nombre descriptivo que los represente adecuadamente.

Asignar un nombre a un rango de celdas Estos nombres se pueden utilizar dentro de una fórmula para ayudar en la compresión de la misma posteriormente. Para asignar un nombre a una celda sigue los siguientes pasos. Selecciona la celda o rango a la que asignarás un nombre y haz clic en el cuadro Nombre que se encuentra en el extremo izquierdo de la barra de fórmulas:

Escribe el nombre que deseas y presiona Entrar.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Otra manera de crear un nombre para un rango es desde la ficha Fórmulas y el botón Asignar nombre.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

9

Una vez que hayas seleccionado el rango de celdas oprime este botón y se mostrará el cuadro de diálogo Nombre nuevo:

En la caja de texto Nombre coloca el nombre que asignarás a la celda o rango y oprime el botón Aceptar.

Utilizar un nombre en una fórmula Como ejemplo final utilizaré el nombre que acabamos de crear dentro de una fórmula para que observes cómo Excel interpreta correctamente el nuevo nombre del rango, observa la barra de fórmulas:

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Ahora ya sabes que puedes nombrar tanto celdas como rangos y utilizar ese nombre dentro de tus fórmulas para facilitar tu trabajo.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

10

Formato Condicional El Formato Condicional en Excel se utiliza para resaltar los datos que cumplan con condiciones específicas aplicándoles un formato seleccionado (Color, tamaño, fuente, etc.), esto se hace con el fin de identificar y supervisar determinada información contenida en las celdas y evitar la revisión una a una. Ésta es una de las herramientas más útiles de Excel cuando se trabaja con listas grandes de datos, no porque calcule nada, sino porque resalta los valores que nos interesan facilitando encontrar "cosas raras", valores fuera de un intervalo razonable, y cosas por el estilo.

Un ejemplo para aplicar el Formato Condicional en Excel es identificar las ventas que sean inferiores o superiores a un valor específico de una base de datos de vendedores.

Capturar la siguiente tabla de datos: Nombre Vendedor País Andrea Serna Argentina José Buitrago Bolivia Amanda Leal Colombia Pablo Bonilla Costa Rica Samuel Villa Cuba Ursula Iguaran Chile Felix Aruda Ecuador Javier Tabora España Susana Lemon Guatemala Robin Ojeda Honduras Marcos Madrid México Alex Rodriguez Nicaragua

Ventas en US$ 20,000 35,000 32,500 37,000 29,000 18,100 48,900 19,200 42,000 39,000 12,000 12,000

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Pasos para implementar el Formato Condicional en Excel: Seleccione el rango de celdas en las cuales desee aplicar el formato condicional 1. Haga Clic en menú “Inicio” 2. Clic en icono “Formato Condicional” 3. Seleccione y configure el tipo de Formato 4. Haga Clic en botón “Aceptar” Escenario práctico: o Se requiere identificar las ventas que hayan sido inferiores o igual a US$ 20,000 o Se requiere identificar las ventas que estén entre US$ 29,000 y US$ 39,000 o Se requiere identificar las 2 ventas más altas Resultado Nombre Vendedor País Andrea Serna Argentina José Buitrago Bolivia Amanda Leal Colombia Pablo Bonilla Costa Rica Samuel Villa Cuba Ursula Iguaran Chile Felix Aruda Ecuador Javier Tabora España Susana Lemon Guatemala Robin Ojeda Honduras Marcos Madrid México Alex Rodriguez Nicaragua

Ventas en US$ 20,000 35,000 32,500 37,000 29,000 18,100 48,900 19,200 42,000 39,000 12,000 12,000

Facilitador: Dr. Víctor Manuel Barraza Espinoza

11

Ejemplo 2.- Promedios para acreditar la materia, tenemos 5 estudiantes que tienen un promedio para exentar, mínimo para exentar es 8 o superior, los que tengan promedio entre 6 y 7 presentan ordinario y los que tengan entre 1 y 5 presentan extraordinario.

Criterios condicionales: • Promedios entre 8 y 10 (fondo azul, valores en blanco, arial, negrita) • Promedios entre 6 y 7 (fondo verde, valores en blanco, arial, negrita) • Promedios entre 1 y 5 (fondo rojo, valores en blanco, arial, negrita) Tabla de datos: # 1 2 3 4 5 6 7 8 9 10

NOMBRE DEL ALUMNO BAEZ RAUL COTA MARIA DIAZ FELIPE ESTRADA LUIS FELIX DELIA FELIX JORGE GARCIA SAUL GARCIA BERTHA HERAS KARLA LOPEZ DANIEL

I

II

III PROM.

9 7 6 9 8 5 2 6 6 10

9 9 7 9 7 4 3 7 7 10

8 8 7 10 9 5 4 5 6 9

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Aplicar criterios de formato condicional Aplicar fórmulas para ver el Resultado: # 1 2 3 4 5 6 7 8 9 10

NOMBRE DEL ALUMNO BAEZ RAUL COTA MARIA DIAZ FELIPE ESTRADA LUIS FELIX DELIA FELIX JORGE GARCIA SAUL GARCIA BERTHA HERAS KARLA LOPEZ DANIEL

I

II

III PROM.

9 7 6 9 8 5 2 6 6 10

9 9 7 9 7 4 3 7 7 10

8 8 7 10 9 5 4 5 6 9

8.7 8.0 6.7 9.3 8.0 4.7 3.0 6.0 6.3 9.7

Otros formatos condicionales (Barras, semáforo, marcas) # 1 2 3 4 5 6 7 8 9 10

NOMBRE DEL ALUMNO BAEZ RAUL COTA MARIA DIAZ FELIPE ESTRADA LUIS FELIX DELIA FELIX JORGE GARCIA SAUL GARCIA BERTHA HERAS KARLA LOPEZ DANIEL

I

II

III

PROM.

PROM.

PROM.

PROM.

9 7 6 9 8 5 2 6 6 10

9 9 7 9 7 4 3 7 7 10

8 8 7 10 9 5 4 5 6 9

8.7 8.0 6.7 9.3 8.0 4.7 3.0 6.0 6.3 9.7

8.7 8.0 6.7 9.3 8.0 4.7 3.0 6.0 6.3 9.7

8.7 8.0 6.7 9.3 8.0 4.7 3.0 6.0 6.3 9.7

8.7 8.0 6.7 9.3 8.0 4.7 3.0 6.0 6.3 9.7

Facilitador: Dr. Víctor Manuel Barraza Espinoza

12

Funciones en Excel En Excel se encuentran muchas herramientas que permiten realizar el trabajo de forma más rápida, una de esas herramientas son las funciones de Excel, las cuales cumplen determinado objetivo con base en la categoría que corresponda, por tal razón a continuación se especifica la categoría y las funciones de Excel asociadas a cada una: Funciones Financieras: Funciones Lógicas:

Facilitador: Dr. Víctor Manuel Barraza Espinoza Funciones Texto:

Funciones Fecha y Hora:

Facilitador: Dr. Víctor Manuel Barraza Espinoza

13

Funciones Búsqueda y referencia:

Funciones Matemáticas y Trigonométricas:

Facilitador: Dr. Víctor Manuel Barraza Espinoza Funciones Estadísticas:

Facilitador: Dr. Víctor Manuel Barraza Espinoza

14

Funciones Ingeniería:

Funciones Información:

Funciones Cubo:

Funciones Comparabilidad:

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Funciones Web:

Facilitador: Dr. Víctor Manuel Barraza Espinoza

15

Funciones lógicas Funciones condicionales en Excel Conoce las funciones SI a forma de condición El programa de hojas de cálculo Excel tiene una gran cantidad de fórmulas y funciones, en este artículo en concreto vas a conocer las funciones condicionales del mismo, que se encuadran en las funciones lógicas del programa.

Estas funciones se caracterizan por que contienen una condición a través de la cual según se cumpla o deje de cumplirse establecerás la obtención de un resultado.

Como es más fácil hacerlo que decirlo vamos a ver ejemplos prácticos de diferentes funciones condicionales en Excel.

En concreto: • Función SI • Función SUMAR.SI • Función SUMAR SI CONJUNTO

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Función SI Esta es la sintaxis general de la función: =SI(condición lógica, “Valor verdadero”, “Valor falso”)

Piensa en el siguiente ejemplo: Tienes una hoja electrónica donde se reflejan las calificaciones de unos alumnos, en donde quieres saber si el resultado de cada alumno es aprobado o reprobado.

Con la función lógica SI, puedes escribir una fórmula en la que indiques lo siguiente: Si la calificación es menor o igual a 5 te devuelva el valor Reprobado, y en caso contrario Aprobado.

La función que necesitas es: =SI(B4<=5,"Reprobado","Aprobado")

Facilitador: Dr. Víctor Manuel Barraza Espinoza

16

Ejemplo: Capture los siguientes datos

Facilitador: Dr. VĂ­ctor Manuel Barraza Espinoza

Facilitador: Dr. VĂ­ctor Manuel Barraza Espinoza

17

Función SI anidada La función SI nos permite evaluar una condición y ejecutar una acción dependiendo el resultado, ya sea verdadero o falso. Esto funciona muy bien cuando solo estamos comparando dos valores, pero ¿Cómo podemos utilizar la función SI al tener más de dos posibles opciones? La respuesta es utilizar la función SI anidada.

Ejercicio: Tenemos las calificaciones de 10 alumnos y se requiere colocar en el resultado lo siguiente, de 0 a 5 Reprobado, 6 Suficiente, 7 Regular, 8 Bien, 9 Muy bien y 10 Excelente.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Resultado:

Colocar los formatos condicionales que gusten, de acuerdo a su criterio….

Facilitador: Dr. Víctor Manuel Barraza Espinoza

18

Función SUMAR:SI La función SUMAR.SI en Excel nos permite hacer una suma de celdas que cumplen con un determinado criterio y de esta manera excluir aquellas celdas que no nos interesa incluir en la operación.

En este caso la sintaxis es: SUMAR.SI(rango,criterio,rango_suma) • • •

Rango (obligatorio): El rango de celdas que será evaluado.

Criterio (obligatorio): La condición que deben cumplir las celdas que serán incluidas en la suma.

Rango_suma (opcional): Las celdas que se van a sumar. En caso de que sea omitido se sumaran las celdas especificadas en Rango.

El Criterio de la suma puede estar especificado como número, texto o expresión. Si es un número hará que se sumen solamente las celdas que sean iguales a dicho número. Si el criterio es una expresión podremos especificar alguna condición de mayor o menor que.

Si el Criterio es un texto es porque seguramente necesito que se cumpla una condición en cierta columna que contiene datos de tipo texto, pero realizar la suma de otra columna que tiene valores numéricos. Todos estos casos quedarán más claros con los siguientes ejemplos.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

El primer ejemplo es muy sencillo ya que de una lista de valores aleatorios quiero sumar todas las celdas que contienen el número 5.

Recuerda que la función SUMAR.SI no realiza una cuenta de las celdas que contienen el número 5, de lo contrario el resultado habría sido 2.

La función SUMAR.SI encuentra las celdas que tienen el número 5 y suma su valor. Ya que las celdas A2 y A7 cumplen con la condición establecida se hace la suma de ambas celdas lo cual da el número 10 como resultado.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

19

Ahora cambiaré la condición a una expresión y sumare aquellas celdas que sean menores a 3. Observa el resultado de esta nueva fórmula.

Código Ascii para < ó > (Alt 60 <, Alt 62 >)

Ventas de un vendedor Ahora utilizaremos un criterio en texto y el tercer argumento de la función SUMAR.SI el cual nos deja especificar un rango de suma diferente al rango donde se aplica el criterio. En el siguiente ejemplo tengo una lista de vendedores y deseo conocer el total de ventas de un vendedor específico.

Para obtener el resultado colocaré el rango A2:A10 como el rango que debe ser igual al texto en la celda F1. El tercer argumento de la función contiene el rango C2:C10 el cual tiene los montos que deseo sumar.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

La celda F2 que contiene la función SUMAR.SI muestra la suma de las ventas que pertenecen a Juan y excluye el resto de celdas. Podría modificar un poco esta fórmula para obtener las ventas de un mes específico. Observa el resultado de esta adecuación en la celda F5:

Facilitador: Dr. Víctor Manuel Barraza Espinoza

20

Función SUMAR.SI.CONJUNTO La función SUMAR.SI.CONJUNTO en Excel nos permite sumar los valores de un rango de celdas que cumplen con varias condiciones. A diferencia de la función SUMAR.SI que permite un solo criterio, la función SUMAR.SI.CONJUNTO permite hasta 127 criterios.

Sintaxis: SUMAR.SI.CONJUNTO(rango_suma,rango_criterios1,criterios1,[rango_criterios2,criterios2],...)

• • • • •

Rango_suma (obligatorio): El rango de celdas que contiene los valores a sumar.

Rango_criterios1 (obligatorio): El rango de celdas que será evaluado por el Criterio1.

Criterio1 (obligatorio): El criterio que deben cumplir las celdas del Rango_criterios1.

Rango_criterios2 (opcional): El segundo rango de celdas que será evaluado por el Criterio2.

Criterio2 (opcional): El criterio que deben cumplir las celdas del Rango_criterios2.

Los valores de las celdas del rango_suma se sumarán solamente si cumplen con los criterios especificados. Los criterios pueden utilizar caracteres comodines como el signo de interrogación (?) para indicar un solo carácter o un asterisco (*) que indicará una secuencia de caracteres.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Solo el Rango_criterios1 y Criterio1 son obligatorios, a partir de ellos podemos especificar múltiples combinaciones de Rango_criterio y Criterio hasta un máximo de 127.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

21

Ejemplo de la función SUMAR.SI.CONJUNTO Tenemos una lista de CD’s vendidos en un día en una tienda de música. Me interesa sumar el número total de CD’s vendidos que tengan exactamente 12 canciones:

La columna que se sumará será la columna Vendidos la cual contiene el número total de CDs vendidos para cada álbum. La columna Canciones es el Rango_criterios1 porque contiene la información que será evaluada.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Finalmente, la condición que se debe cumplir se encuentra en la celda B17. Puedes ver en color verde las celdas que cumplen con la condición y cuya celda Vendido termina siendo sumada por la función SUMAR.SI.CONJUNTO.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

22

Múltiples criterios con la función SUMAR.SI.CONJUNTO Modificando un poco el ejemplo anterior, ahora deseo sumar el número total de CD’s vendidos que tengan más de 11 canciones y cuyo precio sea mayor a $4.00.

Para resolver este caso necesitaré especificar un segundo criterio que se aplicará a la columna Precio.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

En este segundo ejemplo hemos agregado otro rango de criterios que es la columna Precio y su respectivo criterio que se encuentra en la celda B18. Puedes ver en color verde aquellas celdas que cumplen con ambas condiciones y cuya columna Vendidos dan como resultado la suma de la celda B19.

La función SUMAR.SI.CONJUNTO en Excel será de gran utilidad cuando necesitamos aplicar más de un criterio antes de realizar una suma.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

23

Fórmulas Condicionales en Excel ¿Qué son las fórmulas condicionales en Excel?

A veces necesitamos obtener ciertos resultados dentro de una lista de datos, para mantener el orden de nuestra base de datos y visualizar lo que nos interese y sea relevante. Para lograrlo, Excel cuenta con funciones condicionales, las cuales pertenecen a la categoría de funciones lógicas. De esta manera, por medio de Excel lograremos que en una celda o varias sólo visualicemos un determinado valor o texto en función a lo que exista en otras celdas.

Función condicional “SI” Es la función condicional más útil y usada, puesto que se complementa con muchas otras fórmulas. Tal y como su nombre lo dice la función SI obedece la lógica de que, si se cumple con la condición que le exigimos, la hoja de cálculo nos mostrará el resultado A, caso contrario, es decir si no cumple con la condición, nos mostrará el resultado B. Ejemplo de Fórmula Condicional SI A continuación, se muestra la lista de las ventas mensuales de una boutique de ropa.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Se le dará un bono de $300.00 a los vendedores que hayan logrado ventas mayores a $4,000.00. Para esto usaremos la siguiente fórmula condicional: =SI(B2>4000,300,0)

Como podemos observar en la figura, la fórmula =SI(B2>4000,300,0) la cual quiere decir, SI las ventas (B2) son mayores que 4000, entonces recibe el bono de 300, de lo contrario no se recibe nada.

Este ejemplo muestra perfectamente como la condición SI arroja los resultados relevantes para nosotros, pues en la columna de Bonos podemos ver quienes cumplen con el requisito o la condicionante para recibir el bono.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

24

Además, podemos usar los condicionantes Y u O para conectar diferentes condiciones en una misma fórmula y obtener un resultado más completo o con mayores condiciones. Se debe tener en cuenta las comas y los paréntesis porque son muy importantes para establecer un orden y separar las condiciones para que Excel las pueda entender, puesto que funcionan como un “entonces” o “sino”.

Asimismo, las condiciones de texto deben estar entre comillas.

Fórmula condicional con función Y Las otras funciones o fórmulas condicionales las usamos para darle mayores detalles a las fórmulas empleadas, veamos el uso de la función Y: En la tienda de ropa cada empleado es calificado por su cliente, en un puntaje del 1 al 5, donde se asigna un puntaje a la atención que recibió por parte del vendedor.

De esta manera, recibirán el bono los vendedores que cumplan con la condición de haber realizado ventas mayores a 4,000.00, además de haber obtenido un promedio de puntuación entre 4 y 5.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Facilitador: Dr. Víctor Manuel Barraza Espinoza

25

Fórmula condicional con Función O De igual manera, también podemos usar el condicionante “O”:

En este caso, hemos complementado con la función condicionante O, y como podemos observar en la imagen la condición ha cambiado, puesto que ahora las vendedoras que recibirán el bono de 300.00, serán las que hayan logrado ventas mayores a 4,000.00 o las que hayan logrado un puntaje entre 4 y 5.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

En conclusión, las funciones condicionales son muy útiles y las podemos adaptar para diferentes circunstancias, sólo es cuestión de armar una fórmula pensando en todos los detalles para que la hoja de cálculo la pueda entender, todos los elementos son indispensables.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

26

Gráficos en Excel Un gráfico es la representación de datos, generalmente numéricos, mediante líneas, superficies o símbolos, para ver la relación que esos datos guardan entre sí y facilitar su interpretación.

Un gráfico también puede ser un conjunto de puntos, que se plasman en coordenadas cartesianas, y sirven para analizar el comportamiento de un proceso, o un conjunto de elementos.

La utilización de gráficos hace más sencilla e inmediata la interpretación de los datos. A menudo un gráfico nos dice mucho más que una serie de datos clasificados por filas y columnas.

Tipos de gráficos en Excel Elegir entre los diferentes tipos de gráficos en Excel para mostrar la información adecuadamente es de suma importancia. Cada tipo de gráfico desplegará la información de una manera diferente así que utilizar el gráfico adecuado ayudará a dar la interpretación correcta a los datos.

Tipos de gráficos más utilizados Estos son los tipos de gráficos más utilizados en Excel:

Facilitador: Dr. Víctor Manuel Barraza Espinoza

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.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

27

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.

Gráficos de Barra. Un gráfico de barra hace un énfasis en la comparación entre elementos en un período de tiempo específico. Este tipo de gráfico incluye cilindros, conos y pirámides.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Gráficos de Área. Los gráficos de área muestran la importancia de los valores a través del tiempo. Un gráfico de área es similar a un gráfico de línea, pero ya que el área entre las líneas está rellena, el gráfico de área le da una mayor importancia a la magnitud de los valores que lo que puede hacer un gráfico de línea.

Gráficos XY (Dispersión). Los gráficos de dispersión son útiles para mostrar la relación entre diferentes puntos de datos. Este tipo de gráfico utiliza valores numéricos para ambos ejes en lugar de utilizar categorías en alguno de los ejes como en los gráficos anteriores.

Existen otros tipos de gráficos en Excel como los de superficie, anillos, burbuja, entre otras. Los gráficos presentados el día de hoy son los tipos de gráficos más utilizados en Excel y pronto aprenderás a obtener el mayor provecho de cada uno de ellos.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

28

Cómo crear un gráfico en Excel Lo primero que debes hacer para crear un gráfico en Excel es organizar los datos que deseas trazar. Es decir, acomodar los datos en columnas y filas de manera que Excel pueda “entender” la información y crear el gráfico.

Observa el siguiente ejemplo de datos: MES VENTAS GANANCIAS ENERO 5,000,000.00 2,000,000.00 FEBRERO 5,500,000.00 2,200,000.00 MARZO 6,000,000.00 2,400,000.00 ABRIL 6,500,000.00 2,600,000.00 MAYO 7,000,000.00 2,800,000.00 JUNIO 7,500,000.00 3,000,000.00 JULIO 8,000,000.00 3,200,000.00 AGOSTO 8,500,000.00 3,400,000.00 SEPTIEMBRE 9,000,000.00 3,600,000.00 OCTUBRE 9,500,000.00 3,800,000.00 NOVIEMBRE 10,000,000.00 4,000,000.00 DICIEMBRE 10,500,000.00 4,200,000.00

Una vez organizada la información debes seleccionar el rango completo para indicar a Excel que trazará el gráfico utilizando como base dicha información:

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Antes de continuar debo decir que es importante conocer los tipos de gráficos en Excel de manera que puedas tomar la decisión adecuada sobre el gráfico a crear.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

29

Crear un gráfico en Excel El siguiente paso para crear un gráfico en Excel es ir a la pestaña Insertar, y dentro del grupo Gráficos hacer clic en el tipo de gráfico que has decidido crear. Para este ejemplo he decidido crear un gráfico de columnas por lo que haré clic sobre Columna y seleccionaré la primera de las opciones:

Justo después de haber seleccionado el gráfico que deseas Excel lo incrustará en la hoja:

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Resultado de los datos seleccionados:

VENTAS MENSUALES 12,000,000.00 10,000,000.00 8,000,000.00 6,000,000.00 4,000,000.00 2,000,000.00 0.00

VENTAS

GANANCIAS

Una vez insertado el gráfico asignar el formato acorde a sus necesidades. (tamaño de texto, colores, color de las barras, titulo del gráfico, personalizar ejes X y Y, líneas primarias y secundarias, valores, ubicación, etc….)

Facilitador: Dr. Víctor Manuel Barraza Espinoza

30

Gráficos combinados en Excel: Hay ocasiones en las que necesitamos combinar dos tipos de gráficos en uno solo para enfatizar las similitudes o diferencias entre las series de datos mostradas. Los gráficos combinados en Excel nos permitirán conseguir ese efecto visual que nos facilitará la comprensión de la información.

En resumen, un gráfico combinado es un gráfico que combina dos tipos de gráficos diferentes en uno solo. La siguiente imagen muestra un ejemplo de un gráfico combinado.

Título del gráfico 12,000,000.00 10,000,000.00 8,000,000.00 6,000,000.00 4,000,000.00 2,000,000.00 0.00

Facilitador: Dr. Manuel Barraza Espinoza VENTAS Víctor GANANCIAS Este gráfico es la combinación de un gráfico de columnas y un gráfico de línea de Excel y en esta ocasión te mostraré todos los pasos necesarios para crear este tipo de gráfico.

Crear gráficos combinados en Excel Supongamos que la siguiente tabla es la información de Ventas vs. Ganancias de una empresa durante todo un año y queremos crear un gráfico combinado que nos permita visualizar el comportamiento de las ganancias respecto a las ventas.

Tabla de datos:

Facilitador: Dr. Víctor Manuel Barraza Espinoza

31

Para iniciar, seleccionaré alguna celda del rango de datos y de inmediato iré a la ficha Insertar > Gráficos > Columna y seleccionaré la opción Columna agrupada.

Se insertará un gráfico que mostrará las columnas para cada una de las series de datos y podrás identificarlas fácilmente por su color y la leyenda desplegada al lado inferior del gráfico.

VENTAS MENSUALES 12,000,000.00 10,000,000.00 8,000,000.00 6,000,000.00 4,000,000.00 2,000,000.00 0.00

Facilitador: Dr. Víctor Manuel Barraza Espinoza VENTAS

GANANCIAS

Ahora debes decidir la serie que permanecerá con columnas y la serie que será convertida en una línea. Para nuestro ejemplo, convertiremos la serie que representa la columna Ganancia y que está representada en este momento por las columnas de color verde.

Así que, haré clic derecho sobre cualquiera de las columnas de color verde y seleccionaré la opción Cambiar tipo de gráfico de series tal como se muestra en la siguiente imagen.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

32

De inmediato se mostrará el cuadro de diálogo Cambiar tipo de gráfico y deberás elegir el tipo de gráfico para representar la serie de datos recién elegida. La siguiente imagen muestra el momento en que elijo en el cuadro combinado el tipo de gráfico para Ganancias, en este caso será el gráfico de Línea con marcadores.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Al hacer clic en el botón Aceptar, se hará el cambio en el tipo de gráfico y tendremos nuestro gráfico combinado como se muestra en la siguiente imagen.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

33

GrĂĄfico de Ventas Mensuales 12,000,000.00 10,000,000.00 8,000,000.00 6,000,000.00 4,000,000.00 2,000,000.00 0.00

VENTAS

GANANCIAS

Y listo, este es el resultado del GrĂĄfico Combinadoâ&#x20AC;Ś. A seguir practicando đ&#x;&#x2DC;&#x160;.

Facilitador: Dr. VĂ­ctor Manuel Barraza Espinoza

Facilitador: Dr. VĂ­ctor Manuel Barraza Espinoza

34

Búsqueda de Datos El uso de buscadores para rangos de datos es muy común, especialmente en las empresas. En muchas de ellas, cuando los recursos no están para levantar una aplicación más completa o simplemente no está el tiempo, una buena alternativa es realizar un buscador en Excel, el cual, a partir de un dato, pueda encontrar otros relacionados.

Fórmula para buscar datos en Excel Una de las acciones más comunes en Excel es la de buscar un valor dentro de una columna, razón por la cual la función BUSCARV es una de las funciones más utilizadas, sin embargo, no es la única alternativa que tenemos para buscar en Excel.

Buscar datos en Excel con BUSCARV Para aquellos que apenas inician en Excel haré un breve recordatorio de la función BUSCARV la cual tiene tres argumentos obligatorios y uno opcional.

Facilitador: Dr. Víctor Manuel Barraza Espinoza 1. El primer argumento de la función BUSCARV indica el valor que vamos a buscar. En el ejemplo nos referimos al valor de la celda B1 que es “Diana”.

2. El segundo argumento es el rango donde se realizará la búsqueda y el cual debe cumplir con dos condiciones. La primera condición es que la primera columna del rango debe ser la columna donde se realizará la búsqueda. La segunda condición es que la columna con el valor que necesitamos como resultado debe estar contenida en dicho rango. En nuestro ejemplo, deseamos encontrar el teléfono de “Diana” y por lo tanto la columna F debe estar incluida en el rango de búsqueda.

3. El tercer argumento indica la columna que deseamos obtener como resultado. Nuestro rango (D2:F6) contiene tres columnas, la primera es el Nombre, la segunda el Apellido y la tercera el Teléfono. Ya que necesitamos como resultado la columna Teléfono, es necesario indicar el valor 3 como el tercer argumento de la función.

4. El cuarto argumento de la función BUSCARV es opcional e indica el tipo de búsqueda que deseamos realizar. En nuestro ejemplo utilizamos el valor FALSO para indicar a la función que deseamos una coincidencia exacta del valor buscado.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

35

Como resultado, la función BUSCARV nos devuelve el valor de la columna Teléfono que corresponde a “Diana”. Con solo cambiar el valor en la celda B1 obtendremos el teléfono de la persona indicada:

Desventaja de la función BUSCARV Considerando el mismo ejemplo anterior, si deseo encontrar el teléfono de una persona basado en su apellido, no puedo hacerlo utilizando la misma fórmula para buscar datos ya que la función BUSCARV siempre realizará la búsqueda en la primera columna. Para hacer este tipo de búsqueda debo modificar la fórmula de la siguiente manera:

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Los dos cambios realizados a la fórmula son los siguientes: • El segundo argumento cambió a E2:F6 porque la función BUSCARV siempre busca en la primera columna, y ya que deseo buscar por apellido, la columna E debe ser la primera en el rango de búsqueda.

• Debido a la modificación anterior es necesario cambiar también el tercer argumento de la función ya que el rango de búsqueda se redujo a la columna Apellido y a la columna Teléfono. Ahora nuestro resultado deseado se encuentra en la segunda columna del rango de búsqueda y por lo tanto debo indicar el valor numérico 2 en el tercer argumento.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

36

El hecho de que la función BUSCARV solo nos permita buscar en la primera columna de un rango representa una desventaja para resolver ciertos casos. Por ejemplo, considera el escenario donde deseamos buscar en nuestros datos de ejemplo el nombre de una persona a partir de su apellido. Esto implica buscar un valor en la columna E y devolver el valor correspondiente de la columna D lo cual es imposible de resolver con la función BUSCARV a menos de que intercambiemos el orden de las columnas moviendo la columna Apellido a la izquierda de la columna Nombre.

En ocasiones no es posible realizar estos cambios en el orden de las columnas y por ello es importante conocer y tener presente un método alterno que nos permitirá crear una fórmula para buscar datos en Excel sin hacer uso de la función BUSCARV.

Función COINCIDIR en Excel Antes de continuar debemos comprender el funcionamiento de la función COINCIDIR la cual busca un valor y nos devuelve su posición dentro del rango de búsqueda. Esta función tiene tres argumentos y solo dos de ellos son obligatorios.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

1. El primer argumento de la función COINCIDIR es el valor a buscar. En el ejemplo anterior será el valor de la celda B1.

2. El segundo argumento es el rango de búsqueda y que para nuestro ejemplo son las celdas que contienen todos los nombres.

3. El tercer argumento es opciones y nos permite especificar el tipo de búsqueda a realizar. Para obtener una coincidencia exacta debemos especificar el valor cero.

Como resultado obtenemos el valor 3 que nos indica que “Diana” es el tercer nombre dentro del rango D2:D6. El hecho de obtener esta posición es de mucha ayuda porque si queremos conocer el teléfono de “Diana” debemos obtener el valor en la posición 3 bajo la columna Teléfono y eso es precisamente lo que haremos en el siguiente paso.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

37

Fórmula para buscar datos en Excel Ahora que ya sabemos cómo encontrar la posición de un valor con la función COINCIDIR, solo nos falta encontrar el valor correspondiente en otra columna y para eso utilizamos la función INDICE. La forma más simple de esta función requiere de solo dos argumentos, el primero de ellos es el rango de búsqueda y el segundo será la posición, dentro de dicho rango, que deseamos obtener.

En este ejemplo estamos buscando el teléfono de “Diana” y ya sabemos, por la función COINCIDIR, que se encuentra en la posición 3 bajo la columna Nombre.

Función INDICE sobre los datos de la columna Teléfono y en su segundo argumento le indicamos que deseamos obtener la posición devuelta por la función COINCIDIR. El resultado es correcto y la fórmula recién creada es útil para obtener el teléfono de cualquier otra persona que indiquemos en la celda B1:

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Facilitador: Dr. Víctor Manuel Barraza Espinoza

38

Ventaja de INDICE y COINCIDIR sobre BUSCARV A diferencia de la función BUSCARV, la fórmula con INDICE y COINCIDIR no nos obliga a realizar la búsqueda en la primera columna de un rango y para demostrarlo resolveremos el caso donde deseamos encontrar el nombre de una persona a partir de su apellido.

La diferencia entre esta fórmula y la del ejemplo anterior son los rangos de búsqueda. La función COINCIDIR hace la búsqueda sobre la columna Apellido y devuelve la posición correcta para que la función INDICE devuelva la misma posición, pero bajo la columna Nombre. Este tipo de búsqueda no es posible de realizar con la función BUSCARV porque ya hemos visto que dicha función siempre hace la búsqueda sobre la primera columna del rango.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

La flexibilidad que nos ofrece la función INDICE nos permite utilizarla en otros escenarios para buscar un valor y regresar múltiples resultados. Por otro lado, la función COINCIDIR combinada con la función BUSCARV es una excelente alternativa cuando deseamos hacer una búsqueda sobre una tabla y elegir la columna que necesitamos como resultado a partir de una lista desplegable. Si quieres leer más sobre esta alternativa consulta el artículo Combinar BUSCARV y COINCIDIR. Ahora ya conoces dos alternativas para crear una fórmula para buscar datos en Excel, ya sea utilizando la función BUSCARV o la combinación de las funciones INDICE y COINCIDIR. Experimenta con ambas opciones y elige la que mejor se adapte a tus necesidades de acuerdo a los beneficios que ofrece cada una de ellas.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

39

Buscar un valor y regresar múltiples resultados Una pregunta que recibo frecuentemente es ¿Cómo hacer para que la función BUSCARV regrese múltiples resultados en Excel? La respuesta siempre es la misma: La función BUSCARV regresa un solo valor y no existe manera alguna de modificar su comportamiento, sin embargo, podemos utilizar un método alterno.

Obtener varios resultados en Excel Plantearé el problema de la siguiente manera. Tengo una lista de alumnos con una lista de puntos obtenidos en diferentes fechas. Ahora deseo obtener todos los puntos obtenidos por Carlos a lo largo del tiempo.

Facilitador: Dr. Víctor Manuel Barraza Espinoza Si utilizo la función BUSCARV solamente obtendré el primero de los resultados tal como lo observas en la imagen anterior. Es por esta razón que debemos recurrir a otras funciones para obtener varios resultados en Excel.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

40

Identificar las filas con los resultados El primer paso para regresar múltiples resultados en Excel es encontrar las filas que contienen el valor buscado (que se encuentra en la celda F1), y para ello utilizaré la siguiente fórmula matricial: =SI($A$2:$A$11=$F$1,FILA())

Recuerda que para que una fórmula sea matricial debes pulsar la combinación de teclas Ctrl + Mayus + Entrar al terminar de introducir la fórmula. Para probar la fórmula propuesta selecciono el rango de celdas F2:F11 e introduzco la fórmula en la barra de fórmulas de Excel y pulso Ctrl + Mayus + Entrar para obtener el siguiente resultado:

Facilitador: Dr. Víctor Manuel Barraza Espinoza Observa que solo aquellas filas que tienen el nombre “Carlos” en la columna A son aquellas que tienen un número bajo la columna F. De esta manera hemos encontrado las filas que nos interesan.

Ordenar los resultados Con la fórmula anterior podemos identificar las filas que concuerdan con el valor buscado, pero debemos ordenar esos resultados de manera que los números 3, 5, 6 y 10 no estén separados por los valores FALSO. Podemos utilizar la siguiente fórmula matricial: =K.ESIMO.MENOR(SI($A$2:$A$11=$F$1,FILA()),FILA()-1)

La función K.ESIMO.MENOR nos ayudará a ordenar los datos de manera ascendente. El primer argumento de la función es la misma fórmula del paso anterior y el segundo argumento es la función FILA que nos ayudará a indicar la posición que necesitamos. Observa el resultado de esta fórmula al aplicarla sobre el rango F2:F11.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

41

Puedes observar que el segundo argumento de la función K.ESIMO.MENOR es la función FILA menos 1. La razón de esta operación es que los datos empiezan en la segunda fila de la hoja, pero necesitamos hacer un ajuste, a través de la resta, para que se comience por el número 1.

Encontrar las coincidencias Ahora que ya tenemos los números de las filas que nos interesan podemos utilizar la función INDICE para obtener los valores de la columna C que contienen los puntos de cada alumno que nos interesa mostrar. Considera la siguiente fórmula matricial:

Facilitador: Dr. Víctor Manuel Barraza Espinoza

=INDICE($C$1:$C$11, K.ESIMO.MENOR(SI($A$2:$A$11=$F$1,FILA()),FILA()-1))

El segundo argumento de la función INDICE es la misma fórmula del paso anterior.

En el primer argumento he colocado el rango que contiene los puntos que deseo obtener. No debes olvidar pulsar la combinación de teclas Ctrl + Mayus + Entrar para crear la fórmula matricial:

De esta manera hemos podido conocer todos los puntos de Carlos utilizando un solo término de búsqueda, pero obteniendo múltiples resultados.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

42

Corrección de errores Como puedes ver en la última fórmula obtenemos varios mensajes de error #¡NUM!, así que un último paso sería utilizar la función SI.ERROR para evitar desplegar los mensajes de error. Con la siguiente fórmula elimino dichos mensajes: =SI.ERROR(INDICE($C$1:$C$11,K.ESIMO.MENOR(SI($A$2:$A$11=$F$1,FILA()), FILA()-1)),"")

De igual manera debo introducir esta fórmula como una fórmula matricial para obtener el resultado deseado:

Facilitador: Dr. Víctor Manuel Barraza Espinoza Con nuestra fórmula establecida podremos realizar fácilmente la búsqueda de cualquier otro nombre y obtendremos todos sus puntos:

Aunque no existe una función que nos devuelva varios resultados con una sola consulta, es posible utilizar un conjunto de funciones que nos permitirán buscar un valor y regresar múltiples resultados en Excel tal como lo hemos visto en esta ocasión.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

43

Combinar BUSCARV y COINCIDIR La función BUSCARV es de gran utilidad para buscar valores dentro de una tabla y hoy les mostraré cómo podemos mejorar la búsqueda utilizando la función COINCIDIR la cual nos permitirá asociar una lista de validación de datos a la función BUSCARV. Empezaremos por definir nuestra tabla de datos que tendrá en la columna A la lista de libros de Excel que están a la venta y los montos para cada mes en las columnas subsecuentes.

Facilitador: Dr. Víctor Manuel Barraza Espinoza Si deseo conocer las ventas del libro “Excel en 24 Horas” para el mes de Marzo puedo utilizar la siguiente fórmula: =BUSCARV("Excel en 24 horas",A5:F14,4,FALSO)

Al colocar esta fórmula en la celda E1 obtengo el resultado esperado:

La fórmula me da el resultado correcto, pero ¿Qué sucede si ahora necesito las ventas del mes de Enero? ¿Y después las ventas del mes de Febrero?

Facilitador: Dr. Víctor Manuel Barraza Espinoza

44

Crear una lista de validación Para evitar la edición de la función BUSCARV cada vez que necesitamos un dato diferente podemos crear una lista de validación con las posibles opciones para los meses. La lista de validación la creamos desde la ficha Datos y el botón Validación de datos.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Para este ejemplo he colocado la lista de validación en la celda B1. Para poder utilizar esta lista dentro de la función BUSCARV debo utilizar la función COINCIDIR la cual buscará el valor seleccionado en la lista y lo comparará con la matriz A4:F4.

Observa que he remplazado el tercer argumento de la función BUSCARV con la función COINCIDIR de la siguiente manera: COINCIDIR(B1,A4:F4,0)

El resultado de la función COINCIDIR me dará el número de columna que corresponde al mes seleccionado en la lista de la celda B1.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

45

Última mejora a la función BUSCARV Una pequeña mejora que aún podemos hacer es agregar otra lista de validación con los nombres de los libros y utilizar dicho valor en el primer argumento de la función BUSCARV.

De esta manera al seleccionar un valor diferente de cualquiera de las listas obtendremos el resultado correspondiente obtenido por la función BUSCARV.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Facilitador: Dr. Víctor Manuel Barraza Espinoza

46

EJERCICIO 2.- Cómo hacer un buscador en Excel Procedimiento:

Para realizar este buscador, nos basaremos en una función de Excel llamada buscarv la cual, valga la redundancia, busca un valor y retorna los correspondientes a la misma fila. Por esto es importante que el valor a buscar sea único dentro del rango, como por ejemplo un código.

1.- Lo primero que haremos, será en una hoja, ordenar y rotular nuestros datos:

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Lo más importante a tener en cuenta es que el dato que buscaremos, el cual debe ser único, en este caso el código, debe estar en la primera columna a la izquierda.

2.- A continuación, en una nueva hoja, construiremos la “interfaz” de nuestro buscador, el cual podemos hacer mediante la edición y color de las celdas, quedando algo así:

Facilitador: Dr. Víctor Manuel Barraza Espinoza

47

La celda blanca superior E5, Ingrese código a buscar es donde ingresaremos el dato que buscaremos en nuestra hoja de detalle, y las celdas inferiores blancas es donde aparecerá la información correspondiente a la misma fila.

3.- Ahora, nos desplazamos a la celda blanca D7, y procedemos a escribir la fórmula para la búsqueda. Como mencionamos antes, utilizaremos la función buscarv de Excel, la cual utiliza los siguientes parámetros: • Valor buscado: Representa el valor que buscaremos, o sea el código.

• Matriz buscar en: Representa la matriz de datos en donde buscaremos el parámetro, o sea, el grupo de datos que se encuentra en la otra hoja. Lo más importante a tener en cuenta es que en este rango de matriz debemos.

• Indicador columnas: Representa la columna donde está el dato que recuperaremos. Estas columnas se ordenan numéricamente del 1 en adelante. Como el nombre es la segunda columna, ponemos el número 2.

• Ordenado: Indica el criterio de búsqueda. Puede tomar 2 valores: Verdadero o Falso. La diferencia radica en que Verdadero se refiere a una búsqueda aproximada y Falso a una búsqueda exacta. Utilizaremos falso, ya que queremos buscar el código exacto.

Con esto en cuenta, nuestra formula quedaría: =BUSCARV(E5,Hoja2!A2:E11,2,FALSO)

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Nota: Recuerden que las columnas pueden variar dependiendo del orden que le hayan dado en su archivo.

4.- Al terminar la fórmula, nos aparecerá lo siguiente en la celda:

Esto es normal, ya que como no hemos ingresado un dato a buscar, no hay referencia a recuperar.

5.- Luego copiamos la formula a las otras celdas, cambiando el indicador de columna a la que corresponde en la matriz de datos y deberíamos tener algo como esto en las fórmulas: =BUSCARV(E5,Hoja2!A2:E11,2,FALSO) =BUSCARV(E5,Hoja2!A2:E11,3,FALSO) =BUSCARV(E5,Hoja2!A2:E11,4,FALSO) =BUSCARV(E5,Hoja2!A2:E11,5,FALSO) Y en cada celda se debería haber puesto el valor #N/A.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

48

6.- A continuación, llega el momento de probar. En la parte superior ingresamos un código y podemos ver el resultado de la Búsqueda y como se llenan los datos:

Con esto tenemos nuestro buscador funcionando.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Facilitador: Dr. Víctor Manuel Barraza Espinoza

49

Tip’s de seguridad Si el buscador lo estamos habilitando para otras personas y no queremos que vean la matriz completa de datos (Tabla de datos de la Hoja2), ni que puedan modificar las fórmulas, podemos aplicar los siguientes trucos.

Ocultando la matriz de datos Simplemente hacemos click con el botón derecho sobre la pestaña Hoja2 donde están los datos y seleccionamos la opción Ocultar.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Y desaparecerá la Hoja2.

(Para desocultar damos click derecho mostrar, Hoja2)

Facilitador: Dr. Víctor Manuel Barraza Espinoza

50

Protegiendo los datos Por defecto todas las celdas de la hoja están bloqueadas, pero esto no se activa hasta que protejamos la hoja.

Esto lo haremos mediante el bloqueo de celdas. Para eso vamos a la celda blanca E5, donde introducimos el código a buscar, hacemos click con el botón derecho y seleccionamos Formato de celdas. Una vez ahí, vamos a la pestaña Proteger y deseleccionamos la opción Bloqueada, Aceptar.

Ahora vamos a Revisar y seleccionamos la opción Proteger hoja:

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Ahí seleccionamos las opciones que le permitiremos ejecutar a los usuarios. En este caso solo les permitiremos seleccionar las celdas desbloqueadas.

Aquí únicamente van a poder introducir el código o en nuestro caso seleccionar el código. Porque ese es el permiso que tienen….

Facilitador: Dr. Víctor Manuel Barraza Espinoza

51

Adicionalmente definimos una contraseĂąa para que la hoja no pueda ser desbloqueada a no ser que sea por nosotros.

Click en el menĂş Revisar, capturar la contraseĂąa, aceptar, Confirmar contraseĂąa, Aceptar.

Y Listo ya estĂĄ protegida nuestra hoja con contraseĂąa.

Por lo tanto, sĂłlo podrĂĄn tener acceso a la celda E5 para capturar el cĂłdigo, ademĂĄs no pueden desbloquear la hoja otros usuarios porque les va a pedir la contraseĂąa â&#x2DC;šđ&#x;&#x2DC;&#x160;đ&#x;&#x2DC;&#x160;đ&#x;&#x2DC;&#x160;đ&#x;&#x2DC;&#x160;.

Facilitador: Dr. VĂ­ctor Manuel Barraza Espinoza

Para volver a la normalidad, ir al MenĂş Revisar, click en Desproteger hoja, capturar su contraseĂąa, aceptar, y listo ya estĂĄ desbloqueada la Hoja.

Y asĂ­ finalizamos nuestro buscador en Excel, el cual nos permitirĂĄ disponer de una sencilla aplicaciĂłn para los casos en que necesitemos generar algo rĂĄpidamente.

Facilitador: Dr. VĂ­ctor Manuel Barraza Espinoza

52

Gestión Base de datos en Excel 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.

Si observas con detenimiento la imagen anterior, notarás que una tabla se asemeja mucho a una hoja de Excel la cual también está dividida en columnas y filas. Por esa razón Excel ha sido utilizado por mucho tiempo, y por muchas personas, como un repositorio de datos.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

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.

Como ejemplo analizaremos el caso de una pequeña librería que desea crear un catálogo de sus libros en Excel, así como tener una lista de sus clientes y las órdenes de compra. Antes de crear la base de datos en Excel comenzaremos por crear el diseño siguiendo los pasos descritos en el artículo Diseño de bases de datos.

Paso 1: Identificar las tablas de la base de datos Nuestro primer paso en el diseño de una base de datos en Excel será identificar las tablas que ocuparemos para organizar la información. Para nuestro ejemplo de la librería podemos mencionar las siguientes tablas:

Facilitador: Dr. Víctor Manuel Barraza Espinoza

53

Para identificar adecuadamente las tablas de una base de datos debes poner atención en las agrupaciones naturales de la información, es decir, en los sujetos que interactúan en la problemática analizada. En este caso los sujetos principales son los libros, los clientes y las órdenes.

Paso 2: Determinar los campos de las tablas Una vez que hemos identificado las tablas debemos indicar el nombre de cada una de sus columnas (campos). El nombre del campo deberá ser descriptivo y nos ayudará a identificar cada una de las propiedades de un elemento de la tabla.

Por ejemplo, para cada libro en venta en la librería guardaremos su código ISBN, el título, el autor y el precio por lo que dicha tabla quedará definida de la siguiente manera:

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Los campos de cada tabla dependerán de la información que está a tu disposición para ser ingresada en la base de datos. Por ejemplo, algunas empresas o negocios tendrán interés en capturar más información sobre sus clientes lo cual hará que su tabla de Clientes tenga más campos que los mostrados en el ejemplo anterior.

Paso 3: Identificar la llave primaria de cada tabla Una llave primaria nos permitirá identificar de manera única cada uno de los registros de las tablas. En el caso de la tabla Libros, el ISBN será la llave primaria ya que es un código único internacional para cada libro publicado por lo que dicho número jamás se repetirá.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

54

En el caso de las tablas para Órdenes y Clientes crearemos un número consecutivo que identificará de manera única cada una de las filas.

Paso 4: Identificar las relaciones entre tablas Este paso es de utilidad para asegurarnos que podremos vincular la información de las tablas a través de la relación que existe entre dos de sus campos. Por ejemplo, si para una determinada orden de compra quiero saber el título del libro que ha sido vendido, bastará con relacionar la columna ISBN de la tabla Órdenes con la columna ISBN de la tabla Libros para conocer el título.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

De manera similar, si deseo conocer el cliente para una determinada orden de compra, solo deberé seguir la relación indicada por el campo IDCliente en ambas tablas.

Paso 5: Identificar datos repetidos en tablas Aun cuando pensemos que hemos terminado con el diseño de nuestra base de datos, es importante hacer un breve ejercicio con algunos datos reales para identificar la posible existencia de datos repetidos y tomar las decisiones adecuadas para evitarlos.

Por ejemplo, considera el caso de la tabla Libros donde podríamos tener uno o más títulos escritos por el mismo autor. En ese caso, estaríamos repitiendo el nombre y apellido del autor en varios registros de la tabla.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

55

Para evitar la repetición de datos lo recomendable es crear una nueva tabla que almacene la información de los autores y hacer referencia a dicha tabla a través de su campo llave tal como se muestra en la siguiente imagen:

De esta manera evitaremos la repetición en el nombre de los autores y también se evitará la captura de un sin número de variantes del mismo. Para seguir con nuestro análisis, haremos una captura de datos de prueba de una orden, por ejemplo:

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Estos son los datos de la orden de compra número 1 donde el cliente ha comprado 3 libros, siendo dos de esas copias del mismo título. El problema con estos datos es que se repite el número de orden y eso no sería posible ya que esa es nuestra llave primaria en la tabla y no puede repetirse. Además, para cada registro se repite la Fecha, así como las columnas IDCliente y TipoPago. Para resolver este problema debemos crear una tabla adicional que almacenará los libros de cada orden de compra de la siguiente manera:

Facilitador: Dr. Víctor Manuel Barraza Espinoza

56

Es así como el haber identificado datos repetidos en las tablas nos ha hecho tomar la decisión de agregar tablas adicionales a nuestro diseño para minimizar el espacio utilizado por nuestra base de datos que será de gran importancia al momento de que los datos crezcan. De esta manera nuestro diseño final queda de la siguiente manera:

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Facilitador: Dr. Víctor Manuel Barraza Espinoza

57

Crear la base de datos 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 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:

Facilitador: Dr. Víctor Manuel Barraza Espinoza

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)

Facilitador: Dr. Víctor Manuel Barraza Espinoza

58

Esta fórmula buscará el valor de la columna IDCliente, que pertenece a la tabla Órdenes, 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 permiten crear fórmulas para buscar datos.

Antes de finalizar este artículo, es necesario hacer algunas aclaraciones importantes sobre las bases de datos en Excel, pero para eso necesito explicar un poco sobre los sistemas de gestión de bases de datos.

Sistemas de gestión de bases de datos (DBMS) Varias décadas atrás, cuando las computadoras comenzaron a hacerse más rápidas y eficientes en el manejo de información, se hizo necesaria la creación de un sistema que se encargase de las operaciones con los datos. Desde la década de los años sesenta se crearon los primeros sistemas de gestión de bases de datos (DBMS por sus siglas en inglés), cuya finalidad era facilitar el almacenamiento, modificación y extracción de los datos.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

El objetivo principal de un DBMS es coordinar y proteger el acceso a la información y dicho objetivo se logra porque los usuarios o aplicaciones solo pueden obtener datos utilizando alguna interfaz o lenguaje de alto nivel como SQL y el sistema de gestión de bases de datos se encargará de devolver la información solicitada ya que el usuario, o aplicación, jamás tiene contacto directo con los datos.

Es muy común que en el campo de la informática se hable de que una base de datos debe tener un DBMS para ser considerada como una verdadera base de datos. Un ejemplo de un DBMS es el producto de Microsoft conocido como SQL Server que es un motor de base de datos diseñado para el ambiente empresarial.

Excel no es un DBMS Aunque exista una gran cantidad de personas que utilizamos Excel para almacenar información, eso no lo convierte en un sistema de gestión de bases de datos. Excel

Facilitador: Dr. Víctor Manuel Barraza Espinoza

59

no tiene un servicio que controle la inserción o eliminación de los datos, sino que el mismo usuario puede hacerlo directamente en la aplicación.

Algunas desventajas que tenemos al utilizar Excel para almacenar nuestros datos son las siguientes: • Solo un usuario puede acceder a la información al mismo tiempo.

• Excel comenzará a ponerse lento cuando la base de datos crezca en gran manera.

• No es posible establecer un nivel de seguridad avanzado como para proteger ciertos datos de ciertos usuarios.

Conclusión: 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.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Mi recomendación es que, si te encuentras con que alguna de las desventajas de Excel te está causando dolores de cabeza con tu trabajo, entonces considera fuertemente la opción de utilizar algún DBMS como Microsoft SQL Server o Access.

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

Facilitador: Dr. Víctor Manuel Barraza Espinoza

60

Gestión de tablas de datos Una de las actividades que realizará con frecuencia en Excel, será ordenar y gestionar listas de datos. Los datos están organizados dentro de una lista con cabeceras de columna que definen campos y filas que contienen registros. Un campo es un elemento específico de información de su lista de datos como, por ejemplo, nombre, matrícula, puesto y sueldo. Un registro contiene toda la información sobre un apartado concreto de su lista.

Se denomina base de datos a una lista completa de datos organizada en campos y registros como, por ejemplo, la lista de empleados de su empresa.

Siempre que tenga una base de datos con personas, lugares o productos, necesita una forma de filtrarlo todo y quedarse con la información específica que necesita en cada momento. También necesita poder ordenar los datos según criterios concretos.

Por ejemplo, si necesita preparar un informe sobre los empleados que trabajan en el departamento administrativo, tendría que visualizar en su lista únicamente dicho departamento. Excel proporciona dos herramientas útiles para localizar la información específica de una lista: el filtrado y la ordenación. Con estas herramientas, podrá ordenar y filtrar fácilmente los datos de su hoja.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Ejercicio: Hacer una agenda personal familiar

Organizar la tabla de datos: • Orden alfabético por apellidos • Crear Autofiltros • Crear Filtros Avanzados • Buscar datos • Crear con formato condicional, otros…

Facilitador: Dr. Víctor Manuel Barraza Espinoza

61

Filtros en Excel Los filtros en Excel nos permiten buscar un subconjunto de datos que cumpla con ciertos criterios. Generalmente todo comienza cuando tenemos un rango de celdas con información y queremos ver solamente aquellas filas que cumplen con ciertas condiciones.

Por ejemplo, en la siguiente imagen se pueden ver los datos de ventas de una empresa. ¿Cómo puedo tener una vista con todas las filas que pertenecen a Hugo?

Eso sería una tarea muy difícil de lograr si no tuviéramos la facilidad de crear filtros en Excel.

Facilitador: Dr. Víctor Manuel Barraza Espinoza Cómo crear filtros en Excel Para crear un filtro podemos utilizar el comando Filtro que se encuentra en la ficha Datos dentro del grupo Ordenar y filtrar.

Al pulsar el botón Filtro se colocarán flechas en el extremo derecho de cada uno de los encabezados de columna de nuestros datos indicando que podemos hacer uso de los filtros. El comando Filtro también podrás seleccionar desde Inicio > Modificar > Ordenar y filtrar > Filtro.

Otra manera de crear un filtro es transformar nuestros datos en una tabla de Excel, lo cual insertará los filtros además de aplicar un formato especial a los datos.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

62

Cómo usar los filtros en Excel Para filtrar la información debemos elegir una columna y hacer clic en la flecha de filtro correspondiente para mostrar las opciones de filtrado. Todos los filtros, en la parte inferior, mostrarán una lista de valores únicos con una caja de selección a la izquierda de cada uno.

Una opción que tenemos para filtrar los datos es elegir de manera individual aquellos valores que deseamos visualizar en pantalla. También podemos utilizar la opción (Seleccionar todo) para marcar o desmarcar todos los elementos de la lista.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

En la imagen anterior he elegido el nombre Hugo de manera que el filtro mostrará solamente las filas con dicho nombre.

Al pulsar el botón Aceptar se ocultarán las filas que no cumplen con el criterio de filtrado establecido. Observa que la flecha de filtro de la columna Vendedor ha cambiado para indicarnos que hemos aplicado un filtro. Además, los números de fila de Excel se muestran en un color diferente indicándonos que existen filas ocultas.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

63

Filtrar por varias columnas Si queremos segmentar aún más los datos mostrados en pantalla podemos filtrar por varias columnas. En el ejemplo anterior filtré las filas pertenecientes a Hugo, pero si además necesito saber las que pertenecen a la región Norte y Sur, entonces debo seleccionar dichas opciones dentro del filtro de la columna Región:

Al aceptar estos cambios se mostrarán solamente las filas que cumplen ambos criterios. Observa que ambas columnas habrán cambiado sus iconos para indicarnos que se ha aplicado un filtro en cada una de ellas.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Esto demuestra que es posible crear tantos filtros como columnas tengamos en nuestros datos y entre más criterios de filtrado apliquemos mucha mayor será la segmentación de datos que obtendremos.

Cómo quitar un filtro en Excel Para quitar un filtro aplicado a una columna debemos hacer clic en la flecha del filtro y seleccionar la opción Borrar filtro de “Columna” donde Columna es el nombre de la columna que hemos elegido. Esta acción eliminará el filtro de una sola columna, pero si tenemos filtros aplicados a varias columnas y deseamos eliminarlos todos con una sola acción, entonces debemos pulsar el comando Borrar que se encuentra en la ficha Datos > Ordenar y filtrar.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

64

Filtrar en Excel buscando valores Ya hemos visto que todos los filtros muestran una lista de valores únicos de la cual podemos seleccionar uno o varios de ellos y justo por arriba de dicha lista de valores se muestra un cuadro de texto que nos permite hacer una búsqueda. Por ejemplo, en la siguiente imagen he colocado la palabra “este” en el cuadro de búsqueda y como resultado se ha modificado la lista de valores mostrando solo aquellos donde se ha encontrado dicha palabra:

Cuando tenemos una lista muy grande de valores únicos y no podemos identificar fácilmente aquellos que deseamos seleccionar, podemos utilizar el cuadro de búsqueda para encontrar los valores que necesitamos. También es posible utilizar caracteres comodines como el asterisco (*) o el símbolo de interrogación (?) tal como si hiciéramos una búsqueda aproximada en Excel de manera que podamos ampliar los resultados de búsqueda.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Filtros de texto en Excel Además de las opciones ya mencionadas para filtrar en Excel, cuando en una columna se detecta el tipo de dato texto, se mostrará una opción de menú llamada Filtros de texto como la siguiente:

Facilitador: Dr. Víctor Manuel Barraza Espinoza

65

Al elegir cualquiera de estas opciones se mostrará un cuadro de diálogo que nos permitirá configurar cada uno de los criterios disponibles. Por ejemplo, al elegir la opción Comienza por se mostrará el siguiente cuadro de diálogo:

Si colocamos la letra “a” en el cuadro de texto junto a la opción “comienza por”, entonces Excel mostrará solamente los elementos de la columna Vendedor que comiencen por la letra “a”.

Filtros de número en Excel De manera similar, si Excel detecta que una columna contiene valores numéricos, nos permitirá utilizar filtros específicos para dicho tipo de dato tal como lo puedes observar en la siguiente imagen:

Facilitador: Dr. Víctor Manuel Barraza Espinoza

A diferencia de los Filtros de texto, Excel nos permitirá utilizar los Filtros de número para mostrar valores que sean mayores o iguales que otro o simplemente aquellos que son superiores al promedio.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

66

Filtros de fecha en Excel Las fechas son el tipo de dato que más opciones de filtrado nos proporcionan, tal como lo muestra la siguiente imagen:

Excel nos permitirá filtrar las fechas por días específicos como hoy, mañana o ayer e inclusive por períodos de tiempo más largos como semanas, meses, trimestres o años con tan solo seleccionar la opción adecuada.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Filtrar por color en Excel No podíamos pasar por alto y dejar de hablar de la opción de Filtrar por color que nos ofrece Excel. Para que esta opción se habilite es necesario que las celdas tengan aplicado un color de relleno ya sea por una regla de formato condicional o modificando directamente el color de relleno con las herramientas de formato. En nuestro ejemplo he aplicado una regla de formato condicional para aquellas celdas que tengan un valor superior a $850 en la columna Total.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

67

Una vez que las celdas tienen un color de relleno, al hacer clic en el filtro de la columna Total se mostrará habilitada la opción Filtrar por color y dentro de ella podré elegir alguno de los colores presentes en la columna.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Facilitador: Dr. Víctor Manuel Barraza Espinoza

68

Filtro avanzado en Excel Aunque los filtros en Excel nos ofrecen una amplia gama de posibilidades para segmentar la información, es posible realizar un filtrado mucho más avanzado si utilizamos el Filtro avanzado.

En Excel puedes realizar un filtrado de datos totalmente personalizado proporcionando los criterios que deseas aplicar a la información. Este tipo de filtrado es conocido como Filtro avanzado y en esta ocasión te mostraré cómo utilizarlo.

Supongamos la siguiente tabla de datos.

Los criterios por los cuales se realizará el filtrado deben especificarse dentro de celdas de la misma hoja. Supongamos que deseo filtrar los registros del departamento de Finanzas.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Para este ejemplo coloqué los criterios por arriba de la tabla de datos, aunque realmente su ubicación no es de importancia.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

69

Aplicar un filtro avanzado a los datos Antes de aplicar el filtro avanzado debo seleccionar la tabla de datos (A4:D13) y posteriormente ir a la ficha Datos y pulsar el botón Avanzadas que se encuentra en el grupo Ordenar y filtrar. Se mostrará el cuadro de diálogo Filtro avanzado.

Observa cómo en el cuadro de texto para Rango de criterios he seleccionado el rango que contiene las condiciones del filtro avanzado necesarias. Solamente resta pulsar el botón Aceptar para aplicar el filtro.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

El resultado es precisamente lo que necesito, así que el filtrado avanzado ha sido exitoso. Pero ahora tengo un desafío mayor ¿Cómo podría hacer para especificar condiciones para una segunda columna? Supongamos que deseo filtrar la información de las personas que tienen apellido Hernández y que además pertenecen al departamento de Finanzas. Para filtrar los datos por estos criterios debo agregar dichas condiciones en otra celda.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

70

Observa cómo el Rango de la lista es el mismo que en el caso anterior ya que son los mismos datos, pero el Rango de criterios ahora abarca también las celdas que contienen el criterio para el Apellido. Al aceptar los cambios Excel aplicará el filtro avanzado adecuadamente.

De la misma manera puedes especificar un criterio para cada columna. Hasta ahora solamente he especificado una sola condición por columna, pero ahora deseo agregar a los resultados del filtro la información del departamento de Informática.

Para ello solamente agregaré una fila adicional al rango de criterios de la siguiente manera.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Al aceptar los cambios Excel filtrará la información de los empleados de Finanzas con apellido Hernández y además mostrará la información de los empleados del departamento de Informática sin importar el apellido que tengan.

Como podrás observar, es factible especificar una condición por cada fila del rango de criterios. De esta manera puedes crear un filtro avanzado en Excel.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

71

Tablas Dinámicas en Excel 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.

Las tablas dinámicas en Excel permiten agrupar datos en una gran cantidad de maneras diferentes para poder obtener la información que necesitamos.

Una tabla dinámica combina y compara en forma rápida grandes volúmenes de datos. Permitiendo el análisis de los datos crear diferentes formas de visualizar reportes con los datos de origen. Yendo desde lo general a lo específico y cambiando el Excel de manera en que la información nos sirva en determinado momento. Las tablas dinámicas, en resumen, sirven para resumir los datos según la consulta realizada.

En esta ocasión veremos un ejemplo claro de cómo crearlas.

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

Facilitador: Dr. Víctor Manuel Barraza Espinoza

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

Facilitador: Dr. Víctor Manuel Barraza Espinoza

72

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.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

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.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

73

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.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Facilitador: Dr. Víctor Manuel Barraza Espinoza

74

Automatizar procesos y datos en Excel Autocompletar en Excel La funcionalidad de Autocompletar es de mucha ayuda cuando estás introduciendo varios datos en una misma columna y Excel se anticipa y realiza una sugerencia del texto a ingresar.

La sugerencia que hace Excel está basada en el texto ingresado previamente.

Observa el siguiente ejemplo:

Una vez que has ingresado un texto en la primera celda y se comienza a introducir un texto similar en la segunda celda, Excel reconoce el mismo patrón de caracteres y hace la sugerencia para el resto del texto.

Para aceptar la sugerencia realizada por Excel es suficiente con pulsar la tecla Entrar y la celda tendrá el texto sugerido sin la necesidad de introducir el resto de las letras. Por otro lado, si no deseas aceptar la sugerencia hecha por el Autocompletar simplemente debes continuar introduciendo los caracteres y al finalizar pulsar la tecla Entrar.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Para deshabilitar o habilitar: Click Botón Archivo, botón Opciones de Excel, Avanzadas, activar o desactivar casilla de verificación, Habilitar Autocompletar para valores en celda

Facilitador: Dr. Víctor Manuel Barraza Espinoza

75

Autocompletar datos previamente capturados Ejemplo: Una vez que tenemos los datos capturados, nos colocamos en la celda inmediata debajo de los nombres, presionamos Alt + flecha hacia abajo y seleccionamos el dato que queramos.

Otros Autocompletar:

Facilitador: Dr. VĂ­ctor Manuel Barraza Espinoza

Seleccionar y arrastrar para ver resultadosâ&#x20AC;Ś

Facilitador: Dr. VĂ­ctor Manuel Barraza Espinoza

76

Construcción de base de datos para automatizar procesos de cálculo, fórmulas y protección de datos en la empresa Paso 1.- Tener una Tabla de Datos Abra un Libro nuevo, en la Hoja1 Capture la siguiente tabla de datos:

Facilitador: Dr. Víctor Manuel Barraza Espinoza Cambie de nombre a la Hoja1 por Datos Paso 2.- Tener la Interfaz del Sistema de Búsqueda En la Hoja2 capture la siguiente Interfaz para nuestro Buscador automatizado

Facilitador: Dr. Víctor Manuel Barraza Espinoza

77

Cambie de nombre a la Hoja2 por Automatizar Paso 3.- Crear las Listas de Validación de Datos, para automatizar el Sistema de Búsqueda de Datos: En B3 crear una lista de validación para obtener los Clientes de la hoja datos Procedimiento: • Click en B3, click en el menú Datos, validar datos, validar datos, en la opción Permitir (Seleccionar Lista), Seleccionar el origen de los Clientes =Datos!D2:D6, Aceptar.

• Listo.

•

Hacer lo mismo con las celdas (B5, A8, B8 y D8) Mes de compra, Cantidad, Producto y Forma de Pago,

Ya tenemos las 5 listas de validación de datos…

Paso 4.- Desarrollar las fórmulas para el cálculo de datos y obtención de resultados

Facilitador: Dr. Víctor Manuel BarrazaSUMA, Espinoza RESTA,

Fórmulas a desarrollar: (SI, SI ANIDADAS, BUSCARV, MULTIPLICACIÓN, PORCENTAJES, MACROS, MODULOS).

Desarrollar fórmulas en C8, E7, E8, B10, B12, B14, B16, B18, B20 Nota: Identificar el tipo de resultado para poder implementar las fórmulas…

=BUSCARV(B8,Datos!B2:E6,2,FALSO) =SI(D8="Contado","Descontar",SI(D8="Crédito","Aumentar",FALSO)) =SI(D8="Contado",0.1,SI(D8="Crédito",0.2,)) =(A8*C8) =SI(D8="Contado",B10*E8,"0") =SI(D8="Crédito",B10*E8,"0") =(B10-B12)*0.16 =(B10-B12)+(B14+B16) =PesosMN(B18) Código fuente para la Macro (Módulo) PesosMN: Function PesosMN(tyCantidad As Currency) As String

Facilitador: Dr. Víctor Manuel Barraza Espinoza

78

Dim lyCantidad As Currency, lyCentavos As Currency, lnDigito As Byte, lnPrimerDigito As Byte, lnSegundoDigito As Byte, lnTercerDigito As Byte, lcBloque As String, lnNumeroBloques As Byte, lnBloqueCero Dim laUnidades As Variant, laDecenas As Variant, laCentenas As Variant, I As Variant 'Si esta como Option Explicit tyCantidad = Round(tyCantidad, 2) lyCantidad = Int(tyCantidad) lyCentavos = (tyCantidad - lyCantidad) * 100 laUnidades = Array("UN", "DOS", "TRES", "CUATRO", "CINCO", "SEIS", "SIETE", "OCHO", "NUEVE", "DIEZ", "ONCE", "DOCE", "TRECE", "CATORCE", "QUINCE", "DIECISEIS", "DIECISIETE", "DIECIOCHO", "DIECINUEVE", "VEINTE", "VEINTIUN", "VEINTIDOS", "VEINTITRES", "VEINTICUATRO", "VEINTICINCO", "VEINTISEIS", "VEINTISIETE", "VEINTIOCHO", "VEINTINUEVE") laDecenas = Array("DIEZ", "VEINTE", "TREINTA", "CUARENTA", "CINCUENTA", "SESENTA", "SETENTA", "OCHENTA", "NOVENTA") laCentenas = Array("CIENTO", "DOSCIENTOS", "TRESCIENTOS", "CUATROCIENTOS", "QUINIENTOS", "SEISCIENTOS", "SETECIENTOS", "OCHOCIENTOS", "NOVECIENTOS") lnNumeroBloques = 1 Do lnPrimerDigito = 0 lnSegundoDigito = 0 lnTercerDigito = 0 lcBloque = ""

Facilitador: Dr. Víctor Manuel Barraza Espinoza

lnBloqueCero = 0 For I = 1 To 3

lnDigito = lyCantidad Mod 10 If lnDigito <> 0 Then Select Case I Case 1 lcBloque = " " & laUnidades(lnDigito - 1) lnPrimerDigito = lnDigito Case 2 If lnDigito <= 2 Then lcBloque = " " & laUnidades((lnDigito * 10) + lnPrimerDigito - 1) Else lcBloque = " " & laDecenas(lnDigito - 1) & IIf(lnPrimerDigito <> 0, " Y", Null) & lcBloque End If lnSegundoDigito = lnDigito Case 3 lcBloque = " " & IIf(lnDigito = 1 And lnPrimerDigito = 0 And lnSegundoDigito = 0, "CIEN", laCentenas(lnDigito - 1)) & lcBloque lnTercerDigito = lnDigito End Select Else lnBloqueCero = lnBloqueCero + 1 End If

Facilitador: Dr. Víctor Manuel Barraza Espinoza

79

lyCantidad = Int(lyCantidad / 10) If lyCantidad = 0 Then Exit For End If Next I Select Case lnNumeroBloques Case 1 PesosMN = lcBloque Case 2 PesosMN = lcBloque & IIf(lnBloqueCero = 3, Null, " MIL") & PesosMN Case 3 PesosMN = lcBloque & IIf(lnPrimerDigito = 1 And lnSegundoDigito = 0 And lnTercerDigito = 0, " MILLON", " MILLONES") & PesosMN End Select lnNumeroBloques = lnNumeroBloques + 1 Loop Until lyCantidad = 0 PesosMN = "SON: (" & PesosMN & IIf(tyCantidad > 1, " PESOS ", " PESO ") & Format(Str(lyCentavos), "00") & "/100 M.N.)" End Function

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Facilitador: Dr. Víctor Manuel Barraza Espinoza

80

Al aplicar las fórmulas, estaremos automatizando y manipulando los datos acordes a nuestras necesidades, y obtendremos los siguientes resultados…

Ver Interfaz terminada en la siguiente figura: Resultados obtenidos:

Facilitador: Dr. Víctor Manuel Barraza Espinoza Paso 5.- Protección de nuestro Sistema de Búsqueda • Ocultar la hoja Datos (Botón derecho sobre la Hoja Datos, Ocultar) • Desbloquear las celdas B3, B5, A8, B8 y D8 (Botón derecho en cada una de ellas empezamos por B3 y seleccionar Formato de celdas, Click en la pestaña Proteger, desactivar la casilla Bloquear, Aceptar, repetir los pasos con las demás celdas…

• Click en el Menú Revisar, Proteger Hoja, activar sólo Seleccionar celdas desbloqueadas, Aceptar.

•

Con este Bloqueo, solo podrán tener acceso a las celdas B3, B5, A8, B8 y D8.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

81

â&#x20AC;˘

Crear ContraseĂąa, para que no puedan desproteger la hoja Automatizar (Click en el MenĂş Revisar, Proteger Hoja, activar sĂłlo Seleccionar celdas desbloqueadas, Capturar la ContraseĂąa que desee, Aceptar, Repetir ContraseĂąa, Aceptar, Listo ya quedamos protegidos đ&#x;&#x2DC;&#x160;.

Facilitador: Dr. VĂ­ctor Manuel Barraza Espinoza

Facilitador: Dr. VĂ­ctor Manuel Barraza Espinoza

82

Minigráficos en Excel Los Minigráficos de Excel son gráficos muy pequeños que caben dentro de una celda y nos permiten mostrar el comportamiento de los datos a través del tiempo o representar las variaciones que existen en la información.

Es importante saber que, a diferencia de los gráficos de Excel, los Minigráficos solo pueden representar una serie de datos a la vez. Los comandos que nos permiten crear los Minigráficos se encuentran en la ficha Insertar y dentro del grupo Minigráficos.

Tipos de Minigráficos en Excel Existen tres tipos diferentes de Minigráficos en Excel 2013 y son los siguientes: • Línea: Similar a un gráfico de línea • Columna: Similar al gráfico de columnas.

• Ganancia o pérdida: Este tipo de Minigráficos no tiene parecido con ningún gráfico de Excel ya que solo representa dos tipos de valores: ganancias o pérdidas. Un cambio positivo en los datos será representado como ganancia mientras que un cambio negativo como pérdida.

En la siguiente imagen puedes observar cada uno de los tipos de Minigráficos en el mismo orden en que fueron mencionados:

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Facilitador: Dr. Víctor Manuel Barraza Espinoza

83

Cómo crear un Minigráficos Para crear un Minigráfico debemos seleccionar primero los datos que serán graficados. Es importante seleccionar solamente los datos, sin títulos de columna ni etiquetas. Posteriormente ir a la ficha Insertar y dentro del grupo Minigráficos pulsar el botón correspondiente al tipo de minigráfico que deseamos crear lo cual mostrará el cuadro de diálogo Crear grupo de Minigráfico.

Dentro del cuadro de diálogo debemos seleccionar el rango de celdas donde serán colocados los Minigráficos y pulsar el botón Aceptar. Cada minigráfico quedará vinculado a los datos y si existe alguna modificación, el minigráfico será actualizado automáticamente.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Si al momento de crear los Minigráficos has seleccionado varias filas (o columnas) de datos, Excel creará un grupo de Minigráficos. Una manera rápida de saber si los Minigráficos están agrupados es seleccionando uno de ellos y Excel colocará un borde y un color de fondo especial sobre todos los Minigráficos del mismo grupo.

Una ventaja de tener los Minigráficos agrupados es al momento de editarlos ya que cualquier cambio en el estilo será aplicado de manera automática a todo el grupo.

Sin embargo, si quieres desagrupar los Minigráficos para tratarlos de manera individual, puedes hacerlo con el comando Herramientas para minigráfico > Diseño > Agrupar > Desagrupar.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

84

Cambiar el tipo de minigráfico Si después de crear los Minigráficos decides que no son del tipo adecuado, no es necesario eliminarlos y volver a crearlos, sino simplemente cambiar su tipo. Este cambio lo podemos hacer seleccionando el grupo de Minigráficos y en Herramientas para minigráfico, dentro del grupo Tipo, elegir el nuevo tipo.

Editar un minigráfico Una de las modificaciones que podemos hacer a un grupo de Minigráficos es cambiar su estilo, es decir, los colores utilizados en el minigráfico. Puedes encontrar y seleccionar un estilo diferente desde Herramientas para minigráfico > Estilo.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Además de poder elegir un nuevo estilo, también podemos mostrar puntos específicos dentro del minigráfico como los siguientes: • Punto alto: Aplica un color diferente al dato de mayor magnitud.

• Punto bajo: Aplica un color diferente al dato de menor magnitud.

• Puntos negativos: Aplica un color diferente a todos los valores negativos.

• Primer punto: Resalta el primer punto de la serie.

• Último punto: Resalta el último punto de la serie.

• Marcadores: Sólo disponible para los Minigráficos de línea que coloca marcadores para todos los puntos de la serie.

Cada uno de estos puntos puede mostrase u ocultarse a través de las cajas de selección ubicadas en la ficha Herramientas para minigráfico.

Un tema importante de mencionar al hablar de la edición de un minigráfico es la modificación de su tamaño. La manera de aumentar o disminuir el tamaño de un minigráfico es modificando el ancho o el alto de la celda a la que pertenece ya que el minigráfico se ajustará para ocupar la totalidad de la celda. Por otro lado, ya que es posible colocar un minigráfico dentro de celdas combinadas, podemos

Facilitador: Dr. Víctor Manuel Barraza Espinoza

85

aumentar el tamaño de un minigráfico combinando varias celdas evitando así la modificación del ancho o alto de celdas individuales.

Borrar un minigráfico Un minigráfico no puede ser eliminado con solo seleccionarlo y pulsando la tecla Suprimir. Es necesario seleccionarlo e ir a la ficha Herramientas de minigráfico y pulsar el botón Borrar.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

El botón Borrar nos permite eliminar solo el minigráfico seleccionado, que es la opción predeterminada, o también podemos borrar todo el grupo de minigráficos.

Estas mismas opciones de borrado las encontramos al hacer clic derecho sobre un minigráfico y seleccionando la opción de menú Minigráficos.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

86

Organigramas Desde Excel podemos crear organigramas sin salir de esta aplicación.

Para ello llevamos a cabo el siguiente proceso: 1) Nos ubicamos en la pestaña Insertar de la barra de herramientas

2) Buscamos el ícono SmartArt

3) Una vez pulsado el botón SmartArt, se despliega un menú de opciones de todas las formas que acá se encuentran

Facilitador: Dr. Víctor Manuel Barraza Espinoza

4) Seleccionamos Jerarquía que corresponde a la elaboración de organigramas

Facilitador: Dr. Víctor Manuel Barraza Espinoza

87

5) Luego seleccionamos el tipo de gráfico de jerarquía que queremos

6) Ya una vez seleccionado, se habrá creado en la hoja de trabajo el tipo de organigrama que hemos escogido

Facilitador: Dr. Víctor Manuel Barraza Espinoza

7) Para cambiar el formato de la letra, y seleccionar una mucho más pequeña que la que presenta la plantilla. Seleccionamos cambiar tamaño de fuente de la barra de herramientas

Facilitador: Dr. Víctor Manuel Barraza Espinoza

88

8) Escribimos el organigrama que deseamos presentar, en el caso del ejemplo es uno sencillo que muestra los niveles más importantes

Modificar Organigramas Podemos modificar el organigrama que ya hemos aprendido a crear en la lección anterior.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Por ejemplo, si queremos aumentar un cuadro más que depende del gerente general, realizamos lo siguiente: 1) Nos ubicamos en el rectángulo de la posición de Gerente General y pulsamos el botón derecho del ratón, para desplegar el menú de opciones

Facilitador: Dr. Víctor Manuel Barraza Espinoza

89

2) Seleccionamos Agregar forma, seguido de la alternativa Agregar forma debajo, pues es el nivel que deseamos crear

3) Se habrá creado en nuestro organigrama un rectángulo más bajo el Gerente General

4) Escribimos la nueva posición, Gerente de Compras

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Si deseamos incrementar un nivel más, indicando los vendedores que dependen del Gerente de Ventas, seguimos los pasos a continuación: a) Nos ubicamos en la casilla Gerente de ventas y apretamos el botón derecho del ratón para desplegar el menú de opciones

Facilitador: Dr. Víctor Manuel Barraza Espinoza

90

b) Seleccionamos Agregar forma, y más concretamente Agregar forma debajo

c) Se habrá creado en el Organigrama de nuestra hoja de trabajo un rectángulo más, donde podremos escribir los datos deseados

d) Como son tres vendedores los que dependen del Gerente de Ventas, escribimos esta información y ya tendremos nuestro Organigrama ya modificado

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Facilitador: Dr. Víctor Manuel Barraza Espinoza

91

Auditoría de fórmulas en Excel Excel cuenta con una interesante herramienta llamada "Auditoría de fórmulas", la encuentras en la ficha "Fórmulas", sirve para mostrarnos "gráficamente", con líneas, las celdas precedentes y dependientes de una celda en particular, un ejemplo te dará una idea de su utilidad.

La auditoría de fórmulas en Excel nos ayuda a encontrar un error en caso de fallo de alguna fórmula podamos rastrear el origen de las celdas que forma un formula o realicemos paso a paso una formula, para ver si la fórmula está escrita correctamente.

Recuerda que debemos tener seleccionada la celda a la que queremos auditar.

Vamos a explicar cada una de las opciones: ▪ Rastrear precedentes: muestra unas flechas con las celdas que forman la fórmula de la celda que tenemos seleccionada. Nos es útil para saber si el origen está correcto, es decir, si la referencia es correcta.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Facilitador: Dr. Víctor Manuel Barraza Espinoza

92

▪

Rastrear dependientes: muestran unas flechas con las celdas que dependen de la celda que tenemos seleccionada. Es útil para saber si la procedencia de la celda dependiente es correcta. Es combinable con el anterior.

▪ ▪ ▪

Quitar flechas: lo único que hace es quitar las flechas anteriores.

Mostrar formulas: todas las celdas que sean formulas se muestran.

Comprobación de errores: Hace una comprobación de todos los errores en las fórmulas de la hoja, si hay algún error, podemos ver el error que nos señale.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

En este caso, el error está en el nombre de la formula, que es SUMA no SUM. ▪ Evaluar formula: realiza paso a paso una formula, para comprobar que hemos realizado correctamente la formula. Es útil por si nosotros nos equivocamos al escribir la formula, que no tiene errores de sintaxis, pero no da el resultado que esperamos.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

93

Veamos un ejemplo práctico. Teniendo una formula =6*(5+5)+(8*(5*4)), veamos como lo hace, copiamos la formula en una celda y pinchamos en evaluar formula.

Esto es lo que vemos.

Vemos que se subraya lo que calcula, si vamos pinchando en evaluar, veremos cómo calcula y vemos si es lo que queremos que calcule.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Facilitador: Dr. Víctor Manuel Barraza Espinoza

94

Evaluar fórmulas Existe una herramienta en Excel que nos permite calcular el total de una fórmula obteniendo el resultado de cada una de sus partes paso a paso. Esto nos ayudará a detectar fácilmente cualquier problema en nuestras fórmulas.

Evaluar fórmulas en Excel paso a paso Lo primero que debes hacer es seleccionar la celda que contiene la fórmula que deseas evaluar y posteriormente hacer clic sobre el botón Evaluar fórmula que se encuentra en la ficha Fórmulas dentro del grupo Auditoría de fórmulas.

Se mostrará el cuadro de diálogo Evaluar fórmula y en el cuadro Evaluación se mostrará la fórmula que será evaluada.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Observa que la primera operación dentro de la fórmula aparece subrayada. Esto indica que al pulsar el botón Evaluar se calculará el resultado de dicha operación.

Al hacer clic en Evaluar se reemplaza el texto de la fórmula por su resultado:

Facilitador: Dr. Víctor Manuel Barraza Espinoza

95

De esta manera puedes hacer clic en Evaluar tantas veces como sea necesario hasta obtener el resultado final de la fórmula. Otra característica especial de esta funcionalidad es que al encontrarse con una referencia a una celda se habilita el botón Paso a paso para entrar. En este ejemplo, al encontrarse con la celda A7 se habilita dicho botón.

Al hacer clic sobre el botón Paso a paso para entrar, Excel “investigará” cómo es calculado el valor de la celda y lo mostrará en un recuadro por debajo.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Facilitador: Dr. Víctor Manuel Barraza Espinoza

96

En este ejemplo Excel ha descubierto que la celda A7 contiene el valor 418. Para continuar con la evaluación de la fórmula superior solamente debes hacer clic en el botón Paso a paso para salir. Ahora observa lo que muestra Excel al evaluar la celda A8 de la fórmula:

En este caso la ceda A8 es una celda que contiene una fórmula y Excel nos da la oportunidad de evaluar también dicha fórmula con el botón Paso a paso para entrar. De esta manera puedes llegar a todos los niveles de cálculo involucrados en cualquier fórmula.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

No solamente podremos encontrar fácilmente los errores en nuestras fórmulas al utilizar esta funcionalidad de evaluación, sino que también entenderemos mejor la manera en que Excel está realizando el cálculo de la fórmula.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

97

Excel cuenta con una interesante herramienta llamada "Auditoría de fórmulas", la encuentras en la ficha "Fórmulas", sirve para mostrarnos "gálicamente", con líneas, las celdas precedentes y dependientes de una celda en particular.

Un ejemplo te dará una idea de su utilidad.

Precedentes de la celda "E9"

Facilitador: Dr. Víctor Manuel Barraza Espinoza Dependientes de la celda "B9"

Utiliza el ícono "Quitar flechas" para volver a ver la pantalla en su condición normal, explora el desplegable, te permitirá quitar las precedencias y dependencias por niveles.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

98

Funciones Financieras en Excel Las funciones financieras de Excel te ayudarán en cálculos como el de amortización, la tasa de interés anual efectivo, el interés acumulado, la tasa nominal entre otros cálculos que te ayudarán en tus modelos financieros.

Todos sabemos que, al aceptar un préstamo, debemos de pagarlo de alguna manera. La mejor forma es mediante anualidades o pagos períodos. En dichos planes, nosotros debemos de abonar al saldo principal y también debemos pagar los intereses devengados del saldo. En esta sección, veremos cómo calcular el importe de la amortización de los intereses de los préstamos bancarios.

Las funciones financieras sirven para realizar para ejecutar una variedad de cálculos financieros, incluyendo los cálculos de rendimiento, evaluaciones de inversión, tasas de interés, tasa de retorno, depreciación de activos y los pagos.

Las funciones financieras de Excel permiten organizar más fácilmente el cálculo, administración y análisis de las finanzas tanto personales como para negocios.

Las funciones financieras también ayudan en la contabilidad de pequeñas empresas y grandes. Las funciones financieras de Excel se pueden utilizar para determinar los cambios en la moneda durante el análisis de inversiones o préstamos.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Principales funciones financieras comúnmente utilizadas en Excel • • • • •

VF, PAGO, VA, TASA, NPER

Facilitador: Dr. Víctor Manuel Barraza Espinoza

99

Función VF VF: Es una función financiera que calcula el valor final de una serie de pagos periódicos y constantes tomando como base un tipo de interés fijo.

Sintaxis: VF(tasa, nper, pago, va, tipo) • tasa: es el tipo de interés por período de pago aplicable.

• nper: es el número total de período de pago en la vigencia de la operación.

• pago: es el importe del pago periódico y constante durante un determinado tiempo.

• va: es el valor actual de los pagos o la inversión inicial del proyecto.

Ejemplo Se desea saber cuánto dinero se obtendrá depositando 500 al inicio de cada mes durante 18 años. Con una tasa de interés anual del 5.3% capitalizable semestralmente.

Facilitador: Dr. Víctor Manuel Barraza Espinoza Depositando 500, al final de los 18 años tendremos 30,291.93 siempre que la tasa sea constante.

Ahora, queremos saber cuánto será nuestro dinero si llegamos a invertir (va) 80,000 con la misma condición anterior, pero a un plazo de 10 años.

En este caso no tenemos las anualidades de 6000, únicamente tenemos un pago de 80,000 lo que nos genera en diez años 134,979.89.

Importante No debemos confundir los VA y VF. VA es para obtener nuestra inversión inicial para obtener determinada ganancia y VF es para obtener nuestra ganancia a partir de una inversión.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

100

Función PAGO PAGO es una función financiera, considera muy importante, la cual calcula el importe del pago periódico motivado por un préstamo amortizable por el método francés. Es decir, calcula las anualidades necesarias caracterizadas por términos de amortización periódicos, constantes que incluyen tanto la amortización del principal como de los intereses basados en una tasa de interés constante.

Para lograr entender mejor este tipo de funciones, debemos de tener conocimiento en lo que es Ingeniería Económica, Administración de Proyectos o Administración Financiera.

Sintaxis: PAGO(tasa, nper, va, vf, tipo) • tasa es el tipo de interés por período de pago aplicable al préstamo. Un banco presta a una tasa de interés del 20% anual.

• nper es el número total de período de pago en la vigencia del préstamo.

El banco hace los préstamos por años o por meses. Este valor debe de estar relacionado con los intereses. Es decir, si nos dan intereses del 2% mensuales, los cálculos que vamos a hacer para determinar el pago los vamos a trabajar con nper mensuales. No debemos utilizar intereses mensuales y hacer referencia a 3 años. Todo debe de estar en la misma medida por decirlo así. Mes-mes Año-año.

• va es el importe del préstamo o del valor actual del préstamo. Este valor va a ser 0, si solo si el valor de pago a buscar es el valor futuro que se tendrá según una serie de depósitos realizados al banco. Es decir, si depositas 250 por 12 meses y quieres saber cuánto dinero será al final del año, nuestro va será 0.

• vf es el valor futuro o saldo en efectivo que se desea lograr después de efectuar el último pago. Este valor será 0 si solo si se desea conocer el pago actual mensual que se necesita hacer para cancelar un préstamo.

• tipo es la modalidad de pago. Este parámetro permite 2 valores: 0 o dato omitido si los términos son pospagados, y 1 si son prepagables.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Facilitador: Dr. Víctor Manuel Barraza Espinoza

101

Ejemplo1: Se desea determinar cuáles son los pagos mensuales durante 120 meses que se deben de hacer para saldar una hipoteca realizada al banco de 10,000 a una tasa de interés del 6% anual.

En primer lugar, para utilizar la función pago, debido a que nos piden el pago mensual y el banco nos da una tasa anual debemos de encontrar la tasa mensual.

Esto lo hacemos dividiendo el 6% entre los 12 meses del año, lo que da como resultado 0.05%.

Entonces, nuestra función y resultados son:

Si nos fijamos bien, el resultado es negativo. Esto es debido a que como estamos calculando un pago que vamos a realizar (es una salida de dinero), por tal motivo aparece en rojo.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Facilitador: Dr. Víctor Manuel Barraza Espinoza

102

Ejemplo 2: Queremos ahorrar dinero para que al final de 5 años tengamos 250,000. El banco nos ofrece un interés del 10% anual capitalizables anualmente. ¿Qué cantidad de dinero debemos depositar en el banco al inicio de cada año para obtener los resultados deseados?

En este caso, vamos a poner un valor futuro (vf) y el valor actual (va) lo indicamos con un 0. Nuevamente se nos pone en negativo debido a que nosotros vamos a hacer un depósito al banco (tenemos una salida de dinero). En cambio, para el banco, este valor será positivo. Nosotros debemos depositar anualmente 40,949.37 para tener al final de los 5 años 250000.

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Facilitador: Dr. Víctor Manuel Barraza Espinoza

103

Función VA VA: Es una Función financiera que calcula el valor actual de una serie de pagos futuros. Pude realizarse según anualidades o realizando únicamente 1 solo pago.

La fórmula sería: P=F/(1+i)^2

• •

• •

Sintaxis VA(tasa, nper, pago, vf, tipo) tasa: es el tipo de interés por período de pago aplicable. nper: es el número total de períodos de pag en la vigencia de la operación financiera. Debe de estar en las mismas unidades que la tasa. Meses / Meses, Año/Año. vf: Valor futuro o el saldo que queremos alcanzar después del último pago. tipo es la modalidad de pago. El valor 0 para pospagables y 1 para prepagables.

Ejemplo1: Se desea tener 50,000 con un único depósito a una tasa del 16% anual capitalizable mensualmente.

Facilitador: Dr. Víctor Manuel Barraza Espinoza Nuestro resultado es de 31,037.46. Esto significa que, al iniciar el primer mes, yo debo depositar dicha cantidad.

Ahora, con el ejemplo anterior, se quiere depositar una cantidad mensual de 6000 y saber cuánto dinero obtendremos al final de los 36 meses. Veamos entonces:

Ejercicio2: Un empresario ha firmado un contrato de alquiler de una nave de su propiedad por el que percibirá durante 6 años una renta anual constante pospagable de 18,000. ¿Cuál es el valor actual de este contrato si aplicamos un tipo de interés anual del 5%?

Facilitador: Dr. Víctor Manuel Barraza Espinoza

104

Referencias: Manuales y ejemplos de Excel http://apuntesexcel.raigosa.co/material-de-estudio/manuales Cursos en línea http://www.formate-gratis.es/temario-32.html "Excel". En: Significados.com. Disponible en: https://www.significados.com/excel/ Consultado: 20 de noviembre de 2017, 08:27 am. http://www.excelintermedio.com/formato-de-celdas-fuente/ https://exceltotal.com/formato-personalizado-de-celdas-en-excel/ https://www.aboutespanol.com/funciones-condicionales-en-excel-1791041 http://www.comolohago.cl/como-hacer-un-buscador-en-excel/ http://especialistasenexcel.com/funcion-si-en-excel/ http://yesenia-excel.blogspot.mx/2011/03/funcion-si.html

Facilitador: Dr. Víctor Manuel Barraza Espinoza

http://www.mailxmail.com/curso-excel-paso-paso/crear-organigramas http://ayudaexcel2010.blogspot.mx/2011/02/funcion-tasa.html http://ayudaexcel2010.blogspot.mx/2011/02/funcion-pagoint.html

http://ayudaexcel2010.blogspot.mx/2011/02/funcion-pagointentre.html https://sites.google.com/site/ramsexcelavanzado/funciones-financieras https://www.taringa.net/posts/ciencia-educacion/9454221/Funciones-Financierasen-Excel-2010.html https://www.finanplan.com/recursos/matematicas-financieras/ https://arnaldojs3.wordpress.com/2013/02/21/funciones-mas-usadas-en-excel/ http://infoeconomistasdelaunefa.blogspot.mx/2009/07/principales-funcionesfinancieras-en.html http://www.cavsi.com/preguntasrespuestas/que-y-cuales-son-las-funcionesfinancieras-excel/

Facilitador: Dr. Víctor Manuel Barraza Espinoza

105

El Manual de Excel avanzado contiene las herramientas necesarias que le permitirán automatizar sus tareas diarias y desarrollar hojas de cálculo profesionalmente. https://issuu.com/vmbe/docs/manual_-_curso_de_excel__avanzado_v

Facilitador: Dr. Víctor Manuel Barraza Espinoza

Facilitador: Dr. Víctor Manuel Barraza Espinoza

106