HOJAS DE CÁLCULO - Capacitacion SysdataColombia

Vaya al Contenido
HOJAS DE CÁLCULO


En este módulo, los estudiantes aprenderán el uso avanzado de Excel para la elaboración de informes en contabilidad, administración y estadística. Adquirirán habilidades para gestionar datos, nombres de rangos y celdas, aplicar formato condicional y utilizar tablas de datos, fórmulas, imágenes e hipervínculos. A través de actividades prácticas, los estudiantes desarrollarán la capacidad de elaborar informes precisos y complejos, utilizando funciones avanzadas de Excel como DESREF, BUSCARV y tablas dinámicas, promoviendo una adecuada presentación y análisis de datos.

Explora las pestañas y descubre el contenido que se encuentra en cada una de ellas. Encontrarás recursos, materiales, vídeos con información fundamental para tu formación.

Ingresa a los vídeos. Aquí
Información Actividades Textos Recursos
Información

CONTENIDOS:

  • Repaso general de interfaz de usuario
  • Tipos de datos
  • Hojas, celdas, referencias
  • Administración de nombres de rangos y celdas
  • Formato condicional, tabla de datos, totales, cambios de campo, fórmulas, imágenes e hipervínculos


Actividades

Actividad Agosto 17 y Agosto 24
Para la clase del 17 desarrollar a elección un caso y para la clase virtual del 24 de agosto seleccionar otro caso y desarrollarlo en su totalidad

Estudio de Caso 1: Análisis de Ventas con Formato Condicional y Fórmulas
Contexto: Una empresa de comercio electrónico desea analizar sus datos de ventas mensuales para identificar las categorías de productos que están teniendo un rendimiento excepcional o que requieren atención. Tienes un archivo de Excel que contiene las siguientes columnas: Categoría, Producto, Ventas Mes 1, Ventas Mes 2, Ventas Mes 3. Se te ha solicitado que resaltes automáticamente las categorías que han tenido un incremento en ventas del 20% o más en el tercer mes en comparación con el primer mes, y que identifiques aquellas que han tenido una disminución en ventas.
Actividad:
  1. Formato Condicional:
    • Aplica un formato condicional que resalte en verde las celdas en la columna Ventas Mes 3 donde el valor sea al menos un 20% mayor que en Ventas Mes 1.
    • Aplica otro formato condicional que resalte en rojo las celdas donde Ventas Mes 3 sea menor que Ventas Mes 1.
  2. Fórmulas:
    • Usa una fórmula para calcular el porcentaje de cambio en las ventas desde el primer mes hasta el tercer mes y coloca los resultados en una nueva columna Cambio (%).
    • Asegúrate de que la fórmula sea correcta y se pueda arrastrar hacia abajo para calcular el cambio en todas las filas.
  3. Presentación de Resultados:
    • Resalta con formato condicional las filas completas de aquellas categorías donde el cambio porcentual sea superior al 20% (en verde) o donde haya habido una disminución (en rojo).
Objetivo de Aprendizaje:
  • Dominar el uso de formato condicional para resaltar datos clave.
  • Aprender a utilizar fórmulas para calcular y analizar cambios porcentuales en Excel.
Estudio de Caso 2: Gestión de Inventarios con Tablas de Datos y Totales
Contexto: Un minorista de tecnología necesita organizar y analizar su inventario de productos. La hoja de cálculo contiene columnas para Producto, Categoría, Cantidad en Inventario, Costo Unitario, y Valor Total de Inventario. Necesitas organizar estos datos en una tabla de datos para facilitar el análisis y calcular el valor total de inventario por categoría.
Actividad:
  1. Creación de la Tabla de Datos:
    • Convierte el rango de datos en una tabla de datos para que sea más fácil de manipular y analizar.
    • Asigna un nombre a la tabla para facilitar su referencia en fórmulas.
  2. Totales:
    • Utiliza la fila de totales de la tabla para calcular automáticamente el Valor Total de Inventario de cada categoría.
    • Añade una nueva columna que calcule el Valor Total de Inventario por producto (Cantidad en Inventario * Costo Unitario) utilizando fórmulas.
  3. Filtrado y Ordenación:
    • Filtra la tabla para mostrar solo los productos de una categoría específica (por ejemplo, "Laptops").
    • Ordena la tabla de mayor a menor según el Valor Total de Inventario.
  4. Resumen de Categorías:
    • Utiliza una fórmula SUMIF o Subtotal para sumar el valor total del inventario por categoría en una sección separada de la hoja de cálculo.
Objetivo de Aprendizaje:
  • Aprender a convertir datos en tablas de Excel y utilizar sus funcionalidades para organizar y analizar información.
  • Practicar la creación y uso de totales y sumas condicionales para obtener información agregada.
Estudio de Caso 3: Creación de un Dashboard de Seguimiento con Cambios de Campo y Gráficos
Contexto: Un gerente de proyectos desea realizar un seguimiento del progreso de las tareas de su equipo. Los datos incluyen las columnas Tarea, Responsable, Estado, Fecha de Inicio, Fecha de Finalización, y Porcentaje de Compleción. Se necesita un dashboard que muestre un resumen visual de las tareas en progreso, completadas y pendientes, junto con un gráfico que muestre el porcentaje de tareas completadas por cada miembro del equipo.
Actividad:
  1. Creación de la Tabla de Datos:
    • Organiza los datos de las tareas en una tabla y asegúrate de que cada columna esté correctamente etiquetada.
  2. Filtrado y Ordenación:
    • Utiliza filtros para mostrar solo las tareas que están "En Progreso" y "Pendientes".
    • Ordena las tareas por fecha de finalización para identificar las que están más cerca de su plazo.
  3. Creación del Dashboard:
    • Crea un gráfico de barras que muestre el número de tareas completadas por cada miembro del equipo.
    • Añade un gráfico circular que represente el porcentaje de tareas completadas frente a las tareas pendientes.
  4. Cambios de Campo:
    • Permite que el gráfico cambie dinámicamente para mostrar datos específicos según se seleccionen diferentes responsables o estados de tarea mediante un slicer.
Objetivo de Aprendizaje:
  • Aprender a crear y utilizar gráficos en Excel para visualizar datos.
  • Dominar el uso de filtros y slicers para permitir cambios de campo y análisis dinámico.
Estudio de Caso 4: Informe Financiero con Imágenes e Hipervínculos
Contexto: Una compañía está preparando un informe financiero anual en Excel que se presentará a los inversores. El informe debe incluir datos de ingresos, gastos, y beneficios, junto con gráficos que resuman la información. Además, cada sección del informe debe estar enlazada con secciones adicionales o documentos externos, y debe incluir el logotipo de la empresa en la parte superior de la hoja.
Actividad:
  1. Inserción de Imágenes:
    • Inserta el logotipo de la empresa en la parte superior del informe financiero.
    • Asegúrate de que la imagen esté bien posicionada y no interfiera con el contenido de la hoja.
  2. Creación de Hipervínculos:
    • Añade hipervínculos a diferentes partes del informe que lleven a secciones adicionales en otras hojas dentro del libro de Excel.
    • Crea hipervínculos a documentos externos relevantes, como el informe detallado de gastos o una presentación de PowerPoint.
  3. Informe de Resumen Financiero:
    • Organiza los datos de ingresos, gastos y beneficios en una tabla y utiliza fórmulas para calcular los totales y porcentajes.
    • Crea gráficos que resuman visualmente la información financiera, como un gráfico de barras que muestre el crecimiento de los ingresos y un gráfico circular que detalle la distribución de gastos.
Objetivo de Aprendizaje:
  • Practicar la inserción de imágenes en Excel para mejorar la presentación de los datos.
  • Aprender a crear y utilizar hipervínculos para mejorar la navegación dentro de hojas de cálculo complejas y conectar con recursos externos.

Actividad Agosto 10
Desarrollar la función importrange en Google Sheets donde se importen datos de una hoja de cálculo a otra y luego publicar en un sitio web
Seleccionar 10 funciones de las siguientes y aplicarlas a una hoja de cálculo ya diseñada:
  • CONCATENAR (O CONCAT): Combina varios textos en una sola celda.
  • IZQUIERDA, DERECHA, EXTRAE: Extrae caracteres específicos de una cadena de texto, ya sea desde el inicio, el final o desde una posición específica.
  • REDONDEAR, REDONDEAR.MAS, REDONDEAR.MENOS: Redondea un número a un número específico de dígitos.
  • COINCIDIR: Devuelve la posición relativa de un elemento en una matriz.
  • INDICE: Devuelve el valor de una celda en una tabla o rango de celdas, basándose en un número de fila y columna.
  • SUBTOTALES: Realiza cálculos como suma, promedio, cuenta, etc., en un rango con la posibilidad de excluir filas ocultas.
  • HIPERVINCULO: Crea un enlace a otro documento, una ubicación en el mismo documento o una página web.
  • MIN, MAX: Encuentra el valor mínimo o máximo en un rango de celdas.
  • DIA, MES, AÑO: Extrae el día, mes o año de una fecha.
  • DIA.LAB, DIA.LAB.INTL: Calcula la fecha de un día laborable en el futuro o pasado, excluyendo fines de semana y feriados opcionales.
  • TEXTO: Convierte un valor numérico en texto con un formato específico.
  • ESNUMERO, ESERROR, ESBLANCO: Verifica si una celda contiene un número, un error o está en blanco.
  • HALLAR, ENCONTRAR: Encuentra la posición de una cadena dentro de otra.
  • RED: Redondea un número al entero más cercano.
  • PRODUCTO: Multiplica todos los números dados como argumento.
  • FRECUENCIA: Calcula la frecuencia con que ocurren valores dentro de un rango de valores.
  • TRANSPONER: Convierte un rango de celdas vertical en horizontal, o viceversa.
  • FECHA: Devuelve el número de serie que representa una fecha específica.
  • HORA, MINUTO, SEGUNDO: Extrae la hora, el minuto o el segundo de una hora específica.
  • AHORA, HOY: Devuelve la fecha y hora actuales, o solo la fecha actual.
  • INDIRECTO: Devuelve la referencia especificada por una cadena de texto.
  • CONVERTIR: Convierte un número de un sistema de medida a otro (ej., de metros a pies, de Celsius a Fahrenheit).
  • BUSCARH: Busca un valor en la fila superior de una tabla o rango y devuelve un valor en la misma columna desde una fila que especifiques.

    • Las consultas serán discutidas en clase y allí se afianzarán los conocimientos.

    Cualquier duda, comunicarla con el profesor.
    Recuerden explorar las pestañas donde se encontrará el material y los recursos necesarios para desarrollar la actividad
    Textos

    1. Repaso general de interfaz de usuario
    La interfaz de usuario en las hojas de cálculo es fundamental para una navegación y uso eficientes del software. En aplicaciones como Microsoft Excel o Google Sheets, la interfaz está diseñada para ser intuitiva, con una barra de herramientas en la parte superior que ofrece accesos rápidos a las funciones más utilizadas, como guardar, deshacer, rehacer y copiar. La interfaz también incluye menús desplegables que permiten acceder a opciones avanzadas, como fórmulas, gráficos y herramientas de datos.

    La cinta de opciones es una característica clave en Excel, donde las herramientas están agrupadas por pestañas temáticas (Inicio, Insertar, Diseño de Página, Fórmulas, Datos, Revisión y Vista). Cada pestaña contiene comandos agrupados en secciones lógicas. Por ejemplo, la pestaña "Inicio" incluye comandos básicos de formato de texto, mientras que la pestaña "Fórmulas" proporciona acceso a una amplia gama de funciones matemáticas y estadísticas.

    El área de trabajo principal de una hoja de cálculo está compuesta por una cuadrícula de celdas, organizadas en filas y columnas. Cada celda puede contener datos numéricos, texto, o fórmulas. La barra de fórmulas situada justo encima de la cuadrícula muestra el contenido de la celda activa y permite editarlo directamente. La barra de estado en la parte inferior de la ventana proporciona información adicional, como el resultado de cálculos rápidos y el estado de la conexión a la red.

    Un aspecto esencial de la interfaz de usuario es la personalización. Los usuarios pueden ajustar el tamaño y la posición de las ventanas y paneles, así como modificar la apariencia de las celdas y hojas mediante opciones de formato. La capacidad de personalizar la interfaz de acuerdo a las necesidades individuales mejora la eficiencia y facilita la gestión de datos complejos.

    2. Tipos de datos
    En las hojas de cálculo, los tipos de datos son cruciales para la correcta manipulación y análisis de la información. Los tipos de datos más comunes incluyen los números, el texto, las fechas y las horas. Los números se utilizan para cálculos matemáticos y pueden incluir enteros, decimales y porcentajes. Es importante ingresar los números correctamente para evitar errores en las fórmulas y funciones que los utilizan.

    El texto se emplea para etiquetar y describir datos. Puede incluir letras, números y caracteres especiales. En hojas de cálculo, el texto se utiliza a menudo para nombres de columnas, encabezados y comentarios. Es fundamental mantener un formato coherente para asegurar la claridad y precisión en la presentación de los datos.

    Las fechas y las horas son tipos de datos que permiten realizar cálculos temporales, como la diferencia entre dos fechas o la suma de horas trabajadas. En Excel, las fechas se manejan como números de serie, donde el número 1 representa el 1 de enero de 1900. Las funciones específicas, como =HOY() o =AHORA(), permiten insertar la fecha y hora actual en una celda.

    Las fórmulas y las funciones son otra categoría importante de datos. Las fórmulas son expresiones matemáticas que realizan cálculos en función de los datos de las celdas. Las funciones son fórmulas predefinidas que realizan operaciones específicas, como SUMA, PROMEDIO o BUSCARV. La correcta comprensión y uso de los tipos de datos es esencial para aprovechar al máximo las capacidades de las hojas de cálculo.

    3. Hojas, celdas, referencias
    Las hojas de cálculo están organizadas en un libro, que puede contener múltiples hojas. Cada hoja está formada por una cuadrícula de celdas, que se identifican por su posición única mediante una combinación de letras (columnas) y números (filas). Por ejemplo, la celda en la intersección de la columna B y la fila 3 se denomina B3. Esta estructura permite una organización clara y sistemática de los datos.

    Las celdas son las unidades básicas de una hoja de cálculo y pueden contener diferentes tipos de datos, como números, texto, fechas, y fórmulas. Las celdas pueden ser formateadas para mejorar la presentación de los datos, cambiando aspectos como el tipo de letra, el color de fondo y los bordes. Además, las celdas pueden combinarse para crear encabezados más grandes o destacar secciones específicas de la hoja.

    Las referencias a celdas son esenciales para la creación de fórmulas y funciones. Hay dos tipos principales de referencias: relativas y absolutas. Una referencia relativa, como A1, cambia cuando se copia a otra celda, adaptándose a la nueva ubicación. Por ejemplo, si se copia una fórmula de la celda B1 a C1, la referencia A1 en la fórmula cambiará automáticamente a B1. En cambio, una referencia absoluta, como $A$1, permanece constante sin importar dónde se copie la fórmula.

    La correcta utilización de hojas, celdas y referencias es fundamental para gestionar y analizar datos eficientemente. Las referencias pueden ser simples (a una única celda) o complejas (a rangos de celdas o incluso a celdas en diferentes hojas). La habilidad para manejar estas referencias es crucial para construir modelos de datos precisos y útiles.

    4. Administración de nombres de rangos y celdas
    La administración de nombres de rangos y celdas es una herramienta poderosa en las hojas de cálculo para mejorar la claridad y gestión de datos. Asignar nombres a rangos específicos de celdas permite referirse a esos rangos de manera más sencilla y comprensible en fórmulas y funciones. Por ejemplo, en lugar de referirse a un rango como A1
    , se puede nombrar ese rango como "VentasQ1" y usar ese nombre en las fórmulas.

    Para asignar un nombre a un rango en Excel, se selecciona el rango deseado y luego se utiliza el cuadro de nombres ubicado a la izquierda de la barra de fórmulas, donde se puede ingresar el nombre elegido. También se puede hacer esto a través del Administrador de Nombres, que ofrece opciones adicionales para definir, editar y eliminar nombres de rangos.

    Los nombres de rangos facilitan la comprensión de las fórmulas, especialmente en hojas de cálculo complejas. Por ejemplo, una fórmula como =SUMA(VentasQ1) es mucho más clara que =SUMA(A1:A10). Además, los nombres de rangos son dinámicos y pueden actualizarse automáticamente si se cambian los datos dentro del rango, asegurando que las fórmulas siempre se refieran a los datos correctos.

    El uso de nombres de rangos también permite la creación de tablas dinámicas y gráficos más eficientes. Los nombres pueden aplicarse a celdas individuales, rangos de celdas, y constantes, proporcionando una gran flexibilidad en la organización y análisis de datos. Esta práctica no solo mejora la legibilidad de las hojas de cálculo, sino que también reduce los errores al evitar referencias incorrectas o confusas.

    5. Formato condicional, tabla de datos, totales, cambios de campo, fórmulas, imágenes e hipervínculos
    El formato condicional es una característica que permite aplicar formatos específicos a las celdas que cumplen con ciertos criterios. Por ejemplo, se puede usar formato condicional para resaltar todas las celdas que contienen valores superiores a un umbral definido. Esto es útil para identificar rápidamente tendencias y patrones en los datos. En Excel, se accede a esta opción a través de la pestaña "Inicio" y el grupo "Estilos". Los usuarios pueden definir reglas personalizadas utilizando fórmulas para aplicarlas a sus datos.

    Las tablas de datos son una forma organizada de gestionar grandes conjuntos de datos. Una tabla en Excel no solo estructura los datos en filas y columnas, sino que también facilita el análisis con características como filtros, ordenamiento y filas de totales. Al convertir un rango de celdas en una tabla, se activa automáticamente el formato de tabla, lo que incluye opciones de diseño para mejorar la presentación visual de los datos. Las filas de totales son particularmente útiles para calcular rápidamente sumas, promedios, y otros estadísticos.

    Los cambios de campo en tablas dinámicas permiten reorganizar los datos de manera interactiva, facilitando el análisis desde diferentes perspectivas. Las tablas dinámicas son herramientas poderosas para resumir y explorar grandes volúmenes de datos. Los usuarios pueden arrastrar y soltar campos en diferentes áreas de la tabla dinámica para generar resúmenes instantáneos y visualizar la información de formas diversas.

    Las fórmulas son esenciales en las hojas de cálculo para realizar cálculos automáticos. Las fórmulas básicas incluyen SUMA, PROMEDIO, MIN y MAX, que se utilizan para agregar, promediar y encontrar valores extremos en los datos. Las fórmulas más avanzadas, como BUSCARV y BUSCARH, permiten buscar datos en tablas, mientras que las fórmulas lógicas como SI permiten realizar operaciones condicionales.

    Las imágenes e hipervínculos mejoran la interactividad y la presentación de las hojas de cálculo. Las imágenes pueden insertarse para ilustrar puntos clave o añadir elementos visuales atractivos. Los hipervínculos permiten a los usuarios navegar rápidamente a otras hojas, documentos, o páginas web relevantes, mejorando la funcionalidad y usabilidad de la hoja de cálculo. La correcta utilización de estas herramientas enriquece la experiencia del usuario y facilita una mejor comprensión de los datos presentados.
    Recursos
    Ingrese al Google Drive y descargue los recursos de apoyo. Aquí
    VÍDEOS
    ventas@sysdatacolombia.com
    Created by SysdataColombia. 2024
    Regreso al contenido