Совершенствование торгово-технологического процесса
Курсовой проект - Маркетинг
Другие курсовые по предмету Маркетинг
? эту формулу в ячейки, соответствующие другим должностям (С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!, #ИМЯ?, #ПУСТО!) и возвращает значение "истина", если значение является кодом ошибки и "ложь" в противном случае.
Таким образом, функция
ЕОШИБКА (ПОИСК(искомый текст; текст для поиска; начальная позиция))
Будет давать значение ложь, если исходный текст найден, истину если не найден.
Соответственно, функция отрицания "НЕ", взятая от функции ЕОШИБКА, будет возвращать истину и ложь на противоположных значениях аргументов, т.е.
НЕ( ЕОШИБКА (ПОИСК ("инженер"; В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).
Контрольные вопросы.
- Каков синтаксис функции ЕСЛИ()?
- Для чего нужны формулы массивов?
- Как выглядит адрес ячейки, расположенной на другом листе?
- В чем различие абсолютной и относительной адресации?
- Что вычисляет функция СЧЕТЕСЛИ()?
Лабораторная работа №3
Цель работы: научиться работать с таблицами в Ехсеl , как с базами данных.
Задание 1. Создать базу данных с записями следующей струк?/p>