Как создать формулу в microsoft office excel 2007

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

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

Формулы в Excel для чайников

Чтобы задать формулу для ячейки, необходимо активизировать ее (поставить курсор) и ввести равно (=). Так же можно вводить знак равенства в строку формул. После введения формулы нажать Enter. В ячейке появится результат вычислений.

В Excel применяются стандартные математические операторы:

Символ «*» используется обязательно при умножении. Опускать его, как принято во время письменных арифметических вычислений, недопустимо. То есть запись (2+3)5 Excel не поймет.

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

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

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

Ссылки можно комбинировать в рамках одной формулы с простыми числами.

Оператор умножил значение ячейки В2 на 0,5. Чтобы ввести в формулу ссылку на ячейку, достаточно щелкнуть по этой ячейке.

В нашем примере:

1. Поставили курсор в ячейку В3 и ввели =.
2. Щелкнули по ячейке В2 – Excel «обозначил» ее (имя ячейки появилось в формуле, вокруг ячейки образовался «мелькающий» прямоугольник).
3. Ввели знак *, значение 0,5 с клавиатуры и нажали ВВОД.

Если в одной формуле применяется несколько операторов, то программа обработает их в следующей последовательности:

• %, ^;
• *, /;
• +, -.

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



Как в формуле Excel обозначить постоянную ячейку

Различают два вида ссылок на ячейки: относительные и абсолютные. При копировании формулы эти ссылки ведут себя по-разному: относительные изменяются, абсолютные остаются постоянными.

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

1. Вручную заполним первые графы учебной таблицы. У нас – такой вариант:



2. Вспомним из математики: чтобы найти стоимость нескольких единиц товара, нужно цену за 1 единицу умножить на количество. Для вычисления стоимости введем формулу в ячейку D2: = цена за единицу * количество. Константы формулы – ссылки на ячейки с соответствующими значениями.



3. Нажимаем ВВОД – программа отображает значение умножения. Те же манипуляции необходимо произвести для всех ячеек. Как в Excel задать формулу для столбца: копируем формулу из первой ячейки в другие строки. Относительные ссылки – в помощь.

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

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

Ссылки в ячейке соотнесены со строкой.

Формула с абсолютной ссылкой ссылается на одну и ту же ячейку. То есть при автозаполнении или копировании константа остается неизменной (или постоянной).

Чтобы указать Excel на абсолютную ссылку, пользователю необходимо поставить знак доллара ($). Проще всего это сделать с помощью клавиши F4.

1. Создадим строку «Итого». Найдем общую стоимость всех товаров. Выделяем числовые значения столбца «Стоимость» плюс еще одну ячейку. Это диапазон D2:D9



2. Воспользуемся функцией автозаполнения. Кнопка находится на вкладке «Главная» в группе инструментов «Редактирование».



3. После нажатия на значок «Сумма» (или комбинации клавиш ALT+«=») слаживаются выделенные числа и отображается результат в пустой ячейке.

Сделаем еще один столбец, где рассчитаем долю каждого товара в общей стоимости. Для этого нужно:

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



2. Чтобы получить проценты в Excel, не обязательно умножать частное на 100. Выделяем ячейку с результатом и нажимаем «Процентный формат». Или нажимаем комбинацию горячих клавиш: CTRL+SHIFT+5



3. Копируем формулу на весь столбец: меняется только первое значение в формуле (относительная ссылка). Второе (абсолютная ссылка) остается прежним. Проверим правильность вычислений – найдем итог. 100%. Все правильно.

При создании формул используются следующие форматы абсолютных ссылок:

• $В$2 – при копировании остаются постоянными столбец и строка;
• B$2 – при копировании неизменна строка;
• $B2 – столбец не изменяется.

Как составить таблицу в Excel с формулами

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

Простейшие формулы заполнения таблиц в Excel:

1. Перед наименованиями товаров вставим еще один столбец. Выделяем любую ячейку в первой графе, щелкаем правой кнопкой мыши. Нажимаем «Вставить». Или жмем сначала комбинацию клавиш: CTRL+ПРОБЕЛ, чтобы выделить весь столбец листа. А потом комбинация: CTRL+SHIFT+»=», чтобы вставить столбец.
2. Назовем новую графу «№ п/п». Вводим в первую ячейку «1», во вторую – «2». Выделяем первые две ячейки – «цепляем» левой кнопкой мыши маркер автозаполнения – тянем вниз.



3. По такому же принципу можно заполнить, например, даты. Если промежутки между ними одинаковые – день, месяц, год. Введем в первую ячейку «окт.15», во вторую – «ноя.15». Выделим первые две ячейки и «протянем» за маркер вниз.



4. Найдем среднюю цену товаров. Выделяем столбец с ценами + еще одну ячейку. Открываем меню кнопки «Сумма» — выбираем формулу для автоматического расчета среднего значения.

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

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

Что такое формула?

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

Проще говоря, именно с их помощью вы можете рассчитать значение заданной ячейки.

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

Первые шаги

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

Для примера, мы имеем две колонки случайных чисел. Итак:

1) Выделите ячейку, значение которой хотите рассчитать.

2) Ввод формулы всегда начинается со знака «=» – он указывает программе, что вводится не просто текст. Вы можете вводить ее прямо в поле, либо в верхнюю строку.

Например, мы вручную ввели формулу в Е11.

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

3) Она будет работать без изменений, даже если вы измените данные в одной из единиц, участвующих в вычислении.

Из примера видно, что значение А3 было изменено, поэтому и Е11 тоже изменилась. Происходит это, логично, благодаря формуле, которую мы задали для Е11.

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

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

Копирование

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

1) Суммируем значения конкретных ячеек. В моем примере это А1 и В1.

2) Получаем результат (С1).

3) Наводим курсор на нижний правый угол ячейки и ждем появления черного крестика («плюсика»).

4) Протаскиваем крестик вниз, выделяя необходимое нам поле. Заданные параметры применяются автоматически.

Функции

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

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

Найти необходимую функцию можно несколькими способами:

• найти в категориях библиотеки в верхнем меню;

• при помощи команды «вставить функцию»;

• ручной ввод.

Команда «Вставить функцию» – это наиболее часто используемый способ, но это вовсе не значит, что другие неприемлемы по каким-либо причинам. Все зависит от того, что конкретно вам покажется удобнее.

На нашем примере будет использоваться команда «Вставить функцию»:

1) При нажатии команды, всплывает диалоговое окно. В строке «Категория» необходимо указать категорию, к которой относится необходимая вам функция. Если вы не знаете категорию, укажите «Полный алфавитный перечень», чтобы отобразился полный список существующих в программе вариантов. Найти необходимый не составит никакого труда – в этом случае, они указаны в алфавитном порядке.

2) После указания параметра поиска, отображается список. Ищите необходимую и нажмите «ОК».

3) Появляется новое диалоговое окно. В строке «Число 1» указан диапазон суммирования. Значение, которое получится в результате этого действия, также указано в диалоговом окне. Для того чтобы сумма отобразилась в ячейке, просто нажмите «ОК».

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

Остальные два способа вызова отличаются от этого незначительно.

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

Другой популярной функцией в Excel является СРЗНАЧ. Нетрудно догадаться, она необходима для расчета среднего значения выбранных вами ячеек.

На этом примере, воспользуемся быстрой кнопкой в меню «Главная».

1) Выделяем ячейку, значение которой рассчитываем с помощью СРЗНАЧ. У меня это В11.

2) Нажимаем стрелку выпадающего меню «Автосумма» и выбираем функцию «Среднее».

3) В контуре «крутящихся муравьев» автоматически выделен весь диапазон колонки. Меняем его, для разнообразия. Теперь высчитывается среднее значение всего пяти ячеек.

4) Нажимаем «Enter» для отображения результата.

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

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

Перейдем
к созданию расчетных формул и внесению
их в соответствующие ячейки. Формулы
представляют собой выражения, по которым
выполняются вычисления на странице.
Формула начинается со знака равенства
(=), может также содержать такие
элементы, как функции. Функцию в программе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).

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

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

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

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

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

Далее
для подсчета среднего балла группы по
трем предметам рекомендуем Вам
воспользоваться снова способом вставки
готовой стандартной функции СРЗНАЧ
(число1; число2; …) и внесения ее в
ячейкуЕ9, выбрав в качестве аргументов
значения ячеек диапазонаЕ3:Е8.
Выделите указанный диапазон известным
способом, перейдите на вкладкуГлавная,
в группе командРедактированиеподведите указатель к инструменту
(команде) с изображением значкаавтосуммы(∑), как показано нарисунках 56,
57, щелкните по небольшой кнопочке с
изображением треугольника, как показано
нарисунке 58.

Рисунок 56.
Переход к группе команд «Редактирование»
на вкладке Главная

В отображенном меню выбрав пункт Среднее,
щелкните один раз левой кнопкой мышки,
в результате, если выполнено правильно,
в ячейкеЕ9появится найденное
значение среднего арифметического из
выделенного предварительно диапазона,
формула имеет вид:=СРЗНАЧ(E3:E8).
Следует отметить, что применительно к
нашему примеру такой способ вставки
формулы оказался более рациональным.

На
следующем этапе подготовки электронной
таблицы задания образца необходимо
добавить в ячейку Е10формулу,
определяющую максимальное значение из
всех средних баллов, причем, это
максимальное значение должно быть целым
числом, не содержащим знаки после
запятой. Добавим в ячейкуЕ10формулу,
определяющую максимальное значение.
Выделив диапазонЕ3:Е8, перейдем на
вкладкеГлавнаяк командеАвтосуммаи выберем в подменю функциюМаксимум,
в результате полученное значение не
будет являться целым число, будут
показаны знаки после запятой, как нарисунке 59, формула ячейки Е9 имеет
вид: =МАКС(E3:E8).

Рисунок 59.
Результат нахождения максимального
значения

В
рассмотренных формулах: =МАКС(E3:E8),
=СРЗНАЧ(E3:E8)словаМАКСиСРЗНАЧявляются специальными зарезервированными
служебными словами, воспринимаемыми
программойMicrosoft
Office Excel
2007как определенные слова, указывающие
на использование функций соответственно
нахождения максимального и среднего,
а параметры, перечисляемые в круглых
скобках, ‑ это диапазоны ячеек,
выступающие в качестве аргументов этих
функций.

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

Рекомендуем
в этом случае прибегнуть к использованию
функции ОКРУГЛ(число;число_разрядов),
округляющей число до указанного
количества десятичных знаков, гдеОКРУГЛ‑ служебное слово,число‑
округляемое число или ссылка на ячейку
или другая функция, результатом которой
является опять же число или ссылка
(адрес) на ячейку с числом,число_разрядов– количество десятичных разрядов, до
которого требуется округлить число. В
нашем примеречисломбудетмаксимальное
значениедля диапазонаЕ3:Е8,число_разрядов примем равным0,
то есть округление до целых.

В
рассматриваемом примере необходимо
округлить найденное максимальное
значение, то есть в качестве округляемого
числа для функции = ОКРУГЛ (число;
число_разрядов) будет использоваться
функция = МАКС (число1; число2; …),
в итоге формула примет вид: =ОКРУГЛ
(МАКС (E3:E8); 0), в которой округляется
максимальное значение чисел из диапазона
E3:E8 до целых. Попробуйте внести вручную
формулу в ячейкуЕ10, учитывая, что
адреса (ссылки на ячейки) записываются
английским буквами, между символами,
используемыми в формуле, пробелы не
ставятся. Процесс создания формулы и
результат сравните с изображениямирисунков 60 и 61.

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

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

В
примере нахождения округленного
максимального значения одна функция
вложена в другую. Аналогично внесем
формулу в ячейку Е11для нахождения
округленного минимального значения,
формула должна иметь вид:=ОКРУГЛ(МИН(E3:E8);0).
Процесс создания формулы и результат
сравните с изображениямирисунков 62
и 63.

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

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

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

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

Окно вставки функции

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

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

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

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

4. После вставки функции в ячейку она отобразится в стандартном виде и все еще будет доступна для редактирования.

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

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

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

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

Ручная вставка формулы в Excel

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

Для начала записи выделите ячейку и обязательно поставьте знак =, после чего начните вписывать название формулы и выберите ее из списка.

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

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

Вставка математических формул

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

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

На экране появится редактор и блок формулы. Его используйте для перемещения, а сам редактор – для того, чтобы заносить в формулу числа и редактировать ее под себя. Учитывайте, что в этом случае не работают никакие проверки, поэтому правильность написания проверять придется собственноручно.

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

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

Excel позволяет пользователю создавать формулы разными способами:

• ввод вручную;
• применение встроенных функций.

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

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

Ручное создание формул Excel

Ввод вручную применим, когда необходимо осуществить простые расчеты (сложение, вычитание, умножение, деление) небольшого количества данных. Чтобы ввести ее следует выполнить следующие шаги:

1. щелчком левой кнопки мыши выделяем ячейку, где будет отображаться результат;
2. нажимаем знак равенства на клавиатуре;
3. вводим выражение;
4. нажимаем Enter.

Между операндами ставят соответствующий знак: +, -, *, /. Легче всего их найти на дополнительной цифровой клавиатуре.

Использование функций Майкрософт Эксель

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

Для выбора требуемой функции нужно нажать на кнопку fx в строке состояния или (если вы работаете в 2007 excel) на треугольник, расположенный около значка автосуммы, выбрав пункт меню «Другие функции».

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

Категории функций Эксель

Функции, встроенные в Excel, сгруппированы в несколько категорий:

1. 1. Финансовые позволяют производить вычисления, используемые в экономических расчетах, связанных обычно с ценными бумагами, начислением процентов, амортизацией и другими показателями;
   2. Дата и время. Эти функции позволяют работать с временными данными, например, можно вычислить день недели для определенной даты;
   3. Математические позволяют произвести расчеты, имеющие отношения к различным областям математики;
   4. Статистические позволяют определить различные категории статистики – дисперсию, вероятность, доверительный интервал и другие;

1. Для обработки ссылок и массивов;
2. Для работы с базой данных;
3. Текстовые используются для проведения действия над текстовой информацией;
4. Логические позволяют установить условия, при которых следует выполнить то или иное действие;
5. Функции проверки свойств и значений.

Правила записи функций Excel

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

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

Для разделения аргументов используется знак «;». Если для вычисления используется массив данных, начало и конец его разделяются двоеточием.

Редактирование формул Microsoft Excel

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

• кликнуть в строке состояния;
• нажать на клавиатуре F2;
• либо два раза щелкнуть мышью по ячейке. (как вам удобнее)

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

Ошибки в формулах Excel

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

• ### – ширины столбца недостаточно для отображения результата;
• #ЗНАЧ! – использован недопустимый аргумент;
• #ДЕЛ/0 – попытка разделить на ноль;

• #ИМЯ? – программе не удалось распознать имя, которое было применено в выражении;
• #Н/Д – значение в процессе расчета было недоступно;
• #ССЫЛКА! – неверно указана ссылка на ячейку;
• #ЧИСЛО! – неверные числовые значения.

Копирование формул Excel

В случае необходимости формулу можно внести в несколько ячеек. Для этого можно использовать несколько способов:

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

Абсолютные и относительные ссылки в Эксель

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

• при вертикальном копировании в ссылке изменяется номер строки;
• при горизонтальном перенесении изменяется номер столбца.

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

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

Закрепить какую-либо ячейку можно, используя знак $ перед номером столбца и строки в выражении для расчета: $F$4. Если поступить таким образом, при копировании номер ячейки останется неизменным.

Относительные ссылки

Абсолютные ссылки

Имена в формулах Эксель

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

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

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

excel 2007

excel 2010

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

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

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

Помогла ли Вам статья?