Практическое использование возможностей MS WORD и EXCEL

Министерство РФ по связи и информатизации ПОВОЛЖСКАЯ ГОСУДАРСТВЕННАЯ АКАДЕМИЯ ТЕЛЕКОММУНИКАЦИЙ И ИНФОРМАТИКИ

ЗАДАНИЯ И МЕТОДИЧЕСКИЕ УКАЗАНИЯ к курсовой работе по дисциплине «ИНФОРМАТИКА» для специальностей 200900, 201000, 201100 ( дневного и заочного отделений ) и направления 550400 «Практическое использование возможностей MS WORD и EXCEL»

Одобрено Методическим Советом ПГАТИ «___»__________ 2004г.

Авторы-составители:

КАДАКОВ Д.А., ст. преп. каф. ВТ, СИРАНТ О.В., ст. преп. каф. ВТ, СТЕФАНОВА И.А., ст. преп. каф. ВТ.

Редактор:

ЛАЗАРЕВ В.А., к.т.н., профессор

Рецензент:

КАМЫШНИКОВ В.В., к.т.н., доцент

Самара 2004

2

Содержание

Введение_______________________________________________ 3 1. Цели и задачи курсовой работы ________________________ 4 2. Постановка задачи курсовой работы. ___________________ 4 3. Выбор варианта______________________________________ 4 4. Задание на выполнение________________________________ 4 5. Требования к оформлению курсовой работы _____________ 8 6. Вопросы и задания к защите__________________________ 10 7. Пример выполнения курсовой работы__________________ 12 ПРИЛОЖЕНИЕ 1. Исходные данные к курсовой работе____ 17 ПРИЛОЖЕНИЕ 2. Табличный процессор EXCEL__________ 21

3 Введение Методическая разработка предназначена для использования в качестве пособия при выполнении курсовой работы «Практическое использование возможностей MS WORD и EXCEL» и способствует приобретению практических навыков работы с операционной системой Windows и компонентов пакета Microsoft Office Word и Excel. Методическая разработка рекомендована для использования на практических занятиях по дисциплине «Информатика» для специальностей 200900, 201000, 20110 (дневного и заочного отделений) и направления 550400. Рекомендуемая литература 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12.

Информатика. Базовый курс / Симонович и др. – СПб: «Питер», 2000. Берлинер Э.М., Глазырин Б.Э., Глазырина И.Б. Офис от Microsoft.М.: ABF, 1997. Эд Ботт. Microsoft Office для Windows 95 (серия «Без проблем!»): Пер. с англ. Под ред. С. Молявко - М.: БИНОМ, 1997. Шафрин Ю. Основы компьютерной технологии. Учебное пособие. 2-е издание дополненное и переработанное - М.: ABF, 1998. Ахметов К.С. Windows 95. 3-е издание переработанное и дополненное. - М.: Компьютер пресс. 1997. Симонович С. Windows 98. Учебный курс. - СПБ.: Питер Ком, 1999. Денисов В. Word 97 с самого начала. - СПБ.: Питер, 1997. Дж. Кокс и др. Microsoft Excel 97. Краткий курс. Пособие ускоренного обучения - СПБ.: Питер, 1998. Электронно-методическое пособие «Word 97». Электронно-методическое пособие «Excel 97». Конспект лекций по дисциплине «Информатика». Алексеев А.П., Камышенков Г.Е. Использование ЭВМ для математических расчетов. Самара: ПГАТИ, 1998.

4 1.

Цели и задачи курсовой работы

Изучение операционной системы Windows, компонентов MS Word и Excel и получение практических навыков работы с современными информационными технологиями. Получение представления о формировании табличной базы данных и о возможностях при работе с ней на примере базы данных в MS Excel. 2.

Постановка задачи курсовой работы

• В MS Excel создать базу данных табличного типа для двух объектов (фирм, типов оборудования, конструкций и т.д.). • Отсортировать базу данных согласно задания. • Рассчитать требуемые показатели. • Спрогнозировать характер изменения объёма продажи оборудования на последующие шесть месяцев. • Создать визитную карточку любым графическим средством для Windows. 3.

Выбор варианта

Для выбора варианта задания необходимо рассчитать код MNβ, а затем по соответствующим таблицам задания произвести выбор исходных данных. Два сравниваемых объекта выбираются по M - последней цифре зачётной книжки и данным таблицы, приведённой в ПРИЛОЖЕНИИ 1. Стоимость анализируемого оборудования выбирается с учётом предпоследней цифры N зачётной книжки студента. Обработка базы данных производится по цифрам M, N и β, где β— это сумма последней (M) и предпоследней (N) цифр зачётной книжки с отбрасыванием разряда десятков. 4.

Задание на выполнение

4.1. Сформировать на Листе 1 базу данных (табл.1) для 2-х видов оборудования в виде единой таблицы, начинающейся с 10 строки: • Столбцы А, B и С заполнить согласно заданного варианта M. • Данные ячеек столбца D получить путем увеличения содержимого соответствующих ячеек столбца С (Цена у.е.) на величину случайного числа, которое задается в диапазоне от 0,1*N до 0,5*N, ( при N=0 цена увеличивается на величину в диапазоне от 0 до 1)∗. • Полученные данные столбца D скопировать в соседний столбец E, используя команду «специальная вставка» и флаг «значения». • Столбец D скрыть. ∗

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

5 • Информацию о курсе 1 у.е. в рублях указать в ячейках А1-А2. (Курс определяется на момент выполнения задания). Присвоить ячейке А2 имя «Курс». • В столбце F (Цена в руб.) рассчитать стоимость оборудования в рублях. • Отсортировать базу данных по возрастанию стоимости оборудования для чётного β и по убыванию – для нечётного β. 4.2. Используя функции Excel, по данным табл. 1. определить минимальную и максимальную цену оборудования в рублях, среднее значение и количество оборудования по сравниваемым объектам. Результаты полученных значений представить таблицей 2 на листе 2. Таблица 2 Цена оборудования (руб.) Наименование объекта МИН МАКС СРЗНАЧ Количество Объект 1 Объект 2 4.3. Используя функции работы с базой данных, определить и вывести стоимость и название оборудования 1-го вида по условию K (выбирается из табл. 3). Таблица 3 Параметр К ( по стоимости в руб.) β 0 Оборудование, имеющее наименьшее отклонение от среднего значения стоимости объектов 1-го вида 1 Оборудование, имеющее наибольшее отклонение от минимального значения стоимости объектов 2-го вида 2 Оборудование, имеющее наименьшее отклонение от максимального значения стоимости объектов 1-го вида 3 Оборудование, имеющее наибольшее отклонение от среднего значения стоимости объекта 2-го вида 4 Оборудование, имеющее наименьшее отклонение от минимального значения стоимости объектов 2-го вида 5 Оборудование, имеющее наименьшее отклонение от максимального значения стоимости объектов 2-го вида 6 Оборудование, имеющее наибольшее отклонение от максимального значения стоимости объектов 2-го вида 7 Оборудование, имеющее наименьшее отклонение от минимального значения стоимости объектов 1-го вида 8 Оборудование, имеющее наибольшее отклонение от среднего значения стоимости объектов 1-го вида 9 Оборудование, имеющее наименьшее отклонение от среднего значения стоимости объектов 2-го вида Примечание. Следует исключить совпадение стоимости по условию К с минимальным и максимальным значениями объектов 1-го вида, для этого следует при выборе объектов создать критерий для функции выбора.

6 4.4. Провести анализ характера изменения объёма и стоимости помесячной продажи оборудования в пределах 6 месяцев, предшествующих моменту заполнения базы данных. Для этого на Листе 3 составить новую базу данных (табл. 4) по трем наименованиям оборудования: с минимальной и максимальной стоимостью объекта 1, а также по условию К. Таблица 4 Наименование Наименование обору- Наименование оборуоборудования по дования по МАКС дования по условию. К Месяц ОбъёмМИН Стоимость Объём Стоимость Объём Стоимость продаж (руб.) продаж (руб.) продаж (руб.) (шт.) (шт.) (шт.) мес. год ... Столбец «объём продаж» табл. 4 заполняется по месяцам полугодия путем копирования из вспомогательного столбца только значений случайных чисел. Случайные числа берутся в диапазоне NM ÷ 1NM для оборудования с максимальной стоимостью, в диапазоне NM ÷ 2NM – для оборудования со стоимостью по условию К и в диапазоне NM ÷ 3NM - для оборудования с минимальной стоимостью. Цены оборудования, необходимые для вычисления данных столбца «стоимость», берутся из предыдущих вычислений. 4.6. Используя данные табл. 4, построить диаграмму для двух видов оборудования, выбранных из табл. 5. Таблица 5 Вариант M Виды оборудования 0, 3, 6, 9 По максимальной и минимальной стоимости 1, 4, 7 По максимальной стоимости и по условию К 2, 5, 8 По минимальной стоимости и по условию К Вид диаграммы выбирается из соображений наглядности представляемой информации. На диаграмме отразить ее название, название осей, легенду, надпись (наименование оборудования). Диаграмму разместить под табл. 4. 4.7. Для каждого из видов оборудования спрогнозировать в табл. 4 объем помесячной его продажи за 6 последующих месяцев. Для прогноза использовать функцию ТЕНДЕНЦИЯ(), РОСТ(), ПРОГРЕССИЯ для разных видов оборудования. Для двух видов оборудования на отдельном листе построить диаграмму, отражающую характер изменения стоимости их помесячной продажи за год. В эту диаграмму добавить соответствующие линии тренда. Для обоснования выбора тренда поместить на диаграмму величину достоверности аппроксимации и закон аппроксимирующей кривой.

7 4.8. Используя данные табл. 4 и соответствующие функции базы данных, решить задачу, приведённую в табл. 6. Таблица 6 Искомый параметр β 0 Подсчитать суммарную стоимость оборудования, выбранного по условию К, за месяцы, в которые объём продаж не превышал 1NM. 1 Вычислить среднюю стоимость оборудования, выбранного по минимальной стоимости, за месяцы, в которые объём продаж находится в пределах от 1NM до 2NM. 2 Рассчитать количество оборудования, выбранного по максимальной стоимости, проданного за месяцы, в которые стоимость продаж была больше произведения 1NM*(«стоимость единицы оборудования»)* 0,5. 3 Подсчитать суммарную стоимость оборудования, выбранного по условию К, за месяцы, в которые объём продаж был больше 1NM. 4 Вычислить среднюю стоимость оборудования, выбранного по максимальной стоимости, за месяцы, в которые объём продаж был меньше 1NM . 5 Рассчитать количество оборудования, выбранного по условию К, за месяцы, в которые стоимость продаж была менее произведения 1NM*(«стоимость единицы оборудования»). 6 Подсчитать суммарную стоимость оборудования, выбранного по минимальной стоимости, за месяцы, объём продаж в которые находится в пределах от 1NM до 2NM. 7 Вычислить среднюю стоимость оборудования, выбранного по условию К, за месяцы, в которые объём продаж был больше 1NM. 8 Рассчитать количество оборудования, выбранного по минимальной стоимости, за месяцы стоимость продаж в которые была меньше произведения 2NM*(«стоимость единицы оборудования»). 9 Подсчитать суммарную стоимость оборудования, выбранного по максимальной стоимости, за месяцы, в которые объём продаж был менее 0,5*1NM. 4.9. Используя данные табл. 4, вывести с помощью соответствующей функции БД месяц с наибольшей суммой продажи для оборудования по условию К. 4.10. Оформить работу в текстовом процессоре Word. 4.11. Используя приложения MS Office, создать визитную карточку с обязательной вставкой графического объекта. Отдельные элементы визитной карточки должны быть сгруппированы в единый объект. 4.12. Используя возможности MS Word, вставить оглавление.

8 5.

Требования к оформлению курсовой работы

5.1. Пояснительную записку оформить в текстовом процессоре MS Word в печатном виде. Результаты выполнения заданий: все таблицы Excel и пояснительную записку представить в электронном виде (на одной дискете допускаются результаты нескольких работ). 5.2. Работа должна содержать цель работы, выбор варианта 5.3. Оформление работы производить в соответствии со следующими указаниями: • Пояснительная записка выполняется на листах формата А4, которые должны быть сброшюрованы в виде папки. • Обложкой курсовой работы служит титульный лист, форма которого приведена на рис. 1. • Текст пояснительной записки располагается на одной стороне листа и выполняется шрифтом Times New Roman размером в 14 пикселей, стиль – обычный, межстрочный интервал – одинарный. Весь текст оформить единообразным стилем. Предусмотреть автоматический перенос слов и выравнивание текста по ширине, проверку орфографии. • Страницы имеют следующие параметры (поля): слева – 25 мм, справа – 10 мм, сверху и снизу – по 20 мм. • Все страницы, за исключением титульного листа (номер 1), нумеруются сверху в центре в следующем порядке: страница 2 – рецензия на КР (оставляется пустой), страница 3 – оглавление с обязательным указанием номеров страниц разделов, страница 4 и т.д. – содержательная часть КР. В конце пояснительной записки приводится список используемой литературы, оформленный в соответствии с правилами ЕСКД. • Рисунки выполняются в текстовом процессоре Word. Таблицы выполнить копированием из табличного процессора Excel, шрифт в таблицах допускается 10 пт. Все рисунки и таблицы должны иметь соответствующую сквозную нумерацию, название и при необходимости пояснительный текст. 5.4. Представить все таблицы в двух вариантах: с формулами, по которым происходят вычисления, и со значениями, вычисленными по этим формулам. Таблицы дополнить адресами столбцов и строк. 5.5. Внести в нижний колонтитул пояснительной записки: свой вариант (например M=0; N=6; β=6), фамилию и номер группы. Пояснительная записка должна содержать подробное описание выполнения каждого пункта задания, включая пояснения к формулам и функциям (как вводятся, копируются, назначение аргументов и т.п.). 5.3. Курсовая работа подписывается студентом на титульном листе.

9 Министерство РФ по связи и информатизации Поволжская государственная академия телекоммуникаций и информатики Кафедра «ВЫЧИСЛИТЕЛЬНАЯ ТЕХНИКА» Сдана на проверку

Допустить к защите

«_____»___________ 200_ г.

«_____»___________200_ г. Защищена с оценкой __________ «_____»___________200_ г.

КУРСОВАЯ РАБОТА ПО ИНФОРМАТИКЕ «ПРАКТИЧЕСКОЕ ИСПОЛЬЗОВАНИЕ ВОЗМОЖНОСТЕЙ MS WORD И EXCEL»

Пояснительная записка на ____листах

Студент (ка) группы ___ (роспись)__________________ (Фамилия И. О.) Руководитель ___(роспись)_______________________ (Фамилия И. О.) № зач.книжки _____________

Самара 200_ г.

Рис. 1. Образец оформления титульного листа

10 6. 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. 20. 21. 22. 23. 24. 25. 26. 27. 28. 29. 30. 31.

Вопросы и задания к защите

Основные понятия базы данных (БД). Виды моделей данных: иерархическая, сетевая, реляционная. Структурные элементы БД: поля, записи, таблица. Как создаётся БД в Excel? К какому типу моделей она относится? Какие средства для ввода данных в БД вы знаете? Как произвести сортировку данных по заданным критериям? Как осуществить фильтрацию данных? Дать понятие критерия БД и пояснить принцип его формирования. Уметь вычислить функции БД, приведённые в таблице 6 задания на проектирование. Как указать абсолютный адрес ячейки? В каких случаях необходимо использовать абсолютный адрес? Как задать имя ячейки и какие символы могут входить в это имя? В каких ситуациях удобно обращаться к имени ячейки? Какие средства автоматизации ввода данных содержит программа Excel? Что такое функция, её типы? Приведите примеры. Особенности ввода формулы? В какой последовательности выполняются операции в арифметических выражениях? Как с помощью мыши упростить набор формулы? Как увидеть формулу, записанную в ячейку? Как сделать так, чтобы в ячейке отображался не результат вычислений по формуле, а сама формула? Какие типы данных используются в Excel? Как установить формат отображения числа и результата вычислений? Какая функция используется для формирования случайного числа? Как сформировать случайное число в произвольном диапазоне? Как защитить содержимое ячеек от изменения? Укажите, какие типы диаграмм используются для интерпретации данных электронных таблиц? Пояснить построение диаграммы с помощью Мастера диаграмм. Как отредактировать отдельные элементы диаграммы? Что отражает линия тренда? Из каких соображений выбирается тип линии тренда? Что общего и чем отличаются функции ТЕНДЕНЦИЯ и РОСТ? Как Excel выдаёт сообщение об ошибке? Что такое активное окно? Перечислите методы переключения между окнами. Как установить промежуток времени, по истечении которого Word будет автоматически сохранять документ? Что такое буфер обмена? Как программы Word и Excel работают с буфером обмена? Как выделить произвольную последовательность символов? Как выделить слово, предложение, абзац и строку в текстовом процессоре?

11 32. 33. 34. 35. 36. 37. 38. 39. 40. 41. 42. 43. 44. 45. 46. 47. 48. 49. 50. 51.

Возможности форматирования символов в Word и Excel? Как произвести форматирование абзацев в MS Word? Как изменить символ списка – перечисления (маркер)? Что такое колонтитул? Как вставить колонтитул в документ? Какие команды содержит панель инструментов «колонтитулы»? Как ввести сноску в документ и отметить её звёздочкой (*)? Перечислите способы создания таблиц в Word. Как можно отформатировать таблицу? Как вставить (удалить) несколько строк или столбцов в таблицу? Как создать и (или) вставить рисунок в документ? Как изменить формат рисунка в документе Word (цвет, размер, обтекание и т.д.)? Возможности палитры «рисования» программы Word для редактирования графических объектов? Как сгруппировать несколько рисунков в единый объект? Как изменить порядок перекрытия объектов на рисунке? Как вводить сложные математические выражения в Word? Перечислите способы вставки в документ Word текстов, таблиц и объектов из других файлов. Укажите способы создания оглавления в документе Word. Как выполнить предварительный просмотр файла перед печатью? Можно ли редактировать текст в этом режиме? Возможности поиска документа в Windows и его приложениях. Отличия связанного и внедренного объектов.

12 7.

Пример выполнения курсовой работы

7.1. В качестве примера рассматривается один объект – тип SP, анализируемое оборудование колонки Genius. На его основе создается база данных ( A10 : F18 ). Начальные строки оставляются для ввода дополнительной информации, например, для создания таблицы критериев (см. табл.7.1). Таблица 7.1 A

B

C

E

F

G

H

1 курс 2

25

3 … 10

Колонки

11 12 13 14 15 16 17 18

Genius SP-306 Genius SP-330 Genius SP-710 Genius SP-715 Genius SP-718 Genius SP-F10 Genius SP-G16 Genius SP-M06А

Объект (тип) SP SP SP SP SP SP SP SP

Опт. цена Розн.цена (у.е.) (у.е.) 14 14,95 20 20,43 18 18,70 27 27,69 45 45,86 15 15,99 25 25,44 11 11,14

Цена |Ц-СРЕД| (руб.) 373,75 189,38 510,75 52,38 467,50 95,63 692,25 129,13 1146,50 583,38 399,75 163,38 636,00 72,88 278,50 284,63

Длина 13 13 13 13 13 13 13 14

Первые три столбца (А, В, С) заполняются соответствующими данными согласно заданного варианта (по последней цифре студенческого билета). Для определения розничной цены (столбец D) используется генератор случайных чисел. Случайное число в диапазоне от 0 до 1 получается с помощью функции СЛЧИС( ). Для получения случайного числа в пределах от p до q используется формула СЛЧИС( )*(q-p)+p. Данные столбца D рассчитываются по формуле =Сn+СЛЧИС( )*(q-p)+p, где n – номер строки. Содержимое столбца D копируется в соседний столбец E, используя команду «специальная вставка» и флаг «значения». Столбец D скрывается одноименной командой. В ячейку А2 вводится значение курса 1 у.е. в рублях, который используется при расчёте стоимости аппаратуры в рублях (столбец Е ). Полученные данные округляются с точностью до центов и копеек соответственно. 7.2. Используя статистические функции, определяются минимальная, максимальная цена оборудования, среднее значение и количество оборудования. Результаты полученных значений приводятся в таблице 7.2, которая строится на листе 2. Таблица 7.2 КОЛОНКИ МИН (руб.)

Тип SP

278,50

МАКС (руб.)

1146,50

СРЗНАЧ (руб.)

563,13

СЧЕТ(шт.)

8

13 7.3. Используя соответствующие функции базы данных, определяются стоимость и название оборудования объекта по условию K (критерий выбирается из табл. 3 задания). В рассматриваемом примере (β=0) осуществляется выбор оборудования по стоимости наиболее близкой к среднему значению колонок. Для этого добавляется столбец G к таблице 1, в который вносится абсолютное значение разности «Цена (руб.)» и «СРЗНАЧ», и определяется его минимум. Название оборудования и его цена определяется с помощью функции для работы с базами данных БИЗВЛЕЧЬ. Для выполнения задания 3 составляется таблица критериев для выбора из базы данных. Первая строка критерия содержит имя поля критерия, вторая – значение, по которому идет выбор. Таблица 7.3 A

B

C

E

1 курс

По к р и т е р и ю К

2

Колонки

25 Длина

3

14 Genius SP-330

Цена (руб.)

F

G

H

критерии Цена (руб.)

Объект (тип) 510,75 <>278,5 SP

=МАКС(H11: =БИЗВЛЕЧЬ(A10:G18; =БИЗВЛЕЧЬ( H18) A10; H2:H3) A10:G18; F10; H2:H3)

|Ц-СРЕД| 52,38 =ДМИН(A10:G18; G10; F2:G3)

Используемые формулы 7.4. Для подсчета количества символов в тексте к основной таблице 1 добавляется столбец H, в ячейки которого вносится текстовая функция =ДЛСТР(Аn), где n – номер соответствующей строки. Затем в таблице критериев (см. табл. 7.3 столбец В) формируются соответствующие критерий с последующим извлечением, например в ячейку В4, наименование оборудования, удовлетворяющего заданным критериям. 7.5. Чтобы проанализировать характер изменения объема продажи оборудования на Листе 3 составляется новая база данных (таблица 7.4), в которой отражаются три наименования оборудования: с минимальной (Колонки SPM06), максимальной (Колонки SP-718) стоимостью и по условию К (Колонки SP-330). Наименование оборудования извлекаются из табл.1 в соответствующие ячейки (B1, D1, F1) новой базы данных. Таблица 7.4 A 1 2 3 4 5 6 7

Месяц янв 99 фев 99 мар 99 апр 99 май 99

B C Genius SP-718

D E Genius SP-M06

F

G Genius SP-330

Об.прод Стоим. Об.прод. Стоим. Об.прод. по Стоим. по усМАКС МАКС (руб.) МИН МИН (руб.) условию К ловию К (руб.) 53 60764,50 165 45952,50 107 54650,25 67 76815,50 228 63498,00 63 32177,25 103 118089,50 187 52079,50 74 37795,50 86 98599,00 128 35648,00 65 33198,75 67 76815,50 259 72131,50 83 42392,25

14 8 9 10 11 12 13 14 15 16

июн 99 июл 99 авг 99 сен 99 окт 99 ноя 99 дек 99

51 68 56 41 38 36 30

58471,50 77962,00 64204,00 47006,50 43567,00 41274,00 34395,00

276 260 280 341 409 405 456

ТЕНДЕНЦИЯ

76866,00 72410,00 77980,00 94968,50 113906,50 112792,50 126996,00

РОСТ

122 137 152 167 182 197 212

62311,50 69972,75 77634,00 85295,25 92956,50 100617,75 108279,00

Арифм.прог.

B ячейки J2:J7, K2:K7 и L2:L7 заносятся случайные числа с учетом задания, которые соответствуют объёмам продаж оборудования по требуемой стоимости. Например, согласно заданным диапазонам, для N=0 и M=6: • в ячейки J2:J7 заносятся случайные числа от 06 до 106; • в ячейки K2:K7 - 06÷306; • в ячейки L2:L7 - 06÷206; Для получения целочисленных значений используется функция ОКРУГЛ для введенных случайных чисел (=ОКРУГЛ(СЛЧИСЛ( ),0) ). Полученные данные столбцов J, K и L копируются в соответствующие ячейки В2:В7, D2:D7 и F2:F7, используя команду «специальная вставка» и флаг «значения». Стоимость продажи трёх видов Колонок SP-718, SP-M06 и SP-330 (в руб.) рассчитывается исходя из объёма его продажи и стоимости за единицу оборудования, взятых из таблиц 7.2 и 7.3. 7.6. Используя данные таблицы 7.4. и «Мастер диаграмм» строится диаграмма продажи Колонок SP-M06 и SP-718 (при М=0) за предшествующие 6 месяцев, которая приведена на рисунке 2. Продажа Колонок Genius по минимальной и максимальной стоимости 300

Объём продажи

250

Об.прод.SP-718 Об.прод.SP-М06

200 150 100

Колонки Genius типа SP

50 0 янв.99

фев.99

мар.99

апр.99

май.99

июн.99

Месяц

Рис. 2. Зависимость объёма продажи оборудования по максимальной и минимальной стоимости за полугодие.

15 7.7. Прогноз продажи соответствующего оборудования за 6 последующих месяцев отображаются в ячейках В8:В13, D8:D13 и F8:F13 в табл. 4. Для прогноза продажи колонок SP-718 используется функция ТЕНДЕНЦИЯ, колонок SP-М06 - функция РОСТ и колонок SP-330 – Арифметическая прогрессия. Для этого в ячейку В8 вводится формула =ТЕНДЕНЦИЯ (В$2:В7;А$2:А7;А8;1) с последующим заполнением всех ячеек столбца В. Прогноз с помощью функции РОСТ ячеек D8:D13 осуществляется аналогично. Прогноз продажи оборудования (ячеек F8:F13) функцией ПРОГРЕССИЯ производится с помощью команды “Автозаполнение”. По результатам таблицы 4 строится диаграмма, которая приведена на рисунке 3. На диаграмме, кроме того, отображаются соответствующие линии тренда, аппроксимирующие зависимость стоимости для выбранного типа оборудования колонок SP-M06 и SP-718. Продажа Колонок Genius по минимальной и максимальной стоимости 140000 y = -64,223x4 + 306887x3 - 5E+08x2 + 4E+11x - 1E+14 R2 = 0,8246

120000

Стоимость (руб.)

100000 Genius SP-718 Genius SP-M06 Полиномиальный (Genius SP-718) 2 линейный фильтр (Genius SP-M06)

80000

60000

40000

20000 Колонки Genius типа SP 9

9

к.9 де

9

.9

но я

ок т. 9

.9 9

р. 99 ап р. 99 ма й. 99 ию н. 99 ию л. 99 ав г.9 9 се н. 99

ма

ф ев

ян в

.9 9

0

Месяц

Рис. 3. Зависимость продажи оборудования по максимальной и минимальной стоимости за год. Выводы: • как видно из диаграммы (Рис. 3) оборудование по минимальной стоимости по сравнению с максимальной продаётся в большем объёме: • закон изменения стоимости оборудования SP-718 – полиномиальный а SP-M06 – скользящее среднее (2 линейный фильтр). • коэффициент аппроксимации R2 близок к единице, что указывает на высокую степень достоверности выбранного закона.

16 7.8.Рассчитаем «суммарную стоимость оборудования за те месяцы, в которые объем продаж оборудования с минимальной стоимостью не превышает 250 единиц». Для этого используем функцию базы данных БДСУММ() и критерий «Об.прод.SP-M06 <250». В свободную ячейку, например I 1 скопируем содержимое ячейки D2 «Об.прод.МИН», а в ячейку I 2 занесём условие «<250». В другую свободную ячейку, например I 3, введём функцию =БДСУММ(A2:G14;E2;J1:J2). 7.9. Для вывода месяца продажи самого дорогого оборудования по условию К используем функцию базы данных БИЗВЛЕЧЬ() и критерий «Стоим. по условию К (руб.) 108279». В свободную ячейку, например, I 5 скопируем содержимое ячейки G2 «Стоим. по условию К (руб.)», а в ячейку I 6 занесём условие «=МАКС (G3:G14)». В другую свободную ячейку, например I 7, введём функцию = БИЗВЛЕЧЬ (A2:G14;А2;J5:J6).

17 ПРИЛОЖЕНИЕ 1. ИСХОДНЫЕ ДАННЫЕ К КУРСОВОЙ РАБОТЕ A B M=0 1. Компьютеры Pentium с поддержкой MMX AMDK6II300MHz/ZIDATX98/DIMM16MbSDRAM10ns AMDK6II333MHz/ZIDATX98/DIMM16MbSDRAM10ns AMDK6II350MHz/ACORP5ALI61/DIMM16MbSDRAM10ns AMDK6II400MHz/ACORP5ALI61/DIMM16MbSDRAM10ns CyrixMII300GP/ZIDATX98/DIMM16MbSDRAM10ns CyrixMII333GP/ZIDATX98/DIMM16MbSDRAM10ns Pentium200MHzINTELMMX/ZIDATX98/DIMM16MbSDRAM10ns Pentium233MHzINTELMMX/ZIDATX98/DIMM16MbSDRAM10ns 2. Компьютеры на процессоре Intel IntelCeleron300MHzbox/ZIDABX98/DIMM16MbSDRAM10ns IntelCeleron333MHz/ACORP6LX87/DIMM16MbSDRAM10ns IntelCeleron366MHz/ACORP6LX87/DIMM16MbSDRAM10ns IntelCeleron400MHz/ACORP6LX87/DIMM16MbSDRAM10ns Pentium300MHz/ZIDABX98/DIMM16MbSDRAM10ns Pentium333MHz/ZIDABX98/DIMM16MbSDRAM10ns Pentium350MHz/ZIDABX98/DIMM16MbSDRAM10ns Pentium400MHz/ZIDABX98/DIMM16MbSDRAM10ns M=1 1. Мониторы Монитор SAMSUNG SamTron 40B - 14», LR, MPR2,NI,0.28 Монитор SAMSUNG SamTron 45B - 14», LR, MPR2,NI,0.28 Монитор SAMSUNG SyncMaster510mS-15»,LR,MPR2,NI,0.28 Монитор SAMSUNG SamTron 50E - 15», LR, MPRII,0.28 Монитор SAMSUNG SyncMaster510S-15»,LR,MPR2,NI,0.28 Монитор SAMSUNG SamTron 70E - 17», LR, MPRII,0.28 Монитор SAMSUNG SyncMaster700IFT DYNAFLAT-17»,0.20 Монитор SAMSUNG SyncMaster710S-17»,0.28,MPRII 2. Мониторы Монитор SONY100 GS-15 «,Trinitron, 0.25, OSD,MALS Монитор SONY100 GST-15 «,Trinitron,0.25,OSD,TCO'95MALS Монитор SONY110 EST-15 «,Trinitron,0.25,TCO95,OSD,MPRII Монитор SONY220AS-17 «,0.25,PnP,USB,TCO-92,MALS,AR.t Монитор SONY200GST-17 «,Trinitron,0.25,85 KHz,OSD,TCO95 Монитор SONY200PST-17 «,SuperFine Trinitron,0.25,MALS,TCO95 Монитор ViewSonic P775-17 «,0.25,TCO-95, OSD M=2 1. Принтеры - струйные и матричные Epson StylusColor440 - струйный, цветной, 4стр/мин, А-4,720 dpi Epson StylusColor640 - струйный,цветной,4стр/мин,А-4,1440x720d Epson StylusColor740 - струйный,цветной, 4стр/мин,А-4,1440x720d Epson LQ-100 - матричный, 24 иглы, формат А4 Epson FX-1170 - матричный,широкий,9игл,380 знаков/мин.,24 кбайт Epson FX-LQ-100+ - матричный, узкий, 24 иглы

Тип Pentium Pentium Pentium Pentium Pentium Pentium Pentium Pentium Intel Intel Intel Intel Intel Intel Intel Intel

Фирма SAMSUNG SAMSUNG SAMSUNG SAMSUNG SAMSUNG SAMSUNG SAMSUNG SAMSUNG

C Цена(у.е.) 264 287 286 324 250 257 263 266 295 314 335 341 349 390 446 458

Цена(у.е.) 133 137 209 166 168 285 510 318

SONY SONY SONY SONY SONY SONY SONY

262 275 243 429 467 585 450

Тип Epson Epson Epson Epson Epson Epson

Цена(у.е.) 137 195 258 137 315 134

18 A 2. Принтеры - лазерные и струйные HP Laser Jet 1100 - лазерный, 600dpi, A4, 2Mb RAM, 8стр/мин HP Laser Jet 1100A - лазерный,600dpi,A4,2MbRAM,8стр/мин,сканер HP Laser Jet 2100 - лазерный,1200dpi,A4,копир,сканер HP Desk Jet 420C - струйный, формат А4,600dpi HP Desk Jet 610C - струйный, цветной, формат А4 HP Desk Jet 695C - струйный, цветной, фотопечать, формат А4 HP Desk Jet 880C - струйный, цветной, 8 стр/мин, формат А4 HP Desk Jet 895Cxi - струйный, цветной RET2, 8 стр/мин, А4 M=3 1. Звуковые карты Creative Sound Blaster 64V PCI (OEM) Creative Sound Blaster AWE 32 (CT - 3990) Creative Sound Blaster AWE 32 (CT - 2760) ASP 512 Kb Creative Sound Blaster AWE 64 ( CT - 4520) (OEM) Creative Sound Blaster PCI128 (CT - 4700) (OEM) Creative Sound Blaster Vibra 128 (CT - 4810), PCI, OEM Creative Sound Blaster Vibra 16 c FM радио Creative Sound Blaster Value 3D PCI (CT - 4670) (OEM) 2. Звуковые карты CRYSTAL 4235 Сhip, 3D, ISA CRYSTAL 4237 Сhip, 3D, ISA Hanmensoft Sound track 128 Ruby ISA (RTL) Turtle Beach Montego A3D Xtream PCI (OEM) Turtle Beach Montego II Vortex2 AU8830A PCI (OEM) Turtle Beach Multisound Fiji ISA (RTL) Turtle Beach Tropez Pius ISA (RTL) Sonic Impact S90 PCI (OEM) M=4 1. AGP Видеокарты 3DLABS Permedia 4Mb - 2 ver., AGP ASUSTEK V3000ZB 8 Mb - AGP, Riva 128zx ASUSTEK V3200 16Mb - Banshee, AGP, RTL ASUSTEK V3200 8Mb - Banshee, AGP, RTL ASUSTEK V3400 16Mb - RivaTNT, AGP ASUSTEK V3400 16Mb - RivaTNT, AGP, TV вход ASUSTEK V3400 8Mb - RivaTNT, FGP, TV вход ASUSTEK V3800 32Mb - RivaTNT2, AGP ASUSTEK V3800 32Mb Deluxe - RivaTNT2 Ultra,AGP,Video-In,TV-Out 2. PCI Видеокарты DIAMOND Stealth 3D 3240/VX2Mb - VRAM, PCI DIAMOND Stealth II S220 4Mb - PCI, SGRAM, Rendition Verite V2100 S3 Trio64V + 2Mb - PCI S3 Trio64V2/DX 1Mb - до 2Mb - PCI S3 Trio64V2/DX 4Mb - PCI S3 Virge 2Mb, TV Tuner, MPEG - PCI, Best MTV 3D S3 Virge 3D 2Mb - до 4 Mb, PCI S3 Virge 3D/DX 2Mb - до 4 Mb, PCI

B Hew Paskard Hew Paskard Hew Paskard Hew Paskard Hew Paskard Hew Paskard Hew Paskard Hew Paskard

C 398 546 749 100 120 132 300 356

Тип SB SB SB SB SB SB SB SB

Цена(у.е.) 23 79 62 43 36 26 37 59

TB TB TB TB TB TB TB TB

10 11 200 61 71 300 70 28

Тип AGP AGP AGP AGP AGP AGP AGP AGP AGP

Цена(у.е.) 23 61 95 91 97 128 124 180 252

PCI PCI PCI PCI PCI PCI PCI PCI

65 60 14 13 21 88 39 20,5

19 A

B

C

Тип _Pentium _Pentium _Pentium _Pentium _Pentium _Pentium

Цена(у.е.) 110 29 65 65 25 47

Pentium II Pentium II Pentium II Pentium II Pentium II Pentium II Pentium II Pentium II Pentium II Pentium II

106 76 58 92 70 106 121 135 113 59

M=5 1. Материнские платы ASUSTEK TX97X - Pentium 75-266, 430TX, 512Kb, MMX, ATX INTEL MARL - Pentium 75-200 MHz, ATX LUCKY STAR - Pentium 75-300 MHz,VIA 5MVP3, 512Kb,3DIMM,AGP NEXT FORCE - Pentium 75-500 MHz,VIA MVP3, 512Kb,2DIMM,AGP ZIDA 6DFX - Pentium 75-200 MHz, 430 FX ZIDA TX98 - Pentium 75-233 MHz, VIA , 512Kb, 2SIMM, 2DIMM 2. Материнские платы ABIT AB-BH6-РentiumII,440BX,AGP,3DIMM,4PCI,2ISA,100MHZ,ATX ACORP 6BX81 -PentiumII,AGP,3DIMM,4PCI,3ISA,100MHZ,ATX ACORP 6EX68 -PentiumII,440EX,AGP,2DIMM,AT ACORP 6LX17 -PentiumII,440LX,AGP,ATX ACORP 6VIA86 -PentiumII,AGP,2DIMM,100MHz, ESS1938, ATX ASUSTEKP299-PentiumII,440ZX,AGP,2DIMM,4PCI,3ISA,100MHz,ATX ASUSTEKP2BB-PentiumII,440BX,AGP,3DIMM,100MHz,AT ASUSTEKP2BFPentiumII,440BX,AGP,4DIMM,5PCI,2ISA,100MHz,ATX IWILLBD100plus-PentiumII,440BX,AGP,4DIMM,5PCI,2ISA,100MHz,ATX ZIDA BX98-PentiumII,VIA,AGP,3DIMM,100MHZ, ATX M=6 1. Память DIMM 16Mb SDRAM 10 ns DIMM 16Mb SDRAM 8 ns (PC100) DIMM 32Mb SDRAM EDO ECC DIMM 32Mb SDRAM 8 ns (PC100) DIMM 64Mb SDRAM 8 ns (PC100) DIMM 64Mb SDRAM 10 ns DIMM 128Mb SDRAM 8 ns (PC100) 2. Память SIMM 4Mb 30 pin SIMM 4Mb 72 pin EDO SIMM 8Mb 72 pin SIMM 8Mb 72 pin EDO SIMM 16Mb 72 pin SIMM 16Mb 72 pin EDO SIMM 32Mb 72 pin EDO

Тип DIMM DIMM DIMM DIMM DIMM DIMM DIMM

Цена(у.е.) 17 20,5 54 31 60 65 116

SIMM SIMM SIMM SIMM SIMM SIMM SIMM

11 8 16 13 36 23 50

M=7 1. Процессоры Pentium II Pentium II 333MHz Pentium II 333MHz box Pentium II 350MHz Pentium II 350MHz box Pentium II 400MHz box Pentium II 450MHz box

Тип Pentium II Pentium II Pentium II Pentium II Pentium II Pentium II

Цена(у.е.) 145 150 188 198 204 313

20 A 2. Процессоры Pentium Celeron Intel Celeron - 300 MHz PPGA(370) 128Kb Cache Intel Celeron - 300 MHz Intel Celeron - 300 MHz box Intel Celeron - 333 MHz PPGA(370) box 128Kb Cache Intel Celeron - 366 MHz PPGA(370) 128Kb Cache Intel Celeron - 366 MHz PPGA(370) box 128Kb Cache Intel Celeron - 400 MHz PPGA(370) 128Kb Cache Intel Celeron - 400 MHz PPGA(370) box 128Kb Cache Intel Celeron - 433 MHz PPGA(370) box 128Kb Cache M=8 1. Процессоры Pentium MMX Процессор ADM K6 II 233MHz Процессор ADM K6 II 300MHz Процессор ADM K6 II 350MHz Процессор ADM K6 II 400MHz Процессор Cyrix M II 266GP Процессор Cyrix M II 300GP Процессор Cyrix M II 333GP Процессор Pentium 200MHz Intel MMX Процессор Pentium 233MHz Intel MMX 2. Процессоры Pentium II Процессор Pentium II 333MHz Процессор Pentium II 333MHz box Процессор Pentium II 350MHz Процессор Pentium II 350MHz box Процессор Pentium II 400MHz box Процессор Pentium II 450MHz box M=9 1. Картриджи для лазерных и струйных принтеров Картридж для (лазер. принт.) HP LJ 1100/1100A - (C4092) Картридж для (лазер. принт.) HP LJ 2100 - (C4096A) Картридж для (лазер. принт.) HP LJ 4000 - (C4127x) Картридж для (лазер. принт.) HP LJ 4Si/4Si - MX/3Si - (HP 92291A) Картридж для (лазер. принт.) HP LJ 5Si/5Si MX - (C3909A) Картридж HP 51625A для (струйн. принт.) HP DeskJet 5xx (цвет.) Картридж HP 51626A для (струйн. принт.) HP DeskJet 5xx (чёрн.) Картридж HP 51641A для (струйн. принт.) HP DeskJet 8xx (цвет.) Картридж HP 51645A для (струйн. принт.) HP DeskJet 8xx (чёрн.) 2. Картриджи для матричных и струйных принтеров Картридж для (матрич. принт.) Epson DFX 5000/8000 Картридж для (матрич. принт.) Epson FX 1000/LX-1170 Картридж для (матрич. принт.) Epson LQ-2170 Картридж для (матрич. принт.) Epson LX-100 Картридж S020025 для (струйн. принт.) Epson Stylus 400/800+/1000 Картридж S020049 для (струйн. принт.) Epson Stylus800/1500(цвет) Картридж S020062 для (струйн. принт.) Epson Stylus 1500 (чёрн) Картридж S020066 для (струйн. принт.) Epson Stylus Pro XL+(цвет)

B Pentium Celer Pentium Celer Pentium Celer Pentium Celer Pentium Celer Pentium Celer Pentium Celer Pentium Celer Pentium Celer

C 72 48 51 76 95 80 98 98 130

Тип Цена(у.е.) Pentium MMX 32 Pentium MMX 49 Pentium MMX 52 Pentium MMX 93 Pentium MMX 33 Pentium MMX 37 Pentium MMX 44 Pentium MMX 51 Pentium MMX 53 Pentium II Pentium II Pentium II Pentium II Pentium II Pentium II

145 150 188 198 204 313

Тип Цена(у.е.) Hew Paskard 53 Hew Paskard 95 Hew Paskard 130 Hew Paskard 120 Hew Paskard 159 Hew Paskard 29 Hew Paskard 28 Hew Paskard 31 Hew Paskard 29 Epson Epson Epson Epson Epson Epson Epson Epson

9 2,5 11 3 9,5 25 41 30

21 ПРИЛОЖЕНИЕ 2 Табличный процессор EXCEL 1. Основные понятия Табличные процессоры – это программы, которые предназначены для обработки данных представленных в виде таблиц. Для табличных расчетов характерны относительно простые формулы, но большие объемы исходных данных, Благодаря своей универсальности при проведении расчетов над данными, представленными в табличной форме, широкое распространение получили пакеты прикладных программ для работы с электронными таблицами, или табличные процессоры. Данные содержаться в виде чисел, текста и формул, Пересчет содержимого происходит мгновенно. Преимуществами являются обозримость и высокое быстродействие (все таблицы находятся в оперативной памяти). Табличные процессоры позволяют не только производить расчеты, но и моделировать варианты и ситуации путем изменения исходных данных в ячейках. Электронную таблицу можно просматривать, изменять, записывать на магнитный диск, выводить на печать. Кратко рассмотрим работу с табличным процессором Excel версия 7.0 фирмы Microsoft. На экране электронная таблица представлена в виде матрицы из столбцов и строк, на пересечении которых образуются ячейки. Максимальное количество столбцов – 256, строк – 65536. Первые 26 столбцов обозначены латинскими буквами от A до Z, вторые 26 – AA... AZ, третьи – BA ... BZ (последний – IV). Строки обозначены числами от 1 до 65536. Каждая ячейка адресуется по номеру столбца и строки (как в «морском бое» или шахматах) – A5, Z99, BZ7. Табличный процессор может работать с различными типами данных: текстом, числами, формулами. Ячейка, на которой установлен табличный курсор (жирная рамка вокруг ячейки), называется активной ячейкой. Блок клеток – любая прямоугольная часть таблицы, задаваемая адресами клеток, лежащих в противоположенных ( по диагонали ) углах прямоугольника. Активная часть электронной таблицы – блок, содержащий данные: левый верхний угол его ячейки А1, правый нижний угол – ячейка на пересечении последних занятых столбца и строки. 2.

