Excel Avanzado

Guía Básica de Excel

Microsoft Excel es una herramienta poderosa para la gestión y análisis de datos, utilizada en entornos empresariales, académicos y personales. Permite organizar, calcular y visualizar información de manera eficiente mediante funciones avanzadas y herramientas integradas.

En esta guía, aprenderás a utilizar las principales funciones de Excel, desde ordenar y filtrar datos hasta crear gráficos y aplicar fórmulas esenciales.

--------------------------------------------------------------------------------------------------------------------------------------------------

Pestaña "Inicio" de Excel

La pestaña "Inicio" es el punto de partida en Excel y ofrece una variedad de herramientas fundamentales que facilitan la gestión y el formato de tus datos. A continuación, exploraremos algunas de las funciones más importantes que encontrarás en esta pestaña.

Ordenar y Filtrar

Permite organizar datos en orden ascendente o descendente y aplicar filtros basados en criterios específicos.

Ordenar

Se usa para organizar datos en orden ascendente o descendente según un criterio específico (números, fechas, nombres, etc.).

• Orden ascendente y descendente: Puedes organizar los datos en orden alfabético (A-Z o Z-A) o numérico (menor a mayor o viceversa).

• Orden personalizado: Si necesitas un orden específico, como agrupar por categorías, puedes crear una lista personalizada.

Pasos para ordenar datos:

1. Selecciona el rango de datos a ordenar.
2. Dirígete a la pestaña "Inicio" y haz clic en "Ordenar y Filtrar".
3. Selecciona "Orden personalizado" si deseas definir varias condiciones de ordenamiento.
4. Configura el criterio (alfabético, numérico o personalizado).
5. Confirma la operación y revisa los resultados.

Aquí hay una guía más detallada para ordenar datos en EXCEL

Filtrar

Permite mostrar solo los datos que cumplen con ciertas condiciones, ocultando temporalmente el resto. Se pueden aplicar filtros personalizados, como "mayor que", "contiene" o "comienza con".

Para filtrar:

1. Selecciona tus datos.
2. Haz clic en "Filtro".
3. Aparecerán flechas en los encabezados para aplicar criterios específicos.

 

Para Filtrar en Excel te presento este video 

👇

     

 

Esto es para entender mejor Ordenar y Filtrar ☝

Formato Condicional

El Formato Condicional es una herramienta poderosa que te permite resaltar celdas basadas en condiciones que determines. Por ejemplo, puedes utilizarlo para marcar automáticamente las celdas que contienen valores por encima de un cierto umbral, lo que te ayuda a identificar rápidamente datos importantes. El Formato Condicional te permite aplicar colores, iconos o barras para resaltar valores específicos.

                        

Para aplicarlo:

1. Selecciona el rango de datos.
2. Ve a "Formato condicional" en la pestaña "Inicio".
3. Elige una regla, como "Mayor que" o "Menor que".

Aquí te presento este video sobre Formato Condicional ☝

--------------------------------------------------------------------------------------------------------------------------------------------------

Pestaña "Insertar"

La pestaña "Insertar" en Excel es donde puedes añadir elementos visuales y estructurales a tus hojas de cálculo, mejorando la presentación y análisis de tus datos. A continuación, exploraremos algunas de las funciones más importantes que encontrarás en esta pestaña.

Tablas Dinámicas

Las Tablas Dinámicas son herramientas poderosas que te permiten resumir y analizar grandes volúmenes de datos de manera eficiente. Con ellas, puedes reorganizar la información fácilmente para descubrir patrones y tendencias.

Para crear una tabla dinámica:

1. Selecciona el rango de datos que deseas analizar.
2. Haz clic en "Insertar" y selecciona "Tabla Dinámica".
3. Elige dónde deseas que se coloque la tabla (en la hoja actual o en una nueva) y haz clic en "Aceptar".

Aquí te presento este video sobre crear una tabla dinámica ☝

Tablas Dinámicas Recomendadas

Si no sabes qué tipo de tabla dinámica usar, Excel te da opciones automáticas según los datos que tienes. Solo eliges la mejor y listo.

Excel ofrece opciones automáticas de tablas dinámicas:

1. Selecciona los datos.

2. Ve a "Insertar", "Tablas Dinámicas Recomendadas".

3. Elige la opción deseada y haz clic en "Aceptar".

Aquí te presento este video sobre crear una tabla dinámica recomendada ☝

Gráficos 

Los gráficos transforman tus datos en imágenes fáciles de entender.

Para visualizar datos de forma gráfica:

1. Selecciona los datos.

2. Ve a "Insertar",  "Gráficos" y elige un tipo.

3. Escoge el tipo de gráfico según la información que deseas representar.

4. Personaliza títulos, colores y etiquetas.

Los gráficos permiten visualizar datos de forma clara y efectiva. Permite representar datos visualmente mediante distintos tipos de gráficos (barras, líneas, sectores, etc.).

       

     Tipos de gráficos disponibles

• Columnas y barras: Comparaciones entre diferentes categorías.

• Líneas: Análisis de tendencias a lo largo del tiempo.

• Circulares (pastel): Distribución de proporciones.

• Dispersión: Relaciones entre variables numéricas.     

                         

                                                          

--------------------------------------------------------------------------------------------------------------------------------------------------

Pestaña "Fórmulas"

La pestaña "Fórmulas" en Excel es fundamental para realizar cálculos y análisis avanzados. Aquí encontrarás una amplia variedad de funciones que pueden facilitar la manipulación y análisis de datos. A continuación, exploraremos algunas de las características y funciones más importantes de esta pestaña.

Biblioteca de Funciones

Conjunto de funciones predefinidas en Excel para realizar cálculos matemáticos, estadísticos y lógicos.

La Biblioteca de Funciones agrupa las diversas funciones que Excel ofrece, organizadas en categorías como:

1. Lógicas

Funciones para tomar decisiones basadas en condiciones:

• SI: Evalúa una condición y devuelve un valor según el resultado.
• =SI(A1 > 100; "Alto"; "Bajo")

2. Búsqueda y referencia

Funciones para buscar datos en tablas:

• BUSCARV: Busca un valor en la primera columna y devuelve un valor de la misma fila.
• =BUSCARV("Producto A"; A1:B10; 2; FALSO)

3. Texto

Funciones para manipular cadenas de texto:

• CONCATENAR: Une varias cadenas en una sola.
• =CONCAT(A8;B8)

4. Matemáticas y trigonométricas

Funciones para cálculos matemáticos:

• SUMA: Suma un rango de celdas.
• =SUMA(C4;E4)

5. Fecha y hora

Funciones para trabajar con fechas y horas:

• HOY(): Devuelve la fecha actual.
• =AHORA()

6. Más funciones

Incluye funciones avanzadas para estadísticas y finanzas.

Ejemplo: Utilizar la función SUMA para calcular el total de ingresos en un rango de celdas.

                                        

Nota:

Las configuraciones regionales en Excel afectan varios aspectos del programa, incluyendo los separadores de argumentos (punto y coma (;) en Latinoamérica y coma (,) en EE. UU.), el formato de números (coma para decimales en Latinoamérica y punto en otras regiones), el formato de fechas (día/mes/año frente a mes/día/año), así como los nombres de funciones que pueden variar. Además, influyen en la configuración de moneda y el idioma de la interfaz de usuario, lo que es crucial para evitar errores al trabajar con datos.

-----------------------------------------------------------------------------------------------------------------------------------------------------------

Pestaña "Datos"

La pestaña "Datos" en Excel es esencial para la gestión y análisis de información. Aquí encontrarás herramientas que te permiten importar, organizar y limpiar tus datos de manera efectiva. A continuación, exploramos las principales funciones de esta pestaña.

Texto en Columnas

Esta función es útil para dividir datos en celdas separadas. Por ejemplo, puedes dividir una columna que contiene nombres completos en nombres y apellidos.

Separar Texto en Columnas:

• Paso 1: Selecciona la columna que contiene el texto que deseas separar.
• Paso 2: Ve a la pestaña "Datos".
• Paso 3: Haz clic en "Texto en Columnas".
• Paso 4: Selecciona "Delimitado" o "De ancho fijo", según cómo estén organizados tus datos, y haz clic en "Siguiente".
• Paso 5: Si elegiste "Delimitado", selecciona el delimitador (como coma o espacio) y haz clic en "Siguiente".
• Paso 6: Elige el formato de los datos y haz clic en "Finalizar".

Quitar Duplicados

Esta función te ayuda a limpiar tus datos eliminando entradas duplicadas en un rango seleccionado, asegurando que cada dato sea único.

Eliminar Valores Duplicados:

• Paso 1: Selecciona el rango de datos del cual deseas eliminar duplicados.
• Paso 2: Ve a la pestaña "Datos".
• Paso 3: Haz clic en "Quitar duplicados".
• Paso 4: En el cuadro de diálogo, selecciona las columnas que deseas verificar y haz clic en "Aceptar".
• Paso 5: Se mostrará un mensaje indicando cuántos duplicados se han eliminado.

Validación de Datos

La opción de Validación de datos te permite establecer reglas sobre qué tipo de datos pueden ingresarse en una celda. Esto es útil para mantener la integridad de tus datos.

Ejemplo: Puedes restringir una celda para que solo acepte números entre 1 y 100.

Establecer Reglas de Validación:

• Paso 1: Selecciona la celda o rango donde deseas aplicar la validación.
• Paso 2: Ve a la pestaña "Datos".
• Paso 3: Haz clic en "Validación de datos".
• Paso 4: En el cuadro de diálogo, selecciona el tipo de validación (como lista, número, fecha, etc.).
• Paso 5: Define los criterios y haz clic en "Aceptar".
• Paso 6: Si se establece una lista, puedes ingresar los valores permitidos.

Para aplicar Validación de Datos ☝

Agrupar 

La función de Agrupar te permite combinar filas o columnas para crear un esquema más organizado. Esto es útil para ocultar o mostrar detalles específicos sin perder la estructura de tus datos.

• Uso: Selecciona las filas o columnas que deseas agrupar y haz clic en "Agrupar". Esto creará un botón que permite expandir o contraer la vista.

• Paso 1: Selecciona las filas o columnas que deseas agrupar.
• Paso 2: Ve a la pestaña "Datos".
• Paso 3: Haz clic en "Agrupar".
• Paso 4: Aparecerá un botón que te permitirá expandir o contraer el grupo.

Desagrupar

La opción de Desagrupar permite revertir el agrupamiento de filas o columnas previamente agrupadas. Esto es útil cuando ya no necesitas una vista resumida y deseas ver todos los datos nuevamente.

• Uso: Selecciona el grupo que deseas desagrupar y haz clic en "Desagrupar".

Paso 1: Selecciona el grupo que deseas desagrupar.

Paso 2: En la pestaña "Datos", haz clic en "Desagrupar".

Subtotal

La función de Subtotal permite agregar automáticamente subtotales a un conjunto de datos. Puedes elegir diferentes funciones (como suma, promedio, conteo, etc.) para calcular subtotales en grupos de datos específicos.

• Uso: Selecciona el rango de datos, haz clic en "Subtotal" y elige la columna para calcular los subtotales y la función que deseas aplicar.

Añadir Subtotales:

• Paso 1: Asegúrate de que tus datos estén organizados por la columna que deseas resumir.
• Paso 2: Selecciona el rango de datos.
• Paso 3: Ve a la pestaña "Datos" y haz clic en "Subtotal".
• Paso 4: En el cuadro de diálogo, elige la columna para agrupar y la función (suma, promedio, etc.) que deseas aplicar.
• Paso 5: Haz clic en "Aceptar" y los subtotales se añadirán a tu tabla.

-------------------------------------------------------------------------------------------------------------------------------------------------

Pestaña "Revisar"

La pestaña "Revisar" en Excel es clave para garantizar la calidad y precisión de tus hojas de cálculo. Aquí encontrarás herramientas para la revisión, el comentario y la protección de tus datos. A continuación, exploramos las principales funciones de esta pestaña.

Proteger Hoja y Libro

Proteger hojas y libros es fundamental para mantener la integridad de tus datos y evitar modificaciones no deseadas. Asegúrate de recordar las contraseñas establecidas, ya que son necesarias para desproteger las hojas y libros.

Proteger hoja

Puedes proteger tu hoja de cálculo para evitar cambios no deseados. Esto te permite establecer contraseñas y restringir el acceso a ciertas celdas, asegurando que solo los usuarios autorizados puedan realizar modificaciones.

Pasos para Proteger Hoja:

• Paso 1: Selecciona la hoja que deseas proteger.
• Paso 2: Ve a la pestaña "Revisar".
• Paso 3: Haz clic en "Proteger hoja".
• Paso 4: En el cuadro de diálogo que aparece, puedes ingresar una contraseña (opcional) para mayor seguridad.
• Paso 5: Marca las opciones que deseas permitir a los usuarios (como seleccionar celdas bloqueadas o desbloqueadas).
• Paso 6: Haz clic en "Aceptar".
• Paso 7: Si estableciste una contraseña, se te pedirá que la confirmes. Ingrésala nuevamente y haz clic en "Aceptar".

Desproteger Hoja

Si necesitas realizar cambios en la hoja protegida:

Pasos para Desproteger Hoja:

• Paso 1: Selecciona la hoja protegida.
• Paso 2: Ve a la pestaña "Revisar".
• Paso 3: Haz clic en "Desproteger hoja".
• Paso 4: Ingresa la contraseña si se estableció y haz clic en "Aceptar".

Proteger libro

Con esta función, puedes proteger todo el libro de Excel, evitando que se realicen cambios en la estructura del archivo, como agregar, mover o eliminar hojas, en pocas palabras impide que se realicen cambios a la estructura del libro, como agregar o eliminar hojas.

Pasos para Proteger Libro:

• Paso 1: Ve a la pestaña "Revisar".
• Paso 2: Haz clic en "Proteger libro".
• Paso 3: En el cuadro de diálogo, puedes ingresar una contraseña (opcional).
• Paso 4: Haz clic en "Aceptar".
• Paso 5: Si estableciste una contraseña, se te pedirá que la confirmes. Ingrésala nuevamente y haz clic en "Aceptar".

Desproteger Libro

Para realizar cambios en la estructura del libro:

Pasos para Desproteger Libro:

• Paso 1: Ve a la pestaña "Revisar".
• Paso 2: Haz clic en "Proteger Libro" y luego te aparecera una ventanita que dice "Desproteger libro". 
• Paso 3: Ingresa la contraseña que se estableció y haz clic en "Aceptar".

Funciones en Excel

SUMA

Suma un rango de celdas:

=SUMA(A1:A10)

SUMARSÍ

Suma valores con una condición:

=SUMAR.SI(A1:A10, ">10")

AHORA

Muestra la fecha y hora actual:

=AHORA()

ALEATORIO y ALEATORIO.ENTRE

Genera números aleatorios:

=ALEATORIO()   'Entre 0 y 1
=ALEATORIO.ENTRE(1,100)   'Entre 1 y 100

AÑO

Extrae el año de una fecha:

=AÑO(A1)

CONTAR y CONTAR.SI

Cuenta celdas con valores:

=CONTAR(A1:A10)
=CONTAR.SI(A1:A10,">10")

BUSCARV y BUSCARH

Busca valores en tablas:

=BUSCARV(10, A2:B10, 2, FALSO)
=BUSCARH("Juan", A1:D4, 2, FALSO)

MIN y MAX

Encuentra el valor mínimo o máximo:

=MIN(A1:A10)
=MAX(A1:A10)

PROMEDIO y PROMEDIO.SI

Calcula el promedio:

=PROMEDIO(A1:A10)
=PROMEDIO.SI(A1:A10, ">10")

SI

Evalúa condiciones:

=SI(A1>10, "Aprobado", "Reprobado")

CONCATENAR

Une textos:

=CONCATENAR(A1, " ", B1)

CONTAR.BLANCO

Cuenta celdas vacías:

=CONTAR.BLANCO(A1:A10)

SUBTOTALES

Calcula subtotales con filtros:

=SUBTOTALES(9, A1:A10)   'SUMA con filtros

INDICE

Devuelve un valor de una matriz:

=INDICE(A1:C3, 2, 3)