У Билла есть столбец чисел, отсортированных в порядке возрастания. В столбце много повторяющихся значений, и это нормально. Однако ему нужно найти первый экземпляр числа в столбце, у которого нет дубликата. Билл задается вопросом, существует ли формула, которая могла бы определить первое однократное значение в столбце.
Существует довольно много способов найти желаемый ответ. Один из способов – добавить вспомогательный столбец справа от ваших чисел. Предполагая, что ваше первое число находится в ячейке 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 (ЕСЛИ (СЧЁТЕСЛИ (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, вам нужно соответствующим образом скорректировать формулу.
Если вы не хотите использовать формулу, вы можете выделить единичные значения в данных с помощью условного форматирования. Выполните следующие действия:
- Выберите ячейки, которые хотите проверить.
- На вкладке «Главная» ленты выберите параметр «Условное форматирование» в группа “Стили”. Excel отображает палитру параметров, связанных с условным форматированием.
- Выберите правила выделения ячеек. В Excel отображается еще больше параметров.
- Выбрать повторяющиеся значения. Excel отображает диалоговое окно “Повторяющиеся значения”. (См. Рис. 1.)
- В раскрывающемся списке слева от диалогового окна выберите «Уникальный».
- Используйте раскрывающийся список в левой части диалогового окна, чтобы указать, как вы хотите отформатировать однократные значения.
- Щелкните OK.
Рис. 1. Диалоговое окно «Дублирование ячеек».
На этом этапе ваши однократные значения отформатированы, как вы указали на шаге 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 = "Найдено первое однократное значение" sMsg = sMsg & "at" & c.Address & vbCrLf sMsg = sMsg & "Value:" & c MsgBox sMsg Exit For End If Next c Else sMsg = «Вы должны выбрать не менее 2 ячеек». MsgBox sMsg End IfEnd Sub
Чтобы использовать макрос, выберите ячейки, которые вы хотите проверить, и затем запустите его. Макрос отображает адрес и значение первого однократного значения в вашем выборе.
Обратите внимание, что все решения, представленные в этом совете (за исключением подхода с условным форматированием), требуют что оцениваемые значения отсортированы, как и сказал Билл. Если ваши значения не отсортированы, вам нужно либо сначала отсортировать их, либо искать совершенно другой подход для получения результатов.