Информатика. Обработка числовых данных в электронных таблицах (Excel)

• Название: Обработка числовых данных в электронных таблицах (Excel)
• Анкор: Информатика
• Дата: 21.10.2022
• Размер: 1.33 Mb.
• Имя файла: laba_5.doc
• Тип: Книга; #745785

Тема: «Обработка числовых данных в электронных таблицах (Excel) »

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

Учебная информация

1. Рабочая книга

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

Вся рабочая область окна Excel занята чистым рабочим листом, разделенным на отдельные ячейки. Элементы данных записываются в ячейки таблицы. Ячейки в Excel расположены на пересечении столбцов и строк, которые обозначаются А, В, С и 1, 2, 3 и т.д. соответственно. Таким образом, адрес ячейки формируется из названия столбца и номера строки, например, А1, В12 .

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

Ячейка может содержать три вида данных:

• 
  Текстовые данные представляют собой строку текста произвольной длины. Ячейка, содержащая текстовые данные, не может использоваться в вычислениях.
  
• 
  Числовые данные – это отдельное число. Как числа рассматриваются данные, определяющие дату или денежные суммы. Ячейки, содержащие числовые данные, могут использоваться в вычислениях.
  
• 
  Формула. Содержимое ячейки рассматривается как формула, если оно начинается со знака «=». Если ячейка содержит формулу, значит ячейка вычисляемая, то есть ее значение зависит от значений других ячеек таблицы. Формулы в ячейках не отображаются, вместо них воспроизводится результат, полученный при вычислении формулы. Формула отображается в строке формул для выделенной ячейки.
  

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

Стандартная Форматирование



Ярлычки листов

Рисунок 1. Окно Excel

2. Выделение диапазона ячеек

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

Если необходимо выделить строку, щелкните мышью на номере-заголовке строки. Если нужно выделить несколько строк проведите мышью, не отпуская клавиши, по соответствующим заголовкам строк. Аналогично можно выделить один или несколько столбцов. С помощью клавиатуры эти действия можно выполнить, нажав комбинацию клавиш Shift+Space для выделения строки или Ctrl+Space для выделения столбца.

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

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

3. Вставка и удаление ячеек

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

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

Если необходимо удалить содержимое ячейки, выберите команду контекстного меню Очистить содержимое, или команду меню Правка – Очистить, или нажмите клавишу Delete.

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

Удалить весь столбец или строку можно, используя команду меню Правка – Удалить. Операции вставки и удаления можно провести, используя соответственно комбинации клавиш Ctrl+ ‘+’ для вставки или Ctrl+ ’–‘ для удаления.

4. Копирование, перемещение и вставка данных

Команды для копирования и перемещения находятся в меню Правка. Выделите блок ячеек, которые нужно скопировать или переместить, и выберите из данного меню соответственно команду Копировать илиВырезать. С помощью команды Вставить вы можете вставить блок ячеек в различные места вашего рабочего листа. Эти операции можно выполнить также, используя комбинации клавиш Ctrl+C – копировать, Ctrl+X – вырезать, Ctrl +V – вставить, или соответственно Ctrl+Insert, Shift+Delete, Shift+Insert.

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

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

5. Поиск и замена данных

Чтобы быстрее находить необходимые места в рабочем листе, в вашем распоряжении есть функция поиска. Диалог, соответствующий этой функции, можно вызвать с помощью команды меню Правка – Найти или нажав комбинацию клавиш Ctrl+F. Введите в поле Найти шаблон, по которому пойдет поиск. Это может быть слово или какая-нибудь цифра. В этом поле можно использовать символы «*» и «?» так же, как они используются в шаблонах имен файлов для команд DOS. Если вы введете, например, шаблон И*, то Excel найдет все слова, начинающиеся на И. С помощью шаблона Ию?ь можно найти слова Июль и Июнь.

При необходимости исправить найденное слово нажмите кнопку Заменить. Если вы хотите не только найти слово или набор символов, но и заменить его, выберите команду меню Правка – Заменить или нажмите комбинацию клавиш Ctrl+H. Когда Excel найдет слово по заданному шаблону, вы сможете его заменить на слово, введенное в поле Заменить на, нажав кнопку Заменить. Если вы хотите заменить найденное слово по всему рабочему листу, нажмите кнопку Заменить все.

6. Фильтрация и сортировка данных

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

Команда меню Данные – Фильтр – Отобразить все позволяет отобразить все записи. Такого же результата можно добиться, выбрав из списка фильтрации пункт Все. Чтобы отменить использование автофильтра, надо повторно дать команду Данные – Фильтр – Автофильтр.

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

7. Числовые ряды

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

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

8. Примечания в ячейках

Чтобы ввести примечание в ячейку, выберите команду меню Вставка – Примечание для выделенной ячейки. Введите текст примечания. Созданное примечание можно изменить с помощью команды контекстного меню Изменить примечание. Команда контекстного меню Отобразить примечание позволяет соответственно отображать или скрывать примечание. Чтобы удалить примечание, используйте команду контекстного меню Удалить примечание.

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

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

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

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

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

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

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

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

Чтобы изменить ширину столбца, вы можете открыть диалоговое окно Ширина столбца с помощьюкоманды меню Формат – Столбец. Этот диалог можно открыть также, щелкнув на выделенном столбце правой кнопкой мыши и выбрав команду контекстного меню Ширина столбца. Регулировать ширину столбца можно также с помощью мыши, перетаскивая границу столбца в поле заголовков столбцов.

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

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

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

11. Вставка и редактирование формул

Чтобы вставить формулу в ячейку, выделите ячейку и начните ввод формулы с нажатия клавиши «=». Затем введите адреса ячеек, над данными в которых вы хотите выполнить определенные действия, а между ними вставьте соответствующий этим действиям оператор. Операторами называют специальные символы, с помощью которых описываются математические функции («+», «-», «*», «/», «%», «<», «:» – диапазон, «;» – объединение, «&» – соединение текстов).

Формулу можно вводить также непосредственно в строке формул.

Для подсчета, например, суммы содержимого нескольких ячеек, расположенных не рядом друг с другом, выполните следующие действия: в ячейке, в которой нужно получить результат, например, F2 (таблица 1), поставьте знак «=» и укажите адреса ячеек, содержимое которых вам нужно сложить, через знак «+». Например, для подсчета прибыли от продажи товаров, имеющих наивысшую цену в каждом из трех месяцев, введите формулу =В2+С3+D3. Вводить адреса ячеек можно не вручную, а с помощью щелчка мыши на той ячейке, содержимое которой вы хотите сложить. Затем, подтвердить введенную формулу нажатием на клавишу Enter. Результат сложения вы увидите в ячейке F2.

Используя функцию автоматической суммы, можно быстро найти сумму данных, содержащихся в расположенных рядом ячейках. Кнопка автосуммирования  находится на панели инструментов Стандартная. Для ее использования выделите ячейку В5 (таблица 1) и при нажатии на кнопку  программа сама выделит ячейки от В2 до В4, а в ячейке и в строке формул появится команда СУММ(В2:В4). Если вы хотите поменять аргументы суммы, то выделите соответствующую область. Подтвердите введенную формулу нажатием на клавишу Enter.

Таблица 1


Для того чтобы не вводить формулу суммы по каждому столбцу отдельно, можно выделить ячейки с В5 по D5 и выбрать команду меню Правка – Заполнить – Вправо. После этого формула из ячейки В5 скопируется в ячейки С5 и, причем, если в ячейке В5 находилась формула СУММ(В2:В4), то при заполнении ячейки С5 будет вставлена формула СУММ(С2:С4), а в ячейке D5 – формула СУММ(D2:D4). Таким образом, при переносе формул адреса ячеек будут автоматически изменяться. С помощью этого способа вы можете заполнять ячейки формулами вправо, влево, вверх или вниз, используя соответствующую команду из меню Правка – Заполнить.

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

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

Если вы хотите отредактировать уже введенную формулу, то можете сделать это несколькими способами:

1) Дважды щелкните мышью на ячейке, чтобы начать редактирование формулы, содержащейся в ней.

2) Выделите ячейку и нажмите клавишу F2.

3) Выделите ячейку и щелкните мышью в строке формул.

12. Относительные и абсолютные адреса ячеек

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

Например, для вычисления процентного соотношения В2/В5 в ячейку Е2 (таблица 2) введите формулу =В2/В5, затем выделите ячейку Е2 и нажмите кнопку % на панели инструментов Форматирование. Теперь скопируйте формулу из ячейки Е2 в ячейки Е3 и Е4. Вы получите ошибочный результат, т.к. при копировании адрес ячейки В5 изменится на В6 и на В7. Чтобы избежать этого, необходимо заменить адрес ячейки с относительного В5 на абсолютный $B$5, т.е. не подлежащий изменению при копировании формул. Эту операцию можно выполнить автоматически, если дважды щелкнуть в строке формул на адресе ячейки В5 и нажать клавишу F4.

При заполнении ячеек формулой как относительная рассматривается только та часть адреса, перед которой нет символа «$». Если записать например, $А4, то при переносе формул будет изменяться только адрес строки ячеек, а при обозначении А$4 – только адрес столбца.

Таблица 2


Другой способ абсолютной адресации ячеек заключается в назначении имен ячейкам и использовании их в формулах. Например, назначив имя Всего ячейке В5 (таблица 2), можно в ячейке Е2 ввести формулу =В2/Всего.

