Федеральное агентство по образованию ГОУ ВПО

Тульский государственный педагогический университет им. Л.Н. Толстого

Кафедра информатики

Курсовая работа

по теме

Создание сайта «Электронные таблицы Excel»

Выполнила:

студентка ф-та МиИ,

3 курса гр. «А»

Боброва И.С.

Проверил:

Якушина Т.В.

Тула 2006 г.

Содержание

Введение

Создание сайта «Электронные таблицы Excel»

Функциональные возможности табличного процессора Excel

Запуск и завершение работы Excel

Окно программы

Структура электронной таблицы

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

Операция перемещения, копирования, вставки

Средства автоматизации ввода и редактирования данных. Заполнение ячеек

Использование маркера автозаполнения

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

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

Типы данных в ячейках электронной таблицы

Адреса ячеек

Форматирование данных и защита информации в Excel

Защита информации таблицах Excel

Средства автоматического обобщения и анализа данных электронной таблицы

Задачи для самостоятельной работы

Заключение

Список литературы

Введение

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

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

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

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

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

Желаю удачи!

Функциональные возможности табличного процессора Excel

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

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

Табличный процессор Excel позволяет:

1. Решать математические задачи: выполнять табличные вычисления (в том числе как обычный калькулятор); вычислять значения и исследовать функции, строить графики функций(sin, cos, tg и т.д.); решать уравнения, работать с матрицами и комплексными числами и т.п.

2. Осуществлять математическое моделирование и численное экспериментирование(Что будет, если? Как сделать, чтобы?).

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

4. Реализовать функции базы данных – ввод, поиск, сортировку, фильтрацию и анализ данных.

5. Вводить пароли или устанавливать защиту некоторых(или всех) ячеек таблицы, скрывать «прятать» фрагменты таблицы или всю таблицу.

6. Наглядно представлять данные в виде диаграмм и графиков.

7. Вводить и редактировать тексты, как в текстовом процессоре, создавать рисунки с помощью графического редактора MC Office.

8. Осуществлять импорт – экспорт, обмен данными с другими программами, например, вставлять текст, рисунки, таблицы, подготовленные в других приложениях, и т.п.

9. Осуществлять многотабличные связи(например, объединять отчеты филиалов фирм).

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

Excel 2000(Excel 9.0) входит в состав интегрированного пакета программ

MC Office 2000. Как и другие приложения этого пакета, Excel 2000 отличается улучшенным интерфейсом, более совершенной системой управления и поддержки сетевых режимов работы, к которым относятся:

• Интеллектуально настраиваемая система меню;

• Наличие расширенного буфера обмена;

• Новый интерфейс и расширенные возможности диалоговых окон Открытие документа и Сохранение документа;

• Расширенные функции Помощника;

• Возможность автоматического восстановления поврежденных файлов;

• Наличие многоязыковой поддержки;

• Повышенная безопасность работы и расширенные возможности защиты от макровирусов;

• Возможность отправки документов на Web – сервер и управления файлами Web – документов, хранящихся на Web – сервере;

• Возможность создания динамических Web – страниц и настройки параметров Web – страниц;

• Расширенные возможности для коллективной работы нескольких пользователей и т.д.

Кроме этого в Excel 2000 появились следующие дополнительные возможности и удобные приёмы работы с электронными таблицами:

• Дополнительные числовые форматы дат с указанием года четырьмя цифрами;

• Европейская денежная единица евро и новые числовые форматы с символом €;

• Автоматическое расширение форматирования и формул в списках при автозаполнении списков;

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

• Расширенные возможности анализа и представления данных с помощью сводных диаграмм;

• Создание и выполнение Web – запросов для загрузки данных, доступных через Интернет, и возможность обновления импортированного текста и др.

Запуск и завершение работы Excel

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

Запустить программу Excel можно из Главного меню Windows с помощью команды ПУСК – Программы - Microsoft Excel. Так же запуск программы может осуществляться любым из стандартных способов запуска приложений, в том числе двойным щелчком мыши по значку документа, ассоциированного с Excel. При запуске Excel без указания имени файла процессор по умолчанию предлагает начать создание нового документа под условным названием Книга1.

Шаблоны документов Excel имеют стандартное расширение *.xlt. Если в каталоге автозагрузки не указано имя шаблона, то новый документ Excel (книга) создается с параметрами, заданными по умолчанию.

По умолчанию Excel 2000 сохраняет книги в формате Excel 2000, который идентичен формату файлов Excel 97, но отличается от форматов предыдущих версий. При открытии и сохранении книги Excel 2000 в Excel 97 утрачиваются некоторые свойства, характерные только для версии Excel 2000. В Excel 2000 предусмотрена возможность сохранения книги в особом двойном формате(Microsoft Excel 97 – 2000 & 5.0/95). При этом книга сохраняется в одном файле, который включает и формат

Excel 2000/97, и Excel 5.0/95. Далее можно продолжать работать с этой книгой в

Excel 2000 без потери каких бы то ни было особенностей, специфических для этой версии.

Завершение работы Excel,как и любого другого приложения Windows,- это закрытие его окна.

Окно программы

Общий вид окна программы Excel 2000 представлен на рисунке. Окно Excel содержит все стандартные элементы, присущие окну приложения Windows (рис.1).

рис.1

Строка заголовка содержит название приложения, имя открытого документа, кнопку системного меню, стандартные кнопки управления окном.

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

Строка меню окна Excel отличается от строки меню Word появлением лишь одного пункта Данные вместо пункта Таблица. Команда ДАННЫЕ предназначена для манипуляций с содержимым ячеек таблицы (например, сортировки, фильтрации, консолидации данных и т.п.).

Панели инструментов окна Excel 2000 Стандартная и Форматирование выводятся по умолчанию в одной строке в сокращенном виде и содержат наиболее важные кнопки аналогичных панелей в программе Word. В Excel 2000 с помощью кнопки Отменить можно отменить 16 последних операций.

Рис.2

Ниже панели инструментов в окне Excel располагается Строка формул,(рис. 2)

которая служит для ввода и редактирования данных в ячейках и содержит:

1. Поле имени – раскрывающийся список, в котором высвечивается адрес или имя активной ячейки или блока ячеек таблицы.

2. Строковое поле для ввода и редактирования данных, предназначенное для просмотра и редактирования текстовых данных. Особенностью строки (поля)

ввода и редактирования является то, что она:

• полностью отображает содержимое активной ячейки, которое иногда не видно в самой ячейке;

• дает возможность видеть содержащуюся в текущей ячейке формулу или функцию, а не её результат, отображенный в самой ячейке;

1. Между этими полями во время ввода данных появляются три кнопки для управления процессом ввода:

• Изменить формулу – [=],

• Заверение ввода – [v],

• Отмена ввода - [x]

Рабочее поле (лист) (рис. 3) окна программы Excel состоит из ячеек, названий столбцов и строк. В верхней части рабочего листа находится строка заголовков столбцов таблицы.

рис. 3

Слева расположены номера строк таблицы. На их пересечении находится «пустая» серая кнопка, которая служит для выделения всего рабочего листа.

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

В самом низу окна программы Excel находится строка состояния табличного процессора(строка подсказки). Она предназначена для выдачи сообщений пользователю относительно его возможных действий в данный момент. Строка состояния может содержать индикатор состояния табличного процессора, отражающий режим его работы (Готово, Ввод, Правка, Запись макроса), расшифровку текущей команды меню, подсказку о назначении используемой кнопки. Кроме того, на строке состояния находится поле для автовычислений, которое используется для быстрого подсчета и просмотра промежуточных результатов. Excel является многократной программой, т.е. позволяет одновременно открыть несколько документов. Перемещаться между окнами открытых документов можно щелчком мыши по знаку документа на Панели задач или через меню Окно. Упорядочить расположение открытых окон документов Excel можно с помощью команды ОКНО – Расположить. Окно документа Excel можно разделить на два или четыре подокна и одновременно работать с разными частями одной и той же таблицы. Разделить окна можно с помощью команды ОКНО – Разделить, либо с помощью разделителей окна – черных прямоугольников на полосах прокрутки. Закрыть окно можно командой меню ОКНО – Снять Разделение

Структура электронной таблицы

Документ в программе Excel принято называть рабочей книгой(Книга 1, 2 и т.д.). Эта книга состоит из рабочих листов, как правило, электронных таблиц.

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

Электронная таблица Excel состоит из 65 536 строк и 256 столбцов. Строки нумеруются числами(то 1 до 65 536), а столбцы обычно обозначаются буквами латинского алфавита А, В, С,..., Z. После столбца Z следуют столбцы AA, AB, AC, BA,BB...IV.

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

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

Ячейка – область электронной таблицы, находящаяся на пересечении столбца и строки, это наименьшая структурная единица на рабочем листе.

Формат и размер ячеек – ширину столбцов и высоту строк можно изменить команд меню, а так же вручную – с помощью мыши или клавиш.

Текущая (активная ячейка) – ячейка, в которой в данный момент находится курсор. Она выделяется на экране жирной черной рамкой. Для выделения любой ячейки достаточно щелкнуть по ней мышью. В активную ячейку можно вводить данные, производить над ней различные операции. Каждая ячейка имеет свой адрес, который используется для указания на ячейку – при ссылке на неё, например, А1.

Ссылка – способ(формат) указания адреса(имени) ячейки. Вводится в строке формул электронной таблицы.

Адреса ячеек могут быть относительными или абсолютными. Ячейки могут иметь собственные имена.

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

Типичными установками, принимаемыми по умолчанию на уровне всех ячеек таблицы, являются:

• ширина ячейки в около 8 разрядов, высота – около 12 пунктов;

• левое выравнивание для символьных данных;

• основной формат для цифровых данных с выравниванием вправо.

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

Блок используемых ячеек может быть указан или выделен двумя способами:

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

• выделением блока при помощи мыши или клавиш управления курсором.

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

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

На одном рабочем листе рекомендуется размещать не более одной таблицы. После помещения данных в ячейки таблицы Excel эти данные необходимо зафиксировать:

• нажать клавишу Enter;

• щелкнуть мышью на другой ячейке;

• перейти к другой ячейке с помощью клавиш управления курсором;

• щелкнуть по кнопке [v] Конец ввода в Строке формул;

• щелкнуть мышью на квадратике в нижнем правом углу ячейки Ввод

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

• активизировать ячейку;

• нажать клавишу F2 или щелкнуть мышью Строке формул (активизировать строковое поле);

• внести в данные необходимые изменения;

• зафиксировать ввод;

Двойной щелчок по ячейке позволяет произвести редактирование непосредственно в активной ячейке.

Если ширина ячейки недостаточна для размещения в ней числовых данных, то ячейка имеет следующий вид:[########]. Для отображения содержимого такой ячейки необходимо увеличить ширину столбца.

Текст в ячейки таблицы можно вводить в виде нескольких строк.

Разделить строки в одной ячейке можно с помощью комбинации клавиш Alt+Enter или с помощью команды ФОРМАТ – Ячейки - Переносить по словам(ячейка будет заполняться в несколько строк).

Для добавления ячеек, строк, столбцов и листов используется команды меню окна ВСТАВКА – Ячейки(Строки, Столбцы, Лист) или команды контекстного меню Добавить ячейки(лист). Команда ВСТАВКА – Ячейки и Добавить ячейки вызывают диалоговое окно Добавление ячеек, в котором нужно указать направление сдвига уже заполненных ячеек. Добавить пустые ячейки можно слева или сверху от выделенной ячейки или строки. Чтобы вставить новые пустые ячейки с помощью контекстного меню надо:

• выделить ячейку, перед которой будет выполняться вставка;

• щелкнуть правой кнопкой мыши по выделенной ячейке;

• в раскрывшемся контекстном меню выбрать команду Добавить ячейки;

• указать направление сдвига выделенной ячейки таблицы – вниз или вправо;

Операции удаления и очистки. Для удаления выделены ячеек, столбцов и строк в Excel используются команды меню ПРАВКА – Удалить или команды контекстного меню Удалить. Для удаления выделенных листов используется команда меню ПРАВКА – Удалить лист или команда контекстного меню листа Удалить.

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

Операция перемещения, копирования, вставки

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

Для перемещения и копирования рабочих листов через буфер обмена используют контекстное меню или команду меню окна Excel ПРАВКА – Переместить/Скопировать лист. Переместить лист внутри рабочей книги можно путем перемещения (перетаскивания) его ярлычка.

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

Итак, начнем.

Откройте новую рабочую книгу Книга1 Excel:

1. Выделите ячейку В1 (Далее мы не будем подробно описывать уже рассмотренные действия; если у вас возникнут затруднения на каком-то этапе, просто загляните в предыдущее занятие).

2. Наберите Продажи и нажмите клавишу Enter.

3. Выделите ячейку А5, наберите Менеджер и нажмите клавишу Enter.

4. В ячейку А6 введите Дата.

Теперь рабочий лист следует сохранить перед дальнейшими изменениями.

Приступим к редактированию данных:

5. Дважды щелкните в ячейке В1 единственного рабочего листа Лист1 рабочей книги Книга2.

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

7. В слове «Продажи» удалите прописную букву «П» и введите строчную букву «п».

8. Нажмите клавишу End и клавишей Backspsce удалите последнюю букву «и»

Средства автоматизации ввода и редактирования данных. Заполнение ячеек

В последних версиях Excel имеются разнообразные средства для автоматизированного ввода данных в ячейки электронной таблицы:

• автозавершение;

• автозамена;

• автозаполнение.

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

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

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

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

• команда меню ПРАВКА – Заполнить (вверх, вниз, вправо, влево, по листам);

• маркер автозаполнения;

При вводе команды меню ПРАВКА – Заполнить – Прогрессия (рис. 4) в диалоговом окне

Прогрессия можно задать точные параметры заполнения ячеек:

рис.4

• Расположение – по строкам или столбцам;

• Шаг – значение, на которое будет увеличиваться или уменьшаться элемент ряда.

• Тип прогрессии (арифметическая, геометрическая, даты, автозаполнения);

• Предельное значение – значение, на котором ряд должен быть закончен.

• Автоматическое определение шага. Если установлен этот флажок, то значение окна Шаг игнорируется.

Использование маркера автозаполнения

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

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

• копировать и размножать содержимое ячеек;

• заполнять ячейки последовательностями числовых рядов и дат, а так же данными предварительно сформированных списков;

• копировать формулы;

• стирать данные в выделенных ячейках;

• удалять и вставлять ячейки – сдвигать ячейки в строках или столбцах.

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

• выделить ячейку или копируемый блок;

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

☺ Продолжим создание отчета. Познакомимся теперь с одним полезным инструментом Excel – Автозаполнением.

9. Введите в ячейке С8 слово Январь.

10. Если ячейка С8 не выделена, сделайте её активной и поместите указатель мыши на небольшой квадратик в правом нижнем углу рамки активной ячейки. Указатель примет форму крестика.

11. Щелкните мышью и, не отпуская кнопки, перемещайте указатель к ячейке G8. Заметьте, что при перемещении указателя рядом с ним появляется подсказка со значением, которое будет помещено в очередную ячейку.

12. На ячейке G8 отпустите левую кнопку мыши.

В результате в диапазоне С8:G8 появилась последовательность месяцев с января по май…

13. В ячейках А10 и А11 введите, соответственно, цифры 0 и 4.

14. Выделите диапазон А10:А11.

15. Щелкните в правом нижнем углу рамки выделения и не отпуская, тащите изменившийся на знак «+» указатель мыши к ячейке А18. Таким образом, в диапазоне А10:А18 будет введена последовательность чисел, кратных четырем.

16. Удалите эту последовательность, нажав клавишу Delete (если выделение диапазона не снято).

Автозамена.

17. Выполните команду Сервис – Автозамена.

18. Проверьте установлен ли флажок Заменять при вводе,если нет, то установите. Щелкните на поле Заменять и введите в нем фа.

19. Щелкните на поле На и введите в нем Фирма А&К group.

20. Нажмите кнопку Добавить. То, что вы ввели, появиться в списке автозамен.

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

21. Нажмите кнопку ОК. Окно диалога будет закрыто.

22. Выделите ячейку В3, напечатайте фа и нажмите клавишу Enter. То, что вы ввели в ячейке, автоматически заменилось на «Фирма А&К group».

Заполните таблицу. Начиная с ячейки А9 и далее в столбик введите данные:

• Статьи дохода

• Компьютеры

• Комплектующие

• Периферия

• Доход всего

• Статьи расхода

• Реклама

• Аренда

• Налоги

• Расход всего

• Прибыль

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

Рис 5

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

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

В Excel диаграммы можно создавать различными способами:

• с помощью команды меню ВСТАВКА – Диаграмма;

• с помощью кнопки панели инструментов окна Excel Мастер диаграмм;

• с помощью команды меню ВСИАВКА – Объект – Новый – Диаграмма Microsoft Graph 2000;

• щелчком по кнопке Создать диаграмму текущего типа;

• с помощью клавиши F11.

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

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

Для запуска мастера диаграмм нужно ввести команду меню ВСТАВКА – Диаграмма или щелкнуть по его кнопке на стандартной панели инструментов.

Первое диалоговое окно Мастера – Шаг 1 из 4 (рис. 6) – Тип диаграммы имеет две вкладки – Стандартные и Нестандартные. На этом шаге нужно выбрать подходящий вариант строящейся диаграммы из нескольких десятков имеющихся образцов.

рис 6

Для перехода к каждому последующему шагу Мастера диаграмм нужно использовать кнопку Далее>.

Во втором окне Мастера диаграмм (рис.7) Источник данных диаграммы на вкладке Диапазон данных отображаются адрес выделенного диапазона исходных данных и образец строящейся диаграммы.

рис. 7

Третье окно Мастера диаграмм (рис.8) содержит следующие вкладки:

• Заголовок – для ввода текста заголовка диаграммы и подписей осей;

• Оси – определения отображения и маркировки осей координат;

• Линии сетки – для выбора типа линий и характера отображения сетки;

• Легенда – для отображения или скрытия легенды и определения ее места на диаграмме. Легенда – небольшое подокно на диаграмме, в котором отображаются названия рядов данных и образцы их раскраски на диаграмме (в виде ключа легенды);

• Подписи данных – для управления отображением надписей, соответствующих отдельным элементам данных на диаграмме;

• Таблица – для добавления к диаграмме (или скрытия) таблицы данных, использованной для построения диаграммы.

рис. 8

Последнее (четвертое) Мастера диаграмм (рис. 9) служит для определения места ее в рабочей книге.

рис. 9

рис. 10 итоговая диаграмма

В Excel 2000 имеются удобные способы, которые позволяют мгновенно создавать диаграммы без использования Мастера диаграмм – за один шаг. В этих случаях по умолчанию создается диаграмма стандартного типа (гистограмма) (рис. 11).

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

Рис. 11

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

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

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

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

☺ Попробуем построить диаграмму работы нашей виртуальной фирмы за месяцы январь и февраль:

23. Выделите диапазон А8:D19;

24. На панели инструментов нажмите значок Мастера диаграмм. В появившемся окне выберите тип диаграммы (допустим гистограмма) и нажмите кнопку Далее;

25. Второй шаг Мастера диаграмм. Выберите закладку Диапазон данных и установите переключатель Ряды – в строках;

26. На закладке Ряд удалите строки не содержащие данных (статьи дохода, доход всего, статьи расхода, расход всего прибыль). Нажмите кнопку Далее;

27. Третий шаг. Перейдите на закладку Легенда и установите её расположение «внизу» и нажмите Далее;

28. Четвертый шаг. Поместите диаграмму на имеющемся листе.

Наша диаграмма готова (см. рис. 12). Самостоятельно сделайте диаграмму за все месяцы работы фирмы.

рис. 12

Типы данных в ячейках электронной таблицы

Ячейки рабочего листа электронной таблицы могут содержать:

• исходные, или первичные данные, имеющие, как правило, постоянные значения;

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

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

• текст, например: МП-15, с.120, В10+В11;

• число;

• формула, например: =А1*А2, =В10-В11;

• дата и время.

Тип данных подразумевает:

1. допустимое множество значений, которое может принимать константа или переменная этого типа;

допустимое множество операций над этими данными.

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

Текст. Для манипуляции с текстовыми данными в Excel существуют специальные текстовые функции. Если текстовые данные используются в качестве аргумента функции, их обязательно нужно заключать в кавычки (“Текст”). В противном случае Excel рассматривает текст как имя и выдает сообщение об ошибке: #ИМЯ? (рис.13)

рис. 13

Числа. Числовые данные могут быть представлены:

• целыми числами;

• в виде десятичных дробей;

• в виде обыкновенных (рациональных) дробей;

• в научном (экспоненциальном) формате – в виде мантиссы порядка для отображения очень больших и очень малых чисел, например 2,35Е+08 для числа 235000000 или 2,35Е-06 для числа 0,0000235;

• в форме процента – значения ячеек умножаются на 100 и вводятся с символом %, например 0,2%

• в денежной форме (с обозначением символа валюты или без него). Для выравнивания денежных обозначений по десятичному разделителю используется финансовый формат.

Форматы отображения числовых данных задаются на вкладке Число в диалоговом окне Формат ячеек, которое выводится на экран командой ФОРМАТ – Ячейки (рис. 14)

Числовые данные в таблицах Excel могут включать только следующие символы:

• цифры;

• круглые скобки;

• знаки +, -, стоящие перед числом;

• косую черту в рациональных дробях;

• знак процента;

• знак мантиссы (Е-, Е+, е-, е+);

• денежные обозначения (р.,€, $ или др.);

рис. 14

Формулы. Формулами в электронной таблице называют арифметические и логические выражения. Формулы в Excel всегда начинаются со знак равенства (=), а после вводятся необходимые элементы, либо непосредственно, либо в виде ссылок на соответствующие ячейки. Например, ввод формулы =Н1+Н2 означает, что нужно сложить содержимое ячеек Н1 и Н2 и вывести результат в активной ячейке (рис. 15)

рис.15

Формулы могут включать:

• константы;

• ссылки на ячейки;

• операторы – знаки арифметических, логических и других операций;

• встроенные функции;

• скобки, закладки и др.

Для ввода в ячейку формулы необходимо:

• активизировать ячейку;

• ввести в клавиатуры знак равно = или в строке формул щелкнуть по кнопке [=] Изменить формулу;

• ввести (без пробелов!) нужные значения или ссылки, а также необходимые операторы;

• зафиксировать ввод.

Адреса ячеек вводят в формулы по латинскому регистру, а знаки арифметических операций – с цифровой клавиатуры.

Для выполнения вычислений с помощью формул и функций в Excel имеются четыре вида операторов, список которых приведен в таблице 1:

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

Оператор	Назначение	Пример
Арифметические операторы
+	Сложение	=А1+С2
_	Вычитание	=300-А2
*	Умножение	=В4*С1
/	Деление	=D3/G7
%	Процент	=10%
^	Возведение в степень	=C3^2
Операторы сравнения(отношения)
=	Равно	=F5=G8
<	Меньше	=A3<F4
>	Больше	=F4>G1
<=	Меньше или равно	=D2<=D1
>=	Больше или равно	=R1>=F1
<>	Не равно	=A1<>A2
Операторы ссылок
:	Диапазон (используется для формирования ссылок на диапазон ячеек)	=СУММ(В5:В10)
;	Объединение (объединяет несколько ссылок в одну ссылку)	=СУММ(В5;С6;А1)
Текстовые операторы
&	Амперсанд (используется для объединения содержимого ячеек в одну текстовую строку)	=А1&B5

Таб.1

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

• сначала производится возведение в степень;

• затем – умножение и деление (они имеют одинаковый приоритет);

• в последнюю очередь – сложение и вычитание(также имеют одинаковый приоритет);

• нормальный порядок выполнения операций изменяют введением скобок. Операции в скобках выполняются первыми.

Для ускорения и облегчения ввода данных при выполнении наиболее часто выполняемой операции суммирования данных на стандартной панели Excel существует кнопка [Σ] Автосумма. С помощью этой кнопки практически мгновенно можно вычислить сумму всех чисел, расположенных выше или левее выделенной ячейки. Если сумма должна быть вычислена в другом месте листа, используют функцию СУММ и в качестве аргументов указывают диапазон ячеек, содержащих суммируемые данные, например, =СУММ(В7:С12;Е7:Е12).

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

29. Выделите ячейку Н10. В нее поместим суммарный доход фирмы от продажи компьютеров за пять месяцев.

30. На панели Стандартная нажмите кнопку Автосумма. Диапазон С10:G10 рабочего листа становится окруженным «бегущей» границей, обозначающий диапазон, в котором будет произведено суммирование, а в активной ячейке появляется формула =SUM(C10:G10) или (СУММ(С10:G10)). Теперь достаточно убедиться в том, что диапазон выбран правильно.

1. Нажмите клавишу Enter или нажмите кнопку Автосумма – сравните оба варианта! В ячейку Н10 помещен результат вычислений.

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

3. Щелкните на ячейке Н12 и обратите внимание на то, что программа уже автоматически предлагает просуммировать столбец, а не строку.

4. Чтобы все же просуммировать нужную строку, поместите указатель мыши на ячейку С12, нажмите левую кнопку мыши и, не отпуская её, переместите указатель на ячейку G12. Т.о. мы указали диапазон суммирования.

5. Нажмите клавишу Enter.

Просуммируем теперь доход фирмы по всем статьям за каждый месяц.

36. Щелкните на ячейке С13 и просуммируйте диапазон С10:С12.

37. Для суммирования остальных столбцов используйте Автозаполнение. Выделите ячейку С13, а затем выполните автозаполнение ячеек С13:Н13. Как видите автозаполнение работает и в случае формул. Теперь у нас подсчитаны сумма дохода фирмы по каждому месяцу и по всем месяцам вместе.

Теперь попробуем просуммировать одновременно строки и столбцы.

38. Выделите диапазон С15:Н18. Это диапазон, который содержит блок суммируемых данных, а также строку и столбец, в которые будут помещаться итоговые суммы.

39. Нажмите кнопку Автосумма. Результат суммирования теперь появился в строке 18 и столбце Н.

Попытаемся сами составить простейшие формулы.

40. Выделите ячейку С19.

41. Введите знак «=», а затем щелкните на ячейке С13. Её адрес С13 записывается в формулу (в ячейке С13 и в строке формул), а вокруг ячейки С13 появляется «бегущая» рамка.

42. Введите знак «-», а затем щелкните на ячейке С18.

43. Нажмите клавишу Enter. Формула введена и в ячейке С19 появилась величина прибыли за январь.

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

Функции представляют собой программы с уникальными именами. Это заранее определенные формулы, для которых пользователь должен задать конкретные значения. Все функции имеют одинаковый формат записи и включают имя функции перечень аргументов. Если написание формулы начинается с функции, перед именем функции вводится знак равенства (=). Аргументы записываются в круглых скобках после имени функции, причем, скобки – обязательная принадлежность функции, даже если у нее нет аргументов(например, функция =ПИ() – 3,1416…).

В качестве аргументов функций могут использоваться:

• числа(константы);

• адреса ячеек или диапазонов;

• имена ячеек или диапазонов;

• текст;

• формулы;

• другие функции;

• логические значения и др.;

В русифицированных версиях Excel имена многих функций записываются на русском языке, например:

=СУММ(А1;В2;С3:С6).

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

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

Мастер функций в Excel имеет два диалоговых окна – два шага.

рис. 16

панель функции ЕСЛИ

Для вызова мастера функций можно использовать:

• команду горизонтального меню Excel ВСТАВКА – Функция;

• кнопку [fx] Вставка функции на панели инструментов;

• комбинацию клавиш Shift+F3.

В диалоговом окне Мастер функций (шаг 1) имеются два подокна:

Категория и Функция. При выборе определенной функции в нижней части диалогового окна появляется краткое ее описание. Если строка формул неактивна, то мастер функций активирует ее, вставит знак равенства (=) и функцию, а затем автоматически введет функцию в выделенную ячейку.

При выборе функции появляется второе окно Мастера функций, в котором отображается имя функции, все ее аргументы, описание функции и состав каждого аргумента.

Excel содержит более 300 встроенных функций, условно разделенных на несколько категорий:

• математические;

• статистические;

• финансовые;

• логические;

• инженерные;

• информационные;

• функции даты и времени;

• функции управления базами данных.

Примеры встроенных функций

Категории функций	Имена функций
Математические и тригонометрические	СУММ, СТЕПЕНЬ, КОРЕНЬ, РАДИАНЫ, ПИ, ГРАДУСЫ, ABS, LN, LOG, EXP, SIN, COS
Статистические	СРЗНАЧ, МАКС, МИН, ВЕРОЯТНОСТЬ, СЧЕТ, СЧЕТ3(3-значений), СРОТКЛ
Текстовые	НАЙТИ, ЗАМЕНИТЬ, ПОИСК, СЦЕПИТЬ, ПОДСТАВИТЬ, СОВПАД, ПОВТОР
Дата и время	ГОД, МЕСЯЦ, ЧАС, МИНУТЫ, СЕКУНДЫ, ДЕНЬ, ДЕНЬНЕД, СЕГОДНЯ
Логические	ЕСЛИ, И, ИЛИ, НЕ, ИСТИНА, ЛОЖЬ

☺ Переходим к освоению мастера функций.

44. Выделите ячейку В6.

45. Наберите =today().

46. Нажмите клавишу Enter.

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

Вставка функции на панели инструментов Стандартная или выполнив команду Вставка – Функция.

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

47. Выделите диапазон С10:С12. В поле имени появился адрес первой ячейки диапазона.

48. Щелкните на Поле имени и введите Доход., Январь.

49. Нажмите клавишу Enter и теперь этот диапазон называется Доход. Январь.

50. Повторите тоже самое для диапазонов, содержащих величины дохода по оставшимся месяцам. Назовите их соответственно Доход. Февраль, Доход. Март, Доход. Апрель и Доход. Май.

51. Выделите диапазон А8:G18. В этом диапазоне содержится вся таблица без столбца Всего.

52. Выполните команду Вставка – Имя – Создать. Появится окно диалога Создать имена.

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

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

55. К сожалению программа не очень удачно присвоила некоторые имена пустым диапазонам Статьи Дохода и Статьи Расхода. Изменим диапазон этих имен. Выполните команду Вставка – Имя – Присвоить. Откроется диалоговое окно Присвоение имени.

56. В списке поля Имя выделите пункт Статьи дохода.

57. Измените диапазон в поле Формула, обозначенный выбранным именем. Пока сверните диалоговое окно и выделите с помощью мыши диапазон С10:G12. Ссылка на этот диапазон заменит старую (выделенную) ссылку. Разверните окно.

58. Нажмите кнопку ОК, если процесс присвоения завершен, или кнопку добавить.

59. Измените диапазон для имени Статьи Расхода таким же образом (шаги 49-42), назначив для этого имени диапазон С15:G17, и по завершении присвоений нажмите кнопку ОК.

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

Адреса ячеек

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

В1, С1, А1 или 11, если столбцы и строки нумеруются числами.

Тип ссылок задается пользователем при настройке параметров работы с помощью команды меню СЕРВИС – Параметры на вкладке Основные переключателем Стиль ссылок - R1C1 или A1 – по умолчанию. При установленном переключателе R1C1 строки и столбцы нумеруются цифрами. Гораздо удобнее вводить адреса ячеек щелчком мыши по этой ячейке. Обозначение ячейки, составленное из номера столбца и номера строки, называется относительным адресом или просто ссылкой или адресом.

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

A1:C12;

A7:E7- весь диапазон ячеек, находящийся в одной строке;

C3:C9 – весь диапазон ячеек, находящийся в одном столбце.

Чтобы ввести ссылку на всю строку или столбец, нужно набрать номер строки или букву столбца дважды и разделить их двоеточием, например: A:A, 2:2 или A:B, 2:4.

Для обозначения адреса ячейки с указанием листа используется имя листа и восклицательный знак: Лист2!B5, Итоги!В5.

Для обозначения адреса ячейки с указанием книги используются квадратные скобки, например: [Книга1] Лист2!А1.

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

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

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

$C$6 – абсолютный адрес ячейки С6, т. е. при копировании формулы не будет меняться номер строки и номер столбца. Абсолютный адрес диапазона ячеек обозначается: Абсолютный адрес диапазона ячеек обозначается: $B$3:$C$8. также используется смешанный адрес:

$C5 –при копировании не будет изменяться номер столбца

C$5 – не будет изменяться номер строки.

Также абсолютным адресам относится присвоение имени ячейке:

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

• должно начинаться с буквы русского/латинского алфавита или символа подчеркивания(_), остальные символы могут быть: буквами, цифрами, символами(_).

Пример собственных имен ячеек: Итоги_года, Системный_баланс и т.д.

Собственные (пользовательские) имена могут содержать листы книги Excel:

• имя не должно быть заключено в квадратные скобки [];

• длина имени не превышает 31 символа;

• имя листа не может содержать двоеточие(:), слэш (/) и обратную косую черту(\), знак вопроса (?), звездочку (*).

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

60. Введите в ячейку J6 текст Евро =.

61. Введите в ячейку К6 значение 0,0468 – стоимость одного рубля в пересчете на евро.

62. Выделите ячейку К10.

63. Подготовьтесь к вводу формулы, введите =.

64. Укажите ячейку J10,а затем введите *1000*.

65. Укажите на ячейку К6.

66. Нажмите клавишу F4. Это приведет к замене относительной ссылки К6 на абсолютную $K$6.

67. Скопируйте с помощью автозаполнения эту формулу в ячейки К11:К13. Обратите внимание, что формула в ячейке К11 выглядит как = J11*1000*$K$6, абсолютный адрес ячейки К6 остался прежним. Для тренировки самостоятельно заполните оставшиеся ячейки в строке Прибыль.

Форматирование данных и защита информации в Excel

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

Для изменения первоначального форматирования ячеек можно использовать:

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

• команду меню окна ФОРМАТ – Ячейки;

• команду контекстного меню Формат ячеек.

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

• число;

• выравнивание;

• шрифт;

• граница;

• вид;

• защита.

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

На вкладке Шрифт задаются параметры символов для выделенного текста.

На вкладке Выравнивание задаются параметры расположения текста в ячейке:

• по горизонтали (по значению, по центру выделения и т.д.);

• по вертикали;

• ориентация (поворот текста, расположение текста в ячейке по вертикали).

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

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

Автоформатирование. Excel 2000 предлагает около 20 стандартных образцов оформления таблицы с использованием различных шрифтов, способов обрамления и заливок. Для автоформатирования необходимо:

• выделить нужный диапазон ячеек или щелкнуть по любой ячейке форматируемой таблицы;

• ввести команду ФОРМАТ – Автоформат;

• в диалоговом окне автоформат выбрать подходящий вариант оформления.

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

Для применения условного форматирования нужно:

1. Выделить необходимые ячейки.

2. Ввести команду ФОРМАТ - Условное форматирование.

3. В диалоговом окне Условное форматирование выполнить одно из следующих действий:

   • Выбрать параметр Значение и ввести нужное значение или формулу, поставив перед ней знак равенства (=);

   • Выбрать параметр Формула и в поле справа ввести нужную формулу.

4. Щелкнуть по кнопке Формат и выбрать тип шрифта, его цвет, подчеркивание, рамку, а так же цвет заливки и узор.

В тех случаях, когда значения ячеек будут отвечать поставленным условиям, а формулы принимать значение ИСТИНА, к ячейке будут применяться параметры оформления, заданные при назначении условного форматирования.

Защита информации таблицах Excel

B Excel 2000 имеются разнообразные способы защиты информации, которые позволяют:

1. ограничить доступ к документу в целом;

2. ограничить возможность внесения изменений в документ;

3. ограничить доступ к отдельным фрагментам документа – рабочим листам или ячейкам;

4. скрыть отдельные фрагменты электронной таблицы;

5. скрыть файл документа – не отображать его название в окне папки и Проводника;

6. предотвратить заражение документа макровирусами.

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

• СЕРВИС – Защита – Защитить книгу;

• СЕРВИС – Защита – Защитить книгу и дать общий доступ;

• СЕРВИС- Доступ к книге;

• ОКНО – Скрыть.

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

1. ввести пароль на открытие файла;

2. ввести пароль для разрешения на изменение данных

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

Для защиты файлов рабочих книг при сохранении документа нужно ввести команду ФАЙЛ – Сохранить как, в диалоговом окне Сохранение документа щелкнуть по кнопке СЕРВИС и выбрать команду Общие параметры, а затем в диалоговом окне Параметры сохранения вести пароли установить нужные переключатели.

Если в диалоговом окне Защита книги установлен флажок Структуру, то запрещается:

• просмотр скрытых листов;

• перемещение, удаление, скрытие или переименование листов;

• вставка новых листов или листов с диаграммами; допускается добавление внедренных диаграмм с помощью Мастера диаграмм;

• перемещение или копирование листов в другую книгу;

• создание для сценариев краткого отчета с помощью Диспетчера сценариев;

• использование инструментов надстройки ” Пакет анализа” для помещения результатов на новый лист;

• запись новых макросов и др.;

Если установлен флажок Окна, то запрещается:

• изменение размеров и положения окон открытой книги;

• перемещение, изменение размеров и закрытие окон и др.

Снять защиту книги и ограничения доступа можно с помощью команды СЕРВИС – Защита – Снять защиту книги. Если был установлен пароль, то чтобы снять защиту, необходимо знать пароль.

Команда СЕРВИС – Защита – Защитить книгу и дать общий доступ используется для защиты элементов рабочей книги при работе с ней нескольких пользователей.

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

Защита рабочих листов. Защиту отдельных рабочих листов можно установить с помощью команды СЕРВИС – Защита – Защитить лист (рис. 17). При этом файл рабочей книги можно свободно открывать, а в незащищенные листы вносить изменения.

рис.17

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

• содержимого ячеек;

• графических объектов – для запрета их перемещения и редактирования;

• сценариев.

Если в диалоговом окне Защитить лист установлен флажок Содержимое, то запрещаются:

• изменение защищаемых ячеек;

• просмотр скрытых перед защитой листа строк и столбцов;

• просмотр скрытых перед защитой листа строк и столбцов;

• просмотр скрытых перед защитой листа формул;

• изменение элементов на листах диаграмм.

Если установлен флажок Сценарии, то запрещаются:

• просмотр скрытых сценариев;

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

Для того чтобы скрыть рабочий лист или его отдельные части (строки или столбцы), используют команды ФОРМАТ – Лист (Строка, Столбец) – Скрыть. Для их отображения – команды ФОРМАТ – Лист (Строка, Столбец) – Отобразить.

Защита ячеек. Для защиты отдельных ячеек используется команда меню ФОРМАТ – Ячейки. (рис.18)

рис. 18

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

Средства автоматического обобщения и анализа данных электронной таблицы

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

1. Автоматические вычисления;

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

3. Средства автоматического подведения общих и промежуточных итогов;

4. Возможность создания и использования структуры таблицы;

5. Средства для обобщения данных;

6. Возможность создания и модификации сводных таблиц, отчетов и диаграмм;

7. Средства условного анализа (анализа “что, если”), к которым относятся:

   • Подбор параметра;

   • Надстройка Поиск решения;

   • Сценарии;

   • Таблицы подстановки.

8. Различные надстройки, мастера, шаблоны, например Мастер суммирования, подстановок, шаблонов, Мастер обновления связей, надстройка Пакет анализа и др.

Средства Excel для работы с данными списка. Электронная таблица, все строки которой содержат однородную информацию, рассматривается как список или база данных. При этом:

• каждая строка списка рассматривается как запись базы данных;

• столбцы списков считаются полями базы данных;

• заголовки столбцов считаются именами полей базы данных;

• все строки таблицы должны содержать однородную информацию – во всех строках в одинаковых столбцах должны находиться однотипные данные;

• заголовки столбцов должны находиться в первом столбце списка;

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

Сортировка данных. Сортировка осуществляется непосредствен в электронной таблице. Строки, столбцы и ячейки в процессе сортировки переупорядочиваются в соответствии с заданным пользователем порядком сортировки. Списки можно сортировать в возрастающем порядке (от 1 до 9, от А до Я) или в убывающем порядке (от 9 до 1, от Я до А). Сортировать можно как текстовые, так и числовые данные. Если в сортируемом столбце содержатся и текстовые и числовые данные, то после сортировки числовые значения будут расположены перед текстом. Сортировка данных в электронных таблицах производятся с помощью команд меню ДАННЫЕ – Сортировка или с помощью кнопок [А^Я↑], [Я^А↓] на Стандартной панели инструментов.

В диалоговом окне Сортировка диапазона указывают требуемые параметры – тип и порядок сортировки (рис.19)

рис. 19

☺ Перейдем к работе со списками. Полями нашей базы данных будут:

• Категория – вид продаваемой продукции;

• Товар – название товара;

• Модель – Модель товара;

• Цена (нал) – стоимость товара при наличном расчете;

• Цена (б\н)стоимость товара при безналичном расчете.

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

Автозавершение.

1. На листе Список товаров выделите диапазон С9:G9.

2. Последовательно в каждую ячейку этого диапазона вводите данные:

Компьютер, Персональный, Gamer’s Dream, 1200, =F9+F9*$H$5.

3. Выделите диапазон С10:F10.

4. Введите K. Excel предложит закончить слово «Компьютер». Позвольте это программе, нажав клавишу Enter.

5. Введите П. Excel предложит закончить ввод словом «Персональный». Нажмите клавишу Enter.

6. В оставшиеся ячейки введите Master Class, 1450.

7. Выделите диапазон С11:F11.

8. Последовательно в каждую ячейку этого диапазона вводите данные: Периферия, Принтер, Epson LQ, 123

9. Выделите диапазон С12:F12.

10. Введите П. Excel предложит закончить ввод словом «Периферия». Нажмите клавишу Enter.

11. Аналогично введите «Принтер».

12. Закончите ввод записи, заполнив оставшиеся ячейки значениями Lexmark 5700, 279.

Попробуем, что же такое выбор из списка.

13. Выделим диапазон С13:F13.

14. Нажмите сочетание клавиш Alt + Стрелка вниз. В раскрывшемся списке выделите пункт Периферия и нажмите клавишу Enter. Нажмите еще раз клавишу Enter, чтобы перейти к нижней ячейке.

15. Щелкните правой кнопкой мыши на ячейке D13. В появившемся окне меню выберите команду Выбрать из списка и в появившемся списке выберите пункт Принтер. Нажмите клавишу Enter, чтобы перейти к следующей ячейке.

16. Нажмите комбинацию клавиш Alt + Стрелка вниз. В раскрывшемся списке щелкните на пункте Epson LQ100.

17. Щелкните в строке формул и замените LQ100 на LX300. Нажмите Enter и перейдите к следующей ячейке.

18. Введите 137.

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

19. Введите еще 10 записей, используя возможности Автозавершения и выбора из списка:

Компьютер, Сервер, WorkMem, 2556;

Компьютер, Сервер, BigBlue, 6521;

Периферия, Факс-Модем, USR 33600 int, 58;

Периферия, Факс-Модем, USR 33600 ext, 68;

Периферия, Клавиатура, Genius, 9;

Периферия, Клавиатура, ВТС 5739, 25;

Периферия, Сканер, Acer 610 PT, 195;

Комплектующие, Винчестер, 3.2Гб IDE Seagate, 128;

Комплектующие, Винчестер, 6.4Гб IDE Quantum FB_EX, 161;

Комплектующие, Винчестер, 9.1Гб UW SCSI_IBM, 499.

Cкопируйте формулу из ячейки G9,заполнив поле Цена (б\н) для всех записей. Теперь рабочий лист готов (см. рис. 20).

рис.20

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

Фильтрация данных с помощью автофильтра:

1. установить курсор внутри таблицы;

2. ввести команду меню ДАННЫЕ – Фильтр – Автофильтр;

3. щелчком мыши по кнопке со стрелкой [▼] раскрыть список столбца, по которому будет производится выборка;

4. указать требуемые значения или выбрать строку ”условие” и задать критерии выборки в диалоговом окне Пользовательский автофильтр.

рис. 21 использование автофильтра для отбора записей по признаку «М» -(мужчина)

рис. 22 рабочий лист после фильтрации списка по признаку «мужчина»

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

• критериев сравнения;

• вычисляемых критериев.

Фильтрация данного типа выполняется с помощью команды меню ДАННЫЕ – Фильтр – Расширенный фильтр. Особенностью этого режима является, то что до выполнения самой команды фильтрации необходимо сформировать специальную область для задания условий фильтрации данных – диапазон условий отбора. После формирования диапазона условий с критериями выборки записей устанавливают курсор внутри таблицы, вводят команду ДАННЫЕ – Фильтр – Расширенный фильтр и в диалоговом окне Расширенный фильтр указывают диапазон ячеек таблицы и адрес или имя сформированного заранее диапазон условий.

Для восстановления всех строк исходной таблицы и отмены режима фильтрации следует ввести команду меню ДАННЫЕ – Фильтр – Отобразить всё.

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

В Excel можно создавать до 8 уровней структуры. Для каждого рабочего листа можно создать только одну структуру.

Структура таблицы создается автоматически после выполнения таких команд меню ДАННЫЕ, как Итоги, Консолидация, Сводная таблица.

Структурировать таблицу можно и самостоятельно с помощью команд меню ДАННЫЕ – Группа и структура.

Автоматическое структурирование таблицы выполняется с помощью команды меню ДАННЫЕ – Группа и структура – Создание структуры. Перед вводом этой команды необходимо выделить диапазон ячеек – область, для которой требуется создать структуру. После выполнения этой команды будут отображены в развернутом виде все существующие структуры.

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

В Excel имеется несколько способов консолидации данных

• консолидация данных по расположению – используется для однотипных данных, упорядоченных одинаковым образом;

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

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

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

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

Анализ и обобщение данных с помощью сводных таблиц и сводных диаграмм.

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

Так же, сводные таблицы и сводные диаграммы можно создавать:

• на основе таблиц, полученных в результате консолидации данных;

• на основе других сводных таблиц;

• на основе внешних источников данных.

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

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

К средствам условного анализа относятся:

• подбор параметра;

• поиск решения;

• сценарии;

• таблицы подстановки.

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

При выполнении этой операции следует иметь в виду, что:

• подбор параметра может выполняться только для ячейки, содержащей формулу;

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

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

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

Excel позволяет создавать таблицы подстановки различных типов:

• таблицы подстановки с одной переменной (с одной или несколькими формулами);

• таблицы подстановки с двумя переменными.

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

☺ Упражнение на сортировку и фильтрацию.

20. На листе Список товаров выделите какую – нибудь ячейку списка, например С8.

21. Выполните команду Данные- Фильтр – Автофильтр. У ячеек с названиями полей появились кнопки, раскрывающие списки значений.

22. Выберите все записи, в которых поле Категория принимает значение «Периферия». Для этого нажмите кнопку поля Категория и в появившемся списке выберите пункт Периферия.

23. В оставшейся части выберите всю информацию о принтерах. Для этого нажмите кнопку поля Товар и в появившемся списке выберите пункт Принтер.

24. Выполните более сложную фильтрацию. Выберите в списке такие принтеры, цена которых не превышает 200 у.е. Для этого нажмите кнопку поля Цена (нал) и выберите пункт Условие.

25. В появившемся окне диалога Пользовательский автофильтр в списке первого поля выберите пункт меньше или равно, а во втором поле со списком введите 200.

26. Нажмите кнопку ОК.

27. Самостоятельно выберите из списка такие компьютеры, цена которых меньше 200 у.е.

28. Включите фильтрацию, выполнив команду Данные – Фильтр – Автофильтр.

29. На листе список товаров выделите какую-нибудь ячейку списка, например С8.

30. Выполните команду Данные – Сортировка. Появится окно диалога Сортировка диапазона.

31. Отсортируем список по полю Категория. Для этого нужно выбрать пункт Категория в списке Сортировать по окна диалога Сортировка диапазона.

33. Нажмите кнопку ОК. Теперь список отсортирован по выбранному полю в алфавитном порядке.

34. Отсортируем список по двум полям одновременно так, чтобы записи полей Категория и Товар шли в алфавитном порядке, а цены на соответствующие товары шли по убыванию. Для этого вызовите еще раз окно диалога Сортировка диапазона.

35. В окне диалога в списке Сортировать по выберите пункт Категория, а в списке Затем по – пункт Товар.

36. Во втором списке В последнюю очередь по выберите пункт Цена (нал), а переключатель рядом с этим списком установите в положение По убыванию.

37. Нажмите кнопку ОК. Теперь список отсортирован.

Задачи для самостоятельной работы

Попробуйте самостоятельно решить предложенные ниже задачи путем построения электронной таблицы. Исходные данные для заполнения таблицы подберите самостоятельно (не менее 10 строк).

Задача 1

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

Задача 2

Каждому пушному зверьку в возрасте от 1-го до 2-х месяцев полагается дополнительный стакан молока в день, если его вес меньше 3 кг. Количество зверьков, возраст и вес каждого известны. Выяснить сколько литров молока в месяц необходимо для зверофермы. Один стакан молока составляет 0,2 литра.

Задача 3

Если вес пушного зверька в возрасте от 6-ти до 8-ми месяцев превышает 7 кг, то необходимо снизить дневное потребление витаминного концентрата на 125 г. Количество зверьков, возраст и вес каждого известны. Выяснить на сколько килограммов в месяц снизится потребление витаминного концентрата.

Задача 4

В доме проживают 10 жильцов. Подсчитать, сколько каждый из них должен платить за электроэнергию и определить суммарную плату для всех жильцов. Известно, что 1 кВт/ч электроэнергии стоит m рублей, а некоторые жильцы имеют 50% скидку при оплате.

Задача 5

Торговый склад производит уценку хранящейся продукции. Если продукция хранится на складе дольше 10 месяцев, то она уценивается в 2 раза, а если срок хранения превысил 6 месяцев, но не достиг 10 месяцев, то - в 1,5 раза. Получить ведомость уценки товара, которая должна включать следующую информацию: наименование товара, срок хранения, цена товара до уценки, цена товара после уценки.

Задача 6

В сельскохозяйственном кооперативе работают 10 сезонных рабочих. Собирают помидоры. Оплата труда производится по количеству собранных овощей. Дневная норма сбора составляет k килограммов. Сбор 1 кг помидоров стоит m рублей. Сбор каждого килограмма сверх нормы оплачивается в 2 раза дороже. Сколько денег в день получит каждый рабочий за собранный урожай?

Задача 7

Если количество баллов, полученных при тестировании, не превышает 12, то это соответствует оценке "2"; оценке "3" соответствует количество баллов от 12 до 15; оценке "4" - от 16 до 20; оценке "5" - свыше 20 баллов. Составить ведомость тестирования, содержащую сведения: фамилия, количество баллов, оценка.

Задача 8

Компания по снабжению электроэнергией взимает плату с клиентов по тарифу: k рублей за 1 Квт/ч и m рублей за каждый Квт/ч сверх нормы, которая составляет 50 Квт/ч. Услугами компании пользуются 10 клиентов. Подсчитать плату для каждого клиента.

Задача 9

10 спортсменов-многоборцев принимают участие в соревнованиях по 5 видам спорта. По каждому виду спорта спортсмен набирает определенное количество очков. Спортсмену присваивается звание мастера, если он набрал в сумме не менее k очков. Сколько спортсменов получило звание мастера?

Задача 10

10 учеников проходили тестирование по 5 темам какого-либо предмета. Вычислить суммарный (по всем темам) средний балл, полученный учениками. Сколько учеников имеют суммарный балл ниже среднего?

Задача 11

Билет на пригородном поезде стоит 5 монет, если расстояние до станции не больше 20 км; 13 монет, если расстояние больше 20 км, но не превышает 75 км; 20 монет, если расстояние больше 75 км. Составить таблицу, содержащую следующие сведения: пункт назначения, расстояние, стоимость билета. Выяснить сколько станций находится в радиусе 50 км от города.

Задача 12

Телефонная компания взимает плату за услуги телефонной связи по следующему тарифу: 370 мин в месяц оплачиваются как абонентская плата, которая составляет 200 монет. За каждую минуту сверх нормы необходимо платить по 2 монеты. Составить ведомость оплаты услуг телефонной связи для 10 жильцов за один месяц.

Задача 13

Покупатели магазина пользуются 10% скидками, если покупка состоит более, чем из пяти наименований товаров или стоимость покупки превышает k рублей. Составить ведомость, учитывающую скидки: покупатель, количество наименований купленных товаров, стоимость покупки, стоимость покупки с учетом скидки. Выяснить сколько покупателей сделало покупки, стоимость которых превышает k рублей.

Задача 14

Компания по снабжению электроэнергией взимает плату с клиентов по тарифу: k1 рублей за 1 кВт/ч за первые 500 кВт/ч; k2 рублей за 1 кВт/ч, если потребление свыше 500 кВт/ч, но не превышает 1000 кВт/ч; k3 рублей за 1 кВт/ч, если потребление свыше 1000 кВт/ч. Услугами компании пользуются 10 клиентов. Подсчитать плату для каждого клиента и суммарную плату. Сколько клиентов потребляет более 1000 кВт/ч.

Задача 15

При температуре воздуха зимой до -20°С потребление угля тепловой станцией составляет k1 тонн в день. При температуре воздуха от -30°С до -20°С дневное потребление увеличивается на 5 тонн, если температура воздуха ниже -30°С, то потребление увеличивается еще на 7 тонн. Составить таблицу потребления угля тепловой станцией за неделю. Сколько дней температура воздуха была ниже -30°С? (Постройте диаграмму).

Заключение

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

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

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

Список литературы

1. В. Рычков. Самоучитель «Изучите Excel 2000 самостоятельно». С.-Петербург, 1999.-457с.

2. Дж. Вейсскопф. Excel 2000 базовый курс. С.- Петербург, 2000.- 380с.

3. В.Т. Безручко. Практикум по курсу «Информатика». Работа в Windows 2000 (Word, Excel). Москва «Финансы и статистика», 2003.- 633с.

4. Б. Карпов. Microsoft Excel 2002: справочник. Питер, 2001-561с.

5. www.microsoft.com/rus/officexp/excel

6. www.compebook.ru