viernes, 25 de enero de 2013

Función BUSCARV para buscar un tipo de cambio en una tabla de Excel.


Cómo buscar un Tipo de Cambio en una tabla con BUSCARV. VLOOKUP en inglés.

Buscar un tipo de cambio en una tabla es algo necesario para los profesionales que trabajan en las áreas de Importaciones o exportaciones, Finanzas, Tesorería, Cobranza, etc. Y todos aquellos departamentos que tengan relación con transacciones en dólares.

La siguiente imagen muestra la tabla de los tipos de cambio de 2012, en la celda B2 debemos obtener el tipo de cambio de la fecha de B1.




La función BUSCARV busca un valor de manera vertical dentro de una tabla o lista, y siempre lo buscará en la primera columna de la misma. Podemos apreciar que en la primera columna de la tabla se encuentran los números de los días, entonces mi valor buscado será el día de la fechade la celda B1. Y lo va a buscar dentro de la tabla TC que ya se encuentra nombrada.

Cuando encuentra dicho valor, nos pide asignar el número de columna donde está el Tipo de cambio que deseamos obtener. Según la fecha de B1, necesitamos el tipo de cambio de Febrero, la columna de Febrero es la número 3. Debemos recordar que mi tabla empieza a partir de D1. Por lo tanto la columna 1 es la de los días, la dos es Enero, la tres Febrero, y así sucesivamente.

Al final tenemos que indicar si queremos el valor exacto o aproximado (0 ó 1).

La función deberá quedar de la siguiente manera:

=BUSCARV(DIA(B1),TC,MES(B1)+1,0)


Estamos buscando el día de la fecha DIA(B1) = 2, dentro de TC. Utilizamos la función MES para saber el número de la columna que vamos a asignar, el mes de B1 es igual a 2 por que es Febrero, pero habíamos acordado que Febrero estaba en la columna 3, por lo tanto le tenemos que sumar 1, por eso ponemos MES(B1)+1 = 3. El cero al final indica que queremos el valor exacto.

Veamos el caso resuelto.




Anidar la función BUSCARV con funciones de fecha, como es este caso, puede ahorrarnos valioso tiempo en hacer búsquedas manuales.


Publicaciones relacionadas: Función BUSCARVFunción DIAFunción MESNOMBRAR RANGOS.


Esta y muchas otras funciones las podrás aprender en nuestro Curso de Excel Avanzado para Contadores.


CONTÁCTANOS: 
ATENCIONECAP@ECAPMEXICO.NET
TELÉFONO EN GUADALAJARA:  0133 36322719 

Herramienta Subtotales en Excel 2007 y 2010 para hacer resumen de ventas.


Cómo utilizar la herramienta Subtotales de Excel 2007 - 2010 para obtener un resumen de ventas.
La siguiente imagen muestra un reporte de ventas del mes de enero de varios vendedores, la herramienta Subtotales nos ayudará a obtener el total de ventas por cada vendedor.




Para aplicar la herramienta Subtotales a esta tabla que nos servirá para obtener el total de ventas por número de vendedor, primero, debemos ordenar la tabla por la columna Número Vendedor, si el reporte lo quisiéramos por nombre, entonces debemos ordenar por la columna Nombre, al igual que por Artículo o Fecha.

Ya teniendo la tabla ordenada, la seleccionamos toda desde los títulos, enseguida, tanto en 2007 como 2010 entramos a la ficha DATOS, del lado derecho en el apartado Esquema damos clic en Subtotal.



Al dar clic en Subtotal aparece el siguiente cuadro de diálogo. El primer campo se utiliza para configurar el criterio que vamos a tomar para obtener los subtotales, en este caso queremos una sumatoria por Número de Vendedor. En Usar función aparece por default Suma, así lo dejamos.

Como queremos obtener la sumatoria de la columna Total, habilitamos la casilla en el campo Agregar subtotal a.
Las tres casillas de abajo las dejamos así como están.

Al lado del botón Aceptar se encuentra otro llamado Quitar todos, este sirve para quitar los Subtotales.




Al dar Aceptar se insertan los subtotales cada que cambia el número de vendedor de la columna A. Podemos apreciar que el vendedor 100 tiene un total de ventas de 21,133.00.

En la parte superior izquierda vemos que aparecen 3 números, estos son los niveles de Subtotales, en este momento estamos en el nivel 3, que muestra todo el detalle.




Si damos clic en el nivel dos, se muestran solo los totales.





Copiar la información y pegarla en otro rango con la herramienta Seleccionar celdas visibles.



Algo común al trabajar con Subtotales es la imperiosa necesidad de copiar la información y pegarla en otro rango, hoja o libro de Excel. Como se puede observar, al dar clic en el nivel 2, muchas filas se ocultaron. Si queremos llevarnos únicamente los totales y hacemos un copiado normal con Ctrl + C y pegamos en la celda A31, todas las filas que estaban ocultas se muestran.




Para evitar esto debemos usar la herramienta Seleccionar Celdas Visibles. Primero seleccionamos el rango que queremos copiar solo lo visible, en este ejemplo es A1:E29, enseguida pulsamos F5, damos clic en Especial y habilitamos la opción Solo celdas visibles.




Al dar Aceptar, sin movernos del rango seleccionado, solo debemos copiar (Ctrl + C), y pegar de nuevo en la celda A31. Vemos como se pegaron solo las celdas visibles.




Regularmente cuando usamos esta herramienta es necesario aplicar funciones como BUSCARV para obtener de nuevo los nombres de los Vendedores. También la Función personalizada AVALOR es de gran ayuda para lograr este objetivo.


Buscar el nombre del vendedor con la función anidada BUSCARV AVALOR.


En la celda B32 necesitamos obtener el nombre de cada vendedor. Primero vamos a utilizar la función AVALOR para extraer el valor numérico de la celda A32.





Ya que tenemos el número del vendedor aplicamos la función BUSCARV.

La función AVALOR será el argumento Valor_buscado de BUSCARV, este valor lo buscaremos dentro de la tabla VENDEDORES, asignamos la columna 2 y el Ordenado será 0 (cero) para que busque el valor exacto.

La función BUSCARV quedaría de la siguiente manera:




Para mayor ilustración, la tabla VENDEDORES se refiere al rango de H31:I36.


Publicaciones relacionadas: Función personalizada AVALORFunción BUSCARVCómo nombrar un rango.



Esta y muchas otras funciones las podrás aprender en nuestro Curso de Excel Avanzado.


Teléfono en Guadalajara: 0133 3632-2719

domingo, 13 de enero de 2013

Convertir una lista vertical a horizontal con "Pegado especial - Transponer" en Excel 2007 y 2010.

Herramienta Pegado especial - Transponer.

La herramienta Pegados Especial - Transponer, sirve para convertir un rango vertical en horizontal y viceversa.

En la siguiente imagen tenemos una lista vertical con los meses del año, utilizando Pegado Especial - Transponer, lo vamos a convertir a horizontal.




Para transponer el rango de meses, primero se debe copiar dicho rango, dar clic derecho en la celda donde se quiere pegar (en este caso es la celda B1), y seleccionamos Pegado Espcial.





Se abre el cuadro de diálogo y seleccionamos Transponer.



Podemos ver el rango convertido a horizontal.



Esta y muchas herramientas más podrás aprenderlas en nuestro Curso de Excel para Contabilidad, Admón. y Finanzas.


TELÉFONO EN GUADALAJARA:             0133 36322719       .

Inicio de curso de Macros para Administración, Contabilidad Y Finanzas

Al finalizar el curso, el alumno: Obtendrá los conocimientos para automatizar tareas por medio de macros, ahorrando gran cantidad de tiempo en la creación de reportes, trabajo con diferentes libros, transferencia de datos, etc.

Beneficios. Ahorros de tiempo en automatización de procesos, será capaz de crear reportes para importar información en sistemas contables o  ERP, declaraciones informativas, DIOT, etc. Aprenderá a consolidar información de varias hojas de cálculo contenidas en varios libros de trabajo de manera automática. Aprenderá a generar instrucciones repetitivas para la depuración de reportes, eliminando celdas vacías o con información innecesaria.

Informática        Descargar Temario de Curso de Macros para Contabilidad, Administración y Finanzas.



CONTÁCTANOS: ATENCIONECAP@ECAPMEXICO.NET
TELÉFONO EN GUADALAJARA:              0133 36322719       

Inicio de Curso de Excel para Contabilidad, Administración y Finanzas.


Sede Guadalajara.




Fechas:30 de Enero, 06, 13, 20 y 27 de Febrero.

Horario:Miércoles de 3:00 pm a 8:00 pm

Duración:25 hrs.

Sede:Guadalajara

Lugar:Instalaciones ECAP

Dirección:Pedro Moreno #1250 int. 23 Col. Americana

Inversión:3,850 precio sin IVA.

Incluye:Material, Reconocimiento avalado por la Secretaría del Trabajo, Coffee Break.




Porqué debo asistir a este curso?



Justificación por Departamento Curso de Excel


Departamento de Cobranza.

Calcular vencimiento de facturas mediante funciones de fecha para poder dar seguimientos a saldos vencidos.
Crear reportes de pago de clientes y obtener saldos por cada uno mediante Tablas Dinámicas.
Registro de pagos de clientes mediante Layouts desde Excel.
Crear reportes de Antigüedad de Saldos de Clientes y llevar un análisis de la cobranza.
Formatos de Recepción de Efectivo donde se convierte el importe en letra.
Bajar Estados de Cuenta Bancarios a Excel y poder analizar los pagos de clientes.

Departamento de Recursos Humanos.

Trabajar con bases de datos de empleados mediante Tablas Dinámicas, para analizar información de Prestaciones, Retenciones deImpuestos, etc.
Cálculo de impuestos, IMSS, etc. mediante fórmulas y funciones anidadas.
Calcular un determinado ingreso neto mediante Buscar objetivo.
Obtener reportes de sueldos por departamento, los Mínimos, Máximos, Promedio, etc. ideal para negociaciones con Sindicatos.
Calcular antigüedad de empleados para determinar aguinaldos, finiquitos, etc.
Obtener reportes de acumulados de todos los empleados, por mes o anual, para saber totales en caja  o fondo de ahorros.
Consolidar varios reportes de nómina para determinar Ingresos y Prestaciones. Ideal para crear reportes para presentar la Declaración Anual de sueldos y Salarios.

Departamento de Impuestos.

Crear reportes para generar información para presentar DIOT, Devoluciones y compensaciones de IVA, todo esto mediante Layouts utilizando funciones, fórmulas anidadas, Herramientas Subtotales y Tablas Dinámicas.
Cálculo de impuestos mediante fórmulas anidadas y funciones.
Conciliar bases de datos para la deteminación del IETU para saber que facturas han sido liquidadas.
Aplicar Hipervínculos para abrir páginas de internet del SAT, Secretaría de Finanzas, etc.

Departamento de Contabilidad General.

Crear layout para captura de pólizas a Contpaqw, Contpaqi, COI, SAP, etc. Captura de nóminas, gastos, reembolsos, depreciaciones, bancos, etc.
Conciliaciones Bancarias mediante funciones y fórmulas anidadas.
Determinación de impuestos mediante reportes, funciones y fórmulas anidadas.
Crear layouts para DIOT, Devoluciones y compensaciones de IVA.
Conciliación automática de cheques en transito, bajando a excel los estados de cuenta bancarios.
Captura de facturas en dólares utilizando funciones de búsqueda para obtener tipos cambio.

Administradores.

Llevar un control de pago de Clientes, pago a Proveedores, Inventarios, etc. por medio de Tablas Dinámicas, Funciones, etc.
Control de Bancos mediante Excel.
Control de Bases de datos de empleados, monitorear sueldos por departamento.
Aprender a crear distintos tipos de formatos por medio de funciones, tablas dinámicas, Validación de celdas, etc. para la correcta administración de los recursos.
Poder monitorear mediante reportes, cálculos, etc.  información clave, pudiendo crear reportes estadísticos, gráficos dinámicos y otros controles mediante funciones, fórmulas, fórmulas anidadas, etc.
Llevar en Tablas Dinámicas reportes de Ventas, Compras, Gastos, etc. de manera mensual y crear comparativos entre meses y años, analizar tendencias de Ingresos, Egresos, Cobranza, etc.
Poder crear reportes de vencimientos de facturas de cobranza y cuentas por pagar, analizando fechas, días de crédito mediante funciones y fórmulas anidadas.

Dueños de Empresas que llevan su propia Administración.

Muy similar al rubro de Administradores.
Llevar Control de Ventas, Listas de clientes, Listas de productos, Comparativos de aumentos o disminuciones en ventas en un periodo dado, por año, por mes, etc.
Poder crear una jerarquía para saber cuales son los mejores clientes.
Poder crear formatos para cotizaciones.
Calcular Comisiones Automáticas de Vendedores, resaltando campos con colores cuando cumplan o no con determinadas condiciones (Herramienta formato condicional).
Llevar un control de pago de clientes, pago a proveedores, inventarios, etc. por medio de tablas dinámicas, funciones, etc.
Control de Bancos mediante Excel.
Control de Bases de datos de empleados, monitorear sueldos por departamento.
Aprender a crear distintos tipos de formatos por medio de funciones, tablas dinámicas, Validación de celdas, etc. para la correcta administración de los recursos.
Poder monitorear mediante reportes, cálculos, etc.  información clave, pudiendo crear reportes estadísticos, gráficos dinámicos y otros controles mediante funciones, fórmulas, fórmulas anidadas, etc.
Llevar en tablas dinámicas reportes de ventas, compras, gastos, etc. de manera mensual y crear comparativos entre meses y años, analizar tendencias de ingresos, egresos, cobranza, etc.
Poder crear reportes de vencimientos de facturas de cobranza y cuentas por pagar, analizando fechas, días de crédito mediante funciones y fórmulas anidadas.

Dueños de empresas que cuentan con personal encargado de cada una de las áreas.

Para los dueños de empresas que cuentan con administrador, contador, personal en pagos, cobranza, almacén, inventarios, etc., este curso es esencial para que comprendan el potencial que tiene el programa Excel para todas sus áreas y así saber qué información, reportes, formatos, etc. debe pedir a su gente, y que pueda medir de una mejor manera los tiempos de entrega de dicha información.


Departamento Tesorería.


Trabajar con bases de datos de pago a proveedores (utilizando funciones de búsqueda).

Trabajar con Edos. de Cuenta  de Bancos para obtener saldos diarios, se copia y pega en Excel.

Conciliación automática de cheques en tránsito, bajando a excel los Estados de Cuenta Bancarios.

Conciliaciones Bancarias por medio de fórmulas anidadas y funciones.

Crear Layouts para hacer pagos masivos en portales bancarios.
Conciliación con proveedores.
Manejar desde Excel Líneas de Crédito para el cálculo de los intereses, por medio de búsquedas con funciones de tasa TIIE.
Hacer programación de pagos por vencimientos de facturas.
Hacer Flujos de Efectivo mediante tablas dinámicas.
Registro de operaciones bancarias mediante pólizas automáticas por medio de un Layout desde Excel.
Formatos de cheques donde se convierten cantidades en letras y se crean bases de datos.
Reportes de Pago a Proveedores con Tablas Dinámicas manteniendo un historial de cada pago.
Trabajar con Bases de Datos para obtener Cuentas Bancarias, vencimientos, tipos de cambio para pago a Proveedores, Reembolsos de Gastos, Compra de Divisas, etc.
Trabajar con bases de datos para obtener tipos de cambio mediante funciones de búsqueda.
Crear reportes de captura de reembolsos o cajas chicas para captura automática de Gastos contra salidas bancarias, por medio de la herramienta Validación de celdas.
Utilizar hipervínculos para abrir desde un archivo de excel diferentes páginas de Bancos.

Departamento de Cuentas por Pagar.

Reportes de Pago a Proveedores con Tablas Dinámicas manteniendo un historial de cada pago.
Registro de facturas mediante Layout desde Excel.
Conciliaciones con Proveedores con Funciones de Búsqueda, de Texto, etc.
Registro de Reembolsos, Cajas chicas, Gastos de Agente Aduanal por medio de Layout.
Crear formatos utilizando validación de celdas para el registro automático en sistemas Contables, de Cajas chicas, Reembolsos de Gastos, etc. mediante Layout.
Calcular vencimientos de facturas por medio de funciones de fecha.



Contáctanos: atencionecap@ecapmexico.net
Teléfono en Guadalajara: 0133 3632-2719





Macro obtener los 56 colores de ColorIndex Excel VBA

Este tutorial explica cómo crear una macro que sirve para obtener los 56 colores de la paleta de colores para ser utilizados con la propiedad ColorIndex en VBA.

Para obtener los 56 colores de la paleta de colores, es necesario utilizar el Bucle For...Next.

A partir de la celda A1 debemos colorear la celda con el índice de color 1 y así
sucesivamente hasta llegar al 56.





.
La macro está asignada al botón Colores.

 

Es necesario habilitar macros para que funcione este archivo. Para saber cómo habilitar macros da clic aquí.

Nuestro curso de Macros para Administración, Contabilidad y Finanzas te ayudará a crear potentes macros para hacer más fácil tu trabajo.


CONTÁCTANOS: atencioneCAP@ECAPMEXICO.NET
TELÉFONO EN GUADALAJARA:              0133 36322719