Москва
Горячая линия – Телеком
2020

УДК 004.9
ББК 32.973-018.2
Ч-45
Р е ц е н з е н т : доктор физ.- мат. наук, профессор А. В. Кочергин.

Чернышов Ю. Н.
Ч-45 Офисные приложения на Excel и Visual Basic. Практикум.–
М.: Горячая линия – Телеком, 2020. – 84 с.: ил.
ISBN 978-5-9912-0784-3.

Представлены практические материалы, предназначенные для
более глубокого по сравнению с курсом информатики освоения пакета Microsoft Office, в частности Excel и Word, а также приведены
начальные сведения о применении Visual Basic. Автор не ставил
своей целью дать полное руководство по программам Microsoft
Office и Visual Basic, однако приведенных в книге указаний достаточно, чтобы студенты смогли сделать первые шаги по автоматизации офисных задач и продвижению сайтов. Вторая часть пособия содержит ценные указания студентам бакалавриата и магистратуры, в данном случае МГТУ им. Н.Э. Баумана, по подготовке
выпускной квалификационной работы (ВКР). В частности, рассмотрены типичные ошибки оформления ВКР и показаны способы
их исправления.
Для студентов, преподавателей вузов, а также широгого круга
читателей, интересующихся новыми информационными технологиями в бизнесе.
ББК 32.973-018.2
Учебное издание

Чернышов Юрий Николаевич
Офисные приложения на Excel и Visual Basic
Практикум

Тиражирование книги начато в 2018 г.
Все права защищены.
Любая часть этого издания не может быть воспроизведена в какой бы то ни было форме и
какими бы то ни было средствами без письменного разрешения правообладателя
© ООО «Научно-техническое издательство «Горячая линия – Телеком»
www.techbook.ru
© Ю.Н. Чернышов

Предисловие

В пособии особое внимание уделяется более глубокому по сравнению с курсом информатики освоению пакета Microsoft Office, в частности Excel и Word, а также даны начальные сведения о применении
Visual Basic. В основе пособия лежат задания, которые автор предлагал студентам на лабораторных занятиях по дисциплине «Информационные технологии в экономике» на протяжении 2005–2018 годов.
В разделах компьютерного практикума автор не ставил своей
целью дать полное руководство по программам Microsoft Office и Visual Basic, однако приведенных в книге указаний достаточно, чтобы студенты смогли сделать первые шаги по автоматизации офисных задач
и продвижению сайтов.
Сотрудники различных компаний на основе рассмотренных в пособии базовых программ на Excel и Visual Basic могут функционально
развивать их или решать собственные прикладные задачи, не обращаясь к программистам.
Читатели, у которых нет времени на самостоятельное выполнение заданий предлагаемого компьютерного практикума, могут найти
все приведенные в книге примеры программ на сайте издательства
www.texbook.ru.
Вторая часть пособия содержит ценные указания студентам бакалавриата и магистратуры, в данном случае МГТУ им. Н.Э. Баумана,
по подготовке выпускной квалификационной работы (ВКР). В частности, рассмотрены типичные ошибки оформления ВКР и показаны
способы их исправления.
Во второй части пособия также рассмотрена начальная настройка
редактора Word, которая позволит значительно сократить время на
форматирование документов. Эти рекомендации, несомненно, будут
полезны всем читателям.

    



 

Сумма прописью

Сумма прописью в графе «Всего к оплате» должна появляться
автоматически. Поскольку эта операция часто встречается в большинстве финансовых и бухгалтерских документов, полезно знать, как
она выполняется.
Программу «сумма прописью» удобно разместить на отдельном
одноименном листе, чтобы было проще копировать ее в другие файлы
Excel (см., например, разделы «Оформление счета с помощью Excel»,
«Платежное поручение»). Если сумма прописью нужна дважды, например для общей суммы и НДС, просто скопируйте созданную ниже
программу на еще один лист, изменив всего лишь ячейку связи A1.
Пусть исходная сумма находится в ячейке А1. В сумме необходимо сначала выделить миллионы, тысячи и единицы, затем разбить
соответствующие трехзначные (включая незначащие нули) числа на
отдельные цифры. Для этого введите в ячейки В1 и А2–С4 следующие формулы:
В1: =ПРАВСИМВ("000000000"&ЦЕЛОЕ(А1);9)
А2: =ЗНАЧЕН(ПСТР(B1;1;1))
А3: =ЗНАЧЕН(ПСТР(B1;2;1))
А4: =ЗНАЧЕН(ПСТР(B1;3;1))
В2: =ЗНАЧЕН(ПСТР(B1;4;1))
В3: =ЗНАЧЕН(ПСТР(B1;5;1))
В4: =ЗНАЧЕН(ПСТР(B1;6;1))
С2: =ЗНАЧЕН(ПСТР(B1;7;1))
С3: =ЗНАЧЕН(ПСТР(B1;8;1))
С4: =ЗНАЧЕН(ПСТР(B1;9;1))

5

Сумма прописью

Первая формула добавляет к сумме в рублях девять незначащих
нулей слева и затем выделяет девять символов справа. В формуле из
ячейки А2 удобно преобразовать номер ячейки В1 в абсолютную ад
ресацию ($B$1) по клавише F4 
 и затем скопировать формулу в ячейки B3–С4 и отредактировать. Все эти формулы уже из полученного
текстового значения выделяют поочередно символы и преобразует их
в цифры. Здесь ПРАВСИМВ(str;n) — функция выбора n символов справа в строке str; ПСТР(str;n;m) — функция выбора подстроки длиной
m, начиная с символа с номером n; ЗНАЧЕН(*) — функция преобразования текста в число.
Далее выделим из суммы число копеек, которое принято записывать двумя цифрами: не 0, а 00; не 5, а 05 и т. д. Чтобы добиться
этого, введите следующие формулы:
С1: =ЦЕЛОЕ(А1∗100-ЦЕЛОЕ(А1)∗100+0,5),
D1: =ПРАВСИМВ("0"&ЦЕЛОЕ(C1);2).
Первая формула выделяет число копеек из общей суммы (слагаемое 0,5 необходимо для правильного округления), вторая — добавляет,
если нужно, знак нуля слева.
Следующий шаг — написать словами числа миллионов, тысяч и
рублей. С этой целью наберите указанную ниже таблицу (словарик).
В столбцах E–H учтены грамматические окончания мужского и женского рода, а также единичного и множественного числа.
9
10
11
12
13
14
15
16
17

A
B
C
D
E
F
G
H
сто
десять
одиннадцать один одна миллион тысяча рубль
двести
двадцать
двенадцать два
две
миллиона тысячи рубля
триста
тридцать
тринадцать три
три
миллиона тысячи рубля
четыреста сорок
четырнадцать четыре четыре миллиона тысячи рубля
пятьсот пятьдесят пятнадцать пять пять миллионов тысяч рублей
шестьсот шестьдесят шестнадцать шесть шесть миллионов тысяч рублей
семьсот семьдесят семнадцать семь семь миллионов тысяч рублей
восемьсот восемьдесят восемнадцать восемь восемь миллионов тысяч рублей
девятьсот девяносто девятнадцать девять девять миллионов тысяч рублей

Далее удобно дать имена столбцам словарика. Выделите курсором столбец (например, сотни А9–А17), затем переводите курсор в
левую часть строки формул (под панелью инструментом, там, где вы
видите номера ячеек), нажмите левую кнопку мыши, введите сло
во «сотни» и нажмите Enter 
. Затем повторите эту операцию для
всех столбцов. В приведенных ниже формулах использованы следующие имена: «сотни», «десятки», «надцать» (числа от 11 до 19), «единицым» (единицы мужского рода), «единицыж» (единицы женского
рода), «миллионы», «тысячи», «рубли». Удалить неправильно вве, которое вызывается
денное имя можно с помощью окна
в меню
.
командой

 



6

Компьютерный практикум

Теперь научимся складывать названия чисел из цифр. Для этого
сначала введите в ячейку А5 следующую формулу (набирать в одной
строке!):
=ЕСЛИ(А2=0;"";ИНДЕКС(сотни;А2;1)&"␣")
&ЕСЛИ(И(А3=1;А4<>0);ИНДЕКС(надцать;А4;1);
ЕСЛИ(А3=0;"";ИНДЕКС(десятки;А3;1)&"␣")
&ЕСЛИ(А4=0;"";ИНДЕКС(единицым;А4;1))).
Знак & здесь обозначает конкатенацию, или соединение, символов, функция «И» выполняет проверку совпадения условий, функция ИНДЕКС(array;row;col) выполняет выбор элемента из массива
«array» по номеру строки «row» и номеру столбца «col». Эта сложная
на первый взгляд формула расшифровывается следующим образом:
если число сотен (А2) равно 0, то к строке ничего не добавлять
(""), иначе выбрать в столбце «сотни» слово, соответствующее сотням,
и добавить к нему пробел ("␣");
если число десятков (А3) равно 1 и число единиц (А4) не равно 0, то выбрать в столбце «надцать» слово, соответствующее числу
единиц, иначе:
если число десятков (А3) равно 0, то к строке ничего не добавлять (""), иначе выбрать в столбце «десятки» слово, соответствующее
числу десятков и добавить к нему пробел ("␣");
если число единиц (А4) равно 0, то к строке ничего не добавлять
(""), иначе найти в столбце «единицым» слово, соответствующее числу единиц.
Скопируйте эту формулу (автозаполнением) в ячейки В5 и С5 и
поменяйте в ячейке В5 название столбца единиц на «единицыж».
Затем выберем грамматические формы слов «миллионы», «тысячи» и «рубли». Введите в ячейки А6, В6 и С6 следующие формулы
(удобно формулу из ячейки А6 скопировать в В6 и С6, а затем отредактировать):
А6: =ЕСЛИ(ИЛИ(А3=0;А4=1);F17;ИНДЕКС(миллионы;A4;1)),
В6: =ЕСЛИ(ИЛИ(B3=0;B4=1);G17;ИНДЕКС(тысячи;B4;1)),
С6: =ЕСЛИ(ИЛИ(C3=0;C4=1);H17;ИНДЕКС(рубли;C4;1)).
Здесь «ИЛИ» — функция, получающая значение ИСТИНА, если
одно из условий верно. Смысл формул состоит в следующем: если
в трехзначном числе нет единиц (А3=0) или есть числа от 11 до 19
(А4=1), то грамматическая форма соответствует множественному числу (слова «миллионов», «тысяч», «рублей» берутся из ячеек F17, G17,
H17), иначе необходимо выбрать грамматическую форму слова из соответствующего столбца по числу единиц.

7

Сумма прописью

Теперь все готово для формирования суммы прописью. Это выполняется с помощью формулы в ячейке A7 (вновь одна строка!):
=ЕСЛИ(ДЛСТР(A5)=0;"";A5&"␣"&A6&"␣")
&ЕСЛИ(ДЛСТР(B5)=0;"";B5&"␣"&B6&"␣")
&ЕСЛИ(ДЛСТР(C5)=0;C6;C5&"␣"&C6)&"␣"
Другими словами, проверяем, есть ли разряды миллионов, тысяч
и единиц (длины соответствующих строк не равны нулю), и соединяем
(с помощью конкатенации) все полученные слова в одно предложение.
Принято сумму прописью начинать с прописной буквы. Заменить
первую букву на прописную, а также добавить число копеек можно с
помощью следующей формулы в ячейке А7:
=ПРОПИСН(ЛЕВСИМВ(А7;1))&ПРАВСИМВ(А7;ДЛСТР(А7)-1)&"␣"&
D1&"␣ коп.".
Здесь ПРОПИСН — функция преобразования букв из строчных в
прописные; ЛЕВСИМВ(str;n) — функция выбора n символов слева в
строке str; ДЛСТР(str) — число символов (длина) строки str.
Таким образом, в ячейках A1–H17 теперь находится первая программа на Excel — получения суммы прописью.
Запишем в ячейке С16 бланка простую формулу =B25. Последняя
проблема: сумма может быть слишком длинной и не помещаться по
ширине бланка. Для ее решения выделите диапазон ячеек C15–G16,
«взведите»
нажмите правую кнопку мыши и в окне
и
. Чтобы
два флажка:
длинная сумма была видна полностью, измените ширину строки 15.
Проверьте работу программы, вводя в ячейку А1 различные числа и считывая из ячейки А7 сумму прописью.
Дополнительное задание: уберите, если необходимо, из суммы
прописью копейки.

  

  
 

Оформление счета
с помощью Excel

Табличный процессор Excel из пакета Microsoft Office позволяет
не только проводить различные расчеты, но и разрабатывать достаточно сложные программы для офисных приложений. Разберем одну
из многочисленных задач, необходимых для менеджеров, — выписку
счетов за товары, имеющиеся на складе торговой фирмы, по каталогу [1]. Для решения этой задачи сначала создадим бланк, в котором полностью автоматизирована такая необходимая в любой офисной программе функция, как сумма прописью. Для нее не потребуются средства Visual Basic, а только ряд встроенных функций обработки
текстов, работы с базой данных и некоторые другие функции (см.
раздел «Сумма прописью»). Затем научимся использовать макрорекордер и применим полученные макросы для полной автоматизации
выписки счета путем выбора мышкой товара в каталоге.
Будем предполагать, что цена товара указывается по текущему
курсу доллара с учетом 3 % за конвертацию на ММВБ. Налог на добавленную стоимость — 18 %.
Подготовьте бланк счета за товар по образцу, приведенному на
рис. 1. Рекомендуемый порядок подготовки бланка: выполните форматирование бланка (задайте рамки и размеры полей), введите все
надписи, а затем формулы. При создании рамок рекомендуется вначале обвести тонкой линией поля (графы) бланка, затем жирной линией — заголовки и контур.
В ячейку F1 введите функцию =СЕГОДНЯ(), которая автоматически формирует текущую дату. В меню «Правка» (появляется при нажатии правой кнопки мыши) для этой ячейки выберите пункт
. . . , затем в закладке
измените формат даты так,
чтобы месяц был написан словом.
Желательно, чтобы числа в графах «Сумма, $» и «Сумма, руб.»
появлялись только тогда, когда заполнена соответствующая строка в
графе «Цена, $» (или «Кол-во»). Воспользуемся функцией ЕПУСТО,
которая выдает значение ИСТИНА, если ячейка пуста, или ЛОЖЬ,
если в ней что-то находится. Для того чтобы принять решение —
оставить ячейку пустой или подсчитать сумму и внести ее в эту ячейку, используется функция ЕСЛИ. Таким образом, в ячейку F7 введем

 





Оформление счета с помощью Excel

 

9

Бланк счета на Excel

формулу∗
=ЕСЛИ(ЕПУСТО(E7);"";D7*E7),
а в ячейку G7 — формулу
=ЕСЛИ(ЕПУСТО(E7);"";F7*$C$4*1,03).
Скопируйте эти формулы в ячейки F8–F12 и G8–G12 соответственно. Убедитесь, что суммы появляются только тогда, когда будут
введены количество и цена товара. Обратите внимание на знаки $
в последней формуле, которые показывают, что множитель C4 (курс
доллара) постоянен (имеет абсолютную адресацию), т. е. при копировании не будет преобразован, в отличие, например, от множителя F7,
который при копировании автоматически преобразуется в F8, F9 и т. д.
При вводе последней формулы можно вначале просто указать курсором ячейку C4, а после ввода выделить в формуле символы «C4» и
для изменения адресации с относительной на абсолютную использо
вать F4 
.
Внесите формулы суммирования и расчета налога:
G13: =СУММ(G7:G12),
G14: =G13/118*18.
∗

При вводе формул начинающие пользователи часто заменяют английские буквы (А, В, С и другие) в номерах ячеек русскими буквами.
Cовет: всю формулу, кроме номеров ячеек, набирайте на русском регистре,
а при вводе номера просто показывайте ячейки мышкой! Можно использовать окна с формулами, предлагаемые мастером функций, однако при
этом неудобно составлять вложенные функции. Кстати, если вы копируете
формулы из этой книги, не вводите точки и запятые после формул в Excel,
это всего лишь знаки препинания, а не часть формулы :-)

10

Компьютерный практикум

Числа в последних двух графах бланка должны иметь два знака
после запятой (центы и копейки). Чтобы этого добиться, выделите
диапазон F7–G12 и в меню «Правка» (правая кнопка мыши) выбе. . . . Далее выберите закладку
,
рите команду
введите или выберите стрелочками число 2 в поле
. Нажмите  . Ту же операцию проделайте для ячеек
G13 и G14.
При желании можно автоматизировать нумерацию (непустых)
строк бланка, если в ячейку A7 ввести формулу
=ЕСЛИ(ЕПУСТО(E7);"";СТРОКА()-6),
а затем скопировать ее в ячейки A8–A12.
примените этот бланк к конкретной
фирме, для чего:
1) добавьте в верхней части бланка фирменный знак и полное
наименование фирмы;
2) под счетом оставьте место для подписи директора и бухгалтера;
3) приведите финансовые реквизиты для заполнения платежного
поручения;
4) создайте диалоговое окно для ввода организации-заказчика,
фамилии и телефона (см. раздел «Платежное поручение»).
. Для того чтобы добавить в заголовок счета фирмен
ный знак, необходимо, не выходя из Excel, с помощью клавиш Пуск 

запустить графический редактор Paint из набора стандартных программ, подготовить рисунок и сохранить его в буфере (вырезать).


При возврате в Excel (с помощью клавиш Alt 
+ Tab 
) рисунок можно
.
разместить на нужном месте по команде

  


  

  

   

  

 

Базы данных и каталоги в Excel

Традиционно принято считать, что для разработки баз данных
необходимо использовать специальные программы типа Microsoft
Access. Однако во многих офисных приложениях, особенно там, где
нужны какие-либо расчеты, вполне достаточно использовать соответствующие функции Excel.
База данных в Excel представляет собой несколько подряд идущих столбцов, в первой ячейке которых находятся названия данных,
а последующие строки содержат их значения (записи базы данных).
Базе данных удобно дать имя, например BASE.
Приведем несколько полезных функций для работы с базой данных. В мастере функций, вызываемом по клавише «fx », для баз данных, кроме одноименной категории, можно использовать следующие
функции категории «Ссылки и массивы»:
БИЗВЛЕЧЬ (BASE, поле, критерий) — извлекает запись, удовлетворяющую заданному критерию;
БСЧЁТ (BASE, поле, критерий) — посчитывает число записей в
поле, удовлетворяющих заданному критерию;
ИНДЕКС (BASE, номер строки, номер столбца) — выбирает значения по числовой ссылке;
ВПР (значение, BASE, номер столбца) — извлекает запись из указаного столбца при условии, что в первом столбце базы данных найдено заданное значение (база данных должна быть отсортирована).
Этих функций во многих случаях достаточно, чтобы выбирать
из базы данных нужные значения. Критерием здесь называются две
ячейки, в первой из которой записано название столбца, а во второй
(расположенной ниже первой) — логическое выражение, определяющее собственно критерий.
Если программа на Excel используется не для личных расчетов,
а для применения в повседневной практике менеджером, то базы данных, например товаров, следует каким-то образом выделить и представить их в виде, удобном для применения. В этом разделе рассмотрены два варианта представления базы данных в виде «склада»
и каталога (прайс-листа).
Продолжим работу над офисной программой выписки счета на
товары (услуги), рассмотренной в предыдущем разделе. Сначала пе-

12

Компьютерный практикум

реименуем Лист1 в «Счет», добавим еще четыре листа и дадим им
названия «База данных», «Склад», «Каталог» и «Накладная». Все
это выполняется с помощью отдельного меню, которое вызывается
правой кнопкой мыши, когда курсор показывает на ярлычки листов.
Склад товаров
При работе с программой выписки счета удобно иметь под рукой
сведения о наличии товара на складе и/или каталог товаров с ценами.
Создадим базу данных товаров и научимся вызывать и редактировать
ее, а о том, как переносить данные о товаре в счет, расскажем в следуюшем разделе.
Пусть база данных о 50 товарах размещается в листе «База данных» в ячейках A2–C51, а в первой строке находятся названия «Наименование», «Цена, $», «Остаток». Ширина полей для ввода данных
соответствует ширине столбца А, поэтому лучше сразу увеличить ширину столбца. Будем считать, что эта таблица будет использоваться
как база данных.
Для удобства заполнения базы данных на панель быстрого до
ступа можно добавить кнопку Форма 
:
• щелкните стрелку рядом с панелью быстрого доступа и выберите
пункт «Другие команды»;
• в поле «Выбрать команды из:» выберите пункт «Все команды»;

• в списке выберите кнопку «Форма»
и нажмите Добавить 
.
Наберите 10–15 записей о товарах с использованием этой формы

(рис. 1). В случае затруднений при работе, нажав F1 
, можно получить подробную справку об этом способе ввода данных.

 

Ввод в базу данных

13

Базы данных и каталоги в Excel

 

Элементы управления (слева–
направо): вверху: кнопка; поле со списком; флажок; счетчик; список; переключатель; внизу: группа; подпись; элемент
прокрутки; поле; поле со списком; поле с раскрывающимся списком. Элементы ActiveX будут рассмотрены в разделе
«Проверка знаний...»

Использовать окно, приведенное на рис. 1, не обязательно, можно
просто заполнять базу данных непосредственно на листе Excel.
Для построения «склада» товаров
воспользуемся элементами управления, которые находятся на закладке «Разработчик»∗ (рис. 2).
Нажмите кнопку
 , затем в левой части экрана обведите область для будущего списка товаров так, как показано на рис. 3.
Нажатием на правую кнопку мыши вызовите меню «Правка» и выбе. . . В поле
рите
панели
следует ввести диапазон ячеек
’База данных’!A1:A51 с наименованиями товара. Для этого необходимо поместить в поле ввода курсор, затем кликнуть по ярлыку «База данных» и выделить этот диапазон мышью. (Возможно придется
в сторону.) В посдвинуть окно
— ввести (показать!) ячейку
ле
F1 листа «База данных». После нажатия на
 в списке появятся
наименования товаров, которые можно выбирать курсором (в форме
указательного пальца). При этом в ячейке F1 будет появляться номер
товара в этом списке (убедитесь в этом).
Аналогично создайте списки с ценами и количеством товара на
следует «свяскладе. Каждый раз в панели
зывать» (помещать результат выбора) эти списки с одной и той же
ячейкой F1 для того, что все три списка работали синхронно, показывая одновременно цену и количество.
Для красоты можно сделать фон, дать названия спискам склада.
выберите панель
и найдите кнопку
На закладке

     

!

  
       
"  ! 
  #  ! 
 $  !   %!

"  ! 

 

∗

&!

Эта закладка появляется, если в меню «Параметры Excel» поставить
галочку «Показывать закладку "Разрабочик" на ленте».

14

Компьютерный практикум

 

Пример оформления склада товаров

   . Обведите цветным фоном область склада и поместите полученный прямоугольник на задний план с помощью меню
. Дать названия спискам можно с поправки и команды
мощью кнопки    .
Создайте на листе кнопки    ,    и счетчик товара. Как их использовать, будет рассказано в следующем разделе
«Знакомство с Visual Basic».

 

Каталог товаров (прайс-лист)
Excel обладает многочисленными функциями по обработке таблиц. Одна из функций — группирование данных. С ее помощью
можно создать раскрывающийся каталог товаров, который последовательно показывает вид товара, фирму-изготовителя, марку и т. д. Для
примера выберем электронную технику: телевизоры, видеомагнитофоны и музыкальные центры. Перейдите на лист «Каталог» и запишите в столбце A вид товара, затем фирму и несколько наименований
(марок) телевизоров, снова фирму и т. д. Выделите, как показано на
рис. 4, заголовок и виды товара, например белыми буквами на темном фоне, а названия фирм напишите на каком-нибудь цветном фоне.
Столбец C зарезервируйте под заказ товара, в столбцах D и E введите
соответственно цены в долларах и количество товара на складе.
выберите команду
и затем,
На закладке
кликнув по стрелке рядом с текстом «Структура», откройте окно
, где следует показать, что размещение итогов выполняется

 

%

'! 

(

15

Базы данных и каталоги в Excel

 !

Пример каталога (прайс-листа)

в строках «над данными», т. е. первый флажок должен быть сброшен.
Нажмите  . Затем выделите строки с марками товара (например,
и
телевизоры одной фирмы), снова выберите команду
т. д. Нажмите на появившуюся слева от названия фирмы кнопку − ,
при этом группа товаров будет скрыта. После группировки строк с товаром аналогичным образом сгруппируйте фирмы-изготовители. Открыть каталог для нужного товара или фирмы можно по кнопке " .
На практике каталог будет занимать много места и часто придется
использовать полосу прокрутки для поиска товара, при этом заголовок и кнопки, которые будут созданы позже, будут скрыты. В таком
случае удобно выделить неподвижную область над каталогом с заголовком и названиями позиций.
Наведите курсор на темную отметку в правом верхнем углу полосы прокрутки, где он примет вид . Понятув вниз этот курсор, можно
разбить окно на два подокна, в верхнем останутся текст «Каталог товаров» и заголовок «Заказ, Цена, $, Склад», в нижнем — собственно
выберите команду
каталог товаров. На закладке
, при этом полоса прокрутки будет действовать только для
нижнего подокна.
Формально функции листов «Склад» и «Каталог» совпадают, на
практике менеджер может выбрать удобную для него форму представления данных о товаре∗.

'! 

 

∗



) 

По замыслу автора лист «Склад» служит чисто учебным целям для
демонстрации различных возможностей Excel. Для практических целей
лучше использовать каталог.

16

Компьютерный практикум

(    !  

(  

Создать на листе
бланк накладной можно различными способами. Самый простой —
скопировать полностью чистый бланк счета, отредактировать его и
вписать формулы, которые будут автоматически заполнять бланк накладной при появлении в счете новых данных. Например, формула
=Счет!F3 в ячейке F3 накладной автоматически вставит фамилию в
момент ее ввода в счет. Эту формулу следует скопировать во все
ячейки накладной, однако при этом возникнут некоторые проблемы
с рамками и форматами чисел в ячейках, которые в принципе легко решаются. Вместо подписи директора (менеджера) в накладной
должны быть строки Отпустил, Получил и т. д.

Знакомство с Visual Basic

Пакет Microsoft Office снабжен языком программирования Visual
Basic for Application (VBA), который можно использовать для Excel
[3], Word, Access и других программ. С помощью этого языка в этом
разделе мы научимся создать программы для автоматического выполнения часто используемых операций, например подготовки нового счета, переноса из базы данных в бланк счета наименования и цены товара, изменения количества товара на складе после выписки накладных
и т. п. Более того, для наиболее часто используемых операций можно
создавать собственные кнопки или меню. Все это больше напоминает
традиционный процесс программирования. Приведенные ниже методы программирования на Visual Basic можно также использовать для
подготовки платежных поручений, ведомостей выдачи зарплаты, путевых листов и других деловых приложений.
Язык Visual Basic включает традиционные для других языков
программирования операторы [4], а также объектно-ориентированные
средства. Основные операторы языка:
• операторы определения макроса (процедуры):
Sub Имя макроса Операторы. . . End Sub
• операторы условия:
If Условие Then Оператор Else Оператор
или
If Условие Then
Операторы
Else
Операторы
Endif
• оператор цикла от N до M с шагом K (по умолчанию 1):
For Счетчик = N To M Step K
Операторы
Next Счетчик
Связь модулей Visual Basic с Excel (а также с Word и Access) осуществляется с помощью объектов, методов и свойств.
Объектами в языке являются рабочие книги (WorkBooks), листы (Sheets), диапазоны ячеек (Range или Cells), строки (Rows), столб-

18

Компьютерный практикум

цы (Columns), элементы управления (Bottom, EditBox и пр.) и др.,
т. е. все то, с чем работает пользователь Excel.
После указания объекта через точку должен следовать метод,
т. е. то, что нужно сделать с объектом. Например, для объекта Range
методами являются Select (выделить), Copy (копировать), Paste (вставить), PasteSpecial (специальная вставка), Value (значение), ClearContents (очистить содержимое) и пр., т. е. уже известные нам команды
из меню Excel.
Для некоторых методов, например PasteSpecial, обязательно через точку необходимо указывать их свойства (см. ниже примеры).
Полный список объектов, методов и свойств можно найти в спра
вочной системе Excel. Нажмите F1 
, вызовите справку по Visual Basic
и научитесь ею пользоваться.
Наиболее важными среди объектов являются Range и Cells. И
тот, и другой объект определяют диапазон ячеек. Различие в том,
что параметром Range является имя или диапазон типа "A1:H9", а
параметрами Cells — номер строки и номер столбца, которые, в частности, могут быть заданы переменными.
Объекты могут быть вложенными, например, оператор
Sheets("Счет").Range("B7:G12").Cells(i,1).Paste
вставляет содержимое буфера в пустую строку бланка счета, если i
(=1. . . 6) — номер пустой строки. Здесь вложены три объекта: лист,
диапазон ячеек и ячейка внутри диапазона, начиная с которой выполняется вставка (метод Paste).
Ввод и отладка модулей
Предварительно следует разрешить использование макросов. Это
делается на закладке «Разработчик» командой «Безопасность макросов» → «Включить все макросы». После этого Excel нужно перезапустить.
Программу на языке Visual Basic можно набрать или ввести методом показа с помощью макрорекордера. Для запуска макрорекордера
на закладке «Разработчик» следует выбрать пункт #$  $  $ .
При желании в одноименном окне можно ввести описание макроса
и задать новое имя вместо стандартных имен — Макрос1, Макрос2
и т. д. После показа следует остановить запись с помощью кнопки
 $   %$  , которая появится на месте кнопки «Запись макроса». Чтобы посмотреть и отредактировать модуль, нужно вызвать


редактор Visual Basic с помощью кнопок Alt 
+ F11 
 или кнопки
&$   . Вид окна редактора показан на рис. 1.

19

Знакомство с Visual Basic

 

Окно редактора Visual Basic

Однако таким способом можно создать лишь примитивные программы, кроме того, такие программы часто содержат лишние объекты и методы. Например, при показе копирования данных из ячейки
К1 будут записаны два оператора с методом Select и объектом Selection
(выделение):
Range("K1").Select
Selection.Copy,
которые можно объединить в один —
Range("K1").Copy.

20

Компьютерный практикум

Кроме того, могут появиться операторы типа
ActiveWindow.ScrollColumn = 1,
которые запомнили действия с полосами прокрутки.
Более сложные программы необходимо создавать путем непосредственного ввода с клавиатуры в редакторе Visual Basic (рис. 1). Для
ввода строк языка не обязательно применять верхний регистр. Если
строка введена правильно, редактор автоматически отформатирует
ее, выделит операторы синим цветом, в объектах, методах и свойствах
сделает заглавной первую букву. В случае синтаксической ошибки
будет сразу же выдано предупреждающее сообщение.
В макросах подчеркивание ( ) задает продолжение оператора на
другую строку, апостроф (’) — комментарий.
. Запуск пошагового
Для отладки предусмотрено меню

режима осуществляется по F8 
. Можно задать точки останова и получать контрольные значения переменных и выражений.
В данном учебном пособии не ставится цель изучение всех возможностей языка Visual Basic, а даются простейшие примеры для начинающих. Для более детального знакомства можно рекомендовать
книги [3, 4]. В [5] приводятся многочисленные примеры программ на
Visual Basic для финансовых отчетов и контроля, планирования, инвестиционных решений и других полезных приложений Excel. Книга
[6] знакомит читателей с более сложными элементами Visual Basic для
поддержки принятия решений в бизнесе.
После краткого вступления продолжим работу над программой
выписки счета и накладной, а затем опишем, как сделать платежное поручение, учесть финансовые поступления и проверить знания
сотрудников.

*+,-.

Автоматическое стирание счета
Начнем изучение программирования на языке Visual Basic с модуля очистки полей счета. Excel обладает способностью запоминать все
действия, которые выполняет пользователь, в виде модуля (макроса).
При этом он запоминает и все ошибки, поэтому перед записью макроса важно продумать все действия. В частности, подготовка нового
, очистку полей F3–G4 и B7–
счета включает нажатие на ярлык
E12 и выделение ячейки C3 для последующего ввода наименования
фирмы-заказчика. Выполните указанные выше операции подготовки
нового счета и нажмите на кнопку
 $   %$  . В результате
:
должен появиться модуль

/

0 1

Sub Макрос1()

21

Знакомство с Visual Basic

Sheets("Счет").Select
Range("B7:E12").ClearContents
Range("F3:F4").ClearContents
Range("C3").ClearContents
End Sub
Вы получили первую программу на Visual Basic! Вызовите редактор и найдите текст этого модуля.
Для того чтобы увеличить номер счета на единицу, перед оператором End Sub введите строку
Range("D1").Value = Range("D1").Value + 1
Закройте окно редактора Visual Basic и вернитесь на лист


«Склад». Там уже были заготовлены кнопки Новый счет 
 и Купить 
.
Кликнув правой кнопкой мыши на первой кнопке, в контекстном меню
. Теперь
выберите пункт «Назначить макрос» и укажите на
можно проверить, как работает кнопка.
Вместо кнопки можно с помощью панели «Фигуры» нарисовать
любой рисунок, например, закрашенный белым прямоугольник (чисс помощью меню
тый лист бумаги), затем назначить ему
«Правка». Этот рисунок также будет работать как кнопка.

0 1

0 1

Автоматическое заполнение счета
Создадим более сложную программу для автоматического заполнения счета. Для этого сформируем в ячейках G1–J1 листа «База
данных» строку, которая должна быть записана в счете: G1 — наименование, H1 — пустая (см. бланк счета), I1 — количество и J1 — цена
в $. Поскольку в ячейке F1 у нас есть номер товара, то воспользуемся уже известной функцией ИНДЕКС. Кроме того, как видно из рис. 2
предыдущего раздела, учтем, что количество купленного товара находится в ячейке H10 листа «Склад». Итак, вводим формулы
G1: =ИНДЕКС($A$1:$A$51;F1;1)
I1: =’База данных’!H10
J1: =ИНДЕКС($A$1:$A$51;F1;2)
Возвращаемся на лист «Склад». Вновь запускаем макрорекордер. Показываем, что надо выбрать лист «База данных», копировать
ячейки G1–J1, перейти на лист «Счет» и в первую строку чистого
бланка счета вставить данные. Обратите внимание, что вместо команследует использовать команду
,
ды
, иначе скопируются формулы.
в которой нужно отметить
. В итоге должен
Кроме того, не забудьте вернуться на лист
получиться следующий модуль:

 

) 

/2   
/ 

22

Компьютерный практикум

Sub Макрос2()
Sheets("База данных").Select
Range("G1:M1").Select
Selection.Copy
Sheets("Счет").Select
Range("B7").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= False, Transpose:=False
Sheets("Склад").Select
End Sub
Плохо то, что этот макрос всегда заполняет первую строку бланка. К сожалению, показать макрорекордеру, что нужно перейти к следующей строке, невозможно. Придется исправлять программу вручную. Используем оператор цикла для просмотра строк бланка и функцию IsEmpty для проверки того, что ячейка пуста. При этом вместо объекта Range используем объект Cells, для которого можно использовать индексы. Заодно сократите текст, убрав слова Select →
Selection. Окончательно модуль примет вид:
Sub Макрос2()
Sheets("База данных").Select
Range("J1:M1").Copy
Sheets("Счет").Select
For i = 12 To 7 Step -1
If IsEmpty(Cells(i, 2)) Then j = i
Next i
Cells(j, 2).PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Склад").Select
End Sub
Необходимо заметить, что знание встроенных функций Excel может значительно упростить программы Visual Basic. Например, в
ячейку I1 листа «Счет» введем функцию БСЧЁТА(B6:B12;1;I2:I3),
которая определяет число непустых значений в столбце «Наименование» (В6:В12), рассматриваемого как база данных, по условию
(I2:I3), где I2:I3 — просто пустые ячейки, т. е. безусловно. Тогда
Макрос2 можно записать в виде
Sub Макрос2a()
Sheets("База данных").Select
Range("J1:M1").Copy
Sheets("Счет").Select

23

Знакомство с Visual Basic

Cells(Range("I1").Value, 2).PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Склад").Select
End Sub

0 3

или
Теперь ранее созданной кнопке    назначьте
. Проверьте работу программы, нажав на кнопку    .
Кроме того, научитесь выполнять пошаговую проверку макроса так,
как указано выше.
В окончательном варианте макросов иногда удобно первым вставить оператор Application.ScreenUpdating = False, которая запрещает вывод на экран результатов работы каждой команды макроса.
Но это актуально для сложных макросов. Наши работают так быстро,
что переключение листов Excel практически не заметно.
При работе с листом «Каталог» для автоматического заполнения
счета не требуется формировать какую-либо новую строку. Каждая
строка каталога имеет нужную нам структуру: наименование – пустая строка – количество – цена. Поэтому необходима корректировка
программы Макрос2. А именно: сделайте копию модуля Макрос2, замените в копии имя на Макрос3 и введите изменения:
Sub Макрос3()
Worksheets("Каталог").Activate
n = Selection.Row
With Worksheets("Каталог")
.Range(.Cells(n, 1), .Cells(n, 4)).Copy
End With
Sheets("Счет").Select
Cells(Range("I1").Value, 2).PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Каталог").Select
End Sub

0 3

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

Теперь можно на листе «Каталог» создать кнопки Новый счет 
 и

Купить 
, которым назначить Макрос1 и Макрос3 соответственно.
Теперь заполните бланк счета произвольными данными (курс доллара, заказчик и пр.), вновь вернитесь на лист «Каталог». Для про-

24

Компьютерный практикум

верки работы программы выберите товар, в столбце «Заказ» введите количество и оставьте эту ячейку активной. Нажмите на кнопку
   , затем посмотрите на листе «Счет» результат. Вернитесь на
лист «Каталог», и купите еще что-нибудь. Надеюсь, вы не ошиблись
и все работает.

    
1) введите кнопку Склад 
, которая будет автоматически вызывать
форму 4    для внесения изменений в базу данных;

2) введите ограничение на число строк бланка (не более 6) с выдачей соответствующего предупреждения;
3) уберите лишние (незаполненные) строки из счета и накладной;


4) создайте кнопки Печать счета 
 и Печать накладной 
 и соответствующие макросы.

  

1. Макрос для вызова базы данных имеет следующий вид:
Sub Макрос4()
Application.ScreenUpdating = False
Sheets("База данных").Select
Range("A1").Select
ActiveSheet.ShowDataForm
Sheets("Склад").Select
End Sub
2. Предупреждение о заполнении бланка можно выдать, используя оператор
If Range("I1").Value=12 Then
MsgBox "Бланк заполнен!"
End If
Работа над данной программой также может быть продолжена в
следующих направлениях:
1) изменение количества товара на складе после продажи;
2) создание базы данных о покупателях;
3) учет формы оплаты (наличные или безналичные), в первом
случае выписывается накладная, во втором — товар резервируется
на складе;
4) запись в архив счетов даты продажи и поиск нужного счета
по различным критериям: по имени заказчика, по наименованию товара и т. п.

Платежное поручение

Для работы с банковским счетом существуют два основных способа: электронный и бумажный. В первом случае на сервере фирмы
устанавливают систему «банк-клиент», и сотрудник бухгалтерии, введя пароль, заполняет электронную форму и выполняет необходимые
платежи. Второй способ традиционен — платежки печатают в бухгалтерии, их подписывают у руководителя и главного бухгалтера и относят в банк. Для печати платежных поручений (платежек) обычно
используют «1С-предприятие» или аналогичную систему. Встречаются также фирмы, обычно с небольшим оборотом, или индивидуальные
предприниматели, которые платежки печатают вручную с помощью
шаблона в Word или Excel.
В Интернете несложно найти программу печати платежного поручения на Excel, их довольно много, однако, по мнению автора данной
книги, практически нет простой и удобной программы. В одной из них
каждое поле платежки снабжено выпадающим меню, так что заполнение формы занимает много времени, в другой стандартная функция
COPY заблокирована, и копировать платежки, например, для сохранения и последующей правки невозможно, третья обязательно просит
зарегистрироваться или проголосовать за сайт и т. п. К тому же практически все программы имеют пароль и не дают «заглянуть» внутрь
ее, хотя бы из любопытства, не говоря уже об изменении программы.
А что от такой программы нам требуется? Указать только назначение и сумму платежа. И все — платежка на принтере. Покажем,
как сделать такую программу, используя приемы, уже рассмотренные
в предыдущих разделах «Компьютерного практикума».
Начнем с вертикальной разметки бланка платежки. Для первых
16 столбцов листа Excel зададим ширину∗ , указанную в первой строке примера платежного бланка на рис. 1. Для этого кликнем правой
кнопкой мыши по шапке столбца, в контекстном меню выберем пункт
∗

Отметим, что в Excel-2007 по умолчанию ширина столбца равна 8,43
каких-то единиц, или 64 пиксела, а высота строки равна 15 таких же (!?)
единиц, или 20 пикселей, что совсем не понятно: почему единицы для столбцов и строк разные? Что это за единицы и как их заменить на миллиметры,
известно, видимо, только разработчикам.

26

Компьютерный практикум

 

Бланк платежного поручения на Excel

«Ширина столбца» и введем приведенные числа (эти числа, разумеется, здесь даны только в качестве указаний, их вводить не надо).
Установим размер шрифта 9. Выполним разметку границ бланка
и введем названия полей, как показано на рис. 1. Рекомендую зайти в список параметров Excel и в окне «автозамена» снять флажок
«Делать первые буквы предложений прописными» (см. по этому поводу раздел «Как настроить Word?»). Заполните поля «Плательщик»,
«Банк плательщика», укажите ИНН, КПП и номера счетов своей гипотетической или реальной фирмы. В поле «Дата» введите функцию
=СЕГОДНЯ() для автоматического ввода текущей даты.
Установим высоты строк, которые даны в последнем, 17-м, столб-

27

Платежное поручение

 

База данных получателей (фрагмент)

це Q на рис. 1. Бланк платежки готов. Переименуем этот лист, пусть
это будет лист «платежка».
Можно скачать платежное получение в виде файла Word из системы «Гарант» и перенести эту форму в Excel, однако высоту и ширину
ячеек все равно нужно установить так, как указано выше.
Перейдем к базе данных получателей. На листе 2 (переименуем
его в «базу данных») оставим в качестве рабочей первую строку, в ней
будем хранить сведения о текущем получателе. Во второй строке наберем названия полей нашей базы данных: Назначение, Получатель,
ИНН, КПП, Банк получателя, Счет No, Назначение платежа и т. д.
Заполним хотя бы несколько строк базы данных тестовыми сведениями, например о налоговых платежах, как показано на рис. 2. Ряд
сведений, в частности номера счетов, могут начинаться с нуля, поэтому при записи этих номеров сначала вводите апостроф (’). Обратите
внимание, что значения полей ИНН и КПП начинаются с этих аббревиатур. Этого можно не делать, но тогда в бланке платежки придется
разбить столбцы А и Е на два так, чтобы названия (ИНН и КПП) и
соответствующие числа были в отдельных ячейках (столбцах).
На лист 3 (новое название «сумма прописью») в диапазон А1:I17
скопируйте программу из раздела «Сумма прописью». В ячейку А1
занесите формулу =платежка!К8, а в ячейку В8 бланка платежки введите формулу =’сумма прописью’!В7, которая перенесет сумму прописью из скопированной программы на бланк платежки.
Теперь займемся «малой автоматизацией». Подготовим диалог с
пользователем, в котором достаточно указать список назначений платежей и поле для ввода суммы.
Кликнем по любому ярлыку внизу листа и в контекстном меню
выберем «Вставить...». Появится окно, показанное на рис. 3.

28

Компьютерный практикум

 

Окно вставки нового листа

 !

Диалоговое окно

Выберем окно диалога Excel 5.0 и построим окно для работы с
платежкой по образцу на рис. 4.
Нажимая кнопку «Вставить» на закладке «Разработчик» (см.
рис. 2 в разделе «Базы данных» на стр. 13), последовательно выбираем элементы управления «Группа», «Кнопка», «Поле» и «Поле
со списком», курсором показываем место, где эти элементы должны
быть размещены (см. рис. 4).
Правой кнопкой вызовите контекстное меню поля со списком и
покажите в формате объекта, что формировать список следует по
диапазону ‘база данных’!$A$3:$A$53, а список связан с полем ’база
данных’!$A$1. (Диапазон и поле лучше не набирать с клавиатуры, а
показывать мышкой.) Это означает, что список в диалоге будет содержать краткие назначения платежей из первого столбца базы данных,

Платежное поручение

29

а номер выбранного платежа будет помещен в ячейку А1 листа «база
данных». Как использовать сумму, будет ясно ниже.
Проверить работу окна диалога можно по команде «Отобразить
окно» в панели Элементы управления.
Повторим еще раз, как вызвать это окно на лист «платежка».
Запускаем режим «Запись макроса», имя макроса «Макрос1» можно
не менять. Переходим на лист «Диалог1» и отображаем окно. Всё,
запись можно останавливать. Запускаем режим изменения макроса,
стираем в нем строку Sheets("Диалог1").Select. На листе «платежка» создаем кнопку для запуска этого макроса. Имя кнопки можно
изменить, например, на «Выбор платежа».
Остается завершить нашу программу, чтобы сведения о получателе платежа переносились автоматически в бланк платежки. Поскольку номер получателя формируется в ячейке А1 листа «База данных»,
воспользуемся функцией ИНДЕКС, которая выбирает данные, находящиеся в строке и столбце заданного диапазона. Введем в ячейку В1
строку
=ИНДЕКС($A$3:$H$53;$A$1;СТОЛБЕЦ()),
где $A$3:$H$53 — диапазон базы данных получателей, рассчитанный
на 50 записей, а функция СТОЛБЕЦ(), очевидно, указывает текущий
номер столбца базы данных. С помощью автозаполнения скопируем
эту формулу в первой строке столько раз, сколько записей в нашей
базе данных.
Теперь все сведения о текущем получателе находятся в стандартных местах в первой строке. Введем в соответствующие поля бланка
платежки формулы типа =В1, =С1 и т. д.
Осталось последнее — перенести сумму из формы диалога в платежку. Для этого после строки
DialogSheets("Диалог1").Show
в Макрос1 добавляем строку
Range("сколько") = DialogSheets("Диалог1").
EditBoxes("сумма").Text
в которой «сколько» — это имя ячейки К10, «сумма» — имя поля
ввода в форме диалога. Эти имена можно и не задавать, тогда строка
будет иметь вид
Range("’платежка’!К10") = DialogSheets("Диалог1").
EditBoxes("Поле вводаN").Text
Здесь N — номер поля ввода в форме.
Кроме того, можно добавить еще одну команду:
Range("’платежка’!D6") = Range("’платежка’!D6").Value+1,

30

Компьютерный практикум

которая будет автоматически увеличивать номер платежки. Это,
впрочем, не исключает возможности вводить номер вручную.
В целом программа готова. Осталось проверить, выполнить ряд
форматирований полей платежки, например, объединить ячейки
А18:Н20 с указанием в формате ячейки «переносить по словам» для
того, чтобы наименование плательщика могло размещаться в трех
строках. Какие ячейки платежки форматировать, станет ясно после
выборки через диалог всех тестовых записей базы получателей и проверки, как данные о получателях «ложатся» в нужные поля бланка.
Автор не ставил своей целью полностью описать действующую
версию программы, ограничившись учебными указаниями по ее созданию. Читатели могут скачать программу с сайта издательства
«Горячая линия–Телеком». Несколько замечаний для тех, кто хочет
самостоятельно завершить создание программы.
Полное описание полей платежки приведено в «Положении о правилах осуществления перевода денежных средств» (утв. Банком России 19.06.2012 N 383-П; ред. от 05.07.2017). В соответствии с этим
документом следует расширить базу данных получателей и ввести соответствующие формулы в бланк платежки.
Эта программа удобна для быстрой печати типовых, часто используемых платежек. Как правило, поле «Назначение платежа» в
них стандартно, однако содержит некоторые переменные, например
месяц, квартал или процент от суммы платежа. Это легко учесть,
если в базе данных вводить шаблоны типа
="Текст "&=ФОРМУЛА&" текст "&=ФОРМУЛА()&" текст ". . . ,
в которых выполняется контатенация (знак &) текста с числами, получаемыми по формулам Excel. Обратите внимание, что в текстах,
заключенных в кавычки, необходимо оставлять пробелы до и после
текста.
В платежках, как правило, указывают название месяца и квартал, предшествующие текущим. К сожалению, в Excel нет формул,
возвращающих название месяца и номер квартала. Для этой цели
введем названия месяцев, например, в столбец К листа «сумма прописью», начиная с декабря по ноябрь. Тогда название предыдущего
месяца можно получить по формуле
=ИНДЕКС(К1:К12,МЕСЯЦ(СЕГОДНЯ()).
Для ввода номера прошедшего квартала можно использовать
формулу
=ЕСЛИ(МЕСЯЦ(СЕГОДНЯ())<4;4;ЦЕЛОЕ(МЕСЯЦ(СЕГОДНЯ())/3)-1).
Можно сделать и по-другому. Рядом с названиями месяцев в
столбец L запишем номера кварталов (IV, I, II, III). Тогда номер про-

31

Платежное поручение

шедшего квартала (на этот раз римскими цифрами) определяется по
формуле
=ИНДЕКС(L1:L12,МЕСЯЦ(СЕГОДНЯ()).
Запишем эти формулы в ячейки М1 и М2 листа «сумма прописью» и будем использовать ссылки на них в соответствующих записях базы получателей, например =’сумма прописью’!М1.
Если назначение платежа нестандартное, то удобнее всего перед

нажатием на кнопку Выбор платежа 
 изменить соответствующую запись
в базе получателей. Менять назначение платежа в самой платежке не
следует, поскольку в соответствущей ячейке находится формула и при
сохранении книги Excel программа уже не будет вставлять нужный
тест. В связи с этим стоит защитить от записи все ячейки с формулами
на листе «платежка».
Напомним, что для удобства работы с базами данных можно воспользоваться кнопкой «Форма», которая создает окно с записями базы
получателей. В разделе «Базы данных» (стр. 12) было показано, как
на панель быстрого доступа.
разместить кнопку «Форма»
Чтобы пользоваться этой кнопкой, нужно сначала выделить базу
данных. После нажатия появится окно, показанное на рис. 5.

 '

База данных получателей

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

32

Компьютерный практикум

Чтобы не переделывать программу, после запуска макрорекордера сначала переходим на лист «база данных», копируем диапазон
А2:H52 на новый лист в диапазон A1:H51 и нажимаем кнопку

Форма... 
 в панели быстрого доступа, после внесения изменения копируем базу данных на место. В результате получаем следующий
Макрос2:
Sheets("база данных").Select
Range("A2:H52").Copy
Sheets("Лист1").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.ShowDataForm
Range("A1:H51").Copy
Sheets("база данных").Select
Range("A2").Select
ActiveSheet.Paste
Для запуска этого макроса на листе «платежка» удобно создать

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

Ведение бухгалтерских книг и журналов

Продолжим знакомство с Visual Basic и рассмотрим практический
пример формирования записи в журнале.
Уточним, что мы хотим создать. Не могут же программы типа
«1С-предприятие» предусмотреть все формы журналов, необходимые
на фирме. Поэтому в бухгалтерии подготавливают свои бланки (ведомости) учета различных документов, таких, как расходные и приходные ордера, авансовые отчеты, накладные и т. п., в которых фигурируют в качестве исполнителей одни и те же лица — сотрудники
фирмы. Ксерокопировать и потом заполнять эти бланки вручную накладно, чаще всего их создают в Excel, где благодаря автоматической
подсказке хотя бы не приходится полностью набирать с клавиатуры
текст, уже имеющийся в данном столбце.
Сделаем еще один шаг и создадим две базы данных (наименований документов и фамилий сотрудников), которые, самое главное, будут автоматически пополняться при вводе нового вида документа или
новой фамилии. Бухгалтеру остается только выбирать мышкой документ и фамилию и вводить суммы. По предложенному здесь методу
можно в бланки добавить другие колонки, например с названиями
фирм, номерами договоров и т. п.
Подготовим страницу этого журнала на листе «Бланк». В ячейки
E6 и F6 введите суммы =CУММ(E4:E5) и =CУММ(F4:F5). Добавление
к сумме «лишних» ячеек E4 и F4 необходимо для того, чтобы при
появлении новых строк журнала автоматически корректировалась и
суммы (рис. 1).

 

Пример бланка

В качестве исходных данных введем в лист «Данные» следующие списки:
в ячейки A2:A20 — фамилии (присвоим ему имя «Список1»);

34

Компьютерный практикум

 

Образец диалога для ввода данных

в ячейки B2:B20 — наименования документов, например: Авансовый отчет, Командировка, Приходный ордер и т. п. («Список2»).
Пусть далее в ячейку A1 будет помещаться номер фамилии в
Списке1:
в ячейку B1 — номер документа в Списке2;
в ячейку C1 — признак записи: приход или расход.
В ячейку D1 введем формулу текущей даты =СЕГОДНЯ();
в ячейку E1 — формулу для определения номера пустой записи в
бланке =БСЧЁТ(Бланк!А3:А20;1;Е2:Е3)+5;
в ячейку F1 — формулу для определения номера пустой записи в
списке фамилий =БСЧЁТА(А2:А20;1;F2:F3)+1;
в ячейку G1 — формулу для определения номера пустой записи в
списке операций =БСЧЁТА(В2:В20;1;G2:G3)+1.
Присвоим ячейкам А1–G1 имена «фамилия», «документ», «приход», «дата», «номер», «Nф», «Nд» соответственно. Здесь E2:E3,
F2:F3 и G2:G3 — пустые ячейки, определяющие критерий подсчета
(в данном случае безусловный критерий).
Теперь построим окно диалога для ввода данных в строку журнала. Щелкните правой кнопкой мыши по ярлыкам листов и в появив. . . . В соответствующем окне
шемся меню выберите команду

и нажмите OK 
.
выберите
На экран будет выведен прообраз будущего окна диалога с пользователем. Можно изменить его размеры, «потянув» за рамку окна.
Теперь в диалог можно вводить необходимые элементы управления.
Образец диалога приведен на рис. 2.
Для формирования записи в журнале понадобятся:

 
5  & 678+9 : ;

Ведение бухгалтерских книг и журналов

35

«поле со списком» — для выбора из списка необходимых данных,
например фамилии сотрудника или названия операции, или ввода
данных;
«поле ввода» суммы — то же «поле со списком», в котором список

следует удалить, щелкнув мышью по списку и нажав Del 
;
«флажок» — для выбора одного из двух значений, например приход или расход;
«кнопка» — для указания перенести текущие данные в бланк.
О том, где искать и как использовать указанные выше формы,
рассказано в предыдущем разделе.
Для размещения в главном окне элемента управления щелкните
мышью по нужному элементу в панели инструментов и с помощью
крестообразного курсора укажите место его размещения в окне диалога. Щелкнув правой кнопкой мыши по элементу управления, в меню выберите «Формат объекта. . . » и укажите на закладке «Элемент
управления» тип данных (строка или число), диапазон, по которому
формируется список, и ячейку, куда будет помещаться результат (номер элемента списка). Например, для поля с фамилиями диапазон
A2:A10, ячейка результата А1.
Для описания окон используйте элементы «Надпись». Можно
также ввести название окна диалога. После размещения проверьте
работу окна с помощью кнопки запуска окна диалога (кнопка с тумблером). В окне диалога присвоим имена «кто», «что», «сумма» для
полей ввода (EditBoxes) фамилии, документа и суммы соответственно.
Вызовите редактор Visual Basic и введите следующие два макроса:
Sub Макрос1()
DialogSheets("Диалог1").Show
End Sub
Sub Макрос2()
No = Range("номер").Value
Rows(No).Insert Shift:=xlDown
Cells(No, 1).Value = No - 4
Cells(No, 2).Value = Range("дата").Value
Nam = Range("фамилия").Value
If Nam = 0 Then
Cells(No, 3) = DialogSheets("Диалог1").EditBoxes("кто").Text
Range("Список1").Cells(Range("Nф").Value, 1) =
Cells(No, 3).Value
DialogSheets("Диалог1").EditBoxes("кто").Text = ""
Else

36

Компьютерный практикум

Cells(No, 3) = Range("Список1").Cells(Nam, 1).Value
End If
Doc = Range("документ").Value
If Doc = 0 Then
Cells(No, 4) = DialogSheets("Диалог1").EditBoxes("что").Text
Range("Список2").Cells(Range("Nд").Value, 1) =
Cells(No, 4).Value
DialogSheets("Диалог1").EditBoxes("что").Text = ""
Else
Cells(No, 4) = Range("Список2").Cells(Doc, 1).Value
End If
Sum = DialogSheets("Диалог1").EditBoxes("сумма").Text
If Range("приход").Value = True Then Cells(No, 5) = Sum
Else Cells(No, 6) = Sum
End Sub
Первый макрос просто вызывает окно диалога на лист «Бланк».
Второй макрос требует некоторых комментариев. Вначале переменной No присваивается номер первой пустой строки бланка. Затем
вставляется новая строка, а в пустую строку заносятся порядковый
номер документа и текущая дата. Переменной Nam присваивается
номер фамилии, указанной пользователем в списке. При этом, если
фамилия не выбрана, а введена в это поле, Nam = 0.
Обратите внимание на следующий оператор If, условие в котором
проверяет, были ли введены данные (Nam = 0) или они выбраны из
списка. Для выборки новой фамилии используется объект поле ввода
EditBoxes("кто"). Следующим оператором эта фамилия заносится в
конец Списка1 (подчеркивание — признак продолжения оператора на
следующей строке). Далее поле ввода очищается, чтобы освободить
место для следующей, быть может, тоже новой фамилии. Кроме того,
такая очистка нужна затем, чтобы не повторять запись фамилии в базу данных, если следующая строка бланка будет относиться к этому
же лицу, но с другим документом. (Чтобы понять эту тонкость программы, удалите этот оператор, выйдите из редактора и для проверки

нажмите несколько раз кнопку Запись 
.)
Далее следуюший оператор If выполняет те же действия, но над
названием документа. Затем из поле ввода EditBoxes("сумма") в
бланк заносится сумма Sum с учетом того, приход это или расход.

Создайте на листе «Бланк» кнопку Ввод данных 
 и назначьте ей

Макрос1, кнопке Запись 
 на листе «Диалог1» назначьте Макрос2.
Если сразу программа не заработает или вы захотите научиться
,
отлаживать программы на Visual Basic, перейдите в меню

/

Ведение бухгалтерских книг и журналов

37

0 

в котором выберите пункт
. . . , затем «войдите» (по кнопке
(   ) в Макрос1. Появится редактор Visual Basic, окно которого для удобства, потянув за края, можно уменьшить так, чтобы был
виден лист с бланком. Для выполнения каждого оператора следует

нажимать F8 
, при этом в редакторе желтым цветом будет показываться текущий оператор, в на листе «Бланк» будут видны действия
программы.
После отладки Макроса1 (т. е. после показа окна диалога и ввода
данных) войдите с целью отладки Макрос2 и проверьте, как данные

из окна диалога переносятся в бланк. Кнопка ОК 
 закрывает окно
диалога.
Если эта часть программы понятна, можно продолжить работать
над ней и ввести следующие функции:
1) автоматической вставки шапки бланка при заполнении страницы, например 25–30 строк в зависимости от размера выбранного
шрифта;
2) сортировки сведений по фамилиям или другим данным;
3) печати и сохранения заполненного бланка под новым именем;
4) передачи бланка в Word или Access.
Формирование главной книги из главного журнала
Подготовьте бланк главного журнала по образцу, приведенному
ниже [3]. На основе программы ведения типового журнала, представленной выше, разработайте программу на языке Visual Basic для
внесения записей в главный журнал. После отладки программы заполните главный журнал, например так, как показано на следующей
странице.
Присвойте имена Счет колонке «Номер счета», Дебит — колонке
«Приход», Кредит — колонке «Расход».
Подготовьте страницу главной книги по образцу, приведенному
ниже. Необходимо заполнить графы «Наименование счета» и «Номер
счета», включив в них те счета, которые используются при финансовых операциях.
Присвойте имена Тдата первой ячейке в графе «Дата», Тсчет —
колонке «Номер счета».
В графы «Дебит» и «Кредит» необходимо ввести следующие
формулы:
=СУММ(ЕСЛИ(МЕСЯЦ(Data)=МЕСЯЦ(Тдата);1;0)*
ЕСЛИ(Счет=Тсчет;1;0)*Дебит)
=СУММ(ЕСЛИ(МЕСЯЦ(Data)=МЕСЯЦ(Тдата);1;0)*
ЕСЛИ(Счет=Тсчет;1;0)*Кредит)

38

Компьютерный практикум

)$ *$
№

Дата

Операция

Номер

пп
1
2
3
4
5
6

01.12.05
01.12.05
03.12.05
03.06.96
07.12.05
07.12.05
09.12.05
09.12.05
10.12.05
10.12.05
13.12.05
13.12.05

Офисное оборудование
Счета, подлежащие оплате
Покупка принтера
Аренда офиса
Касса
Ежемесячные платежи
Оплаченные счета
Продажа
Продажа товара
Расходы на телефон
Счета, подлежащие оплате
Счет за телефонные переговоры
Зарплата
Касса
Премия Юле и Яне
Подписка
Касса
Журнал «Курьер» за 3 месяца

Сумма, $

счета

Приход

22
30

686

21
1

1187

40
60

716

23
30

86

50
1

3457

29
1

10

Всего

Расход
686
1187
716
86
3457
10

6142

6142

)$$ $
Дата

Наименование счета

12.05

Касса
Аренда офиса
Офисное оборудование
Расходы на телефон
Подписка
Счета, подлежащие оплате
Оплаченные счета
Зарплата
Продажа
Баланс

Номер счета
1
21
22
23
29
30
40
50
60

Дебит

Кредит
4654

1187
686
86
10
772
716
3457
716
6142

6142

Эти формулы автоматически проверяют все записи в главном
журнале и в случае совпадения номера месяца в колонке «Дата» с
текущей датой в главной книге и номера счета в колонке «Счет» с
текущим номером счета в главной книге выполняют суммирование
соответственно в графах «Дебит» и «Кредит». Подобные формулы
называются формулами массива. Для того чтобы они работали,

вместо клавиши Enter 
 необходимо одновременно нажать клавиши



Ctrl 
+ Shift 
+ Enter 
!

Управление запасами и прогнозы продаж

Управление складскими и производственными запасами возникает практически на всех фирмах, как производственных, так и торговых. Разумеется, для этой цели можно купить готовые программы типа «1С-Логистика: Управление складом» за 20 тысяч рублей.
Однако многие функции, предлагаемые этими программами, например обработка штрих-кодов товаров, поиск оптимального пути автопогрузчика по территории склада и другие, часто совершенно излишни.
Главная задача многих таких программ — подробный документооборот, совмещенный с другими программами управления предприятиями, например «1С-Бухгалтерией». С другой стороны, эти программы
не отслеживают динамику продаж или расхода материалов со склада
и не дают рекомендации по поставкам.
Многие небольшие фирмы, проведя анализ существующих программных средств, приходят к выводу о необходимости автоматизировать процесс управления запасами своими силами и по собственным
упрощенным алгоритмам.
Поскольку данные о запасах на складе по существу представляют
собой таблицу, часто обращаются к Excel. Сотрудник фирмы создает
на листе Excel таблицу с колонками «наименование», «запас», «приход» и «расход», пытается ввести очевидную формулу
Запас = запас + приход - расход,
но сразу обнаруживает предупреждающее сообщение о том, Excel не
может обработать формулы с циклическими ссылками. Нередко это
ставит сотрудника в тупик, и он вынужденно возвращается на рынок
специализированных программ управления складом.
Тем не менее Excel совсем несложно использовать не только для
простого учета количества товаров или материалов на складе, но и
для оптимального управления складскими запасами на основе прогнозирования. Рассмотрим, как это делается.
Учет поступления и расхода товаров
Создадим на листе Excel таблицу данных, содержащую столбцы
«Наименование», «Фирма», «Категория», «Цена, $» (или в рублях),
«Запас», «Приход», «Расход», «Поставка» (рис. 1). При этом удобно ввести эти имена во второй строке, оставив первую строку для

40

Компьютерный практикум

 

Лист «Склад»

служебных целей. Первые четыре столбца — информационные, они
необходимы для быстрого поиска нужной позиции. Для этого удобно использовать функцию Excel «Автофильтр» (в меню «Данные» –
«Фильтр»). Справа от введенных имен столбцов появится маленькая
кнопка со стрелкой, нажав на которую, можно вызвать контекстное
меню с критериями фильтрации данных. Пример такого меню виден
на рис. 1.
Столбцы «Запас», «Приход», «Расход», как следует из названий,
будут использоваться для учета складских запасов. В столбце «Поставка» будут даваться рекомендации по пополнению запасов, а «Неликвиды» покажут товар, который давно не продавался и, следовательно, который необходимо выставить на распродажу.
Примем следующие допущения:
1) учет поступления на склад происходит по мере необходимости
(в тот же день или позже);
2) данные по расходу (продажам) вводятся ежедневно, например,
в конце рабочего дня, это допущение будет необходимо далее для прогнозирования закупок;
3) приход и расход при необходимости можно совмещать;
4) отрицательные значения в столбце «Запас» разрешены и указывают отсроченную поставку (товар оплачен и должен быть получен
покупателем, например, в течение трех дней).
Для того чтобы избежать указанной выше циклической ссылки
в формуле учета, следует придерживаться следующей последовательности действий:
1) ввод данных о приходе и расходе товара;
2) расчет новых значений складских запасов;
3) обновление данных в столбце «Запас».

Управление запасами и прогнозы продаж

41

Первое действие очевидно. Сотрудник переносит числовые данные из накладных или других документов в столбцы «Приход», «Расход». Для расчета новых значений запасов во второй строке любого
свободного столбца, например J, введем формулу
=E3+F3-G3,
которую затем «размножим» функцией «Автозаполнение» на весь
столбец (или из расчета максимального количества наименований товаров).
Далее, конечно, можно каждый раз вручную копировать значения из столбца J в столбец Е (не забыть, что нужно воспользоваться
специальной вставкой с указанием копировать только значения и затем стереть данные прихода и расхода), но лучше однажды записать
соответствующий макрос и присвоить его кнопке, установленной на
листе с помощью панели инструментов «Формы» (см. рис. 1). Этой
кнопке дайте имя, например «Ввод закончен».
Макрос1 после исключения очевидных повторов Select...Selection имеет вид
Sub Макрос1()
Columns("J:J").Copy
Columns("E:E").PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("F3:G36").ClearContents
End Sub
Простейшая программа складского учета готова.
Прогнозирование продаж и рекомендации по поставкам
Расширим функции программы учета. Для этого введем средства
хранения предыстории продаж, например, за месяц (30 рабочих дней)
и попробуем предсказать, сколько товара каждого наименования будет
продано, скажем, на следующей неделе.
Добавим к нашей программе новый лист «История», имеющий
вид, показанный на рис. 2. На этом листе повторены столбцы «Наименование», «Запас», «Цена» и «Расход». Первые два столбца просто повторяют аналогичные столбцы листа «Склад» (в них записаны
формулы типа =Склад!A3). Столбец «Цена» может включать оптовую
цену товара в отличие от отпускной цены на листе «Склад». Столбец «Расход» также включает формулы вида =Склад!G5 и необходим
для того, чтобы превратить пустые ячейки из аналогичной колонки на
листе «Склад» в числовые ячейки с нулями (что нужно для правильной работы функции прогнозирования). На рисунке для простоты
столбцы H–AI (средние столбцы массива продаж) и AM–AP (средние

42

Компьютерный практикум

 

Лист «История»

столбцы прогноза) скрыты; в реальной книге Excel этого делать не
следует.
Для прогноза используем функцию Excel
=(ПРЕДСКАЗ(x нов; х; y),
где х нов — значение аргумента, для которого выполняется предсказание, x и y — соответствующие данные предыстории. В данном случае
x — номера дней, предшествующих текущему, y — расход (продажи)
на каждый день. Для правильного применения этой функции обратим
внимание на следующую ее особенность: пустые ячейки в массивах x
и y для расчета линейной регрессии (предсказания) не используются.
В качестве массива x используем диапазон из 32 ячеек, первая и последняя из которых пустая, а в остальных записаны числа от 1 до 30.
Разместим этот массив, например, в диапазоне AU1:BZ1. В ячейках
AL–AR запишем числа 31–37 — номера дней, для которых делается
предсказания. Для предыстории продаж также добавим в начале и
конце массива y пустые ячейки (столбцы F и AK на рис. 2). Такая
организация предыстории продаж очень важна и ниже будут даны
дополнительные пояснения.
В ячейку AL3 массива «Прогноз» запишем формулу
=ОКРУГЛ(ПРЕДСКАЗ(AL$2;$F3:$AK3;$AU$1:$BZ$1);0),
где ОКРУГЛ — функция округления до целого числа предсказанного
значения; AL$2 — ссылка на ячейку с числом 31 (следующий день после 30-дневной истории); $F3:$AK3 — массив продаж; $AU$1:$BZ$1 —
диапазон из 32 ячеек с числами от 1 до 30 (см. выше). Скопируем
эту формулу методом автозаполнения во все ячейки массива «Прогноз». Напомним, что сначала необходимо заполнить этой формулой
диапазон АL3:AR3, а затем, выделив этот диапазон мышкой, продолжить автозаполнение всех строк массива. Теперь ясно, что знаки $
в данной формуле расставлены так, чтобы при автозаполнении эта
формула была правильно преобразована.

Управление запасами и прогнозы продаж

43

Вернемся на лист «Склад». Для формирования предыстории и
прогноза воспользуемся следующей последовательностью операций,
которые необходимо с помощью макрорекордера записать в виде макроса Visual Basic:
1) перейти на лист «История»;
2) выбрать столбец АК и добавить ячейки (вставить новый столбец);
3) выбрать столбец D («Расход») и скопировать его;
4) выбрать столбец АК и с помощью специальной вставки записать данные расхода;
5) удалить столбец G.
В результате этих операций вся предыстория продаж циклически
сдвинется на один день.
Теперь поясним, зачем в массив предыстории вставлены пустые
столбцы. При удалении последнего столбца этого массива Excel автоматически исправил бы формулу предсказания, а при добавлении
нового столбца в эту формулу не были бы внесены исправления. И то
и другое привело бы к ошибке предсказания. Поскольку мы работаем
со вторым и предпоследним столбцами массива, формула предсказания не меняется. Впрочем, при записи макроса временно все формулы =ПРЕДСКАЗ будут давать ошибку #НД, которая исчезнет после
последней операции (удаления столбца G).
Запись этих операций с помощью макрорекордера и удаление пар
Select–Selection из Макроса2 (что можно и не делать) дают следующие
операции:
Sheets("История").Select
Columns("AK:AK").Insert Shift:=xlToRight
Columns("D:D").Copy
Columns("AK:AK").PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Columns("G:G").Delete Shift:=xlToLeft
Объединим два макроса, вставив эти операции в Макрос1 перед
последним оператором стирания. Проверьте работу Макрос1 путем
ввода данных о расходе и нажатия на кнопку «Ввод закончен», лучше
это сделать 30 раз, модулируя работу склада в течение месяца. В
результате для каждого товара будет построена линейная регрессия
и предсказан объем продаж (расхода) на каждый из семи следующих
рабочих дней.
Для просмотра товаров, для которых рекомендуется поставка,
можно использовать автофильтр (рис. 3) и отобразить строки с положительной поставкой, т. е. выбрать позицию «не содержит» с пустой

44

Компьютерный практикум

 

Автофильтрация по данным поставки

 !

Пользовательский автофильтр

правым окошком (на рис. 4 показано окно автофильтра и результат,

полученный после нажатия ОК 
).
Чтобы каждый раз не задавать фильтр, удобно создать новый
лист «Поставка» и макрорекордером создать новый макрос. Последовательность записи макроса следующая:
1) переходим на лист «Поставка», выделяем его (мышкой на перечении названий строк и столцов) и удаляем содержимое;
2) копируем столбец А с листа «Склад» на лист «Поставка»;
3) копируем столбцы H и I с листа «Склад» и делаем специальную
вставку только значений в столбцы В и С листа «Поставка»;
4) выделяем ячейки В2–С2 и задаем для них фильтр;
5) кликаем по кнопке фильтра в ячейке В2 и задаем текстовый
фильтр «не содержит», как показано выше.
Закрываем запись макроса. Должно получиться следующее:

Управление запасами и прогнозы продаж

45

Sub Макрос2()
Sheets("Поставка").Select
Cells.Select
Selection.Delete Shift:=xlUp
Sheets("Склад").Select
Columns("A:A").Select
Selection.Copy
Sheets("Поставка").Select
Columns("A:A").Select
ActiveSheet.Paste
Sheets("Склад").Select
Columns("H:I").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Поставка").Select
Columns("B:C").Select
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks :=False,
Transpose:=False, Range("B2:C2").Select
Application.CutCopyMode = False
Selection.AutoFilter
ActiveSheet.Range("B2:C23").AutoFilter Field:=1,
Criteria1:=<>**, Operator:=xlAnd
End Sub

Создаем на листе «Склад» кнопку Поставка 
 и назначаем ей
.
Как воспользоваться этими данными? Ясно, что для успешной
работы фирмы на складе желательно иметь то количество товара, которое равно сумме семи предсказанных значений. Однако тренд (линия регрессия) может иметь отрицательный наклон, который означает, что спрос на товар падает. В этом случае предсказанные значения
продаж могут иметь отрицательные значения. Очевидно, их принимать во внимание не следует. Для суммирования только положительных значений в Excel можно воспользоваться функцией, которая при
записи, например, в ячейку AS3 будет иметь следующий вид:
=СУММЕСЛИ(AL3:AR3;”>0”;AL3:AR3),
где AL3:AR3 — диапазон суммируемых значений (диапазон записан
дважды, поскольку числа, для которых проверяется условие, и суммируемые числа могут не совпадать); ”>0” – условие суммирования.
Очевидно, что теперь необходимо для поставки выделить те товары, запас которых на складе меньше предсказанного объема продаж.

0 3

46

Компьютерный практикум

Это условие реализуется формулами вида =B3-AS3 в столбце «Остаток», а также формулами вида
=ЕСЛИ(История!AT3<0;-История!AT3;"")
в столбце «Поставка» на листе «Склад». Последняя формула отображает только те значения остатков, которые меньше нуля, меняя,
разумеется, знак «–» на «+».
Если известен примерный объем продаж за неделю, то с помощью
формулы вида
=ЕСЛИ(И(История!AT3>0;История!AS3*8<История!AT3);
История!B3;"")
можно проверить, будет ли данный товар на складе продан, скажем,
за два месяца (8 недель), и на листе «Склад» ввести столбец «Неликвиды», в котором будут показаны неликвидный товар. На основании
этих данных менеджер может снизить цены или объявить распродажу.
Как видно из рисунков, лист «История» необходим для промежуточных расчетов, рекомендуемый объем поставок отображается сразу
после ввода данных о расходе (продажах) на листе «Склад».
Для удобства пользования программой удобно перед предсказанием, т. е. перед обработкой данных расхода, проверить, а введены ли
данные расхода, быть может, выполняется только обработка прихода.
Для этого просуммируем числа в столбце «Расход» на листе «Склад»
(эта сумма для удобства помещена в ячейку G1 под кнопкой) и вручную в макрос перед оператором перехода на лист «История» вставим
оператор
If Range ("G1").Value = 0 Goto 1,
пометив меткой 1 последний оператор стирания.
Ежедневное или частое применение программы только для учета
прихода на склад без данных расхода (без указанного выше оператора
if) искусственно увеличит число дней предыстории, а предсказанные
данные поставок будут несколько занижены. В целом это хорошо.
Например, пусть некий товар хорошо продавался в первые недели месяца, а затем по какой-либо причине спрос на него резко упал. Линия
тренда будет иметь отрицательный наклон, и приведенная выше методика даст пусть небольшой, но положительный прогноз на начало следующей недели и отрицательный к концу недели. Суммарный объем
новых поставок будет положительным. А правильно ли это? Решение
в данном случае за менеджером, при этом ему полезно обратиться за
справкой о продажах на лист «История».

Проверка знаний студентов или сотрудников

Тестирование с помощью компьютера — традиционный способ
проверка знаний студентов во время сессии или на этапе контроля
успеваемости. Типичная процедура проверки заключается в выводе
на дисплей вопросов по заданной тематике вместе с несколькими ответами (обычно не более трех), один из которых верен, а остальные
нет, но похожи на верные. Аналогичная задача решается на многих
фирмах при приеме на работу или аттестации сотрудников. Составить
такой набор вопросов с ответами для опытного преподавателя (руководителя фирмы или начальника отдела кадров) не составляет труда,
однако для большинства из них, особенно не знакомых с программированием, достаточно сложно написать программу тестирования.
Покажем, как с помощью Excel и Visual Basic создать универсальную программу, которая не зависит от тематики и содержимого
вопросника. Это достаточно просто, если ознакомиться с таким элементом редактора Visual Basic, как пользовательская форма.


После вызова редактора Visual Basic ( Alt 
 + F11 
) в меню Insert
(«Вставка») следует выбрать пункт UserForm (пользовательская форма). Одновременно появляется панель элементов. Выбрав мышкой
нужный элемент управления, например текст, его можно перенести
в форму (рис. 1).
По внешнему виду и набору инструментов пользовательская
форма практически совпадает с окном диалога (см. раздел «Знакомство с Visual Basic» и другие), однако по методу применения различие
между ними более существенно. В частности, окно диалога является
статическим, и изменение его внешнего вида невозможно. С другой
стороны, для каждого элемента пользовательской формы редактор
отображает множество свойств (слева на рис. 1), которые можно редактировать как вручную, так и с помощью операторов Visual Basic.
В нашем случае (как, впрочем, и в большинстве приложений) нас
будут интересовать всего два свойства — имя элемента Name и его назначение Caption (показано стрелкой). Имя элемента в принципе можно не менять, а просто использовать то, которое дается по умолчанию:
Label1 — для надписи, TextBox1 — для поля ввода, OptionButton1 —
для переключателя и т. д. (цифра 1 для последующих элементов заменяется на 2, 3 и т. д.). Но из соображений читаемости будущей про-

48

 

Компьютерный практикум

Пользовательская форма в редакторе Visual Basic с заготовкой для окна
диалога

 

Вариант подготовки вопросов и ответов

граммы на Visual Basic удобно переименовать элемент, для чего новое
имя просто вводится в верхнюю строку панели свойств вместо имени
по умолчанию. Текст, указывающий назначение элемента, можно изменять как на самой форме, так и в панели свойств в строке Caption.
Для программы тестирования необходима пользовательская форма с четырьмя элементами: надписью Label1, которая будет в дальнейшем заменена на текущий вопрос, и тремя переключателями OptionButton, которые будут соответствовать трем возможным ответам

Проверка знаний студентов или сотрудников

49

(см. рис. 1). Первому элементу дадим имя question, переключателям — имена ans1, ans2 и ans3 соответственно. Одновременно присвоим пользовательской форме новое имя Test, которое заменит имя по
умолчанию UsetForm1.
Подготовим файл с вопросами и ответами. Этот файл можно
набрать в Word и затем скопировать в столбец В листа Excel так,
как показано на рис. 3. Столбец А используем для указания номера правильного ответа. Для удобства присвоим столбцам имена: ans
(ответы) — столбцу А и ask (вопросы) — столбцу В.
Теперь все готово для программирования на Visual Basic. Заменить текст элемента пользовательской формы, например, с вопросом,
на текст из листа Excel можно оператором
Test.question.Caption = Range("В1").Value.
Аналогичные операторы позволяют перенести на форму варианты ответов, только вместо имени question будут стоять имена ans1,
ans2 и ans3, а вместо номера ячейки В1 — номера В2, В3 и В4 соответственно. Вслед за этими операторами необходимо записать оператор
отображения формы Test.Show.
Указанные пять операторов выводят на экран форму с первым вопросом. Однако нам необходимо, во-первых, организовать цикл для
вывода последовательности вопросов с проверкой на конец теста и,
во-вторых, проверять правильность ответа и подсчитывать число правильных ответов. Для первой цели введем счетчик i, а для перебора
вопросов будем использовать оператор вида
Test.question.Caption = Range("ask").Rows(i).Value,
в котором объект Rows(i) позволяет последовательно выбирать строки из столбца ask (столбца В). Для проверки конца тестирования используем оператор
If Range("ask").Rows(i).Value <> "" Then GoTo 1,
где 1 – метка начала блока формирования формы.
Для сравнения номера ответа, выбранного переключателем, с номером правильного ответа, заданным в столбце ans (столбце А), воспользуемся тем, что пользовательская форма автоматически формирует макросы при выборе элемента формы. А именно, если вернуться
к окну редактора с пользовательской формой и кликнуть дважды на
переключателе, например ans1, то автоматически сформируется новый макрос вида
Private Sub ans1 Click()
End Sub

50

Компьютерный практикум

В тело этого макроса добавим оператор no = 1, который переменной no присвоит номер переключателя. Аналогично поступим для
других переключателей. Для того чтобы эта переменная была доступна основной программе, присвоим ей статус общедоступной:
Public no As Integer.
Проверку правильности выбора ответа выполним оператором
If no = Range("ans").Rows(i).Value Then Sum = Sum + 1.
Последний оператор
MsgBox ("Число правильных ответов: " & Sum)
позволяет выдать на экран типовую форму с числом правильных ответов.
Окончательно вся программа тестирования вместе с данными операторами имеет следующий вид:
Public no As Integer
Sub Макрос1()
Sum = 0
i = 1
1:
form.question.Caption = Range("ask").Rows(i).Value
form.ans1.Caption = Range("ask").Rows(i + 1).Value
form.ans2.Caption = Range("ask").Rows(i + 2).Value
form.ans3.Caption = Range("ask").Rows(i + 3).Value
form.Show
If no = Range("ans").Rows(i).Value Then Sum = Sum + 1
i = i + 4
If Range("ask").Rows(i).Value <> "" Then GoTo 1
MsgBox ("Число правильных ответов: " & Sum)
End Sub
Private Sub ans1 Click()
no = 1
End Sub
Private Sub ans2 Click()
no = 2
End Sub
Private Sub ans3 Click()
no = 3
End Sub
Остается подготовить внешнее оформление программы тестирования. Для этого на листе Excel создадим кнопку с надписью «Начать
тестирование», скроем вопросы и ответы, установив для строк А и В

Проверка знаний студентов или сотрудников

51

нулевую ширину (это можно сделать мышкой). Кроме того, исполь
зуя кнопку Прямоугольник 
 в меню «Рисование», можно сделать цветной фон на этом листе. Для того чтобы тестируемый не смог убрать
фон и вскрыть колонки с ответами, защитим этот лист паролем.
Результаты тестирования можно обрабатывать различными способами. Часто достаточно просто узнать число правильных ответов,
чтобы дать оценку знаний тестируемого. Несложно также перевести
это число в пятибальную шкалу оценок и выдать оценку с помощью
оператора MsgBox.

Конкурентная разведка в Интернете

Эффективность продвижения на рынок новых продуктов и услуг,
расширения производства, увеличения объема продаж и других проектов, очевидно, в значительной степени зависит от имеющейся на
фирме информации о рынке, конкурентах, предпочтениях потребителей и т. п. Информация может быть получена в результате коммерческой (корпоративной, конкурентной) разведки.
Под системой корпоративной разведки понимается организационная структура, занимающаяся вопросами добывания, сбора, проверки
(верификации), обработки и анализа данных по различным аспектам
экономической деятельности предприятия, его партнеров с дальнейшим использованием полученной информации для решения конкретных задач его хозяйственной деятельности [1].
Под конкурентной разведкой следует понимать деятельность отдельных лиц и сообществ, направленную на поиск, анализ, систематизацию и оценку информации, представляющей бизнес-интерес в условиях рыночной конкуренции.
По неофициальным оценкам сотрудников ФСБ России, практически каждое крупное отечественное предприятие крадет информацию у своих конкурентов и одновременно страдает от аналогичных
действий с их стороны [1].
Не будем останавливаться на методах и источниках коммерческой
разведки, этим вопросам посвящен ряд монографий [1, 2]. В данном
разделе компьютерного практикума рассмотрим такой важный и емкий источник информации о конкурентах, как Интернет.
Разумеется, отдел маркетинга должен знать все о сайтах своих
конкурентов, их структуре (карте сайта), наличии прайс-листов, новостных полос и т. п. Проблема состоит в том, чтобы оперативно отслеживать появление на их страницах информации о новинках, начало
рекламных акций и другие изменения сайтов. Если конкурентов много, а их сайты содержат массу информации, то отслеживание таких
изменений весьма трудоемко. По разным оценкам, для того чтобы
внимательно просмотреть маркетинговый материал в сети Интернет,
требуется не менее двух-трех дней. При этом менеджер должен сопоставлять материалы сайтов с данными, полученными ранее. В этом

Конкурентная разведка в Интернете

53

случае он должен либо полагаться на свою память, но здесь неизбежны ошибки, либо вести соответствующие записи, что занимает много
рабочего времени. Более того, сайты конкурентов, в том числе цены
на их продукцию, обновляются все же не так часто, и идеально, если
бы менеджер своевременно узнавал бы об этом. Последнее является залогом выполнения встречающихся иногда рекламных обещаний
покупателям «вернуть разницу, если найдете дешевле».
Предложим метод решения следующих проблем конкурентной
разведки в Интернете:
• отслеживание изменения рейтингов фирм-конкурентов в различных поисковых системах Интернета;
• обнаружение новых фирм на заданом секторе рынке;
• выявление обновления сайтов конкурирующих фирм и составление списка сайтов (страниц) для последующего просмотра и принятия решения менеджером;
• просмотр цен на конкретный товар или услугу и оценка их динамики;
• поиск новых изделий в данной области и просмотр новостей с
автосоставлением резюме.
Будем считать, что в результате предварительных маркетинговых
исследований составлен перечень сайтов фирм-конкурентов и страниц
с прайс-листами и новостями.
Основная идея заключается в том, чтобы на основе Web-запросов
в Excel и несложных макросов Visual Basic создать программу, которая будет автоматически заходить на страницы сайтов конкурирующих компаний с ценами и новыми предложениями, загружать прайслисты и обнаруживать изменения цен на товары (услуги) или появление новых товаров. На практике такую программу можно запускать
с помощью планировщика во внерабочее время (ночью), а в рабочее
время по специальному запросу менеджер может просматривать результаты ее работы.
Для примера выберем такой современный сектор рынка, как «умный дом», иначе говоря, средства домашней автоматики с дистанционным управлением (см. лекцию «Обработка знаний и искусственный
интеллект»).
Объем информации в сети Интернет по тематике «интеллектуальный дом», «умный дом», «умные вещи», «домашняя автоматика»
весьма велик. На рис. 1 показана первая страница с результатами
поиска «Яндекса» по ключевым словам «умный дом».
Для нашей фирмы эта и следующие несколько страниц уже представляют значительный интерес, поскольку позволяют отследить из-

54

Компьютерный практикум

 

Страница Yandex с результатом поиска

менения рейтинга в Интернете как собственного сайта, так и сайтов
конкурентов, увидеть появление на рынке новых фирм, стремительно
вошедших в первые ряды фирм, занимающих данный сегмент рынка. Здесь слева вверху и справа — реклама по программе Яндекса
«Купи слова», а это позволяет выявить начало рекламной компании
фирмы-конкурента.
Покажем, как обрабатывать информацию этой страницы на Excel.
Прежде всего запомните (скопируйте) адрес этой страницы, в данном
случае
http://www.yandex.ru/yandsearch?stype=www&nl=0&
text=%F3%EC%ED%FB%E9+%E4%EE%EC
(здесь в конце адреса закодированы слова «умный дом»). По этому
адресу данную страницу можно вызывать в любое время. Разумеется,
доступ к Интернету не будем выключать.
Чтобы получить данные из Интернета в Excel, в меню «Данные» выбираем «Импорт внешних данных», а затем — «Создать вебзапрос».
В поле «Адрес» окна «Создание веб-запроса» выбираем из списка указанный выше адрес (или вставляем его из буфера) и нажимаем кнопку «Пуск». После загрузки в это окно выбранной страницы
нажимаем «Импорт» и далее указываем ячейку, например А1, или
выбираем новый лист, в который будет помещена информация с этой

Конкурентная разведка в Интернете

 

55

Страница Excel с результатом поиска

страницы.
Полученная страница не так «симпатична», как исходная, и все
же приведем часть ее (начиная с 100-й строки) на рис. 2. Столбец А
содержит практически все текстовые данные поиска, выше этот столбец, а также столбец В включают рекламные материалы.
Эта страница в общем-то не нужна для оперативного просмотра
(лучше изучать данные непосредственно в Explorer), поэтому научимся загружать ее автоматически из Visual Basic с тем, чтобы в дальнейшем полученный макрос расширить функциями анализа данных.
Включаем режим записи макроса и повторяем процесс импорта
данных из Интернета. В результате будем получен следующий макрос:
Sub Макрос1()
’
With ActiveSheet.QueryTables.Add(Connection:="URL;
http://www.yandex.ru/yandsearch?stype=www&nl=0&
text=%F3%EC%ED%FB%E9+%E4%EE%EC",Destination:=Range("$A$1"))
.Name = "www.yandex.ru"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False

56

Компьютерный практикум

.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
ActiveWindow.SmallScroll Down:=-12
End Sub
Нельзя сказать, что текст макроса этого макроса понятен, но ведь
он получен автоматически и работает!
Алгоритм обработки заключается в сравнении текстов в определенных ячейках или всего листа с ранее сохраненными в памяти данными. Для этого не обязательно писать программу на Visual Basic.
Например, пусть, как показано на рис. 2, данные из Интернета импортированы на лист 2, а на листе 3 находятся те же данные, загруженные ранее. Для обнаружения изменений занесем в ячейку А1
листа 4 функцию
=ЕСЛИ(СОВПАД(Лист2!A1;Лист3!A1);1;0)
и методом автозаполнения внесем ее в строки 1–200 столбцов А и В.
Теперь простое суммирование этих 400 ячеек покажет, что изменений
нет (при сумме равной 0) или изменилось столько-то строк. Этой
информации уже достаточно менеджеру, чтобы принять решение о
необходимости детального изучения страницы поиска Яндекса, как
уже говорилось, с рейтингами и рекламой фирм-конкурентов.
Аналогично можно составить макросы для просмотра страниц
сайтов и прайс-листов конкурирующих фирм. Известно, что Интернет
постоянно меняется, поэтому подготовленные макросы и программы
(набор функций Excel) для анализа данных могут уже не работать,
так что придется время от времени их модернизировать. Большей

Конкурентная разведка в Интернете

57

частью подобные изменения сведутся к изменению не макроса, а только функций, например =ЕСЛИ(СОВПАД..., с номерами ячеек страниц,
загруженных из Интернета и содержащих интересующие менеджера
сведения. Поводом для изменений будет сообщение программы об
изменениях (событиях) на сайте, которое не будет соответствовать запрограммируемому ранее событию. Например, наша программа конкурентной разведки «сообщила», что изменилась цена товара, а оказалось, что эта цена просто переместилась на другое место в изучаемом нами прайс-листе, поскольку добавлена строка с новым товаром.
Однако это нам тоже очень интересно. Мы корректируем функцию
Excel, сравнивающую цены на старый товар и, быть может, принимаем
решение о добавлении новой функции анализа цены нового товара.
Для тех, кто решит создать подобный набор макросов и программ, подскажем, что удобно объединить все макросы в один с именем Auto Open. В этом случае запуск такого макроса будет автоматическим в момент загрузки книги Excel, что позволяет запускать
данную программу конкурентной разведки с помощью планировщика
Windows в заданное время.
Конечно, подготовка подобной программы конкурентной разведки в Интернете требует времени, однако мы значительно экономим
время менеджера на просмотре сайтов конкурирующих фирм, так как:
• менеждеру не нужно собственноручно просматривать сайты, программа сама делает это в автоматическом режиме, скажем ночью,
а утром выдаст менеджеру результаты поиска;
• если есть изменения на интересующих сайтах, программа выдаст
сообщение с указанием на конкретный сайт (страницу);
• менеджеру не нужно сосредотачивать свое внимание на поиске
изменений на конкретном сайте.
Таким образом, как уже было сказано, ранее на просмотр всех
сайтов требовалось два–три дня, более того, пока менеджер просматривает последние в списке сайты, нет гарантии, что за эти три дня
на первых из просматриваемых сайтов не произошли изменения. Начиная новый цикл просмотра, менеджер, естественно, обнаружит эти
изменения и сообщит об этом руководству, но время уже будет потеряно. Теперь этот процесс должен занять до 1 часа в день при весьма
своевременном оповещении о действиях конкурентов.
Более сложная программа оптимизации портфеля акций по данным о ценах на акции веб-узла Yahoo приведена в главе 27 весьма
полезной для менеджеров книги К. Олбрайт [3].

Определение рейтинга сайта в поисковых
системах

Основная идея заключается в том, чтобы на основе Web-запросов
в Excel и несложных макросов Visual Basic создать программу, которая будет автоматически заходить на страницы c результатами, которые формируют известные поисковые системы, например yandex.ru,
google.com, rambler.ru и др. Эти страницы можно загружать в Excel,
затем выполнять поиск стандартными средствами Excel имени сайта
компании, в данной работе выбран интернет-магазин shop.pack.ru, и
в результате формировать справку о рейтинге компании в поисковых
системах. На практике такую программу можно запускать с помощью
планировщика во внерабочее время (ночью), а в рабочее время, открыв страницу Excel с рейтингом, менеджер может оценить, насколько продвинулся или опустился рейтинг компании за текущее время.
В ходе выполнения ВКР по заказу компании «Упаковка и Сервис» была создана программа на Excel определения рейтинга по следующим ключевым словам:
• упаковочные липкие ленты;
• упаковочная пленка;
• лента с логотипом;
• пищевые пленки;
• вакуумные пакеты;
• пакеты-майка.
Например, на рис. 1 показана первая страница с результатами
поиска «Яндекса» по ключевому слову «лента с логотипом».
Чтобы открыть эту же страницу в Excel, необходимо знать ее
URL-адрес, для его получения заходим в историю и обнаруживаем
следующую строку:
Покажем, как обрабатывать информацию этой страницы на Excel.
Прежде всего запоминаем (копируем) адрес этой страницы. По этому
адресу данную страницу можно вызывать в любое время, не загружая
yandex и не набирая ключевое слово «лента с логотипом».
Чтобы получить данные из Интернета в Excel, в меню «Данные»
выбираем «Импорт внешних данных», а затем «Создать веб-запрос».
В поле «Адрес» окна «Создание веб-запроса» выбираем из списка указанный выше адрес (или вставляем его из буфера) и нажимаем кнопку «Пуск». После загрузки в это окно выбранной страницы

Определение рейтинга сайта в поисковых системах

 

 

59

Страница поисковой выдачи

Адрес сайта в истории yandex.ru

(рис. 3) нажимаем «Импорт» и далее указываем ячейку, например А1,
или выбираем новый лист, в который будет помещена информация с
этой страницы.
После нажатия на кнопку «Импорт» открывается окно, где нужно указать лист с данными, в данном случае просто нажимаем ОК
(рис. 4).
Фрагмент с полученными данными приведен на рис. 5.
Эта страница в общем-то не нужна для оперативного просмотра
(лучше изучать данные непосредственно в браузере), поэтому научимся загружать ее автоматически из Visual Basic с тем, чтобы в дальнейшем полученный макрос расширить функциями анализа данных.
Включаем режим записи макроса и повторяем процесс импорта
данных из Интернета. В результате будем получен макрос, который
был уже рассмотрен в предыдущей работе.

60

Компьютерный практикум

 

 !

Загрузка страницы в Excel

Окно для выбора листа для импорта

Алгоритм обработки заключается в поиске сайта интернет-магазина shop.pack.ru на листе с загруженными результатами поиска. Для
этого сначала напишем на Visual Basic простую программу поиска
текста, в нашем случае "shop.packru".
Чтобы сохранять результаты поиска, создадим на листе2 таблицу
результатов (рис. 6). Области B2:F8 дадим имя result.
Эту область перед началом работы необходимо очистить. С помошью макрорекордера получаем следующий макрос:
Sub Макрос3()
’ Макрос3 Макрос
Range("B3:F8").Select
Selection.ClearContents

Определение рейтинга сайта в поисковых системах

 '

61

Фрагмент листа Excel с результатом поиска

 +

Таблица для сохранения рейтинга

End Sub
Две предпоследние строки используем как первые строки нашей
программы оценки рейтинга.
Нужный вид функции поиска можно получить, запустив макрорекордер и выполнив поиск по клавише CTRL+F. Независимо от результата поиска, останавливаем макрорекордер и считываем из полученного макроса следующий вызов функции Cells.Find:
Cells.Find(What:="shop.pack.ru", After:=ActiveCell,

62

Компьютерный практикум

LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False,
SearchFormat:=False).Activate
Теперь пишем сравнительно простой оператор Visual Basic, который проверяет, найден ли текст:
F = Cells.Find(What:="shop.pack.ru", After:=ActiveCell,
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False,
SearchFormat:=False).Activate
If Not F Is Nothing Then
Sheets("Лист2").Select
Range("B5").Select
ActiveCell.FormulaR1C1 = "1"
Endif
Теперь объединяем программу загрузки страницы из Интернета
и данную программу поиска.
Аналогичную операцию сначала проводим для поиска на второй
странице результатов. Снова возвращаемся в Интернет, в поисковой
выдаче переходим вниз страницы и нажимаем «Далее». Затем в истории копируем адрес второй страницы результатов поиска:
Видим, что добавился текст «&p=1». Очевидно, что для третьей
страницы будет &p=2» и т. д.
Для поиска нашей компании на второй странице уже не требуется повторять описанную выше процедуры с макрорекордером. Достаточно скопировать полученный макрос, заменить (точнее, добавить) в
адресе номер страницы, а также изменить в последних строках номер
ячейки результата с B5 на В6.
Аналогично делаем все другие макросы еще для трех страниц
поиска (считаем, что пользователь, скорее всего, ограничится пятью
страницами поиска), а затем и для других ключевых слов.
Наконец, объединяем все макросы в один с именем Workbook Open.
В этом случае запуск такого макроса будет автоматическим в момент
загрузки книги Excel, что позволяет запускать данную программу с
помощью планировщика Windows в заданное время.
Конечно, подготовка подобной программы оценки рейтинга в Интернете требует времени, однако мы значительно экономим время менеджера на оценку рейтинга по всем поисковым системам и для всех
ключевых слов.

    

     
    

Требования к оформлению выпускной
квалификационной работы

Специального стандарта на оформление выпускной квалификационной работы (ВКР) не существует, поэтому в высших учебных заведениях используют стандарты на оформление технической документации и отчетов по научно-исследовательским или опытно-конструкторским работам. Однако, как следует из многочисленной документации, выложенной в Интернете многими вузами, требования к оформлению ВКР могут незначительно отличаться, в частности, по размерам полей, шрифтам и другим элементам.
Ниже приведены основные правила оформления расчетно-пояснительной записки (РПЗ), изложенные в приложениях к Положениям «О порядке подготовки и защиты выпускной квалификационной
работы...» в МГТУ им. Н.Э. Баумана [11].
РПЗ ВКР должна быть грамотно написана и правильно оформлена. Она должна быть распечатана на одной стороне белого листа
бумаги формата А4 (210×297 мм) шрифтом черного цвета Times New
Roman размером 14 пунктов, кроме фрагментов кода программ, для
которых необходимо использовать шрифт Courier New.
Разрешается использовать компьютерные возможности акцентирования внимания на определенных терминах, формулах, теоремах,
применяя разное начертание шрифта.
Для переноса слов в тексте необходимо использовать автоматическую расстановку переносов.
Для избежания дополнительной проверки (см. ниже) рекомендуется придерживаться требований оформления РПЗ и перечисленных
ниже правил:

64

Оформление ВКР

1) текст РПЗ должен быть единым, без внедрения в него других
документов MS Word;
2) надписи, таблицы и рисунки должны быть встроены в текст (не
использовать различные варианты обтекания текстом) и не выходить
за его рамки;
3) графические объекты, используемые для создания рисунка,
должны быть объединены в группу, встроенную в текст;
4) рисунки должны содержать небольшое количество текстовой
информации;
5) текст в надписях не должен выходить за их границы;
6) цвет шрифта и обрамления в таблицах черный на белом фоне
страницы. Высота строк таблицы должна быть не менее 8 мм;
7) все формулы и обозначения, используемые в формулах, вводить с помощью редакторов формул (даже простые, типа Pэ , x2 и др.).
В РПЗ необходимо соблюдать следующие размеры полей страницы: левое — 3 см, правое — 1 см, нижнее — 2 см, верхнее — 2 см.
Если ширина таблицы больше ширины текста на листе, допускается размещение таблицы на отдельном листе альбомной ориентации.
Таблицу необходимо расположить так, чтобы ее можно было читать, поворачивая РПЗ по часовой стрелке и настроить поля страницы: левое — 2 см, правое — 2 см, нижнее — 1 см, верхнее — 3 см.
Выравнивание текста — по ширине, без отступов и интервалов.
Отступ первой строки абзацев — 1,25 см. Междустрочное расстояние — 1,5 строки.
Расстояние между заголовками — 2 интервала, между заголовком
и текстом — 3–4 интервала.
Номер страницы проставляется внизу листа в нижнем колонтитуле и должен располагаться по центру страницы, симметрично тексту.
Размеры колонтитулов в РПЗ — 1,25 см.
Верхний колонтитул должен быть пустой, в нижнем — только
номер страницы.
Все листы РПЗ должны быть пронумерованы, включая титульный лист, номер на котором не ставится.
Основную часть работы следует делить на главы (разделы), подразделы, пункты и подпункты.
Разделы (главы), подразделы, пункты и подпункты нумеруют
арабскими цифрами, например: раздел 1, подраздел 1.2, пункт 1.2.1.
Разделы и подразделы должны иметь заголовки. Слова «глава»,
«раздел», «подраздел», «пункт» не пишутся. Заголовки должны четко и кратко отражать содержание раздела. В конце заголовка точка

Требования к оформлению ВКР

65

не ставится. Допускается применение полужирного шрифта. Заголовки разделов (глав), подразделов, пунктов приводят после их номеров
через пробел. Пункт может не иметь заголовка.
Заголовки разделов, а также слова «ВВЕДЕНИЕ», «ЗАКЛЮЧЕНИЕ», «АННОТАЦИЯ», «СОДЕРЖАНИЕ», «СПИСОК ИСПОЛЬЗОВАННЫХ ИСТОЧНИКОВ», «СПИСОК ТЕРМИНОВ», «ПРИЛОЖЕНИЯ» «СПИСОК УСЛОВНЫХ ОБОЗНАЧЕНИЙ» следует располагать по центру без точки в конце и печатать прописными буквами,
не подчеркивая. Допускается полужирный шрифт. Данные разделы
не имеют номеров.
Переносы слов в заголовках не допускаются.
Каждую структурную часть РПЗ следует начинать с нового
листа.
***
В МГТУ им. Н.Э. Баумана для организации хранения ВКР и
проведения проверки на объем заимствованного текста разработана
электронно-библиотечная система «Банк ВКР».
Система доступна только из локальной сети Университета, при
этом место хранения работ закрыто от общего доступа, а результаты
проверки общедоступны. Они сгруппированы по факультетам, кафедрам, годам и находятся по адресу: http://vkr.bmstu.ru/vkr/result.
Для хранения и проверки принимаются РПЗ выпускных квалификационных работ в формате docx, объемом не более 20 Мбайт, созданные в текстовом редакторе MS Word.
Проверка РПЗ выполняется в два этапа:
• на первом этапе проверяется наличие титульного листа, структура РПЗ, соответствие отправленной РПЗ фамилии указанного
студента и оформление текста. Если работа не прошла проверку на этом этапе, то выдаются соответствующие замечания, которые студент должен устранить. После прохождения первого
этапа работа закладывается на хранение и начинает участвовать
в проверке на объем заимствованного текста в других работах;
• на втором этапе отсекается титульный лист, список использованных источников и выполняется проверка на объем заимствованного текста в оставшейся части РПЗ. По результатам этой проверки выдается общий процент заимствованного текста и ссылки
на источники, с которыми имеются совпадения, расположенные в
порядке убывания процента совпадений.
До передачи уполномоченному по работе с Банком ВКР (нормоконтроллеру) студент может проверить правильность оформления

66

Оформление ВКР

ВКР с помощью программы TestVkr, которую можно скачать с сайта
Университета.
Если программа TestVkr обнаружит оформление, которое может
использоваться для обхода проверки на объем заимствования, будет
проводится детальная проверка РПЗ, которая может занять 1...2 дня
(в зависимости от количества таких работ).
Не секрет, что студенты для написания ВКР используют файлы
своих курсовых работ, отчет о преддипломной практике, материалы о
компании, на которой проходила практика или на которой они работают. Кроме того, нередки случаи, когда студенты включают в ВКР
материалы, размещенные на сайте компании, и часто скачивают готовые работы по аналогичной теме из Интернета.
Вопрос об степени оригинальности готовой ВКР (необходимо не
менее 60 % «своего» текста) выходит за рамки данного учебного пособия. Так или иначе, но независимо от того, пишет ли студент ВКР
самостоятельно или «собирает» ее из различных источников, ему приходится решать проблему соблюдения правил оформления ВКР.
Многие студенты, как и большинство обычных пользователей,
считают, что популярный в настоящее время редактор Word достаточно прост в освоении, и начинают с ним работать, имея минимальные
навыки в общении с компьютером.
Обратим внимание на то, что при открытии нового документа
его начальный формат (формат по умолчанию) различен: установлен шрифт Times, 10 или 12 пунктов, выравнивание по левому краю;
уставлен запрет переносов, нет абзацного отступа, нет номеров страниц и пр. и пр. Этот формат очень далек от рекомендуемого для ВКР.
Видимо, так удобно американцам (и, конечно, разработчику
Word — компании Microsoft) — у них даже многие книги и особенно
документация напечатаны в стиле raggedright (рваный правый край),
а переносы им не нужны (большинство слов короткие).
Ну, что же теперь, форматировать документ каждый раз? Нет
и еще раз нет!
Кроме того, практически все пользователи сталкиваются с типичной для Word ситуацией: при правке текста или при копировании
фрагмента текста из других приложений или других текстовых файлов Word «сбивается» с установленного в тексте размера шрифта и
формата абзаца. Приходится вновь и вновь устанавливать «свой любимый размер».
В упомянутом выше Положении [11] для ускорения оформления
РПЗ студентам предлагается воспользоваться размещенным на сайте

Требования к оформлению ВКР

67

документом, в котором представлены оформленные различные части РПЗ. Для переноса параметров форматирования они могут воспользоваться командой «Формат по образцу» на вкладке «Главная».
Однако, по мнению автора, это не лучшее решение проблемы оформления РПЗ.
Исправить эту ситуацию несложно, однако большинство пользователей Word, не следуя главному правилу — RTFM (Прочти, наконец, эту чертову инструкцию), не знают, как это сделать. Да, честно
говоря, в этом Manual (например, [13]) так много всего, что не всегда
найдешь нужные советы.
Рекомендации, впервые предложенные автором еще в 2002 году [9]
и приведенные в следующем разделе, помогут значительно сократить
время на форматирование ВКР. Более того, главное: настройка Word
на своем компьютере, на которую будет потрачено 10–15 минут, позволит и в дальнейшем, при работе в серьезной компании, требующей
четкого соблюдения правил документооборота, выпускать качественно оформленные документы.

Как настроить Microsoft Word?

Основная идея заключается в том, чтобы раз и навсегда в Word
установить нужное форматирование и при открытии нового документа или копировании текста из других файлов уже никогда не заботиться о соблюдении правил оформления документа.
При открытии нового документа Word начинает работу с открытия файла Normal.dotx или Normal.dotm (с поддержкой макросов).
Это файл в последних версиях Windows находится в папке
C:\Users\Пользователь\AppData\Roaming\Microsoft\Шаблоны
Здесь "Пользователь" нужно заменить на имя пользователя, выбранное при инсталляции Windows (на рис. 1 это имя Asus).
При попытке перейти по указанному выше пути, обнаруживаем,
что папка AppData в проводнике не видна, она помечена как скрытая. Поэтому в Проводнике переходим на закладку «Вид» и устанавливаем флажок «Скрытые элементы» (рис. 1). Теперь мы видим
папку AppData, открываем ее и двигаемся далее до папки «Шаблоны». Находим файл Normal.dotm и копируем его, например, в папку
«Документы».

 

Доступ к скрытой папке AppData

В различных версиях Windows и Microsoft Office этот файл может
находиться в другом месте, его тогда можно найти с помощью опции
«Поиск» в Проводнике. Далее открываем свойства Normal.dotm, копируем его расположние и вставляем в адресную строку Проводника.
Попадаем сразу в папку с нужным файлом, не зависимо от наличия
скрытых папок. Не закрываем эту папку, чтобы после всех настроек
сохранить в ней Normal.dotm.

69

Как настроить Microsoft Word?

 

Установка полей страницы

 

Установка размеров колонтитулов

Запускаем Word, обязательно закрываем пустой документ, который открылся, и открываем файл Normal.dotm из папки «Документы».
В Normal.dot хранятся все установки по умолчанию для нового
документа Word. Настроим Word для работы с РПЗ по правилам,
изложенным в [11] и кратко описанным в предыдущем разделе.
Начнем с полей страницы и колонтитулов. Установка полей с помощью мыши не лучшее решение. Следует открыть закладку «Разметка страницы», далее выбрать «Поля» и в открывшемся окне внизу
нажать на ссылку «Настраиваемые поля» В новом окне устанавливаем размеры полей, как на рис. 2.
Переходим на закладку «Макет». Устанавливаем размер колонтитулов (рис. 3). Установите также флажок «Различать колонтитулы
первой страницы», чтобы убрать номер страницы с титульного листа.
Теперь на закладке «Главная» нажимаем кнопку параграфа (¶).
Внизу страницы видим, что в нижнем колонтитуле есть лишняя строка после номера «1» (рис. 4). Устанавливаем курсор на эту строку и
нажимаем кнопку Backspace. Затем нажимаем эту кнопку еще два раза, стирая номер страницы. Тем самым на первой странице номера не
будет, на следующей странице (когда будем работать с документом)
номер будет равен 2.

 !

В нижнем колонтитуле не должно быть две строки

Установку шрифтов и прочих параметров текста можно, конечно,
установить на закладке «Главная», но в этом случае, как только вы

70

Оформление ВКР

будете вставлять тест из внешнего источника или редактировать свой
тест, Word может неожиданно перейти на совсем другой шрифт.
Поэтому настоятельно рекомендуется настроить формат стиля
«обычный», с которого начинается любой документ и который, собственно, использует Word при вставке текста из других файлов, когда
вы выбираете (если умеете) «Специальную вставку» неформатированного текста (см. об этом ниже).
Теперь самое время установить для
всех будущих документов параметры основного текста и всех заголовков, с которыми Вы предпочитаете работать (в примере ниже — параметры, рекомендованные для ВКР).
Удобно кликнуть по стрелке справа
от текста «Стили» и открыть окно стилей
(рис. 5).
Вначале настроим стиль «обычный».
В открывшемся поле «Стили» кликните
правой кнопкой по стилю «Обычный», в
контекстном меню с помощью команды
«Изменить» откройте новое окно — «Изменение стиля». Выберите тип и размер
шрифта (Times New Roman, 14 пт), кликните по кнопке «Формат», в контекстном
меню выберите «Абзац», далее установите выравнивание по ширине, задайте абзацный отступ равным 1,25 см, междустрочный интервал равный 1,5 строки, интервалы перед и после абзаца 0 пт.
Обязательно настройте аналогично
стили заголовков, в частности, установите цвет шрифта на черный (в ВКР запре ' Область «Стили» щен цветной шрифт). Размер шрифта
установите равным, например, 16 пт, 15 пт и 14 пт для заголовков
уровней 1, 2 и 3 соответственно, выберите полужирный шрифт, выравнивание по центру, отступ 0 пт, интервалы перед и после равны 28
и 42 пт соответственно (это примерно 2 и 3 интервала, как необходимо для РПЗ). Кроме того, на закладке «Положение на странице» окна
«Абзац» установите флажок запрета автоматического переноса слов.
Рекомендуется изменить формат стиля «Без интервала»: шрифт
Times New Roman, 12 пт, выравнивание по центру, абзацный отступ

Как настроить Microsoft Word?

71

0 пт. Этот стиль будет в дальнейшем использоваться для таблиц и
рисунков, сделанных из фигур.
Наконец, переходим на закладку «Разметка страницы» и по кнопке «Установка переносов» ставим галочку «Авто».
В принципе основные настройки файла Normal завершены. Закрываем Word. Использовать команду «Сохранить» не обязательно,
при закрытии файла в нем сохранятся все изменения.
Скопируйте файл Normal с помощью Проводника на его законное место, указанное в начале этого раздела. Файл Normal в папке
«Документы» может еще пригодиться, если Word по какой-то причине вернется к старым настройкам. Это, как следует из форумов в
Интернете, иногда случается.
Проверьте теперь свои настройки, просто запустив Word.
Помните, что указание стилей для заголовков нужно не только
для их выделения (что при небольшом числе заголовков можно сделать и вручную), а для быстрого создания оглавления. Для этого
перейдите в меню «Ссылки» → «Оглавление». Оглавление в РПЗ
должно идти после Аннотации (Реферата). После автоматического составления оглавления не забудьте изменить заголовок «Оглавление»
на «Содержание».
В случае значительной правки в документе оглавление легко исправить, кликнув в любом месте оглавления и выбрав из контекстного
меню «Обновить поле».
Нередко в окне «Стиль» выпадает так много стилей, что у пользователя сразу отпадает желание ими пользоваться. Как правило, это
связано с тем, что при традиционно используемой вставке «чужого»
текста последний вставляется вместе со своими форматами, совсем не
нужными для данного документа.
Чтобы сразу тест вставлялся сразу в нужном формате и без добавления стилей после его копирования, кликните по стрелке под кнопкой «Вставить», затем кликните по «Специальной вставке», в появившеся окне выберите «неформатированный тест» и, наконец, нажмите
.
Если эту операцию приходится делать часто, удобно заменить четыре клика мышкой на один клик по новой кнопке, которую можно
сделать с помощью макрорекордера.
Для этого перейдите в закладку «Вид», далее кликните по стрелке под кнопкой «Макросы» и выберите «Запись макроса...». В открывшемся окне «Запись макроса» просто нажимаем
 . К курсору добавляется значок, похожий на магнитофонную кассету. Теперь

72

Оформление ВКР

повторяем операцию вставки неформатированного текста, кликаем по
стрелке под кнопкой «Макросы» и выбираем «Остановить запись».
Мы получили макрос с единственной командой Visual Basic
Selection.PasteAndFormat (wdPasteDefault)
Чтобы использовать этот макрос, кликните по стрелке справа от
панели быстрого доступа и в контекстном меню выберите «Другие команды». Откроется меню настройки панели быстрого доступа и сочетаний клавиш. В выпадающем меню «Выбрать команды из» выбираем
макросы, затем кликаем по Normal.New.Macros.Macros1 и нажимаем
.
кнопку «Добавить». В панели быстрого доступа появится значок
Можно сделать по другому. В окне «Запись макроса» в поле
«Назначить макрос» выбрать «клавишам». Затем в новом окне в поле
«Новое сочетание клавиш» покажите сочетание, например, ALT+V.
Далее повторить запись макроса (который будем иметь имя Macros2).
Итак, с этого момента новый текст из других документов, Интернета или иных программ будем вставлять только с помощью кнопки
или сочетания ALT+V. При этом не придется заниматься форматированием!
Обратим внимание, что если в новом тесте встретятся таблицы, то
они превратятся в текст. Таблицы придется копировать и вставлять
отдельно. Для их форматирования можно использовать стиль «Без
интервала», который был подготовлен при настройке файла Normal.
Конечно, было бы очень хорошо, если бы Word автоматически
переключался на стиль «таблица» или «рисунок», когда вы чертите
таблицу или начинаете работать в меню «Вставка — Фигуры». Но
увы! Это не реализовано.
Для таблиц рекомендуется следующий стиль: шрифт на 2 пункта меньше основного, выравнивание по центру, без абзацного отступа;
для рисунков — тот же стиль, но дополнительно убрать переносы. Полезно создать свои стили для названий таблиц и подписей к рисункам.
По правилам, принятым в книжных издательствах, все столбцы
таблиц с числовыми данными центрируются, за исключением так называемого боковика, в котором обычно располагаются названия строк
(наименование параметров, статей затрат и пр.), причем первая ячейка боковика в шапке таблицы также центрируется. Пример такой
таблицы приведен ниже.
Обратите внимание, что шапка таблицы здесь выделена более
жирной линией. Можно выделить текст в шапке полужирным шрифтом. Если в каком-либо столбце таблицы есть текст, то он центрируется, если занимает одну-две строки, и выравнивается влево, если он
достаточно большой.

73

Как настроить Microsoft Word?

Таблица (номер)
Заголовок
По центру

по центру

по центру

по центру

Выравнивание влево

по центру

по центру

по центру

Выравнивание влево

по центру

по центру

по центру

Выравнивание влево

по центру

по центру

по центру

 +

Создание «рекомендуемого» стиля таблицы

При вставке таблицы автоматически появляется закладка «Конструктор», которая позволяет создавать правильные, используемые, в
частности, в книгоиздании, стили таблиц. Вот как, например, может
выглядеть стиль «Рекомендуемый» (рис. 6). Таблица разбивается на
отдельные элементы (заголовок, первый столбец, строка итогов и пр.),
для которых шрифты, выравнивание, границы и прочее оформление
задаются индивидуально. В данном случае порядок форматирования
следующий. Сначала применяем для всей таблицы центрирование,
стираем старые границы и устанавливаем внешние и внутренние горизонтальные границы, затем для заголовка и строки итогов вводим
все границы, для первого столбца применяем выравнивание влево.
Форматирование и редактирование ячеек таблицы позволяет делать закладка «Макет». Здесь следует обратить внимание, например, на возможность выполнять арифметические операции с числами
в таблицах (пункт «формулы»).
Несколько других полезных советов.
Многие настройки Word устанавливаются в меню «Параметры

74

Оформление ВКР

Word». Соответствующую кнопку вызова этого меню можно увидеть,
если нажать кнопку «Office» (так называется кнопка слева вверху всех
приложений Microsoft Office). В пункте «Параметры автозамены» меню «Параметры Word» можно посоветовать:
• отменить замену буквы, следующей после точки, на прописную
(иначе, скажем, в экономических документах будут встречаться
словосочетания «тыс. Руб.», а вырабатывать привычку нажимать
Ctrl + Z или бегать мышкой в меню правки за отменой совсем ни
к чему);
• убрать автоматическую нумерацию абзацев с автоматической
выключкой, сняв флажок «Применить к спискам»;
• добавить замену двух дефисов при наборе текста на тире (не все
знают, что тире следует ставить с помощью клавиш Ctrl + «серый
минус», или клавиш Ctrl + «;» (Ctrl + Ж на ноутбуке без дополнительной «малой» клавиатуры), или через меню «Вставка» →
«Символ», а поэтому и не используют его);
• внимательно изучите другие замены и решите, нужны ли они.
Пользователям, которым часто приходится набирать тексты на
определенную тему, можно посоветовать разработать для себя систему сокращений, например: кп — компьютер; бд — база данных и т. д.
Важно, чтобы само сокращение не было словом. Если задать такие
сокращения в меню «автозамена», то можно значительно ускорить
ввод текста. Подобные комбинации букв Word будет автоматически
заменять на указанные слова, если после них будут следовать пробел
или знак препинания. При этом ввод прописных букв, например БД,
даст замену на прописные — БАЗА ДАННЫХ. Внутри слов эти комбинации не заменяются. Здесь, правда, придется нажимать Backspace,
чтобы ввести грамматические окончания слов, но к этому, если уж
вводить подобную автоматизацию, можно привыкнуть. Автозамена
будет действовать для всех новых документов и не требует настройки
файла Normal.dot.

Типичные ошибки при оформлении ВКР

Программу TestVkr
можно скачать c домашнего компьютера, зайдя на сайт МГТУ им. Н.Э. Баумана http://bmstu.ru и выполнив поиск "testvkr". В результатах поиска можно выбрать одну из
страниц (в сентябре 2018 года их было 13), где можно скачать не только программу, но и файл с требованиями к ВКР. Поиск testVkr можно
выполнить и непосредственно в Интернете, например в yandex.ru. В
поисковой выдаче появятся несколько персональных страниц кафедр
или преподавателей МГТУ им. Н.Э. Баумана, на которых есть ссылка на программу.
Запускаем программу. Поскольку издатель неопознан, ваш компьютер может выдать сообщение «Система Windows защитила ваш
компьютер..». Кликаем ниже этого сообщения на ссылку «Подробнее» и затем «Выполнить в любом случае». Последующие запуски
пройдут без такого сообщения.
Указываем уровень подготовки. Окрываем проводник и перетаскиваем свою РПЗ в окно программы (рис. 1), и она проверит на
правильность оформления. На антиплагиат программа не проверяет,
это может сделать нормоконтроллер из внутренней сети МГТУ им.
Н.Э. Баумана.

 

Первая (и единственная) страница программы TestVkr

Ниже рассмотрены типичные ошибки, которые выдает программа TestVkr в результате проверки РПЗ. Этот список ошибок собран

76

Оформление ВКР

за последние несколько лет работы с программой. Возможно, могут
быть и другие ошибки, которые обнаружит или будет обнаруживать
программа в новых версиях.
Отсутствует титульный лист.
Неправильно введен титульный лист.
Допущены ошибки в названии университета на титульном
листе.
Не пытайтесь самостоятельно набрать титульный лист. Скачайте
его с сайта Университета или возьмите на выпускающей кафедре.
До реферата (аннотации) введено слишком много текста.
Иногда в РПЗ после титульного листа студенты включают задание, календарный план и другие методические материалы. Они в РПЗ
не нужны. После титульного листа идет сразу реферат (аннотация).
В содержании отсутствуют номера страниц (возможно
после содержания идет непонятный для программы заголовок).
Не определено начало основной части. После введения должен идти первый раздел (глава) основной части (в том числе
и в содержании).
Приведите в соответствие заголовки в основной части и
содержании:...
Скорее всего, содержание ВКР оформлено вручную, например,
в виде таблицы. Содержание необходимо подготовить по правилам
Word. А именно: все заголовки пометить в окне «Стили» как «Заголовок 1» (названия глав) и «Заголовок 2» (разделы в главах). Затем перейти на закладку «Ссылки», выбрать «Оглавление» и затем
«Автособираемое оглавление 1». Не забудьте после автоматического составления оглавления заменить «Оглавление» на «Содержание».
Предварительно измените формат заголовков, в частности замените
стандартный (для Word) синий цвет теста на черный.
В документе отсутствует реферат (аннотация) или неправильно оформлен его заголовок.
В документе отсутствует содержание или неправильно
оформлен его заголовок.
В документе отсутствует заключение или неправильно
оформлен его заголовок.
В документе отсутствует список использованных источников или неправильно оформлен его заголовок.
Оформите правильно заголовок приложения ("ПРИЛОЖЕНИЕ А").
Приведите в соответствие заголовки в основной части и содержании. Проверьте, что данные структурные заголовки РПЗ написаны

Типичные ошибки при оформлении ВКР

77

без ошибок, без точки и находятся в нужном порядке. Заголовок приложения нужет всегда, даже если приложение не предусмотрено.
Документ содержит страницы, размеры полей на которых
не соответствуют требованиям.
Иногда студент проверяет первую или вторую страницу РПЗ и
видит, что все поля установлены верно. Ошибка здесь может быть
в случае, если для размещения новых глав использованы разрывы
разделов. В каждом разделе могут быть свои поля, отличающиеся от
полей первых страниц документа. Разрыв раздела необходим только
при вставке большой таблицы на страницу с альбомной ориентацией.
Если больших таблиц нет, рекомендуется заменить все разрывы
разделов (^b) на разрыв страницы (^m). Указанные в скобках символы нужно ввести в соответствующие поля окна «Найти и заменить»,
можно также выбрать эти символы в контекстном меню «Специальный». После этого установить правильные поля (см. рис. 2 в предыдущем разделе) по всему документу.
Если есть большие таблицы, то следует, включив для контроля на
закладке «главная» отображение скрытых символов форматирования
(знак ¶), проверить поля в каждом разделе.
Размеры колонтитулов должны быть 1,25 см.
Ситуация с разрывами разделов описана выше. Проверьте размеры колонтитулов в каждом разделе (см. рис. 3 в предыдущем разделе).
Нижние колонтитулы должны содержать один абзац с номером страницы, выровненным по центру. На титульном листе номер страницы не ставится.
Данная ситуация рассмотрена в предыдущем разделе (см. рис. 4
и пояснения к нему).
Верхние колонтитулы должны содержать один пустой абзац.
Дважды кликните по верхней части страницы и удалите лишние
строки (удобно включить ¶).
Левый отступ в абзацах должен быть равен 0 см.: "текст1"|
"текст2"...
Используя подсказки "текст1"|"текст2"..., находим абзацы с указанным текстом и проверяем в окне «Абзац» отступы. В одной из
студенческих работ в поле отступов было обнаружено число −0. Найти визуально такой абзац, очевидно, невозможно.
Совет: чтобы не набирать в окне «Найти и заменить» текст подсказок, скопируйте из окна программы TestVkr подсказку, помещенную между знаками «|», и вставьте ее в поле поиска Word.

78

Оформление ВКР

Междустрочный интервал в абзацах должен быть равен 1,5
строки (в таблицах одинарный): "текст1"|"текст2"...
Используя подсказки "текст1"|"текст2"..., находим абзацы с указанным текстом и проверяем в окне «Абзац» размер междустрочного
интервала.
В документе много символов, цвет которых отличается от
черного: "текст1"|"текст2"...
В документе много символов, цвет которых плохо различим
от цвета фона: "текст1"|"текст2"...
В документе много символов, цвет фона которых отличается от белого: "текст1"|"текст2"...
В таблицах, имеющихся в документе, много символов, цвет
фона которых отличается от белого: "текст1"|"текст2"...
Иногда может потребоваться много времени, чтобы найти все такие символы. Проще всего выделить весь документ (горячая клавиша
убрать цветные симвоCtrl + A) и с помощью кнопок
лы и заливку фона.
В документе много символов, размер которых меньше 8 пт:
"текст1"|"текст2"...
В документе много уплотненных символов.
Здесь также можно выделить весь текст и установить один размер
шрифта (14 пт). Однако при этом может испортится форматирование
таблиц. Можно и отдельно найти текст из подсказок.
Левый (правый) отступ в абзацах должен быть равен 0 см.:
Для исправления выделяем весь документ (горячая клавиша Ctrl
+ A), кликаем по полю «Абзац» на закладке «Главная». Открывается
окно «Абзац» (рис. 2). Все поля в окне пустые, поскольку в документе
они имеют разные значения. Вводим в поля «Отступ» значение 0 и
нажимаем «ОК».
В документе много скрытых символов: "текст1"|"текст2"...
Скорее всего, текст заимствован из ненадежного источника, автор
которого постарался таким образом навредить тому, кто скачал текст,
или защитить свои авторские права. Попытка найти в РПЗ, например,
"текст1" не даст результата. Есть два способа удалить скрытый текст.
Первый: выделяем весь текст (Ctrl + A), открываем окно
«Шрифт» (кликаем по стрелке справа от поля «Шрифт» на закладке
«Главная»). Мы видим, что последний флажок «Скрытый» в списке «Видоизменение» содержит черный квадратик. Кликаем по нему
дважды, чтобы он стал пустым. Нажимаем
.

Типичные ошибки при оформлении ВКР

 

79

Окно «Абзац»

Второй: более мощный, используем его, если первый способ не
дал результата. В основном меню кнопки Office выбираем «Подготовить» и затем «Инспектор документов». Получаем окно, показанное на рис. 3. С помощью кнопок справа удаляем не нужные в РПЗ
элементы.
Документ содержит таблицы с включенным обтеканием
текстом.

 

Окно инспектора документов

80

Оформление ВКР

 !

Окно свойства таблицы

Документ содержит таблицы, выходящие за рамки текста
страницы.
В данном случае никаких подсказок программа не дает. Придется просмотреть все таблицы в документе и в каждой посмотреть ее
свойства (кликнуть в любом месте таблицы и в контекстном меню
выбрать «Свойства таблицы», рис. 4).
Проверяем, что обтекания нет, отступ 0 пт, ширина таблицы не
более 17 см (можно чуть больше: это за счет того, что граничные
линии чуть-чуть выходят за поля страницы).
Высота строк в таблицах должна быть не менее 8 мм.:
"текст1"|"текст2"...
Замечена подозрительная высота строк в таблицах документа. Переключите указание высоты строк из режима "Точно" на "Минимум" (доп. проверка): "текст1"|"текст2"...
Кликаем в окне свойств таблицы (см. рис. 4) по закладке «Строка», устанавливаем высоту 8 мм и снимаем режим "Точно". Здесь
можно не смотреть все таблицы, а воспользоваться подсказками.
Документ содержит плавающие рисунки. Встройте рисунки в текст (доп. проверка).
Снова придется просмотреть все рисунки, кликнув правой кнопкой мыши по каждому рисунку и посмотреть в контекстном меню,
что рисунок находится в тексте (рис. 5). Для некоторых типов рисун-

Типичные ошибки при оформлении ВКР

 '

Проверка обтекания рисунка

 +

Проверка обтекания объекта

81

ков, как правило, скопированных из различных графических редакторов, это меню не содержит пункта с обтеканием, тогда надо выбрать
пункт «Формат объекта» и в открывшемся окне проверить обтекание
«в тексте» (рис. 6).
Документ содержит рисунки, выходящие за рамки текста.
Такие рисунки находим визуально, мышкой уменьшаем размеры,
если рисунок выходит за рамки текста.
Документ содержит плавающие графические фигуры.
Встройте фигуры в текст (доп. проверка): "текст1"|"текст2"...
В РПЗ не разрешено использовать вставки фигур, из которых
часто студенты рисуют структурные схемы. Самый простой способ
оставить такие схемы — превратить их в рисунок. Для этого кликаем Shift + PrtSc, делая скрин-шот экрана, затем открываем простейший графический редактор Paint (он вызывается из меню стандартных программ Windows). Вставляем скрин-шот, вырезаем по нужному контуру и вставляем рисунок снова в Word.
В документе много текста в рисунках.
Раз много текста, ничего не сделаешь, как правило, это таблица, сканированная или скачанная из Интернета. Либо удаляем такой
рисунок, либо набираем текст из рисунка в таблицу.

82

Оформление ВКР

Документ содержит плавающие надписи. Встройте надписи в текст (доп. проверка): "текст1"|"текст2"...
Необходимо выполнить поиск текста из подсказки, правой кнопкой кликнуть по надписи и в контекстном меню выбрать «Формат
надписи». Откроется одноименное окно, оно полностью аналогично
окну, показанному на рис. 5. Надпись должна быть в тексте.
Для поиска всех надписей можно использовать простой способ:
выделить весь текст (Alt + A). Надписи останутся не выделенными
(белыми).
Надпись иногда может попасть между страницами и не видна на
экране. Для ее обнаружения придется стирать (временно) часть текста, чтобы надпись переместилась на страницу и стала видимой.
Укажите ссылки на использованные источники:... Удалите
ссылки на отсутствующие источники:...
На все использованные источники должна быть ссылка в тексте.
Во втором случае, скорее всего, текст скачан из Интернета и в нем
остались ссылки, которые не соответствуют списку источников в ВКР.
Итогом работы программы TestVkr могут быть три сообщения.
Первые два:
Документ претендует на детальную проверку, которая может занять 1-2 дня...
Для окончательной проверки необходимо исправить замечания и повторить попытку...
не дают право загрузки РПЗ в Базу ВКР. Придется, как указано, исправить замечания и повторить проверку.
Третье сообщение:
Проверка закончена. Замечаний нет.
свидетельствует об успешном завершении первого этапа проверки
оформления РПЗ.
Автор желает всем студентам успешно пройти проверку на заимствование и защиту ВКР.

Литература

< = (
< = (
4! "
>!< 0
? & ?
5 % ?

1.
Информационные технологии в экономике.
Учебное пособие. — Горячая линия — Телеком, 2008. — 238 с.
Разработка офисных программ с помощью
2.
Visual Basic: Учебное пособие. — М.: МГУЛ, 1998–2015.
Excel Visual Basic для приложений: Пер. с англ. —
3.
М.: Восточная книжная компания, 1996.
Visual Basic для студентов. — М.: Радио и
4.
связь, 2001.
Бизнес-анализ с помощью Excel: Пер. с англ. —
5.
Киев: Диалектика, 2005.
Моделирование с помощью Microsoft Excel и VBA.
6.
Разработка систем поддержки принятия решений: Пер. с англ. — М.:
Издательский дом «Вильямс», 2005.
Корпоративная разведка. —
7.
М.: Ось-89, 2005.
Коммерческая разведка. — СПб.: Питер,
8.
2005.
Как настроить Microsoft Word? // Наука и
9.
жизнь. 2002. № 7. — http://www.nkj.ru/archive/articles/4488/
Тайны и секреты компьютера. 2-е изд. — M.:
10.
Горячая линия — Телеком, 2002.
о порядке подготовки и защиты выпускной ква11.
лификационной работы студентов МГТУ им. Н.Э. Баумана, обучающихся по основным образовательным программам бакалавриата. —
М.: МГТУ им. Н.Э. Баумана, 2016.
О нормоконтроле, размещении текстов в
12.
электронно-библиотечной системе и проверке на объем заимствования выпускных квалификационных работ бакалавров, специалистов,
магистров МГТУ им. Н.Э. Баумана. — М.: МГТУ им. Н.Э. Баумана, 2016.
и др. Microsoft Office 2016 / Office 365.
13.
Полное руководство. — М.: НиТ, 2017. — 448 с.

>
@ 4!  >
2% A
< = (
5 A A
B
B 1

/&%

Содержание

 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
  C ? D%  ! . . . . . . . . . . . . . . . . . . . . . . . .

Сумма прописью . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Оформление счета . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Базы данных и каталоги . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Знакомство с Visual Basic . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Платежное поручение . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Ведение бухгалтерских книг и журналов . . . . . . . . . . . . . . . . .
Управление запасами и прогнозы продаж . . . . . . . . . . . . . . . .
Проверка знаний студентов или сотрудников . . . . . . . . . . . . .
Конкурентная разведка в Интернете . . . . . . . . . . . . . . . . . . . . . .
Оценка рейтинга сайта в поисковых системах . . . . . . . . . . . .

  CC & !%  E 2%  
 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

Требования к оформлению выпускной квалификационной
работы . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Как настроить Microsoft Word . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Типичные ошибки при оформлении ВКР . . . . . . . . . . . . . . . . .
..............................................

F !

3
5
5
8
11
17
25
33
39
47
52
58
63
63
68
75
83