Конспект лекций по дисциплине ЕН.02. «Информационные технологии в профессиональной деятельности» Тема: Работа в программе Microsoft Excel

Санкт-Петербургское государственное бюджетное специальное
реабилитационное образовательное учреждение среднего профессионального образования техникум для инвалидов «Профессионально-реабилитационный центр»
Методическое пособиеКонспект лекций по дисциплинеЕН.02. «Информационные технологии в профессиональной деятельности»Тема: Работа в программе Microsoft Excel 9277353483610для обучающихся Санкт-Петербургского государственного бюджетного
специального реабилитационного образовательного учреждения среднего профессионального образования техникума для инвалидов
“Профессионально-реабилитационный центр”
Разработали:Таранова О.А.– преподавательСПб ГБУ «Профессионально-реабилитационный центр»Санкт – Петербург2015 г.
Содержание TOC \o "1-3" \h \z \u
1.Обработка данных средствами табличного процессора Microsoft Excel PAGEREF _Toc439946100 \h 32.Основные понятия PAGEREF _Toc439946101 \h 33.Ячейки и их адресация PAGEREF _Toc439946102 \h 44.Диапазон ячеек PAGEREF _Toc439946103 \h 45.Ввод, редактирование и форматирование данных PAGEREF _Toc439946104 \h 46.Ввод текста и чисел PAGEREF _Toc439946105 \h 47.Копирование содержимого ячеек PAGEREF _Toc439946106 \h 58.Форматирование содержимого ячеек PAGEREF _Toc439946107 \h 59.Автоматизация ввода PAGEREF _Toc439946108 \h 610.Формулы PAGEREF _Toc439946109 \h 711.Ссылки на ячейки PAGEREF _Toc439946110 \h 712.Абсолютные и относительные ссылки PAGEREF _Toc439946111 \h 813.Построение диаграмм и графиков PAGEREF _Toc439946112 \h 914.Тип диаграммы PAGEREF _Toc439946113 \h 915.Создание диаграммы. PAGEREF _Toc439946114 \h 1016.Выбор данных PAGEREF _Toc439946115 \h 1017.Оформление диаграммы PAGEREF _Toc439946116 \h 1118.Размещение диаграммы PAGEREF _Toc439946117 \h 1219.Редактирование диаграммы PAGEREF _Toc439946118 \h 1220.Инструменты управления диаграммами. PAGEREF _Toc439946119 \h 1321.Изменение типа диаграммы. PAGEREF _Toc439946120 \h 1322.Замена источника данных. PAGEREF _Toc439946121 \h 1423.Замена столбцов и строк. PAGEREF _Toc439946122 \h 1424.Редактирование элементов диаграммы. PAGEREF _Toc439946123 \h 1425.Удаление диаграммы. PAGEREF _Toc439946124 \h 1526.ОШИБКИ ПРИ ОБРАБОТКЕ ЭЛЕКТРОННЫХ ТАБЛИЦ PAGEREF _Toc439946125 \h 15
Обработка данных средствами табличного процессора Microsoft Excel
Для представления данных в удобном виде используют таблицы, что дает возможность не только отображать, но и обрабатывать данные. Класс программ для этой цели называется табличными процессорами.
Особенность табличных процессоров заключается в возможности применения формул для описания связи между значениями различных ячеек. Расчет по заданным формулам выполняется автоматически. Изменения содержимого какой-либо ячейки приводит к пересчету значений всех ячеек, которые с ней связаны формульными отношениями.
Применение табличных процессоров упрощает работу с данными и позволяет получать результаты без проведения расчетов вручную. Служат для:
• проведения однотипных расчетов над большими наборами данных;
• автоматизации итоговых вычислений;
• решения задач путем подбора значений параметров;
• обработки результатов экспериментов;
• проведения поиска оптимальных значений параметров;
• подготовка табличных документов;
• построение диаграмм и графиков по имеющимся данным.
Программа Microsoft Excel предназначена для работы с таблицами данных, преимущественно числовых. При формировании таблицы выполняют ввод, редактирование и форматирование текстовых и числовых данных, а также формул. Наличие средств автоматизации облегчает эти операции.
Основные понятия
Документ Excel называется рабочей книгой. Рабочая книга представляет собой набор рабочих листов, каждый из которых имеет табличную структуру и может содержать одну или несколько таблиц. В окне документа в программе Excel отображается только текущий рабочий лист, с которым и ведется работа. Каждый рабочий лист имеет название, которое отображается на ярлычке листа, находящемся в его нижней части (Рис. 1). По ярлычкам можно переходить к другим рабочим листам внутри одной книги. Чтобы переименовать рабочий лист, надо дважды щелкнуть на его ярлычке. Рабочий лист состоит из строк и столбцов. Столбцы озаглавлены прописными латинскими буквами, и, далее, двухбуквенными комбинациями. Всего рабочий лист может содержать до 16 384 столбцов от А до IV. Строки последовательно нумеруются цифрами от 1 до 1048 576.
960120-116840Ярлычки листа
00Ярлычки листа

