Элементарные расчеты в Excel. Диаграммы

9

ЛАБОРАТОРНАЯ РАБОТА

«Элементарные расчеты в Excel. Диаграммы»

Продолжительность занятия: 4 часа

Цель работы: получение практических проведения элементарных табличных расчетов, построения и редактирования диаграмм средствами MS Excel.

1.                   ЭЛЕМЕНТАРНЫЕ РАСЧЕТЫ В EXCEL

1.1.             Общие сведения

1.1.1.                    Операции Excel

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

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

Операции Excel

Таблица 1.1.

1.1.2.                    Вставка и редактирование формул

Максимальная длина формулы, которую можно вставить в ячейку, составляет 1024 символа. Для ввода формулы в активную ячейку:

   Начните набор формулы, нажав клавишу "=".

   Введите формулу.

   Закончите ввод формулы, нажав клавишу Enter или щелкнув мышью по кнопке с галочкой в строке формул.

Если вы хотите отредактировать уже введенную формулу, то можете сделать это несколькими способами.

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

   Сделайте ячейку активной и нажмите F2.

   Сделайте ячейку активной и щелкните мышкой по строке формул.

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

             Практикум

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

1.1.3.                    Установка режима вычислений

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

   Выполните команду Параметры меню Сервис.

   Перейдите на панель Вычисления.

   Установите режим Вручную и включите флажок Пересчет перед сохранением.

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

1.2.                           Автосуммирование по столбцам и строкам

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

Для автоматической вставки формулы суммирования в  ячейку:

   Выделите диапазон ячеек, предназначенных к суммированию.

   Щелкните по кнопке с изображением знака суммы.

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

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

   сделайте текущей ячейку, в которую следует вставить формулу;

   щелкните по кнопке  - Excel выделит интервал для суммирования бегущей линией;

   если вас этот интервал не устраивает, выделите мышкой новый интервал;

   нажмите Enter.

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

             Практикум

       Создайте на первом рабочем листе представленную ниже таблицу.

Таблица A – Объемы продаж компьютерной техники по филиалам в 2010 году  (тыс. руб.).

       В таблице A, используя кнопку автоматической суммы:

 Вставьте  в первую ячейку строки "Итого по кварталам" формулу суммирования содержимого ячеек  столбца "1-ый кв.". Затем, используя операцию заполнения смежных блоков, скопируйте формулу суммирования в остальные ячейки строки.

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

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

       Просмотрите вид формул, вставленных в ячейки столбца "Итого по филиалам" и строки "Итого по кварталам".

1.3.             Формат данных, основные типы форматов

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

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

В MS Excel установка для ячеек наиболее часто используемых форматов автоматизирована с помощью следующих инструментов панели Форматирование.

   Инструмент Формат с разделителями устанавливает для ячейки числовой формат с целой и дробной частями, причем в целой части сотни, тысячи, миллионы и т.д. будут отделены пробелами, например: 123  456  789,4356.

   Инструмент Денежный формат устанавливает для ячейки представление числа в форме денежной единицы, заданной в стандарте Windows (диалог Язык и стандарты панели управления Windows).

   Инструмент Процентный формат устанавливает для ячейки формат, который умножает число в ячейке на 100 и добавляет знак процентов.

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

             Практикум

       Вставьте в одну из свободных ячеек рабочего листа число 17236,3475.

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

 17  236,348

 17 236,35р.

 1723634,750%

       Задайте денежный формат для ячеек таблицы A, содержащих числовые данные.

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

1.4.             Имя ячейки

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

   выделите область электронной таблицы, которой присваиваете имя;

   выполните команду Имя меню Вставка, затем команду Присвоить;

   в поле Имя наберите имя;

   выполните ОК.

Имя может быть в длину до 255 символов и содержать буквы, цифры, символы подчеркивания, точки, вопросительные знаки, символы “\” и “/”. Первым символом должна быть буква, или символ подчеркивания, или обратная косая черта “\”. Допускается кириллица. Не допускаются имена, совпадающие с адресами ячеек.

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

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

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

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

Пусть ячейка A1 содержит формулу =B5/C8. Скопируем формулу в ячейку В3 (адрес строки увеличится на 2, а адрес столбца увеличится на 1). Соответственно изменятся адреса влияющих ячеек, входящих в формулу. Т.е. в ячейке B3 мы получим формулу =C7/D10.

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

В нашем примере, если в формуле ячейки А1 записать =B$5/$C8, то после переноса в ячейке B3 будем иметь: =C$5/$C10.

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

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