Excel Avanzado

Excel Avanzado - Alejandro Quiceno.xls

Agradezco me envíes tus comentarios al siguiente Blog:

Microsoft Excel es una hoja de cálculo de la suite Microsoft Office

***IMPORTANTE** PARA PODER NAVEGAR ENTRE LAS HOJAS DEBES DE HABILITAR LAS MACROS.

Si no están habilitadas por favor cierre el archivo, vaya al menú Herramientas - Macro - Seguridad y

cambie el nivel a medio. Luego vuelva a abrir el archivo esta vez habilitando las macros.

http://aquiceno.blogspot.com/

ExcelCasos prácticos para áreas contables

Por: Alejandro Quiceno García

Avanzado

Nada produce tanto regocijo como el no cumplir muchos cometidos ni emprender asuntos engorrosos ni violentar nuestra capacidad mas allá de sus fuerzas, pues todo esto provoca perturbaciones en nuestra naturaleza.

Epicuro - Filosofo Griego 300 A.C.Agradecimientos

Acerca de Alejandro Quiceno

Busquen llegar a lo más alto, estudiar es crecer, no solo por lo que se aprende intelectualmente, sino por la experiencia humana, la gente alrededor de uno que lo alimenta emocionalmente para tener cada día mayor control sobre uno mismo, y

espiritualmente, para moldear un mayor carácter de servicio a los demás, donde el ego se reduzca a su más mínima expresión y se crezca en humildad y fuerza moral. Una va con otra. Eso es vivir, crecer para servir (...).

Aparte de la carta de Ingrid Betancur desde su cautiverio







Acerca de Alejandro Quiceno:

Medios de contacto:

Por Telefono Celular o Móvil en Colombia :

312- 7679391

317- 8081673

301- 5519274

Email:[email protected]

Blog :

http://aquiceno.blobspot.com

[email protected]

Nacido en Cali, Colombia un 15 de Julio de 1971. Estudió contaduría pública en la Universidad del Valle; se desempeñó en variasempresas algunas de ellas multinacionales y firmas de auditoría internacional. "Puedo decir que gracias al Excel, es que pude hacer la diferencia respecto a los demás. Quien diría que esta aplicación fuera tan poderosa llegando a límites en los cuales es posible realizar grandes tareas reduciendo los esfuerzos de equipos enteros de trabajo, de tal manera que pueda ser llevada

a cabo por una o muy pocas personas. Muchos de esos "Tips" están en este trabajo"

mailto:[email protected]

http://aquiceno.blobspot.com/







El tema anterior forma parte del curso de Excel Avanzado, casos prácticos para areas contables

mas de 230 lecciones al mejor precio, buena informacion para autoaprendizaje a muy bajo costo.

Favor visitar mi blog

Agregarme como amigo en facebook

http://aquiceno.blogspot.com

[email protected]

Cruces de información

Como detectar diferencias mediante tablas dinámicas

Como se hace un cruce de información con tablas dinámicas

Armando la tabla dinámica

Resultado del ejemplo práctico

Cruces de información con tablas dinámicas y campos calculados

Cruces de información con tablas dinámicas y elementos calculados

Cruces sin campos ni elementos calculados

Cruces de Información








Este tema es inherente a la partida doble. El principio de la partida doble se sustenta en un principio Universal, el

equilibrio de las fuerzas expresado en el principio de la polaridad:

" Todo es doble; todo tiene dos polos; todo su par de opuestos; los semejantes y los antagónicos son lo mismo; los opuestos son idénticos

en naturaleza, pero diferentes en grado; los extremos se tocan; todas las verdades son semiverdades; todas las paradojas pueden reconciliarse"

-El Kybalión.

La Percepción, la manera en que presentamos -o nos presentan las cosas- es lo que hace que se vean contrarios u opuestos.

La creación de un sistema contable incluye este principio; El detalle y la generalidad, el diario y el mayor, el módulo

y la contabilidad. Pero esto es sólo una manera en que se ha creado el sistema, no necesariamente esto es así, ni se limita

a esto; (sencillamente por la dificultad que en ciertas épocas entrañaba el manejo de enormes volúmenes de información;

pero, la manera de generar la información es posible realizarla "en línea" sin necesidad de "Postear" -del inglés "Post"- pasar

las transacciones resumidas de los módulos a la contabilidad general. Es sólo siglo veinte) la esencia de la polaridad en

la contabilidad aparece siempre que exista una hecho económico que involucre mínimo dos entes. Incluso rompe esta frontera

dual: Puedo representar una misma cosa de dos maneras diferentes: Lo que recibí y tengo, que es lo mismo que lo que me

dieron o tengo vs lo que me dieron o debo o me debo. Que en últimas no es más que la representación de El dar y el recibir.

Ahora, dado que en la representación de los hechos económicos de un ente económico participan muchas personas e incluso es

sujeto (el sistema) a accidentes casi todos -por no decir todos- originados por la mano del hombre, (Pues el universo en que

se mueven las transacciones pasa desde la infraestructura tecnológica, plataforma del sistema hasta la subjetividad de quien

se representa - o ingresa y captura- estos mismos hechos económicos) es que es posible que se pierda el equilibrio del sistema

contable y ese tipo de situaciones es la que de manera general y cíclica consuma las energías de quienes controlan, administran

y participan en el sistema contable.

Este tema de cruces de información ya lo hemos manejado y solucionado en varios anexos de este trabajo.

Si usted revisó el anexo de formulas anidadas, habrá visto una buena solución que emplea la función =BuscarV() anidándola

dentro de una función =SI() y una función=ESERROR(): Buscando Items incorrectos o desconocidos entre dos tablas

En ese mismo anexo, en .También se tocó el tema en el anexo relacionado

con el formato condicional concretamente en la técnica

Siguiente:

busqueda sin necesidad de funciones de busqueda ;

Comparar la celda correspondiente con otra hoja

Cruces de Información o detectar diferencias con Tablas Dinámicas

Cruces de Información






Cruces de Información o detectar diferencias con Tablas DinámicasUna de las mejores virtudes de las tablas dinámicas es que no sólo sirven para hacer reportes dinámicos; en lo que

a mí respecta, lo mejor de las tablas dinámicas es que, convenientemente montada la información base, te puede

cruzar en segundos dos bases de datos diferentes.

Ejemplos de este tema pueden ser:

1- Cruzar información de los módulos vs la contabilidad como por ejemplo:

Un listado detallado de cartera vs las cuentas de cartera.

Un listado detallado de Kardex vs las cuentas de inventario.

Un listado detallado del módulo de cuentas por pagar vs todas las cuentas contables por pagar.

De manera general, los anexos de las cuentas del Balance deben presentarse de manera detallada, sustentando

debidamente los montos que en ellas se encuentran, listando los soportes que la componen; si existen diferencias

después del cierre contable las mismas no pueden superar mas de dos cierres sin haber hallado y corregido la diferencia.

Esa dificultad se salva tan rápidamente que Usted se quedará asombrado, con un cruce con tabla dinámica.

Otros Ejemplos de cruces de información pueden ser:

2- Una conciliacion bancaria: Cruzar el extracto bajado en medio magnético contra el libro auxiliar del banco.

3- Las cuentas de orden; cuentas de orden deudoras vs las cuentas de orden acreedoras.

4- Cuadres de compañías; cuando se trata de empresas grandes que deben conciliar las unidades de negocio contra la

compañía administrativa

Pero el poder de las tablas dinámicas trasciende encontrando aplicaciónes y soportando trabajos de Auditoría tales

como:

5- Organizar las cuentas auxiliares de Retenciones de Impuestos efectuadas, consolidar de la misma manera que se

presenta la información en las declaraciones de retención y encontrar diferencias. (Trabajo que obligatoriamente se debe

realizar cuando se acerca la época de entrega de Certificados de Retenciones practicadas), de tal manera que se cumpla

con la norma de Certificar lo Declarado, que a su vez debe cruzar perfecto contra lo contabilizado, claro está, si son

ordenados.






6- Tomar las cuentas de Gasto que constituyen base para impuesto Iva, calcularles un 16% o la tasa que aplique y rastrear

si existe el iva contabilizado en las cuentas correspondientes.

De igual forma, las tablas dinámicas pueden comparar el P&L (cuentas de resultado) contra el presupuesto; la ejecución

presupuestal de las inversiones contra las compras de activos fijos y las construcciones en curso.

Acostúmbrese amigo Gerente a usar o hacer que empleen las tablas dinámicas para este fin. Las tablas dinámicas no

tienen nada de vulgar frente a un reporte tradicional. El formato es lo de menos; incluso en esto las tablas dinámicas ofrecen

muy buenos formatos prediseñados. En el mundo financiero en donde escasea el tiempo, frecuentemente se desperdicia

mas tiempo haciendo reportes "Bonitos" que en un contenido rápido y preciso que puede ser elaborado en segundos con

una tabla dinámica, y lo mejor, puede reorganizar la data y consolidar por el criterio que necesite tan rapidamente que no

necesita nisiquiera llamar a su asistente para que lo realice a la antigua, tardando tiempo precioso, pagando mano de obra

ineficiente.

Siguiendo con este tema, en el anexo anterior se explicó como organizar un listado bajado a Excel de un Kardex. Un buen

informe de control es presentar la impresión de la ultima hoja del Kardex o de los Kardex, como soporte del anexo de las

cuentas de inventario contra las cuentas contables de inventario. En una compañía grande, el Contador de área o Asistente

tiene una responsabilidad no pequeña, cuidando que este saldo siempre sea consistente con la contabilidad; pero hay casos

en los que se presenta una diferencia; pequeña o grande, material o immaterial no viene al caso; los Saldos del módulo de

inventario TIENEN que ser perfectamente iguales a los saldos de las cuentas contables; cuando esto susede, las cantidades

de registros pueden ser enormes, de tal manera que detectar la diferencia es una tarea que unida a las labores del día a día

se torna muy complicada de sacar adelante.

Siguiente : Como se hace el cruce de información con tablas dinamicas






Como se hace un cruce con tabla dinámicaUsted tiene ambas Data: Por un lado (Kardex organizado, extracto bancario, el informe detallado o data de otra fuente que sea)

y por el otro, la otra base o informe: libro auxiliar de inventario, cartera o banco, informe de otra fuente, etc.

Ambos archivos los tiene en Excel. Antes de continuar, quiero decirle que ambos cuadros o informes TIENEN que estar orga-

nizados en forma de tabla de datos. En otras partes ya he comentado acerca de esto, en esta ocasión se lo presento gráfica-

mente: debe ser una relación de datos organizados en filas y columnas por este estilo:

Documento Fecha Cuantía

XC-0527 19980728 2.500.000

XC-0528 19980728 3.000.000

XC-0529 19980728 1.500.000

Ok. Habíendo entendido que ambos cuadros deben estar organizados como una tabla base de datos, encuentre la, o las

coincidencias a través de las cuales puede comparar ambos informes: Algo que exista en ambas bases de datos.

Por ejemplo:

Un Id de registro

Un tipo y número de documento

Un numero de identificación tal como Nit o código "Vendor"

La referencia QUE SEA que exista en ambos informes.

Si tiene uno o varios datos que pueda cruzar, tiene una gran porción del trabajo hecho, pues lo mas dificil es este punto.

Descripción

dejé la comida de mamá

mi papá tambien

Por ultimo mi hermanito






En el siguiente ejemplo vamos a inventar un ejemplo pequeño que nos ilustre esta técnica, vamos a cruzar un libro

auxiliar de inventario contra un kardex. Primero el Kardex, luego el libro auxiliar

Kardex Aminoacidos Ficticios

Bod Ref Descripción Lote Fecha Venc Documento Fecha Cant Entrada Cant Salida Costo Unitario Costo Transacc

Granel A0001 Alanine 00959482 31/12/2010 EA1525 15/01/2008 68 365836 24876848

Granel A0002 Arginine 00411641 04/04/2011 EA1525 15/01/2008 35 7288 255080

Granel A0003 Aspartic Acid 00900266 08/04/2011 EA1525 15/01/2008 50 968514 48425700

Granel A0004 Cysteine 00293793 30/04/2011 EA1526 15/01/2008 29 799754 23192866

Granel A0005 Glutamic Acid 00152684 27/06/2011 EA1526 15/01/2008 37 383410 14186170

Granel A0006 Glycine 00917437 04/09/2011 EA1526 15/01/2008 35 803211 28112385

Granel A0007 Hidroxyproline 00488654 23/09/2011 EA1527 16/01/2008 18 328475 5912550

Granel A0008 Isoleucine 00554997 22/11/2011 EA1527 16/01/2008 58 996530 57798740

Granel A0009 Leucin 00996395 27/12/2011 EA1527 16/01/2008 14 165824 2321536

Granel A0010 Lysine 00135017 04/04/2012 EA1527 16/01/2008 7 175521 1228647

Granel A0011 Methionine 00373055 04/04/2012 EA1528 17/01/2008 56 742020 41553120

Granel A0012 Phenlalanine 00500444 14/05/2012 EA1528 17/01/2008 54 931127 50280858

Granel A0013 Proline 00637860 25/06/2012 EA1528 17/01/2008 7 437475 3062325

Granel A0014 Serine 00392212 21/09/2012 EA1528 17/01/2008 61 633618 38650698

Granel A0015 Threonine 00241040 24/12/2012 EA1529 18/01/2008 36 749444 26979984

Granel A0016 Tyrosine 00422966 24/01/2013 EA1529 18/01/2008 50 641146 32057300

Granel A0017 Valine 00320611 13/03/2013 EA1529 18/01/2008 47 710572 33396884

Granel A0018 Ornithine 00200661 22/03/2013 EA1529 18/01/2008 7 777944 5445608

Granel A0019 Hidrolyzed Protein 00738693 10/04/2013 EA1530 19/01/2008 9 90726 816534

Granel A0020 Sodium 00545093 09/07/2013 EA1530 19/01/2008 26 475653 12366978

Granel A0021 Calcium 00610202 15/07/2013 EA1530 19/01/2008 32 34441 1102112

Granel A0022 Vitamin C (Ascorbic Palmitate)00037665 16/09/2013 EA1530 19/01/2008 40 122152 4886080

Granel A0005 Glutamic Acid 00152684 27/06/2011 SP2030 20/01/2008 6 383410 -2300460

Granel A0006 Glycine 00917437 04/09/2011 SP2030 20/01/2008 4 803211 -3212844

Granel A0007 Hidroxyproline 00488654 23/09/2011 SP2030 20/01/2008 4 328475 -1313900

Granel A0019 Hidrolyzed Protein 00738693 10/04/2013 SP2031 20/01/2008 14 90726 -1270164

Granel A0020 Sodium 00545093 09/07/2013 SP2031 20/01/2008 2 475653 -951306

Granel A0021 Calcium 00610202 15/07/2013 SP2031 20/01/2008 12 34441 -413292

Granel A0022 Vitamin C (Ascorbic Palmitate)00037665 16/09/2013 SP2031 20/01/2008 11 122152 -1343672

446.103.365,00






Listo, con lo anterior es suficiente, además de que yá listé los componentes de mi frasquito de Protein Amino. Ahora vamos con el libro Auxiliar:

Tenga en cuenta que ambos informes debe organizarse en forma de Tabla de datos

Auxiliar Cuenta Inventario a Granel 14XXXXXXXX

Doc Id Nit Fecha Fecha Ven Oc No. Fecha Oc Ref Fecha Doc Ref Debitos Creditos Valor

FP105340 890XXX686 20/01/2008 19/02/2008 OC0722 21/11/2007 EA1525 16/12/2007 73.302.548,00 73.302.548,00

FP105340 890XXX686 20/01/2008 19/02/2008 OC0722 21/11/2007 EA1526 16/12/2007 65.491.421,00 65.491.421,00

FP105340 890XXX686 21/01/2008 20/02/2008 OC0722 22/11/2007 EA1527 17/12/2007 67.261.473,00 67.261.473,00

FP105340 890XXX686 22/01/2008 21/02/2008 OC0722 23/11/2007 EA1528 18/12/2007 133.547.001,00 133.547.001,00

FE003240 XX679XXX 23/01/2008 22/02/2008 OC0723 24/11/2007 EA1529 19/12/2007 97.879.776,00 97.879.776,00

FE003240 XX679XXX 24/01/2008 23/02/2008 OC0723 25/11/2007 EA1530 20/12/2007 19.171.704,00 19.171.704,00

FE003240 XX679XXX 25/01/2008 24/02/2008 OC0723 26/11/2007 SP2030 21/12/2007 -5.513.304,00 -5.513.304,00

FE003240 XX679XXX 25/01/2008 24/02/2008 OC0723 26/11/2007 SP2031 21/12/2007 -3.978.434,00 -3.978.434,00

456.653.923,00

Fijese que en el auxiliar se le dá mas importancia a la negociación como tal; el primer documento es el numero de la

factura y el segundo es la Orden de Compra. Pero, por lo general cuando se ingresa una Entrada de Almacén, esta se

asocia a una Orden de compra, para saber si el proveedor envió la cantidad correcta o si se encuentra dentro de los

límites permitidos. Totalizando ambos reportes podemos ver que tienen una diferencia, y tambien nos podemos dar cuenta

que existe un dato en el que ambas tablas coinciden; es el numero de Entrada a Almacén.

Armando la tabla dinámica para cruzar la infoVamos a unir las dos tablas; siga estas instrucciones:

1. En una misma hoja de cálculo, va a acomodar una de las tablas en la parte de arriba de la hoja de cálculo y la otra

directamente debajo.

2. Para llevar a efecto el paso 1, puede crear una copia de uno de los informes, inmovilizar los encabezados de columna

con Ventana, inmovilizar paneles, pues es requisito BASICO, y debajo de ella pegar la copia del otro informe.

3. Inserte una columna en A, es decir, quedará primero que todas. Esta columna servirá para identificar la tabla de arriba

y la tabla de abajo.

4. El dato que coincide en ambas tablas DEBE estar alineado en la misma columna, en este caso lo que hace referencia

al número de Entrada de Almacén. El Valor total tambien DEBE estar alineado.

Siguiente: Armando la tabla dinámica






Al unir las dos tablas nos vá quedando así:























Granel A0022 Vitamin C (Ascorbic Palmitate) 00037665 16/09/2013 EA1530 19/01/2008 40 122152 4886080







Granel A0022 Vitamin C (Ascorbic Palmitate) 00037665 16/09/2013 SP2031 20/01/2008 11 122152 -1343672

Doc Id Nit Fecha Fecha Ven Oc No. Fecha Oc Ref Fecha Doc Ref Debitos Creditos Valor

FP105340 890XXX686 20/01/2008 19/02/2008 OC0722 21/11/2007 EA1525 16/12/2007 73.302.548,00 73.302.548,00

FP105340 890XXX686 20/01/2008 19/02/2008 OC0722 21/11/2007 EA1526 16/12/2007 65.491.421,00 65.491.421,00

FP105340 890XXX686 21/01/2008 20/02/2008 OC0722 22/11/2007 EA1527 17/12/2007 67.261.473,00 67.261.473,00

FP105340 890XXX686 22/01/2008 21/02/2008 OC0722 23/11/2007 EA1528 18/12/2007 133.547.001,00 133.547.001,00

FE003240 XX679XXX 23/01/2008 22/02/2008 OC0723 24/11/2007 EA1529 19/12/2007 97.879.776,00 97.879.776,00

FE003240 XX679XXX 24/01/2008 23/02/2008 OC0723 25/11/2007 EA1530 20/12/2007 19.171.704,00 19.171.704,00

FE003240 XX679XXX 25/01/2008 24/02/2008 OC0723 26/11/2007 SP2030 21/12/2007 -5.513.304,00 -5.513.304,00

FE003240 XX679XXX 25/01/2008 24/02/2008 OC0723 26/11/2007 SP2031 21/12/2007 -3.978.434,00 -3.978.434,00






En el siguiente paso eliminamos el segundo encabezado























Granel A0022 Vitamin C (Ascorbic Palmitate) 00037665 16/09/2013 EA1530 19/01/2008 40 122152 4886080







Granel A0022 Vitamin C (Ascorbic Palmitate) 00037665 16/09/2013 SP2031 20/01/2008 11 122152 -1343672

FP105340 890XXX686 20/01/2008 19/02/2008 OC0722 21/11/2007 EA1525 16/12/2007 73.302.548,00 73.302.548,00

FP105340 890XXX686 20/01/2008 19/02/2008 OC0722 21/11/2007 EA1526 16/12/2007 65.491.421,00 65.491.421,00

FP105340 890XXX686 21/01/2008 20/02/2008 OC0722 22/11/2007 EA1527 17/12/2007 67.261.473,00 67.261.473,00

FP105340 890XXX686 22/01/2008 21/02/2008 OC0722 23/11/2007 EA1528 18/12/2007 133.547.001,00 133.547.001,00

FE003240 XX679XXX 23/01/2008 22/02/2008 OC0723 24/11/2007 EA1529 19/12/2007 97.879.776,00 97.879.776,00

FE003240 XX679XXX 24/01/2008 23/02/2008 OC0723 25/11/2007 EA1530 20/12/2007 19.171.704,00 19.171.704,00

FE003240 XX679XXX 25/01/2008 24/02/2008 OC0723 26/11/2007 SP2030 21/12/2007 -5.513.304,00 -5.513.304,00

FE003240 XX679XXX 25/01/2008 24/02/2008 OC0723 26/11/2007 SP2031 21/12/2007 -3.978.434,00 -3.978.434,00






Insertamos una columna en A que nos identifica cada una de las tablas

Tabla Bod Ref Descripción Lote Fecha Venc Documento Fecha Cant Entrada Cant Salida Costo Unitario Costo Transacc

Kardex Granel A0001 Alanine 00959482 31/12/2010 EA1525 15/01/2008 68 365836 24876848

Kardex Granel A0002 Arginine 00411641 04/04/2011 EA1525 15/01/2008 35 7288 255080

Kardex Granel A0003 Aspartic Acid 00900266 08/04/2011 EA1525 15/01/2008 50 968514 48425700

Kardex Granel A0004 Cysteine 00293793 30/04/2011 EA1526 15/01/2008 29 799754 23192866

Kardex Granel A0005 Glutamic Acid 00152684 27/06/2011 EA1526 15/01/2008 37 383410 14186170

Kardex Granel A0006 Glycine 00917437 04/09/2011 EA1526 15/01/2008 35 803211 28112385

Kardex Granel A0007 Hidroxyproline 00488654 23/09/2011 EA1527 16/01/2008 18 328475 5912550

Kardex Granel A0008 Isoleucine 00554997 22/11/2011 EA1527 16/01/2008 58 996530 57798740

Kardex Granel A0009 Leucin 00996395 27/12/2011 EA1527 16/01/2008 14 165824 2321536

Kardex Granel A0010 Lysine 00135017 04/04/2012 EA1527 16/01/2008 7 175521 1228647

Kardex Granel A0011 Methionine 00373055 04/04/2012 EA1528 17/01/2008 56 742020 41553120

Kardex Granel A0012 Phenlalanine 00500444 14/05/2012 EA1528 17/01/2008 54 931127 50280858

Kardex Granel A0013 Proline 00637860 25/06/2012 EA1528 17/01/2008 7 437475 3062325

Kardex Granel A0014 Serine 00392212 21/09/2012 EA1528 17/01/2008 61 633618 38650698

Kardex Granel A0015 Threonine 00241040 24/12/2012 EA1529 18/01/2008 36 749444 26979984

Kardex Granel A0016 Tyrosine 00422966 24/01/2013 EA1529 18/01/2008 50 641146 32057300

Kardex Granel A0017 Valine 00320611 13/03/2013 EA1529 18/01/2008 47 710572 33396884

Kardex Granel A0018 Ornithine 00200661 22/03/2013 EA1529 18/01/2008 7 777944 5445608

Kardex Granel A0019 Hidrolyzed Protein 00738693 10/04/2013 EA1530 19/01/2008 9 90726 816534

Kardex Granel A0020 Sodium 00545093 09/07/2013 EA1530 19/01/2008 26 475653 12366978

Kardex Granel A0021 Calcium 00610202 15/07/2013 EA1530 19/01/2008 32 34441 1102112

Kardex Granel A0022 Vitamin C (Ascorbic Palmitate)00037665 16/09/2013 EA1530 19/01/2008 40 122152 4886080

Kardex Granel A0005 Glutamic Acid 00152684 27/06/2011 SP2030 20/01/2008 6 383410 -2300460

Kardex Granel A0006 Glycine 00917437 04/09/2011 SP2030 20/01/2008 4 803211 -3212844

Kardex Granel A0007 Hidroxyproline 00488654 23/09/2011 SP2030 20/01/2008 4 328475 -1313900

Kardex Granel A0019 Hidrolyzed Protein 00738693 10/04/2013 SP2031 20/01/2008 14 90726 -1270164

Kardex Granel A0020 Sodium 00545093 09/07/2013 SP2031 20/01/2008 2 475653 -951306

Kardex Granel A0021 Calcium 00610202 15/07/2013 SP2031 20/01/2008 12 34441 -413292

Kardex Granel A0022 Vitamin C (Ascorbic Palmitate)00037665 16/09/2013 SP2031 20/01/2008 11 122152 -1343672

Contabilidad FP105340 890XXX686 20/01/2008 19/02/2008 OC0722 21/11/2007 EA1525 16/12/2007 73.302.548,00 73.302.548,00




Contabilidad FE003240 XX679XXX 23/01/2008 22/02/2008 OC0723 24/11/2007 EA1529 19/12/2007 97.879.776,00 97.879.776,00

Contabilidad FE003240 XX679XXX 24/01/2008 23/02/2008 OC0723 25/11/2007 EA1530 20/12/2007 19.171.704,00 19.171.704,00

Contabilidad FE003240 XX679XXX 25/01/2008 24/02/2008 OC0723 26/11/2007 SP2030 21/12/2007 -5.513.304,00 -5.513.304,00

Contabilidad FE003240 XX679XXX 25/01/2008 24/02/2008 OC0723 26/11/2007 SP2031 21/12/2007 -3.978.434,00 -3.978.434,00






Alineamos el documento en común, en la misma columna

El costo de la transacción en este caso Ya se encuentra alineado.

Tabla Bod Ref Descripción Lote Fecha Venc Documento Fecha Cant Entrada Cant Salida Costo Unitario Costo Transacc

Kardex Granel A0001 Alanine 00959482 31/12/2010 EA1525 15/01/2008 68 365836 24876848

Kardex Granel A0002 Arginine 00411641 04/04/2011 EA1525 15/01/2008 35 7288 255080

Kardex Granel A0003 Aspartic Acid 00900266 08/04/2011 EA1525 15/01/2008 50 968514 48425700

Kardex Granel A0004 Cysteine 00293793 30/04/2011 EA1526 15/01/2008 29 799754 23192866

Kardex Granel A0005 Glutamic Acid 00152684 27/06/2011 EA1526 15/01/2008 37 383410 14186170

Kardex Granel A0006 Glycine 00917437 04/09/2011 EA1526 15/01/2008 35 803211 28112385

Kardex Granel A0007 Hidroxyproline 00488654 23/09/2011 EA1527 16/01/2008 18 328475 5912550

Kardex Granel A0008 Isoleucine 00554997 22/11/2011 EA1527 16/01/2008 58 996530 57798740

Kardex Granel A0009 Leucin 00996395 27/12/2011 EA1527 16/01/2008 14 165824 2321536

Kardex Granel A0010 Lysine 00135017 04/04/2012 EA1527 16/01/2008 7 175521 1228647

Kardex Granel A0011 Methionine 00373055 04/04/2012 EA1528 17/01/2008 56 742020 41553120

Kardex Granel A0012 Phenlalanine 00500444 14/05/2012 EA1528 17/01/2008 54 931127 50280858

Kardex Granel A0013 Proline 00637860 25/06/2012 EA1528 17/01/2008 7 437475 3062325

Kardex Granel A0014 Serine 00392212 21/09/2012 EA1528 17/01/2008 61 633618 38650698

Kardex Granel A0015 Threonine 00241040 24/12/2012 EA1529 18/01/2008 36 749444 26979984

Kardex Granel A0016 Tyrosine 00422966 24/01/2013 EA1529 18/01/2008 50 641146 32057300

Kardex Granel A0017 Valine 00320611 13/03/2013 EA1529 18/01/2008 47 710572 33396884

Kardex Granel A0018 Ornithine 00200661 22/03/2013 EA1529 18/01/2008 7 777944 5445608

Kardex Granel A0019 Hidrolyzed Protein 00738693 10/04/2013 EA1530 19/01/2008 9 90726 816534

Kardex Granel A0020 Sodium 00545093 09/07/2013 EA1530 19/01/2008 26 475653 12366978

Kardex Granel A0021 Calcium 00610202 15/07/2013 EA1530 19/01/2008 32 34441 1102112

Kardex Granel A0022 Vitamin C (Ascorbic Palmitate)00037665 16/09/2013 EA1530 19/01/2008 40 122152 4886080

Kardex Granel A0005 Glutamic Acid 00152684 27/06/2011 SP2030 20/01/2008 6 383410 -2300460

Kardex Granel A0006 Glycine 00917437 04/09/2011 SP2030 20/01/2008 4 803211 -3212844

Kardex Granel A0007 Hidroxyproline 00488654 23/09/2011 SP2030 20/01/2008 4 328475 -1313900

Kardex Granel A0019 Hidrolyzed Protein 00738693 10/04/2013 SP2031 20/01/2008 14 90726 -1270164

Kardex Granel A0020 Sodium 00545093 09/07/2013 SP2031 20/01/2008 2 475653 -951306

Kardex Granel A0021 Calcium 00610202 15/07/2013 SP2031 20/01/2008 12 34441 -413292

Kardex Granel A0022 Vitamin C (Ascorbic Palmitate)00037665 16/09/2013 SP2031 20/01/2008 11 122152 -1343672

Contabilidad FP105340 890XXX686 20/01/2008 19/02/2008 OC0722 EA1525 21/11/2007 16/12/2007 73.302.548,00 73.302.548,00




Contabilidad FE003240 XX679XXX 23/01/2008 22/02/2008 OC0723 EA1529 24/11/2007 19/12/2007 97.879.776,00 97.879.776,00

Contabilidad FE003240 XX679XXX 24/01/2008 23/02/2008 OC0723 EA1530 25/11/2007 20/12/2007 19.171.704,00 19.171.704,00

Contabilidad FE003240 XX679XXX 25/01/2008 24/02/2008 OC0723 SP2030 26/11/2007 21/12/2007 -5.513.304,00 -5.513.304,00

Contabilidad FE003240 XX679XXX 25/01/2008 24/02/2008 OC0723 SP2031 26/11/2007 21/12/2007 -3.978.434,00 -3.978.434,00

Siguiente: Resultado final Tabla Dinámica






Resultado final del ejemplo de cruce con tabla dinámicaEsta es la tabla dinámica:

Datos Tabla

Total Diferencia

Documento Contabilidad Kardex Contabilidad Kardex

EA1525 73.302.548,00 73.557.628,00 -255.080,00

EA1526 65.491.421,00 65.491.421,00

EA1527 67.261.473,00 67.261.473,00

EA1528 133.547.001,00 133.547.001,00

EA1529 97.879.776,00 97.879.776,00

EA1530 19.171.704,00 19.171.704,00

SP2030 -5.513.304,00 -6.827.204,00 1.313.900,00

SP2031 -3.978.434,00 -3.978.434,00

En la tabla dinámica obtenida, podemos ver que existe una diferencia en el Doc EA1525 y laSP2030






Instrucciones para armar esta tabla dinámicaPaso 1: vaya al menú Datos - Informe de tablas y gráficos dinámicos

Los cuadros que vamos a mostrar a continuación son de Excel 2003, pero no difieren en mayor grado respecto a versiones

anteriores; salvo en la versión del ultimo Excel, encontrarás que la tabla dinámica está dentro del Menú TABLA.

Este asistente te ayudará a armar la tabla dinamica. La primera pregunta es fácil, nuestra data está en Excel.

Click en Siguiente para llegar a:

Selecciona el rango donde estan las dos tablas unidas, incluyendo los encabezados, en este caso se encuentran

en la hoja "Armando la tabla dinámica". Una vez seleccionado TODO el rango, Click en siguiente.

(Ten en cuenta que debes tomar los encabezados de la tabla; la tabla debe ser compacta, sin columnas vacias intermedias)






En esta ventana hacer click en DISEÑO.

Llegará a esta ventana:






Observe que los encabezados de columna del listado ejemplo que armamos, se presentan como botones para seleccionar

y arrastrar sobre las áreas Pagina, fila, Columna y Datos. Acomode los botones como se muestra a continuación:

Fijese que el costo de la transacción lo arrastramos DOS veces. Ahora si en este paso, hace doble click en Documento,

Le saldrá este nuevo cuadro. Aquí hará click en Subtotales - Ninguno. Luego Aceptar para regresar a la ventana anterior.






En la ventana anterior, hará doble clik en el segundo botón de Suma de Costo transaccion2 o sea el de abajo.

Le saldrá esta ventana:

El nombre "Suma de Costo Transacción" lo puede cambiar. Yo puse

Diferencia

Haga click en Opciones para que se despliegue la parte inferior.

Escoja Diferencia de Tabla y elemento base Kardex

Cuando termino de usar este paso, click en Aceptar, y en la otra ventana click en aceptar otra vez.






Llegará a esta ventana. (Una de las del principio)

Aquí haga click en Opciones.






Llegará a esta ventana: aquí desactivará la verificación de totales generales de columnas y totales generales de filas

Click en Aceptar.






En la ultima ventana,hara click en Hoja de cálculo existente, Yo escogí la celda A9, que es en donde quedó la

tabla dinámica que se encuentra arriba.






Volvamos a la tabla dinámica:Esta es una imagen de la tabla dinámica de arriba:

Observe que hay diferencia en dos documentos. Como el elemento base es el Kardex, (Pues consideramos que el Kardex

es el que tiene la información Veráz) las diferencias nos aparecen en la tabla contabilidad.

Si hace doble click en cada uno de los valores de las filas de los documentos con diferencia, Excel extraerá la información

que totaliza dichos valores, y abrirá automáticamente una hoja de calculo para cada uno de ellos.

Es lo que comunmente llaman "Drill Down". El "Drill Down" trae el detalle de esos montos.

Haciendo doble click en los 73.302.548, y en los 73.557.628, nos abre dos hojas de cálculo. Esa información que generó,

la uní y la traje a esta hoja de cálculo para que usted la vea a continuación:

Esta información igualmente la puede trabajar para detectar qué le constituye la diferencia de los 255.080.oo

En este caso es una transacción que hace falta, correspondiente a la refencia A0002






CRUCES DE INFORMACION CON CAMPOS CALCULADOS

Datos

Area Real 20XA Ppto 20XA Ppto-Real Año XA Real 20XB Ppto 20XB Ppto-Real Año XB

Auditoría 99,6 2,6 -97 57,5 8,9 -48,6

Direccion Administrativa 26,8 61 34,2 4,5 8,6 4,1

Financiero 28,2 43,9 15,7 36,1 82,3 46,2

Juridico 23,8 76,7 52,9 33,1 35 1,9

Mantenimiento 33,8 59,7 25,9 1 22 21

Planeación 38 48,9 10,9 96,7 26,3 -70,4

Recursos humanos 85,6 48,1 -37,5 17,4 69,5 52,1

Revisoría Fiscal 13 6,6 -6,4 68,1 94 25,9

Sistemas 32,1 62,4 30,3 17,3 0,2 -17,1

Total general 380,9 409,9 29 331,7 346,8 15,1

Como se arma la tabla dinámica:

Organiza la información como se muestra aquí:






La tabla te quedará así: (Esta es una imagen.)

Con el ratón ubíquese en datos, haga click sostenido, y cuando le aparezca una cruz que termina en flecha en

todas sus puntas, arrastre ese campo al área de columnas. Le deberá quedar así:

Ubicado sobre el área de los totales, vaya al menú de la tabla dinámica y escoja Fórmulas - Campo Calculado











Te saldrá esta ventana: En ella vas a armar una formula que reste el campo Ppto 20XA - Real 20XA,

y Ppto 20XB - Real 20XB, así:

Para hacerlo, primero te ubicas en nombre y le pones un nombre al nuevo campo que nos vamos a inventar.

Yo le puse Ppto-Real Año XA

Luego seleccioné el campo Ppto 20XA y le di click a insertar campo. Me quedó en el campo fórmula.

Allí adicioné un signo menos. Volví a la zona de campos, escogí Real 20XA y le dí click a insertar campo.

Cuando la formula me quedó lista, le dí click a Sumar. (que es como un Guardar esa formula)






Luego me fui a armar el segundo campo calculado: Fijate que en este paso ya aparece incluido el campo prediseñado:






Finalmente, si desplegamos los nombres que disponemos, tenemos :

Puedes armar todas las formulas comparativas que quieras, entre los campos de totales de años que quieras, tales como

=(PptoAño - RealAño) / PptoAño - 1, y así por el estilo para conocer el cumplimiento y demás.






Puedes regresar a la opción tabla dinámica - asistente para tablas dinámicas para reorganizar los campos de los datos.

Vamos a incluir la diferencia del Ppto y el real de cada año, en su año correspondiente.

Simplemente nos ubicamos en el campo calculado y lo arrastramos en donde queremos.






Fíjate que aquí ya hemos arrastrado el campo calculado de la primer diferencia debajo del año correspondiente.

