185Атм3,6т4т5тВсе адреса здесь относятся к сечению нормативной таблицы, относя­ще­муся к режиму в 70 градусов, поскольку именно такова темпе­ратура в процессе 1.

Усложним формулу, заменив непосредственные адреса на ссылки, применяя функцию ДВССЫЛ()

=ИНДЕКС(ДВССЫЛ(нормы!B3); ПОИСКПОЗ(C2;ДВССЫЛ(нормы!B4);1); ПОИСКПОЗ(D2;ДВССЫЛ(нормы!B5);1)).

Здесь используется не сама таблица (для 70º), а ссылки на нее, находящиеся в верхней части листа НОРМЫ.

Пока еще функция не позволяет работать с другими температурными режимами. Чтобы это было возможным, мы заменим фиксированные адреса нормы!$B$3, $B$4 и $B$5 на адреса, найденные функцией ГПР()

E2= ИНДЕКС(ДВССЫЛ(ГПР(B2;нормы!$B$2:$D$5;2;1));

ПОИСКПОЗ(C2;ДВССЫЛ(ГПР(B2;нормы!$B$2:$D$5;3;1));1);

	A	B	C
1	Зарплата по дням
2	Имя	Дата	Сумма
3	Петр	1.9 Ср	50
4	Иван	2.9 Чт	65
5	Петр	3.9 Пт	20
6	Петр	4.9 Сб	40
7	Иван	5.9 Вс	35
8	Олег	6.9 Пн	40
9	Петр	7.9 Вт	70
10	Всего	7	320

Рис. 5.38а. Лист ЗАРП

ПОИСПОЗ(D2;ДВССЫЛ(ГПР(B2;нормы!$B$2:$D$5;4;1));1)).

Именно это выражение и использовано в таблице на листе ВЕС. Оно может с сохранением функциональности беспрепятственно копироваться во все другие ее строки.

К оформлению. 1. Единицы измерения атм, мин, т и гр при параметрах процесса, конечно, являются не элементами данных, а пользовательскими форматами. 2. Если справочные таблицы велики, их удобнее располагать каждую на отдельном листе.

Когда справочных таблиц немного, то можно, используя функцию ЕСЛИ( ) сразу записать необходимую формулу без формирования текстовых ссылок. В нашем случае можно создать листы содержащие данные для температурных режимов от 70 градусов, от 80-ти и от 90 (рис. 6.38г). Дадим им имена г70, г80 и г90 соответственно. Лист НОРМЫ более не нужен. Выражение для извлечения необходимых данных строится следующим образом

вес=ЕСЛИ(температура < 80, данные извлекаются из листа г70;

ЕСЛИ(температура < 90, данные извлекаются из листа г80;

иначе данные извлекаются из листа г90)).

Или в виде формулы

E2=ЕСЛИ(B2<80;ИНДЕКС(г70!$A$2:$D$5; ПОИСКПОЗ(C2;г70!$A$2:$A$5;1);ПОИСКПОЗ(D2;г70!$A$2:$D$2;1));

ЕСЛИ(B2<90;ИНДЕКС(г80!$A$2:$D$5; ПОИСКПОЗ(C2;г80!$A$2:$A$5;1);ПОИСКПОЗ(D2;г80!$A$2:$D$2;1));

ИНДЕКС(г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
1	Сводка за месяц
2	Имя	Рабочих дней		Зарплата
3		Всего	Выходных	Всего	В вых.
4	Петр	4	1	180	40
5	Иван	2	1	100	35
6	Олег	1	0	40	0
7	Сергей	0	0	0	0
8	Итого	7	2	320	75
		Рис.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
1	Продажи				1	Итоги по месяцам
2	Продавец	Дата			2	Месяц:	1	2	3	4	
3	Петр	12.янв	300		3	Петр	300	0	200	0	500
4	Иван	04.фев	400		4	Иван	0	400	250	0	650
5	Олег	20.фев	300		5	Олег	0	300	450	0	750
6	Иван	03.мар	250		6	Сергей	0	0	0	0	0
7	Олег	12.мар	450		7	Итого	300	700	900	0	1900
8	Петр	30.мар	200
9	Всего	6 дней	1900			Рис. 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
1	№	Фирма	Даты продаж	Текущий платеж	Нарастающий итог		Лидер продаж	Преды- дущая продажа
2					Число продаж	Сумма продаж
3	1	Весна	01.май	20	1	20	Весна
4	2	Старт	02.май	10	1	10
5	3	Старт	20.май	50	2	60	Старт	02.май
6	4	Весна	02.май	100	2	120	Весна	01.май
7	5	Спорт	18.май	30	1	30
8	6	Старт	28.май	50	3	110		20.май
9	7	Весна	03.июнь	60	3	180	Весна	02.май
10	8	Весна	03.июнь	20	4	200	Весна	0 Рис. 5.40 3.июнь
11	9	Спорт	06.июнь	10	2	40		18.май

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

Пример 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 располагает и специальными мощными инструментами для этих целей (инструменты Фильтрация, Итоги, Консолидация, Сводная таблица).

–72–