12. Закрыть отчет.

144

Упражнение № 14. Вставка графических объектов в БД

Графический объект можно вставлять (или осуществлять с ним связь) в форму или отчет как свободный рисунок Также графический объект может быть вставлен в форму как объект типа OLE

Вставить картинки людей из коллекции Clipart студентов в таблицу Студент БД Деканат Если есть какие-либо фотоизображения людей, можно вставлять их, открыв в соответствующем приложении

1. Добавить в таблицу ФИЗИКИ поле Фото, тип поле объекта OLE
2. Запустить Графический редактор Paint через Главное_меню\Стандартные
3. Открыть любую картинку, выделить весь рисунок при помощи команды Правка\Выделитъ_все и занести в буфер при помощи команды Правка\Копировать
4. Перейти в Access и установить курсор на первую запись поля Фото
5. Выполнить команду Правка\Специальная_вставка
6. В появившемся окне изучить при помощи значка ? варианты команды Как
7. Выбрать тип Picture (Рисунок) В поле Фотография появится запись (Рисунок или Картинка )
8. Аналогичным образом вставить еще 2 - 3 рисунка
9. Закрыть таблицу
10. Открыть форму ФИЗИКИ Конструктора
11. Из пиктограммы Список_полей добавить поле Фото
12. Отредактировать его местоположение
13. Перейти в режим формы и просмотреть записи В них должны присутствовать изображения
14. В окне свойств поля Фото в строке Установка размеров выбрать Вписатъ_в_ рамку или По_размеру_рамки

145

135 :: 136 :: 137 :: 138 :: 139 :: 140 :: 141 :: 142 :: 143 :: 144 :: 145 :: Содержание

145 :: 146 :: 147 :: Содержание

Лабораторные работы

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

Создание БД, ввод и редактирование данных

Время выполнения 6 часов

Задание. Создайте базу данных "Студенческая группа"

145

Задание общее для всех студентов. Создайте структуру базы данных с полями ФИО, курс, номер группы, номер зачетной книжки, возраст, адрес жительства. Введите не менее 10 записей. Удалите выборочно две записи, а затем добавьте 4 новые записи.

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

Многотабличная БД, установление связей между таблицами

Время выполнения 6 часов.

Задание. Создайте базу данных "Музыкальный альбом" из двух таблиц. Установите связи между ними и проведите операции, подобные упражнению 1.

Задание общее для всех студентов. Создайте структуру двух таблиц КОМПОЗИТОР, ПЕВЦЫ. Самостоятельно придумайте поля этих двух таблиц, выделите ключевые поля, установите связи между таблицами. Заполните данными и проведите редактирование записей таблиц.

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

Управление, вычисляемые поля, запросы по образцу, графика

Время выполнения 8 часов.

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

Варианты заданий

• Вариант 1. Городской телефонный справочник.
• Вариант 2. Каталог программного обеспечения персонального компьютера.
• Вариант 3. Электронный алфавитный каталог библиотеки.
• Вариант 4. Электронный систематический каталог библиотеки.
• Вариант 5. Электронный алфавитно-систематический каталог домашней библиотеки.
• Вариант 6. Система "Деканат".
• Вариант 7. Система "Учебный план факультета информатики".
• Вариант 8. Система "Научные труды и методические разработки кафедры".
• Вариант 9. Система "Расписание занятий" (модель).
• Вариант 10. Система "Выпускники факультета математики и информатики".
• Вариант 11. Система "Биржатруда".
• Вариант 12. Система "Тестовые задания по школьному курсу информатики".
• Вариант 13. Система "Участники конференции".
• Вариант 14. Система "Кто есть кто: выдающиеся информатики России".
• Вариант 15. Система "Календарь проводимых мероприятий по информатике".
• Вариант 16. Система "Телеконференции по информатике и информационным технологиям".
• Вариант 17. Система "Белые страницы Интернет".
• Вариант 18. Система "Желтые страницы Интернет".
• Вариант 19. Система "Музыкальный альбом".
• Вариант 20. Система "Инвентарная книга факультета".
• Вариант 21. Система "Компьютерный салон".

146

• Вариант 22. Система "Художественная галерея".
• Вариант 23. Система "Склад товаров магазина "Детский мир;"".
• Вариант 24. Система "Склад продовольственных товаров мелкооптового магазина".
• Вариант 25. Система "Учет товаров оптовой базы промышленных товаров".

147

145 :: 146 :: 147 :: Содержание

147 :: Содержание

Дополнительная литература

1. ACCESS 7.0 для WINDOWS95. - Киев: Торгово-издательское бюро BHV, 1996.
2. Бекаревич Ю.Б., Пушкина H.B. СУБД ACCESS для WINDOWS95 в примерах. - СПб.: BHV - Санкт-Петербург, 1997.
3. Богумирский Б.С. Эффективная работа на IBM PC в среде Windows95. - СПб.: Питер-Пресс, 1997.
4. Вемпен Ф. Microsoft Office Professional: 6 книг в одной: Пер. с англ. - M.: Бином, 1977.
5. Джонс Э., Саттон Д. Библия пользователя Office97: Пер. с англ. - Киев: Диалектика, 1997.
6. Ефимова O., Морозов В., Шафрин Ю. Курс компьютерной технологии. - M.: ФБФ, 1998.
7. Ефимова O., Морозов В., Шафрин Ю. Практикум по компьютерной технологии. - M.: ФБФ, 1998.
8. Каратыгин C., Тихонов А., Долголаптев В. Базыданных: Простейшие средства обработки информации. Т. 1, 2. Серия "Компьютер для носорога". - M.: ФИА, 1995.
9. Крамм Р. Системы управления базами данных dBASE II и dBASE III для персональных компьютеров. - M.: Финансы и статистика, 1988.
10. Лядова Л.Н. Персональный компьютер: От начинающего пользователя до профессионала: В 2 т. - Пермь: ПГУ, 1998.
11. Макарова H.B. и др. Информатика. Практикум по технологии работы на компьютере. - M.: Финансы и статистика, 1998.
12. Назаров C.B., Першиков В.И. u др. Компьютерные технологии обработки информации. - M.: Финансы и статистика, 1995.
13. Смирное C.C. Программные средства персональных ЭВМ. - Л.: Машиностроение. Ленингр. отд-ние, 1990.

147

147 :: Содержание

147 :: Содержание

§ 7. ЭЛЕКТРОННЫЕ ТАБЛИЦЫ

Рекомендации по проведению занятий

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

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

147

147 :: Содержание

148 :: 149 :: 150 :: Содержание

Краткие сведения

Электронные таблицы Excel

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

	А	В	С	D	Е	F
1
2
3		Ячейка В3
4

Адрес ячейки определяется обычным координатным способом, например, ячейка В3, ячейка C5 и т.д.

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

Тексты и числовые константы, занесенные в ЭТ, сами по себе в ходе работы таблицы никогда не изменятся. Что же касается формул, то программа по специальной команде производит вычисление их значений и отображает его на экране (в той же ячейке, куда занесена формула).

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

Существует несколько программ такого класса. Ниже обсуждается одна из них, популярная в настоящее время программа Excel из пакета Microsoft Office, в которой реализованы многочисленные возможности и удобный и комфортный интерфейс.

Задаваемый обычным образом адрес ячейки называется относительным адресом или относительной ссылкой.

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

Группа ячеек (диапазон) задается через двоеточие, например, B3:D4 (или B3..D4), и образует прямоугольник, включающий ячейки В3, C3, D3, B4, C4, D4.

Ячейки могут содержать следующие типы данных:

1. Символьные (текстовые) данные. Могут включать в себя алфавитные, числовые и специальные символы. По умолчанию символьные данные при вводе выравниваются по правому краю ячейки. При необходимости их можно выровнять по левому краю или по центру.
2. Числовые данные. Могут содержать только числовые данные. Исключением являются десятичная точка (или запятая) и знак числа, стоящий перед ним. Числовые данные по умолчанию выравниваются при вводе по левому краю ячейки.

148

3. Формулы. Формула может включать ряд арифметических, логических и других действий, производимых с данными из других ячеек. На экране после ввода формулы отображается результат вычислений, а сама формула отображается в строке ввода над таблицей. Ввод формул начинается со знака равенства.
4. Функции. Функции могут самостоятельно присутствовать в ячейках, а также могут входить в состав формул. Различают математические, статистические, логические, финансовые и другие функции.
5. Даты. В этом типе данных возможны такие функции, как добавление к дате числа (пересчет даты вперед и назад) или вычитание разности двух дат (длительность периода).

Названия ссылок	Обозначения	Результат при копировании или переносе формул
Частичная абсолютная ссылка	$А5	Не меняется номер столбца
	А$5	Не меняется номер строки
Полная абсолютная ссылка	$A$5	Не меняется ни номер строки, ни номер столбца

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

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

Чтобы выполнить какое-либо действие с данными, помещенными в ячейки (ввод, копирование, удаление, форматирование и т.п.), необходимо их выделить. Чтобы выделить ячейку, укажите на нее и нажмите кнопку мыши. При нажатой кнопке можно выделить диапазон ячеек. После выделения необходимой области нажмите правую кнопку мыши, вызывая контекстное меню, которое позволяет выполнить ряд команд: Вырезать, Копировать, Вставить и т.п.

Изменение данных проводят прямо в ячейке. Перемещение или копирование содержимого ячеек можно осуществить перетаскиванием его с помощью мыши. Чтобы скопировать (а не переместить), держите нажатой клавишу CTRL.

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

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

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

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

149

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

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

Excel не только "дружен" с текстовыми и графическими системами, но и поддерживает основные действия, характерные для систем управления базами данных (СУБД). Более того, у него развит аппарат импортирования и экспортирования данных из других программных систем.

В Excel, кроме адресов ячеек, предусмотрен также удобный способ ссылки на ячейку с помощью присвоения этой ячейке произвольного имени. Это делается при выделенной ячейке с помощи меню Вставка\Имя\Присвоить... Ввести любое имя, например, Сумма. Введенное имя можно использовать в дальнейшем вместо адреса.

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

150

148 :: 149 :: 150 :: Содержание

150 :: Содержание

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

1. Что представляет собой электронная таблица?
2. Как формируется адрес ячейки?
3. Что называется диапазоном ячеек? Как он задается?
4. Чем различаются относительная и абсолютная ссылки?
5. Что означают частичная и полная относительная ссылки?
6. Какие типы данных встречаются в электронных таблицах?

150

150 :: Содержание

150 :: Содержание

Темы для рефератов

1. Работаем с QuattroPro.
2. Что мы знаем о Lotus 1, 2, 3.
3. Компьютерная графика в электронных таблицах.
4. Могут ли электронные таблицы заменить СУБД?
5. Программируем в электронных таблицах.

150

150 :: Содержание

150 :: Содержание

Тема семинарских занятий

Знакомство с технологией обработки числовых данных с помощью электронных таблиц. Основные сведения по работе с Excel.

150

150 :: Содержание

150 :: Содержание

Рекомендации по программному обеспечению

Могут быть использованы следующие программы:

1. SuperCalc-4,5.
2. Excel-95, 97, 98, 2000.
3. QuattroPro.
4. Lotus 1, 2, 3.

150

150 :: Содержание

151 :: 152 :: 153 :: 154 :: 155 :: 156 :: 157 :: 158 :: 159 :: 160 :: 161 :: 162 :: 163 :: Содержание

Задачи и упражнения

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

Упражнение № 1. Основные приемы работы с ЭТ:
ввод данных в ячейку, форматирование шрифта,
автозаполнение, ввод формул, обрамление таблицы

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

Формула п-го члена арифметической прогрессии: an = a1 + d(n - 1), где a1 - первый член прогрессии, d- разность арифметической прогрессии. Формула суммы n первых членов арифметической прогрессии: S = (a1 + аn)·n/2.

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

Составить таблицу:

	А	В	С	D
1	Вычисление n-го члена и суммы арифметической прогрессии
2
3	d	п	ап	Sn
4	0,725	1	-2	-2
5	0,725	2	-1,275	-3,275
6	0,725	3	-0,55	-3,825
7	0,725	4	0,175	-3,65
8	0,725	5	0,9	-2,75
9	0,725	6	1,625	-1,125
10	0,725	7	2,35	2,225
11	0,725	8	3,075	4,3
12	0,725	9	3,8	8,1
13	0,725	10	4,525	12,625

2. Выделить ячейку A1 (щелкнуть курсором мыши в нее) и ввести заголовок таблицы "Вычисление n-го члена и суммы арифметической прогрессии". Заголовок займет несколько ячеек правее A1. Введенные в ячейку данные фиксировать нажатием на клавишу выполнения (Enter).
3. Оформить строку заголовков столбцов. В ячейку А3 введите d, в B3 - n, в C3 - ап, в D3 - Sn. Размер шрифта 11 п., выравнивание по центру, применить полужирный стиль начертания символов. Для набора нижних индексов воспользоваться командой Формат\ячейки, выбрать вкладку Щрифт\Нижний_индекс.
4. Оформить заголовок таблицы. Выделить ячейки Al:D2, применить полужирное начертание символов и выполнить команду Формат\ячейки, вкладка Выравнивание - установить выравнивание по горизонтали "По центру", активизировать переключатели "Переносить по словам" и "Объединение ячеек".

151

