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

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:

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:

¿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.

Seguir leyendo...

Cálculo de un Promedio Excluyendo Altos y Bajos

Ha sido decidir un título para este post, aunque la idea es muy clara.
Cómo calcular el promedio sobre un rango de celdas excluyendo los dos valores máximos y mínimos, pero en caso de que alguno de estos valores más bajos o más altos esté repetido, solo tener en cuenta uno de ellos.!!.
Planteemos un ejemplo. Disponemos de este listado de valores:
0 - 2 - 2 - 2 - 5 - 6 - 8 - 12 - 12 - 15
y deberíamos calcular la media sobre los valores:
2 - 5 - 6 - 8 - 12
esto es, media aritmética = 6,60

El asunto podría parecer vanal, pero después de dar varias vueltas al asunto no conseguí una única fórmula con la que obtener el resultado directo... y es que trabajar con los valores repetidos y quedarse solo con un único valor correspondiente al máximo o mínimo es complejo
Mi solución (a falta de otra mejor) es trabajar el promedio como el cociente de una suma entre en un conteo.

Para esto, mi primer cálculo corresponde a la suma de los valores estríctamente mayores y menores al segundo valor más alto y bajo del rango.
Estos valores se calculan (ver celdas D4 y D5) con las funciones:
segundo valor más alto: =K.ESIMO.MAYOR(A1:A10;2)
segundo valor más bajo: =K.ESIMO.MENOR(A1:A10;2)

Así pues en B13:
=SUMAR.SI.CONJUNTO(A1:A10;A1:A10;">"&K.ESIMO.MENOR(A1:A10;2);A1:A10;"<"&K.ESIMO.MAYOR(A1:A10;2))
notemos que aplicamos desigualdes estrictas, dejando fuera del cálculo los segundos valores.
De forma parecida realizamos el conteo de valores que cumplen las condiciones. En B14:
=CONTAR.SI.CONJUNTO(A1:A10;">"&K.ESIMO.MENOR(A1:A10;2);A1:A10;"<"&K.ESIMO.MAYOR(A1:A10;2))

Ahora necesitamos evaluar si procede o no incluir una única vez (en caso de repetición) el segundo valor más alto y más bajo.
Estudiamos el caso para el segundo valor por arriba. En la celda C13:
=SI(CONTAR.SI(A1:A10;K.ESIMO.MAYOR(A1:A10;2))=1;0;K.ESIMO.MAYOR(A1:A10;2))
que nos indica que si hay repetición para este segundo valor más alto nos devuelve dicho valor, en caso contrario (es decir, que solo aparezca una sola vez) devuelva un cero...
En nuestro ejemplo al estar repetido dos veces el valor 12, nos devuelve un 12.

Repetimos la idea para el valor por debajo, para incorporar el conteo. En la celda C14:
=SI(CONTAR.SI(A1:A10;K.ESIMO.MAYOR(A1:A10;2))=1;0;1)
obtendremos un 0 si solo aparece en nuestro listado una vez y un 1 si más.

Replicamos los cálculos, con sus ajustes, para los valores más bajos.
En D13:
=SI(CONTAR.SI(A1:A10;K.ESIMO.MENOR(A1:A10;2))=1;0;K.ESIMO.MENOR(A1:A10;2))

y en D14 para el conteo:
=SI(CONTAR.SI(A1:A10;K.ESIMO.MENOR(A1:A10;2))=1;0;1)

Sumamos por separado cantidades del rango B13:D13 (para el numerador de nuestro cociente) y por otra parte sumamos el rango B14:D14 (para el denominador del cociente).

Finalmente dividimos una y otra cantidad consiguiendo nuestro promedio buscado.

Podríamos juntar todo en una sola celda, pero sería poco visual para el aprendizaje...

Una comprobación de lo correcto del proceso sería para el listado de valores siguiente:
0 - 2 - 4 - 5 - 5 - 6 - 8 - 12 - 14 - 15
y deberíamos calcular la media sobre los valores:
4 - 5 - 5 - 6 - 8 - 12
esto es, media aritmética = 6,66666

Nos fijamos que se excluyen del cálculo el 0 y 2 por debajo, y el 14 y 15 por arriba; y que al no estar repetidos dichos valores no se produce inclusión alguna de éstos en el proceso.

Seguir leyendo...

Referencias Absolutas en una Tabla de Excel

En esta ocasión hablaré del uso de las referencias absolutas dentro del contexto de una Tabla.

Seguramente te habrás percatado al trabajar con tablas que al arrastrar una campo calculado de una tabla todas las fórmulas se desplazan a los campos adyacentes.
Veamos un ejemplo sobre la siguiente tabla (que he llamado 'TblDatos'):

Vemos el campo calculado llamado 'Ratio1' con la fórmula:
=[@Semestre]/[@Ene]
típica estructura de fórmula en contexto de tabla...

Si arrastramos esa celda (o campo) hacia la derecha comprobaremos el efecto por el cual se ha desplazado la fórmula como si se tratara de una referencia relativa, esto es:

haz clic en la imagen

Nada especial, es más, era de esperar... otra cuestión es que esperásemos que su comportamiento fuera otro...

La cuestión del post. Cómo consigo fijar una campo para mi cálculo de ratios, es decir, cómo fijo para mi fórmula el campo 'Semestre' ???.

La clave es introducir la fórmula de la siguiente manera:
=TblDatos[[Semestre]:[Semestre]]/[@Ene]

es decir, para fijar un campo, empezamos por el nombre de la Tabla de trabajo (en mi ejemplo la Tabla se llamaba 'TblDatos'), seguido del nombre completo del campo repetido y separado por dos puntos (:)
TblDatos[[Semestre]:[Semestre]]
esto equivale a una referencia NO relativa, con la que conseguimos que al arrastrar dicha fórmula, siempre se mantenga la referencia sobre dicho campo...

Comprobamos que para campo destino (ratio2, ratio3, etc.) la fórmula conseguida es la esperada:
Ratio2:=TblDatos[[Semestre]:[Semestre]]/[@Feb]
Ratio3:=TblDatos[[Semestre]:[Semestre]]/[@Mar]
Ratio4:=TblDatos[[Semestre]:[Semestre]]/[@Abr]
Ratio5:=TblDatos[[Semestre]:[Semestre]]/[@May]
Ratio6:=TblDatos[[Semestre]:[Semestre]]/[@Jun]

Seguir leyendo...