Лабораторная работа №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 – Приходный кассовый ордер