MS Excel как инструмент обработки экономической информации

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

Экономическая информация Виды по функциям применения. ¡ Плановая. ¡ Учетно-аналитическая. ¡ Статистическая. ¡ Нормативно-справочная. Формы представления. ¡ Вербально-описательная или текстовая. ¡ Табличная ¡ Графическая

АНАЛИЗ ДАННЫХ В EXCEL ¡ Структура Меню «ДАННЫЕ»

Сортировка Подготовка списка для успешной ¡ ¡ сортировки данных в Excel рекомендуется проверить, как организованы данные в сортируемой таблице: если заголовок каждого из столбцов размещен строго в одной ячейке, расположенной непосредственно над столбцом, то такая таблица может быть отсортирована без предварительной подготовки если заголовок каждого из столбцов размещен в нескольких ячейках, то следует перед первой строкой данных в таблице вставить строку с условными (вспомогательными) заголовками.

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

ФИЛЬТРАЦИЯ ДАННЫХ ¡ ¡ Фильтр – инструмент Excel, позволяющий отобразить на экране только записи, удовлетворяющие определенному условию. Для успешной фильтрации данных в Excel, так же как и для сортировки, рекомендуется проверить, как организованы заголовки в таблице, и при необходимости вставить строку с условными заголовками столбцов. В Excel существуют два вида фильтров: автофильтр и расширенный фильтр. Фильтрация по простым критериям реализуется с помощью автофильтра. После выделения всех данных таблицы, включая заголовки столбцов, следует выполнить команду Данные Фильтр Автофильтр. Около заголовка каждого из столбцов появится стрелка.

автофильтра. ¡ ¡ Если щелкнуть на стрелке, например, в столбце «Продавец» , то в появившемся окне можно выбрать любую из фамилий В результате этих действий в таблице останутся только строки, содержащие фамилию продавца Если затем щелкнуть на стрелке «Район» и выбрать район «Южный» , то количество записей уменьшится, т. к. теперь останутся только записи, удовлетворяющие обоим условиям Если щелкнуть на стрелке в столбце «Выручка» и выбрать команду «Условие» , то появится диалоговое Пользовательский автофильтр В этом окне, можно задать условия на значения столбца «Выручка» .

РАСШИРЕННЫЙ ФИЛЬТР ¡ ¡ ¡ Для применения расширенного фильтра необходимо предварительно подготовить т. н. Диапазон условий и Диапазон, в который будут помещены результаты. Для организации Диапазона условий следует: в свободную строку вне таблицы скопировать заголовки тех столбцов, на данные которых будут наложены ограничения (заголовки несмежных столбцов могут оказаться рядом); под каждым из заголовков задать условие отбора данных. Важно!Условия, находящиеся в одной строке Excel рассматривает, как условия И, условия, находящиеся в разных строках как условия типа ИЛИ.

Пример

Пример ¡ Тот же список по схеме И ¡ Схема И ИЛИ

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

Пример

Формы ¡ ¡ ¡ ¡ Инструмент для заполнения списка, а также поиск в списке записей по определенным критериям. Технология Выделить любую ячейку списка Данные/Форма Критерии/Задать критерии Нажать Далее или Назад Редактирование: Добавить или Удалить

Схема ¡ Список

Шаги 2 и 3

Шаг 3

ВЫЧИСЛЕНИЕ ИТОГОВ И СТРУКТУРИРОВАНИЕ ДАННЫХ. ¡ Большие таблицы неудобны для анализа – при обработке данных представленных в них приходится делать много лишних перемещений по полям и записям таблицы (операции навигации), закрывать и открывать отдельные части таблиц. С целью упрощения анализа данных предусмотрены операции по группировке и структурированию данных, которые позволяют выделить из основной таблицы подтаблицы, то есть представить таблицу в виде иерархии подтаблиц.

Структурирование. ¡ ¡ ¡ Структурирование таблиц можно проводить как по строкам, так и по столбцам. На одной и той же таблице можно построить несколько вариантов структур. Требования к структурируемым данным: Структурируемые данные находились в смежных полях (столбцах) или записях (строках), которые образуют классы структуры; Пересечение классов данных должно быть пустое множество, в противном случае два класса сливаются в один (связные классы). Выполнение структурирование выполняется с помощью команд: ДАННЫЕ/ Группа и структура / группировать; ДАННЫЕ/ Группа и структура / создать структуру (автоструктурирование)

Пример структуры

Технологии ¡ ¡ ¡ Выделим весь список Выберем команду ДАННЫЕ/ группа и структура / группировать / столбцы. Получим структуру первого уровня (вся таблица). Обратите внимание на появившуюся линию уровня Охватывающую все поля списка и заканчивающуюся кнопкой со знаком « - » . Выполнив щелчок по кнопке можно свернуть список. Выберем команду ДАННЫЕ/ группа и структура / группировать / столбцы. Получим структуру второго уровня , продолжая процесс группировки по столбцам , а затем по строкам, можно получить иерархию таблиц. Щелчки по соответствующим кнопкам со знаком «- » и «+» , позволяют сворачивать и разворачивать элементы структуры. Для удаления структуры выбирается команда ДАННЫЕ/Группа и структура/Удалить структуру

Автоструктурирование ¡ ¡ Автоструктурирование выполняется только для таблиц содержащих формулы. Требование Ссылки в формулах должны быть на ячейки в смежных левых полях или смежных верхних записях. Пример

Структурирование с подсчетом ИТОГОВ. ¡ ¡ ¡ Выполнение структурирование выполняется ДАННЫЕ/ Итоги. Последняя команда не только выполняет структурирование, но и позволяет выполнять расчеты над записями таблицы. Промежуточными итогами называются вычисления выполненные с помощью определенной функцией по изменяющимся значениям записей одного из полей списка. Общим итогом называется итог, выполненный с помощью той же функцией по значениям промежуточных итогов. Итоги подсчитывают с помощью операции промежуточные итоги.

Технологии ¡ ¡ ¡ ¡ ¡ Для выполнения структурирования необходимо определить основное поле по которому будет проводиться структурирование. Выполним сортировку записей в поле , тем самым разобьем записи на классы. Выполним команду ДАННЫЕ/Итоги. В открывшемся окне установим Заголовок изменяющегося поля; Операцию; Поля с вычисляемыми итогами; Итоги под данными ; Другие опции. ОК Свернем структуру. Получим только итоговые строки. Выполнив свертку еще раз, получим одну строку Общих итогов.

Пример

КОНСОЛИДАЦИИ ДАННЫХ И АНАЛИЗ В СВОДНОЙ ТАБЛИЦЕ. ¡ ¡ ¡ Консолидация данных- это один из способов вычисления Итогов , но данные источников могут располагаться на одном или нескольких листах, одной или нескольких книг. Существуют операции, позволяющие выполнять несколько вариантов консолидации списков. Консолидация данных производится с помощью команды ДАННЫЕ / консолидация. По расположению По категориям

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

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

Консолидация технологии ¡ ¡ ¡ Подготовим исходные данные. Дополним таблицы записями , так чтобы число их в каждой таблице было одинаковое. В свободном месте одного из листов активизируем ячейку и введем команду ДАННЫЕ /консолидация. ¡ ¡ В открывшемся окне , введем диапазон, занимаемым первым списком (за исключением строки ведомость месяц), затем второй, третий и т. д. Установим функцию из раскрываемого списка, например сумма Установим флажки на опциях, использование в качестве имен, или установим связь с исходными данными (но не одновременно). ОК. Получим таблицу консолидированных данных. ¡ ¡

Сводная таблица ¡ ¡ Сводная таблица - это таблица для автоматического подведения итогов взятых из разных списков-таблиц, которые могут находится на разных источниках , в том числе и на Сайтах Интернет, отличаются структурой полей и количеством записей. Таким образом сводная таблица является наиболее полным способом вычисления итогов. Создание и обработка сводных таблиц осуществляется с помощью МАСТЕРА СВОДНЫХ ТАБЛИЦ, ДАННЫЕ / Сводная таблица.

Технология ¡ ¡ ДАННЫЕ Сводная таблица. откроется первое диалоговое окно МАСТЕРА СВОДНЫХ ТАБЛИЦ

Шаг 2 ¡ В следующем диалоговом окне необходимо указать интервал ячеек, данные из которого будут представлены в сводной таблице

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

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

Технологии (шаг 1)

Технологии (шаг 2)

Шаг 3

Шаг 4

Шаг 5

шаг 6

Результат

OLAP кубы (исходная таблица)

Структура куба

Трехмерное представление таблицы

шаг 7

Шаг 8

Шаг 9

Результат

Дополнительные возможности EXCEL Меню Сервис

Структура меню

Технологии анализа на основе процедур «Что- если» . ¡ ¡ 1. 2. 3. Анализ «Что -если» -Процесс изменения значений ячеек и анализа влияния этих изменений на результат вычисления формул в пределах рабочего листа. Например, изменение процентной ставки, используемой в таблице амортизации для определения сумм платежей. Включает: Анализ посредством Подбора параметров Анализ на основе Сценариев Использование Таблиц подстановок

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

пример ¡ ¡ ¡ Какую максимальную ссуду на год можно взять, если вы должны ограничить ежемесячные выплаты суммой 250 тыс. р. процентная ставка – простая- 12% годовых. Шаг 1 Найдем величину ежемесячных выплат для произвольной величины ссуды по формуле: Платеж (1+Процентная ставка)*Величина займа/Срок в месяцах. Шаг 2. Применим инструмент Подбор параметра. Для этого: Выберите команду Сервис Подбор параметра. В появившемся диалоговом окне в панель Установить в ячейке введите ссылку на ячейку, содержащую формулу. Введите искомый результат в поле Значение (в нашем примере 250000 р. )

Анализ на основе Сценариев ¡ ¡ ¡ Сценарием в Microsoft Excel называется набор значений параметров, используемый для прогнозирования поведения модели. Существует возможность создания и сохранения на листе различных сценариев и переключения на любой из этих сценариев для просмотра результатов. Так, если требуется сформировать бюджет, но годовой доход точно не известен, то для дохода определяются различные значения, а затем для каждого варианта сценария выполняется анализ «Чтоесли» . Изменяемые ячейки в модели – это ячейки, содержащие значения, которые требуется использовать в качестве переменных. Для применения Сценариев следует на рабочем листе Excel смоделировать решение задачи при некотором наборе входных параметров. В отличии от процедуры «подбор параметра» процедура анализа на основе сценариев предусматривает изменения содержимого нескольких ячеек

Алгоритм 1. 2. 3. 4. 5. 6. 7. Создадим расчетную модель для начального сценария Выберем команду СервисСценарии. В появившемся диалоговом окне нажмите кнопку Добавить. В окне Добавление сценария ввести имя первого сценария, а затем в этом же диалоговом окне следует указать адреса изменяемых ячеек В появившемся диалоговом окне Значения ячеек сценария введите значения изменяемых ячеек В каждом поле можно ввести константу или формулу (формулы могут использовать только константы, например =1, 2*120000, и не могут содержать имен ячеек После ввода значений параметров нажмите ОК. Чтобы создать другой сценарий снова нажмите кнопку Добавить, задайте имя нового сценария, нажмите ОК, задайте новые значения , нажмите ОК и т. д. После формирования сценариев можно перейти к анализу модели. Существует возможность их поочередного просмотра. Для этого в окне Диспетчер сценариев следует выбрать имя сценария и нажать кнопку Вывести.

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

Пример Для фирмы требуется составить варианты расходов на следующий год. ¡ Заметим, что ячейка Расходы содержит формулу - сумму величин: Арендная плата, Коммунальные услуги, Административные расход ¡ Пример 2. ¡

Замечания ¡ ¡ ¡ При работе со сценариями удобно ячейкам с изменяемыми значениями и ячейкам с результатами присваивать имена. Рекомендуется исходный вариант модели сохранить как один из сценариев. При сохранении рабочей книги Excel сценарии сохраняются вместе с другими данными рабочего листа. Важно!При указании несмежных диапазонов удерживайте нажатой клавишу Ctrl. Вариант расчета расходов фирмы Важно! Нельзя в качестве изменяемых ячеек указывать ячейки с формулами.

Анализ на основе ТАБЛИЦ ПОДСТАНОВКИ ¡ ¡ Инструмент Таблица подстановки может быть использован в одном из двух вариантов: Таблица подстановки с одной переменной. Таблица подстановки с двумя переменными. В отличии от предыдущих процедур «таблица подстановок» позволяет «проиграть» варианты подачи на вход анализируемой модели массив

Таблица подстановки с одной переменной. ¡ ¡ Инструмент Таблица подстановки используется для расчетов значения, вычисляемого по формуле, при нескольких вариантах одного из входных параметров. Чтобы создать такую таблицу, необходимо выполнить следующие действия: Все варианты входного параметра расположить в столбец, например А 7: А 11 или в строку А 7 -Е 7 Затем ввести формулу для вычислений. Если варианты входного параметра расположены в столбце, то формулу следует ввести в пустую ячейку, расположенную в строке над столбцом параметров справа от него. Если же значения входного параметра расположены в строке, то формула вводится в пустую ячейку левее строки параметров и ниже ее

Таблица подстановки ¡ ¡ На следующем шаге выделите диапазон таблицы данных – минимальный прямоугольный блок ячеек, включающий в себя формулу и все значения входного диапазона. ). Выполните команду Данные Таблица подстановки. В появившемся диалоговом окне в поле Подставлять значения по строкам в следует задать местонахождение ячейки с начальным значением изменяемого параметра (Если же данные расположены в строке, то эта ссылка вводится в поле Подставлять значения по столбцам в. ) Нажмите кнопку ОК. Excel выведет значения формулы для каждого входного значения параметра в ячейках диапазона таблицы данных

Пример ¡ ¡ Рассмотреть различные варианты ежемесячных выплат по ссуде 100000 р. на 1 год для нескольких процентных ставок. Эту информацию предоставит таблица подстановки с одной переменной. Инструмент таблица подстановок, особенно удобен при анализе большого количества данных, например выбранных случайно.

Настройки Excel Заключение

технология частичного суммирования (мастер суммирования ) Исходные данные список «Заказы» . Допустим нам необходимо вычислить сколько оплатила фирма Монитор ЗАО, за всю поставленную продукцию ¡

Шаг 2 зададим имя поля суммирование из раскрываемого списка полей, для данного примера поле «оплачено» и имя анализируемого поля , оператор сравнения и значение в поле из раскрываемых списков, для данного примера поле «Фирма» = Монитор ЗАО. Щелкнем добавить поле и далее. .

Шаг 3 В следующем окне, установим флажок на одном из вариантов размещения и нажмем далее.

Шаг 4 и далее ¡ В следующих окнах зададим ячейку для вывода названия фирмы и вычисленного значения

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

Состав надстройки

Инструменты ¡ Корреляционный анализ применяется для количественной оценки взаимосвязи двух наборов данных, представленных в безразмерном виде. корреляционный анализ дает возможность установить, ассоциированы ли наборы данных по величине, то есть, большие значения из одного набора данных связаны с большими значениями другого набора (положительная корреляция), или, наоборот, малые значения одного набора связаны с большими значениями другого (отрицательная корреляция), или данные двух диапазонов никак не связаны (нулевая корреляция).

