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

Лабораторная работа №1 посвящена изучению следующих действий:

- работа с книгами и листами;

- ввод и редактирование данных в таблице;

- форматирование текстовых и числовых данных;

- работа с буфером;

- работа с формулами;

- оформление таблиц;

- построение диаграмм и графиков;

- работа с базой данных.

Теоретическая часть работы

1. Табличный процессор. Общее представление. Ввод данных в

таблицу

Электронная таблица (ЭТ) - программное средство для подготовки и обработки данных, представленных в табличном виде. По внешнему виду окно ЭТ напоминает окно редактора Word, но есть очевидные отличия.

Под двумя панелями инструментов располагается строка ввода (строка формул). Рабочее поле представляет собой пустую таблицу, которую предстоит заполнить. Строки таблицы пронумерованы цифрами, а столбцы названы буквами. Таким образом, у каждой клетки таблицы есть уникальный адрес, например А1.

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

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

Некоторые кнопки инструментария аналогичны кнопкам редактора Word. К их числу относятся: кнопки работы с таблицей как с документом (Создать, Открыть, Сохранить); кнопки установки вида и размера шрифта; кнопки начертания (Жирный, Курсив, подчеркивание); кнопки выравнивания информации в таблице (По левому краю, По центру, По правому краю) и некоторые другие.

Ввод данных в таблицу

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

Любая последовательность введенных в ячейку символов, которая не может быть интерпретирована как число, формула, время/дата, интерпретируется как текст. Введенный текст выравнивается в ячейке по левому краю. Ячейка может вмещать до 255 символов текста.

Даты и время рассматриваются как числа, поэтому с ними можно выполнять обычные арифметические действия. Но на экране даты и время отображаются в форматах, которые отличны от числовых. Дату можно вводить, используя в качестве разделителей символы: "/", "-" и "." .

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

1. Чтобы изменить ширину столбца, выполните следующее: поставьте курсор на границу между двумя метками столбца (он примет вид двунаправленной стрелки) и протащите мышью до нужного размера.

2. Чтобы очистить клетку необходимо щелкнуть по клетке, нажать Backspace, затем Ввод.

3. Чтобы очистить блок клеток, выделите блок клеток, затем выполните команду меню Правка Очистить Все.

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

5. Для вставки строки поместите курсор в строку, над которой хотите вставить пустую строку. Для вставки столбца поместите курсор в столбец, левее которого хотите вставить новый. Далее выполните команду меню Вставка Строки или Вставка Столбца.

6. Для удаления строки или удаления столбца поставьте курсор на строку или столбец, затем исполните команду меню ПравкаУдалитьСтроку или ПравкаУдалитьСтолбец.

3. Форматирование данных в ЭТ

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

По умолчанию информация выводится в формате Общий. Можно изменить формат представления информации в выделенных ячейках. Для этого выполните команду меню Формат Ячейки.

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

Запомните!!! Если вы хотите вводить числа, не связывая себя какими-либо форматами, то Excel будет вводить их в коде формата Общий.

4. Работа с буфером

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

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

- Чтобы выделить строку (столбец), необходимо щелкнуть по метке (цифре) строки, для столбца - щелкнуть по букве столбца.

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

- Если требуется выделить данные в разных частях таблицы, выделение выполняют при нажатой клавише Ctrl.

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

Команды работы с буфером Вырезать, Копировать, Вставить находятся в меню Правка.

Можно также воспользоваться аналогичными кнопками инструментария .

5. Работа с формулами

Чтобы ввести в клетку формулу, надо в строке ввода набрать знак =, а затем саму формулу. Формула может содержать:

- число;

- адрес клетки, блока данных;

- функцию;

- операцию вычисления: + , - , * , / ;

- скобки.

Чтобы ввести формулу:

1. Выделите клетку

2. Наберите =

3. Наберите формулу.

4. Нажмите Ввод или в строке ввода щелкните на кнопке ввода .

Exсel выводит текст формулы в строке формул, а результат вычислений помещает в ячейку. Чтобы увидеть формулы в самих клетках таблицы, надо командой меню Сервис Параметры Вид поставить флажок-галочку в окошке Формулы.

