Curso Taller de Excel Avanzado para Contadores

1. Curso Taller de Excel Avanzadopara Contadores Descargar el material del curso: http://najar2009.pbworks.com/02 DICIEMBRE DE 2009

2. Objetivo del curso • Que el participante obtenga conocimientos avanzados sobre las funciones y herramientas de Excel que le permitan realizar su trabajo más rápido y con menos riesgo de error.

3. Temario

4. Metodología • Marco conceptual • Explicación teórica • Conocimiento técnico • Práctica • Reforzar el conocimiento • Ejercicio

5. Introducción • En la actividad del contador público su aplicación incide en: • Análisis financiero • Auditoría • Impuestos • Control internos • Costos • Entre muchos otros ....

6. Introducción Antecedentes y evolución en México • 1990: MS-Works (en plataforma MS-DOS)

7. Introducción Antecedentes y evolución en México • LOTUS 1-2-3 para MS-DOS era el más usado…

8. Introducción Antecedentes y evolución en México • 1994: Gana terreno la versión de Excel 2.0 y luego la 4.0 (en plataforma Windows 3.1)

9. Introducción Antecedentes y evolución en México • Evolucionando hasta las versiones más usadas hoy: Excel 2003 y 2007 (para Windows XP y Vista)

10. Introducción ¿Qué no es una hoja de cálculo? • No es un sistema de procesamiento de transacciones ni base de datos relacional. Para estos casos se recomienda usar programas especializados, de acuerdo a la necesidad del usuario.

11. Temario

12. FUNCIONES AVANZADAS

13. 1. Funciones avanzadas • ¿QUÉ ES UNA FUNCIÓN? ES UN PROGRAMA DENTRO DE EXCEL QUE: • Obtiene argumentos. • Con ellos, realiza operaciones ó acciones. • Devuelve un resultado. fx Argumento 1 Argumento 2 Resultado ……. Argumento n

14. 1. Funciones avanzadas • ¿CÓMO SE REDACTA UNA FUNCIÓN? =función(argumento1,argumento2, argumento3…) • Los argumentos siempre van separados por comas. • Cada función tiene diferente forma de • acomodar sus argumentos. • Los argumentos pueden ser: • Tipo Ejemplo Descripción • Valores 1200, 14767, 1 Cualquier número • Textos “LOCO” Cualquier texto; siempre va entre comillas • Celdas A1 Cualquier celda • Rangos A1:Z8 celda inicial : celda final • Criterios “>=” Un operador; siempre va entre comillas • (ver operadores de comparación sig. dispositiva)

15. 1. Funciones avanzadas • OPERADORES DE COMPARACIÓN • Para ser usados dentro de las funciones y las fórmulas

16. 1. Funciones avanzadas Funciones más usadas por los contadores: • MATEMÁTICAS:Sumar, Sumar.Si, Promedio, Contar, Redondear, Contar.Si, Truncar, Máximo, Mínimo • DE TEXTO:Extrae,Izquierda, Derecha, Mayusc, Minusc, Nompropio, Repetir, Texto, Valor, Concatenar • LÓGICAS:Si, O, Y. Valores FALSO y VERDADERO, • DE FECHA:Hoy(), Dia, Mes, Año • DE BÚSQUEDA Y REFERENCIA:BuscarV

17. 1. Funciones avanzadas • ¿CÓMO SE INTRODUCE UNA FUNCIÓN? • Escribiéndola manualmente • ó • Usando el asistente de funciones Este link lleva a la ayuda sobre la función seleccionada. Es muy útil explorar la ayuda de Excel.

18. 1. Funciones avanzadas • LA AYUDA DE EXCEL • También se puede acceder a ella en cualquier momento presionando la tecla F1 o con el botón • Puede usarse: • El buscador • La tabla de contenido

19. 1. Funciones matemáticas =suma(rango) • Obtiene la suma aritmética. =promedio(rango) • Obtiene el promedio aritmético. • Hay que diferenciar entre cero y vacío: el cero sí se calcula en el promedio y la celda vacía, no.

20. 1. Funciones matemáticas =sumar.si(rango_1, “criterio”, [rango a sumar]) • Obtiene la suma aritmética de las celdas de un rango que cumplan con un criterio determinado. • El criterio ó condición se anota entre comillas usando los operadores de comparación =, >, <, <>, >= • El rango_1 es donde se encuentra el criterio que será evaluado. • El [rango a sumar] son las celdas que serán sumadas, siempre que se cumpla con el criterio. • Si se omite [rango a sumar], será el mismo que rango_1.

21. 1. Funciones: HERRAMIENTA BUSCAR OBJETIVO • En la búsqueda de un resultado en una fórmula, simplifica el método de “prueba y error”.

22. 1. Funciones de texto • ¿Qué es una cadena de texto? • ¿Qué es un valor? • Diferencia entre una cadena de texto y un valor • Nota: valores importados de otros sistemas en ocasiones son cadenas de texto y hay que convertirlos

23. 1. Funciones de texto =valor(texto) • Convierte un texto en valor. =texto(valor,formato) • Convierte un valor en texto • El formato va entre comillas, por ejemplo “$ ###,##0.00” • Donde el símbolo de # se omitirá cuando se trate de ceros a la izquierda de un valor. =concatenar(texto1,texto2,...) • Une cadenas de texto. • El texto debe siempre va “entre comillas”. • Es más fácil usar el símbolo & en lugar de esta función.

24. 1. Funciones de texto =mayusc(texto) • Convierte todo el texto a mayúsculas. =minusc(texto) • Convierte todo el texto a minúsculas. =nompropio(texto) • Convierte la primer letra de cada palabra a mayúsculas y el resto a minúsculas.

25. 1. Funciones de texto =derecha(texto, numero_de_caracteres) • Extrae el número de caracteres del lado derecho =izquierda(texto,numero_de_caracteres) • Extrae el número de caracteres del lado izquierdo =extrae(texto,posición_inicial,numero_de_caracteres) • Extrae una cadena de texto iniciando donde se indica

26. 1. Funciones de fecha =año(fecha) • Devuelve el año de una fecha =mes(fecha) • Devuelve el mes de una fecha =dia(fecha) • Devuelve el día de una fecha

27. 1. Funciones condicionales =o(condición1,condición2,...) • Devuelve VERDADERO si una de las condiciones es verdadera. • Devuelve FALSO si todas las condiciones son falsas. =y(condición1,condición2,...) • Devuelve VERDADERO si todas las condiciones son verdaderas. • Devuelve FALSO si una o más condiciones son falsas.

28. Funciones condicionales =SI(condición,valor_si_verdadero,valor_si_falso) • 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.

29. 1. Funciones de búsqueda =buscarv(valor_buscado,matriz, columna,ordenado) • Busca un valor en una tabla y devuelve el valor si lo encuentra, o el más acercado • IMPORTANTE: LA TABLA DEBE ESTAR ORDENADA EN ORDEN ASCEDENTE • ESTA FUNCIÓN PUEDE SER USADA PARA CALCULAR EL ISR DE PERSONAS FÍSICAS.

30. 1. Funciones de búsqueda =elegir(valor,opcion1,opcion2,opcion3,...) • Devuelve la opción seleccionada

31. 1. Funciones automáticas • Realiza y muestra el resultado de una función con sólo seleccionar un rango de números. • Las funciones automáticas son suma, promedio, contar, máximo, mínimo, contar números, entre otras. El resultado de la función automática aparece en la esquina inferior derecha de la ventana de Excel. Se pueden modificar las funciones haciendo click en el botón derecho del mouse.

32. 1. Auditoría de fórmulas • BOTONES MÁS IMPORTANTES: RASTREAR/QUITAR RASTREAR/QUITAR PRECEDENTES DEPENDIENTES

33. Temario

34. 2. TABLAS Y GRÁFICOSDINÁMICOS

35. 2. Tablas y gráficos dinámicos • ¿Qué es una tabla dinámica? • Es una hoja de cálculo interactiva. Herramienta que permite y analizar, agrupar y presentar de forma resumida grandes volúmenes de información. • Es fácil de usar y sobre todo, RÁPIDA. • Realiza los resúmenes utilizando funciones de hoja de cálculo como: SUMA, PROMEDIO, MAX, MIN, CONTAR, entre otras. • Permanece vinculada con la tabla de datos de origen.

36. 2. Tablas y gráficos dinámicos • Pasos para crear la tabla dinámica: • Seleccionar el rango de datos, incluyendo los encabezados de columna y sin incluir totales (IMPORTANTE: La tabla no debe estar cortada con filas o columnas en blanco ni subtotales). • En Excel 2003: Menú Datos  Informe de Tablas y Gráficos dinámicos. • En Excel 2007: Pestaña Insertar  Botón Tabla dinámica • Posteriormente se siguen los pasos del asistente y click en “Aceptar”

37. 2. Tablas y gráficos dinámicos • Así funciona: • Los encabezados de columna se arrastran y sueltan sobre los campos de la tabla, acomodándolos como los quieras ver.

38. 2. Tablas y gráficos dinámicos • El resultado: • De manera instantánea Excel agrupará, ordenará y sumará los datos. • También los puede contar cuando son textos.

39. 2. Tablas y gráficos dinámicos • Recomendaciones para trabajar con la tabla dinámica: • Opta por crear las tablas dinámicas en una hoja diferente; es mejor. • Usa siempre una sola tabla dinámica. No crees varias en el mismo libro de Excel, pues se perdería el sentido de lo rápido, práctico y fácil. • Las tablas que resulten de interés, imprímelas ó envíalas un archivo PDF; o pégalas como valores en otra hoja. • Experimenta con diversas formas de acomodar los datos en la tabla, una y otra y otra vez, sin temor a equivocarte; recuerda que la fuente de datos origen está segura y a salvo en otra hoja.

40. 2. Tablas y gráficos dinámicos • Filtros: muestra solo algunos de los datos • Otras herramientas a usar en la tabla dinámica • Extractos: con un doble click en uno de los datos, se obtiene un extracto automático en otra hoja.

41. 2. Tablas y gráficos dinámicos • ¿Qué es una gráfico dinámico? • Es un gráfico que se genera de forma automática, partiendo de los datos que hay en la tabla dinámica en ese momento, y se comporta de manera sincrónica con la tabla dinámica si esta se cambia. Se crea con un click en el botón siguiente: Excel 2003 Excel 2007 Se recomienda para tablas dinámicas con pocas agrupaciones.

42. Temario

43. 3. LAS MACROS EN EXCEL

44. 3. Uso de macros en Excel • ¿Qué es una macro? • Es la automatización de una tarea repetitiva mediante el uso de VBA (Visual Basic para Aplicaciones).

45. 3. Uso de macros en Excel • Aplicaciones de las macros: • Generar textos repetitivos • Dar formatos • Realizar cálculos • Tipos de Macros • Absolutas: las que se ejecutan siempre en la misma celda. • Relativas: las que se ejecutan dependiendo del lugar donde se esté.

46. Temario

47. 4. CREACIÓN DE FORMULARIOS

48. 4. Creación de formularios • ¿Para qué crear formularios? • Para validar la captura de datos que realizará otra persona • Para evitar que los datos y fórmulas en algunas celdas sean modificados • Aplicaciones de los formularios en la contaduría • Formatos y procedimientos en controles internos • Proformas de auditoría, entre otros

49. 4. Creación de formularios • ¿Cómo se crean los formularios? Existen dos maneras: 1) RESTRICCIÓN DE CELDAS. Significa que algunas celdas se pueden restringir para capturar solamente ciertos valores, que cumplan una condición (rangos numéricos o de fechas, longitudes, etc.) 2) CONTROLES DE FORMULARIO.Significa insertar en la hoja elementos tales como botones, casillas de verificación, etc. que pueden controlar valores de otras celdas.

50. 4. Creación de formularios • RESTRICCIÓN DE CELDAS • Excel 2003: menú datos  validación • Excel 2007: ficha datos  botón validación de datos En esta ventana se configuran los criterios de validación. También permite configurar un mensaje de ayuda así como mensajes de error.