41834

Решение бухгалтерских задач с помощью пакета Excel

Лабораторная работа

Информатика, кибернетика и программирование

Решение бухгалтерских задач с помощью пакета Excel Цель работы Познакомиться с работой пакета Excel как инструмента для решения задач бухгалтерского учета. Научиться правильно задавать имена переменным определять ссылки на ячейки использовать функции при вводе формул работать с массивами данных в Excel. Должна быть установлена программа Microsoft Excel.

Русский

2013-10-25

286.36 KB

152 чел.

4

Лабораторная работа №9.

Решение бухгалтерских задач с помощью пакета Excel

9.1 Цель работы

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

9.2 Приборы и материалы

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

9.3 Решение задачи учета доходов и расходов в семье

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

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

Пакет Excel предоставляет в распоряжение пользователя мощные и гибкие средства для решения бухгалтерских задач. В лабораторной работе студенты должны решить задачу аналогичную бухгалтерской, но для разработанного самими плана счетов. Решение поставленной в данной лабораторной работе задаче студенты начинают с создание плана счетов, который записывают на первый лист книги для решения задачи, так как показано на рисунке 9.1. В диапазон ячеек F10 – G16 показан один из вариантов плана счетов для ведения домашней бухгалтерии. При решении задачи студенты могут сами создать удобный для их семьи план счетов. Кроме плана счетов на первом листе книги Excel, который назовем «Текущие доходы и расходы» создадим аналог журнала операций в бухгалтерии. Только записывать в него будем не бухгалтерские операции, а наши текущие доходы и расходы.

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

Приступая к выполнению задачи, создайте в пакете Excel файл под именем Книга1_лаб9_(ваша фамилия). В этом файле назовите Лист1 – «Текущие доходы и расходы», Листы 2-5 назовем именами месяцев (например, июнь, июль, август, сентябрь). Переименовать листы можно, например, воспользовавшись контекстным меню названия листа. Записи в главном журнале (лист – «Текущие доходы и расходы») в хронологическом порядке фиксируют все операции по доходам и расходам. Записи в главной книге (остальные листы книги, названные именами месяцев) накапливают на счетах данные о каждой хозяйственной операции из главного журнала по месяцам.

Для облегчения переноса записей из главного журнала в главную книгу в рабочем листе «Текущие доходы и расходы» введите четыре имени диапазонов: ДатаВвода (например, от $А$4 до $А$500), НомерСчета (например, от $С$4 до $С$500), Доход (например, от $D$4 до $D$500) и Расход (например, от $Е$4 до $Е$500). Все эти имена и связанные с ними диапазоны ячеек должны быть на листе «Текущие доходы и расходы». Обратите внимание, что массивы ячеек должныбыть равноразмерны.

Заполните лист «Текущие доходы и расходы» как показано на рисунке 9.1. Заполняя лист «Текущие доходы и расходы», не забудьте правильно задать форматы данных для заполняемых ячеек.

В главной книге (листы с именами месяцев) введите имя ДатаКнига_ноябрь, относящееся к ячейке $A$4, поскольку это – абсолютная ссылка (на это указывают два символа доллара). Абсолютные ссылки всегда указывают на конкретные ячейки. Если перед буквой или номером стоит знак доллара, например, $A$4, то ссылка на столбец или строку является абсолютной. Если необходимо, чтобы ссылки не изменялись при копировании формулы в другую ячейку, пользуются абсолютными ссылками. По умолчанию имена являются абсолютными ссылками.

В Excel кроме абсолютных ссылок на ячейки используются также и относительные ссылки. При создании формулы эти ссылки обычно учитывают расположение относительно ячейки, содержащей формулу. Ссылка может быть полностью относительной относительный столбец и относительная строка (например, C1). Или смешанной относительный столбец и абсолютная строка (C$1), абсолютный столбец и относительная строка ($C1). Введем также имя ГКСчет относящееся к ячейке $D4. Это смешанная ссылка. Зафиксирован только столбец, а строка может изменяться в зависимости от того, куда вводится ссылка на ГКСчет.

В рабочих листах главной книги (листы с названиями месяцев) заполните строку под номером три заголовками, как показано на рисунке 9.2. В столбцы С и D введите соответственно наименования счетов и их номера (см. рисунок 9.2). В ячейке $А$4 установите формат ячейки Дата с сокращенным выводом даты на экран. Введите в эту ячейку даты: 01.11.10 для ноября, 01.12.10 для декабря и 01.01.11 для января месяца соответственно.

Рисунок 9.1 – Лист Главный журнал

В столбцах Доход для сбора соответствующих записей из журнала регистрации в ноябре месяце используется следующая формула:

СУММ(ЕСЛИ(МЕСЯЦ(ДатаВвода)=МЕСЯЦ(ДатаКнига_ноябрь);1;0)*ЕСЛИ(НомерСчета=ГКсчет;1;0)*Доход)

Для выбора соответствующих расходов в ноябре месяце служит формула:

СУММ(ЕСЛИ(МЕСЯЦ(ДатаВвода)=МЕСЯЦ(ДатаКнига_ноябрь);1;0)*ЕСЛИ(НомерСчета=ГКсчет;1;0)*Расход)

Обе эти формулы следует вводить как формулы массива. Набрав формулу, не спешите нажимать <Enter>. Сначала нажмите комбинацию клавиш <Ctrl+Shift>. Вы увидите, что теперь формула заключена в фигурные скобки. Это значит, что Excel принял вашу формулу как формулу массива. Не надо вводить фигурные скобки вручную (с клавиатуры), поскольку в этом случае Excel воспримет формулу как текст.

Формула в Excel выполняет следующую процедуру.

  1.  Сравнивает каждую запись в диапазоне ДатаВвода главного журнала (лист «Текущие доходы и расходы») с месяцем в дате главной книги (листы с названиями месяцев). При соответствии этих значений возвращает значение 1; в противном случае значение – 0.
  2.  Оценивает каждую запись в диапазоне НомерСчета главного журнала. Если номер счета там совпадает с номером текущего счета главной книги, то возвращает значение 1; в противном случае значение – 0.
  3.  Умножает результат, полученный в пункте 1 на результата пункта 2. Только в том случае если выполнены оба предыдущих условия, результат будет равен 1; в противном случае – 0.

Рисунок 9.2 – Рабочий лист главной книги

  1.  Умножает результат, полученный в пункте 3, на записи журнала регистрации в диапазоне Доход (или на Расход, если используется вторая из двух приведенных выше формул).
  2.  Возвращает значение при выполнении пункта 4.

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

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

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

=C:\Финансы\[Главная_книга.xls]”Журнал”!$A$4:$A$215

9.4 Порядок выполнения работы

  1. Разработайте план счетов для ведения бухгалтерии вашей семьи.
  2. Переименуйте листы книги Excel, в которой будете решать задачу.
  3. Заполните 1 и 2 строки листа «Текущие доходы и расходы» так, как показано на рисунке 9.1.
  4. На листе «Текущие доходы и расходы» определите имена для массивов размерностью 500 ячеек для даты операции, номера счета, дохода и расхода.
  5.  Определите формат ячеек для массивов, созданных в предыдущем пункте. Для массива ДатаВвода формат ячеек – формат ячеек Дата. Для массива НомерСчета – формат ячеек целое число. Для массивов Доход и Расход – формат ячеек Денежный.
  6.  Заполните несколько строк листа «Текущие доходы и расходы» данными.
  7. Подготовьте листы главной книги (листы с названиями месяцев).
  8. Заполните строку 3 так, как показано на рисунке 9.2.
  9. Введите дату в ячейку А4.
  10.  Скопируйте с листа «Текущие доходы и расходы» план счетов.
  11.  Введите формулу в Е4 закончите ввод формулы нажатием сочетания клавиш Ctrl + Shift + Enter.
  12.  Воспользовавшись курсором автозаполнение, скопируйте формулу в остальные ячейки столбца «Доход».
  13.  Воспользовавшись курсором автозаполнение, скопируйте формулу в ячейку F4.
  14.  Замените в формуле ссылку на массив Доход на ссылку на массив Расход.
  15.  Воспользовавшись курсором автозаполнение, скопируйте формулу в остальные ячейки столбца «Расход».
  16.  Добавьте ячейки с итоговыми суммами по столбцам Доход и Расход в главной книге (листы с названиями месяцев).
  17.  Добавьте поле остаток.
  18. Оформить результаты лабораторной работы в виде отчета.

9.5 Оформление отчета

Отчет должен содержать следующее:

  1. Вашу фамилию имя отчество и номер группы.
  2. Цель работы.
  3. Название файла созданного в Microsoft Excel.
  4. Письменные ответы на два (по заданию преподавателя) контрольных вопроса.
  5. Выводы.

9.6 Контрольные вопросы

  1. Что такое относительная ссылка на ячейку?
  2. Что такое абсолютная ссылка на ячейку?
  3. Что такое массив ячеек?
  4. Как задать имя массива?
  5. Как использовать имя массива при вводе формулы?
  6. Как правильно вводить формулы?
  7. Как правильно вводить формулу, содержащую несколько функций?

 

А также другие работы, которые могут Вас заинтересовать

74485. Финансовое планирование. Формирование и распределение прибыли на предприятии 159.5 KB
  Планирование финансовой деятельности предприятия тесно связано с конечными результатами производства важнейшим из которых в условиях рыночных отношений является общая прибыль или совокупный доход что требует усиления роли финансов в достижении этих показателей. Доход предприятия или прибыль является в условиях рынка основой самофинансирования всех видов производственнохозяйственной деятельности и социальнотрудовых отношений персонала. Валовая прибыль определяет величину общего совокупного дохода предприятия без...
74486. ТЕХНИКА ПЕРЕГОВОРНЫХ ПРОЦЕССОВ 72.5 KB
  При проведении переговоров важно понимать общие закономерности делового общения что позволит анализировать ситуацию учитывать интересы партнера говорить на общем языке. В процессе переговоров можно выделить три основные стадии: подготовка к переговорам; процесс их ведения; анализ результатов переговоров и выполнение достигнутых договоренностей. Основные этапы переговоров способы подачи позиции Исходный пункт любых управленческих действий это определение цели. Ошибается тот кто в качестве цели переговоров ставит безоговорочное...
74487. Особенности делового общения: деловая беседа, деловые переговоры, деловые совещания, деловые дискуссии 66 KB
  Структура деловой беседы: подготовка к деловой беседе; установление места и времени проведения встречи; начало беседы: вступление в контакт; постановка проблемы и передача информации информирование партнёров; аргументирование выдвигаемых положений; опровержение доводов собеседника; анализ альтернатив поиск оптимального или компромиссного варианта либо конфронтация участников; принятие решения; фиксация договорённостей; завершение беседы; анализ результатов беседы своей тактики общения. является трудным и ответственным делом...
74488. Конфликты. Формы работы с конфликтами и методы их разрешения 217.5 KB
  Конфликты. Конфликты всегда существовали существуют и будут существовать они неотъемлемая часть человеческих взаимоотношений. Возможность возникновения конфликтов существует во всех сферах. Конфликты рождаются на почве ежедневных расхождений во взглядах разногласий и противоборства разных мнений нужд побуждений желаний стилей жизни надежд интересов и личностных особенностей.
74489. Стили и средства общения 479.5 KB
  Конкретный выбор стиля общения определяется многими факторами: личностными особенностями человека его мировоззрением и положением в обществе характеристиками этого общества и многим другим. Все присутствующие знают друг друга лет двадцать собираются вместе 3–4 раза в год сидят несколько часов и говорят об одном и том же. Когда ваша рука захватывает руку другого человека так что ладонь оказывается поверхностью вниз – это свидетельствует о том что вы хотите главенствовать в процессе общения с вашим партнёром рис. Оно бывает необходимо в...
74490. Этика и этикет делового общения 57 KB
  Нравственные эталоны и образцы поведения руководителя: Стремитесь превратить вашу организацию в сплочённый коллектив с высокими моральными нормами общения. etiquette означает установленный порядок поведения гделибо. Деловой этикет – важнейшая сторона морали профессионального поведения делового человека. Деловой этикет – результат длительного отбора правил и форм наиболее целесообразного поведения которое способствовало успеху в деловых отношениях.
74491. Общая характеристика общения 100.5 KB
  Общение – это сложный многоплановый процесс установления и развития контактов между людьми порождаемый потребностями совместной деятельности и включающий в себя обмен информацией выработку единой стратегии взаимодействия восприятия и понимание другого человека. Перцептивная сторона общения Вопрос о том как происходит чтение другого человека что позволяет нам понимать его поведение встают перед каждым из нас. Для того чтобы понимать это необходимо ответить на следующие вопросы: Как формируется первое впечатление Как происходит...
74492. РУКОВОДСТВО И ЛИДЕРСТВО 113 KB
  В первом случае руководитель отождествляя себя с более крупными группами организации нежели с группой подчиненных может считать что эмоциональная привязанность к рабочей группе может стать тормозом на его пути. Однако недостатков больше чем достоинств: высокая вероятность ошибочных решений; подавление инициативы творчества подчиненных замедление нововведений застой пассивность сотрудников; неудовлетворенность людей своей работой своим положением в коллективе; неблагоприятный психологический климат подхалимы козлы отпущения...
74493. ТЕХНИКА И ТАКТИКА АРГУМЕНТИРОВАНИЯ 82 KB
  Правило №1 правило Гомера Очередность приводимых аргументов влияет на их убедительность. Как видно из примера причина неудачи в том что просительница нарушила правило Гомера. Правило №2 правило Сократа Для получения положительного решения по очень важному для вас вопросу поставьте его на третье место предпослав ему два коротких простых для собеседника вопроса на которые он без затруднения скажет вам да. Правило №3 правило Паскаля Не загоняйте собеседника в угол.