Лабораторная работа 2. Работа со встроенными функциями Excel. Лабораторная работа Функции в excel цель работы освоение основных приемов работы с функциями в Excel. Функции рабочего листа
 Скачать 265.02 Kb.
|
|
Лабораторная работа № 2. Функции в EXCEL Цель работы: освоение основных приемов работы с функциями в Excel. Функции рабочего листа Microsoft Excel являются вычислительными инструментами, которые могут быть использованы в рабочем листе для автоматического принятия решений, выполнения действий и вычисления значений. Microsoft Excel предоставляет обширный набор функций, которые производят различные типы вычислений. Выбор встроенных в Excel функций осуществляется с помощью Мастера функций. Мастер функций упрощает процесс вставки функции в формулу. Для того, чтобы запустить Мастер функций, можно выбрать команду Функция в меню Вставка или воспользоваться пиктограммой панели инструментов Стандартная. Функции сгруппированы по категориям, таким как «Финансовые», «Математические и тригонометрические», «Статистические», «Логические» и др. В нижней части окна мастера функций отображается синтаксис, а также определение выделенной функции. Синтаксис функций. Синтаксисом функции называется последовательность, в которой должны располагаться аргументы функции. Величины, которые используются для вычисления значений функций, называются аргументами. Величины, которые являются результатом вычисления функций, называются возвращаемыми значениями. В строке описания синтаксиса функции обязательные аргументы выделяются полужирным шрифтом, а необязательные аргументы набраны обычным прямым шрифтом. Аргументы функции записываются в скобках через точку с запятой. Скобки должны быть парными, пробелы перед скобками или после них не допускаются. После выбора функции из списка появляется диалоговое окно выбранной функции, которое содержит поля для ввода аргументов. В нижней части окна функции отображается описание того аргумента, в поле ввода которого стоит курсор. Аргументы могут быть числами, текстами, логическими величинами или ссылками. Задаваемые аргументы должны иметь допустимые для данного аргумента значения. Аргументы могут быть как константами, так и формулами. Эти формулы, в свою очередь, могут содержать другие функции. Функции, являющиеся аргументом другой функции, называются вложенными. В формулах Microsoft Excel можно использовать до семи уровней вложения функций. 1. Функции даты и времени. Следуя описанным выше правилам использования встроенных функций Excel, выполнить следующие действия: В ячейку А1 вставить функцию СЕГОДНЯ, которая возвращает текущую дату. В ячейку В1 вставить функцию ДЕНЬНЕД. В открывшемся окне в качестве аргумента указать адрес А1. Выбрать подходящее значение аргумента Тип. Что возвращает эта функция? Определить в какой день недели вы родились? 2. Текстовые функции. Выполнить следующие операции с текстовой информацией: В ячейку А3 ввести текст «годовой отчет». В ячейку А4 вставить функцию ПОДСТАВИТЬ. В качестве первого аргумента указать адрес ячейки А3, в качестве второго - «годовой», третьего - «недельный». В ячейке А6, используя функцию ПОВТОР, получить текст,состоящий из 50 символов «#». 3. Логические функции. Функция ЕСЛИ имеет три аргумента. ЕСЛИ(логическое_выражение;значение_если_истина; значение_если_ложь) Логическое выражение – это любое значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ (например, А4>8, D3<=B2, F6=12). Если логическое выражение выполняется (т.е. логическое выражение имеет значение ИСТИНА), то функция возвращает значение второго аргумента (значение_если_истина), в противном случае (логическое выражение имеет значение ЛОЖЬ) - значение третьего аргумента (значение_если_ложь). Второго и третьего аргумента может не быть. Тогда функция возвращает значение логического выражения (истина, или ложь). 3.1.Создать таблицу анализа температуры на Листе2. Ввести заголовок «Анализ среднесуточной температуры за вторую неделю мая». Создать таблицу: Таблица 6. – Анализ среднесуточной температуры за вторую неделю мая День недели Температура Анализ день ночь среднесуточная понедельник вторник … воскресение Норма среднесуточной температуры в мае 12 Названия дней недели ввести, используя автозаполнение. Заполнить произвольными значениями ячейки, содержащие дневные и ночные температуры. Вычислить значение ячейки, содержащей среднесуточную температуру понедельника, используя функцию СРЗНАЧ из категории Статистические. Скопировать формулу в ячейки для вторника и т.д. Вычислить значение ячейки, содержащую анализ среднесуточной температуры понедельника, используя функцию ЕСЛИ (если среднесуточная температура больше или равна норме, то значение – тепло, иначе – холодно). Скопировать формулу в ячейки для остальных дней недели. (Внимание! Адресация!) Сохранить таблицу. 3.2. Создать таблицу анализа поведения курса доллара в течение года на Листе3. Ввести заголовок «Анализ поведения курса доллара в течение года» Создать шапку таблицы в следующей форме: Месяц Средний курс доллара Поведение курса доллара С помощью автозаполнения ввести названия месяцев с января по декабрь. Произвольно ввести значения среднего курса доллара по месяцам. Вычислить значение ячейки, содержащей поведение курса доллара в феврале, используя функцию ЕСЛИ (если средний курс доллара в текущем месяце больше, чем в предыдущем, то значение - вырос, если меньше – упал, если равен – без изменений). В данном случае функция ЕСЛИ будет содержать одну вложенную функцию ЕСЛИ. Т.к. в задаче имеется три взаимоисключающих условия, то необходимо проверить два из них, если оба проверяемых условия окажутся ложными, то третье – автоматически является истинным. Для того, чтобы сформировать такую функцию необходимо выполнить следующее: с помощью Мастера функций вставить функцию ЕСЛИ; в диалоговом окне функции ЕСЛИ занести в поле ввода первого аргумента соответствующее логическое выражение, в поле ввода второго аргумента – соответствующее значение; поставить курсор в поле ввода третьего аргумента и вставить вложенную функцию ЕСЛИ с помощью кнопки Функция, находящейся слева от строки формул. После этого появится новое окно вложенной функции ЕСЛИ, которое необходимо заполнить. Просмотрите полученную формулу в строке формул. Скопировать формулу для всех остальных месяцев. Сохранить таблицу. 3.3. Создать рейтинг-лист группы с нарастающим итогом и выставлением итоговой оценки на Листе4. Ввести заголовок «Рейтинг-лист». Ввести в ячейку А3 текст – «базовый рейтинг», в ячейку С3 – число 100. Ввести шапку таблицы: ФИО Баллы за тему Баллы за экзамен Сумма баллов Оцен ка Рейтинг по предмету тема1 тема2 тема3 тема4 Заполнить ячейки Ф.И.О., баллы за тему и баллы за экзамен (для 10 человек). За каждую тему можно получить от 0 до 15 баллов, за экзамен - от 0 до 40 баллов. Ввести формулу для вычисления суммы баллов. Оценка выставляется следующим образом: если сумма баллов больше 84, то значение оценки равно 5, иначе, если сумма баллов больше 72, то значение - 4, иначе, если сумма баллов больше 53, то значение - 3, иначе - 2. Чтобы реализовать эту схему, в ячейку для оценки нужно занести формулу с функцией ЕСЛИ. В качестве первого аргумента ввести условие Сумма баллов> 84. (Внимание! Под суммой баллов подразумевается адрес ячейки, где хранится сумма баллов, например, для первой фамилии из введенного списка). Если полученная сумма больше 84, то есть значение логической функции - ИСТИНА, то по условию оценка должна быть равна 5, таким образом, в качестве второго аргумента нужно ввести значение 5. В противном случае (логическое условие не выполняется) в качестве третьего аргумента необходимо вставить еще одну функцию ЕСЛИ. Для этого надо переключиться в третью строку окна функции и мышью нажать кнопку Функция, находящуюся слева от строки формул. Далее выбрать из предложенного списка функцию ЕСЛИ и аналогично заполнить аргументы для проверки второго логического условия, затем третьего и т.д. В последней вложенной функции в качестве третьего аргумента указать 2. (Внимание! Максимально возможное число вложенных функций в Excel - 7). Скопировать формулу для вычисления оценки в остальные ячейки столбца. Вычислить рейтинг по предмету в соответствии со следующим: 2 , % 10 3 , % 50 4 , оценка если базовому оценка если базовому оценка если базовому рейтинг В данном случае в качестве аргументов функции ЕСЛИ будут использоваться формулы. Скопировать формулу для рейтинга в ячейки для рейтинга остальных студентов. Под таблицей вычислить среднюю сумму баллов, используя функцию Автосуммирование, и среднюю оценку, используя функцию СРЗНАЧ из раздела Статистические. Оформить таблицу. Сохранить таблицу с рейтинг-листом на диске. |