Лабораторная работа №1 EXCEL

5

EXCEL

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

Цель работы: научиться работать с электронными таблицами и научиться строить различные диаграммы.

Краткие теоретические сведения

Excel является программой для расчетов с помощью электронных таблиц.

Интерфейс окна приложения Excel аналогичен интерфейсу окна приложения Word (строка заголовка, строка меню, панели инструментов, строка состояния). Но добавляется строка формул, которой нет в Word.

Различают два вида отображения документа Excel – «обычный» и «разметка страницы», которые можно установить в меню Вид.

Параметры страницы настраиваются в меню Файл/Параметры страницы. Здесь же можно установить Верхний и Нижний колонтитулы на странице. В верхнем колонтитуле можно указать, например, номер группы, в нижнем – ФИО студента. Во вкладке «Лист» можно настроить последовательность вывода страниц.

Рабочая книга. Документ Excel представляет собой рабочую книгу, состоящую из совокупности рабочих листов, сохраняемых на диске в едином файле. По умолчанию в книге 3 листа. Это количество можно изменить (до 255 ) в меню Сервис/Параметры/вкладка Общие. Также в книгу в любое время можно добавить или удалить листы (через контекстное меню с пом. правой кнопки). Листы в книге можно склеить (кн.Shift+щелчки на тех листах, которые надо склеить). Информация, записанная на склеенных листах, одинакова. Например, если надо создать один и тот же шаблон таблицы на нескольких листах, их надо склеить, один раз создать таблицу, затем листы «разгруппировать» через контекстное меню. На всех листах, которые были склеены, будет одна и та же таблица.

Кроме рабочих листов, в рабочей книге можно хранить диаграммы, построенные на основе данных одной или нескольких таблиц, и макросы. Макрос – это программа на языке Visual Basic, обрабатывающая данные таблицы.

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

Рабочий лист. Состоит из электронных ячеек, имеющих адрес: А1, В10 и т.д. Адрес текущей ячейки отображается в поле имени (крайнее левое поле строки формул). Рабочие листы содержат 256 столбцов и 65536 строк. Заголовки столбцов – A…Z, AA…AZ, BA…BZ. Заголовки строк: 1 до 65536.

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

Существует два представления данных ячейки: внутримашинное и экранное. Внутримашинное используется для вычислений, это внутренние значения ячеек, а не отображаемые на экране. Экранное представление определяется форматом ячейки.

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

Числа. Числа хранятся в машине с наибольшей точностью. Экранное представление числа определяется форматом: Формат/ Ячейки/ Число/ Числовые форматы. Можно вводить целые числа, десятичные дроби или числа в степенной (экспоненциальной) форме. Если ячейка заполняется знаками (диез), это значит, что вводимое число превышает ширину столбца.

Текст. Это любой введенный набор символов, которые Excel не воспринимает как число, дату и время, логическое значение или ошибочное значение. В ячейку можно ввести до 255 символов текста. Чтобы ввести в формулу числа как текст, надо заключить его в кавычки. =”45,00”.

Форматирование текста: Формат/Ячейки/вкладки Выравнивание, Шрифт, Граница, Вид.

Дата и время. Дата представлена в машине в виде числа, определяемого количеством дней от системной даты (1900) до представленной в

ячейке. Это можно увидеть, если в ячейке с датой выбрать формат «Общий». Дата 22.01.2005 эквивалентна числу 38374 дней от 01.01.1900 , а дата 07.01.2005 – числу38359 дней от 01.01.1900. Поэтому над датами можно производить операции сложения и вычитания (в ячейках с датой «15.01.1900» и с числом «15» находится формула =A1-B1, вычисляющая количество дней между датами «22.01.2005-07.01.2005». Разница равна 15). Время представлено в машине в виде дроби. Это также можно увидеть, если в ячейке со временем выбрать формат «Общий». Время 16:14 эквивалентно дроби 0,6763889.

Экранное представление даты и времени определяется также в меню Формат/Ячейки/Число/Числовые форматы. Чтобы быстро ввести в ячейку текущее время, надо нажать Ctrl+<:>, а для текущей даты – Ctrl+<;>.

Логические значения принимают значения «истина» и «ложь». Данные значения являются результатом выполнения логических операций и операций сравнения.

Ошибочные значения являются результатом ошибочных вычислений. Ошибочные значения начинаются со знака диез : н/д! (недопустимое значение), ссылка! (неверная ссылка), знач!(неверный тип аргумента в функции), имя!(не понимает имя), число!(не может правильно истолковать формулу в ячейке) и т.д.

Диапазон ячеек – группа последовательных ячеек. В ссылках на диапазон используются следующие адресные операции:

: (двоеточие) –позволяет ссылаться на все ячейки между границами диапазона вклю-

чительно (А1:В15);

, (запятая) – оператор объединения диапазонов ячеек или отдельных ячеек

(А2:В5, А6);

ب (пробел) – оператор пересечения, который ссылается на общие ячейки диапазонов,

Β5:B15ٮ A7:D7. В этом примере ячейка В7 является общей для двух диапазонов.

Ввод, редактирование и форматирование данных.

Различают непосредственный ввод данных и использование средств автоматизации при вводе.

Непосредственный – непосредственный ввод данных в текущую ячейку. Для завершения ввода в текущей ячейке и для перехода к последующей ячейке надо нажать одну из следующих клавиш

Вниз	Enter
Вверх	Shift+Enter
Направо	Tab
Налево	Shift+Tab

При вводе одних и тех же данных в диапазон надо: Выделить диапазон – Ввести данные в активную ячейку диапазона – нажать Ctrl+Enter.

Автоматизация ввода.

1. Автозавершение. При вводе текста Excel проверяет, есть ли такой текст выше в столбце по одной первой букве. Если есть слова с начатой буквы, то текст появляется автоматически и если он устраивает Вас, то Enter (автозавершение).

2. Автозаполнение. В правом нижнем углу рамки текущей ячейки имеется черный квадратик – маркер заполнения .При наведении на него указатель мыши приобретает форму тонкого черного крестика . При перетаскивании левой кнопкой идет операция копирования, если копирование началось с одной заполненной ячейки. А если перетаскивание левой кнопкой началось с двух заполненных ячеек, то идет операция заполнения арифметической прогрессией с шагом, равным разности значений этих ячеек. При перетаскивании правой кнопкой (специальное перетаскивание) появляется контекстное меню: при выборе пункта «Копирование» будет произведено копирование содержимого ячейки; при выборе пункта «Прогрессия» открывается окно «Прогрессия», где можно выбрать тип прогрессии, величину шага и предельное значение; и другие.

3. С началом ввода в ячейку Excel автоматически переключается в режим ввода данных. В строке состояния появляется слово «Ввод». Вводимая информация будет отображаться в строке формул. В режиме ввода все средства редактирования кроме забоя предыдущего символа Backspace будут заблокированы. Конец ввода надо зафиксировать одной из вышеприведенных клавиш (Enter и др.). После фиксации ввода Excel выходит из режима ввода, в статусной строке появляется слово «Готово». Excel готов к выполнению следующей команды.

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

Операции редактирования можно разделить на следующие две группы:

1. Редактирование введенных в ячейку данных. Содержимое ячеек можно редактировать как непосредственно в ячейке (двойной щелчок на ячейке), так и в строке формул (щелчок в правой стороне строки формул), при этом в строке состояния появляется слово «Правка». В этом режиме становятся доступными все средства редактирования.

2. Редактирование на уровне ячеек, диапазонов, строк, столбцов. В основном это команды редактирования меню «Правка» и «Вставка».

Форматирование.

Все команды форматирования данных, строк, столбцов, листов и др. сосредоточены в меню «Формат».

Диаграммы в Excel.

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

Объекты диаграммы. Ось (Х – ось категорий, У – ось значений). Точка данных – один элемент данных, например, з/плата за январь. Ряд данных – совокупность точек данных (хорошо виден на графике – все точки ряда данных соединены одной линией). Легенда – значки, узоры, цвета, используемые для различения рядов данных. Маркер данных – представляет точку данных на диаграмме в виде прямоугольника, сектора, точки и т.д., вид маркера зависит от вида диаграммы; все маркеры одного ряда данных имеют одинаковую форму и цвет. Текст – все надписи (название диаграммы, значения и категории на осях) и подписи (тест, связанный с точками данных); для подписей можно воспользоваться пиктограммой «надпись» на панели рисования, либо создать плавающий текст: щелкнуть по одному из рядов данных - ввести тест (он появится в строке формул) - нажать «Enter».

Правила, используемые Excel по умолчанию при построении диаграмм.

1. Excel предполагает, что ряд данных для построения диаграммы идет вдоль длинной стороны выделенного диапазона ячеек.

2. Если выделен квадратный диапазон ячеек или он занимает по ширине больше ячеек, чем по высоте, то названия категорий будут расположены в верхней строке диапазона. Если больше ячеек по высоте, чем по ширине, то названия категорий идут вниз по левому столбцу. А если в ячейках, которые Excel будет использовать как названия категорий, содержатся числа (не текст и не даты), то Excel предполагает, что в этих ячейках содержится ряд данных, а названия категорий нумерует как 1, 2, 3, 4 и т.д.

