Глава 10. ЭЛЕКТРОННЫЕ ТАБЛИЦЫ
Содержание главы
| 10.1 Введение в электронные таблицы 10.2 Электронные таблицы 10.3 Использование ссылок 10.4 Как подготовить простую таблицу 10.5 Графики и диаграммы 10.6 Книги и листы |
Основные понятия:
Электронные таблицы (динамические таблицы), или табличные процессоры - это компьютерные программы, предназначенные для экономистов, бухгалтеров, инженеров, научных сотрудников - всех тех, кому приходится работать с большими массивами табличной информации и их обработкой.
10.1 Введение в электронные таблицы
Довольно часто обрабатываемую информацию нам приходится представлять в виде таблиц. При этом часть ячеек таблицы содержит исходную или первичную информацию, а часть — производную. Производная информация является результатом различных арифметических и иных операций, совершаемых над первичными данными.
Например в результате физического эксперимента произведено 15 измерений напряжения и силы тока в электрической цепи при различном сопротивлении. Сопротивление в цепи изменялось с помощью реостата. Необходимо рассчитать величину сопротивления для каждого измерения.
Очевидно, что при большом количестве экспериментов результаты целесообразно представить в виде таблицы, макет которой показан ниже.
Результаты физического эксперимента
Напряжение B | Сопротивление 0м | Сила тока А |
В таблице напряжение (U) и сопротивление (R) являются первичной информацией, а сила тока (I)– производная информация, которая рассчитывается по закону Ома:
I = U/ R.
Представление данных в виде таблицы существенно упрощает анализ информации. Например, большое количество первичных и производных данных приходится обрабатывать при различных учетных операциях, — при поступлении товаров в магазин, при проведении банковских операций с различными счетами и т. д. Поэтому автоматизация такого рода операций значительно повышает качество и эффективность расчетов.
|
Для решения задач, которые можно представить в виде таблиц, разработаны специальные пакеты программ, называемые электронными таблицами (ЭТ) или табличными процессорами. Электронные (динамические) таблицы или табличные процессоры позволяют обрабатывать числовую информацию с помощью компьютера, сохранять ее во внешних запоминающих устройствах (ВЗУ) и получать копии на бумаге. Электронные таблицы ориентированы прежде всего на решение экономических задач. Однако заложенные в них инструментальные средства позволяют успешно решать инженерные задачи, например, выполнять расчеты по формулам, строить графические зависимости.
Освоение работы электронных таблиц обеспечивает возможность самостоятельно решать различные задачи, не прибегая к услугам программиста. Создавая ту или иную таблицу, пользователь выполняет одновременно функции алгоритмиста, программиста и конечного пользователя. Это обеспечивает высокую эффективность эксплуатации программ, так как в них оперативно могут вноситься любые изменения, связанные с модернизацией алгоритма, перекомпоновкой таблицы и т. д.
Области применения электронных таблиц весьма велики. Таблицы используются при решении различных финансовых и бухгалтерских задач, например, при расчете заработной платы небольшой организации. Их можно применять при выполнении различных технико-экономических расчетов, — например, при оценке целесообразности строительства цеха. Наконец, электронные таблицы можно использовать в повседневной жизни для учета и анализа расходования средств: при ежедневной покупке продуктов и хозяйственных товаров, при оплате счетов и т. п. Табличные процессоры могут служить обычным калькулятором.
Электронные таблицы используются в компьютерной технологии около 15 лет. В 80-х годах в нашей стране большое распространение получили табличные процессоры Lotus 1-2-3 фирмы Lotus Development и SuperCalc фирмы Computer Associates, работавшие с MS-DOS в текстовом режиме. До сих пор на маломощных машинах используется русифицированная версия SuperCalc-4 под названием “Тагра”. На современных машинах чаще применяются более совершенные программы обработки электронных таблиц, работающие в графическом режиме под управлением операционной системы Microsoft® Windows, причем в нашей стране наиболее популярны QuattroPro фирмы Novell и особенно Microsoft® Excel фирмы Microsoft®. Табличный процессор имеется также в интегрированном пакете Microsoft® Works.
С перечисленными программами возможны следующие режимы работы: формирование таблицы, редактирование, вычисления по формулам, сохранение на ВЗУ, построение графиков и диаграмм, статистическая обработка элементов таблицы, упорядочение по признаку, работа с графикой, печать на бумагу.
К аппаратным средствам, поддерживающим работу программы электронных таблиц, относят ОЗУ, ВЗУ, клавиатуру, дисплей, принтер. Для работы с электронными таблицами целесообразно использовать большие мониторы с размером диагонали от 17’’ и выше. При работе табличного процессора все данные электронных таблиц находятся в ОЗУ, а математические расчеты в таблицах бывают довольно громоздки. Поэтому при работе с ЭТ требуется большего объёма ОЗУ и скорости процессора.
В расчетах по формулам можно использовать так называемые встроенные функции, например тригонометрические, алгебраические и другие (их более 400), что обеспечивает широкий диапазон применения электронных таблиц.
Важно, что программа позволяет хранить в табличной форме не только большое количество исходных данных, результатов расчетов, но и связи (математические соотношения) между ними.
Программа допускает копирование и перенос содержимого ячеек, получение усредненных значений. Возможна одновременная работа с несколькими таблицами, их анализ по единому признаку, атрибуту.
10.2 Электронные таблицы
Объект нашего изучения – программа Microsoft® Excel, предназначенная для обработки электронных таблиц. Необходимо иметь в виду две особенности:
1) техника обработки электронных таблиц в Excel в целом тесно связана с общими подходами Windows, а в частности — с технологией создания текстовых документов Microsoft® Word. Поэтому к освоению материала надо иметь навыки работы в Microsoft® Windows;
2) Ограничим изучение лишь принципиально важных идей и приемов работы с электронными таблицами. Более или менее полное изучение возможностей мощной профессиональной среды Excel выходит за рамки нашего пособия.
Что такое Microsoft® Excel version 7.0
Программа Excel 7.0 входит в офисный пакет Microsoft® Office и предназначена для подготовки и обработки электронных таблиц под управлением операционной системы Windows.
Документом (т. е. объектом обработки) Excel является файл с произвольным именем и расширением. XLS. В терминах Excel такой файл называется рабочей книгой (Workbook). В каждом файле XLS может размещаться от 1 до 255 электронных таблиц, каждая из которых называется рабочим листом (Sheet). Чтобы упростить изложение, будем считать, что вы работаете только с одним рабочим листом.
Что можно записать в ячейку таблицы?
В любую ячейку электронной таблицы можно ввести:
· число;
· формулу;
· текстовую (алфавитно-цифровую) информацию.
Как Excel отличает один тип данных от другого?
Число. Если вы набрали некую последовательность символов, в которую входят цифры, а также знаки “+”, “-” (в начале последовательности) или “.” (как разделитель целой и дробной части), эта последовательность считается числом. Примеры чисел:
257; -145.2; +4890.859.
По умолчанию после фиксации числа Excel сдвигает его к правой границе ячейки, однако вы можете выровнять его по центру ячейки или сместить к левой границе ячейки с помощью кнопок выравнивания.
Кроме того, вы можете записать число в экспоненциальной форме (в виде мантиссы и порядка). Например, число 48900 можно представить как 4.89Е-04.
Чтобы число в ячейке выглядело на экране именно так, как вы его ввели, количество вводимых знаков не должно превышать некоторую величину, зависящую от установленной вами (или по умолчанию) ширины столбца (обычно это 8-12 символов). Слишком большие или слишком малые числа Excel попытается представить в экспоненциальной форме (например, вместо в ячейке может появиться
4.89Е+07,
т. е. 4.89·107, а при дальнейшем увеличении количества знаков в числе Excel отобразит число как цепочку символов “#” (##...#). Точность числа (количество знаков после точки) можно регулировать с помощью кнопок панели инструментов “Форматирование” 
.
Формула. Если набранная вами последовательность символов начинается со знака “=” (равно). Excel считает, что вы набрали формулу. В формулу могут входить данные разного типа, однако мы будем считать ее обычным арифметическим выражением, в которое можно записать только числа, адреса ячеек и функции, соединенные между собой знаками арифметических операций. Например, если вы ввели в ячейку ВЗ формулу:
=A2+C3*F7,
значением этой ячейки будет число, которое равно произведению чисел, записанных в СЗ и F7, сложенному с числом из ячейки А2.
В зависимости от значения переключателя Formulas (Формулы) на вкладке Сервис команды [Параметры-Параметры страницы], в ячейке на экране может отображаться либо формула, либо результат вычислений по данной формуле.
Текст. Если набранная вами последовательность в представлении Excel не является ни числом, ни формулой, она считается текстом и не подвергается каким-либо преобразованиям. Например, последовательность -145.2 будет считаться числом, а -145,2 или vl45.2 - текстом. Последовательность =А1+А2 - это формула, а А1+А2 - текст.
Если вводимый в ячейку текст превысит по длине видимую ширину столбца, возможны два случая:
1) если следующие ячейки в строке пусты, — тогда визуально текст накроет эти ячейки;
2) если в следующих ячейках есть данные, — тогда правая часть текста скроется “под” этими ячейками.
10.3 Использование ссылок
Ссылкой однозначно определяется ячейка или группа ячеек листа. Ссылками указываются ячейки, значения которых нужно использовать в формуле. С помощью ссылок можно использовать в формуле данные, находящиеся в различных местах листа, а также использовать значение одной и той же ячейки в нескольких формулах. Кроме этого, можно ссылаться на ячейки, находящиеся на других листах книги или в другой книге, или на данные другого приложения. Ссылки на ячейки других книг называются внешними ссылками. Ссылки на данные в других приложениях называются удаленными ссылками.
В ссылках на ячейки используются обозначения соответствующих строк и столбцов листа. Такие ссылки называются ссылками A1. По умолчанию в Microsoft® Excel столбцы помечены латинскими буквами (A, B, C, D, ... Z; AA, AB, AC, AD, ... AZ; BA, BB, BC и т. д.), а строки пронумерованы. Можно заменить буквы столбцов на номера. Такие ссылки называются ссылками R1C1 (Row1-строка1 Column1-столбец1).
A | B | C | |
| |||
| |||
3 |
1
2Ссылка на диапазон ячеек B2:C3
Ссылка на ячейку А1
Относительные и абсолютные ссылки
Относительные ссылки используются в формуле для указания адреса ячейки в относительной системе координат с началом в текущей ячейке. Относительные ссылки имеют вид: A1, B2 и т. д..
Абсолютные ссылки используются в формуле для указания адреса ячейки в абсолютной системе координат не зависящего от координаты текущей ячейки. Абсолютные ссылки имеют вид: $A$1, $B$2 и т. д..
Различия между относительными ссылками и абсолютными ссылками проявляются при копировании формул из одной ячейки в другую. По умолчанию при наборе формул в Microsoft® Excel используются относительные ссылки.
При копировании абсолютные ссылки в формулах не изменяются, а относительные ссылки автоматически обновляются в зависимости от нового положения, причем при копировании ячейки с абсолютной ссылкой на n столбцов и m строк отстоящей от исходной относительная ссылка также сдвигается на n строк, m столбцов.
Абсолютные ссылки создаются путем добавления в контекст ссылки префикса абсолютного адреса “$”. Префикс ставится перед указателем на строку и столбец отдельно. Таким образом осуществляется возможность ссылаться на относительный столбец и абсолютную строку (и наоборот).
A | B | C | D | E | |
1 | |||||
| =B1 | =$B$1 | =C$1 | ||
3 | =D$1 | ||||
4 | =$B$1 | =C3 |
2
направление копирования
10.4 Как подготовить простую таблицу.
Общие правила подготовки таблицы.
Чтобы освоить на практике основные идеи обработки электронных таблиц, рассмотрим следующую задачу.
Пусть некая фирма, торгующая мороженым в городе N., ведет учет выручки (в миллионах рублей) по четырем округам города в летние месяцы 1997 г.
Исходные данные нашей задачи – двенадцать чисел, каждое из которых – выручка по конкретному округу (например, по Западному) за конкретный месяц (например, за июль).
На основании этих исходных данных мы хотим найти производные величины:
· сумму выручки по городу за каждый месяц; – сумму выручки по каждому округу за все лето; – общую сумму выручки;
· процент выручки по каждому округу относительно общей суммы.
Прежде всего введем в таблицу исходные данные. В ячейках А1 и А2 мы набрали тексты, которые представляют собой некую описательную информацию (“Продажа мороженого...”, “Лето 1996 г.”). В ячейках А6-А9 набраны названия округов, в ячейках С5 – Е5 – названия месяцев. Наконец, в ячейки С6, D6, ... мы ввели числа, которые и представляют собой исходные значения выручки. И наконец в ячейках С10, D10, E10. Теперь приступим к расчетам. В ячейку С10 необходимо поместить сумму за июнь по всем округам, т. е. С6+С7+С8+С9. Для этого надо выделить ячейку С10 и ввести в нее формулу: =С6+С7+С8+С9.
С10 |
| =С6+С7+С8+С9 | |||||||||||||
A | B | C | D | E | F | G | |||||||||
1 | Продажи мороженого по округам города N (млн. руб.) | ||||||||||||||
2 | Лето 1996 г. | ||||||||||||||
3 | |||||||||||||||
4 | |||||||||||||||
5 | Июнь | Июль | Август | ||||||||||||
6 | Центральный | 11 | 15 | 13 | |||||||||||
7 | Западный | 12 | 13 | 15 | |||||||||||
8 | Северный | 13 | 14 | 14 | |||||||||||
9 | Южный | 11 | 13 | 12 | |||||||||||
10 | 47 | 55 | 54 | ||||||||||||
11 | |||||||||||||||
После ввода этой формулы в ячейке немедленно появится результат. Ну, а если в вашей таблице не 4, а 444 строки? Перечислять все слагаемые? Разумеется, нет: в Excel имеется функция SUM (СУММ), которая возвращает значение суммы значений своих аргументов.
Примечание При вводе формулы можно использовать мастер функций. Мастер функций вызывается нажатием кнопки 
стандартной панели инструментов.
10.5 Графики и диаграммы
Создадим по нашей таблице график, который бы отображал информацию о сумме выручки от продажи мороженого по разным округам города.
Данные в электронных таблицах можно представить в виде графика или диаграммы. График отображает тенденции изменения данных за равные промежутки времени, тем самым график скорее подчеркивает скорость изменения величины, чем саму величину изменения. График является одним из типов диаграммы, рассмотрим все типы.
Типы диаграммы:
Заполненная
Заполненная диаграмма подчеркивает изменение общего количества в течении какого-то периода времени, показывая сумму введенных значений. Она также отображает вклад отдельных значений в общую сумму.
Полосковая
Полосковая диаграмма показывает показывает отдельные значения в определенный момент времени или отражает соотношение компонентов. Составная полосковая диаграмма (является подтипом полосковой) показывает соотношение отдельных элементов в целом.
Гистограмма
Гистограмма показывает изменения в течении некоторого периода времени или отражает соотношение величин.
Круговая и кольцевая
Круговая и кольцевая диаграммы показывают соотношение частей и целого. Кольцевая диаграмма похожа на круговую, но отличается тем, что может отображать не один ряд данных.
Точечная
Точечная диаграмма показывает взаимосвязь между числовыми значениями в нескольких рядах данных и представляет две группы чисел в виде одного ряда точек в координатах XY. Эта диаграмма отображает неравномерность интервалов и группировки данных и часто используется для представления данных научного характера.
Биржевая
Биржевая диаграмма часто используется для демонстрации цен на акции. Для того чтобы построить биржевую диаграмму, необходимо правильно организовать ряды данных.
Радиальная (лепестковая)
В радиальной диаграмме каждая категория имеет собственную ось координат, исходящую из начала координат. Линиями соединяются все значения из определенной серии. Радиальная диаграмма позволяет сравнить значения из нескольких наборов данных.
Поверхность
Поверхностная диаграмма используется для поиска наилучшего сочетания двух наборов данных. Как и на топографической карте, области с одним значением (высотой) выделяются одинаковым цветом и узором.
10.6 Книги и листы
Книга в Microsoft® Excel представляет собой файл, используемый для обработки и хранения данных. Каждая книга может состоять из нескольких листов, поэтому в одном файле можно поместить разнообразные сведения и установить между ними необходимые связи.
Листы служат для организации и анализа данных. Одновременно на нескольких листах данные можно вводить, править, производить с ними вычисления. В книгу можно вставить листы диаграмм для графического представления данных и модули для создания и хранения макросов, используемых при выполнении специальных задач.
Имена листов находятся на ярлычках, расположенных в нижней части окна книги. Для перехода с одного листа на другой необходимо указать соответствующий ярлычок. Название текущего листа всегда выделено жирным шрифтом. Листы можно переименовывать, вставлять, удалять, перемещать или копировать в пределах одной книги или из одной книги в другую.
Контрольные вопросы
Вопросы и задания по теме: “Представление информации в табличной форме”.
1. Чем отличается производная информация от первичной или исходной?
2. Какие преимущества может дать обработка информации с помощью электронных таблиц по сравнению с обработкой вручную?
3. В каких областях деятельности человека могут использоваться электронные таблицы?
4. Чем отличаются различные пакеты электронных таблиц? Назовите наиболее распространенные.
5. Составьте два-три примера макетов таблиц, в которых присутствовала бы первичная и не менее двух видов производной информации.
Вопросы и задания по теме: “Электронные таблицы”
1. Что такое относительный адрес ячейки?
2. Можно ли изменить формат относительного адреса ячейки? Если да, то как это сделать?
3. Как указать абсолютный адрес ячейки? В каких случаях необходимо использовать абсолютный адрес?
4. Перечислите все способы ссылки на ячейку и на диапазон ячеек.
5. Какие символы могут входить в имя ячейки?
6. Приведите примеры использования имен ячеек.
7. Назовите основные виды информации, используемые в электронных таблицах
8. Как Excel отличает число от текста, текст от функции?
9. Как будет воспринято программой число, в котором разделителем дробной части вместо точки поставлена запятая: как ошибка, как алфавитная информация или как формула?
10. Объясните, что такое функция.
11. Как увидеть формулу, записанную в ячейку? Как сделать так, чтобы в ячейке отображался не результат вычислений по формуле, а сама формула?
12. Как установить (изменить) точность отображения числа и результата вычислений?
Литература
1. Философия информационной цивилизации. - М.: Мир, 1994.
2. Борковский -русский словарь поинформатике.-М.:ММШП,1992.
3. IBM PC для пользователя. - М.: ИНФРА-М, 1996.
4. Основы компьютерной технологии. - М.: ABF, 1996.
5. Практикум по компьютерной технологии. - М.: ABF, 1996.
6. Информатика. Учебное пособие. - М.: АСТ, 1996.
7. Лицензионное программное обеспечение.
