Повторите те же действия для второго поля ввода. Пусть, например, там было указано условие <=50. Включите селекторную кнопку And, если нужно, чтобы выполнялись оба условия, или кнопку Or, если достаточно, чтобы выполнялось одно из условий фильтрации. По умолчанию включена первая из этих кнопок. Щелкните по командной кнопке OK, тогда в таблице появятся только те элементы, которые удовлетворяют комбинированному логическому условию. В данном примере, если оставить включенной кнопку And, на экране останутся те элементы списка, у которых значение указанного поля расположено в диапазоне от 20 до 50.
8.7. Сортировка списков.
Вызовите директиву Sort из меню Data. Откроется диалоговое окно этой директивы. В группе полей Sort by этого окна необходимо указать критерии сортировки. По умолчанию программа предлагает в первом поле одно из значений этого поля в качестве критерия. В следующих полях можно дополнительно указать значения, по которым будут сортироваться элементы, имеющие одинаковые значения в предшествующем поле.
Пара селекторных кнопок Ascending и Descending, которые расположены рядом с каждым полем, позволяет проводить сортировке либо в возрастающей, либо в убывающей последовательности. Щелкните по командной кнопке OK, тогда программа Excel выполнит сортировку списка в соответствии с указанными критериями.
9. АНАЛИЗ ДАННЫХ
Для анализа больших списков данных в программе Excel предусмотрены специальные средства.
Под анализом данных здесь понимаются методы, позволяющие лучше понять тенденции и закономерности, которым подчиняются табличные данные. Опорные таблицы (Pivot Table), включенные в пятую версию программы Excel, предназначены для анализа соотношений между данными в списке. Опорные таблицы строятся с помощью Конструктора таблиц за четыре шага. Структуру опорной таблицы можно легко изменить в интерактивном режиме путем перемещения названий полей данных из одной части таблицы в другую.
Кроме опорных таблиц, в программе Excel имеются и другие методы анализа наборов данных. Очень полезной является директива поиска решения уравнений, которая по заданным значениям полей находит другие значения, удовлетворяющие определенным соотношениям.
9.1. Опорные таблицы.
Директивой PivotTable из меню Data откройте диалоговое окно Конструктора опорных таблиц. В этом окне за четыре шага нужно построить опорную таблицу.
На первом шаге следует указать источник поступления данных. Если исходные данные находятся в списке, то щелкните по селекторной кнопке Microsoft Excel List or Database и перейдите к следующему шагу с помощью командной кнопки Next.
На втором шаге нужно указать область рабочего листа, где находится список. По умолчанию программа предлагает список, который расположен на активном рабочем листе. Здесь можно указать только часть списка, если для построения опорной таблицы остальная часть не нужна. Щелкните по командной кнопке Next, чтобы перейти к следующему шагу.
На третьем шаге задается структура (Layout) опорной таблицы. В правом поле расположены названия полей списка, а в среднем - представление структуры опорной таблицы. В это поле включается поле названий строк, поле названий столбцов и поле данных. Чтобы задать структуру таблицы, следует отбуксировать мышью названия полей списка в соответствующие поля таблицы. При построении опорной таблицы придерживайтесь следующих правил. Названия полей, по значениям которых будет проводится анализ данных, отбуксируйте в поле данных Data. В поле названий строк Row и в поле названий столбцов Column переместите названия полей, по значениям которых классифицируются (разбиваются) данные. Затем щелкните по командной кнопке Next, чтобы перейти к следующему шагу.
На четвертом шаге можно установить некоторые опции для построения таблицы. В поле ввода Pivot Table Starting Cell указывается адрес, с которого начинается построение таблицы (левый верхний угол). В поле ввода Pivot Table Name можно указать название таблицы. По умолчанию программа использует название PivotTable1 с последующим увеличением номера таблицы. Если включить контрольные индикаторы Grand Totals for Columns и Grand Totals for Rows, то в каждой строке и в каждом столбце опорной таблицы будут вычисляться общие суммы. Контрольный индикатор Save Data With Table Layout служит для сохранения опорной таблицы с заданной структурой. Включение контрольного индикатора AutoFormat Table позволяет автоматически сформатировать опорную таблицу. После закрытия диалогового окна Конструктора командной кнопкой Finish программа Excel размещает опорную таблицу в указанной позиции.
Между опорной таблицей и исходным списком возникает статическая связь, т.е. связь, которая существует только в момент построения таблицы. При изменении данных в списке опорная таблица не будет изменяться. Чтобы привести в соответствие содержимое таблицы и содержимое списка, нужно вызвать директиву Refresh Data из меню Data.
9.2. Редактирование опорных таблиц.
Названия полей, по которым классифицируются данные, можно перемещать с помощью мыши, чтобы изменить структуру таблицы. Если в опорную таблицу необходимо добавить новые поля, то вызовите директиву PivotTable из меню Data или щелкните по пиктограмме Конструктора таблиц на опорной (Pivot) панели. После этого появится третье окно Конструктора опорных таблиц, в котором можно изменить размещение значений полей в таблице. Следите за тем, чтобы при вызове этой директивы указатель ячеек находился внутри таблицы, в противном случае Конструктор начнет построение новой таблицы.
9.3. Нахождение значений.
Иногда бывает заранее известно, каким должен быть результат вычисления некоторой функции, но неизвестно, при каких значениях аргумента он достигается.
Предположим, что нам надо найти максимальную сумму кредита, которую можно дать на три года по фиксированной процентной ставке, если клиент готов возвращать по 2000$ ежемесячно.
В программе Excel имеется функция PMT, которая по значениям суммы кредита, периода и процентной ставки вычисляет величину ежемесячного взноса для погашения кредита. Нам нужно решить обратную задачу, когда заранее известна величина ежемесячно возвращаемой суммы и нужно найти сумму кредита. Для решения этой задачи вызовите директиву Goal Seek из меню Tools.
Откроется диалоговое окно этой директивы. В поле ввода Set Cell: этого окна укажите адрес ячейки, в которой расположено заданное значение функции (в нашем случае это ячейка, где находится функция PMT). В поле ввода To Value укажите значение функции, которое в нашем случае равно 2000$. В поле ввода By changing Cell укажите адрес ячейки, в которой программа сможет подбирать нужное значение, удовлетворяющее заданным условиям. В нашем случае здесь надо указать адрес ячейки, где находится значение суммы кредита. Закончив установки полей, щелкните по командной кнопке OK.
Путем подбора значений в указанной ячейке программа Excel пытается найти максимальное приближение к заданному целевому значению функции. Результат появляется в диалоговом окне. Если Вас удовлетворяет расхождение между найденным и целевым значением, то закройте диалоговое окно щелчком по кнопке OK, после чего найденное значение появится в таблице. В противном случае щелкните по командной кнопке Cancel, чтобы не передавать значение в таблицу.
10. БАЗЫ ДАННЫХ
В комплект поставки Excel 5.0 входит программа обработки внешних баз данных MS Query.
Программа Query (Запрос) может работать с текстовыми файлами и с данными, которые были подготовлены следующими программами управления базами данных: Ms Access, dBASE, FoxPro, Oracle, Paradox, SQL Server. Набор данных, хранящийся на диске в формате, одной из перечисленных выше баз данных, будем называть внешней базой данных.
Если в процессе работы с таблицей в программе Excel возникает необходимость обратиться к внешней базе данных, нужно запустить программу Query. Это позволит импортировать данные из базы и обрабатывать их как обычные табличные данные в программе Excel. В некоторых случаях внешние данные можно обработать непосредственно программой Query.
10.1. Запуск программы обработки баз данных.
Вызовите Менеджер расширений директивой Add-Ins из меню Tools. Откроется диалоговое окно Менеджера. В нем можно выбрать из списка опции расширения, которые включаются и выключаются щелчком по соответствующей опциональной кнопке, расположенной перед именем расширения.
По списку опций можно перемещаться с помощью линейки прокрутки. Найдите в списке строку MS Query Add-In и щелкните по кнопке в этой строке, чтобы в ней появился крестик. Затем закройте диалоговое окно щелчком по командной кнопке OK.
Теперь загляните в меню Data. В нижней части этого меню должна появиться директива Get External Data, с помощью которой можно запускать программу Query.
10.2. Оболочка программы Query.
Окно программы Query содержит многие элементы, которые являются общими для всех программ в среде Windows. В верхней части экрана находится строка заголовка с кнопками увеличения и уменьшения размеров окна. Под ней расположена строка меню с элементами меню, которые содержат директивы работы с внешними базами данных.
Под строкой меню находится строка пиктографического меню (пиктографическая панель), в которой расположены пиктограммы для вызова наиболее часто используемых директив. Если установить указатель мыши на пиктограмму в статусной строке, появится описание действия, которое связано с этой пиктограммой.
После загрузки файла из базы данных под пиктографической панелью появляется имя этого файла вместе со списком названий полей содержащихся в нем данных. Большое поле в нижней части окна отводится для размещения данных из базы. Линейки прокрутки, расположенные в правой части окна, позволяют перемещаться внутри набора данных.