"Общий список"

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

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

Требования к списку данных, которые мы будем предъявлять:

1. Однозначная идентификация полезных данных в строке.
2. Понятные в работе названия столбцов.
3. Стандартные названия АЗС, товаров, месяцев (самый важный).

Другими словами, список для нашего примера должен однозначно идентифицировать данные по товарообороту по уникальному названию АЗС, по конкретному товару, за конкретный месяц, за конкретный год. Или, другими словами, уникальным ключом являются поля (АЗС, товар, месяц, год), повторение остальных полей допускается. Такая таблица не приводится к нормальным формам, так как человеку не может быстро использовать результаты. Такое представление данных нам пришло из Баз данных, которые стали использоваться в Excel.  Уникальный ключ напоминает адрес проживания человека в населенном пункте.

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

Требования к названиям АЗС, товара, года и месяца одно из самых важных при работе. Одинаковые по сути объекты к примеру «АЗС 1» и «АЗС №1» будут интерпретироваться ЭВМ как два совершенно разных объекта. Поэтому если в списке АЗС называется «АЗС 1» то название должно оставаться неизменным. Аналогичные требования накладываются и на названия товара, года и месяца. Так, месяц январь и 1 месяц - это тоже два разных объекта для ЭВМ. Не выполнение этого требования стандартных названий делает невозможным существование самого проекта. Спецификация названий должна исходить из одного источника, который регламентирует названия для каждого объекта. Обычно, если данные приходят из IT (обменные данные из Базы данных), то названия принимаются из этого источника. Вот пример корректного списка.

Приведем пример некорректного списка.

В таблице повторились данные по АЗС 1, за 1 месяц 2012 года по 1 товару.

На что может повлиять не соблюдение данных на уникальность? Происходит влияние на среднюю величину товарооборота. Например, АЗС 1 продал товара 1 в первом месяце 2012 года на 60 ед. При  расчете среднего товарооборота в месяц мы должны 60 ед. поделить на два (так как две записи), в результате получим 30 ед. в месяц.

Как организовать такую проверку на уникальность в Excel и как минимизировать ошибку в названиях объектов?

Решение поставленных задач организовывается пока несколькими рекомендациями:

1. Организация шаблона для вставки данных.
2. Сама проверка данных на уникальность.

Пренебрегая этими проверками, мы поставим под сомнение подведение итогов по каждой АЗС и сделаем некорректным подведение средних значений. Далее пойдет

Организация шаблона для вставки данных и проверка данных на уникальность

Шаблон обычно вставляется в конец списка и в него добавляются данные.

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

Итак, организация шаблона - задача практически тривиальная. Нужно заполнить 1 раз под поступающие данные, сделать новые строки в списке, для каждой АЗС и каждого товара. При этом значения товарооборота оставить пустыми или «0» . Пустые значения не портят средние значения (так как не берутся в расчет среднего), тогда как при операциях над множествами создают определенные неудобства.

На нашем примере будет все выглядеть следующим образом. Пусть поступают данные за 2 месяц 2012 года. Приведем пример шаблона для вставки данных:

Такой шаблон представляет декартовое произведение множества АЗС на множество товаров или, другими словами, по каждой АЗС нужно заполнить данные по товарообороту по каждому товару вставить «0» или пустое значение. Заполнить повторяющиеся поля: месяца, года и филиалов. Такой шаблон назовем «полным месячным шаблоном». Строк в таком шаблоне будет 200. Более подробную информацию о декартовых произведениях можно найти в любых книгах по базам данных.

Заполнение шаблона происходит обычным способом копирования данных, причем вы сможете сразу ответить на вопросы:

1. Полностью ли поступили данные по всем объектам (контроль данных).
2. Минимизировали ошибки в названиях.

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

Как можно еще создать шаблон? Ответ предполагает знание работы с фильтрам списков.

Понятие фильтра пришло к нам из работ с базами данных. Суть понятия заключается в следующем. Происходит отбор строк с заданным значением или с заданным условием. Фильтрация может происходить по произвольному количеству столбцов списка. В Excel разных версий и названия разные (2003 автофильтр, в 2007 фильтр), но суть осталось прежней – отбор строк по заданному значению (см. Работа с автофильтром). Далее нужно отфильтровать в уже имеющихся данных выбрать один год и один месяц (нажимать на стрелки). Скопировать данные и вставить в конец списка. У вставленных данных исправить год и месяц на нужные. Очевидно, что такой шаблон не полный, так как он формируется на основании фактических данных о продажах и на основе количества АЗС, которые действовали именно в тот момент времени. За это время могло измениться  количество АЗС и список продаваемых товаров на АЗС.

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

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

Борьба с пустыми и нулевыми значениями ведется при помощи фильтра. Отбираем нулевые и/или пустые значения и удаляем строки.

Осталось убедится в последнем: нет ли данных, которые нарушают уникальность выбранного ключа. Проверка очень простая. Нужно отсортировать строки в следующем порядке: год, месяц,  АЗС, товар (см. Работа с сортировкой). Этим мы добьемся, что повторяющиеся данные будут следовать друг за другом. Теперь нужна проверка на равенство ключевых значений. Выполнить равенство двух значений в ячейках можно следующей формулой «=a1=b1». «Истина», когда два значения равны, и «ЛОЖЬ», когда значения не совпадают.

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

Вставка данных в шаблон

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

1. Данные должны занимать свое место.
2. Копирование данных должно включать как можно меньше операций.

Рассмотрим два варианта. Первый данные прислали в виде списка и второй – в виде таблицы.

Вариант 1

При вставке данных в шаблон мы должны научиться контролировать порядок данных, так как шаблон, подготовленный нами, не всегда совпадет с присланным списком (увеличилось количество товара, изменилось количество точек и т.д.). Операция прямого копирования данных может обернуться настоящей катастрофой.  Изменится весь шаблон и нарушится уникальность ключа. Поэтому логично создать пустую книгу и там произвести объединение поступивших данных. Далее сортировками привести список к нашему списку (сортировка по полям в шаблоне) и произвести вставку данных, но опять не в шаблон, а правее. Далее сравниваем данные по ключу (сравнение по: году, месяцу, АЗС, продукту) функцией «=». Добиваемся соответствия шаблона и вставляемых данными по ключу. Копируем и вставляем только необходимые данные в шаблон.

Как видим, требуется только одна операция копирования и вставки.

Вариант 2

При вставлении данных из таблицы в список, оказывается полезной функция «специальная вставка». Галочку надо установить в «транспонировать». Тогда происходит преобразование строки в столбец, что ускоряет занесение данных. Нужно следить только за порядком вставления данных, что было рассмотрено в Варианте 1.

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

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

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