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

Введение

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

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

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

Выполнение базовых арифметических операций

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

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

Например, давайте представим, что нам необходимо сложить два числа - «12» и «7». Установите курсор мыши в любую ячейку и напечатайте следующее выражение: «=12+7». По окончании ввода нажмите клавишу «Enter» и в ячейке отобразится результат вычисления - «19».

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

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

Чтобы это исправить, выделите ячейку с числом, у которого необходимо изменить количество десятичных знаков после запятой и на вкладке Главная в группе Число выберите команду Уменьшить разрядность . Каждое нажатие на эту кнопку убирает один знак.

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

Составление формул

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

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

Чтобы посчитать суммарный расход за январь в ячейке B7 можно написать следующее выражение: «=18250+5100+6250+2500+3300» и нажать Enter, после чего вы увидите результат вычисления. Это является примером применения простейшей формулы, составление которой ничем не отличается от вычислений на калькуляторе. Разве что знак равно ставится вначале выражения, а не в конце.

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

С учетом этого давайте изменим нашу формулу вычисления суммарных ежемесячных расходов.

В ячейку B7, введите знак равно (=) и… вместо того, чтобы вручную вбивать значение клетки B2, щелкните по ней левой кнопкой мыши. После этого вокруг ячейки появится пунктирная выделительная рамка, которая показывает, что ее значение попало в формулу. Теперь введите знак «+» и щелкните по ячейке B3. Далее проделайте тоже самое с ячейками B4, B5 и B6, а затем нажмите клавишу ВВОД (Enter), после чего появится то же значение суммы, что и в первом случае.

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

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

Установите курсор на пустой клетке сбоку нужной строки (в нашем случае это H2). Затем нажмите кнопку Сумма на закладке Главная в группе Редактирование . Теперь, вернемся к таблице и посмотрим, что же произошло.

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

Теперь посмотрим на саму формулу. Как и раньше, вначале идет знак равенства, но на этот раз за ним следует функция «СУММ» - заранее определенная формула, которая выполнит сложение значений указанных ячеек. Сразу за функцией идут скобки расположенные вокруг адресов клеток, значения которых нужно просуммировать, называемые аргументом формулы . Обратите внимание, что в формуле не указаны все адреса суммируемых ячеек, а лишь первой и последней. Двоеточие между ними обозначает, что указан диапазон клеток от B2 до G2.

После нажатия Enter, в выбранной ячейке появится результат, но на этом возможности кнопки Сумма не заканчиваются. Щелкните на стрелочку рядом с ней и откроется список, содержащий функции для вычисления средних значений (Среднее), количества введенных данных (Число), максимальных (Максимум) и минимальных (Минимум) значений.

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

Автозаполнение

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

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

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

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

Конечно, Excel не всегда верно «понимает», как нужно заполнить последующие клетки, так как последовательности могут быть довольно разнообразными. Представим себе, что нам необходимо заполнить строку четными числовыми значениями: 2, 4, 6, 8 и так далее. Если мы введем число «2» и попробуем переместить маркер автозаполнения вправо, то окажется, что программа предлагает, как в следующую, так и в другие ячейки вставить опять значение «2».

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

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

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

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

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

Существует так же способ заполнения ряда с помощью ленты. Воспользуемся им для вычисления сумм затрат по всем расходным статьям (столбец H).

Выделяем диапазон, который следует заполнить, начиная с ячейки с уже введенными данными. Затем на вкладке Главная в группе Редактирование нажимаем кнопку Заполнить и выбираем направление заполнения.

Добавление строк, столбцов и объединение ячеек

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

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

Щелкните в любой ячейке второй строки правой кнопкой мыши и в открывшемся меню выберите команду Вставить… , а затем в окне - Добавить строку .

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

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

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

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

На ленте для операций добавления можно использовать кнопку Вставить , расположенную в группе Ячейки на закладке Главная , а для удаления, одноименную команду в той же группе.

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

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

Белые неформатированные ряды в таблице мы оставили специально, что бы отделить доходную, расходную и итоговую часть друг от друга, написав в них соответствующие заголовки. Но перед тем как это сделать, мы изучим еще одну операцию в Excel - объединение ячеек .

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

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

Вернемся к нашей таблице. Для того, что бы написать заголовки в белых строчках нам понадобится лишь одна ячейка, в то время как сейчас они состоят из восьми. Давайте исправим это. Выделите все восемь ячеек второго ряда таблицы и на вкладке Главная в группе Выравнивание щелкните на кнопку Объединить и поместить в центре .

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

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

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

Заключение

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

Теперь переведем эти расчеты в формулы понятные Excel. Для января (ячейки B14) формула очень проста и будет выглядеть так: «=B5-B12». А вот для ячейки С14 (февраль) выражение можно записать двумя разными способами: «=(B5-B12)+(C5-C12)» или «=B14+C5-C12». В первом случае мы опять проводим расчет баланса предыдущего месяца и затем прибавляем к нему баланс текущего, а во втором в формулу включается уже рассчитанный результат по предыдущему месяцу. Конечно, использование второго варианта для построения формулы в нашем случае гораздо предпочтительнее. Ведь если следовать логике первого варианта, то в выражении для мартовского расчета будет фигурировать уже 6 адресов ячеек, в апреле - 8, в мае - 10 и так далее, а при использовании второго варианта их всегда будет три.

Для заполнения оставшихся ячеек с D14 по G14 применим возможность их автоматического заполнения, так же как мы это делали в случае с суммами.

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

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

А вот и наша итоговая таблица с выполненными расчётами:

Теперь, при желании, вы уже самостоятельно сможете продолжать ее наполнение, вставляя как дополнительные статьи расходов или доходов (строки), так и добавляя новые месяцы (столбцы).

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

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

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

1. Формула расчета доли в процентном отношении.

Пусть задано два числа: A 1 и A 2 . Надо определить, какую долю в процентном отношении составляет число A 1 от A 2 .

P = A 1 / A 2 * 100.

В финансовых расчетах часто пишут

P = A 1 / A 2 * 100%.

Пример. Какую долю в процентном отношении составляет 10 от 200

P = 10 / 200 * 100 = 5 (процентов).

2. Формула расчета процента от числа.

Пусть задано число A 2 . Надо вычислить число A 1 , составляющее заданный процент P от A 2 .

A 1 = A 2 * P / 100.

Пример. Банковский кредит 10 000 рублей под 5 процентов. Сумма процентов составит.

P = 10000 * 5 / 100 = 500.

3. Формула увеличения числа на заданный процент. Сумма с НДС.

Пусть задано число A 1 . Надо вычислить число A 2 , которое больше числа A 1 на заданный процент P. Используя формулу расчета процента от числа, получаем:

A 2 = A 1 + A 1 * P / 100.

A 2 = A 1 * (1 + P / 100).

Пример 1. Банковский кредит 10 000 рублей под 5 процентов. Общая сумма долга составит.

A 2 = 10000 * (1 + 5 / 100) = 10000 * 1.05 = 10500.

Пример 2. Сумма без НДС равна 1000 рублей, НДС 18 процентов. Сумма с НДС составляет:

A 2 = 1000 * (1 + 18 / 100) = 1000 * 1.18 = 1180.

style="center">

4. Формула уменьшения числа на заданный процент.

Пусть задано число A 1 . Надо вычислить число A 2 , которое меньше числа A 1 на заданный процент P. Используя формулу расчета процента от числа, получаем:

A 2 = A 1 - A 1 * P / 100.

A 2 = A 1 * (1 - P / 100).

Пример. Денежная сумма к выдаче за минусом подоходного налога (13 процентов). Пусть оклад составляет 10 000 рублей. Тогда сумма к выдаче составляет:

A 2 = 10000 * (1 - 13 / 100) = 10000 * 0.87 = 8700.

5. Формула вычисления исходной суммы. Сумма без НДС.

Пусть задано число A 1 , равное некоторому исходному числу A 2 с прибавленным процентом P. Надо вычислить число A 2 . Иными словами: знаем денежную сумму с НДС, надо вычислить сумму без НДС.

Обозначим p = P / 100, тогда:

A 1 = A 2 + p * A 2 .

A 1 = A 2 * (1 + p).

Тогда

A 2 = A 1 / (1 + p).

Пример. Сумма с НДС равна 1180 рублей, НДС 18 процентов. Стоимость без НДС составляет:

A 2 = 1180 / (1 + 0.18) = 1000.

style="center">

6. Расчет процентов на банковский депозит. Формула расчета простых процентов.

Если проценты на депозит начисляются один раз в конце срока депозита, то сумма процентов вычисляется по формуле простых процентов.

S = K + (K*P*d/D)/100
Sp = (K*P*d/D)/100

Где:
S — сумма банковского депозита с процентами,
Sp — сумма процентов (доход),
K — первоначальная сумма (капитал),

d — количество дней начисления процентов по привлеченному вкладу,
D — количество дней в календарном году (365 или 366).

Пример 1. Банком принят депозит в сумме 100 тыс. рублей сроком на 1 год по ставке 20 процентов.

S = 100000 + 100000*20*365/365/100 = 120000
Sp = 100000 * 20*365/365/100 = 20000

Пример 2. Банком принят депозит в сумме 100 тыс. рублей сроком на 30 дней по ставке 20 процентов.

S = 100000 + 100000*20*30/365/100 = 101643.84
Sp = 100000 * 20*30/365/100 = 1643.84

7. Расчет процентов на банковский депозит при начислении процента на процент. Формула расчета сложных процентов.

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

S = K * (1 + P*d/D/100) N

Где:


P — годовая процентная ставка,

При расчете сложных процентов проще вычислить общую сумму с процентами, а потом вычислить сумму процентов (доход):

Sp = S - K = K * (1 + P*d/D/100) N - K

Sp = K * ((1 + P*d/D/100) N - 1)

Пример 1. Принят депозит в сумме 100 тыс. рублей сроком на 90 дней по ставке 20 процентов годовых с начислением процентов каждые 30 дней.

S = 100000 * (1 + 20*30/365/100) 3 = 105 013.02
Sp = 100000 * ((1 + 20*30/365/100) N - 1) = 5 013.02

style="center">

Пример 2. Проверим формулу начисления сложных процентов для случая из предыдущего примера.

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

S 1 = 100000 + 100000*20*30/365/100 = 101643.84
Sp 1 = 100000 * 20*30/365/100 = 1643.84

S 2 = 101643.84 + 101643.84*20*30/365/100 = 103314.70
Sp 2 = 101643.84 * 20*30/365/100 = 1670.86

S 3 = 103314.70 + 103314.70*20*30/365/100 = 105013.02
Sp 3 = 103314.70 * 20*30/365/100 = 1698.32

Общая сумма процентов с учетом начисления процентов на проценты (сложные проценты)

Sp = Sp 1 + Sp 2 + Sp 3 = 5013.02

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

8. Еще одна формула сложных процентов.

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

S = K * (1 + P/100) N

Где:
S — сумма депозита с процентами,
К — сумма депозита (капитал),
P — процентная ставка,
N — число периодов начисления процентов.

Пример. Принят депозит в сумме 100 тыс. рублей сроком на 3 месяца с ежемесячным начислением процентов по ставке 1.5 процента в месяц.

S = 100000 * (1 + 1.5/100) 3 = 104 567.84
Sp = 100000 * ((1 + 1.5/100) 3 - 1) = 4 567.84

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

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

В Excel формулы начинаются со знака =. Скобки () могут использоваться для определения порядка математических операции.

Excel поддерживает следующие операторы:

• Арифметические операции:
  • сложение (+);
  • умножение (*);
  • нахождение процента (%);
  • вычитание (-);
  • деление (/);
  • экспонента (^).
• Операторы сравнения:
  • = равно;
  • < меньше;
  • > больше;
  • <= меньше или равно;
  • >= больше или равно;
  • <> не равно.
• Операторы связи:
  • : диапазон;
  • ; объединение;
  • & оператор соединения текстов.

Таблица 22. Примеры формул

Упражнение

Вставка формулы -25-А1+АЗ

Предварительно введите любые числа в ячейки А1 и A3.

1. Выберите необходимую ячейку, например В1.
2. Начните ввод формулы со знака=.
3. Введите число 25, затем оператор (знак -).
4. Введите ссылку на первый операнд, например щелчком мыши на нужную ячейку А1.
5. Введите следующий оператор(знак +).
6. Щелкните мышью в той ячейке, которая является вторым операндом в формуле.
7. Завершите ввод формулы нажатием клавиши Enter . В ячейке В1 получите результат.

Автосуммирование

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

1. Выберите ячейку, в которую надо поместить результат суммирования.
2. Щелкните кнопку Автосумма - ∑ или нажмите комбинацию клавиш Alt+=. Excel примет решение, какую область включить в диапазон суммирования, и выделит ее пунктирной движущейся рамкой, называемой границей.
3. Нажмите Enter для принятия области, которую выбрала программа Excel, или выберите с помощью мыши новую область и затем нажмите Enter.

Функция "Автосумма" автоматически трансформируется в случае добавления и удаления ячеек внутри области.

Упражнение

Создание таблицы и расчет по формулам

1. Введите числовые данные в ячейки, как показано в табл. 23.

	А	В	С	D	Б	F
1
2		Магнолия	Лилия	Фиалка	Всего
3	Высшее	25	20	9
4	Среднее спец.	28	23	21
5	ПТУ	27	58	20
в	Другое	8	10	9
7	Всего
8	Без высшего

Таблица 23. Исходная таблица данных

1. Выберите ячейку В7, в которой будет вычислена сумма по вертикали.
2. Щелкните кнопку Автосумма - ∑ или нажмите Alt+= .
3. Повторите действия пунктов 2 и 3 для ячеек С7 и D7.

Вычислите количество сотрудников без высшего образования (по формуле В7-ВЗ).

1. Выберите ячейку В8 и наберите знак (=).
2. Щелкните мышью в ячейке В7, которая является первым операндом в формуле.
3. Введите с клавиатуры знак (-) и щелкните мышью в ячейке ВЗ, которая является вторым операндом в формуле (будет введена формула).
4. Нажмите Enter (в ячейке В8 будет вычислен результат).
5. Повторите пункты 5-8 для вычислений по соответствующим формулам в ячейках С8 и 08.
6. Сохраните файл с именем Образование_сотрудников.х1s.

Таблица 24. Результат расчета

	А	B	С	D	Е	F
1	Распределение сотрудников по образованию
2		Магнолия	Лилия	Фиалка	Всего
3	Высшее	25	20	9
4	Среднее спец.	28	23	21
5	ПТУ	27	58	20
6	Другое	8	10	9
7	Всего	88	111	59
8	Без высшего	63	91	50

Тиражирование формул при помощи маркера заполнения

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

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

1. Выберите ячейку, содержащую формулу для тиражирования.
2. Перетащите маркер заполнения в нужном направлении. Формула будет размножена во всех ячейках.

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

Упражнение

Тиражирование формул

1.Откройте файл Образование_сотрудников.х1s.

1. Введите в ячейку ЕЗ формулу для автосуммирования ячеек =СУММ(ВЗ:03).
2. Скопируйте, перетащив маркер заполнения, формулу в ячейки Е4:Е8.
3. Просмотрите как меняются относительные адреса ячеек в полученных формулах (табл. 25) и сохраните файл.

	А	В	С	D	Е	F
1	Распределение сотрудников по образованию
2		Магнолия	Лилия	Фиалка	Всего
3	Высшее	25	20	9	=СУММ{ВЗ:03)
4	Среднее спец.	28	23	21	=СУММ(В4:04)
5	ПТУ	27	58	20	=СУММ(В5:05)
6	Другое	8	10	9	=СУММ(В6:06)
7	Всего	88	111	58	=СУММ(В7:07)
8	Без высшего	63	91	49	=СУММ(В8:08)

Таблица 25. Изменение адресов ячеек при тиражировании формул

Относительные и абсолютные ссылки

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

Использование относительных ссылок аналогично указанию направления движения по улице - "идти три квартала на север, затем два квартала на запад". Следование этим инструкциям из различных начальных мест будет приводить в разные места назначения.

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

Абсолютная ссылка на ячейку.иди область ячеек будет всегда ссылаться на один и тот же адрес строки и столбца. При сравнении с направлениями улиц это будет примерно следующее: "Идите на пересечение Арбата и Бульварного кольца". Вне зависимости от места старта это будет приводить к одному и тому же месту. Если формула требует, чтобы адрес ячейки оставался неизменным при копировании, то должна использоваться абсолютная ссылка (формат записи $А$1). Например, когда формула вычисляет доли от общей суммы, ссылка на ячейку, содержащую общую сумму, не должна изменяться при копировании.

Знак доллара ($) появится как перед ссылкой на столбец, так и перед ссылкой на строку (например, $С$2), Последовательное нажатие F4 будет добавлять или убирать знак перед номером столбца или строки в ссылке (С$2 или $С2 - так называемые смешанные ссылки).

1. Создайте таблицу, аналогичную представленной ниже.

Таблица 26. Расчет зарплаты

1. В ячейку СЗ введите формулу для расчета зарплаты Иванова =В1*ВЗ.

При тиражировании формулы данного примера с относительными ссылками в ячейке С4 появляется сообщение об ошибке (#ЗНАЧ!), так как изменится относительный адрес ячейки В1, и в ячейку С4 скопируется формула =В2*В4;

1. Задайте абсолютную ссылку на ячейку В1, поставив курсор в строке формул на В1 и нажав клавишу F4, Формула в ячейке СЗ будет иметь вид =$В$1*ВЗ.
2. Скопируйте формулу в ячейки С4 и С5.
3. Сохраните файл (табл. 27) под именем Зарплата.xls.

Таблица 27. Итоги расчета зарплаты

Имена в формулах

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

• имена могут содержать не более 255 символов;
• имена должны начинаться с буквы и могут содержать любой символ, кроме пробела;
• имена не должны быть похожи на ссылки, такие, как ВЗ, С4;
• имена не должны использовать функции Excel, такие, как СУММ, ЕСЛИ и т. п.

В меню Вставка, Имя существуют две различные команды создания именованных областей: Создать и Присвоить.

Команда Создать позволяет задать (ввести) требуемое имя (только одно ), команда Присвоить использует метки, размещенные на рабочем листе, в качестве имен областей (разрешается создавать сразу несколько имен ).

Создание имени

1. Выделите ячейку В1 (табл. 26).
2. Выберите в меню Вставка, Имя (Insert, Name) команду Присвоить (Define) .
3. Введите имя Часовая ставка и нажмите ОК .
4. Выделите ячейку В1 и убедитесь, что в поле имени указано Часовая ставка .

Создание нескольких имен

1. Выделите ячейки ВЗ:С5 (табл. 27).
2. Выберите в меню Вставка, Имя (Insert, Name) команду Создать (Create) , появится диалоговое окно Создать имена (рис. 88).
3. Убедитесь, что переключатель в столбце слева помечен и нажмите ОК .
4. Выделите ячейки ВЗ:СЗ и убедитесь, что в поле имени указано Иванов.

Рис. 88. Диалоговое окно Создать имена

Можно в формулу вставить имя вместо абсолютной ссылки.

1. В строке формул установите курсор в то место, где будет добавлено имя.
2. Выберите в меню Вставка, Имя (Insert, Name) команду Вставить (Paste), появится диалоговое окно Вставить имена.

1. Выберите нужное имя из списка и нажмите ОК.

Ошибки в формулах

Бели при вводе формул или данных допущена ошибка, то в результирующей ячейке появляется сообщение об ошибке. Первым символом всех значений ошибок является символ #. Значения ошибок зависят от вида допущенной ошибки.

Excel может распознать далеко не все ошибки, но те, которые обнаружены, надо уметь исправить.

Ошибка # # # # появляется, когда вводимое число не умещается в ячейке. В этом случае следует увеличить ширину столбца.

Ошибка #ДЕЛ/0! появляется, когда в формуле делается попытка деления на нуль. Чаще всего это случается, когда в качестве делителя используется ссылка на ячейку, содержащую нулевое или пустое значение.

Ошибка #Н/Д! является сокращением термина "неопределенные данные". Эта ошибка указывает на использование в формуле ссылки на пустую ячейку.

Ошибка #ИМЯ? появляется, когда имя, используемое в формуле, было удалено или не было ранее определено. Для исправления определите или исправьте имя области данных, имя функции и др.

Ошибка #ПУСТО! появляется, когда задано пересечение двух областей, которые в действительности не имеют общих ячеек. Чаще всего ошибка указывает, что допущена ошибка при вводе ссылок на диапазоны ячеек.

Ошибка #ЧИСЛО! появляется, когда в функции с числовым аргументом используется неверный формат или значение аргумента.

Ошибка #ЗНАЧ! появляется, когда в формуле используется недопустимый тип аргумента или операнда. Например, вместо числового или логического значения для оператора или функции введен текст.

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

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

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

Функции в Excel

Более сложные вычисления в таблицах Excel осуществляются с помощью специальных функций (рис. 90). Список категорий функций доступен при выборе команды Функция в меню Вставка (Insert, Function).

Финансовые функции осуществляют такие расчеты, как вычисление суммы платежа по ссуде, величину выплаты прибыли на вложения и др.

Функции Дата и время позволяют работать со значениями даты и времени в формулах. Например, можно использовать в формуле текущую дату, воспользовавшись функцией СЕГОДНЯ .

Рис. 90. Мастер функций

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

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

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

Текстовые функции предоставляют пользователю возможность обработки текста. Например, можно объединить несколько строк с помощью функции СЦЕПИТЬ .

Логические функции предназначены для проверки одного или нескольких условий. Например, функция ЕСЛИ позволяет определить, выполняется ли указанное условие, и возвращает одно значение, если условие истинно, и другое, если оно ложно.

Функции Проверка свойств и значений предназначены для определения данных, хранимых в ячейке. Эти функции проверяют значения в ячейке по условию и возвращают в зависимости от результата значения ИСТИНА или ЛОЖЬ .

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

Упражнение

Вычисление величины среднего значения для каждой строки в файле Образование.хls.

1. Выделите ячейку F3 и нажмите на кнопку мастера функций.
2. В первом окне диалога мастера функций из категории Статистические выберите функцию СРЗНАЧ , нажмите на кнопку Далее .
3. Во втором диалоговом окне мастера функций должны быть заданы аргументы. Курсор ввода находится в поле ввода первого аргумента. В это поле в качестве аргумента число! введите адрес диапазона B3:D3 (рис. 91).
4. Нажмите ОК .
5. Скопируйте полученную формулу в ячейки F4:F6 и сохраните файл (табл. 28).

Рис. 91. Ввод аргумента в мастере функций

Таблица 28. Таблица результатов расчета с помощью мастера функций

	А	В	С	D	Е	F
1	Распределение сотрудников по образованию
2		Магнолия	Лилия	Фиалка	Всего	Среднее
3	Высшее	25	20	9	54	18
4	Среднее спец.	28	23	21	72	24
8	ПТУ	27	58	20	105	35
в	Другое	8	10	9	27	9
7	Всего	88	111	59	258	129

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

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

В качестве операндов могут использоваться постоянные значения (числовые или текстовые константы), ссылки на ячейки или диапазоны ячеек, имена, логические величины (например, ИСТИНА или ЛОЖЬ) и массивы. Константой считается число или текст, которые непосред­ственно вводятся в ячейку, например, текст «Московские известия».

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

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

Операторы, используемые в Microsoft Excel

В Excel используют следующие операторы:

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

· Операторы сравнения – используются для сравнения двух чисел. В результате выполнения операции сравнения получается логическое значение: истина или ложь.

· Текстовый оператор – применяется для обозначения операции объединения нескольких после­довательностей символов в одну последовательность символов.

Назначение операторов приведено в табл. 2.2.

Таблица 2.2

Назначение используемых в Excel операторов

Арифметический оператор	Назначение оператора
+ (знак плюс)	Сложение
- (знак минус)	Вычитание (или унарный минус, например, -1)
/ (косая черта)
* (звездочка)	Умножение
% (знак процента)
^ (крышка)	Возведение в степень
Операторы сравнения
= (знак равенства)
> (знак больше)
< (знак меньше)
>= (знак больше и знак равенства)	Больше или равно
<= (знак меньше и знак равенства)	Меньше или равно
<> (знак меньше и знак больше)
Текстовый оператор
& (амперсанд)	Объединение двух текстовых строк в одну
Адресные операторы
: (двоеточие)
; (точка с запятой)

Арифметические операторы

Арифметическими операторы получили наиболее широкое распространение. Они обеспечи­вают сложение, вычитание, умножение, деление, возведение в степень, нахождение процента по данным, приведенным в ячейках электронной таблицы. Примеры использования арифметических операторов приведены в табл. 2.3. Предполагается, что в ячейках А1, А2 и А3 содержатся числа 4, 5 и 10 соответственно.

Таблица 2.3

Примеры использования арифметических операторов

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

Скобки должны быть парными, пробелы перед скобками или после них не допускаются, например:

15 – числовая константа,

“+” и “/” – операторы сложения и деления.

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

Ввод формулы в ячейку

Для ввода формулы в ячейку выделите ее и введите в строку формул или в ячейку знак “=”. Строка формул используется как средство, позволяющее создавать и изменять формулы.

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

Рис. 2.13. Окно Excel в режиме ввода формул

В качестве примера введем формулу в ячейку D4. Закончив ввод формулы, нажмем клавишу Enter или щелкнем в строке формул по кнопке Ввод. В ячейке, содержащей формулу, отобразится результат вычисления, сама формула будет видна в строке формул (рис. 2.14).

Рис. 2.14. Отображение расчетной формулы в строке формул

ОТОБРАЖЕНИЕ ФОРМУЛЫ В ЯЧЕЙКЕ. При стандартной настройке в ячейке с формулой отображается результат вычислений, а не сама формула. В некоторых случаях, например, при составлении и проверке сложных расчетов в ячейке удобнее отображать не числовое значение, а саму формулу. Для отображения формул на рабочем листе выберите команду Параметры в меню Сервис , откройтевкладку Вид и в рамке Параметры окна установите флажок Формулы .

Для возврата к принятому режиму отображения формул снимите флажок. Чтобы перейти в режим отображения формул или вернуться в обычный режим нажмите клавиши Ctrl+` (клавиша обратного апострофа находится на той же клавише, что и тильда “~” – ниже клавиши Esc).

Например, нам надо подсчитать сумму чисел, хранящихся в ячейках А1, А2, А3. Щелкнем дважды ячейку А5 и поместим в нее формулу=А1+А2+А3. Чтобы вычислить сумму чисел, хранящихся в ячейках С1, С2, С3, можно не писать заново формулу, а скопировать ее из ячейки А5 в С5. Excel автоматически изменит относительные ссылки и формула примет вид = С1+С2+С3. Измененную формулу можно увидеть, щелкнув ячейку С5. (Для отображения формул в окне приложения установите флажок Формулы на вкладке Вид диалогового окна Параметры ).

Абсолютная ссылка сохраняет адрес определенной ячейки независимо от местоположения ячейки с формулой. Например, если скопировать формулу из ячейки А7 с абсолютными ссылками в ячейку С7, то формула не изменится. Для указания абсолютной ссылки используется знак доллара $. $А$7, $С$7 (рис. 2.15).

Рис. 2.15. Отображение формул с абсолютными и относительными ссылками в окне Excel

В тех случаях, когда при копировании или перемещении формулы необходимо сохранить неизменным только номер строки или только наименование столбца, применяют смешанную ссылку, например $D7 или F$5. Более подробно использование абсолютной ссылки в формуле рассмотрено ниже в разделе «Копирование формулы».

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

При вводе ссылки на ячейку другого рабочего листа имя листа отделяется от имени ячейки восклицательным знаком. Если вводится ссылка на другую рабочую книгу, то указывается имя файла рабочей книги, которое заключается в квадратные скобки, например, [Книга2]Лист1!$C$3.

Копирование формул

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

КОПИРОВАНИЕ ФОРМУЛЫ МЕТОДОМ ПЕРЕТАСКИВАНИЯ. Приведем пример копиро­вания формулы методом перетаскивания. Предположим надо возвести в третью степень числа 5, 7 и 10, которые находятся в ячейках А3, А4 и А5. Результаты вычислений запишем соответственно в ячейки В3, В4 и В5. Выделим ячейку В3 и введем в нее формулу =А3^3 (символ “^” используется как оператор возведения в степень). Нажмем клавишу Enter. В ячейке появится результат 125. Выделим еще раз ячейку В3. Установим указатель мыши на маленький черный квадратик - маркер заполнения. Нажмем кнопку мыши и растянем рамку еще на две ячейки вниз. В выделенных ячей­ках отобразятся результаты вычислений: 343 и 1000. Щелкнем ячейку В4 – в строке формул увидим =А4^3, т.е. относительный адрес ячейки изменился.

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

КОПИРОВАНИЕ ФОРМУЛ С ПОМОЩЬЮ КОМАНДЫ КОПИРОВАТЬ В МЕНЮ ПРАВКА. Чтобы скопировать формулу, выделите ячейку с формулой и выберите в меню Правка команду Копировать . Затем выделите ячейку или диапазон ячеек, куда будет вставлена формула, и выбе­рите команду Вставить в меню Правка . Ячейки, в которые копируется формула, могут нахо­диться на другом листе или в другой книге. При копировании и при перемещении формул проис­ходит автоматическое изменение ссылок.

Стандартные функции Excel

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

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

· математические;

· статистические;

· текстовые;

· логические;

· финансовые;

· функции даты и времени и др.

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

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

Текстовые функции преобразуют числовое значение в форматированный текст, и результат больше не участвует в вычислениях как число. Они позволяет вычислить длину строки, преобра­зовать заглавные буквы в строчные и т.п. Для объединения нескольких текстовых строк в одну строку используется Амперсанд (&).

Например, в ячейке В3 вы можете записать фамилию продавца (Петров), в ячейке С3 – объем его продаж (5000). После записи в какой-нибудь ячейке В3& «продал»&ТЕКСТ(С3; "0,00 руб.")&" единиц товара» при проведении вычислений произойдет объединение содержимого ячеек в одну фразу: Петров продал на 5000,00 руб. единиц товара.

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

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

Функции для работы с датами и временем . Функция Дата вычисляет дату в числовом формате как число дней, прошедших с 1 января 1900 года. Функция Время – это доля 24 часового интервала, записанная в виде десятичной дроби, которая может принимать значения от 0 до 0,99999999. Нулю соответствуют 12:00:00 ночи. Часы изменяются от 0 до 23, минуты и секунды от 0 до 59.

Приведем примеры наиболее часто встречающихся функций.

СУММ(Список) – статистическая функция определения суммы всех числовых значений в Списке. Список может состоять из адресов ячеек и блоков, а также числовых значений.

СУММ(А2:А5;С1:С8)

СрЗнач(Список)– статистическая функция определения среднего арифметического значения всех перечисленных в Списке величин.

СрЗнач(B1:B10)

МАКС(Список) – статистическая функция, результатом которой является максимальное значение в указанном Списке.

МАКС (А3:А8;253)

IF (Условие, Истинно, Ложно) – логическая функция, проверяющая на истинность заданное логическое условие. Если условие выполняется, то результатом функции является значение аргу­мента "Истинно". Если условие не выполняется, то результатом функции становится значение аргумента "Ложно".

IF (B4<100, 100,200)

– если ячейка В4 содержит число меньше 100, то функции присваивается значение 100, если же это условие не выполняется (т.е. содержимое ячейки В4 больше или равно 100), функции присваивается значение 200.

РАЗЛИЧНЫЕ ВАРИАНТЫ ВЫПОЛНЕНИЯ РАСЧЕТОВ. В большинстве случаев Excel предусматривает несколько вариантов выполнения расчетов. Покажем это на примере суммирова­ния данных. Для сложения чисел в диапазоне ячеек используется функция СУММ, которая явля­ется самой часто используемой функцией.

Введем слагаемые в ячейки D3, D4, D5. Суммирование можно выполнить следующими спосо­бами:

I способ .

Выделим ячейку D6 и введем в нее формулу для расчета, сделав ссылку на ячейки, содержа­щие исходные данные:

Нажмем клавишу Enter, и в ячейке D6 появится результат.

II способ

Введем знак “=” в ячейку D6, щелкнем ячейку D3 - после знака равенства в ячейке D6 отобра­зится адрес ячейки D3.

Поставим знак “+” и щелкнем ячейку D4. В ячейке D6 появится запись “=D3+D4”. Введем знак “+”, щелкнем ячейку D5 и нажмем клавишу Enter. В ячейке D6 появится результат

III способ .

Щелкнем ячейку D6 и нажмем кнопку Автосумма на панели инструментов Стандартная . В ячейке появится запись =СУММ(D3:D5), т.е. предлагается провести суммирование данных, запи­санных в ячейках от D3 до D5, расположенных в одном столбце с D6.

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

IV способ .

Выделим ячейку D6 и введем в нее с клавиатуры формулу, содержащую функцию = СУММ (D3:D5).

V способ.

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

Использование ссылок в функциях

Чтобы сослаться на диапазон ячеек, введите ссылку на верхнюю левую ячейку диапазона, поставьте двоеточие (:), а затем – ссылку на правый нижний угол диапазона. Например, A2:C5. Ссылка на все ячейки между 6-й и 15-й строками включительно имеет вид 6:15, на все ячейки в столбце С – С:С.

Примеры записей диапазонов ячеек в функции:

· =СУММ(Е:Е) – определяется сумма числовых значений, содержащихся в столбце Е;

· =СУММ(Е2:G5) – подсчитывается сумма числовых значений, расположенных в диапазоне от Е2 до G5;

· =СУММ(5:5) – суммируются данные всех ячеек пятой строки;

· =СУММ(2;4) – подсчитывается сумма 2+4.

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

СУММ(С8;D4;Е2:E5;F5)

Выполнение расчета с использованием стандартных функций

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

Для вставки функции нажмите кнопку Вставка функции , На экране отобразится панель формул. Панель формул появляется также при нажатии кнопки Вставка функции на панели инст­рументов Стандартная . При неправильной записи формулы на экране может появиться сообщение о циклической ссылке. Имена функций можно набирать строчными буквами. Они будут преобра­зованы в прописные после нажатия клавиши Enter. Функции, являющиеся аргументом другой функции, называются вложенными. В формулах Excel можно использовать до семи уровней вложения функций.

Чтобы выполнить расчет, используя стандартную функцию, выполните следующие действия:

1. Выделите ячейку, в которую надо вставить функцию, введите “=”, а затем в раскрываю­щемся списке Функции в строке формул выберите нужную из списка. На экране отобразится диалоговое окно Аргументы функции. Если в раскрывающемся списке выбрать Другие функции, то откроется диалоговое окно Мастер функций – шаг 1 из 2 (рис. 2.16).

Другие способы отображения диалогового окна Мастер функций – шаг 1 из 2 :

· нажмите кнопку Вставка функции в строке формул;

· выберите команду Функция в меню Вставка .

Рис. 2.16. Диалоговое окно мастера функций – шаг 1 из 2

2. В диалоговом окне Мастер функций – шаг 1 из 2 в поле Поиск функции введите описание действия, которое вы хотели бы выполнить. Например, чтобы найти функции, связанные с лога­рифмами чисел, введите log . Если вы не знаете, к какой категории относится ваша функция, то в раскрывающемся списке или категория выберите строку Полный алфавитный перечень и просмотрите список всех функций в алфавитном порядке. Полоса прокрутки позволяет просмот­реть невидимые в данный момент элементы списка. В нижней части окна дается определение выделенной функции и ее аргументов. Чтобы получить описание функции, выберите функцию в списке и щелкните ссылку Справка по этой функции . Выделите нужную строку в списке Выбе­рите функцию и нажмите кнопку OK или клавишу Enter.

3. На экране отобразится диалоговое окно Аргументы функции (рис. 2.17). В верхней части окна размещаются поля, предназначенные для ввода аргументов, в нижней части – справочная информация: имя выбранной функции, все ее аргументы, назначение функции и каждого аргу­мента, текущий результат функции и всей формулы. В тех случаях, когда аргумент приведен полужирным шрифтом, он является обязательным, если обычным шрифтом, то его можно пропус­тить.

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

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

Рис. 2.17. Использование панели формул для оценки дисперсии по выборке

ПРИМЕР 1. Excel помогает вычислить различные справочные данные, не пользуясь справочниками. Найдите десятичный логарифм числа 250. Выполните упражнение следующим образом:

Вставка функции Мастер функций – шаг 1 из 2 Категория выделите строку Математические, а в нижнем списке Выберите функ­цию – LOG10 и нажмите кнопку OK . На экране отобразится диалоговое окно Аргументы функции (рис. 2.18). Введите число 250 в строку Число диалогового окна и получите результат 2,398.

Рис. 2.18. Вычисление десятичного логарифма

ПРИМЕР 2. Найдите значения тригонометрических функций: синуса, косинуса, тангенса, котангенса для угла 10 градусов.

Выделите ячейку, в которую надо вставить функцию, и нажмите кнопку Вставка функции в строке формул. В диалоговом окне Мастер функций – шаг 1 из 2 (см. рис. 2.16) в раскрываю­щемся списке Категория выделите строку Математические, а в нижнем списке Выберите функ­цию выберите соответствующую тригонометрическую функцию и нажмите кнопку ОК . Введите число 10 в строку Число диалогового окна Аргументы функции и увидите результат. Для справки приведем значения тригонометрических функций: sin 100=0,17, cos100=0,98, tg 100=0,18, ctg 100=5,67.

ПРИМЕР 3. Определите среднее арифметическое значение следующих величин: 5, 20, 10, 8. Разместите указанные величины в различных ячейках, например, В6, B10, B13 и B17. Установите курсор в ячейке, в которой будет расположен результат вычислений и нажмите кнопку Вставка функции в строке формул. В диалоговом окне Мастер функций – шаг 1 из 2 (см. рис. 2.16) в раскрывающемся списке Категория выделите строку Статистические, а в нижнем списке Выбе­рите функцию установите СРЗНАЧ. В окне Аргументы функции нажмите кнопку свертывания окна, расположенную справа от поля Число 1 и выделите диапазон ячеек, в котором расположены числа. Нажмите клавишу Ввод . В нижней части окна Аргументы функции вы прочтете значение среднего, которое после нажатия кнопки ОК будет помещено в заданную вами ячейку.

Примечание

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

Если на вкладке Вид диалогового окна Параметры в группе Примечания переключатель уста­новлен в положение Только индикатор, то ячейки, содержащие примечания, помечаются в верх­нем правом углу индикатором, имеющим вид красного треугольника. Если переключатель нахо­дится в положении Примечание и индикатор, то видны как индикатор, так и часть примечания, ограниченная рамкой (рис. 2.19).

Рис. 2.19. Отображение индикатора и текста комментария ячейки

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

Сообщение об ошибке

Сообщение об ошибке начинается со знака #, например, если при выполнении расчетов в ячейке появится #ЗНАЧ!, то это означает, что программа не может найти исходные данные. В зависимости от причины возникновения ошибки меняется вид сообщения. Так, сообщение #ДЕЛ/0! появляется, когда в формуле предлагается провести деление на ноль (рис. 2.20). При подводе указателя мыши к значку смарт-тега рядом с ними отображается, кнопка. Щелкните значок, чтобы открыть меню, из которого вы узнаете, какого типа ошибка обнаружена, сможете просмотреть этапы вычислений, провести изменения в строке формул и т.д.

Рис. 2.20. Сообщение об ошибке

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

Приближенные вычисления с помощью дифференциала

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

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

Для успешного освоения примеров необходимо уметь находить производные функций хотя бы на среднем уровне, поэтому если с дифференцированием совсем нелады, пожалуйста, начните с урока Как найти производную? Также рекомендую прочитать статью Простейшие задачи с производной , а именно параграфы о нахождении производной в точке и нахождении дифференциала в точке . Из технических средств потребуется микрокалькулятор с различными математическими функциями. Можно использовать Эксель, но в данном случае он менее удобен.

Практикум состоит из двух частей:

– Приближенные вычисления с помощью дифференциала функции одной переменной.

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

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

Приближенные вычисления
с помощью дифференциала функции одной переменной

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

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

Пример 1

Решение: Пожалуйста, перепишите в тетрадь рабочую формулу для приближенного вычисления с помощью дифференциала :

Начинаем разбираться, здесь всё просто!

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

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

В качестве подбираем «хорошее» значение, чтобы корень извлекался нацело . Естественно, это значение должно быть как можно ближе к 67. В данном случае: . Действительно: .

Примечание: Когда с подбором всё равно возникает затруднение, просто посмотрите на скалькулированное значение (в данном случае ), возьмите ближайшую целую часть (в данном случае 4) и возведите её нужную в степень (в данном случае ). В результате и будет выполнен нужный подбор: .

Если , то приращение аргумента: .

Итак, число 67 представлено в виде суммы

Сначала вычислим значение функции в точке . Собственно, это уже сделано ранее:

Дифференциал в точке находится по формуле:
– тоже можете переписать к себе в тетрадь.

Из формулы следует, что нужно взять первую производную:

И найти её значение в точке :

Таким образом:

Всё готово! Согласно формуле :

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

Ответ:

Пример 2

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

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

У некоторых, возможно, возник вопрос, зачем нужна эта задача, если можно всё спокойно и более точно подсчитать на калькуляторе? Согласен, задача глупая и наивная. Но попытаюсь немного её оправдать. Во-первых, задание иллюстрирует смысл дифференциала функции. Во-вторых, в древние времена, калькулятор был чем-то вроде личного вертолета в наше время. Сам видел, как из местного политехнического института году где-то в 1985-86 выбросили компьютер размером с комнату (со всего города сбежались радиолюбители с отвертками, и через пару часов от агрегата остался только корпус). Антиквариат водился и у нас на физмате, правда, размером поменьше – где-то с парту. Вот так вот и мучились наши предки с методами приближенных вычислений. Конная повозка – тоже транспорт.

Так или иначе, задача осталась в стандартном курсе высшей математики, и решать её придётся. Это основной ответ на ваш вопрос =)

Пример 3

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

Фактически то же самое задание, его запросто можно переформулировать так: «Вычислить приближенное значение с помощью дифференциала»

Решение: Используем знакомую формулу:
В данном случае уже дана готовая функция: . Ещё раз обращаю внимание, что для обозначения функции вместо «игрека» удобнее использовать .

Значение необходимо представить в виде . Ну, тут легче, мы видим, что число 1,97 очень близко к «двойке», поэтому напрашивается . И, следовательно: .

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

Находим первую производную:

И её значение в точке :

Таким образом, дифференциал в точке:

В результате, по формуле :

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

Абсолютная и относительная погрешность вычислений

Абсолютная погрешность вычислений находится по формуле:

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

Относительная погрешность вычислений находится по формуле:
, или, то же самое:

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

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

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

Вычислим абсолютную погрешность:

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

Ответ: , абсолютная погрешность вычислений , относительная погрешность вычислений

Следующий пример для самостоятельного решения:

Пример 4

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

Примерный образец чистового оформления и ответ в конце урока.

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

Но для страждущих читателей я раскопал небольшой пример с арксинусом:

Пример 5

Вычислить приближенно с помощью дифференциала значение функции в точке

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

Пример 6

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

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

Алгоритм решения принципиально сохраняется, то есть необходимо, как и в предыдущих примерах, применить формулу

Записываем очевидную функцию

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

Анализируя таблицу, замечаем «хорошее» значение тангенса, которое близко располагается к 47 градусам:

Таким образом:

После предварительного анализа градусы необходимо перевести в радианы . Так, и только так!

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

Дальнейшее шаблонно:

Таким образом: (при вычислениях используем значение ). Результат, как и требовалось по условию, округлён до двух знаков после запятой.

Ответ:

Пример 7

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

Это пример для самостоятельного решения. Полное решение и ответ в конце урока.

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

Приближенные вычисления
с помощью полного дифференциала функции двух переменных

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

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

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

Пример 8

Решение: Как бы ни было записано условие, в самом решении для обозначения функции, повторюсь, лучше использовать не букву «зет», а .

А вот и рабочая формула:

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

По условию требуется найти приближенное значение функции в точке .

Число 3,04 представим в виде . Колобок сам просится, чтобы его съели:
,

Число 3,95 представим в виде . Дошла очередь и до второй половины Колобка:
,

И не смотрите на всякие лисьи хитрости, Колобок есть – надо его съесть.

Вычислим значение функции в точке :

Дифференциал функции в точке найдём по формуле:

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

Вычислим частные производные первого порядка в точке :

Полный дифференциал в точке :

Таким образом, по формуле приближенное значение функции в точке :

Вычислим точное значение функции в точке :

Вот это значение является абсолютно точным.

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

Абсолютная погрешность:

Относительная погрешность:

Ответ: , абсолютная погрешность: , относительная погрешность:

Пример 9

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

Это пример для самостоятельного решения. Кто остановится подробнее на данном примере, тот обратит внимание на то, что погрешности вычислений получились весьма и весьма заметными. Это произошло по следующей причине: в предложенной задаче достаточно велики приращения аргументов: . Общая закономерность такова – чем больше эти приращения по абсолютной величине, тем ниже точность вычислений. Так, например, для похожей точки приращения будут небольшими: , и точность приближенных вычислений получится очень высокой.

Данная особенность справедлива и для случая функции одной переменной (первая часть урока).

Пример 10

Решение : Вычислим данное выражение приближенно с помощью полного дифференциала функции двух переменных:

Отличие от Примеров 8-9 состоит в том, что нам сначала необходимо составить функцию двух переменных: . Как составлена функция, думаю, всем интуитивно понятно.

Значение 4,9973 близко к «пятерке», поэтому: , .
Значение 0,9919 близко к «единице», следовательно, полагаем: , .

Вычислим значение функции в точке :

Дифференциал в точке найдем по формуле:

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

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

;

.

Полный дифференциал в точке :

Таким образом, приближенное значение данного выражения:

Вычислим более точное значение с помощью микрокалькулятора: 2,998899527

Найдем относительную погрешность вычислений:

Ответ: ,

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

Пример 11

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

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

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

Пример 12

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

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

Если честно, немного утомился, поскольку материал был нудноватый. Непедагогично это было говорить в начале статьи, но сейчас-то уже можно =) Действительно, задачи вычислительной математики обычно не очень сложны, не очень интересны, самое важное, пожалуй, не допустить ошибку в обычных расчётах.

Да не сотрутся клавиши вашего калькулятора!

Решения и ответы:

Пример 2: Решение: Используем формулу:
В данном случае: , ,

Таким образом:
Ответ:

Пример 4: Решение: Используем формулу:
В данном случае: , ,