Электронные таблицы Microsoft Excel
методическая разработка на тему

ГОУ СПО КМБ № 40

ПЦК «Информационно-телекоммуникационные технологии»

Дисциплины «Информационные технологии в

профессиональной деятельности»;

«Информационно-коммуникационные технологии

в профессиональной деятельности»;

«Информатика и информационно-

телекоммуникационные технологии в

профессиональной деятельности»

Тема: Электронные таблицы Microsoft Excel

Цель работы: Знакомство с интерфейсом электронных таблиц Microsoft Excel. Строение окна, строка меню, панели инструментов, быстрый переход в ячейку, заполнение, редактирование и форматирование информации в ячейке; копирование и перемещение данных ячейки, сохранение файла по определенному адресу с соблюдением параметров  безопасности.  На примере показать основные правила работы с электронными таблицами.  Параметры страницы.

Учащиеся должны знать: строение окна; возможности, предоставляемые строкой меню; возможности, предоставляемые панелями инструментов; параметры безопасности файла; способы проверки правописания. Правила заполнения, форматирования и редактирования ячеек и данных в них; маркер заполнения и вставку формул; возможности, предоставляемые окном «Формат ячейки». Способы работы с листами электронной книги; способы копирование данных с одного листа на другой; способы сортировки данных таблицы. Мастер диаграмм. Окна «Параметры страницы» и «Печать».

Учащиеся должны уметь: работать с книгой электронных таблиц Microsoft Excel. Вносить данные в ячейки. Устанавливать формат данных. Устанавливать границы ячеек и таблицы. Производить расчеты с помощью формул. Работать с листами книги. Производить сортировку данных. Строить диаграммы и графики. Подготавливать листы к печати. Выводить на печать.

«ЭЛЕКТРОННЫЕ ТАБЛИЦЫ MICROSOFT EXCEL»

1. Откройте электронные таблицы Microsoft Excel с помощью главного меню или пиктограммы, установленной на рабочем столе, или значка на панели задач. В низу окна над строкой состояния находятся ярлыки листов –    «Лист 1», «Лист 2» и «Лист 3». Листы книги можно добавлять, переставлять, переименовывать, копировать и удалять. Все данные работы выполняются с помощью контекстного меню, вызываемого на соответствующем ярлыке листа.
2. Рассмотрите строку меню и панели инструментов «Стандартную» и «Форматирования» электронных таблиц. Большинство пунктов меню, входящих в них команд и инструментов совпадают с текстовым редактором.
3. Непосредственно сверху рабочей области листа книги имеется строка имен столбцов (первые столбцы именуются одной буквой, а в дальнейшем - комбинацией из двух букв), а слева от неё – столбец имен строк. На пересечении имён столбца и строки образуется имя ячейки (правило игры «Морской бой»).
4. Под панелями инструментов располагается строка формул, которая начинается с опции «Имя». В данной опции указывается имя активной ячейки. Вся работа в электронных таблицах производится в активных ячейках. Каждая ячейка – отдельное поле для работы. 
5. Принимая во внимание вышеизложенное можно сделать вывод, что лист книги электронной таблицы не соответствует печатному листу
6. Из основного правила работы в среде Windows вытекает необходимость уметь выделять ячейки, строки, столбы и диапазоны.

• Выделение одной ячейки – щелчок по данной ячейки или ввод имени в опцию имя или клавишами управления курсором. Ячейка обрамлена темной рамкой.
• Выделение строки – щелчок по имени строки. Например, для выделения строки 15 необходимо в столбце имен строк щелкнуть по числу 15. 255 ячеек данной строки окрашиваются в темный цвет, а ячейка А15 становится активной.
• Выделение столбца – щелчок по имени столбца. Для примера выделим столбец К щелкнув в строке имен столбцов по соответствующей букве. В опции «Имя» показана активная ячейка К1, а остальные 65535 ячеек столбца окрашиваются в темный цвет.
• Снять выделение можно щелчком по любой ячейке.
• Выделение диапазона ячеек можно произвести двумя способами. Первый способ: буксировка мыши от первой ячейки до последней ячейки диапазона. Выделим диапазон D4:G17. Для этого установите указатель мыши на ячейку D4 и удерживая левую клавишу мыши переместите её на ячейку G17. Отпустите клавишу мыши. D4 является активной ячейкой, а остальные ячейки указанного диапазона окрасились в темный цвет. Второй способ (позволяет дольше сохранить мышь в рабочем состоянии): сделайте активной ячейку А3, удерживая нажатой клавишу Shift, щелкните по ячейке D11. Таким образом Вы выделили диапазон А3:D11.
• Выделение нескольких диапазонов одновременно производится при нажатой клавише Ctrl. Выделите диапазон А1:В13, нажмите и удерживайте клавишу Ctrl, выделите диапазон Н2:Н15. Вы видите, что ячейки двух диапазонов окрасились в темный цвет.

При вводе данных необходимо понимать, что в электронных таблицах работа производится только в активной ячейке, ввод данный обязательно заканчивается командой «Ввод», которая подается компьютеру или клавишей управления курсором, или клавишей Enter, или дополнительным инструментом «Ввод» в строке формул. Учиться создавать документ в электронных таблицах Microsoft Excel мы будем на примере сравнительного анализа стоимости оборудования и оформления офиса организации на 5 и 9 сотрудников.

7. Перейдите на первый лист книги.
8. В ячейке А1 напечатайте «Оборудование и оформление офиса на 5 сотрудников». Данное предложение не умещается в ячейке. Это не препятствует дальнейшей работе, т.к. оно относится только к ячейке А1.
9. В строке формул появились дополнительные инструменты: «отмена» и «ввод». Инструмент «Отмена» применяется, если пользователь решил, что введенные данные являются ошибкой, а инструмент «ввод» позволяет закончить ввод данный в ячейку и оставить её активной, в отличие от других способов окончания ввода данных. Щелкните по инструменту «Ввод».
10. С помощью панели инструментов «Форматирование» установите 12-ый размер шрифта и полужирное начертание.
11. В ячейку В3 введите фразу «Курс доллара:» и нажмите клавишу →. Тем самым Вы дали команду «завершить ввод в ячейку В3» и сделали активной ячейку С3. Введите 30,36.
12. Сделайте активной ячейку В3. Войдите в пункт меню «Формат», выделите строку «Столбец» и в подменю щелкните по строке «Автоподбор ширины».
13. Активная ячейка С3. Комбинацией клавиш Ctrl + 1 откройте диалоговое окно «Формат ячеек». Перейдите на вкладку «Число». В опции «Числовые форматы» щелкните по строке «Денежный», в поле «Число десятичных знаков» установите 2, а в поле «Обозначение» - р. Щелкните по кнопке «ОК».
14. В ячейку Е3 введите дату: 5.5.2003 и щелкните по инструменту «ввод» в строке формул. Откройте окно «Формат ячеек» на вкладке «Число». В поле «Числовые форматы» укажите строку «Дата», а в прокручиваемом списке «Тип» строку 14.03.99 (или если такой строки нет, то похожую на неё). Щелкните по кнопке «ОК».
15. В ячейку А5 введите «№ п/п», закройте ввод и выровняйте с помощью автоподбора ширины столбца. Выделите ячейки А5 и А6. Откройте окно «Формат ячеек» на вкладке «Выравнивание». Раскрывающимися списками опции «Выравнивание» по горизонтали и по вертикали установите «по центру», а в опции «Отображение» поставьте флажок в поле «объединение ячеек». Щелкните по кнопке «ОК».
16. В ячейку В5 – «Наименование оборудования». Выделите ячейки В5 и В6. Откройте окно «Формат ячеек» на вкладке «Выравнивание» и установите флажки в опции «Отображение» в полях «переносить по словам» и «объединение ячеек». Щелкните по кнопке «ОК».
17. Выделите ячейки С5 и D5 и щелкните по инструменту «Объединить и поместить в центре», который располагается на панели инструментов «Форматирование». В объединенное поле введите «Цена». В С6 введите «$US», а в D6 – «Руб. коп.». Выделите ячейки С6 и D6 и с помощью инструмента «по центру» произведите соответствующее выравнивание.

18. В ячейку Е5 введите слово «Количество». Объедините ячейки Е5 и Е6, произведя выравнивание по центру по горизонтали и вертикали. Увеличьте ширину столбца.
19. В ячейку F5 введите слово «Сумма». Объедините ячейки F5 и F6, произведя выравнивание по центру по горизонтали и вертикали.
20. В ячейку А7 введите 1, а в ячейку А8 – 2. Выделите ячейки А7 и А8. Установите указатель мыши в нижнем правом углу рамки выделения. Данный элемент рамки называется маркер заполнения.  Буксировкой протяните мышь до ячейки А13. Не снимая выделения диапазона щелкните по инструменту «по центру».
21. Заполните ячейки в соответствии с образцом.

22. Сдвинем таблицу на один столбец вправо. Для этого необходимо вставить столбец перед столбцом А:

• Выделите столбец А.
• Откройте пункт меню «Вставка» и произведите щелчок по строке «Столбцы».
• Подобным образом вставляются и строки электронной таблица.
•  Выделите строку 7, над которой необходимо вставить дополнительную строку. В пункте меню «Вставка» производите щелчок по строке «Строки». В ячейку В7 введите цифру 1, а в ячейку С7 – 2. Выделите ячейки В7 и С7. Установите указатель мыши в нижнем правом углу рамки выделения и буксировкой протяните мыши до ячейки G7.
• Если необходимо вставить дополнительную ячейку, то выделяют смежную с ней ячейку и в пункте меню производят щелчок по строке «Ячейки», в диалоговом окне указывают направление сдвига соседних ячеек и нажимают на кнопку «ОК».

23. Рассчитаем стоимость оборудования офиса в рублях и копейках. Для этого:

• Выделите ячейку Е8 – первую ячейку, которая содержит данный расчет. Нажмите кнопку  « = ».
• Щелкните по ячейке D8, в данной ячейке находится первое число, участвующее в расчете.
• Нажмите клавишу умножения (*).
• Щелкните по ячейке D3, в ячейке содержится число курса доллара, которое будет постоянным в дальнейших расчетах данного столбца, т.е. абсолютная ссылка. В строке формул Вы видите не числовые значения, а имена ячеек. Дополнительная панель показывает Вам результат вычислений.
• Т.к. данное число ячейки D3 является абсолютной ссылкой для дальнейших расчетов, в строке формул произведите щелчок перед буквой D и с клавиатуры введите символ $, переместите текстовый курсор перед цифрой 3 и снова введите символ $.
• Щелкните по кнопке «ОК».
• Установите указатель мыши в нижний правый угол рамки активной ячейки и буксировкой переместите мышь до ячейки Е14.
• Не снимая выделение с ячеек установите денежных формат полученных чисел, воспользовавшись инструментом «Денежный формат», который находится на панели инструментов «Форматирование».

24. Рассчитаем сумму оборудования офиса. Необходимо умножить цену в рублях и копейках на количество.

• Активная ячейка G8.
• Введите формулу =Е8 * F8
• В данных расчетах нет абсолютной ссылки, поэтому символ $ вводить не надо.
• За маркер заполнения распространите формулу на диапазон G8:G14.
• Не снимая выделения ячеек, щелкните по инструменту «Автосумма», расположенный на панели инструментов «Стандартная» и по инструменту «Денежных формат» на панели инструментов «Форматирование».
• Если у Вас в ячейках вместо чисел появилась решетка, то увеличьте ширину столбца.

25. Выделите диапазон ячеек В5:G15. Откройте диалоговое окно «Формат ячеек» на вкладке «Границы». Установите двойную линию внешних границ и тонкую линию внутренних границ. Нажмите кнопку «ОК».

26. Выделите диапазон ячеек В1:G1. Щелкните по инструменту «Объединить и поместить в центре. Таким образом, Вы установили название по центру таблицы.
27. Переименуйте ярлык «Лист1», дав ему название «5 сотр.».
28. Скопируем лист «5 сотр.». Для этого вызовите контекстное меню на ярлыке этого листа. Щелкните по строке «Переместить/скопировать». В диалоговом окне установите флажок в поле «Создавать копию», а в опции «Перед листом» выделите строку «Лист 4». Нажмите кнопку «ОК».

Переименуйте лист «5 сотр.(2)» в «9 сотр.».

29. Щелкните по заголовку таблицы. В строке формул внесите изменения в название таблицы, указав 9 сотрудников.
30. Внесите изменения только в ячейки столбца «Количество», а изменения в столбе «Сумма» произойдет автоматически.
31. Создадим сводную таблицу суммы оформления офиса на разное количество сотрудников. Для этого:

