У Билла есть столбец чисел, отсортированных по возрастанию. В столбце много повторяющихся значений, и это нормально. Однако ему нужно найти первый экземпляр числа в столбце, у которого нет дубликата. Билл задается вопросом, есть ли формула, по которой можно определить первое однократное значение в столбце.

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

=IF(AND(A1<>A2,A3<>A2),"single","")

Скопируйте формулу вниз на столько ячеек, сколько необходимо, и вы сможете легко найти первую ячейку, которая имеет одно значение в столбце A.

Вы также можете использовать следующую формулу в ячейке B2:

=COUNTIF($A:$A,$A2)

Скопируйте его столько, сколько необходимо; формула показывает количество раз, когда значение в столбце A встречается в столбце A. Затем вы можете использовать следующую формулу для определения первого значения, которое встречается один раз в столбце A:

=INDEX($A:$A,MATCH(1,$B:$B,0))

Если вспомогательный столбец невозможен, вы можете полагаться на формулы массива.

Любой из них покажет первое значение, которое встречается один раз:

=INDEX(A2:A999,MATCH(1,COUNTIF(A2:A999,A2:A999),0))

=SMALL(IF(COUNTIF(A2:A999,A2:A999)=1,A2:A999,""),1)

Помните, что это формулы массива, а это значит, что вам нужно вводить их, используя Ctrl + Shift + Enter. Кроме того, если в диапазоне нет единственного значения, формула возвращает ошибку # N / A.

Если вы хотите узнать, какая строка содержит первое однократное значение, вам подойдет следующая формула массива:

=MATCH(1,COUNTIF(A2:A999,A2:A999),0)+1

Обратите внимание, что формула проверяет ячейки A2: A999. Так как строка A1 пропущена, в конце формулы требуется «+1». Если у вас нет строки заголовка или если ваши данные начинаются в строке, отличной от строки 2, вам нужно соответствующим образом скорректировать формулу.

Если вы не хотите использовать формулу, вы можете выделить однократные значения в данных с помощью условного форматирования.

Выполните следующие действия:

  1. Выделите ячейки, которые хотите проверить.

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

  3. Выберите правила выделения ячеек. Excel отображает еще больше возможностей.

  4. Выберите повторяющиеся значения. Excel отображает диалоговое окно «Повторяющиеся значения». (См. Рис. 1.)

  5. В раскрывающемся списке слева от диалогового окна выберите Уникальный.

  6. Используйте раскрывающийся список в левой части диалогового окна, чтобы указать, как вы хотите отформатировать однократные значения.

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

На этом этапе ваши однократные значения отформатированы, как вы указали на шаге 6, и вы можете легко их обнаружить. Если вы хотите видеть только одноразовые значения, после применения условного формата вы можете использовать фильтрацию для выполнения задачи.

Если вы предпочитаете подход с использованием макросов, вы можете использовать следующий макрос:

Sub FirstUnique()

Dim c As Range     Dim sMsg As String     Dim bLone As Boolean

If Selection.Cells.Count > 1 Then         For Each c In Selection.Cells             bLone = False             If c.Row = 1 Then                 If c <> c.Offset(1, 0) Then bLone = True             Else                 If c <> c.Offset(-1, 0) And _                    c <> c.Offset(1, 0) Then bLone = True             End If             If bLone Then                 sMsg = "First single-occurrence value found "

sMsg = sMsg & "at " & c.Address & vbCrLf                 sMsg = sMsg & "Value: " & c                 MsgBox sMsg                 Exit For             End If         Next c     Else         sMsg = "You must select at least 2 cells."

MsgBox sMsg     End If End Sub

Чтобы использовать макрос, выберите ячейки, которые хотите проверить, и запустите его. Макрос отображает адрес и значение первого однократного значения в вашем выборе.

Следует отметить, что все решения, представленные в этом совете (за исключением подхода условного форматирования), требуют, чтобы оцениваемые значения были отсортированы, как сказал Билл. Если ваши значения не отсортированы, вам нужно либо сначала отсортировать их, либо искать совершенно другой подход для получения результатов.

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

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