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


	Загрузить архив:
	Файл: ref-13326.zip (37kb [zip], Скачиваний: 126) скачать

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

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

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

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

1.Назначение редакторов электронных таблиц. Возможности MSEXCEL. Структура документа 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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Прежде всего нужно промаркировать группу ячеек, содержимоекоторых должно быть удалено. Затем можно воспользоваться одним из способов удаления данных. Для этого в меню «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»:, в котором показано,какбудет выглядеть содержимое ячейки в выбранном формате.

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

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

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

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

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

Прежде всего надо промаркировать ячейки, в которых нужно изменить шрифт.Послеэтого можно либо открыть контекстное меню правой кнопкой мыши ивызвать директиву«FormatCells», либовызвать директиву«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. Выбор типа линии.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

	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. Использование логических функций: ЕСЛИ, И, ИЛИ.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Если вы внесете изменения в ту часть таблицы, по которой строилась диаграмма, то программа 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.

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

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

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

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

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

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

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

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

Директивы добавления расположены в меню «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» в перечне значений поля,по которому выполнялась фильтрация.

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

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

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

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

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

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

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

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

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

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

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

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

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

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