Excel Avanzado

EXCEL AVANZADO

Hola, ¿Cómo están? espero que estén todos muy bien. En el día de hoy explicaremos funciones del Excel Avanzado, incluídas las opciones Ordenar y Filtrar y Formato Condicionar, Tablas Dinámicas y Tablas Recomendadas y muchas opciones más, así también como algunas de las fórmulas más utilizadas y para que sirven.

Primero, empezaremos antes que todo explicando las fórmulas. Las fórmulas son aquellas combinaciones que nos ayudan a resolver problemas numéricos o nos facilitan la obtención de soluciones con respecto a dichos problemas.

FÓRMULAS

SUMA: Esta fórmula nos permite después de seleccionar un rango de números la respectiva suma de estos.

SUMAR.SI: Esta opción nos permite hacer la suma de un respectivo rango dependiendo el criterio que hayamos colocado. Ej:

Si tenemos dos columnas: en la primera columna están los nombres de "Hombres y Mujeres", mientras que en la segunda están la cantidad de medallas que estos ganaron en un concurso. Entonces, si queremos saber la cantidad de medallas que uno de estos grupos ganó en específico, escribimos la fórmula siguiente:

=SUMAR.SI(F3:F8;"mujer";G3:G8

Pasos:

• Escribimos el signo de igual (para toda fórmula que hayamos que hacer siempre agregamos el signo de igual al principio.

• Colocamos o mejor dicho, escribimos la formula, que en este caso es "SUMAR.SI"

• Seleccionamos el rango completo de donde se mencionan a Hombres y Mujeres.

• Escribimos el nombre del grupo en específico, ya sea, "Hombres o Mujeres"

• Y por último seleccionamos el rango en donde se mencionan la cantidad de medallas que hay para cada grupo y pulsamos ENTER.

AHORA: Devuelve el número de series de la fecha y horas actuales.

Para poder escribir esta fórmula hacemos lo siguiente:

• Escribimos el símbolo de igual (=)

• Colocamos la fórmula AHORA.

• Abrimos y cerramos Paréntesis "()".

• Entonces, luego de que hayamos escribido la fórmula completa =AHORA() seleccionamos ENTER.

• Inmediatamente nos arrojará una combinación, la cual dicha combinación si queremos convertirla en una fecha o una hora solo cambiamos el formato de cerda y automáticamente se nos pondrá la fecha u hora actual.

ALEATORIO Y ALEATORIO ENTRE: Esta opción solamente sirve para agregar una cadana de número dentro de un rango en específico.

AÑO: Extrae solo el año de una fecha numérica.

CONTAR: Suma la cantidad de celdas siempre y cuando en las cuales se encuentren solo valores numéricos.

CONTAR.SI: Suma laa cantidad de celdas dependiendo de un criterio en específico.

BUSCARV: Busca un valor vertical dentro de un conjunto de rangos en específicos.

BUSCARH: Busca un valor horizontal dentro de un conjunto de rangos específicos.

MIN: Busca el valor numérico más bajito dentro de un rango seleccionado.

MAX: Al contrario de la función MIN, esta opción nos permmite buscar el valor más alto dentro de un rango seleccionado.

PROMEDIO: Busca un valor promedio dentro de un rango seleccionado.

PROMEDIO.SI: busca el valor numérico promedio teniendo en cuenta ciertos criterios, loos cuales serán puestos por el propio usuario.

SI: Calcula un valor dependiendo de una condición determinada

CONCATENAR: Tiene como función unir dos o más palabras que se encuentren en celdas diferentes en una sola celda.

CONTAR.BLANCO: Tiene como función sumar todas las celdas que se entren en blanco dentro de un rango en el que hayamos seleccionados.

SUBTOTALES: Suma todos los valores numéricos de un rango seleccionado.

ÍNDICE: Devuelve un  valor o la referencia a un valor de un tabla o rango.

...Ya terminando con la definición de estas respectivas fórmulas pasamos con lo siguiente, lo cual es, las funciones de Excel Avanzado.

Ordenar y Filtrar

Ordenar

En primer lugar, la función ordenar es una función perteneciente al grupo o bloque de Modificar.

Esta opción nos permite luego de haber seleccionado un respectivo rango y aplicar la función de Ordenar organizar los datos para facilitar su análisis, dichos datos se pueden organizar, ya sea, de forma ascendente o descendente.

Tambien, dentro del bloque Modificar hay otras opciónes referentes a la función de Ordenar las cuales son:

Ordenar de menor a mayor o de A a Z: Nos permite luego de haber seleccionado una respectiva columna o rango organizar los texto, ya sea al igual que el abecedario (A hasta Z) o si son con datos numéricos con los cuales estamos tratando estos se organizarían desde el más bajito al más alto.

Ordenar de mayor a menor o de Z a A: Esta opción es todo lo contrario de la función "Ordenar de menro y mayor".

Orden personalizado: Esta opción nos permite organizar nuestros datos según sean nuestros gustos.

Tambien, dentro de dicho bloque (Modificar) se encuentra otra función la cual es Filtrar.

Esta opción es básicamente los mismo, con respecto a la opción Ordenar. Lo único que cambia aquí es que los datos aquí se organizar por columnas y en orden específico.

Cuando Excel Ordena y/o Filtra, utiliza los siguientes criterios:

Ordenar por:

• Texto o datos Mixto (texto y número)

• Fecha

• Número

• Listas personalizadas

• Color de Celda, color de fuente o tipo de icono.

• Niveles (más de un criterio)

• Filas o columnas de un listado (de izquierda a derecha o de arriba abajo)

Filtrar por:

Segun el tipo de dato que contiene una celda con los que se pueden aplicar diferentes criterios en el momento de filtrar.

Los tipos de datos que se pueden filtrar son: 

• Texto (igual a, contiene, personalizado, etc)

• Fechas (días, meses, años trimestre, rango de fechas, etc)

• Número (mayor que, menor que, igual, diferente de, diez mejores, etc)

• Por color (celda, de fuente, de contenido, de icono, etc).

Paleta Insertar Tablas Dinámicas y Tablas Dinámicas Recomendadas.

Tablas Dinámicas: Una tabla dinámica es una herramienta avanzada para calcular, resumir y analizar datos que le permite ver comparaciones, patrones y tendencias en ellos.

Tablas Dinámicas Recomendadas: Una tabla dinámica es un informe resumido en el que puedes cambiar la información a tu conveniencia y rápidamente es por ello el nombre de dinámicas.

GRÁFICOS

Gráficos de Columna: Este tipo de gráfico hace un énfasis especial en las variaciones de los datos a través del tiempo. Las categorías de datos aparecerán en el eje horizontal y los valores en el eje vertical.

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

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

Gráfico 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.

Gráfico de Area: Los gráficos de área muestran la importancia de los valores a través del tiempo. Un gráfico de área es similiar a un gráfico de línea, pero ya que el área entre las líneas está relleno, 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áfico de 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.

Bibliotecas de Funciones

1. Insertar función:

Te permite buscar fácilmente una función ingresando la descripción de lo que estás buscando.

2. Autosuma: Con este comando puedes obtener automáticamente el resultado de funciones comunes.

3. Recientes: Usa esta categoría para acceder a las funciones con las que has trabajado recientemente.

4. Financieras: Esta categoría contiene funciones para cálculos financieros como determinar un pago (PAGO) o la tasa de interés de un préstamo (RATE)

5. Lógicas: Las funciones en esta categoría evalúan los argumentos por valor o condición. Por ejemplo, si una orden es superior a $50 se cobra $4.99 por envío. Pero si es superior a $100, no se cobra.

6. Texto: Esta categoría contiene funciones que trabajan con argumentos de texto. Por ejemplo, convertir palabras a mayúsculas o reemplazar textos.

6. Fecha y Hora: Contiene funciones para trabajar con datos y tiempo. Obtienes resultados como la fecha y hora actual.

7. Búsqueda y referencia: Contiene funciones para obtener resultados por búsqueda y referencia. Por ejemplo, puedes agregar un hipervínculo a una celda o ver el valor de la intersección de una fila y columna.

8. Matemáticas y trigonométricas: Incluye funciones para argumentos numéricos. Por ejemplo, puedes redondear valores (REDONDEAR), encontrar el valor de Pi (PI), multiplicar (PRODUCTO), subtotal (SUBTOTALES) y mucho más.

9. Más funciones: Contiene funciones adicionales dentro de las categorías de Estadística, Ingeniería, Cubo, Información y Compatibilidad.

Textos en columnas 

• Seleccione la celda o columna que contiene el texto que quiere dividir.

• Seleccione Datos > Texto en columnas.

• En el Asistente para convertir texto en columnas, seleccione Delimitado > Siguiente.

• Seleccione los Delimitadores para sus datos. Por ejemplo, Coma y Espacio. Puede ver una vista previa de los datos en la ventana Vista previa de datos.

• Seleccione Siguiente.

• Seleccione el Formato de datos de la columna o use la sugerencia de Excel.

• Seleccione el Destino, que es donde quiere que aparezcan los datos divididos en la hoja de cálculo.

• Seleccione Finalizar.

Quitar duplicados: 

Seleccione las celdas en las que desea comprobar duplicados.

Haga clic en Inicio > Formato condicional >Resaltar reglas de celdas > Valores duplicados.

En el cuadro que se encuentra junto a valores con, elija el formato que desea aplicar a los valores duplicados y haga clic en Aceptar.

Quitar valores duplicados

Cuando use la característica Quitar duplicados, los datos duplicados se eliminarán de manera permanente. Antes de eliminar los duplicados, es una buena idea copiar los datos originales a otra hoja de cálculo para que no pierda ninguna información de forma accidental.

Seleccione el rango de celdas con valores duplicados que desea quitar.

Haga clic en Datos > Quitar duplicados y, a continuación, debajo de Columnas, active o desactive las columnas donde desea eliminar los duplicados.

Por ejemplo, en esta hoja de cálculo, la columna Enero tiene información de precios que deseo conservar.

Por tanto, he desactivado Enero en el cuadro Quitar duplicados.

Validación de datos:

Seleccione una o más celdas para validar.

En la pestaña Datos, del grupo Herramientas de datos, haga clic en Validación de datos.

En la pestaña Configuración, en el cuadro Permitir, seleccione Lista.

En el cuadro Origen, escriba los valores de la lista, separados por comas. Por ejemplo:

Para limitar una respuesta a dos opciones (por ejemplo "¿Tiene hijos?"), escriba Sí,No.

Para limitar la reputación de la calidad de un proveedor a tres clasificaciones, escriba Baja, Media, Alta.

Procedimiento recomendado: También puede crear entradas de la lista mediante referencia a un rango de celdas ubicadas en otra parte del libro. La manera más eficaz es crear la lista, después aplicar el formato como una tabla de Excel (desde la pestaña Inicio, seleccione Estilos > Dar formato como tabla > elija el estilo de tabla que sea mejor para usted). Después, seleccione el rango de cuerpo de datos de la tabla, que es la parte de la tabla que solo tiene la lista, no el encabezado de la tabla (en este caso, Departamento), y proporciónele un nombre significativo en el cuadro Nombre encima de la columna A.

.

a que probablemente nunca cambien. Si tiene una lista que pueda cambiar, o si necesita agregar o quitar elementos de vez en cuando, le irá mejor si sigue el paso del siguiente procedimiento recomendado.

En lugar de escribir los valores de la lista en el cuadro Origen de la validación de datos, agregue el nombre que acaba de definir precedido de un signo igual (=).

Agrupar:

1. para mostrar filas para un nivel, haga clic en los símbolos de esquema 1,2,3 adecuados.

2. El nivel 1 contiene las ventas totales de todas las filas de detalle.

3. El nivel 2 contiene las ventas totales para cada mes en cada región.

4. El nivel 3 contiene las filas de detalle — en este caso, las filas 11 a 13.

5. para expandir o contraer datos en el esquema, haga clic en los símbolos de esquema + y - .

Desagrupar:

Identifica las hojas que estén agrupadas. Las pestañas de hojas agrupadas estarán resaltadas en un tono o color similar y el texto en la pestaña de la hoja activa en el grupo estará en negrita.

Haz clic derecho sobre la pestaña de una de las hojas agrupadas y elige "Desagrupar hojas" en el menú emergente. Las hojas se desagruparán y podrás realizar cambios sin afectar a todas las hojas.Subtotal: Los subtotales se calculan con una función de resumen, como Suma o Promedio, con la función SUBTOTAL. Puede mostrar más de un tipo de función de resumen de cada columna.

Proteger Hojas: Para que los datos que tienes en una determinada hoja del libro de cálculo queden protegidos en su totalidad y así nadie pueda realizar cambios sobre ellos, simplemente pueda consultarlos pero sin realizar modificaciones vas a seguir los pasos siguientes:

· Abre un libro de Excel, realiza alguna anotación de datos en la hoja 1.

· Situado en esta hoja pulsa en la pestaña Revisar, en la zona superior, después dentro del grupo Cambios pulsa en Proteger hoja.

· Se abre una ventana nueva, fíjate que están con un clic arriba donde pone: Proteger hoja y contenido de las celdas bloqueadas, así como las dos opciones superiores de la lista, luego haz clic en Aceptar.

Proteger Libros: Esto es muy fácil hacerlo. Mientras trabajo en Excel, vaya a la pestaña Archivo elegir la ficha de Información haga clic en la opción de Proteger Libro y elija Cifrar con Contraseña.

Como es el caso con cualquier contraseña, elija una combinación fuerte y segura de letras, números y caracteres, teniendo en cuenta que las contraseñas distinguen mayúsculas de minúsculas.