Автоматическое копирование ячеек в excel по условию
День добрый.
Не раз обращался за помощь. к здешним гуру, и практически всегда помогали решать мою задачу!! за это огромное спасибо.
И в очередной раз уперся в тупик.
В общем в чем вопрос.
Есть некая таблица учета финансов. Когда сумма счета закрыта, то счет автоматически приобретает статус "закрыт", если предоплата или недоплата, тогда "в работе".
Можно-ли сделать каким-то образом чтобы при статусе "в работе" значение ячеек А7, В7, J7 и К7 копировались в следующий месяц соответственно к примеру в A20, B20 и С20, D20. Другими словами дата и номер в свои-же столбики, а значения "сальдо на конец" скопировалось в "сальдо на начало" в след. месяц.
И можно-ли чтобы это копирование происходило в следующую пустую строку после А20 (или номер строки указать). Или еще лучше чтоб добавлялась строка после указанной. Это нужно потому что значений (счетов) может быть разное количество в каждом месяце.
Ну а если статус "закрыт", тогда не производить вышеописанные действия.
Надеюсь понятно описал свои фантазии). Буду признателен за помощь!
Заранее спасибо и с праздниками.
День добрый.
Не раз обращался за помощь. к здешним гуру, и практически всегда помогали решать мою задачу!! за это огромное спасибо.
И в очередной раз уперся в тупик.
В общем в чем вопрос.
Есть некая таблица учета финансов. Когда сумма счета закрыта, то счет автоматически приобретает статус "закрыт", если предоплата или недоплата, тогда "в работе".
Можно-ли сделать каким-то образом чтобы при статусе "в работе" значение ячеек А7, В7, J7 и К7 копировались в следующий месяц соответственно к примеру в A20, B20 и С20, D20. Другими словами дата и номер в свои-же столбики, а значения "сальдо на конец" скопировалось в "сальдо на начало" в след. месяц.
И можно-ли чтобы это копирование происходило в следующую пустую строку после А20 (или номер строки указать). Или еще лучше чтоб добавлялась строка после указанной. Это нужно потому что значений (счетов) может быть разное количество в каждом месяце.
Ну а если статус "закрыт", тогда не производить вышеописанные действия.
Надеюсь понятно описал свои фантазии). Буду признателен за помощь!
Заранее спасибо и с праздниками. mefisto
Иногда на то, чтобы придумать некоторые вещи, уходит очень много времени. Но когда их УЖЕ придумали, то постфактум они кажутся очевидными и даже банальными. Из серии "а что, так можно было?".
С самых первых версий в строке состояния внизу окна Microsoft Excel традиционно отображались итоги по выделенным ячейкам:
При желании, можно было даже щёлкнуть по этим итогам правой кнопкой мыши и выбрать в контекстном меню, какие именно функции мы хотим видеть:
И только совсем недавно в последних обновлениях Excel разработчики Microsoft добавили простую, но гениальную фишку - теперь при щелчке мышью по этим итогам они копируются в буфер!
Но что делать тем, у кого пока (или уже?) нет такой версии Excel? Тут могут помочь несложные макросы.
Копирование суммы выделенных ячеек в Буфер с помощью макроса
Откройте на вкладке Разработчик (Developer) редактор Visual Basic или воспользуйтесь для этого сочетанием клавиш Alt + F11 . Вставьте новый пустой модуль через меню Insert - Module и скопируйте туда следующий код:
Логика его работы проста:
- Сначала идёт "защита от дурака" - мы проверяем что именно выделено. Если выделены не ячейки (а, например, диаграмма), то выходим из макроса.
- Затем при помощи команды GetObject мы создаем новый объект данных, где будет храниться впоследствии наша сумма выделенных ячеек. Длинный и непонятный буквенно-цифровой код - это, на самом деле, ссылка на ветку реестра Windows, где лежит библиотека Microsoft Forms 2.0 Object Library, которая умеет создавать такие объекты. Иногда такой трюк ещё называют неявным поздним связыванием. Если его не использовать, то пришлось бы заранее делать в файле ссылку на эту библиотеку через меню Tools - References.
- Сумма выделенных ячеек считается командой WorksheetFunction.Sum(Selection), а затем полученная сумма помещается в буфер обмена командой PutInClipboard
Для удобства использования можно, конечно же, повесить этот макрос на сочетание клавиш с помощью кнопки Макросы на вкладке Разработчик (Developer - Macros) .
А если хочется видеть, что именно скопировалось после выполнения макроса, то можно включить панель Буфер обмена с помощью маленькой стрелки в правом нижнем углу соответствующей группы на Главной (Home) вкладке:
Не только сумма
Если кроме банальной суммы хочется что-то ещё, то можно воспользоваться любой из функций, которую нам предоставляет объект WorksheetFunction:
Например, там есть:
- Sum - сумма
- Average - среднее арифметическое
- Count - количество ячеек с числами
- CountA - количество заполненных ячеек
- CountBlank - количество пустых ячеек
- Min - минимальное значение
- Max - максимальное значение
- Median - медиана (центральное значение)
- . и т.д.
С учетом фильтров и скрытых строк-столбцов
Что если в выделенном диапазоне окажутся скрытые (вручную или фильтром) строки или столбцы? Чтобы не учитывать их в итогах, нужно будет чуть-чуть модифицировать наш код, добавив к объекту Selection свойство SpecialCells(xlCellTypeVisible):
В этом случае подсчет любой функции итога будет применён только к видимым ячейкам.
Если нужна живая формула
Если пофантазировать, то можно придумать сценарии, когда в буфер лучше скопировать не число (константу), а именно живую формулу, которая подсчитывает нужные нам итоги по выделенным ячейкам. В этом случае придётся склеить формулу из фрагментов, добавив к ней дополнительно удаление знаков доллара и замену запятой (которая используется как разделитель адресов нескольких выделенных диапазонов в VBA) на точку с запятой:
Суммирование с дополнительными условиями
Ну и, наконец, для совсем уже маньяков можно написать макрос, который будет суммировать не все выделенные ячейки, а только те, что удовлетворяют заданным условиям. Так, например, будет выглядеть макрос помещающий в Буфер сумму выделенных ячеек, если их значения больше 5 и при этом они залиты любым цветом:
Как легко сообразить, условия можно задать абсолютно любые - вплоть до форматов ячеек - и в любых количествах (в том числе, связывая их между собой логическими операторами or или and). Простор для фантазии большой.
Как известно, для полноценной работы с данными (фильтрации, сортировки, подведения итогов и т.д.) нужен непрерывный список, т.е. таблица без разрывов (пустых строк и ячеек - по возможности). На практике же часто мы имеем как раз таблицы с пропущенными пустыми ячейками - например после копирования результатов сводных таблиц или выгрузок в Excel из внешних программ. Таким образом, возникает необходимость заполнить пустые ячейки таблицы значениями из верхних ячеек, то бишь.
из | сделать |
В общем случае, может возникнуть необходимость делать такое заполнение не только вниз, но и вверх, вправо и т.д. Давайте рассмотрим несколько способов реализовать такое.
Способ 1. Без макросов
Выделяем диапазон ячеек в первом столбце, который надо заполнить (в нашем примере, это A1:A12).
Нажимаем клавишу F5 и затем кнопку Выделить (Special) и в появившемся окне выбираем Выделить пустые ячейки (Blanks) :
Не снимая выделения, вводим в первую ячейку знак "равно" и щелкаем по предыдущей ячейке или жмём стрелку вверх (т.е. создаем ссылку на предыдущую ячейку, другими словами):
И, наконец, чтобы ввести эту формулу сразу во все выделенные (пустые) ячейки нажимаем Ctrl + Enter вместо обычного Enter . И все! Просто и красиво.
В качестве завершающего мазка я советовал бы заменить все созданные формулы на значения, ибо при сортировке или добавлении/удалении строк корректность формул может быть нарушена. Выделите все ячейки в первом столбце, скопируйте и тут же вставьте обратно с помощью Специальной вставки (Paste Special) в контекстом меню, выбрав параметр Значения (Values) . Так будет совсем хорошо.
Способ 2. Заполнение пустых ячеек макросом
Если подобную операцию вам приходится делать часто, то имеем смысл сделать для неё отдельный макрос, чтобы не повторять всю вышеперечисленную цепочку действий вручную. Для этого жмём Alt + F11 или кнопку Visual Basic на вкладке Разработчик (Developer) , чтобы открыть редактор VBA, затем вставляем туда новый пустой модуль через меню Insert - Module и копируем или вводим туда вот такой короткий код:
Как легко можно сообразить, этот макрос проходит в цикле по всем выделенным ячейкам и, если они не пустые, заполняет их значениями из предыдущей ячейки.
Для удобства, можно назначить этому макросу сочетание клавиш или даже поместить его в Личную Книгу Макросов (Personal Macro Workbook), чтобы этот макрос был доступен при работе в любом вашем файле Excel.
Способ 3. Power Query
Power Query - это очень мощная бесплатная надстройка для Excel от Microsoft, которая может делать с данными почти всё, что угодно - в том числе, легко может решить и нашу задачу по заполнению пустых ячеек в таблице. У этого способа два основных преимущества:
- Если данных много, то ручной способ с формулами или макросы могут заметно тормозить. Power Query сделает всё гораздо шустрее.
- При изменении исходных данных достаточно будет просто обновить запрос Power Query. В случае использования первых двух способов - всё делать заново.
Для загрузки нашего диапазона с данными в Power Query ему нужно либо дать имя (через вкладку Формулы - Диспетчер имен), либо превратить в "умную" таблицу командой Главная - Форматировать как таблицу (Home - Format as Table ) или сочетанием клавиш Ctrl + T :
После этого на вкладке Данные (Data) нажмем на кнопку Из таблицы / диапазона (From Table/Range) . Если у вас Excel 2010-2013 и Power Query установлена как отдельная надстройка, то вкладка будет называться, соответственно, Power Query.
В открывшемся редакторе запросов выделим столбец (или несколько столбцов, удерживая Ctrl ) и на вкладке Преобразование выберем команду Заполнить - Заполнить вниз (Transform - Fill - Fill Down) :
Вот и всё :) Осталось готовую таблицу выгрузить обратно на лист Excel командой Главная - Закрыть и загрузить - Закрыть и загрузить в. (Home - Close&Load - Close&Load to. )
В дальнейшем, при изменении исходной таблицы, можно просто обновлять запрос правой кнопкой мыши или на вкладке Данные - Обновить всё (Data - Refresh All) .
Привет великим гуру владеющим в совершенстве Excel. В программе не силен, поэтому вопрос глупый: Подскажите как решить вот такую задачу: в файле есть два листа. Первый для внесения данных(дата,фио) и есть столбец со списком для выбора "да" или "нет". Сама трудность: как скопировать значение ячейки "фио" на второй лист на последнюю пустую строку, если в столбце со списком было выбрано "да".
Если можно составить формулу, хотелось бы ее увидеть. Если нет, то через макросы.
Необходимо чтобы копирование происходило автоматически при заполнении данных.
Прикрепил файл, так сказать, образец необходимого. Заранее спасибо
Привет великим гуру владеющим в совершенстве Excel. В программе не силен, поэтому вопрос глупый: Подскажите как решить вот такую задачу: в файле есть два листа. Первый для внесения данных(дата,фио) и есть столбец со списком для выбора "да" или "нет". Сама трудность: как скопировать значение ячейки "фио" на второй лист на последнюю пустую строку, если в столбце со списком было выбрано "да".
Если можно составить формулу, хотелось бы ее увидеть. Если нет, то через макросы.
Необходимо чтобы копирование происходило автоматически при заполнении данных.
Прикрепил файл, так сказать, образец необходимого. Заранее спасибо AR84
и протянул ее. в итоге только первая строка заполнилась, а остальные пустые. Что я сделал не так7
[moder]Используйте для формул кнопочку fx. Поправила.[/moder]
и протянул ее. в итоге только первая строка заполнилась, а остальные пустые. Что я сделал не так7
[moder]Используйте для формул кнопочку fx. Поправила.[/moder] AR84
и протянул ее. в итоге только первая строка заполнилась, а остальные пустые. Что я сделал не так7
[moder]Используйте для формул кнопочку fx. Поправила.[/moder] Автор - AR84
Дата добавления - 11.02.2016 в 12:27
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("D1:D2000"), Target) Is Nothing Then
Dim s&
If Target = "Да" Then
s = Sheets("1").Range("C" & Rows.Count).End(xlUp).Row + 1
Target.Offset(0, -1).Copy Sheets("1").Cells(s, 3)
Target.Offset(0, -2).Copy Sheets("1").Cells(s, 2)
End If
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("D1:D2000"), Target) Is Nothing Then
Dim s&
If Target = "Да" Then
s = Sheets("1").Range("C" & Rows.Count).End(xlUp).Row + 1
Target.Offset(0, -1).Copy Sheets("1").Cells(s, 3)
Target.Offset(0, -2).Copy Sheets("1").Cells(s, 2)
End If
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("D1:D2000"), Target) Is Nothing Then
Dim s&
If Target = "Да" Then
s = Sheets("1").Range("C" & Rows.Count).End(xlUp).Row + 1
Target.Offset(0, -1).Copy Sheets("1").Cells(s, 3)
Target.Offset(0, -2).Copy Sheets("1").Cells(s, 2)
End If
End If
End Sub
Формула массива вводится сочетанием клавиш:
CTRL SHIFT Enter Автор - SLAVICK
Дата добавления - 11.02.2016 в 14:22
Тоже формула массива, вводится одновременным нажатием Контрл Шифт Ентер Автор - _Boroda_
Дата добавления - 11.02.2016 в 20:24
П.С. понимаю что вопросы глупые, но без вашей помощи не разберусь
П.С. понимаю что вопросы глупые, но без вашей помощи не разберусь AR84
П.С. понимаю что вопросы глупые, но без вашей помощи не разберусь Автор - AR84
Дата добавления - 12.02.2016 в 09:33
А макрос чё, не подходит, или не смотрел?
PS: Понял, Выбор Да-Нет не разовый. Значит надо каждый раз переписывать данные.
Ну в общим исправил. Мож пригодится.
[vba]
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("D1:D2000"), Target) Is Nothing Then
Sheets("1").Range("B3:D45").ClearContents
Dim s&, i&
s = 3
For i = 3 To Range("D" & Rows.Count).End(xlUp).Row
If Cells(i, 4) = "Да" Then
Sheets("1").Cells(s, 2) = Cells(i, 2)
Sheets("1").Cells(s, 3) = Cells(i, 3)
s = s + 1
End If
Next
End If
End Sub
А макрос чё, не подходит, или не смотрел?
PS: Понял, Выбор Да-Нет не разовый. Значит надо каждый раз переписывать данные.
Ну в общим исправил. Мож пригодится.
[vba]
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("D1:D2000"), Target) Is Nothing Then
Sheets("1").Range("B3:D45").ClearContents
Dim s&, i&
s = 3
For i = 3 To Range("D" & Rows.Count).End(xlUp).Row
If Cells(i, 4) = "Да" Then
Sheets("1").Cells(s, 2) = Cells(i, 2)
Sheets("1").Cells(s, 3) = Cells(i, 3)
s = s + 1
End If
Next
End If
End Sub
PS: Понял, Выбор Да-Нет не разовый. Значит надо каждый раз переписывать данные.
Ну в общим исправил. Мож пригодится.
[vba]
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("D1:D2000"), Target) Is Nothing Then
Sheets("1").Range("B3:D45").ClearContents
Dim s&, i&
s = 3
For i = 3 To Range("D" & Rows.Count).End(xlUp).Row
If Cells(i, 4) = "Да" Then
Sheets("1").Cells(s, 2) = Cells(i, 2)
Sheets("1").Cells(s, 3) = Cells(i, 3)
s = s + 1
End If
Next
End If
End Sub
Эх. вижу что макросом проще написано, но я в них совсем ноль. пытаюсь изучить что делает формула и как ее потом можно усовершенствовать и применить (файлик будет расти, дополняться поля и другие формулы). Спасибо Boroda за подробное объяснение формулы, хотелось бы такую же информацию от Wasilic по поводу его макроса =)
ПС. осилю формулы, перейду к макросам.
Эх. вижу что макросом проще написано, но я в них совсем ноль. пытаюсь изучить что делает формула и как ее потом можно усовершенствовать и применить (файлик будет расти, дополняться поля и другие формулы). Спасибо Boroda за подробное объяснение формулы, хотелось бы такую же информацию от Wasilic по поводу его макроса =)
ПС. осилю формулы, перейду к макросам. AR84
ПС. осилю формулы, перейду к макросам. Автор - AR84
Дата добавления - 12.02.2016 в 11:14
Например, есть таблица покупки фруктов, и теперь вам нужно скопировать записи на новый лист на основе указанных фруктов, как это легко сделать в Excel? Здесь я расскажу о нескольких методах копирования строк на новый лист на основе критериев столбца в Excel.
Копировать строки на новый лист на основе критериев столбца путем фильтрации и копирования
Вы можете фильтровать данные по указанному значению столбца, а затем вручную копировать отфильтрованные строки на новый лист в Excel. Пожалуйста, сделайте следующее:
1. Выберите столбец, на основе которого вы будете копировать строки, в нашем примере выберите столбец Fruit. А затем щелкните Данные > Фильтр.
2. Щелкните стрелку рядом с указанным заголовком столбца, а затем проверьте только указанный фрукт в раскрывающемся списке и щелкните значок OK кнопку.
В нашем примере щелкните стрелку рядом с заголовком столбца Fruit, а затем отметьте Apple только в раскрывающемся списке. См. Снимок экрана ниже:
3. Теперь отфильтровываются только записи указанных фруктов. Скопируйте эти записи.
4. Создайте новый лист, щелкнув значок or на панели вкладок листа.
5. Затем вставьте скопированные записи в новый лист.
И тогда вы увидите, что все записи об Apple скопированы и сохранены на новом листе. См. Снимок экрана ниже:
6. Повторите шаги 2–5, чтобы скопировать другие записи фруктов на новые листы.
Если в таблице много видов фруктов, копирование каждого вида записей фруктов на новые листы будет пустой тратой времени.
Скопируйте строки на новый лист на основе критериев столбца Kutools for Excel
Второй метод поможет вам пакетно копировать строки на новый лист на основе критериев столбца Kutools for Excel Разделить данные утилита.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!
Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel
1. Выберите диапазон, в который вы будете копировать строки на основе критериев столбца, и нажмите кнопку Кутулс Плюс > Разделить данные.
2. В открывшемся диалоговом окне «Разделить данные на несколько листов» вам необходимо:
(1) Проверьте Конкретный столбец вариант, щелкните раскрывающийся список ниже и укажите столбец, из которого вы будете копировать строки;
(2) Щелкните значок Правила поле и укажите Значения столбца из выпадающего списка;
3. Нажмите Ok кнопку.
Затем вы увидите, что каждый вид фруктовых записей копируется на отдельный новый лист новой созданной книги, как показано ниже:
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Демонстрация: копирование строк на новый лист на основе критериев столбца в Excel
Простое копирование и вставка нескольких ячеек / диапазонов одновременно в Excel
Возможно, вы заметили, что Microsoft Excel не поддерживает одновременное копирование нескольких непоследовательных ячеек (находящихся в разных столбцах). Но копирование этих ячеек / выделений одно за другим - пустая трата времени и утомительно! Kutools для Excel Копировать диапазоны Утилита может помочь сделать это легко, как показано на скриншоте ниже. Полнофункциональная бесплатная 30-дневная пробная версия!
Читайте также: