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

Правила ввода формул

Ввод любой формулы всегда нужно начинать со знака равенства «=». В формуле могут находиться:

знаки арифметических действий: «+», «–», «*», «/», «^» (знак возведения числа в степень), знак «%»;

числа, строки (они берутся в кавычки);

ссылки на ячейки и диапазоны ячеек (как на текущем листе, так и на других листах книги) для определения порядка вычислений, скобки;

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

=А2+В2 — сложение значений двух ячеек;

=А1*0,8 — умножение числа из ячейки А1 на 0,8;

=D1^2+1 — возведение числа из ячейки D1 в квадрат и прибавление единицы к результату;

=СУММ(А1:А5) — суммирование значений из диапазона ячеек А1:А5. Это пример использования встроенной функции. Здесь СУММ — имя функции, А1:А5 — диапазон ячеек, ее единственный аргумент, заключенный в скобки;

=МУМНОЖ(B1:B2;B7:C7) — вычисление произведения матриц B1:B2 и B7:C7. Как видно, данная функция имеет два аргумента, которые являются массивами данных из выделенных диапазонов. Если функция имеет несколько аргументов, они отделяются друг от друга точкой с запятой. В качестве аргументов функций вы можете использовать ссылки на ячейки и диапазоны на текущем листе и на других листах. В последнем случае перед адресом ячейки или диапазона следует ввести название листа, отделенное штрихами, и поставить разделитель «!», например ‘Лист1’!В2, ‘Лист 3’!А1:С4. Штрих можно ввести, нажав клавишу «Э» при активной английской раскладке.

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

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

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

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

Рис. 17. Просмотр назначения функции

Часто применяемые на практике функции вынесены в меню кнопки , которая находится в группе Редактирование на вкладке Главная. Рассмотрим задачи, связанные с их использованием.

Функция суммирования данных является самой востребованной, именно поэтому задействовать ее в Excel проще всего.

Если данные расположены в одном столбце или строке, выделите их и нажмите кнопку . Результат сложения тут же отобразится внизу (в случае столбца) или справа (в случае строки) ряда. Выполните на нем щелчок, и в строке формул вы увидите, что Excel задействовал функцию =СУММ().

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

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

1. Выделите щелчком ячейку, в которой нужно отобразить результат суммирования.

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

3. Выделите нужный диапазон ячеек (при необходимости — несколько диапазонов, удерживая нажатой клавишу ). При этом он будет охвачен бегущей рамкой, а скобках формулы появятся ссылки на диапазоны ячеек (рис. 18).

4. Нажмите для получения результата.

Рис. 18. Суммирование диапазона ячеек

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

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

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

3. Нажмите для подсчета нового результата.

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

Среднее. Вызывает функцию =СРЗНАЧ(), с помощью которой можно подсчитать арифметическое среднее диапазона ячеек (просуммировать все данные, а затем разделить на их количество).

Число. Вызывает функцию =СЧЕТ(), которая определяет количество ячеек в выделенном диапазоне.

Максимум. Вызывает функцию =МАКС(), с помощью которой можно определить самое большое число в выделенном диапазоне.

Минимум. Вызывает функцию =МИН() для поиска самого маленького значения в выделенном диапазоне.

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

Рис. 19. Результаты вычислений в строке состояния для выделенного диапазона

Комплексные расчеты

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

Задача 1. Выбрать оптимальный тарифный план при подключении к сети сотовой связи, если в месяц планируется 2,5 часа разговоров внутри сети и 0,5 часа разговоров с абонентами городской сети и других сотовых операторов. Цены на услуги представлены в таблице на рис. 20 без учета НДС.

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

Рис. 20. Таблица для расчета оптимальной стоимости услуг связи

Выделите ячейку А1 и наберите в ней название работы. В ячейках следующей строки создайте заголовки таблицы. Воспользуйтесь кнопкой Перенос текста из группы Выравнивание, чтобы отрегулировать высоту строки. Ширину столбцов подберите вручную перетаскиванием границ их заголовков. С помощью кнопок выравнивания поместите текст в «шапке» посередине (относительно верхней и нижней границы ячейки) и по центру (относительно левой и правой границ).

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

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

Суммарные затраты будут складываться из абонентской платы, стоимости разговоров внутри сети и с абонентами других сетей, а также из налога на добавленную стоимость. Это нужно представить в виде формулы. Начнем расчеты с первого тарифного плана Минутка. К содержимому ячейки В3 (абонплата) нужно прибавить произведение ячеек С3 и Е3 (общая стоимость разговоров внутри сети) и произведение ячеек D3 и F3 (общая стоимость разговоров с абонентами других сетей). Так будет подсчитана сумма затрат без учета НДС. Чтобы прибавить к полученной сумме налоговую ставку, необходимо умножить результат на 18% и прибавить его к стоимости услуг. Это результат и должен отобразиться в ячейках последнего столбца.

Выделите ячейку G3, наберите в ней знак «=» и нажмите кнопку . При этом будет вставлена функция суммирования, автоматически определен диапазон ячеек, а его адрес будет выделен в скобках функции. Поскольку данный диапазон нам не подходит, нажмите , чтобы удалить ссылку из скобок. Затем выполните щелчок на ячейке В3, чтобы поместить ее адрес в формулу, и поставьте «;» для отделения следующего аргумента (слагаемого). Далее щелкните на ячейке С3 для ввода ссылки на нее в формулу, наберите знак умножения «*» и выделите ячейку Е3. Отделите новый аргумент точкой с запятой. Для ввода последнего слагаемого щелкните на ячейке D3, введите «*» и выделите ячейку F3. Так мы создали часть формулы, которая отвечает за подсчет суммарных затрат без учета налогов. Она должна выглядеть следующим образом: =СУММ(B3;C3*E3;D3*F3). Если вы где-то допустили ошибку, установите курсор возле нее щелчком мыши или с помощью клавиш движения и внесите исправления. Далее нужно прибавить налоговую часть формулы. Переместите курсор в конец за скобку и наберите «+». Ставка НДС составляет 18%. Следовательно, нужно умножить 18% на результат, полученный в первой части формулы. Наберите 18% и знак умножения «*», затем выделите первую часть формулы, не включая знака «=» (точно так же, как вы бы выделили текстовый фрагмент в Word), выполните на ней правый щелчок и задействуйте команду Копировать контекстного меню. Щелчком установите курсор в конец формулы, выполните правый щелчок и обратитесь к команде Вставить. Расчетная формула готова: =СУММ(B3;C3*E3;D3*F3)+18%*СУММ(B3;C3*E3;D3*F3). Нажмите , чтобы увидеть итоговую сумму.

Набирать вручную формулу в остальных ячейках не нужно. Воспользуйтесь автозаполнением — выделите ячейку с формулой и протяните рамку за маркер вниз. В результате формула будет скопирована в нижние ячейки, а ссылки в ней автоматически заменятся на адреса ячеек из той же строки.

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

В завершении работы отформатируйте таблицу, чтобы она имела презентабельный вид. В первую очередь стоит перевести ячейки с тарифами в денежный формат. Для этого выделите все диапазоны с денежными величинами в столбцах Абонплата, Минута внутри сети, Минута в другие сети, Суммарно с учетом НДС 18% и Оптимум, щелкните на стрелке раскрывающегося списка Числовой формат в группе Число и выберите пункт Денежный. Выделенные ячейки будут переведены в денежный формат, но поскольку в Excel по умолчанию отображается два десятичных знака, в конце каждого числа появится незначащий ноль. Чтобы скрыть его, выделите диапазоны и щелкните на кнопке Уменьшить разрядность . Для некоторых ячеек может понадобиться скрыть и второй ноль после запятой. Сделать это можно аналогичным образом.

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

В заключение отформатируйте название задания и ячейку с конечным результатом. Выделите ячейки, на которые по длине распространяется название, и щелкните на стрелке кнопки и задействуйте команду Объединить ячейки. Затем задайте для ячейки с названием определенные параметры шрифта и цвет фона с помощью инструментов группы Шрифт или примените к ней один из стилей, воспользовавшись кнопкой Стили ячеек в группе Стили. Оформите и «шапку» ячейки с оптимальной суммой. После выполнения всех операций форматирования таблица с расчетом должна принять вид, подобный тому, что представлен на рис. 21.

Рис. 21. Готовая таблица расчета оптимального тарифного плана

Относительная и абсолютная адресация

Адреса ячеек и диапазонов в Excel могут быть относительными и абсолютными. До сих пор мы говорили об относительных ссылках на ячейки и диапазоны, которые состоят только из номера строки и буквы столбца, например В2 или D4:D8. Преимущество относительной адресации состоит в том, что при копировании ячеек и использовании автозаполнения ссылки в скопированных формулах меняются автоматически (относятся к ячейкам текущей, а не исходной строки), поэтому нет необходимости набирать вручную каждую формулу. Наглядный пример: в предыдущем примере мы набрали только одну формулу в первой ячейке столбца Суммарно с учетом НДС 18%, а затем воспользовались автозаполнением. Однако на практике встречаются ситуации, когда адрес ячейки или диапазона необходимо зафиксировать, чтобы он не изменялся при копировании или автозаполнении ячеек. Для этого необходимо добавить перед номером строки и буквой столбца знак «$». Так, если сделать адрес ячейки В2 абсолютным, он будет выглядеть как $B$2. Также можно зафиксировать в ссылке адрес только столбца ($B2) или только строки (B$2). Это называется смешанной адресацией. Чтобы быстро изменить адресацию в готовой формуле, выполните по ней двойной щелчок, установите курсор на нужную ссылку и последовательно нажимайте клавишу для изменения типа адреса. Знак «$» можно добавлять в формулы и вручную с клавиатуры.

Рассмотрим использование абсолютной адресации на конкретном примере.

Задача 2. Рассчитать конечную стоимость товара для оптового покупателя в зависимости от оговоренного размера скидки.

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

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

Чтобы рассчитать цену с учетом скидки, необходимо от текущей цены отнять процент скидки. В нашем примере он равняется десяти. На первый взгляд решение задачи очень похоже на предыдущее, где прибавлялась процентная ставка НДС. Глядя на рис. 22, можно предположить, что в ячейке C4 необходимо из цены в ячейке В4 вычесть скидку, которая представляет собой произведение размера скидки из ячейки С1 на цену товара (В4). В виде формулы это запишется как =В4–С1*В4. Останется только распространить формулу на остальные ячейки столбца с помощью автозаполнения. Попробуйте выполнить описанные действия, и в конечном итоге вы обнаружите, что в вычислениях произошла ошибка. А заключается она в следующем.

При использовании автозаполнения ссылки в ячейках автоматически изменяются по мере копирования формул. В нашем случае формула, введенная в первую ячейку прайса, была правильной, но при попытке распространить ее на остальные ячейки столбца ссылка на ячейку с размером скидки стала меняться «сползая вниз» (превращаясь в С2, С3 и т.д.). Чтобы этого не происходило, ее адрес необходимо зафиксировать — сделать абсолютным. Для этого выполните на первой ячейке столбца скидки двойной щелчок (С4), поместите курсор на адрес ячейки, где находится величина скидки (в нашем случае это ячейка С1) и нажмите клавишу . При этом к номеру строки и букве столбца будет добавлен знак $ ($C$1), и адрес ячейки станет абсолютным — не изменится при автозаполнении столбца. В результате конечная формула будет выглядеть так: =В4–$C$1*В4. Теперь можно повторить процедуру автозаполнения, чтобы получить корректный результат. Щелкните на любой ячейке столбца скидок, чтобы убедиться в неизменности абсолютной ссылки. При изменении величины скидки весь ряд будет автоматически пересчитан.

Рис. 22. Прайс-лист для расчета цены с учетом скидки

Гораздо нагляднее использовать вместо абсолютной ссылки, содержащей знаки «$», имя ячейки, которое можно присвоить следующим образом: выделить щелчком ячейку и слева в строке формул набрать для нее уникальное имя. Щелкните на ячейке С1 (где указан размер скидки), слева в строке формул наберите Скидка. Затем в первой ячейке столбца цены со скидкой исправьте абсолютную ссылку $C$1 на имя ячейки Скидка. В результате должна получиться формула =В4–Скидка*В4. Осталось распространить формулу на все ячейки столбца с помощью автозаполнения.

В описанных выше примерах мы не упоминали об использовании кнопок группы Библиотека функций на вкладке Формулы и о Мастере функций для вставки встроенных функций в формулу. Эти моменты мы рассмотрим в примере расчета выручки от продажи товара, приведенном в подразделе «Построение графиков и диаграмм».

Ошибки в формулах

При работе с формулами в Excel нередко возникают ошибки, связанные не только с правильностью написания формулы, но и с корректным определением адресов ячеек и диапазонов с данными. Проведем краткий обзор способов выявления и устранения ошибок. Если Excel обнаружит ошибку в синтаксисе формулы (например, если между аргументами функции будет отсутствовать разделитель «;», будут пропущены или поставлены лишние скобки, обнаружится меньше аргументов, чем того требуется для данной функции), он выдаст сообщение об ошибке. Текст сообщения будет различным в зависимости от того, удалось Excel определить источник ошибки или нет. Когда система не может определить источник ошибки, появляется сообщение, представленное на рис. 23.

Рис. 23. Сообщение об ошибке, не распознанной Excel

В этом случае вернитесь к ячейке с формулой, перепроверьте ее и исправьте ошибку.

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

Рис. 24. Сообщение о распознанной ошибке

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

  • #ЗНАЧ! — ошибка в типе данных, используемых в формуле. Возможно, в одной из ячеек диапазона находится текст.
  • #ИМЯ? — ошибка в имени функции или адресах ячеек и диапазонов, присутствующих в формуле.
  • #ССЫЛКА! — удалены или перемещены ячейки или диапазоны, на которые ссылается формула.
  • #ДЕЛ/0! — при расчете происходит деление на ноль.
  • ###### — данные не умещаются по ширине в ячейку. Увеличите ширину столбца протаскиванием границы заголовка.

    Самая опасная ошибка — правильность ввода адресов ячеек и диапазонов в формулу. Система определяет только математические и синтаксические ошибки, но предугадать, данные каких именно ячеек должны присутствовать в формуле, не может. За эти должны внимательно следить вы.

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

    Без формул таблица Excel мало чем отличалась бы от таблиц, созданных в Word. Формулы позволяют выполнять очень сложные вычисления. Как только мы изменяем данные для вычислений, программа тут же пересчитывает результат по формулам.

    В отдельных случаях для пересчета формул следует воспользоваться специальным инструментом, но это рассмотрим в отдельном разделе посвященным вычислениям по формулам.

    Заполнение листов Excel формулами

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

    Задание 1. В ячейке F2 введите следующую формулу следующим образом: =D2+D2*E2. После ввода нажмите «Enter».

    Задание 2. В ячейке F2 введите только знак «=». После чего сделайте щелчок по ячейке D2, дальше нажмите «+», потом еще раз щелчок по D2, дальше введите «*», и щелчок по ячейке E2. После нажатия клавиши «Enter» получаем аналогичный результат.

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

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

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

    находится перед клавишей с единичкой «1») для переключения в режим просмотра формул. Повторное нажатие данной комбинации вернет в режим отображения результатов вычисления формул.

    Все формулы пересчитываются динамически. Как только изменяется содержание ячейки с входящими данными для расчетов, формулы автоматически пересчитывают их и сразу выдают новый результат вычислений. Например, если сейчас изменить ежемесячный оклад в ячейке D2 и нажать «Enter», то по адресу E2 сразу появится новый результат.

    Копирование формул в колонку

    Задание 1. Перейдите в ячейку F3 и нажмите комбинацию клавиш CTRL+D. Таким образом, автоматически скопируется формула, которая находится в ячейке выше (F2). Так Excel позволяет скопировать формулу на весь столбец. Также сделайте и в ячейке F4.

    Задание 2. Удалите формулы в ячейках F3:F4 (выделите диапазон и нажмите клавишу «delete»). Далее выделите диапазон ячеек F2:F4. И нажмите комбинацию клавиш CTRL+D. Как видите, это еще более эффективный способ заполнить целую колонку ячеек формулой из F2.

    Задание 3. Удалите формулы в диапазоне F3:F4. Сделайте активной ячейку F2, переместив на нее курсор. Далее наведите курсор мышки на точку в нижнем правом углу прямоугольного курсора. Курсор мышки изменит свой внешний вид на знак плюс «+». Тогда удерживая левую клавишу мыши, проведите курсор вниз еще на 2 ячейки, так чтобы выделить весь диапазон F2:F4.

    Как только вы отпустите левую клавишу, формула автоматически скопируется в каждую ячейку.

    Каждую формулу в любой ячейке можно прочитать в строке формул.

    Формулы можно скопировать еще тремя способами:

    • с помощью инструментов на полосе;
    • с помощью комбинации горячих клавиш;
    • с помощью управления курсором мышки и нажатой клавишей «CTRL».

    Эти способы более удобны для определенных ситуаций, которые мы рассмотрим на следующих уроках.

    Последнее десятилетие компьютер в бухгалтерии стал просто незаменимым инструментом. При этом его применение разнопланово. В первую очередь это, конечно, использование бухгалтерской программы. На сегодняшний день разработано довольно много программных средств, как специализированных («1С», «Инфо-Бухгалтер», «БЭСТ» и т. д.), так и универсальных, подобно Microsoft Office. На работе, да и в быту часто приходится делать массу различных расчётов, вести многострочные таблицы с числовой и текстовой информацией, проделывая с данными всяческие вычисления, выводя на печать варианты. Для решения ряда экономических и финансовых задач целесообразно использовать многочисленные возможности электронных таблиц. Рассмотрим в этой связи вычислительные функции MS Excel.

    Источник: Журнал «Бухгалтер и Компьютер» №4 2004г.

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

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

    Все операторы делятся на несколько групп (см. таблицу).


    АРИФМЕТИЧЕСКИЕ ОПЕРАТОРЫ

    Вычитание Унарный минус


    ОПЕРАТОРЫ СРАВНЕНИЯ


    ТЕКСТОВЫЙ ОПЕРАТОР

    Объединение последовательностей символов в одну последовательность символов

    = «Значение ячейки В2 равняется: «&В2


    АДРЕСНЫЕ ОПЕРАТОРЫ

    Ссылка на все ячейки между границами диапазона включительно

    Ссылка на объединение ячеек диапазонов

    Ссылка на общие ячейки диапазонов

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

    При вводе формул можно применять обычные числовые и текстовые значения. Напомним, что числовые значения могут содержать только цифры от 0 до 9 и специальные символы: (плюс, минус, косая черта, круглые скобки, точка, запятая, знаки процента и доллара). Текстовые значения могут содержать любые символы. Необходимо отметить, что используемые в формулах текстовые выражения должны заключаться в двойные кавычки, например “константа1”. Кроме того, в формулах можно использовать ссылки на ячейки (в том числе в виде имён) и многочисленные функции, которые соединяются между собой операторами.

    Ссылки представляют собой включаемые в формулу адреса ячеек или диапазоны ячеек. Ссылки на ячейки задаются обычным образом, т. е. в виде A1, B1, C1. Например, для того, чтобы получить в ячейке A3 сумму ячеек A1 и A2, в неё достаточно ввести формулу =A1+A2 (рис. 1).

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

    • выделить ячейку, в которую нужно ввести формулу;

    • начать ввод формулы, нажав клавишу “=” (равно);

    • щёлкнуть мышью на ячейке A1;

    • щёлкнуть мышью на ячейке B2;

    • закончить ввод формулы, нажав клавишу Enter.

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

    Диапазон ячеек представляет собой некоторую прямоугольную область рабочего листа и однозначно определяется адресами ячеек, расположенными в противоположных углах диапазона. Разделённые символом “:” (двоеточие), эти две координаты составляют адрес диапазона. Например, чтобы получить сумму значений ячеек диапазона C3:D7, используйте формулу =СУММ(C3:D7).

    В частном случае, когда диапазон состоит целиком из нескольких столбцов, например от В до D, его адрес записывается в виде В:D. Аналогично если диапазон целиком состоит из строк с 6-й по 15-ю, то он имеет адрес 6:15. Кроме того, при записи формул можно использовать объединение нескольких диапазонов или ячеек, разделяя их символом “;” (точка с запятой), например C3:D7; E5;F3:G7.

    Редактирование уже введённой формулы можно сделать несколькими способами:

    • двойным щелчком левой кнопки мыши на ячейке, чтобы корректировать формулу непосредственно в этой ячейке;

    • выбрать ячейку и нажать клавишу F2 (рис. 2);

    • выбрать ячейку, переместив курсор в строку формул, щёлкнуть левой кнопки мыши.

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

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

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

    Напомним, как правильно осуществить режим копирования. Здесь могут быть различные варианты (и проблемы тоже).

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

    Например, вам нужно складывать построчно значения столбцов А и В (рис. 8) и поместить результат в столбец С. Если вы копируете формулу =А2+В2 из ячейки С2 в ячейку С3* (и далее вниз по С), то Excel сам преобразует адреса формулы соответственно как =А3+В3 (и т. д.). Но если вам нужно поместить формулу, скажем, из С2 в ячейку D4, то формула уже будет выглядеть как =В4+С4 (вместо нужной =А4+В4), и соответственно результат вычислений будет неправильным! Иными словами, обратите особое внимание на процесс копирования и при необходимости вручную корректируйте формулы. Кстати, само копирование из С2 в С3 делается следующим образом:

    1) выбираем ячейку С2, из которой нужно скопировать формулу;

    2) нажимаем кнопку “Копировать” на панели инструментов, или клавиши Ctrl+C, или выбираем в меню “Правка ® Копировать”;

    3) выбираем ячейку С3, в которую будем копировать формулу;

    4) нажимаем кнопку “Вставить” на панели инструментов, или клавиши Ctrl+V, или через меню “Правка ® Вставить” с нажатием Enter.

    Рассмотрим режим автозаполнения. Если необходимо перенести (скопировать) формулу в несколько ячеек (например, в С3:С5) вниз по столбцу, то это удобнее и проще сделать так: повторить предыдущую последовательность действий до пункта 3 выбора ячейки С3, далее курсор мыши подвести к начальной ячейке диапазона (С3), нажать левую кнопку мыши и, не отпуская её, протащить ниже до требуемой последней ячейки диапазона. В нашем случае это ячейка С5. Затем отпускаем левую кнопку мыши, переводим курсор на кнопку “Вставить” панели инструментов и нажимаем её, а потом Enter. Excel сам преобразует адреса формул в выделенном нами диапазоне по соответствующим адресам строк.

    Иногда возникает необходимость скопировать только числовое значение ячейки (диапазона ячеек). Для этого нужно проделать следующее:

    1) выбрать ячейку (диапазон), из которой нужно скопировать данные;

    2) нажать кнопку “Копировать” на панели инструментов или выбрать в меню “Правка ® Копировать”;

    3) выбрать ячейку (левую верхнюю нового диапазона), в которую будут копироваться данные;

    4) выбрать в меню “Правка ® Специальная вставка” и нажать Enter.

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

    :: Функции в Excel

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

    В таблице часто требуется вычислить итоговую сумму по столбцу или строке. Для этого Excel предлагает функцию автоматической суммы, выполняемой нажатием кнопки (“Автосумма”) на панели инструментов.

    Если мы введём ряд чисел, установим курсор под ними и выполним двойной щелчок мышью по значку автосуммирования, то произойдёт сложение чисел (рис. 3).

    В последней версии программы справа от значка автосуммирования имеется кнопка списка, позволяющая произвести вместо суммирования ряд часто используемых операций (рис. 4).

    :: Автоматические вычисления

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

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

    Именно поэтому начиная с версии Excel 7.0 в электронную таблицу была встроена функция автовычисления. Теперь в электронных таблицах Excel имеется возможность быстрого выполнения некоторых математических операций в автоматическом режиме.

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

    Для примера рассчитаем с помощью этой функции сумму значений для диапазона В3:В9. Выделите числа в диапазоне ячеек В3:В9. Обратите внимание, что в строке состояния, расположенной внизу рабочего окна, появилась надпись Сумма=X, где X — число, равное сумме выделенных чисел диапазона (рис. 5).

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

    :: Мастер функций

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

    Вызвать окно диалога “Мастера” можно с помощью команды “Вставка ® Функция”, комбинацией клавиш Shift+F3 или кнопкой на стандартной панели инструментов.

    Первый диалог “Мастера функций” организован по тематическому принципу. Выбрав категорию, в нижнем окне мы увидим список имён функций, содержащихся в данной группе. Например, функцию СУММ () вы сможете отыскать в группе “Математические”, а в группе “Дата и время” находятся функции ЧИСЛО(), МЕСЯЦ(), ГОД(), СЕГОДНЯ().

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

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

    После заполнения соответствующих полей функции будет произведён расчёт амортизации имущества.

    Нередко нужно произвести сложение чисел, удовлетворяющих какому-либо условию. В этом случае следует использовать функцию СУММЕСЛИ. Рассмотрим конкретный пример. Допустим необходимо подсчитать сумму комиссионных, если стоимость имущества превышает 75 000 руб. Для этого используем данные таблицы зависимости комиссионных от стоимости имущества (рис. 8).

    Наши действия в этом случае таковы. Устанавливаем курсор в ячейку В6, кнопкой запускаем “Мастера функций”, в категории “Математические” выбираем функцию СУММЕСЛИ, задаём параметры, как на рис. 9.

    Обратите внимание, что в качестве диапазона для проверки условия мы выбираем интервал ячеек А2:А6 (стоимость имущества), а в качестве диапазона суммирования — В2:В6 (комиссионные), при этом условие имеет вид (>75000). Результат нашего расчёта составит 27 000 руб.

    :: Дадим имя ячейке

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

    Присваивая имена, необходимо помнить, что они могут состоять из букв (в том числе русского алфавита), цифр, точек и символов подчёркивания. Первый знак в имени должен быть буквой или знаком подчёркивания. Имена не могут иметь такой же вид, как и ссылки на ячейки, например Z$100 или R1C1. В имени может быть больше одного слова, но пробелы недопустимы. В качестве разделителей слов могут быть использованы знаки подчёркивания и точки, например Налог_на_продажи или Первый.Квартал. Имя может содержать до 255 знаков. При этом прописные и строчные буквы воспринимаются одинаково.

    Чтобы вставить имя в формулу, можно воспользоваться командой “Вставка ® Имя ® Вставить”, выбрав нужное имя в списке имён.

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

    Имена в Excel можно определять не только для отдельных ячеек, но и для диапазонов (в том числе несмежных). Для присвоения имени достаточно выделить диапазон, а затем ввести название в поле имени. Кроме того, для задания имён диапазонов, содержащих заголовки, удобно использовать специальную команду “Создать” в меню “Вставка ® Имя”.

    Чтобы удалить имя, выберите его в списке и нажмите кнопку “Удалить”.

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

    то для подсчёта общей суммы для столбца “Комиссионные” используется формула =СУММ(Комиссионные) (рис. 11).

    :: Дополнительные возможности Excel — шаблоны

    В состав MS Excel входит набор шаблонов — таблицы Excel, которые предназначены для анализа хозяйственной деятельности предприятия, составления счёта, наряда и даже для учёта личного бюджета. Они могут быть использованы для автоматизации решения часто встречающихся задач. Так, можно создавать документы на основе шаблонов “Авансовый отчёт”, “Счёт”, “Заказ”, которые содержат бланки используемых в хозяйственной деятельности документов. Эти бланки по своему внешнему виду и при печати не отличаются от стандартных, и единственное, что нужно сделать для получения документа, — заполнить его поля.

    Для создания документа на основе шаблона выполните команду “Создать” из меню “Файл”, затем выберите необходимый шаблон на вкладке “Решения” (рис. 12).

    Шаблоны копируются на диск при обычной установке Excel. Если шаблоны не отображаются в окне диалога “Создание документа”, запустите программу установки Excel и установите шаблоны. Чтобы получить подробные сведения об установке шаблонов, посмотрите раздел “Установка компонентов Microsoft Office” в справке Excel.

    Например, для создания ряда финансовых документов выберите шаблон “Финансовые шаблоны” (рис. 13).

    Эта группа шаблонов содержит формы следующих документов:

    • командировочное удостоверение;
    • авансовый отчёт;
    • платёжное поручение;
    • счёт-фактура;
    • накладная;
    • доверенность;
    • приходный и расходный ордера;
    • платёжки за телефон и электроэнергию.

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

    Excel позволяет пользователю самому создавать шаблоны документов, а также редактировать уже имеющиеся.

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

    Для таких случаев в Excel, как и во многих других программах, которые работают с электронными документами, предусмотрена возможность создания и редактирования шаблонов для часто используемых документов. Шаблон Excel — это специальная рабочая книга, которую можно применять как образец при создании других рабочих книг того же типа. В отличие от обычной книги Excel, имеющей расширение *.xls, файл шаблона имеет расширение *.xlt.

    При создании документа на основе шаблона программа Excel автоматически создаёт его рабочую копию с расширением *.xls, добавляя в конец имени документа порядковый номер. Шаблон-оригинал при этом остаётся нетронутым и может быть впоследствии использован повторно.

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

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

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

    Как сделать расчеты в Excel

    Произвести расчеты в Excel легко. Вот как:

    • Введите символ равенства (=) в ячейку. Это говорит Excel, что вы вводите формулу, а не только цифры.
    • Введите уравнение, которое вы хотите посчитать. Например, чтобы посчитать сумму 5 и 7, введите =5+7
    • Нажмите клавишу Enter, чтобы завершить расчет. Готово!

    Вместо того, чтобы вводить цифры непосредственно в формулу расчета, вы можете поместить их в отдельные ячейки, а затем сослаться на эти ячейки в своей формуле, например, =A1+A2+A3

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

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

    Быстрый расчет формул из Excel на C#

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

    Изучая требования нового проекта в компании, мы обнаружили один очень интересный пункт: «Разработанное решение должно уметь рассчитывать стоимость продукта (а именно — балкона) при изменении конфигурации и мгновенно отображать новую стоимость на интерфейсе. Необходимо предоставить возможность загрузки Excel-файла со всеми функциями расчета и прайс-листами компонентов». Клиенту требовалось разработать портал для проектирования конфигурации балконов в зависимости от размеров, форм, видов остекления и используемых материалов, типов креплений, а также многих других сопутствующих параметров, которые применяются для расчета точной стоимости производства и показателей надежности.

    Формализуем входные требования, чтобы было проще понимать, в каком контексте нужно было решить задачу:

    • Быстрый расчет цены на интерфейсе при изменениях параметров балкона;
    • Быстрый расчет проектных данных, включающий множество разных конфигураций балконов и индивидуальных предложений, поставляемых отдельным файлом расчетов;
    • В перспективе — развитие функциональности с применением различных ресурсозатратных операций (задачи оптимизации параметров и т.д.)
    • Все это — на удаленном сервере с выводом через API, т.к. все формулы являются интеллектуальной собственностью заказчика и не должны быть видны третьим лицам;
    • Поток входных данных с пиковыми нагрузками: пользователь может часто и быстро менять параметры, чтобы подобрать конфигурацию продукта по своим требованиям.

    Звучит очень необычно и сверхзаманчиво, приступим!

    Любое исследование для решения сложных задач начинается с серфинга по StackOverflow, GitHub и множеству форумов в поисках готовых решений.

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

    Библиотека Ссылка Документация Лицензия
    EPPlus 4 github.com/JanKallman/EPPlus github.com/JanKallman/EPPlus/wiki GNU Library General Public License
    EPPlus 5 github.com/EPPlusSoftware/EPPlus github.com/EPPlusSoftware/EPPlus/wiki Polyform Noncommercial License 1.0.0 ( www.epplussoftware.com/LicenseOverview)
    NPOI github.com/tonyqus/npoi github.com/tonyqus/npoi/wiki Apache License 2.0
    Spire www.e-iceblue.com/Introduce/excel-for-net-introduce.html www.e-iceblue.com/Tutorials/Spire.XLS/Spire.XLS-Program-Guide/Spire.XLS-Program-Guide-Content.html www.e-iceblue.com/Tutorials/Licensing/License-Agreement.html
    Excel Interop(Microsoft.Office.Interop.Excel) n/a docs.microsoft.com/ru-ru/dotnet/api/microsoft.office.interop.excel._workbook?view=excel-pia Требует предустановленного Excel со всеми вытекающими последствиями лицензирования

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

    Т.к. речь идет еще и о деньгах (стоимость продукта), то важно посмотреть и на точность полученных результатов. За эталон в данном случае будем брать результирующие значения, которые были получены с помощью Excel Interop, т.к. данные, полученные этим способом, вычислены через ядро Excel и равны значениям, которые заказчики видят при разработке формул и ручном расчете стоимости.

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

    Переведенная исходная формула для тестирования:

    Формируем поток случайных входных данных для N итераций (в данном случае мы используем 10000 векторов).

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

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

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

    Для каждой отдельной библиотеки был создан собственный класс, реализующий интерфейс ITestExecutor и включающий в себя 3 метода — SetUp , Execute и TearDown .

    Методы SetUp и TearDown используются единожды в процессе тестирования библиотеки и не учитываются при замере времени расчетов на всем наборе входных данных.

    В итоге алгоритм тестирования свелся к следующему:

    • Подготовка данных (формируем поток входных векторов заданной точности);
    • Выделение памяти под результирующие данные (массив результатов на каждую библиотеку, массив с количеством ошибок);
    • Инициализация библиотек;
    • Получение результатов расчетов для каждой из библиотек с сохранением полученного результата в заранее заготовленный массив и записью времени исполнения;
    • Завершение работы с кодом библиотек;
    • Анализ полученных данных:

    Графическое отображение данного алгоритма представлено ниже.

    Схема алгоритма проведения тестирования производительности и точности библиотек поддержки Excel формата

    Показатель Native EPPlus 4
    & EPPlus 5
    NPOI Spire Excel Interop
    Время инициализации (мс) 0 257 266 632 1653
    Ср. время на 1 проход (мс) 0,0002 0,4086 0,6847 6,9782 38,8423
    Ср.кв. отклонение 0,000394 0,000395 0,000237 0,000631 n/a
    Точность 99,99% 99,92% 99,97% 99,84% n/a
    Ошибки 0,0% 1,94% 1,94% 1,52% 1,94%

    Первая итерация тестов показала неплохой результат, в котором сразу же стали видны лидеры среди библиотек. Как видно из приведенных данных, абсолютным лидером в данной ситуации является библиотека EPPlus.

    Результаты не впечатляющие по сравнению с нативным кодом, но жить можно.

    На этом можно было бы и остановиться, но после общения с заказчиками закрались первые сомнения: результаты получились слишком хорошие.

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

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

    Новая формула, которая будет использоваться для последующих тестов:

    Как можно заметить, результирующая формула получилась значительно сложнее, что естественным образом сказалось и на результатах — не в лучшую сторону. Таблица с результатами представлена ниже:

    Показатель Native EPPlus 4EPPlus 5 NPOI Spire Excel Interop
    Время инициализации (мс) 0 241 368 722 1640
    Ср. время на 1 проход (мс) 0,0004 0,9174(+124%) 1,8996(+177%) 7,7647(+11%) 50,7194(+30%)
    Ср.кв. отклонение 0,035884 0,000000 0,000000 0,000000 n/a
    Точность 98,79% 100,00% 100,00% 100,00% n/a
    Ошибки 0,0% 0,3% 0,3% 0,28% 0,3%

    Примечание: Т.к. Excel Interop имеет слишком большие значения, их пришлось исключить из диаграммы.

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

    Если вы вдруг никогда не сталкивались с похожей задачей, то проведем краткий экскурс.

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

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

    Для этого необходимо:

    • Прочитать формулу из файла;
    • Собрать все зависимости;
    • Транслировать полученные формулы в аналогичный код на C#;
    • Скомпилировать динамическую сборку с новой функцией;
    • Подключить ее к текущему процессу и пробросить ссылку на функцию для дальнейшего использования;
    • Использовать полученную функцию для расчета результатов.

    Для чтения формул подходят все представленные библиотеки, однако самый удобный интерфейс для такого функционала оказался у библиотеки EPPlus. Немного порывшись в исходниках этой библиотеки, я обнаружил публичные классы для формирования списка токенов и дальнейшего его преобразования в дерево выражений. Бинго, подумал я! Готовое дерево выражений из коробки — это идеальный вариант, достаточно лишь пройтись по нему и сформировать наш код на C#.

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

    Идем на уровень ниже и пробуем работу со списком токенов. Здесь все достаточно просто: у нас есть токены, которые имеют тип и значение. Т.к. нам дана функция и нам необходимо сформировать функцию, то мы можем вместо построения дерева преобразовать токены в эквивалент на C#. Главное в таком подходе — это организовать совместимые функции. Большинство математических функций уже были совместимы — такие как вычисление косинуса, синуса, получение корня и возведение в степень. Но функции агрегации — такие как максимальное значение, минимальное, сумма — требовалось доработать. Основное отличие в том, что в Excel данные функции работают с диапазоном значений. Для простоты прототипа мы сделаем функции, которые на вход принимают список параметров, предварительно разворачивая диапазон значений в линейный список. Таким образом мы получим корректное и совместимое преобразование из Excel-синтаксиса в C#-синтаксис.

    Ниже представлен основной код преобразования списка токенов Excel-формулы в валидный C#-код.

    Следующим нюансом при конвертации было использование констант Excel — они представляют собой функции, поэтому в C# их также придется обернуть в функцию.

    Осталось решить только один вопрос: преобразование ссылок на ячейку в параметр. В том случае, когда токен содержит информацию о ячейке, мы в первую очередь определяем, что именно хранится в этой ячейке. Если это формула — разворачиваем ее рекурсивно. Если константа — заменяем на ссылку C#-аналога, вида p[row, column] , где p может быть как двумерным массивом, так и классом с индексным доступом для корректного маппинга данных. С диапазоном ячеек мы делаем то же самое, только предварительно разворачиваем диапазон в индивидуальные ячейки и обрабатываем их отдельно. Таким образом, мы покрываем основной функционал при трансляции Excel-функции.

    Ниже представлен код преобразования ссылки на ячейку Excel-таблицы в валидный C#-код:

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

    Пишем класс-обертку для тестирования и запускаем тесты с замером времени.

    В итоге мы имеем прототип данного решения и помечаем ее как EPPlusCompiled, Mark-I. После запуска тестов получаем долгожданный результат. Ускорение почти в 300 раз. Уже неплохо, но полученный код все еще медленнее нативного в 16 раз. Можно ли лучше?

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

    Библиотека Ср. время (мс) Коэф. замедления
    Native 0,00004 1
    EPPlusCompiled, Mark-II 0,003 8
    EPPlusCompiled, Mark-I 0,0061 16
    EPPlus 1,2089 3023

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

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

    Тезисы

    Показатель Native EPPlus Compiled, Mark-II EPPlus 4EPPlus 5 NPOI Spire Excel Interop
    Время инициализации (мс) 0 239 241 368 722 1640
    Ср. время на 1 проход (мс) 0,0004 0,003 0,9174 1,8996 7,7647 50,7194
    Ср.кв. отклонение 0,035884 0,0 0,0 0,0 0,0 n/a
    Точность 98,79% 100,0% 100,0% 100,0% 100,0% n/a
    Ошибки 0,0% 0,0% 0,3% 0,3% 0,28% 0,3%

    Быстрый расчет формул из Excel на C#

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

    А какие самые необычные и интересные интеграции с Excel-документами приходилось реализовывать вам?

    Как в эксель проводить расчеты