Лекция №13

ТЕМА: Программирование в Excel

 

1. ЦЕЛЬ РАБОТЫ

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

 

2.1. Коротко о главном

Ссылки абсолютные и относительные

Если по науке, то относительные ссылки, это те ссылки, которые мы получаем по умолчанию. А абсолютные, когда к ним мы добавляем знак $ доллара. Этот знак не позволяет изменяться содержимому при копировании. Любая ссылка состоит из названия столбца и номера строки. Столбцы обзываются буквами латинского алфавита, строки – цифрами.

Давайте рассмотрим пример. Создаем новый документ Excel. В ячейку А1 заносим цифру 1. В ячейку А2 заносим цифру 2. Нажимаем e. Один раз нажимаем стрелку вверх с клавиатуры. Нажимаем и держим нажатой клавишу j и не отпуская ее еще раз стрелку вверх. Выделили две ячейки. Ухватились мышкой (мелким черным крестом) за правый нижний угол и раскопировали ряд до А10. В ячейку  В1 заносим цифру 12. В ячейку С1 пишем формулу. Ставим в нее знак = щелкаем мышкой в ячейку А1, потом ставим знак умножить (звездочка с клавиатуры в правом верхнем углу через одну клавишу правее от ]). После этого щелкаем мышкой в ячейку В1 и нажимаем e . В ячейке С1 появилась цифра 12 – результат умножения единицы на 12. Становимся в ячейку С1, Цепляем мышкой за нижний правый угол (черным крестиком) и тянем вниз до ячейки С10. Протянули и бросили. Что видим? Сначала стоит 12, а потом нолики. Правильно. А хотелось бы видеть результат умножения первого столбца на цифру 12. Пройдитесь по столбцу С и посмотрите что там стоит. Компьютер добросовестно перемножим каждую ячейку первого столбца на соответствующую ячейку второго. А надо было весь первый столбец перемножить лишь на одну ячейку В1 в столбце два. Компьютеру мы ничего на эту тему не сказали, вот он и сделал так, как нас понял. Хотите по другому? Нет проблем. Становимся в ячейку С1. В строке формул (которая располагается под панелью инструментов форматирования чуть выше названия столбцов) мы видим содержимое формулы, то есть видим =А1*В1. Щелкаем мышкой в этой строке между В и единицей. Щелкнули? Текстовой курсор заморгал между буквой и цифрой? Отлично. Теперь нажимаем на клавишу o на клавиатуре. Вот чудо, компьютер подрисовал знак доллара перед В и перед цифрой один. Готово. Жмем e. Стрелкой вверх с клавиатуры возвращаемся в ячейку С1, хватаем мышкой за нижний угол и опять раскопировали до С10. Вот теперь получилось то, что хотелось. Пока столбец выделен, нажмите знак автосуммы, и в ячейке С11 увидим результат 660. У Вас тоже так получилось? Прекрасно! Так вот – знак доллара это признак абсолютной ссылки он привязывает вычисления к конкретной ячейке и не позволяет связям менять ее значение (уйти с нее).

Составление реестра

Рассмотрим  другую ситуацию. Очень хочется создать бланк, какого либо документа. Заполнять его по одной форме, причем, чтобы он сам помнил порядковую нумерацию. Да еще видеть реестр заполненных бланков с основными сведениями из них. Много хочется? Ничего подобного. Для Офиса это элементарно. Сейчас покажем. Опять рассмотрим пример.

Давайте мы рассмотрим случай автоматизации составления актов на списание денежных средств по ООО «Радуга». Идея хорошая, и здесь может быть все, что хотите. Итак.

Пишем файл (документ) в который заносим бумажный эквивалент того, что хотим автоматизировать. В данном случае Акт на списание. Мы это сделали и вот как он выглядит: Акт на списание.  А у кого бумажный вариант, то он вот какой:

         Рассмотрим некоторые небольшие хитрости.

Во-первых, вся область акта, чтобы красивее она смотрелась на экране, залита белым цветом. Выделили всю область и нажали на кнопку Цвет заливки в панели инструментов Форматирование (сначала на черном треугольнике чуть правее этой кнопки и выбрали белый цвет).

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

В-третьих, обратите внимание на рисунок. Между строками 6 и 10; 12 и 20 есть скрытые спрятанные строки. На кухне работает три повара, и их фамилии и инициалы записаны в этих строчках, на втором участке записаны названия мероприятий, которые наиболее часто встречаются в жизни. Когда пользователь начнет писать то, что спрятано внизу, у него на экране выскочит уже готовый текст, с предложением его вставить в эту ячейку. Он нажимает e и далее писать не надо, все уже стоит так, как хочется.

В-четвертых, применено форматирование отдельных столбцов в денежном формате и как дата с определенным форматированием.

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

Вот как это выглядит в развернутом виде:

Заполняем акт, и после заполнения он должен выглядеть вот так:

            Итак, размножаем пустой бланк акта, сохраняем этот файл с разными именами. Ставим номер акта 2 и сохраняем под именем 2. Ставим номер акта 3 и сохраняем под номером 3. Таким образом, делаем столько актов, сколько их Вам может потребоваться на год. Примерно.  Размножили пустые бланки. Это полдела.

Теперь составляем файл реестра. Этот файл записываем в ту же папочку, где у нас лежат и файлы бланков.

Вот ссылка на него:

Реестр годовой, а у кого нет диска, вот фрагмент его внешнего вида:

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

Итак. Файл готов. Делаем автоматизацию. В ячейку А21 заносим 1, в А22  цифру 2. Выделили эти две ячейки. Раскопировали вниз на то количество, сколько актов Вы создали. Получили список порядковых номеров актов. Становимся в ячейку А21. Нажимаем на кнопку Вставить  гиперссылку в панели инструментов Стандартная и в открывшемся окне находим и указываем путь на акт №1, после чего нажимаем Ок. Цифра один в ячейке А21 стала синей и подчеркнутой. Это гиперссылка. Щелчок на ней левой клавишей мыши приводит к открытию файла Акт №1. Аналогично делаем гиперссылку на файл 2, для акта 2 и так до конца, сколько там у Вас актов. Поздравляю, это опять полдела. Нам нужно вытащить из этого акта в реестр шесть значений: Дату, Назначение, С представителем, Списать с Цаповой, Списать с Айнушева, ВСЕГО. Все нужные поля в акте имеют следующие адреса: В3, F12, A18, A20, D20, E19. Вы их нашли в акте? Далее, дело техники. На компьютере запущена одна  программа Microsoft Excel. Теперь давайте откроем 22 акт, для этого щелкнем по цифре 22 и этот акт откроется. У нас открыто два окна: 22 и акт годовой. Так? Отлично, если не так, делаем, чтобы было так, все лишнее закрываем. Становимся в ячейку В42 реестра и нажимаем клавишу равно (=) с клавиатуры. Переключаемся в другое окно (либо мышкой через панель задач, либо нажав не отпуская  a и один раз на клавишу | с клавиатуры, бросаем a) и там мышкой щелкаем (указываем) в ячейку В3. Нажимаем  e. Опять чудо? В реестре  появилась дата с акта (естественно, предварительно дата в акте должна стоять). Так? Отлично. В строке формул в реестре у Вас будет другая строка, поскольку, наверняка, путь к расположению файла будет другой. Мой путь виден на рисунке сверху. Далее идет повтор. В ячейке С42 реестра нажимаем =, щелкаем a + |, потом мышкой в ячейке F12, e . Если в акте была цифра, автоматически она будет перенесена в реестр. Именно это нам и надо. Заполняем всю 42 строку реестра. Отлично. Далее выделяем ячейки В42:G42.  Как? А это уже проходили. (Стали в ячейку В42, нажали j и не отпуская Шифта стрелку вправо до ячейки G42). Выделили, отлично. Разыскали внизу выделенной области маленький черный квадратик с правой стороны внизу, навели на него курсор мыши, вид указателя изменился со стрелки на тонкий черный крестик, нажали левую клавишу  и не отпуская ее тянем мышь вниз. Раскопировали этот диапазон на то количество строк, сколько у Вас актов. Сначала вниз, а потом то же самое вверх Думаете все? Ошибаетесь. Посмотрите, во всех ячейках реестра у Вас стоит  ссылка на один и тот же файл. Заполните акт и закройте его. Закройте реестр. Откройте реестр. Он весь заполнен данными из одного файла (22), так? А хотелось бы, чтобы из многих. Для этого надо внести изменения в реестр. Нет ничего проще. Выделяем весь диапазон, где должны быть внесены изменения В21:G121 (к примеру, если у Вас 100 актов). Выделили? Отлично! Жмем ПравкаЗаменить. Появляется окно Найти и заменить. Курсор подмаргивает Вам в поле Найти:, приглашая Вас вписать в это поле значение, пишем цифру 22, потом нажимаем | , и в поле Заменить на: ставим цифру 1. Готово? Отлично. Шесть раз нажимаем на кнопку Заменить, пока поле замены не перейдет на ячейку В22. Здесь поле Заменить на: исправляем с 1 на цифру 2 и еще 6 раз жмем Заменить. Когда замена дойдет до поля В23, исправляем 2 на 3 и еще 6 раз жмем Заменить и так до конца таблицы. Закрываем все. Открываем реестр. Щелкните по номеру 12 открывается файл - акт №12. Заполняем его. Закрываем акт и видим, что все, что Вы в него занесли у Вас уже лежит в реестре.  Удалите из акт 12 все ненужные значения. Можно ускорить эту работу через использование макросов.

Работа с макросами

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

Предполагаем, что Вы обычный компьютерщик средней руки, не знаете программирования и не «ас» английского языка. Тогда путь освоения макросов один: жмете СервисМакрос Начать запись. Выполняете последовательность действий, что- то там, на компьютере, а потом останавливаете макрос, либо специальной кнопочкой, либо тем же путем: СервисМакросОстановить макрос. Открыв вновь созданный макрос (удобнее всего сочетанием клавиш a + ). Вы анализируете, что у Вас получилось, и как он работает. После анализа вносите коррективы, запускаете его вновь и видите результат его работы. Устраивает, все отлично. Не устраивает, опять меняете тело макроса, пока не получите то, что хочется.

Вот, к примеру, макрос, который в автоматическом режиме расставляет гиперссылки (присваивает чему-то там, определенную гиперссылку):

Sub гипертекст()

' гипер Макрос

' Макрос записан 29.01.2005 (Березуцкий В.И. bvi@mail.ru)

On Error Resume Next

Dim m As Integer

Dim n As Variant

    n = 2

    For m = 22 To 325

          Cells(m, 1).Select

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=n & ".xls"

n = n + 1

    Next m

End Sub

         Разбирать его не хочется специалист поймет сам, кто не поймет, значит тому еще рано. С опытом разберетесь. А еще с помощью макросов можно назначить какое-либо действие на открытие (закрытие) файла. Пишется макрос и дается ему имя AutoOpen, вместо гипертекст. При открытии такого файла сработает то, что написано в теле макроса. Иногда бывает очень удобно для автоматизации делопроизводства, чтоб не задавать глупых вопросов.

3. ЗАДАНИЕ ДЛЯ САМОСТОЯТЕЛЬНОЙ РАБОТЫ

Откройте Excel. Скорее всего, у Вас в новой книге будет три листа. Сделайте двойной щелчок левой клавишей мыши на ярлычке листа, тот выделится черным цветом и напишите новое название листа – первый. Аналогично – двойным щелчком выделите название второго листа и дайте ему новое имя – второй. И третий раз выделяем лист 3 и переименовываем его в – третий. Добавьте в книгу еще три листа. Как? Элементарно. В строке меню жмем на слово Вставка и в выпавшем меню выбираем четвертую сверху строку – Лист. Лист вставился, и еще два листика вставляем. А потом даем им новые имена – четвертый, пятый и шестой. Сохраните книгу с именем Проба листов. Если Ваши листики вставились не по порядку, какие проблемы? Нажимаете левой клавишей мыши на названии «неправильного» листа и, не отпуская левой клавиши мыши, перемещаете этот лист вправо (влево), на свое место. Мелкий черный треугольник, над названием листов, поможет Вам сориентироваться, в каком месте надо отпустить левую клавишу мыши, чтобы лист оказался там, где Вам хочется. Навели порядок, умницы!!!

Переключились в первый лист и набрали в нем следующую таблицу:

Для изменения ширины столбца (В) надо подвести указатель мыши к границе между названиями столбцов, (В и С), и когда вид указателя мыши примет вид двунаправленной стрелки, нажать левую клавишу мыши и не отпуская ее подвинуть левую границу (В) вправо до нужного места, где и бросить левую клавишу мыши. А еще можно щелкнуть правой клавишей мыши в названии столбца (В), когда вид указателя примет вид черной жирной стрелки направленной вниз, весь столбец выделится синим цветом и выпадет дополнительное меню, в котором третья строка снизу – ширина столбца. Щелкаете по этой строке левой клавишей мыши и указываете цифру 40 и ОК. На листе второй пишите следующий текст:

и вставляете в него связи, как? Легко. Становитесь в ячейку А7, нажимаете знак равно с клавиатуры, мышкой идите в лист первый и щелкаете левой клавишей мыши в ячейке С4, после чего нажимаете e. В ячейке А7 появился нолик. Поставьте любую цифру в ячейку С4 на первом листе и полюбуйтесь, что она сразу же отразится в ячейке А7 второго листа. Прекрасно. Аналогично ставите связи по всем остальным ячейкам. Причем в ячейке В7 мы щелкнули правой клавишей мыши, выбрали Формат ячеек… и на вкладке Число выбрали Тип формата Дата другой, такой, какой нам понравился. В ячейке D5 и  F5, мы применили Автосумма. У Вас все получилось, отлично! А если нет, то  проделайте упражнение с начала.

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

4. КОНТРОЛЬНЫЕ ВОПРОСЫ

1                   Что могут и чего не могут макросы?

2                   Какие функции Excel Вы знаете, что можно с их помощью сделать?

3                   А как получить подсказку (помощь) по конкретной функции, ее работе?

4                   Вы смотрели примеры применения функции?