Эксель основные формулы которые надо знать
Перейти к содержимому

Эксель основные формулы которые надо знать

  • автор:

20+ функций Excel, которые ускорят работу в несколько раз

20+ функций Excel, которые ускорят работу в несколько раз

20+ функций Excel, которые ускорят работу в несколько раз

Ксения Самоткан Редакция «Текстерры»

Обновил Сергей Ломакин

Краткая справка

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

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

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

Продвинем ваш бизнес

В Google и «Яндексе», соцсетях, рассылках, на видеоплатформах, у блогеров

1. ВПР

ВПР расшифровывается как «вертикальный просмотр».

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

Запустим контекстную рекламу в Яндексе

Синтаксис

Функция состоит из 4 аргументов и представлена следующей формулой:

=ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])

  • «Искомое значение» указывают в первом столбце диапазона ячеек. Аргумент может являться значением или ссылкой на ячейку.
  • «Таблица». Группа ячеек, в которой выполняется поиск искомого и возвращаемого значения. Диапазон ячеек должен содержать искомое значение в первом столбце и возвращаемое значение в любом месте.
  • «Номер столбца». Номер столбца, содержащий возвращаемое значение.
  • «Интервальный просмотр» – необязательный аргумент. Это логическое выражение, определяющее, насколько точное совпадение должна обнаружить функция. В связи с этим условием выделяют 2 функции:

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

○ Ложь – ищет точное значение в первом столбце.

Примеры

Рассмотрим несколько примеров использования функции ВПР.

Предположим, нам нужно найти в таблице количество просмотров по запросу «купить планшет».

Как найти в Excel определенное значение

Чтобы найти в табличке нужное значение, понадобится функция ВПР

Функции нужно найти данные, соответствующие значению «планшет», которое указано в отдельной ячейке (С3) и выступает в роли искомого значения. Аргумент «таблица» здесь – диапазон поиска от A1:B6; номер столбца, содержащий возвращаемое значение – «2». В итоге получаем следующую формулу: =ВПР(С3;А1:B6;2). Результат – 31325 просмотров в месяц.

Результат – значение ячейки, содержащей искомое слово «планшет». «31325»

Используем формулу ВПР

В следующих двух примерах применен интервальный просмотр с двумя вариантами функций: ИСТИНА и ЛОЖЬ.

Обратите внимание на количество просмотров запроса «купить машину». Приблизительное значение – «886146»

Применен интервальный просмотр с функцией ИСТИНА, которая выдает приблизительное значение «886146» («купить машину») при искомом значении «900000»

Используем функцию ЛОЖЬ

Применение интервального просмотра с функцией ЛОЖЬ позволяет найти значение, в точности равное искомому

2. ЕСЛИ

Функция ЕСЛИ выполняет проверку заданных условий, выбирая один из двух возможных результатов: если сравнение истинно и если сравнение ложно.

Синтаксис

Формула функции состоит из трех аргументов и выглядит следующим образом:

=ЕСЛИ(логическое_выражение;«значение_если_истина»;«значение_если_ложь»),

  • «логическое выражение» – формула;
  • «значение если истина» – значение, при котором логическое выражение выполняется;
  • «значение если ложь» – значение, при котором логическое выражение не выполняется.

Примеры

Рассмотрим пример использования обычной функции ЕСЛИ.

Значение подставляется автоматически благодаря функции ЕСЛИ

Выполнение плана продаж здесь обусловлено наличием суммы выручки больше 30 000 руб.

Чтобы узнать, кто из продавцов выполнил план, а кто нет, нужно ввести следующую формулу:

=ЕСЛИ(B2>30000;«План выполнен»;«План не выполнен»)

  • Логическое выражение здесь – формула «B2>30000».
  • «Значение если истина» – «План выполнен».
  • «Значение если ложь» – «План не выполнен».

Вложенные функции ЕСЛИ

Помимо обычной функции ЕСЛИ, которая выдает всего 2 результата – «истина» и «ложь», существуют вложенные функции ЕСЛИ, выдающие от 3 до 64 результатов. В данном случае формула может вмещать в себя несколько функций.

Используем функцию ЕСЛИ

В этом примере одна функция вложена в другую, и всего внесено 3 результата

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

Существует еще один способ использования функции ЕСЛИ – для проверки, пуста ячейка или нет. Для этого ее можно использовать с функцией ЕПУСТО:

=ЕСЛИ(ЕПУСТО(номер ячейки);«Пустая»;«Не пустая».

Используем формулу =ЕСЛИ(ЕПУСТО(номер ячейки);«Пустая»;«Не пустая».

Пример совмещения 2 функций, позволяющего выявить верный результат

Вместо функции ЕПУСТО также можно использовать другую формулу:

«номер ячейки=«» (ничего).

Используем формулу «номер ячейки=«» (ничего)

Формула не требует добавления других функций

ЕСЛИ – одна из самых популярных функций в Excel. Она помогает определить истинность тех или иных значений, получить результаты по разным данным и выявить пустые ячейки, к тому же ее можно использовать в сочетании с другими функциями.

Функция ЕСЛИ является основой других формул: СУММЕСЛИ, СЧеТЕСЛИ, ЕСЛИОШИБКА, СРЕСЛИ. Мы рассмотрим три из них – СУММЕСЛИ, СЧеТЕСЛИ и ЕСЛИОШИБКА.

«Google Таблицы»: большой и простой гайд

«Google Таблицы»: большой и простой гайд

3. СУММЕСЛИ и СУММЕСЛИМН

Функция СУММЕСЛИ позволяет суммировать данные, соответствующие условию и находящиеся в указанном диапазоне.

Синтаксис

Функция состоит из трех аргументов и имеет формулу:

=СУММЕСЛИ(диапазон;условие;[диапазон_суммирования])

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

Пример

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

В нашем случае функция вычисляет сумму 519414

Используем синтаксис. В примере функция вычислила сумму (519414), которая является сложением ячеек (B2:B4), удовлетворяющих условию (>100000). Формула: =СУММЕСЛИ(B2:B6;«>100000»)

Если нужно суммировать ячейки в соответствии с несколькими условиями, можно воспользоваться функцией СУММЕСЛИМН.

Синтаксис

Формула функции имеет следующий вид:

=СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …)

«Диапазон условия 1» и «условие 1» – обязательные аргументы, остальные – необязательные.

4. СЧЕТЕСЛИ и СЧЕТЕСЛИМН

Функция СЧеТЕСЛИ считает количество непустых ячеек, соответствующих заданному условию внутри указанного диапазона.

Синтаксис

=СЧЕТЕСЛИ(диапазон;критерий)

  • «Диапазон» – группа ячеек, которые нужно подсчитать.
  • «Критерий» – условие, согласно которому выбираются ячейки для подсчета.

Пример

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

Получилось 3 ключа, соответствующих заданным условиям

Функция подсчитывает количество элементов, не указывая ссылок на них

В функции СЧЕТЕСЛИ можно использовать только один критерий. Если нужно сделать подсчет по нескольким условиям, примените функцию СЧЕТЕСЛИМН.

Синтаксис

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

=СЧЕТЕСЛИМН(диапазон_условия1;условие1;[диапазон_условия2;условие2];…)

«Диапазон условия 1» и «условие 1» – обязательные аргументы, остальные необязательны. Можно использовать до 127 пар диапазонов и условий.

5. ЕСЛИОШИБКА

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

Синтаксис

Функция имеет 2 аргумента и представлена формулой:

=ЕСЛИОШИБКА(значение;значение_если_ошибка),

Формула в примере выдает правильный результат

  • «значение» – формула, которая проверяется на наличие ошибки;
  • «значение_если_ошибка» – значение, появляющееся в ячейке в том случае, если вычисление в формуле выдало ошибку.

Что-то не сходится

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

Альт

Функция знает, что на ноль делить нельзя, поэтому вводит значение, указываемое при возможной ошибке

6. ЛЕВСИМВ

Функция ЛЕВСИМВ позволяет выделить необходимое количество знаков с левой стороны строки.

Синтаксис

Функция состоит из 2 аргументов и представлена формулой:

=ЛЕВСИМВ(текст;[число_знаков]),

  • «текст» – текстовая строка, содержащая знаки, которые необходимо извлечь;
  • «число знаков» – необязательный аргумент, указывающий на количество извлекаемых знаков.

Пример

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

Формула следующая: =ЛЕВСИМВ(А5;60)

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

Если вы хотите, чтобы тайтлы были лаконичными и состояли из 60 знаков, функция отсчитает первые 60 символов и покажет, как будет выглядетьтайтл. Для этого необходимо составить формулу: =ЛЕВСИМВ(А5;60), где А5 – адрес рассматриваемой ячейки, «60» – число извлекаемых символов.

7. ПСТР

Функция ПСТР позволяет извлечь необходимое количество символов внутри текста, начиная с указанной позиции.

Синтаксис

Формула функции состоит из 3 аргументов:

=ПСТР(текст;начальная_позиция;число_знаков)

  • «Текст» – строка, содержащая символы, которые нужно извлечь.
  • «Начальная позиция» – позиция знака, с которого начинается извлекаемый текст.
  • «Число знаков» – количество извлекаемых символов.

Пример

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

Начальная позиция – «9» (то есть 9-й символ в ячейке А3 – «К»), а число знаков – 100

Тайтл стал более читабельным

8. ПРОПИСН

Функция ПРОПИСН делает все буквы в тексте прописными.

Синтаксис

=ПРОПИСН(текст)

«Текст» здесь – текстовый элемент или ссылка на ячейку.

Пример

Строчные буквы преобразованы в прописные в мгновение ока

Если ячеек много, мы сэкономим кучу времени

9. СТРОЧН

Функция СТРОЧН делает все буквы в тексте строчными.

Синтаксис

=СТРОЧН(текст)

Аргумент «текст» – текстовый элемент или адрес ячейки.

Пример

Формула: =СТРОЧН(B1) позволила быстро превратить прописные буквы в строчные

Замены прошла успешно и «РОЖДЕНИЯ» превратилось в «рождения»

10. ПОИСКПОЗ

Функция ПОИСКПОЗ помогает найти указанный элемент в массиве ячеек и определяет его положение.

Синтаксис

=ПОИСКПОЗ(искомое_значение;просматриваемый_массив;тип_сопоставления)

«Искомое значение» и «просматриваемый массив» – обязательные аргументы, «тип сопоставления» – необязательный.

Рассмотрим подробнее аргумент «тип сопоставления». Он указывает, каким образом сопоставляется найденное значение с искомым. Существует 3 типа сопоставления:

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

0 – точное совпадение;

-1 – наименьшее значение, которое больше или равно искомому.

Примеры

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

Обратите внимание на то, что результат «3» указывает не на строку в таблице, а на позицию строки в выделенном диапазоне

Получаем значение 3

Формула функции здесь:

=ПОИСКПОЗ(900;B2:B6;1)

  • 900 – искомое значение.
  • B2:B6 – просматриваемый массив.
  • 1 – тип сопоставления (меньше или равно искомому).

Результат – «3», то есть третья позиция в указанном диапазоне.

11. ДЛСТР

Функция ДЛСТР позволяет определить длину текста, содержащегося в указанной ячейке.

Синтаксис

Формула функции имеет всего один аргумент – текст (номер ячейки):

=ДЛСТР(текст)

Пример

Функцию можно использовать для проверки длины символов в description.

Формула функции в примере: =ДЛСТР(A1). Введенный description содержит 152 символа

Длина – 125 символов

12. СЦЕПИТЬ

Функция СЦЕПИТЬ позволяет объединить несколько текстовых элементов в одну строку. В формуле для объединения элементов указываются как номера ячеек, содержащих текст, так и сам текст. Можно указать до 255 элементов и до 8192 символов.

Синтаксис

Для того чтобы объединить текстовые элементы без пробелов, используются следующие формулы:

=СЦЕПИТЬ(текст1;текст2;текст3)

Аргумент «текст» – текстовый элемент или ссылка на ячейку.

Примеры

В приведенном ниже примере введена формула:

=СЦЕПИТЬ(А2;B2;С2)

Слияние слов без пробелов сделало текст абсолютно нечитабельным

Сделаем ФИО читаемым при помощи формулы

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

=СЦЕПИТЬ(текст1;« »;текст2;« »;текст3;« »)

В следующем примере функция представлена формулой:

=СЦЕПИТЬ(A2;» «;B2;» «;C2)

Текстовые элемент разделены пробелами

Получилось. Текст стал читаемым

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

=СЦЕПИТЬ(«текст1 »;«текст2 »;«текст3 »)

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

Не перепутайте. Номера ячеек не сработают

13. ПРОПНАЧ

Функция ПРОПНАЧ преобразует заглавные буквы всех слов в тексте в прописные (верхний регистр), а все остальные буквы – в строчные (нижний регистр).

Синтаксис

Функция представлена короткой формулой, имеющей всего один аргумент:

=ПРОПНАЧ(текст)

Пример

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

В примере слова с разным размером букв приведены в надлежащий вид

Получили читаемый текст, исправив регистровку букв в ячейках

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

14. ПЕЧСИМВ

Функция ПЕЧСИМВ позволяет удалить все непечатаемые знаки из текста.

Синтаксис

=ПЕЧСИМВ(текст)

Пример

В приведенном примере текст в ячейке A1 содержит непечатаемые знаки конца абзаца.

Функция ПЕЧСИМВ убрала все ненужные символы

Если в ячейке присутствовали непечатаемые знаки конца абзаца, то они будут автоматически удалены

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

15. СЖПРОБЕЛЫ

Функция удаляет все лишние пробелы между словами.

Синтаксис

=СЖПРОБЕЛЫ(номер_ячейки)

Пример

Функция позволяет убрать лишние пробелы в тексте

Лишние пробелы удалены сразу

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

Функция не распознает пробелы внутри слов

Такие придется поправить вручную

16. НАЙТИ

Функция НАЙТИ позволяет обнаружить искомый текст внутри текстовой строки и указывает на начальную позицию этого текста относительно начала просматриваемой строки.

Синтаксис

Функция НАЙТИ состоит из 3 аргументов и представлена формулой:

=НАЙТИ(искомый_текст;просматриваемый_текст;[начальная_позиция]);

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

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

Пример

В примере функция представлена следующей формулой: =НАЙТИ(«чай»;A4)

Результат, который вывела формула – 9, то есть искомый текст («чай») начинается с девятого символа в рассматриваемой строке (А4).

Аналогичный результат даст функция НАЙТИБ (1 байт равен одному символу)

17. ИНДЕКС

Функция ИНДЕКС позволяет возвращать искомое значение.

Синтаксис

Формула функции ИНДЕКС имеет следующий вид:

=ИНДЕКС(массив; номер_строки; [номер_столбца])

«Номер столбца» – необязательный аргумент.

Пример

В этом примере введена формула: =ИНДЕКС(A2:B3;2;2), данным которой соответствует значение («сливы»)

Можно заменить ВПР, применив ИНДЕКС + ПОИСКПОЗ

Функцию ИНДЕКС можно использовать вместе с функцией ПОИСКПОЗ с целью замены функции ВПР.

18. СОВПАД

Функция проверяет идентичность двух текстов, и, если они совпадают, выдает значение ИСТИНА, если различаются – значение ЛОЖЬ.

Синтаксис

=СОВПАД(текст1;текст2)

Пример

В примере формула «=СОВПАД(A3;B3)» выдает значение ИСТИНА

Получаем значение true

Пары слов из строк 1 (A1 и B1) и 2 (A2 и B2) различны по написанию, поэтому функция выдает значение ЛОЖЬ, а слова из 3-й строки идентичны, поэтому определяются как ИСТИНА.

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

Читайте также

Продвижение «ВКонтакте»: 54 совета и море полезных сервисов

Как самому сделать мобильное приложение – все варианты

19. ИЛИ

Логическая функция ИЛИ возвращает значение ИСТИНА, если хотя бы один аргумент в формуле имеет значение ИСТИНА, и значение ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.

Синтаксис

=ИЛИ(логическое_значение1;[логическое_значение2];. )

Здесь «логическое значение1» – обязательный аргумент, остальные аргументы необязательны. В формулу можно добавлять от 1 до 255 логических значений.

Пример

Формула в примере появляется значение ИСТИНА, так как 2 из 3 аргументов имеют значение ИСТИНА.

Формула хорошо подходит для проверки различных вычислений

20. И

Функция И возвращает значение ИСТИНА, если все аргументы в формуле имеют значение ИСТИНА, и значение ЛОЖЬ, если хотя бы один из аргументов имеет значение ЛОЖЬ.

Синтаксис

Функция может содержать множество аргументов и имеет формулу:

=И(логическое_значение1;[логическое_значение2];…)

«Логическое_значение1» – обязательный аргумент, остальные аргументы – необязательные.

Формула в примере имеет 3 аргумента и выдает значение ИСТИНА

Проверили корректность формулы. Получилось значение true

В этом примере все аргументы имеют значение ИСТИНА, поэтому результат соответствующий.

Функции И и ИЛИ просты в использовании, но, если сочетать их друг с другом или с другими функциями (ЕСЛИ и НЕ), можно вывести более сложные и интересные формулы.

21. СМЕЩ

Функция СМЕЩ возвращает ссылку на диапазон, отстоящий от ячейки или группы ячеек на указанное число строк и столбцов.

Синтаксис

Функция состоит из 5-ти аргументов и представлена следующей формулой:

=СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;[высота];[ширина])

Рассмотрим каждый из аргументов:

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

Пример

Рассмотрим пример использования функции СМЕЩ, имеющую следующую формулу:

A4 – ссылка на ячейку, от которой вычисляется смещение.

С2 – ячейка, на которую ссылается ячейка А4, а в ячейке E2 введена формула с результатом «27» – возвращаемая ссылка.

Функция возвращает значение ячейки С2, координаты которой указаны в формуле (A4;-2;2)

Обратите внимание на подсказку. Мы получили значение 27 – корректно

Зачем маркетологу Excel

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

Работа в Excel с формулами и таблицами

Работа в Excel с формулами и таблицами

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

Маркетинг

Читайте также:

Из чего состоят формулы «Эксель»

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

Все действия в таблицах производятся в выбранных пользователем ячейках. Каждая из них имеет уникальный идентификатор — обозначения строки и столбца, на пересечении которых она располагается. Столбцы описываются буквами латинского алфавита, а строки — числами. Примеры идентификаторов ячеек — А2, B6, C12 и прочие.

Использование операторов

В формулах «Эксель» операторы нужны, чтобы программа «понимала», какие именно вычисления необходимо произвести. С их помощью складывают, умножают, сравнивают и объединяют значения из ячеек. Выделяют 9 типов действий:

  • Арифметические. Это сложение (+), вычитание (-), умножение (*), деление (/), процент (%) и возведение в степень (^). Например, если нужно вычесть значение ячейки B4 из показателя ячейки A2, то формула будет выглядеть так: =A2-B4.

Бизнес не может обойтись без выполнения расчетов и анализа данных — для этого и необходим «Эксель». А для того, чтобы собрать всю информацию по маркетинговым кампаниям, подключите Сквозную аналитику Calltouch. Сервис отследит количество сделок, лидов и прибыли по каждой кампании и рассчитает ROI для всех каналов продвижения. Все это вы увидите в наглядном отчете и узнаете, какие площадки приносят доход, а какие — только расходуют бюджет.

Сквозная аналитика Calltouch

  • Анализируйте воронку продаж от показов до денег в кассе
  • Автоматический сбор данных, удобные отчеты и бесплатные интеграции
  • Сравнение. Сюда входят равенство (=), больше и меньше (>, <), больше или равно (>=), меньше или равно (<=), не равно (<>). Когда значения верны, в ячейке появляется слово «ИСТИНА», в противном случае — «ЛОЖЬ».
  • Объединение текста. Чтобы соединить символы из нескольких ячеек, используют амперсанд (&). Для добавления пробела или какого-либо символа используют кавычки-лапки (“). Например: B3&” “&E2&” “&G8.
  • Изменение естественного порядка действий в операциях. В Excel соблюдается стандартный порядок выполнения действий в числовых выражениях — сначала программа умножает, затем — складывает значения. Если сложение требуется выполнить в первую очередь, данные заключают в скобки — например: A2*(D4+E3).
  • Добавление ссылок на ячейки. Чтобы одна ячейка отображала значение из другой, устанавливают ссылку с помощью знака «равно» и идентификатора ячейки — например: =A5.
  • Добавление простых ссылок. Когда нужно выбрать диапазон, указывают первую и последнюю ячейки, а между ними ставят двоеточие (:). Если же требуется выбрать определенные ячейки, их разделяют точкой с запятой (;). Например: =СУММ(A1:A11) или =СУММ(A3;A8;A9).
  • Добавление ссылок на другой лист. Чтобы добавить ссылку на другой лист, нужно написать его название, добавить восклицательный знак (!) и указать идентификаторы ячеек. Например: =Лист3!B6;B8.
  • Добавление относительных ссылок. Если ячейку с формулой скопировать, она автоматически подстроится под столбец или строку в соответствии с заданным расположением. Например, если в A10 написать формулу =СУММ(A1:A9) и скопировать ее в B10, то формула примет вид: =СУММ(B1:B9).
  • Добавление абсолютных ссылок. Иногда автоматический перенос формул требует корректировки. Зайдите в ячейку, выберите значение и нажмите на клавиатуре F4. Данные ячейки останутся неизменными. Формулу можно скопировать в другие ячейки.

Если в формулах в таблице Excel есть ошибки, программа идентифицирует их как «ЛОЖЬ» или просто не произведет вычисление.

Операции с формулами

Все операции с формулами Excel можно разделить на 6 типов:

  • Копирование/вставка. Формулы дублируют с помощью выбора команд «Копировать» и «Вставить» правой кнопкой мыши или комбинаций клавиш Ctrl+C и Ctrl+V. При копировании ячейка, данные из которой вы переносите в другую, обрамляется подвижным пунктиром.
  • Отмена операций. На панели быстрого доступа вверху располагается кнопка отмены действий в виде стрелки, повернутой влево. Также отменить операцию можно при помощи сочетания клавиш Ctrl+Z.
  • Повторение действий. Если операцию отменили ошибочно, можно вернуться к предыдущему шагу, нажав Ctrl+Y. Также можно использовать кнопку на панели быстрого доступа — она выглядит как стрелка, повернутая вправо.
  • Перетаскивание. Чтобы переместить ячейки из одного места в другое, нужно мышкой выделить диапазон и переместить курсор к одной из границ выделения. Появится четырехсторонняя стрелка, на которую необходимо нажать левой кнопкой мыши и, не отпуская, перетащить группу ячеек в другое место.
  • Копирование путем перетаскивания. Оно производится так же, как и в предыдущем пункте, но после выделения диапазона нужно зажать на клавиатуре Ctrl.
  • Автозаполнение формулами. Чтобы работать с этим инструментом, следует навести курсор на правый нижний край ячейки. Появится жирный черный крестик. Если на него нажать, можно продублировать формулу из одной ячейки на весь выделенный диапазон.

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

Примеры основных формул Excel

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

  1. Укажите знак «=».
  2. Введите название действия, например СУММ (сложение), ПРОИЗВЕД (умножение), КОРЕНЬ (квадратный корень числа). Если начать ввод, программа подскажет корректное название.
  3. В скобках укажите ячейки, для которых нужно выполнить действие (через точку с запятой), или их диапазон (через двоеточие). Например, в результате вычисления =СУММ(А2;С2;F2) отобразится сумма чисел трех указанных ячеек, а формула =СУММ(А2:F2) задействует весь промежуток от А2 до F2 включительно.
  4. Нажмите на Enter.

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

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

Технология
речевой аналитики
Calltouch Predict

  • Автотегирование звонков
  • Текстовая расшифровка записей разговоров

Выделим 12 наиболее популярных формул «Эксель»:

  • СУММ. Самая простая функция, с помощью которой складывают значения внутри ячеек.
  • СУММЕСЛИ. Тоже позволяет суммировать значения, но при соблюдении определенных условий. Например, когда нужно посчитать продажи от конкретного филиала или от определенной цены товара. В формуле сначала указывают диапазон, затем условие. Например, мы можем сложить стоимость всех товаров в столбце С, цена которых больше 100 рублей. Вид формулы: =СУММЕСЛИ(С1:С8;“>100”).
  • СТЕПЕНЬ. Функцию используют, когда нужно возвести в степень какое-нибудь число. Сначала пишут идентификатор ячейки, затем степень. Например: =СТЕПЕНЬ(В4).
  • СЛУЧМЕЖДУ. Формулы Excel позволяют находить случайное число из выбранного диапазона по принципу рандомайзера. Сначала указывают нижнюю границу, затем верхнюю. Например: =СЛУЧМЕЖДУ(А1;С12).
  • ВПР. Функция поиска, необходимая для работы с таблицами большого объема. Например, нужно найти номер телефона сотрудника по его фамилии. Для этого указывают искомое значение, потом выбранный диапазон, затем номер столбца. Интервальный просмотр нужен, чтобы найти приблизительное значение.
  • СРЗНАЧ. Высчитывает среднее арифметическое значение. Складывает все числа и делит полученную сумму на количество слагаемых. Полезно, когда нужно посчитать среднюю выручку со всех филиалов.
  • МАКС. С помощью этой функции определяют наибольшее значение среди отдельных ячеек или в рамках выбранного диапазона.
  • КОРРЕЛ. Оценивает связь между несколькими значениями. Чем больше отличий, тем меньше корреляция. Она может быть от -1 до +1. Такую функцию используют, например, для сравнения курсов валют.
  • ДНИ. Простая и полезная функция, которая высчитывает количество дней между датами. В первом значении указывают конечную дату и только потом — начальную.
  • ЕСЛИ. Функцию удобно использовать, когда необходимо узнать, выполняется условие или нет. Например, если работник выполнил план, ему назначают премию. Сначала указывают логическое выражение, потом значение, которое нужно показать при выполнении условий. Третье значение можно не указывать.
  • СЦЕПИТЬ. Помогает объединить несколько текстовых ячеек в одну. Чтобы текст не получился слитным, между значениями добавляют пробел в кавычках: ” “.
  • ЛЕВСИМВ. Функция поможет обрезать часть текста до определенного размера. Полезно при составлении метатегов для сайтов. Сначала указывают текст, затем количество знаков.

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

Главное в статье

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

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

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

И сегодня я хочу рассказать вам о принципах его работы, хитростях и подводных камнях. А чтобы не сводиться к сухим пересказам документации, разбавленным дополнениями «из жизни» — я заодно вкратце расскажу, как мы реализовывали подобный механизм.

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

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

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

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

Операнды – константы, массивы, ссылки;

  • ParsedThingNumeric
  • ParsedThingInt
  • ParsedThingString
  • ParsedThingBool
  • ParsedThingMissingArg
  • ParsedThingError
  • ParsedThingArray
  • ParsedThingName, ParsedThingNameX
  • ParsedThingArea, ParsedThingAreaErr, ParsedThingArea3d, ParsedThingAreaErr3d, ParsedThingAreaN, ParsedThingArea3dRel
  • ParsedThingRef, ParsedThingRefErr, ParsedThingRef3d, ParsedThingErr3d, ParsedThingRefRel, ParsedThingRef3dRel
  • ParsedThingTable, ParsedThingTableExt

Операторы – математические, логические, ссылочные, а так же вызовы функций;

  • ParsedThingFunc
  • ParsedThingFuncVar
  • ParsedThingAdd
  • ParsedThingSubtract
  • ParsedThingMultiply
  • ParsedThingDivide
  • ParsedThingPower
  • ParsedThingConcat
  • ParsedThingLess
  • ParsedThingLessEqual
  • ParsedThingEqual
  • ParsedThingGreaterEqual
  • ParsedThingGreater
  • ParsedThingNotEqual
  • ParsedThingIntersect
  • ParsedThingUnion
  • ParsedThingRange
  • ParsedThingUnaryPlus
  • ParsedThingUnaryMinus
  • ParsedThingPercent

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

  • ParsedThingMemArea
  • ParsedThingMemNoMem
  • ParsedThingMemErr
  • ParsedThingMemFunc
  • ParsedThingParentheses
  • ParsedThingAttrSemi
  • ParsedThingAttrIf
  • ParsedThingAttrChoose
  • ParsedThingAttrGoto
  • ParsedThingAttrSum
  • ParsedThingAttrSpace
  1. «=A1*(1+true)». Во внутреннем представлении будет выглядеть так:
  2. «=SUM(A1,1,”2”,)». Во внутреннем представлении будет выглядеть так:
Вычисления

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

Но в вычислении выражений из Excel не обошлось и без хитростей. Разработчики наделили все операнды свойством «тип значения». Это свойство указывает, как должен быть преобразован операнд перед вычислением оператора или функции. Например, обычные математические операторы не могут выполняться над ссылками, а могут только над простыми значениями (числовыми, логическими и т.д.). Чтобы выражение “A1 + B1:C1” работало корректно, Excel указывает для ссылок A1 и B1:C1, что те должны быть преобразованы к простому значению перед помещением результата вычисления в стек.

  • Reference;
  • Value;
  • Array.

Конечным пользователям не нужно забивать голову типами данных: Excel сам подбирает нужный тип операнда уже на этапе разбора выражения. А на этапе вычисления не обойтись без «неявного приведения типов». Оно происходит в соответствии со следующей схемой:

Значение типа Value можно преобразовать к Array, в этом случае создастся массив из одного значения. В обратном направлении (Array->Value) преобразование тоже достаточно простое — из массива берется первый элемент.

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

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

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

Пусть мы хотим привести к Value значения ячеек с A1 по A4, в которых находится одинаковая формула “=B2:B3”, имеющая тип Reference. Диапазон B2:B3 состоит из одной колонки. Если бы это было не так и колонок было бы больше, преобразование Reference к Value для всех ячеек с A1 по A4 вернуло бы #VALUE! и на этом бы завершилось. Ячейки A2 и A3 находятся в строках, пересекающихся с диапазоном B2:B3. Преобразование Reference->Value для этих ячеек вернет соответствующее значение из диапазона B2:B3, т.е. преобразование для A2 вернет 1, а для A3 вернет 2. Для остальных ячеек, таких как A1 и A4, преобразование вернет #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 просто не даст такую формулу записать в ячейку.

Выражения внутри “имен” и некоторых других конструкций приводятся к типу «по умолчанию». В отличие от формул внутри ячеек, выражения в которых приводятся к типу Value. Выражение внутри некоторого “имени” name «=A1:B1» в результате вычисления будет равно диапазону A1:B1. Это же выражение в ячейке будет вычисляться и в результате будет либо одно значение, либо ошибка #VALUE! Но выражение в ячейке «=name» уже будет иметь тип Value и будет вычисляться в зависимости от текущей ячейки.

Парсер

Написав на коленке первый вариант парсера мы поняли, что монстр слишком велик и слабо поддается модернизации. А она в нашем случае была неизбежна, поскольку большое количество тонкостей мы познавали уже когда парсер худо-бедно работал. Для интереса решил попробовать другие методы и вооружился для этого генератором трансляторов Coco/R. Выбор на него в тот момент пал в основном из-за того, что я был с ним уже неплохо знаком. Coco/R оправдал мои надежды. Сгенеренный им парсер показал весьма неплохие результаты по скорости работы, поэтому решили остановиться на этом варианте.

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

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

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

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

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

void GenTokens() < foreach (Symbol sym in tab.terminals) < if (Char.IsLetter(sym.name[0])) gen.WriteLine("\tpublic const int _= ;", sym.name, sym.n); > > 

Далее, получившийся код продолжает быть невалидным, теперь уже по мнению FxCop. В нашей компании сборки постоянно тестируются на соответствие большому числу правил. Конечно, поскольку код сгенерирован, можно было бы сделать для него исключение и подавить проверку сгенерированных классов. Но это не лучший выход. К счастью, проблема только одна – публичные поля не соответствуют правилу Microsoft.Design: CA1051. Чтобы все исправить достаточно внести необходимые правки в файлы Parser.frame и Scanner.frame, которые располагаются рядом с файлом грамматики. То есть, сам Coco/R пересобирать не надо. Вот примеры:

public Scanner scanner; public Errors errors; public Token t; // last recognized token public Token la; // lookahead token 

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

При разработке грамматики для Coco/R я пользовался плагином для студии.

Его плюшки

  • Подсветка синтаксиса для файла с грамматикой;
  • Автоматический запуск генератора при сохранении файла с грамматикой;
  • Intellisense для ключевых слов;
  • Показывает ошибки компиляции, возникающие в файле парсера в соответствующем месте в файле с грамматикой

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

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

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

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

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

public interface IBuffer < string GetString(int beg, int end); int Peek(); int Pos < get; set; >int Read(); > 

Сама реализация StringBuffer простая:

public class StringBuffer : IBuffer < int stringLen; int bufPos; string str; public StringBuffer(string str) < stringLen = str.Length; this.str = str; if (stringLen >0) Pos = 0; else bufPos = 0; > public int Read() < if (bufPos < stringLen) return str[bufPos++]; else return StreamBuffer.EOF; >public int Peek() < int curPos = Pos; int ch = Read(); Pos = curPos; return ch; >public string GetString(int beg, int end) < return str.Substring(beg, end - beg); >public int Pos < get < return bufPos; >set < if (value < 0 || value >stringLen) throw new FatalError("buffer out of bounds access, position: " + value); bufPos = value; > > > 

Тестируем

  1. инициализация из строки;
  2. чтение символа (вызов метода IBuffer.Read() N раз) ;
  3. получение 10 символов из строки(вызов IBuffer.GetString(i-10, i) (N-10) раз).

При N = 100:
Init x 100000:
Buffer: 171 мс
StringBuffer: 2 мс
Read xNx10000:
Buffer: 14 мс
StringBuffer: 8мс
GetString x (N-10) x 10000:
Buffer: 250 мс
StringBuffer: 20 мс

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

Грамматика для Coco/R описывается в РБНФ ( EBNF ). Разработка грамматики для Coco/R сводится к построению РБНФ и оформлению ее в соответсвии с грамматикой Coco/R в файле с расширением atg.

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

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

COMPILER FormulaParserGrammar 

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

CHARACTERS digit = "0123456789". chars = "~!@#$%^&*()_-+=<[]>|\\:;\"',./?<> ". eol = '\r'. blank = ' '. letter = ANY - digit - chars - eol - blank + '_'. 

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

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

TOKENS ident = letter . wideident = letter ('?'|'\\') . positiveinumber = digit . fnumber = "." digit [("e" | "E") ["+" | "-"] digit ] | digit ( "." digit [("e" | "E" ) ["+" | "-"] digit ] | ("e" | "E") ["+" | "-"] digit ). space = blank. quotedOpenBracket = "'[". quotedSymbol = "''" | "']" | "'@" | "'#". pathPart = ":\\". trueConstant = "TRUE". falseConstant = "FALSE". 
  • Может содержать только буквы, цифры, и символы: _. \,?;
  • Не может быть равен TRUE или FALSE;
  • Первый символ может быть только буквой, знаком подчеркивания, или бекслешем;
  • Если первый символ строки – бекслеш, то второго символа может не быть, либо это должен быть один из: _. \,?;
  • Не должен быть схож с названием диапазона (например, A10);
  • Не должен начинаться на строку, которая может быть воспринята как ссылка в формате R1C1. Природа этого условия сложнообъяснима, приведу только несколько примеров идентификаторов, которые ему не удовлетворяют: “R1_test”, “R1test”,“RC1test”,“R”,“C”. При этом «RCtest» – вполне подходит.

Выделение quotedOpenBracket, quotedSymbol и pathPart в отельный токен – не более чем хитрость. Она позволила пропустить символы в именах колонок в табличной ссылке, перед которыми должен следовать апостроф. Например, в выражении “=Table1[Column'[1′]]” имя колонки начинается после символа ‘[’ и продолжается до символа ‘]’. При этом первый такой символ вместе с предшествующим ему апострофом будет прочитан сканером как терминал quotedSymbol(‘]) и, тем самым, чтение имени колонки на нем не остановится.

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

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

По всем нетерминалам (из которых Coco/R сделает методы) будет передаваться ссылка на выражение в RPN форме, а так же тип данных, к которому надо его привести. При вызове парсера для формулы внутри ячейки начальный тип данных – Value. Далее во время разбора он будет меняться, и в ветви дерева разбора будет передаваться подготовленный тип. К примеру, при разборе выражения “=OFFSET(A1:B1, A1, A2)” элемент польской записи — функция OFFSET — получит тип Value, при разборе же аргументов первый будет приводиться к Reference, другие два к Value. Для всех функциий мы храним информацию, какие аргументы и каких типов должны в нее передаваться.

Задачей парсера также является проверка формулы на правильность. Формулу будем считать некорректной, если Excel не дает записать ее в ячейку. Кроме синтаксических ошибок формулу некорректной могут сделать и неправильное количество аргументов, переданное в функцию или же несовпадение типа данных запрошенному. Например, функция ROW либо вообще не нуждается в параметрах либо только в одном, и он должен быть исключительно Reference. Мы уже говорили, что к Reference невозможно привести ни один другой тип, а это значит, что выражения «=ROW(1)», «=ROW(“A1”)» будут невалидными.

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

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

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

LogicalClause (. IParsedThing thing = null; .) = ConcatenateClause < ( '' (.thing = ParsedThingGreater.Instance; .) | "=" (.thing = ParsedThingGreaterEqual.Instance; .) | "<>" (.thing = ParsedThingNotEqual.Instance; .) | '=' (.thing = ParsedThingEqual.Instance; .) ) ConcatenateClause (. expression.Add(thing); .) > . 

По этому принципу будут строится: ConcatenateClause, AddClause, MultipyClause, PowerClause, UnaryClause, PercentClause, RangeUnionClause, RangeIntersectionClause, CellRangeClause. На CellRangeClause заканчиваются нетерминалы, описывающие операторы. За ним следует первый операнд – OperandCommonClause. Он будет выглядеть примерно так:

Однако, в приведенной грамматике есть неоднозначность. Она заключается в том, что SheetName и OperandClause могут начинаться с одного и того же терминала — с идентификатора. Например, может следовать выражение “=Sheet!A1”, а может “=name”. Тут “Sheet” и “name” – идентификаторы. К счастью, Coco/R позволяет разрешать конфликты, просматривая входящий поток сканером на несколько терминалов вперед. Т.е. мы можем просмотреть в поисках символа ‘!’, если таковой будет найден – то мы разбираем SheetName, иначе – OperandClause. Вот так будет выглядеть грамматика:

OperandCommonClause = ( IF(IsSheetDefinition()) ( ( SheetNameQuoted | [ '[' FileDefinitionSimple ] [ SheetName [ ':' SheetName | '|' SingleQuotedIdent ] ] ) '!' CellReferenceClause ) | OperandClause ) . 

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

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

SheetName (. int sheetNameStart = la.pos;.) = ( [positiveinumber | fnumber] [ident] ) (. sheetName = scanner.Buffer.GetString(sheetNameStart, la.pos); .) . 

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

SheetName (. int sheetNameStart = la.pos;.) = ( positiveinumber | fnumber [ IF(la.pos - t.pos == t.val.Length) ident ] | ident ) (. sheetName = scanner.Buffer.GetString(sheetNameStart, la.pos); .) . 

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

OperandClause = ( CellReferenceClause | ArrayClause | StringConstant ) | '(' CommonCellReference ')' (. expression.Add(ParsedThingParentheses.Instance);.) . 

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

CellReferenceClause = ( IF (IsTableDefinition()) TableReferenceExpressionClause | IF (IsFunctionDefinition()) FunctionClause | IF (IsDefinedNameDefinition()) DefinedNameClause | IF(IsRCCellPosition()) CellPositionRCClause | IF(IsA1CellPosition()) CellPositionA1Clause | CellError | TermNumber | BoolConstant | wideident (. expression.Add(new ParsedThingName(t.val);.) ) . 

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

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

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

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

void RegisterCellRange(CellRange range, int sheetDefinitionIndex, int position, int length) 

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

На этом же стал основываться еще один механизм – предсказания. В выражении “=1*(1+2” нарушен баланс скобок, но, с большой вероятностью, пользователь забыл поставить скобку именно в конце выражения. То есть можно попробовать исправить эту формулу, дописав к ней недостающую скобку. Конечно, парсер сам этим заниматься не будет, он только скажет где и чего по его мнению не хватает. Так, например, в уже знаком нам OperandClause появилась следующие строки:

'(' CommonCellReference (. if(la.val != ")") parserContext.RegisterSuggestion(new FunctionCloseBracketSuggestion(la.pos)); .) ')' 

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

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

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

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

Класс атрибутов Mem. Сюда входят сразу несколько атрибутов. Их объединяет то, что они созданы для оптимизации вычисления ссылок. По сути, они являются оберткой над некоторым выражением. Во время вычисления внутреннее выражение может и не вычисляться, за него результат выдаст Mem. Отличительной особенностью этих элементов является то, что они вставляются в обратной польской записи до выражения, которое оптимизируют.

  • ParsedThingMemFunc — указывает на то, что выражение внутри должно вычисляться каждый раз и не результат не может быть закеширован. Например, все выражение =INDIRECT(«A1»):B1 будет обернуто в MemFunc, т.к. функция INDIRECT является volatile функцией и при следующем расчете может вернуть уже другое значение.
  • ParsedThingMemArea. Заключает в себе выражение, значение которого уже посчитано и не будет меняться. Это значение сохранится внутри атрибута и при следующем расчете в стек будет добавлено именно оно, а внутреннее выражение вычисляться вообще не будет.
  • ParsedThingMemErr. Заключает в себе выражение, значение которого посчитано, не будет меняться и равно ошибке.
  • ParsedThingMemNoMem. При вычислении выражения внутри Excel столкнулся с нехваткой памяти. На практике я такое ни разу не встречал.

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

Атрибут AttrIf применяется совместно с одним или двумя операторами Goto для оптимизации вычисления функции IF. Напомню синтаксис функции IF: IF(условие, значение_истина, [значение_ложь]). Из двух значений можно вычислить только одно и сэкономить время на вычислении другого, если сразу после вычисления условия перейти к нужному значению. Тем самым, простое выражение =IF(condition,”v_true”,”v_false”) Excel густо разбавляет атрибутами. Получается примерно следующее:

Вычисление идет так. Значение condition помещается в стек. Следующим на очереди идет атрибут IF. Он смотрит на значение на вершине стека. Если оно истинно — ничего не делает. Если ложно, прибавляет текущий счетчик элементов в выражении на записанное внутри смещение, тем самым счетчик начинает указывать на “v_false”. Следующим рассчитывается либо “v_true”, либо “v_false” и результат помещается в стек. Далее идет Goto, первый или второй. Но оба они ссылаются на конец выражения (либо на следующие операторы в выражении, если таковые имеются).

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

  • SpaceBeforeBaseExpression,
  • CarriageReturnBeforeBaseExpression,
  • SpaceBeforeOpenParentheses,
  • CarriageReturnBeforeOpenParentheses,
  • SpaceBeforeCloseParentheses,
  • CarriageReturnBeforeCloseParentheses,
  • SpaceBeforeExpression;

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

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

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

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

Огромное количество знаний о работе формул в Excel мы получили, когда запустили задачу на тестирование вычислений формул из тех же 10к OpenXML и 20к xls файлов. Файл открывается, записывается в модель данных. Затем поочередно мы начинаем помечать ячейки на листе как не посчитанные, вычисляем и сравниваем новое значение с тем значением, которое было прочитано из файла. Тем самым мы убили двух зайцев – отладили парсер формул и привели результаты вычислений максимально близко к тем, что получаются при использовании Excel.

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

Спасибо за внимание!

  • Блог компании Developer Soft
  • Веб-разработка
  • Программирование

7 полезных формул для тех, кто считает деньги в эксель-таблице

Аватар автора

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

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

Как упростить жизнь с помощью «Экселя»
Курс, о том, как таблицы помогут планировать бюджет, считать расходы и упорядочить бытовые дела

Баннер

Начать учиться

Соединить текст из разных ячеек

Иногда надо быстро собрать данные из разных ячеек в одной. Поочередно копировать долго и неудобно, поэтому лучше использовать формулу с амперсандом — знаком «&».

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

Соединение текста экономит скорее время, чем деньги, но при правильном подходе это легко конвертировать

Подобрать значения для нужного результата

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

Для этого на вкладке «Данные» надо выбрать «Анализ „Что если“», с помощью функции «Подбор параметра» задать целевое значение и выбрать ячейку, которую нужно изменить для получения желаемой цифры.

Обложка статьи

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

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

Обновить курс валют

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

Чтобы использовать эту функцию, на вкладке «Данные» выберите кнопку «Из интернета» и вставьте адрес надежного источника, например cbr.ru. Эксель предложит выбрать, какую именно таблицу нужно загрузить с сайта — отметьте нужную галочкой.

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

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

Планировать действия

Теперь, когда курсы валют и стоимость акций подгружаются автоматически, эксель становится динамичным рабочим инструментом. Но можно пойти дальше и требовать от него реакций — и даже советов! — при определенных изменениях. Для этого понадобится функция «Если». Она заполняет ячейки заданными значениями в зависимости от того, что происходит в остальной таблице.

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

Обложка статьи

Вот так: =ЕСЛИ (ячейка с ценой акции ≥ цена выгодной продажи; «продавать»; ЕСЛИ (ячейка с ценой акции ≤ цена выгодной покупки; «покупать»; «ничего»)).

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

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

Выделить цветом нужные данные

Когда таблицы большие и многое происходит автоматически, легко пропустить что-то важное. От этого спасает функция выделения цветом. Для ее активации на главной вкладке выберите «Условное форматирование» и задайте условия и цвет выделения.

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

Также можно выделить значения, которые находятся в определенном интервале (в условиях форматирования — «между»), содержат нужный текст («текст содержит»), или задать сразу несколько условий

Суммировать только нужное

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

Мы попробуем узнать, сколько Аня тратит на еду в офисе. Для этого в таблице создаем формулу =СУММ((А2:А16=F2)*(B2:B16=F3)*C2:С16) и получаем 915 рублей. Теперь постепенно.

В первой скобке программа ищет значение из ячейки F2 («Аня») в столбце с именами. Во второй скобке — значение из ячейки F3 («Еда на работе») из столбца с категориями расходов. А после считает сумму ячеек из третьего столбца, которые выполнили эти условия.

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

Расставить по порядку

В экселе можно быстро узнать максимальное, минимальное и среднее значение для любого массива ячеек. Для этого в скобках формул =МАКС(), =МИН() и =СРЗНАЧ() нужно указать диапазон ячеек, в которых будет искать программа. Это пригодится для таблицы, в которую вы записываете все расходы: вы увидите, на что потратили больше денег, а на что — меньше. Еще этим тратам можно присвоить «места» — и отдать почетное первое место максимальной или минимальной сумме.

Например, вы считаете зарплаты сотрудников и хотите узнать, кто заработал больше за определенный срок. Для этого в скобках формулы =РАНГ() через точку с запятой укажите ячейку, порядок которой хотите узнать; все ячейки с числами; 1, если нужен номер по возрастанию, или 0, если нужен номер по убыванию.

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

Уверены, теперь вы сможете прокачать наши таблицы до максимального уровня:

  1. Экселька, которая ведет семейный бюджет.
  2. Помогает выбрать что угодно.
  3. И считает доходность по вкладам.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *