Гпр(что искать; где искать; откуда взять; как искать).

Параметр тип поиска имеет значение ИСТИНА или ЛОЖЬ. Если ЛОЖЬ, поиск в первой строке будет точным, если ИСТИНА (или параметр не задан) – приблизительным (интервальным). В этом случае найденным считается наибольшее значение, меньшее или равное искомому. Ключевая строка тогда должна быть отсортирована по возрастанию. Если этого не сделать, результаты будут непредсказуемы. Напомним, что логические значения ИСТИНА и ЛОЖЬ внутри формул могут быть заменены на 1 и 0 соответственно.

Примеры (исходные данные берутся из рис. 4.8-1).

ГПР(7;B2:F4;3;ЛОЖЬ)=12

Здесь в верхней строке области B2:F4 ищется значение, в точности равное 7. Если поиск успешен, возвращается значение клетки в строке номер три, отсчитывая от верхней строки области. Иными словами

ГПР(искомое_значение 7;область_поиска B2:F4;

строка,_из_которой_извлекается_результат 3; поиск_должен_быть_точным ЛОЖЬ).

В нашем случае результатом явится значение клетки Е4, содержащей число 12. Совершенно идентичные результаты будут получены формулой ГПР(7;B2:F4;3;0) поскольку 0 заменяет значение ЛОЖЬ.

Функции вида ГПР(7;B2:F4;3;1) и ГПР(7;B2:F4;3) скорее всего, вызовут сообщение об ошибке (#Н/Д), поскольку интервальный поиск при неупорядоченной ключевой строке непредсказуем. Хотя в некоторых случаях результат может оказаться верным, рассчитывать на это не стоит.

ГПР(25;B3:F4;2;ИСТИНА)=45

В верхней строке области B3:F4 ищется значение, близкое к 25. Поскольку точно этого значения в строке В3:F3 нет, поиск завершается при нахождении числа 12 в клетке С3, так как следующее значение было (30) бы уже больше искомого (25). Функция возвращает значение клетки в строке номер два, отсчитывая от верхней. В нашем случае это клетка С4. Аналогичный результат будет получен с помощью формулы

ГПР(25;B3:F4;2;1), поскольку 1 заменяет значение ИСТИНА.

ü ВПР(искомое значение; область поиска; номер столбца извлечения [;тип поиска])

– осуществляет поиск искомого значения в самом левом ключевом столбце области поиска. Результат извлекается из столбца с заданным номером относительно ключевого столбца области поиска. Функция является

	A	B	C
		Ключевой столбец	Столбец извлечения
			а
			б
			в
			г
			д
Рис. 4.8-2

полным вертикальным аналогом функции ГПР().

Примеры (данные из рис. 4.8-2):

ВПР(6;B2:C6;2;0)=ВПР(6;B2:C6;2;1)=в – цифра 6 найдена в ключевом столбце, результат извлечен из второго (относительно ключевого) параллельного столбца C.

ВПР(7;B2:C6;2;1)=в – цифра 7 не найдена, но поскольку поиск интервальный, подходящим считается число 6 и результат (в) берется из столбца C.

ВПР(15;B2:C6;2;1)=д – число 15 не найдено. Берется ближайшее меньшее – 11. Результат поиска – буква д.

ВПР(1;B2:C6;2;0)= #Н/Д, ВПР(7;B2:C6;2;0)= #Н/Д,

ВПР(15;B2:C6;2;0)= #Н/Д, ВПР(1;B2:C6;2;1)= #Н/Д.

Точный поиск чисел 1, 7 и 15 оказался неудачным (в ключевой строке нет этих чисел), что и повлекло сообщение об ошибке (“Нет данных”). Вообще, никакой поиск единицы не будет успешным, поскольку ключевой столбец начинается с цифры 3.

Пример. Функции ВПР()/ГПР() могут использоваться не только при вычислениях, но и при условном форматировании. Пусть таблица (рис. 4.8-3) содержит данные о рабочих днях сот­рудников. Нам нужно создать средство быстрого выделения искомых рабочих дней среди перечня рабочих смен, отработанных сотрудниками учреждения. Искомые даты (до трех) вводятся оператором в область C1:E1.

	A	B	C	D	E
	Искомые даты:	6.окт	10.окт	12.окт
	Имя	Рабочие смены
	Петр	10.окт	11.окт	12.окт	13.окт
	Иван	6.окт	9.окт	10.окт	11.окт
	Олег	9.окт	11.окт	12.окт	13.окт
Рис. 4.8-3

Установим для клетки В4 условное форматирование вида:

Условие 1

значение равно =ГПР(B4;$C$1:$E$1;1;0)

формат – шрифт полужирный курсив

и распространим его на все клетки основной части таблицы. Результат вы видите на рисунке. Теперь мы легко определим, кто именно работал в указанные дни (эти даты выделены).

Пример. Рассмотрим (рис. 4.8-4) содержательный пример, связанный с использованием функции ГПР().

Пусть по разряду рабочего (клетка В8) требуется в тарифной сетке найти соответствующий ему суточный тариф (D5) оплаты труда для дальнейшего начисления зарплаты по итогам месяца. Затем тариф умножается на число рабочих дней (сейчас не рассматривается).

Здесь в клетке C8 должен быть получен тариф Петра по его разряду (т.е. по третьему разряду 42 руб.)

тариф_Петра=ГПР(разряд_Петра;тарифная_сетка; строка_Тариф; поиск_точный)

или в виде функции С8=ГПР(В8;B$4:E$5;2;0), где значение В8 ищется в верхней строке области B4:E5. Если поиск оказался успешным, результат извлекается из второй строки области поиска и заносится в С8.

Оформление таблицы, изображенное на рисунке, представляет тарифы наглядным и легкоредактируемым образом. В случае если это не нужно, можно воспользоваться массивами – вместо области поиска задаются сами данные о тарифах, помещенные в фигурные скобки. Вначале перечисляются разряды, затем (через знак двоеточие) – собственно расценки С8=ГПР(B8;{1;2;3;4:30;35;42;50};2;0).

	A	B	C	D
	Фирма	Взнос
	Весна
	Сервис
	Наташа
	Восток
	Заказчик	Взнос	Оплата	Остаток
	Наташа
	Запад
Рис. 4.8-5

 

	A	B	C	D	E
		Тарифная сетка
	Разряд
	Тариф
	Имя	Разряд	Тариф
	Петр

 

Рис. 4.8-4

Здесь не требуется иметь таблицу тарифов, но зато при их изменении, придется редактировать сами формулы.

Замечание. Если объект поиска целое число, поиск точный, а строка извлечения не слишком велика (до 29 элементов), то для тех же целей мож­но воспользоваться функцией ВЫБОР(). Так (см. выше), вместо С8=ГПР (В8;B$4:E$5;2;0) можно применить С8=ВЫБОР(В8;B$5;C$5;D$5;E$5).

Пример. Задача с использованием функции ВПР() (рис. 4-8.5).

Положим нужно обслужить фирмы-заказчики, предварительно внес­шие некоторые денежные взносы (область А2:В5). Таблица должна быть устроена таким образом, чтобы оператор, вводя название фирмы (например, в А8) должен сразу (в В8) видеть эту сумму, с тем, чтобы не допустить отпуск товара свыше нее. В списке фирм заказчик может и отсутствовать. В этом случае в столбце Взнос должен быть выведен 0. Для розыска значения взноса достаточно формулы вида

В8=ВПР(A8;A$2:B$5;2;0).

Однако, если введено имя фирмы, не сделавшей взноса, в клетке будет сообщение об ошибке вида #Н/Д (нет данных). Для формирования правильной реакции системы следует усложнить выражение, использовав средства, способные распознать такую ситуацию

взнос_фирмы=если(фирма не найдена, то 0р., иначе ее взнос).

Или в виде формулы B8=ЕСЛИ(ЕНД(ВПР(A8;A$2:B$5;2;0));0;ВПР(A8;A$2:B$5;2;0)).

Здесь использована функция ЕНД() распознавания соответствующей ошибки (см. раздел 4.13). Тогда Остаток: D8=B8-C8.

Замечание. В некоторых случаях значение четвертого аргумента (тип поиска) функций ВПР()/ГПР() кажется безразличным. В предыдущем примере функция =ГПР(В8;B$4:E$5;2;0) даст тот же результат, что и =ГПР(В8; B$4:E$5;2;1), поскольку строка разрядов упорядочена по возрастанию. В случае, если предполагается точный поиск, следует все-таки всегда указывать параметр 0. Это позволит пользователю вовремя заметить допущенную им при заполнении таблицы ошибку. Например, если он для некоторого работника ввел значение несуществующего пятого разряда (рис. 4.8-4), Excel выдаст сообщение об ошибке. В том же самом случае при параметре тип поиска=1, система предложит считать результатом поиска тариф ближайшего четвертого разряда. Конечно, такую ошибку довольно сложно обнаружить.

ü СМЕЩ(ссылка; строк смещения по вертикали; столбцов смещения по горизонтали; высота; ширина)

– функция возвращает диапазон заданной высоты и ширины, смещенный относительно ссылки на соответствующее число строк и столбцов. Результатом может быть как адрес одной клетки, так и целый блок. Функция может быть использована другими функциями, работающими с адресами (например СУММ()).

Пример. СУММ(СМЕЩ(A1;0;1;1;3))=СУММ(B1:D1).

Здесь относительно клетки A1 на расстоянии вниз на 0 строк и вправо на 1 столбец (т.е. начиная с позиции A2) выбирается блок размером в одну строку и три столбца (т.е. блок B1:D1).

ü ЧИСЛСТОЛБ(область) – возвращает число столбцов в области.

Примеры: ЧИСЛСТОЛБ(A1:C4) = 3 (столбцов в блоке),

ЧИСЛСТОЛБ({1;2;3:4;5;6}) = 3 (столбцов в массиве).

ü ЧСТРОК(области) – возвращает число строк в области.

Примеры: ЧСТРОК(A1:C4) = 4 (строк в блоке),

ЧСТРОК({1;2;3:4;5;6}) = 2 (строк в массиве).

ü АДРЕС(номер строки; номер столбца; [тип ссылки]; [стиль ссылки]; [имя листа])

– возвращает в текстовой форме адрес ячейки, находящейся на пересечении строки и столбца с указанными номерами в заданном листе.

Тип ссылки – указывает тип возвращаемой ссылки и может иметь одно из значений:

1 – абсолютный (например, $A$1);

2 – абсолютная строка, относительный столбец (A$1);

3 – относительная строка, абсолютный столбец ($A1);

4 – относительный (A1).

Если тип не указан, он считается абсолютным (тип 1).

Стиль ссылки – логическое значение. ИСТИНА (или опущено) – ссылка в стиле А1; ЛОЖЬ – в стиле С1К1.

Имя листа – имя рабочего листа или листа макросов.

Примеры: АДРЕС(2;3) = "$C$2", АДРЕС(2;3;2) = "C$2",

АДРЕС(2;3;2;ЛОЖЬ) = "R2C[3]", АДРЕС(2;3;1;ЛОЖЬ;"Лист1") = "Лист1!R2C3".

ü ДВССЫЛ(ссылка на ячейку; стиль ссылки) – возвращает ссылку, заданную ссылкой на ячейку.

Ссылка на ячейку – адрес ячейки, которая содержит либо ссылку в стиле А1, либо ссылку в стиле R1C1, либо имя, определенное как ссылка. Если ссылка не допустима, возвращается значение ошибки #ССЫЛ!.

	A	B	C	D	E
Рис. 4.8-6

Стиль ссылки – логическое значение, указывающее стиль ссыл­ки, содер­жащейся в ячейке (ИСТИНА – ссылка в стиле A1, ЛОЖЬ – в стиле R1C1).

Функция используется для того, чтобы получить значение, находящееся в ячейке, ссылка на которую находится в другой ячейке. Примеры: Если A1 содержит текст "B2", а ячейка B2 – значение 8, то ДВССЫЛ(A1) = 8.

Если Excel настроен на работу с адресами в стиле R1C1 и ячейка R3C5 содержит R2C2, где в свою очередь находится число 2, то 3*ДВССЫЛ(R3C5;ЛОЖЬ) = 6.

ü ИНДЕКС(область; номер строки; номер столбца) – возвращает ссылку на ячейку, на пересечении строки и столбца с указанными номерами внутри области.

	A	B	C	D	E	F
			ТАРИФНАЯ СЕТКА
		долж. стаж	Рабочий	Звеньевой	Бригадир	Прораб
		от 0 лет	1,0	2,0	3,0	5,0
		от 5 лет	1,2	2,3	3,5	5,5
		от 10 лет	2,0	2,5	3,8	6,0
		от 15 лет	2,2	2,7	4,4	6,7
			Рис.	4.8-7

Результат функции интерпретируется другими функциями как ссылка (адрес), однако функция ИНДЕКС() “в чистом виде” возвращает значение (число, текст), находящееся в клетке. Если область содержит только одну строку/столбец, то аргумент строка или столбец, соответственно, является необязательным. Так, для одной строки можно использовать форму ИНДЕКС(область;;столбец). Если аргумент строка/стол­бец равен 0, функция вернет ссылку соответственно на целую строку/столбец.

Примеры (данные взяты из рис. 4.8-6).

=ИНДЕКС(B2:B5;3)=B4=9,

=ИНДЕКС(B5:E5;;4)=E5=16,

=ИНДЕКС(B2:E5;3;2)=C4=10,

=СУММ(ИНДЕКС(B2:E5;2;2):ИНДЕКС(B2:E5;3;4))=СУММ(C3:E4)=54,

=СУММ(ИНДЕКС(B2:B5;0))=СУММ(B2:B5)=28.

Функция ИНДЕКС() удобна для вычисления адресов блоков.

Пример. Положим (рис. 4.8-7) имеется тарифная сетка определения зарплаты (в тыс. руб.) работников с учетом их должности и стажа работы. Здесь в области В4:В7 представлены сведения о стаже в формате вида “от” ## “лет”, т.е., несмотря на внешний вид, это не текстовые, а числовые данные (числа 0, 5, 10, 15).

Пусть, нам нужно найти зарплату для сотрудника в должности Бригадир со стажем 8 лет. В таблице тарифов этим данным соответствует число 3,5 (клетка выделена), находящееся на пересечении второй строки и третьего столбца области C4:F7. Если все параметры поиска установлены, их можно указать в функции

ИНДЕКС($C$4:$F$7;2;3)=3,5.

Однако номера строки и столбца нам пока неизвестны. Их предстоит вычислить, исходя из должности и стажа. Номер столбца (3) находится формулой (поиск точный)

=ПОИСКПОЗ("Бригадир";$C$3:$F$4;0)), а номер (2) столбца =ПОИСКПОЗ(8;$B$4:$B$7;1).

