¿Cómo puedo separar los valores en una hoja de datos?
Nuevamente le escribo para plantearle la siguiente cuestión:
Tengo una hoja de datos (ejemplo más abajo), en ella el campo Importe contiene tanto los cargos como los abonos, se reconocen unos y otros por el campo Concepto, los cargos empiezan con una "C" y los abonos con una "R" .
Quiero hacer varias cosas,
1.- Separar los cargos y los abonos y llevarlos a columnas separadas, luego insertar un campo Saldo para, en base al campo NFactura que la macro sumarice los cargos por un lado y los abonos por otro de cada factura y genere un saldo de cada factura.
2.- Enseguida que pueda extraer sólo las facturas con saldo para su posterior análisis.
Fecha Concepto Nfactura Cliente Importe FechaVenc
01/01/2001 C1 B825630 1000
05/01/2001 RM B825630 1000
02/01/2001 CI B835630 2500
03/01/2001 RM B835630 1000
04/01/2001 RM B835630 2500
Tengo una hoja de datos (ejemplo más abajo), en ella el campo Importe contiene tanto los cargos como los abonos, se reconocen unos y otros por el campo Concepto, los cargos empiezan con una "C" y los abonos con una "R" .
Quiero hacer varias cosas,
1.- Separar los cargos y los abonos y llevarlos a columnas separadas, luego insertar un campo Saldo para, en base al campo NFactura que la macro sumarice los cargos por un lado y los abonos por otro de cada factura y genere un saldo de cada factura.
2.- Enseguida que pueda extraer sólo las facturas con saldo para su posterior análisis.
Fecha Concepto Nfactura Cliente Importe FechaVenc
01/01/2001 C1 B825630 1000
05/01/2001 RM B825630 1000
02/01/2001 CI B835630 2500
03/01/2001 RM B835630 1000
04/01/2001 RM B835630 2500
1 Respuesta
Respuesta de fejoal
1
Si entendí correctamente tu pedido, creo que una tabla dinámica resolverá tu problema eficientemente.
Una tabla dinámica es una herramienta de MS Excel para obtener un reporte agrupado de distintas maneras basado en datos incluidos en una tabla o base de datos.
Es dinámica porque, una vez armada, permite agregar, mover, o eliminar campos en ella hasta lograr el reporte deseado. Además, si cambian los datos en la base, bastará actualizarla (esto no es automático) para que refleje los nuevos resultados.
Previamente, necesitarás una columna adicional que indique que tipo de operación estará considerando en cada línea (Cargo o Abono). En una columna libre luego de la última ocupada ingresa esta fórmula en la primer línea con datos de tu base:
=SI(IZQUIERDA(B2;1)="C";E2;-E2)
[Considera si sueles usar comas o punto y coma para separar argumentos de las funciones. Yo usé ";"]
Asígnale el formato deseado y, luego, copia esta celda y pégala en las restantes de esta misma columna, una por cada fila de tu base.
Esta fórmula toma el importe como está si fuera un cargo y si fuera un abono lo convierte en negativo. Después verás para qué.
Ahora bien, para armar la Tabla dinámica, sigue los siguiente pasos:
1. - Es necesario que cada columna tenga un titulo único. Selecciona tu lista desde esta primera fila (títulos) hasta el último registro en ella. Continúa resaltando este rango para que futuros registros (if any)se incluyan en la Tabla dinámica.
Mantenla seleccionada.
2. - Pulsa el botón en "Datos" encontrado en la barra del menú principal y selecciona "Asistente de tabla dinámica".
3. - Una ventana pide seleccionar la fuente de los datos. Marca "Lista o base de datos de MS Excel".
4. - Si hubieras dejado tu rango seleccionado, se asumirá ésta como la base de datos fuente. En caso contrario, puedes seleccionar el área en este paso.
5. - Se mostrarán los títulos de las columnas en el próximo paso. Verás por ejemplo:
Fecha
Concepto
Nfactura
Cliente
Importe
FechaVenc
C ó R (este título corresponde a la columna que agregaste)
6. - El próximo paso depende de ti, porque esto es muy "personalizable". Sólo tienes que arrastrar etiquetas al área donde quieres que ellas sean mostradas.
Por ejemplo, en tu caso:
- Arrastra la etiqueta "Nfactura" al área de FILAS.
- Arrastra la etiqueta "C o R" al área de DATOS
Escoge a operador de Suma.
(Nota: otras funciones posibles sobre campos numéricos son:
Contar
Promedio
Min
Max
Producto
Contar Números
Desvest
Desvestp
Var
Varp)
Presiona el botón "Siguiente"
7. - Selecciona dónde poner la tabla dinámica (puede ser en una hoja nueva) y aprieta el botón del "Terminar".
Así conseguirás una tabla donde mostrará algo así:
Nfactura | Suma de C o R
Y debajo de cada titulo un listado por cada factura y su saldo (por aquella suma algebraica de positivos y negativos).
Finalmente si eliges una celda con el saldo de esta tabla dinámica y presionas el botón de ordenar, rápidamente verás cuales son las facturas con saldos, cuales las saldadas (en cero) y, eventualmente, si hay alguna que tenga más abonado que cargado (tendrá saldo negativo). Es más, puedes dar doble click sobre esta celda y MS Excel creará una hoja nueva con sólo los valores que dieron origen a tal resultado.
aclarando qué entendí mal o qué faltó.
Una tabla dinámica es una herramienta de MS Excel para obtener un reporte agrupado de distintas maneras basado en datos incluidos en una tabla o base de datos.
Es dinámica porque, una vez armada, permite agregar, mover, o eliminar campos en ella hasta lograr el reporte deseado. Además, si cambian los datos en la base, bastará actualizarla (esto no es automático) para que refleje los nuevos resultados.
Previamente, necesitarás una columna adicional que indique que tipo de operación estará considerando en cada línea (Cargo o Abono). En una columna libre luego de la última ocupada ingresa esta fórmula en la primer línea con datos de tu base:
=SI(IZQUIERDA(B2;1)="C";E2;-E2)
[Considera si sueles usar comas o punto y coma para separar argumentos de las funciones. Yo usé ";"]
Asígnale el formato deseado y, luego, copia esta celda y pégala en las restantes de esta misma columna, una por cada fila de tu base.
Esta fórmula toma el importe como está si fuera un cargo y si fuera un abono lo convierte en negativo. Después verás para qué.
Ahora bien, para armar la Tabla dinámica, sigue los siguiente pasos:
1. - Es necesario que cada columna tenga un titulo único. Selecciona tu lista desde esta primera fila (títulos) hasta el último registro en ella. Continúa resaltando este rango para que futuros registros (if any)se incluyan en la Tabla dinámica.
Mantenla seleccionada.
2. - Pulsa el botón en "Datos" encontrado en la barra del menú principal y selecciona "Asistente de tabla dinámica".
3. - Una ventana pide seleccionar la fuente de los datos. Marca "Lista o base de datos de MS Excel".
4. - Si hubieras dejado tu rango seleccionado, se asumirá ésta como la base de datos fuente. En caso contrario, puedes seleccionar el área en este paso.
5. - Se mostrarán los títulos de las columnas en el próximo paso. Verás por ejemplo:
Fecha
Concepto
Nfactura
Cliente
Importe
FechaVenc
C ó R (este título corresponde a la columna que agregaste)
6. - El próximo paso depende de ti, porque esto es muy "personalizable". Sólo tienes que arrastrar etiquetas al área donde quieres que ellas sean mostradas.
Por ejemplo, en tu caso:
- Arrastra la etiqueta "Nfactura" al área de FILAS.
- Arrastra la etiqueta "C o R" al área de DATOS
Escoge a operador de Suma.
(Nota: otras funciones posibles sobre campos numéricos son:
Contar
Promedio
Min
Max
Producto
Contar Números
Desvest
Desvestp
Var
Varp)
Presiona el botón "Siguiente"
7. - Selecciona dónde poner la tabla dinámica (puede ser en una hoja nueva) y aprieta el botón del "Terminar".
Así conseguirás una tabla donde mostrará algo así:
Nfactura | Suma de C o R
Y debajo de cada titulo un listado por cada factura y su saldo (por aquella suma algebraica de positivos y negativos).
Finalmente si eliges una celda con el saldo de esta tabla dinámica y presionas el botón de ordenar, rápidamente verás cuales son las facturas con saldos, cuales las saldadas (en cero) y, eventualmente, si hay alguna que tenga más abonado que cargado (tendrá saldo negativo). Es más, puedes dar doble click sobre esta celda y MS Excel creará una hoja nueva con sólo los valores que dieron origen a tal resultado.
aclarando qué entendí mal o qué faltó.
