Работа в среде EXCEL. Средства управления базами данных в EXCEL

Отделение ускоренного обучения,

Экономический факультет,

2 курс, 3 семестр, 2002

«Работа в среде EXCEL. Средства управления базами данных в EXCEL».

1. Назначение редакторов электронных таблиц. Возможности MS EXCEL. Структура документа Excel. Размеры электронной таблицы.

2. Способы получения справочной информации в среде Excel.

3. Идентификация ячейки, диапазона ячеек. Относительные и абсолютные адресные ссылки. Имя ячейки, диапазона ячеек.

4. Первичная и вторичная информация. Ввод информации в ячейку.

5. Средства редактирования (перемещение, поиск и замена в таблице; выделение, перемещение, копирование, удаление информации).

6. Средства форматирования (изменение числовых форматов, выравнивание данных, задание нужного шрифта и размера).

7. Применение к ячейкам. Средства проверки правильности заполнения ячейки.

8. Назначение специальной (частичной) вставки.

9. Оформление ячеек. Форматирование границ ячейки, узоров цветов.

10. Форматирование строк и столбцов (изменение размеров ячейки, объединение ячеек, перенос по словам, сокрытие строк или столбцов).

11. Условное форматирование. Автоформат.

12. Формулы. Ввод и копирование формул. Мастер функций, палитра формул.

13. Задание условий в Excel. Использование логических функций: ЕСЛИ, И, ИЛИ.

14. Диаграммы. Построение, редактирование.

15. Защита ячеек таблицы от несанкционированного доступа.

16. Графические возможности Excel. Внедрение объектов WordArt.

17. Работа на нескольких рабочих листах. Вставка, удаление, переименование рабочих листов. Ссылки на ячейки другого листа.

18. Понятия базы, записи, поля данных. Системы управления базами данных. Примеры.

19. Создание баз данных в EXCEL. Размер базы данных. Сохранение базы данных.

20. Ввод данных с помощью формы базы данных.

21. Сортировка базы данных.

22. Поиск данных с помощью средства автофильтра. Восстановление исходной базы после поиска.

23. Расширенная фильтрация с помощью диапазона критериев.

24. Создание промежуточных итогов. Функции подведения промежуточных итогов. Свёртывание записей промежуточных итогов. Удаление промежуточных итогов.

25. Поиск данных с помощью функций баз данных.

26. Подготовка документации к печати в Excel.

Назначение редакторов электронных таблиц. Возможности MS EXCEL. Структура документа Excel. Размеры электронной таблицы.

Рабочее окно программы Excel содержит стандартную строку заголовка и панели команд. Строка под ними содержит поле имени и строку имени. Строка состояния программы Excel выдаёт информацию о текущем выборе, команде или операции.

Рабочая книга – многостраничный документ в Excel. Каждая страница рабочей книги называется рабочим листом, а страница, активная в данный момент, отображается в окне документа. Каждый рабочий лист разделён на столбцы, строки и ячейки, отдельные друг от друга разделительными линиями. Столбцы – это вертикальные разделы, строки – горизонтальные. Ячейка – это область пересечения строки и столбца.

Способы получения справочной информации в среде Excel.

Меню Справка (Help), содержит следующие команды: «Справка по Microsoft Excel», которая выводит на экран помощника, если он не отключён; «Скрыть помощника»; «Что это такое»? которая позволяет вывести подсказку об элементе, указанном на экране; «Office на Web», запускающая ваш браузер (программу просмотра, называемую ещё обозревателем) и подключающая его к узлам Microsoft в Интернете, которые предлагают дополнительную справочную информацию; «О программе», сообщающая информацию о программе Microsoft Excel. Две команды не появляются в кратком меню и для их отображения нужно нажать двойную стрелку в нижней части меню. Команда «Найти и устранить» пытается исправить все ошибки, которые могли произойти при установке Excel.

1. Окно справочной системы.

Окно предлагает три вкладки: «Содержание», «Мастер ответов» и «Указатель».

Вкладка «Содержание» подобна оглавлению книги, где каждая глава представлена заголовком со значком закрытой книги. После нахождения интересующего вас раздела можно вывести его содержимое в правой части окна справки. Часто разделы содержат ссылки на другие разделы и справки. Подчёркнутый текст в правой области окна справки является гиперссылкой на другой раздел. Щелчок на гиперссылке активизирует соответствующий раздел. Это напоминает работу с браузером.

Для поиска нужной информации можно также использовать вкладку «Указатель». Если вы введёте ключевое слово и нажмёте кнопку «Найти» или дважды щёлкните на нужном слове в списке, то получите список всех подходящих разделов справки.

1. Получение справочной информации через Интернет.

Если имеется доступ к Интернету, для вас доступны дополнительные ресурсы. Команда «Office на Web» запускает браузер и подключает его к странице Office Update на Web-узле Microsoft. Этот узел постоянно обновляется, так что там можно найти свежую информацию.

1. Контекстная справка.

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

1. Всплывающие подсказки.

Для тех, кому трудно запомнить назначение всех кнопок панелей инструментов, Excel предоставляет всплывающие подсказки. Если всплывающие подсказки включены, то при установке указателя мыши на кнопке панели инструментов появляется название кнопки. По умолчанию всплывающие подсказки включены. Чтобы отключить их, в меню «Вид» выберите команду «Панели инструментов», затем - «Настройка» и на вкладке «Параметры» открывшегося окна диалога снимите флажок «Отображать подсказки для кнопок».

Идентификация ячейки, диапазона ячеек. Относительные и абсолютные адресные ссылки. Имя ячейки, диапазона ячеек.

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

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

Первичная и вторичная информация. Ввод информации в ячейку.

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

Простые числовые значения могут содержать только цифры от 0 до 9 и специальные символы + - Е е ( ) . , $ % /. Текстовое значение может содержать практически любые символы.

Чтобы ввести числовое значение или текст, выделяйте ячейку и вводите с клавиатуры. Вводимые данные отображаются в строке формул и в активной ячейке. Мигающая вертикальная черта, которая появляется в строке формул и в активной ячейке называется точкой вставки. По окончании ввода данных их нужно зафиксировать, чтобы оно постоянно хранилось в ячейке. Простейшим способом фиксации ввода является нажатие клавиши Enter. После этого точка вставки исчезает, и Excel сохраняет введённое значение в ячейке. Если по окончании ввода нажать клавиши Tab, Shift+Tab, Enter, Shift+Enter или клавишу со стрелкой, Excel фиксирует ввод и активизирует соседнюю ячейку.

Средства редактирования (перемещение, поиск и замена в таблице; выделение, перемещение, копирование, удаление информации).

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

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

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

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

Действие, которое выполнено последним, можно отменить директивой «Undo» из меню «Edit» или комбинацией клавиш «Ctrl-Z». Операцию отмены можно также выполнить, щелкнув мышью по 11-й пиктограмме на панели управления.

Директива отмены после выполнения превращается в директиву подтверждения изменения «Redo». Выполнив эту директиву, можно снова ввести в таблицу измененные данные и т.д. Это можно сделать также с помощью клавиатурной комбинации «Ctrl-Z» или щелчка по 11-й пиктограмме.

Предыдущее действие можно повторить - для этого служит директива «Repeat» из меню «Edit». Для повторения последнего действия можно также нажать клавишу «F4» или щелкнуть по 12-й пиктограмме на панели управления.

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

Прежде всего нужно промаркировать группу ячеек, содержимое которых должно быть скопировано, а затем вызвать директиву «Copy» из меню «Edit». После этого маркированная область будет заключена в рамку. Теперь следует установить указатель мыши в позицию, куда должны быть скопированы данные, и нажать клавишу ввода «Enter». Содержимое маркированной области появится в новом месте. Если область, в которую нужно поместить копию, тоже маркирована, то размеры обеих групп должны быть одинаковыми, в противном случае будет выдано сообщение об ошибке.

В контекстном меню правой кнопки мыши также есть директива копирования. Для копирования можно также использовать комбинацию клавиш «Ctrl-C» или 8-ю пиктограмму на панели управления.

Если нужно удалить содержимое группы ячеек и поместить его на новое место в таблице, то следует использовать директиву «Cut» из меню «Edit». На панели управления этой директиве соответствует 7-я пиктограмма (ножницы), а на клавиатуре - комбинация клавиш «Ctrl-X».

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

Прежде всего нужно промаркировать группу ячеек, содержимое которых должно быть удалено. Затем можно воспользоваться одним из способов удаления данных. Для этого в меню «Edit» есть подменю «Clear», которое содержит следующие четыре директивы:

«All» - удаляет содержимое и отменяет формат ячейки;

«Formats» - отменяет только формат ячейки;

«Contents» - удаляет только содержимое ячейки;

«Notes» - удаляет только комментарии к ячейке.

Директива удаления «Clear Contents» есть также в меню правой кнопки мыши. С помощью этой директивы можно удалить только содержимое ячеек. Тот же результат достигается просто нажатием клавиши «Del». Если Вы случайно удалили нужную информацию, то воспользуйтесь комбинацией клавиш «Ctrl-Z», которая отменит директиву удаления.

Средства форматирования (изменение числовых форматов, выравнивание данных, задание нужного шрифта и размера).

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

Прежде всего нужно промаркировать ячейки, в которых надо изменить формат представления чисел. После этого можно либо открыть правой кнопкой мыши контекстное меню и вызвать в нем директиву «Format Cells», либо вызвать директиву «Cell» из меню «Format». В любом случае на экране появится диалоговое окно «Format Cells». По умолчанию в верхнем поле этого окна будет выбрана опция «Number», которая предназначена для форматирования чисел. В поле «Category» приведены различные типы форматов, которые выбираются щелчком мыши или с помощью клавиш управления курсором. В поле «Format» «Codes»: показаны коды форматов выбранного типа. В поле ввода «Code»: пользователь может задать код своего формата и запомнить его в списке форматов. В нижней части окна «Format» «Cells» находится поле просмотра «Sample»:, в котором показано, как будет выглядеть содержимое ячейки в выбранном формате.

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

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

Для выравнивания содержимого ячеек в программе Excel можно использовать либо директивы меню, либо пиктограммы на панели форматирования (4-я, 5-я, 6-я). Прежде всего надо промаркировать выравниваемые ячейки. Далее можно открыть контекстное меню правой кнопкой мыши и вызвать директиву «Format Cells», либо вызвать директиву «Cell» из меню «Format». В любом случае на экране появится диалоговое окно «Format Cells». В верхнем поле этого окна выберите опцию «Alignment». После этого в поле «Horizontal» можно выбрать одну из селекторных кнопок выравнивания: по левому краю (Left), по центру (Center) и по правому краю (Right). По умолчанию текст выравнивается по левому краю, а числа по правому.

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

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

Прежде всего надо промаркировать ячейки, в которых нужно изменить шрифт. После этого можно либо открыть контекстное меню правой кнопкой мыши и вызвать директиву «Format Cells», либо вызвать директиву «Cell» из меню «Format». В верхнем поле открывшегося окна «Format Cells» нужно выбрать опцию Font. В окне появится поле «Font»:, в котором можно выбрать вид шрифта из предлагаемого списка. Содержание этого списка зависит от установок, сделанных в среде Windows.

В правом поле «Size» устанавливается размер (кегль) шрифта. По умолчанию программа Excel устанавливает размер шрифта в 10 пунктов. В списке шрифтовых стилей «Font Style»: можно выбрать обычный стиль (Regular), курсив (Italic), жирный шрифт (Bold) и жирный курсив (Bold Italic). В поле «Underline» можно выбрать тип подчеркивания текста (одной или двумя линиями). В поле «Effects» расположены три опциональные кнопки, которые дают возможность зачеркнуть текст (Strikethrough) либо разместить его на месте верхнего (Superscript) или нижнего (Subscript) индексов.

Пиктограммы на второй панели управления (1-я, 2-я, 3-я) позволяют задать шрифтовые стили: жирное начертание (символ B), курсив (символ I), подчеркивание (символ U с чертой внизу).

Применение к ячейкам. Средства проверки правильности заполнения ячейки.

К ячейкам рабочего листа можно добавить примечания, содержащие пояснения к вычислениям, предположения либо какие-то напоминания. Выделите ячейку, на которой вы хотите добавить комментарий, и затем в меню «Вставка» выберите команду «Примечание» или нажмите кнопку «Создать примечание» на панели инструментов «Зависимости». Хотя к ячейке можно присоединить только одно примечание, его текст может быть любой длины. Если в окне примечания нужно начать новый абзац, нажмите клавишу Enter. С помощью маркеров изменения размеров вы можете изменить размеры этого окна.

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

Чтобы изменить примечание, выделите ячейку с присоединённым примечанием и затем в меню «Вставка» выберите команду «Изменить примечание».

Для удаления примечания откройте его для редактирования, затем щёлкните на границе его окна и нажмите клавишу «Delete» или щёлкните правой кнопкой мыши и в контекстном меню выберите команду «Удалить примечание»

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

Назначение специальной (частичной) вставки.

Иногда может возникнуть необходимость переместить или скопировать значение ячейки, не перенося при этом формулу, с помощью которой это значение получено. Также бывает нужно скопировать только формулу, но без формата ячейки. Команда «Специальная вставка» меню «Правка» предлагает удобный способ вставки только определённых элементов копируемых ячеек.

Переключатель «Формулы» позволяет перемещать только формулы из ячеек копируемого диапазона в ячейки диапазона вставки. Все форматы или примечания в диапазоне вставки остаются без изменения.

Переключатель «Форматы» позволяет переносить только форматы из копируемых ячеек в диапазон вставки. Этот режим равносилен выделению диапазона ячеек и выбору соответствующей команды в меню «Формат».

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

В случае установки переключателя «Условия на значения» в диапазон вставки копируются только условия на значения, применённые к исходным ячейкам. Переключатель «Всё» вставляет формулы, значения, форматы и примечания к ячейкам из копируемого диапазона в диапазон вставки.

Оформление ячеек. Форматирование границ ячейки, узоров цветов.

Границы и заливка ячеек могут быть хорошим средством для оформления различных областей рабочего листа или привлечения внимания к важным ячейкам. Пользуясь вкладкой «Граница» окна диалога «Формат ячеек» вы можете задать положение границ, а также тип и цвет линий.

1. Выбор типа линии.

Щёлкните на любом из тринадцати типов линии границы.

1. Выбор цвета линии.

По умолчанию цвет линии границы является чёрным, если на вкладке «Вид» окна диалога «Параметры» в поле «Цвет» установлено значение «Авто»

Чтобы выбрать цвет, отличный от чёрного, щёлкните на стрелке справа от поля «Цвет». Раскроется текущая 56-цветная палитра, в которой можно использовать один из имеющихся цветов или определить новый. Обратите внимание, что для выбора цвета границы нужно использовать список «Цвет» на вкладке «Граница» Если вы попытаетесь сделать это с помощью панели инструментов форматирования, то измените цвет текста в ячейке, а не цвет границы.

1. Выбор расположения границы.

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

Чтобы удалить все границы, имеющиеся в выделении, нажмите кнопку «Нет». Область просмотра позволяет контролировать размещение границ. Чтобы разместить границу, щёлкните на области просмотра в том месте, где должна находиться граница, или нажмите соответствующую кнопку рядом с этой областью.

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

Чтобы удалить размещённую границу, просто щёлкните на ней в области просмотра. Если нужно изменить формат границы, выберите другой тип линии или цвет и щёлкните на этой границе в области просмотра. Если хотите начать размещение границ заново, нажмите кнопку «Нет» в области «Всё».

1. Применение цвета и узоров.

Вкладка «Вид» окна диалога «Формат ячеек» используется для применения цвета и узоров к выделенным ячейкам. Эта вкладка содержит текущую палитру и раскрывающуюся палитру узоров. Палитра «Цвет» на вкладке «Вид» позволяет задать фон для выделенных ячеек. Если вы выберете цвет в палитре «Цвет», не выбирая узора, то заданный цветовой фон появиться в выделенных ячейках. Если выбрать цвет в палитре «Цвет», а затем – узор в раскрывающейся палитре «Узор», этот узор накладывается на цвет фона. Цвета в раскрывающейся палитре «Узор» контролируют цвет самого узора.

Форматирование строк и столбцов (изменение размеров ячейки, объединение ячеек, перенос по словам, сокрытие строк или столбцов).

1. Изменение размеров ячейки.

MS Excel по умолчанию использует ширину столбца, равную 8,43 символа. Часто стандартная ширина столбца оказывается недостаточной для полного вывода содержимого ячейки. Чтобы изменить ширину столбца с помощью мыши, установите указатель в области заголовков столбцов на линии, определяющей этот столбец от его соседа справа. Теперь при нажатой кнопки мыши перетащите линию раздела столбцов вправо или влево. При перетаскивании ширина столбца выводится виде экранной подсказки. Когда ширина достигнет нужной величины, отпустите кнопку мыши.

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

Настройка высоты строки аналогична настройке ширины столбца.

1. Объединение ячеек.

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

1. Скрытие строк или столбцов.

Иногда бывает нужно скрыть информацию в рабочем листе – например, заработную плату сотрудников и т.д. Excel позволяет скрывать целые столбцы или строки. Чтобы скрыть столбец, сначала выделите ячейку в этом столбце. В меню «Формат» выберите команду «Столбец» и затем - «Скрыть». Чтобы снова вывести столбец в рабочем листе, сначала выделите столбцы, расположенные слева и справа от скрытого столбца. После этого в меню «Формат» выберите команду «Столбец» и затем - «Отобразить».

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

Условное форматирование. Автоформат.

1. Условное форматирование.

Условное форматирование действует подобно будильнику. Оно позволяет применять форматы к конкретным ячейкам, которые остаются «спящими», пока значения в этих ячейках не достигнут некоторых контрольных значений.

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

Чтобы удалить условие форматирования, выделите ячейку или диапазон и затем в меню «Формат» выберите команду «Условное форматирование». После открытия окна диалога нажмите кнопку «Delete». Появиться окно диалога «Удаление условия форматирования». Укажите условия которые хотите удалить и нажмите «OK». Указанные условия исчезнут из окна диалога «Условное форматирование», фактически они не удаляются, пока в этом окне диалога не нажата кнопка «ОК». Если вы передумали, можете отказаться от удаления, нажав кнопку «Отмена» в окне диалога «Условное форматирование».

1. Автоформат.

Вы можете сберечь своё драгоценное время, используя команду «Автоформат» меню «Формат». Автоматические форматы Excel – это заранее определённые сочетания числового формата, шрифта, выравнивания, границ, узора, ширины столбца и высоты строки. Команда «Автоформат» использует существующие формулы и текстовые надписи, чтобы определить, какое форматирование применить. После выбора команды «Автоформат» можно обратиться к другим командам форматирования для частичного изменения отображаемых на экране значений. Если вас что-то не устраивает, выберите команду «Отменить автоформат» меню «Правка» и затем попробуйте добавить пустые столбцы или строки, чтобы отделить область, на которую не должна воздействовать команда «Автоформат». Также можно выделить только те блоки ячеек в листе, к которым можно применить автоформат.

Формулы. Ввод и копирование формул. Мастер функций, палитра формул.

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

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

В качества аргументов в формуле обычно используются числа и адреса ячеек. Для обозначения арифметических операций могут использоваться следующие символы: + (сложение); - (вычитание); * (умножение); / (деление).

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

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

1. Ввод формул.

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

Предположим, что в ячейке А1, таблицы находится число 100, а в ячейке В1 - число 20. Чтобы разделить первое число на второе и результат поместить в ячейку С1, в ячейку С1 следует ввести соответствующую формулу (=А1/В1) и нажать «Enter».

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

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

Применение сложных формул продемонстрируем на примере.

	B	C	D	E
10	РАБОТА	ВРЕМЯ	ЦЕНА	СУММА
11	Настройка клавиатуры	2	$ 17.80	$ 35.60
12	Обслуживание машин	4	$ 3.80	$ 15.20
13	Подготовка формуляров	8	$ 1.56	$ 12.48
14
15
16	Сумма			$ 63.28
17	НДС 15%			$ 9.49

Рис.1.

В столбце С, приведенной таблицы указано время (в часах), затраченное на выполнение работы, в столбце D - стоимость одного часа работы, а в столбце Е - промежуточная сумма, которую надо заплатить за эту работу. В ячейке Е16 нужно вычислить общую стоимость всех работ. Для этого туда надо записать следующую формулу: =Е12+Е13+Е14. Полученную сумму умножьте на 0,15, чтобы учесть налог на добавленную стоимость, и результат поместите в ячейке Е17: =Е16*0,15.

Для вычисления конечной суммы, подлежащей оплате (например, в ячейке Е19) надо сначала сложить промежуточные суммы, а затем результат умножить на 0,15. Формула должна иметь вид: =(Е12+Е13+Е14)*0,15. Конечно, можно было бы и просто просуммировать содержимое ячеек Е16 и Е17. Для сложения чисел можно также использовать функцию суммы SUM(), тогда формула будет выглядеть следующим образом: =Sum(E12:E14)*0,15.

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

Чтобы начать редактировать содержимое ячейки, нужно сначала промаркировать эту ячейку. На следующем шаге необходимо включить режим редактирования, нажав клавишу «F2» или выполнив двойной щелчок мышью. В режиме редактирования в верхней части экрана (под строкой пиктографического меню) активизируется наборная строка, в которой видна сама формула, а не результат ее вычисления.

Задание условий в Excel. Использование логических функций: ЕСЛИ, И, ИЛИ.

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

Функция ЕСЛИ имеет следующий синтаксис:

=ЕСЛИ (логическое_выражение; значение_если_истина; значение_если_ложь)

В функции ЕСЛИ можно также использовать текстовые аргументы.

Функции И, ИЛИ – позволяют создавать сложные логические выражения. Эти функции работают в сочетании с простыми операторами сравнения: =, <, >, <=, >=, и <>. Функции И и ИЛИ могут иметь до 30 логических аргументов и имеют следующий синтаксис:

=И (логическое_значение1; логическое_значение2; .....; логическое_значение30)

=ИЛИ (логическое_значение1; логическое_значение2; .....; логическое_значение30)

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

Диаграммы. Построение, редактирование.

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

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

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

Конструктор диаграмм является одним из наиболее мощных средств в программе Excel. Построение диаграммы с его помощью выполняется за несколько шагов. Конструктору указывается исходная область таблицы, тип диаграммы, используемые надписи и цвета. На основной панели имеется пиктограмма для вызова «Конструктора диаграмм».

1. Построение диаграмм.

Прежде чем строить диаграмму, необходимо закончить все работы в таблице, включая ее форматирование. Вызовите «Конструктор диаграмм», щелкнув на основной панели по 17-й пиктограмме.

Конструктор диаграмм будет ожидать информации о местонахождении диаграммы, поэтому указатель мыши приобретает форму пиктограммы Конструктора. Вы должны сделать с помощью указателя мыши прямоугольник на свободном месте рабочего листа, где будет размещена диаграмма. Для этого щелкните левой кнопкой мыши, а затем, не отпуская ее, отбуксируйте образовавшуюся после щелчка пунктирную рамке из верхнего левого угла прямоугольной области в правый нижний угол. Если диаграмму нужно построить на отдельном листе, то откройте в меню «Insert» подменю «Chart» и выберите в нем директиву «As New Sheet».

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

Чтобы выполнить два следующих шага, щелкните по командной кнопке «Next» и выберите из предложенных образцов тип и вид диаграммы соответственно. На четвертом шаге Конструктор предлагает предварительный просмотр получившейся диаграммы. На последнем (пятом) шаге остается только задать имя диаграммы, ее легенду (т.е. соответствие между столбцами диаграммы и табличными данными), а также указать надписи на осях диаграммы. В заключении щелкните по командной кнопке «Finish», и диаграмма появится в указанной области рабочего листа.

1. Типы диаграмм.

В меню «Format» появится директива «Chart Type». Вызовите эту директиву и в открывшемся диалоговом окне выберите подходящий тип диаграммы.

Однако проще всего воспользоваться графическим меню. Щелкните правой кнопкой мыши в области пиктографической панели и в открывшемся меню вызовите директиву «Chart». В верхней части экрана справа появится маленькое графическое меню. Первая пиктограмма этого меню определяет тип диаграммы. После щелчка по стрелке, которая находится рядом с этой пиктограммой, перед Вами откроется список, содержащий все типы диаграмм.

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

В меню «Insert» расположены директивы, которые позволяют придать диаграмме более привлекательный вид. В частности, здесь есть директива «Picture», которая импортирует рисунки в стандартных графических форматах (BMP, PCX и т.д.). После вызова этой директивы открывается окно загрузки графического файла. На диаграмме появляется выбранный рисунок, заключенный в рамку. Эту рамку можно перемещать по диаграмме с помощью мыши и изменять ее размер.

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

Рисунок на диаграмме можно нарисовать вручную с помощью пиктографической панели «Drawing», на которой есть все необходимые инструменты. Вызвать эту панель можно через меню правой кнопки мыши или директивой «Toolbars» из меню «View».

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

Если вы внесете изменения в ту часть таблицы, по которой строилась диаграмма, то программа Excel автоматически модифицирует диаграмму. Если же вы добавили в таблицу новую строку или столбец, то действуйте следующим образом. Промаркируйте новые данные в таблице и перенесите их с помощью мыши на диаграмму. Для этого поставьте указатель мыши на границу маркированной области и, не отпуская левой кнопки мыши, переместите ее на диаграмму. Как только вы отпустите кнопку мыши, диаграмма будет изменена (актуализирована). Эта техника получила в корпорации Microsoft название Drag & Drop или Drag & Plot.

Если диаграмма расположена на отдельном рабочем листе, то для ее актуализации можно использовать директиву New Data из меню Insert. В диалоговом окне этой директивы нужно указать область таблицы, которая была добавлена. Для этого либо промаркируйте эту область, либо укажите ее адрес. Закройте диалоговое окно щелчком по кнопке OK, и программа внесет в диаграмму соответствующие изменения. Если вы неудовлетворенны результатом актуализации диаграммы, то отмените ее директивой Undo из меню Edit.

Защита ячеек таблицы от несанкционированного доступа.

Для введения пароля необходимо вызвать директиве «Save As» из меню «File». В открывшемся диалоговом окне следует щелкнуть по командной кнопке «Options». Появляется диалоговое окно «Save Options». В поле ввода «Protection Password»: этого окна можно указать свой персональный пароль, тогда при каждом вызове этого файла программа будет запрашивать пароль. При вводе пароля на экране вместо букв появляются звездочки. После закрытия окна «Save Options» открывается окно «Confirm Password, в котором нужно подтвердить пароль. Если записать пароль в поле ввода «Write Reservation Password», то перед открытием этого файла появится диалоговое окно, в котором можно задать пароль или щелкнуть по кнопке «Read Only». В последнем случае файл можно просматривать, но нельзя записать его на диск под тем же именем.

Графические возможности Excel. Внедрение объектов WordArt.

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

Кнопка «Добавить объект WordArt» на панели инструментов «Рисование» открывает палитру замечательных шрифтовых стилей, которые вы можете использовать для создания выразительных текстовых графических объектов. После выбора в палитре подходящего стиля и нажатия кнопки ОК откроется окно диалога «Изменение текста WordArt». В этом окне диалога вы можете выбрать шрифт и его размер, ввести текст надписи. Сразу после создания и каждый раз при выделении законченного объекта WordArt на экран выводится панель инструментов WordArt.

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

1. Управление рабочими листами.

Отдельные рабочие листы одного документа расположены друг под другом. С помощью именного указателя (регистра имен), расположенного в нижней части экрана, можно переходить с одного листа на другой. На именном указателе находятся корешки рабочих листов, расположенные в порядке возрастания номеров: Sheet1, Sheet2 и т.д.

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

Щелчок по пиктограмме с правой стрелкой сдвигает на единицу вправо диапазон (окно) видимости корешков рабочих листов. Щелчок по пиктограмме с левой стрелкой сдвигает этот диапазон на единицу влево. Стрелки с вертикальными штрихами позволяют перейти соответственно к первому и к последнему листам документа.

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

Директивы добавления расположены в меню «Insert». Пользователь может добавить в документ элементы следующих типов:

- рабочие листы для создания таблиц;

- диаграммы (в качестве элемента таблицы или на отдельном листе);

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

Новый лист всегда вставляется перед активным рабочим листом. Если лист предназначен для создания таблицы, то независимо от занимаемой позиции он будет иметь название «Sheet17» с последующим увеличением номера при добавлении новых таблиц. Новые диаграммы, расположенные на отдельных рабочих листах, нумеруются, начиная с «Chart1» и т.д. Рабочие листы с макрокомандами нумеруются, начиная с «Macro1», а с диалоговыми окнами - начиная с «Dialog1» и т. д. Рабочие листы с программными модулями написанными на языке Visual Basic, нумеруются, начиная с «Module1». Пользователь может щелкнуть по названию рабочего листа правой кнопкой мыши, после чего откроется контекстное (зависящее от ситуации) меню, в котором также имеется директива добавления. Если нужно удалить рабочий лист, нужно открыть щелчком правой кнопки мыши контекстное меню и выполнить директиву удаления.

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

Установите указатель мыши на корешке рабочего листа, который нужно переместить, и щелчком правой кнопки мыши откройте контекстное меню. С помощью директивы «Move or Copy» откройте диалоговое окно с тем же названием и укажите в нем новую позицию переставляемого листа. Закройте окно «Move or Copy» щелчком по кнопке «OK», и рабочий лист займет новую позицию. Если включить опциональную кнопку «Create a Copy», то данный рабочий лист останется на прежней позиции, а новую позицию займет его копия. Название копии листа будет образовано путем добавления порядкового номера к имени копируемого листа, например, «Sheet1».

Можно переставить сразу несколько листов. Для этого промаркируйте эти листы, щелкнув по их именам в именном указателе при нажатой клавише «Shift». Затем выполните директиву «Move or Copy», которая в этом случае будет применяться сразу ко всем маркированным рабочим листам.

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

Установите указатель мыши на корешке рабочего листа, который нужно переименовать, и щелкните правой кнопкой мыши. Откроется контекстное меню, в котором с помощью директивы «Rename» нужно открыть диалоговое окно «Rename Sheet». Это окно можно также открыть двойным щелчком левой кнопки мыши по названию рабочего листа. В поле ввода «Name» укажите новое имя листа, которое должно содержать не более 31 символа, включая пробелы. После ввода имени щелкните по кнопке OK, и на именном указателе появится новое имя рабочего листа. При задании имен рабочих листов не должны использоваться следующие символы: квадратные скобки «[ ]»; двоеточие «:»; правая косая черта «/»; левая косая черта «\».

Понятия базы, записи, поля данных. Системы управления базами данных. Примеры.

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

Для сортировки информации базы данных сначала выберите все ячейки, а затем дайте команду «Данные» «Сортировка». Программа Excel выберет данные из базы и откроет диалоговое окно «Сортировка диапазона». Excel выбирает ячейки во всех направлениях от активной ячейки, пока не обнаружит пустую строку или столбец. Просмотрев верхнюю строку базы данных, программа либо решит, что она представляет собой запись и включит её в выборку, либо сочтёт, что это строка заголовков столбцов. Последняя часть диалогового окна «Сортировка диапазона» позволяет исправить ошибочный выбор, прямо указав, существует ли строка заголовков. Можно также сортировать базу данных с помощью кнопок сортировки на панели инструментов «Стандартная». Выбрав отдельную ячейку в столбце, по которому требуется сортировка, щёлкните на кнопке «Сортировка по возрастанию» или «Сортировка по убыванию».

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

Создание баз данных в EXCEL. Размер базы данных. Сохранение базы данных.

Размер баз данных в Excel ограничен числом строк на рабочем листе – 65536. Несмотря на эти и другие ограничения, средства управления данными в Excel – это мощный инструмент для создания небольших баз данных и работы с выборкой записей из крупных баз данных.

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

- Пустая строка указывает на окончание базы данных. Не оставляйте пустых строк между заголовками столбцов и записями данных.

- Имена полей располагаются в верхней части столбцов.

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

Ввод данных с помощью формы базы данных.

В Excel предусмотрено мощное, удобное и в то же время простое средство для облегчения ввода в базу данных – форма ввода данных. Для того, чтобы воспользоваться формой данных: 1) выделите диапазон базы данных. 2) выберите команду меню «Данные», «Форма». Откроется диалоговое окно с именем рабочего листа, на котором находится база данных. 3) С помощью полосы прокрутки выберите запись, которую необходимо отредактировать или удалить. 4) Нажмите кнопку «Удалить», если необходимо удалить запись. 5) Нажмите кнопку «Добавить», если нужно создать новую запись. При этом поля ввода очистятся, и в них можно будет ввести данные. 6) Если необходимо внести данные в область критериев, нажмите кнопку «Критерии». Для возврата к вводу в область базы данных нажмите кнопку «Форма». 7) С помощью кнопок «Назад» и «Далее» можно переходить на предыдущую и последующую записи соответственно. 8) По окончании ввода нажмите кнопку «Закрыть».

Поиск данных с помощью средства автофильтра. Восстановление исходной базы после поиска.

Откройте в меню «Data» подменю «Filter» и включите в нем опцию «AutoFilter». В таблице рядом с названиями полей появятся маленькие пиктограммы со стрелками. Щелкните по одной из этих стрелок, и на экране появится меню с перечнем значений данного поля.

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

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

Сортировка базы данных.

Для сортировки информации базы данных сначала выберите все ячейки, а затем дайте команду «Данные» «Сортировка». Программа Excel выберет данные из базы и откроет диалоговое окно «Сортировка диапазона». Excel выбирает ячейки во всех направлениях от активной ячейки, пока не обнаружит пустую строку или столбец. Просмотрев верхнюю строку базы данных, программа либо решит, что она представляет собой запись и включит её в выборку, либо сочтёт, что это строка заголовков столбцов. Последняя часть диалогового окна «Сортировка диапазона» позволяет исправить ошибочный выбор, прямо указав, существует ли строка заголовков. Можно также сортировать базу данных с помощью кнопок сортировки на панели инструментов «Стандартная». Выбрав отдельную ячейку в столбце, по которому требуется сортировка, щёлкните на кнопке «Сортировка по возрастанию» или «Сортировка по убыванию».

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

Расширенная фильтрация с помощью диапазона критериев.

Команда «Расширенный фильтр», в отличие от команды «Автофильтр», имеет некоторые дополнительные возможности. Можно задавать условия, соединённые логическим оператором ИЛИ, для нескольких столбцов. Допускается задавать вычисляемые условия (например можно вывести на экран список только тех сотрудников, у которых оклад хотя бы на 25% выше среднего). Кроме того команда «Расширенный фильтр» может использоваться для извлечения строк из списка и вставки копий этих строк в другую часть текущего листа. Поскольку при фильтрации скрываются целые строки, диапазон условий лучше поместить выше или ниже списка.

Диапазон условий должен содержать, по крайней мере, две строки. Введите один или несколько заголовков столбцов в верхней строке, а условия отбора – во вторую и последующие строки. За исключением вычисляемых условий, заголовки в диапазоне условий должны точно совпадать с заголовками столбцов в списке. Для обеспечения точности эти заголовки лучше всего создавать, выделяя заголовки столбцов в списке и копируя их в верхнюю строку диапазона условий с помощью команд «Копировать и Вставить» меню «Правка».

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

Создание промежуточных итогов. Функции подведения промежуточных итогов. Свёртывание записей промежуточных итогов. Удаление промежуточных итогов.

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

Для подведения простых промежуточных итогов: 1) Выделите диапазон, подлежащий обработке. 2) Выполните команду «Данные», «Итоги». Откроется окно «Промежуточные итоги». 3) Установите флажок «Заменить текущие итоги», если в выделенной области рабочего листа уже имеются какие-либо итоги, и их предполагается заменять. 4) При необходимости установите флажок «Конец страницы между группами» - если требуется, чтобы конец страницы не разрывал данные по одной группе. 5) Установите флажок «Итоги под данными» - в этом случае окончательные итоги будут выведены в нижней части таблице. 6) Нажмите кнопку «ОК».

При необходимости, наоборот, удалить ранее созданные промежуточные итоги, следует повторить описанные выше действия, но вместо кнопки «ОК» нужно нажать кнопку «Убрать всё».

Подготовка документации к печати в Excel.

Если просто выбрать в меню «Файл» команду «Печать» и нажать кнопку «ОК», MS Excel напечатает одну копию всей заполненной области текущего листа. Совсем не обязательно задавать диапазон печати, кА этого требуют некоторые другие программы электронных таблиц.

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

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

Область «Ориентация» определяет, как будет печататься рабочий лист: горизонтально или вертикально. Если установлен переключатель «Книжная», Excel располагает строки рабочего листа на печатаемой странице горизонтально. По умолчанию используется именно книжная ориентация. Альбомная ориентация удобна для печати страниц рабочих листов, которые не помещаются по ширине на стандартном листе бумаги.