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

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

Порядок выполнения работы:

 

1. Запустить Мiсrоsоft Ехсеl 2007.

2. Первому листу новой рабочей книги присвоить название "Ремонт портовых устройств", щелкнув два раза мышью на название листа.

3. Второму листу новой рабочей книги присвоить название "Всего", щелкнув два раза мышью на название листа.

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

- Вставить в таблицу заголовки столбцов и строк.

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

- Заполнить значения дат "начало месяца" в ячейках соответствующей строки.

- Заполнить столбец "Портовые устройства" (Регулируемый ПОГРУЗЧИК ML 906R; Подвижный ПОГРУЗЧИК ML1612R; Боковой ПОГРУЗЧИК 5016; Реверсивный транспортер; Контейнерный ТРАНСПОРТЕР ML 35 СM; Скважинный насос GRUNDFOS SP 1A; Центробежный насос GRUNDFOS CR 15), копируя названия портовых технических устройств.

5. Просмотреть макет таблицы. Образец:

 

 

6. Заполнить таблицу данными по устройствам (если портовое техническое устройство находится в капитальном ремонте - автоматически ставится 1, если нет - пустая ячейка). Для этого выполнить следующие действия:

- Активировать первую ячейку нижней части таблицы (пересечение даты 12.1.2008 и устройства "Регулируемый ПОГРУЗЧИК ML 906R").

- Установить курсор в строку формул и набрать с клавиатуры знак "=".

- Из раскрывающегося списка функций слева в верхней части окна выбрать логическую функцию ЕСЛИ (на ленте вкладка "Формулы", диалоговое окно "Библиотека функций", кнопка "Логические функции", функция "ЕСЛИ").

- Если в раскрывшемся списке ее нет, то нужно выбрать "Другие функции", категория "Логические", функция ЕСЛИ.

- В окне функции ЕСЛИ указать аргумент: в "Значение_если_истина" набрать с клавиатуры - 1.

- В окне функции ЕСЛИ указать аргумент: в "Значение_если_ложь" набрать с клавиатуры - "" (пустая строка).

- Переустановить курсор в строку аргумента "Лог_выражение".

 

 

- В раскрывающемся списке выбрать функцию И. Если в списке ее нет, то нужно выбрать "Другие функции", категория "Логические", функция И.

- В окне функции И указать аргумент: в строке "Логическое_значение1", щелкая на соответствующие ячейки ввести логическое выражение (начало месяца > начала ремонта) с использованием смешанной адресации.

- В окне функции И указать аргумент: в строке "Логическое_значение2", щелкая на соответствующие ячейки ввести логическое выражение (начало месяца < окончания ремонта) с использованием смешанной адресации.

 

 

Пример из строки формул: =ЕСЛИ(И(D$1>$A2;D$1<$B2);1;"")

 

- Распространить формулу на всю таблицу.

7. Просмотреть и отформатировать заполненную таблицу. Образец:

 

 

8. Открыть лист с названием "Всего".

9. Создать макет таблицы для заполнения данными по общему количеству портовых технических устройств, находящихся в капитальном ремонте, на начало каждого месяца и вставить в таблицу заголовки столбцов и строк (даты целесообразно скопировать с уже созданной таблицы на листе "Ремонт портовых устройств"). Образец:

 

 

10.Заполнить столбец с именем № в таблице. Для этого выполнить следующие действия:

- Ввести число 1 в первую ячейку столбца №.

- Выделить мышкой весь диапазон заполнения, обязательно захватив и введенное число 1.

- Выбрать на ленте вкладку "Главная", диалоговое окно "Редактирование", кнопку "Заполнить", опцию "Прогрессия"

- Выполнить заполнение с шагом 1, прогрессия "Арифметическая", заполнение по столбцам

- Заполнить столбец таблицы с названиями типов технических устройств (под заголовком "Total"), установив связь между двумя таблицами по названиям технических устройств. Для этого выполнить следующие действия:

- Активировать первую ячейку столбца верхней части таблицы с названиями технических устройств порта

- Набрать с клавиатуры знак "="

 

 

- Щелкнуть встретившееся в ячейке название устройства № 1 "Центробежный насос GRUNDFOS CR 15" столбца "Портовые устройства" листа "Ремонт портовых устройств"

 

Пример из строки формул: ='ремонт портовых устройств'!C40

 

 

- По аналогии заполнить весь данный столбец таблицы названиями технических устройств (названия устройств в ячейках столбца должны быть заданы автоматически как ссылки на их имена в общем списке компаний столбца "Портовые устройства") из листа "Ремонт портовых устройств"

 

 

11.Вычислить общее количество устройств типа "Центробежный насос GRUNDFOS CR 15", находящихся в ремонте 12.1.2008, т.е. заполнить первую ячейку строки для устройства типа "Центробежный насос GRUNDFOS CR 15". Для этого необходимо выполнить следующие действия:

- Активировать данную ячейку первой строки

- Установить курсор в строку формул и набрать с клавиатуры знак "=".

- Из раскрывающегося списка функций слева в верхней части окна выбрать математическую функцию СУММЕСЛИ (на ленте вкладка "Формулы", диалоговое окно "Библиотека функций", кнопка "Математические", функция "СУММЕСЛИ").

- Если в раскрывшемся списке ее нет, то нужно выбрать "Другие функции", категория " Математические", функция СУММЕСЛИ.

- В окне функции СУММЕСЛИ установить курсор в строку аргумента функции "Критерий", щёлкнуть по ячейке с названием устройства "Центробежный насос GRUNDFOS CR 15" и применить смешанную адресацию

 

 

- Переустановить курсор в строку аргумента "Диапазон" и выделить на листе "Ремонт портовых устройств" все значения столбца "Портовые устройства" с названиями устройств, используя смешанную адресацию

- Затем в окне функции СУММЕСЛИ переустановить курсор в строку аргумента "Диапазон_суммирования", выделить на листе "Ремонт портовых устройств" все ячейки таблицы, содержащие данные по нахождению устройств в капитальном ремонте (с расставленными единицами), и применить смешанную адресацию

 

 

Пример из строки формул:

=СУММЕСЛИ('ремонт портовых устройств'!$C$2:$C$46;$B3;'ремонт портовых устройств'!D$2:K$46)

 

- Распространить данную формулу на всю таблицу

 

 

12.Заполнить строку "Total" верхней части таблицы. Для этого выполнить следующие действия:

- Активировать первую ячейку строки

- Вычислить общее количество устройств, находящихся в капитальном ремонте, для всех типов с помощью математической функции СУММ (на ленте вкладка "Формулы", диалоговое окно "Библиотека функций", кнопка "Математические", функция "СУММ").

 

Пример из строки формул: =СУММ(C3:C9)

 

- Распространить формулу на всю строку "Total":

13.Отформатировать итоговую таблицу.

14.Сохранить работу, просмотреть и проверить результаты. Образец заполненной итоговой таблицы на листе "Всего":

 

 

15.В больших таблицах MS Excel с данными целесообразно использовать функцию "Закрепление областей". Для этого необходимо выполнить следующие действия:

- Активировать ячейку (установить курсор) первую ячейку строки "Total" с суммой (в созданной таблице ячейка С2 с числом 28)

- Выбрать на ленте вкладку "Вид", диалоговое окно "Окно", кнопку "Закрепить области

 

 

- Используя маркеры линеек прокрутки (горизонтальной и вертикальной), изменить границы видимости таблицы

 

 

- Для большей наглядности целесообразно применение функции "Разделить". Для этого необходимо выбрать на ленте вкладку "Вид", диалоговое окно "Окно", кнопку "Разделить"

 

 

- Используя маркеры линеек прокрутки (горизонтальной и вертикальной) изменить границы видимости таблицы

16.Отменить разделение таблицы, снова нажав кнопку "Разделить"

17.Вставить в рабочую книгу новый лист, назвать "Отчет"

18.Выделить в итоговой таблице (лист "Всего") диапазон данных с количеством устройств, находящихся в капитальном ремонте на начало каждого месяца и выбрать команду "Копировать":

 

 

19.Перейти в лист "Отчет", активировать любую пустую ячейку и выбрать команду "Вставить". В результате получится таблица с нулевыми значениями данных на начало каждого месяца:

 

 

20.Обнуление произошло, т.к. при перемещении и копировании ячеек стандартным способом учитывается вся содержащаяся в них информация. Это относится как к формулам, так и к параметрам форматирования. Для решения вышеописанной задачи копирования данных в лист "Отчет" в MS Excel 2007 предусмотрены возможности "Специальной вставки". Для этого необходимо выполнить следующие действия:

- Удалить вставленные данные на листе "Отчет"

- Снова выделить в итоговой таблице (лист "Всего") диапазон данных с количеством устройств, находящихся в капитальном ремонте на начало каждого месяца и выбрать команду "Копировать"

- Перейти в лист "Отчет", активировать любую пустую ячейку, правой клавишей мыши вызвать контекстное меню и выбрать команду "Специальная вставка"

 

 

- Включить в диалоговом окне опцию "Значения и форматы чисел" и нажать кнопку ОК

 

 

- Проверить результаты копирования данных с помощью "Специальной вставки"

 

 

- Отформатировать получившуюся таблицу

21.Сохранить работу, просмотреть и проверить результаты.

Лабораторная работа № 9 "Условное форматирование"

Порядок выполнения работы:

1. Запустить Мiсrоsоft Ехсеl 2007.

2. Листу чистой рабочей книги присвоить название "Условное форматирование".

3. Создать макет таблицы для заполнения информацией по двигателям, прошедшим капитальный ремонт (если двигатель не находится в ремонте, строка в таблице выделяется красным цветом).

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

 

 

5. Значения столбца № ввести с помощью автозаполнения. Для этого:

- ввести число 1 в первую ячейку столбца №

- выделить мышкой весь диапазон заполнения (8 строк), обязательно захватив и введенное число 1

- выполнить заполнение с шагом 1 (на ленте вкладка "Главная", диалоговое окно "Редактирование", кнопка "Заполнить", опция "Прогрессия", шаг 1, прогрессия "Арифметическая", заполнение по столбцам)

6. Ввести с клавиатуры названия двигателей, заполнив столбец "Двигатели"

7. Ввести с клавиатуры данные в столбце "Тип топлива"

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

9. Считать срок ремонта у двигателей - 6 месяцев (180 дней). Т.е. значения столбца "Окончание ремонта" необходимо получить с помощью вычислений по формуле. Для этого необходимо:

- Активировать первую ячейку столбца "Окончание ремонта"

- Набрать с клавиатуры знак "="

- Щелкнуть на ячейку с датой начала ремонта для первого двигателя и добавить в формулу +180

- Распространить формулу на весь столбец

 

Пример из строки формул: =D2+180.

 

№	Двигатель	Тип топлива	Начало ремонта	Окончание ремонта
	VAL	бензин	12.09.2007	10.03.2008
	Diesel 1.8 T	дизель	30.09.2007	28.03.2008
	HCS	бензин	15.12.2009	13.06.2010
	Diesel TCI 1.8	дизель	02.06.2009	29.11.2009
	CVH	бензин	11.01.2007	10.07.2007
	Endura-TE	дизель	15.06.2009	12.12.2009
	OHC	бензин	15.08.2009	11.02.2010
	DI-Diesel	дизель	15.12.2008	13.06.2009

 

10. Данные строк в столбце "№" отформатировать красным цветом по дате окончания ремонта с помощью условного форматирования (на ленте вкладка "Главная", диалоговое окно "Стили", кнопка "Условное форматирование"). Для этого используется стандартная функция СЕГОДНЯ() (на ленте вкладка "Формулы", диалоговое окно "Библиотека функций", кнопка "Дата и время", функция "СЕГОДНЯ()"). Для этого необходимо выполнить следующие действия:

- Выделить значения столбца "№"

- Выбрать на ленте вкладку "Главная", диалоговое окно "Стили" и нажать кнопку "Условное форматирование"

 

 

- Из списка опций выбрать "Создать правило" и тип правила "Использовать формулу для определения форматируемых ячеек "

- Набрать с клавиатуры знак "="

- Щелкнуть на ячейку с датой окончания ремонта для первого двигателя

- Отменить абсолютную адресацию адреса ячейки

- Добавить в формулу выражение: <сегодня() (функция категории "Дата и время")

 

Пример итоговой формулы: =Е2<сегодня()

 

 

· Нажать в диалоговом окне кнопку "Формат"

· Задать формат данных: цвет - красный, начертание - полужирный

 

 

11. Произвести аналогичные действия с данными строк в столбцах "Двигатель", "Тип топлива" и "Начало ремонта": отформатировать красным цветом по дате окончания ремонта с помощью условного форматирования (на ленте вкладка "Главная", диалоговое окно "Стили", кнопка "Условное форматирование").

12. Данные строк в столбце "Окончание ремонта" отформатировать красным цветом по дате окончания ремонта с помощью условного форматирования (на ленте вкладка "Главная", диалоговое окно "Стили", кнопка "Условное форматирование"). Для этого необходимо выполнить следующие действия:

- Выделить значения столбца "Окончание ремонта"

- Выбрать на ленте вкладку "Главная", диалоговое окно "Стили" и нажать кнопку "Условное форматирование"

- Из списка опций выбрать "Создать правило" и тип правила "Форматировать только ячейки, которые содержат"

- Выбрать в диалоговом окне из списков значения параметров: Значение ячейки и меньше или равно

- Ввести с клавиатуры формулу: =Сегодня()

 

 

- Нажать в диалоговом окне кнопку "Формат"

- Задать формат данных: цвет - красный, начертание - полужирный

13. Отформатировать итоговую таблицу

14. Сохранить работу, просмотреть и проверить результаты. Образец заполненной итоговой таблицы (все данные строк в таблице для двигателей № 1, 2, 5 и 8 автоматически выделятся красным цветом):

 

№	Двигатель	Тип топлива	Начало ремонта	Окончание ремонта
	VAL	бензин	12.09.2007	10.03.2008
	Diesel 1.8 T	дизель	30.09.2007	28.03.2008
	HCS	бензин	15.12.2009	13.06.2010
	Diesel TCI 1.8	дизель	02.06.2009	29.11.2009
	CVH	бензин	11.01.2007	10.07.2007
	Endura-TE	дизель	15.06.2009	12.12.2009
	OHC	бензин	15.08.2009	11.02.2010
	DI-Diesel	дизель	15.12.2008	13.06.2009

 

15. При необходимости отмены условного форматирования столбца необходимо выполнить следующие действия:

- Выделить значения столбца

- Выбрать на ленте вкладку "Главная", диалоговое окно "Стили" и нажать кнопку "Условное форматирование"

- Из списка опций выбрать "Удалить правила" и "Удалить правила из выделенных ячеек"