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

Теперь сделайте диаграмму по последней таблице. Она должна выглядеть так.

1. Выделите таблицу

2. Выполните команду Вставка / (Диаграммы) Гистограмма, далее Все типы диаграмм. Выберите тип график. Нажмите OK.

3. Выделите Область построения. Выполните команду Макет/ (Текущий фрагмент) Формат выделенного фрагмента. Выберите цвет заливки и укажите пункт Сплошная заливка и нажмите Закрыть.

4. Выделите горизонтальную ось категорий. Аналогично предыдущему выберите Формат выделенного объекта. В параметрах оси выберите положение оси по делениям и нажмите Закрыть.

5. Выделите Основные линии сетки горизонтальной оси, далее Формат выделенного объекта. Выберите сплошная линия, цвет линии – белый. Во вкладке Тип линии: Тип штриха выберите штрих. Нажмите Закрыть.

6. Аналогично проделайте операции с Основными линиями сетки вертикальной оси.

7. Выделите ряд гольф. Зайдите в Формат объекта, вкладка цвет линии. Выберите сплошная линия, цвет – белый. Задайте черный цвет для ряда теннис.

8. Сделайте цвет для легенды заливку более темный оттенок 25%.

9. Отредактируйте размер диаграммы.

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

1. Создайте список следующего вида

и

консолидируйте

данные

этого

списка

следующей

таблице

.

2.

Создайте

еще

одну

таблицу

,

используя

трехмерные

ссылки

.

Создайте таблицу следующего вида и сохраните ее под названием «список студентов»:

 

Указания. Чтобы в ячейках B2:F4 получить числовые значения, необходимо «суммировать» трехмерные ссылки, т.е.:

a) Выбираем ячейку в которую собираемся вводить значение. Например В2.

b) Ставим знак «=» и переходим в документ «список студентов».

c) Смотрим, есть ли в списке студенты из 903 группы, рожденные в 90 году? Да, таких трое. Кликаем на ячейку А2. В строку, где пишем формулу, ставим знак «+», т.к. у нас три студента. Кликаем ячейку А3, снова ставим «+», кликаем А4. Нажимаем Enter. Если мы все сделали правильно, в ячейке В2 должна появиться формула ='[список студентов.xls]Лист1'!$A$2+'[список студентов.xls]Лист1'!$A$3+'[список студентов.xls]Лист1'!$A$4.

d) Аналогично заполняем и остальные ячейки.

e) Если нет студентов, родившихся в определенный год, то в ячейку ставим знак «-». Как в случае с ячейкой В3.

Для второй таблицы создайте диаграмму. (диаграммы / линейчатая / каноническая)

Шестой урок

Создание сводной таблицы.

Сделайте такую таблицу (если у Вас сохранилась заготовка с прошлого урока, можете использовать ее).

1. Введите данные в ячейки.

2. Выделите получившуюся таблицу и выберите Главная/(Ячейки)/Формат/Формат ячеек. В этом окне диалога во вкладке Граница выберите внешние и внутренние границы. Нажмите OK.

3. Выделите шапку таблицы и в том же диалоговом окне во вкладке Заливка выберите черный цвет, а во вкладке Шрифт цвет шрифта – белый и нажмите OK.

Создадим для этого списка сводную таблицу и сводную диаграмму.

Сводная таблица — это таблица, которая используется для быстрого подведения итогов или объединения больших объемов данных. Меняя местами строки и столбцы, можно создать новые итоги исходных данных; отображая разные страницы можно осуществить фильтрацию данных, а также отобразить детальные данные области.

Таблица должна выглядеть следующим образом.

Диаграмма должна выглядеть следующим образом.

Чтобы создать сводную таблицу и диаграмму, сделайте следующее.

1. Выделите таблицу.

2. Выполните команду Вставка/(Таблицы)/ Сводная таблица/Сводная диаграмма и нажмите OK.

3. Справа выберите поля спорт и продажи для добавления в отчет, затем щелкните правой кнопкой мыши на поле год и выберите пункт Добавить к полям легенды (ряды).

4. Отформатируйте диаграмму.

Функция «ЕСЛИ»

Очень часто в таблицах удобно использовать функции. Логические функции предназначены для проверки выполнения условия или для проверки нескольких условий. Так, функция ЕСЛИ позволяет определить, выполняется ли указанное условие, и возвращает одно значение, если условие истинно, и другое — если оно ложно. Нужно сделать таблицу следующего вида:

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

Создайте сначала следующую таблицу.

1. Введите данные во все ячейки, кроме последней. Там будут располагаться формулы.

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

3. Во вкладке Выравнивание поставьте выравнивание По верхнему краю и включите флажок Переносить по словам.

В столбец Состояние бюджета нужно вставить функцию ЕСЛИ, которая показывает, были ли превышены расходы.

1. Поставьте курсор во вторую ячейку столбца.

2. Выполните команду Формулы/(Библиотека функций) Вставить функцию. Нажмите OK.

3. В поле Лог_выражение введите С2<В2 (фактические расходы меньше предполагаемых).

4. В поле Значение_если_истина введите «превышает», Значение_если_ложь — «не превышает» и нажмите OK.

5. Выделенную ячейку потяните за маркер на все ячейки этого столбца.

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

1. Выделите таблицу.

2. Выполните команду Вставка/(Таблицы)/ Сводная таблица/Сводная диаграмма и нажмите OK.

3. Справа выберите поля Год, Фактические расходы, Предполагаемые расходы для добавления в диаграмму.

 

Отформатируйте диаграмму.

1. Измените тип диаграммы. Выполните команду Конструктор/(Тип) Изменить тип диаграммы. В появившемся окне выберите «График с маркерами.»

2. Щелкните мышкой по маркеру графика «Сумма по полю предполагаемые расходы» и выполните команду Макет/(Текущий фрагмент) Формат выделенного объекта.

3. В появившемся окне выберите вкладку Параметры маркера, в ней установите переключатель Тип маркера на «Встроенный», тип: квадрат.

4. Во вкладке Заливка маркера выберите «Сплошная заливка», цвет маркера — красный, акцент 2.

5. Для графика «Сумма по полю фактические расходы» сделайте: тип маркера — ромб,

6. цвет — синий, акцент 1.

7. Для обоих графиков во вкладке Цвет линии установите параметры: сплошная линия, цвет — черный. Во вкладке Тип линии установите значение поля Ширина 2,25 пт.

8. Выделите фрагмент «Область построения», выполните команду Макет/(Текущий фрагмент) Формат выделенного объекта.

9. Во вкладке заливка установите переключатель в значение «Градиентная заливка», тип заливки — Линейный, направление — вправо, угол — 0°.

10. В разделе «Точки градиента» для добавления точки нажмите кнопку «Добавить точку градиента». Для удаления – кнопку «Удалить точку градиента».

11. Установите следующие параметры:

• для 1-й точки: положение — 0%, цвет — белый, прозрачность — 0% • для 2-й точки: положение — 100%, цвет — черный, прозрачность — 0%.