Лабораторная работа №3.

Тема: Работа с табличными данными в MS Excel.

1. Цель лабораторной работы.

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

2. В результате выполнения лабораторной работы студенты должны знать:

• приемы работы в табличном процессоре MS Excel;

• принципы создания электронных таблиц;

• возможности обработки больших массивов данных;

• использование вложенности формул;

• принципы работы с различными видами диаграмм.

2. Теоретический материал для домашнего изучения.

Microsoft Excel - мощный табличный процессор, предназначенный для выполнения всех процессов обработки математических формул и статистических данных. Excel позволяет автоматизировать процессы расчета математических формул, существенно упрощает работу с большими массивами числовых данных, позволяет строить графики и диаграммы. Для ограничения доступа к документу можно установить пароль на таблицу, который Excel будет спрашивать при загрузке таблицы или для выполнения с ней каких-либо действий. Excel позволяет открывать много окон для одновременной работы с несколькими таблицами, а так же разбить одно активное окно по горизонтали на два и выровнять их.

Запуск и прекращение работы в процессоре. Запуск Excel с помощью меню Пуск:

Для того чтобы запустить MS Excel, выполните следующие действия:

• Нажмите кнопку Пуск на панели задач, расположенной в нижней части экрана.

• Переместите указатель мыши на команду Программы. На экране появится меню Программы.

• Нажмите мышью пункт меню Microsoft Excel.

• После запуска MS Excel автоматически открывает новый документ, основанный на шаблоне “Книга”.

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

Работа с табличным процессором MS Excel.

Формат ячеек.

Ввод чисел, текста, даты или времени суток

1 . Укажите ячейку, в которую необходимо ввести данные.

2 . Наберите данные и нажмите клавишу ENTER или клавишу табуляции.

При вводе даты используйте точку или дефис в качестве разделителя, например, 09.05.96 или Янв-96.

Для отображения времени суток в 12-часовом формате введите букву a или p, отделенную пробелом от значения времени, например 9:00 p. В противном случае время будет интерпретировано на основе 24-часового формата.

Ввод чисел.

Вводимые в ячейку числа интерпретируются как константы.

В Microsoft Excel число может состоять только из следующих символов:

0 1 2 3 4 5 6 7 8 9 + - ( ) , / $ % . E e

Стоящие перед числом знаки плюс (+) игнорируются, а запятая интерпретируется как разделитель десятичных разрядов. Все другие сочетания клавиш, состоящие из цифр и нецифровых символов, рассматриваются как текст.

Символы, которые рассматриваются как числа, зависят от установленных параметров в компоненте Язык и стандарты панели управления.

Перед рациональной дробью следует вводить 0 (нуль), чтобы избежать ее интерпретации как формата даты; например, вводите 0 1/2

Перед отрицательным числом необходимо вводить знак минус (-) или заключать его в круглые скобки ( ).

Введенные числа выравниваются в ячейке по правому краю. Чтобы изменить выравнивание, выделите ячейки, выберите команду Ячейки в меню Формат, а затем на вкладке Выравнивание установите необходимые параметры.

Формат числа, назначаемый ячейке, определяет способ просмотра числа на листе. Если число вводится в ячейку, имеющую общий числовой формат, то может быть использован другой формат. Например, если вводится 14,73р., то будет использован денежный формат. Чтобы изменить числовой формат, выделите ячейки, которые содержат числа, выберите команду Ячейки в меню Формат, а затем на вкладке Число выберите необходимый формат.

В ячейках, имеющих общий числовой формат, отображаются целые числа (789), десятичные дроби (7,89) или числа, представленные в экспоненциальной форме (7,89E+08), если число длиннее ширины ячейки. Используя общий формат можно просматривать до 11 разрядов, включая десятичные запятые и такие символы, как "E" и "+." Чтобы использовать числа с большим количеством разрядов, используйте встроенный экспоненциальный числовой (экспоненциальное представление числа) или пользовательский формат.

Независимо от количества отображаемых разрядов числа хранятся с точностью до 15 разрядов. Если число имеет больше 15 значащих знаков, то разряды после 15-го преобразуются в нули (0).

Число хранится в числовом формате, даже если была использована команда Ячейки, чтобы назначить текстовый формат ячейкам, содержащим числа. Для интерпретации чисел, например, инвентаризационных номеров, как текста, необходимо назначить текстовый формат незаполненным ячейкам. Только после этого следует вводить числа. Если числа уже введены, то назначьте ячейкам текстовый формат и, выделяя каждую ячейку, нажимайте клавишу F2, а затем – клавишу ENTER, чтобы ввести заново данные.

