Мій Комп'ютер, №6, 04.02.2008

Тема взагалі-то визначилася наступним чином. Потрібно було зобразити Excel'ем ту задачу, яку я зазвичай вирішую АКСЕС. Було начальником запропоновано не ходити на горобців з гарматою і поважати ліцензійні проблеми.

Як зазвичай у мене влаштована аксесовская база? Грубо кажучи - Основна база і довідники, пов'язані з нею по ключу. В Основний у відповідних полях розташовані числа, а в довідниках - їх «розшифровка»: той же номер, що і в Основний, плюс в інших стовпцях купа додаткової інформації. Наприклад, машина з ключем 65 з довідника машин: в стовпці «назва» - «легкова», місто з ключем 54 з довідника населених пунктів: в стовпці «назва» - «Ясинувата». Якщо я роблю запит до такої бази і бажаю вивести повну інформацію по техніці, то потрібно або «об'єднувати» Основну і довідники (робити до них запит з використанням join, наприклад) - тоді і дані з Основною, і найменування з довідників будуть присутні відразу в запиті і можуть бути відображені в формі або звіті відразу ж, без додаткового праці. Або доведеться витягати значення цифр з довідників «на місці», після запиту, спрямованого тільки до Основною - наприклад, застосовувати в звіті, безпосередньо в полях, спеціальні функції типу DLookUp, тягнуть дані з довідників; в такому випадку в запиті даних з довідників немає, стоять лише цифри, відповідні зовнішнім ключам. Ось і почала я шукати в Excel звичний мені DLookUp.

У сабже, до слова, вчинила аналог Основний на одному аркуші, аналоги довідників - на інших аркушах. Щоб зробити звіт - тобто звести дані разом на якійсь окремій сторінці, прикрасивши все це справа шрифтами, підсумками та телефоном виконавця в кутку - і потрібен був вищезгаданий DLookUp.

Аналог знайшовся, його звали схоже - VlookUp (російськими буквами - ВВР). Сабж був всім хороший, крім одного - дозволяв накладати на поля довідника тільки одна умова. Мені ж потрібно було мінімум два, тому життя не склалося. Але раз вже зайшла мова, коротко вкажу синтаксис VLookUp'а.

VLookUp (что_іщем, где_іщем (совкупность осередків), в_каком_столбце (номер оного від початку інтервалу), іщем_точно_ілі_как (якщо точно -True, якщо не точно - False)

Неточний пошук, до речі, дуже неточний, шукає від абсолютної балди або вимагає попереднього сортування об'єктів, серед яких буде шукати. Що, як правило, неможливо або складно в реальних задачах. Функція досить шкідлива у вживанні, не слухається, погано поводиться. Намучилася я з нею! Образила вона мене ...

Тепер про юзаніі сабжа в програмі, а не у формулі.

Для початку - як пройти в відладчик? В Excel з 97 по 2003 - меню Сервіс, пункт Макрос, пункт Редактор Visual Basic. А ось в новому Excel 2007 - вкладка Вид, остання піктограма на стрічці Макроси. Клацаємо на ній і вибираємо пункт - знову ж Макроси. Якщо потрібно редагувати конкретний макрос - вибираємо кнопку Змінити і проходимо. Якщо у файлі вже є макроси і вони заблоковані, ці кнопки будуть недоступні .

Інформація про заблокованих макросах розташована під стрічкою .

Тиснемо на кнопку Параметри і приймаємо рішення: розблокувати (якщо макрос знайомий) або спробувати в інший раз і з іншим файлом .

Кнопка ж, просто запускає відладчик, відразу не знаходиться. Серед вкладок є така, називається Розробник, вона не показується за замовчуванням. Клацаємо на кнопці Мікрософт Office (верхня зліва, велика, красива), там внизу шукаємо кнопку Параметри Excel .

Далі вкладка Основна, ставимо птицю навпаки Показувати вкладку «Розробник» на стрічці .

Ось вона вам, будь ласка: потрібна вкладка, «меню», починається з кнопки, яка запускає відладчик - кнопка Visual Basic .

Тиснемо і користуємося!

Тепер повернемося до нашої функції. Спроба в синтаксисі просто замінити крапку з комою на кому завершується нічим: не знаходить таку функцію. Починаєш думати: чи не потрібно підключити якусь бібліотеку? Або що-небудь оновити? Навіть була божевільна думка: а чи не варто написати назву російськими буквами? Локалізація, знаєте ... Все виявилося простіше: функцію потрібно викликати не коротко, її ім'ям (perem12 = VLookup (...)), а із зазначенням родоводу - вона входить, разом з деякими іншими подібними функціями, в «сімейство» WorksheetFunction:

ActiveSheet.Range ( «G» & (i + 1)). Value = _

Application.WorksheetFunction.VLookup (nomer_telefon, _

ThisWorkbook.Worksheets ( «PeopleTel»). Range ( "A1: G60000»), 7, False)

Об'єкт пошуку поміщаємо в змінну - як вище зазначено (змінна описана без вказівки типу, по-нехлюйство - Dim nomer_telefon); або, наприклад, в клітинку, названу повністю - книга / сторінка книги / осередок з такими-то координатами (Workbooks (yearr_xls) .Worksheets (monthh_h) .Range ( «E» & (1 + i)). Value); або ж в клітинку, названу коротко, - активна сторінка (якщо ви її перед цим вказали правильно!) / осередок на ній (ActiveSheet.Range ( «E» & (1 + i)). Value).

«Як добути інформацію про цю функцію в хелпаре» - таке завдання виникає лише в Excel 97. Синтаксис сабжа не знайдеш днем ​​з вогнем - ні в хелпе для користувачів (який вискакує відразу), ні в полуанглійском хелпе для программеров. Решта версій (2000-2007) жваво видають інфу. Правда, іноді лізуть за нею в Інет. Тому не можу сказати точно, де кінчається Інет і починається нормальний хелп.

В Excel 97 потрібно шукати з англійської назвою функції. Про всяк випадок - цитата з хелпа, окремо лежить в Інеті (http://www.sql.ru/forum/actualthread.aspx?tid=378743).

Пошук програмним чином (якщо вже ми залізли в відладчик) можна здійснити і за допомогою функції, яку ми зазвичай використовуємо для пошуку руками, яка викликається з меню Правка, пункт Знайти (в Офісах до 2007 або з «меню» Головна, на стрічці праворуч піктограма знайти). Покажу шматок програми з пошуком:

'Оголошуємо змінні

Dim i As Integer

Dim firstAddress

Dim c As Range

'Оголошуємо активної потрібну сторінку книги

Workbooks (yearr_xls) .Worksheets (monthh_h) .Activate

'Приступаємо до роботи зі стовпцем цієї сторінки

With Workbooks (Dir_TextBox1_Value) .Worksheets (1) .Range ( «A: A»)

i = 1

'Починаємо шукати з параметрами:

'What (що шукаємо?) - «Контракт №»,

'After (після чого, з якого місця) - Cells (1, 1) (верхня ліва комірка),

'LookIn (у чому, серед чого шукати) - xlValues (в значеннях),

'LookAt (яким чином шукати) - xlPart (дивитися, чи не є шукане слово частиною осередку, з якою порівнюють - тобто мова йде про входження шуканого в клітинку як частини, а не про рівність осередки, з якою порівнюють, з текстом),

'SearchOrder (порядок пошуку, порядок обходу осередків) - xlByColumns (по стовпцях),

'SearchDirection (напрямок пошуку) - xlNext (до наступного елементу, вперед),

'MatchCase (чи враховувати регістр, великі чи маленькі букви) - False (нітрохи не враховувати).

Set c = .Find (What: = »Контракт №», After: = Cells (1, 1), LookIn: = xlValues, _

LookAt: = xlPart, SearchOrder: = xlByColumns, SearchDirection: = xlNext, _

MatchCase: = False)

'Якщо чогось знайшли, «з» ненульове

If Not c Is Nothing Then

'то дамо змінної значення того, що знайшли

firstAddress = c.Address

'і будемо в циклі з ним щось робити.

Do

......

'щось зробивши, збільшимо індекс - якщо потрібно

i = i + 1

'і будемо шукати далі

Set c = .FindNext (c)

'і будемо так ходити колами, поки не знайдемо всі (c Is Nothing) або НЕ почнемо отримувати повторювані значення (c.Address <> firstAddress).

Loop While Not c Is Nothing And c.Address <> firstAddress

End If 'If Not c Is Nothing Then

End With 'With Workbooks (Dir_TextBox1_Value) .Worksheets (1) .Range ( «A: A»)

Вкладені Find викликати не можна - вони один одного будуть збивати. Тобто знайти спочатку рядок, а потім вкладеним Find'ом знайти в ній осередок можна. Потрібно всередині Find'а шукати якимось іншим способом. Зберігати пошук, шукати інше і відновлювати пошук з зупиненого місця - не пробувала.

Проблема більш ніж одного умови досить часто піднімалася на sql.ru - досить задати в пошуку ВПР. Або ГПР (HLOOKUP) - це як ВВР, але транспонована; ВПР шукає «в сторону», ГПР шукає «вниз». Дивіться, наприклад, топіки http://www.sql.ru/forum/actualthread.aspx?tid=189992, http://www.sql.ru/forum/actualthread.aspx?tid=361209 (проблеми при пошуку / порівнянні текстів), http://www.sql.ru/forum/actualthread.aspx?tid=422263 (дуже сподобалося останні повідомлення топіка - про швидкість роботи формул при різних умовах), http://www.sql.ru/forum/ actualthread.aspx? tid = 376243 (призначена для користувача функція, яка вирішує проблему ВВР, але щоб з двома умовами), http://www.sql.ru/forum/actualthread.aspx?tid=515466 (народ досліджує швидкість роботи формул). На інші страшні формули і застосування названих функцій (ВВР та ін.) Можна подивитися тут: http://www.sql.ru/forum/actualthread.aspx?tid=426773.

Інший, більш зручний спосіб пошуку чогось (теж з однією умовою) полягає у використанні функції СУММЕСЛИ:

= СУММЕСЛИ (де шукаємо; що шукаємо; що підсумовуємо)

приклад:

= СУММЕСЛИ (Лист1! E $ 7: E $ 7884; Аркуш2! B7; Лист1! B $ 7: B $ 7884)

Шукаємо в стовпці Е то, що зазначено в осередку Аркуш2! B7 і, знайшовши, підсумовуємо підходяще (в тому ж рядку знаходиться) число, взяте з шпальти B. Причому порівняння шуканого і вмісту стовпця - дослівне, побуквенное: пробіл попереду істотно змінює ситуацію. Якщо ми шукаємо включення слова-зразка (або просто побоюємося пробілу ззаду / спереду) у словах, розташованих в розвідувати стовпці, краще додати зірок в зразок, з яким порівнюють: в осередку B7 пишемо не "Причеп», а «* Причеп *». А якщо написати «* Вантажне *», то тоді знайдеться і просто «Вантажна машина», і «Вантажний автолетучка» і навіть «Вантажний автоямобура».

Всі назви типів транспорту - справжні :).

Ще один спосіб полягає у використанні так званих формул масивів. Штука це чудова, але юзается дуже рідко, а даремно. Що воно таке - краще Майкрософта я не скажу: «Формула масиву може виконати кілька обчислень, а потім повернути одне значення або групу значень. Формула масиву обробляє кілька наборів значень, званих аргументами масиву. Кожен аргумент масиву повинен включати однакове число рядків і стовпців. Формула масиву створюється так само, як і інші формули, з тією різницею, що для введення такої формули використовуються клавіші Ctrl + Shift + Enter »(http://office.microsoft.com/ru-ru/excel/HP051983191049.aspx). Після натискання клавіш з'являються спеціальні фігурні дужки. Просто забивати фігурні дужки не потрібно і безглуздо, так не спрацює. Натискайте комбінацію клавіш.

Ідея приблизно така. Відомо, що логічне значення True еквівалентно одиниці, а False - нулю (зрозуміло, що не скрізь і не завжди, зрозуміло, що з застереженнями і т.п.). Тому, множачи через підрядник в якійсь формулі логічні вирази (дужки з умовами) один на одного, а потім на відповідні значення, ми повинні в підсумку (грубо кажучи, підсумувавши такі вирази «внизу», звичайної сумою) отримати потрібне значення, яке задовольняє заданим умовам. Звести цю порядкову перевірку і підсумовування в одну єдину формулу якраз і допомагають формули масиву.

Показую на прикладі. Ось, беремо таку симпатичну табличку підвищеної свідомості: перший стовпець - номера рядків; верхній рядок - букви стовпців.

Хочу порахувати, скільки грошей наговорили з телефону 30000001 при параметрі, що не перевищує 5. Чи отримаю результат за допомогою формули:

{= СУММ ((C22: C26 <5) * (B22: B26 = 30000001) * (D22: D26))}

Перша дужка перевіряє стовпець С на відповідність умові «той чи параметр?»; друга дужка дивиться в стовпці B, який номер телефону; третя «множить» на гроші з останнього стовпця D. Порівняння дат здійснюється як порівняння чисел, наприклад:

{= СУММ ((E22: E26> G21) * (B22: B26 = +30000001) * (D22: D26))}

Тут я порівнюю дату, що стоїть в осередку G21, з безліччю дат з шпальти E. Спроби вказати дату прямо у формулі - в лапках (як текст), в решеточкой (#) або без всяких витребеньок - незмінно приводили до помилки.

Глюки: можливо незнаходження через розбіжності форматів - десь текстовий, а десь числовий, десь різні текстові з точністю до наявності / відсутності прогалин. Якщо чого, перевіряйте. Але проблем з цифрою не виникало, були глюки тільки з текстовим форматом.

По-моєму, це найпростіший і доступний для огляду спосіб вирішення поставленого вгорі завдання.

А тепер покажу, як схоже завдання вирішити складно і заплутано, та ще й двома шляхами. Навіщо? Щоб показати можливості ще кількох маловідомих функцій - раптом в житті знадобиться. І ще - святкує: коли наберете формули в Excel, буде зрозуміліше - там все дужки розфарбовують в різні кольори, та ще й виділяють цими квітами області на аркуші. За дві формули, які будуть згадані нижче, виражається всенародний респект камрада з Sql.ru (http://www.sql.ru/forum/actualthread.aspx?tid=440965&pg=-1).

Будемо шукати не суму грошей, а прізвище людини, що задовольняє умовам - дата початку (пакета 🙂 менше певної дати, телефон строго певний (наприклад, 3000-0000). Для початку ось така штука:

{= ІНДЕКС (A22: A26; СУММ ((E22: E26 <$ G $ 22) * (B22: B26 =

»3000-0000») * СТРОКА (A22: A26)) - СТРОКА (A22); 1)}

Дужки означають формулу масиву. Усередині СУМ бачимо вже зустрічалися нам дужки з умовами (E22: E26 <$ G $ 22) і (B22: B26 = "3000-0000"). Якийсь з них було це слово, яке-то помилково, твір умов дасть одиницю в місці їх спільного виконання. Функція СТРОКА (A22: A26) - просто вказує рядок осередку в дужках. Тут в дужках бачимо діапазон, як і в умовах - значить, для кожного осередку діапазону функція СТРОКА буде вказувати її номер рядка. Тобто, розглядаючи скопом: виконані умови дадуть одиницю, функція СТРОКА - номер рядка. Отримаємо, що СУМ дорівнює номеру рядка, в якому виконуються умови. Отже, ми вже знаємо рядок, в якій знаходиться потрібний нам відповідь. Уффф ... Їдемо далі.

Функція ІНДЕКС просить як параметри, по-перше, якийсь діапазон, сукупність осередків - у нас це A22: A26 (там, де прізвища). По-друге, просить рядок і, бажано, стовпець - чи не абсолютні координати, а взяті всередині діапазону. Тобто, якщо діапазон буде починатися в осередку В100, а я захочу отримати значення осередки С102 за допомогою ІНДЕКС, то я підставлю НЕ 102, а 2. А коли буду вказувати стовпець, вкажу 1 (різниця між В і С - один стовпець).

А тепер починаємо збирати кубики разом - по частинах ми вже всі подивилися. Перший параметр - стовпець з прізвищем, другий параметр - рядок правильного (з виконаними умовами) людини. Так це ж наш СУМ! Але оскільки координата рядка відносна (щодо початку діапазону), віднімаємо номер рядка початку діапазону (СТРОКА (A22)) - тобто СУММ () - СТРОКА (). Третій параметр - стовпець, в якому розташовується прізвище, тобто просто 1. І відносна, і абсолютна координата дорівнює 1. Ще раз, в швидкому темпі - діапазон прізвищ; рядок, в якій виконана умова - мінус номер рядка початку діапазону, номер стовпця. Отже, ІНДЕКС (діапазон, СУМ-СТРОКА, 1). Наша формула, ура! Усе. Здолали.

Для розминки і закріплення - аналог нашої формули:

= ІНДЕКС ($ A $ 22: $ A $ 26; СУММПРОИЗВ (($ E $ 22: $ E $ 26 <$ G $ 22) * ($ B $ 22: $ B $ 26 = »3000-0000") * СТРОКА ($ A $ 22: $ A $ 26)) - СТРОКА ($ A $ 21))

Вже не формула масиву - зате замість витончених дужок стоїть функція СУММПРОИЗВ. По суті, виходить те ж саме, просто ми бачимо, що збір в купу «творів умов» відбувається різними методами - або за допомогою формули масиву, або за допомогою суми нулів і одиниць, виконаних і невиконаних умов. В результаті ми так чи інакше виходимо на номер правильної рядки - рядки, в якій виконуються умови. Але з формулою масиву - коротше і доступному для огляду, наочніше. А взагалі, вважайте як вам зручніше, хоч як-небудь та вийде.

Аналогічно, до речі, можна замінити

{= СУММ ((C22: C26 <5) * (B22: B26 = 30000001) * (D22: D26))}

на

= СУММПРОИЗВ ((C22: C26 <5) *

(B22: B26 = 30000001) * (D22: D26))

як радять на http://www.sql.ru/forum/actualthread.aspx?tid=189992#4137986.

Дозвольте дати на завершення посилання на хороший ресурс по формулам: http://msoffice.nm.ru/faq/formula.htm (три сторінки, посилання на першу). Ще два посилання пропонують на http://www.sql.ru/forum/actualthread.aspx?tid=397224 - це англомовні сторінки http://www.emailoffice.com/excel/arrays-bobumlas.html і http: // www.xldynamic.com/source/xld.SUMPRODUCT.html.

Наталя ЛИТВИНЕНКО