Здесь поиск интервальный, поскольку фактический стаж может и не совпадать с перечисленными цифрами лет в таблице. Или целиком

=ИНДЕКС($C$4:$F$7;ПОИСКПОЗ(8;$B$4:$B$7;1);ПОИСКПОЗ("Бригадир";$C$3:$F$4;0)).

На практике, конечно, в качестве аргументов используются не константы (слово Бригадир и число 8), а ячейки, содержащие соответствующие индивидуальные данные о стаже и должности работника.

ü СТОЛБЕЦ([ссылка]) – возвращает номер столбца по заданной ссылке на клетку.

Пример: СТОЛБЕЦ(A3) = 1.

ü СТРОКА([ссылка]) – возвращает номер строки по заданной ссылке на клетку. Пример: СТРОКА(A3)=3.

Функции СТОЛБЕЦ() и СТРОКА() без аргументов возвращают номера текущих столбца и строки.

Пример. В списке сотрудников (рис.4.8.8) найти человека с наибольшим разрядом. Решением будет функция =ИНДЕКС(A2:A4;ПОИСКПОЗ(МАКС(B2:B4);B2:B4)), которая выработает ответ – Иван. Функция ПОИСКПОЗ() ищет номер строки в столбце разрядов, в которой находится максимальный разряд рабочего.

 

