Evolcom.ru

Бытовая техника
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как подсчитать одинаковые или повторяющиеся значения только один раз в столбце?

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

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

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

Найти и выделить дубликаты в таблице можно с помощью условного форматирования в Excel. Выделите весь диапазон данных в нужной таблице. Нажмите «Условное форматирование» на вкладке «Главная». Выберите в меню «Правила выделения ячеек» — «Дубликаты значений» .

Повторяющиеся значения в Excel

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

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

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

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

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

Еще одним вариантом удаления дубликатов является фильтр. Выберите столбцы и заголовок таблицы. Нажмите «Дополнительно» в разделе «Сортировка и фильтр» на вкладке «Данные».

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

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

Читайте так же:
Как переместить определенные файлы из одной папки в другую в Excel?

Если вам нужно найти и подсчитать количество повторяющихся значений в Excel, создайте для этого рабочий лист Excel. Добавьте столбец «Код» в исходную таблицу и заполните его «1»: поставьте 1, 1 в первые две ячейки, выделите их и перетащите вниз. При обнаружении дубликатов в строках значение в столбце «Код» каждый раз увеличивается на единицу.

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

Подробнее о работе с электронными таблицами в Excel читайте в этой статье, перейдя по ссылке.

Далее, ячейки диапазона уже указаны, отметьте маркер «На новый лист» и нажмите «OK».

С правой стороны перетащите первые три заголовка таблицы в область «Названия строк». «Код» следует перетащить в область «Значения».

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

Для удобства выделите все значения в колонке «Сумма по полю кода» и отсортируйте их в порядке убывания.

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

Подсчет разных значений в сводной таблице Excel (простое пошаговое руководство)

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

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

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

Расчет различных значений для сводной таблицы — одна из таких задач.

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

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

Разные значения против уникальных значений

Это выглядит как одно и то же, но это не так.

Читайте так же:
Как показать / отобразить более 8 строк в списке проверки данных в Excel?

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

Разница между уникальным и различными значениями

Разница между уникальными и разными значениями

Уникальные значения / имена — это те, которые встречаются только один раз. Это означает, что все имена, которые повторяются и имеют дубликаты, не являются уникальными. Уникальные имена перечислены в столбце D вышеупомянутого набора данных.

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

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

Подсчет разных значений в сводной таблице Excel

Предположим, что у вас есть данные о продажах:

исходные данные

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

С помощью этого набора данных вам нужно найти ответ на следующие вопросы:

  • Сколько сотрудников в каждом регионе (что является ничем иным, как количеством различных сотрудников в каждом регионе)?
  • Сколько сотрудников продали принтер в 2019 году?

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

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

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

Среди методов, рассматриваемых в данной статье, следующие:

  • Добавьте вспомогательный столбец к исходному набору данных для вычисления различных значений (работает во всех версиях).
  • Добавьте данные в модель данных и используйте настройку «Количество различных элементов» (доступно в Excel 2013 и более поздних версиях).

Есть и третий метод, он вызывает метод crosstab в crosstab.

Добавление вспомогательного столбца в набор данных

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

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

Читайте так же:
Как получить список последних или текущих открытых файлов Excel?

Однако у этого простого обходного пути есть несколько недостатков (о которых мы расскажем позже).

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

Предположим, у меня есть набор данных, подобный приведенному ниже:

исходные данные

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

Приведенная выше формула использует функцию СКРАТЕСЛИМН для подсчета количества раз, когда имя встречается в заданном регионе. Также обратите внимание на диапазоны критериев: $C$2:C2 и $B$2:B2. Поэтому они продолжают увеличиваться по мере продвижения вниз по столбцу.

Например, в ячейке F2 критериями являются $C$2:C2 и $B$2:B2, но в ячейке F3 эти диапазоны расширены до $C$3:C3 и $B$3:B3.

Функция READSLYMN считает первый экземпляр имени как 1, второй как 2 и так далее.

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

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

Добавляем вспомогательный столбец

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

Ниже подробно описаны шаги, которые необходимо предпринять:

  • Выберите любую ячейку в таблице.
  • Перейдите на вкладку «Вставка».

Вкладка Вставка

  • Нажмите кнопку «Итоговая таблица».

Кнопка Сводная таблица

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

Создание сводной таблицы

  • Нажмите ОК.

Вышеописанный шаг вставляет новый лист сводных таблиц.

Перетащите поле «Регион» в область «Строки» и поле «Помощник» в область «Значения».

Области сводной таблицы

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

Полученная сводная таблица

Заголовок колонки теперь читается как «Сумма по полю Assistant», а не «Количество сотрудников».

Читайте так же:
Как предотвратить ввод строчных букв в выделении Excel?

Недостатки использования вспомогательного столбца

Хотя этот метод довольно прост, он имеет несколько недостатков:

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

Добавить данные в модель данных и суммировать, используя «Число различных элементов»

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

Если вы используете предыдущую версию, вы не можете использовать этот метод (воспользуйтесь методом, описанным выше).

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

Исходные данные

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

  • Выберите любую ячейку в таблице.
  • Перейдите на вкладку «Вставка».

Вкладка Вставка

  • Нажмите на кнопку сводной таблицы.

Кнопка Сводная таблица

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

Диалоговое окно Создание сводной таблицы

  • Хаз нажмите кнопку OK.

На предыдущих шагах мы создали новый рабочий лист с новой сводной таблицей.

Перетащите область в область «Строки», а область «Сотрудник» — в область «Значения». Вы получите сводную таблицу, как показано ниже:

Новая сводная таблица

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

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

  • Щелкните правой кнопкой мыши по любой ячейке в «Число элементов в столбце Сотрудник»
  • Нажмите на «Параметры полей значений».

Параметры полей значений

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

Окно Параметры поля значений

  • Нажмите кнопку «Принять».

Обратите внимание, что название столбца было изменено с «Количество элементов в столбце сотрудника» на «Количество различных элементов в столбце сотрудника». Вы можете изменить это.

Читайте так же:
Как предотвратить дублирование записей в столбце Excel?

Число разных элементов

Некоторые вещи, которые необходимо знать при добавлении данных в модель данных:

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

Что если вы хотите посчитать уникальные значения (а не разные значения)?

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

Помните — уникальные значения и разные значения не одно и то же. Нажмите здесь, чтобы узнать разницу.

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

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

Вот формула для данного случая:

= ЕСЛИ (ЕСЛИ (СЧЁТЕСЛИМН ($C$2:$C$1001; С2; $B$2:$B$1001; В2) / СЧЁТЕСЛИ ($C$2:$C$1001; С2) <1;0;1); ЕСЛИ (СЧЁТЕСЛИ ($С2:С$22; С2) > 1;0;1);0)

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

Формула возвращает 1, если имя встречается более чем в одном регионе, и 0 — если нет.

Формула также проверяет, встречается ли имя в одном и том же регионе или нет. Если имя повторяется, то только первый экземпляр возвращает 1, а остальные — 0.

Это может показаться сложным, но опять же, все зависит от того, чего вы пытаетесь достичь.

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector