Условное форматирование

61. С помощью условного форматирования данных можно быстро определить и продемонстрировать важные тенденции и отклонения в данных.

Рассмотрим возможности условного форматирования с гистограммой, отображающей пропорциональное значение величин в диапазоне ячеек G3: G10:

• выделить диапазон ячеек G3: G10;

• на вкладке Главная в группе Стили нажать Условное форматирование, выбрав Гистограммы/Градиентная заливка/Оранжевая гистограмма.

Используем возможности условного форматирования для диапазона ячеек J3:J9. Если значение суммы З/П к выдаче меньше прожиточного минимума, то необходимо значение вывести красным цветом с двойным подчеркиванием, иначе значение выводить синим цветом.

Для этого необходимо:

• выделить диапазон ячеек J3:J9;

• на вкладке Главная в группе Стили нажать Условное форматирование;

• в диалоговом окне команды Условное форматирование выбрать Правила выделения ячеек и выбрать Другие правила;

• в диалоговом окне Создание правила форматирования выбрать Значение ячейки «меньше» и, нажав кнопку Свернуть диалоговое окно _, ввести ссылку на ячейку $F$27 щелчком мыши;

• щелкнуть по кнопке Формат;

• в диалоговом окне Формат ячеек на вкладке Шрифт в списке Подчеркивание выбрать «двойное по значению», а в списке Цвет выбрать «красный»;

• нажать ОК;

• в диалоговом окне команды Условное форматирование выбрать Правила выделения ячеек, затем выбрать Другие правила и создать еще одно условие – операцию «больше или равно», со ссылкой на ячейку $F$27;

• щелкнуть по кнопке Формат;

• в диалоговом окне Формат ячеек на вкладке Шрифт в списке Цвет выбрать синий;

• дважды нажать OK.

62. Для диапазона ячеек G3:G9 установите следующие форматы: если работник проработал целый месяц, вывести значение черным цветом, если проработал меньше месяца – вывести значение красным цветом с одинарным подчеркиванием.

63. Сохраните результаты лабораторной работы в файле lab1.xlsx.

ЛАБОРАТОРНАЯ РАБОТА № 2

Графическое представление табличных данных

Цель лабораторной работы:

Лабораторная работа служит для получения практических навыков по изучению следующих тем:

• создание диаграмм разных типов на основе табличных данных;

• настройка и редактирование диаграмм;

• применение диаграмм для анализа и прогнозирования данных.

Основные сведения о методах создания диаграмм

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

Существуют два варианта размещения диаграмм:

• вставка диаграммы в лист непосредственно (внедренная диаграмма);

• создание диаграммы на новом листе рабочей книги.

Алгоритм создания диаграммы:

1. Создаем таблицу данных или выделяем необходимый диапазон ячеек, динамическое изменение которых мы хотим отобразить в диаграмме.

2.  На вкладке Вставка в группе Диаграммы выбираем подходящий тип диаграммы.

3. На рабочем листе появиться диаграмма, которую можно легко перемещать и изменять ее размеры.

4. Для настройки внешнего вида диаграммы можно пользоваться тремя управляющими кнопками: Элементы, Стили и Фильтры диаграммы

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

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

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

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

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

Каждый тип может иметь несколько вариантов (подтипов). Кроме того, имеются 20 типов нестандартных диаграмм.

При работе с диаграммами используется специальная терминология:

Ряд данных – группа связанных точек данных диаграммы, отображающая значение строк или столбцов листа. Каждый ряд данных отображается по-своему. На диаграмме может быть отображен один или несколько рядов данных Данные одного ряда для большинства типов диаграмм закрашиваются одним цветом. На круговой диаграмме отображается только один ряд данных, при этом сектора диаграммы окрашиваются разными цветами.

Категория данных – понятие, взаимосвязанное с рядом данных. Если в качестве рядов данных выбраны столбцы таблицы, то категориями будут называться строки и наоборот. Обычно названия категорий располагаются вдоль оси Х.

Параметры диаграммы:

• заголовки;

• оси;

• линии сетки;

• легенда;

• подписи данных;

• таблица данных.

Заголовки содержат названия различных элементов диаграммы:

• заголовок диаграммы;

• название горизонтальной оси категорий (ось Х);

• название вертикальной оси значений (ось Y);

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

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

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

Область диаграммы – это вся диаграмма, вместе со всеми ее элементами.

Область построения – это область, ограниченная осями и содержащая все ряды диаграммы.

EXCEL обладает достаточно мощными средствами по настройке и редактированию диаграмм, такими как:

• изменение диапазонов данных, добавление новых или удаление существующих;

• выбор линий сетки;

• определение размера и расположения легенды;

• изменение места пересечения осей, корректировка масштаба осей;

• добавление заголовков к осям и диаграмме, размещение текста в диаграмме и т.д.

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

1. Запустите программу MS Excel 2013.

Построение и редактирование гистограммы

2. Загрузите файл lab1.xlsx с рабочей книгой, созданной в первой лабораторной работе.

3. Скопируйте таблицу «Лицевой счет» (диапазон ячеек A1:J9) в буфер обмена.

4. Добавьте в Книгу Excel новый лист, нажав на кнопку с плюсиком слева от ярлыка Лист1 внизу экрана. Перейдите на новый рабочий лист (Лист2), установите курсор в ячейку A1, вызовите контекстное меню щелчком правой клавиши мыши и выберите команду Специальная вставка. В диалоговом окне команды установите переключатель значения и форматы чисел, нажмите ОК. Таблица «Лицевой счет» будет вставлена в рабочий лист, но при этом все формулы в ней будут заменены значениями.

5. Для нанесения на диаграмму выделите несмежные диапазоны ячеек с фамилиями, а также с начисленными суммами, включая заголовки столбцов (B2:B9, H2:H9).

6. Постройте трехмерную гистограмму для сравнительного анализа начисленных сумм всех работников предприятия. Для этого на вкладке Вставка в группе Диаграммы щелкните по маленькой стрелочке на ленте справа от надписи Диаграммы и на вкладке Все диаграммы выберите Объемную Гистограмму с группировкой. Диаграмму можно легко перемещать, а также изменять ее размеры.

Примечание. При создании диаграммы на ленте появляется кнопка Работа с диаграммами, которая содержит вкладки Конструктор и Формат. Если щелкнуть мышью вне диаграммы, кнопка Работа с диаграммами исчезнет. Чтобы вывести ее на экран снова, нужно щелкнуть в области диаграммы.

7. Добавьте на диаграмму еще один ряд данных с удержанными суммами. Для этого:

• щелкните по диаграмме – в правой ее части появятся три управляющих кнопки:

• щелкните по кнопке Фильтр , позволяющей выбрать, какие именно данные будут отображены на диаграмме, и в появившемся окне нажмите Выбрать данные;

• в диалоговом окне Выбор источника данных нажмите кнопку Добавить;

• в окне Изменение ряда введите имя ряда, щелкнув по ячейке таблицы с заголовком «Удержано»;

• введите значения, выделив в таблице диапазон ячеек I3:I9;

• два раза нажмите ОК.

8. Добавьте на диаграмму названия самой диаграммы, а также названия ее осей. Для этого:

• щелкните по кнопке Элементы диаграммы , в появившемся окне выберите Название диаграммы, Название осей, Таблица данных;

• введите название диаграммы «Результаты расчетов З/П по предприятию за текущий месяц»;

• по горизонтальной оси введите название «Фамилии работников», а по вертикальной – «Сумма (руб.)»;

• в окне Элементы диаграммы выберите и удалите Подписи данных и Легенду.

9. Переместите диаграмму на отдельный лист. Для этого щелкните правой клавишей мыши по рамке диаграммы, из контекстного меню выберите Переместить диаграмму и затем На отдельном листе. Нажмите ОК.

10. Отредактируйте перемещенную диаграмму, расположенную на листе «Диа­грамма1»:

10.1. Измените стиль диаграммы. Для этого щелкните по кнопке Стили диаграммы и выберите Стиль 9.

10.2. Измените цвет ряда данных «Начислено». Для этого щелкните правой клавишей мыши по любому элементу этого ряда, из контекстного меню выберите Формат ряда данных, затем Заливка и границы/Сплошная заливка/Цвет – темно-красный. Щелкните по кнопке Закрыть.

10.3. Измените сетку диаграммы, щелкнув правой клавишей по горизонтальной оси диаграммы и из контекстного меню выбрав: Формат линий сетки/Сплошная линия/Цвет – белый. Щелкните по кнопке Закрыть.

10.4. Установите новый фон стенок диаграммы. Для этого вызовите контекстное меню задней стенки диаграммы, выберите команду Формат стенок, затем Градиентная заливка, Предустановленный градиент – Легкий градиент (Акцент 2), Тип – Линейный. Щелкните по кнопке Закрыть.

10.5. Измените угол разворота диаграммы. Для этого:

• щелкните правой клавишей в области диаграммы, из контекстного меню выберите команду Поворот объемной фигуры;

• в диалоговом окне Формат области диаграммы установите поворот вокруг оси Х – 70°, поворот вокруг оси Y – 40°, глубина (% от базовой) – 200. Щелкните по кнопке Закрыть.

10.6. Выполните форматирование заголовка диаграммы, изменив начертание, размер и цвет символов шрифта. Для этого:

• выделите заголовок диаграммы, вызовите контекстное меню, нажмите кнопку Формат названия диаграммы и выберите в списке Цвет границы элемент «Сплошная линия», Цвет – произвольный. Щелкните по кнопке Закрыть;

• с помощью контекстного меню измените шрифт заголовка. Для этого выберите из списка Шрифт – Arial Black, Начертание – обычный, Размер символов – 18, Подчеркивание – нет, Цвет текста – красный;

• нажмите OK;

• аналогично измените цвет (на черный) и размер шрифта (на 12) подписей данных над столбцами;

• переименуйте лист «Диаграмма1» в «Гистограмма1». Для этого щелкните правой кнопкой мыши по ярлыку этого листа и из контекстного меню выполните команду Переименовать. Введите новое имя без пробела (!).

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

12. Для выхода из режима печати и предварительного просмотра нажмите кнопку в левом верхнем углу окна Печать.

13. Самостоятельно (!) постройте объемный вариант круговой диаграммы на основании данных столбцов «Фамилия» и «З/П к выдаче» таблицы «Лицевой счет» и расположите ее на одном листе с таблицей (Лист2). Установите следующие параметры диаграммы:

• заголовок диаграммы – «Распределение заработной платы по работникам предприятия»;

• не добавлять легенду;

• подписи данных – имена категорий и значения в общем объеме.

Выполните форматирование всех элементов диаграммы и переименуйте Лист2 в «Круговая_диаграмма».

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

14. Создайте новый рабочий лист (Лист3).

15. Введите на этот лист рабочей книги следующую таблицу:

16. Выделив диапазон ячеек A4:D7, постройте объемную гистограмму с группировкой, отражающую динамику изменения объемов финансирования каждой отрасли по годам. Введите название диаграммы – «Динамика изменения объемов финансирования по отраслям» и заголовки осей: Х – «Отрасли социальной сферы» и Y – «В млрд. рублей». Разместите гистограмму на отдельном листе рабочей книги и переименуйте его в «Гистограмма2».

17. Отредактируйте построенную диаграмму. Для этого:

• замените в легенде имена Ряд1, Ряд2 и Ряд3 на 2015 г., 2016 г., 2017 г. Для этого выделите легенду, из контекстного меню легенды выберите команду Выбрать данные, нажмите кнопку Изменить и в поле Имя ряда введите новую ссылку щелчком по ячейке В3 (2015 г.) в исходной таблице на Листе3. Аналогично измените остальные имена;

• измените способ вывода в диаграмме строк и столбцов. Такая диаграмма будет наглядно характеризовать динамику роста финансирования каждой социальной отрасли. Для этого на вкладке Конструктор в группе Данные выберите команду Строка/Столбец. Повторным щелчком по кнопке Строка/Столбец верните диаграмму в исходное состояние;

• с помощью контекстного меню исключите из диаграммы ряд, соответствующий 2015 г.;

• добавьте в диаграмму новый ряд для 2018 г. Для этого добавьте соответствующий ряд в исходную таблицу, перейдите на лист с диаграммой, из контекстного меню выберите команду Выбрать данные, нажмите кнопку Добавить. В поле Имя ряда введите новую ссылку щелчком по ячейке Е3 (2015 г.) в исходной таблице на Листе3, а в поле Значения – ссылку на диапазон ячеек Е5:Е7;

• измените место расположения диаграммы. Для этого щелкните правой клавишей мыши по рамке диаграммы, из контекстного меню выберите Переместить диаграмму, затем На имеющимся листе и выберите из списка лист с исходной таблицей (Лист3). Нажмите ОК;

• измените тип диаграммы. Для этого из контекстного меню внутри рамки выберите Изменить тип диаграммы и выберите Объемную гистограмму с группировкой;

• измените стиль и поворот диаграммы;

• переименуйте Лист3 в «Гистограмма2».