 |
Задание № 2. Встроенные функции Excel
|
|
|
|
1. Изучите группы команд на вкладке Формула.
2. Ознакомьтесь с категориями встроенных функций, которыми обладает Еxcel.
3. Выполните задания 2.1-2.4, направленные на применение статистических и логических функций.
4. Выполните задания 2.5, 2.6, научитесь применять математические функции для расчетов, строить графики.
5. На примере задания 2.7 ознакомьтесь с понятием Условное форматирование.
Задание 2.1. Создайте таблицу и выполните ряд расчетов с применением следующих функций:
СУММ (диапазон) - суммирует данные из диапазона;
МАКС (диапазон) – возвращает наибольшее значение из списка аргументов;
МИН (диапазон) – возвращает наименьшее значение из списка аргументов;
СРЗНАЧ (диапазон) – возвращает среднее арифметическое своих аргументов;
СЧЁТЕСЛИ (диапазон; условие) - подсчитывает в диапазоне количество ячеек, отвечающих условию;
ЕСЛИ (условие; оператор1; оператор2) - если условие истинно, выполняется оператор1, иначе оператор2;
И (условие1;…; условие K) - истина, если все K условий истинны, иначе ложь.
Рассчитайте средние затраты, наибольшее, наименьшее значение стоимости товара. В колонке «Примечание», используя функцию ЕСЛИ, выведите сообщение «Превышение бюджета», если фактические затраты больше предполагаемых, и «ОК» в противном случае:
Задание 2.2. Создайте следующую таблицу. Заполните колонку «Оценка», используя функции ЕСЛИ и И:
более 90 баллов – «отлично»;
70-90 баллов – «хорошо»;
40-69 балла – «удовлетворительно»;
менее 40 баллов – «неудовлетворительно».
В примечании отметьте «Прошел по конкурсу», если получена оценка «хорошо» или «отлично».
В задании 2, чтобы определить оценку по количеству баллов, придется использовать функцию ЕСЛИ многократной степени вложенности.
|
|
|
Примечание:
Например, в следующей формуле используются две функции ЕСЛИ:
=ЕСЛИ(A2>27;"Сегодня жарко"; ЕСЛИ (И(A2>18;А2<=27); "Сегодня тепло"; "Сегодня прохладно")).
Формула читается следующим образом: «Если значение в ячейке А2 больше 27, выводится строка «Сегодня жарко». В противном случае, если значение в ячейке А2 находится между 18 и 27, выводится строка «Сегодня тепло». И если ни одно из этих условий не выполняется, выводится строка «Сегодня прохладно».
Задание 2.3. С помощью функции ЕСЛИ выполните вычисления по формулам:
а) 
 ; шаг по x, y = 0,2.
| б) 
 ; шаг по x, y = 0,5.
|
Данные представьте в виде таблиц следующего вида:
 y x
| 0,2 | 0,4 | 0,6 | 0,8 | |
| 0,2 | |||||
| 0,4 | |||||
| 0,6 | |||||
| 0,8 | |||||
Примечание:
Здесь также потребуется функция ЕСЛИ. Адреса ссылок на ячейки должны быть смешанные -очевидно, что значения х всегда находятся в одной строке (в первой), а значения у – в одном столбце (в столбце А). Заполнение таблицы должно проходить двумя движениями мыши – введенную формулу протянуть вниз, затем вправо или наоборот.
Задание 2.4. Создайте и заполните таблицу следующего вида:
| ФИО | Масса, кг | Рост, см |
| Авдеев А.С. | ||
| Борина К.П. | ||
| Зимин К.А. | ||
| Соколов Л.Р. | ||
| Шпакин В.В. | ||
| Среднее значение | ||
| Наибольшее | ||
| Наименьшее |
Для расчетов используйте стандартные функции MAКС, MИН, СРЗНАЧ. В отдельных ячейках подсчитайте количество человек, имеющих вес менее 75 кг, и количество человек, имеющих рост более 170 см, используя функцию СЧЕТЕСЛИ.
Задание 2.5. Рассчитать значение функции на интервале [-2,2] с шагом h=0,5 для k1=3 и на интервале [-1,1] с шагом h=0,25 при k1 =8, где k=sin(k1.x), и построить ее график.
|
|
|
Примечание:
В упражнении 5 для удобства проведения расчета и возможности пересчета при других начальных данных целесообразно построить две таблицы: основную для проведения расчета и вспомогательную для исходных данных.
Удерживая клавишу CTRL, выделите значения x и y. Выберите точечную диаграмму в группе Диаграммы на вкладке Вставка. Используя контекстную вкладку Работа с диаграммами, проведите необходимое форматирование графика.
Выполните перерасчет задачи, изменив исходные данные во вспомогательной таблице.
Условное форматирование обеспечивает наглядность при исследовании и анализе данных, обнаружении критических проблем, схем и тенденций.
Для форматирования ячеек по цвету в зависимости от значения температуры воспользуйтесь командой Правило выделения ячеек в группе Стили на вкладке Главная ( щелкните стрелку рядом с пунктом Условное форматирование).
В появившемся диалоговом окне легко записать правило форматирования ячеек.
Задание 2.6. Рассчитать значения функции 
при х=0,5 с шагом 0,5; у = -2. Рядом рассчитать значения функции при у = 1. Значения у занести в отдельные ячейки, на них сделать абсолютные ссылки. Построить в одной системе координат графики функции z(x,y) при заданных значениях у.
Задание 2.7. Условное форматирование.
Дана таблица значений температуры за неделю в октябре. Необходимо выделить ячейки с положительными температурами красным цветом, с отрицательными температурами – синим цветом. Использовать условное форматирование. Для иллюстрации колебаний температуры построить график.
| Число | |||||||
| Температура, 0С | -2 | -1 | -4 |
В отдельных ячейках введите формулы для определения наибольшей, наименьшей и средней температур за наблюдаемый период.
|
|
|
