Механизм составления оборотно-сальдовой ведомости традиционным образом производится в несколько этапов:
1. Выбирается группа объектов, по которым необходимо составить оборотно-сальдовую ведомость.
2. Последовательно по каждому элементу данной группы производится выборка операций в рамках определенного периода. При этом операции по поступлению вписываются в графу приход, операции по выбытию (отпуску) – в графу расход. После этого производится суммирование по графам «Приход» и «Расход».
3. Агрегированные (суммированные) данные по каждому элементу выписываются в отдельную таблицу.
4. Если нет входящий остатков, то необходимо выполнить шаги 1-3 для периода <Начало деятельности> - <Начальная дата - 1>.
При изменении периода такую последовательность операций необходимо производить снова и снова, что многократно увеличивает трудозатраты на составление такого вида отчетов. Таким образом, процесс составления оборотно-сальдовой ведомости традиционным способом является чрезвычайно трудоемкой задачей. Рассмотрим автоматизацию этого процесса средствами MS Excel.
Автоматизированная информационная технология предполагает для выполнения операций по обработке данных применения специальных средств. Источником данных для оборотно-сальдовой ведомости является набор данных, реализованный в виде таблицы Excel и представляющий собой журнал регистрации операций. Например, на листе Лист1.
ЖУРНАЛ РЕГИСТРАЦИИ ОПЕРАЦИЙ
| № п.п. | Дата | Кому (от кого), Ф.И.О. или название организации | Наименование материала | Ед. изм. | Приход, ед. | Расход, ед. |
| 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| 1 | 12.03.2001 | Иванов | Гвозди | кг | 10 | |
| 2 | 13.03.2001 | Петров | Гвозди | кг | 5 | |
| 3 | 15.03.2001 | Сидоров | Гвозди | кг | 34 | |
| 4 | 15.03.2001 | Петров | Гвозди | кг | 6 | |
| 5 | 15.03.2001 | Иванов | Порошок | пач | 10 | |
| 6 | 16.03.2001 | Иванов | Порошок | пач | 15 | |
| 7 | 19.03.2001 | Иванов | Гвозди | кг | 15 | |
| 8 | 20.03.2001 | Сидоров | Гвозди | кг | 5 | |
| 9 | 25.03.2001 | Сидоров | Порошок | пач | 15 |
Из указанных исходных данных на листе Лист2 необходимо получить таблицу вида:
ОБОРОТНО-САЛЬДОВАЯ ВЕДОМОСТЬ
ПО ДВИЖЕНИЮ МАТЕРИАЛОВ НА СКЛАДЕ
с 15.03.2001 по 20.03.2001
Склад: СКЛАД №1
Группа товаров: Разное
| № п.п. | Наименование | Ед. изм. | Остаток на начало, ед. | Приход, ед. | Расход, ед. | Остаток на конец, ед. |
| 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| 1 | Гвозди | кг. | ? | ? | ? | ? |
| 2 | Порошок | пач. | ? | ? | ? | ? |
Причем данная форма должна автоматически пересчитываться при
изменении периода расчета.
| ? | Пример выполнения работы |
1. Необходимо создать форму (создать книгу Excel и набрать таблицу) в соответствии с вариантом на листе Лист1 новой книги Excel.
2. Необходимо поименовать (присвоить имена) диапазоны ячеек во всех столбцах таблицы, содержащих какую-либо информацию:
- выделить диапазон ячеек-значений определенного столбца;
- Вставка ® Имя ® Присвоить… ® В поле имя указать имя (например, НаимТовар) ® Ok.
- Горячая клавиша для присвоения имен ячейкам: Ctrl+F3.
- Таким образом, мы получим имена НомерП, ДатаРег, НаимТовар, ЕдИзм, ПриходКолво, РасходКолво.
3. Переходим на Лист2 и создаем форму оборотно-сальдовой ведомости в соответствии с заданием.
4. Присвоить имена ячейкам, в которых хранятся дата начало периода и дата конца отчетного периода.
- Выделяем ячейку, которая содержит начальную дату периода;
- Ctrl + F3 ® Имя: НачДата ® Ok.
- Выделяем ячейку, которая содержит конечную дату периода;
- Ctrl + F3 ® Имя: КонДата ® Ok.
Вводим в первую строку, после шапки оборотно-сальдовой ведомости в графу Остаток на начало следующую формулу
=СУММ((ЕСЛИ($B8=НаимТовар;1;0)) * (ЕСЛИ(ДатаРег<НачДата;1;0)) *
(ПриходКолво - РасходКолво))
и, не выходя из режима редактирования, нажать Ctrl + Shift + Enter для того, чтобы Excel идентифицировала данную формулу как формулу обработки массива. Формула примет вид:
{=СУММ((ЕСЛИ($B8=НаимТовар;1;0)) * (ЕСЛИ(ДатаРег<НачДата;1;0)) *
(ПриходКолво - РасходКолво))}
Рассмотрим составляющие данной формулы.
(ПриходКолво-РасходКолво) – означает, что от каждого элемента массива ПриходКолво необходимо отнять элемент массива РасходКолво.
={СУММ((ПриходКолво-РасходКолво))} - означает, что необходимо произвести суммирование всех элементов указанных массивов.
(ЕСЛИ($B8=НаимТовар;1;0)) – означает наложение ограничения на суммирование выражения (ПриходКолво-РасходКолво), а именно: товар должен быть равен товару, указанному в ячейке B8. Таким осуществляется косвенная выборка, т.е. суммируются только те элементы массивов, у которых наименование товара равно B8.
(ЕСЛИ(ДатаРег<НачДата;1;0)) – ограничения по дате совершения операции.
Выражения внутри операции СУММ соединены операцией умножения, т.к. только в случае выполнения всех условий произведение условий будет давать единицу, и, следовательно, данный элемент будет учитываться при суммировании.
Вводим в первую строку, после шапки оборотно-сальдовой ведомости в графу Приход следующую формулу
{=СУММ((ЕСЛИ($B8=НаимТовар;1;0)) * (ЕСЛИ(ДатаРег>=НачДата;1;0)) *
(ЕСЛИ(ДатаРег<=КонДата;1;0)) * ПриходКолво)}
в графу Расход следующую формулу
{=СУММ((ЕСЛИ($B8=НаимТовар;1;0)) * (ЕСЛИ(ДатаРег>=НачДата;1;0))
* (ЕСЛИ(ДатаРег<=КонДата;1;0)) * РасходКолво)}
в графу Остаток следующую формулу =D8+E8-F8, т.е. (Остаток на начало + Приход – Расход).
Далее необходимо выделить ячейки граф Остаток на начало, Приход, Расход, Остаток на конец и скопировать содержащиеся в них формулы вниз, в каждую строку с наименованием. В результате получим таблицу:
| è | Порядок выполнения работы |
1. Изучение теоретического материала.
2. Выполнение вариантов заданий с помощью рассмотренных инструментов, средств, приемов и технологий
3. Составление отчета о проделанной работе. Отчет должен содержать следующие разделы:
- наименование работы;
- цель работы;
- пошаговое последовательное описание процесса выполнения варианта задания по видам выполняемых действий.
4. Результат выполнения варианта задания должен быть сохранен под именем ФИО_Работа№_Вариант№ (например, «ИвановНН_Работа8 _Вариант1. xls») на жесткий диск в папку «Мои документы\ИТ в экономике» и на дискету – в двух копиях (две копии одной и той же информации в разных папках на дискете).
5. Представление результатов выполнения работы (отчета и файлов на дискете) для проверки преподавателю.
6. Защита выполненной работы: ответ на контрольные вопросы к теоретическому материалу занятия и ответ на замечания преподавателя по выполненной работе.
7. Оценка преподавателем выполненной работы.
| s | Контрольные вопросы |
1. Что такое формула массива?
2. Для чего используются формулы массива?
3. Опишите состав и порядок использования формул массива в вычислениях?
4. Как задаются фигурные скобки, ограничивающие формулу массива.
5. Опишите механизм, который лежит в основе технологии формул массива. Что делает формула массива?
6. Почему при использовании формул массива необходимо именовать диапазоны ячеек?
7. Как создать формулы массива, использующие диапазоны ячеек в других книгах Excel?
8. Что представляет собой оборотно-сальдовая ведомость? Опишите ее назначение. Почему такого вида расчеты необходимо выполнять с применением формул массива?
9. Опишите порядок выполнения работы. Как должна быть оформлена работа? Как необходимо представлять результаты проделанной работы?
| Вариант 1 | 20 - 30 мин. |
Произведена предоплата товаров и услуг:
| № | Дата операции | Кому (организация или Ф.И.О.) | Сумма, руб. | Примечание |
| 1 | 10.03.2001 | ООО "СИГМА" | 20 000 | за доску |
| 2 | 13.03.2001 | ООО "Альбион" | 80 000 | за Кафель белый 20 х 20 |
| 3 | 15.03.2001 | ОАО "Металлоконструкции" | 59 3000 | за арматуру |
| 4 | 19.03.2001 | ООО "Стройматериалы" | 50 000 | за стройматериалы |
| 5 | 26.03.2001 | ООО "СИГМА" | 100 000 | за стройматериалы |
Поступление продукции на склад:
| № | Дата операции | Наименование товара | Ед. изм. | От кого (организация или Ф.И.О.) | Кол-во | Цена, руб. | Сумма, руб. |
| 1 | 15.03.2001 | Доска обрезная 25 см | м3 | ООО "СИГМА" | 10 | 2 000 | 20 000 |
| 2 | 16.03.2001 | Кафель белый 20 х 20 M-500 | тн | ООО "Альбион" | 68 | 700 | 47 600 |
| 3 | 16.03.2001 | Кафель белый 20 х 20 M-400 | тн | ООО "Альбион" | 68 | 600 | 40 800 |
| 4 | 18.03.2001 | Арматура d12 | тн | ОАО "Металлоконструкции" | 30 | 9 000 | 270 000 |
| 5 | 18.03.2001 | Арматура d16 | тн | ОАО "Металлоконструкции" | 38 | 8 500 | 323 000 |
| 6 | 20.03.2001 | Кирпич | тыс. шт | ООО "Стройматериалы" | 10 | 960 | 9 600 |
| 7 | 20.03.2001 | Доска обрезная 25 см | м3 | ООО "СИГМА" | 50 | 1 900 | 95 000 |
| 8 | 22.03.2001 | Песок | маш. | ООО "Стройматериалы" | 20 | 750 | 15 000 |
| 9 | 25.03.2001 | Шифер 12-волн. | шт. | ООО "СИГМА" | 300 | 30 | 9 000 |
| 10 | 30.03.2001 | Кирпич | тыс. шт. | ООО "Стройматериалы" | 20 | 960 | 19 200 |
Требуется с использованием технологии обработки массивов определить cостав дебиторской (нам должны) и кредиторской (мы должны) задолженности перед поставщиками (по каждой организации) за период и свести в таблицу следующего вида.
СОСТАВ ДЕБИТОРСКОЙ И КРЕДИТОРСКОЙ ЗАДОЛЖЕННОСТИ
с «___» __________ 2001г. по «___» __________ 2001г.
| № п/п | Наименование организации | Дебиторская задолженность, руб | Кредиторская задолженность, руб. |
| ? | ? | ? | ? |
| … | … | … | … |
Составленная форма должна автоматически пересчитываться при изменении периода расчета и состава исходных данных.
| Вариант 2 | 20 - 30 мин. |
Исходная информация представлена в нижеследующей таблице.
С использованием технологии формул массива составить оборотно-сальдовую ведомость количественного учета.
| № п.п. | Дата | Кому (от кого), Ф.И.О. или название организации | Наименование материала | Ед. изм. | Приход, ед. | Расход, ед. |
| 1. | 12.03.2001 | Иванов | Гвозди | кг | 10 | |
| 2. | 13.03.2001 | Петров | Гвозди | кг | 5 | |
| 3. | 15.03.2001 | Сидоров | Гвозди | кг | 34 | |
| 4. | 15.03.2001 | Петров | Гвозди | кг | 6 | |
| 5. | 15.03.2001 | Иванов | Порошок | пач | 10 | |
| 6. | 16.03.2001 | Иванов | Порошок | пач | 15 | |
| 7. | 19.03.2001 | Иванов | Гвозди | кг | 15 | |
| 8. | 20.03.2001 | Сидоров | Гвозди | кг | 5 | |
| 9. | 25.03.2001 | Сидоров | Порошок | пач | 15 | |
| 10. | 12.04.2001 | Иванов | Гвозди | кг | 10 | |
| 11. | 13.04.2001 | Петров | Гвозди | кг | 5 | |
| 12. | 15.05.2001 | Сидоров | Гвозди | кг | 34 | |
| 13. | 15.06.2001 | Петров | Гвозди | кг | 6 | |
| 14. | 15.06.2001 | Иванов | Порошок | пач | 10 | |
| 15. | 16.06.2001 | Иванов | Порошок | пач | 15 | |
| 16. | 19.06.2001 | Иванов | Гвозди | кг | 15 | |
| 17. | 20.07.2001 | Сидоров | Гвозди | кг | 5 | |
| 18. | 15.05.2001 | Сидоров | Гвозди | кг | 34 | |
| 19. | 15.06.2001 | Петров | Гвозди | кг | 6 | |
| 20. | 15.06.2001 | Иванов | Порошок | пач | 10 | |
| 21. | 16.06.2001 | Иванов | Порошок | пач | 15 | |
| 22. | 19.06.2001 | Иванов | Гвозди | кг | 15 | |
| 23. | 20.07.2001 | Сидоров | Гвозди | кг | 5 |
| Вариант 3 | 20 - 30 мин. |
Исходная информация представлена в нижеследующей таблице. Ячейки, отмеченные знаком вопроса необходимо рассчитать (Кол-во * Цена). Требуется с использованием технологии формул массива на основе исходных данных составить:
- оборотно-сальдовую ведомость количественного учета;
- форму для определения прибыли от реализации (метод оценки товарно-материальных запасов – «по средней»).
ПОСТУПЛЕНИЕ И РЕАЛИЗАЦИЯ ТОВАРОВ ОАО "Торговля+"
Дата: 2018-12-28, просмотров: 403.
