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

Иван Арисов,

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

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

Часть 2

Продемонстрируем преимущества Excel при проведении обычных повседневных вычислений на примере.

Имеются плановые данные о валютообменных операциях коммерческого банка и сети из 10 дополнительных офисов на год по месяцам.

Требуется вычислить планируемый объём доходов от валютообменных операций в разбивке по месяцам и отделениям банка по формуле:

Доход = Кр х Мр + (Пр – Кр) х Мо,

где Кр – сумма валюты, купленная в розницу;

Мр – розничная маржа;

Пр – сумма валюты, проданная в розницу;

Мо – оптовая маржа.

Для решения задачи построим следующую расчётную таблицу:

Как обычно, формула записывается только один раз. В остальные ячейки она просто копируется. Таким образом, мы применим эту формулу 11 х 12 = 132 раза.

Конкретные результаты решения не важны, ибо пример условный.

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

Ну что, “1-0” в пользу Excel? Но это далеко не всё. О чём думает бухгалтер только что произведя сложные, продолжительные и важные расчеты? Правильно – не ошибся ли он. Применяя калькулятор, можно только одним способом проверить достоверность полученных данных – пересчитать всё заново несколько раз. В Excel ситуация совершенно иная – все исходные данные по-прежнему у нас перед глазами, для проверки достаточно убедиться в их достоверности, проверить структуру исходной формулы и адекватность её поведения при копировании. Если всё сходится – значит расчёты верны. “2-0” в пользу Excel! Но и это ещё не все. Если в расчёты всё же вкралась ошибка? Скажем, при вводе данных в таблицу рука бухгалтера дрогнула, и он ввёл не то число. Что можно предпринять после выявления ошибки? Применяя калькулятор, только одно – пересчитать всё заново и опять несколько раз проверить. В Excel же достаточно внести исправления в исходные данные Все результаты, включая промежуточные, будут мгновенно пересчитаны. “3-0” в пользу Excel!

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

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

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

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

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

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

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

Команда “Специальная вставка” также позволяет производить несколько операций (сложение, вычитания, деление, умножение) над вставляемыми числами. Допустим, есть таблица с данными по объёмам продаж в рублях, а нам хочется видеть её в тысячах рублей, или в долларах по некоторому курсу. Excel позволяет выполнять подобные преобразования практически мгновенно. В любую ячейку в стороне вводим “1000” или курс доллара для пересчёта. Копируем содержимое ячейки, выделяем нашу таблицу, выполняем команду “Специальная вставка” с параметрами “Операция – разделить”. Всё, таблица имеет желаемый вид. Теперь представьте, сколько времени займёт пересчёт в долларовый эквивалент таблицы 10х10 ячеек с помощью калькулятора?

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

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

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

Для удобства использования функции разделены по видам: Финансовые, Математические, Статистические, Текстовые, Дата и время, Ссылки и массивы и т.д. Каждый вид функций предоставляет пользователю набор уникальных возможностей. Кратко рассмотрим несколько функций даты и времени.

Вообще дата в Excel – такой же равноправный вид данных, как и обычные числа. Благодаря этому имеется возможность производить с датами ряд интересных операций. Какое, например число будет через 93 дня после 19.07.00 г.? Не нужно искать календарь, просто введите в A1 – “19.07.00”, в B1 – “93”, а в C1 – “=A1+B1”. Будет 20.10.00 г. А сколько дней будет длиться один из трёх равных по величине отрезков времени между 25.06.00 г. и 22.03.01 г.? Вводим в A1 – “25.06.00”, в B1 – “22.03.01”, а в C1 – “=(B1-A1)/3”. Получаем 90 дней.

Всё дело в том, что Excel представляет себе любую дату как количество дней, прошедшее с начала 1900 г. Так, 01 января 1900 г. трактуется как “1”, 02 января 1900 г. как “2”, а 11 августа 2000 г. как “36749”. Причём Excel знает точное количество дней в месяцах и различает високосные годы и простые. Это и делает возможными некоторые несложные, но полезные арифметические операции с датами. Умение Excel правильно обращаться с датами положено в основу многих финансовых функций, позволяющих просчитывать аннуитеты, графики погашения кредитов и операции по начислению процентов самыми различными способами, инвестиционные проекты, операции с ценными бумагами и многое другое.

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

У вас много деловых партнеров во всех уголках России? Хотите знать, сколько времени сейчас у каждого из них? Вот, наверняка не единственный, но весьма простой способ. Введите в любую ячейку выражение “=ТДАТА()-7/24”, установите формат ячейки как временной – вот вам Московское время (для Хабаровска) т.д. Минуты такой операции также поддаются. Если немного потрудиться над условным форматированием или логическими функциями, то Excel не только сообщит местное время контрагента, но и предупредит об обеденном перерыве, окончании рабочего дня и прочих помехах телефонному разговору.

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

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

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

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

Главная