Ряды данных, большие таблицы в MS Excel

Цель занятия

Изучение технологии создания рядов данных и работы с большими таблицами в MS Excel.

Задание 1 Создать на разных листах электронной книги две таблицы по образцу Порядок работы:

• 1 Запустите редактор электронных таблиц Microsoft Excel.
• 2 В новой электронной книге на Листі создайте табл.1 «Цены на изделие» (рис.1).

excel таблица данные.

Рис. і Таблица «Цены на изделие»

Переименуйте Листі, присвоив ему имя «цена». 3 На Лист2 создайте табл.2 «Заказ» с данными 2006;2007г.

Краткая справка При заполнении данными столбцов таблицы «Год», «Месяц», «Количество изделий» используйте встроенные средства автоматизации MS Excel (автозаполнение с использованием маркера автозаполнения, арифметическую прогрессию и др.).

Отформатируйте таблицу по образцу.

Объедините ячейки А5: А16 для записи года (2006).

Объедините ячейки Н5: Н16 для расчета итогов за год (2006 г.).

Аналогично оформите таблицу для 2007 г.

Рис. 2 Таблица «Заказ» с данными за 2006;2007г.

• 5 Введите в таблицу «Заказ» данные за 2009 г. (ниже данных за 2008 Г.).
• 4 Введите в таблицу «Заказ» данные за 2008 г.(ниже данных за 2007 г.). Оформление таблицы проведите по образцу.

Рис. 3. Данные за таблицы «Заказ» 2008г

Оформление таблицы проведите по образцу.

6 Введите в таблицу «Заказ» данные за 20 010 г.(ниже данных за 2009 г.).

Оформление таблицы проведите по образцу.

Рис. 5 Данные таблицы «Заказ» за 20Юг

7. Переименуйте Лист23 присвоив ему имя «заказ».

Задание 2 Произвести соответствующие расчеты в таблице «Заказ».

Порядок работы:

1 Рассчитайте количество изделий всех наименований за каждый месяц. Расчетная формула:

Количество изделий (всего) = Изделие! + Изделие! + ИзделиеЗ В ячейку F5 введите формулу:

=СУММ (С5:Е5).

Если все сделано правильно, то количество изделий всех наименований за январь 2006 г составит 1550 шт.

Скопируйте формулу вниз по столбцу, используя маркер автозаполнения.

Рассчитайте стоимость изделий всех наименований за каждый месяц.

Расчетная формула:

Стоимость изделий за месяц = Изделие1*цена1 + Изделне2*цена2 + ИзделиеЗ* ценаЗ

В ячейке G5 будет формула:

=С5*цена!$С$ 4+заказ!В5*цена!$С$ 5+заказ!Е5*цена!$С$ 6.

Внимание! В расчете используются данные разных листов: данные о цене берутся с листа «цена», данные о количестве — с листа «заказ». При этом к адресу ячейки добавляется имя листа.

Цена каждого изделия задается в виде абсолютной адресации ячеек, при этом в адрес ячейки добавляется знак $ (проще всего абсолютную адресацию задавать клавишей F4).

Если все сделано правильно, то стоимость изделий всех наименований за январь 2006 г составит 2 075 000,00 руб.

Скопируйте формулу вниз по столбцу, используя маркер автозаполнения.

Рассчитайте итоговую стоимость изделий за каждый год.

Расчетная формула:

Итого за год = Сумма стоимостей изделий за месяц В ячейке Н5 введите формулу:

Если все сделано правильно, то итоговая стоимость изделий за 2006 г составит 51 225 000,00 руб.

Скопируйте формулу вниз по столбцу, используя маркер автозаполнения.

Добавьте в таблице «Заказ» (справа) новую колонку с именем «Средняя стоимость за год».

Объедините ячейки 15:116,117:128 и т. д. до 2010 г.

Рассчитайте среднее значение стоимости изделий за каждый год. Используйте функцию СРЗНАЧ.

В ячейке 15 введите формулу:

=CP3HA4(G5:G16).

Если все сделано правильно, то средняя стоимость изделий за 2006 г составит 4 268 750,00 руб.

А общий вид таблицы за 2006 г. имеет вид:

Рис. 6 Итоги расчетов таблицы «Заказ» за 2006 г.

Скопируйте формулу вниз по столбцу, используя маркер автозаполнения.

5 Добавьте в таблице «Заказ» (снизу) новую строку с именем «Итого». Рассчитайте итоговые суммы по колонкам (за все годы). Используйте функцию СУММ.

В ячейке С65 введите формулу: =СУММ (С5:С64).

Скопируйте формулу вправо по стоке, используя маркер автозаполнения.

Если все сделано правильно, то результаты суммирования следующие:

Рис. 7 Итоги таблицы «Заказ» за все годы

Задание 3. Отформатировать текст и данные таблицы разным цветом по годам

Порядок работы:

Выделите для удобства работы все данные и расчеты по годам разным цветом:

• — 2006 год — черный цвет шрифта
• — 2007 год — красный цвет шрифта
• — 2008 год — синий цвет шрифта
• — 2009 год — зеленый цвет шрифта
• — 2010 год — фиолетовый цвет шрифта

Задание 4 Построить диаграммы по данным таблицы «Заказ».

Порядок работы

На отдельных листах построить диаграммы:

График с маркерами «Стоимость изделий по месяцам за период с 2006;2010 гг.».

Гистограмму «Стоимость изделий по месяцам за период с 2006;2010 гг.».

На листе «ЗАКАЗ» построить диаграммы «Стоимость изделий за месяц»:

• 1 Объемную гистограмму за 2006 г.
• 2 Лепестковую за 2007 г.
• 3 Круговую объемную за 2008 г.
• 4 Кольцевую разрезанную за 2009 г.
• 5 Цилиндрическую за 2010 г.
• 6

Задание 5 Закрепление областей таблицы.

Порядок работы

• 1 Скопируйте таблицу «Заказ» на новый лист.
• 2 Для скопированной таблицы «Заказ» закрепите область заголовка (строки и столбца), чтобы не терять их из виду.

Пользуясь справкой изучите технологию закрепления областей таблицы Excel.

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

Для закрепления ВЕРХНЕЙ ГОРИЗОНТАЛЬНОЙ ОБЛАСТИ выделите строку, под которой требуется разбить лист, и в меню Окно . выберите команду Закрепить области.

Для закрепления ЛЕВОЙ ВЕРТИКАЛЬНОЙ ОБЛАСТИ выделите столбец, слева от которого требуется разбить лист, и в меню Окно . выберите команду Закрепить области.