aplica excel contable.com – modulo...

INDICE

INDICE------------------------------------------------------------------------------------------------------- 1

Diseñando Tú Plantilla Contable en Excel.--------------------------------------------------------- 4

DISEÑANDO TU FORMULARIO DE ASIENTO DE DIARIO. ----------------------------------------------- 4

Dibujando Botones del Formulario Asiento de Diario. ------------------------------------------------------------- 5

DISEÑANDO TU FORMULARIO COMPROBANTE DE INGRESO. ---------------------------------------- 7

DISEÑANDO TU FORMULARIO COMPROBANTE DE EGRESO. ----------------------------------------- 8

Rediseñando la Hoja E. ----------------------------------------------------------------------------------------------------- 8

DISEÑANDO TU FORMULARIO NOTA DE DEBITO. -------------------------------------------------------- 8

DISEÑANDO TU FORMULARIO DE NOTA DE CREDITO. ------------------------------------------------- 9

Diseñando Tú Libro Diario En Excel. ------------------------------------------------------------------------- 9

Diseñando Tú Libro Mayor en Excel. ----------------------------------------------------------------------- 10

Diseñando Tu Balance de Comprobación. ---------------------------------------------------------------- 10

Creando Tú Catalogo de Cuenta. ---------------------------------------------------------------------------- 11

Diseñando Tu Formato de Estado de Resultado. ------------------- ¡Error! Marcador no definido.

Diseñando Tu Formato de Balance General. ------------------------ ¡Error! Marcador no definido.

Desarrollando TU Plantilla Contable en Excel. ------------------------------------------------- 12

NOMBRANDO CELDAS DE LA HOJA D, I, E, ND Y NC. -------------------------------------------------- 12

Nombrando Celdas del Libro Diario . ---------------------------------------------------------------------- 12

Nombrando Celdas de la Hoja Balance. ------------------------------------------------------------------- 13

Nombrando Celdas en la Hoja Balance General. ------------------- ¡Error! Marcador no definido.

Nombrando Celdas en la Hoja Estado de Resultado. -------------- ¡Error! Marcador no definido.

Agregando Formulas al formulario asiento de diario. ------------------------------------------------ 13

Agregando Formulas al formulario cOMP. INGRESOS. ----------------------------------------------- 13

Agregando Formulas al formulario cOMP. EGRESOS. ------------------------------------------------- 14

Agregando Formulas al formulario NOTA DE DEBITO. ------------------------------------------------ 14

Agregando Formulas al formulario NOTA DE CREDITO. ---------------------------------------------- 14

Agregando Formulas en la Hoja Mayor. ------------------------------------------------------------------ 14

Agregando Formulas en la Hoja Balance de Comprobación. ---------------------------------------- 15

Agregando Formulas en la Hoja Balance General. ----------------- ¡Error! Marcador no definido.

Agregando Formulas en la Hoja Estado de Resultado.------------ ¡Error! Marcador no definido.

Creando Macros para la Hoja Diario ----------------------------------------------------------------------- 16

DISEÑANDO LA HOJA MENU -------------------------------------------------------------------------------- 17

Validando y Protegiendo Celdas y Hojas ----------------------------------------------------------------- 18

Validando la Hoja MENU ------------------------------------------------------------------------------------------------ 18

VALIDANDO LA HOJA D. ------------------------------------------------------------------------------------------------- 19

Protegiendo Celdas y Hojas de la hoja d. --------------------------------------------------------------------------- 19

Protegiendo la Hoja Balance, General y Resultado ------------------------ ¡Error! Marcador no definido.

PROGRMANDO TU SISTEMA CONTABLE. -------------------------------------------------------- 19

Validando las Hojas D, I, E, ND y NC para imprimir --------------- ¡Error! Marcador no definido.

Rediseñando el Área de Impresión de la Hoja E -------------------- ¡Error! Marcador no definido.

COPIANDO COIDGOS EN LOS MODULOS DE VBA DE TU SISTEMA. -------------------------------- 20

PROGRAMANDO LOS BOTONES DE LA HOJA D. -------------------------------------------------------- 23

PROGRAMANDO LOS BOTONES DE LA HOJA i. --------------------------------------------------------- 25

PROGRAMANDO LOS BOTONES DE LA HOJA e. --------------------------------------------------------- 25

PROGRAMANDO LOS BOTONES DE LA HOJA ND. ------------------------------------------------------ 26

PROGRAMANDO LOS BOTONES DE LA HOJA NC. ------------------------------------------------------ 26

PROGRAMANDO LOS BOTONES DE LA HOJA MENU. ------------------------------------------------- 26

CODIGOS PARA LA HOJA THISWORKBOOK. ------------------------------------------------------------- 27

Transacciones y Pasos Para Hacer Cierre de Periodo ---------------------------------------- 27

Transacciones de tu negocio --------------------------------------------------------------------------------- 27

Cierre de Periodo ------------------------------------------------------------------------------------------------ 27

ROGER R. PEREZ BELLO 4

APLICA EXCEL CONTABLE.COM – MODULO III

DISEÑANDO TÚ PLANTILLA CONTABLE EN EXCEL.

La contabilidad es la base fundamental en el negocio para la toma de decisiones, ahh

pero es la base fundamental cuando tienes todo actualizado. Por eso en este módulo

vamos a empezar a Diseñar tu Sistema Contable en Excel.

Antes que empecemos, primero abrimos Excel, si observas ya lo tengo abierto, le

insertamos 6 hojas más (Si estas usando Office 2013) o 4 más si estas usando el office

2010 o las versiones anteriores

Ahora Vamos a Nombrar las Hojas, a la Hoja1 lo llamaremos MENU, a la Hoja2 lo

llamaremos D, a la Hoja3 lo llamaremos DIARIO, a la Hoja 4 MAYOR, a la Hoja 5

BALANCE, a la Hoja 6 GENERAL y a la Hoja 7 lo llamaremos RESULTADO.

DISEÑANDO TU FORMULARIO DE ASIENTO DE DIARIO.

Para Comenzar nos ubicamos en la Celda C9 y escribimos CODIGO, le aplicamos un

ancho de columna de 108 pixeles, en la Celda D9 escribimos DESCRIPCION, y le

aplicamos un ancho de columna de 178 pixeles, en la Celda E9 escribimos DEBE, y le

aplicamos un ancho de columna de 91 pixeles, en la Celda F9 escribimos HABER y le

aplicamos un ancho de columna de 91 pixeles y en la Celda G9 escribimos DETALLES y

le aplicamos un ancho de columna de 243 pixeles. Seleccionamos de la C9 a la G9 y le

aplicamos negrilla y Centrar.

Nos ubicamos en la Celda C1 y escribimos ASIENTO DE DIARIO, en la celda C2 escribimos

ASI- (con un guion) y lo alineamos a la derecha, en la celda C3 escribimos Nº ASIENTO,

en la celda D3 le aplicamos bordes Externos, en la Celda E3 escribimos FECHA: y lo

alineamos a la derecha, en la Celda F3 le aplicamos bordes externos.

En la Celda C5 escribimos CONCEPTO: y le aplicamos negrilla, seleccionamos la Celda

C6:G8 y le aplicamos Combinar y Centrar más bordes de cuadro Grueso.

Seleccionamos de la C9:G9 y le aplicamos Todos los bordes, Seleccionamos de la C10:C19

y le aplicamos bordes externos, seleccionamos de la celda D10:D19 y le aplicamos

bordes externos, repetimos este paso hasta la Columna G.

En la Celda C20 escribimos NOTAS: , seleccionamos de la C21:D24 y le aplicamos

Combinar y Centrar más bordes externos. En la Celda D20 escribimos TOTAL y lo

alineamos a la Derecha.

Seleccionamos de la A1:M30 y le aplicamos Color de Relleno, Azul, énfasis 5, Claro 80%.

Seleccionamos de la C2:G24 y le aplicamos color de relleno Blanco, Fondo 1.



Seleccionamos la Celda C1: G1 y le aplicamos Color de Relleno Azul Oscuro mas

Combinar y Centrar, seleccionamos el texto de la Celda C1 y le aplicamos color de fuente

Blanco, Fondo 1.

En la Celda D19 escribimos HAZ CLIC EN EL BOTON NUEVA LINEA, seleccionamos de la

C19:D19 y le aplicamos color de relleno rojo, seleccionamos el texto de la Celda D19 y le

aplicamos color de Fuente Blanco, Fondo 1.

En la Celda E20 escribimos la Formula =SUMA(E10:E19), en la Celda F20 escribimos la

formula =SUMA(F10:F19), en la Celda F21 escribimos la formula =E20-F20.

Seleccionamos de la I20:F21 y le aplicamos estilo millares.

En la Celda G20 escribimos la Formula =SI(F21<>0;"OJO ASIENTO NO CUADRA";""),

ahora le aplicamos Formato Condicional, creamos una nueva regla, se presenta una

ventana dentro de esta escogemos la opción “Aplicar Formato Únicamente a las Celdas

que contengan, en la Primera lista de opciones escogemos Texto especifico, en la

Segunda lista de opciones lo dejamos con “que contiene” y en la Tercera lista de

opciones escribimos =$G$20. Ahora dentro de esta misma ventana en vista previa le

aplicamos formato, nos ubicamos en relleno y le aplicamos el Color amarrillo y pulsamos

aceptar, una vez aplicado el formato también pulsamos aceptar para aplicar el formato

condicional. Seleccionamos el texto de la Celda G20 y le aplicamos color de fuente Rojo.

DIBUJANDO BOTONES DEL FORMULARIO ASIENTO DE DIARIO.

Vamos Crear cinco botones y un cuadro de lista. Comencemos entonces:

Botón 1. – Para Regresar a la Hoja MENU.

Insertamos desde la Pestaña INSERTAR una Flecha A curvada hacia la Izquierda, lo

dibujamos de la G2:G4 en la parte derecha, pulsamos clic derecho sobre la forma y

seleccionamos Modificar Texto, escribimos MENU, seleccionamos el texto le aplicamos

centrar, negrilla y color de fuente Rojo.

Nuevamente seleccionamos la forma y nos ubicamos en la Pestaña FORMATO, sección

estilos de forma y nos ubicamos en efectos de formas, lo desplegamos y escogemos de

la lista Reflexión, se despliega otra lista y dentro de esta escogemos “Reflejo total,

desplazamiento de 4 Puntos”.

Botón 2. – Para Insertar Nueva Línea.

Insertamos desde la Pestaña DESARROLLADOR (Programador), un botón de Comando

(control Active X). Lo dibujamos desde la Celda B10:B11, Pulsamos Clic derecho sobre el

botón y seleccionamos la opción propiedades, se abre una nueva ventana, nos ubicamos

en Caption y borramos Commandbutton1 y escribimos “NUEVA LINEA”, nos ubicamos



en BackColor, lo desplegamos y escogemos Fondo de la Ventana (Blanco), nos ubicamos

en ForeColor lo desplegamos y escogemos Texto del Botón (Negro que llevo negrilla) y

cerramos la ventana propiedades.

Botón 3. – Para Imprimir Comprobante.

Insertamos desde la Pestaña DESARROLLADOR (Programador), un botón de Comando

(control Active X). Lo dibujamos desde la Celda E23:E24, Pulsamos Clic derecho sobre el

botón y seleccionamos la opción propiedades, se abre una nueva ventana, nos ubicamos

en Caption y borramos Commandbutton2 y escribimos “IMPRIMIR”, nos ubicamos en

BackColor, lo desplegamos y escogemos Barra de Titulo Activo, nos ubicamos en

ForeColor lo desplegamos y escogemos Texto del Botón (Negro que llevo negrilla) y

cerramos la ventana propiedades.

Botón 4. – Para Guardar la Transacción en el Libro Diario (BASE).

Pulsamos Clic derecho sobre el Botón Imprimir copiamos y pegamos, nos ubicamos el

botón desde la esquina derecha de la celda F23 hasta la esquina izquierda de la Celda

G24, pulsamos Clic derecho sobre el botón y seleccionamos propiedades, se abre una

nueva ventana, nos ubicamos en Caption y borramos IMPRIMIR y escribimos

“GUARDAR”, nos ubicamos en BackColor, lo desplegamos y escogemos “Resalte”, nos

ubicamos en ForeColor lo desplegamos y escogemos “Resalte de Texto” y cerramos la

Ventana Propiedades.

Botón 5. – Para Generar un Nuevo Formulario.

Pulsamos Clic derecho sobre el Botón Guardar copiamos y pegamos,nos ubicamos el

botón desde el centro de la celda G23 hasta la esquina derecha de la Celda G24,

