Оффисные приложения Ms Excel 2010
.pdfМинский филиал государственного образовательного учреждения высшего профессионального образования «Московский государственный университет экономики, статистики и информатики (МЭСИ)»
Курбацкий В.Н.
Информатика
для студентов экономических специальностей
Часть 3
Стандартные офисные приложения. MS Excel
Учебно-методическое пособие
МИНСК
2011
Основы работы с табличным процессором Microsoft Excel»
Оглавление |
|
1. Основы работы с табличным процессором Microsoft Excel............................................... |
4 |
1.1.Определение, назначение, основные функции и области применения табличных
процессоров................................................................................................................................ |
4 |
|
1.2. Табличный процессор Microsoft Excel 2010 как приложение пакета Microsoft |
|
|
Office5 |
|
|
1.3. Функциональные возможности Microsoft Excel .......................................................... |
5 |
|
1.4. Элементы интерфейса Microsoft Excel 2010. Основные понятия и определения..... |
7 |
|
1.5. |
Характеристика типов данных: текст, число, формула............................................. |
10 |
1.6. Возможности Excel 2010, которые не поддерживаются в предыдущих версиях |
|
|
Excel 12 |
|
|
1.7. |
Создание книги.............................................................................................................. |
13 |
1.8. |
Колонтитулы.................................................................................................................. |
13 |
1.9. |
Печать листа.................................................................................................................. |
14 |
1.10. Выделение ячейки, диапазона или текста в ячейке................................................ |
15 |
|
1.11. |
Форматирование данных.......................................................................................... |
17 |
1.12. Создание и удаление таблицы Excel на листе........................................................ |
18 |
|
1.13. Вставка и удаление ячеек, строк и столбцов.......................................................... |
21 |
|
1.14. Объединение ячеек или разделение объединенных ячеек.................................... |
23 |
|
2. РАБОТА С ФОРМУЛАМИ И ФУНКЦИЯМИ В MICROSOFT EXCEL ........................ |
28 |
|
2.1. |
Правила записи формул................................................................................................ |
28 |
2.2. |
Типы операторов. Иерархия (последовательность выполнения) операторов......... |
29 |
2.3.ССылки в формулах: относительные, абсолютные и смешанные ссылки,
внутренние и внешние ссылки. Стиль ссылок R1C1. .......................................................... |
31 |
2.4.Определение функции. Категории функций в Microsoft Excel. Общие принципы
построения функций. .............................................................................................................. |
34 |
2.5.Табулирование функций. Табулирование функций с использованием операций
копирования............................................................................................................................. |
38 |
2.6. Решение систем линейных алгебраических уравнений............................................ |
39 |
3.ОТОБРАЖЕНИЕ ДАННЫХ В ВИДЕ ДИАГРАММ. ПЕЧАТЬ ЭЛЕКТРОННЫХ
ТАБЛИЦ И ДИАГРАММ В MICROSOFT EXCEL.................................................................. |
41 |
|
3.1. |
Основные сведения о диаграммах............................................................................... |
41 |
3.2. |
Создание диаграмм....................................................................................................... |
43 |
3.3.Изменение диаграмм. Применение стилей и макетов. Форматирование диаграмм. 45
4. Работа с данными в Microsoft Excel.................................................................................... |
55 |
|
4.1. |
Проверка данных........................................................................................................... |
55 |
4.2. Фильтрация и сортировка данных............................................................................... |
56 |
|
4.3. |
Структурирование списка данных на листе. Промежуточные итоги. ..................... |
60 |
2
Основы работы с табличным процессором Microsoft Excel»
4.4. |
Создание или удаление отчета сводной таблицы или сводной диаграммы............ |
65 |
4.5. |
Консолидация данных. ................................................................................................. |
72 |
3
Основы работы с табличным процессором Microsoft Excel»
1. ОСНОВЫ РАБОТЫ С ТАБЛИЧНЫМ ПРОЦЕССОРОМ MICROSOFT EXCEL
Цель занятия – изучить принципы работы с табличным процессором MS Excel.
1.1.ОПРЕДЕЛЕНИЕ, НАЗНАЧЕНИЕ, ОСНОВНЫЕ ФУНКЦИИ И ОБЛАСТИ ПРИМЕНЕНИЯ ТАБЛИЧНЫХ ПРОЦЕССОРОВ
Табличный процессор – комплекс программных средств, обеспечивающих создание, хранение, редактирование и обработку информации в форме электронных таблиц. Так как размещение информации осуществляется в ячейках таблицы, приложения данной категории часто называют просто электронными таблицами.
Одна из основных функций табличного процессора – производство вычислений. Вычисления базируются на математических формулах, которые, в конечном счете, основываются на арифметических (сложение, вычитание, умножение, деление) и/или логических операциях. В формулах наряду с простейшими операциями могут использоваться функции, например, КОРЕНЬ (корень квадратный из числа). Формулы, как и данные в электронных таблицах, хранятся в общей табличной структуре.
Помимо вычислительной современные табличные процессоры имеют ряд функций, которые можно отнести к основным, дополнительным и сервисным функциям, например,
построение диаграмм, оформление и печать документов, работа с таблицами в стиле баз данных, экспорт/импорт информационных объектов в/из других программных приложений и др.
Области применения электронных таблиц самые разнообразные – бухгалтерия, экономический анализ, прикладная статистика, бизнес, медицина, социология и психология. С помощью табличных процессоров могут успешно решаться вычислительные задачи математики, физики, производиться технические и конструкторские расчеты. Поскольку таблица является основным конструктивным элементом баз данных, то с помощью электронных таблиц можно реализовывать базы данных – структурированные хранилища информации с возможностью выборки требуемых данных по определенным критериям (запросам).
Табличный процессор Microsoft Excel - это программа для работы с электронными таблицами, входящая в состав пакета Microsoft Office. С помощью Excel можно создавать и форматировать книги (наборы листов) для анализа данных и принятия более обоснованных деловых решений. Например, с помощью Excel можно отслеживать данные, разрабатывать модели анализа данных, создавать формулы для вычислений с этими данными, сводить данные множеством способов, а также отображать их на профессионально выглядящих диаграммах различных видов.
Ниже перечислены распространенные области применения Excel.
Учет. Можно использовать мощные вычислительные возможности Excel для работы с различными финансовыми документами, например отчетами о движении денежных средств, отчетами о доходах или отчетами о прибылях и убытках.
Бюджетирование. В Excel можно создавать как личный бюджет, так и бюджеты компании, например план выделения средств на маркетинг, бюджет мероприятия или пенсионных отчислений.
Выставление счетов и продажи. Приложение Excel также полезно применять для управления данными о выставлении счетов и продажах. Можно легко создавать нужные формы, например счета, отборочные накладные или заказы на покупку.
Создание отчетов. В Excel можно создавать отчеты различных типов, в которых анализируются или обобщаются данные, например отчеты о ходе выполнения
4
Основы работы с табличным процессором Microsoft Excel»
проекта, расхождениях между предполагаемыми и фактическими результатами, а также отчеты для прогнозирования данных.
Планирование. Excel прекрасно подходит для создания профессиональных планов или удобных планировщиков, таких как расписание занятий на неделю, план маркетинговых исследований, план налогообложения, а также планы отпусков, вечеринок или приготовления пищи на неделю.
Отслеживание. С помощью Excel можно отслеживать данные в листах учета или списках — к примеру, листах учета рабочего времени либо товарных списках для складского учета.
Работа с календарями. Рабочая область Excel, имеющая вид таблицы, позволяет легко создавать календари различных видов — например, учебный календарь для отслеживания различных видов деятельности в течение учебного года, либо календарь финансового года для отслеживания
Приложение обладает широкими функциональными возможностями – от построения диаграмм по табличным данным и экспорта/импорта объектов из других приложений Microsoft Office 2003 до создания простейших (но весьма объемных и с возможностью сложной обработки) баз данных.
1.2.ТАБЛИЧНЫЙ ПРОЦЕССОР MICROSOFT EXCEL 2010 КАК ПРИЛОЖЕНИЕ ПАКЕТА
MICROSOFT OFFICE
Табличный процессор Microsoft Excel 2010 входит в состав пакета Microsoft Office 2010. Microsoft Office – это многофункциональный программный продукт, поддерживающий единую пользовательскую среду для всех своих приложений и обеспечивающий их полную интеграцию. В его состав входит около двух десятков отдельных программ, электронные таблицы Microsoft Excel наряду с текстовым редактором Microsoft Word включены во все версии Microsoft Office. Microsoft Office 2010 может работать под управлением современных операционных систем семейства Windows и широко использует их возможности, что значительно упрощает работу пользователя.
Табличный процессор Microsoft Excel 2010 как приложение пакета Microsoft Office 2010 характеризуется тем, что:
имеет стандартный для всех приложений пакета Microsoft Office пользовательский интерфейс;
реализована возможность быстрого перехода из приложения Microsoft Excel в другие приложения, например, текстовый процессор Microsoft Word, СУБД
Microsoft Access и другие;
совместно используются данные в Microsoft Excel и в других приложениях (например, копирование данных из одного приложения в другое, связывание данных из документов разных приложений, внедрение данных из документа одного приложения в документ другого приложения);
совместно используются ресурсы в Microsoft Excel и в других приложениях (например, единые графические ресурсы для всех приложений);
существует единый язык управления Microsoft Excel и другими приложениями, которым является язык программирования Microsoft Visual Basic.
1.3.ФУНКЦИОНАЛЬНЫЕ ВОЗМОЖНОСТИ MICROSOFT EXCEL
Чтобы можно было исследовать большие объемы данных на листах, Office Excel 2010 поддерживает листы размером до одного миллиона строк и 16-ти тысяч столбцов. Так сетка Office Excel 2007 состоит из 1 048 576 строк и 16 384 столбцов, что обеспечивает увеличение числа строк на 1500% и столбцов на 6300% по сравнению с приложением Microsoft Office Excel 2003. И вот почему: нумерация столбцов теперь оканчивается на XFD вместо IV.
5
Основы работы с табличным процессором Microsoft Excel»
Вместо четырех тысяч разных способов форматирования ячеек теперь их можно использовать неограниченное количество, а максимальное число ссылок на ячейки, составлявшее ранее восемь тысяч, теперь ограничивается только объемом доступной памяти.
Указанный выше максимальный размер таблицы – 1 048 576 строк и 16 384 столбцов соответствует стандартному размеру «листа» рабочего документа – «книги» Microsoft Excel. «Книга», которая может состоять из большого множества листов, – объект файлового уровня в среде Microsoft Windows. Файлы электронных таблиц Microsoft Excel имеют стандартное расширение xlsx.
В отличие от вычисляемых таблиц, которые могут быть созданы, например, в текстовом процессоре Microsoft Word, в электронных таблицах Microsoft Excel могут производиться вычисления с использованием сложных функций, в том числе специальных и специального (предметно-профильного) назначения (например, бухгалтерских, финансовых, статистических).
Технология создания электронной таблицы – последовательное заполнение ячеек, в два шага
– сначала ячейка назначается (активируется), затем в нее вводится информация (с клавиатуры или при помощи действий с мышью). При активации следующей ячейки, информация автоматически заносится в предыдущую. Другая стандартная возможность – копирование и вставка информации из других ячеек.
Интерфейсом Microsoft Excel предусмотрен широкий спектр инструментов для форматирования информации в электронных таблицах – пользователь имеет возможность определять (назначать) тип и формат данных в ячейках и группах ячеек, задавать границы областей таблиц, выделять символы цветом и назначать цвет фона ячейкам. Предусмотрена операция объединения ячеек (для создания таблиц сложной структуры), перенос текста внутри ячейки, его разворот под произвольным углом и т.п.
Помимо возможности ввода в ячейки таблицы простейших формул (выражений, запись которых начинается с символа =, знака плюс или знака минус) с клавиатуры, Microsoft Excel дает возможность производить вычисления и логическую обработку информации с помощью встроенного аппарата мастера функций.
Мастер функций – структурированная по категориям функций библиотека вычислительных подпрограмм-процедур. Имеются следующие основные категории функций: математические, финансовые, статистические, текстовые и логические функции, а также функции для работы с базами данных, со ссылками и массивами данных, со значениями даты и времени, функции для проверки свойств и значений информации в ячейках.
В Microsoft Excel реализованы возможности как двумерной, так и трехмерной графики. Многообразие встроенных типов диаграмм, средств для их оформления, а также возможность создания пользователем новых типов диаграмм – все это позволяет использовать данный инструмент для иллюстрации данных различного типа и различных категорий.
Средства табличного процессора Microsoft Excel для работы со встроенными объектами позволяют придать таблицам и диаграммам профессиональный внешний вид – дополнить их граничными линиями, соединительными и фигурными стрелками и т.п., снабдить комментариями в виде вынесенных текстовых полей.
Имеется возможность использовать встроенные приложения Microsoft Excel для создания схематических и организационных диаграмм, для вставки в документы (книги Excel) графических изображений различного типа – формул, фотографий, видеоклипов.
6
Основы работы с табличным процессором Microsoft Excel»
1.4.ЭЛЕМЕНТЫ ИНТЕРФЕЙСА MICROSOFT EXCEL 2010. ОСНОВНЫЕ ПОНЯТИЯ И ОПРЕДЕЛЕНИЯ
На рис. ниже приведен интерфейс приложения Microsoft Excel 2010. Верхней строкой окна программы является строка заголовка. Здесь находится название табличного процессора,
пиктограмма системного меню и кнопки управления размерами окна (
Свернуть,
Развернуть или 
Свернуть в окно, 
Закрыть). А еще наименование новой рабочей книги – Книга 1, которая автоматически создается при запуске программы. Рабочая книга – это основной документ, используемый Microsoft Excel для хранения и обработки данных. Рабочая книга состоит из отдельных рабочих листов, каждый из которых имеет табличную структуру. По умолчанию для Microsoft Excel их три. Однако количество рабочих листов в рабочей книге может регулироваться по желанию пользователя.
В верхней левой части расположена панель быстрого доступа. Панель быстрого доступа является настраиваемой и содержит набор команд, не зависящих от вкладки, отображаемой в данный момент на ленте. Можно перемещать панель быстрого доступа в одно из двух возможных местоположений, а также добавлять на панель кнопки, выполняющие команды.
Ниже строки заголовка размещена лента Excel, содержащая вкладки: Файл, Главная,
Вставка, Разметка страницы, Формулы, Данные, Рецензирование, Вид, Надстройки.
Чтобы увеличить рабочую область, ленту можно свернуть. Кроме стандартного набора вкладок, которые отображаются на «ленте» при запуске Office Excel 2010, имеются вкладки еще двух типов, которые отображаются в интерфейсе в зависимости от выполняемой задачи. Вкладки появляются, когда они нужны для работы. Каждая вкладка, в свою очередь, состоит из нескольких групп взаимосвязанных элементов управления, реализующих те или иные операции Word. Группы на каждой вкладке разбивают задачу на ее составляющие. Кнопки команд в каждой группе служат для выполнения команд или отображения меню команд.
7
Основы работы с табличным процессором Microsoft Excel»
Строка |
|
Лента Excel |
кнопка |
Кнопка |
кнопка |
|
|
Свернуть |
Развернуть |
Закрыть |
|
заголовка |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
поле имени |
строка |
вертикальная |
|
полоса прокрутки |
||
текущая |
Название |
формул |
|
группы |
|
|
|
ячейка |
|
|
|
|
|
|
заголовки
столбцов
заголовки
строк
горизонтальная полоса прокрутки
кнопки |
ярлычки рабочих |
|
маркер |
|
|
|
|
строка |
Режимы |
||||
|
|
|||||
прокрутки |
листов |
|
разбиения |
|
||
ярлычков |
|
|
ярлычков |
|
состояния |
работы |
|
||||||
|
|
|
|
|
|
|
РАБОЧЕЕ ОКНО MICROSOFT EXCEL 2010
В строке вкладок тоже есть кнопки управления размерами окна (Свернуть окно,
Восстановить окно, Закрыть окно), но уже для окна текущей рабочей книги. Инструменты на вкладках ленты встречаются двух типов – кнопки и поля со списками. Щелчок мыши по кнопке приводит к выполнению соответствующей операции. При работе с полями нужно сначала щелкнуть по кнопке раскрывающегося списка, а затем выбрать нужный элемент списка. Набор кнопок на каждой панели инструментов стандартный, но может быть изменен согласно желанию. Это означает, что пользователь может удалять любые кнопки на панелях или размещать новые, но стандартные наборы всегда восстанавливаются при новой установке этих панелей на экран.
Основную часть экрана занимает рабочая область. Она организована в виде электронной таблицы, состоящей из – 1 048 576 строк и 16 384 столбцов, на пересечении которых образуются ячейки.
8
Основы работы с табличным процессором Microsoft Excel»
Ячейка – элементарный объект (минимальный элемент) электронной таблицы, расположенный на пересечении строк и столбцов, используемый для хранения данных.
Поскольку ячейка расположена в определенном столбце и определенной строке таблицы, то чтобы не запутаться в бесконечном множестве ячеек, каждому столбцу и каждой строке присвоен свой заголовок. Столбцы обычно обозначаются латинскими буквами: A, B, C, D, … , АА, АВ, …, а строки числами от 1 до 1 048 576, хотя иногда используется и другой стиль ссылок, когда и строки и столбцы нумеруются числами. Таким образом, каждая ячейка имеет свое обозначение, в котором указывается соответствующий столбец и строка и которое используется как адрес ячейки или, иначе говоря, как ссылка на ячейку, например, А1, В5,
АА2.
Строка, расположенная между лентой и заголовками столбцов, состоит из двух частей: поля имени и строки формул. Поле имени содержит адрес текущей (активной) ячейки. Текущая ячейка помечена указателем. На рис. 1 текущей является ячейка А1. По умолчанию ввод данных, формул и многие другие действия относятся к текущей ячейке. Указатель ячейки – светящуюся рамку вокруг ячейки – часто называют табличным курсором.
Строка формул используется для ввода и редактирования данных или формул в ячейках рабочего листа. В ней всегда отображается содержимое текущей ячейки.
Справа и внизу рабочая область обрамлена вертикальной и горизонтальной полосами прокрутки, которые предназначены для перемещения по строкам и столбцам рабочего листа. Прокрутка электронной таблицы вверх/вниз по одной строке или вправо/влево по одному столбцу осуществляется с помощью кнопок со стрелками в начале или в конце полос прокрутки. Для быстрой прокрутки таблицы можно использовать бегунок. В табл. 1 представлена подробная информация об основных приемах работы с полосами прокрутки.
ТАБЛИЦА 1 |
|
|
|
|
|
|
|
|
|
|
|
|
Прокрутка |
электронной |
таблицы |
Щелчок мышью по кнопкам со |
|
стрелко |
|||||
|
вверх/вниз по одной строке |
|
вверх/вниз в начале или в конце вертикально |
||||||||
|
|
|
|
|
полосы прокрутки |
|
|
|
|
|
|
|
Прокрутка |
электронной |
таблицы |
Щелчок мышью по кнопкам со |
|
стрелко |
|||||
|
влево/вправо по одному столбцу |
влево/вправо |
в |
начале |
или |
в |
конц |
||||
|
|
|
|
|
горизонтальной полосы прокрутки |
|
|
||||
|
Прокрутка |
электронной |
таблицы |
Щелчок мышью по вертикальной полос |
|||||||
|
вверх/вниз на одно окно |
|
прокрутки выше или ниже бегунка |
|
|
||||||
|
Прокрутка |
электронной |
таблицы |
Щелчок мышью по горизонтальной полос |
|||||||
|
влево/вправо на одно окно |
|
прокрутки слева или справа бегунка |
|
|
||||||
|
Быстрая |
прокрутка |
электронной |
Протащить |
бегунок |
вертикальной |
|
полос |
|||
|
таблицы вверх/вниз |
|
|
прокрутки вверх/вниз |
|
|
|
|
|||
|
Быстрая |
прокрутка |
электронной |
Протащить |
бегунок |
горизонтальной |
полос |
||||
|
таблицы влево/вправо |
|
|
прокрутки влево/вправо |
|
|
|
|
|||
|
Быстрый |
переход |
к |
последней |
Протащить |
бегунок |
вертикальной |
|
полос |
||
|
использовавшейся строке электронной |
прокрутки в крайнее нижнее положение |
|
||||||||
|
таблицы |
|
|
|
|
|
|
|
|
|
|
|
Быстрый |
переход |
к |
последнему |
Протащить |
бегунок |
горизонтальной |
полос |
|||
|
использовавшемуся |
|
столбцу |
прокрутки в крайнее правое положение |
|
|
|||||
|
электронной таблицы |
|
|
|
|
|
|
|
|
|
|
Перед горизонтальной полосой прокрутки расположено три типа элементов окна: ярлычки рабочих листов, кнопки прокрутки ярлычков и маркер разбиения ярлычков. Ярлычки
9
Основы работы с табличным процессором Microsoft Excel»
используются для быстрого переключения между рабочими листами. На экране всегда виден один рабочий лист – текущий. Ярлычок текущего рабочего листа всегда подсвечен. Переход к другому листу осуществляется по щелчку мыши на его ярлычке. Перед ярлычками находятся четыре кнопке прокрутки ярлычков (
). Они помогают отыскать нужный рабочий лист в том случае, когда рабочая книга пользователя содержит больше листов, чем может уместиться в области, отведенной на их отображение. Внешние кнопки осуществляют прокрутку к первому и последнему листу, внутренние – к предыдущему и к последующему соответственно. Маркер разбиения ярлычков регулирует размеры поля, выделенного на ярлычки рабочих листов. Перемещения маркера разбиения влево приводит к увеличению длины горизонтальной полосы прокрутки, а вправо – к увеличению количества одновременно выводимых ярлычков.
Последняя строка окна электронной таблицы – строка состояния. В ней отображается краткая информация о текущем состоянии данной программы, а также о включении некоторых режимов (Обычный, Разметка страницы, Страничный). Правее расположена шкала масштаба отображения документа. После завершения загрузки Microsoft Excel в строке состояния отобразится сообщение Готово, а в процессе работы здесь будут появляться краткие комментарии, подсказки или описания производимых операций.
1.5. ХАРАКТЕРИСТИКА ТИПОВ ДАННЫХ: ТЕКСТ, ЧИСЛО, ФОРМУЛА
Каждую ячейку электронной таблицы можно заполнить различного типа данными, которые в дальнейшем могут не только храниться, но и обрабатываться при помощи табличного процессора. Как уже было сказано выше, первичным элементом таблицы Microsoft Excel является ячейка. Существует три типа данных, которые можно ввести в ячейку рабочего листа электронной таблицы: текст, числовые данные и формулы. Правда для того, чтобы вводимая пользователем информация обрабатывалась корректно, необходимо указать формат ячейки. В некоторых случаях формат ячейки определяется автоматически по виду вводимой информации, но чаще задается пользователем. В табл. 2 содержится перечень форматов числовых данных, используемых табличным процессором.
ТАБЛИЦА 2
Общий
Числовой
Денежный
Финансовый
Дата
При вводе чисел этот формат используется по умолчанию. В большинстве случаев числа, имеющие формат Общий, отображаются так, как вводятся. Однако если ширины ячейки недостаточно для отображения всего числа, то числа в формате Общий округляются. Для чисел, содержащих более 12, знаков в формате Общий используется научное (экспоненциальное) представление.
Этот формат используется как основной для отображения чисел. Можно задать количество отображаемых знаков после запятой, применение разделителя групп разрядов и способ отображения отрицательных чисел.
Этот формат используется для денежных значений и отображает рядом с числом обозначение денежной единицы по умолчанию. Можно задать количество отображаемых знаков после запятой, применение разделителя групп разрядов и способ отображения отрицательных чисел.
Этот формат также используется для денежных значений, но он выравнивает в столбце обозначения денежных единиц и десятичные разделители.
Этот формат отображает числовые представления даты и времени как значения даты в соответствии с заданным типом и языком (местоположением). Форматы даты, начинающиеся со звездочки (*),
10