Выполнение экономических расчетов в среде ms excel

ВЫПОЛНЕНИЕ ЭКОНОМИЧЕСКИХ РАСЧЕТОВ В СРЕДЕ MS EXCEL

 

 

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

КОСТРОМА 2013 г

 

УДК 681.3

ББК

 

 

Составители: сотрудники кафедры экономической кибернетики, ФГБОУ ВПО Костромская ГСХА к.т.н, доцент М.А. Козлова, к.э.н., доцент Л.В. Климкина, ассистент О.В.Сосова

 

Рецензент:

 

Рекомендовано к изданию
методической комиссией экономического факультета,
протокол № от 2013 года.

 

И

Информационные технологии и базы данных:практикум для студентов экономического факультета очной и заочной формы обучения / сост. М.А. Козлова, Л.В. Климкина, О.В. Сосова,— Кострома: КГСХА, 2013. — 41 с.

Приведено описание практических работ, в которых рассмотрен анализ экономической информации в среде электронных таблиц EXCEL связанный с созданием и обработкой больших массивов данных, представленных в виде списков, или баз данных. Методические рекомендации предназначены для студентов очной и заочной форм обучения экономического факультета по направлению подготовки 080100 «Экономика» (квалификация (степень) «бакалавр») по профилям «Бухгалтерский учет, анализ и аудит», «Налоги и налогообложение», «Финансы и кредит», 080200 «Менеджмент» по профилю «Управление малым бизнесом.

 

УДК 681.3

ББК

 

 

	Ó ФГБОУ ВПО Костромская ГСХА, 2013
	ÓМ.А.Козлова, Л.В. Климкина, О.В. Сосова, составление, 2013
	Ó Оформление, РИО КГСХА, 2013

ПОДГОТОВКА НОРМАТИВНО-СПРАВОЧНОЙ ИНФОРМАЦИИ

Нормативно-справочная информация для расчета сдельной зарплаты хранится на листах Тарифы, Картотека и Работы.

Лист Тарифы

Лист Тарифы содержит перечень рабочих разрядов и соответствующих им тарифных коэффициентов и ставок. Так как ставка 1 разряда может периодически изменяться, то должен быть обеспечен автоматический пересчет ставок всех разрядов. Ставка разряда рассчитывается по формуле: коэффицент*Ставка_1_разряда.

Таблица 2 – Таблица тарифных ставок

	А	В	С
1.	Ставка 1 разряда	36,70
2.
3.	Разряд	Коэффициент	Ставка
4.			36,70
5.		1,35	49,55
6.		1,57	57,62
7.		1,85	67,90
8.		2,12	77,80
9.		2,57	94,32

Информационная технология создания листа Тарифы

1. Заполнить на листе Тарифы таблицу ставок (столбцы Разряд и Коэффициент), ввести сумму ставки 1 разряда.

2. Ввести формулу расчета тарифной ставки для 1 разряда: = B4*$C$1,

где В4 - адрес ячейки, содержащий тарифный коэффициент 1 разряда;

$C$1 - абсолютный адрес ячейки для ставки 1 разряда.

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

4. Создать именованные блоки ячеек для столбцов Разряд, Коэффициент, Ставка - команда Формулы, Определенные имена, Присвоить имя (названия столбцов не входят в состав блоков).

5. Снять защиту с ячейки ставки 1 разряда - команда Главная, Число, Защита, сбросить флажок Защищаемая ячейка.

6. Скрыть формулы в ячейках блока Ставка - команда Главная, Число, Защита, с указанием Защищаемая ячейка и Скрыть формулы.

7. Защитить лист Тарифы от изменений - команда Рецензирование, Изменения, Защитить лист

Лист Картотека

Лист Картотека содержит сведения о работающих:

Таблица 3- Картотека

Табельный номер	ФИО	Профессия	Разряд работающего	Тариф	Дети
	Иванов А.П.	Кладовщик
	Колесов В.И.	Грузчик
	Крылов А.Р.	Грузчик
	Михайлов П.Р.	Грузчик
	Смирнов И.А.	Кладовщик
	Соколов Р.В.	Уборщик

