Microsoft Excel
трюки • приёмы • решения
Как автоматизировать учет техники средствами VBA Excel
Продолжим тему, связанную с фирмой, которая занимается поставкой и ремонтом сложной строительной техники. В данном случае нас будет интересовать именно учет продаж машин. Разработка будет представлять новую автоматизированную книгу Microsoft Excel, и поэтому начнем работу с ее создания. На рис. 3.15 показан первый лист, который содержит данные о моделях предлагаемой техники. Столбец Код мы используем для обеспечения уникальности модели.
Рис. 3.15. Лист с информацией об имеющихся моделях
Для внесения нового названия модели мы воспользуемся формой, показанной на рис. 3.16. Вызов ее производится программно с помощью щелчка на кнопке Добавить новое название. Необходимая для этого процедура представлена в листинге 3.10. Из текста процедуры видно, что для значения свойства Name выбрано AddMod.
‘ Листинг 3.10. Обработка щелчка на кнопке Добавить новое название Private Sub CommandButton1_Click() AddMod.Show End Sub
Форма на рис. 3.16 включает несколько элементов управления. Два элемента Label играют поясняющую роль для соответствующих текстовых окон. Для текстового окна, отводимого под код модели, в качестве значения свойства Name выбрано Cod. Нижнее текстовое окно отводится для названия модели (Name Nazv). Еще один элемент на форме — кнопка (Name — Coml), которая позволяет добавить указанную новую модель на лист.
Рис. 3.16. Форма для добавления новой модели техники
Добавление модели производится с помощью процедуры, представленной в листинге 3.11. Обработка проверяет уникальность кода, и при положительном ответе информация записывается на лист Модели.
‘ Листинг 3.11. Обработка щелчка на кнопке Добавить модель Private Sub Com1_Click() If Cod.Text = «» Then MsgBox («Поле КОД необходимо заполнить») Exit Sub End If If Nazv.Text = «» Then MsgBox («Поле названия необходимо заполнить») Exit Sub End If Nom = 0 While Worksheets(«Модели»).Cells(Nom + 2, 2).Value <> «» Nom = Nom + 1 Wend For i = 1 To Nom CodList = Worksheets(«Модели»).Cells(i + 1, 1).Value If CStr(CosList) = CStr(Cod.Text) Then MsgBox («Такой код модели уже встречался») Exit Sub End If Next Worksheets(«Модели»).Cells(i + 1, 1).Value = Cod.Text Worksheets(«Модели»).Cells(i + 1, 2).Value = Nazv.Text MsgBox («Информация внесена») AddMod.Hide End Sub
На рис. 3.17 продемонстрирован один из вариантов заполнения информации о модели.
Рис. 3.17. Заполнение информации о новой модели в форме
Добавим небольшой сервис — при активизации формы в текстовое окно для указания кода обеспечим внесение кода модели предыдущей записи. Для этого процедуру UserForm_Activate необходимо оформить так, как показано в листинге 3.12.
‘ Листинг 3.12. Процедура, выполняемая при активизации формы на рис. 3.16 Private Sub UserForm_Activate() Nom = 0 While Worksheets(«Модели»).Cells(Nom + 2, 1).Value <> «» Nom = Nom + 1 Wend NumPred = Worksheets(«Модели»).Cells(Nom + 1, 1).Value Cod.Text = NumPred End Sub
Теперь перейдем к следующему листу (рис. 3.18), который будет хранить информацию о номенклатуре (конкретных серийных номерах моделей). Здесь данные в столбце Код модели представляют значения уникальных кодов, содержащихся на листе Модели.
Рис. 3.18. Организация листа Номенклатура
Щелчком на кнопке Добавить открывается форма (рис. 3.19), которая позволяет добавить в перечень номенклатуры еще одну позицию. Для начала потребуется оформить процедуру, выполняемую по щелчку на кнопке Добавить на рис 3.18 (листинг 3.13).
‘ Листинг 3.13. Обработка щелчка на кнопке, расположенной на листе Номенклатура Private Sub CommandButton1_Click() AddSerNum.Show End Sub
Теперь перейдем к процедурам, непосредственно связанным с формой на рис. 3.19. Первая процедура (листинг 3.14), которая потребуется, выполняется при открытии данной формы.
‘ Листинг 3.14. Процедура, выполняемая при активизации формы Private Sub UserForm_Activate() Nom = 0 While Worksheets(«Модели»).Cells(N + 2, 1).Value <> «» N = N + 1 Wend Spk.Clear For i = 1 To N Spk.AddItem Worksheets(«Модели»).Cells(i + 1, 2).Value Next End Sub
Рис. 3.19. Форма для добавления записи на лист Номенклатура
Таким образом, список моделей на форме заполнен, и пользователь, выбрав модель, может внести в текстовое окно ее серийный номер. После этого осталось щелкнуть на кнопке Добавить (Name — OK), а необходимая процедура обработки щелчка на данной кнопке приведена в листинге 3.15.
‘ Листинг 3.15. Процедура внесения очередной записи на лист Номенклатура Private Sub OK_Click() If Spk.ListIndex = -1 Then MsgBox («He указана модель») Exit Sub End If ‘ Индекс списка, который соответствует выбранной модели NomMod = Spk.ListIndex ‘ Извлечение кода модели KodModel = Worksheets(«Модели»).Cells(NomMod + 2, 1).Value ‘ Считывание серийного номера из текстового окна на форме NumberSer = SerNum.Text ‘ Подсчет строк на листе Номенклатура N = 0 While Worksheets(«Номенклатура»).Cells(N + 2, 1).Value <> «» N = N + 1 Wend ‘ Запись информации о модели и новом серийном номере Worksheets(«Номенклатура»).Cells(N + 2, 1).Value = KodModel Worksheets(«Номенклатура»).Cells(N + 2, 2).Value = Number.Ser Hide End Sub
Технически наиболее трудоемкая задача заключается в заполнении бланка заказа (рис. 3.20). Для облегчения труда сотрудников офиса мы создадим дополнительный лист управления, который представлен на рис. 3.21. Он позволит, используя имеющиеся данные на разных листах книги, эффективно, с минимальной потерей времени производить заполнение списка заказа.
Рис. 3.20. Структура списка заказа
При активизации листа, представленного на рис. 3.21, мы произведем заполнение поля со списком моделей (Name — Spk1). Далее пользователь, выбрав в этом списке необходимую модель, автоматически получает (за счет процедуры Spk1_Click) в правом поле со списком (Name — Spk2) перечень имеющихся серийных номеров. Теперь ему осталось выбрать конкретный серийный номер и щелчком на кнопке Включить добавить запись на лист Бланк. Перейдем к технической реализации рассмотренных действий. Первая процедура, которая нам понадобится для реализации описанного плана, выполняется при активизации листа (листинг 3.16).
‘ Листинг 3.16. Процедура, выполняемая при активизации листа Управление Private Sub Worksheet_Activate() N = 0 While Worksheets(«Модели»).Cells(N + 2, 1).Value <> «» N = N + 1 Wend Spk1.Clear For i = 1 To N Spk1.AddItem Worksheets(«Модели»).Cells(i + 1, 2).Value Next Spk2.Clear End Sub
При выборе пользователем необходимой модели с помощью щелчка в поле со списком Spk1 автоматически выполняется процедура Spk1_Click, которая приводит к заполнению на листе Управление (см. рис. 3.21) правого списка (Spk2) серийными номерами выбранной модели. Для этого требуется оформить процедуру Spk1_Click в соответствии с листингом 3.17. Здесь последовательно перебираются все серийные номера на листе Номенклатура, и при нахождении серийного номера, относящегося к указанной модели, он включается в поле со списком Spk2.
Для удобства мы сделали автоматическое выделение в списке серийных номеров первого (самого верхнего) элемента:
If Spk2.ListCount > 0 Then Spk2.ListIndex = 0 End If
Таким образом, мы обеспечили пользователю выбор модели машины и ее серийного номера.
‘ Листинг 3.17. Процедура, выполняемая при щелчке на списке моделей rivate Sub Spk1_Click() Kod = Worksheets(«Модели»).Cells(Spk1.ListIndex + 2, 1).Value N = 0 While Worksheets(«Номенклатура»).Cells(N + 2, 1).Value <> «» N = N + 1 Wend Spk2.Clear For i = 1 To N If Worksheets(«Номенклатура»).Cells(i + 1, 1).Value = Kod Then Spk2.AddItem Worksheets(«Номенклатура»).Cells(i + 1, 2).Value End If Next If Spk2.ListCount > 0 Then Spk2.ListIndex = 0 End If End Sub
Рис. 3.21. Организация листа Управление, предназначенного для заполнения бланка
Теперь, когда определены модель машины и ее серийный номер, следует включить указанную позицию в бланк заказа (лист Бланк). Однако, кроме включения, указанная позиция должна быть удалена с листа Номенклатура. В листинге 3.18 представлена необходимая для этого процедура, которая выполняется при щелчке на кнопке Включить.
‘ Листинг 3.18. Процедура, выполняемая при щелчке на кнопке Включить Private Sub OK_Click() ‘ Подсчет имеющихся записей в заказе на листе Бланк Nzakaz = 0 While Worksheets(«Бланк»).Cells(Nzakaz + 5, 1).Value <> «» Nzakaz = Nzakaz + 1 Wend ‘ Подсчет имеющихся записей на листе Номенклатура N = 0 While Worksheets(«Номенклатура»).Cells(N + 2, 1).Value <> «» N = N + 1 Wend ‘ Извлечение кода модели указанной в поле со списком Spk1 Kod = Worksheets(«Модели»).Cells(Spk1.ListIndex + 2, 1).Value ‘ Обработка указанной модели с выбранным серийным номером For i = 1 To N If Worksheets(«Номенклатура»).Cells(i + 1, 1).Value = Kod _ And Worksheets(«Номенклатура»).Cells(i + 1, 2).Value = Spk2.Text Then ‘ Запись нового номера позиции в списке заказа Worksheets(«Бланк»).Cells(Nzakaz + 5, 1).Value = Nzakaz + 1 ‘ Внесение названия модели Worksheets(«Бланк»).Cells(Nzakaz + 5, 2).Value = Spk1.Text ‘ Внесение серийного номера Worksheets(«Бланк»).Cells(Nzakaz + 5, 3).Value = Spk2.Text ‘ Удаление строки с указанным серийным номером на листе Worksheets(«Номенклатура»).Row(i + 1).Delete Exit For End If Next ‘ Очистка списка Spk2 и повторное заполнение его серийными номерами Spk2.Clear For i = 1 To N If Worksheets(«Номенклатура»).Cells(i + 1, 1).Value = Kod Then Spk2.AddItem Worksheets(«Номенклатура»).Cells(i + 1, 2).Value End If Next If Spk2.ListCount > 0 Then Spl2.ListIndex = 0 End If End Sub
Здесь после включения указанного серийного номера модели в список заказа производится повторное заполнение списка серийных номеров и активизация верхнего элемента поля со списком. Это действие можно также реализовать меньшим количество строк программного кода:
Spk2.Delete Spk2.ListIndex If Spk2.ListCount > 0 Then Spk2.ListIndex = 0 End If
На рис. 3.22 представлен заполненный вариант бланка заказа. Разумеется, рассмотрешгая задача подразумевает развитие, которое является достаточно индивидуальным для каждой организации.
Рис. 3.22. Вариант заполненного бланка заказа
Источник
Смета на ремонт квартиры под ключ
Почему я советую составлять общую смету всего ремонта квартиры, от самого начала до полного завершения? Потому, что вы получите четыре рычага управления, необходимых для грамотного планирования ремонта.
- Станет ясна затратная часть вашего бюджета, реальные цифры, четко обозначенная цель;
- Можно строить долгосрочные бюджетные планы, ориентированные на эту цель;
- Сокращается время нахождения в «перманентном ремонте», постоянной стрессовой ситуации;
- Можно принимать оперативные финансовые и дизайнерские решения, понимая отдаленные последствия таких действий.
Бюджет, а не смета
Смета, это документ наемных работников, получающих оплату своих услуг в соответствии с утвержденной сметой.
Для тех, кто финансирует ремонт, важен бюджет. Документ, объединяющий расходы (по сметам), накопления и финансовые поступления. Но открытому обсуждению подвергается только затратная часть бюджета, то есть собственно сумма смет, или общая смета ремонта квартиры под ключ.
Общую смету надо составлять самостоятельно, учитывая все виды планируемых затрат, даже отдаленных. Как это сделать, читайте «Составляем бюджет на ремонт квартиры — пошаговая инструкция «.
То есть, в этой смете складываются все окончательные цены по договорам на выполнение работ, поставки, монтажа и изготовления. Плюс отделочные материалы, отнесенные к поставке заказчика. Еще все покупное оборудование, светильники, мебель, бытовая техника.
Обязательно добавьте транспортные расходы ( доставку), непредвиденные расходы и организационные расходы.
Все отделочные материалы и оборудование, которое вы будете покупать для ремонта, я советую снабдить отдельной сметой. Например, такой.
Таблица Excel
Общая смета на ремонт квартиры под ключ составляется в программе Excel. Это просто даже для начинающих пользователей.
Все сметы на выполнение работ и монтаж оборудования предоставляются вашими подрядчиками.
Перечни, номенклатура и количество отделочных материалов указаны в дизайн проекте. Даже если вы сами делаете дизайн, эта информация должна в проекте быть. Стоимость материалов определяете самостоятельно, исследовав рынок.
Цену на мебель, светильники и технику надо посмотреть в интернет магазинах.
Далее, используя функцию автосумма Σ, считаем общую смету. Это будет проектная, или плановая стоимость ремонта квартиры.
Контроль сметы
Рядом со столбцом плановая стоимость обязательно отобразите фактические затраты по данной позиции. Контроль ведется по факту оплаты ( чекам) – вашим ежедневным записям.
Эти данные удобно вносить в электронную таблицу. Сразу видно, где превышена смета и на чем можно сэкономить, чтобы сумма в разделе не вышла за пределы плана.
Как вести таблицы общей сметы
Очень рекомендую иметь две таблицы
1. В формате .xls в папке с файлами по вашему ремонту. Форму таблицы можно скачать по ссылке в конце статьи. Ежедневно записываете все ваши расходы по ремонту квартиры в простой список (бумажный или электронный – как удобно). Примерно раз в неделю вносите цифры в вашу таблицу в формате .xls в ячейки фактических затрат. Настройте автоматическое суммирование, если по одной позиции вы делаете несколько платежей.
В этой таблице вы будете видеть ваши текущие расходы.
2. В печатном виде, в папке с документами по ремонту или в удобном планировщике ремонта, ежедневнике, упрощающем организацию ремонта квартиры.
Этот лист будет вам напоминать контрольные цифры вашего бюджета.
По окончании покупок, сюда надо будет добавить фактические затраты из таблицы в формате .xls. Ваши успехи будут сразу видны.
Таблицу общей сметы скачайте, перейдя по ссылке.
Если захотите узнать больше про удобный планировщик ремонта, подпишитесь на рассылку обновлений, скоро появятся его описание и инструкция пользователя.
Источник