Список повторяющихся значений Excel
Обновлено: 21.11.2024
Команда Excel недавно объявила о новых формулах динамического массива, которые могут создавать уникальные списки, сортировать и фильтровать с помощью простой формулы. Эти новые формулы станут доступны подписчикам Office 365 в течение следующих нескольких месяцев. Однако не у всех есть подписка, и они будут обновляться, когда это целесообразно для их бизнеса, часто сочетая это с обновлением оборудования. Таким образом, может пройти 6 или более лет, прежде чем достаточное количество пользователей получат новую функциональность, чтобы безопасно использовать ее и обеспечить совместимость.
Вопрос. А пока что лучше?
Ответ: Формулы массива
СЧЁТЕСЛИ – это неиссякаемый источник энергии для большинства пользователей Excel. Подсчет ячеек, соответствующих определенным критериям, может показаться не особенно полезным, но в сочетании с другими функциями и логической логикой (истина/ложь) он открывает новые возможности, о которых вы и не подозревали.
В этом посте рассматривается один из аспектов этого и рассматривается, как использовать функцию СЧЁТЕСЛИ для создания и сравнения списков для проверки на наличие повторяющихся или уникальных значений. Мы начнем с некоторых базовых сценариев и постепенно усложняем их, пока не добьемся продвинутой магии формул.
Сравнение двух уникальных списков
Функция СЧЁТЕСЛИ может использоваться для сравнения двух списков и возврата количества элементов в обоих списках.
Давайте рассмотрим пример. На скриншоте ниже представлен список учеников школы Святого Иоанна (ячейки A2 – A7) и список учеников, сдавших определенный экзамен (ячейки B2-B6). Нас попросили указать количество лиц, включенных в оба списка (т. е. сколько учащихся школы Святого Иоанна приняли участие в экзамене).
Формула в ячейке E2:
Обычно функция СЧЁТЕСЛИ подсчитывает количество элементов из списка, соответствующих одному критерию. В данном случае мы не предоставили ни одного критерия, а использовали диапазон ячеек. Поскольку мы не использовали какие-либо логические операторы, такие как «больше чем» (>), Excel по умолчанию применяет «равно» (=) в качестве своего логического оператора. Таким образом, эта формула будет сравнивать каждую ячейку в диапазоне B2–B6, чтобы определить, равна ли она каким-либо ячейкам в диапазоне A2–A7.
В этом примере в рамках одной формулы выполняется несколько вычислений. Вычисление 1 сравнивает ячейку B2 с ячейками A2:A7, вычисление 2 сравнивает ячейку B3 с ячейками A2:A7 и т. д. и т. д. Всего имеется 5 отдельных результатов, рассчитанных одновременно; этот тип формулы известен как формула массива.
Функция СЧЁТЕСЛИ вычисляет в обратном порядке следующим образом:
Обратите внимание, что показаны все 5 результатов, разделенные точкой с запятой. Обернув это в функцию SUM, он добавит список из 1 и 0, который представляет собой количество элементов, которые появляются в обоих списках.
Поскольку это формула массива, не вводите фигурные скобки в начале или в конце формулы, Excel сам включит их, когда вы нажмете Ctrl + Shift + Enter, чтобы ввести формулу в ячейку или строку формул.
Если бы мы не хотели нажимать Ctrl + Shift + Enter, мы могли бы использовать функцию СУММПРОИЗВ, а не функцию СУММ. Формула в ячейке E4:
СУММПРОИЗВ – это специальная функция, которая может работать с массивами без использования Ctrl + Shift + Enter.
Обычно СУММПРОИЗВ используется для умножения ячеек или чисел, а затем сложения результатов умножения. В нашем случае в функции СУММПРОИЗВ не происходит умножения, поэтому она просто суммирует результат.
Сравнение списков с дубликатами
В приведенном выше примере оба списка уникальны, но что, если один из списков содержит дубликаты? В этом случае результат формулы будет неправильным. Я расширил пример, включив дубликаты для двух участников экзамена (см. снимок экрана ниже). Люси Кинг и Билли Томпсон теперь появляются дважды в столбце B. Результат предыдущего расчета показан в ячейках E2 и E4. Результат которого неправильно рассчитан как 5. Результат увеличился на 2 из-за повторяющихся значений.
При работе с уникальными данными не имеет значения, какой список используется в каждом аргументе функции СЧЁТЕСЛИ. Однако при наличии дубликатов первым диапазоном в функции должен быть диапазон, содержащий дубликаты, а вторым диапазоном — уникальные значения. Обратите внимание на это изменение в примерах для оставшейся части этого поста.
Подсчет уникальных элементов
Формула в ячейке E6:
Эта формула является расширением примера из раздела выше, но добавляет дополнительную сложность. Давайте потратим немного времени, чтобы понять, как это работает.
К формуле добавлена логическая проверка, поэтому включаются только элементы, количество которых >=1 (т. е. уникальные значения).
Это логическое выражение будет вычислено как TRUE или FALSE.
- Умножить значения TRUE/FALSE на 1
- Умножить значения ИСТИНА/ЛОЖЬ на – – (минус, минус).
В нашем примере я решил использовать метод двойного минуса. Теперь формула рассчитывается следующим образом:
Функция СУММ вернет правильное значение 3.
Если мы хотим избежать Ctrl + Shift + Enter, СУММПРОИЗВ — это вариант, как показано в ячейке E8:
Подсчет дубликатов
Применив те же формулы, но изменив логический порог, мы можем вычислить количество повторяющихся значений.
Формула в ячейке E10:
Поскольку оператору логики требуются значения больше или равные 2, он будет учитывать только дубликаты.
Снова мы можем использовать СУММПРОИЗВ, чтобы не нажимать Ctrl + Shift + Enter, как показано в ячейке E12:
Подсчет несовпадающих элементов
Последним показателем интереса может быть количество элементов, не соответствующих ни одному из списков. Для этого оператор логики снова меняется, чтобы показывать только те элементы, у которых счетчик равен нулю.
Формула в ячейке E14:
Версия без Ctrl + Shift + Enter в ячейке E16:
Извлечь список уникальных значений
Используя эту концепцию сравнения двух списков с помощью СЧЁТЕСЛИ, мы можем не только подсчитывать уникальные значения, но и извлекать список уникальных значений.
Данные примера изменились. Столбец А содержит список студентов, присутствовавших на экзамене, но содержит дубликаты. Мы будем использовать эти данные для создания уникального списка значений, как показано в столбце B.
Формула в ячейке B3:
Это формула массива, для которой требуется Ctrl + Shift + Enter.
Эта формула использует то, что мы уже узнали о СЧЕТЕСЛИ, и расширяет его еще больше. Давайте рассмотрим это подробнее.
Знакомство с формулой
Обратите внимание, что функция СЧЁТЕСЛИ включает относительную и абсолютную ссылку на ячейку с номерами строк в первом аргументе (B2:B$2). Это гарантирует, что при копировании функцией диапазон ячеек увеличивается в размере.
Для этой первой ячейки в нашем списке уникальных значений нет ничего (поскольку уникальный список в настоящее время представляет собой просто пустое значение в ячейке B2), поэтому СЧЁТЕСЛИ возвращает нули для каждого результата.
Далее мы рассмотрим функцию ПОИСКПОЗ.
Эта функция возвращает позицию первого 0, используя метод точного совпадения. Результатом функции ПОИСКПОЗ является 1, поскольку первый 0 находится в первой позиции.
Далее функция ИНДЕКС найдет ячейку в диапазоне от A2 до A16, которая имеет позицию 1 (т. е. первая ячейка). Это вычисляется следующим образом:
При использовании этой формулы нам не нужно знать, сколько существует уникальных значений, поскольку функция ЕСЛИОШИБКА гарантирует, что после достижения конца уникального списка будет возвращено пустое значение.
Скопируйте формулу
Перетащите формулу в ячейке B3 вниз, чтобы получить достаточно строк для текущих данных и любого будущего расширения.
Трудно оценить эту формулу, глядя только на первый результат, поэтому, чтобы лучше понять, как эта формула работает на самом деле, посмотрите на ячейку B4.
СЧЁТЕСЛИ теперь будут вычисляться следующим образом:
Теперь в результате есть 1. Это происходит каждый раз, когда значения в ячейках B2–B3 появляются в ячейках A2–A16. Теперь, когда функция ПОИСКПОЗ ищет 0, она возвращает второй элемент в списке (следующий уникальный элемент).
Для следующего примера проверьте ячейку B6:
По мере того, как в уникальный список теперь включается больше элементов (который теперь вырос до ячеек B2–B5), появляется больше единиц. Четвертый элемент в списке — это первый ноль, поэтому возвращается это значение.
По мере того, как формула копируется дальше, нулей становится меньше.
Если не осталось нулей (т. е. список содержит все уникальные значения), функция ПОИСКПОЗ вернет ошибки. Эти ошибки перехватываются оператором IFERROR и превращаются в пустые ячейки с двумя кавычками ("").
Если вы хотите избежать Ctrl + Shift + Enter, мы не можем использовать СУММПРОИЗВ, как раньше. Вместо этого мы можем использовать функцию ИНДЕКС для обработки массива. Требуемое изменение выделено ниже.
Извлечь список повторяющихся значений
Рассмотрев извлечение списка уникальных значений, мы теперь переходим к рассмотрению извлечения списка повторяющихся значений.
Формула в ячейке D3 настолько длинная, что занимает две строки ниже, но вы можете разместить ее в одной строке в Excel.
Отличия от уникальной формулы из приведенного выше раздела выделены ниже:
Первая функция СЧЁТЕСЛИ изменена и теперь включает логическую проверку того, где значение равно 0. Мы знаем, что 0 появляется там, где элемент ещё не появился в списке. Если он равен 0, результат будет ИСТИНА, в противном случае - ЛОЖЬ.
Вторая функция СЧЁТЕСЛИ сравнивает исходный список с самим собой, чтобы подсчитать количество экземпляров каждого значения. Если счетчик больше или равен 2 (т. е. это повторяющееся значение), будет возвращено значение TRUE, в противном случае будет возвращено значение FALSE.
Поскольку теперь у нас есть два списка ИСТИНА/ЛОЖЬ, мы можем перемножить их вместе. Там, где есть два значения TRUE (т. е. значение появляется более одного раза в основном списке и еще не отображается в нашем списке дубликатов). умножение возвращает 1, все остальные значения будут содержать значение FALSE, поэтому будут умножаться на 0.
Мы изменили функцию ПОИСКПОЗ, чтобы найти 1, а не 0.
Перетащите эту формулу вниз, и мы создадим список повторяющихся значений.
Еще раз, если вы хотите избежать Ctrl + Shift + Enter, используйте функцию ИНДЕКС для обработки массива. Требуемое изменение выделено ниже.
Зачем использовать сложную формулу?
- Сводная таблица
- Энергетический запрос
- Удалить дубликаты с ленты данных
Каждый из этих параметров легче понять, чем сложные формулы, которые мы создали, так зачем их беспокоить? Не проще ли использовать один из этих других методов? Да и нет.
Для работы всех этих параметров требуется либо макрос, либо взаимодействие с пользователем. Для сводных таблиц и Power Query пользователь должен «обновить» данные. Для удаления дубликатов пользователь должен вручную удалять дубликаты каждый раз, когда происходит изменение.
У формулы есть одно существенное преимущество. он может быть полностью динамическим и автоматически обновляться при изменении данных. Если исходные данные представляют собой таблицу Excel или динамический именованный диапазон, список уникальных или повторяющихся данных может быть обновлен, даже если к исходным данным добавляются новые данные. На мой взгляд, это делает его гораздо более удобным решением.
Пока мы не получим новые формулы динамического массива, которые будут развернуты и хорошо зарекомендовали себя, описанные выше методы, вероятно, будут доступны.
Заключение
Как мы видели, простая функция СЧЁТЕСЛИ значительно мощнее, чем думает большинство пользователей Excel. Используя диапазон в качестве критерия, мы смогли создать формулу массива, которая содержит несколько вычислений в одной ячейке. Затем в сочетании с ИНДЕКС и ПОИСКПОЗ можно создавать списки уникальных или повторяющихся значений.
Понимание того, как Excel обрабатывает ИСТИНА/ЛОЖЬ, является важной частью создания сложных формул в Excel.
Получите БЕСПЛАТНУЮ электронную книгу VBA с 30 наиболее полезными макросами Excel VBA.
Автоматизируйте Excel, чтобы сэкономить время и перестать выполнять работу, которую могла бы выполнять обученная обезьяна.
Не забывайте:
Если вы нашли этот пост полезным или у вас есть лучший подход, оставьте комментарий ниже.
Вам нужна помощь в адаптации этого к вашим потребностям?
Я предполагаю, что примеры в этом посте не совсем соответствуют вашей ситуации. Мы все используем Excel по-разному, поэтому невозможно написать пост, который удовлетворит потребности всех. Потратив время на изучение методов и принципов, изложенных в этом посте (и в других местах на этом сайте), вы сможете адаптировать его к своим потребностям.
- Читайте другие блоги или смотрите видео на YouTube по той же теме. Вы получите гораздо больше пользы, найдя собственные решения.
- Спросите «Excel Ninja» в вашем офисе. Удивительно, какие вещи знают другие люди.
- Задайте вопрос на форуме, таком как Mr Excel, или в сообществе Microsoft Answers. Помните, что люди на этих форумах обычно отдают свое время бесплатно. Поэтому постарайтесь сформулировать свой вопрос, убедитесь, что он четкий и лаконичный. Перечислите все, что вы пробовали, и предоставьте скриншоты, сегменты кода и примеры книг.
- Используйте Excel Rescue, моего партнера-консультанта. Они помогают решить небольшие проблемы с Excel.
Что дальше?
Пока не уходите, в Excel Off The Grid есть чему поучиться. Ознакомьтесь с последними сообщениями:
Как извлечь все дубликаты из столбца в Excel?
Если у вас есть столбец списка продуктов, и некоторые из них являются дубликатами, теперь вам нужно извлечь только дубликаты из этого списка в другой столбец, как вы можете решить? Как правило, вы можете найти их один за другим, а затем ввести вручную. Сейчас я расскажу вам о нескольких быстрых способах извлечения дубликатов из одного столбца в Excel.
Извлечение дубликатов из одного столбца с помощью Kutools for Excel
Извлечение дубликатов из одного столбца с формулами
Здесь я представляю вам две формулы для извлечения дубликатов.
Или вы можете использовать эту формулу =ЕСЛИОШИБКА(ИНДЕКС(A2:A11, ПОИСКПОЗ(0, СЧЁТЕСЛИ(C1:$C$1, A2:A11)+ЕСЛИ(СЧЁТЕСЛИ(A2:A11, A2:A11)>1, 0, 1), 0)), "") , нажмите клавиши Shift + Ctrl + Enter. Затем перетащите маркер заполнения вниз, пока не появится пустая ячейка.
Примечание. В приведенном выше примере A2:A11 — это диапазон столбцов, из которого вы хотите извлечь дубликаты, а C1 — это первая ячейка столбца, в которую введена ваша формула.
Извлечение дубликатов из одного столбца с помощью Kutools for Excel
Формулы может быть трудно запомнить, но если у вас есть Kutools for Excel's Select Duplicate & Unique Cells, вы можете быстро выбрать повторяющиеся ячейки, а затем скопировать и вставить их в другой столбец.
После бесплатной установки Kutools for Excel сделайте следующее:
<р>1. Выберите список столбцов, исключая заголовок, если у вас есть заголовок, нажмите Kutools > Выбрать > Выбрать повторяющиеся и уникальные ячейки. Смотрите скриншот:<р>2. В диалоговом окне «Выбор повторяющихся и уникальных ячеек» установите флажок «Дубликаты (кроме 1-го)» или «Все дубликаты» (включая 1-й), как вам нужно. Затем нажмите OK, появится диалоговое окно, в котором будет указано, сколько строк выбрано. Смотрите скриншот:
<р>3. Нажмите «ОК». Нажмите Ctrl + C, чтобы скопировать повторяющиеся значения, выберите ячейку и нажмите Ctrl + V, чтобы вставить их.
Совет. Если вам нужно, вы можете применить функцию Данные > Удалить дубликаты, чтобы удалить дубликаты в столбце Дубликаты.
Формула массива в ячейке C2 извлекает повторяющиеся значения из столбца A. В столбце C отображается только один дубликат каждого значения.
В этом видео объясняется, как использовать формулу и как она работает
Следующая формула устарела, приведенная выше формула меньше и лучше.
Формула массива в D3:
=ИНДЕКС($B$3:$B$21, ПОИСКПОЗ(0, СЧЁТЕСЛИ(D2:$D$2, $B$3:$B$21)+ЕСЛИ(СЧЁТЕСЛИ($B$3:$B$21, $B$3) :$B$21)>1, 0, 1), 0))
Как ввести формулу массива
- Копировать (Ctrl + c) формулу выше.
- Дважды нажмите левой кнопкой мыши на ячейку C2.
- Вставить (Ctrl + V) в ячейку C2.
- Одновременно нажмите и удерживайте клавиши CTRL + SHIFT.
- Нажмите Enter один раз
- Отпустить все клавиши
Теперь ваша формула выглядит следующим образом:
Не вводите фигурные скобки, они появляются автоматически.
Формулы массива позволяют выполнять сложные вычисления, недоступные для обычных формул.
Как скопировать формулу
Скопируйте ячейку C2 и вставьте ее в ячейки ниже, насколько это необходимо.
=ЕСЛИОШИБКА(ИНДЕКС($B$3:$B$21, СОВПАДЕНИЕ(0, СЧЁТЕСЛИ(D2:$D$2, $B$3:$B$21)+ЕСЛИ(СЧЁТЕСЛИ($B$3:$B$21, $ B$3:$B$21)>1, 0, 1), 0)), "")
Скопируйте ячейку C2 и вставьте ее в ячейку D20.
Функция ЕСЛИОШИБКА позволяет выявить большинство ошибок в формулах Excel. Он был представлен в Excel 2007. В предыдущей версии Excel […]
Предыдущие версии Excel, формула массива в C2:
=ЕСЛИ(ОШИБКА(ИНДЕКС($A$2:$A$20, ПОИСКПОЗ(0, СЧЁТЕСЛИ(C1:$C$1, $A$2:$A$20)+ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$20) , $A$2:$A$20)>1, 0, 1), 0))), "", ИНДЕКС($A$2:$A$20, ПОИСКПОЗ(0, СЧЁТЕСЛИ(C1:$C$1, $A$2) :$A$20)+ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$20, $A$2:$A$20)>1, 0, 1), 0)))
Функция ЕСЛИОШИБКА появилась в Excel 2007. Если у вас более ранняя версия, используйте приведенную выше формулу.
Как работает (старая) формула массива
Шаг 1. Показать повторяющееся значение только один раз
=ИНДЕКС($B$3:$B$21, ПОИСКПОЗ(0, СЧЁТЕСЛИ(D2:$D$2, $B$3:$B$21)+ЕСЛИ(СЧЁТЕСЛИ($B$3:$B$21, $B$3) :$B$21)>1, 0, 1), 0))
СЧЁТЕСЛИ(D2:$D$2, $B$3:$B$21) содержит как относительную, так и абсолютную ссылку (D2:$D$2) на диапазон.
Когда вы копируете ссылку на ячейку таким образом, ссылка на ячейку расширяется.
СЧЁТЕСЛИ(D2:$D$2, $B$3:$B$21) возвращает этот массив в ячейке C2: (0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 , 0, 0, 0, 0, 0, 0, 0, 0)
Подсчитывает количество ячеек, соответствующих определенному условию.
Шаг 2. Отфильтруйте значения в $A$2:$A$20, имеющие дубликаты
=ИНДЕКС($B$3:$B$21, ПОИСКПОЗ(0, СЧЁТЕСЛИ(D2:$D$2, $B$3:$B$21)+ЕСЛИ(СЧЁТЕСЛИ($B$3:$B$21, $B$3) :$B$21)>1, 0, 1), 0))
СЧЁТЕСЛИ($B$3:$B$21, $B$3:$B$21) подсчитывает количество ячеек в диапазоне, удовлетворяющих заданному условию. Возвращаемый массив: (2, 1, 1, 2, 1, 1, 2, 2, 1, 2, 2, 1, 2, 1, 1, 2, 1, 1, 1, 1).
СЧЁТЕСЛИ($B$3:$B$21, $B$3:$B$21)>1 возвращает этот массив: (ИСТИНА, ЛОЖЬ, ЛОЖЬ, ИСТИНА, ЛОЖЬ, ЛОЖЬ, ИСТИНА, ИСТИНА, ЛОЖЬ, ИСТИНА, ИСТИНА, ЛОЖЬ, ИСТИНА, ЛОЖЬ, ЛОЖЬ, ИСТИНА, ЛОЖЬ, ЛОЖЬ, ЛОЖЬ, ЛОЖЬ).
ЕСЛИ(СЧЁТЕСЛИ($B$3:$B$21, $B$3:$B$21)>1, 0, 1) возвращает (1, 0, 0, 1, 0, 0, 1, 1, 0, 1, 1, 0, 1, 0, 0, 1, 0, 0, 0, 0).
Проверяет, встречается ли логическое выражение. Возвращает конкретное значение, если TRUE, и другое конкретное значение, если FALSE.
Шаг 3. Вычисление объединенных массивов
=ИНДЕКС($B$3:$B$21, ПОИСКПОЗ(0, СЧЁТЕСЛИ(D2:$D$2, $B$3:$B$21)+ЕСЛИ(СЧЁТЕСЛИ($B$3:$B$21, $B$3) :$B$21)>1, 0, 1), 0))
СЧЁТЕСЛИ(D2:$D$2, $B$3:$B$21)+ЕСЛИ(СЧЁТЕСЛИ($B$3:$B$21, $B$3:$B$21)>1, 0, 1)
(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0) + (1, 0, 0, 1, 0, 0, 1, 1, 0, 1, 1, 0, 1, 0, 0, 1, 0, 0, 0, 0) равно
(1, 0, 0, 1, 0, 0, 1, 1, 0, 1, 1, 0, 1, 0, 0, 1, 0, 0, 0, 0)
Шаг 4. Найдите дубликаты
=ИНДЕКС($B$3:$B$21, ПОИСКПОЗ(0, СЧЁТЕСЛИ(D2:$D$2, $B$3:$B$21)+ЕСЛИ(СЧЁТЕСЛИ($B$3:$B$21, $B$3) :$B$21)>1, 0, 1), 0))
ПОИСКПОЗ(0, СЧЁТЕСЛИ(D2:$D$2, $B$3:$B$21)+ЕСЛИ(СЧЁТЕСЛИ($B$3:$B$21, $B$3:$B$21)>1, 0, 1 ), 0) становится
ПОИСКПОЗ(0, (1, 0, 0, 1, 0, 0, 1, 1, 0, 1, 1, 0, 1, 0, 0, 1, 0, 0, 0, 0), 0 ) равно 2.
Match возвращает относительное положение элемента в массиве, которое соответствует указанному значению
Определить положение значения в массиве.
Шаг 5. Возврат дубликатов
=ИНДЕКС($B$3:$B$21, ПОИСКПОЗ(0, СЧЁТЕСЛИ(D2:$D$2, $B$3:$B$21)+ЕСЛИ(СЧЁТЕСЛИ($B$3:$B$21, $B$3) :$B$21)>1, 0, 1), 0))
=INDEX($B$3:$B$21, 2) — Федерер, Роджер.
Индекс возвращает значение или ссылку на ячейку на пересечении определенной строки и столбца в заданном диапазоне
Получает значение в определенном диапазоне ячеек на основе номера строки и столбца.
Заключительные мысли
Когда формула из c2 копируется в c3, ссылка изменяется.
Формула в c2: =ИНДЕКС($A$2:$A$20, ПОИСКПОЗ(0, СЧЁТЕСЛИ(D2:$D$2, $A$2:$A$20)+ЕСЛИ(СЧЁТЕСЛИ($A$2:$A $20, $A$2:$A$20)>1, 0, 1), 0))
Затем скопируйте формулу в C3.
Формула ссылается на изменения: =ИНДЕКС($A$2:$A$20, ПОИСКПОЗ(0, СЧЁТЕСЛИ(D3:$D$2, $A$2:$A$20)+ЕСЛИ(СЧЁТЕСЛИ($A$2:$A). $20, $A$2:$A$20)>1, 0, 1), 0))
Это позволяет не использовать предыдущие значения ячеек (C2) и вычислять только оставшиеся значения.
Получить файл Excel
Как извлечь-список-дубликатов-из-столбцов-в-excelv2.xlsx
Еженедельный блог EMAIL
[newsletter_signup_form Я Оскар и здесь, чтобы помочь вам.
Не стесняйтесь оставлять комментарии и задавать вопросы по Excel.
Подпишитесь на мою рассылку, чтобы не пропустить новые статьи блога.
Статьи по теме
Следующая обычная формула извлекает повторяющиеся значения из столбцов B (список 1), D (список 2) и F (список 3), вместе взятых, результат […]
В этой статье объясняется, как извлечь значения, существующие в трех разных столбцах, они должны встречаться в каждом из […]
Вопрос. Как получить наибольшее и наименьшее уникальное и повторяющееся значение? На изображении ниже показан список […]
На изображении выше показана формула в ячейке E3, которая извлекает повторяющиеся элементы, если они относятся к одной и той же дате. […]
Вопрос: у меня есть два диапазона или списка (List1 и List2), из которых я хотел бы извлечь уникальный отдельный […]
На рисунке выше показано, как объединить два столбца в один список с помощью формулы. Если вы ищете […]
Вопрос: Как извлечь адреса электронной почты из этого листа? (См. рис. ниже) Ответ: Это зависит от того, как электронные письма […]
Функция ЕОШИБКА возвращает ИСТИНА, если ячейка возвращает ошибку. Формула в ячейке D3: =ЕОШИБКА(B3) Синтаксис функции Excel ЕОШИБКА(значение) […]
46 ответов на вопрос «Извлечь список дубликатов из столбца»
Никакого упоминания о Надале :D Я предполагаю, что вы фанат Федерера. Недурно
Я попробовал эту формулу, она у меня не работает. Как понять, что я делаю не так??
Как создать формулу массива
Выберите ячейку C2.
Скопируйте/вставьте формулу массива.
Нажмите и удерживайте Ctrl + Shift.
Нажмите Enter.
как извлечь уникальный отдельный столбец с одного листа на другой лист в той же рабочей книге с помощью формулы.
Изменить ссылки на ячейки.
я изменил ссылку на ячейку, но значение отображается как "0".
лист 1 - столбец A имеет 123412323422 значения (с пустыми ячейками).
Я хочу, чтобы эти уникальные значения отображались в столбце A листа2..
предоставьте формулу для получения значений на листе 2 из листа 1.
добавлено к приведенному выше
я хочу получить результат в столбце A листа 2 как 1234 (без пустых ячеек)
Вот пример книги:
audithya.xlsx
Я использовал этот пример книги в своем собственном приложении, и он отлично работает. Мне интересно, есть ли способ отфильтровать результаты и/или критерии. В столбце дубликатов у меня есть широкий спектр ячеек, заполненных текстом, которые довольно часто дублируются. Я хотел бы, чтобы эта формула возвращала только ячейки, начинающиеся с буквы «L», а также удаляла все дубликаты. Возможно ли это?
Столбец A, Лист 1
ЯБЛОКО
ПЕКАРЬ
ЛОТ1
ЛОТ3
ФЕРМА1
ФЕРМА1
СТОЛ
ЛОТ1
/>ЛОТ4
ЛОТ3
Возврат данных в столбец A, лист2
(Не обязательно в отсортированном порядке)
У меня есть два листа в книге. один столбец первого листа содержит первый лист содержит повторяющиеся данные. на втором листе в столбце есть данные, которые извлекаются из уникальных данных с первого листа. я хочу сравнить данные этих двух столбцов, если они равны, тогда результат следующего столбца на листе 1 будет на листе 2
ColA ColB
a Pass
a Fail
ab Pass
abc Pass
abcd Pass
abcde Fail
abc Fail
Лист2: хочет быть похожим
ColA ColB
a Пройдено
ab пройдено
abc пройдено
abcd пройдено
abcde не пройдено
этот colB листа 2 извлекает данные из colB листа 1, если sheet2!ColA=Sheet1!ColA.
Как найти повторяющиеся значения, не удаляя их в Excel?
В Excel функция «Удалить дубликаты» может помочь пользователям быстро удалить все повторяющиеся значения из списка. Но в некоторых случаях вам может потребоваться только найти и идентифицировать повторяющиеся значения, не удаляя их, как показано на снимке экрана ниже. Как вы можете быстро справиться с этим?
Найти повторяющиеся значения без удаления с помощью Kutools for Excel
Поиск повторяющихся значений без удаления с помощью условного форматирования
Условное форматирование может быстро выделить повторяющиеся значения в списке.
<р>1. Выберите значения, которые вы хотите найти дубликаты, нажмите «Главная» > «Условное форматирование» > «Правила выделения ячеек» > «Повторяющиеся значения». Смотрите скриншот:
<р>2. В появившемся диалоговом окне «Повторяющиеся значения» выберите параметр выделения, который вам нужен, из правого раскрывающегося списка. Смотрите скриншот:
<р>3. Нажмите «ОК». И дубликаты были выделены.
Поиск повторяющихся значений без удаления по формулам
Вот несколько формул для определения повторяющихся значений в списке.
Выберите пустую ячейку рядом со списком значений, которые вы хотите найти дубликаты, введите эту формулу =ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$14,A2)>1,"ДУБЛИКАЦИЯ","") , перетащите заполнение обработайте ячейку, к которой нужно применить эту формулу. Смотрите скриншот:
Примечание:
<р>1. Если вы хотите найти дубликаты, исключая первое появление, вы можете применить эту формулу =ЕСЛИ(СЧЁТЕСЛИ(A$2:A2,A2)>1,"ДУБЛИКАЦИЯ","") .
Найти повторяющиеся значения без удаления с помощью Kutools for Excel
На самом деле, есть удобная утилита – Выберите повторяющиеся и уникальные ячейки Kutools для Excel, которая может оказать вам хорошую услугу, быстро идентифицируя и выделяя повторяющиеся значения или уникальные значения.
После установки Kutools for Excel сделайте следующее: (Загрузите Kutools for Excel сейчас!)
<р>1. Выберите список значений, которые вы хотите идентифицировать дубликаты, нажмите Kutools > Выбрать > Выбрать повторяющиеся и уникальные ячейки. Смотрите скриншот:
1) Выберите нужные параметры в разделе «Правило», например, выберите «Дубликаты (кроме 1-го)», чтобы выбрать повторяющиеся значения, исключая первое появляющееся;
2) Укажите, следует ли выделять повторяющиеся ячейки с другим цветом фона или цветом шрифта;
3) Укажите, следует ли выбирать целые строки или выбирать дубликаты с учетом регистра.
<р>3. Нажмите «ОК». Появится диалоговое окно, чтобы напомнить вам, сколько повторяющихся ячеек было выбрано, и вы можете нажать OK, чтобы закрыть его, и в то же время повторяющиеся значения были идентифицированы и выделены.
Совет.Если вы хотите получить бесплатную пробную версию функции «Выбор дубликатов и уникальных ячеек», сначала перейдите к бесплатной загрузке Kutools for Excel, а затем перейдите к применению операции в соответствии с описанными выше шагами.
Читайте также: