Назначение и области применения табличных процессоров

Назначение и области применения табличных процессоров

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

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

Табличные процессоры обеспечивают:

¶ ввод, хранение и корректировку данных;

¶ автоматическое проведение вычислений по заданным формулам;

¶ наглядность и естественную форму документов, представляемых пользователю на экране;

¶ построение различного рода диаграмм и графиков на основе табличных данных, что особенно важно при решении некоторых задач экономического характера;

¶ оформление и печать электронных таблиц;

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

¶ работу с электронными таблицами как с базами данных: сортировку таблиц, выборку данных по запросам;

¶ дружественный интерфейс;

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

¶ возможность работы в локальной сети;

¶ организацию обмена данными с другими программными средствами, например, с системами управления базами данных;

¶ возможность работы с трехмерной организацией электронных таблиц;

¶ разработку макрокоманд, настройку среды под потребности пользователя и т.д.

Основные понятия

Табличный процессор	Программный продукт, позволяющий создать электронные таблицы и автоматизировать обработку табличных данных
Электронная таблица	Автоматизированный эквивалент обычной таблицы, в ячейках которой находятся либо данные, либо результаты расчета по формулам
Рабочая область электронной таблицы	Состоит из строк и столбцов, имеющих свои имена
Имена строк	Это номера строк: 1,2,3 и т. д.
Имена столбцов	Это буквы латинского алфавита сначала от А доZ, затем от AA до AZ, BA до BZ и т. д.
Ячейка	Область, определяемая пересечением столбца и строки ЭТ, имеющая свой уникальный адрес
Адрес ячейки	Определяется именем столбца и именем строки, на пересечении которых находится ячейка
Ссылка	Указание адреса ячейки
Блок ячеек	Группа смежных ячеек, определяемая с помощью адреса. Блок ячеек может состоять из одной ячейки, строки (или её части), столбца (или его части), а также последовательности строк и столбцов (или их частей)
Адрес блока ячеек	Задается указанием строк первой и последней его ячеек, между которыми ставится разделительный символ - : или ..
Панели управления	Часть экрана, дающая пользователю информацию об активной ячейке и ее содержимом, меню и режиме работы
Строка состояния (статусная строка)	Содержит сведения о текущем режиме работы программы, имени файла текущей электронной таблицы, номере текущего окна и т. п.
Строка ввода	Отображает вводимые в ячейку данные. В ней пользователь может просматривать или редактировать содержимое текущей ячейки
Текущая (активная) ячейка	Ячейка ЭТ, в которой в данный момент находится курсор. Адрес и содержимое текущей ячейки вводится в строке ввода ЭТ

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

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

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

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

Формулы - это выражения, состоящие из числовых величин, адресов ячеек и функций, соединенных знаками арифметических операций. Пример формулы: =А5/(Н8+12).

В ячейке, в которой находится формула, виден только результат вычислений над данными, содержащимися в А5 и Н8, саму формулу можно увидеть в строке ввода, когда данная ячейка станет активной.

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

Пример функции: СУММ(B2:B6,B9) - функция вычисления суммы значений множества чисел, находящихся в ячейках В2.ВЗ,В4.В5.В6 и В9.

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

ДД-ММ-ГГ (04-10-98);

ДД.ММ.ГГ (04.10.98);

ДД-МММ (04-Янв) и т.д.

Работа со списками

Работа с подготовленным списком в MS Excel может осуществляться по трем направлениям:

сортировка – выстраивание данных в нужном порядке;

отбор данных – извлечение записей данных из списка в соответствии с некоторыми требованиями (критериями);

анализ данных – обработка различными средствами MSЕxcel информации, находящейся в списке или в отфильтрованных данных.

III. Сортировка данных.

Символы структуры

Символ структуры	Назначение
+	Кнопка для показа детальных данных
-	Кнопка для скрытия соответствующих детальных данных
Номера уровней	Последовательные уровни для строк и столбцов
Уровень структуры	Все детальные строки или детальные столбцы одного уровня

Для автоматического создания структуры следует:

¶ проверить, что в итоговых формулах содержатся ссылки на детальные данные, расположенные в одном направлении относительно итоговых;

¶ для структуризации части рабочего листа необходимо выделить нужный диапазон ячеек; для структуризации всего рабочего листа – выбрать одну ячейку;

¶ воспользоваться командой Данные – Группа и структура – Создание структуры.

¶ При структуризации рабочего листа вручную необходимо:

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

¶ воспользоваться командой Данные – Группа и структура – Группировать;

¶ в случае ошибочных действий или для разгруппировки данных выбрать команду Данные – Группа и структура – Разгруппировать;

¶ команда Данные – Группа и структура – Настройка позволяет указать вид организации документа и создать структуру;

¶ для возврата рабочего листа в исходное состояние следует использовать команду Данные – Группа и структура – Удалить структуру.

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

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

¶ простые промежуточные итоги;

¶ сложные промежуточные итоги;

¶ итоги, связанные с вычислением частичных сумм (используется Мастер Частичных сумм).

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

С помощью операции Итоги можно:

¶ указать способ группировки данных;

¶ вывести промежуточные и общие итоги для одной группы в списке;

¶ вывести промежуточные и общие итоги для нескольких групп в списке;

¶ выполнить расчеты над данными.

Промежуточные итоги подводятся следующим образом (методика подведения итогов).

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

2. Провести сортировку по необходимому полю или полям (команда Данные – Сортировка).

3. Подвести итоги (команда Данные – Итоги). При создании вложенных промежуточных итогов следует четко представлять уровни итогов и создавать их в порядке увеличения уровня детализации: сначала – по первому ключу, далее, отключая опцию Заменить текущие итоги, – по вторичному ключу и т.д.

Чтобы убрать Итоги, необходимо установить указатель в список с итогами и воспользоваться командой Данные – Итоги – кнопка Убрать все.

При создании итогов при необходимости можно выполнить следующие действия:

¶ использовать одну операцию для нескольких столбцов данных;

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

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

¶ использование Мастера суммирования (команда Сервис – Мастер – Частичная сумма) позволяет просуммировать только те данные в списке, которые удовлетворяют заданному критерию (например, вычислить сумму продаж для товаров, цена которых больше некоторого значения).

КОНСОЛИДАЦИЯ ДАННЫХ

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

СВОДНЫЕ ТАБЛИЦЫ.

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

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

¶ обобщения большого количества однотипных данных;

¶ группировать данные в сводных таблицах по категориям;

¶ реорганизации данных (с помощью перетаскивания;

¶ повышать удобочитаемость Сводной таблицы путем её форматирования;

¶ использовать данные из других программ;

¶ модифицировать Сводную таблицу для изменения представления данных;

¶ обновлять сводную таблицу с учетом текущих данных;

¶ создавать стратегические отчеты для Сводных таблиц.

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

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

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

2. Местонахождение исходных данных – список MS Excel, внешний источник данных, диапазоны консолидации, находящиеся в другой сводной таблице.

3. При задании структуры сводной таблицы определить:

¶ поля, находящиеся в строках и столбцах таблицы;

¶ поля, по которым подводятся итоги (с выбором необходимой операции);

¶ поля для страниц, что позволяет представить информацию в трехмерном виде.

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

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

6. Сводные таблицы допускают возможность группировки элементов полей по различным уровням иерархии путем объединения (выделение данных сводной таблицы осуществляется, например, с помощью мыши) в группы (группы можно переименовывать по желанию). Для этой цели на панели инструментов Сводные таблицы существуют две кнопки – Группировать и Разгруппировать. Можно скрывать и показывать детали в группе. Элементы самого высокого уровня группировки (обобщающие элементы) располагаются по верхней или по крайней левой границе сводной таблицы (отображение исходных данных в данном случае производится путем выделения группированной ячейки сводной таблицы и применения соответствующей команды отображения данных – кнопка Отобразить детали). Бывает следующая группировка сводной таблицы:

¶ группировка элементов по их именам;

¶ группировка чисел по диапазонам (для числовых данных, кнопка Группировать на панели инструментов Сводные таблицы);

¶ группировка по временным диапазонам (данные в формате Дата/Время).

7. Возможность построения диаграмм на основе сводных таблиц.

8. Некоторые дополнительные возможности сводных таблиц (многие из них производятся путем выбора соответствующей команды контекстного меню необходимого поля или выделенной области):

¶ сортировка элементов в сводной таблице;

¶ размещение страниц сводной таблицы на различных рабочих листах (кнопка – Отобразить страницы);

¶ управление общими и промежуточными итогами;

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

¶ вставка вычисляемого поля в сводную таблицу;

¶ использование автоформата для форматирования сводной таблицы.

СРЕДСТВА КОНТРОЛЯ

Контроль рабочих листов

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

Контроль логических ошибок

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

При наличии ошибок другой категории нарушается логика работы программы. В этом случае сама программа Excel способна помочь в их поиске и исправлении. Эти ошибки также можно разделить на две группы: невычисляемые формулы и циклические ссылки.

Неверные формулы. Если получить значение в результате вычисления формулы по каким-то причинам невозможно, программа Excel выдает вместо значения ячейки код ошибки. Возможные коды ошибок и причины их появления приведены в таблице 13.2.

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

Таблица 13.2. Стандартные сообщения программы об ошибках

КОД ОШИБКИ	ПРИЧИНА ВОЗНИКНОВЕНИЯ
##### (символы заполняют ячейку целиком)	Числовые данные не помещаются в ячейку по ширине или же при вычислении по формуле из даты вычитается более поздняя
#ДЕЛ/0!	В знаменателе формулы получается нулевое значение или стоит ссылка на пустую ячейку. Последнее может оказаться связано с неверным использованием относительной ссылки
#ЗНАЧ!	У функции пропущен обязательный параметр или же в качестве параметра использована ссылка на пустую ячейку или ячейку с данными, тип которых не соответствует типу нужного параметра
#ИМЯ?	Опечатка в имени функции или имени ячейки (диапазона). Может быть вызвана пропуском двоеточия при указании диапазона или кавычек при указании текстового параметра
#ПУСТО!	В формуле использовано пересечение диапазонов, не включающее ни одной ячейки
#ЧИСЛО!	При вычислении получилось число, слишком большое или слишком маленькое для программы Excel. Как правило, это следствие неправильности формулы
#ССЫЛКА!	Формула указывает на ячейку, которая отсутствует на рабочем листе. Чаще всего такай ошибка возникает вследствие копирования формулы с относительной адресацией влево или вверх, из-за чего возникают ссылки на столбцы, предшествующие столбцу А, и строки, предшествующие первой

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

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

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

Проверка вводимых данных

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

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

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

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

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

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

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

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

Способ уведомления о нарушении правил ввода задается на вкладке Сообщение об ошибке. Здесь описывается вид появляющегося диалогового окна, если введенные данные не удовлетворяют заданным условиям.