Вопросы для самоконтроля.

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

2. Как внедренную диаграмму разместить на отдельном листе диаграмм?

3. Как изменить тип диаграммы?

4. Как добавить легенду?

5. Как редактировать отдельные части диаграммы?

6. Как в подписях данных получить расположение подписей: имена рядов, имена категорий, значения друг под другом?

7. Как добавить линии сетки?

Лабораторная работа № 7

Знакомство с автоформатами, работа с областями и именами областей

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

• Числовой формат.

• Вид шрифта.

• Выравнивание.

• Рамка.

• Узор и цвет фона.

• Ширина столбца.

• Высота строки.

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

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

Порядок выполнения работы: Скопируем таблицу 6.8. из работы № 6 (A1:G10) в новую рабочую книгу, которую мы откроем.

Поместим активную клетку в любое место таблицы и идем в Меню/Формат / Автоформат. Появиться диалоговое окно Автоформат, где представлены названия автоформатов и как они выглядят. Ознакомимся со всеми.

Выбираем Цветной 2 <ОК> По адресу НЗ введем "Всего" и скопируем из области G3:G10 по Н3:Н10 формат с помощью <Копировать формат по образцу> (Formatpainter).

Скопируем эту таблицу (без названия) по адресу А12, далее вставим группу пустых ячеек по адресам В12:С19, для этого: переместим группу ячеек (В12:Н19) вправо на 2 столбца с помощью операции drag&drop. Затем в полученную пустую область введем следующую информацию:

по В12 - марка автомобиля

по С12 - Стоимость за 1шт, $

Далее ввести данные в соответствии с табл.7.9

По D12 и далее – названия месяцев

В области D13:I18 – нужно вычислить выручку от продажи автомобилей, для этого перемножить стоимость на количество проданных машин =$C13*B4, данную формулу нужно ввести в область с помощью «Ctrl Enter».

Таблица 7.9.

Марка автомобиля	Стоимость за 1шт.	Январь Февраль Июнь
Skoda	13000	Вычислить выручку, полученную от продажи машин и скопировать на всю таблицу
Жигули	7000
Волга	8000
УАЗ	5000
Hynday	18000
Daewoo	22000

После заполнения таблицы нужно проверить суммы «итого» и «всего» и, в случае необходимости их пересчитать.

Когда таблица получена, выбираем формат финансовый 2, затем создадим пользовательский формат "Все форматы" с $ и поменяем в таблице все рубли на $.

Результат показать преподавателю и сохранить в свою папку.

Скопируем эту таблицу на новый лист, используя <Специальную вставку>, т.е. переносим на лист только значения ячеек. Зададим автоформат Объёмный эффект2. Через столбик зададим различные цвета (Формат Ячейки, вид, выбрать цвет).

НЕ ДЕЛАТЬ! Полученную таблицу можно сохранить качестве шаблона: Меню/Файл

/Сохранить как ...В окне: Тип файла выбрать шаблон; ваше_имя.xlt. Выйти из Excel.

Загрузить Excel: Меню / Файл / Открыть.

Тип Файла. Выбрать свой Шаблон <ОК>

Теперь ваша таблица будет храниться в качестве шаблона. Её также можно изменять. Можно сохранять рабочую область, тогда расширение будет *.xlw Аналогично, при необходимости, можно сохранять ваш файл в более ранних версиях MSOffice.

Построить следующие графики в отдельных листах диаграмм:

1. Гистограмму с накоплением вырученных денег от продажи машин за весь период продажи каждой фирмой за каждый месяц. Данные располагаются в столбцах. В мастере диаграмм «шаг2 из 4» нужно поработать с вкладкой «Ряд»: ось абсцисс должна иметь три подписи: наименование фирмы, марка автомобиля и стоимость; по оси ординат показать объём выручки; также на диаграмме должны размещаться значения; легенда, название диаграммы и т.д.

2. График зависимости всех вырученных денег (столбец «Всего») от названия магазина. График также должен иметь подписи, легенду, название и т.д. При построении данного графика нужно использовать “несмежные выделения” с помощью клавиши «Ctrl».

3. График зависимости вырученных денег (строка «Итого») по месяцам. График также должен иметь подписи, легенду, название и т.д. При построении данного графика нужно использовать “несмежные выделения”, кроме того, здесь также придется поработать с вкладкой «Ряд».

4. Графики зависимостей вырученных денег по месяцам для трех заданных магазинов (на одной диаграмме). Графики должны иметь подписи, легенду, название и т.д. При построении данных графиков нужно использовать “несмежные выделения”, кроме того, здесь также придется поработать с вкладкой «Ряд».

5. Графики зависимостей вырученных денег для всех магазинов по месяцам.

Создадим электронный ярлычок примечаний к клеткам, где производятся расчеты (например, к ячейке «Всего»). Выделяем область, идем в Меню/Вставка/Примечание. Вводим текст примечания: “Данные из таблицы 1х на данные таблицы 2”; <Добавить> <ОК>, в ячейке в правом верхнем углу появится красная точка. При наведении курсора на данную ячейку примечание будет «всплывать», а информация о ячейке появится в строке состояния. Аналогично, можно добавить примечание с помощью правой клавиши мыши.

Показать результат преподавателю. Можно вносить изменения в некоторые функции автоформата, например, в параметры форматирования (шрифта) идем в Меню/Формат/ автоформат, кнопка <Параметры>. В поле шрифт убираем "галочку" (там где нужно внести изменения) и выбираем новый шрифт. Результат виден на примере.

Для работы с областями можно использовать не только адреса, но и заголовки строк и столбцов. Вычислить сумму выручки за январь можно с помощью автосуммы, а можно =сумм(январь), т.е. можно работать с заголовками строк и столбцов, а можно задавать имена. Имя - слово или строка знаков, представляющие ячейку, диапазон ячеек, формулу или константу. Имя, по умолчанию, является абсолютной ссылкой. Определенное имя в формуле облегчает понимание назначения формулы.

По умолчанию Microsoft Excel не распознает заголовки в формулах. Чтобы использовать заголовки в формулах, выберите команду Параметры в меню/Сервис/Параметры на вкладке «Вычисления» в группе «параметры книги» установите флажок «Допускать названия диапазонов».

Например, формулу =СУММ(Продано_в_первом_квартале) легче опознать чем =СУММ(C20:C30) По умолчанию Microsoft Excel не распознает заголовки в формулах. Чтобы использовать заголовки в формулах, выберите команду Параметры в меню Сервис. На вкладке Вычисления в группе Параметры книги установите флажок Допускать названия диапазонов.

При задании имён нужно знать некоторые правила:

• Допустимые знаки. Первый знак в имени должен быть буквой или знаком подчеркивания. Остальные знаки имени могут быть: буквами, числами, точками и знаками подчеркивания.

• Имена не могут иметь такой же вид, как и ссылки на ячейки, например Z$100 или R1C1.

• В имени может быть больше одного слова, но пробелы недопустимы. В качестве разделителей слов могут быть использованы знаки подчеркивания и точки— например: Налог_на_продажи или Первый.Квартал.

• Имя может содержать до 253 знаков.

• Имя может состоять из строчных и прописных букв, но Microsoft Excel их не различает. Например, если создано имя «Продажа», а затем в той же книге создано другое имя «ПРОДАЖА», то второе имя заменит первое.

• Если с помощью диалогового окна Заголовки диапазонов создаются заголовки, содержащие год или дату, то при вводе формулы Microsoft Excel определяет дату как заголовок путем заключения ее в одинарные кавычки. Например, пусть лист содержит два заголовка «2007» и «2008», определенных в диалоговом окне Заголовки диапазонов. При вводе формулы =СУММ(‘2008’) год автоматически заключается в кавычки.

Многоуровневые заголовки. Если на листе используются заголовки столбцов и строк, эти заголовки можно использовать при создании формул, ссылающихся на данные листа. Если лист содержит многоуровневые заголовки столбцов — в которых за заголовком в одной ячейке ниже следуют один или более заголовков — можно использовать эти заголовки в формулах, ссылающихся на данные листа. Например, если в ячейке E5 находится заголовок «Запад», а в ячейке E6 — заголовок «Проект», то формула =СУММ(Запад;Проект) возвращает общее значение для столбца «Запад Проект».

Подсчитать общую выручку всеми магазинами за весь период, предварительно задав этой области имя : Выделить нужную область, Меню/Вставка/Имя присвоить и задать имя «Выручка». Определить число, которое находится на пересечении столбца «март» и строки «Дельта» =Март Дельта. Задать для двух групп ячеек имена и подсчитать их суммы (или воспользоваться имеющимися). Подсчитать суммы продаж для каждой фирмы по двум любым месяцам, например: =Февраль+Апрель; подсчитать суммарную выручку для каждого месяца для двух магазинов, например: = Аргон+Логоваз. По адресу К1 задать процент инфляции 12% и присвоить ячейке К1 имя «инфляция». Предположим, что рост цен на машины в будущем году будет соответствовать инфляции. Определить новую стоимость автомобилей, используя в формуле имя константы «инфляция».

Результат показать преподавателю и сохранить в свою папку.