SUPUESTO 6 Extractos de...

Excel para PYMES – Supuesto 6 Página 1

SUPUESTO 6 – Extractos de Contaplus

Se desea producir un informe con los movimientos de las cuentas de tesorería o

de terceros de la empresa, con la posibilidad de seleccionar los movimientos no

conciliados.

El proceso de

conciliación en

Contaplus

consiste en

colocar una

marca de

punteo () en

cada apunte de

la cuenta del

banco que se

compruebe

con el extracto

de la entidad.

En un extracto de Contaplus, aparecerá una columna con la cabecera “P/C” por

“Punteado/Casado” con una “X” si el apunte ha sido marcado con ese propósito.

Mediante Excel y MS Query podemos generar un extracto de cualquier cuenta y

decidir si deseamos que aparezcan todos los apuntes, o sólo los pendientes de

puntear.

Haremos este supuesto paso a paso para mostrar la aplicación de algunas de las

técnicas Excel aprendidas en secciones anteriores; repetiremos las explicaciones

ya vistas con la intención de que el usuario las interiorice cuanto antes y pueda

aplicarlas a sus propios modelos.

Comenzaremos con un libro Excel en blanco e invocaremos a MS Query mediante

el procedimiento que hemos visto anteriormente y que depende de la versión de

Excel instalada. Nos aseguraremos de utilizar el driver ODBC adecuado (Tablas

de Visual Foxpro).


Configuramos la conexión eligiendo Directorio de tablas libres, y buscaremos la

ruta donde residen los datos de la empresa Contaplus.

Hasta aquí, nada nuevo, pues hemos visto este proceso en secciones anteriores.

En la pantalla de Agregar tablas, tenemos que buscar dos: subcta (Subcuentas) y

diario (Diario de contabilidad).

Investigar dónde están los datos

Una investigación sobre las tablas de Contaplus utilizando MESP_00 arroja la

siguiente información sobre los datos que, en principio, parecen necesarios para

nuestro proyecto:

Tabla Campo Descripción

Subcta Cod Código de subcuenta

Titulo Descripción de la subcuenta

Diario Asien Número de asiento

Fecha Fecha del asiento

Subcta Subcuenta del apunte

Concepto Concepto del asiento

Eurodebe Importe al debe en euros

Eurohaber Importe al haber en euros

Estado Estado del punteo y conciliación

Documento Documento

Nos encontramos con la necesidad de RELACIONAR dos tablas, concepto que

mencionamos en secciones anteriores, y que ahora veremos de nuevo. Las tablas

que utilizaremos se relacionan mediante un campo que tiene información común:

el código de la subcuenta.

Hemos insistido en lo venerable (una forma elegante de decir antiguo) que es el

sistema de almacenamiento de la información en Contaplus / Facturaplus, y una

de las consecuencias es que las tablas (dbf) no contienen información sobre las

relaciones entre ellas. En DBMS más modernos estas relaciones están

almacenadas también en la base de datos.

Para nuestros fines, sin embargo, tenemos que indicar a MS Query cómo estas dos

tablas están relacionadas.


Relacionar las tablas

Afortunadamente, esto puede hacerse visualmente, conectando el campo “cod”

de la tabla “subcta” con el campo “subcta” de la tabla “diario”.

El procedimiento, recordemos, es localizar ambos campos en las tablas, pulsar

con el botón izquierdo del ratón en uno de ellos, y arrastrarlo hasta el de la otra

tabla, soltando entonces el botón.

En nuestro caso, el resultado se

ve en el panel de tablas de MS

Query: Una línea de RELACIÓN

aparece vinculando ambas tablas

mediante los campos que

acabamos de indicar.

¿Cuál es el efecto real de esta

relación?

MS Query creará un “registro

combinado” (virtual, no físico)

con los campos de las dos tablas,

siempre que “subcta” = “cod”.

Para cada apunte de la tabla de

diario, estará disponible, entre

otros, el campo “titulo” de la subcuenta, que se almacena en la tabla “subcta”.

Seleccionar los campos

