2. Уменьшение стоимости за счет износа в течение срока амортизации. Ежегодно стоимость снижается на одинаковую часть (например станок на 1/30).

Нам следует вычислить новую цену предмета с учетом указанных процессов.

Сначала учтем инфляцию. Так, например, для станка, купленного в 1994г., новая цена будет определяться инфляционными процентами за годы с 1994 по 1999 включительно. Здесь следует воспользоваться финансовой функцией определения будущего значения

=БЗРАСПИС(цена_закупки;инфляционные_проценты).

Или для первого объекта (станка) =БЗРАСПИС(C8;C4:H4).

Аналогично для крана =БЗРАСПИС(C9;E4:H4) и т.д.

Наша задача заключается в том, чтобы научиться определять начальную позицию блока инфляционных коэффициентов (здесь C4 и E4) как функцию года приобретения изделия. Для этой цели мы введем технический столбец (№), в котором определяется номер колонки с годом, совпадающим с годом покупки, в блоке инфляционных процентов. Здесь можно воспользоваться функцией ПОИСКПОЗ(). Однако следует учесть, что год покупки может и не входить в сетку инфляционных лет с 1992г. Поскольку до этого периода инфляции не было, предыдущие годы в таблице отсутствуют. Это значит, что, если год приобретения, например, 1983, его следует считать 1992. Такое условие можно реализовать функцией МАКС(год_покупки; 1992г). Отсюда, для первого объекта запишем E8=ПОИСКПОЗ(МАКС(B8; A$3);A$3:H$3).

Вычислим новую цену предмета с учетом инфляции. Начало блока, содержащего нужные инфляционные проценты, будет определено с помощью функции

ИНДЕКС(строка_процентов_по_годам;;номер_колонки_внутри_строки).

Или для станка F8=БЗРАСПИС(C8;ИНДЕКС(A$4:H$4;;E8):H$4).

Второй аргумент в функции ИНДЕКС() опущен, поскольку ее объектом является единственная строка.

Теперь учтем влияние износа в форме коэффициента, на который нужно умножить цену

(срок_амортизации – лет_эксплуатации)/срок_амортизации

или (D8–(C1–B8))/D8. Эта формула верна только до тех пор, пока не кончился амортизационный период. В последнем случае результат будет бессмысленным (отрицательным). Чтобы учесть указанное обстоятельство, сделаем так, чтобы он никогда не становился меньше нуля МАКС((D8–(C$1–B8))/D8;0).

Окончательно, для формирования новой цены нам понадобится формула следующего вида

F8=БЗРАСПИС(C8;ИНДЕКС(A$4:H$4;;E8):H$4)*МАКС((D8–(C$1–B8))/D8;0).

После того, как сформированы правильные выражения, при желании можно скрыть технический столбец с заголовком № или включить его содержимое в столбец Е.

Пример 5.29. Продажа авиабилетов. Построим таблицу бронирования мест на авиарейсы. О каждом рейсе известна номинальная цена билета для каждого из трех классов. Эти тарифы хранятся на отдельном листе Рейс. Собственно данные о заказах хранятся на другом листе Бронь. Это номер желаемого рейса, класс салона, а также число требуемых взрослых и детских билетов.

В столбце Цена билета предъявляется цена билета из листа Рейс, извлекаемая следующей формулой

цена_билета=ИНДЕКС(область_тарифов;строка_ рейса; столбец_класса).

или для первого заказа

С5= ИНДЕКС(рейс!A$1:D$100;ПОИКОЗ(A5;рейс!A$1:A$100;0);ПОИСКПОЗ(B5;рейс!A$1:D$1;0)).

Общая стоимость заказа формируется путем умножения количества заказанных взрослых билетов на цену билета, плюс число детских билетов на цену билета со скидкой на детей (здесь 40%): F5=C5*D5+C5*E5*(1–C$1). Кроме того, и при заказе на сумму более семи полных билетов положена скидка (оплачивается только 80% стоимости заказа)

	A	B	C	D	E	F	G
1	Скидки:	на детей	40%	на группу	20%
2	БРОНИРОВАНИЕ АВИАБИЛЕТОВ
3	№ рейса	Класс	Цена билета	Число билетов		Стоимость билетов
4				взрос.	детских	полная	со скидкой
5	12	1	800	12	1	10080	8064
6	45	3	500	4	2	2600	2600
7	101	2	600	1		600	600
8	Всего			17	3	13280	11264
			Рис.	5.29б. Лист	Бронь

G5=F5*ЕСЛИ(F5>7;C5*(1–E$1);1).

	A	B	C	D
1	класс рейс	1	2	3
2	12	800	700	600
3	65	700	610	530
4	101	640	600	580
5	45	600	550	500
6	67	950	850	780
Рис. 5.29а. Лист Рейс

Пример 5.30. Расчеты в гостинице. Построим таблицу расчетов с клиентами гостиницы. Данные находятся в двух листах. На листе Номера помещается список из тридцати номеров отеля с указанием имеющихся в них мест и цены за место. Для упрощения выкладок области листа A1:C21 присвоено имя Места. На втором листе Оплата располагаются собственно данные о проживающих (области В5:D100 назначим имя Гост). На листе Номера формируется информация о числе уже занятых и свободных мест.

	A	B	C	D	E		D	E