3. Excel предполагает, что названия вдоль короткой стороны выделения должны использоваться как метки легенды для каждого ряда данных. Если ряд данных один, то Excel использует это название в качестве заголовка диаграммы. А если в ячейках, которые Excel намерен использовать как метки легенды, содержатся числа (не текст и не даты), то Excel предполагает, что в этих ячейках содержатся первые точки рядов данных, а каждому ряду данных присваивает имя: «Ряд1», «Ряд2» и т.д.

Макросы. Служит для автоматизации повторяющихся операций в Excel. Макрос состоит из последовательности внутренних команд Excel (макрокоманда). В Excel макрос создается с помощью команды «Сервис/Макрос/Начать запись». Эта команда позволяет создать макрос с помощью макрорекордера (способа записи программы). Параллельно с действиями пользователя макрорекордер протоколирует действия пользователя, автоматически переводя их на собственный макроязык. Таким способом можно создавать сравнительно несложные программы, которые выполняются без участия пользователя.

Пример: создать с помощью макрорекордера макрос, который строит диаграмму динамики заработной платы Иванова А.П. по месяцам. Для этого надо:

• перевести Excel в режим записи макроса с помощью команды «Сервис/Запись макроса/Начать запись»;

• в открывшемся диалоговом окне «Запись макроса» сделать нужные установки – задать имя макроса, например «Иванов», назначить сочетание клавиш для быстрого вызова макроса, например «Ctrl+p», из раскрывающегося списка «Сохранить в» выбрать пункт «Эта книга»;

• в строке состояния установятся индикаторы режимов «Готово» и «Запись»; Excel находится в режиме записи макроса, при этом любые действия пользователя автоматически запоминаются макрорекордером, поэтому надо быть внимательным при выполнении нужных действий;

• построить диаграмму;

• с помощью команды «Сервис/Макрос/Остановить запись» закончить запись макроса;

• удалить построенную диаграмму из рабочего листа;

• можно еще создать пиктограмму для вызова макроса, для этого надо задать команду «Вид/Панели инструментов/Настройка», в левом окне «Категории» выбрать пункт «Макросы», в правом окне выбрать пиктограмму и отбуксировать ее на стандартную панель инструментов, затем вызвать контекстное меню для этой пиктограммы, выбрать команду «назначить макрос» и имя макроса «Иванов»;

• запустить макрос поочередно тремя способами: с помощью команды «Сервис/Макрос/Макросы», с помощью сочетания клавиш «Ctrl+p», с помощью пиктограммы ; перед каждым вызовом макроса не забывать удалять ранее построенную диаграмму.

Задания к лабораторной работе №1.

1. Создать электронную таблицу по заданию преподавателя.

2. Построить на базе этой таблицы две диаграммы:

   1. гистограмму с одной осью Y;

   2. диаграмму с основной и со вспомогательной осями Y, два ряда данных при этом представить в виде графиков.

3. Построить смешанную диаграмму, в которой один ряд данных представлен в виде гистограммы, а второй ряд данных – в виде графика. Ряды данных задать в редакторе Word, сохранить файл с расширением .txt, затем импортировать этот файл из программы Excel. Данные выдает преподаватель.

4. Создать макрос (по заданию преподавателя).

Вопросы

1. Чем окно приложения Word отличается от окна приложения Excel? Какое расширение получают документы Excel при их сохранении на диск?

2. Что из себя представляет документ в Excel? Как можно настроить количество листов в книге по умолчанию? И каково максимальное количество листов в книге? Что, кроме рабочих листов, можно хранить в рабочей книге?

3. Виды отображения документа в Excel.

4. Как устанавливаются параметры страницы в Excel? Какие это параметры?

5. Можно ли склеить листы в книге? Если можно, то для чего это нужно?

6. Расскажите, как устроен рабочий лист в книге? Каково максимальное количество строк и максимальное количество столбцов в рабочем листе книги?

7. Какие два вида данных можно вводить в ячейки?

8. Что такое внутримашинное и экранное представление данных ячейки?

9. Перечислите и кратко опишите типы данных, которые могут находиться в электронных ячейках рабочего листа.

10. Что означают знаки (диез) в ячейках? Что означают ошибки #число!, #имя!, #знач!, #ссылка, #н/д?

11. Что такое диапазон ячеек и какие адресные операции используются в ссылках на диапазон ячеек?

12. Какие средства автоматизации ввода данных имеются в Excel?

13. На какие группы можно разбить операции редактирования данных?

14. Как осуществляется форматирование данных, строк, столбцов и рабочих листов книги?

15. Перечислите и кратко охарактеризуйте основные объекты диаграмм.

16. Что такое плавающий текст на диаграмме и как его можно ввести?

17. Сформулируйте основные правила, которые Excel использует при построении диаграмм.

18. Как можно создать диаграмму с двумя шкалами (смешанную и диаграмму со вспомогательной осью)?

19. Что такое макросы и зачем они нужны? Как они создаются в Excel?