Для автоматизации работы приложений часто используются макросы. Любой макрос — это последовательность действий, записанная под определенным именем. Если при работе с Microsoft Excel возникает необходимость несколько раз выполнить одну и ту же последовательность операций (например, сложное форматирование текущей ячейки или добавление новой строки с заполнением некоторых ее ячеек формулами), то можно записать эти действия, а затем воспроизводить их столько раз, сколько потребуется.

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

Вкладка Разработчик , позволяющая записывать макросы, по умолчанию скрыта. Для того чтобы ее показать, необходимо выбрать Office → Параметры Excel → Основные и использовать опцию Показывать вкладку «Разработчик» на ленте . После установки этого флажка на ленте появится вкладка Разработчик .

Запись макросов

Для того чтобы записать макрос, необходимо:

1. Детально представить ту последовательность действий, которую нужно выполнять (во избежание возможных ошибок, лишних или неправильных действий).
2. Выбрать элемент, к которому будут применяться действия, записываемые в макрос (установить курсор в пределах ячейки и пр.).
3. Вызвать диалоговое окно Запись макроса одним из двух способов:
   • нажать кнопку Запись макроса на панели Код вкладки Разработчик;
   • нажать кнопку Запись макроса в левой части строки состояния Excel.
4. Ввести в поле Имя макроса название создаваемого макроса. При этом первым символом имени макроса должна быть буква. Остальные символы могут быть как буквами, так и цифрами или знаком подчеркивания. В имени не допускаются пробелы; в качестве разделителей слов рекомендуется использовать знаки подчеркивания.
5. В выпадающем списке Сохранить в указать, где будет сохранен данный макрос. Возможные варианты:
   • Личная книга макросов — макрос сохраняется в специальный файл, который будет доступен для всех документов сразу после загрузки Excel;
   • Новая книга — будет создана новая книга, и макрос будет сохранен в ней. Этот макрос будет доступен только в том случае, если содержащая его книга будет открыта;
   • Эта книга — макрос будет сохранен в текущей книге. Обычно такие макросы необходимы, когда их использование ограничено мелкой автоматизацией в текущей книге. Тем не менее, макрос будет доступен из других документов, если они будут открыты параллельно.
6. Если необходимо, чтобы макрос вызывался комбинацией клавиш, ввести букву или цифру в поле Сочетание клавиш .

Если вводится буква, то важен как регистр, так и раскладка. Например, если вводится буква д (малая русская), то комбинация клавиш для этого макроса будет Ctrl+д , но не Ctrl+I (малая английская L, которая находится на той же клавише).

7. Нажать кнопку OK , затем кнопку Запись макроса на панели Код вкладки Разработчик , которая в строке состояния превратится в кнопку Остановить запись , останавливающую запись макроса.
8. Выполнить последовательность действий, которые будут записаны в макрос.

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

9. Завершить запись макроса нажатием кнопки Остановить запись . По окончании записи макроса он появляется в списке макросов иод своим именем.

Выполнение макросов

Для выполнения макроса можно воспользоваться диалоговым окном Макрос . Чтобы отобразить это окно, можно нажать комбинацию клавиш Alt+F8 либо воспользоваться кнопкой Макросы на панели Код . Далее в этом окне необходимо выбрать требуемый макрос и нажать кнопку Выполнить . Второй возможностью выполнить макрос является комбинация клавиш, выбранная на этапе создания макроса. Кроме того, в Excel предусмотрена возможность вызова макроса при помощи кнопки па панели быстрого доступа.

Создание кнопки макроса на панели быстрого доступа

Для создания кнопки на панели быстрого доступа нужно:

1. Вызвать диалоговое окно Параметры Excel и перейти на вкладку Настройка .
2. В выпадающем списке Выбрать команды из выбрать пункт Макросы .
3. Выбрать нужный макрос из списка макросов и нажать кнопку Добавить — он скопируется в правое окно и станет доступен в виде кнопки на панели быстрого доступа.
4. Чтобы для созданной кнопки изменить рисунок и отображаемое имя, следует воспользоваться командой Изменить .
5. Нажать кнопку ОК в диалоговом окне Параметры Excel.

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

Удаление макроса

Для удаления макроса следует нажать кнопку Макросы на панели Код или комбинацию клавиш Alt+F8 — появится диалоговое окно Макрос , содержащее список макросов, доступных в открытых книгах. В данном окне необходимо выбрать удаляемый макрос и нажать кнопку Удалить.

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

Редактирование макроса

Созданный макрос на самом деле представляет собой процедуру, записанную на языке программирования VBA. Ее легко можно увидеть в окне редактора Visual Basic Editor. Подробно о VBA и редакторе Visual Basic Editor рассказано в других наших статьях. Сейчас важно отметить то, что макрос записывается в виде обычного текста, а это значит, что при необходимости его легко можно редактировать.

Для входа в режим редактирования макроса нужно:

1. В диалоговом окне Макрос выбрать подлежащий редактированию макрос.
2. Нажать кнопку Изменить — откроется окно Microsoft Visual Basic.

Для примера создадим в текущей книге Excel простейший макрос, который должен применяться к выделенной ячейке и изменять ее форматирование (цвет фона - красный, цвет шрифта — светло-коричневый, Фон 2). Чтобы создать этот макрос, необходимо:

1. Выделить любую ячейку на листе и ввести в нее какой-нибудь текст (чтобы можно было посмотреть на результат работы).
2. Включить запись макроса с помощью кнопки Запись макроса в строке состояния.
3. В диалоговом окне Запись макроса все параметры оставить выбранными по умолчанию и нажать кнопку ОК .
4. На вкладке Главная (панель Шрифт) выбрать нужные параметры цвета.
5. Остановить запись макроса при помощи кнопки Остановить запись в строке состояния.

Теперь для того чтобы просмотреть код созданного макроса, необходимо вызвать редактор Visual Basic способом, описанным выше, — откроется окно кода для созданного макроса, в котором будет записан следующий код:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

// Макрос форматирования ячейки Макрос1 Sub Макрос1() "Макрос! Макрос With Selection.Interior .Pattern = xlSolid .PatternColorlndex = xlAutomatic .Color = 255 .TintAndShade = 0 .PatternTintAndShade = 0 End With With Selection.Font .ThemeColor = xlThemeColorDark2 .TintAndShade = 0 End With End Sub

// Макрос форматирования ячейки Макрос1 Sub Макрос1() "Макрос! Макрос With Selection.Interior .Pattern = xlSolid .PatternColorlndex = xlAutomatic .Color = 255 .TintAndShade = 0 .PatternTintAndShade = 0 End With With Selection.Font .ThemeColor = xlThemeColorDark2 .TintAndShade = 0 End With End Sub

Чтобы при последовательном обращении к нескольким свойствам одного объекта каждый раз не указывать длинную цепочку типа Selection.Interior.Pattern = xlSolid , используется специальная конструкция языка VBA: With…End With — внутри нес имя объекта можно опустить, указывая лишь точку перед именем свойства.

Для тех, кто знает английский язык, назначение и принцип работы команд в данном коде достаточно очевидны: для объекта Selection.Interior (т.е. для внутренней области выделенной ячейки) выполняется задание значений различным параметрам фона. Основные параметры — Pattern (способ заливки, в данном случае задан xlSolid , т.е. сплошным) и Color (Цвет). Аналогичным образом происходит изменение цвета шрифта (начиная со строки With Selection.Font ).

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

Цели полностью удалить код макроса в редакторе Visual Basic, то макрос также будет полностью удален (это аналогично действию кнопки Удалить диалогового окна Макрос ).

Для переименования макроса достаточно войти в режим его редактирования и в тексте программы исправить заголовок в строке Sub Макрос1() — новое имя автоматически заменит старое в списках макросов. Заметим, что не все замены происходят автоматически. Если с макросом были связаны графические объекты или кнопки, то необходимо сделать для них переназначения.

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

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

При записи макроса записываются все действия, описанные в Visual Basic для приложений (VBA) коде. Эти действия могут включать ввод текста или чисел, нажатие ячеек или команд на ленте или в меню, форматирование ячеек, строк или столбцов, а также импорт данных из внешнего источника, например Microsoft Access. Приложение Visual Basic (VBA) - это подмножество мощного языка программирования Visual Basic, которое входит в большинство приложений Office. Несмотря на то, что VBA обеспечивает возможность автоматизации процессов между приложениями Office, вам не нужно знать код VBA или программное программирование, если это нужно.

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

Разработчик , которая по умолчанию скрыта, поэтому сначала нужно включить ее. Дополнительные сведения см. в статье Отображение вкладки "Разработчик" .

Запись макроса

На вкладке Разработчик щелкните Макросы , чтобы просмотреть макросы, связанные с книгой. Кроме того, можно нажать клавиши ALT+F8 . При этом откроется диалоговое окно Макрос .

Внимание:

		Сведения о параметрах безопасности макросов и их значении.
	Макросы можно запускать различными способами, например с помощью сочетания клавиш, графического объекта, панели быстрого доступа, кнопки или даже при открытии книги.
	С помощью редактора Visual Basic можно изменять макросы, присоединенные к книге.
	назначить макрос . В поле Назначить макроса
	Узнайте, как включать и отключать макросы в файлах Office.
	Нажмите клавиши ALT+F11 .

Работа с записанным кодом в редакторе Visual Basic (VBE)

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

Пример изменения записанного кода можно найти в статье Начало работы с VBA в Excel .

Запись макроса

Перед записью макросов полезно знать следующее:

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

Если вам нужно записать длинную последовательность задач, советуем вместо этого использовать несколько более мелких макросов.

В макросе могут содержаться и задачи, не относящиеся к Excel. Процесс макроса может охватывать прочие приложения Office и другие программы, которые поддерживают Visual Basic для приложений (VBA). Например, вы можете записать макрос, который сначала обновляет таблицу в Excel, а затем открывает Outlook для ее отправки по электронной почте.

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

Перейдите в раздел настройки _гт_ Excel ... панель инструментов _Гт_ ленты _амп_ .

Чтобы записать макрос, следуйте инструкциям ниже.

Работа с макросами, записанными в Excel

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

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

Ниже приведены дополнительные сведения о работе с макросами в Excel.

		Узнайте, как включать и отключать макросы в Excel для Mac.
	Чтобы сэкономить время на выполнении часто повторяющихся задач, можно записать соответствующую последовательность действий в виде макроса. Узнайте, как создавать и выполнять макросы.
	Если книга содержит макрос VBA, который нужно использовать где-либо еще, этот модуль можно скопировать в другую книгу с помощью редактора Microsoft Visual Basic.
Назначение макроса объекту, фигуре или графическому элементу	На листе щелкните правой кнопкой мыши объект, рисунок, фигуру или элемент, которому нужно назначить существующий макрос, а затем выберите команду назначить макрос . В поле Назначить макроса выберите макрос, который вы хотите назначить.
	Вы можете назначить макрос значку и добавить его на панель быстрого доступа или ленту.
	Вы можете назначать макросы формам и элементам ActiveX на листе.
Открытие редактора Visual Basic	На вкладке Разработчик щелкните Visual Basic или выберите Сервис > Макрос > Редактор Visual Basic .
	Узнайте, как найти справку по элементам Visual Basic.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community , попросить помощи в сообществе Answers community , а также предложить новую функцию или улучшение на веб-сайте

Последнее десятилетие компьютер в бухгалтерии стал просто незаменимым инструментом. При этом его применение разнопланово. В первую очередь это, конечно, использование бухгалтерской программы. На сегодняшний день разработано довольно много программных средств, как специализированных («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 Опубликовано: 28 декабря 2017

Автоматизация составления исполнительной документации по строительству в Excel

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

Введение.

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

Итак, поговорим про строительство, конкретно про составление Исполнительной документации (далее ИД).

об Исполнительной документации

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

Большая часть работ закрывается по форме Акта освидетельствования скрытых работ (утвержденным приказом Федеральной службы по экологическому, технологическому и атомному надзору от 26 декабря 2006 года N 1128 (В редакции, введенной в действие с 6 марта 2016 года приказом Ростехнадзора от 26 октября 2015 года №42. Далее АОСР).

Исходные данные для автоматизации.

По-этому возьмем форму АОСР за основу. Итак, у нас есть шаблон документа, в который заносится следующая информация:

Постфикс номера акта;
- наименование объекта капитального строительства;
- юридические данные об участниках строительства (Застройщик или Заказчик; Лицо, осуществляющее строительство; Лицо, осуществляющее подготовку проектной документации;
Лицо, осуществляющее строительство, выполнившее работы, подлежащие освидетельствованию; иные лица.)
- перечень лиц с наименованием организаций, должностей и приказов подтверждающих их полномочия;
- наименование выполненных работ;
- сроки выполнения работ;
- перечень работ, входящих в выполненные работы;
- ссылки на НТД и разделы проекта/тех.документации;
- ссылки на исполнительные схемы, акты испытаний (если таковые требуются);
- перечень используемых материалов с сылками на документы подтверждающими их соответствие (паспорта, сертификаты и т.п.)

Мысли о принципиальных методах решения задачи

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

1. Слияние с файлом Word
2. Заполнение при помощи макросов шаблона на базе Excel.

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

Итак, теперь мы упираемся в 2 задачи:

1. Заполнение шаблона на основе табличных данных
2. Какие поля достаточно ввести один раз, какие будет меняться время от времени и какие поля будут отличаться в каждом акте.

Решая задачу №2 мы вынесем на отдельный лист те данные, которые будут едины в рамках объекта/раздела проекта строительства - это:

Спойлер

На текущем листе заполним лишь один раз, а для остальных актов просто проставим ссылки на эти значения:

Спойлер

И поля, которые будут меняться в каждом акте:

Спойлер

Теперь об удобствах, если записать ФИО ответственных лиц, их организацию, приказ о назначении с датой, то при помощи инструмента «проверка данных» можно занести их фамилии в спойлер , а формулой подтягивать их регалии.

ЕСЛИОШИБКА(ДВССЫЛ(СЦЕПИТЬ(""Данные для проекта"!" ;АДРЕС((ПОИСКПОЗ(E30 ;"Данные для проекта" !$G$15:$G$34;0 ))+14 ;6 )));"-" )

Т.е. на листе "Данные для проекта" в диапазоне $G$15:$G$34, в 6м столбце ищем значение в ячейке Е30, и как только находим нехитрым методом преобразуем в адрес, который будет преобразован в ссылку формулами.

Новая проблема уже упирается в длину строки, если использовать шрифт Times New Roman №10, то длина текста, которая будет выводится на печать, не превышает 105 символов. Т.е. мы упираемся в необходимость создавать костыль для переносов. Итак код функции на VBA:

Function PatrOfString(StringOfTable As String , Nnumber As Byte) As String Dim МассивБлоков(1 To 10 ) As String Dim i As Integer " Dim j As Integer " Dim k As Integer " Dim p As Integer " For i = 1 To 10 Let МассивБлоков(i) = " " Next i Let k = 1 Let p = Len (StringOfTable) Let p1 = Len (StringOfTable) For i = 1 To Round (Len (StringOfTable) / 105 ) + 1 Step 1 If p > 0 And p < 105 Then If k <= p1 Then Let МассивБлоков(i) = Mid $(StringOfTable, k, p) Else If Mid (StringOfTable, k, 1 ) = " " Then If k <= p1 Then Let МассивБлоков(i) = Mid $(StringOfTable, k, 105 ) Let p = p - 105 k = k + 105 Else j = 105 * i If j - k >= 105 Then j = k + 105 End If Do j = j - 1 Loop While Mid $(StringOfTable, j, 1 ) <> " " Let МассивБлоков(i) = Mid $(StringOfTable, k, j - k + 1 ) Let p = p - (j - k + 1 ) Let k = j + 1 End If End If Next i If Nnumber - 1 > 0 Then If МассивБлоков(Nnumber) = МассивБлоков(Nnumber - 1 ) Then МассивБлоков(Nnumber) = " " End If PatrOfString = МассивБлоков(Nnumber)

Т.е. мы сперва забираем текст, потом вырезаем 105 символов, ищем с конца первый символ пробел, как его находим, то закидываем в первую строку массива текст длиной с первого символа, по номер найденного пробела. Затем продолжаем операцию до тех пор пока текст не закончится или не забьется выводной массив. На данном этапе он ограничен памятью в 10 строк. Затем мы выводим по ссылке содержание нужной строки из 1-10. Из минусов решения - забивается память, и для каждого нового запроса осуществляется пересчет заново. Но костыль работает.

Теперь вывод в типовой шаблон АОСР. Есть опять 2 варианта, либо вручную прописывать сопоставлению столбцу(/строки в случае горизонтально расположенных данных под каждый акт), то это будет долго и ресурсоемко подгонять каждый новый шаблон или изменения под таблицы данных. Потому делаем оптимизацию. Данные на каждый один акт будут располагаться вертикально, а сопоставление управляющей комбинации символов (на латинице, т.к. сами акты сплошь на кириллице) будут строки с информацией в этих актах, таким образом в двойном вложенном цикле поиском в тексте управляющих символов мы сопоставим нужное значение из колонки.

Do wb.Worksheets("Пример акта входного контроля" ).Copy after:=Worksheets(Worksheets.Count) Set новыйЛист = wb.Worksheets(Worksheets.Count) For x = 1 To 15 Step 1 " Перебираем столбцы в листе "Пример акта входного контроля" For y = 1 To 71 Step 1 " Перебираем строки в листе "Пример акта входного контроля" If Sheets(новыйЛист.Name).Cells(y, 20 ) = 1 Then Let k = CStr (Sheets(новыйЛист.Name).Cells(y, x)) " Ищем только если в ячейке что-то есть If k <> "" Then For i = 1 To Кол_воЭл_овМассиваДанных Step 1 Let k = Replace (k, arrСсылкиДанных(i), Worksheets().Cells(i, НомерСтолбца)) Next i новыйЛист.Cells(y, x) = k End If End If Next y Next x " Если Вы захотите добавить новые данные для автозаполнения, продолжайте этот список. " Ячейки имеют координаты формата Cells(3, 2), где 3 - пример номера строки, 2 - пример номера столбца " Чтобы легко узнавать номер столбца, можно включить стиль ссылок R1C1 " (Файл -> Параметры -> Формулы -> галочка на поле "Стиль ссылок R1C1") " либо задавать координаты в формате Cells(1, "A"), где 1 - пример номера строки, "А" - пример буквенного обозначения столбца Rem -= Уточняем имя файла и текущий путь к папке откуда запущен макрос =- Let ИмяФайла = ИмяФайла + CStr (Worksheets("БД для входного контроля (2)" ).Cells("1" , НомерСтолбца)) + "-" Let ИмяФайла = ИмяФайла + CStr (Worksheets("БД для входного контроля (2)" ).Cells("2" , НомерСтолбца)) + ".xlsx" НовыйПуть = Replace (ThisWorkbook.FullName, ThisWorkbook.Name, ИмяФайла) Application.DisplayAlerts = False " выключаем вывод предупреждений Sheets(новыйЛист.Name).Copy " Копируем текущий лист в новую книгу ActiveWorkbook.SaveAs Filename:=НовыйПуть, _ FileFormat:=51 ActiveWindow.Close Sheets(новыйЛист.Name).Delete " Удаляем созданный лист Application.DisplayAlerts = True " Включаем вывод предупреждений обратно Let НомерСтолбца = НомерСтолбца + 1 Loop While НомерСтолбца <= КонечныйНомерСтолбца End Sub

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

"Ускоряем Excel путём отключения всего "тормозящего" Public Sub AccelerateExcel() "Больше не обновляем страницы после каждого действия Application.ScreenUpdating = False "Расчёты переводим в ручной режим Application.Calculation = xlCalculationManual "Отключаем события Application.EnableEvents = False "Не отображаем границы ячеек If Workbooks.Count Then ActiveWorkbook.ActiveSheet.DisplayPageBreaks = False End If "Отключаем статусную строку Application.DisplayStatusBar = False "Отключаем сообщения Excel Application.DisplayAlerts = False End Sub

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

(по материалам https://habrahabr.ru/post/344956/)