Портал сметчиков

---

Урок 4 - Каркас приложения.

а) Сметная строка

Изучив вкратце на предидущем уроке азы работы с ЕКСЕЛЬ, можем начинать воплощать в жизнь свой проект.
Открываем ЕКСЕЛЬ, сохраняем книгу как 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".
Медитируем.

На следующем уроке будем расчитывать ОПР для сметной строки.