Excel 70 методичка

Введение

Идеальным средством для решения большого класса задач автоматизации являются программные продукты, называемые «электронные таблицы».

В данных методических указаниях описаны наиболее важные элементы программы Excel 7.0, основные технические приемы, необходимые при работе с электронными таблицами.

Технология работы с программой Excel 7.0 излагается в сжатой, иногда в алгоритмической форме, что позволяет легко и быстро найти ответ на решение конкретной проблемы.

Методическое пособие предназначено для выполнения студентами лабораторных работ дневной и заочной формы обучения специальности 0104.

Запуск

Excel 7.0

Программа установки Excel 7.0 помещает команду вызова Excel непосредственно в меню Программы.

Запуск Excel можно осуществить используя меню:

Пуск – Программы – Microsoft Excel
.

Удобнее запускать Excel , щелкнув по пиктограмме файла, с которым недавно работали:

Пуск → Документы → щелчок мышкой по пиктограмме файла, сожданного пакетом
Microsoft Excel.

Пункт Документы вызывает меню, в котором накапливается имена документов, открывавшихся и редактировавшихся пользоавтелем при работе с Windows 95
(до 15 последних файлов).

Выход из

Excel 7.0

Выход из Excel 7.0 используя основное меню осуществляется так:

Файл → Выход

Иначе процесс выхода из Excel 7.0 можно выпонить:

1. Щелкнуть на кнопке закрытия в верзнем правом углу окна Excel (или нажать комбинацию клавиш Alt+ F4
).

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

3. Щелкнуть на кнопке Пуск
и выбрать пункт Завершение раб
оты.

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

Экран

Excel 7.0

Экран Excel содержит следующие основные компоненты (рис. 1):

- строка заголовка с кнопками управления окном программы и окном документа;

- строка основного меню;

- две панели инструментов Стандартная
и Форматировани
я;

- строка формул и поле имени;

- рабочая область;

- полоса прокрутки;

- полоса перебора страниц рабочей книги;

- строка состояния.

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

Кнопки управления окном программы и окном документа (рис. 1):

- свернуть в пиктограмму, кнопка минимизации;

- развернуть во весь экран, кнопка максимизации;

- вернуть окно в предыдущее состояние, кнопка восстановления;

- закрыть окно, кнопка закрытия;

- кнопка системного меню. Щелчок на этой кнопке откроет системное меню, позволяющее манипулировать окном Excel.

Основное меню

Каждый из пунктов меню объединяет набор команд, имеющих общую функциональную направленность (рис. 2). Щелчок на любом пункте меню разворачивает список команд выбранного пункта. Работа с меню – один из возможных способов задавать команды Excel.

Контекстное меню

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

При выполнении щелчка правой
кнопкой мыши на пинтограмме, ячейке, выделенной группе ячеек или на встроенном объекте, возле указателя мыши открывается меню с основными функциями, применяемыми в данной ситуации – в контексте.

Команды, входящие в контекстное меню, всегда относятся к активному объекту.

Контекстное меню можно вызват также с помощью комбинации клавиш Shift+F10.

Панели инструментов

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

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

При движении вдоль пинтограмм этих панелей указателем мыши, под каждой из них появляется имя инструмента в окне (орудие намеков).

Настройка панелей инструментов (пиктографических меню) предоставляет следующие возможности:

- отображение на экране пиктографических меню;

- добавление отдельных пиктограмм;

- создание пользовательского пиктографического меню;

- создание собственных пиктограмм пользователя и привязка к ним определенных действий.

Все операции по форматированию пиктографических меню выполняются в окне:

Вид → Панели инструментов

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

Панели инструментов объединяют функционально близкие пиктограммы: Стандартная
– стандартные возможности системы (создание, загрузка, сохранение, печать рабочих папок и др.);

Форматирование
– оформление ячеек таблицы;

Запрос и сводная
– средства работы с базами данных таблицы;

Диаграмма
– инструменты построения диаграмм;

Рисование
– элементы схем и иллюстраций;

Мастер подсказок
– подсказка, помогающая оптимизировать рабочие шаги пользователя;

Элементы управления
– создание стандартных управляющих элементов (меню, кнопок, полос прокрутки …);

Запись макроса
– закончить запись последовательности команд, которые автоматически преобразуют Excel в макрокоманду;

Зависимости
– анализ связи между ячейками;

Коллективная работа
– совместная работа с документами;

Visual Basic
– инструменты создания и отладки пользовательских программ на встроенном языке Excel - Visual Basic;

Microsoft
– вызов приложений Microsoft для работы в рамках механизма OLE.

Панель формул (строка формул)

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

Поле имени указывает имя или адрес активной ячейки в текущей рабочей книге. Щелчок на стрелке раскроет список, в котором перечислены все поименованные ячейки и диапазоны активной рабочей книги.

Создание собственного пиктографического меню

Для создания собственного пиктографического меню необходимо:

1. Ввести имя панели в поле «Имя панели инструментов».

2. Нажать кнопку MENU или ОК.

Добавление пиктограмм в одно из пиктографических меню

Технология добавления пиктограмм в одно из пиктографических меню состоит в следующем:

1. Выбрать в меню Вид
® Панели инструментов
® Настройка
.

2. Выбрать категорию в списке категорий.

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

Возврат панели в исходное состояние.

Чтобы возвратить панели в исходное состояние необходимо:

1. Выбрать Вид
® Панели инструментов.

2. Выбрать название панели.

3. Сброс.

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

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

1. Перейти в окно Настройка
панелей инструментов и перетащить какую-нибудь пиктограмму из категории Пустые кнопки
на требуемую панель инструментов.

2. Щелкнуть по пустой пиктограмме, появится диалоговое окно, позволяющее связать с этой пиктограммой макрос.

Рабочие таблицы.

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

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

Перемещение между листами:

- переход к определенному листу – щелчок по его ярлыку

<- переход к первому ярлыку

- переход к последнему ярлыку

- переход к предыдущему / последнему ярлыку

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

Скопировать лист можно удерживая клавишу CTRL нажатой при перемещении мыши.

Excel располагает до 255 листов, каждый из которых разделен на 256 столбцов и 16384 строк. Столбцы обозначают буквами: A, B, C, …AA,..AZ…,BA…(или цифрами 1,2,3. . . 256). Строки обозначают всегда цифрами. В каждом столбце может помещатся от 0 до 255 символов.

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

Вся информация заносится в клетки, адреса которых получаются из буквы столбца и номера строки (А9, С16, D29). Текущая клетка обведена утолщенной рамкой – курсором.

Перемещение курсора по листу при помощи клавиш:

¬, ­, ®, ¯, - перемещение на одну клетку

Pg Up, Pg Dn – экран вверх, экран вниз

Home –на первую клетку строки

End – на последнюю клетку строки

Ctrl + Home –на первую клетку таблицы

Ctrl + End –на последнюю заполненную клетку

Перемещение по рабочему листу с помощью меню:

Правка ® Перейти ® адрес клетки ® переход в нужную клетку

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

Для быстрой прокрутки к другой части рабочего листа нужно протянуть бегунок.

Строка состояния

Эта строка находиться в нижней части экрана (рис.1 ). На ней находится следующая информация:

- текущая операция (например Enter);

- калькулятор (например Sum=0);

- выделение /добавление (EXT);

- режим Caps Lock (Caps);

- режим Num Lock (Num);

- (фиксированный десятичный формат Fix).

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

Режим выделения включается клавишей F8 и отображается кодом EXT (ВДЛ). Этот режим имитирует нажатие клавиши Shift + F8 и обозначается как ADD (ДОБ). Он позволяет добавить новые диапазоны ячеек к уже имеющемуся выделению. Отменить эти режимы можно клавишей ESC.

Режимы Caps Lock и Num Lock являются активными, если горят соответствующие индикаторы на малой цифровой клавиатуре. Отмена этих режимов – повторное нажатие соответствующей клавиши.

Режим фиксированного десятичного формата включается последовательным выбором команд меню:

Сервис ® Параметры ® фиксированный десятичный формат при вводе.

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

Кнопка выделения всех ячеек рабочей таблицы выделяет все ячейки активной рабочей таблицы в текущем окне.

Кнопки прокрутки ярлычков листов позволяют прокручивать ярлычки листов во время просмотра.

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

Горизонтальная полоса прокрутки позволяет прокручивать лист по горизонтали.

Вертикальная полоса прокрутки позволяет прокручивать лист по вертикали.

Выделение элементов таблицы

Выделение клеток с помощью мыши:

- отметить одну клетку – щелкнуть по ней мышкой;

- отметить столбец – щелкнуть на букве столбца;

- отметить строку – щелкнуть по цифре строки;

- отметить непрерывный прямоугольник:

1. щелкнуть на левой верхней клетке,

2. показать указателем мыши на нижнюю правую,

3. Shift +щелчок;

- отметить прерывистый блок:

1. щелчок на первой клетке,

2. Ctrl +щелчок на каждой нужной клетке

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

- отметить все строки и столбцы – щелкнуть на «чистой» кнопке в левом верхнем углу таблицы.

Выделение клеток можно осуществлять с помощью клавиатуры:

- выделение прямоугольной области:

F8 – левый верхний угол;

F9 –правый нижний угол;

- выделение нескольких прямоугольный областей:

Shift + F8 – переход к выделению несмежных областей, который выполняется с помощью той же клавиши F8 описанным выше способом;

- выделение колонки:

курсор находится в выделенной колонке Ctrl +пробел;

- выделение строки:

курсор находится в выделяемой строке Shift +пробел;

- выделение всей таблицы:

Ctrl + Shift +пробел.

Типы данных.

Каждая ячейка в Excel может содержать данные одного из трех типов:

- текст,

- число, формула (начиная со знака равно).

Ячейка текстового типа данных может содержать слова, предложения, произвольный набор символов.

Ячейка числового типа может содержать числа в диапазоне – 1.67*10 …1, 67*10, при этом количество значимых цифр – на более 15.

Excel предоставляет возможность вводить числа в различном формате. Можно вводить десятичные числа, денежные единицы, проценты и представлять числа в экспоненциальном виде.

Чтобы задать или изменить формат ячейки или выделенных групп ячеек необходимо:

Выбрать в меню Формат
®
Ячейки
®
Число
или нажать Ctrl +1

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

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

Формулы автоматически пересчитывают свои значения, как только один из их аргументов изменен.

Ввод данных

Данные вводятся непосредственно в клетку. Для ввода данных в клетку необходимо:

1. Указатель мыши поместить в клетку, в которую необходимо ввести данные. В строке формул появятся координаты выбранной клетки.

2. Ввести данные с клавиатуры.

3. Нажать клавишу Enter или щелкнуть по любой другой клетке.

Редактирование данных

Чтобы отредактировать содержимое клетки нужно:

1. Сделать редактируемую клетку активной.

2. Нажать клавишу F2, либо двойной щелчок на редактируемой клетке, либо щелкнуть на панели формул и редактировать непосредственно в панели формул.

3. Enter – окончание редактирования.

При редактировании можно использовать клавиши Delete и Backspace для удаления символов и клавишу Insert для управления режима вставки замены.

Для удаления содержимого клетки или нескольких выделенных клеток необходимо:

- выделить удаляемую область;

- выбрать в меню Правка ® Удалить

Перемещение данных в другую позицию выполняется следующим образом:

- выделить перемещаемую область;

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

С помощью меню этот же процесс заключается в следующем6

- выбрать в меню – Правка ® Вырезать;

- перейти в новую позицию;

- выбрать Правка
® Вставить
.

Вставка новых строк в таблицу требует следующих действий:

- установить курсор в ячейку столбца, перед которым нужно вставить столбец

- выбрать в меню Вставка ® Столбец

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

При редактировании данных формулы автоматически пересчитываются.

Работа с листами, строками, колонками, используя меню Excel заключается в следующем:

1. спрятать рабочий лист:

- выбрать в меню Формат
® Лист
® Скрыть
;

2. восстановить спрятанный рабочий лист:

- выбрать в мен. Формат ® Лист ® Показать;

3. спрятать колонку или строку:

- выделить строку или столбец;

- выбрать в меню Формат
® Строка / Столбец
®Скрыть

4. Восстановить спрятанную строку или столбец:

- выделить две соседние колонки (вокруг спрятанной) или две соседних строки;

- выбрать в меню Формат
® Строка / Столбец
®Показать

Используя контекстное меню можно выполнить:

1. спрятать колонку или строку

- выделить строку или столбец

- щелкнуть правой клавишей по выделенной область

- в появившемся контекстном меню выбрать Скрыть
.

2. восстановить спрятанную строку или столбец:

- выделить две соседние колонки (вокруг спрятанной) или две соседних строки;

- подвести курсор справа к спрятанным столбцам или снизу в спрятанным строкам (указатель мыши превратится в знак равенства с растяжкой);

- сделать двойной щелчок мышей или вызвать контекстное меню;

- выбрать Показать
.

Вычисления. Формулы и функции

Формулой называется введенная в ячейку последовательность символов, начинающаяся со знака равенства «=». В эту последовательность символов могут входить: константы, адреса ячеек, функции, операторы.

Результат вычислений отображается в ячейке, а сама формула – в строке формул.

Функции используются для выполнения стандартных вычислений. Excel имеет более 400 встроенных функций, объединенных в 9 групп: финансовые, дата и время, математические, статистические, ссылки и массивы, работ м базой данных, текстовые, логические, проверка свойств и значений.

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

Правила работы с формулами и функциями

Можно выделить следующие правила работы с формулами и функциями:

1. Формулы начинаются со знака «=»

2. Формулы могут включать обращения к одной или к нескольким функциям.

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

4. Аргументы перечисляются через запятую.

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

6. В формулах недопустимы пробелы.

7. Функция не может иметь более 30 аргументов.

8. В формуле можно использовать знаки арифметических операций: +, -, /, *, ^ (возведение в степень), % ( взятие процента) (приложение 2).

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

10. Длина формулы не должна превышать 1024 элементов.

Перемещение и копирование содержимого клетки.

Копирование содержимого клетки (или блока клеток) включает следующие шаги:

- выбрать копируемую клетку или блок клеток;

- выбрать меню Правка
® Копировать
или вызвать контекстное меню и выбрать Копировать
, или нажать Ctrl+C;

- перейти в новое место;

- выбрать меню Правка
® Вставка
или вызвать контекстное меню и выбрать Вставка
, или нажать Ctrl +V
.

При копировании формула перенастраивается на новые адреса. Чтобы при копировании формулы адрес некоторой ячейки был абсолютным ( не перенастраивался на новые адреса) надо после указания этого адреса во время формирования формулы нажать F4 или записать адрес в виде, например $$$4.

При перемещении формулы в новое место таблицы ссылки в формуле не изменяются.

Вычисление части формулы

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

- установить курсор в клетку, содержащую формулу;

- в строке формул выделить часть формулы, которую нужно вычислить;

- нажать клавишу F9 – вычисление формулы (при нажатии Enter – результат вычислений вставится в формулу, а при нажатии Esc – осуществится возврат формулы в исходное состояние).

Специальное копирование

При копировании не всего содержимого клетки, а только какой-то составляющей (формата, значения) применяется специальное копирование, которое можно выполнить:

1. с помощью меню:

- выбрать копируемую клетку или блок клеток;

- выбрать в меню Правка
® Копировать
;

- перейти в новое место;

- выбрать в меню Правка
®Специальная вставка.

2. с помощью контекстного меню:

- выбрать копируемую клетку или блок клеток;

- вызвать контекстное меню;

- выбрать Копировать
;

- перейти в новое место;

- выбрать Специальная вставка
.

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

Можно произвести копирование с тем, что находится в клетке, куда копируем. В зависимости от флага в колонке Операция
можно:

- просто копировать;

- сложить содержимое клетки с копируемым значением;

- вычесть из значения в клетке копируемое значение;

- умножить значение в клетке на копируемое значение;

- разделить значение в клетке на копируемое значение.

Если установлены соответствующие флаги, то при копировании можно:

- пропустить пустые ячейки (не копировать штриховку, бордюр, формат, шрифт ячейки, если в них отсутствуют значения);

- транспортировать;

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

Автоматическое заполнение данных

Для автоматического заполнения ряда чисел, дат, перечисляемых названий необходимо:

- ввести в клетку первое значение;

- сделать эту клетку активной;

- установить указатель мыши в правый нижний угол клетки (курсор при этом примет вид +);

- перетащить в сторону или вниз ( шаг равен 1).

Если необходимо задать шаг, отличный от 1, то следует выполнить:

- задать два значения в двух соседних клетках;

- эти клетки выделить;

- установить курсор в правый нижний угол выделенных клеток так, чтобы он принял вид +;

- перетащить курсор в сторону или вниз.

Если при автозаполнении клетка не распознана как перечисляемая, то происходит копирование.

Создание своей последовательности для заполнения

Для создания своей последовательности пользователь должен в меню выбрать:

Сервис
® Параметры
® Списки
® Элементы списка

В опции Списки
выбрать опцию Новый список
. В опции Элементы списка
внести свободную последовательность, затем щелкнуть по клавише Добавить
(введенная последовательность при этом запоминается).

Если нужная последовательность уже существует в рабочем листе, то ее достаточно выделить и передать в это окно, щелкнув на кнопке Импорт
.

Мастер функций

При вводе формулы обращение к встроенной функции можно выполнить вручную или вызвать Мастер функций. Эту же операцию можно выполнить через меню Excel: выбрать в меню Вставка ® Функция. Либо на панели инструментов или на панели формул выбираем инструмент fx
. В левом окне выбрать категорию функции, в правом – саму функцию. Выбрав кнопку шаг > выполнить пошаговую подстановку аргументов. Значение аргумента можно записать вручную, а можно выделить на листе диапазон клеток. Результат вычислений получим, щелкнув на кнопке Закончить
.

Создание текстовых документов

Excel предоставляет широкие возможности для формирования сложных текстовых документов. Для этого предназначены средства выбора шрифтов, выравнивания, выбора рамок, штриховок.

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

Для изменения шрифтов можно воспользоваться пиктограммами на панели форматирования: выбор шрифта и его размера, шрифты полужирный, наклонный, и подчеркнутый.

А. Выбор шрифтов при помощи меню:

- выделить клетку или диапазон клеток, в которых надо изменить шрифты;

- выбрать в меню Формат ® Ячейки ® Шрифт;

- выбрать нужный шрифт и щелкнуть по кнопке ОК.

Б. Выравнивание содержимого ячеек осуществляется с помощью Панели форматирования, которая имеет четыре пиктограммы:

- прижать текст в клетке к левому краю;

- расположить текст по центру клетки;

- прижать текст в клетке к правому краю;

- расположить текст по центру нескольких выбранных ячеек.

Выравнивание содержимого ячеек при помощи меню:

- выделить в меню Формат
® Ячейки
® Выравнивание
;

- выбрать нужную позицию;

- щелкнуть по кнопке ОК.

Г. Обрамление таблиц в рамки.

При печати таблиц на бумаге линии сетки таблиц отсутствуют. Поэтому необходимо заключать поля таблиц в рамки.

В Excel функцию установки рамок для выделенных ячеек выполняет пиктограмма панели форматирования Рамки.

Если щелкнуть мышкой по стрелке, то в раскрывшемся списке будут предоставлены шаблоны рамок для выбранных ячеек. Самый первый шаблон убирает рамки.

Для обрамления таблиц при помощи меню необходимо:

- выделить группу ячеек, для которых необходимо установить рамки;

- выбрать в меню Формат
® Ячейки
® Рамка;

- выбрать тип рамки, тип линии, цвет линии обрамления;

- щелкнуть по кнопке ОК
.

Чтобы убрать сетку с экрана необходимо :

Выбрать в меню Сервис
®Параметры