jueves, 16 de febrero de 2017

Alternativas a la función UNIRCADENAS de Excel 365

Recientemente una alumna, con una versión de escritorio de Excel 2016, preguntaba por la posibilidad de concatenar fácilmente el contenido de varias celdas añadiendo un separador entre ellas...
Obviamente salió a la luz la existencia solo para la versión de Excel 3656 la función UNIRCADENAS, la cual ofrece y cubre la necesidad... pero ¿y que pasa con los usuarios de otras versiones de Excel?.
Improvisé rápidamente unas funciones anidadas para responder la duda.. aunque lógicamente tenía ciertas limitaciones.
Esta fue la función adaptada a su caso, para unir cuatro celdas:
=IZQUIERDA(A9&"|"&B9&"|"&C9&"|"&D9;LARGO(A9&"|"&B9&"|"&C9&"|"&D9)-(4-CONTARA(A9:D9)))



Como se observa, se une empleando el operador de unión ampersand (&) el contenido de las celdas, incorporando entre cada una de ellas un separador (la barra vertical |):
A9&"|"&B9&"|"&C9&"|"&D9

A partir de ese dato devuelto, se emplean las funciones de texto IZQUIERDA y LARGO junto a CONTARA, para eliminar el exceso de barras añadidas, provenientes de las celdas vacías.

Un problema añadido sería que en caso de que las celdas rellenas no fueran contiguas, el resultado obtenido, no sería de nuestro gusto.
Un último problema es la limitación del número de celdas, siendo necesaria una modificación de la formula en caso de requerir unir más de esas cuatro celdas.


Para evitar estos contratiempos, me he permitido definir con algo de programación, una función personalizada (UDF) en la ventana de código de un módulo estándar:

Function ConcatenaCeldas(rng As Range, Separador As Variant) As String
Dim Final As String
'recorremos las celdas del rango
For Each celda In rng
    'construimos el concatenado celda a celda
    'con la precaución de si la celda tiene o no contenido
    Final = Final + IIf(celda.Value = "", CStr(celda.Value), CStr(celda.Value) + Separador)
Next celda
'devolvemos el dato eliminando el último Separador añadido...
ConcatenaCeldas = Left(Final, Len(Final) - 1)
End Function



El resultado, como se vé en la imagen siguiente, rompe cualquier limitación anterior... permitiendo además elegir el separador a incluir:

Alternativas a la función UNIRCADENAS de Excel 365



Una ayuda para los que no trabajamos con Excel 365...

martes, 14 de febrero de 2017

Algunas Especificaciones de la Hoja de Cálculo Excel

Toca hoy repasar algunas de las especificaciones y límites técnicos de nuestra hoja de cálculo. Al fin y al cabo es fundamental conocer los límites de nuestra herramienta...
Estas restricciones o limitaciones corresponden a las versiones Excel 2010, 2013 y 2016 (hasta la fecha).


Algunos básicos:
1 Tamaño de hoja: 1.048.576 filas por 16.384 columnas
Como curiosidad, estos números no son casualidad, responden a un valor basado en un dato binario: 2 elevado a 20 (=1.048.576 filas) y 2 elevado a 14 (=16.384 columnas).
Para versiones anteriores como 2003: 2 elevado a 16 (=65536 filas) y 2 elevado a 8 (=256 columnas)
2 Ancho máximo de columna: 255 caracteres
3 Alto máximo de fila: 409 puntos
4 Número total de caracteres que puede contener una celda: 32.767 caracteres
5 Formatos o estilos de celdas distintos: 64.000
Mejor no llegar nunca a ese número, ya que los estilos de celda aumentan en demasía el tamaño de nuestros libros de trabajo.
6 Hipervínculos en una hoja de cálculo: 66.530 hipervínculos
Sí... existe un límite para los hipervínculos...
7 Formatos de número en un libro: entre 200 y 250 (dependerá del idioma de la versión de Excel instalada).
Os imagináis un libro con esa cantidad de formatos distintos para nuestros números??
8 Celdas cambiantes en un escenario: 32
Parece poco probable que tengamos ese número de variables en un escenario, verdad?
9 Celdas ajustables en Solver: 200
10 Escala de zoom: de 10 % a 400 %
11 Criterios de ordenación: 64 combinados en una única operación (sin límte en operaciones de ordenación secuenciales)
12 Niveles de deshacer: 100
13 Campos máximos en un formulario: 32
14 Elementos mostrados en listas de filtros desplegables: 10.000
Un clásico insalvable...
15 Número de celdas discontinuas que pueden seleccionarse: 2.147.483.648
16 Longitud del contenido de una fórmula: 8.192 caracteres


Otros límites que afectan al cálculo:
1 - Precisión numérica: 15 dígitos
2 - Número negativo más bajo permitido: -2.2251E-308
3 - Número positivo más bajo permitido: 2.2251E-308
4 - Número positivo más alto permitido: 9.99999999999999E+307
5 - Número negativo más alto permitido: -9.99999999999999E+307
6 - Número positivo más alto permitido mediante fórmula: 1.7976931348623158e+308
7 - Número negativo más alto permitido mediante fórmula: -1.7976931348623158e+308
8 - Rangos seleccionados: 2.048
9 - Argumentos en una función: 255
10 - Niveles anidados de funciones: 64
11 - Dependencia en una sola celda: 4.000 mil millones de fórmulas pueden depender de una sola celda
Muy curioso, sin duda...
12 - Longitud del contenido de celdas vinculadas de libros cerrados: 32.767
¿Te suena la cantidad?, sí, es el número total de caracteres que puede contener una celda.
13 - Número de elementos únicos por cada campo: 1.048.576
14 - Filtros de informe en un informe de tabla dinámica: 256 (puede estar en función de la memoria disponible)
15 - Campos de valores en un informe de tabla dinámica: 256


Un par de curiosidades de los Libros de Excel compartidos:
- Usuarios que pueden abrir y compartir simultáneamente un libro compartido: 256
- Días que se mantendrá el historial de cambios: 32.767 (OJO!!, el valor predeterminado es 30 días)
- Celdas que pueden resaltarse en un libro compartido: 32.767
- Colores utilizados para identificar los cambios realizados por diferentes usuarios si el resaltado de cambios está activado: 32 (cada usuario se identifica mediante un color; los cambios realizados por el usuario actual se resaltarán en color azul marino)
- Tablas de Excel en un libro compartido: 0 (cero!!!)
efectivamente la gran limitación de los libros compartidos.


Esta es una selección de algunas limitaciones o características de nuestros libros de Excel.

jueves, 9 de febrero de 2017

Suma Acumulada Cruzada por Fila y Columna

Un lector consultaba por la manera de Acumular cantidades de acuerdo a las repeticiones de elementos por filas y columnas en Excel:
[...]como a partir de la matriz en la cual los títulos de columnas y de filas pueden repetirse en varias columnas y filas, hacer una matriz resumida que responda a la suma de cada celda con el mismo título de fila y columna?..., no se si se entiende bien la pregunta
Un ejemplo corto sería:
COL
FILA c1 c2 c1 c3 c2 c1
f1 1 1 1 1 1 1
f2 1 1 1 1 1 1
f3 1 1 1 1 1 1
f3 1 1 1 1 1 1
f1 1 1 1 1 1 1
f2 1 1 1 1 1 1
f1 1 1 1 1 1 1

Para lograr el siguiente resumen:
COL
FILA c1 c2 c3
f1 9 6 3
f2 6 4 2
f3 6 4 2 
[...]

El asunto podría parecer 'peliagudo', pero nada más lejos de la realidad.
Emplearemos nuestras fórmulas matriciales para resolver el caso.
Partiremos del siguiente ejemplo:

Suma Acumulada Cruzada por Fila y Columna



La idea es completar de manera directa y acumular, sin repetición, por los elementos dispuestos por filas y columnas, esto es, completar el siguiente cuadro:

Suma Acumulada Cruzada por Fila y Columna


¿Cuál es la fórmula matricial buscada que nos resolverá la cuestión?.
Insertamos en K2:
=SUMA(SI($A$2:$A$10=$J2;SI($B$1:$G$1=K$1;$B$2:$G$10)))
y ejecutamos matricialmente!! (presionamos Ctrl+Mayusc+Enter)


Luego podemos copiar y pegar al resto de celdas de nuestro resumen, obteniendo los datos acumulados por fila y columna.
Podemos comprobar en el total calculado en una y otra tabla que la suma acumulada se ha completado correctamente.

Fijémonos en el doble condicional aplicado matricialmente dentro de nuestra suma... en él indicamos que cuando en los encabezados:
$A$2:$A$10 y $B$1:$G$1
coincidan con los elementos de nuestra segunda tabla resumen, se devuelva la matriz de datos $B$2:$G$10 !!!.
La fórmula entiende por sí misma que los datos que interesan son los que cruza unos con otros... tal como necesitábamos.