Усовершенствование таблицы




Созданная таблица обладает недостатком, а именно тем, что является преимуществом электронных таблиц - наличием формул. Почему же недостаток? Формулы содержатся в диапазоне ячеек I10:J5000 и О10:Q5000, то есть их около 25 тысяч. Это не только "утяжеляет" файл, но и может доставить неприятности. Например, случайное и незамеченное удаление формулы может внести ошибки в расчеты.

В качестве примера опишем возможную ситуацию, которая неоднократно встречалась на практике. Например, при расчете суммы комиссионных при конвертации валюты, в ячейку N17 введено не числовое значение, а формула:

=M16*0,5%

которая производит умножение суммы валюты, отправляемой на продажу, на процент комиссионных за эту услугу. Суммы оборотов по поступлению и списанию денежных средств в ячейках I6 и J6 равны соответственно 2400000 и 2412000 рублей. Произведите сортировку списка, например, по убыванию, по наименованию операции. Полученный результат суммирования поменяет значение на 2400000 и 2406000 рублей соответственно. Это произошло потому что ячейка N17 в результате сортировки находится по адресу N16, и формула в ней изменилась на:

=M15*0,5%

а в ячейке М15 нет никакого значения.

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

Макрос определения курса валют на указанную дату и расчета эквивалента денежных средств по курсу

Макросы ввода формул записываются уже после ввода вышеописанных формул в ячейки. Задача макроса Курс заключается в следующем:

  • открыть файл Курс;
  • вернуться в файл ДенежныеПотоки;
  • ввести в столбце L формулу определения курса указанной валюты на указанную даты и заменить формулы на определенные с помощью формул значения;
  • закрыть файл Курс;
  • ввести в диапазон ячеек I10:J5000 формулу расчета эквивалента денежных средств по определенному курсу.

Для записи макроса Курс (рис. 17.6.) выполните следующее:

с помощью кнопки Открыть или выполнив команду Файл/Открыть откройте диалоговое окно Открытие документа; перейдите на диск D и в директории Самоучитель откройте файл Курс дважды щелкнув по его имени правой кнопкой мыши; выполнив команду Окно/ДенежныеПотоки вернитесь в книгу ДенежныеПотоки и выделите область ячеек L10:L5000; выделив формулу определения курса валют в строке формул, нажмите комбинацию клавиш Ctrl+Enter для одновременного ввода формул во все ячейки выделенного диапазона; не перемещая табличного курсора, скопируйте содержимого выделенного диапазона в буфер обмена и вызовите диалоговое окно Специальная вставка, в котором в области Вставить выделите переключатель Значения; выполните команду Окно/Курс и закройте файл Курс, выполнив команду Файл/Закрыть или нажав кнопку закрытия файла; введите формулы в диапазон ячеек I10:J5000. Заменять формулы на вычисленные ими значения временно не следует. В предыдущем случае это было необходимо с позиции повышения быстродействия выполнения подпрограммы; перейдите в Редактор Visual Basic и отредактируйте текст кода VBA.

Рис. 17.5. Подпрограмма Курс

Макрос расчета сумм налогов

Запись макроса РасчетНалогов (рис. 17.6.) заключается в последовательном вводе формул в ячейки столбцов O, P и Q.

Рис. 17.6. Подпрограмма РасчетНалогов

Макрос расчета итоговых сумм

Макрос РасчетСумм производит ввод формул:

  • суммирования оборотов по поступлению рассчитанных денежных средств по курсу в ячейку I6 и вставке ее в ячейки J6 и O6:Q6. Для одновременной вставки формул в диапазон ячеек J6,O6:Q6 после ввода формулы в ячейку I6 и копирования ее содержимого выделите ячейку I6, и нажав и удерживая клавишу Ctrl выделите мышью диапазон ячеек O6:Q6, произведите вставку скопированного;
  • суммирования по принципу "если" по наименованию валюты в ячейке М5 и последующего копирования и вставки содержимого этой ячейки в диапазон M5:N6.

Далее для замены всех формул на вычисленные ими значения на рабочем листе ИсхДанные нажатием на кнопку Выделить весь лист произведите выделение всего рабочего листа (Cells) и через диалоговое окно Специальная вставка замените формулы на значения. Отредактируйте текст подпрограммы.

Немаловажная деталь при редактировании, на которую хотелось бы обратить внимание читателя. Обратите внимание на строки ввода формул, например, фрагмент: Range("I6,J6,O6:Q6").Formula. Во-первых, при вводе формул макрос не выделяет ячейки, в которые вводятся формулы, потому что при редактировании удален этот оператор (Select). Dо-вторых формулы одновременно вводятся в различные ячейки. Этим значительно увеличивается быстродействие выполнения макроса.

Рис. 17.7. Подпрограмма РасчетСумм

Соберем все макросы вместе

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

Рис. 17.8. Подпрограмма ПолныйРасчетИсхДанные

Макрос ввода текста наименования валют

Неудобство ввода наименования валют заключается во вводе текста наименования с клавиатуры. Это неудобно само по себе и возможен ввод ошибок. При расчете же эквивалентного денежного потока, формула расчета воспринимает введенное наименование валюты дословно и при вводе ошибочного текста он не будет понят. Макрос ввода наименования валюты должен вводить текст как в отдельно выделенную ячейку, так и в диапазон ячеек. Для записи макроса прежде чем начать запись, введите текст валюты в любую ячейку и выделите диапазон ячеек, начиная с этой ячейки. После начала записи макроса нажмите клавиши F2 и Enter. Записанная строка кода VBA:

Selection.FormulaR1C1 = "RUR"

показывает, что в выделенный диапазон записана формула ввода текста. Замените оператор формулы на оператор присвоения значения Value.

Рис. 17.9. Подпрограммы ввода текста наименования валют

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



Поделиться:




Поиск по сайту

©2015-2024 poisk-ru.ru
Все права принадлежать их авторам. Данный сайт не претендует на авторства, а предоставляет бесплатное использование.
Дата создания страницы: 2016-04-27 Нарушение авторских прав и Нарушение персональных данных


Поиск по сайту: