2015-01-30
382
Лабораторная работа № 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 календарь будет автоматически отображаться на экране.
