В период мирового кризиса с деньжатами стало туговато.
У маленьких предприятий расходы превысили доходы.
Цены кусаются, досада. Особенно, если то что нужно стоит дорого.
Вот и необходимые ПК - не по карману.
И объемы то маленькие, а Заказчики требуют сметы в АВК.
Как быть??
Как и во все тяжелые времена включаем смекалку.
Ставим перед собой задачу: сделать свой ПК без сторонних программ для программирования.
Словом: используем средства Винды и офиса.
Все расчеты выполняем в ЕКСЕЛЕ, а чтобы никто не догадался, выпускаем документы в ВОРДе, а еще лучше в шаблонах документов тех ПК, которые требует Заказчик.
ИТАК закупите ручки и бумагу - начинаем творить.
Прежде всего нам понадобиться база расценок ДБН.
В свободной публикации этих вещей нет, есть в Зодчем техкарты, но при их использовании нужно вручную создавать базу самим.
Учитывая украинский опыт взаимствования норм (пример с Гарячкиным) мы не будем сами заниматься разработкой базы.
Если у наших друзей или знакомых есть ПК, рекомендованный Минрегионстроем, попросим набрать сметку со всеми расценками из всех сборников.
Что, трудоемко?
Не думаю.
Для примера возьмем ПК АВК5 (для рекламы этого продукта) последней версии и попробуем создать базу расценок всех сборников Р (Ремонтные работы).
Создаем стройку, объект, смету.
На уровне локальной сметы жмем ALT+F2.
В НСИ отмечаем "Ремонтно строительные-работы". В поле поиска "поисковый образ/часть" ставим букву Р и жмем "Начать поиск".
Все расценки всех сборников выстроились в порядке возрастания.
Осталось только выбирать.
Ремонтных расценок где-то больше 7500шт. Выбирать вручную будет очень накладно.
Проанализируем наши действия при наборе и постараемся процесс автоматизировать.
1.ALT+F2
2.ENTER
3.1
4.ENTER
5.ALT+F2
6.опускаемся на одну позицию вниз (DOWN)
7.повторяем п.2-6
и т.д. до конца.
Мы нажимаем одни и те же клавиши на клавиатуре в цикле 7500раз.
Если заставить клавиши нажиматься самим, то и набор сметки выйдет в сто крат дешевле.
Заставить клавиши работать самостоятельно при помощи ВИНДЫ можно, если написать соответствующий скрипт.
Итак открываем обычный блокнот и пишем: '=============================================
Set WshShell = CreateObject("WScript.Shell")
DIM cnt
cnt=10
While cnt > 1
NABOR
cnt=cnt-1
Wend
Sub NABOR ()
WshShell.SendKeys "%{F2}"
WshShell.SendKeys "{DOWN}"
WshShell.SendKeys "{ENTER}"
WScript.Sleep 100
WshShell.SendKeys "1"
WshShell.SendKeys "{ENTER}"
End Sub
'=============================================
Попробуем разобраться:
1. Set WshShell = CreateObject("WScript.Shell") '(в переменную WshShell создать объект "WScript.Shell")
2.Создаем счетчик количества вызова функции набора одной строки
3. Устанавливаем счетчик в 10 (набор десяти строк) для эксперимента, потом поставим сколько нужно.
4. While -это цыкл вызова функции: выполняется до тех пор пока счетчик больше 1 (первую позицию мы набрали вручную), дальше с каждым вызовом счетчик уменьшается на единицу cnt=cnt-1 и при достжении cnt=1 -выходим - Wend
5. Sub NABOR () -это и есть сама функция набора расценок.
6. WshShell.SendKeys "%{F2}" - означает подать команду SendKeys объекту WshShell.
SendKeys - это виртуальные клавиши, "%{F2}" - это ALT+F2
7. Далее {DOWN} , {ENTER}, WScript.Sleep 100 -это задержка в 100миллисекунд чтобы в АВК установилась расценка, далее 1 и {ENTER}. Все понятно.
Всю нашу писанину сохраняем под именем RAVK5.vbs
Присвоив файлу расширение vbs мы как бы прописываем его к программе (есть в ВИНДЕ), которая выполняет скрыпты.
А теперь внимание: ни вкоем случае не пробуйте запускать ваш файл, т.к. начнут работать виртуальные клавиши и натворят делов на компе без вашего на то разрешения.
Делаем следующее:
Становимся курсором на файл, правая кнопка мыши >> отправить >> Рабочий стол (создать ярлык).
На рабочем столе появится ярлык нашего скрипта.
Становимся на него курсором, правая кнопка мыши >> свойства >> ставим курсор в окно "Быстрый вызов". Нажимаем клавиши ( к примеру) Ctrl+Alt+Z. Жмем ОК.
Мы присвоили ярлыку гарячие клавиши и теперь мы всегда сможем вызывать наш скрипт по нажатию этих клавиш.
Итак открываем смету и делаем в строгой последовательности:
1.На уровне локальной сметы жмем ALT+F2.
2.В НСИ отмечаем "Ремонтно строительные-работы". В поле поиска "поисковый образ/часть" ставим букву Р и жмем "Начать поиск".
Все расценки всех сборников выстроились в порядке возрастания.
Жмем:
3.ENTER
4.1
5.ENTER
Набрана первая расценка, теперь передаем слово скрипту: жмем Ctrl+Alt+Z.
Если все правильно сделано - пошла работа, ждем пока не наберутся 10 расценок.
Далее - установите в скрипте счетчик на 100 или 200 и повторите урок для закрепления и уверенности в дальнейших действиях.
Пока работает ваш робот, не мешайте ему, не нажимайте никаих клавиш, пейте кофе и любуйтесь теперь уже вашим детищем.
Робот сможет вам набрать расценки из любых сборников: прейскуранта, материалов и пр.
На слудеющем уроке набранную нами смету мы превратим в свою базу расценок.
Напоминаю, что мы своими дейстиями не нарушаем закон об авторском праве, мы используем программу в прямых целях - набираем сметы.
Есть вопросы-задавайте, нет - поедем дальше.
Змінено: Valentin, January 22 2009 22:32
Вирусы-это вредоносные программы. Создаются любыми способами.
За распространение вредоностных программ есть уголовная ответсвенность.
Змінено: Valentin, January 26 2009 12:53
Итак наши друзья или товарищи выполнили нашу просьбу и набрали нам смету.
Т.к. их труд мы превратили в удовольствие, а за удовольствие нужно платить, то и за смету мы им ничего не должны.
Теперь сформируем максимум выходных документов в АВК- не помешают.
1. Локальная смета.
2. Локальная смета с расчетом единичной стоимости.
3. Расчет ОПР.
4. Если версия 2.8.0. - файл OUTBOX.
Если есть версия 2.8.0, то эти документы можно продублировать в формате ЕКСЕЛЬ -пригодятся.
Создаем папку MAVK5_280_UKR (или RUS), а в ней папку DOC.
Забрасываем в нее наши документы, благодарим друзей и продолжаем работу.
Определяемся с алгоритмом поставленной задачи.
Что нам нужно для выпуска сметы??
1. Создать сметную строку 11 граф.
2. Создать концовку.
3. Произвести расчет.
4. Вывести на печать.
Все!
Для создания сметной строки мы имеем достаточно данных, которые есть в базовой смете.
Создадим таблицу из того , что имеем:
===============================================================
ШИФР |Наименование |Ед измерен. |Всего |З-та рабочих |МАШ |З-та машинист |ТЗ раб |ТЗ МАШ |ОПРК1 |ОПРК2
Скопируйте эти строки и вставьте в ЕКСЕЛЬ в первую ячейку A1.
Так будет наглядней, чем в текстовом файле.
Все остальные строки идентичны.
Данные полученные нами из АВК уже расчитаны с учетом з-ты 2300грн. и продолжительности рабочего времени 167,00 ч-час в месяц.
(для 2009г нужно установить в АВК 167 ч-час)
ОПР у нас есть тоже расчитаны для каждой строки, мы могли бы взять итоги, но учитывая много вопросов по расчету ОПР, возмем только К1 (к для определения ТЗ) и К2 (к для определения остальных статей расходов) и расчитаем сами.
Стоимость материалов, которая нужна для концовки можно высчитать в каждой графе:
Всего - З-та рабочих - МАШ = МАТ или 10623,43 - 9390,1 - 4,20 = 1229,13грн
Если откроем нашу смету с единичкой, то увидим там, что материалы для Р1-1-1 стоят 1229,13грн.
Также можно высчитать разряд работ, зная ТЗ, З-ту и шкалу тарифных ставок.
Вобщем табличка, которую мы создали вполне подойдет нам как база данных.
Теперь задача: как из сметы получить табличку в таком виде?
Нам подойдет программка для конверации РТФ в ЕКСЕЛЬ (в загрузках).
Конвертируем локальную смету. Далее все делаем ручками, т.к. мы еще не знакомы с автоматикой ЕКСЕЛЬ.
В ЕКСЕЛЕ удаляем столбцы А, D, F, H, I, K -не нужны.
Дальше удаляем строки 1-17 -не нужны.
Осталось 5 столбцов с таким набором:
A B C D E
Р1-1-1 Розробка грунту всередині будівлі в котлованах
100м3 10623,43 9390,10 4,20 1,89 787,10 0,15
Становимся на ячейку А1.
Далее >> меню >> данные >> сортировка АЯ.
После сортрировки ненужные строки вначале и в конце листа удалить.
Должны остаться только строки с расценками.
Выделим столбцы C,D,E, в меню Правка нажмем Вырезать и вставим в столбец O ( перенесем пока , чтобы не мешали) и сохраним наш файл как УРОК2.xls в нашу папку DOC.
Смотрим на второй столбец, где наименование.
Текст и ед. измерения у нас слиты в одной ячейке.
Становимся на ячейку B2.
Выделим в тексте пробелы от конца текста до начала единицы измерения (100м3), жмем копировать ( в буфер).
Далее выделяем столбец B >> правка >> найти - вводим (вставляем скопированные пробелы) >> заменить все -@ - происходит замена строки пробелов на один символ @.
Далее >> меню >> данные >> текст по столбцам >> с разделителями >> другой (@) >> готово.
Наименования и ед. измерения разделились.
Копируем столбец О в D, D выделяем >> меню >> данные >> текст по столбцам >> с разделителями >> отмечаем пробел >> готово.
Копируем столбец Р в F, повторяем операции разделения.
Копируем столбец Q в H, повторяем операции разделения.
Столбцы O,P,Q удаляем. Выделяем D - I >> правка >> найти - вводим -- >> заменить все -оставляем пусто - происходит замена ненужных символов --. Тоже проделываем и с __-__, потом с - .
Осталось вставить последние столбцы -Коеф ОПР.
Конвертируем расчет ОПР в ЕКСЕЛЬ. Обнаруживаем, что количество строк меньше, чем в смете.
Обнаруживаем, что на многие расценки ОПР не начисляется.
Дело в том, что там где нет з-ты рабочих остается очень маленькая з-та машинистов и ОПР в этом случае меньше гривны.
АВК такие строки пропускает. Как же нам достать все К1 и К2??
Если мы станем на уровне нашей сметы в АВК и зададим К к объему 1000, то з-та вырастет в 1000раз (она нам не нужна), но зато не будет строк с результатом ОПР меньше 1грн. К1 и К2 при этом не изменятся.
Так и сделаем. Формируем документ ОПР, опять конвертируем.
Удаляем строки 1-6, отмечаем ячейку B2, включаем сортировку АЯ, удаляем ненужные строки вначале и в конце.
Удаляем все столбики, кроме, шифра и К1-К2 (0,098 и 2,21).
Копируем столбцы А-С и вставляем в выделенный столбец J нашей базы.
Проверим шифры в столбцах А и J в начале и в конце таблицы, если они равны, то удаляем столбец J.
Все, мы получили таблицу такую, как хотели.
Теперь меню >> файл >> сохранить как >> (тип файла) Текстовые файлы (с разделителями табуляции), (имя файла) P.dat
То есть конвертируем нашу таблицу в текстовый формат даты.
Вот мы и получили базу данных ремонтных расценок P.dat
Такие базы есть и в АВК и в АС4 и в СТС, но они имеют разный формат и зашифрованы от нас пользователей.
Мы шифроваться пока не будем, а перенесем нашу базу на место ее постоянного жительства в ее родную папку.
Создаем в папке MAVK5_280_UKR еще одну для хранения баз - Data и переносим в нее файл P.dat
Мы таким способом можем создать и Е.dat и МАТ.dat и МАШ.dat и т.д.
Как мы уже говорили раньше, имея список расценок или готовую смету можно создать фрагмент для последующего его применения в следующих версиях АВК, чтобы не пользоваться медленным скриптом.
Берем программку "Пользовательские фрагменты для АВК3" (в загрузках).
Меню >> утилиты >> создать накопительную .... Ф4 в FRG..
Выбираем нашу смету, от выбора следующей отказываемся.
Задаем произвольный номер, в наименовании пишем "Ремонтные расценки"
Получим текстовый файл такого содержания: =====================================================
ФПН252`Ремонтные расценки*
:ДУ`Нажмите кнопку "Сохранить" `0*
:Д*
:ВС=Р1-10-1*
:Л?С.`1,0*
:ВС=Р1-10-2*
:Л?С.`1,0*
:ВС=Р1-10-3*
:Л?С.`1,0*
:ВС=Р1-10-4*
:Л?С.`1,0*
:ВС=Р1-1-1*
:Л?С.`1,0*
:ВС=Р1-11-1*
:Л?С.`1,0*
:ВС=Р1-11-10*
:Л?С.`1,0*
:ВС=Р1-11-11*
:Л?С.`1,0*
:ВС=Р1-11-12*
:Л?С.`1,0*
:ВС=Р1-11-13*
:Л?С.`0,01*
...........................................
======================================================
Если номер ФПН252 больше 7 знаков -АВК не пропустит, сотрите последние цифры, если такие будут.
Проверим фрагмент, вставим в АВК. (как это делать тема отдельная, думаю все знают).
Теперь расценки набираются очень-очень быстро.
Скопируем себе в папку Doc этот файл на будущее.
Обращаю ваше внимание на то, что мы не ищем наиболее эффективных путей для достижения нашей цели, мы преследуем кроме реализации нашей задачи еще одну цель - максимум освоить и применить на практике полезности Офиса и Винды.
Медитируем, есть вопросы -задавайте, нет -поедем дальше.
На следующем уроке мы будем знакомиться с макросами ЕКСЕЛЬ и учиться создавать свои функции.
Змінено: Valentin, January 26 2009 13:01
Прежде чем начать манипуляции с нашей базой данных, нам нужно научиться управлять командами ЕКСЕЛЯ.
С работой на листах многие не просто знакомы. Применение формул, функций встроенных в ЕКСЕЛЬ не раз приходилось применять в повседневной работе. А вот создавать свои функции и подчинать ЕКСЕЛЬ своим требованиям пожалуй умеют не многие (кто тему знает, может урок пропустить).
Итак МАКРОСЫ.
Приставка "макро" часто встречается в в техническом и научном языке, происходит от греческого слова - "большой". В ЕКСЕЛЬ -это большая команда, состоит из набора небольних команд.
Макрос -это даже минипрограмма, составленная из определенных команд.
ЕКСЕЛЬ оснащен механизмом, который создает сам макросы.
Он записывает Ваши действия в виде команд VBA (язык понятен приложениям М-офиса), которые впоследствии можно воспроизвести.
Настроим наш ЕКСЕЛЬ на работу с макросами.
Открываем -меню >> сервис >> макрос >> безопасность >> устанавливаем: средняя.
Сохраняем книгу под именем "Урок-3".
Установим для удобства панель инструменов - меню >> вид >> панель инструментов >> Visual Basic. Попробуем записать наш первый макрос.
На панели нажимаем красную кнопочку (записать макрос), имя макроса оставим как есть, ОК и как в магнитофоне -тишина, идет запись.
Вы полняем действия: немного раздвинем столбик B, в ячейку A1 пишим 1, в В1 - Иванов, С1 -муж, D1- 1990. Нажимаем на плавающем окошке - остановить.
Макрос записан. Сотрем все наши записи и попробуем вызвать джина из бутылки.
Нажимаем зеленую стрелку - выполнить макрос - внизу окна -надпись - находится в: выберем- Урок 3, т.е. в нашей книге. Выбираем Макрос1 -жмем -выполнить. Макрос воспроизвел наши записи.
Где же хранятся команды, которые записал макрос??
На панеле жмем- редактор Visual Basic - и попадаем за кулисы, в окно проекта VBA или в среду программирования, где и хранятся все формы книги, листы и пр.
Слева в верхнем окне проекта видим папку MODULES, открываем, в ней Module1- это наш модуль с нашим макросом:
Sub Макрос1()
'
' Макрос1 Макрос
' Макрос записан 31.01.2009 (Valentin)
'
Из записей наших действий все становится понятным. Если мы не знаем каких-то команд VBA, мы можем выполнить интересующие нас действия и ЕКСЕЛЬ сам запишет нужные команды.
Sub Макрос1() -начало макроса или программы, End Sub -окончание.
Но тупое повторение наших действий вряд ли пригодится в нашей программе.
Изменим наш макрос. Зададим условие: Если в ячейке A1 установить значение 2, то B1 должна быть заполнена записью "Иванов Иван Иванович", размер ячейки B1 при этом увеличится ну раза в два. Пишем:
Sub Макрос1()
'
' Макрос1 Макрос
' Макрос записан 31.01.2009 (Valentin)
'
'
Range("A1").Select
If ActiveCell.Value = "2" Then 'если в ячейке А значение 2 тогда:
Columns("B:B").ColumnWidth = 35
Range("B1").Select
ActiveCell.FormulaR1C1 = "Иванов Иван Иванович"
Range("C1").Select
ActiveCell.FormulaR1C1 = "муж"
Range("D1").Select
ActiveCell.FormulaR1C1 = "1990"
Else: 'иначе
Columns("B:B").ColumnWidth = 17.86
Range("A1").Select
ActiveCell.FormulaR1C1 = "1"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Иванов"
Range("C1").Select
ActiveCell.FormulaR1C1 = "муж"
Range("D1").Select
End If 'конец условия
End Sub
Ставим в А1 -2, запускаем, техника нас слушается.
Запускать макрос (программу) через меню панели -очень неудобно.
Создадим кнопочку для запуска макроса.
б)Кнопки.
Выберем в нашей панельке "элементы управления".
Нажмем "Кнопка" и где-то в районе F2 нашего листа нарисуем курсором прямоугольник.
Отпустив кнопку мішки -увидим появившуюся кнопку.
Кликаем по ней два раза, попадаем в окно проекта: Private Sub CommandButton1_Click()
End Sub
Вставим в тело программы имя нашего макроса: Макрос1 Private Sub CommandButton1_Click()
Макрос1
End Sub
Вернемся к нашей кнопке и выйдем из режима конструктора.
Теперь нажав на кнопку вызывается наша программа Макрос1.
Думаю, сегодня мы достаточно ознакомились с азами программирования в ЕКСЕЛЬ.
Настало время приступить непосредственно к программированию нашей программы.
На следующем уроке этим и займемся.
Змінено: Valentin, February 01 2009 18:17
Valentin можно я добавлю чуток....
если ктото захочет кнопку на панель управления поставить,
панель управления--настройки--
вкладка панели инструментов--создать--даем название панели--размещаем панель на панели управления--
вкладка команды--макросы--настраиваемая кнопка--левой клавишей мышки нажать на кнопке и перетащить во вновь созданную панель--кликаем на кнопке правой клавишей мышки--редактируем название кнопки(по желанию)--в самом низу назначить макрос--выбираем созданный нами макрос
Изучив вкратце на предидущем уроке азы работы с ЕКСЕЛЬ, можем начинать воплощать в жизнь свой проект.
Открываем ЕКСЕЛЬ, сохраняем книгу как MAVK1U.xls или MAVK1R.xls или для двух языков сразу: MAVK1.xls в папку нашей программы MAVK5_280_UKR или рус или просто MAVK5, кто какую создал.
Учитывая, что лист ЕКСЕЛЬ вмещает более 65 тыс. строк нашу базу можем разместить на одном листе, чтобы не обращаться дополнительно к файлу базы.
Для этого на ярлычке "Лист1" правой кнопкой мыши >> переименовать - "Baza".
Так будет называться наш лист, на который мы перенесем нашу базу расценок.
Перенести данные можно разными способами, один из простейших: файл базы P.dat переименуем в P.txt, откроем (блокнот), выделим весь текст, скопируем и вставим в ячейку А1 листа "Baza".
Столбец B (наименование) можно раздвинуть, чтобы удобно было читать тексты, потом весь его выделить >> правой кнопкой мыши >> формат ячеек >> Выравнивание >> по горизонтали (по левому краю -отступ); по вертикали (по верхнему краю) >> отображение (отметим -переносить по словам)>> ОК.
База готова к употреблению.
"Лист2" переименуем в "ЛС" -здесь мы будем строить локальную смету.
Первую строку разобъем на колонки, т.е. присвоим ячейкам наименования:
--------------------------------------------------------------------------------- Шифр Наименование работ Ед Всего Зарпл МАШ З_МАШ ТЗРАБ ТЗМАШ К1 К2 Количество
Прямые затраты Зарпл МАШ З_МАШ ТЗО ТЗМО K1 K2 ОБЩ_ПР ТЗ_ОП ЗП_ОП МАТ Всего с коеф. Зарпл
с коеф. МАШ с коеф. З_МАШ с коеф. ТЗРАБ с коеф. ТЗМАШ с коеф. МАТ с коеф. Разряд
-----------------------------------------------------------------------------------
Эту строку можно скопировать и вставить в А1. Предназначение каждой обсудим в процессе построения каркаса нашей программы.
Столбец B (наименование)подготовим как и в "Baza".
Всю строку 1 раздвигаем немного по-выше и форматируем наименования столбцов в удобный для визуального восприятия формат.
Ячейки А1-К1 -закрашиваем в серый цвет (разбиваем сектора), L1- желтый, М1-Т1-салатовый, U1-W1 -кирпичный, Х1 -салатовый, Y1-AE1-серый, AF1 - малиновый.
Цвета могут быть разными, на свой вкус.
Выделяем столбцы D-L, далее меню >> данные >> группа и структура >> группировать.
Группируем N-T, потом V-AF.
Закрываем группировку (слева вверху левее А появились кубики 1 и 2, нажимайте на них, смотрите).
Когда столбики свернуты, мы должны видеть только A, B, C, L, M ,U.
Чтобы составить локальную смету нам необходимо посчитать прямые затраты т.е. составляющие 11 граф сметной строки и посчитать "накрутки" т.е. сделать концовку.
В строке 2 будем выполнять все расчеты для сметной строки, а потом формулы этой строки копировать и таким образом накапливать строки сметы.
Поехали:
В ЕКСЕЛЕ есть много интересных функций, одна из них, которая нам очень поможет: ВПР.
Эта ф-ция "ищет значение в крайнем левом столбце таблицы и возвращает значение ячейки, находящейся в указанном столбце той же строки. По умолчанию таблица должна быть отсортирована по возростанию."
Т.е. если мы в ячейке А2 напишим "Р1-1-1", то в крайнем левом столбце таблицы (а она у нас на листе "Baza" имеет диапазон А:К) ф-ция будет искать "Р1-1-1" и возвращать значение указанной ячейки в указанном столбце той же строки (для наименования у нас служит столбец 2, для ед.измерения 3 и т.д.).
Пишем: =ВПР(A2;Baza!A:K;2;1)
Последняя 1 означает ИСТИНА.
Если мы скопируем эту формулу и вставим ее в ячейку B2, то задав условия для поиска в ячейке А2 - "Р1-1-1", мы должны получить значение ячейки 2-го столбца той же строки, т.е. наименование.
Вставляем: получаем результат.
Чтобы наша ф-ция не заблудилась в дебрях нашей базы, записи нужно отсортировать.
Переходим в базу, становимся на А1, нажимаем сортировку от А до Я и продолжаем заполнять формулами нашу сметную строку.
=ВПР(A2;Baza!A:K;3;1) - для С2
=ВПР(A2;Baza!A:K;4;1) - для D2
=ВПР(A2;Baza!A:K;5;1) - для E2
=ВПР(A2;Baza!A:K;6;1) - для F2
=ВПР(A2;Baza!A:K;7;1) - для G2
....................
=ВПР(A2;Baza!A:K;11;1) - для K2
Обращаю внимание: не копируйте формулы из строки формул ЕКСЕЛЯ, т.к. он автоматически присваивает новые значения.
Вставив последнюю формулу в столбец К2 (серый сектор) - мы получим последнее значение строки нашей базы.
Поиграйтесь, измените искомые расценки в А2, значения вашей строки должны изменяться во всех ячейках.
Эта операция напоминает Ctrl+F3 в АВК.
Переходим в столбец М (прямые затраты). Значение этой ячейки зависит от "Всего" и "Кол", а именно: =D2*L2
и далее в салатовом секторе заполняем: =Е2*L2.... и т.д. до столбика R (ТЗ машинистов)
в S2 и T2 (К1,К2) значения не меняем и пишем соответственно: =J2 =K2
Соберите группировку строк.
Так нам удобнее будет работать.
Теперь если вводить количество, результат будет меняться.
Я подробно не комментировал вычисления, т.к. они просты и по заглавиям столбцов все д.б. понятно.
Наша программа уже позволяет проверять расценки инвесторских смет на соответствие с нормами ДБН.
Сохраните книгу в папке "Урок4".
Медитируем.
На следующем уроке будем расчитывать ОПР для сметной строки.
Змінено: Valentin, February 07 2009 18:29
На прошлом уроке мы создали макет сметной строки.
Повторим назначение отмеченных цветами секторов.
1. А-К - серый - исходные данные для расчета сметной строки (берется из БД).
2. L -желтый - количество
3. М-Т -салатовый - данные для 11-графки с учетом количества.
4. U-W- ОПР - кирпичный - расчитывается в каждой сметной строке для учета ОПР в концовке сметы.
5. Х- салатовый - расчет материалов в составе расценки салатового сектора.
6. Y-AE - серый - данные для 11-графки с учетом коеффициентов
7. AF - малиновый - расчитывается в каждой сметной строке для учета среднего разряда в концовке сметы.
Займемся П4. Для концовки нам необходимо в каждой строке считать ОПР и их составляющие: трудозатраты в составе ОПР и Зарплата в составе ОПР.
Как известно из ДБН ОПР считаются по формуле:
=======================================
ОПР= ЗТО+СОЦ+ОСТ
где
ЗТО - 1-й блок = зарплата в составе ОПР
СОЦ - 2-й блок = соцотчисления
ОСТ - 3-й блок = остальные статьи расходов
ЗТО в ОПР = З7Р*TЗО
TЗО в ОПР = ТЗ*К1
где
З7Р - средняя з-та 7-го разряда
TЗО - трудозатраты в составе ОПР
ТЗ - трудозатраты в прямых затратах
К1 - понижающий коеф. для ТЗ в составе ОПР
СОЦ = (ЗП+ЗТО)*КСОЦ
где
ЗП -зарплата в прямых затратах
КСОЦ - % начислений на соцнужды
ОСТ = ТЗ*К2
где
К2 - показатель на покрытие остальных расходов ОПР
========================================================
Теперь создадим функцию. Мы уже знакомы с процедурами (макросами). Функции - те же процедуры, только им для работы нужны данные ( параметры) и тогда они возвратят значения.
Судя по колонкам кирпичного сектора нам необходимо получить 3 значения:
ОБЩ_ПР = (ОПР) , ТЗ_ОП = (трудозатраты в ОПР) , ЗП_ОП = (зарплата в ОПР).
Значит и функций придется писать аж 3.
Открываем проект, создаем модуль (место куда будем писать ф-ции, при изучении макросов мы знакомиоись с Модуль1).
Пишем:
========================================= Function ЗТО(TЗО, З7Р)
ЗТО = З7Р * TЗО
End Function
Function TЗО(TЗ, К1)
TЗО = TЗ * К1
End Function
======================================
Теперь на в колонке ТЗ_ОП ячейке V2 вставляем нашу ф-цию:
меню >> вставка >> функция.. >> категория -определенная пользователем >> выбираем ТЗО >> 1-параметр указываем на (Q2+R2) -это ТЗ +ТЗМ , 2- параметр -указываем на S2 (К1). В ячейке возвратилось значение.
Проверим ТЗО в нашем документе посчитанном на АВК -увидим тот же результат.
В ячейку V2 пока вставим =ЗТО(V2;21,85)
Пока, потому, что на зарплату 7-го разряда 21,85грн/час могут влиять К к з-те, которые мы определим в концовке.
Далее ф-ция СОЦ:
============================================ Function СОЦ(ЗТОБ, КСОЦ)
СОЦ = ЗТОБ * КСОЦ
End Function
и ОСТ:
Function ОСТ(ТЗ, К2)
ОСТ = ТЗ * К2
End Function
======================================
Теперь в ячейке ОПР пишем:
=ОКРУГЛ(W2+СОЦ((N2+P2+W2);0,38918)+ОСТ((Q2+R2);T2);0)
т.е. результат округляем до 0 (как в АВК).
На следующем уроке будем расчитывать разряд, закончим расчеты по сметной строке и перейдем к концовке.
Змінено: Valentin, February 16 2009 13:46
На прошлом уроке мы решали задачи по расчету ОПР комбинировано: часть -средствами встроенных функций,а часть -при помощи своих, написанных на VBA.
Сегодня мы постараемся всю нашу работу сделать только при помощи встроенных функций.
ИТАК, впомним как расчитывается разряд.
Если в месяце Х -час, а среднемесячная з-та =2300грн, то среднечасовая з-та =2300/Х.
Но этот показатель отражает цену разряда =3,8.
В марте 2008г. в ЦО была опубликована тарифная сетка для продолжительности рабочего времени 167,58час в месяц.
Здесь разряд 3.8 = 2300/167,58 = 13,72грн
В этом году среднемесячная продолжительность составляет 167.00 час.
Исходя из формулы расчета разряд 3.8. для 2009г = 2300/167,00 = 13,77грн.
Чтобы расчитать тарифную сетку для всех разрядов при СПРВ 167 час, нужно К=167,58/167=1,003473054 умножить на показатели з-ты 2008г.
Чтобы не вписывать в код программы таблицу тарифной сетки, которая может измениться, дополним нашу программу листом "RD", т.е. Разряд.
В ячейку А1 вставляем записи:
========================== ТЗ2009 СРМЗ-ТА ТЗ2008 КТЗ
==========================
где
-ТЗ2009 - (трудозатраты в 2009г)
-СРМЗ-ТА - (среднемесячная зарплата)
-ТЗ2008 - (трудозатраты в 2008г)
-КТЗ - (коеффициент трудозатрат)
В ячейку А2 -значения:
========================== 167,00 2300 167,58 КТЗ
==========================
Ячейке D2 присвоим имя: меню >> вставка >> имя >> присвоить >> КТЗ >> ОК
В ячейку А3 - заглавия столбцов для тарифной сетки:
========================== З-та 2009 Разряд З-та 2008
==========================
Протягиваем формулы от А4 до А74
========================== =С4*КТЗ
=С5*КТЗ
..........
..........
=С74*КТЗ
========================
Теперь изменяя значение А2 (ТЗ) -изменяются все значения з-ты 2009г.
Чтобы внедрить зависимость з-ты от среднемесячной з-ты, нужно составить таблицу межразрядных коеффициентов, но эту задачу вы сможете решить сами.
Переходим в нашу сметную строку и в ячейку AF2 вставляем знакомые формулы:
==========================================
Теперь, меняя расценки в А2 в AF2 будет определятся разряд, а если нет ТЗ ( т.е. работают одни машины) то AF2=0
Поиграйтесь с расценками, протестируйте механизм определения разряда, будут вопросы, задавайте, нет - пойдем дальше.
Змінено: Valentin, February 24 2009 13:41
Строка №2 листа ЛС занята нами под сметную строку.
Подготовим строку 3 для подведения итогов.
Выделим А3-AF3 закрасим желтым (условно), в ячейке В3 напишем "ИТОГО:" .
Далее скопируем:
============================================================= Всего прямых затрат: грн
в том числе:
материалы: грн
заработная плата (всего): грн
Общепроизводственные расходы: грн
трудоемкость в общепроизводственных расходах: чел-час
заработная плата в общепроизводственных расходах: грн
Всего по смете: грн
Сметная трудоемкость: чел-час
Сметная заработная плата:
Коеффициенты:
К трудозатратам рабочих-строителей и монтажников К=
К трудозатратам машинистов К=
К продолжительности работы машин К=
К объему К=
Среднемесячная зароботная плата грн/К=
К материалам К=
================================================================
и вставим в В4.
В ячейке D19 -установим значение среднемесячной зарплаты по умолчанию =2300.
В Е19 - D19/2300 -формула определения К к з-те.
В Е15 установим 1 и присвоим имя - ТЗРМ
В Е16 установим 1 и присвоим имя - ТЗМ
В Е17 установим 1 и присвоим имя - РМАШ
В Е18 установим 1 и присвоим имя - КОБ
В Е19 присвоим имя - КЗТ
В Е20 установим 1 и присвоим имя - КМАТ
В М4 скопируем и вставим следующие ссылки:
============================================= =M3
=P4
=F4+H4
=U4
=V4
=W4
=M4+M8
=Q4+M9+R4
=M7+M10
===============================================
В N4 скопируем и вставим следующие ссылки:
============================================== =N3 =O3 =P3 =Q3 =R3 =U3 =V3 =W3 =X3
==============================================
Из наименований столбцов легко разобраться ссылками и формулами.
Зависимость значений количества от К объема изменит формулы в ячейках М2-R2.
Скопируйте измененные формули и вставьте в М2:
==================================================== =D2*L2*КОБ =E2*L2*КОБ =F2*L2*КОБ =G2*L2*КОБ =H2*L2*КОБ =I2*L2*КОБ
===================================================
Изменятся формулы для расчета ОПР в ячейках U-W.
Скопируйте измененные формули и вставьте в U2:
=================================================== =ОКРУГЛ(W2+СОЦ((Z2+AB2+W2);0,38918)+ОСТ((AC2+AD2);T2);0) =TЗО((AC2+AD2);S2) =ЗТО(V2;21,85*КОБ)
===================================================
А также все остальные в ячейках Х-AF
Скопируйте измененные формули и вставьте в X2:
=================================================== =M2-N2-O2 =Z2+AA2+AE2 =N2*КЗТ*ТЗРМ =(O2-P2)*РМАШ+AB2 =P2*КЗТ*ТЗМ*РМАШ =Q2*ТЗРМ =R2*ТЗМ*РМАШ =X2*КМАТ
==================================================
Проверьте влияние Коеффициентов на составляющие цены сметной строки, сверьте их с любым ПК, возникнут вопросы-задавайте.
Теперь нам осталось научиться искать расценки в базе, вставлять их в наш макет сметы и посчитать результаты. После всего - вывод на печать.
На следующем уроке займемся поиском и вставкой строк в смету.
Змінено: Valentin, March 02 2009 21:44
Учитывая, что вопросов по построению программы за весь период не было можно сделать заключение, что пользователю все ясно по исходному коду без комментариев.
Поэтому выкладываю весь готовый проект.
При тестировании формулы, рассматриваемые выше, немного изменились.
При желании пользователь сможет сам создать документы ССР, ДЦ КБ2,
КБ3
Запустив файл MAVK1U.xlt его нужно "сохранить как.." книгу ЕКСЕЛЬ под любым именем в папке программы, а потом уж работать.
Valentin приєднав файл: mavk5_280_ukr.zip
Змінено: Valentin, March 18 2009 14:11
Я работаю в Строительных технологиях, поэтому немного трудновато разобраться, может такую программу можно создать и для Ст? А вообщем-то очень интересно и познавательно, но самое главное - практика!!!
Ни с АВК ни с СТС эта программа общего ничего не имеет. Ее можно назвать и Мини АС4.
Была попыткка показать сметчикам, что можно создавать свои программки просто в ЕКСЕЛЕ, призводить расчеты в соответствии с ДБН печатать на бланках тех ПК, которые требует Заказчик.
Как видно из работы программки сметы можно набирать в считанные минуты и сразу же на печать.:)
![[off]](../infusions/shoutbox_panel/sh_offline.png "Offline")
21.11.2024
