Eliminar Subtotales que deben valor cero

rvillarroel · el 7 de noviembre

Saludos a todos, tengo una base de dato bastante extensa de cuentas por cobrar clientes y me gustaría eliminar los subtotales que tengan saldo en cero para efecto de análisis de dicha data, alguien que me preste el apoyo de una macro que pueda agilizar dicho trabajo; adjunto ejemplo.

Saludos;

Ejemplo.xls

tese1969 · el 7 de noviembre

En tu ejemplo no indicas cómo quieres que aparezca la información......ni qué sería necesario eliminar.....

Un saludo,

Tese

rvillarroel · el 7 de noviembre

Tese, lo que necesito que solo queden los totales que tiene saldo ó monto y los totales que den como resulato cero eliminarlo, en el nuevo archivo adjunto coloco como quiero que aparecez para que sea mas facil visualizar.

Saludos.

Ejemplo(2).xls

tese1969 · el 7 de noviembre

Mira a ver si vamos avanzando.........

He incluido un pequeño código en un módulo........

Prueba y me cuentas...

Un saludo,

Tese

Ejemplo(2).xls

rvillarroel · el 8 de noviembre

Tese, excelente la macro pero cuando lo utilizo en un archivo del 118.000 fila hay totales con saldo cero que no lo elimina.

tese1969 · el 8 de noviembre

No los elimina porque yo puse en el código hasta la fila 20000..........desconocía que necesitaras tantas filas....

Cambia donde pone......



For Each cell In Range("f2:f20000")

[/CODE]

por.......

[CODE]
For Each cell In Range("f2:f200000") [COLOR=#ff0000]'doscientos mil........si son más filas aún aumentas el número[/COLOR]
[/CODE]

Prueba a ver si así va bien.....

Un saludo,

Tese

rvillarroel · el 23 de noviembre

Tese, hay dos cosas que debe definir la macro, eliminar las celdas que dan como resultado cero, es decir las celdas que intervienen en el subtotal en cero y otro punto es que hay subtotales con valor cero que no son eliminados.

Saludos;

digitalboy · el 25 de noviembre

Hola! Realmente necesitas que esta tarea sea de manera automatizada? o es solo por que no encontraste la manera de hacerla en forma manual, la cual es posible y solo toma menos de 5 minutos....

Aun asi estoy trtando de hacerla en Python... me tope con algunos problemas pero espero pronto terminarlo!

Saludos!

- - - - - Mensaje combinado - - - - -

Bien ya tengo la solucion en Python para tu problema, si quieres darte la oportunidad de probar y aprender python aqui esta la guia para usar el complemento en Excel:

Xefion – DiscoveryScript « MeXcel Plus

Este es el script Python que resuelve tu problema:



import time


tempo = time.time()

ws = excel.ActiveSheet    #ws hara referencia a la hoja actual

filas = []    #se define una lista (llamada filas) en blanco

datos = []    #se define una lista (llamada datos) en blanco

aux = []


for fila in ws.Range["A1"].CurrentRegion.Rows:    #recorremos fila por fila la region activa...

    filas.append(list(fila.Value2))    #convertimos en lista los valores de la fila, y la lamacenamos de la lista de datos


for fila in filas:    #recorremos el contenido de la lista de datos (la cual es una lista de listas)...

    aux.append(fila)    #agregamos fila (la cual es una lista) a la lista aux

    datos.append(fila)    #agregamos fila (la cual es una lista) a la lista datos


    if "Total" in str(fila[-1]):    #si la palabra "Total" se encuentra en el ultimo elemento de la lista fila...

        if int(fila[-3]) == 0:    #y si el penultimo elemento es igual a cero...

            for i in range(len(aux)):    #deacuerdo al tamaño de la lista aux..

                datos.pop()    #se va eliminando la ultima lista contenida en la lista datos

                aux = []

        else:

            aux = []


archivo = open("c:\\datos.txt", "w")    #se crea el archivo "c:\\datos.txt" para escribir en el...

datos.insert(0, filas[0])    #se agregan los encabezados


for fila in datos:    #se recorre el contenido de la lista datos...

    aux = [str(i) for i in fila]    #cada uno de los datos los convertimos a cadena de texto y los recolectamos en una lista (aux)

    archivo.write(",".join(aux))    #escribimos el contenido de la lista aux en el archivo (concatenado cada uno de sus elementos por una coma, se trata de un CSV)

    archivo.write("\n")    #se escribe un salto de linea


archivo.write(" Tiempo consumido %f segundos!" %(time.time()-tempo))

archivo.close()    #se cierra el archivo

[/CODE]

y la solucion en VBA es:

[CODE]
Option Explicit

Sub Procesa()
Dim datos As Range
Dim celda As Range
Dim aux As Variant
Dim tempo As Double

tempo = Timer

Set datos = Range("B1").CurrentRegion
Range("A:A").Insert
Set datos = datos.Resize(, 1)

datos.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(RC[3]=0,""Eliminar"",""No Eliminar"")"
'datos.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=RC[3] =0"
Set datos = datos.Offset(0, -1)
Set datos = datos.Resize(datos.Rows.Count - 1).Offset(1, 0)

datos.FormulaR1C1 = "=IF(OR(RC[1]=""Eliminar"",RC[1]=""No Eliminar""),RC[1],R[1]C)"
aux = datos
datos = aux
datos.Item(1).Offset(-1, 0) = "Filtro"
Set datos = datos.CurrentRegion
datos.Select

Range("K1:K2") = Application.Transpose(Array("Filtro", "No Eliminar"))
datos.AdvancedFilter xlFilterCopy, Range("K1:K2"), Range("M1"), False
datos.Columns(2).SpecialCells(xlCellTypeFormulas) = vbNullString
Range("A:A").Delete
Range("J:L").Delete

MsgBox "Tiempo: " & Timer - tempo & " segundos!", vbInformation, "Tiempo Consumido"
End Sub
[/CODE]

La diferencia entre ambos es que el script python te guarda la extraccion en un archivo de texto en tu disco duro, el cual tendrias que abrir y delimitar con comas y la macro en vba te pone al lado la extraccion.

Saludos!

Ah por cierto en cuanto a tiempos de procesamiento entre ambas soluciones:

VBA - 0.265625 Segundos!

Python - 0.015633 Segundos!

Seria interesante poder medir los tiempos entre ambas soluciones procesando ese archivo de 118 mil registros que mencionas con ambos metodos y ver cual nos reditua el mejor tiempo en este caso en particular!

rvillarroel · el 25 de noviembre

Hola digitalboy, excelente tu colaboración en realidad necesito que esta tarea sea automatizada ya que de manera manual tardaría mucha ya que trabajo con una base de dato bastante extensa aproximadamente de 100.000 filas y tengo poca experiencia referente a las macros; tu solución en VBA aun me indica cuales serian los subtotales que debo eliminar pero necesito que me borre las filas que dan como resultado cero; de manera que me quedaría una base de dato únicamente las partidas pendiente.

Saludos:

digitalboy · el 25 de noviembre

Tu solución en VBA aun me indica cuales serian los subtotales que debo eliminar pero necesito que me borre las filas que dan como resultado cero; de manera que me quedaría una base de dato únicamente las partidas pendiente.

No se supone que mi macro extrae las partidas pendientes (que es lo mismo que eliminar las partids en cero)? ya me confundi!

joshua · el 25 de noviembre

Saludos.

Prueba con la siguiente macro acondiciona el rango que necesitas.

Sub Eliminar_Saldo_Cero()

Dim celda As Range

Application.ScreenUpdating = False

    With Sheets("Hoja1")

        For Each celda In .Range("D4:D49"). _

            SpecialCells(xlCellTypeVisible)

            If celda = 0 Then _

            d = d & "," & celda.Address

        Next celda

        rango = Right(d, Len(d) - 1)

        .Range(rango).EntireRow.Delete

        .Range("D1").Select

    End With

Application.ScreenUpdating = True

End Sub

[/CODE]

Prueba y comentas.

Atte.

joshua

el 25 de noviembre

Hola! r (digitalboy y joshua)

Te adjunto -en la Hoja 2- otra forma de hacerlo.

Nota que la columna con el Nº de factura no requiere estar ordenada.

Saludos, Cacho R.

Ejemplo-3.xls

digitalboy · el 26 de noviembre

No se.... todavia estoy dudoso de si esto realmente merece ser automatizado, si esto! es un proceso intermedio y le siguen otros despues que van a ser automatizados, sin duda debe automatizarse, pero si solo es extraer los datos de acuerdo al criterio y hasta ahi llega todo, no hubiese sido mas facil usal una tabla dinamica para ver las facturas que estan en saldo ceroy las que no y despues extraer las facturas que tienen saldo diferente de cero y despues hacer un filtro avanzado para extraer los datos... espero que alguno de los otros colegas del foro puedan brindarte una solucion satisfactoria!

Saludos!

rvillarroel · el 27 de noviembre

Hola Cacho, estuve probando tu macro, pero cuando trabajo con una data grade (130000 filas excel) no me da el resultado que deberia; sera que la macro no esta diseñada para una data tan grande???

Agradecido por la ayuda;

Saludos.

el 27 de noviembre

... cuando trabajo con una data grade (130000 filas excel) no me da el resultado que deberia; sera que la macro no esta diseñada para una data tan grande??? ...

Antes bien te diría: ¡Todo lo contrario! pues el método sugerido tiende a ser de aplicación primaria para los casos de muchos datos.

___

Te sugiero que revises varias cosas:

- ¿Estás seguro que tu tabla de datos tiene 5 columnas (período, debe, haber, fecha y factura) en el orden indicado?...

- ¿Estás seguro que tu tabla comienza en A1 como el ejemplo mostrado?

- ¿Estás seguro que tu tabla no tiene celdas en blanco a lo largo de la columna A?

- Y finalmente: ¿En qué te basas para afirmar que los resultados no son lo que deberían?

Por ejemplo podrías intentar trabajar -exclusivamente- con las facturas en las que crees haber detectado diferencias (2, 3 o 4 de ellas), y analizar la diferencia entre el código entregado y tus análisis paralelos.

En cualquier caso podrías subir aquí un archivo con esas facturas y te ayudaríamos a ver donde están las diferencias...

Saludos, Cacho R.

genu72 · el 10 de diciembre

Hola, llevo un buen rato intentando adaptar esta macro, pero no soy capaz de añadir más columnas con datos. En mi caso son 7.000 líneas y funciona bien.

Gracias y saludos.

Cabeceras.xls