Информационная технология создания листа Картотека

1. Заполнить шапку таблицы.

2. Создать именованные блоки ячеек столбцов: Табельный_номер; ФИО; Профессия; Разряд_работающего; Тариф; Дети – команда Формулы, Определенные имена, Присвоить имя (названия столбцов не входят в блоки), ориентировочное число строк – 10.

3. Ввод табельных номеров осуществляется в текстовом формате чисел. Перед заполнением табельных номеров следует выделить блок Табельный_номер и выполнить команду Главная, Число, вкладка Число, Текстовый.

4. Выбор разряда работающего производится из ограниченного списка значений ячеек блока Разряд таблицы Тарифы.

Выделить блок ячеек Разряд_работающего, выполнить команду меню Данные, Работа с данными, Проверка данных, Проверка данных на вкладке Параметры указать тип данных Список, поместить курсор в поле Источник, нажать клавишу <F3> и выбрать блок Разряд.

5. Тариф выводится в соответствии с разрядом работающего по формуле:

=ПРОСМОТР(D2; Разряд; Ставка),

где D2 - ссылка на тарифный разряд работающего.

Формулу размножить по всем ячейкам блока Тариф.

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

Выделить блок ячеек Дети, выполнить команду Данные, Работа с данными, Проверка данных, Проверка данных. На вкладке Параметры указать: тип данных – Целое число, Значение – между 0 и 10.

7. Скрыть расчетные формулы в ячейках блока Тариф

8. Отсортировать строки таблицы Картотека по столбцу ФИО.

9. Защитить информацию рабочего листа Картотека.

Лист Работы

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

Таблица 4 - Работы

Наименование работы	Единица измерения	Норма времени	Расценка
Погрузка автомашины	машина
Подбор грузов	т	2,12
Разгрузка автомашины	машина
Складирование грузов	т	4,12

Информационная технология создания листа Работы

1. Заполнить шапку таблицы.

2. Создать именованные блоки ячеек столбцов: Наименование_работы; Единица_измерения; Норма_времени; Расценка (названия столбцов не входят в блоки, ориентировочное число строк – 10). Для этого выделить блок данных, включая название столбца, и выполнить команду Формулы, Определенные имена, Создать из выделенного фрагмента; установить флажок в строке выше.

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

Выделить блок ячеек Единица_измерения, выполнить команду Данные, Работа с данными, Проверка данных, Проверка данных. На вкладке Параметры указать тип данных – Список, поместить курсор в поле Источник, ввести через точку с запятой элементы списка значений.

4. Отсортировать строки таблицы Работы по столбцу Наименование работы.

5. Защитить информацию рабочего листа Работы.

Лист Шаблон

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

Информационная технология создания лицевой стороны наряда

1. Ввести постоянный текст документа (рис.2).

2. Создать именованные блоки для ячеек, содержащих итоговые показатели по наряду: Сумма_заработка_по_наряду, Тарифная_зарплата_по_наряду, Приработок, Нормативная_трудоемкость_по_наряду – команда меню Формулы, Определенные имена, Присвоить имя.

3. Установить максимально возможное число строк в табличной части лицевой стороны наряда, например – 4. Создать именованные блоки ячеек столбцов табличной части документа Объем, Трудоемкость, Сумма_заработка (названия столбцов не входят в блоки) – команда меню Формулы, Определенные имена, Присвоить имя.

 

Наряд на сдельную работу №
Дата выписки наряда		Сумма заработка по наряду
	Тарифная зарплата по наряду
Приработок
Нормативная трудоемкость по наряду
№ п/п	Наименование работы	Единица измерения	Трудоемкость	Расценка	Объем	Сумма заработка

Рисунок 2 – Лицевая сторона наряда

4. Выбор наименований работ при заполнении наряда производится из блока Наименование_работ таблицы Работы.

Выделить блок ячеек столбца Наименование работы, выполнить команду Данные, Работа с данными, Проверка данных, Проверка данных, на вкладке Параметры указать тип данных - Список, поместить курсор в поле Источник, нажать клавишу <F3> и выбрать блок Наименование_работы.

5. Для автоматического отображения единицы измерения, трудоемкости и расценки выбранной работы в ячейки столбцов Единица измерения, Трудоемкость и Расценка вводятся формулы. Для первой работы наряда формулы имеют вид:

=ПРОСМОТР(B8;Наименование_работы;Единица_измерения)

=ПРОСМОТР(B8;Наименование_работы;Норма_времени)

=ПРОСМОТР(B8;Наименование_работы;Расценка)

где B8 - адрес ячейки первой строки наряда, содержащей наименование работы.

Введенные формулы первой строки лицевой стороны наряда следует скопировать в остальные строки.

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

=объем * расценка

Ввести формулу в первую строку наряда и размножить ее по всем остальным строкам.

7. Сумма заработка по наряду вычисляется по формуле:

=СУММ(Сумма_заработка) итоговая сумма по всем строкам наряда

8. Тарифная зарплата по наряду и Приработок вычисляются после заполнения оборотной стороны наряда.

9. Нормативная трудоемкость по наряду вычисляется по формуле:

=СУММПРОИЗВ(Объем; Трудоемкость)

10. Скрыть от просмотра введенные формулы.

11. Открыть для изменений ячейки, содержащие номер наряда, дату оформления наряда, объемы выполненных работ.

Информационная технология создания оборотной стороны наряда

1. Ввести постоянный текст документа (рис.3)

№ п/п	ФИО	Разряд работающего	Тариф	Количество часов	Зарплата по тарифу	КТУ	Зарплата по наряду

Рисунок 3 – Оборотная сторона наряда

2. Создать именованные блоки ячеек столбцов Зарплата_по тарифу, Зарплата по наряду, КТУ - команда Формулы, Определенные имена, Присвоить имя (названия столбцов не входят в блоки).

Установить максимально возможное число строк в табличной части наряда, например – 5.

3. Выбор работающих по наряду осуществляется из фиксированного списка таблицы Картотека.

Выделить блок ячеек столбца ФИО, выполнить команду Данные, Работа с данными, Проверка данных, Проверка данных, на вкладке Параметры указать тип данных - Список, поместить курсор в поле Источник, нажать клавишу <F3> и выбрать блок ФИО.

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

Для первой строки табеля формулы вида:

=ПРОСМОТР(B19;ФИО;Разряд_работающего)

=ПРОСМОТР(B19;ФИО;Тариф)

где В19 - адрес ячейки первой строки, содержащей ФИО работающего.

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

5. Количество часов отработанных по наряду и коэффициент трудового участия (КТУ) каждого работающего для распределения суммы приработка вводятся вручную.

6. Вычисляется Зарплата по тарифу по формуле:

=Количество часов* Тариф.

7. Вычисляется итоговое значение тарифной зарплаты всех работающих по наряду по формуле:

Тарифная зарплата по наряду: =СУММ (Зарплата_по_тарифу)

8. Вычисляется итоговое значение приработка (премии) по формуле:

Приработок = Сумма заработка по наряду - Тарифная зарплата по наряду.

9. Для каждого работающего вычисляется Зарплата по наряду по формуле:

=F19+Приработок*G19/СУММ(КТУ)

где F19, G19 – адреса ячеек строки табеля для первого работающего.

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

10. Скрыть от просмотра введенные формулы.

11. Открыть для изменений ячейки, содержащие количество часов и КТУ.

12. Проверить работоспособность формул, путем ввода исходных данных, после чего удалить все введенные данные.

13. Создать страховую копию листа Шаблон – вызвать контекстно-зависимое меню листа, Переместить/скопировать лист, установить флажок Создавать копию.

14. Переименовать лист-копию в Копия шаблона и защитить лист.

ОФОРМЛЕНИЕ СДЕЛЬНЫХ НАРЯДОВ

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

Новый лист переименовать в лист Наряд №, ввести исходные данные; лишние строки в таблицах лицевой и оборотной стороны наряда следует удалять. Создать четыре наряда.

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

ИТОГИ ПО СДЕЛЬНОЙ ЗАРПЛАТЕ

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

Информационная технология построения сводной таблицы:

1. Добавьте новый лист Зарплата.

2. Добавьте на панель быстрого запуска кнопку Мастера сводных страниц и диаграмм. Для этого:

§ Щелкните стрелку рядом с панелью инструментов, а затем щелкните пункт Другие команды.

§ В группе "Выбрать команды из" выберите значение "Все команды".

§ В списке выберите пункт Мастер сводных таблиц и диаграмм, нажмите кнопку Добавить, а затем – кнопку ОК.

3. Установить курсор в ячейку листа Зарплата;

4. Выполнить команду Мастер сводных таблиц на панели быстрого доступа;

· На шаге 1: указать Исходные данные находятся в нескольких диапазонах консолидации;

· На шаге 2а: указать Создать поля страницы;

· На шаге 2б:

a) Установить Количество страничных полей сводной таблицы - 1.

b) Перечислить диапазоны, содержащие данные для агрегирования (рис. 4). Каждый диапазон соответствует блоку ячеек оборотной стороны наряда Наряд №. Блок включает имена столбцов и данные строк. Столбец № п/п не входит в диапазон.

Рисунок 4 – Добавление диапазонов консолидации

Для каждого диапазона ячеек, т.е. для каждого нового наряда выполняются следующие действия:

§ Нажать кнопку для определения диапазона;

§ Перейти на рабочий лист Наряд № и выделить ячейки диапазона;

§ Вернуться в окно диапазонов при помощи кнопки возврата

§ Нажать кнопку Добавить;

§ Дать название страничного поля диапазона.

5. Разместить сводную таблицу на листе Зарплата. Нажать Готово.

6. Выполнить настройку макета и отдельных полей. Для этого курсор последовательно устанавливается на поле сводной таблицы в разделе областей: Страница1, Строка, Столбец, Данные:

Имя поля в области Страница1 - Наряды;

Имя поля в области Строка - ФИО;

Имя поля в области Столбец - Сведения; скрыть элементы: КТУ, Разряд работающего; Тариф ( установите курсор на поле сведения в разделе сведения,щелкните на стрелку выпадающего спискаснимите пометки напротив тех элементов, которые нужно исключить из анализа).

Имя поля в области Сумма по полю Значение - Итоги, операция – Сумма, Формат… - числовой, Число десятичных знаков - 2.

7. Настроить параметры сводной таблицы: вычислить общие итоги по столбцам. Для этого на вкладке Конструктор в группе Макет нажмите кнопку Общие итоги и выберите вариант Общие итоги по столбцам.

Полученную сводную таблицу модифицировать. Для этого следует:

§ Добавить в область значений поле Значение ( щелкните имя поля в разделе полей и, удерживая кнопку нажатой, перетащите его в нужную область раздела макета) и выполнить его настройку: имя - Структура, операция - Сумма, вкладка Дополнительные вычисления и выбрать Доля от суммы по столбцу. Окончательный вид сводной таблицы приведен на рис. 5.

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

Option Explicit

' Пользовательский тип данных для добавления данных документа в базу данных

‘ Объявление пользовательского типа данных – shet

' Объявление переменных различных типов: String – текст,

‘Integer - целое, Variant - произвольное, Date – дата, Long - число

Type shet

Товар As String

Наименование As String

Едизм As String

Цена As Long

Количество As Long

End Type

Dim sh() As shet

Dim nr As Integer

Dim nc As Integer

Dim i As Integer

Dim str1 As Variant

Dim str As Date

Dim str2 As Variant

4. Выполнить команду меню Insert, Procedure, указать имя процедуры Накладная_в_БД

5. Текст программного модуля:

' начало процедуры переноса данных документа в базу данных

Sub Накладная_в_БД()

' выбор блока Накладная, находящегося на листе Документ

Application.Goto Reference:="Накладная"

' определение количества строк в блоке Накладная

nr = Selection.Rows.Count

' настройка размерности динамического массива

ReDim sh(nr)

' установка на начало области блока Накладная

ActiveCell.Offset(1, 0).Range("A1").Select

' цикл формирования элементов динамического массива

‘ на основании блока Накладная

For i = 1 To nr

sh(i).Товар = Range("Накладная").Cells(i, 1).Value

sh(i).Наименование = Range("Накладная").Cells(i, 2).Value

sh(i).Едизм = Range("Накладная").Cells(i, 3).Value

sh(i).Цена = Range("Накладная").Cells(i, 4).Value

sh(i).Количество = Range("Накладная").Cells(i, 5).Value

Next i

i = 1

' присвоение переменной str2 номера накладной

Range("C1").Select

str2 = ActiveCell.Value

' присвоение переменной str даты выписки накладной

Range("F1").Select

str = ActiveCell.Value

' присвоение переменной str1 названия организации

Range("C3").Select

str1 = ActiveCell.Value

' выбор блока База_данных

Application.Goto Reference:="База_данных"

' установка на ячейку - А2

ActiveCell.Offset(1, 0).Range("A1").Select

' вставка новой записи в базу данных

Selection.EntireRow.Insert

' организация цикла добавления новой строки в блок База_данных

‘ заполнение ячеек строки данными динамического массива

‘счетчик цикла равен числу строк

‘табличной части документа Накладная

For i = 1 To nr

Cells(i + 1, 1).Value = str

Cells(i + 1, 2).Value = sh(i).Товар

Cells(i + 1, 3).Value = sh(i).Наименование

Cells(i + 1, 4).Value = sh(i).Едизм

Cells(i + 1, 5).Value = sh(i).Цена

Cells(i + 1, 7).Value = str2

' проверка вида документа для заполнения ячеек и вычисление

‘стоимости поступивших и стоимости отгруженных товаров

If Range("Вид_движения").Value = 1 Then _

Cells(i + 1, 8).Value = sh(i).Количество

If Range("Вид_движения").Value = 2 Then _

Cells(i + 1, 9).Value = sh(i).Количество

'формулы вычисления стоимости поставки/отгрузки товаров

Cells(i + 1, 10).FormulaR1C1 = "=RC[-2]*RC[-5]"

Cells(i + 1, 11).FormulaR1C1 = "=RC[-2]*RC[-6]"

‘ перемещение курсора на начало строки

Selection.EntireRow.Insert

‘ повторение цикла

Next i

' удаление лишней пустой строки в блоке Движение

Selection.EntireRow.Delete

' конец прцедуры

End Sub

Отладка программного модуля

1. Заполнить лист Документ первичными данными.

2. Перейти на лист программного модуля и выполнить запуск макроса клавишей F5.

3. Перейти на лист Движение и проверить правильность формирования записей списка.

4. Перейти на лист Итоги и проверить изменение остатков товаров.

5. Перейти на лист Расчеты и проверить изменение итоговых показателей.

6. Если обработка выполняется корректно, перейти на лист Движение, удалить введенные строки, оставив первую вспомогательную строку блока База_данных, проверить его границы (блок База_данных должен содержать строку за головков столбцов и хотя бы одну строку).

Запуск программы

Для запуска программного модуля использовать использование командной кнопки, которая размещается в форме документа Накладная:

1. Снять защиту с листа Документ, разместить кнопку запуска модуля Накладная_в_БД:

§ Выполнить команду Разработчик, Элементы управления, Вставить.

§ Выбрать кнопку и назначить ей макрос – Накладная_в_БД.

§ Изменить название кнопки – Запись документа.

2. Защитить лист Документ.

3. Заполнить документ и нажать кнопку для переноса данных в базу данных Движение.

4. Перейти в базу данных, проверить наличие новых данных и сохранность определения блока База_данных.

АНАЛИЗ ДВИЖЕНИЯ ТОВАРОВ

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

Промежуточные итоги

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

1. Выбрать блок База_данных.

2. Отсортировать список по полям: Товар (старший ключ), Организация - младший ключ сортировки;

3. Сформировать итог 1 уровня - команда Данные, Промежуточные итоги, указав:

Поле группирования – Код товара,

Операция - Сумма,

Добавить итоги по полям: Поступление, Реализация, Итого поступление, Итого реализация

Итоги размещать под данными, предыдущие итоги не удалять.

4. Сформировать итог 2 уровня - команда Данные, Промежуточные итоги, указав:

Поле группирования - Организация,

Операция - Сумма,

Добавить итоги по полям: Поступление, Реализация, Итого поступление, Итого реализация

Итоги размещать под данными, предыдущие итоги не удалять.

5. Убрать итоги - команда Данные, Промежуточные итоги, Убрать все.

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

Выполнить автофильтрацию базы данных Движение:

§ Получить сведения о реализации товаров определенных видов;

§ Получить сведения о реализации товаров за указанный интервал времени;

§ Получить сведения о поставках товаров, цена которых находится в заданном интервале;

§ Получить сведения о движении крупных партий товаров за определенный интервал дат;

§ Получить сведения о 10 продажах с наименьшей общей стоимостью;

§ Получить сведения о 5 поступлениях товаров с наибольшей общей стоимостью и др.

Подготовить на листе Критерии условия для реализации запросов:

1. Сведения о продажах товаров, цена которых выше заданного значения, а также о продажах товаров на сумму в заданном диапазоне:

Таблица 11 - Расширенный фильтр

Цена	Итого реализация	Итого реализация
>100		>0
	>=12000	<=30000

2. Сведения о товарах, продажа которых превосходит средний уровень на 15%:

Таблица 12 - Вычисляемый критерий

Стоимость товаров

=Движение!K2>=СРЗНАЧ(Движение!$K$2:$K$94)*1,15

Информационная технология фильтрации базы данных Движение:

1. Установить курсор на лист Движение.

2. Выполнить команду меню Данные, Фильтр, Дополнительно и указать:

Обработка - Фильтровать список на месте

Исходный диапазон - имя блока База_данных,

Диапазон условий - блок критериев (лист Критерии);

Только уникальные записи.

Макрос “ Фильтрация”

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

Таблица 13 - Условия фильтрации

	А	В
		Реализация товаров
	Организация	Итого реализация
	Универмаг ЦФТ	>0

1. В ячейке А1 обеспечен выбор вида движения товаров (1 - поступление товаров, 2 - реализация товаров).

2. В ячейке А3 содержится постоянный текст Организация, который совпадает с именем столбца базы данных Движение.

3. В ячейке А4 выбирается название организации из списка возможных значений.

4. В ячейке В3 автоматически изменяется название поля критерия, которое совпадает с именем поля базы данных.

5. В ячейке В4 сдержится постоянный текст >0.

Если выбран вид движения - 1, в ячейке В1 появляется слово Поступление товаров, а в ячейке В3 появляется Итого поступление, совпадающее с названием столбца в базе данных. При выборе вида движения 2 в ячейке В1 будет записано Реализация товаров, а в ячейке В3 – Итого реализация, совпадающее с именем столбца базы данных Движение..

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

Информационная технология подготовки листа Фильтрация.

1 этап. Подготовка условий фильтрации записей базы данных:

1. Установить курсор в ячейку А1 и выполнить команду Данные, Проверка данных, указать тип - Список, источник - 1; 2.

2. Установить курсор в ячейку В1 и ввести формулу вида:

=ВЫБОР(A1;"Поступление товаров";"Реализация товаров")

3. Установить курсор в ячейку А3 и ввести текст Организация. Соответствует названию поля базы данных движения товаров.

4. Установить курсор в ячейку А4 и выполнить команду Данные, Проверка данных, указать тип - Список, источник - блок Название

5. Установить курсор в ячейку В3 и ввести формулу:

=ВЫБОР(A1;"Итого поступление";"Итого реализация")

Результат функции соответствует выводу названия столбца базы данных движения товаров.

6. Установить курсор в ячейку В4 и ввести символы >0

2 этап. Фильтрация базы данных.

1. Установить курсор в ячейку $A$7

2. Фильтрация базы данных - команда меню Данные, Дополнительно

Указать: Исходный диапазон - База_данных, Диапазон условий - $A$3:$B$4, Скопировать результат в другое место, Поместить результат в диапазон - $A$7.

3 этап. Создание макроса.

Макрос состоит из следующих действий:

· Удаление области результата предыдущей фильтрации;

· Выполнение команды Данные, Дополнительно с параметрами базы данных и диапазона условий.

1. Выполнить команду Разработчик, Записать макрос. В диалоговом окне задать имя макроса - Фильтрация и "горячие" клавиши вызова макроса - <Ctrl>+<q>, указать место хранения макроса - Эта книга, нажать кнопку ОК

2. Выделить диапазон строк, например, 7 - 25, выполнить команду Удалить.

3. Снять выделение, установив курсор в ячейке А7.

4. Выполнить команду меню Данные, Дополнительно, указав:

§ Исходный диапазон - База_данных,

§ Диапазон условий - $A$3:$B$4,

§ Скопировать результат в другое место, Поместить результат в диапазон - $A$7.

5. Выполнить команду меню Разработчик, Остановить запись.

Макрос Фильтрация будет записан.

Для проверки макроса Фильтрация следует:

1. Изменить условия отбора (изменить вид движения, название организации).

2. Выполнить команду меню Разработчик, Макросы.

3. Выбрать из списка макрос Фильтрация, нажать кнопку Выполнить или нажать "горячие клавиши".

Для создания управляющего элемента - кнопки, нажатие которой вызывает запуск предписанного макроса:

1. Выполнить команду меню Разработчик, Элементы управления, Вставить;

2. Нажать элемент Кнопка и обрисовать кнопку на листе,

3. Назначить кнопке макрос Фильтрация.

4. Изменить текст на кнопке - Фильтрация БД.

Итоги движения товаров

Построить сводные таблицы для базы данных Движение:

Таблица А использует макет вида:

Фильтр - нет

Строка - Наименование;

Столбец - Организация;

Значения - поле Итого поступление, операция - Количество чисел, имя - Количество документов;

поле Итого поступление, операция - Сумма, имя - Всего поступило;

поле Итого поступление, операция - Сумма, дополнительное вычисление - Доля от суммы по строке; имя - Уд. вес.

Сводную таблицу разместить на новом листе с именем Своды.

Таблица В использует макет вида:

Фильтр - Наименование;

Строка - Дата;

Столбец - Организация;

Значения - поле Итого реализация, операция - Количество чисел, имя - Количество документов;

поле Итого реализация, операция - Сумма, имя - Сумма реализации.

Сводную таблицу разместить на листе Своды. Выполнить группирование данных по полю Дата - интервал группирования месяц. Для группирования курсор поместить в сводной таблице в область значений поля Дата, выполнить команду меню Данные, Структура, Группировать, Группировать указать шаг – Месяцы.

ВЫПОЛНЕНИЕ ЭКОНОМИЧЕСКИХ РАСЧЕТОВ В СРЕДЕ MS EXCEL

 

 

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

КОСТРОМА 2013 г

 

УДК 681.3

ББК

 

 

Составители: сотрудники кафедры экономической кибернетики, ФГБОУ ВПО Костромская ГСХА к.т.н, доцент М.А. Козлова, к.э.н., доцент Л.В. Климкина, ассистент О.В.Сосова

 

Рецензент:

 

Рекомендовано к изданию
методической комиссией экономического факультета,
протокол № от 2013 года.

 

И

Информационные технологии и базы данных:практикум для студентов экономического факультета очной и заочной формы обучения / сост. М.А. Козлова, Л.В. Климкина, О.В. Сосова,— Кострома: КГСХА, 2013. — 41 с.

Приведено описание практических работ, в которых рассмотрен анализ экономической информации в среде электронных таблиц EXCEL связанный с созданием и обработкой больших массивов данных, представленных в виде списков, или баз данных. Методические рекомендации предназначены для студентов очной и заочной форм обучения экономического факультета по направлению подготовки 080100 «Экономика» (квалификация (степень) «бакалавр») по профилям «Бухгалтерский учет, анализ и аудит», «Налоги и налогообложение», «Финансы и кредит», 080200 «Менеджмент» по профилю «Управление малым бизнесом.

 

УДК 681.3

ББК