На этом шаге мы приведем несколько полезных советов, на которые будем опираться в дальнейшем.
Формулы промежуточных вычислений в списках
Довольно часто на практике приходится сталкиваться с тем, что в списки (или табличные базы данных) вводятся формулы, производящие промежуточные расчеты, с указанием в них ссылок на другие ячейки этого же списка. К чему это может привести - рассмотрим на простом примере.
На рисунке 1 в ячейку D6 введено числовое значение - 18.
Рис.1. Значение 18 в ячейке D6
После сортировки списка это числовое значение не изменилось (рисунок 2).
Рис.2. Значение 18 в ячейке D6 не изменилось
Предположим что числовое значение 18 получено, как результат умножения содержимого ячеек С4 и В8. Введите в ячейку D6 формулу умножения с указанием ссылок на эти ячейки (рисунок 3).
Рис.3. Список с введенной формулой умножения значений, содержащий ссылки на ячейки С4 и В8
Для того чтобы проконтролировать - в каком месте списка после сортировки окажется ячейка с этой формулой, задайте шрифту этой ячейки другой цвет.
Если произвести операцию любой сортировки списка, например, по возрастанию по столбцу D, то полученный результат показан на рисунке 4.
Рис.4. Результат сортировки списка с введенной формулой со ссылками на другие ячейки списка
В столбце D в конце списка появилось значение 0, которого раньше не было. Поместите табличный курсор на ячейку D11, в которой стало находиться прежнее содержимое ячейки D6 и обнаружите, что введенные ранее адреса ссылок изменились. Последовательность выполнения Excel операции сортировки была следующая:
- результат умножения формулы в ячейке D6 равный значению 18 при сортировке был воспринят как значение;
- после сортировки произошел перерасчет формулы с изменившимися ссылками на ячейки;
- полученный результат в виде значения 0 отобразился на экране монитора.
Если список был довольно большой, формулы вводились неоднократно, и подобные ошибки не были обнаружены своевременно, то через некоторое время исправить их уже не представляется возможным. Тогда всю работу по созданию списка необходимо начинать сначала.
Поэтому если создаваемая таблица будет относиться к спискам и, как следствие, с ее данными возможно проведение операций сортировки, то не вводите в нее формулы промежуточных расчетов. А если такая необходимость присутствует, то после проведения вычисления замените ее на вычисленной значение.
Или учтите что при необходимости ввода таких формул, вычисления должны проводиться только с содержимым ячеек, находящимися в одной строке списка.
Применение диалогового окна Специальная вставка
При создании компьютерных моделей или других расчетных таблиц в Excel, довольно часто производится последующая замена формул, производящих вычисления, на определенное ими значение. Или же вычисленное значение служит операндом при проведении арифметических вычислений с другими значениями.
Для того чтобы не производить операцию замены формул на значения вводом значений с клавиатуры, довольно часто будет использоваться диалоговое окно Специальная вставка (рисунок 5).
Рис.5. Диалоговое окно Специальная вставка
Это диалоговое окно вызывается командой Правка | Специальная вставка или выбором строки Специальная вставка в контекстном меню.
Диалоговое окно Специальная вставка вызывается только после выполнения команды Копировать. Команда Вырезать для этого случая не подходит.
В области Вставить диалогового окна находятся следующие переключатели:
- Все. Осуществляется вставка всех параметров скопированной области (ячейки). Эта операция ничем не отличается от обыкновенной операции копирования и последующей вставки из буфера обмена.
- Формулы. Производит вставку только формул. Основное преимущество этого метода состоит в том, что при вставке из буфера обмена не нарушается форматирование ячеек вставки и не изменяются примечания, которые находятся в них.
- Значения. Выполняет вставку только значений. Этот метод вставки полезен в тех случаях, когда необходимо зафиксировать или перенести какие-то результаты расчетов в виде значений, прежде чем изменять исходные данные. Метод не нарушает форматирования ячеек и примечаний.
- Форматы. Производит вставку только форматов. Это дает возможность при вставке из буфера обмена не затрагивать значения, формулы и примечания, содержащиеся в ячейках области вставки.
- Примечания. Вставка только примечаний.
Соответствующие переключатели области Операция диалогового окна Специальная вставка позволяют выполнять операции сложения, вычитания, умножения и деления значений или формул, находящихся в скопированной ячейке, со значениями или формулами ячейки, в которую производится вставка.
В дальнейшем, при необходимости вызова диалогового окна Специальная вставка и замены формул на вычисленные значения, или при вставке значений, вычисленных формулами в другую область, а также при необходимости активизации переключателей области Операция, эта операция будет называться: выполнение действий с вызовом диалогового окна Специальная вставка.
Лучший метод одновременного ввода формул в выделенный диапазон ячеек
При создании моделей довольно часто будет возникать необходимость ввода однотипных формул в диапазоны ячеек. При этом формула сначала вводится в верхнюю или верхнюю левую ячейку выбранного диапазона, после чего она тиражируется в весь выбранный диапазон.
Наиболее распространенный способ на практике - копирование содержимого ячейки с формулой с последующим выделением диапазона и вставкой в него формулы из буфера обмена. На самом деле это наиболее трудоемкий способ.
Приведем один из наиболее оптимальных способов на примере ввода простой формулы ссылки на соседнюю ячейку столбца В в диапазон ячеек С1:С5. Для этого выделите диапазон ячеек С1:С5 начиная с ячейки С1, введите знак равно и выделите ячейку В1, после чего нажмите комбинацию клавиш Ctrl+Enter. Формула будет введена в весь выделенный диапазон.
Эту операцию легко проводить с простыми формулами, а как поступить, если формула сложнее? Например:
=ЕСЛИ(B1=1;"Один";"Значение не определено")
Для одновременного ввода формулы в выделенный диапазон:
- введите эту формулу в ячейку С1 и проверьте правильность выполнения вычислений;
- выделите диапазон ячеек С1:С5 начиная с ячейки С1;
- щелкните правой кнопкой мыши по любой области строки формул в которой отображена эта формула. Excel воспримет эту операцию как редактирование формулы;
- нажмите комбинацию клавиш Ctrl+Enter.
Формула будет введена во все ячейки выделенного диапазона. Эта операция воспримется как редактирование формул во всех ячейках выделенного диапазона
Описанная операция может быть еще упрощена, если вместо редактирования в строке формул, использовать нажатие клавиши F2. Тогда операция ввода формул следующая:
- введите эту формулу в ячейку С1 и проверьте правильность выполнения вычислений;
- выделите диапазон ячеек С1:С5 начиная с ячейки С1;
- нажмите клавишу F2 и затем комбинацию клавиш Ctrl+Enter.
Каждый пользователь выбирает для себя методику одновременного ввода формул (значений, текста) в выделенный диапазон наиболее удобную для себя.
В дальнейшем эта операция будет называться: операция одновременного ввода формул (значений, текста) в выделенный диапазон ячеек.
Объекты Visual Basic for Application (VBA)
Создаваемые далее компьютерные модели, приложения, расчетные таблицы и пр. для выполнения поставленной перед ними задачи, обязательно проводят вычисления. Таблицы, проводят вычисления одновременно после ввода в них изменений, или в заданной последовательности.
Автоматизация различных процессов проведения вычислений в заданной последовательности с помощью Excel, а тем более создание компьютерных моделей, практически невозможна без применения макросов, а основа макросов - язык Visual Basic for Application (VBA), который далее будет именоваться кодом VBA. Мы не ставим задачу полного описания всех возможностей VBA. Для этого существует достаточное количество специализированной литературы и при желании, любой может почерпнуть в ней более детальную информацию.
Здесь мы дадим кораткий обзор объектной модели Excel, которые являются основой для разработки различных приложений. Объекты расположены в иерархическом порядке. В Excel существует более 100 различных встроенных объектов, имеющих различный уровень иерархии.
Например, наиболее часто используемые объекты Excel имеют следующую иерархию:
Application (Microsoft Excel) - WorkBook (рабочая книга) -
WorkSheet (рабочий лист) - Range (ячейка).
Объект Application - это само приложение Microsoft Excel, а все другие объекты расположены на иерархическом уровне ниже него. На втором уровне расположено пятнадцать объектов. Некоторые из них:
- Dialog - диалоговое окно Excel;
- Name - имена диапазонов ячеек;
- VBE - объект управления редактором VBA;
- Window - доступ к различным окнам Excel;
- WorkBook - файл рабочей книги и др.
Каждый из этих объектов в свою очередь может содержать другие объекты. Например, объект WorkBook содержит объекты:
- Chart - диаграмма;
- Module - модуль;
- Name - имя;
- WorkSheet - рабочий лист;
- Window - окно и др.
Эти объекты могут также содержать объекты. Например, объект WorkSheet содержит:
- PageSetup - параметры страницы;
- Range - диапазон и др.
Свойства объектов
Каждый объект Excel имеет набор свойств, используемых для его описания, которые можно назвать его характеристиками. Например, свойства объекта WorkBook:
- Author - имя автора, создателя рабочей книги;
- Name - имя рабочей книги;
- Path - путь к файлу рабочей книги и др.
Свойствам объектов могут быть присвоены различные значения (аргументы), которые будут описаны далее в следующих шагах.
Методы объектов
Каждый объект обладает набором методов, которые могут быть выполнены им или же с его помощью. Следовательно, методы - действия. Например, методы объекта WorkBook:
- Close - закрытие рабочей книги;
- Save - сохранение рабочей книги.
Методам объектов могут быть также присвоены различные значения (аргументы), которые будут описаны в следующих шагах.
Применение Visual Basic for Application (VBA) для создания моделей
Здесь VBA - это всего лишь инструмент, с помощью которого создание какой-то компьютерной модели или расчетной таблицы может быть достигнуто с наименьшими усилиями. Например, - одновременное определение точки безубыточности в многономенклатурном производстве для всех видов продукции, выпускаемой предприятием. Или расчет движения денежных протоков в ежедневном разрезе при бюджетировании. Без применения VBA, используя только одни формулы, это было бы просто невозможной задачей.
Или же с помощью VBA создана какая-то технология, облегчающая труд пользователя. Например, ввод данных в журнал регистрации кассовых документов. В данном случае применение VBA оправдано повышением производительности труда и большей гарантией избежания ввода ошибок.
Макросы (подпрограммы) в большинстве своем написаны не на высоком уровне профессионального программиста. Основная задача - показать путь (или различные направления), технологии решения каких то отдельных задач или проблем, часто встречающихся на практике и, возможно, не освещенных в популярной литературе. А если уж направление есть, то исправить или дописать профессионально код VBA - дело техники.
Если на каком-то этапе будет необходимо описание автоматизации процесса, в котором будут использованы элементы VBA, то это будет освещено на примерах.
Основа для создания компьютерных моделей выбрана следующая:
- описание проблемы (или задачи), которую необходимо решить;
- описание технологии ее решения;
- создание и ввод формул, производящих вычисление отдельных элементов создаваемой модели;
- механическая запись мини-подпрограмм (макросов) и при необходимости их усовершенствование;
- создание и внедрение элементов управления для управления создаваемыми моделями.
Со следующего шага мы начнем рассматривать проблему автоматизации оформления и выписки командировочных удостоверений.
