Объемы воды в крупнейших озерах и водохранилищах

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

Задание 1. День недели прописью

Задача решена двумя способами. С помощью форматирования ячейки и с помощью встроенных функций.

В этом задании необходимо создать модуль (в данном случае электронную таблицу, которая решает определенную задачу), который позволяет по заданной дате определять день недели.

Модуль работает следующим образом: в ячейку D1вводится дата, в ячейке D2 автоматически высвечивается день недели.

Для получения в данной ячейке дня недели ее нужно отформатировать, вызвав диалоговое окно Формат ячеек, перейдя в режим все форматы, в поле Тип указать ДДДД.

 

Рисунок 1 – День недели прописью

 

В таком случае можно воспользоваться функциями ЕСЛИ и ДЕНЬНЕД. Функция ДЕНЬНЕД, позволяет определять день недели у заданной даты. Ее можно ввести, вызвав мастер функций и выделив в списке Категория элемент Дата и время.

Аналогично нужно создать вложенные функции для всех дней недели.

Полученные моду ли можно перемещать по рабочему листу и копировать на другие листы. Подкорректировав ссылку в формуле, можно изменить расположение ячеек ввода и вывода: разместить их не в одном столбце, а в одной строке. В результат выполнения задания 1 у вас должно получиться два модуля, которые работают для всех дней недели (рисунок №).

Рисунок 3 – День недели прописью

Задание 2. Дата прописью

Решить эту задачу можно следующим методом:

1) разбить дату на составляющие – день, месяц и год (причем для месяца сформировать не его порядковый номер в году, а название);

2) после этого соединить все компоненты в текстовой строке.

Результат выполнения задания представлен на рисунке 4.

Рисунок 4 – День недели прописью

Задание 3. Написание суммы прописью

При заполнении различных документов наряду с суммой в числовом виде требуется указывать ее величину прописью. Поэтому создайте небольшую электронную таблицу, которая поможет автоматизировать данную работу.

Рисунок 5 – Написание суммы прописью

 

Будущая таблица расчетов будет занимать диапазон А1:Н21 и состоять из шести областей:

Ячейка А1–входная.

Ячейки А2:Н4–здесь выполняется разделение числа на разряды и определение значения каждого разряда.

Ячейки А5:Н13– блок формирования числительного для каждого разряда

Ячейки А14и D14формируют названия разрядов.

Ячейка G14–формирует название единицы измерения.

Ячейки А15:А21–здесь выполняется объединение и завершающая обработка результатов.

Максимальное число, с которым может работать таблица, –9999999,99.

Однако ее легко модифицировать для обработки чисел больше этого.

Разбивка числа на разряды. В строке 3 определяется, сколько целых миллионов, тысяч, десятков и т. д. имеется в числе. Для этого следует разделить число на значение соответствующей разрядности и отбросить дробную часть. Например, для определения количества целых сотен число нужно разделить на 100 и с помощью математической функции ОТБР отбросить дробную часть результата.

Таким образом, в ячейке ЕЗ должна находиться такая формула:

=ОТБР (А1/100;0)

Если число больше или равно 100, то мы получим количество сотен в этом

числе. В противном случае результатом будет 0. Аналогичные формулы

используем и для других разрядов. Значение знаменателя дроби в этих формулах

изменяется в зависимости от разряда: для тысяч —1000, для миллионов —1 000 000 и т. д. Результат выполнения задания представлен на рисунке 6.

Рисунок 6 – Написание суммы прописью

Задание 4. Условное форматирование ячеек

Задание 4.1

Создайте условное форматирование, которое, при вводе положительно числа, окрашивает ячейку в желтый цвет, при вводе отрицательного – в зеленый цвет (рисунок 7).

Рисунок 7 – «Положительные и отрицательные числа»

 

Задание 4.2 – 4.3

Создайте условное форматирование, которое выделяет в синюю рамку три самых больших числа в заданном диапазоне.

Создайте условное форматирование, которое определяет 40% самых больших чисел в диапазоне и выделяет их красным цветом шрифта (рисунок 8).

Рисунок 8 – «Три самых больших числа. 40% самых больших чисел»

 

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

Создание автоматических таблиц для расчетов в MS Excel.

Задание 1. Создание таблицы умножения

Заполнили ячейки А2:А11 числами от 1 до10. Через диалоговое окно «Прогрессия» установили нужные параметры. После введения формул установили относительные, абсолютные и смешанные ссылки. Заполнили область вычислений и изменили начальные параметры (рисунок 9).

Рисунок 9 – Создание таблицы умножения

 

Задание 2. Таблица расчета процентов по вкладу

Ход выполнение задания:

1.Скопируйте таблицу, созданную в первом задании, на лист Задание 2.

2.Вставьте две строки (после строк 2 и 7).

3.Заполните область ввода новыми наименованиями и значениями.

4.В ячейке E3введите число 0,03и поменяйте формат ячейки на Процентный(на вкладке Главная в области Число).

5.В ячейке Е5 введите 0,01 и установите формат Процентный.

6.Ячейка А10должна ссылаться на ячейку Е3.

7.Установите формат Процентный для диапазона А10:А19.

8.Установите курсов в ячейку В10и выделите диапазон B10:K19.

9.Введите знак равенства, выделите ячейку E2и нажатиями клавишиF4задайте абсолютную ссылку.

10.Введите знак *(умножить), круглую открывающую скобку, цифру 1и знак+.

11.Выделите ячейку А10 и три раза нажмите клавишу F4(будет создана абсолютная ссылка на имя столбца), затем введите круглую закрывающую скобку.

12.Переключитесь на английский шрифт и введите знак возведения в степень «^» путем нажатия комбинации клавиш Shift+6.

13.Выделите ячейку В9 и дважды нажмите клавишу F4 (будет создана абсолютная ссылка на номер строки).

14.Завершите ввод формулы нажатием комбинации клавиш Ctrl +Enter.

15.Установите формат Денежный для области, где вычисляются суммы вклада с процентами. В отличие от статической таблицы полученная динамическая таблица позволяет изменять сумму, ставки депозита и вычислять результат для разных временных периодов.

 

Задание 3.Создание модулей расчета процентов по вкладу.

Все расчеты, которые производятся в таблице, можно выполнить с помощью небольшого модуля, размером 4x2 ячейки. Задавая для него входные данные, вы получите те же результаты, что и в ранее созданной таблице.

Рисунок 10 – Создание модулей расчета процентов по вкладу.

 

Ввод дат в модуль

В Excel существует возможность выполнять операции с датами. В частности, даты можно автоматически вычитать. Применим эту возможность в модуле расчета. Сначала скопируем модуль в ячейку F3. Чтобы более точно определить период, добавим в модуль: дату размещения денежных средств на счете (ячейка I1) и дату закрытия депозита(ячейка I2), а в ячейку I5, определяющую срок размещения, введем формулу=(I2-I1)/365

В этой формуле одна дата вычитается из другой, а полученный результат

делится на количество дней в году.

Рисунок 11–Создание модулей расчета процентов по вкладу

 

Задание 4.Создание модуля для расчета параметров кредита.

Многие из вас знакомы с приложениями, позволяющими рассчитывать основные параметры кредита: срок, сумму кредита, ежемесячные платежи и процентные ставки. Такие приложения могут быть встроены в органайзер мобильного телефона, а также представлены на сайтах банков и кредитных агентств. Расчет параметров кредита можно организовать с помощью формул в Excel.

Рисунок 12–Создание модуля для расчета параметров кредита

Задание 5. Построение графика функции, заданной через условия

Рассмотрим построение графика функции, состоящей из трех разных участков.

Необходимо построить график функции на интервале от (f–10) до (с+10).В диапазон ячеек А1:F2введем параметры и их значения (a, b, d, e–выберете по своему усмотрению).

Рисунок 13 – Построение графика функции, заданной через условия.

 

Далее необходимо ввести значения x в столбец или строку, начиная с (f–10) и заканчивая (с+10).В Варианте 0 от –11 до 13. Также с помощью условного форматирования выделите значения x, соответствующие разным отрезкам.

Рисунок 14 – Построение графика функции, заданной через условия

 

Далее необходимо заполнить значения для в соседнем столбце. Обратите внимание, что в формуле удобно использовать абсолютные ссылки на ячейки, в которых содержатся параметры функции.

Рисунок 15 – Построение графика функции, заданной через условия

 

Теперь можно построить график. Нужно выбрать тип диаграммы – «Точечная с прямыми отрезками».

Рисунок 16 – Построение графика функции, заданной через условия

Задание 6. Построение графика трехмерной функции

Построим график функции z= x2+ y2.Для этого сначала зададим значения для переменных x и y в диапазоне от –2 до 2 с шагом 0,1 (рисунок 17).

Рисунок 17 – Построение графика трехмерной функции.

 

Затем в ячейку В3 введем формулу для z. В данном случае необходимо использовать смешанные ссылки, чтобы легко скопировать данную формулу на весь диапазон ячеек (рисунок 18).

Рисунок 18 – Построение графика трехмерной функции.

 

Для построения трехмерного графика необходимо выделить диапазон значений для z и выбрать тип диаграммы «Проволочная поверхность».

В итоге будет получен следующий график (рисунок 20).

Рисунок 20 – Построение графика трехмерной функции.

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

Использование диаграмм в MS Excel

Задание 1. Создание гистограммы

На сайте Федеральной службы государственной статистики подберите

данные и создайте гистограмму содержащую:

·Несколько рядов данных;

·Название;

·Название осей;

·Подписи данных;

·Легенду.

Адрес сайта:

http://www.gks.ru/wps/wcm/connect/rosstat_main/rosstat/ru/

Рисунок 21 – Создание гистограммы

 

Задание 2. Создание графика

Подберите данные в Российском статистическом ежегоднике и создайте график.

Убедитесь, что на графике содержится вся необходимая информация для его понимания и использования.

Электронная версия Российского статистического ежегодника находится на

сайте Федеральной службы государственной статистики/ Официальная статистика/ Публикации/ Каталог публикаций/ Статистические сборники/

Российский статистический ежегодник.

Объемы воды в крупнейших озерах и водохранилищах

В европейской части страны находятся семь крупных озер, каждое из которых имеет площадь более 1000 км2, в азиатской части страны расположено самое крупное в Азии озеро Байкал, площадь которого составляет около 32 тыс. км2. Для озера Байкал, запасы воды которого очень велики и не сопоставимы с их годичными изменениями, объем считается неизменным от года к году (рисунок 22).

Рисунок 22 – Создание графика

 

Задание 3. Создание точечной диаграммы

Подберите данные в интернете или используйте свои данные и постройте точечную диаграмму.

 

Рисунок 23 – Создание точечной диаграммы

Задание 4.Создание биржевой диаграммы

Выберите данные на сайте Московской межбанковской валютной биржи (www.micex.ru) и постройте биржевую диаграмму.