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

Иван Арисов,

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

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

Часть 3

Теперь представим, что некий человек или подразделение решили составить представление о работе организации в целом. Для этого необходимо собрать, обобщить, и обработать интересующую информацию из нескольких источников. Обрабатывать эту информацию, т.е. вести расчёты и проводить анализ данных будем с помощью таблиц Excel. Но как собрать эти разрозненные данные в свою таблицу? Можно, конечно, просматривать все интересующие нас файлы (разбираясь в их структуре и логике), вычленять интересующую нас информацию из моря лишней, копировать её, и заносить в одну сводную таблицу. Причём, если организация активно живёт и развивается, информацию необходимо будет регулярно актуализировать, повторяя вышеописанный процесс.

Попытаемся более рационально решить задачу “вытягивания” данных из чужих таблиц. Задача не из легких. Зачастую из-за разницы в логике “передающего” и “принимающего” файлов, она становится не решаемой, особенно, если нет содействия со стороны “хозяев” информации.

Однако, при желании, всё же можно наладить сбор информации из внешних источников на должном уровне. Несколько примеров будет рассмотрено ниже.

Собственно, “вытащить” данные из-за пределов текущего листа весьма просто, посредством тех же ссылок. Чтобы сослаться на ячейку, расположенную на другом листе того же файла, необходимо слева от координат ячейки через знак “!” приписать имя листа. Так запись “=Имя_листа!A1” в произвольной ячейке означает, что в ней будет отображаться содержимое ячейки A1, расположенной на листе с указанным именем, в том же файле.

Можно ссылаться на ячейки и из других файлов. Для этого достаточно еще левее в квадратных скобках “[…]” указать имя файла-донора.

Например, “=[Имя_файла.xls]Имя_листа!$B$1”.

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

“='Путь_к_файлу\[Имя_файла.xls]Имя_листа'!$B$1” (Обратите внимание на расположение одинарных кавычек “'…'”). Это изменение вполне объяснимо, ведь если файл-донор не загружен, то чтобы считать данные, его надо как-то найти.

Теперь при открытии таблицы со ссылками на внешние данные, Excel будет спрашивать нужно ли обновить их, получив положительный ответ, искать указанные в ссылках файлы и считывать оговоренные данные.

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

“='Путь_к_файлу1\[Имя_файла1.xls]Имя_листа1'!$B$1/'Путь_к_файлу2\[Имя_файла2.xls]Имя_листа2'!$X$24 * 100”.

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

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

Выход из ситуации достаточно прост. Как известно, Excel позволяет присваивать ячейкам и группам ячеек индивидуальные имена. Для того, чтобы присвоить имя ячейке (ячейкам) необходимо выделить их мышью, и ввести какое-нибудь имя в специальную рамку, расположенную на экране слева вверху.

При этом, в ссылке необходимо указать не координаты ячеек, а их имя (“='Путь_к_файлу\Имя_файла.xls'!Имя_области”). Обратите внимание, что Имя листа не указывается, так как имена областей не могут повторяться в пределах одного файла. Ценность именования ячеек, применительно к рассматриваемой проблеме заключается в том, что ссылка на имя области будет находить эту область независимо от места расположения её на листе. Если, например, для каких-либо вычислений нам нужен некий коэффициент, рассчитываемый другим отделом, достаточно присвоить ячейке с этим коэффициентом, скажем, имя “Koeff”. После этого, можно смело ссылаться на это имя, и независимо от того, куда будут перемещать ячейку с коэффициентом его "расчётчики", мы всегда сможем иметь его актуальное значение.

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

Для работы с группами ячеек специально предназначены несколько видов функций Excel. Один из этих видов (Ссылки и массивы) мы сейчас затронем. Сначала необходимо договориться с сотрудниками планового отдела, чтобы они интересующую нас область из пяти ячеек как-нибудь назвали (например, “Расходы”). Далее, решая поставленную задачу, мы применим функцию “ИНДЕКС()”. Данная функция просматривая заданную область ячеек, возвращает значения одной из них, исходя из указанных номеров столбца и строки.

Итак, в качестве имени массива задаем “Расходы” (со всеми необходимыми путями – см. выше). Данный массив состоит из одной строки и пяти столбцов, поэтому, как номер строки указываем “1”. Со столбцами поступим несколько сложнее – укажем не конкретную цифру, а ссылку на ячейку, например, “G12”).

Всё готово. Теперь, указывая в ячейке “G12” порядковый номер дня недели (1-5) мы будем иметь в ячейке с функцией “ИНДЕКС()” план расходов на этот день даже на заглядывая в файл плановиков. Причём, плановики нисколько не ограничены в своём творчестве. Они могут сколько угодно менять структуру своего файла (одно ограничение – нельзя вставлять или удалять столбцы непосредственно из области “Расходы”). Конкретная схема решения, конечно, исключительно условна, важным является лишь общий смысл применения функций Excel для считывания внешних данных.

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

Функция “ВПР()” действует аналогично, она просматривает левый столбец заданной таблицы, находит в этом столбце ячейку, значение которой идентично некоторому эталонному. Далее функция отсчитывает заданное количество столбцов вправо и возвращает содержимое полученной ячейки.

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

А наша таблица имеет следующий вид:

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

Итак, начинаем формировать нашу таблицу. В клетку A1, соответствующую началу отсчёта времени вписываем функцию “СЕГОДНЯ()”. В ячейку A2 – формулу “=A1+1”, после чего копируем её в необходимое количество нижележащих ячеек. Таким образом, мы получили ряд дат на месяц вперёд, который всегда будет начинаться с текущей даты. Путём несложных дополнений, можно легко добиться, чтобы из таблицы исключались, например, выходные дни и т.д.

В ячейку B1 заносим функцию “ГПР()”, со следующими параметрами: В виде исходного значения указываем ячейку A1 – таким образом, функция будет искать сумму гашения кредита именно на эту дату. Таблицу задаём выражением “$1:$25 ” – с первой по двадцать пятую строки (все необходимые пути дописываются автоматически). Номер строки указываем “25” - именно из двадцать пятой строки будут считываться суммы погашения кредитов. Как диапазон просмотра укажите “ЛОЖЬ”, об этом параметре мы поговорим позже. Теперь готовую функцию необходимо скопировать в нижележащие ячейки, после чего, наша таблица готова, и выглядит следующим образом:

Отметим, что, несмотря на громоздкость, благодаря Мастеру функций, составлять подобные формулы достаточно просто.

Предположим, сегодня 03 августа 2000 г., а в таблице кредитного отдела содержатся следующие данные:

Тогда наша таблица будет выглядеть следующим образом:

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

Проджолжение следует

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

Главная