Тема: Создание таблицы Excel

Лабораторная работа №4 (4 часов)

Цель работы: Освоить операции по вводу данных в таблицу Excel

Задание:

Рис.1. Образец таблицы

1. Создать таблицу MS Excel в соответствии с рис.1. Для этого:

· Присвоить Листу1 имя Прайс - лист с помощью контекстного меню, пункт переименовать (меню вызывается щелчком правой кнопкой мыши по имени листа).

· Сохранить файл в собственном дисковом пространстве (диск I:\) под именем Excel1.xls.

· Включить режим автосохранения (Файл – Параметры –Сохранение – Поставить флажок( ) автосохранение каждые: 10 мин).

· В строке 1 объединить ячейки B:G с помощью соответствующей кнопки на Панели инструментов. Ввести заголовок документа (см. рис.1, название таблицы).

· Увеличить ширину столбцов F и G (Для этого выделяем столбец – Контекстное меню – Ширина столбца) до 13,5.

· Объединить по строкам пары ячеек F3, G3 и F4, G4.

· Ввести заголовки столбцов F, G, H (см. рис. 1).

· Объединить ячейки A5:C5. Ввести заголовок в ячейку А5 (см. рис. 1).

· Объединить по строкам ячейки А6:Е6, … А9:Е9. Ввести названия систем (см. рис. 1).

· В соответствующие ячейки ввести числовые данные. Задать соответствующие числовые форматы (Для этого нужно выделить соответствующий фрагмент, вызвать контекстное меню и выбрать из него команду Формат ячеек. В открывшемся окне перейти на вкладку Число, выбрать формат Числовой и установить соответствующее число в поле ввода Число десятичных знаков.).

· Начертить по образцу границы ячеек с помощью контекстного меню Формат ячеек - Граница.

· Объединить ячейки A10:D10. Ввести в ячейку А10 заголовок (см. рис. 1).

· Объединить по строкам ячейки А11:Е11, … А13:Е13. Ввести названия систем.

· В соответствующие ячейки ввести числовые данные. Задать соответствующие числовые форматы(Для этого нужно выделить соответствующий фрагмент, вызвать контекстное меню и выбрать из него команду Формат ячеек. В открывшемся окне перейти на вкладку Число, выбрать формат Числовой и установить соответствующее число в поле ввода Число десятичных знаков.).

· Начертить по образцу границы ячеек с помощью контекстного меню Формат ячеек - Граница.

· Выделить столбец H (Для этого нужно щёлкнуть по названию столбца).

· Вставить пустой столбец, для этого Выделить столбец - контекстное меню – Вставить.

· Уменьшить ширину вставленного столбца до 10 (Контекстное меню – Ширина столбца).

Ввести данные из столбца «Документы» на место пустого столбца таблицы, начиная с ячейки H5 (рис.2).

Рис. 2 Столбец «Документы»

· Объединить по строкам ячейки H3:I3 и H4:I4 (I это название столбца).

· Установить курсор в ячейку А10. Вставить стоку (Вставка – Cтроки).

2. Вычислить общую стоимость (без НДС) основных информационных систем, для чего установить курсор в ячейку F10, нажать знак равенства.

Выбрать из списка функций () сумму (СУММ()). В открывшейся вкладке нажать первую кнопку со значком стрелки () для выбора диапазона суммирования. Выделить диапазон F6:F9 при нажатой левой кнопке мыши. Повторно нажать кнопку со значком стрелки () для завершения операции выделения диапазона. Нажать клавишу <Enter>.

3. Вычислить общую стоимость (с НДС) основных информационных систем, а также суммарный объем систем. Для этого установить курсор в правый нижний угол ячейки F10, когда он примет форму “ + ”, зажать левую кнопку мыши и распространить формулу в соседние ячейки G10:I10.

4. Повторить вычисления сумм по столбцам для дополнительных систем. (см. пункт 2)

5. Рассчитать для каждой системы НДС в процентах.

Сначала установить курсор в ячейку J6 и нажать знак равенства, затем ввести формулу =(G6-F6)/F6*100. Нажать клавишу <Enter>. Распространить формулу для вычисления НДС в процентах в соседние ячейки J7:J9. Скопировать формулу из ячейки J9 в буфер обмена (Правка - копировать). Вставить формулу в ячейку J12. Распространить формулу в ячейки J13,J14, зажав левую кнопку мыши.

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

6. Вычислить процент от общей суммы для каждой из основных систем. Сначала установить курсор в ячейку К6 со стоимостью системы с НДС и нажать знак равенства, затем ввести формулу, используя для ячейки суммы абсолютную адресацию (знак $ перед номером строки) = G6/G$10*100. Нажать клавишу <Enter>.

7. Распространить формулу в соседние ячейки К7:К9. Проследить за правильностью изменения адресов ячеек в формулах. Повторно установить курсор в ячейку К6 и распространить формулу расчета % для количества документов в столбец L и для объема в Мб в столбец M. Проверить правильность изменения адресов ячеек в формулах.

8. Задать вычисленным значениям целый числовой формат. Выделить столбец J (щелкнуть по названию столбца), нажать кнопку Формат по образцу на панели инструментов (). Выделить с помощью левой кнопки мыши диапазон ячеек K6:M6 и K9:M9, отпустить кнопку мыши.

9. Повторить вычисление НДС и процента от общей суммы для каждой из дополнительных систем. Установить курсор в ячейку К6, скопировать формулу (Правка – Копировать) и вставить в ячейку K12, но деление нужно производить уже не на общую стоимость с НДС основных систем, ячейка G$10, а на общую стоимость с НДС дополнительных систем, ячейка G$15.

10. Объединить ячейки с номерами 4,5 в каждом из столбцов K, L, M и ввести сокращенное название столбца % ст. с НДС, % док. и %Мб соответственно.

11. Вычислить общую стоимость и объем Основных и Дополнительных систем. В ячейку F16 ввести функцию суммирования =СУММ, для выделения несмежных диапазонов ячеек, зажать клавишу <Ctrl> и, начиная с ячейки F6, щелкать левой кнопкой мыши для добавления суммируемых ячеек (F7, F8, F9, F12, F13, F14). Распространить формулу в столбцы G, H, I.

12. Применить условное форматирование к несмежному диапазону ячеек K6:K9, K12:K14. Проверяемое условие определим следующим образом, если % от суммарной стоимости систем превышает 20, то его значение выводится шрифтом красного цвета. Для этого выделим требуемый диапазон ячеек с помощью клавиши <Ctrl> и выполним команду Панель инструментов Главная - Область Стили - Условное форматирование – Правила выделения ячеек – Больше…. Зададим условия в соответствии с рис.3. С помощью команды Пользовательский формат… на вкладке Шрифт установим полужирный шрифт красного цвета.