Excel: как найти и просуммировать данные, перемешанные с другими?

Мой Компьютер, №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.

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

Robo User
Web-droid редактор

     

Читайте також