Введение формул в ячейки для расчетов данных

Занятие 6.6

Табличный редактор Excel.

Работа с формулами

ВВЕДЕНИЕ ФОРМУЛ В ЯЧЕЙКИ ДЛЯ РАСЧЕТОВ ДАННЫХ

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

Ввод формулы вручную

Задание. В ячейки A1, A1 и A3 введите соответственно числа 1, 2 и 3. В A4 просуммируйте их.

Сначала рассмотрим ручной способ ввода:

В ячейку A4 введите следующую формулу: =A1+A2+A3 и нажмите клавишу «Ввод» (Enter).

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

Обратите внимание, адреса ячеек подсвечены разными цветами.

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

Внимание. Вычислительная функция формул является динамической. Например, если мы изменим значение ячейки A1 на 3, то сумма автоматически изменится на 8.

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

Ввод формулы с помощью мышки

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

1. Перейдите в ячейку A4 и введите символ «=». Таким образом, вы указываете, что следующим значением является формула или функция.

2. Щелкните по ячейке A1 и введите знак «+».

3. Сделайте щелчок по ячейке A2 и снова нажмите клавишу «+».

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

Ввод формулы с помощью стрелок клавиатуры

Вводить адреса ячеек в формулы можно и с помощью клавиш управления курсором клавиатуры (стрелками).

1. Так как любая формула начинается из знака равенства, в A4 введите «=».

2. Нажмите 3 раза клавишу на клавиатуре «стрелка вверх» и курсор сместится на ячейку A1. А ее адрес будет автоматически введен в A4. После чего жмем «+».

3. Соответственно 2 раза нажимаем «стрелку вверх» и получаем ссылку на A2. Затем жнем «+».

4. Теперь нажимаем только один раз клавишу «стрелка вверх», а затем Enter для ввода данных ячейку.

Управление ссылками в формулах стрелками клавиатуры немного напоминает предыдущий способ.

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

Заполнение листов формулами

В таблице (которая отображена ниже на картинке) необходимо посчитать суму, надлежащую к выплате учитывая 12% премиальных к ежемесячному окладу.

Задание 1. В ячейке F2 введите следующую формулу следующим образом: =D2+D2*E2. После ввода нажмите «Enter».

Задание 2. В ячейке F2 введите только знак «=». После чего сделайте щелчок по ячейке D2, дальше нажмите «+», потом еще раз щелчок по D2, дальше введите «*», и щелчок по ячейке E2. После нажатия клавиши «Enter» получаем аналогичный результат.

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

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

По умолчанию в ячейках с формулами отображается результат их вычисления. При просмотре сразу не определишь где ячейки с формулами, а где с входящими данными для расчетов. Поэтому иногда удобно использовать комбинацию горячих клавиш CTRL+~ (клавиша ~ находится перед клавишей с единичкой «1») для переключения в режим просмотра формул. Повторное нажатие данной комбинации вернет в режим отображения результатов вычисления формул.

Все формулы пересчитываются динамически. Как только изменяется содержание ячейки с входящими данными для расчетов, формулы автоматически пересчитывают их и сразу выдают новый результат вычислений. Например, если сейчас изменить ежемесячный оклад в ячейке D2 и нажать «Enter», то по адресу E2 сразу появится новый результат.

ФУНКЦИЯ СУММ И ПРИМЕРЫ ЕЕ ИСПОЛЬЗОВАНИЯ

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

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

Вычисление суммы по столбцу

Просуммируем значение ячеек A1, A2 и A3 с помощью функции суммирования.

1. После ввода чисел перейдите на ячейку A4. На закладке инструментов «Главная» выберите инструмент «Сумма» в разделе «Редактирование» (или нажмите комбинацию горячих клавиш ALT+=).

2. Диапазон ячеек распознан автоматически. Адреса ссылок уже введенные в параметры (A1:A3). Пользователю остается только нажать Enter.

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

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

Вычисление объема и площади

В ячейке A1 запишем формулу вычисления объема параллелепипеда: a = 6 см; b = 8 см; c = 12 см.

В ячейке A2 запишем формулу вычисления площади круга: r = 25 см.

В ячейке A3 формула содержит безаргументную функцию ПИ(), которая содержит в себе полное число ПИ (а не 3.14). Поэтому значения ячеек A2 и A3 немного отличаются.

Ссылка на лист в формуле

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

Задача: сделать ссылку на другой лист книги, для реализации данной задачи делаем следующее:

1. Заполните Лист1, Лист2 и Лист3 так как показано выше на рисунке.

2. Перейдите на Лист4, ячейка B2.

3. Поставьте знак «=» и перейдите на Лист1 чтобы там щелкнуть левой клавишей мышки по ячейке B2.

4. Поставьте знак «+» и повторите те же действия предыдущего пункта, но только на Лист2, а потом и Лист3.

5. Когда формула будет иметь следующий вид: =Лист1!B2+Лист2!B2+Лист3!B2, нажмите Enter. Результат должен получиться такой же, как на рисунке.

Ссылка на лист немного отличается от традиционной ссылки. Она состоит из 3-х элементов:

1. Имя листа.

2. Знак восклицания (служит как разделитель и помогает визуально определить, к какому листу принадлежит адрес ячейки).

3. Адрес на ячейку в этом же листе.

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

ФУНКЦИИ В EXCEL

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

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

Функция ЕСЛИ

В ячейки D7 и E7 введем логическую функцию, которая позволит нам проверить, все ли студенты имеют оценки. Пример использования функции ЕСЛИ:

1. Перейдите в ячейку D7 и выберите инструмент: «Формулы»-«Логические»-«ЕСЛИ».

2. Заполняем аргументы функции в диалоговом окне как показано на рисунке и жмем ОК (обратите внимание вторая ссылка $A$4 - абсолютная):

3. Функцию из D7 копируем в E7.

Описание аргументов функции: =ЕСЛИ(). В ячейке A4 у нас количество всех студентов, а в ячейке D6 и E6 – количество оценок. Функция ЕСЛИ() проверяет, совпадают ли показатели D6 и E6 со значением A4. Если совпадают, то получаем ответ ДА, а если нет – ответ НЕТ.

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

Примечание. Закладка «Формулы» предоставляет доступ только к наиболее часто используемым функциям. Больше можно получить, вызвав окно «Мастер функций» нажав на кнопку «Вставить функцию» вначале строки формул. Или нажать комбинацию клавиш SHIFT+F3.

Как округлять числа

Функция =ОКРУГЛ() более точна и полезнее чем округление с помощью формата ячеек. В этом легко убедиться на практике.

1. Создайте исходную таблицу так как показано на рисунке:

2. Ячейку B2 отформатируйте так, чтобы были отображены только 2 знака после запятой. Это можно сделать с помощью диалогового окна «Формат ячеек» или инструментом расположенном на закладке «Главная»-«Уменьшить разрядность»

3. В столбце C проставьте формулу вычитания 1,25 из значений столбца B: =B-1,25.

Описание аргументов функции =ОКРУГЛ():

1. Первый аргумент – это ссылка на ячейку значение, которой нужно округлить.

2. Второй аргумент – это количество знаков после запятой, которое нужно оставить после округления.

Внимание! Форматирование ячеек только отображает округление но не изменяет значение, а =ОКРУГЛ() – округляет значение. Поэтому для вычислений и расчетов нужно использовать функцию =ОКРУГЛ(), так как форматирование ячеек приведет к ошибочным значениям в результатах.

Работа с датами В EXCEL

Дата и время в Excel – это числа сформатированные специальным образом. Датой является целая часть числа, а время (часы и минуты) – это дробная часть.

По умолчанию число 1 соответствует дате 01 января 1900 года. То есть каждая дата – это количество дней прошедших от 01.01.1900.

Ввод даты в ячейку

Заполните таблицу датами, так как показано на рисунке:

Разные способы ввода дат. В столбце А – способ ввода, а в столбце B – результат отображения.

Обратите внимание, что в формате ячеек по умолчанию «Общий», даты также как и число выравниваются по правой стороне, а текст по левой. Значение в ячейке B4 распознается программой как текст.

В ячейке B7 Excel сам присвоил текущий год (сейчас 2015-ый) по умолчанию. Это видно при отображении содержимого ячеек в строке формул. Обратите внимание, как изначально вводилось значение в A7.

Вычисление даты

На чистом листе в ячейки А1:B1 введите 01.01.1900, а в ячейках A2:B2 вводим 01.01.1901. Теперь меняем формат ячеек на «числовой» в выделенном диапазоне B1:B2. Для этого можно нажать комбинацию клавиш CTRL+SHIFT+1.

B1 теперь содержит число 1, а B2 – 367. То есть прошел один високосный год (366 дней) и 1 день.

Способ отображения даты можно задать с помощью диалогового окна «Формат ячеек». Для его вызова нажмите: CTRL+1. На закладке «Число» выберите в списке «Числовые форматы» - «Дата». В разделе «Тип» отображаются самые популярные форматы для отображения дат.

Занятие 6.6

Табличный редактор Excel.

Работа с формулами

ВВЕДЕНИЕ ФОРМУЛ В ЯЧЕЙКИ ДЛЯ РАСЧЕТОВ ДАННЫХ

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

Ввод формулы вручную

Задание. В ячейки A1, A1 и A3 введите соответственно числа 1, 2 и 3. В A4 просуммируйте их.

Сначала рассмотрим ручной способ ввода:

В ячейку A4 введите следующую формулу: =A1+A2+A3 и нажмите клавишу «Ввод» (Enter).

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

Обратите внимание, адреса ячеек подсвечены разными цветами.

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

Внимание. Вычислительная функция формул является динамической. Например, если мы изменим значение ячейки A1 на 3, то сумма автоматически изменится на 8.

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