Работа с массивами Excel, страница 2

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

{=CЧET(EСЛИ(D2:D5=ж;1))} и {=СУММ(ЕСЛИ(D2:D5="ж";1))}.

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

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

{=СУММ(ЕСЛИ(D2:D5="ж";Е2:Е5))}.

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

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

Следует помнить:

заканчивать ввод формул по обработке массивов следует нажатием трёх клавиш

Ctrl + Shift + Enter

Примеры.

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

{=СУММ(ЕСЛИ(Е2:Е5>10000;ЕСЛИ(D2:D5="ж";1)))}

или     {=СЧЁТ(ЕСЛИ(Е2:Е5>10000; ЕСЛИ (D2:D5=">k";1)))},

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

{=СУММ(ЕСЛИ(Е2:Е5>10000; ЕСЛИ(D2:D5="ж";Е2:Е5)))}.

2.  Вычислить число женщин, имеющих третий разряд,
 {=СУММ(ЕСЛИ(В2:В5=3;ЕСЛИ(D2:D5="ж";1)))}.

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

{=СУММ(ЕСЛИ(Е2:Е5>10000; ЕСЛИ(Е2:Е5<=20000;1)))}

{=СУММ(ЕСЛИ(Е2:Е5>10000; ЕСЛИ(Е2:Е5<=20000;Е2:Е5)))}.

4.  Найти число женщин, работающих на складе, и их заработок
 {=СУММ(ЕСЛИ(С2:С5="Склад";ЕСЛИ(D2:D5="ж";1)))}
 {=СУММ(ЕСЛИ(С2:С5="Склад"; ЕСЛИ(D2:D5="ж"; Е2:Е5)))}.

5. Определить число людей, работающих на складе и в дирекции,
{=СУММ(ЕСЛ И(С2:С5="Склад"; 1)) + СУММ(ЕСЛИ(С2:С5="Дирекция";1))}.

Впрочем, эту задачу можно решить и не прибегая к массивам =СЧЁТЕСЛИ(С2:С5;"Склад")+СЧЁТЕСЛИ(С2:С5;"Дирекция").

6. Определить число женщин, работающих на складе и в дирекции
{=СУММ(ЕСЛИ(С2:С5="Склад"; EСЛИ(D2:D5="ж"; 1)))+

СУММ(ЕСЛИ(С2:С5="Дирекция";ЕСЛИ(D2:D5="ж";1)))}.

7.  Найти число работников, получающих зарплату в размере менее пяти минимальных зарплат   (клетка G2),
 {=СУММ(ЕСЛИ(Е2:Е5<5*G2;1)).

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

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

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

 начиная от 0 руб.) сотрудников

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

10. Найти максимальную зарплату, получаемую женщинами, рабо­тающими в дирекции
 {=МАКС(ЕСЛИ(С2:С5="Дирекция";ЕСЛИ(D2:D5="ж";Е2:Е5)))}.

11.   Найти наибольший разряд, среди женщин, работающих в дирекции
 {=МАКС(ЕСЛИ(С2:С5="Дирекция";ЕСЛИ(D2:D5="ж";В2:В5)))}.