Для того чтобы быстро вставить клетку или блок в формулу необходимо:

1. набрать текст формулы до того места, где должна располагаться ссылка на клетку или интервал. Например, если хотим вычислить сумму ячеек с А1 по А4, сначала введем =сумм(

2. выделить мышью клетки с А1 по А4, в строке формул появится =сумм(А1:А4

3. набрать оставшуюся часть формулы, т.е. скобку, затем нажмите Ввод, в результате получится готовая формула =сумм(А1:А4) .

В формуле можно указать сразу несколько интервалов, эти интервалы следует перечислять, отделяя точкой с запятой, например =сумм(А1:А4;С1:С4). В таком случае сначала надо выделить блок А1:А4, затем ввести точку с запятой и выделить второй блок С1:С4.

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

1. Выделим клетку А4.

2. Исполним команду меню Правка Копировать.

3. Поместим курсор в клетку В4.

4. Исполним команду меню Правка Вставить. В клетку В4 вставится формула =сумм(В1:В4), ее можно увидеть в строке ввода, а в самой клетке В4 будет результат вычисления суммы.

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

Это следующие категории функций:

- Функции даты и времени;

- Математические;

- Экономические;

- Статистические;

- Логические;

- Специальные функции.

Просмотреть названия этих функций можно, воспользовавшись кнопкой Мастер функций панели инструментов .

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

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

Адреса ячеек можно выделить в исходной таблице, щелкнув на кнопку в правой части поля ввода . Нажмите OK.

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

- СУММ(Список) - статистическая функция определения суммы всех числовых значений в Списке. Пример: СУММ(В5:Е5).

- СРЗНАЧ(Список) - статистическая функция определения среднего арифметического значения всех перечисленных в списке величин.

- МАКС(Список) - статистическая функция, результатом которой является максимальное значение в указанном списке.

- ЕСЛИ(Условие; Истинно; Ложно) - логическая функция, проверяющая на истинность заданное логическое Условие. Если Условие выполняется, то результатом функции является значение аргумента Истинно (например, некоторое число или формула). Если Условие не выполняется, то результатом функции становится значение аргумента Ложно (например, другое число или другая формула). Пример: ЕСЛИ(В4<100; 100; 200).

6. Относительная и абсолютная адресация

В Excel применяется относительная и абсолютная адресация ячеек.

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

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

Для указания абсолютной адресации вводится символ $. Различают два типа абсолютной адресации: полная и частичная.

Полный абсолютный адрес указывается, если при копировании формулы адрес ячейки не должен меняться. Для этого символ $ ставится перед наименованием столбца и номером строки, например: $B$5; $D$12.

Частичная абсолютная адресация указывается, если при копировании формулы не меняется номер строки или наименование столбца. При этом символ $ в первом случае ставится перед номером строки, а во втором - перед наименованием столбца: B$5; D$12.

7. Оформление таблиц

1. Ввод заголовка таблицы. При вводе заголовка таблицы следует выделить ячейки заголовка и воспользоваться кнопкой панели инструментов Объединить и поместить в центре .

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

3. Расчертить таблицу. Расчертить таблицу проще всего командой меню ФорматАвтоформат, предварительно выделив всю таблицу. В списке форматов можно выбрать надлежащее оформление и щелкнуть на ОК.

8. Диаграммы и графики

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

1. Следует предварительно выделить необходимый фрагмент таблицы, на основе которого строится диаграмма, причем хорошо бы, чтоб левый столбец содержал названия строк, а первая строка - названия столбцов. Тогда мастер сразу включит их в диаграмму, расположив по осям или вставив в легенду. Если требуется выделить данные в разных частях таблицы, выделение выполняют при нажатой клавише Ctrl.

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

3. Уточните диапазон данных и где они размещены (в строках или столбцах).

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

5. Определите, как разместить диаграмму: на отдельном листе или вместе с таблицей.

6. Нажмите Готово.

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

Готовую диаграмму можно отредактировать. Для этого надо

1. выделить ее,

2. один раз щелкнув по ней (выделенная диаграмма отмечена черными квадратиками по углам).

3. теперь ее можно удалить (Delete), двигать мышью по листу в нужное место листа, уменьшать или растягивать за черные квадратики.

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

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

9. Работа с базой данных

Термин "База данных" можно применить к любой совокупности связанной информации, объединенной вместе по определенному признаку. Например, базой данных является телефонный справочник, содержащий фамилии, номера телефонов и адреса граждан. Данные располагаются в столбцах, такая база данных занимает прямоугольный блок электронной таблицы. Основным назначением баз данных является быстрый поиск содержащейся в них информации.

Строки таблицы, представляющей базу данных, называются записями. Одна запись содержит информацию об одном объекте. Запись состоит из полей. Поле - наименьшая неделимая единица информации. Названия полей соответствуют названиям столбцов базы данных.

При работе с базой данных представляют интерес три основные группы задач:

1. Построение базы данных.

2. Сортировка данных.

3. Поиск и обработка данных.

Построение базы данных

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

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

2. В каждой колонке следует использовать один и тот же тип данных, т.е. не смешивать в одной колонке числовые и текстовые данные.

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

4. В базе данных не должно быть одинаковых имен полей, желательно, чтобы имя поля состояло из одного слова длиной не более 15 символов.

Сортировка данных

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

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

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

1. Поместить курсор в любое место базы данных.

2. Исполнить команду Данные Сортировка.

3. В открывшемся диалоговом окне укажите поле, по которому будет выполняться первичная сортировка, выбрав его из списка, т.е. Отдел, и поле, по которому далее упорядочиваются записи, т.е. Оклад. Укажите порядок сортировки (по возрастанию или по убыванию) и щелкните OK. Таблица будет отсортирована.

Поиск данных

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

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

1. Поместите курсор в область базы данных.

2. Выберите команду ДанныеФильтрАвтофильтр.

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

Например, если нам надо найти работников проектного отдела, имеющих оклад больше 300, во-первых, в списке Отдел выберем проект, во-вторых, в списке Оклад выберем Условие... и в открывшемся диалоговом окне введем условие отбора:

В результате фильтрации в БД будут выделены строки, удовлетворяющие критериям:

Вернуть базе данных первоначальный вид можно командой ДанныеФильтр Отобразить все, а заодно и выключить Автофильтр.

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

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

2. В нижележащие строки заносятся условия отбора.

Например, если мы хотим найти сотрудников, родившихся до 1960 года и имеющих оклад меньший или равный 400, надо сформировать следующий блок условий отбора:

Далее исполним команду ДанныеФильтрРасширенный фильтр. Откроется диалоговое окно, в котором укажем

1. область базы данных (исходный диапазон),

2. область диапазона условий,

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

Возможно формирование более гибких условий отбора:

1. для выделения строк БД, содержащих текстовые данные, включающие некоторый фрагмент, требуется в качестве условия указать этот фрагмент и символ "*". Звездочка заменит собой любое число символов. Для замены одного символа служит "?".

2. для отбора записей, содержащих данные, попадающие в некоторый интервал, условия отбора можно записать в нескольких столбцах. Например, отберем записи, относящиеся к сотрудникам, фамилии которых начинаются с буквы "П", родившимся в период с 1960 по 1969 годы: .

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

Проверочные задания

После прочтения теоретической части, выполните следующие задания:

Задание №1

1. Создать таблицу, представленную на рисунке: установить ширину столбцов, оформить шапку таблицы, вести названия строк, столбцов, числовые данные таблицы. Таблица должна быть красиво оформлена.

2. Выполнить вычисление суммы по всем столбцам (строка Итого).

3. Вставить в таблицу дополнительные столбцы Сдали и Процент сдавших после столбца Сдавало.

4. Для одной клетки таблицы вычислить значение в столбце Сдали по формуле как разность между Сдавало и Сдали на “2”.

5. Выполнить копирование полученной формулы в другие ячейки столбца таблицы Сдали.

6. Определить для одной клетки таблицы Процент сдавших как отношение Сдали к Сдавало. Результат перевести в проценты.

7. Выполнить копирование полученной формулы в другие ячейки столбца Процент сдавших.

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

S = (K5*5+K4*4+K3*3+K2*2) / (K5+K4+K3+K2),

где K5, K4, K3, K2 - количество пятерок, четверок, троек и двоек соответственно (использовать адреса ячеек). Выполнить копирование этой формулы для прочих ячеек этого столбца.

Выполнить округление полученных значений до двух знаков после запятой.

9. Выполнить центральное выравнивание числовых данных таблицы.

10. Построить несколько диаграмм.

11. Расчертить таблицу. Выполнить предварительный просмотр.

12. Сохранить таблицу под именем “Моя таблица”.

Задание №2

Дана следующая таблица

1. Добавить столбец Площадь государства (усл.ед.). Вычислить значение для одной ячейки столбца по формуле:

Su = S1+S2,

где Su - площадь государства в условных единицах,

S1 - площадь государства в условных единицах до некоторого правителя,

S2 - площадь, добавленная правителем в усл.ед.

Выполнить копирование формулы для остальных ячеек столбца.

2. Добавить столбец Площадь государства (тыс.км.кв.). Вычислить значение для одной ячейки столбца по формуле:

Sg = Su*K,

где Su - площадь государства в условных единицах,

K - коэффициент перевода площади, K = 33,69.

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

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

3. Добавить столбец Площадь, добавленная правителем (тыс.км.кв.):

Sp=S2*K

где Sp - площадь, добавленная правителем в тыс. квадратных километрах,

S2 - площадь, добавленная правителем в усл.ед.,

K - коэффициент перевода площади, K = 33,69.

4. Построить диаграмму «Правители - присоединенные земли».

5. Построить график «Изменение площади государства Российского». По горизонтальной оси графика - годы, по вертикальной - площадь в тыс.кв.км.

Задание №3

Дана следующая таблица:

1. Введите названия столбцов и строк.

2. Введите данные первого года (1995): Объем продаж, Цена, Расходы.

3. Введите Прогнозные допущения: Рост объема продаж и Рост цен.

4. В ячейку B5 запишите формулу для вычисления дохода:

Доход(1995) = Объем продаж * Цена.

5. В ячейку B7 запишите формулу для вычисления прибыли:

Прибыль(1995) = Доход - Расходы.

6. Введите формулы в столбец второго года:

Объем продаж(1996) = Объем продаж(1995) * (1+%Роста объема продаж).

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

Цена(1996) = Цена(1995) *(1+%Роста цен).

Для ячейки Рост цен также использовать абсолютный адрес.

Для вычисления Доход(1996) содержимое ячейки Доход(1995) копируется.

Расходы(1996)=Расходы(1995)*(1+%Роста цен).

Пересчет остальных параметров из столбца B в столбец C выполняется аналогичным образом.

7. Столбцы D, E, F заполняются копированием формул, содержащихся в столбце С.

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

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

Задание №4. «Использование функций»

1. Заполните ведомость для расчета заработной платы. Процент надбавок к окладу определяется из расчета: 5%, если стаж работы меньше 3 лет; 15%, если стаж от 3-х лет и больше. Исходная таблица:

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

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

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

Область справочных данных для расчетов:

Вручную в таблицу заносятся:

- фамилия квартиросъемщика;

- размер жилой площади;

- количество проживающих;

- все справочные данные.

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

3. Определить фамилии абитуриентов, зачисленных после сдачи вступительных экзаменов. Абитуриент зачисляется в том случае, когда сумма набранных баллов не менее проходного балла или сумма баллов на единицу меньше проходного, но информатика сдана на пять.

Исходная таблица:

Справочные данные:

В результате исходная таблица преобразуется в следующую таблицу:

Задание №5. «Работа с базой данных»

Создать базу данных подержанных автомобилей по образцу, всего 10-12 записей.

С помощью автофильтра найдите:

1. недорогие автомобили, имеющие пробег меньше заданного;

2. все автомобили “Жигули”, выпущенные после заданного года.

С помощью расширенного фильтра найдите:

1. автомобили, имеющие дату выпуска, попадающую в заданный диапазон;

2. автомобили, имеющие цену меньше заданной или имеющие пробег меньше заданного.

Результаты поиска поместите в отдельной области таблицы.