Guia practica de excel by Kira Valarezo

2014 Funciones Avanzadas de Excel

Kira Valarezo Avilés Universidad Técnica de Ambato 10-12-2014

Kira Valarezo Avilés FUNCIONES AVANZADAS DE EXCEL

UNIVERSIDAD TÉCNICA DE AMBATO FACULTAD DE CIENCIAS HUMANAS Y DE LA EDUCACIÓN CARRERA DE DOCENCIA EN INFORMÁTICA

Gestión De Centros de Cómputo CUARTO “A”

KIRA ISABEL VALAREZO AVILÉS

ING. WILMA GAVILANES

Kira Valarezo Avilés FUNCIONES AVANZADAS DE EXCEL

INDICE CONTENIDO Indice ............................................................................................................................................. 4 Introducción ................................................................................................................................... 6 Validación de Datos ....................................................................................................................... 8 Pasos para la Validacion de Datos .......................................................................................... 8 Como funciona la "Validación de datos" ............................................................................... 8 Pasos.......................................................................................................................................... 8 Como lo verificamos ................................................................................................................ 9 Para borrar la Validación ......................................................................................................... 9 Listas Personalizadas .....................................................................................................................10 Pasos para la realización de Listas Personalizadas ...............................................................10 Como funcionan las "Listas Personalizadas" ........................................................................11 Formato Condicional ....................................................................................................................11 Proposito del formato condicional: .......................................................................................12 Pasos para aplicar Formato Condicional ...................................................................................12 Como funciona el formato condicional: ................................................................................13 Resaltar celdas con formato condicional ..............................................................................15 Reglas de formato condicional ..............................................................................................16 La opción Es mayor ...................................................................................................................16 Las opciones Es menor que, Es igual a, ......................................................................................16 La opción Una fecha ..................................................................................................................17 Reglas superiores e inferiores para analizar datos ...............................................................19 Eliminar formato condicional de celdas seleccionadas ........................................................20 Eliminar formato condicional de todas las celdas ................................................................21 Eliminar algunas reglas de formato condicional ..................................................................21 Ordenar y Filtrar ...........................................................................................................................22 Ordenar una tabla en Excel ....................................................................................................22 Múltiples criterios de ordenación ..........................................................................................22 Filtrar datos en una tabla de Excel .........................................................................................24 Filtros de números y fecha .....................................................................................................25 Quitar el filtro de una tabla ....................................................................................................26 Filtrar por segmentación de datos .........................................................................................26 Formulas Condicionantes ..............................................................................................................27

Kira Valarezo Avilés FUNCIONES AVANZADAS DE EXCEL Funcion SI ................................................................................................................................27 Sintaxis de la función SI..........................................................................................................27 Ejemplos de la función SI .......................................................................................................28 Utilizar una función como prueba lógica ..............................................................................28 Utilizar una función como valor de regreso .........................................................................29 Utilizar Funcion SI como condiciones anidadas ...................................................................29 Tablas Dinámicas ..........................................................................................................................30 Pasos para crear una tabla dinámica .....................................................................................30 Función BuscarV ...........................................................................................................................33 Crear una tabla de búsqueda .................................................................................................33 Sintaxis de la función BUSCARV ............................................................................................34 Ejemplo de la función BUSCARV ............................................................................................35 Errores comunes al usar la función BUSCARV ......................................................................36 Macros en Excel ............................................................................................................................37 Pasos para establecer la Barra de Desarrollador .......................................................................37 Pasos para codificar una Macros ...........................................................................................38 Creacion de Botones de Macros .............................................................................................39 Como guardar correctamente ................................................................................................40 Linkografía ....................................................................................................................................44

Kira Valarezo Avilés FUNCIONES AVANZADAS DE EXCEL

INTRODUCCIÓN La siguiente guía digital está elaborada como material de apoyo para todo informático o administrador que requiera de conceptos e instrucciones referente al libro u hoja de cálculo conocido también como “Excel” que forman parte del estudio y manejo del paquete de Microsoft Office. El presente material se ha preparado mediante la compilación de información clara y necesaria acerca de las características y funciones avanzadas que podemos encontrar en este programa, los cuales son muy sencillas de manejar y nos brindan ese estilo personalizado que requerimos a más de optimizar nuestro tiempo de trabajo. El objetivo primordial de esta revista es tener a la mano una herramienta a la cual podamos acudir por conceptos o funciones avanzadas las cuales nos servirán como administradores.

Kira Valarezo Avilés FUNCIONES AVANZADAS DE EXCEL

VALIDACIÓN DE DATOS Esta opción nos permite validar o condicionar las celdas donde ubicaremos o ingresaremos nuestros datos, de tal manera que estas se bloqueen para datos erróneos y permitan únicamente datos correctos. Para aplicar esta función seguiremos lo siguientes pasos: PASOS PARA LA VALIDACION DE DATOS Hacemos clic en Datos > en el grupo Herramientas de datos > haga hacemos en la flecha situada junto a Validación de datos > hacemos clic en Validación de datos.

Buscamos este icono:

COMO FUNCIONA LA "VALIDACIÓN DE DATOS " Escoja una regla de la lista predeterminada o cree su propia regla para limitar el tipo de datos que pueden escribirse en una celda. Por ejemplo, puede proporcionar una lista de valores, como 1, 2 y 3, o solo permitir números mayores que 100 como entradas válidas.

PASOS 1. Nos dirigimos a la opción de validación como lo hicimos antes

Kira Valarezo Avilés FUNCIONES AVANZADAS DE EXCEL

2. Configuramos la validación que queremos establecer

3. Ubicandonos en la ultima pestaña del cuadro de dialogo, ingresamos un titulo de Cuadro; en este caso “ERROR”, y como mensaje de entrada especificaremos que los “DATOS NO son PERMITIDOS”

COMO LO VERIFICAMOS Intentaremos ingresar datos que estén fuera de la validación que establecimos, podremos ver que aparecerá un cuadro de dialogo indicando que los valores ingresados no son aceptables: PARA BORRAR LA VALIDACIÓN Selecciona las celdas con valores y pulsa en Validación de datos haz clic en el botón Borrar todos que está situado abajo a la izquierda de la ventana

Kira Valarezo Avilés FUNCIONES AVANZADAS DE EXCEL

LISTAS PERSONALIZADAS Esta opción nos permite ingresar una lista de datos de acuerdo a nuestra necesidad con el objetivo de optimizar el tiempo de transcripción de datos en lista tales como nombres o ciudades. Para aplicar esta función seguiremos lo siguientes pasos: PASOS PARA LA REALIZACIÓN DE LISTAS PERSONALIZADAS Hacemos clic en Archivo > en el grupo Opciones > hacemos clic en la opción avanzadas > bajamos hasta el final de cuadro de dialogo > hacemos clic en Modificar listas personalizadas

Kira Valarezo Avilés FUNCIONES AVANZADAS DE EXCEL

COMO FUNCIONAN LAS "LISTAS PERSONALIZADAS " Haremos elementos

listado

que

los

necesitemos

ubicar uno debajo de otro; ejemplo, artículos tecnológicos: Monitor Teclado… etc Y aceptamos al momento de ingresar todos los datos.

De esta forma podremos ubicar el cursor en la parte inferior derecha de la primera celda hasta que nuestro cursor tome una forma mas delgada y nos permita arrastrar las opciones de datos hacia abajo. Una vez ingresada la lista no es necesario tipear todos los datos, únicamente arrastarlos.

De esta forma podremos ingresar listados de objetos, nombres y valores:

FORMATO CONDICIONAL El formato condicional en Excel es una funcionalidad de gran utilidad al momento de realizar el análisis de datos ya que puedes dar un formato especial a un grupo de celdas en base al valor de otra celda. Esto te permitirá aplicar un tipo de fuente específico o un color de relleno diferente para aquellas celdas que cumplan con ciertas reglas y así poder identificarlas fácilmente en pantalla.

Kira Valarezo Avilés FUNCIONES AVANZADAS DE EXCEL

PROPOSITO DEL FORMATO CONDICIONAL: Siempre tienes la opción de aplicar manualmente un formato a cada una de las celdas que cumplen con una condición, pero eso se puede convertir en un proceso largo y repetitivo, especialmente si tienes una tabla de datos muy grande y que cambia frecuentemente. Es por eso que el formato condicional puede hacer más fácil la tarea de cambiar automáticamente el formato de la celda que cumple con ciertos criterios. Así podemos apreciarlo en la siguiente tabla: Formato especial para todos los valores entre 20 y 30:

De esta forma podemos apreciar otro ejemplo: Resaltar los valores por debajo del promedio (Para este ejemplo el promedio es 22.2).

Para aplicar esta función seguiremos lo siguientes pasos: PASOS PARA APLICAR FORMATO CONDICIONAL Hacemos clic en Archivo > en el grupo Estilos > hacemos clic en Formato Condicional

Kira Valarezo Avilés FUNCIONES AVANZADAS DE EXCEL

COMO FUNCIONA EL FORMATO CONDICIONAL: Debes selccionar las celdas a las que se les aplicará el formato condicional y después hacer clic en el comando Formato condicional que aparece en el grupo Estilos de la ficha Inicio. al seleccionar alguna de las opciones del menú, el formato se aplicará para aquellas celdas que cumplan con cierta condición o regla. Un formato condicional en Excel está siempre basado en una regla que posteriormentre se podrá editar si así lo deseas. Las reglas que se crean para los formatos condicionales se pueden dividir en dos grandes grupos:

Reglas basadas en valores de celda

Estas reglas se basan en el mismo valor de la celda (Mayor que, Menor que, Igual a, Entre, etc.).

Reglas basadas en fórmulas Estas reglas ofrecen mayor flexibilidad porque puedes aplicar un formato especial utilzando una fórmula donde podrás aplicar una lógica más compleja.

Por lo mismo es un poco más complicado de aprender, pero una vez que lo hagas seré muy intuitivo de utilizar

Kira Valarezo Avilés FUNCIONES AVANZADAS DE EXCEL

Tambien encontramos la opción de “NUEVA REGLA”,

esta opción nos

permite crear nuestros propios parámetros y personalizar los formatos existentes. Al hacer clic en

obtendremos un cuadro de dialogo en el cual podremos

escoger el tipo de regla que vayamos a establecer.

De igual manera apreciamos que esta nueva regla estará sujeta a la personalización que deseemos darle a la misma, esto puede ser: Estilo de formato, tipo, valor, color, y vista previa de los cambios que realicemos. La opción mas común es la segunda: “Aplicar formato únicamente a las celdas que contengan”… esta nos permitirá establecer los parámetros y a su vez la edición de formato en el resultado o presentación de valores.

Kira Valarezo Avilés FUNCIONES AVANZADAS DE EXCEL

Asi podemos apreciar las opciones que nos presenta el cuadro de dialogo de formato de celdas para nuestros trabajos personalizados. En el siguiente ejemplo vemos que presenta las siguientes condiciones para los valores correspondientes al promedio:

RESALTAR CELDAS CON FORMATO CONDICIONAL Esta opción de menú nos dará la oportunidad de destacar celdas que cumplan con la regla de formato condicional especificada. Cada opción muestra su propio cuadro de diálogo solicitando los argumentos necesarios para crear la regla.

Kira Valarezo Avilés FUNCIONES AVANZADAS DE EXCEL

REGLAS DE FORMATO CONDICIONAL

LA OPCIÓN ES MAYOR que muestra el siguiente cuadro de diálogo:

En el primer cuadro de texto deberás colocar un número a partir del cual se aplicará el formato especificado. Para este ejemplo coloqué el número 50 en la caja de texto, dejé el formato predeterminado y el resultado fue el siguiente:

LAS OPCIONES ES MENOR QUE, ES IGUAL A, Texto que contiene, esperan que ingreses un solo valor con el cual comparar las celdas a las que se aplicará el formato. La opción Entre muestra un cuadro de diálogo diferente porque solicita 2 valores que delimitarán el rango de valores a buscar:

Kira Valarezo Avilés FUNCIONES AVANZADAS DE EXCEL

LA OPCIÓN UNA FECHA muestra una lista de opciones de donde podrás seleccionar la que mejor se adapte a tus necesidades.

Por supuesto, es necesario que tus celdas tengan datos de tipo fecha de manera que esta regla de formato condicional pueda encontrar valores coincidentes. Finalmente la opción DUPLICAR VALORES te permitirá resaltar ya sea los valore únicos ó los valores duplicados dentro de tus datos En primer lugar deberás seleccionar el rango de datos al cual se le aplicará el formato condicional: Después deberás ir a la ficha Inicio y en el grupo Estilos hacer clic sobre Formato Condicional y posteriormente desplegar el menú Resaltar reglas de celdas y elegir la opción Duplicar valores.

Kira Valarezo Avilés FUNCIONES AVANZADAS DE EXCEL

Podemos ver que se mostrará el cuadro de diálgo Duplicar Valores:

Deja las opciones predeterminadas y haz clic en Aceptar. Todos los valores son resaltados excepto el día Sábado porque es el único de toda la lista que no se repite

Kira Valarezo Avilés FUNCIONES AVANZADAS DE EXCEL

REGLAS SUPERIORES E INFERIORES PARA ANALIZAR DATOS Estas reglas se muestran dentro del menú Formato condicional y dentro de la opción Reglas superiores e inferiores.

La REGLA 10 SUPERIORES te permitirá resaltar de manera inmediata las celdas que contengan los 10 valores con mayor valor dentro del rango de celdas. El cuadro de diálogo de esta regla es el siguiente:

Observa que puedes cambiar el número de elementos a seleccionar, por lo que en lugar de los 10 superiores podrías seleccionar los 20 superiores si así lo deseas. La REGLA 10

INFERIORES funciona de manera similar, solo que Excel obtendrá las celdas con menor valor dentro del rango.

La regla 10% de valores superiores es similar a las reglas anteriores, pero en lugar de indicar un número específico de celdas se introduce un porcentaje de celdas a seleccionar. Por ejemplo, dentro de un rango que contiene 20 celdas, si se especifica un 10% entonces el formato condicional se aplicará a las 2 celdas con mayor valor porque el 10% de 20 celdas son 2 celdas. El cuadro de diálogo es el siguiente:

Kira Valarezo Avilés FUNCIONES AVANZADAS DE EXCEL

De la misma manera puedes aumentar o disminuar el porcentaje. Si especificas un 20% para un rango de 20 celdas, entonces el formato condicional se aplicará a 4 celdas. La regla 10% de valores inferiores selecciona el 10% de celdas del rango que tenga el menor valor. Finalmente la regla Por encima del promedio hace dos cosas. En primer lugar calcula el valor promedio de las celdas del rango seleccionado y posteriormente aplica el formato condicional a todas aquellas celdas que tienen un valor por encima del promedio recién calculado. Al no tener nigún argumento, el cuadro de diálogo de esta regla solamente solicta el formato a aplicar:

ELIMINAR FORMATO CONDICIONAL DE CELDAS SELECCIONADAS Una manera de remover los formatos condicionales es seleccionar las celdas que tienen el formato que deseas eliminar y hacer clic sobre Formato condicional y seleccionar la opción Borrar reglas y posteriormente Borrar reglas de las celdas seleccionadas.

Kira Valarezo Avilés FUNCIONES AVANZADAS DE EXCEL

Excel quitará de inmediato cualquier formato condicional que esté aplicado sobre las celdas seleccionadas.

ELIMINAR FORMATO CONDICIONAL DE TODAS LAS CELDAS El segundo método para remover los formatos es seleccionar la segunda opción del menú previamente mencionado: Borrar reglas de toda la hoja. Al seleccionar esta opción debes estar seguro de que no necesitas ningún formato condicional en toda la hoja de Excel ya que se borrarán todos y cada uno de ellos.

ELIMINAR ALGUNAS REGLAS DE FORMATO CONDICIONAL El último método que te mostraré se debe utilizar en caso de tener varias reglas de formato y solamente querer remover una sola de ellas. De igual manera debes seleccionar las celdas que tienen el formato y hacer clic en Formato condicional y posteriormente en la opción Administrar reglas, lo cual mostrará el siguiente cuadro de diálogo:

Este cuadro de diálogo contiene todas las reglas creadas para las celdas seleccionadas. Elige la regla que deseas borrar y haz clic en el botón Eliminar regla y posteriormente en el botón Aceptar. Excel removerá la regla seleccionada y dejará el resto de las reglas tal como estaban siendo aplicadas sobre los datos.

Kira Valarezo Avilés FUNCIONES AVANZADAS DE EXCEL

ORDENAR Y FILTRAR Cada encabezado de columna de una tabla en Excel tiene un botón de flecha en su extremo derecho el cual es conocido como el botón de filtro y cuando hacemos clic sobre él se muestran diferentes opciones para ordenar y filtrar datos en Excel. ORDENAR UNA TABLA EN EXCEL Al pulsar el botón de filtro en el encabezado de una columna veremos algunas opciones de ordenación de datos. Al aplicar un criterio de ordenación a una tabla de Excel las filas se reorganizarán de acuerdo al contenido de la columna por la cual estamos ordenando. Si la columna contiene datos de tipo texto, entonces podremos ordenar los valores de A a Z (alfabéticamente) o de Z a A.

Por el contrario, si la columna contiene datos numéricos, entonces podremos ordenar de menor a mayor o también podremos hacerlo de mayor a menor. En el caso de que tengamos fechas podremos ordenar de más antiguo a más reciente o viceversa. Otra opción de ordenación que nos da Excel es la de ordenar por color y eso hará que las celdas se ordenen de acuerdo al color de fondo de las celdas. Esta opción es relevante solamente cuando hemos aplicado algún formato condicional a las celdas para modificar su color de fondo o el color de la fuente.

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

Kira Valarezo Avilés FUNCIONES AVANZADAS DE EXCEL

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

Agregar nivel

•Nos permite agregar un nuevo nivel de ordenación.

Eliminar nivel

•Si ya no deseamos que se aplique un criterio de ordenación podemos eliminarlo de la lista

Copiar nivel

•Hace una copia del nivel seleccionado

Flechas arriba y abajo

Opciones

•Nos permiten mover los niveles de ordenación hacia arriba o hacia abajo para establecer un orden preferido. •Con este botón podemos indicar si la ordenación de datos va a distinguir entre mayúsculas y minúsculas

La ordenación de los datos se hará comenzando por el nivel superior y hacia abajo tomando en cuenta tanto la columna como el criterio de ordenación especificado en cada nivel

Kira Valarezo Avilés FUNCIONES AVANZADAS DE EXCEL

FILTRAR DATOS EN UNA TABLA DE EXCEL Cuando hablamos de filtrar datos en una tabla de Excel nos referimos a desplegar solamente aquellas filas que cumplen con los criterios especificados. Los datos de la tabla no son eliminados ni alterados sino que solamente se ocultan a la vista. Las opciones de filtrado se muestran también al pulsar el botón de flecha que se encuentra al lado de los encabezados de columna.

En la imagen anterior observamos las opciones de filtrado que Excel provee cuando la columna tiene datos de tipo texto. La opción Filtros de texto muestra varias opciones a elegir como Es igual a, Comienza por, Contiene, etc. Cualquiera de estas opciones mostrará el cuadro de diálogo Autofiltro personalizado que nos permitirá indicar el detalle del filtro que deseamos aplicar.

En este cuadro de diálogo podemos especificar dos condiciones para una misma columna y elegir si deseamos que se cumplan ambas o solamente una de ellas. Al hacer clic en el botón Aceptar se aplicará el filtro a los datos. En la parte inferior del menú de filtrado observamos una lista de los valores únicos de la columna y cada opción tiene a su lado una caja de selección que podemos marcar o desmarcar indicando si deseamos ver u ocultar aquellas filas que contienen dicho valor. Si la lista tiene tantos valores únicos que se nos dificulta encontrar la opción que necesitamos, entonces podemos utilizar el cuadro de búsqueda de manera que podamos encontrar los valores adecuados y seleccionarlos.

Kira Valarezo Avilés FUNCIONES AVANZADAS DE EXCEL

FILTROS DE NÚMEROS Y FECHA En

sección

revisamos los Filtros de texto que provee Excel, pero las opciones que se muestran cuando el tipo de datos es un número o una fecha serán diferentes. Por ejemplo, en la siguiente

imagen

observar

las

puedes

opciones

Filtros de fecha

Excel reconoce el tipo de dato contenido en una columna y muestra los criterios de filtrado correspondientes. En la siguiente imagen puedes observar las opciones de Filtros de número:

Algunas de las opciones de filtros mostrarán el cuadro de diálogo Autofiltro personalizado para permitirnos indicar el detalle del criterio de filtrado a aplicar, pero otras opciones como el filtro de fecha Hoy se aplicará de inmediato sobre los datos. Lo mismo sucederá con el filtro de número Diez mejores que no necesita de ningún parámetro adicional y se aplicará de inmediato sobre la columna.

Kira Valarezo Avilés FUNCIONES AVANZADAS DE EXCEL

QUITAR EL FILTRO DE UNA TABLA Una vez que hemos aplicado un filtro en una columna, el icono del botón de filtro cambiará su apariencia para indicarnos que dicha columna tiene un filtro aplicado. Para quitar un filtro de una tabla de Excel tenemos dos opciones: Hacer clic sobre el botón de filtro y seleccionar la opción Borrar filtro de y se eliminará el filtro de dicha columna. Si queremos borrar con un solo clic todos los filtros aplicados a una tabla entonces debemos ir a Inicio > Modificar > Ordenar y filtrar > Borrar.

FILTRAR POR SEGMENTACIÓN DE DATOS Excel 2013 nos permite filtrar datos de una tabla utilizando la segmentación de datos. Este método no es muy utilizado porque requiere de espacio adicional en pantalla pero es importante que conozcas su uso en caso de que llegues a encontrar una tabla con este tipo de funcionalidad. Para agregar la segmentación de datos debemos seleccionar una celda de la tabla e ir

Herramientas

tabla

Diseño

Herramientas > Insertar segmentación de datos. Se mostrará un cuadro de diálogo con las columnas de nuestra tabla: En este ejemplo he seleccionado la columna País y al pulsar el botón Aceptar se insertará en la hoja de Excel un panel con las opciones de segmentación de datos para la columna previamente seleccionada:

Kira Valarezo Avilés FUNCIONES AVANZADAS DE EXCEL

Al hacer clic en cualquiera de las opciones del panel se aplicará el filtro correspondiente sobre la tabla. Para seleccionar varias opciones del panel debemos mantener pulsada la tecla Ctrl mientras hacemos clic con el ratón sobre ellas. Para remover el filtro de una columna debemos pulsar el botón que aparece en la esquina superior derecha del panel de segmentación de datos correspondiente. De esta manera podemos agregar tantos paneles como columnas tengamos en nuestra tabla y aplicar filtros sobre los datos con solo un clic. Una ventaja de utilizar la segmentación de datos es que tendemos una ayuda visual para conocer rápidamente los filtros que están siendo aplicados a la tabla. Para eliminar los paneles de segmentación de datos debes hacer clic sobre su borde y pulsar la tecla Suprimir o hacer clic derecho sobre el panel y seleccionar la opción Quitar.

FORMULAS CONDICIONANTES FUNCION SI La función SI en Excel es parte del grupo de funciones Lógicas y nos permite evaluar una condición para determinar si es falsa o verdadera. La función SI es de gran ayuda para tomar decisiones en base al resultado obtenido en la prueba lógica. SINTAXIS DE LA FUNCIÓN SI Además de especificar la prueba lógica para la función podemos

también especificar

valores a devolver de acuerdo al resultado de la función.

Prueba_lógica (obligatorio):

Valor_si_verdadero (opcional):

• Expresión lógica que será evaluada para conocer si el resultado es VERDADERO o FALSO.

• El valor que se devolverá en caso de que el resultado de la Prueba_lógica sea VERDADERO.

Valor_si_falso (opcional): • El valor que se devolverá si el resultado de la evaluación es FALSO.

Kira Valarezo Avilés FUNCIONES AVANZADAS DE EXCEL

La Prueba_lógica puede ser una expresión que utilice cualquier operador lógico o también puede ser una función de Excel que regrese como resultado VERDADERO o FALSO. Los argumentos Valor_si_verdadero y Valor_si_falso pueden ser cadenas de texto, números, referencias a otra celda o inclusive otra función de Excel que se ejecutará de acuerdo al resultado de la Prueba_lógica

EJEMPLOS DE LA FUNCIÓN SI Probaremos la función SI con el siguiente ejemplo. Tengo una lista de alumnos con sus calificaciones correspondientes en la columna B. Utilizando la función SI desplegaré un mensaje de APROBADO si la calificación del alumno es superior o igual a 60 y un mensaje de REPROBADO si la calificación es menor a 60. La función que utilizaré será la siguiente: =SI(B2>=60,"APROBADO","REPROBADO")

Observa el resultado al aplicar esta fórmula en todas las celdas de la columna C.

