* Упражнение за Excel 5
Публикувано на 28 март 2016 в раздел УКИТ.
В това упражнение ще се запознаем с Conditional Formatting и диаграми в клетки. Първо изтеглете заготовката. Ще видите, че клетките в обхвата на седмиците първоначално ще са празни. От вас се очаква да направите така, че да се попълнят букви „X“ в клетките тогава, когато дадена седмица попада в интервала от начало до край (естествено трябва да използвате формула, а не да ги попълвате ръчно). Трябва да се получи следното:
Сега ще демонстрираме как да форматираме клетките на база на стойностите в тях. Маркирайте цялата област и отидете на Contitional Formatting > More Rules…
Ще видите екран сходен с показаната по-долу картинка. Изберете „Format only cells that contain“ и изберете условие, което казва „стойността в клетката да е равна на буквата X“. По този начин указваме на Excel, че преформатирането на клетката ще се извърши само и единствено когато това условие е вярно. Изберете някакво форматиране по ваш избор и вижте резултата.
Сега ще поискаме да направим нещо по-сложно – искаме да оцветим клетките с „X“ в конкретен цвят в записимост от това кой е отговорен за даденото действие. Отидете на „Manage rules“:
Добавете съответните правила както е показано на картинката. Когато добавяте правило, използвайте „Use a formula to determine which cells to format“ и след това използвайте функция AND, за да укажете двойно условие – хем клетката да е със стойност „X“, хем в колона „Изпълнител“ да има съответното име.
В крайна сметка трябва да получите нещо подобно на следния резултат:
Сега ще покажем един стар трик за показване на „in cell charts“ (симулация на диаграма вътре в клетка). Преди появата на Conditional Formatting това се правеше с функцията REPT, която кара даден символ да се повтаря множество пъти, в комбинация с форматиране на клетката. Копирайте само уникалните стойности от колоната „Изпълнител“ в B15 и надолу (използвайте за по-лесно Advanced Filter). До тях ще добавим колона „Натовареност“. Искаме в крайна сметка да изобразим следното:
За целта сме използвали следната Array Formula:
=REPT(„n“; SUM(IF(B15=$B$4:$B$10;{1;1;1;1;1;1;1}+$D$4:$D$10-$C$4:$C$10);0))
Сега отидете на формата на клетката и задайте шрифт Wingdings. Ще се получи следното:
В по-новите версии на Excel същото може да се получи в много по-красиви варианти чрез Conditional Formatting. Направете същото преброяване, но този път без функцията REPT, т.е. това:
чрез формулата:
=SUM(IF(B15=$B$4:$B$10;{1;1;1;1;1;1;1}+$D$4:$D$10-$C$4:$C$10);0)
Сега маркирайте четирите клетки и отидете на Conditional Formatting > Data Bars:
Ще видите, че имате различни заготовки за дизайн. В „More Rules…“ можете да настройвате по-фини детайли около дизайна, минимална стойност и т.н. Разгледайте ги.
Накрая ще разгледаме още един вид „графика в клетка“ – така наречените Sparklines. Добавете ред със стойности под седмичната графика, в който изчислете по колко човека общо работят над един и същи проект в дадена седмица:
На клетките под тях задайте „Merge“ така, че да стане една дълга клетка. След това маркирайте клетките със стойностите и отидете на Insert > Line:
В диалоговия прозорец посочете новата дълга клетка. Трябва да получите следното:
Разгледайте възможностите в раздел „Design“ на клетката. Сменете типа на диаграмата на Bar, предефинирайте минималната стойност по вертикалната ос и сложете маркер за най-висока точка, за да направите диаграмата да изглежда по следния начин:
Добави коментар