FUNCIONES AVANZADAS DE EXCEL by Evelyn Simba

GESTION DE CENTROS DE CÓMPUTO Autor: Evelyn Simba 2014

GESTION DE CENTROS DE CÓMPUTO

Índice INTRODUCCION .............................................................................................................................. 4 ¿CÓMO GENERAR LISTAS AUTOMATICAS EN EXCEL? ................................................................... 5 Configurar nuestras listas personalizadas. .................................................................................... 5 A continuación debemos ir al Botón de Office y hacer clic en Opciones de Excel. ........... 5 En la categoría Más Frecuentes, encontraremos dentro de la sección Opciones principales para trabajar con Excel el botón Modificar listas personalizadas. ...................... 5 VALIDACION DE INGRESO DE DATOS ............................................................................................ 6 ¿Qué es la validación de datos? .................................................................................................. 7 ¿Cuándo es útil la validación de datos? ..................................................................................... 8 Mensajes de validación de datos ................................................................................................. 9 FORMULAS CONDICIONALES ....................................................................................................... 10 Formulas Condicionadas Simples ................................................................................................ 10 Formulas Condicionadas Dobles................................................................................................. 13 FORMATO CONDICIONAL ............................................................................................................ 14 .......................................................................................................................................................... 14 .......................................................................................................................................................... 15 Barra de Datos ............................................................................................................................... 15 Aplicar Formato ............................................................................................................................. 16 TABLAS DINAMICAS ...................................................................................................................... 18 Paso 1: ............................................................................................................................................. 18 Paso 2: ............................................................................................................................................. 19 Paso 3: ............................................................................................................................................. 19 Paso 4: ............................................................................................................................................. 19 Formato de valores en una tabla dinámica ............................................................................. 20 GRAFICOS DINAMICOS ................................................................................................................ 22 Cómo crear un gráfico dinámico en Excel ............................................................................... 22 BUSCARV ....................................................................................................................................... 24 Estructura de la función de Excel BUSCARV .............................................................................. 26 Ejemplo: ........................................................................................................................................... 27 MACRO .......................................................................................................................................... 28 ¿Qué es una macro de Excel? .................................................................................................... 28

GESTION DE CENTROS DE CÓMPUTO Paso 1: Activar la Barra de Herramientas Programador .......................................................... 28 Paso 2: Habilitar Macros................................................................................................................ 29 Paso 3: Preparar la Hoja de Cálculo........................................................................................... 30 Un lenguaje de programación .................................................................................................... 30 Ejemplo: ........................................................................................................................................... 31

GESTION DE CENTROS DE CÓMPUTO

INTRODUCCION

Excel es un programa del tipo Hoja de Cálculo que permite realizar operaciones con números organizados en una cuadrícula. Es útil para realizar desde simples sumas hasta cálculos de préstamos hipotecarios. Ahora vamos a ver cuáles son los elementos básicos de Excel, la pantalla, las barras, etc., para saber diferenciar entre cada uno de ellos. Aprenderás cómo se llaman, donde están y para qué sirven. También cómo obtener ayuda, por si en algún momento no sabes cómo seguir trabajando. Cuando conozcas todo esto estarás en disposición de empezar a crear hojas de cálculo en el siguiente tema. Abriendo Excel lo vamos hacer desde el botón iniciar situado, normalmente, en la esquina inferior izquierda de la pantalla. Coloca el cursor y haz clic sobre el botón Inicio se despliega un menú; al colocar el cursor sobre Programas, aparece otra lista con los programas que hay instalados en tu ordenador; buscar.

GESTION DE CENTROS DE CÓMPUTO

¿CÓMO GENERAR LISTAS AUTOMATICAS EN EXCEL? Configurar nuestras listas personalizadas. A continuación debemos ir al Botón de Office y hacer clic en Opciones de Excel. En la categoría Más Frecuentes, encontraremos dentro de la sección Opciones principales para trabajar con Excel el botón Modificar listas personalizadas.

Al presionar este botón accederás al cuadro Listas personalizadas donde podrás observar cuales son las listas personalizadas que ya tienes configuradas, modificarlas o simplemente crear nuevas listas personalizadas.

En este cuadro presionaremos la opción NUEVA LISTA y a continuación podemos agregar nuestras entradas de lista presionando el botón Seleccionar rango, procediendo a marcar en nuestra hoja de cálculo cual es el rango de datos que deseamos integre nuestra lista personalizada (en nuestro ejemplo el rango A2:A21). Una vez seleccionado presionamos en nuestro teclado el botón ENTER y de vuelta en el cuadro Listas personalizadas haremos clic en el botón Importar.

Una vez seguidos estos pasos deben quedar configuradas en nuestro equipo nuestras listas personalizadas, tal como en la imagen anexa:

GESTION DE CENTROS DE CÓMPUTO

Para finalizar damos clic al botón Aceptar Es importante destacar que sólo se puede crear listas personalizadas basándonos en un valor (texto, número, fecha u hora). No es posible crear una lista personalizada basándonos en un formato (color de celda, color de fuente e icono). Estos dos sencillos pasos nos permiten configurar listas personalizadas de una forma sencilla, ahorrándonos tiempo y haciendo más eficiente nuestro uso de Excel. Pueden probar que tal funciona su nueva lista personalizada insertando en cualquier celda de su hoja de cálculo una de las entradas que la integra:

VALIDACION DE INGRESO DE DATOS La validación de datos se usa para controlar el tipo de datos o los valores que los usuarios pueden escribir en una celda. Por ejemplo, es posible que desee restringir la entrada de datos a un intervalo determinado de fechas, limitar las opciones con una lista o asegurarse de que sólo se escriben números enteros positivos. En este artículo se describe el funcionamiento de la validación de datos en Excel y las diferentes técnicas de validación de datos existentes. No analiza la protección de celdas que es una característica que permite "bloquear" u ocultar ciertas celdas de una hoja de cálculo para que no se puedan editar ni sobrescribir. 6

GESTION DE CENTROS DE CÓMPUTO Para obtener más información sobre la protección de celdas, vea el tema sobre cómo proteger elementos de la hoja de cálculo o libro.

¿Qué es la validación de datos? La validación de datos es una función de Excel que permite establecer restricciones respecto a los datos que se pueden o se deben escribir en una celda. La validación de datos puede configurarse para impedir que los usuarios escriban datos no válidos. Si lo prefiere, puede permitir que los usuarios escriban datos no válidos en una celda y advertirles cuando intenten hacerlo. También puede proporcionar mensajes para indicar qué tipo de entradas se esperan en una celda, así como instrucciones para ayudar a los usuarios a corregir los errores. Por ejemplo, en un libro de marketing, puede configurar una celda para permitir únicamente números de cuenta de tres caracteres. Cuando los usuarios seleccionan la celda, puede mostrarles un mensaje como el siguiente:

Si los usuarios no tienen en cuenta este mensaje y escriben datos no válidos en la celda, como un número de dos o de cinco dígitos, puede mostrarles un mensaje de error específico. En un escenario un poco más avanzado, podría usar la validación de datos para calcular el valor máximo permitido en una celda según un valor que se encuentra en otra parte del libro. En el siguiente ejemplo, el usuario ha escrito 4.000 dólares en la celda E7, lo cual supera el límite máximo especificado para comisiones y bonificaciones.

GESTION DE CENTROS DE CÓMPUTO

Si se aumentara o redujera el presupuesto de nómina, el máximo permitido en E7 también aumentaría o se reduciría automáticamente. Las opciones de validación de datos se encuentran en la ficha Datos, en el grupo Herramientas de datos.

La validación de datos se configura en el cuadro de diálogo Validación de datos.

¿Cuándo es útil la validación de datos? La validación de datos es sumamente útil cuando desea compartir un libro con otros miembros de la organización y desea que los datos que se escriban en él sean exactos y coherentes. Puede usar la validación de datos para lo siguiente, entre otras aplicaciones: Restringir los datos a elementos predefinidos de una lista Por ejemplo, puede limitar los tipos de departamentos a Ventas, Finanzas, Investigación y desarrollo y TI. De forma similar, puede crear una lista de valores a partir de un rango de celdas que se encuentren en otra parte del libro.

GESTION DE CENTROS DE CÓMPUTO Lista desplegable creada mediante validación de datos

Restringir los números que se encuentren fuera de un intervalo específico Por ejemplo, puede especificar un límite mínimo de deducciones de dos veces el número de hijos en una celda específica

Restringir las fechas que se encuentren fuera de un período de tiempo específico Por ejemplo, puede especificar un período de tiempo entre la fecha actual y los 3 días siguientes

Restringir las horas que se encuentren fuera de un período de tiempo específico Por ejemplo, puede especificar un período de tiempo para servir el desayuno entre la hora en que abre el restaurante y cinco horas después.

Limitar la cantidad de caracteres de texto Por ejemplo, puede limitar el texto permitido en una celda a 10 caracteres o menos.

Validar datos según fórmulas o valores de otras celdas Por ejemplo, puede usar la validación de datos para establecer un límite máximo para comisiones y bonificaciones de 3.600 dólares

Mensajes de validación de datos Lo que los usuarios vean al escribir datos no válidos en una celda depende de cómo se haya configurado la validación de datos. Puede elegir mostrar un mensaje de entrada cuando el usuario seleccione la celda. Los mensajes de entrada suelen usarse para ofrecer a los usuarios orientación acerca del tipo de datos que debe especificarse en la celda. Este tipo de mensaje aparece cerca de la celda. Si lo desea, puede mover este mensaje y dejarlo visible hasta que el usuario pase a otra celda o presione ESC.

GESTION DE CENTROS DE CÓMPUTO

También puede elegir mostrar un mensaje de error que solo aparecerá cuando el usuario escriba datos no válidos.

FORMULAS CONDICIONALES La comprobación de si las condiciones son verdaderas o falsas y la realización de comparaciones lógicas entre expresiones son elementos comunes de varias tareas. Para crear fórmulas condicionales, puede utilizar las funciones Y, O, NO y SI. Por ejemplo, la función SI utiliza los siguientes argumentos.

Fórmula que usa la función SI prueba_lógica: condición que se desea comprobar.

valor_si_verdadero: valor que se devolverá si la condición se cumple. valor_si_falso: valor que se devolverá si la condición no se cumple.

Formulas Condicionadas Simples Si estás familiarizado con la programación, sabrás que la función SI… Entonces es una operación fundamental en todos los lenguajes. Puedes usarla también en

GESTION DE CENTROS DE CÓMPUTO Excel, donde actúa como una función lógica permitiéndote comprobar si ciertas condiciones son verdaderas o falsas. Simplemente le dice a Excel que, si una condición es cierta, realice la acción A; o en otro caso, realice la acción B. La función SI es una función lógica. Comprueba si una cierta condición es verdadera o falsa y a continuación actúa acorde. Por ejemplo, si un estudiante ha

Obtenido más de 75 puntos en un examen, imprime “Aprobado”, si no, imprime “Suspendido”. Sus usos más comunes: Comparar valores (¿A1 es mayor que B1?) Usar cálculos basados en el resultado de la comparación de valores (si A1 es mayor que B1, multiplica A1 por 10) Sintaxis La función Si tiene al siguiente sintaxis: SI(prueba_lógica;valor_si_verdadero;valor_si_falso) Dónde: prueba_lógica = la condición puesta a prueba, por ejemplo: ¿Tu nombre es John? ¿Obtuviste más de 75 puntos? valor_si_verdadero = texto, valor numérico o fórmula si la condición es verdadera valor_si_falso = texto, valor numérico o fórmula si la condición es falsa Para poner a prueba si una condición es verdadera o no, puedes usar operadores lógicos. Excel es compatible con los siguientes operadores lógicos: < Inferior a > Superior a <= Inferior o igual a

>= Superior o igual a = Igual <> No igual a 11

GESTION DE CENTROS DE CÓMPUTO Vamos a entender esto usando un ejemplo. Supón que tienes los resultados de un examen en el que una puntuación por encima de 75 es clasificada como “aprobada” e inferior a 75 clasificado como “suspendido”. Puedes hacer esto fácilmente usando la función SI de Excel: =SI(A1<75; “Aprobado”; “Suspendido”)

En ocasiones es más sencillo entender la función SI traduciéndola a palabras. En el ejemplo anterior, esencialmente estamos diciendo: si el valor de la celda A2 es inferior a 75, entonces introduce “Suspendido” en la celda C2. Si el valor es superior a 75, introduce “Aprobado” en C2.

Aquí hay otro ejemplo. Digamos que quieres comprobar si el nombre de un usuario es John =SI(A1=”John”; “Tu nombre es John”; “Tu nombre no es John”) Veamos un último ejemplo en el que se usa la función SI para encontrar celdas vacías: =SI(A1=””; “Vacía”, “”)

GESTION DE CENTROS DE CÓMPUTO

Así, usando la Función SI de esta forma, nos preguntamos: ¿La celda A1 está vacía? Si lo está, entonces escribe “Vacía”, y si no lo está, no escribas nada, deja la celda en blanco.

Formulas Condicionadas Dobles Utilizando la función Y podemos evaluar condiciones múltiples en Excel que deben cumplirse obligatoriamente. Es decir, solamente para aquellos casos donde cada condición sea verdadera la función Y regresará un valor verdadero. Bastará que solo un valor sea falso para que todo el resultado sea falso. Observa la siguiente función.

Como puedes observar, la función Y puede evaluar múltiples expresiones y solamente cuando cada una de ellas sea verdadera regresará como resultado el valor verdadero. Por el contrario observa el resultado para Gabriela donde solamente la columna Proyecto es falsa pero suficiente para ocasionar que todo el resultado sea falso.

GESTION DE CENTROS DE CÓMPUTO

FORMATO CONDICIONAL

Paso 1: Selecciona las celdas a las que quieres agregar el formato. Paso 6:

Paso 2:

Selecciona un estilo de formato en el menú desplegable. Verás que éste se aplica a las celdas que seleccionaste.

En la ficha Inicio, haz clic en el comando Formato condicional. Un menú desplegable aparecerá.

Paso 5:

Paso 3:

En el cuadro de diálogo, introduce un valor en el espacio correspondiente. En este ejemplo, queremos dar formato a las celdas cuyo valor es superior a $ 3.500.000

Selecciona Resaltar reglas de celdas o Reglas superiores e inferiores. Aquí, vamos a elegir la primera opción. Verás un menú con varias reglas. Agregar un formato condicional Paso 4: Selecciona la regla que quieras (Mayor que, por ejemplo).

GESTION DE CENTROS DE CÓMPUTO

Barra de Datos Una barra de datos le ayuda a ver el valor de una celda con relación a las demás. La longitud de la barra de datos representa el valor de la celda. Una barra más grande representa un valor más alto y una barra más corta representa un valor más bajo. Las barras de datos son útiles para encontrar números más altos y más bajos especialmente con grandes cantidades de datos, como las mayores y menores ventas de juguetes en un informe de ventas.

GESTION DE CENTROS DE CÓMPUTO

Aplicar Formato 1º. Selecciona el rango C4:C18 2º. En la ficha Inicio, en el grupo Estilos, haz clic en la flecha junto a Formato condicional 3º. Haz clic en Barras de datos y, a continuación se muestran diferentes colores de relleno: degradado y sólido, la opción Más reglas para realizar otras configuraciones. Selecciona un icono de la barra de datos.

GESTION DE CENTROS DE Cร MPUTO

El resultado serรก el siguiente:

GESTION DE CENTROS DE CÓMPUTO TABLAS DINAMICAS 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. Para crear una tabla dinámica que responda a la pregunta, sigue estos pasos:

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

GESTION DE CENTROS DE CÓMPUTO

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

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

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

GESTION DE CENTROS DE CÓMPUTO Formato de valores en una tabla dinámica En esta ocasión te mostraré cómo dar formato rápidamente a los valores agrupados de una tabla dinámica de manera de puedan tener el formato de número adecuado. Solamente sigue los siguientes pasos: Supongamos la siguiente tabla dinámica:

Para dar formato a los valores numéricos debes hacer clic sobre el campo correspondiente dentro del área Valores.

Del menú mostrado debes seleccionar la opción Configuración de campo de valor.

GESTION DE CENTROS DE CÓMPUTO

Se mostrará el cuadro de diálogo Configuración de campo de valor.

Debes hacer clic en el botón Formato de número y se mostrará el cuadro de diálogo Formato de celdas donde podrás seleccionar el formato deseado:

GESTION DE CENTROS DE CÓMPUTO Después de hacer la selección adecuada acepta los cambios y de inmediato se aplicará el nuevo formato a todos los valores de la tabla dinámica:

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

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

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

GESTION DE CENTROS DE CÓMPUTO

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

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

BUSCARV La función BUSCARV tiene cuatro argumentos, los primeros tres son obligatorios y el último es opcional. Para poder hacer un buen uso de la función debemos tener claridad sobre el significado de cada argumento. Como primer argumento de la función BUSCARV debemos indicar el valor que estamos buscando. Ya sea que proporcionamos el valor directamente o colocamos una referencia de celda en donde se encuentra el valor.

GESTION DE CENTROS DE CÓMPUTO Debemos recordar que el valor del primer argumento de la función será buscado siempre en la primera columna de la tabla de datos. No es posible buscar en una columna diferente que no sea la primera columna. El segundo argumento de la función indica la totalidad del rango que contiene los datos. En este rango es importante asegurase de incluir la columna que vamos a necesitar como resultado.

Y la tabla tiene encabezados, lo más recomendable es excluirlos del rango para evitar que la función considere a los títulos dentro de la búsqueda. El tercer argumento indica la columna que deseamos obtener como resultado:

El último argumento de la función es opcional, pero si no proporcionamos un valor, la función BUSCARV hará una búsqueda aproximada. Para que la función realice una búsqueda exacta debemos colocar el valor falso y obtendremos como resultado el valor de la columna que hayamos indicado.

GESTION DE CENTROS DE CÓMPUTO Estructura de la función de Excel BUSCARV

Estructura de la función de Excel BUSCARV

Indicador_columnas:

Es el valor del número de columna de matriz_buscar_en

Valor_buscad o

Dos o más columnas de datos

desde la cual debe devolverse el valor coincidente.

se entiende como el criterio que se va a buscar en la primera columna de la matriz de tabla

Matriz_buscar_en:

GESTION DE CENTROS DE CÓMPUTO Ejemplo:

Para obtener los datos deseados insertamos en una celda de nuestra preferencia la función Excel BUSCARV: =BUSCARV(E3;$B$3:$C$12;2;FALSO) E3: Representa el valor buscado que en este caso es el nombre de la fruta. $B$3:$C$12: Representa la matriz donde se van a buscar los datos. 2: Representa el indicador de columnas de donde se va a extraer la información FALSO: Representa de qué forma se quieren los datos y en efecto se desean de manera exacta. De forma visual la función Excel BUSCARV aplicada se representa de la siguiente manera:

GESTION DE CENTROS DE CÓMPUTO El resultado final del ejemplo sería algo como lo que se muestra a continuación:

MACRO ¿Qué es una macro de Excel? Si utilizas Excel frecuentemente es posible que en alguna ocasión te hayas encontrado ejecutando una misma serie de acciones una y otra vez. Esas acciones que haces repetidas veces se podrían automatizar con una macro. Una macro es un conjunto de comandos que se almacena en un lugar especial de Excel de manera que están siempre disponibles cuando los necesites ejecutar.

Paso 1: Activar la Barra de Herramientas Programador Para escribir macros, necesitará acceso a la barra de herramientas Programador en la cinta superior. Ésta está oculta por defecto. Para activarla, vaya a Archivo->Opciones>Personalizar Cinta y marque la casilla junto a ‘Programador’.

GESTION DE CENTROS DE CÓMPUTO Pulse OK. Ahora debería ver la barra de herramientas Programador en la cinta.

Paso 2: Habilitar Macros Las macros están deshabilitadas por defecto en Excel. Es así para protegerle de macros maliciosas que se ejecutan automáticamente en ficheros Excel descargados. Para usar macros, tendrá que primero habilitarlas desde el Centro de Confianza. Para hacer esto vaya a Archivo -> Opciones -> Centro de Confianza. Haga click en el botón ‘Ajustes Centro de Confianza’ en este menú.

En esta nueva ventana, vaya a ‘Ajustes Macro’ y seleccione el botón de radio ‘Habilitar todas las macros’.

GESTION DE CENTROS DE CÓMPUTO Haga click en OK. Ahora puede empezar a usar macros en sus hojas de cálculo Excel.

Paso 3: Preparar la Hoja de Cálculo En este tutorial, crearemos una macro muy simple para cambiar el color, tamaño y negrita del texto. En su hoja de cálculo, escriba algún texto en una de las celdas. Más tarde grabaremos una macro para cambiar el formato de este texto. Excel provee de una herramienta especial que permite crear una macro sin necesidad de conocer los detalles del lenguaje de programación.

Un lenguaje de programación Las macros se escriben en un lenguaje de computadora especial que es conocido como Visual Basic for Applications (VBA). Este lenguaje permite acceder a prácticamente todas las funcionalidades de Excel y con ello también ampliar la funcionalidad del programa.

GESTION DE CENTROS DE CÓMPUTO Ejemplo:

GESTION DE CENTROS DE CĂ&#x201C;MPUTO

BIBLIOGRAFIA http://www.excellentias.com/funcion-excel-buscarv/ http://exceltotal.com/tutorial-excel-2010-funcion-buscarv/ https://www.udemy.com/blog/tutorial-de-macros-excel-como-crear-una-sencillamacro-en-excel-2013/ http://exceltotal.com/que-es-una-macro-de-excel/ http://es.calameo.com/read/002571161a8b76e91cb00 http://www.fisicacollazos.260mb.com/archivos/Manual%20Electronics%20Workbenc hECI.pdf