Как объединить ячейки в сводной таблице excel
В этой статье рассмотрим, как объединять ячейки в таблице Excel, а так же как объединить текст и другие типы данных в ячейках без потери данных.
В Excel существует возможность объединения области из нескольких ячеек или столбцов в одну, а так же объединять данные в ячейках через формулы и Мгновенное заполнение , о котором вы можете узнать в отдельной статье.
Как объединить ячейки
Объединить несколько ячеек (строк/столбцов) в Excel очень просто. Способ применяется для объединения пустых ячеек или ячеек, среди которых только одна содержит данные, например шапка таблицы.
Для объединения выделите область ячеек, которые нужно объединить и нажмите на вкладке Главная (Выравнивание) подходящий вариант:
Результат объединения выделенных ячеек:
Использование объединения ячеек таким способом не самый лучший вариант, для дальнейшей работы с данными, например сортировка и фильтрация, создание сводных таблиц. Старайтесь использовать его в исключительных случаях, а лучше никогда.
Подобный эффект, но без последствий, даёт форматирование ячеек ( Ctrl+1 ), вкладка Выравнивание ► по горизонтали ► по центру выделения :
Если необходимо объединить несколько ячеек содержащих данные, то стандартными средствами сделать это не получится, о чем Excel вас любезно предупредит:
Но вы настойчивы, и вот результат, мы потеряли часть данных:
Нет не решаемых задач. На помощь придет макрос, который поможет объединить данные из разных ячеек (строк/столбцов) в одну общую без потери данных:
Результат работы макроса, объединенный текст в одной ячейке:
Как объединить ячейки при помощи формул
1. Самый простой способ объединить данные в ячейках – использование знака конкатенации & (амперсанд):
Ссылка на ячейки с добавлением оператора & , приведет к склейке данных без разделителя. Добавить разделитель можно используя в формуле необходимый разделитель заключённый в кавычки.
- пробел " ";
- запятая и пробел ", " ;
- точка с запятой ";" и т.д.
2. Следующий вариант объединить строки – функция СЦЕПИТЬ (CONCATENATE), одна из текстовых функций, созданная для объединения двух или нескольких текстовых строк в одну.
Начиная с Excel 2016 является пока ещё рабочим атавизмом и используется в рамках совместимости версий.
Синтаксис функции:
СЦЕПИТЬ(текст1;[текст2];…) , где текст1 , ссылка на первую ячейку с данными или текстовую константу, соответственно [текст2] ссылка на следующую ячейку или текстовую константу и т.д. Объединять можно текстовое значение, число (дату) или ссылки на ячейки.
Формула СЦЕПИТЬ может содержать до 255 элементов и до 8 192 символов.
Функция склеивает текст без разделителей, устраняется проблема, добавлением между аргументами разделителя в кавычках.
При объединении текста проблем не возникнет, но в случае объединения чисел или дат результат может отличаться от ожиданий.
Например, требуется объединить текст и число в ячейке с формулой, округленное до целого числа или добавить дату.
Число присоединится так, как хранится в памяти программы, дата является числом, потому покажет себя без формата.
Чтобы задать нужный формат числу или дате после объединения, необходимо добавить функцию ТЕКСТ :
Недостаток объединения данных при помощи оператора & и функции СЦЕПИТЬ , состоит в том, что все части текста нужно указывать отдельными аргументами, поэтому соединение большого числа ячеек является весьма утомительным делом.
Решение появляется начиная с Excel 2016, в арсенал которого вошли две новых функции СЦЕП (CONCAT) и ОБЪЕДИНИТЬ (TEXTJOIN), речь о которых пойдет ниже.
3. Функция СЦЕП , полностью выполняющая функционал предшественника, но работает и с диапазонами данных.
Синтаксис функции:
СЦЕП(текст1; [текст2]; …) , где текст – элемент, который нужно присоединить: строка или массив строк, например диапазон ячеек.
Формула СЦЕП может содержать до 253 аргументов.
Если объединенная строка содержит свыше 32767 символов (ограничение на ввод данных для ячейки), возникнет ошибка ЗНАЧ!.
Работает без проблем, но если требуется соединить диапазон с разделителем, СЦЕП не поможет.
4. Лучший вариант для объединения – функция ОБЪЕДИНИТЬ , объединяет текст из нескольких диапазонов и/или строк, вставляя между текстовыми значениями указанный пользователем разделитель.
Синтаксис функции:
ОБЪЕДИНИТЬ(разделитель; игнорировать_пустые; текст1; [текст2]; . ) , где
- Разделитель – символ, который будет вставлен между фрагментами, указывается в кавычках;
- игнорировать_пустые – если 0, то пустые ячейки включаются, если 1 – игнорируются ;
- Текст. - диапазон ячеек, содержимое которых нужно склеить.
Формула ОБЪЕДЕНИТЬ может содержать до 252 аргументов.
Если объединенная строка содержит свыше 32767 символов (ограничение на ввод данных для ячейки), возникнет ошибка ЗНАЧ!.
Сводные таблицы Excel - очень удобный и гибкий инструмент, позволяющий быстро сгруппировать данные нужным образом. И вот пять настроек, которые позволят вам использовать этот инструмент максимально эффективно.
Макет сводной таблицы
Часто приходится создавать однотипные сводные таблицы и затем для каждой из них настраивать внешний вид, например, изменять макет отчета, добавлять пустые и итоговые строки.
Получившийся макет можно установить в качестве настройки по умолчанию и он будет использоваться при создании новых сводных таблиц.
Через меню Файл заходим в Параметры Excel, раздел Данные и нажимаем на Изменить макет по умолчанию .
Здесь можно выбрать нужные настройки с помощью выпадающих меню и чек-боксов (1, см.рис.ниже), либо произвести импорт настроек (3), указав на листе (2) подходящую сводную таблицу.
После применения изменений все новые сводные таблицы будут использовать этот макет.
Объединение ячеек
Если сводная таблица используется в качестве готового отчета, то возникает желание привести ее вид к более привычному, например, объединить ячейки некоторых полей.
Стандартными средствами Excel сделать это не получится, но через контекстное меню обратимся к параметрам сводной таблицы.
Выбираем пункт, который позволит объединить и выровнять по центру ячейки с подписями полей. Останется лишь задать для них подходящее форматирование - шрифт, его размер, начертание и т.д.
Изменение ширины столбцов
По умолчанию сводная таблица подстраивается под данные, которые в ней выводятся, поэтому при обновлении сводной таблицы может измениться и ширина столбцов, что не всегда удобно.
Например, такое может случиться при использовании фильтров. Так как сам фильтр находится над сводной таблицей, то его значение будет определять ширину ячейки фильтра, а значит и ширину всего столбца листа, в том числе и сводной таблицы.
Кроме этого, и текст в заголовках таблицы определят ширину соответствующих столбцов, а значит каждый раз поле обновления таблицы будут «слетать» и произведенные вами настройки.
Зафиксировать ширину столбцов можно через параметры сводной таблицы. Здесь на вкладке Макет и формат снимаем флажок с параметра Автоматически изменять ширину столбцов при обновлении .
После применения настроек ширина столбцов при обновлении сводной таблицы изменяться не будет.
Автообновление
Сводную таблицу можно построить на основе данных из внешнего источника, например, из другого документа.
Если данные в источнике изменятся, то сводную таблицу нужно будет обновлять вручную. Но можно облегчить задачу, включив в параметрах сводной обновление при открытии файла.
Теперь данные в сводной таблице будет автоматически обновляться каждый раз при открытии файла, в котором она содержится.
Фильтры
Ну и в заключение расскажу о решении проблемы, с которой можно столкнуться при использовании фильтров.
Проблема заключается в том, что после обновления сводной таблицы в фильтре остаются значения, которые уже были удалены из источника данных.
Основное предназначение сводной таблицы - анализ данных. И для этого она снабжена достаточным довольно обширным функционалом, включая группировку элементов. Для чего она нужна. Предположим, имеется таблица примерно такого вида:
И необходимо проанализировать данные в разрезе месяца или квартала или на основании групп артикулов с определенным шагом.
ГРУППИРОВКА ДАТЫ И ВРЕМЕНИ
Если необходимо просмотреть суммарную стоимость предложений по кварталам, то пригодиться группировка по датам.
- Выделить любую ячейку нужного поля из области строк или столбцов и щелкнуть правой кнопкой мыши;
- Выбрать из контекстного меню пункт Группировать (Group) ;
- В поле Начиная с (Starting at) ввести начальную дату для группы;
- В поле по (Ending at) ввести конечную дату для группы;
- В поле с шагом (By) выбрать диапазон группировки: секунды, минуты, часы, дни, месяцы, кварталы, годы (seconds, minutes, hours, days, months, quarters, years) ;
- Нажать OK
Начиная с 2016 версии Excel группировка полей с датой и временем создается автоматически сразу при добавлении поля в область сводной таблицы. Чтобы отключить автогруппировку перейдите в: Файл (File) -Параметры (Options) -Данные (Data) -снять галочку с пункта Отключить автоматическое группирование столбцов даты и времени в сводных таблицах (Disable automating grouping of Date|Time columns in PivotTables) .
ГРУППИРОВКА ЧИСЛОВЫХ ПОЛЕЙ
Может пригодиться для группировки по занятым местам или по ценам предложений. Например, можно отобрать все предложения от 110 000р до 130 000р с шагом 10 000р. В данном случае получим таблицу, в которой будут интересующие предложения из указанного диапазона, разбитые с нужным шагом. Если какие значения превышают указанную сумму(130 000р), то будет отдельная группа: >130000, если меньше:
- Выделить любую ячейку нужного поля из области строк или столбцов и щелкнуть правой кнопкой мыши;
- Выбрать из контекстного меню пункт Группировать (Group) ;
- В поле Начиная с (Starting at) ввести первый элемент группы (110000);
- В поле по (Ending at) ввести последний элемент группы (130000);
- В поле с шагом (By) указать шаг для детализации в каждой группе (10000).
Но может быть и так, что зеленых треугольников нет, а группировка не работает. Для начала следует убедиться, что в столбце нет ячеек с явным текстом, а так же ячеек с пробелами. По хорошему - пустые ячейки тоже лучше убрать или заменить нолями. Опять обновить пару раз сводную.
Если после этого группировка все равно отказывается работать - значит где-то еще есть числа/даты, записанные как текст. Но они могут быть не подсвечены зеленым треугольником. Такое поведение часто наблюдается в файлах, выгруженных из 1С или иных программ. Часто побеждают это очень упорным трудом: выделяют ячейку, жмут F2(чтобы войти в режим редактирования ячейки) и Enter. Тогда Excel преобразует дату/число в настоящие дату/число. Но если таких ячеек хотя бы 100 - это уже не на пару минут рутины. Благо, все это можно сделать за пару секунд. Чтобы быстро преобразовать ячейки с датами/числами, записанными как текст в реальные даты/число необходимо:
- скопировать любую пустую ячейку на листе
- выделить все ячейки с датами/числами
- правая кнопка мыши -Специальная вставка (Paste Special) -в окне выбрать Значения (Values) , операция - Сложить (Multiply)
- ОК
Excel автоматом преобразует даты и числа в нормальные данные. Возможно, придется заново задать формат датам - но это уже совершенно не сложно: правая кнопка мыши -Формат ячеек (Format Cells) -Дата (Date) .
Про другие возможности Специальной вставки можно прочитать в этой статье: Как быстро умножить/разделить/сложить/вычесть из множества ячеек одно и то же число?
ГРУППИРОВКА ТЕКСТОВЫХ ПОЛЕЙ ИЛИ ОТДЕЛЬНЫХ ЭЛЕМЕНТОВ
- Выделить ячейку из области строк или столбцов с одним из элементов поля для группировки;
- Удерживая CTRL или SHIFT выделить другие элементы (ячейки) этого поля;
- Щелкнуть правой кнопкой по любой выделенной ячейке и выбрать из контекстного меню пункт Группировать (Group) или на вкладке Параметры (Options) в группе Группировать (Group) нажать кнопку Группа по выделенному (Group Selection);
- При необходимости задать свое имя группе
В полях с уровнями можно группировать только элементы, имеющие одинаковые подуровни. Например, если в поле есть два уровня «Страна» и «Город», нельзя сгруппировать города из разных стран.
ПЕРЕИМЕНОВАНИЕ ГРУППЫ ПО УМОЛЧАНИЮ
При группировке элементов Excel задает имена групп по умолчанию, например Группа1 (Group1) для выбранных элементов или Кв-л1 (Qtr1) для квартала 1(если работаем с датами). Задать группе более понятное имя совсем несложно:
- Выделить имя группы;
- Нажать клавишу F2;
- Ввести новое имя группы.
- Выделить группу элементов, которые требуется разгруппировать;
- На вкладке Параметры (Options) в группе Группировать (Group) нажать кнопку Разгруппировать (Ungroup) (или щелкнуть правой кнопкой мыши и выбрать из контекстного меню пункт Разгруппировать (Ungroup) ).
- Группируем по значению
- Группируем по дате
- Группируем данные вручную
- Исправляем ошибки
- Детализация групп
- Группировка и несколько диапазонов консолидации
- Кликнуть правой кнопкой мыши по левому столбцу сводной таблицы (столбец, содержащий сгруппированные значения);
- В появившемся меню нажать Разгруппировать (Ungroup).
Для числовых полей или полей даты и времени все группы для поля будут разгруппированы. При выборе групп будут разгруппированы только выбранные элементы, а поле группы не будет удалено из списка полей сводной таблицы, пока все группы в поле не будут удалены.
Helen Bradley рассказывает, как группировать данные в сводной таблице Excel для их дальнейшего обобщения и анализа.
Группируем по значению
Представьте себе такую ситуацию: Вы анализируете подборку счетов, собранных в сводной таблице. Вы можете обобщать счета, объединяя их в группы по 5 — 10 или даже 100 счетов. Давайте рассмотрим пример, где идентификатор строки OrderID — это числовое поле. В данный момент каждому счёту соответствует отдельная строка, и результат получается довольно громоздким. Мы можем сгруппировать эти данные, чтобы упростить таблицу.
В этой сводной таблице каждый идентификатор OrderID представлен в отдельной строке. Такие данные можно считать обобщёнными, но нам этого недостаточно.
Для этого кликните правой кнопкой мыши по одному из OrderID данной сводной таблицы и в появившемся меню выберите Group (Группировать). Так как OrderID это числовое поле, то диалоговое окно уже будет подготовлено для численной группировки с полями Starting At (Начиная с), Ending At (По) и By (С шагом). В данной ситуации Excel предлагает Вам группировать элементы по 10 штук.
По умолчанию Excel предлагает в качестве значения Starting At (Начиная с) первый элемент OrderID: 10248. Тем не менее, Вы можете настроить точнее или создать другую группировку, установив собственное значение параметра Starting At (Начиная с). Чтобы начать с элемента 10240, введите это значение в поле Starting At (Начиная с) — Вы можете установить любое стартовое значение, даже такое, которого нет в данных.
Мы настраиваем параметры группировки данных — они будут сгруппированы по полю OrderID по десять последовательно пронумерованных заказов.
Итак, мы настроили значение Starting At (Начиная с), оставили значение Ending At (По) таким, как предложил Excel, и оставили значение By (С шагом) равным 10, поскольку оно нам подходит. Вы можете делать группы меньше или больше, изменяя это значение — например: 5, 20, 50 или 100. Теперь жмём ОК. Данные будут сгруппированы по значению OrderID в более удобные для обработки группы.
Вот так выглядят данные, сгруппированные по OrderID. Как видите, они значительно более обобщены, чем ранее.
Группируем по дате
Если данные, с которыми Вы работаете, являются датами, то Вы сможете использовать похожий приём. Чтобы сгруппировать информацию по датам, кликните правой кнопкой мыши по дате в столбце или строке Вашей сводной таблицы и выберите Group (Группировать). Вы можете выбрать шаг группировки — Seconds (Секунды), Minutes (Минуты), Hours (Часы), Days (Дни), Months (Месяцы), Quarters (Кварталы) или Years (Годы), и установить начальный и конечный момент времени. Для группировки по годам, кварталам и месяцам интервал зафиксирован равным 1, а для группировки по дням Вы можете установить собственный интервал, например, равный 7 дням, чтобы сгруппировать данные по неделям. Для этого выберите Days (Дни) как шаг группировки, установите количество дней равным 7, укажите для параметра Starting At (Начиная с) дату, которая выпадает на начало недели, и нажмите ОК. Информация будет сгруппирована по неделям.
Большой объём данных будет сгруппирован по неделям (по 7 дней), и первым днем выступит воскресенье — день недели, соответствующий дате, указанной в параметре Starting At (Начиная с).
Группируем данные вручную
Не всегда набор записей можно разделить по группам с такой же лёгкостью, как последовательность чисел или дат. Иногда группа становится группой просто потому, что Вы так решили. В этом случае можно создать свою собственную группировку. Откройте лист со сводной таблицей, кликните по первому элементу данных, который нужно включить в группу и далее, с нажатой клавишей Ctrl, последовательно щелкните по всем элементам, которые также нужно включить в эту группу. Затем на вкладке PivotTable / Options (Работа со сводными таблицами / Анализ) нажмите Group Selection (Группировка по выделенному) и элементы будут добавлены в новую группу. Таким же образом Вы можете выделить другие элементы и создать еще одну группу.
Выделив несколько полей, Вы можете объединить их в группы так, как будет удобно.
При помощи этого инструмента Вы можете группировать данные в небольшие подборки, с которыми удобно работать. Так как по умолчанию группы названы именами Group 1 (Группа 1) и Group 2 (Группа 2), Вам придётся самостоятельно переименовать их. Для этого кликните ячейку, содержащую имя группы, и в строке формул введите более подходящее имя для группы.
После того, как группы созданы, Вы вправе переименовывать их, как угодно.
Вы можете развернуть или свернуть группу, нажав символы плюс или минус (+/-) слева от имени группы.
Исправляем ошибки
Детализация групп
Одним из преимуществ, которые даёт группировка данных, является то, что Вы можете извлечь подмножество сгруппированных данных на новый лист. Чтобы сделать это, выберите группу, и дважды щелкните по ячейке с интересующим Вас итоговым значением. Данные, из которых было собрано это итоговое значение, будут извлечены на новый лист текущей рабочей книги Excel.
Двойной щелчок мышью по любому значению в сводной таблице создаёт новый лист, содержащий все данные, относящиеся к этому значению.
Группировка и несколько диапазонов консолидации
Вы можете создавать группы в сводной таблице, которая собрана из нескольких консолидированных диапазонов, например, из данных на разных листах рабочей книги. Для этого в Excel 2007 и 2010 на Панель быстрого доступа необходимо добавить команду PivotTable and PivotChart Wizard (Мастер сводных таблиц и диаграмм).
Теперь Вы можете создать сводную таблицу из нескольких диапазонов Excel с единообразной компоновкой данных. Вам понадобятся данные с одинаковым количеством столбцов, одинаковыми заголовками столбцов, при этом каждая таблица может иметь разное количество строк.
Эта команда исключена из меню Excel 2010, но Вы можете добавить её на Панель быстрого доступа самостоятельно.
Создайте новый лист в Вашей рабочей книге Excel, кликните только что добавленную кнопку PivotTable and PivotChart Wizard (Мастер сводных таблиц и диаграмм), выберите пункты Multiple Consolidation Ranges (В нескольких диапазонах консолидации) и PivotTable (Сводная таблица), а затем нажмите Next (Далее). Выберите I will create the page fields (Создать поля страницы) и снова нажмите Next (Далее). Теперь Вам нужно указать каждый из диапазонов. Зайдите на первый лист, выберите все данные, включая заголовки таблицы, и нажмите Add (Добавить). Повторите шаги, чтобы добавить информацию со следующего листа и так далее, пока не добавите данные со всех листов, которые планировали использовать в сводной таблице.
Мастер сводных таблиц и диаграмм позволяет объединять сразу несколько диапазонов в одной сводной таблице.
В Мастере сводных таблиц и диаграмм первым делом нужно выбрать, какие диапазоны данных будут использованы.
Далее укажите количество полей, которые нужно добавить в сводную таблицу — вероятнее всего, Вам понадобится одно или два. Теперь выберите один из диапазонов в поле немного выше и введите для него имя группы. Затем выберите следующий диапазон и проделайте для него те же самые шаги. И так далее для всех диапазонов. Если имя группы уже существует, Вы можете не вводить его, а выбрать из выпадающего списка.
Так, например, диапазон, содержащий даты, может быть частью группы, которая разделяет месяцы по кварталам, а также частью группы, которая разделяет месяцы по сезонам: Winter (Зима), Spring (Весна), Summer (Лето) и Fall (Осень). Группировки могут быть по любому признаку, который Вы посчитаете подходящим для своих данных. Нажмите Next (Далее), кликните по ячейке, в которой должен будет находиться верхний левый угол сводной таблицы и нажмите Finish (Готово).
В Мастере сводных таблиц и диаграмм Вы можете связать диапазоны со страницами, как показано на рисунке выше — Quarters (Кварталы) и Seasons (Сезоны).
Сводная таблица будет сформирована автоматически, группы будут созданы как поля Report Filter (Фильтры) в списке полей сводной таблицы, но Вы, если пожелаете, можете перетащить эти поля в область Row Labels (Строки) или Column Labels (Колонны).
На рисунке выше показано: мы переместили страницу Page 2 (Сезоны) из области Report Filter (Фильтры) в область Row Labels (Строки).
Группируете ли Вы данные в уже готовой сводной таблице или Вы делаете это вместе с созданием сводной таблицы из нескольких диапазонов — результат будет одинаковый, и это станет ещё одним полезным инструментом в Вашем арсенале для обобщения данных в сводных таблицах Excel.
Часто возникает необходимость выполнить группировку в сводной таблице по заголовкам строки или столбца. Для численных значений Excel может сделать это автоматически (в том числе для дат и времени). Далее это продемонстрировано на примерах.
Пример 1: Группировка в сводной таблице по дате
Предположим, мы создали сводную таблицу (как на картинке ниже), которая показывает данные продаж для каждого дня первого квартала 2016 года.
Если нужно выполнить группировку данных о продажах по месяцам, то это можно сделать вот так:
-
Кликните правой кнопкой мыши по левому столбцу сводной таблицы (столбец с датами) и выберите команду Группировать (Group). Появится диалоговое окно Группирование (Grouping) для дат.
Пример 2: Группировка в сводной таблице по диапазону
Предположим, мы создали сводную таблицу (как на картинке ниже), которая группирует список из 150 детей по возрасту. Группы делятся по возрастам от 5 до 16 лет.
Если нужно пойти еще дальше и объединить возрастные группы в категории 5-8 лет, 9-12 лет и 13-16 лет, то можно поступить вот так:
-
Кликните правой кнопкой мыши по левому столбцу сводной таблицы (столбец с возрастами) и выберите команду Группировать (Group). Появится диалоговое окно Группирование (Grouping) для чисел.Excel автоматически заполнит поля Начиная с (Starting At) и По (Ending At) минимальным и максимальным значениями из наших исходных данных (в нашем примере это 5 и 16).
Как разгруппировать сводную таблицу
Чтобы разгруппировать значения в сводной таблице, нужно:
Распространённые ошибки при группировке в сводной таблице
Ошибка при группировке в сводной таблице: Выделенные объекты нельзя объединить в группу (Cannot group that selection).
После этого кликните правой кнопкой мыши по сводной таблице и нажмите Обновить (Refresh). Данные в сводной таблице будут обновлены, и теперь группировка строк или столбцов должна быть доступна.
Читайте также: