Як скласти кошторис в Excel

У роботах по плануванню і проектування важливу роль становить кошторис. Без неї не вийде запустити жоден серйозний проект. Особливо часто до складання кошторису вдаються в будівельній галузі. Звичайно, правильно скласти кошторис - це справа непроста, яке під силу лише фахівцям. Але і вони змушені вдаватися до різного програмного забезпечення, часто платному, для виконання цього завдання. Але, якщо у вас на ПК встановлено екземпляр Excel, то цілком реально скласти якісну кошторис і в ньому, без покупки дорогого узконаправленного ПО. Давайте розберемося, як це зробити на практиці.

зміст

Складання найпростішої кошторису витрат
Питання та відповіді

Складання найпростішої кошторису витрат

Кошторис витрат - це повний перелік всіх витрат, які понесе організація при здійсненні конкретного проекту або просто за певний період часу своєї діяльності. Для розрахунків застосовуються спеціальні нормативні показники, які, як правило, є у відкритому доступі. На них і повинен спиратися фахівець при складанні даного документа. Також слід зазначити, що кошторис складається на початковому етапі запуску проекту. Поетом до даної процедури потрібно поставитися особливо серйозно, так як вона є, по суті, фундаментом проекту.

Нерідко кошторис розділяється на дві великі частини: витрати на матеріали і витрати на виконання робіт. В самому кінці документа ці два види витрат підсумовуються і обкладаються ПДВ, якщо підприємство, яке є підрядником, зареєстрована платником цього податку.

Етап 1: початок складання

Давайте спробуємо скласти найпростішу кошторис на практиці. Перед тим, як до цього приступити, потрібно отримати технічне завдання від замовника, на підставі якого ви будете її планувати, а також озброїтися довідниками з нормативними показниками. Замість довідників можна також використовувати інтернет-ресурси.

Отже, почавши складання найпростішої кошторису, перш за все, робимо її шапку, тобто, найменування документа. Назвемо його «Кошторис на роботи». Отцентровивать і форматувати назва поки не готова таблиця не будемо, а просто помістимо його в верхній частині листа.

Відступивши один рядок, робимо каркас таблиці, яка і буде основною частиною документа. Вона складатиметься з шести стовпців, яким дамо імена «№ п / п», «Найменування», «Кількість», «Одиниця виміру», «Ціна», «Сума». Розширюємо межі осередків, якщо назви колонок не поміщаються в них. Виділяємо комірки, що містять дані імена, перебуваючи у вкладці «Головна», тиснемо на розташовану на стрічці в блоці інструментів «Вирівнювання» кнопку «Вирівняти по центру». Потім натискаємо по значку «Жирний», який знаходиться в блоці «Шрифт», або просто набираємо поєднання клавіш на клавіатурі Ctrl + B. Таким чином, ми надаємо найменувань стовпців елементи форматування для більш наочного візуального відображення.

Потім окреслюємо межі таблиці. Для цього виділяємо передбачувану область табличного діапазону. Можна не переживати, що захопіть занадто багато, так як потім ми все одно будемо проводити редагування.
Після цього, перебуваючи все на тій же вкладці «Головна», клацаємо по трикутнику, який розташований праворуч від значка «Кордон», розміщеного в блоці інструментів «Шрифт» на стрічці. У списку вибираємо варіант «Все кордону».

Як бачимо, після останньої дії весь виділений діапазон був розділений кордонами.

Етап 2: складання Розділу I

Далі приступимо до складання першого розділу кошторису, в якому будуть розташовуватися витрати на витратні матеріали при виконанні робіт.

У першому рядку таблиці пишемо найменування «Розділ I: Витрати на матеріали». Дана назва не поміститься в одну клітинку, але не потрібно розсовувати кордону, адже після ми їх просто приберемо, а поки залишимо так, як є.

Далі заповнюємо саму таблицю кошторису найменуваннями матеріалів, які планується використовувати для реалізації проекту. В даному випадку, якщо назви не поміщаються в осередку, то розсовуємо їх. У третій стовпець вносимо кількість конкретного матеріалу, необхідне для виконання заданого обсягу робіт, згідно з діючими нормами. Далі вказуємо його одиницю виміру. У наступній колонці пишемо ціну за одиницю. Стовпець «Сума» не чіпаємо, поки не заповнимо всю таблицю вищевказаними даними. У нього значення будуть виводитися за допомогою формули. Також не чіпаємо перший стовпець з нумерацією.

Тепер розташуємо дані з кількістю і одиницями вимірювання по центру осередків. Виділяємо діапазон, в якому ці дані розташовані, і клацаємо по вже знайомому нам значку на стрічці «Вирівняти по центру».

Далі виконаємо нумерацію введених позицій. У осередок шпальти «№ п / п», яка відповідає першому найменуванню матеріалу, вводимо цифру «1». Виділяємо елемент листа, в який було введено дане число і встановлюємо покажчик на його нижній правий кут. Він трансформується в маркер заповнення. Затискаємо ліву кнопку миші і тягнемо вниз включно до останнього рядка, в якій розташовується найменування матеріалу.

Але, як бачимо, вічка не були пронумеровані по порядку, так як у всіх них розташоване число «1». Щоб змінити це, натискаємо на піктограму «Параметри заповнення», яка знаходиться внизу виділеного діапазону. Відкривається список варіантів. Переставляємо перемикач в позицію «Заповнити».

Як бачимо, після цього нумерація рядків була виставлена по порядку.

Після того, як всі найменування матеріалів, які будуть потрібні на реалізацію проекту, внесені, переходимо до розрахунку суми витрат по кожному з них. Як не важко здогадатися, розрахунок буде представляти множення кількості на ціну по кожній позиції окремо.
Встановлюємо курсор в осередок шпальти «Сума», яка відповідає першому найменуванню з переліку матеріалів в таблиці. Ставимо знак «=». Далі в тому ж рядку натискаємо по елементу листа в стовпці «Кількість». Як бачимо, його координати тут же відображаються в осередку для виведення вартості матеріалів. Після цього з клавіатури ставимо знак «помножити» (*). Далі в тому ж рядку клацаємо по елементу в стовпці «Ціна».
У нашому випадку вийшло наступна формула:
=C6*E6

Але у вашій конкретній ситуації вона може мати і інші координати.

Для виведення на екран результату розрахунку тиснемо на клавішу Enter на клавіатурі.

Але ми вивели результат тільки для однієї позиції. Звичайно, за аналогією можна ввести формули і для інших комірок стовпчика «Сума», але існує більш легкий і швидкий шлях за допомогою маркера заповнення, про який ми вже говорили вище. Ставимо курсор в нижній правий кут комірки з формулою і після перетворення його в маркер заповнення, затиснувши ліву кнопку миші, тягнемо вниз до останнього найменування.

Як бачимо, загальна вартість по кожному окремому матеріалі в таблиці розрахована.

Тепер порахуємо підсумкову вартість всіх матеріалів разом узятих. Пропускаємо рядок і в першій клітинці наступного рядка робимо запис «Разом за матеріалами».

Потім з затиснутою лівою кнопкою миші виділяємо діапазон в стовпці «Сума» від першого найменування матеріалу до рядка «Разом за матеріалами» включно. Перебуваючи у вкладці «Головна» виробляємо клік по значку «Автосумма», який розташований на стрічці в блоці інструментів «Редагування».

Як бачимо, розрахунок загальної суми витрат на покупку всіх матеріалів для виконання робіт проведений.

Як ми знаємо, грошові вираження, зазначені в рублях, прийнято використовувати з двома десятковими знаками після коми, маючи на увазі не тільки рублі, а й копійки. У нашій таблиці значення грошових сум представлені виключно цілими числами. Для того, щоб виправити це, виділяємо все числові значення стовпців «Ціна» і «Сума», включаючи підсумковий рядок. Виробляємо клік правою кнопкою миші по виділенню. Відкривається контекстне меню. Вибираємо в ньому пункт «Формат ячеек ...».

Відбувається запуск вікна форматування. Переміщаємося у вкладку «Число». У блоці параметрів «Числові формати» встановлюємо перемикач в позицію «Числовий». У правій частині вікна в полі «Число десяткових знаків» повинна бути встановлена цифра «2». Якщо це не так, то вводимо потрібну цифру. Після цього клацаємо по кнопці «OK» в нижній частині вікна.

Як бачимо, тепер в таблиці значення ціни і вартості відображаються з двома десятковими знаками.

Після цього трохи попрацюємо над зовнішнім виглядом даної частини кошторису. Виділяємо рядок, в якій знаходиться найменування «Розділ I: Витрати на матеріали». Розташувавшись у вкладці «Головна», тиснемо на кнопку «Об'єднати і помістити в центрі» в блоці «Вирівнювання на стрічці». Потім натискаємо по вже знайомому нам значку «Жирний» в блоці «Шрифт».

Після цього переходимо до рядка «Разом за матеріалами». Виділяємо її всю до кінця таблиці і знову тиснемо на кнопку «Жирний».

Потім знову виділяємо комірки цього рядка, але на цей раз в виділення не включаємо елемент, в якому розташована загальна сума. Кількома на трикутник праворуч від кнопки на стрічці «Об'єднати і помістити в центрі». З випадного переліку дій вибираємо варіант «Об'єднати осередки».

Як бачимо, елементи листа об'єднані. На цьому роботу з розділом витрат на матеріали можна вважати завершеною.

урок: Форматування таблиць в Ексель

Етап 3: складання Розділу II

Переходимо до оформлення розділу кошторису, який буде відображати витрати на виконання безпосередніх робіт.

Пропускаємо одну сходинку і на початку наступного пишемо найменування «Розділ II: вартість робіт».

У новому рядку в стовпці «Найменування» записуємо вид робіт. У наступній колонці вносимо обсяг виконаних робіт, одиницю виміру і ціну одиниці виконаної роботи. Найчастіше одиницею виміру виконаних будівельних робіт є квадратний метр, але іноді бувають винятки. Таким чином, заповнюємо таблицю, вносячи всі процедури, які виконував підрядник.

Після цього проводимо нумерацію, підрахунок суми по кожному найменуванню, обчислюємо загальний підсумок і виробляємо форматування точно так же, як ми це робили для першого розділу. Так що додатково на зазначених завданнях зупинятися не будемо.

Етап 4: розрахунок загальної величини витрат

На наступному етапі нам належить підрахувати загальну величину витрат, яка включає вартість матеріалів і праці працівників.

Пропускаємо рядок після останнього запису і пишемо в першій клітинці «Разом за проектом».

Після цього виділяємо в даній сходинці осередок в стовпці «Сума». Не важко здогадатися, що підсумкова сума по проекту буде обчислюватися шляхом складання значень «Разом за матеріалами» і «Разом за вартістю робіт». Тому в виділеної осередку ставимо знак «=», а потім натискаємо по елементу листа, який містить значення «Разом за матеріалами». Потім встановлюємо з клавіатури знак «+». Далі клацаємо по осередку «Разом за вартістю робіт». У нас вийшла формула такого виду:
=F15+F26

Але, природно, для кожного конкретного випадку координати в цій формулі будуть мати власний вид.

Для виведення загальної суми витрат на лист клацаємо по клавіші Enter.

Якщо підрядник є платником податку на додану вартість, то внизу додаємо ще два рядки: «ПДВ» і «Разом за проектом включаючи ПДВ».

Як відомо, розмір ПДВ становить в Росії 18% від бази оподаткування. У нашому випадку базою оподаткування є сума, яка записана в рядку «Разом за проектом». Таким чином, нам потрібно буде помножити цю величину на 18% або на 0,18. Ставимо в осередку, яка знаходиться на перетині рядка «ПДВ» і шпальти «Сума» знак «=». Далі натискаємо по осередку зі значенням «Разом за проектом». З клавіатури набираємо вираз «* 0,18». У нашому випадку виходить наступна формула:
=F28*0,18

Клацаємо по клавіші Enter для підрахунку результату.

Після цього нам потрібно буде розрахувати загальну вартість робіт, включаючи ПДВ. Існує кілька варіантів розрахунку даної величини, але в нашому випадку найпростіше буде просто скласти загальну вартість робіт без ПДВ з сумою ПДВ.
Отже, в рядку «Разом за проектом включаючи ПДВ» в стовпці «Сума» складаємо адреси осередків «Разом за проектом» і «ПДВ» тим же способом, яким ми проводили підсумовування вартості матеріалів і робіт. Для нашої кошторису виходить наступна формула:
=F28+F29

Тиснемо на кнопку ENTER. Як бачимо, ми отримали значення, яке вказує, що загальні витрати на реалізацію проекту підрядником, включаючи ПДВ, складуть 56533,80 рубля.

Далі зробимо форматування трьох підсумкових рядків. Виділяємо їх повністю і клацаємо по іконці «Жирний» у вкладці «Головна».

Після цього, щоб підсумкові значення виділялися серед іншої інформації кошторису, можна збільшити шрифт. Не знімаючи виділення у вкладці «Головна», натискаємо по трикутнику праворуч від поля «Розмір шрифту», яке розташоване на стрічці в блоці інструментів «Шрифт». У списку вибираємо величину шрифту, яка більше поточної.

Потім виділяємо все підсумкові рядки до стовпчика «Сума». Перебуваючи у вкладці «Головна» натискаємо по трикутнику, який розташований праворуч від кнопки «Об'єднати і помістити в центрі». У випадаючому списку вибираємо варіант «Об'єднати за рядками».

урок: Формула ПДВ в Ексель

Етап 5: завершення оформлення кошторису

Тепер для повного завершення оформлення кошторису нам залишилося тільки зробити деякі косметичні штрихи.

Перш за все, приберемо зайві рядки в нашій таблиці. Виділяємо зайвий діапазон комірок. Переходимо у вкладку «Головна», якщо в даний момент відкрита інша. У блоці інструментів «Редагування» на стрічці клацаємо по іконці «Очистити», яка має зовнішній вигляд гумки. У списку вибираємо позицію «Очистити формати».

Як бачимо, після цього дії все зайві рядки були видалені.

Тепер повертаємося до найпершого, що ми робили при оформленні кошторису - до найменування. Виділяємо відрізок рядки, де знаходиться ім'я, по довжині рівний ширині таблиці. Виробляємо клацання по знайомій нам клавіші «Об'єднати і помістити в центрі».

Потім, не знімаючи виділення з діапазону, тиснемо на значок «Жирний».

Закінчуємо форматування назви кошторису, клікнувши по полю розміру шрифту, і вибравши там величину більшу, ніж ми встановлювали раніше для підсумкового діапазону.

Після цього оформлення кошторису в Excel можна вважати завершеним.

Ми розглянули приклад складання найпростішої кошторису в програмі Excel. Як бачимо, даний табличний процесор має в своєму арсеналі всі інструменти для того, щоб відмінно впоратися з вказаною завданням. Більш того, при необхідності в цій програмі можна складати і набагато більш складні кошторису.