БЕЛОРУССКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ

Механико-математический факультет

Кафедра численных методов и программирования

МЕТОДЫ ПРОГРАММИРОВАНИЯ

ИСПОЛЬЗОВАНИЕ EXCEL

ДЛЯ ВЫПОЛНЕНИЯ

МАТЕМАТИЧЕСКИХ РАСЧЁТОВ

для студентов специальности G.31.03.00 – «Математика»

Минск 2002 УДК ББК Р Рецензент доцент кафедры информационного и программного математического обеспечения автоматизированных производств Блинов И.Н.

Авторы-составители: Г.А. Расолько, доцент, канд. физ.-мат. наук Е.В. Кремень, ассистент Ю.А. Кремень, доцент, канд. физ.-мат. наук Утверждено на совете факультета 26 марта 2002 г., протокол № Методы программирования. Использование Excel для выполнения математических расчётов / Г.А. Расолько, Е.В. Кремень, Ю.А. Кремень. – Мн.: БГУ, 2002. – 70 с.

ISBN В учебном пособии проиллюстрированы основные возможности табличного процессора: работа с формулами и функциями, возможность создания функций пользователя в Visual Basic for Applications, графическое представление данных на диаграммах и графиках, работа с однотабличными базами данных и их анализ при помощи сводных таблиц, внедрение и связь таблиц Excel в Word, использование надстроек на примере статистического пакета Анализ данных.

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

УДК ББК Р ISBN © Коллектив авторов,

ВВЕДЕНИЕ

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

Стандартно принято, что Excel можно использовать для упрощения различных математических операций над табличными данными, а также для построения диаграмм различного типа на основе данных таблицы, создавать базы данных и работать с ними, ставить численный эксперимент по сценарию «что – если» и др. Однако, благодаря мощным математическим и инженерным функциям с помощью Excel можно решать множество задач в области численного анализа.

В «Методическом пособии» описаны наиболее важные элементы программы Excel 2000, основные технические приемы, необходимые при работе с электронными таблицами с ориентацией на решение большого спектра задач естествознания.

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

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

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

нами будет написано «Выберите меню Формат – Лист | Строка | Столбец – Скрыть или Показать».

ТЕМА 1. ЭЛЕКТРОННЫЕ ТАБЛИЦЫ:

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

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

Активная Строка Панели ячейка меню инструментов Поле Заголовок Заголовимени окна Строка формул ки строк и столб- Строка Область Вертикальная и состояния индикаци Кнопки прокрут- горизонтальная ки ярлычков Ярлычки линейки пролистов листов крутки Задание. Ознакомьтесь с окном Excel. Разыщите: строку заголовка;

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

Основные понятия электронных таблиц Рабочее поле электронной таблицы Excel – это видимая часть текущего листа текущей книги. Оно состоит из строк и столбцов. Каждое пересечение строки и столбца образует ячейку, в которую можно вводить данные (текст, число или формулы). Каждая ячейка имеет уникальный адрес, состоящий из буквы столбца и номера строки, например А1 или С6.

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

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

Внимание! Не бойтесь ошибок. Почти все действия могут быть отменены, например, кнопкой Отменить панели инструментов Стандартная или командой меню Правка – Отменить.

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

Электронная книга в Excel имеет трёхмерную структуру. Она состоит из строк, столбцов и листов. На экране обычно виден только один лист – текущий (верхний), но снизу имеются ярлычки других листов. Щёлкая левой кнопкой мыши на ярлычках листов, можно перейти к другому листу. Если нужный ярлычок не виден, то линейной прокрутки (слева внизу) можно найти его. По умолчанию листы называются стандартно «Лист1», «Лист2» и т. д. Их можно переименовывать, переставлять местами, удалять, добавлять новые или выделить группу листов, например, для последующего удаления. Эти действия можно сделать при помощи контекстного (контекстно-зависимого) меню, которое появляется, если на объекте щёлкнуть правой кнопкой мыши.

Задание. Оставьте в книге четыре листа и переименуйте их соответственно так: Алгебра, Геометрия, Анализ, Программирование.

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

• рабочего листа – щёлкнуть на пересечении имён строк и столбцов;

• столбца – щёлкнуть на букве-имени столбца;

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

• строки – щёлкнуть на числе-номере строки;

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

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

• нескольких несмежных блоков – выделить один блок, нажать клавишу Ctrl и, не отпуская её, выделить следующие блоки.

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

Выделенные строки (столбцы) можно спрятать (показать). Делается это либо через контекстное меню, либо через меню Формат – Лист | Строка | Столбец – Скрыть или Отобразить.

Сохранение книги: меню Файл – Сохранить как и указываем, куда и с каким именем. Быстрое сохранение: кнопка Сохранить на панели инструментов. Excel сохраняет файл с расширением xls.

Можно установить режим автосохранения: меню Сервис – Надстройки и устанавливаем флажок Автосохранение. Далее снова меню Сервис – Автосохранение и устанавливаем нужные флажки.

Закрытие рабочих книг: меню Файл – Закрыть.

Открытие существующих документов: меню Файл – Открыть или кнопка Открыть панели инструментов.

Создание нового документа: меню Файл – Создать и выбираем, например, Книга.

Быстрое создание: кнопка Создать панели инструментов.

Поиск документов: меню Файл – Открыть и идём на Найти, задав необходимые критерии поиска.

Индивидуальная настройка программы Задание. Познакомьтесь с параметрами, которые влияют на работу программы и, по необходимости, измените их. Для этого войдите в меню Сервис – Параметры.

Изучите все вкладки: Общие; Вид; Вычисления; Правка; Переход; Списки; Диаграмма; Цвет.

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

Щёлкните правой кнопкой мыши на пустом месте в области панелей инструментов, откроется контекстное меню. Изучите его возможности.

Работу с окнами в Windows мы знаем, а в Excel функции работы с окнами собраны в меню Окно (главном или контекстном).

Задание. Познакомьтесь с:

• меню Формат – Лист – Скрыть;

• меню Формат – Лист – Отобразить;

• меню Окно;

• меню Окно – Новое • меню Окно – Скрыть (скрыть всю рабочую книгу) Отобразить;

• меню Окно – Разделить (рабочая область листа разделяется двумя линиями на четыре подокна со своими полосами прокрутки) Снять разделение;

• меню Окно – Закрепить области Снять закрепление областей;

• меню Окно – Расположить – Рядом Сверху вниз Слева направо Каскадом.

Каждая ячейка в Excel может содержать данные одного из трех типов: текст, число, формула (начинается со знака «=»).

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

Ячейка числового типа может содержать числа в диапазоне –1,67*10308…1,67*10308 (количество значащих цифр – не более 15).

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

Excel сам определяет тип ячейки по контексту введенных в нее данных:

• Если введенные в ячейку данные есть литерал [±][целая часть][,][дробная часть][Е[±]степень], то ячейка будет числового типа.

• Введенные данные начинаются с символа «=» – ячейка типа формула.

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

По умолчанию задан такой формат ввода данных, что недопустим символ «пробел» в формуле.

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

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

В виде формулы может быть записано выражение, которое начинается со знака «=» и состоит из чисел и ссылок на ячейки, содержащие данные, объединённых знаками операций или функций и не содержит пробелов. После ввода формулы, она подсчитывается и результат записывается на её место. Результат вычислений по формуле может быть числом или текстом. Если ячейку, содержащую формулу, сделать активной, то в строке формул появится сама формула, а в ячейке – результат подсчёта по формуле. Хотя это правило можно изменить.

Операции в формулах:

•арифметические (+ – * / % ^);

•адресации (; : пробел, задающий пересечение двух областей);

•текстовый оператор & конъюнкции.

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

(15+30)*2+26/4!+11/5!

Задание. Наберите в некоторой ячейке число 1234567890 и понаблюдайте за изменением его представления при изменении ширины столбца.

Функции предназначены для упрощения расчётов и имеют следующую форму: имя_функции (аргументы). Аргументы функции указываются после её названия в круглых скобках и отделяются символом «;». Так, к примеру, для вычисления суммы ряда данных используют функцию СУММ(аргум1; аргум2; …). Аргументы можно ввести после открывающей скобки автоматически, выделив мышью ячейки, содержимое которых должно использоваться в качестве аргументов. Если в строке формул одного листа надо набрать адрес ячейки другого листа, можно мышью найти нужную ячейку и Excel сам вставит нужный адрес, включающий информацию вида: =B5*Лист1!A1.

Адрес выделенного диапазона ячеек сразу же будет представлен в позиции курсора ввода.

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

=СУММ(С7:С9;Лист2!В3:В15;[Книга2]Лист1!А2:А6).

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

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

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

Задание. Пусть переменная х = 1,56 и записана в ячейке Н1. Запишите выражение для подсчета следующих выражений:

а) 12х^3–72х^2+2х+ б) Sin(x^2)+log(x^2)–exp(x) в) sin(x)–tg(x)/3+2x^4–5x^2+5. Excel не содержит специальных типов ячеек для даты и времени. Для этого используется числовой тип. Причем, целая часть числа интерпретируется как количество дней, прошедших с 1.1.1900 года, а дробная – как часть от 24 часов.

ТЕМА 2. ЭЛЕМЕНТЫ ФОРМАТИРОВАНИЯ

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

Самый простой – использование кнопок форматирования на панели инструментов. В стандартной конфигурации Excel панель инструментов Форматирование присутствует на экране. Кроме кнопок, установленных на панели по умолчанию, существует и ряд скрытых за одной (или нескольких) из кнопок, которые применяются для двойного подчеркивания, изменения цвета выделенного текста, выравнивания текста по обоим краям, выбора стиля, поворота текста на 900 и т. д.

Пользоваться такими инструментами несложно – надо выделить фрагмент для форматирования и нажать нужную кнопку.

Форматировать ячейки или блоки можно и через меню Формат – Ячейки. Эта команда открывает доступ к шести вкладкам: Число, Выравнивание, Шрифт, Граница, Вид, Защита, на которых отражены все аспекты форматирования ячеек.

Формат позволяет отображать числовые данные в том или ином виде. Чтобы задать/изменить формат ячейки (блока) надо войти в меню Формат – Ячейки – Число или Ctrl+1.

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

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

По умолчанию задан формат Общий. Excel сам подбирает подходящий формат отображения числа либо с фиксированной запятой, либо в экспоненциальной форме. Если ширина введённого числа больше, чем ширина ячейки на экране, то Excel отображает его в экспоненциальной форме или вместо числа ставит символы «####» (при этом число в памяти сохраняет верно).

Пользователь может выбрать другой подходящий формат.

Формат, заданный для ячейки, не отображается непосредственно в каком-то информационном окне. «Посмотреть» текущий формат ячейки можно, вызвав Формат – Ячейки – Число.

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

Шаблоны состоят из набора знаков-кодов преобразования.

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

Существует несколько правил, по которым создается формат.

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

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

Приведем некоторые из кодов преобразований:

E или е Экспоненциальный формат [цвет n] Цвет шрифта, n – число от 0 до 56 (может задаваться именем) $ p. – + / ( ) : проСимволы, которые выводятся без преобразования в < > = = Условные операторы * Заполнение ячейки символом, следующим за символом "звездочка" Примеры использования шаблонов форматов