Выявить пробелы в последовательности в одной ячейке - Excel

Question

У меня есть список элементов в моем листе Excel, которые нумеруются вместе с общим числом следующим образом:

Пример:

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

Теперь я хочу, чтобы в столбце B отображались номера отсутствующих элементов, например:

   | 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

Мне удалось создать длинную формулу для случаев, как в строке 1, но это работает только путем проверки последних двух цифр ячейки в столбце A, вычитания ее из значения столбца C в этой строке, а затем заполнения столбца B с помощью результат.

(в основном что-то вроде этого (с некоторыми особыми случаями, на которые не влияет "проблема разрыва"): $C1-Right($A1;2)&" - "&$C1)

Но мой подход (очевидно) не может справиться с пробелами, как в строках 2 и 3, и я хочу знать, есть ли способ решить мою проблему.

PS Я не был уверен, какое название дать этой проблеме, поэтому я прошу прощения, если это вводит в заблуждение.

Answer 1

Ваша проблема может быть решена с помощью функции TREND:

1. Взяв первую выборку данных в столбце A, вам нужно создать 1-ю последовательность от 1 до 13 и 2-ю от 1 до 20.
2. Запишите эту формулу массива в ячейку C1 , завершите с помощью Ctrl+Shift+Enter и заполните.

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

1. Вы получаете серию от 14 to 20 .

2. В D1 введите эту формулу, чтобы получить значение 1st Cell value . =INDEX(C1:C7,MATCH(TRUE,INDEX((C1:C7<>0),0),0))

3. Введите эту формулу в D2 чтобы получить last Cell value .

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

4. В ячейку D4 введите это =D1&"-"&D2 , вы получите 14-20 .

Примечание: для 2-го образца данных вам нужно создать 2 серии.

1- 2 to 9

2-1 1 to 10

Третий образец данных требует 3 серии.

От 1 to 4 .

6 to 11

3-1 1 to 11

1. Повторите вышеуказанные шаги для других образцов данных.
2. При необходимости измените ссылки на ячейки в формуле.