сряда, 22 декември 2010 г.

#37 Параметър Target

Както вече обещах ето ви малко детайли за параметъра Target. При събитията свързани с даден работен лист (промяна на данни, избор на клетки, натискане на десен бутон и т.н.) към подпрограмата се изпраща параметър Target който съдържа клетките/клетките които се променят, избират и т.н. Ето някой трикове за работа с този параметър.  N.B! Примерите в тази глава се въвеждат в частта за код на съответния лист (десен бутон върху името на листа и View Code).
1. Проверка за броя на клетките. Най-лесно е просто когато параметъра съдържа повече от една клетка да се излезе от програмата. За целта в началото на подпрограмата се поставя следния код : If target.count>1 then exit sub. Това върши работа в голяма част от случаите,  но когато искаме да работим с повече от една клетка трябва да се направи цикъл от вида:

Dim c As Range
 For Each c In Target
   Код за работа със променливата C
 Next 

Пример 1: Да се направи програма за оцветяване на променяните клетки в зелено и на изтритите клетки в жълто. За целта се програмира събитието за промяна на клетка.

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim c As Range
For Each c In Target 'За всяка клетка в зоната
 If IsEmpty(c.Value) Then 'Ако клетката е празна
   c.Interior.Color = RGB(255, 255, 0) 'жълт цвят = червено + зелено
 Else
   c.Interior.Color = RGB(0, 255, 0) ' Зелен
 End If
 Next
End Sub

2.Действия само върху избрани клетки. Много често се налага дадено действие да се извършва само ако става дума за определени клетки от работния лист. Има различни начини за проверка дали зоната target (или клетките от нея) са подходящи.
Вариант 1: Цвят на клетката. Това е много "бабешки" начин, но не изисква промяната на кода (освен ако не искаме друг цвят). Просто потребителя оцветява нужните клетки в даден цвят в работната книга и само за тези клетки даденото събитие се активира. За целта се използва конструкция:
if Target.Interior.Color = xxx then

end if

Пример 2: При натискане на десен бутон върху жълтите клетки (само върху тях!) в избраната клетка да се въвежда текущата дата. В случая се програмира събитието което настъпва при натискане на десен бутон на мишката:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Target.Count > 1 Then Exit Sub ' Изход ако са повече от една клетка
If Target.Interior.Color = RGB(255, 255, 0) Then ' Ако цветът на клетката е жълт
  Target.Value = Now() ' Записва датата
  Target.NumberFormat = "dd/mm/yyyy" 'Оформя се клетката като дата
  Cancel = True ' Излиза се от подпрограмата без да се показва стандартното меню за десен бутон
End If
End Sub

Вариант 2:  Трикът със цветовете винаги ми е вършил работа, но има и по "научен" начин чрез използване на функцията Intersect. Тази функция връща пресечната точка (сечението) на няколко зони. Форматът и е Intersect(зона1, зона2....). Ако няма нито една пресечна точка функцията връща Nothing. За нас е важно да НЕ е Nothing. Ето конструкцията която се използва.
If NOT (Intersect(target,range(Зона)) is Nothing) then

end if

В зона се посочва или адреси на конкретни клетки (например Range("а1:а99")) или име на зона (например Range("nomera"). Недостатък на метода е че трябва да се пипа кода (или името на зоната) за да се промени обхватът. Но тук предимството че потребителя не е обвързан с определен цвят.

Пример 3: При избор на клетка от зоната B1:B22 да се изведе предупреждение. За целта се програмира събитието за избор на клетки.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not (Intersect(Target, Range("B1:B22")) Is Nothing) Then
   MsgBox ("Данните са важни!")
End If








вторник, 14 декември 2010 г.

#36 Статична дата и/или час

Спокойно можех да кръстя темата "Как да не използваме Now()":)) Проблемът се крие в "умните" функции Now() и Today() които се самообновяват при преизчисляване (натискане на F9 или някой от другите комбинации за преизчисляване). Това е доста досадно и съм виждал много некадърно направени таблици в които датите (например дата на фактура) е с помощта на Today() или Now()!  И в резултат на което получавате дата която се мени постоянно:):) Когато искаме датата да не се променя най-бързия начин за въвеждане е с помощта на комбинациите Ctrl+; (точка и запетая) за въвеждане на текущата дата и Ctrl+Shift+; за въвеждане на текущия час. Така въведените стойности няма да се променят!!

По сложен е въпросът ако искаме датата (или часът) да се въвеждат автоматично при промяна или попълване на дадена клетка. 
Задача:
Да се направи таблица в която автоматично да се попълва дата и час при промяна на дадена клетка:
Автоматична дата

В посочения пример се иска когато потребителя въвежда данни в колонка А автоматично да се въведе текущата дата и час в колонка В и то без да се променя при всяко преизчисляване на листа!

Вариант едно: Явно варианта с формула в клетка В2 =IF(A2="","", now()) е примамлив но неприложим по простата причина, че при всяко преизчисляване ще се променя дата и часа:( !
Да   опитаме да се направим на умни. =IF(A2="","",IF(B2="",NOW(),B2)). Тази формула има лееек дефект:):) Проблемът е, че самата тя зависи от себе си (казано на кратко получава се зацикляне (circular reference!) на формулата. Колкото и да се мъчим (например с помощта на други клетки) винаги формулата се самозацикля:(  Но от това също има изход. Малко груб, но работи. Excel позволява самозациклянето на формули в определени ситуации. Това са така наречени итерационни изчисления. Нашия случай не е точно такъв, но ще "излъжем" Excel че искаме той да не обръща внимание на самозациклените формули. Настройките се извършват от Excel/Options.

Включване на итерациите
Това е. Но по принцип е трик от типа "не правете така в къщи":) Хубавото му е, че няма нужда от макроси и свързани с това промени в сигурността и т.н.

Вариант две: С помощта на макрос. Натискане десен бутон на името на листа и показвате редактора на код чрез View Code. В прозорецът вкарвате следния код (Copy/Paste). В кодът съм сложил доста коментари, но обещавам следваща тема да бъде за параметъра Target и как се използва той.

'Събитие при промяна на клетки
Private Sub Worksheet_Change(ByVal Target As Range)

' Ако се прави групова промяна на клетки се излиза от подпрограмата
' Функцията не работи с промяна/изтриване на повече от една клетка!

If Target.Count > 1 Then Exit Sub

' Ако променяната клетка е в зоната A2:A999

If Not (Intersect(Target.Cells, Range("A2:A999")) Is Nothing) Then
   'Ако НЕ изтриваме клетката
   If Not IsEmpty(Target.Value) Then
   ' Ако съседната клетка все още е празна
   If Target.Offset(0, 1) = "" Then
      ' В съседната и клетка записваме текущата дата
      Target.Offset(0, 1) = Now()
      ' Оформяме съседната клетка като дата и час
      Target.Offset(0, 1).NumberFormat = "dd.mm.yyyy HH:MM"
    End If
    Else ' Ако изтриваме клетката
    ' Премахваме съдържанието и на съседната клетка
      Target.Offset(0, 1) = ""
    End If
End If
End Sub

Както казах втория вариант има недостатък, че трябва да е книга от тип XLSM , но има по-големи възможности и не се пипат настройките за калкулиране. Както споменах, ще направя няколко теми да разясня нещата в този код в детайли. Успех и внимателно със датите:)

вторник, 2 ноември 2010 г.

#035 Заключване на клетки

Беше ми зададена следната задача. В таблица след като се пипне някоя клетка да се заключи. Т.е. нещо като правилото в шаха "пипнато-местено":) Разбира се при необходимост да може да се сваля защитата, но за обикновения потребител попълващ таблицата да не е възможно да се връща след като въвел нещо. Ето как се реализира това:

1. Избират се клетките в които по начало е разрешено въвеждането и се "отключват". С командата Format (Лента Home)/Lock Cell (да се махне "катинарчето"). Или "класическия начин" чрез Format Cells (Ctrl+1)/ Protection и махане на Lock отметката.

"Отключване" на клетките за въвеждане
2. Активиране на защитата. Използва се командата Format/Protect Sheet. NB! Добре е да се разреши само избора на разрешените за въвеждане клетки. Не поставяйте парола! (Това ще коментираме малко по-късно!)

Включване на защитата на листа
3. Поставяне на макроса.
3.1. Активизиране на кода на листа. Заставате долу на името на листа/десен бутон/View Code.
Активиране на редактора

3.2 Присвояване на код на събитието на работния лист "Change". За целта в левия списък над редактора се избира "Worksheet" а в десния "Change".

Създаване на подпрограма за обработка на събитие
3.3 Поставяне на кода в създаденото събитието. В отворената процедура напишете следния код:

If Me.ProtectContents Then
  Me.Unprotect
  Target.Locked = True
  Me.Protect
End If