Чтобы назначить имя ячейке В5, выделите ячейку В5 и выберите команду меню Вставка – Имя – Присвоить или введите имя в поле имени. Аналогично вы можете назначить имя для диапазона ячеек, предварительно выделив его в рабочем листе.

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

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

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

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

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

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

14. Матрица

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

В строку формул введите формулу =А1:А5*5 и нажмите комбинацию клавиш Ctrl+Enter. Такого же результата можно добиться, если в ячейку В1 ввести формулу =А1*5, нажать клавишу Enter, а затем данную формулу скопировать в ячейки с В2 по В5 любым известным вам способом.

Таблица 3

	А	В	С	D
1	1	5	2	2
2	3	15		6
3	5	25		10
4	7	35		14
5	9	45		18

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

- введите в ячейку D1 формулу =А1*$С$1 и нажмите клавишу Enter

- скопируйте введенную формулу в ячейки D2:D5.

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

15. Создание диаграмм

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

• 
  Ряды данных – это наборы значений, которые требуется изобразить на диаграмме (значения функции на оси Y).
  
• 
  Категории (аргументы функции на оси Х) служат для упорядочения значений в рядах данных.
  
• 
  Легенда – это условные обозначения значений различных рядов данных на диаграмме.
  

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

1. 
   щелкнув на кнопке Мастер диаграмм на панели инструментов Стандартная.
   
2. 
   используя контекстно-зависимое меню для ярлычков рабочих листов и, вызвав команду Добавить, на вкладке Общиевыберите Диаграмма из предложенных вариантов объектов.
   
3. 
   выбрав команду меню Вставка – Диаграмма.
   

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

Таблица 4

	A	B	C	D
1	Наименование	1998	1999	2000
2	Книжный мир	48	43	54
3	Дом книги	44	60	67
4	Техническая книга	53	48	52

Для примера, построим гистограмму (рисунок 2), используя таблицу 4.

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

Выбрав диапазон данных A1:D4, щелкните на кнопке Мастер диаграмм на панели инструментов Стандартная. Далее следуйте пошаговым указаниям и выполняйте следующие действия: 1 шаг – выберите тип диаграммы Гистограмма на вкладке Стандартная; 2 шаг – установите флажок в строках в опции Ряды на вкладке Диапазон данных; 3 шаг – на вкладке Легенда активизируйте переключатель Добавить легенду и установите флажок справа в опции Размещение; 4 шаг – установите опцию Поместить диаграмму на листе имеющемся. Подтвердите ваши установки нажатием на кнопку Готово. В результате вы увидите следующую диаграмму


Рисунок 2. Гистограмма

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

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

Созданные диаграммы можно редактировать. Вы можете, например, вставить название к созданной выше диаграмме. При создании диаграмм используется два вида текстов: связанный и свободный. Связанный текст – это заголовки элементов диаграммы, а свободный вы можете вводить по своему желанию. Чтобы вывести связанный текст, выберите команду Параметры диаграммы из контекстного меню для области диаграммы и на вкладке Заголовки в окно Название диаграммы введите текст «книги», в окно ОсьХ(категорий) – текст «года», а в окно Ось У(значений) – текст «количество».

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

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

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

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

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

Если вы выполните вышеописанные операции по редактированию созданной диаграммы, то ваша диаграмма примет следующий вид:


Рисунок 3. Надписи к гистограмме

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

Вы можете более наглядно оформить свою диаграмму, проведя линии сетки (рисунок 4). Для этого выделите область диаграммы, выберите команду контекстного меню Параметры диаграммы и в одноименном диалоговом окне на вкладкеЛинии сеткиустановите соответствующие опции. Например, установите флажки Основные линии в опциях Ось Х(категорий) и Ось У(значений). С помощью вкладки Оси этого окна вы можете включать или выключать изображение осей диаграммы. Отключите, например, изображение оси Х. Но, чтобы при этом не потерять значения категорий, установите на вкладке Подписи данных флажок в опции Включить в подписи - имена категорий.

В результате вы можете получить следующий вид диаграммы:


Рисунок 4. Отформатированная диаграмма

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

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

17. Работа с несколькими листами и окнами

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

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

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

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

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

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

18. Работа с листами рабочей книги

Например, вам необходимо создать рабочую книгу с поквартальным отчетом. Для этого вам нужно всего 4 листа. Удалите лишние листы книги, а оставшиеся четыре листа переименуйте. Для этого воспользуйтесь командами меню Правка – Удалить лист и Формат – Лист – Переименовать или соответствующими командами контекстно-зависимого меню для ярлычков рабочей книги Удалить и Переименовать. Листам удобнее давать названия, отражающие их содержание, например, 1 квартал, 2 квартал и т.д.

Если требуется удалить несколько подряд идущих листов, щелкните на ярлычке первого из них, и, удерживая нажатой клавишу Shift, щелкните на ярлычке последнего листа. Можно выделить несколько листов, расположенных не подряд, щелкнув на них при нажатой клавише Ctrl.

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

Перемещать и копировать рабочие листы можно:

• 
  с помощью команды контекстного меню Переместить / Скопировать. Для копирования установите флажок Создавать копию в диалоговом окне Переместить и скопировать.
  
• 
  с помощью мыши, перетаскивая ярлычок в нужную позицию. Для копирования выполните перемещение при нажатой клавише Сtrl.
  
• 
  воспользовавшись командой меню Правка – Переместить / Скопировать лист. Лист будет вставлен перед тем листом, который вы укажете. Для копирования установите флажок Создавать копию в открывшемся диалоговом окне.
  

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

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

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

19. Связывание рабочих листов

С помощью связывания листов можно свести воедино значения разных таблиц на одном рабочем листе. Для примера создайте два рабочих листа Прайс-Лист и Калькуляция. Расположите их рядом с помощью команды Окно – Расположить -Рядом.



Рисунок 5. Три листа одной книги, отображенных в одном окне

В левом окне откройте Прайс-Лист, а в правом – Калькуляция. В столбце «Итого» листа Калькуляция введите формулы, использующие данные с Прайс-Листа и с листа Калькуляция. При этом адрес ячейки, расположенной на листе Калькуляция указывается обычным способом, а адрес ячейки с листа Прайс-Лист – путем разделения имени листа и адреса знаком «!», т.е. в ячейку С2 введите формулу =Прайс-Лист!В4*А2, а в ячейку С3 – формулу = Прайс-Лист!В3*А3. Рассчитайте значение в ячейке С5 листа Калькуляция.

Расширим наш пример еще одним рабочим листом и назовем его Предложение. Чтобы ссылаться на ячейку С5 на листе Калькуляция с указанием общей суммы заказа, дадим имя этой ячейке Всего. Это имя можно использовать при связывании листов Калькуляция и Предложение. В ячейку В6 листа Предложение введите формулу =Всего.

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

1) Как выделить несколько ячеек, соединенных и не соединенных в один блок?

2) Как выделить строку и столбец ячеек, рабочий лист?

3) Как добавить в созданную таблицу или удалить из нее строку, столбец, несколько столбцов, несколько строк?

4) Какие вы знаете способы копирования и перемещения данных?

5) Как осуществить быстрый поиск и замену нужных данных в рабочем листе?

6) Для чего используется фильтрация данных? Как установить фильтр?

7) Как можно быстро создать ряд данных (рассмотрите 2 способа)?

8) Как можно изменить ширину столбца и высоту строки, установить оптимальную ширину столбца?

9) Как можно ввести формулу в ячейку и отредактировать введенную формулу?

10) Как скопировать формулу в соседние ячейки (опишите 2 способа)?

11) Чем отличаются относительные адреса ячеек от абсолютных? Как ввести абсолютный адрес ячейки (опишите два способа)? В чем состоит отличие записей $А$2, $А2, А$2?

12) Для чего дается имя ячейке? Как назначить имя ячейке (опишите 2 способа)?

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

14) В каких случаях может потребоваться открыть новое окно, разбить окно на подокна? Как разбить окно на подокна (опишите 2 способа)? Для чего предназначена функция Закрепить области?

15) Какие действия необходимо выполнить для удаления и вставки рабочего листа? Опишите 2 способа переименования рабочих листов.

16) Как можно выделить несколько листов, подряд и не подряд следующих в рабочей книге? Рассмотрите 2 способа копирования и перемещения листов.

Задание

1. 
   Создайте рабочую книгу из трех рабочих листов и переименуйте их. Лист1 – Количество, Лист2 – Выручка, Лист3 – Диаграмма.
   
2. 
   Разработайте таблицу 1 на первом рабочем листе и таблицу 2 на втором рабочем листе, введя условные данные в отмеченные ячейки. Количество наименований должно быть не меньше 6. Ввод данных в первый и второй столбцы в таблице 1 и таблице 2 осуществляйте, сгруппировав два листа.
   

Таблица 1

№ п/п	Название книги	Количество продаж				Итого	Макс. кол-во
		1 квартал	2 квартал	3 квартал	4 квартал
1	Анна Каренина	х	х	х	х
2	х	х	х	х	х
Всего

Таблица 2

№ п/п	Название книги	Цена	Выручка, руб.	Курс, $	Выручка, $	Процент
1	Анна Каренина	х		х
2	х	х

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