Расчёт остатков плана

Спрашивает клиентка: «Мне надо рассчитывать остаток месячного плана на остаток месяца, разнося его по рабочим дням. Каждый день я заношу в таблицу фактическую выручку. А в оставшиеся дни автоматически должны записываться расчётные величины остатка, поделённого на количество дней. В примере на листе «Январь» я привела свой текущий расчёт. Но в нём слишком много ручной работы. Каждый раз приходится перестраивать формулы для того, чтобы посчитать итоговую сумму фактической выручки и остаток месячного плана. Помогите перестроить табличку, чтобы уменьшить хлопоты по её ведению.»

Посидел на досуге и набросал решение. В нём использовал:

1.     Функцию ЕФОРМУЛА, которая помогает понять, что находится в ячейке — формула или просто число.

2.     С её помощью получил признак «Ф/П». «Ф» (факт) проставляется, если в столбике «Показатель» стоит число, а «П» (план) — если формула. Мне эти признаки помогают просчитать количество оставшихся дней. Да, знаю, что можно было выпендриться, применить массивы и обойтись без этого столбца, но я решал задачку не для себя, а для клиента. И тут важно не только решение, но и способность пользователя поддерживать систему в рабочем состоянии. А кто у нас знает, что такое «массивы»? Только такие задроты как я.

3.     В столбце «Показатель» я использовать СЧЁТЕСЛИМН, чтобы подсчитать количество оставшихся дней.

4.     Таблицу я сделал «умной», чтобы облегчить размножение формул при добавлении новых дней. Да и вообще, если таблица представляет собой «водопад» (так я называю правильно организованные потоки данных), то для неё нет альтернативы — только «умность».

5.     Вычисления справа содержат Функцию СУММЕСЛИМН для подсчёта итоговой суммы фактической выручки, немножко ЕСЛИ, чтобы остатки не оказались отрицательными, СЧЁТЕСЛИМН для подсчёта фактически отработанных дней и немножко арифметики

6.     Чуть не забыл про условное форматирование, которое при помощи функции ЕФОРМУЛА красит зелёным клеточки в столбце «Показатель», если в них есть формула, ну и заодно помечает дни, в которых стоит расчётный, а не фактический показатель.

Ну вот, пожалуй, и всё. Лёгкий моцион в формулах Excel окончен, можно идти на прогулку с собакой.

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