Инструменты ¡ ¡ ¡ Описательная статистика Это средство анализа служит для создания одномерного статистического отчета, содержащего информацию о центральной тенденции и изменчивости входных данных. Гистограмма Используется для вычисления выборочных и интегральных частот попадания данных в указанные интервалы значений. При этом рассчитываются числа попаданий для заданного диапазона ячеек Регрессия Линейный регрессионный анализ заключается в подборе графика для набора наблюдений с помощью метода наименьших квадратов. Регрессия используется для анализа воздействия на отдельную зависимую переменную значений одной или более независимых переменных Скользящее среднее используется для расчета значений в прогнозируемом периоде на основе среднего значения переменной для указанного числа предшествующих периодов. Скользящее среднее, в отличие от простого среднего для всей выборки, содержит сведения о тенденциях изменения данных. Примеры использования

Корреляционный анализ ¡ ¡ Сервис анализ данных корреляция Введите данные

Описательная статистика ¡ ¡ ¡ Сервис анализ данных описательная статистика Введите данные Обязательно установите хотя бы один статистический параметр

Регрессия ¡ ¡ Выберем в качестве объясняемой переменной У , столбец интерес студентов, а определяющей творческий подход. Выберем команду регрессия

Надстройка «поиск решений» ¡ ¡ Поиск решений является частью блока процедур, «Что- если» Процедура поиска решения позволяет найти оптимальное значение формулы содержащейся в ячейке, которая называется целевой. Эта процедура работает с группой ячеек, прямо или косвенно связанных с формулой в целевой ячейке. Чтобы получить по формуле, содержащейся в целевой ячейке, заданный результат, процедура изменяет значения во влияющих ячейках. Чтобы сузить множество значений, используемых в модели, применяются ограничения. Эти ограничения могут ссылаться на другие влияющие ячейки.

Пример ¡ Фирма выпускает продукцию 4 видов, условно П 1, п 2, П 3, П 4, располагает ресурсами, количество которых 2, 5, 1 мил. руб. Известны затраты ресурсов на единицу выпущенной продукции в денежном исчислении, известна доходность от реализации продукции. Требуется спланировать деятельность фирмы, чтобы получит максимальный доход. Все данные приведены в таблице.

Схема использования пример

Результат

Отчет

Отчет по устойчивости

Технология ¡ ¡ Введем данные В строку план введем начальные значения равные 1

Подготовка модели ¡ Присоединим к таблице столбец Расход и введем в его ячейки формулу

Процедура поиска ¡ ¡ ¡ ¡ Загрузим процедуру Сервис поиск решения Последовательно введем параметры Установить в ячейке (ссылка) Значение максимума Изменяя ячейки (ссылка на строку план) Добавить (ячейки столбца расход Меньше ячеек столбца запас, ячейки плана неотрицательные)

Результат После выполнения поиска у вас возможности ¡ Сохранить исходные данные ¡ Сохранить результат поиска ¡ Сохранить сценарий ¡ Выдать отчет ¡ Проделайте все операции ¡

ИТ автоматизации работы создания документов

Литература ¡ ¡ Гарнаев А. Ю. Excel, VBA, Internet в экономике и финансах. СПб. : БХВ-Петербург, 2001. Демидова Л. А. , Пылькин А. Н. Программирование в среде Visual Basic for Application. М. : Телеком, 2004. Уокенбах Д. Профессиональное программирование на VBA в Excel 2002. Пер. с англ. – М. : Издательский дом «Вильямс» , 2003. Олблайн К. Моделирование с помощью MS Excel и VBA. М. : Издательский дом «Вильямс» , 2005

Офисное программирование ¡ ¡ ¡ Программирование - это разработка и отладка программ" «Офисное программирование» (ОП) - это программирование при создание документов" Документ становится первичным, а программа - лишь его часть. Документ в ОП понимается как объект (структура данных - собрание данных разного типа) и программ, их обрабатывающих. Программирование в Office — это, прежде всего, уменьшение количества повторяющихся действий (и ручной работы, которая для этого требуется).

Ситуации для программирования в Office 1. 2. Изготавливать массовые(типовые)документы, очень похожие друг на друга: приказы, распоряжения в бухгалтерию, договоры, отчеты и т. п. Часто информацию можно взять из базы данных, тогда использование программирования может дать очень большой выигрыш во времени. Иногда данные приходится вводить вручную, но и тогда автоматизация дает выигрыш во времени и в снижении количества ошибок. Разновидность такой же ситуации: одни и те же данные нужно использовать несколько раз. Например, вы заключаете договор с заказчиком. Одни и те же данные (наименование, адрес, расчетный счет, номер договора, дата заключения, сумма и т. п. ) могут потребоваться во многих документах: самом договоре, счете-фактуре, акте сдачи выполненных работ и т. д. Логично один раз ввести эту информацию (скорее всего, в базу данных), а затем автоматически формировать (например, в Word) требуемые документы;

Ситуации для программирования в Office ¡ ¡ ¡ Нужно сделать так, чтобы вводимые пользователем данные автоматически проверялись. Вероятность ошибки при ручном вводе данных зависит от многих факторов, но, согласно результатам некоторых исследований, она в среднем составляет около 2%. "Вылавливать" потом такие ошибки в уже введенных данных — очень тяжелый труд, поэтому лучше сразу сделать так, чтобы они не возникали. Автоматизировать любое действие, которое вам приходится повторять несколько раз. Например, занесение сотен контактов в Outlook, или замена ресурса в десятках проектов Project, или анализ информации из базы данных за разные периоды в таблице Excel — это те ситуации, когда знание объектных моделей приложений Office. Разработка новых процедур и функций (пользовательских) , которые отсутствуют в библиотеках Office

Определение Макроса В приложениях Microsoft Office помимо использования стандартных средств , с целью ускорения работ, можно использовать программные модули расширяющие возможности основных инструментов с помощью макросов. ¡ Макрос — это набор команд и инструкций, выполняемых как одна команда. ¡

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

создания макроса Для создания макроса в Microsoft Office существует два метода: использование средства для записи макросов ¡ c помощью Макрорекордера ¡ прямое программирование в редакторе Visual Basic ¡

Macrorecorder ¡ ¡ ¡ Для автоматического создания макросов служит макрорекордер Macrorecorder - это транслятор. Всякий раз, когда пользователь запускает Macrorecorder, создается программа (макрос) на языке VBA, которая является результатом трансляции действий пользователя с момента запуска Macrorecorder до момента окончания записи макроса. Пользователь оперирует с образами объектов на экране, программа - с объектами Office. Одного и того же эффекта, пользователь иногда может достичь разными путями : он может вызвать команду меню или щелкнуть соответствующую кнопку ; нажать комбинацию "горячих" клавиш. С этими разными событиями может связываться одна и та же процедура обработки. Поэтому созданная Macrorecorder программа в таких случаях будет одинаковой, не зависящей от того, как пользователь добился нужного ему эффекта.

Принцип работы макрорекордера ¡ ¡ Принцип работы макрорекордера больше всего похож на принцип работы магнитофона: нажимаем на кнопку — начинается запись тех действий, которые мы выполняем. Мы нажимаем на вторую кнопку — запись останавливается, и мы можем ее проиграть (т. е. повторно выполнить ту же последовательность действий). Конечно, макрорекордер позволяет написать только самые простые VBA-программы. Однако и он может принести много пользы. Например, можно "положить" на горячие клавиши те слова, словосочетания, варианты оформления и т. п. , которые вам часто приходится вводить (должность, название фирмы, продукт, ФИО директора и ответственного исполнителя и т. д. ), этим вы сэкономите много времени.

Алгоритм работы с макрорекордером ¡ ¡ Включить запись макроса, настроив некоторые параметры; Выполнить действия, которые вы хотели бы автоматизировать; Остановить запись; Использовать записанный макрос по необходимости.

¡ ¡ Macrorecorder, помимо написания макросов, изучить VBA и тонкости объектных моделей приложений Office . Например, вы не знаете точно, как именно реализовать то или иное действие программно, но знаете, как это делается вручную. Просто запишите макрос с нужными вам действиями, а потом откройте его в редакторе VBA. Вы сможете проанализировать полученный код, а возможно и включить его фрагменты в свое приложение. Macrorecorder не "интеллектуален" - он слепо копирует действия пользователя , не занимается оптимизацией созданной программы. Так, если пользователь записал в ячейку А 1 число 15, в А 2 - 17, а потом передумал и решил вернуться к ячейке А 1 и заменить 15 на 21, то Macrorecorder в программе повторит все его действия. "Любимый" объект Macrorecorder – Selection т. к. всякий раз, когда пользователь выбирает новый объект, Macrorecorder создает новый объект Selection.

Рекомендации ¡ ¡ ¡ необходимо очень тщательно спланировать макрос, хорошо продумав, что вы будете делать и в какой последовательности. Если есть возможность, определите подготовительные действия. Например, если нужно вставить текущую дату в начало документа, может быть, имеет смысл первой командой макроса сделать переход на начало документа (<Ctrl>+<Home>); посмотрите, нет ли готовой команды, которую можно сразу назначить клавише или кнопке на панели инструментов без создания макроса. Сделать это можно при помощи меню Сервис | Настройка. С вкладки Команды можно перетащить нужную команду на требуемую панель управления, и, нажав на этой же вкладке кнопку Клавиатура, в окне Настройка клавиатуры назначить для команды нужную комбинацию клавиш; если вы собираетесь при помощи макроса менять оформление текста, правильнее вначале создать новый стиль с вашим оформлением, а потом уже применить этот стиль к тексту. В этом случае опять-таки можно обойтись без макроса, просто назначив стиль комбинации клавиш. Делается это при помощи того же диалогового окна Настройка клавиатуры.

Создание макроса ¡ ¡ ¡ Чтобы создать макрос в макрорекордере (для тех программ Microsoft Office, для которых это средство предусмотрено, например, Word, Excel, Power. Point, Project) В меню Сервис | Макрос выберите команду Начать запись. В открывшемся окне Запись макроса вам потребуется определить: Имя макроса. Правило такое: имя не должно начинаться с цифры, не должно содержать пробелы и символы пунктуации. Максимальная длина в Excel — 64 символа, в Word — 80 символов. Назначение. будет ли макрос назначен кнопке на панели управления или комбинации клавиш. Где сохранить макрос. В Word в вашем распоряжении текущий файл и шаблон для всех вновь создаваемых документов — Normal. dot, в Excel — текущая книга, возможность создать макрос одновременно с созданием новой книги и личная книга макросов PERSONAL. XLS (макросы из этой скрытой книги будут доступны во всех книгах) Описание. В это поле лучше ввести информацию о том, для каких целей создается этот макрос — это подарок не только для других пользователей, но и для себя (через несколько месяцев).

