Вставка формул в таблицу

Теоретическое обоснование

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

· возможность вставки в таблицы сложных формул и функций.

· гибкая система ссылок;

· возможность организации сценариев и итераций.

Вставка формул в таблицу

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

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

Арифметические операторы. Оператор сложения (+) складывает операнды, между которыми он находится; Оператор вычитания (­) вычитает второй операнд из первого; Оператор умножения (*) перемножает операнды, между которыми находится; Оператор деления (/) делит первый операнд на второй; Оператор процента (%) делит операнд на 100; Оператор возведения в степень (^) возводит операнд в степень; Среди операторов нет знака извлечения корня, но нужно помнить, что извлекать корни любой степени можно путем возведения в степень.

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

Если при сравнении с помощью оператора = (равно) сравниваемые значения равны, то результатом является ИСТИНА, иначе — ЛОЖЬ. Если при сравнении с помощью оператора < (меньше) левый операнд меньше правого, то результатом является ИСТИНА, иначе — ЛОЖЬ. Это относится и к сравнениям с помощью оператора <= (меньше или равно), с помощью оператора <> (не равно).

Текстовый оператор в Excel всего один — это & (амперсанд). Он служит для объединения (конкатенации) нескольких текстовых значений в одно

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

Операторов ссылок в Excel два – это оператор диапазона (:) и оператор объединения (;).

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

Абсолютная ссылка — это ссылка, которая всегда указывает на одну и ту же фиксированную ячейку независимо от того, куда и каким образом ее копируют или перемещают. Если нужно, чтобы ссылка была абсолютной, то нужно при вводе ссылки в строку формул указать это при помощи знака доллара ($) перед каждой из координат ссылки, например, $A$1. Кроме того, можно применить этот знак только к одной из координат ссылки. Тогда часть ссылки станет относительной, а часть останется абсолютной, например, $A1 или A$1. В случае, когда делают абсолютной одну из координат ссылки, ссылка будет всегда указывать на один и тот же фиксированный столбец или же на одну и ту же фиксированную строку.

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

Ссылку в Excel можно установить не только на ячейку, но и на диапазон ячеек и на несколько диапазонов ячеек одновременно. Для этого используются специальные операторы – диапазона (:) и объединения (;).

Имена. Удобной оказывается способность Excel различать отдельные ячейки и группы ячеек по именам. Для того чтобы воспользоваться именем, его нужно задать. Делается это так:

· В таблице выделите ячейку (или диапазон ячеек, или несколько.

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

Ссылки на ячейки на других листах и в других книгах. Для того чтобы вставить ссылку на ячейку, которая находится в текущей книге, но на другом листе, нужно в строке формул ввести ссылку в виде: «Имя_листа!Адрес_ячейки»

2. 2 Функции Мастер функций.

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

Рисунок 1. Всплывающий список функций.

Все функции, несмотря на их разнообразие, имеют одинаковый стандартный формат: имя функции и находящийся в круглых скобках перечень аргументов, разделенных точками с запятой. Например: =ОКРУГЛ(A2:A6;0).

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

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

 

Рисунок 2. Первое окно Мастера функций.

 

 

Рисунок 3. Второе окно Мастера функций

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

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

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

Копируются формулы так же, как и другие данные: выделить ячейку, выполнить команду ПРАВКА> Копировать (или комбинация клавиш Ctrl+C); выделить целевую ячейку (ячейки), то есть область, куда надо поместить копируемые данные; выполнить команду ПРАВКА >Вставить (или Ctrl+V). Важным при копировании и перемещении формул является преобразование содержащихся в них ссылок на другие ячейки. В определенных случаях адреса должны оставаться неизменными, а в других ситуациях необходимо, чтобы их пересчитали с учетом относительного изменения местоположения. В электронных таблицах поддерживается система относительных и абсолютных ссылок.

Значение ошибки. Если в ячейке содержится формула, результат которой программа не может правильно определить, то в ячейке будет отображено значение ошибки (табл. 1.).

 

Таблица 1. ЗНАЧЕНИЕ ОШИБКИ В РАБОЧЕМ ЛИСТЕ

 

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