Определить пробелы в последовательности в одной ячейке - 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, и я хочу узнать, есть ли способ решить мою проблему.

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

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 ряда.

1- 2 to 9.

2- 1 to 10.

Для 3-й выборки данных необходимо 3 серии.

1- 1 to 4.

2- 6 to 11.

3- 1 to 11.

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