Организация расчетов в MS Excel 2016»

ПРАКТИЧЕСКАЯ РАБОТА №12

«Основы работы с электронными таблицами.

Организация расчетов в MS Excel 2016»

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

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

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

· числовое значение;

· текст;

· формула.

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

ФУНКЦИЯ АВТОЗАПОЛНЕНИЕ

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

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

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

Отпустить кнопку мыши, ячейки заполнятся числами.

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

Задание 1:

1. Создайте новую рабочую книгу MS Excel.

2. На Листе1 в левой верхней части листа заполните таблицу данными так, как приведено на рис. 4. Верхняя левая ячейка таблицы соответствует ячейке А1.

Рис. 4.

3. Используя функцию Автозаполнение, создайте таблицу согласно рис. 5.

Рис. 5.

4. Сохраните таблицу.

СОЗДАНИЕ ФОРМУЛ

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

Формула, введенная в ячейку, может содержать следующие элементы:

· операторы;

· ссылки на ячейки (включая поименованные ячейки и диапазоны);

· числа;

· функции.

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

Ввод формулы всегда начинается со знака равенства (=). По этому признаку MS Excel отличает формулы от текста. MS Excel позволяет использовать в формулах самые разные операторы. В перечислены операторы, которые распознает программа.

Символ	Оператор
+	Сложение
-	Вычитание
*	Умножение
/	Деление
^	Возведение в степень
&	Конкатенация
=	Логическое сравнение «равно»
>	Логическое сравнение «больше чем»
<	Логическое сравнение «меньше чем»
> =	Логическое сравнение «больше или равно»
< =	Логическое сравнение «меньше или равно»
< >	Логическое сравнение «не равно»

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

=(Прибыль-Расходы)*Ставка_налога

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

Без скобок данная формула примет вид:

=Прибыль-Расходы*Ставка_налога,

т.е. из значения ячейки «Прибыль» вычитается произведение ячеек «Расходы» и «Ставка_налога». Таким образом, формула будет возвращать ошибочный результат. Чтобы избежать подобных ошибок, необходимо использовать в формулах круглые скобки, изменяя естественный порядок выполнения операторов и указывая последовательность выполнения вычислений.

ü Примечание: Ссылки на ячейки, используемые в качестве аргументов функции, следует вводить на английском языке

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

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

Задание 2:

1. Убедитесь, что активный является английский шрифт.

2. Установите табличный курсор в ячейке D2.

Рис. 6.

3. Введите знак равенства, а затем формулу: В2*С2. Нажмите клавишу < Enter >.

Рис. 7.

5. Убедитесь, что в ячейке D2 появилось числовое значение рис. 8.

Рис. 8.

АДРЕСАЦИЯ ЯЧЕЕК

Относительная адресация

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

Задание 4:

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

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

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

Рис. 12.

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

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

Абсолютная адресация

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

Задание 5:

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

Рис. 13.

1. В ячейке Е2 измените формулу следующим образом: =D2*$B$19.

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

3. Просмотрите формулы и убедитесь, что относительные ссылки изменились, но ссылка на ячейку В19 осталась прежней рис.14.

Рис. 14.

Упрощение формул

Например, среднее значение чисел, находящихся в десяти ячейках диапазона А1: А10 можно вычислить по следующей формуле: =(А1+А2+А3+А4+А5+А6+А7+А8+А9+А10)/10

Данную формулу можно заменить встроенной функцией: =AVERAGE(А1: А10), где

AVERAGE – функция вычисления среднего значения чисел диапазона;

А1: А10 – диапазон ячеек, содержащий числа, среднее значение которых требуется найти.

ФОРМАТИРОВАНИЕ ДАННЫХ

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

Форматирование влияет только на способ отображения числового значения в ячейке.

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

Некоторые операции форматирования MS Excel способна выполнять автоматически. Например, если ввести в ячейку значение 12, 2%, то программа знает, что вы хотите использовать процентный формат, и применяет его автоматически.