Вопросы для самоконтроля.

1. Что такое абсолютная и относительная ссылки на ячейки? Как они задаются?

2. Как возвести число в какую-либо степень? Как извлечь корень любой степени?

3. Перечислите способы копирования.

4. Как ввести натуральные числа, например с 220; 221; ….; 230 с помощью автозаполнения?

5. Как ввести числа с каким-нибудь шагом с помощью автозаполнения?

6. К какому краю ячейки по умолчанию прижимается текст, а какому числа?

7. Где задается символ разделения целой и дробной части числа?

Получить вариант поверочной работы.

Лабораторная работа №2

Макрокоманды и работа с ними.

Цель работы: Создание макрокоманд. Автоматическая запись макрокоманд (макросов). Запись повторяющихся действий с помощью макрокоманд. Выполнение макрокоманд. Если эта работа покажется слишком сложной, то ее можно пропустить.

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

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

В Excel включен язык программирования Visual Basic for Applications (VBA). При записи макроса с помощью макрорекодера путем выбора команды Сервис\Макрос\Начать запись можно создать разнообразные макросы не набирая код «вручную». Редактор Visual Basic можно вызвать: Сервис\Макрос\Редактор Visual Basic.

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

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

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

Создать 4 макроса автоматически выполняющие эти действия.

Порядок выполнения работы:

1. На листе1 в новой рабочей книге, по адресу А1 набрать название: Поставщики. Создать список Поставщиков (см. табл. 2.4.) по адресам A3:G13.

2. По адресу А15 скопировать название поля "Телефон", по адресу А16 внести любой номер телефона из списка.

Область А15:А16 отводится под область критерия. По адресу А18 позже будут заноситься результаты фильтрации.

3. Автоматическая запись макрокоманды для расширенного фильтра. 3.1. Поместить активную ячейку в любое место таблицы "Поставщики".

Идем в Меню/Сервис/Макрос/Начать Запись новой команды (Menu/Tools/Record New Macro). Появится окно "Запись макрокоманды" (Record Macro). Здесь можно задать:

• Имя макрокоманды, например Macro1 или filter, без пробелов;

• Сочетание клавиш для вызова макроса, например, <Ctrl Shift А>;

• Описание макрокоманды до 255 символов, например, "Выбор информации по № телефона с помощью расширенного фильтра" <OK>

С этого момента все ваши действия будут записаны в виде макрокоманд в подпрограмме.

3.2. В результате мы перейдем в режим записи макрокоманды. На экране должна появиться панель с двумя кнопками: левая кнопка для завершения макроса, а правая для останова (прерывания) макроса или относительного адреса (Не делать ничего лишнего! Все попадет в макрос!!!). Если панель не появилась, то все действия можно дублировать через меню.

Разместим активную ячейку по адресу А18 а с помощью клавиатуры (или мыши) выделим область A18:G19. Затем идем в Меню/Правка/Очистить/.Все (Menu/Edit/Clear/All)

Курсор можно становить в любом месте таблицы.

Далее идем в Меню/Данные/Фильтр/Расширенный фильтр (Menu/Data/Filter/ Advanced Filter). Перед нами окно расширенного фильтра:

• Ставим точку <•> в "Скопировать результат в другое место"

• Отмечаем область $А$3: $G$ 13

• Отмечаем область критериев (диапазон условий) $А$ 15 :$А$ 16

• Результат копировать в А18 <ОК>

3.4. Нажать кнопку остановки записи макроса или выполнить: Меню/ Сервис/ Макрос/остановить запись (Menu/Tools/Macro/Stop Recording).

3.5. Убедиться в том, что Ваш макрос работает верно: по адресу А16 набрать любой

Номер телефона из списка, далее: Меню/Сервис/Макрос/Макросы

(Menu/Tools/Macro/Macros <Run>) выбрать макрос с Вашим именем Macro1 или filter, команда <Выполнить>.

3.6. Создадим свою кнопку в панели инструментов для выполнения макроса: Меню/ Вид/Панели инструментов/Настройка (Menu/View/Toolbars/ Customize, Commands), выбрать Macros кнопку Custom Button) вкладка "Команды" выбрать: Макросы, настраиваемая кнопка. Эту кнопку с помощью нажатой левой клавиши мыши перетащить в панель инструментов.

После того, как кнопка перенесена в панель инструментов, становится доступной команда <Изменить выделенный объект> (Modify Selection). Здесь Вы можете: задать основной стиль; задать имя; выбрать вид кнопки с помощью стандартного значка: «Выбрать значок для кнопки» (Change button Image) или «Изменить значок на кнопке» (Edit button Image) (т.е. нарисовать свою уникальную кнопку). Кроме того, Вы можете выбрать стиль кнопки, которая будет содержать только изображение кнопки или только текст, или и то и другое.

С помощью команды «Назначить Макрос» (Assign Macro) выбранной кнопке можно присвоить имя макроса. Всплывающую подпись под Вашей кнопкой вызова макроса можно задавать следующим образом: Щелкнуть правой клавишей мыши по изображению (т.е. вызвать контекстное меню), там выбрать пункт Настройка(Customize), затем щелкнуть по кнопке и выбрать пункт Изменить выделенный объект (Modify Selection) и в пункте Имя (Name) задать имя, например <&Фильтр> .

В результате должно получиться следующее:

Таблица 2.4

	А	B	C	D	E	F	G
1	Поставщики
2
3	№	Имя	Фамилия	Телефон	Страна	Фирма	Заметки
4	1001	Юлия	Муравчик	3222232	Китай	Asus	должник
5	1002	Настя	Медведева	1125656	Дания	Philips	надежен
6	1003	Елена	Шаронова	2114545	Япония	Panasonic	должник
7	1004	Наташа	Яксон	3312222	Корея	Aiwa	надежен
8	1005	Арина	Кулагина	5152424	Финляндия	Nokia	надежен
9	1006	Алексей	Пашкевич	6137777	Швеция	E-lux	должник
10	1007	Никита	Дугинов	2456666	Австрия	Grundic	должник
11	1008	Олег	Савелов	3548888	Германия	Bosch	надежен
12	1009	Марина	Шуманская	2346767	Китай	Funai	надежен
13	1010	Иван	Рубин	3219999	Япония	Sony	должник
14
15	Телефон
16	6137777
17
18	№	Имя	Фамилия	Телефон	Страна	Фирма	Заметки
19	1006	Алексей	Пашкевич	6137777	Швеция	E-lux	должник

Результат показать преподавателю и сохранить в свою папку.

4. С помощью двух новых макросов записи будут распределяться по соответствующим листам рабочей книги: надежные поставщики на один лист, а должники «в черный список » на другой лист (т.е. повторяющиеся действия: выделение отфильтрованной записи, копирование ее в буфер, и вставка из буфера на определенное место, должны производиться автоматически с помощью макроса).

4.1. Прежде чем приступить к созданию 2 и 3 макросов, необходимо:

• На листе2 по адресу А1 задать название: Надежные поставщики

• По адресам A3: G3 скопировать названия полей таблицы из листа1. Ширину столбцов задать такую же, как и на листе1 с помощью копирования форматов

• По адресу I3 на листе2 ввести: Число записей

• По адресу I4 вызвать мастер функций и найти функцию: =Счет(Лист2!А4:А24). Функция «Счет» определяет, сколько ячеек заполнено числами в указанной области, величина области выбрана больше чем число записей в исходной таблице. Значение ячейки I4 первоначально будет равно 0, а при добавлении новых записей на лист надежных поставщиков, это значение будет изменяться.

(Для нерусифицированного пакета выбирается функция: =Counta(Sheet2!A4:A24)

• Аналогично на листе3 создать таблицу: Поставщики - должники и выбрать функцию: =Счет(Лист3!А4:А24)

Если нужно подсчитать количество числовых и текстовых ячеек, то нужно использовать функцию Счетз()

4.2. Создать следующий макрос: Меню\Макрос\Макросы, указать свой макрос1 и выбрать пункт: Войти или Изменить (Menu/Tools/Macro/Macros пункт Edit), появится окно редактора Visual Basic. Установить курсор на следующей строке после команды End Sub и ввести исходный текст 2-го макроса:

Sub Macro2()

' копирование записей на лист надежных поставщиков

n = 0 ' предварительно обнуляем число записей

Sheets("Лист1").Select 'выбираем лист1

Range("a19:g19").Select ' помечаем область

Selection.Copy 'копируем выделенную область в буфер

Sheets("Лист2").Select 'выбираем лист2

Range("I4").Select ' помечаем адрес

n = Selection.Value 'присваиваем n значение ячейки I4

'n- соответствует количеству скопированных записей

Worksheets("Лист2").Cells(n + 4, 1).Select ' встаем на первое пустое место

ActiveSheet.Paste 'вставляем информацию из буфера

Sheets("Лист1").Select

Range("a16").Select

End Sub

Проверить работу макроса.

4.3. По аналогии создать третий макрос. Скопировать Macro2 и заменить название листов на нужные (т.е. Лист2 на Лист3)

В результате получим 3 макроса:

Sub Macro1()

' Macro1 Макрос by Admakina Olga

' Фильтрация по номеру телефона

'

' Сочетание клавиш: Ctrl+Shift+A

'

Range("A18:G19").Select

Selection.Clear

Range("A3:G13").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _

"A15:A16"), CopyToRange:=Range("A18"), Unique:=False

End Sub

Sub Macro2()

' Macro2 Макрос

' копирование записей на лист надежных поставщиков

'

n = 0 ' предварительно обнуляем число записей

Sheets("Лист1").Select 'выбираем лист1

Range("a19:g19").Select ' помечаем область

Selection.Copy 'копируем выделенную область в буфер

Sheets("Лист2").Select 'выбираем лист2

Range("I4").Select ' помечаем адрес

n = Selection.Value 'присваиваем n значение ячейки I4

'n- соответствует количеству скопированных записей

Worksheets("Лист2").Cells(n + 4, 1).Select

' встаем на первое пустое место

ActiveSheet.Paste 'вставляем информацию из буфера

Sheets("Лист1").Select

Range("a16").Select

End Sub

Sub Macro3()

'

' Macro3 Макрос

' копирование записей на лист поставщиков - должников

'

n = 0 ' предварительно обнуляем число записей

Sheets("Лист1").Select 'выбираем лист1

Range("a19:g19").Select ' помечаем область

Selection.Copy 'копируем выделенную область в буфер

Sheets("Лист3").Select 'выбираем лист3

Range("I4").Select ' помечаем адрес

n = Selection.Value 'присваеваем n значение из ячейки I4

'n- соответствует количеству скопированных записей

Worksheets("Лист3").Cells(n + 4, 1).Select

' встаем на первое пустое место

ActiveSheet.Paste 'вставляем информацию из буфера

Sheets("Лист1").Select

Range("a16").Select

End Sub

Если при работе макросов возникли ошибки, то исправить их можно следующим образом: Сервис\Макрос\Макросы выбрать нужный Макрос (Tools/Macro/Macros,/Edit или Step Into) Войти или Изменить.

Проверить работу макросов.

Результат показать преподавателю и сохранить в свою папку.

5. Следующий макрос будет автоматически разносить записи о поставщиках по разным страницам в соответствии с информацией, помещенной по адресу G19.

Sub Macro 4 ()

'Автоматическое распределение записей:

Sheets("Лист1").Select

If Range ("G19").Text = "должник " Then

Call Macro3

Else

IF Range ("G19") = "надежен " Then

Call Macro2

End If

End If

End Sub

Вынесем кнопку, отвечающую за выполнение данного макроса на панель инструментов.

Результат показать преподавателю и сохранить в свою папку.

6. Самостоятельно создать пятый макрос, который объединит в себе результаты работы всех макросов, т.е. будет фильтровать нужные записи и автоматически распределять их по нужным страницам.

7. Изменить название ярлычков листа на "Поставщики" вместо «Лист 1», «Лист 2» на "Надежны", «Лист 3» на "Должники" и внести соответствующие изменения в макросы.

Результат показать преподавателю и сохранить в свою папку.