Создание формул разных видов

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

Выполните одно из перечисленных ниже действий.

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

  1. Щелкните ячейку, в которую хотите ввести формулу.

  2. Введите = (знак равенства).

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

    • Введите константы и операторы, которые будут использоваться в вычислении.

Формула

Описание

=5+2

Сложение 5 и 2

=5-2

Вычитание 2 из 5

=5/2

Деление 5 на 2

=5*2

Умножение 5 на 2

=5^2

Возведение 5 во вторую степень

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

Формула

Описание

=A1+A2

Сложение значений в ячейках A1 и A2

=A1-A2

Вычитание значения в ячейке A2 из значения в ячейке A1

=A1/A2

Деление значения в ячейке A1 на значение в ячейке A2

=A1*A2

Умножение значения в ячейке A1 на значение в ячейке A2

=A1^A2

Возведение значения в ячейке A1 в степень, указанную в ячейке A2

  1. Совет : В формулу можно добавить любое число констант и операторов (общей длиной до 8192 знаков), чтобы получить нужный результат вычисления.

  2. Нажмите клавишу RETURN.

Создание формулы с помощью ссылок на ячейки и именованных ячеек

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

  1. Щелкните ячейку, в которую хотите ввести формулу.

  2. В строка формул введите = (знак равенства).

  3. Выполните одно из указанных ниже действий.

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

    • Чтобы задать ссылку на имя диапазон, в меню Вставка наведите указатель мыши на пункт Имя и щелкните Вставить. В поле Вставить имя выберите нужное имя и нажмите кнопку ОК.

Формула

Описание

=C2

Использует значение в ячейке C2

=Лист2!B2

Использует значение в ячейке B2 на листе "Лист2"

=Актив-Обязательства

Вычитает значение в ячейке "Обязательства" из значения в ячейке "Актив".

  1. Нажмите клавишу RETURN.

Создание формулы с использованием функции

  1. Щелкните ячейку, в которую хотите ввести формулу.

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

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

  3. Введите аргумент.

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

Формула

Описание

=СУММ(A:A)

Сложение чисел в столбце A

=СРЗНАЧ(A1:B4)

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

  1. Завершив ввод формулы, нажмите клавишу RETURN.

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

Во вложенных функция одна функция используется в качестве аргумента другой. Допускается до 64 уровней вложенности. Указанная ниже формула суммирует набор чисел (G2:G5) только в том случае, если среднее арифметическое другого набора (F2:F5) больше 50. В противном случае она возвращает 0.

  1. Щелкните ячейку, в которую хотите ввести формулу.

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

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

  3. Чтобы задать аргумент, выполните одно или несколько из указанных ниже действий.

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

    • Чтобы указать в качестве аргумента другую функцию, введите ее в соответствующее поле аргумента. Например, можно задать значение СУММ(G2:G5) в поле з_е_истина функции ЕСЛИ.

    • Компоненты формулы в разделе Аргументы соответствуют функции, выбранном на предыдущем этапе. Например, если выбрана функция ЕСЛИ, в разделе Аргументы отображаются ее параметры.

  4. Завершив ввод формулы, нажмите клавишу RETURN.

Создание формулы массива, возвращающей один результат

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

  1. Щелкните ячейку, в которую хотите ввести формулу.

  2. Введите нужную формулу.

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

    Если формула {=СУММ(B2:C2*B3:C3)} введена как формула массива, Excel умножает число акций на цену каждой из них (500*10 и 300*15), а затем суммирует полученные результаты и возвращает значение 9500.

  3. Чтобы ввести формулу как формулу массива, нажмите клавиши COMMAND + RETURN .

    Excel автоматически заключит формулу в символы { } (открывающую и закрывающую фигурные скобки).

    Примечания : 

    • Если формулу заключить в фигурные скобки вручную, она не превратится в формулу массива: для этого необходимо нажать клавиши COMMAND + RETURN .

    • Каждый раз при редактировании формулы массива фигурные скобки ({ }) исчезают, и чтобы применить изменения и вернуть скобки, необходимо еще раз нажать клавиши COMMAND + RETURN .

Создание формулы массива, возвращающей несколько результатов

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

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

  2. Введите нужную формулу.

    Например, функция ТЕНДЕНЦИЯ, которая принимает в качестве аргумента ряд из трех значений объема продаж (столбец B) за три месяца (столбец A), определяет линейные значения объема продаж. Чтобы увидеть все результаты, возвращенные этой формулой, ее нужно ввести в три ячейки столбца C (C1:C3).

    Если формула =ТЕНДЕНЦИЯ(B1:B3;A1:A3) введена как формула массива, она возвращает три отдельных результата (22196, 17079 и 11962) на основании трех значений объема продаж и трех месяцев.

  3. Чтобы ввести формулу как формулу массива, нажмите клавиши COMMAND + RETURN .

    Excel автоматически заключит формулу в символы { } (открывающую и закрывающую фигурные скобки).

    Примечания : 

    • Если формулу заключить в фигурные скобки вручную, она не превратится в формулу массива: для этого необходимо нажать клавиши COMMAND + RETURN .

    • Каждый раз при редактировании формулы массива фигурные скобки ({ }) исчезают, и чтобы применить изменения и вернуть скобки, необходимо еще раз нажать клавиши COMMAND + RETURN .

Советы и рекомендации по созданию формул

Быстрая смена типа ссылок

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

  1. Выделите ячейку с формулой.

  2. В строке формул выделите ссылку, которую хотите изменить.

  3. На вкладке Формула в группе Функция щелкните Переключить тип ссылки.

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

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

Быстрое копирование формул

Одну формулу можно быстро ввести в диапазон ячеек. Выделите диапазон, в который необходимо вывести полученные результаты, скопируйте формулу в строку формул и нажмите клавиши CONTROL + RETURN . Например, если ввести =СУММ(A1:B1) в диапазон C1:C15 и нажать клавиши CONTROL + RETURN , приложение Excel обновит формулу, скопированную в каждую из ячеек диапазона, используя для этого A1 в качестве относительной ссылки.

Использование встроенной справки по функциям и аргументам

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

Распространенные ошибки при создании формул

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

Что необходимо проверить

Дополнительные сведения

Проверьте соответствие между всеми открывающими и закрывающими скобками.

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

Используйте двоеточия для обозначения диапазонов.

При указании диапазона ячеек отделяйте двоеточием ссылку на первую ячейку диапазона от ссылки на последнюю (например, A1:A5).

Проверьте наличие всех обязательных аргументов.

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

Уровень вложенности функций не должен превышать 64.

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

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

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

Указывайте путь к внешним книгам.

Все внешние ссылки должны содержать имя соответствующей книги и путь к ней.

Числа не должны содержать форматирование.

Не форматируйте числа, которые вводятся в формулы. Например, если значение, которое требуется ввести, превышает 1 000 долларов США, укажите в формуле значение 1000.

См. также

Ввод формулы

Операторы вычислений и порядок операций

Использование относительных и абсолютных ссылок

Использование функции автозаполнения при вводе формул

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

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

Совершенствование навыков
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×