#033 R1C1 адреси и преобразуване на имена на колони в номера и обратно...

За по-лесното боравене с адреси по подразбиране Excel борави с така наречената A1 нотация... Т.е. "име на колоната и номер на реда". Съществува и втори вид наречен R1C1... При не го се посочва номер на реда(първо е ред!) и НОМЕР на колоната. Преди числата се постановя R и C. Например R1C1 е клетката $A$1 (първа колона първи ред). R3C2 е $B$3 (ВТОРА колона и ТРЕТИ ред!). Обърнете внимание и на още една особеност. Не случайно дадох отговорите като абсолютни адреси ($A$1 и $B$3)!  Задаването на относителни адреси е по-особено. Например ако адресът е R[-1]C означава "клетката с адрес един ред нагоре спрямо текущата клетка и същата колона"! Например ако сме в клетка G5 става въпрос за клетката G4! Адресът R[+1]C[-1] ако сме B3 е еквивалентен на A4 (ред надолу и колона на ляво)... И т.н... За повече информация  прочетете тук:

http://office.microsoft.com/bg-bg/excel-help/HP005198323.aspx където има информация и как да си включите/изключите този режим на показване на работните листи (не че има някакъв смисъл де):) Използването на R1C1 адреси е доста често използван в макросите за това и "подгрявам" публиката с тази тема:)

Вчера ми се наложи да правя преобразуване на номер на колона в име и преобразуване на име на колона в номер... Покрай тези примери ще разгледам и  две "екзотични" функции:)

Пример 1. Да се преобразува име на колона в число... Името се намира клетка A1 (например XZ).

=COLUMN(INDIRECT(A1&":"&A1)) Тук няма никаква магия. Създава се валиден адрес и се взема неговата колона. Адресът се получава като се долепят името на колоната сама до себе си (получава се  "XZ:XZ"), чрез Indirect се преобразува в зона и чрез Column се преобразува в число. Със същия успех може да се "сглоби" адрес като се долепи към името на колонката някакво число за номер на ред. Например A1&1 което е и по-кратко!

=COLUMN(INDIRECT(A1&1))

Пример 2. Да се преобразува номер на колона в нейно име... Номера се намира в клетка А1 (например 156).
=MID(ADDRESS(1,A1),2,FIND("$",ADDRESS(1,A1),2)-2)
Тук "магията" е във функцията ADDRESS. Пълното описание на български може да намерите тук: http://office.microsoft.com/bg-bg/excel-help/HP010062407.aspx

ADDRESS(1,A1) За разлика от Indirect, която преобразува текст в зона, функцията Addrеss преобразува числа (ред и колона) в адрес. В нашия случай получавам адресът на клетка с ред едно (използваното число няма значение) и НОМЕРА на колоната посочен в клетка A1. Например за числото 156 функцията ще върне $EZ$1

(по подразбиране връща абсолютен адрес) след което извличаме с Mid знаците от втория (първия е $!) до следващия (обърнете внимание на Find) намерен знак $ (ако имате проблеми с Mid/Find комбинирането пишете да за да спретна някоя темичка):)

Пример 3. Да се намери адресът на средната клетка в листа. Да не се влияе от версията на Excel (2003 и 2007 имат различен брой редове и колони!)

=ADDRESS(SUMPRODUCT(MAX(ROW(A:A))) /2, SUMPRODUCT(MAX(COLUMN(1:1))) / 2) или CSE {=ADDRESS(MAX(ROW(A:A)) / 2, MAX(COLUMN(1:1)) / 2)}
"Пакетират" се функциите Max със Sumproduct за да се намери най-големия ред (колона) на дадена зона... Ако премахнем Sumproduct трябва да въведем формулата като CSE! Номерата и колоните се делят на две (търсим средата) и се дават като параметри на Address....

----
Забележка под линия:):) Има различни варианти за правенето на нещо... Често и аз бъркам и не винаги ви давам най-добрия вариант забравяйки някоя функция на Excel:):) Когато за първи път правих примерът със средата бях изключил за Address... И го направих по много екзотичен начин:)
=CELL("address",INDIRECT("R" & SUMPRODUCT(MAX(ROW(A:A))) / 2 & "C" & SUMPRODUCT(MAX(COLUMN(1:1))) / 2,FALSE))
Използвах два "трика". Първия трик е възможността Indirect да работи с R1C1 зони (обърнете  внимание на параметърът False. (просто "слепвам" числа и буквите R и C!). Зоната получена от Indirect я давам като параметър на функцията Cell за да превърне зона в адрес. Функцията Cell е много екзотична и много рядко се използва:) http://office.microsoft.com/bg-bg/excel-help/HP010062392.aspx . Както споменах посоченото решение е много тромаво и объркано и ме е леееко срам от него, но нали в крайна сметка работи:):):)
Но както казват хората ... Many (not All) Roads Lead to Rome  :):)

Бързата кучка слепи ги ражда:) Вместо да се правя на умен вместо MAX(ROW(A:A) просто може да се напише Rows(A:A) и съответно Columns(1:1)...

#032 Функция SubTotal и защо е по-добра от Sum

Да погледнем #030 в реда за обобщение. Там намираме не Sum и Average ами функцията SubTotal. Да се замислим защо Excel използва нея а не "класическите" функции. SubTotal не се преподава, но е доста по-мощна и гъвкава от Sum и т.н. Та в общи линии първия параметър е число показващо каква е използваната функция а втория е самата зона. Ето стойностите на първия параметър:

Функция_ном (включва скрити стойности)	Функция_ном (игнорира скрити стойности)	Функция
1	101	AVERAGE
2	102	COUNT
3	103	COUNTA
4	104	MAX
5	105	MIN
6	106	PRODUCT
7	107	STDEV
8	108	STDEVP
9	109	SUM
10	110	VAR
11	111	VARP

Вижда се, че имаме две възможности за всяка функция. СЪС и БЕЗ да се включват скритите стойности! Тук се крие силата на тази функция!  По време на правенето или използването  на една таблица "скриваме" редове или колонки както с помощта да Hide или с помощта на филтрите. Сега е моментът да се замислите че SUM смята ВСИЧКО което му е подадено като параметър без да го е грижа какво се вижда и какво не! Т.е. "това което виждате може да НЕ е това което се сумира"!:) За разлика от SUM/Average и т.н. SubTotal ви дава право на избор!

=SubTotal(9,A1:A100) е пълен аналог на =SUM(A1:A100) сумирайки независимо дали са видими, докато =SubTotal(109,A1:A100) ще зависи кои клетки от зоната са видими! Този "малък" на пръв поглед нюанс може да ви създаде главоболия:) Забелязал съм, че рядко се набляга на този "дефект" на класическите функции и сякаш никога няма да мине през акъла на някой да скрива или филтрира:):)

Както казах обаче при таблиците в реда за обобщаване Excel "мъдро" слага правилните функции (т.е. SubTotal с отчитане на скритите редове). Ако не искате това ще се наложи да подмените предложените от Excel функции с "вашите" любими такива:) Чара на SubTotal е че може да работи и като класическа функция, за това просто сменяте първия параметър и сте ОК:)

NB! Да бъдем коректни не винаги е възможно използването на SubTotal! Тя не възприема така наречените 3D зони (зони които са между няколко работни листа). Например =SUM(Sheet1:Sheet4!A1) няма как да я подмените с SubTotal! За това не бързайте да погребвате Sum, Average и т.н. :):)

Като бонус ето как се прави табличка за демонстрация възможностите на SubTotal:)

Табличка за SubTotal

Условие: Когато потребителя пипа C1 и C2 да се вижда правилния резултат.

0. Подготовка. Колонките D, E и F са помощни и спокойно може да са на друг лист да не загрозяват пейзажа:) Или просто ги скрийте когато приключите с настройките и видите, че всичко е ОК

1. Дефиниране на имена. Понеже все забравям да направя тема за именуване сега ще пиша много:( Трябват ни три имена "Функции", "Всички", "Видими" за данните в помощните колонки. Най-бързия начин е да изберете клетките от D1 до F12 (данните с имената!) и да изпълните командата Formulas/Define Names/From Selection и да посочите че използвате и заглавния ред и първата колона. Така ще получим освен трите имена на колонките и имената "Average" за първите две стойности, "Count" за вторите и т.н.

Създаване на имена

Резултат от именуването (проверка чрез Formulas/NameManager)

Тук има един малък проблем. Името "Функции" сочи към данните а ние искаме да сочи към имената на функциите. За това трябва да оправим този проблем като редактираме името от Name Manager.

Редактиране на името "Функции"

2. Контрол на C1 и C2. За да може потребителя да избира стойности за контролиране на C1 използваме списъка "Функции",  а за C2 зоната E1:F1. (Вижте #002).

Контрол на C2

3. Формула в C3. Ще дам два варианта да е по-весело:) И да имате теми за размисъл и четене:)

Вариант 1: =SUBTOTAL(INDEX(INDIRECT(C2),MATCH(C1,Функции,0)),A1:A24)

Тук извличаме номера на функцията чрез Index:

INDIRECT(C2) - "Обръща" съдържанието на клетка C2 в зона. (Всички/Видими). Така получаваме зоната от която ще извличаме числото.

MATCH(C1,Функции,0) -  Тук намираме редът от който извличаме търсейки съдържанието на C1 в зоната Функции (за това и се наложи корекция!)

Вариант 2: =SUBTOTAL(INDIRECT(C1) INDIRECT(C2),A1:A28) 

 

Извличането на стойността е в израза:

INDIRECT(C1) INDIRECT(C2) - Това е странен израз. Две зони разделени с интервал. Тук хитростта е че се намира "сечението" на двете зони. Зоната C1 (това е хоризонталната зона на съответната функция) определя реда, Зоната C2 пък представяла колоната. Например ако сме избрали Max и Видими  се търси сечението между E5:F5 (зона Max) и F2:F12 (зона Видими). Сечението (общото между двете зони) е клетка F5. Която и съдържа това което ни трябва!

Явно и тема за зоните трябва да понапиша:)

Успех със SubTotal:)

#031 Таблици (имена и формули)

Както споменах при използването на таблици се появява още една "екстра", която е свързана с автоматично дефинирани имена за създадената таблица. Ще използвам създадената в #030 таблица. 

Примерна таблица

Както вече се видя в #030, че се използваха имена от вида [Цена] и [Стойност]. Тези имена се създават автоматично от заглавията на таблицата. Различното на тези имена е че те са видими САМО вътре в самата таблица. За тяхното разграничаване се използва [] за ограждане.

Малко анализ на формулата в колонка Стойност. Тя беше =[Цена]*[Количество] . Тук трябва да се има предвид, че става дума за цяла зона клетки, но при тяхното умножение се връща резултатът за съответния ред (цената от съответния ред умножена по количеството от съответния ред). Звучи объркващо, но Excel си знае работата:) За да ви объркам още повече да ви кажа че същата формула може да се запише и като =[@Цена]*[@Количество] :) Знакът @ означава текущия ред. Т.е. изрично показваме, че искаме да умножим данните от текущия ред. Както посочих Excel в зависимост от контекста избира и правилната формула. 

Все пак да видим разликите. Ако в даден ред в таблицата (NB! имената са видими само в таблицата!) напишем формулата =max([Цена]) в този случай excel ще върне максималната от ВСИЧКИ цени! Ако напишем =max([@Цена]) ще се върне максималната цена от ТЕКУЩИЯ ред (примера е безсмислен, но искам да усетите разликата между двете формули! Виждал съм доста примери в които се слага @ да се подчертае фактът, че става дума за текущия ред. Както се казва "не пречи". И като се замисли човек е по-ясно да се запомни, че [Стойност] е зона а [@Стойност] е единична клетка, а не да разчита на Excel да се сеща:)

Това е за "вътрешността" на таблицата. Да видим как изглежда таблицата от "външния" свят. В областта с имена се е появило само името на самата таблица (в нашия случай Фактура). То ни дава възможност да извличаме данни от вътрешността на таблицата индексирайки редовете или колонките. Освен имената на колонките (които обхващат само данните за съответната колонка!) може да използваме няколко служебни имена:

#all - цялата таблица

#data - Само областта за данни (без заглавие и обобщаващия ред)

#headers - само заглавията

#totals - само обобщаващия ред

Ето примери и обяснения. Посочените формули се въвеждат ИЗВЪН таблицата!

• =MAX(Фактура[Цена])  - връща най-голямата цена
• =SUM(Фактура[Стойност]) - Сумата на колонка Стойност
• =Фактура[[#Totals],[Стойност]] - Връща стойността на клетката ПОД колонката Стойност. Прилича на предходния пример (ще върне същия резултат), но има различен смисъл и зависи от състоянието на таблицата.  В нашия случай това наистина е сумата на всички елементи, но при филтриране или смяна на функцията ще се върне друг резултат. Обърнете внимание на синтаксисът. Когато имаме няколко елемента те също се заграждат в [] !
• =Фактура[[#Headers],[Количество]] - Заглавието на колонка Количество (не много мъдър пример):)
• =INDEX(Фактура[#Headers],2) - Името на втората колона! Както се вижда може да използваме Index и другите функции както ги прилагаме върху имена! (NB! Двойката е параметър на Index!)
• =INDEX(Фактура[Цена],3) - Връща третата цена. Обърнете внимание на една приятна възможност на Index сравнявайки този и предходния пример. Когато блока е единичен ред или колона числото Excel сам преценява, че става дума за отместване надясно или отместване надолу!
• =INDEX(Фактура[Цена],MATCH("Име 3",Фактура[Наименование],0))  - цената на стока с наименование "Име 3"
• От мен да мине един Vlookup пример (скоро пак участвах в едно меле в което оплюхме здраво Vlookup;) =VLOOKUP(10,Фактура[#Data],4,False)   връща количеството (стойността на четвъртата колонка) за стока с номер 10. 
• =SUM(Фактура[[Цена]:[Стойност]]) - Сумата на всички колонки в зоната Цена-Стойност. (Цена, Количество, Мярка, Стойност)

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

Успех:)

#030 Таблици (създаване)

Скоро се замислих каква е ползата да има понятието таблица в Excel. Нали всичко е таблица в един работен лист? В тази тема ще се опитам накратко да покажа ползите и "триковете" при работа с таблици. По същество таблиците представляват обособени и именувани (!) части от работния лист.

1. Създаване
- Въвеждам заглавията на колонките и избирам колко реда ще бъде таблицата
- Изпълняваме Insert/Table и указваме че имаме заглавни редове!
- Даваме име на таблицата Фактура
- Въвеждаме формулата за стойност (=[Цена]*[Количество]).
- Добавяме сумарен ред (избор на Total Row от лентата Design)
- Определяме къде и какъв тип изчисления да се правят в сумарния ред

Ето кратко филмче:




При създаването на таблицата веднага се виждат няколко неща на които ще обърна внимание:

• Автоматично активиране на филтрите за всяка колонка (стрелките до всяка колонка). Ще направя отделна тема за работа на филтрите.
• Улеснение при въвеждане на формули (обърнете внимание на създаването на формулата в колонка Стойност). Формулата сама се размножи до края на таблицата и в нея използвах имена (натискайки знака "[") които автоматично са създадени! Ще направя отделна тема за имената в една таблица и начините за работя с тях. Има доста "магии" в тези формули:)
• Лесно добавяне на сумарен ред под таблицата с избор на видовете аритметични операции. За използваната функция SubTotal ще направя отделна тема.
• Не на последно място при създадена таблица и нейния избор се появява нова лента TableTools Design (най в дясно) за лесно оформяне на таблицата.

Както споменах ще има още теми за таблиците с които ще се опитам за ви "зарибя" и покажа че доста ви улесняват животът:) За това не пренебрегвайте тази възможност за създаване на лесни за разбиране и добре оформени таблици.

П.П Съжалявам, че във филмчето липсва курсорът, но в момента си правя експериментирам в търсенето на най-добрия софтуер за запис.:)

#029 Условно събиране с DSUM

Това е продължение на #028. Много често се подминават така наречените функции за работа с бази от данни ( Database или D-функциите). Може би ще направя отделна тема за тях. При тях проблемът е че на пръв поглед изглеждат "разхвърляни". От една страна имаме входна област (база данни) от друга имаме зона критерии. Това разпределяне обаче опростява много самите формули. Ето примера за условно сумиране.

Условно форматиране

Тук имаме две зони. Зоната в която са данните и зоната за критерии. Както казах тази тема няма за цел да влиза в детайли и да разглежда видовете условия и начини за формиране на зоната за критерии. Само искам да обърна внимание, че при зоната за критерии се посочва СЪЩОТО име на колонка (колонки) както са в областта за данни!

Формулата за сумиране е: =DSUM(A1:B6,B1,D1:D3)
- Данни (заедно със заглавния ред)
- Какво сумираме (само заглавната клетка)
- Зоната с критерии (заедно със заглавния ред)

Това е:) Очаквайте още за D-функциите скоро:)

#028 Условно събиране с ИЛИ

Попитаха ме как да се намери сумата по условие когато става дума за ИЛИ.

Примерни данни

 

Въпросът беше да се намери сумата на клетките в колонка B за стойностите в колонка А "aaa" И "bbb". Въпросът не е коректен, защото просто няма как да са хем "aaa" И "bbb"":)  Връзката между двете условия  е ИЛИ! Това е важно за да може и формулата да бъде вярна.

Ако опитаме да приложим мощната функция SumIfS (която я има само в Excel 2007 и следващи) и напишем =SUMIFS(B2:B7,A2:A7,"aaa",A2:A7,"bbb") ще получим НУЛА! Защото тази формула означава "Сумирай клетките от колонка B за който първото условие (А2:A7="aaa") И второто условие (A2:A7="bbb") са верни! Двете условия са несъвместими и за това просто трябва да ги отделим в отделни суми. Сумата на числата за които първата колонка е "aaa" и към нея да добавим сумите на числата с "bbb"! Разделяме формулата на две части и резултатите ги  сумираме. Разделяй и владей:) Сума на два независими един от друг  SumIFS-a! Просто е:))

=SUMIFS(B2:B7,A2:A7,"aaa") + SUMIFS(B2:B7,A2:A7,"bbb")

След като във всяка една от функциите условието е едно може да се използва и по-старата функция SUMIF която си я има и в по-старите версии на Excel, но позволява само едно условие!! (NB! Не забравяйте, е параметрите са разположени по друг начин спрямо SumIfS!!)

=SUMIF(A2:A7,"aaa",B2:B7)+SUMIF(A2:A7,"bbb",B2:B7)

Да отидем малко по-далече. Много често за заместител на SumIF и на SumIFS се препоръчва Sumproduct (разгледайте всички тагнати теми за SumProduct). Не съм му фен, но все пак да дам и решението с нейна помощ. По инерция може да се напише:

=SUMPRODUCT((A2:A7="aaa")*(A2:A7="bbb"),B2:B7)

Ако разровите темите със SumProduct ще видите, че умножаването на двете условия е тяхното свързване. Но да не ви мътя главите тогава пропуснах (умишлено) да ви кажа, че умножението означава И! В нашия случай {Истина,Лъжа,Лъжа,Истина,Лъжа,Истина} И {Лъжа, Истина, Лъже,Лъжа,Истина,Лъжа}  дава масив от лъжи.

Сега е моментът да кажа, че ако искаме да свържем няколко условия с ИЛИ знакът е +! В този случай дава правилен резултатен масив {Истина,Истина,Лъжа,Истина,Истина,Истина} който се умножава от Sumproduct със зоната за сумиране (не забравяйте, че Истина е 1 а лъжата е 0!) И дава верен резултат:)
=SUMPRODUCT((A2:A7="aaa")+(A2:A7="bbb"),B2:B7)

Поуката от темата е да внимавате с ИЛИ и И съюзите:):) Ако използвате Sumproduct запомнете, че свързването на условията с * означава И, а със знака + означава ИЛИ!
 


Успех