Вставка формул в таблицу
Теоретическое обоснование
Мощь 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! | Задано деление на ноль |
| #ЗНАЧ! | Указан неправильный аргумент или неправильный оператор |
| #ИМЯ? | Указано недопустимое имя |
| #Н/Д | Значение не указано |
| #ПУСТО! | Задана область пересечения двух диапазонов, которые не пересекаются |
| #ССЫЛКА! | Указана некорректная ссылка |
| #ЧИСЛО! | Ошибка при использовании/получении числа |
| ###### | Результат не помещается в ячейке, ширину ячейки необходимо увеличить |