Как выполнять вычисления в таблицах Excel пакета Office 365

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

Использование формул

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

Простейшие формулы

Рассмотрим простой пример, в котором будем использовать таблицу, представленную на рис. 5.27.

Рис. 5.37. Пример таблицы

Рис. 5.37. Пример таблицы

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

Обратите внимание, к ячейкам столбца Цена применен формат Денежный. Данный формат добавляет к указанному числу 2 десятичных знака и обозначение денежной единицы. Такой же формат применен и к ячейкам столбца Стоимость, но на текущий момент эти ячейки пусты. В ячейки столбца Стоимость нужно добавить формулы, вычисляющие стоимость каждого товара. Это очень простые формулы, в которых нужно умножить цену единицы товара на его количество. В качестве переменных будут использоваться ячейки из столбцов Цена и Количество. Итак, начнем.

  1. Создайте таблицу, аналогичную приведенной на рис. 5.37.
  2. Выделите ячейку D2, в которой должна рассчитываться стоимость первого товара.
  3. Введите знак = (равно). Ввод любой формулы начинается с этого знака.
  4. Щелкните мышью по ячейке B2. Она будет выделена пунктирной рамкой, и адрес указанной ячейки окажется вставлен в формулу.
  5. Введите знак * (звездочку). Это оператор умножения.
  6. Щелкните мышью по ячейке C2. Адрес этой ячейки будет вставлен в формулу. Формула в ячейке D2 должна иметь вид =B2*C2.
  7. Нажмите клавишу Enter, чтобы завершить ввод формулы (рис. 5.38).
Рис. 5.38. Результат вычисления в ячейке D2

Рис. 5.38. Результат вычисления в ячейке D2

Как только вы завершили ввод формулы, в ячейке D2 мгновенно появился результат вычисления. Значение, указанное в ячейке B2, было умножено на значение, указанное в ячейке C2, и результат умножения был возвращен ячейкой D2 (ячейкой, в которой находится формула). Выделите ячейку D2. Обратите внимание, в выделенной ячейке отображается результат вычисления. Но если вы посмотрите на строку формул, то увидите в ней формулу, содержащуюся в ячейке. Таким образом, в этой строке вы можете редактировать формулу. Также обратите внимание, что мы не вводили адреса ячеек в формулу вручную. Вместо этого мы просто выделяли ячейки, адреса которых нужно подставить в формулу. Тем не менее ручной ввод адресов тоже допускается.

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

  1. Выделите ячейку D2, то есть ячейку, в которой содержится формула.
  2. Нажмите кнопку Копировать в группе Буфер обмена на вкладке Главная (или нажмите сочетание клавиш Ctrl+C). По периметру ячейки появится пунктирная рамка и содержимое ячейки (именно формула, а не возвращенный ей результат), будет помещено в буфер обмена.
  3. Выделите ячейки D3:D9, то есть ячейки, в которые нужно добавить формулу.
  4. Нажмите кнопку Вставить в группе Буфер обмена на вкладке Главная (или сочетание клавиш Ctrl+V). Формула будет вставлена во все выделенные ячейки, и в них мгновенно отобразится результат вычисления (рис. 5.39).
Рис. 5.39. Формула размножена на остальные ячейки столбца D

Рис. 5.39. Формула размножена на остальные ячейки столбца D

Виды ссылок

Теперь рассмотрим одну особенность, которая позволяет тиражировать формулу сразу в несколько ячеек. Выделите ячейку D3 и посмотрите на строку формул. Вы увидите, что в ячейке содержится формула =B3*C3. Выделите ячейку D7 и убедитесь, что в ней содержится формула =B7*C7. Как так получилось, если мы копировали в буфер формулу =B2*C2?

Дело в том, что Excel распознает смещение формулы в другую ячейку и автоматически применяет это смещение ко всем переменным в формуле. Таким образом, программа считает, что, если вы копируете формулу из второй строки (и в формуле участвуют переменные из второй строки) в третью строку, значит, и к переменным в формуле нужно применить аналогичное смещение. То есть адреса ячеек меняются, например, C2 на C3, C4, C5 и т. д. Это свойство позволяет копировать ранее созданную формулу в сотни ячеек одновременно, не заботясь о корректировке переменных. Ссылки, которые автоматически корректируются при копировании формулы в другие ячейки, называются относительными.

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

Предположим, что стоимость товара нужно еще разделить на курс валюты, который указан в единственной отдельно расположенной ячейке E2. Если мы создадим формулу вида =(B2*C2)/E2, то при копировании формулы в третью строку она будет преобразована в =(B3*C3)/E3, что приведет к неверному результату вычисления, поскольку курс валюты указан только в ячейке E2 и нигде больше. Поэтому в формуле =(B2*C2)/E2 нужно заменить относительный адрес ячейки E2 на абсолютный. В абсолютном адресе перед заголовком столбца и номером строки вводится знак $. То есть, чтобы добавить в формулу абсолютный адрес ячейки E2, нужно ввести $E$2, чтобы формула приняла вид =(B2*C2)/$E$2. Теперь при копировании формулы в третью строку, формула будет преобразована в =(B3*C3)/$E$2. Как видите, относительные ссылки в формуле изменились в соответствии со смещением позиции формулы на листе, но ссылка на ячейку с курсом валюты осталась неизменной.

Существуют также смешанные ссылки . Это ссылки, в которых одна из координат является абсолютной, а другая — относительной. Пример: $E2 и E$2. В первом случае столбец в ссылке остается неизменным при любых обстоятельствах, в то время как номер строки может меняться при смещении формулы. Во втором случае, наоборот, строка ссылки закреплена, в то время как столбец в адресе ячейки может меняться. Знак $ для добавления в формулу абсолютной ссылки можно не вводить вручную. При добавлении в формулу ссылки методом выделения соответствующей ячейки, нажмите после выделения ячейки клавишу F4. Каждое нажатие клавиши будет последовательно менять ссылку на абсолютную, смешанную и относительную.

Математические операции

Любая математическая формула в ячейке создается достаточно просто. Вы вводите формулу точно так же, как писали бы ее на бумаге, только вместо переменных подставляете адреса ячеек, в которых они находятся. Предположим, нам нужно создать формулу вида D=1,25*A/(В+С)*2.

Разберем эту формулу. D — это результат вычисления формулы. Этот результат будет выводиться в ячейке, в которой расположена формула. То есть в электронной версии формулы нам данная буква не нужна. 1,25 и 2 — постоянные числа в формуле, то есть константы. Соответственно, они остаются неизменными. А, В и С — переменные. Эти числа могут меняться, что повлияет на результат вычисления. В электронном виде в качестве переменных используются ячейки. Предположим, что переменная А находится в ячейке C2, B — в ячейке D2, а C — в ячейке E2. Таким образом, формула в ячейке Excel будет иметь вид =1,25*C2/(D2+E2)*2.

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

Математические операторы Excel

Оператор Операция Пример использования
+ Сложение A1+B1
Вычитание A1-B1
* Умножение A1*B1
/ Деление A1/B1
^ Возведение в степень A1^2 (число A1 в степени 2)
15^A1 (15 в степени числа A1)
A1^B1 (число A1 в степени числа B1)

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

Рис. 5.40. Дополненная таблица

Рис. 5.40. Дополненная таблица

В колонке Скидка указаны размеры скидки для каждого вида товаров (в процентах). В колонке Стоимость с учетом скидки нужно создать формулу, которая будет высчитывать итоговую стоимость вида товара с вычетом скидки. Формула должна иметь вид =Ст-(Ст/100)*Ск, где Ст — стоимость товара, а Ск — скидка, выраженная в процентах.

  1. Выделите ячейку F2, то есть первую ячейку в колонке Стоимость с учетом скидки.
  2. Введите знак = (равно).
  3. Щелкните мышью по ячейке D2, чтобы подставить в формулу адрес ячейки с возвращенной стоимостью товара.
  4. Введите знак «минус».
  5. Введите круглую открывающую скобку.
  6. Снова щелкните мышью по ячейке D2, чтобы подставить ее в формулу.
  7. Введите знак деления /.
  8. Введите 100.
  9. Введите круглую закрывающую скобку.
  10. Введите знак умножения *.
  11. Щелкните мышью по ячейке F2 (первой ячейке в колонке Скидка). Адрес ячейки будет вставлен в формулу. У вас должна получиться формула =D2-(D2/100)*E2 (рис. 5.41).
  12. Нажмите клавишу Enter, чтобы завершить ввод формулы (рис. 5.42).
Рис. 5.41. Ввод формулы в ячейку F2

Рис. 5.41. Ввод формулы в ячейку F2

Рис. 5.42. Результат вычисления в ячейке F2

Рис. 5.42. Результат вычисления в ячейке F2

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

  1. Выделите ячейку с только что созданной формулой.
  2. Нажмите кнопку Копировать на вкладке Главная ленты или нажмите сочетание клавиш Ctrl+C.
  3. Выделите ячейки F3:F9, то есть остальные ячейки столбца Стоимость с учетом скидки.
  4. Нажмите кнопку Вставить на вкладке Главная или сочетание клавиш Ctrl+V. Формула будет вставлена в выделенные ячейки. При этом относительные ссылки в ячейках скорректируются с учетом номеров строк (рис. 5.43).
    Рис. 5.43. Формула размножена на остальные ячейки столбца F

    Рис. 5.43. Формула размножена на остальные ячейки столбца F

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

    Чтобы подсчитать итоговую стоимость всех товаров, необходимо вычислить сумму всех чисел, расположенных в столбце Стоимость с учетом скидки. Конечно, мы могли вы создать формулу вида =F2+F3+F4+F5…. Но это нерационально, а если учесть, что подобная таблица может содержать сотни строк, то еще и долго. Вместо этого лучше использовать функцию, которая автоматически суммирует числа в указанном диапазоне ячеек.

    1. Выделите ячейку E10.
    2. Введите слово Итого. Данная ячейка не участвует в вычислении, это просто элемент наглядного оформления таблицы.
    3. Выделите ячейку F10.
    4. Перейдите на вкладку Формулы.
    5. Нажмите кнопку Математические, расположенную в группе Библиотека функций.
    6. В появившемся меню найдите и выберите функцию СУММ. На экране появится диалоговое окно Аргументы функции (рис. 5.44).
    Рис. 5.44. Диалоговое окно Аргументы функции

    Рис. 5.44. Диалоговое окно Аргументы функции

    Аргумент является некой переменной, которая используется в функции. В качестве аргумента может использоваться константа, адрес ячейки или диапазон ячеек. В нашем случае аргументом функции должен являться диапазон ячеек F2:F9, в которых находятся рассчитанные суммы для каждого товара в отдельности. Обратите внимание, программа Excel часто интеллектуально определяет набор данных. В нашем примере так и произошло, и нужный нам диапазон уже указан в поле Число1. Если этого не произошло, выполните следующие действия. Вы можете выполнить их в любом случае, чтобы попрактиковаться.

    1. Очистите содержимое поля Число1.
    2. Переместите диалоговое окно Аргументы функции, чтобы видеть ячейки столбца Стоимость с учетом скидки.
    3. Выделите ячейки F2:F9. Адрес выделенного диапазона будет вставлен в поле Число1.
    4. Нажмите кнопку ОК. Диалоговое окно закроется, а в ячейке F10 отобразится сумма всех чисел диапазона F2:F9. Именно столько будет стоить весь набор канцтоваров с учетом скидки (рис. 5.45).
    Рис. 5.45. Итоговая стоимость, вычисленная с помощью функции СУММ

    Рис. 5.45. Итоговая стоимость, вычисленная с помощью функции СУММ

    Для быстрой вставки функции суммирования можно воспользоваться кнопкой Сумма на вкладке Главная ленты. При нажатии данной кнопки в выделенную ячейку вставляется функция СУММ, при этом автоматически выделяются все ячейки с числами, расположенные выше. Диалоговое окно Аргументы функции при этом не появляется. Вам остается лишь согласиться с выделенным диапазоном, либо выделить другой диапазон, чтобы подставить его в качестве аргумента, после чего нажать клавишу Enter.

    Зависимость ячеек

    Ячейки, участвующие в вычислениях, могут быть зависимыми и влияющими. Разберем эти термины.

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

    Влияющие ячейки — это ячейки, которые ссылаются на формулы, и могут влиять на результат вычисления формулы, расположенной в другой ячейке. Так, например, ячейки B2 и C2 влияют на результат, возвращаемый ячейкой D2. То есть ячейки B2 и C2 являются влияющими на ячейку D2. В свою очередь, на ячейку F2 влияют ячейки, участвующие в ее формуле, то есть, D2 и E2. Но, поскольку результат в ячейке D2 зависит от ячеек B2 и C2, то эти ячейки тоже косвенно влияют на результат вычисления в ячейке F2. Зависимые ячейки — это ячейки с формулами, которые зависят от ячеек, указанных в качестве переменной в формуле. Так, например, ячейка F2 является зависимой от ячеек D2 и E2. А ячейка D2 зависима от ячеек B2 и C2.

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

    1. Выделите ячейку F2.
    2. Перейдите на вкладку Формулы.
    3. Нажмите кнопку Влияющие ячейки в группе Зависимости формул.
    4. В таблице появятся стрелки, исходящие из ячеек D2 и E2 и указывающие на ячейку F2. Это говорит о том, что для ячейки F2 ячейки D2 и E2 являются влияющими (рис. 5.46).
    5. Нажмите кнопку Влияющие ячейки повторно. К имеющимся стрелкам добавятся стрелки, исходящие из ячеек B2 и C2. Эти ячейки влияют на ячейку D2 и, следовательно, на ячейку F2. Данные ячейки влияют на конечный результат не прямо, а косвенно (рис. 5.47).
    6. Нажмите кнопку Убрать стрелки в группе Зависимости формул на вкладке Формулы, чтобы убрать стрелки с таблицы.
    Рис. 5.46. Прямое влияние на ячейку F2

    Рис. 5.46. Прямое влияние на ячейку F2

    Рис. 5.47. Полное влияние на ячейку F2

    Рис. 5.47. Полное влияние на ячейку F2

    Теперь выявим ячейки, зависимые от, например, ячейки B2.

    1. Выделите ячейку B2, то есть ячейку с ценой первого товара.
    2. Нажмите кнопку Зависимые ячейки в группе Зависимости формул на вкладке Формулы. Появится стрелка, исходящая из ячейки B2 и указывающая на ячейку D2. Стоимость товара зависит от его цены (рис. 5.48).
    3. Снова нажмите кнопку Зависимые ячейки. Появится стрелка, соединяющая ячейки D2 и F2. Стоимость товара с учетом скидки зависит от стоимости товара, а также от его цены.
    4. В третий раз нажмите кнопку Зависимые ячейки. Появится стрелка, соединяющая ячейки F2 и F10. Итоговая сумма зависит от стоимости с учетом скидки, стоимость с учетом скидки зависит от стоимости, стоимость зависит от цены (рис. 5.49). Все связи зависимости показаны с помощью стрелок. Таким образом, вы можете отследить эти связи и выявить ошибку.
    5. Нажмите кнопку Убрать стрелки, чтобы очистить таблицу от стрелок.
    Рис. 5.48. Ячейки, зависимые от ячейки B2

    Рис. 5.48. Ячейки, зависимые от ячейки B2

    Рис. 5.49. Все ячейки, зависимые от ячейки B2

    Рис. 5.49. Все ячейки, зависимые от ячейки B2

    Сообщения об ошибках

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

    • #########. Собственно, это не ошибка. Такое сообщение в ячейке вы увидите, если число невозможно отобразить в заданном формате. Это может быть, если число не помещается по ширине ячейки. Решается проблема просто — увеличьте ширину столбца, чтобы число помещалось в ячейке. Также данное сообщение может появиться, если ячейка, к которой применен формат Дата или Время, содержит отрицательное число;
    • #ИМЯ?. Такое сообщение появляется если в формуле используется несуществующее имя диапазона (диапазонам ячеек можно присваивать имена, но мы не рассматриваем эту тему в рамках данной книги );
    • #ДЕЛ/0!. В формуле делается попытка деления на ноль. Ошибка может возникать, как при неверном составлении формулы, так и если в формуле содержится операция деления на переменную из ячейки с нулем или отсутствующим значением;
    • #ЧИСЛО!. Неправильное применение математического оператора;
    • #Н/Д. Не указан аргумент функции, либо аргумент ссылается пустую ячейку;
    • #ССЫЛКА!. В формуле или функции используется ссылка на несуществующую ячейку;
    • #ЗНАЧ!. Используется недопустимый тип аргумента в функции. Например, когда аргументом функции должно быть число, но задана ссылка на ячейку с текстом;
    • #ПУСТО!. Возникает при отсутствии общих ячеек, когда задано пересечение двух областей.

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

Наверх