UTILIZAR UNA FUNCIÓN COMO PRUEBA LÓGICA Es posible utilizar el resultado de otra función como la prueba lógica que necesita la función SI siempre y cuando esa otra función regrese como resultado VERDADERO o FALSO. Un ejemplo de este tipo de función es la función ESNUMERO la cual evalúa el contenido de una celda y devuelve el valor VERDADERO en caso de que sea un valor numérico. En este ejemplo quiero desplegar la leyenda “SI” en caso de que la celda de la columna A efectivamente tenga un número, de lo contrario se mostrará la leyenda “NO”.

Kira Valarezo Avilés FUNCIONES AVANZADAS DE EXCEL

UTILIZAR UNA FUNCIÓN COMO VALOR DE REGRESO Como último ejemplo mostraré que es posible utilizar una función para especificar el valor de regreso. Utilizando como base el ejemplo anterior, necesito que en caso de que la celda de la columna A contenga un valor numérico se le sume el valor que colocaré en la celda D1. La función que me ayudará a realizar esta operación es la siguiente: =SI(ESNUMERO(A2), SUMA(A2, $D$1), "NO")

Como puedes observar, el segundo argumento es una función la cual se ejecutará en caso de que la prueba lógica sea verdadera. Observa el resultado de esta fórmula:

UTILIZAR FUNCION SI COMO CONDICIONES ANIDADAS la función SI anidado nos permite entrelazar mas de dos condiciones dentro de una misma celda, el objetivo de esta condición es el de permitir que el usuario pueda realizar cálculos mas complejos y obtenga los resultados requeridos en menor tiempo, para ellos aplicaremos el siguiente ejercicio de calificaciones:

Kira Valarezo Avilés FUNCIONES AVANZADAS DE EXCEL

Como podemos observar en la columna de observación encontramos el estado de los estudiantes, ya sea aprobado, reprobado o suspenso. Para ellos aplicaremos la fórmula del SI anidado en el que tendremos que utilizar la siguiente estructura: =SI(M6>7;"APROBADO";SI(M6>=5;"SUSPENSO";"REPROBADO"))

TABLAS DINÁMICAS Una tabla dinámica sirve para resumir los datos que hay en una hoja de cálculo. Lo mejor de todo es que puedes cambiarla fácil y rápidamente para ver los datos de una manera diferente, haciendo de ésta una herramienta muy poderosa. Nos permite trabajar con una tabla principal y obtener subtablas en función de criterios. Debe tener datos alfabéticos y numéricos para que presente mas opciones de criterio

PASOS PARA CREAR UNA TABLA DINÁMICA Partiendo de una hoja de cálculo que contiene las estadísticas de ventas para una empresa ficticia, supongamos que queremos responder a la pregunta: ¿Cuánto es el total de ventas por cada vendedor? Esto puede llevar mucho tiempo porque cada uno de ellos aparece en varias filas al igual que su venta mensual. Aunque podríamos utilizar la función Subtotal todavía tendríamos un montón de datos por analizar. Por suerte, una tabla dinámica puede hacer todas las operaciones matemáticas al instante y resumir los datos de una manera que no sólo es fácil de leer sino también, de manipular.

Kira Valarezo Avilés FUNCIONES AVANZADAS DE EXCEL

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

Paso 2: En la ficha Insertar, haz clic en el comando Tabla dinámica. Paso 3: Aparecerá el cuadro de diálogo Crear tabla dinámica. Asegúrate de que la configuración sea correcta y haz clic en Aceptar.

Kira Valarezo Avilés FUNCIONES AVANZADAS DE EXCEL

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

Obtendremos de este procedimiento los siguientes resultados:

Kira Valarezo Avilés FUNCIONES AVANZADAS DE EXCEL

En el caso que utilicemos graficos podrmeos representar los resultados de la tabla dinámica de la siguiente manera:

FUNCIÓN BUSCARV La función BUSCARV en Excel nos permite buscar un valor dentro de un rango de datos, es decir, nos ayuda a obtener el valor de una tabla que coincide con el valor que estamos buscando. Un ejemplo sencillo que podemos resolver con la función BUSCARV es la búsqueda dentro de un directorio telefónico. Si queremos tener éxito para encontrar el teléfono de una persona dentro del directorio telefónico de nuestra localidad debemos tener el nombre completo de la persona. Posteriormente habrá que buscar el nombre dentro del directorio telefónico para entonces obtener el número correcto.

CREAR UNA TABLA DE BÚSQUEDA Para poder utilizar la función BUSCARV debemos cumplir con algunas condiciones en nuestros datos. En primer lugar debemos tener la información organizada de manera vertical con los valores por debajo de cada columna. Esto es necesario porque la función BUSCARV recorre los datos de manera vertical (por eso la “V” en su nombre) hasta encontrar la coincidencia del valor que buscamos.

Kira Valarezo Avilés FUNCIONES AVANZADAS DE EXCEL

Por ejemplo, nuestro directorio telefónico debería estar organizado de la siguiente manera:

Otra condición que forzosamente debemos cumplir es que la primera columna de nuestros datos debe ser la columna llave, es decir, los valores de esa columna deben identificar de manera única a cada una de las filas de datos. En este ejemplo la columna Nombre servirá como la columna llave porque no hay dos personas que se llamen igual. Algo que debemos cuidar con la tabla de búsqueda es que si existen otras tablas de datos en la misma hoja de Excel debes dejar al menos una fila en blanco por debajo y una columna en blanco a la derecha de la tabla donde se realizará la búsqueda. Una vez que la tabla de búsqueda está lista podemos utilizar la función BUSCARV. SINTAXIS DE LA FUNCIÓN BUSCARV La función BUSCARV tiene 4 argumentos:

Valor_buscado (obligatorio): Este es el valor que se va a buscar en la primera columna de la tabla. Podemos colocar el texto encerrado en comillas o podemos colocar la referencia a una celda que contenga el valor buscado. Excel no hará diferencia entre mayúsculas y minúsculas.

Kira Valarezo Avilés FUNCIONES AVANZADAS DE EXCEL

Matriz_buscar_en (obligatorio): La tabla de búsqueda que contiene todos los datos donde se tratará de encontrar la coincidencia del Valor_buscado. Indicador_columnas (obligatorio): Una vez que la función BUSCARV encuentre una coincidencia del Valor_buscado nos devolverá como resultado la columna que indiquemos en este argumento. El Indicador_columnas es el número de columna que deseamos obtener siendo la primera columna de la tabla la columna número 1. Ordenado (opcional): Este argumento debe ser un valor lógico, es decir, puede ser falso o verdadero. Con este argumento indicamos si la función BUSCARV realizará una búsqueda exacta (FALSO) o una búsqueda aproximada (VERDADERO). En caso de que se omita este argumento o que especifiquemos una búsqueda aproximada se recomienda que la primera columna de la tabla de búsqueda esté ordenada de manera ascendente para obtener los mejores resultados002E

EJEMPLO DE LA FUNCIÓN BUSCARV Para probar la función BUSCARV con nuestra tabla de búsqueda ejemplo que contiene información de números telefónicos seguimos los siguientes pasos: 1. En la celda E1 colocaré el valor que deseo buscar. 2. En la celda E2 comienzo a introducir la función BUSCARV de la siguiente manera: =BUSCARV( 3. Hago clic en la celda E1 para incluir la referencia de celda e introduzco una coma (,) para concluir con el primer argumento de la función: =BUSCARV(E1, 4. Para especificar el segundo argumentos debo seleccionar la tabla de datos sin incluir los títulos de columna. Para el ejemplo será el rango de datos A2:B11. Una vez especificada la matriz de búsqueda debo introducir una coma (,) para finalizar con el segundo argumento: =BUSCARV(E1,A2:B11, 5. Como tercer argumento colocaré el número 2 ya que quiero que la función BUSCARV me devuelva el número de teléfono de la persona indicada en la celda E1. Recuerda que la numeración de columnas empieza con el 1 y por lo tanto la columna Teléfono es la columna número 2. De igual manera finalizo el tercer argumento con una coma (,): =BUSCARV(E1,A2:B11,2, 6. Para el último argumento de la función especificaré el valor FALSO ya que deseo hacer una búsqueda exacta del nombre. =BUSCARV(E1,A2:B11,2,FALSO)

Kira Valarezo Avilés FUNCIONES AVANZADAS DE EXCEL

Observa el resultado de la función recién descrita:

Una ventaja de haber colocado el valor buscado en la celda E1 es que podemos modificar su valor para buscar el teléfono de otra persona y la función BUSCARV actualizará el resultado automáticamente.

ERRORES COMUNES AL USAR LA FUNCIÓN BUSCARV Si la columna llave no tiene valores únicos para cada fila entonces la función BUSCARV regresará el primer resultado encontrado que concuerde con el valor buscado. Si especificamos un indicador de columna mayor al número de columnas de la tabla obtendremos un error de tipo #REF! Si colocamos el indicador de columna igual a cero la función BUSCARV regresará un error de tipo #VALOR! Si configuramos la función BUSCARV para realizar una búsqueda exacta, pero no encuentra el valor buscado, entonces la función regresará un error de tipo #N/A. La función BUSCARV es una de las funciones más importantes en Excel. Es necesario que dediques tiempo para aprender correctamente su uso y verás que podrás sacar mucho provecho de esta función.

Kira Valarezo Avilés FUNCIONES AVANZADAS DE EXCEL

MACROS EN EXCEL Las macros Excel es un conjunto de instrucciones programadas en la hoja de cálculo electrónica Excel, las cuales automatizan las operaciones que realiza la aplicación ofimática Excel con el objetivo de eliminar tareas repetitivas o realizar cálculos complejos en un corto espacio de tiempo y con una nula probabilidad de error. Para utilizar esta función primeramente debemos integrar una barra de funciones adicional, llamada Desarrollador. Para ello seguiremos lo siguientes pasos: PASOS PARA ESTABLECER LA BARRA DE DESARROLLADOR Escogemos la opcion Archivo > Seleccionamos Opciones > en ella nos ubicamos en la opcion Personalizar Cinta de Opciones > Habilitamos la barra del Desarrollador

De esta manera mantendremos activo una barra escencial para posteriores progresos del archivo, mismo que al final deberá guardarse en un formato habilitado para la macros, lo que permitirá que se conserve modificable en próximas circunstancias

Existen 2 maneras de realizar Macros en Excel: Mediante el uso de la grabadora de Macros. Mediante el uso del lenguaje VBA para programar a Excel

Kira Valarezo Avilés FUNCIONES AVANZADAS DE EXCEL

La primera de las opciones es la más fácil, dado a que solo requiere apretar al botón grabador de macros y realizar el conjunto de operaciones

que

queramos que se repitan, una

vez

finalizado

guardamos

macro

grabada con el nombre que queramos. Cada vez que queramos ejecutar esta

macro

tendremos

que

apretar

ejecutar

macro

botón y

seleccionarla.

Esta primera opción es la más fácil de usar pero es la que presenta más limitaciones, dado a que solo ejecutará una vez la tarea grabada así como no utilizará toda la potencia del lenguaje VBA. La segunda opción requiere del conocimiento y práctica del lenguaje de programación VBA, pero es sin duda la opción más potente en cuestión de desarrollo de aplicaciones, cualquier cosa que se nos ocurra puede realizarse mediante el uso de VBA, desde una aplicación compleja hasta la automatización de tareas repetitivas de nuestra hoja de cálculo Excel. PASOS PARA CODIFICAR UNA MACROS Para ello realizamos los siguientes pasos: Hacemos click en la barra Desarrollador > escogemos la opción Macros Nos aparecerá un cuadro de dialogo en el cual ingresaremos el nombre que deseamos asiganarle a la macros, posterior a ello damos click en la opción de Crear.

Kira Valarezo Avilés FUNCIONES AVANZADAS DE EXCEL

Nos aparecerá de inmediato una nueva ventana denominada Visual Basic para Aplicaciones, en la que nos permitirá ingresar líneas de codificación para indicar procesos y parámetros de ejecucicion para la macros (aplicacion) que deseamos plantear.

CREACION DE BOTONES DE MACROS Para permitir que nuestra macros pueda ejecutarse dentro de la hoja de calculo debemos insertar botones de ejecuciones (Programacion Orientada a Objetos) los cuales podemos ubicarlos de la siguiente manera: En la pestaña del Programador o desarrollador >Escogemos la opción Insertar > escogemos de los diferentes controloes que se presentan el denominado > Boton Los botones que decidamos ingresar están sujetos a la personalización del usuario

Kira Valarezo Avilés FUNCIONES AVANZADAS DE EXCEL

COMO GUARDAR CORRECTAMENTE Para guardar correctamente una hoja de calculo que ha sido modificada con la pestaña de desarrollador debe guardarse mediante los siguientes pasos: Hacemos click en Archivo > Guardar como > Escogemos la ubicación del archivo > Asignamos un nombre > cambiamos la extensión del libro: escogeremos la opción > “Libro de Excel habilitado para Macros”

De esta forma podremos guardar nuestro archivo y mantenerlo habilitado para futuras modificaciones y utilizaciones.

Kira Valarezo Avilés FUNCIONES AVANZADAS DE EXCEL

CONCLUSIÓN Gracias a este documento he podido comprender mucho más el funcionamiento de las opciones avanzadas que presenta La hoja de Calculo Excel, lo recomiendo para su uso práctico dentro del ámbito educativo como empresarial orientado a cualquier rama de las ciencias digitales.

Esta guía permitirá el desarrollo del razonamiento lógico con la para aprender más sobre las diversas funciones que nos brinda a más de aprovechar los recursos tecnológicos que tenemos a nuestro alcance.

Espero que todo aquel que llegue a obtener este material pueda encontrarle gran utilidad en el desarrollo de cálculos y funciones que tienen como objetivo el de optimizar el tiempo del usuario al realizar asuntos de gestión digital.

Kira Valarezo Avilés FUNCIONES AVANZADAS DE EXCEL

LINKOGRAFÍA http://exceltotal.com/la-funcion-si-en-excel/ http://exceltotal.com/ordenar-y-filtrar-datos-en-excel-2013/ http://exceltotal.com/fundamentos-de-formato-condicional/ http://exceltotal.com/resaltar-celdas-con-formato-condicional/ http://exceltotal.com/aplicar-formato-condicional-a-valores-duplicados-o-valores-unicos/ http://exceltotal.com/reglas-superiores-e-inferiores-para-analizar-datos/ http://exceltotal.com/borrar-reglas-de-formato-condicional/ http://exceltotal.com/formato-condicional-en-excel/ http://exceltotal.com/el-codigo-de-la-grabadora-de-macros/ https://support.office.com/es-hn/article/Crear-una-macro-455512ef-3532-404e-b8dd-ea6589512c1b http://office.microsoft.com/es-es/excel-help/filtrar-datos-de-un-rango-o-una-tablaHP010342517.aspx http://exceltotal.com/introduccion-a-las-macros/ http://www.queesexcel.net/que-son-las-macros-excel.html http://www.todoexcel.com/la-funcion-excel-buscarv/