ФайлÞОбласть печатиÞЗадать

7. Нажать на кнопку предварительного просмотра - .

8. Вкладка Страница - указать ориентацию, масштаб.

9. Вкладку Поля -центрирование по вертикали и горизонтали,

установить поля страницы.

10. Оформить Колонтитулы, нажать на кнопку:

10.1. Создать Верхний колонтитул – задать месяц с помощью кнопки (&[Лист]), шрифт с помощью кнопки , нажать на ОК;

10.2. Создать Нижний колонтитул с помощью кнопки в центральной секции установить номер текущей страницы и нажать

на ОК;

10.3. Нажать на ОК, закрыть окно просмотра;

10.4. Сохранить вид экрана под именем Январь: ВидÞ Диспетчер отчетов, в окне «Диспетчер отчетов» нажать на кнопку Добавить, ввести имя отчета Январь, нажать на Добавить, в Разделе для добавления в поле Лист будет Январь, нажать Добавить.

11. В поле Лист раскрыть список, выбрать Февраль - Добавить и т.д. до

Декабря.

12. Поставить флажок для сплошной нумерации страниц в поле.

13. ОК. Отчет должен иметь вид:

Рис. 4.13

14. Самостоятельно выполнить за другие месяцы.

15. Чтобы просмотреть, как будут выглядеть все листы при печати, необходимо сцепить листы с Января по Декабрь, нажать на предварительный просмотр и пролистать от начала до конца.

Конечно, данный пример демонстрирует только дополнительные

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

4.4. Вариант заработной платы с отдельными книгами

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

Постановказадачи.

Табель-

календарь

Виды начислений

Просмотр

Начислено Удержано Ведомость

Справочник

Виды удержаний

Блок-схема варианта зарплаты

Печать

1. В выходном документе, хранящемся в отдельной книге(файле) будут

представлены только поля Таб_номер, ФИО, Всего начислено, Всего удержано и К выдаче. Листы переименованы по месяцам. Все данные

берутся с соответствующих книг, кроме расчетной К выдаче и пустой

колонки Подпись.

Рис. 1

2. В качестве НСИ будет Справочник сотрудников:

Рис. 2

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

Рис. 3

4. Книги Начисления и Удержано будут промежуточными, все расчеты будут происходить в них; если какая-то часть расчета еще не будет автоматизирована, то они будут проходить по статье Прочие.

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

Рис. 4

Рис. 5

Порядокдействий

1. Создаем Справочник сотрудников (рис.2) и сохраняем под тем же именем. Не закрываем! Все 5 книг сохраняем, но не закрываем: будут нужны

для дальнейших ссылок.

* Следите за номерами строк во всех книгах: при нашей технологии они

должны соответствовать друг другу!

2. Опять Файл Þ Создать Þ Книга т.е. Табель-календарь(рис.3). Ссылки для колонок А(Таб_ном) и В(Фио) будем формировать следующим образом:

a. выделить блок А10:А17(B10:B17);

b. =;

c. Через Окно или панель задач открыть Справочник, в нем выделить блок А10:А17(B10:B17);

d. Комбинация клавиш для копирования на диапазон ячеек Ctrl + Enter.

В конце также Сохраним книгу под именем

Табель-календарь.

3. Файл Þ Создать Þ Книга т.е. Начислено(рис.4).

a. Колонки A и B аналогично;

b. Колонка C (Начисления за осн. работу): также выделить весь блок С10:С17, равно =,перейти к соответствующим книгам и т.д.

c. Колонка D-F, H-I в нашем примере пока не рассчитываются, для

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

d. Колонка G (Премия) пусть будет одинаковой для всех – 100% от

оклада:

e. Колонка J (Всего) - стандартное автосуммирование.

f. Колонка K (C начала года) – накапливаемая. За Январь просто копия значений колонки J (Специальная вставкаÞЗначения), а за последующие месяцы с нарастанием:

4. Опять Файл Þ Создать Þ Книга т.е. Удержано(рис. 5).

a. Колонки A и B аналогично;

b. Колонка С (Льготы) через функцию ЕСЛИ:

