MS Excel
(урок 5)
Вспомни, как мы строим график функции в тетради:
1) берем несколько значений x (аргумента функции) – чем больше, тем точнее получится график,
2) для каждого из них рассчитываем значение y (самой функции),
3) в системе координат строим точки (каждая пара x и y – координаты одной точки),
4) соединяем точки плавной (ну, у кого как получится) линией.
Сюрприз – excel строит графики точно так же. Только точек он может взять сколько угодно, и значения y для каждого x рассчитает сам.
Итак.
Построим график функции 
(берем тригонометрические функции, чтобы получить интересный график).
Шаг 1. Значения x.
Помним, что, чем больше точек, и чем ближе друг к другу они стоят, тем лучше. Поэтому возьмем значения от –10 до +10 с шагом 0,5. Это 41 точка.
Будем ли мы писать вручную все эти числа: –10, –9,5, – 9 и т.д.?
Ни за что. Мы уже проходили ряды данных, вот этот навык и применим. Получаем столбец со значениями x.
Шаг 2. Значения y.
Напишем формулу для расчета значения функции для первого значения x. При этом вместо того, чтобы писать «–10» мы будем ссылаться на ячейку, где содержится нужное нам значение аргумента.
Зачем?
Затем, что ссылки в excel по умолчанию относительные! А значит, если мы скопируем получившуюся функцию и вставим ее в ячейки ниже, то ссылка на значение x также сместится – и мы получим значения функции для всех остальных аргументов, не прилагая никаких усилий.
| Это знак возведения в степень. Можно использовать и конструкцию A1*A1, но если показатель степени будет больше 2, это уже неудобно |
| В Excel есть функции синуса, косинуса и т.д. – находятся в категории математических |
| В математике квадрат синуса записывается так: sin2(x). Но во всех компьютерных средах функция и ее аргумент – неразделимы. Поэтому степень указывается после аргумента |
| Нельзя использовать конструкцию 2x без знака умножения, excel не поймет такую запись. |
Обрати внимание на несколько тонкостей:
|
|
Получили одно значение функции, теперь просто копируем эту ячейку и вставляем во все ячейки ниже, получаем 41 значение y для 41 значения x.
Можно и проще – навести мышь на правый нижний угол ячейки, зажать и растянуть (как при создании рядов данных) – функция так же скопируется. Получаем:
Шаг 3 и 4. Построение точек с заданными координатами и соединение их плавной / прямой линией – то есть создание графика функции.
Для создания графика нужно выделить все имеющиеся значения x и y (в данном случае – диапазон A1:B41), затем зайти в меню «Вставка» и найти там панель вставки диаграмм:
И сразу в глаза бросается слово «График». Внимание – «График» нам НЕ НУЖЕН!
Диаграмма типа «График» не умеет воспринимать два ряда значений (два столбца данных) как координаты x и y множества точек. Для «Графика» здесь два набора значений y, а вместо значений x он подставит натуральные числа.
Попробуй выбрать тип «График», убедись, что у тебя в одной системе координат будет построено две линии, и больше так не делай. Удали теперь построенный график и вернись на шаг назад, к выбору типа диаграммы.
Единственный тип диаграммы, который поможет нам построить график функции – «Точечная». Потому что строит точки по двум координатам, а потом уже соединяет их линией. Или не соединяет – это ты решишь на следующем шаге:
|
|
Привычный нам вид графика позволит получить точечная диаграмма с гладкими кривыми.
Выбери ее.
Вот наш график функции:
Полученная диаграмма состоит из множества элементов, каждый из которых имеет свои настройки.
Если ты кликнешь правой кнопкой мыши по тому или иному элементу диаграммы, появится контекстное меню, в котором нижний строкой будет указано «Формат легенды» или «Формат области диаграммы» – в зависимости от того, по какому элементу ты попал.
Задание: кликая правой кнопкой мыши по своей диаграмме, найди настройки формата:
- области диаграммы
- области построения
- оси X
- оси Y
- легенды
- ряда данных
- линий сетки
Настроек множество, и запоминать их все наизусть – смысла нет. Тем более что в других версиях программы (и в других табличных редакторах) эти настройки могут называться немного иначе. Гораздо важнее умение по описанию задачи определить, к какому элементу диаграммы она относится, в каком контекстном меню искать решение.
Например: нужно изменить цвет графика функции. Куда будешь кликать? – легко, на ряд данных (саму линию графика).
А вот сложнее: по умолчанию оси x и y не имеют стрелок, нужно их добавить – попробуй, найди эти настройки. То есть действительно – попробуй. Сейчас. Чтобы не искать второпях на зачете.
Еще задача: нужно сделать так, чтобы горизонтальные линии сетки шли не через 20 единиц, а через 10.
И еще одна: допустим, твоя диаграмма идеально отформатирована, и тут ты обнаруживаешь, что диапазон данных изначально выбран неверно – не захвачена часть ячеек. Как заново выбрать данные для этой диаграммы, чтобы не создавать новую?
|
|
Если нужен какой-то элемент диаграммы, которого изначально на ней нет, например – вертикальные линии сетки, заголовок – их можно добавить через меню «Макет» (появляется, когда диаграмма выделена).
И, напоследок, негатив.
Механизм построения графиков функций в excel – не совершенен.
Точечная диаграмма строит всего лишь точки, а потом соединяет их линиями, не задумываясь, график какой именно функции ты строишь, какова область допустимых значений этой функции?
Посмотри еще раз внимательно на нашу функцию:
Видишь? Косинус в знаменателе!
А ведь косинус определенного аргумента равен 0, а на 0 тебе делить пока нельзя! Значит, в графике функции должны быть разрывы.
Почему же их нет? Потому что, подбирая значения x, мы «не попали» ни на один такой разрыв. Ведь косинус равен 0, когда угол равен π/2 + πn, в радианах это будет иррациональное число. Вот и вышло, что все наши точки попадают в ОДЗ функции, а линии, которые их соединяют… это просто плавные линии, а не рассчитанные значения.
Давай попробуем поставить в знаменатель синус, возьмем функцию 
При х = 0 синус от х тоже будет равен 0. Значит, в этой точке должен быть разрыв графика.
Строим, смотрим:
Разрыва нет, но что мы видим в столбце значений y при х = 0?
Ошибка деления на 0!
А любые ошибочные значения excel считает равным 0, вот и строит точку с координатами (0;0).
Если увидишь в таблице своих значений такую ошибку – удали значение этой ячейки. В этом случае появится разрыв графика функции:
Беда в том, что разрыв в этой функции будет не один, ведь синус равен 0, когда угол равен πn (то есть где-то в точке 3,14 должен быть разрыв, и в точке 6,28, и то же самое с отрицательными значениями).
Но эти точки в построении графика участия не принимают, поэтому мы и не можем удалить значения функции для них.
Вывод: построение графика функции в excel дает лишь примерное представление о характере самой функции. И не заменяет знание математики.
Задание: построй два графика (разных функций) в одной системе координат. Подсказка: ряд x должен быть один, а рядов y – два.
Построение гистограмм
Каждый тип диаграмм имеет свое назначение. Гистограмма (диаграмма «столбиками») позволяет визуально сравнить элементы одного или нескольких рядов данных.
Например. Вот таблица результатов ЕГЭ (средний балл) за несколько лет:
| 2016-2017 | 2017-2018 | 2018-2019 | |
| География | |||
| Биология | |||
| Обществознание | |||
| История | |||
| Физика | |||
| Химия | |||
| Информатика |
Чтобы понять, какие экзамены были самыми успешными в определенном году, или как изменялись результаты по определенному предмету за все эти годы, нам нужно всматриваться в числа, сравнивать их между собой, держать в уме. Нет наглядности.
Чтобы увидеть все эти данные в удобном для восприятия формате, построим гистограмму.
Выделяем эту таблицу (в excel[1]) вместе с заголовками (это важно – тогда excel сам расставит подписи по оси X и сформирует легенду), заходим в меню «вставка», выбираем «Гистограмма с группировкой». И вот она:
| Подписи по оси x |
| Легенда |
Обрати внимание на легенду – она дает нам понять, как excel прочитал таблицу – по столбцам или по строкам.
В данном случае столбцы таблицы представляют собой ряды данных, их три, и каждый из них состоит из 7 элементов (предметов).
В этом виде гистограммы мы можем легко сравнить ряды данных, сгруппированных поэлементно: например, если сравним первый элемент всех трех рядов (географию), то увидим, что с каждым годом этот предмет сдают все успешнее, так же, как и информатику. А вот обществознание, история и химия снижают качество.
Но ведь таблицу можно читать и по-другому, представить результаты каждого предмета как отдельный ряд данных из трех элементов.
| 2016-2017 | 2017-2018 | 2018-2019 | 2016-2017 | 2017-2018 | 2018-2019 | |||
| География | География | |||||||
| Биология | Биология | |||||||
| Обществознание | Обществознание | 49 | ||||||
| История | или | История | ||||||
| Физика | Физика | |||||||
| Химия | Химия | |||||||
| Информатика | Информатика |
Чтобы сменить направление чтения рядов данных можно транспонировать саму таблицу (развернуть ее так, чтобы строки стали столбцами, а столбцы – строками), но это не всегда возможно. Да и не нужно – можно поменять местами строки и столбцы только для гистограммы.
Для этого нужно кликнуть по гистограмме правой кнопкой мыши, в контекстном меню нажать на «Выбрать данные», и в появившемся окошке… а вот теперь иди и попробуй так сделать, там несложно догадаться.
В результате на основе той же самой таблицы получим гистограмму такого вида:
Те же самые данные, но сгруппированы теперь по годам – и мы уже можем сделать новые выводы – например, о том, в какой код результаты ЕГЭ были более однородными.
Так же, как и в случае построения графика функции, каждый элемент гистограммы имеет множество настроек. Можно форматировать ряды данных, оси, подписи данных, легенду, линии сетки и т.п.
Теперь пару слов о других типах гистограмм: гистограмма с накоплением и нормированная гистограмма с накоплением.
Первая – гистограмма с накоплением – не группирует значения рядов данных, а складывает их, и рисует «столбики» один над другим.
Вторая – нормированная гистограмма с накоплением, тоже складывает значения, но показывает не абсолютные величины, а доли в сумме.
Давай на примере, вот таблица потребления жиров, белков и углеводов (в граммах) на завтрак, обед и ужин:
| завтрак | обед | ужин | |
| белки | |||
| жиры | |||
| углеводы |
Гистограмма с накоплением для этой таблицы будет выглядеть так:
Сразу видно, какой прием пищи был самым насыщенным.
А нормированная гистограмма с накоплением выглядит так:
Здесь лучше видно распределение веществ в разные приемы пищи. Самым полезным оказался ужин.