5. В ячейку A4 ввести величину разности арифметической прогрессии.
6. Далее надо заполнить ряд нижних ячеек. Для этого в выделенной ячейке A4 установить курсор мыши в правый нижний угол ячейки. Когда курсор примет форму черного крестика (маркер заполнения), нажать на левую кнопку мыши и протянуть маркер заполнения на несколько ячеек вниз. Весь ряд заполнится данными, повторяющими данные ячейки A4.
7. В следующем столбце необходимо ввести последовательность чисел от 1 до 10. Для этого ввести в ячейку B4 число 1, в ячейку B5 число 2. Выделите обе ячейки и, взявшись за маркер заполнения, протяните его вниз.
8. Или: ввести в ячейку B4 число 1. Выделить блок, который надо заполнить. Выбрать команду Правка\Заполнитъ\Прогрессия. Выбрать необходимые параметры.
9. В ячейку C4 ввести значение первого члена арифметической прогрессии.
10. В остальные нижние ячейки надо ввести формулу n-го члена арифметической прогрессии. Выделите ячейку C5 и введите в нее формулу =C4 + A4 (все формулы начинаются со знака равенства!). Можно не набирать адреса ячеек при вводе формул, а просто в тот момент, когда надо в формуле набрать адрес ячейки, щелкнуть именно в эту ячейку.
11. Скопировать данную формулу в нижние строки данного столбца.
12. В данном примере разность арифметической прогрессии необязательно писать во всех строках, так как она везде одинакова. Достаточно ввести ее в ячейку A4. Тогда при вводе формулы в ячейку C5 необходимо применить абсолютную ссылку на ячейку A4. Внесите эти изменения.
13. Занести в ячейку D4 формулу суммы n первых членов арифметической профессии с учетом адресов ячеек = (С$4+С4)*В4/2. Заполните формулами нижние столбцы.
14. Выделить блок - ячейки с данными (кроме заголовков таблицы и столбцов) и установить необходимые параметры для шрифта, например, размер шрифта 11 п., выровнять вправо, используя меню Формат\ячейки, вкладки Выравнивание и Шрифт. Если какие-то данные в ячейках не помещаются, необходимо выделить блок ячеек с данными и выполнить автоподбор ширины ячеек через меню Формат\Столбец...\Автоподбор_ширины.
15. Если выбрать команду Файл\Просмотр, то наша таблица окажется не обрамленной. Для обрамления выделить таблицу (без заголовка), выбрать стиль линии (Формат\Ячейки... вкладка Рамка) и активизировать переключатели Сверху, Снизу, Слева, Справа.
16. Выполнить те же действия для обрамления заголовка, предварительно выделив блок ячеек заголовка.

Диаграммы, графики, условия, функции

Упражнение № 2. Нахождение наибольшего и наименьшего элементов в
числовой таблице

Например, имеется таблица:

	А	В	С	D
1	12	-14,5	23	42
2	36	17	9	-3,76
3	64	39	25	-1
4

152

1. Создать новую рабочую книгу.
2. Внести элементы данной таблицы в ячейки.
3. Установить курсор в ячейку C4, ввести запись "максимальное".
4. Перейти в ячейку D4, выполнить команду Вставка\Функция или щелкнуть на кнопку Вставка_фунщии на панели инструментов.
5. В появившемся диалоговом окне выбрать функции Статистические\МАКС.
6. В следующем диалоговом окне необходимо в строке Число1 ввести диапазон Al:D3. Для этого выделить его в таблице.
7. Аналогично пунктам 3 и 4 выполнить действия по нахождению минимального в строке 5.
8. Переименовать лист. Для этого выполнить команду Формат\Лист\Переименовать или выполнить двойной щелчок внизу на вкладке Лист1. Ввести имя МаксМин.

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

1. Перейти на Лист 2.
2. Протабулировать функцию y = 2·x2 - 4·x - 6 на отрезке [-5, 5] с шагом 1. Найти промежутки перемены знака значений функции. Определить корни уравнения. Должна получиться следующая таблица:

	А	В
1	X	Y
2	-5	64
3	-4	42
4	-3	24
5	-2	10
6	-1	0
7	0	6
8	1	-8
9	2	-6
10	3	0
11	4	10
12	5	24

3. Построить график функции на данном интервале. Для этого выполнить команду Вставка\Диаграмма\На_этом_листе или щелкнуть на панели инструментов кнопку Мастер_диаграмм. Далее выполнить шаги 1 -4.
4. Шаг 1: во вкладке Стандартные выбрать тип График и левый верхний вид.
5. Шаг 2: при активной вкладке Диапазон_данных выделить диапазон значений функции в таблице вместе с заголовком Cl:C12. При активной вкладке Ряд в строке Подписи_оси_Хактивнзировать курсор, затем выделить диапазон данных в таблице B2:B12.
6. Шаг 3: самостоятельно изучить все вкладки на этом этапе.
7. Шаг 4: указать местоположение полученной диаграммы (на новом листе или на этом).
8. Измените цвет и толщинулинии графика. Для этого подведите курсор мыши к линии графика и выполните двойной щелчок мышью. В появившемся окне Форматирование_ряда_данных выберите другой цвет и другую толщину линии, активизировав вкладку Вид.

153

9. Выполнив двойной щелчок мыши на линиях осей, измените цвет в появившемся диалоговом окне Форматирование осей.
10. Дайте имя Листу 2 "Функция" или "График".

Упражнение № 4.

Условия в электронных таблицах

Вычислить значения функции в зависимости от значений аргумента на интервале [-5, 5] с шагом 1.

y = {	x · x - 4	x + 5
	для x < 0,	для х > 0 или х = 0.

Должна получиться следующая таблица:

	А	В
1	X	Y
2	-5	=ЕСЛИ(А2<0;А2*А2-4;А2+5)
3	-4
4	-3
5	-2
6	-1
7	0
8	1
9	2
10	3
11	4
12	5

1. Перейти на Лист 2.
2. В ячейки столбца, озаглавленного буквой X, внести значения от -5 до 5 с шагом 1.
3. Прочитать справку в Excel o логических функциях. Для этого выбрать в меню Excel ? \Вызов_справки. Во вкладке Предметный_указатель в строке поиска ввести текст логические функции.
4. В первую строку значений Y ввести логическую функцию Если, используя Мастер функций.
5. Скопировать формулу в нижние ячейки.
6. Построить график. Определить, при каких значениях Х функция Y принимает значение нуль.

Упражнение № 5. Нахождение корня уравнения методом
последовательных приближений

Напомним, что метод последовательных приближений при решении уравнения x = (х) заключается в построении итерационной последовательности xn+1 = (xn). Если функция (х) является сжимающей, то последовательность {хп} сходится к корню уравнения.

154

	A	B	C	D
1	Метод последовательных приближений
2	Номер приближения	Приближение	Значение X	Отклонение
3	1	1	1,393	0,393
4	2	1,393	1,474	0,081
5	3	1,474	1,487	0,013
6	4	1,487	1,489	0,002
7	5	1,489	1,490	0,001
8	6	1,490	1,490	0,000

Продемонстрируем процесс решения на примере уравнения x = 1 + 0,5 arctg(x).

1. Перейти на Лист 3
2. Ввести данные (константы) в ячейки A3:A7.
3. В ячейку B3 ввести значение 1.
4. В ячейку C3 ввести формулу 1 + 0,5*ATAN($B3), для ввода функции arctg использовать Мастер_функций.
5. В ячейку D3 ввести абсолютную разность ячеек B3 и C3, применив для них абсолютную адресацию столбцов. Для ввода функции использовать Мастер_функций.
6. В ячейку B4 ввести содержимое ячейки C3, используя абсолютный адрес столбца: =$C3.
7. Скопировать все формулы вниз до тех пор, пока в колонке Отклонение не появится нуль.
8. Дать имя листу "Приближение".

Упражнение № 6. Решение квадратного уравнения

Решить квадратное уравнение у = ax2+ bx + c, используя ЭТ. Оформить заголовок. Выполнить решение для нескольких наборов коэффициентов:

1) а = 2, в = -3, с = -2; 2) а = 2, в = -4, с = -6; 3) а = -3, в = 1, с = 1.

Упражнение № 7. Использовать ЭТ для решения математических,
физических, экономических и других прикладных задач

Каждый студент предлагает свою задачу.

Структурирование и отбор данных в ЭТ Упражнение

№ 8. Сортировка (упорядочение) записей списка

Рассмотрим заданную таблицу УЧЕТ ТОВАРОВ НА СКЛАДЕ, представленную ниже.

Прокомментируем эту таблицу. Таблица имеет вид базы данных, состоящей из записей продажи товаров со склада. Запись указывает, какой организации продан товар, когда проведена продажа, товар, единицу измерения товара, его стоимость и количество. В столбцах Дебет и Кредит заносится стоимость покупки и долг перед организацией, т.е. Цена*Кол-во. В последнем столбце указывается форма оплаты: безналичный расчет (б/р), бартер (бар), наличный расчет (н/р).

Для дальнейшей работы создать эту таблицу в Excel на Листе! и сохранить ее в виде отдельного файла.

155

Учет товаров на складе

	A	В	С	D	Е	F	G	Н	I	J
1	Организация	Дата	Товар	Ед. изм.	Цена	Кол-во 1	Дебет	Кол-во 2	Кредит	Ф. опл.
2	АО "Альянс"	1 Янв	соль	кг	15000	550	8250000			б/р
3	АОЗТ "Белокуриха"	1 Янв	сахар	кг	16000	200	3200000			б/р
4	АОЗТ "Белокуриха"	3 Янв	хлеб	бул	700		0	900	630000	бар
5	Бийск.маслосырзавод	3 Июн	сода	пач	5500	300	1650000			б/р
6	АОЗТ "Белокуриха"	4 Янв	сок	бан	56	26000	1456000			б/р
7	к/з "ЗАРЯ"	4 Янв	пиломт	метр			0			б/р
8	АО "Альянс"	13 Янв	лимоны	кг	4000	50	200000			б/р
9	АО "Альянс"	3 Фев	компьют	шт	250000	2	5000000			б/р
10	АОЗТ "Белокуриха"	12 Фев	хлеб	бул	700		0	500	350000	б/р
11	Бийск.маслосырзавод	12 Фев	бензин	л	450		0	6048	2721600	н/Р
12	АОЗТ "Белокуриха"	2 Map	хлеб	бул	3000	215	645000			б/р
13	к/з "ВОСТОК"	2 Map	апельсин	кг	4000	100	400000			б/р
14	к/з "ЗАРЯ"	5 Map	апельсин	кг	2300	124	285200			н/Р
15	к/з "ЛУЧ"	4 Апр	апельсин	кг	5000		0	50	250000	б/р
16	к/з "ЗАРЯ"	6 Апр	мука	кг	20000	1000	2000000			н/р
17	к/з "ВОСТОК"	6 Май	сахар	кг	16000	50	800000			б/р
18	к/з "ВОСТОК"	13 Июн	лимоны	кг	6000		0	50	300000	б/р
19	к/з "ВОСТОК"	13 Июн	хлеб	бул	700	300	210000			б/р

156

156

1. Для упорядочения записей необходимо определить, по каким полям вы хотите отсортировать таблицу. Например, необходимо отсортировать наименования организаций в алфавитном порядке, внутри каждой организации наименование товара в алфавитном порядке и внутри каждого наименования товара отсортировать по возрастанию количество проданного товара.
2. Курсор установить в область таблицы, выполнить команду Данные\Сортировка. В первом уровне сортировки выбрать поле Организация, во втором - Товар, в третьем - Кол-во1.
3. Просмотреть результаты сортировки.

Упражнение № 9. Фильтрация (выборка) записей списка

Автофильтр:

1. Скопируйте таблицу с Листа1 на Лист2 и назовите новый лист Автофильтр.
2. Допустим, нам необходимо выбрать из заданного списка только те строки, где есть запись АОЗТ "Белокуриха". Выполнить команду Данные\Фильтр\Автофильтр.
3. В строке заголовка таблицы появились значки падающего меню. (Чтобы их убрать, необходимо выполнить ту же команду, по которой их вызывали.)
4. Щелкнуть на значок в столбце Организация и выбрать АОЗТ "Белокуриха". Появились только те записи, где присутствует указанная организация. Чтобы вернуть все записи, надо опять щелкнуть на значок и выбрать строку Все.
5. Вывести на экран записи, содержащие организацию АОЗТ "Белокуриха", где в столбце "Товар" присутствует "хлеб", т.е. осуществить выборку по двум полям. Вернуть все записи.
6. Вывести на экран записи, содержащие организацию АОЗТ "Белокуриха", в которых цена товара не превышает 16000. Для выборки по столбцу "Цена" при открытии меню выбрать строку Условие. В появившемся окне Полъзователъский_автофилътр при помощи значков открывающегося меню установить условие <16000 в верхней строке. Вернуть все записи.
7. Вывести на экран записи, содержащие колхоз "Восток" и дату покупки товара в промежутке после 2 марта до 13 июня. В данном случае в окне Полъзовательский_автофилътр заполнить обе строки. Правильно выбрать соединение условий И или ИЛИ. Вернуть все записи.
8. Вывести на экран записи, содержащие колхоз "Восток", а в поле Цена установить условие: больше 700, но меньше 16000. Вернуть все записи.

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

Прочитать встроенную справку Excel Фильтры\Расширенные:_Примеры_условий_omбopa_расширенного_фильтра.

1. Скопируйте таблицу с Листа 1 на Лист 3 и дайте имя листу Расширенный фильтр.
2. Выполнить задание по автофильтру, воспользовавшись командой Расширенный_фильтр.

• Ниже таблицы, оставив пустые 2 - 3 строки, скопировать строку заголовка таблицы, например, в строку 23. В строке 24 сформировать критерий отбора записей. В столбец Организация ввести АОЗТ "Белокуриха", в столбец Товар - "хлеб". Установить курсор в область таблицы, в которой будет производиться выборка данных.
• "Далее выполнить команду Данные\Фильтр\Расширенный_фильтр. В появившемся диалоговом окне в строке Исходный диапазон появится запись Al:J19.
• В диалоговом окне установить курсор в строку Диапазон условий, перейти в таблицу и выделить диапазон A23:J24.

157

• Для того чтобы новые данные печатались в другом месте, необходимо активизировать кнопку Скопировать_результат_в_другое_место и также указать диапазон, куда будут выводиться отсортированные данные, например, $A$26:$J$36.

Начиная со строки 26 и ниже расположен результат выполнения расширенного фильтра.

3. Выполнить задание п. 6 из упражнения. В данном случае для выборки товаров, стоимость которых не превышает 16000, в диапазоне критериев введите "<16000".

Примечание. Алгоритм выполнения расширенного списка можно прочитать в справке Excel Фильтры \Расширенные:_ Фильтрация_списка_с_помощъю_расширенного_фильтра.

158

Упражнение № 10. Автоматическое подведение итогов

1. Скопируйте таблицу с Листа 1 на Лист 4.
2. Предположим, необходимо подвести итоги о продаже товаров каждой организации, затем еще итоги в каждой организации по датам.
3. Сначала необходимо упорядочить таблицу по полю Организация, второй уровень сортировки - Дата.
4. Затем выполнить команду Данные\Итоги. В появившемся окне в первой строке выбрать Организация, в строке Операция из списка выбрать Сумма, в третьей строке выбрать поля, по которым подводить итоги: Дебет и Кредит.
5. Просмотреть результаты. В левой половине экрана появились символы структуры (значки "плюс" и "минус"). Пощелкать на них и ознакомиться с их назначением.
6. Далее вновь выполнить команду подведения итогов. Вводим поле Дата. Чтобы предыдущие итоги не стерлись, значок Заменять текущие итоги должен быть выключен. В результате получим таблицу итогов о продаже товара каждой организации и вдобавок еще итоговые данные по датам продажи.
7. Отмените все итоги. Чтобы убрать итоги, вызвать вновь окно Итоги и выбрать команду Убрать_все.

Упражнение № 11. Консолидация данных
(способ получения итоговой информации из разных листов,
одинаковых по структуре)

1. Предположим, есть три таблицы одинаковой структуры УЧЕТ ПРОДАЖИ ТОВАРА одной фирмы, имеющей три склада в разных точках города. Создадим эти три таблицы. Для этого надо скопировать таблицу с Листа 1 на Листы 4, 5 и 6. Дать имена Листам 4, 5, 6 Склад 1, Склад 2, Склад 3 соответственно.
2. Изменить некоторые данные на Листах 5 и 6 для организации АОЗТ "Белокуриха" для товара хлеб, например, Количество!, Количество 2 или Цену.
3. Допустим, необходимо подвести итоги о продаже хлеба организации АОЗТ "Белокуриха" в сумме в этих трех точках.
4. Для этого все три таблицы должны быть упорядочены по полю Организация, внутри каждой организации упорядочены по полю Товар. Подвести итоги по полю Товар, суммирующие значения по полям Дебет и Кредит.
5. Дать имя Листу 7 Консолидация. Находясь в этом листе, выполнить команду Данные\Консолидация.
6. В появившемся диалоговом окне выбрать функцию Сумма.
7. Щелкнуть мышью в поле Ссылки, перейти на лист Склад 1 и выделить итоговую сумму продажи сахара АОЗТ "Белокуриха". Данные появятся в поле ссылки. Нажать кнопку Добавить.
8. To же самое выполнить для листов "Февраль" и "Март".
9. Указать флажок Создавать_связи_с_исходными_данными. Тогда, если будут меняться исходные таблицы, автоматически будет пересчитываться и суммирующая таблица.
10. Просмотреть полученную таблицу.

Упражнение № 12. Сводные таблицы

Команда Данные\Сводная_таблица вызывает Мастера_сводных_таблиц для построения сводов, т.е. итогов определенных видов на основании данных списков,

159

других сводных таблиц, внешних баз данных Сводная таблица обеспечивает различные способы агрегирования информации

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

1. Скопировать таблицу с Листа 1 на Лист 8
2. Выполнить команду Данные\Сводная_таблица
3. Выполнить первые два шага работы с Мастером самостоятельно
4. На третьем шаге перетащить значки с названиями столбцов нашей таблицы следующим образом

Информация, которую мы хотим разместить в строках, например

• Организация Схватить мышью значок и перетащить в область Строка
• Перенести значок Дебет в область Данные

5. Если выполнить двойной щелчок на перенесенных значках, можно редактировать их назначение На значках, помещенных в окне Строка, активизировано состояние строки, в окне Столбец - столбца Двойной щелчок на значке в области данных позволяет выполнить операцию

6. Просмотреть полученную таблицу

160

7. Программа предлагает создать на новом листе сводную таблицу или создать на нем существующую таблицу
8. Установить курсор в область сводной таблицы и выполнить команду Данные\ Сводная_таблица
9. Перенести поле Организация на область Столбец, а поле Товар на область Строка

10. Просмотреть полученную таблицу

11. Можно менять группировку строк и столбцов следующим образом в полученной таблице поле Организация перенести влево Ваша таблица примет другой вид В конце таблицы - общая сумма

161

12. Если нужна группировка не по товарам, а по организациям, поле Товар перетащить вправо.

Фильтрация сведенных данных:

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

162

Предположим, нам необходима сводная таблица о продажах по всем организациям по месяцам.

15. Изменим сводную таблицу. Курсор должен находиться в области таблицы. Выполнить команду Данные\Сводная_таблица. Установить значок Организация в область строк, а значок Дата в область Столбец. В поле Данные оставить значок Сумма по полю Дебет.
16. Просмотреть полученную таблицу.
17. Преобразуем полученную таблицу по месяцам. Для этого установить курсор на ячейку с записью Дата и выбрать пункт меню Данные\ Группа и структура\ Группировать.

Упражнение № 13. Структурирование таблиц

Применяется для работы с большими таблицами, если есть необходимость закрывать и открывать отдельные строки таблицы.

1. Отсортировать строки списка по нужной вам классификации, например, по организациям или по наименованию товара.
2. Вставить пустые строки для разделенных групп.
3. Выделить первую группу, выполнить команду Данные\Группа и структура\Группировать.
4. Аналогичные действия выполнить для последующих групп.
5. Слева на экране появится значок "-". При щелчке на этот значок данные скрываются.
6. Для отмены группировки необходимо выделить группы и выполнить команду Данные\Группа и структура\Разгруппировать.
7. Отменить все группировки.

163

151 :: 152 :: 153 :: 154 :: 155 :: 156 :: 157 :: 158 :: 159 :: 160 :: 161 :: 162 :: 163 :: Содержание

163 :: 164 :: 165 :: 166 :: Содержание

Лабораторные работы

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

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

Время выполнения 2 часа.

Задание. Составьте таблицу значений функции двух переменных F(x, у), заданной в прямоугольной области [a, b] [с, d], для аргументов xi = а + ihx, yj = с + jhy, где i = 0..Ny, j = 0..Ny, (hx = (b - a)/Nx, hy = (d - c)/Ny).

Варианты заданий

№	F(x,y)	а	b	с	d	hx	hy
1	ху + 5,6(x + у)	0	1	0	1	0,1	0,1
2	ln(x + y)	1	3	0	2	0,2	0,2
3	cos(x) + sin(y)	0	1	0	1	0,1	0,1
4	sin(x) + cos(y)	0	1	0	1	0,1	0,1
5	tg(x + y)	1	3	0	2	0,2	0,2
6	sin(x) + xy	0	1	0	1	0,1	0,1
7	cos(x) + 5xy	0	1	0	1	0,1	0,1
8	y + tg (x + у)	1	3	0	2	0,2	0,2
9	sin(xy) + cos(xy)	0	1	0	1	0,1	0,1

163

Окончание таблицы

№	F(x,y)	а	b	с	d	hx	hy
10	5sin(cos(x + у) + 3,78)	0	1	0	1	0,1	0,1
11	6,4cos(5,8 + ln(xy))	1	3	0	2	0,2	0,2
12	ху + х2 + у2	0	1	0	1	0,1	0,1
13	sin2(x + у) + cos2(x + у)	0	1	0	1	0,1	0,1
14	xsin(y) + xcos(y)	1	3	0	2	0,2	0,2
15	10xysin((xy) + cos(y))	0	1	0	1	0,1	0,1
16	xy - 5,6(x - y)	0	1	0	1	0,1	0,1
17	xyln(x + y)	1	3	0	2	0,2	0,2
18	xy(cos(x) + sin(y))	0	1	0	1	0,1	0,1
19	x(sin(x) + cos(y))	0	1	0	1	0,1	0,1
20	xytg(x + у)	1	3	0	2	0,2	0,2
21	sin(x) + xy - 2	0	1	0	1	0,1	0,1
22	cos(x) + 5xy - sin(y)	0	1	0	1	0,1	0,1
23	yx + tg (x + у) - 4	1	3	0	2	0,2	0,2
24	xy(sin(xy) + cos (xy))	0	1	0	1	0,1	0,1
25	ху sin(cos(x + у) + 3,78)	0	1	0	1	0,1	0,1

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

Диаграммы, графики, условия, функции

Время выполнения 4 часа.

Задание. С помощью ЭТ найдите приближенное значение одного из корней уравнения F(x) = 0 заданным методом:

• а) деления отрезка пополам; б) хорд;
• в) касательных; г) простой итерации.

Для каждого из методов сделать 10 итераций. Составить таблицу приближений для каждого шага итераций. Проверить точность каждого приближения подстановкой в заданное уравнение.

Построить график функции вблизи корня.

Варианты заданий

№	F(x)	Метод	№	F(x)	Метод
1	x·x - 5,6·x + ln(x)	a)	13	cos(x) +sin(x·x) -0,5	a)
2	x·x - 5,6·x + ln(x)	б)	14	cos(x) +sin(x·x) -0,5	б)
3	x·x - 5,6·x + ln(x)	в)	15	cos(x) + sin(x·x) - 0,5	в)
4	x·x - 5,6·x + ln(x)	г)	16	cos(x) + sin(x·x) - 0,5	г)
5	ln(x + 1,13) - x	a)	17	sin(x) + x·x	a)
6	ln(x + 1,13) - x	б)	18	sin(x) + x·x	б)
7	ln(x + 1,13) - x	в)	19	sin(x) + x·x	в)
8	ln(x + 1,13) - x	г)	20	sin(x) + x·x	г)
9	1/x + x·x - sin(x)	a)	21	log(x) - l/x + 2,7	a)
10	1/x + x·x - sin(x)	б)	22	log(x) - l/x + 2,7	б)
11	1/x + x·x - sin(x)	в)	23	log(x) - l/x + 2,7	в)
12	1/x + x·x - sin(x)	г)	24	log(x) - l/x + 2,7	г)