2015-01-30
343
Лабораторная работа № 1
«Программа Microsoft Excel и её возможности».
Количество часов: 2
Цель работы:
Целью лабораторной работы является ознакомление студентов с электронными таблицами Excel, их возможностями.
Рабочее задание.
1. Создать таблицу «Клиенты» на листе «Сведения о клиентах» с шапкой:
| Номер клиента | Фамилия | Имя | Отчество | Год рождения | Место работы | Паспорт | Адрес |
2. Заполнить таблицу данными, используя средство Форма (не менее 20-ти строк).
3. На втором листе «Сведения о машинах» создать еще одну таблицу «Машины» с шапкой:
| Код машины | Марка | Номер | Цвет | Длина пробега на момент проката машины (км) | Год выпуска | Стоимость в сутки |
и также заполнить данную таблицу данными, используя для этого форму (не менее 15 строк).
4. Создать два дополнительных листа. Первый с названием - «Оформление заказа», второй – «Анализ данных».
5. На листе «Оформление заказа» создать таблицу с названиями столбцов: Код заказа, Код заказчика, Заказчик, Код машины, Марка, Стоимость в сутки, Дата проката, Дата предполагаемого возврата, Дата фактического возврата, Стоимость заказа, Доплата, Всего за заказ.
6. Для заполнения столбцов Заказчик, Марка, Стоимость в сутки использовать функцию ПРОСМОТР().
7. Столбец Стоимость заказа заполнять как количество дней проката (Дата предполагаемого возврата - Дата проката) помноженное на стоимость проката в сутки.
8. Столбец Доплата определить с помощью функции ЕСЛИ(), смысл которой заключается: если столбец Дата фактического возврата пуст, то Доплата = 0, иначе Доплата будет равна произведению разницы (Дата фактического возврата – Дата проката) и стоимости проката в сутки за вычетом Стоимости заказа.
9. Столбец Всего определить как сумму Стоимости заказа и Доплаты.
10. Заполнить таблицу, расположенную на листе «Оформление заказа» данными (не менее 10 строк).
11. На листе «Анализ данных» создать сводную таблицу, где будут отражаться данные о количестве взятия в прокат каждой машины.
12. На основе данных сводной таблицы построить диаграмму (круговую, так как используется только один ряд данных).
Порядок выполнения работы:
1. Открыть электронные таблицы Пуск – Программы – Microsoft Excel.
2. В первой строке листа определить заголовки столбцов, соответствующих порядку и названиям пункта задания №1.
3. Для добавления данных в таблицу необходимо:
3.1. Выделить заголовки столбцов;
3.2. Пункт строки меню Данные – Форма. Нажать кнопку ОК.
3.3. Через предложенную форму записать 20 строк.
4. Щелкнув два раза по названию листа «Лист1», переименовать лист в «Сведения о клиентах».
5. Во втором листе определить заголовки столбцов, соответствующих порядку и названиям пункта задания №3 и заполнить таблицу посредством формы.
6. Щелкнув два раза по названию листа «Лист2», переименовать лист в «Сведения о машинах».
7. В третьем листе создать таблицу с заголовками столбцов, соответствующих порядку и названиям пункта задания №5.
8. Подготовить некоторые столбцы к автоматическому заполнению данных:
8.1. Столбец «Заказчик» должен заполняться посредством функции ПРОСМОТР(). Вызвать Мастер функции (f(x)) – категория Ссылки и массивы – ПРОСМОРТ(). Выбрать первую строку аргументов (Искомое_значение;Просматриваемый_вектор;Вектор_результатов). Нажать ОК. Искомое значение – это то, которое нам надо найти на другом листе. В нашем случае – это B2 (код заказчика). Просматриваемый вектор – это массив, где мы будем искать выбранный код заказчика. Этот массив находится на листе «Сведения о клиентах» А2:А21. Данный массив надо обязательно зафиксировать (поместить курсор между буквой А и цифрой 2, нажать клавишу F4, потом поместить клавишу между А и 21 и тоже нажать клавишу F4). Вектор результатов – это массив, отображающий фамилии клиентов по соответствующему коду заказчика (B$2$:B$21$). Массив также зафиксировать. Должна получиться следующая картина (рисунок 2.1.)
Рисунок 2.1.
8.2. Аналогичным способом заполнить столбцы «Марка» и «Стоимость в сутки» (снова используя функцию ПРОСМОТР()). Массивы использовать с листа «Сведения о машинах».
8.3. Заполнить столбец «Стоимость заказа», сформировав произвольную формулу, соответствующую пункта задания №7: =(«Дата предполагаемого возврата» - «Дата заказа»)* «Стоимость в сутки».
8.4.
 |
Заполнить столбец «Доплата», используя функцию ЕСЛИ(). Вызвать мастер функций – категория Логические – ЕСЛИ(). Функция ЕСЛИ() содержит 3 аргумента: логическое выражение; значение_если_истина; значение_если_ложь. В аргументе «логическое выражение» записывают условие, которое проверяется на предмет истинности или ложности. В нашем случае, такое условие звучит: если поле столбца «Дата фактического возврата» пусто, то «Доплата» должна быть равной 0, если поле столбца «Дата фактического возврата» заполнено, то «Доплата» будет вычисляться как («Дата фактического возврата» - «Дата проката»)*«Стоимость в сутки»-«Стоимость заказа». Функция после ввода всех аргументов, должна выглядеть как на рисунке 2.2.
Рисунок 2.2.
8.5. Столбец «Всего за заказ» вычисляется как сумма значений столбцов «Стоимость заказа» и «Доплата».
9. Переименовать четвертый лист вашей рабочей книги как «Анализ данных».
10. Создать на листе «Анализ данных» сводную таблицу:
10.1. Пункт меню Данные – Сводная таблица;
10.2. Выбрать параметр «в списке или базе данных Microsoft Excel» - Далее;
10.3.
 |
Указать диапазон – на листе «Оформление заказов» выделить столбец «Марка» вместо с заголовком (рисунок 2.3.) – Далее;
Рисунок 2.3.
10.4. Перетащить название «Марка» в место расположения Строка и второй раз в область Данные (рисунок 2.4.) – Далее;
 |
Рисунок 2.4.
10.5. Оставить таблицу на существующем листе и нажать кнопку Готово.
11. Создать диаграмму:
11.1. Пункт меню Вставка – Диаграмма;
11.2. Выбрать тип диаграммы – Круговая;
11.3. Выбрать один из вариантов круговой диаграммы, Далее;
11.4. Поставив курсор на строку диапазон, выделить всю сводную таблицу. Появится изображение будущей диаграммы. Нажать кнопку Далее.
11.5. Подписать диаграмму – Долевое соотношение заказов.
11.6. На вкладке подписи данных установить галочку напротив параметра Доли. Нажать кнопку Готово.
11.7. Щелкнуть по диаграмме правой кнопкой и в контекстном меню выбрать команду – Скрыть кнопки сводной таблицы.
Заменить содержимое ячеек с датами календарем.
Наша задача - добавить в книгу Excel пользовательскую форму (окно) с автоматическим календарем. Вот такое:
Окошко будет появляться при нажатии сочетания клавиш и после выбора в нем нужной даты - она попадает в текущую ячейку листа. Просто и красиво!
Делаем!
Откройте редактор Visual Basic через меню Сервис - Макрос - Редактор Visual Basic (Tools - Macro - Visual Basic Editor).
Создайте в нем новую пустую пользовательску форму, выбрав в меню Insert - User form.
Автоматически должна появиться панель Toolbox:
Если не появилась - жмите в меню View - Toolbox.
На этой панели представлены различные управляющие элементы окон: кнопки, списки, счетчики и т.д. Но нам нужен элемент (календарь), которого пока на панели не видно. Чтобы добавить его, щелкните по серому фону панели правой кнопкой мыши и выберите Additional Controls. Появится вот такое окно:
В нем надо найти и отметить Элемент управления Календарь 11.0 (Calendar Control 11.0). Версии могут отличаться (11.0, 10.0 и т.д.) в зависимости от версии Microsoft Office, это несущественно. После нажатия на ОК на панели появится новая кнопка - Calendar:
Щелкните по ней, а затем нарисуйте календарь на поле формы, удерживая нажатой левую кнопку мыши:
При желании подправьте размеры формы и календаря и введите текст заголовка окна в панели свойств слева.
Теперь переключитесь в режим ввода программного кода созданной формы (меню View - Code) и скопируйте туда этот текст:
Private Sub Calendar1_Click()
ActiveCell = Calendar1.Value
ActiveCell.NumberFormat="dd/mm/yy"
End Sub
Private Sub UserForm_Activate()
Me.Calendar1.Value = Date
End Sub
Private Sub Calendar1_Click() ActiveCell = Calendar1.Value ActiveCell.NumberFormat="dd/mm/yy" End Sub Private Sub UserForm_Activate() Me.Calendar1.Value = Date End SubТеперь надо заставить Excel показывать нам созданную форму, когда мы этого захотим. Для этого вставьте новый модуль (Insert - Module) и скопируйте туда этот текст:
Sub ShowCalendar()
UserForm1.Show
End Sub
Sub ShowCalendar() UserForm1.Show End SubОсталось закрыть редактор Visual Basic и, вернувшись в Excel, назначить созданному макросу ShowCalendar любое подходящее сочетание клавиш (меню Сервис - Макрос - Макросы - кнопка Параметры).
P.S.
Если у Вас установлена не полная версия Office, то элемента управления Календарь может не оказаться в списке Additional Controls. Тогда скачайте этот файл, поместите его в папку C:\Windows\system32 и зарегистрируйте новый элемент управления через меню Пуск - Выполнить, далее набрать Regsvr32 c:\windows\system32\mscal.ocx и нажать ОК.
(За дополнение спасибо Vadim)
P.P.S.
Если Вы хотите, чтобы календарь автоматически появлялся на экране, когда пользователь выделяет определенные ячейки (диапазон) на листе, то щелкните правой кнопкой мыши по ярлычку этого листа и выберите Исходный текст (View Source). В открывшееся окно редактора Visual Basic скопируйте следующий код:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("A1:A20"), Target) Is Nothing Then
UserForm1.Show
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Not Application.Intersect(Range("A1:A20"), Target) Is Nothing Then UserForm1.Show End IfEnd SubТеперь при выделении любой ячейки из диапазона А1:А20 календарь будет автоматически отображаться на экране.
