Como Hacer un Sistema Contable Con Microsoft Excel Cap. 002
Registros
Los
registros/asientos contables son la información básica de cualquier sistema
contable. Para lograr generar los registros/asientos contables construiremos una
plantilla base para ingresar datos.
Plantilla para cargar registros contables
Crearemos
una plantilla en la cual realizaremos nuestras aplicaciones contables, basados
en el principio de partida doble, incorporaremos una macro para volcar todos los
datos necesarios a una tercera hoja, para poder acumular los datos de cada uno
de los registros que carguemos, como si fuese una base de datos, la cual nos
servirá para la emisión de los reportes que realicemos.
Así
también implementaremos controles que se le apliquen a la plantilla, los cuales
son fundamentales para que no se guarden/vuelquen en nuestra Base de Datos asientos
incompletos o erróneos.
Hoja:
Registros | Celda x Celda
1- Celda
A1: Solamente está reducida al
mínimo tamaño que ocupa un carácter, puedes utilizar el que prefieras.
2- Celda
C2: Contiene el texto “Partida #”.
3- Celda
D2: Contiene el numero de partida
que se digitara.
4- Celda
F2: Contiene el texto “Fecha:”.
5- Celda
G2: Contiene la formula de la fecha
actual.
a. Formula:
=AHORA()
6- Celda
C4: Contiene el texto “Concepto General:”.
7- Celda
D4 a G4: Celdas combinadas para formar un solo espacio donde digitaremos
el concepto general de la partida/asiento que estemos generando.
8- Celda
C6: Contiene el texto “Tipo:”.
9- Celda
D6: Contiene una lista desplegable
de los tipos de partida que podemos registrar.
a. Seleccionamos
la Celda D6.
b. Nos
dirigimos a la pestaña Datos y en la
sección Herramientas de Datos,
Seleccionamos la opción: Validación de Datos.
c.
En la pestaña Configuración, vamos a la opción Permitir y seleccionamos: Lista, en la opción Origen, digitamos: Diario, Provisión, Ingreso, Egreso, Ajuste, Liquidación, Apertura.
10- Celda
F5: Contiene el texto “Total Cargos”.
11- Celda
F6: Contiene la formula por medio de
la cual se obtiene el valor total de cargos.
a. Formula:
=SUMAR.SI(C10:C69,">0",F10:F69)
12- Celda
G5: Contiene el texto “Total Abonos”.
13- Celda
G6: Contiene la formula por medio de
la cual se obtiene el valor total de abonos.
a. Formula:
=SUMAR.SI(C10:C69,">0",G10:G69)
14- Celda
F7: Contiene el texto “Diferencias”.
15- Celda
G7: Contiene la formula por medio de
la cual se obtiene el residuo del valor total de cargos menos el valor total de
abonos.
a. Formula:
=F6-G6
16- Celda
B9: Contiene el texto “Posteo” y de la Celda: B10 a B69: Contiene la formula que nos indicara si la cuenta que estamos
digitando es o no de posteo.
a. Formula:
=SI.ERROR(BUSCARV(C10,Catalogo!A:K,11,0),"")
17- Celda
C9: Contiene el texto “Cuenta” y de la Celda: C10 a C69: Celdas en las que digitaremos el código/número de cuenta que
deseamos afectar.
18- Celda
D9: Contiene el texto “Nombre de Cuenta” y de la Celda: D10 a D69: Contiene la formula que nos dirá el nombre de la cuenta que
estamos digitando y nos dará un mensaje de error si dicha cuenta no existe.
a. Formula:
=SI(C10=0,"
",SI.ERROR(BUSCARV(C10,Catalogo!A:K,8,0),"----- No Existe
-----"))
19- Celda
E9: Contiene el texto “Concepto” y de la Celda: E10 a E69: Celdas en la que digitaremos el concepto del
movimiento/afectación a la cuenta.
20- Celda
F9: Contiene el texto “Cargo” y de la Celda: F10 a F69: Celdas en las que digitaremos los valores de cargo.
21- Celda
G9: Contiene el texto “Abono” y de la Celda: G10 a G69: Celdas en las que digitaremos los valores de abono.
22- Celda
H1: Contiene el texto “Clave” y de la Celda H10 a H69: Celdas que contienen la formula que nos indicara una clave que
nos será de utilidad posteriormente.
a. Formula:
=SI.ERROR(CONCATENAR(I10,O10),"")
23- Celda
I1: Contiene el texto “Partida” y de la Celda I10 a I69: Celdas que contendrán la formula que nos indicara la partida
que estamos registrando.
a. Formula:
=SI(C10<>0,$D$2," ")
24- Celda
J1: Contiene el texto “Fecha” y de la Celda J10 a J69: Celdas que contendrán la formula que nos indicara la fecha del
registro que estamos realizando.
a. Formula:
=SI(C10<>0,$G$2," ")
25- Celda
K1: Contiene el texto “Concepto General” y de la Celda K10 a K69: Celdas que contendrán la formula que nos indicara el concepto
general de la partida que estamos registrando.
a. Formula:
=SI(C10<>0,NOMPROPIO($D$4),"
")
26- Celda
L1: Contiene el texto “Tipo Partida” y de la Celda L10 a L69: Celdas que contendrán la formula que nos indicara el tipo de
partida que estamos registrando.
a. Formula:
=SI(C10<>0,$D$6," ")
27- Celda
M1: Contiene el texto “Clasificación” y M10 a M69: Celdas que
contendrán la formula que nos indicara la clasificación de la partida que
estamos registrando.
a. Formula:
=SI.ERROR(BUSCARV(C10,Catalogo!A:K,9,0),"")
28- Celda
N1: Contiene el texto “No Mes” y de la Celda N10 a N69: Celdas que contendrán la formula que nos indicara el numero
del mes en que estamos realizando el registro.
a. Formula:
=SI.ERROR(MES(J10),"")
29- Celda
O1: Contiene el texto “Mes” y de la Celda O10 a O69: Celdas que contendrán la formula que nos indicara el nombre
del mes en que estamos realizando el registro.
a. Celda
H5: Contiene el texto “CodMes” y de la Celda I5 a T5: Celdas que contendrán los números del 1 al 12 respectivamente.
b. Formula:
=SI.ERROR(BUSCARH(N10,$H$5:$T$6,2,0),"")
30- Celda
P1: Contiene el texto “Contar” y de la Celda P10 a P69: Celdas que contendrán la formula que nos indicara cuantos
dígitos tiene el código de cuenta que estamos registrando.
a. Formula:
=SI(LARGO(C10)=0,"",LARGO(C10))
31- Celda
Q1: Contiene el texto “Nivel” y de la Celda Q10 a Q69: Celdas que contendrán la formula que nos indicara el nivel al
que pertenece la cuenta que estamos registrando.
a. Formula:
=SI.ERROR(BUSCARH(P10,Catalogo!$B$3:$G$4,2,0),"")
32- Celda
R1: Contiene el texto “1” y de la Celda R10 a R69: Celdas que
contendrán la formula que nos dirá la cuenta de nivel 1 de la que depende la
cuenta que estamos registrando.
a. Formula:
=SI.ERROR(CONCATENAR(VALOR(EXTRAE(C10,$R$1,INDICE(Catalogo!$B$3:$G$3,COINCIDIR($R$1,Catalogo!$B$4:$G$4,0)))),"
-
",BUSCARV(VALOR(EXTRAE(C10,$R$1,INDICE(Catalogo!$B$3:$G$3,COINCIDIR($R$1,Catalogo!$B$4:$G$4,0)))),Catalogo!A:K,8,0)),"")
33- Celda
S1: Contiene el texto “2” y de la Celda S10 a S69: Celdas que
contendrán la formula que nos dirá la cuenta de nivel 2 de la que depende la
cuenta que estamos registrando.
a. Formula:
=SI.ERROR(CONCATENAR(VALOR(SI(Q10>$R$1,EXTRAE(C10,1,INDICE(Catalogo!$B$3:$G$3,COINCIDIR($S$1,Catalogo!$B$4:$G$4,0))),"
"))," -
",BUSCARV(VALOR(SI(Q10>$R$1,EXTRAE(C10,1,INDICE(Catalogo!$B$3:$G$3,COINCIDIR($S$1,Catalogo!$B$4:$G$4,0))),"
")),Catalogo!A:K,8,0)),"")
34- Celda
T1: Contiene el texto “3” y de la Celda T10 a T69: Celdas que
contendrán la formula que nos dirá la cuenta de nivel 3 de la que depende la
cuenta que estamos registrando.
a. Formula:
=SI.ERROR(CONCATENAR(VALOR(SI(Q10>$S$1,EXTRAE(C10,1,INDICE(Catalogo!$B$3:$G$3,COINCIDIR($T$1,Catalogo!$B$4:$G$4,0))),"
"))," -
",BUSCARV(VALOR(SI(Q10>$S$1,EXTRAE(C10,1,INDICE(Catalogo!$B$3:$G$3,COINCIDIR($T$1,Catalogo!$B$4:$G$4,0))),"
")),Catalogo!A:K,8,0)),"")
35- Celda
U1: Contiene el texto “4” y de la Celda U10 a U69: Celdas que
contendrán la formula que nos dirá la cuenta de nivel 4 de la que depende la
cuenta que estamos registrando.
a. Formula:
=SI.ERROR(CONCATENAR(VALOR(SI(Q10>$T$1,EXTRAE(C10,1,INDICE(Catalogo!$B$3:$G$3,COINCIDIR($U$1,Catalogo!$B$4:$G$4,0))),"
"))," - ",BUSCARV(VALOR(SI(Q10>$T$1,EXTRAE(C10,1,INDICE(Catalogo!$B$3:$G$3,COINCIDIR($U$1,Catalogo!$B$4:$G$4,0))),"
")),Catalogo!A:K,8,0)),"")
36- Celda
V1: Contiene el texto “5” y de la Celda V10 a V69: Celdas que
contendrán la formula que nos dirá la cuenta de nivel 5 de la que depende la
cuenta que estamos registrando.
a. Formula:
=SI.ERROR(CONCATENAR(VALOR(SI(Q10>$U$1,EXTRAE(C10,1,INDICE(Catalogo!$B$3:$G$3,COINCIDIR($V$1,Catalogo!$B$4:$G$4,0))),"
"))," -
",BUSCARV(VALOR(SI(Q10>$U$1,EXTRAE(C10,1,INDICE(Catalogo!$B$3:$G$3,COINCIDIR($V$1,Catalogo!$B$4:$G$4,0))),"
")),Catalogo!A:K,8,0)),"")
37- Celda
W1: Contiene el texto “6” y de la Celda W10 a W69: Celdas que
contendrán la formula que nos dirá la cuenta de nivel 6 de la que depende la
cuenta que estamos registrando.
a.
Formula: =SI.ERROR(CONCATENAR(VALOR(SI(Q10>$V$1,EXTRAE(C10,1,INDICE(Catalogo!$B$3:$G$3,COINCIDIR($W$1,Catalogo!$B$4:$G$4,0))),"
"))," -
",BUSCARV(VALOR(SI(Q10>$V$1,EXTRAE(C10,1,INDICE(Catalogo!$B$3:$G$3,COINCIDIR($W$1,Catalogo!$B$4:$G$4,0))),"
")),Catalogo!A:K,8,0)),"")
Notas:
Cada formula, en aquellos casos en los que se
indican las Celdas 10 a la 69 de la misma columna, pertenece a la fila 10,
digítala en dicha celda y copia hacia abajo hasta la celda 69.
Como indique anteriormente, los formatos son a gusto
del cliente, en la imagen de la hoja registros observaras el formato que yo he
aplicado.