c. Колонка D (Налогооблагаемая база=Начислено - Льготы:

d. Колонка Е (Профсоюзные взносы = 1% от начислено):

e. Колонка F (Подоходный налог = 13% от базы):

f. Колонка G (Алименты на детей – на 2 детей 33%, одного - 25%):

g. Другие виды удержаний могут быть взяты с листов по отдельным видам, но мы сейчас просто вставим с «потолка». Колонки Всего и С начала года рассчитывать мы уже умеем.

5. Расчет итоговой Ведомости (Рис. 19) уже сводится к простейшей формальности, расчетные формулы просты и наглядны:

и

и

6. За последующие месяцы Февраль и Март проделайте

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

В дальнейшем при открытии взаимосвязанных книг будет запрашиваться разрешение на обновление связей. Будьте милостивы, разрешайте…

Резюме: Преимущества использованной технологии заключаются прежде всего:

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

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

кадров, а файл может находиться на другом диске или даже сервере

ƒ в простоте реализации и сопровождения без приглашения штатных программистов

Самостоятельная работа №9

h В наш шаблон расчета з\п добавьте книги с листами по месяцам Расчет больничных листов, Отчисления за кредиты. При начислениях больничных листов понадобится стаж работы со Справочника: при стаже

до 5-ти лет выплачивается 50% от оклада, до 8-ми лет – 80%, свыше –

100%. Уточните в бухгалтерии. Расчет стажа работы также можно

автоматизировать: Стаж до поступления на данную работу + календарное

время работы на данном предприятии.

4.5. Базы данных в Excel

Обычно базы данных (БД) предназначены для хранения больших объемов структурированной информации и работа с БД осуществляется при помощи специальных программ: систем управления базами данных (СУБД). В Office это Субд Access. Ho u Excelпозволяет работать с таблицами как с базами данных. Базой данных в Excelслужит обычная таблица, где:

· первая строка обозначает заголовки полей БД

· строка - запись БД

· столбец - поле записи

Все инструменты работы с БД сосредоточены в одном пункте меню

Данные.

Задание 4.5.1.

1. Самостоятельно создать таблицу БД, изображенную на Рис. 4.14.

Рис. 4.14

2. Выделить всю таблицу, кроме 1-ой строки.

3. ДанныеÞ форма.

Появится окно формы, изображенное на Рис. 4.15.

Рис. 4.15

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

для редактирования ( в нашем случае месяц).

Достоинством работы с формой является автоматическое копирование формул и форматов данных на последующие записи.

Работа с формами.

Перемещение по полям записи.

·TAB - перейти к следующему полю;

·Shift+TAB - вернуться к предыдущему полю;

·Щелчок мышкой по полю

Перемещение по записям:

· Полоса прокрутки;

·Клавиши управления курсором ;

·CTRL+ - первая запись;

·CTRL+ - последняя запись;

·Enter - следующая запись;

·Кнопки Назад, Далее;

Добавление / удаление записей.

·Кнопки Добавить - ввод новой записи.

Новая запись добавляется только в конец БД!.

При выделении таблицы как БД нужно выделить и нижнюю незаполненную строку, иначе вы не сможете добавить новые записи!

·Кнопка Удалить- удаляет текущую запись;

· Кнопка Восстановить- восстанавливает исходные значения полей записи, если еще не было перехода на другую запись

4. При помощи формы добавить несколько записей для Сидорова и Иванова, Пушкина, удалить одну запись для Иванова, изменить суммы начислений для Петрова. Кнопкой Закрыть завершить работу с формой.

5. Поиск записей по критерию: Найти все записи, начинающиеся на букву Пиз суммы начислений >500.

а) ДанныеÞ формаÞ критерии

б) ФИО - П*

в) Сумма начислений - >200.

г) Кнопками предыдущая / Следующая подвигайтесь по БД и убедитесь в том, что критерии включены.

6. Отсортируйте БД по алфавиту ФИО, а внутри по возрастанию кода

начислений: ДанныеÞ Сортировка.

7. Выбор записей через фильтр.

Вывести записи с кодом начислений =1:

а) выделить таблицу без 1-ой строки (A2:D10);

б) данные - фильтр – автофильтр;

в) снять выделение с таблицы (у каждой ячейки заголовков таблицы появилась кнопка Список ;

г) раскрыть список ячейки Код начислений;

д) из списка выбрать настройка и в появившемся окне

Пользовательского автофильтра установить условие выборки = и

01;

е) ОК. Должны выводится только данные с условием =1.

9. Вывести записи для всех Ивановых, которым начислено меньше 100

рублей:

а) Снять предыдущий фильтр Код начислений, установив на первую команду: (Все).

б) В списке «Фамилия, имя, отчество» через кнопку выбрать (Условие…) и в окне Пользовательский автофильтр установить равно, Иванов *, кнопка И, а в списке «Сумма начислений» –

(Условие …), меньше, 100, кнопка И.

Перед применением автофильтра предыдущего условия надо выключить через команду (Все).

Автофильтр отменяется через ДанныеÞфильтрÞснять отметку.

При печати кнопки фильтра не выводятся.

10. Подведение итогов.

Еще одна дополнительная возможность - подведение итого, как промежуточных, так и общих через ДанныеÞ Итоги. Единственное требование к БД: записи должны быть отсортированы по тем столбцам, по которым хотим получить промежуточные итоги.

Пример: Получить по всем сотрудникам итоговую сумму начислений.

10.1. Отсортировать таблицу по возрастанию ФИО;

10.2. Встать в любую клетку таблицы;

10.3. ДанныеÞ итоги;

10.4. В окне «Промежуточные итоги»: первая строка - ФИО, вторая

- сумма, третья - включить сумма,

Таблица примет примерно следующий вид:

Рис. 4.16

Для каждой фамилии подсчитана сумма начислений и есть итоговая сумма по всему столбцу.

Кнопки «-»и «+»слева от таблицы позволяют выключать и включать режимы подведения итогов. С их помощью можно оставить

только итоги или промежуточные. Попробуйте разные режимы. На рис.

4.16 приведен вид экрана, когда выводятся только промежуточные и общий итоги.

Рис. 4.17

4.6. Моделирование связей БД

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

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

те записи, в которых количество поступления больше нуля.

Но есть способ лучше - предварительное кодирование информации

и использование связей между листами (таблицами) через коды товаров. Код товара пусть будет трехзначным и позиционным: ХХХ -где первый знак означает код группы товаров, второй и третий - код товара. Пусть 1

- хлебобулочные изделия, 2 -бакалейные товары, 3 -кондитерские, 4 -

молочные, 5 -мясные, 6 -рыбные и т.д.

Постановка задачи.

На Листе 1 будет размещаться выходная информация «Учет поступления товаров», а данные колонок А ,В ,С будут браться с листа 2 при помощи встроенной функции ВПР. Функция ВПР выбирает из базы данных запись с указанным значением и возвращает значение поля записи. Имеет 4 параметра :

1 -искомое значение,

2 -область БД, где осуществляется поиск ,

3 -номер поля БД от 1 до N, где осуществляется поиск ,

4 -необязательный параметр ,принимающий 2 значения : ИСТИНА

или ЛОЖЬ (по умолчанию ИСТИНА).

Отрицательным результатом применения функции ВПР будет код ошибки #Н/Д -нет данных. Надо его дополнительно обработать при помощи функции EНД, принимающей значения ЛОЖЬ или ИСТИНА, проверяя появление ошибки типа #Н/Д. Также потребуется уже знакомая функция ЕСЛИ, позволяющая при отсутствии соответствующей записи в БД выводить определенное сообщение. Данные для примера приведены на рисунках 4.18 и 4.19. Если логика понятна, то приступить к решению задачи.

Порядок действий.

1. Переименовать Лист 2 в Справочник и ввести конкретные данные

в колонки А, В, С(другие колонки можно только обозначить: их данные нам не нужны).

Рис. 4.18

2. Отсортировать лист Справочник по возрастанию кода товара.

Любая БД, где происходит поиск конкретной записи, обязательно должна быть отсортирована по возрастанию соответствующего

кода.

3. Переименовать Лист 1 в Поставки и ввести данные в 1 и 2 строки (оформить заголовок и «шапку» документа). К ячейке Е1применить встроенную функцию Сегодня.

4. В колонки В, Dи Еввести оперативные данные, а значения в колонке F вычислить самостоятельно (Рис. 4.18). А вот данные колонок

Аи Свзять с листа «Справочник».

Рис. 4.19.

4. В ячейку АЗввести формулу следующим образом:

a. Вызвать функцию ЕСЛИ;

5.2. Сформулировать логическое условие (первую строку) для

ЕСЛИ:

а) нажать кнопку , в левом углу (в начале) строки формул, раскроется список наиболее часто используемых функций; если среди них нет функции EНД, нажать на Другие функции

и в окне Мастер функций в категории Проверка свойств и значений вызвать функцию ЕНД;

б) аналогичным образом для EНД вызвать ВПР;

в) в окне ВПР:

Поставить курсор на строку формул после ЕНД, и появится окно функции ЕНД:

г) в окне EНДпосле слова ложьвписать скобку и продолжить набирать <>ИСТИНА, поставить курсор на строку формул после ЕСЛИ,

и появится окно функции ЕСЛИ:

д) в окне ЕСЛИвызвать опять функцию ВПР, в котором:

1 строка – В3;

2 строка - Справочник!$A$3:$C$11;

3 строка – 2.

Здесь четвертая строка (параметр) в окне ВПРнеобязательная; поставить курсор на строку формул после ЕСЛИ, и появится окно функции ЕСЛИ, в котором на 3 строке вписать НЕТ ЗНАЧЕНИЯ,и нажать на ОК.

В строке формул появится формула

«=ЕСЛИ(ЕНД(ВПР(B3;Справочник!$A$3:$C$11;2;ЛОЖЬ))<>ИСТИ НА;ВПР(B3;Справочник!$A$3:$C$11;2);"НЕТ ЗНАЧЕНИЯ")»

5.4. Размножаем получившееся выражение на всю колонку с помощью маркера заполнения.

6. В колонку С введем такую же формулу, только в листе Справочник извлечем значение третьей колонки. После ввода соответствующей формулы на строке формул отразится запись:

«=ЕСЛИ(ЕНД(ВПР(B3;Справочник!$A$3:$C$11;3;ЛОЖЬ))<>ИСТИ НА;ВПР(B3;Справочник!$A$3:$C$11;3);"НЕТ")»

Конечно, встроенная функция ВПР - хорошая функция, но и у

нее есть один недостаток, а именно: она требует, чтобы искомое значение (в СУБД это называется ключом) в области выделенной

БД находилось в первом поле ( для нашего примера код товара находился в колонке А).

Самостоятельная работа № 10. Оборотная ведомость - 2.

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

Подсказка.

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

других листах. Если запись отсутствует, то в качестве третьего параметра формулы ЕСЛИ (в предыдущем примере мы просто печатали «нет значения») надо взять число 0 (ноль). Тогда формулы и ссылки в документе будут примерно следующие:

· Колонка А: = Справочник!А3 (просто ссылка на другой лист и клетку).

· Колонка В: - также со Справочника.

· Колонка С (Остаток на начало) будет примерно следующее выражение:

«=ЕСЛИ(EНД(ВПР(Справочник!А3;Остаток!А3:В20;2;ЛОЖЬ))<>И СТИНА;ВПР(Справочник!А3;Остаток!А3:В20;2);0)»

· Другие колонки аналогично.

Поля базы данных можно задавать и их именами: вместо

А3:В20 вполне можно Код : Остаток.

Итоговая контрольная работа

Используя функцию ВПР(можно без связки ЕСЛИ(ЕНД(ВПР…)), а только ВПР), измените последний вариант зарплаты. Получившуюся программу можете предложить какой-нибудь фирме.

Литература

1. Столяров А., Столярова Е. «Шпаргалка» по Еxcel 7.0 . М., «Вербо»,

2. О.Ефимова, М.Моисеева, Ю.Шафрин. Практикум по компьютерной технологии . ABF , М., 1997

3. А.С.Пушкин. ПСС в 6-ти томах. М., «Художественная литература»,

4.

5. Образцы док ументов в Excel

5.1. Табель учета рабочего времени.

Эта таблица и постановка экономической задачи составлены студентами

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

5.2. Постановка экономической задачи (вариант )

Организационно - экономическая сущность задачи

Пусть требуется произвести расчет заработной платы сотрудников предприятия ООО «Прима». Расчет выполняется ежемесячно в соответствии

с принципами повременной оплаты. По каждому месяцу составляется ведомость начисления заработной платы. Расчеты в ведомостях

осуществляются согласно действующему на 2003 год законодательству.

Задача решается для бухгалтерии и руководства предприятия.

***разрыв страницы***

Выходная информация

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

Задача «Учет труда и заработной платы» имеет несколько выходных документов, однако в приведенном фрагменте реализации задачи мы рассмотрим

только один – «Ведомость начисления заработной платы за март 2003 г.».

Структура выходного документа представлена в табл. 1.

ВЕДОМОСТЬ НАЧИСЛЕНИЯ ЗАРАБОТНОЙ ПЛАТЫ ЗА МАРТ 2003г.
№	Всего начис., р	НДФЛ р.	Проф. взнос, р.	ЕСН, р.	Всего удержано, р.	Долг , р.	К выдаче р.	Фамилия И.О.	Росп ись
	15674,34	2037,66	156,74	5580,07	7774,47		13479,93	Афоничкина Е А
	18099,12	2352,89	180,99	6443,29	8977,16		15565,24	Баклашкин В Н
	5049,56	656,44	50,50	1797,64	2504,58		4342,62	Луценко В А
	15586,73	2026,27	155,87	5548,88	7731,02		13404,59	Панфилов С С
	10561,95	1373,05	105,62	3760,05	5238,73		9083,28	Морозов А В

Табл.1

Выходной документ формируется на основе документов «Лицевая карточка»,

«Таблица настройки», «Отпуска» и «Оборот» в конце каждого месяца, по мере готовности информации для заполнения всех лицевых карточек.