Код към събитието
Готово:)
Таблицата трябва да я запишете като XLSM или като стария съвместим със старите версии XLS формат. Припомням, че XLSX формата не поддържа макроси!

Няколко думи за кода. Събитието Change настъпва когато променяте съдържането на клетка. Кодът проверява дали е активираната защитата на листа. Това позволява при деактивирана защита да не се извършва заключване при промяна! Изключва защитата, защитава променената клетка (параметър Target се предава на събитието и съдържа адресът на променяната клетка) и отново активира защитата. Просто:)
Както беше споменато по начало ние не сложихме парола при активиране на защитата. Ако искаме да има парола кодът трябва да изглежда по следния начин:
If Me.ProtectContents Then
   Me.Unprotect ("123456")
   Target.Locked = True
   Me.Protect ("123456")
End If
Където 123456 е използваната парола! (NB! не разчитайте много на тази парола:) Има прекалено много инструменти за нейното премахване). При промяна на паролата се налага и промяна на кода!

сряда, 27 октомври 2010 г.

#034 Таблици за търсене

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


до 80%
2
от 81 до 99%
4
от 100 % до 119 %
6
120% и повече
8

Вариант 1: Първо ще реализираме цялата таблица с помощта само на IF. =IF(A1<=80%,2,IF(A1<100%,4,IF(A1<120%,6,8)))    * ако данните са в А1
 Този вариант е добър в случаите когато числата не се променят често, но при промяна потребителя трябва да се занимава с редактиране на формули и т.н.

Вариант 2: Таблица за търсене

Таблица за търсене
Въвеждаме данните в произволни клетки (може и на друг лист). Избираме клетките и им задаваме име (в случая  TablicaP).  NB! Не забравяйте да натискате Enter след като именувате областта!.  Обърнете внимание как е изградена таблицата спрямо основните данни! Така изградена таблицата позволява търсенето на всякакви стойности по-големи или равни на нула. 

Формулата която търси в тази таблица е :  =VLOOKUP(A1,tablicaP,2,TRUE) . Приблизително търсене чрез VLookup връщайки стойността от втора колонка (за повече  вижте помощната информация в Excel). Както казах тази формула ще работи само с неотрицателни числа! При отрицателни стойности или текст ще се издъни. Най лесно е да се "пакетира" в IFError (виж темата за тази функция). =IFERROR(VLOOKUP(A1,tablicaP,2,TRUE),0)

Вариант 3: Понеже имах цяла тема как да не се използва Vlookup ето и варианта без нея. Но в случая става доста оплетено и за предпочитане е Vlookup. Все пак ето формулата и нейната "дисекция":
=IFERROR(INDEX(tablicaP,MATCH(A1,INDEX(tablicaP,0,1),1),2),0)

INDEX(tablicaP,0,1) - Това връща цялата първа колонка от таблицата за търсене
MATCH(A1,INDEX(tablicaP,0,1),1) - Приблизително търсене на стойността от А1 в първата колонка на таблицата за търсене. Резултатът представлява номерът на намерения ред. (NB! Единицата означава приблизително търсене в подредени във възходящ ред стойности!)
INDEX(tablicaP,MATCH(A1,INDEX(tablicaP,0,1),1),2) - Връща от таблицата за търсене стойностна от намерения ред и втората колонка.

Ако сте разбрали и трите варианта нямате проблеми с търсенето (става дума за търсене в Excel а не търсене на сериозно гадже):):)



четвъртък, 16 септември 2010 г.

#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)...


петък, 3 септември 2010 г.

#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 (най в дясно) за лесно оформяне на таблицата.

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

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

четвъртък, 2 септември 2010 г.

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

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

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

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

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

сряда, 1 септември 2010 г.

#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 запомнете, че свързването на условията с * означава И, а със знака + означава ИЛИ!
 


Успех

вторник, 31 август 2010 г.

# 027 Лента Developer

Това е една важна мини тема:) За обединяване на всички функции за програмиране в Еxcel 2007 и 2010 е създадена специална лента наречена Developer. Явно подценявайки своите потребители тази лента по подразбиране не е активна.

Лента Developer

Ето как се активира ...

... в Excel 2010

  1. Активира се лентата  File
  2. Команда Options
  3. Customize Ribbon
  4. Активизира се Developer.
  5. OK


    Excel 2010

... в  Excel 2007

  1. Натиска  се Microsoft Office бутона (кръглия бутон горе в ляво)
  2. Бутон Excel Options
  3. Избира се групата Popular
  4. Активизира се  Show Developer tab in the Ribbon.
  5. OK

#026 Макроси и функции (основни понятия)

Това е първата тема която се явява преход към макросите и функциите. Ще има няколко такива. От гледна точка на потребителите се разграничават две основни понятия:
1. Макрос. Това е пакет от действия който трябва да се активизира по някакъв начин (ще разгледаме начините за активизиране в отделна тема). Макросът позволява да се извършват действия за промяна съдържанието и оформянето на група клетки. Може да се създават и  изтриват редове, колони и работни листи и т.н. Т.е. може да се прави всичко с компонентите на една работна книга!
2. Потребителска функция. Това е функция която връща резултат в текущата клетка. НЕ МОЖЕ ДА ПРОМЕНЯ СТОЙНОСТТА И ОФОРМЛЕНИЕТО НА ДРУГА КЛЕТКА! Това е много важно да се запомни. Много хора не правят разлика между макрос и потребителска функция.

Проблемът на макросите е че някой/нещо трябва да ги активира, а проблемът при функциите е че връщат стойност само в текущата клетка и не могат да правят нищо друго!

От програмистка гледна точка нещата са леко по-сложни:) Първо за да е кашата пълна в ранните версии на Excel съществуваше специален език за писане на макроси. Това са така наречените XLM (да не се бърка с XML!) макроси. Остатъци от тях може да намерите още. (например ако от контекстното меню което се появява върху името на лист изпълните Insert ще видите три вида работни листи които са свързани с този анахронизъм).
Стария вид макроси

В следващите версии Microsft вградиха в продуктите си езика Visual Basic For Application (ще използвам популярното съкращение VBA) който е много по-мощен и стандартен. Няма да се учудя обаче, ако лека полека вградят .Net езиците (сега има възможност за програмиране, но не е вградено в самия офис пакет). Така че в момента като препоръчителна среда за програмиране е VBA.

Във  VBA понятието Макрос не съществува! Може да обособим четири важни понятия върху които ще се концентрирам в различни теми:

1. Процедура (Sub). Това е аналог на Макроса. В крайна сметка всяка процедура се появява в списъкът с макроси. За това аз ще говоря за процедури в следващите теми и ще забравя понятието макрос! 
2. Процедура-Събитие (Sub). Това е особена процедура, която се активира при настъпване на дадено събитие. Например промяна съдържанието на клетка, натискане на десен бутон на мишката и т.н. При тези процедури в зависимост от събитието се подават различни параметри (зоната която е избрана, клетката която е променена и т.н.).
3. Функция (Function). Функциите за разлика от процедурите освен извършваните от тях действия връщат резултат (стойност) в мястото на тяхното извикване.
4. Функции за работа в работни листи (Function). Това са функции които могат да се използват като останалите функции в работните листи. За да може една функция да стане такава тя трябва да отговаря на определени условия. Най-важното от което е в тялото си да не променя обекти на Еxcel!

Два вида процедури и два вида функции.Това е по-добро разграничаване от понятията Макрос и Потребителска функция в началото!


Начин на съхранение. Във версиите на Excel преди 2007 XLS файловете позволяват съхранение в себе си на процедури и функции. При новите формати за по-лесен контрол на съдържанието се дефинират два основни формата XLSX който НЕ МОЖЕ да съдържа процедури и функции и XLSM който МОЖЕ да съдържа процедури и функции. Това означава, че когато правите примери с програмен код ще се наложи да записвате в XLSM или в стария XLS формат (който не поддържа всички визуални възможности на новите версии на Excel)!

Сигурност. Office 2007 и 2010 имат сложна система за предпазване на потребителите и няма да ни стигне цяла тема. Ако не сте пипали настройките по подразбиране те блокират кода във вашата работна книга, но ви позволяват да го активирате. Просто следете информационното съобщение. Понякога е в отделен прозорец понякога е като лента!

Отделен прозорец за разрешаване на код
Информационна лента
Както обърнах внимание това са настройките по подразбиране за 2007 и 2010, но при Excel 2003 настройката по подразбиране е да бъдат блокирани всички макроси без предупреждение и възможност за активиране! За целта се налага да изпълните командата Tools/Macro/Security и да активирате предупреждаващото съобщение.
Настройки на Excel 2003



понеделник, 30 август 2010 г.

#025 Уникални стойности

Зададоха ми задачата как да се извлекат уникалните стойности от зона. Създаване на индекс.

Примерни данни
Първи вариант
Първото което ми мина през главата е да показвам мускули:) Т.е. да използвам формули. Ето този вариант.
Стъпка 1. Създаваме помощна колонка D.  В клетка D2 въвеждам формулата:
=IF(COUNTIF($B$2:B2,B2)=1,ROW(B2),"") . Тази формулка "маркира" тези редове в които за първи път се среща дадената стойност. Тук магията е в зоната посочена в CountIF.
$B$2:B2. Това е  полуабсолютна зона. (Може да направя отделна тема за зоните). Хитростта е че тази зона ще се разширява при влачене на формулата надолу. Т.е. във втората клетка ще стане $B$2:B3 и т.н. Този трик е доста популярен. "Маркировката" се изразява в посочването номера на реда където за първи път се среща дадената стойност (Countif=1).
Помощна колонка
Стъпка 2. Формула за извличане. Формулата в клетка C2 e :
=IFERROR(INDEX(B:B,SMALL(D:D,ROW(A1))),"")
Тук се използва трикът с променящ се при размножение брояч вътре във формулата. Row(A1) ще осигури брояч със стойност 1,2,3 и т.н. (NB. Внимавайте ако вмъквате редове или местите формулата, защото А1 ще стане например A10 и броячът ще започне от 10!). Този брояч осигурява извличането на първата по големина стойност от колонка D (използва се Small), после втората и т.н. (В нашия случай 2,5,6....). Остава да превърнем тези числа (редове от таблицата) в конкретни стойности. За целта използваме Index (има отделна тема)!
"Пакетираме" в IFerror за да избегнем отговорите при липса на стойност (ние не знаем колко са уникалните стойности затова "дърпаме" формулата достатъчно надолу.

Както казах това е "мускулестия вариант":)

Ето ви по-простичко решение)

Втори вариант
Изпълняваме командата Data/Advanced Filter:
Data/Advanced filter
Правят се следните настройки в диалоговия прозорец

Използва се възможността за копиране само на уникалните стойности! (За повече информация книжките за начинаещи):):) И готово:) Няма мускули няма трикове:) 

Всеки само си избира кой вариант да избере:)




вторник, 24 август 2010 г.

#024 SumProduct или дисекция на трикови формули

Вчера пак обяснявах в един форум за SumProduct трик, който не беше разбран и реших да се опитам (дано е успешно) да разбуля тайната на SumProduct и да обърна внимание на подводни камъни при използването и. Тази тема може да се каже е противоположна на #005, защото ще видите, че много често ще използвам формули за масиви. Припомням, че това са формули които се въвеждат с Ctrl+Shift+Enter (за това и ще ги означавам с CSE) и след тяхното въвеждане имат {} около тях. За повече прочетете първата препоръчана връзка в #005!

Примерни данни
0. Начин на действие. =SUMPRODUCT(B1:B6,C1:C6). Това е "класическото" приложение на SumProduct. Служи за умножение на две зони и намиране на сумата на произведенията. Т.е. ще се изчисли B1*C1+B2*C2+...+B6*C6. Спокойно може да напишете формулата като =SUMPRODUCT(B1:B6*C1:C6). Така е по-ясна. Всъщност този начин на записване ви поставя въпросът не може ли да използваме SUM?? Отговорът е ДА! Но като CSE формула!
{=SUM(B1:B6*C1:C6)} . Всъщност Sum има дефекта да не може да обработва масиви в нормален режим.

1. Да се намери сумата на числата за колона C за който са изпълнени две условия:
Съдържанието на колона A да е ABCD И колонка B да е по-голяма от 7.

След малко ровене в Google може да достигнете до :
=SUMPRODUCT(--(A1:A6="ABCD"),--(B1:B6>7),C1:C6)

Да видим как работи тази "магическа" формула. Тук се използва трикът с двата минуса за преобразуване на логически стойности в единици и нули. Т.е. първият масив ще съдържа {1,0,1,0,1,0}, втория {1,1,1,1,0,1} в резултат на тяхното умножение ще се получи {1,0,1,0,0,0}. И резултатния масив се умножава по стойностите на колонка C (тук са числа). 1х100+1х90=190.

Нищо сложно. Тази формула може да се запише и като
=SUMPRODUCT((A1:A6="ABCD")*(B1:B6>7)*C1:C6). Тук при самото умножени се извършва преобразуването и няма нужда от минусите! Ако сте разбрали предходната точка вече сте наясно, че може да се използва и CSE формулата {=SUM((A1:A6="ABCD")*(B1:B6>7)*C1:C6)} !

И двете решения обаче изглеждат доста странно дори и да са ви станали ясни. В Excel 2007 са се погрижили за по-лесното сумиране по няколко критерия. За целта си има функция SumIfS !
=SUMIFS(C1:C6,A1:A6,"ABCD",B1:B6,">7") 
Единствен недостатък е, че става за Excel 2007 и следващи. Но няма нищо "триково" в нея:)

2. Да се намери БРОЯТ на елементите за който са изпълнени две условия:
Съдържанието на колона A да е ABCD И колонка B да е по-голяма от 7.
=SUMPRODUCT(--(A1:A6="ABCD"),--(B1:B6>7)) . Отново SUMPRODUCT но този път за броене!?! Ако сравните с първата формула от предходния пример, ще видите че липсва само частта с колонка C (реалната стойност). Всъщност тук намираме сумата на нулите и единиците от резултатния масив (маркиран в зелено в горния пример). Тъй като в резултат на умножението се получава единица там където е вярно нашата цел е да съберем тези единици. Което е БРОЯ.  (NB! ако елементите са единици тяхната сума е равна на техния брои. Което се знае от всеки първокласник:):)
Ето още два варианта на същата формула: =SUMPRODUCT((A1:A6="ABCD")*(B1:B6>7)) и CSE {=SUM((A1:A6="ABCD")*(B1:B6>7))} . Както се вижда от този пример SumProduct може да се използва както за сумиране така и за броене!

За Excel 2007 и следващи: =COUNTIFS(A1:A6,"ABCD",B1:B6,">7")  , което си е по-класическо.

3. Да се намери най-голямата дата от колонка D за стойност на колонка A "АBCD". 

С помощта на Google стигаме до следната формула която дава верен резултат (не забравяйте да оформяте резултатната клетка като дата!):
=SUMPRODUCT(MAX((A1:A6="ABCD")*(D1:D6)))

не случайно съм оцветил в червено това решение. Но да видим защо работи първо:) 
Умножение на масиви: (A1:A6="ABCD")*(D1:D6) . Вече сте се досетили, че първия масив ще съдържа нули и единици в зависимост от условието и след това като се умножи по датите (които са числа) ще останат датите или НУЛИ в зависимост от стойността на условието!
С MAX извличаме най-голямата дата. За какво ни е SumProduct?! За нищо:) Т.е. за да накара функцията  Max да започне да работи с масиви. в нормален режим Т.е. спокойно може да си заменим функцията с CSE варианта {=MAX((A1:A6="ABCD")*(D1:D6))}

Дали ще ползвате единия вариант или другия си е ваше решение. Но този вариант не е универсален!! За това не му се доверявайте винаги. За това ще видим в следващия пример.

4. Да се намери най-голямото число от колонка E за стойност на колонка A "АBCD". 

Тук по инерция може да напишем
=SUMPRODUCT(MAX((A1:A6="ABCD")*(E1:E6))) и..... ще получим НУЛА:) Сега е моментът да погледнете в предходната точка и да осъзнаете защо съм оцветил в червено нулата:) Там където условието е грешно в резултатния масив има нула. Която е МАКСИМАЛНАТА стойност в нашия случай (за да е гадно умишлено сложих всички стойности да са отрицателни):))
Така че "триковия" пример не работи в случай на отрицателни стойности!!!  Нямаше да работи с Мin за дати, защото щеше а има нула което е по-малко за най-малката дата! Мисля че е ясно,че и CSE формулата от предния пример няма да работи защото тя отново използва умножение с масиви. Т.е. ТРИКЪТ не е толкoва трик след като не може да се приложи във всички ситуации и не може лесно да бъде заменена функцията Max с Min!

 Ето правилния вариант (CSE): {=MAX(IF(A1:A6="ABCD",E1:E6))} За съжаление на някой любители на Sumproduct няма да може да се накара IF да проработи в нормален режим. Тук няма умножение на масиви. Просто се вземат само стойностите за който е вярно условието. Запомнете този вариант и въпреки, че решенията в точка 3 работят е по-добре да използвате:
{=MAX(IF(A1:A6="ABCD",D1:D6))}!

Ето два бързи примера:)

5. Да се намери най-малкото число от колонка E за стойност на колонка A "АBCD". 
{=MIN(IF(A1:A6="ABCD",E1:E6))}

6. Да се намери най-малкото число от колонка Е за  за стойност на колонка A "АBCD" И стойност в B>7.
=MIN(IF(((A1:A6="ABCD")*(B1:B6>7)),E1:E6))

Mоже да се каже че в тази тема ви показах как да си направите функции MinIf и MaxIf:)

неделя, 22 август 2010 г.

#023 Търсене (Първа част?)

Тази тема няма за цел в детайли да ви запознае с функциите за търсене. Искам да обърна внимание на разликите (и приликите) между функциите и да обърнете внимание на техните особености. Няма да се спирам в детайли на техните параметри. За целта си има клавиш F1:)

1. Функциите Find и Search служат за търсене вътре в текст. Т.е. може да ги използвате за претърсване вътрешността на клетки. Разликата между двете функции е, че Find прави разлика между малки и главни букви когато търси! Двете функции връщат позицията на намерения текст. Т.е. число! За съжаление вместо нула ако няма успешен резултат от търсенето се връща грешка (#Value) което си е досадно (трябва да се обработи или с ISError или IFError).

2. Функцията Match служи да търси в област от клетки или масив. В този случай разглежда цялата клетка като едно цяло! Връща число показващо номера на първия елемент отговарящ  на даденото условие. Връща ПОЗИЦИЯ (брои от едно)!! (NB! Функцията може да търси и "приблизително" но за целта елементите трябва да подредени. За повече вижте помощната информация.) . Бих ви заострил внимание на подразбиращата се стойност на третия параметър (тип на търсенето)! Тя  НЕ Е 0(!!) (точно търсене). Ако искате да търсите точно не прескачайте този параметър!

3. Функците Vlookup, Hlookpup, Lookup отново служат за търсене в област от клетки или масив. За разлика от Match те връщат СТОЙНОСТ. Стойността се извлича от областта за търсене или от друга област. Всъщност тези функции са "глезотии" за улеснение на потребителите.:) Могат да бъдат създадени с помощта на други функции. Ще дам примери по-късно.

Трябва да се отбележи един недостатък на Match, Vlookup и другите функции от тази група, че не правят разлика между малки главни букви. Т.е. ако ви трябва наистина точно съвпадение ще се наложи да прибягвате към различни трикове.

Ето примерните данни:
Примерни данни
Пример 1. Да се намери стойността на колонка "C" за 1.10.2010 в колонка "A".

Тук се решава с "простия" Vlookup.
=VLOOKUP(DATEVALUE("1.10.2010"),A1:C6,3,FALSE) 

Функцията връща стойността на ТРЕТАТА колонка от блока търсейки в първата колона. Да не е прекалено скучно с тази формулка ви напомням, че датите и часовете са ЧИСЛА и ако търсите определена стойност трябва да я преобразувате в дата/час! В случая се налага да преобразуваме текстовата стойност в дата чрез DateValue! Ако датата беше записана като дата в дадена клетка (например e1) формулата щеше да изглежда:
=VLOOKUP(E1,A1:C6,3,FALSE)  без преобразуване. 

Но да се върнем към нашата задача. Функцията Vlookup често се явява нещо като Еверест и когато някой HR чуе че кандидата я знае направо подскача от радост. За мен това е излишна радост:) Vlookup си е много много базова функция:)

Ще дава още един вариант на решение на тази задача:

=INDEX(A1:C6,MATCH(DATEVALUE("1.10.2010"),A1:A6,0),3)

Хммм... Сега ще ме обвините, че е по-сложно! Да но е по-универсално! За това се опитайте да разберете как действа този пример и сте има няма няколко педи (не повече) над "Еверест-а " Vlookup:) В #010 разгледахме Index. Казах тогава че по-подходяща за показване на единична стойност. При нея се задава блок и се посочват координатите (ПОЗИЦИИТЕ!) на реда и на колоната. Колонката както при Vlookup е третата колона. "Далаверата" е, че контролираме по-добре и частта с реда. В случая използваме Match (зеления сегмент). Не забравяйте да укажете третия параметър (НУЛА за точно търсене)! Обърнете внимание че ние задаваме зоната за търсене в Match (A1:A6) и може да няма нищо общо със зоната за извличане в Index!

Пример 2 Да се намери датата която да отговаря числото 50 в третата колонка.
"Майсторите" на Vlookup-a загинаха:) Vlookup има "дефектът" ВИНАГИ да търси в първата колонка на блока. Този дефект е незабележим в голяма част от ситуациите, защото първата колонка най-често е колонката за търсене. Но не винаги :(
Ето решението (ако вече не сте се справили):
=INDEX(A1:C6,MATCH(50,C1:C6,0),1)
Просто е:) Търсим в третата колонка (C1:C6) и връщаме стойност от първата. Елементарно. Просто го запомнете;)

Пример 3 Да се намери стойността на трети ред за колонката съдържаща числото 20 в първия си ред. Тук се извършва хоризонтално търсене.
Този пример не е много подходящ за данните, но искам да ви покажа как да "ритнем" и Hlookup от нашите примери:) Ето "класическо" търсене в първия ред:

=HLOOKUP(20,A1:C6,3,FALSE)
Ето по-гъвкавия вариант:
=INDEX(A1:C6,3,MATCH(20,A1:C1,0)
Тук редът от който извличаме е константа, а Match определя колонката!  Нищо сложно:)  Запомнете втория вариант и забравете HLookup:)

Пример 4 Да намерим позицията първата клетка в колонка B която съдържа "А".

Първи опит: =MATCH("A",B1:B6,0)
Независимо, че сме сложи "A" и 0 (точно) търсене. За данните които съм дал функцията връща 2 (!), което е позицията на малкото "a". Както ви обърнах внимание Match (и братовчедите) не разбират от малки и главни букви. За целта ще се наложи малко "гимнастика":)

Втори опит (отговор): {=MATCH(TRUE,EXACT("A",B1:B6),0)} 
Тази формула я въвеждате без {}, но със Shift+Ctrl+Enter! Това е функция за работа с масиви.
Тук използваме функцията EXACT  която връща истина или лъжа ако имаме ПЪЛНО съвпадение между два текста. В случая резултатът от тази функция ще бъде {FALSE, FALSE,TRUE,FALSE,TRUE,FALSE}. 
Остава да намерим първия True с помощта на Match:) Елементарно:) За да проработи не забравяйте Ctrl+Shift+Enter при въвеждането. 

Пример 5. Да намерим позицията на първата клетка която вътре в себе си съдържа "А".
За разлика от предходния пример тук търсим дали няма някъде в текстът буквата "А"
Отговор: =MATCH(TRUE,NOT(ISERROR(FIND("A",B1:B6))),0)


Пример 6: Да се намери най-голямата дата за която стойността в колонка C е по-голяма от 20.
Отговор 1{=MAX((C1:C6>20)*(A1:A6))}
Въведете с Ctrl+Shift+Enetr! Форматирайте резултатната  клетка като дата!
Тук нещата са леко екзотични:) Извършва се умножение на два масива. Първия е с нули и единици в зависимост от резултата на условието. Втория са датите.
{0,1,1,0,1,1}*{1.1.2010,1.2.2010, 1.10.2010, 1.9.2010, 1.8.2010, 1.7.2010} в резултат се получава 
{0,1.2.2010, 1.10.2010, 0, 1.8.2010, 1.7.2010}. Чрез функцията MAX извличаме най-голямата дата.
Отговор 2: =SUMPRODUCT(MAX((C1:C6>20)*(A1:A6)))
Тук използваме една възможност за "пакетиране" на Max с цел избягване на функцията за работа с масиви. Използваме Sumproduct.Тази функция е "нормална" и няма нужда от въвеждането и с Ctrl+Shift+Eneter!
Hint! Ако искате направо да се форматира като дата може да използвате

=TEXT(SUMPRODUCT(MAX((C1:C6>20)*(A1:A6))),"dd.mm.yyyy")


Пример 7: Да се намери най-малката дата за която стойността в колонка C е по-голяма от 20.

Може по инерция да решите, че всичко ви е ясно и да си помислите, че заменяйки MAX с MIN си решавате проблемът. Да ама не:) Ако обърнете внимание на резултатния масив от по-горния пример ще видите, че най-малката стойност е нула:( което автоматично ви реже възможността да използвате Min върху произведението на двата масива. За целта се налага леко да видоизменим по-горната формула:
Отговор: {=MIN(IF(C1:C6>20,A1:A6))}
Формулата е за работа с масиви (Ctrl+Shift+Enter!)  и няма как да бъде "пакетирана" и "нормализирана! В случая чрез IF-а се създава масив в който има само датите които ни интересуват. Без нулеви стойности!

NB! Много се дискутира до колко формулите за масиви са по-бавни от нормалните. Из доста от формули се водят доста оживени дискусии. Честно казано единствената причина да използвам SumProduct и други трикове за нормализация е, че формулите за работа с масиви се редактират по-трудно (има по-стара тема по въпросът). Не съм установил чак толкова огромна разлика във времената.

Толкова по темата за търсене за сега:) Ако се сетя още нещо или имате някакъв конкретен практически пример и въпроси може да има втора серия:)