Запуск и завершение работы:

Запуск: • щелчок на ярлычке на панели Microsoft Office или в Главном меню Æ Программы – MS Excel. • либо найти через программу ПРОВОДНИК в главном меню в разделе ПРОГРАММЫ. Microsoft Excel.lnk

22 • • • •

Завершение работы: Alt +F4 меню «Файл» ⇒ «Выход», щелчок на кнопке системного меню r при кратковременном выходе из программы щелчок на кнопке ▬ Вид экрана

Операционное меню Заголовок окна Обозначение столбцов Адрес активной ячейки

Пиктографическое меню (панель инструментов) Строка (окно) формул

Табличный курсор (активная ячейка А1) Ярлычки листов Обозначение строк

Панель статуса

Экран Excel оформлен в соответствии с общепринятыми стандартами Windows. Верхние пять строк: • панель заглавия: кнопки системного меню, максимизации и минимизации размера окна, сворачивание окна и закрытие программы; • панель операционного меню («Файл», «Правка», «Вид», «Вставка», «Формат», «Сервис», « Данные», «Окно», «?»); • панель стандартных инструментов; • панель инструментов форматирования; • панель формул ( выводится адрес активной ячейки и её содержание); Следующие строки – окно документа. Самая нижняя строка – панель статуса; в ней высвечивается режим работы Excel (в начале сеанса – «Готов»); индикаторы NUM, CAPS и т.п.

23 3. Перемещение по электронной таблице С помощью клавиш управления курсором: • на одну ячейку вверх, вниз, вправо, влево: стрелки «↑», « ↓», « →», « ←»; • в столбец A текущего ряда: Home; • в начало книги (ячейка А1): Ctrl+Home; • в нижний правый угол активной части Ctrl+End; • на экран вверх, вниз: PageUp, PageDown; • переход в любую ячейку F5, появится окно «ПЕРЕХОД» в поле ссылка введите имя нужной Вам ячейки и щелкните ОК, либо нажмите Enter ( при переходе к ячейке на другом листе имя начинайте с набора листа: «Лист5!А8», т.е. переход в ячейку А8 на листе 5), либо меню ПРАВКА ⇒ ПЕРЕХОД. 4. Перемещение между листами книги • к предыдущей таблице: Ctrl+ PageUp; • к следующей таблице Ctrl+PageDown; • щелчок КМ по ярлычку листа. 5. Динамическое (контекстное) меню В Excel кроме операционного меню, которое вызывается так же , как и в других приложениях Windows, существует динамическое меню объекта (ячейки, строки, столбца и т.п.) Вызов динамического меню: после установки курсора мыши на объекте однократный щелчок ПРАВОЙ кнопкой мыши. 6. Типы данных Текстовые данные К ним относятся все данные, которые не имеют общего с числами и вычислениями. Текстовые данные прижимаются к левому краю ячейки таблицы. Если текст не умещается в одной ячейке, то он «растекается» вправо в соседние ячейки до первой не пустой. Редактирование текстовых данных может осуществляться только при адресации к ячейке, в которой он начинал вводиться. Ячейки «растекания» текста при просмотре в окне функций выглядят незаполненными Числа Числа выравниваются по правому краю. Если число не умещается в ячейке, то оно представляется в экспоненциальной форме ( например, 1.23Е+12 означает: 1,23 умноженное на 10 в степени 12 ). Числа можно записывать в виде обыкновенной дроби – целая часть отделяется пробелом от дробной ( например 1 2/3 или 0 1/5 ) ; могут числа вводиться в виде процентов.

24 Даты и время, записанные определенным образом, могут использоваться в вычислениях. Признак даты и времени – соответствующий формат, например: формат времени – 3:21 РМ, 3:21:04 РМ — время пополудни; 7:43 АМ – время до полудня; 15:21, 15:21:04; формат даты – 10/25/94, 10-25-94, Янв/97, 6/Янв/97, 23/Июнь (используется текущий год). Формулы Признак формулы – знак равенства ( = ) в начале записи. Операндами ( слагаемыми, вычитаемыми и т.п.) могут быть: • числа; • АДРЕСА клеток; • функции. Операнды соединяются знаками арифметических операций: +, —, *(умножение ) , /(деление), ¬ ( возведение в степень ). Действия выполняются слева направо, порядок может быть изменен с использованием скобок ( ). Формулы пересчитываются при каждом изменении содержимого любой ячейки. Возможно включение режима ручного пересчета, когда пересчет происходит только по команде пользователя: • Меню: «Сервис» ⇒ «Параметры» ⇒ «Вычисления» ⇒ «Ручной пересчет» • Пересчет: F9. 7.

Ввод и корректирование данных

ВВОД данных в активную ячейку: 1) начало ввода информации с клавиатуры происходит после ввода первого же символа. В панели формул появляются кнопки «Отказ», «Ввод», «Мастер функций»: ⇒ r √ fx 2) Вводимая информация появляется в текущей ячейке и в панели формул, в панели статуса появляется индикация «Ввод». 3) Завершение ввода с сохранением введенной информации (при этом информация, находившаяся в ячейке, заменяется на введенную) осуществляется одним из способов: • Enter; • стрелки « ↑», « ↓», « →», « ←» ( после этого активной становится соседняя ячейка); • щелчок КМ (курсором мыши) на кнопке в строке формул.

a

25 Отмена завершения ввода (восстанавливается информация, которая была в ячейке до начала ввода ): • Esc; • щелчок курсором мыши по кнопке r в строке формул. Ввод функций — кнопка «Мастер функций» Заполнение последовательностей: 1) Курсор мыши (КМ) установить в правый нижний угол заполненной первой ячейки последовательности (вид КМ – черный крестик (+); 2) Растягивать вправо или вниз в сторону распространения области заполнения. Примеры последовательностей: Июнь, Июль, ... ( месяцы) 4/1/94, 4/2/94, 4/3/94, ... ( дата ) Июнь 94, Июль 94, Август 94, .... Цех 1, Цех 2, ..... КОРРЕКТИРОВАНИЕ содержимого активной ячейки: • F2; • двукратный щелчок левой кнопкой мыши (редактирование внутри ячейки); • установить курсор мыши в нужное место для корректировки в строке формул. В строке формул появляется та же информация, что и при вводе. В панели статуса – индикация «Правка». Перемещения по корректируемой строке: Delete, « ↑ «, « ↓ «, «→«, «←«, Home, End, Ctrl+(« → «, « ← «) – (на слово вправо/влево), Home (в начало строки), End (в конец строки данных), Ins (режим ВСТАВКА / ЗАМЕНА). 8.

Использование функций

Два способа • занести функцию в ячейку вручную; • кнопка «Мастер функций» или автоматическое суммирование кнопка Σ ; выбор меню «автосуммирование». Статистические функции Статистические функции позволяют выполнять статистический анализ диапазонов данных. Например, с помощью статистической функции можно провести прямую по группе значений, вычислить угол наклона и точку пересечения с осью Y и прочее. Статистические функции, используемые в курсовой работе:

26 МИН (Результат :Наименьшее значение в списке аргументов) МИН ( число 1; число 2;, ...) МАКС (Результат :Наибольшее значение в списке аргументов) МАКС(число 1; число 2; ... ) СРЗНАЧ (Результат :Среднее значение ). СРЗНАЧ (число 1; число 2; ...) СТАНДОТКЛОН (Оценивает стандартное отклонение по выборке. Стандартное отклонение - это мера того, насколько широко разбросаны точки данных относительно их среднего. ·СТАНДОТКЛОН использует следующую формулу: СТАНДОТКЛОН(число1; число2; ...) Синтаксис : ФУНКЦИЯ(число 1; число 2; ... ) Аргументы : число 1 , число 2 , ...

от 1 до 30 чисел или имена , массивы или адресные ссылки на диапазоны ячеек, среди которых имеется или вычисляется искомое значение,

Для определения случайного числа используется функцию СЛЧИС(), которая генерирует случайное число от 0 до 1. Для получения случайного числа в пределах от 1 до 100 используется следующая формула =СЛЧИС()*(100-1)+1 . Для работы с базой данных используются функции «работа с базой данных» (см. раздел «Работа с данными») 9.

Сохранение, загрузка и печать таблиц

Вновь созданную или отредактированную таблицу необходимо сохранить – записать на диск, где она хранится в виде файла. Сохранение таблицы под тем же именем: • меню «Файл» ⇒ «Сохранить»; • Ctrl +S. Сохранение существующей таблицы под другим именем. • меню «Файл» ⇒ «Сохранить как»; • пиктограмма с изображением дискеты ( ) . Загрузка с диска существующей таблицы: • меню «Файл» ⇒ «Открыть»; • пиктограмма с изображением открывающейся папки (). Создание новой таблицы • меню «Файл» ⇒ «Создать»; • пиктограмма с изображением чистого листа с загнутым уголком ().

27 Закрытие таблицы: • меню «Файл» ⇒ «Закрыть»; Вывод на печать таблицы: • меню «Файл» ⇒ «Печать»; • пиктограмма с изображением принтера . 10. Выделение блоков Сделать активной ячейку в одном из углов блока, далее расширять блок одним из перечисленных ниже способов: • растягивать при нажатой левой клавише мыши; • Shift + стрелки. • установить табличный курсор на первой ячейке диапазона + «Shift» + щелкнуть КМ на последней ячейке отмечаемого диапазона. Выделение всей таблицы: • Shift +Ctrl + Пробел; • однократный щелчок по пустой кнопке в левом верхнем углу (левее А и выше 1 ); • «Правка»⇒ «Выделить всё». Выделение столбца: • Ctrl + Пробел; • однократный щелчок по букве заголовка столбца. Выделение строки: • Shift + Пробел ; • однократный щелчок по номеру строки слева. Выделение нескольких блоков одновременно: • Выделив один диапазон, как описано ранее, затем, удерживая кнопку «Ctrl», отметить второй диапазон ячеек. 11. Копирование, перемещение, вставка, удаление, очистка Копирование и перемещение При копировании содержимого ячеек текст и числа, находящиеся в этой ячейке перемещается в другую. Входящие в формулы относительные адреса ячеек модифицируются – координаты изменяются на столько, на сколько клеток произошло перемещение ячейки с формулой (например, при копировании ячейки с формулой «=B2+C3» в ячейку, расположенную на 3 ряда ниже и 2 столбца правее, формула принимает вид «=D5+E6»). Абсолютные адреса (символ $ перед координатой, например «=N$1+$G$3») не изменяются при распространении формулы.

28 Копирование через буфер содержимого блока ячеек в другой блок: 1) Выделение блока ячеек, откуда копируется информация (см. «Выделение блоков») 2) Копирование выделенного блока в буфер (вокруг выделенного блока появляется мерцающий пунктир): • Ctrl + Ins; • Ctrl + C; • меню «Правка» ⇒ «Копирование»; • щелчок по правой кнопке мыши на копируемой ячейке ⇒ «Копирование»; 3) Выделение блока клеток, куда копируется информация (см. «Выделение блоков»). 4) Вставка из буфера в этот блок: • Shift + Ins; • Ctrl +V; • меню «Правка» ⇒ «Вставить»; • щелчок по правой кнопке мыши ⇒ «Вставить». Пункты 3) и 4) можно повторять многократно – копировать одно и то же в разные блоки. Копирование и перемещение выделенного блока (один раз): 1) КМ установить на границу блока (принимает вид толстой белой стрелки); 2) перетаскивать в нужное место ( в зависимости от нажатых при этом клавиш результат различный): • ни одна клавиша не нажата – блок перемещается, содержимое ячеек замещается новым блоком; • нажата клавиша – блок копируется, содержимое клеток замещается новым блоком; • нажата клавиша <Shift> — блок перемещается, «раздвигает» ячейки и вставляется между ними; • нажаты – блок копируется, «раздвигает» ячейки и вставляется между ними. Вставка Вставка пустых столбцов или строк на место выделенных столбцов или строк. • меню «Вставка» ⇒ «Столбец» или «Строка»; • меню столбца или строки ⇒ «Вставка». Вставка пустого блока на место выделенного • меню «Правка» ⇒ «Вставка» ⇒ «Ячейки»; • меню ячейки ⇒ «Вставка». Удаление Удаление выделенных строк или столбцов: • меню «Правка» ⇒ «Удалить»;

29 • меню столбца или строки «Удалить». Удаление выделенного блока: • меню «Правка» ⇒ «Удалить»; • меню ячейки ⇒ «Удалить». Очистка Очистка только содержимого выделенного блока (формат сохраняется): • ; • меню ячейки ⇒ «Очистить»; • меню «Правка» ⇒ «Очистить»⇒ «Содержание». Очистка только формата блока (содержимое сохраняется): • меню «Правка» ⇒ «Очистить»⇒ «Форматы». Очистка содержимого и формата блока: • меню «Правка» ⇒ «Очистить»⇒ «Всё». 12. Отмена и восстановление последнего действия (Undo) Последнюю выполненную операцию можно отменить или повторить. Если требуется восстановить таблицу, то это необходимо делать сразу же, до выполнения следующей команды: • меню «Правка» ⇒ «Отменить <последнее действие>». 13. Форматирование Форматирование – задание способа представления хранящейся в ячейке информации. Задание ширины столбцов и высоты строк: • курсор мыши (КМ) установить на правую границу столбца (в верхней строке с обозначением столбцов) или на нижнюю границу строки (в левом столбце с обозначением строк) – курсор принимает вид горизонтальных (или вертикальных) линий со стрелками; • перетаскиваем КМ, нажав на левую клавишу, до достижения нужного размера столбца или строки. Задание автоматической ширины столбца (по максимальной ширине записи в ячейке): • двукратный щелчок по правой границе столбца.. Спрятать/показать столбец: • курсор мыши установить на заголовке столбца; далее выбрать один из способов: • меню “Формат”⇒“Столбец”⇒“Скрыть”/“Отобразить” • контекстное меню столбца (вызывается щелчком по правой клавише мыши) ⇒“Скрыть”/“Отобразить”

30 Примечание: при задании команды “показать столбец” необходимо предварительно выделить оба столбца, между которыми находится скрытый столбец. Автоматическое форматирование (по имеющимся образцам): • меню “Формат”⇒“Автоформат”, выбрать из списка образцов. Задание формата для блока ячеек: • Ctrl+1; • Меню “Формат” ⇒ “Формат ячеек” ⇒ Закладки: “Число / Выравнивание / Шрифт / Рамка / Вид ”; • Контекстное меню ячейки ⇒ “Формат ячеек” ⇒ “Число / Выравнивание / Шрифт / Рамка / Вид ”; Коды форматов для вывода чисел: 0 или # — место для цифр; М – месяцы, D – дни, H – часы, S – секунды. 14. Масштаб, окна, закрепление областей таблицы Изменение масштаба: • Меню “Вид” ⇒ “Масштаб”. Для просмотра таблицы в двух различных местах окно должно быть “расщеплено”: 1) установить курсор мыши на область разделения экрана над правой верхней стрелкой линейки прокрутки 5 – курсор принимает вид горизонтальных линий со стрелками; 2) перетаскивать линию раздела экрана в нужное положение. Переход из окна в окно: функциональная клавиша F6. Отмена разделения окна: • двукратный щелчок по полосе разделения. Закрепление строк таблицы выше активной ячейки и столбцов левее активной ячейки (при перемещении активной ячейки они не будут перемещаться): • меню “Окно” ⇒ “Закрепить области”./“Снять закрепление областей”. 15. Защита Для того, чтобы случайно или злонамеренно нельзя было изменить содержимое заполненных ячеек, их необходимо защитить. Защита всей таблицы. • меню: «Сервис» ⇒ «Защита» ⇒ «Защитить лист». Частичная защита: 1) выделить блок незащишаемых клеток, 2) выбрать один из вариантов действий: • Ctrl+1 ⇒ «Защита»;

31 • меню: «Формат» ⇒ «Формат ячеек» ⇒ «3ащита»; • меню «Ячейки» ⇒ «Формат ячеек» ⇒ «3ащита» 3) убрать маркер  с флажка «Защищаемая ячейка» 4) меню: «Сервис» ⇒ «Защита» ⇒ «Защитить лист». Отмена защиты:. • меню: «Сервис» ⇒ «Защита» ⇒ «Снять защиту». 16. Работа с несколькими таблицами Каждый файл Excel может состоять из нескольких таблиц, называемых листами – при создании файла их 16. Весь файл называется рабочей книгой. В нижней части окна документа показаны обозначения листов, имеющихся в книге. Переход от листа к листу. • с помощью кнопок слева от полосы горизонтальной прокрутки перемещаться по списку листов  Если активизировать несколько листов, то при вводе информации в ячейку на одном листе она заносится в ячейки с тем же адресом на всех активных листах Активизирование листа, • однократный щелчок по указателю листа. Активизирование нескольких соседних листов. 1) однократный щелчок по первому указателю листа, 2) Shift + однократный щелчок по последнему отмечаемому листу. Активизирование нескольких не соседних листов: 1) однократный щелчок по первому указателю листа; 2) Сtrl + однократный щелчок по последнему. Отмена активизации: • активизировать другой лист. Вставка листов: 1) однократный щелчок по указателю, перед которым должен вставляться новый 2) выбрать один из способов: • меню Листа: «Вставка», • меню: «Правка» ⇒ «Вставить» ⇒ «Лист». Удаление активизированных листов: • меню: «Правка» ⇒ «Удалить лист». • меню Листа; ⇒ «Удалить». Переименование листов: • двукратный щелчок по указателю;

32 • меню Листа ⇒ «Переименовать» • Изменение порядка следования листов: перетаскивать указатель листа в нужное место. 17. Работа с базами данных В Ехсel имеются средства, позволяющие осуществлять операции, присущие работе с базами данных (БД): ведение БД (ввод, коррекция и удаление записей); фильтрация (отбор) записей по задаваемому критерию поиска, сортировка записей по одному и нескольким полям; получение итогов по группе записей и всей БД ·База данных в Microsoft Excel - это список связанных данных, в котором строки данных являются записями, а столбцы - полями. Верхняя строка списка содержит названия каждого из столбцов (Поле). Ссылка может быть задана как диапазон ячеек. либо как имя, соответствующее диапазону списка. Ведение БД с помощью «Формы « Вводить, удалять и корректировать записи данных можно традиционными для Excel способами (см. выше). Кроме того, эти операции осуществляются следующим образом: 1) сделать активной любую клетку внутри блока БД. 2) меню «Данные» ⇒ «Форма» появляется диалоговое окно «Форма» (Рис.2), соответствующее одной (текущей) записи (строке); в левой части окна рядом с наименованиями полей расположены окна, в которых показаны значения полей этой записи; в правой части окна номер записи и количество записей в БД, а также кнопки действия и линейна прокрутки. Рис. 2. Диалоговое окно «Форма». Переходы от записи к записи. • с помощью линейки прокрутки; • клавишами Вверх, Вниз; • PgDn, PgUp (на 10 вниз или вверх), • Ctrl + Стрелка (на первую или последнюю) • кнопками действия - «Предыдущая», «Следующая». Переход от поля к полю:

33 • Tab (вниз) или Shift ⇒ Tab (верх). Замена значения поля: • ввод символов в поле - старое значение исчезает. Коррекция значения поля: • щелчок по полю; далее исправления. Очистка поля, в котором находится курсор: • Delete . Копирование из предыдущей записи: • Ctrl + « •» : Создание или удаление записи: • кнопками действия «Создать» или «Удалить». Сортировка записей Сортировка по одному полю: 1) сделать активной любую клетку внутри столбца, по которому проводится сортировка; 2) кнопки на панели стандартных инструментов «А¨Я» (по возрастанию) или «Я¨А» (по убыванию). Сортировка по одному - трем полям: 1) сделать активной любую клетку внутри блока БД; 2) меню: «Данные» ⇒ «Сортировка»; 3) в появившемся диалоговом окне выбрать первое, второе, третье поля сортировки по возрастанию или убыванию. Отбор записей С использованием «Формы” (переход от записи к записи происходит только по тем записям, которые удовлетворяют критерию поиска): 1) меню: «Данные» ⇒ «Форма» ⇒ «кнопка «Критерии»; 2) ввести в окна полей значения критериев поиска, можно использовать шаблон (* и ?), знаки >, <, >=, <=, =, <>(не равно). Завершение работы с «Формой»: кнопка «Отменить». С использованием фильтрации (в БД видны только записи, удовлетворяющие критерию поиска): 1) сделать активной любую клетку внутри блока БД, 2) меню «Данные» ⇒ «Фильтр» ⇒ «Автофильтр» — справа от имен полей появляются стрелки выпадающих списков. 3) щелкнуть по стрелке, выбрать значение поля для фильтрации: • (все) - фильтр отменяется; • (настройка) - задание сложных фильтров; • любое значение поля из появившегося списка.

34 После выбора стрелка окрашивается в синий цвет, на экране остаются только записи, удовлетворяющие критерию; 4) при необходимости повторить пункт 3) для другого поля. Фильтрованные записи можно копировать в другое место листа. Отмена всех фильтров: • меню: «Данные» ⇒ «Фильтр» ⇒ «Авто фильтр» Получение итогов Если в БД есть числовые поля, то можно получить промежуточные итоги по группе записей, а также общий итог: 1) отсортировать БД по полю, по которому группируются записи для итогов (см. п. «Сортировка записей»); 2) меню: «Данные» ⇒ «Итоги»; 3) в появившемся диалоговом окне задать: • поле, при каждом изменении значения в котором, подводится итог (по этому полю проводилась сортировка), • числовые поля, для которых подводится итог, • функции, по которым производится подсчет итогов; 4) ОК. Функции баз данных. Использование критериев для работы с функциями БД В Microsoft Excel имеется 12 функций рабочего листа, используемых для анализа данных из списков или баз данных. Каждая из этих функций, которые из соображений совместимости имеют обобщенное название БДФункция, использует три аргумента: база_данных, поле и критерий. Эти три аргумента ссылаются на интервалы ячеек на рабочем листе, которые используются данной функцией. Синтаксис БДФункция(база_данных;поле;критерий) База_данных - это интервал ячеек, формирующих список или базу данных. Во всех функциях баз данных в том случае, если база данных ссылается на ячейку в сводной таблице, вычисления производятся только для данных в сводной таблице. Для вычисления и добавления промежуточных итогов в список следует пользоваться командой <Итоги> меню <Данные>. Поле определяет столбец, используемый функцией. Поля данных в списке должны содержать идентифицирующее имя в первой строке. Аргумент «поле» может быть задан как текст с названием столбца в двойных кавычках,

35 например « ВОЗРАСТ « или « ПРИБЫЛЬ « в приведенном ниже примере базы данных, или как адрес ячейки, содержащей название поля. Критерий - это ссылка на интервал ячеек, задающих условия для функции. Функция возвращает данные из списка, которые удовлетворяют условиям, определенным диапазоном критериев. Диапазон критериев включает копию названия столбца, для которого выполняется подведение итогов, в списке. Ссылка на критерий может быть введена как интервал ячеек, например A1:E2 в приведенном ниже примере базы данных, или как имя интервала, например «Критерии». Советы ·Любой интервал, который содержит, по крайней мере, одно название столбца и, по крайней мере, одну ячейку под названием столбца с условием, может быть использован как аргумент критерий БДФункции. Несмотря на то, что диапазон критериев может располагаться в любом месте таблицы, не стоит помещать его внизу списка. Это связано с тем, что данные, добавляемые в список командой <Форма> меню <Данные>, вставляются с первой строки после списка. Если эта строка не пустая, Microsoft Excel не сможет добавить данные в список. Диапазон критериев не должен перекрываться со списком. Чтобы выполнить операцию над целым столбцом базы данных, нужно ввести пустую ячейку под названием столбца в интервале критерия. Примеры Следующий рисунок является примером базы данных для небольшого фруктового сада. Каждая запись содержит информацию об одном дереве. Диапазон A6:E12 имеет название База_Данных, а диапазон A1:E4 - Критерий. 1 2 3 4 5 6 7 8 9 10

A ДЕРЕВО Я бло ня ДЕРЕВО Груш а

ДЕРЕВО Я бло ня Груш а В и ш ня Я бло ня 1 1 Груш а 1 2 Я бло ня

B ВЫ С О ТА >5 ВЫ С О ТА

C D E В Ы С О Т А У Р О Ж А Й (к г) П Р И Б Ы Л Ь <10 ВОЗРАСТ

<10

<10

В Ы С О Т А В О З Р А С Т У Р О Ж А Й (к г) П Р И Б Ы Л Ь 6 20 14 1 0 5 0 0 0 р. 4 12 10 9 6 0 0 0 р. 4 14 9 1 0 6 0 0 0 р. 5 ,5 15 10 7 5 0 0 0 р. 3 8 8 7 6 8 0 0 р. 3 9 6 4 5 0 0 0 р.

БСЧЁТ(A6:Е12; «ВОЗРАСТ»;A1:С2) равняется 2. Эта функция просматривает записи о яблонях, которые имеют высоту от 5 до 10 и подсчитывает количество числовых полей “Возраст” в этих записях.

36 БСЧЁТ(A6:Е12; « ПРИБЫЛЬ »;A3: С4) равняется 1. Эта функция просматривает записи о грушах, которые имеют высоту до 10 и возраст менее 10 и подсчитывает количество непустых полей “Прибыль” в этих записях. ДМАКС(A6:Е12; « ПРИБЫЛЬ»;A1: А4) равняется 105,00 руб. — максимальный доход от яблонь и груш. ДМИН(A6:Е12;«ПРИБЫЛЬ»;A1:B2) равняется 75,00 руб. — минимальный доход от яблонь выше 5. БДСУММ(A6:Е12;E6;A1:A2) равняется 225,00 руб. — суммарный доход от яблонь. БДСУММ(A6:Е12; «ПРИБЫЛЬ»;A1: С2) равняется 180,00 руб. — суммарный доход от яблонь высотой от 5 до 10 . ДСРЗНАЧ(A6:Е12; «УРОЖАЙ (кг)»;A1:B2) равняется 12 — средний урожай яблонь высотой более 5. ДСРЗНАЧ(A6:E12;C6;A6:A11) равняется 13 — средний возраст всех деревьев. БИЗВЛЕЧЬ(A6:Е12;D6;A1:B2) возвращает значение ошибки #ЧИСЛО!, потому что критерию удовлетворяет более чем одна запись. БИЗВЛЕЧЬ(A6:Е12; «УРОЖАЙ (кг)»;A3:С4) равняется 8, возвращает значение поля « УРОЖАЙ (кг)» для груш высотой меньше 10 и возраст, которых меньше 10. Замечания Если ни одна из записей не удовлетворяет критерию, то функция возвращает значение ошибки #ЗНАЧ!. Если более чем одна запись удовлетворяет критерию, то функция возвращает значение ошибки #ЧИСЛО!