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

Перейдем к созданию расчетных формул и внесению их в соответствующие ячейки. Формулы представляют собой выражения, по которым выполняются вычисления на странице. Формула начинается со знака равенства (=), может также содержать такие элементы, как функции. Функцию в программе Microsoft Office Excel можно рассматривать как стандартную формулу, которая возвращает результат выполнения определенных действий над значениями, выступающими в качестве аргументов. Функции позволяют упростить формулы в ячейках листа, особенно, если они длинные или сложные. В формулах используются ссылки, операторы, константы. В качестве оператора выступают знаки или символы, задающие тип вычисления в выражении. Существуют математические, логические операторы, операторы сравнения. Константой является постоянное (не вычисляемое) значение. Например, число 210 и текст «Квартальная премия» являются константами. Выражение и результат вычисления выражения константами не являются.

Для начала ввода формулы необходимо впечатать в ячейку знак «=», после чего всю дальнейшую вводимую последовательность программа Microsoft Office Excel 2007 будет воспринимать, обрабатывать, анализировать, как формулу.Подтвердить ввод формулы в ячейку можно несколькими способами, например:

· нажатием клавиши Enter (Ввод), находясь в режиме редактирования в ячейке с формулой;

· нажатием клавиши Enter (Ввод), установив курсор после формулы в строке формул;

· щелчком по кнопке с изображением «галочки», расположенной в сроке формул (рисунок 42).

Рисунок 42. Кнопки строки формул

Остановимся более подробно на компонентах формул, создаваемых в программе Microsoft Office Excel 2007, рассмотрим их типы:

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

В нашем рассматриваемом задании в ячейку Е3 нужно ввести формулу, рассчитывающую среднее значение для Студента1 по трем оценкам (соответственно по трем предметам: математике, физике, химии). В данном случае нас интересует вычисление среднего арифметического для трех чисел, значений ячеек: B3, C3, D3, результат нахождения которого должен отобразиться в ячейке Е3. Щелкнув по ячейке Е3, наберите знак «=», затем, подведя указатель к ячейке В3, щелкните по ней один раз левой кнопкой мышки, при этом адрес ячейки В3 отобразится в создаваемой формуле (рисунок 43).

Рисунок 43. Начало создания формулы

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

Продолжая конструировать формулу, нажмите клавишу с изображением «+» на клавиатуре, удерживая при этом нажатой клавишу Shift, затем, подведя указатель к ячейке С3, щелкните по ней один раз левой кнопкой мышки, при этом адрес ячейки С3 также отобразится в создаваемой формуле (рисунок 44).

Рисунок 44. Процесс создания формулы

Далее внесите «+» и щелкните по ячейке D3. Убедившись в том, что все три адреса ячеек, значения которых должны быть просуммированы, отображены в создаваемой Вами формуле, добавьте круглые скобки в соответствии с рисунком 45 для возможности деления суммы на три. Для вставки круглых скобок можно воспользоваться соответствующими клавишами на клавиатуре, работая при этой с формулой, находящейся в редактируемой в данный момент ячейке (в нашем случае это ячейка с адресом Е3), либо в строке формул.

Рисунок 45. Процесс создания формулы

Для завершения работы над созданием формулы необходимо добавить знак деления «/» (наклонную черту) и ввести вручную с клавиатуры значение 3. Промежуточный результат сравните с изображением рисунка 46. В случае обнаружения у себя ошибок в полученной формуле, помните о том, что в программе Microsoft Office Excel их можно исправить в любой момент, в качестве подсказки пользуйтесь цветовым выделением областей (границ ячеек) и ссылками на их адреса в редактируемой формуле. Для подтверждения правильности внесения компонентов в формулу, нажмите клавишу Enter, сравните результат с изображением рисунка 47.

Рисунок 46. Завершающий этап создания формулы

Рисунок 47. Результат создания формулы

Как видно из рисунка 47, после ввода формулы, в ячейке Е3 отобразился результат вычислений, то есть среднее арифметическое значение для чисел 5, 4, 4, являющихся значениями ячеек В3, С3, D3 соответственно. Заметьте, что результат является не целым числом, содержит два знака после запятой.

Рекомендуем Вам самостоятельно, не прибегая к использованию пошаговой инструкции, повторить ввод формул для ячеек: Е4, Е5, Е6, Е7, Е8 для отработки навыка создания формулы без использования встроенных стандартных функций.

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

Рисунок 48. Вызов контекстного меню для очистки содержимого ячеек

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

Рисунок 49. Отображение вкладки Формулы

Рисунок 50. Диалоговое окно Мастер функций

В поле Категория с помощью раскрывающегося списка пользователь может выбрать ту категорию, к которой относится искомая функция, либо отыскать ее, перебирая полный алфавитный перечень названий функций. Для нахождения нужной функции пользователь должен знать ее название, иначе поиск может оказаться долгим. В программу Microsoft Office Excel 2007 заложен достаточно большой объем функций различного назначения, сгруппированных по смысловым категориям в зависимости от разных параметров. Знать и помнить все стандартные функции (информацию о выполняемых действиях, символьные обозначения, перечень параметров) не представляется возможным в связи с огромных объемом справочной информации, которую для этого необходимо просто запоминать, поэтому рекомендуем ознакомиться с имеющимся набором функций и информацией по ним для дальнейшего более быстрого поиска нужной функции и ее нахождения. В справочной системе программы Microsoft Office Excel 2007, вызываемой нажатием клавиши F1, содержатся все необходимые сведения по стандартным функциям и их использовании, включая примеры, поэтому не считаем нужным рассматривать справочную информацию по всем функциям в данном пособии.

Возвращаясь к изображению рисунка 50, обращаем Ваше внимание на факт расположения названий функций в алфавитном порядке, что обеспечивает удобство и быстроту поиска в нашем примере стандартной функции нахождения среднего арифметического значения (функции СРЗНАЧ(число1; число2; …)). Воспользовавшись прокручивающимся списком, найдите искомую функцию (рисунок 51), выделите ее и нажмите кнопку ОК в диалоговом окне, в результате на экране появится следующее диалоговое окно (рисунок 52).

Рисунок 51. Выбор функции в диалоговом окне «Мастер функции»

В диалоговом окне «Аргументы функции» (рисунок 52) в качестве аргументов для нахождения среднего арифметического выступают: Число1, Число2, и т.д. В поле ввода элемента диалогового окна Число1 автоматически внесена ссылка на диапазон ячеек B3: D3, программа находит числовые значения слева и автоматически вносит их в поле. В данном случае программа правильно определила нужный диапазон ячеек, значения которых используются для подсчета среднего арифметического, но это происходит так не всегда. Например, если в таблице будут расположены числовые значения в ячейках, расположенных левее и выше, то диапазон может быть определен программой автоматически неверно, поэтому у пользователя всегда есть возможность указать нужный диапазон ячеек, и он будет внесен вместо обозначенного программой.

Рисунок 52. Диалоговое окно «Аргументы функции» при вставке функции СРЗНАЧ

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

Рисунок 53. Основные этапы процесса свертывания окна «Аргументы функции»

После того, как окно «Аргументы функции» примет «паркующийся» вид, как показано на втором по порядку изображении рисунка 53, выделите, используя известный уже способ, диапазон ячеек, для которых необходимо подсчитать среднее арифметическое значение, B3: D3, после появления в поле этого диапазона, щелкните левой кнопкой мышки по небольшой квадратной кнопочке. Это приведет к развертыванию диалогового окна «Аргументы функции», далее нажмите кнопочку ОК для подтверждения правильности выполненных действий.

В результате создания формулы вторым способом с помощью вставки стандартной функции СРЗНАЧ (число1; число2; …) в ячейке Е3 отобразится число 4, 33, как показано на рисунке 47.

Далее необходимо добавить расчетные формулы для определения среднего арифметического значения для ячеек: Е4, Е5, Е6, Е7, Е8, при этом в качестве аргументов выбираются соответствующие значения из строк с оценками по трем предметам. Конечно, можно для каждой из перечисленных ячеек заново создавать расчетную формулу либо первым рассмотренным в пособии способом, либо вторым с помощью вставки стандартной функции СРЗНАЧ (число1; число2; …), но, как уже упоминалось ранее, подобный путь ‑ не рационален, особенно, если представить ситуацию, в которой количество строк, для которых должны быть произведены вычисления по формуле, равно не пяти или шести, а, например, 200. Во избежание повторения одних и тех же действий (расчетов по одной и той же формуле, но с разными входными значениями), выполнения рутинных операций, в программе Microsoft Office Excel 2007 предусмотрены средства автоматизации вычислений, одним из которых является возможность распространения формулы на диапазон выделяемых ячеек, или, иначе говоря, копирование формул в смежные ячейки. Эта процедура отчасти напоминает процесс автозаполнения значениями, рассмотренный также ранее в данном пособии.

Для выполнения операции копирования формулы, содержащейся в ячейке Е3, в смежные ячейки: Е4, Е5, Е6, Е7, Е8, выделите Е3, установите указатель мышки на небольшой черный квадратик, расположенный в правом нижнем углу выделенной ячейки, нажмите левую кнопку мышки, и смещайте указатель вниз, удерживая кнопочку нажатой до тех пор, ока не окажется выделенным весь диапазон: Е4: Е8. В процессе Вы должны наблюдать выделенный пунктирной рамкой диапазон ячеек: Е4: Е8, в завершение процедуры копирования формулы (распространения формулы на диапазон ячеек), отпустите кнопку мышки, сравнивая результаты с рисунками 54, 55.

Рисунок 54. Процесс копирования формулы вниз

Рисунок 55. Результат копирования формулы вниз

Просмотрите формулы, полученные Вами в процессе копирования формулы ячейки Е3 (распространения на диапазон ячеек Е4: Е8), сравните их с далее приведенной таблицей. Для просмотра функции щелкните по интересующей Вас ячейке и наблюдайте результат в строке формул.

Обратите внимание на то, как именно изменились адреса ячеек при копировании формул. В нашем примере копирование формулы осуществлялось в направлении вниз, каждый номер строки при этом увеличивался в формуле этой строки на единицу, а названия столбцов в формулах остались неизменными. При изменении исходных значений (оценок студентов), программа Microsoft Office Excel 2007 автоматически осуществляет пересчет, что достаточно быстро отражается в результирующих ячейках (диапазоне Е4: Е8).