Añadamos ahora al panel de datos los campos que utilizaremos en nuestro

informe, y que hemos listado en la tabla anterior.

Recordemos que basta con hacer doble click sobre el nombre del campo deseado

en los cuadros de tabla, y este se añadirá como una columna más al área de datos

de MS Query.

Es habitual referirse a los campos precediendo el nombre con el de la tabla

donde están y un punto. Así, el campo “cod” de “subcta” se denomina

“subcta.cod”.


Para nuestro ejemplo, añadiremos los campos siguientes:

diario.asien, diario.fecha, diario.subcta, subcta.titulo, diario.concepto,

diario.eurodebe, diario.eurohaber, diario.estado, diario.documento

El efecto en MS Query es el que se observa en la En la descripción de nuestro

proyecto, se especificaba que el

informe estaba pensado para

mostrar cuentas “de tesorería”.

Para que este informe sea útil,

deberá referirse a alguna

cuenta concreta que será

necesario indicar.

Incorporar criterios para seleccionar datos

Para activaremos el panel de Criterios de MS Query pulsando la opción

correspondiente en la barra de herramientas.

Figura—1 – Resultado inicial de la consulta


El Panel de Criterios aparece entre el de tablas y el de datos, y es muy similar al

que ya conocemos para el filtro avanzado de Excel. Seleccionaremos inicialmente

la primera cuenta

bancaria de la

empresa. Para ello

habrá que colocar una

expresión que MS

Query interprete como

“selecciona registros

en los que cod sea

igual a “5720000000”.

La Figura —2 ilustra el

efecto de colocar este

filtro.

Podemos probar el aspecto que esta

consulta tendrá en Excel, pulsando el

botón “Devolver datos” de la barra de

herramientas de MS Query.

Excel nos presenta la siguiente

pantalla:

Elegiremos ver los datos como una

tabla y, por razones que veremos

enseguida, situaremos los datos en

A5.

Al aceptar estas opciones, MS Query vuelca los datos de la consulta en el lugar

elegido. A partir de Excel 2007 el resultado es una Tabla que Excel reconoce

como tal, y sobre la que aplica automáticamente un Autofiltro (aunque sin

condiciones), y además formatea con el formato de tabla por defecto que esté

activo. Nosotros desactivaremos el filtro, que para nuestro propósito no se va a

utilizar. El aspecto de la consulta es el que se puede ver en la Figura —3.

Figura —2 – Aplicación de un criterio de filtro


Mejorar la consulta

Vamos a refinar esta consulta para asegurarnos de que los datos se presentan en

orden de fecha. Por defecto, los datos aparecerán

en el orden en que se hayan contabilizado los

asientos.

Para volver a MS Query y modificar la consulta, el

proceso cambia también entre Excel 2003 y Excel

2007. En ambos casos puede accederse pulsando el

botón derecho del ratón sobre una celda con datos

de la consulta, y seleccionar “Modificar consulta…”

(Excel 2003) o “Tabla” y “Modificar consulta…”

(Excel 2007 y ss).

Una vez en MS Query, seleccionaremos la columna

“fecha” pulsando sobre la cabecera, y MS Query la

mostrará sombreada. Seguidamente pulsaremos el

botón de ordenar A-Z en la barra de herramientas,

y si no está activada la consulta automática, pulsaremos “Actualizar consulta”1.

1 Si se pulsa el botón de “Consulta automática” de la barra de herramientas de MS Query, la

consulta se actualizará cada vez que realicemos un cambio por pequeño que este sea. Para consultas a tablas pequeñas o medianas, o si disponemos de un hardware muy rápido, esto es conveniente. Sin embargo, si las tablas contienen miles de registros o si realizamos varios cambios seguidos, debe

Figura —3 – Resultado inicial de la consulta

Figura —4 - Modificar consulta en Excel 2003


Ahora los datos se mostrarán

por orden de fechas aunque

no sea ese el orden en que

se incorporaron los asientos

a la contabilidad.

Tal como está en este

momento, la consulta es

válida para mostrar los

apuntes de la cuenta

