Как выделить дубли в гугл таблице

Google Таблицы. Поиск дубликатов

У меня вопрос по Google Таблице. У меня есть данные. Мне необходимо выделить повторяющееся значения в определенных столбцах. Сразу оговорю что это Фамилии. Я точно знаю что повторы есть и мне надо выяснить какие. Как это сделать? Если я не знаю какие именно значения у меня у меня повторяются.

Лучший ответ

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

Главное меню Таблицы — Формат — Условное форматирование .
справа «Форматирование ячеек» выбираем в самом низу «Ваша формула» и вставляете :

=AND(NOT(ISBLANK(A1)); COUNTIF($A$1:$F; » отсекает» пустые ячейки NOT(ISBLANK(A1));
COUNTIF($A$1:$F; » //otvet.imgsmail.ru/download/8198039_53c6620ea1c63b9ea551f52309b7c954_800.png» alt=»» data-lsrc=»https://otvet.imgsmail.ru/download/8198039_53c6620ea1c63b9ea551f52309b7c954_120x120.png» data-big=»1″>

ДенисУченик (227) 4 года назад

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

Коран по поискам дублей в Google Spreadsheet

Помните ли вы овцу Долли? При работе с документами google, особенно со spreadsheet (MS Excel) таблицами для решения многих нетипичных задач, таких как: специальное форматирование по множеству условий или удаление дубликатов, необходимо было научится пользоваться JavaScript, так как не все можно исполнить с помощью стандартных функций, а по формулам есть ограничения, которые раскроем в следующем выпуске. Теперь же представляем ликбез по поиску дублей ссылок и текста в Google Spreadsheet.

Поэтому начнем писать скрипты для Google Spreadsheet. Если вы знаете несколько языков программирования хотя бы поверхностно, вам, конечно, будет легче. На JavaScript я начал писать без проблем, но работал в основном с VBA, Visual basic, C/C++, C#. Как говорится, всего понемногу и везде по чуть-чуть. Ежели знания языков программирования хромают, то добро пожаловать на наш ликбез.

Введение

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

Поставим задачу
Поставим задачу — найти в текстовом списке дубликаты и удалить их, создав резервную копию списка.
Создадим новый документ и назовем его “Поиски дублей”, страницу назовем так же. Искать дубликаты будем по 1-й колонке. Для того, чтобы было удобнее, пропишем название столбцов и закрепим их. Для того, чтобы закрепить строку или столбец, нужно потянуть за соответствующую жирную линию, при этом она переместится на 1 строку вниз или на 1 столбец вправо (Рисунок 1).

Рисунок 1. Закрепление столбцов и строк.

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

Создадим еще 2 листа “Рабочая страница” на которой мы будем проводить манипуляции с данными. “Копия” — лист для копии начальных данных, в случае неправильной работы скрипта или потери данных (Рисунок 2).

Рисунок 2. Создание 2-х листов.

Приступаем к написанию кода. Нажимаем в верхнем ниспадающем меню Инструменты -> Редактор скриптов (Рисунок 3).

Рисунок 3. Открытие редактора скриптов в Google Spreadsheet.

Перед нами в новом окне откроется редактор скриптов с вариантами создания скриптов
(Рисунок 4)

Рисунок 4. Варианты создания скриптов в Редакторе скриптов.

Если вы выберете любой из пунктов слева (создание соответствующего скрипта для Диска, Почты и т.д.), то увидите вступительный скрипт с подробными объяснениями каким образом работать с тем или иным скриптом, Но это только показательные скрипты. Но мы выберем “Пустой проект” и увидим только начало всех скриптов

function myFunction()<>

Стандартное название проекта — это “Проект без названия”, я же поменял название кликнув на эти слова (верхняя строка на рисунке) и назвал его “Поиски дубликатов” (Рисунок 5). Это для того, чтобы потом при наличии 10 и более скриптов, можно было их различать и без особых усилий находить нужный нам скрипт.

Рисунок 5. “Пустой проект” с первыми стандартными строками кода и измененным названием

В фигурных скобках и будем писать наш скрипт.

Углубляться в изучение JavaScript мы особо не будем, но чтобы понял даже новичок, по ходу написания кода, я буду подробно комментировать происходящее.
Пишем алгоритм
Перед тем, как приступить к написанию любого автоматизирующего скрипта (задачи), нам нужно нарисовать для себя алгоритм действий. И пусть многим это может показаться бредом и тратой лишнего времени и, возможно, вы посчитаете данные действия лишними, но это очень важный этап работы. При работе с большими проектами это очень важно, так как запутаться можно и в 3-х соснах — алгоритм решает данную проблему. Это касается не только JavaScript, это касается абсолютно любого языка, да и вообще любых действий. Для понимания приведем пример на основе получения чашки чая (Рисунок 6).

Рисунок 6. Алгоритм действий получения чашки чая (пример).

Надеюсь, вы заварили чашечку 🙂 и с хорошим настроением читаете дальше.

Нарисуем алгоритм для нашего скрипта по поиску дубликатов (Рисунок 7), он будет более подробный чем пример с чашкой чая.

Рисунок 7. Алгоритм для задачи по поиску дубликатов

Внесу небольшое пояснение, это уже окончательный вид алгоритма, поэтому в нем присутствуют 2 блока которые, возможно не сразу будут понятны для обычного пользователя.
Блок 2 — касается удаления контента и комментариев со страницы, на странице, которая, только что создана и совсем ничего не содержит. Этот блок нужен при повторном использовании скрипта, поэтому мы сразу закладываем данную функцию в скрипт. Об этом более подробно будет написано ниже.
Блок 5 — создание массива и конвертация, об этом будет рассказано подробно ниже. Сейчас же скажу, что созданный массив будет основным элементом обработки данных.
Кодин
Объявим переменные для наших листов, чтобы программа знала, как к ним обращаться.

Для объявления переменной в текущей области видимости используется ключевое слово var. Причем, особенностью языка javascript, как впрочем, и многих других, таких как php, является то, что не нужно специально объявлять тип переменной (как например, в C#: число объявляется integer; строка — string и т.д.).
Для того, чтобы описывать строки кода внутри самого кода, мы будем использовать комментарий в виде текста, который начинается с двух прямых слешей “//”. В разных языках программирования теги комментариев выглядят по разному и представляют из себя последовательность некоторых символов, вот несколько из них: (см. Таблицу 1.)

Таблица 1. Сравнительная таблица тегов комментариев в некоторых языках

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

Рисунок 8. Иллюстрация подсветки синтаксиса кода.

Объявим три переменные для наших листов:
переменная sheet_work_page для страницы с названием “Рабочая страница”, сюда мы будем помещать текст для поиска и после работы получать результат.
переменная sheet_find_dubles для страницы с названием “Поиск дубликатов”, на этой странице будет проходить обработка дубликатов.
переменная sheet_copy для страницы с названием “Копия”, создается копию данных перед началом обработки, так как никогда не знаешь, когда понадобится оригинал.

 var sheet_work_page = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Рабочая страница'); var sheet_find_dubles = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Поиск дубликатов'); var sheet_copy = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Копия'); А также переменные для работы. var i,Page_Last_Row,k,archurls; переменные i, k - счетчики циклов, так как имена не несут большую смысловую нагрузку, не будем им присваивать имена покрасивее. переменная Page_Last_Row - последняя строка листа, для которого, эта переменная будет объявлена. переменная archurls - массив для хранения и перебора значений. Для каждой новой обработки нам необходимо удалять контент и комментарии со страниц “Рабочая страница” и “Копия”, так как наш скрипт будет оперировать с комментариями. Это операция предотвращает попадание в конечный результат данных, которые могли остатся от предыдущих использований скрипта. Например: Вы, сначала работали с 1000 строк, а потом перешли на 20 строк, не удалив предыдущий результат, Вы в итоге получите 1000 строк, а не 20. Эту операцию на страницах можно выполнить так: удалить содержимое, нажав клавишу delete, а комментарии через контекстное меню соответствующей командой. При простом удалении, например, при нажатии клавиши delete, комментарии остаются. Также можно воспользоватся клавиатурой: Встать на вторую строку, нажать клавиши Shift + Space(пробел) так мы выделим всю строку, далее Shift+Ctrl+Arrow_Down (стрелка вниз) (выделить все до конца листа) и через контекстное меню "Удалить строки". Получаем девственно чистый лист с шапкой. Рисунок 9 Правильно удаляем примечания. Для удаления Нам необходимо знать границы диапазона где производится операция чистки. Это нужно для того чтобы, например, оставить шапку в документе нетронутой, либо если нам необходимо затронуть лишь часть данных, а также это необходимость формулировки языков программирования - знать рабочий диапазон. Для нашего диапазона за основу берем диапазон ячеек “A2:D номер последней строки” Номер последней строки - количество строк листа “Поиски дубликатов”. Диапазон начинается с ячейки A2 так как первая строка или ячейка A1 находится в закреплении для обозначения шапки листа. А работать с закрепленной областью скритп не умеет. Также вид A2:Dномер последней строки можно записать как (2,1,номер последней строки,4) от этого ничего не поменяется, подробнее об этом Вы можете прочитать здесь. на русском языке на английском языке Напишем код для всего, что было сказано выше об удалении примечаний и содержимого, это всего лишь 4 строки. sheet_work_page.getRange("A2:D" + sheet_find_dubles.getLastRow()).clearContent(); sheet_work_page.getRange("A2:D" + sheet_find_dubles.getLastRow()).clearComment(); sheet_copy.getRange("A2:D" + sheet_find_dubles.getLastRow()).clearContent(); sheet_copy.getRange("A2:D" + sheet_find_dubles.getLastRow()).clearComment();

Опишем немного подробнее:
sheet_work_page — символьное имя, которое мы присвоили листу “Рабочая страница”.
getRange(первая строка, первый столбец, последняя строка, последний столбец) — указание диапазона для совершения дальнейших действий.
clearContent() — удаление содержимого в ячейках, указанных в диапазоне.
clearComment() — удаление примечаний в ячейках, указанных в диапазоне.

Создаем переменную datatocopy и выделяем диапазон ячеек с данными A2:D последняя строка в листе “Поиск дубликатов” и копируем на страницы “Рабочая страница” и “Копия”.
Особенность данной операции в том, что вставить скопированый диапазон можно только при выделении соответствующего диапазона.

 var datatocopy = sheet_find_dubles.getRange("A2:D" + sheet_find_dubles.getLastRow()).getValues(); sheet_work_page.getRange(2,1,sheet_find_dubles.getLastRow()-1,4).setValues(datatocopy); var datatocopy1 = sheet_find_dubles.getRange("A2:D" + sheet_find_dubles.getLastRow()).getValues(); sheet_copy.getRange(2,1,sheet_find_dubles.getLastRow()-1,4).setValues(datatocopy1);

переменной Page_Last_Row присваиваем значение последней строки страницы “Поиск дубликатов”

 Page_Last_Row = sheet_find_dubles.getLastRow();

В объявленный ранее массив archurls присваиваем все данные из первой колонки листа “Поиск дубликатов”.

 archurls = sheet_find_dubles.getRange(2, 1, Page_Last_Row-1, 1).getValues();

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

for (i=0; i

Чтобы лучше понять механизм действий и составить правильное представление, рассмотрим скриншот “Как выглядят данные в массиве archurls” рисунок 8

Рисунок 8 Как выглядит массив со значениями изнутри.

Переменной i, которая в данном случаем будет использоваться для порядкового номера проверяемой строки листа “Рабочая страница”, присваиваем значение 2-й строки, так как 1-я у нас является названиями столбцов, i = 2;

И переходим к написанию главного цикла по поиску дубликатов. Объявляем цикл while (цикл, условие которого проверяется перед началом операций всего цикла) и в нем перебираем построчно значения из листа “Рабочая страница”, сравнивая со значениями листа “Поиск дубликатов”.

 while (i = 0) < if ((k+2)==i) < sheet_work_page.getRange(i, 4).setComment('Найдено совпадение со строкой ' + (k+1) + ' архива'); i++; continue; >else < sheet_work_page.getRange("A" + i + ":C" + i).clearContent(); i++; delete_count++; continue; >> >

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

Сортируем, если есть такая необходимость.
Описание: Лист.sort(номер колонки, true/false) true — от А до Я, false — от Я до А

 sheet_work_page.sort(3, true);

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

 function onOpen() < SpreadsheetApp.getActiveSpreadsheet().addMenu("Поиск дубликатов?", []); >

Добавляем меню с названием “Копаем?” указываем название подменю “Копаем!” и присваиваем кнопке выполнение функции той, что мы написали выше:check_duplicates_one_sheet (рисунок 9)

Рисунок 9

Можете зайти в наш документ описанный в этой статье.
Дополнение:
“А если необходимо просто удалить дубликаты из колонки А, быстренько и без глупостей?”, — спросите вы.
Что ж напишем более скоростной скрипт.

 function removeDuplicates() < //объявим наш листик. var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Ускоренная чистка дублей для одной колонки"); //получаем массив данных из нашего листа. var data = sheet.getDataRange().getValues(); //объявим новый массив для результатов. var newdata = new Array(); //проверяем на дубли for(i in data)< var row = data[i]; var duplicate = false; for(j in newdata)< if(row[0] == newdata[j][0])< duplicate = true; >> //обновляем строку if(!duplicate) < newdata.push(row); >> //очищаем содержимое ячеек с исходного листа sheet.clearContents(); //вставляем в лист новый отчищенный массив sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData); //сортируем по возрастанию sheet.sort(1, true); >

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

До новых встреч! Ваш покорный слуга.

Также можете посетить наши предыдущие статьи:
Талмуд по формулам в Google SpreadSheet
Рассылка писем в Google Docs (Drive)
ждите наши следующие выпуски.

Как найти и выделить дубликаты в Google Sheets

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

Настройка формулы условного форматирования

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

один раз сделали свой выбор, нажмите «Формат» > «Условное форматирование» в меню приложения Google Sheets.

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

В разделе “ Правила форматирования», щелкните раскрывающийся список и выберите «Пользовательская формула».

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

ДИАПАЗОН: введите диапазон столбцов, который вы ранее выбрали. Для простоты мы рекомендуем использовать диапазон столбцов, а не диапазон ячеек. Гораздо проще ввести A:A для обозначения всего столбца A, чем $A$1:$A$1000 для обозначения всех ячеек. В этом примере, поскольку я выбрал строку A, я использую A:A. Если бы я выбрал столбец B, я бы выбрал B:B и т. д. КРИТЕРИИ. Введите первую ячейку в выбранном столбце, содержащую данные. В моем примере первой ячейкой с данными является A1, поэтому я буду использовать ее для своих критериев.

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

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

Мне просто нужны шаги!

1. Откройте электронную таблицу

2. Выберите ячейки , в которых вы хотите найти дубликаты

3. Нажмите Формат > Условное форматирование

4. Выберите вкладку Один цвет на этой боковой панели и убедитесь, что диапазон в поле «Применить к диапазону» соответствует выбранным вами ячейкам

5. В разделе «Правила форматирования» нажмите раскрывающееся меню и выберите «Пользовательская формула»

6. Введите приведенную ниже настраиваемую формулу =СЧЁТЕСЛИ(ДИАПАЗОН,КРИТЕРИИ)>1, где ДИАПАЗОН — это столбец выбранных вами значений, а КРИТЕРИИ — это первый столбец. ячейка с данными.

7. Настройте стиль форматирования по своему вкусу

8. Нажмите Готово

Ограничения использования этого метода

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

Вы можете редактировать и удалять это условное форматирование по мере необходимости, и хотя оно не идеально, оно превосходит решение”все или ничего”, которое в настоящее время предоставляет Google Sheet.

Документы и Диск Google

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

Задача

Необходимо выделить цветом те ячейки, в которых значения повторяются более одного раза.
Главное меню Таблицы - Формат - Условное форматирование .

Необходимо указать, что за контролем форматирования будет следить ваша формула, и указать ее:

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

Главное меню Таблицы - Формат - Условное форматирование .

=COUNTIF($A$1:$F; " https://docs.google.com/spreadsheets/d/1n4CkKgDdSNn3OFFD-eluGSAms-nupJreDkVx8lqLHyY/edit?usp=sharing" target="_blank" >Повторяющиеся значения в Таблице Google .

Корректной выборки!
Не удается вставить формулу? Посмотрите Как правильно писать формулы в Таблицах Google.

Получить ссылку
Facebook
Twitter
Pinterest
Электронная почта
Другие приложения

А вот интересно, можно ли сравнивать значения с разных листов? Есть второй лист с текстовыми данными. Надо, чтобы в первом листе подсвечивались данные, которые встречаются во втором листе. В первом листе могут быть повторяющиеся значения. Их выделять мне не нужно. Ответить Удалить

Да, для этого необходимо ссылаться на данные соседнего листа через формулу INDIRECT. Удалить

=И(НЕ(ЕПУСТО(A1)), СЧЁТЕСЛИ($A$1:$G, "=" ДВССЫЛ("Чёрныйсписок!"&A1) > 1))
Эта формула выдаёт ошибку. Не пойму что не так.
"сравнивает в диапазоне $A$1:$F количество значений равных A1 с единицей" Вот эту фразу вообще не понял. Что за значения равные А1? Удалить

Действительно, что-то запутано. Можете привести пример в Таблице? Я пока вижу две ошибки: (1) запятые, вместо точек с запятой, (2) сложная интерпретация "Чёрныйсписок!"&A1, которая, возможно, посто неверная. Удалить

Добрый день, можете пожалуйста привести пример, где сравнение идет с конкретным столбцом на другом листе, а не по всему другому листу?

Присоединюсь к просьбе Удалить

Пожлуйста, приводите свои примеры Таблиц с минимальными данными. Для каждого просто невозможно создавать одно и тоже. Проявите инициативу. Как сделать промер https://contributor.pw/post/why-you-should-create-an-example/ Удалить

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

Например: Есть столбец с номерами договоров и нужно выделить повторяющиеся, но при этом в этом же столбце есть значения "б/н" и "на почту", которые не нужно учитывать при выделении.

Необходимо расширить проверку условия COUNTIF до нескольких параметров. Это можно сделать несколькими способами. Приведите пример Таблицы. Удалить

Что то не получается! Точно знаю что есть одно повторения в столбце из 50 ячеек, функция "удалить дубликаты" убирает 1 ячейку и смещает вверх столбец, что тоже скверное решение(((( Почему не работает? Просто окрашивает весь столбец в серый цвет( Ответить Удалить

Лучше с примером. Удалить

Добрый день. Подскажите пожалуйста, например, у меня в ячейке А1 - "Номер 1", а в ячейке В1 - "22.07.2019" (Любая дата). Как сделать подсветку если, и в столбце "А" и в столбце "В" есть совпадения и по Номеру и по Дате (А3 "Номер 1", В3 "22.07.2019? Надеюсь понятно написал) Ответить Удалить

Буду рад помочь, если будет пример https://contributor.pw/post/why-you-should-create-an-example/ Удалить

Спасибо огромное! Очень выручили Ответить Удалить

Добрый день. Подскажите как исключить из сравнения определенный текст из столбца. На пример мы имеет столбец в котором идут значения
vk.com\id1
vk.com\id2

И он выделяет их как совпадение ибо есть начальное совпадение. А мне необходимо что бы он сравнивал только то что идет после ID
Заранее спасибо Ответить Удалить

Здравствуйте. Нужен пример. У меня ничего не выделяет https://docs.google.com/spreadsheets/d/1n4CkKgDdSNn3OFFD-eluGSAms-nupJreDkVx8lqLHyY/edit#gid=1118376404 Удалить

Вот пример. Формула скопирована с текста в вашем посте.
https://docs.google.com/spreadsheets/d/1sBgGhD7L3fRLPDTR6WainlqRXxBrOq5F6uebiab0ghE/edit?usp=sharing

Я обнаружил, что у вас там было два правила условного форматирования. Первое перекрывало второе. Удалить

Формула для колонки отличается от формулы для диапазона. Должно быть =AND(NOT(ISBLANK(D1)); COUNTIF($D$1:$D; "=" &D1) > 1) для колонки D. Поправил у вас в Таблице. Удалить

Попробовала, у меня не работает Удалить

К сожалению, без вашего примера нет возможности представить, что там пошло не так. Пришлите пример. Удалить

Работает. Спасибо большое ! Ответить Удалить

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