Excel Лаб

автоматически Распространите формулу вправо и сравните результат.

Смешанная адресация. Символ $ ставится только там, где он необходим. Например: В$4 или $С2. Тогда при копировании один параметр адреса изменяется, а другой. — нет. В первом случае будет зафиксированным положение строки(при перемещении формулы данные будут браться только из четвертой строки), а во втором случае,

зафиксирован столбец, то есть ссылка относится к столбцу С, а

положение строки изменяется относительно перемещения формулы.

Задание 2. Использование смешанных ссылок

1. Составьте таблицу степеней (до 5-ой) для чисел первого десятка. Для этого заготовьте таблицу по образцу.

По вертикали располагаются числа, по горизонтали – степени.

Числа первого столбца нужно возводить в степени первой строки используя формулу =СТЕПЕНЬ(число; степень) , размещенную в ячейке В2 и смешанную адресацию ячеек.

Растяните формулу, записанную в ячейке В2, вниз и вправо,

таким образом, заполнив всю таблицу.

11

Задание 3. Самостоятельно составьте таблицу сложения

чисел первого десятка.

12

ЛАБОРАТОРНАЯ РАБОТА №3.

«Работа с электронной таблицей как с базой данных. Сортировка и

фильтрация данных»

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

Основные понятия баз данных

Область таблицы, содержащая заголовки столбцов и данные,

можно рассматривать как базу данных. Столбцы А, В,- С, D называются

полями, а строки, начиная с 3 – записями (рис.1). Область A2:D2

содержит имена полей. Информация по полям (столбцам) должна бьпъ однородной, т. е. только цифры или только текст.

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

Фильтрация данных

Команды Данные, Фильтр позволяют выделять (фильтровать)

нужные записи. Фильтрация возможна как через автоматический фильтр — Автофильтр, так и через ручной — Расширенный.

Рассмотрим автоматический способ фильтрации данных.

При использовании Автофильтра необходимо выделить область,

содержащую базу данных. Затем нужно выполнить команды Данные – Фильтр - Автофильтр. На именах полей появятся кнопки с

изображением стрелок вниз, нажимая на кнопки, можно задавать

13

критерии фильтрации. В появлявшемся подменю пункт Все отключает фильтрацию, а пункт Условие вызывает диалоговое окно, в котором можно установить параметры фильтрации. Для одного поля могут бьпъ заданы два условия одновременно, связанные логическими операциями И или ИЛИ

Создайте таблицу:

Задание 1. С использованием Автофильтра осуществить поиск учащихся, которые живут на улице Кулакова и имеют телефон

1.Выполните команды Данные, Фильтр, Автофильтр. На полях появились кнопки.

2.Нажмите стрелку в поле Адрес. Выберите пункт Условие. B

диалоговом окне задайте критерии начинается с К и нажмите кнопку

ОК

3.Нажмите на кнопку в поле Телефон. Выберите пункт Условие.

4.В диалоговом окне задайте критерий Непустые

5.Выполните команды меню Данные, Фильтр, Все для отключения фильтрации.

14

Задание 2. С использованием Автофильтра самостоятельно осуществите поиск учащихся, родившихся в1991 году.

Задание 3. С использованием Автофильтра самостоятельно осуществите поиск учащихся, телефон которых начинается с 4.

Сортировка данных по одному ключу.

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

например, упорядочить Информацию по заработной плате вначале в порядке возрастания номера цеха, а внутри цеха - в алфавитом порядке фамилий. В этом случае используется два поля сортировки: поле ЦЕХ и поле Ф.И.О.

Отсортируем данные в таблице в алфавитном порядке фамилий учащихся. Первое поле Ф.ИО. является ключевым, так как именно в зависимости от информации именно в этом поле необходимо упорядочить все данные в таблице.

Для выполнения сортировки необходимо выделить область базы данных, это блок A3:D9, но не АЗ:А9, так как в этом случае сортируется информация лишь в Столбце А и получится, что учащиеся переселятся на другие улицы и приобретут другие телефоны.

