Evolcom.ru

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

Как подсчитать уникальные значения между двумя датами в Excel?

Извлекать уникальные предметы из списка

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

Примечание: это формула массива, ее следует вводить с помощью Ctrl + Shift + Enter

Эта формула в основном представляет собой базовый поиск с ИНДЕКСАМИ:

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

Трудная работа заключается в вычислении номера STRING для получения ИНДЕКСА, чтобы мы получали только уникальные значения. Это делается с помощью SEARCH и RATE, и главный трюк здесь :

В качестве примера C OUNTIF подсчитывает, сколько раз определенный элемент появляется в уникальном списке, используя расширяющуюся связь, $ D $ 4: D4.

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

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

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

Для критериев в SCHOTESLY мы используем сам мастер-лист. Если задано более одного критерия, SCHOTESLY возвращает несколько результатов в наборе . В каждой новой строке мы имеем еще один массив, подобный этому:

Примечание. СЧЁТЕСЛИ обрабатывает несколько критериев с отношением «ИЛИ» (т. Е. СЧЁТЕСЛИ (диапазон, <'красный', 'синий', 'зеленый'>) подсчитывает красный, синий или зеленый цвет.

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

Теперь, когда у нас есть массивы, нам нужно найти позиции (номера строк). Для этого мы используем SEARCH, установленный на точное соответствие, чтобы найти нулевые значения. Если мы поместим массивы, созданные выше с помощью SCRUTESLY, в SEARCHPOZ, вот что мы получим:

SEARCHPOPE обнаруживает элементы путем поиска количества, равного нулю (т.е. поиск элементов, которые еще не появились в едином списке). Это работает, потому что SEARCH всегда возвращает первое совпадение при наличии дубликатов.

Наконец, позиции передаются в INDEX в виде номеров строк, и INDEX возвращает имя в данной позиции.

Версия без массива с LOOKUP

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

Построение формулы аналогично приведенной выше формуле ПОИСК ИНДЕКСОВ, но VIEWER может обрабатывать операцию с массивами с самого начала.

  • SCREEN возвращает номер каждого значения ‘list’ в расширении диапазона $ D $ 4 : D4
  • Сравнение с нулем создает массив значений TRUE и FALSE.
  • Число 1 делится на массив, образуя массив единиц и ошибок # DIV / 0.
  • Этот массив становится вектором поиска в LOOKUP
  • Поисковое значение 2 больше любого значения в векторе поиска
  • SCREEN будет соответствовать последнему безошибочному значению в массиве поиска
  • SCREEN возвращает соответствующее значение в векторе результатов, диапазон с именем ‘list’.

Извлеките элементы, которые появляются только один раз

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

В выражении COUNTIF было добавлено только второе выражение:

Здесь SCIENCE возвращает массив с рядом элементов, таких как:

Которые сравниваются с 1, в результате чего получается массив значений True/False:

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

Читайте так же:
Как показать шаги расчета в Excel?

В Excel 365 функция UNIQUE — это лучший способ извлечения уникальных значений.

Как найти уникальные значения в Google Таблицах (2 простых метода)

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

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

Как найти уникальные значения в Google Таблицах

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

  • Используйте инструмент «Удаление дубликатов», встроенный в Google Таблицы.
  • Использование функции UNIQUE .

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

Обратите внимание, что в строках 2 и 5, а также в строках 6 и 7 есть повторяющиеся записи.

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

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

Поиск уникальных значений с помощью инструмента «Удалить дубликаты»

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

Вот шаги, которые следует предпринять:

  • Выберите диапазон данных, с которыми вы хотите работать. В нашем примере мы выбрали столбцы A и B, перетащив указатель мыши на их заголовки.
  • В меню «Данные» выберите «Удалить дубликаты».
  • Откроется диалоговое окно «Удалить дубликаты».
  • Если ваш выбор также содержит заголовки столбцов, установите флажок рядом с параметром «Данные имеют строку заголовка».
  • В разделе «Столбцы для анализа» вы можете выбрать, хотите ли вы, чтобы инструмент считал все выбранные столбцы или несколько выбранных столбцов. Прямо сейчас, поскольку мы хотим избавиться от записей, в которых имя и фамилия совпадают, мы оставим для него значение «Выбрать все».
  • Нажмите кнопку «Удалить дубликаты».
  • Вы увидите окно сообщения с подробностями о том, сколько повторяющихся строк было удалено и сколько уникальных строк было сохранено.
Читайте так же:
Как поместить значение ячейки в верхний / нижний колонтитул в Excel?

Теперь вы должны увидеть, что строки 5 и 7 удалены. Другими словами, когда инструмент Remove Duplicates находит дубликаты значений, он сохраняет только первый экземпляр каждого значения, удаляя все остальные.

Если вы хотите удалить все строки, содержащие дубликаты имени, вы можете просто снять флажок рядом со столбцом B в диалоговом окне «Удалить дубликаты» (шаг 5).

В этом случае вам придется удалить три повторяющихся строки, поскольку строка, содержащая имя Пола Родригеса, также считается дубликатом.

Тогда ваш результирующий набор данных будет следующим.

Поиск уникальных значений с помощью функции UNIQUE

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

Синтаксис функции UNIQUE

Синтаксис функции UNIQUE:

Здесь диапазон (range) — это диапазон данных, из которых вы хотите извлечь уникальные записи.

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

Использование уникальной функции для поиска уникальных значений в Google Таблицах

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

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

Чтобы удалить дубликаты из предыдущего набора данных, необходимо воспользоваться функцией UNIQUE:

  • Нажмите кнопку «Назад».

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

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

Читайте так же:
Как подсчитать абсолютные значения больше / меньше заданного значения в Excel?

Также обратите внимание, что никакие изменения не могут быть внесены в вывод. Если вы попытаетесь это сделать, вы получите ошибку #REF.

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

Функция UNIQUE выводит уникальные данные в новом диапазоне данных, оставляя исходные данные неизменными.

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

Для этого выделите ячейки, содержащие результат, и скопируйте их. Затем используйте комбинацию клавиш CTRL + SHIFT + V (на PC) или CMD + SHIFT + V (на Mac), чтобы вставить значения из скопированных ячеек.

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

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

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