una - Recuperando valores de columnas combinadas de Excel

comparar dos celdas en excel y devolver un valor

Question

Estoy haciendo un hlookup contra de un valor que abarca varias columnas. Mis datos son similares a esto:

      A      B      C      D 
  ---------------------------  
1|       Col1          Col2
2|     x      y      z      w
3|
4|

En las filas 3 y 4 (A3, B3, C3, D3, etc.), me gustaría poner fórmulas que hagan un hlookup en otro lugar del libro de trabajo. El truco es que me gustaría buscar "Col1" para las columnas A y B y "Col2" para las columnas C y D. "Col1" está en A1, pero realmente está integrado en A1 y B1. Cuando hago referencia a A1, aparece "Col1", pero cuando hago referencia a B1, el valor de retorno está en blanco.

¿Algunas ideas?

Answer 1

Las celdas B1 y D2 no contienen valores, solo A1 y C1 tienen algo dentro de ellas.

Así que solo deberá asegurarse de que sus fórmulas en las columnas A y B se refieran a A1 como el valor de búsqueda, y que sus fórmulas en las columnas C y D se refieran a C1 para el valor de búsqueda.

Answer 2

Para obtener acceso a las etiquetas "Col1" y "Col2", puede usar lo siguiente:

= INDICE ($ 1: $ 1,1, COLUMNA () - MOD (COLUMNA () - 1,2))

Nota: Esto supone que está agrupando el mismo número de celdas. Si fueran tres celdas, simplemente cambiaría el último número en la fórmula a un 3, y así sucesivamente.

Editar: Así es como funciona:

ÍNDICE ($ 1: $ 1,1, x) devuelve el valor de la celda en la fila 1, columna x. Si su tabla no está realmente ubicada en la esquina superior izquierda de la hoja de trabajo, puede cambiarla al rango real que incluye todas las etiquetas fusionadas. En este caso, sería: ÍNDICE ($ A $ 1: $ D $ 1,1, x)

COLUMN () devuelve el número de columna de la celda actual (1 en la columna A, 2 en la columna B, etc.)

MOD (COLUMN () - 1, x) devuelve un desplazamiento de la columna actual a la columna que contiene la etiqueta adecuada

Answer 3

Construí una función simple en vba que resolverá este problema:

Function mergedText(rngMergedCell As Range)

    If rngMergedCell.MergeCells = True Then
        mergedText = rngMergedCell.MergeArea(1, 1)
    Else
        mergedText = rngMergedCell
    End If

End Function

Si la celda es una celda fusionada, la función devolverá el valor en el primer elemento de la celda fusionada, aquí es donde la celda fusionada almacena su valor

Answer 4

Aquí hay otra solución que también puede funcionar cuando las celdas combinadas son de diferentes anchuras, déjenme ilustrar con un ejemplo:

1. Abra un Excel nuevo, combine B1, C1, D1
2. Escriba Col1 en la celda combinada
3. En B2 , escriba fórmula = B1 , y en C2 = C1 , en D2 = D1
4. Debería ver B2 para ser Col1, mientras que C2, D2 son 0
5. En B3 , escribe la fórmula = A3 , cópiala
6. Haga clic con el botón derecho en la celda combinada B1: D1 , seleccione "pegar especial -> fórmulas"
7. Debería ver que la celda fusionada es 0
8. Escriba Col1 en la celda combinada
9. Ahora debería ver que todos los B2, C2, D2 son Col1 , es decir, ahora puede hacer referencia a la celda fusionada como espera que sea.

Si puede combinar varias celdas, cada una de diferentes anchuras, simplemente pegue la fórmula en todas ellas de una vez.

La razón detrás de este trabajo es debido a una elección de diseño más completa por parte de Microsoft. Parece que cuando pega fórmulas en celdas combinadas, cada celda subyacente recibe la fórmula (en contraste, si ingresa un valor, solo la celda superior izquierda lo obtiene). De modo que puede usarlo a su ventaja y pegar una fórmula que haga referencia la celda contigua y luego sobrescribe la celda superior izquierda con el valor que desea, luego cada celda subyacente a la celda fusionada tendrá ese valor.

Answer 5

Me doy cuenta de que llego tarde a este hilo, pero encontré una respuesta realmente simple para esto.

Si, por ejemplo, su etiqueta se fusiona en 4 columnas a1: d1, y si hace referencia a b1, devolverá "". Para encontrar dinámicamente las etiquetas correctas, use este fx en su nueva tabla:

=if(OriginalTable!B1="",ThisTable!A1,OriginalTable!B1)

Estoy seguro de que se dará cuenta de que esto capturará los rangos en e1: h1, etc. a medida que arrastre.

Eso es. Espero que ayude a alguien.

Answer 6

Una variante más genérica de la propuesta de e.James es:

={INDEX($A$1:A1, 1, MAX(NOT(ISBLANK($A$1:A1))*COLUMN($A$1:A1)-COLUMN($A$1)+1))}

Esto se basa en el hecho de que las celdas combinadas están vacías, excepto la primera (a menos que esté en un caso como la propuesta de Martin).

Nota: Las llaves están ahí para marcar una fórmula de matriz (no las ingrese, solo presione alt + return para validar la fórmula en la celda).