Эксель переместить ячейки по условию
Данные, расположенные в ячейке или в диапазоне ячеек, можно перемещать с одного места рабочего листа в другое, используя как прямое перетаскивание мышью, так и кнопки на вкладках ленты, контекстное меню, а также процедуры (макросы) – программы, написанные на языке Visual Basic for Application, сокращенно VBA.
Перемещение с помощью мыши
Простейший способ переместить ячейки с места на место – использовать манипулятор мыши. Для перемещения ячейки на новое место, необходимо выделить нужную ячейку или диапазон, навести курсор мыши на границу выделенного диапазона, чтобы курсор стал наклонной стрелкой с крестиком, как показано на рисунке и при нажатой левой кнопке мыши, перетащить выделенную область на новое место и после этого отпустить кнопку мыши. Если при наведении курсора мыши на границу выделенного диапазона он не превращается в наклонную стрелку с крестиком и делает невозможным перетаскивание ячеек, значит, отключена специальная настройка и ее необходимо включить, щелкнув в верхнем левом углу программы по кнопке «Office» и перейдя в Параметры Excel/Дополнительно. В группе «Параметры правки» необходимо установить флажок возле пункта «Разрешить маркеры заполнения и перетаскивание ячеек».
Вырезать и вставить
Для переноса ячеек наряду с их перемещением мышью используется вырезание с последующей вставкой. Для того чтобы вырезать/вставить ячейку или диапазон ячеек, необходимо последовательно выбрать команду «Вырезать», переместить курсор на новое место и выбрать команду «Вставить». Для этого можно использовать контекстное меню (меню, которое появляется при нажатии правой кнопки мыши в области выделенного участка), группу «Буфер обмена» на вкладке «Главная» или горячие клавиши (сочетание клавиш на клавиатуре, за которыми закреплена та или иная команда).
Сочетание клавиш для вырезания – Ctrl+X или Shift+Delete. Сочетание клавиш для вставки – Ctrl+V или Shift+Insert.
Кроме того, ячеек может быть настолько много, что перенос их вручную может превратиться в кошмар. Другой проблемой, которая может возникнуть при переносе ячеек с места на место, может оказаться сам поиск необходимых для перемещения ячеек. Для наглядности рассмотрим такой пример: необходимо разнести по разным столбцам ячейки, в зависимости от их значений.
Все эти и аналогичные задачи легко решаются при помощи надстройки для Excel.
Надстройка для быстрого перемещения ячеек
Для быстрого переноса ячеек с одного места в другое создана надстройка для Excel, а кнопка, запускающая эту надстройку выводится на отдельную вкладку ленты Excel либо на отдельную панель инструментов (в зависимости от версии Excel). Нажатие кнопки вызывает диалоговое окно, в котором пользователь может выбрать нужные ему условия, значения, диапазоны, направление перемещения и так далее.
Перемещение производится автоматически при помощи макроса. В вышеприведенном примере были применены следующие условия: найти все ячейки с числовыми значениями, содержащими "3100" и переместить их на 1 столбец влево и на 1 строку вверх.
1. одним кликом мыши вызывать диалоговое окно макроса прямо из панели инструментов excel;
2. задавать искомое значение для ячеек;
3. задавать диапазон для поиска;
4. выбирать одно из двенадцати условий для значений ячеек;
5. определять направление перемещения для ячеек, удовлетворяющих заданным условиям и значениям:
а) перемещать ячейки на заданное количество строк вверх или вниз;
б) перемещать ячейки на заданное количество столбцов влево или вправо;
Функция СМЕЩ часто пугает неискушенного пользователя наличием аж пяти аргументов и непонятным назначением. Но в Excel часто встречаются задачи, в которых она проявит себя с самой лучшей стороны и станет незаменимым помощником. Например, когда Вам нужно:
1) Сделать зависимый выпадающий список, в который можно добавлять данные;
2) Добиться того, чтобы новые строки сразу попадали в сводную таблицу при обновлении;
3) Отображать на графике только данные за последние 7 дней, причем новые данные вносятся ежедневно;
4) В списке помесячных продаж за год мгновенно подсчитать сумму с января по март, а потом с февраля по апрель, а потом с сентября по декабрь.
5) В новом столбце сослаться на каждую пятую ячейку соседнего столбца
Как видите, задачи интересные и нетривиальные. Если Вы хотите научиться их решать в два счета, то стоит уделить несколько минут изучению функции СМЕЩ.
Синтаксис и алгоритм работы функции
Функция СМЕЩ умеет возвращать ссылку на диапазон заданного размера (высота и ширина), отстоящий от стартовой ячейки на заданное число строк и столбцов.
Функция имеет следующий синтаксис:
Как видите, у неё есть 5 аргументов:
1) " Ссылка ". Обязательный аргумент. Указывает стартовую ячейку, от которой будет происходить смещение. Здесь можно указать ссылку на ячейку или на диапазон ячеек. Во втором случае смещение будет считаться от левой верхней ячейки диапазона;
2) " Смещ_по_строкам ". Обязательный аргумент. Указывает, на сколько строк нужно сместиться от заданной ячейки. Положительные числа означают смещение вниз, отрицательные - вверх, ноль - без смещения;
3) " Смещ_по_столбцам ". Обязательный аргумент. Указывает, на сколько столбцов нужно сместиться от заданной ячейки. Положительные числа означают смещение вправо, отрицательные - влево, ноль - без смещения;
4) " Высота" . Необязательный аргумент. Высота в строках возвращаемого диапазона. Если опустить - высота будет равна высоте диапазона, указанного в аргументе "Ссылка";
5) " Ширина" . Необязательный аргумент. Ширина в строках возвращаемого диапазона. Если опустить - ширина будет равна ширине диапазона, указанного в аргументе "Ссылка".
На рисунке ниже представлен алгоритм работы.
1) В качестве "Ссылки" задана ячейка B2.
2) От нее происходит смещение на 2 строки вниз (так как число 2 положительное). Мы оказываемся в ячейке B4.
3) Из ячейки B4 происходит смещение на 3 столбца вправо (так как число 3 положительное). В итоге мы попадаем в ячейку E4.
4) Принимая ее за верхнюю левую, выделяем диапазон из 3 строк и 4 столбцов. Результат - диапазон E4:H6.
Данная команда позволяет удалить, переместить или скопировать строки/столбцы по различным условиям:
Операция - выбор действия над строками/столбцами. Возможно Удаление, Перемещение и Копирование. Перемещение и Копирование возможно на новый лист или в указанный диапазон того же листа.
Удалить/Переместить/Скопировать - в данном разделе можно выбрать над чем производить Удаление/Копирование/Перемещение: над строками или столбцами, а так же выбрать диапазон для выполнения действий.
Строки - действия будут произведены со строками
Столбцы - действия будут произведены со столбцами
На всем листе - действия над строками/столбцами будут произведены на всем листе
В диапазоне - действия над строками/столбцами будут произведены только внутри указанного диапазона. На картинке выше указан диапазон Лист1!$A$1:$E$25. Это значит, что если выбрано Удаление Строк, то строки будут удалены только в этом диапазоне. Даже если строка 27 листа будет удовлетворять условиям, она не будет удалена, т.к. выходит за пределены указанного диапазона.
Переместить - указывается место назначения для копируемых или перемещаемых строк/столбцов.
Наименование данного раздела меняется в зависимости от выбранного действия и принимает три значения: Переместить, Скопировать и Не доступно. Не доступно появляется при выборе действия Удалить, т.к. в данном случае определение диапазона для вывода результата не требуется.
Переместить строки - указываются критерии для отбора строк/столбцов к удалению/перемещению/копированию.
- Все пустые - будут удалены все пустые строки/столбцы в указанном диапазоне.
- Если значение ячейки: Ниже в примерах применения данных параметров описаны операции по удалению строк, но все это применимо для любой выбранной операции.
- Равно - будут удалены те строки в ячейках которых есть значение, равное указанному. В критериях можно применять символы подстановки(звездочка( * ), вопр.знак( ? ) ). Например на картинке указан критерий: "*С.Петербург*". Это означает, что если в ячейке будет записано "Произведено в г. С.Петербург 12.03.2008", то данная строка будет удалена. Если же указать "С?Петербург", то будут удалены строки, значение в которых равно либо "С.Петербург", либо "С-Петербург", либо "С Петербург" и т.п. Если же указать "С.Петербург", то будут удалены только те строки, значение которых в точности совпадает с "С.Петербург".
- Не равно - будут удалены те строки в ячейках которых нет значения, равного указанному. Все работает так же, как и при выборе Равно, но в обратном направлении. Т.е. если указать "С.Петербург", то будут удалены все строки, значение которых не равно "С.Петербург".
- Учитывать регистр - при отборе значений будет учитываться регистр критерия или нет. Если не установлено, то при указании равно "С.Петербург" будут удалены строки равные и значению "С.Петербург" и значению "с.петербург".
- В поле ниже указывается номер столбца, в котором просматривать эти критерии. Например, если в этом поле указать 2 и На всем листе, то критерии будут просматриваться во втором столбце листа - $B . Если указать 2 и диапазон $B$1:$E$25, то критерии будут просматриваться во втором столбце указанного диапазона, т.е. в третьем столбце листа - $C . Если данное поле оставить пустым, то значения будут просматриваться во всех столбцах диапазона.
Закрыть форму после выполнения операции - по умолчанию включен. Если снять, то после Удаления/Перемещения/Копирования строк/столбцов форма не закроется и можно будет указать следующий критерий.
Видеоинструкции по использованию надстройки MulTEx
Поделитесь своим мнением
Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на ФорумДобрый день!
Не скажу, что совсем не знаю Excel но все таки требуется помощь!
Есть таблица. На первом листе со множеством столбцов и ещё большим количеством строк. Содержание ячеек представлено в виде текстовой информации, дат, цен, текстовой информацией, пусть будет "Ресстр". Данный Реестр каждый день пополняется новыми данными
Как сделать так, чтобы на следующем листе книги отражалась (дублировалась) информация (строка) лишь по нескольким ячейкам из листа №1, при условии что данные в одном из столбцов > 0 То есть, если в Таблице № 1 в столбце "6" какой-либо строчки появляется значение >0, тогда эта строчка с определенным (меньшим) набором ячеек переносится в Таблицу № 2 и встает по порядку. И по мере добавления строк в лист 1 данные автоматически отображались бы в листе 2 при указанном выше условии.
Лучше бы без макросов.
Буду признателен за пошаговую инструкцию, дабы иметь возможность разрабатывать самому вариации применения
файл ниже
За ранее благодарен!Добрый день!
Не скажу, что совсем не знаю Excel но все таки требуется помощь!
Есть таблица. На первом листе со множеством столбцов и ещё большим количеством строк. Содержание ячеек представлено в виде текстовой информации, дат, цен, текстовой информацией, пусть будет "Ресстр". Данный Реестр каждый день пополняется новыми данными
Как сделать так, чтобы на следующем листе книги отражалась (дублировалась) информация (строка) лишь по нескольким ячейкам из листа №1, при условии что данные в одном из столбцов > 0 То есть, если в Таблице № 1 в столбце "6" какой-либо строчки появляется значение >0, тогда эта строчка с определенным (меньшим) набором ячеек переносится в Таблицу № 2 и встает по порядку. И по мере добавления строк в лист 1 данные автоматически отображались бы в листе 2 при указанном выше условии.
Лучше бы без макросов.
Буду признателен за пошаговую инструкцию, дабы иметь возможность разрабатывать самому вариации применения
файл ниже
За ранее благодарен! chulpanРазложить табличные данные в Excel на составляющие части и разнести их по разным листам рабочей книги можно при помощи перемещения строк. Проблемы начинаются при попытке перенести несколько строк одновременно, но обо всем попорядку.
Как переместить строку в Excel?
Для перемещения строки в Excel, можно подвести курсор мыши к границе маркера выделения до появления маленьких стрелок в четыре разные стороны, нажать левую кнопку мыши и удерживая её переместить строку вверх либо вниз. В этом случае содержимое конечных ячеек будет заменено. Осуществить такое перемещение можно только в пределах активного листа.
В общем же случае, чтобы переместить строку, необходимо её вырезать и вставить уже в нужном месте. Для этого необходимо:
1) выделить перемещаемую строку (подвести курсор мыши к номеру строки и кликнуть на нем левой кнопкой);
2) вырезать выделенную строку любым удобным способом (через главное меню, через контекстное меню, горячими клавишами Ctrl+X)
3) на нужном листе выделить строку, либо самую левую ячейку строки, в которую будет произведена вставка;
Здесь следует заметить, что в контекстном меню (по нажатию правой кнопки мыши) доступны два пункта, связанные со вставкой - "Вставить" и "Вставить вырезанные ячейки".
При выборе первого из этих пунктов вырезанная строка будет помещена поверх строки, выбранной для вставки, а при выборе пункта второго, строка будет помещена между строк, то есть без потери каких-либо данных.
Как быстро переместить строки на новый лист Excel?
Одним из таких инструментов является надстройка для Excel на основе Visual Basic for Application.
При использовании надстройки, однообразный ручной труд заменяется программной обработкой данных. Поиском нужных строк, проверкой на соответствие заданным условиям и перемещением строк на новый лист занимается уже сама вычислительная техника, что быстрее и удобнее.
С этой надстройкой Вы сможете:
1. Одним кликом мыши вызывать диалоговое окно макроса прямо из панели инструментов Excel;
2. Задавать значение для поиска;
3. Задавать несколько значений для поиска через символ-разделитель ";" точку с запятой;
4. Учитывать либо не учитывать регистр при поиске заданных значений;
5. Выбирать диапазон ячеек для поиска заданных значений;
6. Выбирать одно из восьми условий для выбранных ячеек:
а) совпадает с искомым значением;
б) не совпадает с искомым значением;
в) содержит искомое значение;
г) не содержит искомое значение;
д) начинается с искомого значения;
е) не начинается с искомого значения;
ж) заканчивается искомым значением;
з) не заканчивается искомым значением.
7. Устанавливать дополнительные ограничения для выбранного диапазона.
А знаете ли Вы, что все листы рабочей книги можно быстро превратить в отдельные файлы?
Читайте также: