close

Вход

Забыли?

вход по аккаунту

код для вставкиСкачать
Электронные таблицы Excel
Лабораторная работа № 1
«Создание, обработка, форматирование, редактирование таблиц с данными; абсолютная и относительная
адресация; связь таблиц между листами»
1. Создайте новую рабочую книгу (щелкнув пиктограмму в виде листа с загнутым краем на панели
инструментов Стандартная либо выбрав команду Файл – Создать) или используйте открытую по
умолчанию.
2. С помощью мыши сделайте текущей ячейку D4, вернитесь в ячейку А1 при помощи клавиш
перемещения курсора.
3. Выделите блок (диапазон) ячеек А1:D7 c помощью мыши (щелкнуть мышью на начальной ячейке
блока и, не отпуская кнопку, протянуть мышь на последнюю ячейку). Вернитесь в ячейку А1.
4. Щелкая по ярлычкам листов слева внизу книги перейдите к другому листу, добавьте новый лист,
щелкнув по ярлычку правой кнопкой мыши и выбрать пункт Добавить, затем – Лист. Назовите его
Основной с помощью того же меню, что и добавляли новый лист.
5. Занесите в ячейку А1 текст: Москва – древний город. Ввод завершите либо клавишей Enter, либо
клавишей управления курсором. В ячейку В1: Москва – город-герой. Ширина текста окажется больше
ширины ячейки и текст в ячейке А1 будет обрезан. Чтобы был виден весь текст необходимо увеличить
ширину ячейки: либо наведите указатель мыши на границу между обозначениями столбцов (т.е. здесь
между столбцом А и В) до появления нового указателя в виде вертикальной линии с горизонтальными
стрелками и не отпуская кнопки протяните мышь до нужной ширины ячейки, либо выделите столбец,
нажав мышью на обозначении столбца, и нажмите правую кнопку мыши, затем выберите Ширина
столбца и введите размер. Можно использовать меню Формат – Столбец – Ширина, Формат –
Строка – Ширина.
6. Введите в ячейку А2 год основания Москвы 1147, в А3 – текущий год.
7. Перейдите в А1 и отредактируйте текст Москва – столица России. Текст в ячейке В1 вырежьте
(удалите).
8. Переместите блок А1:В3 строкой ниже: выделите его, навести указатель мыши на рамку блока
(указатель примет форму белой стрелки), не отпуская кнопки, перетащите блок, указав на ячейку А2.
9. Введите в ячейку В3 формулу: =А4-А3, после Enter в ячейке появиться значение возраста Москвы.
10. Перейдите в ячейку В4 и щелкните по ней правой кнопкой мыши, выберите Формат ячеек, вкладку
Число, в поле Числовые форматы выберите Дата, в поле Тип – 16.04.97, ОК. Введите в ячейку
текущую дату.
11. Скопируйте блок А2:В4 в А15:В17. Для этого выполните такие же команды как в пункте 8, но при
этом держите нажатой клавишу Ctrl. И перетаскивайте, указывая на ячейку А15.
12. Заполните автоматически столбец порядковых номеров: перейдите в ячейку В5 и введите 1, в ячейку
В6 – 2, выделите блок В5:В6 и укажите мышью на маленький черный квадратик в правом нижнем
углу выделенного блока, нажмите кнопку мыши и, не отпуская ее протяните мышь до ячейки В14, вы
увидите как автоматически заполнился столбец порядковыми номерами.
13. Аналогично введите дни недели (в С5 введите понедельник и проделайте ту же операцию, что и в п.12
до ячейки С11) и месяцы (в D5 введите январь и проделайте ту же операцию, что и в п.12 до ячейки
D16)
14. Сохраните файл
15. Создайте новую рабочую книгу щелкнув пиктограмму на панели инструментов Стандартная либо
выбрав команду Файл – Создать.
16. Назовите Лист1 Данные, Лист2 – Данные2. На первом листе создайте таблицу из следующих
данных:
Фамилия
Кол-во часов.
коэф.
Ставка
Зарплата
Иванов
100
820
Петров
1200
850
Сидоров
1723
900
Павлов
1378
820
Алексеев
260
850
Андреев
1378
900
Федоров
1768
820
Васин
245
900
17. Отформатируйте таблицу таким образом, чтобы столбцы имели ширину, позволяющую просмотреть
все результаты; названия столбцов отцентрируйте, границы табличных ячеек сделайте видимыми
(выделите диапазон, занятый таблицей, затем на панели Форматирование выберите пиктограмму Все
границы, где нажмите пиктограмму в виде сетки)
18. В первую ячейку столбца Ставка введите формулу: =В2/С2, выделите ячейку и протяните мышью до
конца таблицы, для всех значений ставка посчитается автоматически.
19. Перейдите на второй лист Данные2 и в нем в ячейку G1 введите название столбца Оклад, в G2
значение 1200.
20. В первую ячейку столбца Зарплата введите формулу:=D2*Данные2!$G$2, выделите ячейку и
протяните мышью до конца таблицы, для всех значений зарплата посчитается автоматически. Первый
множитель в произведении формулы меняется (берется следующая в столбце ячейка – это
относительная адресация), а второй остается постоянным – это и есть абсолютная адресация.
21. Сохраните книгу…
Электронные таблицы Excel
Лабораторная работа №2
Седьмой элемент
Шампунь
Седьмой элемент
Красящий шампунь
Седьмой элемент
Губная помада
Седьмой элемент
Пена для ванн
Седьмой элемент
СМС
Седьмой элемент
Лак для волос
Седьмой элемент Всего
Общий итог
Подведение промежуточных итогов
Создание и сортировка списка таблиц Excel
1.
2.
3.
4.
1.
2.
3.
4.
5.
6.
Запустите программу Excel.
Откройте рабочую книгу, созданную в предыдущих заданиях (Файл-Открыть).
Дважды щелкните на ярлычке чистого рабочего листа и дайте ему название Список. Если в Рабочей книге
нет чистых листов вставьте их, выбрав из меню Вставка команду Лист.
Создайте список как показано на рисунке :
Предприятие
Продукция
Объем
Сбыт
Прометей
Седьмой элемент
Прометей
Прометей
Седьмой элемент
Седьмой элемент
Прометей
Прометей
Галина
Седьмой элемент
Прометей
Галина
Седьмой элемент
Прометей
Прометей
Напитки
Шампунь
Напитки
Мясо
Красящий шампунь
Губная помада
Мясо
Продукты
Сигареты
Пена для ванн
Сигареты
Напитки
СМС
Мясо
Мясо
4879
1966
323
65565
56565
121
12212
454
545
56565
78
545
232
55656
2121
54500 р.
120000 р.
126660 р.
15400 р.
456000 р.
12100 р.
1200 р.
121540 р.
5454 р.
45000 р.
1200 р.
4545 р.
12125 р.
12200 р.
5487 р.
Седьмой элемент
Лак для волос
121
12800 р.
Прометей
Галина
Продукты
Продукты
3265
554
12120 р.
58545 р.
Данные для подведения промежуточных итогов
Выделите список, включая метки столбцов. В меню Данные выберите команду Сортировка.
В появившемся окне «Сортировка диапазона» в поле сортировать по выберите Продавец и установите
флажок «по убыванию».
В поле "Затем по" выберите Продукция и установите флажок «по возрастанию».
В поле "В последнюю очередь по" выберите Объем и установите флажок «по возрастанию». Нажмите ОК.
Вы получите отсортированный список.
Теперь необходимо подвести итоги по каждому продавцу, выбрав из меню Данные команду Итоги….
В появившемся окне «Промежуточные итоги» в поле При каждом изменении в выберите Продавец. Далее
определите Операцию – Сумма. В поле Добавить итоги по установите «галочки» для Объема и Сбыта.
Нажмите ОК. Список будет иметь вид, рисунок:
Предприятие
Продукция
Объем
Сбыт
Галина
Сигареты
545
5 454р.
Галина
Напитки
545
4 545р.
Галина
Продукты
554
58 545р.
1644
68 544р.
Галина Всего
Прометей
Напитки
4879
54 500р.
Прометей
Напитки
323
126 660р.
Прометей
Мясо
65565
15 400р.
Прометей
Мясо
12212
1 200р.
Прометей
Продукты
454
121 540р.
Прометей
Сигареты
78
1 200р.
Прометей
Мясо
55656
12 200р.
Прометей
Мясо
2121
5 487р.
Прометей
Продукты
3265
12 120р.
144553
350 307р.
Прометей Всего
1966
56565
121
56565
232
121
115570
261767
120 000р.
456 000р.
12 100р.
45 000р.
12 125р.
12 800р.
658 025р.
1 076 876р.
Из полученного списка необходимо выделить в отдельную таблицу информацию о продавцах, торгующих
мясом с объемом более 500 и сбытом, не превышающим 100000 р. Для этого скопируйте метки столбцов в
ячейки Н1:К1. В ячейку I2 занесите Мясо, в ячейку J2 - >500, в ячейку K2 - <=100000. Таким образом, вы
определили интервал критериев.
8. Аналогично выведите информацию о фирме Седьмой элемент.
9. Выделите ячейку списка. В меню Данные выберите команду Расширенный фильтр. В появившемся на
экране диалоговом окне Исходный диапазон указан автоматически (проверьте его правильность).
Щелкните мышью в поле Диапазон условий и выделите интервал I1:K2. Установите переключатель
скопировать результат в другое место. Щелкните в поле Поместить результат в диапазон и выделите любую
свободную ячейку рабочего листа, например А29. Нажмите кнопку ОК.
10. С помощью расширенного фильтра из исходного списка выделите и поместите в отдельную таблицу
данные о товарах со сбытом более 10000р, которыми торгует Прометей (используйте навыки, полученные
при выполнении п.11-12).
11. Аналогичным образом из исходного списка выделите и поместите в отдельную таблицу данные о товарах с
объемом не более 1000 и сбытом не менее 12500р.
12. Сохраните данные в рабочей книге.
7.
Использование Мастера функций Excel для расчетов
Лабораторная работа № 3
«Расчеты с помощью логических функций; пример расчета при начислении стипендии»
Создайте новую рабочую книгу (щелкнув пиктограмму в виде листа с загнутым краем на панели
инструментов Стандартная либо выбрав команду Файл – Создать) или используйте открытую по
умолчанию.
2. Назовите лист Начисление стипендии.
3. В ячейки диапазона А1:В4 введите следующие данные:
A
B
1
Интервал
Коэффициент
2
0..2,99
0
3
3..4,5
1
4
4,6..5
1,5
4. В ячейку Е1 введите название Размер стипендии, в ячейку Е2 – стандартный размер стипендии.
5. Оформите таблицу как показано на рисунке ниже включая фамилии и оценки по предметам для
каждого студента.
А
В
С
D
E
F
10
Средний
Фамилия
Экономика
Ин.яз.
Информатика
Сумма
бал
Иванов И.И.
11
Петров П.П.
12
Сидоров С.С.
13
Павлов П.П.
14
…
…
Васин В.В.
20
6. Для вычисления среднего балла в ячейку Е11 занесите формулу с использованием функции СРЗНАЧ.
Скопируйте ее для всех остальных ячеек столбца Е
7. Для расчёта стипендии в ячейку F11 запишите следующую формулу с использованием логической
функции ЕСЛИ: =ЕСЛИ(Е11>4,5;Е2*В4;ЕСЛИ(Е11>=3;Е2*В3;0))
8. Проверьте, правильно ли начислена стипендия для первого студента в списке. Размножьте формулу,
записанную в ячейке F11, на остальные ячейки столбца F.
9. Убедитесь, что результат расчёта оказался неверным. Это связано с тем, что адреса Е2, В3 и В4 в
формуле заданы относительными ссылками и при копировании изменяются.
10. Исправьте формулу в ячейке F11, задав соответствующие ссылки как абсолютные, и снова размножьте
её в столбце F.
11. Отсортируйте данные в таблице.
12. Проверьте правильность результатов расчёта по заданной формуле, и, если они верны, сохраните
данные.
1.
Электронные таблицы Excel
Лабораторная работа №4
Простейшие операции с базой данных
1.
2.
3.
4.
5.
6.
Запустите программу Excel и откройте рабочую книгу, созданную ранее.
Создайте новый рабочий лист, присвойте ему имя Сведения о поставках.
Предполагается, что предприятие получает пять видов материалов: бумагу, фанеру, картон, полиэтилен и
ткань — от пяти поставщиков, находящихся в Братске, Казани, Курске, Мурманске и Череповце. Каждый
из поставщиков может поставлять любой вид материалов. Поставки производятся не чаще раза в месяц,
единица измерения — тонна.
ВячейкиА1-D1 введите заголовки полей базы данных, соответственно: Месяц, Поставщик, Товар, Объем.
Введите несколько десятков записей, имеющих описанную выше структуру. Реальные «объемы поставки»
значения не имеют.
Произведите общую сортировку базы данных. Сделайте текущей любую ячейку базы данных и дайте
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
команду Данные - Сортировка. Убедитесь, что при этом выделяется вся (кроме заголовков полей) база
данных.
В списке - Сортировать по, выберите пункт Месяц и Режим по возрастанию.
В списке - Затем по (вторичная сортировка), выберите пункт Поставщик и режим По возрастанию.
В списке - В последнюю очередь, выберите пункт Товар и режим По возрастанию.
Убедитесь, что база данных отсортирована по указанным критериям.
Выполните последовательную сортировку базы данных. С помощью кнопки - Отменить на панели
инструментов восстановите прежний порядок записей базы данных.
Выберите любую ячейку в столбце Товар и щелкните на кнопке Сортировка по возрастанию на панели
инструментов.
Выберите любую ячейку в столбце Поставщик и щелкните на кнопке Сортировка по возрастанию на панели
инструментов.
Выберите любую ячейку в столбце Месяц и щелкните на кнопке Сортировка по возрастанию на панели
инструментов. Убедитесь, что итоговый порядок сортировки тот же, что и в предыдущем случае.
Произведите фильтрацию данных. Выделите заголовки столбцов. Выполните команду Данные - Фильтр Автофильтр. Обратите внимание на появление раскрывающих кнопок у заголовков полей базы данных.
Чтобы отобрать только записи, описывающие поставки из Братска, щелкните на раскрывающей кнопке у
поля Поставщик и выберите в списке пункт Братск. Обратите внимание на то, что раскрывающая кнопка
действующего фильтра и номера отобранных строк отображаются синим цветом. Чтобы отменить текущий
фильтр, еще раз щелкните на раскрывающей стрелке и выберите пункт Все.
Чтобы отобрать наиболее крупные разовые поставки, щелкните на раскрывающей стрелке у поля Объем и
выберите в списке вариант Первые 10.
Выберите с помощью счетчика число 20 и далее пункт наибольших и вариант % от количества элементов.
Щелкните на кнопке ОК. В результате будет отобрано 20% записей, содержащих наибольшие значения
объема поставок.
Чтобы отменить режим фильтрации записей, еще раз дайте команду Данные - Фильтр - Автофильтр.
Электронные таблицы Excel
Лабораторная работа №5
«Форматирование таблиц. Построение диаграмм на основе данных в таблицах»
1.
Создайте новую рабочую книгу (щелкнув пиктограмму в виде листа с загнутым краем на панели
инструментов Стандартная либо выбрав команду Файл – Создать или используйте открытую по умолчанию.
2.
Заполните лист рабочей книги следующим образом
E
A
B
C
D
F
1
Авиалинии «ГОЛУБОЕ НЕБО»
2
Квартальный отчет по сбыту
3
4
Район
5
Северный
10110
13400
6
Южный
22100
24050
7
Восточный
13270
15670
Январь
Февраль
формате. Следует расширить столбцы на нужную величину, поместив и подвинув указатель мыши между
заголовками столбцов.
9.
Выделите любую ячейку таблицы сбыта, в меню Формат выберите Автоформат, в появившемся окне
найдите в списке форматов Цветной2, нажмите ОК. Заданный автоформат применится к выделенной таблице.
10. На основе созданной и отформатированной таблицы постройте круговую диаграмму, показывающую рост
сбыта в Западном районе в марте: выделите интервал В5:В8, затем при нажатой клавише Ctrl выделите диапазон
Е5:Е8. Щелкните по кнопке Мастер диаграмм в виде цветных столбиков на стандартной панели. В
появившемся окне во вкладке Стандартные в поле Тип выберите Круговая, в поле Вид – Объемный вариант
круговой диаграммы, нажмите Далее, в появившемся окне указания диапазона ничего менять не нужно, т.к.
диапазон был указан ранее, нажмите опять Далее.
11. Теперь в новом окне на третьем шаге во вкладке Заголовки в поле Название диаграммы введите надпись
Рост сбыта в марте. Во вкладке Легенда отмените пометку поля Добавить легенду, во вкладке Подписи
данных пометьте флажки имена категорий и значения. Нажмите Далее.
12. На четвертом шаге выбирается место расположения диаграммы (на имеющемся или новом листе).
Щелкните на переключателе имеющемся, если он не включен, и нажмите Готово.
13. На листе расположения исходной таблицы появится диаграмма. Щелкните на области диаграммы в том
месте, где расположены подписи частей диаграммы, появятся черные квадратики, измените размер шрифта на 8,
выбрав этот размер из списка на панели форматирования. Диаграмма должна выглядеть примерно так:
Рост сбыта в марте
8
Западный
10800
21500
3. Поставьте указатель мыши на маркер заполнения в нижнем правом углу ячейки D4 (указатель мыши примет
вид тонкого черного крестика). Перетащите маркер заполнения из ячейки D4 в ячейки E4, F4. Убедитесь, что
при перетаскивании названия месяцев Март и Апрель появились автоматически.
4. Заполните ячейки следующим образом:
Е5:20800, F5:15000
Е6:27890, F6:18900
Е7:19850, F7:17000
Е8:37550, F8:18200
5. В ячейку В9 введите запись Итого. Выделите ячейку С9, щелкните по кнопке Автосумма в виде значка
обозначения суммы ∑ на стандартной панели инструментов. В ячейке С9 появится формула, а диапазон
суммирования будет обведен пунктирной мигающей рамкой. Нажмите Enter, в ячейке появится результат
вычисления по формуле.
6. Поставьте указатель мыши на маркер заполнения в нижнем правом углу ячейки С9 (указатель мыши примет
вид тонкого черного крестика). Перетащите маркер заполнения из ячейки С9 в ячейки D9, E9, F9. Убедитесь, что
формула скопирована в эти ячейки, причем адреса изменились в зависимости от суммируемых ячеек.
7. Отсортируйте данные по убыванию сбыта за март: выделите мышью диапазон ячеек B4:F8, в меню Данные
выберите пункт Сортировка, в появившемся окне Сортировка диапазона в списке Сортировать по выберите
Март, рядом щелкните переключатель по убыванию, нажмите ОК.
Восточный;
19 850,00р.
Западный;
37 550,00р.
Северный ;
20 800,00р.
Южный;
27 890,00р.
14. Теперь постройте гистограмму, показывающую динамику сбыта в Северном районе на основе той же
таблицы: выделите диапазон B7:F7, вызовите мастер диаграмм, в появившемся окне во вкладке Стандартные в
поле Тип выберите Гистограмма, в поле Вид – Обычная гистограмма отображает значения различных
категорий, нажмите Далее.
15. На следующем шаге запрашивается диапазон данных, на он был указан ранее. Выберите вкладку Ряд, а в
ней, в поле Подписи оси Х введите диапазон C4:F4, нажимайте Далее.
16. На третьем шаге в появившемся окне выберите вкладку Заголовки, в поле Название диаграммы введите
запись Динамика сбыта в Северном районе, в поле Ось Х (категорий) введите Месяц, в поле Ось У (значений) –
Сумма, во вкладке Легенда уберите пометку Добавить легенду, нажмите Далее, затем Готово.
17. На выведенной диаграмме щелкните дважды на области оси У- Ось значений, в появившемся окне
Формат оси выберите вкладку Шкала, а в ней установите следующие значения: минимальное значение – 0,
максимальное значение – 25000, цена основных делений – 5000, цена промежуточных делений – 1000, нажмите
ОК. Гистограмма будет выглядеть следующим образом:
Сумма
Динамика сбыта в Северном
районе
30 000,00р.
20 000,00р.
10 000,00р.
- р.
Месяц
8.
Представьте объем сбыта в денежном формате: выделите интервал С5:F9, нажмите кнопку Денежный
формат в виде прямоугольника с монетками на панели форматирования. Если в некоторых ячейках после
форматирования появились знаки ######, то это означает, что столбцы узки для вывода на экран числа в новом
18. Сохраните вашу работу предварительно уменьшив и расставив диаграммы так, чтобы они были видны
полностью вместе с таблицей.
Электронные таблицы Excel
Лабораторная работа №6
13. Расположите табельные номера по центру, для чего маркируйте содержащую их область (А4:А12).
Ввод данных и корректировка информации в электронной таблице
1.
2.
Запустите программу Excel.
Введите в таблицу следующий документ (порядок ввода данных документа рассматривается ниже).
A
B
C
D
E
F
Ф .И .О .
Я н в а рь
Ф ев ра ль
М а рт
А п рель
15.
G
С в е д е н и я о зараб о т н о й п л ат е с о т руд н и к о в
та б ельн ы й №
Нажмите кнопку в пиктографическом меню
1
С и д оров А .Г .
320
360
400
400
2
И в а н ов П .П .
300
450
400
420
6
Е м ельян ов Е .О .
340
430
420
480
4
С ем ен ов а А .А .
360
370
410
430
3
Б елокон ь П .Н .
430
390
390
410
7
И в а н ов И .И .
420
420
400
400
9
Ры б и н С .В .
350
380
370
400
5
П етров В .Н .
300
400
390
410
8
К етков В .Н .
370
410
400
450
И того
И т о го
(“По центру”).
14. Расположите название первого столбца в две строчки, для чего сделайте активной ячейку А3. По команде
16.
17.
18.
19.
20.
“ФОРМАТ ЯЧЕЕК” после открытия диалогового окна “ФОРМАТ ЯЧЕЕК” на листе “Выравнивание”
включите переключатель “Переносить по словам” и нажмите кнопку ОК.
Измените ширину второго столбца. Подведите курсор мыши к координатной ячейке столбца В справа.
Курсор трансформируется в двунаправленную стрелку. Отбуксируйте вправо на нужное расстояние
разделитель столбцов и отпустите кнопку мыши.
Расположите в центре ячейки название столбцов документа.
Заключите аналогичным способом в рамку содержимое подстроки «Итого» (А13:G13).
Измените шрифт для заголовка документа, используя команду контекстно-зависимого меню “ФОРМАТ
ЯЧЕЕК” и лист “Шрифт”. Поэкспериментируйте с различными характеристиками оформления текста,
такими как шрифт, стиль, размер, эффект. Выберите наиболее удачный с Вашей точки зрения вариант
шрифта для заголовка.
Измените также шрифт текста в последней строке таблицы, для чего используйте кнопку в
пиктографическом меню
(“ПОВТОРИТЬ”). Команда “Повторить” ускоряет работу в ситуации, когда
одна и та же операция выполняется над различными объектами.
Если Вы успешно справились с каждой операцией по вводу данных и оформлению внешнего вида
документа, то в конечном итоге Ваш документ должен приобрести следующий вид
С в е д е н и я о за р а б о т н о й п л а т е с о т р у д н и к о в
Вводимый документ
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
Ввод данных осуществляется в активную ячейку. Введите данные в пока пустую электронную таблицу в
следующем порядке.
Сделайте активной ячейку В2 и введите название таблицы “Сведения о заработной плате сотрудников”.
Обратите внимание, что вводимая информация дублируется в строке формул. Для окончания ввода текста в
ячейку необходимо или нажать клавишу <Enter>, или перевести табличный курсор в другую ячейку.
Сделайте активной ячейку А3 и введите название первой колонки “табельный номер”. Введенная
информация размещается пока в нескольких ячейках.
Переведите курсор мыши в ячейку В3 и введите название второй колонки “Ф.И.О.”.
Аналогично введите названия остальных столбцов документа.
Введите собственно данные документа за исключением столбца и строки “Итого”, для его расчета
выполните следующие операции.
Сделайте активной ячейку «Итого»-«Сидоров». Нажмите кнопку в пиктографическом меню
(“Автосуммирование”), в ячейке появится формула =СУММ(С4:F4). Нажмите <Enter>. В ячейку G4 будет
внесено число 1470, которое получено при сложении С4+D4+E4+F4=320+350+400+400=1470, а в строке
формул будет записана соответствующая расчетная формула.
Сделайте активной ячейку «Итого»-«Иванов». Вызовите команду “ВСТАВКА/ФУНКЦИЯ”. В списке
“Категория” выберите класс функций “Математические”. В списке “Функция” с помощью кнопок линейки
прокрутки найдите и выделите функцию СУММ, нажмите кнопку «Далее». В строку аргумента введите с
помощью мыши диапазон суммируемых ячеек, нажмите «Enter».
Скопируйте содержимое ячейки G5 в ячейки G6:G12, для чего сделайте активной ячейку G5. Нажав правую
кнопку мыши, вызовите контекстное меню, в котором выделите команду “КОПИРОВАТЬ”. Ячейка G5
будет помещена в рамку с движущимися элементами. Маркируйте (т.е. сделайте активной) область G6:G12
и нажмите <Enter>. Любым из способов заполните ячейки, в каждой из которых содержится сумма
заработной платы сотрудников отдела за соответствующий месяц.
Для улучшения внешнего вида таблицы выделите рамкой названия столбцов. Маркируйте область,
названий столбцов таблицы (A3:G3). Вызовите команду через контекстно-зависимое меню “ФОРМАТ
ЯЧЕЕК” и перейдите к листу “Рамка”. Определите положение рамки и тип линии. Нажмите кнопку ОК.
та б ел ь н ы й №
Ф .И .О .
Я нварь
Ф ев р а л ь
М арт
А п р ел ь
И то го
1
С и д о р о в А .Г .
320
360
400
400
1470
2
И в а н о в П .П .
300
450
400
420
1570
3
Е м ел ь я н о в Е .О .
340
430
420
480
1670
4
С ем ен о в а А .А .
360
370
410
430
1570
5
Б ел о к о н ь П .Н .
430
390
390
410
1620
6
И в а н о в И .И .
420
420
400
400
1640
7
Р ы б и н С .В .
350
380
370
400
1500
8
П етр о в В .Н .
300
400
390
410
1500
9
К етк о в В .Н .
370
410
400
450
1630
3190
3600
3580
3800
14170
И т о го
Итоговый документ
Использование Мастера функций Excel для расчетов
Лабораторная работа № 7
Размер ежемесячных выплат
Платежи по процентам за первый месяц
«Расчеты статистической информации с помощью статистических и математических функции; расчет
экономической задачи с помощью финансовых функций»
1.
2.
3.
Создайте новую рабочую книгу или используйте открытую по умолчанию.
Первый лист назовите Статистика, второй – Финансы
В первый лист в ячейку А1 введите название столбца – Случайное число, введите данные в диапазон
А2:А13:
4,407678
4.
5.
6.
7.
8.
9.
10.
11.
12.
1)
2)
3)
4)
5)
13.
1)
9,914596
3,361304
3,48115
0,49059
1,194127
8,629884
6,119983
2,65438
В листе Финансы создайте таблицу:
Количество лет
Проценты
Основные платежи за первый месяц
2)
3)
4)
8,779637
7,755563
5,058417
В ячейке В1 напечатайте заголовок Округление, выделите ячейку В2, щелкните по кнопке вызова
Мастера функций (fx)
В поле Категория открывшегося окна выберите Математические, в поле Функция найдите в
списке и щелкните мышью на функции ОКРУГЛ(). Нажмите ОК.
Для выбранной функции укажите 2 параметра: в поле Число введите А2, в поле Число_разрядов (
или Количество_цифр) – 2(количество значащих цифр после запятой).
Скопируйте формулу во все ячейки столбца В: наведите указатель мыши на маркер заполнения
(черный крестик в правом нижнем углу табличного курсора), не отпуская кнопки мыши протяните
прямоугольный контур до ячейки В13.
Составьте еще 3 колонки: Корень (ячейка С1), Целое (ячейка D1), Факториал (ячейка Е1).
Для создания третьего столбца С, содержащего квадратные корни из соответствующих ячеек столбца
В, используйте математическую функцию КОРЕНЬ, где параметр – значение ячейки В2.Скопируте
формулу для всех значений столбца В.
Для записи значений в четвертый столбец D, содержащий целые значения соответствующих ячеек
столбца С, используйте матем. функцию ЦЕЛОЕ.
Для записи значений в пятый столбец Е, содержащий факториалы чисел, расположенных в
соответствующих ячейках столбца D, используйте матем. функцию ФАКТР.
На базе полученных выборок составьте вторую таблицу:
В столбце А, начиная с ячейки А15, расположите названия:
Среднее значение
Дисперсия
Среднеквадратичное отклонение
Медиана
Расширьте ячейки, если названия в них не помещаются. Выделите заголовки полужирным шрифтом.
Выделите ячейку В15, вызовите мастер функций и в открывшемся окне в поле Категория выберите
Статистические, в поле Функция – СРЗНАЧ, диапазон укажите – В2:В13
Скопируйте формулу в ячейки C15, D15, E15.
Для вычисления дисперсии используйте статистическую функцию ДИСП, для среднеквадратического
отклонения – КВАДРОТКЛ, для медианы – МЕДИАНА.
С помощью некоторых финансовых функций решите экономическую задачу:
Клиент желает взять 25-летнюю ссуду в размере 1 000 000$ под 8% годовых. Определите величину
ежемесячных выплат, выплат по процентам и основных выплат за указанный период.
Размер ссуды
Платежи по процентам за последний месяц
1 000 000р.
25
8%
5)
6)
Основные платежи за последний месяц
Ячейке В1 задайте денежный формат через Формат ячеек, В3 – процентный.
Назначьте ячейке В4 финансовую функцию ППЛАТ (или ПЛТ) через мастер функций. В поле Норма
(или Ставка) укажите норму месячной ставки В3/12, в поле Кпер – число периодов или время
вложения в месяцах – В2*12, в поле Нз (или пс) - размер ссуды (В1). Параметры Бс и Тип указывать
необязательно. Нажмите ОК.
Назначьте ячейке В5 функцию ПЛПРОЦ (или ПРПЛТ) через мастер функций. В поле Норма (или
Ставка) укажите норму месячной ставки В3/12, в поле Период – заданный период в месяцах (1), в
поле Кпер – число периодов или время вложения в месяцах – В2*12, в поле Тс (или пс) - размер
ссуды (В1). Параметры Бс и Тип указывать необязательно. Нажмите ОК.
Назначьте ячейке В6 функцию ПЛПРОЦ (или ПРПЛТ) через мастер функций, указав в поле Период
значение 300 (количество месяцев за 25 лет выплаты ссуды).
С помощью финансовой функции ОСНПЛАТ(или ОСПЛТ) заполните значениями ячейки В7 и В8
таблицы, задав в поле Период сначала 1 затем 300
Результат ваших вычислений должен быть следующим
Размер ссуды
Количество лет
Проценты
25
8%
Размер ежемесячных выплат
7 718,16р.
Платежи по процентам за первый месяц
6 666,67р.
Платежи по процентам за последний месяц
Основные платежи за первый месяц
7)
1 000 000р.
Основные платежи за последний месяц
Сохраните рабочую книгу…
51,11р.
1 051,50р.
7 667,05р.
Электронные таблицы Excel
Лабораторная работа № 8
«Подведение промежуточных итогов, структуризация рабочих листов»
Создайте новую рабочую книгу (щелкнув пиктограмму в виде листа с загнутым краем на панели
инструментов Стандартная либо выбрав команду Файл – Создать или используйте открытую по
умолчанию.
Заполните лист рабочей книги следующим образом
1.
2.
A
B
2
NN
Фирма
3
1
«Василек»
4
2
«Василек»
1
E
F
D
Проплата счетов за I квартал 1999 года
Дата
Сумма в
Дата
N счета
счета
счете
оплаты
200р.
20.01.99
21
10.01.99
C
22
G
Сумма
оплаты
200р.
03.02.99
200р.
02.03.99
200р.
20.02.99
200р.
5
3
«Василек»
23
17.02.99
200р.
6
4
«Гвоздика»
41
05.03.99
400р.
15.03.99
300р.
7
5
«Ландыш»
11
05.01.99
100р.
12.01.99
100р.
30.01.99
400р.
8
6
«Ландыш»
12
20.01.99
400р.
9
7
«Ландыш»
13
10.02.99
400р.
18.02.99
250р.
10
8
«Ландыш»
13
10.02.99
0р.
19.03.99
150р.
300р.
23.01.99
150р.
11
9
«Ромашка»
31
15.01.99
12
10
«Ромашка»
31
15.01.99
0р.
02.02.99
150р.
13
11
«Ромашка»
32
12.02.99
300р.
18.02.99
300р.
300р.
25.02.99
14
6.
7.
H
Долг
300р.
12
33
«Ромашка»
18.02.99
3. В ячейку Е15 введите формулу =СУММ(Е3:Е14), в G15 введите =СУММ(G3:G14) или
воспользуйтесь Автосуммой.
4. В ячейку Н3 введите формулу =E3-G3, с помощью маркера заполнения распространите формулу для
ячеек H4:H14. В ячейку Н15 введите формулу =СУММ(Н3:Н14).
5. Для подведения итогов по каждой фирме выполните команду Данные – Итоги. В появившемся окне
Промежуточные итоги введите следующие данные (все остальные пометки уберите, если они были
установлены по умолчанию):
8.
9.
После нажатия ОК получится таблица с подведенными итогами по каждой фирме. Обратите внимание
на появление в левой части рабочей книги символов структуры, которые позволяют скрывать или
показывать детальную информацию по группам. Например, Вам не нужна на экране подробная
информация о фирмах «Гвоздика» и «Ландыш». Нажмите слева на кнопки со знаком « – » напротив 8ой и 13-ой строк таблицы. На экране останутся лишь итоговые данные по этим фирмам.
Теперь проведем операцию для возможности временного удаления с экрана столбцов Номер счета и
Долг. Выделите столбец С, наведя и щелкнув указатель мыши на область обозначения столбца до
появления вертикальной черной стрелки. Затем выберите команду Данные – Группа и структура –
Группировать. Те же действия сделайте со столбцом Н. Вверху Вы увидите аналогичные знаки
структуры, что и слева. При нажатии « – » или кнопки «1» столбцы исчезнут, при нажатии « + » или
«2» появятся вновь.
Рассмотрим более сложную задачу: необходимо подвести промежуточные итоги I квартала по
месяцам, а внутри каждого месяца по фирмам. Для этого необходимо сделать следующее: прежде
всего отмените итоговые вычисления выполнив команду Данные – Итоги, и нажав в появившемся
окне кнопку Убрать все. Затем выделите ячейку Е2, выполните команду Вставка – Столбцы,
появится новый столбец. Введите в Е2 заголовок Месяц в счете, в ячейку Е3 формулу =МЕСЯЦ (D3).
Распространите формулу с помощью маркера заполнения на диапазон Е4:Е14.
В полученной таблице выделите диапазон B2:I14. Выполните сортировку: Данные – Сортировка:
10. Затем, не снимая выделения диапазона выполните дважды операцию подведения итогов – сначала по
столбцу Месяц в счете (в поле Добавить итоги по выделите Сумма в счете, Сумма оплаты и Долг;
снимите выделение Заменить текущие итоги), затем по столбцу Фирма:
11. Сохраните работу.
Электронные таблицы Excel
Лабораторная работа № 9
«Связывание ячеек и консолидация рабочих листов»
1)
2)
Если количество строк в таблице велико, то подведение итогов способом, описанным в лабораторной
работе №3, становится неудобным. Выходом из ситуации является разбиение набора данных на части по какомуто признаку и размещение этих частей на отдельных листах рабочей книги или в отдельных рабочих книгах, и
использование для обработки таких данных специальных приемов обработки, имеющихся в Excel.
1. Создайте новую рабочую книгу (щелкнув пиктограмму в виде листа с загнутым краем на панели
инструментов Стандартная либо выбрав команду Файл – Создать или используйте открытую по
умолчанию.
2. Создадим совокупность данных, разделенную на разные листы рабочей книги. Заполните первый лист
рабочей книги (лист назовите Январь) следующим образом:
Если в рабочей книге только три листа, то добавьте ещё один: выполните команду Вставка –
Лист, после появления листа захватите мышью ярлычок листа и перенесите его на последнее
место после третьего листа. Назовите лист Итоговая таблица.
На листе Январь выделите диапазон A2:H7, выполните команду Правка – Копировать,
перейдите на лист Итоговая таблица, активизируйте ячейку А2 и выполните команду Правка –
Специальная вставка. В появившемся окне пометить данные, как показано ниже на рисунке1 и
нажмите Вставить связь, затем не снимая выделения выполнить
команду Правка –
Специальная вставка, но отметив в появившемся окне данные, как показано на рисунке 2 и
нажав ОК.
Проплата счетов за январь 1999 года
NN
Фирма
1
«Василек»
2
3
«Ландыш»
«Ландыш»
Дата
счета
N счета
21
11
12
Сумма
в счете
Сумма
оплаты
Долг
10.01.99
200р.
20.01.99
200р.
0р.
05.01.99
100р.
12.01.99
100р.
0р.
20.01.99
400р.
30.01.99
400р.
0р.
23.01.99
150р.
150р.
02.02.99
150р.
-150р.
4
«Ромашка»
31
15.01.99
300р.
5
«Ромашка»
31
15.01.99
0р.
3.
Дата
оплаты
Заполните второй лист рабочей книги (лист назовите Февраль) следующим образом:
Проплата счетов за февраль 1999 года
Фирма
NN
«Василек»
1
Сумма в
счете
Дата
оплаты
Сумма
оплаты
N счета
Дата счета
Долг
22
03.02.99
200р.
02.03.99
200р.
0р.
20.02.99
200р.
0р.
2
«Василек»
23
17.02.99
200р.
3
«Ландыш»
13
10.02.99
400р.
18.02.99
250р.
150р.
19.03.99
150р.
-150р.
4
«Ландыш»
13
10.02.99
0р.
5
«Ромашка»
32
12.02.99
300р.
18.02.99
300р.
0р.
6
«Ромашка»
33
18.02.99
300р.
25.02.99
300р.
0р.
рисунок 1
рисунок 2
На листе Февраль выделите диапазон А3:H8, выполните команду Правка – Копировать,
перейдите на лист Итоговая таблица, активизируйте ячейку А8 и выполните те же операции что
и с данными листа Январь.
4) На листе Март выделите диапазон А3:H3, выполните команду Правка – Копировать, перейдите
на лист Итоговая таблица, активизируйте ячейку А14 и выполните те же операции что и с
данными предыдущих листов.
5) Теперь с этой таблицей можно выполнять сортировку и подведение итогов. Заметьте, что при
внесении изменений в таблицы, расположенные в листах Январь, Февраль и Март, в таблице
Итоговая таблица изменения произойдут автоматически. Это происходит потому, что
установлены связи между ячейками (отметьте на листе Итоговая таблица любую заполненную
ячейку, например С7, и Вы увидите, что в строке формул отражена формула с адресом ячейки С7
листа Январь (=Январь!С7))
Воспользуемся теперь другими возможностями Excel для подведения итогов в разделенных таблицах –
консолидацией данных. Идея консолидации – автоматическое получение итоговых результатов на
3)
4.
Заполните третий лист рабочей книги (лист назовите Март) следующим образом:
Проплата счетов за март 1999 года
NN
1
5.
Фирма
N счета
Дата счета
«Гвоздика»
41
05.03.99
Сумма в
счете
400р.
Дата
оплаты
15.03.99
Сумма
оплаты
300р.
Долг
100р.
Рассмотрим вариант связывания ячеек новой таблицы с соответствующими ячейками исходной
таблицы. Исправления, внесенные в ячейки исходных таблиц, автоматически проявляются и в
итоговой таблице:
6.
основе данных, которые могут быть расположены в разных местах, или, применительно к
рассматриваемой задаче, автоматическое выполнение основных действий варианта связывания ячеек,
в которых суммируются данные с трех рабочих листов в одной итоговой таблице.
1) Создайте новый рабочий лист и назовите его Консолидация. В ячейку А1 введите заголовок
Итоги за первый квартал 1999 года, сделайте активной ячейку А2.
2) Выполните команду Данные – Консолидация.
3) В появившемся окне укажите в поле Ссылка по очереди адреса диапазонов трех рабочих листов
следующим образом: перейдите на лист Январь и выделите мышью диапазон B2:H7, нажмите
кнопку Добавить, затем перейдите на лист Февраль и выделите мышью диапазон B2:H8,
нажмите кнопку Добавить, затем перейдите на лист Март и выделите мышью диапазон B2:H3,
нажмите кнопку Добавить, отметьте все флажки, окно примет вид, как показано ниже, нажмите
ОК.
Применение Excel для анализа данных
Лабораторная работа № 10
«Анализ данных с помощью сводных таблиц, группировки данных»
1.
Ф.И.О.
2.
4)
Создайте в новой книге базу данных:
Расход по складу стройматериалов
Цена
Количество
Сумма
Дата
Иванов
кирпич
700
5
3500
02.01.96
Петров
вагонка
900
6
5400
06.01.96
Сидоров
вагонка
900
4
3600
12.03.96
Иванов
кирпич
800
20
16000
15.03.96
Петров
вагонка
950
2
1900
19.04.96
Иванов
кирпич
750
8
6000
22.05.96
Иванов
цемент
500
3
1500
24.06.96
Сидоров
вагонка
950
2
1900
24.07.96
Петров
кирпич
800
15
12000 24.10.96
Создайте сводную таблицу, в которой отражались бы данные о том, на какую сумму каждое
материально-ответственное лицо получило конкретные материалы в каждом квартале, и итоговые
данные за год:
1) Выделите диапазон А1:F11 и присвойте ему имя База_данных выбрав команду Вставка –
Имя – Присвоить…
2) Выделите какую-нибудь ячейку в списке, выберите команду Данные – Сводная таблица,
появится диалоговое окно Мастер сводных таблиц и диаграмм:
Полученную таблицу отредактируйте (увеличьте или уменьшите, где необходимо, ширину
столбцов и высоту строк, расположите надпись таблицы относительно её середины, отсортируйте
список по возрастанию поля Долг). Вы должны получить следующую таблицу и общий вид
рабочей книги:
3)
7.
Наименование
материала
В этой таблице есть недостатки (суммировались поля N счета), но их легко устранить удалив весь
столбец С, т.к. он не нужен в рамках поставленной задачи – сумму в счете и сумму оплаты за первый
квартал для всех месяцев с помощью консолидации мы определили. Сохраните работу.
Пометьте переключатели как показано на рисунке выше и нажмите Далее. Появится окно
запроса на диапазон
4)
Укажите диапазон данных, по которым вы хотите создать сводную таблицу. Если
изначально диапазон указан верно, то нажмите Далее
5) После нажатия кнопки Готово появится окно на запрос диапазона, куда
поместить
созданную сводную таблицу. Укажите его по вашему усмотрению, но чтобы основная база
данных была видна.
6) На основе сводной таблицы создайте диаграмму, выбрав на панели инструментов Сводные
таблицы команду Сводная таблица– Выделить – Заголовки и данные, затем на панели
инструментов Сводные таблицы нажать пиктограмму Мастер диаграмм, в рабочую книгу
вставится лист с диаграммой (тип диаграммы Линейчатая выберите щелкнув правой кнопкой
мыши по области диаграммы и выбрав в появившемся контекстном меню Тип диаграммы).
3. Сгруппируйте данные, создав категорию более высокого уровня: выделите одну из ячеек Дата
сводной таблицы, на панели инструментов Сводные таблицы щелкните кнопку Группировать, после
чего выберите в диалоговом окне группировку по кварталам, также группировку по кварталам можно
сделать выбрав команду Сводная таблица – Группа и структура – Группировать – выбрать
группировку по кварталам.
4. Сохраните файл…
А) В следующем диалоговом окне, если вы используете версию Office XP, следует указать место
вставки сводной таблицы. Затем нажмите Готово, появиться новый рабочий лист
с
разделенными полями, в которые нужно вставить данные по аналогии, указанной в следующем
п.6
Применение Excel для анализа данных
Лабораторная работа №11
Б) В версии Office 98, 2000 появится макет сводной таблицы, перенесите в поле Строка имена
столбцов исходной таблицы (см. кнопки макета справа) Ф.И.О. и Наименование материала, в
поле Столбец – Дата, в поле Данные – Сумма, в поле Страница – Ф.И.О., у вас должен
получиться примерно следующий макет:
При разметке таблицы перетащите кнопки полей в области:
СТРОК
- для использования данных поля в качестве заголовков строк
СТОЛБЦОВ
- для использования данных поля в качестве заголовков
столбцов
ДАННЫХ - для суммирования значений поля в ячейках таблицы
ВНЕ ТАБЛИЦЫ
- для вывода данных в таблице, относящихся только к этому
полю
Ф.И.О
.
Страниц
а
Ф.И.О
.
Наменов
Строка
№
Дата
Построение сводной таблицы и диаграммы по данным таблицы
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
Дата
Ф.И.О.
Столбец
12.
Наименов
Сумма по полю
Сум
13.
Цена
14.
Количест
Данные
Сумма
Отмена
<Назад
Далее>
Для изменения полей дважды щелкните их
Готово
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
Запустите программу Excel и откройте рабочую книгу лабораторные.xls, созданную ранее.
Откройте рабочий лист Сведения о поставках.
Сделайте текущей ячейку в пределах базы данных. Дайте команду Данные - Сводная таблица.
Убедитесь, что установлены переключатели в списке или базе данных Microsoft Excel и сводная таблица.
Щелкните на кнопке Далее.
Убедитесь, что диапазон базы данных выбран правильно. Щелкните на кнопке Далее.
Убедитесь, что установлен переключатель Новый лист. Щелкните на кнопке Макет.
Перетащите кнопки в соответствующие области макета сводной таблицы: кнопку Месяц — в область
Страница, кнопку Поставщик — в область Столбец, кнопку Товар — в область Строка, кнопку Объем - в
область Данные.
Кнопка в области Данные будет иметь вид Сумма по полю Объем. Щелкните на кнопке ОК.
Щелкните на кнопке Параметры. В поле Имя введите текст - Сводная таблица поставок. Щелкните на
кнопке ОК. Щелкните на кнопке Готово.
Переименуйте рабочий лист со сводной таблицей, дважды щелкнув на его корешке. Дайте ему имя Сводная
таблица поставок.
Посмотрите на готовую сводную таблицу. Она показывает, сколько материалов определенного типа
пришло от конкретного поставщика, независимо от времени поставки. Дважды щелкните на любой из ячеек
сводной таблицы, чтобы увидеть на новом рабочем листе записи, на основе которых сформированы данные
в этой ячейке.
Выберите конкретный месяц в раскрывающемся списке Месяц, чтобы увидеть данные, относящиеся к
этому месяцу.
Перетащите кнопку Месяц в область Столбец, а кнопку Товар — в область Страница. Сводная таблица
автоматически перестроится в соответствии с новой структурой.
Дважды щелкните на кнопке Сумма по полю объем. В открывшемся диалоговом окне Вычисление поля
сводной таблицы щелкните на кнопке Дополнительно. В раскрывающемся списке Дополнительные
вычисления выберите пункт Доля от суммы по строке. Щелкните на кнопке ОК.
Посмотрите на новый вид сводной таблицы.
Сохраните рабочую книгу лабораторные.xls.
Щелкните на кнопке Мастер диаграмм на панели инструментов Сводная таблица. Сводная диаграмма
строится автоматически на новом рабочем листе.
Переименуйте этот рабочий лист, дав ему имя Сводная диаграмма.
Поэкспериментируйте со сводной диаграммой, чтобы выяснить, какие ее параметры можно изменять.
Выполните фильтрацию отображаемых данных.
Измените величину, отображаемую на диаграмме.
Переместите поле базы данных в другую область диаграммы.
Измените тип диаграммы.
Измените формат отображения элементов диаграммы.
Применение Excel для анализа данных
Лабораторная работа№ 12
12. В диалоговом окне имеется блок "Использовать в качестве имен" из двух флажков "подписи верхней
строки" и "значения левого столбца". Установите оба флажка. Эти флажки нужно установить, потому что
информация в таблице будет идентифицироваться по названиям строк и столбцов.
13. Щелкните по кнопке "ОК" на рабочем листе появится таблица:
Сделки
Объем
Решение задач с помощью построения консолидирующей таблицы
Задача: На рабочих листах с именами "январь", "февраль", "март" приведены фамилии торговых агентов и
количество сделок, которые они совершили в течение месяца. Построить сводку за первый квартал
1.
2.
3.
4.
Запустите программу Excel. Создайте новую рабочую книгу. Вставьте дополнительный четвертый лист
командой Правка - Вставить - Лист. Переименуйте листы: дайте им названия "Январь", "Февраль", "Март",
"1 квартал".
Внесите заголовки одновременно в несколько листов: Выделите все листы с названиями месяцев: для
этого перейдите на лист "Январь", нажмите клавишу Shift и, не отпуская ее, щелкните по ярлычку листа
"Март". Будут выделены все листы рабочей книги, при этом активным листом остается "Январь".
Введите в ячейку А1 "Ф.И.О.", в ячейку В1 "Сделки", в С1 "Объем". Щелкните по ярлычку листа
"Февраль", выделение с нескольких листов будет снято. Убедитесь, что в ранее выделенные листы внесен
один и тот же текст в ячейки A1, В1, С1. Для иллюстрированных целей поменяйте на листе "Февраль"
содержимое ячеек: в В1 "Объем", а в С1"Сделки".
Введите в листы с названиями месяцев информацию в соответствии с таблицами (названия месяцев
вводить не нужно, они на ярлычках листов).
Ф.И.О.
Иванов И.И.
Антонов А.В.
Медведев К.Л.
Сделки
6
12
7
Ф.И.О.
Объем
250
430
180
Сидоров И.Н.
Иванов И.И.
Исходные данные Лист Январь
Объем
200
220
Сделки
Иванов И. И.
26
670
Сидоров И.Н.
15
500
Антонов А.В.
20
580
Медведев К.Л.
13
400
Консолидирующие данные
14. Обратите внимание, что заголовок "Фамилии" отсутствует.
15. Внесите изменения в один из диапазонов консолидации, например увеличив количество сделок,
записанных за Ивановым, то таблица на листе "1 квартал" не изменится, так как не был установлен флажок
"создавать связи с исходными данными".
16. Выполнить команду Данные - Консолидация, в диалоговом окне ничего не менять, только щелкнуть "ОК",
то произойдет обновление таблицы.
17. Для установления связей между таблицами выделите на листе "1 квартал" ячейку А1. Откройте диалоговое
окно "Консолидация" и установите флажок "создавать связи с исходными данными". Таблица изменилась.
18. Выполните подгонку ширины столбцов.
5
8
Применение Excel для анализа данных
Лабораторная работа №13
Формирование структуры таблицы
Исходные данные Лист Февраль
Задание1 Форматирование и заполнение ее постоянными данными
Ф.И.О.
Иванов И.И.
Сидоров И.Н.
Антонов А.В.
Медведев К.Л.
Сделки
12
10
8
б
Объем
200
300
150
220
Подготовьте электронную экзаменационную ведомость, форма которой представлена на рис. 1.
Исходные данные Лист Март
Обратите внимание, что фамилии в листах идут в полном беспорядке, заголовки столбцов тоже перепуганы
(но фамилии всегда в первом столбце!).
6. Консолидация. Для консолидации нужно выделить ячейку, которая будет служить верхней левой ячейкой
для блока с результатами консолидации. Для этого перейдите на лист "1 квартал" и выделите ячейку А1.
7. Выполните команду меню Данные - Консолидация. Появится диалоговое окно "Консолидация". В
выпадающем списке "Функция:" выставлено "Сумма", т.е. выбранные данные будут суммироваться. Но
можно выбрать и другую итоговую функцию: посмотрите список.
8. Для поля: "Ссылка", выполните последовательность действий: установив фокус ввода в этом поле, по
очереди выделяйте диапазоны для консолидации; когда в этом поле появится очередной диапазон, щелкнем
кнопку "Добавить" — адрес диапазона переместится в окно "Список диапазонов".
9. Щелкните мышью в поле "Ссылка:", затем щелкните по ярлычку листа "Январь" (в поле ввода появится
"Январь!" — формируется адрес).
10. Выделите блок А1:С4 (в поле ввода "Январь!$А$1:$С$4") — вокруг блока бегущая пунктирная рамка.
11. Щелкните кнопку "Добавить" — адрес диапазона окажется в поле "Список диапазонов:" Аналогично
предыдущему пункту добавьте диапазоны "Февраль!$А$1:$С$3" и "Март!$А$1:$С$5". Список диапазонов
консолидации будет сформирован.
5.
Рис. 1. Форма экзаменационной ведомости
Технология работы:
1. Запустите программу Excel (Пуск —› Программы —› Microsoft Excel) и создайте новую рабочую книгу
(команда Файл —› Создать или кнопка Создать на стандартной панели инструментов).
2. Введите в указанные ячейки (табл. 2) тексты заголовка и шапки таблицы, после чего отформатируйте
ячейки и данные.
Таблица 2
Содержимое ячеек,
в которых располагаются название таблицы и метки столбцов
Адрес ячейки
Вводимый текст
B1
ЭКЗАМЕННАЦИОННАЯ ВЕДОМОСТЬ
A3
Группа №
C3
Дисциплина
A5
№ п/п
B5
Фамилия, имя, отчество
C5
№ зачетной книжки
D5
Оценка
E5
Подпись экзаменатора
3. Заполните ячейки столбца B данными о студентах учебной группы (приблизительно 10-15 строк) и
отформатируйте данные.
4. Присвойте каждому студенту порядковый номер: введите в ячейку A6 число 1; установите курсор в
нижний правый угол ячейки A6 так, чтобы указатель мыши приобрел изображение креста и, нажав правую
кнопку мыши, протяните курсор на требуемый размер; выполните команду локального меню Заполнить.
5. После списка студентов в нижней части таблицы введите в ячейки столбца A текст итоговых строк
согласно рис.1.
6. Объедините две соседние ячейки для более удобного представления текста итоговых строк. Для этого
выделите две ячейки; вызовите контекстное меню и выберите команду Формат ячеек; на вкладке
Выравнивание установите флажок Объединение ячеек и нажмите кнопку ОК.
7. Сохраните рабочую книгу в своей рабочей папке (имя файла - Session).
Задание 2. Технология работы с формулами
В рабочей папке с именем Session рассчитайте:
o количество неявок и оценок (отлично, хорошо, удовлетворительно, неудовлетворительно),
полученных в данной группе;
o общее количество полученных оценок.
Технология работы
1. Загрузите рабочую книгу с именем Session.
2. Введите названия (5, 4, 3, 2, неявки) соответственно в ячейки F5, G5, H5, I5, J5 вспомогательных столбцов.
3. Используя Мастер функций, введите в столбцы F5-J5 вспомогательные формулы. Рассмотрим эту
технологию на примере ввода формулы в ячейку F6:
o установите курсор в ячейку F6 и выберите мышью на панели инструментов кнопку Мастера функций;
o в 1-м диалоговом окне выберите вид функции (Категория - логические; Функция - ЕСЛИ) и щелкните
по кнопке ОК;
o во втором диалоговом окне введите соответствующие операнды логической функции (Логическое
выражение - D6 = 5; значение_если_ истина - 1; Значение_если_ ложно - 0) и щелкните по кнопке ОК.
4. С помощью Мастера функций введите формулы аналогичным способом в остальные ячейки данной
строки.
5. Скопируйте эти формулы во все остальные ячейки дополнительных столбцов:
o выделите блок ячеек F6:J6;
o установите курсор в правый нижний угол выделенного блока и после появления черного крестика,
нажав правую кнопку мыши, перетащите ее до конца таблицы;
o выполните в контекстном меню команду Заполнить значения.
6. Определите имена блоков ячеек по каждому дополнительному столбцу.
Рассмотрим эту технологию на примере столбца F:
o выделите все значения дополнительного столбца и введите команду Вставка —› Имя —› Присвоить;
o в диалоговом окне в строке Имя введите слово ОТЛИЧНО и щелкните по кнопке Добавить;
o проводя аналогичные действия с остальными столбцами, создайте еще имена блоков ячеек: ХОРОШО,
УДОВЛЕТВОРИТЕЛЬНО, НЕУДОВЛЕТВОРИТЕЛЬНО, НЕЯВКА.
7. Выделите столбцы F - J целиком и сделайте их скрытыми (команда Формат —› Столбец —› Скрыть).
8. Введите формулу подсчета суммарного количества полученных оценок определенного вида, используя
имена блоков ячеек с помощью Мастера функций. Покажем эту технологию на примере подсчета отличных
оценок:
o установите указатель мыши в ячейку С17 подсчета количества отличных оценок и щелкните по
кнопке Мастер функций;
o в диалоговом окне выберите: Категория - Математические, функция - СУММ; щелкните по кнопке
ОК;
o в следующем диалоговом окне в строке Число 1 установите курсор и введите команду Вставка —›
Имя —› Вставить;
o в появившемся диалоговом окне выделите имя блока Отлично и щелкните по кнопке ОК;
o повторите аналогичные действия для подсчета количества других оценок в ячейках С18 - С21.
9. Подсчитайте общее количество (ИТОГО) всех полученных оценок другим способом:
o установите курсор в пустой ячейке С21 и щелкните по кнопке <S>;
o выделите блок ячеек, где подсчитываются суммы по всем видам оценок, и нажмите клавишу Enter.
10. Переименуйте текущий лист:
o установите курсор на имени текущего листа и вызовите контекстное меню;
o выберите параметр Переименовать и введите новое имя, например Экзамен 1.
11. Скопируйте несколько раз текущий лист Экзамен 1:
o установите курсор на имени текущего листа и вызовите контекстное меню;
o выберите параметр Переместить/Скопировать, поставьте флажок Создавать копию и параметр
Переместить в конец; щелкните по кнопке ОК.
12. Сохраните рабочую книгу.
Задание 3. Подготовка ведомостей назначения студентов на стипендию по результатам
экзаменационной сессии (рис. 2)
Рис. 2. Форма стипендиальной ведомости
Алгоритм действий по технологии выполнения задания:
1. Загрузите экзаменационную ведомость.
2. На новом листе создайте ведомость стипендии и скопируйте в нее список группы из экзаменационной
ведомости.
3. Вычислите средний балл по результатам сдачи экзаменов по каждому студенту.
4. Используя минимальное значение стипендии и учитывая, что сданы все экзамены, введите формулу
начисления стипендии по условию:
o
o
o
если средний балл не менее 4,5, выплачивается 50%-ная надбавка к минимальной стипендии;
если средний балл от 3 (включительно) до 4,5, выплачивается минимальная стипендия;
если средний балл меньше 3, стипендия не выплачивается.
5. Подсчитайте сумму стипендиального фонда всей группы.
Технология работы
1. Загрузите рабочую книгу с именем Session.
2. Создайте в этой книге новый лист - Стипендия, на который из столбцов A и B листа Экзамен 1 скопируйте
фамилии и порядковые номера студентов.
3. Оформите название и шапку ведомости назначения на стипендию согласно рис. 10.
4. Укажите размер минимальной стипендии в ячейке D3.
5. Вставьте два дополнительных столбца перед столбцом Стипендия и введите их названия - Средний балл и
Кол-во сданных экзаменов. Сверьте полученное изображение электронной таблицы с рис. 3.
8. Введите в столбец D формулу подсчета количества сданных каждым студентом экзаменов с учетом
неявок. Технология ввода аналогична п. 6 (только в диалоговом окне выберите параметр Имя - СЧЕТ).
9. Скопируйте формулу по всем ячейкам столбца D (аналогично п. 7).
10. Введите формулу для вычисления размера стипендии студента в ячейку E6. Эта формула должна иметь
следующий вид:
=ЕСЛИ(И(С6>=4,5;D6=3);$D$3*1,5;ЕСЛИ(И(С6>=3;D6=3);$D$3;0)).
Технология ввода формулы аналогична описанной в п. 6.
11. Скопируйте эту формулу в другие ячейки столбца E (аналогично п. 7).
12. Проверьте работоспособность таблицы путем ввода других оценок в экзаменационную ведомость и
изменения минимального размера стипендии.
13. Сохраните рабочую книгу.
Применение Excel для анализа данных
Лабораторная работа №14.
Сортировка данных
Создайте книгу и сохраните ее под именем Spisok, переименуйте Лист1 на Список, а Лист2 - на
Сортировка.
2. На листе Список создайте таблицу, приведенную в табл. 4.
3. Произведите копирование списка (базы данных) с листа Список на лист Сортировка.
4. Сделайте сортировку на трех уровнях по возрастанию: по преподавателям, по номеру группы, по коду
предмета.
Технология работы
1. Проведите подготовительную работу в соответствии с п. 1 задания.
2. Сформируйте на листе Список шапку таблицы 4. Для этого:
o выделите первую строку, вызовите контекстное меню и выберите команду Формат ячеек;
o произведите форматирование ячеек первой строки, установив параметры на вкладке Выравнивание:
1.
По горизонтали: по значению
По вертикали: по верхнему краю
Переносить по словам: установить
флажок
Рис. 3. Электронная таблица Ведомость назначения на стипендию
6. Введите формулу начисления среднего балла для первого студента (ячейка С6):
o установите курсор в ячейке С6, вызовите Мастер функций и выберите в диалоговом окне параметры:
Категория - Статистические, Имя - СРЗНАЧ; щелкните по кнопке ОК;
o установите курсор в 1-й строке (имя Число 1) панели ввода аргументов функции, щелкните на
названии листа Экзамен 1 и выберите ячейку D6 с оценкой конкретного студента по первому
экзамену;
o установите курсор во 2-й строке (имя Число 2), щелкните на названии листа Экзамен 1(2) и выберите
ячейку D6 с оценкой того же студента по второму экзамену;
o установите курсор в 3-й строке (имя Число 3), щелкните на названии листа Экзамен 1(3) и выберите
ячейку D6 с оценкой того же студента по третьему экзамену;
o щелкните по кнопке ОК; в ячейке С6 появится значение, рассчитанное по формуле: СРЗНАЧ('Экзамен
1'!D6;'Экзамен 1(2)!D6;' Экзамен 1(3)'!D6).
7. Скопируйте формулу по всем ячейкам столбца С:
o установите курсор в ячейке С6;
o наведите указатель мыши на правый нижний угол этой ячейки, добившись появления черного
крестика;
o нажмите левую кнопку мыши и протащите ее до конца этого столбца;
o просмотрите все формулы этого столбца, устанавливая курсор в каждой ячейке.
o
o
введите названия столбцов (имен полей) в соответствии с таблицей 4;
заполните таблицу данными.
3. Выделите список, начиная от имен полей и вниз до конца записей таблицы, и скопируйте их на лист
Сортировка.
4. Выполните сортировку по столбцу Таб. № препод. Для этого:
o установите курсор в поле списка и введите команду Данные —› Сортировка. При этом должна
выделиться вся область списка. Если этого не произошло, то предварительно выделите весь список, а
затем введите указанную команду;
o в диалоговом окне <<Сортировка диапазона>> установите:
Сортировать по: поле Таб. № препод., по возрастанию
Затем по: Номер группы, по возрастанию
В последнюю очередь по: поле Код предмета, по
возрастанию
o
установите флажок Идентифицировать поля по записям.
Таблица 4
Выборка данных из списка по критерию отбора, используя Автофильтр:
1. Переименуйте новый лист на Автофильтр и скопируйте на него исходную базу данных (табл. 4).
2. Выберите из списка данные, используя критерий:
o для преподавателя а1 выбрать сведения о сдаче экзамена на положительную оценку;
o вид занятий - лк.
3. Отмените результат автофильтрации.
4. Выберите из списка данные, используя критерий: для группы 1А получить сведения о сдаче экзамена по
предмету п1 на оценки 3 и 4.
5. Отмените результат автофильтрации.
Технология работы
1. Переименуйте Лист3 на Автофильтр и скопируйте на него исходную базу данных.
2. Для выполнения п. 2 задания:
o установите курсор в область списка и выполните команду Данные —› Фильтр —› Автофильтр (в
каждом столбце появятся кнопки списка);
o сформируйте условия отбора записей: в столбце Таб. № препод. из списка условий отбора выберите
а1; в столбце Оценка из списка условий выберите Условие и в диалоговом окне сформируйте условие
отбора >2;
в столбце Вид занятия из списка условий выберите лк.
3. Отмените результат автофильтрации, установив указатель мыши в список и выполнив команду Данные —›
Фильтр —› Автофильтр.
4. Выполните п. 4 задания, воспользуясь аналогичной п. 3 технологией фильтрации.
5. Отмените результат автофильтрации, установив указатель мыши в список и выполнив команду Данные —›
Фильтр —› Автофильтр.
Выборка данных из списка, используя Расширенный фильтр, по Критерию сравнения и по
Вычисляемому критерию:
1. Переименуйте новый лист на Расширенный лист и скопируйте на него исходную базу данных (табл. 4).
2. Скопируйте имена полей списка в другую область на том же листе.
3. Сформируйте в области условий отбора Критерий сравнения - о сдаче экзаменов студентами группы 1А по
предмету п1 на оценки 4 и 5.
4. Произведите фильтрацию записей на том же листе.
5. Сформируйте в области условий отбора Вычисляемый критерий: для каждого преподавателя выбрать
сведения о сдаче студентами экзамена на оценку выше средней, вид занятия - лк.
6. Произведите фильтрацию записей на том же листе.
Технология работы
1. Переименуйте Лист4 на Расширенный лист и скопируйте на него исходную базу данных.
2. Скопируйте все имена полей списка в другую область (область формирования условий отбора записей) на
том же листе, например установив курсор в ячейку J1.
3. Сформируйте в области условий отбора Критерий сравнения в соответствии с п. 3 задания. Для этого
после имен полей введите в столбец Номер группы - 1А, в столбец Код предмета - п1, в столбец Оценка условие - >3.
4. Произведите фильтрацию записей расширенным фильтром на том же листе:
o установите курсор в область списка (базы данных);
o выполните команду Данные —› Фильтр —› Расширенный фильтр;
o в диалоговом окне Расширенный фильтр задайте параметры:
o Скопировать результат в другое место: установить флажокИсходный диапазон: A1:G17Диапазон
условия: J1:P2Поместить результат в диапазон: J4
o нажмите кнопку ОК.
5. Сформируйте в области условий отбора Вычисляемый критерий в соответствии с п. 3 задания. Для этого:
o в столбец Вид занятия введите лк;
o переименуйте в области критерия столбец Оценка, например, на имя Оценка 1;
o в столбец Оценка 1 введите вычисляемый критерий вида
=G2>СРЗНАЧ($G$2:$G$17).
6. Произведите фильтрацию записей расширенным фильтром на том же листе аналогично п. 4.
Выборка данных из списка с использованием Формы:
1. Переименуйте новый лист на Форма и скопируйте на него исходную базу данных (табл. 4).
2. Просмотрите записи списка с помощью формы и добавьте две новые.
3. Сформируйте условие отбора с помощью формы данных: для преподавателя а1 выбрать сведения о сдаче
студентами экзамена на положительную оценку, вид занятий - лк.
4. Просмотрите отобранные записи.
Технология работы
1. Переименуйте Лист5 на Расширенный лист и скопируйте на него исходную базу данных. Установите
курсор в область списка и выполните команду Данные —› Форма.
2. Просмотрите записи списка и внесите необходимые изменения с помощью кнопок Назад и Далее. С
помощью кнопки Добавить добавьте две новые записи.
3. Сформируйте условие отбора в соответствии с заданием:
o нажмите кнопку Критерии, название которой поменяется на Правка;
o в пустых строках имен списка введите критерии: в строку Таб. № препод. введите а1, в строку Вид
занятия - лк, в строку Оценка - условие >2.
4. Просмотрите отобранные записи, нажимая на кнопку Назад или Далее.
Применение Excel для анализа данных
Лабораторная работа №15
Структурирование таблицы ручным способом:
Таблица 5
Пример исходной таблицы для автоструктурирования
1. Откройте книгу с таблицей, отображенной в табл. 4, переименуйте новый лист на Структура и скопируйте
на него исходную базу данных.
2. Отсортируйте строки списка по номеру учебной группы.
3. Вставьте пустые разделяющие строки между учебными группами.
4. Создайте структурные части таблицы для учебных групп.
5. Создайте структурную часть таблицы для столбцов: Код предмета, Таб. № препод., Вид занятия.
6. Закройте и откройте структурные части таблицы.
7. Отмените структурирование.
Технология работы
1. Откройте книгу с именем Spisok, переименуйте Лист6 на Структура и скопируйте на него исходную базу
данных.
2. Отсортируйте строки списка по номеру учебной группы (команда Данные —› Сортировка).
3. Вставьте пустые разделяющие строки между учебными группами:
o выделите первую строку с другим, отличным от предыдущей строки, номером группы;
o вызовите контекстное меню и выполните команду Добавить ячейки.
4. Создайте структурные части таблицы для учебных групп:
o выделите блок строк, относящихся к первой группе;
o выполните команду Данные —› Группа и структура —› Группировать. В появившемся окне
установите флажок строки;
аналогичные действия повторите для других групп.
5. Создайте структурную часть таблицы для столбцов Код предмета, Таб. № препод., Вид занятия
аналогично
п.
4
(в
появившемся
окне
установите
флажок
столбцы).
6. Закройте и откройте созданные структурные части таблицы, нажимая на кнопки Минус или Плюс.
7. Отмените структурирование командой Данные —› Группа и структура —› Разгруппировать.
Авто структурирование таблицы и введение дополнительного иерархического уровня структуры
ручным способом:
1. Откройте книгу с именем Spisok, вставьте и назовите новый рабочий лист.
2. Создайте таблицу расчета заработной платы (табл. 5), в которой:
- в столбцы Фамилия, Зарплата, Надбавка, Премия надо ввести константы;
- в строке Итого подсчитываются суммы по каждому столбцу;
- в остальные столбцы надо ввести формулы:
o Подоходный налог = 0,12*Зар.плата
o Пенсионный фонд = 0,01*Зар.плата
o Общий налог = Подоходный налог + Пенсионный фонд
o Итого доплат = Надбавка + Премия
o Сумма к выдаче = Зар.плата - Общий налог + Итого доплат
3. Создайте автоструктуру таблицы расчета заработной платы.
4. Ознакомьтесь с разными видами таблиц, нажимая на кнопки иерархических уровней и кнопки со знаками
плюс и минус.
5. Введите в структурированную таблицу дополнительный иерархический уровень по строкам.
Технология работы
1. Откройте книгу с именем Spisok, вставьте новый рабочий лист и назовите его - Зар.плата.
2. Создайте таблицу согласно табл. 5. Введите формулы в ячейки в соответствии с п. 2 задания, используя
метод автозаполнения. Проведите сортировку в списке по фамилиям.
3. Создайте автоструктуру таблицы расчета заработной платы: установите курсор в любую ячейку области
данных и выполните команду Данные —› Группа и структура —› Создать структуру.
4. Ознакомьтесь с разными видами таблиц, нажимая на кнопки иерархических уровней и на кнопки со
знаками плюс и минус.
5. Введите в структурированную таблицу дополнительный иерархический уровень по строкам, разделив весь
список фамилий на группы по две фамилии. Для этого:
o
вставьте пустую строку после первых двух фамилий: выделите третью строку и в контекстном меню
выберите команду Добавить ячейки;
o аналогично вставьте пустую строку перед строкой Итого;
o выделите строки с первыми двумя фамилиями, вызовите контекстное меню и выполните команду
Данные —› Группа и структура —› Группировать;
o выделите строки с остальными фамилиями, вызовите контекстное меню и выполните команду Данные
—› Группа и структура —› Группировать.
Структурирование таблицы с автоматическим подведением итогов по группам таблицы,
представленной в табл. 4:
1. Откройте книгу с таблицей, отображенной в табл. 4, назовите новый лист Итоги и скопируйте на него
исходную базу данных.
2. Отсортируйте записи списка по номеру группы, коду предмета, виду занятий.
3. Создайте 1-й уровень итогов - средний балл по каждой учебной группе.
4. Создайте 2-й уровень итогов - средний балл по каждому предмету для каждой учебной группы.
5. Создайте 3-й уровень итогов - средний балл по каждому виду занятий для каждого предмета по всем
учебным группам.
6. Просмотрите элементы структуры, закройте и откройте иерархические уровни.
Технология работы
1. Откройте книгу Spisok, назовите новый лист Итоги и скопируйте на него исходную базу данных.
2. Отсортируйте список записей с помощью команды Данные —› Сортировка, выбрав в старшем ключе
номер группы, в промежуточном - код предмета, в младшем - вид занятий. Установите флажок
Идентифицировать поля по подписям.
3. Создайте 1-й уровень итогов - средний балл по каждой учебной группе:
o установите курсор в произвольную ячейку списка и выполните команду Данные —› Итоги;
o
в диалоговом окне Промежуточные итоги укажите:
Таблица 5
При каждом изменении в - Номер группы
Операция: Среднее
Добавить итоги по: Оценка
Заменять текущие итоги: нет
Конец страницы между группами: нет
Итоги под данными: да
4. Аналогично п. 3 создайте 2-й уровень итогов - средний балл по каждому предмету для каждой учебной
группы.
5. Аналогично п. 3 создайте 3-й уровень итогов - средний балл по каждому виду занятий для каждого
предмета по всем учебным группам.
6. Просмотрите элементы структуры, закройте и откройте иерархические уровни, используя кнопки с
минусом и плюсом.
Построение сводных таблиц для таблицы 4:
1. По учебным группам подведите итоги по каждому предмету и виду занятий с привязкой к преподавателю:
средний балл, количество оценок, минимальная оценка, максимальная оценка.
2. По каждому преподавателю подведите итоги в разрезе предметов и номеров учебных групп: количество
оценок, средний балл, структура успеваемости.
Технология работы
1. Откройте книгу Spisok, переименуйте новый лист на Итоги и скопируйте на него исходную базу данных.
2. Создайте сводную таблицу с помощью Мастера сводных таблиц по шагам (команда Данные —› Сводная
таблица):
этап 1 (выбор источника данных ) - щелкните по кнопке <в списке или базе данных Excel> и по кнопке
<Далее>;
этап 2 - отображение в строке Диапазон блока ячеек списка (базы данных). Если диапазон указан неверно, то
его надо стереть и указать нужный блок ячеек;
этап 3 - построение макета сводной таблицы для п. 1 задания. Технология его построения состоит в
следующем:
o перетащите элемент с именем поля, находящегося в правой стороне макета, в одну из областей: №
группы - в Страницу; № зач. кн. в Строку, Таб. № преп. и Вид занятия - в Столбец, Оценку - в Данные,
o в области Данные два раза щелкните левой кнопкой мыши и в диалоговом окне Вычисление поля
сводной таблицы выберите соответствующую операцию над значением поля;
этап 4 - выбор места расположения: существующий лист.
3. Выполните автоформатирование полученной сводной таблицы (команда Формат —› Автоформат).
4. Внесите изменения в исходные данные и выполните команду Данные —› Обновить данные.
5. Аналогично постройте сводную таблицу для п. 2 задания.
Создание консолидация данных по расположению и по категориям:
1. Откройте книгу Spisok, вставьте два листа и переименуйте их, присвоив им имена Консол.распол. и
Консол.катег.
2. Создайте на листе Консол.распол. таблицу расчета заработной платы за январь (табл. 5).
3. Скопируйте созданную таблицу на тот же лист и измените в ней данные. Эта таблица будет отражать
уровень заработной платы за февраль (табл. 5).
4. Выполните консолидацию данных по расположению.
5. Скопируйте обе таблицы с листа Консол.распол. на лист Консол.катег. и измените вторую таблицу в
соответствии с табл. 6.
6. Выполните консолидацию данных по категориям.
Таблица 6
Исходные таблицы для консолидации данных по категориям
(первая таблица не изменяется)
Технология работы
1. Выполните п. 1 задания.
2. Выполните п. 2 задания.
3. Выполните п. 3 задания.
4. Выполните консолидацию данных по расположению:
o установите курсор в первую ячейку области, где будет располагаться консолидированная таблица,
например в ячейку A11;
o выполните команду Данные —› Консолидация;
o в диалоговом окне Консолидация выберите из списка функцию Сумма и установите флажки подписи
верхней строки, значения левого столбца;
o установите курсор в окне Ссылка, выделите блок ячеек A2:D7 и нажмите кнопку Добавить;
o установите курсор в окне Ссылка, выделите блок ячеек F2:I7 и нажмите кнопку Добавить;
o нажмите кнопку ОК;
5. Выполните п. 5 задания.
6. Аналогично п. 4 выполните консолидацию данных по категориям.
Использование Excel для анализа и оптимизации данных
Лабораторная работа № 16
«Решение задачи линейного программирования»
1. Решите задачу распределения ресурсов с помощью средств Excel:
Цех предприятия производит два вида продукции (Продукт1 и Продукт2). Следует рассчитать оптимальные
недельные объемы производства этих продуктов с точки зрения максимизации прибыли. Прибыль (Целевая
функция – F) от первого продукта составляет 5 единиц, от второго – 5,5. На производстве действуют
ограничения по сырью, трудовым ресурсам и транспортным расходам:

для Продукта1 требуется 3 единицы сырья, для Продукта2 – 6. Весь цех располагает 18 единицами
сырья;

для изготовления Продукта1 требуется 6 рабочих, для Продукта2 – 4. В цехе – 24 рабочих;

транспортные расходы на перевозку Продукта1 составляют 2 единицы, а Продукта2 – 1 единицу. Эти
затраты не могут быть менее двух единиц (цена аренды одного автомобиля минимальной грузоподъемности в
течение дня). Полагаем, что вся дневная продукция цеха может быть вывезена на одном грузовике.
Очевидно, что ни одна из переменных (число единиц продукции) не может быть менее нуля.
2. Оформите таблицу данных следующим образом
A
№
1
B
Вид ресурса
C
Продукт1
D
Продукт2
E
Вычисленные
значения
F
Заданные
ограничения
5.
2
1 Сырье
3
6
18
3
2 Труд
6
4
24
4
3 Транспорт
2
1
Ограничения устанавливаются с помощью кнопки Добавить, которая вызывает окно их ввода
2
Прибыль
5
6
Целевая функция
7
Результаты
5
5,5
3. Введите в ячейки следующие формулы:
Е2=СУММПРОИЗВ(С$7:D$7;C2:D2)
Е3=СУММПРОИЗВ(С$7:D$7;C3:D3)
Е4=СУММПРОИЗВ(С$7:D$7;C4:D4)
Е6=СУММПРОИЗВ(С$7:D$7;C6:D6)
4. Для оптимизации в Excel используется инструмент Поиск решения, вызываемый через меню Сервис,
который предъявляет окно, в которое введите следующие данные:
6. После ввода всех ограничений и других условий следует нажать кнопку Выполнить для решения
поставленной задачи
Использование Excel для прогнозирования процессов
Лабораторная работа № 17
«Аппроксимация зависимостей. Линейная регрессия»
Excel располагает средствами, позволяющими прогнозировать процессы. Задача аппроксимации
возникает в случае необходимости аналитически описать явления, имеющие место в жизни и заданные в виде
таблиц, содержащих значения аргумента (ов) и функции. Если зависимость удается найти, можно сделать
прогноз о поведении исследуемой системы в будущем и, возможно выбрать оптимальное направление её
развития. Такая аналитическая функция, называемая трендом, может иметь разный вид и разный уровень
сложности в зависимости от сложности системы и желаемой точности представления.
1. Оформите таблицу следующим образом:
A
B
C
D
E
F
G
i
Xi
Yi
1
1
1
2
2
5
3
3
6
4
4
5
5
5
4
6
6
3
7
7
4
8
8
6
9
9
9
10
10
10
6. Постройте линию тренда, используя значения У:
Выделите диапазон ячеек, нажмите на пиктограмму Мастер диаграмм
12
Р яд 2
10
8
Л инейны й
(Р я д 2)
6
Уравнение регрессии
Y=a+bx
Т.о. мы узнаем, что при Х=11 ожидается У=8,8 – значение ячейки Е19
a
b
Xi*Yi
X2
П олином иаль н
ы й (Р я д 2)
4
R
Yрасч.
Y2
Сумма
2. В соответствующие ячейки введите формулы:
D5=B5*C5 (и т.д для ячеек D6:D14);
E5=B5^2; (и т.д для ячеек B6:B14);
G5=C5^2; (и т.д для ячеек C6:C14);
F5=E$3+D$3*B5; (и т.д для ячеек F6:F14);
D3=(СЧЁТ(B5:B14)*D15-B15*C15)/(СЧЁТ(B5:B14)*E15-B15*B15)
E3=(C15-D3*B15)/СЧЁТ(B5:B14)
G3=(СЧЁТ(B5:B14)*D$15-B$15*C15)/(КОРЕНЬ(СЧЁТ(B5:B14)*E$15-B15*B15)*
КОРЕНЬ(СЧЁТ(B5:B14)*G$15-C$15*C$15))
B15=СУММ(B5:B14)
C15=СУММ(C5:C14)
D15=СУММ(D5:D14)
E15=СУММ(E5:E14)
G15=СУММ(G5:G14)
3. В ячейку А16 введите заголовок Вычисление коэффициентов с помощью функции. Выделите
две ячейки D17:E17 и в строке формул введите следующее: =ЛИНЕЙН(C5:C14;B5:B14), затем
нажмите вместе 3 клавиши – Ctrl+Shift+Enter (вместо обычного Enter)
4. Результаты в клетках D17:E17 должны совпадать с D3:E3
5. В ячейку А18 введите заголовок Вычисление предположений. В соответствующие ячейки
введите формулы:
E19= ТЕНДЕНЦИЯ(C$5:C$14;B$5:B$14;C19)
D19=11
2
0
1
2
3
4
5
6
7
8
9 10
Э к с п оненциал
ь ны й (Р я д 2)
Использование Excel для прогнозирования процессов
Лабораторная работа № 18
«Полиномиальная аппроксимация»
Хотя уравнение регрессии правильно отображает направление роста функции, оно является
достаточно грубым приближением. Здесь необходимо воспользоваться более сложной аппроксимирующей
функцией. В качестве таких функций чаще всего используют степенные полиномы разной степени.
1. Создайте таблицу для нахождения уравнений регрессии первой и второй степени:
A
B
C
3. Введите формулы
D7=($B$7-($B$4+$C$4*$A$7+$D$4*$A$7^2))^2 (распространить формулу для ячеек D8:D16)
D17=СУММ(D7:D16)
Выберите команду Сервис – Поиск решения и установите следующие значения
D
Подбор коэффициентов регрессии
У(х)
Коэфф.
А
b
c
a+bx
a+bx+cx2
Аргумент Х
Погрешности
Функция
a+bx
У(Х)
(прямая)
1
1
2
5
3
6
4
5
5
4
6
3
7
4
8
6
9
9
10
10
a+bx+cx2
(парабола)
Сумма квадратов:
2. В соответствующие ячейки введите формулы:
С7=($B$7-($B$3+$C$3*$A$7))^2 (распространить формулу для ячеек С8:С16)
С17=СУММ(С7:С16)
Выберите команду Сервис – Поиск решения и установите следующие значения
Нажмите кнопку Выполнить
4. Заметьте, что, повысив порядок уравнения регрессии, погрешность приближения уменьшилась
(сравните ячейки C17 и D17).
5. Построим аппроксимирующие зависимости для линейного и полиномиального сглаживания: выделите
диапазон В7:В17, нажмите пиктограмму Мастер диаграмм и во вкладке Стандартные выберите в
поле Тип – График, в поле Вид – График с маркерами, помечающими точки данных, нажмите
Готово.
6. В появившейся области графика выделите сам график, нажмите правую кнопку мыши и выберите
команду Добавить линию тренда. В появившемся окне выберите в поле Тип – Линейная, затем ОК
и проделайте то же самое, но выберите Тип – Полиномиальная.
7. Выделите первую линию тренда, нажмите правую кнопку мыши и выберите Формат линии тренда,
где во вкладке Параметры пометьте флажок показывать уравнение на диаграмме, проделайте то
же самое со второй линией.
8. Убедитесь, что вычисленные ранее коэффициенты уравнений совпадают с полученными графическим
способом.
12
10
8
6
4
2
0
Ряд2
y = 0,6364x + 1,8
y = 0,1023x2 - 0,4886x + 4,05
1 2 3 4 5 6 7 8 9 10
Нажмите кнопку Выполнить
Линейная
(Ряд2)
Полиномиальна
я (Ряд2)
1/--страниц
Пожаловаться на содержимое документа