Начальные сведения о MS Excel Простейшие экономические расчеты в MS Excel Графический анализ экономической информации
Работа добавлена на сайт samzan.net: 2015-07-05
Поможем написать учебную работу
Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
от 25%
Подписываем
договор
абораторная работа «Начальные сведения о MS Excel»
Тема. Начальные сведения о MS Excel. Простейшие экономические расчеты в MS Excel. Графический анализ экономической информации.
Цель: научиться:
а) вводить данные в ячейки рабочего листа с представлением в различных форматах, б) заполнять ячейки вручную произвольными числами из заданного интервала с проверкой, в) составлять и записывать в ячейку формулу для получения случайных чисел в заданном диапазоне; г) использовать стандартные функции MS Excel для расчета среднего, максимального и минимального значений; д) строить диаграммы по заданным значениям; е) добавлять данные на уже построенную диаграмму;
освоить:
а) операцию автозаполнения ячеек двумя способами – с помощью маркера заполнения и используя окно Прогрессия, б) два варианта ввода функции в формулу – с помощью Панели формул или используя Мастер функций, в) способ вычисления итоговой суммы, г) простейшие приемы оформления внешнего вида таблицы; д) комбинированный способ записи функций И() и ЕСЛИ() для составление сложных условий оценки данных; е) основные приемы форматирования элементов диаграммы.
- ВОПРОСЫ ДЛЯ САМОПОДГОТОВКИ
- Перечислите способы перемещения по ячейкам электронной таблицы.
- Перечислите и опишите три способа выделения отдельной ячейки рабочей книги MS Excel.
- Что такое диапазон ячеек и как он адресуется (приведите пример записи адреса диапазона смежных и несмежных ячеек).
- Перечислите и опишите способы выделения диапазона смежных и несмежных ячеек рабочей книги MS Excel.
- Как изменить высоту строк и ширину столбцов?
- Задание
Создать в личной папке рабочую книгу ЛР1.xlsx, содержащую два рабочих листа под названиями «Часть 1» и «Часть 2» соответственно. Оформить рабочий лист «Часть 1» по образцу рис. 4 в точном соответствии с указаниями. Оформить рабочий лист «Часть 2» по образцу рис. 7 в точном соответствии с указаниями. Построить диаграммы (см. рис. 10 и рис. 11). Оформить отчет по лабораторной работе. Отчет должен содержать следующие элементы:
- номер лабораторной работы;
- тема лабораторной работы;
- цель;
- ответы на вопросы для самоподготовки;
- ответы на контрольные вопросы;
- вывод.
III. Порядок выполнения задания
Часть 1. Начальные сведения о MS Excel.
1. Создание рабочей книги Excel.
Указания.
Новая рабочая книга создается автоматически при запуске программы Excel. По умолчанию такой рабочей книге присваивается имя Книга1.xlsx. Другие способы создания рабочей книги:
- с использованием «Ленты»: Файл – Создать – Новая книга;
- с использованием «Панели быстрого доступа»: щелкнуть по кнопке («Создать») на «Панели быстрого доступа»;
- с использованием клавиатуры: нажать сочетание клавиш <Ctrl>+<N>.
Примечание. Excel можно запустить несколькими способами:
- двойным щелчком левой кнопкой мыши по ярлыку на рабочем столе;
- одиночным щелчком левой кнопки мыши по значку на Панели задач;
- выполнением команды Пуск – Все программы – Microsoft Office – Microsoft Excel.
2. Оформление заголовка задания.
Указания.
2.1. Выделить диапазон ячеек A1:G1 методом протягивания ЛКМ (см. рис. 1).
Рис. 1 – выделенный диапазон ячеек A1:G1.
2.2. Щелкнуть по кнопке «Объединить и поместить в центре» , которая находится на вкладке «Главная» в группе «Выравнивание».
2.3. В получившуюся объединенную ячейку ввести текст заголовка таблицы по образцу (см. рис. 4).
3. Оформление «шапки» таблицы по образцу.
Указания.
- Выделить и объединить диапазон ячеек B2:C2 и ввести текст в объединенную ячейку (см. рис. 4).
- Ввести текст в ячейки A2, D2, E2, F2 и G2 по образцу (см. рис. 4).
Примечание. Принудительный переход на новую строку в тексте ячейки осуществляется нажатием клавиш <Alt+Enter>.
- Ввод данных в столбцы A, B и C.
Указания.
- Ввести текст в ячейки таблицы с A3 по A11 по образцу.
- Активизировать ячейку B3. С клавиатуры ввести число 15,3 (см. образец рис. 4). После окончания ввода нажать <Enter>.
- Скопировать введенное в B3 значение в ячейки диапазонов B4:B11 и С3:C11 методом автозаполнения (см. «Примечание» ниже).
Примечание. Операция Автозаполнение позволяет быстро заполнить указанный диапазон ячеек повторяющимися значениями или значениями, изменяющимися по определенному правилу. С помощью мыши она реализуется следующим образом:
- выделить ячейку, в которой находятся введенные данные;
- навести указатель мыши на маркер автозаполнения (см. рис. 2);
- зажав левую/правую кнопку мыши, выделить протягиванием диапазон, который необходимо заполнить данными;
- отпустить левую/правую кнопку мыши (если была зажата левая кнопка мыши, то по окончании операции произойдет копирование содержимого первой ячейки, а если правая – то появится контекстное меню для более гибкого управления операцией автозаполнения).
|
Рис. 2 – Маркер автозаполнения.
|
5. Форматирование введенных данных по образцу.
Примечание. Для изменения параметров шрифта (цвет, начертание, размер), абзаца (отступ, выступ, выравнивание, межстрочный интервал) и самой ячейки (размеры, цвет и тип границ, цвет и тип заливки) используется диалоговое окно «Формат ячеек», которое вызывается следующими способами:
- вызвать контекстное меню для выделенной ячейки или диапазона ячеек и выбрать команду ;
- на Ленте на вкладке «Главная» в группе «Ячейки» щелкнуть по кнопке и в появившемся меню выбрать команду .
Некоторые вкладки д/о «Формат ячеек» вынесены на Ленту в виде соответствующих групп: «Шрифт», «Выравнивание», «Число».
Технология форматирования ячеек в Excel следующая.
- Выделить ячейку, содержимое которой нужно отформатировать (если параметры форматирования одинаковы для нескольких ячеек, то выделить диапазон, содержащий эти ячейки).
- Вызвать диалоговое окно «Формат ячеек».
- Назначить необходимые параметры форматирования.
- Завершить работу с диалоговым окном, щелкнув по кнопке «OK» для применения установленных настроек.
Указания.
- Для заголовка задания (диапазон A1:G1) назначить шрифт 16 пт, полужирное начертание, выравнивание в ячейке – по центру (относительно горизонтальных и вертикальных границ ячейки).
- Для шапки таблицы (ячейки диапазона A2:G2) назначить шрифт 11 пт, полужирное начертание, выравнивание – как в п. 5.1.
- Для текста в столбце A (диапазон A3:A11) назначить шрифт 11 пт, курсив, выравнивание относительно вертикальных границ ячейки – по левому краю, относительно горизонтальных границ – по центру.
- Каждому из чисел в ячейках диапазона C3:C11 задать формат, название которого указано в ячейке той же строки в столбце A (использовать вкладку «Число» диалогового окна «Формат ячеек» или группу «Число» на Ленте).
- Для диапазона ячеек A12:G12 назначить шрифт 10 пт, полужирное начертание, цвет заливки – желтый.
- Выделить диапазон A12:C12 и объединить, назначить объединенной ячейке выравнивание относительно вертикальных границ – по правому краю, относительно горизонтальных границ – по центру.
- Диапазонам ячеек D3:D11, E3:E11 и F3:F11 назначить формат «Числовой» с двумя знаками после запятой.
- Диапазону ячеек G3:G11 назначить «Краткий формат даты».
- Выделить диапазон ячеек A2:G12 и задать границы всем ячейкам в диапазоне (д/о «Формат ячеек» – вкладка «Границы» или Лента – вкладка «Главная» – группа «Шрифт» – кнопка «»).
6. Заполнение диапазона ячеек D3:D11 произвольными значениями чисел, не превышающих число 15,3 (с проверкой вводимых данных).
Указания.
- Выделить указанный диапазон ячеек. Вызвать диалоговое окно «Проверка вводимых значений» (Лента – вкладка «Данные» – «») и задать установки, как на рис. 3.
Рис. 3 – Вид вкладок окна «Проверка вводимых значений».
- В ячейки диапазона вручную ввести числа по образцу.
- Для проверки контроля ввода ввести в ячейку D6 значение, большее 15,3 (например, число 100). Excel выведет сообщение об ошибке.
7. Заполнение диапазона ячеек Е3:Е11 рядом действительных случайных чисел х[3; 15] с двумя фиксированными десятичными знаками после запятой.
Указания.
7.1. Активизировать ячейку E3 и ввести в нее формулу для вычисления случайного значения в заданном диапазоне. Для этого выполнить следующие действия.
- Ввести с клавиатуры знак =.
- Вызвать диалоговое окно «Мастер функций» (кнопка «Вставить функцию» в «Строке формул») и выбрать из списка встроенных функций СЛУЧМЕЖДУ (функция относится к категории математических). Завершить работу с данным диалоговым окном, щелкнув по кнопке «ОК».
- В открывшемся д/о «Аргументы функции» в поле «Нижн_граница» ввести число 3, в поле «Верхн_граница» ввести число 15.
- Для завершения ввода функции щелкнуть по кнопке «ОК» или нажать <Enter>.
7.3. Методом автозаполнения скопировать введенную в ячейку E3 формулу в остальные ячейки диапазона.
Внимание! Числовые значения диапазона Е3:Е11 могут не совпадать с аналогичными значениями из образца. Это связано со спецификой функции СЛУЧМЕЖДУ.
Примечание. Формулы в Excel используются для проведения вычислений, анализа или изменения информации. Ввод формулы в ячейку всегда начинается со знака = (равно), после знака указывается выражение, которое может содержать ссылки на ячейки, встроенные функции и знаки математических операций.
Технология ввода формулы в ячейку с использованием клавиатуры:
- активизировать ячейку, в которую вводится формула;
- набрать с клавиатуры знак = и саму формулу (формула не должна содержать пробелов);
- нажать <Enter>.
8. Заполнение диапазона ячеек F3:F11 упорядоченным рядом чисел х[3; 15] с шагом 1,5 по образцу.
Указания.
8.1. Выделить ячейку F3 и ввести число 3 (начальное значение ряда), затем нажать клавишу <Enter>.
8.2. Выделить диапазон F3:F11. Вызвать д/о «Прогрессия» (Лента – вкладка «Главная» – группа «Редактирование» – кнопка «» – команда «Прогрессия…»).
8.3. В появившемся д/о указать тип прогрессии (арифметическая) и задать указанный в задании шаг прогрессии. Закончить работу с диалоговым окном «Прогрессия» щелчком по кнопке «ОК». В результате диапазон ячеек F3:F11 заполнится значениями арифметической прогрессии от 3 до 15 с шагом приращения равным 1,5.
9. Заполнение диапазона G3:G11 рабочими днями мая месяца 2020 года с использованием метода автозаполнения с помощью окна «Прогрессия».
Указания.
9.1. В ячейку G3 ввести дату первого рабочего дня мая 2020 года. Нажать <Enter>.
9.2. Снова активизировать ячейку G3. Навести указатель мыши на маркер автозаполнения, зажать правую кнопку мыши и выделить диапазон ячеек до ячейки G11.
9.3. Отпустить правую кнопку мыши. Из появившегося контекстного меню выбрать команду «Прогрессия…» и в диалоговом окне установить необходимые настройки. Завершить работу с диалоговым окном «Прогрессия» щелчком по кнопке «ОК».
10. Расчет суммы в ячейках D12, E12 и F12 с помощью инструмента «Автосумма».
Примечание. Технология использования инструмента «Автосумма»:
- выделить ячейку, в которую помещается сумма;
- на «Ленте» открыть вкладку «Главная» и щелкнуть по кнопке «Автосумма» ;
- с помощью мыши выделить диапазон, содержащий значения для суммирования (нужный диапазон может быть выделен автоматически, тогда данный пункт не выполняется);
- нажать <Enter>.
Рис. 4 – Образец для выполнения задания.
Часть 2. Вычисления в электронной таблице MS Excel с использованием стандартных функций. Графическая обработка экономической информации.
1. Создание таблицы с исходными данными.
Указания.
1.1. Перейти на чистый рабочий лист рабочей книги и присвоить ему имя «Часть 2».
1.2. Ввести данные в диапазон A1:O13 по образцу рис. 5.
Рис. 5 – Образец для выполнения задания.
2. Рассчитать среднее значение величин в диапазоне ячеек С11:N11 с помощью функции СРЗНАЧ.
Указания.
2.1. Активизировать ячейку C11.
2.2. Ввести в выделенную ячейку формулу следующим образом:
- с клавиатуры ввести знак =;
- начать набирать с клавиатуры название функции СРЗНАЧ, пока не появится меню со списком доступных функций (см. рис. 6);
- с помощью клавиш управления курсором или выбрать требуемую функцию и нажать клавишу Tab;
- протягиванием ЛКМ выделить диапазон, содержащий значения для расчета среднего значения (C4:C10);
- закончить ввод формулы нажатием клавиши <Enter>. В ячейке C11 отобразится среднее значение длины окна сканирования, рассчитанное при помощи стандартной функции MS Excel.
2.3. Скопировать формулу из ячейки C11 в ячейки диапазона D11:N11 любым известным вам способом (например, методом автозаполнения).
2.4. Сравнить полученные результаты расчетов с образцом (см. рис. 7).
3. Рассчитать максимальное значение величин в диапазоне ячеек С12:N12 с помощью функции МАКС().
Указания.
3.1. Максимальные значения величин рассчитать самостоятельно.
4. Рассчитать минимальное значение величин в диапазоне ячеек С12:N12 с помощью функции МИН().
Указания.
4.1. Минимальные значения величин рассчитать самостоятельно.
5. Рассчитать гадуированную оценку (ячейки O4:O7) с помощью функции ЕСЛИ().
Примечание. Для расчета градуированной оценки использовать следующий алгоритм:
- устройству выставляется оценка «отлично», если его разрешение больше среднего значения, а скорость сканирования меньше среднего значения скорости;
- устройству выставляется оценка «хорошо», если его разрешение больше среднего значения и скорость сканирования больше среднего значения скорости;
- устройству выставляется оценка «удовл.», если его разрешение меньше среднего значения и скорость сканирования больше среднего значения скорости.
Указания.
5.1. Активизировать ячейку О4 и ввести в нее следующую формулу:
=ЕСЛИ(И(E4>$E$11;F4<$F$11);"отлично";ЕСЛИ(И(E4<$E$11;F4<$F$11);"хорошо";"удовлетв."))
Закончить ввод формулы нажатием клавиши <Enter>.
5.2. Скопировать содержимое ячейки О4 в диапазон ячеек О5:О10 любым известным вам способом.
5.3. Сравнить полученные результаты расчетов с образцом (см. рис. 7).
Примечание. При копировании формул в MS Excel ссылки на ячейки, содержащиеся в формуле, будут изменяться автоматически.
Относительная ссылка – ссылка, изменяющаяся при копировании.
MS Excel меняет ссылки в формуле по следующему правилу:
- если формула копируется в ячейки одного и того же столбца, то в ссылках изменяются номера строк;
- если формула копируется в ячейки одной и той же строки, то в ссылках изменяются названия столбцов;
- если формула копируется в произвольную ячейку, то в ссылках изменяются и названия столбцов, и номера строк.
Чтобы MS Excel не менял ссылки в формуле при копировании, необходимо использовать абсолютную адресацию.
Абсолютная ссылка – ссылка, не изменяющаяся при копировании формулы.
Для задания абсолютной адресации нужно в ссылке поставить знак $, при этом возможны три варианта:
- знак $ размещается только перед названием столбца (пример: $A1), тогда при копировании не будет изменяться название столбца;
- знак $ размещается только перед номером строки (пример: A$1), тогда при копировании не будет изменяться номер строки;
- знак $ размещается и перед названием столбца, и перед номером строки (пример: $A$1), тогда при копировании не будет изменяться ни название столбца, ни номер строки.
Чтобы быстро изменить способ адресации, можно установить курсор в ссылку между названием столбца и номером строки и последовательно нажимать клавишу <F4>.
Рис. 7 – Образец для выполнения задания.
6. Графически проанализировать динамику объема продаж сканеров STS-715, MSO 300 Sagem и BioLink U-Match 3.5 за период с 2005 по 2016 года.
Указания.
6.1. Выделить диапазоны ячеек, содержащие данные для осей абсцисс и ординат:
- методом протягивания ЛКМ выделить диапазон ячеек I3:N3 (значения, которые будут отложены по оси абсцисс ОХ);
- зажать клавишу <ctrl> и, удерживая ее, последовательно выделить диапазон ячеек I6:N6 и I8:N8 (значения, которые будут отложены по оси ординат ОY);
6.2. На ленте открыть вкладку «Вставка» и в группе «Диаграммы» выбрать тип «Точечная с гладкими кривыми и маркерами». Будет построена диаграмма как на рис. 8.
Рис. 8 – заготовка диаграммы, отражающей динамику продаж сканеров STS-715 и MSO 300 Sagem.
6.3. Добавить название диаграммы (см. рис. 9). Для этого:
- выделить диаграмму одиночным щелчком ЛКМ (на ленте появятся дополнительные вкладки по работе с диаграммами: «Конструктор», «Макет», «Формат»);
- перейти на вкладку «Макет» и в группе «Подписи» выбрать команду «Название диаграммы» (вариант размещения названия выбрать в соответствии с образцом рис. 9);
- в появившейся на диаграмме области ввести название (см. рис. 9).
6.4. Добавить подписи к осям абсцисс и ординат (см. рис. 9). Для этого:
- перейти на вкладку «Макет» и в группе «Подписи» выбрать команду «Название осей» (варианты размещения названий для осей выбрать в соответствии с образцом рис. 9);
6.5. Изменить названия рядов, чтобы легенда стала информативной (см. рис. 9). Для этого:
- перейти на вкладку «Конструктор» и в группе «Данные» выполнить команду «Выбрать данные»;
- в появившемся д/о «Выбор источника данных» в области «Элементы легенды (ряды)» одиночным щелчком ЛКМ выбрать «Ряд 1» и щелкнуть по кнопке «Изменить»;
- в открывшемся д/о «Изменение ряда» щелкнуть по кнопке в поле «Имя ряда» (при этом д/о «Изменение ряда» свернется до размеров соответствующего поля) и выделить ячейку, содержащую название сканера STS-715 (ячейка B6);
- развернуть д/о «Изменение ряда» повторным щелчком ЛКМ по кнопке и нажать кнопку «ОК»;
- для второго ряда указать имя самостоятельно;
- завершить работу с д/о «Выбор источника данных» щелчком по кнопке «ОК».
Рис. 9 –диаграмма, отражающая динамику продаж сканеров STS-715 и MSO 300 Sagem.
6.6. Добавить на диаграмму данные для сканера BioLink U-Match 3.5 (см рис. 10). Для этого:
- перейти на вкладку «Конструктор» и в группе «Данные» выполнить команду «Выбрать данные»;
- в появившемся д/о «Выбор источника данных» в области «Элементы легенды (ряды)» щелкнуть по кнопке «Добавить»;
- свернуть открывшееся д/о «Изменение ряда» по полю «Имя ряда» нажатием соответствующей кнопки , выделить ячейку, содержащую название сканера BioLink U-Match 3.5 (ячейка B4) и развернуть д/о «Изменение ряда» повторным щелчком по кнопке ;
- свернуть д/о «Изменение ряда» по полю «Значения Х» нажатием соответствующей кнопки , выделить диапазон ячеек, содержащих года (ячейки I3:N3) и развернуть д/о «Изменение ряда» повторным щелчком по кнопке ;
- свернуть д/о «Изменение ряда» по полю «Значения Y» нажатием соответствующей кнопки , выделить диапазон ячеек, содержащих объем продаж заданного сканера (ячейки I4:N4) и развернуть д/о «Изменение ряда» повторным щелчком по кнопке ;
- завершить работу с д/о «Изменение ряда» щелчком по кнопке «ОК»;
- завершить работу с д/о «Выбор источника данных» щелчком по кнопке «ОК».
Рис. 10 –диаграмма, отражающая динамику продаж сканеров STS-715, MSO 300 Sagem и BioLink U-Match 3.5.
7. Графически проанализировать продажи сканеров в 2008.
Указания.
7.1. Создать заготовку диаграммы. Для этого:
- выделить диапазон ячеек, содержащий данные для построения диаграммы (ячейки K4:K10);
- на ленте открыть вкладку «Вставка» и выбрать вариант диаграммы «Круговая».
7.2. Добавить название диаграммы (см. п. 6.3).
7.3. Изменить подписи к горизонтальной оси, чтобы легенда стала информативной. Для этого:
- перейти на вкладку «Конструктор» и в группе «Данные» выполнить команду «Выбрать данные»;
- в появившемся д/о «Выбор источника данных» в области «Подписи горизонтальной оси (категории)» щелкнуть по кнопке «Изменить»;
- в открывшемся д/о «Подписи оси» в поле «Диапазон подписей оси:» указать ссылки на диапазон ячеек, содержащих названия сканеров (ячейки B4:B10);
- завершить работу с д/о «Подписи оси» щелчком по кнопке «ОК»;
- завершить работу с д/о «Выбор источника данных» щелчком по кнопке «ОК».
7.4. Выбрать макет диаграммы. Для этого:
- на ленте открыть вкладку «Конструктор» и в группе «Макеты диаграмм» выбрать вариант, на котором отображается название диаграммы, легенда и значения в процентах (Макет 6).
7.5. Выбрать стиль диаграммы. Для этого:
- на ленте открыть вкладку «Конструктор» и в группе «Стили диаграмм» выбрать «Стиль 26».
Рис. 11 –диаграмма, отражающая суммарные доли продаж сканеров в 2008г.
IV. контрольные вопРОСЫ
- Как называется документ, созданный средствами табличного процессора MS Excel? Какие четыре способа создания рабочей книги MS Excel Вы знаете, опишите их.
- Как называется и для чего служит кнопка , расположенная на «Ленте» на вкладке «Главная». Опишите технологию использования этой кнопки.
- Как ввести текст в ячейку электронной таблицы в несколько строк (укажите два способа).
- Для чего служит операция «автозаполнение»? Где находится «маркер автозаполнения»? Два способа реализации операции автозаполнения (ЛКМ и ПКМ).
- Как в Excel назначить проверку вводимых данных? Для чего используется такая возможность? Для каких типов данных можно назначить проверку?
- Опишите два варианта ввода формул в Excel.
- Запишите формулы, выполняющие следующие действия:
- выбор случайного числа в диапазоне от 0 до 1;
- выбор случайного числа в диапазоне [0; 7].
- Опишите стандартную технологию вычисления итоговой суммы с помощью инструмента «Автосумма».
- Для чего служит д/о «Прогрессия»? Укажите два способа вызова данного д/о. Какие типы прогрессий будут созданы при выборе следующих настроек в д/о «Прогрессия»: арифметическая, геометрическая, даты, автозаполнение?
- Что такое встроенная функция в Excel? К какой категории функций относится функция СЛУЧМЕЖДУ? Опишите два способа добавления функций в формулу.
11
Технология ввода данных в ячейку (три этапа).
Маркер автозаполнения
Рис. 6 – меню с вариантами стандартных функций.
-