Данные, хранимые в ячейках электронной таблицы

НОВОУРАЛЬСКИЙ ГОСУДАРСТВЕННЫЙ ТЕХНОЛОГИЧЕСКИЙ ИНСТИТУТ

Кафедра информатики и программирования

ЭЛЕКТРОННАЯ ТАБЛИЦА

MICROSOFT EXCEL

Учебно-методическое пособие по курсу «Информатика»

для всех специальностей очной формы обучения

Новоуральск 2002

Автор Николаев Николай Александрович

Рецензент к.т.н., доцент Дюгай Павел Алексеевич

Учебно-методическое пособие по курсу «Информатика» для всех специальностей очной формы обучения.

Новоуральск, НГТИ, 2002, 51с.

Пособие представляет собой описание основных принципов работы с электронными таблицами Microsoft EXCEL.

Содержит 27 рисунков, 4 таблицы, 7 библиографических названий.

Пособие может использоваться при самостоятельном изучении электронных таблиц Microsoft EXCEL.

Методическое пособие рассмотрено на заседании кафедры

Протокол № 43 от 27 мая 2002 г.

Зав.кафедрой Н.А.Николаев

СОГЛАСОВАНО:

Председатель методкомиссии НГТИ

д.т.н., профессор Беляев А.Е.

СОДЕРЖАНИЕ

ВВЕДЕНИЕ
1 ОСНОВНЫЕ ПОНЯТИЯ ЭЛЕКТРОННОЙ ТАБЛИЦЫ
2 ДАННЫЕ, ХРАНИМЫЕ В ЯЧЕЙКАХ ЭЛЕКТРОННОЙ ТАБЛИЦЫ
3 ВВОД И РЕДАКТИРОВАНИЕ ДАННЫХ 3.1 Общие правила ввода данных в ячейки 3.2 Ввод чисел 3.3 Ввод текста 3.4 Ввод дат и времени 3.5 Редактирование данных Копирование и перемещение данных
4 ИЗМЕНЕНИЕ ФОРМАТА ТАБЛИЦЫ 4.1 Добавление и удаление строк, столбцов 4.2 Изменение ширины столбцов и высоты строк 4.3 Изменение формата числовых данных 4.4 Выравнивание данных в ячейках 4.5 Границы, рамки, цвет заливки 4.6 Условное форматирование
5 ФОРМУЛЫ 5.1 Общие понятия о формулах 5.2 Ввод данных и формул 5.3 Копирование формул 5.4 Перемещение формул и данных
6 ИСПОЛЬЗОВАНИЕ ВСТРОЕННЫХ ФУНКЦИЙ EXCEL 6.1 Основные понятия 6.2 Обзор списка встроенных функций 6.2.1 Функции даты и времен 6.2.2 Логические функции 6.2.3 Арифметические и тригонометрические функции 6.2.4 Статистические функции 6.3 Панель формул
7 АВТОМАТИЗАЦИЯ ВВОДА
8 ПРИМЕР ИСПОЛЬЗОВАНИЯ EXCEL
8.1 Создание листа «Расходы»
8.2 Форматирование ячеек
8.3 Диаграммы
9 ЗАДАНИЯ ДЛЯ ЛАБОРАТОРНОЙ РАБОТЫ
9.1 Создание платежной ведомости
9.2 Работа с формулами 9.3 Расчет стоимости 9.4 Форматирование, построение диаграмм
ЛИТЕРАТУРА

ВВЕДЕНИЕ

Электронные таблицы - это русский вариант англоязычного тер­мина spreadsheets (развернутые таблицы). Этот термин появился сравнительно недавно – в 1979 году, однако он уже широко применяется пользователями персональных компьюте­ров и трактуется ими достаточно однозначно.

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

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

- программы (или пакета программ) для обработки таких дан­ных.

Достаточно прозрачны причины появления и высокой популяр­ности таких программных средств.

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

- данные размещаются в таблице на бумаге, а обработка их про­изводится с помощью электронного калькулятора;

- данные размещены в компьютере, а для их обработки применя­ется один из языков программирования, например, BASIC или PASCAL.

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

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

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

Пользователи EXCEL могут получить информацию об этой программе разными способами, например, прочтя толстое руководство или просмотрев встроенный справочник. Данное пособие предназначено для начинающего пользователя-студента, желающего как можно быстрее разобраться в ее особенностях в объеме, соответствующем учебным программам курса “Информатика” для очной формы обучения НГТИ.

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

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

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

Пособие можно просто читать, но автор рекомендует работать с EXCEL за компьютером. В последнем разделе работы приведены задания для студентов, выполняющих лабораторные работы по разделу EXCEL курса “Информатика”.

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

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

Таблица 1 - Система обозначений действий пользователя.

Используемое обозначение	Выполняемое действие
Ø Название команды	Выбор в текущем меню команды с указанным названием, то есть либо позиционирование подсветки на указанной команде с помощью клавиш управления курсором и нажатие клавиши <Enter>, либо позиционирование указателя мыши на указанной команде и однократное нажатие левой кнопки мыши.
q Название кнопки	Нажатие кнопки с указанным названием в активном диалоговом окне, то есть либо позиционирование выделяющей рамки на указанной кнопке с помощью клавиши <Tab> и нажатие клавиши <Enter>, либо позиционирование указателя мыши на указанной кнопке и однократное нажатие левой кнопки мыши.
ò Название раскрывающегося списка или палитры	Развертывание раскрывающегося списка или палитры, то есть позиционирование указателя мыши на кнопке раскрытия списка (кнопка со стрелкой, направленной вниз) и однократное нажатие левой кнопки мыши.
ö Фрагмент текста, ячейка или диапазон ячеек, элемент списка или раскрывающегося списка	Выделение указанного фрагмента текста, диапазона ячеек или элемента списка, то есть его выделение либо с помощью клавиш управления курсором при нажатой и удерживаемой клавише <Shift>, либо при помощи мыши при нажатой и удерживаемой левой кнопке мыши.
Название вкладки	Раскрытие вкладки с указанным названием в активном диалоговом окне, то есть либо нажатие на клавиатуре клавиши, соответствующей подчеркнутой букве названия вкладки на ярлычке вкладки, либо позиционирование указателя мыши на указанном ярлычке и однократное нажатие левой кнопки мыши.
<Клавиша>	Нажатие соответствующей клавиши на клавиатуре.
<Клавша1>+<Клавиша2>	Нажатие Клавиши1 и нажатие Клавиши2 при удерживаемой Клавише1.
<Клавиша1>,<Клавиша2>	Последовательное нажатие двух клавиш на клавиатуре

1 ОСНОВНЫЕ ПОНЯТИЯ ЭЛЕКТРОННОЙ ТАБЛИЦЫ

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

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

Рис 1.1 - Интерфейс электронной таблицы EXCEL.

Документ EXCEL называется рабочей книгой. Рабочая книга представляет собой набор рабочих листов, максимальное число которых в книге – 256. В окне документа отображается только текущий рабочий лист, с которым и ведется работа. Каждый рабочий лист имеет имя, которое отображается на корешке листа, расположенном в его нижней части. С помощью корешков можно переходить к другим рабочим листам, входящим в ту же самую рабочую книгу. Чтобы переименовать рабочий лист, нужно дважды щелкнуть на его корешке, ввести новое название и нажать клавишу <Enter>.

Рабочая область электронной таблицы состоит из строк и столбцов, имеющих свои имена. Имена строк — это их номера. Нумерация строк начинается с 1 и заканчивается максимальным числом, установленным для данной программы (65536 для EXCEL 97). Имена столбцов — это буквы латинского алфавита сначала от А до Z, затем от АА до AZ, ВА до BZ и т. д. , всего 256 столбцов.

Имя столбца и номер строки в совокупности однозначно идентифицируют ячейку, которая им одновременно принадлежит. Этот идентификатор называется адресом ячейки или ссылкой на ячейку, например, ячейка на рис.1.1, выделенная рамочкой (активная ячейка), имеет адрес С4. Ячейкам, кроме того, можно присваивать собственные имена и использовать эти имена для ссылок на ячейки наряду с адресами.

