Descubrir Errores en las Secuencias en Excel

Un lector consultaba por la manera de localizar errores o discontinuidades en una secuencia de números en Excel:

[...]Alguna formula para checar la secuencia nuúmerica ?? y que arroje un " correcto" o " incorrecto" según la condición  Ejm. ( 6061, 6062, 6063, 6064, 7021, 6065, 6066) que me marque el error en la secuencia que en este caso sería el "7021")[...]

Este sería el objetivo a conseguir:

El inicio del trabajo es fácil, asociamos al primer valor de la secuencia, en el ejemplo en la celda B2 el valor fijo: correcto.
Importante porque a partir de este dato se construirá la fórmula que necesitamos.

En B3 insertamos la matricial:
=SI(B2="correcto";SI(A3=A2+1;"correcto";"incorrecto");SI(A3=MAX(SI($B$2:B2="correcto";$A$2:A2))+1;"correcto";"incorrecto"))
(recuerda ejecutarla presionando Ctrl+Mayusc+Enter).
Una vez validada puedes copiar esta fórmula y pegarla al resto de celdas B4:B11.

Como se observa, tenemos una serie de condicionales anidados, que nos permiten descubrir saltos en la secuencia, e incorrecciones siguientes... tal como preguntaba el lector.

El primer condicional exige que el valor anterior sea 'correcto':
SI(B2="correcto"
si fuera así verificamos con el segundo SI si el valor a analizar es el siguiente en la secuencia:
SI(A3=A2+1;"correcto";"incorrecto")
Si fuera el caso, y el anterior dato no fuera 'correcto', esto es, no siguiera la secuencia, evaluamos una nueva condición para localizar el último dato 'correcto':
SI(A3=MAX(SI($B$2:B2="correcto";$A$2:A2))+1;"correcto";"incorrecto")
que deberá corresponderá al mayor (al Máximo) de los anteriores...si el dato actual es el siguiente a éste, será correcto.

Tenemos entonces la solución buscada.
para ello hemos empleado la siempre útil función SI, apoyada por un uso matricial.

Seguir leyendo...

Contando Únicos sobre Múltiples Criterios

Un lector consultaba por la manera de Realizar Conteos de valores únicos en base a varios criterios en Excel:

[...]Como logro con una formula recoger estos valores, que me indique el producto cuantas veces se repite según cantidad de ordenes, osea la manzana se repite en dos pedidos distintos[...]

El planteamiento del lector sería:

La fórmula matricial buscada y que soluciona el problema:
=SUMA(SI(D3=$B$2:$B$19;1/(CONTAR.SI.CONJUNTO($B$2:$B$19;D3;$A$2:$A$19;$A$2:$A$19))))
(recuerda ejecutarla matricialmente!!)

La fórmula toma una idea tradicional para contar valores distintos, que es obtener para cada registro coincidente el inverso del número total de veces que aparece esa 'múltiple' coincidencia, para a continuación sumarla y obtener un número entero que es precisamente el valor buscado.

Lo interesante de este ejercicio es el uso matricial que se hace del CONTAR.SI.CONJUNTO, y como al indicar el segundo criterio:
CONTAR.SI.CONJUNTO($B$2:$B$19;$F$3;$A$2:$A$19;$A$2:$A$19)
se emplea un rango!; así, para el ejemplo, se tendrán en cuenta todas las posibilidades que aparecen en la columna A (en el campo 'Orden').
Vemos el resultado que obtendríamos para cada registro en caso de buscar coincidencias de 'Manzana':

En el rango C2:C19 he añadido la función (extraída de la matricial anterior):
=1/(CONTAR.SI.CONJUNTO($B$2:$B$19;$F$3;$A$2:$A$19;$A$2:$A$19))
que devuelve los cocientes inversor, a partir de los cuales sumamos y obtenemos el número de coincidencias distintas.

Comprobamos el correcto funcionamiento si modificamos nuestro listado:

Observamos los tres tipos de registros asociados al elemento 'Manzana':
123 Manzana
126 Manzana
127 Manzana
Dando por buena la fórmula... y lo más interesante es que abre la puerta hacía el trabajo sobre varios campos, tantos como admita la función CONTAR.SI.CONJUNTO (hasta 127 criterios).

Seguir leyendo...

Conjunto de Iconos en Celdas con Texto

Un lector consultaba por la manera de Añadir un Conjunto de Iconos en celdas que contuvieran texto en Excel:

[...]quiero colocar semáforos [formato condicional] pero las celdas son textos.  Ejemplos. si la celda dice "pendiente" que coloque un semáforo rojo Si la celda dice "Cumplido" que coloque un semáforo verde Es posible?[...]

Inicialmente tal cosa no es posible, ya que este tipo de formato condicional no permite añadir fórmulas ni operar por tanto sobre texto.

Pero siempre hay una salida. Conseguiremos ver lo que deseamos con un sencillo truco.

Lo primero será evitar los textos en nuestras celdas, así pues codificaremos los conceptos sobre los que necesitamos trabajar:
1 - cumplido
0- pendiente
Al trabajar sobre números (y no textos) aplicar el 'semáforo' deseado será factible...

Pero por otra parte también queremos ver en nuestras celdas las palabras 'cumplido' y 'pendiente', así pues a nuestro rango de trabajo aplicaremos el siguiente formato personalizado de número:
[=1]"cumplido";[=0]"pendiente"

haz clic en la imagen

El resultado es que nuestros valores numéricos 0 y 1 se visualizan como texto ('pendiente' y 'cumplido'):

Finalmente aplicamos sobre nuestro rango el Formato Condicional > Conjunto de iconos > 3 semáforos (con marco)

Accedemos ahora al administrador de reglas de formato condicional e indicamos los valores de referencia a tipo número, con valores 1 y 0 según la codificación requerida (semáforo rojo para los pendientes y verde para los cumplidos):

Es importante dónde hemos colocado las desigualdades en la configuración para mandar al cero a un semáforo rojo y el uno al semáforo verde...
Consiguiendo el aspecto que deseábamos, esto es, ver las palablas cumplido o pendiente con un semáforo incluido.

Seguir leyendo...

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)))

haz clic en la imagen

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:

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

Seguir leyendo...

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.

Seguir leyendo...