Припрема изворних података за изведену табелу

Имати податке у правом формату кључан је корак у стварању робусне изведене табеле без грешака. Ако то не учините на прави начин, можете имати много проблема са заокретном табелом.

Шта је добар дизајн изворних података за изведену табелу?

Погледајмо пример добрих изворних података за изведену табелу.

Ево шта га чини добрим дизајном изворних података:

• Први ред садржи заглавља која описују податке у колонама.
• Свака колона представља јединствену категорију података. На пример, колона Ц садржи само податке о производу и само колону Д и податке о месецима.
• Сваки ред је запис који би представљао једну инстанцу трансакције или продаје.
• Заглавља података су јединствена и не понављају се нигде у скупу података. На пример, ако имате продајне бројеве за четири квартала у години, НЕ би требали све то назвати продајом. Уместо тога, дајте овим заглављима колона јединствена имена, попут продаје К1, продаје К2 итд.
  • Ако немате јединствене наслове, и даље можете да креирате заокретну табелу, а Екцел би их аутоматски учинио јединственим додавањем суфикса (као што су Салес, Салес2, Салес3). Међутим, то би био грозан начин за припрему и употребу изведене табеле.

Уобичајене замке које треба избегавати приликом припреме изворних података

• У изворним подацима не би требало да постоје празне колоне. Овај је лако уочити. Ако у изворним подацима имате празну колону, не бисте могли да направите изведену табелу. Показаће грешку као што је приказано испод.
• У изворним подацима не би требало да постоје празне ћелије/редови. Иако можете успешно креирати изведену табелу упркос томе што имате празне ћелије или редове, постоји много нуспојава које вас могу угристи касније током дана.
  • На пример, рецимо да имате празну ћелију у колони продаје. Ако креирате изведену табелу користећи ове податке и поставите поље продаје у област колона, приказаће вам се ЦОУНТ а не СУМ. То је зато што Екцел тумачи целу колону као текстуалне податке (само због једне празне ћелије).
• Примените одговарајући формат на ћелије у изворним подацима. На пример, ако имате датуме (који су ускладиштени као серијски бројеви у позадини програма Екцел), примените један од прихватљивих формата датума. Ово би вам помогло да креирате изведену табелу и користите Датум као један од критеријума за сумирање, груписање и сортирање података.
  • Ако имате неколико секунди, покушајте ово. Обликујте датуме у изведеној табели као бројеве, а затим направите ове податке помоћу ових података. Сада у изведеној табели изаберите поље за датум и погледајте шта се дешава. Аутоматски ће га ставити у област вредности. То је зато што ваша изведена табела не зна да су то датуми. То тумачи као бројеве.
• Не уносите укупне колоне, укупне редове, просеке итд. Као део изворних података. Када добијете изведену табелу, касније их можете лако добити.
• Увек креирајте Екцел табелу, а затим је користите као извор за изведену табелу. Ово је више добра пракса, а не замка. Ваша изведена табела би радила сасвим добро са изворним подацима који нису ни Екцел табела. Предност програма Екцел Табле је та што може прилагодити проширене податке. Ако у скуп података додате још редова, не морате стално и изнова прилагођавати изворне податке. Можете једноставно освежити изведену табелу и она ће аутоматски узети у обзир нове редове додане изворним подацима.

Примери лоших дизајна изворних података

Погледајмо неке лоше примере дизајна изворних података.

Лош дизајн изворних података - Пример 1

Ово је уобичајен начин за одржавање података тако да их је лако пратити и разумети. Постоје два проблема са овим распоредом података:

• Не добијате потпуну слику. На пример, можете видети да је продаја за Средњи запад у првом кварталу 2924300. Али да ли је то појединачна продаја или већи број продаја. Ако имате сваки запис доступан у засебном реду, можете направити бољу анализу.
• Ако наставите и креирате изведену табелу користећи ово (што можете), добићете различита поља за различите квартале. Нешто као што је приказано испод:

Лош дизајн изворних података - Пример 2

Ова репрезентација података може бити добро прихваћена од стране менаџмента и публике ПоверПоинт презентација, али није погодна за креирање изведене табеле.

Опет, ово је врста резимеа који можете лако да направите помоћу изведене табеле. Дакле, чак и ако на крају желите такав изглед својих података, одржавајте изворне податке у формату спремном за заокрет и креирајте овај приказ помоћу изведене табеле.

Лош дизајн изворних података - Пример 3

Ово је опет излаз који се лако може добити помоћу изведене табеле. Али не може се користити за креирање изведене табеле.

У скупу података постоје празне ћелије, а четвртине су распоређене као заглавља колона.

Такође, регион је наведен при врху, док би требало да буде део сваког записа.

[СТУДИЈА СЛУЧАЈА] Претварање лоше форматираних података у изворне податке спремне за изведену табелу

Понекад можете добити скуп података који није погодан за употребу као изворни податак за изведену табелу. У том случају можда нећете имати другог избора него да податке конвертујете у формат података прилагођен заокрету.

Ево примера лошег дизајна података:

Сада можете користити Екцел функције или заокретни упит за претварање ових података у формат који се може користити као изворни податак за изведену табелу.

Погледајмо како функционишу обе ове методе.

1. метод: Коришћење Екцел формула

Хајде да видимо како да помоћу Екцел функција претворимо ове податке у формат спреман за изведену табелу.

• Направите јединствено заглавље колоне за све категорије у оригиналном скупу података. У овом примеру то би били регион, квартал и продаја.
• У ћелији испод заглавља региона користите следећу формулу: = ИНДЕКС ($ А $ 2: $ А $ 5, РОУНДУП (РОВС ($ А $ 2: А2)/ЦОУНТА ($ Б $ 1: $ Е $ 1), 0))
  • Повуците формулу надоле и она ће поновити све регионе.
• У ћелији испод заглавља четвртине користите следећу формулу: = ИНДЕКС ($ Б $ 1: $ Е $ 1, РОУНДУП (МОД (РОВС ($ А $ 2: А2), ЦОУНТА ($ Б $ 1: $ Е $ 1) +0,1)) , 0))
  • Повуците формулу надоле и она ће поновити све четвртине.
• У заглављу испод продаје користите следећу формулу: = ИНДЕКС ($ Б $ 2: $ Е $ 5, МАТЦХ (Г2, $ А $ 2: $ А $ 5,0), МАТЦХ (Х2, $ Б $ 1: $ Е $ 1,0 ))
  • Повуците га надоле да бисте добили све вредности. Ова формула користи податке о региону и кварталу као вредности претраживања и враћа вредност продаје из оригиналног скупа података.

Сада ове добијене податке можете користити као изворне податке за изведену табелу.

Кликните овде за преузимање датотеке примера.

2. метод: Коришћење програма Повер Куери

Повер Куери има функцију која лако може претворити ову врсту података у формат података спреман за Пивот.

Ако користите Екцел 2016, функције Повер Куери биле би доступне на картици Подаци у групи Преузми и трансформиши. Ако користите Екцел 2013 или старије верзије, можете га користити као додатак.

Ево одличног водича о инсталирању Повер Куери -а од Јона из програма Екцел Цампус.

Опет, с обзиром да имате податке форматиране као што је приказано испод:

Ево корака за претварање изворних података у формат спреман за изведену табелу:

• Претворите податке у Екцел табелу. Одаберите скуп података и идите на Уметни -> Табеле -> Табела.
• У дијалогу Уметање табеле уверите се да је изабран тачан опсег и кликните на дугме У реду. Ово ће претворити табеларне податке у Екцел табелу.
• У програму Екцел 2016 идите на Подаци -> Преузми и трансформиши -> Из табеле.
  • Ако користите додатак Повер Куери у претходној верзији, идите на Повер Куери -> Спољни подаци -> Из табеле.
• У уређивачу упита изаберите колоне које желите да уклоните. У овом случају, то су они за четири квартала. Да бисте изабрали све колоне, држите тастер Схифт, а затим изаберите прву колону, а затим последњу колону.
• У оквиру уређивача упита идите на Трансформ -> Ани Цолумн -> Унпивот Цолумнс. Ово ће претворити податке колоне у формат прилагођен изведеној табели.
• Повер Куери даје генеричка имена колонама. Промените ова имена у она која желите. У овом случају, промените Атрибут у квартал и Вредност у Продаја.
• У уређивачу упита идите на Датотека -> Затвори и учитај. Ово ће затворити дијалошки оквир Повер Куери Едитор и створити засебан радни лист који ће имати податке са неокренутим колонама.

Сада када знате како да припремите изворне податке за изведену табелу, спремни сте за Екцел у свету изведених табела.

Ево неких других водича за заокретну табелу који би вам могли бити корисни:

• Како освежити изведену табелу у програму Екцел.
• Коришћење резача у Екцел заокретној табели - водич за почетнике.
• Како груписати датуме у изведеним табелама у Екцелу.
• Како груписати бројеве у изведеној табели у програму Екцел.
• Заокретна кеш меморија у програму Екцел - шта је то и како је најбоље користити.
• Како филтрирати податке у изведеној табели у програму Екцел.
• Како додати и користити израчунато поље Екцел заокретне табеле.
• Како применити условно обликовање у изведеној табели у програму Екцел.
• Како заменити празне ћелије нулама у Екцел заокретним табелама.