Excel. Как упростить себе жизнь – часть I

В этом кратком уроке я постараюсь показать вам как несложно упростить себе жизнь, если вы пользуетесь Excel. Описываемая задача упрощена, но так лучше будет понятно что я имею ввиду. Не поленитесь заглянуть на второй вариант, который скоро появится, – там неожиданное для многих решение, которое вы сами сможете применять в своих задачах.

Допустим у нас вот такая условие:

Каждый клиент должен заплатить определенную сумму до какого-то срока. Некоторая сумма уже выплачена. Надо разбить остаток платежей на несколько лет (оценивая дату завершения). Нам нужно получить красные циферки с картинки примера.

Например,первая строка. Уплатил 1000, осталось еще 1000. Контракт заканчивается в 2010 году – надо разбить на 4 платежа.

Новички пытаются решить проблему в лоб. Пишутся длиннющие формулы, которые совершенно невозможно читать, разбираться в них и поддерживать. Попробуйте ка разобраться что делает вот такая формула? (бессмысленный пример взятый из гугля):

=ЕСЛИ(ЕНД(ЕНД(ИНДЕКС(AssetsB:B;ПОИСКПОЗ(D9;AssetsA:A;0)));ИНДЕКС(AssetsE:E;ПОИСКПОЗ(D9;AssetsD:D;0));ИНДЕКС(AssetsB:B;ПОИСКПОЗ(D9;AssetsA:A;0))));0;(ЕНД(ИНДЕКС(AssetsB:B;ПОИСКПОЗ(D9;AssetsA:A;0)));ИНДЕКС(AssetsE:E;ПОИСКПОЗ(D9;Assets ...

Как лучше решить проблему? Есть два способа. Я намеренно неоптимально решаю задачу, что бы показать суть.

Совет N1 Не усложняйте себе жизнь. Лучше сделать несколько маленьких шажков, чем один большой и запутанный…

Рассмотрим, на первый взгляд, способ попроще (и не интересный):

Скачать файл с примером из урока

Использование дополнительных столбцов.
Почему бы не разбить задачу на два этапа? Вставляем дополнительные столбцы:
1) вычисляем надо ли платить в этом году. Если надо – единица, Если не надо – ноль.

2) Вычисляем количество платежей

И считаем результат… Формулы все просты. Разобраться в них легко…

Теперь прячем ненужные столбцы. Помечаем их, кликаем правой кнопкой и выбираем “Hide” (Спрятать)

Столбцы с промежуточными вычислениями спрятаны.

Если мы захотим вернуть их для просмотра и редактирования – помечаем два соседних столбца, где буквы идут не по порядку и

Обратите внимание . В формулах, для задания адреса ячейки, можно использовать знак $ Он означает “зафиксировать”. Поясню.

Если я напишу вот такую формулу =IF(YEAR(D2)>=E1;1;0) – она будет работать. Но что будет, если я попытаюсь её скопировать куда либо? Например в соседний, правый столбец? =IF(YEAR(E2)>=F1;1;0) Заметили? Буковки “поехали”.

А если я “зафиксирую букву или цифру (или обе), то формула:
=IF(YEAR($D2)>=E$1;1;0) после копирования вправо будет иметь вид =IF(YEAR($D2)>=F$1;1;0)

Подробнее об этом вот тут

то, что не отмечено значком $ увеличилось! А то, что мы отметили значком – осталось.

Следующая часть самая интересная. Я напишу вам как решать задачи намного проще и без всего этого … всякого. Устал писать :)

UPD: Постараюсь написать её в пятницу вечером – раньше не получится, надо уехать.