ОСНОВНЫЕ ОПЕРАЦИИ ТАБЛИЧНОГО ПРОЦЕССОРА EXCEL

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

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

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

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

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

1. Щелкнуть по кнопке главного меню Пуск, которая находится в левом нижнем углу экрана;

2. Выбрать элемент Программы;

3. Выбрать элемент Microsoft Excel. Открытие любого файла с расширением .xls приводит к запуску табличного процессора и возможности работать с ним.

Завершение работы. Для завершения работы в Excel необходимо, находясь в открытом файле:

1. Выбрать команду меню Файл \ Выход или нажать на экранную кнопку Закрыть, расположенную в правом верхнем углу окна;

2. Если какие-либо данные остались несохраненными, то на экране появится диалоговое окно с предложением сохранения результатов;

3. Выбрать Да (если данные надо сохранить), Нет (если сохранение не требуется, но необходимо завершить работу), Отмена (если необходимо продолжить работу).

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

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

Сохранение файла. Для сохранения файла вновь созданной рабочей книги необходимо:

1. Выбрать команду Файл \ Сохранить как… или щелкнуть на кнопке Сохранить на панели инструментов Стандартная;

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

3. В поле Имя файла ввести имя создаваемого файла;

4. Щелкнуть на кнопке Сохранить (по умолчанию файлу будет присвоено расширение .xls).

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

В верхней части окна Excel расположена строка заголовка, в которой указаны имя приложения (в данном случае Microsoft Excel) и имя книги (Книга1). В левом конце строки заголовка находится кнопка вызова системного меню, а в правом – кнопки управления окном. Системное меню содержит команды Восстановить, Переместить, Размер, Свернуть, Развернуть, Закрыть, предназначенные для выполнения различных операций с окном. Кнопки управления окном позволяют быстро активизировать некоторые команды системного меню.

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

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

Существуют кнопки, характерные только для Excel, например, Мастер функций, Мастер диаграмм, Автосуммирование, Сортировка.

Ниже панелей инструментов расположена строка, содержащая строку формул.

Выбор алфавита и шрифтов. Для выбора требуемого алфавита (латиницы или кириллицы) для ввода символов можно воспользоваться пиктограммой в правой нижней части экрана или нажать две из трех клавиш Alt, Shift, Ctrl (в зависимости от настроек). Шрифт выбирается в окне Шрифт. Обычно используется шрифт Arial или Times New Roman. Греческие символы можно ввести с помощью шрифта Simbol.

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

Ввод формул. Ввод данных и формул выполняются в ячейках на листах книги. Лист (таблица) разделен на строки и столбцы. Заголовки столбцовобозначаются латинскими буквами (A,B,C, …); заголовки строк – цифрами (1,2,3, …).

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

Адресация ячеек. В Excel существует два типа адресации ячеек. Относительный адрес показывает, где расположена та или иная ячейка относительно заданной ячейки. Однако при копировании формул, содержащих относительные адреса ячеек, эти адреса будут автоматически изменятся. Например, в ячейку F4 ввели формулу =СУММ(B4:B10), а затем скопировали с помощью автозаполнения эту формулу в ячейку F5. В ячейке F5 будут суммироваться содержимое ячеек B5:B11. Таким образом, относительный адрес зависит от того, в какой ячейке располагается использующая этот адрес формула.

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

Возможны случаи, когда строку не нужно менять, а столбец должен быть относительным, и наоборот. Например, ссылка D$2 в формуле, расположенной в ячейке C2, означает, что нужно использовать ячейку всегда из второй строки, но отстоящую при этом от текущей ячейки с формулой на одну ячейку вправо.

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

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

Стандартные функции. Наиболее часто используемые функции занесены в список, которым управляет Мастер функций. Он вызывается нажатием соответствующей кнопки на панели инструментов, или комбинацией клавиш Shift+F3. Список групп функций задается в Категории функций. После выбора определенной группы выбор самих функций производится из списка в окне Функции. При необходимости можно пользоваться встроенной системой поиска требуемых функций. Применение Мастера функций намного удобней и надежней, чем ввод с клавиатуры формата функций. При самостоятельном вводе следует руководствоваться следующим правилом: каждая стандартная функция обязательно должна содержать круглые скобки для аргументов, даже если сами аргументы отсутствуют, например, в функциях ПИ(), СЕГОДНЯ().

Операции с матрицами. Для выполнения действий с матрицами существуют, в частности, следующие функции Мастера функций:

МОПРЕД (массив) – вычисление определителя матрицы,

МУМНОЖ (массив) – умножение матриц,

МОБР (массив) – нахождение обратной матрицы,

ТРАНСП (массив) – транспонирование матрицы.

При выполнении этих операций вначале нужно выделить массив ячеек для записи результатов. Затем следует обратиться к Мастеру функций, выбрать необходимую функцию и указать в открывшемся диалоговом окне исходные массивы. Массив ячеек, в котором записана матрица, можно указать с клавиатуры (адреса двух крайних по диагонали ячеек через двоеточие). Однако лучше это сделать с помощью мыши, выделив соответствующий блок ячеек, после чего нажать OK или клавишу Enter. Для получения окончательного результата в виде массива надо установить курсор в строку формул и нажать комбинацию клавиш Ctrl+Shift+Enter.

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

В о п р о с ы

1. Как создать файл Excel, как его переименовать, как стереть?

2. Как открыть существующий файл Excel, как его закрыть?

3. Как производится форматирование ячеек?

4. Укажите основные правила ввода формул.

5. Как включить или отключить режим показа формул в ячейках?

6. Какой адрес ячейки называется относительным? Какой адрес ячейки назы-вается абсолютным? В чем их синтаксические различия?

7. Для каких целей используется Мастер функций?

8. Что называется массивом ячеек? Приведите примеры встроенных функций Excel, работающих с массивами.

ОРГАНИЗАЦИЯ РАБОТЫ С ТАБЛИЦАМИ EXCEL

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

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

• номер шага;

• значения аргументов (переменных);

• значения функций;

• результаты анализа вычислений (условия остановки).

При анализе условий остановки пользователь предусматривает выдачу сообщения ”Продолжить” или ”Стоп”. Возможен учет двух и более условий остановки, и тогда для каждого из них нужно предусмотреть отдельный столбец (строку). Расчет ведется до тех пор, пока не выполнятся все условия остановки. В этом случае во всех столбцах (строках) появится сообщение “Стоп”. В столбцах анализа могут помещаться и другие сообщения.

Погрешности вычислений. Во всех вычислительных задачах необходимо оценивать погрешность вычислений. Если на очередном шаге вычислительного процесса найден конечный интервал неопределенности на котором существует точное решение, и приближенное решение выбирается в любой точке этого отрезка, тоабсолютная погрешность этого решения оценивается по формуле: b-a. Относительная погрешность оценивается по формуле . Для простоты и без существенной потери точности вычислений в знаменателе выбирают значение, соответствующее любой внутренней точке последнего отрезка неопределенности, например, его середины или одной из его границ. Приd=a относительная погрешность определяется по формуле .

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

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

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

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

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

Оформление отчета. Отчет по выполненной работе оформляется в письменном виде. Он включает:

1. Фамилию и инициалы студента, наименование факультета, курс и номер группы, наименование темы.

2. Индивидуальные исходные данные (по номеру варианта).

3. Полную формулировку задания для выполнения.

4. Порядок выполнения последовательно по пунктам задания.

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

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

Отчет по самостоятельной работе предъявляется преподавателю для проверки в распечатанном виде или на внешнем носителе.