Алгоритм вычислений в электронной таблице excel

Обновлено: 07.07.2024

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

Функции с побочным эффектом и неявная передача данных

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

ПравильнаяФункция( X As Variant) As Variant . Это пример хорошей, правильно построенной функции. Через параметр X ей передается значение некоторой ячейки рабочего листа (объект Range ). В качестве результата она возвращает значение функции,- в нашем примере результат является копией входного параметра X .
ПобочныйЭффект(X As Variant, Y As Variant) As Variant . В данной функции помимо вычисления результата изменяется и значение параметра Y . Поскольку по умолчанию параметр передается по ссылке ( By Ref ), то это должно было бы привести к побочному эффекту и изменить содержимое ячейки рабочего листа, переданной в качестве параметра Y . Мы увидим, что этого, однако, не происходит.
НеявнаяПередача(X As Variant) As Variant В данной функции результат зависит не только от входного параметра X , но и от значения другой, неявно используемой ячейки рабочего листа.

Вот как выглядят описания наших функций:

Вот как выглядит рабочий лист Excel , на котором вызываются эти функции:

Рис. 2.1. Побочный эффект и неявная передача данных

Анализируя полученные результаты, обратим внимание на следующие моменты:

Циклические вычисления

Укажем, особенности семантики циклических вычислений:

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

В каких же ситуациях требуется прибегать к циклическим вычислениям? Это, возможно, следует делать, когда речь идет о реализации итерационного процесса, вычислениях по рекуррентным соотношениям. У нас уже были примеры реализации итерационных процессов, например, вычисление суммы ряда, задающего экспоненту, в которых не применялись циклические ссылки. Платой за это было использование дополнительных ячеек таблицы Excel . Правда, появлялись и новые возможности, - возможность построить график , проанализировать процесс сходимости и т.д. Тем не менее, программисту, привыкшему к традиционным языкам, и привыкшему "с детства" экономить на переменных, может показаться странным предложенное решение задачи о нахождении корня уравнения, где на экран выводятся результаты всех приближений. В Excel экономия ячеек не главная задача. Тем не менее, при реализации итерационных процессов можно, конечно, и в Excel иметь одну единственную ячейку X , значение которой изменяется, начиная от начального приближения до искомого результата. Это в большей степени соответствует понятию переменной в языках программирования.

Циклические вычисления и нахождение корней уравнения

Покажем, как можно использовать циклические вычисления на примере задачи нахождения корня уравнения методом Ньютона. Для простоты я начну с квадратного уравнения, а позже рассмотрю и более "серьезные" уравнения. Итак, рассмотрим квадратное уравнение: X 2 -5X+6 =0 . Найти корень этого (и любого другого уравнения) можно, используя всего одну единственную ячейку Excel . Для этого достаточно включить режим циклических вычислений и ввести в произвольную ячейку с именем, скажем X , рекуррентную формулу, задающую вычисления по Ньютону:

где F и F1 задают соответственно выражения, вычисляющие функцию и производную. Для нашего квадратного уравнения после ввода формулы в ней появится значение 2 , соответствующее одному из корней уравнения. А как получить второй корень? Обычно, это можно сделать путем изменения начального приближения. В нашем случае начальное приближение не задавалось, итерационный процесс вычислений начинался со значения, хранимого в ячейке X по умолчанию и равного нулю. Как же задать начальное приближение в циклических вычислениях? Возникшая проблема не связана с данной конкретной задачей. Она возникает всегда в циклических вычислениях, - до начала цикла надо задать начальные установки. В рекуррентных соотношениях всегда есть некоторый начальный отрезок . Решать задачу задания начальных установок в каждом случае можно по -разному. Я продемонстрирую один прием, основанный на использовании функции ЕСЛИ . Вот как выглядит "настоящее" решение этой задачи, использующее 4 ячейки, две из которых нужны по существу дела, а две используются для повышения наглядности процесса вычислений:

Вычисления в таблицах пакета Excel осуществляются при помощи формул. Ввод формул всегда начинается с символа =. Формула может содержать числа, адреса ячеек (ссылки на ячейки) и функции, разделенные знаками математических операций: ^ (возведение в степень), *, /, +, -. Круглыми скобками изменяется вышеприведенный порядок выполнения операций. Если ячейка содержит формулу и ее сделать активной, то на рабочем листе в этой ячейке отображается результат вычисления по этой формуле, а сама формула отображается в строке формул.

Запись функции состоит из имени функции, после которого в скобках записывается список параметров. Например, при известном х, вычисление sin(x) производится записью в строке формул функции =SIN(Х). Если значение Х введено в ячейку с номером В4, то для вычисления синуса этого значения в строку формул следует ввести: =SIN(В4).

Ячейка, содержащая формулу с адресами (ссылками), является зависимой. При вводе или редактировании формулы, ячейки, от которых зависит значение формулы, выделяются цветными рамками.

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

Абсолютный адрес определяется записью символа $ перед каждым элементом номера ячейки, например $A$12, $B$2:$B$10. Символ $ может вводиться в строку формулы с клавиатуры. Иногда при редактировании формулы, для изменения относительного адреса на абсолютный, в строке формул выделяют элементы относительного адреса и нажимают клавишу F4.

Создание диаграмм в табличном процессоре Excel

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

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

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

В Excel существует11 типов встроенных диаграмм, каждый из которых имеет еще множество разновидностей (видов). Выбор типа диаграммы определяется задачами, решаемыми при ее создании.

При создании диаграммы следует убедиться, что данные на листе расположены в соответствии с типом диаграммы:

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

· При создании диаграммы типа «Поверхность» должно быть два столбца (строки) данных, не считая столбца (строки) подписей категорий.

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

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

· Я познакомлю вам с некоторыми типами диаграмм.

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

· Линейчатая диаграмма делает отображение продажи билетов наглядным, например, становится яснее разница между пунктами назначения, хорошо видно, что в Кокшетау ездят реже.

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

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

Домашняя работа

Ответьте на вопросы теста:

Какие из записей функции являются правильными?

3. К математическим функциям не относится функция:

4. В ячейке С1 записана формула =СРЗНАЧ(ЕСЛИ(А10;А1;А2); А3). Значение ячейки С1 будет следующим:

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

Я разберу основы работы с формулами и полезные «фишки», способные упростить процесс взаимодействия с таблицами.

Поиск перечня доступных функций в Excel

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

Откройте вкладку «Формулы» и нажмите на кнопку «Вставить функцию» либо разверните список с понравившейся вам категорией функций.

Вместо этого всегда можно кликнуть по значку с изображением «Fx» для открытия окна «Вставка функции».

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

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

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

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

Вставка функции в таблицу

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

Использование математических операций в Excel

Если необходимо выполнить математические действия с ячейками или конкретными числами, в Excel тоже создается формула, поскольку все записи, начинающиеся с «=» в ячейке, считаются функциями. Все знаки для математических операций являются стандартными, то есть «*»– умножить, «/» – разделить и так далее. Следует отметить, что для возведения в степень используется знак «^». Вкратце рассмотрим объявление подобных функций.

Выделите любую пустую ячейку и напишите в ней знак «=», объявив тем самым функцию. В качестве значения можете взять любое число, написать номер ячейки (используя буквенные и цифровые значения слева и сверху) либо выделить ее левой кнопкой мыши. На следующем скриншоте вы видите простой пример =B2*C2, то есть результатом функции будет перемножение указанных ячеек друг на друга.

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

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

Растягивание функций и обозначение константы

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

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

В итоге вы должны увидеть, что функция растянулась на выбранный диапазон, а значения в ней подставлены автоматически. Так, изначальная функция имела вид =B2*C2, но после растягивания вниз последующие значения подставились автоматически (от B3*C3 до B13*C13, что видно на следующем изображении). Точно так же растягивание работает с СУММ и другими простыми формулами, где используется несколько аргументов.

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

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

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

В закрепление темы рассмотрим три константы, которые можно обозначить при записи функции:

$В$2 – при растяжении либо копировании остаются постоянными столбец и строка.

B$2 – неизменна строка.

$B2 – константа касается только столбца.

Построение графиков функций

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

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

Читайте также: