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

Обновлено: 04.07.2024

Общей чертой всех табличных процессоров является способ ссылок на ячейки, используемых при конструировании формул. Ссылки бывают трех типов: абсолютные, относительные и смешанные. По умолчанию при создании формул применяются относительные ссылки. Относительная ссылка на ячейку , например, A1, основана на положении этой ячейки по отношению к ячейке, содержащей формулу. При перемещении ячейки с формулой или при копировании формулы вдоль строк или вдоль столбцов относительные ссылки, включенные в формулу, автоматически корректируются. Например, формула "=A1+B1" в ячейке C1 при копировании вниз в ячейку C2 изменяется на "=A2+B2", а при копировании вправо в ячейку D1 изменяется на "= B1+C1".

Абсолютная ссылка на ячейку , например, $A$1, всегда указывает на ячейку, расположенную в определенном месте. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов абсолютная ссылка не корректируется. Например, формула "=$A$1+$B$1", введенная в ячейку C1, при копировании в ячейку C2 или D1 останется без изменения.

Смешанная ссылка содержит либо абсолютный адрес столбца и относительный адрес строки, например, $A1, $B1, либо абсолютный адрес строки и относительный адрес столбца, например, A$1, B$1 и т. д. При копировании или перемещении ячейки, содержащей формулу, относительная часть ссылки изменяется, а абсолютная часть остается неизменной, т.е. при копировании формулы вдоль строк и вдоль столбцов относительная часть ссылки автоматически корректируется, а абсолютная часть ссылки не изменяется. Например, формула "=$A1+$B1", расположенная в ячейке C1, при копировании в C2 изменится на "=$A2+$B2", а при копировании в D1 останется без изменения. Напротив, формула "=A$1+B$1", расположенная в ячейке C1, при копировании в C2 останется без изменения, а при копировании в D1 изменится на "=B$1+C$1".

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

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

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

Все табличные процессоры используют одинаковые знаки арифметических и логических операций: "+" – сложение , "–" – вычитание , "*" – умножение , "/" – деление , "^" – возведение в степень, ">" – больше, "<" – меньше, "=" – равно, ">=" – больше или равно, "< http://www.intuit.ru/2010/edi" >

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

Например, пусть требуется решить в MS Excel следующую финансовую задачу: "Через сколько лет ежеквартальные вклады размером 160 тыс. руб. принесут доход в 10 млн руб. при ставке процента 13,5% годовых? Процент начисляется по полугодиям". Для решения этой задачи применяется финансовая функция КПЕР, диалоговое окно которой с заполненными полями и результатом вычисления приведено на рис.7.11.

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

Приложение Microsoft Excel версии 2007 поддерживает различные типы диаграмм, позволяя строить не только диаграммы стандартных типов, но и смешанные диаграммы, используя несколько типов диаграмм.

Рис. 7.12. Диалоговое окно выбора типа линии тренда

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

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

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

Рис. 7.13. Диалоговое окно выбора типа линии тренда

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

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

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

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

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

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

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

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

Рассмотрим применение аппарата сценария на примере решения задачи следующего содержания. Предположим, что вероятность того, что некто, посетивший электронный магазин на некотором сайте в Интернете, совершит покупку, составляет 20%. Если сайт посетят одновременно 10 человек, то какова вероятность того, что:

никто не сделает ни одной покупки;
точно два человека совершат по одной покупке;
не более 2-х человек совершат по одной покупке;
по крайней мере два человека совершат по одной покупке.

Как изменятся вероятности в пунктах 1)-4), если сайт посетят одновременно 20 человек или если вероятность совершения покупки одним человеком составляет 10%?

На рис.7.16 показан отчет по сценариям для решения каждого пункта сформулированной задачи.

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

Самый простой макрос – это записанная последовательность нажатия клавиш, перемещений и щелчков кнопками мыши. Инструментальное средство Macrorecorder (Макрорекордер) отслеживает действия пользователя, выполняемые при помощи команд меню или клавиатурных эквивалентов ("горячих" клавиш), генерирует и записывает соответствующие макрокоманды до тех пор, пока пользователь не остановит процесс записи. В результате создается программа ( макрос ), выполнение которой дает тот же эффект, что и выполненные действия. Последовательность действий может быть воспроизведена, как магнитофонная запись . Это позволяет автоматизировать процессы обработки данных, проводимые командами меню , неоднократно выполняя записанную программу. Ее можно обработать и изменить, добавив стандартные макрокоманды и макрофункции. Для использования макрорекордера не требуется никаких знаний по программированию. При помощи макрорекордера удобно создавать макросы, которые автоматизируют рутинные операции : форматирование таблиц, создание диаграмм и сводных таблиц, работу со списками данных и т.д.

Созданный макрос можно изменить, добавив операторы встроенного языка программирования или его стандартные функции и организовав диалоги с пользователем. Современные программы обработки электронных таблиц позволяют пользователю создавать на базе табличного процессора новые приложения со специализированными диалоговыми окнами, что делает работу с приложением максимально удобной. Для создания приложений табличные процессоры содержат в качестве дополнительной компоненты язык программирования высокого уровня. Например, в MS Excel это язык Visual Basic for Application ( VBA ) – объектно-ориентированный язык программирования , который базируется на командах и синтаксисе языка Basic . VBA встроен в офисную среду и позволяет манипулировать не только объектами MS Excel , но и объектами всех популярных приложений Microsoft Office.

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

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

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

Табличный процессор Excel: что это за инструмент

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

Применяется программа в десятках других областей, решает широкий круг задач:

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

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

Составляющие электронной таблицы: строка, столбец, ячейка, их обозначение

ЭТ хранятся в файлах преимущественно форматов xlsx и xls, называются рабочими книгами. Состоят из листов, представленных вкладками. Они имеют собственные названия, отображающиеся на ярлыке листа внизу справа. Переименовываются через контекстное меню (правый клик).

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

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

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

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

В качестве содержимого клетки может выступать:

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

Столбцы электронной таблицы обычно обозначаются цифрами от единицы до миллиона, строки – буквами английского алфавита: сначала одной (A-Z), затем двумя (AA-ZZ), потом – тремя и т. д.

К ЭТ применимы понятия блока и диапазона ячеек. Блок – объект, над которым проводят операции в Excel. Им может быть ячейка, строка, столбец, их диапазон. Обозначается как адрес начальной и конечной клеток, например:

B7 – ячейка;
B2:B9 – фрагмент столбца B со второй до десятой строки;
C4:D12 – прямоугольная область.

К блокам относится и свободная область – несколько ячеек, расположенных, в том числе не последовательно. При выделении объектов для образования блока его строки обозначаются:

R1: R – row;
C1: C – column;
цифра – число выделенных строк и столбцов соответственно.

Структура окна Excel

Электронная таблица каждого листа способна вместить свыше миллиона строк и 16 484 столбцов. Для работы с ними следует знать названия основных элементов интерфейса программы. Верхняя часть окна – заголовок приложения, слева расположена панель быстрого доступа. Ниже следует главное меню в виде ленты с вкладками (с Excel 2007) – через него вызывается большинство функций программы.

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

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

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

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

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

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

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

Функции и виды табличных процессоров

Функции табличных процессоров:

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

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

Виды табличных процессоров

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

Поддерживается работа на Linux, Windows, Mac OS, ReactOS, SkyOS, BeOS и других ОС.

Функционал приложения очень широкий.

Это ответвление табличного процессора OpenOffice Calc.

KSpread или KCells

Свободный табличный процессор, входит в пакеты KOffice и KDE.

Доступен для Linux, Unix-like OS, Windows.

IBM Lotus Symphony

18.01.2012 разработка прекращена, а IBM переключилась на работу с Apache OpenOffice.

Доступны версии для Linux, Windows, DOS.

Возможности табличного процессора

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

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

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

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

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

Рассмотрим основные составные части рабочего листа редактора Excel. В него входит 65536 строк и 256 столбцов. Строки обозначаются цифрами, столбцы имеют буквенное обозначение: от A до Z, далее АА до AZ, BA до BZ и т.д. вплоть до столбца с обозначением IV. Нумерация строк с левой стороны листа сверху вниз от 1 до 65536. На пересечении строк и столбцов формируются ячейки.

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

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

Первая панель инструментов – это меню, содержащие слева направо Файл, Правка, Вид, Вставка, Формат, Сервис, Данные, Окно, Справка и три кнопки: для сворачивания и разворачивания окна и для закрытия таблицы Excel. Затем следуют панели "Стандартная" и "Форматирование".

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