Pregunta Expresiones simétricas en matriz excel


A veces trabajo con matrices simétricas en MS-Excel (tanto v2007 como v2003).

¿Existe alguna opción para ayudarme a copiar las expresiones del triángulo inferior al superior?

Debería ser algo así como copiar y pegar / transponer, pero esas funciones normalmente funcionan solo con áreas rectangulares.

en la imagen agregada puede ver un ejemplo de una expresión que debo replicar vinculando el valor simétrico en el triángulo superior de la matriz. symmetric expression matrix


10
2017-10-14 13:12


origen


Respuestas:


Para obtener el número en la celda apropiada, podemos usar OFFSET y la dirección de la celda forma la base de la tabla. Tenga en cuenta que la fórmula producirá un error de "Referencia circular" si se ingresa en la diagonal. La fórmula funcionará para ambos lados de la diagonal; solo tienes que decidir cuál contendrá los datos y cuál contendrá la fórmula.

Offset toma Row y Column para decidir el objetivo. Al restar la fila y columna de la celda base de la posición actual, podemos invertir la fila y las columnas, y obtener los datos.

Usando su ejemplo, con el origen de la tabla en B2, terminamos con la siguiente fórmula:

=OFFSET($B$2,COLUMN()-COLUMN($B$2),ROW()-ROW($B$2))

puedes copiar esta fórmula en las celdas y obtener el reflejo. Ahora que tiene el número, puede hacer cualquier cálculo que necesite en la reflexión. Usando su ejemplo, esto haría que la fórmula:

=10-OFFSET($B$2,COLUMN()-COLUMN($B$2),ROW()-ROW($B$2))

Resultado:

example

Utilizando INDEX hacerlo no volátil cambiaría la fórmula ligeramente. Primero, necesitaríamos una referencia a toda la tabla, no solo a la celda superior. Segundo, necesitaríamos agregar 1 al cálculo de fila / columna, ya que se refiere a la primera celda como fila / columna 1, no una compensación de 0 como la fórmula anterior.

=INDEX($B$2:$K$11,COLUMN()-COLUMN($B$2)+1,ROW()-ROW($B$2)+1)

y tu ejemplo de 10-Cell se convertiría en:

=10-INDEX($B$2:$K$11,COLUMN()-COLUMN($B$2)+1,ROW()-ROW($B$2)+1)

9
2017-10-14 15:20



Como lo demuestra una de las respuestas anteriores, esto se puede hacer mediante el uso de fórmulas de Excel. Sin embargo, considero que este es un procedimiento muy tedioso. Especialmente si esto es algo que necesita hacer regularmente. En ese caso, VBA podría ahorrarle mucho tiempo.

El siguiente código funcionará en una selección cuadrada y llenará el resto de la matriz sin importar si es la parte inferior o superior de la matriz que está previamente llena.

Option Explicit

Sub FillSymetricMatrix()
    Dim i As Integer, j As Integer
    Dim SelRng As Range
    Dim FillArea As String
    Dim FRow As Integer
    Dim FCol As Integer

    Set SelRng = Selection
    FRow = SelRng.Rows(1).Row
    FCol = SelRng.Columns(1).Column

    'Returns information about which area to fill
    If ActiveSheet.Cells(FRow + SelRng.Rows.Count - 1, FCol).Value <> vbNullString Then       'Lower filled
        If ActiveSheet.Cells(FRow, FCol + SelRng.Columns.Count - 1).Value = vbNullString Then 'Upper empty
            FillArea = "Upper"
        Else
            FillArea = "Error"
        End If
    Else
        If ActiveSheet.Cells(FRow, FCol + SelRng.Columns.Count - 1).Value <> vbNullString Then 'Upper filled
            FillArea = "Lower"
        Else
            FillArea = "Error"
        End If

    End If

    'Determines if the selection is square
    If SelRng.Rows.Count <> SelRng.Columns.Count Then FillArea = "Error"


    'Fills empty area of the square (symetric) matrix
    Select Case FillArea
        Case Is = "Upper"
            For i = 0 To SelRng.Rows.Count - 1 Step 1
                For j = 0 To SelRng.Columns.Count - 1 Step 1
                    If i <= j Then ActiveSheet.Cells(i + FRow, j + FCol).Value = ActiveSheet.Cells(j + FRow, i + FCol).Value
                Next j
            Next i

        Case Is = "Lower"
            For i = 0 To SelRng.Rows.Count - 1 Step 1
                For j = 0 To SelRng.Columns.Count - 1 Step 1
                    If i <= j Then ActiveSheet.Cells(j + FRow, i + FCol).Value = ActiveSheet.Cells(i + FRow, j + FCol).Value
                Next j
            Next i

        Case Else
            MsgBox "The procedure cannot be performed on the current selection!"
    End Select
End Sub

2
2017-10-14 15:29



Supongo que lo que necesitas es una función que devuelve el valor "diagonal" de una matriz cuadrada, p. para cualquier X(j,k) regreso X(k,j)

Prueba esto:

Function DIAGONAL(Arg As Range, Reference As Range) As Variant
Dim MyRow As Long, MyCol As Long

    If Reference.Rows.Count <> Reference.Columns.Count Then
        DIAGONAL = CVErr(xlErrRef)
    Else
        MyRow = Arg.Row - Reference.Row + 1
        MyCol = Arg.Column - Reference.Column + 1
        If MyRow < 1 Or MyCol < 1 Or MyRow > Reference.Rows.Count Or MyCol > Reference.Columns.Count Then
            DIAGONAL = CVErr(xlErrNA)
        Else
            DIAGONAL = Reference(MyCol, MyRow)
        End If
    End If

End Function

una vez que ingresaste esta función en VBA, puedes utilizarla dentro o fuera de tu matriz cuadrada ... solo necesitas asegurarte de que tu argumento (parámetro: Arg) está dentro de la matriz (parámetro: Referencia) ... o obtienes un # N / A error. O se obtiene un error #REF si la matriz no es cuadrada.

Entonces en tu ejemplo, entrarías en B4: =10-DIAGONAL(B4,$B$2:$K$11) y copie esto en todo el triángulo inferior.

Incluso puede transponer una matriz completa ... en su captura de pantalla, pasar a la celda B13, ingresar =DIAGONAL(B2,$B$2:$K$11) y copia 9x abajo y derecha

Sin botones, sin necesidad de iniciar explícitamente un Sub ... cualquier tamaño de matriz n x n, maneja cadenas y números, ...


2
2017-10-14 16:04



Aquí hay un ejemplo con VBA. Comience con una tabla sin llenar y un botón.

SCREEN1

Luego haz que el botón ejecute el código:

Option Explicit

Private Sub symmButton_Click()
    MakeSymmetric Range("B2")
End Sub

Public Sub MakeSymmetric(ByRef r As Range)

    Dim M As Long
    M = CountCols(r)

    Dim vals() As Variant
    vals = r.Resize(M, M).Value2

    Dim i As Long, j As Long
    For i = 2 To M
        For j = 1 To i - 1
            vals(i, j) = vals(j, i)
        Next j
    Next i

    r.Resize(M, M).Value2 = vals
End Sub


Public Function CountCols(ByRef r As Range) As Long
    If IsEmpty(r) Then
        CountCols = 0
    ElseIf IsEmpty(r.Offset(0, 1)) Then
        CountCols = 1
    Else
        CountCols = r.Worksheet.Range(r, r.End(xlToRight)).Columns.Count
    End If
End Function

y finalmente observar los resultados

enter image description here


1
2017-10-14 14:12



Similar a la solución de Sean, también usaría fórmulas. Para obtener el valor transpuesto, use esta fórmula:

=INDEX($B$2:$G$7,COLUMN()-COLUMN($B$2)+1,ROW()-ROW($B$2)+1)

Si quieres hacer una operación más compleja (p. =10-[transposedValue]), Te recomendaría que uses un rango con nombre: inserta un nuevo nombre, p. TransposedValueen el Administrador de nombres. En lugar de un enlace celular, proporcione la fórmula anterior. Ahora puedes literalmente escribir la siguiente fórmula en tu matriz:

=10-TransposedValue

1
2017-10-30 15:20



Tengo de esta manera. Como dijo, copie y pegue el trabajo de transposición en un rango rectangular. Y tu problema es que tienes un rango triangular.

Te encantará esto ...

1). Seleccione el rango cuadrado que contiene su matriz triangular superior y Copiar.

2). Seleccione un celda en un lugar vacío y hacer los siguientes dos pasos

  • a.) Pegado especial - Valores
  • b.) Pegado especial - Valores - Transposición - Saltar espacios en blanco

Y tienes tu matriz simétrica :-)

Añil.


0
2018-02-24 14:08



Mezclando el código de relleno de Ja72 con el código de función de Excel de SeanC c, creo que puedo hacer una plantilla de matriz genérica que esté correctamente llena con la fórmula dinámica de Excel. Tan dinámico, y puede ser reutilizado sin copiar y pegar.

Public Sub MakeSymmetric(ByRef r As Range)

    Dim M As Long
    M = 300
    ' Was CountCols(r), but I just limited to 300 columns for now

    Dim vals() As Variant
    vals = r.Resize(M, M).Value2

    Dim i As Long, j As Long
    For i = 2 To M
        For j = 1 To i - 1
            vals(j, i) = "=OFFSET($B$2,COLUMN()-COLUMN($B$2),ROW()-ROW($B$2))"

        Next j
            'Make diagonal down the middle show ---
            vals(j, i) = "---"
    Next i
    vals(1, 1) = "---"

    r.Resize(M, M).Value2 = vals
End Sub

Sub FillSymmetric()
    MakeSymmetric Range("B2")
End Sub

Realmente no conozco ningún VB, así que todavía no he descubierto cómo completar el encabezado. Aún no conozco Stackoverflow, pero intentaré agregar una imagen. Lista original para matricular

Transposición dinámica de los valores escritos en SouthWest half to NorthEast half


0
2018-03-27 07:25



Respuesta corta: INDIRECT(ADDRESS(COLUMN(D2), ROW(D2)))

Exploración: puede recordar que usamos coordenadas con números para representar una ubicación en el sistema de coordenadas cartesianas. Por lo tanto, es fácil obtener un valor simétrico diagonal, p. Ej. simplemente cambie (2, 3) a (3, 2).

Pero en Excel, necesitamos un acuerdo si queremos hacerlo. Porque la dirección está marcada por una combinación de una letra y un dígito, por ejemplo B2. No puedes simplemente cambiar B2 a 2B. Afortunadamente, aún podemos usar números para representar una célula aprovechando el poder de COW () y COLUMN ().

En la imagen de abajo, C2 y B3 son simétricos Esto muestra cómo poner el valor de C2 a B3. enter image description here


0
2018-05-04 15:05