Por Chuck Vigeant
Este es el primero de dos artículos en los que voy a mostrarles varias formas de abordar las ventas e impuestos por estado de envío dentro de Excel – una sin tabla pivotante, y otra con una tabla pivotante. Sí, usaremos el driver QODBC para importar los datos, pero los guiaré por la mano en esa parte.
El propósito principal de este ejercicio es triple:
- Crear un simple impuesto sobre las ventas enviando un informe estatal con los artículos de venta y el impuesto en columnas separadas
- Demostrar varias maneras de usar Excel para este propósito
- Usar una tabla pivotante, y demostrar su poder y facilidad de creación
Creación de un informe de ventas/impuestos por ciudad de envío en Excel
Supongo que muchos de los que hemos usado QuickBooks durante años hemos crecido usando la función de exportación a Excel. Por razones de comodidad, probablemente continuamos haciéndolo – aunque el uso del controlador QODBC podría ahorrarnos tiempo en el proceso – especialmente si es algo que repetimos cada mes.
Para aquellos que están asustados o abrumados por la perspectiva de aprender algo técnico , entiendo por qué podría rehuir las tablas, relaciones, datos, etc. La curva de aprendizaje puede ser empinada y puede quitarle tiempo a las horas facturables; créame que lo entiendo.
La función de la tabla dinámica de Excel también lleva tiempo de aprendizaje y, sí, puede ser difícil. Pero estoy seguro de que más de ustedes han tratado de jugar con la característica de la tabla dinámica que con la de QODBC. ¿Alguien quiere apostar? ¡Usar una mesa giratoria es mucho más fácil de lo que se piensa!
¿Cuándo debo usar las tablas pivotantes?
Muchos de nosotros confundimos las tablas pivotantes con las tablas cruzadas o el análisis multidimensional , pero hay dos palabras que describen mejor las tablas pivotantes:
- Resumen, en el que se pueden mostrar fácilmente datos resumidos de cada grupo en una lista
- Dinámico, en el que puedes cambiar fácilmente cómo quieres que se agrupen las cosas, por ejemplo, arrastrando y soltando
Así que si quisieras hacer un subtotal de una lista de todos los artículos de una factura antes de los impuestos, sería muy fácil hacerlo usando una tabla pivotante. Pero primero, vamos a establecer la tabla mostrando cómo hacerlo de la manera más difícil , mientras que le presentamos algunas de las características disponibles – pero no muy utilizadas – en QODBC y Excel 2007.
Usando Excel sin la tabla pivote, paso a paso
Desde dentro de Excel, invoca el Microsoft Query Wizard:
Elija la fuente de datos de QuickBooks:
Verá una lista de tablas de datos de QuickBooks:
Desplácese hacia abajo hasta la tabla de la Línea de Facturas (haga clic en el signo «+» para convertirlo en un «menos»):
Selecciona los siguientes campos y haz clic en el botón Siguiente:
Vamos a filtrar las fechas:
Luego filtra por Estado de la dirección de envío = «CA» y haz clic en el botón Siguiente:
Salte la siguiente pantalla, haga clic en el botón Siguiente:
Devuelva los datos a Excel:
Por último, decida dónde colocar los datos en su hoja de trabajo:
Los datos regresan en Excel:
Sin embargo, si te das cuenta, tenemos un problema: el mecanismo de subtotal está gris, y es una de las principales razones por las que usaríamos tablas pivotantes para este ejemplo. La opción Subtotal está no disponible para un rango dinámico, como el creado por nuestra consulta QODBC.
Sin embargo, completaremos el ejercicio para demostrar el uso de la herramienta de subtotal. Si no la han usado antes, la encontrarán muy útil para una variedad de desafíos:
Primero, eliminemos la conexión con la fuente de datos en vivo para poder utilizar la herramienta de subtotal (Haga clic con el botón derecho del ratón en cualquier lugar dentro del rango.)
Recibirás el siguiente mensaje; haz clic en OK:
Ahora verá que el mecanismo de subtotal está disponible en la pestaña de datos. Usémoslo.
(Puede recibir el siguiente mensaje si no incluyó los encabezados de las filas en su definición de rango. Haz clic en Sí para continuar).
Asegúrate de activar las siguientes opciones y haz clic en OK:
Obtendrá algo como lo siguiente (los cuadros amarillos que representan el total de cada factura):
Ahora hagamos clic de nuevo en el botón de subtotal: Observe que estamos añadiendo otro subtotal (Reemplazar los subtotales actuales es sin marcar ).
Fíjense ahora que tenemos una columna que nos da el total de las ventas (amarillo), y otra que nos da el total de los impuestos (púrpura):
También obtenemos los grandes totales calculados automáticamente:
Esto es genial, pero hay algunas advertencias
Hemos creado un ejemplo que, siendo realistas, le faltan algunos elementos:
- Sólo utilizamos facturas (no recibos de venta ni abonos)
- Sólo representa los artículos y clientes que fueron etiquetados como sujetos a impuestos
- Se basa en la acumulación
- No muestra la tasa de impuesto cobrada, el artículo de impuesto de ventas usado, o el nombre del cliente
- No tenemos una fórmula MENSUAL, así que podemos desglosar las cantidades por mes
- No hemos tenido en cuenta el hecho de que algunas transacciones no tienen una dirección de envío, sólo una dirección de facturación.
Podemos incluir los siguientes elementos en nuestra consulta para abordar algunas de las preocupaciones de (A) a (E) anteriores. Sólo agregue algunos otros elementos de la tabla de InvoiceLine como este:
Para obtener el cálculo del MES y la dirección de envío o de facturación, tendremos que hacer un poco de magia.
Lo haremos en el ejemplo del próximo artículo cuando creemos la tabla pivote – y obtengamos todo lo que necesite para tener un informe preciso y completo del Impuesto sobre las Ventas por Estado de Envío – con la capacidad de analizar rápidamente usando métodos de arrastrar y soltar.
Sobre el autor:
Chuck Vigeant M.Ed., fundador de ACCOUNTiGRATE, LP , es considerado por muchos como la mayor autoridad en integración de QuickBooks, reportes e Inteligencia de Negocios, y es el padre de los reportes personalizados de QuickBooks usando Crystal Reports y el driver QODBC ahora incluido en QuickBooks Enterprise Solutions. Vigeant también es consultor de The Sleeter Group .
Artículos relacionados:
- Entendiendo la opción Deshacer en Excel
- Algunos usos improbables para la función SUMPRODUCTO de Excel