C, PHP, VB, .NET

Дневникът на Филип Петров


* Упражнение за Excel 5

Публикувано на 28 март 2016 в раздел УКИТ.

В това упражнение ще се запознаем с Conditional Formatting и диаграми в клетки. Първо изтеглете заготовката. Ще видите, че клетките в диапазона на седмиците първоначално ще са празни. От вас се очаква да направите така, че да се попълнят букви „X“ в клетките тогава, когато дадена седмица попада в интервала от начало до край (естествено трябва да използвате формула, а не да ги попълвате ръчно). Трябва да се получи следното:

1

Сега ще демонстрираме как да форматираме клетките на база на стойностите в тях. Маркирайте цялата област и отидете на Contitional Formatting > More Rules…

2

Ще видите екран сходен с показаната по-долу картинка. Изберете „Format only cells that contain“ и изберете условие, което казва „стойността в клетката да е равна на буквата X“. По този начин указваме на Excel, че преформатирането на клетката ще се извърши само и единствено когато това условие е вярно. Изберете някакво форматиране по ваш избор и вижте резултата.

3

Сега ще поискаме да направим нещо по-сложно – искаме да оцветим клетките с „X“ в конкретен цвят в записимост от това кой е отговорен за даденото действие. Отидете на „Manage rules“:

4

Добавете съответните правила както е показано на картинката. Когато добавяте правило, използвайте „Use a formula to determine which cells to format“ и след това използвайте функция AND, за да укажете двойно условие – хем клетката да е със стойност „X“, хем в колона „Изпълнител“ да има съответното име.

5

В крайна сметка трябва да получите нещо подобно на следния резултат:

6

Сега ще покажем един стар трик за показване на „in cell charts“ (симулация на диаграма вътре в клетка). Преди появата на Conditional Formatting това се правеше с функцията REPT, която кара даден символ да се повтаря множество пъти, в комбинация с форматиране на клетката. Копирайте само уникалните стойности от колоната „Изпълнител“ в B15 и надолу (използвайте за по-лесно Advanced Filter). До тях ще добавим колона „Натовареност“. Искаме в крайна сметка да изобразим следното:

7

За целта сме използвали следната 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. Ще се получи следното:

8

В по-новите версии на Excel същото може да се получи в много по-красиви варианти чрез Conditional Formatting. Направете същото преброяване, но този път без функцията REPT, т.е. това:

9

чрез формулата:

=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:

10

Ще видите, че имате различни заготовки за дизайн. В „More Rules…“ можете да настройвате по-фини детайли около дизайна, минимална стойност и т.н. Разгледайте ги.

Накрая ще разгледаме още един вид „графика в клетка“ – така наречените Sparklines. Добавете ред със стойности под седмичната графика, в който изчислете по колко човека общо работят над един и същи проект в дадена седмица:

11

На клетките под тях задайте „Merge“ така, че да стане една дълга клетка. След това маркирайте клетките със стойностите и отидете на Insert > Line:

12

В диалоговия прозорец посочете новата дълга клетка. Трябва да получите следното:

13

Разгледайте възможностите в раздел „Design“ на клетката. Сменете типа на диаграмата на Bar, предефинирайте минималната стойност по вертикалната ос и сложете маркер за най-висока точка, за да направите диаграмата да изглежда по следния начин:

14

 

 



Добави коментар

Адресът на електронната поща няма да се публикува


*