Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
Лабораторная работа №4
Применение электронных таблиц Microsoft Excel в вычислениях
Цель работы: обучиться технологии обработки числовых данных при помощи табличного процессора.
Ход выполнения работы
Задание. Необходимо выполнить расчет и анализ показателей производства по месяцам, а также провести итоговые расчеты за год.
Для каждого месяца следует отразить в таблице:
Возможный вид таблицы, соответствующей поставленной задаче, приведен ниже.
Необходимо для отдельных совокупностей табличных данных представить диаграммы, наглядно отражающие содержание данных, а также их взаимосвязи и взаимозависимости.
Разделим решение ЗАДАЧИ на этапы:
1. Введите в ячейку А1 заголовок таблицы Показатели производства.
2. Сохраните создаваемую таблицу в личной папке, дав файлу подходящее имя, например, lab1 (будет создан файл lab1.xls). Не забывайте регулярно сохранять файл во избежание потери результатов работы.
3. Выделите диапазон ячеек A4:G4 и подготовьте его для ввода заголовков столбцов таблицы, занимающих несколько строк. Для этого из строки меню или контекстного меню вызовите диалоговое окно Формат ячеек, выберите вкладку Выравнивание, а в ней - выравнивание по центру по горизонтали и по вертикали и режим переноса по словам. После того как это сделано, введите в ячейки В4, D4, Е4, F4, G4 названия для столбцов таблицы (соответственно):
Месяцы, План выпуска, Фактически выпущено, Процент выполнения плана, Выполнено в % к фактически выпущенному за год.
4. Измените ширину столбцов так, чтобы заголовки столбцов таблицы приняли такой же вид, как на рисунке. Для этого подведите курсор мыши к вертикальному разделителю между заголовками столбцов рабочего листа, например, к разделителю между столбцами Q и Н. Курсор изменит форму и примет вид крестика с горизонтальными стрелками. Нажмите левую кнопку мыши и, не отпуская ее, передвиньте этот разделитель вправо или влево до необходимой ширины, отпустите кнопку мыши.
5. Далее необходимо заполнить три столбца под общим заголовком «Месяцы». Хотя на первый взгляд эти столбцы дублируют друг-друга, всего лишь по-разному графически обозначая одни и те же временные промежутки, существенное отличие все же есть - здесь использованы альтернативные способы работы в режиме Автозаполнения: построение числовых рядов, рядов из дат, использование стандартного списка. Поэтому рекомендуется в учебных целях применить все три способа, отрабатывая навыки работы в этом режиме.
6. В диапазоне ячеек А5:А16 постройте числовой ряд со значениями от 1 до 12.
7. В диапазоне ячеек В5:В16 постройте ряд из дат - последних чисел каждого месяца:
8. Для диапазона ячеек С5:С16 воспользуйтесь стандартным списком из названий месяцев:
1. В ячейке В18 наберите текст Итого за год.
2. В ячейке D18 необходимо получить значение планового задания по выпуску на год — сумму значений в диапазоне D5:D16. Для этого лучше всего воспользоваться режимом Автосуммирования:
3. В ячейке Е18 для вычисления значения суммы фактически выпущенной продукции за год аналогичным образом постройте формулу =СУММ(Е5:Е16)
4. Введите в ячейку F5 формулу для вычисления процента выполнения плана за месяц: =E5/D5
Внимание! Не набирайте на клавиатуре адреса ячеек, а получайте их щелчком левой кнопки мыши на соответствующей ячейке.
5. Выделите ячейку F5 и выполните автоматическое заполнение формулами диапазона ячеек F5:F16, используя маркер заполнения.
6. Выполните форматирование диапазона ячеек F5:F16, представив данные в процентном формате. Для этого выделите диапазон F5:F16 и нажмите кнопку Процентный формат на панели инструментов Форматирование, a затем дважды - кнопку Увеличить разрядность на той же панели (это позволит вывести два десятичных знака в значении процента).
7. В ячейку F18 введите формулу для вычисления процента выполнения плана за год =Е18/D18
Отформатируйте эту ячейку в процентном формате с двумя цифрами дробной части. Это можно сделать описанным выше способом, а можно скопировать формат, используя кнопку Формат по образцу (форматная кисть) панели инструментов Стандартная.
8. В диапазоне ячеек G5:G16 предстоит вычислить для каждого месяца; его долю (в процентах) в годовом выпуске, которая вычисляется как отношение выпущенного в каждом месяце к выпущенному за год. Так как во всех формулах диапазона в качестве делителя выступает одна и та же ячейка (сумма за год), то в формуле адрес этой ячейки должен быть задан в виде абсолютной ссылки, чтобы он не изменялся при копировании формулы. Формула в ячейке G5 должна иметь вид: =Е5/$Е$18
Для получения абсолютной ссылки $Е$18 необходимо поместить в формулу ссылку Е18 и затем нажать клавишу F4.
9. Выделите ячейку G5 и выполните автоматическое заполнение формулами диапазона ячеек G5:G16, используя маркер заполнения. Выполните форматирование диапазона G5:G16 в процентном формате с двумя цифрами дробной части.
10. Сохраните текущее состояние таблицы.
1. Можно выполнить условное форматирование для диапазона F5:F16 с целью выделения тех ячеек, в которых значение меньше 1 (или, что то же самое, меньше 100 %). Для этого выделите диапазон F5:F16 и выполните команду Формат/Условное форматирование...; появится диалоговое окно Условное форматирование.
2. Раскройте список во втором поле и выберите строку «меньше», в третье поле введите значение 1 или 100 %, нажмите кнопку Формат..., появится диалоговое окно Формат ячеек с вкладками Шрифт, Граница, Вид. Определите формат для значений, удовлетворяющих заданному условию, например, измените цвет и начертание. Закройте диалоговые окна нажатием кнопки ОК.
3. Значения в диапазоне ячеек G5:G16 можно выровнять по центру.
4. Расположите заголовок таблицы Показатели производства по центру таблицы. Для этого выделите диапазон A1:G1 и нажмите кнопку Объединить и поместить в центре. Можно изменить размер шрифта заголовка и его начертание.
5. Так как при печати таблицы линии сетки не видны, то для лучшего восприятия таблицы необходимо обвести ее рамками. Для проведения линий и рамок можно воспользоваться кнопкой Границы на панели Форматирование, но большие возможности дает вкладка Границы диалогового окна Формат ячеек. Рекомендуется сначала провести все внутренние рамки и линии, а затем - внешние, при этом можно изменить тип и толщину линий.
6. При желании можно изменить шрифт, его размер и начертание как для числовых данных, так и для текстов в таблице.
7. Можно изменить цвет фона и цвет шрифта отдельных элементов таблицы.
8. Сохраните текущее состояние таблицы.
Графическое представление данных
Построение гистограммы:
1. Выделите диапазон ячеек С4:Е16, содержащий два ряда числовых данных: план выпуска и фактический выпуск по месяцам, а также ряды с названиями строк и заголовками столбцов. Названия в строках будут использоваться в качестве обозначений на оси X (категории), а заголовки столбцов - в качестве легенды.
2. Вызовите Мастер диаграмм.
3. В диалоговом окне первого шага Мастера диаграмм во вкладке Стандартные в окне Тип выберите и выделите тип Гистограмма, а в окне Вид - первую картинку в первом ряду (обычная плоская гистограмма). Нажав и удерживая кнопку Просмотр результата, можно увидеть образец диаграммы. Перейдите к следующему шагу, нажав кнопку Далее.
4. На втором шаге Мастер диаграмм выводит образец диаграммы и сообщает, что гистограмма для выделенных данных сформирована стандартным образом: ряды данных - в столбцах. Это соответствует представлению данных задачи, поэтому перейдите к следующему шагу, нажав кнопку Далее.
5. На третьем шаге во вкладке Заголовки определите название диаграммы и названия для осей: в поле ввода Название диаграммы введите текст Показатели производства, в поле Ось X (Категорий) - Месяцы, в поле Ось Y (Значений) - Количество, во вкладке Легенда включите переключатель Добавить легенду и укажите место размещения легенды. Внесенные изменения будут сразу же отражены в образце диаграммы. Во вкладке Подписи данных можно выбрать режим, в котором на диаграмме будут показываться значения (по умолчанию значения не выводятся). Установив необходимые параметры, перейдите к следующему шагу.
6. На последнем шаге выберите место размещения диаграммы (лучше на имеющемся листе, а не на отдельном). Нажмите кнопку Готово.
7. Обрамленная маркерами диаграмма появится на рабочем листе. Переместите ее, если необходимо, захватив мышью в области диаграммы.
8. Используя маркеры, измените размеры области диаграммы для лучшего размещения объектов диаграммы.
9. Выполните редактирование диаграммы и, если необходимо, форматирование для сбалансированности объектов диаграммы и лучшего восприятия данных.
Построение круговой диаграммы:
1. Выделите блок ячеек, состоящий из двух несмежных столбцов С4:С16 и Е4:Е16, содержащих соответственно названия месяцев (для оформления диаграммы) и фактический выпуск по месяцам (собственно данные). Несмежные столбцы выделяются при нажатой клавише Ctrl.
2. Вызовите Мастер диаграмм.
3. На первом шаге Мастера диаграмм для типа Круговая выберите вид, соответствующий первой картинке во втором ряду, посмотрите на образец. Перейдите к следующему шагу.
4. Второй шаг соответствует размещению данных, вмешательства не требуется. Перейдите к следующему шагу.
5. На третьем шаге во вкладке Заголовок можете ввести или изменить название диаграммы. Во вкладке Легенда включите переключатель Добавить легенду (если он не включен) и выберите место размещения легенды. Во вкладке Подписи данных выберите переключатель доля (в этом случае около каждого сектора диаграммы будет выводиться доля в процентах) или категория и доля (тогда будут выводиться названия месяцев и доля в процентах). Переключатель Линии выноски отключите. Посмотрите на образец. Перейдите к следующему шагу.
6. Выберите место размещения диаграммы на имеющемся листе, нажмите кнопку Готово.
7. Переместите диаграмму в подходящее место листа. Если необходимо, то измените ее размеры и выполните редактирование отдельных элементов диаграммы.
8. Если секторы круга на диаграмме оказались маленького размера, то это значит, что необходимо увеличить размер области построения диаграммы. Для этого выполните следующие действия:
•Перемещая курсор мыши и следя за подсказкой к нему, постарайтесь попасть на область построения диаграммы (это чуть выше границы сектора).
•Нажмите левую кнопку мыши; область построения диаграммы окажется заключенной в квадрат с маркерами в углах.
•Потянув за любой из маркеров, измените размер области диаграммы; захватив мышью выделенную область, можно переместить ее в области диаграммы.
9. Отформатируйте на круговой диаграмме подписи данных.
•Щелкните на одной из подписей. Все подписи как объект диаграммы будут выделены и обрамлены маркерами.
•Значении долей выводятся в процентном формате как округленные целые числа. Добавьте в процентный формат две цифры дробной части. Для этого щелкните два раза на кнопке Увеличить разрядность.
•Сравните значения процентов, выведенные на диаграмме, со значениями в столбце таблицы с заголовком «Выполнено в процентах к фактически выпущенному за год». Значения процентов должны совпадать.
•Правой кнопкой вызовите контекстное меню.
•Выберите пункт Формат подписей данных... .
•В открывшемся диалоговом окне, выбирая вкладки Вид, Выравнивание, Шрифт, можно изменить формат подписей для категорий (названий месяцев).
10. Можно отформатировать заголовок диаграммы и легенду.
1. Выделите диапазон ячеек, состоящий из двух несмежных столбцов С4:С16 и Е4:Е16, содержащих названия месяцев и фактический выпуск по месяцам (несмежные столбцы выделите при нажатой клавише Ctrl).
2. Вызовите Мастер диаграмм.
3. На первом шаге Мастера диаграмм для типа График выберите вид, соответствующий первой картинке во втором ряду, посмотрите на образец. Перейдите к следующему шагу.
4. Выполните остальные шаги Мастера диаграмм.
5. Отредактируйте диаграмму, изменяя размеры и шрифты для лучшего представления данных.
1. Выделите диапазон ячеек C4:F16, содержащий заголовки строк, столбцов и две группы разнотипных рядов данных: одна группа- значения планового и фактического выпуска, другая - процент выполнения плана по месяцам.
2. Вызовите Мастер диаграмм.
3. На первом шаге Мастера диаграмм выберите вкладку Нестандартные, а в ней тип График/Гистограмма 2. Этот тип обеспечивает использование гистограммы для одного ряда данных, графика — для другого и наличие вспомогательной оси.
4. Выполните остальные шаги Мастера диаграмм. Отредактируйте диаграмму.
Контрольные вопросы