Способы обработки большого объема данных

Финансы в Excel

Обработка больших объемов данных. Часть 1. Формулы

Вложения:

nwdata_sums.xls	[Обработка данных (формат 97-2003)]	2725 kB
nwdata_sums.xlsx	[Обработка данных (формат 2007)]	732 kB

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

Методы переноса данных в Excel могут быть различны:

• Копирование-вставка результатов запросов
• Использование стандартных процедур импорта (например, Microsoft Query) для формирования данных на рабочих листах
• Использование программных средств для доступа к базам данных с последующим переносом информации в диапазоны ячеек
• Непосредственный доступ к данным без копирования информации на рабочие листы
• Подключение к OLAP-кубам

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

Обработка этих данных в Excel может вестись различными методами. Выделим основные способы работы:

1. Обработка данных стандартными средствами интерфейса Excel
2. Анализ данных при помощи сводных таблиц и диаграмм
3. Консолидация данных при помощи формул рабочего листа
4. Выборка данных и заполнение шаблонов для получения отчета
5. Программная обработка данных

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

В данной статье будут рассмотрены способы консолидации и выборки данных при помощи стандартных формул Excel.

Описание примеров

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

Выгруженный из этой базы данных набор записей сформирован при помощи Microsoft Query.

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

Файл nwdata_sums.xls используется для версий Excel 2000-2003

Файл nwdata_sums.xlsx имеет некоторые отличия и используется для версий Excel 2007-2010.

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

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

Применение метода

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

Проблема при консолидации данных при помощи сводных таблиц появляются, если предполагается дальнейшая работа с этими агрегированными данными. Например, сравнить или дополнить данные из двух разных сводных таблиц (как вариант: объемы продаж и прайс листы). В таком случае обычно прибегают к методу копирования значений из сводных таблиц в промежуточные диапазоны с дальнейшим применением формул поиска (VLOOKUP/HLOOKUP). Очевидно, что проблема возникает при обновлении исходных данных (например, при добавлении новых строк) – требуется заново копировать результаты консолидации из сводной таблицы. Другим, с нашей точки зрения, не совсем корректным методом решения является применение функций поиска непосредственно к диапазонам, которые занимают сводные таблицы. Это может привести к неверному поиску при обновлении не только данных, но и внешнего вида сводной таблицы.

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

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

Суммирование по одному ключевому полю

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

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

Нижние таблицы показывают возможности другой редко используемой функции DSUM

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

В другом варианте указания условий именем поля в этом диапазоне можно пренебречь, задав его прямо в тексте условия:

Здесь data!Z2 означает ссылку на текущую строку данных, а не на конкретную ячейку, так как используется относительная ссылка. К сожалению, нельзя указать в третьем параметры ссылку на одну ячейку – строка заголовка полей все равно требуется, хотя и может быть пустой.

В принципе, функции типа DSUM являются устаревшим методом работы с данными, в подавляющем большинстве случаев лучше использовать SUMIF, SUMPRODUCT или формулы обработки массивов. Но иногда их применение может дать хороший результат, например, при совместном использовании с интерфейсной возможностью «расширенный фильтр» – в обоих случаях используется одинаковое описание условий через дополнительные диапазоны.

Суммирование по нескольким критериям

Таблицы с формулами на листе SUM2 показывают вариант суммирования по нескольким критериям.

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

Пример: Есть, если два поля с перечнем слов. Пары слов «СТОЛ»-«ОСЬ» и «СТО»-«ЛОСЬ» дают одинаковый ключ «СТОЛОСЬ». Что соответственно даст неверный результат при консолидации данных. При использовании служебного символа комбинации ключей будут уникальны «СТОЛ;ОСЬ» и «СТО;ЛОСЬ», что обеспечит корректность вычислений.

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

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

Обрабатываются все ячейки диапазона (data!$M$2:$M$3000), но для тех ячеек, где условия не выполняются, в суммирование попадает нулевое значение (логическая константа FALSE приводится к числу «0»). Такое использование этой функции близко по смыслу к формулам обработки массива, но не требует ввода через Ctrl+Shift+Enter.

Третий пример аналогичен, описанному использованию функций DSUM для листа SUM, но в нем для диапазона условий использовано несколько полей.

Четвертый пример – это использование функций обработки массивов.

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

Пятый пример содержится только в файле формата Excel 2007 (xlsx). Он показывает возможности новой стандартной функции

Поиск по одному критерию

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

Первый вариант – это использование популярной функции VLOOKUP.

Во втором вариант использовать VLOOKUP нельзя, так как результирующее поле находится слева от искомого. В данном случае используется сочетание функций MATCH+OFFSET.

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

Поиск по нескольким критериям

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

В первом варианте используется техника использования служебного столбца, описанная в примере к листу SUM2:

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

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

Выборка по одному критерию

Таблица на листе SELECT показывает вариант фильтрации данных через формулы.

Предварительно определяется количество строк в выборке:

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

Вторая и последующие строки ищутся в вычисляемом диапазоне с отступом от предыдущей найденной строки:

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

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

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

Выборка вариантов

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

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

Третий служебный столбец проверяет значение второго ключа на уникальность:

Результирующий столбец второго ключа ProductName ищет уникальные значения в служебном столбце C:

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

Заключение

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

Источник

Большие данные: свойства, методы обработки, описание

Биг Дата – понятие, которое возникло в современном мире относительно недавно. Но с развитием информационных технологий и IT оно стало занимать все больше места в жизни каждого. Не всем понятно, как работать с соответствующей составляющей, что она собой представляет, а также для чего необходима. В данной статье будут раскрыты ответы на все перечисленные вопросы. А еще каждый сможет выяснить, каким образом удастся стать настоящим Big Data Engineer. Справиться с поставленной задачей не всегда легко, но, если постараться и задаться целью, все обязательно получится.

Определение

Big Дата или большие данные – это некая специальная методика обработки сведений электронного формата. Включает в себя просто огромные объемы информации, которые достигают тысячи Терабайт. Увеличивается их количество постоянно и с большой скоростью.

Если говорить простыми словами, рассматриваемый термин – это большое количество совершенно разных сведений, известных миру, поступающих в «места хранения» на постоянной основе.

Классификация

BigData обладают собственной классификацией. Условно принято разделять все большие сведения на несколько групп:

1. Структурированные. Они обладают структурой таблиц, а также отношений. Сюда можно отнести Excel, а также документы CSV.
2. Полуструктурированные. Еще называются слабоструктурированными. Сведения, не обладающие строгой табличной составляющей и отношениями. Имеют разнообразные маркеры, при помощи которых в реальной жизни удается отделить семантику и обеспечение иерархии полей и записей. Пример – электронные материалы о письмах по e-mail.
3. Неструктурированные. Не имеют никакой четкой организации и структуры: текст на естественном языке, аудиодорожки, видеоролики, изображения.

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

Внимание: не стоит путать Big Data с базами данных. Это совершенно разные понятия. Второй элемент относительно небольшой по сравнению с рассматриваемым термином.

Свойства

Любой крупной компании (да и мелкой при наличии перспектив роста и развитии) требуется BigData. Определяются они по трем свойствам:

1. Объем. БигДата – это просто огромный набор информации. Если бы не их размер, информация оказалась бы «обычной». С ней смог бы справиться любой компьютер.
2. Скорость. Big Data – материалы, которые с течением времени только увеличиваются. Прирост информации осуществляется с колоссальной скоростью. Все, происходящее вокруг людей, тем или иным способом производит новые сведения. Большинство из них прекрасно подходит для бизнес-разработок.
3. Многообразие. В БигДата включены такие особенности, как их разнообразие. Соответствующие «хранилища» забирают неоднородные электронные материалы. Представляются они совершенно разными способами: табличками, БД, числовыми последовательностями, медиафайлами и так далее.

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

1. Полезность. Свойство, которое каждой фирмой определяется в индивидуальном порядке. Специалист, работающий с большими датами, может найти так называемый КПД тех или иных сведений. Это помогает «отсеивать» лишнее.
2. Достоверность. Слово говорит само за себя. В бизнесе должны быть задействованы только полезные и актуальные материалы. Недостоверность приводит к серьезным негативным последствиям – как для предприятия непосредственно, так и для клиентуры.

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

Немного истории

Определение Биг Data – это только «верхушка айсберга». Разбираться с соответствующим понятием на самом деле весьма трудно. Особенно если не понимать, как проводить дальнейший анализ материалов.

Первые упоминания Биг Data появились в 60-70-х годах прошлого века. Тогда начался активный рост и развитие информационных технологий. И продолжается подобный прогресс по сей день. Это не может не отражаться на «дате» — то, что еще 10 лет назад казалось огромным объемом, теперь является «мелочью». Чтобы убедиться в этом, достаточно посмотреть на размеры памяти современных девайсов или «вес» выпускаемого софта.

С 2005 года организации начали потихоньку разбираться в масштабах софта пользовательских интернет сервисов – YouTube, OK, VK и так далее. Тогда же появилась одна из первых платформ для работы с большими объемами данных. Она получила названием Hadoop. Сегодня так называют суперкомпьютер, стек, предназначенный для Big Дата. Чуть позже мир узнал об еще одной технологии – NoSQL, которая представлена связью методов, которыми создаются системы управления Big Data.

Этапы работы с «Датой»

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

• интегрирование;
• управление;
• анализ данных.

У каждого этапа имеются свои проблемы, особенности и предназначение.

Интеграция

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

В процессе подключаются инструменты форматирования и обработки. Это помогает при дальнейшей работе с Big Data.

Осуществление управления

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

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

Проведение анализа

«Хранилища» полезны для бизнеса не сразу. Их польза начинается с момента анализа. Проводится операция специально обученными людьми – аналитиками Big Data. Данные обрабатываются при помощи разнообразных методик. К ним относят машинное обучение, регрессионный анализ и так далее.

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

Какими методами работают с BigData

Big Data предусматривают различные методы обработки. Они позволяют при помощи всевозможных информационных технологий работать с большим потоком информации. Обычным компьютерам такие задачи не под силу. Искусственный интеллект и нейросети – лидеры в соответствующей сфере.

Работа с данными может производится через:

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

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

Обучение машинного типа

Эта модель предусматривает:

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

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

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

Ассоциации

Еще одно решение для работы с Big Data. Применяется для того, чтобы:

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

Метод правил ассоциаций распространен в крупных торговых сетях, где для сбора и хранения применяются специальные устройства. Они называются POS-системы.

Древо классификаций

Big Data может помочь:

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

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

Социальные сети и настроения

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

Метод анализа соцсетей способствует:

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

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

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

Впервые эти методы оказались крайне полезными в телекоммуникации. С развитием IT они стали неотъемлемой частью анализа Big Data для компаний и организаций.

Генетические алгоритмы

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

Следуют генетические алгоритмы принципам работы эволюции. «Обращают внимание» и опираются на естественный отбор и всевозможных преобразованиях (мутациях).

Регрессионный анализ

Большой объем данных может обрабатываться путем метода регрессионного анализа. Этот поход в Big Data оказывается эффективным при:

• оценке, насколько клиентура удовлетворена теми или иными услугами/компаниями/товарами;
• определения влияния погоды на звонки в службы поддержки населения;
• выявлении того, как влияет район и размер жилья на его стоимость.

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

О сборе и обработке

В рассматриваемой и столь большой области приходится задумываться над тем, как собирать данные и обрабатывать их. «С ходу», «просто так» справиться с поставленной задачей не получится. Связано это с тем, что Big Data требует наличия большого пространства, а также ресурсов у задействованных устройств.

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

1. HPPC – большой суперкомпьютер с открытым исходным кодом. Называется DAS. Обрабатывает данные в режиме реального времени или в «пакетном состоянии». Все зависит от ситуации и настроек.
2. Hadoop – одна из первых и самых больших технологий обработки Big Data. Ориентирован на «пакетную» работу. Реализация осуществляется через несколько машин, которые после проводят масштабирование сведений до большого количества серверов.
3. Storm – удобная и универсальная система, предлагающая обработку в режиме реального времени. Подключает Eclipse Public License. Имеет открытые исходные коды.

Нет смысла использовать сразу все перечисленные инструменты. В зависимости от возможностей и потребностей специалисты выбирают те или иные варианты.

Актуальность и перспективы

Big Data вызывает немало вопросов. Эта область сегодня развивается весьма стремительно, но люди задумываются – а стоит ли вообще углубляться в соответствующую сферу деятельности. Ведь для того, чтобы добиться успеха в качестве аналитика «больших данных», придется изучить и усвоить немало информации.

Ответ однозначен – да. В России, Америке и других развитых странах вместе с «большими сведениями» с 2015 года началось развитие так называемого «блокчейна». Это – отличное дополнение изученного термина, обеспечивающее защиту и конфиденциальность электронных материалов.

Статистика показывает – инвестициями в Big Data занимаются почти все существующие крупные и известные корпорации. Кто-то — больше, кто-то – меньше. Анализ соответствующих данных помогает обнаруживать различные скрытые схемы. Они потребуются при разработке наиболее эффективных и инновационных технологий и бизнес-проектов. А если учесть не только то, какие определение имеет Big Data, но и перспективы развития IT, можно сделать вывод – большие данные со временем окажутся еще более ценными.

Советы будущим специалистам

Стать специалистом в рассмотренной области – дело не из простых. Москва и другие регионы России на данный момент предлагают довольно мало ВУЗов, в которых учат на специалистов пор «большим материалам».

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

• отыскать зарубежный ВУЗ, где обучают на BigData Engineer;
• посетить специализированные курсы с выдачей сертификата по упомянутому направлению.

Чтобы добиться успеха, придется интересоваться IT и математикой, а также информатикой. Знания программирования тоже окажутся не лишними.

Внимание: в России для обучения на BigData Engineer и изучения технологии Big Data чаще всего используются специализированные курсы. Они проводятся как оффлайн, так и онлайн. Вот некоторые из таких профессиональных курсов:

Источник