¡ ¡ После нажатия кнопки OK или назначения кнопки или клавиатурной комбинации начнется запись макроса. Указатель мыши при этом примет вид магнитофонной кассеты и появится маленькая панель Остановить запись. На ней всего две кнопки — Остановить запись и Пауза. Если вы случайно закрыли эту панель, остановить запись можно через меню Сервис | Макрос | Остановить запись. Самый простой способ запустить макрос, которому не назначена кнопка или клавиатурная комбинация, — в меню Сервис выбрать Макрос | Макросы (или нажать комбинацию клавиш <Alt>+<F 8>), в открывшемся окне Макрос (в списке выбрать нужный макрос и нажать кнопку Выполнить. Из этого же окна можно просматривать и редактировать макросы, удалять или перемещать их и т. п.

Замечания ¡ ¡ Если макросов создано много, то получить список всех назначений клавиш(включая назначения для встроенных макросов Word) можно при помощи меню Сервис | Макросы, затем в окне Макрос в списке Макросы из выбрать Команд Word, а в списке Имя выбрать макрос List. Commands и нажать кнопку Выполнить. В ответ на приглашение нужно выбрать Текущие настройки меню и клавиш (иначе будет выведен полный список команд Word на 26 страниц). В ваш документ будет вставлена таблица с текущими назначениями клавиш, которую можно распечатать. Сохраняя файл Microsoft Word, содержащий макросы, обязательно убедитесь в том, что вы сохраняете его в формате. docm (Документ Word с поддержкой макросов). Дело в том, что используемый по умолчанию формат. docx (Документ Word) не поддерживает макросы. Если вы сохраните документ с макросами в таком формате, результаты работы будут утеряны.

Пример Создать макрос в Microsoft Word, который автоматически форматирует выделенный текст следующим образом: ¡ Шрифт: Times New Roman, 14 -й, курсивный ¡ Цвет шрифта: красный ¡ Назначить вызов макроса по нажатию комбинации клавиш Alt + Ctrl + Shift + A и по нажатию настраиваемой кнопки. ¡

Решение ¡ Выделение слова « Ctrl + ←» ; Ctrl+ Shift + →» ; Форматирование шрифта –Формат шрифт прописные Выбор цвета- красный. ¡ Выберем Сервис макросы ¡ ¡

¡ ¡ ¡ В открывшемся меню нам нужна команда Запись макроса. Появится окно для настройки свойств макроса Рассмотрим поля этого диалога. Имя макроса: в это поле нужно ввести имя макроса. Имена макросов должны начинаться с буквы, не должны содержать пробелов. Желательно давать макросам какие-нибудь осмысленные имена. Например, Формат_Times_Красный. Назначить макрос кнопке: диалоговое окно назначения макроса настраиваемой кнопке. Назначить макрос клавишам: диалоговое окно назначения макроса сочетанию клавиш на клавиатуре.

Замечание по Заполнению ¡ ¡ ¡ В поле Макрос доступен для надо выбрать место сохранения макроса, которое определяет его доступность для различных документов. По умолчанию здесь выбран параметр Всех документов (Normal. dotm). Normal. dotm —это общий шаблон, доступный для всех документов Microsoft Word. Если макрос будет сохранен в Normal. dotm — вы сможете запустить его из любого Word-документа. Не следует сохранять все записываемые вами макросы в Normal. dotm. Делайте это лишь тогда, когда вы точно уверены в том, что макрос понадобится вам в различных документах. Мы выберем в этом поле значение Документ 1 (документ). Именно так здесь называются еще не сохраненные документы. Выбрав этот пункт, мы сохраняем макрос в текущем документе, то есть сможем вызывать макрос лишь из этого документа. В поле Описание содержится описание макроса. Вы вполне можете оставить его пустым. Кнопка OK начинает запись макроса. Но до начала записи воспользуемся кнопкой Назначить макрос клавишам. Появится окно настройки комбинации клавиш для запуска макроса

сочетание клавиш ¡ ¡ ¡ Чтобы назначить макросу сочетание клавиш, нужно установить курсор в поле Новое сочетание клавиш и нажать нужное сочетание на клавиатуре. В случае, если это сочетание не назначено ранее для быстрого вызова какихлибо команд, под полем Текущие сочетания вы увидите надпись Текущее назначение: [нет]. Если вы увидите здесь что-нибудь другое — лучше всего поискать свободное сочетание. Иначе вы можете столкнуться с неожиданным поведением знакомых вам горячих клавиш. В поле Сохранить изменения в выберем Документ 1 — то есть наш документ, который мы в данный момент редактируем. По умолчанию здесь установлен уже знакомый вам Normal. dotm. Теперь нажмем на кнопку Назначить. Выбранное сочетание клавиш переместиться в поле Текущие сочетания. Нажав кнопку Закрыть,

Запись макроса ¡ ¡ ¡ После нажатия закрыть появляется панель записи макроса. Выполним необходимые действия Нажмем на клавиатуре клавиши управления курсором Отформатируем шрифт согласно заданию Остановим запись Обратите внимание на кнопку Пауза. С ее помощью можно приостановить запись макроса, выполнить какие-нибудь действия, которые не войдут в него, после чего возобновить запись.

Текст макроса ¡ ¡ ¡ ¡ ¡ Sub Макрос2() ' ' Макрос2 Макрос ' Макрос записан 08. 03. 2010 ' Selection. Move. Up Unit: =wd. Line, Count: =1 Selection. Move. Right Unit: =wd. Character, Count: =1 With Selection. Font . Name = "Times New Roman" . Size = 12 . Bold = False . Italic = True . Underline = wd. Underline. Words . Underline. Color = wd. Color. Automatic . Strike. Through = False . Double. Strike. Through = False . Outline = False . Emboss = False

запуск макроса с помощью кнопки. ¡ ¡ ¡ Для этого в окне запись макроса укажите Назначить макрос кнопке В списке Выбрать команды из выберите Макросы. В поле, которое расположено ниже, появятся ссылки на доступные макросы. Выделите нужный и нажмите на кнопку Добавить>>. Ссылка на него появится в поле выбранной панели.

способы запуска макроса ¡ Самый простой способ — воспользоваться окном Макрос, которое можно открыть при помощи меню Сервис | Макросы

¡ ¡ Если вы пользуетесь макросом постоянно, то можно использовать самый быстрый способ его вызова — клавиатурную комбинацию. На практике клавиатурным комбинациям есть смысл назначать только те макросы, которые используются каждый день, например, ввод информации об ответственном исполнителе, о руководителе, которому пойдет документ на подпись, о полном названии вашей организации и т. п. Главное — чтобы вы использовали их постоянно, иначе вы просто забудете,

Назначение комбинаций Word ¡ ¡ ¡ В Word это выглядит так: с помощью меню Сервис | Настройка открываем одноименное окно и переходим на вкладку Команды. Затем нажимаем на кнопку Клавиатура: откроется окно Настройка клавиатуры В списке Категории нужно выбрать Макросы, в списке Команды — созданный вами макрос, установить указатель ввода в поле Новое сочетание клавиш и нажать требуемое сочетание клавиш. Помимо обычных сочетаний типа <Alt>+<1>, <Alt>+<M> и т. п. , можно использовать и более сложные. Например, вы вставляете при помощи макросов два варианта названия вашей организации: полное и краткое. Этим макросам можно назначить клавиатурные комбинации вида <Alt>+<N>, <F> и <Alt>+<N>, <S>. Это значит, что если вы вначале нажмете вместе клавиши <Alt>+<N>, а затем <F>, то соответствующий макрос будет выполнен. Вводить такое сочетание клавиш в поле Новое сочетание клавиш нужно точно так же, как вы будете его применять. После того как нужное сочетание клавиш будет введено, нажмите кнопку Назначить, а потом Закрыть.

Назначение комбинаций Excel ¡ ¡ клавиатурные комбинации назначаются в меню Сервис выбрать Макрос | Макросы, выбрать в списке нужный макрос и нажать кнопку Параметры. Откроется окно Параметры макроса в котором вы сможете выбрать нужную клавиатурную комбинацию (только в сочетании с клавишей <Ctrl>) и ввести описание макроса. Вообще говоря, любое сочетание клавиш можно назначить макросу и в Excel, но простыми способами этого сделать нельзя — придется писать программный код, в котором будут перехватываться события приложения. Как мы уже говорили, клавиатурные комбинации есть смысл назначать только тем макросам, которыми вы пользуетесь каждый день. А что делать с полезными макросами, которые активно используются, к примеру, в отчетный период. Для этого случая в меню Сервис выбрать Макрос | Макросы, выбрать в списке нужный макрос и нажать кнопку Параметры. Откроется окно Параметры макроса в котором вы сможете выбрать нужную клавиатурную комбинацию (только в сочетании с клавишей <Ctrl>) и ввести описание макроса. Вообще говоря, любое сочетание клавиш можно назначить макросу и в Excel, но простыми способами этого сделать нельзя — придется писать программный код, в котором будут перехватываться события приложения.

¡ ¡ 1. В меню Сервис выберите Настройка и перейдите на вкладку Панели инструментов. 2. Нажмите кнопку Создать, введите название панели (например, Мои_макросы) и выберите тот документ, в котором она будет создана. Если вы выберете Normal. dot, то панель будет доступна для всех документов Word на этом компьютере (что чаще всего и необходимо). Другой вариант — создать панель инструментов в том документе Word, который у вас открыт. В этом случае панель будет доступна только из этого файла. 3. После того как вы нажмете кнопки OK и Закрыть, будет создана новая пустая панель (которая будет находиться гденибудь прямо поверх документа). Чтобы было удобней, перетащите ее к стандартным панелям инструментов, а потом вновь воспользуйтесь командой главного меню Сервис | Настройка. В окне Настройка перейдите на вкладку Команды, в списке Категории выберите Макросы и просто перетащите на панель инструментов нужные макросы из списка Команды. Если на панель инструментов нужно поместить не один, а несколько макросов, то, возможно, удобнее будет нажать кнопку Упорядочить команды и воспользоваться очень удобным диалоговым окном Изменение порядка команд

настройка кнопок. Для этого при открытом окне Настройка (это условие обязательно!) просто щелкните правой кнопкой мыши по кнопке панели инструментов, которую надо настроить. Откроется специальное контекстное меню ¡. ¡

Описание меню ¡ ¡ ¡ Удалить — удалить кнопку (также можно просто перетащить ее обратно с панели на окно Настройка); Имя — ввести имя, т. е. надпись на кнопке или пункте меню. Для меню использование надписи удобно, для кнопки на панели инструментов — не очень, поскольку это занимает много места; Копировать значок на кнопке и Вставить значок для кнопки — воспользоваться понравившимся вам значком с другой кнопки; Изменить значок на кнопке — откроется скромный редактор, в котором вы сможете сами нарисовать нужный значок; Выбрать значок для кнопки — выбрать один из 42 стандартных значков. На самом деле только в Word значков, которые можно использовать, несколько тысяч; Основной стиль — под этой надписью скрывается то, что нам обычно и нужно: чтобы кнопка была представлена только рисунком, безо всяких надписей; Только текст (всегда), Только текст (в меню), Значок и текст — определяют, что именно из набора надпись/рисунок будет показано на кнопке. Наиболее часто используемый вариант — Основной стиль; Начать группу — слева от кнопки появится вертикальная черта (разделитель); Назначить гиперссылку — назначить ссылку на другое место в вашем документе или на страницу в Интернете. все эти преобразования доступны только при открытом диалоговом окне Настройка

Создание меню ¡ ¡ ¡ Откройте то же диалоговое окно Настройка (меню Сервис | Настройка). В списке Категории выберите Новое меню. Перетащите команду Новое меню из списка Команды того же окошка в нужное место основного меню. Далее точно так же при открытом окне Настройка щелкните правой кнопкой мыши по созданному вами пункту меню и переименуйте его(например назовите его Макросы). Далее нужно нажать кнопку Упорядочить команды в окне Настройка. В открывшемся диалоговом окне Изменение порядка команд нужно в списке Строка меню выбрать Макросы и добавить в него нужные элементы (т. е. созданные вами макросы). Переименовать их можно при помощи кнопки Изменить выделенный объект прямо из этого окна. В результате у вас может получиться меню, в котором пользователю запутаться будет трудно

Настройки меню Excel ¡ ¡ ¡ Сервис | Настройка) и в списке Категории выберем Макросы, то вместо списка макросов в списке Команды будет две возможности: Настраиваемая команда меню и Настраиваемая кнопка — это готовая кнопка, которую можно перетащить на панель инструментов, а потом открыть для нее контекстное меню и воспользоваться командой Назначить макрос. Для выбора иконки, формата отображения и т. п. можно воспользоваться возможностями контекстного меню, которые доступны и в Word. Для создания нового меню в Excel нужно точно так же создать новое меню, как и в Word, а потом нажать на кнопку Упорядочить команды и добавить в это меню несколько элементов Настраиваемая команда меню. Их реальная настройка (в том числе и назначение макросов) производится по нажатию кнопки Изменить выделенный объект.

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