А

В

A

B

C

D

E

F

G

ФИО

Разряд

Работник

Март

Апрель

Май

Июнь

Июль

Лучший месяц

Ольга

Петр

Апрель

Иван

Иван

Июнь

Олег

Олег

Май

Рис. 4.

8-8

Лучший в месяце

Иван

Петр

Олег

Иван

#Н/Д

Рис. 4.8-9

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

B5=ИНДЕКС($A$2:$A$4;ПОИСКПОЗ(МАКС(B2:B4);B2:B4;0)),

G1=ИНДЕКС($B$1:$F$1;ПОИСКПОЗ(МАКС(B2:F2);B2:F2;0)).

 

Сводные функции

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

ü СУММЕСЛИ(область просмотра;критерий поиска [;область суммирования]) – функция ищет в заданной области просмотра данные, отве­чающие критерию поиска и суммирует значения соответствующих ячеек из области суммирования. Если область суммирования не задана, суммирование производится из области просмотра. Критерий поиска может включать только одно условие.

ü СЧЁТ(область просмотра) – подсчет в области просмотра количества числовых ячеек. Пустые ячейки, логические значения и тексты пропускаются.

ü СЧЁТЗ(область просмотра) – подсчет в области просмотра количества непустых ячеек, которыми считаются значения любого типа, включая и строки нулевой длины ("").

ü СЧЁТЕСЛИ(область просмотра;критерий поиска) – производится подсчет в области просмотра числа ячеек, отвечающих критерию поиска.

В функциях СЧЁТЕСЛИ() и СУММЕСЛИ() в качестве критерия можно использовать не только константы, но и ячейки. Однако последние нельзя брать в кавычки, т.е. возможно сравнение только на строгое равенство. Примеры (данные берутся из таблицы на рис. 4.9-1, содержащей сведения о числе отработанных рабочими дней):

СУММЕСЛИ(A2:A5;"Иван";B2:B5)=34 – число дней, отработанных Иваном;

СЧЁТЕСЛИ(B2:B5;">20")=2 – число человек, работавших больше 20 дней;

СЧЁТЕСЛИ(A2:A5;"Иван")=2 – сколько раз в документе встретилось имя Иван;

СЧЁТЕСЛИ(A2:A5;A3)=2 – сколько раз в документе встретилось имя Иван.

ü СРЗНАЧ(число1;число2;...) –среднее арифметическое всех непустых значений.

Пример: СРЗНАЧ(B2:B5)=(23+12+20+22)/4.

ü МИН(число1;число2;...) – возвращает минимальное число из списка. Если аргументы не содержат чисел, возвращает 0. Примеры: МИН(12;7;-2;4;7)=-2, МИН(B2:B5)=12.

ü СЧИТАТЬПУСТОТЫ(область просмотра) – возвращает количество пустых ячеек в заданной области просмотра (нули не считаются пустыми значениями).

ü МАКС(число1;число2;...) – максимальное число. Если аргументы не содержат чисел, возвращается 0.

ü НАИБОЛЬШИЙ(область просмотра;номер) – возвращает наибольший по счету элемент с указанным номером начиная от максимального значения в заданной области просмотра или массиве данных.

ü НАИМЕНЬШИЙ(область просмотра;номер) – возвращает наименьший по счету элемент с указанным номером начиная от минимального значения в заданной области или массиве. Примеры:

НАИБОЛЬШИЙ(B2:B5;1)=23 – максимальный элемент,

НАИБОЛЬШИЙ({5;2;4;8};2)=5 – элемент, находящийся на 2-ом месте для данных, отсортированных по убыванию,

НАИМЕНЬШИЙ({5;2;4;8};1)=2 – минимальный элемент,

НАИМЕНЬШИЙ({5;2;4;8};2)=4 – элемент, находящийся на 2 месте для данных, отсортированных по возрастанию.

Если номер больше, чем элементов данных, функции НАИБОЛЬШИЙ() и НАИМЕНЬШИЙ() возвращают значение ошибки #ЧИСЛО!. Функции удобны, например, для определения наилучших/наихуд­ших результатов среди всех данных.

1 - Срзнач	7 - Стандотклон			A
2 - Счёт	8 - Стандотклонп
3 - Счётз	9 - Сумм
4 - Макс	10 - Дисп				ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;А1:А2)
5 - Мин	11 - Диспр
6 - Произвед
					ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;А4:А5)
					ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;А1:А6)
Рис.	4.9-2				Рис. 4.9-3

ü ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер функции;обрабатываемый блок) – возвращает итог в соответствии с номером функции, который указы­вает на вид используемой функции. Названия функций (рис. 4.9-2) здесь соответствуют именам существующих “отдельных” функций Excel. Пример:

ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9,C3:C5)

– подведет итоги для ячеек C3:C5, используя функцию суммирования (аналогично функции СУММ(C3:C5)).

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

1. Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() игнорирует клетки, содержа­щие эту же функцию в диапазоне обработки (не допускается двойная обработка). Проиллюстрируем применение функции (рис. 4.9-3). Здесь в ячейках А3,A6,A7 находятся функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;A1: A2), ПРО­МЕЖУТОЧНЫЕ.ИТОГИ(9;A4:A5), ПРОМЕЖУТОЧНЫЕ.ИТО­ГИ(9;A1:A6). Видим, что в клетке A7 вычисляется сумма только оригинальных данных, т.е. не происходит двойное суммирование (клетки А3 и А6 пропускаются). Описанный инструмент позволяет нам получать как промежуточные, так и правильные окончательные итоги не прибегая к каким-либо дополнительным манипуляциям с исследуемыми данными.

2. Другое удобство проявляется при операции фильтрации, которая заключается в выделении из всего множества данных некоторого его подмножества (см. раздел 8.2). В этом случае результаты, полученные с помощью функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ, будут автоматически скорректированы, а итоги, полученные с помощью обычных функции – нет. На рис. 4-9.4 (слева) показано исходное состояние таблицы, содержащей перечень наименований товаров. Здесь в клетке А6 функция СЧЁТЗ(A2:A4), а в A7 – ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;A2:A4), которые подсчитывают число занятых ячеек и их значения, конечно, совпадают. При установлении режима фильтрации и отделении товара стул, результат (справа) функции СЧЁТЗ останется без изменений, а результат функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ будет правильно отображать число именно стульев (здесь 2 стула).

ü РАНГ(число;диапазон;порядок) – возвращает порядковый номер значения клетки среди элементов заданного диапазона. Параметр порядок определяет способ упорядочения. Если он имеет значение 0 или опущен, то Excel определяет ранг числа так, как если бы ссылка была списком, отсортированном в порядке убывания. Если порядок – любое ненулевое число, то – в порядке возрастания.

Замечание. Функция присваивает повторяющимся числам одинаковый ранг (номер), что влияет на ранг последующих чисел. Например, если в списке число 10 появляется дважды и имеет ранг 5, то 11 будет иметь ранг 7 и никакое число не будет иметь ранг 6.

Примеры (рис. 4.9-5). Здесь во второй строке вычисляется ранг соответствующей ячейки по формуле вида

В2=РАНГ(B1;$B$1:$G$1), в третьей В3=РАНГ(B1;$B$1:$G$1;1).

	A	B	C	D	E	F	G	Рис. 4.9-5
	Исходные данные:
	Ранг (убывание):							здесь 2 вторых места и ни одного третьего
	Ранг (возрастание):							здесь 2 первых места и ни одного второго

ü СУММПРОИЗВ(блок1;блок1; …) – перемножает пары элементов, включенных в блоки и возвращает их сумму. Примеры:

СУММПРОИЗВ(A1:А3;В1:В3) = A1*B1+A2*B2+A3*B3,

СУММПРОИЗВ({2;3;1};{4;5;0}) = 2*4+3*5+1*0=31.

Функции обработки дат

ü ДАТА(год;месяц;день) – возвращает дату из отдельных ее компонент, полученных, возможно, в результате вычислений. Пример: ДАТА(98;10;03)=03.10.98

ü СЕГОДНЯ() – возвращает текущую системную дату компьютера.

ü ДЕНЬНЕД(дата;начало отсчета дней в неделе) – возвращает номер дня недели из даты. Параметр начало отсчета задает номер первого дня в неделе и может принимать значения:

1 - нумерация дней от 1 (Воскресенье) до 7 (Суббота),

2 - нумерация от 1 (Понедельник) до 7 (Воскресенье),

3 - нумерация от 0 (Понедельник) до 6 (Воскресенье).

Для нас, конечно, удобно значение 2.

Пример: ДЕНЬНЕД(B8;2), ДЕНЬНЕД("8.5.2001";2) =2 (вторник).

ü ГОД(дата) – возвращает год даты в форме числа. Пример: ГОД("10.01.99") =1999.

ü МЕСЯЦ(дата) – возвращает номер месяца даты в форме числа. Пример: МЕСЯЦ("10.01.99") =1.

üü НОМНЕДЕЛИ(дата;1) – возвращает номер недели с начала года, на которую приходится заданная дата.

Пример: НОМНЕДЕЛИ("13.8.99";1) =33-я неделя.

ü ДЕНЬ(дата) – возвращает день даты в форме числа. Примеры: ДЕНЬ("10.01.99") =10.

Найти число первого дня месяца в дате, содержащейся в клетке А1 (для 25.01.99 будет получено)

1-ДЕНЬ(A1)+1 =1.01.99.

ü ДАТАЗНАЧ(текст) – преобразует текстовую форму даты в числовую.

Пример: ДАТАЗНАЧ("24.12.97")=336518 или 24.12.97

– одна и та же дата может быть отображена в числовом формате и в формате даты.

üü РАБДЕНЬ(начальная дата;число дней[;праздники])

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

Пример: РАБДЕНЬ("01.Сен.98"; 80; "07.Ноя.98")

– функция вычисляет дату, отстоящую на 80 рабочих дней от 1 сентября 1998г. с учетом праздника 7 ноября.

В число рабочих дней функция включает и день начальной даты, так РАБДЕНЬ("01.Сен.99";1) = 01.Сен.99".

üü ЧИСТРАБДНИ(начальная дата;конечная дата[;праздники]) – используется для вычисления количества рабочих дней в диапазоне от начальной до конечной даты.

Пример: ЧИСТРАБДНИ("01.03.98";"12.06.98";"01.05.98")

– функция выдает число рабочих дней от 1 марта 1998г. до 12 июня 1998г.с учетом праздника 1 мая.

Функция возвращает число рабочих дней, включая начальную и конечную даты. Если это одна и та же дата, результат – единица.

ЧИСТРАБДНИ("01.09.99";"01.09.99") =1.

Пример: Найти число рабочих дней в месяце. Пусть, для определенности, это май 1998г. Начальная дата любого месяца очевидна. Конечную удобно найти с помощью функции ДАТАМЕС(). С учетом двух праздничных дней (1 и 2 мая) можем записать

=ЧИСТРАБДНИ("1.5.98";ДАТАМЕС("1.5.98";1);{"1.5.98";"2.5.98"})

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

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

üü КОНМЕСЯЦА(начальная дата; число месяцев) – возвращает дату последнего дня месяца, отстоящего на указанное число месяцев от начальной даты. Функция используется для вычисления даты вступления в силу договора или даты платежа, которая приходится на конец месяца. Параметр число месяцев может быть отрицательным. В этом случае отсчет ведется назад.

Примеры: КОНМЕСЯЦА("18.09.97";5)=28.02.98, КОНМЕСЯЦА("18.09.97";-5)=30.04.97.

üü ДАТАМЕС(начальная дата; число месяцев) – возвращает в числовом виде дату, отстоящую на указанное число месяцев от начальной даты. Функция используется для вычисления даты вступления в силу договора или даты платежа, которая приходится на конец месяца. Параметр число месяцев может быть отрицательным. В этом случае отсчет ведется назад.

Примеры: ДАТАМЕС("18.09.97";-5) = 35538,

Чтобы представить дату в привычном виде используйте формат Дата

ДАТАМЕС("18.09.97";-5) = 18.04.97, ДАТАМЕС("18.09.97";5) = 18.02.98.

Если дня с таким числом в новом месяце нет, возвращается последний день этого месяца

ДАТАМЕС("30.01.97";1) = 28.02.97.

Здесь поскольку феврале нет 30-го числа, функция возвращает последний существующий день февраля.

Дата, отстоящая от сегодняшней на 8 месяцев ДАТАМЕС(СЕГОДНЯ();8).

ü ТДАТА() – возвращает текущую системную дату и время. Пример. ТДАТА() =2.12.99 15:06.

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

=ЕСЛИ(ТЕКСТ(B1;"ДД.ММ")=ТЕКСТ(Е4;"ДД.ММ");"Сегодня день рождения";"").

Здесь используется функция преобразования даты в текстовую форму с включением только дня и месяца (ТЕКСТ(…; "ДД.ММ")). Положим, В1=10.16.80, а Е4=10.16.99. Тогда наша функция проделает следующие вычисления

=ЕСЛИ(ТЕКСТ("10.16.1980";"ДД.ММ")=ТЕКСТ("10.16.1999";"ДД.ММ");"Сегодня день рождения";"")

или =ЕСЛИ("10.16"="10.16;"Сегодня день рождения";"").

Здесь результатом явится выражение "Сегодня день рождения".

Пример. Установить количество дней, прошедших с начала года. Здесь следует от текущей даты (клетка В1) отнять последнюю дату (т.е. 31 декабря) предыдущего года. Для этого из текущей даты выделяется текущий год, из которого отнимается 1 год (ГОД(В1)-1). Далее результат превращается в текст в формате “0000” (например, “1998”) и присоединяется к последнему дню года “31.12”. Полученный текст снова превращается в формат даты (31.12.1998) и вычитается из текущей даты.

=B1-ДАТАЗНАЧ("31.12."&ТЕКСТ(ГОД(B1)-1;"0000")).

Положим, В1=25.6.1999. Тогда (выполним последовательные преобразования данных)

=25.6.1999-ДАТАЗНАЧ("31.12."&ТЕКСТ(ГОД(25.6.1999)-1;"0000")),

=25.6.1999-ДАТАЗНАЧ("31.12."&ТЕКСТ(1998;"0000")),

=25.6.1999-ДАТАЗНАЧ("31.12.1998"),

=25.6.1999-31.12.1998=176 дней.

Финансовые функции

Финансовые функции используются в коммерческих расчетах и широко представлены в Excel. Данная группа функций позволяет учесть разную ценность денег в зависимости от времени. Здесь следует сделать важное замечание. Все рассуждения о ценности денег и анализе коммерческой ситуации имеют мало смысла, если вы храните деньги “под подушкой”. Это возможно только если они работают, т.е. вложены во что-то.

Поясним это на примере. Положим вам предлагается купить дом за 100 000$ при оплате немедленно или в кредит. В последнем случае можно заплатить сразу 50 000$ а затем вносить по 1 000$ ежемесячно в течение 60 месяцев, или сразу 30 000$, а затем по 1 000$ в течение 100 месяцев. Получается, что нужно внести соответственно 100 000$, 110 000$ или 130 000$. Однако очевидная разница в конечной цене ни о чем не говорит, поскольку она возникла как оплата кредита разной длительности (так, 130 000$ вы выплатите только через пять лет) и не факт, что лучше воспользоваться предложением, кажущимся самым дешевым (100 000$), т.е. купить дом сразу. Возможно, выгоднее купить дом в кредит, а остатки денег положить в банк под проценты, из которых можно оплачивать кредит и еще что-то заработать. Чтобы сравнить эти суммы, нужно привести их к одному моменту времени, обычно, к началу расчетов. Именно такие и подобные задачи решает финансовая математика. Хотя сфера применения финансовых функций, в основном, работа с деньгами, они с успехом могут применяться для решения и других задач, где участвуют время и проценты.

В коммерческих расчетах используются два важнейших понятия – наращивание и дисконтирование. Учет наращивания исходного капитала позволяет определить его значение в будущем. Дисконтирование решает обратную задачу – вычисление настоящей величины активов, исходя из желаемого значения в будущем. Рассмотрим суть процессов на простом примере. Пусть на банковский счет положена некоторая сумма (назовем ее первоначальным значением ПЗ) под некоторый процент, определяемый годовой процентной ставкой, на несколько лет. Последовательно определим будущее значение (БЗ) вклада на счету по прошествии N лет:

БЗ1 = ПЗ(1+ставка1) после первого года,

БЗ2 = ПЗ(1+ставка1)(1+ставка2) после второго,

БЗ2 = ПЗ(1+ставка1)(1+ставка2)(1+ставка3) после третьего,

...

БЗN = ПЗ(1+ставка1)(1+ставка2) … (1+ставкаN) после N-го.

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

БЗN = ПЗ(1+ставка)N.

Отсюда легко решить и обратную задачу – вычислить необходимое первоначальное значение вклада (ПЗ) при известном желаемом будущем значении (БЗ) после N лет хранения, т.е. произвести дисконтирование

ПЗ = БЗN/[(1+ставка1)(1+ставка2)…(1+ставкаN)] при разной ставке,

ПЗ = БЗN/(1+ставка)N при одинаковой ставке.

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

Ниже представлены некоторые важнейшие финансовые функции.

Здесь приняты следующие обозначения:

ставка – годовая процентная ставка по вкладу.

период – базовый период расчета, к которому относится процентная ставка (обычно один год).

количество периодов – число периодов для которого производятся расчеты.

выплата – вносимая/получаемая сумма. Вносимая сумма вводится со знаком минус.

ü БЗ(ставка; количество периодов; выплата; [начальное значение]; [тип]) – определяет буду­щее значение (будущую стоимость) вклада, как функцию начального значения вклада и срока хранения.

Здесь: тип – определяет время начисления процентов: в конце/на­чале (0/1) периода. Если тип опущен, он считается равным 0. Примеры:

Вычислить значение суммы на расчетном счете по истечении 6 лет, если в банке было размещено 10000 руб. под 10% годовых и начисление процентов производится один раз в год.

БЗ(10%;6;;-10000) = 17 715,61 р.

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

Условия те же, но начисление процентов производится раз в полгода.

БЗ(10%/2;6*2;;-10000) = 17 958,56 р.

То же, но вкладчику в конце каждого полугодия выплачивают сумму в 800 руб.

БЗ(10%/2;6*2;800;-10000) = 5 224,86 р.

Найти сумму на счету через три года, если конце каждого месяца вкладчиком вносится 500 руб.

БЗ(10%/12;3*12;-500) = 20 890,91 р.

То же, но в начале месяца

БЗ(10%/12;3*12;-500;;1) = 21 065,00 р.

üü БЗРАСПИС(начальный вклад; ставки) – определяет будущее значение инвестиции (начального вклада) с переменной процентной ставкой в разные периоды. Примеры:

Положим, начальный вклад, составляющий 10000 руб., инвестирован на три года под 10%, 20% и 25% годовых соответственно. По окончании всего периода он составит:

БЗРАСПИС(10000;{0,1;0,2;0,25})=16 500.