Para reducir el tamaño de los anchos de las columnas, puedes hacer doble click en ellos y cambiarles el nombre.

Espero que esta técnica de cruce tambien te sea de gran utilidad.

AQ.






CRUCES DE INFORMACION CON ELEMENTOS CALCULADOSHemos unido dos tablas de datos, el informe del banco y el libro auxiliar relacionado con la cuenta bancaria.

Hemos incluido en una primer columna un elemento que nos ayuda a identificar cada una de las tablas. (Tipo).

Sabemos que las transferencias se contabilizan como consignaciones, entonces insertamos una columna

para unificar el criterio de ambas tablas y la renombramos. (Es lo que está sombreado de gris).

Tipo Ref Ref2 fecha valor

Extracto Banco Transferencia Abonos 07/05/2008 801680,37



Extracto Banco Cheque pagado Pagos 07/05/2008 198935,87

Extracto Banco Cheque pagado Pagos 07/05/2008 936976,44

Auxiliar Consignación Abonos 07/05/2008 801980,37



Auxiliar Pagos Pagos 07/05/2008 198935,87

Auxiliar Pagos Pagos 07/05/2008 939679,44

De esta manera antes de armar la tabla dinámica, puede identificar todo lo que es común entre un extracto

y un libro auxiliar.






Como se arma la tabla dinámica.

Vas a organizar la información así:






La tabla dinámica te quedará así, pero sin la parte de abajo que dice "Diferencia". Ya vamos a explicar como se hace.

Suma de valor

Tipo Ref2 Total

Auxiliar Abonos 2033889,06

Pagos 1138615,31

Total Auxiliar 3172504,37

Extracto Banco Abonos 2033559,06

Pagos 1135912,31

Total Extracto Banco 3169471,37

Diferencia Abonos -330

Pagos -2703

Total Diferencia -3033

Total general 6338942,74

Ubicate dentro de la tabla dinámica en el campo Pagos o Abonos, luego, en la barra Tabla dinámica vas a

escoger Formulas - Elemento calculado






Saldrá una ventana similar a los campos a los campos calculados:






De manera similar a como se explicó en los campos calculados, vas a armar una formula con los elementos

calculados, así:






Cuando lo hagas, se te incluirá la diferencia como un nuevo elemento, y verás que ya cuentas con él.

Aquí la volví a armar con la misma base de datos pero de manera independiente, y arrastré el elemento TIPO a la zona

de Columnas. ;me parece que es mejor esta visulaización de las diferencias. Antes de finalizar me fui a la sección de

opciones de tabla y le quité la opción de totales generales de Filas. El número lo puedes formatear en el paso diseño,

haciendo doble click en "Suma de Valor". Allí te saldrá el asistente de formato, con el cual ya estás familiarizado.

Suma de valor Tipo

Ref2 Auxiliar Extracto Banco Dif Total general

Abonos 2033889,06 2033559,06 -330 4067118,12

Pagos 1138615,31 1135912,31 -2703 2271824,62

CRUCES DE INFORMACION SIN CAMPOS NI ELEMENTOS CALCULADOSSiguiendo con el ejemplo, lo unico que tenemos que adicionar es una nueva columna en la cual le vamos a cambiar el signo

A UNA de las tablas (lo que está sombreado de color canela)

Tipo Ref Ref2 fecha valor valor2

Extracto Banco Transferencia Abonos 07/05/2008 801680,37 -801680,37



Extracto Banco Cheque pagado Pagos 07/05/2008 198935,87 -198935,87

Extracto Banco Cheque pagado Pagos 07/05/2008 936976,44 -936976,44

Auxiliar Consignación Abonos 07/05/2008 801980,37 801980,37



Auxiliar Pagos Pagos 07/05/2008 198935,87 198935,87

Auxiliar Pagos Pagos 07/05/2008 939679,44 939679,44






Y vas a organizar la información así:






La tabla dinámica te quedará así:

Suma de valor2 Tipo

Ref2 valor Auxiliar Extracto Banco Total general

Abonos 346117,15 346.117,15 -346.117,15

801680,37 -801.680,37 -801.680,37

801980,37 801.980,37 801.980,37

885761,54 -885.761,54 -885.761,54

885791,54 885.791,54 885.791,54

Total Abonos 2.033.889,06 -2.033.559,06 330,00

Pagos 198935,87 198.935,87 -198.935,87

936976,44 -936.976,44 -936.976,44

939679,44 939.679,44 939.679,44

Total Pagos 1.138.615,31 -1.135.912,31 2.703,00

Total general 3.172.504,37 -3.169.471,37 3.033,00

Como ves, es una forma mejor para hacer los cruces que todas las formas anteriores vistas.

En donde te aparezca un Total general en Cero, es porque no hay diferencia.

Además de que estamos cruzando todos los valores de cada una de las referencias.

Puedes darte cuenta que las diferencias surgieron porque quien capturó la información confundió los 6 con los 9

Cuando no se conoce esta técnica, esta manera de encontrar diferencias es sencillamente asombrosa, pues Excel

cruza cantidades enormes de información en segundos, y si supera las 65536 filas, puedes partir tus fuentes de

información por periodos de tiempo, o usar Excel 2007 que supera el MILLON de filas.

Espero que esta manera de trabajar te haya sido de utilidad.

Si deseas adquirir el curso completo, entra a mi blog, veras que tiene un precio economico, y se aprende bastante.

Alejandro Quiceno

face book:

[email protected]







Documents

Excel Avanzado