pulsamos Clic derecho sobre el botón y seleccionamos propiedades, se abre una nueva

ventana, nos ubicamos en Caption y borramos GUARDAR y escribimos “NUEVO”, nos

ubicamos en Backcolor, lo desplegamos y escogemos “Resalte”, nos ubicamos en

ForeColor lo desplegamos y escogemos “Resalte de Texto” y cerramos la Ventana

Propiedades.

Insertando Cuadro de Lista.

Antes de insertar el cuadro de lista, no ubicamos en la celda H2 y escribimos PLAN DE

CUENTAS, seleccionamos de la H2:L2 y le aplicamos Combinar y Centrar más color de

relleno amarillo.

Nos ubicamos en la Pestaña DESARROLLADOR, sección controles e insertamos un cuadro

de lista (Control de Formulario), lo dibujamos desde el Centro de la Celda H3 hasta la

parte inferior de la Celda L12. Por ahora este cuadro lo dejamos en Blanco, más adelante

le vamos a agregar el cátalo de cuentas.



Definiendo el área de Impresión del Comprobante.

Nos ubicamos en la Celda AA1 y escribimos la formula =MENU!C1, seleccionamos de la

AA1:AE1 y le aplicamos Combinar y Centrar.

En la Celda AA3 escribimos la formula =C1, seleccionamos de la AA3:AE3 y le aplicamos

Combinar y Centrar.

En la Celda AD4 escribimos ASI Nº, seleccionamos la Celda AD4:AE4 y le aplicamos

negrilla y tamaño de fuente 16, en la Celda AE4 escribimos la Formula =D3.

En la Celda AD 5 escribimos FECHA: y en la celda AE5 escribimos la formula =F3.

En la Celda AA5 escribimos CONCEPTO: , seleccionamos desde la Celda AA6:AE9 y le

aplicamos Combinar y Centrar más Bordes Externos. En la Celda AA6 escribimos la

formula =C6.

En la Celda AA10 escribimos CODIGO, y le aplicamos un ancho de columna de 97 pixeles,

en la celda AB10 escribimos DESCRIPCION, y le aplicamos un ancho de columna de 192

pixeles, en la celda AC10 escribimos DEBE, y le aplicamos un ancho de columna de 101

pixeles, en la celda AD10 escribimos HABER, y le aplicamos un ancho de columna de 101

pixeles, en la celda AE escribimos DETALLES, y le aplicamos un ancho de columna de 221

pixeles. Seleccionamos de la AA10:AE10 y le aplicamos negrilla más borde doble inferior.

En la Celda AB56 escribimos SUMAN, lo centramos y le aplicamos negrilla, en la celda

AC56 escribimos la formula =SUMA(AC11:AC55), en la celda AD56 escribimos la formula

=SUMA(AD11:AD55), seleccionamos la Celda AC56:AD56,le aplicamos negrilla más estilo

millares.

En la Celda AA61 escribimos ELABORADO POR, en la Celda AC61 escribimos REVISADO

POR, en la celda AE61 escribimos AUTORIZADO POR.

Insertamos una forma desde la Pestaña INSERTAR, en este caso seleccionamos una

forma “Línea” y lo dibujamos desde la Celda AA60 hasta la mitad de la celda AB60, nos

dirigimos a la pestaña FORMATO, sección estilos de Forma y seleccionamos Línea

Intensa – Oscuro 1. Ahora bien, esta misma forma lo seleccionamos y le creamos dos

copias, lo ubicamos encima de REVISADOR POR Y AUTORIZADO POR.

DISEÑANDO TU FORMULARIO COMPROBANTE DE INGRESO.

Para crear el Formulario de Comprobante de Ingreso, sigamos los siguientes pasos:

1. Creamos una Copia de la Hoja D y lo insertamos después de la Hoja D.

2. Modificamos el nombre de la Hoja D (2) que es la copia generada y lo nombramos

“I”.



3. Ahora Modificamos la Celda C1 y escribimos COMPROBANTE DE INGRESO.

4. Modificamos la Celda C2 y escribimos CIN-.

5. Modificamos la Celda C3 y escribimos (INGRESO Nº:).

DISEÑANDO TU FORMULARIO COMPROBANTE DE EGRESO.

Para crear el Formulario de Comprobante de Egreso, sigamos los siguientes pasos:

1. Creamos una Copia de la Hoja I y lo insertamos después de la Hoja I.

2. Modificamos el nombre de la Hoja I (2) que es la copia generada y lo nombramos

“E”.

3. Ahora Modificamos la Celda C1 y escribimos COMPROBANTE DE EGRESO.

4. Modificamos la Celda C2 y escribimos CEG-.

5. Modificamos la Celda C3 y escribimos (EGRESO Nº:).

REDISEÑANDO LA HOJA E.

Vamos a personalizar el Comprobante de Egreso, para eso primeramente vamos a

escribir en la Celda C4 (CHEQUE Nº:), a la Celda D4 le aplicamos bordes externos.

En la Celda E4, vamos a escribir BENEFICIARIO: y en la celda F4 le aplicamos bordes

externos.

En la Celda E5, escribimos CUENTA Nº: y en la Celda F5 le aplicamos Bordes Externos.

Muy bien, ahora Seleccionamos la forma MENU con un Clic derecho ahora pulsamos clic

izquierdo sobre la forma y oprimimos el teclado Suprimir (Del).

Nos dirigimos a la Pestaña INSERTAR y elegimos la forma FLECHA IZQUIERDA, y lo

dibujamos desde la celda G2:G3, casi ubicado en la esquina derecha de la Celda.

Pulsamos Clic derecho sobre la Forma y seleccionamos modificar Texto, escribimos

MENU, seleccionamos el texto MENU, le aplicamos negrilla y color de fuente Rojo.

DISEÑANDO TU FORMULARIO NOTA DE DEBITO.

Para crear el Formulario de Comprobante de NOTA DE DEBITO, sigamos los siguientes

pasos:

1. Creamos una Copia de la Hoja I y lo insertamos después de la Hoja E.

2. Modificamos el nombre de la Hoja I (2) que es la copia generada y lo nombramos

“ND”.

3. Ahora Modificamos la Celda C1 y escribimos NOTA DE DEBITO.

4. Modificamos la Celda C2 y escribimos N_D.



5. Modificamos la Celda C3 y escribimos (NOTA DEBIT. Nº:).

