Descarga la aplicación para disfrutar aún más
Vista previa del material en texto
INGENIERÍA Y GESTIÓN INGENI Y GEST Gestión Excel Cobranzas con de Ges� ón de Cobranzas con Excel Autor: Ing. Johnny Pacheco Contreras © Derecho de autor reservado Empresa Editora Macro E.I.R.L. © Derecho de edición, arte gráfi co y diagramación reservados Empresa Editora Macro E.I.R.L. Edición a cargo de: Empresa Editora Macro E.I.R.L. Av. Paseo de la República 5613 – Mirafl ores Lima - Perú (511) 719-9700 ventas@editorialmacro.com h� p://www.editorialmacro.com Primera edición: Marzo 2011 - 1000 ejemplares Impreso en los Talleres Gráfi cos de Empresa Editora Macro E.I.R.L. Lima - Perú ISBN Nº 978-612-304-054-3 Hecho el Depósito Legal en la Biblioteca Nacional del Perú Nº 2012-03572 Prohibida la reproducción parcial o total, por cualquier medio o método de este libro sin previa autorización de la Empresa Editora Macro E.I.R.L. � � ING. JOHNNY PACHECO CONTRERAS Ingeniero Industrial egresado de la Universidad Nacional de Ingeniería. Cuenta con la cer� fi cación Microso� MCAS: Microso� Cer� fi edApplica� onSpecialis� or Excel® 2007. Ha desempeñado funciones en empresas transnacionales como analista en el área Comercial, Post- Venta, Cobranzas y Riesgos. Posee una experiencia de 13 años como docente en cursos de Microso� Offi ce, diseño de páginas web, cómputo general y sistemas opera� vos. De manera independiente ha desarrollado aplicaciones “VBA for Excel” para las empresas Arte Tex� l La� no S.A., Servicios Legales Vidal & Vidal y Productos Tissue del Perú S.A. Actualmente se desempeña como docente en el Ins� tuto Sistemas UNI de la Universidad Nacional de Ingeniería e Ins� tuto Cibertec – Grupo Educa� vo UPC. Dedicatoria A mis padres Don Marcelino y Doña Ana, con todo mi amor y cariño,por ser mi modelo a seguir, por ser bondadosos, llenos de paz y sabiduría y a mis hermanos J. Agradecimiento Quiero darle gracias a un amigo que conocí trabajando en el área de cobranza, Luis Narváez Contreras que actualmente es jefe de cobranza de una empresa transnacional. Y también a mi amigo Víctor Lavado Flores, Jefe de cobranza de la empresa Servicios Legales Vidal & Vidal. Y a los trabajadores de la empresa Vidal & Vidal que fueron capacitados en el uso de la herramientas de Excel para mejorar la ges� ón de cobranza. Felicitar a los ingresantes a la facultad de ingeniería industrial y de sistemas de la Universidad Nacional de Ingeniería periodo 2012-1, que fueron capacitados en Microso� offi ce 2010. Sobre todo al estudiante Depaz Alegre, Emor y Damian por obtener la mayor nota en la capacitación, además a los que culminaron todo el curso aprobándolo con altas califi caciones, Roberto Dávila, Brenda Fajardo, Erick García, Luis Lino, Hernán Mamani, Silvana Soto, Jean Rafael y Carlos Vicente. Quiero agradecer a los docentes por su aporte y apoyo Humberto Coveñas, José Villanueva, Edison Reátegui y en especial al Gerente General de la empresa JH&CE Ingenieros SAC Cesar Echevarria por su gran apoyo desinteresado. Introducción Excel es un programa de hojas de cálculo de Microso� Offi ce que se puede usar para hacer un seguimiento de las cuentas por cobrar, crear modelos para analizar datos, escribir fórmulas para realizar cálculos con dichos datos, dinamizar los datos de diversas maneras y presentarlos en una variedad de gráfi cos con aspecto profesional. Vea lo que ha cambiado en Excel 2010 y por qué los cambios ayudan a crear mejores hojas de cálculo en menos � empo. Cuando pruebe el nuevo diseño, descubrirá que � ene sen� do la forma en que se han agrupado los comandos que ya sabe u� lizar. O quizás ha trabajado en Excel 2010 un par de veces, pero sigue preguntándose cómo se llevan a cabo algunas de las tareas básicas, por ejemplo, desplazarse por las celdas de una hoja de cálculo, seleccionar una celda o rango con� nuos o discon� nuos, cómo se escriben diferentes � pos de datos, cómo se modifi can o cómo se agregan y eliminan columnas y fi las que sobran. Conocerá los diferentes � pos de referencias de celdas y cuándo usarlas al copiar fórmulas: rela� vas, absolutas y mixtas. Los reportes son necesarios para que los gestores de cobranza trabajen efi cientemente, además de brindarle información para tomar una estrategia. Esto permite generar la información necesaria para diseñar y administrar el fl ujo de trabajo de su estrategia de cobranzas usando las funciones lógicas, búsqueda y las herramientas ordenar, subtotales, informes de tabla dinámica y segmentación de datos. La rotación de cartera establece el número de veces que las cuentas por cobrar giran, en promedio de un período determinado de � empo generalmente un año. El indicador de rotación de cartera permite conocer la rapidez de la cobranza, se puede preparar usando funciones lógicas, gráfi cos y minigráfi cos. El Análisis de Cartera consiste en estudiar el comportamiento de las cuentas por cobrar al cierre del mes y/o los históricos de cierre de la cartera deudora. Para ello, se realiza primero un enriquecimiento de información para luego segmentar la cartera según las reglas de ordenamiento que surjan de dicho análisis. Con el resultado obtenido, se categorizarán las deudas con la fi nalidad de establecer una estrategia de ges� ón diferenciada según sea el caso. ¿Le ocurre que repite las mismas acciones una y otra vez en las hojas de cálculo? ¿Sabía que puede crear macros de modo que Excel haga las tareas repe� � vas? Las macros son conjuntos de instrucciones basadas en acciones que graba cuando trabaja en una hoja de cálculo. Al grabar una macro, puede volver a usarla cuando sea necesario. En el úl� mo capítulo conocerá la herramienta PowerPivot para Excel es un complemento que puede usar para realizar efi caces análisis de datos en Excel 2010, al � empo que lleva la inteligencia empresarial de autoservicio a su escritorio. PowerPivot para Excel incluye una ventana para agregar y preparar datos, y una pestaña de PowerPivot en la cinta de opciones de Excel que puede usar para tratar los datos de una hoja de cálculo de Excel. ÍNDICE CAPÍTULO 1 INTRODUCCIÓN A LA INTERFACE EXCEL 2010 ...............................................................................13 Novedades Excel 2010 ....................................................................................................................... 15 Cinta de opciones mejorada ....................................................................................................... 15 Minigráfi cos ................................................................................................................................ 15 Segmentación de datos............................................................................................................... 16 Nuevas funciones estadís� cas .................................................................................................... 16 Herramienta de modo de compa� bilidad ................................................................................... 16 Formato condicional ................................................................................................................... 16 Capacidades de fi ltro mejoradas ................................................................................................ 17 Pegar con vista previa dinámica .................................................................................................. 18 Nuevos límites de gráfi cos .......................................................................................................... 18 Grabación de macros para elementos de gráfi cos ...................................................................... 18 Mejoras en la interfaz de usuario del gráfi co .............................................................................. 18 Complemento de Microso� SQL Server PowerPivot para Excel .................................................19 Nueva versión de Solver ............................................................................................................. 19 ¿Qué ha pasado con el menú Archivo? .............................................................................................. 19 Barra de herramientas de acceso rápido ........................................................................................... 20 Agregar un comando o grupo a la barra de herramientas de acceso rápido .............................. 21 Cinta de opciones ............................................................................................................................... 22 Usar la cinta de opciones ............................................................................................................ 23 ¿Que hay en la cinta de opciones? ............................................................................................. 24 Más comandos pero sólo cuando se necesitan .......................................................................... 24 Más opciones si las necesita ....................................................................................................... 25 Agregar una fi cha personalizada ................................................................................................. 25 Quitar una fi cha personalizada ................................................................................................... 26 ¿Dónde están los métodos abreviados de teclado? ................................................................... 27 Vista Diseño de página de Excel ......................................................................................................... 27 Elementos de Excel ............................................................................................................................ 28 Libro de Excel .............................................................................................................................. 28 Hoja de cálculo ............................................................................................................................ 28 Celda ........................................................................................................................................... 29 Rango .......................................................................................................................................... 29 La barra de � tulo......................................................................................................................... 30 La barra de fórmulas ................................................................................................................... 30 Cuadro de Nombre ..................................................................................................................... 30 Movimiento rápido en el libro .................................................................................................... 30 CAPÍTULO 2 CONCEPTOS BÁSICOS .....................................................................................................................33 Desplazarse ........................................................................................................................................ 35 Seleccionar rangos ............................................................................................................................. 36 Una única celda........................................................................................................................... 36 Un rango de celdas ..................................................................................................................... 36 Celdas no adyacentes o rangos de celdas ................................................................................... 37 Todas las celdas de una hoja de cálculo ...................................................................................... 37 Una fi la o una columna ............................................................................................................... 38 Filas o columnas adyacentes ....................................................................................................... 38 Filas o columnas no adyacentes .................................................................................................. 38 Selección especial ....................................................................................................................... 38 Edición de Formato ............................................................................................................................ 40 Fórmulas ............................................................................................................................................ 52 U� lizar operadores de cálculo en fórmulas ................................................................................ 52 Tipos de operadores ................................................................................................................... 52 Celdas Rela� vas, Absoluta y Mixta. .................................................................................................... 55 CAPÍTULO 3 REPORTES DE COBRANZA ..............................................................................................................59 Reporte por días de demora por documento legal ............................................................................ 61 Función Si .................................................................................................................................... 61 Reporte por cliente deudor y línea de negocio .................................................................................. 68 Ordenar datos en un rango ......................................................................................................... 68 Insertar subtotales en una lista de datos de una hoja de cálculo ............................................... 69 Resumir, analizar, explorar y presentar datos de resumen ................................................................ 73 Qué es un informe de tabla dinámica ......................................................................................... 73 Usar la segmentación de datos para fi ltrar datos de tablas dinámicas ....................................... 111 CAPÍTULO 4 INDICADORES DE COBRANZA ........................................................................................................117 Ra� os de Cobranza ............................................................................................................................ 119 Indicadores de Cobranza .................................................................................................................... 121 Gráfi cos de Cobranza ......................................................................................................................... 129 Tipos de gráfi cos disponibles ...................................................................................................... 130 Elementos de un Gráfi co ............................................................................................................. 131 Modifi car la posición de un gráfi co ............................................................................................. 132 Cambiar la vista del gráfi co entre fi las y columnas ..................................................................... 135 Girar un Gráfi co 3D ..................................................................................................................... 136 Dar Formato a Planos de un Gráfi co 3D ...................................................................................... 138 Formato a Planos Posteriores y Lateral ....................................................................................... 140 Dar Formato a los EJES ................................................................................................................141 Líneas de Cuadrículas.................................................................................................................. 145 Agregar un � tulo al gráfi co ......................................................................................................... 147 Agregar � tulos de eje .................................................................................................................. 148 Mostrar u ocultar la Leyenda ...................................................................................................... 149 Mostrar u ocultar e� quetas de datos de un gráfi co .................................................................... 150 Mostrar u ocultar Tabla de Datos ................................................................................................ 151 Cambiar Formato del gráfi co ...................................................................................................... 152 Crear dos conjuntos de porciones comparando deuda por mes ...................................................... 154 Crear un gráfi co que muestre la tendencia de la cuenta por Cobrar ................................................. 158 Crear gráfi cos de barra de medio de cobro y gráfi co de línea de obje� vo ......................................... 161 Crear gráfi co de tendencia cuando no se � ene información en algunos meses ................................ 169 Gráfi co de columnas con banda de indicador .................................................................................... 170 Crear gráfi cos que se actualizan al ingresar más datos ...................................................................... 179 Función DESREF .......................................................................................................................... 179 Crear gráfi cos usando controles de formularios o validación de datos ............................................. 185 Cómo u� lizar los controles de formulario en una hoja de cálculo para modifi car los datos del gráfi co. ... 187 Crear grafi co que muestre deuda por negocio en porcentaje vs deuda total. ................................... 192 Minigráfi cos ....................................................................................................................................... 197 ¿Qué son los minigráfi cos? ......................................................................................................... 197 Ventajas del uso de minigráfi cos ................................................................................................. 197 Crear un minigráfi co ................................................................................................................... 198 Crear un minigráfi co con los valores de los gestores Jenny, Jacky y Jefrey. ................................ 201 CAPÍTULO 5 ANÁLISIS DE COBRANZA ................................................................................................................205 Análisis de facturas canceladas .......................................................................................................... 207 Concatenar .................................................................................................................................. 207 Derecha ....................................................................................................................................... 207 Izquierda ..................................................................................................................................... 208 Analizar la variación de la línea de negocio ....................................................................................... 213 Análisis de Total cartera ..................................................................................................................... 216 CAPÍTULO 6 AUTOMATIZACIÓN CON MACROS .................................................................................................229 Introducción a la Macros ................................................................................................................... 231 Herramienta de Seguridad ................................................................................................................. 232 Grabadora de Macros ........................................................................................................................ 233 Grabar una macro ....................................................................................................................... 234 Ejecutar la macro ........................................................................................................................ 236 Ejecutar una macro ..................................................................................................................... 236 Ejecutar una macro presionando una combinación de teclas de método abreviado con CTRL . 236 Ejecutar una macro haciendo clic en un botón de la barra de herramientas de acceso rápido . 237 Ejecutar una macro mediante un botón de un grupo personalizado de la cinta de opciones ... 238 Ejecutar una macro haciendo clic en un área de un objeto gráfi co ............................................ 240 Editor de Visual Basic ......................................................................................................................... 243 Partes de la ventana de Microso� Visual para Aplicaciones ....................................................... 244 Propiedades ................................................................................................................................ 245 Código ......................................................................................................................................... 246 Editar Macro ...................................................................................................................................... 247 Descripción de un procedimiento o macro ................................................................................. 248 Guardar libro que con� ene macro .............................................................................................. 249 Lenguaje de programación de Visual Basic ........................................................................................ 250 Objetos ....................................................................................................................................... 250 Propiedades y métodos .............................................................................................................. 250 Colecciones ................................................................................................................................. 250 Declaraciones de variables ......................................................................................................... 251 Tipos de variables ....................................................................................................................... 251 El Núcleo de VBA ........................................................................................................................ 253 Añadir un nuevo módulo de VBA ................................................................................................ 254 Quitar un módulo VBA ................................................................................................................ 255 Ejecutar Código VBA ................................................................................................................... 255 MsgBox ....................................................................................................................................... 255 Estructuras .................................................................................................................................. 262 Select Case ..................................................................................................................................263 For … Next ................................................................................................................................... 264 For Each...Next ............................................................................................................................ 266 Do...Loop ..................................................................................................................................... 268 Eventos del objeto Worksheet .................................................................................................... 270 Eventos del objeto Workbook ..................................................................................................... 271 Automa� zar procesos con cartera deudor al cierre del mes ...................................................... 274 Guardar la macro el archivo Personal.xlsb .................................................................................. 286 Iden� fi car depósitos de los clientes ............................................................................................ 287 Aplica� vo Ges� ón de Cobranza Telefónica ........................................................................................ 344 CAPÍTULO 7 POWERPIVOT PARA EXCEL .............................................................................................................375 Introducción a PowerPivot para Excel ................................................................................................ 377 Disfrute de cálculos rápidos y análisis de grandes volúmenes de datos ..................................... 377 Tome ventaja de las expresiones de análisis de datos ................................................................ 377 Sacar provecho de las habilidades existentes y caracterís� cas familiares de Excel .................... 377 Integrar los datos de casi todas las fuentes ................................................................................ 377 Acceder a los datos relevantes tecla rápidamente ..................................................................... 377 Trabajar de forma integrada a través de un navegador Web ...................................................... 377 Compar� r y colaborar en el análisis con los demás .................................................................... 378 Actualizar automá� camente las aplicaciones compar� das ........................................................ 378 Descubrir y aprovechar las soluciones existentes ....................................................................... 378 Rastrear y monitorear las aplicaciones de análisis en un panel de control central .................... 378 Instalar PowerPivot para Excel ........................................................................................................... 378 Requisitos de hardware y so� ware ............................................................................................. 378 Requisitos de hardware .............................................................................................................. 378 Requisitos de so� ware ................................................................................................................ 379 Ficha PowerPivot ............................................................................................................................... 382 Ventana de PowerPivot ...................................................................................................................... 384 Portapapeles ............................................................................................................................... 384 Obtener datos externos .............................................................................................................. 384 Informes ...................................................................................................................................... 385 Formato ...................................................................................................................................... 385 Ordenar y fi ltrar .......................................................................................................................... 385 Ver ............................................................................................................................................... 386 Agregar datos al libro PowerPivot ...................................................................................................... 386 Agregar datos u� lizando una tabla vinculada de Excel ............................................................... 386 Agregar datos u� lizando el Asistente para la importación de tablas .......................................... 389 Crear una conexión a una base de datos de Access.................................................................... 389 Crear relaciones entre tablas ............................................................................................................. 392 ¿Qué es una relación? ................................................................................................................ 392 Requisitos para las relaciones ..................................................................................................... 394 Crear una columna calculada ............................................................................................................. 397 Cambiar el nombre de una columna calculada ........................................................................... 397 Para cambiar el nombre de una columna calculada existente o editarla ................................... 397 Descripción del rendimiento de las columnas calculadas ........................................................... 397 Crear una columna calculada para el análisis de la deuda en el rango de 10 a 90 días .............. 398 Crear una tabla dinámica a par� r de los datos PowerPivot ............................................................... 400 Agregar una tabla dinámica al análisis ........................................................................................ 400 Crear un gráfi co dinámico a par� r de los datos PowerPivot .............................................................. 404 111 CAPÍTULO INTRODUCCIÓN A LA INTERFACE EXCEL 2010 OBJETIVO: Descubra cómo hacer lo que hacía antes a medida que se familiariza con el diseño y entender el nuevo aspecto de Excel 2010 y buscar comandos habituales en la cinta de opciones. Se presenta una nueva versión de Excel 2010. También notará que hay nuevos elementos en la parte superior de la ventana. Los menús y los botones anteriores de Excel 2003 han sido reemplazados por esta nueva cinta de opciones, con fi chas en las que puede hacer clic para llegar a los comandos, además el botón offi ce de Excel 2007 ha sido reemplazado por la fi cha Archivo de color verde. La cinta de opciones se pensó para facilitar el uso de Excel y la búsqueda de los comandos que más se utilizan. Vea lo que ha cambiado y por qué los cambios ayudan a crear mejores hojas de cálculo en menos tiempo. Cuando pruebe el nuevo diseño, descubrirá que tiene sentido la forma en que se han agrupado los comandos que ya sabe utilizar. Conozca más detalles sobre el nuevo diseño y prepárese para trabajar con la nueva versión de Excel 2010. TEMA: � Novedades Excel 2010. � ¿Qué ha pasado con el menú Archivo? � Barra de herramientas de acceso rápido. � Cinta de opciones. � Vista Diseño de página de Excel. � Elementos de Excel. GESTIÓN Y METODOLOGÍA GESTIÓN DE COBRANZAS CON EXCEL 15 • NOVEDADES EXCEL 2010 La nueva versión de Excel 2010 incorpora nuevas herramientas y características. • CINTA DE OPCIONES MEJORADA Introducida en Excel 2007 por primera vez, la cinta de opciones permite al usuario encontrar fácilmente los comandos y las características que antes se incluían en complejosmenús y barras de herramientas. Aunque en Excel 2007 era posible personalizar la barra de herramientas de acceso rápido, no era posible agregar fi chas y grupos personalizados a la cinta de opciones. En Excel 2010, en cambio, se pueden crear fi chas y grupos personalizados y cambiar el nombre o el orden de las fi chas y grupos integrados. • MINIGRÁFICOS Los minigráfi cos son un nuevo tipo de visualización en Excel 2010. Son gráfi cos de tamaño de celda pequeño utilizados para mostrar tendencias en series de valores mediante el uso de gráfi cos de líneas, columnas o ganancias y pérdidas. Los minigráfi cos permiten que el visor vea en una sola celda gráfi cos densos de información que aumentan en gran medida la comprensión de datos del lector. Muestran el “qué” y no el “porqué” de los datos. Figura 1.0 Cinta de opciones mejorada. Figura 1.1. Se pueden usar minigráfi cos para resumir visualmente las tendencias junto a los datos. INTRODUCCIÓN A LA INTERFACE EXCEL 2010CAP. 116 • SEGMENTACIÓN DE DATOS La segmentación de datos facilita el fi ltrado y la interpretación de los datos. Mejoran las tablas dinámicas y funciones de cubos en un libro. Los sectores fi ltran datos de forma interactiva. Están por encima de la cuadrícula y se comportan como fi ltros de informe, por lo que se pueden enlazar a las funciones de cubos, gráfi cos dinámicos y tablas dinámicas para crear paneles o informes interactivos. • NUEVAS FUNCIONES ESTADÍSTICAS La biblioteca de funciones de Excel mejoró en Excel 2010. Mejoró la precisión de las funciones, mejoró la coherencia con los nombres de función de Excel y las defi niciones con 50 nuevas funciones, y se agregó una nueva interfaz de usuario de funciones. Se implementaron nuevos algoritmos para mejorar la precisión de nuestras funciones matemáticas, fi nancieras y estadísticas. • HERRAMIENTA DE MODO DE COMPATIBILIDAD Las nuevas características de Excel 2010 se deshabilitan cuando se abren versiones anteriores de Excel. Por ejemplo, las nuevas características de segmentación de datos y minigráfi cos de la versión 2010 se deshabilitan cuando se abre una versión anterior de Excel. Los libros creados en el formato de archivo de Excel 97-2003 (.xls) se abren automáticamente en modo de compatibilidad. Los libros creados en Excel 2010, pero que se destinan a otros usuarios que usan versiones anteriores de Excel, deben activar el modo de compatibilidad para evitar el uso accidental de funciones y características que son incompatibles con las versiones anteriores de Excel. Esto es muy importante para los usuarios que planean compartir libros con otros usuarios que aún no han migrado a Offi ce Excel 2007 o una versión posterior. • FORMATO CONDICIONAL El formato condicional permite que se hagan referencias a distintas hojas del libro (formato condicional entre hojas). Use el formato condicional para detectar y mostrar tendencias importantes, así como para resaltar excepciones de datos. Ahora están disponibles más estilos, opciones de barra de datos Figura 1.2. La segmentación de datos ofrece una forma muy visual para fi ltrar los datos de las tablas dinámicas. GESTIÓN Y METODOLOGÍA GESTIÓN DE COBRANZAS CON EXCEL 17 y nuevos conjuntos de iconos. Las referencias a otras hojas de cálculo permiten reglas de formato condicional. El formato condicional almacena dependencias de las fórmulas usadas, por lo que ya no es necesario volver a evaluar todo el formato condicional con tanta frecuencia. Las tablas dinámicas o desplazamiento se actualizan con mayor rapidez, por lo que se pueden mostrar más rápidamente. • CAPACIDADES DE FILTRO MEJORADAS Nuevo fi ltro de búsqueda. Cuando fi ltra datos en tablas, tablas dinámicas y gráfi cos dinámicos de Excel, puede aprovechar un nuevo cuadro de búsqueda que le permite encontrar rápidamente lo que necesita en las hojas de cálculo grandes. Por ejemplo, para encontrar un producto específi co en un catálogo que almacena más de 50.000 elementos, empiece a escribir el término de búsqueda y los productos correspondientes aparecerán instantáneamente en la lista. Puede restringir aún más los resultados si no selecciona el contenido que no desea ver. Figura 1.3. En Excel 2010 se � ene acceso a más conjuntos de iconos, incluidos triángulos, estrellas y cuadros. Figura 1.4. Buscar lo que necesita de manera rápida y efi caz es una necesidad básica. INTRODUCCIÓN A LA INTERFACE EXCEL 2010CAP. 118 • PEGAR CON VISTA PREVIA DINÁMICA La característica de pegado con vista previa dinámica permite ahorrar tiempo cuando se reutiliza contenido en Excel 2010 o en otros programas. Permite obtener una vista previa de las distintas opciones de pegado, como Pega y conserva los anchos de columna de origen, Sin bordes o Mantener formato de origen. La vista previa dinámica permite decidir de manera visual el aspecto que tendrá el contenido pegado antes de pegarlo en la hoja de cálculo. Cuando se mueve el puntero sobre las opciones de pegado para obtener una vista previa de los resultados, se muestra un menú con los elementos que cambian en contexto para ajustar lo mejor posible el contenido que va a reutilizar. La información en pantalla ofrece información adicional que le ayudará a tomar la decisión adecuada. • NUEVOS LÍMITES DE GRÁFICOS Es más fácil trabajar con gráfi cos en Microsoft Offi ce Excel 2007. Las mejoras específi cas incluyen nuevos límites de gráfi cos. En Microsoft Offi ce Excel 2007 puede tener hasta 32.000 puntos de datos en una serie de datos para gráfi cos 2-D. En Excel 2010, el número de puntos de datos en una serie de datos está limitado solamente por la memoria disponible. Esto permite a los usuarios, sobre todo a aquellos de la comunidad científi ca, visualizar y analizar de forma efi caz grandes conjuntos de datos. Se podrían producir errores de memoria si se calculan hojas de cálculo de 64 bits en un equipo de 32 bits. • GRABACIÓN DE MACROS PARA ELEMENTOS DE GRÁFICOS En Offi ce Excel 2007, la grabación de una macro mientras se aplica formato en un gráfi co u otro objeto no produce ningún código de macro. Sin embargo, en Excel 2010, se puede usar la grabadora de macros para grabar cambios de formato en gráfi cos y otros objetos. • MEJORAS EN LA INTERFAZ DE USUARIO DEL GRÁFICO Las mejoras incluyen interactividad de gráfi cos dinámicos, mejoras de formato, mejoras de paridad y aumentos de límites. Haga doble clic en cualquier elemento de gráfi co y aparecerá el cuadro de diálogo del formato. El selector de elementos de gráfi co está disponible en la minibarra de clic secundario. Los rellenos de trama volverán a estar disponibles al aplicar formato en los gráfi cos. Ahora puede grabar Figura 1.5. Puede copiar y pegar el contenido o los atributos de celda específi cos desde el Portapapeles en una hoja de cálculo. GESTIÓN Y METODOLOGÍA GESTIÓN DE COBRANZAS CON EXCEL 19 una macro para usar cambios de diseño y formato otra vez. Las mejoras de paridad se realizaron en las áreas de escala de los ejes, diseño, paridad visual y paridad de modelo de objetos. Los límites de tamaño de datos en los gráfi cos se quitan o se incrementan. Se quita el límite anterior de 32.000 puntos por serie de datos en 2D. La memoria del equipo es el límite actual. El máximo de 256.000 puntos de datos se ha quitado. El único límite es la memoria disponible y capacidad del equipo. Para obtener más información, vea el blog sobre mejoras adicionales en los gráfi cos en Excel 2010. • COMPLEMENTO DE MICROSOFT SQL SERVER POWERPIVOT PARA EXCEL Si desea modelar y analizar grandes cantidades de datos, puede descargar el complemento de PowerPivot para Excel y trabajar con esos datos dentro de los libros de Excel. Al usar este complemento, puede combinar rápidamente datos de varios orígenes, incluidas bases de datos corporativas, hojas de cálculo, informes y fuentes. A continuación, puede explorar, calcular y resumir de forma interactiva los datos mediante el uso de tablas dinámicas,segmentaciones de datos y otras características de Excel. Cuando interactúe con los datos, observará que el tiempo de respuesta es rápido, independientemente de si trabaja con cientos de fi las o cientos de millones de estas. Si tiene acceso a Servicios de Excel en Microsoft SharePoint Server 2010, puede poner sus informes y análisis en un sitio de SharePoint para que otros usuarios de la organización puedan sacar provecho de su trabajo. • NUEVA VERSIÓN DE SOLVER Excel 2010 incluye una nueva versión del complemento Solver, que se puede usar para buscar soluciones óptimas en los análisis de hipótesis. Solver tiene una interfaz de usuario mejorada, un nuevo Evolutionary Solver basado en algoritmos genéticos que controla modelos con cualquier función de Excel, nuevas opciones de optimización global, mejores métodos de optimización no lineal y de programación lineal, así como nuevos informes de viabilidad y linealidad. Además, el complemento Solver está disponible ahora en una versión de 64 bits. • ¿QUÉ HA PASADO CON EL MENÚ ARCHIVO? El nuevo diseño de Microsoft Excel 2010 incluye la fi cha Archivo que reemplaza al Botón de Microsoft Offi ce incluido en la versión de Microsoft Excel 2007. El botón y la fi cha reemplazan al menú Archivo incluido en algunos programas de 2007 Microsoft Offi ce System y en Microsoft Offi ce 2003 y versiones anteriores. Después de hacer clic en la fi cha Archivo, podrá ver la Vista Backstage de Microsoft Excel. La Vista Backstage es donde se administran los archivos y sus datos, creando, guardando, inspeccionando metadatos o información personal ocultos y confi gurando opciones. En síntesis, se trata de todo lo que se hace a un archivo que no se hace en él. Figura 1.6. La fi cha Archivo es la fi cha de color verde en Microso� Excel 2010, ubicada en la esquina superior izquierda del programa de Microso� Excel 2010. INTRODUCCIÓN A LA INTERFACE EXCEL 2010CAP. 120 • BARRA DE HERRAMIENTAS DE ACCESO RÁPIDO La barra de herramientas de acceso rápido es una barra de herramientas que se puede personalizar y que contiene un conjunto de comandos independientes de la fi cha en la cinta de opciones que se muestra. Esta barra se puede mover desde una de las dos ubicaciones posibles y se le pueden agregar botones que representan comandos (haga clic en la barra de herramientas de acceso rápido y selecciona un comando que se desea agregar). Figura 1.7 La Vista Backstage se puede abrir, guardar, imprimir, compar� r y administrar los archivos, así como confi gurar las opciones del programa. Figura 1.8. La barra de herramientas de acceso rápido, que se encuentra sobre la cinta de opciones cuando se inicia Excel 2010 (también se puede mostrar debajo de la cinta de opciones). GESTIÓN Y METODOLOGÍA GESTIÓN DE COBRANZAS CON EXCEL 21 En la barra de herramientas de acceso rápido se pueden agregar los botones de comando. • AGREGAR UN COMANDO O GRUPO A LA BARRA DE HERRAMIENTAS DE ACCESO RÁPIDO Agregar Comando 1 En la cinta de opciones, haga clic en la fi cha Insertar. 2 En el grupo Tablas haga clic en el botón Tabla Dinámica. 3 Señale el botón de comando Tabla dinámica y haga clic en el botón secundario del mouse. 4 Excel muestra un menú contextual, haga clic en Agregar a la barra de herramientas de acceso rápido. Agregar grupo 1 En la cinta de opciones, haga clic en la fi cha Datos. 2 En el grupo Herramientas de datos señale una sección libre y haga clic en el botón secundario del mouse. 3 Excel muestra un menú contextual, haga clic en Agregar grupo a barra de herramientas de acceso rápido. Figura 1.9. Agregar botón de comando a barra de herramientas de acceso rápido. Figura 1.10. Seleccionar Agregar grupo a barra de herramientas de acceso rápido. INTRODUCCIÓN A LA INTERFACE EXCEL 2010CAP. 122 Agregar más comandos En la barra de herramientas de acceso rápido, haga clic y seleccione Más comandos, Excel muestra el cuadro de diálogo Opciones de Excel y automáticamente selecciona la Barra de herramientas de acceso rápido; en la sección Personalice la barra de herramientas de acceso rápido realice lo siguiente: 1 En comandos disponibles seleccione una fi cha, para este ejemplo haga clic en la fi cha Insertar. 2 Se muestran los botones de comandos de la fi cha Insertar, seleccione el botón de comando Tabla dinámicas. 3 Haga clic en el botón Agregar. 4 Finalmente, clic en el botón Aceptar. • CINTA DE OPCIONES La cinta de opciones se introdujo en Offi ce 2007 como parte de la interfaz de usuario de Offi ce. Se incluye en aplicaciones de Offi ce 2007 exclusivas (Access, Excel, PowerPoint y Word), y está incluida en todas las aplicaciones de escritorio de Offi ce 2010. La cinta de opciones se diseñó para facilitar el trabajo con las aplicaciones de Offi ce y para ayudar a los usuarios a descubrir las efi caces características y capacidades de Offi ce. Los menús y las barras de herramientas se han ampliado con el tiempo, haciendo que fuera más difícil para los usuarios encontrar los comandos necesarios rápida y fácilmente. Figura 1.11. Cuadro de diálogo Opciones de Excel. GESTIÓN Y METODOLOGÍA GESTIÓN DE COBRANZAS CON EXCEL 23 La cinta de opciones a primera vista: CARACTERÍSTICA DESCRIPCIÓN Botón Offi ce (Offi ce 2007) y fi cha Archivo (Offi ce 2010) Proporciona una ubicación centralizada de todas las cosas que puede hacer con un archivo: compar� r, imprimir o enviar. Barra de herramientas de acceso rápido Una barra de herramientas personalizable que muestra los comandos que más usa. Fichas contextuales Estas fi chas se muestran en la cinta de opciones sólo cuando son relevantes a la tarea que está realizando, como aplicar formato a una tabla o a una imagen. Galerías Seleccione entre una variedad de temas, es� los, efectos, formas y plan� llas con formato previo. Vista previa dinámica Vea los cambios de formato antes de aplicarlos al documento. • USAR LA CINTA DE OPCIONES Offi ce 2010 aplica Interfaz de usuario de Fluent, o la cinta de opciones, a todas las aplicaciones de Offi ce, lo que proporciona un aspecto homogéneo a todas las aplicaciones. Además, para ayudar a los usuarios que se desplazan por la cinta mediante el teclado, ahora es posible saltar de grupo en grupo pulsando Ctrl+fl echa derecha/izquierda en una fi cha de la cinta y pasar al grupo siguiente o anterior. Y ahora también puede personalizar la cinta, de modo que le resultará aún más fácil encontrar las funciones que más utilice. La primera vez que inicie Microsoft Excel de 2010, quizá le sorprenda lo que vea. Los menús y las barras de herramientas de algunos programas se han sustituido por la cinta de opciones. Figura 1.12. La cinta de opciones se ha diseñado para ayudarle a encontrar fácilmente los comandos necesarios para completar una tarea. INTRODUCCIÓN A LA INTERFACE EXCEL 2010CAP. 124 • ¿QUE HAY EN LA CINTA DE OPCIONES? Son tres los componentes básicos de la cinta de opciones: 1 Fichas: Hay siete en la parte superior y cada una de ellas representa una de las tareas básicas que se hacen en Excel. 2 Grupos: Cada fi cha tiene grupos que muestran elementos relacionados entre sí. 3 Comandos: Un comando es un botón, un cuadro en el que se escribe información o un menú. Los comandos más importantes de Excel se encuentran en la primera fi cha, la fi cha Inicio. Estos comandos son los que se han identifi cado en Microsoft como los más empleados para llevar a cabo tareas básicas en hojas de cálculo. Los grupos aglutinan todos los comandos que se necesitan para realizar una tarea concreta, y se mantienen a la vista y disponibles para su uso mientras se trabaja en la tarea, en lugar de permanecer escondidos en los menús. Estos comandos esenciales están a la vista en la parte superior del área de trabajo. • MÁS COMANDOS PERO SÓLO CUANDO SE NECESITAN Los comandos de la cinta de opciones son los que más se utilizan. En lugar de mostrar todos los comandos todo el tiempo, Excel 2010 muestra algunoscomandos en respuesta a las acciones que se realizan. Figura 1.13. La cinta de opciones se ex� ende a lo largo de la parte superior de Excel y los comandos relacionados de la cinta de opciones están organizados en grupos. Figura 1.14. Cree un gráfi co y, a con� nuación, aparecerán las fi chas de las Herramientas de gráfi cos: Diseño, Presentación y Formato. GESTIÓN Y METODOLOGÍA GESTIÓN DE COBRANZAS CON EXCEL 25 Por ejemplo, si no hay un gráfi co en una hoja de cálculo, los comandos que se usan para trabajar con gráfi cos no son necesarios. Pero una vez creado, aparecen las Herramientas de gráfi cos con tres fi chas: Diseño, Presentación y Formato. En dichas fi chas están los comandos que se precisan para trabajar con un gráfi co. La cinta de opciones responde a la acción del usuario. Use la fi cha Diseño para cambiar el tipo de gráfi co o moverlo a otra ubicación. • MÁS OPCIONES SI LAS NECESITA Cuando vea esta fl echa (denominada inicializador de cuadros de diálogo) en la esquina inferior derecha de un grupo, signifi ca que hay más opciones dentro del grupo. Haga clic en la fl echa y se mostrará un cuadro de diálogo o panel de tareas. Por ejemplo, en la fi cha Inicio, en el grupo Fuente, se encuentran todos los comandos que más se utilizan para efectuar cambios en las fuentes: cambio de fuente, cambio del tamaño de fuente y formato de negrita, cursiva o subrayado. • AGREGAR UNA FICHA PERSONALIZADA Puede cambiar el nombre y cambiar el orden de las fi chas y grupos predeterminados que vienen integrados en Microsoft Excel 2010. Sin embargo, no puede cambiar el nombre de los comandos predeterminados, cambiar los iconos asociados a los comandos ni cambiar el orden de esos comandos. 1 Haga clic en la fi cha Archivo. 2 Luego clic en Opciones. Excel muestra el cuadro de diálogo Opciones de Excel. 3 Haga clic en Personalizar la cinta de opciones. Figura 1.15. Si desea más opciones, haga clic en el inicializador de cuadros de diálogo. INTRODUCCIÓN A LA INTERFACE EXCEL 2010CAP. 126 4 En la sección Personalizar la cinta de opciones seleccione la fi cha Vista. 5 Haga clic en Nueva Ficha. 6 Seleccione nueva fi cha (personalizada) y haga clic en Cambiar nombre. 7 Excel muestra la ventana Cambiar nombre, escriba Gestión Cobranza. 8 Seleccione nuevo grupo (personalizada) y haga clic en Cambiar nombre. 9 Excel muestra la ventana Cambiar nombre, escriba Indicadores. 10 En la sección Comandos disponibles seleccione fi chas personalizadas y haga clic en una fi cha y seleccione el botón de comando que se agregar{a a la nueva fi cha. 11 Haga clic en el botón Agregar. 12 Repita los pasos 10 y 11 para agregar más botones de comando. 13 Para fi nalizar haga clic en Aceptar. • QUITAR UNA FICHA PERSONALIZADA Es posible ocultar fi chas personalizadas y predeterminadas, pero sólo se pueden quitar fi chas personalizadas. 1 Haga clic en la fi cha Archivo. 2 Luego haga clic en Opciones. 3 Haga clic en Personalizar la cinta de opciones. 4 Seleccione Ficha y haga clic en Quitar. 5 Para ver y guardar sus personalizaciones, haga clic en Aceptar. Figura 1.16. También puede cambiar el nombre de un comando y agregar un icono que represente al comando haciendo clic en el comando y luego en Cambiar nombre. GESTIÓN Y METODOLOGÍA GESTIÓN DE COBRANZAS CON EXCEL 27 • ¿DÓNDE ESTÁN LOS MÉTODOS ABREVIADOS DE TECLADO? Si trabaja con el teclado más que con el mouse, le gustará conocer los métodos abreviados de Excel 2010. Los nuevos métodos abreviados también tienen nombre: Sugerencias de teclas, que aparecen al presionar ALT. Todas las fi chas de la cinta de opciones, los comandos de las fi chas, la barra de herramientas de acceso rápido tienen sugerencias de teclas. Presione la tecla de la fi cha que desea mostrar. Aparecerán todos los identifi cadores de sugerencias de teclas para los botones de la fi cha. Utilice la tecla correspondiente al botón que desea. 1 Presione la tecla [ALT] para que aparezcan las sugerencias de teclas de método abreviado. 2 Después presione la tecla [O] para seleccionar la fi cha Inicio. 3 Presione la tecla [1] para aplicar negrita el texto seleccionado. • VISTA DISEÑO DE PÁGINA DE EXCEL La cinta de opciones no es la única novedad de Excel 2007. La vista Diseño de página también es nueva. Si ha trabajado en la vista Diseño de página de Microsoft Offi ce Word, se alegrará de disfrutar de las mismas ventajas en Excel. Para llegar a la vista, haga clic en Vista Diseño de página, en la barra de herramientas Ver / en la parte inferior derecha de la pantalla. O haga clic en la fi cha Vista de la cinta de opciones y después en vista Diseño de página, en el grupo Vistas de libro. La vista Diseño de página de Excel. 1 Encabezados de columna. 2 Encabezados de fi la. 3 Reglas de margen. Figura 1.17. Puede usar las sugerencias de teclas, por ejemplo, para aplicar negrita al texto en la celda. 1. [ALT] 2. [O] 3. [1] INTRODUCCIÓN A LA INTERFACE EXCEL 2010CAP. 128 En la vista Diseño de página hay márgenes de página en la parte superior, lateral e inferior de la hoja de cálculo, además de un espacio azul entre las hojas. La regla lateral y superior ayuda a ajustar los márgenes. • ELEMENTOS DE EXCEL • LIBRO DE EXCEL Un libro de Microsoft Offi ce Excel es un archivo que incluye una o varias hojas de cálculo que se pueden utilizar para organizar distintos tipos de información relacionada. Para crear un libro nuevo, se puede abrir un libro en blanco. Asimismo, el nuevo libro se puede basar en otro existente, en la plantilla de libro predeterminada o en cualquier otra plantilla. Los libros de trabajo de Excel tienen la extensión .XLSX para que la computadora los reconozca como tal. Cuando se inicia una sesión de Excel automáticamente se abre un nuevo libro de trabajo con el nombre provisional de Libro1. • HOJA DE CÁLCULO La hoja de cálculo es uno de los distintos tipos de hojas que puede contener un libro de trabajo. Es una herramienta muy útil para todas aquellas personas que trabaja con gran cantidad de números y necesitan realizar cálculos u operaciones con ellos. Las hojas de cálculo están formadas por columnas y fi las. Es como una gran hoja cuadriculada formada por 16,384 columnas y 1’ 048,576 fi las. Una columna es el conjunto de celdas seleccionadas verticalmente. Cada columna se nombra por letras, por ejemplo A, B, C,.......AA, AB,........AAA, AAB……XFD. Una fi la se numera desde 1 hasta 1’ 048,576 y es la selección horizontal de un conjunto de celdas de una hoja de cálculo. Figura 1.18. Con esta nueva vista, no hace falta la vista preliminar para hacer ajustes en la hoja de cálculo antes de imprimirla. GESTIÓN Y METODOLOGÍA GESTIÓN DE COBRANZAS CON EXCEL 29 • CELDA La intersección de una columna y una fi la se denomina Celda y se nombra con el nombre de la columna a la que pertenece y a continuación el número de su fi la, por ejemplo: La primera celda pertenece a la columna A y la fi la 1, por lo tanto la celda se llama A1. Cuando el cursor está posicionado en alguna celda preparado para trabajar con ésta, dicha celda se denomina Celda activa y se identifi ca porque aparece más remarcada que las demás. • RANGO Un rango es un conjunto de celdas. La fórmula normal para el rango de referencia determina primero la celda de arriba a la izquierda (Inicio), después la de abajo a la derecha (Final), como en B3:E11 (las letras de las columnas no necesitan ser mayúsculas). Figura 1.19. Hoja de cálculo está formada por 16,384 columnas y 1’048,576 fi las. Figura 1.20. Rango B3:E11 formado por 36 celdas. INTRODUCCIÓN A LA INTERFACE EXCEL 2010CAP. 130 • LA BARRA DE TÍTULO Contiene el nombre del documento sobre el que se está trabajando en ese momento. Cuando creamos un libro nuevo se le asigna el nombre provisional Libro1, hasta que lo guardemos y le demos el nombre que queramos. En el extremo de la derecha están los botones para minimizar, restaurar y cerrar. •LA BARRA DE FÓRMULAS La barra Fórmula nos muestra el contenido de la celda activa, es decir, la casilla donde estamos situados, cuando vayamos a modifi car el contenido de la celda. • CUADRO DE NOMBRE El Cuadro de Nombres muestra la celda de referencia o el nombre de la celda activa. Para un rango, solo se muestra la celda activa del Rango. • MOVIMIENTO RÁPIDO EN EL LIBRO Dentro de nuestro libro de trabajo existen varias hojas de cálculo. Por defecto aparecen 3 hojas de cálculo aunque el número podría cambiarse. Para movernos por las distintas hojas del libro de trabajo, utilizar la barra de etiquetas. Una vez visualizada la hoja a la que queremos acceder, bastará con hacer clic sobre la etiqueta de ésta. Para visualizar a partir de la Hoja1. Para visualizar la hoja anterior a las que estamos visualizando. Para visualizar la hoja siguiente a las que estamos visualizando. Para visualizar las últimas hojas. Figura 1.21. En la barra de � tulo el libro se guardó con el nombre Novedades 2010. Figura 1.22. La barra Fórmula muestra el contenido de la celda A1. Figura 1.24. Si todas las hojas del libro de trabajo caben en la barra, estos botones no tendrán ningún efecto. Figura 1.23. Cuadro de nombres muestra la celda A1 ac� va. GESTIÓN Y METODOLOGÍA GESTIÓN DE COBRANZAS CON EXCEL 31 También puede hacer clic derecho en la barra de etiquetas y le muestra solo 15 Hojas, si tienen más hojas y desea seleccionar la Hoja 19 haga clic en Más hojas … y Excel muestra el cuadro de diálogo Activar y seleccione la Hoja 19 y luego clic en Aceptar. Figura 1.25. Hacer clic derecho en la barra de e� quetas y seleccionar la Hoja 7. 222 CAPÍTULO CONCEPTOS BÁSICOS OBJETIVO: Ha dedicado algún tiempo a orientarse con el nuevo aspecto de Excel 2010. Ahora ha llegado el momento de ponerse a trabajar. Supongamos que le han pedido que escriba unos datos en Excel 2010, pero no ha trabajado nunca con Excel. ¿Por dónde debe empezar? O quizás ha trabajado en Excel 2010 un par de veces, pero sigue preguntándose cómo se llevan a cabo algunas de las tareas básicas, por ejemplo, desplazarse por las celdas de una hoja de cálculo, seleccionar una celda o rango continuos o discontinuos, cómo se escriben diferentes tipos de datos, cómo se modifi can o cómo se agregan y eliminan columnas y fi las que sobran. Usar Excel para sumar, dividir, multiplicar o restar. Aprender cómo escribir fórmulas y usar operadores matemáticos para que Excel haga cálculos correctos. Escribir fórmulas que puedan actualizar automáticamente los resultados cuando se cambian los valores o cuando se copia una fórmula en otra ubicación. Conocer los diferentes tipos de referencias de celdas y cuándo usarlas al copiar fórmulas: relativas, absolutas y mixtas. TEMA: � Desplazarse. � Seleccionar rangos continuos o discontinuos. � Edición de Formato. � Fórmulas � Celdas Relativas, Absoluta y Mixta. GESTIÓN Y METODOLOGÍA GESTIÓN DE COBRANZAS CON EXCEL 35 • DESPLAZARSE En la fi gura 2.0 se tiene información de cobranza que cuenta 24,301 fi las y 19 columnas. Para desplazarse desde la celda A1 hasta la celda A24301 (última fi la que contiene datos) realice lo siguiente: 1 Seleccione celda A1. 2 Pulse las teclas [ Ctrl ] + [ ↓ ] (Tecla direccional hacia abajo). De igual forma, si quiere desplazarse desde la celda A1 hasta la celda S1 (última columna que contiene datos) realice lo siguiente: 1 Seleccione celda A1. 2 Pulse las teclas [ Ctrl ] + [ → ] (Tecla direccional hacia la derecha). Figura 2.0. Una vez realizado los pasos 1 y 2 la nueva celda ac� va es A24301 Figura 2.1. una vez realizado los pasos 1 y 2, la nueva celda ac� va es S1 . CONCEPTOS BÁSICOSCAP. 236 Las siguientes combinaciones de teclas también nos permiten desplazarnos: � [ Ctrl ] + [ Inicio ] Va al inicio de una hoja de cálculo, selecciona la celda A1. � [ Ctrl ] + [ Fin ] Selecciona la celda que representa la última columna que contiene datos y la última fi la que contiene datos de una hoja de cálculo. � [ Av Pág ] Baja una pantalla en una hoja de cálculo. � [ Re Pág Sube una pantalla en una hoja de cálculo. � [ Alt ] + [Av Pág ] Desplaza una pantalla a la derecha en una hoja de cálculo. � [ Alt ] + [Re Pág ] Desplaza una pantalla a la izquierda en una hoja de cálculo. � [TECLAS DE DIRECCIÓN] Mueve el cursor una celda hacia arriba, hacia abajo, hacia la izquierda o hacia la derecha. • SELECCIONAR RANGOS En una hoja de cálculo puede seleccionar celdas, rangos, fi las y columnas. También puede seleccionar todo o parte del contenido de una celda y activar el modo de edición para modifi car esos datos. • UNA ÚNICA CELDA Haga clic en la celda o presione las teclas de fl echa para moverse hasta ella. • UN RANGO DE CELDAS Existen varias formas de seleccionar el rango B5:L11, realice lo siguiente: � Haga clic en la primera celda (B5) del rango y, a continuación, arrastre el cursor del mouse hasta la última celda (L11). � Haga clic en la primera celda del rango (B5) y, a continuación, mantenga presionada la tecla MAYÚS [SHIFT] mientras presiona las teclas direccionales para extender la selección. � Haga clic en la primera celda del rango (B5) y, a continuación, mantenga presionada la tecla MAYÚS (SHIFT) + [CTRL] mientras presiona las teclas direccionales para extender la selección. � Haga clic en la primera celda del rango (B5) y presionar después [F8] para extender la selección utilizando las teclas direccionales. Vuelva a presionar [F8] para detener la extensión de la selección. � Haga clic en una celda que pertenezca al rango (B5:L11) y pulse las teclas [Ctrl] + [E] ó [Ctrl] + [*] Figura 2.2. Existen varias formas de seleccionar un rango. GESTIÓN Y METODOLOGÍA GESTIÓN DE COBRANZAS CON EXCEL 37 • CELDAS NO ADYACENTES O RANGOS DE CELDAS Existen varias formas de seleccionar los rangos no adyacente B5:L11 y B15:L21, realice lo siguiente: � Seleccione la primera celda o rango (B5:L11) y, a continuación, mantenga presionada la tecla [CTRL] mientras selecciona otras celdas o rangos no adyacentes con el mouse. � También puede seleccionar la primera celda o rango (B5:L11), y presionar a continuación MAYÚS [SHIFT] + [F8], luego seleccionar otras celdas o rangos de celdas no adyacentes con el mouse. Para dejar de agregar a la selección celdas o rangos, presione MAYÚS [SHIFT] + [F8] otra vez. • TODAS LAS CELDAS DE UNA HOJA DE CÁLCULO Haga clic en el botón Seleccionar todo. También puede presionar [CTRL] + [E] para seleccionar toda la hoja de cálculo. Figura 2.3. Existen varias formas de seleccionar rangos no adyacentes. No puede cancelar la selección de una celda o rango de celdas en una selección no adyacente sin cancelar toda la selección. Si la hoja con� ene datos, al presionar [CTRL] + [E] se selecciona el área actual. Si presiona [CTRL] + [E] una segunda vez se selecciona toda la hoja de cálculo. CONCEPTOS BÁSICOSCAP. 238 • UNA FILA O UNA COLUMNA Haga clic en el encabezado de fi la o de columna. 1 Encabezado de fi la. 2 Encabezado de columna. • FILAS O COLUMNAS ADYACENTES Arrastre los encabezados de fi la o columna, o bien, seleccione la primera fi la o columna y, a continuación, mantenga presionada la tecla MAYÚS [SHIF] mientras selecciona la última fi la o columna. • FILAS O COLUMNAS NO ADYACENTES Haga clic en el encabezado de columna o de fi la de la primera fi la o columna de la selección. Después, mantenga presionada la tecla MAYÚS mientras hace clic en los encabezados de columna o de fi la de las otras fi las o columnas que desea agregar a la selección. • SELECCIÓN ESPECIAL Para seleccionar rápidamente todas las celdas que contienen tipos específi cos de datos (como fórmulas) o solo celdas que cumplen con criterios específi cos (como celdas en blanco, visibles únicamente o la última celda de la hoja de cálculo que contiene datos o formato) lo puede realizar mediante el comando Ir a. � Seleccionar celdasen blanco Muchas veces cuando se baja información de un sistema ERP, la información que se exporta a Excel muestra una fi la vacía y de esta forma no se puede realizar herramientas como subtotales, tabla dinámica, fi ltros, etc. Se tiene que seleccionar las celdas vacías para eliminar las fi las vacías. 1 Seleccione la columna A. 2 Haga clic en la fi cha Inicio, en el grupo Modifi car haga clic en el botón Buscar y seleccionar. 3 Y luego clic en Ir a Especial. 4 Excel muestra el cuadro de diálogo Ir a Especial y, a continuación haga clic en la opción Celdas en blanco. 5 Y para fi nalizar clic en Aceptar. Figura 2.4. En pocos segundos puede seleccionar muchas celdas en blanco. GESTIÓN Y METODOLOGÍA GESTIÓN DE COBRANZAS CON EXCEL 39 � Seleccionar celdas con fórmulas Cuando tenga la necesidad de seleccionar solo celdas que contienen fórmulas en una hoja de cálculo realice lo siguiente: 1 Seleccione toda la hoja. 2 Haga clic en la fi cha Inicio, en el grupo Modifi car haga clic en el botón Buscar y seleccionar. 3 Y luego clic en Ir a Especial. 4 Excel muestra el cuadro de diálogo Ir a Especial y, a continuación haga clic en la opción Celdas con fórmulas. 5 Y para fi nalizar clic en Aceptar. � Seleccionar celdas con constantes Cuando tenga la necesidad de seleccionar solo celdas que contienen constantes en una hoja de cálculo realice lo siguiente: 1 Seleccione toda la hoja. 2 Haga clic en la fi cha Inicio, en el grupo Modifi car haga clic en el botón Buscar y seleccionar. 3 Y luego clic en Ir a Especial. 4 Excel muestra el cuadro de diálogo Ir a Especial y, a continuación haga clic en la opción Constantes. 5 Y para fi nalizar clic en Aceptar. Figura 2.5. En pocos segundos puede seleccionar muchas celdas que con� enen fórmulas. CONCEPTOS BÁSICOSCAP. 240 • EDICIÓN DE FORMATO El área de crédito y cobranza de una empresa es muy importante dentro de una organización. Antes de una venta el área de crédito debe decidir a quién, hasta qué monto y a qué plazo venderle, y después de la venta el área de cobranza ve que los plazos de pago se cumplan. Esto viene a estar directamente relacionado con los ingresos de la compañía y con su capital de trabajo, y se refl eja en su estado fi nanciero. El objetivo principal de Cobranzas es sintéticamente: “Cobrar todo en el menor tiempo posible”, sin perder la visión de la importancia de conservar la relación comercial con los clientes. Las grandes empresas cuentan con un sistema ERP (Enterprise Resource Planning) que son sistemas de información gerenciales que integran y manejan muchos de los negocios asociados con las operaciones de producción y de los aspectos de distribución de una compañía en la producción de bienes o servicios. El área de cobranza usa la transacción FBL5N del sistema ERP – SAP que le permite visualizar los documentos legales pendientes de pago (partidas abiertas) y cancelados (partidas compensadas). Figura 2.6 Información de la transacción FBL5N GESTIÓN Y METODOLOGÍA GESTIÓN DE COBRANZAS CON EXCEL 41 La información de la transacción FBL5N se exporta a Excel y se muestra como en la fi gura 2.7. Figura 2.7. Archivo Cierre CIC-2011 que muestra la cartera deudor de todos los clientes. CONCEPTOS BÁSICOSCAP. 242 Abrir el archivo CIERRE CIC-201101 exportado del sistema SAP muestra muchas fi las y columnas vacías, los datos mostrados son como la fi gura 2.7. La columna G muestra el código del cliente para cada documento legal (factura, Boleta, Nota de crédito, Nota de Débito, etc.), pero no muestra el nombre del cliente para cada documento legal sino una sola vez. Pasos para eliminar fi las y columnas vacías y repetir el nombre. 1 Insertar una hoja de cálculo, para lo cual debemos hacer clic en el botón insertar hoja de cálculo 2 Seleccione las columnas H e I y haga clic derecho en el encabezado Columna, luego seleccione Copiar. 3 Haga clic en la hoja 1, señale la celda A1 y haga clic derecho, luego seleccione Pegar. Figura 2.8. El nombre del cliente se debe repe� r para cada documento legal. Figura 2.9. También puede copiar con la combinación de teclas [Ctrl] + [C] Figura 2.10. También puede pegar con la combinación de teclas [Ctrl] + [V]. GESTIÓN Y METODOLOGÍA GESTIÓN DE COBRANZAS CON EXCEL 43 Los códigos del cliente tienen que estar en la misma fi la con el nombre. 4 Seleccione la celda B4:B6. 5 Presione la combinación de teclas [Ctrl] + [-]. 6 Excel muestra el cuadro de diálogo Eliminar celdas, haga clic en el botón opción Desplazar las celdas arriba. 7 Haga clic en el botón Aceptar. Figura 2.11. El contenido de la celda B7 se desplazará a la celda B4. Figura 2.12. Los códigos están alineados con los nombres. CONCEPTOS BÁSICOSCAP. 244 Ahora eliminar las celdas vacías de la columna A para tener una lista de datos. 8 Seleccione la columna A. 9 Pulse la tecla [F5], Excel muestra el cuadro de diálogo Ir a, 10 Haga clic en el botón Especial, Excel muestra el cuadro de diálogo Ir a Especial. 11 Haga clic en el botón de opción Celdas en Blanco. 12 Haga clic en el botón Aceptar. 13 Pulse la combinación de teclas [Ctrl] + [-]. 14 Excel muestra el cuadro de diálogo Eliminar celdas, haga clic en el botón de opción Toda la fi la. 15 Haga clic en el botón Aceptar. En la hoja Cierre CIC, elimine fi las y columnas vacías. La fi la 11 muestra el primer título cabecera, nos interesa los datos de cada título cabecera y en las vacías eliminaremos las columnas. 16 Seleccione la hoja Cierre CIC. 17 Seleccione la fi la 11. 18 Pulse la tecla [F5], Excel muestra el cuadro de diálogo Ir a, 19 Haga clic en el botón Especial, Excel muestra el cuadro de diálogo Ir a Especial. 20 Haga clic en el botón de opción Celdas en Blanco. 21 Haga clic en el botón Aceptar. 22 Pulse al combinación de teclas la combinación de teclas [Ctrl] + [-]. 23 Excel muestra el cuadro de diálogo Eliminar celdas, haga clic en el botón de opción Toda la Columna. 24 Haga clic en el botón Aceptar. Figura 2.13 Las fi las vacías se eliminaron, la lista de datos la usaremos más adelante. GESTIÓN Y METODOLOGÍA GESTIÓN DE COBRANZAS CON EXCEL 45 Ahora eliminar las fi las vacías, debemos estar seguros qué columna se va a seleccionar para no eliminar datos importantes. 25 Seleccione la Columna B. 26 Pulse la tecla [F5], Excel muestra el cuadro de diálogo Ir a, 27 Haga clic en el botón Especial, Excel muestra el cuadro de diálogo Ir a Especial. 28 Haga clic en el botón de opción Celdas en Blanco. 29 Haga clic en el botón Aceptar. 30 Pulse al combinación de teclas la combinación de teclas [Ctrl] + [-]. 31 Excel muestra el cuadro de diálogo Eliminar celdas, haga clic en el botón de opción Toda la fi la. 32 Haga clic en el botón Aceptar. Figura 2.14. Se puede observar en la fi la 11, rango A11:O11 no hay celdas vacías. Figura 2.15. Se eliminaron las fi las y columnas vacías. CONCEPTOS BÁSICOSCAP. 246 En la fi gura 2.15 se observa como los títulos cabeceras se repiten; se eliminarán los títulos cabeceras excepto el de la fi la 1. 33 Seleccione la celda L1, escriba un valor numérico (para este ejemplo escriba 7). 34 Seleccione la columna L, (para seleccionar las celdas que contienen valores constantes de tipo texto). 35 Pulse la tecla [F5], Excel muestra el cuadro de diálogo Ir a, 36 Haga clic en el botón Especial, Excel muestra el cuadro de diálogo Ir a Especial. 37 Haga clic en el botón de opción Constantes y solo active la casilla de verifi cación Texto. 38 Haga clic en el botón Aceptar. 39 Pulse la combinación de teclas [Ctrl] + [-]. 40 Excel muestra el cuadro de diálogo Eliminar celdas, haga clic en el botón de opción Toda la fi la. 41 Haga clic en el botón Aceptar. 42 Seleccione la celda L1 y escriba Demora. 43 Elimine la columna A. Aplicar formato a la lista de datos obtenida, en la columna I y J los valores son fechas pero están como tipo texto, se convertirá los textos en fechas. Seleccionar el rango I2:I17892 (la celdaI17892 es la última celda que contiene datos de la columna I). 44 Seleccione la celda I2. 45 Presione las teclas [Ctrl] + [Shift] + [↓] (Tecla direccional). 46 En el grupo Herramientas de datos de la fi cha Datos, haga clic en Texto en columnas. 47 En el paso 1 del Asistente para convertir texto en columnas, haga clic en Delimitados y luego en Siguiente. 48 En el paso 2, haga clic en Siguiente. Figura 2.16. Luego de realizar los 43 pasos tenemos una lista de datos. GESTIÓN Y METODOLOGÍA GESTIÓN DE COBRANZAS CON EXCEL 47 49 En el paso 3, en la sección Formato de los datos en columnas active el botón de opción Fecha y en el botón despliegue seleccione DMA (Día, Mes, Año). 50 Por último haga clic en el botón Finalizar. 51 Seleccione la celda J2. 52 Presione las teclas [Ctrl] + [Shift] + [↓] (Tecla direccional). 53 En el grupo Herramientas de datos de la fi cha Datos, haga clic en Texto en columnas. 54 En el paso 1 del Asistente para convertir texto en columnas, haga clic en Delimitados y luego en Siguiente. 55 En el paso 2, haga clic en Siguiente. 56 En el paso 3, en la sección Formato de los datos en columnas active el botón de opción Fecha y en el botón despliegue seleccionar DMA (Día, Mes, Año). 57 Por último haga clic en el botón Finalizar. Figura 2.17. Se convierte los valores de texto a valores de fechas. Figura 2.18. El valor que muestran las fechas es ##########, esto es porque la columna no es lo sufi cientemente ancha para mostrar los valores. CONCEPTOS BÁSICOSCAP. 248 58 Seleccione toda la hoja. 59 En el grupo Celdas de la fi cha Inicio, haga clic en el botón de comando Formato y luego seleccione Autojustar ancho de columna. Los valores de la columna M también se muestran con texto, y se debe convertir a número para realizar cálculos. 60 Seleccione la celda M2. 61 Presione las teclas [Ctrl] + [Shift] + [↓] (Tecla direccional). 62 En el grupo Herramientas de datos de la fi cha Datos, haga clic en Texto en columnas. 63 En el paso 1 del Asistente para convertir texto en columnas, haga clic en Siguiente. 64 En el paso 2, haga clic en Siguiente. 65 En el paso 3, haga clic en el botón Avanzadas. 66 Excel muestra el cuadro de diálogo Confi guración avanzada de importación de textos. 67 Para reconocer los datos numéricos, en Separador decimal seleccione coma y en separador de miles seleccione punto. 68 Haga clic en el botón Aceptar. Figura 2.21. Los valores de la columna M se muestran como valor � po texto y se convierten a valor numérico. Figura 2.19. Opciones para modifi car el ancho de una columna. Figura 2.20. Los valores de la columna I y J se muestran como valor � po fecha. GESTIÓN Y METODOLOGÍA GESTIÓN DE COBRANZAS CON EXCEL 49 69 En el paso 3, haga clic en el botón Finalizar. Una vez obtenido la lista de datos del deudor, se aplicará formato para mejorar su presentación. 70 Seleccione la celda A1 (puede seleccionar una celda de la lista de datos). 71 Pulse las teclas [Ctrl] + [E] (para seleccionar la lista de datos). 72 En el grupo Fuente de la fi cha Inicio, haga clic el inicializador de cuadro de diálogo. 73 Excel muestra el cuadro de diálogo Formato de celdas. Haga clic en la fi cha Bordes (para aplicar bordes externos e internos de la lista de datos). 74 Para bordes externos en la sección Estilo haga clic en estilo línea continua grueso. 75 En el cuadro despliegue seleccione el color rojo. 76 En la sección preestablecidos haga clic en Contorno. Figura 2.22. Los valores de la columna M se muestran como valor � po número. Figura 2.23. Inicializador de cuadro de diálogo también se encuentra en el grupo Alineación y Número. CONCEPTOS BÁSICOSCAP. 250 77 Para bordes internos en la sección Estilo haga clic en estilo línea continua delgada. 78 En el cuadro despliegue seleccione el color celeste. 79 En la sección preestablecidos haga clic en Interior. 80 Para fi nalizar haga clic en el botón Aceptar. Aplicar formato de Relleno 81 Seleccione el rango A1:N1. 82 En el grupo Fuente de la fi cha Inicio, haga clic el inicializador de cuadro de diálogo . 83 Excel muestra el cuadro de diálogo Formato de celdas. Haga clic en la fi cha Relleno. 84 Haga clic en un color. 85 Luego haga clic en el botón Aceptar. Figura 2.25 Formato de Relleno, también puede seleccionar efectos de relleno como tramas y sus diferentes es� los de tramas. Figura 2.24. Formato de bordes. GESTIÓN Y METODOLOGÍA GESTIÓN DE COBRANZAS CON EXCEL 51 Aplicar formato de Fuente 86 Seleccione el rango A1:N1. 87 En el grupo Fuente de la fi cha Inicio, haga clic el inicializador de cuadro de diálogo . 88 Excel muestra el cuadro de diálogo Formato de celdas. Haga clic en la fi cha Fuente. 89 En la sección Estilo, haga clic en Negrita. 90 En la sección Tamaño, haga clic en 12. 91 En el cuadro despliegue seleccione el color Azul. 92 Luego haga clic en el botón Aceptar. 93 En el grupo Mostrar de la fi cha Vista, haga clic para desactivar la casilla de verifi cación Líneas de cuadrícula. Después de aplicar formato el resultado es como se muestra en la fi gura 2.28: Figura 2.27. En el grupo Mostrar también puede ocultar la barra fórmula, � tulo cabeceras de fi la y columnas. Figura 2.26. Formato de Fuente. Figura 2.28. Lista de datos con formato. CONCEPTOS BÁSICOSCAP. 252 Para guardar el archivo con un nuevo nombre realizar los siguientes pasos: 1 Haga clic en la Ficha Archivo, 2 Luego haga clic en Guardar como. 3 Excel muestra el cuadro de diálogo Guardar como. 4 Escriba el nombre de archivo Cierre mayo 2011. 5 Elija unidad de disco o carpeta donde se guardará el archivo. 6 Para fi nalizar haga clic en el botón Guardar. • FÓRMULAS Las fórmulas son ecuaciones que pueden realizar cálculos, devolver información, manipular el contenido de otras celdas, comprobar condiciones, etc. Una fórmula siempre comienza con el signo igual (=). • UTILIZAR OPERADORES DE CÁLCULO EN FÓRMULAS Los operadores especifi can el tipo de cálculo que desea ejecutar en los elementos de una fórmula. Existe un orden predeterminado en el que tienen lugar los cálculos (que sigue las reglas matemáticas generales), pero puede cambiar este orden utilizando paréntesis. • TIPOS DE OPERADORES Existen cuatro tipos de operadores de cálculo: aritmético, comparación, concatenación de texto y referencia. Figura 2.29. Guardar como, permite guardar con un nuevo nombre, ubicación y � po de archivo. GESTIÓN Y METODOLOGÍA GESTIÓN DE COBRANZAS CON EXCEL 53 Operadores aritméticos Para ejecutar las operaciones matemáticas básicas como suma, resta, multiplicación o división, potencia o radicación. Operadores de comparación Se pueden comparar dos valores con los siguientes operadores. Cuando se comparan dos valores usando estos operadores, el resultado es un valor lógico: VERDADERO o FALSO. Operador de concatenación de texto Utilice la y comercial (&) para concatenar (unir) una o varias cadenas de texto con el fi n de generar un solo elemento de texto. Figura 2.30. Los operadores aritmé� cos son los enlaces entre varios argumentos. Figura 2.32. La celda C2 muestra el valor de la celda E1 y F1 separado por un espacio. Figura 2.31. Cuando se usa operadores de comparación solo se � enen como resultado valores lógicos. CONCEPTOS BÁSICOSCAP. 254 Operadores de referencia Combine rangos de celdas para los cálculos con los siguientes operadores: Orden en que Excel ejecuta las operaciones en las fórmulas En algunos casos, el orden en el que se ejecuta el cálculo puede afectar al valor devuelto de la fórmula. Por tanto, es importante comprender cómo se determina el orden y cómo puede cambiar el orden para obtener los resultados deseados. Las fórmulas calculan los valores en un orden específi co. Las fórmulas de Excel siempre comienzan por un signo igual (=). Excel interpreta los caracteres detrás del signo igual como una fórmula. Tras el signo igual están los elementos que se van a calcular; por ejemplo, constantes
Compartir