что такое плоская таблица с чистыми данными

data_client

Как создать плоскую сводную таблицу

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

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

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

1. Создаем сводную таблицу

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

2. Изменяем макет сводной таблицы на табличный

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

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

3. Удаляем итоговые строки

Для удаления итоговых строк кликните правой кнопкой мыши на любую итоговую ячейку, к примеру «Январь 2017 Итог». В появившемся контекстном меню выберите «Параметры поля» и там в блоке «Итоги» выберите «Нет«.

Итак, итоги удалены. Осталось добавить в пустые строки дублирующие данные и плоская сводная таблица будет готова!

4. Заполняем пропущенные данные

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

Источник

Что может сводная таблица (часть 1)

Сводная таблица – невероятно сильный инструмент анализа. Конечно, мы не спорим с дисперсионным анализом данных, с дескриптивной статистикой, с регрессивным анализом, с методами Machine Learning и BigData (графы, ближайший сосед и пр.), речь о том, что «сводная таблица» это самый простой и доступный прикладной инструмент, освоить который можно в течении 5-10 минут. При этом, по принципу Парето «80 на 20», знание такого простого инструмента поможет вам решить 80% всех задач, связанных с обработкой данных. Для освоения нам нужно немного совсем простой (специально упрощенной) теории. Дело в том, что Сводную таблицу можно построить только из таблицы определённого типа… (забегая вперед из «Плоской таблицы») Во-вторых, чтобы анализировать данные, нам нужно понимать, что такое данные (хоть вопрос и кажется тривиальным).

Итак мы имеем два основных вопроса.

1. НАШИ ВОПРОСЫ

Запоминаем. ВОПРОС 1: «Что такое «плоская таблица»? ВОПРОС 2: «Какие бывают данные»? ))

2. А теперь ПРИМЕР

Допустим вы производите и продаете какую-то продукцию. И хотите спланировать свою деятельность. Сколько вам произвести или закупить?

Вот ваша таблица со среднемесячными продажами. (Рисунок 1)

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

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

И вот тут-то вы призадумались! Как вам расположить поля? Можно использовать разные листочки, для каждого месяца свой лист (Рисунок 3)

Можно попробовать добавить строк в таблицу (Рисунок 4).

А что если появится еще что-то … это может быть что угодно, давайте придумаем пример, хотя он не важен.

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

Если появится еще что-то, как мы это разместим в таблице? Будем делать строчки под январем, февралем? Или разделим ячейку на несколько цветов? Почему вообще мы имеем ограничения, почему нам сложно, когда у нас появляется еще что-то? И как назвать это «еще что-то»?

Отметим, что у нас появились еще вопросы. Как назвать это «еще что-то», при появлении которого нам приходится перестраивать таблицу? Забегая вперед, это производная от ВОПРОСА 2 («Какие бывают данные»)?

И еще появился еще один вспомогательный ВОПРОС 3: «Почему сложно построить большие таблицы»? Пример не пока не помог нам ответить на ВОПРОС 1, ВОПРОС 2, а только добавил ВОПРОС 3.

3. ТОТ ЖЕ ПРИМЕР, ТОЛЬКО с другой стороны

Вторая таблица (там, где был Контрагент и Товар) будет выглядеть так (Рисунок 7). Теперь наши коробки лежат не на прямой, а на плоскости. По одной линии мы откладываем Контрагент 1, Контрагент 2, а по другой линии откладываем Товар 1, Товар 2…А на пересечении лежит наш коробок с цифрой, с ответом на вопрос, сколько Товара N покупает Контрагент N. Это двухмерный мир. Декартова система координат (кто помнит математику 6-го класса). Математики бы сказали: «Двухмерный массив данных».

Наша третья таблица выглядела бы так (нарисуем в ней циферки, чтобы легче было представлять). По одной прямой мы бы откладывали метки Контрагент 1, 2. по другой Товар 1,2. а по третьей Месяц 1,2…И на пересечении этих трех линий коробков был бы наш искомый коробок, с нужной нам цифрой. Это уже походе на трехмерный мир, и математики бы назвали бы это «трехмерный массив данных».

4. А ТЕПЕРЬ ОТВЕТЫ

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

А то, что мы откладывали как метки по прямым (Контрагент 1,2. Товар 1,2, Месяц 1,2..) — это были аналитические разрезы или характеристики данных, или еще говорят: «аналитики-разрезы-аналитические разрезы-характеристики данных- измерения»… (слово «измерение» скорее всего вам не знакомо, но программисты используют едва ли не чаще всего; почему, мы это узнаем чуть позже).

Кстати, в нашем примере была одна бумажка с в коробке, но это для простоты, на самом деле и данных может быть очень много, В нашем примере, например, % накапливаемой скидки за эту продажу, или за цена (если мы каждому клиенту продаем по своей цене, так называемая — ценовая дискриминация), себестоимость, но это легко представить, как просто 2,3… подписанные бумажки с цифрами в нашей коробочке.

«Почему сложно построить большие таблицы»? ОТВЕТ: потому что мы живем в трехмерном измерении, а аналитических разрезов бывает на-а-а-а-много больше, чем 3. Да даже когда их 3, мы уже вынуждены напрягаться, разные таблицы или расшифровка строк. А как же быть, если аналитик больше, чем три? (ЭТО ВОПРОС 4, но дадим на него ответ сразу.)

ВОПРОС: «А как же быть, если аналитик больше, чем три»? ОТВЕТ: использовать «плоскую таблицу». Плоская таблица дает возможность упорядоченно расположить таблицу с любым количеством Аналитических разрезов.

5. КАК СТРОЯТСЯ ПЛОСКИЕ ТАБЛИЦЫ

Посмотрим, как они строятся?

На том же примере: Сначала у нас была одна линия, по которой мы откладывали Контрагент 1,2.. Потом появилось «две пересекающиеся линии», на пересечении которых наши данные. Потом «три пересекающиеся линии». А что если эти линии не пресекать? Если они будут параллельными? А считать данные мы сможем прочтя строку (см. самую правую картинку на Рисунок 9). Контрагент 1, Товар 1, Март, можем добавить что-то еще, и в конце данные по продаже – 15. Кажется в такой таблице можно поместить бесконечное количество параллельных Аналитик и данных и бесконечное количество данных.

Забегая вперед, для каждого аналитического разреза и для каждого ресурса (для бумажки с цифрой) мы выделим один – «свой» столбец.

Кстати, таблицы, где есть пересекающиеся измерения – называются «кросc-таблицами», от английского cross – пересечение.

Кстати, почему программисты и математики часто используют слово «измерения», потому что это очень похоже на двух-мерный, трех-мерный, одно-мерный мир.

Для закрепления построим из нашей двухмерной кросс-таблицы — «плоскую таблицу». Вспомним ее (Рисунок 2)

Вырезаем «контрагентов» – они у нас в отдельной колонке (столбце ли – я до сих пор путаюсь, как правильно). Во вторую колонку (столбец ли) помещаем Товар, и заполняем Товаром 1. И потом на заполняем третью колонку (столбец) данными по продаже. (Рисунок 10) Возможно, это сложно давайте прямо по действиям…

Итак … По действиям …

Выделяем для Аналитики Товар 1-й столбец (колонку) И заполняем ее всеми нашими возможными контрагентами. (перебираем всех наших конрагентов) (Рисунок 11)

Теперь заполняем вторую колонку (столбец) Товаром, но так как товаров у нас много, то мы пока зафиксируем Товар 1. (Рисунок 12)

Теперь заполним (а можно просто вырезать из нашей двухмерной кросс-таблицы данные и подставить к нашим двум столбцам. (Рисунок 13)

И так и идем не переставая, пока не закончится товар. И вот что у нас получится (таблица сильно уйдет вниз, поэтому мы всю ее не будем представлять). ВОТ ОНА НАША «ПЛОСКАЯ ТАБЛИЦА» Пока двухмерная. (Рисунок 14).

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

Осталось немного… посмотреть как будет выглядеть плоская трехмерная таблица из нашего примера (Рисунок 13), ну правда только для января, но восприятия это уже не испортит, потому что нам нужно всего лишь перебрать таким же способом Февраль, март и т.д.

Вот мы и поняли, что данные бывают сами данные (или «данные-сами данные-цифры-измеряемые величины-ресурсы-цифры-показатели»)

Бывают «аналитики-разрезы-аналитические разрезы-характеристики данных- измерения-характеристики».

И мы всегда теперь отличим плоскую таблицу от кросс-таблицы.

Кстати, а вы всегда отличите данные от аналитических разрезов? Не торопитесь, данные это не всегда цифры, а аналитические разрезы, это не всегда строки. В вопросе: Сколько в классе школьников, у которых средний бал 3, 4, 5? Здесь, 3,4,5 – это разве данные или скорее это аналитические разрезы? Это аналитический разрез, а не «сами данные», хотя они выражены цифровыми значениями. В другую сторону пример привести сложнее, потому что мы измеряем (сами данные) это чаще все-таки цифры. Но мы можем измерять и, например, «счастлив» «не счастлив», в зависимости от аналитических разрезов «пол», возраст (опять цифры в аналитических разрезах данных), национальности, образования…

Eще один интересный момент, а почему мы говорим о «аналитических разрезах». Об этом можете прочесть в ЧАСТИ 2.

Источник

Аналитика данных: как построить дашборд в Excel

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

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

ОНЛАЙН-ШКОЛА ВИЗУАЛИЗАЦИИ ДАННЫХ EXCELLENT

Дашборд помогает решать задачи менеджерам по продажам, HR-специалистам, бухгалтерам, маркетологам, руководителям

Дашборд ― динамический отчёт, который состоит из структурированного набора данных и их визуализации на основе диаграмм, графиков и таблиц.

Основные задачи дашборда:

Построение дашбордов ― такой же hard skill, как владение формулами в Excel. По статистике, пользователь Excel среднего уровня может освоить этот навык за 20 часов обучения и практики.

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

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

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

Существует большое количество сервисов для бизнес―аналитики, такие как Tableau, Power BI, Qlik, DataLens, Google Data Studio. Самым доступным можно назвать Excel.

Главное и самое интересное в дашборде ― интерактивность.

Настроить интерактивность можно с помощью следующих приёмов:

Создаём классический дашборд для руководителя отдела продаж

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

Советуем проделать все шаги вместе с нами. Как говорит гуру мотивации Наполеон Хилл, «мастерство приходит только с практикой и не может появиться лишь в ходе чтения инструкций». Файл с данными для тренировки можно скачать здесь.

Собираем данные

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

Плоская таблица (flat table) ― двумерный массив данных, состоящий из столбцов и строк. Столбцы ― это информационные атрибуты таблицы, строки ― отдельные записи, состоящие из множества атрибутов.

Пример плоской таблицы:

В примере выше атрибуты — это «Наименование», «День», «Год», «Склад», «Продажи (тыс. руб)», «Менеджер», «Заказчик». Они вынесены в заголовок таблицы.

Эта таблица послужит основой для построения нашего дашборда по продажам.

Выбираем макет дашборда и цели

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

Также необходимо определиться с макетом — структурой — дашборда. Для начала достаточно будет прикинуть её на листе формата А4.

Пример универсальной структуры, которая подойдёт под любые задачи:

Количество информационных блоков может быть разным: это зависит от того, сколько метрик надо отразить на дашборде. Главное — соблюдать выравнивание по сетке.

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

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

Построим несколько сводных таблиц по продажам

— на основе таблицы с данными, приведённой выше в качестве примера плоской таблицы.

Таблицы будут показывать продажи по месяцам, по товарам и по складу.

Источник

Что такое плоская таблица с чистыми данными

Типичным примером плоской таблицы является адресно-телефонный справочник, или, например, такая вот таблица:

Здесь атрибутами являются Код заказчика, Предприятие, Представитель, Страна и Город. Названия атрибутов помещаются в заголовке. Первая по счету запись указывает, что представителем предприятия ООО Андрей и К является некий Стасов, и что местоположением предприятия является город Киев, Украина.

ПЛОСКАЯ ТАБЛИЦА КАК МОДЕЛЬ БАЗЫ ДАННЫХ

ПРЕИМУЩЕСТВА ПЛОСКОЙ ТАБЛИЦЫ

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

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

ЭЛЕКТРОННЫЕ ПЛОСКИЕ ТАБЛИЦЫ

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

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

Источник