Методичка ПДР

Группа клеток, выделяемая для выполнения каких-либо действий, называется интервалом или диапазоном (А1:С1, А1:А3, А1:С3).

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

ТбП обычно предоставляют следующие возможности:

1.Редактирование, копирование, удаление содержимого клеток.

2.Данные в клетке могут быть рассчитаны по заданной формуле.

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

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

5.Внесенные в ЭТ данные могут рассматриваться как база данных, что позволяет выполнять сортировку, поиск, выборку необходимой информации.

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

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

Основные недостатки – в некоторой нерациональности использования ресурсовЭВМ, сложностиветвлениярасчетовиконтроляправильностивычислений.

В настоящее время используются в практике ТбП: Ouattor Pro, Excel и др. Крометого, ЭТвстроеныврядинтегрированныхсистем: Framework, Works ит. п.

Имеются элементы работы с ЭТ и в MS Word`, но в сильно усеченном виде. Среди свободно распространяемых достаточно многофункциональным ТбП является модуль электронных таблиц OpenOffice.org Calc, работающий в оболочке Windows. На примере данного ТбП можно ознакомиться с возможно-

стями этого вида программных средств.

4.2. ОБЩИЕ СВЕДЕНИЯ ПО РАБОТЕ В ТБП CALC

Экран Calc содержит в основном элементы, имеющиеся в любом пакете OpenOffice.org, но дополнительно включает:

поле адреса ячейки ниже ИП и строку редактирования ячейки;

электронную таблицу с заголовками строк и колонок (1,2,3,…А,В,С,…);

ярлыки рабочих листов (Лист1, Лист2…).

Движение активной клетки выполняется «мышью» или клавишами управления курсором. Переход на нужную клетку можно также выполнить указанием ее адреса в поле адреса.

21

При входе в ТбП открывается ЭТ Без имени 1. Каждая ЭТ – это файл, с которым выполняются операции открытия, сохранения и т. п. Calc представляет возможность открытия и работы с несколькими ЭТ. Каждый лист ЭТ в свою очередь состоит из 245 колонок и 65536 строк. Каждому листу ЭТ может быть присвоено свое имя командой Формат/Лист/Переименовать или двойным щелчком по ярлыку листа.

Слистами ЭТ можно производить необходимые операции: удаление, вставка, копирование и т. п. см. Правка/Лист.

В клетки ЭТ можно вносить видимую информацию (текст, числа) и скрытую: формула, название ячейки (Вставка/Названия), примечание (Встав-

ка/Примечание).

Подгон размеров клетки под вносимую информацию можно выполнить командой Формат/Столбец, Формат/Строка или БМ за границу адреса столбца/строки.

Ссодержимым клеток можно выполнять действия: удаление, редактирование, копирование и т. д.

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

Выполнению операций с клетками, столбцами, строками и блоками клеток должно предшествовать их выделение. Перемещение, копирование элементов ЭТ выполняется кнопками ИП.

Объединение нескольких клеток выполняется кнопкой на ИП
	.

ВCalc возможно простое и логическое копирование. При этом простое обеспечивает копирование в заданные клетки один к одному, а логическое – с адаптацией адресов клеток или под конкретное содержание. Логическое копирование дней недели или названий месяцев выполняется на основе списка (см.

Сервис/Параметры/Списки сортировки) с использованием БМ за опорную точку в правом нижнем углу клетки.

Данный вид копирования можно применять для внесения последовательных значений выполнить в клетки таблицы (п. Правка /Заполнить/Ряды…).

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

1) выделите ячейки, на которые надо установить ссылку; 2) скопируйте их: Правка/Копировать;

3) перейдите в то место электронной таблицы, где вы хотите разместить ссылку;

4) выполните команду Правка/Вставить как, которая появляется при наличии информации в буфере обмена ;

5) в диалоговом окно установите флажок Вставить связь.

22

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

4.3. ФОРМАТЫ ДАННЫХ, ФОРМУЛЫ В CALC

Модель ячейки в Calc имеет 5 уровней: значения, формат, формула, название и примечание. Первый уровень – это отображаемые в ячейке на экране текст или число. Остальные уровни скрыты, но с ними можно проводить необходимые манипуляции.

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

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

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

В Calc формула находится на третьем, скрытом уровне клетки, а в клетке виден только числовой результат. Сама же формула отображается в строке редактирования ячейки (для активной клетки).

Формулы в Calc вводят, начиная со знака = . Они обычно включают имена клеток, с которыми выполняются соответствующие математические или логические действия. Например, в клетку А5 можно поместить формулу:

=(А1-А2)/3 + А3*2 + А4^2.

Во многих случаях вместо составных формул можно использовать функции (готовые формулы). Например, вместо =А1 + А2 + А3 + А4 можно записать: =SUM (А1:А4), где указан диапазон клеток, значения в которых складываются, или сложение значений отдельных клеток =SUM (А1;А2;А6).

Имеющиеся в Calc функции вводятся с использованием диалога, называе-

мого Мастером функций - кнопка ƒ(х).

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

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

логически скопированы с автоматическим изменением адресов клеток. При этом используются так называемые относительные ссылки. Когда этого не требуется, применяются абсолютные ссылки. Для их обозначения используют знак $. Например, $А$2.

23

Для проверки отображения формул в клетках используется пункт Сер-

вис/Параметры/Calc/Вид/Показать формулы.

Форматирование текста в клетках. Эта проблема возникает при необходимости включения в клетки заголовков столбцов. Здесь для клетки заданной ширины используются установки в окне Формат/Ячейки/Выравнивание:

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

мат/Объединить ячейки.

Тип и стиль шрифта в клетках таблицы устанавливается после их выделения с использованием п. Формат/Ячейки/Шрифт. Применение выбранного шрифта в других клетках может выполняться копированием.

Выделение полей таблицы цветом и узорами производится из пункта Формат/Ячейки/Фон. Обрамление фрагментов и таблиц – пункт Формат/Ячейки/Обрамление. Эти операции можно выполнить также соответствующими кнопками на ИП.

ЗАДАНИЕ НА ПРАКТИЧЕСКУЮ РАБОТУ №4

Табличный процессор OpenOffice.org Calc. Подготовка и обработка таблиц

1.В папке PROBA-Open создайте папку PROBA-4.

2.Откройте табличный процессор Calc.

3.Ознакомьтесь: с видом экрана, содержанием Главного меню, назначением кнопок инструментальных панелей, содержанием справки.

4.Сохраните файл под именем lab4 (формат .sxс) в папке PROBA-4.

5.На Листе 1 с форматированием данных по центру подготовьте нижеприведенную таблицу:

Оплата труда на фирме за II-е полугодие 2008 года

	Июль Август Сентябрь Октябрь Ноябрь Декабрь
Отдел 10	4000	4500	4250	5000	5000	4500
Отдел 11	5000	5500	5250	6000	6000	5500
Отдел 12	6000	6500	6250	7000	7000	6500
Отдел 13	5000	5500	5250	6000	6000	5500
Всего:	20000	22000	21000	24000	24000	22000

Присвойте Листу 1 имя Оплата. Сохраните файл.

5.На Листе 2 в нижеуказанной последовательности подготовьте следующую таблицу:

24

	A	B	C	D	E	F	G	H	I
1	РАСХОДЫ
	ФИРМЫ
2	II-е полуго-
	дие 2008 года
3	Статьи рас-	Июль						Всего:	%
	ходов
4	Матзатраты	11100	12200	11900	12700	12900	12900
5	Оплата труда
6	Отчисления
7	Прочие рас-	14500	15300	15100	15100	14900	13700
	ходы
8	Всего:

-подготовьте заголовки в строках 1, 2;

-заполните статьи расходов в колонке А, подогнав ширину колонки под длину записи;

-в ячейку В3 внесите «июль» и логическим копированием заполните месяцы

вданной строке до G3 (включительно);

-в диапазоне ячеек В4:Н8 установите формат – Числовой (целые числа);

-внесите числовые данные в строки 4, 7, используя копирование одинаковых значений;

-динамически свяжите итоговые данные таблицы листа Оплата со строкой Оплата труда подготавливаемой таблицы;

-в ячейку В6 введите формулу =B5*0,4 (используется латинский шрифт!). логически скопировав ее до ячейки G6 (включительно);

-выделите диапазон ячеек В4:Н8 и проведите суммирование по строкам и колонкам, проверьте полученные результаты (в ячейке H8 должен получиться результат 348500);

-в колонке I вычислите % по статьям расходов относительно общих расходов в ячейке H8;

-сцентрируйте заголовки в строках 1 и 2;

-измените тип и размеры шрифта в строке 1 на Полужирный, 14;

-измените тип шрифта в строке 2 на Полужирный;

-обрамите и раскрасьте отдельные части таблицы (на усмотрение студента);

-опробуйте изменение форматов в диапазоне B4:H8.

Присвойте Листу 2 имя Расходы. Сохранить файл.

7. Скопируйте таблицу Расходы на Лист 3.

Последовательно изменяя числа в строках 4,7, наблюдайте изменение итогового результата в ячейке H8 и добейтесь получения итога 300 000.

Изменения в строках 4,7 выделите зеленым цветом.

Присвойте Листу 3 имя Анализ и сохраните файл.

25

8. Скопируйте таблицу Расходы на Лист 4 и в ней произведите следующие перемещения:

-строку 4 разместите ниже строки 6;

-поменяйте местами содержимое колонок B и G по строкам 4-7;

-проведенные изменения выделите красным цветом.

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

Присвойте Листу 4 имя Замена и сохраните файл.

8. На листе 5 с использованием объединения ячеек, центрирования, соответствующей ориентации надписей и соблюдением видов шрифтов (как в задании) подготовьте нижеприведенную таблицу:

Численность населения города

Районы города

Годы	Ленинский	Железно-	Засвияж-	Заволжский
		дорожный	ский
1996	120000	110000	140000	200000

Всего по городу

Оформите данную таблицу с использованием процедуры Заполнение.

По графе Годы с интервалом 1 год до 2000 года, по колонкам Районы города – с интервалом 10000, а в графе Всего по городу выполните подсчет итоговых сумм.

Присвойте листу 5 имя Формат и сохраните файл.

9.Запишите контрольные вопросы.

10.Предъявите преподавателю файлы листы 1, 2, 3, 4, 5 файла lab4.

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

По листу Расходы:

1.Для каких ячеек использовалось логическое копирование?

2.Как выполнена динамическая связь по строке Оплата труда?

3.Как получены итоговые результаты по строкам и колонкам таблицы? По листу Формат:

1.Какое диалоговое окно используется для форматирования заголовков

таблицы?

2. Какая команда используется для реализации процедуры Заполнение?

26

ЗА Н Я Т И Е №5

5.1.СЛУЖЕБНЫЕ ФУНКЦИИ CALC

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

Поиск и замена. Для поиска информации в больших таблицах, и если при этом необходимо заменять данные, целесообразно использовать окно Прав-

ка/Найти и заменить.

Создание окон для работы с фрагментами больших таблиц и фиксирования заголовков используется разбиение рабочих листов на окна. Эта операция выполняется п. Окно/Разбить (влево вверх от выделенной клетки). Дополнительные окна можно открыть из п. Окно/Новое окно. Открытые окна автоматически нумеруются. Соответствующими командами можно менять, упорядочивать, скрывать и закрывать.

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

Формирование таблиц, объединяющих данные нескольких таблиц, выполняется с помощью окна Данные/Объединить. При этом можно связать информацию этих таблиц, включив последовательно кнопки Дополнительно и Свя-

зать с исходными данными в указанном окне.

5.2. ГРАФИЧЕСКОЕ ОТОБРАЖЕНИЕ ТАБЛИЦ В CALC

Если в ЭТ представлены взаимосвязанные данные, то их можно отобразить плоскостными или объемными диаграммами. В Calc имеется 9 типов диаграмм, каждый из которых содержит также несколько видов.

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

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

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

2)Вызвать Мастер диаграмм (МД) кнопкой на ИП или через меню

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

3) В окне МД «Тип диаграммы» указать тип и вид диаграммы.

27

4)В окне МД «Диапазон данных» проверить правильность выбранного интервала и включить необходимые кнопки «Ряды данных» в строках или в столбцах в зависимости от того, какой титул выносится на ось X.

5)В окне МД «Ряд данных» при необходимости можно расположить нужным образом ряды данных.

6)В окне МД «Элементы диаграмм» задать названия осей, заголовок всей диаграммы, при необходимости сетку и расположение легенды. Легенда – это расшифровка переменных при наличии нескольких рядов данных.

Если представленная в окне диаграмма устраивает пользователя, то при нажатии кнопки «Готово» формируется её изображение.

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

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

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

5.3. СОЗДАНИЕ И ОБРАБОТКА БАЗ ДАННЫХ (БД) В CALC

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

В качестве БД может быть использована любая таблица, имеющая заго-

ловки столбцов. Например, список продавцов:
		А	B	C		D	E
	1	Фирма	Город	Изделие		Цена	Количество
	2

В такой таблице совокупность заполненных клеток каждой строки называ-

ется записью.

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

Упорядочение информации в БД обеспечивается процедурой сортировки. Эта процедура выполняется из п. Данные/Сортировка или соответствующими кн. ИП. Сортировку производят по возрастанию или убыванию, по одному или нескольким столбцам, предварительно выделив таблицу с заголовками.

28

Простой поиск в БД можно выполнять с помощью процедуры Прав-

ка/Найти и заменить.

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

В Calc применяются: автофильтр, стандартный и расширенный фильтры, работа с которыми выполняется из п. Данные/Фильтр.

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

Стандартный фильтр дает возможность проводить выборки с использованием 1-3 критериев. Он реализуется с использованием окна, открываемого командой Данные/Фильтр/Стандартный фильтр. Для фильтрации на месте в зоне указанного окна необходимо снять флажок «Копировать результат в …». При включении указанного флажка полученные результаты помещаются вне анализируемой таблицы в заданное место ЭТ.

Для более сложных выборок (до восьми условий фильтрации) используется процедура Расширенный фильтр. Здесь работа выполняется в следующем порядке:

1.Формируется область критериев дублированием заголовков рассматриваемой таблицы в свободную часть ЭТ и ниже задаются величины критериев.

2.Выделяется анализируемая таблица.

3.В диалоговом окне Данные/Фильтр/Расширенный фильтр задаются необходимые условия поиска данных.

Если в указанном окне установлен флажок Регулярные выражения, то можно также использовать в определении фильтра знаки РАВНО (=), НЕ РАВНО (<>) и другие символы из списка регулярных выражений, поддерживаемых OpenOffice.org (см. справку по Расширенному фильтру). Это также относится к соответствующим ячейкам, указанным в Расширенном фильтре.

ЗАДАНИЕ НА ПРАКТИЧЕСКУЮ РАБОТУ №5

Графическое отображение таблиц, работа с данными

вТбП OpenOffice.org Calc.

1.В папке PROBA-Open создайте папку PROBA-5.

2.Откройте в Calc файл lab4 (папка Prоba).

3.Постройте диаграмму, соответствующую таблице Расходы в следующей последовательности:

- в таблице Расходы выделите диапазон клеток A3:G7 и откройте окно Мастера Диаграмм (МД);

29

-в окне МД «Тип диаграммы» укажите тип диаграммы - Гистограмма и

вид - Обычная;

-в окне МД «Диапазон данных» проверьте правильность выбранного интервала клеток и включите кнопку «Ряды данных в строках»;

-в окне МД «Элементы диаграмм» задайте заголовок диаграммы -

РАСХОДЫ ФИРМЫ, название оси X – II-е полугодие 2008 года, название оси Y – Тысячи рублей, размещение Легенды – справа;

-«вырежьте» диаграмму с листа Расходы и вставьте ее на лист 2;

-рационально разместите диаграмму на экране, при необходимости, изменив ее размеры, подберите рациональные шрифты заголовков, Легенды, надписей по осям и откорректируйте их расположение, измените цвет переменной «Матзатраты» на Бирюзовый 1;

-введите надписи значений данных по переменной «Оплата труда» с подбором рационального шрифта;

-присвойте листу имя График.

Сохраните файл под именем lab 5-1(формат .sxс) в папке PROBA-5.

4. На листе Расходы файла lab5-1 выполните:

- по строкам – месяцы и оплата труда таблицы Расходы фирмы (диапазоны клеток B3:G3 и B5:G5) линейчатую диаграмму с заголовком Оплата труда и названием оси Y – Тысячи рублей. Оцифровку оси Y выполните через 2000;

- по столбцам – статьи расходов и % той же таблицы (А4:А7 и I4:I7) разделенную круговую диаграмму с заголовком Расходы фирмы с надписями значений данных как проценты.

Поле данных диаграмм выделите сине-серым цветом. Сохраните файл.

5. Выполните формирование базы данных и операции с ней в Calc в следующей последовательности:

-выполните команды Создать, Файл/Сохранить как..., присвоив файлу имя lab5-2 (формат .sxс) в папке PROBA-5 (в файле должно быть 4 листа);

-на Листе 1 для колонок A, B, C установите категорию формата «Текстовый», для D – «Числовой» и переименуйте Лист 1 в База;

-здесь же подготовьте нижеследующую таблицу:

	A	B	C	D
1	Фирма	Изделие	Тип	Цена
2	Система	Принтер	Струйный	300
3	Оргтех	Сканер	Ручной	250
4	Аякс	ПЭВМ	Сeleron	600
5	Венс	ПЭВМ	Сeleron	800
6	Март Арт	Принтер	Лазерный	400
7	Взлет	ПЭВМ	Сeleron	900
8	Экон	ПЭВМ	Pentium	1200
9	Анкор	ПЭВМ	Pentium	1800
10	Апрель	ПЭВМ	Pentium	1100
11	Аргон	ПЭВМ	Pentium	1900
12	Симбирск-М	Принтер	Лазерный	500

30