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

 

Технологии интегрированного электронного офиса позволяют:

· эффективно обрабатывать первичные данные, формировать выходные документы и создавать отчеты с использованием формул и функций Excel,

· агрегировать экономические данные с помощью инструментов: консолидация данных, сводные и итоговые таблицы,

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

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

Технология ввода формул в Excel предусматривает ввод формулы только в одну ячейку и дальнейшее ее копирование по определенным правилам на последующие ячейки.

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

Таким образом, консолидация – агрегирование (объединение) данных, представленных в исходных областях-источниках. Результат консолидации – таблица консолидации, размещается в области назначения. Таблица консолидации создаётся путём применения функции обработки (среднее значение, максимальное, минимальное и т.д.) к исходным значениям. Области-источники могут находиться на различных листах или рабочих книгах. В консолидации может участвовать до 255 областей источников.

Существуют следующие варианты консолидации данных:

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

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

· по категории – для областей-источников, содержащих однотипные данные, различающиеся по своей структуре;

· консолидация внешних данных, области-источники которых содержатся в различных файлах.

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

 

Таблица 3 - Целевой выбор способа консолидации данных

Цель	Действие
Упорядочить данные во всех листах, задав им одинаковый порядок и расположение	Консолидация по расположению
Организовать данные на разных листах по разным принципам, но с использованием одинаковых названий строк и столбцов, чтобы их можно было сравнить в основном листе	Консолидация по категории
Применять формулы со ссылками на ячейки или объемными ссылками на другие листы из-за отсутствия постоянных позиций или категорий, на которые можно было бы опираться	Консолидация по формуле

 

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

· подсчитать промежуточные итоги любого столбца, содержащего числовые данные,

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

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

4. Сводные таблицы и сводные диаграммы - одно из наиболее мощных средств Excel по работе с табличными данными. Они полезны как для анализа, так и для агрегирования (обобщения) информации, хранящейся в исходной таблице, на рабочих листах, во внешних файлах. При создании сводной таблицы или сводной диаграммы можно использовать различные источники исходных данных:

· списки или базы данных MS Excel,

· внешние источники данных,

· несколько диапазонов консолидации,

· другую сводную таблицу,

· изменение исходных данных исходной таблицы при их обновлении.

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

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

Сводная диаграмма - графический вариант сводной таблицы.

Сводные таблицы позволяют:

· создавать новые режимы отображения данных электронной таблицы,

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

· анализировать тенденции,

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

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

Для подготовки сводной таблицы (или сводной диаграммы) из исходной таблицы Excel предлагает пользователю мастер сводных таблиц.

К исходной таблице предъявляются определенные требования:

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

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

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

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

При формировании сводной таблицы Excel автоматически вычисляет промежуточные итоги и общие итоги.

Мастер сводных таблиц предоставляет новую область листа, содержащую все, что необходимо для представления данных в виде сводной таблицы:

· список полей сводной таблицы, из которого перетаскиваются поля, и

· область размещения.

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

· в области Поля строк данные отображаются по вертикали, по одному элементу на строку;

· в области Поля столбцов данные отображаются по горизонтали, по одному элементу в столбце;

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

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

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

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

· сортировка,

· фильтрация.

Сортировка выполняется по определенным правилам и связана с перестановкой элементов (например, строк) таблицы. Сортировать можно:

· строки по возрастанию и убыванию данных,

· строки по двум, трем, четырем столбцам,

· строки по месяцам и дням недели,

· столбцы,

· с использованием собственных данных в качестве порядка сортировки.

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

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

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

В Microsoft Excel доступны для фильтрации таблиц два инструмента:

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

· расширенный фильтр для более сложных условий отбора.

При формировании условий поиска Excel можно использовать подстановочные знаки (таблица 4)

 

Таблица 4 – Подстановочные знаки в условиях отбора

Подстановочный знак	Цель использования
? (знак вопроса)	при поиске заменяет один любой знак
* (звездочка)	при поиске заменяет любое количество любых знаков
~ (тильда), за которой следует ?, * или ~	чтобы найти вопросительный знак, звездочку или тильду.

 

Автофильтр используется для вывода строк,

· содержащих то или иное значение,

· отвечающих нескольким условиям для одного столбца.

С помощью автофильтра можно осуществить отбор:

· по наименьшему и наибольшему значению данных столбца,

· строк таблицы, содержащих определенный текст,

· пустых ячеек или ячеек со значениями,

· отбор чисел, которые больше или меньше указанного значения,

· чисел, которые равны или не равны указанному значению,

· текстовых строк по их началу или концу,

· наибольших или наименьших чисел в процентном соотношении

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

Используя расширенный фильтр, можно:

· установить фильтр только для уникальных записей,

· отобрать строки из исходной таблицы по сложным критериям.

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

· несколько условий для одного столбца,

· одно условие для нескольких столбцов,

· разные условия для разных столбцов,

· один из двух наборов условий для двух столбцов,

· более двух наборов условий для одного столбца,

· условия выбора по вычисляемому критерию.

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

 

Выводы по теме

 

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