Электронные таблицы Microsoft Excel, как средство обработки финансово-экономической и статистической информации

Вид материала

Содержание

Подобный материал:

1 2 3 4 5 6 7 8 9 10 11

Создание пользовательского списка автозаполнения или порядка сортировки

Существует возможность создания пользовательского списка автозаполнения или порядка сортировки либо из существующих на листе элементов, либо с помощью непосредственного ввода списка.

Если ряд элементов, который необходимо представить в виде пользовательского списка автозаполнения, был набран заранее, то выделите его на листе.
Выберите команду Параметры в меню Сервис, а затем откройте вкладку Списки.
Чтобы использовать выделенный список, нажмите кнопку Импорт.

Чтобы создать новый список, выберите Новый список из списка Списки, а затем введите данные в поле Элементы списка, начиная с первого элемента. После ввода каждой записи нажимайте клавишу ENTER. Нажмите кнопку Добавить после того, как список будет набран полностью.

Примечание. Пользовательский список может содержать текст или текст в сочетании с числами. Чтобы создать пользовательский список, состоящий только из чисел, например, инвентаризационных номеров, выделите сначала достаточное количество свободных ячеек, предназначенных для его размещения. Выберите команду Ячейки в меню Формат, а затем – вкладку Число. Свободным ячейкам назначьте текстовый формат и введите в них список чисел.

Создание структуры листа

Использование структуры

Когда данные представлены в виде списка, Microsoft Excel может создать структуру, что позволяет скрыть и отобразить уровни детализации простым нажатием кнопки мыши. Структура позволяет быстро отобразить только строки или столбцы с итоговыми значениями, заголовки разделов листа или отобразить области подробных данных, подчиненных итоговым строкам или столбцам.

Отображение и скрытие подробных данных Структура может иметь до 8 уровней детализации, в которых каждый уровень обеспечивает подробную информацию для предыдущего уровня. В приведенном ниже примере строка, содержащая общий итог для всех строк, имеет уровень 1, строки, содержащие итоги для южного и западного регионов, имеют уровень 2 и конкретные данные для областей имеют уровень 3. Для отображения только строк на определенном уровне достаточно щелкнуть номер уровня, который нужно просмотреть. Строки с подробностями для западного региона скрыты, но можно щелкнуть символ структуры + для их отображения.

Способы структурирования данных

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

Автоматическое создание структуры листа   Если данные на листе обобщены формулами, которые используют функции, например СУММ, Excel позволяет автоматически структурировать данные, как в приведенном выше примере. Итоговые данные должны располагаться рядом с подробными данными.

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

Структурирование листа вручную

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

Выделите строки или столбцы, содержащие детальные данные.

Строки или столбцы детальных данных обычно прилегают к строке или столбцу, содержащему соответствующие итоговые данные. Например, если строка 6 содержит суммы для строк с 3 по 5, то выделите строки 3 - 5.

Выберите в меню Данные команду Группа и структура и подкоманду Группировать.

Рядом с группой на экране появятся символы структуры.

Продолжайте выделение детальных строк или столбцов и выполнение команды Группировать до тех пор, пока не будут созданы все необходимые уровни структуры.

Примечание. По умолчанию итоговые строки располагаются под детальными строками, а итоговые столбцы —справа от детальных столбцов. Если итоговые строки или столбцы располагаются по-другому, перед структурированием можно изменить эти настройки. Для этого выберите в меню Данные команду Группа и структура и подкоманду Настройка, а затем установите флажки в группе В направлении.

Новые возможности работы со списками и итоговыми данными

Поиск данных и сортировка списков являются наиболее частыми задачами при работе с Microsoft Excel. Теперь возможен еще более быстрый поиск необходимых данных и подсчет итоговых значений.

Автоматическое заполнение списка (Введено в версии 2000)   Excel 2000 автоматически расширяет форматирование и формулы в списках, упрощая эту распространенную задачу.

