Файл: 2_MS_Excel.pdf

РОССИЙСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ ИННОВАЦИОННЫХ ТЕХНОЛОГИЙ И ПРЕДПРИНИМАТЕЛЬСТВА кафедра «Прикладная информатика»

РАЗДЕЛ 2. Работа с табличным процессором Microsoft Excel 2003

Общие сведения о табличном процессоре Microsoft Excel

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

электронными таблицами.

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

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

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

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

Основные возможности Excel:

–редактирование и форматирование текстовых, числовых данных и формул;

–проведение однотипных расчетов над большими наборами данных;

–автоматизация итоговых вычислений;

–использование функций различных категорий;

–решение задач путем подбора значений параметров, табулирования функций;

–обработка результатов экспериментов;

–подготовка табличных документов;

–построение диаграмм и графиков по имеющимся данным;

–интеграция программ MS Office;

–установка защиты;

–создание сводных таблиц;

–построение сводных диаграмм;

–консолидирование данных из разных таблиц;

–структурирование и отбор данных.

РОССИЙСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ ИННОВАЦИОННЫХ ТЕХНОЛОГИЙ И ПРЕДПРИНИМАТЕЛЬСТВА кафедра «Прикладная информатика»

Глоссарий

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

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

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

Легенда – условные обозначения значений различных рядов данных на диаграмме..

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

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

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

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

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

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

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

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

Относительная ссылка указывает на ячейку, основываясь на ее положении относительно ячейки, в которой находится формула, например «на две строки выше». При перемещении формулы относительная ссылка изменяется, ориентируясь на ту позицию, в которую переносится формула. Например, если в клетке С1 записана формула =А1+В1, то при копировании ее в клетку С2 формула будет иметь следующие относительные ссылки =А2+В2; при копировании в D1 — =В1+С1.

РОССИЙСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ ИННОВАЦИОННЫХ ТЕХНОЛОГИЙ И ПРЕДПРИНИМАТЕЛЬСТВА кафедра «Прикладная информатика»

Абсолютными являются ссылки на ячейки, имеющие фиксированное расположение на листе. Эти ссылки не изменяются при копировании формул. Абсолютная ссылка содержит знак $ перед именем столбца и именем строки.

Смешанные ссылки являются комбинацией относительных и абсолютных. Например, фиксированный столбец и относительная строка: $D7.

Формулы – выражения, по которым выполняются вычисления на листе. Формула начинается со знака равенства (=). Формула может содержать числовые константы, ссылки на ячейки и функции Excel, соединенные знаками математических операций.

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

Строка формул – панель в верхней части окна MS Excel, которая используется для ввода или изменения значений или формул в ячейках или на диаграммах. На ней отображается константа или формула, содержащаяся в активной ячейке. Редактировать содержимое ячейки можно непосредственно в самой ячейке или в поле содержимого ячейки. В первом случае надо выполнить двойной щелчок по ячейке или нажать клавишу F2, во втором — активизировать ячейку и щелкнуть в поле содержимого строки формул.

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

Запись базы данных - строка списка. Поле базы данных - столбец списка.

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

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

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

Консолидация – операция объединения значений из нескольких диапазонов данных. Консолидировать данные в MS Excel можно несколькими способами.

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

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

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

РОССИЙСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ ИННОВАЦИОННЫХ ТЕХНОЛОГИЙ И ПРЕДПРИНИМАТЕЛЬСТВА кафедра «Прикладная информатика»

Основные приемы работы с Microsoft Excel

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

MS Excel может быть запущен как непосредственно:

через пункт главного меню: Пуск → Программы → Microsoft Office → Microsoft Office

Excel 2003

запуском исполнимого файла EXCEL.EXE или ссылающегося на него ярлыка

так и опосредованно:

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

После запуска программы на экране появляется окно, основными элементами ко¬торого являются: строка заголовка, панель меню, панели инструментов, область задач, линейки, полосы прокрутки, строка состояния, заголовки строк и столбцов, строка формул, ярлычки листов (Рисунок 2.1). Excel имеет стандартный интерфейс Windows. На рисунке 2.1. изображено окно программы и обозначены только те элементы рабочего окна, которые отличают Excel от Word.

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

На панели форматирования следует отметить кнопку "Объединить и поместить в центре", а

также "Денежный формат" и "Процентный формат".

Рис. 2.1: Структура окна MS Excel

РОССИЙСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ ИННОВАЦИОННЫХ ТЕХНОЛОГИЙ И ПРЕДПРИНИМАТЕЛЬСТВА кафедра «Прикладная информатика»

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

Рабочий лист состоит из строк и столбцов. Столбцы озаглавлены прописными латинскими буквами или парами букв. Всего рабочий лист может содержать до 256 столбцов, пронумерованных от A до IV. Строки последовательно нумеруются цифрами, от 1 до 65 536 (максимально допустимый номер строки).

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

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

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

Отдельная ячейка может содержать данные, относящиеся к одному из трех типов: текст, число или формула – или оставаться пустой. MS Excel при сохранении рабочей книги записывает в файл только прямоугольную область рабочих листов, примыкающую к левому верхнему углу (ячейка A1) и содержащую все заполненные ячейки. Тип данных, размещаемых в ячейке, определяется автоматически при вводе. Если эти данные можно интерпретировать как число, MS Excel так и делает. В противном случае данные рассматриваются как текст. Ввод формулы всегда начинается с символа "=" (знак равенства).

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

РОССИЙСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ ИННОВАЦИОННЫХ ТЕХНОЛОГИЙ И ПРЕДПРИНИМАТЕЛЬСТВА кафедра «Прикладная информатика»

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

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

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

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

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

При абсолютной адресации адреса ссылок при копировании не изменяются, так что ячейка, на которую указывает ссылка, рассматривается как нетабличная. Для изменения способа адресации при редактировании формулы надо выделить ссылку на ячейку и нажать клавишу F4. Элементы номера ячейки, использующие абсолютную адресацию, предваряются символом $. Например, при последовательных нажатиях клавиши F4 номер ячейки A1 будет записываться как A1, $A$1, A$1 и $A1. В двух последних случаях один из компонентов номера ячейки рассматривается как абсолютный, а другой – как относительный (смешанная адресация).

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

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