12

МИНИСТЕРСТВО СЕЛЬСКОГО ХОЗЯЙСТВА РОССИЙСКОЙ ФЕДЕРАЦИИ

ФЕДЕРАЛЬНОЕ ГОСУДАРСТВЕННОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ ВЫСШЕГО ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ «БАШКИРСКИЙ ГОСУДАРСТВЕННЫЙ АГРАРНЫЙ УНИВЕРСИТЕТ»

Кафедра статистики и

информационных систем

в экономике

Б2.Дв.1 Информационные технологии в менеджменте

Руководство по изучению дисциплины «Информационные технологии в менеджменте».

Направление подготовки 080200 Менеджмент

Профиль подготовки (специализация, магистерская программа)

Производственный менеджмент

Финансовый менеджмент

Квалификация (степень) выпускника

Бакалавр

Уфа – 2013

УДК 004

ББК 32.81

Л 12

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

Составители: к.э.н., доцент А.Г. Шарафутдинов

Рецензент: к.э.н.,

Ответственный за выпуск: заведующий кафедрой статистики и информационных систем в экономике к.э.н., доцент А.М. Аблеева

г.Уфа, БГАУ, кафедра статистики и информационных систем в экономике

Оглавление

Введение………………………………………………………………..4

1 Разработка базы данных в Access, проектирование таблиц, форм, запросов, отчетов в Access ….……………………………………….. 5 2 Системы классификации и кодирования ……..……………..…... 12 Проектирование носителей информации. Разработка базы данных в MS Exsel………………………………………………………….……19 3 Алгоритмитизация решения экономических задач…….…..…….26 4 Информационно-справочная система Консультант+, поиск необходимой информации. …………………………………….…....28 5 Web технологии, создание Web страниц……………………....….30 6 Задание по выполнению РГР ……………………………………...50 7 Задание по выполнению контрольной работы ……………..…...51 Библиографический список ……… ………………………………...52

ВВЕДЕНИЕ

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

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

1. Работа с СУБД Access

1) После загрузки Windows щелкните на пиктограмме Мiсrosoft Access. В появившемся диалоговом окне Создание базы данных выберите Новая база данных. Далее в окне Файл новой базы данных дайте имя новой базе, например, sclad.mbd.

2)Перед вводом и обработкой данных необходимо создать таблицу. Создадим таблицу ЗАКАЗЧИКИ. Для этого в окне sclad:база данных выберите вкладыш Таблицы и нажмите кнопку Создать. В окне Новая таблица выберите Конструктор. В появившемся окне макета таблицы введите имена и свойства полей таблицы 3АКАЧИКИ (таблица 6). Для обозначения ключевого поля КЗ в меню Правка выберите директиву Ключевое поле. Около поля КЗ появится знак ключа. Закройте окно таблицы и на вопрос «Сохранить изменения макета или структуры таблицы?» ответьте Да, в окне Сохранение введите имя таблицы ЗАКАЗЧИКИ

Рис. 1 Логическая структура реляционной БД задачи

. Таблица 1 Структура (макет) таблицы ЗАКАЗЧИКИ

Поля и их свойства	КЗ	НЗ	АЗ
Ключевое поле	да	нет	нет
Тип данных	текстовый	текстовый	текстовый
Размер поля	4	25	25
Подпись	Код заказчика	Наименование заказчика	Адрес заказчика
Обязательное поле	нет	нет	нет
Пустые строки	нет	нет	нет
Индексированное поле	Да (совпадений не допускается)	нет	нет

Таблица 2 Структура (макет) таблицы ИЗДЕЛИЯ

Поля и их свойства	КИ	НИ	ЦЕНА	ЕДИЗМ
Ключевое поле	да	нет	нет	нет
Тип данных	текстовый	текстовый	текстовый	текстовый
Размер поля	4	25	С плав.т.(8 байт)	4
Число десят.зн.	-	-	2	-
Маска ввода	-	-	9999,99	-
Подпись	Код изделия	Наименование изделия	Цена изделия	Ед.изм
Знач.по умолч.	-	-	0	-
Обязательное	нет	нет	нет	нет
Пустые строки	нет	нет	нет	нет
Индексированное поле	Да (совпадения не допускаются)	нет	нет	нет

Таблица 3 Структура (макет) таблицы ТТН

Поля и их свойства	НТТН	КЗ	ДОТГР
ключевое	да	нет	нет
Тип данных	текстовый	текстовый	Дата/Время
Размер поля	4	4	-
Формат поля	-	-	Краткий формат даты
Маска ввода	-	-	99.99.9999
Подпись	№ ТТН	Код заказчика	Дата отгрузки
обязательное	да	нет	нет
Пуст. строки	нет	нет	-
Индексир. поле	Да(совпадения не допускаются)	нет	нет

Таблица 4 Структура (макет) таблицы СТТН

Поля и их свойства	НТТН	КИ	КОЛОТГР
Ключевое	Да	Да	Нет
Тип данных	текстовый	текстовый	текстовый
Размер поля	4	4	С плав.т.(4 байт)
Формат поля	-	-	Фиксиров.
Число десят знаков			3
Подпись	№ НТТН	Код изделия	Количество отгружено
Знач. По умолч.	-	-	0
Обязательное	нет	нет	-
Пуст. строки	нет	нет	нет
Индексир. поле	Да(совпадения допускаются)	нет	нет

Таблица 5 Структура (макет) таблицы ПТР

Поля и их свойства	НПТР	КЗ	НТТН	ДОПЛ
Ключевое	Да	Да	Нет	Нет
Тип данных	текстовый	текстовый	текстовый	Дата/время
Размер поля	4	4	4	-
Формат поля	-	-	-	Краткий формат даты
Маска ввода	-	-	-	99.99.9999
Подпись	№ ПТР	Код заказчика	№ ТТН	Дата оплаты
Обязательное	да	нет	да	нет
Пуст. строки	нет	нет	нет	нет
Индексир. поле	Да(совпадения не допускаются)	нет	Да (допук.совпад.)	нет

Таблица 6 Структура (макет) таблицы СПТР

Поля и их свойства	НПТР	КЗ	КИ	КОЛОПЛ
Ключевое	Да	Да	Да	Нет
Тип данных	текстовый	текстовый	текстовый	числовой
Размер поля	4	4	4	С плав.т.(4 байт)
Формат поля	-	-	-	Фиксиров.
Число десят знаков				3
Подпись	№ НПТР	Код заказчика	Код изделия	Количество оплачено
Знач.по умолч.	-	-	-	0
Обязательное	да	нет	нет	-
Пуст. строки	нет	нет	нет	нет
Индексир. поле	Да(совпадения допускаются)	Да (допук.совпад.)	нет	нет

З) Аналогичным образом создайте таблицы ИЗДЕЛИЯ, ТТН, СТТН, ПТР, СПТР (таблицы 1-6).

4) Далее установите связи между таблицами. Для этого вызовите директиву Схема данных из меню Сервис. Дважды нажав мышью имя таблицы в диалоговом окне Добавление таблицы, можно поместить таблицу, участвующую в связи, в окно Схема данных. Добавьте туда все шесть таблиц и нажмите кнопку Закрыть. Теперь буксируйте поле КЗ из таблицы ЗАКАЗЧИКИ на одноименное поле таблицы ТТК В результате Access определяет таблицу ЗАКАЗЧИКИ в качестве главной, а ТТН — в качестве подчиненной. Включите опции Обеспечение целостности данных, каскадное обновление связанных полей и каскадное удаление связанных полей в диалоговом окне Связи и нажмите кнопку Создать. Отношение один-ко-многим будет представлено на экране в виде линии, проведенной между связующими полями таблицы.

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

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

6) Создадим форму ввода-вывода “Товарно-транспортная накладная” для загрузки данных в таблицы базы данных ТТН и СТТН (рис. 2). Для этого в окне базы данных выберите пиктограмму Форма и нажмите кнопку Создать. Откроется диалоговое окно Новая форма, где укажите на Мастер форм и Выберите в качестве источника данных таблицу ТТН. Access откроет диалоговое окно Создание форм, в которой нажатием кнопки > выберите из таблицы ТТН поля НТТН, К3, ДОТГР. В этом же окне укажите на таблицу ЗАКА3ЧИКИ, из которой выберите поля НЗ, АЗ. Потом аналогичным образом выберите поля КИ, КОЛОТГР из таблицы СТТН и НИ, ЦЕНА, ЕДИЗМ из таблицы

Рис 2 Форма ввода-вывода «Товарно-транспортная накладная»

ИЗДЕЛИЯ, после чего нажмите кнопку Далее. На следующем шаге Выберите тип представления данных — по ТТН, Подчиненные формы, кнопка Далее ; Выберите внешний вид подчиненной формы — Ленточный, кнопка Далее; Выберите требуемый сталь - обычный, кнопка Далее; Задайте имена форм — Форма — ТТН, Подчиненная форма — ТТВ: подчиненная форма.

Теперь откройте форму ТТН в режиме Конструктора. Увеличьте область Заголовка формы с помощью мыши, в Панели инструментов выберите элемент Надпись, напишите ТОВАРНО-ТРАНСПОРТНАЯ НАКЛАДНАЯ.

Далее откройте ТТН: подчиненная форма в режиме Конструктора. Здесь в Заголовке формы добавьте надпись Сумма, а в Области данных поле = [КОЛОТГР]*[ЦЕНА]. Увеличив область Примечания формы, добавьте туда поле =SUM([КОЛОТГР]*[ЦЕНА]) и надпись Итого по ТТН.

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

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

7) Аналогично создайте три формы: «Информация о заказчиках”, “Информация об изделиях”, “Платежное требование” (рис.12).

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

Для получения отчета с оценкой оплат по заданному изделию (см. форму 1) необходима входная информация из таблиц базы данных и ввод кода заданного изделия с клавиатуры в соответствия с блок-схемой, приведенной на рис.13. Основной блок этой схемы «Расчет оплаченной части и недооплаты по заданному изделию” состоит из трех модулей (запросов

Соединение таблиц ПТР и СПТР. Подсчет оплаченного количества изделия и оплаченной суммы по каждому номеру ТТН.

Соединение модулей 1 и 2, т.е. двух запросов Запрос_ТТН и Запрос_ПГР. Подсчет недооплаты по заданному изделию по каждому номеру ТТН.

9) Теперь выполним три запроса (модуля). Модуль 1 (Запрос_ТТН): Соединение таблиц ТТН и СТГН. Подсчет суммы отгруженного изделия. Выберите вкладыш Запрос, нажмите клавишу Создать. В появившемся окне Новый запрос выберите Конструктор. На экране в режиме конструктора появится окно Запроса на выборку, поверх которой расположено окно Добавление таблицы. Для данного запроса выберите таблицы ЗАКАЗЧИКИ, ИЗДЕЛИЯ, ТТН и СТТН, после чего нажмите кнопку Закрыть.

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

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

В строке Поле: указываются имена столбцов (полей) создаваемого запроса. Существующее имя поля можно выбрать из раскрывающего списка (щелкнув мышью на поле) или просто перетащить в ячейку Поле: мышью из таблицы в верхней части окна запроса. Для выполнения Модуля 1 в нижней части окна Запроса на выборку в строке Поле: введите имена поли КИ, НИ, НТТН, ДОТГР, КЗ, НЗ, КОЛОТГР.

Для всех этих полей в строке Групповая операция ухажите Группировка. Строку Групповая операция можно вставить в макет, выбрав одноименную директиву в меню Вид.

Для расчета суммы отгруженного изделия добавим поле СУММОТГР:Sum([КОЛОТГР]*[ЦЕНА]), для которого в строке Групповая операция укажите Выражение. Далее для этого поля из меню Вид выберите Свойства, а в окне Свойства поля в строке Подпись напишите название поля Сумма отгружено

