Васильев А.Н. Числовые расчеты в Excel. Учебное пособие – скачать книгу полностью

Жанры  |  Рассылка книг (в соцсетях, на email)

скачать полностью книгу Васильев А.Н. Числовые расчеты в Excel. Учебное пособие

 Скачать бесплатно книгу Васильев А.Н. Числовые расчеты в Excel. Учебное пособие

 Читать  онлайн  книгу в форматах fb2, epub, pdf, mobi, lrf

заказать книгу в интернет-магазинеЧисловые расчеты в Excel | Купить книгу с доставкой

Васильев А.Н. Числовые расчеты в Excel. Учебное пособие. –СПб.: Издательство «Лань», 2014. –608с: ил. –(Учебники для вузов. Специальная литература). ISBN 978-5-8114-1580-9.

Как провести числовой расчет в Экселе с использованием циклических ссылок

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

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

Распространённые ошибки вычислений по циклическим ссылкам в таблице Excel

Эта же причина может привести к некоторым недоразумениям, связанным с результатом вычислений по циклическим ссылкам при изменении начального приближения для корня уравнения. Еще раз подчеркнем, что схема вычислений такая: если изменяется какая-то ячейка в рабочем документе, то пересчитываются зависимые от нее ячейки и дальше по цепочке. Если в документе имеется явная или неявная циклическая ссылка, то в расчетах используется текущее значение ячейки. Например, в созданном нами документе при изменении значения ячейки В4 (начальное приближение для корня уравнения), пересчитывается значение ячейки В8. Как следствие изменения значения ячейки В8, пересчитываются ячейки В5 и В6. Это приводит к пересчету по явной циклической ссылке ячейки В7. Изменение ячейки В7 приводит к пересчету ячейки В8 и т. д.

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

Решение алгебраических уравнений и систем в программе Excel

Перед началом вычислений числами и формулами заполняются ячейки диапазона A4:G5. Столбец А содержит номер итерации. В столбцах В и С отображаются вычисляемые значения для переменных, относительно которых решается система уравнений. В столбцах D и F вычисляются и отображаются значения для функций в правых частях уравнений системы. Если решение найдено, то значения в столбцах В и D, а также С и Е должны совпадать. Для удобства и наглядности в столбцах FhG отображается разница между текущим значением переменной в левой части уравнения и функцией в правой части уравнения. Более подробно вводимые в ячейки документа значения описаны в таблице 12.4.

Процедура дальнейших вычислений нам уже знакома: выделяем диапазон ячеек A5:G5 и заполняем ячейки внизу перетягиванием маркера заполнения. Результат вычислений представлен на рисунке 12.35.
Мы получили достаточно неплохое приближение для точного решения системы уравнений х = 3 и у = 1.

Метод последовательных приближений
Ячейка Значение Комментарий
А4 =0 Начальная итерация
А5 =А4+1 Вычисление следующей итерации
В4 =0 Начальное приближение для переменной х
В5 =D4 Следующее приближение для переменной х. Вычисляется как значение функции 2^х2 + у2 + 1б Fx(x,y) = -f- (первое уравнение) на предыдущей итерации
С4 =0 Начальное приближение для переменной у
С5 =Е4 Следующее приближение для переменной у. Вычисляется как значение функции f(х,y) (второе уравнение) на предыдущей итерации
D4 =3*КОРЕНЬ(В4Л2+С4Л2+15)/5 Формула для вычисления значения функции (первое уравнение)
D5 =3*КОРЕНЬ(В5л2+С5л2+15)/5 Формула копируется из ячейки D4
Е4 =(В4+2)/5 Формула для вычисления функции (второе уравнение)
Е5 =(В5+2)/5 Формула копируется из ячейки Е4
F4 =B4-D4 Разница между переменной х и функцией F,(x, у) для нулевой итерации
F5 =B5-D5 Формула копируется из ячейки F4
G4 =С4-Е4 Разница между переменной у и функцией Fy(x, у) для нулевой итерации
G5 =С5-Е5 Формула копируется из ячейки G4

Вставлена ссылка на ячейку Excel и раскрыто контекстное меню параметров вставки

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

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

Сценарий происходящего при вставке содержимого из буфера обмена (при вставке с помощью опций диалогового окна Специальная вставка) определяется положением обеих переключателей (Вставка и Операция), и эффект может быть удивительным, а иногда и странным. Например, в рассматриваемом примере в ячейку А2 введена формула =$А$1+3*В1. Если в ячейку В2 введено числовое значение 2 и в эту ячейку копируется (через буфер обмена) значение ячейки А2 с переключателем Вставка в положении все и переключателем Операция в положении разделить, то в результате в ячейке В2 будет значение =2/($А$1+3*С1). Объяснение достаточно простое. Исходная ячейка содержит формулу, поэтому результатом будет формула. Выражение в формуле получается делением текущего значения конечной ячейки на формулу, содержащуюся в исходной ячейке. При этом формула исходной ячейки преобразуется в соответствии с правилами преобразования абсолютных и относительных ссылок.

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

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

Есть еще одно крайне немаловажное обстоятельство, которое следует иметь в виду при работе с формулами. Речь идет о так называемом режиме ссылок в формате R1C1. Чтобы перейти в соответствующий режим, на вкладке Файл выбираем пункт Параметры Excel и в открывшемся диалоговом окне настройки параметров приложения в разделе Формулы устанавливает флажок опции Стиль ссылок R1C1 (рис. 5.20).

 

Не получается скачать и прочитать книги в форматах djvu, pdf, rar? Прочтите подсказки

Скачайте похожую бесплатную книгу из раздела Формулы Excel:
Форман Дж. Много цифр: Анализ больших данных при помощи Excel
Яковлев В., Яковлева О. Группировка статистических данных в Excel
Козлов А.Ю. и др. Статистические функции MS Excel в экономико-статистических расчетах

     

Полный каталог книг для бесплатного скачивания

Рейтинг@Mail.ru