I. Составление сметы обучения на бухгалтерских курсах

Начальные условия задачи. Базовая стоимость разли­чается для разных курсов, отдельно оплачиваются дополнительные мате­риалы. Постоянным клиентам предо­ставляется скидка, а если не внесена предоплата, общая сумма увеличива­ется.

1. На первом шаге необходимо создать следующую заготовку на листе табличного процессора

2. В начале нужно выбрать, что за курс предполагается прослушать. В нашем примере эта информация вводится в ячейку А4. При этом, нужно указать, что в ячейку А4 можно вводить информацию только из ячеек С4, С5, С6 и С7. Любая другая ин­формация в этой ячейке недопустима.

Сделайте текущей ячейку А4, щелкнув на ней мышью, и выберите команду меню Данные > Проверка, чтобы открыть диалог настройки проверки вводимых значений. На вкладке Параметры этого диалога выберите в списке Тип данных вариант Список. Этим вы укажите, что в данную ячейку можно вводить значения только из определенного списка. Далее нужно указать, где расположен этот спи­сок. Щелкните мышью на поле Источник и выделите с помощью мыши ячейки с С4 по С7. Адреса ячеек появятся в поле. Вы также можете указать адрес списка вручную, введя в поле выражение =$С$4:$С$7. Установите флажок Список допус­тимых значений, и нажмите кнопку ОК. Диалог закроется, и теперь вы не сможе­те ввести в ячейку неправильное значение.

Когда вы сделаете активной ячейку А4, правее ячейки появится кнопка. Нажав ее, вы откроете список возможных вариантов. Выберите ва­риант, список закроется, и нужный вариант будет вставлен в ячейку. Попытай­тесь ввести неверное значение, и Excel сообщит об ошибке. Вы сможете только отменить неверный ввод, но никаким способом нельзя ввести ошибочную ин­формацию.

3. Аналогично нужно задать проверку для ячеек А5, А6 и А7. Единственное от­личие в том, что для каждой ячейки задаются свои собственные списки. После того как все списки заданы, введите в ячейки про­извольную информацию. Конечно, вы сможете ввести любую информацию, но только из спис­ков. С первой задачей мы справились, теперь ввод исходных данных для сметы стал значительно проще. Теперь нужно автоматизировать расчет сметы.

4. Введите в ячейку В4 формулу =ВПР(A4;C4:D7;2;ЛОЖЬ).

Функция ВПРочень полезна и будет использоваться во многих примерах. Она позволяет най­ти в таблице строку, содержащую нужное значение, и вернуть значение из дру­гой ячейки этой же строки. В нашем случае мы ищем название курса, а хотим узнать его цену, записанную в той же строке. Диапазон ячеек C4:D7 указывает на область таблицы, в которой будет выполняться поиск. Поиск осуществляет­ся по ячейкам первого столбца области, то есть по ячейкам столбца С. Значе­ние, которое будет искаться, указано в ячейке А4, что и задается первым аргу­ментом функции. Число 2 в качестве третьего аргумента указывает, что нужно взять содержимое второго столбца найденной строки, то есть столбца D. Сло­во ЛОЖЬ в формуле говорит, что список в указанном диапазоне ячеек может быть не отсортирован. Теперь, если вы введете в ячейку А4 значение Курс буху­чета, в ячейке В4 появится сумма из третьей строки столбца D, так как имен­но в этой строке в прейскуранте находится данное название. В нашем примере это будет число 5000.

5. Аналогично, в ячейку В5 нужно ввести формулу =ВПР(А5;С9:D14;2;ЛОЖЬ), чтобы вставить в смету стоимость дополнительных материалов.

6. Так как остальные вычисления предполагают увеличение или уменьшение базовой суммы, формулы получатся немного сложнее. В ячейку В6 введите фор­мулу =(В4+В5)*(ВПР(А6;С16:D17;2;ЛОЖЬ)-1). Сумма В4+В5 вычисляет стои­мость курса вместе с дополнительными материалами. Выражение ВПР(А6;С16:D17;2;ЛОЖЬ) возвращает процент, который должен уплатить кли­ент. Если вычесть из него единицу, то мы получим процент скидки, причем он будет отрицательный. Умножив первую часть формулы на вторую, мы получим размер предоставляемой скидки.

7. В ячейку В7 введите очень похожую формулу, вычисляющую наценку: =(В4+В5+В6)*(ВПР(А7;С19:D20;2;ЛОЖЬ)-1). В отличие от скидки, наценка по­лучается неотрицательной.

8. Все составные части сметы рассчитаны, осталось их только просуммировать. Введите в ячейку В9 формулу =СУММ(В4:В7). Коммен­тарии тут не требуются. Далее вы можете добавить дополнительные вычисления, например, рассчитать НДС и НСП. Потратив немного времени, вы составили таблицу, существенно ускоряющую составление смет. Выбрав нужные значения из четырех списков, вы мгновенно получаете готовую смету.