ГБОУ СПО МО «Училище (техникум) олимпийского резерва по футболу

«Мастер-Сатурн»

ПРАКТИКУМ

по дисциплине

«Информатика и ИКТ»

Егорьевск,

2013

Рекомендовано

предметно-цикловой комиссией математических и общих естественно-научных дисциплин

для использования в учебном процессе

Практикум содержит задания, позволяющие студентам получить следующие практические навыки: работать в операционной системе WINDOWS; форматировать и редактировать документы с помощью текстового редактора WORD; проектировать электронные таблицы с помощью табличного редактора EXCEL; составлять программы с использованием языка VBA для создания пользовательских функций, макрокоманд, форм для проведения расчетов и ввода данных, построения графиков, проектирования графического интерфейса; создавать базы данных, состоящие из связанных таблиц, проектировать запросы, формы, отчеты, макрокоманды с помощью программы ACCESS.

Практикум предназначен для студентов ГБОУ СПО МО «Училище (техникум) олимпийского резерва по футболу «Мастер-Сатурн».

СОДЕРЖАНИЕ

ВВЕДЕНИЕ

Практикум соответствует требованиям к уровню освоения содержания дисциплины и структурирован с учетом содержания учебной программы по дисциплине «Информатика и ИКТ».

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

• работать в операционной системе WINDOWS: настраивать программу, формировать интерфейс, производить файловые операции, работать со стандартными программами;

• форматировать и редактировать документы с помощью текстового редактора WORD;

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

• проектировать электронные таблицы с помощью табличного редактора EXCEL;

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

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

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

1. ОПЕРАЦИОННАЯ СИСТЕМА WINDOWS

Настройка, файловые операции, стандартные программы

1. Сервис / Свойства папки, вкладки Общие, Вид.

Вид / Таблица, Упорядочить значки.

2. Контекстное меню папки / Создать папку (ввести имя); Создать ярлык, Обзор, найти файл запуска программы или буксировать пиктограмму программы.

3. Пуск / Найти / Файлы и папки.

4. Копирование, Перемещение - Правка / Копировать или буксировка + CTRL, / Вставить; Правка / Вырезать или буксировка, / Вставить.

5. Контекстное меню папки / Создать текстовый документ; Контекстное меню файла / Свойства, атрибуты скрытый, только чтение.

6. Удаление - DELETE или Контекстное меню / Удалить.

7. КОРЗИНА - Контекстное меню / Свойства - объем; / Открыть, выбрать файл, Контекстное меню / Восстановить.

8. Контекстное меню Пуск / ПРОВОДНИК, - + - свернуть, развернуть ветви.

9. Пуск / Программы / Стандартные.

Задание 1.1. Настройка WINDOWS, файловые операции

1. Настроить «Мой компьютер»:

- диск С: открывать для каждой папки отдельное окно,

- представление объектов в виде таблицы,

- сортировка объектов по имени,

- показывать все файлов,

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

2. В папке МОИ ДОКУМЕНТЫ создать папку ГРУППА, в папке ГРУППА создать папки НОВАЯ, ТЕСТ и ЗАЧЕТ.

3. В папке ЗАЧЕТ создать ярлыки для программ «Мой компьютер», «Корзина» и папку ФАЙЛЫ.

4. В папку ФАЙЛЫ скопировать 15 файлов размером не более 30 Кб, из них: 5 - с расширением *.txt, 5 - с расширением *.bmp, 5 - с расширением *.doc.

5. В папку ТЕСТ из папки ФАЙЛЫ скопировать 4 самых новых файла.

6. В папку НОВАЯ из папки ФАЙЛЫ переместить 2 самых больших файла.

7. В папке ТЕСТ с помощью БЛОКНОТА создать 3 текстовых файла, присвоить им атрибуты: 2 - только для чтения, 1 - скрытый.

8. В папке НОВАЯ удалить и восстановить 1 файл.

9. Работа с программой ПРОВОДНИК.

- представить объекты в виде списка, сортировка по имени,

- свернуть все ветви папок диска С:,

- показать панель инструментов, убрать строку состояния,

- переместить папки ТЕСТ и НОВАЯ в папку ЗАЧЕТ.

3. Установить объем «КОРЗИНЫ» 3 %.

11. Найти файлы, созданные в прошедшем месяце и содержащие в тексте слово MICROSOFT.

12. Удалить созданные объекты.

Задание 1.2. Стандартные программы WINDOWS

1. Создать документ: рисунок и шапку в PAINT, текст - в WORDPAD (при создании текста использовать табуляцию)

ОТЧЕТ

Продажа системных блоков

№ Наименование Дата Цена, $

1 Samsung 01.03 204

2 X-Ring 02.05 250

3 Lizard 05.07 215

4 Sony 06.09 305

5 Fillips 07.11 202

6 Olympus 09.12 145

Директор Иванов И.И.

Главный бухгалтер Петров П.П.

2. Рассчитать на КАЛЬКУЛЯТОРЕ:

- cos /11

- sin 54^o25'

- ((18+9)/14,5*5)*8+15=

- среднее значение чисел: 15; 18; 25,9; 134,8; 18,4; 125,1.

2. ТЕКСТОВЫЙ РЕДАКТОР WORD

1. Форматирование текста

! Установки- диалоги или инструменты на вкладках.

Настройка - , Параметры Word, вкладка Вид.

1. Разметка страницы /  Параметры страницы (поля, размер бумаги, переплет).

