Решение прикладных задач на компьютере. Практикум для студентов
5c8b6e8c

Лабораторная работа № 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– скалярное произведение векторов

|A|=ÖA12+A22+A32 – модуль вектора А
|B|=ÖB12+B22+B32 – модуль вектора B.

Затем применить функции ACOS (арккосинус) и Градусы

(преобразование радиан в градусы).

Лабораторная работа № 8. Действия с  матрицами

Вычисление определителя матрицы 3х3.

Матрица

Первые 2 столбца

1-е диа-гонали

2-е диа-гонали

3-и диа-гонали

2

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

Для упрощения расчетов скопировать 2 первых столбца матрицы в соседние столбцы, затем перемножить элементы по диагоналям и просуммировать со знаками + или -.

Умножение матриц: 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

Вычисляем положение центра инерции и момент инерции относительно оси вращения х=0, у=0: для этого вычисляем массы по срезам шатуна (суммы по строкам), умножаем на значения Y, суммируем и делим на массу.



Для вычисления момента инерции строим таблицу значений 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

Построить производные и интегралы от прямых линий у= 3х-5, у= -3х+5,

от экспоненты 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

Лабораторная работа № 15: решение уравнений

     Корни уравнения 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

Содержание раздела