DISEÑANDO TU FORMULARIO DE NOTA DE CREDITO.

Para crear el Formulario de Comprobante de NOTA DE CREDITO, sigamos los siguientes

pasos:

1. Creamos una Copia de la Hoja ND y lo insertamos después de la Hoja ND.

2. Modificamos el nombre de la Hoja ND (2) que es la copia generada y lo

nombramos “NC”.

3. Ahora Modificamos la Celda C1 y escribimos NOTA DE CREDITO.

4. Modificamos la Celda C2 y escribimos N_C.

5. Modificamos la Celda C3 y escribimos (NOTA CREDIT. Nº:).

DISEÑANDO TÚ LIBRO DIARIO EN EXCEL.

Para Comenzar nos ubicamos en la Celda A1, escribimos FECHA con un ancho de

columna de 80 pixeles, pulsamos la Tecla Tab y nos cruzamos en la celda B1, escribimos

ASI Nº con un ancho de columna de 49 pixeles, en la C1 escribimos ING. Nº con un ancho

de columna de 49 pixeles, en la D1, escribimos EGR Nº con un ancho de columna de 49

pixeles, en la E1 escribimos ND Nº con un ancho de columna de 49 pixeles, en la F1

escribimos NC Nº con un ancho de columna de 49 pixeles, en la G1 escribimos CODIGO

con un ancho de columna de 80 pixeles, en la H1 escribimos CUENTAS con un ancho de

columna de 219 pixeles, en la I1 escribimos DESCRIPCION con un ancho de columna de

312 pixeles, en la J1 escribimos DEBE, en la K1 escribimos HABER y en la L1 escribimos

DETALLES . Seleccionamos de la A1:L1 y le aplicamos negrilla, Centrar más borde doble

inferior.

Ingresamos ahora a la Vista de libro que dice “Vista Previa de salto de Páginas, nos

ubicamos en la Pestaña Diseño de Pagina, sección Configurar página, comando

orientación, desplegamos este comando y seleccionamos horizontal. Ahora ajustamos

el área de impresión, si observas hay columnas que están fuera del área de impresión,

lo ajustamos, Ahora seleccionamos el modo de vista de Libro a “Diseño de Pagina” y le

agregamos en el encabezado de página del centro y escribimos DIARIO GENERAL, ahora

regresamos a la Vista Normal.

Ahora vamos a insertar una forma y un botón, entonces comencemos:

Vamos a insertar una forma FLECHA IZQUIERDA, lo dibujamos de la Celda O8 a la P8,

pulsamos clic derecho sobre la forma, seleccionamos Modificar texto y escribimos IR A

MENU, seleccionamos el texto y le aplicamos color de fuente Blanco.



Ahora vamos a insertar un Botón (Control de Formulario), lo dibujamos des de la Celda

O2:P3, pulsamos clic derecho sobre el botón y seleccionamos Modificar Texto,

escribimos NUEVO DIARIO.

DISEÑANDO TÚ LIBRO MAYOR EN EXCEL.

Para Comenzar nos ubicamos en la Celda A9, escribimos FECHA con un ancho de

columna de 80 pixeles, pulsamos la Tecla Tab y nos cruzamos en la celda B9, escribimos

ASI Nº con un ancho de columna de 49 pixeles, en la C9 escribimos ING. Nº con un ancho

de columna de 49 pixeles, en la D9, escribimos EGR Nº con un ancho de columna de 49

pixeles, en la E9 escribimos ND Nº con un ancho de columna de 49 pixeles, en la F9

escribimos NC Nº con un ancho de columna de 49 pixeles, en la G9 escribimos CODIGO

con un ancho de columna de 104 pixeles, en la H1 escribimos DESCRIPCION con un

ancho de columna de 312 pixeles, en la I9 escribimos DEBE con un ancho de Columna

de 109 pixeles , en la J9 escribimos HABER con un ancho de columna de 109 pixeles y

en la K9 escribimos SALDOS con un ancho de Columna de 112 pixeles. Seleccionamos

de la A9:K9 y le aplicamos negrilla, Centrar, todos los bordes más borde doble inferior.

En la Celda A2 escribimos FECHA, y en la Celda B2 escribimos la formula =HOY(),

seleccionamos de la B2:F2 y le aplicamos Combinar y Centrar, También le aplicamos

formato de fecha y escogemos el más completo.

En la celda A4 escribimos la siguiente Formula =MENU!C1, seleccionamos de la A4:K4 y

le aplicamos Combinar y Centrar más negrilla.

En la Celda A6 escribimos MAYOR GENERAL, seleccionamos de la A6:K6 y le aplicamos

Combinar y Centrar Mas Negrilla. En la Celda G7 escribimos CUENTA: , lo alineamos a la

derecha y le aplicamos negrilla.

Ahora vamos a insertar una Forma, nos ubicamos en la Pestaña INSERTAR y escogemos

la forma FLECHA IZQUIERDA. Lo dibujamos de la L7 a la M7, pulsamos clic derecho sobre

la forma y seleccionamos modificar Texto, escribimos IR A MENU, seleccionamos el

Texto y le aplicamos color de fuente Blanco.

DISEÑANDO TU BALANCE DE COMPROBACIÓN.

Comencemos a Diseñar el Balance de comprobación y para eso nos ubicamos en las

celdas:

A8, escribimos “COGIDO”, en la B8 escribimos CUENTAS y le aplicamos ancho de

columna 290 pixeles, en la C8 escribimos DEBITOS, en la D8 escribimos CREDITOS, en la

E8 escribimos DEUDOR, en la F8 escribimos ACREEDOR, a estos encabezado le aplicamos



negrilla y la centramos. Ahora seleccionamos la Celda C7:D7 y le aplicamos el comando

combinar y Centrar, también negrilla y escribimos MOVIMIENTOS, en la E7 ejecutamos

la misma acción y escribimos SALDOS.

En la Celda A1 vamos a colocar la siguiente formula =MENU!C1 para que nos aparezca

el nombre de la empresa y le aplicamos negrilla. Ahora seleccionamos de la A1:F1 y le

aplicamos Combinar y Centrar.

En la A3 escribimos el nombre del documento y le aplicamos negrilla, seleccionamos de

la A3:F3 y le aplicamos Combinar y Centrar.

Ahora en la A5 escribimos “FECHA” y en la B5 escribimos la formula =HOY(). Ingresamos

en Formato de Celdas, sección Relleno y entramos en efectos de relleno, se abre una

