Программирование в Excel или записки чокнутого программера

Автор: WargaL

Привет всем! Это я, WargaL, и сегодня мы поговорим о программировании в Excel. Да-да! Вы не ослышались, именно в Excel. Вообще, для тех, кто не знает, Excel - это не какая-нибудь новая, навороченная среда программирования. Это обычный редактор электронных таблиц, который входит в состав MS Office. По сути Excel не предназначен для программирования. Основная его задача – подсчет формул в ячейках электронной таблицы. Спросите: тогда как будем программировать? Я отвечу: создадим такую последовательность формул, которая будет эквивалентна программному коду. Главное слово – эквивалентна, т.к. эта связка не будет являться последовательностью команд. Это всего-навсего куча организованных формул. Разница между кучей формул и кучей команд огромна. Во-первых, команды выполняются поочередно (т.е. последовательно), а формулы считаются одновременно (на самом деле не совсем одновременно, но я недалек от истины). Во-вторых, некоторые команды, в силу первой причины, могут, не выполняться, а некоторые наоборот, могут быть выполнены неопределенное количество раз. Формулы же считаются всем скопом и только один раз. Реализовать в таких условиях простой (с точки зрения программирования) цикл невозможно. Его можно только сымитировать. Как? Читаем дальше!

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

ЧАСТЬ 1. Азы.

А начнем мы вот с чего. Это интерфейс программы Excel.

Что мы видим на рисунке:
1 – главное меню
2 – панель «форматирование»
3 – имя выбранной ячейки (вернее, ее координаты)
4 – содержание ячейки (строка формул)
5 – поле электронной таблицы
6 – оси координат
7 – выделенная ячейка
8 – маркер заполнения
9 – количество листов и номер выбранного
10 – полосы прокрутки

Как видно, не очень похоже на среду программирования. Кстати, если у кого оси координат буквенно-числовые, а не числовые, то это можно исправить. Для этого необходимо войти в меню «сервис», что в главном меню, и выбрать пункт «параметры...». Затем перейти на вкладку «общие» и поставить галку на чекбокс «стиль ссылок R1C1». Я буду пользоваться числовыми ссылками, поэтому, во избежание разногласий, настоятельно советую включить именно его. Вообще, в Excel, как вы догадались, 2 режима ссылок: числовые и буквенно-числовые. Отличая в них следующие. В числовом, адрес ячейки выглядит так: RxCy. Где x – номер строки, а y – номер колонки. В буквенно-числовом же, он выглядит совсем по другому. Здесь номера колонок записаны в виде букв латинского алфавита (a – iv, всего 256), и в адресе ячейки они стоят на первом месте, т.е. например: A1, B23, FZ13... Итак, E17 и R17C5, ссылаются на одну и туже ячейку. Заметьте: использовать одновременно обе системы ссылок нельзя!

Кроме того, в Excel существует абсолютная и относительная адресация. Абсолютная отсчитывается от левого верхнего угла (начало координат), например: R1C1 (или $A$1 для буквенно-числовой адресации). Она не изменяется при копировании формулы. Относительная отсчитывается от выбранной ячейки и обозначается «[]» (квадратные скобки), например: R[1]C[1] (по умолчанию используется относительная). Переключение между абс. и относ. осуществляется нажатием F4. При копировании относительные адреса «смещаются» вместе с ячейкой. Например: в ячейке R10C5 стоит ссылка на R1C1. А в ячейке R10C6 - на R[1]C[1]. В этих ячейках будут разные значения, т.к. они ссылаются в разные места: R10C5 - на R1C1, а R10C6 - на R11C7. И вот, что произойдет при копировании этих ячеек на 5 строк вниз: R15C5 (бывшая R10C5) – ссылка на ту же R1C1, а R15C6 (бывшая R10C6) – не на R11C7 (как раньше), а на R16C7. Т.е. запись R[1]C[1] означает, что это адрес ячейки смещенной относительно выбранной на 1 вправо и 1 вниз. Если непонятно, то я могу лишь посоветовать прочесть какую-нибудь обучающую книгу по Excel. Надеюсь, у авторов этой книги получится лучше объяснить. Ну, а мы движемся дальше.

И следующая на очереди – строка формул. Вернее, не сама строка, а формулы, которые в нее можно поместить. Вообще, ячейка таблицы может содержать данные различных типов: это может быть и число, и дата, и строка, и... даже формула. Для того, чтобы Excel мог отличить строку формул от просто строки, первым знаком в строке с формулой должен быть «=». Это объяснит Excel, что перед ним формула, а не просто куча символов. Для того, чтобы добавить в ячейку формулу, можно ввести ее с клавиатуры или воспользоваться кнопкой «вставка функции». При нажатии на нее запустится «мастер функций» и в появившемся окне вам предложат на выбор все доступные функции. Для удобства пользования они рассортированы по разным группам (см. рисунок). При наведении на какую-либо функцию внизу окошка появляется ее описание, а в верхней части - поле для поиска подходящей формулы по необходимому действию. Это может быть полезным при поиске, кроме того, всегда можно воспользоваться помощью электронного помощника.

Теперь немного о самих формулах. Операндами для них могут являться ячейки (вернее значения ячеек), константы и, внимание, другие формулы. Т.е. можно сразу записать: «=ABS(COS(R[1]C[1]))» вместо того, чтобы разделять их по разным ячейкам. Естественно этих функций в одной ячейке может быть и больше (вплоть до 8), главное чтобы суммарная длина строки не перевалила за 256 знаков. Название и описание всех функций использованных мной я приведу ниже, по мере их необходимости.

Осталось последнее, о чем надо рассказать: маркер заполнения и копирование функций. В принципе тут и говорить-то не о чем. Маркер заполнения используется собственно для копирования формул. Он представляет собой маленький квадратик в нижнем правом углу выбранной ячейки (или диапазоне, см. рисунок). Он нужен для быстрого и удобного заполнения пустых ячеек посредством копирования выделенной. При копировании, как я и говорил, относительная адресация делает свое дело. Аргументы функции перемещаются вместе с формулой. Для более полного понимания советую немного поиграть с ячейками и формулами в Excel. Или прочесть обучающую литературу.

ЧАСТЬ 2. Попытка приручения.

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

Все максимально просто: берем число из одной ячейки, а результат записываем в другую. Но...результат-то надо как-то высчитать! Алгоритм перевода числа в двоичную систему счисления я описывал в статье «двоичный код» (задание сформулировано именно под него, чтобы не забивать вам голову каким-нибудь другим). Расскажу вкратце: последовательно делим исходное число на 2, до того момента пока в остатке не останется 1. При этом записываем, опять-таки последовательно, в результат либо 0, либо 1, все зависит от четности получаемого числа. Основное в этом алгоритме то, что все делается последовательно, т.е. поочередно друг за другом. В Pascal это все реализовывалось через цикл. А тут как? Ведь вы уже знаете, Excel считает таблицу один раз и всю целиком. Как реализовать цикл? Надо как-то выходить из получившейся ситуации! И...выход есть! Цикл можно сымитировать. Т.е. это будет не настоящий цикл, а нечто похожее на его разворот, мы заранее оставим некоторое количество ячеек и заполним их определенными формулами.

Теперь по поводу всего остального. Разработку «программы» будем вести с веху вниз. Как и в любом уважаемом языке программирования. Т.е. исходными данными для нижележащей ячейки будет результат, полученный в вышележащей. А циклы, в этом случае будем «растягивать» вправо (по строке). Т.о. получается, что максимальное количество повторений в цикле – 256. Если кого этот вариант не устраивает, тот может писать «программу» слева направо, «растягивая» цикл сверху вниз (65536 повторений).

Начнем. Создадим новый документ. Для этого надо обратиться в ближайший офис Microsoft :). Для того чтобы избежать ошибок (#ЗНАЧ!, #ДЕЛ/0!, #ССЫЛКА!, #ЧИСЛО!, #ПУСТО, #ИМЯ?), сразу заполним первую ячейку исходными данными. Н.р.: R1C1 = 255. Я еще вдобавок выделил ее красным цветом (что б выделялась). Да, еще забыл сказать, чтобы вы увеличили ширину второго столбца, т.к. мы будем выводить в него результат (ячейка R1C2). Если этого не сделать, Excel автоматически укоротит нашу строку или поставит вместо нее «#####».

С вводом данных покончено. Приступим к обработке. В ячейку R2C1 вписываем следующее: «=R[-1]C» (можно просто поставить знак равенства, а затем указать на ячейку R1C1). Тем самым делаем ссылку на ячейку, в которой записано исходное число.

А вот в ячейку R2C2, впишем следующее: «=ОТБР(RC[-1]/2;0)». Я поясню. Функция «ОТБР(число; разряд)» принадлежит к группе «математические» и делает следующее: отбрасывает от параметра «число» энное количество знаков, оставляя после десятичной точки ровно столько, сколько записано в параметре «разряд». В параметре «число» у нас указано «RC[-1]/2», т.е. половина числа находящегося в ячейке слева. А в параметре «разряд» красуется «0», т.е. до целых.

А теперь, делаем следующее: «растягиваем» за маркер заполнения ячейку R2C2 до конца строки («до конца строки» – значит до ячейки 256).

Получается последовательность из чисел: 255 127 63 31...1 0 0 0... Теперь примемся за третью строку. В ячейку R3C1 впишем: «=ЕСЛИ(НЕЧЁТ(R[-1]C)=R[-1]C;1;ЕСЛИ(R[-1]C=0;"";0))» Не надо пугаться. Тут все просто. Функция «ЕСЛИ(логическое_выражение; если_истина; если_лож)» работает так: если «логическое_выражение» является истиной (т.е. правда или true), то ячейка принимает значение «если_истина». Иначе – «если_лож».

If логическое_выражение Then если_истина Else если_лож

Может так будет понятней?

Адрес R[-1]C, значит ячейка сверху. Функция «НЕЧЁТ(число)» округляет «число» до ближайшего нечетного. Если само «число» является нечетным, то она (функция) равна этому числу.
Выражение «НЕЧЁТ(R[-1]C)=R[-1]C» является истинным, только тогда, когда в ячейке «R[-1]C» записано нечетное число. Я не нашел в Excel функцию, которая проверяла бы число на четность. Поэтому пришлось сделать так.
В общем, в Pascal вся эта строка выглядит так(естественно вместо ссылки должна быть переменная):

If not odd(R[-1]C) Then Result:='1' Else If R[-1]C=0 Then Result:='' Else Result:='0'

«Растягиваем» ячейку R3C1 за маркер заполнения на всю строку.

Следующая строка. В ячейку R4C1 запишем «=R[-1]C» (ссылка на ячейку сверху). А в ячейку R4C2 – «=СЦЕПИТЬ(R[-1]C;RC[-1])». Функция «СЦЕПИТЬ(строка1; страка2;...;строка30)» склеивает строки вместе, превращая их в одну целую. Ячейка «R[-1]C» - ссылка на ячейку сверху, а «RC[-1]» - на ячейку слева. И «растягиваем» «R4C2» до конца строки.

Т.о. в ячейке R4C256 мы имеем конечный результат. Но нам его надо вписать в R1C2. Поэтому, делаем ссылочку в ячеечке «R1C2» на «R4C256» (R1C2 = «=R[3]C[254]» или R1C2 = «=R4C256»). Все!

А обратно слабо? Мне нет.
Итак, цель: Число, записанное в двоичном виде (строка), из ячейки R1C1, перевести в десятичную систему счисления и записать в ячейку R1C2.

Летсгоу. Первым делом создаем новый документ. Увеличиваем ширину первой колонки и окрашиваем ячейку R1C1 в красный цвет. Вводим исходное число, н.р.: «1111111» (только из 1 и 0 – система счисления-то двоичная). В ячейку R2C1 введем «1» (это будет аналогом строки i:=1;). В R2C2 вводим «=RC[-1]+1» и «растягиваем» ее до конца строки.

Это аналог инициализации цикла и перебора элементов. Они нам необходимы для того, чтобы разбить исходную строку на отдельные разряды и записать их в отдельные ячейки.
Идем дальше. В ячейку R3C1 вписываем «=ЕСЛИ(ДЛСТР(R1C1)>=R[-1]C;ПРАВСИМВ(ЛЕВСИМВ(R1C1;R[-1]C);1);"")».
Функция «ЕСЛИ...» вам уже известна.
Функция «ДЛСТР(строка)», определяет длину параметра «строка»
Функция «ПРАВСИМВ(строка; количество)» возвращает указное «количество» символов с конца строки «строка».
Функция «ЛЕВСИМВ(строка; количество)» аналогична предыдущей, с той лишь разницей, что делает это с другой стороны – с начала.
Комбинация «ПРАВСИМВ(ЛЕВСИМВ(R1C1;R[-1]C);1)» вырезает из строки R1C1 символ с номером R[-1]C. «Растягиваем» ячейку R3C1 до конца третьей строки.

В ячейке R4C1 ссылка на R3C1 – «=R[-1]C». А в R4C2 «=ЕСЛИ(НЕ(R[-1]C="");RC[-1]*2+R[-1]C;RC[-1])». Тут должно быть все понятно. И «растянем» R4C2 на всю длину оставшейся строки.

В ячейке R4C256 находится искомый ответ. Делаем ссылочку в ячейке R1C2 на R4C256.

Все. Мы перевели число из десятичной в двоичную систему счисления и обратно.

И в заключение хотелось бы отметить то, что данная статья наглядно доказывает возможность применения Excel для реализации несложных алгоритмов. Но, если в алгоритме присутствуют подциклы (циклы второго, третьего и т.д. порядков), то реализация заметно усложняется. И если с циклом второго порядка все более или менее понятно; надо «растягивать» ячейки не только по строке, но и вниз (эквивалент плоскости); то реализацию цикла третьего порядка можно осуществить только теоретически. Чисто теоретически возможно подключить другие листы книги для придания объема плоскому циклу (т.е. создать цикл третьего порядка). А про циклы более высоких порядков придется забыть вообще, их реализация невозможна. Также заметно усложнена реализация алгоритмов построенных в обход основных принципов структурного программирования (таких как НРА). Теперь о хорошем. Алгоритмы (программы) реализованные в Excel могут быть использованы в офисных приложениях для мобильных устройств (смартфоны, КПК и коммуникаторы). Это может заметно упростить решение очередной нудной контрольной.

P.S.