GFPI-F-135 GUÍA #2 Manejo Intermedio MS Excel v2 RAP2 PDF

Preview

Summary

manejo y funcionamiento de excel intermedio con enfasis en el mejoramiento de formulas...

Description

LÍNEA TECNOLÓGICA DEL PROGRAMA CLIENTE FORMATO GUÍA DE APRENDIZAJE RED TECNOLÓGICA TECNOLOGÍAS DE GESTIÓN ADMINISTRATIVA Y SERVICIOS FINANCIEROS Denominación del Programa de Formación: MANEJO INTERMEDIO DE LA HERRAMIENTA DE HOJAS DE CALCULO EXCEL Código del Programa de Formación: 13410564 Competencia: 220501046 - Utilizar herramientas informáticas de acuerdo con las necesidades de manejo de información ELEMENTO(S) DE LA COMPETENCIA Emplear herramientasinformáticas Resultados de Aprendizaje Alcanzar: RA 2: APLICAR LAS FUNCIONES DE UNA HOJA DE CÁLCULO SEGÚN LAS CARACTERÍSTICAS DE LAS OPERACIONES A DESARROLLAR.

Duración de la Guía: 60 horas (Trabajo directo: 40 horas – Trabajo independiente: 12 horas

2. PRESENTACIÓN El déficit de la formación del recurso humano, es una de las principales barreras identificadas en el estudio para el desarrollo de un sector competitivo en los países en desarrollo. A este respecto, en muchas naciones existe un déficit importante de ingenieros de software y especialistas de áreas afines a las Tecnologías de la Información y la Comunicación (TIC), así como insuficiencias en materia de capacidades de emprendimiento e innovación, competencias de gerencia y gestión comercial. El problema principal en estos países, lo que aplica también para Colombia, es que la demanda potencial de la industria supera la oferta local de mano de obra calificada, a un ritmo que puede estancar su crecimiento, especialmente hacia los mercados externos.

Colombia es el quinto mercado en la región de América Latina, contribuyendo con el cinco por ciento (1.600 millones USD) del total del mercado de servicios de TI de 32,000 millones USD y se pronostica que el mercado crecerá al 12.1% para alcanzar 2,600 millones USD para el 2016. Proexport, el cuerpo de inversión y promoción a las exportaciones en Colombia, reporta que el Mercado de TI, comprendido de hardware, productos y servicios de software, es de 6.800 Pág. 1

GFPI-F-135 V01

millones USD, de los cuales, el 30% son servicios de TI llevándonos a 2.052 millones USD. Mientras que el total de la industria de TI ha crecido 12% anualmente los últimos 5 años, la industria de servicios de TI ha crecido al 23% y se pronostica un déficit incremental de empleos de 166,956 (agresivo), 103,250 (moderado) o 55,217 (conservador) para 2020. Dadas las proyecciones, después de 2016 el problema de la escasez de talento de TI crece exponencialmente y no puede resolverse fácilmente. Por lo tanto el Gobierno de Colombia debe tomar serias medidas para superar la disponibilidad de talento y ver la efectividad de las medidas. Lo anterior, demuestra la necesidad actual y futura existente de formación de personal en tecnologías relacionadas con el sector de las TIC, las cuales han sido identificadas hacia desarrollos a la medida, programación, algoritmos, bases de datos, redes (Arquitectura ClienteServidor), integración aplicada de las áreas, testing, la adaptación del software a mercados y sectores locales, tecnologías de seguridad digital o ciberseguridad, tecnologías de producción, gestión y manipulación de contenidos digitales sobre redes y plataformas digitales, fijas, móviles y convergentes, big data, SCRUM, tecnologías de seguridad, generación, gestión, producción y explotación de software en la nube, así como reforzar conocimiento en áreas de administración de proyectos, habilidades analíticas y fortalecer las habilidades blandas en áreas como la comunicación, trabajo en equipo, interacción con clientes e Inglés de Negocios. ESTRATEGIA METODOLÓGICA: Centrada en la construcción de autonomía para garantizar la calidad de los procesos formativos en el marco de la formación por competencias, el aprendizaje por proyectos y el uso de técnicas didácticas activas que estimulan el pensamiento para la resolución de problemas simulados y reales; soportadas en la utilización de las tecnologías de la información y la comunicación, integradas, en ambientes abiertos y pluritecnológicos, que en todo caso recrean el contexto productivo y vinculan al aprendiz con la realidad cotidiana y el desarrollo de las competencias. Igualmente, debe estimular de manera permanente la autocrítica y la reflexión del aprendiz sobre el que hacer y los resultados de aprendizaje que logra a través de la vinculación activa de las cuatro fuentes de información para la construcción de conocimiento: El instructor - Tutor El entorno Las TIC El trabajo colaborativo.

3. CONOCIMIENTOS 3.1. CONOCIMIENTOS DE CONCEPTOS Y PRINCIPIOS    

Formato condicional Aplicar formato condicional Eliminar el formato condicional Utilizar referencias de celdas en las condiciones Pág. 2

GFPI-F-135 V01

                                 

Utilizar fórmulas como condiciones Modificar, copiar y quitar Funciones lógicas SI Y O SI.ERROR Anidadas Otras funciones Sumar.Si Contar.Si Concatenar Aleatorio.Entre Jerarquia Sumaproducto Factorial Promedio.si Búsqueda y referencia Buscar Buscarv Creación de una base de datos Creación de base de datos en excel. Importación/exportación de datos. Ordenación de registros. Autofiltros. Filtros avanzados. Tablas dinámicas Herramientas Cálculos Gráficos dinámicos Macros. Grabadora de macros. Botones de macro. Controles de formulario.

Pág. 3

GFPI-F-135 V01

3.2. CONOCIMIENTOS DE PROCESO  Elaborar informe utilizando tablas y gráficos dinámicos en la hoja de cálculo.  Utilizar formato condicional  Aplicar filtros, consultas y validación de datos.  Crear base de datos  Realizar operaciones sobre la base de datos  Aplicar macros para automatizar procesos  Utilizar herramientas de análisis de datos  Realizar auditoria de las formulas  Identificar acciones de mejoras a realizar  Implementar las acciones de mejora identificadas

3.3 Actividades de contextualización e identificación de conocimientos necesarios para el aprendizaje.  

Contextualización de generalidades en el uso de herramientas ofimáticas para la gestión de bases de datos Resolver problemas reales de su día a día con el uso de funciones estadísticas.

3.4 Actividades de apropiación.  Interpreta la manera práctica para procesar información en hojas de cálculo, aplicando formulas y funciones  Utilizar adecuadamente las Funciones de suma, resta, multiplicación y porcentaje en la elaboración de documentos para manejo de información numérica.  Conocer y usar las funciones básicas que se aplican en hojas de cálculo para la elaboración de nómina, facturas, presupuestos, listados, planillas y otros documentos.  Filtrar información desde listados ya elaborados.  Graficar y analizar información usando la utilidad de gráficos de Excel. Manejar libros con varias hojas, haciendo uso de referencias relativas y  absolutas. 4. CRITERIOS DE EVALUACIÓN 



Explica las funcionalidades de los elementos de tablas dinámicas, base de datos y macros de la hoja de cálculo de acuerdo con la disposición de la interfaz gráfica del programa. Elabora un informe utilizando las funciones de tablas y gráficos dinámicos de la hoja de cálculo, de acuerdo con las necesidades del mismo.

Pág. 4

GFPI-F-135 V01

         

Realiza análisis, filtros, consultas y validación de datos utilizando las funcionalidades de la hoja de cálculo. Aplica las potencialidades del formato condicional, de acuerdo con el requerimiento dado. Diseña base de datos de acuerdo con el requerimiento dado. Aplica funciones lógicas y otras funciones en las consultas y búsqueda de acuerdo con las necesidades de la información solicitada. Realiza operaciones en la base de datos de acuerdo con las necesidades y/o requerimientos solicitados. Crea escenarios de acuerdo con los requerimientos del trabajo. Crea macros para automatizar procesos según las necesidades. Utiliza tablas y gráficos dinámicos de acuerdo con las necesidades del trabajo. Verifica si los datos en otras aplicaciones para exportación según las necesidades de las actividades a realizar. implementa las acciones de mejora de acuerdo con las acciones de mejora identificadas.

5. PERFIL TÉCNICO DEL INSTRUCTOR Requisitos Académicos Título de tecnólogo o cuatro (4) años de estudios universitarios, Relacionados con la especialidad objeto de formación. Competencias mínimas Buen nivel de lectura y escucha del idioma inglés. Formular, ejecutar y evaluar proyectos. Trabajo en equipo. Manejo de tecnologías de la información y la comunicación. Establecer procesos comunicativos asertivos Experiencia Laboral: Veinticuatro (24) meses de experiencia: de los cuales dieciocho (18) meses, Estarán relacionados con el ejercicio de la profesión u oficio objeto de la Formación profesional y seis (6) meses en labores de docencia. Recursos de apoyo Tecnológicos - Celular - Plataforma meet - Repositorio drive de Google - Whatsap - Correo electronico Ambientes de aprendizaje - Plataforma SOFIA PLUS SENA - Plataforma territorium

Pág. 5

GFPI-F-135 V01

Material audiovisual - Tutoriales - Talleres - Diapositivas 5. CONTROL DEL DOCUMENTO NOMBRE

CARGO

DEPENDENCIA / RED

FECHA

Responsable del diseño

FABIAN JOSE FADUL GUTIERREZ

EQUIPO DE DISEÑO CURRICULAR

CENTRO NACIONAL COLOMBO ALEMAN. REGIONAL ATLÁNTICO

18/06/2015

Responsable del diseño

CLAUDIA MARCELA PORRAS ORTIZ

APROBAR ANALISIS

CENTRO DE SERVICIOS Y GESTION EMPRESARIAL. REGIONAL ANTIOQUIA

19/06/2015

Responsable del diseño

CLAUDIA MARCELA PORRAS ORTIZ

Aprobación

ACTIVAR PROGRAMA

CLAUDIA MARCELA PORRAS ORTIZ

CENTRO DE SERVICIOS Y GESTION EMPRESARIAL. REGIONAL ANTIOQUIA

08/07/2015

CENTRO DE SERVICIOS Y GESTION EMPRESARIAL.

08/07/2015

6. GLOSARIO DE TÉRMINOS Excel: Aplicación distribuida por Microsoft Office para hojas de cálculo. Entorno: Lo que rodea a alguien o algo. Datos: Información amplia o concreta que permite una deducción o conocimiento exacto. Formula: Modelo establecido para expresar, realizar o resolver algo Técnica: Conjunto de procedimientos o recursos de los que se sirve una ciencia, un arte o un oficio

Pág. 6

GFPI-F-135 V01

7. REFERENTES BILBIOGRÁFICOS MANUAL AVANZADO MICROSOFT EXCEL 2016, AUTOR: FRANCISCO CHARTE, I.S.B.N: 978-84-415-3806-1, Editorial: ANAYA MULTIMEDIA. Curso de Excel COMPLETO y GRATIS: https://www.youtube.com/playlist?list=PLLniqWgyb4HHfkyWNgMWSvTq6RlUGusgN Tutoriales de Excel Basicos y Avanzados: https://www.youtube.com/playlist?list=PLLniqWgyb4HEbaXnpcNlJy36f33VmtROF

Pág. 7

GFPI-F-135 V01

FORMULAS EN HOJAS DE CALCULO MS EXCEL Formulación Básica Son operaciones que se utilizan dentro de la hoja aplicando los mismos valores que tiene ella. Siempre se inician con el signo igual (=) Pasos para aplicarla: •

Seleccionar la celda donde se colocará la formula

•

Digitar la operación a realizar iniciando por el signo =.o +

•

Presionar Enter para finalizar

•

Ejemplo: =2+15

Formulación por coordenadas Nos permite realizar cualquier tipo de operación dentro de la hoja utilizando los nombres de las celdas Ej.: A1+B1, es decir mencionando primero a la columna y luego la fila.

Se utiliza de la misma manera que la formulación básica a diferencia que no se usan valores, sino los nombres de las celdas.

Para Tener en Cuenta •

Siempre que se aplique la formulación se debe anteponer el signo igual (=)

•

Realizar múltiples operaciones.

•

Para modificar el contenido de una celda no hay necesidad de modificar todo su contenido, podemos utilizar la tecla F2.

Pág. 8

GFPI-F-135 V01

Referencias de Celdas Las referencias de celdas pueden indicar celdas independientes o rangos de celdas en columnas y filas. Referencias de

Hacen referencia a los valores de

A10

la celda de la columna A y la fila 10

A10;A20

la celda A10 y la celda A20

A10:A20

el rango de celdas de la columna A desde la fila 10 a la 20

B15:E15

el rango de celdas de la fila 15 desde la columna B a la E

A10:E20

el rango de celdas desde la columna A a la E y desde la fila 10 a la

Tipos de Referencias Referencia Relativa: Las referencias de celdas relativas de una fórmula cambian automáticamente cuando la fórmula se copia en toda una columna o en toda una fila.

Referencia Absoluta: Una referencia de celdas absoluta es fija. Las referencias absolutas no cambian al copiar una fórmula de una celda en otra. Las referencias absolutas tienen signos de dólar ($) como esta: ($A$1)

Las referencias absolutas se activan anteponiendo un signo ($) antes de la letra de la columna ($A1) o antes del numero de la fila (A$1) o también utilizando el bloqueo de la columna y fila simultáneamente ($A$1). ($A1) Columna Bloqueada. (A$1) Fila Bloqueada. ($A$1) Columna y Fila Bloqueada.

Referencia Mixta: Una referencia de celdas mixta tiene bien una columna absoluta y una fila relativa, o bien una fila absoluta y una columna relativa. Por ejemplo, $A1 es una referencia absoluta a la columna A y una referencia relativa a la fila 1. Cuando una referencia mixta se copia de una fila en otra, la referencia absoluta permanece igual pero la referencia relativa cambia. Pág. 9

GFPI-F-135 V01

Insertar Gráficos Para Insertar Gráficos, seleccione el contenido del conjunto de la columna descriptica de texto y la columna que sea numérica, posteriormente diríjase a la pestaña o Cinta Insertar, seleccione la opción del gráfico que desea posicionar.

Insertar Texto El Texto a insertar tiene como propósito escoger opciones como Cuadros de Texto, WordArt, Encabezados de Página entre otros. Para hacer uso de este procedimiento, diríjase a la Cinta Insertar, seleccione el Panel Texto, y escoja la opción de su preferencia.

Insertar Símbolos

El Símbolo a insertar tiene como destino, escoger opciones de imágenes que permitan describir Ecuaciones como: Área de Circulo, Teorema binomial, expansión de una suma, Series de Fourier, entre otras.

Para hacer uso de este procedimiento, diríjase a la Cinta Insertar, seleccione el Panel Simbolos, y escoja la opción la ecuación de su preferencia

Pág. 10

GFPI-F-135 V01

FUNCIONES EN HOJAS DE CALCULO MS EXCEL Las funciones son operaciones de cálculo automáticos que vienen incluidas en el aplicativo o software para el uso por parte del usuario, se diferencian de las fórmulas por cuanto estas últimas son operaciones realizadas de mecánicamente por parte del usuario del software

FUNCIONES BASICAS EN HOJAS DE CALCULO Son un conjunto de operaciones automatizadas en el aplicativo o software para facilitar el desempeño de las tareas en el usuario que las usa, las mas comunes son Auto Suma, Promedio, Max (# Máximo), Min (# Mínimo) (ver ejercicio propuesto).

Pág. 12

GFPI-F-135 V01

FUNCIONES LOGICAS

Devuelve un valor si la condición especificada es VERDADERO y otro valor si dicho argumento es FALSO. Utilice SI para realizar pruebas condicionales en valores y fórmulas. Prueba_ lógica es cualquier valor o expresión que puede evaluarse como VERDADERO o FALSO.

Función SI La función SI es una de las funciones más populares de Excel y le permite realizar comparaciones lógicas entre un valor y un resultado que espera. En su forma más sencilla, la función SI dice: SI(Algo es Verdadero, hacer algo; de lo contrario hacer algo diferente) 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 rueba lógica para la función SI, también podemos especificar valores a devolver de acuerdo al resultado de la función.

Prueba_lógica (obligatorio): Expresión lógica que será evaluada para conocer si el resultado es VERDADERO o FALSO. Valor_si_verdadero (opcional): 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. Pág. 13 GFPI-F-135 V01

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 Función en todas las celdas de la columna C.

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")

Pág. 14

GFPI-F-135 V01

FUNCION LOGICA SI ANIDADA La función SI le permite realizar una comparación lógica entre un valor y el resultado que espera probando una condición y devolviendo un resultado si es Verdadero o Falso.

=SI(Algo es Verdadero, hacer algo; de lo contrario hacer algo diferente) Por esto, una instrucción SI puede tener dos resultados. El primer resultado es si la comparación es Verdadera y el segundo si la comparación es Falsa.

Las instrucciones SI son extremadamente sólidas y forman la base de muchos modelos de hoja de cálculo, pero también son la causa principal de muchos de los problemas en las hojas de cálculo. Lo ideal es que una instrucción SI se aplique a condiciones mínimas, como Hombre/Mujer, Sí/No/Quizás, por nombrar algunos ejemplos, pero a veces es posible que deba evaluar escenarios más complejos que requieren el anidamiento* de más de 3 funciones SI juntas. Sintaxis: SI(prueba_lógica; valor_si_verdadero; [valor_si_falso])

Ejemplo: =SI(A2>B2,"Presupuesto excedido";"Correcto") =SI(A2=B2, B4-A4, "")

Pág. 15

GFPI-F-135 V01

Ejemplo: A continuación, encontrará un ejemplo de una instrucción SI anidada relativamente estándar para convertir resultados de exámenes en su equivalente de la calificación mediante letras.

=SI(D2>89,"A",SI(D2>79,"B",SI(D2>69,"C",SI(D2>59,"D","F"))))

Esta instrucción SI anidada compleja sigue una lógica sencilla:



Si el resultado (en la celda D2) es mayor que 89, la calificación es A



Si el resultado es mayor que 79, la calificación es B



Si el resultado es mayor que 69, la calificación es C



Si el resultado es mayor que 59, la calificación es D



En caso contrario, la calificación es F

Este ejemplo concreto es relativamente seguro porque no es probable que la correlación entre resultados de exámenes y calificaciones mediante letras cambie, por lo que no requiere mucho mantenimiento. Pero, ...