Microsof tExcel Ввод формул

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

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

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

Урок 1
Начало работы

Ввод формул
Начало работы
Представьте, что программа Microsoft Excel открыта и в ней обрабатываются расходы семейного бюджета по статье «Развлечения».
Ячейка C6 пуста; сумма, потраченная на компакт-диски в феврале не введена.
В ходе этого урока будет рассказано, как с помощью Excel выполнять основные математические действия, вводя простые формулы в ячейки. Вы также научитесь суммировать все значения в столбце с помощью формулы, которая пересчитывает результат при изменении значений.

Ввод формул
Начните со знака «равно»
Два компакт-диска, купленных
в феврале, стоили 12,99 и 16,99 рублей.
Их суммарная стоимость равна расходам на компакт-диски в этом месяце.
Эти значения можно сложить в Excel путем ввода простой формулы в ячейку C6.

Ввод формул
Начните со знака «равно»
На рисунке показан порядок действий.
Введите формулу в ячейку C6. Формулы Excel всегда начинаются со знака «равно». Чтобы сложить 12,99 и 16,99, введите:
=12,99+16,99
Знак «плюс» (+) является математическим оператором, который указывает Excel, что необходимо сложить значения.

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

Ввод формул
Использование других математических операторов
Для составления формул,
в которых присутствует не только операция сложения, используются другие математические операторы.
В Microsoft Excel для написания формул используются общеупотребительные знаки.
Как показано в таблице, для вычисления разности используется знак «минус» (-), для вычисления произведения — знак «звездочка» (*), для вычисления частного — знак «косая черта» (/).
Помните, что формулы в Microsoft Excel всегда начинаются со знака «равно» (=).

Ввод формул
Суммирование значений в столбце
Чтобы просуммировать все расходы за январь, не требуется вводить все значения заново.
Используйте встроенную формулу, которая называется функция.
На вкладке «Главная» нажмите кнопку «Сумма» в группе Правка на ленте.
Чтобы получить общие расходы за январь, выберите ячейку B7, а затем:
Ячейки будут обведены цветовым индикатором, а
в ячейке B7 будет выведена формула.

Ввод формул
Суммирование значений по столбцу
Чтобы просуммировать все расходы за январь, не требуется вводить все значения заново.
Используйте встроенную формулу, которая называется функция.
Нажмите клавишу ВВОД, чтобы отобразить результат в ячейке В7: 95,94.
Чтобы получить общие расходы за январь, выберите ячейку B7, а затем:
Щелкните в ячейке B7, чтобы отобразить формулу =СУММ(B3:B6) в строке формул.

Ввод формул
Суммирование значений в столбце
Знаки «B3:B6» называются аргументом, аргументы сообщают функции «СУММ», какие значения требуется суммировать.
Использование ссылки на ячейки (B3:B6) вместо самих значений
в ячейках позволяет автоматически обновлять результат при изменении значений в ячейках.
Двоеточие (:) между B3 и B6 обозначает диапазон ячеек в столбце B: строки с третьей по шестую. Круглые скобки отделяют аргумент от функции.

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

Ввод формул
Копирование формулы вместо создания новой
Сначала выберите ячейку B7.
Затем, как показано на рисунке:
Затем наведите указатель мыши на правый нижний угол ячейки, чтобы он принял вид черного креста (+).
Перетащите маркер заполнения из ячейки B7 в ячейку C7 и отпустите его. В в ячейке C7 появится сумма расходов за февраль—126,93.
Формула =СУММ(C3:C6) также будет выведена
в строке формул над листом.

Ввод формул
Копирование формулы вместо создания новой
Сначала выберите ячейку B7.
Затем, как показано на рисунке:
Затем наведите указатель мыши на правый нижний угол ячейки, чтобы он принял вид черного креста (+).
Появится кнопка параметров автозаполнения, которая позволяет использовать некоторые параметры форматирования. В данном случае форматирование не нужно, поэтому никаких действий не требуется. Кнопка будет скрыта при вводе данных в любую ячейку.

Ввод формул
Упражнения для практического занятия
Создание формулы для суммирования
Создание других формул
Суммирование чисел в столбце.
Суммирование чисел в строке.
Интерактивное практическое задание (требуется Excel 2007)

Ввод формул
Тест 1, вопрос 1
Что надо набрать в пустой ячейке, чтобы начать ввод формулы? (Выберите один ответ.)
*
(
=

Ввод формул
Тест 1, вопрос 1, ответ:
=
Начало формулы в Microsoft Excel обозначается знаком равенства (=).

Ввод формул
Тест 1, вопрос 2
Что такое функция? (Выберите один ответ.)
Заранее определенная формула.
Математический оператор

Ввод формул
Тест 1, вопрос 2, ответ:
Заранее определенная формула.
Функции — это заранее определенные формулы (например, «СУММ»), которые упрощают работу.

Ввод формул
Тест 1, вопрос 3
Результат формулы отображается в ячейке C6. Вы хотите узнать, как получен этот результат. Что необходимо сделать, чтобы увидеть формулу? (Выберите один ответ.)
Щелкнуть ячейку C6 и нажать клавиши CTRL+SHIFT.
Щелкнуть ячейку C6 и нажать клавишу F5.
Щелкнуть ячейку C6.

Ввод формул
Тест 1, вопрос 3, ответ:
Щелкнуть ячейку C6.
Все очень просто! Формула отображается в строке формул
в верхней части листа при каждом выделении ячейки C6. Кроме того, можно дважды щелкнуть ячейку C6, чтобы увидеть формулу в ячейке C6. Затем нажмите клавишу ВВОД, чтобы снова отобразить результат формулы в ячейке.

Урок 2
Использование ссылок на ячейки

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

Ввод формул
Обновление результатов вычислений
Предположим, что значение 11,97 в ячейке C4 было неправильным. Не было учтено 3,99 руб. за прокат видеокассет.
Программа Microsoft Excel автоматически обновляет значение суммы при изменении суммируемых значений.
Чтобы сложить 3,99 и 11,97, выделите ячейку C4 и введите следующую формулу, а затем нажмите клавишу ВВОД:
=11,97+3,99

Ввод формул
Обновление результатов вычислений
На рисунке показано, что при изменении значения в ячейке C4 сумма за февраль в ячейке C7 изменяется с 126,93 на 130,92.
Это происходит потому, что формула =СУММ(C3:C6) в ячейке C7 содержит ссылки на ячейки.
Если использовать в формуле ячейки C7 конкретные значения, например 11,97, сумма не будет обновлена. Потребуется изменить 11,97 на 15,96 не только в ячейке C4, но и в формуле в ячейке C7.

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

Ввод формул
Другие способы ввода ссылки на ячейку
Например, требуется узнать сумму, потраченную в феврале на прокат видеокассет и компакт-дисков.
В ячейке C9 введите знак равенства, введите СУММи открывающую круглую скобку.
Щелкните ячейку C4. Ссылка на ячейку C4 появится в ячейке C9. Введите запятую после ссылки в ячейке C9.
В этом примере рассматривается ввод формулы в ячейку C9 путем щелчка ячейки.

Ввод формул
Другие способы ввода ссылку на ячейку
Допустим, что требуется узнать сумму, потраченную в феврале на прокат видеокассет и компакт-дисков.
Щелкните ячейку C6. Ссылка на эту ячейку с запятой после ссылки появится в ячейке C9. Введите после запятой закрывающую круглую скобку.
В этом примере рассматривается ввод формулы в ячейку C9 путем щелчка ячейки.
Нажмите клавишу ВВОД, чтобы отобразить результат вычислений по формуле, 45,94.
Цветовой индикатор, отмечающий выбранные ячейки, будет скрыт при нажатии клавиши ВВОД для отображения результата.

Ввод формул
Другие способы ввода ссылки на ячейку
Немного подробнее о том, как эта формула работает.
Аргументы C4 и C6 определяют суммируемые функцией «СУММ» значения. Круглые скобки отделяют аргументы от функции.
Запятая, наличие которой обязательно, разделяет аргументы.

Ввод формул
Типы ссылок
Теперь, когда ссылки на ячейки изучены более подробно, следует обсудить типы ссылок.
На рисунке показаны два типа ссылок, относительные и абсолютные.
Относительные ссылки автоматически изменяются при их копировании по столбцу или по строке.
В примере на рисунке при копировании формулы =C4*$D$9 из строки в строку относительная ссылка на ячейку изменяется с C4 на C5 и C6.

Ввод формул
Типы ссылок
Теперь, когда ссылки на ячейки изучены более подробно, следует обсудить типы ссылок.
На рисунке показаны два типа ссылок, относительные и абсолютные.
Абсолютные ссылки постоянны. Они не изменяются при копировании формулы из одной ячейки в другую. Абсолютные ссылки обозначаются знаком доллара ($), например $D$9.
На рисунке показано, что при копировании формулы =C4*$D$9 из строки в строку абсолютная ссылка на ячейку $D$9 не изменяется.

Ввод формул
Типы ссылок
Имеется еще один тип ссылки на ячейку.
Например, ссылка $A1 является абсолютной по столбцу A и относительной по строке 1.
При копировании смешанной ссылки из ячейки в ячейку ее относительная ссылка меняется, а абсолютная остается неизменной.
Смешанная ссылка является абсолютной по столбцу и относительной по строке или относительной по столбцу и абсолютной по строке.

Ввод формул
Использование абсолютных ссылок на ячейки
Абсолютные ссылки используются
в случаях, когда при копировании формулы требуется сохранить ссылку неизменной.
По умолчанию ссылки являются относительными. Чтобы изменить тип ссылки на абсолютную, требуется ввести знак доллара ($), как это показано в примере под цифрой 2.

Ввод формул
Использование абсолютных ссылок на ячейки
Допустим, вы получили несколько купонов
с семипроцентной скидкой на прокат видеокассет и компакт-дисков. Какую сумму можно сэкономить за месяц, используя скидки?
Для вычисления 7 процентов от февральских расходов можно использовать формулу.
Начните с ввода размера скидки 0,07 в пустую ячейку D9, а затем введите формулу в ячейку D4.

Ввод формул
Использование абсолютных ссылок на ячейки
Допустим, вы получили несколько купонов
с семипроцентной скидкой на прокат видеокассет и компакт-дисков. Какую сумму можно сэкономить за месяц, используя скидки?
Затем в ячейке D4 введите =C4*. Помните, что эта относительная ссылка на ячейку меняется от строки к строке.
Введите знак доллара ($) и D, чтобы сделать абсолютной ссылку на столбец D, и $9 чтобы сделать абсолютной ссылку на строку 9. Результатом вычислений по формуле будет произведение значений ячеек C4 и D9.

Ввод формул
Использование абсолютных ссылок на ячейки
Допустим, вы получили несколько купонов
с семипроцентной скидкой на прокат видеокассет и компакт-дисков. Какую сумму можно сэкономить за месяц, используя скидки?
В ячейке D9 хранится значение семипроцентной скидки.
С помощью маркера заполнения можно скопировать формулу из ячейки D4 в ячейку D5. При копировании формулы относительная ссылка изменится с C4 на C5, а абсолютная ссылка на размер скидки в ячейке D9 останется неизменной ($D$9).

Ввод формул
Упражнения для практического занятия
Ввод ссылок на ячейки в формулу.
Выбор ссылок на ячейки для формулы.
Использование абсолютной ссылки в формуле.
Сложение нескольких результатов.
Изменение значений и суммы.
Интерактивное практическое задание (требуется Excel 2007)

Ввод формул
Тест 2, вопрос 1
Как работает абсолютная ссылка на ячейку? (Выберите один ответ.)
Ссылка на ячейку автоматически изменяется, когда формула копируется по столбцу или по строке.
Ссылка на ячейку имеет постоянное значение.
В ссылке на ячейку используется стиль ссылок A1.

Ввод формул
Тест 2, вопрос 1, ответ:
Ссылка на ячейку имеет постоянное значение.
Абсолютные ссылки на ячейки не изменяются при копировании формулы из ячейки в ячейку.

Ввод формул
Тест 2, вопрос 2
Какая из этих ссылок является абсолютной? (Выберите один ответ.)
B4:B12
$A$1

Ввод формул
Тест 2, вопрос 2, ответ:
$A$1
Знак доллара обозначает абсолютную ссылку на столбец A и строку 1, неизменную при копировании.

Ввод формул
Тест 2, вопрос 3
Какая формула будет в ячейке C5 при копировании формулы =C4*$D$9 из ячейки C4 в ячейку C5? (Выберите один ответ.)
=C5*$D$9
=C4*$D$9
=C5*$E$10

Ввод формул
Тест 2, вопрос 3, ответ:
=C5*$D$9
При копировании формулы относительная ссылка изменится с C4 на C5. Абсолютная ссылка, $D$9, не изменится; она остается одинаковой во всех строках, в которые она копируется.

Урок 3
Упрощение формул с помощью функций

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

Ввод формул
Нахождение среднего
Функцию «СРЗНАЧ» можно использовать для вычисления средних расходов на развлечения за январь и февраль.
Формула будет введена приложением Excel.
Щелкните ячейку D7, а затем:
На вкладке Главная в группе Правка щелкните стрелку на кнопке «Сумма» , затем щелкните «Среднее» в списке.
Нажмите кнопку ВВОД, чтобы отобразить результат в ячейке D7.

Ввод формул
Нахождение наибольших и наименьших значений
Функция «МАКС» используется для нахождения наибольшего значения в некотором диапазоне, функция «МИН» — для нахождения наименьшего.
На рисунке показано использование функции «МАКС».
Щелкните ячейку F7. Затем:
на вкладке Главная в группе Правка щелкните стрелку на кнопке «Сумма», затем щелкните «Максимум» в списке.
Нажмите клавишу ВВОД, чтобы отобразить результат вычислений по формуле в ячейке F7. Наибольшее значение в последовательности — 131,95.

Ввод формул
Нахождение наибольшего и наименьшего значения
Функция «МАКС» используется для нахождения наибольшего значения в некотором диапазоне, функция «МИН» — для нахождения наименьшего.
На рисунке показано использование функции МАКС.
Чтобы найти наименьшее значение диапазона, выберите пункт Минимум в списке и нажмите клавишу ВВОД.
Наименьшее значение в последовательности — 131,75.

Ввод формул
Печать формул
Формулы можно распечатать, чтобы можно было легко вспомнить, как их создавать.
Но сначала формулы требуется отобразить на листе.
Ознакомьтесь с тем, как это делается:
Нажмите «Формулы».
В группе «Зависимости формул» нажмите кнопку «Показать формулы» .

Ввод формул
Печать формул
Формулы можно распечатать, чтобы можно было легко вспомнить, как их создавать.
Но сначала формулы требуется отобразить на листе.
Ознакомьтесь с тем, как это делается:
Нажмите кнопку «Office» в верхнемлевом углу окна Excel и выберите «Печать».
Совет.Можно также использовать клавиши CTRL+`, чтобы отобразить и скрыть формулы.

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

Ввод формул
Непонятные знаки на листе
Программа Microsoft Excel не может произвести вычисления по формуле, если формула содержит ошибки.
В этом случае в ячейке вместо результата будет отображено значение ошибки.
#ССЫЛКА! Ссылка на ячейку неверна. Возможно, ячейки были удалены или вставлены поверх.
Наиболее часто встречаются три значения ошибки.
#ИМЯ? Опечатка в имени функции или использование имени, неизвестного программе Microsoft Excel.

Ввод формул
Использование дополнительных функций
В Microsoft Excel встроено множество полезных функций, например функции для работы с датой или временем и функции для обработки текста.
Нажмите кнопку «Сумма» в группе «Правка» на вкладке «Главная».
Выберите «Другие функции» в списке.
В диалоговом окне «Вставка функции», которое будет открыто, можно выполнить поиск функции.
Чтобы увидеть все другие функции:

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

Ввод формул
Упражнения для практического занятия
Нахождение среднего.
Нахождение наибольшего значения.
Нахождение наименьшего значения.
Отображение и сокрытие формул
Создание и исправление значений ошибок.
Создание и исправление значения ошибки #ИМЯ?.
Интерактивное практическое задание (требуется Excel 2007)

Ввод формул
Тест 3, вопрос 1
Как распечатать формулы? (Выберите один ответ.)
Нажать кнопку Office и выбрать команду Печать.
Выбрать Обычный на вкладке Вид вверху экрана, нажать кнопку Office и выбрать команду Печать.
В группе Зависимости формул на вкладке Формулы выбрать Показать формулы; затем нажать кнопку «Office» и выбрать Печать.

Ввод формул
Тест 3, вопрос 1, ответ:
В группе Зависимости формул на вкладке Формулы выбрать Показать формулы; затем нажать кнопку Office и выбрать Печать.
Выбор пункта показать формулы отображает формулы на листе перед печатью.

Ввод формул
Тест 3, вопрос 2
Что означает «####»?
(Выберите один ответ.)
Столбец слишком узок для отображения содержимого ячейки.
Ссылка на ячейку неверна.
Опечатка в имени функции или использование имени, неизвестного программе Microsoft Excel.

Ввод формул
Тест 3, вопрос 2, ответ:
Столбец недостаточно широк для отображения содержимого ячейки.
Для отображения содержимого можно увеличить ширину столбца.

Ввод формул
Тест 3, вопрос 3
Какое сочетание клавиш используется для отображения формул на листе? (Выберите один ответ.)
CTRL+`
CTRL+:
CTRL+;

Ввод формул
Тест 3, вопрос 3, ответ:
CTRL+`
Клавиша «`» на большинстве клавиатур расположена рядом с клавишей «1». Это сочетание клавиш используется для отображения и скрытия формул.

Ввод формул
Краткий справочник
Сводку практических заданий этого учебного курса см. в кратком справочнике.