Как объединить ячейки в сводной таблице Excel

Обновлено: 21.11.2024

Друзья не позволяют друзьям объединять ячейки! Это то, что вы часто слышите от энтузиастов Excel. Люди обычно объединяют ячейки, пытаясь сделать электронную таблицу более красивой. При этом… не только красота электронной таблицы менее важна, чем ее функциональность, на которую определенно негативно влияют объединенные ячейки… но на самом деле есть способ изменить внешний вид, идентичный слиянию ячеек, без всех многочисленных недостатков, связанных с объединенные ячейки. Давайте проверим это.

Если вы хотите повторить мою демонстрацию ниже, вот файл упражнения: MergedCells

Это вымышленный список учащихся и оценок с несколькими объединенными ячейками вверху.

Первая строка данных содержит 3 набора объединенных ячеек: A1 с B1; С1 с Д1; и E1 с F1. Если вы выберете любой из них, обратите внимание, что в группе «Выравнивание» выбрано «Объединить и центрировать».

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

На самом деле есть несколько причин, по которым объединенные ячейки могут быть проблематичными. Вот лишь некоторые из них.

Сортировка и фильтрация

Допустим, я хочу использовать настраиваемые кнопки фильтра, с которыми мы играем в Excel Essentials. Вы хотите отфильтровать всех учащихся с оценкой A.

<р>1. Нажмите в любом месте верхней строки и в правой части вкладки "Главная" выберите "Сортировка и фильтр" и "Фильтр".

<р>2. Теперь перейдите к раскрывающемуся списку, созданному рядом с Оценкой.

Обычно у меня была бы возможность фильтровать по буквенной оценке, но поскольку ячейки E1 и F1 были объединены, вместо этого Excel предлагает фильтровать только по оценке в %. Не очень полезно.

Сводные таблицы

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

Что это? Мы получаем сообщение об ошибке, потому что у нас нет истинных меток столбцов (строка заголовка), когда ячейки объединены, как они находятся в нашей верхней строке. Это означает, что Excel не знает, какие у нас категории для создания сводной таблицы.

(Бесстыдный плагин: приходите на курс Excel: сводные таблицы, если хотите узнать больше.)

Формулы

Вероятно, это самое большое для меня. Допустим, я хочу подсчитать количество 22-летних учеников в моем классе. Без проблем! Давайте сделаем формулу CountIf.

В I1 я ввел =Countif(… затем попытался выбрать свой диапазон, столбец D…. посмотрите, что получилось:

Excel не разрешает мне включать только столбец D… он также хочет включать столбец C. Как раздражает! Мы, вероятно, могли бы решить эту проблему с формулой, но даже в этом случае, я гарантирую, что эти объединенные ячейки будут мешать вам с будущей формулой.

Макросы

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

В общем, объединенные ячейки не стоят заморочек.

Если вас действительно привлекает внешний вид объединенных ячеек, есть еще один вариант. Он называется Центрировать по выделенному фрагменту.

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

<р>2. Выберите A1 и B1 и щелкните правой кнопкой мыши по ним. Выберите «Формат ячеек».

<р>3. Во всплывающем окне перейдите на вкладку «Выравнивание» и щелкните раскрывающийся список рядом с «Горизонтально».Выберите Центрировать выделение. Нажмите "ОК".

<р>4. Повторите этот шаг с выбранными C1 и D1, затем с выбранными E1 и F1. По внешнему виду он будет выглядеть как объединенные ячейки.

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

Выбираете ли вы центрировать ячейки или объединять их, я думаю, важно задать себе вопрос: почему вы хотите это сделать? Это действительно необходимо? Когда это возможно, я бы рекомендовал избегать любой из этих практик. Я понимаю желание украсить книгу, но четко обозначенные столбцы с длинными строками непрерывных данных — это действительно красивые электронные таблицы. Их красота в их функциональности; а когда функциональность утеряна, никого особо не волнует, как выглядит верхний ряд. Просто мысль от человека, который на протяжении многих лет «разъединял» множество ячеек в электронных таблицах многих людей.

Что вы думаете? Убедило ли это вас разъединиться и больше никогда не объединяться? В любом случае, я буду здесь, чтобы помочь вам.

Поздравляем наших новых опытных пользователей! Полную галерею и дополнительную информацию о программе WSU Microsoft Office Power User Program можно найти на странице: wichita.edu/poweruser

Шери Смит
(первый опытный пользователь десятилетия!)

Обучение работе с приложениями ITS

Авторские права на весь контент Али Левин. Пожалуйста, не воспроизводите без письменного разрешения.

как сделать выравнивание текста ориентации до -90 градусов и объединить ячейки для группы в сводной таблице.

Факты об Excel

Главная, Условное форматирование, Выделение ячеек, Дублирование записей, OK, чтобы добавить розовое форматирование ко всем дубликатам в выбранном диапазоне.

БрайанБ

Известный участник

Попытки отформатировать сводные таблицы — пустая трата времени, поскольку любое форматирование теряется при обновлении таблицы.

Статистика форума

Поделиться этой страницей

Мы обнаружили, что вы используете блокировщик рекламы.

Какой блокировщик рекламы вы используете?

Отключить AdBlock

Следуйте этим простым шагам, чтобы отключить AdBlock

1)Нажмите значок на панели инструментов браузера.
2)Нажмите значок на панели инструментов браузера.
2)Нажмите кнопку «Приостановить на этом сайте».

Отключить AdBlock Plus

Следуйте этим простым шагам, чтобы отключить AdBlock Plus

1)Нажмите значок на панели инструментов браузера.
2)Нажмите на переключатель, чтобы отключить его для «mrexcel.com».

Отключить источник uBlock

Следуйте этим простым шагам, чтобы отключить uBlock Origin

1)Нажмите значок на панели инструментов браузера.
2)Нажмите кнопку "Питание".
3)Нажмите кнопку "Обновить".

Отключить uBlock

Следуйте этим простым шагам, чтобы отключить uBlock

1)Нажмите значок на панели инструментов браузера.
2)Нажмите кнопку "Питание".
3)Нажмите кнопку "Обновить".

Одним из наиболее часто используемых способов является использование параметра Объединить и центрировать на вкладке "Главная".

Проблема с использованием Объединить и центрировать заключается в том, что он может объединять ячейки, но не текст в этих ячейках (т. е. вы теряете некоторые данные при объединении ячеек).

Допустим, у нас есть набор данных, как показано ниже:

Если я выберу ячейки A1 и B1 и воспользуюсь параметром "Объединить и центрировать", он сохранит текст из крайней левой ячейки (в данном случае A1), но вы потеряете данные из всех других ячеек.

Однако Excel не совсем безжалостен — он предупреждает вас, прежде чем это произойдет.

Если вы попытаетесь объединить ячейки, содержащие текст, появится всплывающее окно с предупреждением об этом (как показано ниже).

Если вы продолжите и нажмете OK, две ячейки будут объединены, а текст останется только из самой левой ячейки. В приведенном выше примере он объединит A1 и B1 и покажет только текст John.

Это руководство охватывает:

Объединить ячейки в Excel без потери данных

Если вы не хотите терять текст из объединенных ячеек, используйте формулу СЦЕПИТЬ. Например, в приведенном выше случае введите следующую формулу в ячейку C1: =СЦЕПИТЬ(A1", ",B1)

Здесь мы объединяем ячейки A1 и B1 и используем пробел в качестве разделителя. Если вам не нужен разделитель, вы можете просто пропустить его и использовать формулу =СЦЕПИТЬ(A1,B1).

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

Этот результат функции СЦЕПИТЬ находится в другой ячейке (в ячейке C1). Поэтому вы можете скопировать его (как значения) в ячейку, которую вы хотите объединить.

Вы также можете использовать знак амперсанда для объединения текста. Например, вы также можете использовать =A1&», «&B1

Преимущество отсутствия объединения ячеек в Excel

Когда вы используете параметр «Объединить и центрировать» для объединения ячеек, он лишает вас возможности сортировать этот набор данных. Если вы попытаетесь отсортировать набор данных, содержащий какие-либо объединенные ячейки, появится всплывающее окно, как показано ниже:

Альтернатива использованию слияния и центрирования

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

Вот как это использовать:

Выберите ячейки, которые хотите объединить.
Нажмите Control + 1, чтобы открыть диалоговое окно форматирования ячеек.
На вкладке "Выравнивание" в раскрывающемся списке "По горизонтали" выберите "По центру выделенного".

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

ПРИМЕЧАНИЕ. Для работы Center to Across убедитесь, что данные есть только в самой левой ячейке.

Из-за этих двух злодеев миллионы людей тратят свою жизнь на борьбу с Excel. Читайте дальше, чтобы узнать, как справиться с ними и исключить их из своей жизни.

Почему они плохие?

Помните, что мы работаем над таблицами Excel. Обычно это RAW DATA — значит — входные данные. Он содержит много информации, но не в удобном для понимания формате. Вот почему мы обрабатываем его и генерируем более понятный вывод. Эти выходные данные обычно содержат объединенные ячейки и данные кросс-таблицы. Что хорошо.

К сожалению, очень часто мы получаем данные INPUT, которые содержат объединенные ячейки и формат Cross-Tab. Вот когда они плохие – совсем плохие!

Необработанные данные НЕ должны содержать объединенные ячейки или кросс-таблицы. Предполагается, что это должны быть простые табличные данные.

Вот почему Excel не позволяет создавать таблицы, если данные содержат объединенные ячейки.

К сожалению, Excel не может понять, являются ли данные кросс-таблицей. Таким образом, он позволяет создать таблицу из кросс-таблицы.

Объединенные ячейки и перекрестная таблица

Мы знаем, что такое табличные данные…

Если мы преобразуем ее в сводную таблицу продаж по продуктам и регионам… она станет кросс-таблицей

Если мы добавим еще и год, каждый регион будет иметь данные за многие годы… поэтому ячейка «Регион» является общей для всех лет… Это делается автоматически с помощью сводной таблицы. Это выглядит как объединенная ячейка, но на самом деле это не так. В любом случае Регион является общим на протяжении нескольких лет, поэтому концептуально это общая (объединенная) ячейка.

Теперь вы понимаете, почему это форматы OUTPUT.

Что делать с объединенными ячейками?

Если ВХОДНЫЕ ДАННЫЕ содержат объединенные ячейки, это в основном означает, что данные являются своего рода выходными данными. Он может быть создан не в сводной таблице, но почему-то это отчет, а не RAW-данные.

RAW DATA не требует объединения ячеек.

Так что же делать? Просто разъединить ячейки? Это не поможет…

В первых данных показаны объединенные ячейки, во вторых все ячейки не объединены.

Теперь, если вы создадите таблицу, она будет выглядеть так…

Это не имеет никакого смысла…

Поэтому нам нужно сделать что-то большее. Обычно объединенные ячейки указывают на многоуровневость заголовков. Например, в этом случае есть один набор заголовков для региона и другой для года.

Чтобы справиться с этой ситуацией, нам нужно объединить два заголовка и создать единый набор заголовков. Вот как это можно сделать.

Это лучше. Проблема с объединением ячеек теперь решена.

Теперь у нас есть КРОСС-ТАБ. Давайте посмотрим, как решить эту проблему.

Преобразование данных кросс-таблицы в табличные

В этой статье мы начали с удобных табличных данных. Итак, мы УЖЕ ЗНАЕМ, какими должны быть необработанные данные. Но если у вас есть кросс-табличные данные, как показано выше, как преобразовать их в табличные?

Давайте разберемся с анатомией данных кросс-таблицы. Первая колонка — Товар — это хорошо. В нем есть заголовки, за которыми следуют согласованные данные. Но Восток 2014 и подобные названия на самом деле ДАННЫЕ. Вместо заголовка сидят данные, что делает их ПЛОХИМИ данными.

Чтобы преобразовать его в табличный, у нас есть два метода.

НЕТ. ДАЖЕ НЕ ДУМАЙТЕ О ТРАНСПОНИРОВАНИИ ВРУЧНУЮ.

Прочитайте эти статьи, чтобы понять оба этих метода.
Консолидация данных перекрестных таблиц (этот метод работает со всеми версиями Excel)
Консолидация данных перекрестных таблиц с помощью Power Query (Excel 2010 Pro, с Power Query)

Есть еще

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

Проблема до сих пор полностью не решена. Столбец Region-Year должен быть разделен на два отдельных столбца, чтобы получить действительно хорошие табличные данные. Это можно легко сделать в самом Power Query.

Что дальше

Теперь, когда мы хорошо знаем концепции, мы увидим правила для ХОРОШИХ данных и то, как создавать хорошие данные с нуля — навык, который поможет вам на протяжении всей вашей жизни. Это не только технические аспекты, но и помогает мыслить более четко и анализировать данные более эффективно.

Читайте также: