Подсчет количества уникальных значений среди повторяющихся

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

Значения 5, 6, 7 и 6, результатом являются три уникальных значения : 5, 6 и 7.

Значения «Брэнли», «Дойл», «Дойл», «Дойл» — это два уникальных значения: «Андрей» и «Дойл».

Существует несколько способов подсчета уникальных значений среди дубликатов.

В диалоговом окне Расширенный фильтр можно извлечь уникальные значения из столбца данных и ввести их в новое место. Затем с помощью функции ЧСТРОК можно подсчитать количество элементов в новом диапазоне.

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

Убедитесь, что диапазон ячеек имеет заголовок столбца.

На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.

Появится диалоговое окно Расширенный фильтр.

Нажмите кнопку Копировать в другое место.

В поле Копировать в введите ссылку на ячейку.

Вы также можете нажать кнопку Свернуть , чтобы временно скрыть диалоговое окно, выбрать ячейку на этом сайте и нажать кнопку Развернуть .

Выберите поле Уникальные записи и нажмите кнопку ОК.

Уникальные значения из выбранного диапазона копируется в новое место, начиная с ячейки, указанной в поле Копировать в.

В пустой ячейке под последней ячейкой диапазона введите функцию СТРОКИ. Используйте диапазон уникальных значений, скопированные в качестве аргумента, исключая заголовок столбца. Например, если диапазон уникальных значений — B2:B45, введите =СТРОКИ(B2:B45).

Для этой задачи используйте сочетание функций ЕСЛИ,СУММ,ЧАСТОТА,НАЙТИИ LEN:

Назначьте значение 1 каждому из истинных условий с помощью функции ЕСЛИ.

Сложить итог с помощью функции СУММ.

Подсчет количества уникальных значений с помощью функции ЧАСТОТА. Функция ЧАСТОТА игнорирует текст и нулевые значения. Для первого вхождения определенного значения эта функция возвращает число, равное количеству его вхождений. Для каждого вхождения с одинаковым значением после первого функция возвращает ноль.

Возвращает положение текстового значения в диапазоне с помощью функции MATCH. Возвращаемая величина затем используется в качестве аргумента функции ЧАСТОТА для оценки соответствующих текстовых значений.

Находите пустые ячейки с помощью функции LEN. Пустые ячейки имеют длину 0.

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

Формулы, приведенные в этом примере, должны быть введены как формулы массива. Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

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

Функция ЧАСТОТА вычисляет частоту ветвей значений в диапазоне значений и возвращает вертикальный массив чисел. Например, с помощью частоты можно подсчитать количество результатов тестирования, которые попадают в диапазоны оценок. Так как эта функция возвращает массив, она должна быть введена как формула массива.

Функция ПОИСК ПОИСК ПО ищет указанный элемент в диапазоне ячеек, а затем возвращает его относительную позицию в диапазоне. Например, если диапазон A1:A3 содержит значения 5, 25 и 38, формула =MATCH(25;A1:A3;0) возвращает число 2, поскольку 25 является вторым элементом в диапазоне.

Функция LEN возвращает количество символов в текстовой строке.

Функция СУММ вычисляет сумму всех чисел, указанных в качестве аргументов. Каждый аргумент может быть диапазоном, ссылкой на ячейку, массивом, константой, формулой или результатом другой функции. Например, СУММ(A1:A5) суммирует все числа, содержащиеся в ячейках A1–A5.

Функция ЕСЛИ возвращает одно значение, если условие, которое вы указываете, возвращает значение ИСТИНА, и другое, если условие возвращает значение ЛОЖЬ.

Удаление повторяющихся значений

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

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

Вы выберите диапазон ячеек или убедитесь, что активная ячейка находится в таблице.

На вкладке Данные нажмите кнопку Удалить дубликаты.

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

Примечание: Данные будут удалены из всех столбцов, даже если на этом этапе не выбраны все столбцы. Например, если выбрать Столбец1 и Столбец2, но не Столбец3, то ключом, используемым для поиска дубликатов, будет значение BOTH Column1 & Column2. Если в столбцах «Столбец1» и «Столбец2» найдено повторяющиеся данные, удаляется вся строка, включая данные из столбца «Столбец3».

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

Примечание: Если вы хотите вернуть данные, просто нажмите кнопку Отменить (или нажмите клавиши CTRL+Z на клавиатуре).

Как посчитать количество повторяющихся в столбце данных (Формулы/Formulas)

​ колонке А.​​ массива​2) Убрать дубликаты​IgMaxx​: Limyh, ну это​ раз это слово​ раз встречается в​
​ПОДСТАВИТЬ() использована для​ и каких именно​ различны.​ весь столбец, щелкнув​ =СЦЕПИТЬ(A15;» «;B15;» «;C15)​
​В строке «Форматировать​

​ «+», значит такую​​ A не меняются.​ ячеек с уникальными​ дубли словами, числами,​Формула убирающая повторы​​=ЕСЛИОШИБКА(ИНДЕКС(A$2:A$998;НАИМЕНЬШИЙ(ЕСЛИ(ЕСЛИ(ПОИСКПОЗ(A$2:A$998;A$1:A$998;)=СТРОКА(A$2:A$998);МУМНОЖ(—(СЧЁТЕСЛИМН(A$2:A$998;A$2:A$998;B$2:B$998;<"Framo";"Linear";"Marflex">)>0);<1:1:1>))=D$1;СТРОКА(A$2:A$998));СТРОКА(D1)));»»)​​ хороший инструмент и​
​, не понятно что​ ​ Вы уж слишком. ​ ​ встречается в диапазоне​
​ А1-А15​ того, чтобы убрать​

​ оценок по каждому​​Причем сумму повторяющихся​
​ его заголовок. В​
​Про функцию «СЦЕПИТЬ»​ формулу для определения​ фамилию уже написали.​

Счёт текстовых строк по условию

Похожее задание, но теперь нужно считать, сколько менеджеров продавали товар с кодом 1001.

количество уникальных строк с условием

А формула такая:

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

Это всё на сегодня, Задавайте ваши вопросы в комментариях. Понравилась статья – поделись с другом!

Добавить комментарий Отменить ответ

Как найти одинаковые значения в столбце Excel

​ он пишет ошибку​ пользователя это не​ Наберите в 2​После функции идет оператор​ – столбец B:​ теме. Однако,​ сумм (счетесли) без​ ячейки.​Таким образом, мы видим,​ слово​ функции.​ заметно отличается от​ и в прошлый​ только числовых значений​. После этого производим​

Как найти повторяющиеся значения в Excel?

​ способа можно производить​ ячеек, содержащих числовые​ #ДЕЛ/0!, я не​ ошибка в формуле.​ разных ячейках Москва​ сравнения количества найденных​Выделите диапазон B2:B9 и​здравствуйте​ диапазона-в зависимости от​ ​ что в Excel​«Мясо»​В поле​ предыдущих двух функций:​ раз, нам понадобится​ в выбранной колонке.​

Список закзов.

​ щелчок по кнопке​ подсчет только всех​ и текстовые данные.​ могу предсказать сколько​ Sh_Alex и Ваш​ и в третей​

​ значений в диапазоне​ выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное​

​.​ расположения диапазонов.Может быть​Повторяющиеся значения на рисунке​ существует целый ряд​. Знак​

  1. ​«Диапазон»​=СЧЁТЕСЛИ(диапазон;критерий)​ всего одно из​Создать правило.
  2. ​ Он игнорирует текстовые​«OK»​Формула.
  3. ​ заполненных значениями ячеек​ Сделать это можно​ будет данных в​ вариант не будет​ сравните их между​Зеленый.
  4. ​ с числом 1.​ форматирование»-«Создать правило».​Можно так​ пересечение. Сколько ещё​ выделены с помощью​ способов подсчитать количество​«<>»​

​тем же способом,​Аргумент​

​ них под названием​ значения и не​внизу данного окошка.​ и нельзя задавать​ просто взглянув на​ столбце и потому​ работать так, как​ собой =А1=В1. Должно​ То есть если​

​Вберете «Использовать формулу для​

Пример функции СЧЁТЕСЛИ и выделение повторяющихся значений

​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(C15=C14;»»;СЧЁТЕСЛИ($C$7:$C$58;C14))​ надо учиться в​ Условного форматирования (см. статью​ значений в столбце.​означает в Экселе​ который мы уже​«Диапазон»​«Значение1»​ включает их в​Мы переходим к окну​ условия подсчета.​ индикатор в строке​ увеличиваю его до​ не происходит сравнения​ получиться ИСТИНА. В​ больше чем одно​ определения форматируемых ячеек».​так легче Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(C8=C7;»»;СЧЁТЕСЛИ($C$7:C7;C7))​ excel.Спасибо форумчане!​ Выделение повторяющихся значений​ Выбор определенного варианта​«не равно»​ не раз описывали​представляется в виде​

​. Вводим в это​ общий итог. Данная​ аргументов функции​

​С помощью оператора​ состояния.​ 25000 тыс.​ между исходными данными​ любую добавьте пробел​ значение, значит формула​Чтобы найти повторяющиеся значения​если просят -​Serge_007​ в MS EXCEL).​ зависит от конкретных​

Как в Excel найти повторяющиеся ячейки (текст) и их количество (см)?

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

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

Мне нужно узнать статистику каждого повтора.

Т.е. это слово встречается 4 раза, это слово 3 раза, это 2 раза. и так далее. Так далее в смысле что четыре столбца не предел 🙂 В общем надеюсь задача понятна

Дополнительное условие (может упростит вам задачу) в каждом столбце все слова уникальны, повторов нет.

Как удалить

Чтобы удалить дубликаты в Excel можно воспользоваться следующими способами. Выделяем заполненные ячейки, переходим на вкладку «Данные» и нажимаем кнопочку «Удалить дубликаты».

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

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

Второй способ для удаления дубликатов – это использование фильтра. Выделяем нужные столбцы вместе с шапкой. Переходим на вкладку «Данные» и в группе «Сортировка и фильтр» нажимаем на кнопочку «Дополнительно».

В следующем окне в поле «Исходный диапазон» уже указаны ячейки. Отмечаем маркером пункт «скопировать результат в другое место» и в поле «Поместить результат в диапазон» указываем адрес одной ячейки, которая будет левой верхней в новой таблице. Ставим галочку в поле «Только уникальные записи» и нажимаем «ОК».

Будет создана новая таблица, в которой не будет строк с повторами информации.

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

Одинаковые значения в разных столбцах

Выполнить это действие можно при помощи функции ВПР, в которой прописываем:

=ВПР(Искомый объект; Список поиска ; номер столбца который нужно вернуть ;

Пример для работы Вы сможете скачать ниже.

В итоге получает третий столбец, показывающий, где находятся повторяющиеся значения из столбца Б в столбце А.

Использование функции ВПР в MS Excel

Важно! #Н/Д значит объект не найдет.

Как найти повторяющиеся значения в двух столбцах в Excel?

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

Быстро находите повторяющиеся значения в двух столбцах с помощью Kutools for Excelхорошая идея3

Быстро находите повторяющиеся значения в одном столбце с помощью Kutools for Excelхорошая идея3

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

Посмотрите на следующий снимок экрана, у меня есть столбец A (A2: A15) и столбец C (C2: C13), которые содержат несколько повторяющихся имен.

док найти дубликаты 1

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

1. В ячейку B2 введите эту формулу: = ЕСЛИ (ЕСТЬ ОШИБКА (ПОИСКПОЗ (A2; $ C $ 2: $ C $ 13,0)); «»; A2) .

док найти дубликаты 2

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

2, нажмите Enter ключ. Выберите ячейку B2 и перетащите маркер заполнения на ячейку B15. И все повторяющиеся имена будут отображаться в столбце B. См. Снимок экрана:

док найти дубликаты 3

Легко объединяйте столбцы или строки без потери данных в Excel

Найдите повторяющиеся значения в двух столбцах с кодом VBA

Следующий код VBA также может помочь вам найти повторяющиеся значения между двумя столбцами.

1. Нажмите разработчик > Визуальный Бейсик, Новый Окно Microsoft Visual Basic для приложений отобразится, щелкните Вставить > модуль, и введите в модуль следующий код:

Код VBA: найти дубликаты из двух столбцов

2. Нажмите док-умножить-расчет-3Чтобы выполнить код, в появившемся окне выберите Диапазон 1, который вы хотите сравнить.

док найти дубликаты 4

3. Нажмите OK и продолжайте выбирать Range 2, с которым вы будете сравнивать.

док найти дубликаты 5

4. Затем нажмите OK, а повторяющиеся значения как в столбце A, так и в столбце C были выбраны в столбце A.

док найти дубликаты 6

Внимание: Если вы хотите выбрать дубликаты из столбца C, вам просто нужно поменять местами два выбранных столбца.

Быстро находите повторяющиеся значения в двух столбцах с помощью Kutools for Excel

С помощью Kutools for Excel, вы можете быстро и удобно найти повторяющиеся значения.

После бесплатная установка Kutools for Excel, сделайте следующее:

Нажмите Kutools > Select > Select Same & Different Cells .

док найти дубликаты 10

1. В Select Same & Different Cells диалоговом окне выполните следующие операции:

(1.) Щелкните первую alt=»документ-заменить-диапазон-имена-7″ width=»32″ height=»19″ />кнопку, чтобы выбрать исходный столбец для сравнения, затем щелкните второй alt=»документ-заменить-диапазон-имена-7″ width=»32″ height=»19″ />кнопка для выбора сравниваемого столбца;

(2.) Выберите Each row под Based on раздел;

(3.) Затем выберите Те же ценности из Найдите раздел;

(4.) Наконец, вы можете выбрать один цвет фона или шрифта, чтобы выделить те же ячейки под Processing of resuts раздел как вам нужно.

док найти дубликаты 8

2. Затем нажмите OK, и одинаковые значения как в столбце A, так и в столбце B были выбраны в столбце A.

док найти дубликаты 9

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

Заметки:

  • My data has headers : Если сравниваемые данные имеют заголовки, вы можете выбрать этот параметр, и заголовки сравниваться не будут.
  • Select entire rows : С помощью этой опции будут выбраны все строки, содержащие одинаковые значения.
  • Case Sensitive : Отметив эту опцию, вы будете сравнивать столбцы с учетом регистра.
  • Два диапазона сравнения должны содержать одинаковое количество столбцов.
  • Если вы хотите выбрать дубликаты из столбца C, вам просто нужно поменять местами два выбранных столбца.

Демонстрация: быстрый поиск и выделение повторяющихся значений в двух столбцах

Быстро находите повторяющиеся значения в одном столбце с помощью Kutools for Excel

После установки Kutools for Excel, сделайте следующее: (Бесплатная загрузка Kutools for Excel прямо сейчас!)

Иногда вам может потребоваться найти повторяющиеся значения в столбце, Kutools for Excel также может помочь вам быстро справиться с этим.

1. Выберите столбец, в котором вы хотите найти повторяющиеся значения, нажмите Kutools > Select > Select Duplicate & Unique Cells .
док найти дубликаты 11

2. в Select Duplicate & Unique Cells диалог, проверьте Duplicates or All duplicates вариант, который вам нужен, и в то же время вы можете решить выделять дубликаты с цветом фона или шрифта или нет в Processing of results раздел.
док найти дубликаты 12

3. Нажмите Ok, появится диалоговое окно с напоминанием о количестве выбранных повторяющихся ячеек, щелкните OK чтобы закрыть его, и дубликаты будут выделены и выделены.
док найти дубликаты 13

1. Удаление повторяющихся значений в Excel (2007+)

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

удалить-дубликаты excel

Если в каждом столбце таблицы имеется заголовок, установить маркер Мои данные содержат заголовки. Также проставляем маркеры напротив тех столбцов, в которых требуется произвести поиск дубликатов.

удалить-дубликаты excel

Щелкаем ОК, диалоговое окно будет закрыто и строки, содержащие дубликаты будут удалены.

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

Формула для выборки всех одинаковых значений в столбце Excel

Dear, I am working on the attached Screenshot and Excel File. I need to calculate the Values in Column «G» i.e. to Count unique text values based on multiple (Two) criteria, but criteria are in NUMBERS. Further, Its is big sheet, therefore I want to use the cell reference in range and in criteria. I am very confused. I want to count the column C i.e. Degree based on the Criteria E and F. That is,I need to set formula in G2, such that, Look E2 in column A and Look F2 in column B and count the unique text values in Column C. Hope it is clear.

I am very thankful to you please

Details:
I have a data of more than 1000 companies with different year. As in column A, I have companies and B shows the year for which these companies have data. For example, First company have data for year 2011, 2012, 2013 and 2015. For company 2, I have data for year 2014 and 2011 and so on. Since majority of companies have data from 2011 to 2015, therefore I select this range in column F for each company. Now, I want to count the unique Degrees for cell G2, if there is 389841988 (Company ID for First company) in A and there is 2011 in Column B. Now I need to set formula in G2,in a way, If I will drag the formula G2 in cell G3, then It should give me the value by looking such as if there is 389841988 (Company ID for First company) in A and there is 2012 in Column B, the based on these two criteria, it should count the unique text in C2 and so on.
Look on the second sheet named Example, I did the same for INDEX, MATCH function and as well for SUMIFS function; in C2 I have the array formula INDEX($F:$H,MATCH(1,(A2=$F:$F)*(B2=$G:$G),0),3) and in D2 I have SUMIFS(I:I,F:F,A2,G:G,B2). These help me to do what I want with two criteria (To match and also to sum) and also I can drag these for rest of cell. I am looking something like this, or anything that I can drag to count unique degrees. Thank you very much. I am waiting.

Plz help. I am very thankful to for this kindness

Dear, I am working on the attached Screenshot and Excel File. I need to calculate the Values in Column «G» i.e. to Count unique text values based on multiple (Two) criteria, but criteria are in NUMBERS. Further, Its is big sheet, therefore I want to use the cell reference in range and in criteria. I am very confused. I want to count the column C i.e. Degree based on the Criteria E and F. That is,I need to set formula in G2, such that, Look E2 in column A and Look F2 in column B and count the unique text values in Column C. Hope it is clear.

I am very thankful to you please

Details:
I have a data of more than 1000 companies with different year. As in column A, I have companies and B shows the year for which these companies have data. For example, First company have data for year 2011, 2012, 2013 and 2015. For company 2, I have data for year 2014 and 2011 and so on. Since majority of companies have data from 2011 to 2015, therefore I select this range in column F for each company. Now, I want to count the unique Degrees for cell G2, if there is 389841988 (Company ID for First company) in A and there is 2011 in Column B. Now I need to set formula in G2,in a way, If I will drag the formula G2 in cell G3, then It should give me the value by looking such as if there is 389841988 (Company ID for First company) in A and there is 2012 in Column B, the based on these two criteria, it should count the unique text in C2 and so on.
Look on the second sheet named Example, I did the same for INDEX, MATCH function and as well for SUMIFS function; in C2 I have the array formula INDEX($F:$H,MATCH(1,(A2=$F:$F)*(B2=$G:$G),0),3) and in D2 I have SUMIFS(I:I,F:F,A2,G:G,B2). These help me to do what I want with two criteria (To match and also to sum) and also I can drag these for rest of cell. I am looking something like this, or anything that I can drag to count unique degrees. Thank you very much. I am waiting.

Поиск дублей в Excel

Как в excel сделать поиск одинаковых значений в столбцах?

Спросите у SEO-шника без чего он, как без рук! Он наверняка ответит: без Excel! Эксель – лучший друг и помощник и для специалиста в SEO, и для вебмастера.

Одна из задач, которую тебе точно придётся решать при работе с большими массивами данных – это поиск дублей в Excel. Не вариант проверять тысячи ячеек руками – угробишь на это часы и выйдешь с работы, пошатываясь, будто пьяный. Я предложу тебе 2 способа, как выполнить эту работу в десяток раз быстрее. Они дают немного разные результаты, но в равной степени просты.

Как в Эксель найти повторяющиеся значения?

Для примера я распределил фамилии прославленных футболистов российской эпохи в пару столбцов. Нарочно сделал повторы в столбиках (иллюстрации кликабельны).

Как в excel сделать поиск одинаковых значений в столбцах?

  • Наша цель – найти повторы в столбцах Excel и выделить их цветом.
  • Действуем так:

Шаг №1. Выделяем весь диапазон.

Шаг №2. Кликаем на раздел «Условное форматирование» в главной вкладке.

Как в excel сделать поиск одинаковых значений в столбцах?

Шаг №3. Наводим на пункт «Правила выделения ячеек» и в появившемся списке выбираем «Повторяющиеся значения».

Как в excel сделать поиск одинаковых значений в столбцах?

Шаг №4. Возникнет окно. Вам нужно выбрать, хотите ли вы подсветить повторяющиеся или уникальные значения. Также можно установить цвета заливки и текста.

Как в excel сделать поиск одинаковых значений в столбцах?

Нажмите «ОК», и вы обнаружите: одинаковые ячейки в двух столбиках теперь выделены! Как видите, это вопрос 30 секунд.

Читайте также:  Расчет среднего заработка для командировки в Excel скачать

Описанный вариант – самый удобный для пользователей Эксель версий 2013 и 2016.

Как вычислить повторы при помощи сводных таблиц

Метод хорош тем, что мы не только определяем повторяющиеся значения в Excel, но и пересчитываем их. Причём делаем это за считанные минуты. Правда, есть и минус – столбец с данными может быть всего один.

Вернёмся к нашим баранам футболистам. Я оставил один столбик, добавив в него ячейки-дубли, а также дописал заглавную строку (это обязательно).

Как в excel сделать поиск одинаковых значений в столбцах?

Далее делаем следующее:

Шаг 1. В ячейках напротив фамилий проставляем единички. Вот так:

Как в excel сделать поиск одинаковых значений в столбцах?

Шаг 2. Переходим в раздел «Вставка» главного меню и в блоке «Таблицы» выбираем «Сводная таблица».

Как в excel сделать поиск одинаковых значений в столбцах?

Откроется окно «Создание сводной таблицы». Здесь нужно выбрать диапазон данных для анализа (1), указать, куда поместить отчёт (2) и нажать «ОК».

Как в excel сделать поиск одинаковых значений в столбцах?

Только не ставьте галку напротив «Добавить эти данные в модель данных». Иначе Эксель начнёт формировать модель, и это парализует ваш комп на пару минут минимум.

Шаг 3. Распределите поля сводной таблицы следующим образом: первое поле (в моём случае «Футболисты») – в область «Строки», второе («Значение2») – в область «Значения». Используйте обычное перетаскивание (drag-and-drop).

Как в excel сделать поиск одинаковых значений в столбцах?

  1. Должно получиться так:

А на листе сформируется сама сводка – уже без дублированных ячеек. Зато во втором столбике будет указано, сколько ячеек-дублей с конкретным содержанием было обнаружено в первом столбике (например, Онопко – 2 шт.).

Этот метод «на бумаге» может выглядеть несколько замороченным, но уверяю: попробуете раз-два, набьёте руку, а потом все операции будете выполнять за минуту.

Заключение

При поиске дублей я, признаться, всегда пользуюсь первым из описанных мною способов – то есть действую через «Условное форматирование». Уж очень меня подкупает предельная простота этого метода.

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

Помогли ли тебе мои методы работы с данными? Или ты знаешь лучше? Поделись своим мнением в х!

Кликни на звездочку, чтобы оценить его!

Средний рейтинг 3.6 / 5. Итог: 5

Поиск в Excel по нескольким критериям

Функция ВПР в Excel – это действительно мощный инструмент для выполнения поиска определённого значения в базе данных. Однако, есть существенное ограничение – её синтаксис позволяет искать только одно значение. Как же быть, если требуется выполнить поиск по нескольким условиям? Решение Вы найдёте далее.

Пример 1: Поиск по 2-м разным критериям

Предположим, у нас есть список заказов и мы хотим найти Количество товара (Qty.), основываясь на двух критериях – Имя клиента (Customer) и Название продукта (Product). Дело усложняется тем, что каждый из покупателей заказывал несколько видов товаров, как это видно из таблицы ниже:

Как в excel сделать выборку из таблицы по условию?

  • Обычная функция ВПР не будет работать по такому сценарию, поскольку она возвратит первое найденное значение, соответствующее заданному искомому значению. Например, если Вы хотите узнать количество товара Sweets, заказанное покупателем Jeremy Hill, запишите вот такую формулу:
  • =VLOOKUP(B1,$A$5:$C$14,3,FALSE)
    =ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
  • – эта формула вернет результат 15, соответствующий товару Apples, так как это первое совпадающее значение.

Есть простой обходной путь – создать дополнительный столбец, в котором объединить все нужные критерии.

В нашем примере это столбцы Имя клиента (Customer) и Название продукта (Product).

Не забывайте, что объединенный столбец должен быть всегда крайним левым в диапазоне поиска, поскольку именно левый столбец функция ВПР просматривает при поиске значения.

Итак, Вы добавляете вспомогательный столбец в таблицу и копируете по всем его ячейкам формулу вида: =B2&C2. Если хочется, чтобы строка была более читаемой, можно разделить объединенные значения пробелом: =B2&” “&C2. После этого можно использовать следующую формулу:

  1. =VLOOKUP(«Jeremy Hill Sweets»,$A$7:$D$18,4,FALSE)
    =ВПР(«Jeremy Hill Sweets»;$A$7:$D$18;4;ЛОЖЬ)
  2. или
  3. =VLOOKUP(B1,$A$7:$D$18,4,FALSE)
    =ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)

Где ячейка B1 содержит объединенное значение аргумента lookup_value (искомое_значение), а 4 – аргумент col_index_num (номер_столбца), т.е. номер столбца, содержащего данные, которые необходимо извлечь.

Как в excel сделать выборку из таблицы по условию?

Пример 2: ВПР по двум критериям с просматриваемой таблицей на другом листе

Если Вам необходимо обновить основную таблицу (Main table), добавив данные из второй таблицы (Lookup table), которая находится на другом листе или в другой рабочей книге Excel, то Вы можете собрать искомое значение непосредственно в формуле, которую вставляете в основную таблицу.

Как и в предыдущем примере, Вам понадобится в таблице поиска (Lookup table) вспомогательный столбец с объединенными значениями. Этот столбец должен быть крайним левым в заданном для поиска диапазоне.

Итак, формула с ВПР может быть такой:

Здесь в столбцах B и C содержатся имена клиентов и названия продуктов соответственно, а ссылка Orders!$A&$2:$D$2 определяет таблицу для поиска на другом листе.

Чтобы сделать формулу более читаемой, Вы можете задать имя для просматриваемого диапазона, и тогда формула станет выглядеть гораздо проще:

Как в excel сделать выборку из таблицы по условию?

Чтобы формула работала, значения в крайнем левом столбце просматриваемой таблицы должны быть объединены точно так же, как и в критерии поиска. На рисунке выше мы объединили значения и поставили между ними пробел, точно так же необходимо сделать в первом аргументе функции (B2&” “&C2).

Запомните! Функция ВПР ограничена 255 символами, она не может искать значение, состоящее из более чем 255 символов. Имейте это ввиду и следите, чтобы длина искомого значения не превышала этот лимит.

Соглашусь, добавление вспомогательного столбца – не самое изящное и не всегда приемлемое решение. Вы можете сделать то же самое без вспомогательного столбца, но в таком случае потребуется гораздо более сложная формула с комбинацией функций INDEX (ИНДЕКС) и MATCH (ПОИСКПОЗ).

Извлекаем 2-е, 3-е и т.д. значения, используя ВПР

Вы уже знаете, что ВПР может возвратить только одно совпадающее значение, точнее – первое найденное. Но как быть, если в просматриваемом массиве это значение повторяется несколько раз, и Вы хотите извлечь 2-е или 3-е из них? А что если все значения? Задачка кажется замысловатой, но решение существует!

Предположим, в одном столбце таблицы записаны имена клиентов (Customer Name), а в другом – товары (Product), которые они купили. Попробуем найти 2-й, 3-й и 4-й товары, купленные заданным клиентом.

Простейший способ – добавить вспомогательный столбец перед столбцом Customer Name и заполнить его именами клиентов с номером повторения каждого имени, например, John Doe1, John Doe2 и т.д. Фокус с нумерацией сделаем при помощи функции COUNTIF (СЧЁТЕСЛИ), учитывая, что имена клиентов находятся в столбце B:

Как в excel сделать выборку из таблицы по условию?

После этого Вы можете использовать обычную функцию ВПР, чтобы найти нужный заказ. Например:

  • Находим 2-й товар, заказанный покупателем Dan Brown:
    =VLOOKUP(«Dan Brown2»,$A$2:$C$16,3,FALSE)
    =ВПР(«Dan Brown2»;$A$2:$C$16;3;ЛОЖЬ)
  • Находим 3-й товар, заказанный покупателем Dan Brown:
    =VLOOKUP(«Dan Brown3»,$A$2:$C$16,3,FALSE)
    =ВПР(«Dan Brown3»;$A$2:$C$16;3;ЛОЖЬ)

На самом деле, Вы можете ввести ссылку на ячейку в качестве искомого значения вместо текста, как представлено на следующем рисунке:

Как в excel сделать выборку из таблицы по условию?

  • Если Вы ищите только 2-е повторение, то можете сделать это без вспомогательного столбца, создав более сложную формулу:
  • =IFERROR(VLOOKUP($F$2,INDIRECT(«$B

    Выборка значений из таблицы Excel по условию

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

    Чтобы определить соответствующие значение первому наименьшему числу нужна выборка из таблицы по условию. Допустим мы хотим узнать первый самый дешевый товар на рынке из данного прайса:

    Как в excel сделать выборку из таблицы по условию?

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

    В месте «диапазон_данных_для_выборки» следует указать область значений A6:A18 для выборки из таблицы (например, текстовых), из которых функция ИНДЕКС выберет одно результирующие значение.

    Аргумент «диапазон» означает область ячеек с числовыми значениями, из которых следует выбрать первое наименьшее число.

    В аргументе «заголовок_столбца» для второй функции СТРОКА, следует указать ссылку на ячейку с заголовком столбца, который содержит диапазон числовых значений.

    Естественно эту формулу следует выполнять в массиве. Поэтому для подтверждения ее ввода следует нажимать не просто клавишу Enter, а целую комбинацию клавиш CTRL+SHIFT+Enter. Если все сделано правильно в строке формул появятся фигурные скобки.

    Обратите внимание ниже на рисунок, где в ячейку B3 была введена данная формула в массиве:

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

    Как в excel сделать выборку из таблицы по условию?

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

    Ключевую роль здесь играет функция ИНДЕКС. Ее номинальное задание – это выбирать из исходной таблицы (указывается в первом аргументе – A6:A18) значения соответственные определенным числам.

    ИНДЕКС работает с учетом критериев определённых во втором (номер строки внутри таблицы) и третьем (номер столбца в таблице) аргументах.

    Так как наша исходная таблица A6:A18 имеет только 1 столбец, то третий аргумент в функции ИНДЕКС мы не указываем.

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

    Функция ЕСЛИ позволяет выбрать значение из списка по условию. В ее первом аргументе указано где проверяется каждая ячейка в диапазоне B6:B18 на наличие наименьшего числового значения: ЕСЛИB6:B18=МИНB6:B18.

    Таким способом в памяти программы создается массив из логических значений ИСТИНА и ЛОЖЬ.

    В нашем случаи 3 элемента массива будут содержат значение ИСТИНА, так как минимальное значение 8 содержит еще 2 дубликата в столбце B6:B18.

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

    Реализовывается данная задача с помощью функции СТРОКА, она заполняет элементы массива в памяти программы номерами строк листа. Но сначала от всех этих номеров вычитается номер на против первой строки таблицы – B5, то есть число 5.

    Это делается потому, что функция ИНДЕКС работает с номерами внутри таблицы, а не с номерами рабочего листа Excel. В тоже время функция СТРОКА умеет возвращать только номера строк листа.

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

    После того как будут отобраны все минимальные значения и сопоставлены все номера строк таблицы функция МИН выберет наименьший номер строки.

    Эта же строка будет содержать первое наименьшее число, которое встречается в столбце B6:B18. На основании этого номера строки функции ИНДЕКС выберет соответствующее значение из таблицы A6:A18.

    В итоге формула возвращает это значение в ячейку B3 в качестве результата вычисления.

    Как выбрать значение с наибольшим числом в Excel

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

    Как в excel сделать выборку из таблицы по условию?

    Если необходимо изменить условия формулы так, чтобы можно было в Excel выбрать первое максимальное, но меньше чем 70:

    Как в excel сделать выборку из таблицы по условию?

    Как в Excel выбрать первое минимальное значение кроме нуля:

    Как в excel сделать выборку из таблицы по условию?

    Как легко заметить, эти формулы отличаются между собой только функциями МИН и МАКС и их аргументами.

    Скачать пример выборки из таблицы в Excel.

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

    #187;&(MATCH($F$2,Table4[Customer Name],0)+2)&»:$C16″),2,FALSE),»»)
    =ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ(«$B

    Запрос на выборку данных (формулы) в MS EXCEL

    Суть запроса на выборку – выбрать из исходной таблицы строки, удовлетворяющие определенным критериям (подобно применению стандартного Фильтра). Произведем отбор значений из исходной таблицы с помощью формул массива. В отличие от применения Фильтра (CTRL+SHIFT+L или ) отобранные строки будут помещены в отдельную таблицу.

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

    1. Один числовой критерий (Выбрать те Товары, у которых цена выше минимальной)

    Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист Один критерий — число).

    Как в excel сделать выборку из таблицы по условию?

    Необходимо отобразить в отдельной таблице только те записи (строки) из Исходной таблицы, у которых цена выше 25.

    Решить эту и последующие задачи можно легко с помощью стандартного фильтра. Для этого выделите заголовки Исходной таблицы и нажмите CTRL+SHIFT+L. Через выпадающий список у заголовка Цены выберите Числовые фильтры…, затем задайте необходимые условия фильтрации и нажмите ОК.

    Как в excel сделать выборку из таблицы по условию?

    Будут отображены записи удовлетворяющие условиям отбора.

    Как в excel сделать выборку из таблицы по условию?

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

    Критерий (минимальную цену) разместим в ячейке Е6, таблицу для отфильтрованных данных — в диапазоне D10:E19.

    Как в excel сделать выборку из таблицы по условию?

    Теперь выделим диапазон D11:D19 (столбец Товар) и в Строке формул введем формулу массива:

    =ИНДЕКС(A11:A19;НАИМЕНЬШИЙ(ЕСЛИ($E$6=$B$13:$B$21 гарантирует, что будут отобраны даты не позже заданной (включая). Условие $B$13:$B$21>0 необходимо, если в диапазоне дат имеются пустые ячейки. Знак * (умножение) используется для задания Условия И (все 3 критерия должны выполняться для строки одновременно).

    Примечание. Случай, когда список несортирован, рассмотрен в статье Поиск ДАТЫ (ЧИСЛА) ближайшей к заданной, с условием в MS EXCEL. Несортированный список.

    7. Один Текстовый критерий (Выбрать Товары определенного вида)

    Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист Один критерий — Текст).

    Задача решается аналогично Задачам 1 и 3. Более подробное решение см. в статье Поиск ТЕКСТовых значений в MS EXCEL с выводом их в отдельный список. Часть1. Обычный поиск.

    8. Два Текстовых критерия (Выбрать Товары определенного вида, поставленные в заданный месяц)

    Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист 2 критерия — текст (И)).

    • Для отбора строк используется формула массива:
    • =ИНДЕКС($A$11:$A$19;НАИМЕНЬШИЙ(ЕСЛИ(($F$6=$A$11:$A$19)*($F$7=$B$11:$B$19);СТРОКА($A$11:$A$19)-СТРОКА($A$10);30);СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК($A$11:$A$19)))))
    • Выражение ($F$6=$A$11:$A$19)*($F$7=$B$11:$B$19) задает оба условия (Товар и Месяц).

    Выражение СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК($A$11:$A$19))) формирует массив последовательных чисел <1:2:3:4:5:6:7:8:9>, т.е. номера строк в таблице.

    9. Два Текстовых критерия (Выбрать Товары определенных видов)

    Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист 2 критерия — текст (ИЛИ)).

    1. В отличие от Задачи 7 отберем строки с товарами 2-х видов (Условие ИЛИ).
    2. Для отбора строк используется формула массива:
    3. =ИНДЕКС(A$11:A$19;НАИБОЛЬШИЙ((($E$6=$A$11:$A$19)+($E$7=$A$11:$A$19))*(СТРОКА($A$11:$A$19)-СТРОКА($A$10)); СЧЁТЕСЛИ($A$11:$A$19;$E$6)+СЧЁТЕСЛИ($A$11:$A$19;$E$7)-ЧСТРОК($A$11:A11)+1))

    Условие ($E$6=$A$11:$A$19)+($E$7=$A$11:$A$19) гарантирует, что будут отобраны товары только заданных видов из желтых ячеек (Товар2 и Товар3). Знак + (сложение) используется для задания Условие ИЛИ (должен быть выполнен хотя бы 1 критерий).

    Вышеуказанное выражение вернет массив <0:0:0:0:1:1:1:0:0>. Умножив его на выражение СТРОКА($A$11:$A$19)-СТРОКА($A$10), т.е. на массив последовательных чисел <1:2:3:4:5:6:7:8:9>, получим массив позиций (номеров строк таблицы), удовлетворяющих критериям. В нашем случае это будет массив <0:0:0:0:5:6:7:0:0>.

    С помощью функции НАИБОЛЬШИЙ() выведем 3 значения из позиции 5 (строка 15 листа), 6 (16) и 7 (17), т.е. значения Товар2, Товар2 и Товар3.

    Для этого используем выражение СЧЁТЕСЛИ($A$11:$A$19;$E$6)+СЧЁТЕСЛИ($A$11:$A$19;$E$7)-ЧСТРОК($A$11:A11)+1, которое последовательно (начиная со строки 11) будет возвращать числа 3; 2; 1; 0; -1; -2; … Формула НАИБОЛЬШИЙ(…;3) вернет число 5, НАИБОЛЬШИЙ(…

    ;2) вернет число 6, НАИБОЛЬШИЙ(…;1) вернет число 7, а НАИБОЛЬШИЙ(…;0) и далее вернет ошибку, которую мы скроем условным форматированием.

    И наконец, с помощью функции ИНДЕКС() последовательно выведем наши значения из соответствующих позиций: =ИНДЕКС(A$11:A$19;5) вернет Товар2, =ИНДЕКС(A$11:A$19;6) вернет Товар2, =ИНДЕКС(A$11:A$19;7) вернет Товар3.

    10. Отбор значений с учетом повторов

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

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

    Предположим, что нас интересует сколько и каких партий товаров поставлялось по цене от 1000р. до 2000р. (критерий 1). Причем, партий с одинаковой ценой должно быть минимум 3 (критерий 2).

    1. Решением является формула массива:
    2. =НАИМЕНЬШИЙ(СТРОКА($A$14:$A$27)*($C$14:$C$27>=$B$7)*($C$14:$C$27=$B$10);F14+($G$8-$G$9))
    3. Эта формула возвращает номера строк, которые удовлетворяют обоим критериям.
    4. Формула =СУММПРОИЗВ(($C$14:$C$27>=$B$7)*($C$14:$C$27=$B$10)) подсчитывает количество строк, которые удовлетворяют критериям.

    В файле примера на листе «10.Критерий — колич-во повторов» настроено Условное форматирование, которое позволяет визуально определить строки удовлетворяющие критериям, а также скрыть ячейки, в которых формула массива возвращает ошибку #ЧИСЛО!

    11. Используем значение критерия (Любой) или (Все)

    В фильтре Сводных таблиц MS EXCEL используется значение (Все), чтобы вывести все значения столбца. Другими словами, в выпадающем списке значений критерия содержится особое значение, которое отменяет сам критерий (см. статью Отчеты в MS EXCEL, Отчет №3).

    В файле примера на листе «11. Критерий Любой или (Все)» реализован данный вариант критерия.

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

    • =ЕСЛИ($C$8=»(Все)»;НАИМЕНЬШИЙ((СТРОКА($B$13:$B$26)-СТРОКА($B$12))*($D$13:$D$26>=$D$8);F13+($G$6-$G$7));
    • НАИМЕНЬШИЙ((СТРОКА($B$13:$B$26)-СТРОКА($B$12))*($D$13:$D$26>=$D$8)*($C$13:$C$26=$C$8);F13+($G$6-$G$7)))
    • Остальная часть формулы аналогична рассмотренным выше.

    #187;&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&»:$C16″);2;ИСТИНА);»»)

  • В этой формуле:
  • $F$2 – ячейка, содержащая имя покупателя (она неизменна, обратите внимание – ссылка абсолютная);
  • $B$ – столбец Customer Name;
  • Table4 – Ваша таблица (на этом месте также может быть обычный диапазон);
  • $C16 – конечная ячейка Вашей таблицы или диапазона.

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

Как в excel сделать выборку из таблицы по условию?

Если Вам нужен список всех совпадений – функция ВПР тут не помощник, поскольку она возвращает только одно значение за раз – и точка. Но в Excel есть функция INDEX (ИНДЕКС), которая с легкостью справится с этой задачей. Как будет выглядеть такая формула, Вы узнаете в следующем примере.

Извлекаем все повторения искомого значения

  1. Как упоминалось выше, ВПР не может извлечь все повторяющиеся значения из просматриваемого диапазона.

Введите эту формулу массива в несколько смежных ячеек, например, в ячейки F4:F8, как показано на рисунке ниже. Количество ячеек должно быть равным или большим, чем максимально возможное число повторений искомого значения. Не забудьте нажать Ctrl+Shift+Enter, чтобы правильно ввести формулу массива.

Как в excel сделать выборку из таблицы по условию?

Если Вам интересно понять, как она работает, давайте немного погрузимся в детали формулы:

Часть 1:

$F$2=B2:B16 – сравниваем значение в ячейке F2 с каждым из значений диапазона B2:B16. Если найдено совпадение, то выражение СТРОКА(C2:C16)-1 возвращает номер соответствующей строки (значение -1 позволяет не включать строку заголовков). Если совпадений нет, функция IF (ЕСЛИ) возвращает пустую строку.

Результатом функции IF (ЕСЛИ) окажется вот такой горизонтальный массив:

Часть 2:

Здесь функция ROW (СТРОКА) действует как дополнительный счётчик. Так как формула скопирована в ячейки F4:F9, мы вычитаем число 3 из результата функции, чтобы получить значение 1 в ячейке F4 (строка 4, вычитаем 3), чтобы получить 2 в ячейке F5 (строка 5, вычитаем 3) и так далее.

Часть 3:

Функция SMALL (НАИМЕНЬШИЙ) возвращает n-ое наименьшее значение в массиве данных. В нашем случае, какую по счёту позицию (от наименьшего) возвращать – определено функцией ROW (СТРОКА) (смотри Часть 2).

Так, для ячейки F4 функция НАИМЕНЬШИЙ(<массив>;1) возвращает 1-й (наименьший) элемент массива, то есть 1.

Для ячейки F5 возвращает 2-й наименьший элемент массива, то есть 3, и так далее.

Часть 4:

Функция INDEX (ИНДЕКС) просто возвращает значение определённой ячейки в массиве C2:C16. Для ячейки F4 функция ИНДЕКС($C$2:$C$16;1) возвратит Apples, для F5 функция ИНДЕКС($C$2:$C$16;3) возвратит Sweets и так далее.

Часть 5:

В завершение, мы помещаем формулу внутрь функции IFERROR (ЕСЛИОШИБКА), поскольку вряд ли Вас обрадует сообщение об ошибке #N/A (#Н/Д) в случае, если количество ячеек, в которые скопирована формула, будет меньше, чем количество повторяющихся значений в просматриваемом диапазоне.

Двумерный поиск по известным строке и столбцу

Выполнение двумерного поиска в Excel подразумевает поиск значения по известному номеру строки и столбца. Другими словами, Вы извлекаете значение ячейки на пересечении конкретной строки и столбца.

Итак, давайте обратимся к нашей таблице и запишем формулу с функцией ВПР, которая найдет информацию о стоимости проданных в марте лимонов.

Как в excel сделать выборку из таблицы по условию?

Существует несколько способов выполнить двумерный поиск. Познакомьтесь с возможными вариантами и выберите наиболее подходящий.

Функции ВПР и ПОИСКПОЗ

Вы можете использовать связку из функций ВПР (VLOOKUP) и ПОИСКПОЗ (MATCH), чтобы найти значение на пересечении полей Название продукта (строка) и Месяц (столбец) рассматриваемого массива:

Формула выше – это обычная функция ВПР, которая ищет точное совпадение значения “Lemons” в ячейках от A2 до A9. Но так как Вы не знаете, в каком именно столбце находятся продажи за март, то не сможете задать номер столбца для третьего аргумента функции ВПР. Вместо этого используется функция ПОИСКПОЗ, чтобы определить этот столбец.

В переводе на человеческий язык, данная формула означает:

  • Ищем символы “Mar” – аргумент lookup_value (искомое_значение);
  • Ищем в ячейках от A1 до I1 – аргумент lookup_array (просматриваемый_массив);
  • Возвращаем точное совпадение – аргумент match_type (тип_сопоставления).

Использовав в третьем аргументе, Вы говорите функции ПОИСКПОЗ искать первое значение, в точности совпадающее с искомым значением. Это равносильно значению FALSE (ЛОЖЬ) для четвёртого аргумента ВПР.

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

Функция СУММПРОИЗВ

Функция СУММПРОИЗВ (SUMPRODUCT) возвращает сумму произведений выбранных массивов:

Функции ИНДЕКС и ПОИСКПОЗ

В следующей статье я буду объяснять эти функции во всех деталях, так что сейчас можете просто скопировать эту формулу:

Именованные диапазоны и оператор пересечения

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

Как в excel сделать выборку из таблицы по условию?

  1. Выделите таблицу, откройте вкладку Formulas (Формулы) и нажмите Create from Selection (Создать из выделенного).
  2. Отметьте галочками Top row (в строке выше) и Left column (в столбце слева). Microsoft Excel назначит имена диапазонам из значений в верхней строке и левом столбце Вашей таблицы. Теперь Вы можете осуществлять поиск, используя эти имена, напрямую, без создания формул.
  3. В любой пустой ячейке запишите =имя_строки имя_столбца, например, так:

=Lemons Mar
… или наоборот:

=Mar Lemons

Формула для выборки всех одинаковых значений в столбце Excel

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

Содержание

  • 1. Найдите и выделите дубликаты
    • 1.1 Найдите дубликаты с формулой
    • 2.1 Отфильтровать дубликаты с условным форматированием
    • 2.2 Отфильтруйте дубликаты с помощью вспомогательного столбца
    • 3.1 Подсчитайте повторяющиеся значения с критериями
    • 4.1 Удалить дубликаты, кроме одного в столбце

    1. Найдите и выделите дубликаты

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

    1.1 Найдите дубликаты с помощью формулы

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

    1.1.1 Найдите повторяющиеся ячейки в одном столбце с помощью формулы

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

    2. Введите формулу = СЧЁТЕСЛИ ($ C $ 3: $ C $ 12; C3)> 1, и нажмите Enter ключ.

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

    Ноты: В формуле = СЧЁТЕСЛИ ($ C $ 3: $ C $ 12, C3)> 1,
    (1) 3 канадских доллара: 12 канадских долларов — это столбец или список, в котором вы найдете повторяющиеся значения. Поскольку столбец статичен, когда мы копируем формулу в другие ячейки, обычно это абсолютная ссылка со знаком «$».
    (2) C3 — первая ячейка указанного столбца. Это относительная ссылка, потому что нам нужно, чтобы она изменялась автоматически, когда мы копируем формулу в другие ячейки.
    (3) Эта формула вернет ИСТИНА or ФАЛАЗА. ИСТИНА означает, что соответствующее значение дублируется, а НЕПРАВДА указывает, что соответствующее значение уникально в столбце.
    (4) Иногда ИСТИНА или ЛОЖЬ не могут быть поняты интуитивно. Мы можем объединить исходную формулу и функцию ЕСЛИ, чтобы вернуть Дубликаты непосредственно.
    = ЕСЛИ (СЧЁТЕСЛИ ($ C $ 3: $ C $ 12; C3)> 1, «Дубликаты», «»)

    1.1.2 Найдите повторяющиеся ячейки в двух столбцах с помощью формулы

    В некоторых случаях вам нужно сравнить два столбца и найти повторяющиеся значения. Например, у вас есть два списка имен, и вы хотите найти повторяющиеся имена во втором списке по сравнению с первым. вы можете применить функции ВПР и ЕСЛИОШИБКА, чтобы сделать это легко.

    1. Выберите пустую ячейку помимо второго списка имен.

    2. Введите формулу = ЕСЛИОШИБКА (ВПР (D3; $ B $ 3: $ B $ 18,1,0); «»), и нажмите Enter ключ.

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

    Ноты: В приведенной выше формуле
    (1) D3 — первая ячейка во втором списке имен. Ссылка должна изменяться автоматически, когда мы копируем формулу в другие ячейки, в результате она относительна.
    (2) 3 млрд долларов: 18 млрд долларов это первый список имен. Это абсолютная ссылка, потому что диапазон должен оставаться статическим, когда мы копируем формулу в другие ячейки.
    (3) Когда имя дублируется с именами в первом списке, формула вернет имя; в противном случае возвращается пустое значение.
    (4) Вы также можете применить формулу = ЕСЛИ (СЧЁТЕСЛИ ($ B $ 3: $ B $ 18; D3)> 0, «Дубликаты», «») найти повторяющиеся имена во втором списке по сравнению с первым списком. Эта формула вернет «Дубликаты» если соответствующее имя повторяется.

    (5) Если вам нужно найти дубликаты в двух столбцах на двух листах, вам просто нужно добавить имя листа перед ссылкой на сравниваемый столбец. В нашем примере просто замените в формуле $ B $ 3: $ B $ 18 на Sheet1! $ B $ 3: $ B $ 18.

    1.1.3 Поиск повторяющихся ячеек с учетом регистра с помощью формулы

    Формулы, представленные выше, не соответствуют регистру при поиске дубликатов, говорят, что они считают «яблоко» дубликатом с «ЯБЛОКО». Здесь вы можете применить формулу массива, чтобы найти повторяющиеся значения в одном столбце с совпадающими регистрами.

    1. Выберите пустую ячейку рядом с столбцом, в котором вы найдете дубликаты.

    2. Введите формулу массива = ЕСЛИ (СУММ ((- ТОЧНО ($ C $ 3: $ C $ 12, C3))) <= 1, «», «Дублировать»), и нажмите Ctrl + Shift + Enter ключи.

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

    Ноты: В приведенной выше формуле массива
    (1) 3 канадских доллара: 12 канадских долларов — столбец, в котором нужно найти повторяющиеся значения. Ссылка является абсолютной, потому что ссылка статическая при копировании формулы массива в другие ячейки.
    (2) C4 — первая ячейка в столбце. Ссылка является относительной, потому что ссылка должна автоматически изменяться при копировании формулы массива в другие ячейки.
    (3) Если соответствующая ячейка дублируется, формула массива вернет «Дубликат», в противном случае возвращается пустое значение.

    1.2 Найдите и выделите дубликаты с условным форматированием

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

    1.2.1 Поиск и выделение повторяющихся ячеек с условным форматированием

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

    1. Выберите столбец, в котором вы будете выделять повторяющиеся ячейки.

    2. Нажмите Главная > Условное форматирование > Выделите правила ячеек > Дублирующие значения. Смотрите скриншот ниже:

    3. В появившемся диалоговом окне повторяющихся значений выберите Дублировать в первом раскрывающемся списке выберите сценарий выделения из второго раскрывающегося списка и щелкните OK кнопку.

    Заметки: Если предустановленные сценарии выделения не могут удовлетворить ваши потребности, вы можете выбрать Пользовательский формат из второго раскрывающегося списка, а затем выберите цвет выделения, шрифт и границы ячеек по мере необходимости в появившемся диалоговом окне «Формат ячеек».

    Затем вы увидите, что все повторяющиеся ячейки выделены в выбранном столбце, как показано ниже.

    Заметки:
    (1) Выделив повторяющиеся ячейки, мы можем легко отфильтровать их. (Нажмите, чтобы посмотреть, как)
    (2) После выделения повторяющихся ячеек мы также можем легко удалить дубликаты в большом количестве. (Нажмите, чтобы посмотреть, как)

    1.2.2 Поиск и выделение строк на основе повторяющихся ячеек

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

    1. Выберите диапазон (исключая строку заголовка), в котором вы будете выделять строки на основе повторяющихся ячеек.

    2. Нажмите Главная > Условное форматирование > Новое правило.

    3. В диалоговом окне «Новое правило форматирования»
    (1) Щелкните, чтобы выбрать Используйте формулу, чтобы определить, какие ячейки следует форматировать. вариант;
    (2) Введите формулу = СЧЁТЕСЛИ ($ C $ 3: $ C $ 12, $ C3)> 1 в Формат значений, где эта формула истинна коробка;
    Советы: В формуле 3 канадских доллара: 12 канадских долларов столбец, содержащий повторяющиеся ячейки, и $ C3 — первая ячейка в столбце.
    (3) Щелкните значок Формат кнопку.

    4. В диалоговом окне «Формат ячеек» укажите необходимый цвет заливки, шрифт, границы ячеек и щелкните значок OK кнопки последовательно, чтобы сохранить настройки.

    Пока что в выбранном диапазоне строки выделяются на основе повторяющихся ячеек в указанном столбце. Смотрите скриншот:

    1.2.3 Найдите и выделите повторяющиеся строки с условным форматированием

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

    1. Выберите диапазон, кроме строки заголовка.

    2. Нажмите Главная > Условное форматирование > Новое правило.

    3. В диалоговом окне «Новое правило форматирования»:
    (1) Щелкните, чтобы выбрать Используйте формулу, чтобы определить, какие ячейки следует форматировать. вариант;
    (2) В Формат значений, где эта формула истинна поле, введите формулу =COUNTIFS($B$3:$B$12,$B3,$C$3:$C$12,$C3,$D$3:$D$12,$D3)>1;
    (3) Щелкните значок Формат кнопку.

    Заметки: В формуле =COUNTIFS($B$3:$B$12,$B3,$C$3:$C$12,$C3,$D$3:$D$12,$D3)>1:
    (1) 3 млрд долларов: 12 млрд долларов — это первый столбец в диапазоне, а $ B3 это первая ячейка в этом столбце;
    (2) 3 канадских доллара: 12 канадских долларов — второй столбец в диапазоне, а $ C3 это первая ячейка в столбце;
    (3) $ D $ 3: $ D $ 12 — третий столбец диапазона, а $ D3 это первая ячейка в столбце;
    (4) Если в вашем диапазоне больше столбцов, вы можете последовательно добавлять ссылки на столбцы и их первые ячейки в формулу.

    4. В диалоговом окне «Формат ячеек» укажите необходимый цвет выделения, шрифт, границы ячеек и т. Д., А затем щелкните OK кнопки последовательно, чтобы сохранить настройку.

    Пока что идентифицируются и выделяются повторяющиеся строки в выбранном диапазоне. Смотрите скриншот:

    1.2.4 Найдите и выделите дубликаты, кроме первых экземпляров

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

    1. Выберите столбец с заголовком.
    Советы: Если вам нужно выделить строки на основе дубликатов в одном столбце, кроме первых экземпляров, выберите диапазон без строки заголовка.

    2. Нажмите Главная > Условное форматирование > Новое правило.

    3. В диалоговом окне «Новое правило форматирования»:
    (1) Щелкните, чтобы выделить Используйте формулу, чтобы определить, какие ячейки следует форматировать. вариант;
    (2) В Формат значений, где эта формула истинна поле, введите формулу = СЧЁТЕСЛИ ($ C $ 3: $ C3; C3)> 1;
    Советы: Чтобы выделить строки на основе дубликатов в одном столбце, введите формулу = СЧЁТЕСЛИ ($ C $ 3: $ C3, $ C3)> 1.
    (3) Щелкните значок Формат кнопку.

    4. В появившемся диалоговом окне Формат ячеек укажите цвет выделения, шрифт, границы ячеек и т. Д. По мере необходимости, а затем нажмите OK кнопки для сохранения настроек.

    Затем вы увидите повторяющиеся ячейки, за исключением того, что выделены первые экземпляры в выбранном столбце (или строки на основе дубликатов в указанном столбце). Смотрите скриншот:

    1.3 Находите и выделяйте дубликаты разными цветами

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

    1, Нажмите другой + F11 вместе, чтобы открыть окно Microsoft Visual Basic для приложений.

    2. В окне нажмите Вставить > модуль, а затем вставьте приведенный ниже код в новое окно модуля.

    VBA: выделите повторяющиеся ячейки разными цветами:

    3, Нажмите F5 ключ или щелкните Выполнить значок для запуска этого VBA.

    4. В появившемся диалоговом окне Kutools for Excel выберите столбец, в котором вы выделите повторяющиеся ячейки разными цветами, и нажмите кнопку OK кнопку.

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

    1.4 Находите и выделяйте дубликаты с помощью сторонней надстройки

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

    1.4.1 Найдите и выделите повторяющиеся ячейки в одном столбце

    Первый инструмент, который я представлю, — это Выберите повторяющиеся и уникальные ячейки особенность, предоставленная Kutools for Excel. Эта функция может быстро найти уникальные или повторяющиеся ячейки.

    Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!

    Kutools for Excel — Combines more than 300 Advanced Functions and Tools for Microsoft Excel

    1. Выберите столбец или диапазон, в котором вы хотите найти, и выделите повторяющиеся ячейки.

    2. Нажмите Kutools > Выберите > Выберите повторяющиеся и уникальные ячейки.

    3. В диалоговом окне «Выбор повторяющихся и уникальных ячеек» отметьте нужные параметры и щелкните значок Ok кнопки, чтобы завершить операцию.

    Заметки: В диалоговом окне «Выбрать повторяющиеся и уникальные ячейки»
    (1) Если вам нужно выделить или выделить все дубликаты, кроме первых, отметьте Дубликаты (кроме 1-го) вариант. В противном случае проверьте Все дубликаты (включая 1-й) вариант.
    (2) Если вам нужно выделить дубликаты, отметьте Заполните цвет фона вариант и укажите нужный цвет выделения.
    (3) Если вы хотите выбрать или выделить строки на основе дубликатов в выбранном столбце, отметьте Выбрать целые строки вариант.
    (4) Если вы хотите выбрать или выделить повторяющиеся значения с совпадающими регистрами, отметьте Деликатный случай вариант.

    1.4.2 Найдите и выделите повторяющиеся ячейки в двух столбцах или листах

    Kutools for Excel также предоставляет удивительный инструмент — Сравнить ячейки, чтобы помочь нам легко найти и выделить повторяющиеся ячейки в двух столбцах.

    Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!

    Kutools for Excel — Combines more than 300 Advanced Functions and Tools for Microsoft Excel

    1. Нажмите Kutools > Сравнить ячейки , чтобы открыть диалоговое окно Выбрать одинаковые и разные ячейки.

    2. В диалоговом окне «Выбрать одинаковые и разные ячейки» укажите два столбца в Найдите значения в и Согласно информации коробки, отметьте Те же значения вариант и отметьте другие параметры по мере необходимости.

    Ноты:
    (1) Если вам нужно найти повторяющиеся строки, отметьте Каждый ряд вариант; и чтобы найти повторяющиеся ячейки, проверьте Одиночная ячейка вариант в на основании раздел;
    (2) Отметьте Заполните цвет фона опцию и укажите цвет выделения, если вам нужно выделить повторяющиеся строки или ячейки;
    (3) Отметьте Выбранные целые строки вариант, если вам нужно выделить или выделить всю строку на основе дубликатов;
    (4) Отметьте Деликатный случай вариант, если вы хотите найти или выделить дубликаты с учетом регистра.

    3. Нажмите Ok кнопки последовательно, чтобы завершить настройки.

    Затем вы увидите дубликаты в столбце, который вы указали в Найдите значения в коробки идентифицированы и выделены.

    2. Отфильтруйте дубликаты.

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

    2.1 Фильтрация дубликатов с условным форматированием

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

    1. Примените условное форматирование, чтобы найти и выделить дубликаты в указанном столбце. (Нажмите, чтобы посмотреть, как)

    2. Щелкните, чтобы выбрать заголовок указанного столбца, и щелкните Данные > Фильтр.

    3. Нажмите на значок фильтра в заголовке столбца и выберите Фильтр по цвету, а затем выберите указанный цвет условного форматирования в раскрывающемся списке. Смотрите скриншот:

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

    2.2 Фильтрация дубликатов с помощью вспомогательного столбца

    В качестве альтернативы мы также можем идентифицировать дубликаты с помощью формулы во вспомогательном столбце, а затем легко фильтровать дубликаты с помощью вспомогательного столбца в Excel.

    1. Помимо исходных данных, добавьте вспомогательный столбец и введите Дублировать как заголовок столбца.

    2. Выберите первую пустую ячейку под заголовком столбца, введите формулу = ЕСЛИ (СЧЁТЕСЛИ ($ C $ 3: $ C $ 12; C3)> 1, «Дублировать», «»), и перетащите маркер автозаполнения этой ячейки, чтобы скопировать эту формулу в другие ячейки.

    Заметки: В формуле выше 3 канадских доллара: 12 канадских долларов столбец, содержащий повторяющиеся данные, и C3 — это первая ячейка (кроме ячейки заголовка) в столбце.

    3. Щелкните, чтобы выбрать заголовок столбца — Дубликаты, и щелкните Данные > Фильтр.

    4. Затем щелкните значок фильтра в заголовке столбца отметьте только Дублировать, и нажмите OK кнопка. Смотрите скриншот:

    Пока отфильтровываются только строки с повторяющимися значениями. Смотрите скриншот:

    3. Подсчитайте дубликаты.

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

    3.1 Подсчет повторяющихся значений с критериями

    В общем, мы можем применить = СЧЁТЕСЛИ (диапазон; критерии) для подсчета общего количества определенного значения, появляющегося в указанном диапазоне. Говорит считать, сколько раз «Яблоко» появляется в списке A2: A10, Мы можем применить формулу = СЧЁТЕСЛИ (A2: A10; «Яблоко») для подсчета количества этого повторяющегося значения.

    Однако формула = СЧЁТЕСЛИ (диапазон, критерии) подсчитывает только указанное повторяющееся значение. Как насчет подсчета повторяющихся значений по двум или нескольким критериям? А что, если подсчет дубликатов с учетом регистра с критериями? Ниже приведены методы, которые помогут вам решить эти проблемы.

    3.1.1 Подсчет дубликатов с учетом регистра с помощью критериев

    Мы можем применить формулу массива для подсчета повторяющихся значений с учетом регистра с критериями в Excel. Например, чтобы подсчитать, сколько раз значение «Apple» появляется в списке B2: B21 с совпадающими регистрами, вы можете сделать это следующим образом:

    1. Выберите пустую ячейку.

    2. Введите формулу = СУММ (- ТОЧНО (B2: B20; E2)).

    3, Нажмите Ctrl + Shift + Enter чтобы вернуть результат подсчета.

    Заметки: В формуле массива
    (1) B2: B20 — это столбец, в котором вы будете считать дубликаты.
    (2) E2 — это ячейка, содержащая указанное значение, количество вхождений которого вы хотите подсчитать. Вы можете изменить ссылку на ячейку на значение в кавычках, говорит «Яблоко».

    3.1.2 Подсчет дубликатов по нескольким критериям

    Иногда вы можете захотеть подсчитать дубликаты по двум или более критериям. Вы можете применить COUNTIFS функция, чтобы сделать это.
    Например, есть таблица продаж фруктов, как показано на скриншоте ниже. Здесь нам нужно подсчитать время повторения яблока, которое было продано 7, а сумма продаж превышает 5. Вы можете подсчитать дубликаты по этим критериям следующим образом:

    1. Выберите пустую ячейку.

    2. Введите формулу =COUNTIFS(B3:B20,G4,C3:C20,G3,D3:D20,»>300″).

    3, нажмите Enter ключ для получения результата подсчета.

    Заметки: В приведенной выше формуле
    (1) B3: B20 — (первый) столбец даты, и G4 критерий даты;
    (2) C3: C20 — (второй) фруктовый столбец, и G3 критерий фруктов;
    (3) D3: D20 — (третий) столбец суммы, и «> 300» критерий суммы.
    (4) Если в вашей таблице больше столбцов и критериев, вы можете добавить ссылку на столбец и критерии.

    3.2 Подсчитайте общее количество дубликатов в одном столбце

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

    3.2.1 Подсчет дубликатов в столбце, исключая первое вхождение

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

    1. Выберите пустую ячейку рядом со столбцом.

    2. Введите формулу = ЕСЛИ (СЧЁТЕСЛИ ($ B $ 3: B3; B3)> 1; «ДА»; «»), а затем перетащите маркер автозаполнения вниз, чтобы скопировать эту формулу в другие ячейки.

    Заметки: В формуле выше
    (1) $ B $ 3: B3 это диапазон, в котором вы считаете дубликаты. В $ B $ 3: B3, B3 изменится автоматически при копировании формулы в другие ячейки.
    (2) B3 — первая ячейка в указанном столбце.
    (3) Эта формула вернет ДА ​​или пусто. ДА означает, что соответствующее значение повторяется, а пустое поле означает уникальное.

    Затем идентифицируются все дубликаты в указанном столбце. Мы можем подсчитать результаты формулы, чтобы получить общее количество дубликатов.

    3. Выберите пустую ячейку.

    4. Введите формулу = СЧЁТЕСЛИ (C3: C16; «ДА»), и нажмите Enter ключ.

    Заметки: В формуле выше
    (1) C3: C16 это диапазон, который мы применили по формуле для выявления дубликатов на последнем этапе.
    (2) ДА — значение, возвращаемое последней формулой.

    Затем мы получаем общее количество повторяющихся значений в указанном столбце. Общее количество дубликатов не включает первые вхождения.

    3.2.2 Подсчет дубликатов в столбце, включая первое вхождение

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

    1. Выберите пустую ячейку.

    2. Введите формулу =ROWS(B3:B16)-SUM(IF(COUNTIF(B3:B16,B3:B16) =1,1,0)).

    3, Нажмите Ctrl + Shift + Enter чтобы вернуть результат подсчета.

    Заметки: В формуле выше B3: B16 — это указанный столбец, в котором мы хотим подсчитать дубликаты, включая первые вхождения в нем.

    3.2.3 Подсчет дубликатов в столбце, включая / исключая первые вхождения

    Чтобы упростить себе работу и избавить себя от запоминания длинных скучных формул, вы можете попробовать Выберите повторяющиеся и уникальные ячейки функция, предоставляемая Kutools for Excel, чтобы быстро подсчитать количество повторяющихся значений в указанном списке или столбце, исключая или включая первые вхождения.

    Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!

    Kutools for Excel — Combines more than 300 Advanced Functions and Tools for Microsoft Excel

    1. Выберите столбец, в котором вы будете подсчитывать количество повторяющихся значений, и нажмите Kutools > Выберите > Выберите повторяющиеся и уникальные ячейки.

    2. В диалоговом окне «Выбор повторяющихся и уникальных ячеек» установите флажок Дубликаты (кроме 1-го) or Все дубликаты (включая 1-й) вариант, как вам нужно, и щелкните Ok кнопку.

    3. Затем выбираются все повторяющиеся значения, включая или исключая первые вхождения, и одновременно появляется диалоговое окно, показывающее, сколько ячеек было выбрано. Смотрите скриншот выше.

    3.3 Подсчитайте дубликаты в двух столбцах

    3.3.1 Подсчет дубликатов между двумя столбцами с помощью формулы

    Говорит, что вы хотите сравнить два списка имен и подсчитать количество дубликатов между ними, как вы могли бы быстро решить эту проблему? Мы также можем сделать это с помощью формулы в Excel.

    1. Выберите пустую ячейку.

    2. Введите формулу = СУММПРОИЗВ (- (ISNUMBER (MATCH (B3: B12, D3: D18,0)))).

    3, нажмите Enter ключ.

    Заметки: В формуле выше
    (1) B3: B12 — это первый столбец имен, в котором вы будете считать дубликаты.
    (2) D3: D18 — это второй столбец имен, на основе которого вы будете подсчитывать дубликаты.

    3.3.2 Подсчет дубликатов между двумя столбцами с помощью сторонней надстройки

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

    Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!

    Kutools for Excel — Combines more than 300 Advanced Functions and Tools for Microsoft Excel

    1. Нажмите Kutools > Выберите > Выберите одинаковые и разные ячейки.

    2. В диалоговом окне Выбрать одинаковые и разностные ячейки,
    (1) Укажите два столбца в Найдите значения в и Согласно информации коробки отдельно.
    (2) Проверьте Однокамерная вариант.
    (3) Проверьте Те же значения вариант.

    4, Нажмите Ok кнопку.

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

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

    3.4 Считайте дубликаты только один раз

    Иногда в столбце встречаются повторяющиеся значения. Когда мы подсчитываем значения в столбце, нам нужно один раз подсчитать дубликаты. Возьмем, к примеру, ряд значений A, A, B, C, C, C, D, E, E, и нам нужно подсчитать значения и получить 5 (A, B, C, D, E). Здесь в этом разделе будут представлены две формулы для решения этой проблемы.

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

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

    1. Выберите пустую ячейку.

    2. Введите формулу =SUMPRODUCT((C3:C19<>»»)/COUNTIF(C3:C19,C3:C19&»»)).

    3, нажмите Enter ключ для получения результата подсчета.

    Заметки: В формуле выше C3: C19 — это указанный столбец, в котором вы хотите один раз подсчитать каждое повторяющееся значение.

    3.4.2 Один раз подсчитать повторяющееся значение с учетом регистра с помощью формулы массива

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

    1. Выберите пустую ячейку.

    2. Введите формулу массива = СУММ (ЕСЛИОШИБКА (1 / ЕСЛИ (C3: C19 <> «», ЧАСТОТА (ЕСЛИ (ТОЧНО (C3: C19; ТРАНСПОЗИРОВАТЬ (C3: C19))), ПОИСКПОЗ (СТРОКА (C3: C19), СТРОКА (C3: C19) ), «»), МАТЧ (СТРОКА (C3: C19), СТРОКА (C3: C19))), 0), 0)).

    3, Нажмите Ctrl + Shift + Enter вместе, чтобы получить результат подсчета.

    Заметки: В приведенной выше формуле массива C3: C19 — это указанный столбец, в котором вы будете подсчитывать каждую серию дубликатов один раз с совпадающими случаями.

    3.4.3 Подсчитайте каждое повторяющееся значение один раз с помощью сторонней надстройки

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

    Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!

    Kutools for Excel — Combines more than 300 Advanced Functions and Tools for Microsoft Excel

    1. Выберите пустую ячейку.

    2. Нажмите Kutools > Формула Помощник > Статистический > Подсчет ячеек с уникальными значениями (включая первое повторяющееся значение).

    3. В диалоговом окне «Помощник по формулам» укажите столбец, в котором вы будете считать дубликаты один раз Диапазон и нажмите Ok кнопку.

    Затем результат подсчета сразу же заполняется в выбранной ячейке.

    3.5 Подсчитайте каждое повторяющееся значение в одном столбце

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

    3.5.1 Подсчитайте каждое повторяющееся значение в одном столбце с помощью функции ПРОМЕЖУТОЧНЫЙ ИТОГ

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

    1. Выберите столбец, в котором вы будете считать каждую серию повторяющихся значений, и нажмите Данные > Сортировка от А до Я or Сортировка Z по A.

    2. В появившемся диалоговом окне предупреждения о сортировке проверьте Расширить выбор и нажмите Сортировать кнопку.

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

    3. Не снимая выделения, нажмите Данные > Сумма.

    4. В диалоговом окне «Итого»
    (1) Выберите указанный столбец из При каждом изменении раскрывающийся список;
    (2) Выберите Количество из Использование функции раскрывающийся список;
    (3) Отметьте только указанный столбец в Добавить промежуточный итог к список;
    (4) Щелкните значок OK кнопку.

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

    3.5.2 Подсчитайте каждое повторяющееся значение в одном столбце с помощью сводной таблицы

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

    1. Выберите диапазон, содержащий указанный столбец, и щелкните Вставить > PivotTable.

    2. В диалоговом окне «Создание сводной таблицы» укажите место, в котором будет размещена новая сводная таблица, и щелкните значок OK кнопку.

    3. На панели полей сводной таблицы перетащите указанный столбец в оба Ряды и Ценности разделы. Затем вы увидите, что каждая серия повторяющихся значений в указанном столбце учитывается массово. Смотрите скриншот:

    3.5.3 Подсчитайте каждое повторяющееся значение в одном столбце с помощью замечательного инструмента

    Если вы установили Kutools for Excel уже вы можете применить его простой в использовании Расширенные ряды комбинирования функция для быстрого подсчета каждой серии повторяющихся значений в указанном столбце.

    Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!

    Kutools for Excel — Combines more than 300 Advanced Functions and Tools for Microsoft Excel

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

    1. Добавьте пустой столбец справа от исходного диапазона данных и назовите новый столбец как Количество.

    2. Выберите вместе исходный диапазон данных и новый столбец и нажмите Kutools > Слияние и разделение > Расширенные ряды комбинирования.

    3. В диалоговом окне Advanced Combine Rows,
    (1) Щелкните, чтобы выбрать указанный столбец, в котором вы будете подсчитывать каждую серию повторяющихся значений, и щелкните Основной ключ.
    (2) Щелкните, чтобы выбрать новый столбец (Число), а затем щелкните Рассчитать > Количество.
    (3) При необходимости укажите комбинацию или типы расчета для других столбцов.
    (4) Щелкните значок Ok кнопку.

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

    3.6 Считайте дубликаты по порядку

    Говорит, что есть список фруктов в столбце. В списке некоторые фрукты встречаются много раз. Теперь вам нужно пометить каждый повторяющийся фрукт в том порядке, в котором он появляется, как бы вы могли его решить? Здесь в этом разделе будет представлена ​​формула, позволяющая легко сделать это в Excel.

    1. Добавьте пустой столбец справа от исходных данных.

    2. Введите формулу =IF(COUNTIF($C$3:$C$14,C3)>1,COUNTIF(C$3:C3,C3),»») в первой ячейке добавленного пустого столбца.

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

    Заметки: В формуле выше
    (1) 3 канадских доллара: 14 канадских долларов — это указанный столбец, в котором вы хотите посчитать повторяющиеся значения по порядку.
    (2) C3 — первая ячейка в указанном столбце.
    (3) Если соответствующее значение повторяется, эта формула вернет порядковый номер 1, 2, 3… в зависимости от порядка появления; если соответствующее значение уникально, эта формула вернет пустое значение.

    4. Удалить дубликаты

    Когда несколько повторяющихся значений складываются в столбец или диапазон, некоторые пользователи ищут простые способы быстрого удаления повторяющихся значений. Здесь в этой части будут представлены несколько решений для легкого удаления повторяющихся значений в Excel.

    4.1 Удаление дубликатов, кроме одного в столбце

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

    4.1.1 Удаление дубликатов, кроме одного, с функцией удаления дубликатов

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

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

    2. Нажмите Данные > Удалить дубликаты.

    3. В диалоговом окне «Предупреждение об удалении дубликатов» установите флажок Продолжить текущий выбор и нажмите Удалить дубликаты кнопку.

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

    4. В диалоговом окне «Удалить дубликаты» отметьте только указанный столбец и щелкните значок OK кнопку.

    Советы: Если вы проверили Расширить выбор на последнем шаге, здесь будут перечислены все столбцы. Тем не менее, вам нужно проверить только указанный столбец.

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

    4.1.2 Удаление дубликатов, кроме одного с функцией расширенного фильтра

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

    1. Нажмите Данные > Дополнительно.

    2. В диалоговом окне «Расширенный фильтр»
    (1) Проверьте Скопировать в другое место вариант;
    (2) В Диапазон списка поле выберите указанный столбец, из которого вы удалите повторяющиеся значения;
    (3) В Скопировать в поле укажите диапазон, в который будет вставлен столбец;
    (4) Отметьте Только уникальные записи вариант.
    (5) Щелкните значок OK кнопку.

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

    4.1.3 Удалить дубликаты, кроме одного, с VBA

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

    1, Нажмите другой + F11 клавиши, чтобы открыть окно Microsoft Visual Basic для приложения.

    2. Нажмите Вставить > модуль, а затем вставьте ниже код VBA в новое окно модуля.

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

    3, Нажмите F5 ключ или щелкните Выполнить кнопку для запуска этого VBA.

    4. В появившемся диалоговом окне укажите диапазон, из которого вы удалите повторяющиеся значения, и нажмите кнопку OK кнопку.

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

    Примечание. Этот код VBA чувствителен к регистру.

    4.2 Удалить дубликаты и оригинал

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

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

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

    1. Примените условное форматирование, чтобы выделить повторяющиеся значения. (Нажмите, чтобы посмотреть, как)

    2. Выберите столбец, из которого вы удалите повторяющиеся значения (включая первые вхождения), и нажмите Данные > Фильтр.

    3. Щелкните значок фильтра в заголовке столбца указанного столбца. В раскрывающемся меню выберите Фильтр по цвету, а затем укажите цвет выделения в подменю.

    Затем все повторяющиеся значения отфильтровываются.

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

    5. Затем все повторяющиеся значения удаляются сразу. Не снимая выделения с отфильтрованного списка, нажмите Фильтр > Данные еще раз, чтобы отменить фильтр.

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

    Заметки: Этот метод удалит строки на основе повторяющихся значений в указанном столбце.

    4.2.2 Удалите все дубликаты и исходные значения с помощью вспомогательного столбца

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

    1. Добавьте вспомогательный столбец рядом с указанным столбцом, введите формулу = СЧЁТЕСЛИ ($ B $ 3: $ B $ 11; B3) в первую ячейку вспомогательного столбца, а затем перетащите дескриптор автозаполнения вниз, чтобы скопировать эту формулу в другие ячейки. Смотрите скриншот:

    Внимание: В формуле выше 3 млрд долларов: 11 млрд долларов — это указанный столбец, из которого вы удалите повторяющиеся значения, и B3 — первая ячейка в указанном столбце.

    2. Выберите вспомогательный столбец и щелкните Данные > Фильтр.

    3. Щелкните значок фильтра в заголовке вспомогательного столбца, затем в раскрывающемся меню отметьте все значения, кроме 1, и нажмите OK кнопка. Смотрите скриншот:

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

    5. В появившемся диалоговом окне подтверждения нажмите кнопку OK кнопку, чтобы продолжить.

    6. Теперь все повторяющиеся значения и их строки удаляются массово. Продолжайте нажимать Данные > Фильтр еще раз, чтобы отменить фильтр.

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

    4.2.3 Удалите все дубликаты и исходные значения с помощью замечательного инструмента

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

    Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!

    Kutools for Excel — Combines more than 300 Advanced Functions and Tools for Microsoft Excel

    1. Выберите столбец, из которого вы удалите дубликаты.

    2. Нажмите Kutools > Выберите > Выберите повторяющиеся и уникальные ячейки.

    3. В диалоговом окне «Выбор повторяющихся и уникальных ячеек» установите флажок Все дубликаты (включая 1-й) и нажмите Ok кнопку.

    Ноты:
    (1) Чтобы выбрать и удалить повторяющиеся значения, исключая первые вхождения, установите флажок Дубликаты (кроме 1-го) вариант.
    (2) Чтобы выбрать и удалить строки на основе повторяющихся значений в указанном столбце, установите флажок Выбрать целые строки вариант.
    (3) Чтобы выбрать и удалить повторяющиеся значения с совпадающими регистрами, установите флажок Деликатный случай вариант.
    (4) Чтобы выбрать, выделить и удалить повторяющиеся ячейки или строки, установите флажок Заполните цвет фона or Цвет заливки шрифта параметры и укажите цвета заливки или шрифта по своему усмотрению.

    4. Затем появится диалоговое окно, показывающее, сколько ячеек было выбрано, щелкните значок OK чтобы закрыть его.

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

    6. В появившемся диалоговом окне «Удалить» проверьте Сдвинуть ячейки вверх и нажмите OK кнопку.

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

    4.3 Удаление строк на основе дубликатов в одном столбце

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

    Первый способ — применить встроенный Удалить дубликаты возможность удаления строк по дубликату в указанном столбце. Просто выберите диапазон, в котором вы будете удалять строки, нажмите Данные > Удалить дубликаты чтобы включить эту функцию, отметьте только указанный столбец в диалоговом окне «Удалить дубликаты», а затем нажмите OK чтобы завершить операцию удаления.

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

    В качестве альтернативы вы можете добавить вспомогательный столбец и применить формулу = СЧЁТЕСЛИ ($ C $ 3: $ C $ 21; C3) для выявления дубликатов в указанном столбце. Затем отфильтруйте числа больше 1 во вспомогательном столбце и легко удалите все отфильтрованные строки. После очистки фильтра вы увидите, что строки с уникальными значениями остаются только в указанном столбце.

    Сторонняя надстройка Kutools for Excel также имеет необычайно удобную функцию, Выберите повторяющиеся и уникальные ячейки, чтобы быстро выбрать строки на основе повторяющихся значений в указанном столбце, а затем вы можете быстро удалить эти выбранные строки, щелкнув меню правой кнопкой мыши.

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

    4.4 Удалить дубликаты в двух столбцах

    Иногда нам нужно сравнить два списка или столбца, а затем удалить дубликаты между ними в Excel. Здесь в этом разделе представлены два решения.

    4.4.1 Удалить дубликаты в двух столбцах с помощью вспомогательного столбца

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

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

    2. В первой ячейке вспомогательного столбца (исключая ячейку заголовка) введите формулу = IF (ISERROR (MATCH (C2; $ A $ 2: $ A $ 13,0)), «Уникальный», «Повторяющийся»), а затем перетащите маркер автозаполнения вниз, чтобы скопировать формулу в другие ячейки.

    Заметки: В формуле выше
    (1) C2 это первая ячейка в указанном столбце, из которой вы удалите повторяющиеся значения;
    (2) $ A $ 2: $ A $ 13 это другой столбец, с которым нам нужно сравнить.
    (3) Эта формула вернет Дублировать если соответствующее значение дублируется со значениями в другом столбце, и вернуть Уникальное Произведение если отличается от значений в другом столбце.

    3. Выберите вспомогательный столбец и щелкните Данные > Фильтр.

    4. Щелкните значок фильтра в заголовке вспомогательного столбца, затем в раскрывающемся меню отметьте только Дублировать, и нажмите OK кнопку.

    5. Теперь все повторяющиеся значения отфильтрованы. Выберите отфильтрованные ячейки, щелкните правой кнопкой мыши и выберите Удалить строку из контекстного меню. Затем нажмите OK во всплывающем диалоговом окне подтверждения.

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

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

    Заметки: Этот метод удалит все строки на основе повторяющихся значений в указанном столбце.

    4.4.2 Удалите дубликаты в двух столбцах с помощью замечательного инструмента

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

    Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!

    Kutools for Excel — Combines more than 300 Advanced Functions and Tools for Microsoft Excel

    1. Нажмите Kutools > Выберите > Выберите одинаковые и разные ячейки для включения этой функции.

    2. В диалоговом окне «Выбрать одинаковые и разные ячейки» укажите оба столбца в поле Найдите значения в и Согласно информации коробки отдельно, отметьте Однокамерная и Те же значения параметры и щелкните Ok кнопка. Смотрите скриншот:

    3. Затем все повторяющиеся значения в количестве двух столбцов выбираются в первом столбце (столбец, который вы указали в Найдите ценность в коробка). И нажмите кнопку OK в появившемся диалоговом окне.

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

    Еще статьи .

    Добавить столбец уникального идентификатора для повторяющихся строк в Excel
    Предположим, что в столбце есть повторяющиеся данные, теперь вы хотите добавить уникальный идентификационный номер для этих повторяющихся строк, как показано на скриншоте ниже. Есть ли у вас какие-нибудь хорошие способы их добавления? Здесь я представляю формулу, которая поможет вам быстро справиться с этой задачей в Excel.

    Автоматически удалять дубликаты из списка в Excel
    В Excel мы можем использовать функцию «Удалить дубликаты» для удаления одинаковых значений из списка, но есть ли какой-нибудь трюк для автоматического удаления дубликатов из списка? В этой статье я расскажу о приемах автоматического удаления дубликатов, если они появляются в указанном столбце Excel.

    Ранжируйте дубликат без пропуска чисел в Excel
    В общем, когда мы ранжируем список с дубликатами, некоторые числа будут пропущены, как показано на скриншоте 1 ниже, но в некоторых случаях мы просто хотим ранжировать по уникальным номерам или ранжировать дубликаты с тем же номером, не пропуская числа, как показано на скриншоте 2. У вас есть какие-нибудь хитрости для решения этой задачи в Excel?

    Используйте ярлык для удаления дубликатов в столбце или строке Excel
    В Excel утилита «Удалить дубликаты» может помочь вам удалить дубликаты в столбце, но знаете ли вы, как использовать ярлык для удаления дубликатов в столбце или строке Excel? Удалите дубликаты ярлыком.

    Vlookup и возврат нескольких значений без дубликатов в Excel
    Иногда вам может понадобиться vlookup и сразу вернуть несколько совпадающих значений в одну ячейку. Но если в возвращенные ячейки внесены повторяющиеся значения, как можно игнорировать дубликаты и сохранять только уникальные значения при возврате всех совпадающих значений, как показано на следующем снимке экрана в Excel?

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

    ​ значений можно найти​​ находится в диапазоне​ парился, классно, что​
    ​ столбце максимальное значение​
    ​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=IF(COUNTIF(B$7:B7;B7)=COUNTIF(B$7:B$58;B7);COUNTIF(B$7:B7;B7);»»)​
    ​ а сами данные​
    ​ подсчет общего количества​
    ​Поклон вам от​
    ​ ее подробнее?​
    ​: Эмм. А как​
    ​ проставлены ручками; что​
    ​ Функция СЧЁТЕСЛИМН проверяет​
    ​2​
    ​4​СЧЁТЕСЛИМН(диапазон_условия1; условие1; [диапазон_условия2; условие2];​Число счетов с суммой,​9 000​
    ​ здесь: Подсчет неповторяющихся​А7:А16 ​ я нашёл такой​ подряд идущих нолей-4.​

    ​Вставьте эту формулу​​ различны.​

    ​ имеющихся вагонов, в​​ меня :)​vikttur​
    ​ поделить единицу на​
    ​ ввести в красную​

    ​ соответствие обоим условиям.​​Финансы​
    ​Продажи​
    ​ …)​
    ​ меньшей 20 000 (4)​
    ​Шашков​

    ​ значений. Вообще, если​
    ​(см. файл примера), то​

    ​ форум.​​ Единиц-2.​ в ячейку А7​Причем сумму повторяющихся​ столбце А приложенного​И не упрекайте,​: Наверное. напутали с​

Ссылка на основную публикацию