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

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

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

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

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

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

Действия:

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

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

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

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

Действия:

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

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

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

Действия:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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). В соответствии с указанными номерами шагов на рисунке 2.15 установить указанные параметры.

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

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

• Продолжить устанавливать следующие форматы для указанных условий. Установив все условия, значения столбца Сумма (руб.) окрасятся в цвета, соответствующие условиям форматирования.

Возможен другой вид форматирования с применением Набора значков.

• Выделить диапазон ячеек J4:J10. Выбрать Главная ► Стили ► Условное форматирование ► Создать правило.... Появится одноименное диалоговое окно (рис. 2.16). В соответствии с указанными номерами шагов на рисунке 2.16 установить соответствующие значения.

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

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

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

Действия:

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

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

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

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

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

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

Действия:

• Скопировать таблицу в ячейки А12:J20.

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

• Удалите предыдущие правила форматирования Главная ► Стили ► Условное форматирование ► Удалить правила ► Удалить правила из выделенных ячеек.

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

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

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

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

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

Действия:

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

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

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

Действия:

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

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

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

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

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

Действия:

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

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

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

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

Действия:

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

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

3.16 Добавить столбец, предупреждающий о необходимости заказа товара.

Действия:

• Добавить в ячейку К3 название колонки Кол-во товара на 31.12.2010.

• Заполнить ячейки К4:К10 случайными числами в диапазоне от 10 до 500. Воспользуйтесь функцией СЛУЧМЕЖДУ из категории МАТЕМАТИЧЕСКИЕ.

• Добавить в ячейку K2 дату 31.12.2010

• Добавить в ячейку L3 название колонки Заказ на поставку товара.

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

• если товара на складе осталось меньше или ровно столько, сколько продаётся в среднем в месяц, напечатать — срочный заказ;

• если товара на складе осталось больше, чем продаётся в среднем в месяц и меньше или ровно столько, сколько продаётся в среднем за три месяца, напечатать — в следующем месяце;

— если товара на складе осталось больше, чем продаётся в среднем за три месяца, оставить ячейку пустой.

• Внести в ячейку L4 формулу:

=ЕСЛИ((E4-K4)/(МЕСЯЦ($K$2)-МЕСЯЦ(D4))<=K4;"срочный заказ"; ЕСЛИ((E4-K4)/ (МЕСЯЦ($K$2)-МЕСЯЦ(D4))<=3*K4;"в следующем месяце";""))

• Размножить внесённую формулу на диапазон L4: L10.

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

Действия:

• Установить курсор в ячейку L4. На вкладке Формулы, в группе Зависимости формул, выбрать команду Влияющие ячейки. Появятся стрелки, указывающие от каких ячеек зависит значение в ячейке L4.

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

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

Действия:

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

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

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

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

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

• Используя вкладку Работа с диаграммами, отформатируйте построенную диаграмму по образцу рисунка 2.20.

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

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