ГПР(что искать; где искать; откуда взять; как искать)

⇐ Назад

Параметр тип поиска имеет значение ИСТИНА или ЛОЖЬ. Если ЛОЖЬ, поиск в первой строке будет точным, если ИСТИНА (или параметр не задан) – приблизительным (интервальным). В этом случае найденным считается наибольшее значение, меньшее или равное искомому. Ключевая строка тогда должна быть отсортирована по возрастанию. Если этого не сделать, результаты будут непредсказуемы. Напомним, что логические значения ИСТИНА и ЛОЖЬ внутри формул могут быть заменены на 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)), которая выработает ответ – Иван. Функция ПОИСКПОЗ() ищет номер строки в столбце разрядов, в которой находится максимальный разряд рабочего.

ФИО

Разряд

Работник

Март

Апрель

Май

Июнь

Июль

Лучший месяц

Ольга

Петр

Апрель

Иван

Июнь

Олег

Май

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

Перечень ставок должен быть заключен в фигурные скобки, т.е. обозначен как массив.

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

БЗРАСПИС(B1;A4:A7).

ü ПЗ(ставка; количество периодов; [периодические выплаты];[разовая выплата]; [тип])

– определяет настоящее (текущее) значение вклада в зависимости от ожидаемого дохода в будущем. Эта функция обратна функции БЗ().

Пример. Пусть вы хотите накопить 20000$ за пять лет, положив некоторую сумму в банк при условии начисления 14% ежегодно. Найти этот начальный вклад.

ПЗ(14%;5;;20000) = -10 387,373$.

Результат отрицательный, так как эта сумма, которую нужно внести.

Проверим результат, решив обратную задачу. Определим ожидаемую сумму вклада в конце периода при тех же условиях и начальном значении вклада, равном 10387,383$.

БЗ(14%;5;;-10387,373) = 20 000,00$.

Видим – полученный результат совпадает с аргументом функции ПЗ().

Пример. Пусть две фирмы предлагают вам недвижимость на следующих условиях.

1. В течение 10 лет вы должны выплачивать по 3000$ каждые полгода.

2. В течение 15 лет выплачивать по 1500$ каждые четыре месяца.

Таким образом, всего нужно внести 3000*2*10=60000$ или 1500* 3*15= 67500$. Однако, как уже указывалось, эти цифры ничего не говорят о реальной цене покупки, поскольку стоимость денег зависит от времени их внесения. Важно значение суммы, пересчитанной на один и тот же момент времени – на начало периода. Сделаем это в предположении, что типичный банковский процент составляет 12% годовых

ПЗ(12%/2;10*2;-3000) = 34 409,76$ ПЗ(12%/3;15*3;-1500) = 31 080,06$.

Полученный результат означает, что вы заплатите 34409$ в пересчете на сегодняшний день по первому предложению и 31080$ – по второму, т.е. последнее выгоднее, несмотря на то, что в процессе возвращения кредита заплатить здесь придется больше (67500$ против 60000$).

ü ППЛАТ(ставка; количество периодов; сумма кредита;[остаток];[тип])

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

Примеры. Определить ежемесячные, ежеквартальные (четыре раза в год) и ежегодные выплаты по взятому вами кредиту в размере 100000 руб., вносимые в течении 3 лет, при годовой ставке в 6%

=ППЛАТ(6%/12; 3*12; 100000) = -3 042,19 р/месяц,

=ППЛАТ(6%/4; 3*4; 100000) = -9 168,00 р/квартал,

=ППЛАТ(6%; 3; 100000) = -37 410,98 р/год.

Информационные функции

Информационные функции полезны в формулах для проверки результатов вычислений и позволяют выяснить тип и содержимое ячеек таблицы. Если эти функции обнаруживают ожидаемое значение, они возвращают значение ИСТИНА. В противном случае – ЛОЖЬ.

ü ЕПУСТО(ссылка) – возвращает значение ИСТИНА, если ячейка, на которую указывает ссылка, пуста и ЛОЖЬ в противном случае. Пример: =ЕПУСТО(А1).

ü ЕЛОГИЧ(ссылка) – выявляет логическое значение.

ü ЕНЕТЕКСТ(ссылка) – в ячейке находится не текстовое значение или ячейка пуста.

ü ЕЧИСЛО(ссылка) – в ячейке число.

ü ЕТЕКСТ(ссылка) – в ячейке текст.

ü ЕССЫЛКА(ссылка) – возвращает ИСТИНА, если обнаружена ссылка.

Кроме перечисленных имеется функция, позволяющая прямо установить тип значения/клетки.

ü ТИП(ссылка) – возвращает число, указывающее на тип содержащегося в ячейке значения: 1 – числовое, 2 – текстовое, 3 – логическое, 8 – формула, 16 – ошибка, 64 – массив. Функция используется, когда результаты вычисления другой функции зависят от типа значения в некоторой ячейке.

Примеры: ТИП("Саша")=2, ТИП(56,12)=1, ТИП({1;2:3;4})=64.

üü ЕЧЁТН(число) – возвращает значение ИСТИНА, если число четное и ЛОЖЬ, если число нечетное.

üü ЕНЕЧЁТ(число) – возвращает значение ИСТИНА, если число нечетное и ЛОЖЬ, если число четное. Если анализируемое число не целое, дробная часть аргумента в функциях ЕЧЁТН() и ЕНЕЧЁТ() отбрасывается.

Примеры: ЕЧЁТН(3)=ЛОЖЬ, ЕЧЁТН(4)=ИСТИНА, ЕЧЁТН(3,4)=ЛОЖЬ,

ЕНЕЧЁТ(3)=ИСТИНА, ЕНЕЧЁТ(4)=ЛОЖЬ, ЕНЕЧЁТ(3,4)=ИСТИНА.

Ошибочные значения

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

#ДЕЛ/0! – попытка деления на ноль.

#ЗНАЧ! – недопустимый тип аргумента. Например, вместо числового аргумента используется текстовый.

#ИМЯ? – в формуле есть ссылка на отсутствующее имя области данных или неверно задано имя функции. Частой причиной может являться, например, ввод адресов ячеек русскими, а не латинскими буквами.

#Н/Д – неопределенные или отсутствующие данные (“нет данных”).

#ПУСТО! – в формуле задано пересечение двух интервалов, которые на самом деле не имеют общих ячеек.

#ССЫЛКА! – недопустимая (обычно отсутствующая) ссылка.

#ЧИСЛО! – используется недопустимый аргумент в числовых формулах, например, отрицательное подкоренное выражение.

Функции анализа ошибок

Эти функции позволяют установить тип возникшей ошибки. Соединяя их с функцией ЕСЛИ(), можно локализовать ошибку в формулах и затем корректно обработать ее.

ü ЕОШ(ссылка) – возвращает ИСТИНА, если обнаруживается значение любой ошибки, кроме #Н/Д.

ü ЕОШИБКА(ссылка) – возвращает ИСТИНА при любом значении ошибки (#Н/Д, #ЗНАЧ!, #ССЫЛ!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? или #ПУСТО!).

ü ЕНД(ссылка) – возвращает ИСТИНА, если обнаружено значение ошибки #Н/Д (нет данных).

ü ТИП.ОШИБКИ(ссылка) – универсальная функция, возвращающая число, соответствующее типу ошибки: #ПУСТО! – 1, #ДЕЛ/0! – 2, #ЗНАЧ! – 3, #ССЫЛ! – 4, #ЧИСЛО! – 6, #Н/Д – 7. Любое другое значение в анализируемой клетке порождает сообщение – #Н/Д.

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

ЕСЛИ(ЕНД(ГПР(A4;A1:M1;1;0)); "Рабочий"; "Нерабочий")

	A	B
	иуп	участник инвалид пенсионер
	пи	инвалид пенсионер
	ри	инвалид
	Аб	Рис. 4.14-1

не найдет значение А4 в области A1:M1, она выведет слово Рабочий (ГПР() выдаст значение #Н/Д, которое опознает функция ЕНД(), что повлечет выработку слова Рабочий). В противном случае, результатом явится сообщение Нерабочий.

Пример. Определить, присутствует или нет определенный символ в некоторой клетке (рис. 4.14-1).

Такая задача возникает, если в ней могут находиться сразу несколько признаков, влияющих на последующие вычисления. Этими признаками могут быть, например, социально важные сведения о том, является ли данный человек пенсионером, инвалидом, участником войны. Соответствующий признак присутствует в клетке в виде одной из букв: “и”, “у”, “п”. Выявление факта наличия нужной буквы в тексте может быть осуществлено с помощью функции ПОИСК(). Однако если буква не найдена, формула выработает ошибку вида #ЗНАЧ!. Чтобы ее корректно обработать, можно воспользоваться функцией ЕОШ(), которая выдаст значение ИСТИНА, если возникла ошибка. Пусть в столбце А находятся некоторые комбинации искомых и любых других символов. Нам нужно в колонке В сформировать предложение, являющееся расшифровкой соответствующих символов. Если для клетки В1 использовать формулу

B1 =ЕСЛИ(ЕОШ(ПОИСК("у";A1)); ""; "участник ") &ЕСЛИ(ЕОШ(ПОИСК("и";A1)); ""; "инвалид ")

&ЕСЛИ(ЕОШ(ПОИСК("п";A1)); ""; "пенсионер ")

и аналогичные ей в следующих (В2, В3, В4) клетках, результат можно увидеть на рис. 4.14-1. Каждый ненайденный символ превращается функцией в текстовую строку нулевой длины (пустые кавычки).

Работа с массивами

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

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

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

A	B	C	D	E	F	G
ФИО	Разряд	Отдел	Пол	Зарплата		Мин. зарп.
Ольга		Склад	ж
Иван		Дирекция	м
Лена		Склад	ж
Олег		Канцелярия	м
Света		Дирекция	ж			Рис. 4.15-1

=СЧЁТЕСЛИ(D2:D6;"ж") и

=СУММЕСЛИ(D2:D6;"ж";E2:E6).

Для этих задач они работают, однако таким образом мы не можем построить более сложный запрос, найти, например, зарплату всех работников, получающих от … и до … и т.п. Если прибегнуть к массивам, можно применять функции СЧЁТ(), МАКС(), СУММ() и другие “итоговые” функции, включив в них необходимые условия отбора. Так, подсчет женщин может быть выполнен следующим образом

{=СЧЁТ(ЕСЛИ(D2:D6="ж";1))} и {=СУММ(ЕСЛИ(D2:D6="ж";1))}.

Здесь все клетки, содержащие в анализируемом диапазоне D2:D5 букву “ж”, будут участвовать в подсчете и в суммировании как единицы.

Общая зарплата всех женщин рассчитывается так

{=СУММ(ЕСЛИ(D2:D6="ж";E2:E6))}.

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

Таким образом, если нужно вычислить количество некоторых элементов, в качестве аргумента функции ЕСЛИ() удобно использовать 1, если сумму – сами эти элементы, содержащие нужные данные. Хотя в функциях, работающих с массивами, нельзя применять логические функции И(), ИЛИ(), НЕ(), зато можно использовать вложенные функции ЕСЛИ().

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

Примеры.

1. Вычислить число женщин, зарабатывающих более 1000 руб.,

{=СУММ(ЕСЛИ(E2:E6>1000; ЕСЛИ(D2:D6="ж";1)))}

или {=СЧЁТ(ЕСЛИ(E2:E6>1000; ЕСЛИ(D2:D6="ж";1)))},

и их суммарный заработок

{=СУММ(ЕСЛИ(E2:E6>1000; ЕСЛИ(D2:D6="ж";E2:E6)))}.

В этих выражениях в косвенной форме реализована функция И. Формулы можно упростить, используя вместо двух только одну функцию ЕСЛИ, где в качестве аргумента взято произведение результатов анализа условий.

{=СУММ(ЕСЛИ((E2:E6>1000)*(D2:D6="ж");1))}

{=СЧЁТ(ЕСЛИ((E2:E6>1000)*(D2:D6="ж");1))}

{=СУММ(ЕСЛИ((E2:E6>1000)*(D2:D6="ж");E2:E6))}.

Поскольку логическое значение ИСТИНА интерпретируется Excel как единица, то для ячеек в диапазоне Е2:Е6, удовлетворяющих условию >1000, будет получена 1. Аналогично для диапазона D2:D6=”ж”. Таким образом, если оба условия истинны, аргументом функции ЕСЛИ будет произведение 1*1=1. Это значение результата будет расценено как истинное, и суммирование (СУММ) или подсчёт (СЧЁТ) будут выполнены. Таким образом, операция умножения здесь полностью аналогична логической функции И.

2. Вычислить число женщин, имеющих третий разряд,

{=СУММ(ЕСЛИ((B2:B6=3)*(D2:D6="ж");1))}.

3. Вычислить число работников, получающих от 1000 до 2000 руб., и их суммарный заработок

{=СУММ(ЕСЛИ((E2:E6>1000)*(E2:E6<=2000);1))}

{=СУММ(ЕСЛИ((E2:E6>1000)*(E2:E6<=2000);E2:E6))}.

4. Найти число женщин, работающих на складе, и их заработок

{=СУММ(ЕСЛИ((C2:C6="Склад")*(D2:D6="ж");1))}

{=СУММ(ЕСЛИ((C2:C6="Склад")*(D2:D6="ж");E2:E6))}.

5. Определить число людей, работающих на складе и в дирекции,

{=СУММ(ЕСЛИ(C2:C6="Склад";1))+СУММ(ЕСЛИ(C2:C6="Дирекция";1))}.

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

{=СУММ(ЕСЛИ((C2:C6="Склад")+(C2:C6="Дирекция");1))}.

Впрочем, эту задачу можно решить и не прибегая к массивам

=СЧЁТЕСЛИ(C2:C6;"Склад")+СЧЁТЕСЛИ(C2:C6;"Дирекция").

6. Определить число женщин, работающих на складе и в дирекции

{=СУММ(ЕСЛИ((D2:D6="ж")*((C2:C6="Склад")+(C2:C6="Дирекция"));1))}.

Здесь фактически реализовано выражение вида

{=СУММ(ЕСЛИ(пол="ж" И (отдел="Склад" ИЛИ отдел="Дирекция");1))}.

7. Найти число работников, получающих зарплату в размере менее пяти минимальных зарплат (клетка G2),

{=СУММ(ЕСЛИ(E2:E6<5*G2;1)).

8. Определить число работников, получающих зарплату меньше средней по предприятию,

{=СУММ(ЕСЛИ(E2:E6<СРЗНАЧ(E2:E6);1))}.

9. Найти число наиболее высокооплачиваемых (входящих в верхние 10% по размеру зарплаты, начиная от 0 руб.) сотрудников

{=СУММ(ЕСЛИ(E2:E6>МАКС(E2:E6)*(1-10%);1))}.

10. Найти максимальную зарплату, получаемую женщинами, работающими в дирекции

{=МАКС(ЕСЛИ((C2:C6="Дирекция")*(D2:D6="ж");E2:E6))}.

11. Найти наибольший разряд, среди женщин, работающих в дирекции

{=МАКС(ЕСЛИ((C2:C6="Дирекция")*(D2:D6="ж");B2:B6))}.

Решите следующие задачи самостоятельно.

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

Рассмотрим пример “из жизни”. Пусть для последующего расчета зарплаты на производстве заполняется табельная ведомость (рис. 4.15-2), куда в столбцы с В по AF вносится число отработанных сотрудниками часов в каждом из дней месяца (всего 31 день).

Наша задача – подсчитать число всех отработанных дней и часов; число дней и часов, отработанных в выходные дни; число сверхурочных часов отработанных всего и в выходные дни. Имея эти данные, затем легко вычислить зарплату (здесь не рассматриваем). Расчет всех дней и часов не вызывает проблем. Для первого рабочего это

AG2=СЧЁТ(B2:AF2), AH2=СУММ(B2:AF2).

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

AI2 {=СЧЁТ(ЕСЛИ((ДЕНЬНЕД(B$1:AF$1;2)>5)*(B2:AF2>0);1))}.

Дата

01.авг.Вс

02.авг.Пн

03.авг.Вт

04.авг.Ср

05.авг.Чт

06.авг.Пт

07.авг.Сб

08.авг.Вс

. . . .

Всего дней

Всего часов

Дней в выходные

Часов в выходные

Всего сверхурочных

Сверхурочных в выходные

Петр

36ч

22ч

12ч

6ч

Иван

29ч

12ч

0ч

Олег

30ч

0ч

6ч

0ч

Рис. 4.15-2

Здесь подсчитывается число дней, для которых номер дня недели больше 5 (т.е. субботы и воскресенья) и в которых было отработано какое-то (>0) число часов. Более просто находится количество часов в эти дни, поскольку второе условие здесь не нужно

AJ2 {=СУММ(ЕСЛИ(ДЕНЬНЕД(B$1:AF$1;2)>5;

B2:AF2))}.

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

AK2 {=СУММ(ЕСЛИ(B2:AF2>8;B2:AF2-8))}.

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

AL2 {=СУММ(ЕСЛИ((ДЕНЬНЕД(B$1:AF$1;2)>5)*(B2:AF2>8);B2:AF2-8))}.

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

Матричные функции

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

ü МУМНОЖ(матрица1;матрица2) – возвращает произведение матриц. Число столбцов матрицы1 должно совпадать с числом строк матрицы2. Результирующая матрица будет иметь столько же строк, как матрица1, и число столбцов, как матрица2.

ü МОБР(матрица) – возвращает матрицу, обратную к данной. Исходная (и полученная) матрица может быть только квадратной, т.е. имеющей одинаковое число строк и столбцов. Не все матрицы имеют обратную (в этом случае будет выдано сообщение об ошибке #ЧИСЛО!). Перемножение обратной и прямой матриц даст единичную матрицу (матрицу, у которой на главной диагонали находятся единицы, а остальные элементы – нули).

ü ТРАНСП(матрица)– транспонирует исходную прямоугольную матрицу, поворачивая ее относительно главной диагонали.

ü МОПРЕД(матрица)– вычисляет определитель исходной прямоугольной матрицы.

Все функции, результатами которых являются матрицы (МУМНОЖ(), МОБР(), ТРАНСП()), должны быть введены как формулы массива в следующей последовательности:

– выделяется блок, где будет размещен результат;

– в текущую ячейку вводится функция;

– нажимаются клавиши Shift+Ctrl+Enter (при этом введенная формула автоматически обрамляется фигурными скобками).

Примеры. На рис.4.16-1 исходная матрица размещена в блоке А2:В3.

⇐ Назад