Совершенствование торгово-технологического процесса

Курсовой проект - Маркетинг

Другие курсовые по предмету Маркетинг

? эту формулу в ячейки, соответствующие другим должностям (С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).

 

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

  1. Каков синтаксис функции ЕСЛИ()?
  2. Для чего нужны формулы массивов?
  3. Как выглядит адрес ячейки, расположенной на другом листе?
  4. В чем различие абсолютной и относительной адресации?
  5. Что вычисляет функция СЧЕТЕСЛИ()?

 

Лабораторная работа №3

 

Цель работы: научиться работать с таблицами в Ехсеl , как с базами данных.

Задание 1. Создать базу данных с записями следующей струк?/p>