1	Номер	Мест	Цена	Занято	Свободно		Занято	Свободно
2	1	2	50	3	–1		=СЧЁТЕСЛИ(гост;A2)	=B2–D2
3	2	2	60	1	1		=СЧЁТЕСЛИ(гост;A3)	=B3–D3
4	3	2	20	1	1		=СЧЁТЕСЛИ(гост;A4)	=B4–D4
5	4	3	20	0	3		=СЧЁТЕСЛИ(гост;A5)	=B5–D5
6	5	2	30	0	2		=СЧЁТЕСЛИ(гост;A6)	=B6–D6
			. . .
22	Всего			70	30		=СУММ(D2:D21)	=СУММ(E2:E21)

Рис. 5.30а. Лист Номера

Занятые места подсчитываются с помощью функции вида

Занято=СЧЁТЕСЛИ(данные_о_занятых_комнатах_в_листе_Оплата; номер_комнаты_из_листа_Номера)

или для первого номера D2=СЧЁТЕСЛИ(гост;A2).

Отслеживать занятость номеров администратору удобно по столбцу Свободно, где подсчитывается число оставшихся мест в номерах E2=B2–D2. Отрицательное значение в этой клетке означает попытку разместить в номере (на листе Оплата) человека при отсутствии в нем свободных мест.

К оформлению. Для лучшего зрительного восприятия данных имеет смысл выделить номера, имеющие свободные места (столбец Свободно). Установим здесь пользовательский формат вида: [Красный] [>0].

При обращении клиента администратор отеля в столбце Номер вводит номер комнаты, куда предполагается его поселить. Тогда в столбце Цена места автоматически формируется стоимость места в этом номере. Для этого было бы достаточно выражения С5=ВПР(B5;места;3;0).

	A	B	C	D	E	F	G	H
1	Сегодня:	20.Сен
2	ОПЛАТА ГОСТИНИЦЫ
3	Ф.И.О.	Но- мер	Цена места	Дата заезда	Оплата:		Доплата:
4					по		дней	
5	Петров	2	60	12.Сен	30.Сен	1080	остаток: 10дн
6	Лукин	1	50	20.Сен	25.Сен	250	остаток: 5дн
7	Васин	3	20	16.Сен	20.Сен	80	СЪЕЗД
8	Попов	1	50	14.Сен	15.Сен	50	доплата 5дн	250
9	Кулик	1	Занято			0	СЪЕЗД

Рис. 5.30б. Лист Оплата

Однако здесь имеет смысл проконтролировать возможность заселения человека в номер – не исключено, что он полон. Для предотвращения поселения в комнату большего числа людей, нежели имеется там мест, выполняется соответствующая проверка, результатом которой явится слово Занято, если мест нет, и цена места, если есть

Цена=ЕСЛИ(число занятых мест в данном номере > общего числа мест в нем, выводится слово Занято,

в противном случае выводится Цена этого места).

Для первого постояльца результат достигается формулой вида

С5=ЕСЛИ(СЧЁТЕСЛИ(гост;B5)>ВПР(B5;места;2;0);"Занято";ВПР(B5;места;3;0)).

Если воспользоваться ею, то слово Занято возникнет во всех строках, содержащих информацию о номере, куда вы пытаетесь поселить лишнего человека. Для предотвращения этого усложним выражение таким образом, чтобы сообщение появлялось только для нового клиента. Введем анализ еще одного условия – отсутствия даты заезда (D5=0)

C5=ЕСЛИ(И(СЧЁТЕСЛИ(гост;B5)>ВПР(B5;мест;2;0);D5=0);"Занято";ВПР(B5;мест;3;0)).

Здесь сначала подсчитывается число уже занятых мест в интересующем номере (СЧЁТЕСЛИ(гост;B5)). Если оно оказывается большим количества имеющихся мест в номере (>ВПР(B5;места; 2;0)), в столбце Цена места формируется слово Занято (место недоступно), если нет – предъявляется цена места в номере.

Далее, если все в порядке, желающий поселиться вносит плату по определенное число (Оплата: по) в сумме F5=(E5–D5)*C5.

Однако, если в С5 будет слово Занято, возникнет сообщение об ошибке (на слово нельзя умножить). Чтобы обойти такую ситуацию, усложним выражение для Суммы оплаты таким образом, чтобы слово Занято интерпретировалось как ноль F5=ЕСЛИ(ЕТЕКСТ(C5);0;(E5–D5)*C5).

Замечание. Здесь следует сказать, что добиваться полной “глад­кости” функционирования таблицы (т.е. выполнять обработку всех сообщений об ошибках), возможно, не всегда имеет смысл. В данном случае и по имеющимся стандартным сообщениям системы оператор поймет, что гостиничный номер уже полон. Обнаружив этот факт, он тут же подыщет клиенту другое место и проблема, а также связанные с ней сообщения об ошибках, снимутся сами собой. Решение об уровне проработки ошибочных реакций системы пользователь каждый раз должен принимать исходя из конкретных обстоятельств ее работы.

В дальнейшем, администратор гостиницы отслеживает своевременность уплаты денег. В столбце Доплата: дней вычисляется разность между текущей датой (В1) и датой, по которую была произведена оплата G5=E5–$B$1. Поскольку дата заезда может отсутствовать, результат может оказаться бессмысленным. В виду этого сделаем так, чтобы разность вычислялась только при наличии даты заезда G5=ЕСЛИ(D5=0;0;E5–$B$1).

К оформлению. Для привлечения внимания администратора в столбце Доплата: дней использован специальный пользовательский формат вида