ventana nueva, dentro de esta ventana seleccionamos Diagonal Hacia arriba y Pulsamos

aceptar.

Seleccionamos de la A10 hasta la F157 y le aplicamos borde sencillo, en la B158

escribimos SUMAS TOTALES, le aplicamos negrilla y lo alineamos a la derecha, ahora

seleccionamos de la B158 hasta la F158 y aplicamos borde sencillo. Bien, ahora

seleccionamos la celda E158:F158 y le aplicamos borde doble inferior.

Nos ubicamos en la A8 y seleccionamos hasta la F8, ahora pulsamos la tecla Ctrl, lo

mantenemos pulsado y con el mouse seleccionamos la celda C7 y la E7, le aplicamos

borde sencillo, nuevamente seleccionamos de la A8:F8 y le aplicamos borde doble

inferior.

Seleccionamos de la C10 hasta la F158 y le aplicamos estilo millares. Para terminar con

la estructuración del Balance de Comprobación vamos a insertar una forma.

Nos ubicamos en la Pestaña INSERTAR e insertamos la forma FLECHA IZQUIERDA, lo

dibujamos de la celda G3 a la H3, pulsamos clic derecho sobre la forma y seleccionamos

modificar texto, escribimos MENU, seleccionamos el texto y le aplicamos color de fuente

Blanco.

CREANDO TÚ CATALOGO DE CUENTA.

En el módulo anterior ya has aprendido como crear tu catálogo de cuenta y pues para

este módulo no te voy a enseñar a crear un catálogo de cuenta, pero si te voy a regalar

un archivo de ejemplo de catálogo de cuenta bastante completo, para que rápidamente

puedas copiarlo y adaptarlo a tu catálogo de cuenta.

Este catálogo es de cinco niveles, y te invito que al crear tu catálogo de cuenta lo hagas

de 5 niveles, ya que es más flexible. Bien, Busca en el Área de Ejemplos y descarga el

Archivo “Ejemplo de Catalogo de Cuentas”. Ahora vamos a copiar y pegar el ejemplo en



la hoja balance desde la celda A10, una vez realizada la copia, nos ubicamos en la celda

V10 y Escribimos la formula =A10&””&B10, rellenamos esta fórmula en todas las celdas

hasta la V154.

Ahora seleccionamos la celda A10 y nos ubicamos justo en medio de las comillas que

está dentro de la formula y le aplicamos 21 espacio, una vez hecho esto vamos a copiar

la formula y la aplicamos a todas las cuentas de primer nivel del catálogo.

Seleccionamos la celda V11 y en este caso estamos hablando del segundo nivel,

entonces nos ubicamos en medio de las comillas y le aplicamos 17 espacio, luego

copiamos la formula y lo pegamos en las celdas que contengan códigos de segundo nivel.

Nos ubicamos en la V12 y le aplicamos 13 espacios y copiamos esta formulas a todas las

celdas que contengan códigos de tercer nivel.

Regresamos a la V13 y le aplicamos 9 espacios, copiamos la formula y lo pegamos en

todas las celdas que contengan códigos de nivel 4.

Para finalizar con los espacios nos ubicamos en la V14 y le aplicamos 2 espacios,

copiamos esta fórmula a todas las celdas que contengan códigos de nivel cinco. Listo

puedes observar una vista mucho más agradable que antes.

Muy bien, ya tenemos el catálogo de cuenta, ahora continuamos con la estructuración

de la Hoja del Estado de Resultado.

DESARROLLANDO TU PLANTILLA CONTABLE EN EXCEL.

Comenzamos a desarrollar la plantilla y cuando digo desarrollar me refiero a la

organización de la plantilla para que trabaje automáticamente una vez ingreses datos

en los formularios. Comencemos con:

NOMBRANDO CELDAS DE LA HOJA D, I, E, ND Y NC.

Vamos a nombrar una celda de la Hoja D, para eso seleccionamos la celda C19, nos

ubicamos en la pestaña Formula, Asignar Nombres, lo nombramos SELEC y pulsamos

aceptar.

Esta misma celda y nombre le asignaremos a las celdas de la Hoja I, E, ND y NC.

NOMBRANDO CELDAS DEL LIBRO DIARIO .

En esta Hoja vamos a nombrar tres rangos de celda, Para nombrar los Rangos de celdas

sigamos los siguientes pasos:



1. Seleccionamos de la G2:G1000 y le definimos el nombre “CodigoD”.

2. Seleccionamos de la J2:J1000 y le definimos el nombre “DEBE”.

3. Seleccionamos de la K2:K1000 y le definimos el nombre “HABER”.

Con estos pasos ya hemos definimos los nombres de Celdas de la Hoja Diario ahora

continuamos con la hoja Balance.

NOMBRANDO CELDAS DE LA HOJA BALANCE.

En esta Hoja vamos a Definir Nombres de 2 rangos de celda y para eso sigamos los

siguientes pasos:

1. El primer rango de Celda a Nombrar es A10:B157, lo seleccionamos y le

asignamos el nombre “PLAN”.

2. La siguiente es la Celda A10, pulsamos asignar nombres y escribimos “CodigoB”

en el cuadro de texto se refiere a dejamos la formula así =BALANCE!$A10.

Muy bien, estamos avanzando, ahora continuamos con el Balance General.

AGREGANDO FORMULAS AL FORMULARIO ASIENTO DE DIARIO.

Comenzamos con lo más importante y estos son las famosas formulas, para esta hoja

vamos a crear 2 fórmulas:

Nº APLICAR A LAS CELDA FORMULA

1 D10:D18 =SI(C10<>"";BUSCARV(C10;PLAN;2;0);"")

2 D2 =MAX(DIARIO!B2:B1000)+1

AGREGANDO FORMULAS AL FORMULARIO COMP. INGRESOS.





2 D2 =MAX(DIARIO!C2:C1000)+1



AGREGANDO FORMULAS AL FORMULARIO COMP. EGRESOS.





2 D2 =MAX(DIARIO!D2:D1000)+1

AGREGANDO FORMULAS AL FORMULARIO NOTA DE DEBITO.





2 D2 =MAX(DIARIO!E2:E1000)+1

AGREGANDO FORMULAS AL FORMULARIO NOTA DE CREDITO.





2 D2 =MAX(DIARIO!F2:F1000)+1

AGREGANDO FORMULAS EN LA HOJA MAYOR.

En esta hoja vamos a aplicar 4 formulas y empezamos por:


1 H7 =SI(MENU!G16<>"";BUSCARV(MENU!G16;PLAN;1;0);"")

2 I7 =SI(MENU!G16<>"";BUSCARV(MENU!G16;PLAN;2;0);"")

3 K10 =I10-J10



4 K11:K200 =SI((I11-J11)<>0;(I11-J11+K10);"")

Hasta aquí hemos formulado la Hoja Mayor, ahora continuamos con la hoja Balance.

AGREGANDO FORMULAS EN LA HOJA BALANCE DE

COMPROBACIÓN.

En la Hoja Balance vamos a ingresar 9 formulas muy valiosas, porque de estas fórmulas

depende la generación del balance de Comprobación, Balance General y el estado de

Resultado.


1 C10:C157 =SUMA(SI(CodigoD=CodigoB;1;0)*DEBE)

2 D10:C157 =SUMA(SI(CodigoD=CodigoB;1;0)*HABER)

3 E10:E157 =SI(C10>D10;C10-D10;"")

4 F10:F157 =SI(C10<D10;D10-C10;"")

5 D159 =C158-D158

6 C158 =SUMA(C10:C157)

7 D158 =SUMA(D10:D157)

8 E158 =SUMA(E10:E157)

9 F158 =SUMA(F10:F157)

La primera y la segunda fórmula es una formula Matricial, por lo tanto antes de rellenar

lo debemos de convertirlos en matricial siguiendo los siguientes pasos:

1. Seleccionamos la celda C10 y pulsamos F2.

2. Pulsamos a la misma vez tecla mayúscula “(shift)+ctrl+enter” y listo. Observaras

que la formula está dentro de una llave, mira debe estar así:

{=SUMA(SI(CodigoD=CodigoB;1;0)*DEBE)}.

3. Para la D10 repetimos los mismos pasos y luego estas fórmulas lo rellanos para

las demás celdas.

Muy bien, hemos avanzado a lo máximo ahora continuamos con la formulación del

Balance General.



CREANDO MACROS PARA LA HOJA DIARIO

Vamos a crear una macro que nos ayude a limpiar los datos contenidos en el Libro Diario.

Esta macro se utilizara solamente en los cierres de periodo.

Por ejemplo: Lo recomendable es, cada vez que se hace el cierre de periodo, se debe

crear una copia del archivo, digamos si el archivo se llama “Registros 2012” entonces

creamos otro que se llame “Registros 2013”. Entonces el cierre de Periodo se Ejecuta en

el archivo Registros 2012 una vez termines de hacer el cierre, abres el archivo Registros

2013 y ejecutas la macro y listo tienes un libro nuevo para un periodo nuevo, solo

copiamos los saldos finales del año 2012 como saldo inicial en el libro 2013 y listo

siempre en marcha con tu contabilidad.

Vamos a crear esta macro de la forma más fácil, sigue los siguientes pasos:

1. Entra a VBA (alt + F11)

2. Insertamos un Modulo

3. Copiamos el siguiente código tal a como esta dentro del módulo:

Sub DIARIO()

'

' ESTA MACRO LIMPIA EL CONTENIDO DEL DIARIO

Range("A2:L1000").Select

Selection.ClearContents

Range("A12").Select

End Sub

4. Guardamos la Información y cerramos Visual Basic.

5. Nos ubicamos en la Hola DIARIO y pulsamos clic derecho sobre el botón NUEVO

DIARIO, seleccionamos asignar macro, se abre una nueva ventana y

seleccionamos la macro llamada DIARIO y pulsamos aceptar.

Muy bien, con esto hemos creado la Macro del Libro DIARIO.



DISEÑANDO LA HOJA MENU

Vamos a Diseñar la Hoja MENU y para eso sigamos los siguientes pasos:

Seleccionamos de la celda A2:P41 y le aplicamos color de relleno Blanco, Fondo 1,

Oscuro 35%. Seleccionamos de la A1:P1 y le aplicamos color de relleno Azul Claro.

En la Celda C1 escribimos NOMBRE DE LA EMPRESA “X”, seleccionamos de la Celda

C1:N1 y le aplicamos combinar y centrar más color de fuente Blanco. En la columna F le

aplicamos un ancho de columna de 136 pixeles.

Seleccionamos de la B2:D2, le aplicamos combinar y centrar, color de relleno Blanco,

Fondo 1. Escribimos CONTABILIZAR y le aplicamos Negrilla.

Seleccionamos de la F2:H2, le aplicamos combinar y centrar, color de relleno Blanco,

Fondo 1. Escribimos REPORTES y le aplicamos Negrilla.

Seleccionamos de la K2:M2, le aplicamos combinar y centrar, color de relleno Blanco,

Fondo 1. Escribimos CONFIGURACIONES y le aplicamos Negrilla.

Desde la Pestaña INSERTAR, insertamos la forma Rectángulo, lo dibujamos de la celda

B3:D4, este botón debe de estar entre en medio de estas dos columnas. Ahora pulsamos

clic derecho sobre el botón y seleccionamos modificar texto, escribimos ASIENTOS, nos

ubicamos en la pestaña FORMATO y le aplicamos efectos de forma.

El primer efecto, desplegamos Preestablecer y le aplicamos Preestablecido 4, segundo

efecto, desplegamos Bisel y le aplicamos Circulo.

Ahora Seleccionamos el botón, copiamos y pulsamos 4 veces pegar, para crear cuatro

copias del botón ASIENTOS. Bien, ubicamos todos los botones uno sobre otro con un

espacio de una fila por botón.

Bien ahora Cambiamos los nombres de los Botones, al Segundo botón lo nombraremos

INGRESOS, al tercer botón EGRESOS, al cuarto botón NOTA DE DEBITO, al quinto botón

NOTA DE CREDITO. Con esto hemos creado los botones de la Sección CONTABILIZAR.

Ahora vamos a crear los botones de la Sección REPORTES, para eso seleccionamos el

botón ASIENTOS, pulsamos copiar y pegamos para generar una copia del botón, ahora

lo ubicamos desde la celda F3:H3, es decir, agrandamos un poquito más el botón.

Cambiamos el nombre del botón por BALANCE DE COMPROBACION.

Creamos tres copias del botón BALANCE DE COMPROBACION, y ubicamos los botones

uno sobre otro con espacio de una fila por botones.



Ahora vamos a cambiar el nombre de los botones, para el segundo botón lo nombramos

BALANCE GENERAL, para el tercer botón lo nombramos BALANCE DE RESULTADO, para

el cuarto botón lo nombramos LIBRO DIARIO.

En la Celda E16 y escribimos SELECCIONE EL CODIGO DE CTA, en la Celda E17, escribimos

PARA PRESENTAR EL LIBRO MAYOR.

Seleccionamos la Celdas G16:G17 y le aplicamos Combinar y Centrar y le aplicamos color

de relleno Blanco. Insertamos un botón de Comando (Control Active X), lo dibujamos

desde la H16 hasta la J17, pulsamos clic derecho sobre el botón y seleccionamos

propiedades, se abre una nueva ventana y nos ubicamos en Caption y modificamos

commandButton1 por PRESENTAR MAYOR, nos ubicamos en BackColor, lo desplegamos

y seleccionamos Resalte, nos ubicamos en ForeColor y seleccionamos Resalte de Texto.

Muy bien ahora vamos a insertar los botones de la Sección CONFIGURACIONES, para eso

seleccionamos el botón asientos, copiamos y pulsamos pegar 6 veces. Ubicamos los

botones uno sobre otro con un espacio de una fila por botón. Los botones deben estar

ubicados desde la Celda K a la M.

Ahora vamos a nombrar los botones, al primero lo nombraremos HOJA DIARIO, al

segundo HOJA MAYOR, al tercero BALANCE DE COMPROB, al cuarto BALANCE GENERAL,

al quinto ESTADO DE RESULTADO y al sexto AYUDA ONLINE.

VALIDANDO Y PROTEGIENDO CELDAS Y HOJAS

VALIDANDO LA HOJA MENU

Vamos a comenzar con la validación de Datos y para eso nos ubicamos en la Hoja MENU.

Antes de Comenzar a Validar, insertamos una hoja más y dentro de esta copiamos todo

el Plan Contable, desde la A1 hasta donde llegue. Ahora, vamos a eliminar todos los

códigos de Nivel 1,2,3, y 4, solo deben quedar los códigos de Nivel 5, osea solo los

códigos que contengan 1.1.1.1.001 se quedan, el resta a borrarlos, para borrar las filas

solo elimina filas y listo.

Bien, ahora nos ubicamos en la celda G16 de la Hoja MENU y validamos la Celda. Nos

ubicamos en la Pestaña DATOS, sección Herramientas de Datos y seleccionamos el

comando validación de Datos, se abre una ventana, dentro de esta, en la primera lista

de opciones seleccionamos lista, y en el cuadro de texto origen, escribimos la formula

=Hoja1!$A$1:$A$70, esta fórmula se refiere a la Hoja nueva que insertamos, no olvides



cambiar el nombre de la hoja, si es diferente a Hoja1, si es Hoja2, entonces cambia en la

formula el 1 por el 2.

VALIDANDO LA HOJA D.

Vamos a validar el rango C10:C19 y validamos el rango, agregándole la lista de la nueva

hoja insertada, o sea los códigos. Realiza estos mismos pasos para la Hoja I,E,ND y NC.

PROTEGIENDO CELDAS Y HOJAS DE LA HOJA D.

Vamos a proteger las celdas que no utilizaras para ingresar datos, esto evitara que

borres las formulas accidentalmente. Vamos a dejar disponible solo las celdas que

utilizaras para digitar datos. Entonces comencemos:

1. Seleccionamos de la C10:C19, pulsamos la tecla ctrl sin soltar y seleccionamos el

rango E10:G18, pulsamos la tecla control y seleccionamos la celda D3, F3, C6 y la

C21 .

2. En la Pestaña INICIO, sección celdas, comando Formatos, seleccionamos

Formato de Celdas.

3. Se abre una ventana, nos ubicamos en la sección Proteger.

4. Observa que hay dos opciones, Bloqueada y Oculto, estas dos opciones tienen

que estar sin el check, en este caso la opción Bloqueado esta seleccionado, lo

que debemos de hacer es deseleccionarla y pulsamos aceptar.

5. Ahora vamos a proteger la hoja para que las celdas bloqueada de efecto, en el

mismo comando formato, seleccionamos proteger Hojas, digitamos la

contraseña “124”, lo repetimos “124” y pulsamos aceptar.

6. Ahora puedes probar o intentar borrar los datos de la celda D10:D18, notaras

que es imposible.

Estos mismos pasos lo aplicaremos a la Hoja I, E, ND y NC.

Ahora vamos a proteger las celdas y Hojas del Libro Mayor.

PROGRMANDO TU SISTEMA CONTABLE.



COPIANDO COIDGOS EN LOS MODULOS DE VBA DE TU SISTEMA.

Entramos a VBA con la combinación de tecla Alt + F11 y e insertamos dos Modulos más

y copiamos los siguientes códigos:

Código para el Modulo 1:

Sub DIARIO()

'

' ESTA MACRO LIMPIA EL CONTENIDO DEL DIARIO

ActiveSheet.Unprotect "123"

Range("A2:L2000").Select

Selection.ClearContents

Range("A2").Select

Sheets("DIARIO").Protect

End Sub

Sub BusquedaContinuaM()

Dim busca As Object

Dim Primero

Dim hojaBusc As String, quebusco As String, mihoja As String

Dim filalibre As Integer

'en la siguiente variable se indica la hoja dónde buscar

hojaBusc = "DIARIO"

'el dato a buscar se encuentra en E3 de la hoja activa

'la variable "mihoja" será donde se volcarán los datos

mihoja = "MAYOR"



filalibre = 10

quebusco = Sheets(mihoja).Range("H7")

'la búsqueda se realiza sobre la columna D de la Hoja3

Set busca = Sheets(hojaBusc).Range("G2:G2000").Find(quebusco, LookIn:=xlValues,

Lookat:=xlWhole)

'si busca No es Vacío… es decir si la búsqueda es exitosa y encuentra el dato, guarda

ladirección en la variable Primero

If Not busca Is Nothing Then

Primero = busca.Address

'comienza el bucle

Do

'completa la fila de la hoja activa (Hoja2) con datos del registro encontrado

Sheets(mihoja).Cells(filalibre, 1) = busca.Offset(0, -6) 'dato de col A FECHA

Sheets(mihoja).Cells(filalibre, 2) = busca.Offset(0, -5) 'dato de col B Nº ASI

