............ Foro Excel
Si no lo has hecho aún: REGISTRATE!!!

Referencia relativa de columnas y filas a partir de un rango seleccionado

Ver el tema anterior Ver el tema siguiente Ir abajo

Referencia relativa de columnas y filas a partir de un rango seleccionado

Mensaje por acid_wild el Lun Jul 06, 2009 1:58 pm

Salu2. A continuación, planteo mi problema. Tengo una hoja con datos numéricos. A partir de una selección en Excel, requiero declarar como variables cada celda de la selección, de tal manera que pueda realizar cálculos (suma, resta, multiplicación, otras) entre las columnas y celdas. La macro final debería poder aplicarse en cualquier lugar de una hoja. El resultado de una suma o una resta irá en una celda fija, ejemplo A1. Agradezco la colaboración.

acid_wild

Masculino Cantidad de envíos : 15
Edad : 37
Ciudad - Pais : Puerto Rico
Version de Excel : 2007
Fecha de inscripción : 06/07/2009

Volver arriba Ir abajo

Re: Referencia relativa de columnas y filas a partir de un rango seleccionado

Mensaje por sailepaty el Lun Jul 06, 2009 5:43 pm

Me parece que no provees informacion suficiente para ayudarte. Si tu rango es de 1,000 filas por 250 columnas necesitas declarar 250,000 variables, como deseas definir las variables? En todo caso eso que deseas hacer en una macro se podria hacer directamente en la hoja.

Saludos
avatar
sailepaty
Moderador
Moderador

Masculino Cantidad de envíos : 135
Edad : 54
Ciudad - Pais : Dallas, TX
Version de Excel : 2007 y 2010
Fecha de inscripción : 04/03/2008

Volver arriba Ir abajo

Re: Referencia relativa de columnas y filas a partir de un rango seleccionado

Mensaje por acid_wild el Lun Jul 06, 2009 6:35 pm

Salu2 sailepaty. No se trata de rangos tan amplios. Se trata rangos variables pero no muy grandes. Mínimamente serán rangos de 4 a 12 columnas, y de 1 a 6 filas. El punto es que el valor del rango cambia. Claro, como indicas puedo hacerlo a mano pero cuando tratas con más de 10 o 12 mil datos se vuelve todo un tedio realizar los cálculos a mano.

acid_wild

Masculino Cantidad de envíos : 15
Edad : 37
Ciudad - Pais : Puerto Rico
Version de Excel : 2007
Fecha de inscripción : 06/07/2009

Volver arriba Ir abajo

Re: Referencia relativa de columnas y filas a partir de un rango seleccionado

Mensaje por sailepaty el Lun Jul 06, 2009 9:14 pm

Sigo sin entender la necesidad o aplicacion de tu requerimiento, pero solo como idea puedes definir una variable como variant, llenarla con el rango seleccionado y despues hacer referencia a las intersecciones para tus formulas.

Código:
Option Base 1
Sub Prueba()
Dim vBase as Variant
Dim lFila as Long
Dim lCol as Long
Dim lSuma as Long
vBase = Range("A2:J10").Value o Selection.Value
'Para sumar la cuarta fila puede ser
lSuma = 0
For lCol = 1 to 10
 lSuma = lSuma + vBase(4,lCol)
Next lCol
End Sub

Escrito aqui en el foro, sin probar y solo como sugerencia.
avatar
sailepaty
Moderador
Moderador

Masculino Cantidad de envíos : 135
Edad : 54
Ciudad - Pais : Dallas, TX
Version de Excel : 2007 y 2010
Fecha de inscripción : 04/03/2008

Volver arriba Ir abajo

Re: Referencia relativa de columnas y filas a partir de un rango seleccionado

Mensaje por acid_wild el Mar Jul 07, 2009 12:10 pm

Ok. Comprendo y agradezco tu sugerencia, la cual revisaré. No obstante, haré una explicación lo mejor posible. El problema parte de una selección múltiple que puede ser en cualquier ubicación de una hoja M.S. Excel. Posteriormente convierto la selección en una variable rango. Requiero varias cosas.
1. Sumar todos los valores en el rango seleccionado -lo cual he hecho con una función 'SUMAW' que se ejecuta desde la macro. Funciona bien para valores horizontales y verticales, pero no he podido ajustarla para múltiples selecciones. El valor de la suma lo he puesto en la celda 'L19'.
2. Quiero crear una función que sume: los valores de las columnas impares. En este caso los valores contenidos en la columnas B, D y F.
3. Requiero una función que multiplique un columna par por una impar. En este caso columna B por columna C, D por E, y F por G.

De momento, te participo del código que he adelantado

Sub Macro1()
Dim Rango1 As Range
Set Rango1 = Selection
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 250
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Rango1.Font.ColorIndex = 50
Range("L19") = SUMAW(Rango1)
End Sub

Function SUMAW(DATOS)
Dim NF, NC, X, Y As Integer
NF = DATOS.Rows.Count
NC = DATOS.Columns.Count
For X = 1 To NF
For Y = 1 To NC
SUMAW = DATOS(X, Y) + SUMAW
Next Y
Next X
End Function

Por último te consulto si sabes cómo puedo almacenar un valor de sumas parciales. Es decir, posteriormente realizaré este mismo procedimiento sobre otra región de Excel y preciso que mi aplicación tome el nuevo valor calculado de suma y lo compute con el anterior.

Salu2 y gracias.


Última edición por acid_wild el Mar Jul 07, 2009 12:17 pm, editado 1 vez

acid_wild

Masculino Cantidad de envíos : 15
Edad : 37
Ciudad - Pais : Puerto Rico
Version de Excel : 2007
Fecha de inscripción : 06/07/2009

Volver arriba Ir abajo

Re: Referencia relativa de columnas y filas a partir de un rango seleccionado

Mensaje por acid_wild el Mar Jul 07, 2009 12:13 pm

A propósito. Olvidé adjuntar la captura que realicé de Excel.

acid_wild

Masculino Cantidad de envíos : 15
Edad : 37
Ciudad - Pais : Puerto Rico
Version de Excel : 2007
Fecha de inscripción : 06/07/2009

Volver arriba Ir abajo

Re: Referencia relativa de columnas y filas a partir de un rango seleccionado

Mensaje por acid_wild el Mar Jul 07, 2009 12:18 pm

La imagen no se aprecia bien en el foro. Puedes verla en:

[Tienes que estar registrado y conectado para ver este vínculo]

acid_wild

Masculino Cantidad de envíos : 15
Edad : 37
Ciudad - Pais : Puerto Rico
Version de Excel : 2007
Fecha de inscripción : 06/07/2009

Volver arriba Ir abajo

Re: Referencia relativa de columnas y filas a partir de un rango seleccionado

Mensaje por acid_wild el Mar Jul 07, 2009 12:26 pm

[Tienes que estar registrado y conectado para ver este vínculo]

acid_wild

Masculino Cantidad de envíos : 15
Edad : 37
Ciudad - Pais : Puerto Rico
Version de Excel : 2007
Fecha de inscripción : 06/07/2009

Volver arriba Ir abajo

Re: Referencia relativa de columnas y filas a partir de un rango seleccionado

Mensaje por sailepaty el Mar Jul 07, 2009 1:51 pm

Sigo sin encontrar una logica del porque lo requieres de esa forma, pero para no dejar el tema sin resolver esta es la forma en que yo la haria.

Código:
Sub Prueba()
    'Le asigna un nombre al rango seleccionado
    Selection.Name = "rRng"
    'Muestra la suma de todos los elementos
    MsgBox [Sum(rRng)]
    'Muestra la suma de las columnas impares
    MsgBox [SUM(INDEX(rRng,,1))+SUM(INDEX(rRng,,2))+SUM(INDEX(rRng,,3))]
    'Muestra el producto de multiplicar columna 1 * columna 2
    MsgBox [SUMPRODUCT(INDEX(rRng,,1),INDEX(rRng,,2))]
End Sub


Saludos
avatar
sailepaty
Moderador
Moderador

Masculino Cantidad de envíos : 135
Edad : 54
Ciudad - Pais : Dallas, TX
Version de Excel : 2007 y 2010
Fecha de inscripción : 04/03/2008

Volver arriba Ir abajo

Re: Referencia relativa de columnas y filas a partir de un rango seleccionado

Mensaje por acid_wild el Mar Jul 07, 2009 5:05 pm

Tu forma funciona bien en selecciones regulares. No comprendo porque no funciona bien si tengo un rango seleccionado como el que ilustro en la figura adjunta.

acid_wild

Masculino Cantidad de envíos : 15
Edad : 37
Ciudad - Pais : Puerto Rico
Version de Excel : 2007
Fecha de inscripción : 06/07/2009

Volver arriba Ir abajo

Re: Referencia relativa de columnas y filas a partir de un rango seleccionado

Mensaje por acid_wild el Mar Jul 07, 2009 5:14 pm

Debo agregar que el código que me has compartido, está supeditado a que existirán columnas declaradas. Ejemplo, tu indicaste que existe la columna 3. Si la selección de rango que hago solo tiene dos columnas, me sacará un error de referencia, #¡REF! ¿Cómo lo resuelvo bajo tu fórmula?

acid_wild

Masculino Cantidad de envíos : 15
Edad : 37
Ciudad - Pais : Puerto Rico
Version de Excel : 2007
Fecha de inscripción : 06/07/2009

Volver arriba Ir abajo

Re: Referencia relativa de columnas y filas a partir de un rango seleccionado

Mensaje por sailepaty el Miér Jul 08, 2009 10:28 am

acid_wild escribió:Si la selección de rango que hago solo tiene dos columnas, me sacará un error de referencia, #¡REF! ¿Cómo lo resuelvo bajo tu fórmula?

Este codigo toma en cuenta la cantidad de columnas seleccionadas y arrojara resultados erroneos si el rango es irregular.

Código:
Sub Prueba()
    Selection.Name = "rRng"
    Dim lRes    As Long
    Dim lCol    As Long
    'Muestra la suma de las columnas impares
    lRes = 0
    For lCol = 1 To Range("rRng").Columns.Count - 1 Step 2
        lRes = lRes + Evaluate("SUM(INDEX(rRng,," & lCol & "))")
    Next lCol
    MsgBox lRes
    'Muestra el producto de multiplicar columnas pares * columnas impares
    lRes = 0
    For lCol = 1 To Range("rRng").Columns.Count - 1 Step 2
        lRes = lRes + Evaluate("SUMPRODUCT(INDEX(rRng,," & lCol & "),INDEX(rRng,," & lCol + 1 & "))")
    Next lCol
    MsgBox lRes
End Sub

Trabajar con rangos irregulares es bastante laborioso, en especial cuando no se tiene claro cual es la finalidad de hacerlo. Por lo cual si requieres mas ayuda esta supeditada a que hablemos de rangos regulares.

Saludos
avatar
sailepaty
Moderador
Moderador

Masculino Cantidad de envíos : 135
Edad : 54
Ciudad - Pais : Dallas, TX
Version de Excel : 2007 y 2010
Fecha de inscripción : 04/03/2008

Volver arriba Ir abajo

Re: Referencia relativa de columnas y filas a partir de un rango seleccionado

Mensaje por acid_wild el Vie Jul 10, 2009 11:01 am

Hola. Agradezco mucho tu asistencia y disposición. No obtante, mi objetivo se centra en rangos irregulares y a partir de tu indicación, noto que la comunicación no fluirá más. Muy agradecido.

acid_wild

Masculino Cantidad de envíos : 15
Edad : 37
Ciudad - Pais : Puerto Rico
Version de Excel : 2007
Fecha de inscripción : 06/07/2009

Volver arriba Ir abajo

Re: Referencia relativa de columnas y filas a partir de un rango seleccionado

Mensaje por acid_wild el Vie Jul 10, 2009 11:02 am

Si por favor puedes indicarme alguna referencia de rangos irregulares, te agradecería.

acid_wild

Masculino Cantidad de envíos : 15
Edad : 37
Ciudad - Pais : Puerto Rico
Version de Excel : 2007
Fecha de inscripción : 06/07/2009

