Файл: информатика.doc

Скачать файл
Заказать решение

ВУЗ: Евразийский национальный университет им. Л.Н. Гумилёва

Категория: Методичка

Дисциплина: Информатика

Добавлен: 05.02.2019

Просмотров: 1831

Скачиваний: 52

ВНИМАНИЕ! Если данный файл нарушает Ваши авторские права, то обязательно сообщите нам.

=СУММ(А5:А9) – сумма ячеек А5, А6, А7, А8, А9;

=СРЗНАЧ(G4:G6) – среднее значение ячеек G4, G5, G6.

Функции могут быть вложенными одна в другой, например: =ОКРУГЛ(СРЗНАЧ(H4:H8);2).

Ввод аргументов функции. Если у функции есть аргументы, появится окно ввода аргументов, элементы которого показаны на рисунке.

1 - имя функции, для которой вводятся аргументы;

2 - поля ввода аргументов;

3 - кнопка сворачивания окна ввода. Если окно свернуто, развернуть его можно повторным нажатием на эту кнопку;

4 - текущие значения аргументов и функции;

5 - область описания функции;

6 - кнопка вызова помощи.

Ввести аргументы функции можно следующим образом:

а) набрать вручную необходимые адреса или диапазоны ячеек;

б) отметить нужные ячейки или диапазоны ячеек на рабочем листе. Окно ввода аргументов при этом можно свернуть (кнопка 3) или перетащить в сторону.

Описание основных функций.

Категория Математические.

ABS (число) – модуль числа.

COS(число) – косинус заданного числа.

EXP(число) – возвращает число е, возведенное в указанную степень.

LN(число) – возвращает натуральный логарифм числа.

SIN (число) – возвращает синус заданного числа.

TAN (число) – возвращает тангенс заданного числа.

КОРЕНЬ (число) – возвращает положительное значение квадратного корня.

ПИ () – возвращает число с точностью до 15 цифр.

СТЕПЕНЬ (число; степень) – возвращает результат возведения в степень;

СУММ (число1; число2; ...) – возвращает сумму всех чисел, входящих в список аргументов;

Статистические функции.

МАКС(аргумент1; аргумент2;…) - ищет максимальный из аргументов;

МИН(аргумент1; аргумент2;…) - ищет минимальный из аргументов;

СРЗНАЧ(аргумент1; аргумент2;…) - вычисляет среднее своих аргументов;

СЧЕТЕСЛИ(диапазон; условие) - подсчитывает число аргументов в диапазоне, отвечающих условию

Арифметические операции:

Сложение

+

Вычитание

-

Умножение

*

Деление

/

Возведение в степень

^

Диаграммы – это графическое представление данных. Они используются для анализа и сравнения данных, представления их в наглядном виде.

Мастер диаграмм позволяет строить диаграммы 14 стандартных типов плоскостного и объемного представления, а так же 24 нестандартных типа. Для автоматического построения диаграммы достаточно нажать клавишу F11.

Редактирование диаграмм выполняется как с помощью контекстного меню, так с помощью команд управляющего меню Диаграмма. К редактированию диаграммы относится:

  • Изменить тип и формат диаграммы;

  • Изменить исходные данные:

  1. переопределить исходный интервал ячеек, на основании которых построена диаграмма;

  2. переопределить ориентацию рядов и их название;

  3. изменить данные, используемые для подписей оси Х;

  4. изменить параметры диаграммы (заголовки, оси, линии сетки, легенду, подписи данных);

  5. изменить размещение диаграммы.


Упражнение 1: Построение графика функции y=cos2(2x)sin(x)+e-x│x│ для х с шагом 0,1.

  1. Создайте на листе 1 следующую таблицу: значения переменной х занесите маркером заполнения.

В ячейку В3 введите формулу: =cos(2*A3)^2*sin(A3)+exp(-3)*ABS(A3), в остальные ячейки формулу скопируйте.

  1. Выделите значения функции и запустите Мастер диаграмм.

  2. Выберите тип диаграммы – График.

  3. Во вкладке Ряд, в Подписях оси Х выделите диапазон значений Х.

  4. Установите свои параметры диаграммы.

  5. Разместите диаграмму в данном листе и переименуйте лист 1 как График функции.


Упражнение 2: Построение диаграмм

  1. Создайте на листе 2 следующую таблицу:

  2. Введите в столбец Итого формулу Сумм, укажите диапазон ячеек и автозаполнением заполните оставшиеся ячейки.

  3. Построите для этой таблицы диаграммы двух типов:

  • Круговую диаграмму, демонстрирующую премиальные выплаты Иванову по месяцам.

  1. Выделите ячейки с числовыми данными и ячейки, которые берутся в качестве подписей к секторам

  2. На панели инструментов или в меню Вставка выберите Диаграммы и тип диаграммы Круговая

  3. В появляющихся диалоговых окнах задаем параметры диаграммы. Диаграммы поместите на данном листе.

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

  1. Лист 2 переименуйте как Диаграммы.


Задания для самостоятельной работы:

  1. Постройте график функций с шагом 0,1… см.таблицу вариантов

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

Производство важнейших видов продукции добывающей промышленности

Виды сырья

2001 г.

2002 г.

2003 г.

Выработка электроэнергии, млрд. кВт/ч

167

173

177

Нефть, млн.т

516

399

307

Природный газ, млрд. м3

641

640

595

Уголь, млн. т

395

337

262

Железная руда, млн. т

107

82

78

Деловая древесина, млн. м3

242

183

93

Рыба и морепродукты, млн. т

8

6

4

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

Таблица вариантов заданий

№ варианта

Задание 1

Задание 2

1

x

;

нужно показать изменения в течение времени и отобразить соотношение величин.

2

y=

нужно показать какую часть занимает каждый вид сырья от общего производства на 2003 г

3

y=

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

4

y=

нужно показать сравнительные показатели добычи угля за каждый период.

5

y=

нужно показать сравнительные показатели всех видов сырья за каждый период производства.

Контрольные вопросы:

  1. Какими способами можно ввести функции в табличном процессоре Excel?

  2. Какие категории функций вы знаете?

  3. Какие статистические функции вы знаете?

  4. Что такое диаграмма? Как стоят диаграммы?

  5. Какие действия относятся к редактированию диаграмм?


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

ТЕМА: «Логические функции. Построение графиков функции с двумя и тремя условиями»


Цель работы: сформировать умение работать с логическими функциями.

Основные понятия:

Логические функции служат для выполнения вычислений в зависимости от выполнения некоторого условия. В условиях могут использоваться операции сравнения =, >, <, <>(не равно), >= (больше или равно), <= (меньше или равно).

Функция ЕСЛИ(лог_выражение; значение_если_истина; значение_если_ложь) результатом является значение1, если логическое_выражение истинно и значение2 в противном случае.

Лог_выражение – это любое значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ.

Значение_если_истина – это значение, которое возвращается, если лог_выражение имеет значение ИСТИНА. Если лог_выражение имеет значение ИСТИНА и значение_если_истина опущено, то возвращается значение ИСТИНА.

Значение_если_ложь – это значение, которое возвращается, если лог_выражение имеет значение ЛОЖЬ. Если лог_выражение имеет значение ЛОЖЬ и значение_если_ложь опущено, то возвращается значение ЛОЖЬ.

 Пример: ЕCЛИ (логическое_выражение; значение1; значение2) - Пример: в ячейке A1 набрано число 30000, а в ячейке B1 формула =ЕСЛИ(A1<20000; 12; 15). Результатом будет число 15, т.к. условие не выполняется.

Функции И, ИЛИ служат для создания сложных условий:

И (логическое_выражение1; логическое_выражение2;....) - возвращает значение “ИСТИНА”, если все аргументы имеют значение “ИСТИНА”, а в противном случае -“ЛОЖЬ”.

ИЛИ (логическое_выражение1; логическое_выражение2;...) - возвращает значение “ИСТИНА”, если хотя бы один из аргументов имеет значение “ИСТИНА”, а противном случае - “ЛОЖЬ”.

П/ры: =ЕСЛИ (И(A1>=20000;A1<40000);15;18) - вычисленное значение равно 15 при величине A1 от 20000 до 40000 и равно 18 в противном случае.

Можно создавать сложные условия и вложением функций ЕСЛИ. Пример:

=ЕСЛИ (A1<20000; 12; ЕСЛИ (A1<40000; 15; 18)) - если величина A1 меньше 20000, вычисленное значение равно 12, иначе если она меньше 40000, то результат равен 15, а в противном случае (то есть, А1 больше 40000) ,формула вернет значение 18.


Упражнение 1. Задан рейтинг по какому-либо предмету в процентах. Если он не ниже 45%, то студент аттестован по данному предмету, в противном случае не аттестован.

  1. Лист1 переименовать на Задание 1.

  2. Заполнить таблицу по образцу.


  1. Определить аттестован ли студент. Выделить ячейку С3. Выполнить следующую команду. Вставка→Функция. В окне Мастера функции выбрать категорию Логические, имя функции ЕСЛИ.

  2. В появившимся окне функции ЕСЛИ. В поле Логическое выражение устанавливаем условие, проверяем ячейку В3, является ли оно больше 45%, если да, то студент аттестован, если нет то не аттестован. Для этого:

В поле Логическое выражение записываем условие: В3>=45%

  • В поле Значение если_истина: «аттестован»

  • В поле Значение если_ложь: «не аттестован»

  • Нажимаем на ОК.

5. Копируем формулу для остальных.


Упражнение 2. Аттестация проводилась по 3 предметам и надо определить аттестован ли студент по всем предметам.

  1. Перейти на Лист2. Переименовать его на Задание2.

  2. Заполнить таблицу по образцу.

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

  • Выделить диапазон В3:D7. Вызвать контекстное меню→Формат ячеек. Перейти на вкладку Числа. Числовой формат выбрать Процентный, Число десятичных знаков установить 0.

  • В ячейке Е3 вызвать функцию ЕСЛИ.

  • На уровне строки формулы с левой стороны щелкнуть на


    1. В списке выбрать Другие функции, в категории Логические выбрать функцию И.

    2. В поле Логическое_значение1: B3>=45%, Логическое_значение2: C3>=45%, Логическое_значение3: D3>=45%.

    3. Щелкнуть в строке формул на ЕСЛИ.

    4. В поле Значение если_истина набрать слова: «аттестован»

    5. В поле Значение если_ложь набрать слова: «не аттестован»

    6. Нажать на ОК

    7. Результат протянуть и для остальных ячеек.



    Упражнение 3: Построение графика функции с двумя условиями, шаг=0,2

    П
    ерейдите на Лист 3 переименуйте его на Задание 3.

    1. В столбце А запишите все значения Х с шагом 0,2, начиная с ячейки А2.

    2. В столбце В2 запишите формулу:

    =Если(А2<=0; (1+2*A2^2-sin(A2)^2)^(1/2); (2+A2)/(2+EXP(-0.1*A2))^(1/3)) или с помощью Мастера функции выберите функцию Если, в появившемся диалоговом окне заполните условие, выражения 1 и выражения 2.

    1. Автозаполнением скопируйте формулы в остальные ячейки.

    2. Постройте график функции.


    Задания для самостоятельной работы:

    Задание 1. Постройте графики функций с шагом 0,2:

    № вар

    При x

    Задание 1

    Задание 2

    1

    Найдите дискриминант D= b2-4ac квадратного уравнения ax2+bx+c=0 … и с помощью функции Если выведите на экран количество корней квадратного уравнения. ( Если D>0, то два корня,)

    2

    Если вес пушного зверька в возрасте от 6-ти до 8-ми месяцев превышает 7 кг, то необходимо снизить дневное потребление витаминного концентрата на 125 г. Количество зверьков, возраст и вес каждого известны. Выяснить на сколько килограммов в месяц снизится потребление витаминного концентрата.

    3

    Отдел работает над двумя проектами, причем каждый сотрудник (ввести фамилии 5 сотрудников на усмотрение) работает только над одним проектом. По каждому проекту начисляется премию (% от оклада). Проценты премий для каждого проекта различны. За 1-й проект – 0,25, за 2-ой проект – 0,35.

    4

    Покупатели магазина пользуются 10% скидками, если покупка состоит более, чем из пяти товаров или стоимость покупки превышает 5000 тг. Составить ведомость, учитывающую скидки: покупатель, количество купленных товаров, стоимость покупки, стоимость покупки со скидкой. Выяснить сколько покупателей сделало покупки, стоимость которых превышает 5000 тг.

    5

    Торговый склад производит уценку хранящейся продукции. Если продукция хранится на складе дольше 10 месяцев, то она уценивается в 2 раза, а если срок хранения превысил 6 месяцев, но не достиг 10 месяцев, то – в 1,5 раза. Получить ведомость уценки товара, которая должна включать следующую информацию: наименование товара, срок хранения, цена товара до уценки, цена товара после уценки.


    Контрольные вопросы:

    1. Какие логические функции вы знаете?

    2. Какое выражение будет выполняться в функции Если при истинном значений логического выражения?

    3. Когда функция И дает истину?

    4. Когда функция ИЛИ дает истину?

    5. Какие операции используются при построении логического выражения в функции Если?


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

    Тема:«Построение поверхности. Решение уравнений методом подбора параметров»


    Цель работы: сформировать умения строить поверхности, решать уравнения методом подбора.

    Основные понятия:

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

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


    У

    пражнение     1: Построение поверхности.


    1. В своей папке создайте рабочую книгу Лаб_Ex_8

    2. C ячейки А2 введите в столбец все значения Х, начиная с –1 с шагом 0,2.

    3. С ячейки В1 введите в строку все значения У, начиная с –1 с шагом 0,2.

    4. На пересечении в ячейку В2 введите формулу: =3*А2^2 – 2* SIN(B1)^2*B1^2

    Здесь нужно подумать: какую адресацию нужно использовать, чтобы при копировании формула была правильной. Поэкспериментируйте, если вы скопируйте в столбец, что изменяется, если вы скопируйте в строку, что изменится? Получается, что Х всегда должны находиться в столбце А, значит фиксируем столбец $А, а У всегда должны находиться в строке 1, значит фиксируем строку $1.

    1. В ячейке В2 редактируем формулу: =3*$А2^2 – 2* SIN(B$1)^2*B$1^2

    2. С помощью маркера автозаполнения, заполняем остальные ячейки столбца В.

    3. Выделяем диапазон В2:В12. Удерживая черный маркер копируем для остальных столбцов.

    1. Выделите всю таблицу данных вместе со значениями Х, У, Z.

    2. Вызовите Мастер диаграмм и выберите тип диаграммы – Поверхность (1-й вид).

    3. Установите свои параметры диаграммы.

    4. Разместите диаграмму на данном листе и переименуйте как Поверхность.



    Упражнение 2: Решение уравнения х3-0,01х2-0,7044х+0,139104=0 методом подбора.

    1. Возьмем интервал [-1;1] с шагом 0,2. Для этого: Введите в ячейку А2 значение –1, а в ячейку А3- значение –0,8. Выделив две ячейки, протяните маркером заполнения до значения 1.

    2. В ячейку В2 введите формулу: =А2^3-0.01*A2^2-0.7044*A2+0.139104 (левая часть уравнения),

    маркером заполнения заполните остальные значения. (получили таблицу значений)

    1. Обратите внимание что функция меняет знак на интервалах [-1;-0.8], [0.2;0.4], [0.6;0.8], за начальные приближения к корню разумно взять средние точки интервалов.

    2. Занесите их в ячейки С2; С3; С4

    3. В ячейку D2 введите формулу: =С2^3-0.01*С2^2-0.7044*С2+0.139104, маркером заполнения протяните в остальные ячейки.

    4. Выделите ячейку D2 .

    5. Выберите команду Сервис/Подбор параметра. На экране отобразится окно Подбор параметра.

    6. В поле Установить в ячейке отобразится ячейка D2.

    7. В поле Значения введите 0. Здесь указывается значение правой части уравнения.

    8. В
      поле       Изменяя значение ячейки введите С2. В данном поле приводится ссылка на ячейку, отведенную под переменную.


    1. Нажмите кнопку ОК.

    2. Аналогично в ячейках С3 и С4 находятся два оставшихся корня.

    3. Графическим способом покажите корни уравнения, т. е. построите график функции у.


    Задания для самостоятельной работы:

    1. Построите поверхность при х, у [-1;1]…

    2. Найти все корни уравнения …


    № варианта

    Задание №1

    Задание №2

    1

    Z=5x2cos2(y) –2y2ey

    x3-2.56x2-1.3251x+4.395006=0

    2

    Z=2x2cos2(x) –2y2

    x3+2.84x2-5.6064x-14.766336=0

    3

    Z=2e0.2xx2 –2y4

    x3+0.85x2-0.4317x+0.043911=0

    4

    Z=x2 –2e0.2yy2

    x3-0.12x2-1.4775x+0.191906=0

    5

    Z=3x2sin2(x) –5e2yy

    x3+0.77x2-0.2513x+0.016995=0

    Контрольные вопросы:

    1. Как расположить данные в таблице, чтобы построить поверхность в трехмерном пространстве?

    2. Какого типа адресация используется при записи формулы поверхности?

    3. В чем заключается метод подбора при решений уравнений?

    4. Для чего служит команда Подбор параметра?

    5. С помощью каких команд можно построить поверхность?



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

    Тема:«Сортировка и фильтрация данных»


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


    Основные понятия:

    Электронные таблицы Excel можно использовать в качестве базы данных. В этом случае электронную таблицу называют списком или базой данных Excel и используют соответствующую терминологию: Строка списка – запись базы данных; Столбец списка – поле базы данных.

    С ортировка это переупорядочивание строки в таблице по любому полю и выполняется командой ДанныеСортировка с установкой необходимых параметров. Целью сортировки является упорядочивание данных.

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

    Фильтрация данных в списке – выбор данных по заданному критерию (условию). В отфильтрованном списке выводятся на экран только те строки, которые содержат определенное значение или отвечают определенным критериям, при этом другие строки скрываются. Осуществляется эта операция с помощью команды ДанныеФильтр.

    Автофильтрация

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

    Расширенный фильтр

    Команда ДанныеФильтрРасширенный фильтр позволяет фильтровать данные с использованием диапазона критериев для вывода только записей, удовлетворяющих определенным критериям. Фильтр обеспечивает использование двух типов критериев для фильтрации записи списка:

    1) Критерий сравнения; 2) Вычисляемый критерий.

    Технология использования расширенного фильтра состоит из двух этапов:

    Этап 1 – формирование области критериев поиска;

    Этап 2 – фильтрация записей списка.


    Упражнение 1: Сортировка данных.

    1. В своей папке создайте рабочую книгу Лаб_10_Ex

    2. Лист 1 переименуйте как Список, создайте таблицу приведенную ниже: Введите имена полей, а во второй строке должны ввести записи т.е. информацию. Ввод с помощью формы данных. Выберите из меню команду Данные/Форма. Появится сообщение Exсel. Ответьте ОК.

      Номер группы

      Номер зач. книжки

      Код предмета

      Таб. № препод.

      Вид занятия

      Дата

      Оценка

      133

      11

      П1

      А1

      Л.

      12.12.04

      4

      133

      12

      П2

      А2

      Пр.

      25.12.04

      4

      133

      13

      П1

      А3

      Л.

      12.12.04

      5

      133

      14

      П2

      А1

      Пр.

      20.12.04

      2

      133

      15

      П1

      А2

      Л.

      12.12.04

      3

      133

      16

      П2

      А1

      Л.

      25.12.04

      4

      133

      17

      П1

      А2

      Пр.

      12.12.04

      5

      133

      18

      П1

      А3

      Пр.

      25.12.04

      5

      134

      19

      П1

      А3

      Л.

      7.12.04

      4

      134

      20

      П2

      А1

      Пр.

      25.12.04

      5

      134

      21

      П1

      А3

      Л.

      7.12.04

      5

      134

      22

      П2

      А2

      Пр.

      25.12.04

      2

      134

      23

      П1

      А2

      Л.

      12.12.04

      4

      134

      24

      П1

      А1

      Л.

      25.12.04

      5

      134

      25

      П2

      А3

      Л.

      7.12.04

      3

    3. Выполнить сортировку данных таблицы по возрастанию столбца Код предмета. Для этого нужно :

    • Установите курсор в таблице, выполнить команду меню Данные→ Сортировка;

    • В окне сортировка диапазона выбрать сортировку по столбцу “ Код предмета ” и установите опцию по возрастанию.

    1. Выполнить сортировку данных по возрастанию по 3 столбцам: по номеру группы, по коду предмета., таб №преподавателя. Для этого следуют установить курсор в таблице, выполнить команду меню Данные→ Сортировка и в диалоговом окне сортировка установить:

    • в строке Сортировать по – поле «по номеру группы» по возрастанию;

    • в строке Затем по – поле «по коду предмета», по возрастанию;

    • в строке Последнюю очередь, по поле «таб №преподавателя», по возрастанию.


    Упражнение 2: Фильтрация данных.

    1. Переименуйте Лист2 на Автофильтр и скопируйте на него исходную базу данных из листа Список.

    2. Выберите из списка данные используя критерий: Для преподавателя А1 выбрать сведения о сдаче экзамена на положительную оценку по виду занятия – Л.Для этого:

    • Примените Автофильтрацию, установив курсор в область списка и выполните команду Данные – Фильтр - Автофильтр.

    • В столбце Таб. № преподавателя нажмите на кнопку и из списка условий отбора выберите А1;

    • Переидите в столбец Вид занятия и из списка фильтра выберите Л., затем в столбце Оценка из списка условий отбора выберите (Условие…) и в диалоговом окне сформируйте условие отбора >3; т.е. выберите условие Больше, а слева наберите 3.

    • Посмотрите на результат.

    1. Отмените результат автофильтрации. Для этого нажмите на в каждом поле где вы применили Автофильтр и выберите Все.

    2. Выберите из списка данные, используя критерий: для группы 133 получить сведения о сдаче экзамена по предмету П1 на оценки 3 и 4.

    3. Отмените результат автофильтрации.


    Упражнение 3: Расширенный фильтр

    1. Переименуйте Лист3 на Расширенный фильтр и скопируйте на него исходную базу данных.

    Этап 1. Формирование диапазона условий по типу Критерий сравнения

    1. При использовании Расширенного фильтра необходимо с начало задать интервал критериев – это область, где задаются критерии фильтрации. Для этого:

    • Скопируйте имена полей (шапка) списка в другую область например в А18 на том же листе.

  • Сформируйте в области условий отбора Критерий сравнения – о сдаче экзаменов студентами группы 134 по предмету П2 на оценки 2 или 5. Для этого:

    • Запишите критерии поиска в интервал критериев

    Номер группы

    Номер зач. книжки

    Код предмета

    Таб. № препод.

    Вид занятия

    Дата

    Оценка

    134


    П2




    2

    134


    П2




    5

    Этап 2. – фильтрация записей списка.

    1. Произведите фильтрацию записей.

    • Поместите курсор в область базы данных

          • Выполните команду Данные→Фильтр→ Расширенный фильтр.

          • В диалоговом окне Расширенный фильтр установите следующие параметры:

          • Установит переключатель скопировать результат в другое место

          • В поле Исходный диапазон укажите диапазон таблицы: А1:G16

          • В Диапазон условии укажите диапазон таблицы критерии: А18:G20.

          • В поле Поместить результат в диапазон укажите ячейку А21.

          • Нажмите кнопку ОК.


          Задания для самостоятельной работы:

          1. Скопируйте исходную базу данных с листа Список на новый лист и отсортируйте Список1 …

          2. Установите автофильтр и выберите данные по следующему условию: см. таблицу

          3. Установите расширенный фильтр: см. таблицу


          №

          Задание 1

          Задание 2

          Задание 3

          1

          По номеру группы и коду пред

          Записи с 7.12.04 по 12.12.04

          Гр. 133, оценки 4 или 5, П1

          2

          По № зач.кн. и виду занятий

          Предметы с кодом П1 и оценки 4

          Гр. 134, оценки 2 или 4

          3

          По таб. № преподав. и № группы

          № зач.кн. с15 по 20

          А2, оценки 4 или 5, Пр.

          4

          По виду занятий и дате

          Таб. № преподав. А1 и А2»

          Гр. 133, А1 или А2, Л.

          5

          По дате и оценке

          Оценки 2 и 3

          Гр. 134, № зач.>21 , Л.


          Контрольные вопросы:

          1. Что такое база данных?

          2. Что значит сортировать данные?

          3. Какие бывают виды фильтрации?

          4. Чем отличается сортировка от фильтрации?

          5. Каким образом устанавливается расширенный фильтр?



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

          Тема: «Матрицы и матричные формулы»

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

          Основные понятия:

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

          При вводе формулы массива (формула, выполняющая несколько вычислений над одним или несколькими наборами значений, а затем возвращающая один или несколько результатов. Формулы массива заключены в фигурные скобки { } и вводятся нажатием клавиш CTRL+SHIFT+ENTER.)

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

          Функция МУМНОЖ ( ) определяет результат произведения нескольких матриц. При использовании этой функции важно помнить, что можно перемножить не только 2 квадратные, но и 2 прямоугольные матрицы, но при этом количество строк второй матрицы должно быть равно количеству столбцов первой матрицы, а число столбцов второй числу строк первой.

          Функция МОПРЕД ( ) вычисляет определитель квадратной матрицей. Результатом вычисления является число.

          Функция МОБР ( ) возвращает обратную матрицу.


          Упражнение 1. Сложение, вычитание и произведение массивов.

          1. Переименуйте Книгу на Лаб_11_Матрицы, а Лист 1 на Задание 1

          2. Оформите следующие таблицы:

          A

          B

          C

          D

          E

          F

          G

          H

          I

          J

          K

          L

          M

          N

          1

          Пример 1


          Пример 2


          Пример 3

          2

           

          15

          12

          16


           

          7

          8

          9


           

          8

          9

          7

          3

          А=

          18

          5

          16


          В=

          4

          9

          15


          C=

          15

          80

          21

          4

           

          19

          2

          25


           

          2

          6

          9


           

          31

          25

          63

          5















          6

           

           

           

           


           

           

           

           


           

           

           

           

          7

          А+В=

           

           

           


          В-С=

           

           

           


          А2=

           

           

           

          8

           

           

           

           


           

           

           

           


           

           

           

           


          1. Для выполнения Примера 1 выполните следующие действия:

          • Выделите диапазон ячеек, в который требуется ввести формулу массива, т.е B6:D8

          • Нажмите на знак =

          • Выделите диапазон матрицы А

          • Нажмите на знак +

          • Выделите диапазон матрицы В

          • Нажмите сочетание клавиш CTRL+SHIFT+ENTER.

          • У вас в строке формулы должно получится следующая формула: = {B2:D4+G2:I4}

        1. Аналогичные действия выполните и для примера 2 и примера 3


          Упражнение 2. Умножение матриц А и В, нахождение обратной матрицы и определителя матриц

          1. Переименуйте Лист 2 на Задание 2

          2. Оформите следующие таблицы:

            A

            B

            C

            D

            E

            F

            G

            H

            I

            J

            K

            L

            M

            N

            1


            2

            6

            4



            -1

            3,1

            7



             

             

             

            2

            А =

            3,1

            7,1

            1


            В =

            2,3

            6

            1


            С=А*В=

             

             

             

            3


            -1,1

            0

            -0,2



            0

            2

            3,22



             

             

             

            4















            5















            6

            А-1=





            А*А-1=





            Det(A)=




            7















            8















          3. Вычислите произведение матриц А*В. Для этого:

          • Выделите область L1:N3

          • Вызовите Мастер функций, выберите категорию Математические и функцию МУМНОЖ, откроется окно Палитры формул.

          • Для ввода аргумента функции в поле Массив 1 выделите первую матрицу, затем перейдите в поле Массив 2 и выделите вторую матрицу, т.е матрицу В

          • Подтвердите ввод формулы массива <Ctrl>+<Shift>+<Enter>

        2. Найдите обратную матрицу к матрице А. Для этого:

          • Выделите область, в которую хотите поместить результат, B5:D7

          • Введите формулу, для этого:

          • Вызовите мастер функции и в категории математические выберите функцию МОБР.

          • В поле Массив выделите матрицу А

          • Для подтверждения ввода формулы, содержащей массив, нажмите <Ctrl>+<Shift>+<Enter>

          • Проверьте результат, вычислив произведение исходной матрицы и обратной к ней

        3. Вычислите определитель матрицы А. Для этого

          • Установите курсор в ячейку L6, вызовите функцию МОПРЕД, и выделите значения матрицы А.


          Упражнение 3.Необходимо решить систему линейных уравнений

          Для реконструкции 3 цехов завода выделены деньги. Для 1 цеха 510000, для второго 180000, для третьего 480000. Для всех цехов необходимо купить станки трех видов А, В, С. Причем для 1 цеха 4 станка А, 8 станков В и 1 станок С. Для 2 цеха 1 станок А, 2 станка В и 1 станок С и для 3 цеха 1 станок А, 5 станков В и 4 станка С. По какой максимальной цене можно покупать станки.

          Обозначим максимальные цены . Тогда

          Представим данные в виде матриц А, х, b,

          где матрица А – матрица коэффициентов, х – матрица неизвестных и b-матрица свободных чисел

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

          Наиболее простыми методами решения системы линейных уравнений является метод Крамера и метод обратной матрицы.

          Технология работы.

          1. Переименуйте Лист1 на Задание 3

          2. Вычислим A-1 описанным ранее методом. Так как Ax=b , то x=A-1 b. Для определения х необходимо перемножить полученную обратную матрицу A-1 и столбец свободных членов . Алгоритм представлен на рис. 1 а результаты решения на рис. 2

          B

          C

          D

          E

          F

          G

          H

          I

          J

          K

          L

          M

          N

          O

          P

          Q

          2


          4

          8

          1



          510000




          =МОБР(C2:E4)




          =МУМНОЖ(K2:M4;H2:H4)


          3

          A

          1

          2

          1


          B

          180000


          A-1


          X


          4


          1

          5

          4



          480000






          5

















          Рис.1

          B

          C

          D

          E

          F

          G

          H

          I

          J

          K

          L

          M

          N

          O

          P

          Q

          2


          4

          8

          1



          510 000



          -0,3333

          3

          -0,667



          50000


          3

          A

          1

          2

          1


          B

          180 000


          A-1

          0,33333

          -1,6667

          0,333


          X

          30000


          4


          1

          5

          4



          480 000



          -0,3333

          1,3333

          0



          70000


          5


















          Задания для самостоятельной работы:

          1. Сложите массивы А+В=...

          2. Вычислите произведение матриц А*В=...

          3. Найдите обратную матрицу к матрице (А+В). Проверьте результат, вычислив произведение исходной матрицы и обратной к ней.

          4. Решите систему линейных уравнений методом обратной матрицы.


          № вар

          Задание 1-3.

          Задание 4

          1

          2

          3

          4

          5


          Контрольные вопросы:

          1. Что представляет с собой массив?

          2. С помощью каких клавиш обеспечивается ввод формул во все элементы массива?

          3. Что выполняет функция МОПРЕД?

          4. Какая функция выполняет умножение массивов?

          5. Как решить систему линейных уравнений с помощью обратной матрицы?


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

          Тема: «Табличный процессор Excel. Массивы. Вычисление сложных выражений. Метод Крамера»


          Цель работы: сформировать умение вычислять сложные выражения, решать систему линейных уравнений с помощью метода Крамера.

          Основные понятия:

          Функция ТРАНСП ( )преобразует вертикальный диапазон ячеек в горизонтальный, и наоборот. Транспонирование массива заключается в том, что первая строка массива становится первым столбцом нового массива, вторая строка массива становится вторым столбцом нового массива и так далее.


          Упражнение 1. В качестве применения использования формулы массива приведем расчет цен группы товаров с учетом НДС (налог на добавленную стоимость).

          В диапазоне В2:В4 даны цены группы товаров без учета НДС. Необходимо найти цену каждого товара с учетом НДС (который будем полагать равным 25%). Таким образом, необходимо умножить массив элементов В2:В4 на 125%. Результат надо разместить в ячейках диапазона С2:С4.


          Упражнение 2. Вычисление функции, зависящей от элементов массива. Пусть в диапазоне А6:В7 имеется некоторый массив данных (введите свои значения). Требуется найти массив, элементы которого равны значениям функции от соответствующих элементов искомого массива в ячейки D6:E7.


          Упражнение 3. Вычислить транспонированную матрицы AT к матрице А


          		1.

            A

            B

            C

            D

            E

            F

            G

            H

            I

            9










            10


            2

            3

            5






            11

            A=

            5

            2

            7


            AT=




            12


            4

            2

            1






            Введите следующие значения матриц:







          1. Для вычисления транспонированной матрицы выделите диапазон G10:I12

          2. В строке формулы через введите следующую формулу =ТРАНСП(B10:D12)


          Упражнение 4. Вычисление сложных выражений.


          где – вектор из компонентов, и – матрицы размерности , причем, , и , , .


          1. Введите данные как в рисунке.

          2. Д ля решения этой задачи нам потребуется функция рабочего листа (SUM), которая суммирует все числа из диапазона ячеек.

          3. Введите в ячейку следующую формулу:

          5. Завершите ввод нажатием комбинации клавиш Ctrl + Shift + Enter.

          6. Этот же результат можно получите, введя в ячейку D6 простую формулу:

          7. .


          Упражнение 5. Решение системы линейных уравнений Методом Крамера

          Дана линейная система , где – матрица коэффициентов, – столбец (вектор) свободных членов, – столбец (вектор) неизвестных.

          По методу Крамера вычисляется по формуле , где - определители матрицы , - определитель исходной матрицы т.е матрицы А. получается из матрицы A заменой i-того столбца столбцом "b"-свободных членов. Это определяет метод реализации алгоритма в Excel.

          Например, нужно решить систему линейных уравнений с 3 неизвестными, с коэффициентами и с правой частью .

          1. Вводим матрицы A, b, затем копируем матрицу A три раза (начальная заготовка для матрицы ) рис.1.

          B

          C

          D

          E

          F

          G

          H

          I

          J

          2


          4

          8

          1





          510 000

          3

          A

          1

          2

          1


          Det(A)=


          В

          180 000

          4


          1

          5

          4





          480 000

          5










          6


          4

          8

          1






          7

          A1

          1

          2

          1


          Det(A1)=


          X1=


          8


          1

          5

          4






          9










          10


          4

          8

          1






          11

          A2

          1

          2

          1


          Det(A2)=


          X2=


          12


          1

          5

          4






          13










          14


          4

          8

          1






          15

          A3

          1

          2

          1


          Det(A3)=


          X3=


          16


          1

          5

          4






          17










          Рис. 1

          1. Затем копируем столбец b и вставляем его в А1 в 1 столбец, в А2 во 2 столбец, в А3 - в 3 столбец

          2. Вычислите определители полученных матриц в ячейки Н7, Н11, Н15.

          3. После определения определителей матриц А1, А2, А3 легко можно получить Х1 по формуле , и так для Х2, Х3


          Задания для самостоятельной работы:

          1. Решить системы линейных уравнений а) Методом Крамера

          2. Вычислите б) квадратичную форму .


          Таблица 1.

          №

          Задание № 1

          Матрица


          №

          Задание №1

          Матрица

          1

          а)

          б)


          4

          а)

          б)

          2

          а)

          б)


          5

          а)

          б)

          3

          а)

          бв)








          3. Найдите значение сложных выражений , где а, x, y – вектор из n компонентов, и – матрица размерности .

          Таблица 2.

          №

          Выражения

          Вектор а, x, y

          Матрица ,

          1

          2

          3

          4

          5


          Контрольные вопросы:

          1. Что значит транспонировать матрицу?

          2. С помощью каких функций сумм вычисляются сложные выражения?

          3. В чем заключается метод Крамера?

          4. При каком условии система линейных уравнений имеет решение?

          5. Что выполняет функция СУММКВ?


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

          Тема: «Поиск решения и решение оптимизационных задач. Линейная оптимизационная задача. Планирования производства красок»


          Цель работы: сформировать умение решать линейные оптимизационные задачи.

          Основные понятия:

          Поиск решения (Solver) – это единый, мощный инструмент решения оптимизационных задач.

          Упражнение 1. Задача об оптимальном производстве красок. Небольшая фабрика выпускает два типа красок: для внутренних (I) и наружных работ (E). Продукция обоих видов поступает в оптовую продажу. Для производства красок используются два исходных продукта А и В. Максимально возможные суточные запасы этих продуктов составляет 6 т и 8 т соответственно. Расходы А и В на 1 т соответствующих красок приведены в таблице 1.

          Таблица 1.

          Исходный продукт

          Расход исходных продуктов (в тоннах) на тонну краски

          Максимально возможный запас, т

          краска Е

          краска I

          А

          1

          2

          6

          В

          2

          1

          8

          Изучение рынка сбыта показало, что суточный спрос на краску I никогда не превышает спроса на краску E более чем на 1 т. Кроме того, установлено, что спрос на краску I никогда не превышает 2 т в сутки. Оптовые цены одной тонны красок равны: 3 000 руб. для краски E и 2 000 руб. для краски I . Какое количество краски каждого вида фабрика, чтобы доход от реализации продукции был максимальным?

          1.1. Для решения этой задачи необходимо сначала построить математическую модель:

          1. Для определения каких величин строится модель? Что является переменными модели?

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

          3. каким ограничениям должны удовлетворять неизвестные?

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

          – суточный объем производства краски I; – суточный объем производства краски E.

          Суммарная суточная прибыль от производства хI краски I и хЕ краски E равна .

          Целью фабрики является определение среди всех допустимых значений и таких, которые максимизируют суммарную прибыль, т.е. целевую функцию . Перейдем к ограничениям, которые налагаются на и . Объем производства красок не может быть отрицательным, следовательно, . Расход исходного продукта для производства обоих видов красок не может превосходить максимально возможный запас данного исходного продукта. Следовательно:

          Кроме того ограничения на величину спроса на краски таковы:

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

          Максимизировать при следующих ограничениях:











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


          1.2. Введите данные как в таблице 2. Отведем ячейки А3 и В3 под значения переменных и .

          Таблица 2.

          1 .3. Выберите команду Сервис/Поиск решения. Если отсутствует команда Поиск решения, то для ее установки необходимо выполнить команду Сервис/Надстройки/Поиск решения. В открывшемся диалоговом окне, в поле Установить целевую ячейку сделайте ссылку на ячейку С4, включите Равной Максимальному значению, введите в поле Значению 0, в поле Изменяя ячейки укажите диапазон ячеек А3:В3. Переходите в поле Ограничения и нажмите кнопку Добавить и в следующем диалоговом окне Добавления ограничения введите ограничения:



          1.3. Теперь нажмите кнопку Параметры в диалоговом окне Поиск решения, для того чтобы проверить, какие параметры заданы для поиска решений.

          1.4. Запишите в тетради условие задачи, алгоритм нахождения решений и сделайте соответствующий вывод.


          Задания для самостоятельной работы:


          Вариант 1. Предприятие электронной промышленности выпускает две модели радиоприемников, причем каждая модель производится на отдельной технологической линии. Суточный объем производства первой линии – 60 изделий, второй линий 75 изделий. На радиоприемник первой модели расходуется 10 однотипных элементов электронных схем, на радиоприемник второй модели – 8 таких же элементов. Максимальный суточный запас используемых элементов равен 800 единицам. Прибыль от реализации одного радиоприемника первой и второй модели равна $30 и $20 соответственно. Определить оптимальный суточный объем производства первой и второй модели.


          Вариант 2. Процесс изготовления двух видов промышленных изделий состоит в последовательной обработке каждого из них на трех станках. Время использования этих станков для производства данных изделий ограничено 10 ч в сутки. Найти оптимальный объем производства изделий каждого вида.

          Изделие

          Время обработки одного изделия, мин

          Удельная прибыль

          Станок 1

          Станок 2

          Станок 3

          1

          10

          5

          8

          2

          2

          5

          20

          15

          3


          Вариант 3. Фирма имеет возможность рекламировать свою продукцию, используя местные радио- и телевизионную сеть. Затраты на рекламу в бюджете фирмы ограничены $1000 в месяц. Каждая минута радиорекламы обходится в $5, а минута телерекламы – в $100. Фирма хотела бы использовать радиосеть, по крайней мере, в два раза чаще, чем сеть телевидения. Опыт прошлых лет показал, что объем сбыта, который обеспечивает каждая минута телерекламы, в 25 раз больше сбыта, обеспечиваемого одной минутой радиорекламы. Определить оптимальное распределение ежемесячно отпускаемых средств между радио- и телерекламой.


          Вариант 4. Автозавод выпускает автомобили четырех видов: W,X,Y,Z (Хат, Седан, Джип, Вагон). Ежемесячно он может выпускать не более 1000 автомобилей (при этом каждого типа – не меньше 100). В течение месяца 1000 работников завода работают по 150 часов каждый. Завод может израсходовать за месяц не более 900 тонн стали.

          Модель продукций

          Затраты стали

          Затраты времени

          Прибыль с единицы продукта

          W (Хат)

          0,76

          80

          625

          X (Седан)

          1,00

          130

          825

          Y (Джип)

          0,72

          110

          600

          Z (Вагон)

          1,50

          140

          1200


          Контрольные вопросы:

          1. Какое средство Excel используется для решения линейных оптимизационных задач?

          2. Каким образом записываются ограничения?

          3. Что такое целевая функция?

          4. Что такое математическая модель?

          5. Как вы понимаете переменные модели?



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

          Тема: «Функции Microsoft Excel для расчета операций по кредитам и займам»


          Цель работы: сформировать умение работать с финансовыми функциями для расчета операций по кредитам и займам.

          Основные понятия:

          В пакете Microsoft EXCEL существует группа функций, предназна­ченная для расчета финансовых операций по кредитам, ссудам, займам. Эти расчеты основаны на концепции временной стоимости денег и предполагают неравноценность денег, относящихся к разным моментам времени. Эта группа функций охватывает следующие расчеты:

          • определение наращенной суммы (будущей стоимости);

          • определение начального значения (текущей стоимости);

          • определение срока платежа и процентной ставки;

          • расчет периодических платежей, связанных с погашением займов;

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

          Операций

          Определение

          Синтаксис

          Формула

          Будущая стоимость

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

          БС(ставка;кпер;плт;пс;тип), где:

          Ставка  — это процентная ставка за период.

          Кпер   — это общее число периодов

          Плт  — это выплата, производимая в каждый период;

          Пс  — это первоначальная стоимость

          Тип  — это число 0 или 1, обозначающее, когда должна производиться выплата. Если 0 - В конце периода, 1- В начале периода

          FV=PV*(1+I)N, где:

          n -общее число периодов;

          i-процентная ставка;

          pv-текущая стоимость вклада;

          fv-будущая стоимость вклада.

          Первона-чальная cтоимость

          Возвращает текущий стоимость вклада на основе постоянных периодических платежей. Этот расчет является обратным к опре­делению будущей стоимости

          ПС(ставка;кпер;плт;бс;тип)

          PV=FV/(1+I)^N.

          Количес-тво периодов

          Возвращает общее количество периодов выплаты для инвестиции на основе периодических постоянных выплат и постоянной процентной ставки.

          КПЕР(ставка;плт;пс;бс;тип)


          Процен-тная ставка

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

          СТАВКА(кпер;плт;пс;бс;тип)


          Выплаты

          Возвращает сумму периодического платежа на основе постоянства сумм платежей и постоянства процентной ставки.

          ПЛТ(ставка;кпер;пс;бс;тип)


          Упражнение 1.1 Рассчитать, какая сумма окажется на счете, если 27000 т. положены на 10 лет под 13,5% годовых, проценты начисляются каждые полгода.

          1. Сохраните книгу под названием Фин. Функции

          2. Оформите Задачу1.1 как показано на рис.

          2. Введите исходные данные в ячейки

          3. Рассчитать будущую стоимость, для этого:

          1-й способ: с помощью применение формулы. В ячейку В6 введите формулу: =B5*(1+B3/2)^(B4*2)

          2-й способ: с помощью использования финансовой функции БС:

          1. Установите курсор в ячейку В7 выполните команду ВставкаФункция категория ФинансовыеБС

          2. В окне функции БС введите следующие значения ячеек: Ставка: B3/2; Кпер: B4*2;ПС: -B5


          Упражнение 1.2. Предположим, есть два варианта инвестирования средств в течение 4 лет: в начале года под 26% годовых или в конце каждого года под 38% годовых. Пусть ежегодно вносится 300 тыс. тенге. Определим, сколько денег окажется на счете через 4-го года для каждого варианта.

          1. Введите данные и для 1-го варианта и для 2-го

          2. Установите курсор в в ячейку где необходимо произвести вычисления (Е6)

          3. Вызовите нужную функцию.

          4. Установите необходимые параметры.

          Упражнение 2.1.

          Фирме потребуется 500000 тенге через 12 лет. В настоящее время фирма располагает деньгами и готова положить их на депозит единым вкладом, чтобы через 12 он достиг 500000. т. Определить необходимую сумму текущего вклада, если ставка по нему составляет 12% в год.

          Упражнение 2.2.Определить текущую стоимость обязательных платежей размером 27000 в течение 7 лет, если процентная ставка составляет 8%.

          Упражнение 3.1.

          Необходимо определить количество периодов, если первоначальная стоимость проекта (ПС) = 12000 тенге, будущая стоимость (БС) = 65000 тенге, процентная ставка(СТАВКА) 12% годовых.

          Упражнение 3.2.

          Вы собираетесь брать в долг 90000 тенге при годовой ставке 15% и выплачивать по 15000 тенге в месяц. Определить число периодов выплат. Обратите внимание на единицы измерения, выплаты производятся по месяцам, а процентная ставка годовая. Переведите выплаты в годовые или процентную ставку в ежемесячные.

          Упражнение 4.1.

          Необходимо определить процентную ставку, при котором первоначальная стоимость 32000 тенге через 5 лет будет равна 120000 тенге

          Упражнение 4.2..

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


          Задания для самостоятельной работы:

          1. Вас просят дать в долг Р тенге и обещают вернуть по А тенге в течение n лет. При какой годовой процентной ставке эта сделка имеет смысл?

            Варианты

            1

            2

            3

            4

            5

            n

            7

            8

            9

            10

            11

            Р

            170000

            200000

            220000

            300000

            350000

            А

            30000

            31000

            33000

            34000

            41000

          2. Вы берете в долг Р тенге под годовую ставку i % и собираетесь выплачивать по А тенге в год. Сколько лет займут эти выплаты?


          Вариант

          1

          2

          3

          4

          5

          P

          170000

          200000

          220000

          300000

          370000

          A

          31000

          32000

          33000

          34000

          41000

          I

          3

          4

          5

          6

          7

          1. Вы собираетесь вкладывать по В тыс. тенге в течение N лет при годовой ставке П%. Сколько денег будет на счету через N лет?

            Вариант

            1

            2

            3

            4

            5

            В

            52

            53

            55

            60

            54

            П

            10

            10.5

            11

            12

            10.9

            N

            5

            6

            8

            10

            9

          2. Определите текущую стоимость обязательных ежемесячных платежей размером Т тыс. тенге в течение N лет, если процентная ставка составляет П% годовых.

            Вариант

            1

            2

            3

            4

            5

            Т

            25

            30

            40

            35

            45

            П

            8

            9

            11

            10

            7

            N

            5

            6

            7

            8

            5

          3. Определите ежемесячные выплаты по займу в Т тыс. тенге, взятому на K лет под П% годовых.

          Вариант

          1

          2

          3

          4

          5

          Т

          25

          30

          40

          35

          42

          П

          6

          8

          9

          10

          7

          К

          5

          6

          7

          8

          5


          Контрольные вопросы:

          1. Какие финансовые функции по кредитам и займам вы знаете?

          2. Чем отличается будущая стоимость от первоначальной?

          3. Для чего нужна функция КПЕР?

          4. Какой параметр в функции БС определяет начисление процентов в начале или в конце периода?

          5. Какая функция определяет ежегодные выплаты?


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

          Тема: «Расчет периодических платежей. Вычисление амортизации»


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

          Основные понятия:

          Функции EXCEL позволяет вычислять следующие величины, связанные с периодическими выплатами и амортизацией:

          Операции

          Определение

          Синтаксис

          Расчет платежей по процентам.

          Функция ПРПЛТ вычисляет платежи по процентам за данный период на основе периодических постоянных выплат и постоянной процентной ставки.

          ПРПЛТ(ставка;период;кпер;пс;бс;тип)

          Расчет суммы платежей по процентам по займу

          Функция ОСПЛТ возвращает величину накопленных доходов по займу, которая погашается равными платежами в каждом расчетном периоде

          ОСПЛТ(ставка;период;кпер;пс;бс;тип)

          Расчет амортизации за один период .

          Под амортизацией подразумевается уменьшение (обычно на единицу времени) стоимости имущества в процессе эксплуатации

          Функция АПЛ возвращает величину амортизации актива за один период

          АПЛ(нач_стоимость;ост_стоимость;время_эксплуатации)

          Нач_стоимость — затраты на приобретение актива.

          Ост_стоимость — стоимость в конце периода амортизации (иногда называется остаточной стоимостью актива).

          Время_эксплуатации — количество периодов, за которые актив амортизируется (иногда называется периодом амортизации).

          Расчет амортизации за данный период.

          Функция АСЧ возвращает величину амортизации актива за данный период.

          АСЧ(нач_стоимость;ост_стоимость;время_эксплуатации;период)


          Упражнение 1. Вычислить 30-летнюю ипотечную ссуду покупки квартиры за 35000 $ с годовой ставкой 8% и начальном взносе 20%. Сделать расчет для ежемесячных и ежегодных выплат.

          1. Запустите Excel.

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

            A

            B

            C

            D

            1

            Расчет ипотечной ссуды (функция ПЛТ)

            2

            Исходные данные

             

             

             

            3

            Цена

            35000$

             

             

            4

            Первый взнос

            20%

             

             

            5

            Годовая процентная ставка

            0,08

             

             

            6

            Размер ссуды

             

             

             

            7

            Срок погашения ссуды

            30

            лет 

             

            8

            Результат расчета

             

             

             

            9

            Ежемесячные выплаты

             

             

             

            10

            Ежегодные выплаты

             

             

             

            11

            Общая сумма выплат




          3. Вычислить размер ссуды. Для того чтобы вычислить размер ссуды необходимо в ячейку В6 ввести следующую формулу: =B3*(1-B4)

          4. Для вычисления ежемесячных выплат, установите курсор в ячейку В9, вызовите функцию ПЛТ, установите необходимые параметры

          5. Самостоятельно вычислите ежегодные выплаты и общую сумму выплат, т.е. сумму выплат за 30 лет


          Упражнение 2. Вы хотите взять ссуду 10000 долларов на 1 год с ежемесячной выплатой. Вы готовы по прошествии каждого месяца платить в течение года по 900 долларов. Какой должна быть процентная ставка? Эта задача может быть решена подбором параметра.

          1. Перейдите на Лист2, переименуйте ее на Задача2.

          2. Создайте таблицу, подобную табл. 2.


            А

            В

            С


            1

            Решение задачи с помощью подбора параметра

            2

            Сумма суды

            10000



            3

            Количество лет

            1



            4

            Ставка




            5

            Ежемесячные выплаты




          3. В этой таблице ячейка В4 будет играть роль регулируемой ячейки. В этой ячейке вы получите результат поиска искомого значения процентной ставки. В начале же в эту ячейку запишите начальное значение процентной ставки, например 10,0%, с которого начнется поиск.

          4. С помощью команды меню Формат, Ячейки установите в ячейке B4 процентный формат с одним знаком дробной части.

          5. В ячейке В5 запишите формулу = ПЛТ(B4/12;В3*12;-10000). Нажмите на ENTER

          6. Выделите ячейку В5 таблицы с формулой.

          7. Выполните команду Сервис, Подбор Параметра. Появится диалоговое окно Подбор параметра В поле Установить в ячейке уже будет находиться ссылка на выделенную на предыдущем шаге ячейку с формулой.

          8. В поле Значение введите величину, которой должна быть равна ежемесячная выплата в результате подбора значения процентной ставки. В рассматриваемом примере это значение 900.

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


          Упражнение 3. Вычислите платежи по процентам за первый месяц от 3 летнего займа в 800000 тенге из расчета 10% годовых.

          1. Перейдите на Лист3, переименуйте ее на Задача3.

          2. Создайте таблицу на основе условии задачи

          3. Для вычисления выплат по процентам используйте функция ПРПЛТ.


          Упражнение 4. Вычислить основные платежи, плату по процентам, общую ежегодную выплату и остаток долга на примере ссуды 100000$ на срок 5 лет при годовой ставке 2%.

          1. Перейдите на Лист4, переименуйте ее на Задача4.

          2. Создайте таблицу 3

            А

            В

            С

            D

            Е

            1

            Погашение долга

            2

            Ставка

            2%




            3

            Срок

            5




            4

            Ежегодная выплата





            5

            Размер ссуды

            100000




            6

            Год

            Сумма займа на начало года

            Плата по процентам

            Основная плата

            Остаток долга

            7

            1

            100000




            8

            2





            9

            3





            10

            4





            11

            5





          3. Для расчета ежегодной выплаты используйте: =ПЛТ(В3;В4;В5)

          4. Расчет платы по процентам выполняется по формуле: =ПРПЛТ($B$2;A7;$B$3;$B$5), протяните это формулу и для остальных ячеек

          5. Сумма платежи по процентам за первый год вычисляется по формуле: =ОСПЛТ($B$2;A7;$B$3;$B$5), протяните это формулу и для остальных ячеек

          6. Остаток долга = предыдущий долг - основная плата, т.е вы должны набрать =B7+D7 (+ потому что основная плата отрицательное число), скопируйте формулу для остальных ячеек

          7. в В8 введите следующую формулу: =Е7

          8. В остатке долга на 5-й год у вас должно получится значение 0, т.к. происходит полное погашение долга и вы ничего не должны


          Упражнение 5. Предположим вы купили за 6000 тенге компьютер, который имеет срок эксплуатации 5 лет, после чего оценивается в 1000 тенге. Определите снижение стоимости компьютера для каждого года эксплуатации.

          Решение:Снижение стоимости компьютера для каждого года эксплуатации вычисляется по формуле АПЛ(нач_стоимость; остаточная_стоимость; время_эксплуатации).


          Упражнение 6. Предположим вы купили за 6000 тенге. компьютер, который имеет срок эксплуатации 5 лет, после чего оценивается в 1000 тенге. Определите амортизацию компьютера за первый и пятый год эксплуатации.

          Решение: Снижение стоимости компьютера за первый год эксплуатации вычисляется по формуле АСЧ(нач_стоимость; остаточная_стоимость; время_эксплуатации; период).


          Задания для самостоятельной работы:

          1. Вы собираетесь вкладывать по В тыс. тенге в течение N лет при годовой ставке П%. Сколько денег будет на счету через N лет?

            Вариант

            1

            2

            3

            4

            5

            В

            52

            53

            55

            60

            54

            П

            10

            10.5

            11

            12

            10.9

            N

            5

            6

            8

            10

            9

          2. Определите платежи по процентам по займу в Т тыс. тенге, выданному на N лет под П% годовых, за К лет, если проценты начисляются квартально.

            Вариант

            1

            2

            3

            4

            5

            Т

            125

            350

            420

            355

            425

            П

            8

            9

            11

            10

            7

            N

            5

            6

            7

            8

            5

            К

            3

            4

            5

            6

            2

          3. Определите ежемесячные выплаты по займу в Т тыс. тенге, взятому на К месяцев под П% годовых.

            Вариант

            1

            2

            3

            4

            5

            Т

            25

            30

            40

            35

            42

            П

            6

            8

            9

            10

            7

            К

            5

            6

            7

            8

            5

          4. Вычислить n-годичную ипотечную ссуду покупки квартиры за Р тенге с годовой ставкой i% и начальным взносом А%. Сделать расчет для ежемесячных и ежегодных выплат для исходных данных, представленных в следующей таблице:

            Варианты

            1

            2

            3

            4

            5

            n

            7

            8

            9

            10

            11

            Р

            170000

            200000

            220000

            300000

            350000

            i

            5

            6

            7

            8

            9

            А

            10

            10

            20

            20

            15

          5. Вычислить основные платежи, плату по процентам, общую ежегодную выплату и остаток долга на примере ссуды Р тенге под годовую ставку i % на срок n лет.

          Вариант

          1

          2

          3

          4

          5

          n

          7

          8

          9

          10

          11

          P

          170000

          200000

          220000

          300000

          350000

          i

          5

          6

          7

          8

          9

          Контрольные вопросы:

          1. Какие финансовые функции для расчета платежей вы знаете?

          2. Что означает функция ОСПЛТ?

          3. Что такое амортизация?

          4. Чем отличаются функции АСЧ от АПЛ?

          5. Что означает функция ПРПЛТ?


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

          Тема: «Сводные таблицы. Консолидация данных»


          Цель работы: сформировать умение строить сводные таблицы, использовать консолидацию данных.

          Основные понятия

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

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


          Упражнение 1. Создать сводную таблицу для отчетов по продаже компьютеров сети из трех магазинов подводящую сумму продаж по месяцам.

          1. Создать заданную таблицу в Excel на Лист1 и переименуйте на Свод_табл. Для ускорения работы использовать копирование и автозаполнения данных.

          1. Вызовите Мастер Сводных таблиц, выполнив команду ДанныеСводная таблица,

          2. В появившимся окне установите переключатель в положение в списке или базе данных Microsoft Excel, щелкните по кнопке Далее.

          3. Укажите диапазон, содержащий исходные данные (выделить всю таблицу с данными), щелкните по кнопке Далее, в следующим окне выберите переключатель в положение новый лист.

          4. Кнопка Макет (для Excel 2000); в появившемся диалоговом окне, в котором создается структура сводной таблицы, выбрать поле, по которому будут подводиться итоги сводных таблиц, и перетащить кнопку, соответствующую этому полю, в область Данные (перетащить кнопку Стоимость в область Данные). Двойной щелчок по кнопке Стоимость в области Данные приведет к отображению диалогового окна Вычисление поля сводной таблицы, которое позволяет установить операцию, на основе которой будут подводиться итоги (выбрать операцию Сумма).

          5. Выбрать поля, которые будут образовывать строки сводной таблицы, и перетащить кнопки, соответствующие этим полям, в область Строка. Перетащить кнопку Магазин в область Строка.

          6. Выбрать поля, которые будут образовывать столбцы сводной таблицы, и перетащить кнопки, соответствующие этим полям, в область Столбец. Перетащить кнопку Месяц в область Столбец.

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

          8. Для перерасчета сводной таблицы, если изменились данные, на основе которых строилась эта сводная таблица: выделить ее, команда Данные / Обновить данные.

          9. Проиллюстрируйте данные полученной сводной таблицы на графике.


          Упражнение 2. Создание консолидации данных по расположению и по категориям

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

          2. Создайте на листе Консол. таблицу расчета заработной платы (за январь и за февраль).

          1. Вычислите сумму подоходного налога и сумму к выдаче

          2. Выполните консолидацию по расположению, для этого: Установите курсор в первую ячейку области, где будет располагаться консолидированная таблица. (Например в А10)

          • Выполните команду Данные, Консолидация.

          • В диалоговом окне << Консолидация>> выберите из списка функцию Сумма и установите флажки подписи верхней строки, значения левого столбца,

          • Установите курсор в окне << Ссылка>>, перейдите на лист с исходными таблицами и выделите блок ячеек A2:D7.

          • Нажмите кнопку < Добавить>, в окне << Список диапазонов>> появится ссылка на выделенный диапазон.

          • Установите курсор в окне << Ссылка>>, перейдите на лист с исходными таблицами и выделите блок ячеек F2:I7.

          • Нажмите кнопку < Добавить>, в окне << Список диапазонов>> появится ссылка на выделенный диапазон.

          • Нажмите кнопку < ОК>.

          1. Скопируйте обе таблицы с листа Консол.распол. на лист Консол.категор. и измените вторую таблицу (за февраль): вставьте новый столбец Премия и заполнить его, добавьте строку с фамилией Дятлов.

          2. Сделайте консолидацию данных по категориям:

          • Установите курсор в первую ячейку области, где будет располагаться консолидированная таблица.

          • Выполните команду Данные, Консолидация.

          • В диалоговом окне << Консолидация>> выберите из списка функцию Сумма и установите флажки подписи верхней строки, значения левого столбца.

          • Установите курсор в окне << Ссылка>>, перейдите на лист с исходными таблицами и выделите блок ячеек A2:D7.

          • Нажмите кнопку < Добавить>, в окне << Список диапазонов>> появится ссылка на выделенный диапазон.

          • Установите курсор в окне << Ссылка>>, перейдите на лист с исходными таблицами и выделите блок ячеек F2:J8.

          • Нажмите кнопку < Добавить>, в окне << Список диапазонов>> появится ссылка на выделенный диапазон.

          • Нажмите кнопку < ОК>.


          Задания для самостоятельной работы:

          1. Создайте сводную таблицу … см.таблицу

          2. Создайте консолидации данных по расположению … см.таблицу


          №

          Вариант 1

          Вариант 2

          Вариант 3

          Вариант 4

          Вариант 5

          1

          Количество компьютеров по магазинам

          Количество компьютеров по месяцам

          Стоимость компьютеров по магазинам

          Стоимость компьютеров по месяцам

          Стоимость компьютеров по компьютерам

          2

          Среднюю заработную плату

          Максимальное значение зарплаты

          Минимальное значение зарплаты

          Среднюю заработную плату

          Максимальное значение зарплаты


          Контрольные вопросы:

          1. Для чего служит сводная таблица?

          2. Как создать сводную таблицу?

          3. Что такое консолидация данных?

          4. Как выполнить консолидацию данных?

          5. Какого типа консолидации вы знаете?


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

          Тема: «Система управления базами данных Microsoft Access. Создание базы данных из нескольких таблиц»


          Цель работы: познакомить с основными объектами базы данных, сформировать умение строить базы данных из нескольких таблиц, создавая связи между ними.

          Основные понятия:

          База данных - это совокупность данных (таблиц), хранящихся вместе и связанных друг с другом, или это специальное электронное хранилище взаимосвязанных данных.

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


          Назначение любой системы управления базами данных (СУБД) – создание, ведение и обработка баз данных.

          Microsoft Access состоит из отдельных компонентов, которые используются для хранения и представления информации. Этими компонентами являются таблицы, формы, отчёты, запросы, макросы, модули. У каждого объекта Microsoft Access есть по крайней мере 2 способа создания: с помощью Конструктора или с помощью Мастера. В режиме Конструктора создается или изменяется структура объекта, в Мастере используются готовые варианты объектов, что предоставляет пользователю выбирать.

          Данными называется любая информация, которую необходимо сохранить, чтобы иметь возможность периодически к ней обращаться. Большинство баз данных имеют табличную структуру. Как мы знаем, в табличной структуре адрес данных определяется пересечением строк и столбцов. В базах данных столбцы называются полями, а строки — записями. Поля образуют структуру базы данных, а записи составляют информацию, которая в ней содержится.

          Поля — это основные элементы структуры базы данных. Они обладают свойствами. От свойств полей зависит, какие типы данных можно вносить в поле, а какие нет, а также то, что можно делать с данными, содержащимися в поле. Например, данные, содержащиеся в поле Цена, можно просуммировать, чтобы определить итоговый результат. Суммировать данные, содержащиеся в поле Номер телефона, совершенно бессмысленно, даже если номера телефонов записаны цифрами. Очевидно, что эти поля обладают разными свойствами и относятся к разным типам.

          Разные типы полей имеют разное назначение и разные свойства.

          1. Основное свойство текстового поля — размер.

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

          3. Поля для ввода дат или времени имеют тип Дата/время.

          4. Для ввода логических данных, имеющих только два значения (Да или Нет; 1 или 0; Истина или Ложь и т. п.), служит специальный тип — Логическое поле. Длина такого поля всегда равна 1 байту, поскольку этого более чем достаточно, чтобы выразить логическое значение.

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

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

          7. У текстового поля есть недостаток, связанный с тем, что оно имеет ограниченный размер (не более 256 символов). Если нужно вставить в поле длинный текст, для этого служит поле типа MEMO. В нем можно хранить до 65 535 символов. Особенность поля MEMO состоит в том, что реально эти данные хранятся не в поле, а в другом месте, а в поле хранится только указатель на то, где расположен текст.

          8. Очень интересное поле Счетчик. На первый взгляд это обычное числовое поле, но оно имеет свойство автоматического наращивания. Если в базе есть такое поле, то при вводе новой записи в него автоматически вводится число, на единицу большее, чем значение того же поля в предыдущей записи. Это поле удобно для нумерации записей.

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

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

          Для того чтобы связи между таблицами работали надежно и по записи из одной таблицы можно было однозначно найти записи в другой таблице, надо предусмотреть в таблице уникальные поля. При создании структуры таблиц одно поле (или одну комбинацию полей) можно назначить ключевым. С ключевыми полями компьютер работает особо. Он проверяет их уникальность и быстрее выполняет сортировку по таким полям. Ключевое поле — очевидный кандидат для создания связей. Иногда ключевое поле называют первичным ключом.

          Типы связей:

          1:1: Отношение «один-к-одному». Например: (студент - № зачетки)

          1:N: Отношение «один-ко-многим». Например: Один поставщик...а)  ... может поставлять несколько товаров, ...б) ... но у каждого товара может быть только один поставщик. (Группа-студенты)

          N:M: Отношение «многие-ко-многим» одной записи в таблице A могут соответствовать несколько записей в таблице B, а одной записи в таблице B несколько записей в таблице A. Например: (Студенты-курсы, которые они слушают)

          Требования к созданию связанных таблиц:

          Целостность данных означает систему правил, используемых в СУБД Access для поддержания связей между записями в связанных таблицах. Контролировать целостность данных можно, если выполнены следующие условия:

          • Связанное поле одной таблицы является ключевым полем или имеет уникальный индекс.

          • Связанные поля имеют один тип данных. Исключение: поле Счетчик может быть связано с Числовым полем, если оно имеет тип Длинное целое.

          • Обе таблицы принадлежат одной базе данных Access.


          Упражнение 1.Создание базы данных «Деканат».

          1. Создайте новую базу данных «Деканат». Для создания новой базы данных:

          • загрузите Access, в появившемся окне с левой стороны щелкните по пункту Новая база данных;

          • в окне «Файл новой базы данных» в поле Имя файла в качестве имени базы данных задайте имя Деканат
            и выберите папку (пункт             Папка), где ваша база данных будет находиться, т.е. в свою папку.

          • щелкните по кнопке <Создать>.

          1. Создайте таблицу базы данных. Для создания таблицы базы данных:

          • В появившимся окне на левой панели окна "База данных" сосредоточены элементы управления для вызова всех типов объектов Access (таблицы, запросы, формы и т.д.). В окне выберите тип создаваемого документа. Вы создаете таблицы, поэтому выберите закладку Таблица. Щелкните по кнопке Создать.

          • Откроется окно диалога «Новая таблица», в правой части которого находится список вариантов дальнейшей работы:

          • Здесь несколько вариантов, но вы выберите Конструктор и щелкните по кнопке ОК. Появится окно Конструктора.

          1. Определите поля таблицы в соответствии с табл. 1. Для определения полей таблицы:

          • введите в строку столбца «Имя поля» имя первого поля Код преподавателя;

          • в строке столбца «Тип данных» щелкните по кнопке списка и выберите тип данных Числовое, а Размер поля; Целое во вкладке Общие

          • Установите поля Код преподавателя ключевым. Для этого щелкните на рисунок ключа на панели инструментов или вызовите контекстное меню

          • Введите ограничения на данные в поле <Код>; эти данные не должны повторяться, т.к. коды преподавателей не должны повто­ряться, а также должна быть обеспечена возможность их изменения (

          • Во вкладке Общие щелкните по строке параметра Индексированное пол ;

          Примечание. Индекс — это средство Access, ускоряющее поиск и сортировку данных в таблице. Ключевое поле (поле первичного ключа) таблицы индекси­руется автоматически. Не допускается создание индексов для полей типа MEMO и Гиперссылка или полей объектов OLE.

          • выберите в списке пункт Да (совпадения не допускаются);

          • Для определения всех остальных полей таблицы базы данных Преподаватели в соот­ветствии с табл..1 выполните действия, аналогичные указанным выше.

          Таблица 1.

          Имя поля

          Тип данных

          Размер поля

          Фамилия

          Текстовый

          20

          Имя

          Текстовый

          15

          Отчество

          Текстовый

          25

          Дата рождения

          Дата/время

          Формат поля: Краткий

          Должность

          Текстовый

          9

          Код дисциплины

          Числовой

          Целое

          Дисциплина

          Текстовый

          11

          Телефон

          Текстовый

          9

          Зарплата

          Денежный

          Формат поля: Основной,

          Число десятичных знаков: 0

          • Сохраните созданную таблицу под именем Преподаватели. Для этого нажмите на знак «Закрыть».В появившемся окне наберите имя таблицы Преподаватели и щелкните по кнопке ОК.

          Упражнение 2. Редактирование базы данных.

          1. Войдите в режим Конструктор для проектируемой таблицы. Если вы находитесь в окне базы данных, то выберите таблицу Преподаватели и щелкните по кнопке

          2. Введите ограничения на данные, вводимые в поле «Должность»; должны вводится только слова Профессор, Доцент или Ассистент.

          Для задания условия на значение для вводимых данных:

          • Щелкните в поле «Должность», в нижней части окна во вкладке Общие переидите на Условие на значение;

          • щелкните по кнопке для определения условий на значение при помощи по­ строителя выражений;

          • в появившемся окне напишите слово Профессор, затем щелкните по кнопке (эта кнопка выполняет функцию ИЛИ), напишите Доцент, снова щелкните по этой же кнопке, напишите Ассистент и щелкните по кнопке <ОК>. Таким образом вы ввели условие, при котором в поле «Должность» могут вводиться только указанные значения

          1. Задайте текст сообщения об ошибке, который будет появляться на экране при вводе неправильных данных в поле «Должность».

          В строке Сообщение об ошибке введите предложение "Такой должности нет, правиль­но введите данные".

          1. Задайте значение по умолчанию для поля «Должность» в виде слова Доцент.

          В строке Значение по умолчанию введите слово "Доцент". Выидите из режима конструктора.


          Упражнение 3. Работа в режиме таблиц.

            1. Перейдите в режим Таблица:

            • щелкнув по кнопке на панели инструментов

            • или выполнив команду Вид, Режим таблицы. На вопрос о сохранении таблицы щелкните по кнопке <Да>.

            2. Заполните таблицу данными в соответствии с табл. 2 и проверьте реакцию системы на ввод неправильных данных в поле «Должность».

            Попробуйте в поле <Должность> любой записи ввести слово Лаборант. Посмотрите, что получилось. На экране должно появиться сообщение: "Такой должности нет, правильно введите данные".Введите правильное слово

            Таблица 2

            Код

            Фамилия




            Имя

            Отчество

            Дата рожд.

            Код дис.

            Должн.

            Дисцип.

            Тел.

            Зар.

            1

            Истомин

            Ремир

            Евгеньевич

            23.10.1954


            Доцент

            Информатика

            104468

            8900

            2

            Миронов

            Павел

            Юрьевич

            25.07.1940


            Профессор

            Экономика

            122140

            12000

            3

            Гришин

            Евгений

            Сергеевич

            05.12.1967


            Доцент

            Математика

            602365

            17600

            4

            Сергеева

            Ольга

            Ивановна

            12.02.1972


            Ассистент

            Математика

            348569

            18900

            5

            Емец

            Оксана

            Ивановна

            16.02.1951


            Доцент

            Экономика

            667533

            12000

            6

            Игнатьева

            Татьяна

            Павловна

            30.05.1966


            Доцент

            Информатика

            103698

            17900

            7

            Миронов

            Алексей

            Николаевич

            30.07.1948


            Доцент

            Физика

            1667533

            18900











            1. Измените ширину каждого поля таблицы в соответствии с шириной данных:

              • щелкните в любой строке поля «Код»;

              • выполните команду Формат, Ширина столбца;

              • в появившемся окне щелкните по кнопке <По ширине данных>. Ширина поля изменится;

              • проделайте эту операцию с остальными полями.

              1. Произведите поиск в таблице преподавателя Миронова:

              • установите курсор в первую строку поля «Фамилия»;

              • выполните команду Правка, Найти;

              • в появившейся строке параметра Образец введите Миронов;

              • щелкните по кнопке <Найти>. Курсор перейдет на вторую запись и выделит сло­во Миронов;

              • щелкните по кнопке <Найти далее>. Курсор перейдет на седьмую запись и так­
                же выделит слово                 Миронов;

              • щелкните по кнопке <3акрыть> для выхода из режима поиска.

              1. Произведите замену данных: измените заработную плату ассистенту Сергеевой с 18900 на 20000:

              • переведите курсор в первую строку поля «Зарплата»;

              • выполните команду Правка, Заменить;

              • в появившемся окне в строке Образец введите 18900

              • в строке Заменить на введите 20000. Обратите внимание на остальные опции —
                вам надо вести поиск по всем записям данного поля;

              • щелкните по кнопке <3аменить все>. Данные будут изменены;

              1. Произведите сортировку данных в поле «Зарплата» по возрастанию.

              • щелкните по любой записи поля «Зарплата»;

              • щелкните по кнопке на панели управления или выполните команду Записи,
                Сортировка, Сортировка по                 возрастанию. Все данные в таблице будут отсорти­рованы в соответствии с возрастанием значений в поле «Зарплата».

              1. Научитесь использовать фильтр:

              а) Произведите фильтрацию данных по полям «Должность» и «Дисциплина»:

              • щелкните по записи Доцент поля «Должность»;

              • щелкните по кнопке или выполните команду Записи, Фильтр, Фильтр по выделенному. В таблице останутся только записи о преподавателях — доцен­тах;

              • щелкните по записи Информатика поля «Дисциплина»;

              • щелкните по кнопке или выполните команду Записи, Фильтр, Фильтр по выделенному. В таблице останутся только записи о преподавателях — доцентах кафедры информатики;

              • для отмены фильтрации щелкните по кнопке на панели инструментов или
                выполните команду                 Записи, Удалить фильтр. В таблице появятся все данные.

              б) Получить записи преподавателей, у которых имена начинаются на букву «О»:

              • Установите курсор в первой записи поля Имя.

              • Выполните команду Записи, Фильтр, Изменить фильтр

              • Установите курсор в первой записи поля Имя. Введите запрос Like «О*» и выполните команду Записи, Применить фильтр. Посмотрите что получилось

              • Запрос Not «О*» будет означать все записи, кроме указанных.

              8. Сохраните таблицу.

              Упражнение 4. Создайте три таблицы: Студенты, Оценки и Дисциплины.

              1. Создайте таблицу Студенты

              Имя поля

              Тип данных

              Размер поля


              Код студента

              Числовой

              Целое


              Фамилия

              Текстовый

              15


              Имя

              Текстовый

              12


              Отчество

              Текстовый

              15


              Номер группы

              Текстовый

              10


              Телефон

              Текстовый



              Стипендия

              Логический

              Да/Нет



              ! в качестве ключевого поля задайте «Код студента». Для этого щелкните по полю «Код студента» и по кнопке на панели инструментов или выполни­те команду Правка, Ключевое поле; закройте таблицу, задав ей имя Студенты.

              1. Создайте таблицу Дисциплины:

              Имя поля


              Тип данных

              Размер поля

              Код дисциплины


              Числовой

              Целое

              Название дисциплины


              Текстовый

              30

              ! В качестве ключевого поля задайте «Код дисциплины». Заполняться эта таблица будет также в режиме формы.

              1. Создайте структуру таблицы Оценки:


              Имя поля

              Тип данных

              Размер поля

              Код студента

              Числовой

              Целое

              Код дисциплины

              Числовой

              Целое

              Оценки

              Числовой

              Байт

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

              1. Разработайте схему данных, т.е. создайте связи между таблицами: Преподаватели, Студенты, Оценки, Дисциплины:

              • щелкните по кнопке на панели инструментов или выполните команду Сер­вис, Схема данных. На экране появится окно «Схема данных»;

              • щелкните по кнопке на панели инструментов или выполните команду Свя­зи, Добавить таблицу;

              • в появившемся окне будет выделено название одной таблицы. Щелкните по кнопке <Добавить>;

              • переведите выделение на имя следующей таблицы и щелкните по кнопке <Добавить>. Аналогично добавьте оставшиеся две таблицы;

              • закройте окно, щелкнув по кнопке <3акрыть>;

              • создайте связь между таблицами Дисциплины и Оценки. Для этого подведите курсор мыши к полю «Код дисциплины» в таблице Дисциплины, щелкните левой кнопкой мыши и, не отпуская ее, перетащите курсор на поле «Код дис­циплины» в таблицу Оценки, а затем отпустите кнопку мыши. На экране откро­ется окно «Связи»;

              • установите флажок ("галочку") в свойстве Обеспечение целостности данных, щелкнув по нему. Это невозможно будет сделать, если типы обоих полей заданы неодинаково

              • установите флажок в свойстве Каскадное обновление связанных полей и Каскад­ное удаление связанных записей;

              Примечание. Задание каскадного обновления связанных полей и каскадного удаления связанных записей позволит вам отредактировать записи только в таблице Дисциплины, а в таблице Оценки эти действия будут со связанными за­писями выполняться автоматически. Например, если вы удалите из таблицы Дисциплины один предмет, то в таблице Оценки удалятся все строки, связан­ные с этим предметом.

              • щелкните по кнопке <Создать>. Связь будет создана;

              • аналогично создайте связи между полем «Код дисциплины» в таблице Дисци­плины и полем «Код дисциплины» в таблице Преподаватели, а также между полем «Код студента» в таблице Студенты и полем «Код студента» в таб­лице Оценки. Результат представлен на рис. 4.6;

              • закройте окно схемы данных, ответив ДА на вопрос о сохранении макета.


              Задания для самостоятельной работы:


              1. Создайте базу данных … см.таблицу.

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

              № варианта

              Задание

              1

              «Музыкальный альбом», состоящую из трех таблиц «Композитор», «Певцы», «Песни».

              2

              «Поликлиника», состоящую из трех таблиц «Врач», «Пациент», «Участки».

              3

              «Расписание занятий», состоящую из трех таблиц «Класс», «Учителя», «Предметы».

              4

              «Компьютерный салон», состоящую из двух таблиц «Ассортимент товаров», «Покупатель».

              5

              «Участники конференции», состоящую из двух таблиц «Список участников», «Темы докладов».

              Контрольные вопросы:


              1. Что такое база данных, СУБД?

              2. Какие основные объекты базы данных Microsoft Access вы знаете?

              3. Какие способы создания объектов вы знаете?

              4. Какие типы данных, типы связей вы знаете?

              5. Какие существуют требования к созданию связей между таблицами?


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

              Тема: «Система управления базами данных Microsoft Access.

              Создание форм»


              Цель работы: сформировать умение создавать различного вида форм.

              Основные понятия:

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

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

              Создать форму можно несколькими способами:

                • Конструктор позволит вам создать форму самостоятельно.

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

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

                • Диаграмма создает форму со встроенной диаграммой,

                • а сводная таблица - со сводной таблицей Excel.

              Существуют 2 вида заполнение данных: В режиме таблицы или в режиме форм.


              Упражнение 1. Создайте форму Дисциплины с помощью Мастера форм.

              1. Для создания формы Дисциплины:

              • В окне базы данных выберите вкладку Формы и щелкните на кнопке Создать.

              • В диалоговом окне Новая форма выберите Мастер форм.

              • Щ елкните по значку списка в нижней части окна и выберите из появившегося списка таблицу Дисциплины

              • Щелкните по кнопке ОК.

              • в появившемся окне выберите поля, которые будут присутствовать в форме. В
                данном примере присутствовать будут все поля, поэтому щелкните по кнопке .

              • Щелкните по кнопке Далее.

              • Выберите внешний вид формы – табличный. Щелкните по кнопке Далее. Выберите требуемый стиль и Далее.

              • Задайте имя формы: Дисциплины. Щелкните по кнопке Готово.

              1. Заполните данными, приведенными ниже.



              Код дисциплины

              Название дисциплины

              1

              Информатика

              2

              Математика

              3

              Физика

              4

              Экономика

              • закройте форму, задав ей имя Дисциплины.

              3. Заполните таблицу Преподаватели в режиме таблиц:

              • В окне базы данных во вкладке Таблицы выберите таблицу Преподаватели, т.е. 2 раза щелкните по таблице Преподаватели.

              • В столбце Код дисциплины заполните данными в соответствии с приведенной выше таблицей.

              Упражнение 2. Заполните таблицу Студенты в режиме форм с помощью автоформ.

              1. Для создания формы Студенты выполните следующие действия:

              • Щелкните по вкладке Формы в окне базы данных;

              • щелкните по кнопке <Создать>;

              • в появившимся окне в низу выберите таблицу Студенты;

              • выберите пункт Автоформа: ленточная;

              • щелкните по кнопке <ОК>. Форма для ввода данных создана.

              2. Заполните таблицу Студенты данными, приведенными ниже, посредством формы.

              Код

              студента

              Фамилия

              Имя

              Отчество

              Номер группы

              Телефон

              Стипендия

              1

              Арбузов

              Николай

              Николаевич

              Ф-15

              260-15-63

              Да

              2

              Киршин

              Петр

              Валерьевич

              Ф-15

              110-67-82

              Да

              3

              Кривинский

              Сергей

              Николаевич

              Ф-17

              172-97-21

              Нет

              4

              Крылова

              Елена

              Петровна

              Ф-18

              130-31-87

              Да

              5

              Кульчий

              Григорий

              Викторович

              Мн-12

              269-53-75

              Да

              6

              Патрикеев

              Олег

              Борисович

              Ф-17

              234-11-63

              Нет

              3. Закройте форму, задав ей имя Студенты.

              Упражнение 3. Создайте форму Оценки с помощью Конструктора.

                1. Для создания формы Оценки;

              • В диалоговом окне данных выберите вкладку Формы. Щелкните по кнопке Создать.

              • В диалоговом окне Новая форма выберите режим Конструктор. В открывшемся окне выберите таблицу Оценки, содержащую данные, на основе которого будет создана форма и ОК.

              • Окно форма будет выведено на экран в режиме Конструктор.

              • Щелкните по кнопке Список полей на панели инструментов. Выделите все поля, держа нажатой левой рукой клавишу Ctrl. Перетащите мышкой поля в область данных.

              • Закройте окно списка полей. Расположите элементы удобно по полю.

              • Задайте размер текста поля Код студента равным 18. Чтобы увеличить размер элемента соответственно надписи, выполните команду Формат – размер - по размеру данных. Сохраните форму с именем Оценки.

              • Закройте форму.

                1. В окне базы данных появится форма Оценки. Щелкните 2 раза по нему. Заполните данными таблицу Оценки посредством формы



              Код студента

              Код дисциплины

              Оценки

              1

              1

              4

              1

              2

              5

              1

              3

              4

              1

              4

              4

              2

              1

              5

              2

              2

              5

              2

              3

              4

              2

              4

              4

              3

              1

              3

              3

              2

              5

              3

              3

              4

              3

              4

              3

              4

              1

              4

              4

              2

              4

              4

              3

              5

              4

              4

              4

              5

              1

              5

              5

              2

              5

              5

              3

              5

              5

              4

              5

              6

              1

              5

              6

              2

              4

              6

              3

              5

              6

              4

              4

              Упражнение 4. Самостоятельно создайте форму в виде заставки с помощью панели элементов в режиме конструктор, в которой будут располагаться кнопки открытия существующих форм.



              Задания для самостоятельной работы:


              1. Заполните созданную базу данных … (см.таблиц) с помощью форм, используя различные способы создания форм.

              № варианта

              Задание

              1

              «Музыкальный альбом»

              2

              «Поликлиника»

              3

              «Расписание занятий»

              4

              «Компьютерный салон»

              5

              «Участники конференции»


              Контрольные вопросы:

              1. Что такое форма?

              2. Какие способы создания форм вы знаете?

              3. Что представляет с собой автоформа?

              4. Как создать кнопочную форму?

              5. Какие способы заполнения таблиц вы знаете?



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

              Тема: «Система управления базами данных Microsoft Access. Создание запросов»


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

              Основные понятия:

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

              Для построения условий отбора используются логические операции: or (или), and (и), not (не), операции сравнения <, >, =, <>, <=, >=, а так же Like, которая встречалась при использовании фильтра.

              Существуют несколько способов создания запросов:

              • Конструктор позволит вам самостоятельно создать любой тип запроса.

              • Простой запрос позволит создать с помощью Мастера запрос на выборку из определенных полей таблиц или других запросов.

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

              - Повторяющиеся записи будет создан запрос на поиск повторяющихся записей в простой таблице или в запросе, а при выборе пункта Записи без подчиненных - запрос на поиск записей, которым не соответствует ни одна запись в подчиненной таблице. Такой запрос используется для многотабличных баз данных.

              Тип запроса

              Описание

              Выборка

              Служит для выборки данных из разных таблиц, когда требуется объединить разные данные из одной или нескольких таблиц

              Обновление

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

              Добавление

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

              Удаление

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

              Перекрестный

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

              Создание таблицы

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

              Вычисления в запросах

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

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

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

              Результативность : [Забито] / [Игры]


              Упражнение 1. Создайте простой запрос на выборку на основе таблицы Преподаватели, в котором должны отображаться Фамилии, Имена, Отчества преподавателей и их Должность.

              1. Для создания простого запроса:

              • в окне базы данных откройте вкладку Запросы;

              • в открывшемся окне щелкните по кнопке <Создать>;

              • из появившихся пунктов окна «Новый запрос» выберите Простой запрос и щелкните по кнопке <ОК>;

              • в появившемся окне в строке Таблицы/запросы выберите таблицу Преподавате­ли (если других таблиц или запросов не было создано, она будет одна в откры­вающемся списке);

              • в окне «Доступные поля» переведите выделение на параметр Фамилия;

              • щелкните по кнопке . Слово Фамилия перейдет в окно «Выбранные поля»;

              • аналогично в окно «Выбранные поля» переведите поля «Имя», «Отчест­во», «Должность» (порядок важен — в таком порядке данные и будут выво­диться);

              • щелкните по кнопке <Далее>;

              • в строке параметра Задайте имя запроса введите новое имя Должности препо­давателей;

              • щелкните по кнопке <Готово>. На экране появится таблица с результатами за­проса.

              2. Сохраните запрос. Для сохранения запроса:

              • щелкните по кнопке или выполните команду Файл, Сохранить;

              • закройте окно запроса.

              Упражнение 2. Создайте запрос на выборку с параметром, в котором должны отображаться фамилии, имена, отчества преподавателей и преподаваемые ими дисциплины, а в качестве пара­метра задайте фамилию преподавателя и выполните этот запрос для преподавателя Гришина.

              1. Для создания запроса на выборку с параметром:

              • создайте запрос на выборку для следующих полей таблицы Преподаватели:
                «Фамилия», «Имя», «Отчество», «Преподаваемая дисциплина». Запрос создавайте аналогично тому, как это делалось в п.1;

              • задайте имя запросу Преподаваемые дисциплины;

              • щелкните по кнопке <Готово>. На экране появится таблица с результатами за­проса;

              • перейдите в режиме конструктора, щелкнув по кнопке или выполнив ко­манду Вид, Конструктор;

              • в строке параметра Условия отбора для поля «Фамилия» введите фразу
                (скобки тоже вводить):                 [Введите фамилию преподавателя];

              • выполните запрос, щелкнув по кнопке на панели инструментов или выполнив команду Запрос, Запуск;

              Примечание. Вышеописанным способом запрос выполняется только в режиме конструктора. Для того чтобы выполнить запрос из другого режима, надо от­крыть вкладку Запросы, выделить требуемый запрос и щелкнуть по кнопке <Открыть>


              • в появившемся окне введите фамилию Гришин и щелкните по кнопке «ОК». На экране появится таблица с данными о преподавателе Гришине — его имя, отче­ство и преподаваемая им дисциплина;

              1. сохраните запрос;

              2. закройте окно запроса.

              Упражнение 3. Создайте запрос с помощью Конструктора, в котором должны отображаться фамилии, имена, отчества, номер группы, дисциплина и оценки по дисциплинам:

                • Из списка создания запроса выберите Конструктор

                • Добавьте нужные таблицы (Студенты, Оценки, Дисциплины), выбирая их и щелкая по кнопке Добавить.

                • Завершите выбор, щелкнув по кнопке Закрыть.

                • Выберите поля Фамилия, Имя, Отчество, Номер группы из таблицы Студент, Оценки из таблицы Оценки, Название дисциплина из таблицы Дисциплины. Для этого достаточно сделать двойной щелчок мышкой по имени поля. Второй вариант - перетащить мышкой название поля в клетки запроса.

                • В строке Условие отбора под полями Оценки поставьте 4 Or 5.

                • Щелкните по кнопке ! на панели инструментов для представления запроса.

                • Сохраните запрос с именем Успеваемость 1, щелкнув по кнопке сохранить.

                Упражнение 4. Самостоятельно создайте запрос с вычисляемым полем

                Создайте вычисляемое поле «Надбавка», получаемое путем увеличения поля «Зарплата» на 20 %.

                Упражнение 5. Самостоятельно изучите создание перекрестного запроса. (см. Access-Справка)



                Задания для самостоятельной работы:

                1. Задайте к базе данных три запроса различного типа …

                № варианта

                Задание

                1

                «Музыкальный альбом»

                2

                «Поликлиника»

                3

                «Расписание занятий»

                4

                «Компьютерный салон»

                5

                «Участники конференции»


                Контрольные вопросы:


                1. Что такое запрос?

                2. Какие способы создания запросов?

                3. Какие типы запросов вы знаете?

                4. Как создаются вычисляемые поля и перекрестные запросы?

                5. Какие операции используются при создании запроса с условием?


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

                Тема: « Система управления базами данных Microsoft Access . Создание отчетов»


                Цель работы: сформировать умение создавать отчеты.

                Основные понятия:

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

                Существует шесть способов создания отчета:

                • Конструктор позволит вам самостоятельно создать отчет, но это непросто даже для опытного пользователя.

                • Мастер отчетов автоматически создаст отчет на основе выбранных вами полей таблиц (запросов) и макетов отчетов. Этот способ создания отчетов является наиболее удобным.

                • Автоотчет в столбец и Автоотчет ленточный - самые простейшие способы создания отчетов: достаточно указать только имя таблицы (запроса), на основе которого будет создан отчет, а остальное сделает мастер отчетов.

                • Мастер диаграмм поможет создать отчет в виде диаграммы,

                • Почтовые наклейки создадут отчет, отформатированный для печати почтовых наклеек.


                Упражнение 1. На основе таблицы Преподаватели создайте отчет с группированием данных по должностям.

                1. Для создания отчета:

                  • откройте вкладку Отчеты и щелкните по кнопке <Создать>;

                  • в открывшемся окне выберите пункт Мастер отчетов;

                  • щелкните по значку раскрывающегося списка в нижней части окна;

                  • выберите из появившегося списка таблицу Преподаватели;

                  • щелкните по кнопке <ОК>. В появившемся окне выберите поля, которые будут присутствовать в форме. В данном примере присутствовать будут все поля из таблицы, поэтому щелкните по кнопке ;

                  • щелкните по кнопке <Далее>;

                  • к в появившемся окне присутствует перечень полей. Переведите выделение на по­ле «Должность»;

                  • щелкните по кнопке . Таким образом вы задаете группировку данных по должности;

                  • щелкните по кнопке <Далее>;

                  • параметры появившегося окна оставим без изменений, поэтому щелкните по кнопке <Далее>;

                  • в появившемся окне выберите стиль оформления отчета;

                  • щелкните по кнопке <Далее>;

                  • в появившемся окне введите название отчета Преподаватели;

                  • щелкните по кнопке <Готово>. На экране появится сформированный отчет;

                  • просмотрите, а затем закройте отчет.

                Упражнение 2. Самостоятельно создайте отчет на основе таблицы Студенты в виде Справки о том, что студент обучается в данной группе экономического факультета с помощью панели элементов режиме Конструктора .


                Задания для самостоятельной работы:

                Создайте к базе данных три отчета разными способами.

                № варианта

                Задание

                1

                «Музыкальный альбом»

                2

                «Поликлиника»

                3

                «Расписание занятий»

                4

                «Компьютерный салон»

                5

                «Участники конференции»


                Контрольные вопросы:


                1. Что такое отчет?

                2. Какими способами создаются отчеты?

                3. Что значит автоотчет?

                4. Для чего служит панель элементов?

                5. Чем отличается автоотчет от Мастера отчетов?




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

                Тема: «Язык создания гипертекстовых документов – HTML. Теги форматирования текста. Вставка рисунков, создание гиперссылки. Списки. Таблицы»

                Основные понятия:

                HyperText Markup Language (HTML) является стандартным языком, предназначенным для создания гипертекстовых документов в среде WEB. HTML-документ представляет собой обычный текстовый файл, который вы можете просматривать и редактировать в любом неформатирующем текстовом редакторе. HTML-документы могут просматриваться различными типами WEB-броузеров, которые интерпретируют (выполняют) HTML документ. Использование HTML позволяет форматировать документы для их представления с использованием шрифтов, линий и других графических элементов на любой системе, их просматривающей. Большинство документов имеют стандартные элементы, такие, как заголовок, параграфы или списки.

                HTML-тэги могут быть условно разделены на две категории:

                • тэги, определяющие, как будет отображаться WEB-броузером тело документа в целом

                • тэги, описывающие общие свойства документа, такие как заголовок или автор документа

                Все тэги HTML начинаются с символа "<" и заканчиваются ">" . Как правило, существует стартовый тэг и завершающий тэг. Для примера приведем тэги заголовка, определяющие текст, находящийся внутри стартового и завершающего тэга и описывающий заголовок документа:

                <TITLE> Заголовок документа </TITLE>

                Завершающий тэг выглядит также, как стартовый, и отличается от него прямым слэшем перед текстом внутри угловых скобок. Некоторые тэги, такие, как <P> (тэг, определяющий абзац), не требуют завершающего тэга, но его использование придает исходному тексту документа улучшенную читаемость и структурируемость.

                Создание таблиц:

                Для представления таблиц используются нескольких видов тегов HTML:

                • теги TABLE, которые обрамляют всю табличную спецификацию

                • необязательный элемент CAPTION, специфицирующий заголовок (имя) таблицы

                • теги TR, специфицирующие строки таблицы

                • теги TH, специфицирующие заголовки строк и столбцов

                • теги TD, специфицирующие данные в таблице, т.е. содержимое табличных ячеек

                Основные табличные структуры

                Давайте начнем с очень простого примера. Он связан с представлением таблицы чисел 2 на 2 (единичная матрицa) без каких либо заголовков. Код HTML выглядит следующим образом:

                1

                0

                0

                1

                <TABLE>

                <TR> <TD> 1 </TD> <TD> 0 </TD> </TR>

                <TR> <TD> 0 </TD> <TD> 1 </TD> </TR>

                </TABLE>

                и обычно представляется броузером следующим образом:

                Дополнительные свойства; типичная таблица с текстовыми ячейками

                Есть несколько элементов, которые Вы можете добавить к вышерассмотренной простой табличной модели:

                • Заголовок (caption) для таблицы, связанный с самой таблицей (в дополнение к строкам текста о таблице в документе).

                • Подзаголовки (headers) (объяснения) как для табличных строк, так и для колонок.

                • Рамки (borders) вокруг таблицы и каждой табличной ячейки.

                Заметим, что атрибуты ALIGN и VALIGN могут существенно влиять на качество отображения, устанавливают расположение таблицы по отношению к полям документа. Допустимые значения: ALIGN=LEFT (выравнивание влево), ALIGN=CENTER (выравнивание по центру), ALIGN=RIGHT (выравнивание вправо). WIDTH - ширина таблицы. Ее можно задать в пикселах (например, WIDTH=400) или в процентах от ширины страницы (например, WIDTH=80%).


                Упражнение 1. Создать следующий HTML- документ, используя тэги форматирования текста

                1. Откройте окно текстового редактора Блокнот. Сохраните данный документ как Задание1.html

                2. Самый первый тэг, который встречается в документе, должен быть тэгом <HTML>. Данный тэг сообщает WEB-броузеру, что ваш документ написан с использованием HTML. Поэтому в первой строке нашего документа набираем <HTML>.


                1. Чтобы ввести заголовок окна, далее набираем:

                <HEAD>
                <TITLE> Пример работы

                с тэгами. Форматирование

                </TITLE>
                </HEAD>

                1. Теперь вводим Тело нашего документа, т.е. то, что будет отражаться в нашем HTML-документе. Для этого в следующей строке наберите

                <BODY BGCOLOR=yellow TEXT=blue >

                где BGCOLOR=yellow определяет, что фон документа – желтый, а TEXT=blue, что текст будет синего цвета.

                1. Далее набираем

                <H1>Это заголовок первого уровня</H1>

                <H2>Это заголовок второго уровня</H2>

                т.е. определяет заголовки разных уровней.

                1. Теперь научимся определять пронумерованные и непронумерованные списки списки. Для этого вводим

                <OL >Это пример пронумерованного списка

                <LI>Первый элемент

                <LI>Второй элемент

                <LI>Третий элемент

                </OL>

                <UL TYPE=DISC>Это пример непронумерованного списка

                <LI>Первый элемент

                <LI>Второй элемент

                <LI>Третий элемент

                </UL>

                1. Далее будем форматировать текст, т.е. использовать различные форматы ввода текста. Для этого

                <I>Это курсив</I>

                <B>Это жирный шрифт</B>

                <U>Это подчеркнутый текст</U>

                <SUP>Это верхний индекс</SUP>

                <SUB>Это нижний индекс</SUB>

                <BR>Это начало нового абзаца.

                <BIG>Это большой шрифт</BIG>

                <SMALL> Это маленький шрифт</SMALL>

                <STRIKE>Это перечеркнутый шрифт</STRIKE>

                1. В следующей строке строке введите <BR><BR>. Это будет означать, что мы дважды перевели строку (нажали клавишу «Enter»).

                2. Наберите в следующей строке <MARQUEE >ЭТО БЕГУЩАЯ СТРОКА</MARQUEE>. Таким образом создается бегущая строка.

                3. На этом информация, отражающаяся в нашем HTML-документе, закончилась и можно закрыть тэг <BODY>, набрав </BODY>.

                4. Теперь запись нашего HTML-документе закончилась, поэтому можно закрыть тэг <HTML>, набрав</HTML>.

                5. Закройте блокнот и откройте ваш документ двойным щелчком.


                Упражнение 2: Поменяйте текст сайта из задания 1: создайте домашнюю страницу с небольшой информацией о себе, используя, все приведенные теги.

                Упражнение 3: Теперь отредактируем наш документ.

                Поменяем фон, поставив фоновый рисунок.

                  1. Скопируйте в свою папку из папки Мои рисунки рисунок формата jpg и переименуйте его на image.jpg

                  2. Вернитесь в окно своего HTML-документа и выполните команду Вид-Просмотр HTML-кода

                  3. В открывшемся окне редактора Блокнот редактируем наш HTML-код. В тэге <BODY> вместо тега цвета фона добавьте BACKGROUND="image.jpg" .

                  4. Закройте окно Блокнота, сохранив документ.

                  5. В окне своего HTML-документа выполните команду Вид-Обновить.


                Упражнение 4. Создать следующий HTML- документ, используя тэги <IMG> и <A HREF>



                1. Откройте окно текстового редактора Блокнот. Сохраните данный документ как Задание4.html

                2. В первой строке нашего документа набираем <HTML>.

                3. Чтобы ввести заголовок окна, далее набираем:

                <HEAD>

                <TITLE> Вставка рисунка и гиперссылки

                </TITLE>

                </HEAD>

                1. Наберите <BODY BGCOLOR=pink TEXT=black LINK=blue VLINK=red>.

                2. Теперь вставим рисунок. Для этого в следующей строке набираем <IMG SRC="image.jpg" ALT="Здесь должен быть рисунок" HEIGHT=250 WIDTH=250 ALIGN=middle ISMAP>

                3. Между рисунком и дальнейшим текстом пропустим три строки. Для этого наберите <BR><BR><BR>

                4. Теперь вставим гиперссылку на документ Задание1.html Для этого наберите <A HREF="Задание1.html"> Перейти по гиперссылке </A>

                5. Закрыть тэг </BODY>.

                6. Закрыть тэг </HTML>.


                Упражнение 5: Создать следующий HTML- документ, который будет содержать таблицы. Наберите текст программы, сохраните документ как Задание 5.html. Изучите все теги, связанные с созданием таблицы.


                1. <HTML>

                <HEAD>

                <TITLE>Пример 2</TITLE>

                </HEAD>

                <H1>Простейшая таблица </H1>

                <TABLE BORDER=1>

                <CAPTION> <!--Это заголовок таблицы-->

                У таблицы может быть заголовок

                </CAPTION>

                <TR>

                <TD> Первая строка, первая колонка

                </TD>

                <TD> Первая строка, вторая колонка

                </TD>

                </TR>

                <TR>

                <TD> Вторая строка, первая колонка

                </TD>

                <TD> Вторая строка, вторая колонка

                </TD>

                </TR>

                </TABLE>

                </BODY>

                </HTML>

                Упражнение 6: Создайте в своей папке HTML- документ, содержащий таблицу с объединенными и разделенными ячейками. Наберите текст программы, сохраните как Задание 6.html. Изучите теги Colspan, Rowspan.

                <HTML>

                <HEAD>

                <TITLE>Пример объединения ячеек с помощью COLSPAN и ROWSPAN</TITLE>

                </HEAD>

                <BODY BGCOLOR = BLUE TEXT = BLACK >

                <TABLE ALIGN= “CENTER” BORDER= “2” LEFTMARGIN= “40” RIGHTMARGIN= “40” CELLPACING= “0” CELLPADDING= “40” WIDTH= “100%” HEIGHT= “200”>

                <TR ALIGN= “CENTER” BGCOLOR=GRAY>

                <TH COLSPAN= “2”> Ячейка1 </TH>

                </TR>

                <TR>

                <TD ALIGN= “CENTER” ROWSPAN= “3”> Ячейка 2 </TD>

                <TD ALIGN= “CENTER” > Ячейка 3 </TD>

                </TR>

                <TR>

                <TD ALIGN= “CENTER” > Ячейка 4 </TD>

                </TR>

                <TR>

                <TD ALIGN= “CENTER” > Ячейка 5 </TD>

                </TR>

                </TABLE>

                </BODY>

                </HTML>



                Задания для самостоятельной работы:

                № варианта

                Задание

                1

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

                2

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

                3

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

                4

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

                5

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


                Примечание: Документ должен содержать все представленные выше теги создания объектов.


                Контрольные вопросы:

                1. Что такое HTML?

                2. Как создается HTML документ?

                3. Какова структура HTML документа?

                4. Для чего служит браузер? Какие браузеры вы знаете?

                5. Какие теги используются для создания таблиц?


                СПИСОК ЛИТЕРАТУРЫ


                1. Гарнаев А.Ю. Использование MS Excel, VBA в экономике и финансах. – СПб.: БХВ – Санкт-Петербург, 2004.

                2. Макарова Н.В. Информатика. – М.: Финансы и статистика, 2004.

                3. Хомоненко А., Гридин В. Microsoft Access. Быстрый старт. – Санкт-Петербург: БХВ – Петербург, 2002.

                4. Информатика. Базовый курс/ С. В. Симонович и др. – СПб: Издательство «Питер», 2000.-640 с.:ил.

                5. Леонтьев В. Новейшая энциклопедия персонального компьютера. – М.: ОЛМА – ПРЕСС, 1999.-640 с.

                6. С. К. Каратыгин, А. Ф. Тихонов и др. Электронный офис: В 2-х томах: Т. 1. – М.: «Нолидж», 1999.-768 с. ил.

                7. Буза М. К. и др. Операционная среда Windows и ее приложения. – Мн.: Выш. Шк., 1997. – 336 с

                8. Б.Курицкий. Поиск оптимальных решений средствами Excel 7.0. –СПб., 1997.

                9. Ф. Новиков, А. Яценко. Microsoft Office 97 в целом. –М.,1998.

                10. В. Долженков, Ю. Колесников, В. Рычков. Практическая работа с MS Excel 97, М 1998

                11. В. Пасько. Access 2000 русифицированная версия. Киев, 2001

                12. В. Пасько. Word 7.0 русифицированный для Windows 95. Киев 1996

                13. К. Айвенс. Эксплуатация Windows NT. Проблемы и решения. М 1996

                14. П. Нортон. Разработка приложений в Access 97. –Киев, 1998.

                15. Н. Николь, Р. Альбрехт. Excel 5.0 для профессионалов. –М.:ЭКОМ. 1995

                16. Симонович С. В., Евсеев Г. А., Алексеев А. Г. Специальная информатика: Учебное пособие. – М.: АСТ _ ПРЕСС: Информ – Пресс, 1998.- 480 с.

                17. Фигурнов В. Э. IBM PC для пользователя. Краткий курс. – М.: ИНФРА _ М,1997. – 480 с. ил.

                СОДЕРЖАНИЕ

                ТЕМА: «Операционная система Windows. Основные объекты и приемы управления Windows. Программа Проводник» 3

                задание 5

                № 1 5

                задание 5

                №3 5

                ТЕМА: «Обслуживание дисков. Архивация файлов. Антивирусные программы» 7

                Тема: «Текстовый процессор Word. Создание, редактирование и форматирование документов» 9

                1 12

                ТЕМА: «Текстовый процессор Word. Вставка рисунков, создание графических объектов» 14

                Тема: «Текстовый процессор Word. Редактор формул. Создание и форматирование таблиц. Вычисления в таблице». 18

                Тема: «Текстовый процессор Word. Создание макросов» 23

                Тема: «Табличный процессор MS Excel. Ввод и редактирование данных. Адресация ячеек» 26

                Тема: «Вычисление значений функции. Мастер функции. Построение графиков и диаграмм» 31

                Деление 33

                Возведение в степень 33

                Упражнение 2: Построение диаграмм 34

                ТЕМА: «Логические функции. Построение графиков функции с двумя и тремя условиями» 35

                Тема:«Построение поверхности. Решение уравнений методом подбора параметров» 39

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

                Тема:«Сортировка и фильтрация данных» 42

                Расширенный фильтр 42

                Тема: «Матрицы и матричные формулы» 45

                Тема: «Табличный процессор Excel. Массивы. Вычисление сложных выражений. Метод Крамера» 48

                Тема: «Поиск решения и решение оптимизационных задач. Линейная оптимизационная задача. Планирования производства красок» 52

                Тема: «Функции Microsoft Excel для расчета операций по кредитам и займам» 55

                Вариант 58

                Вариант 58

                Вариант 59

                Тема: «Расчет периодических платежей. Вычисление амортизации» 59

                Вариант 62

                Вариант 62

                Вариант 62

                Тема: «Сводные таблицы. Консолидация данных» 63

                Тема: «Система управления базами данных Microsoft Access. Создание базы данных из нескольких таблиц» 66

                Тема: «Система управления базами данных Microsoft Access. 74

                Создание форм» 74

                Тема: «Система управления базами данных Microsoft Access. Создание запросов» 78

                Тема: « Система управления базами данных Microsoft Access . Создание отчетов» 81

                Тема: «Язык создания гипертекстовых документов – HTML. Теги форматирования текста. Вставка рисунков, создание гиперссылки. Списки. Таблицы» 83

                Упражнение 5: Создать следующий HTML- документ, который будет содержать таблицы. Наберите текст программы, сохраните документ как Задание 5.html. Изучите все теги, связанные с созданием таблицы. 87



                13


                Смотрите также файлы