2. Главная /  Шрифт (вкладка Шрифт - имя шрифта, начертание, курсив, подчеркивание, размер и др.; вкладка Интервал - масштаб, разреженный (интервал между символами), По умолчанию.
   3. Главная /  Абзац (вкладка Отступы и интервалы - выравнивание слева, справа, по центру, по ширине; отступ слава и справа от абзаца; отступ перед и после абзаца; межстрочный интервал - «точно» в пунктах (ввести).

! При выравнивании по центру убрать красную строку.

4. Вставка / Надпись .

5. Вставка / Буквица, в тексте, Параметры буквицы - высота, шрифт, расстояние от текста.

6. Главная /  Абзац, вкладка Положение на странице ( с новой страницы);
   7. Разметка страницы / Границы страниц (Рисунок - рамка на странице).

8. Вставка / Номер страницы,  особый колонтитул для первой страницы - нет №;
9. Разметка страницы / Расстановка переносов, авто.

10. Рецензирование / Правописание, красный - орфография, зеленый - грамматика.

11. Вставка / Верхний, нижний колонтитул,  Разные колонтитулы для четных и нечетных страниц, двойной щелчок в тексте - выход из колонтитулов.

12. Разметка страницы / Колонки, Другие колонки,  разделитель.

13. Ссылки / Вставить сноску, (курсор установить где будет сноска, Сноска, формат сноски).

14. Настройка панели быстрого доступа, Другие команды, Все команды, Параметры автозамены.

15. Главная /  Буфер обмена.

Задание 2.1.1. Форматирование текста

(Текст - четыре главы с заголовками (глава 1, 2, 3, 4) и подзаголовками (в которой ...) в каждой главе).

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

1. Параметры страницы: бумага - 19,5 х 27,5 см; поля - левое, правое - по 1,5 см, верхнее, нижнее - по 2 см; переплетный отступ - 1,2 см.

2. Форматирование текста:

Заголовки глав (глава первая, вторая, третья, четвертая):

- шрифт: ARIAL, стиль - полужирный, размер- 22 пт, разрядка - 5 пт, масштаб символов 140 %;

- абзац: выравнивание по центру, перед/после абзаца - 6 пунктов;

Подзаголовки глав (в которой…):

- шрифт: TIMES NEW ROMAN, стиль - курсив, размер - 18 пт, подчеркивание - только слова;

- абзац: отступ абзаца слева/справа - по 2 см, выравнивание - по центру, межстрочный интервал - точно 22 пункта, перед/после абзаца - 12 пунктов;

Основной текст:

- шрифт: TIMES NEW ROMAN, размер - 14 пт.

- абзац: первая строка - отступ на 1,8 см, межстрочный интервал - 18 пунктов, выравнивание - по ширине, перед/после абзаца - 4 пункта;

3. Первый абзац каждой главы: буквица - высота 3 строки, шрифт - ARIAL, расстояние до текста - 0,4 см.

4. Заголовок каждой главы на новой странице; обрамление страниц рамкой.

5. Расставить нумерацию страниц (внизу в центре) и переносы.

6. Проверить орфографию.

7. Верхний колонтитул: на четных страницах - «Задание по WORD»; на нечетных - свою Фамилию И.О.

8. Последнюю главу разбить на две колонки с разделителем (без заголовка и подзаголовка; не включать буквицу).

9. В текст вставить 2 сноски - Яло - имя Оля наоборот, Гурд - друг наоборот.

10. Создать элемент автозамены «Королевство» (символы кр).

11. Добавить в буфер обмена 5 элементов.

2.2. Списки - перечисления

, определить новый маркер (новый формат номера),

задать начальное значение.

Многоуровневый

• ввести строки списка,

• выделить все строки, объявить многоуровневым списком,

• выделить одну строку,

• - изменение уровня строки списка.

- выделить список; Определить новый стиль списка: начальное значение; Уровень - 1, 2, 3 . .. - выбрать, выбрать вид нумерации, формат номера;

- Определить новый многоуровневый список: шрифт, отступы, дописать свой текст в Формат номера.

Задание 2.2.1. Создание списков

1. Создать маркированный список:

• Пример маркированного списка

• Пример маркированного списка

• Пример маркированного списка

( Маркер - шрифт Symbol, размер маркера - 20 пт, отступ - 1 см. Текст пунктов списка - абзац - красная строка 3 см, перед/после - 6 пт, шрифт текста - Arial, полужирный, 14 пт).

2. Создать многоуровневый список:

3. Пример многоуровневого списка

   1. Пример многоуровневого списка

      1. Пример многоуровневого списка

      2. Пример многоуровневого списка

   2. Пример многоуровневого списка

4. Пример многоуровневого списка

   1. Пример многоуровневого списка

      1. Пример многоуровневого списка

         1. Пример многоуровневого списка

(Маркер - шрифт Arial, полужирный, 14 пт, от номера до списка - 0,5 см. Текст пунктов списка - абзац - перед/после - 6 пт, шрифт Times, полужирный, 13 пт).

2.3. Графический редактор

1. Вставка / Рисунок, Фигуры, WordArt, выделить.

2. Формат / Заливка, Контур, Эффекты тени, Объем, Упорядочить (обтекание текстом, расположение на заднем, переднем плане, выравнивание, группировка, поворот), формат объекта WordArt.

Задание 2.3.1. Графические объекты,

вставка рисунков в текст

1. Вставить в текст графические объекты:

(использовать любой текстовый документ)

2. Создать графические объекты WordArt по образцу:

2.4. Создание таблиц, формулы и вычисления

1. Вставка / Таблица; количество строк и столбцов.

2. Конструктор / Границы, Заливка выделенных ячеек.

3. Макет / Удаление и вставка строк, столбцов, ячеек; объединение и разделение ячеек, Размер строк и столбцов; Выравнивание текста относительно границы; направление текста, поля ячеек, Сортировка, Преобразовать в текст.

4. Макет / Формула:

-ввод формулы (начинается с =, + - сложение, - - вычитание, * - умножение, / - деление, ^ - возведение в степень), (формула вводится без пробелов, латинскими буквами),

- адресация ячеек:

- выбор формата числа (количество знаков после «,», %).

- выбор функции (AVERAGE - среднее значении, MIN - минимум, MAX - максимум, SUM - сумма: ABOVE выше, LEFT слева, RIGHT справа от текущей ячейки),

Примеры: = SUM (A3; B3; A1) - сумма отдельных ячеек,

= SUM (A1 : B10) - сумма диапазона,

- контекстное меню формулы / Коды/Значения полей - режимы отображения формулы.

Задание 2.4.1. Создание таблицы

Создать таблицу по образцу:

Задание 2.4.2. Вычисления в таблицах

1. Создать таблицу, отсортировать по фамилии:

№

Фамилия И.О.

Стаж

Начисления, руб.

Удержания, руб.

Оклад

Премия

Подоходный

налог

К выдаче

1

Фирсов С.В.

6 мес.

12000

2

Андреев И.Т.

1 год 2 мес.

15000

3

Проскурин Н.В.

2 года

22000

4

Иванов М.А.

3 года 7 мес.

21000

5

Павлов И.И.

8 лет

25000

6

Давыдов Г.В.

15 лет

27000

7

Шмелев П.А.

6 лет 4 мес.

18000

8

Якушев Н.И.

3 мес.

22000

9

Николаев А.А.

11 мес.

11000

10

Андреев И.Т.

4 года

16000

Итого

Среднее

2. Рассчитать:
- величину премии в зависимости от стажа: (менее 1 года - 5 %, от 1года до 5 лет - 10 %, более 5 лет - 15 %),
- подоходный налог (13 % от оклада),

- сумму к выдаче в зависимости от подоходного налога,

- итого, среднее: оклад, премия, подоходный налог, к выдаче,

- максимальную зарплату, минимальную зарплату,

- разность между максимальной и минимальной зарплатой,

- максимальный подоходный налог, минимальную премию,

- разность между максимальным и минимальным подоходным налогом,

- долю в % от общей суммы окладов первого и третьего сотрудника,

- долю в % от общей суммы окладов второго и четвертого сотрудника,

- сумму налогов сотрудников под нечетными номерами,

- сумму налогов сотрудников под четными номерами.

2.5. Макросы, стили, гиперссылки, формы, слияние

1. Макросы

1.1.Создание:

- Вид / Макросы / Начать запись,

- Имя макроса - ввести (правила: начинается с буквы, до 255 символов, нельзя: пробелы, точки, запятые), ОК,

- выполнение набора действий,

- Вид / Макросы / Остановить запись.

1.2. Выполнение:

- Вид / Макросы / Макросы, выбрать, Выполнить,

- Настройка панели быстрого доступа / Другие команды, Выбрать команду из, Макросы, Добавить, Изменить - имя и кнопку,

Настройка - Категория: Макросы, Новое сочетание клавиш - нажать (alt="Практикум по дисциплине Информатика и ИКТ" или CTRL или SHIFT + любой символ),

1.3. Удаление макроса: Вид / Макросы / Макросы, выбрать Удалить.

2. Стили

2.1. Главная /  Стили, Создать стиль,

2.2. Ввести имя, выбрать Формат шрифта и абзаца.

2.3. Выбрать абзац для применения стиля, выбрать стиль из списка Стили .

3. Гиперссылки

3.1. Вставка / Гиперссылка, Текст - имя гиперссылки, Подсказка - при наведении курсора, Связать с: файлом или веб - страницей, местом в документе, новым документом, электронной почтой.

4. Формы

4.1. Кнопка «OFFICE», Параметры WORD, Основные  Показывать вкладку Разработчик.

4.2. Элементы управления, Режим конструктора:

- Форматированный текст, Текст, Раскрывающийся список, Дата,

- Свойства: название, Добавить, Краткое имя - список значений, формат даты,

- Блокировка,  Элемент управления содержимым нельзя удалить,  Содержимое нельзя редактировать - защита формы,

4.3. Разработчик, Защитить документ , Ограничить форматирование и редактирование,  Разрешить только указанный способ редактирования документа, Да, включить защиту - защита всего документа.

5. Слияние

5.1. Создание источника данных: содержит одну таблицу, первая строка - заголовки, строки - записи для включения в составной документ.

5.2. Рассылки / Начать слияние , Выбрать тип документа: (обычный документ, письма, сообщения электронной почты, конверты, …)

5.3. Подключение документа к источнику данных - Рассылки / Выбрать получателей / Ввести новый список или Использовать существующий список (по пункту 5.1).

5.4. Уточнение списка получателей или элементов - Рассылки / Изменить список получателей:   - Выделение записей; Сортировка записей - щелчок в заголовке столбца  сортировка от A до Я или Сортировка, ввести условия; Фильтр, ввести условия

5.5. Добавление текстовых заполнителей (полей слияния):

- Рассылки / Подбор полей (Установление соответствия полей слияния файлу данных), Оставить или выбрать поля,

- щелчок, где необходимо вставить поле, Рассылки / Блок адреса, / Строка приветствия, / Вставить поле слияния, выбрать,

- форматирование обычное (выделение с « »),

- Рассылки / Просмотр результатов,  - пролистывание записей,

- Рассылки / Найти и объединить, Печать.

Задание 2.5.1. Создание макроса, стиля, формы, гиперссылки

1. Создать макрос

- Имя - Текст_Заголовок

- Клавиши alt="Практикум по дисциплине Информатика и ИКТ" +Т, выбрать другую кнопку,

- Действия: п/ж, по центру, подчеркнутый, 24 пт, разрядка 5 пт, двойной межстрочный интервал, с новой страницы.

2. Стили

- Заголовок текста: Arial, 20 пт, п/ж, подчеркнутый, ширина 120 %, по центру, после абзаца 6 пт;

- Подзаголовок текста: Arial, 18 пт, п/ж, курсив, подчеркнутый, ширина 110 %, по центру, после абзаца 4 пт;

- Текст: Times New Roman, 14 пт, по ширине, красная строка 15 мм, межстрочный интервал точно 25 пт;

- Применить стили в тексту …\ ZADAN\ Текст для стилей.

3. Вставить в файл «Текст для стилей» 3 гиперссылки на любые текстовые документы (имя - Документ 1,2,3; Подсказка - дата создания документа).

4. Вставить формы:

- Форматированный текст: Оставьте Ваш отзыв.

- Текст: Оставьте Ваши координаты.

- Раскрывающийся список: Вам понадобится информация? (элементы: Да, Нет, Возможно).

- Дата: текущая, формат 25.01.2008.

Задание 2.5.2. Слияние документов

1. Создать Источник данных:

2. Создать документ слияния:

- создать Обычный факс (использовать шаблон),

- выбрать дату,

- заполнить строки:
Название организации отправителя: Небесные ласточки,
Адрес организации отправителя: 245234, Москва, ул. Стромынка, д. 20, строение 1,
От: Директора,
Телефон отправителя: (495)269-45-66,
Название организации: Небесные ласточки,
Факс: (495)269-45-66,
Число страниц: 1,
Срочный: Да,
удалить поле Необходимое действие: , ввести текст: Сообщаю Вам, что совещание директоров состоится 25.02.2008 в 15.00 в офисе нашей фирмы.

- добавить поле Адрес (форматированный текст, название: Введите адрес),

- подсоединить Источник данных,

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

3. Просмотреть результаты слияния.

3. ЭЛЕКТРОННЫЕ ТАБЛИЦЫ EXCEL

3.1. Построение таблиц, вычисления

1. Форматирование ячеек.

Главная / шрифт, заливка, Выравнивание - объединение ячеек.

2. Формулы:
   в E3 = D3*50, скопировать формулу вниз;
   в F3 = E3*12, скопировать формулу вниз.

3. Расчет Итого:
   Главная / , сумма ячеек сверху, скопировать формулу вправо.

4. Разметка страницы / Параметры страницы (поля, размер бумаги, ориентация).

5. Автоматическая нумерация - ввести 1, встать в ячейку, скопировать вниз + CTRL.

6. Формат даты и денежный: Главная / Число, вкладка Число, Дата или Денежный.

7. Главная / Условное форматирование, Правила выделения ячеек: между, Пользовательский формат.

8. Главная / Сортировка и фильтр, От минимального к максимальному (от А к Я).

9. Создание диаграмм:

9.1. Гистограмма "Клиент - затраты времени"

- выделить фамилии и время: + CTRL , без заголовков,

- Вставка / Гистограмма, выбрать тип,

- Макет / Название диаграммы, Подписи данных, Заливка, Оси и т.д.

9.2. Круговая "Клиент - сумма за год"

- выделить фамилии и сумму за год: + CTRL , без заголовков,

- Вставка / Круговая, выбрать тип,

- Макет / Название диаграммы, Подписи данных, Заливка, Оси и т.д.

! Для форматирования диаграммы выделить ее.

10. Вставка / Колонтитулы, Вид / Обычный.

11. Перемещение элементов - буксировка.
    , Печать, Предварительный просмотр, 
    Разметка страницы / Параметры страницы, поля, Центрировать на странице  горизонтально,  вертикально.
    Главная / Вставить, Вставить строки на лист, объединить ячейки, ввести заголовок.

Вставка / Рисунок.

3.2. Встроенные функции, абсолютные и

относительные ссылки, операции с матрицами,

решение уравнений

1. Вычисления:

- введение формулы «вручную» (начинается со знака «=»),

- выбор встроенной функции:

Формулы / f_x или / Математические: COS, SIN, КОРЕНЬ и др.,

/ Другие функции / Статистические: СРЗНАЧ, МАКС, МИН и др.

2. Корни квадратного уравнения:

=(-В1 КОРЕНЬ(В1*В1-4*А1*С1))/2/А1,

где А1, В1, С1 - адреса ячеек с коэффициентами.

3. Пример операции с датами (разница) = «18.06.1999» - «11.05.1960»

4. Ссылки на ячейки:

- одна (А1); диапазон (А1:С8 (разделитель - :)),

- относительные (А1), при копировании изменение значений,

- абсолютные ($A$1), при копировании значение не меняется,

- смешанные (А$1 - не изменяется строка, $A1 - не изменяется столбец).

5. Умножение матриц:

- правило: количество столбцов первой матрицы должно быть равно количеству строк второй матрицы,

- выделить блок ячеек под итоговую матрицу (кол-во строк = первой, столбцов = второй),

- Формулы / Математические, МУМНОЖ, указать две матрицы,

- CTRL+SHIFT+.

6. Решение системы уравнений:

- правило: найти обратную матрицу коэффициентов и умножить ее на матрицу свободных членов.

- обратная матрица: выделить блок ячеек под обратную матрицу (равен исходной), Формулы / Математические, МОБР, выделить исходную матрицу, CTRL+SHIFT+,

- умножение матриц по п. 5.

7. Умножение матрицы на число: умножить на число (или на абсолютную ссылку на ячейку с числом) ссылку на первый (верхний левый) элемент матрицы; скопировать формулу вправо и вниз по количеству элементов матрицы

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

= щелчок по ячейке с формулой * выделить исходную матрицу, CTRL+SHIFT+.

Задание 3.2.1. Встроенные функции,

абсолютные и относительные ссылки

1. Рассчитать:

- (COS (1,2) + SIN (0,7)) * КОРЕНЬ(9) / 4,

- корни квадратного уравнения x² - 5x + 6 = 0,

- количество дней между Новым годом и 8 Марта

2. Рассчитать в таблице (файл на компьютере):

- все значения «Итого»,

- СРЗНАЧ (С2:С16) * МАКС (Е2:Е16) / 32,

3. Рассчитать зарплату с использованием относительных и абсолютных ссылок по формуле:

Зарплата = Объем в месяц * % * курс

Задание 3.2.2. Умножение матриц, решение систем уравнений

1. Найти произведение матриц:

2

14

5

17

2

5

6

1

2

3

11

-6

-7

8

22

*

*

;

*

*

4

6

5

-8

-12

-2

-6

8

11

23

11

7

9

4

5

6

6

4

5

6

7

5

23

-4

1

4

6

3

2

6

4

8

1

1

7

9

3

7

8

-3

1

;12

-2

4

7

-6

5

3

-5

-3

9

3

-5

7

-1

5

7

2

3

1

2. Решить системы уравнений:

x₁ - x₂ + x₃ = 3, x₁ + 2x₂ + 3x₃ - 2x₄ = 6, 2x - 3y + z -2 = 0,

2x₁ + x₂ + x₃ = 11, 2x₁ + 4x₂ - 2x₃ - 3x₄ = 18, x + 5y - 4z + 5 = 0,

x₁ + x₂ + 2x₃ = 8. 3x₁ + 2x₂ - x₃ + 2x₄ = 4, 4x + y - 3z + 4 = 0.

2x₁ - 3x₂ + 2x₃ + x₄ = - 8.

3x + 2y + z = 5, x - 2y + 3z = 6, 4x - 3y +2z = 9,

2x + 3y + z = 1, 2x + 3y - 4z = 20, 2x + 5y - 3z = 4,

2x + y + 34z = 11. 3x - 2y - 5z = 6. 5x + 6y - 2z = 18.

x + y + 2z = - 1, x + y + 2z = - 1, 2x - y - z = 4,

2x ­- y - 3z = 4, 2x - y + 2z = - 4, 3x + 4y - 2z =11,

5x + 6y - 2z = 18. 4x + y + 4z = - 2. 3x - 2y + 4z = 11.

3x + 4y + 2z = 8, 4x + 7y - 3z = - 10, - 3x + 6y + 8z = - 7,

2x - y - 3z = - 4, 2x + 9y - z = 8, 9x - 11y - 15z = - 15,

x + 5y + z = 1. x - 6y + 3z = - 3. 18x - 22y + 3z = - 3.

3. Умножить матрицу на число.

12

4

6

7

8

1

1

7

9

3

71

*8

9

-22

43

7

*

5

-6

5

3

*

12

5

-31

9

21

5

7

2

3

1

3.3. Создание макросов

1. Запись макроса: Разработчик / Запись макроса,

- ввести имя макроса (без пробелов), можно ввести сочетание клавиш для быстрого запуска, ввести описание (может не быть),

- сохранить в Новой книге - при запуске создается новая книга, Эта книга - доступен только при открытой книге, Личная книга макросов - доступен для всех книг,

- выполнить совокупность действий,

(запись макроса идет с абсолютными ссылками, кнопка «Относительные ссылки» - выполнение макроса в любом месте листа),

2. Разработчик / Макросы, выбрать, Параметры - (можно изменить сочетание клавиш),

3. Добавление кнопки на панель быстрого доступа - / Другие команды, Выбрать команды из, Макросы,

4. Удаление макроса: Разработчик / Макросы, выбрать, Удалить.

Задание 3.3.1. Создание макросов

1. Записать макросы для создания таблиц с относительными ссылками и вызовом сочетанием клавиш CTRL+Т и CTRL+Р.

Расписание занятий группы на 1 семестр

1

пн

к-т

вт

к-т

ср

к-т

чт

к-т

пт

к-т

2

3

4

5

2. Записать макрос для создания таблицы для ввода анкетных данных.

Задание 3.3.2. Графики функций

Функция

Значения аргумента

от

до

с шагом

y₁ = 3 ln x

0,1

1,9

0,1

y₂ = e^x - 2

y₃ = 8/(x+0,6) - 7

y₄ = 4x³ - 20x² + 20x1 . Построить графики функций:

- выделить нужные значения функции;

- вкладка Вставка, тип диаграммы Точечная с гладкими кривыми и маркерами;

- правой кнопкой по диаграмме, Добавить линию тренда, выбрать максимально совпадающую с графиком функции;

- дополнительно через добавление данных построить все четыре графика на одной области (без линий тренда):

3.4. Работа со списками

1. Сортировка (установить курсор внутрь списка):

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

- Порядок / Настраиваемый список, НОВЫЙ СПИСОК, ввести элементы списка через ,

2. Фильтрация (внутри параметра по 2-м условиям):

- Данные / Фильтр,  выбрать нужный элемент,

- Внутри фильтра может быть своя фильтрация,

- / Текстовые фильтры / Настраиваемый фильтр или выбрать условие (можно 2 : «и» - выполнение накладывается, «или» - выполняются или/или).

3. Расширенный фильтр (внутри одного параметра более 2-х условий):

- скопировать заголовок таблицы,

- под заголовком вставлять условия,

- правила:

1) критерии в одной строке - «и», критерии в разных строках - «или»;

2) знак «=» не используется;

3) операции: >, <, >=, <= - больше, меньше, больше или равно, меньше или равно;

4) в диапазон критериев не включать пустые строки,

5) пустые ячейки -любое значение,

6) фильтр по текстовому условию с использованием * (п*, *п* …).

- Данные / Фильтр / Дополнительно,

- фильтровать на месте - замена списка, скопировать в другое место - указать диапазон,

- выбрать Исходный диапазон (или предварительно установить курсор внутри) и Диапазон условий,

- Очистить - убрать фильтр.

4. Промежуточные итоги:

- сортировка списка по элементу вычисления итогов,

- Данные / Промежуточные итоги,

- При каждом изменении в - элемент сортировки,

- Операция - выбрать,

- добавить итоги по - столбец, в котором нужны вычисления, остальные выключить,

-  заменять (предыдущие итоги),

-  итоги под данными,

- Убрать все - удаление итогов,

- 1 2 3 - структура таблицы при вычислении итогов: 1 - общий итог,

2 - только промежуточные итоги, 3 - все.

5. Сводные таблицы

- Вставка / Сводная таблица,

- выбрать диапазон, или установить курсор в список,

- поместить таблицу на новый или текущий лист,

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

- двойной щелчок по полю «Сумма» - выбрать операцию,

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

- Сводная диаграмма - по сводной таблице.

Задание 3.4.1. Работа со списком

1. Оформить таблицу: добавить - заголовок: «Расходная смета фирмы HOUSE», рамку, выделить заголовки столбцов другим форматом.

2. Отсортировать данные «Расходы» по алфавиту, показать промежуточные итоги (суммы) по видам расходов.

3. Построить на отдельном листе диаграмму для пункта 3 «Расходы - сумма» (данные в строках), переименовать лист с диаграммой в «Расходы - сумма».

4. Отсортировать данные по «получатель», показать количество расходов по каждому получателю.

5. Убрать итоги, с помощью автофильтра показать данные для получателя АО Престиж.

6. С помощью автофильтра показать данные за январь, построить диаграмму «Дата - сумма» и переименовать ее.

7. Убрать автофильтр, отсортировать данные по дате, пронумеровать строки таблицы автозаполнением, вставив столбец перед столбцом А, добавить рамку в столбце А.

8. В ячейке С240 рассчитать общую сумму расходов при помощи функции СУММ(), в ячейке С241 рассчитать общую сумму в рублях (курс: 25 руб).

Задание 3.4.2. Работа со списком

1. Сортировка списка в последовательности «день - группа - Фамилия Имя».

2. В общем списке:

- добавить заголовок «Район» и данные (по номеру телефона):

- добавить новый элемент сортировки в следующем порядке:

Строгино, Химки, Выхино, Крылатское, Таганская,

- отсортировать список по созданному элементу.

3. С помощью автофильтра:

- показать фамилии, начинающиеся на «К» и содержащие «р»,

- учащихся 599 школы,

- учащихся 1-ой группы,

- учащихся 2-ой и 3-ей групп по понедельникам и вторникам.

4. С помощью расширенного фильтра показать количество учащихся по условиям 1-5:

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

6. Создать сводные таблицы:

- количество учащихся по классам из школ,

- количество учащихся по дням из групп,

- количество учащихся по дням из классов.

4. ЯЗЫК ПРОГРАММИРОВАНИЯ VBA

4.1. Основные элементы языка VBA

Переменная - участок оперативной памяти для временного хранения данных.

Константа - для хранения постоянного значения.

Имя переменной:

- начинается с букв ;

- не более 255 символов;

- не содержит . _└┘ ! @ # & % $

(регистр символов не имеет значения)

- типы переменных:

Описание

Диапазон

Тип

Целочисленный

от 0 до 255

Byte

Целочисленный

-32 768 до 32 767

Integer

Целочисленный

-2 147 483 648 до 2 147 483 647

Long

С плавающей запятой

-3,4´10³⁸ до 3,4´10³⁸

Single

С плавающей запятой

-1,79´10³⁰⁸ до 1,79´10³⁰⁸

Double

С плавающей запятой

-9,22´10¹¹ до 9,22´10¹¹

Currency

- операции отношения и логические операции:

Операция

Описание

Операции отношения

>

Больше

<

Меньше

>=

Больше либо равно

<=

Меньше либо равно

<>

Не равно

Логические операции

OR

Или

AND

И

NOT

Нет

- математические операторы:

Оператор

Операция

+

Сложение

-

Вычитание

*

Умножение

/

Деление

\

Целочисленное деление

mod

Остаток от деления нацело

^

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

- математические функции:

Название и обозначение функции

Оператор

Абсолютная величина (модуль)

| х |

Abs(x)

Корень квадратный

√

Sqr(x)

Экспонента (степень числа е ~ 2.72)

e^x

exp(x)

Синус (угол в радианах)

sin x

sin(x)

Косинус (угол в радианах)

cos x

cos(x)

4.2. Макросы

- , Параметры Excel, Показывать вкладку «Разработчик»,

- (окно VBA), View / Immediate Window, View / Code,

1. Запись макроса:

- ! - безопасность макроса, добавить кнопку, включить все макросы,

- запись макроса - п. 3.3,

- Вид / Макросы / Макросы, выбрать, Изменить

- изменить: цифру Color

- добавить: Selection.Font.Size=28

 (Выполнить) - выполнение программы (изменение цвета и размера шрифта), добавить:

Selection.Font.Bold=True (жирный)

Selection.Font.Italic=True (курсив)

2. Создание макроса

-! - безопасность макроса, добавить кнопку, включить все макросы,

- задание: вычислить при x=6.678; y=7.565,

- вывести исходные данные и результаты расчетов, полученные результаты округлить до 2-х знаков после запятой, набрать программу,  (Выполнить).

Код макроса

Комментарий

Option Explicit

Объявление необходимости задания переменной

Public Sub Execute()

Начало процедуры

Dim x As Single, y As Single

DIM - оператор объявления (задания) переменной, Single - тип переменной (с плавающей запятой)

Dim z As Single, d As Single

x = 6.678

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

y = 7.565

z = Sqr(x ^ 2 + y ^ 2)

Расчетные формулы

d = z ^ 3

Debug.Print "X="; x; " Y="; y

Вывод результатов

Debug.Print "Z="; Format(z, "###0.00"); "D="; Format(d, "###0.00")

Вывод результатов с округлением (не переносить строку)

End Sub

Окончание процедуры

Задание 4.2.1. Запись, редактирование, создание макроса

1. Записать макрос «Таблица», назначить макросу сочетание клавиш CTRL+Т. Изменить размер шрифта (18 пт), начертание - курсив.

Расписание занятий группы на 1 семестр

2. Создать макросы по расчету значений функций с точностью 2 знака:

2.1. x = 12.347, y = 10.678

2.2. x = 6.121, y = 15.773

2.3.

x = 15.664, y = 29.786, t = 32.124

4.3. Условный переход (If … Then…)

Пример: рассчитать значение А и n при x = 2.3, y=45.8, z=55.5 и условиях:

Код макроса

Option Explicit

Public Sub Execute()

Dim a As Single, x As Single, y As Single

Dim z As Single, n As Single

x = 2.3

y = 45.8

z = 55.5

a = Sqr(Abs(x + y ^ 2 + z))

If a > 2 Then

n = x ^ 2 - y ^ 3 + 2 * x * y

End If

If a = 2 Then

n = y ^ 3 - x ^ 2

End If

If a < 2 Then

n = 1 - (y - z) / (y + z)

End If

Debug.Print "x ="; x; "y ="; y; "z ="; z

Debug.Print "a ="; a

Debug.Print "n ="; n

End Sub

Задание 4.3.1. Условный переход

4.4. Функции пользователя

Пример: создать функцию для вычисления в EXCEL

1. Insert / Module - добавление в проект модуля,

2. щелчок на пиктограмме Module 1,

3. в окне свойств (Properties), Names (имя), ввести Vector (имя модуля),

4. в окне VBA ввести операторы функции:

Public Function getVector(a As Single, b As Single) As Single

Dim z As Single

z = Sqr(a ^ 2 + b ^ 2)

getVector = z

End Function

(Function - имя процедуры, Public - глобальный доступ)

4. Сохранить результаты, закрыть окно VBA.

5. Найти созданную функцию в EXCEL- f_x , выбрать категорию

«Определенные пользователем», ввести значения аргументов.

Задание 4.4.1. Функции пользователя

1. Создать пользовательские функции,

2. Рассчитать значения x

при a = 0,1  0,1 в диапазоне от 0,1 до 3,

b = 2

3. Построить графики функции x(a)

Formula1

Formula2

Formula3

Formula4

4.5. Организация Windows диалога

1. Функция InputBox (для ввода строки текста в программу c помощью окна диалога Windows):

- формат вызова функции:

Dim s As String

S=InputBox(«приглашение»,["значение по умолчанию"])

- функции преобразования чисел в типы данных:

CInt(s) - Integer, CLng(s) - Long, CSng(s) - Single, CDbl(s) - Double, CDate(s) - Date, Cvar - Variant

2. Функция MsgBox (для организации диалога с пользователем):

- формат вызова функции:

response=MsgBox("Текст сообщения",флаги,"Заголовок")

- аргументы функции:

- "Текст сообщения" - cсообщение пользователю,

- флаги - комбинация системных констант VBA для вывода кнопок и пиктограммы к сообщению (vb…):

Системные константы

- при закрытии диалога в переменную response возвращается код нажатой кнопки:

Коды кнопок

- "Заголовок" - текст для заголовка окна (необязателен).

Пример: выполнить вычисления (макрос) по формуле

- данные вводятся с клавиатуры,

- если данные не числа - сообщение об ошибке,

- после вычислений вывести запрос на продолжение.

Код макроса

Комментарий

Option Explicit

Public Sub ExecDialog()

Начало процедуры

Dim a As Single, b As Single

Задание переменных

Dim z As Single

Dim strA As String, strB As String

Dim response

newInput:

Ввод данных в диалог

strA = InputBox("InputA")

strB = InputBox("InputB")

If Not IsNumeric(strA) Or Not IsNumeric(strB) Then

Проверка ввода (условие) (если данные не числа - сообщение об ошибке в диалоге с пиктограммой (х))

MsgBox "Error!" & Chr(10) & Chr(13) & "Not number data!", vbCritical, ""

Exit Sub

End If

a = CSng(strA): b = CSng(strB)

Преобразование a и b в число с плавающей запятой

z = Sqr(a ^ 2 + b ^ 2)

Формула

MsgBox "Z=" & Format(z, "###0.00") & "A=" & a & "B=" & b, vbInformation, ""

Вывод результатов работы в диалог с пиктограммой (!)

response = MsgBox("Input new data", vbQuestion + vbYesNo, "")

Запрос на повторный ввод данных в диалоге с пиктограммой (?) и кнопками Да, Нет

If response = vbYes Then GoTo newInput

Если Да, новый ввод данных

End Sub

Окончание процедуры

Задание:

1. Убрать проверку ввода.

2. Убрать запрос на ввод новых данных.

3. Изменить имя процедуры на «h».

4. Изменить имена переменных: strA на с, strB на d.

5. Изменить сообщения InputA на ВводА, InputB на ВводВ.

Задание 4.5.1. Организация Windows диалога

- выполнить вычисления (макрос) по формулам:

- данные вводятся с клавиатуры,

- если данные не числа - сообщение об ошибке,

- после вычислений вывести запрос на продолжение.

4.6. Массивы

1. Массивы - это объекты программы, которые позволяют хранить однотипные значения под определенным именем.

Доступ к элементам массива осуществляется по их номеру - индексу. Если для доступа к элементу массива нужен один индекс, то массив одномерный, если два индекса, то массив двухмерный (матрица).

2. Одномерные массивы задаются в следующем виде:

Dim имяМассива (1 То n) As T, где T - тип элементов.

Матрицы задаются в виде:

Dim имяМассива (1 То n, 1 To m) As T, где n - число строк, а m - число столбцов

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

Randomize - активизация генератора случайных чисел;

Rnd - получение случайного вещественного числа нормально распределенного в диапазоне от 0 до 1.

4. Обработка массивов производится в цикле. Основным циклом является цикл типа FOR:

For i = n1 to n2 [step n3]

'операторы

next i

i - счетчик шагов цикла,

n1 - начальное значение счетчика цикла,

n2 - конечное значение счетчика,

n3 - шаг приращения счетчика (необязательный параметр, по умолчанию = 1).

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

Do While условие

'операторы

loop

6. Range - объект для работы с данными в ячейках рабочего листа.

Cells - объект доступа к диапазону ячеек по индексам.

Пример 1 (одномерный массив): заполнить массив случайными вещественными числами, вычислить их сумму, вывести сумму и массив.

Операторы программы

Комментарий

1

2

Option Explicit

Public Sub ExecArray()

Начало процедуры

Const n = 10

Задание константы

Dim sngArray(1 To n) As Single

Задание одномерного массива

Dim i As Integer

Задание счетчика шагов

Dim mes As String

Задание переменной mes

Randomize

Активизация генератора случайных чисел

mes = "V["

Начальное значение для строки элементов массива

For i = 1 To n

Начало цикла

sngArray(i) = Rnd

Получение случайного вещественного числа

mes = mes & "" & Format(sngArray(i), "###0.00")

Вывод переменной mes с округлением

Next i

Следующее значение i

mes = mes + "]"

Конечное значение для строки элементов массива

Dim s As Single

Задание переменной s

s = 0

Начальное значение суммы

For i = 1 To n

Начало цикла

1

2

s = s + sngArray(i)

Вычисление суммы

Next i

Следующее значение i

MsgBox "S=" & s & Chr(10) & Chr(13) & mes, vbInformation, ""

Вывод результатов в диалог

(Chr - управляющие коды для перехода на новую строку)

End Sub

Окончание процедуры

Задание:

1. Изменить имя процедуры на «Massiv».

2. Изменить имя массива на L.

3. Убрать начальное и конечное значение переменной mes.

4. Убрать округление переменной mes.

5. Убрать пиктограмму с «!».

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

Код макроса

Комментарий

Option Explicit

Public Sub getNumbers()

Начало процедуры

Dim strN As String, strX As String

Задание переменных

Dim n As Single, s As Single, x As Single

strN = InputBox("Вв. Предельное число N")

Диалог для ввода предельного числа

If Not IsNumeric(strN) Then Exit Sub

Если введено не число - выход из программы

n = CSng(strN)

Преобразование n в число с плавающей запятой

s = 0

Начальное значение суммы

Do While s < n

Начало цикла по логическому условию

strX = InputBox("Введите число")

Диалог для ввода числа

If Not IsNumeric(strX) Then Exit Do

Если введено не число - выход из программы

x = CSng(strX)

Преобразование x в число с плавающей запятой

s = s + x

Вычисление суммы

Loop

Конец

MsgBox "Получено=" & s & "N=" & n

Вывод результатов в диалог

End Sub

Окончание процедуры

Задание:

1. Заполнить два массива случайными числами, вычислить их сумму.

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

Пример 3 (операции с матрицами): Заполнить квадратные матрицы А и В размера n x n случайными вещественными числами. Получить новую матрицу С по формуле: С(i,j)=A(i,j)+B(i,j).

Рабочий лист после обработки данных:

1. Создать 3 командные кнопки, изменить имя и заголовок.

2. Ввести операторы программы в окне VBA

Задание 4.6.1. Одномерные массивы

Создать массив и процедуру его обработки:

1. С помощью генератора случайных чисел создайте два массива: M1 и М2 по 30 чисел каждый. Вычислите сумму каждой пары элементов этих массивов и выведите в диалоговом окне новый массив МЗ m3(i)=m1(i)+m2(i). Найдите максимальные значе­ния элементов каждого массива.

2. С помощью генератора случайных чисел создайте массив на 25 элементов. В диалоговом режиме введите два целых числа X и Y. В исходном массиве замените нулями элементы, модуль ко­торых при делении на X дает в остатке число, большее Y. По­лученный массив вывести в диалоговом окне.

3. Заполните массив M1 30-ю случайными числами в диапазоне от -50 до 50. С помощью генератора случайных чисел создайте число А1 и выведите его в окне. Все элементы последователь­ности, меньшие числа А1, замените нулями. Посчитайте сум­му элементов диапазона от -5... до 9 и количество элементов, вошедших в этот промежуток. Результаты вывести в диалого­вых окнах.

4. Массивы A,B,C,D заполнены числами (по 20 чисел). Массив F заполнить максимальными элементами этих массивов, а мас­сив J - минимальными. Вывести все массивы в диалоговых окнах.

5. Массивы A, B, C, D заполнить числами. Найти минимальный эле­мент каждого массива и найти произведение этих минимумов. Вывести все массивы, минимальные элементы и их произведе­ния в разных диалоговых окнах.

6. Даны два массива С и D по 30 элементов каждый. Поменять местами элементы столбца С, имеющие четный индекс, на эле­менты столбца D, имеющие нечетный индекс. Вывести все мас­сивы в разных диалоговых окнах.

7. В массиве С находится возраст 30-ти жителей дома от 1 до 75 лет в произвольном порядке. Выведите массив, отсортирован­ный по возрастанию, поместив около каждой цифры слова: "год", "года", "лет".

8. В массивы В и Е введите оценки двух групп по дисциплине "Информатика" и определите, в какой группе больше пятерок. В какой группе больше двоек? Определите, в какой группе лучше успеваемость. Результаты выведите на рабочий лист в диа­логовых окнах.

9. Массив А заполнить годами: от 1970 до 2000. Массив С запол­нить последовательно значениями урожайности зерновых за последние 30 лет. В каком году урожайность была ниже сред­него значения? Результат вывести на рабочий лист в диалого­вом окне.

10. Массив F заполнить произвольными числами. Все элементы, предшествующие минимальному, выделить в отдельный мас­сив и переместить в массив К. Выведите оба массива и мини­мальный элемент в диалоговых окнах. Рассмотрите граничные варианты.

11. Массив А заполнить количеством осадков в мм, выпавших в Москве в течение последних 30 лет XX века. Найти среднее количество осадков и отклонение от среднего для каждого года. Результаты вывести на рабочий лист в диалоговых окнах.

12. Массив А содержит имена: Маша, Коля, Саша, Валя, Ира и т.д. Массив В, соответственно, содержит значения жен, муж, муж, муж, жен и т.д. Сформируйте два массива С и D из лиц мужско­го и женского пола. Выведите оба массива в диалоговых окнах.

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

14. Массивы А и В заполнены целыми числами. Вычислить в мас­сиве С сумму кубов каждой пары элементов и разместить по убыванию в массиве D. Выведите все массивы в диалоговых окнах.

15. Массив А(15) заполнить целыми числами величиной от 100 до 100000. В массив В поместить сумму цифр каждого числа. Выведите оба массива в диалоговых окнах. Отдельно выведите значение максимального числового значения элемента (по сум­ме цифр).

Задание 4.6.2. Матрицы

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

1. Диапазон А1:К16 заполнить целыми числами. Расположить строки по возрастанию суммы их элементов.

2. Начиная с ячейки В1 рабочего листа диапазон содержит в произ­вольном порядке даты рабочих дней за первый квартал. Распределите даты в трех диапазонах (строках) - для каждого месяца свой. Отсортируйте даты в каждой строке по убыванию.

3. Диапазон A1:F6 заполнен целыми числами. Расположить диагональные элементы таким образом, чтобы они оказались выс­троены по убыванию.

4. Двумерный диапазон неизвестного размера заполнен целыми числами. Поменять местами строки с номерами р и q (номера вводятся в диалоговом окне).

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

6. Дана целочисленная матрица А1:N14. Необходимо сформиро­вать одномерный массив из элементов, лежащих выше главной диагонали, включая диагональ, и разместить его в столбце Р.

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

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

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

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

11. Матрица размерностью N строк и М столбцов заполнена целы­ми числами. Необходимо определить, какой из размеров боль­ше, и недостающие ячейки заполнить единицами.

12. На рабочем листе интервал A1: E1 заполнен целыми числами. В каждой строке есть нулевой элемент. Необходимо поменять местами нулевые элементы на цифры первого столбца.

13. Интервал A2:J11 заполнен целыми числами. Поменять местами максимальные значения элементов 1-ой и 2-ой строки, 3-ой и 4-ой, ..., 9-ой и 10-ой.

14. На листе Excel дана таблица продаж за месяц в виде:

В столбце Е рассчитайте себестоимость каждого вида товара (B*C-D). Выведите сообщение: какой товар продавать выгод­нее? На следующем листе повторите таблицу, расположив то­вары по размеру прибыли на единицу товара. 15.

15. Столбец А заполнен целыми числами от 1 до 100000. В стол­бец В нужно поместить сумму цифр каждого числа, из сосед­ней ячейки столбца А значение максимального элемента (по сумме цифр) вывести красным цветом, применив жирный кур­сив.

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

На втором листе размещается таблица товаров в валюте, куп­ленных в этот период:

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

4.7. Проектирование интерфейса. Объект User Form

1. Элементы управления (ActiveX).

1.1. Добавление кнопки:

- Разработчик / Вставить / Элементы ActiveX , Кнопка, режим конструктора,

- растянуть кнопку на листе,

- правой кнопкой по кнопке / Свойства:

1. 2. Программирование кнопки:

- правой кнопкой по кнопке / Исходный текст - окно VBA,

- ввести код:

Private Sub cmdNumber1_Click()

Начало процедуры для события Click - щелчок по кнопке

'Загрузка формы

Комментарий

Load frmNumber1

Загрузка формы с именем Number1

frmNumber1.Show

Вывод на экран формы Number1

End Sub

Окончание процедуры

1.3. Пользовательские формы:

- Insert / User Form, View / Properties Window

- визуальные компоненты диалога: View / ToolBox

Визуальный

компонент

Назначение

Префикс

Label

Текстовая метка - ввод текста

lbl

TextBox

Тестовое поле - ввод - вывод строки текста

txt

ListBox

Список - выбор значения из набора

lst

CheckBox,

OptionButton

Переключатели

chk,opt

ToggleButton

Кнопка - выбор значения

tgb

CommandButton

Командная кнопка - выполнение действия

cmd

Frame

Кадр - группировка элементов формы

fra

1.4. Создание формы «Закрыть»:

- создать на рабочем листе командную кнопку с заголовком «Закрыть форму!», запрограммировать ее:

Private Sub cmdForm1_Click()

Начало процедуры для события Click - щелчок по кнопке

'Загрузка формы

Комментарий

Load Form1

Загрузка формы с именем Form1

Form1.Show

Вывод на экран формы Form1

End Sub

Окончание процедуры

- создать форму Form1 с текстовой меткой и командной кнопкой:

Текстовая метка:

Свойство

Значение

Caption (Заголовок)

Первая форма

AutoSize (Автоподбор)

True

BackColor (Цвет заливки)

Выбрать

Font (Шрифт)

Гарнитура шрифта

ForeColor (Цвет шрифта)

Выбрать

Picture (Рисунок)

Выбор рисунка

TextAlign(Выравнивание текста)

2 - … Center

Командная кнопка: имя - CloseButton, заголовок - Закрыть!, изменить шрифт, заливку.

Программирование кнопки:

- открыть редактор VBA ,

- ввести End между операторами:

Private Sub CloseButtom_Click()

End

End Sub

- проверка работы формы.

4. Проверить работу формы.

Задание 4.7.1. Создание формы

Читатель

Программирование командной кнопки в форме:

Private Sub CommandButton1_Click()

Reader.Caption = TextBox1.Text

End Sub

Проверка работы формы - возможность изменения заголовка окна.

Задание 4.7.2. Создание формы (расчет по курсу валют)

Разместить и запрограммировать на рабочем листе и форме элементы:

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

- Текстовые поля: ввод курсов валют, ввод суммы для расчета.

- Блоки радиокнопок для выбора вида операции и курса валют.

- Текстовое поле «Итого» для вывода результатов расчета.

- Командные кнопки «Расчет» и «Сброс».

1. Размещение на листе кнопки. Свойства кнопки:

2. Программирование кнопки:

Private Sub cmdLoad_Click()

'Загрузка формы

Load frmValuta

frmValuta.Show

End Sub

3. Insert / User Form - создание формы. Свойства формы:

4. Размещение на форме текстовых полей, меток, флажков, рамок, командных кнопок, имеющих свойства:

Свойства текстовых полей:

Name

Text

txtEurRub

35,70

txtUSDRubl

24,85

txtSumma

-

Свойства меток текстовых полей:

Name

Autosize

Caption

lblEURRUB

True

Евро/Рубль

lblUSDRub

True

Доллар/Рубль

lblSumma

True

Сумма

Свойства блока кнопок для выбора курса валют:

Элемент

Name

Caption

Autosize

Value

Рамка

FraKurs

Курс

Нет

Нет

Радиокнопка

optEURRUB

ЕВРО

False

False

Радиокнопка

optUSDRUB

ДолларСША

False

True

Свойства блока кнопок для выбора типа операции:

Элемент

Name

Caption

Autosize

Value

Рамка

FraExec

Вид операции

Нет

Нет

Радиокнопка

optValutaRub

Валюта-Рубли

False

True

Радиокнопка

optRubValuta

Рубли-Валюта

False

False

Свойства командных кнопок:

Name

Caption

Default

cmdGo

Расчет

True

cmdReset

Сброс

False

Свойства текстовой метки для вывода результатов расчетов:

Name

Caption

Autosize

BorderStyle

Value

lblItog

Итого:

False

1

Нет

Операторы программы

Option Explicit

Private Sub cmdGo_Click()

'Контроль данных

If Not IsNumeric(txtUSDRubl.Text) Or Not IsNumeric(txtEurRub.Text) Or Not IsNumeric(txtSumma.Text) Then

MsgBox "Введены неверные данные !", vbCritical, ""

Exit Sub

End If

'Чтение курса валюты

Dim kurs As Single

If OptUSDRUB.Value Then

kurs = CSng(txtUSDRubl.Text)

Else

kurs = CSng(txtEurRub.Text)

End If

'Проведение вычислений

Dim summa As Single

Dim mes As String

summa = CSng(txtSumma.Text)

Dim itogo As Single

If optValutaRub.Value Then

itogo = kurs * summa

mes = " Валюта - Рубли «

Else

itogo = summa / kurs

mes = " Рубли - Валюта "

End If

lblItog.Caption = " Итого: Вид операции " & Chr(10) & Chr(13) & mes & Format(itogo, "###0.00")

End Sub

Private Sub cmdReset_Click()

'Возврат формы в исходное состояние

txtUSDRubl.Text = "24,85"

txtEurRub.Text = "35,70"

txtSumma.Text = ""

lblItog.Caption = " Итого: "

optValutaRub.Value = True

OptUSDRUB.Value = True

End Sub

5. БАЗЫ ДАННЫХ ACCESS

5.1. Проектирование таблиц

1. Совокупность объектов Базы данных (БД):

- таблицы, в Access данные хранятся в виде связанных таблиц - реляционные БД; запись: данные об объекте (строка), поле: однородные данные (столбец);

- запросы: отбор данных;

- формы: бланки для заполнения;

- отчеты: отображение данных;

- макросы: автоматизация выполнения операций;

- модули: вычисления или события на Visual Basic.

2. Создание БД:

- Новая база данных, ввести имя БД,  - выбрать расположение,
Создать;

- Главная / Режим , Конструктор, ввести имя таблицы;

- ввод имен полей: до 64 символов + └┘ , нельзя ! . > < ;

3. Подбор типов данных ( Счетчик  ):

- ввести описание (комментарии, необязательное поле);

4. Задание параметров полей (Свойства поля / Общие) в зависимости от типа данных:

Размер числового поля:

5. Установка поля первичного ключа (уникальное значения для различения записей, определяет содержимое других полей):

- режиме конструктора,

6. Создание подстановок:

- свойства поля, вкладка Подстановка;

- тип элемента управления: поле со списком

- тип источника строк: список значений (данные с клавиатуры)

- источник строк: «Знач1»; «Знач2»; «Знач3» (ввод в « » через ;).

7. Режим таблицы, ввод данных в таблицу.

8. Установка связей между таблицами:

- Работа с базами данных / Схема данных, добавить таблицы;

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

Задание 5.1.1. Проектирование таблиц

1. Создать базу данных (имя «Туры Греции»), состоящую из таблиц «Предлагаемые туры» и «Клиенты».

2. Установить первичный ключ - Код тура.

3. Подобрать типы данных, свойства полей.

4. Создать связь между таблицами (Код тура).

5. Создать подстановки для пансиона («FB»; «HB»; «BB») и обращения («Господину»; «Госпоже»).

Таблица «Предлагаемые туры»

Имя,

Тип данных

Свойство поля

Имя,

Тип данных

Свойство поля

Код тура

(Числовой)

Формат - основной

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

Обязательное поле - Да

Индексированное поле - Да (Совпадения не допускаются)

Отель

(Текстовый)

Размер - 20

Индексированное - Да (Допускаются совпадения)

Категория

(Числовой)

Размер - Байт

Формат - основной

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

Тип номера (Текстовый)

Размер - 20

Пансион

(Текстовый)

Размер - 10

Стоимость (Денежный)

Формат - Евро

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

(Числовой)

Размер - Байт

Формат - Основной

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

Значение по умолчанию - 2

Условие на значение

… = 2 or 1

Сообщение об ошибке - Поле может содержать только числа 2 или 1

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

(Числовой)

Размер - Байт

Формат - Основной

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

Значение по умолчанию - 14

Условие на значение

… = 14 or 7

Сообщение об ошибке - Поле может содержать только числа 14 или 7

Ребенок

(Логический)

Формат - Да/Нет

НДС

(Денежный)

Формат - Евро

Экскурсии

(Логический)

Формат - Да/Нет

Страховка (Денежный)

Формат - Евро

Значение по умолчанию - 50

Таблица «Клиенты»

Имя,

Тип данных

Свойство поля

Имя,

Тип данных

Свойство поля

Код тура

(Числовой)

Формат - Основной

Индексированное поле - Да (Допускаются совпадения)

Номер договора (Числовой)

Формат - Основной

Индексированное поле - Да (Совпадения не допускаются)

Обращение

(Текстовый)

Размер - 10

Значение по умолчанию - Господину

Условие на значение

…= «Господину» or «Госпоже»

Сообщение об ошибке - Допустимы значения «Господину» или «Госпоже»

Имя, отчество (Текстовый)

Размер - 30

Фамилия (Текстовый)

Размер - 40

Почтовый адрес (Текстовый)

Размер - 40

Почтовый индекс

(Числовой)

Размер - Длинное целое

Формат - Основной

Индексированное поле - Да (Допускаются совпадения)

Населенный пункт

(Текстовый)

Размер - 40

Телефон (Текстовый)

Размер - 20

Дата заказа

(Дата / Время)

Индексированное поле - Да (Допускаются совпадения)

Скидка

(Числовой)

Размер - Двойное с плавающей точкой

Формат - Процентный

5.2. Создание форм

1. Создание / Пустая форма, Режим Конструктор.

2. Добавить поля, буксировать на форму.

- перемещение полей буксировкой,

- форматирование полей - лента «Конструктор»,

3. Добавление элементов управления (Конструктор):

- Линия, Прямоугольник - для разделения информации;

- Рисунок - растянуть рамку, выбрать;

- Надпись - текстовое поле;

- Кнопка - растянуть, Свойства, вкл. Все, адрес гиперссылки - связь с файлом, объектов в БД;

- Поле - вычисляемое поле: в поле «Свободный» вставить формулу (построитель выражений или ввести формулу, Пример: =[Поле1]+[Поле2]);

Правила:

- арифметические операции: + - * / ^;

- логические операции: > (больше), < (меньше), = (равно), <> (не равно), >= (больше или равно), <= (меньше или равно);

- Имя поля в [ ];

- Поле в формуле должно быть в форме.

4. Добавление подчиненной формы - буксировкой в любое поле формы названия другой формы из перечня объектов слева.

Задание 5.2.1. Создание формы

Создать форму по таблице «Предлагаемые туры» (оформление по образцу)

кнопка (рисунок)

с гиперссылкой на любой запрос из БД

вычисляемое поле

(стоимость без НДС)

кнопка с гиперссылкой

на таблицу «Клиенты»

гиперссылка на любой текстовый документ

5.3. Создание запросов, отчетов

1. Фильтрация:

- установить курсор в поле;

- Главная / Фильтр,  или  - отображение записей,

- Фильтр / Выделить все - все записи;

- Главная / Дополнительно, Расширенный фильтр, ввести условия отбора (по правилам в 5.2).

2. Проектирование запросов:

- Создание / Конструктор запросов, добавить таблицы (таблицы должны быть связаны);

- добавить поля (двойной щелчок);

- сортировка - записей;

-  вывод на экран;

- условие отбора (пример: = 4, (см. правила в 5.2); или - второе условие (при наличии первого условия - продублировать его в строке «или»);

- Конструктор / ! - выполнение запроса.

3. Групповые операции (итоговый запрос):

- Конструктор /  Итоги - строка Групповая операция: Группировка - одинаковых записей; выбрать операцию - вычисления:

- ! - выполнение запроса - вычисление по сгруппированным записям;

- отжать кнопку  Итоги - удаление групповой операции.

4. Вычисляемое поле:

- Конструктор, строка Поле, ввести выражение (пример: =НДС*2);

- ! - выполнение запроса - добавление поля Выражение 1,2 … с вычислениями для каждой записи.

5. Перекрестный запрос (по двум и более значениям группировки):

- добавить таблицы (запросы), поля;

- Конструктор / Перекрестный:

- строка Групповая операция: группировка для объединения записей, операция для вычисления);

- строка Перекрестная таблица: заголовки строк и столбцов для сгруппированных записей, значение для вычислений).

6. Запросы на изменение:

6.1. Запрос на добавление:

- создать запрос на выборку (добавить таблицы, поля);

- Конструктор / !+Добавление;

- ввести имя новой таблицы;

- сохранить запрос;

6.2. Другие запросы: составить запрос на выборку, Конструктор / Обновление, строка Обновление - ввести условие (Построитель), выполнить запрос; / Создание таблицы - по отобранным записям; / Удаление - записей по условиям.

7. Создание отчетов:

- Создание / Мастер отчетов, выбрать таблицы, добавить поля;

- выбрать (если нужно): группировка (однородных записей), порядок сортировки, вид макета, ориентацию бумаги,  настроить ширину полей, стиль, ввести имя;

- Режим / Конструктор - области отчета:

1) заголовок - название отчета,

2) верхний колонтитул - заголовки столбцов,

3) нижний колонтитул - дата = Now ( ), нумерация страниц = [Page] (1, 2, 3),

4) область данных,

5) примечание;

- Главная /, Конструктор / - форматирование, редактирование отчета;

- Конструктор / - встраивание элементов управления (!вычисляемое поле вставляется в примечание);

- встраивание подотчета - буксировка в область заголовка отчета из окна слева;

- Конструктор / Группировка - групповые операции, сортировка.

8. Внешние данные / Импорт - файлов из других приложений, / Экспорт - файла ACCESS в другие программы.

9. Создание / Макрос / Макрос, Макрокоманды , выбрать действие (например: Открыть таблицу), выбрать Аргументы (например, выбрать таблицу), сохранить макрос, ! - выполнение макроса (или двойной щелчок в перечне объектов слева).

Команда

Назначение

Примечание

Восстановить

Восстановление
свернутого окна

ВыполнитьКоманду

Выполнение команды

меню MS ACCEESS

Например: команды упорядочивания положения окон

РядомВертикально

РядомГоризонтально

Выход

Завершение работы MS ACCESS

Закрыть

Закрытие текущего окна

ЗапускЗапросаSQL

Выполнение запроса

Запрос на изменение данных или структуры БД

ЗапускМакроса

Выполнение макроса

ОткрытьЗапрос

Выполнение запроса

Запрос на выборку

ОткрытьОтчет

Открытие отчета

ОткрытьТаблицу

Открытие таблицы

ОткрытьФорму

Открытие формы

Развернуть

Раскрытие текущего окна во весь экран

Свернуть

Свертывание текущего окна

Сигнал

Подача звукового сигнала

Сообщение

Вывод сообщения

Сообщение выводится в системном окне Windows. Нужно задать текст сообщения, выбрать его тип, задать текст заголовка окна

Задание 5.3.1. Создание запросов, форм, отчетов

- По таблице «Предлагаемые туры»

1. создать подстановки для полей «Ребенок» - Да, нет; «Пансион» (список значений); «Экскурсии» - Да, Нет,

2. фильтрация таблицы по категории 5,

3. составить запрос с полями: «Код тура», «Отель», «Пансион»,

4. создать форму с полями: «Код тура», «Отель», «Пансион», «Категория», вычисляемое поле «Стоимость с НДС», вставить гиперссылку на таблицу «Предлагаемые туры»,

5. создать отчет с полями: «Тип номера», «Стоимость», «Количество человек».

- По таблице «Клиенты»

1. создать подстановки для полей «Код тура», «Обращение» (списки значений),

2. фильтрация таблицы по коду тура 4, населенному пункту «Киев»,

3. составить запрос с полями: «Код тура», «Фамилия», «Имя, отчество», «Телефон»,

4. создать форму с полями: «Код тура», «Фамилия», «Населенный пункт», «Дата заказа», вставить графический объект, вставить кнопку с гиперссылкой на таблицу «Клиенты»,

5. создать отчет с полями: «Код тура», «Фамилия», «Почтовый адрес», «Почтовый индекс», «Телефон».

Задание 5.3.2. Создание запросов, форм, отчетов

(база данных - файл «Сластена Лили»)

1. Выполнить подстановки:

- в таблице «Заказы» - страна получатель - Canada, USA;

- в таблице «Заказы» -кредитная карта - 1, 2, 3;

2. Выполнить фильтрацию:

- в таблице «Заказчики» - только USA;

- в таблице «Заказы» - страна USA, кредитная карта 3;

- в таблице «Заказы» - страна Canada, дата оплаты: 01.01, кредитная карта 1 и 2;

- в таблице «Сотрудники» - отдел «Экспедиция»; дата рождения > 01.01.62.

3. Создать запросы:

- таблица «Заказчики» - фамилия; адрес; страна: USA,

- таблица «Доставка» - код доставки + таблица «Заказы» - вид заказа; имя получателя; страна получатель: USA; дата заказа > 03.11.

4. Создать отчеты:

- таблица «Заказчики» - фамилия, имя, телефон, город, страна.

- таблица «Заказы» - город, страна, номер счета, дата оплаты.

Задание 5.3.3. Создание базы данных

1. Создать базу данных (имя «Сделки фирмы-посредника»

2. Подобрать типы данных, свойства полей.

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

Создать:

4. подстановки: Город Продавца и Номер продавца.

5. Запросы:

- по Таблице 1: Имя Продавца, Процент;

- по Таблицам 2, 3: Номер Продавца, Рейтинг Заказчика; Имя Заказчика;

- по Таблицам 1, 3: Номер Продавца, Имя Продавца, Сумма;

- по Таблице 1: запрос на обновление - увеличить процент в 2 раза;

- по Таблице 3: итоговый запрос - сумма по заказчикам.

6. Формы:

- по Таблице 2 (все поля):

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

2) гиперссылка с кнопкой (подпись) на таблицу1,

3) гиперссылка с кнопкой (рисунок) на таблицу3,

• по Таблице 3 (все поля) + подчиненная форма по Таблице 1 (все поля):

1) вставить графический объект (логотип фирмы - нарисовать самим);

2) добавить вычисляемое поле «Общая сумма»: =[Сумма]*0,25.

• отформатировать элементы форм.

7. Отчеты:

- по Таблице 3: номер заказчика, сумма, дата,

- отформатировать отчет,

- вставить логотип (см. пункт 5),

- перенести дату в заголовок,

- встроить подотчет по Таблице 2: номер заказчика, имя заказчика, город,

- в примечании отчета вставить вычисляемое поле: итого сумма,

- нумерация страниц: 1, 2, 3.

8. Макросы:

- открытие Таблицы 1;

- открытие запроса;

- открытие формы;

- закрытие таблицы 3.

Таблица 1. Продавцы

Номер продавца

Имя продавца

Город продавца

Процент

1

Пушкин

Москва

0,12

2

Лермонтов

Питер

0,13

3

Волошин

Ялта

0,11

4

Есенин

Питер

0,15

5

Маяковский

Москва

0,10

Таблица 2. Заказчики

Номер

заказчика

Имя

заказчика

Город

заказчика

Рейтинг

заказчика

Номер

продавца

1

Карамзин

Тверь

100

1

2

Костомаров

Москва

200

3

3

Соловьев

Питер

300

2

4

Мережковский

Нижний

200

2

5

Пыляев

Москва

300

1

6

Гиляровский

Москва

200

2

7

Алданов

Урюпинск

100

4

Таблица 3. Покупки

Номер сделки

Сумма

НДС

Дата

Номер

заказчика

Номер

продавца

1

19,43

да

10/03/2008

2

1

2

720,3

да

24/03/2008

5

3

3

2000,2

нет

03/06/2008

5

2

4

107,3

нет

24/06/2008

1

4

5

10,33

нет

24/10/2008

4

5

6

10,56

да

04/11/2008

3

3

7

2012,45

нет

04/11/2008

6

2

8

120,34

нет

05/11/2008

7

4

9

654,83

нет

09/11/2008

2

5

10

456,67

нет

10/12/2008

3

1

11

34,5

да

10/12/2008

1

2

Задание 5.3.4. Работа с базами данных

1. Импортировать в ACCESS таблицу EXCEL «Книги».

2. С помощью запроса на обновление увеличить «Сумму» на 15 %.

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

4. С помощью запроса на удаление удалить записи о книгах за август.

5. Создать итоговые запросы:

- сумма по месяцам;

- количество покупателей по городам;

- количество проданных книг в июле а Киеве.

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

7. В таблице «Книги» добавить вычисляемое поле: разница между покупателями и проданными книгами в Киеве.

8. Создать 2 макроса для открытия и закрытия любого объекта базы данных.