“572000000” solamente. Si

queremos ver datros de otra

cuenta, tenemos que

modificar el panel de

criterios para seleccionar

una distinta. Podemos probar

esto mientras tenemos

abierto MS Query.

Pero una consulta de este tipo tendría un valor muy limitado. MS Query ofrece la

posibilidad de trabajar con consultas a las que es posible pasar parámetros.

Consulta con parámetro

En nuestro caso queremos pasar a MS

Query un parámetro en lugar del número

de cuenta fijo que hemos venido usando.

Para esto, sustituimos en el panel de

criterios el número de cuenta

(“572000000”) por una expresión como

“[indicar subcta]”. La expresión consiste

en un nombre de parámetro entre dos

corchetes.

El nombre del parámetro es irrelevante.

desactivarse esta opción y pulsar el botón de “Actualizar consulta” cada vez que se desee ver el efecto de los cambios.

Figura—5 - Modificar consulta en Excel 2007 y ss

Figura —6 – Parámetro de consulta


Por ejemplo, igualmente válido sería colocar “[código de subcuenta]” .

IMPORTANTE. En algunas combinaciones de Office

y Windows, dar al parámetro un nombre idéntico al

de un campo puede provocar resultados indeseados.

Conviene asignar a los parámetros nombres

especiales en todos los casos

Una vez hecho este cambio, si pulsamos el botón de “Actualizar consulta”, MS

Query mostrará una pantalla solicitando el

valor del parámetro, que podremos

introducir desde el teclado. Como es

evidente, será válido cualquier código de

cuenta correcto, aunque MS Query no

discriminará la información. Si escribimos un

código de cuenta erróneo, o con el número

de dígitos equivocado, el resultado de la consulta será “ningún registro”. En

nuestro ejemplo, la cuenta “570000000” indicada como valor del parámetro,

producirá los apuntes de la cuenta de Caja de nuestra empresa de pruebas.

Este resultado está bien, y sería operativo si estuviésemos usando MS Query de

forma autónoma. Pero este no es el caso. Necesitamos un método para pasar el

parámetro de forma más automática (o “transparente”, como se ha puesto de

moda decir), así que daremos un paso más.

De momento, volveremos a Excel pulsando el botón de “Devolver datos”

que vimos anteriormente.

Una vez en la hoja colocaremos el texto “Subcuenta” en la celda B2, e

iluminaremos con un color vivo, por ejemplo amarillo, la celda B3.

Luego pulsamos el botón derecho del ratón teniendo seleccionada una celda con

datos de la consulta, y seleccionamos la opción “Parámetros”, que en la Figura —4

(Excel 2003) y la Figura—5 (Excel 2007 y ss), estaba deshabilitada, pero que ahora

veremos que está disponible.


De entre las opciones que ofrece la pantalla “Parámetros” (Figura Figura—7),

estaba seleccionada la primera, pero la cambiamos por “Tomar el valor de la

siguiente celda”, y seleccionamos la C2, que acabamos de iluminar en amarillo.

También marcamos la opción de “Actualizar automáticamente cuando cambie el

valor de las celdas” y pulsamos Aceptar.

A partir de este momento, bastará escribir un número de subcuenta en la celda

C2, para que Excel envíe a MS Query este número, seguido de una solicitud de

actualización de la consulta. MS Query devolverá a Excel los datos, y nuestra tabla

quedará actualizada.

Mejorar la presentación

Figura—7 – Obtener el valor de los parámetros


El lector que haya seguido

este supuesto hasta este

punto, podrá realizar

distintas pruebas y

comprobar cómo los datos

se actualizan correctamente

en cada ocasión. Sin

embargo, Excel es a veces

demasiado diligente y

acomoda el ancho de las

columnas a la información

que recibe, lo que da como

resultado un cambio de

aspecto de la hoja que no es

deseable.

Este comportamiento, así

como algunas otras opciones

del rango de datos pueden cambiarse, aunque el método es distinto según la

versión de Excel. En 2003 el

menú contextual (Figura —4)

incluye la opción

“Propiedades del rango de

datos”, que muestra una

pantalla con múltiples

opciones (Figura—8). Para

nuestro supuesto, bastará

con quitar la marca a

“Ajustar el ancho de la

columna”.

En Excel 2007 y 2010, sin

embargo, los resultados de

la consulta no se guardan

directamente en un rango

de la hoja, sino en una Tabla

y por consiguiente, la opción

Figura—8 – Propiedades del rango de datos

Figura—9 - Propiedades de los datos externos


“Propiedades de los datos externos” (Figura—5) ofrece menos posibilidades de

personalización, pero sólo porque las demás opciones son comunes a todas la

tablas, y aparecen en la cinta de opciones “Herramientas de tabla”.

La Figura—9 muestra la pantalla de “Propiedades de los datos externos junto con

algunas de las opciones de la cinta referidas a tablas.

En este caso también es posible desmarcar el ajuste del ancho de la columna

fácilmente.

Otro cambio que podemos hacer para mejorar el modelo, es incluir totales y el

cálculo del saldo de la subcuenta.

El usuario que haya tenido la paciencia de llegar hasta este punto sólo se

conformará con soluciones muy profesionales, así que las fórmulas a incluir deben

contener cierto nivel de sofisticación.

La Figura—10 muestra el resultado de colocar unas fórmulas que se adaptan

automáticamente al número de filas de datos que contenga la consulta. También,

en el caso del saldo, se prevé que este pueda ser deudor o acreedor, y que se

sitúe en la columna correspondiente en su caso.

Figura—10 – Las fórmulas de totales y saldo


Para visualizar la consulta, se ha utilizado la posibilidad de “Inmovilizar paneles”

de manera que las cinco primeras filas de la hoja, que incluyen el encabezamiento

de las columnas, permanecen visibles siempre.

El modelo ya funciona, excepto que muestra siempre todos los apuntes de la

cuenta. Recordemos que el requisito era que el usuario tuviera la opción de

mostrarlos todos, o solamente los no conciliados.

Añadir otro parámetro

Si vemos los movimientos de la cuenta del banco, habermos observado que los

conciliados tienen una “X” en el campo “estado”. Tenemos que buscar un medio

de pasar a MS Query un parámetro que añadir al panel de criterios, y seleccionar

entre “todos los registros” y “sólo registros sin conciliar”.

Como casi siempre, hay muchas maneras de llegar a un resultado que funcione.

Proponemos a continuación uno de los posibles, que se nos antoja sencillo.

Para MS Query, “todos los registros” son aquellos cuyo campo “estado” contiene

cualquier cosa (realmente, Contaplus sólo usa X, C, P o un espacio en blanco), y

los “registros no conciliados” son aquellos cuyo campo “estado” contiene un

espacio en blanco. Por tanto, si la condición que pasamos a MS Query fuera del

tipo:

Estado <> [concil]

Si el parámetro [concil] es una X,

mostrará sólo los registros con el

estado en blanco, es decir, los

pendientes de conciliar (o con P o

C, que intervienen en procesos de

casación de partidas si se usa esta

opción de Contaplus).

Si el parámetro [concil] es un

carácter no utilizado por

Contaplus, por ejemplo una Ñ,

mostrará todos los registros (con

el estado en blanco o con una X).

Figura—11 – Segunda condición para la consulta


Probemos si esto funciona, volviendo a “Modificar consulta”.

Añadiremos al panel de criterios una segunda condición, tal como se aprecia en la

Figura—11.

Si Actualizamos la consulta, MS Query nos pedirá valores para ambos parámetros,

y podemos realizar las pruebas que queramos.

Si volvemos a Excel y cambiamos el código de subcuenta en C2, Excel aún

necesita conocer el valor del segundo parámetro, y nos lo pedirá.

Sólo nos queda buscar una forma de pasar este segundo parámetro también de

manera automática, y para ello utilizaremos uno de los controles de formulario

que estudiamos en la sección correspondiente: un par de botones de opción, a los

que denominaremos respectivamente “Ver todos los movimientos” y “Ver sólo

pendientes de conciliar”.

Como recordaremos, ambos botones estarán vinculados a la misma celda que

tendrá el valor 1 o 2 según esté activado el primero o el segundo botón.

En nuestro caso, vincularemos los botones a una celda fuera de la zona del

informe, por ejemplo, la M1.

En la celda M2, colocamos la fórmula siguiente:

=SI(M1=1;"Ñ";"X")

Figura—12 – Resultado de la consulta con dos condiciones


Finalmente, seleccionamos de

nuevo la opción “Parámetros”,

y la pantalla que aparece

mostrará ahora la existencia de

los dos que hemos definido.

Seleccionaremos el segundo, e

indicaremos que MS Query

deberá tomar el valor de la

celda M2, y que la consulta deberá actualizarse si cambia este valor.

Algunas mejoras al modelo

Nuestro modelo de consultas funciona razonablemente bien, y hemos conseguido

un buen nivel de automatización ¡sin escribir ninguna macro!

Sin embargo, hay algunas cosas que podemos mejorar con los conocimientos

adquiridos hasta ahora. Por ejemplo:

a. Deberíamos poder seleccionar el código de la cuenta de una lista

desplegable. Así no será necesario memorizar los códigos, y además

aseguramos que el código siempre corresponderá a una cuenta existente.

b. La columna de “titulo” de la subcuenta es redundante. No es necesario

que aparezca en todas las filas, habría que colocar el nombre de la

subcuenta en la cabecera y eliminar la columna.

c. Un

apunte

contab

le no

tiene

import

e al

mismo

tiempo

en el

Debe

y en el

Haber. Figura—13 – Consulta en la hoja 2, primer paso


Sería más legible el informe si en lugar de ceros, estos campos se

mostraran en blanco si el valor es cero.

Vamos a incorporar estas mejoras en nuestro modelo y lo haremos paso a paso.

Ya sabemos cómo insertar un cuadro combinado en la hoja. Lo que ahora

debemos lograr es incorporar la lista de las subcuentas que sirva de origen de

datos al cuadro. Para esto está MS Query.

En la Hoja2 del modelo, y con la celda A1 seleccionada, activaremos MS Query

como ya hemos visto. Indicaremos que el Origen de datos es “Tablas de Visual

Foxpro”, e identificaremos la carpeta de los datos para que sea exactamente la

misma que en la consulta de la Hoja1.

En la pantalla de Agregar tablas, elegiremos “subcta”, y de esta tabla elegiremos

dos campos: “cod” y “titulo”. Con la columna “cod” seleccionada, pulsaremos el

botón de ordenar A-

Z. El resultado de

estos pasos debería

ser similar al que

muestra la Figura—

13.

Tenemos un problema, y es que en la lista desplegable queremos que aparezca

tanto el código de la cuenta como su título. Sin embargo, la lista sólo muestra una

columna. Una solución es pedir a

MS Query que “construya” una

columna nueva que combine esos

dos campos.

Para ello, elegiremos del menú de

MS Query la opción “Registros”, y

seguidamente “Agregar

columna…”.

MS Query muestra la pantalla

correspondiente, en la que introduciremos la fórmula:

trim(cod) + " - " + titulo


Que da como resultado el código y el título separados por un guión.

La Tabla 15 - Funciones ODBC de Texto, y siguientes, incluidas en este capítulo,

muestran las funciones disponibles con este DBMS.

Como título de la columna escribiremos, por ejemplo, “Codigo_y_Titulo”, y

seguidamente pulsaremos el botón Agregar.

La consulta en MS Query tiene ahora el aspecto que muestra la Figura—14

después de haber ajustado el ancho de la nueva columna para ver mejor el

resultado de la fórmula.

Pulsando el botón de “Devolver datos”, MS Query vuelve a Excel, donde

confirmaremos que queremos la información en la Hoja2, celda A1.

Volviendo a la Hoja1 donde tenemos nuestro informe, podemos ahora dibujar un

cuadro combinado, por ejemplo a la derecha de la celda C2. En la pantalla de

Formato, indicamos como Rango de entrada la columna “codigo_y_titulo” de la

hoja2 (excluyendo el título de la columna), y vinculamos el control, por ejemplo,

con la celda M3 situada a la derecha, fuera del área del informe.

Figura—14 – Consulta con columna calculada


A partir de ahora, al seleccionar una cuenta en el cuadro combinado, podremos

usar la celda M3 para extraer el código de cuenta.

Podemos colocar en la celda C2 una fórmula que dé como resultado el código de

cuenta que MS Query usará para actualizar la consulta. La fórmula será:

=INDICE(Hoja2!A2:A81;Hoja1!M3)

En Excel 2007 y 2010, como ya hemos advertido, MS Query coloca los datos en

una Tabla que Excel reconoce como tal. Por eso, cuando colocamos la fórmula es

posible que Excel identifique el rango Hoja2!A2:A81 como el campo “cod”, y

escriba la fórmula como:

=INDICE(Tabla_Consulta_desde_Visual_FoxPro_Tables3[cod];Hoja1!M3)

Figura—15 – Definir cuadro combinado

Figura—16 – Aspecto final de la consulta


Ahora podemos eliminar las columnas “subcta” y “titulo” de la consulta, ya que el

código y el título de la subcuenta aparecen en el cuadro combinado. Esto es tan

sencillo como volver a MS Query, seleccionar la columna “titulo” y pulsar

Suprimir.

De vuelta a Excel, hay que corregir el ancho de las columnas y revisar las

fórmulas de totales y saldo, que habrán quedado desplazadas. También podemos

desplazar el control de cuadro combinado para que cubra la celda C2 que ya

sólo contiene información también visible en el cuadro. La celda en sí sigue

siendo necesaria porque MS Query toma de ella el parámetro.

Para que no se muestren los valores cero, hay dos métodos posibles:

1) Que no se muestre ningún valor cero en la hoja.

2) Dar formato a las celdas donde se desee que no aparezcan los ceros.

El primer método varía según la versión de Excel.

En Excel 2003, pulsando Herramientas / Opciones aparece la pantalla de

Opciones. En la pestaña “Ver”, hay “Opciones de ventana”, y una de ellas es

“Valores cero” que puede marcarse (ver ceros) o no (no ver ceros).

En Excel 2007 y 2010 hay que activar las Opciones de Excel y bajo Avanzadas,

buscar “Mostrar opciones para esta hoja”, donde se podrá marcar o desmarcar

“Mostrar un cero en celdas que tienen un valor cero”.


Esta opción es específica para cada hoja del libro.

El segundo método consiste en aplicar a las columnas de “eurodebe” y

“eurohaber” el formato personalizado:

#.##0,00;-#.##0,00;;

Que, si repasamos la sección correspondiente, veremos que oculta los valores

cero.

Como producto final de todo este esfuerzo tenemos una hoja conectada a

Contaplus, donde es posible producir el extracto de cualquier cuenta de la

empresa elegida. “Conectada” significa que si se incorpora nueva información a

Contaplus, aparecerá al actualizar la consulta,

bien porque cambiemos la subcuenta

seleccionada, o bien pulsando “Actualizar

todo”.

Si utilizamos la posibilidad de “punteo” que

ofrece Contaplus para marcar las partidas que se van comprobando, es posible

preparar extractos de partidas “pendientes”.

Figura—17 – Mostrar/ocultar valores cero


Podemos dar formato a la hoja como mejor nos parezca. Incluir el logotipo de la

empresa o cualquier otro elemento es muy sencillo.

Disponer de la información en Excel permite copiarla y pegarla con facilidad a un

e-mail donde se envían los detalles de la cuenta, a una carta en Word donde se

confirman las partidas pendientes a los auditores, a un archivo de texto… las

posibilidades son muy numerosas.

Si se trata de la cuenta de un cliente, por ejemplo, añadir cálculos de recargo

financiero es muy sencillo al disponer de importes y fechas en Excel.

SUPUESTO 6 Extractos de...

Documents

Transcript of SUPUESTO 6 Extractos de...