Помогни ни да направим Uroci.net по - богат! Добави урок

Работа с оцветени клетки и текстове в Ексел 2003

oldstar   трудност:    видян: 108928

Работата с цветове в Ексел 2003 става малко по-сложно. За разлика от Ексел 2007, в 2003 няма вграден инструмент за сортиране на оцветени клетки и текст. Независимо от това, работата с оцветени клетки и текст, някои изчисления в 2003 могат да се извършват без и с макрос.

За целта в Ексел 2003 трябва да бъдат инсталирани  Analysis ToolPak I Analysis ToolPak-VBA – Tools –Add-ins-маркираме двете опции


БЕЗ МАКРОСИ

Преброяване

За преброяване  на цветните клетки или цветен текст може да се използва един прост начин

1.Edit-Find (Ctrl+F)Options-Format. От Format  посочване цвета на клетката (Pattern). Ако се търси цветен текст избираме съответно Font-Color



2.Маркираме колоната и Find All

3.В прозорчето излизат общия брой на оцветени клетки, адресите им, формули (ако са свързани) и съдържанието на клетката.


Ето как примерно би изглеждало едно преброяване на жълти клетки по този начин:




Сортиране и събиране

Сортиране

Това може да стане чрез въвеждане на една допълнителна колона, в която се прави отделна наша маркировка.

Как става?

а) сортиране на всички клетки                                                           

1. Създаваме допълнителна колона срещу записите, в която срещу еднаквите цветове правим наша маркировка (колоните трябва да имат антетки)

2. Маркираме цялата таблица и Data-Sort. Избираме начина на подреждане (без значение). Ако таблицата има анкета,  маркираме Header Row. В Sort by посочваме антетката на нашата маркировка и потвърждаваме. Ако няма анкета, маркиране No header row и в Sort by-колоната на нашата маркировка.

3.  Клетките се сортират една след друга по цвят.




б) Сортиране на определен цвят

1. Създаваме допълнителна колона срещу записите, в която срещу еднаквите цветове правим наша маркировка (колоните трябва да имат антетки)

2. Маркираме цялата таблица и Data-Filter-Autofilter

3. От стрелкичката на допълнителната колона избираме цвета на клетката (по нашата маркировка). В примера избираме с” за синьо. Сините клетки се сортират-подреждат се една след друга.




Забележка: Тази допълнителна колона с маркировката ни дава по-лесна възможност за преброяване на клетките.Ако искаме пак да преброим сините клетки в колоната, в празна клетка прилагаме формулата

=COUNTIF(B2:B11;"с"), където В е колоната(адресите на първата и последна клетка на колоната) с нашата маркировка, а „с” е съответния цвят. При втория случай (с филтър-поз.3), на празна клетка прилагаме формулката =subtotal(2;A2:A11), където А2 и А11 са първия и последния адрес на колоната с цветни клетки.

По същия начин се постъпва и при цветен текст.


Събиране на стойностите в еднакво оцветените клетки:

За събиране на стойностите в  оцветените клетки се прилага формулата      

=SUMIF(B2:B11;"с";A2:A11)


Обяснение на формулата:

-В колоната В (с нашата маркировка) се търси съответствие на „с” (синия цвят в случая)

- Намирайки съответствието в В, стойностите на съответвието  в колона А се сумират.

Така може да намерим сумата от различните оцветени клетки.

Ако искаме директно да получим сумата от стойностите на определен цвят, използвме метода за сортитране от по-горе и  в клетка А12 (под стойностите-поз.3), натискаме иконката за AUTOSUM  или изписваме формулата

=SUBTOTAL(9;A2:A11), където А2:А11 показва адресите на първата и последна клетка на колоната (без антетката)




След въвеждане на тази формула, при всяко ново избиране на цвят от нашата маркировка, под сортирането ще излиза общата стойност на клетките с избрания цвят. Ако ако се използвали и SUBTOTAL за броя на клетки, той щe излиза винаги при различните цветове

С МАКРОСИ.

Важно уточнение:

Наименованията на макросите по-долу са условни.  При смяна на името, съответната формула трябва да се съобрази с него - напр. Function CountCell – формулата е =CountCell(…)


Опитал съм се да дам повече варианти и примери  за работа с цветни клетки и текст,  като за някои от тях съм ползвал информация от Интернет. Макросите тук са възможно най-опростени и които с малки изменения да могат да се ползват за цветни клетки и за цветен текст.

Известно е, че всеки цвят има номер- цветови индекс (color index number). Точно той се използва тук при макросите. Цветовият индекс се прилага както при цветовете на клетките, така и за цвета на текста. Има много различни начини с макроси да се установи индекса. Тук ви предлагам един от тях.  

 

1.Отваряме Ексел и си правим колонка с клетки в различни цветове.

2.Tools-Macro-Visual Basic Editor (или едновремено натискане Alt+F11)  Отваря се  прозорчето на VB

3.Insert-Module Вътре изписваме следното:



4.Затваряме. File-Close and Return to Excel

Първата част (жълтата) се отнася до цвета на клетките, а втората (зелената) – до цвета на текста. Ако ще се проверява само цвета на клетките или текста, изписва се съответната част. Те могат да се комбинират (както е в случая), за да се сравнят индексите- за тест или за едновременна проверка.

В една празна клетка срещу първия цвят изписваме формулата  =intcolor(A1) и в друга празна се изписва формулата  = intfontcolor(A1).  Формулите се копират по цялата колона.



Както се вижда от картинката цвета на клетката съвпада с цвета на текста с две изключения - индекса на черния цвят на клетката е 1, а на текста (черен по подразбиране) е -4105. На белия цвят на текста индексът е 2, докато за белия цвят (или без цвят) на клетката индексът е -4142

Сотиране на клетки по цвят

Сортирането на клетки по цвят става по същия начин, както по-горе (с наша маркировка), но този път се използват номерата на индексите. След прилагане на Sort-Filter-Aurofilter, например избираме 3 за всички червени клетки, 5 за сини итн.

Преброяване на цветни клетки  или цветен текст/цифри

а) директно преброяване на определен цвят

Търсим в колоната клетки с жълт цвят

За целта се прилага следния макрос. В случая се преброяват клетките с жълт цвят.




- Ако искаме да сменим цвета на клетките при търсенето, променяме  съответно функцията (цвета) и индекса (жълтата част) - напр. за червен цвят става Red, index 3, за синьо Blue –index 5 итн.

- Ако искаме да броим клетки с цветен текст, заменяме Interior с Font. Вместо cell (зелената част),  пишем font. Функцията се променя съответно цвета.

В първия случай (за цвят на клетки) формулата е =Countyellowcell(А1:А11) –цялата колона, а при втория –цвят на текст =Countyellowfont(A1:11)

б) Ако сме установили предварително индексите на различните цветове, можем да направим  събирането и по цветови индекс

Чрез този макрос се събират само всички цветни стойности (цифри е еднакъв цвят) в колоната  в зависимост от цветовия индекс, без значение какъв е цвета на клетката.




Формулата е =sumcolor (A1:А11; 3) Формулата означава, че се търси сбора  на всички цифри в колоната, които изписани с червен цвят.

Ако искаме да намерим сбора на всички стойности в клетки с еднакъв цвят, променяме Font  на Interior и името на функцията , напр. Sunincolor. (зелената част). Тогава формулата става =sumincolor (A1:А11; 3). Формулата събира всички стойносте в клетките с еднакъв цвят, без значение от цвета на текста


Горният метод е  малко неудобен, тъй като трябва да се знаят индекситe. По-удобно е да сортираме-събиране  по „мостра”, т.е да се посочи наличен цвят в колоната и чрез него да се  извършат изчисленията.  Чрез долния макрос се показва броя на цветните клетки в колоната.



След като се изпиоше макроса, прилага се формулата

=Colorcount(А1:А11;А1),  където (А1:А11) е цялата колона,  А1 е произволна клетка от колоната с цвета, който търсим. Такъв цвят може да бъде посочен и в клетка извън колоната с друг адрес-като „мостра”

Чрез същия макрос (с малки преобразувания) може да се търси сбора от стойностите в клетките с еднакъв цвят. Преобразуванието е следното(променя се само зелената част):


1.Вместо ColorCount се пише ColorSum

2. ColorCount=ColorCount + 1 се замества с

If IsNumeric(cell.Value) Then ColorSum = ColorSum + cell.Value

И формулата става  =colorsum(A1:A11;A1). където (А1:А11) е цялата колона,  А1 е произволна клетка от колоната с цвета, който търсим. Такъв цвят може да бъде посочен и в клетка извън колоната с друг адрес-като „мостра


Това са по-основните моменти в работата с цветни клетки и текстове в Ексел 2003

За улеснение  съм качил всики макроси в Word цел направо копирането им в Ексел. Документа може да се изтегли от тук:                                                                            

http://dox.bg/files/dw?a=a539c9fc82



автор: oldstar



Коментари (2)

maribor на 02.08 2015 в 17:50ч.
Как да направя диагонали на клетка в таблица на Excel? Моля отговорете ми на SKYPE: bobi_t или на адрес: cfdib@abv.bg . Оставам с уважение и благодарност - Боби!
kiro17 на 22.05 2017 в 13:45ч.
Как да оцветявам автоматично клетки от различни шиитове ако се дублират- имена например ??

Регистрирайте се, за да добавите коментар


Калдейта ЕООД - © 2003-2010. Всички права запазени.
Препоръчваме: IT Новини