Файл: Контрольная работа по теме Базы данных в Excel 72 IV. Макросы в ms excel 78 Макросы для автоматизации работ 78.doc

Вариант 7

1. 
   Отсортировать данные сначала по месяцам, а внутри месяцев по декадам, а внутри декад по видам акций.
   
2. 
   Определить суммарную стоимость сделок по дням недели.
   
3. 
   Определить суммарную стоимость сделок для каждой компании по месяцам.
   
4. 
   Рассчитать суммарную стоимость сделок с акциями Рособувь, совершенных брокером Загребаевым, по понедельникам в июле.
   

Вариант 8

1. 
   Отсортировать данные сначала по месяцам, а внутри месяцев по декадам, а внутри декад по объемам продаж.
   
2. 
   Определить суммарную стоимость сделок-продаж по месяцам.
   
3. 
   Определить суммарную стоимость сделок-продаж в каждой декаде по дням недели.
   
4. 
   Рассчитать суммарную стоимость акций Русал, проданных брокером Коробочкиным, по понедельникам в августе.
   

Вариант 9

1. 
   Отсортировать данные сначала по месяцам, а внутри месяцев по декадам, а внутри декад по объемам покупок.
   
2. 
   Определить суммарную стоимость сделок-покупок для каждого брокера.
   
3. 
   Определить суммарную стоимость сделок-покупок по декадам в каждом месяце.
   
4. 
   Рассчитать суммарную стоимость акций Татнефть, купленных брокером Кубышкиным, по вторникам в августе.
   

Вариант 10

1. 
   Отсортировать данные сначала по дням недели, а внутри дней по декадам, а внутри декад по объемам продаж.
   
2. 
   Определить среднюю стоимость сделок с акциями разных компаний.
   
3. 
   Определить среднюю стоимость сделок по дням недели в каждом месяце.
   
4. 
   Рассчитать среднюю стоимость сделок, совершенных брокером Халявиным с акциями Химпром, в первой декаде октября.
   

Вариант 11

1. 
   Отсортировать данные сначала по дням недели, а внутри дней по декадам, а внутри декад по объемам покупок.
   
2. 
   Определить среднюю стоимость сделок-продаж по дням недели.
   
3. 
   Определить среднюю стоимость сделок-продаж каждого брокера с акциями каждой компании.
   
4. 
   Рассчитать среднюю стоимость сделок-продаж, совершенных брокером Везунчиковым с акциями Автоваза, во второй декаде ноября.
   

Вариант 12

1. 
   Отсортировать данные сначала сумма продаж, а внутри сумм по декадам, а внутри декад по объемам покупок.
   
2. 
   Определить суммарную стоимость сделок-покупок по месяцам.
   
3. 
   Определить среднюю стоимость сделок-покупок каждого брокера по декадам.
   
4. 
   Рассчитать среднюю стоимость сделок покупок, совершенных брокером Загребаевым с акциями Газпрома, по пятницам в декабре.
   

---

Вариант 13

1. 
   Отсортировать данные сначала по месяцам, а внутри месяцев по дням недели, а внутри дней по декадам.
   
2. 
   Определить суммарную стоимость сделок-покупок для каждого брокера.
   
3. 
   Определить общее количество сделок для каждого брокера по дням недели.
   
4. 
   Определить общее количество сделок, совершенных брокером Коробочкиным с акциями Лукойл, в первой декаде января.
   

Вариант 14

1. 
   Отсортировать данные сначала по месяцам, а внутри месяцев по объемам продаж, а внутри объемов по декадам.
   
2. 
   Определить количество сделок-продаж с акциями каждой компании.
   
3. 
   Определить количество сделок-продаж для каждого брокера по месяцам.
   
4. 
   Определить количество сделок, связанных с продажей акций НорильскНикель, совершенных брокером Кубышкиным по воскресеньям февраля.
   

Вариант 15

1. 
   Отсортировать данные сначала по месяцам, а внутри месяцев по суммам покупок, а внутри сумм по декадам.
   
2. 
   Определить количество сделок-покупок по дням недели.
   
3. 
   Определить количество сделок-покупок акций каждой компании по декадам.
   
4. 
   Определить количество сделок, связанных с покупкой акций Промтрактор, совершенных брокером Халявиным в третьей декаде марта.
   

IV. Макросы в MS Excel
Макросы представляют собой встроенные в документ Excel программы на Visual Basic. Они применяются в тех случаях, когда встроенных средств Excel не достаточно.

По назначению их можно весьма условно разделить на следующие группы:

1. Макросы, обеспечивающие автоматизацию операций по обеспечению работы в Excel;

2. Макросы вычислительного характера;

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

Пусть для работы Вам необходимо произвести расчеты нескольких таблиц и затем экспортировать их в документ Word.

По умолчанию в Excel установлено:

• 
  тип шрифта – «Arial Cyr»;
  
• 
  размером шрифта – 10;
  
• 
  выравнивание для текстовой информации по левому краю, а для числовой – по правому краю.
  

После экспорта в Word таблицу приходится вручную перенастраивать под следующие параметры:

• 
  размер шрифта – 14;
  
• 
  тип шрифта – «Times New Roman»;
  
• 
  выравнивание содержимого таблицы – по центру.
  

---

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

Создание макроса в Excel состоит из следующих этапов:

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

Выделим нужную часть таблицы и выполним команды:

Сервис > Макрос > Начать запись > В появившемся окне запроса о параметрах макроса указать только осмысленное имя макроса (например, «Настройка») > Ok.

Система перейдет в режим записи макроса. Здесь необходимо очень аккуратно выполнить все необходимые команды.

В данном случае:

• 
  установить размер шрифта, равный 14;
  
• 
  установить тип шрифта «Times New Roman»;
  
• 
  установить выравнивание по центру.
  

После этого тут же остановить запись: Сервис > Макрос > Остановить запись.
2. Обеспечение запуска макроса.

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

Сервис > Настройка > В окне «Настройка» выбрать закладку «Команды» > В списке категорий выбрать категорию «Макросы» > В списке команд выбрать команду «Настраиваемая кнопка» и перетащить ее на панель инструментов > Не закрывая окна «Настройка» установить указатель мыши на только что перетащенную кнопку > Щелкнуть правой кнопкой мыши > В открывшемся меню выбрать пункт «Назначить макрос» > Из списка макросов выбрать макрос «Настройка».
Примечание

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

После оформления кнопки окно «Настройка» закрыть.
3. Проверка действия макроса

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

Сам текст макроса можно просмотреть, если выполнить команды:

Сервис > Макрос > Макросы > Выбрать нужный > Изменить > Система перейдет в редактор VisualBasic, в котором будет представлен текст выбранного макроса.

Для рассматриваемого примера должно появиться примерно следующее:
Sub Настройка()

With Selection.Font

.Name = "Times New Roman"

---

.Size = 14

.Strikethrough = False

.Superscript = False

.Subscript = False

.OutlineFont = False

.Shadow = False

.Underline = xlUnderlineStyleNone

.ColorIndex = xlAutomatic

End With

With Selection

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlBottom

.WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

End Sub
Здесь все команды настройки записаны в виде команд Visual Basic.

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

Точно такой же макрос и с точно таким же вариантом запуска можно создать и в Word.
4.2. Вычислительные макросы
Создание подобных макросов требует от пользователей наличия у них определенных навыков программирования в Visual Basic for Application. Данное требование обычно не предъявляется к студентам экономических специальностей. Поэтому приводимые далее примеры являются относительно несложными.
4.2.1. Пример 1. Расчет точки безубыточности

Описание задачи выглядит следующим образом:

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

– себестоимость выпуска одного изделия равна С руб.;

– цена реализации изделий равна S руб.

Тогда:

– затраты на производство V изделий будут равны:
Z = N + C * V (4.1)

– выручка от продаж будет составлять:

P = S * V (4.2)

Производство станет безубыточным в том случае, когда выручка от продаж превзойдет затраты на производство. Необходимый для этого объем производства можно определить из условия равенства уравнений 4.1 и 4.2.
N + C * V = S * V (4.3)

Из уравнения 4.3 находим минимально необходимый объем выпуска:
V = N / (S – C) (4.4)

Возможный интерфейс расчетов приведен в табл. 4.1.

Таблица 4.1.

Интерфейс программы расчета точки безубыточности

	A	B	C	D	E
1
2	Начальные затраты	70000
3	Себестоимость	50
4	Цена реализации	150
5	Точка безубыточности	700
6
7
8		Объем выпуска	Затраты	Выручка
9		0	70000	0
10		70	73500	10500
11		140	77000	21000
12		210	80500	31500
13		280	84000	42000

---

От пользователя требуется ввести в ячейки B2:B4 исходные данные и затем щелкнуть по кнопке «Расчет».

В результате в ячейку B5 должно быть выведено значение точки безубыточности, а в ячейки B9:D29 - результаты более детальных расчетов. На основе данных ячеек B9:D29 должен автоматически строиться график – рис. 4.1.


Рис. 4.1. Графическое представление результатов расчетов

в задаче о точке безубыточности

Для обеспечения расчетов необходимо выполнить следующие шаги.

1. 
   В соответствии с табл. 4.1 ввести на лист Excel необходимые сопроводительные надписи.
   
2. 
   Создать командную кнопку.
   

Для этого вызывается панель инструментов VisualBasic (Вид > Панели инструментов > VisualBasic) и на ней активизируется кнопка «Элементы управления». На появившейся панели выбирается элемент «Кнопка» и рисуется в нужном месте экрана.

Для смены надписи на кнопке:

– щелкнуть по ней правой кнопкой мыши и в появившемся меню выбрать пункт «Свойства»;

– в окне свойств (Properties) выбрать свойство Caption (надпись) и исправить ее на слово «Расчет».

3. 
   Написать текст макроса для кнопки.
   

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

– щелкнуть правой кнопкой мыши по нарисованной кнопке и в появившемся меню выбрать пункт «Исходный текст»;

– система перейдет в редактор VisualBasic, в котором будет пустая заготовка макроса:

Private Sub CommandButton1_Click()

End Sub

– ввести в нее следующий текст:
Private Sub CommandButton1_Click()

N = Range("B2") ‘ Из ячеек считываются

C = Range("B3") ‘ исходные данные

S = Range("B4") ‘

V = N / (S - C) ‘ Рассчитывается точка безубыточности

Range("B5") = V ‘ и выводится в ячейку B5

Vmax = 2 * V ‘ Диапазон расчета

h = Vmax / 20 ‘ Шаг расчета

k = 8 ‘ Номер строки

For V = 0 To Vmax Step h

k = k + 1

Cells(k, 2) = V

Cells(k, 3) = N + V * C

Cells(k, 4) = V * S

Next

End Sub
Внимание!! Очень важно!!

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

4. 
   Активизировать кнопку «Расчет».
   

Для этого необходимо:

---