Задание №3. Создание электронной таблицы

Задание Создать ЭТ по образцу приведенному на рис.2.18 (верхняя таблица). Научиться применять условное форматирование. Скопировать верхнюю таблицу в нижнюю часть листа. Создать и отобразить Примечание в ячейке. Произвести вычисления в скопированной таблице используя в формуле именованные ссылки на данные. Создать автоматически диаграмму (рис.2.19)

Порядок выполнения задания.

Открыть файл с заданиями по Excel. После открытия книги перейти на чистый рабочий лист и переименовать его Лаб3.

3.1 Написать название задания, используя технологию объединения нескольких ячеек А1 :J1 в одну.

Действия:

• Выделить диапазон ячеек, которые нужно объединить. Выбрать команду Формат > Ячейки... Раскрыть вкладку Выравнивание. Установить флажок Объединение ячеек и нажать кнопку ОК. Выделенные ячейки объединятся в одну ячейку.

• Написать текст с названием темы задания

3.2 Оформить заголовок таблицы, по аналогии, используя технологию объединения нескольких ячеек A2:J2 в одну.

Действия:

• Для размещения текста в двух строках (как в примере), установить курсор после слова "Альянс" и нажать комбинацию клавиш <Alt> + <Enter>.

• Для размещения текста по центру использовать окно Формат ячеек, предварительно выделив объединенную ячейку заголовка. Активизировать вкладку Выравнивание и выбрать в списках полей: по горизонтали — по центру и по вертикали — по центру

• Для оформления заголовка заливкой использовать вкладку Вид окна Формат ячеек.

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

Действия:

• Выделить диапазон ячеек A3:J3, в которых текст нужно расположить в несколько строк. Выбрать Формат ► Ячейки.... Раскрыть вкладку Выравнивание. Установить флажок Переносить по словам и щелкнуть кнопку ОК.

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

3.4 Ввести текст в ячейки <шапки> таблицы и настроить ширину столбцов по образцу.

Действия:

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

3.5 Оформить внешний вид <шапки> таблицы, используя опции вкладок диалогового окна Формат ячеек:

• Шрифт (Times New Roman, 10, полужирный);

• Выравнивание (по горизонтали и по вертикали - по центру);

• Выравнивание (для ячейки В3— вертикальная ориентация текста);

• Граница (тип линий для оформления границ);

• Вид (выбрать заливку, по собственному усмотрению).

3.6 Ввести данные в столбцы А, В, С, D, F, Н и с учетом типов данных, установить соответствующие форматы

Название - текстовый;

Код - текстовый;

Страна поставщик - текстовый;

Дата поставки - дата;

Количество - число;

Единица измерения - текстовый;

Цена в валюте -денежный

Курс валюты - число;

3.7 Произвести расчеты в столбце "Цена в рублях". Формат значений столбца Цена в рублях - денежный.

Действия:

Установить курсор ввода в ячейку I4 и ввести формулу = G4*H4. . В ячейках I4:I10 произвести автозаполнение используя маркер заполнения. Должен появиться результат как на образце (рис. 2.18).

3.8 Вычислить значения Суммы (руб.) в ячейке J4 по формуле =Е4*I4, затем размножить формулу на ячейки J5:J10. Результат сравнить с образцом (рис. 2.18).

3.9 Установить Условный формат для отображения в разном цвете значений столбца "Сумма (руб.)" в соответствии с условиями:

• для суммы больше или равно 300000, установить цвет — красный.

• для суммы между значениями 100000 и 300000, установить цвет —синий;

— для суммы меньше или равно 100000, установить цвет — зеленый.

Использование отображения информации в таблице в разном цвете удобно при отслеживании, например, границ цен (рост, падение).

Действия:

• Выделить диапазон ячеек J4:J10. Выбрать команду Формат > Условное форматирование.... Появится одноименное диалоговое окно (рис. 2.15). В появившемся окне установить для поля 1-е условие — значения. Нажать кнопку Формат и выбрать цвет красный для 1-го условия. После возвращения в окно Условное форматирование щелкнуть на кнопке А также» и повторить шаги для 2-го, затем для 3-го условий.

• Для завершения действий нажать кнопку ОК. В результате значения столбца Сумма (руб.) будут отображены в цвете соответствий условному формату.

Рис. 2. 1 Вариант отображения значений для условного формата

3.10 Установить Условный формат. Выделить голубым цветом ячейки содержащие Название товара, если его количество на складе менее среднего значения.

Действия:

• Выделить диапазон ячеек А4:А10 содержащий наименования товаров.

• Выбрать команду Формат > Условное форматирование.... Появится одноименное диалоговое окно (рис. 2.16). В появившемся окне заменить значение выпадающего списка на формулу и внести формулу по образцу (рис.2.16).

• Нажать кнопку Формат, выбрать вкладку Вид и установить голубой цвет.

Рис. 2. 2 Вариант отображения значений для условного формата

• Для завершения действий нажать кнопку ОК. В результате ячейки столбца Название будут окрашены в голубой цвет в соответствии с условиями форматирования.

3.11 Установить Условный формат. Выделить желтым цветом строки с товарами, доставленными на склад позднее 10 августа 2008 г.

Действия:

• Выделить диапазон ячеек А4:J10, т.е. всю таблицу сданными.

• Выбрать команду Формат > Условное форматирование.... Появится одноименное диалоговое окно (рис. 2.17). В появившемся окне заменить значение выпадающего списка на формулу и внести формулу по образцу (рис.2.17).

• Нажать кнопку Формат, выбрать вкладку Вид и установить желтый цвет.

• Для завершения действий нажать кнопку ОК. В результате в желтый цвет будут окрашены те строки таблицы, которые содержат дату поступления товара позднее 10 августа 2008 года.

Рис. 2. 37 Вариант отображения значений для условного формата

3.12 Создать примечание (комментарий) в ячейке В10 столбца "КОД".

Действия:

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

• Чтобы изменить текст примечания, следует выбрать команду Вид►Примечание или, установив курсор в ячейку с признаком примечания, щелкнуть правой кнопкой мыши и выбрать в контекстном меню команду Изменить примечание. На рабочем листе появится панель для редактирования примечания.

3.13 Выделить и скопировать созданную таблицу в нижнюю часть листа. Начало нижней таблицы должно совпадать с ячейкой А12.

Действия:

• Выделить созданную таблицу, нажать кнопку копировать стандартной панели инструментов.

• Установить курсор в ячейку А12 и нажать кнопку вставить.

3.14 В скопированной таблице присвоить столбцу: "Цена в валюте" имя "Цена", а столбцу "Курс валюты ЦБ РФ" — "Курс".

Действия:

• Выделить диапазон ячеек G14:G20 для присвоения имени.

• Выбрать из группы команд Вставка, команду Имя►Присвоить. Ввести любое имя (не совпадающее с адресами ячеек), и не имеющее пробелов, например, имя "Цена".

Имена создают абсолютные ссылки.

• По аналогии, присвоить диапазону ячеек Н14:Н20 имя Курс.

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

Действия:

• Установить курсор ввода в ячейку I14 и ввести формулу = Цена* Курс, используя меню Вставка►Имя►Вставить.

• Скопировать формулу ячейки I14 в ячейки I15:I20 с помощью маркера заполнения.

• Присвоить имена данным Е14:Е20 столбца Кол-во и данным I14:I20 столбца Цена в рублях. Затем использовать присвоенные имена для вычисления значений в столбце Сумма (руб.).

3.16 Отобразить примечание ячейки В20 на экране. Отобразить все имена электронных таблиц книги.

Действия:

• Установить курсор в ячейку В19, имеющую признак наличия примечания. Нажать правую кнопку мыши и в появившемся контекстном меню выбрать команду Отобразить примечание.

• Все имена диапазонов книги отобразятся в списке, если щелкнуть на треугольничке поля адресов и имен (левое поле, рядом с полем ввода формул).

3.17 Отобразить влияющие ячейки.

Действия:

• Установить курсор в ячейку J14. Выбрать команду Сервис►Зависимости►Влияющие ячейки. Появятся стрелки, указывающие от каких ячеек зависит значение в ячейке J14.

Эта информация удобна при поиске ошибок при расчетах.

3.18 Создать автоматически диаграмму для данных двух столбцов таблицы: А4:А10 и J4:J10 по образцу (рис. 2.19).

Действия:

• Выделить диапазон данных А4:А10.

• Нажать клавишу <Ctrl> и, не отпуская ее, выделить следующий диапазон данных J4:J10. Будут выделены два несмежных диапазона ячеек.

• Нажать клавишу <F11>. На рис. 2.19 показана гистограмма, построенная описанным только что способом.

• Для оформления заголовка гистограммы следует щелкнуть правой кнопкой мыши по области диаграммы и выбрать из контекстного меню команду Параметры диаграммы.... Появится одноименное диалоговое окно.

• В поле окна Название диаграммы: ввести заголовок по образцу (рис.2. 19). Нажать кнопку ОК.

Рис. 2. 48 Вид электронной таблицы к заданию №3 по Excel

Рис. 2. 59 Диаграмма, созданная автоматически на листе диаграмм