|
Встречающиеся термины
Формула: "возвращает"
В последующем тексте книги довольно часто встречается термин — формула ВОЗВРАЩАЕТ значение. Для понимания этого термина рассмотрите следующий пример.
На рис. 3.1. приведен пример — в ячейки столбца А вводится любое числовое значение, а формула в столбце В (показана в строке формул на рис. 3.1.), основанная на функции ЕСЛИ возвращает следующий результат:
- при введенном в столбец А значении равном единице, на экране монитора — результат вычисления — текст: Единица;
- при введении любого другого значения, результат вычисления формулы: Значение неизвестно.
Термин "возвратит" — это результат, полученный при вычислении формулы, при заданных каких-то специфических, введенных для нее условий, которые участвуют в процессе вычисления.
Рис. 3.1. Пример формулы для объяснения термина "возвратит"
Табличные базы данных (Списки)
Базы данных как способ хранения и обработки различной информации играют в настоящее время огромную роль. В базах данных хранят сведения о клиентах, заказах, различные справочники телефонов и адресов и т.д. Но немногие догадываются, что большинство различных таблиц относятся к табличным базам данных или в Excel введено понятие список, как аналог базы данных в электронных таблицах.
Список — это группа строк таблицы, содержащая связанные данные. Отличительной особенностью списка является то, что каждый его столбец содержит однотипные данные.
Если провести аналогию между списком и табличной базой данных, то столбцы списка являются полями базы данных, а его строки — записями. Считается, что первая строка списка является его заголовком и содержит названия столбцов списка. Заголовок должен иметь на листе электронных таблиц горизонтальную ориентацию. Заголовки могут применяться в Excel для поиска и организации данных. Шрифт, размер шрифта, выравнивание и другие параметры форматирования, присвоенные заголовкам столбцов списка, как правило, должны отличаться от параметров, назначенных для строк данных.
Для отделения заголовка от расположенных ниже данных применять пустые строки не следует. Не следует также допускать пустые строки и внутри самой базы данных.
В этой книге базы данных рассматриваются практически в каждой главе. Для того чтобы каждый раз не возвращаться к этой теме, рассмотрим некоторые правила организации этих данных.
Показанную на рис. 3.2. простую таблицу с числовыми значениями можно также отнести к списку или табличной базе данных.
Рис. 3.2. Таблица с числовыми значениями, которую также можно отнести к простой табличной базе данных
Фильтрация списка
Выбрать необходимые данные из списка можно при помощи фильтрации, то есть путем сокрытия всех строк списка, кроме тех которые удовлетворяют заданным критериям. Чтобы воспользоваться функцией фильтрации установите табличный курсор в любое место списка и выполните команду Данные/Фильтр/Автофильтр, после чего в правом нижнем углу каждой ячейки заголовка появится маленький квадратик со стрелкой раскрывающегося списка.
Если список создан правильно, то, например, при выполнении фильтрации списка в столбце D по значению 2, список будет иметь вид, показанный на рис. 3.3. Номера строк 4 и 8, выбранных при фильтрации будут синего цвета.
Рис. 3.3. Вид таблицы при выполнении фильтрации по значению 2 в столбце D
В списке выбрано две строки удовлетворяющих критерию фильтрации. Причем при выполнении операции фильтрации при нажатию на кнопку автофильтра раскрывшийся список выглядит, как показано на рис. 3.4.
Рис. 3.4. Раскрывшийся список при выполнении операции фильтрации
Нарушьте правила формирования списка — удалите данные из строки 5. При выполнении операции фильтрации открывшийся список будет содержать данные только области списка до первой пустой строки, и будет выполнена фильтрация только этой области (рис. 3.5.). В данном случае нарушены правила организации данных списка и один список разбит на два:
- верхний (В3:D4) организован согласно правил и имеет заголовок;
- нижний (В6:D11) — неправильно организован и не имеет заголовка.
Рис. 3.5. Фильтрация списка с нарушенными правилами его организации
Сортировка списка
Довольно часто данные требуется представить в соответствии с некоторым заданным критерием: в порядке возрастания или убывания. Изменение порядка расположения строк списка называется сортировкой.
Для выполнения сортировки необходимо поместить курсор в любую ячейку поля, по которому будет происходить данная операция, и щелкнуть правой кнопкой мыши по одной из кнопок сортировки (По возрастанию или По убыванию), расположенных на стандартной панели инструментов.
При выполнении операции сортировки списка, показанного на рис. 3.2. полученный результат ее выполнения показан на рис. 3.6.
Рис. 3.6. Список, организованный по правилам, после сортировки по возрастанию по столбцу D
Если же выполнить такую же сортировку списка при нарушении правил его организации (рис. 3.5.), то произойдет сортировка только той области списка, в которой находится табличный курсор (рис. 3.7.).
Рис. 3.7. Сортировка списка при нарушении правил его организации
Формулы промежуточных вычислений в списках
Довольно часто на практике приходится сталкиваться с тем, что в списки (или табличные базы данных) вводятся формулы производящие промежуточные расчеты, с указанием в них ссылок на другие ячейки этого же списка. К чему это может привести — рассмотрим на простом примере.
На рис. 3.2. в ячейку D6 введено числовое значение — 18. После сортировки списка это числовое значение не изменилось (рис. 3.6.).
Предположим что числовое значение 18 получено, как результат умножения содержимого ячеек С4 и В8. Введите в ячейку D6 формулу умножения с указанием ссылок на эти ячейки (рис. 3.8.). Для того чтобы проконтролировать — в каком месте списка после сортировки окажется ячейка с этой формулой, задайте шрифту этой ячейки другой цвет.
Рис. 3.8. Список с введенной формулой умножения значений, содержащий ссылки на ячейки С4 и В8
Если произвести операцию любой сортировки списка, например, по возрастанию по столбцу D, то полученный результат показан на рис. 3.9. В столбце D в конце списка появилось значение 0, которого раньше не было. Поместите табличный курсор на ячейку D11, в которой стало находиться прежнее содержимое ячейки D6 и обнаружите, что введенные ранее адреса ссылок изменились. Последовательность выполнения Excel операции сортировки была следующая:
- результат умножения формулы в ячейке D6 равный значению 18 при сортировке был воспринят как значение;
- после сортировки произошел перерасчет формулы с изменившимися ссылками на ячейки;
- полученный результат в виде значения 0 отобразился на экране монитора.
Рис. 3.9. Результат сортировки списка с введенной формулой со ссылками на другие ячейки списка
Если список был довольно большой, формулы вводились неоднократно, и подобные ошибки не были обнаружены своевременно, то через некоторое время исправить их уже не представляется возможным. Тогда всю работу по созданию списка необходимо начинать сначала.
Поэтому если создаваемая таблица будет относиться к спискам и, как следствие, с ее данными возможно проведение операций сортировки, то не вводите в нее формулы промежуточных расчетов. А если такая необходимость присутствует, то после проведения вычисления замените ее на вычисленной значение.
Или учтите что при необходимости ввода таких формул, вычисления должны проводиться только с содержимым ячеек, находящимися в одной строке списка.
Применение диалогового окна Специальная вставка
При создании компьютерных моделей или других расчетных таблиц в Excel, в данной книге довольно часто производится последующая замена формул, производящих вычисления, на определенное ими значение. Или же вычисленное значение служит операндом при проведении арифметических вычислений с другими значениями.
Для того чтобы не производить операцию замены формул на значения, вводом значений с клавиатуры, в этой книге довольно часто будет использоваться диалоговое окно Специальная вставка (рис.3.10).
Это диалоговое окно вызывается командой Правка/Специальная вставка или выбором строки Специальная вставка в контекстном меню.
Рис. 3.10. Диалоговое окно Специальная вставка
Диалоговое окно Специальная вставка вызывается только после выполнения команды Копировать. Команда Вырезать для этого случая не подходит.
В области Вставить диалогового окна находятся следующие переключатели:
- Все. Осуществляется вставка всех параметров скопированной области (ячейки). Эта операция ничем не отличается от обыкновенной операции копирования и последующей вставки из буфера обмена.
- Формулы. Производит вставку только формул. Основное преимущество этого метода состоит в том, что при вставке из буфера обмена не нарушается форматирование ячеек вставки и не изменяются примечания, которые находятся в них.
- Значения. Выполняет вставку только значений. Этот метод вставки полезен в тех случаях, когда необходимо зафиксировать или перенести какие-то результаты расчетов в виде значений, прежде чем изменять исходные данные. Метод не нарушает форматирования ячеек и примечаний.
- Форматы. Производит вставку только форматов. Это дает возможность при вставке из буфера обмена не затрагивать значения, формулы и примечания, содержащиеся в ячейках области вставки.
- Примечания. Вставка только примечаний.
Соответствующие переключатели области Операция диалогового окна Специальная вставка позволяют выполнять операции сложения, вычитания, умножения и деления значений или формул, находящихся в скопированной ячейке, со значениями или формулами ячейки, в которую производится вставка.
В дальнейшем, в книге при необходимости вызова диалогового окна Специальная вставка и замены формул на вычисленные значения, или при вставке значений, вычисленных формулами в другую область, а также при необходимости активизации переключателей области Операция, эта операция будет называться — выполнение действий с вызовом диалогового окна Специальная вставка.
Лучший метод одновременного ввода формул в выделенный диапазон ячеек
При создании моделей в этой книге довольно часто будет возникать необходимость ввода однотипных формул в диапазоны ячеек. При этом формула сначала вводится в верхнюю или верхнюю левую ячейку выбранного диапазона, после чего она тиражируется в весь выбранный диапазон.
Наиболее распространенный способ на практике — копирование содержимого ячейки с формулой с последующим выделением диапазона и вставкой в него формулы из буфера обмена. На самом деле это наиболее трудоемкий способ.
Приведем один из наиболее оптимальных способов на примере ввода простой формулы ссылки на соседнюю ячейку столбца В в диапазон ячеек С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) для создания моделей
Автор не ставил перед собой цели — предоставить полное пособие читателю для изучения Visual Basic for Application (далее VBA).
В данной книге VBA — это всего лишь инструмент, с помощью которого создание какой-то компьютерной модели или расчетной таблицы, может быть достигнута наименьшими усилиями. Например, — одновременное определение точки безубыточности в многономенклатурном производстве для всех видов продукции, выпускаемой предприятием. Или расчет движения денежных протоков в ежедневном разрезе при бюджетировании. Без применения VBA, используя только одни формулы, это было бы просто невозможной задачей.
Или же с помощью VBA создана какая-то технология, облегчающая труд пользователя. Например, ввод данных в журнал регистрации кассовых документов. В данном случае применение VBA оправдано повышением производительности труда и большей гарантией избежания ввода ошибок.
Макросы (подпрограммы) в большинстве своем написаны не на высоком уровне профессионального программиста. Автор не ставил перед собой такой задачи. Задача автора как опытного пользователя — показать путь (или различные направления), технологии решения каких то отдельных задач или проблем, часто встречающихся на практике и, возможно, не освещенных в популярной литературе ранее. А если уж направление есть, то исправить или дописать профессионально код VBA — дело техники.
Если на каком-то этапе будет необходимо описание автоматизации процесса, в котором будут использованы элементы VBA, то это будет популярно освещено на практических примерах.
Основа для создания компьютерных моделей выбрана следующая:
- описание проблемы (или задачи), которую необходимо решить;
- описание технологии ее решения;
- создание и ввод формул, производящих вычисление отдельных элементов создаваемой модели;
- механическая запись мини-подпрограмм (макросов) и при необходимости их усовершенствование;
- создание и внедрение элементов управления, для управления создаваемыми моделями.
|
