Наименование товара», С- «Остаток на начало»
Колонки Аи Втакие же, как на листе «Справочник», поэтому их скопируем со справочника:
а) щелчок на «Справочник», выделить диапазон А2: В10;
б) через Контекстное менюпункт Копировать;
в) щелчок на лист «С предыдущего решения»;
г) встать на ячейку А2;
д) Контекстное меню => Вставить.
4.3. Колонку С заполняем самостоятельно с клавиатуры.
Рис. 4.3.
5. На листе «Оперативная»вводим данные по движению товаров:
5.1. Строка 1 – «Движение товаров»;
5.2. Строка 2 и ниже (для колонок А и В) заполняются через копирование с листа «Справочник»:
а) Открыть «Справочник»;
б) Выделить А2: В10;
г) Контекстное меню => Копировать;
д) Щелчок «Оперативная»;
е) Встать на А2;
ж) Контекстное меню => Вставить;
.3. Данные колонок С- приходи Д- расходввести с клавиатуры
Рис. 4.4.
6. Оформить выходной документ «Оборотная ведомость»:
1. Строка 1 – «Оборотная ведомость»;
2. Строка 2 - Наименование колонок можно скопировать самостоятельно с других листов;
3. Заполняем Строку 3: 1 Смотри в оба!!!
Столбец А:
3.1. В ячейку А3ввести «=»;
3.2. Щелкнуть по названию листа «Справочник»и в нем на А3. В
строке формул высветится формула «= Справочник! А3»;
3.3. Нажать на Enter;
3.4. Курсор обратно на А3и формулу скопировать на всю колонку.
Столбцы Ви Сформируются аналогичным образом.
Столбец Д:
1. В ячейку D3ввести «=»;
2. Щелкнуть по названию листа «С предыдущего решения»и
в нем на С3. В строке формул высветится формула «= С
предыдущего решения! С3»;
3. Нажать на Enter;
4. Формулу размножить на весь столбец.
Столбцы E и F- аналогично с листа «Оперативная».
Столбец G: «=D3 + Е3 - F3»
Итоговую строкуи форматирование - самостоятельно.
Рис. 4.5.
* Посмотрим, что получилось. «Пройдитесь» по одной из строк,
наблюдая за строкой формулой. У вас в строке формул должны быть
только ссылки на листы или формулы, никаких введенных или
скопированных данных, кроме заголовков. Масло масляным не бывает, оно
по определению масляное. Замените в справочнике первоисточник
тавтологии на «сливочное» и посмотрите по листам, что изменилось: там,
где скопировано – ничего, надо опять перебивать масло, а в итоговом листе
– автоматически исправилось.
* А если представить реальную ситуацию, что была допущена случайная ошибка во время приема товаров (количество указано не верно), и этот показатель учитывается во многих бухгалтерских, финансовых и других расчетных документах, то исправлять ошибку во всей этой цепочке бухгалтерского учета - дело очень трудоемкое. Но: если выходные документы сформированы грамотно, везде установлена связь с входными данными, то нет проблем: меняем только источник ошибок и все остальные исправления происходят автоматически!!!
Вот мы и вышли на простейшее правило формирования
выходныхдокументов:
* Во входныхданных не должно быть расчетных
показателей (формул), а в выходных- наоборот, только ссылки
на первоисточники и формулы.
Разве лишь в «одноразовых» документах возможны единичные отклонения от правил и принципов создания информационных систем.
Примечание:Для нашего примера предполагается, что показатели
по всем товарам располагаются на одинаковых строках на каждом листе. Список и последовательность товаров одинаков на всех листах.
Например, показатели «Сыра голландского» располагаются на втором
месте списка на всех листах. Это единственное дополнительное требование к приведенной технологии. Но и эта задача разрешима, вариант решения задачи, когда в качестве ключа будет не номер строки,
а код товара, будет приведен в разделах 4.6. Концепция серии «от простого к сложному» сохраняется.
Самостоятельная работа №8
«Ай да Пушкин, ай да сукин сын!»
Александр Сергеевич о себе после творческой удачи
h 1. Используя предыдущую информацию, создать отчет о движении товаров с колонками Наименование товара, Ед.изм., Приход, Расход, Отклонение из двух колонок + и – (если Приход больше Расход, то в +, иначе в колонке -) (см. главу 3.2 со встроенной функцией Если)
h 2. Применив указанную технологию, разработайте выходной документ, переделав уже имеющийся документ с самостоятельной №7. Входные данные берутся через ссылки с листов План и Факт. Названия райпо в листе Факт также берутся из Плана. Не забудьте построить и соответствующие диаграммы на выходном листе, используя таблицы с разных листов
. FECI QUOD POTUI, FACIANT MEUORA POTENTESлат. [фэци квод
потуи, фациант мэлиора потэнтэс] — я сделал, что мог, кто может, пусть сделает лучше. В несколько измененном виде этой фразой римские консулы завершали свою отчетную речь, сдавая свои полномочия следующему консулу.
4.2. Расчет заработной платы12
Постановка задачи.
Несколько упрощенный вариант расчета заработной платы демонстрирует другие возможности Excel: работу со склеенными листами, специальную вставку, сложные комбинации встроенных функций, использование абсолютной адресации и т.д.
Налогооблагаемая база и сумма подоходного налога рассчитываются с нарастанием за каждый месяц. Макет (шаблон) ведомости заготавливается сразу на весь год и корректируется за каждый месяц.
Более подробные разъяснения даются по ходу решения задачи.
Порядок действий
1. Создать новый файл: Файл—Создать.
2. Сохранить этот файл с именем «Зарплата_ФИО» на диске
С:\student\ папка (каталог) группы\ФайлJСохранить как.
Примечание: Сохранение еще не законченного документа нужно для
того, чтобы потом не искать место сохранения этого файла и в любое время можно было бы прервать работу нажатием на простую кнопку
«Сохранить».
3. Переименовать первый рабочий лист как «Справочник сотрудников»
и 12 других рабочих листов как названия месяцев (если количество листов не соответствует количеству месяцев, то добавить рабочие листы с помощью контекстного меню или через меню ВставкаÞ Лист). Переименоватьлисты через контекстное меню или просто двойным щелчком по названию листа. После переименования рабочие листы будут иметь вид:
4. а) Налоговая ставка на момент выпуска этого пособия установлена
13% (п.1 статьи 224 Налогового Кодекса РФ);
б) При расчете заработной платы налогоплательщику предоставляются стандартные налоговые вычеты в размере 400 рублей и в размере 300 рублей на каждого ребенка (600 рублей на ребенка-инвалида) у налогоплательщика, на обеспечении которого находится ребенок, и действует до месяца, в котором доход, исчисленный нарастающим итогом с начала налогового периода, превысил 20000 рублей. Начиная с месяца, в котором указанный доход превысил 20000 рублей, налоговый вычет не применяется (п.3),4) статьи 218 Налогового кодекса РФ);
12 Вариант Егоровой Г.Н. Все претензии к ней. Ну, и лавры тоже…
в) На листе «Справочник сотрудников» создать таблицу (рис.4.6):
Рис. 4.6.
5. Склеить(выделить) рабочие листы с названием месяцев. Для этого щелкнуть на листе Январь и при нажатой клавише Shiftщелкнуть на лист Декабрь. После склеивания рабочие листы будут иметь следующий вид:
Все, что мы теперь будем писать на листе Январь, как "под копирку" появится на всех выделенных листах.
6. На листе «Январь» в ячейки А1, А2, А3, Е2 ввести «Налогооблагаемая
база – НОБ», «Профсоюзные взносы – ПФ», «Налог на доходы с физических лиц - НДФЛ». В ячейку А7 ввести «Расчетно-платежная ведомость»,
выделить ячейки А7:J7, объединить и поместить в центре, шрифт и размер в
произвольной форме. Ячейки A8 – J8 оформить как на рис. 4.7.
Рис. 4.7.
7. В ячейки А9 и В9 перенести информацию о табельном номере и фамилии сотрудника, указывая ссылку на данные с листа «Справочник сотрудников»
(п.3.3. Работа с листами, автоформат). С помощью «маркера заполнения»
протащить до 16-й строки (Рис. 4.8.).
Рис. 4.8.
8. Прежде чем начать ввод каких-либо данных, введем все соответствующие формулы для расчета заработной платы. Все формулы создаются для первого человека, а затем копируются для всего списка
сотрудников. Все суммы округляются до двух десятичных разрядов. Во
всех ячейках после ввода формул появится запись «#ДЕЛ/0!»(деление
на ноль). Так и должно быть - входных данных еще нет.
8.1. В ячейку D9 ввести формулу для расчета начисленной суммы
(Оклад / Количество рабочих дней * Отработано дней) –
«='Справочник сотрудников'!C9/$G$2*C9»;
8.2. В ячейку Е9 ввести формулу, с использованием логической функции ЕСЛИ:
а) Выделить ячейку Е9;
б) Включить 
(мастер функций), в окне «Мастер функций»
выбрать категорию Логические, функцию ЕСЛИ, нажать ОК.
В первой строке диалогового окна ЕСЛИ нужно указать проверяемое условие, во второй, что нужно вычислить, если условие верно, в третьей - если не верно.
1 строка: Проверить, что начисленная сумма > 2000013, т.е. D9>20000;
2 строка:Если D9 > 20000, то стандартные налоговые вычеты не
предоставляются, т.е. сумма стандартных налоговых вычетов равна 0;
3 строка:Если D9 <= 20000 стандартные налоговые вычеты предоставляются, т.е. сумма стандартных налоговых вычетов равна (400
+ 300*количество детей).
13 Конечно, значение показателя 20 000 надо бы задавать ссылкой на конкретную ячейку,
изменение значения которой происходит независимо от программы, как в С2-С5 для льгот,
«обозвав» ее как «Граница льгот»
На Рис. 4.9 показано, как выглядит окно логической функции ЕСЛИ после оформления соответствующих строк, а в строке формул отразится формула следующего вида:
«=ЕСЛИ(D9>20000;"0";'Справочник сотрудников'!$C$3+ 'Справочник сотрудников'!$C$4*'Справочник сотрудников'!D9)»
Рис. 4.9.
8.3. В ячейку F9 ввести формулу для определения налогооблагаемой базы (НОБ) – «= Всего начислено – Льготы». Эту формулу отразить в ячейке средствами Excel.
8.4. Определить налог на доходы с физических лиц (НДФЛ) за месяц по формуле «=НОБ * Ставка подоходного налога». Эту формулу отразить в ячейке средствами Excel.
8.5. Определить налог на доходы с физических лиц (НДФЛ) с начала года. В январе месяце «НДФЛ за месяц = НДФЛ с начала года». Эту формулу отразить в ячейке средствами Excel.
8.6. Профсоюзные взносы с сотрудника удерживаются в размере 1%
со всей начисленной суммы: «=Всего начислено * 1% (или 0,01)». Эту формулу отразить в ячейке средствами Excel.
8.7. Сумма к выдаче в J9 = Всего начислено - НДФЛ за месяц – 1%
ПФ. Размножить формулу вниз.
8.8. Выделить ячейки, в которых нужно произвести автосуммирование, и нажать на кнопку на стандартной панели инструментов пиктограммы «Автосумма».
9. Разъединить рабочие листы при помощи контекстного меню пункта
Разгруппировать листы).
10. В ячейку G2 листа Январь ввести число 19 – это количество рабочих дней в январе месяце.
11. После ввода в ячейки С9:С16 количества отработанных дней конкретным сотрудником, лист Январь будет иметь вид Рис. 4.10:
Рис. 4.10.
Расчет заработной платы за январь закончен.
12. Склеить рабочие листы с Февраля по Декабрь.
13. Перейти на Февраль и с учетом предыдущего месяца изменить формулы в ячейках Е9 и G9, а именно в Е9 для логического сравнения в феврале берется совокупный доход НОБ14 с января и «Всего начислено»
за февраль:
13.1. Встать на Е9;
13.2. Щелчок по кнопке =на панели формул и набрать выражения:
«=ЕСЛИ(D9>20000;"0";'Справочник сотрудников'!$C$3 +
'Справочник сотрудников'!$C$4 * 'Справочник сотрудников'!D9)»;
13.3. Ввести знак +, перейти на январь, встать на F9, нажать Enter и
мы снова в февральской ведомости, а в строке формул отразится формула следующего вида:
«=ЕСЛИ(D9+Январь!F9>20000;"0";'Справочник
сотрудников'!$C$3 + 'Справочник сотрудников'!$C$4*
'Справочник сотрудников'!D9)»;
13.4. Встать на G9;
13.5. Перейти в панель формул (щелкнуть мышью после H9)
выражения: «=H9»;
13.5. Ввести знак +, перейти на январь, встать на G9, нажать Enter и
мы снова в февральской ведомости, а в строке формул отразится формула следующего вида: «=H9+Январь!G9»;
14 Маленькая неточность. Совокупный доход не от НОБ, а сумма всех начислений с начала года. Поэтому для реальной зарплаты по месяцам нужна будет накапливаемая колонка. Смотри 2-ой вариант зарплаты. Но за январь и февраль рассчитано все верно,
поэтому выполняйте по книжке, а за другие месяцы можете подкорректировать. Просто бухучет изменился со временем…
14. В ячейку G2 листа Февраль ввести число 20 – это количество рабочих дней в феврале месяце. После ввода в ячейки С9:С16 количества отработанных дней конкретным сотрудникам, лист Февраль будет иметь вид Рис. 4.11:
Рис. 4.11.
15. Разгруппировать листы.
16. Аналогично для всех остальных месяцев расчет сводится к
изменению формул по ячейкам «Льготы» и «НДФЛ с нач.года», вводу количества рабочих дней в месяце и количества отработанных дней в месяце.
Естественно, за все другие месяцы вместо Января будет соответствующий предыдущий месяц. Напоминаю, что «методичка не догма, а руководство к действию»15.
4.3. Печать , представления и отчеты
Подготовка к печати в Excelпроисходит примерно так же, как и в текстовом редакторе. Перед печатью есть возможность предварительного просмотра через меню Файл - предварительный просмотр или пиктограммой 
.Далее устанавливаются режимы печати, колонтитулы и т.д., но есть и дополнительные возможности: хранение нескольких представлений и отчетов одной и той же таблицы
без создания дополнительных файлов. Создаются они при помощи главного меню: Вид - Диспетчер отчетов. Если эти диспетчера
отсутствуют в меню Вид, то их можно доустановить при помощи
Сервис – Надстройки, в окне Надстройки найти надпись Диспетчер отчетов и поставить флажок, затем нажать на ОК.
· Если установить не удается, то перейдите к заданию 4.4.
15См. Максимов Н.А. Полное собрание неопубликованных сочинений, т.2003, стр.11.04
Задание 4.3.1. Печать выплаты заработной платы – ведомость кассира.
Порядок действий.
Загрузить ранее созданную таблицу «Зарплата»(первый вариант)и встать на лист Январь. Такой вид экрана малопригоден для печати. Надо убрать верхние строки:
1.1.Выделить строки с 1по 7;
1.2.Контекстное меню (или Формат\Строка) – Скрыть;
1.3.Выделить колонки С:I;
1.4.Контекстное меню(или Формат\Столбец) – Скрыть.
Для сохранения или распечатки полученной таблицы необходимо выполнить следующие действия
2.1. В ячейку К8 ввести «Подпись о получении», отредактировать как основной документ;
2.2. Нажать на кнопку предварительного просмотра - 
;
2.3. Выбрать вкладку Страница, указать ориентацию Книжная,
установить масштаб - размер таблицы в отчете 120 %;
2.4. Выбрать вкладку Поля и отменить центровку по вертикали, т.е. снять флажок перед строкой Вертикально, изменить поля страницы: левое – 3, правое - 2, нижнее и верхнее – 2,5;
2.5. Для формирования заголовка ведомости и подписи выбрать
вкладку Колонтитулы, нажать на кнопку:
а) Создать верхний колонтитул и в центре ввести «Ведомость выплаты заработной платы за январь». Для изменения шрифта пользоваться кнопкой 
, нажать на ОК.
б) Создать нижний колонтитул и в левом поле указать текущую дату, нажав кнопку 
(&[Дата]), в центре вписать –
«Главный бухгалтер», справа внести линию и нажать на ОК.
в) Нажать на ОК, закрыть окно просмотра.
г) Сохранить вид экрана под именем Отчет 1: ВидÞ
Диспетчер отчетов, в окне «Диспетчер отчетов» нажать на кнопку Добавить, ввести имя отчета Отчет 1, нажать на
Добавить, ОК. После этого окно «Диспетчер отчетов» можно
Закрыть или этот отчет можно отправить на Печать, чтобы получить бумажный вариант отчета.
Восстановить исходный вид экрана следующим образом:
3.1. Выделить всю таблицу, щелкнув по «чистой кнопке» в левом верхнем углу таблицы;
3.2. Формат\Строка – Отобразить;
3.3. Формат\Столбец – Отобразить;
Рис. 4.12
Задание 4.3.2. Сводная ведомость начислений за год.
Порядок действий.
1. Сцепить все листы с Января по Декабрь.
2. В строке 17 объединить ячейки А17:D17 и вписать слово «ИТОГО:» и выровнять по правому краю.
3. Автосуммированием найти итоговую сумму по колонке «Всего начислено».
Выделить на листе Январь клетки А8:D16.
Разгруппировать листы с помощью контекстного меню.