Приложения Microsoft Excel и Microsoft Access

Министерство  образования и науки Российской Федерации

Федеральное государственное бюджетное образовательное  учреждение

  высшего профессионального образования

«Южно-Уральский  государственный университет»

(национальный  исследовательский университет)

Факультет «Экономика и управление»

Кафедра «Экономика и финансы»

 

 

 

 

Вариант 17. Российская Федерация

 

 

ПОЯСНИТЕЛЬНАЯ ЗАПИСКА К КУРСОВОЙ РАБОТЕ

по  дисциплине «Информатика»

 

 

 

 

 

 

Руководитель, доцент

__________

__________________ 2012 г.

 

 

Автор работы

студентка группы _______________

____________________ 2012 г.

 

Работа  защищена

с оценкой

___________________________

_____________________2012 г.

 

 

 

 

 

 

 

 

Челябинск 2012

 

 

АННОТАЦИЯ

Курсовая  работа по информатике. – Челябинск: ЮУрГУ, ЭиУ–205, 30 стр., рисунков – 1 шт., таблиц – 11 шт.,  библиографический список – 2 наименования

Объект  исследования –  Оптовая торговля.

Цель работы – закрепление полученных знаний по приложениям Microsoft Excel и Microsoft Access.

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

 

СОДЕРЖАНИЕ

ВВЕДЕНИЕ 4

1. MICROSOFT EXCEL 5

1.1. Подсчет  данных по формулам 5

1.2. Сортировка  данных 8

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

1.3.1. Автофильтрация 11

1.3.2. Расширенная  фильтрация 11

1.4. Структурирование  в трех уровнях 13

1.5. Подведение  итогов 15

1.6. Консолидация 17

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

1.8. Диаграмма 21

2. СОЗДАНИЕ СЕРИЙНОГО ДОКУМЕНТА 22

3. MICROSOFT ACCESS 24

3.1.Запрос  на выборку 24

3.2. Запрос  на выборку с параметром 25

3.3. Отчет  с группированием 26

3.4.Кнопочная  форма 28

ЗАКЛЮЧЕНИЕ 29

БИБЛИОГРАФИЧЕСКИЙ СПИСОК 30

 

 

ВВЕДЕНИЕ

 

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

Цель работы, задачи работы:

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

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

2) Использовать приложение  Microsoft Access для решения задач в различных предметных областях, для чего должны быть созданы следующие объекты:

• запрос на выборку;
• запрос на выборку с параметром;
• отчет с группированием;
• кнопочная форма;

Кнопочная форма для открытия всех созданных объектов.

 

1. MICROSOFT EXCEL

1.1. Подсчет данных  по формулам

Формулы, используемые при  расчете полей таблицы (1.1), следующие: (в качестве примера берется первая строка):

1. Стоимость товара: =ПРОИЗВЕД(C3:D3), где С – «партия товара», а D –  «цена товара».
2. Количество возвращенного товара: =C3-H3, где

С – «партия товара»,  Н – «количество товара, полученного на реализацию».

3. Продолжительность нахождения товара до реализации: =ДНЕЙ360(K3;L3), где K  –«дата получения товара», L – «дата реализации». Формула ДНЕЙ360 помогает вычислить количество дней между двумя дат на основе 360 дневного года.
4. Фамилия реализатора с максимальным количеством товара: =ЕСЛИ(H3=МАКС(H3:H22);F3;" "), выявление наибольшего значения среди представленных.
5. Товар, имеющий минимальный срок реализации: =ЕСЛИ(M17=МИН(M3:M22);M17;" "), где М –продолжительность нахождения товара до реализации.
6. Фамилия реализатора, работающего по доверенности: =ЕСЛИ(G3="есть";F3;" "), где G4 –наличие доверенности, F– фамилии реализаторов .

 

 

Таблица 1 - Подсчет данных по формулам

Оптовая торговля															17.05.2012
№ п/п	Товар	Партия товара	Цена	Стоимость товара	Фамилия реализатора	Доверенность	Количество товара, полученного на реализацию	Количество возвращенного товара	Количество нерелизованного товара	Дата поступления товара	Дата реализации	Продолжитльность нахождения товара до реализации	Фамилия реализатора с максимальным количеством товара	Товар, имеющий минимальный срок реализации	Фамилия реализатора, работающего по доверенности
1	Топливный насос	100	190	19 000,0	Лунин	есть	99	1	0	23.01.2012	05.05.2012	102	Лунин		Лунин
2	Зеркала/F1	57	500	28 500,0	Козлов	нет	20	37	0	14.04.2012	01.05.2012	17
3	Шины R14/michelin	18	3700	66 600,0	Иванов	нет	10	8	0	21.03.2012	28.04.2012	37
4	Диски R14/sliko	24	4500	108 000,0	Степанов	есть	16	8	0	17.02.2012	26.03.2012	39			Степанов
5	Амортизаторы передние /SS20	100	1200	120000	Лунин	есть	82	18	0	23.01.2012	11.05.2012	108			Лунин
6	Амортизаторы задние/ SS20	98	2300	225 400,0	Перов	нет	74	24	0	01.05.2012	13.05.2012	12
7	Головка цилиндров	63	6500	409 500,0	Никитин	есть	60	3	0	18.04.2012	24.04.2012	6			Никитин
8	Шатун	99	450	44 550,0	Козлов	нет	96	3	0	17.03.2012	21.03.2012	4
9	Ремень ГРМ	100	100	10 000,0	Лунин	есть	99	1	0	01.05.2012	10.05.2012	9	Лунин		Лунин
10	Диодный мост	60	1200	72 000,0	Черевко	нет	58	2	0	14.04.2012	10.05.2012	26
11	Аккомулятор	77	900	69 300,0	Севостьянов	есть	70	7	0	14.02.2012	21.02.2012	7			Севостьянов
12	Вентилятор	90	4570	411 300,0	Перов	нет	86	4	0	29.04.2012	01.05.2012	2
13	Помпа	88	2450	215 600,0	Петров	есть	87	1	0	13.05.2012	13.05.2012	0		0	Петров
14	Свечи	97	1800	174 600,0	Иванов	нет	88	9	0	22.01.2012	17.02.2012	25
15	Турбина STI	98	9900	970 200,0	Кузьмин	есть	78	20	0	27.02.2012	06.03.2012	9			Кузьмин
16	Интекуллер STI	99	6900	683 100,0	Никитин	есть	97	2	0	11.01.2012	12.04.2012	91			Никитин
17	Коленвал	99	4500	445 500,0	Кузьмин	есть	99	0	0	11.01.2012	14.02.2012	33	Кузьмин		Кузьмин
18	Масло Castrol	100	320	32 000,0	Лунин	есть	98	2	0	23.01.2012	18.04.2012	85			Лунин
19	Блок - фара	76	120	9 120,0	Степанов	есть	72	4	0	28.02.2012	25.03.2012	27			Степанов
20	Коврики	74	400	29 600,0	Черевко	нет	69	5	0	26.03.2012	01.05.2012	35

 

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

Сортировка  – упорядочение данных по возрастанию или убыванию.

Универсальное средство сортировки содержится в меню Данные –Сортировка с установкой необходимых параметров.

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

Сортировка  осуществляется на том же листе.

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

Сначала осуществляется сортировка в столбце 1-го уровня, затем  сортируются одинаковые записи 1-го столбца по столбцу 2-го уровня, затем  сортируются одинаковые записи 2-го столбца по столбцу 3-го уровня.

В том же окне устанавливается порядок сортировки в столбцах — по возрастанию или убыванию.

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

В таблице 2 сортировка выполнена  по трем уровням: «партия товара», «фамилии реализаторов», «дата реализации».

 

Таблица 2 - Сортировка данных

Оптовая торговля															17.05.2012
№ п/п	Товар	Партия товара	Цена	Стоимость товара	Фамилия реализатора	Доверенность	Количество товара, полученного  на реализацию	Количество возвращенного товара	Количество нереализованного товара	Дата поступления товара	Дата реализации	Продолжитльность нахождения товара до реализации	Фамилия реализатора с максимальным количеством товара	Товар, имеющий минимальный срок реализации	Фамилия реализатора, работающего по доверенности
3	Шины R14/michelin	18	3700	66 600,0	Иванов	нет	10	8	0	21.03.2012	28.04.2012	37
4	Диски R14/sliko	24	4500	108 000,0	Степанов	есть	16	8	0	17.02.2012	26.03.2012	39			Степанов
2	Зеркала/F1	57	500	28 500,0	Козлов	нет	20	37	0	14.04.2012	01.05.2012	17
10	Диодный мост	60	1200	72 000,0	Черевко	нет	58	2	0	14.04.2012	10.05.2012	26
7	Головка цилиндров	63	6500	409 500,0	Никитин	есть	60	3	0	18.04.2012	24.04.2012	6			Никитин
20	Коврики	74	400	29 600,0	Черевко	нет	69	5	0	26.03.2012	01.05.2012	35
19	Блок - фара	76	120	9 120,0	Степанов	есть	72	4	0	28.02.2012	25.03.2012	27			Степанов
11	Аккомулятор	77	900	69 300,0	Севостьянов	есть	70	7	0	14.02.2012	21.02.2012	7			Севостьянов
13	Помпа	88	2450	215 600,0	Петров	есть	87	1	0	13.05.2012	13.05.2012	0			Петров
12	Вентилятор	90	4570	411 300,0	Перов	нет	86	4	0	29.04.2012	01.05.2012	2
14	Свечи	97	1800	174 600,0	Иванов	нет	88	9	0	22.01.2012	17.02.2012	25
15	Турбина STI	98	9900	970 200,0	Кузьмин	есть	78	20	0	27.02.2012	06.03.2012	9			Кузьмин
6	Амортизаторы задние/ SS20	98	2300	225 400,0	Перов	нет	74	24	0	01.05.2012	13.05.2012	12
8	Шатун	99	450	44 550,0	Козлов	нет	96	3	0	17.03.2012	21.03.2012	4
17	Коленвал	99	4500	445 500,0	Кузьмин	есть	99	0	0	11.01.2012	14.02.2012	33			Кузьмин
16	Интекуллер STI	99	6900	683 100,0	Никитин	есть	97	2	0	11.01.2012	12.04.2012	91			Никитин
18	Масло Castrol	100	320	32 000,0	Лунин	есть	98	2	0	23.01.2012	18.04.2012	85			Лунин
1	Топливный насос	100	190	19 000,0	Лунин	есть	99	1	0	23.01.2012	05.05.2012	102	Лунин	0	Лунин
9	Ремень ГРМ	100	100	10 000,0	Лунин	есть	99	1	0	01.05.2012	10.05.2012	9			Лунин
5	Амортизаторы передние /SS20	100	1200	120000	Лунин	есть	82	18	0	23.01.2012	11.05.2012	108			Лунин

 

 

 

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

Фильтрация  данных в списке — это выбор данных по заданному критерию (условию), т.е. эта операция позволяет выделить нужные данные среди имеющихся.

1.3.1. Автофильтрация

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

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

Если какая-то из кнопок была использована для назначения фильтра, в треугольнике появляется значок фильтра.

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

1. «Партия товара» > 50 ,  2. «Продолжительность нахождения товара до реализации» < 60, 3. «Цена»> 1400.

1.3.2. Расширенная  фильтрация

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

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

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

1. «Партия товара» > 85 шт. 2. «Доверенность» - нет. 3. «Продолжительность нахождения товара до реализации» < 20 лет. 

Таблица 3 - Атофильтрация

Таблица 4 - Расширенная фильтрация

 

1.4. Структурирование  в трех уровнях

Структурирование таблицы — группирование строк и столбцов.

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

Структурирование  выполняется с помощью команды  меню Данные-Группировать, а затем выбирается конкретный способ — ручной (далее Группировать) или автоматический (далее Создание структуры).

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

В таблице 5 проведено структурирование по трем уровням: 1.Цена; 2.Партия товара; 3.Товар.

 

Таблица 5 - Структурирование в трех уровнях

 

1.5. Подведение  итогов

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

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

В окне «Промежуточные итоги» в поле (при каждом изменении) задать имя этого поля.

В поле «Операция» задать функцию выполнения (в данном случае Сумма).

В поле «Добавить итоги по…» , затем нажать ОК.

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

В таблице 6 подведены итоги  – в трех уровнях – в виде средних значений признаков: количество населения, занимаемая площадь, возраст города.  Уровни подведения итогов в порядке убывания ранга – цена, партия, товар.

 

Таблица 6 - Подведение итогов

 

 

1.6. Консолидация

Консолидация — агрегирование (объединение) данных, представленных в исходных областях-источниках.

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

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

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

 Ключевым полем считается  самое левое поле (колонка) таблицы  или ее выделенная часть. 

Для выполнения консолидации предварительная сортировка не нужна.

 

Таблица 7 - Консолидация

 

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

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

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

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

1.8. Диаграмма

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

 

 

 

Рисунок 1 –  Различие цены на товары оптовой фирмы 

2. СОЗДАНИЕ СЕРИЙНОГО  ДОКУМЕНТА

Excel позволяет создавать только табличные документы.

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