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