Методическое пособие по MS Excel

Федеральное агентство по образованию

Государственное образовательное учреждение

Высшего профессионального образования

«Башкирский государственный педагогический университет

им М.Акмуллы»

Программа дистанционного обучения по

Microsoft Excel

Методическое пособие

Уфа 2010

Федеральное агентство по образованию

Государственное образовательное учреждение

Высшего профессионального образования

«Башкирский государственный педагогический университет

им М.Акмуллы»

Тарасова И.О.

Программа дистанционного обучения по

Microsoft Excel

Методическое пособие

Уфа 2010

Тарасова И.О. Программа дистанционного обучения по Microsoft Excel. Методическое пособие. Уфа, 2010. 41 с.

Рецензент: Горбунов В.М.

Методические рекомендации содержат сведения по работе в программном продукте Microsoft Excel.

Методическое пособие предназначено школьникам и студентам ВУЗов по курсу программного обеспечения.

СОДЕРЖАНИЕ

Введение …………………………………………………………………………...6

1. Основы работы в Microsoft Excel ……………………………………………...7

1.1. Установка программы Microsoft Excel.…………………………….7

1.2. Запуск Excel…………………………………………………………..7

1.3. Завершение работы в Excel………………………………………….7

1.4. Окно программы……………………………………………………..8

1.5. Лабораторные работы……………………………………………….9

1.6. Контрольные вопросы ………………………………………………9

2. Основы работы с документами ……………………………………………...10

2.1. Рабочие книги ………………………………………………..10

2.2. Работа с листами книги …………………………………….10

2.3. Работа с несколькими книгами одновременно ………………….10

2.4. Масштаб документа…………………………………………………….11

2.5. Режимы просмотра документа…………………………………………11

2.6. Лабораторные работы ………………………………………………….12

2.7. Контрольные вопросы ………………………………………………….12

3. Ввод и редактирование данных ………………………………….13

3.1. Ввод данных в ячейки…………………………………………………..13

3.2. Типы данных в Excel……………………………………………………13

3.3. Редактирование данных в ячейках…………………………………….13

3.4. Лабораторные работы ………………………………………………….14

3.5. Контрольные вопросы …………………………………………………15

4. Форматирование таблиц …………………………………………16

4.1. Что такое форматирование ………………………………….16

4.2. Форматирование строк и столбцов …………………………..16

4.3. Форматирование ячеек ……………………………………..16

4.4. Лабораторные работы …………………………………………………17

4.5. Контрольные вопросы …………………………………………………17

5. Работа с числами. Формулы ……………………………………..18

5.1. Особенности ввода числовых данных………………………………..18

5.2. Использование формул………………………………………………..18

5.3. Лабораторные работы …………………………………………………19

5.4. Контрольные вопросы ………………………………………………...19

6. Построение диаграмм ………………………………………….20

6.1 Мастер диаграмм ………………………………………….20

6.2 Форматирование текста диаграммы ………………………….20

6.3. Ввод в диаграмму дополнительного текста …………………………..20

6.4. Вставка в диаграмму графического объекта ………………………….21

6.5. Использование диаграмм для анализа данных ……………………….21

6.6. Лабораторные работы ………………………………………………….22

6.7. Контрольные вопросы ………………………………………………….22

7. Работа с данными ……………………………………………..23

7.1. Использование списков в качестве баз данных …………………23

7.2. Отбор данных ……………………………………………..23

7.3. Поиск данных ……………………………………………………………23

7.4. Сортировка данных ……………………………………………………..24

7.5. Построение сводных таблиц ……………………………………………24

7.6. Лабораторные работы …………………………………………………..25

7.7. Контрольные вопросы …………………………………………………..25

8. Справка и решение проблем………………………………………………….26

8.1. Справочная система Microsoft Excel……………………………………26

8.2. Получение справки в режиме онлайн…………………………………..26

8.3. Поиск информации в Интернете………………………………………..27

Приложение 1. Панели инструментов Excel………………………………….28

Приложение 2. Операции в формулах………………………………………...29

Приложение 3. Назначение функциональных клавиш………………………30

Приложение 4. Сообщение об ошибках………………………………………32

Приложение 5. Описание лабораторных работ.……………………………...33

Введение

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

1. Основы работы в Microsoft Excel

1.1. Установка программы Microsoft Excel

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

Установка Microsoft Excel, как и многих других программ, начинается с запуска файла Setup. ехе. Расширение ЕХЕ указывает на то, что файл является исполняемым, то есть содержит в себе какую-нибудь программу (в данном случае программу установки). Название файла (Setup) переводится с английского языка как установка. Найти данный файл вы можете на установочном компакт-диске Microsoft Office (обычно в корневом каталоге).

1.2. Запуск Excel

Запустить Microsoft Excel можно несколькими способами. Каждый пользователь обычно выбирает наиболее удобный для него вариант. Рассмотрим основные спо- собы открытия программы:

- выполнение команды Пуск > Программы t Microsoft Office > Microsoft Office Excel;

- щелчок на значке программы на панели быстрого запуска;

- двойной щелчок на ярлыке Microsoft Excel на Рабочем столе.

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

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

- щелкнув правой кнопкой мыши на значке файла в Проводнике и выбрав в контекстном меню пункт Открыть;

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

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

- щелкнув правой кнопкой мыши на значке файла в окне папки и выбрав в контекстном меню пункт Открыть;

- выполнив команду Пуск > Документы и выбрав нужный файл.

1.3. Завершение работы в Excel

Окно программы Microsoft Excel состоит из таких же элементов, как другие знакомые вам окна операционной системы Windows. Поэтому завершать работу с программой вы можете привычными способами. Существует несколько вариантов закрытия программы:

- выполнить команду Файл > Выход;

- воспользоваться сочетанием клавиш Alt+F4;

- нажать кнопку Закрыть в правом верхнем углу окна программы;

- щелкнуть на значке Microsoft Excel в левом верхнем углу окна программы и выбрать в меню пункт Закрыть.

Если на момент закрытия в программе открыты файлы, изменения в которых не были сохранены, то перед завершением работы программа задаст вопрос, нужно ли сохранять изменения. Для подтверждения сохранения изменений нужно нажать кнопку Да, для отклонения — Нет. Чтобы вернуться к документу и продолжить работу с ним, нажмите кнопку Отмена.

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

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

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

Во-вторых, на ней отображается название текущего файла. Если документ, с которым вы работаете, пока не сохранен, то вместо имени файла будет надпись Книга1, Книга2 и т. д. Эти названия Microsoft Excel дает созданным файлам по умолчанию.

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

1.5. Лабораторные работы

Лабораторная работа №1.

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

Лабораторная работа №2.

Создание пользовательского меню в окне документа.

Лабораторная работа №3.

Отображение кнопок меню на двух строках окна документа.

Лабораторная работа №4.

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

Лабораторная работа №5.

Добавление кнопок на панель инструментов.

Лабораторная работа №6.

Создание собственной панели инструментов.

1.6. Контрольные вопросы

1) Способы и технологии запуска Excel

2) Способы и технология выхода из Excel

3) Назвать основные компоненты экрана Excel

4) Назначение и структура

5) Строки заголовка

6) Строки основного меню

2. Основы работы с документами

2.1. Рабочие книги

Документы программы Microsoft Excel называются рабочими книгами. Каждая книга представляет собой отдельный файл с расширением XLS. Когда вы открываете окно программы Microsoft Excel, в нем автоматически создается книга. Ячейки таблицы, о которых было рассказано в разд. 1.4, являются элементами рабочей книги. Само по себе окно программы ни строк, ни столбцов не содержит. Более того, когда в окне Excel не открыта ни одна книга, большинство кнопок на панелях инструментов, команд главного меню, а также поле Имя и строка формул неактивны.

2.2. Работа с листами книги

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

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

Microsoft Excel позволяет одновременно работать не только с несколькими листами, но и с несколькими книгами (см. разд. 2.3). По умолчанию книга Excel состоит из трех листов. Переходить от одного к другому можно с помощью ярлыков в левом нижнем углу окна программы, на которых написаны названия листов. По умолчанию это Лист1, Лист2 и ЛистЗ. Вы можете изменить название одного или нескольких листов. Зачем это нужно? Если ваша рабочая книга небольшая, то названия листов, которые Microsoft Excel присваивает им по умолчанию, вас вполне могут устраивать. Однако если листов больше, то такие названия могут стать причиной путаницы. Название листа может содержать от 1 до 31 символа, при этом не допускаются знаки «?», «*», «\», «/», « [» и «] ».

2.3. Работа с несколькими книгами одновременно

Microsoft Excel позволяет работать с несколькими рабочими книгами одновременно.

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

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

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

- выбрать один из файлов в меню Окно;

- нажать сочетание клавиш Ctrl+F6;

- использовать комбинацию Alt+Tab — удерживая клавишу Alt, нажимать Tab до тех пор, пока не будет выбран нужный документ Excel.

2.4. Масштаб документа

Как было сказано в гл. 1, электронная таблица Excel состоит более чем из 16 тыс. строк и около 300 столбцов. Конечно, полностью такую таблицу невозможно увидеть на экране компьютера. Отображаемое на экране содержимое окна Excel — это всего лишь небольшая часть таблицы. В некоторых случаях, когда таблицы, с которыми вы работаете, небольшие, вы можете видеть их на экране полностью. Но чаще они не помещаются из-за большого количества данных. Чтобы иметь возможность увидеть больший участок таблицы, можно изменять масштаб. Он помогает увеличить или уменьшить размер изображения рабочего листа. Масштаб влияет исключительно на вид таблицы на экране компьютера, а не на изображение, полученное при печати. Уменьшив масштаб, можно увидеть сразу большое количество строк и столбцов, а увеличив масштаб — изображение небольшой части рабочего листа, если нужно рассмотреть данные подробно. Для изменения масштаба документа выполните одно из следующих действий:

- в списке Масштаб на панели инструментов Стандартная выберите значение в пределах 10-400 %. Можно/также ввести число в поле Масштаб вручную;

- выполните команду Вид > Масштаб и в открывшемся окне выберите масштаб документа.

2.5. Режимы просмотра документа

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

2.6. Лабораторные работы

Лабораторная работа №7.

Изменение папки, открываемой по умолчанию в окне Открытие документа.

Лабораторная работа №8.

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

Лабораторная работа №9.

Изменение количества запоминаемых программой файлов.

Лабораторная работа №10.

Выполнение основных операций с файлами.

2.7. Контрольные вопросы

1) Что мы называем рабочей книгой?

2) Способы создания рабочей книги

3) Типы новой рабочей книги

4) Способы открытия рабочей книги Excel вне окна программы

5) Способы сохранения рабочей книги

6) Как спрятать рабочий лист и восстановить его с помощью меню Excel и контекстного меню?

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

3.1. Ввод данных в ячейки

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

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

3.2. Типы данных в Excel

Данные в Excel условно можно разделить на три типа — это формулы, текст и числовые значения (включая также значения даты и времени). Чтобы программа правильно понимала, о каком типе данных идет речь, разработан специальный синтаксис. Так, ввод формулы необходимо начинать со знака =, а цифры, которую программа должна воспринять как текст, — с символа '. Кроме правил ввода данных, которые являются обязательными, существуют также общие правила построения таблиц. Их придерживаться не обязательно, но желательно, так как это существенно облегчает создание таблицы и последующую работу с ней.

3.3. Редактирование данных в ячейках

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

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

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

Завершить редактирование данных можно так же, как завершается простой ввод данных (см. разд. 3.1).

3.4. Лабораторные работы

Лабораторная работа №11.

Ввод данных в ячейку.

Лабораторная работа №12.

Изменение параметров активизации ячейки после окончания ввода.

Лабораторная работа №13.

Изменение отображения данных в числовом формате.

Лабораторная работа №14.

Использование при расчетах округленных чисел.

Лабораторная работа №15.

Изменение формата ячейки на текстовый.

Лабораторная работа №16.

Ввод данных в текстовом формате.

Лабораторная работа №17.

Редактирование данных в ячейке.

Лабораторная работа №18.

Работа с выделенным диапазоном ячеек.

Лабораторная работа №19.

Выделение разных областей таблицы.

Лабораторная работа №20.

Копирование содержимого ячейки.

Лабораторная работа №11.

Копирование и перемещение содержимого ячейки при помощи мыши.

Лабораторная работа №12.

Использование буфера обмена.

Лабораторная работа №13.

Использование команды Специальная вставка для работы с внешними приложениями.

Лабораторная работа №14.

Удаление ячеек.

Лабораторная работа №21.

Добавление ячеек.

3.5. Контрольные вопросы

1) Типы данных, клетки в Excel

2) Что содержит каждый тип данных

3) Процесс ввода данных в ячейку

4) Как задать или изменить формат ячейки

5) Как отредактировать содержимое ячейки

6) Процесс удаления содержимого ячейки

7) Как отредактировать содержимое ячейки

8) Процесс удаления содержимого ячейки

9) Способы перемещения данных в другую позицию

10) Как вставить строку в таблицу

11) Замена данных в клетке

4. Форматирование таблиц

4.1. Что такое форматирование

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

4.2. Форматирование строк и столбцов

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

Для этого выполните следующие действия:

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

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

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

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

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

Чтобы выделить несколько непоследовательных столбцов, выполняйте эту операцию, удерживая клавишу Ctrl.

4.3. Форматирование ячеек

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

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

4.4. Лабораторные работы

Лабораторная работа №22.

Изменение ширины столбца и высоты строки.

Лабораторная работа №23.

Выравнивание данных в таблице.

Лабораторная работа №24.

Добавление в таблицу границ и заливки.

Лабораторная работа №25.

Изменение параметров шрифта.

4.5. Контрольные вопросы

1) Как изменить шрифт в текстовом документе с помощью меню

2) Способы выравнивания содержимого ячеек

3) Как выполнить обрамление ячеек

4) Что понимается под фоном ячеек?

5) Как залить ячейки цветом

6) Как понимать «стиль представления данных»

5. Работа с числами. Формулы

5.1. Особенности ввода числовых данных

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

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

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

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

5.2. Использование формул

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

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

5.3. Лабораторные работы

Лабораторная работа №26.

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

Лабораторная работа №27.

Установка параметров проверки данных при вводе.

Лабораторная работа №28.

Расчет данных по простой формуле.

Лабораторная работа №29.

Установка параметра отображения формул в ячейках.

5.4. Контрольные вопросы

1) Что мы понимаем под формулой и какие символы она может содержать?

2) Для чего предназначены функции. Сколько их на какие группы разбиты

3) Для чего предназначены функции. Сколько их на какие группы разбиты

4) Перечислить правила работы с формулами и функциями

5) Назначение Мастера функции

6) Процесс обращения к встроенной функции посредством Мастера функций и через меню Excel

7) Знаки математических вычислений

8) Как осуществить автоматическое заполнение клетки

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

6.1. Мастер диаграмм

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

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

- выберите в меню команду Вставка > Диаграмма;

- нажмите кнопку Мастер диаграмм на панели инструментов Стандартная;

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

6.2. Форматирование текста диаграммы

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

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

Последний способ является наиболее простым.

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

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

Для его вызова выполните одно из следующих действий:

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

- щелкните на тексте правой кнопкой мыши и выберите в контекстном меню пункт Формат подписей данных.

6.3. Ввод в диаграмму дополнительного текста

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

разместив их в определенных местах. Для этого используется панель инструментов Рисование.

6.4. Вставка в диаграмму графического объекта

Кроме дополнительных текстовых элементов в диаграммах используются графические элементы (например, стрелки, линии и другие фигуры). Для добавления в диаграмму графического объекта также используются кнопки панели инструментов Рисование.

6.5. Использование диаграмм для анализа данных

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

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

6.6. Лабораторные работы

Лабораторная работа №30.

Создание диаграммы в таблице.

Лабораторная работа №31.

Создание дополнительной надписи на диаграмме.

Лабораторная работа №32.

Добавление в диаграмму графического объекта.

Лабораторная работа №33.

Добавление в диаграмму оригинального графического объекта.

6.7. Контрольные вопросы

1) Что такое диаграмма и какие типы диаграмм существуют?

2) Технология построения диаграмм

3) Вывод диаграммы на печать (на новом листе)

7. Работа с данными

7.1. Использование списков в качестве баз данных

Многие таблицы в Excel имеют вид списков данных.

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

Списки также содержат записи — информацию о каждом элементе списка. Обычно записями являются строки таблицы.

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

Для упрощения создания и редактирования списков используется команда Данные > Форма.

7.2. Отбор данных

При помощи средств Excel вы можете сделать так, чтобы на экране отображались только необходимые в данный момент данные. Это поможет вам сконцентрироваться на главном. Для выполнения отбора данных выберите в меню Данные > Фильтр > Автофильтр.

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

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

- все;

- первые 10;

- условие.

Данные, отсеянные в результате фильтрации, не удаляются, а скрываются. Для отключения фильтрации снова выполните команду Данные * Фильтр > Автофильтр.

7.3. Поиск данных

Если вам необходимо найти данные в списке, то можете это сделать при помощи кнопки Критерии диалогового окна формы. Для доступа к нему выполните команду Данные > Форма. Кнопка Критерии позволяет искать в списке по заданным параметрам. Такими параметрами могут быть текстовые и числовые фрагменты, а также выражение.

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

* — заменяет неограниченное количество символов;

? — заменяет одну букву или другой символ.

Чтобы просмотреть записи, соответствующие заданному критерию, используйте кнопки Назад и Далее.

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

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

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

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

- числа;

- текст;

- логические значения типа ЛОЖЬ;

- логические значения типа ИСТИНА;

- значения ошибок;

- пустые значения.

7.5. Построение сводных таблиц

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

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

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

7.6. Лабораторные работы

Лабораторная работа №34.

Отбор данных в списке.

Лабораторная работа №35.

Поиск данных.

Лабораторная работа №36.

Сортировка данных в списке.

Лабораторная работа №37.

Построение сводной таблицы.

7.7. Контрольные вопросы

1) Технология отбора данных

2) Технология поиска данных

3) Цель сортировки данных

4) Порядок упорядочивания данных

5) Технология сортировки данных

6) Что такое сводные таблицы?

8. Справка и решение проблем

8.1. Справочная система Microsoft Excel

Программа Microsoft Excel имеет очень мощную справочную систему. Многие ответы на вопросы можно найти, обратившись к ней. В программе предусмотрено несколько способов доступа к справочной информации:

- вызов справки из главного меню командой Справка > Справка: Microsoft Excel;

- вызов справки нажатием клавиши F1;

- вызов справки при помощи кнопки Справка: Microsoft Excel на панели инструментов Стандартная;

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

- использование поля Введите вопрос, которое расположено в строке меню справа.

8.2. Получение справки в режиме онлайн

В каждой новой версии интеграция Microsoft Office с онлайн-ресурсами становится более тесной. Каждый день увеличивается количество пользователей Интернета, и многие пользователи не могут представить себе работу без него. Компьютер, подключенный к Интернету, — сегодня уже не роскошь, а необходимость.

Именно поэтому в Microsoft Office 2003 встроенна система предоставления справочных материалов в режиме онлайн. Более того, это предпочтительнее обращения к встроенным в программы пакета справочным файлам, потому что на сайте Microsoft постоянно появляются новые сведения.

Поэтому информация, которая отсутствует в файлах справки, входящих в дистрибутив Microsoft Office, может быть доступна в режиме онлайн.

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

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

- Office в Интернете — программа связывается с главной страницей сайта Office Online. Здесь вы можете узнать последние новости о программах пакета и бесплатно скачать полезные дополнения (например, наборы шаблонов, надстройки, утилиты, коллекции картинок);

- Свяжитесь с нами — переход на страницу сайта Microsoft, поддерживающую пользователей русскоязычной версии пакета. Вы можете обратиться к этой странице, если вам не удалось найти ответ на вопрос по работе с программой в других источниках. Также с этой страницы можно отправить в службу поддержки Microsoft Office свои комментарии и советы по улучшению работы программы;

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

8.3. Поиск информации в Интернете

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

Приложение 1. Панели инструментов Excel

Панели инструментов	Назначение
Зависимости	Поиск ошибок; отслеживание связей между ячейками и добавление примечаний к файлам.
Диаграмма	Создание и форматирование диаграмм.
Рисование дуг	Создание линий, прямоугольников, эллипсов, стрелок, а также области, внутри которой может находиться текст.
Форматирование	Форматирование ячеек, содержимого ячеек и других объектов.
Формы	Разработка листов, оснащенных различными элементами управления.
Во весь экран	Увеличение размеров окна документа на весь экран, до строки меню.
Microsoft	Запуск (или переключение) других приложений Microsoft, например Microsoft Word
Запрос и сведение	Анализ данных Excel с помощью сводных таблиц
Стандартная	Обеспечение доступа к основным командам, таким как создать новую рабочую книну, открыть уже существующую, сохранить рабочею книгу и вывести ее на печать.
Остановка записи	Прекращение процесса записи макрокоманды.
Мастер подсказок	Вывод рекомкндаций, как лучше выполнить текущую операцию.
Visual Basic	Вставка нового модуля на языке Visual Basic, создание меню, а также запись и отладка макрокоманд.
Рабочая группа	Работа с Excel в сети.

Приложение 2. Операции в формулах

Арифметические операции
Обозначения	Операции
+	Сложение
-	Вычитание
-	Инверсия если это знак перед единственным операндом
*	Умножение
/	Деление
%	Преобразование в проценты
^	Возведение в степень
Операции сравнения
Обозначения	Операции
=	Равно
<	Меньше чем
>	Больше чем
<=	Меньше или равно
=>	Больше или равно
<>	Не равно
Операции адресации
Обозначения	Операции
:	Объединяет два адреса, т.е создает объединение несколькиъ областей возможно несмежных
:	Задает интервал смежных клеток, указывая координаты левого верхнего и правого нижнего углов
Пробел	Задает пересечение двух областей. В пересечение двух областей входят все те ячейки, которые лежат как к первой так и во второй области: (А1:С3 С3:Е5)
Текстовый оператор
Обозначения	Операции
&	Соединяет текстовые цепочки

Приложение 3. Назначение функциональных клавиш

Комбинация клавиш	Назначение
<F1>	Открывает окно справки
<Shift+F1>	Представляет контекстно-зависимую справку
<Alt+F1>	Создает новую диаграмму
< Alt+Shift+F1>	Вставляет новый лист
< Alt+Ctrl+F1>	Создает новый лст для макрокоманды MS Excel 4.0
<F2>	Редактирует активную ячейку
< Shift+F2>	Позволяет вводить, редактировать и удалять примечание к активной ячейке
< Ctrl+F2>	Выводит окно, в котором представлена информация об активной ячейке
< Alt+F2>	Открывает диалоговое окно Сохранение документа
< Alt+Shift+F2>	Сохраняет активный документ
< Alt+Ctrl+F2>	Открывает диалоговое окно Открытие документа
< Alt+Ctrl+Shift+F2>	Открывает диалоговое окно Печать
<F3>	Открывает диалоговое окно Вставка имени
< Shift+F3>	Открывает диалоговое окно Мастер функций
< Ctrl+F3>	Открывает диалоговое окно Присвоить имя
< Ctrl+Shift+F3>	Открывает диалоговое окно Создать имена
<F4>	Повторяет последнее действие или изменяет ссылки на ячейки (абсолютная/относительная)
< Ctrl+F4>	Закрывает окно активного документа
< Alt+F4>	Закрывает окно приложения
<F5>	Открывает диалоговое окно Переход
< Shift+F5>	Открывает диалоговое окно Найти
< Ctrl+F5>	Восстанавливает предыдущие размеры окна документа
<F6>	Переходит на следующее подокно в раздельном окне
< Shift +F6>	Переходит на предыдущее подокно в раздельном окне
< Ctrl+F6>	Активизирует следующее открытое окно рабочей книги
< Ctrl+Shift +F6>	Активизирует предыдущее окно рабочей книги
<F7>	Запускает программу проверки орфографии
< Ctrl+F7>	Преремещает текущее окно
<F8>	Расширяет выделение в таблище
< Shift +F8>	Выделяет еще один участок таблицы
< Ctrl+F8>	Разрешает изменение размеров текущего окна с помощью клавиш управления курсором
<F9>	Пересчитывает всю рабочую книгу
< Shift +F9>	Пересчитывает только лист текущей рабочей таблицы
< Ctrl+F9>	Минимизирует текущее окно
<F10>	Активизирует строку меню
< Shift +F10>	Активизирует контекстное меню
< Ctrl+F10>	Максимизирует текущее окно
<F11>	Создает диаграмму на новом листе
< Shift +F11>	Вставляет новый лист
< Ctrl+F11>	Вставляет лист макрокоманды MS Excel 4.0
<F12>	Открывает диалоговое окно Сохранение документа
< Shift +F12>	Сохраняет активный документ
< Ctrl+F12>	Открывает диалоговое окно Открытие докумена
< Ctrl+ Shift +F12>	Открывает диалоговое окно Печать

Приложение 4. Сообщение об ошибках

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

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

В формуле сделана попытка деления на ноль 0: #DIV\0!, #ДЕЛ\0!

Значение не доступно. Это значение можно ввести в ячейку для запрета вычислений с использованием этой ячейки, пока данные для нее не готовы: #N\A!, #H\Д!

Было использовано имя, которое Excel не распознала, т.е. имя было не указано, удалено, неверно записано или текст был введен в формулу без кавычек и рассматривается как имя: #Name?, #ИМЯ?

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

Проблема с числом. В функции был применен неверный аргумент, например SQRT – 1, или результат формулы является слишком большим или слишком маленьким для Excel: #NUM!, #ЧИСЛО!

Задана некорректная ссылка на поле: #REF!, #ССЫЛКА!

Был использован неверный тип аргумента или оператора, например, текст стоит там, где должно быть число: #VALUE!, #ЗНАЧ!

Приложение 5. Описание лабораторных работ

Лабораторная работа №2.

Создание пользовательского меню

Цель: научиться создавать пользовательское меню

Ход выполнения:

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

1) Запустите Excel;

2) Выполните команду Сервис > Настройка;

3) Перейдите на вкладку Команды;

4) В списке Категории выберите строку Новое меню. При этом в списке Команды появится команда Новое меню;

5) Щелкните на команде Новое меню мышью и, не отпуская кнопку, переместите ее на свободное место строки меню. На то, что меню может быть добавлено на панель, указывает значок +;

6) Отпустите кнопку мыши. Меню будет добавлено;

7) Не закрывая диалоговое окно Настройка, щелкните на меню правой кнопкой мыши и ведите его название в соответствующее поле;

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

9) Добавив все нужные команды в меню, закройте диалоговое окно Настройка при помощи кнопки Закрыть. Меню готово, им можно пользоваться (рис.1.).

Рис.1. Пользовательское меню

Лабораторная работа №6.

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

Цель: научиться создавать пользовательскую панель

Ход выполнения:

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

1) Запустите Excel;

2) Выберите в меню Сервис > Настройка;

3) Перейдите на вкладку Панели инструментов;

4) Нажмите кнопку Создать;

5) В открывшемся окне Создание панели инструментов введите название панели и нажмите кнопку ОК;

6) Панель появится на экране. Кнопки не ней пока отсутствуют;

7) Перейдите на вкладку Команды окна Настройка;

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

9) После окончания добавления кнопок на панель инструментов закройте окно Настройка;

10) Щелкните на панели мышью и, не отпуская ее, перенесите панель на требуемое место (рис.2.);

11) Если необходимо, то скройте ненужные панели. Для этого щелкните правой кнопкой мыши на любой панели инструментов и в появившемся списке снимите флажки напротив отображаемых панелей.

Рис.2. Размещение пользовательской панели в удобном месте экрана

Лабораторная работа №11.

Копирование и перемещение содержимого ячейки при помощи мыши

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

Ход выполнения:

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

12) Запустите Excel;

13) Выделите ячейку, которую нужно переместить данные;

14) Установите курсор таким образом, чтобы он превратился в стрелку;

15) Удерживая нажатой левую кнопку мыши, укажите ячейку, в которую нужно переместить данные. Для удобства выбора возле курсора отображается адрес ячейки;

16) Когда вы отпустите кнопку мыши, данные будут перемещены. Ячейка, в которую были внесены данные, станет активной;

Быстрое копирование при помощи мыши осуществляется точно так же, однако перед указанием ячейки, в которую нужно скопировать данные, необходимо нажать и удерживать клавишу Ctrl. При этом возле курсора появится знак плюс (рис.3.)

Рис.3. Результат копирования данных в несколько ячеек одновременно

Лабораторная работа №22.

Изменение ширины столбца и высоты строки

Цель: научиться изменять ширину столбца и высоту строк.

Ход выполнения:

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

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

1) Запустите Excel;

2) Выделите столбец В, щелкнув на области заголовка. Удерживая клавишу Shift, выделите также столбец С. Установите курсор на правую границу заголовка одного из столбцов;

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

4) Выполните команду Правка > Отменить, чтобы вернуть первоначальный вид таблицы. Подобрать нужную ширину столбца описанным выше способом удается не всегда, поэтому попробуем сделать это при помощи автоматического подбора;

5) Снова выделите столбцы В и С при нажатой клавише Shift. Выполните команду Формат > Столбец > Автоподбор ширины. Убедитесь, что ширина столбцов изменилась, и данные помещаются в ячейках;

6) Выделите строку 5, щелкнув на ее заголовке. Удерживая клавишу Ctrl, выделите также строки 3 и 8. Установите курсор на нижней границе заголовка одной из выделенных строк;

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

8) Снимите выделение, щелкнув на любой невыделенной ячейке таблицы;

9) Выделите строку 3, щелкнув на ее заголовке. Удерживая клавишу Shift, щелкните на заголовке строки 8. Убедитесь, что были выделены строки 3-8;

10) Установите курсор на нижней границе заголовка одной из выделенных строк и дважды щелкните на нем. Убедитесь, что высота всех строк изменилась и стала одинаковой (рис.4.).

Рис.4. Изменение высоты строк в автоматическом режиме

Лабораторная работа №26.

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

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

Ход выполнения:

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

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

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

1) Запустите Excel;

2) Выделите ячейку А4 и введите в нее значение 1. Завершите ввод при помощи клавиши Enter. Введите в ячейку А5 значение 2. Выделите обе ячейки;

3) Подведите указатель к маркеру автозаполнения и, удерживая кнопку мыши, перетащите указатель на столько строк вниз, сколько товаров будет в таблице. Число возле указателя подскажет возле какой ячейки отпускать кнопку мыши. В нашем случае в таблице планируется разместить восемь категорий товара, поэтому мы заполнили ячейки до числа 8;

4) Если требуется добавить еще несколько строк, выделите ячейки с данными 7 и 8, подведите указатель к маркеру автозаполнения и проделайте операцию снова (рис.5.);

Сохраните файл.

Рис.5. Повторное использование маркера автозаполнения

Лабораторная работа №37.

Построение сводной таблицы

Цель: научиться строить сводную таблицу.

Ход выполнения:

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

1) Запустите Excel;

2) Создайте таблицу и укажите в ней названия товаров, а также вбъемы продаж за три месяца. На втором и третьем листах таблицы введите данные для магазинов в других городах. Обратите внимание, что три таблицы должны быть оформлены одинаково;

3) Выделите одну из ячеек списка и выполните команду Данные > Сводная таблица;

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

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

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

7) Повторите операцию для всех диапазонов ячеек. Вы можете переключаться на другие рабочие листы для выделения диапазонов;

8) Нажмите кнопку Далее и в последнем окне Мастера сводных таблиц укажите, где должна быть создана сводная таблица: на новом листе или на существующем;

9) Нажмите кнопку Готово, чтобы завершить создание сводной таблицы. По получившейся таблице очень удобно наблюдать за динамикой продаж и за общими объемами (рис.6.);

10) Для удобства использования таблицы предусмотрен вывод на экран только некоторых записей. Чтобы задать просматриваемые записи, раскройте список Строка и выберите нужные элементы.

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

Рис.6. Пример сводной таблицы

Методическое пособие по MS Excel

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

28.01.2020