Excel макрос для заполнения ячеек на другом листе
Всем привет и хорошего дня! Я сюда пришёл, конечно же, за помощью. У меня было задание: давался сводный лист с компаниями в сводном листе были заголовки и одна огромная таблица, компаний в ней было 44. Я разбил таблицу на отдельные листы, т.е. данные одной компании находились на одном листе, другой компании на другом листе и т.д. Задание заключалось в автоматическом заполнении сводного листа по кнопке, т.е. я ввожу данные в лист компании №13, например, и в сводном листе в таблице заполнялись нужные ячейки, там где были данные о компании №13, всё это делалось по нажатии кнопки(но с заданием я не совсем справился, т.к. макрос выполнялся через Ctrl+q). Теперь, мне нужно чтобы всё также заполнялось, но автоматически, т.е. достаточно просто написать в листе №13 и всё, и данные уже были в сводном. Возможно так сделать? Вот код по предыдущему заданию:
[vba]
Sub PerenosTarget()
Dim TargetRow As Long
Dim TargetColumn As Integer
Dim FoundNomer As Range
Dim FoundOkno As Range
Dim FoundOknoRow As Long
TargetRow = ActiveCell.Row
TargetColumn = ActiveCell.Column
With Sheets("Ñâîä") '
Set FoundNomer = .Columns(1).Find("8602/" & ActiveSheet.Name, , xlValues, xlWhole)
Set FoundOkno = .Columns(1).Find(What:=Cells(ActiveCell.Row, 1), After:=FoundNomer, LookIn:=xlValues, _
LookAt:=xlWhole, SearchDirection:=xlPrevious)
FoundOknoRow = FoundOkno.Row
.Cells(FoundOknoRow, TargetColumn) = ActiveCell
End With
End Sub
код записан в стандартном модуле.
Всем привет и хорошего дня! Я сюда пришёл, конечно же, за помощью. У меня было задание: давался сводный лист с компаниями в сводном листе были заголовки и одна огромная таблица, компаний в ней было 44. Я разбил таблицу на отдельные листы, т.е. данные одной компании находились на одном листе, другой компании на другом листе и т.д. Задание заключалось в автоматическом заполнении сводного листа по кнопке, т.е. я ввожу данные в лист компании №13, например, и в сводном листе в таблице заполнялись нужные ячейки, там где были данные о компании №13, всё это делалось по нажатии кнопки(но с заданием я не совсем справился, т.к. макрос выполнялся через Ctrl+q). Теперь, мне нужно чтобы всё также заполнялось, но автоматически, т.е. достаточно просто написать в листе №13 и всё, и данные уже были в сводном. Возможно так сделать? Вот код по предыдущему заданию:
[vba]
Sub PerenosTarget()
Dim TargetRow As Long
Dim TargetColumn As Integer
Dim FoundNomer As Range
Dim FoundOkno As Range
Dim FoundOknoRow As Long
TargetRow = ActiveCell.Row
TargetColumn = ActiveCell.Column
With Sheets("Ñâîä") '
Set FoundNomer = .Columns(1).Find("8602/" & ActiveSheet.Name, , xlValues, xlWhole)
Set FoundOkno = .Columns(1).Find(What:=Cells(ActiveCell.Row, 1), After:=FoundNomer, LookIn:=xlValues, _
LookAt:=xlWhole, SearchDirection:=xlPrevious)
FoundOknoRow = FoundOkno.Row
.Cells(FoundOknoRow, TargetColumn) = ActiveCell
End With
End Sub
код записан в стандартном модуле. dmgust2704
Sub PerenosTarget()
Dim TargetRow As Long
Dim TargetColumn As Integer
Dim FoundNomer As Range
Dim FoundOkno As Range
Dim FoundOknoRow As Long
TargetRow = ActiveCell.Row
TargetColumn = ActiveCell.Column
With Sheets("Ñâîä") '
Set FoundNomer = .Columns(1).Find("8602/" & ActiveSheet.Name, , xlValues, xlWhole)
Set FoundOkno = .Columns(1).Find(What:=Cells(ActiveCell.Row, 1), After:=FoundNomer, LookIn:=xlValues, _
LookAt:=xlWhole, SearchDirection:=xlPrevious)
FoundOknoRow = FoundOkno.Row
.Cells(FoundOknoRow, TargetColumn) = ActiveCell
End With
End Sub
код записан в стандартном модуле. Автор - dmgust2704
Дата добавления - 17.06.2015 в 08:12
Как известно, для полноценной работы с данными (фильтрации, сортировки, подведения итогов и т.д.) нужен непрерывный список, т.е. таблица без разрывов (пустых строк и ячеек - по возможности). На практике же часто мы имеем как раз таблицы с пропущенными пустыми ячейками - например после копирования результатов сводных таблиц или выгрузок в 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. Другим поводом для использования макросов в вашей работе может стать необходимость добавить в Microsoft Excel недостающие, но нужные вам функции. Например функцию сборки данных с разных листов на один итоговый лист, разнесения данных обратно, вывод суммы прописью и т.д.
Макрос - это запрограммированная последовательность действий (программа, процедура), записанная на языке программирования Visual Basic for Applications (VBA). Мы можем запускать макрос сколько угодно раз, заставляя Excel выполнять последовательность любых нужных нам действий, которые нам не хочется выполнять вручную.
Способ 1. Создание макросов в редакторе Visual Basic
Для ввода команд и формирования программы, т.е. создания макроса необходимо открыть специальное окно - редактор программ на VBA, встроенный в Microsoft Excel.
- В старых версиях (Excel 2003 и старше) для этого идем в меню Сервис - Макрос - Редактор Visual Basic(Toos - Macro - Visual Basic Editor).
- В новых версиях (Excel 2007 и новее) для этого нужно сначала отобразить вкладку Разработчик (Developer) . Выбираем Файл - Параметры - Настройка ленты (File - Options - Customize Ribbon) и включаем в правой части окна флажок Разработчик (Developer) . Теперь на появившейся вкладке нам будут доступны основные инструменты для работы с макросами, в том числе и нужная нам кнопка Редактор Visual Basic(Visual Basic Editor)
:
К сожалению, интерфейс редактора VBA и файлы справки не переводятся компанией Microsoft на русский язык, поэтому с английскими командами в меню и окнах придется смириться:
Макросы (т.е. наборы команд на языке VBA) хранятся в программных модулях. В любой книге Excel мы можем создать любое количество программных модулей и разместить там наши макросы. Один модуль может содержать любое количество макросов. Доступ ко всем модулям осуществляется с помощью окна Project Explorer в левом верхнем углу редактора (если его не видно, нажмите CTRL+R). Программные модули бывают нескольких типов для разных ситуаций:
-
Обычные модули - используются в большинстве случаев, когда речь идет о макросах. Для создания такого модуля выберите в меню Insert - Module. В появившееся окно нового пустого модуля можно вводить команды на VBA, набирая их с клавиатуры или копируя их из другого модуля, с этого сайта или еще откуда нибудь:
Обычный макрос, введенный в стандартный модуль выглядит примерно так:
Давайте разберем приведенный выше в качестве примера макрос Zamena:
С ходу ясно, что вот так сразу, без предварительной подготовки и опыта в программировании вообще и на VBA в частности, сложновато будет сообразить какие именно команды и как надо вводить, чтобы макрос автоматически выполнял все действия, которые, например, Вы делаете для создания еженедельного отчета для руководства компании. Поэтому мы переходим ко второму способу создания макросов, а именно.
Способ 2. Запись макросов макрорекордером
Макрорекордер - это небольшая программа, встроенная в Excel, которая переводит любое действие пользователя на язык программирования VBA и записывает получившуюся команду в программный модуль. Если мы включим макрорекордер на запись, а затем начнем создавать свой еженедельный отчет, то макрорекордер начнет записывать команды вслед за каждым нашим действием и, в итоге, мы получим макрос создающий отчет как если бы он был написан программистом. Такой способ создания макросов не требует знаний пользователя о программировании и VBA и позволяет пользоваться макросами как неким аналогом видеозаписи: включил запись, выполнил операци, перемотал пленку и запустил выполнение тех же действий еще раз. Естественно у такого способа есть свои плюсы и минусы:
- Макрорекордер записывает только те действия, которые выполняются в пределах окна Microsoft Excel. Как только вы закрываете Excel или переключаетесь в другую программу - запись останавливается.
- Макрорекордер может записать только те действия, для которых есть команды меню или кнопки в Excel. Программист же может написать макрос, который делает то, что Excel никогда не умел (сортировку по цвету, например или что-то подобное).
- Если во время записи макроса макрорекордером вы ошиблись - ошибка будет записана. Однако смело можете давить на кнопку отмены последнего действия (Undo) - во время записи макроса макрорекордером она не просто возрвращает Вас в предыдущее состояние, но и стирает последнюю записанную команду на VBA.
Чтобы включить запись необходимо:
- в Excel 2003 и старше - выбрать в меню Сервис - Макрос - Начать запись(Tools - Macro - Record New Macro)
- в Excel 2007 и новее - нажать кнопку Запись макроса (Record macro) на вкладке Разработчик (Developer)
Затем необходимо настроить параметры записываемого макроса в окне Запись макроса:
- Имя макроса - подойдет любое имя на русском или английском языке. Имя должно начинаться с буквы и не содержать пробелов и знаков препинания.
- Сочетание клавиш - будет потом использоваться для быстрого запуска макроса. Если забудете сочетание или вообще его не введете, то макрос можно будет запустить через меню Сервис - Макрос - Макросы - Выполнить(Tools - Macro - Macros - Run) или с помощью кнопки Макросы (Macros) на вкладке Разработчик (Developer) или нажав ALT+F8.
- Сохранить в. - здесь задается место, куда будет сохранен текст макроса, т.е. набор команд на VBA из которых и состоит макрос.:
- Эта книга - макрос сохраняется в модуль текущей книги и, как следствие, будет выполнятся только пока эта книга открыта в Excel
- Новая книга - макрос сохраняется в шаблон, на основе которого создается любая новая пустая книга в Excel, т.е. макрос будет содержаться во всех новых книгах, создаваемых на данном компьютере начиная с текущего момента
- Личная книга макросов - это специальная книга Excel с именем Personal.xls, которая используется как хранилище макросов. Все макросы из Personal.xls загружаются в память при старте Excel и могут быть запущены в любой момент и в любой книге.
После включения записи и выполнения действий, которые необходимо записать, запись можно остановить командой Остановить запись (Stop Recording) .
Запуск и редактирование макросов
Управление всеми доступными макросами производится в окне, которое можно открыть с помощью кнопки Макросы (Macros) на вкладке Разработчик (Developer) или - в старых версиях Excel - через меню Сервис - Макрос - Макросы (Tools - Macro - Macros) :
- Любой выделенный в списке макрос можно запустить кнопкой Выполнить(Run) .
- Кнопка Параметры(Options) позволяет посмотреть и отредактировать сочетание клавиш для быстрого запуска макроса.
- Кнопка Изменить(Edit) открывает редактор Visual Basic (см. выше) и позволяет просмотреть и отредактировать текст макроса на VBA.
Создание кнопки для запуска макросов
Чтобы не запоминать сочетание клавиш для запуска макроса, лучше создать кнопку и назначить ей нужный макрос. Кнопка может быть нескольких типов:
Кнопка на панели инструментов в Excel 2003 и старше
Откройте меню Сервис - Настройка (Tools - Customize) и перейдите на вкладку Команды (Commands) . В категории Макросы легко найти веселый желтый "колобок" - Настраиваемую кнопку (Custom button) :
Перетащите ее к себе на панель инструментов и затем щелкните по ней правой кнопкой мыши. В контекстом меню можно назначить кнопке макрос, выбрать другой значок и имя:
Кнопка на панели быстрого доступа в Excel 2007 и новее
Щелкните правой кнопкой мыши по панели быстрого доступа в левом верхнем углу окна Excel и выберите команду Настройка панели быстрого доступа (Customise Quick Access Toolbar) :
Затем в открывшемся окне выберите категорию Макросы и при помощи кнопки Добавить (Add) перенесите выбранный макрос в правую половину окна, т.е. на панель быстрого доступа:
Кнопка на листе
Этот способ подходит для любой версии Excel. Мы добавим кнопку запуска макроса прямо на рабочий лист, как графический объект. Для этого:
- В Excel 2003 и старше - откройте панель инструментов Формы через меню Вид - Панели инструментов - Формы (View - Toolbars - Forms)
- В Excel 2007 и новее - откройте выпадающий список Вставить (Insert) на вкладке Разработчик (Developer)
Выберите объект Кнопка (Button) :
Затем нарисуйте кнопку на листе, удерживая левую кнопку мыши. Автоматически появится окно, где нужно выбрать макрос, который должен запускаться при щелчке по нарисованной кнопке.
Создание пользовательских функций на VBA
Создание пользовательских функций или, как их иногда еще называют, UDF-функций (User Defined Functions) принципиально не отличается от создания макроса в обычном программном модуле. Разница только в том, что макрос выполняет последовательность действий с объектами книги (ячейками, формулами и значениями, листами, диаграммами и т.д.), а пользовательская функция - только с теми значениями, которые мы передадим ей как аргументы (исходные данные для расчета).
Чтобы создать пользовательскую функцию для расчета, например, налога на добавленную стоимость (НДС) откроем редактор VBA, добавим новый модуль через меню Insert - Module и введем туда текст нашей функции:
Обратите внимание, что в отличие от макросов функции имеют заголовок Function вместо Sub и непустой список аргументов (в нашем случае это Summa). После ввода кода наша функция становится доступна в обычном окне Мастера функций (Вставка - Функция) в категории Определенные пользователем (User Defined) :
После выбора функции выделяем ячейки с аргументами (с суммой, для которой надо посчитать НДС) как в случае с обычной функцией:
доброго времени суток, господа! на листе 1 имеются ячейки для заполнения(анкета). требуется при нажатии кнопки, чтобы данные из этих ячеек переносились на лист 2 в строку (чтото типа базы), при новом заполнении данные заносятся в следующую строку на листе 2. не силен в VB, честно говоря. заранее благодарю!
Копирование некоторых ячеек с одного листа в первую пустую строку на другом листе
Добрый день! У меня такая задача, есть лист Anketa и лист MB. Лист Anketa это анкета для.Копирование значений ячеек после поиска по массиву на другом листе книги
Добрый день! Помогите, пожалуйста, разобраться с задачей. Необходимо автоматически заполнять лист.Автообновление содержимого ячеек на другом листе
Знающие люди, помогите. Как можно организовать автообновление ячеек на втором листе, при изменении.Найти пустую строку на листе и записать туда данные
Создаю форму для ввода иностранных слов. Надо обговорить: очень мало понимаю в этом, поэтому не.- нужно ли учитывать форматирование. Форматирование - это заливка, границы у ячеек и может быть что-то ещё;
- нужно ли учитывать скрытые строки. Строки могут быть скрыты, например, при использовании "Автофильтра";
- нужно учитывать: есть ли таблица (появилась в "Excel 2007") и список (был в "Excel 2003") на листе;
- нужно указать столбец, по которому нужно определять последнюю строку. Может быть ситуация, что последняя строка определяется не по конкретному столбцу.
думаю формат здесь не нужен.
примерчик хотя бы . а так. лень что - то самому придумавать. зная что ето потом опять нужно под реальный пример переделавать. как то влом
Извиняюсь за отсутствие. Вот файл, чтобы не быть голословным.
Спасибо Igor_Tr, а как работать не со строкой, а с ячейками, находящимися не в одной строке (помечено цветом), тобишь как под мой заточить не пойму.
и еще при копировании из ячейки С11 в I3 что нужноо прописать , чтобы копировалось значение.Попробуем, только что такое "*заточить"? Что такое "чумаху" - уже знаю.
Добавлено через 2 минуты
Сказка. Скиньте в 2003 (*.xls)Сколько будет записей одновременно?
Что такое "Итоговая оценка" - 3.8? Если самооценка - я бы лучше сразу написал словами "выше неба. ".
Ничиго сложного. Заганяете в массив - и на лист. Сделаю, но сейчас должны друзья подойти, немного позже.
И еще - на листе 1 диапазон A1 : D11 - он плавающий или стабильный?
Пока кидайте ответы, а я разрулюсь и сделаю.ok.
1. итоговая оценка 3,8 это ср. арифм. оценок столбца С на листе 1. это значение нужно перенести на лист 2 ячейку I3, просто как цыфру.
2. на листе 1 диапазон A1 : D11 стабильный, т.е. заполняются требуемые ячейки, нажимаем кнопку и данные заносятся на лист2, в идеале заполненные ячейки листа 1 очищаются. далее аналогично - заполняем, жмем кнопку, данные заносятся на лист 2 в следующую строку.Ну вот, то, что *заточилось. Не люблю жестких привязок, поэтому на листе 1 можете вставлять по бокам, сверху, снизу, писать на листе что хотите и где (кроме значений закрашенных Вами - к ним (значениям) жесткая привязка. Поменял кнопку. Когда вижу такое что-то серое - на душе тоска сразу. Можете переприменить. Вашу работу не трогал. Убрал формулы. Пусть сама (железка) считает. Деньги за нее платили. Нажмете кнопку и смотрите Лист 2.
Все. Удачи.Тьфу ты. Забыл очистку. Если успею сегдня - доделаю. Нет - завтра кину измененный. Там немного, но зато тут и компания, и пиво.
Добавлено через 14 минут
Теперь чистит. Сразу за собой. Все. Пиво кончилось. Погнали добровольца.
Сами замените в том же модуле старое на это:Мотался по области. Только добрался. Дождь.
Короче, удаляйте все мое художество. Оно получилось какое-то садровое, негибкое, ненадежное. Смотрите это. Там лист Info, на нем все комментарии. Но изменения не радикальные, сами по себе. Принцып тот же. Удачи.Вот что наваял, но как остановить цикл, а то он весь диапазон заполняет. я чтото неверно сделал очевидно.
У Вас цикл вставляет значения три! раза. И Step по умолчанию = 1. Используйте "-1" если идете в обратном порядке, или если шаг не равен 1 (0.х, 3 и т.д.). Нужно вот так:
Но Вам это не поможет. У Вас получается очень жесткая привязка. Малейшее изменение условий - и все.
Там, кстати, у себя я нашел ошибку. Сейчас кину.Добавлено через 10 минут
Кто-то для переключки пасьянс ганяет, а мне VBA и интересно, и отключает.
Ну вот в этом варианте (думаю, это уже все ) если Вы внесете один/несколько дополнительных критериев оценки сотрудников в таблицу на листе 1, VBA сам поймет, если таких критериев нет на листе 2, сам создаст столбец, сам его назовет и сам поймет, куда и что записать. Если подойдет - пользуйтесь. Если заглючит - исправим вместе.
Замените в самом последнем варианте все полностью.Спасибо за огромную работу)
i = currCell.Row
на этой строке пишет ошибку.
честно говоря очень сложно для меняУ меня работает. Скиньте фрагмент и номер ошибки. Завтра посмотрю, сегодня занят.
Ваша задача кажется легкой, но это далеко не совсем так. Это базисное. Работа с ячейками, диапазонами, листами, книгами и т.д. Почему я залез в дебри? Для меня, когда раскидывают на несколько листов, как правило означает, что или очень много данных, или нужно срочно получить готовые материалы для отправки/распечатки, или структурируются/группируются данные для дальнейшей обработки с целью. или. и т.д. Так я и воспринял Ваши проблемы, как маленькую часть чего-то большого. А почему у Вас раскидано - теперь я уже не понимаю. Сделайте все на одном листе, что нужно на распечатку - выделили, "печать выделенного диапазона". Зачем мучиться, если просто нужно 2 + 2? Это все можно вручную.
Насчет сложности. Да, не просто. Если бы еще кто-то подключился, возможно что-то бы и упростили. Но не уверен, что много. Делать другими методами для получения тех же эффектов (средствами VBA) - думаю, кода было бы много больше. И это у Вас сегодня там 5 критериев оценки сотрудников. А завтра? На выставках собак критериев в десятки раз больше, а тут люди. Может еще упростить?
Давайте инфу. Завтра посмотрю.Еще так попробую.
1.В этой книге, которую кидаю теперь, несколько дополнительных листов. Черный ярлык - только для показа, как на Лист2 (. ) Вы можете перетянуть только шапку (можна и всю таблицу), и после запуска с Лист1 данные будут установлены в НОВОМ МЕСТЕ ПО СВОИМ МЕСТАМ (я специально на листах шапки сделал на сером фоне). Тоже самое Вы можете сделать с листом1. Все будет работать. Вся другая информация на Лист1 вокруг серого (сверху, снизу, слева, справа) будет игнорироваться. Вся другая инвормация на Лист2 (сверху, слева, справа) будет игнорироваться. а вот инфа снизу может/будет влиять на номер ряда для новых значений (но не номера столбцов. ).
2. Название листа Лист1 значения не имеет. Называйте как хотите.
3. Название листа Лист2 играет огромную! роль. Можно научить железку работать с переименованными листами Лист2. Это не много и не сложно. Но уже сами.
4. На Лист1 Вы применили "Данные > Проверка". Считаю, что сами себе устроили минное поле. Рано или поздно бабахнет. Если для ввода числа от 1 до 5 Вы считаете это нужным, то я бы лучше добавил 10 рублей к зарплате уборщицы, что б она вводила. И ей хорошо, и я больше чем уверен, что ошибки не будет. Другой, алтеранативный выход - вверху, жирными буквами: "ОЦЕНКА ПРОИЗВОДИТСЯ ПО ПЯТИБАЛЬНОЙ СИСТЕМЕ". Или что-то подобное.
Последнее. Что б скрыть столбик, его не "стягивают", а скрывают: формат > столбец >
скрыть (или что-то такое, в меня другая локализация). Потом найти легче.Макрос который создаст новую пустую строку через строку
Добрый день! Имеется excel файл, в оригинальном файле заполнено 50 тыс. строк x 60 столбцов.Копирование диапазона в том же листе - макрос
Всем привет! Ребята, помогите У меня есть формулы в ячейках АD23:AS34. Мне надо чтобы.Копирование данных с одного листа в таблицу на другом листе
Напишите пожалуйста код, как все данные из Лист2 колонки B9 скопировать на другой лист1 в умную.Макрос для сравнения значений в таблице со справочником, находящимся на другом листе
Добрый день, необходима помощь , а именно. Необходим макрос запускающийся по кнопке, который.Добрый день!
В написании макросов я новичок. По долгу службы надо было оптимизировать работу, поэтому пришлось приложить усилия и что-то наваять.Смысл работы нижеприведенного макроса следующий:
1) первым действием макрос вытягивает из листа "Реестр операций" на лист "Cash flow"(данный лист является основным)
2) затем выполняется цикл по суммированию (с помощью суммеслимн) и заполнению определенных диапозонов на листе "Cash flow" . Данные для суммирования также берутся из листа "Реестр операций".Подскажите, как можно оптимизировать цикл по заполнению ячеек, поскольку сейчас выполнение макроса занимает от 3 до 5 мин. Как можно его ускорить, либо быть может заменить имеющийся цикл For next на другую более быструю структуру.
Ниже приведен макрос.
Option Explicit
Sub Rachet()Dim vItem, avArr, li As Long
ReDim avArr(1 To Rows.Count, 1 To 1)Dim k As Integer, i As Integer, j As Integer
Dim a, b, c, d, e As Range
Dim Sum(1 To 18) As Long
Dim Name As String
Dim Shape As Shape
Dim sheet1, sheet2 As WorksheetSet sheet1 = Worksheets("Cash flow")
Set sheet2 = Worksheets("Реестр операций")
Set a = sheet2.Range("H3:H999000")
Set b = sheet2.Range("F3:F999000")
Set c = sheet2.Range("C3:C999000")
Set d = sheet2.Range("L3:L999000")
Set e = sheet2.Range("G3:G999000")'Блок №1 - вытаскивает уникальные значение "Статей затрат" на лист Кэшфло
With New Collection
On Error Resume Next
For Each vItem In b.Value
.Add vItem, CStr(vItem)
If Err = 0 Then
li = li + 1: avArr(li, 1) = vItem
Else: Err.Clear
End If
Next
End With
If li Then sheet1.[C8].Resize(li).Value = avArr'Блок №2 - заполнение ячеек с помощью формулы СУММЕСЛИМН на листе Кэшфло
For j = 1 To 70
k = 3
For i = 4 To 35
Sum(1) = Application.WorksheetFunction.SumIfs(a, b, sheet1.Cells(j + 7, 3), c, sheet1.Cells(2, k + 1), d, "*" & sheet1.Cells(j + 7, 2) & "*")
Cells(j + 7, i) = Sum(1)Sum(2) = Application.WorksheetFunction.SumIfs(a, b, sheet1.Cells(j + 79, 3), c, sheet1.Cells(2, k + 1), d, "*" & sheet1.Cells(j + 79, 2) & "*")
Cells(j + 79, i) = Sum(2)Sum(3) = Application.WorksheetFunction.SumIfs(e, b, sheet1.Cells(j + 150, 3), c, sheet1.Cells(2, k + 1), d, "*" & sheet1.Cells(j + 150, 2) & "*")
Cells(j + 150, i) = Sum(3)Sum(4) = Application.WorksheetFunction.SumIfs(a, b, sheet1.Cells(j + 223, 3), c, sheet1.Cells(2, k + 1), d, "*" & sheet1.Cells(j + 223, 2) & "*")
Cells(j + 223, i) = Sum(4)Sum(5) = Application.WorksheetFunction.SumIfs(e, b, sheet1.Cells(j + 294, 3), c, sheet1.Cells(2, k + 1), d, "*" & sheet1.Cells(j + 294, 2) & "*")
Cells(j + 294, i) = Sum(5)Sum(6) = Application.WorksheetFunction.SumIfs(e, b, sheet1.Cells(j + 367, 3), c, sheet1.Cells(2, k + 1), d, "*" & sheet1.Cells(j + 367, 2) & "*")
Cells(j + 367, i) = Sum(6)Sum(7) = Application.WorksheetFunction.SumIfs(a, b, sheet1.Cells(j + 442, 3), c, sheet1.Cells(2, k + 1), d, "*" & sheet1.Cells(j + 442, 2) & "*")
Cells(j + 442, i) = Sum(7)Sum(8) = Application.WorksheetFunction.SumIfs(e, b, sheet1.Cells(j + 513, 3), c, sheet1.Cells(2, k + 1), d, "*" & sheet1.Cells(j + 513, 2) & "*")
Cells(j + 513, i) = Sum(8)Sum(9) = Application.WorksheetFunction.SumIfs(a, b, sheet1.Cells(j + 589, 3), c, sheet1.Cells(2, k + 1), d, "*" & sheet1.Cells(j + 589, 2) & "*")
Cells(j + 589, i) = Sum(9)Sum(10) = Application.WorksheetFunction.SumIfs(e, b, sheet1.Cells(j + 660, 3), c, sheet1.Cells(2, k + 1), d, "*" & sheet1.Cells(j + 660, 2) & "*")
Cells(j + 660, i) = Sum(10)Sum(11) = Application.WorksheetFunction.SumIfs(a, b, sheet1.Cells(j + 736, 3), c, sheet1.Cells(2, k + 1), d, "*" & sheet1.Cells(j + 736, 2) & "*")
Cells(j + 736, i) = Sum(11)Sum(12) = Application.WorksheetFunction.SumIfs(e, b, sheet1.Cells(j + 807, 3), c, sheet1.Cells(2, k + 1), d, "*" & sheet1.Cells(j + 807, 2) & "*")
Cells(j + 807, i) = Sum(12)Sum(13) = Application.WorksheetFunction.SumIfs(a, b, sheet1.Cells(j + 883, 3), c, sheet1.Cells(2, k + 1), d, "*" & sheet1.Cells(j + 883, 2) & "*")
Cells(j + 883, i) = Sum(13)Sum(14) = Application.WorksheetFunction.SumIfs(e, b, sheet1.Cells(j + 954, 3), c, sheet1.Cells(2, k + 1), d, "*" & sheet1.Cells(j + 954, 2) & "*")
Cells(j + 954, i) = Sum(14)Sum(15) = Application.WorksheetFunction.SumIfs(a, b, sheet1.Cells(j + 1027, 3), c, sheet1.Cells(2, k + 1), d, "*" & sheet1.Cells(j + 1027, 2) & "*")
Cells(j + 1027, i) = Sum(15)Sum(16) = Application.WorksheetFunction.SumIfs(e, b, sheet1.Cells(j + 1098, 3), c, sheet1.Cells(2, k + 1), d, "*" & sheet1.Cells(j + 1098, 2) & "*")
Cells(j + 1098, i) = Sum(16)Sum(17) = Application.WorksheetFunction.SumIfs(a, b, sheet1.Cells(j + 1176, 3), c, sheet1.Cells(2, k + 1), d, "*" & sheet1.Cells(j + 1176, 2) & "*")
Cells(j + 1176, i) = Sum(17)Sum(18) = Application.WorksheetFunction.SumIfs(e, b, sheet1.Cells(j + 1247, 3), c, sheet1.Cells(2, k + 1), d, "*" & sheet1.Cells(j + 1247, 2) & "*")
Cells(j + 1247, i) = Sum(18)Читайте также: