Лабораторная работа №3. Объединение таблиц с автоматизацией расчетов

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

Вначале необходимо оформить счет-фактуру (см. рис.1.14).

Рисунок 1.14 – Структура документа «Счет-фактура»

Таблица 1.8 – Формулы для ввода в документ «Счет-фактура»

Ячейки	Формулы	Область копирования
G1	=СЕГОДНЯ()	-
Е11	=C11*D11	Е12-Е16
Н11	=E11*G11	Н12-Н16
I11	=E11+H11	I12-I16
Е7	=СЕГОДНЯ()	-
I17	=СУММ(I11:I16)	-
C19	=I17	-

Для того, чтобы в таблице не отображались нулевые суммы, можно применить особый формат ячеек. Выделите ячейки Е11-Е16; Н11-Н16; I11-I16 и выберете команду контекстного меню Формат ячеек, и на вкладке Число появившегося диалога выберете в списке форматов все форматы. Введите в поле формата # ##0,00;#;#.Точками с запятой разделяются варианты числа – положительное, отрицательное и нулевое. Символ # указывает, что нули не выводятся. Первые символы описывают ввод числа в виде 7 876,00.

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

=СуммаПрописью(C19)

Данный файл можно скачать из Интернета по адресам:

http://superbook.nm.ru/sumprop.xls

http://super-book.euro.ru/sumprop.xls

После этого, необходимо выполнить действия: Нажать кнопку Office, нажать клавишу Параметры Excel. Выбрать пункт Центр управления безопасностью и нажать клавишу Параметры центра управления безопасностью. Затем перейти на вкладку Параметры макросов и установить флажок на поле Отключить все макросы с уведомлением.

Далее заполните счет-фактуру произвольными данными.

В результате у вас должен получиться примерно такой документ (рис. 1.15)

Рисунок 1.15 – Образец заполненной счета-фактуры

Далее на основании информации из счета-фактуры составим счет. Это сделать очень просто, так как информация в счете почти полностью повторяет информацию из счета-фактуры. Для создания бланка счета рекомендуем создать новый рабочий лист. Образец незаполненного бланка счета представлен на рисунке 1.16.

Рисунок 1.16 – Образец незаполненного бланка «Счет»

После того, как пустой бланк счета вами будет создан, приступайте к его автоматическому заполнению (рис. 1.17).

Рисунок 1.17 - Счет

Для того, чтобы заполнить созданный вами счет данными из счета-фактуры, в ячейки необходимо вставить ссылки на соответствующие ячейки счета-фактуры. Для этого в ячейке A1 поставьте знак =, а затем перейдя на лист со счетом фактурой, нажмите мышкой на ячейку В3. В результате у вас создастся формула =Лист1!B3. После нажатия клавиши ввода, у вас автоматически появится название фирмы-продавца. Все остальные формулы создаются аналогично, и представлены в таблице 1.9.

Таблица 1.9 – Формулы для заполнения документа «Счет»

Ячейки	Формулы	Область копирования
А2	=Лист1!B4	-
Е5	=Лист1!G1	-
С7	=Лист1!H3	-
С9	=Лист1!I6	-
В12	=Лист1!A11	В13-В16
D12	=Лист1!C11	D13-D16
Е12	=Лист1!D11	Е13-Е16
F12	=Лист1!E11	F13-F16
F17	=СУММ(F12:F16)	-
E18	=Лист1!G11	-
F18	=F17*E18	-
F19	=СУММ(F17;F18)	-
A22	=Лист1!C19&" "&Лист1!D19	-
F26	=Лист1!C20	-
F28	=Лист1!I20	-

На третьем рабочем листе создайте бланк приходного кассового ордера. Образец пустого бланка представлен на рисунке 1.18.

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

В приходном кассовом ордере рубли и копейки выводятся в разных ячейках. Если сумма из счета-фактуры расположена на листе Лист1 в ячейке C19, то формула для выделения рублей будет следующей: =ЦЕЛОЕ(ЛИСТ1!C19). Поместите ее в нужную ячейку, например в F18, как в нашем случае. Для выделения копеек в ячейку G18 нужно ввести:

=ТЕКСТ(ОКРУГЛ((Лист1!C19-F18)*100;0);”00”)

В поле основания нужно перечислить все товары, для чего можно применить операцию сцепления, например

=Лист2!В12&” “&Лист2!В13&” “&Лист2!В14

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

Рисунок 1.18 – Пустой бланк «Приходный кассовый ордер»

Рисунок 1.19 – Приходный кассовый ордер

Упражнение №3

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

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

Рисунок 1.20 – Описание продаж и выплат

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

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

Рисунок 1.21 – Расчет авторского вознаграждения

Когда оформление таблицы будет закончено, в ячейку В3 введите фамилию и инициалы одного из авторов.

(Внимание! Фамилия и инициалы должны быть написаны точно так же, как на первом листе рабочей книги).

В ячейку В4 введите сальдо на начало периода.

Таблица 1.10 – Формулы для расчета авторских вознаграждений

Ячейки	Формулы	Область копирования
B5	=СУММ(G10:G12)	-
B6	=СУММЕСЛИ(Операции!$D$5:$D$100; B3;Операции!$E$5:$E$100)	-
B7	=B4+B5-B6	-
F10	=(СУММЕСЛИ(Операции!$A$5:$A$100;A10; Операции!$B$5:$B$100))*C10	F11-F12
G10	=ЕСЛИ(F10<D10;D10;F10)+E10+B10	G11-G12

Ячейки В10:Е12заполняются произвольными данными.