Форма — это наиболее удобный способ для просмотра, изменения, добавления и удаления записей списка, а также для поиска записей, удовлетворяющих заданным условиям. Перед работой с формой необходимо задать заголовки столбцов списка. Эти заголовки используются для создания полей формы. Создание макроса на языке Visual Basic для получения внешних данных Чтобы получить доступ к внешнему источнику данных с помощью макроса, написанного на языке Visual Basic, необходимо установить параметр Объекты DAO для Visual Basic в программе установки. Чтобы получить более подробные сведения, нажмите кнопку . Чтобы использовать макрос, созданный в Microsoft Excel 5.0 или в более ранней версии, выберите команду Надстройки в меню Сервис и установите флажок Надстройка ODBC. Чтобы получить более подробные сведения о создании макросов на языке Visual Basic, обращайтесь к справке по Visual Basic. Сумирование данных в таблице Способы подведения итогов данных Для подведения итогов и анализа выделенных данных можно воспользоваться сводной таблицей. Кроме того, подвести итоги и упорядочить данные можно с помощью отчета Microsoft Access.
Вставка промежуточных итогов в список
В большом списке для автоматического структурирования данных листа могут быть использованы промежуточные итоги. Имеется возможность «вложения» или вставки итогов для групп, находящихся внутри больших групп, что полезно при подведении итогов в группах значений. Кроме того, итог может быть удален из списка, и исходные данные не будут изменены.
Суммирование данных с помощью промежуточных итогов и структур
Если данные представлены в виде списка, то существует возможность подведения в листе промежуточных итогов. При выборе элементов, для которых создаются промежуточные итоги, суммируемых значений и итоговой функции лист представляется в виде структуры, так что можно скрывать или показывать столько деталей, сколько нужно. Если при суммировании данных были использованы формулы, содержащие такие функции, как СУММ, то данные структурируются автоматически. Если вместо формул строки и столбцы итоговых данных содержат значения, то можно создать структуру вручную.
Консолидация данных
Предусмотрено несколько способов консолидации: . С помощью трехмерных ссылок, что является наиболее предпочтительным способом. При использовании трехмерных ссылок отсутствуют ограничения по расположению данных в исходных областях. . По расположению, если данные исходных областей находятся в одном и том же месте и размещены в одном и том же порядке. Используйте этот способ для консолидации данных нескольких листов, созданных на основе одного шаблона. Если данные, вводимые с помощью нескольких листов-форм, необходимо выводить на отдельные листы, используйте мастер шаблонов с функцией автоматического сбора данных. . По категориям, если данные исходных областей не упорядочены, но имеют одни и те же заголовки. Используйте этот способ для консолидации данных листов, имеющих разную структуру, но одинаковые заголовки. . С помощью сводной таблицы. Этот способ сходен с консолидацией по категориям, но обеспечивает большую гибкость при реорганизации категорий.
Создание сводной таблицы
Перед созданием сводной таблицы, использующей данные внешнего источника данных, необходимо сначала задать эти данные. 1. Откройте книгу, в которой необходимо создать сводную таблицу. Если сводная таблица создается на основе данных, находящихся в списке или базе данных Microsoft Excel, выделите ячейку списка или базы данных. 2. Выберите команду Сводная таблица в меню Данные. 3. Следуйте инструкциям мастера сводных таблиц. Анализ данных с помощью сводных таблиц.
Выделение данных в сводной таблице
В сводной таблице можно использовать выделение форматируемых частей. Чтобы выделить сводную таблицу, на панели инструментов Сводные таблицы выберите команду Выделить в меню Сводная таблица. Проверьте, что нажата кнопка Разрешить выделение . . Чтобы выделить всю таблицу, выберите команду Выделить (меню Сводная таблица, панель инструментов Сводные таблицы), а затем — команду Таблица целиком. . Чтобы выделить все метки элементов в поле, нажмите кнопку поля. В представленном примере можно нажать кнопку «Дата заказа», чтобы выделить метки «Кв.2» и «Кв.3». Если необходимо выделить данные вместе с метками, проверьте, что нажата кнопка Заголовки и данные. По умолчанию при указании элемента поля выделяются и метки, и данные. Чтобы выделить только метки, нажмите кнопку Только заголовки . Чтобы выделить только данные, нажмите кнопку Только данные. . Чтобы выделить все одинаковые элементы поля, можно указать только один элемент. Например, выберите «Батурин» для выделения всех строк «Батурин»: . Чтобы снять выделение всех одинаковых элементов поля и оставить только одно, необходимо выбрать элемент поля дважды. Например, если выбрать « Батурин» второй раз, выделится строка «Батурин» для элемента поля «Мясные». Если в сводной таблице содержится несколько полей строк, для выделения необходимых элементов нужно выполнить данную процедуру несколько раз. Например, если в сводной таблице содержится четыре поля строки, для выделения только одного элемента необходимо выбрать внутренний элемент в поле строки четыре раза. Достаточно указать любой итог, чтобы выделить в поле все итоги строк. Повторный выбор итога снимает это выделение. . В сводной таблице можно выделить несколько элементов поля. Сначала выделите один элемент. Нажмите клавишу SHIFT или CTRL и, удерживая ее нажатой, выделите остальные элементы внутри одного поля. Чтобы снять выделение с элемента, нажмите клавишу CTRL и, удерживая ее нажатой, снимите выделение с этого элемента. . Чтобы отключить выделение частей сводной таблицы и пользоваться обычным способом выделения, проверьте, что кнопка Разрешить выделение (команда
Выделить, меню Сводная таблица, панель инструментов Сводные таблицы) не нажата. Чтобы после обновления и изменения структуры сводной таблицы форматы ее областей сохранялись, необходимо нажать кнопку Разрешить выделение.
Перед созданием сводной таблицы, использующей данные внешнего источника данных, необходимо сначала задать эти данные. Для получения сведений о способах задания внешних данных для сводной таблицы нажмите кнопку . 1. Откройте книгу, в которой необходимо создать сводную таблицу. Если сводная таблица создается на основе данных, находящихся в списке или базе данных Microsoft Excel, выделите ячейку списка или базы данных. 2. Выберите команду Сводная таблица в меню Данные. 3. Следуйте инструкциям мастера сводных таблиц.
Изменение структуры сводной таблицы
Представление сводной таблицы можно изменить непосредственно на листе перетаскивая названия кнопок полей или элементов полей. Если необходимо использовать все предусмотренные средства структурирования сводной таблицы или если в текущую таблицу не были ранее включены все поля исходных данных, следует воспользоваться мастер сводных таблиц. Если сводная таблица содержит большую группу полей страницы, то их можно разместить в строках или столбцах. Изменение структуры сводной таблицы не затрагивает исходные данные.
Использование общих и промежуточных итогов в сводной таблице
Сводную таблицу можно настроить на выполнение вычислений с помощью итоговой функции, вставив дополнительные строки или столбцы промежуточных итогов для полей данных. Промежуточные итоги автоматически отображаются в самой последней строке или столбце, если в сводной таблице создано несколько полей строки или столбца. При добавлении промежуточных итогов их можно размещать и внутри полей строк или столбцов.
Создание сводной таблицы на основе данных, находящихся во внешнем источнике данных
При создании сводной таблицы можно воспользоваться несколькими внешними источниками данных, например базами данных, текстовыми файлами или любыми другими, отличными от книг Microsoft Excel. Кроме того, можно воспользоваться источниками данных в Интернете. Для указания и поиска данных обычно используется мастер сводных таблиц. В мастере сводных таблиц можно открыть файлы запроса, а также создать новые запросы с помощью мастера запросов или Microsoft Query. В следующих ситуациях передача внешних данных с помощью мастера сводных таблиц невозможна: . если для передачи данных используется шаблон отчетов, который не включен в сводную таблицу, необходимо сначала открыть шаблон отчетов, а затем создавать сводную таблицу на основе диапазона, содержащего внешние данные; . если для передачи данных используется запрос с параметрами, необходимо сначала создать запрос с параметрами, а затем создавать сводную таблицу на основе диапазона, содержащего внешние данные; . если для передачи данных через Интернет используется Web-запрос, необходимо сначала создать запрос на Web, а затем создавать сводную таблицу на основе диапазона, содержащего внешние данные. Анализа «что-если»
Создание таблицы подстановки с одной переменной
Следует сформировать таблицу подстановки с одной переменной так, чтобы введенные значения были расположены либо в столбце (ориентированные по столбцу), либо в строке (ориентированные по строке). Формулы, используемые в таблицах подстановки с одной переменной должны ссылаться на ячейку ввода. 1. Либо в отдельный столбец, либо в отдельную строку введите список значений, которые следует подставлять в ячейку ввода. Если значения расположены в столбце, то введите формулу в ячейку, расположенную на одну строку выше и на одну ячейку правее первого значения. Правее первой формулы введите любые другие формулы. 2. Если значения расположены в строке, то введите формулу в ячейку, расположенную на один столбец левее и на одну строку ниже первого значения. В том же столбце, но ниже наберите любые другие формулы. 3. Выделите диапазон ячеек, содержащий формулы и значения подстановки. 4. Выберите команду Таблица подстановки в меню Данные. Если таблица подстановки данных ориентирована по столбцам, то введите ссылку на ячейку ввода в поле Подставлять значения по столбцам в. 5. Если же таблица подстановки данных ориентирована по строкам, то ссылка на ячейку ввода вводится в поле Подставлять значения по строкам в. Создание таблицы подстановки с двумя переменными Таблицы подстановки с двумя переменными используют одну формулу с двумя наборами значений. Формула должна ссылаться на две различных ячейки ввода. 1. В ячейку листа введите формулу, которая ссылается на две ячейки ввода. 2. В тот же столбец ниже формулы введите значения подстановки для первой переменной. Значения подстановки для второй переменной вводятся в строку правее формулы. 3. Выделите диапазон ячеек, содержащий формулу и оба набора данных подстановки. 4. Выберите команду Таблица подстановки в меню Данные. 5. В поле Подставлять значения по столбцам в введите ссылку на ячейку ввода для значений подстановки в строке. 6. В поле Подставлять значения по строкам в введите ссылку на ячейку ввода для значений подстановки в столбце. Поиск определенного результата для ячейки с помощью подбора значения другой ячейки 1. Выберите команду Подбор параметра в меню Сервис. 2. В поле Установить в ячейке введите ссылку на ячейку, содержащую необходимую формулу. 3. Введите искомый результат в поле Значение. 4. В поле Изменяя значение ячейки введите ссылку на ячейку, содержащую подбираемое значение.
Создание сценария
1. Выберите команду Сценарии в меню Сервис. 2. Нажмите кнопку Добавить. 3. Введите необходимое имя в поле Название сценария. 4. Введите ссылки на ячейки, которые необходимо изменить, в поле Изменяемые ячейки. 5. Установите необходимые флажки в наборе флажков Защита. 6. Нажмите кнопку OK. 7. Введите необходимые значения в диалоговом окне Значения ячеек сценария. 8. Чтобы создать сценарий, нажмите кнопку OK. Для создания дополнительных сценариев нажмите кнопку Добавить, а затем повторите шаги с 3 по 7. После завершения создания сценариев нажмите кнопку OK, а затем – кнопку Закрыть в диалоговом окне Диспетчер сценариев.
Постановка задачи и оптимизация модели с помощью процедуры поиска решения
1. В меню Сервис выберите команду Поиск решения. 2. Если команда Поиск решения отсутствует в меню Сервис, установите соответствующую надстройку. 3. В поле Установить целевую ячейку введите адрес или имя ячейки, в которой находится формула оптимизируемой модели. 4. Чтобы максимизировать значение целевой ячейки путем изменения значений влияющих ячеек, установите переключатель в положение максимальному значению. Чтобы минимизировать значение целевой ячейки путем изменения значений влияющих ячеек, установите переключатель в положение минимальному значению. Чтобы установить значение в целевой ячейке равным некоторому числу путем изменения значений влияющих ячеек, установите переключатель в положение значению и введите в соответствующее поле требуемое число. 5. В поле Изменяя ячейки введите имена или адреса изменяемых ячеек, разделяя их запятыми. Изменяемые ячейки должны быть прямо или косвенно связаны с целевой ячейкой. Допускается установка до 200 изменяемых ячеек. Чтобы автоматически найти все ячейки, влияющие на формулу модели, нажмите кнопку Предположить. 6. В поле Ограничения введите все ограничения, накладываемые на поиск решения. 7. Нажмите кнопку Выполнить. 8. Чтобы сохранить найденное решение, установите переключатель в диалоговом окне Результаты поиска решения в положение Сохранить найденное решение. Чтобы восстановить исходные данные, установите переключатель в положение Восстановить исходные значения. Статистический анализ
Статистический анализ
В состав Microsoft Excel входит набор средств анализа данных (называемый пакет анализа), предназначенный для решения сложных статистических и инженерных задач. Для проведения анализа данных с помощью этих инструментов следует указать входные данные и выбрать параметры; анализ будет проведен с помощью подходящей статистической или инженерной макрофункции, а результат будет помещен в выходной диапазон. Чтобы вывести список доступных инструментов анализа, выберите команду Анализ данных в меню Сервис. Если команда Анализ данных отсутствует в меню Сервис, то необходимо запустить программу установки Microsoft Excel. После установки пакета анализа его необходимо выбрать и активизировать с помощью команды Настройки. Чтобы получить более подробные сведения о пакете анализа, нажмите кнопку . Для успешного применения процедур анализа необходимы начальные знания в области статистических и инженерных расчетов, для которых эти инструменты были разработаны. Открытие совместного доступа к книге С помощью Microsoft Excel и других программ Microsoft Office можно открывать совместный доступ к файлам, и несколько пользователей могут работать с данными одновременно. Эти программы упрощают возможность просмотра чужих файлов и предоставления другим пользователям своих. . Существует возможность подготовки копий книги и распределения их для просмотра и внесения пометок. Чтобы распределить книгу для просмотра, отправьте ее с помощью электронной почты или проведите ее в общу папку Microsoft Exchange. Если необходимо иметь отдельный набор комментариев от каждого лица, то отправьте им по отдельной копии книги. Чтобы получить совокупное множество комментариев, в котором каждый человек видит изменения, внесенные предыдущими пользователями, проведите или направьте книгу в общую папку. При получении копий книги с пометками, имеется возможность либо просмотра и объединения изменений каждой копии, либо слияния всех изменений в одну копию книги. . Если необходимо, чтобы с данными одной книги одновременно работали несколько человек, то ее следует сохранить как общую книгу,, а затем сделать доступной в сети. Общая книга помещается в общий сетевой каталог или в общую папку на компьютере. ----------------------- [pic]
[pic]
Страницы: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21
