РАБДЕНЬ
Результатом является значение даты, которое может быть отформатировано как дата. Таким образом, получается дата дня, который входит в заданное число рабочих дней , начиная с начальной даты .
This function is part of the Open Document Format for Office Applications (OpenDocument) standard Version 1.2. (ISO/IEC 26300:2-2015)
Синтаксис
WORKDAY(StartDate; Days [; Holidays])
Начальная дата : дата начала расчётов. Если начальная дата попадает на рабочий день, этот день будет учтён.
Дни : количество рабочих дней. Положительное значение — для результата после начальной даты, отрицательное значение — для результата перед начальной датой.
Праздники : необязательный список праздничных дней. Они являются нерабочими. Введите диапазон ячеек, в котором перечислены праздничные дни по отдельности.
При вводе дат как части формул, косые черты или тире, используемые в качестве разделителей дат, интерпретируются как арифметические операторы. Поэтому даты, введённые в этом формате, не распознаются как даты и приводят к ошибочным вычислениям. Чтобы даты не интерпретировались как части формул, используйте функцию DATE, например DATE (1954;7;20) или поместите дату в кавычки и используйте нотацию ISO 8601, например «1954-07-20». Избегайте использования форматов даты, зависящих от локальных настроек, таких как «07/20/54», расчёт может привести к ошибкам, если документ загружен в соответствии с иными локальными настройками.
Unambiguous conversion is possible for ISO 8601 dates and times in their extended formats with separators. If a #VALUE! error occurs, then unselect Generate #VALUE! error in LibreOffice — Preferences Tools — Options — LibreOffice Calc — Formula , button Details. in section «Detailed Calculation Settings», Conversion from text to number list box.
This function ignores any text or empty cell within a data range. If you suspect wrong results from this function, look for text in the data ranges. To highlight text contents in a data range, use the value highlighting feature.
Примеры
На какую дату приходится 17 рабочих дней после 1 декабря 2001 года? Введите начальную дату «2001-12-01» в ячейку C3 и количество рабочих дней в ячейку D3. Ячейки от F3 до J3 содержат следующие рождественские и новогодние праздники: «2001-12-24», «2001-12-25», «2001-12-26», «2001-12-31», «2002-01-01».
=WORKDAY(C3;D3;F3:J3) returns 2001-12-28. Format the serial date number as a date, for example in the format YYYY-MM-DD.
LibreOffice Calc – полезные приемы работы с таблицей
Накопилось некоторое количество “фокусов” работы с Open/Libre Office. Если предыдущая заметка на эту тему была посвящена LO Writer, то на сей раз – “подопытным” выступает пакет Calc (электронные таблицы, аналог MS Excel).
Для затравки – простое, но часто нужное действие – перемещение строк и столбцов таблицы.
Перемещение столбцов / строк
- Выделить нужный диапазон при помощи выделения всего столбца/строки (для этого можно щелкнуть ЛКМ на заголовок столбца/строки).
- Кликнуть и зажать ЛКМ внутри выделенного диапазона (на самих выделенных ячейках с данными, не на заголовке).
- Тащить мышью диапазон, затем при отпускании — выделенный диапазон перезапишет тот диапазон ячеек, на который был перетащен. А при зажатом Alt – диапазоны ячеек поменяются местами.
LibreOffice Calc: поменять местами столбцы
Изменение типа данных ячеек
- Выделить нужный диапазон ячеек (или весь столбец/строку целиком по клику на заголовок).
- Верхнее меню: Данные -> Текст по столбцам.
- ЛКМ на столбец в нижней области открывшегося окошка (для выделения диапазона, в котором требуется преобразование).
- Выбрать тип столбца – Ок.
Эту операцию удобно проиллюстрировать примером: есть столбец с датами, которые хранятся, как текст (т.е. тип данных внутри офиса у них – текстовый, такое часто встречается при работе с кривыми выгрузками для Экселя). Использование меню Формат – Формат чисел – Дата не поможет в данном случае, т.к. дата хранится в виде строки ‘2023-06-16.
Вот здесь и пригодится изменение типа данных ячеек, после которого их уже можно будет отформатировать, как даты (например — чтобы изменить отображение, если надо).
LibreOffice Calc: преобразовать текст в дату
Автоматическое вычисление промежуточных итогов
Ещё одна отличная функция Calc – можно получить итоговые значения для заданного набора данных (агрегировать). Буквально – в несколько кликов! Что отдельно приятно – функция работает с фильтрами.
- Ctrl + * – выделить все данные на листе. Можно выбирать и не весь диапазон, естественно.
- Данные -> Промежуточные итоги.
- Выбрать столбец, по которому агрегировать.
- Выбрать столбец для которого нужно получить агрегат.
- Выбрать агрегатную функцию.
- Кнопками слева от таблицы (1-2-3 и +/-) можно переключать вид (свернуть-развернуть).
LibreOffice Calc: промежуточные итоги (subtotals)
Вывод, в общем-то, простой: если поискать, поинтересоваться и немного глубже узнать возможности электронных таблиц Calc – с их помощью можно достаточно широкий круг рутинных задач решить быстро и просто. Пользуйтесь с удовольствием!
Приготовлено из Телеграм-канала Press any key to.
Финансовые функции (часть первая)
В эту категорию входят математические функции LibreOffice Calc для финансовых вычислений.
DB
Возвращает снижение стоимости актива для определённого периода по методу «суммы чисел».
Такой метод снижения стоимости можно использовать, если значение в начале амортизации должно быть выше, чем при линейной амортизации. Значение амортизации уменьшается с каждым периодом на величину, которая уже вычтена из начальной стоимости.
Синтаксис
ФУО(Стоимость; Ликв_стоим; Время_эксплуатации; Период [;Месяц])
Стоимость : начальная стоимость актива.
Ликвидационная стоимость : стоимость актива в конце амортизации.
Время эксплуатации – период, в течение которого стоимость актива снижается.
Период длительность каждого период. Для этого параметра следует использовать тот же формат даты, что и для периода амортизации.
Месяц (необязательно): количество месяцев для первого года амортизации. Если параметр не определен, то используется значение по умолчанию 12.
Пример
A computer system with an initial cost of 25,000 currency units is to be depreciated over a three-year period. The salvage value is to be 1,000 currency units. The first period of depreciation comprises 6 months. What is the fixed-declining balance depreciation of the computer system in the second period, which is a full year starting from the end of the first six-month period?
=DB(25000; 1000; 3; 2; 6) returns 11,037.95 currency units.
PV
Возвращает текущую стоимость инвестиции после ряда платежей.
Эта функция служит для расчёта суммы, необходимой для инвестиций с фиксированным процентом для получения определённой суммы (годовой ренты) за указанное число периодов. Можно также определить, какая сумма будет оставаться после истечения периода. Кроме того, необходимо указать время для выплаты суммы: в начале или в конце каждого периода.
Введите значения в виде чисел, выражений или ссылок. Например, если ежегодный процент составляет 8%, но в качестве периода требуется указать месяц, введите 8%/12 в поле Процент и LibreOffice Calc автоматически рассчитает правильное значение.
Синтаксис
ПС(Ставка; КПЕР; Платёж [;Остаток [;Тип]])
Процент : процентная ставка за каждый период.
КПер – общее число периодов (платёжный период).
Выплата – регулярная выплата за период.
БудЗначение (необязательно) определяет будущее значение, оставшееся после итогового платежа.
Тип (необязательно) определяет дату исполнения для платежей. Type = 1 означает, что срок исполнения находится в начале периода, а Type = 0 (по умолчанию) означает, что срок исполнения находится в конце периода.
В функциях LibreOffice Calc параметры, отмеченные, как «необязательные» могут быть пропущены, только если нет параметров, идущих после. Например, в функции с четырьмя параметрами, в которой последние два параметра «необязательные», вы можете пропустить 4-й параметр или 3-й и 4-й, но нельзя пропустить только 3-й параметр.
Пример
Какова текущая стоимость инвестиций, если ежемесячные выплаты составляют 500 денежных единиц, а ежегодная процентная ставка — 8%? Платёжный период — 48 месяцев; в конце платёжного периода должно остаться 20000 денежных единиц.
=PV(8%/12;48;500;20000) = -35 019,37 денежных единиц. При описанных выше условиях на текущий момент необходимо вложить 35 019,37 денежных единиц, чтобы ежемесячные выплаты составляли 500 денежных единиц в течение 48 месяцев, а остаток на конец периода был равен 20 000 денежных единиц. Перекрёстная проверка показывает, что 48 x 500 денежных единиц + 20 000 денежных единиц = 44 000 денежных единиц. Разница между этой суммой и вложенной суммой, равной 35 000 денежных единиц, – выплаченные проценты.
Если вместо значений в формулу ввести ссылки, можно рассчитать любое число сценариев типа «если-то». Обратите внимание, что ссылки на константы должны быть определены как абсолютные ссылки. Примеры такого применения функции можно найти в описании функций амортизации.
АМОРУВ
Служит для расчёта величины линейной амортизации за платёжный период. Если имущество приобретено в течение платёжного периода, используется пропорционально распределенная амортизация.
Синтаксис
АМОРУВ(Стоимость; Дата; Первый период; Остаточная стоимость; Период; Ставка [;Базис])
Стоимость – первоначальная стоимость.
Дата – дата приобретения.
Первый период – дата окончания первого платёжного периода.
Ликвидационная стоимость – ликвидационная стоимость имущества в конце периода амортизации.
Период – рассматриваемый платёжный период.
Процент – процентная ставка амортизации.
Аргумент Базис : выбирается из списка и указывает метод вычисления года.
Американский метод (NASD): 12 месяцев по 30 дней в каждом
Точное число дней в месяцах, точное число дней в году
Точное число дней в месяцах, число дней в году принимается за 360
Точное число дней в месяцах, число дней в году принимается за 365
Европейский метод: 12 месяцев по 30 дней в каждом
Пример
An asset was acquired on 2020-02-01 at a cost of 2000 currency units. The end date of the first settlement period was 2020-12-31. The salvage value of the asset at the end of its depreciable life will be 10 currency units. The rate of depreciation is 0.1 (10%) and the year is calculated using the US method (Basis 0). Assuming linear depreciation, what is the amount of depreciation in the fourth depreciation period?
=AMORLINC(2000; «2020-02-01»; «2020-12-31»; 10; 4; 0.1; 0) returns a depreciation amount of 200 currency units.
Be aware that Basis 2 is not supported by Microsoft Excel. Hence, if you use Basis 2 and export your document to XLSX format, it will return an error when opened in Excel.
АМОРУМ
Служит для расчёта величины дегрессивной амортизации за платёжный период. В отличие от АМОРУВ для этой функции используется коэффициент амортизации, не зависящий от периода амортизации.
Синтаксис
АМОРУМ(Стоимость; Дата; Первый период; Остаточная стоимость; Период; Ставка [;Базис])
Стоимость – первоначальная стоимость.
Дата – дата приобретения.
Первый период – дата окончания первого платёжного периода.
Ликвидационная стоимость – ликвидационная стоимость имущества в конце периода амортизации.
Период – рассматриваемый платёжный период.
Процент – процентная ставка амортизации.
Аргумент Базис : выбирается из списка и указывает метод вычисления года.
Американский метод (NASD): 12 месяцев по 30 дней в каждом
Точное число дней в месяцах, точное число дней в году
Точное число дней в месяцах, число дней в году принимается за 360
Точное число дней в месяцах, число дней в году принимается за 365
Европейский метод: 12 месяцев по 30 дней в каждом
Пример
An asset was acquired on 2020-02-01 at a cost of 2000 currency units. The end date of the first settlement period was 2020-12-31. The salvage value of the asset at the end of its depreciable life will be 10 currency units. The rate of depreciation is 0.1 (10%) and the year is calculated using the US method (Basis 0). Assuming degressive depreciation, what is the amount of depreciation in the fourth depreciation period?
=AMORDEGRC(2000; «2020-02-01»; «2020-12-31»; 10; 4; 0.1; 0) returns a depreciation amount of 163 currency units.
Be aware that Basis 2 is not supported by Microsoft Excel. Hence, if you use Basis 2 and export your document to XLSX format, it will return an error when opened in Excel.
АСЧ
Возвращает процент амортизации, рассчитанной методом «суммы чисел».
Эта функция служит для расчёта величины амортизации для одного периода полной амортизации для объекта. При амортизации методом «суммы чисел» величина амортизации уменьшается на фиксированную сумму каждый период.
Синтаксис
АСЧ(Стоимость; Ликвидационная стоимость; Время эксплуатации; Период)
Стоимость : начальная стоимость актива.
Ликвидационная стоимость представляет собой стоимость актива после амортизации.
Время эксплуатации : период, в течение которого стоимость актива амортизируется.
Период : период, для которого рассчитывается амортизация.
Пример
Видеосистема, начальная стоимость которой составляет 50000 денежных единиц, амортизируется ежегодно в течение 5 лет. Ликвидационная стоимость будет равна 10000 денежных единиц. Требуется рассчитать амортизацию за первый год.
=АСЧ(50000; 10000; 5; 1) =13 333,33 денежных единиц. Величина амортизации за первый год – 13 333,33 денежных единиц.
Чтобы получить общее представление о процентных ставках амортизации за период, необходимо определить таблицу амортизации. Вводя различные формулы расчёта амортизации, доступные в Calc LibreOffice , можно выбрать наиболее подходящий метод амортизации. Введите значения в таблицу.
PyOOCalc — Библиотека для генерации отчетов, счетов Libre/Open Office Calc на Python
Порой возникают задачи, когда необходимо формировать отчеты и прочие документы. В моей практике данная задача возникала не раз.
Проекты, в которых возникала данная задача:
- складской учет
- учет объектов недвижимости
- документооборот
Все эти трудности привели к поиску более простых решений. Я посмотрел список поддерживаемых языков и увидел Python, что меня очень заинтересовало, т.к. уже довольно активно его начал использовать. Нашел простенький пример, попробовал — работает. Проверил на разных версиях и — о, чудо! Без малейших правок один и тот же скрипт работает на разных дистрибутивах и версиях офиса.
Те, кто уже сталкивался с подобными задачами — знает, что ковыряние API дело не благодарное. Посему первым делом я принялся искать уже готовые библиотеки. Наиболее интересным вариантом для меня оказался PyOO. Данная библиотека содержит довольно обширный функционал и проста в использовании. В ней реализованы функции от создания/сохранения документов до объединения ячеек и создания диаграмм. Но… Ни одна из найденных мной библиотек не обеспечивала необходимый мне функционал. Много вкусного, но лишнего для меня.
Вернемся к изначальной задаче: необходимо формировать отчеты и счета. Как уже упоминалось выше, шаблоны проще всего создавать в офисном пакете. Ведь их смогут создавать и конечные пользователи без участия разработчиков.
С созданием шаблонов все ясно, но как знать, куда необходимо вставлять данные. Можно использовать индексы (колонка, строка) или имя (например: «E5»).
А что если для одного отчета необходимо создать несколько шаблонов для генерации документа. Например один шаблон для книжного расположения, второй для альбомного. Но нет никакой гарантии, что и в одном и в другом шаблоне одно и то же поле (данные) необходимо вставить по одному и тому же имени (или индексу) ячейки. А это означает, что нужно хранить данные о расположении полей в шаблоне. Но есть более простое универсальное решение. Данную задачу можно реализовать через NamedRange.
NamedRange — это имя ячейки или области на листе. При этом NamedRange уникальный в рамкой одной книги (документа).
Еще одна необходимая функция для решения данной задачи — вставка строк. При этом вставка должна быть с учетом сохранения форматирования строки (шрифт, объединенные ячейки и т.п.).
Исходя из вышеперечисленных задач, был разработан модуль на Python, который реализует следующие функции:
- Открытие и создание документов
- Сохранение документов разных форматах (поддерживаемых Libre/Open Office)
- Вставка/удаление листов
- Вставка строк с копированием форматирования
- Вставка/получения значения имен (NamedRange)
- Вставка/получения значения ячеек по имени (индексу)
import pyoocalc # open document doc = pyoocalc.Document() file_name = os.getcwd() + "/example.ods" doc.open_document(file_name) # Get document fields fields = doc.fields() # Get field "HEADER" field = fields.field("HEADER") print ("Document header is: " + str(field.is_null())) # Set values field = fields.field("TABLE_NAME") field.set_value("Test table name") print ("New table name is: " + field.value()) # Insert 5 rows field1 = fields.field("FIELD_1") num_rows = 5 step = 2 if num_rows > 0: field1.insert_rows(num_rows=num_rows-1, step=step, columns_to_copy=200) for i in range(1, num_rows + 1): field1.set_value("F1." + str(i), 0, i * step - (step - 1)) # Set value="value1" at column=1, row=1 (B1) sheet = doc.sheets().sheet(0) sheet.set_cell_value_by_index(1, 0, "value1") print (sheet.cell_value_by_index(1, 0)) Уверен, что данная задача возникала не только у меня. Рад, если кому-то пригодится.
Пример документа с данными требованиями и пример использования библиотеки находятся в каталоге examples.