Sheets(mihoja).Cells(filalibre, 3) = busca.Offset(0, -4) 'dato de col c Nº ING

Sheets(mihoja).Cells(filalibre, 4) = busca.Offset(0, -3) 'dato de col c Nº EGR

Sheets(mihoja).Cells(filalibre, 5) = busca.Offset(0, -2) 'dato de col c Nº ND

Sheets(mihoja).Cells(filalibre, 6) = busca.Offset(0, -1) 'dato de col c Nº ND

Sheets(mihoja).Cells(filalibre, 7) = busca 'dato de col D CODIGO

Sheets(mihoja).Cells(filalibre, 8) = busca.Offset(0, 2) 'dato de col J DESCRIPCION

Sheets(mihoja).Cells(filalibre, 9) = busca.Offset(0, 3) 'dato de col P DEBE

Sheets(mihoja).Cells(filalibre, 10) = busca.Offset(0, 4) 'dato de col R HABER

filalibre = filalibre + 1

'continúa la búsqueda

Set busca = Sheets(hojaBusc).Range("G2:G2000").FindNext(busca)



'se repite la rutina hasta volver a la primer dirección guardada.

Loop While Not busca Is Nothing And busca.Address <> Primero

End If

'se libera la variable

Set busca = Nothing

Call Imprimirmayor

Call LimpiaMayor

End Sub

Sub LimpiaMayor()

Range("A10:J2000").ClearContents

End Sub

Sub Imprimirmayor()

Call IRAMAYOR

On Error Resume Next

Sheets("MAYOR").Activate

ActiveSheet.PrintPreview

End Sub


Sub REGRESARMENU()

Sheets("MENU").Visible = True

Sheets("MENU").Select

Sheets("D").Visible = True

Sheets("I").Visible = True

Sheets("E").Visible = True



Sheets("ND").Visible = True

Sheets("NC").Visible = True

Sheets("DIARIO").Visible = True

Sheets("MAYOR").Visible = False

Sheets("BALANCE").Visible = True

Sheets("GENERAL").Visible = True

Sheets("RESULTADO").Visible = True

End Sub

Sub IRAMAYOR()

' IRA A LA HOJA MAYOR Macro

Sheets("MAYOR").Visible = True

Sheets("MENU").Visible = False

Sheets("MAYOR").Select

End Sub


PROGRAMANDO LOS BOTONES DE LA HOJA D.

Comenzamos con el Botón Nº 1 – NUEVA LINEA, Seleccionamos el botón, clic derecho,

ver códigos y en medio escribes los códigos a continuación te dejo los códigos:

Códigos para el Botón nº 2 – IMPRIMIR.

Códigos para el Botón Nº 3 – GUARDAR

Private Sub CommandButton3_Click()

ActiveSheet.Unprotect Password:="124"

Sheets("DIARIO").Activate

Sheets("DIARIO").Unprotect "123"

Sheets("D").Select



'rutina que graba la factura venta en hoja base

'se busca la primer fila libre en hoja Fact

filalibre = Sheets("DIARIO").Range("A1048576").End(xlUp).Row + 1

'se pasarán los datos desde la 1er fila de items

ActiveSheet.Range("C10").Select

fila = 10

While ActiveCell.Value <> ""

'copiamos datos fijos ajustando nro col

Sheets("DIARIO").Cells(filalibre, 2) = ActiveSheet.Range("D3") 'NRO ASI

Sheets("DIARIO").Cells(filalibre, 1) = ActiveSheet.Range("F3") 'FECHA

Sheets("DIARIO").Cells(filalibre, 9) = ActiveSheet.Range("C6") 'CONCEPTO

Sheets("DIARIO").Cells(filalibre, 3) = ActiveSheet.Range("H1") 'Nº ING.

Sheets("DIARIO").Cells(filalibre, 4) = ActiveSheet.Range("H1") 'Nº EGRESO

Sheets("DIARIO").Cells(filalibre, 5) = ActiveSheet.Range("H1") 'Nº ND

Sheets("DIARIO").Cells(filalibre, 6) = ActiveSheet.Range("H1") 'Nº NC

'otros datos del encabezado

'copiamos lista de items

Sheets("DIARIO").Cells(filalibre, 7) = ActiveCell.Offset(0, 0) 'CODIGO

Sheets("DIARIO").Cells(filalibre, 8) = ActiveCell.Offset(0, 1) 'DESCRIPCION

Sheets("DIARIO").Cells(filalibre, 10) = ActiveCell.Offset(0, 2) 'DESCRIPC

Sheets("DIARIO").Cells(filalibre, 11) = ActiveCell.Offset(0, 3) 'PRECIO

Sheets("DIARIO").Cells(filalibre, 12) = ActiveCell.Offset(0, 4) 'PRECIO



'incremento la variable fila para repetir el bucle

filalibre = filalibre + 1

'repito el bucle para los items siguientes

ActiveCell.Offset(1, 0).Select

Wend

'una vez concluída la copia debiera limpiarse el form de datos

'para el ingreso de nueva factura

MsgBox "Tus Datos se Guardaron Corectamente"

ActiveSheet.Protect Password:="124"

Sheets("DIARIO").Activate

Sheets("DIARIO").Protect "123"

ActiveWorkbook.Save

End Sub

Códigos para el Botón Nº 4 – NUEVO

Macro para el Botón Nº 5 – MENU

PROGRAMANDO LOS BOTONES DE LA HOJA I.



PROGRAMANDO LOS BOTONES DE LA HOJA E.









PROGRAMANDO LOS BOTONES DE LA HOJA ND.







PROGRAMANDO LOS BOTONES DE LA HOJA NC.







PROGRAMANDO LOS BOTONES DE LA HOJA MENU.

Códigos para el Botón PRESENTAR MAYOR

Private Sub CommandButton1_Click()

Sheets("DIARIO").Unprotect "123"

Call BusquedaContinuaM

Sheets("DIARIO").Protect "123"

Call REGRESARMENU

End Sub



CODIGOS PARA LA HOJA THISWORKBOOK.

Para Concluir con el Diseño, Desarrollo y programación del Sistema Contable vamos a

copiar los siguientes códigos en la hoja Thisworkbook:

TRANSACCIONES Y PASOS PARA HACER CIERRE DE PERIODO

TRANSACCIONES DE TU NEGOCIO

CIERRE DE PERIODO

aplica excel contable.com – modulo...

Documents