Затем выполните команды Данные, Сортировка. При этом появится диалоговое окно. Установите в окошке «Сортировать по» столбец А, укажите Метод сортировки: по возрастанию (упорядочить от А до Я) или по убыванию (от Я до А) и нажмите кнопку ОК. После указанных действий база будет упорядочена в алфавитном порядке фамилий

15

Задание 4. Отсортируйте данные в таблице в порядке

убывания даты рождения учащихся.

Сортировка данных по нескольким ключам.

Задание 5. Подготовьте репертуарный сборник театров, используя данные приведенной ниже таблицы

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

Выполните команду Данные, Сортировка. Заголовком в окне

Сортировка выберите первый ключ сортировки «Дата» (сортировать по), а второй ключ «Время» (Затем по). В этом случае данные будут отсортированы по датам и, для одинаковых дат, по времени начала

16

спектакля.

Самостоятельно отсортируйте репертуарный сборник по трем

ключам. «Автор», «Название спектакля», «Дата» .

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

Спомощью фильтрации найдите спектакли, которые начинаются раньше 18.00 или позже 19.00.

Спомощью фильтрации найдите спектакли, которые идут во вторник в театре сатиры после 19.00

17

ЛАБОРАТОРНАЯ РАБОТА №4.

«Графические возможности Excel»

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

Задание 1. Познакомьтесь с правилами построения диаграмм

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

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

трехмерных диаграмм.

Упражнение №1. Построение гистограммы

Рассмотрим задачу: проанализировать выполнение плана производством поквартально и за год.

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

(факт/план).Для ячеек В5 – F5 установите процентный формат.

18

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

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

1)Щелкните по кнопке Мастер диаграмм в панели инструментов открывается диалоговое окно Мастер диаграмм, в

котором задается вид диаграммы. Выберите на вкладке диалогового окна Стандартные тип Гистограмма, объемный вариант обычной гистограммы и щелкните на кнопке Далее.

2)Мастер диаграмм переходит в диалоговое окно источник данных диаграммы. Здесь на вкладке Диапазон данных следует указать мастеру, где находятся данные для построения диаграммы: в строках или в столбцах. В нашем случае - в строках. Активизируйте соответствующий переключатель щелчком левой кнопки мыши. Затем щелкните на красной стрелке в окошке Диапазон, мастер вернет вас к исходной таблице. Установите указатель мыши в ячейку В3 и, нажав левую кнопку мыши, заключите блок ВЗ:Е4 в рамку и вернитесь к мастеру диаграмм, вновь выбрав красную стрелку. В том случае если данные находятся не в смежных строках или столбцах, то отмечать ряды следует при нажатой клавише Ctrl.

3)Выберите вкладку Ряд и задайте подписи по оси Х - так же по красной стрелке перейдите к данным, отметьте блок В2 – Е2,

содержащий слова Март, Июнь, Сентябрь, Декабрь, и Итого, вернитесь к

мастеру диаграмм.

19

В центре диалогового окна вы видите диаграмму, построенную в результате ваших действий. Если вид диаграммы отражает результат решаемой задачи, щелкните на кнопке <Далее>, или перейдите к предыдущему шагу, щелкнув по кнопке <Назад> в противном случае.

Диалоговое окно Мастер диаграмм (шаг 3 из 4): параметры диаграммы содержит ряд вкладок, команды которых позволяют нанести на область диаграммы поясняющий текст, значения, заголовки.

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

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

6) Выберите вкладку Заголовки и залайте название диаграммы

«Выполнение плана производством». При желании можно нанести заголовок на ось X и ось У, однако заголовков не должно быть слишком много. Чрезмерное увлечение надписями приводит к тому,

что теряется наглядность диаграммы.

7)Выберите вкладку Оси, здесь можно включить или отключить показ значений на осях.

8)Выберите вкладку Линии сетки. Дополнительные линии сетки позволяют точнее определить результат графического решения задачи.

Щелкая в окошках «Основные линии», «Промежуточные линии» подберите наилучший вариант линий сетки для нашего примера

9) Выберите вкладку Таблица данных. Команды этой вкладки позволяют включить в область диаграммы таблицу с данными, на основании которых строилась диаграмма.

Когда все параметры диаграммы будут установлены, щелкните

20