Использование программы MS Excel для решения бухгалтерских задач
При проведении расчетов по формулам перед вычислениями необходимо установить в столбцах необходимый тип данных. В числах целая часть от дробнойотделяется запятой, например, 5,7. В адресах ячеек используются буквылатинскогоалфавита.
Практическая работа 1
Цель работы: Научиться решать бухгалтерские задачи в MS Excel.
• Задание 1. Основываясь на данных таблицы 2.14, провести бухгалтерские вычисления.
Порядок выполнения задания
1. Создайте новый файл под именем «Книга 2».
2. На Листе 1 создайте табл.2.14 по образцу(табл. 2.14). Обозначьтеграницы, параметры заливки, выравнивания, вид числа задайте произвольные.
3. Применить следующие формулы для расчета таблице:
- в столбце «Начислено/всего»используйте формулу = оклад + премия,в ячейке ЕЗ она будет выглядеть следующим образом: =C3+D3;
-в столбце «Удержано/Подоходный налог» используйте формулу = начислено / всего × подоходный налог, в ячейке F3 она будет выглядеть следующим образом: =ЕЗ*13%;
- в столбце «Удержано/Профсоюзный налог» используйте формулу = начислено / всего × профсоюзные взносы, в ячейке G3 она будет выглядеть следующим образом: =E3*1%;
- в столбце «Удержано, р./всего»используйте формулу = Удержано / Подоходный налог + Удержано / Профсоюзный налог, в ячейке НЗ она будет выглядеть следующим образом: =F3+G3;
- в столбце «На руки»используйте формулу = начислено / всего – удержано/ всего, в ячейке I3она будет выглядеть следующим образом: =ЕЗ-Н3.
| Подоходный налог = 13% и Профсоюзный налог = 1% можно поместить в ячейках памяти вне таблицы и в формулах использовать абсолютные адреса этих ячеек. |
4. Для расчета остальных ячеек примените автозаполнение.
5. Для ячеек с результатами расчетов задать формат Финансовый.Для этого выделите блок данных, нажмите правую кнопку мыши и выберите в контекстном меню Формат ячеек.В открывшемся окне выберите вкладку Числои задайте параметры форматирования ячеек: числовой формат - финансовый, обозначение – нет, число десятичных знаков после запятой - 2.
Таблица 2.14
| Таб. Номер | Ф.И.О. | Начислено | Удержано | На руки | ||||
| оклад | премия | всего | подоходный | профс. | всего | |||
| Алексин А.С. | ||||||||
| Баликов И.С. | ||||||||
| Борова В.Н. | ||||||||
| Иванов А.Т. | ||||||||
| Колзин Н.М. | ||||||||
| Мухин С.В. | ||||||||
| Новеев А.К. | ||||||||
| Прошина В.К. | ||||||||
| Петров И.И. | ||||||||
| Савкин А.А. | ||||||||
| Солодов М.Д. | ||||||||
| Степина Н.А. | ||||||||
| Трушкин М.Э | ||||||||
| Ялин С.М. | ||||||||
| Итого: |
Таблица 2.14. Итоговая
| Таб. Номер | Ф.И.О. | Начислено | Удержано | На руки | ||||
| оклад | премия | всего | подоходный | профс. | всего | |||
| Алексин А.С. | ||||||||
| Баликов И.С. | 1319,5 | 101,5 | ||||||
| Борова В.Н. | ||||||||
| Иванов А.Т. | ||||||||
| Колзин Н.М. | ||||||||
| Мухин С.В. | 1189,5 | 91,5 | ||||||
| Новеев А.К. | ||||||||
| Прошина В.К. | ||||||||
| Петров И.И. | 656,5 | 50,5 | ||||||
| Савкин А.А. | ||||||||
| Солодов М.Д. | ||||||||
| Степина Н.А. | ||||||||
| Трушкин М.Э | 864,5 | 66,5 | ||||||
| Ялин С.М. | ||||||||
| Итого: |
6. Присвойте Листу 1 имя «Задача 1».
• Задание 2. Основываясь на данных таблицы 2.15 провести бухгалтерские вычисления и построить диаграмму.
Порядок выполнения задания
1. На Листе 2 введите данные, произведите необходимые вычисления по приведенному образцу (табл. 2.15).
2. Формулы для расчета:
- Фонд соц. страхования = Фонд зарплаты х 5,4%; =В5*5,4 %;
- Пенсионный фонд = Фонд зарплаты х 28,0%; =В5*28%;
- Фонд мед. страхования = Фонд зарплаты х 3,6%; =В5*3,6%;
- Фонд занятости = Фонд зарплаты х 1,5%; =В5*1,5%.
3. Чтобы рассчитать результат в строке «Итого» необходимо выделить каждый столбец без заголовка и выбрать вкладку Главная • Редактирование • Сумма«Σ».
Для выполнения автосуммы можно пользоваться кнопкой Автосуммирование (Σ) на панели инструментов или функцией СУММ. Выберите вкладку Формулы • Библиотека функций • Автосумма.В качестве первого числа выделите группу ячеек с данными для расчета суммы.
Таблица 2.15
| Сотрудники | Фонд зарплаты | Отчисления | |||
| Фонд соц. страхования | Пенсионный фонд | Фонд мед. страхования | Фонд занятости | ||
| 5,40% | 28,00% | 3,60№ | 1,50% | ||
| Инженеры | 104542,0 | ||||
| Бухгалтеры | 12401,7 | ||||
| Администрация | 9184,6 | ||||
| Рабочие | 25271,5 | ||||
| Мастера ПО | 131939,4 | ||||
| Автомеханики | 12464,3 | ||||
| Уборщицы | 5237,6 | ||||
| Сторожа | 8400,0 | ||||
| Итого |
Таблица 2.15 Итоговая
| Сотрудники | Фонд зарплаты | Отчисления | |||
| Фонд соц. страхования | Пенсионный фонд | Фонд мед. страхования | Фонд занятости | ||
| 5,40% | 28,00% | 3,60№ | 1,50% | ||
| Инженеры | 104542,0 | 5645,27 | |||
| Бухгалтеры | 12401,7 | 669,69 | |||
| Администрация | 9184,6 | 495,97 | |||
| Рабочие | 25271,5 | 1364,66 | |||
| Мастера ПО | 131939,4 | 7124,73 | |||
| Автомеханики | 12464,3 | 673,07 | |||
| Уборщицы | 5237,6 | 282,83 | |||
| Сторожа | 8400,0 | 453,60 | |||
| Итого | 309441,0 | 16709,81 | 866,44 |
4. Постройте диаграмму (рис. 2.19). Для этого выделите столбцы А («Сотрудники») и В (Фонд зарплаты) и выберите вкладку Вставка • Диаграммы • Круговая • Объемная разрезанная круговая.
5. Для внесения заголовка диаграммы и подписей данных выберите всплывающую вкладку Работа с диаграммами • Макет • Подписи • Подписи данных.
Рис. 2.19
6. Присвойте Листу 2 имя «Задача 2».
• Задание 3. На Листе 3 ввести данные, произвести необходимые вычисления (табл. 2.16) и построить диаграмму по приведенному образцу (рис. 2.20).
Порядок выполнения задания
1. Формулы для расчета:
- Стаж на текущий год=текущий год - год поступления; =$А$11 - С2;
- Итоговый оклад=оклад, р- + оклад, р × повышение оклада/100; =D2+ D2*F2/100.
Таблица 2.16
| № п/п | Ф.И.О. | Год поступления | Оклад, р. | Стаж на текущий год | Повышение оклада, % | Итоговый оклад |
| Лепин Н.И. | ||||||
| Уланов Т.О. | ||||||
| Петрова И.Г. | ||||||
| Пименова Е.Н. | ||||||
| Репина А.В. | ||||||
| Никонов В.В. | ||||||
| Сидоров У.Р. | ||||||
| Текущий год | ||||||
Таблица 2.16. Итоговая
| № п/п | Ф.И.О. | Год поступления | Оклад, р. | Стаж на текущий год | Повышение оклада, % | Итоговый оклад |
| А | B | C | D | E | F | G |
| Лепин Н.И. | ||||||
| Уланов Т.О. | ||||||
| Петрова И.Г. | ||||||
| Пименова Е.Н. | ||||||
| Репина А.В. | ||||||
| Никонов В.В. | ||||||
| Сидоров У.Р. | ||||||
| Текущий год | ||||||
2. Постройте диаграмму. Удерживая нажатой клавишу Ctrl, выделите столбцы в (Ф.И.О.), D (Оклад, р) и С (Итоговый оклад) с данными расчета результатов и выберите вкладку Вставка • Диаграммы • Гистограмма • Объемная с группировкой.
3. Введите название диаграммы при помощи всплывающей вкладки Работа с диаграммами • Макет • Подписи • Подписи данных.
Рис. 2.20
4. Присвойте Листу 3 имя «Задача 3» и сохраните файл в «Книга 2».
Практическая работа 2
Цель работы: Основываясь на данных таблицы 2.17 провести бухгалтерские вычисления и построить диаграмму (рис. 2.21).
Порядок выполнения работы
1. Откройте файл под именем «Книга2». На листе 4 введите данные по образцу табл. 2.17.
Таблица 2.17.
| Продажи за 1-е полугодие 20… г. | |||||
| Месяц | Товар | Артикул | Цена за кг, р. | Количество | Сумма, р. |
| Мясо | |||||
| Молоко | |||||
| Творог | |||||
| Январь | Итого: | ||||
| Мясо | |||||
| Молоко | |||||
| Творог | |||||
| Февраль | Итого: | ||||
| Мясо | |||||
| Молоко | |||||
| Творог | |||||
| Март | Итого: | ||||
| Мясо | |||||
| Молоко | |||||
| Творог | |||||
| Апрель | Итого: | ||||
| Мясо | |||||
| Молоко | |||||
| Творог | |||||
| Май | Итого: | ||||
| Мясо | |||||
| Молоко | |||||
| Творог | |||||
| Июнь | Итого: | ||||
| ИТОГО: |
2. В столбце Dскопируйте ячейки 3-5 за «Январь» и вставьте в этот же столбец в «Февраль», «Март», «Апрель», «Май», «Июнь»
Формула для расчета Суммы, р. = цена за кг, р. × количество.
4. При помощи автозаполнения произведите расчеты для каждого месяца отдельно.
5. Чтобы рассчитать результат в строке «Итого», необходимо выделить в столбце D ячейки за определенный месяц и выбрать вкладку Главная • Редактирование • Сумма«Σ».
6. Чтобы подсчитать «Итого» за все месяцы, необходимо при нажатой клавише Ctrlвыделить содержимое последнего столбца «Итого» для каждого месяца и выбрать вкладку Главная • Редактирование • Сумма«Σ».
Таблица 2.17. Итоговая
| Продажи за 1-е полугодие 20… г. | |||||
| Месяц | Товар | Артикул | Цена за кг, р. | Количество | Сумма, р. |
| Мясо | |||||
| Молоко | |||||
| Творог | |||||
| Январь | Итого: | ||||
| Мясо | |||||
| Молоко | |||||
| Творог | |||||
| Февраль | Итого: | ||||
| Мясо | |||||
| Молоко | |||||
| Творог | |||||
| Март | Итого: | ||||
| Мясо | |||||
| Молоко | |||||
| Творог | |||||
| Апрель | Итого: | ||||
| Мясо | |||||
| Молоко | |||||
| Творог | |||||
| Май | Итого: | ||||
| Мясо | |||||
| Молоко | |||||
| Творог | |||||
| Июнь | Итого: | ||||
| ИТОГО: |
7.Чтобы построить диаграмму, составьте новую табл. 2.17.11 Итоговая, используя данные табл. 2.17. Итоговая. Выделите табл. 2.17.1. Итоговая и постройте резанную круговую диаграмму (рис. 2.21).
Таблица 2.17.1 Итоговая
| Продажи за 1-е полугодие 2011 г. | |
| Январь | |
| Февраль | |
| Март | |
| Апрель | |
| Май | |
| Июнь |
Рис. 2.21
8. Присвойте Листу 4 имя «Задача 4» и сохраните файл «Книга 2».
Практическая работа 3
Цель работы; Составить сводные таблицы о доходах и расходах семьи в течение одного года на разных листах, составить баланс семьи, работая с данными, расположенными на двух листах. Определить итоговые, средние значения доходов и расходов по каждой статье, построить диаграмму, отражающую изменения расходов в течение года.
Порядок выполнения работы
1. Откройте файл «Книга 2».
2. Назовите Лист 5 «Доходы». На Лист 5 введите таблицу по образцу (табл. 2.18). Выберите границу и заливку произвольно.
Таблица 2.18
| Семейные доходы | ||||
| Месяц | Отец | Мать | Ребенок | Итого |
| Январь | ||||
| Февраль | ||||
| Март | ||||
| Апрель | ||||
| Май | ||||
| Июнь | ||||
| Июль | ||||
| Август | ||||
| Сентябрь | ||||
| Октябрь | ||||
| Ноябрь | ||||
| Декабрь | ||||
| Итого | ||||
| Среднее значение | ||||
| Минимум | ||||
| Максимум |
3. Для расчета в столбце Е (Итого) выделите построчно ячейки в столбцах В, С, Dи выберите вкладку Главная • Редактирование • Сумма«Σ». При помощи автозаполнения произведите расчет в остальных ячейках.
4. Чтобы рассчитать результат в строке «Итого» по столбцам В, С, D, необходимо выделить ячейки в столбце и произвести автосуммирование.
5. Для подсчета значения в строке «Минимум» выделите ячейку для получения результата выберите вкладку Главная • Редактирование • Сумма«Σ» • Минимуми задайте область расчетов - ячейки с января по декабрь в столбце, по которому производиться вычисление.
6. Для подсчета значения в строке «Максимум» выделите ячейку для получения результата, выберите вкладку Главная • Редактирование • Сумма«Σ» • Максимуми задайте область расчетов - ячейки с января по декабрь в столбце, по которому производится вычисление.
Таблица 2.18. Итоговая
| Семейные доходы | ||||
| Месяц | Отец | Мать | Ребенок | Итого |
| Январь | ||||
| Февраль | ||||
| Март | ||||
| Апрель | ||||
| Май | ||||
| Июнь | ||||
| Июль | ||||
| Август | ||||
| Сентябрь | ||||
| Октябрь | ||||
| Ноябрь | ||||
| Декабрь | ||||
| Итого | ||||
| Среднее значение | 333,3333 | 32083,3333 | ||
| Минимум | ||||
| Максимум |
7. На листе 6 наберите таблицу по образцу (табл. 2.19). Выберите границу и заливку произвольно.
Таблица 2.19
| Семейные расходы | |||||||||
| Месяц | Квартира | Телефон | Транспорт | Питание | Одежда | Досуг | Прочее | Итого | Баланс |
| Январь | |||||||||
| Февраль | |||||||||
| Март | |||||||||
| Апрель | |||||||||
| Май | |||||||||
| Июнь | |||||||||
| Июль | |||||||||
| Август | |||||||||
| Сентябрь | |||||||||
| Октябрь | |||||||||
| Ноябрь | |||||||||
| Декабрь | |||||||||
| Итого | |||||||||
| Среднее значение | |||||||||
| Минимум | |||||||||
| Максимум |
8. Для расчета в столбце I (Итого) выделите построчно ячейки в столбцах В-Н и выберите вкладку Главная • Редактирование • Сумма«Σ». При помощи автозаполнения произведите расчет в остальных ячейках.
9. Поизвести расчет в графе «Баланс» по формуле:
Баланс = 'Доходы'!Е3-'Расходы'!I3
Открыть Лист 6, в столбце «Баланс» поставить знак «=», войти в Лист 5, в столбец «Итого» таблицы «Семейные доходы».
Выделить первую строку в столбце «Итого», поставить знак «-» минус, войти на Лист 6, выделить первую строку в столбце «Итого» таблицы «Семейные расходы», нажать клавишу Enter, результат появится в ячейке. Тянуть за крестик (+) по всему столбцу «Баланс» до «Декабря».
10. Для подсчета в строке «Среднее значение» выделите ячейку для получения результата, выберите вкладку Главная • Редактирование • Сумма«Σ» • Среднее значениеи задайте область расчетов - ячейки с января по декабрь в столбце, по которому производится вычисление.
11.Аналогично п. 5 и 6 рассчитайте Максимум и Минимум в табл. 2.19.
Таблица 2.19. Итоговая
| Семейные расходы | |||||||||
| Месяц | Квартира | Телефон | Транспорт | Питание | Одежда | Досуг | Прочее | Итого | Баланс |
| Январь | |||||||||
| Февраль | |||||||||
| Март | |||||||||
| Апрель | |||||||||
| Май | |||||||||
| Июнь | |||||||||
| Июль | |||||||||
| Август | |||||||||
| Сентябрь | |||||||||
| Октябрь | |||||||||
| Ноябрь | |||||||||
| Декабрь | |||||||||
| Итого | |||||||||
| Среднее значение | 1641,667 | 445,5833 | 804,58333 | 14651,08 | 1204,167 | 258,3333 | 19190,42 | 12892,92 | |
| Минимум | |||||||||
| Максимум |
12. Удерживая нажатой клавишу Shift или Ctrl (зависит от настройки клавиатуры), выделите столбцы А (Месяц) и любой другой с данными расчета результатов и постройте резаную круговую диаграмму (рис. 2.22).
Рис. 2.22.
Диаграмма отражает изменение расходов на телефонную связь в течение одного года.
13. Сохраните файл.