Электронная таблица на платформе 1С
(Версия 2.0)
Основные отличия новой версии от предыдущей (кратко).
-
Электронная таблица стала трех мерной (как Excel) - появились листы. Количество листов в электронной таблице ограниченно только возможностями платформы. Листы электронной таблицы можно добавлять, переименовывать, удалять. Соответственно в формулах теперь можно обращаться к ячейкам расположенным на других листах.
-
При вставке новой или удалении существующей области электронной таблицы в сдвигаемых областях и зависимых от сдвигаемых областей автоматически происходит изменение адресов в формулах (как в Excel). Это исключает ручное изменение формул при добавлении или удалении областей (строк, колонок или прямоугольных областей).
-
При копировании данных области, или вырезки области с последующей вставкой, формулы в вырезаемой или копируемой областях также изменяются.
-
В формулах при задании адресов ячеек или областей электронной таблицы можно применять символ «$» запрещающий изменение части адреса (как в Excel) при выполнении операций, указанных в п.2 и п.3.
-
При переименовании листа электронной таблицы происходит также автоматическое изменение формул, которые обращаются к ячейкам данного листа.
-
Переработан механизм расчета. Теперь формулы не анализируются каждый раз при необходимости пересчета. Исполняемый код хранится в подготовленном виде в каждой ячейки с формулами.
Электронная таблица — программное обеспечение, позволяющее проводить вычисления с данными, представленными в виде двумерных или трехмерных массивов. Электронные таблицы (ЭТ) представляют собой удобный инструмент для автоматизации вычислений. Многие расчёты, в частности в области бухгалтерского учёта, выполняются в табличной форме: балансы, расчётные ведомости, сметы расходов и т. п. Использование формул в электронных таблицах позволяет представить взаимосвязь между различными параметрами некоторой системы. Решения многих вычислительных задач, которые раньше можно было осуществить только с помощью программирования, стало возможно реализовать через моделирование в электронной таблице.
Примерами электронных таблиц являются такие программы как: SuperCalc, Microsoft MultiPlan, Quattro Pro, Lotus 1-2-3, Microsoft Excel, и т.д.
Функционал электронной таблицы для программ на платформе 1С реализован на основе табличных документов. Функционал реализован в виде обработки.
Далее по тексту: «Электронная таблица» - «Электронная таблица 2.0 реализованная средствами 1С».
Большую часть формы обработки занимают листы (закладки) с табличными документами, которые выполняет роль электронной таблицы. Листы могут быть добавлены, удалены или переименованы. Ограничение по количеству листов определяется возможностью платформы. Над табличным документом располагается панель основных свойств ячейки с реквизитами для задания имени области, типа ячеек документа («значение» или «формула»), а также типа значений данных содержащихся в ячейках (см. Рис. ниже).
Оформление и другие параметры ячейки электронной таблицы доступны по правой кнопке мыши. Это типовой набор свойств ячейки табличного документа.
Формулы электронной таблицы – основные тезисы.
В формулах электронной таблицы можно использовать любые языковые конструкции, процедуры и функции 1С. О способах их применения будет рассказано ниже.
Для обращения в формулах электронной таблицы к другим ячейкам или группе ячеек электронной таблицы (табличного документа) применяется конструкция вида:
&[ИмяЛиста]RНомерСтрокиCНомерКолонки - для обращения к значению другой ячейки табличного документа. Если обращение в формуле идет к ячейке, расположенной на том же листе что и формула, то имя листа допускается опускать. В имени листа можно использовать предопределенное ключевое слово «ЭтотЛист». Т.е. использование синтаксиса обращения к ячейке электронной таблицы &[ЭтотЛист]RНомерСтрокиCНомерКолонки идентично синтаксису &RНомерСтрокиCНомерКолонки.
&”ИмяЛиста”!RНомерСтрокиCНомерКолонки - существует второй вариант синтаксиса для обращения к ячейке расположенной на другом листе в стиле Excel.
&[ИмяЛиста]RНомерСтрокиЛевоВерхCНомерКолонкиЛевоВерх: RНомерСтрокиПравоНизCНомерКолонкиПравоНиз – для получения массива значений заданной области ячеек табличного документа. Порядок значений в массиве сверху вниз и слева направо заданной области.
&”ИмяЛиста”!RНомерСтрокиЛевоВерхCНомерКолонкиЛевоВерх: RНомерСтрокиПравоНизCНомерКолонкиПравоНиз – второй вариант синтаксиса для обращения к области ячеек расположенной на другом листе в стиле Excel.
&[ИмяЛиста]R$НомерСтрокиC$НомерКолонки - при задании адреса ячейки или области табличного документа можно применять символ «$» препятствующий изменению части адреса при добавлении или удалении областей, вставках вырезаемых или копируемых областей.
Также допустимо обращаться к ячейкам табличного документа по имени именованной области:
&(ИменованнаяЯчейка) - для обращения к значению другой ячейки табличного документа. Где «ИменованнаяЯчейка» - имя именованной одной ячейки табличного документа.
&(ИмяОбластиЯчеек) - для получения массива значений именованной области ячеек табличного документа. Порядок значений в массиве сверху вниз и слева направо заданной области.
Примеры обращения к ячейкам табличного документа:
-
Сумма двух ячеек табличного документа. Обращение к ячейкам задано в виде номера строки и колонки. Ячейки расположены на том же листе что и сама формула.
=&R2C3+&R2C6
-
Сумма двух ячеек табличного документа. Обращение к ячейкам задано в виде номера строки и колонки. Ячейки, к которым обращается формула, расположены на других листах электронной таблицы.
=&[Лист1]R2C3+&[Лист2]R2C6
Или другой вариант синтаксиса
=&"Лист1"!R2C3+&"Лист2"!R2C6
-
Сумма всех ячеек области табличного документа. Обращение к области ячеек задано в виде номеров строк и колонок. Область ячеек табличного документа расположена на том же листе что и сама формула.
= Сумм(&R2C3:R10C11)
Или другой вариант синтаксиса
= Сумм(&[ЭтотЛист]R2C3:R10C11)
-
Сумма всех ячеек области табличного документа. Обращение к области ячеек задано в виде номеров строк и колонок. Область ячеек табличного документа, к которой обращается формула, расположена на другом листе электронной таблицы.
= Сумм(&[Лист2]R2C3:R10C11)
Или другой вариант синтаксиса
= Сумм(&"Лист2"!R2C3:R10C11)
-
Сумма двух ячеек табличного документа. Обращение к одной из ячеек задано в виде номера строки и колонки, а другой в виде имени. Именованная область может располагаться на другом листе электронной таблицы нежели сама формула.
=&R2C3+&(Слагаемое2)
-
Сумма всех ячеек именованной области табличного документа.
= Сумм(&(МояОбластьЧисел))
Определение и удаление именованных областей табличного документа.
Для работы с именованными областями табличного документа на панели основных свойств ячейки расположен специальный реквизит (см. Рис. ниже). С помощью данного реквизита можно задать имя текущей выделенной области табличного документа, удалить текущее имя именованной области, перейти к именованной области из списка. Имена именованных областей уникальны в приделах всей электронной таблицы. Т.е. не может быть нескольких областей с одинаковым именем на одном и том же или разных листах электронной таблицы.
Для задания имени области ячеек или одной ячейки необходимо сначала выделить ту область или ячейку табличного документа электронной таблицы, которой мы хотим присвоить имя. Затем в реквизит вносим имя области.
При перемещении по ячейкам табличного документа, или выделении области в данном реквизите отображается текущее имя. Если имя не задано, то отображается адрес ячейки или области в виде номеров строк и колонок.
Для быстрого перехода к именованной области ячеек или ячейке необходимо вызвать список именованных областей и выбрать нужное имя из списка. При выборе конкретной области из списка она становится текущей в табличном документе.
Выбранная именованная область становится текущей в табличном документе.
Для удаления имени области ячеек или ячейки сначала нужно чтоб данная область или ячейка стала текущей. Для этого можно воспользоваться вышеописанным методом. Далее нажимаем крестик расположенный справа от реквизита именования областей. Имя области будет удалено.
Тип ячейки, значение ячейки и тип значения ячейки
Тип ячейки, значение ячейки и тип значения ячейки нужно рассматривать вместе, т.к. значения каждого из этих реквизитов оказывают влияние друг на друга.
Тип ячейки табличного документа может принимать одно из двух значений: «Значение» или «Формула». Тип ячейки определяет, как будет задаваться значение ячейки, путем ввода значения пользователем, или путем расчета формулы. При изменении типа ячейки на значение «Формула» появляется возможность в реквизит значения (соседний реквизит формы) ввести расчетную формулу.
Тип значения ячейки табличного документа позволяет задать типы значений, которые пользователь может ввести в ячейку. Доступны простые типы данных, поддерживаемые 1С, а также все типы данных (метаданные) текущей конфигурации. При изменении типа изменяется поведение реквизита для задания значения ячейки табличного документа. Ввод данных будет ограничен указанными типами значений.
Также возможно задать авто тип. Это значит, что тип значения необходимо будет выбрать перед вводом самого значения. Авто тип также применим к ячейкам с формулами. В этом случае тип ячейки будет установлен такой, каким будет результат вычисления формулы.
Если для ячейки установить авто тип, то перед занесением данных в ячейку необходимо будет сначала указать тип вводимого значения (см. Рис. ниже).
Использование формул для расчета значений ячейки
Как было сказано ранее, значение ячейки может быть вычислено по формуле. В формулах электронной таблицы можно использовать любые языковые конструкции, процедуры и функции 1С.
Среди способов написания алгоритма для вычисления значения ячейки можно выделить два основных:
-
Написание алгоритма возвращающего значение ячейки как результат выполнения имеющихся в платформе 1С и конфигурации функций языка 1С.
-
Написание алгоритма как конструкций языка 1С. Для получения значения ячейки требуется явное присвоение данных в алгоритме предопределенной переменной с именем «Результат».
Способ задания формулы в виде алгоритма возвращающего значение ячейки как результат выполнения имеющихся в платформе 1С и конфигурации функций языка 1С, самый простой, и аналогичен тому как это, например, делается в MS Excel. Формула может, как и в MS Excel, начинаться со знака равенства «=». Но это не является обязательным.
Приведем пример (см. Рис. ниже).
Если формула длинная, то для удобства написания и редактирования можно открыть отдельное окно.
В некоторых случаях, удобнее или нагляднее составить алгоритм с помощью конструкций языка 1С. В этом случае языковая конструкция не возвращает значений, поэтому расчетное значение, которое мы хотим присвоить ячейки табличного документа, необходимо присвоить самостоятельно путем присвоения значения предопределенной переменной «Результат».
Приведем пример (см. Рис. ниже).
Пересчет формул ячеек
В случае использования в формулах электронной таблицы данных из самой таблицы (см. п. «Формулы электронной таблицы – основные тезисы»), пересчет зависимых ячеек с формулами производится автоматически.
Также в качестве формул электронной таблицы могут выступать функции, которые получают данные из данных учетной системы. При изменении таких данных электронная таблица не знает об изменении данных, и может возникнуть необходимость пересчитать формулы электронной таблицы. Например, при использовании бухгалтерских функций. Или, например, при изменении параметров выполнения бухгалтерских функций. Для выполнения пересчета всех формул электронной таблицы или только части ячеек, на командной панели расположены команды (кнопки) «Выполнить пересчет всех листов», «Выполнить пересчет текущего листа» и «Выполнить пересчет текущей области».
Команда «Выполнить пересчет всех листов» производит расчет формул во всех ячейках всех листов электронной таблицы.
Команда «Выполнить пересчет текущего листа» производит расчет формул во всех ячейках текущего листа электронной таблицы.
Команда «Выполнить пересчет текущей области» производит расчет формул в выделенной области ячеек или одной текущей ячейки табличного документа. Зависимые ячейки также пересчитываются.
Сохранение электронной таблицы в файл, открытие ранее сохраненной электронной таблицы из файла.
Для сохранения в файл и открытия электронной таблицы из файла на командной панели расположены команды (кнопки) «Сохранить в файл» и «Открыть из файла». При выборе данных команд открываются стандартные диалоги сохранения и открытия файлов. Для сохранения электронной таблицы в файл используется формат «MMxl» (Multi Mxl). Одноименные команды присутствую и в меню.
Необходимо обратить внимание что в диалоге открытия файла необходимо выбрать тип файла «Электронная таблица (*.MMxl)».
Сохранение в файл текущего листа электронной таблицы, загрузка данных из файла в текущий лист электронной таблицы.
Также можно сохранить в файл формата «Mxl» текущий лист электронной таблицы. Или загрузить данные из файла формата «Mxl» в текущий лист электронной таблицы.
Для сохранения текущего листа электронной таблицы в файл необходимо выбрать пункт меню «Сохранить как…».
В появившемся диалоговом окне «Сохранить файл» нужно выбрать тип файла «Лист электронной таблицы (*.Mxl)». После задания имени файла и нажатия кнопки «Сохранить» текущий лист электронной таблицы будет сохранен в стандартный формат табличного документа.
Для загрузки данных в текущий лист электронной таблицы из файла формата «Mxl» необходимо выбрать команду на форме или одноименный пункт меню «Открыть из файла». В диалоговом окне «Открыть файл» необходимо выбрать тип файла «Лист электронной таблицы (*.Mxl)». После выбора файла с расширением «Mxl» и нажатия кнопки «Открыть» данные из выбранного файла будут загружены в текущий лист электронной таблицы.
Добавление, удаление, переименование листов электронной таблицы.
По умолчанию при открытии обработки реализующей функционал электронной таблицы присутствует только несколько листов. Как ранее было указано выше количество листов в электронной таблице ограниченно только возможностями платформы. Для выполнения операций над листами электронной таблицы в меню присутствуют пункты «Добавить новый лист», «Переименовать текущий лист», «Удалить текущий лист». Содержание пунктов меню прозрачно и пояснений не требует.
ВАЖНО! При удалении листа электронной таблицы имя листа во всех формулах, которые ссылаются на данный лист, будет изменено на «#ЛИСТ!». Это позволяет понять какие формулы ссылались на ячейки удаленного листа.
Добавление новых, удаление существующих областей электронной таблицы.
Как было указано ранее, при вставке новой или удалении существующей области электронной таблицы в сдвигаемых областях и зависимых от сдвигаемых областей автоматически происходит изменение адресов в формулах (как в Excel). Это исключает ручное изменение формул при добавлении или удалении областей (строк, колонок или прямоугольных областей). Для выполнения этих операций предназначены команды на командной панели и одноименные пункты меню «Добавить область» и «Удалить область». В формулах при задании адресов ячеек или областей электронной таблицы можно применять символ «$» запрещающий изменение части адреса (как в Excel).
ВАЖНО! При использовании команд контекстного меню «Раздвинуть» и «Удалить» автоматическое изменение адресов в формулах, ссылающихся на сдвигаемые области, не происходит.
Копирование, вырезание, вставка областей электронной таблицы.
При копировании данных области, или вырезки области с последующей вставкой, формулы в вырезаемой или копируемой областях также изменяются. Для выполнения данных операций предназначены команды на командной панели и одноименные пункты меню «Вырезать», «Копировать» и «Вставить». В формулах при написании адресов ячеек или областей электронной таблицы можно применять символ «$» запрещающий изменение части адреса (как в Excel).
ВАЖНО! При использовании команд контекстного меню «Вырезать», «Копировать» и «Вставить» автоматическое изменение адресов в формулах, ссылающихся на сдвигаемые области, не происходит.
Еще раз хотел бы обратить внимание, что в данной электронной таблице:
-
в качестве значений ячеек могут быть любые типы данных доступные в конфигурации.
-
в формулах ячеек можно использовать любые процедуры, функции языка 1С, любые процедуры и функции общих модулей, модулей менеджеров, объектов и т.п.
-
Также в формулах можно использовать любые языковые конструкции 1С.
Никто не пытался Excel заменить, но некоторые похожие функции все-таки добавлены.
В актуальной версии доступны функции:
Сумм, Максимум, Минимум, СрЗнач, ДлСтр, Заменить, Значен, ЛевСимв, Повтор, Подставить, Поиск, ПравСимв, Прописн, ПСТР, Рубль, СжПробелы, Строчн, Сцепить, ВПР, Выбор, ГПР.
Обработка (Версия 2.0) проверена на платформе: 8.3.18.1334
Полная версия инструкции по работе с обработкой доступна для скачивания - Электронная таблица средствами 1С (Версия 2.0) (обработка, инструкция, пример файла электронной таблицы).
Предыдущую обработку от 2019 года также оставляю доступной для скачивания - Электронная таблица средствами 1С (обработка, руководство пользователя, пример файла электронной таблицы)
P.S. Критика, найденные ошибки, недочеты - приветствуются.