Рис. 1. Расположение ярлычков листа на экране
Ячейки и их адресация
На пересечении столбцов и строк образуются ячейки таблицы. Они являются минимальными элементами для хранения данных. Обозначение отдельной ячейки сочетает в себе номера столбца и строки (в этом порядке), на пересечении которых она расположена (А1, D4). Номер ячейки выполняет функции ее адреса. Адреса ячеек используются при записи формул, определяющих взаимосвязь между значениями, расположенными в разных ячейках. Одна из ячеек всегда активна и выделяется рамкой активной ячейки. Эта рамка играет роль курсора. Операции ввода и редактирования всегда приводятся в активной ячейке.
Диапазон ячеек
На данные с соседних ячейках можно ссылаться в формулах как на единое целое. Такую группу ячеек называют диапазоном. Наиболее часто используются прямоугольные диапазоны, образующиеся на пересечении группы последовательно идущих строк и столбцов. Диапазон ячеек обозначают, указывая через двоеточие номера ячеек в противоположных концах прямоугольника на пример (А1:С15). Выделение диапазона производят протягиванием указателя от одной угловой ячейки до противоположной по диагонали. Чтобы выбрать столбец или строку целиком, следует щелкнуть на их заголовке. Протягиванием по заголовкам можно выбрать несколько идущих подряд столбцов или строк.
Ввод, редактирование и форматирование данных
Отдельная ячейка может содержать данные одного из типов: текст, число, дата, время или формула. Тип данных определяется автоматически при вводе в ячейку. Ввод формулы всегда начинается со знака равенства.
Ввод текста и чисел
Ввод данных осуществляется непосредственно в текущую ячейку или в строку формул. Место ввода обозначается текстовым курсором. Если щелкнуть на строке формул или дважды на текущей ячейке, можно редактировать содержимое ячейки. Чтобы завершить ввод, сохранив данные, используют Enter. Для очистки содержимого ячейки - Delete.
Копирование содержимого ячеек
Копирование и перемещение ячеек в Excel можно осуществлять методом перетаскивания (для небольшого числа ячеек) или через буфер (при работе с большими диапазонами).
Чтобы скопировать или переместить текущую ячейку (выделенный диапазон) вместе с содержимым, следует навести курсор на рамку текущей ячейки, он примет вид стрелки. Теперь ячейку можно перетащить в любое место рабочего листа (точка вставки помечается всплывающей подсказкой).
348869016954500
Для выбора способа перетаскивания можно использовать специальное перетаскивание при нажатой правой кнопке мыши. При отпускании кнопки в появившемся меню можно выбрать операцию.
При использовании буфера обмена вставка данных возможна лишь немедленно после копирования или вырезания. "Вырезанные" данные удаляются из места их исходного размещения только в момент вставки. Команды Копировать и Вырезать можно найти в контекстном меню, которое открывается при нажатии правой клавиши мыши на объекте или на Вкладке Главная, подменю Буфер обмена.

Контекстное менюПодменю Буфер обмена
Форматирование содержимого ячеекПо умолчанию текстовые данные выравниваются по левому краю ячейки, а числа по правому. Изменить формат отображения данных - команда Формат, Ячейки. Вкладки этого диалогового окна (Рис. 2) позволяют выбирать формат записи данных, направление текста и метод его выравнивания, определять шрифт и начертание, задавать фоновый цвет, управлять отображением и видом рамок.

Рис. 2. Окно Формат ячеек
Автоматизация ввода
Для ввода повторяющихся или однотипных данных. К числу средств автоматизации ввода относятся: автозавершение, автозаполнение числами и автозаполнение формулами.
Автозавершение применяют при вводе в ячейки одного столбца текстовых строк среди которых есть повторяющиеся. Если при вводе в ячейку обнаружено соответствие ранее введенным строкам, введенный текст автоматически дополняется. Нажатие Enter подтверждает автозавершение, иначе ввод можно продолжать, игнорируя предлагаемый вариант. Операция действует при введении данных в ячейки, идущие подряд.
Автозаполнение числами. В правом нижнем углу рамки текущей ячейки имеется черный квадратик – маркер автозаполнения. При наведении на него указателя мыши (обычно он имеет форму толстого белого креста), он приобретает форму тонкого черного крестика. Перетаскиванием маркера можно "размножить" содержимое ячейки по вертикали или горизонтали.
Если ячейка содержит число (дату, денежную сумму), то при перетаскивании происходит копирование ячеек или их заполнение арифметической прогрессией. При перетаскивании правой кнопкой мыши в диалоговом окне (появляется после отпускания мыши) можно выбрать способ автозаполнения.
Пример: пусть А1 содержит 1. Перетаскиваем маркер на ячейки В1, С1. В открывшемся меню выбираем Копировать ячейки, все ячейки будут содержать 1. Если выберем Заполнить, то в ячейках окажутся числа 1, 2 и 3.
Чтобы сформулировать условия заполнения нужно выполнить последовательность команд Правка, Заполнить, Прогрессия. В окне выбирается тип прогрессии, величина шага и предельное значение.
Автозаполнение формулами выполняется также. Ее особенность заключается в необходимости копирования ссылок на другие ячейки. В ходе автозаполнения принимается во внимание характер ссылок в формуле: относительные ссылки изменяются в соответствии с относительным расположением копии и оригинала, абсолютные остаются без изменений.
Формулы Вычисления осуществляются при помощи формул. Формула может содержать числовые константы, ссылки на ячейки и функции Excel, соединенные знаками математических операций. Скобки позволяют изменить стандартный порядок выполнения действий. Если ячейка содержит формулу, то в рабочем листе отображается текущий результат вычисления этой формулы. Если сделать ячейку текущей, то сама формула отображается в строке формул (Рис. 3).

Рис. 3. Отображение формулы в «строке формул»
Правило использования формул в Excel:
если значение ячейки действительно зависит от других ячеек таблицы, всегда следует использовать формулу. Это гарантирует, что последующее редактирование таблицы не нарушит ее целостности и правильности вычислений в ней.
формулы обязательно должны вводиться в английской раскладке клавиатуры.
Ссылки на ячейки
Формула может содержать ссылки, то есть адреса ячеек, содержимое которых используется в вычислениях. Таким образом, результат вычисления формулы зависит от числа, находящегося в другой ячейке. Ячейка, содержащая формулу, является зависимой. Значение, отображаемое в ячейке с формулой, пересчитывается при изменении значения ячейки, на которую указывает ссылка. Ссылку можно задать разными способами:
адрес ячейки можно ввести вручную;
щелкнуть на нужной ячейке или выбрать диапазон, адрес которого нужно ввести (выделяются при этом пунктирной рамкой).
Обратите внимание на кнопки минимизации, присоединенные к соответствующим полям у всех диалоговых окон, которые требуют указания номеров ячеек (Рис. 4).
308864015684500
Рис. 4. Кнопки минимизации на панели редактирования свойств формулы
Эти кнопки сворачивают окно до минимального размера, что облегчает выбор ячеек на листе. Для редактирования формулы следует дважды щелкнуть на соответствующей ячейке. При этом ячейки, от которых зависит значение формулы, выделяются на рабочем листе цветными рамками, а сами ссылки отображаются в ячейке и в строке формул тем же цветом. Это облегчает редактирование формул.
Абсолютные и относительные ссылкиПо умолчанию, ссылки на ячейки в формулах рассматриваются как относительные. Это означает, что при копировании формула адреса в ссылках автоматически изменяются в соответствии с относительным расположением исходной ячейки и создаваемой копии.
При абсолютной адресации адреса ссылок при копировании не изменяются, так что ячейка, на которую указывает ссылка, рассматривается как нетабличная. Для изменения способа адресации при редактировании формулы надо выделить ссылку на ячейку и нажать клавишу F4. Адрес ячейки предваряется знаком $.

Построение диаграмм и графиков
Чтобы помочь вам проанализировать и понять информацию, представленную в числовом виде, MS Excel позволяет вывести ее в графическом виде – как график или диаграмму. Способность наглядно представлять большие массивы числовых данных делает MS Excel очень удобным средством отслеживания зависимостей и составления прогнозов.
В программе Excel термин диаграмма используется для обозначения всех видов графического представления числовых данных. Построение графического изображения производится на основе ряда данных. Так называют группу ячеек с данными в пределах отдельной строки или столбца. На одной диаграмме можно отображать несколько рядов данных.
Чтобы создать диаграмму, упрощающие понимание данных рабочего листа, сначала потребуется познакомиться со структурой и назначением самой диаграммы.
Ряды данных. Числовые данные, на основе которых MS Excel строит диаграмму.
Ось Х. Определяет ширину диаграммы.
Ось Y. Задает высоту диаграммы.
Легенда. Содержит текст, описывающий отображение на диаграмме данные.
Заголовок диаграммы. Описывает назначение диаграммы.
Диаграмма представляет собой вставной объект, внедренный на один из листов рабочей книги. Она может отображаться на листе с данными или на любом другом листе (часто для диаграммы отводят отдельный лист). Диаграмма сохраняет связь с данными, на основе которых она построена, и при обновлении этих данных немедленно изменяет свой вид.
Тип диаграммы
Форму диаграммы выбирают в разделе Диаграммы на вкладке Вставка. Для выбранного типа диаграммы справа указывается несколько вариантов представления данных (Рис. 5). Необходимо выбрать один из вариантов и нажать на кнопку ОК.

Рис. 5. Окно выбора типа диаграммы
Гистограмма. Количественные показатели представляются в виде вертикальных столбцов, увеличивающихся в верхнем направлении. Эффективна при сравнении двух или большего количества показателей, например ежемесячных продаж или объемов продаж разных менеджеров.
График. Количественные показатели представляются в виде линий. На графике показаны только самые верхние точки гистограммы.
Круговая. Подобна графику, но в ней цветом заливается вся область под линией.
Линейчатая. Напоминает гистограмму, повернутую на 900: столбцы «растут» слева направо.
С областями. Позволяет сравнить показатели относительно общего, например, объем продаж, совершенных одним менеджером, по отношению к объему продаж всей компании.
Создание диаграммы.Перед созданием диаграммы необходимо ввести дополнительную информацию, которая нужна для обозначения в MS Excel отдельных элементов диаграммы. (Вы вполне можете изменить диаграмму и в дальнейшем, если не хотите, чтобы MS Excel выводила отдельные подписи прямо сейчас.)
Выбор данных
Для выбора данных, по которым будет строиться диаграмма, используется команда Выбор данных на вкладке Конструктор (Рис. 6). Если диапазон выбран заранее, то в области окна мастера появится отображение диаграммы (Рис. 7).

Рис.6. Окно выбора источника данных

Рис.7. Отображение диаграммы
Итак, чтобы создать диаграмму, выполните ряд действий.
Выделите числовые данные и подписи данных, которые будут принимать участие в создании диаграммы.
Перейдите во вкладку Вставка. Изучите список доступных типов диаграмм.
Щелкните на кнопке понравившегося типа диаграммы, например График или Гистограмма. На экране появиться палитра подтипов диаграммы.
Выберите необходимый вариант. MS Excel создаст диаграмму и отобразит на экране вкладку Работа с диаграммами.
Оформление диаграммы
На вкладке Макет режима Работа с диаграммами (Рис. 8) задаются:
название диаграммы, подписи осей;
отображение и маркировка осей координат;
отображение сетки линий, параллельных осям;
описание построенных графиков (Легенда);
отображение надписей, соответствующих отдельным элементам данных на графике (Подписи данных);
представление данных, использованных при построении графика, в виде таблицы (Таблица данных).
и т.д.


Рис. 8.
В зависимости от типа диаграммы некоторые из перечисленных вкладок могут отсутствовать.
Размещение диаграммы
Указать место размещения диаграммы (новый рабочий лист или уже имеющийся) (Рис.9) можно на Вкладке Конструктор режима Работы с диаграммами. Этот выбор важен только для последующей печати документа, содержащего диаграмму.

