Какую формулу использовать в Excel

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

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

В 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.

Таблица 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. Результат расчета

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

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

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

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

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

Упражнение

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

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

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

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

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

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

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

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

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

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

Абсолютная ссылка может быть создана только при наборе формулы, перед адресом строки и столбца вводится знак доллара — $.

Для создания абсолютной ссылки удобно использовать клавишу абсолютной ссылки F4, которая осуществляет преобразование относительной ссылки в абсолютную и наоборот.

Знак доллара ($) появится как перед ссылкой на столбец, так и перед ссылкой на строку (например, $С$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 может распознать далеко не все ошибки, но те, которые обнаружены, надо уметь исправить.

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

Оцените статью
Как в офисе.ru
Добавить комментарий