Ввод формулы в программе Microsoft excel нужно начинать с символов

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

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

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

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

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

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

Способ задания формулы №1

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

Рис. 1. Задание формулы с помощью мастера функций (шаг 1 из 2)

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

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

Рис. 2. Окно со справочной информацией по функции СУММ

Рис. 3. Задание аргументов формулы с помощью мастера функций (шаг 2 из 2)

Применительно к рассматриваемому примеру можно согласиться с диапазоном аргументов, предлагаемым программой MS Excel по умолчанию, поскольку аргументами функции суммирования СУММ является диапазон ячеек А1:А10. В этом же окне сразу отображается результат суммирования — число 55. Вводимая формула отображается также в строке ввода и редактирования формул.

Для завершения ввода формулы следует нажать кнопку ОК. После ввода функции суммирования СУММ в вычислимую ячейку A11 рабочий лист будет иметь следующий вид (рис. 4, а).

Рис. 4. Результат ввода функции суммирования в ячейку A11 при отображении результата выполнения формулы (а) и собственно формулы (б)

Хотя в вычислимой ячейке А11 хранится соответствующая формула, которая отображается в строке ввода и редактирования формул, по умолчанию в самой ячейке отображается результат выполнения этой формулы. В отдельных случаях для проверки правильности задания формул в группе ячеек желательно отобразить в них сами формулы. Для отображения формул в ячейках рабочего листа следует выполнить операцию главного меню: Формулы → Показать формулы. После изменения способа отображения формул указанным способом этот же рабочий лист будет иметь следующий вид (рис. 4, б).

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

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

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

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

Рис. 5. Задание аргументов формулы с помощью специального окна мастера функций (шаг 2 из 2)

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

Второй способ задания формул в вычислимые ячейки основан на непосредственном вводе выражения, служащего для выполнения соответствующих расчетов. Для определенности рассмотрим в качестве примера ввод следующей функции: f(x) = 2х 2 — 3x + 10 , которая используется для расчета значения функции одного из значений независимой переменной х. С этой целью на существующем или отдельном листе выделим ячейку А2, предполагая, что отдельное значение независимой переменной будет вводиться в ячейку А1.

Способ задания формулы № 2

1. Выделить вычислимую ячейку, в которую предполагается ввести некоторую формулу. В рассматриваемом примере пусть это будет ячейка А2.
2. Начать ввод формулы в ячейку А2. для чего в качестве первого символа следует ввести знак равенства (=). Знак равенства в программе MS Excel служит признаком того, что в выделенную ячейку вводятся не данные» а некоторая формула.
3. В качестве выражения для формулы в ячейку А2 следует ввести следующую строку символов: =2*A1^2-3*A1+10 . Рабочий лист с вводом формулы данным способом будет иметь следующий вид (рис. 6).

Рис. 6. Задание формулы с помощью непосредственного ввода выражения в вычислимую ячейку

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

После окончания ввода выражения для формулы следует нажать клавишу Enter. В результате будет завершен ввод формулы, а в ячейке А2 при отсутствии ошибок будет указан результат выполнения введенной формулы — число 10. Этот результат получен в предположении, что в ячейке А1 содержится число 0, хотя это число не вводилось ранее. Если в эту ячейку ввести любое другое число, например, число 5, то после его ввода изменится и значение вычислимой ячейки А2, которое станет равно 45.

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

С этой целью ввести в поле поиска окна документации, вызванного с помощью клавиши F1, ключевое слово «операторы». В результате будет открыто отдельное окно справочной системы программы MS Excel, в котором следует выбрать информацию из группы «Типы операторов», нажав на ссылке с соответствующим именем (рис. 7).

Рис. 7. Окно со справочной информацией по типам операторов программы MS Excel

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

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

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

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

Речь идет о том, что в случае обычной адресации ячеек, которая называется относительном при копировании формул программа MS Excel автоматически изменяет адреса тех ячеек, которые используются в качестве аргументов и имеют относительную адресацию. Так, например, при копировании обычным способом содержимого ячейки A2 в ячейку В2 вместо ячейки А1 в качестве аргумента рассматриваемой функции будет подставлен адрес ячейки В1. Соответственно изменится и расчетное значение функции в ячейке В2 (рис. 8).

Рис. 8. Копирование формулы с относительной адресацией ячеек-аргументов

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

Для того чтобы при копировании функции вычислимой ячейки рассматриваемого примера всегда указывать в качестве ее аргумента ячейку А1, следует при первоначальном вводе этой функции или последующем ее редактировании записать соответствующее выражение функции в виде: =2*$A$1^2-3*$A$1+10 . В этом случае при копировании содержимого вычислимой ячейки А2 в ячейку В2 адрес ячейки-аргумента в соответствующей формуле не изменится (рис. 9).

Рис. 9. Копирование формулы с абсолютной адресацией ячеек-аргументов

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

Источник: excelexpert.ru

Ввод формулы

Формулы — это выражения, с помощью которых выполняются вычисления со значениями на листе. Все формулы начинаются со знака равенства (=). Простую формулу можно создать с помощью константа и вычислений оператор. Например, с помощью формулы =5+2*3 можно умножить два числа, а затем прибавить число к результату.

Если вы хотите ссылаться на переменные вместо констант, можно использовать значения ячеок, например =A1+A2. При работе с длинными столбцами данных или данными, которые находятся в разных частях листа или на другом листе, можно использовать диапазон, например =СУММ(A1:A100)/СУММ(B1:B100), который представляет деление суммы первых сотен чисел в столбце A на сумму этих чисел в столбце B. Если формула ссылается на другие ячейки, каждый раз при изменении данных в Excel пересчитыются результаты автоматически.

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

знаками равной запускают все формулы.

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

операторы определяют тип вычисления, выполняемого формулой. Например, оператор ^ (caret) поднимет число в число, а оператор * (звездочка) — для умножения чисел.

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

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

Ввод формулы, ссылающейся на значения в других ячейках

1. На листе, содержащем столбцы чисел, щелкните ячейку, в которой должны выводиться результаты формулы.
2. Введите знак равенства (=).
3. Щелкните первую ячейку, которую требуется включить в вычисление.
4. Введите оператор. Оператор представляет математическую операцию, выполняемую формулой. Например, оператор * (звездочка) перемножает числа. В этом примере используйте оператор / (косая черта), чтобы разделить числа. На этом этапе формула должна выглядеть так:
5. Щелкните следующую ячейку, которую нужно включить в вычисление. Теперь формула должна выглядеть так:
6. Нажмите клавишу RETURN. В ячейке отобразится результат вычисления.

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

Ввод формулы, содержащей функцию

1. На листе, содержащем диапазон чисел, щелкните пустую ячейку, в которой должны выводиться результаты формулы.
2. Введите знак равенства (=) и функцию, например =МИН. Функция МИН находит наименьшее число в диапазоне ячеек.
3. Введите открывающую круглую скобку, выберите диапазон ячеек, которые требуется включить в формулу, и введите закрывающую круглую скобку.
4. Нажмите клавишу RETURN. В этом примере функция МИН возвращает 11 — наименьшее число в ячейках от A1 до C4.

Советы

При вводе формулы в ячейке формула также отображается в строке формул.

Кнопки в строке формул могут помочь вам в создании формул.

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

Источник: support.microsoft.com

Как ввести формулу в ячейку Excel

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

Понятие формулы и функции

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

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

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

Термины, касающиеся формул

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

1. Константа. Это значение, которое остается одинаковым, и его невозможно изменить. Таким может быть, например, число Пи.
2. Операторы. Это модуль, необходимый для выполнения определенных операций. Excel предусматривает три вида операторов:

1. Арифметический. Необходим для того, чтобы сложить, вычитать, делить и умножать несколько чисел.
2. Оператор сравнения. Необходим для того, чтобы проверить, соответствуют ли данные определенному условию. Может возвращать одно значение: или истину, или ложь.
3. Текстовый оператор. Он только один, и необходим, чтобы объединять данные – >0;A1:A10))

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

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

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

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

   Источник: office-guru.ru