formula que extrae varios elementos?

[Miguelsp]

Hola

Tengo una duda e igual me pueden ayudar

Una de mis hojas excel tiene una columna que incluye referencias con su correspondiente precio. El problema es que en una misma celda me incluye varias referencias con sus precios con lo que no puedo saber cual es el precio final del pedido. Os pongo un ejemplo

LK.A5CES.002 - 105.70, bt.A57ES.030 - 51.63
AP.A4CES.014 - 16.34
60.A56ES.003 - 21.62, 60.A589S.004 - 46.62

Como pueden ver puede ser que haya una referencia, dos, ninguna o hasta 15. No espero que haya más pero podría ser, es decir, el número de referencias no está limitado en principio.

Ahora mismo lo tengo medio arreglado con una macro que me hace un texto en columnas utilizando el espacio como separador y me elimina las columnas que contienen la referencia y el signo - . Luego elimino las , y ya me queda el coste en distintas columnas, que claro, tengo que sumar... En definitiva la macro me ayuda a limpiar pero al final me toca hacer demasiado trabajo manual.

Mi pregunta es si habría alguna manera de hacerlo con una fórmula para que pueda ponerla directamente en una celda o si me pueden ayudar con una macro más simplificada.

mil gracias por adelantado!

 

[cgcamal]

Hola Miguel,

Si entendí bien, querés quitar todo lo que no es "valor monetario" (###.##) para luego colocar en
diferentes columnas cada cantidad/costo y sumar los costos de cada columna.

Si es así, una opción sería hacer como sigue:

1-) Usar Expresiones Regulares (Regex) para eliminar todas las referencias, espacios y guiones, y dejar
solo costos en la celda. Entonces colocá en un modulo el siguiente código de funciones que aceptan
expresiones regulares.
* Referencia: Expresiones Regulares con Excel

Option Explicit
#Const LateBind = True

Function SUSTITUIR_ExpresionRegular(ReplaceIn, _
        ReplaceWhat As String, ReplaceWith As String)
    #If Not LateBind Then
    Dim RE As RegExp
    Set RE = New RegExp
    #Else
    Dim RE As Object
    Set RE = CreateObject("vbscript.regexp")
        #End If
    RE.Pattern = ReplaceWhat
    RE.Global = True
    SUSTITUIR_ExpresionRegular = RE.Replace(ReplaceIn, ReplaceWith)
    End Function
Function ENCONTRAR_ExpresionRegular(FindIn, FindWhat As String, _
        Optional IgnoreCase As Boolean = False)
    Dim i As Long
    #If Not LateBind Then
    Dim RE As RegExp, allMatches As MatchCollection, aMatch As Match
    Set RE = New RegExp
    #Else
    Dim RE As Object, allMatches As Object, aMatch As Object
    Set RE = CreateObject("vbscript.regexp")
        #End If
    RE.Pattern = FindWhat
    RE.IgnoreCase = IgnoreCase
    RE.Global = True
    Set allMatches = RE.Execute(FindIn)
    ReDim rslt(0 To allMatches.Count - 1)
    For i = 0 To allMatches.Count - 1
        rslt(i) = allMatches(i).Value
        Next i
    ENCONTRAR_ExpresionRegular = rslt
    End Function

Estas 2 funciones (SUSTITUIR_ExpresionRegular y ENCONTRAR_ExpresionRegular) las podés usar ahora
como cualquier otra de Excel, y para tu caso, asumiendo que los datos están en columna A, y poniendo
en B1 la función

=SUSTITUIR_ExpresionRegular(A1,"[ ]*..\.\S+\.\d+ - ","")

se obtiene,

105.70,51.63

2-) Entonces generalizando en una macro podés agregar el siguiente código debajo del anterior:

Sub Separar_En_Columnas_y_Sumar()
'CC 19/04/2011
'Macro para separar contenido de celdas después de aplicar con fórmula de
' sustitución de Expresiones Regulares "SUSTITUIR_ExpresionRegular()"

Dim Ul As Integer
Dim Uc As Integer

Application.ScreenUpdating = False

'Usando función de Sustitución de Expresiones Regulares en B1, tomando datos de columna A
Range("B1") = "=SUSTITUIR_ExpresionRegular(RC[-1],""[ ]*..\.\S+\.\d+ - "","""")"
    
 Ul = Range("A1").End(xlDown).Row 'Última fila usada
 
    Range("B1").AutoFill Destination:=Range("B1:B" & Ul)
    Range("B1:B" & Ul).Copy
    
    'Copiando valores de B:B a columna C para poder separar usando comas como delimitador
    
    Range("C1:C" & Ul).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
        
    Range("C1:C" & Ul).TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False
        
    Uc = Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Column 'Última columna usada

    Range("C" & Ul + 1) = "=SUM(R[-" & Ul & "]C:R[-1]C)"
    Range("C" & Ul + 1).AutoFill Destination:=Range(Cells(Ul + 1, 3), Cells(Ul + 1, Uc)), Type:=xlFillDefault
    
    'Eliminando columna de datos originales y dejando solo las de costos
    Columns("A:B").Delete Shift:=xlToLeft
    Range("A1").Select
    
Application.ScreenUpdating = True
End Sub

Ahora, con ambos códigos dentro del módulo, sólo debés
1-) Ubicar los datos en columna A, comenzando en A1 (sino lo adaptás); y,
2-) Ejecutar la macro "Separar_En_Columnas_y_Sumar".

Entonces sumado el primer codigo de las funciones de Expresiones Regulares más la macro obtendrás
los costos separados en columna comenzando en A1 con sus respectivas sumas y elimanadas las
columnas originales.

Espero te ayude.

Saludos cordiales

 

[Miguelsp]

Hola pude más o menos sacar la información usando

=RegExpSubstitute(BX202;"............ -";"+")

ya que había referencias que eran sólo numéricas y otras que en el tercer grupo de caracteres eran alfanuméricos.

Al final me da algo así como

+ 286.66, + 75.74+ 14.14

que tras tratar con una pequeña macro que sustituya ", " por "" y luego "." por "," me da

+ 286,66+ 75,74+ 14,14

Pero el problema es que esta suma no la ejecuta! Si es sólo una bloque lo considera número y me lo presenta bien, pero tiene varios me lo deja como texto y no calcula... Se te ocurre alguna manera?

Mil gracias

M

 

[cgcamal]

Hola Miguel,

Pero el problema es que esta suma no la ejecuta! Si es sólo una bloque lo considera número y me lo presenta bien, pero tiene varios me lo deja como texto y no calcula... Se te ocurre alguna manera?

Entonces necesitás sumar horizontalmente los costos que quedan despues de eliminar
los valores innecesarios?

Veo que como separador decimal usás coma, pero igual, en este caso si hasta el momento estás obteniendo "+ 286,66+ 75,74+ 14,14",
entonces si al inicio de la cadena agregás un signo igual "=", la suma se puede hacer con una macro.

Para obtener sólo números con separador decimal igual a coma "," y los signos mas "+" entre números más el "=" al inicio podés usar:

="=" & SUSTITUIR(SUSTITUIR_ExpresionRegular(A1,"[, ]*..\.\S+\.\d+ - ","+"),".",",")

Luego de esto solo falta usar una pequeña macro para pasar el valor de texto obtenido, en una cadena de números para sumar.

Se puede usar cualquiera de estas 3:

Sub Suma_Costos1()[COLOR=green]'Para convertir el texto en el resultado total de la suma dentro de la celda contigua[/COLOR]
Range("B1").Copy
Range("C1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("C1") = Range("B1").Value
End Sub
-------------------------------------------------------------------
Sub Suma_Costos2()[COLOR=green]'Para convertir el texto en el resultado total de la suma dentro de la celda contigua[/COLOR]
Range("C1") = Range("B1").Value
End Sub
-------------------------------------------------------------------
Sub Suma_Costos3() [COLOR=green]'Para convertir el texto en el resultado total de la suma dentro de la misma celda[/COLOR]
Range("B1") = Range("B1").Value
End Sub

Además, tal vez podrías mostrarnos cuál es el formato de todos los tipos de referencias que tenés
colocando algunas líneas del archivo original.

Así podriamos tratar de pensar en una Regex más general y aislar los costos para poderlos sumar en conjunto.

La Regex que usé reemplaza cadenas del tipo XX.XXXXX.XXX, donde X puede ser número
o letra, más precisamente, los caracteres antes y después del punto pueden ser
cualquier caracter y los de enmedio una cadena.

Saludos cordiales

 

[cgcamal]

+ 286,66+ 75,74+ 14,14

Pero el problema es que esta suma no la ejecuta! Si es sólo una bloque lo considera número y me lo presenta bien, pero tiene varios me lo deja como texto y no calcula... Se te ocurre alguna manera?

Hola Miguel,

Aparte de lo que te mencioné en el post anterior, luego de averiguar como hacer que Excel evalue un texto que
parece fórmula y que presente el resultado, para que probés te puedo decir que hagás como sigue:

1-) En A1 colocá estos datos:
<TABLE style="WIDTH: 241pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=321><COLGROUP><COL style="WIDTH: 241pt; mso-width-source: userset; mso-width-alt: 11739" width=321><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 241pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=321>LK.A5CES.002 - 105.70, bt.A57ES.030 - 51.63</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20>AP.A4CES.014 - 16.34</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20>60.A56ES.003 - 21.62, 60.A589S.004 - 46.62


2-) En B1 colocar esta función:

=SUSTITUIR(SUSTITUIR_ExpresionRegular(A1;"[, ]*..\.\S+\.\d+ - ";"+");".";",")

3-) Definir un nombre, Por ej. Suma_Costos:
- En Excel 2007/2010 (Menú Fórmulas-->Asignar nombre)

</TD></TR></TBODY></TABLE>
- Versiones anteriores (Insertar-->Nombres-->Definir)

4-) Una vez definido el nombre, dentro de la casilla de "Hace Referencia a:" colocar

=EVALUAR($B1)

5-) Ahora, en cualquier celda que escribás (por ej. en C1)

=Suma_Costos

te saldrá la evaluación de la fómula en formato de texto de la celda B1, en este caso el texto representa una suma.

Con esto evitarás escribir una macro para eliminar, puntos, comas, para después sumar las cantidades. Todo hecho
a nivel de funciones, excepto el código VBA para las 2 funciones de Expresiones Regulares.


Espero sea de utilidad.


Saludos cordiales

 

[Miguelsp]

de verdad muchas gracias por la ayuda

ya casi lo tenemos

finalmente la fórmula que filtra bien es

="="&SUBSTITUTE(RegExpSubstitute(RegExpSubstitute(B4;"[, ]*..\.\S+\.\S+ - ";"+");"[, ]*\d+ - ";"+");".";",")

fíjate que he puesto S+ en vez de D+ ya que d+ hacía referencia a una cadena numérica mientras podía ser alfanumérica.

Pero no consigo que calcule con ninguna de las tres macros. Con la opción nombrar fórmula sí. Pero mi excel es en inglés y no entiende la expresión =EVALUAR($B1) al poner la referencia

No la sabrás en ingles?

Mil gracias!

 

[cgcamal]

Pero no consigo que calcule con ninguna de las tres macros.

Me parece extraño que no funcione ninguna macro para pasar el valor de texto a resultado aritmético. En este caso
podrías probar no usar las macros que esscrbí, sino grabar o probar una propia, tal vez algo en las versiones de ambos
excel provoque que a mi si me funcione.

Con la opción nombrar fórmula sí. Pero mi excel es en inglés y no entiende la expresión =EVALUAR($B1) al poner la referencia

No la sabrás en ingles?

La función indocumentada "EVALUAR()" en inglés es "EVALUATE()".

Que bien que ya obtuviste la Regex y la función que sirve para todos los casos, solo que con respecto a la función
para hacerla más simple se pueden suprimir dos cosas.

Luego de hacer pruebas detecté que la función no necesita el igual "=" antes y también podría simplemente usarse
la función SUBSTITUTE() que trae Excel en conjunto con la de Regex, una vez cada una, asi:
SUBSTITUTE(Regex_SUBSTITUTE(Regex;"Texto nuevo");".";","), obvianmente teniendo la Regex general para
tu caso, sino usarla como la tenés, funciones anidadas. Lo anterior que digo podría servir para hacer la función más
simple y más rápida en la ejecución solamente.

Entonces la función final quedaría en lugar de:
(lo que está en rojo se puede eliminar):

=[B][COLOR=Red]"="&[/COLOR][/B]SUBSTITUTE(RegExpSubstitute(RegExpSubstitute(B4;"[, ]*..\.\S+\.\S+ - ";"+");"[, ]*\d+ - ";"+");".";",")

Así:

 =SUBSTITUTE(RegExpSubstitute(RegExpSubstitute(B4;"[, ]*..\.\S+\.\S+ - ";"+");"[, ]*\d+ - ";"+");".";",")

Saludos cordiales