Объединение ячеек в Google Sheets из нескольких строк в одну строку на основе значения столбца
Объединение повторяющихся строк в ваших электронных таблицах может превратиться в одну из самых сложных задач. Давайте посмотрим, чем могут помочь формулы Google, и познакомимся с одним умным дополнением, которое сделает всю работу за вас.
Функции для объединения ячеек с одинаковым значением в Google Sheets
Вы же не думали, что в Google Таблицах не хватает функций для такого рода задач, не так ли? 😉 Вот формулы, которые вам понадобятся для объединения строк и удаления повторяющихся ячеек в электронных таблицах.
СЦЕПИТЬ — функция Google Sheets и оператор для объединения записей
Первое, что приходит на ум, когда я думаю не просто об удалении дубликатов, а о объединении повторяющихся строк, — это функция Google Sheets CONCATENATE и амперсанд (&) — специальный оператор конкатенации.
Предположим, у вас есть список фильмов для просмотра, и вы хотите сгруппировать их по жанрам:
- Вы можете объединять ячейки в Google Таблицах только с пробелами между значениями:
=СЦЕПИТЬ(B2,” “,C2,” “,B8,” “,C8)
=B2&” “&C2&” “&B8&” “&C8
- Или используйте пробелы с любыми другими отметками, чтобы объединить повторяющиеся строки вместе:
=СЦЕПИТЬ(A3,”: “,B3,” (“,C3,””), “,B6,” (“,C6,”) “)
=A3&”: “&B3&”(“&C3&”), “&B6&”(“&C6&”)
Как только строки будут объединены, вы можете избавиться от формул и оставить только текст на примере этого урока: Преобразование формул в значения в Google Sheets
Каким бы простым ни казался этот путь, он явно далек от идеала. Это требует, чтобы вы знали точное положение дубликатов, и именно вы должны указать их формуле. Итак, это может работать для небольших наборов данных, но что делать, когда они становятся больше?
Объединяйте ячейки, но сохраняйте данные с помощью UNIQUE + JOIN
Этот тандем формул находит за вас дубликаты в Google Таблицах (и объединяет ячейки с уникальными записями). Тем не менее, вы все еще отвечаете и должны показать формулы, где искать. Давайте посмотрим, как это работает в том же списке для просмотра.
- Я использую Google Sheets UNIQUE в E2 для проверки жанров в столбце A:
=УНИКАЛЬНЫЙ(A2:A)
Формула возвращает список всех жанров независимо от того, повторяются они или нет в исходном списке. Другими словами, он удаляет дубликаты из столбца A.
Кончик. Если вы добавите больше значений в столбец A, формула автоматически дополнит список уникальными записями.
- Затем я создаю следующую формулу с помощью функции JOIN Google Sheets:
=СОЕДИНИТЬ(“, “,ФИЛЬТР(B:B,A:A=E2))
Как работают элементы этой формулы?
- FILTER сканирует столбец A на предмет всех экземпляров значения в E2. После обнаружения он извлекает соответствующие записи из столбца B.
- JOIN объединяет эти значения в одну ячейку запятой.
Скопируйте формулу вниз, и вы получите все заголовки, отсортированные по жанрам.
Примечание. Если вам также нужны годы, вам нужно будет создать формулу в соседнем столбце, поскольку JOIN работает с одним столбцом за раз:
=СОЕДИНИТЬ(“, “,ФИЛЬТР(C:C,A:A=E2))
Таким образом, эта опция предоставляет Google Sheets несколько функций для объединения нескольких строк в одну на основе дубликатов. И это происходит автоматически. Ну, почти. Я намерен придержать идеальное решение до самого конца статьи. Но не стесняйтесь прыгай к нему сразу 😉
Функция QUERY для удаления повторяющихся строк в Google Sheets
Есть еще одна функция, помогающая оперировать огромными таблицами – QUERY. Сначала это может показаться немного сложным, но как только вы научитесь им пользоваться, оно станет вашим верным помощником в работе с электронными таблицами.
Вот сама функция QUERY:
=ЗАПРОС(данные, запрос, [headers])
Как это работает:
- data (обязательно) — диапазон вашей исходной таблицы.
- запрос (обязательно) — набор команд для определения условий для получения конкретных данных.
Кончик. Вы можете получить полный список всех команд здесь.
- заголовки (необязательно) — количество строк заголовков в исходной таблице.
Проще говоря, Google Sheets QUERY возвращает некоторые наборы значений на основе указанных вами условий.
Пример 1
Я хочу получать только фильмы по комиксам, которые мне еще предстоит посмотреть:
=ЗАПРОС(A1:C,”выберите *, где A=’Комикс'”)
Формула обрабатывает всю мою исходную таблицу (A1:C) и возвращает все столбцы (выберите *) для фильмов комиксов (где A=’Comic Book’).
Кончик. Я намеренно не указываю последнюю строку моей таблицы (A1:C) — чтобы формула оставалась гибкой и возвращала новые записи в случае добавления в таблицу других строк.
Как видите, он работает аналогично фильтру. Но на практике ваши данные могут быть намного больше — с числами, которые вам, возможно, придется вычислить.
Пример 2
Предположим, я провожу небольшое исследование и отслеживаю кассовые сборы новейших фильмов в кинотеатрах по выходным:
Я использую Google Sheets QUERY для удаления дубликатов и подсчета общей суммы денег, заработанных за фильм за все выходные. Я также отсортировал их по жанрам:
=ЗАПРОС(B1:D, “выбрать группу B,C, SUM(D) по B,C”)
Примечание. Для группа по команду, вы должны перечислить все столбцы после Выбратьиначе формула не будет работать.
Вместо этого, чтобы отсортировать записи по фильму, я могу просто изменить порядок столбцов для группа по:
=ЗАПРОС(B1:D, “выбрать группу B,C, SUM(D) по C,B”)
Пример 3
Предположим, вы успешно управляете книжным магазином и отслеживаете все книги, которые есть в наличии во всех ваших филиалах. Список доходит до сотен книг:
- Из-за ажиотажа вокруг серии книг о Гарри Поттере вы решаете проверить, сколько у вас осталось книг, написанных Дж. К. Роулинг:
=ЗАПРОС(‘Копия В наличии’!A1:D,”выберите A,B,C,D, где A=’Роулинг'”)
- Вы решаете пойти дальше и оставить только серию книг о Гарри Поттере, исключив другие сказки:
=QUERY(‘В наличии’!A1:D,”выберите A,B,C,D, где (A=’Роулинг’, а C содержит ‘Гарри Поттер’)”)
- С помощью функции ЗАПРОС Google Таблиц вы также можете подсчитать все эти книги:
=ЗАПРОС(‘В наличии’!A1:D,”выберите A,B, sum(D) где (A=’Роулинг’ и C содержит ‘Гарри Поттер’) сгруппируйте по A,B”)
Думаю, теперь вы получили представление о том, как функция ЗАПРОС «удаляет дубликаты» в Google Таблицах. Хотя это доступный всем вариант, для меня он больше похож на окольный способ объединения повторяющихся строк.
Более того, пока вы не научитесь используемые им запросы и правила их примененияфункция не сильно поможет.
Самый быстрый способ объединить повторяющиеся строки
Когда вы теряете всякую надежду найти простое решение для объединения нескольких строк на основе дубликатов, наше дополнение для Google Sheets станет отличным выходом. 🙂
Объединение повторяющихся строк сканирует столбец с повторяющимися записями, объединяет соответствующие ячейки из других столбцов, разделяет эти записи разделителями и объединяет числа. Все одновременно и в несколько кликов мышью!
Помните мой список книг в магазине с несколькими сотнями строк? Посмотрим, как инструмент справится с этим.
Кончик. Поскольку утилита является частью Электроинструментыпожалуйста, сначала установите его и перейдите непосредственно к Слияние и объединение группа:
Затем щелкните значок дополнения, чтобы открыть его:
- После запуска надстройки выберите диапазон, в котором вы хотите объединить повторяющиеся строки:
- Выберите те столбцы, которые содержат повторяющиеся значения. В моем случае они Фамилия а также Имя:
- Следующий шаг позволяет вам решить следующее:
- столбцы со значениями, которые вы соберете
- способы объединения этих записей: объединить или вычислить
- разделитель для объединения ячеек с текстом
- функция для вычисления чисел
Для меня я хотел бы, чтобы все книги, принадлежащие одному автору, были сведены в одну ячейку и разделены разрывными линиями. Если какие-либо заголовки повторяются, дополнение покажет их только один раз.
Что касается количества, я не против суммировать все книги на одного автора. Номера повторяющихся заголовков, если таковые имеются, будут суммироваться.
- После настройки всех параметров нажмите «Готово». Дополнение выполнит свою работу и через несколько секунд покажет сообщение со всем обработанным:
Инструмент объединил повторяющиеся строки в моем списке книг. Вот часть того, как мои данные выглядят сейчас:
Кончик. Взгляните на то, как я использовал надстройку:
Или посмотрите короткое видео, знакомящее с инструментом:
Используйте сценарии для полуавтоматического объединения дубликатов
Еще одна возможность, которую предлагает Combine Duplicate Rows, — это полуавтоматическое использование.
Если вы часто проходите шаги и выбираете одни и те же параметры, вы можете сохранить их в сценарии. Сценарии позволяют без особых усилий повторно использовать одни и те же настройки в одних и тех же или разных наборах данных.
Вам нужно будет дать вашему сценарию имя и указать лист и диапазон, который он должен обрабатывать:
Сохраненные здесь настройки можно быстро вызвать из меню Google Sheets. Надстройка сразу же начнет объединять повторяющиеся строки, сэкономив вам дополнительное время:
Я действительно призываю вас лучше познакомиться с этим инструментом и его возможностями, поскольку Google Таблицы «мрачны и полны ужасов», если вы понимаете, о чем я;)