Лабораторная работа №2. Расчеты в таблицах и вычисления с помощью функций Excel

Цель работы: вычисления по формулам, расчет сумм, средних, поиск минимумов и максимумов, функции математические, логические, статистические. (4 часа.)

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

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

Рассмотрим схему применения данной пиктограммы.

1. Выделите фрагмент с информацией, которую необходимо просуммировать.

2. Установите указатель мыши на место вывода суммы и щелкните по нему.

3. Щелкните мышью по пиктограмме автосуммирования. В ячейке, определенной в п. 2, появится наименование процедуры суммирования (СУММ) с указанием диапазона ячеек, значения которых предназначены для суммирования.

4. Для подтверждения правильности суммирования щелкните мышью по этой пиктограмме еще раз.

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

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

Как ввести формулу. В Excel существует несколько способов создания формул расчетов. Рассмотрим некоторые из них. Определим некоторые операции, операнды и операторы, которые могут использоваться в формулах. В табл. 2.1. приведены основные операции Excel.

Табл. 2.1. Операции Excel

Операндами в формуле могут быть:

– относительные или абсолютные адреса ячеек;

– определенные имена фрагментов;

– константы текстовые или числовые (например, 2, -3.5, строка символов "abc" и т. п.);

– имена и аргументы специальных функций.

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

Для ввода формулы выполните следующие действия.

1. Установите указатель мыши в ячейку результата.

2. Введите первый символ формулы – знак "=".

3. Введите операнды и операции формулы таким образом, чтобы между операндами не было пробелов (см. табл. 2.1., символ пробела сам по себе является операцией).

Если в операнде-функции есть несколько аргументов, они должны разделятся знаком "," и помещаться в круглые скобки. В случае отсутствия аргументов скобки все равно должны присутствовать, но между ними уже не будет пробела (т. е. 0). Если в качестве операнда используется текстовая информация, она должна быть заключена в кавычки.

Таблица 2.2. Примеры записи в формулах в Excel

При отсутствии скобок первой выполняется операция с наименьшим приоритетом.

В табл. 2.2. приведены примеры записи формул в Excel.

Как создать формулу с помощью Мастера Функций. Для упрощения ввода формулы и (или) функции можно воспользоваться Мастером Функций.

Рис. 2.1

Вызвать Мастер функций можно или из меню Вставка или щелкнув мышью по пиктограмме

На экране появится первое диалоговое окно Мастера Функций (рис. 2.1).

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

После нажатия кнопки Далее на экране появится второе диалоговое окно Мастера функций (рис. 2.2.), в котором следует задать аргументы функции.

Для ввода каждого аргумента в диалоговом окне предусмотрены отдельные поля ввода. Переход от одного поля ввода к другому осуществляется с помощью клавиши [Tab]. При помещении курсора в поле ввода в диалоговом окне будет представлена краткая информация об аргументе. В поле Значение в правом верхнем углу окна отображается текущее значение функции. Задать аргумент функции можно и путем выделения ячейки или диапазона ячеек непосредственно в рабочем листе. Для этого следует поместить курсор ввода в поле задания соответствующего аргумента и выделить нужную ячейку или диапазон.

Рис. 2.2

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

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

Многие функции становятся доступными только после установки дополнения Пакет анализа. Если это дополнение не установлено, то следует выбрать команду Надстройки меню Сервис, активизировать Диспетчер надстроек и в появившемся диалоговом окне выделить элемент Пакет анализа. При описании функции указывается, является ли используемая функция доступной без установки дополнения (надстройки) Пакет анализа.

Наиболее часто в Excel используются следующие стандартные математические функции:

Как автоматически сформировать списки в таблицах. При заполнении таблиц может возникнуть ситуация, при которой необходимо заполнить столбец или строку по заданному закону, где каждое последующее значение завист от предыдущего (например, составить столбец чисел по формуле A0 =1; A1 = A0 +5; A2 = A1+5; ...; AN = AN-1+5, т. е. получить следующий столбец: 1, 6, 11, 16, 21, ...). Excel позволяет автоматизировать данный процесс. Для этого достаточно выделить в таблице столбец или строку для заполнения, в меню Правка [Edit] отметить пункт Заполнить [Fill] и инициализировать команду Прогрессия [Series]. На экране появится диалоговое окно с опциями:

– Прогрессия. Содержит кнопки выбора для указания элементов упорядочения: По столбцам [Columns], По строкам [Rows]. Это значит, что ряд чисел будет расположен в столбце (строке), где находится первый элемент прогрессии;

– Тип [Types]. Указывает тип прогрессии: Арифметическая [Linear], Геометрическая [Growth], Даты [Date], Автозаполнение [Autofill];

– Единицы [Date Unit]. Определяет минимальную единицу, на которую будет изменятся значение даты: День [Day], День недели [Week Day], Месяц [Month], Год [Year];

– Шаг [Step Value]. Служит для установки шага приращения; по умолчанию шаг равен единице. Если необходимо установить собственное приращение, введите в поле ввода необходимое значение;

Таблица 2.3. Основные функции над данными

– Предельное значение [Stop Value]. Устанавливает, до какого конечного значения заполнять столбец или строку. Если это значение не указано, то заполнение ограничивается количеством ячеек в выделенном столбце или строке.

Как использовать функции над данными при работе Excel. Некоторые функции пакета описаны в табл. 2.3.

Лабораторная работа №3. Построение диаграмм и графиков

Цель работы: ознакомление с возможностями Excel по построению графиков. (4 часа.)

Как строить диаграммы и графики по таблицам. Для этой цели служит так называемый Мастер Диаграмм [Chart Wizard], вызвать который можно с помощью команды Диаграмма [Chart] меню Вставка [Insert]. На экране появится диалоговое окно – Мастер Диаграмм – шаг 1 из 5 [Chart Wizard – Step 1 of 5]. Вызвать Мастера Диаграмм можно и из пиктографического меню.

После вызова (любым способом) Мастера Диаграмм на экране появится уменьшенная копия его пиктограммы. Используя принцип Drag&Drop, установите ее в место размещения диаграммы и щелкните мышью.

Если вы предварительно выделили некоторый фрагмент таблицы, то эта выделенная область будет указана в поле Диапазон [Range]. Если нет, то введите в это поле диапазоны фрагментов таблицы для построения диаграммы.

Если вы включите параметр выделения заголовка строк и столбцов, то на диаграмме эти заголовки будут присутствовать в качестве координат. Щелкнув мышью по кнопке Далее [Next], вы перейдете ко второму диалоговому окну Мастера Диаграмм – шаг 2 из 5, на котором указаны 15 типов различных диаграмм. Выделив требуемый вам тип, перейдите к третьему шагу Мастера Диаграмм, который позволяет выбрать необходимый формат диаграммы. На экране появится окно четвертого шага Мастера Диаграмм с предварительным изображением построенной диаграммы. Справа от изображения указаны диалоговые области, позволяющие установить его базовые параметры:

– Ряды данных находятся в [Data Series in]. Указывает размещение данных: Строках [Rows] или Столбцах [Columns];

– Считать ... столб. метками оси X (Категорий) [User First ... Column[s] for category [X] Axis Labels]. Служит для указания количества позиций, которые следует выделить для наименования градаций оси X;

– Считать ... стр. метками легенды [User First ... Row[s] for Legend Text]. Представляет возможность выделить определенное количество строк, в которых будет расположена надпись к диаграмме (легенда). Перейдя к пятому и последнему шагу Мастера Диаграмм, вы увидите окончательное изображение построенной диаграммы. Диалоговые области этого окна позволяют добавить надпись к диаграмме – Добавить легенду [Add a Legend] и определить содержимое этой надписи – Название диаграммы [Chart Title], заголовки координатных осей – Название осей [Axis Titles] (Категорий [Category] X, Значений [Value] Y, Вторая [Second] Y). Для изменения диаграммы вернитесь к предыдущему шагу, иначе – щелкните мышью на кнопке Готово [Finish].

После фиксации команды Готово в указанном месте рабочего листа появится построенная диаграмма.

Рис. 3.1

На рис. 3.1 проиллюстрированы понятия, введенные выше.

Введите следующую таблицу:

Рис. 3.2

После выделения ячеек B3:E7, входим в меню Вставка в появляющемся подменю выбираем пункт Диаграмма и На этом листе. Мышью выбираем место на рабочем листе и щелкаем клавишей. Появляется диалоговое окно Мастер Диаграмм – шаг 1 из 5. Если диапазон ячеек указан правильно, то

Рис.3.3

нажимаем на кнопке Далее. Появляется окно Мастер Диаграмм – шаг 2 из 5, предлагающее выбрать тип диаграммы. Щелчком мыши выбираем Круговая (в виде пирога). В окне Мастер Диаграмм – шаг 3 из 5 выбираем вид диаграммы 7. В диалоговом окне Мастер Диаграмм – шаг 4 из 5 нажимаем на кнопку Далее. И, наконец, в окне Мастер Диаграмм – шаг 5 из 5 нажимаем на кнопку Готово. В результате получаем диаграмму, приведенную выше (рис. 3.3.) и иллюстрирующую процентный вклад в общий доход каждого члена семьи.

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

Многие элементы диаграммы объединены в группы.

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

Рис. 3.4

Для того чтобы ввести в диаграмму метки данных, нужно после активизации диаграммы выбрать в меню Вставка команду Метки значений. На экране появится диалоговое окно Метки значений. Выбрать в диалоговом меню нужные параметры и нажать кнопку Ok (рис. 3.4.).

Для того чтобы изменить формат меток данных нужно дважды щелкнуть мышью на произвольной метке данных любого ряда. На экране появится диалоговое окно Форматирование меток данных. Далее задаем все необходимые изменения формата на вкладках Вид, Шрифт, Число, Выравнивание. Для того чтобы задать такой же формат для других меток данных, нужно их выделить и нажать клавишу F4. Нажать кнопку Ok.

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

В том случае, если был создан вариант диаграммы со многими установками, отличающимися от первоначальных, то может оказаться желательным сохранить этот вариант диаграммы в качестве нового типа (автоформат). Для этого необходимо активизировать в меню Формат команду Автоформат и в появившемся на экране одноименном диалоговом окне в группе Типы диаграмм установите опцию Дополнительные. В левой части окна появится список дополнительных типов диаграмм. Нажмите кнопку Настроить для перехода в диалоговое окно управления дополнительными типами диаграмм. Чтобы добавить новый тип, нажмите кнопку Добавить и укажите в диалого-

Рис. 3.5

вом окне название и краткое описание типа, затем нажатием кнопки Ok закройте диалоговое окно Добавление нового типа диаграммы. В дальнейшем для применения созданного типа диаграммы следует активизировать нужную диаграмму, выбрать команду Автоформат меню Формат и в одноименном диалоговом окне после установки опции Дополнительные выбрать созданный тип. В результате нажатия кнопки Ok данный тип будет применен к активной диаграмме.

Задание по лабораторной работе. Ввести данные по магазину «Одежда» (рис.3.1) построить диаграмму и осуществить редактирование элементов диаграммы (рис. 3.4 – 3.5).