Ми постійно стикаємось з однією особливістю, на яку мало хто звертає увагу. Ви в більшості використовуєте 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.