Три редких, но полезных формул в редакторе таблиц Р7-Офис

Фото: magnific

Вычисляем ставку

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

Например, автосалон предлагает машину в кредит. Менеджер уверяет, что ставка по акции составляет всего 5% годовых. Но в договор вшиты такие необременительные мелочи как обязательная страховка жизни, карта помощи на дорогах и другие дополнительные услуги. В итоге при стоимости машины в 3 000 000 рублей вам выдают кредит на 3 600 000 рублей (с учетом «допов»). Допустим, платить вам за средство передвижения предстоит 5 лет по 75 000 рублей каждый месяц. Сделаем небольшую табличку, чтобы оценить масштабы финансовой нагрузки. 

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

Кпер — количество платёжных периодов. Платить нам пять лет, поэтому указываем редактору на поле B2 (можно мышкой, кликнув в квадрат со вписанным крестиком). Но платим мы каждый месяц, то есть 12 раз в год, так что умножим на 12.

Плт — размер регулярного платежа. У нас это поле B3, 75000. Обратите внимание — со знаком минус: мы же отдаём деньги.

Пс — полная сумма нашего кредита.

И по необязательным тоже стоит пробежаться.

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

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

Нажимаем «Ок», и вот результат:

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

Но и это не всё. Реальная стоимость приобретаемого автомобиля — 3 млн рублей, так что давайте подставим в таблицу эту сумму.

Больше 17% — совсем не похоже на льготные 5%, не правда ли?

Считаем точное число рабочих дней

Иногда бывает необходимо сосчитать количество рабочих дней за определённый период. В больших организациях это делает специальное ПО, и делает, как правило, безошибочно (за очень редкими исключениями, связанными чаще всего с человеческим фактором). Что делать, если бухгалтер вам недоступен, но очень хочется прикинуть свою эффективность в роли самозанятого, или просто проверить, правильно ли вам начисляют заработную плату? Считать вручную, склонившись над календарём — долго и непродуктивно. Здесь вам пригодится функция ЧИСТРАБДНИ. Она достаточно простая, но при этом бывает очень полезна.

Встав в нужное поле, на вкладке «Формула» жмём кнопку «Функция». Вводим уже известное нам название. Достаточно ввести «чистр», чтобы осталось всего два варианта.

Выбираем нужный. И поговорим об аргументах.

Их тут немного. Нач_дата и Кон_дата — очевидно, точки старта и окончания нашего периода, и это все обязательные аргументы. В принципе для подсчёта рабочих дней этого хватит, но есть ведь праздники, которые ещё и меняются постоянно. Например, еще совсем недавно 31 декабря не было выходным днём. Табличный редактор следить за нормотворчеством наших дорогих властей не умеет, так что считаем сами и указываем на нужное поле. Можно вписать его адрес, можно указать мышкой, кликнув в уже знакомый нам квадрат с крестиком.

Получилось 187 рабочих дней — что и требовалось выяснить.

Подставляем правильно!

Иногда (на самом деле довольно часто) бывает нужно отредактировать текстовое содержимое ячеек. Например, данные поступили в не самом удобоваримом виде, и с ними просто сложно работать. Допустим, у нас есть список контрагентов, для каждого их которых указана организационно-правовая форма АО. Надо бы избавиться от лишних символов — мы и так знаем, что они АО, а вот поле с автофильтром (для сортировки и выборочной демонстрации) будет выглядеть опрятнее. То есть, мы хотим видеть в поле вместо АО «ТАОБАО» просто «ТАОБАО».

Табличка для примера. Мы заранее встали в нужное поле и на вкладке «Формула» нажали значок «Функция», ввели «подста» и выбрали функцию «ПОДСТАВИТЬ».

Обязательные аргументы у нас здесь такие:

Текст — выбираем поле, в котором мы хотим что-то поменять;

Стар_текст — нет, тут не про звёзды. Это про текст, который надо заменить. Может быть слово целиком, может быть несколько символов, или даже один-единственный. Мы хотим удалить «АО» — вводим.

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

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

Итак, жмём «Ок», протягиваем нашу формулу вниз, любуемся результатом.

Очевидно, что-то пошло не так. Там, где сочетание символов «под замену» встречалось и в названии организации, оно тоже пошло «под нож», и АО «АОРИСТ» превратилось в непонятный «РИСТ». А о том, что случилось с АО «ТАОБАО», просто рассказывать страшно.

Вот тут нам как раз номер вхождения пригодится. 

И результат:

Уже лучше, не так ли?

Но возможности функции ПОДСТАВИТЬ этим не исчерпываются. Часто в списках контрагентов организационно-правовая форма статусом АО не ограничивается — встречаются ведь и ООО, и даже ИП. Хорошо, что наша функция умеет работать по нескольким «целям» сразу, для чего можно использовать вложенные друг в друга скобки. Например, она может выглядеть примерно так:

=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A3;"ООО ";"");"АО ";"");"ИП ";"")

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

И работать это будет примерно так:

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