Прикладная программа Excel

МОСКОВСКИЙ ЭКОНОМИКО-СТАТИСТИЧЕСКИЙ ИНСТИТУТ К У Р С О В А Я Р А Б О Т А Н А Т Е М У ПРИКЛАДНАЯ ПРОГРАММА EXCEL РУКОВОДИТЕЛЬ РАБОТЫ ИСПОЛНИТЕЛИ Смирнов А.А. Морозов С.В. Минаев В.В. Москва 1996 СОДЕРЖАНИЕ ВВЕДЕНИЕ 1. УПРАВЛЕНИЕ ФАЙЛАМИ 1. Создание нового документа 1.2. Загрузка рабочего документа 3. Сохранение документа 4. Автоматическое сохранение 5. Создание резервных копий 6. Защита данных 7. Дополнительные сведения о файлах 2. СТРУКТУРА ДОКУМЕНТОВ 1. Управление рабочими листами 2. Добавление рабочих листов 3. Перемещение рабочих листов 2.4. Переименование рабочих листов 5. Коррекция высоты строк и ширины столбцов 3. ПОСТРОЕНИЕ ТАБЛИЦ 1. Маркирование ячеек 2. Отмена операций 3. Копирование данных 4. Удаление данных 5. Форматирование чисел 6. Выравнивание данных 7. Установка шрифтов 4. ТАБЛИЧНЫЕ ВЫЧИСЛЕНИЯ 1. Ввод формул 2. Сложные формулы 4.3. Редактирование формул 4. Информационные связи 5. Групповые имена 5. ПОСТРОЕНИЕ И ОФОРМЛЕНИЕ ДИАГРАММ 1. Построение диаграмм 2. Типы диаграмм 3. Надписи на осях 4. Дополнительные объекты 5. Актуализация диаграмм 6. ФУНКЦИИ 1. Конструктор функций 2. Редактирование функций 6.3. Вычисление суммы 4. Вычисление среднего значения 5. Вычисление величины линейной амортизации 6. Комбинирование функций 7. Текстовый режим индикации формул 7. ОБМЕН ДАННЫМИ 1. Импортирование рисунков в Excel 2. Редактирование рисунков на рабочем листе 3. Включение таблицы в текст 8. ОБРАБОТКА СПИСКОВ 8.1. Создание списков 2. Ввод списка данных 3. Поиск элемента в списке 4. Редактирование списков 5. Автоматический фильтр 6. Комбинированная фильтрация 7. Сортировка списков 9. АНАЛИЗ ДАННЫХ 1. Опорные таблицы 2. Редактирование опорных таблиц 3. Нахождение значений 10. БАЗЫ ДАННЫХ 1. Запуск программы обработки баз данных 10.2. Оболочка программы Query 3. Загрузка внешнего набора данных 4. Критерии выбора данных 5. Комбинированные запросы 6. Обмен данными с внешней базой 11. КОНФИГУРИРОВАНИЕ ПРОГРАММЫ EXCEL 1. Создание пиктографического меню 2. Изменение вида основного окна 3. Изменение вида рабочего листа 11.4. Установка стандартного шрифта 5. Установка национальных параметров 6. Изменение цветов 7. Автоматическая загрузка документов 8. Шаблоны таблиц VISUAL BASIC 1. Программирование табличных функций 2. Встраивание функций 3. Применение функций ВВЕДЕНИЕ Программа MS Excel, являясь лидером на рынке программ обработки электронных таблиц, определяет тенденции развития в этой области.

Вплоть до версии 4.0 программа Excel представляла собой фактический стандарт с точки зрения функциональных возможностей и удобства работы.

Теперь на рынке появилась версия 5.0, которая содержит много улучшений и приятных неожиданностей.

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

Контекстные меню значительно расширены, а дополнительные программные инструменты облегчают решение сложных прикладных задач.

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

В программу Excel встроена удобная подсистема помощи, которая в любой момент готова выдать необходимую справку.

Описанные до сих пор новшества касаются в основном комфорта в работе и быстрого освоения программы.

Одним из важнейших функциональных расширений программы, предназначенным для профессионалов, является встроенная в Excel Среда программирования Visual Basic VBA для решения прикладных задач.

Благодаря VBA фирме Microsoft удалось не только расширить возможности языка макрокоманд Excel 4.0, но и ввести новый уровень прикладного программирования, поскольку VBA позволяет создавать полноценные прикладные пакеты, которые по своим функциям выходят далеко за рамки обработки электронных таблиц.

Кроме этого, следует назвать следующие важные новшества программы Excel 5.0 - менеджер файлов, который выдает подробную информацию о всех файлах - диалоговые окна-регистры - отдельная пиктограмма для форматирования - появление механизма Drag Plot, предназначенного для быстрой активизации диаграмм. 1.

УПРАВЛЕНИЕ ФАЙЛАМИ

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

Создание нового документа

Создание нового документа. Для создания нового документа из меню File нужно вызвать директиву New. На экране появится документ с именем Book2 программа Excel присваивает новым документам имя Book Книга с добавлением текущего порядкового номера.

Новый документ можно создать также, щелкнув мышью по первой пиктограмме, которая находится в первой основной пиктографической панели первая строка пиктографического меню . 2.

Загрузка рабочего документа

Можно также щелкнуть мышью по второй пиктограмме, которая находится на... Чтобы загрузить с диска расположенный там файл с рабочим документом, н... Если щелкнуть по имени этого файла, оно появится в поле File Name. . После этого нужно закрыть диалоговое окно, щелкнув мышью по кнопке OK ...

Сохранение документа

Не следует также использовать символы кириллицы, чтобы избежать непред... После того как будут сделаны все установки, нужно закрыть диалоговое о... Сохранение документа. При первом сохранении Вашего документа нужно выз... 1.4. .

Автоматическое сохранение

В открывшемся диалоговом окне включите опцию AutoSave, щелкнув по ней ... После этого снова откройте меню Tools, в котором должна появиться дире... Автоматическое сохранение. 1.5. .

Создание резервных копий

Создание резервных копий. Для создания резервной копии необходимо вызвать директиву Save As из меню File. В открывшемся диалоговом окне нужно щелкнуть по командной кнопке Options.

Появится следующее диалоговое окно, имеющее название Save Options. В этом окне нужно включить опциональную кнопку Always Create Backup. Теперь при сохранении файла будет автоматически создаваться резервный файл с тем же именем, что и основной, но с расширением. BAK. 1.6.

Защита данных

В поле ввода Protection Password этого окна можно указать свой персона... После закрытия окна Save Options открывается окно Confirm Password, в ... В последнем случае файл можно просматривать, но нельзя записать его на... . 1.7.

Дополнительные сведения о файлах

Дополнительные сведения о файлах. При первом сохранении файла директив... В этом окне пользователь может заполнить поля ввода Title , Subject и ... В поле Author по умолчанию указывается информация, полученная программ... В поле ввода Comments можно поместить соответствующие комментарии. Есл... 2.

СТРУКТУРА ДОКУМЕНТОВ

Это преимущество новых документов особенно проявляется при связывании ... 2.1. На именном указателе находятся корешки рабочих листов, расположенные в... Щелчок по пиктограмме с левой стрелкой сдвигает этот диапазон на едини... 2.2.

Добавление рабочих листов

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

Перемещение рабочих листов

Перемещение рабочих листов. С помощью директивы Move or Copy откройте диалоговое окно с тем же наз... Если включить опциональную кнопку Create a Copy, то данный рабочий лис... Название копии листа будет образовано путем добавления порядкового ном... 2.4.

Переименование рабочих листов

Переименование рабочих листов. Это окно можно также открыть двойным щелчком левой кнопки мыши по назв... После ввода имени щелкните по кнопке OK, и на именном указателе появит... Установите указатель мыши на корешке рабочего листа, который нужно пер... .

Коррекция высоты строк и ширины столбцов

При выборе одного из этих подменю открывается меню следующего уровня. 3. Например, для чисел и текстов задается определенный вид и размер шрифт... В программе Excel имеются контекстные меню, которые вызываются правой ... 3.1.

Маркирование ячеек

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

Отмена операций

Директива отмены после выполнения превращается в директиву подтвержден... Отмена операций. Действие, которое выполнено последним, можно отменить... 3.3. . Это можно сделать также с помощью клавиатурной комбинации Ctrl-Z или щ...

Копирование данных

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

Удаление данных

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

Форматирование чисел

. Форматирование чисел. Прежде всего нужно промаркировать ячейки, в кото... После этого можно либо открыть правой кнопкой мыши контекстное меню и ... В нижней части окна Format Cells находится поле просмотра Sample, в ко... По умолчанию в верхнем поле этого окна будет выбрана опция Number, кот...

Выравнивание данных

. После этого в поле Horizontal можно выбрать одну из селекторных кнопок... По умолчанию текст выравнивается по левому краю, а числа по правому. П... 3.7. Выравнивание данных.

Установка шрифтов

Содержание этого списка зависит от установок, сделанных в среде Window... В правом поле Size устанавливается размер кегль шрифта. В списке шрифтовых стилей Font Style можно выбрать обычный стиль Regul... В поле Underline можно выбрать тип подчеркивания текста одной или двум... 4.

ТАБЛИЧНЫЕ ВЫЧИСЛЕНИЯ

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

Сложные формулы

Сложные формулы. Применение сложных формул продемонстрируем на примере... В столбце С приведенной таблицы указано время в часах, затраченное на ... Полученную сумму умножьте на 0,15, чтобы учесть налог на добавленную с... Формула должна иметь вид Е12 Е13 Е14 0,15. Для сложения чисел можно также использовать функцию суммы SUM , тогда ...

Редактирование формул

В режиме редактирования в верхней части экрана под строкой пиктографич... Информационные связи. В программе Excel довольно легко ввести ссылку н... Если в названии листа есть пробелы, то оно название заключается в кавы... Затем нужно выполнить директиву Paste Special из меню Edit и в диалого... 4.5.

Групповые имена

Для этого нужно промаркировать соседние строки столбцы, включая первые... В открывшемся диалоговом окне нужно указать местонахождение имен в пер... Если теперь вызвать диалоговое окно Define Name, то в списке имен можн... Теперь при вводе формулы вместо перечисления адресов ячеек, образующих... рис.

ПОСТРОЕНИЕ И ОФОРМЛЕНИЕ ДИАГРАММ

Конструктор диаграмм является одним из наиболее мощных средств в прогр... Первая пиктограмма этого меню определяет тип диаграммы. После щелчка п... 5.3. На диаграмме появится текст, который можно редактировать и форматирова... Для этого нужно лишь щелкнуть по нему мышью, чтобы появилась текстовая...

Дополнительные объекты

. Эту рамку можно перемещать по диаграмме с помощью мыши и изменять ее р... Рисунок на диаграмме можно нарисовать вручную с помощью пиктографическ... Для вставки текста нужно ввести его с клавиатуры в наборную строку и н... В частности, здесь есть директива Picture, которая импортирует рисунки...

Актуализация диаграмм

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

Редактирование функций

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

Вычисление суммы

Вычисление суммы. Промаркируйте ячейку, где должна располагаться сумма, и щелкните мышью... Если Excel угадала правильно, щелкните по пиктограмме с галочкой. В противном случае промаркируйте группу ячеек, которые нужно сложить. Эту клавишу нужно удерживать в нажатом состоянии до тех пор, пока все ...

Вычисление среднего значения

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

Вычисление величины линейной амортизации

Вычисление величины линейной амортизации. Для вычисления величины линейной амортизации какого-либо оборудования ... В правом поле щелкните по аббревиатуре SLN функция линейной амортизаци... Конструктор выдаст краткое описание этой функции. 6.6.

Комбинирование функций

ОБМЕН ДАННЫМИ Обмен данными позволяет пользователю Excel импортировать... Она поддерживает новейший стандарт в обмене данными, который называетс... Эти информационные связи реализованы динамически, например, копия табл... . После этого запустите Конструктор функций и выберите внутреннюю функци...

Импортирование рисунков в Excel

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

Редактирование рисунков на рабочем листе

Дважды щелкните левой кнопкой мыши внутри рамки, в которой находится р... После этого автоматически запускается графический редактор Paintbrush,... Теперь в рисунок можно внести изменения, используя средства, предостав... После этого управление возвращается программе Excel, и измененный рису... 7.3.

Включение таблицы в текст

. После этого таблица будет вставлена в текстовый документ. Затем щелкните на основной панели по 5-й пиктограмме. Программа Excel ... Запустите программу Excel и промаркируйте таблицу, в которой находятся... Щелкните мышью в том месте текста, где нужно вставить таблицу.

ОБРАБОТКА СПИСКОВ

ОБРАБОТКА СПИСКОВ. Списки позволяют эффективно работать с большими однородными наборами д... Каждый элемент списка занимает одну строку, в которой данные распредел... В программе Excel списки являются специальным видом таблиц, для которы... Так называемые автоматические фильтры позволяют отобразить только те ч...

Создание списков

8.2. С технической точки зрения списки представляют собой обычные таблицы, ... Следите за тем, чтобы между ними не оставалось свободных столбцов. Эту... Названия полей используются программой Excel для поиска элементов в сп... .

Ввод списка данных

Затем можно начать ввод следующего элемента данных и т.д пока не будут... . Здесь нужно ответить утвердительно, щелкнув по кнопке OK. 8.3. Ввод списка данных.

Поиск элемента в списке

Поиск элемента в списке. После вызова директивы Form из меню Data в диалоговом окне появляется ... Теперь щелкните по командной кнопке Find Next. Например, можно ввести поиск элементов, у которых значение некоторого ... Если ни один элемент, удовлетворяющий критерию поиска, не найден, выда...

Редактирование списков

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

Автоматический фильтр

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

Комбинированная фильтрация

. Щелкните по командной кнопке OK, тогда в таблице появятся только те эл... После этого появится диалоговое окно Custom AutoFilter, в котором можн... Повторите те же действия для второго поля ввода. Щелкните по стрелке автофильтра в выбранном столбце, после чего на экр...

Сортировка списков

Сортировка списков. Вызовите директиву Sort из меню Data. Если исходные данные находятся в списке, то щелкните по селекторной кн... На втором шаге нужно указать область рабочего листа, где находится спи... Названия полей, по значениям которых будет проводится анализ данных, о...

Редактирование опорных таблиц

. Если в опорную таблицу необходимо добавить новые поля, то вызовите дир... Названия полей, по которым классифицируются данные, можно перемещать с... Редактирование опорных таблиц. 9.3.

Нахождение значений

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