• Переименуйте ярлык «Лист 4» в «Сводная».
• В ячейку А1 введите 14-ым размером шрифта полужирным начертанием шрифта «Сводная таблица стоимости».
• На листе «5 сотр.» выделите диапазон В8:С14. Щелкните по инструменту «Копировать».
• Перейдите на лист «Сводная». Выделите ячейку А5 и щелкните по инструменту «Вставить». Подберите ширину столбцов А и В.
• В ячейку А3 введите «№п/п», а в ячейку В3 – «Наименование оборудования». Закончите ввод в ячейку В3 инструментом «Ввод» в строке заголовка. Откройте диалоговое окно «Формат ячеек» на вкладке «Выравнивание». Установите флажок в поле «Переносить по словам» и нажмите кнопку «ОК». Установите данные в ячейке по центру.
• В ячейку С3 введите «Сумма на 5 сотр.», а в ячейку D3 – «Сумма на 9 сотр.». Выделите ячейки С3 и D3 и установите «Переносить по словам» в окне «Формат ячеек».
• Перейдите на лист «5 сотр.». Выделите диапазон ячеек G8:G15. Вызовите на выделении контекстное меню и щелкните по строке «Копировать».
• На листе «Сводная» сделайте активной ячейку С5. Вызовите на ней контекстное меню и произведите щелчок по строке «Специальная вставка». В открывшемся диалоговом окне установите переключатель в поле «Значения» и щелкните по кнопке «ОК».
• Не снимая выделения, установите денежный формат.
• Подобную работу произведите со столбцом «Сумма» в таблице листа «9 сотр.»: скопируйте  с помощью специальной вставки с листа «9 сотр.» диапазон ячеек G8:G15 на лист «Сводная» в ячейку D5.
• В ячейку Е3 введите «Разница стоимости».
• В ячейку Е5 введите формулу разности суммы на 9 сотр. И суммы на 5 сотр. (=D5-С5). Распространите формулу на диапазон Е5:Е12.
• В строку 4 введите нумерацию столбцов таблицы с помощью маркера заполнения.
• Объедините ячейки А12 и В12. Введите в объединенную область «Итого».
• Установите границы ячеек таблицы.
• Установите заголовок таблицы по центру.

32. Построим график сумм оборудования и оформления офиса на 5 и 9 сотрудников.

• В сводной таблице выделите диапазон В5:D11 (без нумерации столбцов таблицы и итоговой суммы). Вызовите Мастер диаграмм с помощью пункта меню «Вставка» строкой «Диаграмма…».
• В диалоговом окне Мастера в опции «Тип» укажите «График», а в опции «Вид» щелчком Вы можете выбрать любой график. Кнопка «Просмотр результата» поможет Вам определить оптимальный вид графика.
• Нажмите кнопку «Далее».
• В данный момент на вкладке «Диапазон данных» производить изменения не надо. Перейдите на вкладку «Ряд». В опции «Ряд» выделите строку «Ряд 1» и клавишей ТАВ перейдите в текстовое поле «Имя» введите с клавиатуры «Сумма на 5 сотрудников». В опции «Ряд» выделите строку «Ряд 2» и в текстовое поле «Имя» введите «Сумма на 9 сотрудников».
• Третий шаг Мастера диаграмм позволяет ввести на вкладке «Заголовки» названия графика и осей. В текстовое поле «Название диаграммы» введите «График сравнения стоимости», в текстовое поле «Ось Х (категория)» - «Наименование оборудования», а в текстовое поле «Ось У (категория)» - «Руб. коп.».
• Перейдите на вкладку «Легенда» и в опции «Размещение» установите переключатель в поле «вверху».
• Нажмите кнопку «Далее».
• Установите переключатель в поле «отдельном» и с клавиатуры введите название листа «График».
• Нажмите кнопку «Готово».

33. Скопируйте лист «Сводная», установив копию перед ярлыком «Лист 3» и дайте этой копии листа название «Сортировка»
34.  Проведем сортировку оборудования по возрастанию разности стоимости.

• Выделите на листе «Сортировка» диапазон ячеек В5:Е11.
• Откройте пункт меню «Данные» и щелкните по строке «Сортировка…».
• В опции «Сортировать по» в раскрывающемся списке укажите столбец 5. Переключатель должен быть установлен в поле «по возрастанию», а в опции «Идентифицировать поля по» - «подписям». Нажмите кнопку «ОК».

35. Постройте на отдельном листе, дав ему название «Диаграмма», самостоятельно диаграмму разности стоимости оборудования офиса с помощью ранее рассмотренного нами Мастера диаграмм. Введите в диаграмму подписи значений. Учтите, что в отличие от построенного нами графика в этом задании необходимо выделить одновременно два диапазона ячеек В5:В11 и Е5:Е11.

Примерный образец диаграммы:

Расставьте листы книги в следующей последовательности:

1. 5 сотр.
2. 9 сотр.
3. Сводная
4. График
5. Сортировка
6. Диаграмма.