Заполнение ячеек в excel по условию vba
Всем нам приходится - кому реже, кому чаще - повторять одни и те же действия и операции в 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) :
После выбора функции выделяем ячейки с аргументами (с суммой, для которой надо посчитать НДС) как в случае с обычной функцией:
Как известно, для полноценной работы с данными (фильтрации, сортировки, подведения итогов и т.д.) нужен непрерывный список, т.е. таблица без разрывов (пустых строк и ячеек - по возможности). На практике же часто мы имеем как раз таблицы с пропущенными пустыми ячейками - например после копирования результатов сводных таблиц или выгрузок в 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) .
Помоги решить проблему, нужно написать макрос или что-то в этом роде. Суть такая есть 2 столбца и нужно чтобы определял что написано во втором столбце и автоматически писал в-первый столбец.
Заполнение ячейки по условию больше или меньше процентов
Здравствуйте. Помогите написать формулу, пожалуйста. У сотрудника есть план продаж и.Скопировать значение ячейки из другой таблицы по условию
Есть таблица (приложенный файл). Необходимо значения последнего столбика (L), скопировать в.Копирование содержимого ячейки на другой лист по условию
Привет великим гуру владеющим в совершенстве Excel. В программе не силен, поэтому вопрос глупый.Заполнение ячейки данными из другой
Добрый день. Стоит такая задача: в одной ячейке есть какой-то текст, отдельные его части должны.Реализовать то не трудно, но вот что именно нужно сделать не очень понятно.
Нашли текст во втором столбце - что с ним делать? Его копировать в первый столбец? Если нет, то что надо записывать в первый столбец?т.е должен определять по второму столбцу (по строчке) если яблоко, то в 1ый столбец пишет фрукт.
1-ый столбец (этот столбец заполняется автоматически) 2-ой столбец (этот столбец заполняется руками)
фрукт яблоко
фрукт банан
овощ свекла
ягода малинаРешение
Здравствуйте. Задача примерно такая же.
В поликлинике карты разных категорий пациентов начинаются с определённых цифр. Например, если карта 91/любые цифры - то это всегда пенсионеры и т.д. - см. пример. Нужно, чтобы при написании полного номера карты - принадлежность категории пациента определялась по первым цифрам.
Добрый день.
Подскажите как сделать, чтобы формулы считались только один раз.
Т.е. есть формулы (а так же функции, связи с другими файлами), которые правильно все считаются, но из-за огромного числа записей открытие и сохранение файла очень долгое.
Как я вижу нужен какой-то макрос, который будет срабатывать при открытии документа, смотреть если есть запись в нужной ячейке, то не трогать, или если там пусто, то сделать нужный расчет (запрос, связь с другим листом или прочее), и записать в ячейку результат (т.е. не формула).Добавлено через 13 минут
добавлю
формул много разных, если будет вопрос о примере, напишите попробую сделать кусочек из файлов.Копирование содержимого ячейки из списка на другой лист по условию
Добрый день! Прошу помощи в решении следующего вопроса: Есть список операций , нужно из него.Заполнение ячейки по данным другой таблицы
Доброго времени суток. В работе с бд возник такой вопрос. Есть несколько таблиц с изделиями, в этих.Заполнение ячейки частичным содержанием другой
Доброго времени суток! Прошу помочь решить задачу. Необходимо заполнить одну ячейку частичным.Поиск и запись значения в определенные ячейки из другой книги по условию даты
Добрый вечер, столкнулся с такой проблемой, сам к сожалению не в силах справится может вы поможете.Добрый вечер!
Нужна помощь профессионалов. Есть выгрузка с данными (вкладка data) и необходимо эти данные заполнить в таблицу (вкладка report). Я новичок в этом деле и никак не могу понять как написать макрос с условиями.
Я создаю два массива с данными а потом как сделать переборку данных по условию с заполнением таблицы?Заполнение таблицы по условию
Доброго всем! Необходимо заполнить таблицу значениями. Сейчас таблица заполняется построчно, при.Заполнение ячейки по условию от содержания другой
Доброго времени суток! Помоги решить проблему, нужно написать макрос или что-то в этом роде.Вставка таблицы по условию
День добрый! В примере в первых двух строках содержатся условия, обозначенные разными цветами.Удаление строк из таблицы по условию
Помогите адаптировать код под удаление всех строк содержащих значение меньше 30, в том числе и.Добрый!
Начало правильное, далее не понимаете что делать.
1. Два словаря тут может быть и не нужны, но пускай - в один из них можно собрать уникальные даты, типа как фамилии в той задаче, откуда Вы этот код взяли.
2. В другой словарь к ключу, создаваемому из значения дата+название заносите значение.
3. В финале в цикле по репорту создаёте аналогичный ключ, по которому извлекаете значение.
Всё точно как в той задаче (уже не помню где кому я её решал. )
Но вот с формированием ключа есть проблемы - как соотнести
27.09.2015 8:35 ХЛЕБНЫЕ ЕДИНИЦЫ
или
27.09.2015 22:20 продлен.
с
27.09.2015
ХЛЕБНЫЕ ЕДИНИЦЫ
завтрак обед ужинПодозреваю что нужно анализировать время, но как - это нам неизвестно.
Там ещё в результате появляется 6 / 2 - это чуть усложняет процесс, но сделать можно.
С примечаниями тоже не всё понятно - оно что, всегда будет только одно одинаковое на весь день?
С формированием результата тоже сложность, в один цикл не уложиться, т.к. там шаг меняется, т.е. нужно делать несколько разных циклов в цикле.Ну а в общем задача точно такая как в том образце.
Добавлено через 5 минут
Нашёл где кому писал, вот линк, там и файл есть рабочий:
Копирование массива данных со страницы на страницуДобавлено через 37 минут
Вот немного заготовил, добивайте - там нужно доделать вывод собранного, ну и когда там что по распорядку мне неизвестно, делал наугад.Залить диапазон ячеек, если одна из ячеек содержит нужный текст
Здравствуйте! Помогите залить цветом диапазон 24R*20C, если ячейка L10 содержит текст "Дефектов не.Как получить диапазон ячеек отвечающих условию
Как получить диапазон ячеек отвечающих условию? Например, нужно получить диапазон ячеек у которых.Вставка формул в динамический диапазон ячеек через равное кол-во ячеек, заданных по условию
Всем доброго дня! Есть диапазон ячеек, изменяющийся динамически, но не превышающий диапазон.Закрашивание нужных ячеек в строке по условию
Добрый день! Думал, что распрощался с VBA, так нет! Снова и снова требуют решать поставленные.Метод Find не даст надежных результатов, так как например ряд "В1" будет найден и в "В10", и в "В1".
Надежен только перебор значений.ААААААААААААААААААААААААААААААААААААААА!
Я в шоке. Спасибо Вам огромное. Времени зря не теряя написал что смог! Посмотрите пожалуйста и подскажите что не так Файл в архиве в виде модуля! Там адреса строк и столбцов другие, но смысл тот же!
Так как вы написали код, я так конечно не смогу, но очень буду к этому стремиться, вы мне очень помогли! Просто не к кому обратиться за помощью, что бы кто то пояснил или подтолкнул, на нужную мысль. Я не так давно занимаюсь этим. Ваш код идеален, буду пробовать ! Еще раз СПАСИБО!Вы правы! Мой код не работает! Сижу разбираюсь с вашим кодом! А как в нем реализовать проверку? Были повторения или нет в местах ?
Добавлено через 15 минут
Применил Ваш код к документу, почему то не работает! Может потому что у меня в таблице данные беруться с другого листа и в ячейке формула? Не пойму!Добавлено через 6 минут
Когда вместо значения ряда "В1" ставлю "1" или "2" не закрашивает!Конечно. Обычно выкладывают пример в том виде, в котором он должен работать.
Добавлено через 28 минут
В ряду В1 30 мест, если поменять на 1 (28 мест) или 2 (29 мест) , не исправив конечное место 30, то получается, что продано несуществующее в ряду место, и разумеется оно не находится и ничего не закрашивается.Дело в том, что данные ряда вноситься могут хаотично, эти данные вносятся каждый день и каждая строка может содержать любое значение из списка рядов! Поэтому я хотел реализовать просмотр каждой ячейки по столбцу с верху в низ и последовательно прорисовывать зал!
Добавлено через 4 минуты
При этом проверяя, было ли закрашено место в Зале.Добавлено через 31 минуту
В документе 39 листов, из них с 1 -31 дни, на каждом листе 30 разделов по имени кассы и у каждой кассы ценовая категория от 100 до 10000 р т.е касса - 1 строка и ниже 100 строк с ценой от 100-10000р. и так 30 раз.
На листе вызывается форма с именем кассы ценой и вариантом сдать или продать билеты, при выборе сдать - открывается форма с вводом ряда, начального места, конечного места и ведется подсчет общего количества билетов по этой цене для данной кассы, потом эти данные копируются в накладную для печати и в лист с именем данного дня заносится сумма количества билетов по этой цене, и так вводятся данные по каждой цене относящейся к этой кассе. Данные ввода заносятся в временный лист накладной, при вводе проверяется правильность ввода данных и в итоге после закрытия накладной по данной кассе со всеми ценами и соответствующими билетами по рядам, из этой накладной копируются все данные в лист "Архив", при копировании ищется последняя пустая ячейка и данные получается, собираются в этом листе каждый день и значение ряда и касс может быть в любом порядке!Читайте также: