Лабораторная работа № 2. Графические возможности электронных таблиц MS Excel

Цель работы: приобрести навыки визуализации данных средствами MS Excel.

Материал для работы: файлы-заготовки ЛР2-1.xlsx, ЛР2-2.xlsx.

Результат работы: файл ЛР2-1_ФАМ.xlsx, ЛР2-2_ФАМ.xlsx.

Выполнение работы

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

Построение диаграмм. Диаграмма в MS Excel – это способ наглядного представления данных как в виде собственно диаграмм, так гистограмм и графиков. Для построения диаграммы на основании данных таблицы необходимо выделить данные таблицы (отдельные строки и столбцы или диапазон ячеек) и выполнить соответствующую команду на вкладке Вставка в группе Диаграммы. Редактирование диаграммы осуществляется командами на дополнительного меню Работа с диаграммами.

Передвигаясь по листам файла ЛР2.xlsх, постройте графики, гистограммы, диаграммы различного вида.

 откройте файл и перейдите на лист Повышение квалификации;

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

 выделите диапазон ячеек A2:B13, затем выполните последовательность действий вкладка Вставка/Диаграммы/Гистограмма. На экране появится основа будущей гистограммы и вкладки дополнительного меню Работа с диаграммами;

 щелкните по элементу Название диаграммы и наберите:

Количество изучаемых предметов по направлениям повышения квалификации

 примените полужирное начертание шрифта, используя команды вкладки

Главная;

 добавьте подписи данных, т.к. высота многих столбцов не совпадает с делениями вертикальной оси. Выберите вкладка Макет/Подписи/Подписи данных. Для подписей примените Цвет текста – красный;

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

На листе Статистика образования постройте график динамики численности детей за период с 2000 по 2017 гг. Для этого:

 выделите диапазон ячеек A1:R1 (подписи), зажмите клавишу Сtrl (используемую для выделения несмежных диапазонов ячеек) и выделите диапазон ячеек A3:R3 (данные для построения);

 выберите Вставка/Диаграммы/График, нажмите на треугольник на пиктограмме График, выберите тип График с маркерами, отображающий развитие процесса с течением времени (по датам или годам), что целесообразно в данном случае (рисунок 7);

Рисунок 7 – Порядок построения графика.

удалите легенду, дублирующую название диаграммы;

измените название на более точное: Динамика численности детей в период с 2000 по 2017гг.;

 используя вкладку Макет, добавьте основные вертикальные линии, подписи данных, а используя вкладку Формат, цвет заливки области построения (рисунок. 8).

Рисунок 8 – Окончательный вид графика.

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

 перейдите на лист Реклама. Предварительно вычислите текущий процент выполнения выделенных годовых затрат на категорию рекламной деятельности. Для этого в ячейке Е2 наберите формулу =D2*100%/C2. Выполните автозаполнение для расчета всех категорий;

 определите процент затрат на рекламу по составляющим еѐ категориям, для этого введите в ячейку F2 формулу =C2*100%/$C$9;

 для построения круговой диаграммы выделите блок ячеек B1:C8, выполните Вставка/Диаграммы/Круговая. На лист таблицы вставится диаграмма, которую необходимо редактировать;

 на дополнительной вкладке Работы с диаграммами/Макет, откройте список Подписи данных, выберите Дополнительные параметры подписей данных, установите в области Включить в подписи – параметры значения и доли, в области Положение подписи – У вершины, снаружи.

 легенду (расшифровку цветовых составляющих диаграммы или текстовых данных таблицы) расположите ниже изображений сегментов диаграммы. Измените название диаграммы. Круговая диаграмма примет вид, как на рисунке 9.

Рисунок 9 – Круговая диаграмма.

На листе Статистика браков-разводов постройте лепестковую диаграмму, отражающую количественные показатели траков/разводов по годам.

перейдите на лист Статистика браков-разводов, выделите диапазон ячеек B3:C21, выполните Вставка/Диаграммы/Создать диаграмму/ Лепестковая/Заполненная лепестковая;

 для добавление временного интервала, приведенного в таблице, выполните Конструктор/Выбрать данные/ Подписи по горизонтальной оси категории) /Изменить;

 выберите диапазон, щелкнув по «красной» стрелке, затем укажите в таблице диапазон ячеек А4:А21. Вокруг контура окружности вместо чисел появятся годы из таблицы;

 измените минимальное значение оси значений (по умолчанию стоит 0). Щелчком правой кнопки мыши в области контура значений оси вызовите контекстное меню контура оси, затем выберите команду Формат оси. В области Параметры оси установите минимальное значение фиксированное 10000. Остальные изменения в диаграмму внесите самостоятельно. Лепестковая диаграмма, отображающая статистику браков и разводов, за указанный период с 1995 по 2016 год примет вид, как на рисунке 10.

Рисунок 10 – Лепестковая диаграмма.

На листе Библиотечный фонд отобразите графически количественные показатели развития публичных библиотек по областям и по Республике Беларусь с помощью смешанной (комбинированной) диаграммы.

 выделите диапазон ячеек В4:Н10 и постройте вид диаграммы – гистограммы с группировкой (но не объемную).

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

выделите ряд с наибольшими значениями. Выберите в контекстном меню ряда Изменить тип диаграммы для ряда, выберите тип диаграммы – график с маркерами;

 расположите значения ряда по вспомогательной оси, выбрав команду Формат ряда данных контекстного меню ряда. В появившемся меню выберите Параметры ряда параметр По вспомогательной оси. Диаграмма, примет вид, как показано на рисунке 11.

Рисунок 11 – Смешанная диаграмма.

Условное форматирование. Условное форматирование является удобным инструментом для анализа данных и наглядного представления результатов. Для выделения данных таблицы по условию применяют команду Условное форматирование на вкладке Главная.

Выделите цветом результаты сдачи экзаменационной сессии студентами. откройте         файл      ЛР2-2.xlsx.     Перейдите     на      лист      Условное

форматирование1;

 для заполнения столбца Средний балл студента в ячейке F2 наберите формулу =СРЗНАЧ(B2:E2), где B2:E2 – аргумент функции, указывающий диапазон ячеек с оценками первого студента. СРЗНАЧ арифметическая функция расчета среднего значения из указанного диапазона ячеек. Для получения результата нажмите Enter;

 средние баллы остальных студентов получите с помощью автозаполнения;

 для заполнения столбца Размер стипендии (стипендия студенту выдается, если все баллы по предметам выше трех и средний балл больше или равен 4) используйте логические функции ЕСЛИ и И. Впишите в ячейку G2 формулу

=ЕСЛИ(И(B2>3;C2>3;D2>3;E2>3;F2>=4);"35";"нет стипендии")

выполните автозаполнение вниз для получения сведений по всем студентам;

 для заполнения столбца повышенная стипендия используйте логическую функцию ЕСЛИ, в ячейку H2 введите формулу =ЕСЛИ(F2>=4,5;G2+25;0). Выполните автозаполнение для всех студентов;

 заполните данными строки Количество 5 по предмету. Используйте функцию СЧЕТЕСЛИ, которая считает в указанном диапазоне количество ячеек, удовлетворяющих заданному условию. В ячейку В8 введите формулу

=СЧЁТЕСЛИ(B2:B7;5). Выполните автозаполнение для всех предметов;

 для заполнения строки Средний балл по предмету в ячейку В9 введите формулу =СРЗНАЧ(B2:B7). Выполните автозаполнение для всех предметов;

 выделите    диапазон    ячеек    таблицы    А2:Е7.    Выберите    вкладка

Главная/область Стили/команда Условное форматирование;

 в раскрывающемся списке способов задания условия форматирования выберите Создать правило. Из предложенных типов правил рациональней выбрать первое правило – форматировать все ячейки на основании их значений. Установите Стиль формата – трехцветная шкала, Минимальное значение – 2, Максимальное значение – 5;

 измените согласно требованиям заданный по умолчанию цвет форматирования, установите: только на «отлично» красным (максимальное значение),    «задолженники»      синим (минимальное значение). Цвета промежуточных значений будут приближаться к заданным цветам максимального и минимального значений. Нажмите ОК для закрытия окна диалога и выполнения условного форматирования выделенных ячеек таблицы;

 к ячейкам столбца Средний балл студента F2:F7 (предварительно выделите заданный диапазон) примените способ условного форматирования Наборы значков/Фигуры/3 сигнала светофора;

 выполните     дополнительно     форматирование     цветовой     заливкой

Условное форматирование/Гистограммы/Градиентная заливка;

 аналогично примените условное форматирование к строке «Количество 5 по предмету».

Задания для самостоятельной работы

1.       На листе НИРС БГУ постройте объемную гистограмму, отражающую статистику публикация и выступлений на конференциях по годам.

2.       На листе Статистика образования постройте график, отображающий динамику численности обучающихся в ПТУ, ССУ и УВО по годам.

3.       На листе Реклама постройте круговую диаграмму, отражающую процент затрат по категориям на текущую дату.

4.       На листе Статистика населения построить лепестковую диаграмму, отражающую соотношение городского и сельского населения с 2010-2017 гг.

5.       На листе Библиотечный фонд с помощью смешанного вида диаграмм отобразите численность пользователей библиотечным фондом в течении 2010- 2016 гг.

6.       ¶ Изучите технологию построения линейчатых и кольцевых диаграмм. На листах Численность населения и Коммунальные расходы постройте соответствующие диаграммы.

7.       На листе Условное форматирование2 файла ЛР2-2.xlsx с помощью условного форматирования выделите: самые холодные дни синим, самые тѐплые дни красным, дни с нулевой температурой жѐлтым цветом. Определите количественные показатели в строках 7-15.

8.       ¶ Изучите технологию вставки спарклайнов. На листе Спарклайны файла ЛР2-2.xlsx постройте различные виды спраклайнов, отражающих динамику процессов.