Финансы в Excel. Автоматизация Excel: автоматизация рутинных операций в Excel без макросов Механизмы автоматизации в excel

Шаблоны - вещь удобная и полезная! Они позволяют сэкономить время при составлении «шаблонных» документов, которых в повседневной работе добрая половина.

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

В документах Word такую роль играют поля со списком. Идея: щелкнуть мышью на определенном месте в документе, в ответ раскроется список с несколькими вариантами, выбираете мышью нужный - он появился в этом месте. Создавать подобные элементы логично в шаблонах документов. Допустим, вы занимаетесь ультразвуковой диагностикой и решили запастись шаблонами для заключений по основным исследованиям. Их не так уж много, и в каждом стереотипно используются одни и те же фразы.

Инструменты для работы с полями сосредоточены на вкладке Разработчик . По умолчанию она скрыта. Чтобы вкладка отображалась, щелкните правой кнопкой мыши на ленте и в контекстном меню выберите команду Настройка ленты . Откроется диалоговое окно Параметры Word , в котором в группе Основные вкладки нужно установить флажок напротив элемента Разработчик . Нажмите кнопку OK, и на ленте добавится названная вкладка.

Установите курсор туда, где нужно вставить элемент управления. Перейдите на ленте на вкладку Разработчик и нажмите кнопку Элемент управления «раскрывающийся список». В указанное место будет вставлен этот элемент управления.
На вкладке Разработчик нажмите кнопку Свойства элемента управления. Откроется диалоговое окно .
В диалоговом окне Свойства элемента управления содержимым нажмите кнопку Добавить . Откроется дочернее окно Добавить вариант .
В диалоговом окне Добавить вариант в поле Краткое имя введите тот текст, который вы хотите видеть как первый из вариантов выбора в списке. Нажмите кнопку OK . Значение будет добавлено в список.
Таким же образом внесите в список остальные варианты. В качестве последнего из них введите просто один пробел - дальше поясню, зачем.
Добавив в список все варианты, нажмите в диалоговом окне Свойства элемента управления содержимым кнопку OK. Диалоговое окно закроется. Раскрывающийся список готов!

Как он работает? Когда в документе вы щелкаете кнопкой мыши на таком элементе, раскрывается список. Вы выбираете из него подходящий вариант, и это значение отображается на месте поля. Выбирать готовые варианты на порядок быстрее, чем что-то вписывать или даже стирать. Если вдруг вы обнаружили совсем казуистическую картину, и среди заготовок подходящего описания нет, выберите «пустой» вариант (пробел). Правее поля вписывайте, что хотите - вот для этого и нужен вариант, состоящий из одного пробела.

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

В электронных таблицах Excel тоже предусмотрено нечто подобное. Только там ячейки с выбором одного из возможных значений проще создавать с помощью проверки данных - необходимые инструменты расположены на вкладке Данные в группе Работа с данными . Используя в шаблоне документа элементы управления, мы создаем простейшую форму. Однако можно пойти дальше. Кроме приведенных в качестве примера полей, в документах и таблицах Microsoft Office предусмотрены и другие элементы управления: кнопки, флажки, переключатели, полосы прокрутки. Мы привыкли видеть их в диалоговых окнах, но такие элементы можно встраивать непосредственно в документ.

Смысл работы с формой в том, что флажки устанавливаются, кнопки нажимаются, что-то, возможно, вписывается в поля ввода, а в результате формируются текст документа или содержимое таблицы. В таких «автоматизированных документах» обработкой действий пользователя занимаются макросы (макрокоманды).

Макрос - короткая простая программа, написанная на языке Visual Basic for Applications (VBA). В приложениях Microsoft Office поддержка этого языка заложена изначально, и есть все необходимые инструменты. Например, для начала макрос можно просто записать, не вникая в его внутреннее устройство. Схематично:

На вкладке Разработчик в группе Код нажмите кнопку Записать макрос . Ваши действия с таблицей или документом начинают отслеживаться.
Сразу же в открывшемся диалоговом окне предлагается дать имя макросу и назначить сочетание клавиш, которое в дальнейшем будет запускать этот макрос на выполнение.
Последовательно выполняйте действия, которые вы хотите включить в макрос. Приложение Office запоминает их в виде последовательности команд, ведь в VBA любой операции с текстом или таблицами соответствует определенный код.
Выполнив все необходимые операции, снова нажмите кнопку Записать макрос .

Макрокоманда будет сохранена в шаблоне или документе. Теперь, если вы нажмете сочетание клавиш, заданное на втором шаге, Excel или Word выполнят эту макрокоманду. Все записанные в ней действия программа повторит автоматически.

При необходимости содержимое макрокоманд можно просмотреть, отредактировать и дополнить в редакторе Visual Basic. Он является обязательной частью пакета Microsoft Office. Обычно только что записанный макрос - линейная последовательность команд. При редактировании в макрокоманды часто вставляют логические функции вида «если… то», «и», «или» и т. д. Выполняя их, макрокоманда проверяет и сравнивает значения разных ячеек, положение элементов управления, и в зависимости от этого строит дальнейшее поведение.

Любой макрос легко связать с элементами управления формы, например, с нажатием кнопки или выбором определенного значения в списке. Например, при создании элемента Кнопка сразу же предлагается назначить ему макрокоманду. Многим нужно бывает вставлять в документы «сумму прописью» или «число прописью». Среди стандартных инструментов Office такая функция отсутствует, однако задача легко решается с помощью макроса. Это одно из любимых упражнений для тех, кто начинает осваивать Visual Basic. В Интернете вы найдете тысячи примеров готовых макрокоманд, а также подробные разборы того, как написать подобный макрос самостоятельно.

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

Средства VBA с самого начала задуманы как «программирование для простых пользователей». Много интересных примеров и приемов работы с Word и Excel приводится на сайтах ladyoffice.ru, www.excel2010.ru, excelexpert.ru и др. И, разумеется, в первую очередь полезно обратиться к справочной системе Microsoft Office и разделам официального сайта Microsoft, посвященным работе с этим пакетом.

Зная о том, на что в принципе способны Word и Excel, вы можете поставить задачу тому, кто разработает «умные» шаблоны легко и с удовольствием. Желающие заняться подобным программированием есть везде. За разработку «малой автоматизации» охотно берутся студенты, любые люди, для которых программирование является хобби. Удачное решение - озадачить знакомых школьников! Им - интересные, притом реальные, темы для рефератов по информатике, а вам - полезные инструменты для работы.

При работе с Excel иногда приходиться налаживать взаимодействие программного кода с другими приложениями офисного пакета или вне него, например с Internet Explorer. Так сказать связывать. Для примера возьмем Word, хотя и IE вставим немного.

Начнем с понятия о позднем и раннем связывании (привязке).

Управлять Word из других приложений позволяет сервер автоматизации Word. Такой же сервер есть и у других офисных приложений (и не только офисных). В данном случае, сервером будет считаться Word, а клиентом Excel. Из него будут посылаться запросы на выполнение некоторого кода, а сервер автоматизации Word будет обрабатывать эти запросы. Это теоретически означает возможность написания программного кода в Excel, который будет выполняться в Word и использовать встроенные функции Word. Для использования такой возможности необходимо указать ссылку на библиотеку, функционал которой собираемся использовать. В данном случае Word. Это можно сделать двумя путями: указать библиотеку явно, до исполнения программного кода и во время исполнения программного кода.

Первый способ называется раннее связывание . В меню Tools-References редактора vba выберите Microsoft Word 12.0 Object Library . В других версиях офисных пакетов, как вы понимаете, .

При раннем связывании новый экземпляр класса Word будет создаваться так:

Dim WApp As Word.Application Set WApp = New Word.Application

Второй способ называется позднее связывание . При этом нет необходимости указывать ссылку на библиотеку. Создается экземпляр класса (объект) и возвращается ссылка на него используя функцию CreateObject .

Синтаксис CreateObject:
CreateObject(класс,[имясервера])

класс - обязательный параметр. Тип данных Variant(String) . Имя приложения и класс, на основе которого создается объект. Имеет синтаксис имяприложения.названиекласса
имясервера - необязательный параметр. Имя компьютера в сети, где объект будет создан. Если параметр не указан (пустая строка), объект будет создан на локальной машине.

Примеры создания объекта при позднем связывании:

Создает новый объект Word.Application

Dim WApp As Object Set WApp = CreateObject("Word.Application")

Создает новый объект Word.Application и делает его видимым.
Dim WApp As Object Set WApp = CreateObject("Word.Application") WApp.Visible = True

Создает объект на удаленной машине. Для этой операции необходимо иметь соответствующие права доступа.
Dim WApp As Object Set WApp = CreateObject("Word.Application", "192.168.1.4")

При наличии нескольких установленных офисных продуктов различных версий в системе, можно указать, на основе какой версии создать объект. Однако если указанной версии на компьютере не окажется, произойдет ошибка.

Dim WApp As Object Set WApp = CreateObject("Word.Application.12")

Замечания: создаваемый объект Word.Application невидимый. Это ускоряет работу с ним, но в случае возникновения ошибки, он остается в памяти. Используйте WApp.Visible = True в обработчике ошибок. Каждый раз при использовании функции CreateObject будет создан новый объект.

Если объект приложения уже создан, то использовать функцию CreateObject не стоит. Вместо нее целесообразно применять функции GetObject, которая просто возвращает ссылку на объект.

Синтаксис GetObject:
GetObject([путь][,класс])

путь - необязательный параметр. Тип данных Variant(String) . Полный путь к файлу, связанному с объектом.
класс - Не обязательный параметр. Тип данных Variant(String) . Строка, указывающая, на какой тип объекта будет сделана ссылка. Если параметр путь не указан, тот параметр обязателен.

Dim WApp As Object Set WApp = GetObject(, "Word.Application") Debug.Print WApp.Documents.Count

Dim WDoc As Object Set WDoc = GetObject(ThisWorkbook.Path & "\Pacienti.txt") Debug.Print WDoc.Sentences.Count

Вот пример с перехватом ошибки отсутствия открытого файла:
Dim WDoc As Object On Error GoTo cvv_Error Set WDoc = GetObject(ThisWorkbook.Path & "\Pacienti.txt") Debug.Print WDoc.Sentences.Count On Error GoTo 0 Exit Sub cvv_Error: If Err.Number = 432 Then MsgBox "Файл " & ThisWorkbook.Path & "\Pacienti.txt" & " не открыт..."

Вот пример позднего связывания с Internet Explorer:
Dim IE As Object Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True

Ну а для раннего связывания с этим браузером, необходимо дать ссылку на библиотеку Microsoft Internet Controls . Ну а если планируется работать с интернет страницами (разбор структуры и данных), то и Microsoft HTML Object Library .

У позднего связывания только одно преимущество: ссылка на библиотеку не слетит никогда (если приложение зарегистрировано в системе). Используйте этот способ, если не уверены, что у конечного пользователя версия офисного пакета совпадет с вашей. В любом другом случае лучше использовать раннее связывание, и на это две причины:
1. Объектная модель приложения доступна в браузере объектов Excel.
2. Быстродействие не страдает, и работают все функции.

Вместо послесловия:
В этой маленькой статье я рассказал о способах подключения к объектной модели приложения. Как ее использовать – огромная, требующая отдельных статей тема. Конечно, использовать приложения не зная их объектной модели не получится. Используйте автоматизацию с умом, без лишних выдумок. Видел я как-то приложение в Excel, которое, чтобы вставить данные из таблицы Access на лист, создавало экземпляр Access, открывало файл базы данных, затем из этого экземпляра копировало таблицу на лист, затем закрывало экземпляр. Хотя проще и быстрее было использовать доступ к данным при помощи ADO или даже DAO…

Автозаполнение

Функция Автозаполнение позволяет заполнять данными область ячеек по определенным правилам. Excel осуществляет поиск правила заполнения введенных данных для того, чтобы определить значения пустых ячеек. Если вводится одно начальное значение образца заполнения, то выделяется одна ячейка, если список с интервалом изменения данных, то необходимо выделить две ячейки,заполненные соответствующими данными.

Введите данные в начальную ячейку или смежные ячейки (если задается правило заполнения).
Выберите ячейку или ячейки, в которых установлено правило заполнения.
Установите курсор мыши на Маркере заполнения (рис. 84), маленьком квадратике в правом нижнем углу активной ячейки.
Перетащите мышью Маркер заполнения (по горизонтали или по вертикали) до последней ячейки, которую хотите заполнить по образцу.
Отпустите кнопку мыши.

Рис. 84 . Маркер заполнения активной ячейки

Есть один полезный прием автозаполнения - это перетаскивание маркера заполнения при нажатой правой клавише мыши. При этом появляется контекстное меню, которое поможет выбрать способ автозаполнения (рис. 85).

Рис. 85 . Контекстное меню автозаполнения

Упражнение

Для каждой группы данных в зависимости от правила заполнения требуется ввести данные в одну или две смежные ячейки. Используйте автозаполнение для ввода следующих данных (табл. 21).

Таблица 21 . Примеры автозаполнения.

Начальное значение	Ряды, полученные с помощью автозаполнения
среда	четверг	пятница	суббота	воскресенье
Январь	Февраль	Март	Апрель	Май
Квартал 1	Квартал 2	Квартал 3	Квартал 4	Квартал 1
1-я группа	2-я группа	3-я группа	4-я группа	5-я группа
07.окт	08.окт	09.окт	10.окт	11.окт
11.01.00	12.01.00	13.01.00	14.01.00	15.01.00

Для удобства работы в Excel существуют стандартные списки, содержащие названия дней недели и месяцев, доступные при выборе команды Параметры в меню Сервис (закладка Списки), а также списки, создаваемые пользователем (рис. 86).

Рис. 86 . Создание пользовательского списка

Стандартные списки в отличие от пользовательских нельзя удалить или отредактировать. Для создания или изменения пользовательского списка автозаполнения выполните следующие действия:

Выберите команду Параметры в меню Сервис (Tools › Options), а затем закладку Списки (List).
Укажите Новый список (New) в поле Списки (List).
Введите элементы списка или сделайте изменения в поле Элементы списка и нажмите кнопку Добавить (Add), затем кнопку ОК .

Для удаления списка выделите его и нажмите кнопку Удалить (Delete).

Последнее десятилетие компьютер в бухгалтерии стал просто незаменимым инструментом. При этом его применение разнопланово. В первую очередь это, конечно, использование бухгалтерской программы. На сегодняшний день разработано довольно много программных средств, как специализированных («1С», «Инфо-Бухгалтер», «БЭСТ» и т. д.), так и универсальных, подобно Microsoft Office. На работе, да и в быту часто приходится делать массу различных расчётов, вести многострочные таблицы с числовой и текстовой информацией, проделывая с данными всяческие вычисления, выводя на печать варианты. Для решения ряда экономических и финансовых задач целесообразно использовать многочисленные возможности электронных таблиц. Рассмотрим в этой связи вычислительные функции MS Excel.

Владимир СЕРОВ, к. п. н., Ольга ТИТОВА

Источник: Журнал "Бухгалтер и Компьютер" №4 2004г.

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

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

Все операторы делятся на несколько групп (см. таблицу).

ОПЕРАТОР	ЗНАЧЕНИЕ	ПРИМЕР
АРИФМЕТИЧЕСКИЕ ОПЕРАТОРЫ
+ (знак плюс)	Сложение	=А1+В2
- (знак минус)	Вычитание Унарный минус	=А1-В2 =-В2
/(косая черта)	Деление	=А1/В2
*(звёздочка)	Умножение	= А1*В2
% (знак процента)	Процент	=20%
^ (крышка)	Возведение в степень	= 5^3 (5 в 3-й степени)
ОПЕРАТОРЫ СРАВНЕНИЯ
=	Равно	=ЕСЛИ(А1=В2;"Да";"Нет")
>	Больше	=ЕСЛИ(А1>В2;А1;В2)
<	Меньше	=ЕСЛИ(АКВ2;В2;А1)
>= <=	Больше или равно Меньше или равно	=ЕСЛИ(А1>=В2;А1;В2) =ЕСЛИ(АК=В2;В2;А1)
<>	Не равно	=ЕСЛИ(А1 <>В2;"Не равны")
ТЕКСТОВЫЙ ОПЕРАТОР
&(амперсанд)	Объединение последовательностей символов в одну последовательность символов	= "Значение ячейки В2 равняется: "&В2
АДРЕСНЫЕ ОПЕРАТОРЫ
Диапазон(двоеточие)	Ссылка на все ячейки между границами диапазона включительно	=СУММ(А1:В2)
Объединение (точка с запятой)	Ссылка на объединение ячеек диапазонов	=СУММ(А1:В2;СЗ;D4:Е5)
Пересечение(пробел)	Ссылка на общие ячейки диапазонов	=CУMM(A1:B2C3D4:E5)

