Други уроци
Adobe Photoshop
Adobe Illustrator
Adobe Flash
Adobe Fireworks
DreamWeaver
CSS и HTML
Corel Draw
Image Ready
PHP
SEO
CMS
Microsoft Windows
Microsoft Word
Microsoft Excel
PowerPoint
Microsoft Access
Microsoft Publisher
Linux
Visual basic
JavaScript
Ajax
3ds Max
Maya 3D
C++
Sound Forge
Gimp
SWiSH
.jpg)
Работа с оцветени клетки и текстове в Ексел 2003
видян: 108958
Работата с цветове в Ексел 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