Descarga la aplicación para disfrutar aún más
Vista previa del material en texto
DEDICATORIA A mi esposa Marisol García Salgado y a mis hijos: Jimena, Paloma y Rogelio Yael Martínez García, por ser mi inspiración para buscar nuevos objetivos en la vida. Un agradecimiento especial al Colegio de Contadores Públicos del Valle de Toluca, A.C., por el apoyo incondicional para la publicación de este libro. El AUTOR Rogelio Martínez Santana es Licenciado en Contaduría por la Facultad de Contaduría y Administración (UAEM) y Contador Público Certificado con Maestría en Impuestos. Cuenta con estudios de posgrado como: Maestría en Impuestos (CEO), Diplomado en Planeación Fiscal, Diplomado en Excel, Certificado en Excel por Microsoft Corporation, Experiencia en Contabilidad, Fiscal, Auditoría y Excel. Ha sido expositor de cursos en el Área Fiscal, de cursos de Excel, encargado de auditoría, contador general y auditor externo e interno de diferentes empresas. Exvicepresidente en el Área de Capacitación y Exvicepresidente del Sector Externo del Colegio de Contadores Públicos del Valle de Toluca, A.C., Exvicepresidente de la Comisión de Normas de Información Financiera. Vicepresidente del Sector Externo del Colegio de Contadores Públicos del Valle de Toluca, A.C. Actualmente es Director General de Jiparo Consultores, S.C., y socio activo del Colegio de Contadores Públicos del Valle de Toluca, A.C. Introducción Quiero agradecer a los lectores esta oportunidad que me da de colaborar directamente con ellos y compartir una herramienta de trabajo necesaria para quienes nos dedicamos a la contaduría y a la administración. La intención de este libro es que los contadores y administradores cuenten con una herramienta que les permita minimizar la carga administrativa, así como hacerla productiva. Este interés surgió a raíz de la necesidad de obtener elementos que optimizaran mi trabajo como contador, y al ser Excel una de la herramientas más utilizadas en esta profesión, encontré en ella una solución de gran importancia. Excel 2016 En la actualidad, por un lado la tecnología nos está rebasando en muchas áreas, es decir las autoridades fiscales utilizan todo lo que está a su alcance para recaudar y realizar operaciones que les permitan hacerlo en poco tiempo; y por otra parte, con la carga administrativa que nos imponen, también deberíamos aprovechar la tecnología como herramienta. Para este propósito, Excel 2016 trae cambios importantes, respecto a las versiones anteriores, pero eso no significa que no sea compatible con dichas versiones. Necesidades y opciones de este libro Cuando necesitamos terminar ciertos trabajos en poco tiempo, quisiéramos contar con una solución inmediata. Como contadores y administradores, normalmente nuestra herramienta de tecnológica es la hoja de cálculo (Excel). Con las funciones y opciones que ofrece este libro, ustedes encontrarán el apoyo que requieren en el ejercicio de su profesión. Al internarse en el contenido advertirán trucos o técnicas que quizá conozcan y que posiblemente no hayan aplicado antes, además de analizar funciones, rangos, tablas dinámicas (unas de mis favoritas) y de analizar una hipótesis, así como otras aplicaciones útiles para desarrollar papeles de trabajo en un menor tiempo. Uno de los cambios importantes que se implementaron desde la versión 2007, fue que para guardar un libro de Excel que no contenga macros, debe hacerse con la extensión XLSX; si este libro contiene macros, se guardará con la extensión XLSM. Se guardan con esta extensión, al momento de seleccionar la opción Guardar como, ya que nos habilita las opciones de libro de Excel, libro habilitado para macros y libro de Excel 97-2003. La diferencia consiste en que al guardarlo como libro de Excel, lo hará de manera normal, sin macros, con la desventaja de que en caso de contener macros, al guardarse como libro de Excel, se perderá la información generada en VBA. Una recomendación importante es que si estoy utilizando Excel 2016 es importante que se tenga instalado en nuestra computadora el Windows 10, debido a que las versiones son las más actuales; en caso de utilizar una versión anterior de Windows, esta no contendrá todos los complementos que se necesitan y posiblemente no se vean correctamente algunas aplicaciones. Lo más importante de los retos que te pone la vida, no es demostrárselo a nadie, sino demostrarte a ti mismo de qué eres capaz. Recordemos aquel dicho muy famoso: “el hambre me tira, pero el orgullo me levanta”. Inicio de Excel Antes de comenzar, por favor descargue el material en la siguiente liga: http://jiparo.com.mx/Archivo_Disco.zip?desgargar=Descargar Excel es la hoja de cálculo más usada en todo el mundo, tanto así que todos los programas de cómputo enfocados en cuestiones numéricas y en las áreas contables, utilizan esta herramienta como base para la determinación de ciertos procedimientos. La cualidad de esta es su versatilidad y por ser parte de Microsoft Office ya es garantía. Además, tiene una vinculación universal con las otras aplicaciones del mismo Microsoft Office, lo cual significa que en el mismo uso de una hoja de cálculo se pueden abrir presentaciones con gráficos, imágenes, texto, Web, etcétera. Ahora bien, lo primero que debemos entender son los principales elementos de la hoja de cálculo, características principales que se muestran a continuación: http://jiparo.com.mx/Archivo_Disco.zip?desgargar=Descargar Estas son algunas de las aplicaciones directas a una hoja de cálculo de un libro de Excel. Nos tardaríamos una eternidad en explicar cada una de ellas; conforme vayamos avanzando en el contenido de este libro, veremos que hay otras aplicaciones de gran utilidad y que minimizan la carga administrativa. Hay muchas combinaciones, tanto de teclas como de funciones, y otras aplicaciones que más adelante explicaremos. Uso del teclado Antes de empezar a utilizar cualquier herramienta que nos facilita Excel, es recomendable aprender a manejar el teclado con aplicaciones directas a esta maravillosa hoja de cálculo. Con este método se minimiza el tiempo de manera considerable. En ocasiones queremos aplicar funciones o vínculos que nos permitan trabajar más rápido dentro de nuestros papeles de trabajo. Con la entrada de la versión 2016 se van minimizando muchas aplicaciones, debido a que ya contiene aplicaciones más directas. Lo recomendable para aprender a manejar el teclado es practicar todo el tiempo con este y olvidarnos por un tiempo del ratón, ya que no utilizarlo nos obliga a no ser dependientes de él. A continuación se muestra un cuadro con algunas opciones para aplicar: Para windows Algunas acciones de Windows están limitadas o habilitadas, dependiendo de la versión que tenga instalada en su computadora. Como prueba del teclado, en una hoja activa de Excel ponga su nombre, después ubíquese en la celda siguiente con dirección hacia adelante y presione CTRL + D; y verá que copia y pega el concepto. Lo puede hacer también hacia abajo, pero con CTRL + J. Aplicación de listas personalizadas En ocasiones cuando necesitamos tener a la mano una serie de datos que aplicamos constantemente, lo que hacemos es anotar uno por uno. Por ejemplo, si soy contador público y necesito una lista de las declaraciones que debo realizar por mes, anoto cada una en una fila o columna, pero la pregunta es ¿cuánto tiempo invierto en hacer una lista por cliente?, ahora imagínese si tengo 100. La opción para este caso son las listas personalizadas. Para realizar una lista personalizada lo primero es ubicar la pestaña de archivo que se encuentra en el extremo superior izquierdo de su pantalla. Dé clic en opciones, se desplegará una lista de opciones. Dé clic en Avanzadas y nos vamos hasta el final de esta opción, en General Opciones Web aparece un recuadrocon el nombre de Modificar listas personalizadas (vea siguiente figura): Después de identificarla, le damos clic y se abrirá un recuadro como el que sigue: Hay dos opciones para generar estas listas. Una es directamente dar clic en Agregar, que habilita el recuadro de entradas de lista, ahí vamos anotando los conceptos que deseamos aparezcan en la hoja de cálculo. Realicemos el siguiente ejemplo: supongamos que necesitamos ubicar dentro de esta lista los conceptos de las declaraciones comunes: • Pago provisional del ISR • Pago provisional del IVA • DIOT Anotamos un concepto y le damos enter, anotamos el que sigue y le damos enter, y así sucesivamente, hasta terminar con los conceptos que deseamos. Al terminar de anotar los conceptos, le damos agregar de nuevo y los conceptos que anotamos automáticamente se pasan al cuadro de listas personalizadas. Inmediatamente le damos clic en aceptar y otra vez le damos clic en aceptar —aparentemente la hoja de Excel no hizo nada—, pero ahora veremos el resultado. Posiciónese en una celda de la hoja activa de Excel y escriba uno de los conceptos que anotó en su lista personalizada; después ubique el ratón en la parte inferior derecha, en la esquina de la celda activa y arrastre el ratón hacia adelante o hacia abajo, verá cómo se van incrustando cada uno de los conceptos. Después, suelte el ratón y los conceptos se pegarán automáticamente en las celdas correspondientes. La otra opción es colocar en una hoja de Excel los conceptos que deseamos aparezcan en nuestra lista personalizada, ya sea en forma horizontal o vertical, aunque recomiendo que siempre sea en forma vertical. Vamos a suponer que queremos que aparezcan los siguientes conceptos en nuestra lista personalizada: • Contabilidad • Auditoría • Impuestos • Costos • Finanzas Los ponemos en nuestra hoja de Excel, luego damos clic en la pestaña Archivo> Opciones> Avanzadas> General> Opciones Web> Modificar listas personalizadas> Importar (después de darle clic en importar, le damos clic en el botón que está al lado izquierdo del icono de importar y seleccionamos los conceptos en la hoja de Excel)> Agregar; después le damos otra vez clic en Importar> aceptar> aceptar. Para probarlo, hay que anotar un concepto de los que consideramos y hacer el mismo procedimiento que en la opción anterior (colocamos el ratón en la parte inferior derecha, lo arrastramos hacia adelante o hacia abajo, y los conceptos se colocan en las celdas seleccionadas). Alternativas para trabajar con hojas de Excel Cuando trabajamos con las hojas de Excel es importante manipular ciertas opciones para minimizar la carga administrativa. Esto se debe a que la mayoría de las veces necesitamos trabajar con un archivo, pero tenemos que ver dos hojas de este al mismo tiempo, como cuando vamos a modificar en una hoja y ver el resultado en otra. Por ejemplo, si manejamos nuestras balanzas y estados financieros en Excel, y necesitamos hacer modificaciones en la balanza, pero también queremos ver el resultado en nuestro balance, se nos complica verlos al mismo tiempo, para eso existe una alternativa; veamos un pequeño procedimiento: Posiciónese en un libro de Excel que tenga datos en hojas vinculadas, después ubíquese en la hoja donde se modifican los datos. En la pestaña Vista> Nueva ventana, en automático se abre un libro con el mismo nombre del que tiene abierto, pero con la diferencia que el primero se convierte con terminación “:1” y el nuevo con terminación “:2”. Ahora, para que esta herramienta pueda ser efectiva, en la misma pestaña de Vista, pero en el icono de Organizar todo, seleccione una de las alternativas que nos da (Mosaico, Horizontal, Vertical, Cascada), después reconocerá que hay dos ventanas visibles y que puede trabajar en una con la hoja en que desee modificar los datos, y en la otra puede ubicarse en la hoja en que quiera ver el resultado. Podemos guardar los cambios que hagamos sin ningún problema; sin embargo, cuando cerremos una de las ventanas —porque ya no se necesite—, deberemos cerrar la que aparece con terminación “:2”. Inmovilizar Otra manera de trabajar con las hojas de Excel es utilizar la herramienta Inmovilizar. Por medio de esta podemos inmovilizar desde una fila o una columna hasta los paneles con los que estemos trabajando (es muy parecido a dividir). Para llevarlo a cabo, nos posicionamos en la parte inferior de una fila que no queramos que se mueva, con el propósito de ver los conceptos que tenemos. Veamos un ejemplo: supongamos que en una hoja de Excel aparecen los meses de todo el año en una fila y queremos ver las columnas en las que están ubicados cada uno de los meses, y hacia abajo tenemos una lista de números por colocar en cada mes. Si la vista de la pantalla me los muestra todos, no hay problema, pero cuando rebaso esa vista empiezan los problemas. La solución estriba en cómo utilizar Excel, seleccionamos: Vista> Inmovilizar> Inmovilizar fila superior, y después podemos trabajar con las celdas de abajo, sin que la fila superior se mueva. El mismo procedimiento se lleva a cabo con Inmovilizar paneles o Inmovilizar columna. Lo mismo se puede hacer con la opción de dividir, pero esta solo separa la vista de la pantalla a partir de la fila y la columna que se hayan seleccionado Vista> Dividir. Cuando queremos movilizar los paneles de nuevo o quitar la división aplicada, seleccionamos Vista> Inmovilizar> Movilizar paneles. Si queremos quitar la división, seleccionamos Vista> Dividir. Si deseamos que una hoja no se encuentre visible dentro del libro, pero sin que esta se tenga que eliminar, hay dos opciones: una es Vista> Ocultar, la otra es posicionarse en la hoja que deseamos ocultar, dar clic derecho al ratón y seleccionar Ocultar. Si después deseamos ver la hoja que ocultamos, solo aplicamos lo siguiente: Vista> Mostrar. Diseño de página A menudo, como contadores o administradores, necesitamos trabajar con hojas de Excel que contengan el número o números de página, el logo de nuestra empresa o despacho, el archivo en que está guardado, la fecha y la hora actual, etcétera. Hay una manera de hacer que Excel trabaje para nosotros y no nosotros para Excel: seleccionamos Vista> Vistas de libro> Diseño de página, en automático se habilitará una división de hojas con encabezado y pie de página. Si pone el ratón en la parte superior de la hoja, se dará cuenta de que se activa el encabezado y este, a su vez, nos habilita tres recuadros en los cuales podemos adherir las opciones que nos permite Excel. Para trabajar con estas opciones es indispensable que el ratón se encuentre en estos recuadros. Al estar dentro de cualquiera de estos recuadros, en la parte superior de la ventana de Excel se habilita una nueva pestaña con el nombre de Herramientas para encabezado y pie de página, debajo de esta se encuentra la pestaña de Diseño, que nos va a permitir continuar con nuestro trabajo. Si le damos clic en otra parte de la hoja, se deshabilita esta pestaña; para regresar a ella hay que dar clic en el encabezado o pie de página que tiene la hoja activa y después seleccionar Diseño. Cuando esté dentro de uno de los recuadros del encabezado, se dará cuenta de que en la parte de la cinta de opciones aparecen unos iconos con los nombres: número de la página, número de páginas, fecha actual, hora actual, etcétera. El procedimiento va a depender de lo que se quiera integrar a la hoja; después de haber seleccionado lo que necesita, dé clic en cualquier parte de la hoja y verá cómo se incrustan las opciones seleccionadas. Después de insertar una opción es importante dar enter para separar cada una de las incrustaciones hechas. Si insertamos una imagen, las imágenes que se vana insertar deben seleccionarse de Internet o de la computadora en la que estamos trabajando; si elegimos la primera opción es indispensable estar conectados a Internet, y si tomamos la segunda opción, hay que darle clic Desde un archivo> Examinar— la imagen aparecerá del tamaño de la hoja; si lo que queremos es que aparezca solo en el encabezado, para que la imagen se haga pequeña, le damos clic al icono de Dar formato a Imagen (dentro de la pestaña de Diseño) y donde dice alto le damos al número 1, y el tamaño se va a minimizar. Nota: si queremos ver la pantalla completa solo seleccionamos Vista> Vistas de libro> Pantalla completa. Impresión de hojas A veces cuando queremos imprimir nos cuesta trabajo dar margen o separar las hojas a imprimir, y lo que normalmente hacemos es que le damos diseño de página> Establecer Área de impresión; pero hay otra forma de aplicar una impresión de hojas, sobre todo cuando el área seleccionada nos la divide en varias hojas solo seleccionamos Vista> Vistas de libro> Ver salto de página en automático se marcan los márgenes de cada una de las hojas con una línea azul, estas líneas nos van a permitir dar el margen necesario para decidir hasta qué rubro deseamos que se imprima y nos lo va a respetar, además nos da el número de página que le corresponde a cada una. En donde se marca la línea azul ponemos el ratón y con este podemos ampliar o disminuir el área de impresión, con la facilidad de insertar una línea que me modifique aún más el margen de impresión, solo nos posicionamos con el ratón en la columna o fila en que deseamos hacer la modificación y le damos clic derecho> Insertar. Nota: si deseamos ver la hoja de Excel en forma normal y que nos quite el formato actual de Ver salto de Página, solo seleccionamos Vista> Vistas de libro> Normal. Cambiar nombre y color de hoja Cuando estamos trabajando con varias hojas de Excel o con un archivo en específico, lo importante es identificar cada una de mis hojas para aplicar mejores métodos y/o distinguir el contenido que tiene cada una de estas. Cuando abrimos un libro nuevo de Excel, por default me da una hoja, pero yo puedo agregar más, ya sea con las teclas SHIFT + F11 o con el botón de agregar hoja (es el que aparece al lado derecho de la Hoja con el signo +). Ahora vamos a suponer que la hoja 1 quiero que se llame: Contabilidades, la hoja 2: Auditorías y la hoja 3: Personal, para hacer esto solo necesitamos poner el ratón encima del concepto de cada hoja y dar clic derecho, al hacerlo aparece un lista de opciones, le damos clic en cambiar nombre; pero, si además queremos identificarla con un color diferente a cada hoja, hacemos lo mismo, nos posicionamos sobre el nombre de cada hoja, le damos clic derecho y le damos clic en color etiqueta. Hay diferentes tipos de color y si no nos gusta ninguno de los que tenemos a primera vista, seleccionamos Más colores y se despliegan otras opciones. Nota: si queremos eliminar una hoja solo nos posicionamos con el ratón en la hoja correcta y le damos clic derecho y seleccionamos eliminar hoja. Manejo de celdas y columnas Cuando nos adentramos un poco en Excel, normalmente tenemos la inquietud de saber cómo ocultar filas y columnas y cómo trabajar con las celdas activas, lo importante de esto es minimizar la carga administrativa y tener los datos a la mano cuando los necesitemos. Comencemos a ver cómo cambiar el contenido de una celda a otra, independientemente de que contenga una fórmula, sin que altere el resultado. Para hacer este movimiento hay dos opciones: una es utilizando el teclado, nos posicionamos en la celda que tiene la información y le damos CTRL + X, nos trasladamos a la nueva celda en que deseamos poner la información y le damos enter; la otra forma es posicionarnos en la celda que contenga la información y colocamos el ratón en el contorno de la celda activa, en el momento en que se forme una figura en forma de cruz, le damos clic y sin soltar el ratón, lo arrastramos hasta la nueva celda en que se vaya a poner la información. Insertar filas y columnas Cuando deseamos insertar filas y/o columnas, normalmente invertimos mucho tiempo utilizando el ratón, aunque a veces es indispensable. Veamos las dos opciones: 1. Ya sea que nos posicionemos entre la columna o en la fila que deseamos insertar (las filas están clasificadas con números y las columnas con letras), damos clic derecho al ratón y le damos insertar, en automático agregará una fila o una columna, dependiendo de lo que necesitemos. 2. La otra forma es utilizando solo el teclado, si quiero seleccionar una fila, presiono SHIFT + Barra espaciadora, y si quiero seleccionar una columna, presiono CTRL + Barra espaciadora, si quiero agregar una fila o columna, solo presiono CTRL + el signo de + y en automático se agregará. Eliminar filas y columnas Al igual que en el proceso anterior, para eliminar filas y/o columnas, hay dos formas de hacerlo: 1. Nos posicionamos en la fila o columna que deseamos eliminar, le damos clic derecho al ratón y seleccionamos eliminar. 2. La otra forma es con el uso del teclado, si quiero seleccionar una fila, presiono SHIFT + Barra espaciadora, si quiero seleccionar una columna, presiono CTRL + Barra espaciadora, si quiero eliminar una fila o columna, solo presiono CTRL + el signo de - y en automático se eliminará. Agrupar filas y columnas Cuando estamos trabajando con hojas que van a contener un gran número de filas o columnas y en las cuales necesitamos ocultar algunas, debido a lo extensa que puede ser la información que necesitamos dentro de la misma hoja, lo que hacemos es seleccionar filas o columnas, después dar clic derecho al ratón y seleccionar ocultar, pero existe la desventaja de que después hay que seleccionar entre las dos filas o entre las dos columnas, dentro de las cuales están ocultas, ya sean las filas o columnas, y darle mostrar; procedimiento en el que se invierte demasiado tiempo. Para mí, la mejor opción es seleccionar las filas o las columnas que deseamos ocultar, después Datos> Esquema> Agrupar, de inmediato en la parte superior de las columnas o al lado izquierdo de las filas se va a insertar una línea con un signo de (-), el cual significa que al darle un clic se van a ocultar las filas, y cuando estas se ocultan se convierte la línea en un signo de (+) esto significa que al darle clic se van a mostrar las filas o columnas. Como se ve en la figura anterior, al agrupar unas filas o columnas, se puede agrupar dentro de esa misma, otro bloque de filas o columnas. Ahora si queremos desagrupar las filas, porque ya no queremos que aparezca así o porque ya no lo necesitamos, solo seleccionamos las filas o columnas que agrupamos Datos> Esquema> Desagrupar, de esta manera nuestra hoja tendrá la misma vista que al inicio. Celdas personalizadas Es importante conocer la manera de personalizar las celdas de mi hoja de cálculo ¿qué pasa cuando dentro de una celda activa necesito poner, tanto el importe como el concepto?, y cuando hacemos esto, en una suma o resta el resultado que nos arroja es un error, aquí la pregunta es ¿se puede poner el importe y el concepto en la misma celda? La respuesta es sí, el procedimiento es el siguiente: supongamos que en una celda tenemos importe, IVA y Subtotal, y necesitamos poner: Importe 10,000.00, IVA 1,600 y Subtotal 11,600.00, para verificar que lo que estamos haciendo es correcto, abra un libro nuevo; para hacerlo, en mi barra de herramientas aparece un icono blanco en forma de hoja, le damos clic. Ya que abrimos el nuevo libro en la celda B5 ponemos 10,000.00, en la celda B6 ponemos 1,600 y en la celda B7 sumamos las celdas B5 + B6. Ahora nos ubicamos en la celdaB5 abrimos formato de celdas, ya sea con clic derecho en el ratón o presione CTRL + 1, nos ubicamos en la pestaña de número y seleccionamos Personalizada. Algo muy importante que debemos saber es que cuando queremos anotar un texto dentro de una fórmula o en formato, este debe ir entre comillas; una vez que estamos en personalizada nos aparece un concepto de Estándar y una lista de formatos, lo que debemos hacer es seleccionar el primer cero. Después de haber seleccionado el primer cero, le damos clic al lado izquierdo de este y luego abrimos comillas, dentro de estas ponemos el importe y las cerramos, después le damos un espacio. Inmediatamente le damos aceptar. El resultado es el siguiente: Si se dan cuenta, en la parte de Barra de Fórmulas aparece solo el importe de 10,000 y no afecta en nada el texto ni la suma, puede hacer el mismo procedimiento para el IVA y el Subtotal. Personalizar fecha Veamos un ejemplo para personalizar el formato de fecha en una celda determinada. Primero vamos a abrir un libro nuevo y poner una fecha actual en forma tradicional en la celda B1, 31/01/2016, al poner esta fecha, podemos optar por considerar los formatos que por default nos maneja Excel, pero vamos a suponer que ninguno de esos nos gusta y quisiéramos que aparezca de la siguiente manera sábado 31 de diciembre de 2016; es similar al ejemplo anterior, ya que tenemos la fecha en la celda B1, abrimos formato de celdas (con CTRL + 1) y en la pestaña de Número nos vamos hasta personalizada, después borramos el texto que tiene en el recuadro tipo y ponemos la d y después la repetimos. Si lo notaron, en cuanto ponemos la primer d me aparece el número del día, si le ponemos dddd nos aparece el día con letra, luego le ponemos otra vez dd y nos aparece de nuevo el número de día. Si recuerdan en párrafos anteriores habíamos comentado que para escribir un texto dentro de una fórmula o formato, tenemos que hacerlo entrecomillado, así que después de que me aparece el día con letra y con número, abrimos comillas y ponemos la palabra de (“de”), así se va formando el formato y este tipo de personalización se ve en la parte superior del recuadro, donde dice muestra. Ahora, para culminar ponemos mmmm y esto hace que aparezca el mes en texto, después abrimos comillas y ponemos la palabra de (“de”), enseguida ponemos aaaa, esto hará que aparezca el año en número, para finalizar le damos aceptar y nuestro formato está listo. Nota: en lo que se refiere a fechas: la letra d significa día, la letra m significa mes y la letra a significa año. Si ponemos una fecha en la celda donde le dimos el formato de celda, veremos que ahora aparece, pero con letra y número. La opción de formato de celda tiene una gran variedad de opciones para trabajar con ellas y darle una mejor imagen a nuestro trabajo. Hay más opciones dentro del formato de celdas, algunas de ellas son: • Alineación. El formato que le podemos dar a nuestras celdas, también se puede hacer que el contenido de la celda de un giro de 90 grados; o bien, combinar celdas, dar sangría a un texto, centrar, rellenar, etcétera. • Fuente. Muchas de las opciones que vienen aquí podemos encontrarlas en la pestaña inicio de Excel, pero es conveniente conocerlas, ya que podemos optar por el tipo de letra, color, tamaño, efectos, etcétera. • Bordes. Es muy importante el contorno que deseamos que aparezca en el formato de nuestra hoja de trabajo. En esta pestaña podemos encontrar diferentes opciones y/o combinaciones a elegir. • Relleno. En ocasiones queremos dar un color diferente a nuestros formatos, ya sea como contenido o como fuente, y en esta pestaña, podemos darle una vista diferente a nuestro trabajo. En caso de que no nos guste alguno de los colores que están predeterminados, tenemos la opción de elegir más colores o tramas. • Proteger. Esta alternativa permite proteger el contenido de nuestro trabajo; sin embargo, este tema lo trataremos en apartados posteriores. Separación de conceptos en una sola celda Cuando tengo un formato definido en una hoja de Excel y necesito agregar en una celda un concepto diferente al que se tiene, es importante utilizar el formato de celdas aplicando los bordes. Supongamos que en una hoja de cálculo tenemos información parecida a la de la siguiente imagen: Aquí el problema equivale a decir que la parte superior se refiere al ejercicio y la parte inferior a los meses ¿complicado no?, pero muchas cosas se puede resolver con formato de celdas. Para esto, en la celda que está marcada (B8), vamos a poner lo siguiente AÑO, ahora sin salirnos de la celda presionamos las siguientes teclas ALT + ENTER, en automático en la celda nos va poner la escritura como si estuviéramos en otra fila, pero en realidad estamos dentro de la misma celda, ahora ponemos MES, le damos enter y nuestra imagen muestra lo siguiente: Todavía se ve incompleto nuestro trabajo, pero lo vamos a corregir de la siguiente manera, regresamos a la celda, presionamos la tecla F2, y nos posicionamos justo atrás de la palabra AÑO, ahora le damos varios espacios con la barra espaciadora, sin salirnos del contorno de la celda y le damos enter. Aunque ya se ve separado nuestro formato, todavía nos falta darle un poco de formalidad, para ello regresamos de nuevo a la celda y le damos Formato de Celdas o presionamos la tecla CTRL + 1, nos vamos a bordes y seleccionamos una diagonal como se ve en la siguiente imagen: Le damos aceptar y veremos que el resultado es maravilloso. Creación de rangos Antes de ver cómo se crean los rangos en Excel es importante comentar que cuando utilizamos esta hoja de cálculo, lo que deseamos es que haya opciones para aplicarlas en todo un libro de Excel, sin que se tenga que estar modificando dato por dato. Supongamos que tenemos el costo de un producto para aplicarlo a diferentes formatos integrados en nuestro libro de trabajo; si es uno o cinco, quiero creer que no hay tanto problema, pero me he encontrado casos en que tienen que modificar hasta 100 formatos, ¡imagínense hacer esto a diario en diferentes libros!, calculen el tiempo invertido y la verdad es bastante, por ello veamos una opción de Excel muy interesante: Rangos. Cómo crear rangos Empecemos con uno sencillo, abran un libro nuevo de Excel y en la celda B5 pongan el concepto de Prontuario, en la celda C5 pongan la cantidad de 640 (vamos a suponer que es costo de un prontuario), pero además supongamos que este importe lo debemos repetir varias veces en diferentes hojas de cálculo del mismo libro de Excel (demasiado tiempo invertido si lo hacemos de forma manual), ubíquense en la celda C5, ahora en el cuadro de nombres ponemos Costo y le damos enter, de aquí en adelante la celda C5 va a ser el rango Costo, para verificar que el rango es correcto, vayan a cualquier hoja de ese mismo libro de Excel y pongan =100 X Costo y verán que el resultado es 64,000. Como se puede apreciar, el rango es una gran ayuda. Identificar un rango es sencillo, solo empiecen a escribir el nombre y aparecerá en forma de función en un icono blanco. Crear un solo rango no fue tan complicado, pero a veces tenemos la necesidad de crear una lista de conceptos con importes en específico —en la que sea necesario considerar cada columna o cada fila como un rango independiente, o una serie de columnas y filas que se puedan considerar como un solo rango—, para hacerlo usamos un procedimiento similar al anterior ejemplo, con la diferencia de que podemos hacerlo con el uso del teclado. Abran un libro nuevo y en la celda A1:A5 pongan Contabilidad, Auditoría, Costos, Impuestos, Finanzas, ahora en las celdas B1:B5 pongan los costos que quieran,por ejemplo: $5,000, $6,000, $7,000, $4,000, $10,000, después seleccionen de A1:B5 y presionamos las siguientes teclas al mismo tiempo CTRL + SHIFT + F3, les va a aparecer un recuadro que dice crear nombres a partir de selección y en ese hay cuatro opciones Fila superior, Columna izquierda, Fila inferior, Columna derecha, como su mismo nombre lo dice, el rango que va a considerar dependerá mucho de lo que seleccionemos; en este caso, vamos a seleccionar Columna izquierda y aceptar, ya que los conceptos en efecto están en la columna izquierda y esos son los que nos va a considerar. A partir de ahí los importes de las celdas siguientes tomarán el nombre del rango que ha creado. Después de esto, para ver cada uno de los rangos creados solo damos clic en el cuadro de nombres y se despliega una lista de los que están creados, si seleccionamos uno nos mandará a la celda donde esté creado. Cómo editar y eliminar rangos Ya que hemos creado nuestros rangos es importante saber cómo editar o eliminar alguno, pues en ocasiones generamos muchos, pero conforme vamos avanzando en nuestros trabajos nos damos cuenta de que algunos son innecesarios o de que el rango realizado con anterioridad se tiene que modificar. Para visualizar el editor de Rangos hay que seguir estos pasos: oprimir las siguientes teclas CTRL + F3, y de inmediato se visualizará un recuadro, como el de la siguiente figura: Al aplicar esta opción, nos da tres alternativas: Nuevo, Editar y Eliminar. Es recomendable practicar con estas alternativas, las cuales se pueden utilizar de la siguiente manera: • Nuevo. Puede crear un nuevo rango, al seleccionar un área diferente o nueva. • Editar. Puede modificar un rango ya creado y modificar el área correspondiente. • Eliminar. Puede eliminar un rango creado con anterioridad. Rellenar filas Para rellenar ciertas filas y columnas con números o tomando en cuenta fechas, existen diferentes maneras de hacerlo, por ejemplo, si necesitamos poner los números del 1 al 1,000, un número por cada fila o por cada columna (de forma manual es mucho tiempo invertido) y lo que hacemos es poner de uno, dos, tres, etc., o ponemos el número uno y en la siguiente celda, ponemos el signo = y luego le ponemos +1, esto hace que se vayan sumando los números e incrementando de uno en uno. Sin embargo, hay otra solución, ejemplo: coloquen el número 1 en la celda A1, ahora colóquense en la misma celda A1 y en la parte inferior derecha de la celda activa, hay un cuadro muy pequeño que se ve como separado del contorno de todo el marco, coloquen el ratón en ese punto específico y este se verá en forma de cruz, ahora presionen el ratón y sin soltarlo arrástrenlo hasta la celda A500, si se dan cuenta se llenó de puros números 1, pero también del lado derecho de la última celda seleccionada hay un cuadro pequeño con el signo de +, den clic normal y desplegará una serie de opciones (copiar celda, serie de relleno, rellenar formato solo, rellenar sin formato), seleccionamos Serie de relleno y en automático se van a rellenar las celdas en forma consecutiva. Hay otra manera de rellenar los números, hagan la prueba poniendo el número 1 en la celda B1, después ubíquense de nuevo en la misma celda B1, y en la parte inferior derecha en la esquina de la celda presionen un clic normal en el ratón, al mismo tiempo la tecla CTRL, sin soltar el ratón y la tecla mencionada arrástrenlo hasta la celda B15, verán cómo se rellena los números en forma consecutiva. Para rellenar conceptos o fechas hay que tomar en cuenta la opción de rellenar que se encuentra en la ficha Inicio en las opciones de Celdas. Por ejemplo, supongamos que necesitamos poner los días laborables de un mes en específico, hacerlo uno por uno sería demasiado complejo, pero la opción de rellenar, antes mencionada, nos permite realizarlo (la única problemática es que considera todos los días de la semana y no separa los días festivos), para ver el efecto, en la celda D1 pongan el número 1, luego seleccionen las celdas de la D1 a la celda D25, ahora den un clic en la opción Rellenar y se desplegará una serie de opciones, donde seleccionamos Series> Columnas> Cronológica> Día laborable> Aceptar. De inmediato se llenará de los números del mes actual, sin considerar sábados ni domingos. Así como esta, pueden hacerse las otras opciones que nos permite la alternativa de Series. Manejo de vínculos e hipervínculos Si queremos trabajar con diferentes formatos, hojas de trabajo o libros con distinto contenido, pero relacionados al que estamos elaborando o modificando, en cierto momento es importante tener una alternativa a la mano, y lo mismo sucede cuando necesitamos ver información de un solo cliente o proveedor, para ello podemos utilizar las opciones de Vínculos e Hipervínculos. Empecemos con la opción de Vínculos, en una hoja de Excel, en un libro nuevo, vamos a poner conceptos de cliente y/o proveedor, después en otra columna o fila vamos a extraer información de otro libro que contenga datos que necesitemos para elaborar este papel de trabajo nuevo (para extraer información solo abrimos el libro donde esté la información que necesitamos, luego nos posicionamos en la celda o columna del libro nuevo donde vamos a plasmarla, ponemos el signo igual, después regresamos al libro donde está la información que queremos, lo seleccionamos con el ratón y le damos enter), al tener esta información de otro libro, al inicio de cualquier sesión que iniciemos con este nos va pedir que actualicemos vínculos —a veces es un poco molesto— para eliminarlo hacemos lo siguiente: Datos> Conexiones> Editar vínculos, al hacerlo aparecerá un recuadro que nos pide Modificar vínculos en el cual nos da cinco opciones, le damos clic en Romper vínculo, y de inmediato nos aparece otro recuadro en el que nos menciona la ruptura permanente de los vínculos, le damos clic en Romper vínculos, y la información cambia de tener todos los datos del libro donde extrajimos la información a formato general. Nota: es importante comentar que al realizar esta acción se perderá la vinculación con el libro del cual extrajimos dicha información. Crear hipervínculos Cuando necesitamos manejar información relacionada entre sí, es complejo estar en una hoja, celda o libro, y para trasladarnos al otro libro u hoja de donde requerimos información para completar nuestro trabajo, muchas veces lo hacemos con el ratón, una buena opción, pero demasiado tardado; en otras ocasiones utilizamos las teclas de avanzar página y CTRL, pero no siempre nos lleva al lugar que necesitamos. Por ello existe la opción de Hipervínculos. Para habilitar esta herramienta es importante tener abierto un libro de Excel con información relacionada, después hay dos opciones para habilitar los Hipervínculos: a) Clic derecho del ratón> Hipervínculos. b) Pestaña Insertar> Hipervínculos. Ahora vamos a suponer que estamos en la hoja 1 y necesitamos trasladarnos a la hoja 2, en la celda B4; para hacer esto nos posicionamos en la hoja 1, en la celda que queramos, insertamos Hipervínculo, seleccionamos Lugar de este documento, después Hoja 2 o la hoja a la que deseamos ir y en la parte superior (escriba la referencia de la celda), ponemos B4. Al terminar le damos aceptar en la celda que seleccionamos y aplicamos el hipervínculo, se pondrá de color azul y luego Subrayado, en cuanto esto aparezca hay que darle un clic normal y en automático nos mandará a la celda B4, que optamos relacionar. Diseño de hipervínculos Cuando estamos trabajando en nuestras hojas de cálculo, poner un Hipervínculo es una gran ayuda, pero también es cierto que si queremos que nuestro trabajo dé una apariencia especial, hay que darle un formato especial con la inserción deformas. Para empezar, ubiquemos la pestaña Insertar> Formas, después seleccionemos una figura que nos parezca adecuada a nuestras necesidades. Una vez que seleccionamos la forma, en cuanto el ratón se ponga en forma de cruz, debemos formar una figura dentro de la hoja de cálculo, de un tamaño considerable, que no estorbe en nuestra información y que nos permita ver el contenido. Ya elaborada la forma insertada, le damos un clic sobre la figura, y en automático en la parte superior derecha de su hoja de cálculo se desplegará una pestaña con el nombre de Herramientas de Dibujo. En el momento en que se genera esta pestaña le damos clic en la subpestaña de formato que también se habilita, para darle un mejor diseño a nuestra forma. Podemos darle clic en efectos de forma u optar por Preestablecer, Sombra, Reflexión, Iluminado, etcétera. Estas son buenas opciones, pero además podemos darle un relleno o un contorno más aceptable, con las diferentes opciones de Formato. En cuanto terminemos de poner todo el diseño que deseamos le damos clic a la figura, después clic derecho al ratón y le damos Modificar texto, enseguida le ponemos el texto que identifique la hoja o el libro al que queremos que nos lleve, enseguida le damos clic derecho al ratón sobre la figura e Hipervínculo, en cuanto se habilite la opción, seleccionamos la ruta a donde queremos que nos lleve. Normalmente cuando insertamos un Hipervínculo, al pasar el ratón por encima de este, aparece la ruta del archivo a donde nos va a mandar, y esto es un poco molesto. Para evitar que esto pase, es importante que cuando estamos dentro del Hipervínculo, le demos clic en Info en pantalla. A continuación le ponemos el nombre corto de la hoja o libro al que queremos que nos lleve. Al final le damos aceptar, después pasamos el ratón encima de la figura con el Hipervínculo insertado y veremos que aparece el texto que pusimos. Después le damos clic a la figura, de inmediato nos mandará a la ruta que le indicamos. Nota: para modificar o eliminar un hipervínculo solo hay que darle clic derecho sobre el hipervínculo generado y seleccionar la opción deseada. Valor relativo y absoluto en una celda Cuando necesitamos trabajar con celdas, tanto en una hoja como al combinar con otras del mismo libro, es importante conocer cuándo el valor de estas puede ser relativo o absoluto: • Relativo. Cuando el valor de una celda va cambiando, dependiendo de la ubicación que se le vaya dando. • Absoluto. Cuando el valor de la celda se vuelve fijo. Independientemente de la ubicación que tenga el valor es el mismo. Ejemplo: en una Hoja de Excel pongamos en una celda el concepto de tasa, en la siguiente ponemos 16%; después, en una columna colocamos los meses, en otra el importe, en otra el concepto de IVA y en otra el total. Luego de llenar nuestras columnas, en la primera celda del IVA ponemos el signo de =, seleccionamos la celda del importe de enero, lo multiplicamos por la celda donde colocamos 16% y le damos enter; después arrastramos con el ratón hacia abajo (ubicamos el ratón en la parte inferior derecha de la celda y en cuanto el ratón se ponga en forma de cruz, presionamos el ratón y sin soltarlo, lo arrastramos hacia abajo), así vemos que el valor de las celdas posteriores no respeta la multiplicación de 16%, por este motivo se hace relativo el valor de las celdas. Para un valor absoluto ponemos los mismos datos en las mismas celdas, solo que ahora, en la celda del IVA, le damos =, después seleccionamos la celda de importe, luego la celda donde está 16%, pero ahora presionamos la tecla F4, cuando hacemos eso estamos anclando la celda de la tasa del IVA. Esto lo podemos saber porque detrás de la letra de la columna y del número de fila se agrega el signo de pesos. Luego, con el ratón arrastramos el valor de la celda hacia abajo y vemos que el valor de todas las celdas se multiplica por 16%. Fórmulas de esquemas En ocasiones necesitamos realizar sumas y agruparlas por mes, además de hacer la suma total, pero estar dividiendo por mes toda nuestra información es mucha inversión de tiempo, para eso existe la opción de Subtotales. Primero ubiquemos la pestaña de Datos> Esquema> Subtotal. En una hoja ponemos una columna donde coloquemos los meses del ejercicio, repitiéndolos varias veces; y otra columna donde pongamos el importe en cada uno de los meses; después una columna del IVA y por último una de total. No colocamos las sumas de las columnas. Seleccionamos todo el contenido y ubicamos la pestaña Datos, Esquema y Subtotal, luego habilitamos los conceptos de Importe, IVA y Total. Le damos aceptar y de inmediato vemos cómo se agrupan por mes y se da el total del mismo, además de que se realiza la suma de las tres columnas con el concepto de Subtotales. Cabe destacar que la función de Subtotales puede darnos la opción de no duplicar los saldos, ya que suma toda la columna y no se altera el resultado total. Esto se debe a que tiene la opción de =SUBTOTALES(9,F37:F37), por lo siguiente: Es importante practicar con todas estas opciones y ver la diferencia en el resultado. Protección de hojas y libros Antes de ver los procedimientos, es importante saber que por más que se proteja un libro o una hoja, si alguien quiere abrir el archivo, lo hará. Cuando tenemos trabajos que son demasiado importantes y no queremos que otra persona modifique o altere su información, es trascendental saber cómo proteger nuestras hojas y libros. Comencemos con la protección de hojas. Al respecto, es importante destacar que estas se resguardan una por una, dependiendo de las que necesitemos proteger y del límite de protección; por ejemplo, puedo permitir que abran el archivo y solo vean el contenido, pero que no se pueda seleccionar nada de esa hoja; o bien, se puede permitir que seleccionen celdas protegidas y celdas desprotegidas, pero hay una desventaja con esta opción, que al momento en que se pueda seleccionar y copiar, la información se pasará al archivo nuevo; la otra opción es permitirle ciertos rangos de modificación al usuario. Asimismo, a veces no queremos que los usuarios puedan ver las fórmulas utilizadas en nuestro trabajo. Como se darán cuenta, hay muchas opciones, vamos a ver los diferentes ejemplos. Antes de hacerlo es importante que trabajemos con una hoja de Excel que tenga varias fórmulas en diferentes celdas. 1. Ejemplo de proteger hoja sin permitir seleccionar ni modificar nada, para hacer esto: Revisar> Cambios> Proteger Hoja. De inmediato aparece un recuadro que nos pide una contraseña y la confirmación de la misma. Es importante guardarla, ya que si la extraviamos será difícil recuperarla. Ahora bien, como se ve en la imagen, no se debe seleccionar ninguna opción, esto hará que quien desee abrir su archivo y ver esta hoja, solo vea el resultado, pero no podrá seleccionar ni copiar nada. Para probar el resultado es recomendable tratar de seleccionar o modificar las celdas, pero veremos que no se podrá realizar. 2. Ejemplo de protección de hoja con permiso de ciertos rangos a modificar, Revisar> Cambios > Permitir que los usuarios modifiquen rangos. Aquí verán que se habilita un recuadro con tres opciones: Nuevo, Modificar y Eliminar, y como no hemos dado ningún rango —ya que este es el primero—, solo aparecerá habilitada la pestaña de Nuevo, con la elección de darle una nueva opción de cuáles celdas son las únicas que quiero que el usuario pueda modificar, le damos clic normal en el icono de Nuevo y aparecerán otras opciones, en especial una de título: no recomiendo ponerle título a cada rango, al final puede ser confuso, mejor dejen el que da por default; correspondiente a las celdas: en esta parte damosclic normal en el cuadro que aparece del lado derecho y tiene una flecha roja dentro, para permitirnos seleccionar las celdas que deseamos, en cuanto se habilite seleccionamos varias celdas, solo hay que recordar cuales; contraseña del rango: aquí se puede poner una contraseña para proteger mi rango, pero no es recomendable poner tantas contraseñas a una hoja. Al terminar estos pasos le damos aceptar. Después de hacer esto, Revisar> Cambios> Proteger hoja, seleccionamos las primeras dos opciones que nos pide el cuadro de protección de hoja, ponemos una contraseña y la confirmamos; ahora, si tratan de modificar una celda de las que no permitimos rangos se darán cuenta de que no se puede, pues indica que se encuentra protegida; pero si después de esta acción modificamos una celda de las que permitimos rangos, verán que en esta sí se pudo realizar el cambio. 3. Ejemplo de protección de hoja. Sin que se puedan visualizar las fórmulas contenidas en nuestra hoja de trabajo, primero seleccionamos toda la hoja que deseamos proteger, haciéndolo de varias maneras: a) Presionando las teclas CTRL + E. b) Presionando CTRL + SHIFT + Barra espaciadora. c) Dando clic normal al icono que está en forma de triángulo invertido, entre la columna A y la fila 1, después presionamos las teclas CTRL + 1 (este uno, es el de la parte superior del teclado), de inmediato se habilitará Formato de Celdas, ahora le damos clic en el icono de Proteger y habilitamos el cuadro de Oculta, pues el de bloqueada ya está habilitado. Después le damos aceptar. Ahora protegemos la hoja Revisar> Cambios> Proteger Hoja, ya que lo hicimos nos ubicamos en cualquiera de las celdas que tienen fórmulas y veremos que podemos visualizar el resultado, pero no ver las fórmulas. Nota: para desproteger una hoja se realizan los mismos pasos: Revisar> Cambio> Desproteger hoja, y pide la contraseña que pusimos en un inicio. Proteger y desproteger libro Cuando estamos trabajando con uno de nuestros libros y no queremos que lo modifiquen, puede ser un tanto complicado porque sí podemos proteger hojas, pero si no protegemos el libro, sí pueden modificarlo, ya sea agregando o quitando hojas. Si damos Revisar> Cambios> Proteger libro, de inmediato se habilitará un cuadro que muestra Proteger Estructura y Ventana (opción deshabilitada), pero recomiendo que solo dejen seleccionado el concepto de Estructura; enseguida pide una contraseña, la ponemos y le damos aceptar, después la confirmamos y otra vez le damos aceptar. Luego de este paso, aparentemente no hizo nada nuevo y tampoco nos habilitará la opción de Desproteger libro, pero si nos posicionamos sobre el icono de cualquier hoja del libro y le damos clic derecho al ratón, veremos que varias opciones del libro, están deshabilitadas, tales como: eliminar, copiar, modificar color, etcétera. Lo anterior significa que el libro está protegido. Ahora, para desprotegerlo no hay una opción como tal; por lo tanto, recurrimos al mismo procedimiento que usamos para proteger: Revisar> Cambios> Proteger libro y nos solicitará la contraseña. Como vemos hay muchas opciones para proteger nuestras hojas y libros; sin embargo, si llegáramos a perder la contraseña tendríamos muchos problemas para desproteger, tanto las hojas como el libro. Trabajo compartido con varias hojas En ocasiones necesitamos poner los mismos conceptos en diferentes hojas y las sumas de estas celdas en la última hoja, lo cual se puede hacer mediante Trabajo compartido con varias hojas, partiendo de la necesidad de trabajar con los mismos conceptos o, posiblemente, con los mismos montos en las mismas hojas; para ello, hay que abrir un libro nuevo, lo aumentamos a 6 hojas y ponemos los meses de enero a mayo, y la última hoja la nombramos como Total; ahora damos clic en la hoja de Enero, después presionamos SHIFT y sin soltarlo le damos clic en la hoja de Mayo, de inmediato veremos que todas las hojas se ponen en blanco; luego nos ubicamos en la celda B5 de la hoja Enero, ponemos el texto Ingresos, después le damos enter y si nos pasamos a cualquiera de las siguientes hojas, de enero a mayo, veremos que el mismo concepto aparece en la misma celda que en la hoja de Enero. Podemos realizar la misma técnica con los importes, hagamos lo mismo con la celda C5, solo que ahora le ponemos la cantidad de 50,000. Para deshabilitar las hojas seleccionadas, solo le damos clic en la hoja Total. Si se desea hacer la suma de los importes que pusimos, solo nos ubicamos en la hoja de Total, en la celda C5, ponemos la función SUMA(, presionamos SHIFT y sin soltarlo le damos clic en enero y luego le damos clic en mayo, cerramos paréntesis y le damos enter. Como veremos, se hará la suma de los importes de enero a mayo. Gráficos Tipos de gráficos disponibles Microsoft Excel admite muchos tipos de gráfico para mostrar los datos de forma comprensible a su audiencia. Cuando se crea un gráfico o se cambia el tipo de uno existente, ya sea en Excel o en otros programas de Office como: Word, Power Point o Outlook, puede seleccionar uno de los siguientes tipos de gráficos: • Gráficos de columnas • Gráficos de líneas • Gráficos de circulares • Gráficos de barras • Gráficos de área • Gráficos de tipo XY (dispersión) • Gráficos de superficie • Gráficos de anillos • Gráficos de burbujas • Gráficos radiales La importancia de insertar gráficos radica en que, con estos, las variaciones de los estados financieros o la actividad que desarrollemos tiene una mejor presentación en nuestros trabajos. Para trabajar mejor con esta opción, abrimos un libro nuevo de Excel y en la hoja 1, en cuatro columnas, vamos a poner los siguientes conceptos: Mes, Ingresos, Costos, Utilidades, escribimos los meses de enero a diciembre, así como los importes en las columnas de Ingresos y Costos, determinamos la utilidad, después seleccionamos todo lo realizado y ahora Insertar> Gráficos> Columna agrupada. Al darle clic en la gráfica de columna se habilitará un gráfico con los datos que pusimos en un inicio. Vamos a ver que el gráfico muestra datos verticales y horizontales, al mismo tiempo, en la parte superior derecha se agrega una nueva pestaña que se llama Herramientas de Gráficos, esta opción, a su vez, habilita dos opciones: Diseño, Formato. En cuanto se genera el gráfico se habilita el icono de Diseño, en esta parte podemos realizar diferentes opciones, aplicar el cambio de filas y columnas; o bien, cambiar el formato que tiene el gráfico con solo darle un clic en la opción de estilos de diseño. Esto debe hacerse cuando se esté dentro del gráfico, de lo contrario no podremos realizarlo, porque la figura es la que tiene las herramientas. Ya que elegimos el tipo de gráfico nos damos cuenta de que a diferencia de Excel, versiones anteriores, esta versión nos permite ver los títulos centrados y otras características de presentación. En versiones anteriores aparece una pestaña con el nombre de presentación y si nos percatamos, del lado derecho aparece un signo de más (+), el cual trae esas opciones. El segundo icono se presenta en forma de pincel y muestra los estilos, y finalmente un icono en forma de filtro nos indica el estilo y color para el formato deseado. En el signo de más (+) le damos clic dentro del mismo y podemos cambiarle el nombre; para poner los títulos en la parte inferior y a los costados, usamos el icono Presentación, en Etiquetas, Rótulos del Eje. En esta parte aparecen dos opciones: Título de eje horizontal primario y Título del eje vertical primario, con las que podemos poner las otras opciones. Así como lo anterior, existen más opciones que se pueden practicar conLeyenda, etiquetas, Líneas de Tendencia, etcétera. La última opción de esta parte es la de Formato, con la cual se puede cambiar el estilo de la letra, rellenar la forma o poner diferentes estilos. Hay diferentes opciones dentro de gráficos, lo importante es ser curiosos y practicar diferentes formatos y aplicaciones. Por último, es conveniente saber que los gráficos se puede trabajar dentro de la misma hoja, o bien, generar una hoja solo para el gráfico, lo cual podemos hacer en el icono de Diseño, en la parte superior derecha, aparece un icono Mover gráfico, le damos clic y nos habilitará dos opciones: Hoja nueva y Objeto en. • Hoja nueva. Con esta opción podemos trabajar en una hoja independiente, donde solo se vea la gráfica. • Objeto en. con esta opción, se puede trabajar dentro de la hoja donde están los datos con los que se elaboraron los gráficos, o bien en una hoja que contenga otro tipo de información. Para eliminar un gráfico, solo tenemos que darle clic normal al gráfico y presionamos la tecla DEL o SUPRIMIR del teclado, o damos clic derecho del ratón y cortamos. Inserción imágenes Cuando trabajamos en cursos donde debemos hacer referencia a ciertas presentaciones, necesitamos contar con imágenes adecuadas al tema, las cuales, normalmente, buscamos en Internet, pero Excel tiene esta opción. Ubicamos la pestaña Insertar> Ilustraciones> Imágenes en línea. En esta parte es indispensable estar conectado a Internet, ya que al darle clic se activará un cuadro que dice buscar, ahí colocamos el nombre de lo que necesitamos y aparecerán diferentes figuras relacionadas con ello. Para ver la efectividad hagamos el procedimiento y solicitemos la palabra “gato”. Veremos cómo aparecen diferentes opciones, en cuanto surja la elegida le damos doble clic y en automático la imagen se instalará en nuestra hoja de cálculo. Inicio de funciones Antes de ver el procedimiento de las funciones de Excel que podemos aplicar los contadores y/o administradores, es importante conocer los operadores indispensables para la aplicación de estas. Operadores de Excel Por más simple que se vea un operador todos son de gran importancia e indispensables para el manejo de nuestras funciones. Además de los operadores es importante conocer los elementos de las funciones —más adelante veremos el mecanismo de cada una de las funciones—, pero sin todos los elementos no se puede terminar. 1. ( ) Paréntesis, son la parte inicial y final de una función, sin la apertura y la clausura de dicho argumento, no podemos tener ningún resultado, solo obtendremos texto o error, por ejemplo: =SUMA(A9:B19) 2. , Coma, yo la llamo comodín, ya que debido a la funcionalidad que tiene dentro de todas las funciones, por ejemplo: (, , ,) 3. : Dos puntos, esta parte la llamo rango o referencia, ya que con esta opción podemos dar una referencia exacta a un rango determinado, normalmente es para una columna o fila, nunca combinadas, por ejemplo: B15:E15. 4. “ Comillas, estas van a jugar un papel muy importante dentro de las funciones: • Si necesito poner texto dentro de una función, necesito abrir comillas al inicio del texto y cerrarlas al final. • Si necesito que una función dé como resultado nada, necesito utilizar doble comillas (“”). • Si necesito poner un espacio, pondré comillas al inicio, después un espacio y cierro comillas (“ ”). 5. Fila, considero como fila, la selección de un renglón completo. 6. Columna, considero como tal, a la columna completa. Nota: para insertar un signo exponencial o cualquier otro icono, debemos hacerlo desde el archivo opciones, personalizar cinta de opciones. En la parte de comandos disponibles aparece por default Comandos más utilizados, ahí se cambia a todos los comandos. Después de haber habilitado lo anterior nos ubicamos en la columna donde se encuentran todos los comandos y presionamos la letra S del teclado, de inmediato aparecerá la aplicación de Suma. En cuanto encontremos el signo exponencial le damos clic en agregar y de inmediato se pasará a la siguiente columna, después aparecerá en la parte superior de nuestra hoja de cálculo, para ser exactos, arriba de archivo. Un método más rápido es darle clic en el filtro que está en la parte superior de la hoja de cálculo. Comencemos con un pequeño ejemplo de Funciones. Veamos un ejemplo de exponente y de raíz cuadrada, para ello utilizaremos el Signo exponencial, el cual agregamos en la primera parte de este libro (^). Ahora bien, en un libro o en una hoja nueva hagamos un ejercicio, y en una celda pongamos el siguiente ejemplo: El resultado es 100,000.00, esto se debe a que 10 se elevó a la quinta potencia, aunque a veces es difícil determinar la raíz cuadrada de cualquier cantidad, pero al utilizar el Signo exponencial podemos elegir esta opción de la siguiente manera: en la celda E8, ponemos =D5^(1/5), y el resultado será 10. Como verán, el resultado se debido a que al sacar el exponente con el factor de decimal se hace la operación inversa. En el momento en que se desee sacar la raíz de alguna cantidad en específico, es importante que después de la cantidad se ponga el signo exponencial y luego se divida 1 entre el múltiplo deseado, ya sea raíz cuadrada, raíz cubica, etc., como se hizo anteriormente. Funciones matemáticas y estadísticas Antes de comenzar a trabajar con estas funciones hay que abrir un libro nuevo de Excel y practicar todas las funciones que vamos a utilizar. SUMA Una de las funciones más usadas es la SUMA, pero veamos cuáles son los argumentos que utiliza. En una celda ponemos la cantidad de 50,000.00 y en la celda que sigue la cantidad de 40,000.00, después ponemos =SUMA, al momento de hacerlo se habilitará la función de =SUMA, entonces presionamos la tecla Tabulador y la función estará lista para usarse, inmediatamente seleccionamos la celda donde pusimos la cantidad de 50,000.00, ponemos una (,), después seleccionamos la otra celda donde pusimos la cantidad de 40,000.00 y le damos enter. El resultado será 90,000.00, pero la función de SUMA también se puede utilizar seleccionando ambas celdas y dará el mismo resultado. ABS =ABS(número) Cuando realizamos operaciones que por alguna razón nos da como resultado un número negativo y necesitamos que nos dé positivo, normalmente la multiplicamos por (-1), a veces esto puede ser un poco complejo, pero para ello existe la función ABS, la cual nos permite que el resultado siempre sea positivo; ahora bien, si el resultado fuera positivo en sí, este no se altera. Para ver la aplicación de esta función, en una celda ponemos el número (-5) y en la celda siguiente ponemos la función =ABS(número), al abrir el paréntesis nos pide el número, seleccionamos la celda donde pusimos el número negativo, le damos enter y el resultado será el mismo número, pero positivo. ALEATORIO =ALEATORIO() Esta fórmula siempre va a mostrar los valores que existen entre el 0 y el número 1, y como su nombre lo dice, solo tendrá números aleatorios; por lo tanto, todos van a cambiar, en cuanto hagamos una nueva función o lo editemos, si queremos ver cómo van cambiando, solo presionamos la tecla F9 del teclado. De hecho, no necesitamos incluir ninguna cantidad. ALEATORIO ENTRE =ALEATORIO.ENTRE(Inferior,superior) Si necesitamos saber cuáles son los números aleatorios que existen entre una cantidad y otra, necesitamos la aplicación de la función ALEATORIO.ENTRE, la cual nos permite ingresar un número inferior y un superior, y dependiendo de la cantidad que manejemos irán cambiando con solo presionar la tecla F9. Pongamos en una celda una cantidad mínima y en la otra la cantidad máxima que deseamos considerar en dicha función, en la siguiente celda ponemos la función y seleccionamosla celda donde colocamos la cantidad mínima, después ponemos una coma, al final seleccionamos la celda donde colocamos la cantidad máxima y le damos enter. A partir de esta fórmula es donde comenzamos a utilizar la (,), como comodín, ya que en esta ocasión está manejando una relación de números que existen entre 500 y 1000. Nota: también podemos anotar las cantidades dentro de la función, sin celdas alternas. COCIENTE Cuando necesitamos obtener el número entero de una división, una de las mejores opciones es la función de COCIENTE, la cual pide un numerador y un denominador. El resultado de esta función siempre dará un número entero —aquí no aplicará lo del redondeo —, en caso de que los decimales que resulten después del entero sean superiores a .50, no subirá al siguiente número, siempre va a regresar al número entero principal, es decir elimina los decimales. Para tener un mejor entendimiento, en una celda que consideraremos como Numerador, ponemos la cantidad de 375.00, en otra celda que supondremos como Denominador, colocamos la cantidad de 54.00, luego aplicamos la función de =COCIENTE(, seleccionamos la celda de Numerador, ponemos (,), después seleccionamos la celda Denominador, le damos enter y el resultado en una división normal sería de 6.9444444, pero como COCIENTE solo admite entero, el resultado es 6. Como se ve en el ejemplo el resultado es entero, en esta ocasión la (,), funcionó como división. Nota: las cantidades se pueden anotar dentro de la función sin necesidad de utilizar celdas alternas. ENTERO Esta función es muy similar a la de cociente, solo que esta siempre redondea el valor hacia abajo, nunca hacia arriba, aunque los decimales sean superiores a .50 esta función nos pide número, por lo que necesitamos poner un número que sea con decimales aproximados al siguiente. Para hacer esto más entendible, en una celda pongamos el número 9.99, en la siguiente ponemos la función =ENTERO(, seleccionamos la celda con el número descrito anteriormente, le damos enter y el resultado es 9. La cantidad también se puede anotar dentro de la función sin utilizar celdas alternas. PRODUCTO =PRODUCTO(número1, número2) Esta función multiplica sus argumentos para manejar las multiplicaciones de una cantidad por otra y el resultado por otra cantidad, a veces es complicado hacerlos dentro de una función, pero con esta opción podemos resolver este pequeño problema. Para entenderlo un poco mejor, realicemos un ejemplo: en una celda ponemos la cantidad de 10, en otra la cantidad de 12 y en otra la cantidad de 12, colocamos la función de =PRODUCTO(, después seleccionamos la primera celda donde colocamos la cantidad de 10, ponemos (,), seleccionamos la segunda celda donde pusimos la cantidad de 12, ponemos (,), ahora seleccionamos la última celda donde pusimos la otra cantidad 12, el resultado es 1,440.00 (esto es como si multiplicáramos 10 x 12 x 12). En esta ocasión, la (,) funcionó como multiplicación; además, las cantidades se pueden colocar dentro de la función sin utilizar celdas alternas. PROMEDIO =PROMEDIO(número1, número2) Devuelve el promedio (media aritmética) de los argumentos, número o texto. Cuando necesitamos que en un rango determinado de ciertas celdas con datos específicos, nos determine el promedio de las cantidades que en ellas se encuentran, una de las mejores opciones es la función de PROMEDIO, aunque esta opción tiene ciertas limitantes; por ejemplo, no se pueden agregar datos y texto directos en la función. Si estoy utilizando esta función, puedo combinar números y texto, siempre y cuando sea mediante celdas alternas. Para entender mejor esta aplicación, veamos un ejemplo. En una celda ponemos la cantidad de 50, en otra la cantidad de 100, en otra celda ponemos Hola, en una última celda ponemos la cantidad de 200, ahora ponemos la función =PROMEDIO(, después seleccionamos todas las celdas, cerramos el paréntesis de la función y vemos que no altera el resultado del promedio, omite el texto, el resultado es 117. REDONDEAR Redondea un número a uno especificado de dígitos. =REDONDEAR(número, núm. decimales) Cuando los contadores o administradores estamos declarando impuestos —pero por alguna razón dejamos de pagar determinada cantidad—, de cualquier manera debemos hacerlo, solo que esta cantidad cambia, ya que además de pagar el importe histórico, también se paga lo que conocemos como Actualización y Recargos. Estos accesorios se deben redondear al número subsecuente y lo que hacemos es dar formato de celdas y quitar los decimales a nuestra celda, lo cual ocasiona un problema cuando copiamos la cantidad y la pegamos en otra celda porque vuelven a verse los decimales que se generaron con las fórmulas que aplicamos normalmente. Lo que hace esta función es que en caso de que tengamos una cantidad con varios decimales, los redondeará a los decimales o al entero que deseamos. Para entender un poco mejor esta función, elaboremos un caso práctico. En una celda ponemos la cantidad de 10, en otra celda ponemos la cantidad de 3.5, en otra celda ponemos la función de =REDONDEAR(, después seleccionamos la celda donde colocamos la cantidad de 10, dividimos entre la celda donde colocamos la cantidad de 3.5, ponemos (,), al final ponemos el número de decimales que deseamos redondee, en mi ejemplo le pondré el 0, ya que no quiero que aparezca ningún decimal. Para ver un mejor comparativo voy a mostrar el resultado con la función y el resultado con solo la división normal. En esta función los datos se puede incluir directamente en la función, de hecho es lo más recomendable, salvo que se esté en el supuesto de realizarlo para determinar saldos de operaciones ya realizadas. Veamos la aplicación con datos dentro de la función explicada. 3 =REDONDEAR(10/3.5,0) Es recomendable practicar con varias opciones para entender un poco más este tipo de funciones, pues quienes nos dedicamos a la contabilidad, dependemos mucho de esta, debido a la complejidad de nuestros cálculos fiscales. Con el ejemplo que explicamos es conveniente realizar diferentes aplicaciones, por ejemplo, cuando se habilite la función y comencemos a realizar la división, para saltar a la parte donde nos pide el número de decimales, debemos poner una (,), inmediatamente podemos probar con diferentes decimales, en el ejemplo le puse 0, pero se puede probar con: 2, 3, 4, etc., o se pueden hacer cálculos con diferentes cantidades. TRUNCAR Trunca un número o dirección de celda a un entero. =TRUNCAR(número,(núm. de decimales) Como ya hemos mencionado, este libro y el contenido que hemos desarrollado están enfocados en los contadores y administradores. En este caso vamos a ver una función que nos ayuda a no pagar impuestos de más, por la determinación de factores, ya que esta aplicación considera los decimales o enteros, dependiendo del rango que le marquemos; es decir, cuando quiero determinar un factor de actualización para actualizar un impuesto con cierta antigüedad, si lo realizo de una manera cotidiana, hay veces que los decimales son bastantes y aunque parezca que no afecta, en ocasiones pagamos bastante dinero por situaciones de este tipo. Sin embargo, si optamos por aplicar la función de TRUNCAR, esta nos permitirá determinar correctamente el resultado. Para ver una mejor aplicación hagamos un ejemplo: en una celda pongamos la cantidad de 100.5456, en otra la cantidad de 99.5612, en la siguiente celda ponemos la función =TRUNCAR(, seleccionamos la celda donde pusimos la cantidad de 100.5456, después la dividimos entre la celda donde pusimos la cantidad de 99.5612, ponemos una (,) para habilitar los decimales, ponemos 4 y con estole estamos diciendo a la función que solo nos considere hasta cuatro decimales, no va a redondear, tampoco a considerar otros que estén continuos, lo que hará es quedarse con la cantidad de hasta cuatro decimales, como si los demás fueran ceros, el resultado es 1.0098000, aunque la división normal da 1.00988739. Esta función nos permite agregar los números directamente sin necesidad de utilizar celdas alternas, si vemos un ejemplo con las mismas cantidades, el resultado será el mismo, no altera en nada a la función. 1.0098 =TRUNCAR(100.5456/99.5612,4) Se pueden hacer varias pruebas con el mismo ejemplo, cambiando los decimales a 2, 3, 4, etc., o realizar ejercicios con diferentes cantidades o celdas que ya estén con cantidades definidas. SUMAR.SI Suma las cantidades de un criterio en específico. =SUMAR.SI(rango, criterio,[rango, suma]) Cuando tenemos conceptos iguales en una hoja de cálculo, pero en diferentes celdas en un rango determinado y necesitamos sumarlos, es complicado hacerlo uno por uno, ya que es difícil identificar celda por celda. Esta función nos pide un rango de conceptos, un criterio que deseamos aplicar y un rango que necesitamos sumar, este tipo de funciones la podemos utilizar con rangos definidos o con selección de celdas. Veamos un ejemplo con la primera opción: Rangos ya definidos. Para esto necesitamos poner en una fila, en cuatro celdas seguidas los siguientes conceptos: ISR, IVA, IETU, ISR, en la siguiente fila ponemos cantidades por: 1,000.00, 2,000.00, 2,000.00 y 5,000.00, ahora seleccionamos solo los conceptos y aplicamos lo que vimos en capítulos anteriores, generando un rango; ya que escogimos los conceptos le damos clic en el cuadro de Nombres y nombramos a nuestro rango Impuestos, luego le damos enter, después seleccionamos las cantidades, le damos clic en el Cuadro de Nombres, nombramos a nuestro rango Cantidad y le damos enter. Ahora vamos a realizar la aplicación de la función =SUMAR.SI(, ponemos el concepto de Impuestos, después ponemos una (,), después vamos a insertar texto en la función (como se había comentado en capítulos anteriores, al escribir texto dentro de una función, hay que ponerlo entre comillas) “ISR”, ahora ponemos de nuevo una (,), después ponemos Cantidad, cerramos paréntesis y nuestra función sumó todos los conceptos que tienen el texto ISR, el resultado es 6,000.00. Ahora hagamos el ejemplo de la función, pero utilizando selección de celdas, realmente el mecanismo es el mismo, la diferencia es que aquí los rangos se definen por celdas seleccionadas. Comencemos haciendo lo mismo que en el anterior, en una fila en cuatro columnas seguidas, ponemos los conceptos de ISR, IVA, IETU, ISR, en la siguiente fila ponemos las cantidades 1,000.00, 2,000.00, 2,000.00, 5,000.00, ahora comencemos a poner la función =SUMAR.SI(, seleccionamos las celdas con los conceptos, ponemos el texto entre comillas “ISR”, después seleccionamos las celdas con las cantidades, cerramos paréntesis y le damos enter, el resultado será el mismo 6,000.00. ¿Qué es lo que pide esta función? Cuando habilitamos esta función nos pide: un rango de conceptos que se puede sumar, un criterio para considerar los conceptos que se pueden sumar y una cantidad por sumar, este es el resultado de los conceptos que contienen cantidades que deben dar un resultado específico. El ahorro de tiempo que se tiene entre esta función y hacerlo por separado celda por celda es bastante, ya que aquí se consideran rangos o celdas seleccionadas. CONTAR.SI Cuenta el número de celdas que no están en blanco dentro de un rango que coincida con los criterios especificados. =CONTAR.SI(rango,criterio) Esta función realiza una tarea que es un poco cansada, el contar cuántas veces se repite un número o un concepto devuelve el número de veces que está escrito el número o el texto. Es similar a la función antes mencionada, para tener una idea más clara sobre esta función vamos a ver un caso práctico. En una fila, en cuatro celdas ponemos lo siguiente: 1,000.00, 2,000.00, 3,000.00, 2,000.00, en la siguiente celda, ponemos la función =CONTAR.SI(, “nos va a pedir un rango”, después seleccionamos las cuatro celdas con las cantidades 1,000.00, 2,000.00, 3,000.00, 2,000.00, “nos va a pedir un criterio”, ponemos la cantidad 2,000.00, cerramos el paréntesis y le damos enter, el resultado va a ser 2, ya que está contado cuántas veces se encuentra 2,000.00 dentro del rango seleccionado. NUMERO.ROMANO Convierte números arábigos en romanos, como texto. =NUMERO.ROMANO(número,[formal]) En ocasiones tenemos la necesidad de convertir un número arábigo a número romano, lo más común es hacerlo manualmente, pero es un poco cansado. Con la función =NUMERO. ROMANO hay una solución a esta problemática, pero tiene la limitante de que solo llega al número 3,999, debido a que el número 4,000, es IV con una línea arriba y la función ya no lo detecta. Para entender un poco más esta función, hagamos un caso práctico. En una celda ponemos el número 40, en la siguiente ponemos la función =NUMERO.ROMANO(, seleccionamos la celda donde colocamos la cantidad de 40, cerramos paréntesis y le damos enter, vemos que el número arábigo se convirtió en número romano y el resultado es XL. En caso de que después de seleccionar el número, pongamos una (,), nos pide la forma más adecuada que deseamos darle con cinco opciones, pero realmente no tiene modificación, por eso es que en el ejemplo no pusimos ninguna de estas; sin embargo, se puede practicar. Operaciones con fracciones Realmente esta no es una función, sino una aplicación de formato de celdas de Excel, la cual permite convertir los números enteros con decimales, en fracciones. Con solo hacer operaciones de suma, resta o multiplicación de número normales, se puede convertir en fracciones. Veamos un ejemplo. En una columna ocupando dos celdas, ponemos 1.5, en la siguiente columna en la misma fila, ponemos la cantidad de 3.109375, en una celda siguiente hacemos la suma de estas dos, el resultado es 4.6641. Ahora seleccionamos las celdas que hemos mencionado y abrimos formato de celdas, para ello hay dos formas: 1. Dar clic derecho del ratón y seleccionar formato de celdas. 2. Presionar las teclas CTRL + 1), dar clic en la pestaña Número y seleccionar Fracción. Hay varias opciones, pero en esta ocasión se selecciona Hasta un dígito (1/4). El resultado de esta operación se muestra en fracciones y sin necesidad de utilizar funciones extras. Función de matrices Devuelve el producto matricial de dos matrices. =MMULT(matriz1,matriz2) Cuando necesitamos que una función realice aplicaciones de Matrices, esta función de =MMULT, solo pide que se tenga el mismo número, tanto de filas como de columnas, y para generar una función correcta necesitamos utilizar la aplicación de lo que se conoce como Matriciales, por medio de las siguientes teclas CTRL + SHIFT + ENTER (presionar al mismo tiempo), se generarán las siguientes llaves { }, las cuales se pueden poner manualmente, pero no va a funcionar, la única manera en que las matriciales funcionen, es mediante las teclas mencionadas; de hecho, si la aplicación ya está realizada y estamos en la celda y le damos F2, se editará la celda y al hacerlo las llaves desaparecerán. Hagamos un caso práctico, en una hoja pongamos los siguientes datos: Como se darán cuenta hay cuatro filas y cuatro columnas (como se mencionó al inicio), ahora realicemos la selección de estas. Ahora solo ponemos la función =MMULT( Ahora seleccionamos el primer cuadro (Matriz 1), después ponemos una (,) para poder activar la siguiente
Compartir