Рис.9 Окно Выбора размещения диаграммы
Редактирование диаграммы
Готовую диаграмму можно изменить. Она состоит из набора отдельных элементов, таких как сами графики (ряды данных), оси координат, заголовок диаграммы, область построения и т.д.
При щелчке на элементе он выделяется маркерами, а при наведении на него указателя мыши - описывается всплывающей подсказкой. Открыть диалоговое окно для форматирования элемента - меню Формат (для выделенного элемента) или через контекстное меню (команда Формат). Если требуется внести существенные изменения, следует вновь воспользоваться командами на вкладках Конструктор, Макет. Предварительно диаграмму надо выделить.
Чтобы удалить диаграмму, можно удалить рабочий лист, на котором она расположена (Контекстное меню, Удалить), или выбрать диаграмму, внедренную в рабочий лист, и нажать на клавиатуре клавишу Delete.
Инструменты управления диаграммами.После щелчка на диаграмме MS Excel выведет на экран вкладку Работа с диаграммами, которая содержит инструменты управления диаграммами, разбитые на четыре категории.
Тип. Позволяет изменять тип диаграммы.
Данные. Применяется для изменения источника данных диаграммы: числовых данных, откладываемых вдоль осей X и Y.
Макеты диаграмм. Инструменты этого раздела применяются для изменения отдельных элементов диаграмм, например заголовка диаграммы, подписей осей X и Y, расположения легенды (вверху, внизу, слева или справа) и т.д.
Стили диаграмм. Предоставляет возможность кардинальным образом изменить вид диаграммы.
Изменение типа диаграммы.Имея в своем распоряжении готовую диаграмму, вы можете поэкспериментировать со способом представления числовых данных в графическом виде. Например, изменить гистограмму на диаграмму с областями. Чтобы изменить тип диаграммы, выполните ряд действий.
Щелкните на диаграмме, тип которой требуется изменить. На экране появится вкладка Работа с диаграммами.
Перейдите во вложенную вкладку Конструктор.
Щелкните на кнопке Изменить тип диаграммы в разделе Тип. Вы увидите диалоговое окно Изменение типа диаграммы.
Выберите необходимый тип диаграммы, например Круговая или Линейчатая. В правой панели диалогового окна вы увидите различные макеты для указанного типа диаграммы.
Щелкните на понравившемся макете диаграммы в правой панели.
Щелкните на кнопке ОК. MS Excel применит к диаграмме новый тип.
Совет. Если вам не нравится, как выглядит диаграмма, нажмите <Ctrl+Z>, чтобы вернуться к исходному дизайну.
Замена источника данных.Еще один способ изменения вида диаграммы – это замена источника данных (ячеек с числовыми данными, на основе которых строится диаграмма). Чтобы изменить источник данных для диаграммы, выполните ряд действий.
Щелкните на диаграмме, которую требуется изменить. На экране появится вкладка Работа с диаграммами.
Перейдите во вложенную вкладку Конструктор.
Щелкните на кнопке Выбрать данные раздела Данные. Вы увидите диалоговое окно Выбор источника данных.
(не обязательно) Щелкните на кнопке свертывания диалогового окна для просмотра большей части текущего рабочего листа.
Выделите все ячейки, данные которых принимают участие в построении диаграммы, включая ячейки с подписями, значениями и формулами.
Щелкните на кнопке ОК.
MS Excel выведет диаграмму, построенную на основе данных.Замена столбцов и строк.Программа MS Excel создает диаграммы, автоматически размещая подписи данных вдоль оси Х, а значения – вдоль оси Y. Тем не менее, подписи со значениями можно поменять местами, сразу же знакомясь с получаемым результатом.
Чтобы поменять столбцы и строки данных местами при построении диаграммы, выполните приведенную ниже инструкцию.
Щелкните на диаграмме, которую необходимо изменить. На экране появится вкладка Работа с диаграммами.
Перейдите во вложенную вкладку Конструктор.
Щелкните на кнопку Строка/столбец раздела Данные. MS Excel поменяет местами данные, выводимые вдоль осей X и Y.
Редактирование элементов диаграммы.Чтобы сделать диаграмму информативнее, в нее добавляются вспомогательные текст и объекты.
Заголовок диаграммы.
Легенда.
Подписи данных.
Подписи осей.
Название осей.
Сетка.
Каждый элемент диаграммы MS Excel можно либо переместить в другое место, либо вообще удалить. Чтобы изменить вспомогательные элементы диаграммы, выполните ряд действий.
Щелкните на диаграмме, которую необходимо изменить. На экране появится вкладка Работа с диаграммами.
Перейдите во вложенную вкладку Макет.
Щелкните на кнопке с названием элемента диаграммы, которую вы хотите изменить, например Легенда или Названия осей. На экране появится меню доступных вариантов.
Удаление диаграммы.Диаграммы весьма функциональны, но даже их изредка приходится удалять. Чтобы удалить диаграмму, выполните следующее.
Щелкните на диаграмме, которую необходимо удалять.
Нажмите клавишу <Delete>.
Совет. Вы также можете щелкнуть на диаграмме правой кнопкой мыши и выбрать в появившемся контекстном меню команду Вырезать.
ОШИБКИ ПРИ ОБРАБОТКЕ ЭЛЕКТРОННЫХ ТАБЛИЦ###...### Значение (результат) формулы не умещается в ячейке или слишком много значащих цифр в числе
#ДЕЛ/0! Делитель в Формуле принял значение «Ноль» (попытка деления на ноль)
#ИМЯ? Указано имя, которое невозможно распознать. Например, искажено имя функции, использовано имя ячейки, которое ранее не было определено или имя ячейки набрано русскими буквами
#ЧИСЛО! Недопустимое значение аргумента в функции (например, извлекается корень из отрицательного числа)
#ССЫЛКА! Недопустимая ссылка на ячейку
#ЗНАЧ! Недопустимый тип аргумента функции или операнда формулы (например, вместо числа введен текст или использована относительная ссылка, вместо абсолютной и при копировании формулы используются пустые (незанятые) ячейки)