Capitulogratis

FINANZAS CON MICROSOFT EXCEL

Capítulo

1

Excel y la contabilidad Comencemos el recorrido de esta obra descubriendo cuáles son las herramientas de Excel que nos facilitarán algunas tareas relacionadas con la contabilidad.

Entre ellas, veremos las tablas y las tablas dinámicas, haremos comparaciones y validaciones.

SERVICIO DE ATENCIÓN AL LECTOR: lectores@redusers.com

Conciliación de información Las funciones de búsqueda Las referencias relativas y absolutas Más detalles Filtros Algunos cambios Tablas y tablas dinámicas Las tablas Las tablas dinámicas Las validaciones de datos Aspectos básicos del sistema contable Los estados contables Búsqueda y reemplazo Cambiar signos El balance Resumen Actividades

14 16 20 21 22 24 26 26 28 31 35 39 39 41 44 45 46

Conciliación de información

1. EXCEL Y LA CONTABILIDAD

CONCILIACIÓN DE INFORMACIÓN Una de las tareas más repetidas en cualquier departamento de contabilidad o administración de una empresa es la realización de conciliaciones de datos e información. Podrían ser, por ejemplo, datos bajados de algún sistema informático de información o, simplemente, listas de datos ingresados en una planilla de Excel que debamos comparar o mayores de cuentas contables que queramos contrastar, entre otras opciones.

Para realizar comparaciones de datos podemos utilizar y combinar distintas herramientas de Excel, por ejemplo: tenemos la posibilidad de usar funciones que nos permitan comparar valores y aplicar algunas opciones de las herramientas de ordenamiento y filtro.

Comencemos con una ejemplificación bien simple. Supongamos que tenemos una extracción de datos de algún sistema de la compañía que debemos comparar con un archivo de Excel donde se hacen seguimientos manuales de las ventas y queremos contrastar si hemos registrado en la contabilidad todas las facturas de venta. En primer lugar, contamos con el detalle de la extracción del sistema, donde figura el número de factura que será la clave que utilizaremos para la comparación de los datos.

En el caso que presentamos, la extracción tiene los datos de la facturación, los números de los comprobantes, las fechas y los importes. Además, contamos con un detalle que lleva el departamento de ventas, donde se registra cada una de las operaciones que realizaron los vendedores. Esa información la remiten al departamento contable todos los meses para que se efectúe la comprobación del registro contable de las facturas y luego se proceda a liquidar las comisiones correspondientes a los vendedores.

Figura 2. El seguimiento del departamento de ventas incluye otros datos, como el canal de ventas y el cliente.

Para realizar esta primera conciliación utilizaremos, como nexo, el número de la factura y aplicaremos funciones de búsqueda.

EN EXCEL 2003

✱

Las limitaciones de Excel 2003 (y otras versiones anteriores) son bastantes si las contrastamos con Excel 2007. La cantidad de filas que se pueden utilizar no puede superar las 65.536, en comparación con la posibilidad de usar más de un millón en

Figura 1. Habitualmente, los sistemas de información, ya sean contables o administrativos, tienen la posibilidad de generar extracciones, en algunos casos directamente a Excel y, en otros, como archivo de texto.

14

la última versión. Además, como máximo, podremos contemplar 256 columnas respecto de las más de 16.000 de Excel 2007.

15

1. EXCEL Y LA CONTABILIDAD

Las funciones de búsqueda Dentro de la pestaña Fórmulas, Microsoft Excel 2007 presenta un grupo denominado Biblioteca de funciones que incluye, divididas en distintos grupos, las fórmulas que podemos aplicar. Entre estos diferentes grupos encontramos las funciones de Búsqueda y referencia.

Conciliación de información

• Lógicas: aquí encontraremos algunas funciones que nos resultarán de gran utilidad al elaborar construcciones condicionales complejas. Por ejemplo, con la función SI podremos analizar una determinada condición que, de cumplirse, nos permitirá realizar un cálculo o una acción. Si arroja como resultado un valor falso, podremos realizar algo diferente.

• Texto: muchas veces tendremos que trabajar con valores no numéricos. Para poder hacerlo, en este grupo hallaremos funciones que nos permitirán trabajar más fácilmente con este tipo de datos.

• Fecha y hora: dentro de este grupo encontraremos las funciones referidas al manejo de fechas y su composición. Es posible determinar días, meses y años, como así también horas, minutos y segundos.

Además de estos grupos que mencionamos, existe el referido a las funciones de Búsqueda y referencia, donde hallaremos funciones que nos habilitarán para realizar búsquedas y comparaciones. También encontraremos un grupo específico que contiene funciones Matemáticas y trigonométricas, y otro grupo que concentrará el resto de las funciones disponibles.

Figura 3. Son varias las funciones que nos permitirán realizar comparaciones.

Las funciones se encuentran agrupadas de acuerdo con su funcionalidad. Hagamos un rápido repaso por el contenido de cada uno de los grupos.

• Autosuma: además de la función Suma (que nos permite realizar sumatorias de valores incluidos en celdas y rangos), este grupo contiene otras funciones sencillas que nos permitirán calcular promedios (Promedio), obtener los valores mínimos (Mín) y máximos (Máx), y realizar diferentes formas de conteos.

• Usadas recientemente: este grupo irá mostrando las últimas funciones utilizadas y nos permitirá un fácil acceso a las que utilizamos con más frecuencia.

• Financieras: dentro de este grupo hallaremos las funciones que nos posibilitarán realizar cálculos financieros (profundizaremos sobre esto en los próximos capítulos). El cálculo de intereses y tasas, de pagos, y la amortización de créditos son algunas de las cuestiones que podremos resolver con estas funciones.

16

Figura 4. La categoría Cubo aparece en la versión 2007 de Excel (no en anteriores) e incluye funciones relacionadas con cubos de dato.

Dentro de este último grupo tendremos funciones específicas relacionadas con cálculos estadísticos y de ingeniería. Asimismo hallaremos las funciones ES y otras que nos permitirán identificar y trabajar con los diferentes tipos de errores que puedan arrojar algunas funciones.

17

Conciliación de información

1. EXCEL Y LA CONTABILIDAD

Para volver a nuestro proyecto, recordemos que contamos con dos planillas diferentes: una, con datos extraídos de un sistema contable y otra, con un detalle de seguimiento del departamento de ventas.

Una de las formas de realizar comparaciones es a través de funciones. En este caso utilizaremos BUSCARV, que forma parte del grupo Búsqueda y referencia.

Esta función nos permite, partiendo de un dato que funciona como llave (valor_buscado), encontrarlo en otra tabla o matriz (matriz_buscar_en) y obtener un determinado dato de esta última. Su sintaxis es =BUSCARV(valor_ buscado;matriz_buscar_en;indicador_columnas;[ordenado]). Debemos tener en cuenta que esta función buscará la información de manera vertical, es decir, el valor buscado debe encontrarse a la izquierda de la tabla o matriz donde deseamos encontrar el dato correspondiente. En nuestro ejemplo, el valor buscado que utilizaremos como llave será el número de la factura, ya que es el dato que figura en las dos tablas, y utilizaremos una celda auxiliar para volcar los resultados. Comenzaremos chequeando desde la planilla extraída del sistema, los datos incluidos en el seguimiento del departamento de ventas.

El primer argumento de la función BUSCARV será la celda que contenga el número de la factura que utilizaremos como nexo. En este ejemplo, esos valores se encuentran dentro de la fila A. Para el segundo argumento tenemos que seleccionar la tabla o matriz donde se debe realizar la búsqueda, que en este caso será la siguiente matriz de datos.

Es importante que tengamos bien en claro que la primera columna del segundo argumento de esta función (la matriz donde se buscarán los valores) debe ser aquélla que contenga los valores de la referencia, es decir, el valor seleccionado como llave en el primer argumento. Por lo tanto, en nuestro ejemplo, este rango comenzará en la columna donde se incluyen los números de facturas de la planilla de seguimiento.

Figura 6. En este caso, nuestra matriz comenzará a partir de la columna C. Figura 5. Cuando comencemos a incluir una función, Excel nos mostrará una ayuda activa.

EN EXCEL 2003

✱

En versiones anteriores de Microsoft Excel, para insertar una función nos podemos dirigir al menú Insertar/Funciones. La agrupación de las funciones es similar a la de Excel 2007, aunque hay algunas específicas que sólo se han incorporado en esta nueva versión. Si no encontramos algo, podemos recurrir a la ayuda.

18

Esta función incluye un tercer argumento que será el número de la columna que queramos obtener como resultado. En este caso, incluiremos un 3 para traer el importe de cada factura. Por último, debemos incluir un cuarto argumento, que le indicará a la función si los valores de la matriz donde debe realizar la búsqueda se encuentran ordenados o no. Si se incluye la palabra FALSO o se lo pone vacío, Excel asume que los valores se encuentran desordenados, mientras que si el valor es VERDADERO o se omite, asume que se encuentran ordenados. Al terminar, la función quedaría de la siguiente forma: =BUSCARV(A3;Seguimiento!C3:F24;3;).

19

Conciliación de información

1. EXCEL Y LA CONTABILIDAD

Figura 7. La hoja que posee los datos del departamento de ventas se ha denominado Seguimiento.

De esta forma, podremos obtener el valor de todas las facturas de la extracción de datos que se encuentren en el seguimiento del departamento de ventas y aquellos valores no encontrados arrojarán un error del tipo #N/A.

Las referencias relativas y absolutas Antes de copiar la función que creamos, debemos asegurarnos de que las referencias incluidas dentro de ella respondan a nuestras necesidades. Para corroborar eso, es necesario saber que existen dos tipos de referencias. Por defecto, todas las celdas o rangos que incluyamos dentro de fórmulas o funciones serán relativas. Al copiar a otra celda una fórmula que incluya una referencia de este tipo, la función quedará referenciada a la posición relativa que corresponda. Por su parte, las referencias absolutas son aquéllas que mantienen fijas las celdas o alguna parte de su dirección (columna o fila) para que al momento de copiar las fórmulas que contengan esas celdas, sus referencias se encuentren relacionadas con las mismas celdas de destino en forma invariable. Podemos decir, entonERRORES #N/A

❘❘❘

ces, que las referencias que posean nuestras fórmulas podrán ser relativas o absolutas según la tarea que estemos realizando, y serán fundamentales ya que nos facilitarán nuestro trabajo al momento de copiar fórmulas y funciones.

Para fijar filas o columnas tenemos que anteponer el signo $ a las coordenadas de la celda que queramos fijar. De esta forma, si deseamos fijar la columna de una referencia para la celda A1 introduciremos $A1, mientras que si lo que necesitamos es congelar la fila utilizaremos A$1. Esto nos permitirá que, al arrastrar fórmulas, las partes de la referencia que se encuentren fijas no sean alteradas. Si deseamos fijar filas y columnas a la vez, es decir, obtener una referencia totalmente absoluta, la dirección de la celda deberá ser $A$1. También podemos utilizar la tecla de funciones F4 para poder alternar entre las diferentes variantes de fijación. En nuestro ejemplo debemos fijar las celdas de la matriz donde buscamos los valores, para que al copiar la función al resto de las celdas la referencia del rango se mantenga. Por lo tanto, nuestra función quedaría de la siguiente forma: =BUSCARV(A3;Seguimiento!$C$3:$F$24;3;).

Más detalles Podemos mejorar de muchas formas lo que realizamos hasta ahora: tenemos la posibilidad de incluir algunas funciones adicionales en nuestra comparación para que al encontrar un valor en ambas planillas aparezca, por ejemplo, la palabra OK o ERROR si los valores no son encontrados. Gracias a esto, luego podremos aplicar algún ordenamiento o filtro sobre esta columna para analizar sólo las partidas con diferencias.

Para lograr esto, incluiremos una combinación de funciones SI (función condicional que nos permitirá evaluar un argumento y, de acuerdo con su resultado, realizar una acción diferente) y ESERROR (función que identifica los errores #N/A, #¡VALOR!, #¡REF!, #¡DIV/0!, #¡NUM!, #¿NOMBRE? y #NULO!).

Como resultado de esto, la función completa quedaría: =SI(ESERROR (BUSCARV(A4;Seguimiento!$C$3:$F$24;3;));”ERROR”;”OK”).

VALORES NO NUMÉRICOS DENTRO DE FUNCIONES

❘❘❘

Cuando las funciones de búsqueda, como BUSCARV y BUSCARH, no encuentren los

Dentro de las funciones podremos agregar como argumentos o resultados de éstas,

valores buscados en la matriz, arrojarán el error #N/A (siglas del inglés not availa-

valores no numéricos o de texto. En todos los casos, estos valores deben incluirse

ble, que significa no disponible). Más adelante aprenderemos a aplicar otras funcio-

entre comillas “”, por ejemplo, “OK” o “ERROR”. Sin embargo, si deseamos incluir

nes que nos permitirán administrar estos errores.

valores numéricos, las comillas no serán necesarias.

20

21

Conciliación de información

1. EXCEL Y LA CONTABILIDAD

La utilidad de filtros es muy poderosa y son muchas las opciones que nos posibilita realizar. En este caso, haremos un filtro sencillo para poder mostrar, solamente, aquellas partidas que tienen errores.

Figura 8. En funciones combinadas, los paréntesis () toman diferentes colores para permitirnos identificar los diferentes pares de apertura y cierre.

Filtros Ahora podemos insertar filtros para lograr que sólo se vean aquellas partidas que contienen errores. Dentro de la ficha de opciones Inicio, en el grupo Modificar, encontraremos el botón Ordenar y filtrar, que nos dará acceso a las herramientas que nos permitirán realizar filtros y ordenamientos.

Figura 10. Seleccionaremos el valor que queramos filtrar.

Luego de incorporar el filtro, desde los indicadores que se visualizan en los encabezados de cada columna, podremos acceder a las diferentes opciones de esta herramienta. Después de seleccionar el filtro que deseamos usar, sólo veremos las filas filtradas, es decir, aquéllas que cumplen con el criterio elegido.

Figura 11. También podríamos ordenar los valores para agrupar aquellas partidas con diferencias.

EN EXCEL 2003

✱

En la versión anterior de Excel, las herramientas de ordenamientos y filtros las encontraremos dentro del menú Datos. Si bien la finalidad es la misma, Excel 2007 posee una gran cantidad de herramientas adicionales y muy eficaces en este rubro,

Figura 9. Las herramientas de filtro y ordenamiento se encuentran en el mismo grupo.

22

que las versiones anteriores no tienen.

23

Conciliación de información

1. EXCEL Y LA CONTABILIDAD

Para completar la conciliación deberíamos realizar las mismas tareas en la planilla de seguimiento del departamento de ventas para poder, de esta forma, identificar las partidas que no se encuentren incluidas dentro de la extracción del sistema contable. Los pasos para realizar esto serían los siguientes: en primer lugar, incluir la combinación de funciones SI, ESERROR y BUSCARV para comparar los valores y, en segundo lugar, agregar filtros para poder identificar todas las partidas que no puedan conciliarse.

También podríamos seguir mejorando esto e incluir, por ejemplo, formatos condicionales para identificar, a través de colores, las partidas que mostraran ERROR. Esta herramienta (que estudiaremos más adelante) la encontraremos dentro de la ficha de opciones Inicio, en el grupo Estilos.

Algunos cambios Todo lo que hemos realizado funciona sólo cuando en las dos planillas tenemos un nexo (en este caso, el número de factura) que se encuentra expresado de la misma forma. ¿Qué sucede si este valor se encuentra con formato diferente en cada una de las planillas? Simplemente, nuestra comparación no funciona. Para resolver esto, realizamos algunas modificaciones en nuestra combinación de funciones para poder realizar la conciliación. Supongamos que la planilla del departamento de ventas incluye los números de las facturas, pero en un formato más simple con menor cantidad de caracteres.

Incluiremos algunas funciones adicionales a través de las cuales, desde la hoja donde están los datos de la extracción contable, podremos transformar el número de la factura en el mismo formato que el incluido en la planilla de seguimiento. Primero utilizaremos la función DERECHA (incluida dentro del grupo de funciones Texto), que nos permitirá extraer desde el extremo derecho una parte del número de la factura, y luego aplicaremos la función VALOR, del mismo grupo, para poder transformar en un valor numérico el resultado extraído por la función DERECHA. En principio, la combinación quedaría =DERECHA(A3;8). De esta forma, podremos extraer de 0001A00000469 el valor 00000469, por ejemplo. A continuación, aplicaremos =VALOR, quedando =VALOR(DERECHA(A3;8)) para transformar el número de la factura en un simple 469. Así, lograremos obtener una misma clave en las dos planillas y volver a aplicar todo esto dentro de la función =BUSCARV. La función completa quedaría de la siguiente manera: =SI(ESERROR(BUSCARV (VALOR(DERECHA(A3;8));’Seguimiento’!$C$3:$F$37;1;));”ERROR”;”OK”).

Al momento de realizar la comparación desde la planilla del departamento de ventas debemos conseguir nuevamente que los nexos que utilicemos dentro de la función =BUSCARV sean comparables. Podemos, por ejemplo, incluir una columna auxiliar que nos facilite la tarea, o tratar de transformar el valor de la factura, por ejemplo 469, en 0001A00000469. Tenemos la posibilidad de decidirlo según nuestra preferencia. Sólo debemos tener en cuenta que, en estos casos, las funciones de texto son nuestras aliadas.

Otras funciones que nos facilitarán tareas Existen otras funciones que nos ayudarán a resolver tareas de comparación: • BUSCARH: forma parte del grupo de funciones Búsqueda y referencia y actúa igual que BUSCARV, pero su objetivo es obtener datos en forma horizontal, con el reemplazo de columnas por filas.

