Подведение промежуточных итогов

 

Порядок работы:

1. Отсортируйте список по столбцу, для которого необходимо подвести промежуточный итог. (Например, чтобы просуммировать стоимость материала, отпущенного со склада одному человеку, нужно отсортировать список по полю [получил].)

2. Укажите ячейку в этом списке.

3. Выберите команду меню Данные/ Промежуточные итоги.

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

5. Выберите функцию, необходимую для подведения итогов, из списка Операция. (Например, сумма.)

6. Выберите столбцы, содержащие значения, по которым необходимо подвести итоги, в списке Добавить итоги по. (Например, стоимость.)

Детализацию данных можно регулировать с помощью кнопок структуры слева от таблицы.

Любая строка итогов может быть удалена из списка, это не повлияет на другие данные. Чтобы удалить все строки с итогами нужно выбрать меню Данные/ Итоги/ Убрать все.

Чтобы подвести итоги по вложенным группам (например, для каждого получателя подсчитать сумму количества и стоимости каждого материала), нужно сначала провести сортировку по всем необходимым столбцам (получил и материал), а затем два раза подвести итоги (сначала При каждом изменении в: получил, затем При каждом изменении в: материал)

Пример: итоги по фамилиям получателей с суммированием стоимости, а также с суммированием количества и стоимости каждого материала для каждого получателя.

 

Таблица 2.22

Итоги по фамилиям получателей

расход материалов со склада № 3
материал	количество	ед. измер.	цена(у.е.)	стоимость(у.е)	Получил	дата
					Иванов А.П. Всего
гвозди		кг			Петров С.К.	14.09.00
гвозди		кг			Петров С.К.	14.10.00
гвозди		кг			Петров С.К.	12.09.00
гвозди Всего
кирпич Всего
					Петров С.К. Всего
гвозди Всего
кирпич Всего
					Сидоров С.К. Всего
					Общий итог

Сводные таблицы

Сводная таблица служит для обобщения информации из базы данных, и представления её в удобном для анализа виде (рис. 35).

Рис. 35. Создание сводных таблиц

Например, данные из списка " расход материалов со склада № 3" можно представить в следующем виде:

ü страницы по полю [получил], на каждой странице собраны данные по одной фамилии;

ü даты сгруппированы по месяцам;

ü в области данных вычисляется сумма по полю [стоимость];

ü добавлены промежуточные итоги по каждой строке, каждому столбцу и общий итог.

Таблица 2.23

Создание сводных таблиц

олучил	Петров С.К.
Сумма по полю стоимость(у.е)	дата
Материал	сен	окт	Ноя	Общий итог
Гвозди
Кирпич
Общий итог

Порядок создания сводной таблицы:

1. Выделить ячейку в списке

2. Выполнить команду Вставка/Сводная таблица.

3. Далее следовать указаниям мастера. Создать таблицу на основе данных, находящихся в списке или базе данных Microsoft Excel.

4. Указать диапазон, содержащий исходные данные (рис. 36).

Рис. 36. Создание сводных таблиц

5. Конструировать сводную таблицу, перетаскивая кнопки полей мышью. Чтобы изменить операцию в области данных (например, вместо суммы вычислить количество данных) нужно дважды щелкнуть на поле в области данных (рис. 37).

Рис. 37. Создание сводных таблиц

6. Определить параметры сводной таблицы.

 

7. Выбрать лист для размещения и завершить процесс создания сводной таблицы.

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

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

Оборудование, инструменты и приборы.

 

ПЭВМ, пакет программ Microsoft Excel

Варианты заданий

 

Варианты заданий для работы с приведены в таблицах 2.24 – 2.28.

Задание 1

1. Создать таблицу аналогичную таблице №1 из теоретической части.

2. Подсчитать стоимость материалов по каждой строке вставив формулу: стоимость=количество*цена

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

4. Получить таблицу со строками, относящимися к октябрю 2000 года.

5. Выбрать строки с одним наибольшим количеством кирпича.

6. Выбрать строки с условием:

(материал=доска) или (материал=гвозди) или (материал=цемент).

7. Выбрать строки с условием:

(получил=иванов) и (дата>10/10/2000) и (дата<1/11/2000).

8. Выбрать строки с условием:

(цена=максимальной цене в списке) или стоимость=максимальной стоимости в списке)

9. Выбрать строки с условием:

значение в столбце 'цена(у.е.)' превышает среднее значение в ячейках $D$3:$D$20.

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

11. Создать сводную таблицу.

Задание 2

1. Создать таблицу 2.24.

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

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

4. Выполнить расширенную фильтрацию планет по спутникам количество которых меньше 5 и по диаметрам с массой больше 1000.

5. Отсортировать данные в созданной таблице в порядке убывания массы.

 

Таблица 2.24

Планеты солнечной системы

	ПЛАНЕТЫ СОЛНЕЧНОЙ СИСТЕМЫ
	Планета	Период	Расстояние	Диаметр	Масса	Спутники
	Солнце
	Меркурий	0,241		4,9	0,2
	Венера	0,615		12,1	4,86
	Земля			12,8
	Марс	1,881		6,8	0,61
	Юпитер	11,86		142,6	1906,98
	Сатурн	29,46		120,2	570,9
	Уран	84,01			87,24
	Нептун	164,8		50,2	103,338
	Плутон	247,7		2,8	0,1

Задание 3

1. Создать указанную таблицу 2.25.

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

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

4. Выполнить расширенную фильтрацию данных по цене большей 25000 и по количеству купленных компьютеров больше 35.

5. Отсортировать данные в созданной таблице в порядке возрастания стоимости.

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

Таблица 2.25

Исходные данные

 

Месяц	Компьютер	Магазин	Количество, шт.	Цена, руб.	Стоимость, руб.
Январь	AMD	Альфа
Январь	Pentium1	Бета
Январь	Pentium2	Гамма
Февраль	AMD	Альфа
Февраль	Pentium1	Бета
Февраль	Pentium2	Гамма
Март	AMD	Альфа
Март	Pentium1	Бета
Март	Pentium2	Гамма
Апрель	AMD	Альфа
Апрель	Pentium1	Бета
Апрель	Pentium2	Гамма
Май	AMD	Альфа
Май	Pentium1	Бета
Май	Pentium2	Гамма

Задание 4

1. Создать таблицу 2.26.

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

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

4. Выполнить расширенную фильтрацию данных по оценке дисциплины математика большей 3 и по оценке дисциплины теоретическая механика больше 3.

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

Таблица 2.26

Исходные данные

№ п/п	Ф.И.О. студента	Наименование дисциплины
математика	физика	сопромат	теоретическая механика	начертательная геометрия	Средняя оценка студента за семестр
	Иванов А.С.						3.8
	Петренко Р.И.						4.0
	Кузнецова Т.А.						4.0
	Новчихин С.М.						3.8
	Петрова А.Р.						3.6
	Евсяков П.П.						3.4
	Зиняков Н.П.						3.4
	Пронин В.В.						3.6
	Былова С.Ю.						3.6
	Средняя оценка по дисциплине	3.9	3.7	3.9	3.3	3.7

Задание 5

1. Создать таблицу 2.27.

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

3. Произвести автоматическую фильтрацию данных созданной таблицы по количеству материала за квартал и по объему работ за квартал.

4. Выполнить расширенную фильтрацию данных по количеству материала за 3 квартал большего 10 и по общему расходу меньшему 100.

5. Отсортировать данные в созданной таблице в порядке возрастания общего расхода материалов.

Таблица 2.27

Исходные данные

ВВид работ

ЕЕдиница измерения

Объем работ

Наименование материала

ЕЕдиница измерения

ННорма на единицу

Количество материала

1кв.

2 кв.

3 кв.

4 кв.

год

1 квартал

2 квартал

3 квартал

4 квартал

Общий расход

Замена сложной деревянной опоры

шт.

опора

м^3

1.63

4.89

13.04

17.93

шт.

изолятор

шт.

шт.

крюк

шт.

шт.

колпачок

шт.

шт.

метизы

кг

15.5

46.5

170.5

шт.

зажим ПА

шт.

шт.

ветошь

кг

0.5

1.5

5.5

шт.

лак битумный

кг

0.25

0.75

2.75

шт.

бензин

кг

0.5

1.5

5.5

шт.

проволока перевязочная

кг

Задание 6

1. Заполнить таблицу 2.28 произвольными данными за два месяца.

Таблица 2.28

Исходные данные

 

Дата	Температура воздуха (С^О)	Количество осадков (мм)
01.10.2008

 

2. Найти дни с температурой <0 в октябре.

3. Найти дни с температурой >0 или с количеством осадков >0.

4. Определить самые холодные и самые теплые дни каждого месяца.

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

Порядок выполнения работы

1. Создать заданную таблицу.

2. Ввести исходные данные в таблицу, причем при вводе данных использовать коэффициент равный № варианта.

3. Каждое новое задание выполнять на новом листе, для этого: команда Вставка / Лист. Каждый заполненный лист переименовать, для этого: двойной щелчок в области названия, ввод нового названия.

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

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

· Выбрать поля, которые будут образовывать строки сводной таблицы, и перетащить кнопки, соответствующие этим полям, в область Строка.

· Выбрать поля, которые будут образовывать столбцы сводной таблицы, и перетащить кнопки, соответствующие этим полям, в область Столбец.

· Если требуется создать сводную таблицу в виде подшивки страниц, то перетащить кнопку, соответствующую полю, по которому будут строиться страницы, в область Страница.

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

· Для перерасчета сводной таблицы, если изменились данные, на основе которых строилась эта сводная таблица: выделить ее, команда Обновить данные.

Содержание отчета

Отчет должен содержать:

· номер, название и цель работы;

· краткую теоретическую часть;

· результаты выполнения работы согласно заданию, выданному преподавателем, а именно, созданные таблицы с результатами фильтрации и сортировки, созданные сводные таблицы;

· заключение по работе.

Контрольные вопросы

1. Создание электронных таблиц в Excel.

2. Задание авто фильтрации в Excel.

3. Работа с расширенным фильтром в Excel.

4. Сортировка данных в Excel.

5. Создание сводных таблиц их назначение.

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

Создание макросов в EXCEL

Цель работы:

· Изучить работу с макросами в Excel.

· Создать макрос построения графиков функций.

Теоретическая часть

 

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

В Microsoft Office встроена среда программирования Visual Basic for Application (VBA). В этой среде можно создавать собственные процедуры для любого приложения Ms Office. Однако, для создания макроса знать VBA совсем не обязательно. В Ms Office встроена программа, позволяющая автоматически переводить в команду VBA любое действие пользователя (нажатие клавиши, ввод данных в ячейку и др. подобные), а последовательность этих действий образует макрос.

Для создания макроса необходимо в меню Excel найти Макрос/ Начать запись (рис. 38).

Рис. 38. Работа с макросами

 

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

 

Рис. 39. Работа с макросами

 

Чтобы использовать относительные ссылки необходимо нажать кнопку Относительная ссылка на панели инструментов. Для остановки записи макроса необходимо в меню выполнить команду Макрос/Остановить запись. Когда запись макроса закончена из меню Макрос/Макросы можно удалить или изменить макрос.

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

Рис. 40. Выбор графика

 

На любом шаге можно нажать Готово в результате чего построение графика завершится. Для того чтобы график удобнее читался, используют Легенду - список обозначения переменных, заключенный в рамку. Для того чтобы построить график функции, необходимо предварительно построить таблицу с ее значениями на заданном отрезке. Шаг, с которым изменяется функция, можно выбрать самостоятельно. Для этого в первую ячейку заносится нижняя граница отрезка, а в последующую - формула для вычисления следующего значения аргумента включающая в себя сумму первого предыдущего значения и выбранного шага. Далее копируется эта формула в столбце на весь диапазон до тех пор, пока значение аргумента не достигнет верхней границы. В ячейку функции заносится формула для вычисления функции и копируется на весь диапазон. Выделив диапазон, содержащий значение функции, вызывается Мастер диаграмм, выбирается тип Графики и в открывшемся окне выбирается тип - График. В открывшемся окне выбирается вид графика. Для того чтобы подписи по оси абсцисс соответствовали истинным значениям, выбирается вкладка Ряд и, установив курсор в строке Подписи по оси х, выделяется диапазон значений аргумента. К графику можно добавить название, подписи по осям координат, легенду и др.

Рис. 41. Построение графика

 

После нажатия кнопки Готово получается построенный график.

 

 

Рис. 42. Построение графика

 

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