A$1:A$6;1);ПОИСКПОЗ(C9;A$1:F$1;0)

Замечание. Здесь в качестве блока поиска определена целиком вся тарифная сетка, включая ячейку А1. Это в некоторых случаях может вызвать неверный результат поскольку ее содержимое в таком случае также попадает в область поиске. Если функция ПОИСКПОЗ(…) посчитает значение клетки A1 соответствующим критерию поиска, результат может быть извлечен совершенно из другого места таблицы тарифов, либо последует сообщение об ошибке. Если такая опасность существует, можно изменить функцию таким образом, чтобы исключить из обработки ячейку А1

ИНДЕКС(B$2:F$6;ПОИСКПОЗ(E9;A$2:A$6;1);ПОИСКПОЗ(C9;B$1:F$1;0)).

 

	A	B	C	D	E	F	G
	Разряд /стаж	1 разр.	2 разр.	3 разр.	4 разр.	5 разр.	Дата расчета:
	0 лет	1,0	1,1	1,2	1,3	1,4	20.09.99
	5 лет	1,2	1,3	1,4	1,5	1,6
	10 лет	1,4	1,5	1,6	1,7	1,8
	20 лет	1,6	1,7	1,8	1,9	2,0
	30 лет	1,8	1,9	2,0	2,1	2,2
		ЗАРПЛАТА
	Имя	Дата поступ.	Разр.	Зарп.	Стаж (лет)	Коэфф.	ВСЕГО
	Петр	20.06.99			0,2	1,4
	Иван	12.08.94			5,0	1,5
	Олег	20.10.65			33,8	2,1
	Сергей	01.04.70			29,3	1,7
	Итого	ср.стаж:17
			Рис.	5.27б

Средний стаж вычисляется как среднее арифметическое дат поступления, отнятое из даты расчета. Для перевода дней в годы результат делится на число дней в году (364) и округляется до целого, поскольку здесь не требуется высокой точности

B12=ОКРУГЛ((G$2–

СРЗНАЧ(B9:B12))/364;0).

Формат ячейки: “ср.стаж:”#.

Выплаты с учетом коэффициента (колонка Всего) определятся формулой вида G9= F9*D9. Внизу таблицы обычные формулы вертикаль­ного суммирования – D13=СУММ(D9:D12), G13=СУММ(G9:G12).

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

	A	B	C	D	E	F	G	H
	Текущий	год:
			Инфляция
	2200%	1200%	700%	150%	30%	16%	50%	30%
			Переоценка
	Фонды	Куплено	Срок аморт.	№	Новая цена
	Год	Цена
	Станок
	Кран
	Стенд
	Копир
	Факс
	ВСЕГО
			Рис.	5.28

1. Увеличение стоимости под влиянием инфляции. Сводка об инфляции (процентах снижения покупательной способности рубля) с 1992г. отображена в строках 3 и 4. Считаем, что до 1992г. инфляции не было. Деноминация рублей учитывается в исходной цене объектов. Цена предметов, купленных на “старые” деньги, уменьшается в 1000 раз, остальные цены остаются без изменения.

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
	Скидки:	на детей	40%	на группу	20%
	БРОНИРОВАНИЕ АВИАБИЛЕТОВ
	№ рейса	Класс	Цена билета	Число билетов	Стоимость билетов
	взрос.	детских	полная	со скидкой
	Всего
			Рис.	5.29б. Лист	Бронь

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

	A	B	C	D
	класс рейс
Рис. 5.29а. Лист Рейс

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

 

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

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

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

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

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

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

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

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

	A	B	C	D	E	F	G	H
	Сегодня:	20.Сен
	ОПЛАТА ГОСТИНИЦЫ
	Ф.И.О.	Но- мер	Цена места	Дата заезда	Оплата:	Доплата:
	по	å	дней	å
	Петров			12.Сен	30.Сен		остаток: 10дн
	Лукин			20.Сен	25.Сен		остаток: 5дн
	Васин			16.Сен	20.Сен		СЪЕЗД
	Попов			14.Сен	15.Сен		доплата 5дн
	Кулик		Занято				СЪЕЗД

Рис. 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).

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

"остаток:"???"дн"; [красный]"доплата"???"дн"; [синий]"СЪЕЗД"

позволяющий предъявить данные в удобной форме. Для лиц, у которых сегодня закончился оплаченный срок, выводится слово СЪЕЗД синего цвета. Для тех, у кого имеется задолженность, выводится слово доплата и число дней, за которые нужно доплатить, красного цвета. В нормальном случае выводится число оставшихся оплаченных дней проживания.

В столбце Доплата: сумма для должников предъявляется сумма, которую им необходимо внести к погашению долга H5=ABS(ЕСЛИ(G5<0;G5*C5;0)).

Пример 5.31. Нерегулярные таблицы. Обычно, пользовательские таблицы, обрабатываемые в Excel, имеют регулярную структуру, где все строки и столбцы содержат одинаковое число элементов. На практике, однако, это наблюдается (см. рис. 5.31а) не всегда. Рассмотрим пример с такой нерегулярной структурой. Пусть (рис 5.31б) требуется подсчитать общее число рабочих дней и часов сотрудников организации. При этом все они могут иметь разное количество рабочих дней и часов в месяце/квартале/году.

Здесь можно поступить традиционным образом – оставить 365 колонок (по одной для каждого дня), а суммы получать в 366 столбце. При такой структуре собственно данные и итоговый столбец оказываются разнесенными на столь большое расстояние, что от пользователя потребуется постоянное утомительное перемещение по таблице, не говоря уже о том, что в рабочем листе всего-то 256 столбцов.

Если “итоговые” функции, которые мы желаем реализовать, несложны, мы можем построить их другим способом. Для Петра, например, число отработанных дней вычисляется в ячейке С2, а часов – в С3. Каждая рабочая смена в таблице зафиксирована датой и числом отработанных часов. Поскольку итоговые клетки (столбец С) находятся слева от заранее неизвестного и изменяющегося числа суммируемых клеток, требуется как-то определить, сколько же столбцов следует обработать. Для выяснения этого обстоятельства здесь создана техническая колонка В (Столбцов), содержащая следующее выражение для Петра B2=ПОИСКПОЗ(999999;D2:VI2;1).

	A	B	C	D	E	F	G	H	I
	Имя	Столбцов	Всего
	Петр		2д	1.Май	8ч	6.Май	6ч
		14ч
	Иван		3д	2.Май	5ч	7.Май	12ч	10.Май	8ч
		25ч
	Олег		1д	4.Апр	20ч
		20ч
	Всего	6д
	59ч					Рис. 5.	31б

Здесь функция ПОИСК() пытается найти заведомо слишком большое (999999) для нашей задачи число во второй строке. Поскольку известно, что никакая возможная дата, ни тем более число рабочих часов не могут его превышать, результатом будет номер последней непустой числовой клетки в этой строке, начиная от начальной D2. Отсюда мы легко найдем количество отработанных дней как число клеток, деленное на два, поскольку для каждого рабочего дня заполняются две клетки C2=ОТБР(B2/2).

Функция отбрасывания понадобилась для того, чтобы получить правильный результат, если дата была введена, а число часов – еще нет. Сложнее сформировать сумму часов. В качестве критерия суммирования положим, что значение слагаемого не должно превышать самой длинной рабочей смены, например 24 часов. Таким образом, не будут просуммированы значения дат. Современные возможные даты это числа больше 36000 (так 1.1.99г= 36161). Сама область суммирования (для Петра) ограничена столбцом D и столбцом, найденным с помощью функции ИНДЕКС() со вторым аргументом, взятым из колонки В,

C3 {=СУММ(ЕСЛИ(D2:ИНДЕКС(D2:VI2;;B2)<24;D2:ИНДЕКС(D2:VI2;;B2);0))}.

Кроме вычислений для отдельного человека, в таблице формируются и вертикальные суммы. Число дней, отработанных всеми сотрудниками, находится во всех четных строках столбца С. Чтобы гарантировать суммирование именно этих значений, в функцию СУММ() включены только те строки, номера которых без остатка делятся на два (т.е. четные строки)

C8 {=СУММ(ЕСЛИ(ОСТАТ(СТРОКА(C2:C7);2)=0;C2:C7;0))}.

Аналогично сумма часов находится в нечетных строках

C9 {=СУММ(ЕСЛИ(ОСТАТ(СТРОКА(C2:C7);2)=1;C2:C7;0))}.

Все функции в столбце С вводятся как функции массива, т.е. ввод завершается нажатием клавиш Ctrl+Shift+Enter, о чем свидетельствуют фигурные скобки. Техническая колонка В создана нами только для наглядности. В дальнейшем колонку можно скрыть или включить ее содержимое в С2 и С3.

Следует отметить, что в данном конкретном примере можно обойтись и гораздо более простыми средствами для подсчета числа отработанных дней и часов по вертикали:

C8=СУММЕСЛИ(C2:C7;”>0”) – сумма всех часов

С9 {=СУММ(ЕСЛИ(C2:C7<1;D2:D7;0))} – число всех дней

и по горизонтали:

C3=СУММЕСЛИ(D2:IV2;”<24”) – сумма часов работника

C2=СЧЁТЕСЛИ(D2:IV2;”>=30000”) – число дней работника,

