Получение уникальных значений в Excel только с использованием формул

Знаете ли вы, что в Excel «вычислять» по формуле список уникальных значений?

Например: диапазон содержит значения "red" , "blue" , "red" , "green" , "blue" , "black"
и я хочу иметь в результате "red , "blue" , "green" , "black" +, в конце концов, две другие пустые ячейки.

Я уже нашел способ получить вычисленный отсортированный список с помощью SMALL или LARGE в сочетании с INDEX, но я бы тоже хотел, чтобы этот расчетный тип, без использования VBA.

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

Я назвал список Colors (A2: A7), и формула массива, помещенная в ячейку C2, является этой ( фиксированной ):

 =IFERROR(INDEX(Colors,MATCH(SUM(COUNTIF(C$1:C1,Colors)),COUNTIF(Colors,"<"&Colors),0)),"") 

Используйте Ctrl+Shift+Enter чтобы ввести формулу в C2 , и скопируйте C2 до C3: C7 .

Объяснение с образцовыми данными {"red"; «Синий»; «Красный»; «Зеленый»; «Синий»; "Черный"}:

  1. COUNTIF(Colors,"<"&Colors) возвращает массив (# 1) с количеством значений, которые меньше, чем каждый элемент данных {4; 1; 4; 3; 1; 0} (черный = 0 единиц меньше, синий = 1 элемент, красный = 4 предмета). Это можно преобразовать в значение сортировки для каждого элемента.
  2. 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} он указывает «черный», и все вхождения «синего» уже присутствуют.
  3. SUM возвращает значение k-го значения, подсчитывая все более мелкие значения, которые уже присутствуют (сумма массива # 2).
  4. MATCH находит первый индекс k-го значения сортировки (индекс в массиве # 1).
  5. IFERROR только скрывать ошибку #N/A в нижних ячейках, когда отсортированный уникальный список завершен.

Чтобы узнать, сколько уникальных предметов у вас есть, вы можете использовать эту обычную формулу :

 =SUM(IF(FREQUENCY(COUNTIF(Colors,"<"&Colors),COUNTIF(Colors,"<"&Colors)),1)) 

Хорошо, у меня есть две идеи для вас. Надеюсь, один из них доставит вас туда, куда вам нужно идти. Обратите внимание, что первый игнорирует запрос, чтобы сделать это как формулу, поскольку это решение не очень красиво. Я решил, что я уверен, что простой способ действительно не сработает для вас; ^).

Используйте команду «Расширенный фильтр»

  1. Выберите список (или поместите свой выбор в любом месте списка и нажмите «ОК», если в диалоговом окне появляется сообщение о том, что Excel не знает, содержит ли ваш список заголовки или нет)
  2. Выберите Data / Advanced Filter
  3. Выберите «Отфильтровать список, на месте» или «Скопировать в другое место»
  4. Нажмите «Только уникальные записи»,
  5. Нажмите ok
  6. Вы сделали. Уникальный список создается либо на месте, либо в новом месте. Обратите внимание, что вы можете записать это действие, чтобы создать однострочный скрипт VBA для этого, который затем можно было бы обобщить для работы в других ситуациях для вас (например, без перечисленных выше шагов управления).

Использование формул (обратите внимание, что я строил решение Locksfree, чтобы в итоге получить список без отверстий)

Это решение будет работать со следующими оговорками:

  • Список должен быть отсортирован (по возрастанию или по убыванию не имеет значения). На самом деле это довольно точно, так как требование действительно состоит в том, что все подобные предметы должны быть смежными, но сортировка – это самый простой способ достичь этого состояния.
  • Требуются три новых столбца (два новых столбца для вычислений и один новый столбец для нового списка). Второй и третий столбцы можно объединить, но я оставлю это упражнение для читателя.
  • Вот краткое изложение решения:

    1. Для каждого элемента в списке вычислите количество дубликатов над ним.
    2. Для каждого места в уникальном списке вычислите индекс следующего уникального элемента.
    3. Наконец, используйте индексы для создания нового списка только с уникальными элементами.

    И вот шаг за шагом:

    1. Открыть новую таблицу
    2. В a1: a6 введите пример, указанный в исходном вопросе («красный», «синий», «красный», «зеленый», «синий», «черный»)
    3. Сортировка списка: поместите выделение в список и выберите команду сортировки.
    4. В столбце B вычислите дубликаты:
      1. В B1 введите «= IF (COUNTIF ($ A $ 1: A1, A1) = 1,0, COUNTIF (A1: $ A $ 6, A1))». Обратите внимание, что «$» в ссылках на ячейки очень важно, так как это сделает следующий шаг (заполнение остальной части столбца) намного проще. «$» Указывает абсолютную ссылку, поэтому, когда содержимое ячейки копируется / вставляется, ссылка не будет обновляться (в отличие от относительной ссылки, которая будет обновляться).
      2. Используйте умную копию для заполнения остальной части столбца B: выберите B1. Наведите указатель мыши на черный квадрат в нижнем правом углу выделения. Нажмите и перетащите вниз в конец списка (B6). Когда вы отпускаете, формула будет скопирована в B2: B6 с обновленными относительными ссылками.
      3. Значение B1: B6 должно теперь быть «0,0,1,0,0,1». Обратите внимание, что записи «1» указывают на дубликаты.
    5. В столбце C создайте индекс уникальных элементов:
      1. В C1 введите «= Row ()». Вы действительно просто хотите C1 = 1, но использование Row () означает, что это решение будет работать, даже если список не начинается в строке 1.
      2. В C2 введите «= IF (C1 + 1 <= ROW ($ B $ 6), C1 + 1 + INDEX ($ B $ 1: $ B $ 6, C1 + 1), C1 + 1)". «If» используется для остановки создания #REF, когда индекс достигает конца списка.
      3. Используйте смарт-копию для заполнения C3: C6.
      4. Значение C1: C6 должно быть «1,2,4,5,7,8»,
    6. В столбце D создайте новый уникальный список:
      1. В D1 введите «= IF (C1 <= ROW ($ A $ 6), INDEX ($ A $ 1: $ A $ 6, C1)," ")". И «if» используется для остановки случая #REF, когда индекс выходит за пределы списка.
      2. Используйте умную копию для заполнения D2: D6.
      3. Значения D1: D6 теперь должны быть «черные», «синие», «зеленые», «красный», «», «».

    Надеюсь это поможет….

    Решение

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

    1. Нажмите Alt + F11
    2. Нажмите « Module в « Insert .
    3. Вставить код.
    4. Если Excel говорит, что ваш формат файла не является дружественным к макросу, чем сохранить его в качестве макроса макроса 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 .

    пример

    Обычный случай

    1. Введите данные и функцию вызова.
      Введите данные и функцию вызова
    2. Расти его.
      Растите его
    3. Вуаля.
      Вуаля

    Пустая ячейка

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

    Пустая ячейка

    Обходной способ – загрузить электронную таблицу Excel в электронную таблицу Google, использовать функцию UNIQUE (диапазон) Google, которая делает именно то, что вы хотите, а затем сохранить таблицу Google в формате Excel.

    Я признаю, что это не жизнеспособное решение для пользователей Excel, но этот подход полезен для всех, кто хочет использовать эту функцию и может использовать электронную таблицу Google.

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

    Допустим, у вас в столбце A2: A7 есть «красный», «синий», «красный», «зеленый», «синий», «черный»

    затем =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)));"") это в 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)));"")

    то он должен выглядеть примерно так: введите описание изображения здесь

    Попробуйте эту формулу в ячейке 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 (ТОЧНО (COUNTIF (A4: $ A $ 6, A3), 1), A4, “”)

    Это даст вам столбец, где все уникальные значения не имеют дубликатов

    Предполагая, что столбец A содержит значения, которые вы хотите найти один уникальный экземпляр, и имеет строку заголовка, я использовал следующую формулу. Если вы хотите, чтобы он масштабировался с непредсказуемым количеством строк, вы могли бы заменить A772 (где мои данные закончились) с = ADDRESS (COUNTA (A: A), 1) .

    = ЕСЛИ (СЧЕТЕСЛИ (А5: $ A $ 772, А5) = 1, А5, “”)

    Это отображает уникальное значение в экземпляре LAST каждого значения в столбце и не предполагает никакой сортировки. Он использует недостаток абсолютов, чтобы по существу иметь уменьшающееся «скользящее окно» данных для подсчета. Когда счет в уменьшенном окне равен 1, эта строка является последним экземпляром этого значения в столбце.

    Даже для получения отсортированного уникального значения это можно сделать с помощью формулы. Это вариант, который вы можете использовать:

     =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

    Это ФОРМУЛА МАШИНЫ

    Решение Дрю Шермана очень хорошее, но список должен быть смежным (он предлагает ручную сортировку, и это неприемлемо для меня). Решение Guitarthrower немного медленнее, если количество элементов велико и не соответствует порядку исходного списка: он выводит отсортированный список независимо.

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

    Мое решение – это усовершенствование решения Дрю Шермана. Аналогично, это решение использует 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); "") 

    Для заполнения столбца используйте смарт-копию. Это уникальный уникальный вывод.

    Вы также можете сделать это таким образом.

    Создайте следующие диапазоны имен:

     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)),"") 

    Я удивлен, что это решение еще не пришло. Я думаю, что это один из самых простых

    Дайте вашим данным заголовок и поместите его в динамический именованный диапазон (т. Е. Если ваши данные находятся в столбце A )

     =OFFSET($A$2,0,0,COUNTA($A:$A),1) 

    А затем создайте сводную таблицу, сделав исходный ваш именованный диапазон.

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

    Я вставил то, что я использую в своем файле 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),)) 

    это формула массива

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

    Пример SO1429899

    Недавно я столкнулся с той же проблемой и, наконец, понял это.

    Используя ваш список, вот паста из моего Excel с формулой.

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

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

    Используя ваш список («красный», «синий», «красный», «зеленый», «синий», «черный»); вот результат: ( у меня нет достаточно высокого уровня для публикации изображения, так что надеюсь, что эта версия txt имеет смысл )

    • [Столбец A: Исходный список]
    • [Столбец B: уникальный результат списка]
    • [Колонка C: уникальная формула списка]

      1. красный, красный, =A3
      2. синий, синий, =IF(ISERROR(MATCH(A4,A$3:A3,0)),A4,"")
      3. красный =IF(ISERROR(MATCH(A5,A$3:A4,0)),A5,"")
      4. зеленый, зеленый, =IF(ISERROR(MATCH(A6,A$3:A5,0)),A6,"")
      5. синий, =IF(ISERROR(MATCH(A7,A$3:A6,0)),A7,"")
      6. черный, черный =IF(ISERROR(MATCH(A8,A$3:A7,0)),A8,"")

    Это работает только в том случае, если значения находятся в порядке, т.е. все «красные» вместе, и все «синие» вместе и т. Д. Предполагают, что ваши данные находятся в столбце A, начинающемся с A2 – (не начинайте с строки 1). B2 type in 1 In b3 type = if (A2 = A3, B2, B2 + 1) Перетащите формулу до конца ваших данных. Все «красные» будут 1, все «синие» будут 2, все «зеленые» будут 3 и т. д.

    В C2-типе в 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- formula / Oscar at 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")

    меры

    1. Сортировка данных
    2. Добавить столбец для формулы
    3. Проверяет, соответствует ли ячейка ячейке над ней
    4. Затем фильтруйте Not a Duplicate
    5. Необязательно: скопируйте данные, вычисленные столбцом формулы, и вставьте только значения (таким образом, если вы начнете удалять данные, вы не начнете получать ошибки
    6. ПРИМЕЧАНИЕ / ПРЕДУПРЕЖДЕНИЕ. Это работает только в том случае, если вы сначала сортируете данные

    Пример Формула: =IF(C105=C104,"Duplicate","Not a Duplicate")

    Оптимизированное решение VBScript

    Я использовал код 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) Нажмите ОК

    теперь вы получаете уникальный список значений.

    Давайте будем гением компьютера.