У Марси есть длинный список отсортированных чисел в столбце A рабочего листа.

Эти числа предположительно последовательные, но она не знает, правда ли это. Изучение списка вручную утомительно и чревато ошибками, поэтому Марсия задается вопросом, есть ли способ каким-то образом выделить любые «пропущенные числа» (те, которые не идут подряд с предыдущим), или составить список пропущенных чисел. в списке.

Есть несколько способов выяснить, где отсутствуют числа. Первый из них я использую довольно часто: я добавляю вспомогательный столбец рядом со столбцом A. Предполагая, что ваши числа начинаются с ячейки A1, я помещаю это в ячейку B2:

=IF(A2<>A1+1,"Error","")

Скопируйте формулу вниз на столько ячеек, сколько необходимо, и вы легко увидите слово «Ошибка» рядом с любым значением, которое не следует за значением чуть выше него. Если вы хотите узнать об ошибке немного больше, вы можете использовать более подробную формулу:

=IF(A2=A1,"Duplicate",IF(A2<>A1+1,"Gap",""))

Другой подход — использовать условное форматирование для ячеек в столбце A. Выполните следующие действия, снова предполагая, что ваши значения начинаются с ячейки A1:

  1. Выберите диапазон от A2 до последнего значения в столбце A.

  2. Открыв вкладку «Главная» ленты, щелкните параметр «Условное форматирование» в группе «Стили». Excel отображает палитру параметров, связанных с условным форматированием.

  3. Выберите «Выделить правила ячеек», а затем выберите «Дополнительные правила» в появившемся подменю. Excel отображает диалоговое окно «Новое правило форматирования».

(См. Рис. 1.)

  1. В области «Выбор типа правила» в верхней части диалогового окна выберите «Использовать формулу для определения ячеек для форматирования».

  2. В поле «Форматировать значения, где эта формула истинна» введите следующую формулу: = A2 <> A1 + 1. Щелкните Форматировать, чтобы открыть диалоговое окно Формат ячеек.

  3. Используя элементы управления в диалоговом окне, укажите формат, который вы хотите использовать для выделения непоследовательных ячеек.

  4. Нажмите кнопку ОК, чтобы закрыть диалоговое окно Формат ячеек. Форматирование, указанное на шаге 7, теперь должно появиться в области предварительного просмотра для правила.

  5. Щелкните ОК.

Наконец, если вы хотите составить список пропущенных чисел в последовательной серии, вы можете использовать формулу массива. Поместите следующее в строку 1 пустого столбца:

=IFERROR(SMALL(IF(COUNTIF($A$1:$A$135, MIN($A$1:$A$135)+ROW($1:$135)-1)=0,  MIN($A$1:$A$135)+ROW($1:$135)-1),ROW(A1)),"")

Помните, что это формула одного массива, поэтому вам нужно ввести ее как одну строку, используя Ctrl + Shift + Enter. Затем вы можете скопировать формулу на несколько ячеек, пока она не перестанет возвращать значения. Кроме того, формула предполагает, что ваша серия находится в диапазоне A1: A135; в противном случае вам необходимо изменить формулу, чтобы отразить фактический диапазон.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (4315) применим к Microsoft Excel 2007, 2010, 2013 и 2016.