Ввод текста.

В Microsoft Excel текстом является любая последовательность, состоящая из цифр, пробелов и нецифровых символов, например, приведенные ниже записи интерпретируются как текст:

10AA109, 127AXY, 12-976, 208 4675.

Введенный текст выравнивается в ячейке по левому краю. Чтобы изменить выравнивание, выберите команду Ячейки (вкладка Выравнивание) в меню Формат, а затем измените необходимые параметры.

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

Чтобы начать в ячейке новую строку, нажмите клавиши ALT+ENTER.

Ввод дат и времени суток.

В Microsoft Excel даты и время суток интерпретируются как числа. Внешнее представление времени или даты на листе зависит от числового формата, назначенного ячейке. При вводе значений даты или времени происходит их автоматическое распознавание, и общий формат ячейки заменяется на встроенный формат даты или времени. По умолчанию значения даты и времени выравниваются в ячейке по правому краю. Если не происходит автоматического распознавания формата даты или времени, то введенные значения интерпретируются как текст, который выравнивается в ячейке по левому краю.

Параметры, установленные с помощью значка Язык и стандарты панели управления, определяют формат для текущей даты и времени и символы, использующиеся в качестве разделителей, например, двоеточие (:) и точка (/) в стандарте России.

Чтобы ввести дату и время суток в одну и ту же ячейку, в качестве разделителя даты и времени используйте пробел.

Чтобы ввести время с использованием 12-часового формата, введите после значения времени отделенные пробелом буквы AM или PM (A или P). В противном случае, время будет интерпретировано с использованием 24-часового формата. Например, при вводе значения 3:00 вместо 3:00 PM, время будет храниться в ячейке в виде 3:00 AM.

Независимо от формата, используемого для представления даты или времени, в Microsoft Excel все даты сохраняются как последовательные числа и время сохраняется в виде десятичной дроби. Чтобы показать дату в виде числа или время в виде дроби, выделите ячейки, содержащие дату или время, выберите команду Ячейки в меню Формат, а затем на вкладке Число из списка Числовые форматы выберите элемент Общий.

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

="05.12.94"-"03.05.94"

Автозаполнение, основанное на смежных ячейках.

С помощью перетаскивания маркера заполнения ячейки можно копировать ее в другие ячейки той же строки или того же столбца.

Маркер заполнения— небольшой черный квадрат в углу выделенного диапазона. Попав на маркер заполнения, указатель принимает вид черного креста. Чтобы скопировать содержимое выделенного диапазона в соседние ячейки или заполнить их подобными данными (например днями недели), нажмите левую кнопку мыши и перемещайте мышь в нужном направлении.

Если ячейка содержит число, дату или период времени, который может являться частью ряда, то при копировании происходит приращение ее значения. Например, если ячейка имеет значение "Январь," то существует возможность быстрого заполнения других ячеек строки или столбца значениями "Февраль", "Март" и так далее. Могут создаваться пользовательские списки автозаполнения для часто используемых значений, например, названий торговых районов компании.

Заполнение рядов чисел, дат и других элементов.

1. Укажите первую ячейку заполняемого диапазона и введите начальное значение. Чтобы задать приращение, отличное от 1, укажите вторую ячейку ряда и введите соответствующее ей значение. Величина приращения будет задана разностью значений, находящихся в этих значениях.

2. Выделите ячейку или ячейки, содержащие начальные значения.

3. Перетащите маркер заполнения через заполняемые ячейки.

Для заполнения в возрастающем порядке перетащите маркер вниз или вправо.

Для заполнения в убывающем порядке перетащите маркер вверх или влево.

Ввод формулы.

1. Укажите ячейку, в которую необходимо ввести формулу.

2. Введите = (знак равенства).

3. Введите формулу.

4. Нажмите клавишу ENTER.

Вычисление значений в формулах.

Ф ормула является основным средством для анализа данных. С помощью формул можно складывать, умножать и сравнивать данные, а также объединять значения. Формулы могут ссылаться на ячейки текущего листа, листов той же книги или других книг. В следующем примере складывается значение ячейки B4 с числом 25. Полученный результат делится на сумму ячеек D5, E5 и F5. Чтобы ознакомиться с примерами наиболее часто используемых в Microsoft Excel формул, нажмите кнопку.

Синтаксис формулы.

Синтаксисом формул называется порядок, в котором вычисляются значения. Синтаксисом формулы задается последовательность вычислений. Формула должна начинаться со знака равенства (=), за которым следует набор вычисляемых величин. В следующем примере представлена формула, вычисляющая разность между числами 5 и 1. Результат выполнения отобразится в ячейке, в которой указана формула: =5-1

Ссылки на ячейку.

В формуле может быть указана ссылка на ячейку. Если необходимо, чтобы в ячейке содержалось значение другой ячейки, введите знак равенства, после которого укажите ссылку на эту ячейку. Ячейка, содержащая формулу, называется зависимой ячейкой ѕ ее значение зависит от значения другой ячейки. Формула может вернуть другое значение, если изменить ячейку, на которую формула ссылается. Следующая формула умножает значение ячейки B15 на число 5. Формула будет пересчитываться при изменении значения ячейки B15: =B15*5

Формулы могут ссылаться на ячейки или на диапазоны ячеек, а также на имена или заголовки, представляющие ячейки или диапазоны ячеек.

Функции.

В Microsoft Excel содержится большое количество стандартных формул, называемых функциями. Функции используются для простых или сложных вычислений. Наиболее распространенной является функция СУММ, суммирующая диапазоны ячеек. Несмотря на то, что пользователь может создать формулу, суммирующую значения несколько ячеек, функция СУММ обладает большими возможностями и может суммировать несколько диапазонов ячеек.

Автосумма.

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

Быстрое заполнение повторяющихся данных в столбце.

Если несколько первых символов, вводимых в ячейку, совпадают с символами записи, ранее введенной в этом столбце, то недостающая часть набора будет произведена автоматически. В Microsoft Excel автоматический ввод производится только для тех записей, которые содержат текст или текст в сочетании с числами. Записи, полностью состоящие из чисел, дат или времени, необходимо вводить самостоятельно.

Для подтверждения предлагаемого варианта, нажмите клавишу ENTER.

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

Работа с данными.

Данные в Microsoft Excel располагаются на листах. Таким образом документ Microsoft Excel представляет собой книгу, состоящую из одного или более листов. Данные на листах могут быть связаны. Каждый лист имеет имя. Чтобы добавить лист, выберите команду Лист в меню Вставка.

При выборе более чем одного листа изменения, сделанные на текущем листе, повторяются на всех остальных листах. Это может привести к потере данных.

Чтобы выбрать:

Отдельный лист	Укажите ярлычок листа.
Два смежных листа или более	Укажите ярлычок первого листа, затем, удерживая клавишу SHIFT, укажите ярлычок последнего листа.
Два несмежных листа или более	Укажите ярлычок первого листа, затем, удерживая клавишу CTRL, укажите ярлычки остальных листов.
Все листы книги	Установите указатель на произвольный ярлычок листа и нажмите правую кнопку мыши. Затем выберите команду Выбрать все листы в контекстном меню.

Чтобы удалить лист, необходимо выделить его и выбрать команду Удалить лист в меню Правка. Имя листа можно изменить дважды щелкнув на ярлычке листа.

Построение графиков и диаграмм.

Диаграммы — это графическое представление данных. Они используются, для анализа и сравнения данных, представления их в наглядном виде.

Диаграмма состоит из элементов, линий, столбиков, секторов, точек и т.п. Каждому элементу диаграммы соответствует число в таблице. Числа и элементы диаграммы связаны между собой таким образом, что при изменении чисел автоматически изменяется изображение элементов диаграммы и наоборот. Различают два вида диаграмм;

• внедренные диаграммы — сохраняются на рабочем листе вместе с данными;

• диаграммные листы — диаграмма в формате полного экрана на новом листе.

Диаграмма создается с помощью Мастера диаграмм, вызываемого командой Вставка, Диаграмма или кнопкой  на панели Стандартная либо кнопкой  на панели Диаграмма. Мастер диаграмм позволяет строить диаграммы 14 стандартных типов плоскостного и объемного представления (с областями, линейчатая, гистограмма, график, кольцевая, лепестковая, точечная, пузырьковая, поверхностная и др.) и 22 нестандартных типа. Некоторые типы диаграмм представлены на рис. 1.

  а

б

в

г

Рисунок 1 - Примеры различных типов диаграмм Excel : а – объемный вариант гистограммы, б – лепестковая диаграмма, в – линейчатая диаграмма, г – смешанная диаграмма

Мастер Диаграмм осуществляет построение новой диаграммы в интерактивном режиме за четыре шага только для выделенного блока ячеек — диапазона (области) данных для построения диаграммы.

Примечание. Блок ячеек может быть выделен как до вызова Мастера диаграмм, так и после его вызова.

Этап 1. Выбор типа и формата диаграммы. На этом этапе необходимо выбрать тип диаграммы (рис. 2) и задать (в окне справа) формат, который делает ее более выразительной. После выбора надо нажать кнопку <Далее> и перейти на следующий этап.

Этап 2 . Выбор и указание диапазона данных для построения диаграммы.

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

Рисунок 2 - Диалоговое окно Мастер диаграмм для выбора типа и формата диаграммы (этап 1)

Рисунок 3 - Диалоговое окно Мастер диаграмм  для задания диапазона данных диаграмм (этап 2)

Примечание. Указание диапазона данных, расположенных в несмежных рядах или столбцах, должно производиться при нажатой клавише <Ctrl. В строке Диапазон адреса несмежных интервалов данных будут разделяться точкой с запятой.

Блок ячеек может включать как сами данные, так и их названия, которые используются для обозначения меток по оси Х и в легендах (расшифровка условных обозначений на диаграммах). Например, на рис. 4 показан блок исходных данных, состоящий из чисел и названий (номеров групп).

Блок ячеек может содержать несмежные ячейки одного рабочего листа. В этом случае выделенные блоки должны иметь одинаковую конфигурацию, например, как показано на рис. 5.

Рисунок 4 - Блок смежных ячеек с исходными данными для построения диаграммы «Сведения об успеваемости»

Рисунок 5 - Блоки несмежных ячеек для построения диаграммы

Excel позволяет также строить диаграмму на основании данных, распределенных по листам одной и той же или разных рабочих книг. Блоку ячеек можно заранее присвоить имя (команда Вставка, Имя, Присвоить).                                

Этап 3 . Задание параметров диаграммы. Задание параметров диаграммы осуществляется в окнах вкладок Мастера диаграмм, представленного на рис. 6.

На вкладке Заголовки вводятся поочередно на соответствующую строку название диаграммы, название оси X, название оси У, название оси Z.

На вкладке Оси устанавливаются переключатели выбора вида обозначения меток осей.

На вкладке Линии сетки устанавливаются переключатели отображения сетки на диаграмме.                                                          

На вкладке Оси указывается место расположения легенды.

На вкладке Таблица данных устанавливается переключатель отображения на диаграмме таблицы исходных данных.                                                 

На вкладке Подписи данных устанавливается переключатель отображения значений данных на диаграмме.

Рисунок 6 - Диалоговое окно Мастер диаграмм для задания параметров диаграммы (этап 3)

Этап 4. Размещение диаграммы. Созданную диаграмму можно разместить на том же листе, где находится таблица с исходными данными, либо на отдельном листе. В диалоговом окне Мастер диаграмм на этапе 4 (рис. 7) для этого надо установить соответствующий переключатель и нажать кнопку <Готово>.

Рисунок 7 - Диалоговое окно Мастер диаграмм для выбора места размещения диаграммы (этап 4).

Редактирование диаграмм.

Редактирование диаграмм выполняется как с помощью контекстного меню, так и с помощью команд управляющего меню Диаграмма. Вызов контекстного меню осуществляется путем установки указателя мыши в пустое место диаграммы (т.е. в один из четырех ее углов) и нажатия правой клавиши мыши. Контекстное меню имеет  вид, представленный на рис. 8. Пункты меню 1-5 предоставляют пользователю возможность вернуться к любому из четырех этапов создания диаграммы и осуществить необходимые изменения т.е.:

• изменить тип  и формат диаграммы;

• изменить исходные данные:

• переопределить исходный интервал ячеек, на основании которых  построена диаграмма;

• переопределить ориентацию рядов и их название;

• изменить данные, используемые для подписей оси X;

• изменить параметры диаграммы (заголовки, оси, линии сетки, легенду, подписи данных);

• изменить размещение диаграммы.

Рисунок 8 - Контекстное меню

Импорт данных.

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

1. Выберите пункт меню Файл \ Открыть .

2. В поле Папка выберите диск, папку или адрес в сети Интернет, определяющие местоположение необходимого файла.

3. В поле Тип фала выберите Текстовые файлы.

4. Дважды щелкните мышью импортируемый файл.

5. Следуйте инструкциям мастера текстов для указания способа разбиения текста на столбцы.