ВЕСТНИК
Хабаровской государственной академии экономики и права

Иван Арисов,

Конэкагропромбанк

ИСПОЛЬЗОВАНИЕ ЭЛЕКТРОННОЙ ТАБЛИЦЫ EXCEL ДЛЯ АВТОМАТИЗАЦИИ ВЫЧИСЛЕНИЙ

Часть 4

На практике, при использовании любых способов “вытягивания” данных, могут возникать сложности. Например, в описанном примере, расстояние между строкой с датами и строкой с суммами погашаемых кредитов, может меняться (разное количество заёмщиков и т.д.). Из-за этого, суммы кредитов не всегда будут располагаться на 25-й строке, и наши функции будут работать не корректно.

Из подобных ситуаций можно найти выход. Например, можно договориться с кредитным отделом о том, что в их таблице непосредственно над строкой с суммами погашаемых кредитов будет находиться строка, дублирующая строку дат.

Таким образом, мы стабилизируем расположение дат и сумм относительно друг друга. Дополнительную строку дат можно скрыть и она не будет “мозолить” глаза хозяевам таблицы. Далее необходимо дать имя области из этих двух строк, и ссылаться не на координаты, а на это имя. Этим мы предусмотрим возможность перемещения интересующих нас данных в таблице как по вертикали, так и по горизонтали.

Наша функция, в этом случае, будет иметь следующий вид:

“=ГПР(A1;Имя_файла.xls!Имя_области;2;ЛОЖЬ)”.

Из примера видно, что всегда можно найти некоторый компромисс между “хозяевами” информации и её получателями. Однако, с точки зрения профессиональной этики, конечно, необходимо стараться вносить как можно меньше изменений в исходные файлы. Ведь тот факт, что мы желаем использовать в своей работе достижения науки и техники, не должен усложнять жизнь разработчикам исходных таблиц. Так каждое подобное наше нововведение накладывает хоть и небольшое, но ограничение на свободу изменения структуры файла с исходными данными. Поэтому, необходимо стараться так организовывать процесс считывание данных, чтобы он был незаметен и не накладывал никаких дополнительных условий на разработчиков исходных таблиц.

Разберём несколько конкретных примеров. Допустим, в том же банке, но уже в отделе депозитов юридических лиц, ведётся аналогичная по смыслу таблица погашения депозитов. Обстоятельства складываются таким образом, что депозиты банк гасит не каждый день. Дни, в которые не планируется возвращать владельцам складов их денежные средства в таблице депозитного отдела, просто не указываются. В общих чертах, эта таблица имеет вид:

Обратите внимание на то, что данные за 04.08.00, 06.08.00 - 08.08.00, и, возможно, за ряд других чисел месяца отсутствуют. Использованная нами в предыдущем примере функция в этой ситуации нормально работать не будет. Все дело в том, что не найдя при сравнении ячеек из заданной области ни одной идентичной эталону, функции “ГПР()” и “ВПР()”, могут вернут нам либо выражение “#Н/Д” (нет данных), либо значение из соседней ячейки. Второй вариант в данном примере вообще абсурден, поэтому, нужно использовать первый. Для его реализации и необходимо последним параметром при записи этих функций использовать логическое выражение “ЛОЖЬ”.

Логично предположить, что если в какой-то из дней депозиты не погашаются, то сумма гашения депозитов в этот день равна 0. Однако, не так всё просто – выражение “#Н/Д” вообще числом не является. Раз так, значит, с ним невозможны математические операции.

Если в нашей таблице вычисляются, например, общая сумма иммобилизации в месяц, или среднее значение выплат, то сейчас в соответствующих ячейках тоже будет красоваться “#Н/Д”, и т.д. Если в таблице имеют место многочисленные сложносоставные связи, то значительная её часть покроется этим выражением.

По условию задачи не будем настаивать на изменениях в заполнении таблицы депозитного отдела, а попытаемся самостоятельно найти выход. Для этого применим сразу две дополнительные функции. Первая из них – “ЕЧИСЛО()” из группы Проверки свойств и значений. Данная функция получает в виде аргумента произвольное выражение, и принимает значение “ИСТИНА”, если это выражение является числом (возвращает число), или “ЛОЖЬ” если выражение числом не является. Вторая функция – Логическая - “ЕСЛИ()”.

Эта функция проверяет истинность некоторого логического выражения, и принимает одно из указанных значений. Одно из значений, если логическое выражение истинно, второе, если оно ложно. Совмещенные в одну формулу эти функции будут выглядеть следующим образом:

Работает эта конструкция следующим образом: Функция “ГПР()” ищет в исходной таблице сумму погашения депозитов за указанную дату. Она может возвратить либо число – собственно сумму, либо выражение “#Н/Д”, если искомая дата в исходной таблице не найдена. Функция “ЕЧИСЛО()”, имея в виде аргумента “ГПР()”, соответственно, принимает значение “ИСТИНА” или “ЛОЖЬ”. Далее функция “ЕСЛИ()” ведёт себя следующим образом:

•  
• либо принимает значение найденное функцией “ГПР()”, в случае, если ЕЧИСЛО()=ИСТИНА, то есть, если найденное значение является суммой погашаемого депозита;
•  
• либо принимает значение 0, если ЕЧИСЛО() не ровно ИСТИНА, то есть, если “ГПР()” вернула не число, а выражение “#Н/Д”.

Таким образом, на экране мы увидим следующую таблицу:

Рассмотрим ещё более сложную и интересную задачу. Допустим, та же таблица погашения депозитов ведётся в разбивке по клиентам. Кроме того, итоговая сумма гашения за день в таблице депозитного отдела не вычисляется за ненадобностью этому отделу. Однако нас интересует именно итоговая сумма в день. В рассматриваемом примере таблица, созданная депозитным отделом, будет выглядеть примерно так:

Обратите внимание, что и в этой таблице отсутствуют записи на некоторые даты. Кроме того, по невнимательности заполняющего нарушена хронологическая последовательность. Однако с точки зрения задач депозитного отдела особых нарушений в таблице нет, ибо все клиенты и все суммы учтены. В этой ситуации нам предстоит наладить процесс, при котором данные будут автоматически корректно считываться из исходной таблицы с формированием итогов за день.

Ни одна из применяемых ранее функций для решения данной задачи не подходит, так как все они могут находить только одно значение в исходной таблице. Поэтому мы рассмотрим ещё одну, последнюю в этой статье, группу функций – “Работа с базой данных”, и конкретно, функцию “БДСУММ()”.

Хотя то, что понимается здесь под “Работой с базой данных” у бывалого программиста вызовет лишь снисходительную усмешку, но все же набор возможностей, предоставляемых этой группой функций, достаточно велик. Мы, правда, рассмотрим его несколько однобоко – для решения всё той же задачи. Однако, уловив суть, можно применять функции работы с базой данных для самых разнообразных целей.

Итак, база данных в Excel - это прямоугольная область ячеек, в которой строки являются записями, а столбцы - полями. Верхняя строка списка содержит названия каждого столбцов (см. исходную таблицу). Функция “БДСУММ()” находит и суммирует числа из указанного поля базы данных, если записи, содержащие эти числа удовлетворяют заданному условию. Таким образом, сначала отфильтровываются записи, отвечающие условию (условиям), а затем, числа из указанного поля в этих записях суммируются. В данном примере будем применять единственное и самое простое условие – записи в таблице должны соответствовать заданной дате, хотя это далеко не предел возможностей данного вида функций.

Начнём с того, что в данном случае, ось времени в “получающей” таблице удобнее расположить горизонтально. Одна строка будет занята возрастающим рядом дат. Однако над ней должна располагаться строка, в каждой ячейке которой будет находиться заголовок столбца с датами “передающей” таблицы, т.е. “Дата”. Дело в том, что конструкция из двух, одна над другой расположенных ячеек, и является условием для фильтрования данных. Верхняя ячейка содержит заголовок столбца, данные из которого будут фильтроваться, а нижняя – признак, по которому происходит фильтрование. Так, в нашем примере, из исходной таблицы будут отбираться строки, дата в которых соответствует той, что указана в нижней ячейке. А так как даты в “принимающей” таблице указываются подряд, начиная с текущей, мы добиваемся процесса автоматической сортировки сумм гашения депозитов по времени. Отсортировав все суммы за одну дату, функция “БДСУММ()” их сложит и поместит в “принимающую” таблицу. Набор формул в принимающей таблице за одну дату будет выглядеть следующим образом:

При записи функции сначала задаётся область базы данных (см. исходную табл.). В нашем случае предполагается, что база данных состоит из 100 строк. Затем указывается номер столбца базы, из которого будут извлекаться данные – 3. Наконец, указывается область с условием, расположенная уже на местном листе. После окончания ввода данных, функция готова к копированию в остальные ячейки, расположенные правее. Полностью сформированная “принимающая” таблица будет выглядеть следующим образом:

Строку 1 рекомендуется скрыть. Единственный недостаток применяемой нами функции: она не умеет “вытягивать” данные из неоткрытых в данный момент файлов. Однако это ограничение легко обойти. Достаточно созданную таблицу записать в исходный файл на отдельный лист, и с помощью ранее описанных функций извлекать из неё приведённые в порядок данные.

ВЕСТНИК Хабаровской государственной академии экономики и права

Главная