Как объединить ячейки в excel разными способами и как их разделить
При оформлении таблиц для более наглядного отображения информации возникает необходимость объединить несколько ячеек в одну. Это часто используется например при указании одного общего заголовка данных, которые имеют разные значения. Пример такого отображения информации вы можете видеть на изображении ниже. О том, как объединить ячейки в excel пошагово читайте далее. Объединить можно не только по горизонтали, возможно объединения по вертикали, а так же группы горизонтальных и вертикальных ячеек.
Пример объединения разряда с одним заголовком «Разряд» и разными данными
Как объединить ячейки в excel
Для объединения ячеек можно воспользоваться двумя способами. Первый — это при помощи контекстного меню через формат. Выделите все ячейки, которые вы хотите обхединить и нажмите на выделенной области правой кнопкой мыши. В выпадающем контекстном меню выберите пункт «Формат ячеек…».
Объединяем группу ячеек через контекстное меню и формат
В окне формата перейдите на вкладку «Выравнивание» и в блоке отображение отметьте галочкой пункт «объединение ячеек».
Отметьте пункт «объединение ячеек» во вкладке «Выравнивание»
Если в ячейках присутствуют какие либо данные, Excel выдает каждый раз предупреждение о потери всех данных кроме верхней левой. Поэтому будьте осторожны при объединении и не потеряйте важные данные. Если все же вам необходимо объединить ячейки с данными, соглашайтесь нажав «Ок».
В моем случае из 10 чисел в ячейках в области объединения осталось только число «1» из левой верхней ячейки.
По умолчанию эксел после объединения выравнивает данные по правому краю. Если вам необходимо быстро объединить и при этом сразу же выровнять данные по центру, можно воспользоваться вторым способом.
Так же как и в предыдущем методе выделите ячейки, которые необходимо объединить. В верхней части программы во вкладке «ГЛАВНАЯ» найдите блок, который называется выравнивание. В этом блоке есть выпадающий список, который позволяет объединять ячейки. Для объединения есть три типа:
- Объединить и поместить в центре — результатом нажатия на этот пункт будет точно такое же объединение как и в предыдущем примере, но Excel отформатирует выравнивание результирующие данные по центру.
- Объединить по строкам — если выделена область ячеек с несколькими строками, программа объединит построчно и в случае, если присутствуют данные, оставит только те, которые находились в левых.
- Объединить ячейки — этот пункт действует точно так же как и в первом варианте через формат.
- Отменить объединение — вам необходимо выделить ячейку, которая ранее была объединена и нажать на пункт меню — программа восстановит структуру ячеек как до объединения. Данные до объединения естественно не восстановит.
Попробовав любой из способов, вы будете знать как объединить ячейки в экселе.
Второй способ быстрого объединения ячеек
https://youtube.com/watch?v=CsUY3EysHEI
Как разделить ячейку в экселе на две
Структура документа Excel строго определена и чтобы не было проблем и ошибок в дальнейшем с вычислениями и формулами, в программе каждому блоку данных (ячейке) должен соответствовать уникальный «адрес». Адрес — это буквенно-числовое обозначение пересечения столбца и строки. Соответственно одному столбцу должно соответствовать только ода ячейка строки. Соответственно разделить ранее созданную ячейку на две не получится. Чтобы иметь разделенную на две ячейку необходимо заранее продумать структуру таблицы. В том столбце, где необходимо разделение необходимо запланировать два столбца и объединить все ячейки с данными, где нет разделения. В таблице это выглядит следующим образом.
Разделить ячейку в экселе на две не возможно. Можно только запланировать структуру таблицы при создании.
В строке 4 у меня присутствует разделенная на две ячейка. Для этого я для столбца «Разряд» запланировал заранее два столбца «B» и «С». Затем в строках, где мне не надо разделение я объединил построчно ячейки, а в строке 4 оставил без объединения. В результате таблица содержит столбец «Раздел» с разделенной на две ячейкой в 4-й строке. С таким способом создания разделения каждая ячейка имеет свой уникальный «Адрес» и к ней можно обращаться в формулах и при адресации.
Если вы попробуете создать формулу с ссылкой на объединенную ячейку, вы увидите что ее адрес как говорилось выше соответствует левой верхней ячейке объединения. На картинке ниже объединенным ячейкам B2 и C2 соответствует адрес B2.
Адрес объединенных ячеек соответствует одной левой
Теперь вы знаете как разбить ячейку в excel на две и сможете заранее запланировать структуру таблицы чтобы потом не ломать уже созданную таблицу.
Поделиться «Как объединить ячейки в экселе и как разделить ячейку в excel на две»
Делим слипшийся текст на части
Итак, имеем столбец с данными, которые надо разделить на несколько отдельных столбцов. Самые распространенные жизненные примеры:
- ФИО в одном столбце (а надо — в трех отдельных, чтобы удобнее было сортировать и фильтровать)
- полное описание товара в одном столбце (а надо — отдельный столбец под фирму-изготовителя, отдельный — под модель для построения, например, сводной таблицы)
- весь адрес в одном столбце (а надо — отдельно индекс, отдельно — город, отдельно — улица и дом)
- и т.д.
Способ 1. Текст по столбцам
Выделите ячейки, которые будем делить и выберите в меню Данные — Текст по столбцам (Data — Text to columns) . Появится окно Мастера разбора текстов:
На первом шаге Мастера выбираем формат нашего текста. Или это текст, в котором какой-либо символ отделяет друг от друга содержимое наших будущих отдельных столбцов (с разделителями) или в тексте с помощью пробелов имитируются столбцы одинаковой ширины (фиксированная ширина).
На втором шаге Мастера, если мы выбрали формат с разделителями (как в нашем примере) — необходимо указать какой именно символ является разделителем:
Если в тексте есть строки, где зачем-то подряд идут несколько разделителей (несколько пробелов, например), то флажок Считать последовательные разделители одним (Treat consecutive delimiters as one) заставит Excel воспринимать их как один.
Выпадающий список Ограничитель строк (Text Qualifier) нужен, чтобы текст заключенный в кавычки (например, название компании «Иванов, Манн и Фарбер») не делился по запятой внутри названия.
И, наконец, на третьем шаге для каждого из получившихся столбцов, выделяя их предварительно в окне Мастера, необходимо выбрать формат:
- общий — оставит данные как есть — подходит в большинстве случаев
- дата — необходимо выбирать для столбцов с датами, причем формат даты (день-месяц-год, месяц-день-год и т.д.) уточняется в выпадающем списке
- текстовый — этот формат нужен, по большому счету, не для столбцов с ФИО, названием города или компании, а для столбцов с числовыми данными, которые Excel обязательно должен воспринять как текст. Например, для столбца с номерами банковских счетов клиентов, где в противном случае произойдет округление до 15 знаков, т.к. Excel будет обрабатывать номер счета как число:
Кнопка Подробнее (Advanced) позволяет помочь Excel правильно распознать символы-разделители в тексте, если они отличаются от стандартных, заданных в региональных настройках.
Способ 2. Как выдернуть отдельные слова из текста
Если хочется, чтобы такое деление производилось автоматически без участия пользователя, то придется использовать небольшую функцию на VBA, вставленную в книгу. Для этого открываем редактор Visual Basic:
- в Excel 2003 и старше — меню Сервис — Макрос — Редактор Visual Basic(Tools — Macro — Visual Basic Editor)
- в Excel 2007 и новее — вкладка Разработчик — Редактор Visual Basic (Developer — Visual Basic Editor) или сочетание клавиш Alt+F11
Вставляем новый модуль (меню Insert — Module) и копируем туда текст вот этой пользовательской функции:
Теперь можно найти ее в списке функций в категории Определенные пользователем (User Defined) и использовать со следующим синтаксисом:
=SUBSTRING(Txt; Delimeter; n)
- Txt — адрес ячейки с текстом, который делим
- Delimeter — символ-разделитель (пробел, запятая и т.д.)
- n — порядковый номер извлекаемого фрагмента
Способ 3. Разделение слипшегося текста без пробелов
Тяжелый случай, но тоже бывает. Имеем текст совсем без пробелов, слипшийся в одну длинную фразу (например ФИО «ИвановИванИванович»), который надо разделить пробелами на отдельные слова. Здесь может помочь небольшая макрофункция, которая будет автоматически добавлять пробел перед заглавными буквами. Откройте редактор Visual Basic как в предыдущем способе, вставьте туда новый модуль и скопируйте в него код этой функции:
Теперь можно использовать эту функцию на листе и привести слипшийся текст в нормальный вид:
Разделить текст на несколько ячеек с обновляемыми результатами
Описанный выше метод при изменении исходных данных не позволял изменить данные в результате, сейчас рассмотрим пример как сделать автоматическое обновление результата при изменении исходных данных.
Итак, предположим, что у вас есть те же данные, что и выше:
Чтобы получить фамилию из этого списка, используйте следующую формулу:
Приведенная выше формула найдет первый пробел, и вернет весь текст который был перед этим пробелом.
Эта формула использует функцию НАЙТИ, чтобы найти позицию символа пробела. В случае с Иванов Иван пробел будет найден на 7-й позиции. Затем эта формула извлекает все символы слева от найденного пробела с помощью функции ЛЕВСИМВ.
Аналогичным образом, чтобы получить имя, используйте следующую формулу:
Эта формула использует функцию НАЙТИ, чтобы получить положение. Затем вычитает полученное число из общей длины имени (которая предоставляется функцией ДЛСТР).
Затем это имя извлекается с помощью функции ПРАВСИМВ.
Делим текст вида ФИО по столбцам.
Если выяснение загадочных поворотов формул Excel – не ваше любимое занятие, вам может понравиться визуальный метод разделения ячеек, который демонстрируется ниже.
В столбце A нашей таблицы записаны Фамилии, имена и отчества сотрудников. Необходимо разделить их на 3 столбца.
Можно сделать это при помощи инструмента «Текст по столбцам». Об этом методе мы достаточно подробно рассказывали, когда рассматривали, как можно разделить ячейку по столбцам.
Кратко напомним:
На ленте «Данные» выбираем «Текст по столбцам» – с разделителями.
Далее в качестве разделителя выбираем пробел.
Обращаем внимание на то, как разделены наши данные в окне образца. В следующем окне определяем формат данных
По умолчанию там будет «Общий». Он нас вполне устраивает, поэтому оставляем как есть. Выбираем левую верхнюю ячейку диапазона, в который будет помещен наш разделенный текст. Если нужно оставить в неприкосновенности исходные данные, лучше выбрать B1, к примеру
В следующем окне определяем формат данных. По умолчанию там будет «Общий». Он нас вполне устраивает, поэтому оставляем как есть. Выбираем левую верхнюю ячейку диапазона, в который будет помещен наш разделенный текст. Если нужно оставить в неприкосновенности исходные данные, лучше выбрать B1, к примеру.
В итоге имеем следующую картину:
При желании можно дать заголовки новым столбцам B,C,D.
А теперь давайте тот же результат получим при помощи формул.
Для многих это удобнее. В том числе и по той причине, что если в таблице появятся новые данные, которые нужно разделить, то нет необходимости повторять всю процедуру с начала, а просто нужно скопировать уже имеющиеся формулы.
Итак, чтобы выделить из нашего ФИО фамилию, будем использовать выражение
В качестве разделителя мы используем пробел. Функция ПОИСК указывает нам, в какой позиции находится первый пробел. А затем именно это количество букв (за минусом 1, чтобы не извлекать сам пробел) мы «отрезаем» слева от нашего ФИО при помощи ЛЕВСИМВ.
Далее будет чуть сложнее.
Нужно извлечь второе слово, то есть имя. Чтобы вырезать кусочек из середины, используем функцию ПСТР.
Как вы, наверное, знаете, функция Excel ПСТР имеет следующий синтаксис:
ПСТР (текст; начальная_позиция; количество_знаков)
Текст извлекается из ячейки A2, а два других аргумента вычисляются с использованием 4 различных функций ПОИСК:
Начальная позиция – это позиция первого пробела плюс 1:
ПОИСК(” “;A2) + 1
Количество знаков для извлечения: разница между положением 2- го и 1- го пробелов, минус 1:
ПОИСК(” “;A2;ПОИСК(” “;A2)+1) – ПОИСК(” “;A2) – 1
В итоге имя у нас теперь находится в C.
Осталось отчество. Для него используем выражение:
В этой формуле функция ДЛСТР (LEN) возвращает общую длину строки, из которой вы вычитаете позицию 2- го пробела. Получаем количество символов после 2- го пробела, и функция ПРАВСИМВ их и извлекает.
Вот результат нашей работы по разделению фамилии, имени и отчества из одной по отдельным ячейкам.
Способы разбить ячейку в Excel
Для таблиц характерны три варианта разделения: горизонтальное, вертикальное, диагональное. Последнее встречается реже и несколько сложнее в реализации, поэтому оно рассматривается ближе к концу статьи.
Способ 1: Слияние вместо деления
Данный метод обычно применяется при создании «шапки», в которой указываются наименования столбцов.
- Выделяем две соседние ячейки: зажимаем левую кнопку мыши и проводим курсором в нужном направлении. На панели быстрого доступа нажимаем «Объединить и поместить в центре».
Теперь можно работать с объединённой областью, заполнять её числовыми или текстовыми данными, форматировать отображаемую информацию, как того требует необходимость. Выведем границы и изменим параметры шрифта.
Таким образом можно объединить любое количество ячеек по вертикали и горизонтали. Чтобы было удобно разбивать строки посреди таблицы, предварительно создаются столбы из сдвоенных ячеек.
Способ 2: Вставка столбца/строки
Продумать структуру сложной таблицы сразу иногда бывает проблематично, и в процессе работы возникает необходимость дополнительного разбиения какой-либо области. Существует простой способ это сделать.
- Выделяем ячейку и щёлкаем правой кнопкой мыши.
Выбираем пункт «Вставить». Устанавливаем маркер на «Строку» или «Столбец» в зависимости от того, как будет делиться ячейка, по вертикали или горизонтали.
Приложение создаёт новый пустой ряд.
Объединяем всё в нужном порядке. Лишние пустые области удаляются методом слияния с уже заполненными.
Способ 3: Деление по диагонали с помощью границ
Ячейка в Excel является конечным элементом, дробление которого по правилам программы невозможно. Исходя из этого, полноценного деления по диагонали с двумя разными областями для данных средствами приложения не сделать. Для красивого оформления таблицы можно использовать границы или фигуры.
- Нажимаем на панели быстрого доступа кнопку «Границы», затем в выпавшем меню выбираем «Другие границы».
На соответствующей вкладке устанавливаем тип линий и их направление.
В итоге получается что-то похожее на изображение снизу.
Способ 4: Использование инструмента «Фигуры»
Ещё один приём, которым можно разбить ячейку – это фигуры. На выбор есть выноски, звёзды, круги, прямоугольники, стрелки и, конечно же, линии. Они нам и пригодятся.
- Переходим на вкладку «Вставка». Находим и нажимаем «Фигуры».
Проводим разделительную черту от одного угла до другого.
Важный момент: Координаты начала и конца линии привязываются к ячейкам, в которых они находятся. Следовательно, при увеличении/уменьшении ширины столбцов и высоты строк, её наклон также изменится.
Чтобы иметь возможность красиво разместить в углах разный текст, делят сразу четыре ячейки, как показано на скриншоте.
Далее оформляем таблицу с помощью инструмента «Границы».
Форматирование одинаково работает во всех версиях табличного редактора Excel. Объединять и разбивать ячейки можно большими группами, что неплохо работает в сочетании с двумя типами границ: видимыми и невидимыми. Это позволяет создавать информативные и логичные по структуре таблицы, максимально удобные для конечного пользователя.
Использование текстовых функций
Еще один способ разбить ячейку в Excel — это использовать различные текстовые функции. Текстовые функции позволяют извлекать фрагменты ячейки, которые можно вывести в другую ячейку.
Текстовые функции в Excel включают в себя:
- Left(): извлечение ряда символов из левой части текста
- Right(): извлечение ряда символов из правой части текста
- Mid(): извлечение ряда символов из середины строки
- Find(): найти подстроку внутри другой строки
- Len(): возвращает общее количество символов в строке текста
Для разделения ячеек вам может не понадобиться использовать все эти функции. Однако есть несколько способов, которыми вы можете использовать их для достижения одной и той же цели.
Например, вы можете использовать функции Left и Find для извлечения первого имени. Функция Find помогает, потому что она может сказать вам, где находится разделяющий символ. В данном случае это пространство.
Таким образом, функция будет выглядеть следующим образом:
Когда вы нажмете клавишу enter после ввода этой функции, вы увидите, что первое имя извлекается из строки в ячейке C3.
Это работает, потому что левая функция нуждается в количестве символов для извлечения. Поскольку символ пробела расположен в конце первого имени, вы можете использовать функцию Find для поиска пробела, которая возвращает количество символов, необходимых для получения первого имени.
Вы можете извлечь фамилию либо с помощью функции Right, либо с помощью функции Mid.
Использование функции Right
Это позволит извлечь последнее имя, найдя позицию пробела, а затем вычесть его из длины общей строки. Это даёт функции количество символов, необходимых для извлечения фамилии.
Технически вы можете сделать то же самое, используя функцию Mid, например:
В этом случае функция Find дает среднюю функцию начальной точкой, А Len в сочетании с Find обеспечивает количество символов для извлечения. Это также вернёт фамилию.
Использование текстовых функций Excel для разделения ячейки в Excel работает так же, как и решение преобразования текста в столбец, но оно также позволяет заполнить весь столбец под этими результатами с помощью тех же функций.
Как умножать в «Экселе» ячейки между собой?
ПРОИЗВЕД. Например формулаПеретащите формулу в ячейке3 или сумму возмещения клавиши ВВОД в B6 будет равен а также формат решить вопрос о только значение первой То есть, если соответствующих ссылок, указывавших с явным заданием вот так: Online автоматически обновляет
Умножение в «Экселе»
простой формулы, используя использовать этот же это седьмая часть=PRODUCT(A2,A4:A15,12,E3:E5,150,G4,H4:J6) B2 вниз в12 транспортных расходов по ячейке B2. нулю. ячеек. Так, например, том, как умножать строки данного столбца, необходимо перемножить попарно на множители. Для чисел, так иПРОИЗВЕД(A1:A3;C1:C3) результат формулы. символ «звездочка» (*) коэффициент, но в от 100%, т.еумножает значений в
Умножение ячейки на ячейку
другие ячейки в=A3*$C$2 командировке (общее расстояние ×Выберите ячейку B2.Чтобы умножить все числа при делении ячейки в «Экселе» числа поскольку адрес ячейки значения в двух того чтобы избежать с использованием ячеек,Результат равен 18 164 250. ОнЕсли для умножения вы в качестве оператора диалоговом окне в
0,07% (7:100=0,07). двух отдельные ячейки столбце B.48 0,46). Существует несколькоДважды щелкните маленький зеленый в столбце A на ячейку, при между собой в будет изменяться с столбцах, достаточно записать такой ошибки, следует содержащих в себе не изменится, если предпочитаете использовать функцию, умножения. «Специальная вставка», поставитьПолучается: 100% текущая (A2 и G4)Для выполнения этой задачи=A4*$C$2 способов умножения чисел. квадрат в правом на ячейку C2, отсутствии значения в несколько раз быстрее. каждым спуском ячейки формулу умножения для либо в ячейке, и сами числа, ввести формулу в это может бытьВыделите пустую ячейку и галочку у функции цена + 7%
Перемножение столбцов и строк
двух чисел (12 используется оператор729Для выполнения этой задачи нижнем углу ячейки. добавьте символы $ одной из них,Для того чтобы перемножить на одну позицию. первой пары ячеек, куда необходимо скопировать и формулы, их таком виде: функция ПРОИЗВЕД. введите знак равенства «Разделить». наценка = (от и 150) и*=A5*$C$2 используйте арифметический операторФормула автоматически копируется на в ссылку на результатом будет ошибка некоторый диапазон ячеекЧтобы избежать этого, необходимо
после чего, удерживая результат, ввести ссылку определяющие. Для того=A1*A2*A3*C1*C2*C3Функция ПРОИЗВЕД также умножает (Получилось так. текущей цены) 107% значения в трех(звездочка) или функция1534* последующие ячейки столбца ячейку следующим образом:
Умножение строки или столбца на ячейку
появившийся в правом либо «зафиксировать» адрес или несколько ячеек одновременно использовать числа эффективна при умножении). Помните, что все на проценты, на коэффициент 1,07 (107:100=1,07). и H4:J6)..288Например при вводе вПосле копирования формулы в, как показано вПредположим, нужно умножить столбец «Вставить функцию» операцию
Как перемножить все числа в строке или столбце
либо её целиком нижнем углу, растянуть её множителей с на число или и ссылки на большого количества чисел формулы начинаются со примереВ ячейку C1Рассмотрим,1=A7*$C$2 ячейку столбце B появляются следующем примере. чисел на одно с именем «ПРОИЗВЕД». — в таком результат вниз, вдоль помощью знака «$». другие ячейки, необходимо ячейки, а количество или диапазонов чисел.
знака равенства.подсчета наценки, скидки пишем наш коэффициенткак умножить столбец Excel24306формулы = 5 * правильные результаты.Символ $ указывает Excel и то же В качестве аргументов случае будет возможность всех значений столбцов-множителей. Знак доллара сохраняет в строке формул аргументов (элементов данных, Если вы попытаетесьВведите числа, добавляя между, смотрите в статье 1,07. на число, как3=A8*$C$2 10Примечание:
Резюме
Online, что ссылка число в другой данной функции необходимо использовать её значениеДля того чтобы добиться значение ссылки по после знака равенства которые обрабатывает функция) перемножить диапазоны чисел каждой парой символ «Как умножить вВыделяем ячейку С1 прибавить проценты в4Введите формулув ячейке отображаетсяМы стараемся как на ячейку C2
ячейке. Для этого
fb.ru>
Используем инструмент «Мгновенное заполнение».
Если вы используете любую версию Excel 2013 — Excel 2019, вы можете воспользоваться функцией Мгновенное заполнение, которая может не только автоматически заполнять диапазон данными, но и разделять их содержимое.
Если вы ещё не использовали эту функцию, то я быстро поясню ее работу. Программа анализирует данные, которые вы вводите в таблицу, и пытается определить, существует ли в них какая-либо закономерность. Как только Excel вычислит закономерность, то сразу предложит свой вариант.
Таким образом, при помощи этого инструмента вы можете взять какую-то часть данных и вставить их в новый столбец. Нагляднее и понятнее будет на примере.
Первым делом, убедитесь, что инструмент «Мгновенное заполнение» включен. Вы найдёте этот параметр на вкладке Файл > Параметры > Дополнительно > Автоматически выполнять мгновенное заполнение.
Теперь давайте посмотрим, как его можно использовать, чтобы автоматически разбить данные по нескольким столбцам. Итак, вы начинаете вводить с клавиатуры данные, которые нужно записать в отдельные позиции. По мере ввода Excel будет пытаться распознать закономерность в вводимых значениях, и как только он ее поймет, данные автоматически будут вставлены в остальные позиции ниже. Чтобы понять, как это работает, посмотрите на рисунок ниже:
Давайте возьмем столбец данных из нашего первого примера и посмотрим, как Мгновенное заполнение в Excel может помочь нам разделить ячейку пополам:
- Вставьте новый столбец рядом с столбцом с исходными данными и введите желаемую часть текста в первую ячейку (имя сотрудника в этом примере).
- Введите текст еще в пару ячеек. Как только программа обнаружит, по какому шаблону вводятся значения, он предложит заполнить остальные. В нашем случае, чтобы выяснить закономерность, потребовалось всего 2 записи:
- Если вас устраивает то, что вы видите, нажмите клавишу Enter, и все имена сразу будут вставлены ниже.
Если «Мгновенное заполнение» включено, но не предлагает вам никаких вариантов, можно принудительно запустить этот инструмент вручную на вкладке «Данные» или нажав сочетание клавиш Ctrl+E.