Identificar espacios en una secuencia en una celda - Excel

Question

Tengo una lista de elementos en mi hoja de Excel que están numerados, junto con un número total, de la siguiente manera:

Ejemplo:

   | Column A (available) | Column B (missing) | Column C (total)
___|______________________|____________________|_________________
1  | 01 - 13              |         ?          | 20
2  | 02 - 09              |         ?          | 10
3  | 01 - 04, 06 - 11     |         ?          | 11

Ahora quiero que la columna B muestre los números de artículos que faltan, así:

   | Column A (available) | Column B (missing) | Column C (total)
___|______________________|____________________|_________________
1  | 01 - 13              | 14 - 20            | 20
2  | 02 - 09              | 01, 10             | 10
3  | 01 - 04, 06 - 11     | 05                 | 11

Me las arreglé para crear una fórmula larga para casos como en la línea 1, pero eso solo funciona verificando los dos últimos dígitos de una celda en la Columna A, restándolos del valor de la Columna C en esa línea, luego completando la Columna B con el resultado.

(básicamente algo como esto (con algunos casos especiales no afectados por el "problema de brecha" en cuestión)) $C1-Right($A1;2)&" - "&$C1:)

Pero mi enfoque (obviamente) no puede manejar las brechas como en las líneas 2 y 3, y quiero saber si hay una manera de resolver mi problema.

PD: No estaba seguro de qué título darle a este problema, así que pido disculpas si es engañoso.

Answer 1

Su problema se puede resolver utilizando la función TENDENCIA:

1. Tomando la primera muestra de datos en la columna A, debe crear1ª secuencia del 1 al 13y2do del 1 al 20.
2. Escriba esta fórmula de matriz en la celda C1, termine con Ctrl+Shift+Entery complete.

{=TREND(A1:A13,B1:B13,B14:B20)}

1. Obtienes series de 14 to 20.

2. Ingrese D1esta fórmula para obtener el 1st Cell value. =INDEX(C1:C7,MATCH(TRUE,INDEX((C1:C7<>0),0),0))

3. Ingrese esta fórmula D2para obtener last Cell value.

=LOOKUP(2,1/(C1:C7<>""),C1:C7)

4. En Cell D4ingresa este =D1&"-"&D2y obtendrás 14-20.

Nota: Para la segunda muestra de datos, necesita crear 2 series.

1- 2 to 9.

2- 1 to 10.

La tercera muestra de datos necesita 3 series.

1- 1 to 4.

2- 6 to 11.

3- 1 to 11.

1. Repita los pasos que se muestran arriba para otras muestras de datos.
2. Ajuste las referencias de celda en la Fórmula según sea necesario.