Содержание
Введение....................................................................................................... 3
1. Общее назначение Microsoft Excel......................................................... 4
2. Создание таблиц...................................................................................... 8
2.1. Таблицы «Доходы».......................................................................... 8
2.1.1. Назначение таблицы
«Доходы»................................................ 8
2.1.2. Создание заголовка
таблицы.................................................... 8
2.1.3. Создание заголовков
таблицы.................................................. 9
2.1.4. Ввод в таблицу исходных
данных............................................ 9
2.1.5. Расчет итогов за год по
каждой статье расходов..................... 9
2.1.6. Расчет общих итогов за
каждый месяц и за год..................... 10
2.1.7. Вид готовой таблицы
«Доходы»............................................. 10
2.1.8. Построение графика
дохода.................................................... 11
2.2. Таблица «Расчет
подоходного налога отца»................................ 15
2.2.1. Назначение таблицы................................................................ 15
2.2.2. Ввод заголовка таблицы.......................................................... 15
2.2.3. Ввод исходных данных в
таблицу.......................................... 15
2.2.4. Выполнение расчетов.............................................................. 15
2.2.5. Расчет подоходного
налога по годовым данным................... 16
2.2.6. Итоговый вид таблицы............................................................ 16
2.3. Таблица «Расчет
подоходного налога дочери»............................ 18
2.4. Таблица «Коммунальные
услуги»................................................. 18
2.4.1. Создание и заполнение
таблицы............................................. 18
2.4.2. Построение диаграммы
«Коммунальные платежи».............. 19
2.5. Таблица «Текущие расходы»........................................................ 21
2.5.1. Проведение расчетов в
таблице.............................................. 21
2.5.2. Построение диаграммы
расходов по месяцам....................... 22
2.6. Таблица «Остатки»......................................................................... 23
2.6.1. Расчет данных в строках
таблицы.......................................... 23
2.6.2. Расчет суммарных
значений.................................................... 23
2.6.3. Построение диаграммы
сравнения доходов и расходов....... 24
Заключение................................................................................................ 27
Литература................................................................................................ 28
Введение
Компьютерная программа, позволяющая управлять финансовой
информацией, а также данные, с которыми она работает, называются электронными
таблицами.
Электронные таблицы облегчают наблюдение за информацией о
продажах, генерацию стоимостных отчетов и делают многое другое. Главное,
электронные таблицы делают за вас все вычисления и даже волшебным образом
заново подводят итоги, если в таблице
изменится хотя бы одно число.
Первое, что вам встретится в Excel, — это электронный
эквивалент разграфленного листа бумаги. Однако экран далеко не пуст. Напротив,
на нем разместился целый ряд инструментов, необходимых в работе. Некоторые из
них кочуют из одной Windows-программы в другую и должны быть вам знакомы. (В
противном случае обратитесь к книге о Windows для начинающих.) Другие
инструменты предназначены специально для Excel. Пусть вас не ошеломляет этот
парад незнакомых кнопок. Большинство инструментов понятны и просты для
овладевания. Другие столь специализированы, что их можно изучать по мере
необходимости или счастливо избежать.
Рабочие листы и рабочие книги являются неотъемлемой частью
среды Excel. Под "рабочими листами", или просто листами, понимают
основную электронную таблицу. Она подобна отдельной странице книги учета.
Рабочая книга, или просто книга,
напоминает книгу учета — набор листов, сохраняемый в одном файле. (Даже если вы
работаете с единственным листом, он хранится в файле-книге, который
потенциально может содержать целый ворох таких листов.)
Книги удобны для размещения связанных между собой таблиц в
одном файле.
Так, информацию о доходах и расходах семьи можно хранить на
отдельных листах, составляющих единую книгу.
Электронные таблицы, подобные Excel, помогают управлять,
анализировать и представлять данные в виде, показанном ниже. Excel позволяет
организовывать числовую информацию в виде легкодоступных строк и столбцов[1].
1. Общее назначение Microsoft Excel
На экране Excel
появляется сетка из строк и столбцов — электронная книга учета, в которую
вводятся данные.
Каждая клеточка сетки (пересечение строки и столбца)
называется ячейкой.
Обычно в ячейки заносятся числа, но можно поместить туда и
текст с описанием, например, в заголовки строк и столбцов.
Excel может выполнять вычисления и выводить результаты. В
любой ячейке могут храниться результаты вычислений, выполненных над содержимым
любых других ячеек таблицы.
Содержимое ячеек легко изменить. И самое лучшее то, что при
изменении числа, которое используется в вычислениях где-нибудь в другом месте
таблицы, автоматически изменится и результат этих вычислений.
Данные можно разместить так, чтобы их было легко читать как
на экране, так и на печати. Можно разместить заголовки по центру, выделить
текст жирным шрифтом и курсивом, обвести важные ячейки жирной рамкой и многое
другое. Но смотрите, не перестарайтесь! Слишком большое количество элементов
форматирования могут скорее затруднить, чем облегчить чтение таблицы.
Excel умеет создавать разнообразные диаграммы и графики,
позволяющие представить данные более наглядно. Эти графики и диаграммы легко
строятся и дают возможность быстрее проанализировать информацию, они выглядят
убедительнее, чем лес из столбцов цифр.
Excel позволяет легко найти нужные данные. Например, можно
быстро выяснить, когда были максимальные расходы.
Запустить Excel можно выбрав из меню кнопки Пуск (Start)
команду Программы (Programs)
и оттуда — Microsoft Excel.
Возможно, вам повезет обнаружить на рабочем столе пиктограмму под названием
Ярлык для Excel или что-то вроде этого. Тогда можно войти в Excel, дважды
щелкнув на этой пиктограмме[2].
В строке заголовка указано имя приложения и имя
используемого в настоящий момент файла-рабочей книги. (Если окно документа не
максимизировано, эти имена окажутся в двух различных заголовках.) При запуске
Excel открывается чистая рабочая книга, которой присваивается имя Книга1
(Book1). Это временное имя используется до тех пор, пока вы не сохраните книгу,
присвоив ей имя,
Подобно всем программам, написанным для Windows, в Excel
имеется строка меню, при помощи которой можно выполнять разнообразные команды.
Развернуть меню можно, щелкнув на его имени или нажав
одновременно клавишу Alt и подчеркнутую букву в имени меню. Если нужно закрыть
меню, не выполняя команды, просто нужно щелкнуть в любом месте экрана за
пределами меню.
На стандартной панели инструментов и панели инструментов
форматирования размещен целый ряд кнопок, позволяющих быстро выполнить многие
команды Excel при помощи мыши. Например, если щелкнуть на кнопке В, данные
будут выделены жирным шрифтом. Если вы не знаете точно, для чего предназначена
какая-либо кнопка, поместите на нее курсор мыши, и рядом с кнопкой появится ее
название.
Ярлычок листа показывает
на каком листе книги находится пользователь. Например, Листе1, для первого
листа книги. Он напоминает первую страницу книги учета[3].
Каждому столбцу присвоена буква, а каждой строке — номер.
Таким образом, каждая ячейка имеет свой уникальный адрес — комбинацию из буквы,
соответствующей столбцу, и номера строки. Например, ячейка, находящаяся на
пересечении столбца Е и строки 7, называется ячейкой Е7. Активная ячейка, т.е.
ячейка, к которой применяются все выполняемые в данный момент действия, выделяется
толстой рамкой.
Находящаяся в нижней
части экрана строка состояния содержит полезную информацию о том, что в данный
момент делает Excel. Сейчас там написано Готово, это означает, что Excel
выполнил предыдущую операцию и готов принять новые данные. В строке состояния
указываются также имена клавиш, таких как Caps Lock и Num Lock, если эти
клавиши используются.
Наконец, что наиболее удобно, здесь указываются результаты
некоторых действий над выделенными числами. Чтобы выбрать, результат
вычислений, который следует выводить в строке состояния, нужно щелкнуть на нем
правой кнопкой мыши.
Попадая на ячейку, курсор мыши принимает форму большого
знака "плюс".
Но его форма может изменяться в зависимости от того, на
какие области экрана он указывает.
В Строке формул (которая находится под панелями
инструментов) показывается содержимое активной ячейки. В данный момент правая
часть строки формул пуста, поскольку в активной ячейке ничего не содержится.
Область задач предназначена для создания новых файлов,
поиска нужной информации, работы с буфером обмена, вставке иллюстраций. Выбрать
задачу можно, щелкнув на кнопке в верхней части области. При этом внешний вид
области задач изменится в зависимости от выбранной задачи.
Если в строке состояния значится Готово, можно вводить
данные в таблицу. Ввод данных состоит из трех этапов. Во-первых, необходимо
активизировать нужную ячейку. Во-вторых, — ввести данные. Наконец, в-третьих,
следует сообщить компьютеру, что ввод данных окончен. Обычно это делается
посредством нажатия клавиш Enter, табуляции или одной из стрелок. Эта
последовательность действий остается неизменной независимо от того, что
вводится — текст (например, заголовки столбцов) или числа.
Одним из наиболее замечательных свойств электронных таблиц
является их способность хранить в ячейках не только фиксированные данные, но
также и формулы, — наборы инструкций для выполнения вычислений, — и показывать
результаты. Формула может быть простой, как =2+2 (в Excel все формулы
начинаются со знака равенства). Но гораздо полезнее применять формулы со
ссылками на ячейки вместо фиксированных чисел. Ссылки на ячейки — это просто
адреса ячеек, сообщающие Excel, что следует производить вычисления над текущим
содержимым указанных ячеек. Если впоследствии данные, на которые ссылаются,
будут изменены, то результат формулы изменится автоматически. Иногда это
называют автоматический пересчетам).
Главное, что требуется от рабочих таблиц, — это точность
содержащихся в них данных и простота их использования. Но не следует
пренебрегать и эстетическим видом таблиц. Данные, выделенные полужирным шрифтом
или курсивом, скорее привлекут внимание пользователя к важной информации.
Округление чисел с сохранением достаточного числа десятичных разрядов облегчит
в будущем занесение новых данных. И наконец, приятный внешний вид таблиц лишь
увеличит радость от хороших новостей, а в случае неудачи — несколько сгладит ее
горечь.
Excel обладает целым арсеналом функций форматирования, с
помощью которых можно улучшить внешний вид рабочих таблиц как при отображении
их на экране, так и при печати. В этой главе описано, как форматируются
текстовые и числовые данные, как с помощью обрамления и заливки можно улучшить
внешний вид ячеек и как выравнивать содержимое ячеек относительно их границ;
как в случае необходимости отменить форматирование.
Форматировать таблицы в Excel чрезвычайно просто. Но все же
не следует поддаваться соблазну чересчур увлекаться этим, превратив
украшательство в самоцель. Рабочая таблица, на оформление которой затрачено так
много сил, может оказаться слишком пестрой, что лишь усложнит чтение данных.
Excel позволяет менять несколько стандартных параметров
готовых шрифтов, таких, как собственно шрифт, начертание (полужирный и курсив)
и размер шрифта (кегль). Можно также воспользоваться рядом специальных
преобразований текста — от самых простых до самых затейливых. При использовании
подобного способа форматирования в действительности преобразуются сами ячейки.
Все, что совершается над содержимым конкретной ячейки, — будь то преобразование
текстовой информации, числовых данных или специальных символов, — связано с процессом форматирования. Если изменить
содержимое отформатированной ячейки, то любые режимы форматирования,
установленные для нее, распространятся и на новые данные.
2. Создание таблиц
2.1. Таблицы «Доходы»
2.1.1. Назначение таблицы «Доходы»
В таблице «Доходы» будут указываться суммарные доходы
каждого члена семьи. Суммарные доходы за первое полугодие, а затем помесячно за
второе полугодие.
По каждой статье расходов должно вычисляться итоговое
значение за год, а также суммарные доходы за 1 полугодие и за каждый следующий
месяц.
2.1.2. Создание заголовка таблицы
Выделим диапазон ячеек A1:I1, из
контекстного меню выберем пункт Формат ячеек.
В открывшемся окне перейдем на вкладку Выравнивание. На этой
вкладке зададим выравнивание по центру по горизонтали и по вертикали.
Также установим флажок объединение ячеек. После этого
вкладка Выравнивание примет вид:
На вкладке Шрифт зададим полужирное начертание и 14
размер шрифта:
2.1.3. Создание заголовков таблицы
Выделим диапазон ячеек A3:A4, в
окне Формат
ячеек перейдем на вкладку Выравнивание установим флажок объединение
ячеек.
В полученное объединение ячеек введем текст Статья доходов.
В объединение ячеек B3:B4
введем текст 1 полугодие.
В объединение ячеек C3:H3
введем текст Месяцы.
В ячейки C4
– H4 введем номера
месяцев от VII до XII соответственно.
В объединение ячеек I3:I4
введем текст Итого за год.
2.1.4. Ввод в таблицу исходных данных
В ячейки A5:A10 введем названия статей
расходов, например, Зарплата отца, Зарплата дочери и т.д.
В ячейки B5:B10 введем суммарные значение
доходов за 1 полугодие по каждой статье доходов.
В ячейки C5:H10 введем помесячные доходы
по каждой из статей с VII
по XII
месяцы.
2.1.5. Расчет итогов за год по каждой статье
расходов
Рассмотрим вычисление итогового значения за год по статье
расходов Зарплата отца.
Перейдем в ячейку I5 и нажмем кнопку Автосумма[4]
на панели инструментов.
Для суммирования выделим диапазон ячеек B5:H5.
После этих действий в ячейке I5 будет записана расчетная формула =СУММ(B5:H5).
Чтобы ввести аналогичные расчетные формулы для других строк
таблицы применим Автозаполнение.
Для этого подведем указатель мыши к правому нижнему углу
ячейки I5, когда
указатель примет вид черного крестика, растянем выделение на диапазон ячеек I6:I10.
При этом ссылки на исходные ячейки, участвующие в расчетных
формулах, будут изменяться автоматически для каждой строки.
2.1.6. Расчет общих итогов за каждый месяц и за год
В ячейку A11
введем поясняющий текст ИТОГО:.
Для всех ячеек диапазона A11:I11
зададим полужирное начертание, нажав
соответствующую кнопку на панели инструментов.
Чтобы рассчитать суммарный доход за 1 полугодие, перейдем в
ячейку B11, нажмем
кнопку Автосумма на панели
инструментов и выделим диапазон ячеек B5:B10.
После этого в ячейке B11 будет записана формула =СУММ(B5:B10).
В остальные столбцы введем аналогичные формулы при помощи Автозаполнения.
Для этого подведем указатель мыши к правому нижнему углу
ячейки B11 и когда он
примет вид черного крестика, расширим выделение на диапазон ячеек C10:I10.
В расчетных формулах указанных ячеек ссылки на исходные
ячейки будут обновляться автоматически для каждого столбца.
2.1.7. Вид готовой таблицы «Доходы»
Когда будут сделаны все вышеописанные операции, таблица
«Доходы» будет иметь вид:
|
Доходы
|
|
|
|
|
|
|
|
|
|
|
|
Статьи доходов
|
1 полугодие
|
Месяцы
|
Итого за год
|
|
VII
|
VIII
|
IX
|
X
|
XII
|
XII
|
|
Зарплата отца
|
60000
|
8300
|
8000
|
9500
|
9700
|
10000
|
11320
|
116820
|
|
Зарплата дочери
|
16800
|
2800
|
3000
|
3300
|
3200
|
3000
|
2600
|
34700
|
|
Пенсия матери
|
13800
|
2300
|
2300
|
2300
|
2300
|
2450
|
2450
|
27900
|
|
Пособие сына по
безработицы
|
4800
|
800
|
800
|
830
|
830
|
840
|
840
|
9740
|
|
Пенсия сына
|
12600
|
2100
|
2100
|
2220
|
2220
|
2300
|
2300
|
25840
|
|
Алименты на
внучку
|
15000
|
2500
|
2500
|
3200
|
3000
|
3000
|
3000
|
32200
|
|
ИТОГО:
|
123000
|
18800
|
18700
|
21350
|
21250
|
21590
|
22510
|
247200
|
Для просмотра этой таблицы в формульном режиме выполним пункт главного меню Сервис > Зависимости формул >
Режим проверки формул[5].
В формульном режиме
получим:
2.1.8. Построение графика дохода
Построим график суммарных доходов по месяцам за второе
полугодие.
На панели инструментов кликнем по кнопке Мастер
диаграмм для запуска одноименного мастера.
На первом шаге выберем вид График.
На втором шаге зададим диапазон ячеек C11:H11, в котором рассчитаны суммарные доходы за II полугодие.
На вкладке Ряд зададим ячейки, значения из
которых будут отображаться в подписях оси Х – номера месяцев II квартала, т.е. ячейки C4:H4.
Далее зададим название графика и подписи осей графика:
Перейдя на вкладку Легенда сбросим флажок Добавить
легенду:
На следующем шаге зададим сохранение диаграммы на имеющемся
листе Доходы:
В результате будет получен следующий график доходов:
2.2. Таблица «Расчет подоходного налога отца»
Таблицу начнем создавать на новом пустом листе книги Microsoft Excel.
Кликнув правой кнопкой по ярлыку листа, зададим ему имя Под.налог
и заработок отца.
2.2.1. Назначение таблицы
В создаваемой таблице будет произведен расчет подоходного
налога отцом семьи за год в зависимости от получаемой зарплаты.
2.2.2. Ввод заголовка таблицы
В объединение ячеек A1:I1
введем заголовок таблицы Подоходный налог и заработок отца.
Для заголовка зададим 14 размер шрифта, полужирное
начертание, а также выравнивание по центру.
В объединения ячеек B3:G3,…,B7:G7 введем текстовые строки Районный
коэффициент от оклада, Доплата за стаж работы, Минимальный
размер заработной платы, Удержание в Пенсионный фонд от общей суммы
ЗП и Льгота по подоходному налогу в минимальных зарплатах.
В ячейки H3:H7 введем соответствующие
значения.
Для ячеек H3,
H4, H6, H7 зададим процентный формат данных.
Для ячеек A10
– H10 зададим Ориентацию
текста под 90о.
2.2.3. Ввод исходных данных в таблицу
В ячейку A11
введем текст За I полугодие, в ячейку A12 название месяца Июлю.
Чтобы ввести названия остальных месяцев, подведем указатель
мыши к правому нижнему углу ячейки A11 и когда он примет вид черного крестика, удерживая нажатой
правую кнопку мыши, растянем выделение на диапазон ячеек A12:A17.
При отпускании правой кнопки мыши из контекстного меню
выберем пункт Заполнить по месяцам.
В ячейку B11
введем формулу =6*7000.
В ячейки B12-B17 введем оклады отца за
каждый месяц второго полугодия соответственно.
2.2.4. Выполнение расчетов
Для подсчета доплаты за стаж работы в ячейку D11 введем формулу =B11 *$H$3. Так
как в ячейке B11
записан суммарный оклад за первое полугодие, а в ячейке H3 – Районный коэффициент от оклада.
Для расчета районного коэффициента в ячейку C11 введем формулу =(B11+D11)*$H$3.
Так как в ячейке B11
записан суммарный оклад за 1 полугодие, в ячейке D11 рассчитана Доплата за стаж, а в
ячейке H3 – записан Районный
коэффициент от оклада. Знак $ в ссылках на ячейку используется, чтобы
при копировании данной формулы оставались постоянными ссылки на исходные ячейки
Чтобы вычислить итоговый размер начислений за 1 полугодие в
ячейку E11 введем
формулу =B11+C11+D11. В
данной формуле в ячейке B11
записан суммарный оклад за 1 полугодие, в ячейке C11 – доплата за Районный коэффициент, а в
ячейке D11 – Доплата
за стаж
В ячейку F11
будет запишем формулу =400*6.
Удержание в пенсионный фонд составляет 1% и записан в ячейку H6. Поэтому в ячейку G11 введем формулу =E11*$H$6.
Для определения Подоходного налога в ячейку H11 введем формулу =(E11-F11)*13%.
Чтобы вычислить итоговый заработок, который будет выдан на
руки, в ячейку I11
введем =E11-G11-H11. Так как в ячейке
E11 записано суммарное
начисление, в ячейке G11
– удержание
в Пенсионный фонд, а в ячейке H11 – Подоходный налог.
Для проведения аналогичных расчетов для остальных месяцев,
применим Автозаполнение.
2.2.5. Расчет подоходного налога по годовым данным
Введем поясняющий текст Подоходный налог по годовым данным в
объединение ячеек A18:G18.
Зададим для текста выравнивание по правому краю и полужирное
начертание.
Для определения суммы подоходного налога перейдем в ячейку H18, нажмем кнопку Автосумма на панели инструментов и для
суммирования выделим диапазон ячеек H11:H17
В результате в ячейке H18 будет записана формула =СУММ(H11:H17).
2.2.6. Итоговый вид таблицы
После выполнения всех вышеописанных операций таблица Расчет зарплаты и подоходного налога отца
примет вид:
|
Подоходный
налог и заработок отца
|
|
|
|
|
|
|
|
|
|
|
|
|
Районный
коэффициент от оклада
|
30,00%
|
|
|
|
Доплата за стаж
работы
|
30,00%
|
|
|
|
Минимальный
размер заработной платы
|
600
|
|
|
|
Удержание в
Пенсионный фонд от общей суммы ЗП
|
1,00%
|
|
|
|
Льгота по
подоходному налогу в минимальных ЗП
|
2,00%
|
|
|
|
|
|
|
|
Месяцы
|
Оклад
|
Доплаты
|
Итого
|
Льготы по
подоходному налогу
|
Удержания
|
Заработок на
руки
|
|
Районный
коэффициент
|
за стаж
|
в Пенсионный
фонд
|
Подоходный
налог
|
|
За I полу-годие
|
42 000,00
|
16 380,00
|
12 600,00
|
70 980,00
|
2 400,00
|
709,80
|
8 915,40
|
61 354,80
|
|
Июль
|
7 000,00
|
2 730,00
|
2 100,00
|
11 830,00
|
400,00
|
118,30
|
1 485,90
|
10 225,80
|
|
Август
|
7 000,00
|
2 730,00
|
2 100,00
|
11 830,00
|
400,00
|
118,30
|
1 485,90
|
10 225,80
|
|
Сентябрь
|
7 500,00
|
2 925,00
|
2 250,00
|
12 675,00
|
400,00
|
126,75
|
1 595,75
|
10 952,50
|
|
Октябрь
|
7 500,00
|
2 925,00
|
2 250,00
|
12 675,00
|
400,00
|
126,75
|
1 595,75
|
10 952,50
|
|
Ноябрь
|
8 000,00
|
3 120,00
|
2 400,00
|
13 520,00
|
400,00
|
135,20
|
1 705,60
|
11 679,20
|
|
Декабрь
|
8 000,00
|
3 120,00
|
2 400,00
|
13 520,00
|
400,00
|
135,20
|
1 705,60
|
11 679,20
|
|
Подоходный
налог по годовым данным
|
18 489,90
|
|
При просмотре данной таблицы в формульном режиме получим:
2.3. Таблица «Расчет подоходного налога дочери»
Данную таблицу получим, скопировав на отдельный лист только
что созданную в пункте 1.2. таблицу.
Затем во всех строках заменим месячную зарплату.
При этом итоговые данные будут пересчитаны автоматически.
Получим:
|
Подоходный
налог и заработок дочери
|
|
|
|
|
|
|
|
|
|
|
|
|
Районный
коэффициент от оклада
|
30,00%
|
|
|
|
Доплата за стаж
работы
|
30,00%
|
|
|
|
Минимальный
размер заработной платы
|
600
|
|
|
|
Удержание в
Пенсионный фонд от общей суммы ЗП
|
1,00%
|
|
|
|
Льгота по
подоходному налогу в минимальных ЗП
|
2,00%
|
|
|
|
|
|
|
|
Месяцы
|
Оклад
|
Доплаты
|
Итого
|
Льготы по
подоходному налогу
|
Удержания
|
Заработок на
руки
|
|
Районный
коэффициент
|
за стаж
|
в Пенсионный
фонд
|
Подоходный
налог
|
|
За I полугодие
|
25 200,00
|
9 828,00
|
7 560,00
|
42 588,00
|
2 400,00
|
425,88
|
5 224,44
|
36 937,68
|
|
Июль
|
4 000,00
|
1 560,00
|
1 200,00
|
6 760,00
|
400,00
|
67,60
|
826,80
|
5 865,60
|
|
Август
|
4 100,00
|
1 599,00
|
1 230,00
|
6 929,00
|
400,00
|
69,29
|
848,77
|
6 010,94
|
|
Сентябрь
|
3 900,00
|
1 521,00
|
1 170,00
|
6 591,00
|
400,00
|
65,91
|
804,83
|
5 720,26
|
|
Октябрь
|
3 800,00
|
1 482,00
|
1 140,00
|
6 422,00
|
400,00
|
64,22
|
782,86
|
5 574,92
|
|
Ноябрь
|
4 200,00
|
1 638,00
|
1 260,00
|
7 098,00
|
400,00
|
70,98
|
870,74
|
6 156,28
|
|
Декабрь
|
4 000,00
|
1 560,00
|
1 200,00
|
6 760,00
|
400,00
|
67,60
|
826,80
|
5 865,60
|
|
Подоходный
налог по годовым данным
|
10 185,24
|
|
2.4. Таблица «Коммунальные услуги»
2.4.1. Создание и заполнение таблицы
На новом листе книги Microsoft Excel создадим
таблицу заданной структуры.
После ввода всех исходных данных, а также расчетных формул,
лист с таблицей примет вид:
Или при просмотре в формульном режиме получим:
2.4.2. Построение диаграммы «Коммунальные платежи»
До начала построения диаграммы выделим диапазоны ячеек A5:A12, E5:E12, G5:G12, I5:I12, K5:K12, M5:M12 и O5:O12 в которых записаны наименования
платежей, а также их суммарные значения в каждом месяце второго полугодия.
Для запуска Мастера
диаграмм нажмем одноименную кнопку на панели инструментов.
На первом шаге мастера выберем Гистограмма из списка стандартных диаграмм.
Так как предварительно были выделены необходимые ячейки, то
будут автоматически созданы 6 отдельных рядов – для каждого вида платежа.
Для Подписей оси X введем
VII, VIII, IX, X XI, XII.
Для Имен рядов зададим ячейки, в которых
записаны номера.
Окно ввода исходных данных для построения диаграммы примет
вид:
На следующем шаге зададим название диаграммы Квартплата
за II полугодие,
подпись оси X – Месяцы,
а подпись оси Y – Сумма платежа (руб).
Сохраним полученную диаграмму на листе с исходной таблицей[6].
В результате будет получена диаграмма:
2.5. Таблица «Текущие расходы»
2.5.1. Проведение расчетов в таблице
В данной таблице будут рассчитываться текущие расходы,
проводимые семьей ежемесячно.
Сюда входят ежемесячная суммарная квартплата, рассчитанная в
соответствующей таблице.
Например, для переноса суммы квартплаты за первое полугодие,
перейдем в ячейку B5
листа Текущие расходы и введем знак =.
Затем перейдем на лист Коммунальные платежи и кликнем по
ячейке E13.
После этого в ячейке C5 таблицы Текущие расходы
будет записана формула ='Коммунальные
платежи'!$E$13.
Для расчета суммарной стоимости квартплаты за II полугодие в ячейку I5 введем формулу =СУММ(C5:H5).
Для расчета размера квартплаты за год, в ячейку J5 введем формулу =B5+I5.
Заполним таблицу остальными данными о текущих расходах.
Данная таблица в формульном режиме будет иметь вид:
2.5.2. Построение диаграммы расходов по месяцам
На первом шаге Мастера
диаграмм выберем тип График с маркерами[7].
На втором шаге зададим отдельные ряды для каждого типа
расходов и для итоговых расходов по месяцам:
В результате будет получен график итоговых расходов по
месяцам:
2.6. Таблица «Остатки»
2.6.1. Расчет данных в строках таблицы
Таблицу Остатки
начнем формировать на отдельном листе книги Microsoft Excel.
В объединение ячеек A1:D1
введем заголовок таблицы Остатки.
В ячейки A3,
B3, C3 и D3 введем заголовки столбцов таблицы Месяцы, Доход,
Расход
и Остаток.
Для первого заголовка месяца введем За первое полугодие, а
далее названия месяцев с июля по декабрь включительно.
Все исходные данные для этой таблицы вставим в виде ссылок
из ранее созданных таблиц, которые были рассчитаны ранее.
Например, для ввода размера доходов за первое полугодие,
выделим ячейку B4 и
введем в нее знак =.
Затем перейдем на лист Доходы и кликнем по ячейке B11. В результате в ячейку B4 листа Остатки будет введена
формула =Доходы!$B$11.
Для переноса размера расхода за первое полугодие в ячейку C4 введем формулу ='Текущие расходы'!$B$11.
Чтобы получить остаток за первое полугодие в ячейку D4 введем формулу
Аналогичные формулы введем для расчета остатков по месяцам
во втором полугодии.
2.6.2. Расчет суммарных значений
Например, чтобы рассчитать суммарный доход за первое
полугодие и суммарно по месяцам за второе полугодие, выделим ячейку B11, нажмем кнопку Автосумма на панели инструментов и
выделим диапазон ячеек B4:B10.
После чего в ячейке B11 будет записана формула для суммирования =СУММ(B4:B10).
Аналогичные формулы для суммирования введем для столбцов Расход
и Остаток.
После проведения всех расчетов, получим следующую таблицы Остатки:
|
Остатки
|
|
|
|
|
|
|
Месяцы
|
Доход
|
Расход
|
Остаток
|
|
1 полугодие
|
123000
|
27883,01
|
95116,99
|
|
Июль
|
18800
|
4653,36
|
14146,64
|
|
Август
|
18700
|
4208,6
|
14491,4
|
|
Сентябрь
|
21350
|
10377,27
|
10972,73
|
|
Октябрь
|
21250
|
11661,88
|
9588,12
|
|
Ноябрь
|
21590
|
7248,11
|
14341,89
|
|
Декабрь
|
22510
|
6716,11
|
15793,89
|
|
Итого:
|
247200
|
72748,34
|
174451,7
|
Или при просмотре данной таблицы в формульном режиме:
2.6.3. Построение диаграммы сравнения доходов и
расходов
Для этой диаграммы выберем тип График с маркерами, помечающими
точки данных[8].
На следующем шаге перейдем на вкладку Ряд и создадим три ряда –
для Доходов,
Расходов
и Остатков соответственно.
Например, для первого ряда зададим имя из ячейки B3, т.е. Доход. Для значений
зададим ячейки B5:B10, в которых введены доходы по месяцам.
Подписи данных зададим названия месяцев, т.е. ячейки A5:A10.
Аналогично добавим еще два ряда.
На следующем шаге зададим названия диаграммы и осей:
Полученный график сохраним на имеющемся листе Остатки.
В итоге получим:
Заключение
В настоящей курсовой работе рассматривался процесс анализа
бюджета семьи при помощи электронной таблицы Microsoft Excel.
Было разработано 6 различных таблиц, данные из которых переносились
между таблицами.
Для облегчения понимания выполняемых действий, в работу были
вставлены копии рассчитанных таблиц, а также все таблицы приводились в режиме
отображения формул, в котором видны все расчетные формулы.
Для облегчения анализа полученных результатов, многие
таблицы снабжались диаграммами, графически отображающие изменение данных.
Литература
1.
Козырев А.А. Самоучитель работы на персональном
компьютере. Учебное пособие. Изд. 2-е, переработанное и дополненное. СПб.:
Изд-во Михайлова В.А., 2000 – 304 с.
2.
д-р Конрад Карлберг Бизнес-анализ с помощью EXCEL Издано:
2001, М., Издательский дом "Вильямс", 480 стр.
3.
Леонтьев В.П.
Новейшая энциклопедия персонального компьютера 2003. М.: ОЛМА-ПРЕСС,
2003. - 920 с.: ил.
4.
Левин А. Самоучитель работы на компьютере. Москва,
издательство «Нолидж», 1999 – 624 с.
5.
Новиков Ф., Яценко А. Microsoft Office XP в целом
6.
Хэлворсон М., Янг М. Эффективная работа: Office XP. Издательский дом «Питер»,
2004. – 1072 с.
7.
Фигурнов В.Э. IBM PC для пользователя. Краткий курс. – М. ИНФРА-М, 1999. – 480 с.:
ил.
8.
Эд Ботт, Вуди Леонард Использование Microsoft Office
2000. Специальное издание Издано:2000,
Вильямс, 1024 стр.
[1] Леонтьев В.П. Новейшая энциклопедия персонального
компьютера 2003. М.: ОЛМА-ПРЕСС, 2003. -
стр. 211
[2] Эд Ботт, Вуди Леонард Использование
Microsoft Office 2000. Специальное издание Издано:2000, Вильямс, стр. 14
[3] Хэлворсон М., Янг М.
Эффективная работа: Office XP.
Издательский дом «Питер», 2004. – стр. 227
[4] д-р Конрад Карлберг Бизнес-анализ
с помощью EXCEL Издано: 2001, М.,
Издательский дом "Вильямс", стр. 202
[5] Фигурнов В.Э. IBM PC для пользователя. Краткий
курс. – М. ИНФРА-М, 1999. – стр. 213
[6] Левин А. Самоучитель
работы на компьютере. Москва, издательство «Нолидж», 1999 – стр. 213
[7] Новиков Ф., Яценко А. Microsoft
Office XP в целом, стр. 354
[8] Козырев А.А. Самоучитель
работы на персональном компьютере. Учебное пособие. Изд. 2-е, переработанное и
дополненное. СПб.: Изд-во Михайлова В.А., 2000 – стр. 92