Курсовая работа: Совершенствование торгово-технологического процесса
Аналогично Среднему доходу производится расчет в колонке Максимальный доход., только вместо функции СРЗНАЧ надо воспользоваться функцией МАКС:
{=МАКС(ЕСЛИ(Лист1!В3:В15="секретарь"; Лист1!С3:С15))}
Здесь опять пришлось воспользоваться формулой массива.
Другой способ получения среднего дохода для каждой должности – использовать функции СУММЕСЛИ() и СЧЕТЕСЛИ(), поделив их результаты.
Обращение к функции СУММЕСЛИ имеет вид:
СУММЕСЛИ (диапазон1; критерий; диапазон_суммирования)
Она позволяет получить сумму значений тех ячеек диапазона суммирования, которым соответствуют ячейки диапазона, удовлетворяющие условию. В нашем случае можно получить суммарный годовой доход работников заданной должности. Для этого в ячейку С2 листа 2 занесем формулу:
=СУММЕСЛИ(ЛИСТ1!В3:В15;"секретарь"; Лист1! С3:С15)/
СЧЕТЕСЛИ(ЛИСТ1!В3:В15;"секретарь").
В этом примере в качестве критерия мы указали константу: "секретарь". Решение будет более гибким, если в качестве критерия мы укажем адрес ячейки второго листа, содержащего соответствующую должность, а именно, В2. Тогда можно будет скопировать эту формулу в ячейки, соответствующие другим должностям (С3 –С6). Для того, чтобы копирование проходило корректно (т.е. не изменялись адреса диапазонов) нужно в адресации диапазонов указать абсолютные адреса. В ячейку С2 листа 2 надо записать формулу:
= СУММЕСЛИ(ЛИСТ1!$В$3:$В$15; В2; Лист1! $С$3:$С$15)/
СЧЕТЕСЛИ(ЛИСТ1!$В$3:$В$15; В2).
Копируем эту формулу в ячейки С3 – С6 и получаем средний доход по указанным должностям.
Задание 3. Определить количество инженеров, чей годовой доход превышает 20000.
Порядок работы.
Данное задание выполним в два этапа. Сначала на первом листе добавим столбец, в котором определим инженеров с годовым доходом превышающим 20000. Определим – это значит, поставим 1, если инженер удовлетворяет нашему условию, а всем остальным: инженерам с годовым доходом не превышающим 20000 или не инженерам с годовым доходом превышающим или не превышающим 20000 поставим 0. Делать это мы будем с помощью функции ЕСЛИ:
ЕСЛИ(И($В$3:$В$15="инженер";$С$3:$С$15>20000);1;0).
Рис. 5.
При записи условия функции ЕСЛИ нам понадобилась логическая функция И(логическое значение1; логическое значение2; …) – логическое умножение. Эта функция возвращает значение "истина", если все аргументы имеют значение "истина" и возвращает значение "ложь", если хотя бы один аргумент имеет значение "ложь".
Чтобы при копировании формулы адреса остались прежними, воспользуемся абсолютной адресацией (поставим знак $ перед буквой и цифрой, означающими адрес ячейки). При копировании абсолютные ссылки в формулах не изменяются, а относительные автоматически обновляются в зависимости от нового положения, сохраняя при этом относительную взаимосвязь между ячейками. После копирования относительные ссылки в исходной и скопированной формулах указывают на разные ячейки, что совсем нас не устраивает.
Итак, теперь у нас есть дополнительный столбец, благодаря которому мы знаем, какие инженеры подходят под наше условие.
Воспользуемся функцией СЧЕТЕСЛИ(), считая, что необходимая нам информация находится в ячейках Е3 – Е15:
СЧЕТЕСЛИ(Е3:Е15;"=1").
В рассмотренном примере будут отобраны только те сотрудники, должность которых – "инженер". Не будут учитываться сотрудники – старшие инженеры или инженеры – конструкторы и т.д. Для того, чтобы учесть и те должности, в названии которых слово "инженер" – не единственное, надо использовать критерий частичного совпадения текстов, т.е. искать такие значения должности, в которые входит слово "инженер".
Этой цели отвечает функция ПОИСК(). Она осуществляет поиск одного текста внутри другого и возвращает номер позиции, где этот текст найден. Обращение к этой функции имеет вид:
ПОИСК(искомый текст; текст для поиска; начальная позиция)
Недостатком этой функции является то, что при отрицательном результате поиска она возвращает код ошибки #ЗНАЧ! Чтобы освободиться от кода ошибки, который не воспринимается другими функциями, надо использовать функцию ЕОШ или ЕОШИБКА.
Функция ЕОШИБКА(значение) проверяет, является ли значение кодом ошибки (#Н/Д, #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ИМЯ?, #ПУСТО!) и возвращает значение "истина", если значение является кодом ошибки и "ложь" – в противном случае.