Главная | Обратная связь | Поможем написать вашу работу!

Базы данных. Сводные таблицы Excel

Цель лабораторной работы: ознакомиться с основными понятиями. Приобрести навыки работы со списками и сводными таблицами в Excel.

Довольно часто возникает необходимость хранить и обрабатывать данные представленные в виде таблиц. Информация, хранящаяся в таблицах, организована в виде строк и столбцов. Каждая строка таблицы, называемая записью, содержит данные об одном объекте. В столбце, называемом полем, содержатся сведения о каком-либо свойстве всех объектов хранящихся в таблице.

Если таблица используется как база данных, то в первой строке обязательно должны быть указаны имена полей. Максимальный размер базы данных в MS Excel определяется возможностями версии Excel (число строк и число столбцов в листе). База данных может быть сформирована на одном листе. Один лист может содержать несколько баз, но активной и доступной для выполнения различных операций в данный момент времени может быть только одна из них.

Рассмотрим процесс построения и обработки базы данных на примере.

Задача 1. На листе сформируем базу данных, в соответствии с табл. 6.

Таблица 1

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

Наименование товара	Дата	Поставщик	Город	Закупочная цена (руб.)	Отпускная цена	Транспортные расходы	Количество
Колбаса	02.03.13	Белмясо	Белгород	296,54	341,02	268,12
Творог	05.03.13	Белый город	Белгород	92,04	105,85	369,48
Хлеб	05.03.13	Колос	Белгород	6,35	7,30	125,44
Булка Дорожная	07.03.13	Колос	Белгород	7,69	8,84	127,36
Булка Дорожная	07.03.13	Колос	Белгород	7,69	8,84	127,69
Молоко	05.03.13	Авида	Старый Оскол	36,45	41,92	405,36
Творог	09.03.13	Авида	Старый Оскол		109,25	398,78
Мясо	05.03.13	Останкино	Белгород		227,70	236,77
Сыр	09.03.13	Авида	Старый Оскол		249,55	354,27
Хлеб	02.03.13	Колос	Белгород	6,38	7,34	136,88
Хлеб	07.03.13	Колос	Белгород	6,38	7,34	142,69
Конфеты	05.03.13	Славянка	Старый Оскол	126,87	145,90	156,55
Пиво	02.03.13	Балтика	Санкт-Петербург	36,48	41,95	963,54
Колбаса	05.03.13	Останкино	Губкин	196,45	225,92	302,55
Конфеты	07.03.13	Славянка	Старый Оскол	96,47	110,94	569,44
Пиво	09.03.13	Балтика	Санкт-Петербург	32,45	37,32	896,33
Сыр	09.03.13	Авида	Старый Оскол	187,33	215,43	305,45
Булка Дорожная	02.03.13	Колос	Белгород	7,73	8,89	101,56
Колбаса	07.03.13	Белмясо	Белгород	224,69	258,39	296,45

Добавим поле Прибыль и введем формулу для расчета (рис. 4.1):

=(F2-E2)*H2-G2.

Рис. 4.1. Ввод формулы для расчета поля Прибыль

С помощью маркера заполнения скопируем эту формулу в ячейки I2:I20. В результате лист примет вид, представленный на рис. 4.2.

Рис. 4.2. Фрагмент данных

Сортировка баз данных

Для сортировки базы выделим ячейки A1:I20, выберем на ленте команду Настраиваемая сортировка. И отсортируем таблицу по возрастанию прибыли (рис. 4.2).

Рис. 4.3. Параметры сортировки

Добавление итогов в таблицу данных

Посчитать суммарную прибыль при продаже всех продуктов можно при помощи функции СУММ. Если же воспользоваться командой Промежуточные итоги меню Данные, то появляется возможность рассчитать сумму прибыли по каждому товару или по каждому поставщику. Итак, для суммирования прибыли по каждому из товаров сделаем следующее: отсортируем таблицу по наименованию товаров и выполним команду Промежуточные итоги (рис. 4.4.). Таблица примет вид, изображенный на рис. 4.5.

Рис.4.41.Окно Промежуточные итоги

Рис. 4.5. Добавление в таблицу промежуточных итогов

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

Процесс поиска и отбора информации называется фильтрацией. В Excel можно применить два вида фильтра, а именно автофильтр и расширенный фильтр.

Для включения автофильтра необходимо:

Щелкнуть в любом месте таблицы данных, в нашем случае диапазон A1:I20. Выбрать команду Фильтр в меню Данные и таблица примет вид, изображенный на рис. 4.6. В качестве условия отбора можно выбрать либо любое значение из списка каждого поля, либо включить Пользовательский фильтр.

Рис. 4.6.. Добавление Автофильтра в таблицу данных

Выберем в качестве условия значение фильтра по полю Наименование товара – Колбаса. В результате в таблице останется информация, касающаяся только поставок колбасы (рис. 4.7.).

Рис. 2.7. Фрагмент отфильтрованной таблицы данных

Расширенный фильтр.

Для выделения из таблицы более сложных условий можно воспользоваться командой Расширенный фильтр. Рассмотрим работу с расширенным фильтром на примере выделения из таблицы записей белгородских производителей хлеба. Для этого определим область для хранения условий отбора. Каждое условие записывается в две ячейки: в верхнюю – имя поля, в нижнюю – знак отношения (>, <, >=,<=,< >) и значение. В нашем случае в ячейку K1 введем Наименование товара, в ячейку K2 – Хлеб, в ячейку L1 – Город, в ячейку L2 – Белгород (рис. 4.8., а).

а)

б)

Рис. 4.8.3 Область условий Расширенного фильтра:
а – условия фильтра, соединенные логическим действием И,
б – условия фильтра, соединенные логическим действием ИЛИ

Теперь выполним команду Дополнительно меню Данные (рис. 4.9.).

Рис.4.9. Условия отбора расширенного фильтра

В данном случае два условия соединены логическим действием И. Для объединения с помощью ИЛИ необходимо между именем поля и условием пропустить строчку (рис. 15, б).

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

Сводная таблица

Сводные таблицы – одно из наиболее мощных средств по работе с таблицами данных. Они полезны как для анализа, так и для обобщения информации, хранящейся в базе.

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

Рис.4.10. Окно команды Сводная таблица

На новом листе в области задач сводной таблицы создадим макет по подсчету прибыли.

Поля БД, на основании которой строится сводная таблица, представлены в области создания сводной таблицы в виде списка полей (рис. 18). Перетаскивая их в соответствующие области, пользователь задает необходимую структуру сводной таблицы.

В окне имеются следующие области:

Названия столбцов – для использования данных поля, расположенного в этой области, в качестве заголовков столбцов;

Названия строк – для использования данных поля, расположенного в этой области, в качестве заголовка строки;

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

Рис. 4.14. Макет создания сводной таблицы

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

Вариант 1

Создайте электронную таблицу для расчета заработной платы
15 сотрудников организации (табл. 1). Премия в организации составляет 50% от начисленной суммы, подоходный налог равен 12% от начисленной суммы с премией, в пенсионный фонд удерживается 1% от начисленной суммы с премией.

Таблица 1

Ведомость заработной платы

№ п/п	ФИО	Должность	Начислено	Размер премии	Удержано	Заработная плата к выдаче
1.	Иванов И.И.	директор

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

Вариант 2

Проанализируйте объемы продаж продуктов 15 наименований
за 6 месяцев 20ХХ года. Исходные данные приведены в табл. 2. Рассчитайте значения пустых ячеек.

Таблица 2

Продажи за январь-июнь 20ХХ год (тыс. руб.)

Наименование	Январь	Февраль	Март	Апрель	Май	Июнь	Сумма	Удельный вес продаж
Лапша
…	…	…	…	…	…	…
ИТОГО:

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

Вариант 3

Проанализируйте расходы на коммунальные услуги за 6 месяцев в 15 квартирном доме. Исходные данные приведены в табл. 3. Рассчитайте значения пустых ячеек.

Таблица 3

Ведомость коммунальных услуг (руб.)

Квартира, владелец	январь	февраль	март	апрель	май	июнь	Сумма	Удельный вес к итоговой сумме месяца
1. Иванов
…	…	…	…	…	…	…
ИТОГО:

Постройте круговую диаграмму по данным строки Сумма. Постройте гистограммы по расходам на коммунальные услуги для каждого месяца.

Вариант 4

Определите товарооборот 15 филиалов компании за 6 месяцев. Рассчитайте сумму налога, которая равна 24% от общей суммы товарооборота по филиалу. Определите чистую прибыль предприятия. Исходные данные приведены в табл. 4.

Таблица 4

Ведомость товарооборота (тыс. руб.)

Филиал	Январь	Февраль	Март	Апрель	Май	Июнь	Общая сумма товарооборота	Сумма налога
Филиал 1
…	…	…	…	…	…	…
ИТОГО:

Постройте диаграмму, отражающую долю каждого филиала в товарообороте всего предприятия.

Вариант 5

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

Определите удельный вес каждого показателя (U_i) в общем объеме (SS). Запишите формулу для расчета. Рассчитайте изменение удельного веса за год и темп прироста показателей (добавив столбцы для новых показателей).

Таблица 5

Структура основных фондов

Наименование показателя	Абсолютное значение, тыс. руб.

на н.г.	на к.г.
Земельные участки и объекты
Здания
Сооружения
Машины и оборудование
Транспортные средства
Производственный и хоз. инвентарь
Многолетние насаждения

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

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

Вариант 6

Создайте электронную таблицу для расчета заработной платы
15 сотрудников организации (табл. 6). Премия в организации составляет 30% от начисленной суммы, подоходный налог равен 12% от начисленной суммы с премией, в пенсионный фонд удерживается 1% от начисленной суммы с премией.

Таблица 6

Ведомость заработной платы

№ п/п	ФИО	Должность	Начислено	Размер премии	Удержано	Заработная плата к выдаче
1.	Петров И.И.	директор

Вариант 7

Проанализируйте объемы продаж продуктов 15 наименований
за 6 месяцев 20ХХ года. Исходные данные приведены в табл.7. Рассчитайте значения пустых ячеек.

Таблица 7

Продажи за январь-июнь 20ХХ год (тыс. руб.)

Наименование	Январь	Февраль	Март	Апрель	Май	Июнь	Сумма	Удельный вес продаж
Макаронные изделия
…	…	…	…	…	…	…
ИТОГО:

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

Вариант 8

Проанализируйте расходы на коммунальные услуги за 6 месяцев в 15 квартирном доме. Исходные данные приведены в табл. 8. Рассчитайте значения пустых ячеек.

Таблица 8

Ведомость коммунальных услуг (руб.)

Квартира, владелец	январь	февраль	март	апрель	май	июнь	Сумма	Удельный вес к итоговой сумме месяца
1. Сидоров
…	…	…	…	…	…	…
ИТОГО:

Вариант 9

Определите товарооборот 15 филиалов компании за 6 месяцев. Рассчитайте сумму налога, которая равна 29% от общей суммы товарооборота по филиалу. Определите чистую прибыль предприятия. Исходные данные приведены в табл.9.

Таблица 9

Ведомость товарооборота (тыс. руб.)

Филиал	Январь	Февраль	Март	Апрель	Май	Июнь	Общая сумма товарооборота	Сумма налога
Филиал 1
…	…	…	…	…	…	…
ИТОГО:

Постройте диаграмму, отражающую долю каждого филиала в товарообороте всего предприятия.

Вариант 10

Определить структуру затрат, используя данные табл. 10.

Таблица 20

Наименование показателя	Абсолютное значение, тыс. руб.
20ХХ год	20ХХгод
Материальные затраты
Затраты на оплату труда
Отчисления на социальные нужды
Амортизация
Прочие затраты

Определите удельный вес каждого показателя (U_i) в общем объеме (SS). Запишите формулу для расчета. Рассчитайте изменение удельного веса за период и темп прироста показателей (добавив столбцы для новых показателей).

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

Постройте на новом рабочем листе диаграмму с областями для абсолютных значений затрат за годы. Добавьте легенду и название диаграммы «Анализ затрат предприятия».

Вариант 11

Создайте электронную таблицу для расчета заработной платы
15 сотрудников организации (табл. 11). Премия в организации составляет 55% от начисленной суммы, подоходный налог равен 12% от начисленной суммы с премией, в пенсионный фонд удерживается 1% от начисленной суммы с премией.

Таблица 31

Ведомость заработной платы

№ п/п	ФИО	Должность	Начислено	Размер премии	Удержано	Заработная плата к выдаче
1.	Орлов К.И.	директор

Вариант 12

Проанализируйте объемы продаж продуктов 15 наименований
за 6 месяцев 20ХХ года. Исходные данные приведены в табл. 12. Рассчитайте значения пустых ячеек.

Таблица 12

Продажи за январь-июнь 20ХХ год (тыс. руб.)

Наименование	Январь	Февраль	Март	Апрель	Май	Июнь	Сумма	Удельный вес продаж
Хлебобулочные изделия
…	…	…	…	…	…	…
ИТОГО:

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

Вариант 13

Проанализируйте расходы на коммунальные услуги за 6 месяцев в 15 квартирном доме. Исходные данные приведены в табл. 13. Рассчитайте значения пустых ячеек.

Таблица 13

Ведомость коммунальных услуг (руб.)

Квартира, владелец	январь	февраль	март	апрель	май	июнь	Сумма	Удельный вес к итоговой сумме месяца
1. Семенов
…	…	…	…	…	…	…
ИТОГО:

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

Вариант 14

Определите товарооборот 15 филиалов компании за 6 месяцев. Рассчитайте сумму налога, которая равна 22% от общей суммы товарооборота по филиалу. Определите чистую прибыль предприятия. Исходные данные приведены в табл. 14.

Таблица 14

Ведомость товарооборота (тыс. руб.)

Филиал	Январь	Февраль	Март	Апрель	Май	Июнь	Общая сумма товарооборота	Сумма налога
Филиал 1
…	…	…	…	…	…	…
ИТОГО:

Постройте диаграмму, отражающую долю каждого филиала в товарообороте всего предприятия.

Вариант 15

Определить структуру основных фондов предприятия, используя данные табл. 15.

Таблица 15

Наименование показателя	Абсолютное значение, тыс. руб.

на н.г.	на к.г.
Здания
Сооружения
Машины и оборудование
Транспортные средства
Производственный и хоз. инвентарь
Другие виды основных средства

Определите удельный вес каждого показателя (U_i) в общем объеме (SS). Записать формулу для расчета. Рассчитать изменение удельного веса за год и темп прироста показателей (добавив столбцы для новых показателей).

Вариант 16

Создайте электронную таблицу для расчета заработной платы
15 сотрудников организации (табл. 16. Общая ставка оплаты в день составляет 3658 руб., общая ставка премии в день 11% от общей ставки оплаты за день. Заработная плата начисляется умножением коэффициента сотрудника на общую ставку в день. Размер премии вычисляется как произведение общей ставки премии на коэффициент сотрудника.

Таблица 14

Ведомость заработной платы

№ п/п	ФИО	Должность	Коэффи-циент заработной платы	Коли-чество отрабо-танных дней	Начисленная заработная плата	Премия	Сумма к выдаче
1.	Орлов К.И.	директор
2.	Иванов А.А.	зам. дирек.	0,8

Вариант 17

Рассчитайте ведомость выполнения плана товарооборота киоска №5 по форме, представленной в табл. 17.

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

Таблица 17

Ведомость товарооборота

№	Месяц	Отчетный год	Отклонение от плана
план, р.	факт, р.	выполнение, %
	Январь	7 800,00 р.	8 500,00 р.
	Февраль	3 560,00 р.	2 700,00 р.
	Март	8 900,00 р.	7 800,00 р.
	Апрель	5 460,00 р.	4 590,00 р.
	Май	6 570,00 р.	7 650,00 р.
	Июнь	6 540,00 р.	5 670,00 р.
	Июль	4 900,00 р.	5 430,00 р.
	Август	7 890,00 р.	8 700,00 р.
	Сентябрь	6 540,00 р.	6 500,00 р.
	Октябрь	6 540,00 р.	6 570,00 р.
	Ноябрь	6 540,00 р.	6 520,00 р.
	Декабрь	8 900,00 р.	10 000,00 р.
	ИТОГО:
			Максимум, %
			Среднее, %

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

Вариант 18

Создайте ведомость регистрации проживающих в гостинице (состоящую из 15 записей) (табл. 18).

Таблица 18

Ведомость проживающих в гостинице

ФИО	Номер	Стоимость номера в сутки, руб.	Дата прибытия	Дата убытия	Кол-во дней прожи-вания	Сумма
Иванов И.И.			2.09.2012	2.10.2012
…	…	…	…	…
Общая сумма

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

Вариант 19

Создайте электронную таблицу для расчета заработной платы
15 сотрудников организации (табл.19). Премия в организации составляет 60% от начисленной суммы, подоходный налог равен 12% от начисленной суммы с премией, в пенсионный фонд удерживается 1% от начисленной суммы с премией.

Таблица 19

Ведомость заработной платы

№ п/п	ФИО	Должность	Начислено	Размер премии	Удержано	Заработная плата к выдаче
1.	Комаров С.	директор

Вариант 20

Определите структуру затрат, используя данные табл. 20.

Таблица20

Структура затрат предприятия

Наименование показателя	Абсолютное значение, тыс. руб.
пред. год	тек. год
Материальные затраты
Затраты на оплату труда
Отчисления на социальные нужды
Амортизация
Прочие затраты

Постройте на новом рабочем листе диаграмму с областями для абсолютных значений затрат за годы, добавьте легенду и название диаграммы «Анализ затрат предприятия».

Вариант 21

Создайте электронную таблицу для расчета заработной платы
15 сотрудников (табл. 21). Общая ставка оплаты в день составляет
4036 руб., общая ставка премии в день 17% от общей ставки оплаты за день. Заработная плата начисляется умножением коэффициента сотрудника на общую ставку в день. Размер премии вычисляется как произведение общей ставки премии на коэффициент сотрудника.

Таблица 21

Ведомость заработной платы

№ п/п	ФИО	Должность	Коэф-т заработной платы	Кол-во отработ. дней	Начисленная заработная плата	Премия	Сумма к выдаче
1.	Орлов К.И.	директор
2.	Иванов А.А.	зам. дирек.	0,8

Вариант 22

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

Таблица 22

Ведомость товарооборота

№	Месяц	Отчетный год	Отклонение от плана
план, р.	факт, р.	выполнение, %
	Январь
	Февраль
	Март
	Апрель
	Май
	Июнь
	Июль
	Август
	Сентябрь
	Октябрь
	Ноябрь
	Декабрь
	ИТОГО:
			Максимум, %
			Среднее, %

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

Вариант 23

Проанализируйте расходы на коммунальные услуги за 6 месяцев в 15 квартирном доме. Исходные данные приведены в табл. 23. Рассчитайте значения пустых ячеек.

Таблица 23

Ведомость коммунальных услуг (руб.)

Квартира, владелец	январь	февраль	март	апрель	май	июнь	Сумма	Удельный вес к итоговой сумме месяца
1. Кротов
…	…	…	…	…	…	…
ИТОГО:

Вариант 24

Определите товарооборот 15 филиалов компании за 6 месяцев. Рассчитайте сумму налога, которая равна 28% от общей суммы товарооборота по филиалу. Определите чистую прибыль предприятия. Исходные данные приведены в табл. 24.

Таблица 24

Ведомость товарооборота (тыс. руб.)

Филиал	Январь	Февраль	Март	Апрель	Май	Июнь	Общая сумма товарооборота	Сумма налога
Филиал 1
…	…	…	…	…	…	…
ИТОГО:

Постройте диаграмму, отражающую долю каждого филиала в товарообороте всего предприятия.

Вариант 25

Определить структуру основных фондов предприятия, используя данные табл. 25.

Таблица 25

Структура основных фондов предприятия

Наименование показателя	Абсолютное значение, тыс. руб.

на н.г.	на к.г.
Земельные участки
Здания
Сооружения и передаточные устройства
Машины и оборудование
Транспортные средства
Производственный и хоз. инвентарь
Другие виды основных средств

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

Вариант 26

Создайте электронную таблицу для расчета заработной платы
15 сотрудников организации (табл.26). Общая ставка оплаты в день составляет 3917 руб., общая ставка премии в день 14% от общей ставки оплаты за день. Заработная плата начисляется умножением коэффициента сотрудника на общую ставку в день. Размер премии вычисляется как произведение общей ставки премии на коэффициент сотрудника.

Таблица 26

Ведомость заработной платы

№ п/п

ФИО

Должность

Коэффи-циент заработной платы

Коли-чество отрабо-танных дней

Начисленная заработная плата

Премия

Сумма к выдаче

Орлов А.П.

директор

Лосев А.А.

зам. дирек.

0,8

⇐ Предыдущая 1 234 5 Следующая ⇒

Воспользуйтесь поиском по сайту: