Применение табличного процессора Excel для расчетов по оплате труда

Размещено на http://www.allbest.ru/

Применение табличного процессора Excel для расчетов по оплате труда

Содержание

Введение

1. Терминология и основные приемы работы с электронной таблицей

1.1 Основные понятия Excel

2. Ведение бухгалтерского учета с использованием табличного процессора Excel

3. Основные типы данных используемые в табличном процессоре Excel

4. Ввод исходных данных, форматирование текстовых и числовых данных, оформление таблицы

4.1 Ввод данных в ячейки

4.2 Изменение содержимого ячеек

5. Алгоритм расчетов по оплате труда в табличном процессоре EXCEL

Список используемой литературы

Приложение

Введение

Принцип «разделяй и властвуй» как нельзя лучше подходит к системам электронных таблиц, и, в частности, к Excel. Основу этой системы составляет рабочий лист рабочей книги -- пространство для хранения данных, разделенное на ячейки. В обычном понимании -- это таблица, строки и столбцы которой составляют структуру рабочего листа. Структурирование как способ хранения всевозможных объектов является основополагающим принципом во многих областях человеческой деятельности. Когда вы ищите книгу в библиотеке, вы имеете дело с карточками -- на каждой из них записано название только одной книги. Память компьютера, с которым вы работаете, - это структура, созданная на кремниевой пластине, набор ячеек, каждая из которых хранит минимум данных. Чем тоньше эта структура, тем она ценнее, в то время как необработанная пластина стоит во много раз дешевле. Электронщик складывает радиодетали одного номинала в отдельную коробочку, но при производстве этих деталей каждая из них упаковывается отдельно -- иначе будет невозможно использовать их в станках для автоматизированного монтажа печатных плат. Подобных примеров можно привести множество. Все они объединены одним принципом -- структурирование и упорядоченность продукта повышает его ценность. Этот принцип справедлив и для компьютерных систем, работающих с данными: систем управления базами данных (СУБД) и систем электронных таблиц. Но для создания структуры необходимо вначале затратить определенные усилия. Ценность структурированных данных заключается в том, что в мире программ разделить данные по смыслу гораздо сложнее, чем слить ранее разделенные, -- ведь компьютеры пока еще человеческим разумом не обладают. (Правда, в последнее время многое сделано в этом направлении, и Excel 7.0 -- яркий тому пример.) Как правило, такое разделение выполняется вручную, с участием оператора, и требует больших затрат рабочего времени, поэтому при создании новых массивов компьютеризованных данных следует так продумать организацию процесса их ввода, чтобы впоследствии приходилось переделывать как можно меньше.

Самое экзотическое свойство рабочего листа Excel (электронной таблицы) - способность хранить в смежных ячейках данные разного типа. Большинство других программных систем не обладают таким свойством. В СУБД, например, в каждом поле можно хранить данные только одного типа. В текстовых редакторах еще проще -- все, что в них вводится, воспринимается как текст. А если не как текст, то как инородные объекты. Если бы в Excel не существовали средства, позволяющие оперировать данными разного типа, то ценность такой программы была бы невелика. К счастью, создатели Excel это понимали и включили в программу множество процедур, которые позволяют произвести анализ и разбор блоков разнородных данных. Примером тому могут служить операции построения диаграмм, сводных таблиц и географических карт. Исходными данными для этих операций служат массивы данных, снабженные заголовками столбцов и/или строк.

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

Excel - система во многом универсальная. Эта программа может хранить текстовую информацию и выполнять функции текстового редактора. Конечно, не такого мощного, как Microsoft Word, но и не такого примитивного, как Microsoft Write (WordPad). Excel выполняет также функции СУБД и системы математической обработки данных. В общем, это программа не только для того, чтобы считать в столбик.

1. Терминология и основные приемы работы с электронной таблицей

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

1.1 Основные понятия Excel

Чаще всего пользователям Excel приходится иметь дело с файлами рабочих книг, имеющих расширение .XLS, шаблонами .XLT и файлами рабочих областей .XLW. Рабочая книга может содержать рабочие листы, которые и являются воплощением понятия «электронная таблица», а также листы диаграмм, листы модулей (с текстами программ на языке Visual Basic) и листы диалога. На рис. 1.1 показан рабочий лист Excel. В данный момент рабочая книга Книга 1 оформлена в виде окна. Кнопки Развернуть, Свернуть и Закрыть -- стандартные для Windows 95. Кнопка Развернуть в этом случае позволяет увеличить окно до максимальных размеров (в зависимости от загруженности экрана другими панелями), превратив его в обычную область на экране. Если же вам требуется только максимально увеличить размер окна, то для этого необходимо использовать команду Окно > Расположить. Кнопка Закрыть позволяет не только свернуть окно, но ц закрыть файл рабочей книги. Перед закрытием программа спрашивает пользователя о необходимости сохранения изменений в файле. Кнопка Свернуть позволяет временно убрать окно с экрана, оставив его ярлычок. Используя этот ярлычок, можно восстановить окно до нормального размера.

Рис. 1.1. Элементы рабочего листа

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

В нижней части окна расположены инструменты для управления рабочей книгой: кнопки для смены листов и ярлычки листов. Если вы щелкните по ярлычку листа левой кнопкой мыши, то выбранный лист станет текущим. если же правой -- откроется контекстное меню, которое позволит выполнить операции над листом в целом: скопировать лист, дать ему новое имя, удалить лист и т. д. Кстати, в Excel существует множество контекстных меню. которые привязаны к самым различным объектам на экране. Например, если щелкнуть правой кнопкой мыши по кнопке смены листов, то откроется меню со списком всех листов рабочей книги, позволяющее выбрать требуемый лист.

2. Ведение бухгалтерского учета с использованием табличного процессора Excel

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

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

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

* бухгалтерский и банковский учет;

* планирование и распределение ресурсов;

* проектно-сметные работы;

* инженерно-технические расчеты;

* обработка больших массивов информации;

* исследование динамических процессов.

Наибольшей популярностью пользуются табличные процессоры Microsoft Excel, Lotus 1-2-3, Quatro Pro, SuperCalc.

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

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

3. Основные типы данных используемые в табличном процессоре Excel

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

Числовые. Могут быть представлены:

1. в целом виде, числа 1 5 12 и т.д.;

2. вещественном 125.87 2.9, при вещественном представлении числа целая и дробная часть числа разделяется точкой;

3. экспоненциальном в виде мантиссы и порядка числа например Мантисса - набор цифр участвующий в записи числа, порядок - степень числа 10, порядок указывается после буквы Е. Обычно мантисса представляется числом меньшим единицы. Так число 1Е3 = 1000, 0.25Е-7 - 0,000000025;

Даты. Дата указывается в цифровом виде вначале день, затем месяц и год. Разделителем является точка. Если год нс указывается, то берется текущий год из системного времени. 2.5 - 2 мая, 2.5.99 2 мая 1999 года.

Логические величины. Принимают два значения ИСТИНА или ЛОЖЬ, являются результатом вычисления логических функций.

Стандартные функции. Вспомогательные алгоритмы реализующие различные алгоритмы вычисления математических, тригонометрических, статистических, финансовых, логических и т.д. функций. SIN, СУММА, ОКРУГЛ, ЕСЛИ и т.д.

Формульные выражения. Представляют собой совокупность соответствующих взаимосвязанных, определенным образом, данных разных типов. Ввод формульного выражения всегда начинается со знака =. При вводе формульных выражений могут использоваться знаки математических операций: + сложение, -вычитание, * умножение, / деление, ^ возведение в степень, знак >, <, >=, <=, <>. Числовые константы, переменные помещаются в определенные ячейки таблицы, а в формуле используются ссылки на адреса ячеек. Адресация может быть относительной например А5 или абсолютной $A$5. При относительной адресации в процессе копирования происходит изменение адреса ячейки, при абсолютной адрес остается постоянным. Абсолютная адресация предполагает закрепление только столбца $А5, только строки A$5 или и того и другого $А$5. Абсолютную адресацию обычно используют для -ячеек содержащих величины общие для всей таблицы расчетов, например минимальная заработная плата или процент отчислений.

4. Ввод исходных данных, форматирование текстовых и числовых данных, оформление таблицы

4.1 Ввод данных в ячейки

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

4.2 Изменение содержимого ячеек

Если выбрать заполненную данными ячейку и набрать новые данные, предыдущие данные будут заменены. Для редактирования содержимого ячеек необходимо поставить курсор на ячейку и нажать клавишу F2, содержимое ячейки появится в строке формул и щелкнув мышью в нужном месте строки можно с помощью клавиш редактирования (Delete, Backspace) изменить содержимое ячейки. Другой вариант изменения (корректировки) данных - двойной щелчок мыши на ячейке. Курсор появляется внутри ячейки и можно редактировать введенные данные.

5. Алгоритм расчетов по оплате труда в табличном процессоре EXCEL

1. Загрузитm программу Excel. Ознакомьться с интерфейсом программы Внимательно изучить элементы табличного процессора (строка меню, панели инструментов, строка формул, заголовки строк и столбцов, строка состояния, горизонтальная и вертикальная линейки прокрутки, ярлычки рабочих листов).

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

3. Для объединения ячеек B1-G1 необходимо предварительно выделить блок этих ячеек и затем нажать кнопку . После чего нужно два раза щелкнуть мышью но области выделения и ввести нужный текст (Ставки подоходного налога). Аналогично нужно ввести текстовые данные в следующие блоки ячеек:

A3-I3 - Сведения о сотрудниках предприятия (наименование предприятия);

4. В ячейки B2-G2 ввести %-е ставки подоходного налога, в ячейку Н2-1% (отчисления в пенсионный фонд), в ячейку I2- 1% (профсоюзный взнос).

5. В ячейки A4-I15 ввести данные о сотрудниках предприятия.

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

7. Сохраните выполненную работу на личном диске. Для этого откройте меню Файл, выполните команду «Сохранить как». В открывшемся окне наберите имя файла - tab-l.xls.

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

1. Открыть ранее сохраненный на личном диске файл - tab-1.хls. Переименовать лист 2 (новое имя листа- Январь).

2. На листе Январь в ячейку А1 ввести наименование - Зар. плата. В ячейку В1 ввести название месяца - Январь. В ячейку А2 ввести наименование -Кол-во раб. дней. В ячейку В2 ввести число рабочих дней в месяце - 22.

3. Перейти на лист Сведения, выделить блок ячеек А4:Е15 (№ п/п, Таб. номер, Ф.И.0., Должность, Оклад). Скопировать выделенные данные в буфер обмена (с помощью кнопки Копировать в буфер на панели инструментов). Перейти на лист Январь, выделить блок ячеек АЗ:Е14. Скопировать из буфера обмена данные (с помощью кнопки Вставить из буфера панели инструментов).

4. На листе Январь в ячейку F3 ввести наименование - Кол-во отраб. дней. В ячейки F4:F14 ввести данные об отработанных за январь днях сотрудниками предприятия (согласно рис.2). В ячейку G3 ввести наименование - Кол-во дней по болезни. В ячейки G4:G14 ввести данные о больничных днях сотрудников предприятия в январе (согласно рис.2. См.Приложения).

5. На листе Январь в ячейку НЗ ввести наименование - Начислено на оклад. Сумма начисленная на оклад рассчитывается по формуле: (Оклад / кол-во раб. дней в месяце) * кол-во отраб. дней. Расчет этой суммы в табличном процессоре можно выполнить следующим образом: установить курсор в ячейку H4, набрать с помощью клавиатуры следующую формулу: =E4/$BS2*F4 (выражение SBS2 - абсолютная ссылка на ячейку В2, сначала в формуле набирается В2, затем нужно нажать клавишу F4). После набора формулы выполнить ввод формулы нажатием клавиши Enter. Скопировать эту формулу в ячейки Н5:Н14. Для этого необходимо установить курсор в ячейку Н4 и затем перевести курсор в правый нижний угол этой ячейки и после того как курсор примет вид: + нажать левую кнопку мыши и не отпуская ее, переместить курсор до ячейки Н14 и затем отпустить левую кнопку мыши: В результате исходная формула будет скопирована остальные ячейки столбца. Например в ячейке Н5 будет введена следующая формула: =E5/SBS2*F5. табличный процессор копирование данный

6. В ячейку 13 ввести наименование - Больничный лист.

Порядок расчета больничных листов: непрерывный стаж > или = 8 лет оплачивается 100 % среднедневной заработной платы; непрерывный стаж <8, но > или = 5 лет оплачивается 80% среднедневной заработной платы, непрерывный стаж <5 лет оплачивается 60 % среднедневной заработной платы. Для расчета больничных листов сотрудников предприятия в табличном процессоре можно воспользоваться логической функцией ЕСЛИ. Установить курсор в ячейку I4, набрать =. Далее нажать кнопку (мастер функций) на панели инструментов или левее строки формул. В открывшемся окне мастера функций необходимо выбрать в левой панели категорию - Логические, в правой панели имя функции - ЕСЛИ. Затем нажать кнопку Далее> (Шаг >) окна мастера функций. В следующем окне мастера функций набрать следующие данные:

В этом поле ничего не вносить, а нажать кнопку . Во вновь открывшемся окне мастера функций, выбрав функцию ЕСЛИ, в окне этой функции ввести следующие данные:

Это поле снова не заполнять, а нажать кнопку . Вновь находим функцию ЕСЛИ и в окне этой функции завершаем ввод данных для расчета больничного листа:

После ввода данных нажать кнопку Готово окна функции ЕСЛИ, далее нажать клавишу Enter. Таким образом в строке формул должна содержаться следующая формула, введенная в ячейку 14:

=ЕСЛИ(Сведсния!С5>=8;С4/$В$2*Е4;ЕСЛИ(Сведения!С5>=5;С4/$В$2*Е4* 0,8;ЕСЛИ(Сведения!С5<5;С4/$В$2*Е4*0,6;0))). Скопировать формулу расчета больничного листа в ячейки 15:114.

7. В ячейку J3 ввести наименование - Начислено всего. Начисленная заработная плата за месяц равна сумме: (Начислено на оклад)+(Персональная надбавка)+(Больничный лист). В ячейку J4 ввести следующую формулу: ==Н4+Сведения!Р5+14. Скопировать формулу в ячейки J5:J14.

8. В ячейку КЗ ввести наименование - Пенсионный фонд 1 %. В ячейку К4 ввести формулу удержания в пенсионный фонд: =J4*0,01. Скопировать формулу в ячейки К5:К14.

9. Для расчета подоходного налога необходимо четко и правильно учитывать условия расчета подоходного налога, указанные на рис.3. В ячейку L3 ввести наименование - Сумма облагаемая подоходным налогом. Сумма облагаемая подоходным налогом (для данного предприятия и за 1-й месяц года) рассчитывается следующим образом:

Если (Начислено всего) < 5000 руб. - =(Нач. всего)-(Мин. зар. плата+Кол-во иждив.)*2 - (Пенс. фонд).

Если (Начислено всего) >или = 5000 руб. и < или = 20000 руб. - = (Нач. всего)-(Мин. зар. плата+Кол-во иждив. - (Пенс. фонд).

Если (Начислено всего) > 20000 руб. - = (Нач. всего) - (Пенс. фонд). В ячейку L4 (с помощью мастера функций и логической функции ЕСЛИ) ввести следующую формулу:

=ЕСЛИ(J4<5000; J4-(Сведсния!;$А$2+Сведения!Н5*Сведепия!$А$2)*2 - K4;ЕСЛИ(J4<=20000;J4- Сведения!$А$2 - Сведения.'Н5*Сведсния!$А$2 -K4;J4-K4). Скопировать формулу в ячейки L5:L14.

В ячейку МЗ ввести наименование - Подоходный налог.

В ячейку М4 (используя логическую функцию ЕСЛИ) ввести формулу расчета подоходного налога. Скопировать формулу в ячейки М5:М14.

10. В ячейку N3 ввести наименование - Профсоюзный взнос. В ячейку N4 ввести формулу пня расчета профсоюзного взноса: =J4*0,01. Скопировать формулу в ячейки N5:N14.

11. В ячейку 03 ввести наименование - Алименты. В ячейку 04 ввести формулу для расчета алиментов: =J4*Сведения!I5. Скопировать формулу в ячейки O5:O14.

12. В ячейку РЗ ввести наименование - Удержано всего. В ячейку Р4 ввести формулу суммирующую удержания: =K4+M4+N4+O4. Скопировать формулу в ячейки Р5:Р14.

13. В ячейку Q3 ввести наименование - К выплате. В ячейку Q4 ввести формулу (Начислено всего- Удержано всего): =J4-P4. Скопировать формулу в ячейки O5:O14.

14. Сохранить выполненную работу в этом же файле (lab-1.xls), выполнив команду Файл, Сохранить.

Ставки подоходного налога на 01.01.1998 г.

Примечание: Прежде чем взять подоходный налог, из зарплаты, кроме пенсионною взноса (1%), высчитывают 1 минимальную зарплату (83 руб.50 коп.) на самого работника и еще по одной минимальной заработной плате на каждого иждивенца. Например: если у работника 2-е детей, то из налогооблагаемой базы вычитаются 3 минимальные заработные платы.

Ставки и порядок расчета подоходного налога

Список используемой литературы

1. Богумирский Б. Эффективная работа на IBM РС-СПб: Питер, 1996. - 688.

2. Фигурнов "IBM PC для пользователя (7-е издание)", М., ИНФРА-М, 1997, 640 с.

3. Информатика: Учебник / Под ред. проф. Н.В. Макаровой. -М.: Финансы и статистика, 1997. - 768 с.

4. Информатика: Практикум по технологии работы на компьютере / Под ред. Н.В. Макаровой. - М.: Финансы и статистика, 1997. - 384 с.

5. Учебно-методические пособия, разработанные на кафедре информационных технологий Сочинского Государственного Университета Туризма и Курортного Дела.

6. Гончаров А. Microsoft Excel 7.0 в примерах. - СПб: Питер, 1996.

7. Додж М., Кайнет К. и др. Эффективная работа с Excel 7.0 для Windows 95. - СПб: Питер, 1996.

8. Долженков В. и др. Практическая работа с Microsoft Excel 7.0. - СПб: БХВ - Санкт-Петербург, 1998.

Приложение

Таблица исходных данных (Лист 1- EXCEL) для расчетов заработной платы сотрудников предприятия

Расчеты по оплате труда за январь (Лист 2- EXCEL)

Размещено на Allbest.ru