ВВЕДЕНИЕ 1 страница

Рецензент С.Д. Чижиумов

Комсомольск-на-Амуре, 2003

ОСНОВЫ РАБОТЫ В EXCEL

ИНФОРМАТИКА.

Методические указания к компьютерному практикуму

УДК 681.3

Информатика. Основы работы в Excel: Методические указания к компьютерному практикуму/ Сост.: А.Д. Бурменский,- Комсомольск-на-Амуре: Комсомольский-на-Амуре гос. техн. ун-т, 2003. - 46 с.

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

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

Печатается по постановлению редакционно-издательского совета Комсомольского-на-Амуре государственного технического университета.

Согласовано с отделом стандартизации.

Редактор

_________________________________________________________

Подписано в печать

Формат 60*84 1/16.Бум.тип. N3. Печать офсетная. усл.печ.л.

Уч.-изд.л. . Тираж . Заказ . Цена

ГОУВПО "Комсомольский-на-Амуре государственный технический университет", 681013, Комсомольск-на-Амуре, пр. Ленина, 27.

Полиграфическая лаборатория технического университета,

681013, Комсомольск-на-Амуре, пр. Ленина, 27.

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

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

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

• проведения однотипных расчетов над большими наборами данных;

• автоматизации итоговых вычислений;

• решения задач путем подбора значений параметров, табулирования формул;

• обработки результатов экспериментов;

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

• подготовки табличных документов;

• построения диаграмм и графиков по имеющимся данным.

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

ЗАНЯТИЕ 1

ПЕРВОЕ ЗНАКОМСТВО С EXCEL

Цели работы:

- познакомиться с основными понятиями электронных таблиц;

- освоить основные приемы заполнения и редактирования таблиц;

- научиться сохранять и загружать таблицы.

Задание 1. Запустите Excel.

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

Задание 2. Разверните окно Excel на весь экран и внимательно рассмотрите его.

Экран компьютера при работе с программой будет иметь вид, представленный на рисунке 1.1 (может меняться в зависимости от настройки программы).

Рисунок 1.1

Верхняя строка - шапка окна с кнопками управления.

Вторая строка - меню Excel.

Третья и четвертая строка - панели инструментов Стандартная и Форматирова­ние.

· Прочитайте назначение кнопок панели инструментов Стандартная, медленно перемещая курсор мыши по кнопкам.

Пятая строка - строка ввода и редактирования или строка формул.

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

Нижняя строка - строка состояния.

В крайней левой позиции нижней строки отображается индикатор режима работы Excel. Например, когда Excel ожидает ввода данных, то находится в режиме «готов» и индикатор режима показывает «Готово».

Задание 3. Освойте работу с меню Excel.

С меню Excel удобно работать с помощью мыши. Выбрав необходимый пункт, нужно подвести к нему курсор и щелкнуть левой кнопкой мыши. Аналогично выбира­ются необходимые команды подменю и раскрываются вкладки, а также устанавлива­ются флажки.

· В меню Сервис выберите команду Параметры и раскройте вкладку Правка.

· Проверьте, установлен ли флажок Перетаскивание ячеек. Если нет, то уста­новите его и нажмите кнопку ОК. (Далее в тексте подобные действия по работе с меню будут описываться в краткой форме: Сервис, Параметры, Правка, Перетаскивание ячеек, ОК.)

Щелчок мыши вне меню приводит к выходу из него и закрытию подменю.

Задание 4. Познакомьтесь с основными понятиями электронных таблиц.

Строки, столбцы, ячейки

Рабочее поле электронной таблицы состоит из строк и столбцов. Каждое пере­сечение строки и столбца образует ячейку, в которую можно вводить данные (текст, число или формулу).

Номер строки - определяет ряд в электронной таблице. Он обозначен на левой границе рабочего поля.

Буква столбца - определяет колонку в электронной таблице. Буквы находятся на верхней границе рабочего поля. Колонки нумеруются в следующем порядке: A-Z, затем AA-AZ, затем BA-BZ и т.д.

Ячейка - первичный элемент таблицы, содержащий данные. Каждая ячейка име­ет уникальный адрес, состоящий из буквы столбца и номера строки. Например, адрес В3 определяет ячейку на пересечении столбца В и строки номер 3.

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

Текущая ячейка помечена указателем. По умолчанию ввод данных и некото­рые другие действия относятся к текущей ячейке.

· Сделайте текущей ячейку D4 при помощи мыши.

· Вернитесь в ячейку А2 при помощи клавиш перемещения курсора.

Блок (область)

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

Адрес блока состоит из координат противоположных углов, разделенных двоеточием. Например, В13:С19, A12:D27 или D:F.

Блок можно задать при выполнении различных команд или вводе формул посред­ством указания координат или выделения на экране.

Рабочий лист, книга

Электронная таблица в Excel имеет трехмерную структуру. Она состоит из лис­тов, как книга (при начальном запуске таких листов 3), но весьма больших размеров. На одном листе может разместиться десятки обычных страниц. На экране виден только один лист - верхний. Нижняя часть листа содержит ярлычки других листов. Щелкая кнопкой мыши на ярлычках, можно перейти к другому листу.

· Сделайте текущим лист 3.

· Вернитесь к листу 1.

Выделение столбцов, строк, блоков, таблиц

Для выделения с помощью мыши:

столбца - щелкнуть кнопкой мыши на букве - имени столбца;

нескольких столбцов - не отпуская кнопку после щелчка, протянуть мышь;

строки - щелкнуть кнопкой мыши на числе - номере строки;

несколько строк - не отпуская кнопку после щелчка, протянуть мышь;

блока - щелкнуть кнопкой мыши на начальной ячейке блока и, не отпуская кноп­ку, протянуть мышь на последнюю ячейку;

рабочего листа - щелкнуть кнопкой мыши на пересечении имен столбцов и номе­ров строк (левый верхний угол таблицы).

Для выделения блока с помощью клавиатуры необходимо, удерживая нажатой клавишу Shift, нажимать на соответствующие клавиши перемещения курсора, или, на­жав клавишу F8, войти в режим выделения и выделить блок при помощи клавиш пере­мещения курсора. Esc - выход из режима выделения.

Для выделения нескольких несмежных блоков необходимо:

· выделить первую ячейку или блок смежных ячеек;

· нажать и удерживать нажатой клавишу Ctrl;

· выделить следующую ячейку или блок и т.д.

· отпустить клавишу Ctrl.

Для отмены выделения достаточно щелкнуть кнопкой мыши по любому невыде­ленному участку рабочего листа. Новое выделение снимает предыдущее.

· Выделите строку 3.

· Отмените выделение.

· Выделите столбец D.

· Выделите блок А2:Е13 при помощи мыши.

· Выделите столбцы А, В, С, D. .

· Отмените выделение.

· Выделите блок C4:F10 при помощи клавиатуры.

· Выделите рабочий лист, затем отмените выделение.

· Выделите одновременно следующие блоки: F5:G10, H15:I11, C18.-F20, Н20.

Задание 5. Познакомьтесь с основными приемами заполнения таблиц.

Содержимое ячеек

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

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

Excel определяет, являются вводимые данные текстом, числом или формулой по первому символу. Если первый символ - буква или знак «'», то Excel считает, что вво­дится текст. Если первый символ цифра или знак «=», то Excel считает, что вводится число или формула.

Вводимые данные отображаются в ячейке и строке формул и помещаются в ячей­ку только при нажатии Enter или клавиши перемещения курсора.

Ввод текста

Текст - это набор любых символов. Если текст начинается с числа, то начать ввод необходимо с символа «'».

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

· В ячейку А1 занесите текст «Москва - древний город»

Обратите внимание, что текст прижат к левому краю.

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

Ввод чисел

Числа в ячейку можно вводить со знаками =, +, - или без них. Если ширина вве­денного числа больше, чем ширина ячейки на экране, то Excel отображает его в экспо­ненциальной форме или вместо числа ставит #### (при этом число в памяти машины будет сохранено полностью).

Экспоненциальная форма используется для представления очень маленьких и очень больших чисел. Число 501000000 будет записано как 5,01Е+08, что обозначает 5,01 *10⁸. Число 0,000000005 будет представлено как 5Е-9, что означает 5*10^-9.

Для ввода дробных чисел используется десятичная запятая или точка (в зависимо­сти от настройки).

· В ячейку В1 занесите число 1147 (год основания Москвы).

· В ячейку С1 занесите число - текущий год.

Ввод формул

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

Формула должна начинаться со знака «=». Она может включать до 240 символов и не должна содержать пробелов.

Для ввода в ячейку формулы C1+F5 ее надо записать как =C1+F5. Это означает, что к содержимому ячейки С1 будет прибавлено содержимое ячейки F5. Результат бу­дет получен в той ячейке, в которую занесена формула.

· В ячейку D1 занесите формулу =С1-В1.

Что за число получилось? Это возраст Москвы.

Задание 6. Познакомьтесь с основными приемами редактирования таблиц.

Изменение ширины столбцов и высоты строк

Эти действия можно выполнить с помощью мыши или через меню. При использовании мыши ее указатель необходимо поместить на разделительную линию между именами столбцов или именами строк. Указатель примет вид двойной черной стрелки. Затем необходимо нажать левую кнопку мыши и растянуть (сжать) столбец или строку.

При использовании меню необходимо выделить строки или столбцы и выполнить команды Формат, Строка, Высота... или Формат, Столбец, Ширина...

· При помощи мыши измените ширину столбца А так, чтобы текст был виден полностью, а ширину В, С, D сделайте минимальной.

· При помощи меню измените высоту строки номер 1 и сделайте ее равной 30.

· Сделайте высоту строки номер 1 первоначальной (12,75).

Редактирование содержимого ячейки

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

Если во время ввода данных в ячейку допущена ошибка, то она может быть ис­правлена стиранием неверных символов при помощи клавиш Backspace и набором сим­волов заново. Клавишей Esc можно отменить ввод данных в ячейку и записать их зано­во.

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

· Определите возраст Москвы в 2000 году.

Замените текущий год в ячейке С1 на 2000. В ячейке D1 появился новый возраст столицы (в 2000 году).

Внимание! При вводе новых данных пересчет в таблице произошел автоматиче­ски. Это важнейшее свойство электронной таблицы.

· Отредактируйте текст в ячейке Al. Новый текст «Москва - столица России».

Операции со строками, столбцами, блоками

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

Перемещение

Перемещая данные, необходимо указать, ЧТО перемещается и КУДА. Для выполнения перемещения требуется выделить ячейку или блок (ЧТО пере­мещается). Затем переместить указатель мыши на рамку блока или ячейки (он должен принять форму белой стрелки). Далее следует перенести блок или ячейку (в место, КУ­ДА нужно переместить данные).

· Выделите блок Al :D1 и переместите его на строку ниже.

· Верните блок на прежнее место.

Копирование

При копировании оригинал (ЧТО) остается на прежнем месте, а в другом месте (КУДА) появляется копия.

Копирование выполняется аналогично перемещению, но при нажатой клавише Ctrl.

· Скопируйте блок A1:D1 в строки 3, 5, 7.

Заполнение

При заполнении исходная ячейка (ЧТО) или блок повторяются несколько раз за одно действие. Заполнение возможно вправо или вниз.

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

· Выделите строку номер 7 и заполните выделенными данными строки по 15 включительно.

· Скопируйте столбец С в столбцы Е, F, G.

Удаление, очистка

Если надо очистить только данные (числа, текст, формулы), то достаточно нажать Delete на выделенной ячейке или блоке.

· Выделите блок A10:G15 и очистите его.

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

· Очистите содержимое ячейки G9, используя команды меню.

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

· Удалите столбец Е.

Обратите внимание на смещение столбцов.

Для удаления данных из таблицы с сохранением пустого места (удаление данных без удаления ячеек) необходимо воспользоваться командами Правка, Очистить.

· Удалите столбец Е с сохранением пустого места.

Для удаления всей рабочей таблицы используется команда Файл, Закрыть; на запрос ответьте «Отмена».

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

Рисунок 1.2

Задание 7. Научитесь использовать функцию автозаполнения.

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

· В ячейку G10занесите текст «январь».

· В ячейку Н10 занесите текст «февраль».

· Выделите блок G10:H10.

· Укажите на маленький квадратик в правом нижнем углу ячейки Н10 (экранный курсор превращается в маркер заполнения).

· Нажмите левую кнопку мыши и, не отпуская ее, двигайте мышь вправо, пока рамка не охватит блок G10:М10

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

· Введите в ячейки G11 :М11 дни недели, начиная с понедельника

· Заполните ячейки G12:M12 годами с 1990 по 1996. Excel позволяет вводить некоторые нетиповые последовательности, если в них удается выделить некоторую закономерность,

· Используя рисунок 1.3, внесите следующие данные в таблицу: в ячейкиG16:M16–века; в ячейку G16 - заголовок «Население Москвы (в тыс. чел.)»; в ячейки G17:M17-данные о населении Москвы по векам.

·

Рисунок 1.3

Задание 8. Освойте действия с таблицей в целом: Сохранить, Закрыть, Создать, Открыть.

Для действий с рабочей книгой в целом используются команды из меню Файл:

Сохранить - сохраняет рабочую книгу на диске для последующего использова­ния;

Сохранить как... - аналогична Сохранить, но при этом позволяет поменять имя файла или записать рабочую книгу на другой диск;

Закрыть - убирает документ с экрана;

Создать - создает новую рабочую книгу (пустую или на основе указанного шаб­лона);

Открыть - возвращает рабочую книгу с диска на экран.

Действия Создать, Открыть, Сохранить закреплены за первыми тремя кнопка­ми панели инструментов Стандартная.

· Сохраните таблицу на рабочем диске в личной папке под именем work1.xls.

· Уберите документ с экрана.

· Вернитесь к своему документу work1.xls.

· Закройте файл.

Задание 9. Завершите работу с Excel.

Для выхода из Excel можно воспользоваться одним из следующих способов:

· командой меню Файл, Выход;

· из системного меню - команда Закрыть;

· с клавиатуры - Alt-F4.

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

Задание 10. Подведите итоги.

В результате выполнения работы 1 вы должны познакомиться с основными поня­тиями электронных таблиц и приобрести первые навыки работы с Excel.

Проверьте:

· знаете ли вы, что такое: элементы окна Excel; строка, столбец, ячейка, страни­ца, книга;

· умеете ли вы: работать с меню; вводить текст, числа, формулы; редактировать данные; изменять размер строк и столбцов; перемещать, копировать, заполнять, уда­лять; сохранять таблицу, закрывать, удалять,

Если нет, то еще раз внимательно перечитайте соответствующие разделы работы.

ЗАНЯТИЕ 2

Часть 1 РЕШЕНИЕ ЗАДАЧИ ТАБУЛИРОВАНИЯ ФУНКЦИИ-

Цели работы:

· закрепить навыки заполнения и редактирования таблиц;

· познакомиться со способами адресации;

· освоить некоторые приемы редактирования таблиц.

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

Постановка задачи: вычислить значения функции у = k(x²-1)/(х²+1) для всех х на интервале [-2,2] с шагом 0,2 при k = 10.

Решение должно быть получено в виде таблицы:

Задание 1. Прежде чем перейти к выполнению задачи, познакомьтесь со спосо­бами адресации в Excel.

Абсолютная, относительная и смешанная адресации ячеек и блоков

При обращении к ячейке можно использовать описанные ранее способы: В3, A1:G9 и т.д. Такая адресация называется относительной. При её использовании в фор­мулах Excel запоминает расположение относительно текущей ячейки. Так, например, когда вы вводите формулу =В1+В2 в ячейку В4, то Excel интерпретирует формулу как «прибавить содержимое ячейки, расположенной тремя рядами выше, к содержимому ячейки двумя рядами выше».

Если вы скопировали формулу =В1+В2 из ячейки В4 в С4, то Excel также интер­претирует формулу как «прибавить содержимое ячейки, расположенной тремя рядами выше, к содержимому ячейки двумя рядами выше». Таким образом, формула в ячейке С4 примет вид =С1+С2.

Если при копировании формул вы пожелаете сохранить ссылку на конкретную ячейку или область, то вам необходимо воспользоваться абсолютной адресацией. Для её задания необходимо перед именем столбца и перед номером строки ввести символ $. Например, $В$4 или $С$2:$Р$48 и т.д. (используйте клавишу F4 для автоматизации проставления знака $ в именах ячеек для чего щелкните мышью на имени ячейки в строке формул, затем нажмите F4. Нажмите F4 еще раз, потом еще - видите что полу­чается?).

Смешанная адресация. Символ $ ставится только там, где он необходим. На­пример, В $4 или $С2. Тогда при копировании один параметр адреса изменяется, а дру­гой - нет.

Задание 2. Заполните основную и вспомогательную таблицы.

· Заполните шапку основной таблицы начиная с ячейки А1:

- в ячейку А1 занесите N;

- в ячейку В1 занесите X;

- в ячейку С1 занесите К и т.д.;

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

· Заполните вспомогательную таблицу начальными исходными данными начи­ная с ячейки H1:

где х0 - начальное значение х, step - шаг изменения х, k - коэффициент (константа).

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

· Используя функцию автозаполнения, заполните столбец А числами от 1 до 21, начиная с ячейки А2 и заканчивая ячейкой А22.

· Заполните столбец В значениями х:

- в ячейку В2 занесите =$Н$2;

Это означает, что в ячейку В2 заносится значение из ячейки Н2 (начальное значе­ние х), знак $ указывает на абсолютную адресацию.

- в ячейку В3 занесите =В2+$I$2;

Это означает, что начальное значение х будет увеличено на величину шага, кото­рая берется из ячейки I2.

- скопируйте формулу из ячейки В3 в ячейки В4:В22. Столбец заполнится значениями х от -2 до 2 с шагом 0,2.

· Заполните столбец С значениями коэффициента k:

- в ячейку С2 занесите =$J$2;

- в ячейку С3 занесите =С2;

Посмотрите на введенные формулы. Почему они так записаны?

- скопируйте формулу из ячейки С3 в ячейки С4:С22. Весь столбец заполнился значением 10.

· Заполните столбец D значениями функции yl=x^2-1:

- в ячейку D2 занесите =B2^2-1;

- скопируйте формулу из ячейки D2 в ячейки D3:D22. Столбец заполнился как положительными, так и отрицательными значениями функции yl. Начальное и конечное значения равны 3.

· Аналогичным образом заполните столбец Е значениями функции y2=x^2+l.

Проверьте! Все значения положительные; начальное и конечное значения равны 5.

· Заполните столбец F значениями функции у = k*(yl/y2):

- в ячейку F2 занесите =C2*(D2/E2);

- скопируйте формулу из F2 в ячейки F2:F22.

Проверьте! Значения функции как положительные, так и отрицательные; началь­ное и конечное значения равны 6.

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

· Измените во вспомогательной таблице значение х: в ячейку Н2 занесите -5.

· Измените значение шага: в ячейку I2 занесите 2.

· Измените значение коэффициента: в ячейку J2 занесите 1.

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

· Прежде чем продолжить, работу, верните прежние начальные значения во вспомогательной таблице: х0 = -2 step = 0,2 k = 10.

Задание 4. Оформите основную и вспомогательную таблицы.

· Вставьте две пустые строки сверху для оформления заголовков:

- установите курсор на строку номер 1;

- выполните команды меню Вставка, Строки (2 раза).

· Введите заголовки:

- в ячейку А1 «Таблицы»;

- в ячейку А2 «основная»;

- в ячейку Н2 «вспомогательная».

· Объедините ячейки Al:J 1 и поместите заголовок «Таблицы» по центру: