Бланки, документи, технічні специфікації

Можливості Excel з форматування тексту, оформлення таблиць, обчисленню по заданих формулах з успіхом можуть бути використані при підготовці документів різного виду У найпростіших документах використовуються лише основні інструменти форматування, в той час як у більш складних випадках можуть знайти застосування функції пошуку, робота з декількома листами робочої книги, макроси і т д

Багато довідково-інформаційні системи, наприклад «Консультант Плюс» (http://wwwconsultantru/online), дозволяють завантажувати файли бланків документів З успіхом можна використовувати і довідкову систему «Гарант» (http://wwwgarantru), яка також надає вільний доступ в Інтернеті до деякої частини своєї інформації У системі «Гарант» є дві окремі частини: Інтернет-версія «Основні нормативні акти »(http://iv2garantru) та Інтернет-версія« Законодавство Росії »(http://ivgarantru) Звернувшись до цих довідкових систем, можна отримати більшість необхідних документів

Бланки документів часто підготовлені у форматі Excel У довідкових системах подібного роду бланки надаються в чистому вигляді, тобто їх графи не містять розрахункових формул, навіть якщо це прості формули підсумовування Ці бланки призначені для роздруківки або ручного заповнення на компютері і є хорошою заготівлею для подальшої роботи Якщо відомі правила заповнення бланків, то додати в них необхідні формули нескладно

.

Першочергове завдання автоматизації діяльності менеджера в багатьох випадках полягає в полегшенні підготовки та заповнення документів Повний їх список може бути досить великий Очевидно, що всі види документів можуть вимагатися далеко не завжди і не всім користувачам Кожен з них повинен скласти свій власний набір документів У цій главі будуть показані основні прийоми роботи з найбільш часто використовуваними документами

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

Для обліку реалізації затверджено форми документів (ТОРГ-12, рахунок-фактура, інв-17) Деякі документи складаються у вільній формі з дотриманням необхідних реквізитів (рахунок, акт, договір)

Створення та автоматичне заповнення подібних документів в Excel може бути виконано різними способами Розглянемо заповнення бланка документа на прикладі рахунку-фактури

Для обліку рахунків-фактур і автоматичного заповнення їх бланків можна передбачити створення додаткового аркуша Список (містить вихідні дані) і використання функцій ВПР або ГПР для пошуку і вставки даних у відповідні поля бланка Якщо в кожному бланку розташовуються дані тільки по одному найменуванню товару, то такий простий метод цілком допустимо Однак у більшості випадків у рахунках-фактурах доводиться вказувати кілька найменувань товарів, і цей варіант значно ускладнює процес пошуку даних на додатковому аркуші Можливі різні способи вирішення цієї проблеми Можна, наприклад, на аркуші Список (рис 81) Робити записи, відповідні кожному рахунку-фактурі, вказуючи при цьому один і той же номер, але різні товари Очевидно, що такі записи, швидше все, будуть розташовуватися один за одним однією групою, і цей факт можна використовувати при автоматизації заповнення бланка

Перша частина бланка рахунки-фактури (рис 82) містить номер, дату, реквізити продавця і покупця Ця частина заповнюється формулами абсолютно аналогічно розглянутому раніше випадку При цьому для пошуку даних в клітинках аркуша Список можуть використовуватися функції ВПР або ГПР Друга частина містить таблицю з даними товарів, в цьому випадку саме її автоматичне заповнення становить найбільший інтерес, і з цим же повязані основні труднощі

Рис 81 Дані рахунків-фактур

Рис 82 Рахунок-фактура

Будемо припускати, що найменування товарів, відповідні одному счетуфактуре, будуть розташовуватися однією групою в сусідніх рядках листа Список В іншому випадку вся логіка роботи алгоритму пошуку буде змінюватися, і відповідно зміняться формули в осередках бланка Передбачається, що формули в осередках в цьому випадку можуть бути досить складними і довгими, тому можна зберігати деякі проміжні результати у допоміжних осередках Допоміжні вічка не повинні роздруковуватися при виведенні на принтер самого бланка рахунки-фактури, тому їх можна розташувати на аркуші Список або ще одному додатковому аркуші Можна також просто приховати ці осередки або вказати відповідну бланку область друку

Номер рахунку-фактури є основним критерієм для пошуку даних Під допоміжну клітинку O9 занесена звичайна формула = ВПР (B5 Список A2: F9 3 БРЕХНЯ) Таким способом визначається перший найменування товару даного счетафактури В іншій допоміжної комірці O11 знаходиться формула = ПОИСКПОЗ (O9 Список C2: C4 0) За цією формулою визначається позиція знайденого найменування товару (вміст комірки O9) в масиві осередків C2: C4 листа Список (при цьому потрібно точний збіг тексту)

Основна складність полягає в обчисленні даних осередків стовпців таблиці Найменування товару, Одиниця виміру, Кількість і Ціна У першому рядку стовпця Найменування товару розміщена формула = ЕСЛИ ( ІНДЕКС (Спісок $ A $ 2: $ F $ 9

$ O $ 11 1) = $ B $ 5 ІНДЕКС (Спісок $ A $ 2: $ F $ 9 $ O $ 11, 3) )

За цією формулою присвоюється значення ІНДЕКС (Спісок $ A $ 2: $ F $ 9

$ O $ 11 3) (у масиві осередків $ A $ 2: $ F $ 9 листа Список шукається значення комірки

.

$ O $ 11, результат обчислення цієї функції присвоюється з шпальти № 3 знайденої рядка) Цей результат обчислення присвоюється тільки в тому випадку, якщо виконується умова перевірки: вміст стовпця № 1 знайденої рядки має збігатися з вмістом комірки $ B $ 5 (номер рахунка-фактури) В інших випадках повинно присвоюватися значення ” (Порожній рядок) Наступні осередку цієї рядки містять аналогічні формули, тільки результат присвоюється із стовпців № № 4, 5, 6 Далі в осередках цього рядка таблиці рахунки-фактури знаходяться прості формули обчислень підсумкових сум, при цьому перевіряється зміст осередків Кількість і Ціна Якщо комірки порожні, то присвоюється нульове значення Наприклад, для обчислення вартості товару використовується формула

= ЕСЛИ (І (E20 <> “; F20 <> “) E20 * F20 0)

У наступних рядках таблиці містяться аналогічні формули, але для використання наступного найменування товару доводиться робити зрушення за списком на один рядок вниз, тому в наступному рядку стовпця Найменування товару знаходиться формула = ЕСЛИ (ІНДЕКС (Спісок $ A $ 2: $ F $ 9 $ O $ 11 +1 1) = $ B $ 5 ІНДЕКС (Спісок $ A $ 2: $ F $ 9 $ O $ 11 +1 3) “) Аналогічні зміни формул робляться і для інших осередків таблиці

Наведений приклад працює правильно, якщо в рахунках-фактурах зустрічаються тільки різні найменування товарів Якщо є однакові найменування товару в різних рахунках-фактурах, то в цьому випадку зясовується, що даний варіант формул не зовсім точний Наприклад, для рахунки-фактури № 4 в комірці O11 обчислюється значення 1, хоча ця позиція відповідає іншому рахунку-фактурі В результаті дані цього рахунку-фактури друкуються правильно, але не в перший, а в четвертому рядку таблиці (цей номер відповідає зрушенню даних на аркуші Список) Це відбувається тому, що у формулі пошуку в комірці O11 використовувалося значення комірки O9 (найменування товару) Якщо для пошуку використовувати значення комірки B5 (номер рахунка-фактури) і відповідно змінити діапазон комірок на Список A2: A9, то в комірці O11 обчислюється правильне значення 4 Тепер все працює правильно Крім того, в цьому варіанті значення комірки O9 ніде не використовується, і її можна очистити

Осередки, в яких обчислюються величини податків і підсумків, були запрограмовані раніше, тому при введенні конкретного номера рахунку фактури в клітинку B5 відбувається автоматичне заповнення таблиці найменуваннями товарів та обчислення всіх необхідних підсумкових значень

РАДА

Розглянутий приклад припускає, що кількість товарів у кожному з рахунків-фактур не перевищуватиме кількості рядків у таблиці бланка Якщо необхідно складати більш довгі списки товарів в одному рахунку-фактурі, то необхідно збільшити кількість рядків, можливо, перенести їх на другий лист При збільшенні кількості документів потрібно буде розширити діапазон клітинок аркуша Список, в якому відбувається пошук даних (замість

$ A $ 2: $ F $ 9 вказати більшого значення) Зміна у всіх формулах зручно робити операцією Замінити .. (кнопка Знайти і виділити вкладки стрічки Головна) Одночасно з цим буде необхідно змінити діапазон осередків у формулі, що міститься в комірці O11

За аналогією з рахунком-фактурою може бути автоматизоване заповнення бланків рахунків, які використовують практично ті ж самі дані З практичної точки зору, доцільно обєднання цих двох бланків в одній книзі Excel

Прибуткова і витратна накладні

Автоматизація заповнення бланків накладних може бути виконана аналогічними методами Розглянемо варіант заповнення бланка прибуткової накладної

Основні дані можуть бути згруповані на окремому аркуші книги Excel (лист Список, див рис 83) Цей лист містить список товарів, дати і номера накладних, а також інші дані Одному найменуванню товару відповідає один рядок у загальному списку

Рис 83 Дані прибуткових накладних

Бланк накладної знаходиться на аркуші Прибуткова накладна, який показаний на рис 84 Для автоматичного заповнення бланка в його осередку введені формули, що містять функції ВПР і ІНДЕКС Використання функції ЯКЩО дозволяє додатково обробляти обчислені значення осередків

Рис 84 Прибуткова накладна

При заповненні бланка накладної користувачеві необхідно ввести в комірці G3 її номер Вся подальша робота може бути автоматизована За введеному номеру накладної за допомогою формули = ВПР (G3 Список A2: F9 2 БРЕХНЯ) визначається дата накладної (осередок E4) В даному випадку в осередках Список A2: F9 шукається точне значення (на це вказує четвертий аргумент, має значення БРЕХНЯ), що збігається з вмістом комірки G3 У другому стовпці (на це вказує третій аргумент функції) рядки із знайденим значенням знаходиться шукана дата накладної Це значення повертається функцією ВПР

.

Осередки таблиці з найменуваннями товарів і їх даними заповнюються іншими формулами Наприклад, в осередок B9 (найменування товару) введена формула =ЕСЛИ(ИНДЕКС(Список$A

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*