Лабораторная работа № 5. Форматирование ячеек и диаграммы Excel
Лабораторная работа № 5. Форматирование ячеек и диаграммы Excel
1. Создать в Word таблицу “Зарплата”: 4 человека за 3 месяца, скопировать ее в Excel:(Выделить – Правка - Копировать, запустить Excel, Правка – Вставить);
2. Ознакомьтесь с меню и панелями инструментов Excel, вызывая их командой Вид - Панели инструментов.
3. Измените размеры ячеек, перетаскивая мышью границы заголовков строк и столбцов и через Автоподбор. Вставьте новую строку, используя команды группы Вставка, и создайте заголовок таблицы. Расставьте по алфавиту, затем - по величине зарплаты, используя выделение ячеек таблицы и кнопки с буквами А-Я. Сохраните Рабочую книгу, используя команду Файл – Сохранить как.
4. Выделите группу ячеек, скопируйте и перенесите их в другие части таблицы и в другую таблицу, используя команды группы Правка, захват и перемещение границы мышью (затем стереть);
5. Поменяйте шрифт и его размер и направление, цвет фона и символов, границы ячеек в группе ячеек Excel, формат представления чисел (денежный, с процентами и т.д.), используя команды группы Формат - Ячейки.
6. Просуммируйте зарплату за каждый месяц и по каждому работнику, используя выделение и автосуммирование (клавиша S панели инструментов).
7. Магистральная линия развития компьютерной техники - обеспечение наглядного отображения данных. Графическое представление данных часто помогает при их анализе, а также при планировании, так как глаз человека является очень хорошим инструментом - он может увидеть закономерности и отклонения, с трудом выявляемые самыми чувствительными аналитическими методами, а красивое графическое построение обычно соответствует оптимальному плану.
Выделите таблицу (включая заголовки), щелкните по кнопке Мастер диаграмм, затем - по ячейке, где будет верхний левый угол диаграммы. Отвечая на вопросы Мастера диаграмм, постройте диаграммы различного вида. Переместите диаграмму и измените ее размеры, используя перемещение границ мышью и команды группы Правка; при щелкании мышью по диаграмме по углам появляются черные квадратики, означающие, что диаграмму можно редактировать, перемещать и копировать.
Щелкая правой клавишей мыши по объектам диаграммы, измените их параметры – размер, цвет, шрифт и т.д. Выделите два столбика чисел (если они не смежные - выделяйте при нажатой Ctrl) и постройте диаграмму типа Точечная; вы получите корреляционный график, отражающий взаимную зависимость переменных.
Лабораторная работа № 6. Арифметические операции в Excel.
1. Арифметические операции: Вставьте простые числа в ячейки А5-А15, В5-В15, С5-С15 и вставьте в ячейку D5 формулу для расчета =(А5+В5)/(В5-С5). Для ввода формулы в ячейку напишите в ней знак = и формулу, при этом координаты ячеек можно вводить в формулы, щелкая по этим ячейкам мышью; Арифметические операции в Excel задаются с помощью символов: + сложение, - вычитание, * умножение, / деление, ^ возведение в степень, ( ) скобки.
2. Усложните формулу, например,
=(SIN(Корень(ABS(A5)))– B5*TAN(C5)^2)/(EXP(B5/10) – LN(C5^2))
Аргумент функции должен быть заключен в скобки, например SIN(A3/57). Функции можно писать вручную или вводить с помощью Мастера функций, вызываемого клавишей fx. Внимательно читайте тексты в окнах Мастера функций! Ознакомьтесь с меню Мастера функций.
3. Копирование формул: ухватив мышью правый нижний угол активной ячейки с формулой D5 и протащив вдоль столбца или строки, распространяем расчет по формуле на весь столбец или строку.
4. Абсолютная адресация: если надо умножить все ячейки в диапазоне А5-С15 на содержимое одной ячейки, например F5, напишите формулу =A5*$F$5 и скопируйте ее по вертикали и по горизонтали.
5. Дайте какой-либо ячейке имя (Вставка – Имя – Присвоить), умножьте на нее ячейки А5-С15.
6. Арифметические операции с использованием разных рабочих листов и книг: Получить на Листе3 таблицу попарных сумм элементов таблиц, расположенных на Листах 2 и 1. Для этого скопируйте ячейки А5-С15 на Лист2 и введите формулу в ячейку Листа3 формулу следующим образом: =, щелчок по ярлычку Лист1, щелчок по А5, + , щелчок по ярлычку Лист2, щелчок по А5, нажать Enter.
Будет создана и заработает формула
= Лист1!А5 + Лист2!А5
Скопируйте формулу по вертикали и по горизонтали.
7. Включите в формулу элементы таблиц других рабочих книг. Для этого откройте другую рабочую книгу (файл) с таблицей, вернитесь через Окно Меню в свой файл, вставьте в формулу знак арифметической операции, перейдите через Окно в другой файл, щелкните по нужной ячейке; если нужна относительная, а не абсолютная адресация, уберите символы $; нажмите Enter; скопируйте формулу по вертикали и горизонтали.
Анализ хозяйственной деятельности предприятия можно проводить по следующей cхеме: создать таблицы исходных данных и связанные с ними формулами таблицы расчетных величин, с которыми, в свою очередь, связаны наглядные диаграммы, выводимые на терминалы аналитиков и руководства.
Лабораторная работа № 7: Угол между векторами
Косинус угла между векторами А и В: Cos a = A*B / (|A|*|B|)
Где A*B = SAi* Bi =A1*B1+A2*B2+A3*B3– скалярное произведение векторов
Затем применить функции ACOS (арккосинус) и Градусы
(преобразование радиан в градусы).
Лабораторная работа № 8. Действия с матрицами
Вычисление определителя матрицы 3х3.
|
Матрица |
Первые 2 столбца |
1-е диа-гонали |
2-е диа-гонали |
3-и диа-гонали |
||||
      |
3 |
4 |
2 |
3 |
2*4*7 |
3*5*5 |
4*3*6 |
S |
|
3 |
4 |
5 |
3 |
4 |
5*4*4 |
6*5*2 |
7*3*3 |
-S |
|
5 |
6 |
7 |
5 |
6 |
Умножение матриц: Cik = Si Aik * Bki
 |
|
|
|
В |
|
|
|
В трансп. |
|
i |
|
Сi1 |
|
|
|||
|
3 |
5 |
6 |
1 |
2 |
3 |
1 |
4 |
7 |
1 |
3*1 |
5*4 |
6*7 |
S |
||||
|
1 |
3 |
5 |
4 |
5 |
6 |
2 |
5 |
8 |
2 |
1*2 |
3*5 |
5*8 |
S |
||||
|
2 |
3 |
7 |
7 |
8 |
9 |
3 |
6 |
9 |
3 |
2*3 |
3*6 |
7*9 |
S |
||||
|
1 |
2 |
3 |
1 |
2 |
3 |
1 |
2 |
3 |
k |
||||||||
Перемножаются строки матрицы А на столбцы матрицы В, или на строки матрицы В транспонированная, что облегчает расчеты. Пример расчета элемента матрицы С:
С11=A11*B11+A12*B21+A13*B31=3*1+5*4+6*7 = 3+20+42
Лабораторная работа № 9: расчет центра инерции и момента инерции
Центр инерции: Yци = S(mi*yi) / Smi
Момент инерции: I = Smi*ri2 где ri 2 =xi2+yi2 - квадрат расстояния от оси вращения. В динамике вращательного движения момент инерции аналогичен массе в динамике прямолинейного движения.
Рисуем шатун с учетом толщины, вращение относительно точки 0,0
|
-2 |
-1 |
0 |
1 |
2 |
Х |
|
mi |
|
mi*ri |
|
mi*(xi2+yi2) |
||||
|
-1 |
|
1 |
|
1 |
1 |
0 |
1 |
0 |
|||||||
|
0 |
1 |
|
1 |
2 |
0 |
1 |
0 |
1 |
|||||||
|
1 |
|
1 |
|
1 |
1 |
1 |
|||||||||
|
2 |
|
1 |
|
1 |
1*2 |
1*22 |
|||||||||
|
3 |
1 |
2 |
1 |
4 |
4*3 |
и т.д |
|||||||||
|
4 |
2 |
2 |
2 |
6 |
6*4 |
||||||||||
|
5 |
2 |
|
2 |
4 |
4*5 |
||||||||||
|
6 |
|
2 |
|
2 |
2*6 |
||||||||||
|
Y |
шатун |
S |
S |
S |
|||||||||||
Для вычисления момента инерции строим таблицу значений mi*(xi2+yi2) и суммируем эти результаты. Не забудьте зафиксировать с помошью знака $ строку в адресах ячеек координат Х и столбец в адресах координат Y.
Найдите центр инерции и момент инерции различных фигур: стержня, прямоугольника и т.д.
Лабораторная работа № 10: графики различных функций в Excel
1. Построить параболу: задать область определения (х)
от –20 до +20. Для этого занести в соседние ячейки (например А5 и А6) –20 и –19, выделить обе ячейки, поставить курсор на черный квадратик в правом нижнем углу, нажать левую клавишу мыши и потащить вниз до появления числа 20; в ячейку рядом с –20 вставить формулу =0,1*А5^2-А5-11, скопировать ее вниз и построить график. Обычно дробная часть числа отделяется точкой, в русифицированном Excel – запятой. Постройте экспоненту, синусоиду (переведите градусы в радианы с помощью функции РАДИАНЫ), сложную функцию, например
=(SIN(Корень(ABS(A5)))– B5*TAN(C5)^2)/(EXP(B5/10) – LN(C5^2))
2. Функции двух переменных: построить таблицу умножения
|
A |
B |
C |
D |
E |
F |
|
|
5 |
2 |
3 |
4 |
5 |
6 |
|
|
6 |
2 |
=$A6*B$5 |
||||
|
7 |
3 |
|||||
|
8 |
4 |
|||||
|
9 |
5 |
3. Постройте более сложную функцию двух переменных и ее диаграмму.
Использование ячеек с именами позволяет вводить формулы в привычном алгебраическом виде. Присвоив имена Х и У строке и столбцу, постройте таблицу умножения, используя формулу =Х*У.
Лабораторная работа № 11: пределы, производные, интегралы функций
1. Вычислить предел функции, например Sin(x)/x: задать х=1, в следующей ячейке х/2 и т.д. Проследить сходимость функции Sin(x)/x к пределу при хÞ0.
2. Построить синусоиду на интервале 0-360о : задать х от 0 до 360 с шагом 3, перевести в радианы, протабулировать у=Sin(x), протабулировать производную Dy/Dx где Dy= y(i+1) –y(i), Dx = х(i+1) –х(i) и интеграл = С+Sуi *Dx .
|
|
А Х град |
В Х радиан |
С Sin x |
D dy/dx |
E интеграл |
|
3 |
0 |
=РАДИАНЫ(A3) |
=SIN(B3) |
=(C4-C3)/(B4-B3) |
0 (константа) |
|
4 |
3 |
0,052359878 |
0,05234 |
0,996803458 |
=E3+B4*(A5-A4) |
|
5 |
6 |
0,104719755 |
0,10453 |
0,991331611 |
0,008213402 |
|
6 |
9 |
0,157079633 |
0,15643 |
0,983142593 |
0,016404291 |
|
7 |
12 |
0,20943951 |
0,20791 |
0,972258849 |
0,027290522 |
|
8 |
15 |
0,261799388 |
0,25882 |
0,958710211 |
0,040842255 |
|
9 |
18 |
0,314159265 |
0,30902 |
0,983631643 |
0,057022347 |
от экспоненты y=exp(-x), от параболы.
Лабораторная работа № 12: Решение дифференциальных уравнений
Численное решение дифференциальных уравнений dx/dt = -kx и
d2x/dt2 = -m*x :
- присвойте имена ячейкам с коэффициентами (здесь C5=k и G5=m);
- задайте начальные значения x, k, m, для второго уравнения: здесь x=1, k = -0,3, m = 0,3, dx/dt = 0; здесь dt = 1.
- введите в ячейки формулы: B5 =-k*A5, B6 = -k*A6, A6 = A5+B5, F5 =-m*D5, F6=-m*D6, E6=E5+F5, D6=D5+E5;
- одновременно скопируйте вниз формулы в А5:В5, затем в D7:F7;
- постройте графики функций и производных (получите экспоненты и - синусоиды).
|
|
A |
B |
C |
D |
E |
F |
G |
|
|
|
x |
dx/dt=-kx |
k= |
x |
dx/dt |
x’’=-m*x |
m= |
|
|
5 |
1 |
0,3 |
-0,3 |
1 |
0 |
-0,3 |
0,3 |
|
|
6 |
1,3 |
0,39 |
0,7 |
-0,3 |
-0,21 |
|||
|
7 |
1,69 |
0,507 |
0,19 |
-0,51 |
-0,057 |
|||
|
8 |
2,197 |
0,6591 |
-0,377 |
-0,567 |
0,1131 |
|||
|
9 |
2,8561 |
0,85683 |
-0,8309 |
-0,4539 |
0,24927 |
|||
|
10 |
3,71293 |
1,113879 |
-1,03553 |
-0,20463 |
0,310659 |
Лабораторная работа № 13: Корреляционный график, сглаживание и статистические функции
1. Корреляционный график: введите два столбика по 30 случайных двузначных чисел (X и Y), выделите оба столбца, постройте диаграмму типа Точечная.
2. Сглаживание столбца Y: напротив второй
ячейки столбца Y введите формулу = (Y1 +Y2 +Y3)/3 и скопируйте ее до предпоследнего Y (метод скользящего среднего); скопируйте первое и последнее значение Y по горизонтали (они не сглаживаются); повторите процедуру сглаживания еще 2 раза; постройте графики по исходной и сглаженным функциям.
3. Вычислите среднее значение и стандартное отклонение случайной выборки из 30 двузначных чисел по указанным формулам и с использованием функций СРЗНАЧ и СТАНДОТКЛ. Среднее значение Хср = S Хi / N, дисперсия
s2 = (S(Хi - Хср)2 )/ (N –1), стандартное отклонение s = Ö s2 . Для построения столбца (Хi - Хср)2 присвойте имя ячейке Хср.
Лабораторная работа № 14: ветвящиеся алгоритмы и функция ЕСЛИ
Построить таблицу зарплаты в диапазоне 30-500 у.е. Рассчитать налог на зарплату, если при зарплате <100 у.е. налог = зарплата*10%, далее – налог на 100 + 20% на каждый у.е. свыше100 у.е., при зарплате >300 налог на 300 + 30% на каждый у.е. свыше 300 у.е. Постройте корреляционный график.
|
зарплата |
налог |
|
(А12) 50 |
=ЕСЛИ(A12<=100;A12*10%;ЕСЛИ(A12<300;10+(A12-100)*20%;10+40+(A12-300)*30%)) |
|
99 |
9,9 |
|
100 |
10 |
|
150 |
20 |
|
200 |
30 |
|
299 |
49,8 |
|
300 |
50 |
|
400 |
80 |
Корни уравнения Y= f(x) – это значения х, при которых Y
обращается в 0 (график функции пересекает ось абсцисс).
1. Построить параболу с двумя корнями, найти корни, используя функцию Подбор параметра: сделать активной ячейку Y вблизи одного из корней, вызвать Подбор параметра (в Меню Сервис), заставить компьютер подобрать х, чтобы Y обратился в 0.
2. Найти корни, двигая мышью точку графика в 0.
Компьютер сам вызовет Подбор параметра.
3. Найти корни сложного уравнения: протабулируйте сложную функцию на достаточно большом интервале, постройте график, определите, сколько корней и где они примерно находятся, найдите корни через Подбор параметра.
4. Спланировать зарплату на одном из рабочих листов, чтобы суммарная зарплата стала равной заданному значению (см. Лаб. № 6).
Лабораторная работа № 16: решение систем уравнений
Решите систему из трех уравнений с тремя неизвестными вида aiX+biY+ciZ=di (i = 1,2,3) , используя команду Сервис-Поиск решения. Для этого внесите в таблицу приблизительные значения неизвестных X, Y, Z, значения коэффициентов при этих неизвестных ai, bi, ci (i = 1,2,3); перемножьте X, Y, Z на соответствующие коэффициенты и просуммируйте произведения по строкам. Запустите Поиск решения; В качестве целевой ячейки возьмите первую сумму, задайте установку в ней значения первого свободного члена d1 , изменяя ячейки X, Y, Z (поставьте курсор в окно Изменяя ячейки и проведите курсором по X, Y, Z; на две другие суммы наложите ограничения: равенство двум другим свободным членам d2 и d3; нажмите кнопку “Параметры” и ознакомьтесь с параметрами и методами, используемыми при оптимизационных расчетах; закройте окно “Параметры”, нажав ОК, и запустите выполнение программы (Выполнить). Пример:
|
|
C |
D |
E |
F |
Комментарии |
|
3 |
X |
Y |
Z |
Неизвестные |
|
|
4 |
|||||
|
5 |
a1 |
b1 |
c1 |
d1 |
Коэффициенты в |
|
6 |
a2 |
b2 |
c2 |
d2 |
уравнениях |
|
7 |
a3 |
b3 |
c3 |
d3 |
|
