Разбираем и вычисляем формулы MS Excel

Принципы работы, хитрости и подводные камни механизма вычисления формул в MS Excel от компании Developer Soft. А чтобы не сводиться к сухим пересказам документации, разбавленным дополнениями «из жизни» — краткий рассказ, как они реализовывали подобный механизм.

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

Внутреннее представление выражения

Полный набор ParsedThing состоит из следующих элементов:

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

Вычисления

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

Результат вычисления функции может быть любого типа. Большинство функций возвращают Value, некоторые (например, INDIRECT) -Reference, остальные — Array(MMULT).

Как видно из схемы, значение типа Reference невозможно получить из Value или Array. Это вполне логично, из числа, строки и т.п. получить ссылку не получится.

При преобразовании Reference к Array все значения из ячеек, входящих в диапазон, переписываются в массив. В случае когда диапазон комплексный (состоящий из двух или более других диапазонов) — результат преобразования равен ошибке #VALUE!

Интересным образом происходит преобразование Reference к Value. Между собой это правило мы прозвали «Кроссинг». Проще всего объяснить его суть на примере:

Точно таким же поведение будет и для диапазона B7:C7, состоящего из одной строки. Для ячеек B6 и C6 преобразование вернет значения 3 и 4 соответственно, а для A6 и — D6 #VALUE! Аналогично, если бы строк в диапазоне было больше, преобразование вернуло бы #VALUE! для всех ячеек с A6 по D6

Значения всех формул, находящихся внутри ячеек, всегда приводятся к типу Value.

• «=123» В этой формуле задана константа, она уже типа Value. Ничего преобразовывать не надо.
• «=» Тут задан массив. Преобразование к Value по правилу дает нам первый элемент массива — 1. Он и будет результатом вычисления выражения.
• Формула «=A1:B1» находящаяся в ячейке B2. Операнд-ссылка на диапазон по умолчанию имеет тип Reference. При вычислении он будет приведен к Value по правилу «кроссинг». Результатом в данном случае будет значение из ячейки B1.

Операторы ссылки не могут работать ни с каким другим типом, кроме Reference. К примеру, формула «=A1:«test»» будет неправильной, ввод такой формулы приведет к ошибке — Excel просто не даст такую формулу записать в ячейку.

Парсер

Конечно, в рамках этой статьи я не стану останавливаться на описании возможностей и пересказе документации Coco/R. Благо, что документация написана на мой взгляд весьма понятно. Кроме этого рекомендую почитать статью на хабре.

Собираем Coco/R из исходников

В некоторых местах Coco/R генерирует не CLS-compliant код.

Проблема в публичных константах, имя которых начинается со знака подчеркивания. Правильный выход из ситуации только один — поправить Coco/R, благо полный исходный код его доступен на сайте разработчиков.

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

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

Плагин тоже пришлось научить генерировать CLS compliant код. Скачиваем исходный код плагина, и повторяем те же операции, что и с самим Coco/R.

Модернизируем сканер и парсер

Напомню, что для разбора выражения Coco/R создает пару классов – Parser и Scanner. Оба они создаются заново для каждого нового выражения. Поскольку в нашем случае выражений много, то пересоздание сканера может занять дополнительное время на большом количестве вызовов. В целом, нам достаточно одного комплекта парсер-сканер. Первая модернизация коснулась именно этого.

Вторая модернизация коснулась вспомогательного класса Buffer, который создается сканером для чтения входящего потока символов. “Из коробки” Coco/R содержит пару реализаций Buffer и UTF8Buffer. Оба они работают с потоком. Нам же поток не нужен: достаточно работы со строкой. Для этого создадим третью реализацию StringBuffer, попутно выделив интерфейс IBuffer:

Разработка грамматики

Парсер строится на основе рекурсивного спуска, грамматика должна удовлетворять LL(k). Сканер основывается на детерминированном конечном автомате.

Итак, приступим. Первым в файле грамматики идет название будущего компилятора:

Далее должна следовать спецификация сканера. Сканер будет case-insensitive, указываем это при помощи ключевого слова IGNORECASE. Теперь надо определиться с символами. Нам надо отделить цифры, буквы, управляющие символы. Получилось следующее:

Coco/R позволяет не только складывать множества символов, но и вычитать. Так, в описании letter применено ключевое слово ANY, которое подставляет все множество символов, из которого вычитаются определенные выше другие множества.

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

• Может содержать только буквы, цифры, и символы: _. \,?;
• Не может быть равен TRUE или FALSE;
• Первый символ может быть только буквой, знаком подчеркивания, или бекслешем;
• Если первый символ строки – бекслеш, то второго символа может не быть, либо это должен быть один из: _. \,?;
• Не должен быть схож с названием диапазона (например, A10);
• Не должен начинаться на строку, которая может быть воспринята как ссылка в формате R1C1. Природа этого условия сложнообъяснима, приведу только несколько примеров идентификаторов, которые ему не удовлетворяют: “R1_test”, “R1test”,“RC1test”,“R”,“C”. При этом «RCtest» – вполне подходит.

Наконец, укажем сканеру, чтобы он пропускал переводы строк и табуляции.
IGNORE eol + ‘\n’ + ‘\t’. Сами выражения могут быть написаны в несколько строк, но на грамматику это не влияет.

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

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

Для визуализации РБНФ использую небольшую программку EBNF Visualizer. Вот так будет выглядеть первый нетерминал в нашей грамматике – логическое выражение:

Далее грамматика для Coco/R. В семантических вставках, оформленных между “(.” и “.)” я добавлю нужный ParsedThing к выражению.

Для разрешения конфликта используется метод IsSheetDefinition(), определенный в классе Parser. Подобные методы удобно писать в отдельном файле, пометив класс как partial.

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

В OperandClause мы будем попадать из OperandCommonClause, если нет ссылки на лист, внешнюю книгу или источник DDE. Из этого нетерминала мы можем попасть в ArrayClause, StringConstant(оба не могут иметь перед собой ссылку на лист), CellReferenceClause, либо встретим скобку и перейдем к началу всего дерева разбора – к LogicalClause.

CellReferenceClause наверно самый большой нетерминал, в нем собраны почти все виды операндов:

Для большинства нетерминалов приходится создавать методы разрешения конфликтов. Дальше описываем грамматики для всех оставшихся нетерминалов.

Парсинг неполных выражений и «предсказания»

Рассмотрим задачу подсветки диапазонов, участвующих в формуле.

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

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

Оптимизация производительности вычислений

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

Атрибут AttrSemi. Этот атрибут добавляется первым элементом в те выражения, которые содержат volatile-функции.

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

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

Взглянув на эти типы можно догадаться, что Excel не умеет сохранять пробелы в конце строки и перед знаком ‘=’. Кроме этого пробелы внутри структурированных ссылок и массивов так же сохранены не будут.

Тестирование

По мере развития контрола добавлялись как поддерживаемые форматы, так и фичи. Так сейчас постоянно тестируются 20к xls файлов и 15к csv файлов. И тестируются не только на чтение-запись, но и проверяются сторонними утилитами, которые также нам очень помогают.

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

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

Как перейти к ячейке в Excel: быстро и легко — Vgev

• Выделите ячейку, в которой вы хотите запустить формулу,
• Перейдите на вкладку «Формулы» в верхней части навигации.
• Выберите «Вставить функцию«. Если вы используете Microsoft Excel 2007, этот параметр будет находиться слева от панели навигации «Формулы».

Чтобы произвести поиск значения в таблице Excel, необходимо на вкладке «Главная» открыть выпадающий список инструмента «Найти и заменить» и щёлкнуть пункт «Найти». Тот же эффект можно получить, используя сочетание клавиш Ctrl + F.

Поиск в таблице Excel — простые способы

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

Простой поиск

Чтобы произвести поиск значения в таблице Excel, необходимо на вкладке «Главная» открыть выпадающий список инструмента «Найти и заменить» и щёлкнуть пункт «Найти». Тот же эффект можно получить, используя сочетание клавиш Ctrl + F.

В простейшем случае в появившемся окне «Найти и заменить» надо ввести искомое значение и щёлкнуть «Найти всё».

Как видно, в нижней части диалогового окна появились результаты поиска. Найденные значения подчёркнуты красным в таблице. Если вместо «Найти все» щёлкнуть «Найти далее», то сначала будет произведён поиск первой ячейки с этим значением, а при повторном щелчке – второй.

Аналогично производится поиск текста. В этом случае в строке поиска набирается искомый текст.

Если данные или текст ищется не во всей экселевской таблице, то область поиска предварительно должна быть выделена.

Расширенный поиск

Предположим, что требуется найти все значения в диапазоне от 3000 до 3999. В этом случае в строке поиска следует набрать 3. Подстановочный знак «?» заменяет собой любой другой.

Анализируя результаты произведённого поиска, можно отметить, что, наряду с правильными 9 результатами, программа также выдала неожиданные, подчёркнутые красным. Они связаны с наличием в ячейке или формуле цифры 3.

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

Щёлкнув «Параметры», пользователь получает возможность осуществлять расширенный поиск. Прежде всего, обратим внимание на пункт «Область поиска», в котором по умолчанию выставлено значение «Формулы».

Это означает, что поиск производился, в том числе и в тех ячейках, где находится не значение, а формула. Наличие в них цифры 3 дало три неправильных результата. Если в качестве области поиска выбрать «Значения», то будет производиться только поиск данных и неправильные результаты, связанные с ячейками формул, исчезнут.

Для того чтобы избавиться от единственного оставшегося неправильного результата на первой строчке, в окне расширенного поиска нужно выбрать пункт «Ячейка целиком». После этого результат поиска становимся точным на 100%.

Такой результат можно было бы обеспечить, сразу выбрав пункт «Ячейка целиком» (даже оставив в «Области поиска» значение «Формулы»).

Если вместо установленного по умолчанию «На листе» выбрать значение «В книге», то нет необходимости находиться на листе искомых ячеек. На скриншоте видно, что пользователь инициировал поиск, находясь на пустом листе 2.

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

При поиске в документах Microsoft Excel, можно использовать и другой подстановочный знак – «*». Если рассмотренный «?» означал любой символ, то «*» заменяет собой не один, а любое количество символов. Ниже представлен скриншот поиска по слову Louisiana.

Иногда при поиске необходимо учитывать регистр символов. Если слово louisiana будет написано с маленькой буквы, то результаты поиска не изменятся. Но если в окне расширенного поиска выбрать «Учитывать регистр», то поиск окажется безуспешным. Программа станет считать слова Louisiana и louisiana разными, и, естественно, не найдёт первое из них.

Разновидности поиска

Поиск совпадений

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

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

Фильтрация

Другая разновидность поиска – фильтрация. Предположим, что пользователь хочет в столбце B найти числовые значения в диапазоне от 3000 до 4000.

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

Различные варианты поиска были рассмотрены на примере Excel 2010. Как сделать поиск в эксель других версий? Разница в переходе к фильтрации есть в версии 2003. В меню «Данные» следует последовательно выбрать команды «Фильтр», «Автофильтр», «Условие» и «Пользовательский автофильтр».

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

Как написать формулу в Excel — пошаговая инструкция

• «=123» В этой формуле задана константа, она уже типа Value. Ничего преобразовывать не надо.
• «=» Тут задан массив. Преобразование к Value по правилу дает нам первый элемент массива — 1. Он и будет результатом вычисления выражения.
• Формула «=A1:B1» находящаяся в ячейке B2. Операнд-ссылка на диапазон по умолчанию имеет тип Reference. При вычислении он будет приведен к Value по правилу «кроссинг». Результатом в данном случае будет значение из ячейки B1.

Различные варианты поиска были рассмотрены на примере Excel 2010. Как сделать поиск в эксель других версий? Разница в переходе к фильтрации есть в версии 2003. В меню «Данные» следует последовательно выбрать команды «Фильтр», «Автофильтр», «Условие» и «Пользовательский автофильтр».

Гиперссылка используется для быстрого доступа к веб-страницам и файлам, ее также можно использовать для поиска мест в документе, таких как документ Excel, поэтому это еще один способ перехода к ячейке в Excel. Значок гиперссылки в Excel находится на вкладке «Вставка» рабочего листа. Его также можно найти, введя Ctrl + K на клавиатуре.

Поиск в Эксель: способы нахождения нужного слова

Это самая простая и полезная фишка – позволяет найти максимум из 255 чисел максимум. Поэтому можно сравнивать расходы не только за последний месяц, но и год. Для этого прописывают =МАКС(дают диапазон).

Для того чтобы избавиться от единственного оставшегося неправильного результата на первой строчке, в окне расширенного поиска нужно выбрать пункт «Ячейка целиком». После этого результат поиска становимся точным на 100%.

Разбираем и вычисляем формулы MS Excel | Excellentricks: искусство аналитики

Конечно, в рамках этой статьи я не стану останавливаться на описании возможностей и пересказе документации Coco/R. Благо, что документация написана на мой взгляд весьма понятно. Кроме этого рекомендую почитать статью на хабре.

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

Как искать в Excel слова, текст, ячейки и значения в таблицах

1. Перейти во вкладку «Главная».
2. Выбрать функцию «Найти и выделить».
3. Нажать на команду «Найти».
4. Перейти в «Параметры» и выбрать «Формат».
5. Здесь выделить функцию «Выравнивание», поставить отметку «Объединить ячейки».
6. Нажать на «Ок».
7. Нажать на кнопку «Найти все» и проанализировать список ячеек, которые объединены на соответствующем листе.

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

Когда вы видите ##### в таблице, это может выглядеть немного страшно. Хорошей новостью является то, что это просто означает, что столбец недостаточно широк для отображения введенного вами значения. Это легко исправить.

ТОП-24 популярных формул в Excel | Интернет-агентство «Третий Путь»

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

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