Арифметические операторы используются для обозначения основных математических операций над числами. Результатом выполнения арифметической операции всегда является число. Операторы сравнения используются для обозначения операций сравнения двух чисел. Результатом выполнения операции сравнения является логическое значение ИСТИНА или ЛОЖЬ.

Для выполнения вычислений в программе Excel используются формулы. С помощью формул можно, например, складывать, умножать и сравнивать данные таблиц, т. е. формулами следует пользоваться, когда необходимо ввести в ячейку листа (автоматически рассчитать) вычисляемое значение. Ввод формулы начинается с символа “=” (знак равенства). Именно этим знаком отличается ввод формул от ввода текста или простого числового значения.

При вводе формул можно применять обычные числовые и текстовые значения. Напомним, что числовые значения могут содержать только цифры от 0 до 9 и специальные символы: (плюс, минус, косая черта, круглые скобки, точка, запятая, знаки процента и доллара). Текстовые значения могут содержать любые символы. Необходимо отметить, что используемые в формулах текстовые выражения должны заключаться в двойные кавычки, например “константа1”. Кроме того, в формулах можно использовать ссылки на ячейки (в том числе в виде имён) и многочисленные функции, которые соединяются между собой операторами.

Ссылки представляют собой включаемые в формулу адреса ячеек или диапазоны ячеек. Ссылки на ячейки задаются обычным образом, т. е. в виде A1, B1, C1. Например, для того, чтобы получить в ячейке A3 сумму ячеек A1 и A2, в неё достаточно ввести формулу =A1+A2 (рис. 1).

При вводе формулы ссылки на ячейки могут символ за символом непосредственно набираться с клавиатуры на латинице, но чаще намного проще указывать их с помощью мыши. Например, чтобы ввести формулу =A1+B2, нужно выполнить следующие действия:

Выделить ячейку, в которую нужно ввести формулу;

Начать ввод формулы, нажав клавишу “=” (равно);

Щёлкнуть мышью на ячейке A1;

Ввести символ “+”;

Щёлкнуть мышью на ячейке B2;

Закончить ввод формулы, нажав клавишу Enter.

Диапазон ячеек представляет собой некоторую прямоугольную область рабочего листа и однозначно определяется адресами ячеек, расположенными в противоположных углах диапазона. Разделённые символом “:” (двоеточие), эти две координаты составляют адрес диапазона. Например, чтобы получить сумму значений ячеек диапазона C3:D7, используйте формулу =СУММ(C3:D7).

В частном случае, когда диапазон состоит целиком из нескольких столбцов, например от В до D, его адрес записывается в виде В:D. Аналогично если диапазон целиком состоит из строк с 6-й по 15-ю, то он имеет адрес 6:15. Кроме того, при записи формул можно использовать объединение нескольких диапазонов или ячеек, разделяя их символом “;” (точка с запятой), например C3:D7; E5;F3:G7.

Редактирование уже введённой формулы можно сделать несколькими способами:

Двойным щелчком левой кнопки мыши на ячейке, чтобы корректировать формулу непосредственно в этой ячейке;

Выбрать ячейку и нажать клавишу F2 (рис. 2);

Выбрать ячейку, переместив курсор в строку формул, щёлкнуть левой кнопки мыши.

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

При заполнении таблицы принято задавать расчётные формулы только для “первой” (начальной) строки или “первого” (начального) столбца, а остальную часть таблицы заполнять формулами с помощью режимов копирования или заполнения. Прекрасный результат даёт применение автокопирования формул с помощью автозаполнителя.

Напомним, как правильно осуществить режим копирования. Здесь могут быть различные варианты (и проблемы тоже).

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

Например, вам нужно складывать построчно значения столбцов А и В (рис. 8) и поместить результат в столбец С. Если вы копируете формулу =А2+В2 из ячейки С2 в ячейку С3* (и далее вниз по С), то Excel сам преобразует адреса формулы соответственно как =А3+В3 (и т. д.). Но если вам нужно поместить формулу, скажем, из С2 в ячейку D4, то формула уже будет выглядеть как =В4+С4 (вместо нужной =А4+В4), и соответственно результат вычислений будет неправильным! Иными словами, обратите особое внимание на процесс копирования и при необходимости вручную корректируйте формулы. Кстати, само копирование из С2 в С3 делается следующим образом:

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

2) нажимаем кнопку “Копировать” на панели инструментов, или клавиши Ctrl+C, или выбираем в меню “Правка ® Копировать”;

3) выбираем ячейку С3, в которую будем копировать формулу;

4) нажимаем кнопку “Вставить” на панели инструментов, или клавиши Ctrl+V, или через меню “Правка ® Вставить” с нажатием Enter.

Рассмотрим режим автозаполнения. Если необходимо перенести (скопировать) формулу в несколько ячеек (например, в С3:С5) вниз по столбцу, то это удобнее и проще сделать так: повторить предыдущую последовательность действий до пункта 3 выбора ячейки С3, далее курсор мыши подвести к начальной ячейке диапазона (С3), нажать левую кнопку мыши и, не отпуская её, протащить ниже до требуемой последней ячейки диапазона. В нашем случае это ячейка С5. Затем отпускаем левую кнопку мыши, переводим курсор на кнопку “Вставить” панели инструментов и нажимаем её, а потом Enter. Excel сам преобразует адреса формул в выделенном нами диапазоне по соответствующим адресам строк.

Иногда возникает необходимость скопировать только числовое значение ячейки (диапазона ячеек). Для этого нужно проделать следующее:

1) выбрать ячейку (диапазон), из которой нужно скопировать данные;

2) нажать кнопку “Копировать” на панели инструментов или выбрать в меню “Правка ® Копировать”;

3) выбрать ячейку (левую верхнюю нового диапазона), в которую будут копироваться данные;

4) выбрать в меню “Правка ® Специальная вставка” и нажать Enter.

При копировании формул компьютер сразу производит расчёты по ним, выдавая, таким образом, быстрый и наглядный результат.

:: Функции в Excel

Функции в Excel в значительной степени облегчают проведение расчётов и взаимодействие с электронными таблицами. Наиболее часто применяется функция суммирования значений ячеек. Напомним, что она имеет название СУММ, а в качестве аргументов служат диапазоны суммируемых чисел.

В таблице часто требуется вычислить итоговую сумму по столбцу или строке. Для этого Excel предлагает функцию автоматической суммы, выполняемой нажатием кнопки (“Автосумма”) на панели инструментов.

Если мы введём ряд чисел, установим курсор под ними и выполним двойной щелчок мышью по значку автосуммирования, то произойдёт сложение чисел (рис. 3).

В последней версии программы справа от значка автосуммирования имеется кнопка списка, позволяющая произвести вместо суммирования ряд часто используемых операций (рис. 4).

:: Автоматические вычисления

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

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

Именно поэтому начиная с версии Excel 7.0 в электронную таблицу была встроена функция автовычисления. Теперь в электронных таблицах Excel имеется возможность быстрого выполнения некоторых математических операций в автоматическом режиме.

Чтобы увидеть результат промежуточного суммирования, достаточно просто выделить необходимые ячейки. Этот результат отражается и в строке состояния в нижней части экрана. Если сумма там не появилась, подведите курсор к строке состояния на нижней части рамки, щёлкните правой кнопкой мыши и в выпавшем меню у строки Сумма нажмите левую кнопку мыши. Более того, в этом меню на строке состояния вы можете выбрать различные варианты рассчитываемых результатов: сумму, среднее арифметическое значение, количество элементов или минимальное значение в выделенном диапазоне.

Для примера рассчитаем с помощью этой функции сумму значений для диапазона В3:В9. Выделите числа в диапазоне ячеек В3:В9. Обратите внимание, что в строке состояния, расположенной внизу рабочего окна, появилась надпись Сумма=X, где X — число, равное сумме выделенных чисел диапазона (рис. 5).

Как видим, результаты обычного расчёта по формуле в ячейке В10 и автовычисления совпадают.

:: Мастер функций

Помимо функции суммирования Excel позволяет обрабатывать данные с помощью других функций. Любую из них можно ввести непосредственно в строке формул с помощью клавиатуры, однако для упрощения ввода и снижения количества ошибок в Excel имеется “Мастер функций” (рис. 6).

Вызвать окно диалога “Мастера” можно с помощью команды “Вставка ® Функция”, комбинацией клавиш Shift+F3 или кнопкой на стандартной панели инструментов.

Первый диалог “Мастера функций” организован по тематическому принципу. Выбрав категорию, в нижнем окне мы увидим список имён функций, содержащихся в данной группе. Например, функцию СУММ () вы сможете отыскать в группе “Математические”, а в группе “Дата и время” находятся функции ЧИСЛО(), МЕСЯЦ(), ГОД(), СЕГОДНЯ().

Кроме того, для ускорения выбора функций Excel “помнит” имена 10 недавно использованных функций в соответствующей группе. Обратите внимание, что в нижней части окна отображается краткая справка о назначении функции и её аргументах. Если вы нажмёте кнопку “Справка” в нижней части диалогового окна, то Excel откроет соответствующий раздел справочной системы.

Предположим, что необходимо произвести расчёт амортизации имущества. В этом случае следует в зоне поиска функции ввести слово “амортизация”. Программа подберёт все функции по амортизации (рис. 7).

После заполнения соответствующих полей функции будет произведён расчёт амортизации имущества.

Нередко нужно произвести сложение чисел, удовлетворяющих какому-либо условию. В этом случае следует использовать функцию СУММЕСЛИ. Рассмотрим конкретный пример. Допустим необходимо подсчитать сумму комиссионных, если стоимость имущества превышает 75 000 руб. Для этого используем данные таблицы зависимости комиссионных от стоимости имущества (рис. 8).

Наши действия в этом случае таковы. Устанавливаем курсор в ячейку В6, кнопкой запускаем “Мастера функций”, в категории “Математические” выбираем функцию СУММЕСЛИ, задаём параметры, как на рис. 9.

Обратите внимание, что в качестве диапазона для проверки условия мы выбираем интервал ячеек А2:А6 (стоимость имущества), а в качестве диапазона суммирования — В2:В6 (комиссионные), при этом условие имеет вид (>75000). Результат нашего расчёта составит 27 000 руб.

:: Дадим имя ячейке

Для удобства работы в Excel имеется возможность присваивания имён отдельным ячейкам или диапазонам, которые затем можно использовать в формулах наравне с обычными адресами. Чтобы быстро присвоить имя ячейке, выделите её, установите указатель на поле имени в левой части строки формул, нажмите кнопку мыши и введите название.

Присваивая имена, необходимо помнить, что они могут состоять из букв (в том числе русского алфавита), цифр, точек и символов подчёркивания. Первый знак в имени должен быть буквой или знаком подчёркивания. Имена не могут иметь такой же вид, как и ссылки на ячейки, например Z$100 или R1C1. В имени может быть больше одного слова, но пробелы недопустимы. В качестве разделителей слов могут быть использованы знаки подчёркивания и точки, например Налог_на_продажи или Первый.Квартал. Имя может содержать до 255 знаков. При этом прописные и строчные буквы воспринимаются одинаково.

Чтобы вставить имя в формулу, можно воспользоваться командой “Вставка ® Имя ® Вставить”, выбрав нужное имя в списке имён.

Полезно помнить, что имена в Excel используются как абсолютные ссылки, т. е. являются разновидностью абсолютной адресации, что удобно при копировании формул.

Имена в Excel можно определять не только для отдельных ячеек, но и для диапазонов (в том числе несмежных). Для присвоения имени достаточно выделить диапазон, а затем ввести название в поле имени. Кроме того, для задания имён диапазонов, содержащих заголовки, удобно использовать специальную команду “Создать” в меню “Вставка ® Имя”.

Чтобы удалить имя, выберите его в списке и нажмите кнопку “Удалить”.

При создании формулы, ссылающейся на данные из листа, можно использовать заголовки строк и столбцов для указания данных. Например, если присвоить значениям колонки имя названия колонки (рис. 10),

то для подсчёта общей суммы для столбца “Комиссионные” используется формула =СУММ(Комиссионные) (рис. 11).

:: Дополнительные возможности Excel — шаблоны

В состав MS Excel входит набор шаблонов — таблицы Excel, которые предназначены для анализа хозяйственной деятельности предприятия, составления счёта, наряда и даже для учёта личного бюджета. Они могут быть использованы для автоматизации решения часто встречающихся задач. Так, можно создавать документы на основе шаблонов “Авансовый отчёт”, “Счёт”, “Заказ”, которые содержат бланки используемых в хозяйственной деятельности документов. Эти бланки по своему внешнему виду и при печати не отличаются от стандартных, и единственное, что нужно сделать для получения документа, — заполнить его поля.

Для создания документа на основе шаблона выполните команду “Создать” из меню “Файл”, затем выберите необходимый шаблон на вкладке “Решения” (рис. 12).

Шаблоны копируются на диск при обычной установке Excel. Если шаблоны не отображаются в окне диалога “Создание документа”, запустите программу установки Excel и установите шаблоны. Чтобы получить подробные сведения об установке шаблонов, посмотрите раздел “Установка компонентов Microsoft Office” в справке Excel.

Например, для создания ряда финансовых документов выберите шаблон “Финансовые шаблоны” (рис. 13).

Эта группа шаблонов содержит формы следующих документов:

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

Выберите нужный бланк для заполнения, а затем введите в него все необходимые реквизиты и распечатайте его. При желании документ можно сохранить как обычную Excel-таблицу.

Excel позволяет пользователю самому создавать шаблоны документов, а также редактировать уже имеющиеся.

Однако бланки документов со временем могут измениться, и тогда существующий шаблон станет непригодным. Кроме того, в шаблоны, которые имеются в поставке Excel, было бы неплохо заранее внести такие постоянные сведения, как данные о вашей организации, руководителе. Наконец, может возникнуть необходимость создать собственный шаблон: например, в плановом отделе скорее всего потребуются шаблоны для оформления смет и калькуляций, а в бухгалтерии — бланк счёта с фирменной эмблемой вашей организации.

Для таких случаев в Excel, как и во многих других программах, которые работают с электронными документами, предусмотрена возможность создания и редактирования шаблонов для часто используемых документов. Шаблон Excel — это специальная рабочая книга, которую можно применять как образец при создании других рабочих книг того же типа. В отличие от обычной книги Excel, имеющей расширение *.xls, файл шаблона имеет расширение *.xlt.

При создании документа на основе шаблона программа Excel автоматически создаёт его рабочую копию с расширением *.xls, добавляя в конец имени документа порядковый номер. Шаблон-оригинал при этом остаётся нетронутым и может быть впоследствии использован повторно.

Для автоматического ввода даты можно воспользоваться следующим способом: в ячейку даты введите функцию СЕГОДНЯ, после этого в ней отобразится текущий день месяца, месяц и год соответственно.

Разумеется, все рассмотренные действия над шаблонами вы можете использовать и при работе с обычными книгами Excel.

Для автоматизации трудоемких или часто повторяющихся задач в Excel используются макросы. Макрос - это последовательность команд и действий, сохраненная под одним именем. Макрос можно создать двумя способами: 1) записать действия автоматически; 2) разработать процедуру в редакторе VBA.

Можно записывать макрос с абсолютными ссылками и с относительными ссылками. Макрос с относительными ссылками выполняется, начиная с ячейки, которая была текущей перед началом выполнения макроса. Макрос с абсолютными ссылками выполняется в одних и тех же ячейках, которые использовались во время записи макроса.

Для работы с макросами и элементами управления используется закладка Разработчик (рис. 7.1). Если такой закладки на ленте инструментов нет, ее нужно включить следующим образом:

Нажать кнопку Office , щелкнуть по кнопке ;

Установить флажок Показывать вкладку “Разработчик” на ленте .

Запись макроса начинается после нажатия кнопки Запись макроса. Способ записи определяется состоянием кнопки Относительные ссылки . Если кнопку включить, то будет записываться макрос с относительными ссылками, если выключить - с абсолютными.

Рис. 7.1. Закладка Разработчик ленты инструментов

Выполнение макроса можно задать комбинации клавиш, кнопке на панели инструментов, элементу управления формы или графическому объекту на рабочем листе.

Удаление макроса производится в диалоге Макросы , который открывается кнопкой Макросы на закладке Разработчик .Нужно выбрать в списке требуемый макрос и нажать кнопку Удалить . В этом же диалоге можно запустить выбранный макрос на выполнение, если не задан другой способ выполнения макроса.

Для автоматизации заполнения шаблонов в Excel используются элементы управления формы и элементы ActiveX. Элемент управления - это графический объект, позволяющий пользователю управлять приложением. Чтобы создать элемент управления, нужно раскрыть кнопку Вставить (рис. 7.1), выбрать требуемый элемент и растянуть мышью до желаемого размера в нужном месте рабочего листа. Когда элемент управления выделен, с помощью кнопки Свойства на закладке Разработчик можно изменять его параметры (рис. 7.6), например, связать с какой-либо ячейкой листа.

Рис. 7.2. Элементы управления формы

1. Группа - рамка, которая используется для объединения переключателей.

2. Кнопка - используется для выполнения назначенного ей макроса.

3. Флажок - если установлен, то в связанной с ним ячейке выводится значение ИСТИНА, если снят - ЛОЖЬ.

4. Переключатель - используется всегда в группе. Когда переключатели объединены в группу, только один из них может установлен. Тогда в ячейке, связанной с этой группой переключателей, выводится порядковый номер выбранного переключателя.

5. Список (а) и Поле со списком (б) - отображают список значений, который нужно предварительно ввести в ячейки, а затем указать диапазон этих ячеек в параметрах элемента управления. Номер выбранного в списке значения помещается в ячейку, связанную со списком.

6. Полоса прокрутки (а) и Счетчик (б) - изменяют значение связанной с ними ячейки. В параметрах элемента управления можно задать диапазон и шаг изменения этого значения.

Пример 7.1. Записать макрос под именем «Первый_день_месяца», который начиная с текущей ячейки выводит даты первых дней шести месяцев, следующих за текущим, и форматирует их так, чтобы название месяца выводилось словом, выравнивание по левому краю, цвет текста - синий, шрифт - Courier New полужирный.Выполнение макроса назначить автофигуре.

Выполнение:

1. Нажать кнопку Запись макроса на закладке Разработчик .

2. В открывшемся диалоге задать имя макроса - Первый_день_месяца , можно задать комбинацию клавиш и затем нажать ОК. При этом включится запись и кнопка Запись макроса будет преобразована в кнопку Остановить запись (рис. 7.3).

Рис. 7.3. Вид фрагмента закладки Разработчик во время записи макроса

4. Проделать действия, которые должен выполнять макрос:

В текущую ячейку ввести формулу, которая будет возвращать дату первого числа месяца, следующего за текущим (текущей является дата 15.05.06): =ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ())+1;1)

Выделить 6 ячеек, начиная с введенной формулы, раскрыть кнопку Заполнить на закладке Главная и выбрать команду Прогрессия… ;

В открывшемся диалоге указать Тип à Даты, Единицы à Месяц.

Открыть диалог Формат ячеек… ;

На закладке Число задать формат ДД ММММ ГГГГ, на закладке Выравнивание à по горизонтали по левому краю, на закладке Шрифт à шрифт - Courier New полужирный, цвет - синий.

5. Нажать кнопку Остановить запись (рис. 7.3).

6. На рабочем листе нарисовать какую-либо автофигуру, выбрав ее на закладке Вставка . В контекстном меню автофигуры выбрать команду Назначить макрос . В открывшемся диалоге указать макрос «Первый_день_месяца» и нажать ОК.

Рис. 7.4. Результат выполнения макроса «Первый_день_месяца»

7. Сделать текущей любую ячейку и проверить работу макроса щелкнув по автофигуре (рис. 7.4).

Пример 7.2. Создать бланк заказ-наряда (рис. 7.5), в котором наименование работы выводится в ячейке В2 с помощью списка, а количество часов в ячейке В3 заполняется с помощью полосы прокрутки.

Рис. 7.5. Создание бланка с элементами управления формы.

Выполнение:

Заполнить ячейки данными как на рис. 7.5, кроме ячеек В2, В3, Е1.
Нарисовать список (рис. 7.2, 5-а) и полосу прокрутки (рис. 7.2, 6-а).
Изменить параметры созданных элементов управления (рис. 7.6). Так как полоса прокрутки связана с ячейкой В3, то щелчок мышью по стрелке полосы прокрутки будет менять значение ячейки на один шаг - 1. Список связан с ячейкой Е1, поэтому при выборе значения «Побелка потолка» в ячейке будет выведен порядковый номер этого значения в списке - 3.

Рис. 7.6. Параметры полосы прокрутки (слева) и списка (справа).

Чтобы вывести в В2 наименование работы, а не ее номер, нужно использовать функцию ВПР, которая в зависимости от номера выбранной из списка работы в ячейке Е1, будет возвращать соответствующее значение из второго столбца таблицы Е2:F5. Таким образом, формула в В2 будет следующей: =ВПР(E1;E2:F5;2).

Замечание . Полосу прокрутки и список можно создать с помощью элементов ActiveX и затем изменить их свойства, как показано на рисунке:

Лабораторная работа № 7

Цель работы: научиться создавать и использовать макросы и элементы управления формы на рабочем листе для автоматизации работы в Excel.

Задания:

I. Записать макросы:

С относительными ссылками. Макрос должен выводить названия месяцев в столбце, начиная с текущей ячейки, со следующими элементами форматирования:

Цвет символов - красный,

Обрамление ячеек - тонкая линия,

Текст выровнен по центру,

Внешнее обрамление столбца - жирная линия.

На панели элементов Формы выбрать элемент Кнопка , нарисовать его на рабочем листе, назвать МЕСЯЦЫ и назначить созданный макрос.

С абсолютными ссылками. Макрос должен очищать весь рабочий лист. На рабочем листе нарисовать любую автофигуру и назначить созданный макрос.

3. Ввести в какую-нибудь ячейку любую дату. Макрос должен, начиная с введенной даты, заполнять 10 ячеек датами по рабочим дням, очищать 10 ячеек двух соседних столбцов и форматировать их так, как показано на рисунке. На рабочем листе нарисовать любую автофигуру и назначить созданный макрос.

II. Создать шаблон для заполнения бланка заказа авиабилетов с элементами управления формы:

1. Заполнить ячейки данными, кроме ячеек С2:С4, С6 и С13, и вставить элементы управления формы:

2. Изменить свойства элементов управления:

2.1. Для поля со списком вывод на печать отключен, объемное затенение включено, список формируется по диапазону I3:I7 , результат помещается в ячейку G3 , т. е. в эту ячейку помещается номер элемента, который был выбран в списке.

2.2. Для счетчика вывод на печать отключен, объемное затенение включено, диапазон изменения от 1 до 10 с шагом 1, результат помещается в ячейку С6 .

2.3. Для переключателей вывод на печать и объемное затенение включены, результат помещается в ячейку G4 . Переключатели объединены в рамку .

2.4. Для кнопки вывод на печать отключен.

3. В ячейках С2:С4 написать формулы (используя функцию ВПР), которые выводят город, время вылета и цену билета в зависимости от выбранного значения списка.

4. В ячейке С13 написать формулу для расчета суммы к оплате в зависимости от количества билетов и типа билета (для льготного - скидка 30%).

5. Снять защиту с ячеек, с которыми связаны элементы управления.

6. Формулы скрыть.

7. Скрыть столбцы, содержащие вспомогательные данные.

8. Создать и назначить кнопке «Печать » макрос, который задает альбомную раскладку и размер бумаги А5 и отображает шаблон в режиме предварительного просмотра.

Контрольные вопросы к теме

1. Какими способами можно автоматизировать заполнение шаблона?

3. Назовите элементы управления и их назначение.

4. Как создать элемент управления на рабочем листе и изменить его параметры?

5. Назовите способы выполнения макроса.

Литература

1. Microsoft Office 2007: все программы пакета: Word, Excel, Access, PowerPoint, Publisher, Outlook, OneNote, InfoPath, Groove / Тихомиров А. Н. [и др.]. - Санкт-Петербург: Наука и техника, 2009. - 599 с.

2. Microsoft Office Excel 2007: [русская версия: перевод с английского] / Кёртис Д. Фрай. - Москва: ЭКОМ, 2009. - 479 с.

3. Excel 2007 для менеджеров и экономистов: логистические, производственные и оптимизационные расчеты / Александр Трусов. - Санкт-Петербург: Питер: Питер Пресс, 2009. – 254 с.

4. Графики, вычисления и анализ данных в Excel 2007 / Серогодский В. В. [и др.]. - Санкт-Петербург: Наука и техника, 2009. - 333 с.

5. Функции в Excel 2007: справочник пользователя / Минько А. А.. - Москва: Эксмо, 2008. - 480 с.

6. Экономическая информатика / под ред. П.В. Конюховского и Д.Н. Колесова. – СПб: Питер, 2001. – 560 с.

7. Гарнаев А.Ю. Excel, VBA, Internet в экономике и финансах. – СПб.: BHV–Петербург, 2001. – 816 с.

8. Додж М., Кината К., Стинсон К. Эффективная работа с Excel 7.0: пер. с англ. – СПб: Питер, 1996. – 1031 с.

9. Обработка и анализ экономической информации в Microsoft Excel: Пособие для студентов экономических специальностей / Д.П. Подкопаев, В.И. Яшкин. – Мн.: Издательство БГУ, 2001. – 50 с.

10. Бизнес-анализ с помощью Microsoft Excel / Карлберг Конрад. – 2-е изд. – М.: Вильямс, 2003. – 446 с.

11. Использование макросов в Excel / С. Роман. – 2-е изд. – СПб: Питер, 2004. – 507 с.

12. Гетц К., Гилберт М. Программирование на Visual Basic и VBA. Руководство разработчика: пер с англ. – К.: Издательская группа BHV, 2001. – 912 с.