Макросы и безопасность ¡ ¡ Microsoft Office снабжен собственной системой безопасности, основная задача которой — противодействие проникновению макровирусов — вредоносных программ на языке VBA. Макровирусы — это основная проблема, которую породил язык программирования, встроенный в офисные программы. Безопасность работы с макросами сводится к очень простому правилу: не следует разрешать выполнение макросов, если вы не знаете точно, для чего нужны эти макросы. Если вы открываете документ, который содержит подозрительный, с точки зрения системы, макрос, при настройках безопасности по умолчанию этот макрос блокируется. В Microsoft Word выводится сообщение безопасности Office Если нажать на кнопку Параметры, которая находится в правой части панели сообщений, появится окно настройки свойств макроса. С помощью этого окна мы можем либо разрешить выполнение макросов в открытом документе, выбрав пункт Включить это содержимое, либо запретить, выбрав Установить защиту от неизвестного содержимого.

¡ ¡ Для настройки системы безопасности в Microsoft Word Сервис Параметрыбезопастнось. нажмите кнопку. Защита от макросрв Появится окно настройки параметров макросов

Решение ¡ ¡ ¡ Это задание можно рассматривать как создание автоматической "записной книжки" емкостью в одну запись. Вызовем Сервис макросы Настроить быстрый вызов макроса можно в том же окне, где задается имя макроса и место сохранения. Имя Excel-макросов подчиняется тем же законам, что и в MS Word, а в качестве горячих клавиш предлагается использовать клавишу в комбинации с Ctrl. После начала записи щелкнем правой кнопкой мыши по выделенной области и выберем в появившемся меню пункт Копировать. Перейдем на лист Записная книжка и вставим скопированное туда командой Вставить с вкладки Главная. Далее – вернемся на лист Price и остановим запись.

Макросы Office 2007 ¡ Вызов макроса. ¡ Запись макроса

¡ Настройка меню макроса

¡ Безопасность макроса: кнопка Параметры, которая находится в правой части панели сообщений