поскольку здесь нам не нужно знать, какая именно клетка для каждого из рабочих была заполнена последней. Критерием вертикального суммирования часов явилось наличие в столбце В числа больше нуля, а количества дней – числа меньше 1. В горизонтальную сумму часов включались только числа, меньше 24, а в подсчет дней – числа больше 30000.

@ Следующие два примера посвящены более сложным, чем ранее, расчетам, связанным с определением временных интервалов. В виду этого рассмотрим несколько технических моментов. Вспомним, что дата в Excel – это целое число, отображенное в формате даты (1 единица = 1 день), время – дробное число в формате времени (один час =1/24= 0,041666667). Таким образом, в одной клетке может сразу отображаться и дата и время (дата_время). Например, число 36069,3958333333 соответствует 01.10.1998 9:30 (1 октября 1998г. 9 часов 30 минут). При расчетах нам может понадобиться извлечь из такой комбинированной даты_времени только дату или только время, иными словами отделить целую или дробную части числа. Дату можно отделить функцией ЦЕЛОЕ():

дата=ЦЕЛОЕ(дата_время).

Время может быть получено с помощью выражения:

время=дата_время – ЦЕЛОЕ(дата_время).

Однако проще использовать функцию вычисления остатка от деления на 1:

время=ОСТАТ(дата_время;1).

Например, если в А1 содержится значение 01.10.1998 9:30, то

ЦЕЛОЕ(А1) = 01.10.1998 0:00 и ОСТАТ(А1;1) = 9:30.

В дальнейших рассуждениях дату, содержащую и время, будем обозначать термином дата_время; дату, содержащую только число, месяц и год, – термином дата; дату, содержащую только время, – термином время.

Пример 5.32. Расчет временных интервалов. Пусть требуется рассчитать зарплату работников, несущих дежурство на производстве, в том числе, и в ночную смену. Предполагается, что график дежурств не определен жестко, а может быть установлен для каждого работника индивидуально и меняться со временем. В этих условиях важно быстро вычислить общее число отработанных часов и количество часов, пришедших на ночную смену. Предполагается также, что длина смены не может превышать одних суток (24 часа).

На предприятии установлено, что ночная смена начинается в 20 часов (клетка С2), а заканчивается в 8 часов следующего дня (клетка Е2). Начало и конец дежурства (НД и КД) для каждого работника вносятся в клетки В5 и С5 (для Петра) в формате, который включает как дату, так и время (день, месяц, год, часы и минуты – Д МММ чч:мм). Оплата труда за каждый час дежурства в дневное время определяется часовым тарифом (клетка Н2). В ночное время оплата производится по удвоенному тарифу плюс 30 руб. на оплату дороги домой (из дома).

Сначала вычислим общее рабочее время как разность конца и начала дежурства, умноженную на 24 (D5=(C5–B5)*24). Последнее необходимо для преобразования времени-дроби при дате в часы.

 

 

	A	B	C	D	E	F	G	H
		ГРАФИК ДЕЖУРСТВ сейчас:	2 ноя 03:24
	НОЧЬ	начало:	20:00ч	конец:	8:00ч	Тариф:	15р/ч
	Имя	Д е ж у р	с т в о	Всего часов		Ночь		å
	начало	конец	начало	конец	час.
	Петр	1 ноя 06:00	1 ноя 12:00	6,0	1 06:00	1 08:00
	Иван	1 ноя 09:30	2 ноя 09:00	23,5	1 20:00	2 08:00
	Олег	3 ноя 12:00	4 ноя 01:00	13,0	3 20:00	4 08:00		Рис. 5.32 300
	ВСЕГО			42,5

Теперь определим дату_время начала первой ночи, которая, возможно, приходится на дежурство. Если работа начинается в дневное время, начало ближайшей ночи (НН) попадет на 20 часов дня заступления на дежурство. Если НД приходится на ночное время, то это и будет начало ночи. Таким образом,

НН=ЕСЛИ(НД приходится на дневное время; дата НД+20:00; НД) или в адресах

E5=ЕСЛИ(И(B5>=ЦЕЛОЕ(B5)+E$2; B5<ЦЕЛОЕ(B5)+C$2);ЦЕЛОЕ(B5)+C$2;B5).

Поскольку здесь нужно соединить дату НД и время, установленное на предприятии как начало/конец ночной смены, в формуле используется функция ЦЕЛОЕ(), отбрасывающая дробную часть аргумента, содержащего время НД.

Время конца ночной смены – всегда 8 часов утра. Наша задача определить день КН. Он может быть как днем заступления на дежурство (ЦЕЛОЕ(E5)), так и следующим днем (ЦЕЛОЕ(E5)+1).