Числовые расчеты в Excel

598 Pages·2022·21.421 MB·Russian

by  Васильев А. Н.

•САНКТ-ПЕТЕРБУРГ
•МОСКВА
•КРАСНОДАР

А.Н.ВАСИЛЬЕВ
ЧИСЛОВЫЕ РАСЧЕТЫ
EXCEL
В
УЧЕБНОЕ ПОСОБИЕ
Издание второе, стереотипное
САНКТ-ПЕТЕРБУРГ
МОСКВА
КРАСНОДАР
2022
УДК 004
ББК22.18я723
В19 ВасильевА.Н. Числовые расчеты в Excel: учебное по-
собие для СПО/ А. Н. Васильев.— 2-е изд., стер. — Санкт-
Петербург: Лань, 2022.— 600с.— Текст: непосредствен-
ный.
ISBN 978-5-8114-9367-8
Книга посвящена методам решения вычислительных задач с помо-
щью приложения Excel. Тематика книги охватывает алгебраические урав-
нения и системы, интерполирование и аппроксимацию функциональных
зависимостей, дифференцирование и интегрирование, решение диффе-
ренциальных и интегральных уравнений, а также некоторые другие темы
из области вычислительных методов. Помимо этого, в книге описывают-
ся основные приемы работы с приложением Excel, обсуждаются способы
организации рабочих документов, анализируются методы ввода и редак-
тирования данных в рабочих документах, изучаются возможности при-
менения форматов и стилей, иллюстрируются принципы использования
встроенных вычислительных утилит, а также даются основы программи-
рования в VBA. Книга может использоваться в качестве учебного пособия
при изучении курсов вычислительной математики и математического
программирования, самоучителя или справочного пособия при решении
вычислительных задач средствами приложения Excel.
Учебное пособие предназначено для студентов, обучающихся в кол-
леджах по образовательным программам среднего профессионального об-
разования.
УДК 004
ББК22.18я723
Рецензенты:
В.Ю.РЕШЕТНЯК— доктор физико-математических наук, профессор ка-
федры теоретической физики физического факультета Киевского нацио-
нального университета им.Т.Шевченко;
С.И.ВИЛЬЧИНСКИЙ— доктор физико-математических наук, профес-
сор, зав. кафедрой квантовой теории поля физического факультета Киев-
ского национального университета им.Т.Шевченко;
Д.В.АНЧИШКИН— доктор физико-математических наук, ведущий на-
учный сотрудник отдела физики высоких плотностей энергии Института
теоретической физики НАН Украины им.Н.Н.Боголюбова.
Обложка
П. И. ПОЛЯКОВА
© Издательство «Лань», 2022
© А. Н. Васильев, 2022
© Издательство «Лань»,
художественное оформление, 2022
ВСТУПЛЕНИЕ
ОКНИГЕ, ПРИЛОЖЕНИИ EXCEL
И ВЫЧИСЛИТЕЛЬНОЙ МАТЕМАТИКЕ
Я в отпуске и потому работаю дома.
Ивообще, мои опыты абсолютно безопасны.
Изк/ф «Иван Васильевич меняет профессию»
К
ниг по Excel написано очень много и самых разных. Есте-
ственным образом возникает вопрос: зачем нужна еще одна? Вопрос прямой
и откровенный, поэтому требует такого же ответа. Вместе с тем, ответ будет
не очень кратким, и состоит он из нескольких пунктов.
Во-первых, эта книга не совсем об Excel— точнее, об Excel, но не только:
речь будет идти о решении вычислительных задач с помощью Excel. Причем
задачи будем рассматривать не просто «вычислительные»— это будут те за-
дачи, что изучаются в курсе вычислительной математики: решение алгебра-
ических уравнений и систем, интерполирование и аппроксимация функцио-
нальных зависимостей, интегрирование и дифференцирование, решение диф-
ференциальных уравнений.
Во-вторых, мы попытаемся сломать определенный стереотип о приложе-
нии Excel, за которым закрепилась слава «лучшего помощника бухгалтера».
Конечно, никто не оспаривает действительно уникальные возможности Excel
в области составления всевозможной финансовой документации, но на са-
мом деле это только вершина айсберга— у Excel очень широкие возможнос-
ти для применения в самых различных областях.
В-третьих, даже если мы задействуем готовые и хорошо проверенные ал-
горитмы числовых расчетов для решения той или иной задачи и при этом в
качестве вычислительного средства используем Excel, адаптация этих са-
мых алгоритмов к вычислительной среде Excel требует некоторых навыков
и сноровки. Об этом тоже пойдет речь в книге.
ОСОБЕННОСТИ КНИГИ
Я бы на месте правительства всем,
кто заочно учится, ордена бы выдавала.
Изк/ф «Девчата»
Несложно сообразить, что тематика книги достаточно широка и объем-
на, особенно если учесть, что в ней описывается как само приложение, так и
конкретные математические задачи и алгоритмы. Чтобы выдержать объем кни-
ги в разумных пределах, на вооружение был взят принцип (закон) Парето.
ВСТУПЛЕНИЕ 5
Назаметку
Вильфредо Парето (1848–1923)— экономист и социолог. Карьеру начинал
как инженер (закончил Политехническую школу в Турине). Автор публика-
ций по экономической теории и математической экономике. Является создате-
лем теории элит. В1893г.— профессор политической экономики в Лозаннс-
ком университете (Швейцария). Диктатор Бенито Муссолини считал Парето
своим учителем. Закон Парето лишь назван в честь ученого, а сформулирован
его последователями.
Закон Парето утверждает, что в любого рода деятельности 20% усилий
приносят 80% результата, а остальные 80% усилий дают лишь 20% резуль-
тата. Кнаписанию книг по Excel это все, на первый взгляд, не имеет ника-
кого отношения. Но это только на первый взгляд.
Главное достоинство Excel состоит в том, что это очень «емкое» прило-
жение. Более-менее полное его описание является задачей крайне пробле-
матичной и малоперспективной. Поэтому самые толстые книги обычно ог-
раничиваются хотя и относительно полным, но во многих отношениях по-
верхностным описанием утилит Excel. Восновном такие «тяжеловесные»
издания играют роль справочников. Справочники, безусловно, нужны. Но
нужны не только справочники. Хотя в процессе учебы хорошо иметь под
рукой объемный справочник, учиться по справочнику тяжело. Учеба—
это процесс. Издесь важно выделить те 20% от возможностей Excel, кото-
рые дадут 80% результата. Собственно, поиску заветных 20% и посвящена
эта книга.
Книга состоит из четырех частей. Впервой части (четыре главы) описы-
ваются основные приемы работы с приложением Excel. Вглавах первой час-
ти можно найти полезную информацию относительно того, как выглядит
рабочее окно приложения, какие существуют режимы работы, и как выпол-
няются основные настройки.
Вторая часть (также четыре главы) посвящена простым вычислениям в
Excel. Здесь можно найти примеры несложных прикладных вычислитель-
ных задач. Особенность ситуации в том, что задачи в этой части книги рас-
сматриваются в контексте методов работы с приложением.
Третья часть книги состоит из трех глав, в которых описываются методы
программирования в среде VBA (Visual Basic for Applications). Вэтой части
(которая, надо сказать, не очень большая) рассматриваются такие задачи:
запись макросов, составление программных кодов и создание пользователь-
ских форм. Сведения, полученные в этой и предыдущих частях книги, будут
полезными при рассмотрении прикладных вычислительных задач в четвер-
той части книги.
Четвертая часть состоит из четырех глав. Вглавах четвертой части рас-
сматриваются задачи по решению алгебраических уравнений и систем, ин-
терполированию и аппроксимации функциональных зависимостей, число-
вому дифференцированию и интегрированию (в том числе речь пойдет о
вычислении несобственных и двойных интегралов), решению дифференци-
альных и интегральных уравнений. Вобщей сложности (по объему) эта часть
немногим уступает трем предыдущим частям в совокупности. Поэтому в из-
6 ЧИСЛОВЫЕ РАСЧЕТЫ В EXCEL
вестном смысле можно утверждать, что книга состоит из двух блоков: в пер-
вом описываются методы работы с приложением, а во втором— методы ре-
шения в Excel вычислительных задач.
Материал в первых трех частях книги подбирался разнородный. Некото-
рые вопросы освещены достаточно поверхностно, некоторые не включены
вовсе. Есть и в деталях разобранные задачи. Понятно, что во многом такой
подход субъективен. Вместе с тем, при написании книги разумного объема с
неизбежностью приходится чем-то жертвовать.
Назаметку
Одной из таких «жертв» стали сводные таблицы. Это исключительно полез-
ная и функциональная опция в Excel. Ксожалению, о сводных таблицах нуж-
но писать много и подробно или не писать вовсе. Ктому же, книга все-таки
ориентирована на реализацию числовых алгоритмов, а красота и элегантность
сводных таблиц проявляется скорее при обработке больших массивов дан-
ных, что несколько уводит нас от центральной темы.
Внекоторых случаях жертвы были неизбежными. Внекоторых— уме-
ренно просчитанными. Генеральный план состоял в том, чтобы выделить
некий «остов» из знаний и навыков в области работы с приложением Excel,
дабы впоследствии читатель смог самостоятельно и без особых проблем ра-
зобраться со всеми вопросами, которые не описаны в книге и могут у него
возникнуть при работе с электронными таблицами.
Что касается четвертой части книги, то она базируется на классическом
курсе числовых методов, который читается для студентов естественнонауч-
ных и инженерных специальностей университетов. Правда, в силу объектив-
ных причин некоторая строгость изложения материала нарушена. Тем не ме-
нее, минимальные (необходимые для понимания происходящего) теоретичес-
кие сведения приводятся в каждой главе четвертой части. Поэтому в четвертой
части книги представлена своеобразная «проекция» теории числовых расчетов
в прикладную плоскость, определяемую возможностями приложения Excel.
НЕМНОГО
О ПРИЛОЖЕНИИ EXCEL
Если вы нашли в подвале машину времени,
никогда не включайте ее без разрешения взрослых.
Изк/ф «Гостья из будущего»
Начиная с версии Microsoft Office 2007 все приложения этого пакета
(включая и Excel) получили новый «ленточный» интерфейс. Стех пор по-
явилось еще две версии «ленточного» приложения: Excel2010 и Excel2013.
Большинство элементов у этих трех версий приложения сходны, однако име-
ются и некоторые различия. Вкниге за базовую выбрана последняя (на мо-
мент написания) версия приложения— Excel2013. При этом все, что каса-
ется вычислительных задач в четвертой части, остается справедливым и для
более ранних версий продукта, причем не только до версии Excel2007, но и
для более ранних версий. Вобщем и целом это же замечание относится ко
ВСТУПЛЕНИЕ 7
второй и третей части книги— с той лишь поправкой, что в «неленточных»
версиях приложения доступ к утилитам получаем не через пиктограммы
ленты, а через команды меню и кнопки панелей инструментов. Вплане на-
строек, которые описываются в первой части, конечно же, не все проходит
гладко с обратной совместимостью (т.е. совместимостью с предыдущими вер-
сиями). Втех местах книги, где между Excel2010 и Excel2013 имеются суще-
ственные отличия, приводятся врезки с соответствующими комментариями.
ОРОЛИ EXCEL ПРИ РЕШЕНИИ
ВЫЧИСЛИТЕЛЬНЫХ ЗАДАЧ
Ипохондрия есть жестокое любострастие,
которое содержит дух
в непрерывном печальном положении.
Тут медицина знает разные средства,
лучшее из которых и самое безвредное— беседа.
Слово лечит, разговор мысль отгоняет.
Изк/ф «Формула любви»
Не будет ошибкой сказать, что первые три части играют вспомогатель-
ную роль и предназначены в первую очередь для тех читателей, кто не зна-
ком или мало знаком с приложением Excel. Методы прикладного использо-
вания Excel описываются в четвертой части. Здесь следует учесть, что воз-
можности для использования приложения Excel для проведения вычислений
широчайшие и не ограничиваются чисто математическими расчетами. При-
ложение идеально подходит для решения общих статистических задач, вы-
полнения регрессионного и дисперсионного анализа, проверки гипотез и
многого другого. Иэто не учитывая «традиционной» финансово-экономи-
ческой составляющей. Даже больше. Стандартные возможности приложе-
ния можно существенно расширить за счет подключаемых надстроек, среди
которых особое место занимают надстройки для совместного использова-
ния Excel и Maple, а также Excel и MATLAB. Эти надстройки не входят в
комплект поставки Excel (они устанавливаются вместе с соответствующи-
ми математическими пакетами), но они есть и их можно использовать. Вс-
вете сказанного возникает вопрос: к чему тогда использовать Excel при ре-
шении задач вычислительной математики, если для этих целей предназна-
чены специальные математические и статистические пакеты? Ответов может
быть несколько.
Во-первых, следует учесть, что нередко профессиональному исследовате-
лю (или студенту) необходимо создать собственный (или усовершенствовать
существующий) алгоритм решения той или иной задачи. Содной стороны.
Сдругой стороны, приложение Excel является достаточно распространен-
ным. Поэтому при наличии соответствующих навыков работы с приложени-
ем автоматически отпадает вопрос выбора среды разработки для решения
задачи. Впользу выбора Excel говорит и тот факт, что приложение обладает
серьезными возможностями в плане визуализации результатов.
Во-вторых, даже «классические» алгоритмы проведения числовых рас-
четов не всегда просты в практическом применении. Другими словами, боль-
8 ЧИСЛОВЫЕ РАСЧЕТЫ В EXCEL
шое значение имеет выбор адекватной (для решения поставленной вычисли-
тельной задачи) среды разработки. Приложение Excel во многих случаях
как раз является «адекватным» (хотя, конечно, и не всегда). Проверять это
утверждение мы будем на задачах стандартного курса вычислительной мате-
матики (или числовых методов— названия могут варьироваться). Разуме-
ется, весь курс охватить нам не удастся, но такая цель и не стоит. Мы про-
иллюстрируем возможности Excel в решении «основных» задач, которые
на практике встречаются наиболее часто. Вэтом отношении книга может
рассматриваться как учебное пособие по курсу вычислительной математи-
ки. Вовсяком случае, хочется верить, что она будет полезной читателю и
поспособствует пропаганде приложения Excel как эффективного вычисли-
тельного средства.
АВТОРА НА СЦЕНУ
—Амы где-то с Вами встречались!
—Ничего удивительного.
Меня ж весь Киев знает.
Изк/ф «Задвумя зайцами»
Обычно начинающим политтехнологам-студентам рассказывают, что са-
мая главная ошибка— думать, будто все остальные думают так же, как ты.
Когда книга пишется, всегда есть идея, которую автор пытается реализо-
вать. Идею бывает сложно выразить кратко, но в хорошей книге она есть.
Что касается книги, которую читатель держит в руках, то уместным было бы
сказать следующее: главная идея книги— это адаптация стандартных (или
«классических») алгоритмов решения задач вычислительной математики
для реализации в приложении, имеющем «ячеечную» структуру, т.е. струк-
туру электронной таблицы. Разумеется, есть у книги и другие «цели»— но
они в основном второстепенные. Насколько удалось реализовать данную кон-
цепцию, судить, конечно, читателю. Влюбом случае автор будет признате-
лен за мнения, замечания, предложения и конструктивную критику. Все это
предлагается высказывать по адресу [email protected].
Назаметку
Некоторую полезную информацию (по книге и не только) можно будет найти
на сайте www.vasilev.kiev.ua. Если читатель там для себя не найдет полезной
информации, имеет смысл предложить ее там разместить. Адрес электрон-
ной почты для обратной связи приведен выше (он же есть и на сайте).
ВСТУПЛЕНИЕ 9