Как переместить ячейки в Excel? Перемещение ячеек в пределах листа. Как переместить строки в Excel? Выборочное перемещение строк по условию

09.05.2019

Решение одним словом: транспонирование (transpose ). Дальше ищущий может гуглить и найти данную статью.

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

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

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

Из спорных преимуществ: сохранится все оформление ячеек, что требуется не всегда. Но главный недостаток способа – довольно трудоемкий процесс. Если строк и столбцов больше 100? Сто раз переносить данные построчно?

Используем формулу

Гораздо более изящное решение.

Функция АДРЕС(номер_строки; номер_столбца) отдает ссылку (адрес) ячейки по 2 числам, где первое — номер строки, второе — номер столбца. Т.е. запись =АДРЕС(1;1) вернет нам ссылку на ячейку А1.

С помощью функций СТРОКА(ячейка) и СТОЛБЕЦ(ячейка) меняем порядок выдачи у функции АДРЕС — не (строка, столбец), а (столбец, строка).

В текущем виде формула =АДРЕС(СТОЛБЕЦ(A1);СТРОКА(A1)) вернет текст $A$1, надо преобразовать результат в ссылку, обернув все выражение в функцию ДВССЫЛ(ссылка_в_виде_текста).

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

В английском Excel:

INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)))

Применив формулу для ячейки А9 (в примере на картинке), растягиваем ее на остальные. Результат:

И сразу можно увидеть 2 небольших минуса этого способа:

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

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

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

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

Выделяем таблицу, выбираем в меню Вставка – Сводная таблица. Указываем, куда вставить новую таблицу (можно на новый лист или куда-нибудь на текущий), график – да/нет. Ок. В настройках меняем местами блоки названия строк и названия столбцов. Результат:

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

Как переместить строку в Excel?

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

В общем же случае, чтобы переместить строку, необходимо её вырезать и вставить уже в нужном месте. Для этого необходимо:

1) выделить перемещаемую строку (подвести курсор мыши к номеру строки и кликнуть на нем левой кнопкой);

2) вырезать выделенную строку любым удобным способом (через главное меню, через контекстное меню, горячими клавишами Ctrl+X)

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

4) вставить вырезанные ячейки.

Здесь следует заметить, что в контекстном меню (по нажатию правой кнопки мыши) доступны два пункта, связанные со вставкой - "Вставить" и "Вставить вырезанные ячейки".

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

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

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

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

Как быстро переместить строки на новый лист Excel?

Одним из таких инструментов является надстройка для Excel на основе Visual Basic for Application.

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

С этой надстройкой Вы сможете:

1. Одним кликом мыши вызывать диалоговое окно макроса прямо из панели инструментов Excel;

2. Задавать значение для поиска;

3. Задавать несколько значений для поиска через символ-разделитель ";" точку с запятой;

4. Учитывать либо не учитывать регистр при поиске заданных значений;

5. Выбирать диапазон ячеек для поиска заданных значений;

6. Выбирать одно из восьми условий для выбранных ячеек:

а) совпадает с искомым значением;

б) не совпадает с искомым значением;

в) содержит искомое значение;

г) не содержит искомое значение;

д) начинается с искомого значения;

е) не начинается с искомого значения;

ж) заканчивается искомым значением;

з) не заканчивается искомым значением.

7. Устанавливать дополнительные ограничения для выбранного диапазона.

А знаете ли Вы, что все листы рабочей книги можно быстро превратить в отдельные файлы ?

Кроме того можно

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

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

Способ 1: процедура копирования

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

После этих действий ненужный элемент будет удален. Таким образом, будет проведена перестановка строк местами.

Способ 2: процедура вставки

Как видим, процедура замены строк местами тем способом, который был описан выше, довольно сложная. На её выполнение потребуется относительно большое количество времени. Полбеды, если нужно поменять местами два ряда, но если требуется поменять местами десяток или более строчек? В этом случае, на помощь придет более простой и быстрый метод вставки.

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

Способ 3: перемещение мышью

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

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

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

Большинство настроек Microsoft Excel задаются по умолчанию, среди них ограничение: одна ячейка – одна строка. Но бывает множество случаев, когда в ячейке нужно сделать несколько строчек. Для этого можно использовать несколько способов.

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

Офисный продукт Excel на многие задачи имеет вариативную линейку решений. Перенос строки в ячейке Excel не исключение: от разрыва вручную до автоматического переноса с заданной формулой и программирования.

Используем комбинацию клавиш

В этом случае используется сочетание клавиш «Alt+Enter ». Пользователю будет удобнее сначала зажать «Alt», затем, не отпуская клавиши нажать «Enter ».

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

Когда требуется написать большой текст можно применить команду «формат ячеек », что позволит уместить текст в одной ячейке Эксель.

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

На верхней панели выделить раздел «выравнивание ».

Поставить галочку против команды «переносить по словам » (графа «отображение»). Нажать кнопку ОК.

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

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

Использование формул

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

символ ();
сцепить ();
подставить ().

Символ

Внутри скобок указывается код – цифровое значение от 1 до 255. Берется код из специальной таблицы, где указано число и соответствующий ему символ переноса. Перенос имеет код – 10. Следовательно, используется формула – «символ(10)».

Разберем на конкретном примере, как работать с формулой «символ(10)». Для начала заполним ячейки, которые впоследствии объединим. 4 столбца первой строки – фамилия, пол, возраст, образование. Вторая – Иванова, жен, 30, высшее.

Выбираем, затем выделяем ячейку , куда осуществим перенос. Ставим курсор в строку формул.

Заполняем формулу (для выбранной ячейки):

A1&A2&СИМВОЛ(10)&B1&B2&СИМВОЛ(10)&C1&C2&СИМВОЛ(10)&D1&D2

Где знак «&» означает сцепление соответствующих ячеек, а символ(10) – перенос строки после каждой сцепленной пары.

После написания формулы нажать клавишу «Enter ». В выделенной области появится результат.

На скриншоте видно, что не задан формат. Используем «формат строки », как было указано выше. После того, как будет отмечена галочка напротив пункта «переносить по словам », ячейка приобретет вид, как на картинке ниже.

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

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

Сцепить

Функция «сцепить()» похожа на предыдущую. Она тоже задается формулой, но в скобках указывается не код, а формула «символ(10)».

Возьмем для примера только первую строку с 4 столбцами.

Выделим область для переноса и направим курсор на строку формул. Запишем:

СЦЕПИТЬ(A1;СИМВОЛ(10);B1;СИМВОЛ(10);C1;СИМВОЛ(10);D1)

Нажмем клавишу «Enter ».

Зададим «переносить по словам ». Получим:

Количество ячеек для сцепления может быть любое.

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

Подставить

Когда ячейка вмещает много слов и нужно их переместить в другое место сразу с переносом, то пользуются формулой «подставить()».

Вносим нужный текст в А4.

Затем левой мышкой выделим А6, запишем в сроке формул:

ПОДСТАВИТЬ(A4;" ";СИМВОЛ(10))

В формулу вставляем адрес ячейки с текстом – А4. После нажатия клавиши «Enter » получим результат.

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

Замена знака переноса на пробел и обратно

Иногда нужно заменить знак переноса на пробел, сделать сплошным текстом столбик слов. Есть несколько способов это осуществить. Рассмотрим два из них:

окно «найти и заменить »;
VBA скрипты .

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

В поле «найти » следует ввести Ctrl+J (сначала зажав клавишу, после набрав букву). При этом поле останется практически пустым (будет лишь едва заметная мигающая точка).

В поле «заменить на » нужно поставить пробел (или несколько), а также любой другой знак, на который планируется менять перенос.

Программа выделит область файла с искомыми значениями. После чего останется нажать «заменить все ».

Столбики из слов перестроятся в строчки с пробелами.

Используем скрипт VBA

Открыть окно редактора можно сочетанием клавиш Alt+F11 .

В редакторе находим панель «VBAProject » и кликаем на искомый файл. Правой кнопкой вызываем контекстное меню. Выбираем сначала «Insert », затем «Module ».

Появляется окно для вставки кода.

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

Sub ЗаменяемПробелы() For Each cell In Selection cell.Value = Replace (cell.Value, Chr(32), Chr(10)) Next End Sub

Если наоборот :

Sub ЗаменяемПереносы() For Each cell In Selection cell.Value = Replace (cell.Value, Chr(10), Chr(32)) Next End Sub

Где Chr (10) – это код переноса строки, а Chr (32) – код пробела.

Всплывет окно, где нужно нажать кнопку «НЕТ ».

Далее необходимо сохранить документ с поддержкой нового макроса.

Чтобы закрыть редактор, нажимаем «Alt+Q ».

Главный минус этого способа – требуется базовое знание языка VBA.

Вариативные решения задачи позволяет пользователю Microsoft Excel выбрать подходящий для себя способ.

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

Простейший способ переместить ячейки с места на место - использовать манипулятор мыши. Для перемещения ячейки на новое место, необходимо выделить нужную ячейку или диапазон, навести курсор мыши на границу выделенного диапазона, чтобы курсор стал наклонной стрелкой с крестиком, как показано на рисунке и при нажатой левой кнопке мыши, перетащить выделенную область на новое место и после этого отпустить кнопку мыши. Если при наведении курсора мыши на границу выделенного диапазона он не превращается в наклонную стрелку с крестиком и делает невозможным перетаскивание ячеек, значит, отключена специальная настройка и ее необходимо включить, щелкнув в верхнем левом углу программы по кнопке «Office» и перейдя в Параметры Excel/Дополнительно. В группе «Параметры правки» необходимо установить флажок возле пункта «Разрешить маркеры заполнения и перетаскивание ячеек».

Вырезать и вставить

Для переноса ячеек наряду с их перемещением мышью используется вырезание с последующей вставкой. Для того чтобы вырезать/вставить ячейку или диапазон ячеек, необходимо последовательно выбрать команду «Вырезать», переместить курсор на новое место и выбрать команду «Вставить». Для этого можно использовать контекстное меню (меню, которое появляется при нажатии правой кнопки мыши в области выделенного участка), группу «Буфер обмена» на вкладке «Главная» или горячие клавиши (сочетание клавиш на клавиатуре, за которыми закреплена та или иная команда).

Сочетание клавиш для вырезания - Ctrl+X или Shift+Delete. Сочетание клавиш для вставки - Ctrl+V или Shift+Insert.

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

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

Все эти и аналогичные задачи легко решаются при помощи надстройки для Excel.

Надстройка для быстрого перемещения ячеек

Для быстрого переноса ячеек с одного места в другое создана надстройка для Excel, а кнопка, запускающая эту надстройку выводится на отдельную вкладку ленты Excel либо на отдельную панель инструментов (в зависимости от версии Excel). Нажатие кнопки вызывает диалоговое окно, в котором пользователь может выбрать нужные ему условия, значения, диапазоны, направление перемещения и так далее.Перемещение производится автоматически при помощи макроса . В вышеприведенном примере были применены следующие условия: найти все ячейки с числовыми значениями, содержащими «3100» и переместить их на 1 столбец влево и на 1 строку вверх.

видео по работе с надстройкой