InformatikUrok

Тема 3
Простейшие вычисления в  таблицах Excel

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

ОСНОВНЫЕ ПОНЯТИЯ

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

Формулой в Excel называется последовательность символов, начинающаяся со знака равенства “=“. В эту последовательность символов могут входить

• постоянные значения,
• ссылки на ячейки,
• имена,
• функции или операторы.

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

Пример №1. Задание: вычислить произведение чисел 25 и 3. Результат отобразить в ячейке А1

Алгоритм выполнения:

1. В ячейке А1 записать формулу:  =25*3 (рис. 1). Нажать enter.
2. Сделать активной ячейку А1. Результат вычислений будет в ячейке А1, а используемая формула отобразится в строке формул.

Рис.1 – Вычисление формулы. Данные введены с клавиатуры.

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

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

Ссылки бывают абсолютные и относительные. По умолчанию MS Excel создает относительные ссылки.

Пример  №2. Задание: вычислить произведение чисел 25 и 3, записав их в ячейках А1 и В1. Результат отобразить в ячейке С1. Для выполнения задания использовать относительные ссылки на  ячейки.

Рис.2 - Вычисление формулы. Использование относительной ссылки.

Алгоритм выполнения:

1. В ячейке А1 записать число 25.
2. В ячейке В1 записать число 3.
3. В ячейке С1 ввести формулу:  =А1*В1 (рис.2). Формулу обязательно набирать английскими буквами. Превый способ вручную записывать адреса ячеек. Второй способ с помощью ЛКМ (левой кнопки мыши). Рассмотрим этот способ: в ячейке С1 пишем знак =, далее ЛКМ щелкаем по ячейке А1, пишем знак *, потом щелкаем по ячейке В1. Нажимаем enter.
4. Сделать активной ячейку С1. Результат вычислений отображается в ячейке С1, а используемая формула - в строке формул.

При копировании или перемещении формулы с относительными ссылками MS Excel изменяет ссылки на ячейки в соответствии с новым расположением формулы. Продемонстрируем это в примере №3.

Пример  №3. Задание: сделать таблицу для вычисления  произведения чисел от 25 до 30 на 3. Для выполнения задания использовать ссылки на  ячейки. При выполнении задания использовать маркер автозаполнения.

Рис.3- Автозаполнение ячеек числами от 25 до 30

Алгоритм выполнения:

1. В ячейке А1 записать число 25.
2. В ячейке А2 записать формулу: =А1+1 (рис.3).
3. Выделить ячейку А2. Навести ЛКМ на правый нижний угол. Курсор примет вид жирного крестика. Удерживая ЛКМ протянуть мышь вниз и заполнить числами от 25 до 30.
4. Используя маркер заполнения для ячейки В1, запишем число 3 в ячейки диапазона В1:В6.
5. В ячейку С1 записать формулу: =А1*В1.
6. Используя маркер заполнения для ячейки С1,  внести формулы в ячейки диапазона С1:С6.
7. В результате таких действий получим таблицу умножения чисел от 25 до 30 на число 3 (рис.4).

Рис. 4 - Таблица для вычисления  произведения чисел от 25 до 30 на 3.

Если ссылка на ячейку не должна меняться при копировании и перемещении, то создаются абсолютные ссылки на ячейки. Для того, чтобы создать такую ссылку, достаточно перед именем строки и столбца поставить знак $. Например, $С$7 – это абсолютная ссылка на ячейку С7.

Пример  №4. Задание: выполнить предыдущее задание, используя абсолютные ссылки на ячейку.

Рис. 5 – Использование абсолютной ссылки на ячейку В1.

Алгоритм выполнения:

1. Внести данные в ячейки А1:А6 сспособом описанным в примере №3.
2. В ячейку В1 записать число 3.
3. В ячейку С1 записать формулу =A1*$B$1, где $B$1- абсолютная ссылка на ячейку В1.
4. Используя маркер заполнения для ячейки С1,  внести формулы в ячейки диапазона С1:С6.
5. В результате таких действий получим таблицу умножения чисел от 25 до 30 на число 3 (рис.5).

Кроме абсолютной ссылки на ячейку, имеются еще два типа абсолютных ссылок:

• Абсолютная ссылка на строку. В этом случае знак $ размещается только перед номером строки. Например, В$3 – это абсолютная ссылка на третью строку.
• Абсолютная ссылка на столбец. В этом случае знак $ размещается только перед именем столбца. Например,  $В3 - это абсолютная ссылка на столбец В.

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

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

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

Контрольные вопросы:

1. Что называется формулой в Excel ?
2. Как ввести формулу в ячейку7
3. Где отображается результат вычислений, а где содержимое ячейки?
4. Что такое относительные ссылки? Привести пример использования.
5. Сравните результат заполнения формулами диапазона С1:С6 в примере №3 и примере №4. Почему результаты разные?
6. Почему в примере №4 используется абсолютная ссылка на ячейку В1?  Что было бы, если бы в примере №4 в ячейке С1 была формула =А1*В1 и применен маркер автозаполнения?
7. Что такое абсолютная ссылка на строку? Пример.
8. Что такое абсолютная ссылка на столбец? Пример.