Знаете ли вы способ в Excel "вычислить" по формуле список уникальных значений?
Например: вертикальный диапазон содержит значения "red", "blue", "red", "green", "blue", "black"
и я хочу получить в результате "red, "blue", "green", "black" + в конце концов еще 2 пустые клетки.
Я уже нашел способ получить вычисленный отсортированный список, используя SMALL или LARGE в сочетании с INDEX, но я хотел бы также иметь этот вычисленный сортировку, БЕЗ ИСПОЛЬЗОВАНИЯ VBA.
Это старое, и есть несколько решений, но я придумал более короткую и более простую формулу, чем любой другой, с которым я столкнулся, и это может быть полезно любому, кто проходит мимо.
Я назвал список цветов Colors (A2: A7) и формула массива, помещенная в ячейку C2, это (fixed):
=IFERROR(INDEX(Colors,MATCH(SUM(COUNTIF(C$1:C1,Colors)),COUNTIF(Colors,"<"&Colors),0)),"")
Используйте Ctrl+Shift+Enter для ввода формулы в C2 и скопируйте C2 до C3: C7.
Объяснение с образцами данных { "red"; "Синий"; "Красный"; "Зеленый"; "Синий"; "Черный" }:
COUNTIF(Colors,"<"&Colors) возвращает массив (# 1) с количеством значений, меньшим, чем каждый элемент данных {4; 1; 4; 3; 1; 0} (черный = 0 единиц меньше, синий = 1 item, red = 4 элемента). Это можно перевести на значение сортировки для каждого элемента.COUNTIF(C$1:C...,Colors) возвращает массив (# 2) с 1 для каждого элемента данных, который уже находится в отсортированном результате. В C2 он возвращает {0; 0; 0; 0; 0; 0} и в C3 {0; 0; 0; 0; 0; 1}, потому что "черный" является первым в сортировке и последним в данных. В C4 {0; 1; 0; 0; 1; 1} он указывает "черный", и все вхождения "синего" уже присутствуют.SUM возвращает значение сортировки k-th, путем подсчета всех меньших значений, которые уже присутствуют (сумма массива # 2).MATCH находит первый индекс k-го значения сортировки (индекс в массиве # 1).IFERROR заключается только в том, чтобы скрыть ошибку #N/A в нижних ячейках, когда отсортированный уникальный список завершен.Чтобы узнать, сколько уникальных элементов у вас есть, вы можете использовать эту правильную формулу:
=SUM(IF(FREQUENCY(COUNTIF(Colors,"<"&Colors),COUNTIF(Colors,"<"&Colors)),1))
Хорошо, у меня есть две идеи для вас. Надеюсь, один из них доставит вас туда, куда вам нужно идти. Обратите внимание, что первый игнорирует запрос, чтобы сделать это как формулу, поскольку это решение не очень красиво. Я решил, что я уверен, что простой способ действительно не сработает для вас; ^).
Это решение будет работать со следующими оговорками:
Список должен быть отсортирован (по возрастанию или по убыванию не имеет значения). На самом деле это довольно точно, поскольку требование действительно состоит в том, что все подобные предметы должны быть смежными, но сортировка - это самый простой способ достичь этого состояния. Требуются три новых столбца (два новых столбца для вычислений и один новый столбец для нового списка). Второй и третий столбцы могут быть объединены, но я оставлю это как упражнение для читателя.Вот краткое изложение решения:
И вот шаг за шагом:
Надеюсь, это поможет...
Я создал для вас функцию в VBA, поэтому вы можете сделать это сейчас простым способом.
Создайте модуль кода VBA (макрос), как вы можете видеть в этот учебник.
Module в Insert.Excel Macro-Enabled в Save As.Function listUnique(rng As Range) As Variant
Dim row As Range
Dim elements() As String
Dim elementSize As Integer
Dim newElement As Boolean
Dim i As Integer
Dim distance As Integer
Dim result As String
elementSize = 0
newElement = True
For Each row In rng.Rows
If row.Value <> "" Then
newElement = True
For i = 1 To elementSize Step 1
If elements(i - 1) = row.Value Then
newElement = False
End If
Next i
If newElement Then
elementSize = elementSize + 1
ReDim Preserve elements(elementSize - 1)
elements(elementSize - 1) = row.Value
End If
End If
Next
distance = Range(Application.Caller.Address).row - rng.row
If distance < elementSize Then
result = elements(distance)
listUnique = result
Else
listUnique = ""
End If
End Function
Просто введите =listUnique(range) в ячейку. Единственным параметром является range, который является обычным диапазоном Excel. Например: A$1:A$28 или H$8:H$30.
range должен быть столбцом.range.Он работает в столбцах, в которых есть пустые ячейки. Также функция не выдает ничего (а не ошибки), если вы перетаскиваете ячейки (вызывающие функцию) в места, где не должно быть вывода, как я сделал это в предыдущем примере. 2. Растите его.
Обходной способ - загрузить электронную таблицу Excel в электронную таблицу Google, использовать функцию Google UNIQUE (диапазон), которая делает именно то, что вы хотите, а затем сохранить таблицу Google в формате Excel.
Я признаю, что это не жизнеспособное решение для пользователей Excel, но этот подход полезен для всех, кто хочет использовать эту функциональность и может использовать электронную таблицу Google.
заметил его очень старый вопрос, но люди, похоже, все еще испытывают трудности с использованием формулы для извлечения уникальных предметов. здесь решение, которое возвращает значения для себя.
Допустим, у вас в столбце A2: A7
есть "красный", "синий", "красный", "зеленый", "синий", "черный",затем поместите это в B2 как формулу массива и скопируйте =IFERROR(INDEX(A$2:A$7;SMALL(IF(FREQUENCY(MATCH(A$2:A$7;A$2:A$7;0);ROW(INDIRECT("1:"&COUNTA(A$2:A$7))));ROW(INDIRECT("1:"&COUNTA(A$2:A$7)));"");ROW(A1)));"")
тогда он должен выглядеть примерно так;
Даже для получения отсортированного уникального значения это можно сделать с помощью формулы. Это вариант, который вы можете использовать:
=INDEX($A$2:$A$18,MATCH(SUM(COUNTIF($A$2:$A$18,C$1:C1)),COUNTIF($A$2:$A$18,"<" &$A$2:$A$18),0))
: A2:A18
в ячейке C2
Это ФОРМУЛА МАШИНЫ
Попробуйте эту формулу в ячейке B2
=IFERROR(INDEX($A$2:$A$7,MATCH(0,COUNTIF(B$1:$B1,$A$2:$A$7),0),1),"")
После нажатия F2 и нажмите Ctrl + Shift + Enter
Вы можете использовать COUNTIF, чтобы получить количество вхождения значения в диапазоне. Поэтому, если значение находится в A3, диапазон равен A1: A6, затем в следующем столбце используйте IF (EXACT (COUNTIF (A3: $A $6, A3), 1), A3, "). Для A4 это будет IF (EXACT (COUNTIF (A4: $A $6, A3), 1), A4," ")
Это даст вам столбец, где все уникальные значения не будут содержать никаких дубликатов
Предполагая, что столбец A содержит значения, которые вы хотите найти один уникальный экземпляр, и имеет строку заголовка, я использовал следующую формулу. Если вы хотите, чтобы он масштабировался с непредсказуемым количеством строк, вы могли бы заменить A772 (где мои данные закончились) с помощью = ADDRESS (COUNTA (A: A), 1).
= ЕСЛИ (СЧЕТЕСЛИ (A5: $A $772, A5) = 1, A5, "")
Это отображает уникальное значение в экземпляре LAST каждого значения в столбце и не предполагает никакой сортировки. Он использует недостаток абсолютов, чтобы по существу иметь уменьшающееся "скользящее окно" данных для подсчета. Когда счет в уменьшенном окне равен 1, эта строка является последним экземпляром этого значения в столбце.
Решение Drew Sherman очень хорошее, но список должен быть смежным (он предлагает ручную сортировку, и это неприемлемо для меня). Решение Guitartrower немного медленнее, если количество элементов велико и не соответствует порядку исходного списка: он выводит отсортированный список независимо.
Мне нужен первоначальный порядок элементов (которые были отсортированы по дате в другом столбце), и, кроме того, я хотел исключить элемент из окончательного списка не только, если он был дублирован, но и по целому ряду других причин,
Мое решение - это усовершенствование решения Drew Sherman. Аналогично, это решение использует 2 столбца для промежуточных вычислений:
Столбец A:
Список с дубликатами и, возможно, пробелами, которые вы хотите фильтровать. В качестве примера я поставлю его в интервале A11: A1100, потому что мне не удалось переместить решение Дрю Шермана в ситуации, когда он не начинался в первой строке.
Столбец B:
Эта формула будет выводить 0, если значение в этой строке допустимо (содержит не дублируемое значение). Обратите внимание, что вы можете добавить любые другие условия исключения, которые вы хотите в первом IF, или как еще один внешний IF.
=IF(ISBLANK(A11);1;IF(COUNTIF($A$11:A11;A11)=1;0;COUNTIF($A11:A$1100;A11)))
Используйте интеллектуальную копию для заполнения столбца.
Столбец C:
В первой строке мы найдем первую допустимую строку:
=MATCH(0;B11:B1100;0)
С этой позиции мы ищем следующее допустимое значение со следующей формулой:
=C11+MATCH(0;OFFSET($B$11:$B$1100;C11;0);0)
Поместите его во вторую строку и используйте умную копию, чтобы заполнить остальную часть столбца. Эта формула выведет ошибку # N/D, если нет более уникальной точки. Мы воспользуемся этим в следующей колонке.
Столбец D:
Теперь нам просто нужно получить значения, указанные столбцом C:
=IFERROR(INDEX($A$11:$A$1100; C11); "")
Используйте интеллектуальную копию для заполнения столбца. Это уникальный уникальный вывод.
Я вставлял то, что я использую в моем файле excel ниже. Это подбирает уникальные значения из диапазона L11:L300 и заполняет их из столбцов V, V11 и далее. В этом случае у меня есть эта формула в v11 и перетащите ее вниз, чтобы получить все уникальные значения.
=INDEX(L$11:L$300,MATCH(0,COUNTIF(V$10:V10,L$11:L$300),0))
или
=INDEX(L$11:L$300,MATCH(,COUNTIF(V$10:V10,L$11:L$300),))
это формула массива
Вы также можете сделать это таким образом.
Создайте следующие диапазоны имен:
nList = the list of original values
nRow = ROW(nList)-ROW(OFFSET(nList,0,0,1,1))+1
nUnique = IF(COUNTIF(OFFSET(nList,nRow,0),nList)=0,COUNTIF(nList, "<"&nList),"")
С этими тремя именованными диапазонами вы можете создать упорядоченный список уникальных значений с помощью приведенной ниже формулы. Он будет отсортирован в порядке возрастания.
IFERROR(INDEX(nList,MATCH(SMALL(nUnique,ROW()-?),nUnique,0)),"")
Вам нужно будет подставить номер строки ячейки чуть выше первого элемента вашего уникального упорядоченного списка для '?' характер.
например. Если ваш уникальный упорядоченный список начинается в ячейке B5, тогда формула будет:
IFERROR(INDEX(nList,MATCH(SMALL(nUnique,ROW()-4),nUnique,0)),"")
Я удивлен, что это решение еще не пришло. Я думаю, что это один из самых легких
Дайте вашим данным заголовок и поместите его в динамический именованный диапазон (т.е. если ваши данные находятся в col A)
=OFFSET($A$2,0,0,COUNTA($A:$A),1)
И затем создайте сводную таблицу, создав исходный именованный диапазон.
Просто поместите заголовок в раздел строк, и вы получите уникальные значения, отсортируйте их любым способом с помощью встроенной функции.
Приведение к сводной таблице может не считаться только с использованием формул, но представляется более практичным, чем большинство других предложений:
Недавно я столкнулся с той же проблемой и, наконец, понял это.
Используя ваш список, вот паста из моего Excel с формулой.
Я рекомендую написать формулу где-то посередине списка, например, в ячейке C6 моего примера, а затем скопировать его и вставить его вверх и вниз по столбцу, формула должна автоматически регулироваться без необходимости перепечатать его.
Единственная ячейка, имеющая уникально отличающуюся формулу, находится в первой строке.
Использование вашего списка ( "красный", "синий", "красный", "зеленый", "синий", "черный" ); вот результат: (У меня нет достаточно высокого уровня для публикации изображения, так что надеюсь, что эта версия txt имеет смысл)
[Столбец C: уникальная формула списка]
=A3=IF(ISERROR(MATCH(A4,A$3:A3,0)),A4,"")=IF(ISERROR(MATCH(A5,A$3:A4,0)),A5,"")=IF(ISERROR(MATCH(A6,A$3:A5,0)),A6,"")=IF(ISERROR(MATCH(A7,A$3:A6,0)),A7,"")=IF(ISERROR(MATCH(A8,A$3:A7,0)),A8,"")Это работает только в том случае, если значения находятся в порядке, т.е. все "красные" вместе, и все "синие" вместе и т.д. предположим, что ваши данные находятся в столбце A, начиная с A2 - (не начинайте с строки 1) В B2 типа в 1 В b3 type = if (A2 = A3, B2, B2 + 1) Перетащите формулу до конца ваших данных. Все "красные" будут 1, все "синие" будут 2, все "зеленые" будут 3 и т.д.
В С2-типе в 1, 2, 3 и т.д. В D2 = OFFSET ($ A $1, MATCH (c2, $B $2: $B $x, 0), 0) - где x - последняя ячейка Перетащите вниз, появятся только уникальные значения. - проверить некоторые ошибки
Для решения, которое работает для значений в нескольких строках и столбцах, я нашел следующую формулу очень полезной: http://www.get-digital-help.com/2009/03/16/unique-values-from-multiple-columns-using-array-formulas/ Оскар на get-digital.help.com даже проходит через него шаг за шагом и с визуализированным примером.
1) Дайте диапазон значений метке tbl_text
2) Примените следующую формулу массива с CTRL + SHIFT + ENTER, в ячейку B13 в этом случае. Измените $B $12: B12, чтобы обратиться к ячейке над ячейкой, в которую вы вводите эту формулу.
=INDEX(tbl_text, MIN(IF(COUNTIF($B$12:B12, tbl_text)=0, ROW(tbl_text)-MIN(ROW(tbl_text))+1)), MATCH(0, COUNTIF($B$12:B12, INDEX(tbl_text, MIN(IF(COUNTIF($B$12:B12, tbl_text)=0, ROW(tbl_text)-MIN(ROW(tbl_text))+1)), , 1)), 0), 1)
3) Скопируйте/перетащите, пока не получите N/A.
Если вы помещаете все данные в одни и те же столбцы и используете следующую формулу
Пример Формула: =IF(C105=C104,"Duplicate","Not a Duplicate")
Шаги
Not a DuplicateПример формулы: =IF(C105=C104,"Duplicate","Not a Duplicate")
Я использовал код totymedli, но обнаружил, что он увядает при использовании больших диапазонов (как указывалось другими), поэтому я немного оптимизировал его код. Если кто-то заинтересован в получении уникальных значений с помощью VBScript, но при обновлении код totymedli медленно работает, попробуйте следующее:
Function listUnique(rng As Range) As Variant
Dim val As String
Dim elements() As String
Dim elementSize As Integer
Dim newElement As Boolean
Dim i As Integer
Dim distance As Integer
Dim allocationChunk As Integer
Dim uniqueSize As Integer
Dim r As Long
Dim lLastRow As Long
lLastRow = rng.End(xlDown).row
elementSize = 1
unqueSize = 0
distance = Range(Application.Caller.Address).row - rng.row
If distance <> 0 Then
If Cells(Range(Application.Caller.Address).row - 1, Range(Application.Caller.Address).Column).Value = "" Then
listUnique = ""
Exit Function
End If
End If
For r = 1 To lLastRow
val = rng.Cells(r)
If val <> "" Then
newElement = True
For i = 1 To elementSize - 1 Step 1
If elements(i - 1) = val Then
newElement = False
Exit For
End If
Next i
If newElement Then
uniqueSize = uniqueSize + 1
If uniqueSize >= elementSize Then
elementSize = elementSize * 2
ReDim Preserve elements(elementSize - 1)
End If
elements(uniqueSize - 1) = val
End If
End If
Next
If distance < uniqueSize Then
listUnique = elements(distance)
Else
listUnique = ""
End If
End Function
Выберите столбец с повторяющимися значениями, затем перейдите на вкладку "Данные", затем "Инструменты данных" выберите "Удалить дубликат" 1) "Продолжить текущий выбор" 2) Нажмите кнопку Удалить дубликат.... 3) Нажмите кнопку "Выбрать все" 4) Нажмите "ОК"
теперь вы получаете уникальный список значений.