Автофильтр (Улучшено в версиях 95 и 97)    Автофильтр отображает строки данных из списка в соответствии с выбором, сделанным пользователем. В Microsoft Excel 95 используйте возможность «Первые 10» для поиска максимальных и минимальных значений в списке. В Microsoft Excel 97 и 2000 автофильтр отображает только фильтры, основанные на примененных фильтрах.

Автозаполнение (Введено в версии 95)   При вводе текста в ячейку производится его сравнение с текстом других ячеек данного столбца и предлагается закончить ввод на основе уже введенного текста.

Новые возможности анализа данных с помощью отчетов сводных таблиц

Отчеты сводных таблиц позволяют получать динамические представления больших объемов данных, а новые возможности обеспечивают еще более простое составление итоговых отчетов в соответствии с потребностями пользователей. Данный раздел содержит справочные сведения по следующим вопросам.

Возможности сводных таблиц

Создание макетов отчетов непосредственно на листах (введено в версии 2000).   После нажатия кнопки Готово в мастере отчетов сводных таблиц и диаграмм на листе появляются синие рамки областей перетаскивания и панель инструментов Сводные таблицы со списком полей исходных данных. Для создания макета отчета сводной таблицы прямо на листе можно перетащить поля с панели инструментов в области перетаскивания.

Отчеты сводных диаграмм (введено в версии 2000).   Новые отчеты сводных диаграмм переносят мощь отчетов сводных таблиц на диаграммы. Отчеты сводных диаграмм интерактивны, снабжены кнопками, с помощью которых можно отображать и скрывать элементы диаграммы.

Структурированные форматы (введено в версии 2000).   Для отчета сводной таблицы можно задать отображение в структурированном формате, аналогичном формату стандартных отчетов базы данных, облегчающем чтение больших или сложных отчетов сводных таблиц.

Автоформаты сводных таблиц (введено в версии 2000).   Автоформаты сводных таблиц можно использовать для отображения структурированных и неструктурированных отчетов сводных таблиц, а также для задания параметров печати сводных таблиц (разрывов страниц и повторяющихся подписей строк и столбцов) для отчетов сводных таблиц, отображаемых в структурированном формате.

Отображение и скрытие элементов полей (введено в версии 2000).   Поля строк и столбцов теперь снабжены стрелками раскрытия списка. По щелчку такой стрелки отображается список доступных элементов, из которого можно выбрать нужный. Этот список позволяет быстро отображать и скрывать элементы полей.

Выделение данных и форматирование (улучшено в версии 2000).   Теперь нет необходимости выделять части сводной таблицы при форматировании отчета сводной таблицы. Форматирование, применяемое с помощью обычного выделения в Excel, сохраняется при обновлении или изменении макета.

Автоматическая сортировка (введено в версии 97).   Элементы отчета сводной таблицы могут сортироваться автоматически с сохранением порядка сортировки при обновлении данных или при изменении структуры отчета сводной таблицы.

Автоматическое отображение наибольшего или наименьшего элемента (введено в версии 97).   Возможно задание вывода наибольшего или наименьшего элемента данных и указание количества выводимых наибольших и наименьших элементов.

Сохранение дат в отчетах сводных таблиц как обычных чисел (введено в версии 97).   Сортировка и форматирование дат в отчетах сводных таблиц теперь выполняется так же, как и в остальной части листа.

Настройка параметров сводных таблиц

Настройка параметров сводных таблиц (введено в версии 97).   Установка параметров для всего отчета сводной таблицы теперь производится в отдельном диалоговом окне Параметры сводной таблицы. Это диалоговое окно доступно как в мастере отчетов сводных таблиц и диаграмм, так и в готовых отчетах сводных таблиц.

Выполнение запросов в фоновом режиме (введено в версии 97).   Запрос на получение данных отчета сводной таблицы из внешней базы данных может выполняться в фоновом режиме, что позволяет продолжать работу в Microsoft Excel во время загрузки данных.

Объединенные заголовки (введено в версии 97).   Существует возможность автоматического использования объединенных заголовков для внешних полей строк и столбцов отчетов сводных таблиц.

Формат полей страниц (введено в версии 97).   В сводной таблице может быть использовано несколько полей страниц. Для вывода большего количества полей страниц на экране их можно выводить по строкам или по столбцам.

Настройка отображения ошибочных значений и пустых ячеек (введено в версии 97).   Способ вывода ошибочных значений и пустых ячеек может быть задан пользователем, за исключением отображения встроенных ошибок, таких как #ССЫЛКА и #ИМЯ или вывода 0 (нуля) для пустых ячеек.

Оптимизация использования памяти (введено в версии 97).   Использование памяти для полей с более чем с 256 элементами оптимизировано, что позволяет небольшим сводным таблицам занимать меньше памяти.

Формулы и вычисления в отчетах сводных таблиц

Вычисляемые поля и элементы (введено в версии 97).   Существует возможность создания полей и элементов полей, выполняющих вычисления по пользовательским формулам над данными отчета сводной таблицы.

Список формул в отчете сводной таблицы (введено в версии 97).   Существует возможность отобразить список формул, использованных для вычисляемых полей и элементов.

Изменения порядка вычислений (введено в версии 97).   Если для вычисляемого элемента задано несколько формул, может быть установлен порядок, в котором они вычисляются.

Ссылки на данные отчета сводной таблицы в формулах (введено в версии 97).   Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ дает возможность использовать данные отчета сводной таблицы в формулах вне отчета сводной таблицы.

Тема 1.7. Автоматизация работы в табличном процессоре с помощью макросов.

Запись макроса

1. Выберите в меню Сервис команду Макрос и подкоманду Начать запись.

2.В поле Имя макроса введите имя для макроса.

Первым символом имени макроса должна быть буква. Остальные символы могут быть буквами, цифрами или знаками подчеркивания. В имени макроса не допускаются пробелы; в качестве разделителей слов следует использовать знаки подчеркивания.

Для того чтобы запускать макрос с помощью сочетания клавиш, введите букву в поле Сочетание клавиш. Допускается использование сочетаний CTRL+ буква (для строчных букв) или CTRL+SHIFT+ буква (для прописных букв), где буква — любая буквенная клавиша на клавиатуре. В сочетаниях клавиш нельзя использовать цифры или специальные символы, такие как «@» или «#». Выбранное сочетание клавиш подавляет стандартные сочетания клавиш Microsoft Excel на то время, пока открыта книга, содержащая данный макрос.
В поле Сохранить в выберите книгу, в которой требуется сохранить макрос.

Если требуется, чтобы макрос был всегда доступен при работе в Excel, сохраните его в личной книге макросов в папке XLStart.

Чтобы создать краткое описание макроса, введите необходимый текст в поле Описание.

Нажмите кнопку OK.

По умолчанию при записи макроса используются абсолютные ссылки. Макрос, записанный с абсолютными ссылками, при выполнении всегда обрабатывает те же ячейки, которые обрабатывались при его записи. Для того, чтобы с помощью макроса обрабатывать произвольные ячейки, следует записать его с относительными ссылками. Для этого нажмите кнопку Относительная ссылка

на панели инструментов Остановка записи. Относительные ссылки будут использоваться до конца текущего сеанса работы в Excel или до повторного нажатия кнопки Относительная ссылка

Выполните макрокоманды, которые нужно записать.
Нажмите кнопку Остановить запись на панели инструментов Остановить запись.

Совет. Если требуется, чтобы макрос сначала выбирал определенную ячейку, выполнял макрокоманду, а затем выбирал другую ячейку относительно активной, можно использовать при записи макроса и абсолютную, и относительную ссылки. При записи относительных ссылок кнопка Относительная ссылка

должна быть нажата. При записи абсолютных ссылок кнопка Относительная ссылка

не должна быть нажата.

Изменение макроса

Для изменения записанного макроса необходимо знакомство с редактором Visual Basic, который используется для написания и изменения макросов Microsoft Excel.

1.В меню Сервис выберите пункт Макрос, а затем выполните команду Макросы.

2.Введите имя макроса в поле Имя макроса.

3.Нажмите кнопку Изменить.

Выполнение макроса в Microsoft Excel

Для того чтобы прервать выполнение макроса, нажмите кнопку ESC.

Откройте книгу, содержащую нужный макрос.
Выберите в меню Сервис команду Макрос и подкоманду Макросы.
В поле Имя макроса введите имя того макроса, который требуется выполнить.
Нажмите кнопку Выполнить.

Назначение клавиши для запуска макроса

1.В меню Сервис выберите пункт Макрос, а затем выполните команду Макросы.

В поле Имя макроса введите имя того макроса, которому нужно присвоить сочетание клавиш, запускающее макрос.
Нажмите кнопку Параметры.
Чтобы выполнить макрос с клавиатуры с помощью сочетания клавиш, введите соответствующую букву в поле Сочетание клавиш. Для строчных букв используется сочетание CTRL+ буква, а для заглавных — CTRL+SHIFT+ буква, где буква — любая клавиша на клавиатуре. Заданное сочетание клавиш будет заменять любое установленное по умолчанию в Microsoft Excel, пока книга, содержащая данный макрос, открыта.

Чтобы создать краткое описание макроса, введите необходимый текст в поле Описание.

Нажмите кнопку OK.
Нажмите кнопку Отменить.

Удаление макроса

Откройте книгу, содержащую удаляемый макрос.
В меню Сервис выберите пункт Макрос, а затем выполните команду Макросы.
В списке Находится в выберите Эта книга.
В списке Имя выберите имя макроса, который нужно удалить.
Нажмите кнопку Удалить.

Примечание: Чтобы удалить все макросы из книги, удалите все макросы, перечисленные в диалоговом окне, и сохраните книгу.

Проверка книг на макровирусы

Для предотвращения заражения компьютера вирусом, содержащимся в макросе, используйте следующую процедуру для вывода предупреждения при каждой попытке открыть книгу, содержащую макросы. Так как сообщение появляется независимо от наличия вируса в макросе, прежде чем начать работу необходимо убедиться в надежности источника, из которого получена книга. Например, заражение книги вирусом возможно в незащищенной сети или на сервере Интернета.

В меню Сервис выберите команду Макрос и затем — Безопасность.
Выберите вкладку Уровень безопасности.
Установите переключатель в положение Средняя.

Новые возможности обеспечения безопасности

Цифровая подпись макроса (новая возможность в Office 2000).   Разработчики имеют возможность подписывать свои макросы, созданные с помощью Visual Basic for Applications (VBA), что позволяет пользователям установить автора макроса, удостовериться в надежности макроса и отсутствии в нем вирусов. Microsoft Office 2000 распознает макросы, полученные из надежных источников, и не выдает на экран предупреждающего сообщения, если такой макрос не был изменен.

Проверка на вирусы (новая возможность в Office 97).   В Microsoft Excel предусмотрена возможность отображения предупреждающего сообщения при открытии книги, содержащие макросы, которые могут содержать вирусы. Такие макросы можно отключить и открыть книгу без них.

Защита общих книг и журналов их изменений (новая возможность в Office 97).   Общую книгу можно защитить таким образом, что другие пользователи не смогут сделать ее недоступной для совместного пользования, и не смогут отключить запись изменений в журнале. Это гарантирует запись всех изменений.

Тема 1.8. Совместное использование текстового редактора и электронных таблиц.

Одна из возможностей совместного использования – создание конвертов.

Ч

тобы создать конверты путем слияния нужно в пункте меню «Сервис» выбрать команду «Слияние».

Кнопка «Создать» – позволяет выбрать вид документа для слияния.

Кнопка «Получить данные» – позволяет выбрать файл – источник.

Кнопка «Объединить» – позволяет сделать отбор записей для слияния.

Имена полей, созданные на форме заключаются в кавычки

Например: «ФИО»

Редактирование полей выполняется также, как и обычный текст.

Blog