Как объединить ячейки в сводной таблице excel
Объединение ячеек – известная команда в Excel. Однако на выполнение этой операции часто уходит много времени. В данной статье рассмотрим, что подразумевают под объединением ячеек, и как быстро выполнить эту операцию. Выбрав оптимальный варианты, вы сэкономите много времени.
Формат ячеек
Команда Excel Правая кнопка мыши → Формат ячеек → Выравнивание → Отображение → объединение ячеек удаляет границы между ячейками в выделенном диапазоне. В результате получается одна большая ячейка. На картинке показано объединение ячеек одной строки и трех столбцов.
Таким же способом можно объединить любой прямоугольной диапазон. После объединения ячеек содержимое часто центрируют. На ленте во вкладке Главная даже есть специальная команда Объединить и поместить в центре.
Начинающие пользователи Excel часто применяют эту команду для размещения названия таблицы по центру.
Выглядит красиво, но крайне непрактично. Если выделить столбец комбинацией клавиш Ctrl + пробел, то диапазон расширится на все столбцы, которые захватывает объединенная ячейка. Возникнут и другие проблемы: при копировании, не работает в таблице Excel, нельзя автоматически подогнать ширину столбца и др. В общем, объединение ячеек сулит много неудобств в дальнейшей работе. Поэтому в большинстве случаев объединение ячеек лучше не применять.
Как найти объединенные ячейки в Excel
Бывает, что в файле уже есть объединенные ячейки и они мешают нормальной работе. Например, в отчете из 1С или при работе с чужим файлом Excel. Тогда их нужно как-то быстро найти и отменить объединение. Как это быстро сделать? Выполните следующие шаги.
Как убрать объединение ячеек в Excel
Для отмены объединения сразу на всем листе Excel выполните следующие действия.
- Выделите все ячейки на листе. Можно щелкнуть на треугольнике, расположенном на пересечении заголовков строк и столбцов, либо горячей комбинацией Ctrl + A (один или два раза).
- Повторно примените команду Главная (вкладка) → Выравнивание (группа) → Объединить и поместить в центре.
Все объединенные ячейки снова станут нормальными. Если что-то пойдет не так, отмените последнее действие.
Горячие клавиши объединения ячеек в Excel
К сожалению, в Excel нет стандартных горячих клавиш для объединения ячеек. Тем не менее, у каждой команды есть свой клавиатурный эквивалент и его можно узнать. Для этого следует нажать клавишу Alt, на ленте под каждой вкладкой появится буква, нажав на которую, вы переходите внутрь этой вкладки.
Затем буквы появятся под каждой командой или выпадающим списком команд. Таким образом можно определить последовательность клавиш вызова нужной команды. Для объединения и центрирования ячеек в моей версии Excel необходимо последовательно нажать Alt – Я – Щ – Б. Это был первый способ.
Второй способ. Любым способом объединить ячейки и повторно вызвать команду клавишей F4 (повтор последнего действия).
Третий способ. Вначале поместите кнопку объединения ячеек на панель быстрого доступа. Для этого нажмите на треугольник справа от всех команд и там выберите Другие команды.
Вы попадаете в настройки панели быстрого доступа. Найдите команду Объединить и поместить в центре и добавьте ее в правое окно.
Кнопка появится на панели быстрого доступа.
После нажатия Alt под этой кнопкой появится цифра, зависящая от порядкового номера в ряду.
Теперь есть еще одна горячая клавиша объединить ячейки в Excel – Alt + 5.
Выравнивание по центру (без объединения ячеек)
Для расположения надписи в середине строки воспользуйтесь командой выравнивание по центру.
- Поместите надпись в левую ячейку строки, где должно произойти выравнивание.
- Выделите вправо нужное количество ячеек.
- Вызываете команду Правая кнопка мыши → Формат ячеек → Выравнивание → по горизонтали → по центру выделения.
Внешне результат такой же, как при объединении, только все ячейки останутся на своем месте. Есть пару нюансов.
- Не всегда понятно, в какой ячейке находится запись.
- Выравнивать по центру можно только по горизонтали, по вертикали нельзя.
В большинстве случаев вместо объединения ячеек лучше применять выравнивание по центру.
Формат ячеек влияет только на отображение данных. На деле часто приходится соединять содержимое из разных ячеек. Далее рассмотрим, как в Excel объединить данные из нескольких ячеек в одну.
Объединение ячеек с помощью & (амперсанд) и функции Excel СЦЕПИТЬ (CONCATENATE)
Объединение содержимого ячеек – очень распространенная задача. Выбор решения зависит от типа данных и их количества.
Если нужно сцепить несколько ячеек, то подойдет оператор & (амперсанд).
Обратите внимание, между ячейками добавлен разделитель в виде запятой с пробелом, то есть к объединению ячеек можно добавить произвольный текст. Полной аналогией & является применение функции СЦЕПИТЬ.
В рассмотренных примерах были только ячейки с текстом. Может потребоваться соединять числа, даты или результаты расчетов. Если ничего специально не делать, то результат может отличаться от ожидания. Например, требуется объединить текст и число, округленное до 1 знака после запятой. Используем пока функцию СЦЕПИТЬ.
Число присоединилось полностью, как хранится в памяти программы. Чтобы задать нужный формат числу или дате после объединения, необходимо добавить функцию ТЕКСТ.
Правильное соединение текста и числа.
Соединение текста и даты.
В общем, если вы искали, как объединить столбцы в Excel, то эти приемы работают отлично. Однако у & и функции СЦЕПИТЬ есть существенный недостаток. Все части текста нужно указывать отдельным аргументом. Поэтому соединение большого числа ячеек становится проблемой.
Функция Excel СЦЕП (CONCAT)
В Excel 2016 на смену функции СЦЕПИТЬ пришла функция СЦЕП. Она работает так же, только в качестве аргумента можно задать целый диапазон.
Все хорошо, но без пробелов. Если требуется соединить с разделителем, СЦЕП не сильно поможет. Поможет другая новая функция для объединения ячеек.
Функция Excel ОБЪЕДИНИТЬ (TEXTJOIN)
Функция ОБЪЕДИНИТЬ также появилась в Excel 2016 и решила сразу все проблемы склеивания ячеек: указание целого диапазона, вставка разделителя и даже пропуск пустых ячеек в диапазоне, чтобы не дублировать разделитель.
разделитель – разделитель, который вставляется между ячейками
пропускать_пустые – если 0, то пустые ячейки включаются, если 1 – игнорируются. Обычно задают 1, чтобы не дублировать разделитель.
текст1;… – ссылка на диапазон или отдельные ячейки для сцепления.
Функция Excel ОБЪЕДИНИТЬ – лучшее решение для склеивания ячеек.
Заполнить – Выровнять
И еще один трюк. Объединить ячейки можно без формул. Исходные ячейки должны быть в одном столбце.
Выполните следующие действия.
- Выделите столбец с данными.
- Расширьте выделение вправо настолько, сколько предположительно должен занять объединенный текст или больше.
- Вызовите команду Главная (вкладка) -> Редактирование (группа) -> Заполнить -> Выровнять.
Текст будет помещен в одну ячейку через пробел.
- Если в ячейках есть числа, даты или формулы, фокус не получится. Работает только с текстом.
- Общая длина текста не должна превышать 255 символов. Лишнее будет перенесено на вторую строку автоматически (см. ролик ниже).
Подведем итоги, как объединить ячейки, строки и столбцы в таблице Эксель. С помощью форматирования можно просто удалить границы между ячейками. Это плохое решение. Лучше воспользоваться выравниванием по центру. Объединение ячеек в Excel без потери данных производится специальными функциями.
Группировка позволяет выделить для анализа определенное подмножество данных сводной таблицы. Например, можно сгруппировать значения даты или времени (поля даты и времени в сводной таблице) в большом и неудобном списке по кварталам и месяцам, как показано на приведенном ниже изображении.
Примечание: В Excel 2016 появилась новая функция — группировка по времени. Приложение автоматически обнаруживает связи между значениями соответствующих полей и группирует их, когда пользователь добавляет в сводные таблицы строки полей времени. Созданную группу можно перетащить в сводную таблицу для анализа.
Группировка данных
Щелкните значение в сводной таблице правой кнопкой мыши и выберите команду Группировать.
В окне Группировка установите флажки Начиная с и Заканчивая и при необходимости измените значения.
В разделе С шагом выберите период времени. Для числовых полей введите число, которое указывает интервал для каждой группы.
Группировка выделенных элементов
Удерживая нажатой клавишу CTRL, выделите несколько значений.
Щелкните правой кнопкой мыши и выберите команду Группировать.
Присвоение имени группе
На вкладке Анализ нажмите кнопку Параметры поля.
Измените значение в поле Пользовательское имя и нажмите кнопку "ОК".
Разгруппировка сгруппированных данных
Щелкните правой кнопкой мыши любой элемент в группе.
Выберите команду Разгруппировать.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Сводная таблица — это эффективный инструмент для вычисления, сведения и анализа данных, который упрощает поиск сравнений, закономерностей и тенденций.
Работа с помощью стеблей немного отличается в зависимости от того, какую платформу вы используете для Excel.
Создание в Excel для Windows
Выделите ячейки, на основе которых вы хотите создать сводную таблицу.
Примечание: Ваши данные не должны содержать пустых строк или столбцов. Они должны иметь только однострочный заголовок.
На вкладке Вставка нажмите кнопку Сводная таблица.
В разделе Выберите данные для анализа установите переключатель Выбрать таблицу или диапазон.
В поле Таблица или диапазон проверьте диапазон ячеек.
В разделе Укажите, куда следует поместить отчет сводной таблицы установите переключатель На новый лист, чтобы поместить сводную таблицу на новый лист. Можно также выбрать вариант На существующий лист, а затем указать место для отображения сводной таблицы.
Настройка сводной таблицы
Чтобы добавить поле в сводную таблицу, установите флажок рядом с именем поля в области Поля сводной таблицы.
Примечание: Выбранные поля будут добавлены в области по умолчанию: нечисловые поля — в область строк, иерархии значений дат и времени — в область столбцов, а числовые поля — в область значений.
Чтобы переместить поле из одной области в другую, перетащите его в целевую область.
Данные должны быть представлены в виде таблицы, в которой нет пустых строк или столбцов. Рекомендуется использовать таблицу Excel, как в примере выше.
Таблицы — это отличный источник данных для сводных таблиц, так как строки, добавляемые в таблицу, автоматически включаются в сводную таблицу при обновлении данных, а все новые столбцы добавляются в список Поля сводной таблицы. В противном случае вам потребуется либо изменить исходные данные для pivotTable,либо использовать формулу динамического именоваемого диапазона.
Все данные в столбце должны иметь один и тот же тип. Например, не следует вводить даты и текст в одном столбце.
Сводные таблицы применяются к моментальному снимку данных, который называется кэшем, а фактические данные не изменяются.
Если у вас недостаточно опыта работы со сводными таблицами или вы не знаете, с чего начать, лучше воспользоваться рекомендуемой сводной таблицей. При этом Excel определяет подходящий макет, сопоставляя данные с наиболее подходящими областями в сводной таблице. Это позволяет получить отправную точку для дальнейших экспериментов. После создания рекомендуемой сводной таблицы вы можете изучить различные ориентации и изменить порядок полей для получения нужных результатов.
Вы также можете скачать интерактивный учебник Создание первой сводной таблицы.
1. Щелкните ячейку в диапазоне исходных данных или таблицы.
2. Перейдите на вкладку > рекомендуемая ст.
3. Excel данные анализируются и представлены несколько вариантов, например в этом примере с использованием данных о расходах семьи.
4. Выберите наиболее подбираемую для вас сетовую и нажмите кнопку ОК. Excel создаст сводную таблицу на новом листе и выведет список Поля сводной таблицы.
1. Щелкните ячейку в диапазоне исходных данных или таблицы.
2. Перейдите в > Вставить.
Если вы используете Excel для Mac 2011 или более ранней версии, кнопка "Сводная таблица" находится на вкладке Данные в группе Анализ.
3. Excel отобразит диалоговое окно Создание таблицы с выбранным диапазоном или именем таблицы. В этом случае мы используем таблицу "таблица_СемейныеРасходы".
4. В разделе Выберите, куда следует поместить отчет таблицы выберите новый или существующий. При выборе варианта На существующий лист вам потребуется указать ячейку для вставки сводной таблицы.
5. Нажмитекнопку ОК, Excel создаст пустую стебли и отобразит список полей.
Список полей сводной таблицы
В области Имя поля в верхней части выберите любое поле, которое вы хотите добавить в свою сетную. По умолчанию не числовые поля добавляются в область строк, поля даты и времени — в область Столбец, а числовые — в область значений. Вы также можете вручную перетащить любой доступный элемент в любое поле или, если элемент в ней больше не нужен, просто перетащите его из списка поля или снимите. Возможность переустановить элементы полей — одна из функций, которые упрощают быстрое изменение внешнего вида.
Список полей сводной таблицы
Суммировать по
По умолчанию поля в области значений отображаются как СУММ. Если Excel данные интерпретируются как текст, они отображаются как счёт. Именно поэтому так важно не смешивать типы данных для полей значений. Вы можете изменить вычисление по умолчанию, щелкнув стрелку справа от имени поля и выбрав Параметры полей.
Затем измените функцию в разделе Суммировать по. Обратите внимание на то, что при изменении метода вычисления Excel автоматически добавляет его название в раздел Пользовательское имя (например, "Сумма по полю имя_поля"), но вы можете изменить это имя. Чтобы изменить числовой формат для всего поля, нажмите кнопку Число. .
Совет: Так как при изменении способа вычисления в разделе Суммировать по обновляется имя поля сводной таблицы, не рекомендуется переименовывать поля сводной таблицы до завершения ее настройки. Вместо того чтобы вручную изменять имена, можно выбрать пункт Найти (в меню "Изменить"), в поле Найти ввести Сумма по полю, а поле Заменить оставить пустым.
Дополнительные вычисления
Значения также можно выводить в процентах от значения поля. В приведенном ниже примере мы изменили сумму расходов на % от общей суммы.
Вы можете настроить такие параметры в диалоговом окне Параметры поля на вкладке Дополнительные вычисления.
Отображение значения как результата вычисления и как процента
Просто перетащите элемент в раздел Значения дважды, щелкните значение правой кнопкой мыши и выберите команду Параметры поля, а затем настройте параметры Суммировать по и Дополнительные вычисления для каждой из копий.
При добавлении новых данных в источник необходимо обновить все основанные на нем сводные таблицы. Чтобы обновить одну сводную таблицу, можно щелкнуть правой кнопкой мыши в любом месте ее диапазона и выбрать команду Обновить. При наличии нескольких сводных таблиц сначала выберите любую ячейку в любой сводной таблице, а затем на ленте откройте вкладку Анализ сводной таблицы, щелкните стрелку под кнопкой Обновить и выберите команду Обновить все.
Если вы создали и решили, что вам больше не нужна, вы можете просто выбрать весь диапазон, а затем нажать кнопку УДАЛИТЬ. Она не влияет на другие данные, с ее стебли или диаграммы. Если сводная таблица находится на отдельном листе, где больше нет нужных данных, вы можете просто удалить этот лист. Так проще всего избавиться от сводной таблицы.
Примечание: Мы постоянно работаем над улучшением работы с Excel для Интернета. Новые изменения внося постепенно, поэтому если действия, которые вы в этой статье, возможно, не полностью соответствуют вашему опыту. Все обновления в конечном итоге будут обновлены.
Выберите таблицу или диапазон данных на листе, а затем выберите Вставить >, чтобы открыть область Вставка таблицы.
Вы можете вручную создать собственную или рекомендованную советку. Выполните одно из следующих действий:
Примечание: Рекомендуемые стеблицы доступны только Microsoft 365 подписчикам.
На карточке Создание собственной таблицы выберите Новый лист или Существующий лист, чтобы выбрать место назначения для этой таблицы.
В рекомендуемой pivottable выберите Новый лист или Существующий лист, чтобы выбрать место назначения для этой таблицы.
При необходимости перед созданием можно изменить источник данных в pivottable.
В области Вставка pivotTable выберите текстовое поле в поле Источник. При изменении источника карточки в области будут недоступны.
Вы можете выбрать данные в сетке или ввести диапазон в текстовое поле.
Нажмите клавишу ВВОД или кнопку, чтобы подтвердить выбор. В области по-новому будут обновлены рекомендуемые с помощью новых источников данных.
В верхней части области Поля pivotTable (Поля) в верхней части выберите любое поле, добавляемое в нее.
По умолчанию не числовые поля добавляются в область строк, поля даты и времени — в область Столбцы, а числовые — в область значения.
Вы также можете вручную перетащить любой доступный элемент в любое поле или, если элемент в ней больше не нужен, просто перетащите его из списка поля или снимите. Возможность переустановить элементы полей — одна из функций, которые упрощают быстрое изменение внешнего вида.
Соответствующие поля в pivotTable:
По умолчанию поля в области значений отображаются как СУММ. Если Excel данные интерпретируются как текст, они отображаются как счёт. Именно поэтому так важно не смешивать типы данных для полей значений. Чтобы изменить вычисление по умолчанию, сначала щелкните стрелку справа от имени поля, а затем выберите Параметры значения.
Затем измените функцию в разделе Операция. Обратите внимание на то, что при изменении метода вычисления Excel автоматически добавляет его название в раздел Пользовательское имя (например, "Сумма по полю имя_поля"), но вы можете изменить имя. Чтобы изменить числовой формат для всего поля, нажмите кнопку Числовой формат.
Совет: Так как при изменении способа вычисления в разделе Операция обновляется имя поля сводной таблицы, не рекомендуется переименовывать поля сводной таблицы до завершения ее настройки. Вместо того чтобы вручную изменять имена, можно нажать кнопку Найти и заменить (CTRL+H), в поле Найти ввести Сумма по полю, а поле Заменить оставить пустым.
Дополнительные вычисления
Значения можно также выводить в процентах от значения поля. В приведенном ниже примере мы изменили сумму расходов на % от общей суммы.
Вы можете настроить такие параметры в диалоговом окне Параметры поля значений на вкладке Дополнительные вычисления.
Отображение значения как результата вычисления и как процента
Просто перетащите элемент в раздел Значения дважды, а затем настройте параметры Операция и Дополнительные вычисления для каждой из копий.
По окончании этого урока вы сможете:
- Объединять несколько ячеек в одну
- Находить все объединённые ячейки
- Задавать перенос текста по словам
1. Объединение ячеек
На Уроке 54. Гриф «Утверждение» я рассказывала о нестандартной таблице, то есть таблица, в которой число ячеек в одной строчке не равно числу ячеек в другой строчке (или число ячеек в одном столбце не равно числу ячеек в другом столбце. Достаточно распространен случай общего заголовка для всей таблицы.
Шаг 1. Вставляем сверху новую строчку (Excel 21):
Шаг 2. Вводим название всей таблицы «Сводная ведомость»:
Попробуйте выровнять название по центру таблицы – у вас ничего не получиться!
Шаг 3. Объединяем несколько ячеек в одну (выделяем диапазон A1:D1 → лента Главная → группа команд Выравнивание → команда Объединить и поместить в центре):
Команда «Объединить и поместить в центре» имеет несколько вариантов объединения ячеек:
Чтобы воспользоваться каким-нибудь из них, нужно открыть список, нажав на кнопку выпадающего меню справа от команды.
1. «Объединить и поместить в центре» несколько выделенных ячеек объединяются в одну, а текст помещается по центру образованной ячейки (только что сделали).
2. «Объединить по строкам» ‒ выделенный диапазон ячеек, состоящий из нескольких строк и столбцов объединяются по центру. Например, нужно объединить диапазоны ячеек А1:1 и А2:D2. Для этого выделяет ячейки А1:D2 → выбираем команду Объединить по строкам:
3. «Объединить ячейки» ‒ несколько выделенных ячеек объединяются в одну, при этом выравнивание текста остается прежним. Данная команда такая же, как и «Объединить и поместить в центре», только не располагает текст по центру.
4. «Отменить объединение ячеек» ‒ отменяет любое выделение ячеек. Хотя для того, чтобы отменить объединение ячеек, достаточно еще раз щелкнуть по команде «Объединить и поместить в центре».
Но, если мы решим объединить две или несколько ячеек с содержимым, то ждет неприятный сюрприз.
Шаг 4. Выделяем две ячейки с содержимым → лента Главная → группа команд Выравнивание → команда Объединить и поместить в центре → команда Объединить ячейки):
Сразу появляется предупреждение «В объединенной ячейке сохраняется только значение из верхней левой ячейки диапазона. Остальные значения будут потеряны.»:
Поэтому будьте осторожны при выполнении операции «Объединение ячеек», которые содержат данные.
2. Поиск объединенных ячеек
Редко, но возникает необходимость поиска объединенных ячеек. Я создала ещё одну объединенную ячейку с набором символов.
Шаг 1. Лента Главная → группа команд Редактирование → команда Найти и заменить → команда Найти из выпадающего меню:
Появилось диалоговое окно «Найти и заменить».
Шаг 2. Команда Формат из диалогового окна «Найти и заменить» → режим «Объединение ячеек» из диалогового окна Найти формат → ОК:
Шаг 3. Обращаем внимание на поле рядом с командой «Формат» ‒ там появилось слово «Образец*» → команда Найти все:
Диалоговое окно «Найти и заменить» раскрывается, и мы видим в нижней части диалогового окна список всех объединенных ячеек и подсказку, что найдено 3 объединенных ячейки, что соответствует действительности. Щелкая по каждому пункту списка, мы выделяем соответствующую объединенную ячейку.
Если вам нужно перейти к другому типу поиска, то не забудьте отменить режим «Найти по формату.
Шаг 4. Лента Главная → группа команд Редактирование → команда Найти и заменить → команда формат → команда Очистить формат поиска из выпадающего меню:
3. Перенос текста по словам
В случаях, когда содержимое ячейки делает её слишком длинной, то следует разместить текст в несколько рядов в одной ячейке.
Шаг 1. Выделяем необходимый диапазон → лента Главная → группа команд Выравнивание → команда кнопка Перенос текста:
А вот и результат:
Шаг 2. Отменить режим «Перенос текста» можно повторным щелчком по этой команде.
| Совет от меня | Приступая к работе с новой таблицей, установите сразу же режим «Перенос текста» |
Шаг 3. Выделяем всю таблицу (щелчок ЛМ на перекрестье имен столбцов и строчек) → лента Главная → группа команд Выравнивание → команда Переносить текст:
Читайте также: