Създаването на табло за управление на Microsoft Excel включва извличане на информация от множество източници, за да можете да преглеждате тези данни на едно място. Excel предлага различни начини да направите това, което го прави един от най-мощните инструменти за отчитане на таблото, които можете да използвате.
Информацията в този урок се отнася за Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007 и Excel за Mac.
Какво е табло за управление на Excel?
Таблото за управление на данни е инструмент, който ви помага визуално да наблюдавате и анализирате показателите, които са най-важни за вас. Може да използвате табло за управление, за да наблюдавате ефективността на вашия бизнес, ефективността на производствения процес или работата на персонала във вашия отдел продажби.
Независимо от начина, по който използвате таблото за управление, концепцията винаги е една и съща: фоновите електронни таблици извличат данни от файлове, услуги или API връзки към бази данни и други източници. Основният лист показва данни от тези няколко листа на едно място, където можете да ги прегледате с един поглед.
Типичните елементи на таблото за управление на данни в Excel включват:
- Диаграми
- Графики
- Габарити
- Карти
Можете да създадете два типа табла за управление. За отчитане на таблото можете да създадете статично табло от данни в други листове, които можете да изпратите на някого в отчет на Word или PowerPoint. динамично табло за управление е това, което хората могат да преглеждат в Excel и то се актуализира всеки път, когато се актуализират данните в другите листове.
Внасяне на данни в табло за управление на Excel
Първата фаза от създаването на табло за управление на Excel е импортиране на данни в електронни таблици от различни източници.
Потенциалните източници за импортиране на данни в Excel включват:
- Други файлове с работни книги на Excel
- Текстови, CSV, XML или JSON файлове
- SQL база данни
- Microsoft Access
- Azure Data Explorer
- Facebook и други уеб страници
- Всяка друга база данни, която поддържа ODBC или OLEDB
- Уеб източници (всеки уебсайт, който съдържа таблици с данни)
С толкова много потенциални източници на данни, възможността какви данни можете да въведете в Excel, за да създадете полезни табла за управление, е неограничена.
За въвеждане на източник на данни:
-
Отворете празен работен лист в Excel. Изберете менюто Данни и в падащото меню Получаване на данни изберете типа данни, който искате, и след това изберете източника на данни.
Image -
Прегледайте файла или друг източник на данни, който искате да импортирате, и го изберете. Изберете Импортиране.
Image -
В зависимост от типа източник на данни, който изберете, ще видите различни диалогови прозорци за трансформиране на данните във формат на електронна таблица на Excel.
Image -
Листът ще се попълни с всички данни от външния файл или база данни.
Image -
За да опресните данните, така че да качват редовно всички промени, направени във външния източник на данни, изберете иконата Обновяване от дясната страна на Заявки и Връзки панел.
Image -
Изберете трите точки до връзката EDIT в долната част на прозореца за опресняване и изберете Properties.
Image -
Конфигурирайте данните да се опресняват от източника на данни на редовни интервали, като зададете Обновяване на всеки xx минути на какъвто и да е интервал, който искате да актуализирате данните.
Опресняването на данни от външни източници е полезно, но може да изразходва процесорно време, ако направите честотата на опресняване твърде честа. Изберете честота на опресняване, която поддържа данните актуализирани толкова често, колкото се променят при източника, но не толкова често, че да копирате само едни и същи данни всеки път.
Image - Повторете процеса по-горе в нови, отделни работни листове, докато не импортирате всички данни, които искате да използвате в новото си табло за управление.
- Накрая създайте нов работен лист, поставете го като първия работен лист в работната книга и го преименувайте Dashboard.
Как да създадете табло за управление на Excel
Сега, когато разполагате с всички необходими данни във вашата работна книга на Excel и всички тези данни се опресняват автоматично, време е да създадете своето табло за управление на Excel в реално време.
Примерното табло за управление по-долу ще използва данни за времето от уебсайтове от целия интернет.
Понякога, когато импортирате данни от външни източници, не можете да направите графика на импортираните данни. Решението за това е да създадете нова електронна таблица и във всяка клетка въведете =convert(и изберете данните от импортираната електронна таблица. За параметрите на единицата просто изберете същите параметри за преди и след. Попълнете целия лист със същата функция, така че всички данни да се копират в новия лист и да се преобразуват в числа, които можете да използвате в различните диаграми, които ще създадете за вашето табло за управление.
-
Създайте Стълбовидна диаграма за показване на една точка от данни. Например, за да покажете текущата относителна влажност (от 0 до 100 процента), трябва да създадете стълбовидна диаграма с 0 процента като най-ниска точка и 100 процента като най-висока точка. Първо изберете менюто Insert и след това изберете 2D Clustered Column стълбовидна диаграма.
Image -
В менюто Дизайн на диаграма от групата Данни изберете Избор на данни.
Image -
В прозореца Избор на източник на данни, който се появява, щракнете върху полето Диаграма от данни на диаграмата и след това изберете клетката в данните електронна таблица, която искате да покажете с тази стълбовидна графика.
Image -
Променете заглавието на диаграмата, за да съответства на данните, които показвате. Актуализирайте границите на оста, за да бъде от 0 до 100 процента. След това преместете диаграмата в областта на тирето, където искате да я покажете.
Image -
Повторете същите стъпки по-горе, за да създадете лентови диаграми за всички други отделни точки от данни, които искате да начертаете. Направете обхвата на оста минимален и максимален за тези измервания. Например добър обхват на барометричното налягане би бил 28 до 32.
Изборът на правилния диапазон от данни е важен, защото ако използвате само стойността по подразбиране, мащабът може да е твърде голям за данните, оставяйки предимно празни лентови диаграми. Вместо това дръжте минималния и максималния край на скалата на оста само малко по-ниско и по-високо от екстремните възможни стойности на вашите данни.
Image -
Създайте Линейна диаграма, за да покажете тенденция на данните. Например, за да покажете история на местните температури за вашия район, трябва да създадете линейна диаграма, обхващаща последния брой дни с данни, които можете да импортирате от таблицата на уебсайта за времето. Първо изберете менюто Вмъкване, изберете диаграмата 2D Area.
Image -
В менюто Дизайн на диаграма от групата Данни изберете Избор на данни.
Image -
В прозореца Избор на източник на данни, който се появява, щракнете върху полето Диапазон от данни на диаграмата и след това изберете клетките в данните електронна таблица, която искате да покажете с тази линейна диаграма.
Image -
Променете заглавието на диаграмата, за да съответства на данните, които показвате, и преместете диаграмата в областта на тирето, където искате да се покаже.
Диаграмите са много гъвкави, когато ги поставяте в табло за управление. Можете да промените местоположението, както и размера и формата на графичния модул. Използвайте тази гъвкавост, за да проектирате организирани табла за управление, които предоставят възможно най-много информация на потребителя в най-малко пространство.
Image -
Създайте Текстово поле за показване на низови данни от листове, които сте импортирали. Например, за да видите актуализации на предупрежденията за времето на вашето табло, свържете съдържанието на текстовото поле към клетка в импортирания лист с данни. За да направите това, изберете менюто Insert, изберете Text и след това изберете Textbox йени
Image -
Поставете курсора на мишката в полето за формула, въведете =и след това изберете клетката в импортираната таблица с данни, която съдържа низовите данни, които искате да покажете.
Image -
Изберете текстовото поле и използвайте прозореца Format Shape вдясно, за да форматирате областта за показване на текст във вашето табло за управление.
Image -
Можете също да сравните две точки от данни във вашите импортирани листове с данни, като използвате кръгови диаграми Например, може да искате да покажете относителната влажност под формата на кръгова диаграма. Първо изберете данните, които искате да покажете, и в менюто Insert изберете 2D Pie диаграма.
Кръговите диаграми сравняват две или повече стойности. Ако показвате процент като относителна влажност, може да се наложи ръчно да създадете друга клетка, която изважда тази стойност от 100% за втората стойност, с която да я сравните. Това ще доведе до кръгова диаграма, която показва процентната стойност като част от общите възможни 100 процента.
-
Променете заглавието на диаграмата, за да съответства на данните, които показвате, и след това преместете диаграмата в областта на тирето, където искате да се покаже.
Image - Добавяйки различни типове диаграми на данни, можете да създадете полезно табло, което показва всички типове данни в едно удобно табло.
Добавяне на визуална привлекателност и контекст с цвят
Друг начин да добавите яснота към таблото си за управление е като дадете на вашите стълбови диаграми градиентно запълване, което изобразява предупредителен цвят като червен за региони от данните, които може да не са добри.
Например, ако искате да покажете, че относителна влажност над 75% е неудобна, можете съответно да промените градиентното запълване на единичната лентова диаграма. Ето как.
-
Щракнете с десния бутон върху външната граница на стълбовидна диаграма и изберете Форматиране на област на диаграма.
Image -
Изберете иконата Fill в панела Format Chart Area и променете избора на Gradient fill.
Image -
Изберете иконата на всяко ниво по протежение на линията за запълване с градиент и променете цвета и тъмнината според това колко „добро“или „лошо“е това ниво. В този пример високата относителна влажност избледнява до тъмночервена.
Image - Повторете този процес за всяка диаграма, където добавянето на цветови контекст към диаграмата има смисъл за тази точка от данни.
Как таблата за управление на Excel се актуализират автоматично
След като създадете табло за управление, не е нужно да правите нищо, за да актуализирате графиките. Всички данни в тези диаграми и уиджети се актуализират, както следва:
- Листове с импортирани данни се опресняват на датата, която сте задали, когато за първи път сте създали импортирането на данни.
- Всички допълнителни листове, които сте създали, за да коригирате или преформатирате данните от импортираните листове, ще се актуализират с новите данни в тези листове.
- Всяка джаджа във вашето табло за управление се актуализира автоматично, за да покаже новите данни в тези актуализирани листове за диапазоните, които сте избрали, когато сте създали тези диаграми.
Тези актуализации се извършват автоматично, докато Excel е отворен.
Как да използвате таблата за управление на Excel
Създаването на табла за управление в Excel може да бъде полезно по много различни причини. Въпреки това е важно да ги създадете въз основа на конкретна нужда, вместо да се опитвате да изградите едно табло за управление, което прави всичко.
Например, ако сте мениджър продажби и се интересувате от наблюдение на ефективността на вашия екип по продажбите, тогава таблото за управление на мениджъра продажби трябва да се съсредоточи върху ключовите показатели за ефективност (KPI), свързани с ефективността на продажбите.
Този вид табло за управление не трябва да включва информация, която не е свързана с ефективността на продажбите, в противен случай таблото за управление може да стане твърде разхвърляно. Претрупаното табло за управление затруднява виждането на връзките на данните и моделите, които имат значение.
Други съображения при изграждане на табла за управление:
- Използвайте правилните диаграми за точните данни.
- Не използвайте твърде много цветове в цялото табло.
- Поставете таблото за управление с подобни данни и типове диаграми в общи блокове.
- Уверете се, че всяка диаграма показва прости етикети и не е твърде претрупана.
- Организирайте уиджетите в йерархия на важност, като най-важната информация е в горния ляв ъгъл на таблото.
- Използвайте условно форматиране, за да сте сигурни, че когато числата са лоши, те са червени, а когато са добри, са зелени.
Най-важното е да използвате креативност, за да проектирате табла за управление, които са информативни и интересни за използване.
