Как объединить ячейки в excel 2003, 2007, 2010, 2013 и 2016 – компьютерные советы | Портал про авто и технологии
Последние:
Компьютеры 

Как объединить ячейки в excel 2003, 2007, 2010, 2013 и 2016 – компьютерные советы

admin

Оглавление

Объединение и разделение ячеек в Excel 2003, 2007, 2010, 2013, 2016

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

Как объединить и разделить ячейки в Excel 2003, 2007, 2010?

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

Чтобы объединить несколько ячеек в одну, нужно выделить их мышкой. После этого делаем правый клик и выбираем в контекстном меню опцию «Формат ячеек».

В этом меню сосредоточены все функции по работе с ячейками и представлению данных в них. Заходим в это меню и выбираем вкладку «Выравнивание». Здесь отмечаем галочку «Объединение ячеек» в секции «Отображение» и кликаем «ОК».

Появляется диалоговое окно подтверждения операции. Соглашаемся с выполняемыми изменениями и жмем «ОК».

На этом все, теперь ячейки оказываются объединенными.

Для того, чтобы разъединить ячейки, придется выполнить обратную операцию. Нужно снова выделить ту ячейку, которую вы желаете разъединить, снова зайти в контекстное меню «Формат ячеек» и на вкладке «Выравнивание» снять птичку с пункта «Объединение ячеек». Далее жмем ОК и наслаждаемся полученным результатом.

Важно понимать, что данные в восстановленных ячейках будут не доступны. Единственный способ вернуть их – это воспользоваться функцией отмены последней операции (Ctrl-Z).

Альтернативный способ операции объединения – воспользоваться верхним меню. После выделения ячеек выберите раздел «Формат» и отметьте подпункт «Ячейки». Теперь выполните действия, описанные ранее.

Как объединить и разделить ячейки в Excel 2013, 2016?

В последних на сегодняшний день версиях редактора таблиц Excel алгоритм объединения и разделения ячеек через контекстное меню абсолютно аналогичен. Поменялся лишь принцип реализации данной операции через ленту.

Чтобы объединить несколько ячеек в одну, заходим на вкладку «Главная» ленты и кликаем на кнопке «Формат». После этого в возникшем меню выбираем опцию «Формат ячеек».

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

Как можно заметить, принцип действия действительно элементарный и лишен всяких подводных камней. Смело пользуйтесь указанной инструкцией, и это существенно сэкономит вам время при постижении азов Excel.

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

Komp.Guru > Microsoft > Excel > Как объединить ячейки в Экселе?

Опытные пользоваватели,пожалуй, знают все о таблицах Exсel. Но для новичка эта программа может быть настолько непонятной, что сам черт ногу сломит. Каждый, кто пользуется программой Excel впервые, задает вопрос: а как можно объединить ячейки? Для того чтобы это легко сделать, необходимо запомнить несколько довольно простых и нехитрых приёмов, о которых и пойдёт речь дальше.

Excel 2010: как объединить ячейки

Для данной версии этой программы существуют следующие пять приёмов.

Приём №1

Первый приём работает при помощи контекстного меню. Опытные пользователи программы считают его самым быстрым и простым. Выполняется он так:

  • зажмите клавишу Ctrl, а затем кликайте по ячейкам, которые необходимо объединить левой кнопкой мыши;
  • выделив их, наведите курсор на них, а потом кликните правой кнопкой мыши;
  • после этого появится диалоговое окно со списком возможных действий. Наведите курсор на строку «Формат ячеек», а затем нажмите левую кнопку мыши;
  • в появившемся окне перейдите во вкладку «Выравнивание»;
  • возле пункта «Объединение ячеек» поставьте галочку и нажмите «ОК».

Применять этот приём стоит только для текстовой информации. Это занятие будет бессмысленным если информация в таблице будет цифровой или в виде формул. Стоит также отметить, что при использовании такого способа сохраняются только те данные, которые находятся в левой верхней части. Программа сообщает об этом пользователю перед началом процесса.

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

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

Приём № 2

Для объединения можно использовать панель инструментов. Так, в Excel 2010, а также в версиях 2007 и 2013 есть иконка, которая называется «Объединить и поместить в центре». Она расположена в меню в разделе «Главная».

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

Кстати, нажав на эту иконку повторно, их можно опять разгруппировать.

Приём № 3

Чтобы во время слияния необходимая информация не исчезла, можно воспользоваться функцией «Сцепить». К примеру, необходимо будет соединить столбцы А и В. Для этого нам нужно выполнить следующие действия:

  • выделяем В1. Во вкладке «Главная» наводим курсор на кнопку «Вставить», а затем кликаем на стрелочку, которая находится возле неё. Появится выпадающее меню, в котором нужно кликнуть на пункт «Вставить столбцы на лист»;
  • между столбцами А и В появится новый столбец. Все данные из столбца В переместятся в С. В появившийся пустой столбец вписывается формула =СЦЕПИТЬ(А1;»,»;С1) и нажимается Enter. В этой формуле в кавычках заключён разделительный знак. Это необязательно должна быть запятая. Для разделения информации можно использовать такие знаки, как: «;», «:»;
  • далее выделяется ячейка с формулой. После этого наводится курсор на правый нижний угол и зажимается левая кнопка мыши. Выделяется весь столбец. Таким образом, формула скопируется во все ячейки и столбцы объединятся;
  • теперь выделите столбец В и нажмите Ctrl+C. После кликните один раз по столбцу правой кнопкой мыши и в появившемся меню сначала выберите пункт «Специальная вставка». В выпавшем меню выберите в пункте «Вставить значение» иконку с цифрами 123. Все эти действия необходимы чтобы выделенные ячейки столбца не были связаны с соседними ячейками формулой;
  • далее удаляются столбцы А и С. Для этого курсор ставится в столбце А. Затем во вкладке «Главная» необходимо нажать на кнопку «Удалить». В выпавшем списке выбрать «Удалить столбцы с листа». Аналогичным образом удалить и столбец С. В итоге в таблице останется лишь один столбец со всеми объединёнными данными.

Приём № 4

Объединить их, не потеряв информацию, можно и другим способом. Алгоритм действий такой же, как и в третьем приёме, только для их склеивания используется формула =А2&С2.

Приём № 5

Объединять ячейки в Excel можно и при помощи горячих клавиш. Для этого нужно скопировать уже соединённую ячейку при помощи сочетания горячих клавиш Ctrl+C, а затем вставить её в нужном месте, используя горячие клавиши Ctrl+V. Стоит отметить, что применение горячих клавиш позволит значительно ускорить процесс создания таблицы с объединёнными ячейками.

Как объединить ячейки в экселе 2003?

Все пять приёмов, описанные выше, можно использовать и для Excel 2003, но в некоторых из них алгоритм действий немного изменится, так как это более старая версия этой программы. Так, используя второй приём, ищите иконку для объединения ячеек на панели инструментов «Форматирование». Выпадающего списка с вариантами объединения у этой иконки не будет, а значит надо будет просто нажать на неё.

Почему пункты меню и кнопки для объединения ячеек могут быть неактивны в Excel?

Иногда объединить ячейки не удаётся, поскольку эта функция неактивна в Excel. Причина этого может быть в том, что на лист установлена защита или же разрешён общий доступ к книге. Устраните одну из этих причин, и функция опять будет активна.

Совместный доступ к файлу Excel одновременно

Совместный доступ в Microsoft Excel дает возможность работать с одним файлом сразу нескольким пользователям. Десять-двадцать человек на разных компьютерах одновременно вносят какие-то данные в один документ. Где находится определенная информация, работают определенные формулы.

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

В книге Excel со совместным доступом не все задачи можно выполнять.

Нельзя:

  • Создавать таблицы Excel.
  • Создавать, менять или просматривать сценарии.
  • Удалять листы.
  • Объединять либо разделять ячейки.
  • Работать с XML-данными (импортировать, добавлять, обновлять, удалять и т.д.).

Выход: отключить общий доступ – выполнить запрещенную задачу – снова включить доступ.

Совместное пользование ограничивает для участников еще ряд задач:

Недопустимо Реально
Вставлять либо удалять группу ячеек Добавлять строку или столбец
Добавлять либо изменять условные форматы Работать с существующими форматами
Включать или изменять инструмент «Проверка данных» Работать с существующими настройками проверки
Создавать или редактировать диаграммы, сводные отчеты Работать с имеющимися диаграммами и сводными таблицами
Вставлять или редактировать рисунки и графические объекты Просматривать имеющиеся рисунки и графические объекты
Вставлять или менять гиперссылки Переходить по имеющимся гиперссылкам
Назначать, редактировать либо удалять пароли Существующие пароли функционируют
Ставить либо снимать защиту листов и книг Существующая защита работает
Группировать, структурировать данные; вставлять подуровни Работать с имеющимися группами, структурами и подуровнями
Записывать, изменять или просматривать макросы Запускать имеющиеся макросы, которые не связаны с недоступными задачами
Изменять или удалять формулы массива Пользоваться существующими формулами
Добавлять в форму данных новую информацию Искать информацию в форме данных



Сначала определяемся, какую книгу мы “откроем” для редактирования сразу несколькими участниками. Создаем новый файл и наполняем его информацией. Либо открываем существующий.

  1. Переходим на вкладку «Рецензирование». Диалоговое окно «Доступ к книге».
  2. Управление доступом к файлу – правка. Ставим галочку напротив «Разрешить изменять файл нескольким пользователям одновременно».
  3. Переходим к инструменту «Подробнее», чтобы настроить параметры многопользовательского редактирования.
  4. Нажимаем ОК. Если мы открываем общий доступ к новой книге, то выбираем ей название. Если совместное пользование предполагается для существующего файла – жмем ОК.
  5. Открываем меню Microsoft Office. Выбираем команду «Сохранить как». Выбираем тот формат файла сохранения, который «пойдет» на всех пользовательских компьютерах.
  6. Местом сохранения выбираем сетевой ресурс / сетевую папку, которую откроют предполагаемые участники. Нажимаем «Сохранить».

Внимание! Нельзя использовать для сохранения общего файла веб-сервер.

Теперь проверим и обновим ссылки:

  1. Вкладка «Данные». «Подключения».
  2. Изменить ссылки / изменить связи. Если такая кнопка отсутствует, в листе нет связанных файлов.
  3. Переходим на вкладку «Состояние», чтобы проверить имеющиеся связи. На работоспособность связей указывает кнопка ОК.

Открытие книги с совместным доступом

  1. Открываем меню Microsoft Office.
  2. Нажимаем «Открыть».
  3. Выбираем общую книгу.
  4. Когда книга открыта, нажимаем на кнопку Microsoft Office.

    Переходим на вкладку «Параметры Excel» (в нижней части меню).

  5. «Общие» – «Личная настройка» – «Имя пользователя». Вводим опознавательную информацию (имя, ник-нейм).

Все.

Можно редактировать информацию, вводить новую. После работы – сохранить.

Случается, что при открытии книги Excel с совместным доступом к файлу появляется запись «Файл заблокирован». Сохранить не получается. При последующем открытии оказывается, что общий доступ отключен. Возможные причины проблемы:

  1. Одну и ту же часть документа редактируют несколько пользователей. К примеру, вбивают разные данные в одну ячейку. Возникает блокировка.
  2. Во время пользования совместным файлом ведется журнал изменений (кто входил, когда, что делал). Книга увеличивается. Начинает «глючить».
  3. Удалили кого-то из пользователей, но пока ему не сказали об этом. Тогда блокировка может появиться только на его компьютере.
  4. Большая нагрузка на сетевой ресурс.

Что можно сделать, если совместный доступ к файлу файл заблокирован:

  1. Почистить либо удалить журнал изменений.
  2. Почистить содержимое файла.
  3. Отменить, а потом снова активировать совместный доступ.
  4. Открыть книгу xls в OpenOffice. И снова сохранить ее в xls.

Замечено, что запись «Файл заблокирован» реже появляется в новейших версиях Excel.

Как удалить пользователя

  1. На вкладке «Рецензирование» открываем меню «Доступ к книге».
  2. В разделе «Правка» мы видим перечень пользователей.
  3. Выбираем имя – нажимаем «Удалить».

Перед удалением проверьте, что пользователи закончили работу с файлом.

Как отключить режим совместного доступа в Excel

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

  1. Вкладка «Рецензирование» – «Исправления» – «Выделить исправления».
  2. Устанавливаем параметры «Исправлений». По времени – «все». Галочки напротив «Пользователем» и «В диапазоне» сняты. Напротив «вносить изменения на отдельный лист» – стоит. Жмем ОК.
  3. Откроется Журнал изменений. Его можно сохранить либо распечатать.

Чтобы отключить совместный доступ к файлу Excel, на вкладке «Рецензирование» нажать «Доступ к книге» и снять галочку напротив «Разрешить изменять файл нескольким пользователям».

В списке должен остаться один пользователь – Вы.

Как объединить строки в Excel 2010 и 2013 без потери данных – Microsoft Excel для начинающих

Это руководство рассказывает о том, как объединить несколько строк в Excel. Узнайте, как можно быстро объединить несколько строк в Excel без потери данных, без каких-либо макросов и надстроек. Только при помощи формул!

Объединение строк в Excel – это одна из наиболее распространённых задач в Excel, которую мы встречаем всюду. Беда в том, что Microsoft Excel не предоставляет сколько-нибудь подходящего для этой задачи инструмента.

Например, если Вы попытаетесь совместить две или более строки на листе Excel при помощи команды Merge & Center (Объединить и поместить в центре), которая находится на вкладке Home (Главная) в разделе Alignment (Выравнивание), то получите вот такое предупреждение:

The selection contains multiple data values. Merging into one cell will keep the upper-left most data only. (В объединённой ячейке сохраняется только значение из верхней левой ячейки диапазона. Остальные значения будут потеряны.)

Если нажать ОК, в объединённой ячейке останется значение только из верхней левой ячейки, все остальные данные будут потеряны. Поэтому очевидно, что нам нужно использовать другое решение. Далее в этой статье Вы найдёте способы объединить нескольких строк в Excel без потери данных.

Как объединить строки в Excel без потери данных

Задача: Имеется база данных с информацией о клиентах, в которой каждая строка содержит определённые детали, такие как наименование товара, код товара, имя клиента и так далее. Мы хотим объединить все строки, относящиеся к определённому заказу, чтобы получить вот такой результат:

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

Как объединить несколько строк в Excel при помощи формул

Microsoft Excel предоставляет несколько формул, которые помогут Вам объединить данные из разных строк. Проще всего запомнить формулу с функцией CONCATENATE (СЦЕПИТЬ). Вот несколько примеров, как можно сцепить несколько строк в одну:

  • Объединить строки и разделить значения запятой:=CONCATENATE(A1,”, “,A2,”, “,A3)=СЦЕПИТЬ(A1;”, “;A2;”, “;A3)
  • Объединить строки, оставив пробелы между значениями:=CONCATENATE(A1,” “,A2,” “,A3)=СЦЕПИТЬ(A1;” “;A2;” “;A3)
  • Объединить строки без пробелов между значениями:=CONCATENATE(A1,A2,A3)=СЦЕПИТЬ(A1;A2;A3)

Уверен, что Вы уже поняли главное правило построения подобной формулы – необходимо записать все ячейки, которые нужно объединить, через запятую (или через точку с запятой, если у Вас русифицированная версия Excel), и затем вписать между ними в кавычках нужный разделитель; например, «, « – это запятая с пробелом; » « – это просто пробел.

Итак, давайте посмотрим, как функция CONCATENATE (СЦЕПИТЬ) будет работать с реальными данными.

  1. Выделите пустую ячейку на листе и введите в неё формулу. У нас есть 9 строк с данными, поэтому формула получится довольно большая:

    =CONCATENATE(A1,”, “,A2,”, “,A3,”, “,A4,”, “,A5,”, “,A6,”, “,A7,”, “,A8)
    =СЦЕПИТЬ(A1;”, “;A2;”, “;A3;”, “;A4;”, “;A5;”, “;A6;”, “;A7;”, “;A8)

  2. Скопируйте эту формулу во все ячейки строки, у Вас должно получиться что-то вроде этого:
  3. Теперь все данные объединены в одну строку. На самом деле, объединённые строки – это формулы, но Вы всегда можете преобразовать их в значения. Более подробную информацию об этом читайте в статье Как в Excel заменить формулы на значения.

Оцените качество статьи. Нам важно ваше мнение:

Практичные советы — Как объединить ячейки в Excel

У пользователей, только начинающих работать с редактором таблиц Excel, часто возникает вопрос, как объединить ячейки в excel.

Для этого существуют специальные команды и функции в самой программе, а также несколько способов типа надстроек и макросов.

Все они достаточно простые для того, чтобы научиться пользоваться всего за пару приёмов, воспользовавшись следующими советами.

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

При этом они выравниваются по центру, соединяются по строкам или просто совмещаются с оставлением прежнего форматирования.

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

  1. Выделяется диапазон ячеек, которые требуют объединения;
  2. На выделенной части таблицы нажимается правая кнопка мыши;
  3. В открывшемся меню выбирается пункт «Формат ячеек»;
  4. Открывается вкладка «Выравнивание»;
  5. Ставится галочка напротив «Объединение ячеек».

Объединение с помощью контекстного меню

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

Стоит отметить, что при этом в объединённой области остаётся только информация из её левой верхней части, о чём программа заранее предупреждает.

Предупреждение Excel о потере данных при объединении

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

Еще рекомендуем прочитать эти статьи:

  • Таблица Эксель — Cоздание и настройка
  • Сводные таблицы Excel — Примеры создания
  • Макросы в Excel — Инструкция по использованию

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

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

Иконка объединения ячеек для Excel 2003

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

На панели Excel 2007/2010/2013 такая кнопка тоже есть, на вкладке «Главная» (группа «Выравнивание»).

Однако здесь она уже снабжена выпадающим меню для увеличения числа выполняемых с её помощью действий.

Контекстное меню в современных версиях Эксель

Пользуясь приведенными командами, можно не просто объединять элементы таблицы с центральным выравниванием, но и выполнить два дополнительных варианты действий:

  • Создать целую группу объединённых по строкам ячеек;
  • Объединить без выставления текста по центру.

Объединить столбцы даже в этой версии невозможно.

Иногда область не удаётся объединить, а кнопки и команды остаются неактивными. Так происходит, если на листе установлена защита или к книге (документу) разрешён только общий доступ.

Перед соединением областей необходимо удалить все эти условия, открыв возможность к форматированию таблицы.

Для того чтобы слияние происходило без потери важной информации и данные не пришлось распределять по другим ячейкам и возвращать обратно уже после объединения, стоит воспользоваться функцией «Сцепить».

  1. Сначала выбирается ячейка недалеко от объединяемых областей и форматируется нужным образом (например, делается размером 3х3 или 2х6);
  2. В ней пишется формула типа СЦЕПИТЬ (А1; А2), где указываются ячейки (по одной!), текст из которых будет объединяться).

Выбор диапазона объединяемого текста.

Информация в области D1:F8 из ячеек А1 и С3, соединённая без потери текста

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

Ведь, если символ «+» нужен для суммирования содержимого ячеек таблицы, то «&» служит для склеивания их содержимого.

Знак находится на цифре «7» верхнего ряда клавиатуры в английской раскладке.

Текст из A1 и B2 в области A7:B9, объединённый символом «&»

  • Если к информации требуется добавить дополнительный текст, его выделяют кавычками (например: A2&«новый текст»&B4);
  • Если пользоваться функцией извлечения первых букв (ЛЕВСИМВ), можно получить при объединении фамилию и инициалы.

Получение Ф.И.О. из полных данных

Другой интересный материал на тему:

  • Выпадающий список в Excel — Инструкция по созданию
  • Формулы EXCEL с примерами — Инструкция по применению

Объединять ячейки в таблице одним и тем же способом (например, создавая области 2х2), можно ускоренным способом, при помощи клавиш «Ctrl»+«C» и «Ctrl»+«V», соответственно, копируя и вставляя форматирование.

Те же действия осуществляются путём написания макроса – в виде формулы или, используя макрорекордер.

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

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

Объединение ячеек в excel

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

Способ первый – классический, самый простой

Выделяем ячейки для объединения.

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

Получаем объединенную ячейку. Задача решена.

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

Например, кнопка Объединить ячейки в Excel 2007 отличается от такой же кнопки в Excel 2013

Поэтому стоит научиться пользоваться контекстным меню, т.к. в отличие от пиктограмм и прочих графических эффектов от версии к версии набор команд и название параметров в таком меню не меняется (могут добавиться новые, но старые сохраняются).

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

Способ второй объединение ячеек

Открываем excel, чтобы объединить ячейки выполняем следующие действия:

  1. Выделяем нужный диапазон (все ячейки, которые надо объединить);
  2. Делаем правой кнопкой мышки щелчок;
  3. В появившемся меню (контекстном) отмечаем строку Формат ячеек;
  1. В открытом диалоговом окне Формат ячеек переходим на вкладку Выравнивание;
  2. Находим кнопку-флаг Объединение ячеек, ставим галочку:

Радуемся полученному результату.

Способ третий – используем, если объединение ячеек надо повторять много раз, на разных фрагментах

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

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

  • Выделяем первый диапазон ячеек, объединяем их любым из описанных выше способов.
  • Затем выделяем новый диапазон, который тоже требует объединения, нажимаем клавишу F4, получаем результат.
  • Продолжаем объединять один фрагмент за другим, не забывая нажимать F4

Дополнительные возможности настроек

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

Для того чтобы выполнить объединение без потери данных можно использовать дополнительную надстройку VBA-Excel (бесплатно распространяется демоверсия).

После ее установки на ленте появится новая вкладка с большим набором функций:

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

Выбираем способ объединения и вид разделителя.

Получаем результат:

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

12 наиболее распространённых проблем с Excel и способы их решения

Читатели Лайфхакера уже знакомы с Денисом Батьяновым, который делился с нами секретами Excel. Сегодня Денис расскажет о том, как избежать самых распространённых проблем с Excel, которые мы зачастую создаём себе самостоятельно.

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

Вы не даёте заголовки столбцам таблиц

Многие инструменты Excel, например: сортировка, фильтрация, умные таблицы, сводные таблицы, — подразумевают, что ваши данные содержат заголовки столбцов. В противном случае вы либо вообще не сможете ими воспользоваться, либо они отработают не совсем корректно. Всегда заботьтесь, чтобы ваши таблицы содержали заголовки столбцов.

Пустые столбцы и строки внутри ваших таблиц

Это сбивает с толку Excel. Встретив пустую строку или столбец внутри вашей таблицы, он начинает думать, что у вас 2 таблицы, а не одна. Вам придётся постоянно его поправлять. Также не стоит скрывать ненужные вам строки/столбцы внутри таблицы, лучше удалите их.

На одном листе располагается несколько таблиц

Если это не крошечные таблицы, содержащие справочники значений, то так делать не стоит.

Вам будет неудобно полноценно работать больше чем с одной таблицей на листе. Например, если одна таблица располагается слева, а вторая справа, то фильтрация одной таблицы будет влиять и на другую.

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

Оно вам надо?

Данные одного типа искусственно располагаются в разных столбцах

Очень часто пользователи, которые знают Excel достаточно поверхностно, отдают предпочтение такому формату таблицы:

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

Дело в том, что данный формат содержит 2 измерения: чтобы найти что-то в таблице, вы должны определиться со строкой, перебирая филиал, группу и агента. Когда вы найдёте нужную стоку, то потом придётся искать уже нужный столбец, так как их тут много. И эта «двухмерность» сильно усложняет работу с такой таблицей и для стандартных инструментов Excel — формул и сводных таблиц.

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

У вас нет одного поля по объёму продаж, которым можно удобно манипулировать, а есть 12 отдельных полей.

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

Если вы захотите применить стандартные формулы суммирования типа СУММЕСЛИ (SUMIF), СУММЕСЛИМН (SUMIFS), СУММПРОИЗВ (SUMPRODUCT), то также обнаружите, что они не смогут эффективно работать с такой компоновкой таблицы.

Рекомендуемый формат таблицы выглядит так:

Разнесение информации по разным листам книги «для удобства»

Ещё одна распространенная ошибка — это, имея какой-то стандартный формат таблицы и нуждаясь в аналитике на основе этих данных, разносить её по отдельным листам книги Excel.

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

Накапливайте информацию на ОДНОМ листе.

Информация в комментариях

Часто пользователи добавляют важную информацию, которая может им понадобиться, в комментарий к ячейке. Имейте в виду, то, что находится в комментариях, вы можете только посмотреть (если найдёте). Вытащить это в ячейку затруднительно. Рекомендую лучше выделить отдельный столбец для комментариев.

Бардак с форматированием

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

  1. Каждая таблица должна иметь однородное форматирование. Пользуйтесь форматированием умных таблиц. Для сброса старого форматирования используйте стиль ячеек «Обычный».
  2. Не выделяйте цветом строку или столбец целиком. Выделите стилем конкретную ячейку или диапазон. Предусмотрите «легенду» вашего выделения. Если вы выделяете ячейки, чтобы в дальнейшем произвести с ними какие-то операции, то цвет не лучшее решение. Хоть сортировка по цвету и появилась в Excel 2007, а в 2010-м — фильтрация по цвету, но наличие отдельного столбца с чётким значением для последующей фильтрации/сортировки всё равно предпочтительнее. Цвет — вещь небезусловная. В сводную таблицу, например, вы его не затащите.
  3. Заведите привычку добавлять в ваши таблицы автоматические фильтры (Ctrl+Shift+L), закрепление областей. Таблицу желательно сортировать. Лично меня всегда приводило в бешенство, когда я получал каждую неделю от человека, ответственного за проект, таблицу, где не было фильтров и закрепления областей. Помните, что подобные «мелочи» запоминаются очень надолго.

Объединение ячеек

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

Объединение текста и чисел в одной ячейке

Тягостное впечатление производит ячейка, содержащая число, дополненное сзади текстовой константой « РУБ.» или » USD», введенной вручную. Особенно, если это не печатная форма, а обычная таблица. Арифметические операции с такими ячейками естественно невозможны.

Числа в виде текста в ячейке

Избегайте хранить числовые данные в ячейке в формате текста. Со временем часть ячеек в таком столбце у вас будут иметь текстовый формат, а часть в обычном. Из-за этого будут проблемы с формулами.

Если ваша таблица будет презентоваться через LCD проектор

Выбирайте максимально контрастные комбинации цвета и фона. Хорошо выглядит на проекторе тёмный фон и светлые буквы. Самое ужасное впечатление производит красный на чёрном и наоборот. Это сочетание крайне неконтрастно выглядит на проекторе — избегайте его.

Страничный режим листа в Excel

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

Не рекомендую постоянно работать в этом режиме, что многие делают, так как в процессе вывода данных на экран участвует драйвер принтера, а это в зависимости от многих причин (например, принтер сетевой и в данный момент недоступен) чревато подвисаниями процесса визуализации и пересчёта формул. Работайте в обычном режиме.

Ещё больше полезной информации про Excel можно узнать на сайте Дениса.

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

У стандартного объединения в Excel имеется недостаток, который заметен только при объединении нескольких ячеек с данными, когда сохраняется значение только верхней левой ячейки выделенного диапазона, а остальные затираются. Но часто возникает необходимость сохранить все данные во время объединения.

Настройка office-menu включает в себя возможность объединения ячеек без потери данных с возможностью их разделения специальными символами.

Установив надстройку (ссылка для ее скачивания расположена в конце статьи), на ленте появится новая вкладка «office-menu», на которой располагаются кнопки объединения без потерь и 2 поля для указания разделителей.

Кнопка «Объединить без потерь»

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

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

Для лучшего понимания на рисунке показан пример:

Кнопка «Объединить строки»

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

Кнопка «Объединить столбцы»

Действие этой кнопки создает объединенные области в диапазоне, разделенные по столбцам. Для разделителя используется только разделитель строк. Пример:

Кнопка «Разбить по шаблону»

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

Разделители значений и строк

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

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

Например, объединим ячейки таким образом, чтобы их значения были окружены html-тегами ячейки таблицы ( и

), а строки html-тегами строки таблицы ( и

). Для этого запишем в поле «Разделитель значений:» строку « ~

», а в поле «Разделитель строк:» – « ~

». После объединения без потерь в области появится такой текст: Знач.1 Знач.2 Знач.3 Знач.4 Знач.5 Знач.6 Знач.7 Знач.8 Знач.9 Знач.10 Знач.11 Знач.12

Для указания разделителем символа тильда запишите его 2 раза подряд (~~).

Для того, чтобы использовать описанные в данной категории функции, скачайте и установите нашу надстройку.
Работа надстройки была успешно протестирована на версиях Excel: 2007, 2010 и 2013. В случае возникновения проблем с ее использованием, сообщайте Администрации сайта.