Особливість Excel для складання кошторисів та розрахунків

Ми постійно стикаємось з однією особливістю, на яку мало хто звертає увагу. Ви в більшості використовуєте Excel для складання кошторисів (особливо зараз, коли плануєте роботу в наступному році). І ще багато об’єднань використовують Excel для нарахувань, формування квитанцій співвласникам.

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

В чому власне проблема з таблицями Excel?

В тому, що ви бачите в комірках не ті цифри, які насправді є після обчислень.

Натисніть правою кнопкою мишки по комірці, де відображені ваші цифри, та оберіть у випадаючому меню пункт “Формат клітинок”.

З’явиться діалогове вікно “Формат клітинок” в якому ви можете змінювати кількість десяткових знаків після коми:

Кількість знаків після коми також можна регулювати кнопками в меню:

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

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

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

Приклад кошторису та розрахунку внеску на утримання будинку

Щоб скласти кошторис, вам необхідно знати:

  • статті майбутніх витрат ОСББ;
  • суми витрат в місяць по кожній статті;
  • загальну житлову площу (площу всіх квартир) в будинку;

Спробуємо порахувати внесок на утримання будинку в якого площа всіх квартир 7241,24 кв.м., п’ять статей майбутніх витрат на утримання будинку. Заповнимо ці дані в таблицю Exlel, вставимо формули і порахуємо розмір внеску на утримання будинку. Ось два варіанти розрахунку внеску на утримання:

Варіант 1

Варіант 2

При розрахунку складових внеску сума витрат по статті ділиться на загальну площу квартир. Як бачите числа в обидвох варіантах однакові, проте результат відрізняється. В чому тоді різниця? І який варіант правильний?

Давайте візьмемо калькулятор і додамо складові внеску: 2,02+1,46+0,93+0,72+0,32=5,45

Чому в першому варіанті вийшло 5,43? Отже правильний Варіант 2? 

Вся справа в тому, що в другому варіанті використали округлення результатів обчислень до двох знаків після коми. А в першому варіанті є тільки одна дія ділення. Ось дивіться, який насправді результат обчислень:

Варіант 1

Варіант 2

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

Ось формули, як використані у кожному варіанті:

Варіант 1

Варіант 2

В другому варіанті стоїть функція ROUND (для англійської версії Excel) В українському (російському) Excel ця функція позначається ОКРУГЛ.

Який варіант правильний?

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

При складанні кошторисів враховуйте наступні застереження:

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

2. Юридичний момент на який я не маю відповіді. Якщо затвердити кошторис ось в такому вигляді:

Чи можуть такий кошторис, наприклад в суді оскаржити  через те що 2,02+1,46+0,93+0,72+0,32=5,45 і це може бути однією з причин для визнання рішення загальних зборів незаконним?

3. У випадках про які йшла мова вище, розмір внеску порахований з заокругленнями є більший від того, який рахувався без заокруглень. А якщо буде навпаки? Коли округлення приведуть до меншого внеску, а на зборах буде прийнятий більший внесок. Гарантовано, що будуть такі співвласники, які звернуть на це увагу, і будуть вимагати перерахунку в меншу сторону.

Якщо ви не використовуєте функцію округлення, тоді краще приймати кошторис ось у такому вигляді:

Можливо, тоді у співвласників не буде зайвих питань.

4. Спеціалізовані програми для ведення обліку в ОСББ (такі як Моє ОСББ) мають можливості для введення більше ніж 2 знаків після коми, для складових тарифу.

Якщо ви введете в програму дані з такого кошторису:

Тоді програма порахує розмір внеску 5,45.

Тому в програму потрібно вносити ось такі числа:

В цьому випадку розрахунки будуть вестись правильно, а розмір внеску буде відображатись як 5,43

Ось як виглядає розбиття на складові внесу в програмі Моє ОСББ:

5. Якщо ви використовуєте перший варіант кошторису, в програму ввели правильні складові, як описано вище в п.4 (фактично розмір внеску 5,434152). Пам’ятайте, що сума нарахувань по всім квартирам в програмі не буде співпадати з тим, що ви порахуєте в Excel, а також не буде співпадати з звичайним множенням загальної площі всіх квартир на розмір внеску 5,434152. У вас завжди будуть розбіжності. 

Це пов’язано з тим, що по кожній квартирі окремо, кінцева сума буде заокруглюватись. Це буде виглядати наступним чином: площа квартири * 5,434152 = результат округлений до двох знаків після коми. По різним квартирам заокруглення буде в одну чи іншу сторону, але в загальному сума не співпаде з тим, що ви рахуєте в Excel.

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

6. Якщо ви використовуєте другий варіант кошторису ось в такому вигляді:

Тоді всі поквартирні нарахування будуть співпадати з нарахуваннями в програмі. Адже складові вже округлені до двох знаків після коми, і всі розрахунки вже відбуваються округленими.

Як додати функцію округлення в Excel?

Якщо ви хочете додати у свої розрахунки в Excel функцію округлення, то ось як це робиться.

Функція виглядає наступним чином (для англійського та українського/російсього Excel відповідно):

ROUND(число;кількість_розрядів)

ОКРУГЛ(число;кількість_розрядів)

Там де “число” ви вставляєте формулу розрахунку, “кількість_розрядів” ставите 2 (тобто два знаки після коми).

Додати функцію в комірку можна декількома способами:

1. Вгорі таблиці відображається рядок формул. Там знайдіть кнопку fx 

Натиснувши на кнопку, з’явиться діалогове вікно:

Якщо не бачите необхідну функцію в списку, тоді в полі пошуку наберіть “round” чи “округл” та натисніть кнопку “Знайти”. Виберіть зі списку функцію округлення.

Натисніть “ОК”.

Далі буде діалогове вікно функції округлення:

В полі “Число” введіть формулу для розрахунку, як ви це звичайно робите в комірці – обираєте першу комірку, ставите знак математичної дії, обираєте другу комірку. 

В полі “Кількість_розрядів” ставите 2 для округлення до двох знаків після коми.

Натискаєте ОК. 

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

2. Обираєте комірку. Вводите в неї знак “=” (дорівнює). Таким чином ви починаєте вводити формулу у комірці. Набираєте перші букви функції в нашому випадку ROUND або ОКРУГЛ.

З’явиться випадаюче меню, в якому подвійним кліком мишки можете вибрати необхідну функцію. 

Далі вводите в ручному режимі формулу розрахунку та кількість розрядів. Не забувайте про дотримання синтаксису функції, тобто вводити “;” та відкрити і закрити дужки “()”, так як зображено у підказці.

3. Подібно як в попередньому варіанті, ви починаєте вводити знак “=” вже в рядку формул. Далі все буде як в пункті 2.

4. В меню Excel обрати “Формули”, далі обрати пункт меню “Вставити функцію”. Повторити шлях як в пункті 1.

5. Натиснути комбінацію клавіш Shift+F3. З’явиться діалогове вікно “Вставлення функції”. Повторити шлях, як в пункті 1.