Auditportal.ru

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

Как извлечь уникальные значения из нескольких столбцов в Excel?

Повторяющиеся строки в столбцах MS Excel и как с ними бороться

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

1. Устранение повторяющихся значений в Excel

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

1

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

Подтвердите свой выбор, нажав кнопку «OK». При обнаружении дубликатов они будут удалены, а на экране появится сообщение о количестве удаленных записей.

2

Таблица удаляется этой функцией при наличии дубликатов записей.

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

2. расширенный (дополнительный) фильтр для устранения дубликатов

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

3

В появившемся диалоге Advanced Filter необходимо активировать переключатель в строке Copy result to another location и в поле Source range указать нужный диапазон (в нашем случае столбец), в поле Place result указать диапазон, куда будет помещен результат фильтрации, и активировать флаг Unique values only. Подтвердите заданные команды кнопкой OK.

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

3. Условное форматирование в Excel

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

4

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

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

5

В общей сложности: Этот метод не дает четкой картины для больших объемов данных. Все имена авторов книг, в которых есть повторы, выделены одним цветом.

4. сводные таблицы для определения повторяющихся величин

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

6

Не забудьте нажать кнопку OK и продолжить перекрестную табуляцию на новом листе. В окне Список полей перекрестной табуляции отметьте все поля, как показано на рисунке. Имена выбранных полей появляются в полях Row Name и Value. Перетащите поле n/a в колонку «Имя строки».

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

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

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

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

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

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

Читайте так же:
Как искать по имени листа в Excel?

Столбики данных

Наша цель — найти и обозначить цветом повторы в столбцах Excel.

Шаг 1: Выделите весь участок.

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

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

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

Повторы данных

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

Настройки выделения

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

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

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

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

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

Столбик из имен футболистов

Далее мы поступаем следующим образом:

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

Дописываем второй столбик

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

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

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

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

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

Шаг 3: Выровняйте поля сводной таблицы следующим образом: первое (в моем случае «Игроки») — в области «Строки», второе («Значение2») — в области «Значения». Используйте обычное перетаскивание.

Перетаскиваем поля

Это должно быть так:

Строки и значения

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

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

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

Заключение

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

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

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

Вас устраивает то, как я работаю с данными? Или вы знаете лучше? Комментируйте и делитесь своими мыслями!

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

Имена людей, которые работали над конкретным проектом, находятся в шести колонках большой таблицы, которая у меня есть. Что-то вроде этого:

На другом листе я хотел бы получить такие результаты:

Это позволит мне определить все уникальные записи в этих шести столбцах и перенести их на другой лист. Моей первой мыслью было сделать это с помощью формул, и это сработало (я использовал COUNTIF в разделе MATCH для индексного сопоставления), но здесь 11 000 записей и 1 200+ имен, которые потенциально могут быть задействованы, и это занимало большую часть дня. VBA, как я предполагал, сделает это быстрее.

Мне показалось несколько вариантов. Я вернулся к этому посту: заполнил уникальные значения в массив VBA из Excel, посмотрел на ответ brettdj (потому что понял, куда он ведет) и в итоге получил следующий код:

Что отлично сработало для одного столбца (BE — столбец Name1 в таблице выше — Data — это лист, где хранятся данные, Crew — это лист, где я хочу получить уникальные значения). Однако я не знаю, как заставить его принимать значения из нескольких столбцов (от BE до BJ).

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

Тем не менее, мне не удалось заставить его объединить информацию из нескольких столбцов в один. С третьей попытки я посмотрел на ответ студента Гэри «Как извлечь уникальные значения из двух столбцов Excel VBA» и попробовал сделать следующее:

(Я попробовал с двумя колонками только в этом случае, чтобы проверить, так ли я его понял, но не понял).

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

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

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

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

4 ответа

  • Как получить уникальные значения для колонки в Laravel 5.2?

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

Предположим, у меня есть кадр данных pandas с 2 столбцами: df: Col1 Col2 1 1 1 1 2 1 2 1 2 2 3 4 3 4 Затем я хочу сохранить только уникальные значения пары (col1, col2) этих двух столбцов и дать их частоту: df2: Col1 Col2 Freq 1 1 1 1 2 3 3 4 2 Я думаю использовать df[‘Col1’, ‘Col2’].value_counts() .

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

Предполагая, что у вас есть Excel 2016 и выше, вы можете сделать это с помощью Power Query. Преобразуйте диапазон данных в таблицу, выберите ячейку в таблице, выберите «From Table» в Data > Get & Transform, а затем вставьте следующий код в Расширенный редактор редактора запросов Power (измените Table3 на любое имя таблицы).

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

Я новичок в pandas. У меня простой вопрос: как извлечь уникальные значения и их количество из столбца и сохранить в datarange с индексным ключом Я пробовал: df = df1[‘Genre’].value_counts() и я получаю серию, но я не знаю, как преобразовать ее в объект Datarange.

Подскажите, пожалуйста, как объединить уникальные значения из каждого столбца в кадре данных (как числовые, так и строковые значения)? a = c(a, b, c, d, a) b = c(1, 2, 3, 4, 3) df <- cbind(a, b) Желаемый результат: переменная Уровень a a a b a c a d 1 b 2 b 3 b 4 Вышеприведенный результат.

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

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

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

Похожие вопросы:

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

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

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

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

Предположим, у меня есть кадр данных panda с 2 столбцами: df : Col1 Col2 1 1 1 1 2 1 2 1 2 1 2 2 2 2 3 3 4 3 4 Далее, я хочу хранить только уникальные значения пары (col1, col2) этих двух столбцов и дать им.

У меня простой вопрос: как извлечь уникальные значения и их количество из столбца и сохранить их во фрейме данных с индексирующим ключом? Я пробовал: df = df1[‘Genre’].value_counts().

Как можно извлечь уникальные значения из каждого столбца рамки данных (числовые значения и строки) и объединить их в один столбец? a = c(a, b, c, d, a) b = c(1, 2, 3, 4, 3) df <- cbind(a, b)

У меня есть набор данных с групповыми переменными и X1:X1000. Для каждого столбца в X1:X1000 я хочу отфильтровать строки, содержащие только значение 0, а затем вернуть уникальные значения групповой переменной w.

Я могу извлекать значения unqiue из одного столбца (sheet1) и вставлять в другой столбец (sheet2) Sub Test() Dim Sh1 As Worksheet Dim Rng As Range Dim Sh2 As Worksheet Set Sh1 = Worksheets(Sheet1).

У меня есть лист под названием «Список студентов», который содержит столбец под названием «Название должности», и я хочу извлечь уникальные значения из этого столбца в другой столбец на другом листе, но с условием НЕ ИСПОЛЬЗОВАНО.

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