Эксель переместить ячейки по условию
Обновлено: 02.07.2024
Разложить табличные данные в Excel на составляющие части и разнести их по разным листам рабочей книги можно при помощи перемещения строк. Проблемы начинаются при попытке перенести несколько строк одновременно, но обо всем попорядку.
Как переместить строку в Excel?
Для перемещения строки в Excel, можно подвести курсор мыши к границе маркера выделения до появления маленьких стрелок в четыре разные стороны, нажать левую кнопку мыши и удерживая её переместить строку вверх либо вниз. В этом случае содержимое конечных ячеек будет заменено. Осуществить такое перемещение можно только в пределах активного листа.
В общем же случае, чтобы переместить строку, необходимо её вырезать и вставить уже в нужном месте. Для этого необходимо:
1) выделить перемещаемую строку (подвести курсор мыши к номеру строки и кликнуть на нем левой кнопкой);
2) вырезать выделенную строку любым удобным способом (через главное меню, через контекстное меню, горячими клавишами Ctrl+X)
3) на нужном листе выделить строку, либо самую левую ячейку строки, в которую будет произведена вставка;
Здесь следует заметить, что в контекстном меню (по нажатию правой кнопки мыши) доступны два пункта, связанные со вставкой - "Вставить" и "Вставить вырезанные ячейки".
При выборе первого из этих пунктов вырезанная строка будет помещена поверх строки, выбранной для вставки, а при выборе пункта второго, строка будет помещена между строк, то есть без потери каких-либо данных.
Как быстро переместить строки на новый лист Excel?
Одним из таких инструментов является надстройка для Excel на основе Visual Basic for Application.
При использовании надстройки, однообразный ручной труд заменяется программной обработкой данных. Поиском нужных строк, проверкой на соответствие заданным условиям и перемещением строк на новый лист занимается уже сама вычислительная техника, что быстрее и удобнее.
С этой надстройкой Вы сможете:
1. Одним кликом мыши вызывать диалоговое окно макроса прямо из панели инструментов Excel;
2. Задавать значение для поиска;
3. Задавать несколько значений для поиска через символ-разделитель ";" точку с запятой;
4. Учитывать либо не учитывать регистр при поиске заданных значений;
5. Выбирать диапазон ячеек для поиска заданных значений;
6. Выбирать одно из восьми условий для выбранных ячеек:
а) совпадает с искомым значением;
б) не совпадает с искомым значением;
в) содержит искомое значение;
г) не содержит искомое значение;
д) начинается с искомого значения;
е) не начинается с искомого значения;
ж) заканчивается искомым значением;
з) не заканчивается искомым значением.
7. Устанавливать дополнительные ограничения для выбранного диапазона.
А знаете ли Вы, что все листы рабочей книги можно быстро превратить в отдельные файлы?
Данная команда позволяет удалить, переместить или скопировать строки/столбцы по различным условиям:
Операция - выбор действия над строками/столбцами. Возможно Удаление, Перемещение и Копирование. Перемещение и Копирование возможно на новый лист или в указанный диапазон того же листа.
Удалить/Переместить/Скопировать - в данном разделе можно выбрать над чем производить Удаление/Копирование/Перемещение: над строками или столбцами, а так же выбрать диапазон для выполнения действий.
Строки - действия будут произведены со строками
Столбцы - действия будут произведены со столбцами
На всем листе - действия над строками/столбцами будут произведены на всем листе
В диапазоне - действия над строками/столбцами будут произведены только внутри указанного диапазона. На картинке выше указан диапазон Лист1!$A$1:$E$25. Это значит, что если выбрано Удаление Строк, то строки будут удалены только в этом диапазоне. Даже если строка 27 листа будет удовлетворять условиям, она не будет удалена, т.к. выходит за пределены указанного диапазона.
Переместить - указывается место назначения для копируемых или перемещаемых строк/столбцов.
Наименование данного раздела меняется в зависимости от выбранного действия и принимает три значения: Переместить, Скопировать и Не доступно. Не доступно появляется при выборе действия Удалить, т.к. в данном случае определение диапазона для вывода результата не требуется.
Переместить строки - указываются критерии для отбора строк/столбцов к удалению/перемещению/копированию.
- Все пустые - будут удалены все пустые строки/столбцы в указанном диапазоне.
- Если значение ячейки: Ниже в примерах применения данных параметров описаны операции по удалению строк, но все это применимо для любой выбранной операции.
- Равно - будут удалены те строки в ячейках которых есть значение, равное указанному. В критериях можно применять символы подстановки(звездочка( * ), вопр.знак( ? ) ). Например на картинке указан критерий: "*С.Петербург*". Это означает, что если в ячейке будет записано "Произведено в г. С.Петербург 12.03.2008", то данная строка будет удалена. Если же указать "С?Петербург", то будут удалены строки, значение в которых равно либо "С.Петербург", либо "С-Петербург", либо "С Петербург" и т.п. Если же указать "С.Петербург", то будут удалены только те строки, значение которых в точности совпадает с "С.Петербург".
- Не равно - будут удалены те строки в ячейках которых нет значения, равного указанному. Все работает так же, как и при выборе Равно, но в обратном направлении. Т.е. если указать "С.Петербург", то будут удалены все строки, значение которых не равно "С.Петербург".
- Учитывать регистр - при отборе значений будет учитываться регистр критерия или нет. Если не установлено, то при указании равно "С.Петербург" будут удалены строки равные и значению "С.Петербург" и значению "с.петербург".
- В поле ниже указывается номер столбца, в котором просматривать эти критерии. Например, если в этом поле указать 2 и На всем листе, то критерии будут просматриваться во втором столбце листа - $B . Если указать 2 и диапазон $B$1:$E$25, то критерии будут просматриваться во втором столбце указанного диапазона, т.е. в третьем столбце листа - $C . Если данное поле оставить пустым, то значения будут просматриваться во всех столбцах диапазона.
Закрыть форму после выполнения операции - по умолчанию включен. Если снять, то после Удаления/Перемещения/Копирования строк/столбцов форма не закроется и можно будет указать следующий критерий.
Функция СМЕЩ часто пугает неискушенного пользователя наличием аж пяти аргументов и непонятным назначением. Но в 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.
Возможность использования значений с других листов Excel позволяет получать информацию в упрощенном виде. Другими словами, Вы можете на одном листе написать много значений, а вычисления по данным значениям проводить на другом листе.
Как лично я использую данную опцию в работе? Считаю свою зарплату :) В конце каждого месяца приходит расчетный лист, я делаю ссылку на него и вывожу все необходимые данные на один общий лист с подсчетами.
Так что пользуйтесь на здоровье в любых целях! Главное, чтобы Вам это пригодилось! А лист с зарплатой показывать не буду :) Но поверьте он есть!
Разберем простой пример.
В прошлой статье по графикам в Excel я показал таблицу. Чтобы не придумывать новые значения, просто воспользуемся ей. Она расположена на листе 4 моего документа.
1. Создадим новый лист (Лист5 в моем случае). Кто не знает как это делать - нажать на плюс возле последнего листа в вашем документе. Для удобства напишем какие данные будем выводить:
2. Чтобы получить значение заданной ячейки из листа 4, нужно выбрать любую ячейку, поставить знак " = ", внизу нажать на лист4 и выбрать нужную ячейку.
Я решил немного усложнить задачу и не просто выбрать одну ячейку, а найти сумму значений нескольких ячеек. Для этого необходимо в пустой ячейке начать писать " =СУММ( " и уже после этого перейти на лист4 :
3. Попадаем на лист 4, находим нужный массив чисел и выделяем его. Вверху автоматически формируется формула:
Разберем формулу для тех кто не понял:
- Знак равно означает начало функции. Так программа понимает, что необходимо будет выполнить какое-то действие.
- Слово " СУММ " означает, что необходимо будет выполнить сложение для выражения, указанного в круглых скобках.
- Слово " Лист4! " означает, что данные будут браться именно с Листа 4, ведь на других листах есть ячейки с такими же названиями.
- Слова " C21:I21 " - это диапазон значений, то есть программа начнет с первой указанной ячейки и будет повторять действие до последней указанной ячейки.
Нажимаем " Enter " и программа переносит нас к результату:
Теперь мы знаем сумму данных за указанный период (1941г). Аналогичным образом я сделал для 1944 года:
Подскажите пожалуйста, как выводить значение ячейки в другую ячейку при условии появления первого значения в соседнем столбце той же строки?
Заранее благодарен за помощь.
__________________
Помощь в написании контрольных, курсовых и дипломных работ здесь![]()
В одном столбце таблицы БД заменить все пустые ячейки на содержимое ячеек в другом столбце из той же строки
Добрый день. Есть таблица .dbf и надо в одном столбце заменить все пустые ячейки на содержимое.![]()
Как узнать значение первой ячейки в строке (QTableView) при нажатии на любую ячейку в строке?
как выводить значение ячейки в другую ячейку при условии появления первого значения в соседнем столбце той же строки?
Сразу извинюсь за корявость речи. Знатоки, подскажите, как узнать значение первой ячейки в строке.Фотофиниш не в мою пользу :D
Спасибо за совет, но в моем случае перед таблицей есть другие данные. Как сделать, чтобы проверялись данные столбца ниже определенного номера строки? Как сделать, чтобы проверялись данные столбца ниже определенного номера строки? Может быть вы тогда опишите задачу целиком и четко, чтобы сразу было понятно что вам требуется?AleksSid, 1:1 :D
Это касается расчета погашения кредита с отсрочкой платежа. нужно найти и вывести в другую ячейку ту дату, на которую приходится первый платеж по кредиту. смотрите пожалуйста вложенный файл с примером.
Кстати, еще хотелось бы найти формулу для расчета суммы кредита при известном аннуитетном платеже.
Спасибо.Что-то я расслабился, даже вложение еще не открывал. 1:2 :( :jokingly:
Спасибо, но эта форма расчета не подходит в моем случае, так как она не позволяет учесть отсрочку погашения по кредиту. Я делаю расчет в форме, скаченной с иностранного сайта, которая позволяет отсрочить возврат кредита, начисляю проценты в месяцы отсрочки и дает сделать дополнительные (досрочные) выплаты наряду с аннуитетными платежами и имеет переключатель с двумя схемами расчета. Первый называется амортизированный, где сам пересчитывает новый аннуитетный платеж после каждой доп. выплаты (как и в Вашей форме), а при второй схеме (называется фиксированная сумма) сама не пересчитывает сумму аннуитетного платежа, а надо самому вручную подгонять нужную фикс. сумму под ноль в конце срока погашения. У меня все получается нормально в обоих схемах, но хотелось бы поставить формулу для расчета фиксированной суммы, привязанной к таблице (к остатку долга, от которой и получается данная фиксир. сумма) чтобы программа сама высчитывала сумму платежа при изменении суммы кредита, а не вручную найти нужную фиксир. сумму аннуитета каждый раз. Надеюсь, смог изложить суть проблемы.
Еще хотелось бы выводить вне таблицы (для расчетов периода отсрочки в месяцах и количества выплат, привязанных к таблице расчетов) ту дату из столбца с датами графика погашения, на которую приходится первая выплата по погашению.
Спасибо.Читайте также:
