Офисные приложения на Excel и Visual Basic

Москва

Горячая линия – Телеком

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 учтены грамматические окончания мужского и женского рода, а также единичного и множественного числа.
A
B
C
D
E
F
G
H

9 сто
десять
одиннадцать один
одна
миллион
тысяча рубль
10 двести
двадцать
двенадцать
два
две
миллиона тысячи рубля
11 триста
тридцать
тринадцать
три
три
миллиона тысячи рубля
12 четыреста сорок
четырнадцать четыре четыре миллиона тысячи рубля
13 пятьсот
пятьдесят
пятнадцать
пять
пять
миллионов тысяч рублей
14 шестьсот шестьдесят шестнадцать шесть шесть миллионов тысяч рублей
15 семьсот
семьдесят
семнадцать
семь
семь
миллионов тысяч рублей
16 восемьсот восемьдесят восемнадцать восемь восемь миллионов тысяч рублей
17 девятьсот девяносто
девятнадцать девять девять миллионов тысяч рублей

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

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

Теперь научимся складывать названия чисел из цифр. Для этого
сначала введите в ячейку А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,
это всего лишь знаки препинания, а не часть формулы :-)