Порядок работы. Данное задание выполним в два этапа

Данное задание выполним в два этапа. Сначала на первом листе добавим столбец, в котором определим инженеров с годовым доходом превышающим 20000. Определим – это значит, поставим 1, если инженер удовлетворяет нашему условию, а всем остальным: инженерам с годовым доходом не превышающим 20000 или не инженерам с годовым доходом превышающим или не превышающим 20000 поставим 0. Делать это мы будем с помощью функции ЕСЛИ:

ЕСЛИ(И($В$3: $В$15=”инженер”; $С$3: $С$15> 20000); 1; 0).

Рис. 5.

При записи условия функции ЕСЛИ нам понадобилась логическая функция И(логическое значение1; логическое значение2; …) – логическое умножение. Эта функция возвращает значение “истина”, если все аргументы имеют значение “истина” и возвращает значение “ложь”, если хотя бы один аргумент имеет значение “ложь”.

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

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

Воспользуемся функцией СЧЕТЕСЛИ(), считая, что необходимая нам информация находится в ячейках Е3 – Е15:

СЧЕТЕСЛИ(Е3: Е15; ”=1”).

В рассмотренном примере будут отобраны только те сотрудники, должность которых – “инженер”. Не будут учитываться сотрудники – старшие инженеры или инженеры – конструкторы и т.д. Для того, чтобы учесть и те должности, в названии которых слово “инженер” – не единственное, надо использовать критерий частичного совпадения текстов, т.е. искать такие значения должности, в которые входит слово “инженер”.

Этой цели отвечает функция ПОИСК(). Она осуществляет поиск одного текста внутри другого и возвращает номер позиции, где этот текст найден. Обращение к этой функции имеет вид:

ПОИСК(искомый текст; текст для поиска; начальная позиция)

Недостатком этой функции является то, что при отрицательном результате поиска она возвращает код ошибки #ЗНАЧ! Чтобы освободиться от кода ошибки, который не воспринимается другими функциями, надо использовать функцию ЕОШ или ЕОШИБКА.

Функция ЕОШИБКА(значение) проверяет, является ли значение кодом ошибки (#Н/Д, #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ИМЯ?, #ПУСТО!) и возвращает значение “истина”, если значение является кодом ошибки и “ложь” – в противном случае.

Таким образом, функция

ЕОШИБКА (ПОИСК(искомый текст; текст для поиска; начальная позиция))

Будет давать значение ложь, если исходный текст найден, истину – если не найден.

Соответственно, функция отрицания “НЕ”, взятая от функции ЕОШИБКА, будет возвращать истину и ложь на противоположных значениях аргументов, т.е.

НЕ(ЕОШИБКА (ПОИСК (“инженер”; В3; 1))) возвращает значение “истина”, если в тексте значения ячейки В3 присутствует слово “инженер”. Если в ячейке В3 слова “инженер” нет, то возвращается “ложь”.

Нам требуется отобрать инженеров с доходом > 20000, т.е. нужно одновременное выполнение двух условий. Поэтому в качестве условия в функции ЕСЛИ необходимо использовать логическую функцию “И”, осуществляющую логическое умножение нескольких логических аргументов.

Таким образом, функция ЕСЛИ, помещаемая в ячейку Е3, примет вид:

ЕСЛИ(И(НЕ(ЕОШИБКА(ПОИСК “инженер”; В3; 1))); С3> 20000); 1; 0).

Скопируем эту формулу в ячейки Е4: Е15. получим столбец, состоящий из 0 и 1. Теперь остается только подсчитать количество единиц или, что то же самое, подсчитать сумму содержимого ячеек этого диапазона:

СУММ(Е3: Е15).

Таким образом, получим количество специалистов, должность которых содержит слово “инженер”, которые имеют годовой доход > 20000.

Задание 4. Определить, насколько самый большой доход превышает самый маленький (по формуле).

Порядок работы.

В табличном процессоре Excel содержится большой выбор различных функций. Есть функции, которые позволяют определить наибольшее число из заданных (функция МАКС()) и наименьшее число (функция МИН()). Поэтому формула следующая:

МАКС(С3: С15)-МИН(С3: С15).

Контрольные вопросы.

1. Каков синтаксис функции ЕСЛИ()?

2. Для чего нужны формулы массивов?

3. Как выглядит адрес ячейки, расположенной на другом листе?

4. В чем различие абсолютной и относительной адресации?

5. Что вычисляет функция СЧЕТЕСЛИ()?