Объединение ячеек в 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 Таблицах (и объединяет ячейки с уникальными записями). Тем не менее, вы все еще отвечаете и должны показать формулы, где искать. Давайте посмотрим, как это работает в том же списке для просмотра.

  1. Я использую Google Sheets UNIQUE в E2 для проверки жанров в столбце A:

    =УНИКАЛЬНЫЙ(A2:A)
    Избавьтесь от повторов в жанрах.

    Формула возвращает список всех жанров независимо от того, повторяются они или нет в исходном списке. Другими словами, он удаляет дубликаты из столбца A.

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

  2. Затем я создаю следующую формулу с помощью функции JOIN Google Sheets:

    =СОЕДИНИТЬ(“, “,ФИЛЬТР(B:B,A:A=E2))
    Google Таблицы — объединяйте строки с одинаковым значением жанра.

    Как работают элементы этой формулы?

    • 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=’Комикс'”)
Google Sheets: используйте QUERY для удаления дубликатов.

Формула обрабатывает всю мою исходную таблицу (A1:C) и возвращает все столбцы (выберите *) для фильмов комиксов (где A=’Comic Book’).

Кончик. Я намеренно не указываю последнюю строку моей таблицы (A1:C) — чтобы формула оставалась гибкой и возвращала новые записи в случае добавления в таблицу других строк.

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

Пример 2

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

Я использую Google Sheets QUERY для удаления дубликатов и подсчета общей суммы денег, заработанных за фильм за все выходные. Я также отсортировал их по жанрам:

=ЗАПРОС(B1:D, “выбрать группу B,C, SUM(D) по B,C”)
Используйте QUERY для удаления дубликатов и объединения строк.

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

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

=ЗАПРОС(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 станет отличным выходом. 🙂

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

Помните мой список книг в магазине с несколькими сотнями строк? Посмотрим, как инструмент справится с этим.

Кончик. Поскольку утилита является частью Электроинструментыпожалуйста, сначала установите его и перейдите непосредственно к Слияние и объединение группа:
Группа «Объединить и объединить» в Power Tools.

Затем щелкните значок дополнения, чтобы открыть его:
Дополнение «Объединить ряды».

  1. После запуска надстройки выберите диапазон, в котором вы хотите объединить повторяющиеся строки:
    Выберите диапазон с вашими данными.
  2. Выберите те столбцы, которые содержат повторяющиеся значения. В моем случае они Фамилия а также Имя:
    Выберите ключевые столбцы с повторяющимися записями.
  3. Следующий шаг позволяет вам решить следующее:
    • столбцы со значениями, которые вы соберете
    • способы объединения этих записей: объединить или вычислить
    • разделитель для объединения ячеек с текстом
    • функция для вычисления чисел

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

    Что касается количества, я не против суммировать все книги на одного автора. Номера повторяющихся заголовков, если таковые имеются, будут суммироваться.
    Выберите столбцы со значениями для объединения.

  4. После настройки всех параметров нажмите «Готово». Дополнение выполнит свою работу и через несколько секунд покажет сообщение со всем обработанным:
    Результат объединения строк.

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

Кончик. Взгляните на то, как я использовал надстройку:
Объединяйте повторяющиеся строки с надстройкой для Google Таблиц.

Или посмотрите короткое видео, знакомящее с инструментом:

Используйте сценарии для полуавтоматического объединения дубликатов

Еще одна возможность, которую предлагает Combine Duplicate Rows, — это полуавтоматическое использование.

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

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

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

Я действительно призываю вас лучше познакомиться с этим инструментом и его возможностями, поскольку Google Таблицы «мрачны и полны ужасов», если вы понимаете, о чем я;)

Вас также могут заинтересовать

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *