Проект Excel

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

Мы будем анализировать сеть АЗС. В конце цикла статей я размещу два решения с комментариями.

Задача №1

Требуется наладить мониторинг 40 объектов (АЗС), которые торгуют 5 разными видами товаров и подчиняются 3 различным территориальным подразделениям (для простоты будем называть филиалами). Система мониторинга должна оперативно отвечать на следующие запросы:

1. Список объектов, где положительная динамика продаж товара (годовая, ежемесячная, квартальная);
2. Список объектов, где отрицательная динамика продаж товара (годовая, ежемесячная, квартальная);
3. Какая динамика продаж товара по продуктам (годовая, ежемесячная, квартальная);
4. Какая общая динамика продаж по объектам (годовая, ежемесячная, квартальная);
5. Какая общая динамика продаж по филиалу (годовая, ежемесячная, квартальная);
6. По каким объектам требуется провести дополнительное исследование и мероприятия;
7. Сравнительный анализ АЗС.

Список требований к системе:

1. Система должна гибко реагировать на добавление товара;
2. Система должна гибко реагировать на изменение количества объектов;
3. Система должна гибко реагировать на изменение филиальной подчиненности;
4. Система должна гибко реагировать на изменение количества показателей деятельности объекта (возможность добавить выручку, валовый доход, прибыль от продаж);
5. Как совместить систему анализа с существующей на предприятии системой сбора данных.
6. Организовать систему проверки первичных данных.
7. Давать оперативные ответы на заранее поставленные вопросы в течении 2-5 часов после поступления информации.
8. Анализ производит 1 человек.

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

Для наглядности мы выберем именно АЗС, так как у них количество нефтепродуктов составляет 3-5 наименований. Если увеличить количество наименований до 100, то использовать Excel будет очень проблематично. Придется выделять группы товаров и пересматривать саму идею хранения информации в Excel.

Пути реализация проекта

Рассмотрим три конкурирующие парадигмы построения хранилища (возможные):

1. Каждая АЗС хранится на отдельном листе. Для каждого вопроса организуем дополнительные листы с результатами. Назовем такое решение «объектная реализация».
2. Данные хранятся в общей куче (в виде списка). Для каждого вопроса строится отдельная диаграмма. Такое решение назовем «общий список».
3. Данные хранятся в виде списков, разбитых по критериям (симбиоз двух подходов). Для каждого объекта или вопроса строятся отдельные диаграммы. Для удобства такое решение назовем «смешанным решением».

Преимущества и недостатки таких решений:

За месяц нам поступает не больше 40*5 = 200 данных. Проект «Объектная реализация» и «Смешанное решение» может принимать данные в течение практически неограниченного периода времени. А проект «Общий список» может принимать данные (к примеру Excel 2003) 65 536/200 ≈ 327 месяцев или 19 лет.

Заполнение данных

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

Данные в виде списка:

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

Таблица копируется в любые три решения («Объектная реализация», «Смешанное решение» и «Общий список») за количество операций «копирование и вставка» больше, чем само количество объектов (АЗС) (то есть более 40 операций копирования и вставок).

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

Плюс ко всему при «объектной реализации» требуется 40 переключений между листами.

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

Контроль данных

Для обеспечения контроля данных можно использовать любые возможности. Чем больше контроля, тем вы более спокойны за качество данных. Основная процедура контроля данных – это проверка контрольной суммы. Такое понятие широко распространены в вычислительной технике. Почти до 90 % ошибок могут быть обнаружены по «контрольной сумме».

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

Подсчитаем количество данных в месяц для решения «Объектная реализация». Контрольные суммы должны пойти по 3 филиалам  и по 5 продуктам 3*5 = 15. Итого получается 15 и 1 итоговая по всем филиалам, 1 - по всем продуктам и 1 - общая = 18. Количество переключений по всем листам для организации контрольных сумм составит 18*40=720!!!. Посчитаем время. Пусть переключение между листами требует в среднем 10 сек./лист + 5 сек. на нахождение нужного значения. Получаем 15*720 = 10 800 сек. или 3 часа чистого рабочего времени. Это означает, что контроль данных - очень трудоемкая задача, которой зачастую пренебрегают обычные пользователи. В итоге получается, что все проекты или большинство таких проектов имеют славу ненадежных и для анализа брать такие данные очень рискованно.

В других двух проектах все проще. Организация контрольных сумм в проектах «общий список» или «смешанное решение» требует всего одной операции с готовым списком (сводная таблица) и анализ полученных результатов. Обычно проверка не превышает 1-7 минут на весь проект.

Как мы видим, «объектная реализация» является наиболее неэффективной организацией работы в Excel. Система очень просто реализуется рядовыми пользователями, но при росте данных система начинает съедать все свободное время пользователя. «Объектная реализация» эффективно работает при малом количестве объектов анализа и при неизменности набора данных.

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

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

Схема-алгоритм

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

• Вконтакте ()
• Google+
• Facebook ( )

• < Назад
• Вперёд >