Индекс(г90. $a$2:$d$5; поискпоз(c2;г90. $a$2:$a$5;1);поискпоз(d2;г90. $a$2:$d$2;1))))

Пример 5.38. Формирование обобщающих сводок. Положим, на листе ЗАРП имеется таблица, отражающая ежедневные заработки рабочих. Задача состоит в том, чтобы на листе СВОДКА сформировать обобщенные данные, где каждый работник представлен только одной строкой, содержащей число отработанных дней (всего и в выходные) и сумму зарплаты (также всего и в выходные). Здесь для этой цели используются формулы счёта и суммирования с условием, а в более сложных случаях – функции обработки массивов. Рассмотрим их на примере расчетов для Петра. Подсчитаем количество отработанных им дней

Всего дней отработано Петром = подсчет числа ячеек в А2:А10, где внесен Петр

	A	B	C	D	E
	Сводка за месяц
	Имя	Рабочих дней	Зарплата
	Всего	Выходных	Всего	В вых.
	Петр
	Иван
	Олег
	Сергей
	Итого
		Рис.5.3	8.б СВОДК	А

или B4=СЧЁТЕСЛИ(зарп!A$2:A$10;A4).

Здесь обратим внимание на то, что в область подсчета включен заголовок (клетка А2) и итоговая строка Всего (клетка А10). Это позволит нам не беспокоиться об изменении размеров таблицы на листе ЗАРП – все новые строки, вставленные между строкой 2 и 10, автоматически попадут в область подсчета сводки.

Для подсчета числа рабочих дней, приходящихся на выходные, нам придется воспользоваться функцией для массивов, поскольку здесь анализируются одновременно два условия – имя работника (Петр) и номер дня недели (больше 5)

Дней в выходные отработано Петром=

подсчет ячеек в А2:А10, где внесен Петр И день=выходной

или C4 {=СЧЁТ(ЕСЛИ((зарп!A$2:A$10=A4)*(ДЕНЬНЕД(зарп!B$2:B$10;2)>5);1))}.

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

D4=СУММЕСЛИ(зарп!A$2:A$10;A4;зарп!C$2:C$10),

E4 {=СУММ(ЕСЛИ((зарп!A$2:A$10=A4)*(ДЕНЬНЕД(зарп!B$2:B$10;2)>5);зарп!C$2:C$10;0))}.

Вертикальные суммы в обоих листах должны строиться таким образом, чтобы вставки/удаления строк не требовали изменения формул. Ранее мы подробно (в начале главы 6) уже обсуждали этот вопрос. Еще один подход применен ниже для листа СВОДКА

В8=СУММЕСЛИ($A$3:$A$8; "<>Итого"; $B$3:$B$8), С8=СУММЕСЛИ($A$3:$A$8; "<>Итого"; $C$3:$C$8),

D8=СУММЕСЛИ($A$3:$A$8; "<>Итого"; $D$3:$D$8), E8=СУММЕСЛИ($A$3:$A$8; "<>Итого"; $E$3:$E$8).

Здесь в область обработки включена и сама строка Итого (это обеспечивает возможность вставки новых строк без необходимости корректировки итогового выражения), однако в суммировании сама она не участвует – складывается только содержимое клеток, для которых в столбце А нет слова Итого (условие “<>Итого”).

Пример 5.39. Обобщение данных о выручке. Рассмотрим другую похожую задачу. Пусть на листе ПРОДАЖИ последовательно фиксируются факты продаж недвижимости (в тысячах рублей) агентами риэлторской фирмы, а в листе ИТОГИ подсчитываются суммы продаж по месяцам для каждого из работников.

Так, продажи Петра в январе подсчитываются формулой

B3 {=СУММ(ЕСЛИ((продажи!$A$2:$A$9=$A3)*(МЕСЯЦ(продажи!$B$2:$B$9)=B$2);продажи!$C$2:$C$9))}.

Остальные клетки листа заполняются путем копирования В3 в область итогов по месяцам B3:E6, например,

C4 {=СУММ(ЕСЛИ((продажи!$A$2:$A$9=$A4)*(МЕСЯЦ(продажи!$B$2:$B$9)=C$2);продажи!$C$2:$C$9))}.

Итоговые функции выглядят следующим образом:

F3=СУММЕСЛИ($A$2:$F$2;">0";$A3:F3), B7=СУММ(ИНДЕКС(B:B;3):ИНДЕКС(B:B;СТРОКА()–1)).

В F3 условие ">0" обеспечивает суммирование только тех клеток, которые в заголовке имеют цифры (т.е. номера месяцев). Клетки, содержащие текстовые данные (Месяц и å) интерпретируются Excel как нули. В B7 блок суммирования ограничен функциями ИНДЕКС(), которые гарантируют правильный диапазон суммирования при включении в таблицу новых строк. В данном случае в качестве начального адреса суммирования нельзя было использовать клетку B2, поскольку в ней находится число (номер месяца), которое, естественно, не должно суммироваться. Здесь применяется функция ИНДЕКС(B:B;3), ссылающаяся на клетку B3. Правильность вычислений подтверждает совпадение общих сумм продаж в листах Продажи и Итоги.

	A	B	C			A	B	C	D	E	F
	Продажи			Итоги по месяцам
	Продавец	Дата	å			Месяц:					å
	Петр	12.янв				Петр
	Иван	04.фев				Иван
	Олег	20.фев				Олег
	Иван	03.мар				Сергей
	Олег	12.мар				Итого
	Петр	30.мар
	Всего	6 дней				Рис. 5.39б. Лист ИТОГИ
Рис. 5.39а. Лист ПРОДАЖИ

Пример 5.40. Текущий анализ продаж и обобщение данных. Положим, при оптовой торговле менеджер вводит имя фирмы-поку­па­теля, дату продажи и сумму покупки (Текущий платеж). Сделаем так, чтобы Excel сам подставлял номер сделки по порядку (столбец А), а также для каждой новой строки отображались: общее число про­даж, осуществленных с данной организацией; нарастающая сумма продаж (нужно для вычисления скидок). Кроме того, в столбце Лидер продаж фиксируется имя фирмы, сделавшей в общей сложности закупки на максимальную сумму, а в столбце H – дата последней сделки с этой фирмой. Рабочие формулы приведены ниже

A3=СЧЁТ(A$2:A2)+1, E3=СЧЁТЕСЛИ(B$3:B3;B3), F3=СУММЕСЛИ(B$3:B3;B3;D$3:D3),

G3=ЕСЛИ(МАКС(F$3:F3)=F3;B3;), H3 {=МАКС(ЕСЛИ(B$2:B2=B3;C$2:C2))}.

Формула (А3) для автонумерации строк, записанная в таком виде, будет работать только при внесении новых строк в конец таблицы. При дополнении таблицы в любом другом месте перенумерация нижележащих строк осуществляться не будет. Чтобы это стало возможным, ее придется усложнить A3=СЧЁТ(A$2:ИНДЕКС(A:A;СТРОКА()–1))+1.

 

	A	B	C	D	E	F	G	H
	№	Фирма	Даты продаж	Текущий платеж	Нарастающий итог	Лидер продаж	Преды- дущая продажа
	Число продаж	Сумма продаж
		Весна	01.май				Весна
		Старт	02.май
		Старт	20.май				Старт	02.май
		Весна	02.май				Весна	01.май
		Спорт	18.май
		Старт	28.май					20.май
		Весна	03.июнь				Весна	02.май
		Весна	03.июнь				Весна	Рис. 5.40 03.июнь
		Спорт	06.июнь					18.май

 

	A	B	C	D	E	F	G	H	I	J
	Расписание поездов		Заявки пассажиров
	Время отправлен.	Номер поезда	Город назначения	Вид		Город назначения	Вид	Время отправления	Номер поезда
		желаемое	ближайшее возможное
	6:05		Рязань	э		Рязань	э	9:36	20:30
	9:00		Киев	п		Курск	п	7:12	11:05
	11:05		Курск	п		Киев	п	12:00	15:40
	15:40		Киев	п
	20:30		Рязань	э
	21:00		Курск	э
	23:30		Рязань	п					Рис. 5.

Пример 5.41. Продажа железнодорожных билетов. В кассе вокзала требуется подобрать пассажиру нужное время отправления (I4) и номер поезда (J4), в заданный город назначения (F4), на желаемом виде поезда (G4). Последний может быть почтовым (п) или экспрессом (э). Пассажир указывает удобное для него время отправления (H4), начиная с которого можно подбирать поезда. Все требования клиента фиксируются в столбцах F:H, а рас­писание – в столбцах А:D. Таким образом, нам следует выделить нужное подмножество поездов из которых затем выбрать номер поезда с самым ближним временем отправления, т.е. как бы сформировать фильтр (не прибегая, однако к прямой фильтрации данных).

Последовательно построим необходи­мые выражения. Сначала вычислим ближайшее подходящее время отправления. Для этого сформируем подмножество выбора поездов для первой заявки на билеты (в Рязань). Это

Город_назначения="Рязань" И Вид_поезда="э" И Ближайшее_время_отправления ³ Желаемое_время_отправления.

Поскольку при работе с массивами нельзя использовать логические функции И(), строим нужное выражение применяя вложенные функции ЕСЛИ()

=ЕСЛИ(город_назначения="Рязань"; ЕСЛИ(вид_поезда="э";ЕСЛИ(время_отправления ³ время_желаемое; …)))

или =ЕСЛИ(C4:C10=F4;ЕСЛИ(D4:D10=G4;ЕСЛИ(A4:A10>=H4; …))).

Теперь находим ближайшее возможное время отхода поезда, применяя функцию МИН() к столбцу А расписания, содержащему время отправления (блок A4:A10) поездов

I4 {=МИН(ЕСЛИ(C$4:C$10=F4;ЕСЛИ(D$4:D$10=G4;ЕСЛИ(A$4:A$10>=H4;A$4:A$10))))}.

Или короче, если использовать операцию умножения

I4 {=МИН(ЕСЛИ((C$4:C$10=F4)*(D$4:D$10=G4)*(A$4:A$10>=H4);A$4:A$10))}.

Имея время отправления (I4), далее легко найти номер поезда J4=ВПР(I4;A$4:B$10;2;1).

Если время отправления не нужно, в I4 можно сразу показать номер поезда

I4 {=МИН(ЕСЛИ((C$4:C$10=F4)*(D$4:D$10=G4)*(A$4:A$10>=H4);B$4:B$10))}.

Кроме рассмотренных функций и приемов обобщения данных, Excel располагает и специальными мощными инструментами для этих целей (инструменты Фильтрация, Итоги, Консолидация, Сводная таблица).