Сборник лабораторных работ "Работа в MS Excel"

Содержание

Интерфейс электронной таблицы.. 3

Лабораторная работа №1. Ввод данных, вычисление функций, форматирование таблицы. 4

Лабораторная работа №2. Работа со случайными числами. Построение диаграмм. 6

Лабораторная работа №3. Использование абсолютной и относительной адресации. 8

Лабораторная работа №4. Построение графиков функций. 11

Лабораторная работа №5. Японский календарь. 15

Лабораторная работа №6. Составление формул. Математические функции. 16

Лабораторная работа № 7. Использование функции ЕСЛИ. 18

Интерфейс электронной таблицы

Элементы интерфейса

1.      Кнопка Office (содержит операции с документом: создание, сохранение, печать, просмотр и т.д.)

2.      Панель быстрого доступа (на ней располагаются часто используемые операции)

3.      Лента (содержит вкладки)

4.      Вкладки (содержит команды, сгруппированные по смыслу)

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

6.      Рабочая область электронной таблицы состоит из строк и столбцов, имеющих свои имена.

Имена строк – это имена, начиная от 1 до 65536 (до MS Office 2007), 1048576 (с MS Office 2007). Имена столбцов – это буквы латинского алфавита сначала от A до Z, затем от AA до AZ, от BA до BZ и т.д (всего 256 столбцов – до MS Office 2007, 16384 - с MS Office 2007).

7.      Ячейка – область, определяемая пересечением столбца и строки электронной таблицы.

8.      Поле имени, где отображается адрес активной ячейки

9.      Ярлыки листов – для переключения между листами книги (по умолчанию в книге 3 листа).

Пересечение строки и столбца образует ячейку таблицы, имеющую свой уникальный адрес. Для указания адресов ячейки в формуле используются ссылки (например, A3 или C5).

Лабораторная работа №1. Ввод данных, вычисление функций, форматирование таблицы.

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

Диалоговое окно состоит из нескольких вкладок:

·         Число – для выбора формата данных (общий, текстовый, числовой, финансовый, денежный и т.д.)

·         Выравнивание – параметры для форматирования данных  в ячейках (горизонтальное и вертикальное выравнивание, направление текста, перенос слов в ячейке)

·         Шрифт – параметры для шрифта (гарнитура, кегль, начертание, цвет)

·         Граница – параметры для настройки границ ячеек

·         Заливка – заливка ячеек

·         Защита – установка защиты данных

Задание 1. Оформить таблицу

Задание 2.  Заполнить таблицу данными, вычислить.

1.      Оформить таблицу, внести данные

2.      Записать функции для определения максимального (МАКС), минимального числа (МИН), суммы (СУММ), среднего значения (СРЗНАЧ) и значения моды (МОДА) для всех введенных чисел.

Формат записи функции можно найти в диалоговом окне ( или Вставка->Функция)

3.      Использовать условное форматирование:

- для ячеек, в которых значения от 20 до 30, выбрать заливку желтого цвета

- для ячеек, в которых значения от 60 до 90, выбрать заливку зеленого цвета

- для ячеек, в которых значения от 200 до 600, выбрать заливку красного цвета

УСЛОВНОЕ ФОРМАТИРОВАНИЕ – применение параметров форматирования при выполнении заданных условий.

Условное форматирование можно задать для ячейки или блока ячеек. Используется команда меню Формат -> Условное форматирование.

Лабораторная работа №2. Работа со случайными числами. Построение диаграмм.

Для генерирования случайных чисел используется функция - СЛЧИС (группа Математические).

Примеры использования:

Запись	Описание
СЛЧИС()	Числа в диапазоне от 0 до 1
СЛЧИС()*100	Числа в диапазоне от 0 до 100
СЛЧИС()*76	Числа в диапазоне от 0 до 76
СЛЧИС()*-8	Числа в диапазоне от -8 до 0
СЛЧИС()*(10-2)+2	Числа в диапазоне от 2 до 10
СЛЧИС()*(6-(-9))-9	Числа в диапазоне от -9 до 6

Для указания диапазона выбора чисел используется формула: СЛЧИС()*(b-a)+a , где a и b - границы диапазона. При нажатии клавиши F9 значения ячеек будут изменяться.

Задание 1. Заполнить таблицу данными, вычислить максимальные  и минимальные значения. .

·      Заполнить блок ячеек B1:B20 случайными числами в диапазоне от 0 до 1

·      Заполнить блок ячеек С1:С20 случайными числами в диапазоне от 0 до 100

·      Заполнить блок ячеек D1:D20 случайными числами в диапазоне от 3 до 50

·      Заполнить блок ячеек E1:E20 случайными числами в диапазоне от -10 до 10

·      Заполнить блок ячеек F1:F20 случайными числами в диапазоне от -100 до 100

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

Пример выполненного задания:

Слой формул:

Примечание. Чтобы переключиться в режим слоя формул используется команда меню Сервис -> Зависимости формул -> Режим проверки формул. Выключить режим можно с помощью этой же команды.

·      Вычислить максимальные значения по строкам (справа от таблицы)

·      Вычислить минимальные значения по строкам (справа от таблицы)

·      Вычислить максимальные значения по столбцам (под таблицей)

·      Вычислить минимальные значения по столбцам (под таблицей)

Задание 2. Построение диаграмм.

·          Вызвать мастер диаграмм  (меню Вставка -> Диаграмма)

·          Выбрать тип ГИСТОГРАММА, вид ОБЪЕМНЫЙ, нажать ДАЛЕЕ

·          В качестве диапазона значений указать диапазон с данными (укажите таблицу со случайными числами), нажать ДАЛЕЕ

·          Задать название «ГИСТОГРАММА», нажать ДАЛЕЕ

·          Диаграмму поместить на отдельном листе, который назвать «ГИСТОГРАММА», нажать ОК.

·          Аналогичным способом построить остальные виды диаграмм:

·          Каждую диаграмму разместить на отдельном листе и назвать по выбранному типу

·          Сохранить файл. Результат работы показать преподавателю.

Лабораторная работа №3. Использование абсолютной и относительной адресации.

При обращении к ячейке можно использовать два способа: относительную адресацию (например, ВЗ, А1:G9) и абсолютную адресацию (например, $A1, A$5, $D$5)

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

Задание 1. Вычисление функций

·        Создайте новую рабочую книгу – LAB.xls.

·        Заполните диапазон ячеек  H1:J2 начальными исходными данными:

	H	I	J
1	x0	step	k
2	-2	0.2	10

где х0 – начальное значение х, step - шаг изменения х, k – коэффициент (константа).

·          используя функцию автозаполнения (копирование с помощью перетаскивания), заполните столбец A числами от 1 до 21, начиная с ячейки A2 и заканчивая ячейкой A22.

·          заполните столбец B значениями х: -

Ø   в ячейку B2 занесите =$H$2. Это означает, что в ячейку B2 заносится значение из ячейки H2 (начальное значение х), знак $ указывает на абсолютную адресацию;

Ø   в ячейку B3 занесите =B2+$I$2. Это означает, что начальное значение х будет увеличено на величину шага, которая берется из ячейки I2;

Ø   скопируйте формулу из ячейки B3 в ячейки В4:B22.

Примечание. Столбец должен заполнится значениями х от -2 до 2 с шагом 0.2.

·          Заполните столбец С значениями коэффициента k:

Ø   в ячейку С2 занесите =$J$2;

Ø   в ячейку СЗ занесите =C2.Посмотрите на введенные формулы. Почему они так записаны?

Ø   скопируйте формулу из ячейки СЗ в ячейки С4:С22. Весь столбец должен заполнится  значением 10.

·          Заполните столбец D значениями функции у₁=x²-1:

Ø   в ячейку D2 занесите =В2*В2-1;

Ø   скопируйте формулу из ячейки D2 в ячейки D3:D22. Столбец заполнился как положительными, так и отрицательными значениями функции у₁. начальное и конечное значения равны 3.

·          Аналогичным образом заполните столбец Е значениями функции у₂=х²+1.

Проверьте! Все значения положительные; начальное и конечное значения равны 5.

·          Заполните столбец F значениями функции

y=k(x²-1)/(x²+1):

Ø   в ячейку F2 занесите =С2*(D2/Е2);

Ø   скопируйте формулу из F2 в ячейки F2:F22.

Проверьте! Значения функции как положительные, так и отрицательные; начальное и конечное значения равны 6.

·           Построить график на отдельном листе по значениям столбца C, отформатировать (Название - График 1;  фон желтый)

·          Построить график на отдельном листе по значениям столбца D, отформатировать (название - График 2, фон зеленый)

·          Построить график на отдельном листе по значениям столбца F, отформатировать (название - График 3, фон голубой)

Задание 2. Вычисление квартплаты.

1.      Переименуйте Лист1 в Квартплата

2.      Задайте цвет ярлыка листа - красный

3.      Оформите лист в соответствии с таблицей:

4.      Вычислите квартплату для каждой квартиры, если на данный момент используются следующие тарифы:

Площадь             - 6,75 руб./кв.м

Электроэнергия  - 1,05 руб./Квт*ч

Газ                        - 5,9 руб./чел.

Тарифы записать в отдельные ячейки.

Задание 2. Курс доллара

1.      Переименуйте Лист2 в Курс доллара

2.      Задайте цвет ярлыка листа - зеленый

3.      Курс доллара: 1$ = 29,2 руб. Для хранения значения курса доллара используйте ячейку A2.

4.      Оформите лист в соответствии с таблицей:

5.      Вычислите стоимость поездки в рублях, а также стоимость проживания 1 дня

6.      Вычислите среднюю стоимость поездки данного турагенства (в руб. и $)

7.      Вычислите максимальную и минимальную цену проживания 1 дня.

Задание 3. Вычисление стипенди

1.      Переименовать Лист 3 в Стипендия

2.      Задайте цвет ярлыка листа - желтый

3.      Оформить и заполнить таблицу на 5 записей.

4.      В отдельной ячейке запишите значение стипендии: 500

5.      Вычислите, заполните столбцы Итого, Средний балл

6.      Вычислить стипендию.

Для вычислений использовать следующие условия:

·         если средний балл < 4, то не назначать стипендию, а вывести текст «Стипендии нет».

·         если средний балл равен или находится в промежутке от 4 до 4.5, то назначить стипендию равную размеру стипендии плюс 25%.

·         если 4.5 ≤ средний балл ≤ 5, то назначить стипендию равную размеру стипендии плюс 50%.

·         Во всех остальных случаях вывести текст «Неверные данные».

7.      Используя условное форматирование, выделите разными цветами значения в столбце Стипендия (например, «Неверные данные» - красный цвет, 625 – зеленый, 750 - желтый)

8.      Сохранить документ с именем Вычисления.

Лабораторная работа №4. Построение графиков функций.

Рассмотрите примеры построений и выполните самостоятельную работу.

Задание. Построить и отформатировать график функции в соответствии с примером.

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

1 этап. Составление таблицы значений

2 этап. Построение графика

·        Запустить мастер диаграмм  

·        Выбрать тип  - график

·        В качестве данных указать созданную таблицу данных (см. рис.)

·        Подписать график

·        Отформатировать (задать фон, легенду, изменить тип линии и т.д.)

Подсказка. Форматировать можно уже готовую диаграмму. Каждый объект диаграммы (точки, линии, легенду, подпись, область графика и т.д.) можно форматировать отдельно, для этого дважды щелкнуть по нужному объекту – откроется соответствующее диалоговое окно.

Задание - Построить и отформатировать 2 графика функций в одной системе координат соответствии с примером.

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

1 этап. Составление таблицы значений

2 этап. Построение графика

При построении нескольких графиков в одной системе координат необходимо создать несколько рядов данных (сколько графиков, столько и рядов).

3 этап. Форматирование диаграммы

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

ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОЙ РАБОТЫ

Задание 1. Построить и отформатировать график функции в соответствии с примером.

Задание 2. Построить и отформатировать 2 графика функции в одной системе координат соответствии с примером.

Задание 3. Построить и отформатировать 2 графика функции в одной системе координат соответствии с примером.

Примечание. В качестве типа используется нестандартный (график + гистограмма)

Лабораторная работа №5. Японский календарь.

Исходные положения

Японский календарь основан на трех циклах: 60-ти летний определяется числом,  12-ти летний названием животного, а пятилетний цветом. В японском календаре существуют следующие животные: крыса, корова, тигр, заяц, дракон, змея, лошадь, овца, обезьяна, петух, собака, свинья. Кроме того, учитываются пять цветов: зеленый, красный, желтый, белый и черный. Для определения номера года нужно к настоящему году прибавить 2397 лет и определить остаток от целочисленного деления

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

·  В ячейках A1, B1, C1,D1, F3, G3, H3 размещаем заголовки Год, Номер года, Животное, Цвет.

·  В столбце F с ячейки F4 записать номера годов (12 лет – полный цикл).

·  В столбце G  с ячейки G4 записать названия животных

·  Столбец H с ячейки H4 записать цвета

·  Записать в ячейку A2 год - 1990

·  В ячейку B2 ввести формулу для вычисления номера года (= ОСТАТ(A2+2397;60))

·  В ячейку C2 ввести формулу для определения животного  (=ВПР(ОСТАТ(B2-1;12); $F$4:$H$15;2))

·  В ячейку D2 ввести формулу для определения цвета (=ВПР(ОСТАТ(ОТБР((B2-1)/2);5);$F$4:$H$8;3))

     

·        Заполнить диапазон ячеек A3:A18 целыми числами – с 1991 по 2006

·        Скопируйте формулу из ячейки B2 в диапазон ячеек B3:B18

·        Скопируйте формулу из ячейки С2 в диапазон ячеек С3:С18

·        Скопируйте формулу из ячейки D2 в диапазон ячеек D3:D18

·        Сравните полученный результат.

·        Для ячеек диапазона D2:D18 задайте условное форматирование:

- для значений «красный » залить ячейку красным цветом;

 - для значений «зеленый » залить ячейку зеленым цветом;

 - для значений «желтый » залить ячейку желтым цветом;

·        Показать результат преподавателю.

Лабораторная работа №6. Составление формул. Математические функции.

Обозначения функций в MS Excel:

	Abs(x)
	корень(x)
	степень(x;2)
	степень(x;1/n)
	степень(x;a/n)
	степень(x;n)
e	exp
	exp(n)
cos x	cos(x)
sin x	sin(x)
	степень(cos(x);2)
	cos(степень(x;2))

Пример записи функции:   В MS Excel: =abs(степень(x;2)+степень(y;2))/корень(x*y)  

Задание 1. Вычислить значение функции:

 , где

Для задания a, b, c; для вычислений y и x использовать разные ячейки.

Примечание. Для проверки задать a=12.5,  b=25,  c=23.5. Сравните результат: x=188.44,  y=0.073049

Задание 2. Вычислить периметр прямоугольника со сторонами a и b:

                            

Для задания x,y; для вычислений a, b и периметра использовать разные ячейки.

Примечание. Для проверки задать x=1.45; y=5.25. Сравните результат: a=0.0468;  y=67.482; P=135.0584

Задание 3. Вычислить объем конуса с радиусом основания R и высотой H:

                

Для задания a,b; для вычислений R, H и объема использовать разные ячейки.

Примечание. Для проверки задать a=4.86; b=24. Сравните результат: R=0.1206; H=63.644; V=0.97

Пример оформления задания:

Задание. Вычислить площадь прямоугольника со сторонами a и b. Для задания x; для вычислений a, b и площади использовать разные ячейки.

                 

Лабораторная работа № 7. Использование функции ЕСЛИ.

Формат записи функции:

=ЕСЛИ ( условие; выражение1; выражение2 )

При истинном условии выполнится выражение1, при ложном – выражение2. Выражение2 может быть пустым.

	= ЕСЛИ(x>0; x+1; x-1) или =ЕСЛИ(x>0; x+1; ЕСЛИ(x<=0);x-1; ))
	y= ЕСЛИ (a>b; (a^2+b^2)/2; (a^2+b^2)/(a-b+1))
	= ЕСЛИ (И(w>0; w<=5); w^5+1; ЕСЛИ(ИЛИ(w>=5; w>=0); 1; ))
	Проверка на четность проводится путем проверки остатка при делении на 0 =ЕСЛИ(ОСТАТ(a;2)=0;"четное";"нечетное")

Задание 1. Вычислить:

      

Для задания x; для вычисления значения использовать разные ячейки.

Примечание.

 Сравните результат:    x=1;  значение = -0.01991

                                    x=1.25; значение =3.265532

                                    x=2; значение = нет решений

Задание 2. Вычислить:

     

Для задания b и c; для вычисления значения использовать разные ячейки.

Примечание.

 Сравните результат:    b=2; c=4;  значение =0.02117

                                    b=6; c=0;  значение = #ДЕЛ/0!

                                    b=8; c=5;  значение = 0.444806

Задание 3. Вычислить:

       

Для задания a и b; для проверки четности; для вычисления значения использовать разные ячейки.

Примечание.

 Сравните результат:    a=-5.25; b=3;  значение =0.512

                                    a=0; b=3;  значение =1

                                    a=1;b=4;  значение = #ДЕЛ/0!

a=6; b=5;  значение = нет решений