Визуальное программирование в MS Excel

 
 
 
 
Г. П. Виноградов, Н. В. Кирсанова 
 
 
 
 
 
 
 
 
 
ВИЗУАЛЬНОЕ ПРОГРАММИРОВАНИЕ В MS EXCEL 
 
 
Учебное пособие 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Москва    Вологда 
«Инфра-Инженерия» 
2024 
1 

 
 
УДК 004.4’242 
ББК 32.973 
В49 
 
 
 
 
Рецензенты: 
заслуженный деятель науки РФ, д. т. н., профессор кафедры «Автоматизация 
производственных процессов» ДГТУ Чернышев Ю. О.;  
зам. директора по науке СПбФ ИЗМИРАН, д. т. н., профессор  
Коробейников А. Г. 
 
 
 
 
 
 
 
 
 
 
Виноградов, Г. П. 
В49  
Визуальное программирование в MS Excel : учебное пособие / Г. П. Виноградов, Н. В. Кирсанова. – Москва ; Вологда : Инфра-Инженерия, 2024. – 
188 с. : ил., табл. 
ISBN 978-5-9729-1886-7 
 
Рассмотрены методы работы одного из популярных офисных приложений – 
Microsoft Excel. Содержит лабораторные работы с описанием алгоритма решения конкретной задачи и необходимым теоретическим материалом. 
Для студентов, аспирантов и учащихся лицеев и гимназий. Может быть использовано преподавателями. 
 
УДК 004.4’242 
ББК 32.973 
 
 
 
 
 
ISBN 978-5-9729-1886-7 
© Виноградов Г. П., Кирсанова Н. В., 2024 
 
© Издательство «Инфра-Инженерия», 2024 
 
© Оформление. Издательство «Инфра-Инженерия», 2024 
 
2 

ОГЛАВЛЕНИЕ 
 
ВВЕДЕНИЕ ................................................................................................................ 4 
ЛАБОРАТОРНАЯ РАБОТА № 1. Построение таблицы значений  
заданной функции ..................................................................................................... 8 
ЛАБОРАТОРНАЯ РАБОТА № 2  
(Продолжение лабораторной работы № 1) ........................................................... 19 
ЛАБОРАТОРНАЯ РАБОТА № 3. Графические возможности MS Excel.  
Построение диаграмм и графиков ......................................................................... 42 
ЛАБОРАТОРНАЯ РАБОТА № 4. Построение и вывод отчетов  
в MS Excel ................................................................................................................ 68 
ЛАБОРАТОРНАЯ РАБОТА № 5. Составление штатного расписания  
фирмы ....................................................................................................................... 87 
ЛАБОРАТОРНАЯ РАБОТА № 6. Разветвляющийся вычислительный  
процесс 
...................................................................................................................... 93 
ЛАБОРАТОРНАЯ РАБОТА № 7. Циклический вычислительный  
процесс 
.................................................................................................................... 103 
ЛАБОРАТОРНАЯ РАБОТА № 8. Определение корней скалярного  
уравнения ............................................................................................................... 115 
ЛАБОРАТОРНАЯ РАБОТА № 9. Обработка массивов.  
Работа с блоками ................................................................................................... 124 
ЛАБОРАТОРНАЯ РАБОТА № 10. Работа с формулами массивов 
................. 139 
ЛАБОРАТОРНАЯ РАБОТА № 11. Решение систем линейных уравнений ... 161 
ЛАБОРАТОРНАЯ РАБОТА № 12. Работа с электронной таблицей  
как базой данных ................................................................................................... 168 
БИБЛИОГРАФИЧЕСКИЙ СПИСОК 
.................................................................. 186 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
3 

ВВЕДЕНИЕ 
 
Microsoft Excel – табличный процессор, программа для создания и 
обработки электронных таблиц. Для представления каких бы то ни было 
данных в табличной форме достаточно тех средств, которыми обладают 
текстовые процессоры. Важнейшими преимуществами электронных таблиц 
являются адресация ячеек и совершение операций над данными в ячейках, в том 
числе и при построении графиков и диаграмм. 
Популярность MS Excel связана не только с его возможностми, но и с тем, 
что концепция визуального программирования в нем получила наиболее 
впечатляющее воплощение. Пользователь, не являющийся программистом, 
имеет возможность решать сложные задачи, пользуясь простой технологией 
«щелк-щелк-щелк» (это бытовое название), требуется только уметь выстроить 
саму последовательность щелчков кнопкой мыши, т. е. построить алгоритм 
решения задачи в MS Excel. Приложение MS Excel постоянно совершенствуется за счет расширения списка типовых операций, и это совершенствование приводит к появлению новых проблем. Теперь из множества 
типовых операций надо выстроить требуемую последовательность операций 
так, чтобы полученный список был близок к оптимальному. Разработка 
алгоритма решения прикладной задачи требует, по сути, решения комбинаторной задачи. Чтобы осознать масштабность этой проблемы, достаточно 
полистать Справку MS Excel в интернете (F1), учебник или другой теоретический материал по этому программному продукту. В них содержится 
описание возможностей данного приложения, но, как правило, не говорится, как 
их использовать для решения практических задач. В то же время MS Excel – 
профессиональная программа, в которой легко запутаться новичку. Осваивать 
такие сложные программные продукты удобнее путем изучения готовых 
алгоритмов с пошаговыми инструкциями решения ряда стандартных задач. 
Такой прием обучения позволяет пользователю быстрее привыкать к 
возможностям интерфейса MS Excel и одновременно овладевать умением 
строить алгоритмы любых других практических задач. Для преподавателя это 
возможность свести свою работу во время проведения лабораторных занятий к 
контролю степени усвоения материала. 
Данное учебное пособие позволяет всем желающим освоить методы 
решения практических задач в MS Excel.  
В начале каждой работы приводится постановка задачи, затем описывается 
алгоритм ее решения, на каждом шаге которого приводится необходимый 
теоретический материал. Заканчивается работа вопросами, на которые надо 
ответить, и перечнем умений, которыми следует овладеть, чтобы материал был 
усвоен. Если ответить не получится, нужно еще раз внимательно перечитать 
соответствующие разделы работы. Преподавателю нужно предъявить краткий 
конспект, файл Лабораторные работы.хlsx на экране и на флеш-карте в личном 
каталоге. 
Успешное освоение приведенного материала возможно только в том 
случае, когда персональный компьютер, ноутбук или другое устройство 
4 

находятся в непосредственном доступе. Хотя список рассмотренных работ не 
охватывает всего многообразия функций MS Excel, цель книги заключается в 
том, чтобы показать, как строятся алгоритмы решения практических задач в 
среде MS Excel визуальными средствами. Такой подход – фундамент для 
дальнейшего самостоятельного изучения новых возможностей самого 
привычного, гибкого и распространенного приложения в мире. 
 
Основы работы в Microsoft Excel 2013 
 
          – ярлык программы Microsoft Excel 2013. MS Excel позволяет работать с 
таблицами в 4 режимах. Для перехода между режимами на вкладке Вид в группе 
Режимы просмотра книги находятся кнопки Обычный, Страничный, 
Разметка страницы и Представление. Если окно свернуто, то кнопки 
появляются в раскрывающемся списке Режимы просмотра книги (рис. 1). 
Аналогичные кнопки находятся в строке Состояние нижней части окна  
MS Excel. 
 
 
 
Рис. 1. Фрагмент окна Microsoft Excel 
 
Назначение режимов: 
1. Обычный – наиболее удобен для выполнения большинства операций. 
2. Страничный – возможность осуществить предварительный просмотр 
разрывов страниц перед печатью. 
3. Разметка страницы – удобен для окончательного форматирования 
книги MS Excel и страниц перед печатью. В этом режиме отображаются 
5 

границы между страницами и границы таблицы, а также колонтитулы. 
Горизонтальные и вертикальные линейки позволяют видеть ширину полей 
(пустое место по бокам листа) и оценить реальную ширину строк и столбцов. 
При желании их габариты можно изменить. 
4. Представление – позволяет сохранить текущие параметры отображения 
и печати как настраиваемого представления для быстрого применения в 
будущем. 
 
Лента 
 
Под заголовком окна находится лента с названиями вкладок, на которых 
сгруппированы все команды MS Excel. Для открытия вкладки необходимо 
щелкнуть мышью на ее имени. После этого появятся команды вкладки, 
объединенные в логические группы по функциональному признаку (см. рис. 1), 
т. е. каждая вкладка связана с определенным типом действий, например с 
работой с формулами или разметкой страницы. 
Когда лента свернута, видны только названия вкладок (рис. 2). 
 
 
 
Рис. 2. Окно MS Excel со свернутой лентой 
 
Чтобы развернуть свернутую ленту, необходимо щелчком мыши открыть 
любую вкладку, лента станет видна, в ее правом нижнем углу следует нажать 
кнопку (      Закрепить ленту). 
Чтобы увеличить рабочую область, некоторые вкладки выводят только при 
необходимости, например вкладку Разработчик (рис. 3). 
Чтобы добавить вкладку на ленту, на вкладке Файл нужно выбрать 
Параметры (рис. 26). В появившемся диалоговом окне Параметры Excel (см. 
рис. 3) следует установить необходимые параметры. Например, для добавления 
вкладки Разработчик выбрать параметр Настроить ленту, установить флажок 
Разработчик. 
 
6 

 
 
Рис. 3. Окно Параметры Excel. Настройка ленты 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
7 

ЛАБОРАТОРНАЯ РАБОТА № 1 
 
ПОСТРОЕНИЕ ТАБЛИЦЫ ЗНАЧЕНИЙ  
ЗАДАННОЙ ФУНКЦИИ 
 
Цели работы: 
– освоить методы работы с формулами и закрепить навыки заполнения и 
редактирования таблицы; 
– познакомиться со способами адресации; 
– освоить некоторые приемы оформления таблиц. 
 
Постановка задачи: 
Вычислить значения функции у = cos2 х – 2ln (x2 + 1) для аргумента х из 
интервала [0; 4] с шагом 0,2. Решение получить в виде таблицы: 
 
№ 
Х 
Y1 = сos2 x 
Y2 = 2 ln (х2+1) 
Y = Y1+Y2 
операций сравнения 
в логических выражениях: 
= равно, 
< меньше, 
> больше, 
<= меньше или равно, 
>= больше или равно, 
<> не равно. 
 
ЗАДАНИЕ 1. Перед выполнением задачи узнайте, что такое формула и 
способы адресации в MS Excel. 
 
Формула 
 
Формула – это выражение, состоящее из операндов, соединенных знаками 
операций. 
Формула в MS Excel всегда начинается со знака = (равно), иначе введенные символы будут восприняты как текст. 
Выражения, входящие в формулу, могут быть арифметическими, логическими и строковыми (тестом). 
Операндами в выражении в зависимости от типа могут быть: числа (целые и вещественные, в том числе и в экспоненциальной форме (например, 
6,5Е-07 = 6,5 × 10-7 = 0,000 000 65)); текстовые константы; адреса ячеек 
(ссылки на ячейки); функции (математические, статистические, функции времени и даты, финансовые и другие); выражения в круглых скобках (арифметические, логические или строковые). 
В выражениях используются знаки операций: 
арифметических операций 
в арифметических выражениях: 
+ сложение, 
– вычитание, 
* умножение, 
/ деление, 
% процент, 
^ возведение в степень. 
8 

Для текстовых данных используется оператор строки & – объединение 
(сцепление) строк. Если строка является операндом в выражении, то она заключается в кавычки (например, = «это строка»). 
Когда в ячейке записана формула, то в ячейке показывается результат 
вычисления по формуле и введенным данным (если не установлен режим Показать формулы на вкладке Формулы), а сама формула отображается в строке 
формул, если активировать эту ячейку (выделить). 
Если значение формулы не может быть вычислено, то в ячейке появится 
сообщение об ошибке, начинающееся со знака # (см. стр. 93, лабораторную работу № 6); если изменять значения в ячейках, на которые есть ссылки в формуле, то результат изменяется автоматически. 
В электронной таблице каждая ячейка имеет адрес, который состоит из 
имени столбца, номера строки и однозначно идентифицирует ее. 
Приведем пример решения квадратного уравнения ax2 + bx + c = 0. В ячейку 
А2 ввести формулу =(-B1-корень(B1*B1-4*A1*C1))/(2*A1), 
в ячейку А3 – =(-B1+корень(B1*B1-4*A1*C1))/2/A1) (рис. 4). 
1.1. В ячейки A1, B1, C1 ввести значения коэффициентов a, b, c соответственно. В ячейку А1 ввести 1, в ячейку В1 ввести – 5, в ячейку С1 – 6. В ячейках 
А2 и А3 появится результат (2 и 3 соответственно). 
 
 
 
Рис. 4. Окно MS Excel. Вкладка Формулы 
 
1.2. Если значение коэффициента а в ячейке А1 изменить на –1. Ответами 
будут 1 и –6 соответственно. 
Функция КОРЕНЬ() вычисляет квадратный корень из числа (аргумента, заключенного в скобки), в данном случае из дискриминанта: 
 
B1*B1-4*A1*C1. 
 
 
9 

Абсолютная, относительная и смешанная  
адресация ячеек и блоков 
 
Относительная ссылка (адрес ячейки) – это обычный адрес ячейки, 
включающий имя столбца и номер строки. Например, B2 означает «ячейка 
находится в столбце В и в строке 2». По адресу обращаемся к ячейке и считываем данные, записанные в ней. При копировании формулы относительная 
ссылка указывает на ячейку, основываясь на ее положении относительно той 
ячейки, в которой находится формула.  
Пусть в ячейку В4 введена формула =В1+В2. MS Excel интерпретирует ее 
как «прибавить содержимое ячейки, расположенной тремя рядами выше, к содержимому ячейки двумя рядами выше». При копировании формулы =В1+В2 
из ячейки В4 в B5 (по столбцу) интерпретация этой формулы выглядит так: 
«прибавить содержимое ячейки, расположенной на три ряда выше, к содержимому ячейки на два ряда выше». Таким образом, формула в ячейке В5 изменит 
свой вид на =В2 +В3. При копировании формулы =В1+В2 из ячейки В4 в С4 (по 
строке) интерпретация формулы – «прибавить содержимое ячейки, расположенной на три ряда выше, к содержимому ячейки на два ряда выше». Формула 
в ячейке С4 изменит вид на =С1+С2, т. е. при копировании формул в относительных ссылках происходит смещение на величину переноса. При копировании относительные ссылки в формуле изменяются! При копировании по 
столбцу В1изменяется на В2, В2 – на В3, В3 – на В4 и т. д.; по строке А1 – на 
В1, В1 – на С1, С1 – на D1, D1 – на E1 и т. д.  
Абсолютная ссылка (адресация). Иногда необходимо, чтобы адрес 
ячейки в копируемой формуле не изменялся (был зафиксирован). 
Ссылка на ячейку, например С5, может иметь вид $C5, C$5, $C$5. Ссылка 
типа $C$5 при копировании остается неизменной и называется абсолютной. 
Положение на листе фиксировано: «Ячейка находится в столбце С и строке 5». 
Если при копировании формул необходимо сохранить ссылку на конкретную 
ячейку или область, для ее задания используется абсолютная ссылка. 
Смешанная адресация. Знак $ ставится только там, где он необходим 
(например, В$4 или $С2). Тогда при копировании один параметр адреса изменяется, а другой – нет. Атрибуты адреса, перед которыми стоит знак $, при 
копировании не изменяются (рис. 5).  
 
 
 
Рис. 5. Копирование диапазона A3:B3 с формулами вниз по столбцам 
10