Volver arriba Ir abajo

Re: Referencia relativa de columnas y filas a partir de un rango seleccionado

Mensaje por sailepaty el Vie Jul 10, 2009 2:28 pm

Haber aprovechando la hora del lunch, aquí tienes un ejemplo para trabajar con rangos irregulares.

Código:
Sub Prueba()
    Dim rRng As Range
    Dim lSum As Long
    Dim lProd As Long
    Dim lArea As Long
    Dim lCol As Long
    Dim lRow As Long
    Set rRng = Selection
    lSum = 0
    lProd = 0
    With rRng
        For lArea = 1 To .Areas.Count
            With .Areas(lArea)
                For lCol = 1 To .Columns.Count - 1 Step 2
                    For lRow = 1 To .Rows.Count
                        'Acumula la suma de las columnas impares
                        lSum = lSum + .Cells(lRow, lCol)
                        'Acumula el producto de multiplicar columnas impares * columnas pares
                        lProd = lProd + (.Cells(lRow, lCol) * .Cells(lRow, lCol + 1))
                    Next lRow
                Next lCol
            End With
        Next lArea
    End With
    MsgBox "Suma:" & Format(lSum, "#,###") & " / Producto:" & Format(lProd, "#,###")
End Sub


Saludos
avatar
sailepaty
Moderador
Moderador

Masculino Cantidad de envíos : 135
Edad : 54
Ciudad - Pais : Dallas, TX
Version de Excel : 2007 y 2010
Fecha de inscripción : 04/03/2008

Volver arriba Ir abajo

Re: Referencia relativa de columnas y filas a partir de un rango seleccionado

Mensaje por acid_wild el Vie Jul 10, 2009 8:03 pm

Gracias por continuar con el post. Disculpa. Me puedes explicar por favor el Set rRng = Selection, la estructura de los dos with. Comprendo para que sirve cada sentencia pero en tu lógica de programar, no me es claro. Permíteme saber cómo lo concebiste.

acid_wild

Masculino Cantidad de envíos : 15
Edad : 37
Ciudad - Pais : Puerto Rico
Version de Excel : 2007
Fecha de inscripción : 06/07/2009

Volver arriba Ir abajo

Re: Referencia relativa de columnas y filas a partir de un rango seleccionado

Mensaje por acid_wild el Vie Jul 10, 2009 9:23 pm

Hola. Muchas gracias por tu ayuda. Probé el código y funciona. Estoy muy, pero muy agradecido contigo. Me gustaría saber si puedo seguir comunicándome contigo para aprender más.

acid_wild

Masculino Cantidad de envíos : 15
Edad : 37
Ciudad - Pais : Puerto Rico
Version de Excel : 2007
Fecha de inscripción : 06/07/2009

Volver arriba Ir abajo

Re: Referencia relativa de columnas y filas a partir de un rango seleccionado

Mensaje por acid_wild el Lun Jul 27, 2009 6:21 pm

Gracias por tu respuesta. En relación con el mismo tema, ilustraré la situación con la siguiente imagen.


[Tienes que estar registrado y conectado para ver este vínculo]

En la columna A tengo una serie de valores numéricos: 1, 2, 3 y 4 distribuidos a lo largo de toda la columna. A partir de la selección, requiero un cálculo condicional relacionado con dicha columna. Ejemplo:
Si el valor de la celda en la columna A es 1, entonces haga el producto de la primer columna por la tercera.
Si el valor de la celda en la columna A es 2, entonces haga el producto de la primer columna por la tercera.
Si el valor de la celda en la columna A es 3, entonces haga el producto de la primer columna por la tercera.
Si el valor de la celda en la columna A es 4, entonces haga el producto de la primer columna por la tercera.
El resultado será el acumulado de cada condición. Es decir, cuatro sumatorias de los que cumplen cada condición en el total de las selecciones realizadas.

He reutilizado el código que me has compartido, y apliqué un condicional en las líneas que se ejecutan las operaciones de cálculo. Lamentablemente no me funciona bien. Me realiza cálculos de más y no comprendo porqué.

Código:

With rRng
  'Ejemplo desde la celda A5 hasta la celda A23
        For X = 5 To 23
            If Cells(X, 1) = "1" Then
                For lArea = 1 To .Areas.Count
                    With .Areas(lArea)
                        For lCol = 1 To .Columns.Count - 1 Step 5
                            For lRow = 1 To .Rows.Count
                            'Acumula la suma del producto de la columna uno por la columa tres en toda la selección
                            Suma1 = Suma1 + (.Cells(lRow, lCol)*.Cells(lRow, lCol + 1))
                            Next lRow
                        Next lCol
                    End With
                Next lArea
            End If
        Next X
        End With

Favor me indicas cómo proceder.

acid_wild

Masculino Cantidad de envíos : 15
Edad : 37
Ciudad - Pais : Puerto Rico
Version de Excel : 2007
Fecha de inscripción : 06/07/2009

Volver arriba Ir abajo

Re: Referencia relativa de columnas y filas a partir de un rango seleccionado

Mensaje por sailepaty el Mar Jul 28, 2009 11:55 am

acid_wild escribió:
Si el valor de la celda en la columna A es 1, entonces haga el producto de la primer columna por la tercera.
Si el valor de la celda en la columna A es 2, entonces haga el producto de la primer columna por la tercera.
Si el valor de la celda en la columna A es 3, entonces haga el producto de la primer columna por la tercera.
Si el valor de la celda en la columna A es 4, entonces haga el producto de la primer columna por la tercera.

No veo ninguna diferencia en la mutiplicacion a realizar dependiendo del numero de la columna A....

Trata de ser mas explicito.

Saludos
avatar
sailepaty
Moderador
Moderador

Masculino Cantidad de envíos : 135
Edad : 54
Ciudad - Pais : Dallas, TX
Version de Excel : 2007 y 2010
Fecha de inscripción : 04/03/2008

Volver arriba Ir abajo

Re: Referencia relativa de columnas y filas a partir de un rango seleccionado

Mensaje por acid_wild el Mar Jul 28, 2009 3:37 pm

Iniciaré la descripción del problema indicando que estoy trabajando sobre ‘n’ rangos irregulares en ‘m’ filas por ‘x’ columnas.



[Tienes que estar registrado y conectado para ver este vínculo]

En este rango irregular realizo varias operaciones que funcionan bien. Surge una novedad con los cálculos en el rango irregular. Tengo en la columna A una serie de valores numéricos: 1, 2, 3 y 4. Estos valores están distribuidos irregularmente en dicha columna -no siguen un orden espeífico.



[Tienes que estar registrado y conectado para ver este vínculo]

En la captura, existe un patrón de cinco columnas llamadas .Col1, .Col2, .Col3, .Col4, y .Col5. Actualmente realizo de forma manual dos cálculos:
[list=1][*] A partir de una selección de rango irregular, de todas las columnas '.Col1' requiero sumar los valores que coincidan con el valor uno '1' de la columna A. De esta manera el resultado de la sumatoria de las celdas que coinciden con el valor de celda cuyo valor es 1 en la columna A, es igual a 20 en la selección de rango irregular mostrado en la captura3.


[Tienes que estar registrado y conectado para ver este vínculo]

[list=2][*] Realizo el producto de la columna 1 por la columna tres en la selección del rango bajo el mismo condicional explicado, cuyo resultado es 200.


[Tienes que estar registrado y conectado para ver este vínculo]

Con los valores de celda cuyo valor sea 2, 3 y 4 en la columna A se realizará la misma prueba condicional. Los resultados que manualmente realizo como ejemplo.


[Tienes que estar registrado y conectado para ver este vínculo]

acid_wild

Masculino Cantidad de envíos : 15
Edad : 37
Ciudad - Pais : Puerto Rico
Version de Excel : 2007
Fecha de inscripción : 06/07/2009

Volver arriba Ir abajo

Re: Referencia relativa de columnas y filas a partir de un rango seleccionado

Mensaje por Contenido patrocinado


Contenido patrocinado


Volver arriba Ir abajo

Ver el tema anterior Ver el tema siguiente Volver arriba


 
Permisos de este foro:
No puedes responder a temas en este foro.