Кроме понятия ячейки, используется понятие диапазона (или интервала) ячеек — прямоугольной области смежных ячеек. Диапазон задается указанием адреса верхней левой ячейки и правой нижней ячейки, разделенных символом : (двоеточие). Например, запись E5:G10 определяет диапазон ячеек, выделенных на рис.1.1 серым цветом.

По умолчанию, адрес ячейки или диапазона, записанный в таком виде, определяет область на текущем листе. Чтобы обратиться к данным на другом листе, перед адресом нужно поставить имя листа и восклицательный знак, например, Лист3!С5 определяет ячейку на лист с именем Лист3, а МАРТ!D5:E8 – диапазон на листе с именем МАРТ.

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

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

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

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

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

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

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

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

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

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

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

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

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

ДАННЫЕ, ХРАНИМЫЕ В ЯЧЕЙКАХ ЭЛЕКТРОННОЙ ТАБЛИЦЫ

Типы входных данных

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

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

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

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

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

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

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

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

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

Формат с фиксированным количеством десятичных знаков обеспечивает представление чисел в ячейках с заданной точностью, определяемой ус­тановленным пользователем количеством десятичных знаков после запятой (десятич­ной точки). Например, если установлен режим форматирования, включающий два десятичных знака, то вводимое в ячейку число 12345 будет записано как 12345.00, а число 0.12345 — как 0.12.

Процентный формат обеспечивает представление введенных данных в форме процентов со знаком % (в соответствии с установленным количеством десятич­ных знаков). Например, если установлена точность в один десятичный знак, то при вводе 0.123 на экране появится 12.3%, а при вводе 123 — 12300.0%.

Денежный формат обеспечивает такое представление чисел, где каждые три разряда разделены пробелом, а в конце числа добавляется р. При этом пользователем может быть установлена опреде­ленная точность представления (с округлением до целого числа или в два десятичных знака). Например, введенное число 12345 будет записано в ячейке как 12 345р. (с округлением до целого числа) и 12 345.00р. (с точностью до двух десятичных знаков).

Научный формат, используемый для представления очень больших или очень маленьких чисел, обеспечивает представление вводимых чисел в виде двух компонен­тов:

— мантиссы, имеющей один десятичный разряд слева от десятичной точки, и некото­рого (определяемого точностью, заданной пользователем) количества десятичных знаков справа от нее;

— порядка числа.

Пример.Введенное число 12345 будет записано в ячейке как 1.2345Е+04 (если установленная точность составляет 4 разряда) и как 1.23Е+04 (при точности в 2 раз­ряда). Число .0000012 в научном формате будет иметь вид 1.2Е-06.

• Формулы. Видимое на экране содержимое ячейки, возможно, — результат вычисле­ний, произведенных по имеющейся, но не видимой в ней формуле. Формула всегда должна начинаться со знака = и может включать ряд арифметических, логических и прочих действий, производимых с дан­ными из других ячеек. Например, предположим, что в ячейке находится формула =В5+С5+2*Е5. В обычном режиме отображения таблицы на экране вы увидите не формулу, а результат вычислений по ней над числами, содержащимися в ячейках В5, С5 и Е5.

Функция представляет собой программу с уникальным именем, для кото­рой пользователь должен задать конкретные значения аргументов функции, стоящих в скобках после ее имени. Функцию (так же, как и число) можно считать частным случаем формулы. Различают статистические, логические, финансовые, математические и другие функции. Например, если ячейка содержит функцию =СУММ(В4:В6), то в ячейке будет отображаться сумма чисел, находящихся в ячейках В4, В5, В6.

• Даты. Особым типом входных данных являются даты. Этот тип данных обеспечива­ет выполнение таких функций, как добавление к дате числа (пересчет даты вперед и назад) или вычисление разности двух дат (длительности периода). Даты имеют и внешний формат. Во внутреннем формате дата выражается количеством дней от 1 января 1900 года, так, например, во внутреннем формате 25 января 1900 года будет представляться просто числом 25. Внешний формат используется для ввода и отображения дат. Наиболее употребительны следую­щие типы внешних форматов дат:

— ДД/ММ/ГГ (04/02/95)

— ДД.ММ.ГГ (04.02.95)

— ДД МММ ГГ (04 янв 95)

— ДД МММ ГГГГ (04 янв 1995)