Далее закройте окно и на вопрос “Сохранить изменения макета или структуры запроса «Запроса 1»?” ответьте утвердительно и введите имя запроса Запрос_ТТН. Теперь можете посмотреть результат работы запроса с помощью кнопки Открыть. Если нужно изменить структуру запроса, нажмите кнопку Конструктор.

Модуль 2 (Запрос_ПТР): Соединение таблиц ПТР и СПТР. Подсчет оплаченного количества изделия и оплаченной суммы по каждому номеру ТТН.

Для создания этого запроса выполните аналогичные команды, выбрав таблицы ИЗДЕЛИЯ, ЗАКАЗЧИКИ, ПТР, СПТР. Теперь обязательно удалите дублирующие связи, оставив три связи один-ко-многим: по КЗ от таблицы ЗАКАЗЧИКИ к ПТР, по НПТР от ПТР к СПТР, по КИ от таблицы ИЗДЕЛИЯ к СПТР.

Теперь в строке Поле: макета запроса введите поля КИ, НТТН, для которых выполняется Группировка; поле КОЛОПЛ, для которого в строке Групповая операция укажите Sum; и СУММОПЛ:Sum([КОЛОПЛ]*[ЦЕНА]), в сроке Групповая операция - Выражение.

Модуль 3 (Полный запрос): соединение модулей 1 и 2, т.е. двух запросов Запрос_ТТН и Запрос_ПТР. Подсчет недооплаты по заданному изделию по каждому номеру ТТН.

Для этого модуля в верхней части окна макета запроса добавляем два запроса Запрос_ТТН и Запрос_ПТР. Устанавливаем между ними связи. Для этого мышью перетащите поле КИ в Запросе_ТТН на одноименное поле в Запросе_ПТР, щелкните на появившейся линии связи мышью, нажмите правую кнопку мыши и выберите Параметры объединения, пункт 2. Также установите по НТТН от Запроса_ТТН к Запросу ПТР.

Выберите поля НИ, НТТН, ДОТГР, КЗ, НЗ, КОЛОТГР, СУММОТГР, для которых выполняется Группировка.

Далее добавьте следующие вычисляемые поля, для которых в строке Групповая операция укажите Выражение:

а) Количество оплаченного изделия

Sum_КОЛОПЛ:Sum(Пf(IsNull([Запрос_ПТР]![Sum_КОЛОПЛ]);0;[Запрос_ПТР]![Sum_КОЛОПЛ]))

б) Оплаченная сумма СУММОПЛ: Sum(IIf(IsNull([Запрос_ПТР]![Sum_КОЛОПЛ]);0;[Запрос_ПТР]![Sum_КОЛОПЛ

в) Недоплата количества изделия Недоплата_кол: Sum([КОЛОТГР])-[Sum_КОЛОПЛ]

г) Недоплата суммы

Недоплата сумм:[СУММОТГР]-[СУММОПЛ]

Для того, чтобы получить информацию о недоовлатах не по всем кодам, а только по заданному коду, добавьте поле КИ, при этом «погасите» флажок выдачи этого столбца на экран, а в поле Условие отбора: в этом столбце наберите текст приглашения [Введите код изделия]. Теперь, когда вы откроете этот запрос, на экране появится диалоговое окно с приглашением: Введите категорию. В ответ на это приглашение наберите код изделия и нажмите ОК. На экране появятся данные о недооплатах только по заданному коду изделия.

10) Отчет. Для получения отчета с оценкой оплат по заданному изделию (рис.15) выберите вкладку Отчет, нажмите кнопку Создать. В появившемся окне Новый отчет укажите на Мастер отчетов. Далее Выберите ч качестве источника данных запрос Полный_запрос и нажмите ОК. В следующем окне Создание отчетов с помощью кнопки > выберите поля НИ, НТТН, ДОТГР, КЗ, НЗ, КОЛОТГР, СУММОТГР, Sum_КОЛОПЛ, СУММОПЛ Недоjплата_кол, Недооплата_сум, после чего нажмите кнопку На следующем шаге Добавьте уровень группировки по полю НИ и нажмите кнопку Далее. На следующем шаге укажите сортировку записей по НТТН по возрастанию, нажмите кнопку Итоги и укажите итоговые значения Sum по полям СУММОТГР, СУММОПЛ, Недоплата_сум. Далее Выберите вид макета для отчета: Макет - по левому краю 1 и Ориентация — альбомная, после чего нажмите кнопку Готово.

Теперь в режиме Конструктора откройте этот отчет и отредактируйте так, как вам нужно, удалив ненужные поля и надписи, правильно их подписан и расположив. В Заголовке отчета напишите название отчета и с помощью мыши в заголовок перетащите поле НИ из области Заголовок группы НИ (см. форму 1). В окне базы данных переименуйте название отчета как Оценка оплаченной части отгрузки, для чего нажмите правую кнопку мыши и выберите в появившемся контекстном меню Переименовать.

11) Теперь для удобства работы пользователя и более оперативного получения информации об оценке оплат по заданному изделию сделаем так, чтобы просмотреть соответствующий отчет, не открывая вкладку Отчеты, а нажав кнопку в самой форме ТТН (рис.16). Для этого откройте форму ТТН в режиме Конструктора и увеличьте мышью область Примечании формы. Откройте Панель элементов и нажмите пиктограмму Кнопка, после чего мышью укажите в области Примечания формы место для кнопки. Появится окно Создание кнопок. Выберите Категории: Работа с отчетом, Действия: Просмотр отчета и нажмите кнопку Далее. На следующем этапе Выберите отчет для просмотра нажатием данной кнопки: Оценка оплаченной части отгрузки и нажмите кнопку Далее. На вопрос Что необходимо разместить на кнопке? укажите на текст и напечатайте ответ Оценка оплаченной части отгрузки по заданноому изделию, после чего нажмите кнопки Далее и Готово.

2 Разработка базы данных в MS Exsel

Присвойте Листу 1 рабочей книги имя "Субсчета". В колонке А будут размещены коды субсче­тов, а в колонке В - их названия. Пример размеще­ния информации приведен на рисунке 1.

Если в проводках используется несколько субсчетов какого-либо счета, то на листе “Субсчета“ следует задавать коды и названия субсчетов; если же этого не предполагается, то можно задать только код и название счета.

	А	В
1	Код	Название
2	01.1	Производственные ОС
3	08.4	Приобретение объектов ОС
4	19.1	НДС при приобретение ОС
5	20	Основное производство
6	40	Выпуск продукции
7	43.1	Готовая продукция растениеводства
8	50.1	Касса в рублях
9	62	Расчеты с покупателями и заказчиками
10	51	Расчетные счета
11	68	Расчеты по налогам и сборам
12	90.2	Себестоимость продаж
13	90.1	Выручка от продаж
14	60	Расчеты с поставщиками и подрядчиками
15	90.3	Продажи. НДС
16	90.9	Прибыль (убыток) от продаж
17	99	Прибыли и убытки
18
19

Рисунок2. 1 Справочник субсчетов

Колонка А должна быть обязательно отформатирована так, чтобы вводимые в нее значения Excel воспринимал как текстовые.

Для удобства записи формул на других листах рабочей книги присвойте области размещения ко­дов субсчетов ($А$2:$А$300) имя "Субсчета.Код". Для этого надо вызвать форму "Присвоение имени" ("Формула - Имя - Присвоить").

Здесь в поле "Имя" следует набрать текст: Субсче­таКод (без кавычек), а в поле формула:

=Субсчета!$А$2:$А$300.

Мы предполагаем, что по максимуму нами будет использоваться не более 299 субсчетов.

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

Для формирования справочника допустимых про­водок присвойте Листу 2 имя "ГлКнига".

Модель размещения данных на этом листе пред­полагает, что в колонке А будут указываться коды проводок, в колонке В - код дебетуемого субсчета проводки, а в столбце С - код кредитуемого субсче­та.

Столбцы А, В, С должны быть отформатированы так, чтобы вводимая в нее информация воспринималась Excel как текстовая.

Пример ввода данных приведен на рисунке 2.

Для задания расчетных формул следует определить имена:

ГлКнига.Код - для диапазона $А$2:$А$1000;

ГлКнига.Дебет - для диапазона $В$2:$В$1000;

ГлКнига.Кредит -для диапазона $С$2:$С$1000;

ГлКнига.Сумма -для диапазона $D$2:$D$1000.

ДопустимыеПроводки -для диапазона $А$2:$С$1000.

	А	В	С
1	Код	Дебет	Кредит
2	084600	08.4	60
3	191600	19.1	60
4	680191	68	19.1
5	010080	01	08.4
6	600510	60	51
7	400200	40	20
8	430400	43	40
9	902430	90.2	43
10	620901	62	90.1
11	903680	90.3	68
12	510620	51	62
13	680510	68	51
14	909990	90.9	99

Рисунок 2. 2 Справочник допустимых проводок

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

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

Ручные проводки будут выполняться на Листе 3, при­свойте ему имя "Проводки". При вводе проводок вруч­ную задают ее код и сумму, при этом дебетуемый и кре­дитуемый субсчета подставляются автоматически.

Код проводки вводится в колонке А, а сумма - в колонке В. В колонках С и D должны отображать­ся дебетуемый и кредитуемый субсчета.

Для того чтобы дебетуемый субсчет подставлял­ся автоматически, введите в ячейку С2 формулу =ВПР(А2;ДопустимыеПроводки;2;ЛОЖЬ).

Аналогичную формулу следует внести и в ячей­ку D2, записав ее в виде:

=ВПР(А2;ДопустимыеПроводки;3;ЛОЖЬ).

Отличие состоит в том, что здесь формула “вытаскивает” код кредитуемого субсчета.

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

Если будут указываться несуществующие коды проводок, то вместо кодов субсчетов появится значение #Н/Д. На рисунке 3, где заданы коды проводок, имеющиеся на листе “ГлКнига”, автоматически проставлены и коды дебетуемого и кредитуемого субсчетов.

Присвойте имена:

Проводки.Код - для диапазона $А$2:$А$3000;

Проводки.Сумма- для диапазона $В$2:$В$3000;

Проводки.Дебет - для диапазона $С$2:$С$3000;

Проводки.Кредит-для диапазона $D$2:$D$3000.

	А	В	С	D
1	Код	Сумма	Дебет	Кредит
2	084600	16000	08.4	60
3	191600	4000	19.1	60
4	010080	16000	01	08.4
5	600510	20000	60	51
6	680191	4000	68	19.1
7	400200	12000	40	20
8	430400	12000	43	40
9	902430	12000	90.2	43
10	620901	15000	62	90.1
11	903680	1500	90.3	68
12	510620	15000	51	62
13	680510	1500	68	51
14	909990	1500	90.9	99

Рисунок 2.3 Пример ввода проводок

Создайте лист “Автопроводки”. Так же, как и на листе "Проводки", определите имена

Автопроводки.Код - для диапазона $А$2:$А$1000;

Автопроводки.Сумма- для диапазона $В$2:$В$1000;

Автопроводки.Дебет - для диапазона $С$2:$С$1000;

Автопроводки.Кредит-для диапазона $D$2:$D$1000.

Итоги по проводкам с одинаковой корреспонденции счетов сформируйте в колонке D, на листе ГлКниги, дав ей заголовок “Оборот”.

Формула расчета оборота, задаваемая в ячейке D2 следующая:

=СУММЕСЛИ(Проводки.Код;А2;Проводки.Сумма)+СУММЕСЛИ(АвтоПроводки.Код;А2;АвтоПроводки.Сумма).

Формула состоит из двух однотипных слагаемых. Оба рассчитывают итоги по проводкам с кодом, за­данным в текущей строке. Но первое суммирует данные на листе "Проводки", а второе — на листе "АвтоПроводки".

После того как формула введена для второй строки, ее следует скопировать в последующие строки колон­ки D листа "ГлКнига" для всех используемых образцов проводок. Если у вас уже введены какие-либо провод­ки, автоматически будут рассчитаны итоги.

Данные листа “ГлКнига” можно использовать для расчета оборотов “Субсчета”. Здесь пока заданы только коды и наименования субсчетов. Они размещены в колонках А и В. В рамках предлагаемой модели в колонку С должны вноситься остатки субсчетов на начало периода. При этом остатки по кредиту субсчетов вводятся со знаком минус. Остатки на начало первого периода заводят вручную, для последующих – они средствами Excel автоматически переносятся из остатков на конец прошлого периода. Далее будет показано, как это делается.

В колонках D и Е будут автоматически рассчиты­ваться, соответственно, дебетовый и кредитовый обороты субсчета данной строки. Для расчета дебе­тового оборота в ячейку D2 должна быть введена формула:

=СУММЕСЛИ(ГлКнига.Дебет;А2;ГлКнига.Сумма).

Она суммирует все итоги по проводкам, в кото­рых дебетуемый субсчет совпадает с заданным в те­кущей строке листа "Субсчета". Аналогично задает­ся формула для расчета кредитового оборота (ячейка Е2):

=СУММЕСЛИ(ГлКнига.Кредит;А2;ГлКнига.Сумма).

	А	В	С	D	E	F
1	Код	Название	Остаток	Дебет	Кредит	ИсхОстаток
2	01.1	Производственные ОС	100000	16000	0	116000
3	08.4	Приобретеие объектов ОС	15000	16000	16000	15000
4	19.1	НДС при приобретение ОС	8600	4000	4000	8600
5	20	Основное производство	30000	0	12000	18000
6	40	Выпуск продукции	57000	12000	12000	57000
7	43.1	Готовая продукция растениеводства	8000	12000	12000	8000
8	50.1	Касса в рублях	30000	0	0	30000
9	62	Расчеты с покупателями и заказчиками	19500	15000	15000	19500
10	51	Расчетные счета	20000	19000	22500	16500
11	68	Расчеты по наогам и сборам	0	2500	2500	0
12	90.2	Себестоимость продаж	0	12000	0	12000
13	90.1	Выручка от продаж	0	0	15000	-15000
14	60	Расчеты с постащиками	0	20000	20000	0
15	90.3	Продажи.НДС	0	1500	0	1500
16	90.9	Прибыль (убыток) от продаж	0	1500	0	1500
17	99	Прибыли и убытки	0	0	1500	1500

Рисунок 2.4 Макет листа “Субсчета”

Формула расчета исходящих остатков для ячей­ки F2:

=C2+D2-E2 для активных счетов,

=С2-D2+E2 для пассивных счетов.

Список субсчетов на листе “Субсчета” отсортируйте в порядке возрастания кодов субсчетов. Сформированный макет листа представлен на рисунке 4 на следующей странице.

Если на листах “Проводки” и “Автопроводки” используются только допустимые проводки, а все субсчета допустимых проводок определены на листе ”Субсчета”, то баланс оборотов на листе “Субсчета” будет выполняться автоматически и кредитовый оборот равен будет дебетовому.

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

Для установки контроля правильности задания кодов дебетуемого и кре­дитуемого субсчетов, т. е. проверки наличия этих кодов в списке субсчетов на листе "Субсчета", на листе "ГлКнига" в ячейке Е2 задай­те формулу:

=ЕСЛИ(ВПР(В2;Субчета.Код;1)=В2;"";"?").

Данная формула проверяет наличие дебетуемого субсчета образца проводки в списке субсчетов на листе “Субсчета”.

Аналогичное правило можно задать и для креди­туемого субсчета. Для этого в соответствующую ко­лонку следует занести формулу:

=ЕСЛИ(ВПР(С2;Субсчета.Код;1)=С2;"";"?").

Если в строке стоит знак вопроса то это означает, что субсчет не опреде­лен на листе "Субсчета".