Officefull.es

Excel, word, powerpoint, android

Concatenar Matrices Agosto 20, 2008

Concatenar Matrices

Válido para versiones Office 97 – 2007

Siguiendo con la Saga de artículos sobre Concatenar o Unir celdas en Excel, hoy os presentamos una manera de realizarlo casi casi sin macros, digo casi casi, porque simple y llanamente utilizaremos Macros de las antiguas, las heredadas, NO macros en sí, sino Una Función Excel- 4.0 llamada RefTexto.

Alternativas

Las alternativas con que contamos para concatenar rangos son:

1 Usar la función de hoja de cálculo

=concatenar(…

2 Concatenar usando el carácter de concatenación: => &

3 Definir una función personalizada(con la que tendrías un mejor control)

4 usar nombres y una que otra función de manera… “no documentada”?

Desarrollo

Tenemos en nuestro archivo de ejemplo, varios rangos con textos para su posible concatenación

  • Por Filas

A4: F4

rango

  • Por columnas

A7:A12

Concatenar columnas

  • Por rango múltiple de Filas y columnas

A15:D16

Concatenar filas-Columnas

Tenemos la celda la cual pondremos nuestro Separador

G19

Concatenar o Unir celdas excel

Podemos poner lo que más gustemos para su separación [] [ _ ] [ / ] [ \ ] [ y ] etc…

Contamos para concatenar o unir nuestras celdas con Una validación de datos

Concatenar matrices excel

Como observáis, podemos elegir de la lista Validación el rango a concatenar o unir el cual marca la columna [ H ]

Cogiendo como Ejemplo el Rango múltiple cuyo nombre definido es lunes, y tenemos el resultado en la celda C21

Columna –Fila

Resultado concatenar o unir

Y en la celda C22 >>

Columna-columna

Resultado concatenar matrices

Si os fijáis en la Unión o concatenación de Columna- fila y Columna-Columna veréis que:

Cuando usas un rango de una sola dimensión: A4:F4 o A7:A12 no tiene importancia…PERO… cuando “el rango” es de varias filas y columnas (empieza a ya NO ser “lo mismo”) Columna-Filaconcatena primero A15, B15, C15… y luego A16, B16, C16… y luego A17, B17, C17… etc… Columna-Columna concatena primero A15, A16, A17 y luego B15, B16,B17… y luego C15, C16,C17

Los nombres Definidos, sus fórmulas y el propósito

s → hoja1!$g$19 aquí es donde está la referencia a los caracteres como "separador"  r → indirecto(!a21) OJO: este nombre se define estando B21 como la celda activa  n → contara(r) cuenta las celdas NO vacías en el rango "escrito" en la celda izquierda  nF → filas(r) cuenta las filas del rango "escrito" en la celda izquierda  nC → columnas(r) cuenta las columnas del rango "escrito" en la celda izquierda   Los dos nombres siguientes son la (re)conversión de la matriz para sus índices xFila / xColumna   rF → reftexto(desref(r,entero((fila(indirecto("1:"&n))-1)/nc),residuo(fila(indirecto("1:"&n))-1,nc),1,1),1)  rC → reftexto(desref(r,residuo(fila(indirecto("1:"&n))-1,nf),entero((fila(indirecto("1:"&n))-1)/nf),1,1),1)   Los dos nombres siguientes son la obtención indice(matriz,n) para xFila / xColumna (SOLO del 2 al 8)  cFa → si(n>1,s&evaluar(indice(rf,2)),"")&si(n>2,s&evaluar(indice(rf,3)),"")& si(n>3,s&evaluar(indice(rf,4)),"")&si(n>4,s&evaluar(indice(rf,5)),"")& si(n>5,s&evaluar(indice(rf,6)),"")&si(n>6,s&evaluar(indice(rf,7)),"")& si(n>7,s&evaluar(indice(rf,8)),"")  cCa → si(n>1,s&evaluar(indice(rc,2)),"")&si(n>2,s&evaluar(indice(rc,3)),"")& si(n>3,s&evaluar(indice(rc,4)),"")&si(n>4,s&evaluar(indice(rc,5)),"")& si(n>5,s&evaluar(indice(rc,6)),"")&si(n>6,s&evaluar(indice(rc,7)),"")& si(n>7,s&evaluar(indice(rc,8)),"")  Los dos nombres siguientes son la concatenación del indice(matriz,1) MáS la concatenación de "los siguientes"   cF → evaluar(indice(rf,1))&cfa  cC → evaluar(indice(rc,1))&cca 

El “significado” de los nombres

"s" = el (o los) separador(es)  "r" = "el rango" tomado de la celda "a la izquierda" "n" = número de celdas NO vacías en "el rango"  "nF" = el número de Filas en "el rango"  "nC" = el número de Columnas en "el rango" "cFa" = la primer "serie" de concatenaciones xFilas (índices 2 a 8)  "cCa" = la primer "serie" de concatenaciones xColumnas (índices 2 a 8)  "cF" = la concatenacion xFila del índice(matriz,1) MAS las siguientes (cFa, cFb, cFc, etc.)  "cC" = la concatenacion xColumna del índice(matriz,1) MáS las siguientes (cCa, cCb, cCc, etc.)

Notas Importantes

  • No está contemplado el uso de celdas Vacías por ello se usa contara(r) en el nombre ‘n
  • Si hay celdas vacías la concatenación será (notoriamente) diferente de Columna-Fila o bien Columna-columna.
  • Si necesitamos las formulas Columna-Columna o bien Columna-Fila ( cC o cF ) a lo largo de nuestras hojas o bien en nuestra hoja donde estamos trabajando necesitaremos que en la celda donde insertemos las formulas a la izquierda incluyamos o bien una validación de datos como el Articulo aquí descrito o bien un nombre definido ejemplo :

Concatenar matrices excel

  • Si necesitamos rangos más largos de 8 celdas, podemos agregar/definir/crear MAS nombres parecidos acFa / cCa que incluyen ya desde el índice 2 a 8 y (obviamente) concatenarlos a cf/ cC, sólo sigue “la secuencia”: nombres: cFa, cFb, cFc (xFilas) y cCa, cCb, cCc (xColumnas)

Ejemplo – Creamos un nuevo nombre definido para Columna Fila:

cFa _1=>  SI(n>8,s&EVALUAR(INDICE(rF,9)),"")&SI(n>9,s&EVALUAR(INDICE(rF,10)),"")& SI(n>10,s&EVALUAR(INDICE(rF,11)),"")&SI(n>11,s&EVALUAR(INDICE(rF,12)),"")& SI(n>12,s&EVALUAR(INDICE(rF,13)),"")&SI(n>13,s&EVALUAR(INDICE(rF,14)),"")  cCa_1=>  SI(n>8,s&EVALUAR(INDICE('rC',9)),"")&SI(n>9,s&EVALUAR(INDICE('rC',10)),"")& SI(n>10,s&EVALUAR(INDICE('rC',11)),"")&SI(n>11,s&EVALUAR(INDICE('rC',12)),"")& SI(n>12,s&EVALUAR(INDICE('rC',13)),"")&SI(n>13,s&EVALUAR(INDICE('rC',14)),"") 

Entonces para La fórmula Columna-Fila: =cF

EVALUAR(INDICE(rF,1))&cFa&cFa_1

Y para la fórmula columna-columna: = cC

EVALUAR(INDICE(rF,1))&cFa&cFa_1

Si vamos a realizar los nombres definidos en un Office Excel 2007>> << OjO >> puesto que necesitaremos que el nombre definido para ‘r’ [ rango tomado a la izquierda ] sea precedido por un Guion Bajo [ _r ] puesto que el nuevo Excel 2007 tiene algunos formatos y configuraciones de estados Unidos [ US Centric ]

Antecedentes

a) concatenar rangos/matrices es básicamente obtener el índice (matriz, fila, columna) de cada elemento

b) para evitar la necesidad de proveer índice de columna se requiere (re)convertir la matriz a SOLO filas

c) con una sola “dimensión” es solo cuestión de concatenar indice(matriz,1)&indice(matriz,2)&indice(matriz,3) …

d) las formulas en los nombres tienen una “restricción” de NO MAS de 255 caracteres <\°|°/> [aumentado en office 2007 ]

e) por esto, los nombres han sido “recortados a los mínimos indispensables” (más bien “representativos”)

f) finalmente, la (re)conversión de la matriz requiere el uso de macro-funciones del (viejo ?) excel-4

<< OJO>> lo anterior es “la causa” de que al abrir el libro excel te pregunte si deseas habilitar “las macros

Descarga del archivo

Concatenar Matrices (9.33 kB)

Categorías: Sin categoría

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *