Разрыв столбца в excel

Обновлено: 06.07.2024

Хотим, упрощенно говоря, повернуть таблицу на бок, т.е. то, что располагалось в строке - пустить по столбцу и наоборот:

Способ 1. Специальная вставка

Выделяем и копируем исходную таблицу (правой кнопкой мыши - Копировать). Затем щелкаем правой по пустой ячейке, куда хотим поместить повернутую таблицу и выбираем из контекстного меню команду Специальная вставка (Paste Special) . В открывшемся диалоговом окне ставим галочку Транспонировать (Transpose) и жмем ОК.

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

Плюсы : в транспонированной таблице сохраняется исходное форматирование ячеек.

Способ 2. Функция ТРАНСП

Выделяем нужное количество пустых ячеек (т.е. если, например, исходная таблица была из 3 строк и 5 столбцов, то выделить обязательно нужно диапазон из 5 строк и 3 столбцов) и вводим в первую ячейку функцию ТРАНСП (TRANSPOSE) из категории Ссылки и массивы (Lookup and Reference):

После ввода функции необходимо нажать не Enter, а Ctrl+Shift+Enter, чтобы ввести ее сразу во все выделенные ячейки как формулу массива . Если раньше не сталкивались с формулами массивов, то советую почитать тут - это весьма экзотический, но очень мощный инструмент в Excel.

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

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

Способ 3. Формируем адрес сами

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

Функция АДРЕС(номер_строки; номер_столбца) - выдает адрес ячейки по номеру строки и столбца на листе, т.е. АДРЕС(2;3) выдаст, например, ссылку на ячейку C2.
Функция ДВССЫЛ(ссылка_в_виде_текста) - преобразует текстовую строку, например, "F3" в настоящую ссылку на ячейку F3.
Функции СТРОКА(ячейка) и СТОЛБЕЦ(ячейка) - выдают номер строки и столбца для заданной ячейки, например =СТРОКА(F1) выдаст 1, а =СТОЛБЕЦ(А3) выдаст 3.

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

=ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1);СТРОКА(A1)))

в английской версии Excel это будет =INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)))

А затем копируем (протягиваем) формулу на соседние ячейки как обычно черным крестом. В итоге должно получиться примерно следующее:

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

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

Минусы : форматирование не сохраняется, но его можно легко воспроизвести Специальной вставкой (вставить только Формат с флажком Транспонировать

Замечали, что, когда вводишь текст в Excel и нажимаешь кнопку Enter текст либо растягивается на свободные ячейки, либо, если включена опция переноса слов, он переносится внутри ячейки на один ряд вниз по ширине самой ячейки. А, что делать если Вам нужно определенные слова перенести в ячейке ниже не привязываясь к ширине ячейки? Как сделать разрыв строки там, где мы хотим, а не где нам предлагает Excel ?

В этой статье мы узнаем как можно несколькими способами вставить разрыв строки в Excel

Вставка разрыва строки с помощью сочетания клавиш

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

Вот как вставить разрыв строки с помощью сочетания клавиш:

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

Обратите внимание, что вам нужно, чтобы ячейка находилась в режиме «Перенос текста» , чтобы содержимое отображалось в следующей строке. Если он не применяется, Вы увидите весь текст в одной ячейке (даже если у вас есть разрыв строки). Параметр «Перенос текста» доступен на вкладке «Главная» на ленте.

Вставка разрывов строк с помощью формул

Сочетание клавиш Alt+Enter - это быстрый способ добавить разрыв строки, если вам нужно сделать это только для нескольких ячеек. Но если вам нужно сделать для этого большого количества ячеек, Вы можете использовать другие методы.

Вы можете добавить разрыв строки как часть результата формулы.

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

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

Всем доброго времени суток. Очень частая проблема, которая встречается пользователям таблиц Excel, это пронумеровать строки в создаваемой таблице. Кто как только не ухищряется это делать.

Основных способов несколько. Я перечислю их по частоте использования:

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

Способ протягивания мышью от первой ячейки до самой последней Способ протягивания мышью от первой ячейки до самой последней

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

Однако в этом простейшем способе есть ряд недостатков:

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

2. Если в таблице опять встречается разрыв строк, но в отличие от первого недостатка, этот разрыв строки состоит из объединенных ячеек

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

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

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

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

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

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

3. Способ самый продвинутый и предполагает использование кода VBA.

В этом способе напрочь отсутствуют недостатки первого и второго способа. Программа просто нумерует строки от первой до последней ячейки. Единственное, что нужно сделать, это выделить все строки с места, от которого нужно пронумеровать. ОПЯТЬ ВЫДЕЛИТЬ. Чем это отличается от первого способа? Спросите вы.

Но ничего страшного, способов выделить таблицу до конца тоже несколько. Первый - мышью. Иногда долго, но действенно. Второй это нажав комбинацию клавиш ctrl+shift+End.

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

Переносы строк внутри одной ячейки, добавляемые с помощью сочетания клавиш Alt + Enter - дело весьма частое и привычное. Иногда их делают сами пользователи, чтобы добавить красоты длинному тексту. Иногда такие переносы добавляются автоматически при выгрузке данных из каких-либо рабочих программ (привет 1С, SAP и т.д.) Проблема в том, что на такие таблицы приходится потом не просто любоваться, а с ними работать - и вот тогда эти невидимые символы переноса могут стать проблемой. А могут и не стать - если уметь правильно с ними обращаться.

Давайте-ка мы разберёмся в этом вопросе поподробнее.

Удаление переносов строк заменой

Если нам нужно избавиться от переносов, то первое, что обычно приходит в голову - это классическая техника "найти и заменить". Выделяем текст и затем вызываем окно замены сочетанием клавиш Ctrl + H или через Главная - Найти и выделить - Заменить (Home - Find&Select - Replace) . Одна неувязочка - не очень понятно, как ввести в верхнее поле Найти (Find what) наш невидимый символ переноса строки. Alt + Enter тут, к сожалению, уже не работает, скопировать этот символ непосредственно из ячейки и вставить его сюда тоже не получается.

Поможет сочетание Ctrl + J - именно оно является альтернативой Alt + Enter в диалоговых окнах или полях ввода Excel:

Обратите внимание, что после того, как вы поставите мигающий курсор в верхнее поле и нажмёте Ctrl + J - в самом поле ничего не появится. Не пугайтесь - это нормально, символ-то невидимый :)

В нижнее поле Заменить (Replace with) либо ничего не вводим, либо вводим пробел (если хотим не просто удалить переносы, а заменить их на пробел, чтобы строки не склеились в единое целое). Останется нажать на кнопку Заменить всё (Replace All) и наши переносы исчезнут:

Нюанс: после выполнения замены введённый с помощью Ctrl + J невидимый символ остаётся в поле Найти и может помешать в дальнейшем - не забудьте его удалить, установив курсор в это поле и несколько раз (для надёжности) нажав на клавиши Delete и Backspace .

Удаление переносов строк формулой

Если нужно решить задачу именно формулами, то можно использовать встроенную функцию ПЕЧСИМВ (CLEAN) , которая умеет очищать текст от всех непечатаемых символов, включая и наши злополучные переносы строк:

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

Замена переносов строк формулой

А если хочется не просто удалить, а именно заменить Alt + Enter на, например, пробел, то потребуется уже другая, чуть более сложная конструкция:

Чтобы задать невидимый символ переноса мы используем функцию СИМВОЛ (CHAR) , которая выводит символ по его коду (10). А потом функция ПОДСТАВИТЬ (SUBSTITUTE) ищет в исходных данных наши переносы и заменяет их на любой другой текст, например, на пробел.

Деление на столбцы по переносу строки

Знакомый многим и очень удобный инструмент Текст по столбцам с вкладки Данные (Data - Text to Columns) тоже может замечательно работать с переносами строк и разделить текст из одной ячейки на несколько, разбив его по Alt + Enter . Для этого на втором шаге мастера нужно выбрать вариант пользовательского символа-разделителя Другой (Custom) и использовать уже знакомое нам сочетание клавиш Ctrl + J как альтернативу Alt + Enter :

Если в ваших данных может встречаться несколько переносов строк подряд, то можно их "схлопнуть", включив флажок Считать последовательные разделители одним (Treat consecutive delimiters as one) .

После нажатия на Далее (Next) и прохождения всех трёх шагов мастера мы получим желаемый результат:

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

Деление на строки по Alt+Enter через Power Query

Ещё одной любопытной задачей является деление многострочного текста из каждой ячейки не на столбцы, а на строки:

Вручную такое делать долго, формулами - сложно, макросом - не каждый напишет. А на практике подобная задача встречается чаще, чем хотелось бы. Самым простым и лёгким решением будет использовать для этой задачи возможности надстройки Power Query, которая встроена в Excel начиная с 2016 года, а для более ранних версий 2010-2013 её можно совершенно бесплатно скачать с сайта Microsoft.

Чтобы загрузить исходные данные в Power Query их нужно сначала преобразовать в "умную таблицу" сочетанием клавиш Ctrl + T или кнопкой Форматировать как таблицу на вкладке Главная (Home - Format as Table) . Если по каким-то причинам вы не хотите или не можете использовать "умные таблицы", то можно работать и с "глупыми". В этом случае просто выделите исходный диапазон и дайте ему имя на вкладке Формулы - Диспетчер имен - Создать (Formulas - Name Manager - New) .

После этого на вкладке Данные (если у вас Excel 2016 или новее) или на вкладке Power Query (если у вас Excel 2010-2013) можно жать на кнопку Из таблицы / диапазона (From Table/Range) , чтобы загрузить нашу таблицу в редактор Power Query:

После загрузки выделим столбец с многострочным текстом в ячейках и выберем на Главной вкладке команду Разделить столбец - По разделителю (Home - Split Column - By delimiter) :

Чтобы всё разделилось на строки, а не не столбцы - не забудьте переключить селектор Строки (By rows) в группе расширенных параметров.

Останется только нажать на ОК и получить желаемое:

Готовую таблицу можно выгрузить обратно на лист с помощью команды Закрыть и загрузить - Закрыть и загрузить в. на вкладке Главная (Home - Close&Load - Close&Load to. ) .

Важно отметить, что при использовании Power Query необходимо помнить о том, что при изменении исходных данных результаты автоматически не обновляются, т.к. это не формулы. Для обновления нужно обязательно щёлкнуть правой кнопкой мыши по итоговой таблице на листе и выбрать команду Обновить (Refresh) или нажать кнопку Обновить всё на вкладке Данные (Data - Refresh All) .

Макрос для деления на строки по Alt+Enter

Для полноты картины давайте упомянем решение предыдущей задачи ещё и с помощью макроса. Откройте редактор Visual Basic с помощью одноимённой кнопки на вкладке Разрабочик (Developer) или сочетания клавиш Alt + F11 . В появившемся окне вставьте новый модуль через меню Insert - Module и скопируйте туда нижеприведённый код:

Вернитесь в Excel и выделите ячейки с многострочным текстом, который надо разделить. Затем воспользуйтесь кнопкой Макросы на вкладке Разработчик (Developer - Macros) или сочетанием клавиш Alt + F8 , чтобы запустить созданный макрос, который и проделает за вас всю работу:

Вуаля! Программисты - это, на самом деле, просто очень ленивые люди, которые лучше один раз как следует напрягутся, чтобы потом ничего не делать :)

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