LA MINI BARRA DE FORMATO

❘❘❘

La estética de una planilla es muy importante al momento de realizar trabajos profesionales. Por eso, Microsoft Excel 2007 incluye una mini barra de formato con todas las herramientas necesarias para ajustar las diferentes opciones de formato. Siempre estará a nuestra disposición con sólo presionar el botón derecho del mouse sobre algún lugar de nuestra planilla.

Figura 12. El número de la factura es un simple valor sin formato.

24

25

Tablas y tablas dinámicas

1. EXCEL Y LA CONTABILIDAD

• IZQUIERDA: nos permite extraer una parte del valor de una celda, pero partiendo desde la izquierda. Es la función inversa a DERECHA. • TEXTO: en contraposición con la función VALOR, ésta permite transformar un valor en un texto. La encontraremos dentro del grupo de funciones Texto.

• ESPACIOS: quita todos los espacios del texto excepto los individuales, que separan palabras. Nos servirá para realizar algunos ajustes cuando trabajemos con datos extraídos de otros sistemas que incluyan espacios de más que nos incomoden las comparaciones.

• CONCATENAR: también está incluida dentro del grupo de funciones Texto y nos permite unir cadenas de caracteres formando un único valor de texto. Si, por ejemplo, poseemos en la celda A1 un nombre y en B1 un apellido, podremos unir todo en una celda si escribimos =CONCATENAR(A1;” “;B1). Los argumentos son muy simples, y ” “ nos permitirá dejar un espacio vacío entre el nombre (valor de A1) y el apellido (valor de B1). Otra opción es usar, en lugar de esta función, el carácter & para relacionar cadenas de caracteres (para este ejemplo sería =A1&” “&B1).

la ficha de opciones Insertar, tenemos la posibilidad de convertir un rango de datos en una tabla. En nuestro ejemplo podríamos seleccionar cualquiera de las planillas (tanto la extracción del sistema contable como el seguimiento del departamento de ventas) y convertirla en una tabla.

TABLAS Y TABLAS DINÁMICAS Comenzaremos a trabajar con tablas y tablas dinámicas. Estas eficaces herramientas nos permitirán realizar muchísimas tareas relacionadas con el análisis de cuestiones contables y financieras. Veamos algunas de sus opciones.

Las tablas Nos servirán para simplificar la administración y el análisis de un grupo de datos relacionados. Con la opción Tabla, incluida dentro del grupo Tablas de LA FUNCIÓN EXTRAE

❘❘❘

Figura 13. Es posible ampliar el rango de la tabla a voluntad, según lo que necesitemos en cada momento.

La tabla nos permitirá ajustar el diseño de los datos, incorporar filtros en las diferentes columnas, e incluir, por ejemplo, una fila con totales donde podremos seleccionar, desde una lista desplegable, la operación que deseemos realizar. Existe otro tipo de tablas más interesante, que veremos a continuación.

EN EXCEL 2003

✱

Así como DERECHA e IZQUIERDA permiten extraer una porción del contenido de una

Las llamadas Tablas de Excel 2007 se denominan Listas en las versiones anteriores

celda comenzando desde un extremo, EXTRAE nos permitirá tomar una parte de la ca-

del programa. Esta herramienta la encontraremos dentro del menú Datos/Lista/

dena empezando desde cualquier punto intermedio. Esta función posee tres argumen-

Crear Lista… y veremos que las opciones y utilidades disponibles son muy similares

tos: la celda desde donde se realizará la extracción, el carácter a partir del cual co-

en ambas versiones. Como en otros casos, si necesitamos realizar algo en

menzaremos a extraer los datos y la cantidad de caracteres que deseemos obtener.

particular y no sabemos cómo, podemos visitar la ayuda del programa.

26

27

Tablas y tablas dinámicas

1. EXCEL Y LA CONTABILIDAD

Las tablas dinámicas Este tipo de tablas nos permite resumir datos y manipular grandes grupos de información. Por ejemplo, podríamos resumir una extracción de los movimientos de una cuenta contable agrupando su información por fechas o por cuentas de contrapartida. Los diferentes comandos relacionados con esta herramienta los utilizaremos para resolver una gran cantidad de problemas. Tomemos una de las planillas con la que estemos trabajando y resumámosla a través de una tabla dinámica. En este caso, resumiremos la lista de datos contables para agrupar aquellos movimientos de acuerdo con la moneda de la transacción. Al presionar el icono de Tabla dinámica, incluido dentro del grupo Tablas de la ficha de opciones Insertar, se activará el asistente que nos guiará en la construcción básica de una tabla dinámica. Luego podremos realizar muchas modificaciones respecto de nuestras necesidades.

Figura 15. Automáticamente, se reserva un área donde se incrustará la tabla dinámica.

Para nuestro ejemplo, en el recuadro Rótulos de fila incluiremos las monedas y en la sección de Valores introduciremos el campo IMPORTE. Esto lo lograremos al arrastrar con el mouse a cada una de las áreas, los campos en cuestión.

Figura 14. Por defecto, automáticamente se mostrará el rango de datos en el que nos encontramos situados.

En el primer paso del asistente debemos seleccionar el rango sobre el cual se aplicará la tabla dinámica. Es necesario que cada una de las columnas tenga un encabezado, que después se utilizará para denominarlas. Tras seleccionar el rango veremos un área de nuestra planilla donde se ubicará la tabla dinámica y una ventana para seleccionar la ubicación que tomará cada uno de los datos.

PEGAR Y REALIZAR OPERACIONES AL MISMO TIEMPO

❘❘❘

Dentro de las opciones de pegado especial podremos realizar algunas operaciones básicas al mismo tiempo. Es posible copiar una celda que contenga un valor numérico para luego poder realizar, en las celdas de destino del pegado, operaciones básicas con este valor copiado, como restar, sumar, dividir y multiplicar.

28

Figura 16. Para incluir un campo, debemos arrastrarlo con el mouse hasta la sección deseada.

29

Las validaciones de datos

1. EXCEL Y LA CONTABILIDAD

Además de mostrar la sumatoria de los importes de venta, entre los valores de la tabla dinámica podríamos introducir, por ejemplo, el conteo de la cantidad de facturas realizadas en cada una de las monedas.

Figura 17. Son varias las operaciones que podemos incluir como resultado de una tabla dinámica.

Excel 2007 está orientado a resultados, por lo tanto, cuando trabajemos con algunas herramientas específicas (como las tablas dinámicas), aparecerán fichas de opciones adicionales para ajustar las características de esos elementos.

En el caso de las tablas dinámicas, agrupadas con el nombre Herramientas de tabla dinámica, se visualizarán las fichas Opciones y Diseño, a través de las cuales podremos ajustar muchos detalles de estas herramientas.

EN EXCEL 2003

✱

LAS VALIDACIONES DE DATOS Una validación nos permite asegurar un ingreso de datos válidos por parte de un usuario. Muchas veces, dentro de las tareas de administración o de contabilidad necesitamos que se realice un ingreso de datos de forma manual.

Este tipo de tareas pueden ir desde la generación, a través de Excel, de una interfaz de datos que luego introduciremos en nuestro sistema contable o un seguimiento de las operaciones por vendedor que realiza algún departamento, hasta la registración de gastos de un fondo fijo.

Por ejemplo, imaginemos que debemos registrar todos los movimientos en la administración de un fondo fijo (también conocido muchas veces como caja chica). Para facilitar tareas posteriores es necesario que, con cada movimiento, se identifique claramente el concepto de la operación y, por ende, la cuenta contable sobre la cual debe realizarse la imputación. Para esto podemos utilizar la herramienta de validaciones de Excel, que nos permitirá ingresar siempre datos válidos y, así, eliminar la posibilidad de errores.

Es importante que todos los movimientos cuenten con un único criterio (por ejemplo, no es lo mismo indicar Dto. de ventas que Dto. Ventas) con el fin de que luego se puedan aplicar funciones, tablas dinámicas, etcétera, para resumir la información o establecer estadísticas. Como veremos, las validaciones se encargarán de ayudarnos a resolver esto.

Figura 18. Podremos apoyarnos en una tabla auxiliar para incluir los valores que serán restringidos a través de la validación.

ORDENAMIENTOS

✱

El asistente para la creación de tablas dinámicas de la versión 2003 de Microsoft Ex-

Podemos ordenar las celdas en forma ascendente o descendente según su conteni-

cel es diferente del de Excel 2007, que nos muestra una mayor cantidad de opcio-

do, pero también podemos hacerlo de acuerdo con su color de relleno, el color de

nes de formato y más simpleza para su creación. Cabe aclarar que en las versiones

su fuente, o su icono (en el caso de celdas con formato condicional, herramienta que

anteriores de este programa, hallaremos esta herramienta dentro del menú Datos/

utilizaremos más adelante). También debemos tener en cuenta que las celdas va-

Informe de tablas y gráficos dinámicos….

cías (en blanco) se ubicarán siempre en último lugar.

30

31

Las validaciones de datos

1. EXCEL Y LA CONTABILIDAD

En nuestro ejemplo incluiremos validaciones en los campos del tipo de movimiento y el concepto del gasto. Este último disparará la cuenta contable sobre la que se aplicará este importe. Para hacerlo, utilizaremos la función BUSCARV, tal como la explicamos anteriormente. En primer lugar, definiremos un rango de datos auxiliar donde incluiremos los valores válidos que formarán parte de la validación en cuestión.

Figura 20. Al presionar el comando Validación de datos aparecen otras herramientas para trabajar con datos.

En la sección Permitir elegiremos la opción Lista para luego poder definir, con el botón de la derecha del campo Origen, la sección de nuestra planilla donde se encuentran esos valores válidos. En este caso, utilizaremos la sección auxiliar que construimos anteriormente.

Figura 19. Podríamos incluir los valores dentro de la misma validación, pero eso nos restaría flexibilidad al momento de realizar cambios.

En la columna Tipo de Movimiento seleccionamos todas las celdas y luego nos dirigimos a la ficha de opciones Datos. Allí elegimos el comando Validación de datos del grupo Herramientas de datos. Al accionarlo, se abrirá el cuadro de diálogo principal de la herramienta. Aquí debemos marcar qué tipo de datos son los permitidos para las celdas seleccionadas. Adicionalmente, podremos incluir un mensaje que aparezca al situarnos en alguna de las celdas con restricciones (pestaña Mensaje de entrada), y un cuadro de diálogo que aparezca cuando se ingresen datos no válidos (pestaña Mensaje de error).

LA ALINEACIÓN POR DEFECTO

❘❘❘

Por defecto, los valores numéricos introducidos en las celdas se alinearán a la derecha, mientras que las cadenas de texto lo harán a la izquierda, en forma predeterminada. Al igual que los primeros, las fechas que sean ingresadas serán alineadas, en forma predeterminada, a la derecha.

32

Figura 21. En el campo Origen debe figurar el rango en el que se encuentra el listado de datos válidos.

Lo mismo realizaremos para las celdas de la columna Concepto, pero seleccionaremos, en el campo Origen, el rango que delimitará el ingreso de datos de este campo. Al utilizar las validaciones visualizaremos un indicador en cada una de las celdas, que nos permitirá abrir una lista desplegable para hacer la selección de los valores posibles. Éstos también podrán ser introducidos directamente y, en caso de coincidir con los datos válidos, se permitirá su ingreso pero, en caso contrario, se visualizará un cuadro de diálogo de error.

33

Aspectos básicos del sistema contable

1. EXCEL Y LA CONTABILIDAD

ASPECTOS BÁSICOS DEL SISTEMA CONTABLE

Figura 22. Se visualiza una lista desplegable con los valores incluidos en la validación.

Por último, en la columna Cuenta Contable agregaremos una combinación de las funciones SI, ESERROR y BUSCARV para que al seleccionar algunos de los conceptos restringidos a través de la validación, obtengamos la cuenta contable correcta. Esto nos permitirá no tener que ingresar manualmente la cuenta contable y se limitará, así, la posibilidad de errores. La combinación de funciones que agregaremos es =SI(ESERROR(BUSCARV(C4;$C$28:$D$38;2;));””; BUSCARV(C4;$C$28:$D$38;2;)).

La potencialidad de Microsoft Excel 2007 para ayudarnos en la realización de tareas relacionadas con la contabilidad y la administración es muy amplia.

Podremos hasta construir un verdadero sistema contable basado en Excel al utilizar una hoja de cálculo donde se introduzcan los asientos contables y una gran cantidad de validaciones de datos, para mantener la integridad de la información y realizar resúmenes. Tendremos la posibilidad, en otra hoja de nuestro proyecto, de resumir todos esos asientos con algunas fórmulas condicionales (SI, SUMAR.SI, etcétera) para luego, con tablas dinámicas, obtener el resumen de la información por cada cuenta contable.

Si nos embarcamos en el desarrollo de la creación de un sistema contable y nos valemos de Excel para hacerlo, debemos tener en cuenta algunas cuestiones específicas que nos salvarán de varios inconvenientes al momento de ponerlo en práctica. Entre ellas, podemos mencionar: • La inclusión de controles que nos permitan asegurar el balanceo de los asientos para que no tengamos problemas cuando queramos obtener un balance de sumas y saldos.

• La mejor manera es utilizar hojas diferentes para cada una de las tareas: la carga de asientos, el resumen de la información, la obtención de mayores, el cálculo de los estados contables, etcétera.

• Utilizar celdas y hojas con cálculos auxiliares.

• Utilizar validaciones para eliminar la posibilidad de errores.

• Realizar los resúmenes de la información a través de tablas dinámicas. De esta forma, podremos, rápidamente, modificar la información que estamos visualizando y manipular los datos según lo que necesitemos.

• Ser consistentes con la información y con los datos utilizados, es decir, siempre utilizar los mismos nombres y la misma simbología. Esto nos permitirá realizar resúmenes, estadísticas, etcétera.

EN EXCEL 2003

✱

Tanto la funcionalidad como la aplicación de la herramienta que nos permite realizar validaciones de datos son exactamente iguales en la versión 2007 y 2003 de Excel. Sus cuadros de diálogos poseen las mismas descripciones y los mismos criterios de selección. En la versión 2003, la encontraremos dentro del menú Datos/Validación….

Figura 23. De esta forma se visualizaría la planilla de fondo fijo.

34

35

Aspectos básicos del sistema contable

1. EXCEL Y LA CONTABILIDAD

Si bien no vamos a explicar cómo realizar toda esta tarea, sí vamos a mostrar algunos detalles. Por ejemplo, podremos definir en una hoja la inclusión de los asientos. Esta hoja deberá contar con las restricciones necesarias (validaciones que nos permitan ingresar sólo datos correctos y algunas funciones, como vimos anteriormente, que nos certifiquen la integridad de la información) para tratar de evitar errores.

Podemos incluir validaciones en las columnas referidas a los números de la cuenta contable y el centro de costo (CeCo), como también en la columna que defina si el valor corresponde al Debe o al Haber (D/H).

Luego, resulta muy útil incorporar funciones de búsqueda (como BUSCARV, por ejemplo) para poder visualizar las descripciones que correspondan a los números de las cuentas contables y los centros de costo. Para esto, podemos tener una tabla auxiliar que incluya los números de las cuentas contables y los centros de costo que utilizaremos como lista de la validación y, además, columnas con sus descripciones, las cuales tenemos la posibilidad de traer a nuestros asientos con un simple BUSCARV. Respecto de los mayores de las cuentas, podemos realizar tablas dinámicas a partir de los asientos. Para lograr esto, una opción que resulta muy recomendable es la de incluir algunas columnas con la información resumen por rubro contable, para luego utilizarlas en los resúmenes.

Figura 24. Algunas cuentas podrán tener un centro de costo (CeCo) asociado.

En nuestro caso, hemos definido una columna para incluir los importes y otra donde decidiremos si ese valor corresponde al Debe o al Haber. Si bien es posible realizar esta tarea de diferentes formas, lo más conveniente es tener una única columna que posea los importes de los asientos, ya que así se nos facilitará la realización de posteriores resúmenes.

Luego, a través de una columna (D/H), deberemos definir lo que corresponda. También es una buena práctica introducir siempre importes en positivo y que a través de esta misma columna se identifiquen sus valores.

EDICIÓN DE CELDAS

❴❵

Figura 25. Utilizamos columnas auxiliares para incluir los rubros y subrubros.

CONTROLES CONTROLADOS

✱

Cuando queramos modificar el contenido de una celda, deberemos dirigirnos a la

Para evitar errores, nos conviene incluir controles cruzados y hacer que nos avisen si

barra de fórmulas, introducir los cambios deseados y hacer doble clic sobre la cel-

hay algún problema en el funcionamiento de la planilla. Por ejemplo, es útil incorpo-

da en cuestión o presionar la tecla F2 de nuestro teclado, que también nos permiti-

rar una fórmula SI que muestre un mensaje cuando una celda que debiera completar-

rá acceder al modo edición para hacer las modificaciones que necesitemos.

se ya hubiera sido completada (o un mensaje de error si aun se encontrara vacía).

36

37

Los estados contables

1. EXCEL Y LA CONTABILIDAD

Lo aconsejable será usar la función BUSCARV para traer, en las columnas Rubro y Subrubro, los valores de acuerdo con el número de la cuenta contable correspondiente. La tabla dinámica la podremos adaptar a nuestras necesidades para mostrar la información como deseemos. Podríamos presentar los datos por Rubro, Subrubro y Cuenta. También podríamos confeccionar un detalle para analizar las cuentas de resultado teniendo en cuenta sus centros de costo.

LOS ESTADOS CONTABLES Ya sea que contemos con un sistema contable o que hayamos desarrollado con los tips anteriores una alternativa utilizando Excel, vamos a necesitar construir los estados contables básicos. Las opciones serán muchas y todo dependerá de cómo obtengamos la información de base. La mayoría de los sistemas contables nos permite definir una estructura de balance y cuenta de resultado que, en los mejores casos, podemos exportar directamente a Excel o, al menos, como un archivo de texto.

En muchas ocasiones tendremos que utilizar algunas de las herramientas de Excel para poder visualizar correctamente los datos, ya que es habitual que los sistemas de información, al exportarlos, representen la información como texto en lugar de valores, o se copien espacios, filas y columnas vacías en muchos lugares. Para resolver esto podremos acudir a algunas de las facilidades que nos provee Excel: desde funciones como TEXTO, VALOR, ESPACIOS, etcétera, hasta las herramientas de búsqueda y reemplazo.

Búsqueda y reemplazo Encontraremos estas herramientas en la ficha de opciones Inicio, dentro del grupo Modificar, bajo el nombre Buscar y seleccionar. También tendremos la posibilidad de ingresar con las teclas de acceso rápido: CTRL+B para las búsquedas y CTRL+L para los reemplazos.

Figura 26. Esta es una forma posible de realizar esta tarea con tablas dinámicas.

Por último, tendríamos que traducir esto en estados contables. Además, siempre que trabajemos con información, reportes, análisis, etcétera, será importante incorporar detalles estéticos en nuestras planillas.

El aspecto es una parte fundamental del trabajo y por eso Excel posee una gran cantidad de comandos y herramientas que nos permitirán mejorar el aspecto y la calidad de nuestros trabajos.

HACER ZOOM RÁPIDAMENTE

❘❘❘

Excel posee una herramienta de zoom que nos permitirá acercar o alejar el tamaño

Figura 27. Al presionar el botón Opciones aparecen varias alternativas para configurar estas herramientas.

de nuestra planilla. Para ajustar esto rápidamente, podremos utilizar el indicador que se halla en la parte inferior derecha de la pantalla, que se encuentra por defecto en 100%. Su valor mínimo es 10% y, máximo, 400%.

38

Gracias a estos comandos podremos, por ejemplo, adecuar los valores de la puntuación de los importes. Esto es útil porque, muchas veces, la configuración regional de los sistemas de información (y por lo tanto de sus extracciones) hace 39

Los estados contables

1. EXCEL Y LA CONTABILIDAD

que debamos modificar puntos por comas o viceversa para que los valores numéricos puedan usarse. En otros casos, las fechas de las operaciones se extraen con una separación diferente de la reconocida por la configuración regional de Windows, y en esa situación también podremos hacer los reemplazos necesarios. Veamos un simple ejemplo: contamos con una planilla de datos bajados de un sistema informático, que posee los separadores de miles y decimales (. y ,) invertidos respecto de la configuración regional de Windows.

Figura 29. En el campo Reemplazar con: no ingresaremos ningún valor y presionaremos el botón Reemplazar todos.

Figura 28. Para separar miles se visualiza una coma (,) y, para los decimales, un punto (.). Todos los importes son una cadena de texto en lugar de valores.

No es posible realizar cálculos con los importes porque Excel los considera texto. Podemos usar la herramienta de reemplazo para intercambiar los separadores por los que sean correctos de acuerdo con nuestra configuración regional. El primer paso siempre será eliminar el separador de miles, en este caso la coma (,), ya que si lo hacemos al revés Excel interpretará que la coma es nuestro separador de decimales y estaremos mostrando valores incorrectos. Entonces, primero reemplazaremos las (,) por nada, (ningún valor) para que desaparezcan.

EN EXCEL 2003

✱

Luego, de la misma forma, reemplazaremos los separadores de decimales (puntos) por los correspondientes para nuestra configuración regional, que en este caso son las comas. Automáticamente, veremos cómo los importes se transforman en valores numéricos (alineados por defecto a la derecha dentro de la celda) con los cuales podremos efectuar cálculos.

Cambiar signos Otro problema muy común con el que podremos encontrarnos es el de los signos de los importes, ya que muchas veces los valores se muestran como valores absolutos y no incluyen el signo necesario para indicar si son positivos o negativos. Una forma rápida de volver un grupo de valores negativos es al utilizar una de las opciones incluidas dentro de las herramientas de Pegado especial. Estos comandos los hallaremos dentro de las opciones LOS GRÁFICOS DINÁMICOS

❴❵

Las herramientas de búsqueda y reemplazo también están presentes en

Es posible crear representaciones gráficas, llamadas gráficos dinámicos, que po-

Microsoft Excel 2003. Los atajos de teclado para acceder a ellas son los mismos

seen el mismo dinamismo que una tabla, pero en lugar de representar un resumen

que en esta nueva versión y su funcionalidad es muy similar. Encontraremos

de datos, éstos serán expuestos a través de alguno de los diferentes tipos de gráfi-

estas utilidades en Edición/Buscar… y Edición/Reemplazar….

cos disponibles en Excel. Más adelante profundizaremos en este tema.

40

41

Los estados contables

1. EXCEL Y LA CONTABILIDAD

desplegables del icono Pegar de la ficha de opciones Inicio. Con esta utilidad, además de poder pegar sólo los valores y sólo las fórmulas o los formatos, entre otras opciones, podemos realizar operaciones al pegar. Tendremos la posibilidad, por ejemplo, de incluir en una celda el valor -1, copiarlo y, al pegar, utilizar la opción de pegado especial de multiplicación sobre las celdas a las que queramos cambiarle el signo. Al hacer esto, el valor se mantendrá inalterable, pero con el signo inverso.

Figura 31. Pegar mientras estamos realizando operaciones nos ayudará a ajustar rápidamente la exposición de nuestros trabajos.

En este caso, debemos elegir dentro de la sección Operación, la opción Dividir. A continuación, basta con presionar el botón Aceptar. De esta manera, los valores quedarán expresados en millones. Lo importante de hacer esto es que habremos conseguido mejorar la estética de la planilla al mismo tiempo que habremos logrado simplificar la lectura de los datos.

Figura 30. También podremos pegar vínculos con la opción que se muestra en la figura.

Esta herramienta también nos permitirá, cuando estemos trabajando con valores demasiado grandes, expresarlos en miles o millones, realizando divisiones por 1.000, 100.000 o 1.000.000, por ejemplo. Para lograrlo, al momento de pegar, debemos seleccionar la opción Pegado especial y luego, dentro de la sección Operación, elegir Dividir. Veamos un ejemplo: tenemos un reporte de ventas cuyos valores son muy grandes y podría ser de utilidad expresar todo en millones. Para esto usaremos una celda auxiliar donde introduciremos el valor 1.000.000. Lo copiaremos (podemos usar el atajo CTRL+C) y después, con la selección de los importes del reporte, usaremos Pegado especial.

EN EXCEL 2003

✱

El manejo de funciones en Excel 2003 es algo más limitado en cuanto a la posibilidad de incluir argumentos y, algunas funciones, ni siquiera están. Por ejemplo, las condicionales que tienen la posibilidad de incluir varias condiciones (SUMAR.SI. CONJUNTO, CONTAR.SI.CONJUNTO, PROMEDIO.SI.CONJUNTO) no existen.

42

Figura 32. Podríamos realizar una operación de multiplicación utilizando el valor inverso 0,000001, en lugar de 1.000.000.

43

Los estados contables

1. EXCEL Y LA CONTABILIDAD

El balance Retomemos el tema central: más allá de la utilización de este tipo de herramientas para mejorar el aspecto de los estados contables, en muchos casos tendremos que construirlos a partir de algunos datos, como pueden ser una extracción de cuentas contables en detalle o el resultado de nuestro sistema contable confeccionado en Microsoft Excel.

Para esto podremos utilizar algunas tablas dinámicas, aunque en este caso nos resultará bastante dificultoso obtener el resultado deseado. Quizá la forma más sencilla sea utilizar funciones con condiciones. La principal es SI, y es una función que nos permite analizar una determinada condición y, según su resultado, realizar una u otra acción. Es posible anidar estas funciones, es decir, incluir una dentro de otra y así elevar las posibilidades y, por lo tanto, las diferentes opciones. Los derivados de estas funciones también serán de gran ayuda, principalmente SUMAR.SI y SUMAR.SI.CONJUNTO, que nos permiten realizar sumatorias de valores cuando se cumplen una o varias condiciones.

Supongamos que estamos utilizando nuestro sistema contable confeccionado en Excel y que construiremos los estados contables en hojas diferentes y realizaremos sumatorias partiendo de los asientos contables.

La función SUMAR.SI posee tres argumentos, donde el primero será el rango de datos donde se encuentra la llave o nexo que incluiremos en el segundo argumento y, por último, tendremos que seleccionar el rango que deseemos sumar cuando se cumpla con la condición establecida. En nuestro ejemplo podremos utilizar los datos de la columna Subrubro, donde hallaremos la rúbrica Caja y Bancos, que será el segundo argumento, y luego tendremos la posibilidad de utilizar la columna de los importes que será el tercer argumento de nuestra función y sumará los importes correspondientes a la selección. De igual forma, construiremos otros reportes, como por ejemplo, un resumen de los gastos por centro de costo o, quizás, el detalle de algún rubro o subrubro en especial para realizar algún análisis pormenorizado. Después, utilizando las mismas herramientas y conceptos, podremos construir una cuenta de resultados. En todos los casos, nuestra necesidad será la que influya en su construcción.

… RESUMEN Durante este primer capítulo comenzamos a aplicar algunas funciones para poder realizar comparaciones de datos y extracciones de información, y descubrimos algunas que nos ayudan a realizar estas tareas con mayor facilidad. Luego, utilizamos tablas y tablas dinámicas para resumir información y manipular los datos de acuerdo con nuestras necesidades. Para finalizar, usamos validaciones para restringir el ingreso de datos, descubrimos una gran cantidad de detalles que debemos tener en cuenta al con-

Figura 33. Podemos trabajar sobre la estética de las planillas para mejorar su exposición, utilizar contraste de colores para resaltar cifras claves y tipografías de distinto tamaño, por ejemplo.

44

feccionar un sistema contable y comentamos algunos aspectos fundamentales para mostrar un balance de sumas y saldos.

45

✔ ACTIVIDADES 1 Siguiendo con el primer ejemplo que realizamos para efectuar una comparación de datos, continúe con el último ejercicio e incluya las funciones correspondientes para transformar, en el seguimiento del departamento de ventas, los números de facturas con el formato completo. Para esto va a necesitar utilizar algunas funciones como CONCATENAR, que permite unir cadenas de texto, o el caracter &.

2 Para practicar la utilización de la eficaz herramienta de tablas dinámicas, tome todas las planillas utilizadas hasta el momento para realizar comparaciones y cree tablas dinámicas para resumir los datos por el criterio que desee.

3 Confeccione un sistema contable simple con Excel. En la primera hoja incluya un detalle para la contabilización de los asientos con restricciones a través de validaciones y funciones, para homogeneizar elementos como los nombres de las cuentas contables, los rubros y los subrubros. Incluya centros de costos para todas las cuentas de resultado, separadas en tres grupos: Administrativos, Comerciales y Operativos. Establezca la mayorización de las cuentas contables a través de la función SUMAR.SI. 4 A partir de la construcción realizada en el punto anterior, establezca una cuenta de resultados que muestre por separado los costos y gastos de cada uno de los centros de costo.

5 Modifique la unidad de moneda de los estados contables confeccionados y para esto aplique las operaciones incluidas dentro de la herramienta Pegado especial.

46