Меню

Как сложить столбцы или строки чисел в Open Office Calc



Формулы OpenOffice Calc

При задании формул наряду с основными арифметическими операциями, OpenOffice Calc предоставляет множество специальных функций, которые можно ввести в интерактивном режиме при помощи Автопилота функций. OpenOffice Calc поддерживает, в частности, многие статистические методы: от регрессионных расчетов до доверительных интервалов. Особенно интересна возможность изменять отдельные параметры в вычислениях, зависящих от многих факторов, и прослеживать, как это влияет на результат. Это так называемые расчеты «что было бы, если». Например, при расчете кредита путем простого изменения периода, процентной ставки или сумм выплаты можно сразу же увидеть, как изменяются остальные факторы.

Как и все другие средства работы с электронными таблицами, OpenOffice Calc позволяет использовать относительные и абсолютные ссылки. Перед каждым значением, которое должно использоваться как абсолютное, ставится знак доллара $ . Для превращения текущей ссылки, в которой находится курсор в строке ввода, из относительной в абсолютную и наоборот, следует использовать комбинацию клавиш Shift+F4 .

Практически все функции (за исключением математических и некоторых статистических) в программе OpenOffice Calc локализованы, т. е. используют русскоязычные имена. К таковым относятся и все функции из раздела Дата&Время (однако функция ПАСХАЛЬНОЕВОСКРЕСЕНЬЕ() определяет дату католической, а не православной Пасхи).

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

Для задания суммы чисел, находящихся в столбце или строке следует использовать кнопку . В ячейке появится формула вида =СУММ(. ) . Программа пытается догадаться, каков интервал суммирования. Если вас не устраивает предложенный диапазон, то выделите левой кнопкой мыши требуемую область ячеек так, чтобы вокруг нее появилась красная рамка. То же самое можно сделать и вручную, указав диапазон ячеек в строке формул. Отметим, что пустые ячейки при суммировании трактуются как содержащие нулевые значения.

Познакомимся поподробнее с функцией ДАТА , обеспечивающей ввод дат в электронную таблицу. OpenOffice Calc хранит такие данные в виде чисел, но отображает в ячейке в формате даты, выравнивая их (аналогично числам) по правому краю. Конечно, можно отформатировать ячейку, содержащую дату, и в числовом формате. Синтаксис функции — ДАТА(Год; Месяц; День) . Год — целое число от 1600 до 3000, при вводе от 0 до 29 добавляется 2000, а при вводе числа от 30 до 99 — 1900. Месяц — число от 1 до 12, задающее номер месяца. День — число от 1 до 31, которое устанавливает день месяца. Когда значения месяца и дня больше допустимых, они пересчитываются на следующую позицию (год, месяц) с переполнением. Формула =ДАТА(00;12;31) дает 31.12.2000, а при вводе =ДАТА(00;13;31) получится дата 31.01.2001.

В программе OpenOffice Calc можно просто ввести даты в формате «месяц.число.год» (без указания кавычек, иначе ввод будет интерпретирован как текст), например, «5.17.2» для 17 мая 2002 года. В этом случае любой ввод со значениями, выходящими за границы допустимых, трактуется не как дата, а как текст. Функция ТДАТА() возвращает дату и время в соответствии с системным временем компьютера, которые обновляются при каждом пересчете документа.

Пример

Вычислим количество дней, часов, минут и секунд, прошедших с 0 часов некоторой даты. Откройте новый документ электронной таблицы и введите в ячейку B1 дату, например, «1.17.84». В ячейку B3 введите формулу =ТДАТА()-A1 , которая по умолчанию отформатируется как дата. Поскольку нам нужно получить разницу между двумя датами в виде числа дней, то необходимо отформатировать ячейку B3 как целое число. Введите остальные формулы: в B4 =B3*24 для часов, в B5 =B4*60 для минут и в B6 =B5*60 для секунд.

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

Для отображения формул, примененных при этих вычислениях, в столбец D помещена функция ФОРМУЛА() , аргументом которой является адрес соответствующей ячейки.

При создании сложных таблиц удобно использовать именованные ячейки. Если нужно составить, к примеру, формулу для вычисления НДС, то она будет более понятной в виде «= Сумма * Размер_налога», чем в форме «= A5 * B12», где ячейка A5 содержит данные о сумме, а ячейка B12 — о размере налога.

Для того чтобы задать имя области (ячейки), необходимо эту область сначала выделить и с помощью пункта Имена — Задать меню Вставка (или при помощи комбинации клавиш Ctrl+F3 ) вызвать диалоговое окно Присвоить имя. Имя должно начинаться с буквы, отличаться от стандартных имен ячеек и не может содержать пробелов. После ввода имени следует нажать кнопку Добавить. В этом же диалоге можно задать имена других областей, введя сначала имя, а затем выделив ячейки листа, которые должны получить это имя. В нем можно присвоить имена даже часто используемым формулам или элементам формул.

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

Читайте также:  Корпоративный жаргон в США почему Good job в американских компаниях иногда означает Все фигня переделывай

Для вычисления годовых процентов создайте небольшую таблицу, в которой из значений капитала (Капитал), количества лет (Количество_лет) и процентной ставки (Ставка) получается результат — сумма процентов за год (Процент). Формула для вычисления результата имеет вид

Ячейкам лучше всего дать имена, чтобы формула имела осмысленный вид. В примере сначала вычисляется, какова будет сумма процентов при вложенном капитале 150 000 р. и процентной ставке 7,5% за год.

Теперь, считая, что процентная ставка (7,5%) и количество лет (1) не изменяются, определим, какого размера должен быть капитал, чтобы давать определенные годовые проценты, скажем, 15 000 р. Выделите ячейку Проценты и выберите в меню Сервис пункт Подбор параметра. Появится одноименное окно, в поле Ячейка формул которого уже указана нужная ячейка.

Поместите курсор в поле диалога изменяемая ячейка. В таблице выделите ячейку, значение которой изменяется, т. е. B2. В текстовом поле Конечное значение диалога введите желаемый результат формулы и нажмите кнопку OK. Если параметр был успешно подобран, то отобразится результат, который можно занести в таблицу.

Источник

Openoffice электронная таблица сумма

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

Функции IF возвращает одно значение, если заданное условие при вычислении дает значение TRUE (ИСТИНА), и другое значение, если FALSE (ЛОЖЬ). Функция IF используется при проверке условий для значений и формул.

IF (Лог_выражение; Значение_если_истина; Значение_если_ложь)

Предположим, что вам нужно подсчитать в рабочей таблице комиссионные по результатам продаж какого-либо товара. Если продавец продал продукции более, чем на $100 000, то ставка его комиссионных составляет 7,5%, если меньше, то 5%. Без использования функции вам пришлось бы создавать две разные формулы и правильно употреблять их для каждого значения объема продаж. Ниже приведена функция, которая позволяет рассчитать комиссионные с учетом суммы продаж.

=IF (А1 A 1*0,05; A 1*0,075), где

IF – функция, которая проверяет, выполняется ли условие, и возвращает одно значение, если оно выполняется, и другое значение, если нет;

A 1*0,05 – значение, которое возвращается, если условие выполняется;

A 1*0,075 – значение, которое возвращается, если условие не выполняется.

Таким образом, если значение ячейки А1 (сумма продаж) будет меньше 100000 (условие выполняется) программа умножит значение ячейки А1 на 0,05 (ставка комиссионных), если же сумма продаж будет больше 100000 (условие не выполняется), программа умножит значение ячейки А1 на 0,075.

Откройте окно Мастер функций , выберите категорию Логические функции . Просмотрите список функций, ознакомьтесь с их описанием.

Перейдите на Лист3 . При отсутствии исходного файла на Листе3 создайте таблицу табл.3.

Источник

Как сложить столбцы или строки чисел в Open Office Calc

Сложение строк или столбцов чисел — одна из наиболее распространенных операций, выполняемых в программах для работы с электронными таблицами, таких как OpenOffice Calc. Чтобы упростить выполнение этой задачи, Calc включает встроенную формулу, называемую SUM функции.

Инструкции в этой статье относятся к OpenOffice Calc v. 4.1.6.

Функция OpenOffice Calc SUM

Два способа входа в эту функцию включают:

  • Посмотрите на график SUM функциональная кнопка быстрого доступа — это греческая заглавная буква сигма (Σ) расположен рядом с строка ввода (как строка формул в Excel).
  • Добавление SUM функцию на лист с помощью диалогового окна мастера функций. Диалоговое окно можно открыть, выбрав Мастер функций расположен рядом с кнопкой Sigma на строка ввода.

Towfiqu Photography / Getty Images

Преимущества ярлыков и диалоговых окон

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

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

Синтаксис и аргументы функции SUM

Синтаксис функции относится к структуре функции и включает имя функции, скобки и аргументы.

Синтаксис для SUM функция:

= СУММ (число 1; число 2; … число 30)

номер 1; номер 2; … номер 30 — данные, суммируемые функцией. Аргументы могут содержать:

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

Функция может добавить не более 30 номеров.

Что игнорирует функция СУММ

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

По умолчанию текстовые данные в Calc выровнены по левому краю в ячейке — как видно с числом 160 в ячейке. A2 на изображении выше — числовые данные по умолчанию выравниваются по правому краю.

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

Ручной ввод функции СУММ

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

Читайте также:  Критерии истины таблица с примерами

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

Суммирование данных с помощью кнопки SUM

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

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

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

Ниже перечислены шаги, используемые для ввода функции СУММ в ячейку. A7 как показано ниже.

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

Нажмите SUM кнопка рядом со строкой ввода.

Это SUM функция должна быть введена в активную ячейку — функция должна автоматически ввести ссылку на ячейку A6 как числовой аргумент.

Чтобы изменить диапазон ссылок на ячейки, используемых для числового аргумента, используйте указатель мыши, чтобы выделить диапазон A1 в A6.

Press вводить для завершения функции.

Ответ 577 должен отображаться в ячейке A7. Когда вы выбираете ячейку A7, полная функция = СУММ (A1: A6) появляется в строка ввода над листом.

Добавить числа с помощью диалогового окна функции SUM в Calc

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

  • Выбор Мастер функций на строка ввода над листом.
  • Прессование Ctrl + F2.

Преимущества диалогового окна

Преимущества использования диалогового окна:

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

Когда данные для суммирования не расположены в непрерывном диапазоне, ссылки на ячейки, такие как A1, A3 и B2: B3, можно легко ввести как отдельные числовые аргументы в диалоговое окно с помощью указателя, что включает в себя щелчок по выбранным ячейкам с помощью мышь, вместо того, чтобы вводить их. Это не только упрощает указание, но и помогает уменьшить количество ошибок в формулах, вызванных неправильными ссылками на ячейки.

Пример функции СУММ

Ниже перечислены шаги, используемые для ввода SUM функция в ячейке A7 как показано на изображении в шаге 15. В инструкциях используется SUM диалоговое окно функции для ввода значений, расположенных в ячейках A1, A3, A6, B2и B3 как числовые аргументы функции.

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

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

Выберите Сток выпадающий список и выберите Математический чтобы увидеть список математических функций.

Под функцияВыберите SUM из списка функций.

Выбрать Следующая.

Выбрать Количество 1 в диалоговом окне, если необходимо.

Выбрать ячейку A1 на листе, чтобы ввести ссылку на эту ячейку в диалоговое окно.

Выбрать Количество 2 в диалоговом окне.

Выбрать ячейку A3 на листе, чтобы ввести ссылку на эту ячейку.

Выбрать Количество 3 в диалоговом окне.

Выбрать ячейку A6 на листе, чтобы ввести ссылку на эту ячейку.

Выбрать Количество 4 в диалоговом окне.

Выделить ячейки B2 и B3 на листе, чтобы ввести этот диапазон.

Выбрать OK , чтобы закрыть диалоговое окно и вернуться на рабочий лист.

Номер 695 должен появиться в камере A7 — так как это сумма чисел, находящихся в ячейках A1 в B3.

Когда вы выбираете ячейку A7. полная функция = СУММ (A1; A3; A6; B2: B3) отображается в строке ввода над рабочим листом.

Источник

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

В этом разделе показано, как использовать классы в пакете Open XML SDK 2.5 для Office для вычисления суммы смежных ячеек в электронной таблице программными средствами.

Для компиляции кода, представленного в этом разделе, требуются следующие директивы сборки.

Получение объекта SpreadsheetDocument

В пакете Open XML SDK класс SpreadsheetDocument представляет пакет документа Excel. Чтобы открыть документ Excel и работать с ним, создайте экземпляр класса SpreadsheetDocument из документа. После создания экземпляра вы сможете получить доступ к основной части книги, содержащей листы. Текст в документе представляется в пакете в виде XML с помощью разметки SpreadsheetML.

Для создания экземпляра класса из документа вызывается один из методов Open. Доступно несколько методов с разными сигнатурами. В примере кода в этой статье используется метод Open(String, Boolean) с сигнатурой, требующей два параметра. Первый параметр — это строка полного пути, представляющая открываемый документ. Второй параметр — это значение true или false, которое указывает, будет ли файл открыт для редактирования. Если этот параметр имеет значение false, любые изменения, внесенные в документ, не будут сохранены.

Код, который вызывает метод Open, показан в следующем операторе using:

Оператор using представляет рекомендуемую альтернативу типичной последовательности методов .Open, .Save, .Close. Он гарантирует, что метод Dispose (внутренний метод, используемый Open XML SDK для очистки ресурсов) вызывается автоматически при достижении закрывающая фигурной скобки. Блок, который следует за оператором using, устанавливает область объекта, который создается или именуется в операторе using. В этом случае это document.

Базовая структура документа SpreadsheetML

Базовая структура документа SpreadsheetML состоит из элементов Sheets и Sheet, ссылающихся на листы в книге. Для каждого листа создается отдельный XML-файл. Например, элемент SpreadsheetML для книги с двумя листами MySheet1 и MySheet2 находится в файле Workbook.xml и показан в следующем примере кода:

Читайте также:  Таблица результатов фнл 2015 2016

XML-файл листа содержит один или несколько элементов уровня блока, например SheetData. Элемент sheetData представляет таблицу ячеек и содержит один или несколько элементов Row. Элемент row содержит один или несколько элементов Cell. Каждая ячейка содержит элемент CellValue, представляющий значение ячейки. Например, элемент SpreadsheetML для первого листа книги, содержащего только значение 100 в ячейке A1, находится в файле Sheet1.xml и показан в следующем примере кода:

С помощью Пакет SDK 2.5 Open XML можно создать структуру и содержимое документа, использующие строго типизированные классы, соответствующие элементам SpreadsheetML. Эти классы можно найти в пространстве имен DocumentFormat.OpenXML.Spreadsheet. В следующей таблице перечислены имена классов, соответствующие элементам workbook, sheets, sheet, worksheet и sheetData.

Элемент SpreadsheetML Класс пакета Open XML SDK 2.5 Описание
книга DocumentFormat.OpenXml.Spreadsheet.Workbook Корневой элемент основной части документа.
sheets DocumentFormat.OpenXml.Spreadsheet.Sheets Контейнер для структур уровня блока, таких как sheet, fileVersion, и других, указанных в спецификации ISO/IEC 29500.
лист DocumentFormat.OpenXml.Spreadsheet.Sheet Лист, указывающий на файл определения листа.
лист DocumentFormat.OpenXml.Spreadsheet.Worksheet Файл определения лист с данными листа.
sheetData DocumentFormat.OpenXml.Spreadsheet.SheetData Таблица ячеек, сгруппированных по строкам.
row DocumentFormat.OpenXml.Spreadsheet.Row Строка в таблице ячеек.
c DocumentFormat.OpenXml.Spreadsheet.Cell Ячейка в строке.
v DocumentFormat.OpenXml.Spreadsheet.CellValue Значение ячейки.

Как работает пример кода

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

Затем код открывает файл для редактирования в качестве пакета документа SpreadsheetDocument для чтения и записи, а также возвращает указанный объект Worksheet. Код возвращает индекс строки для первой и последней ячейки в непрерывном диапазоне ячеек путем вызова метода GetRowIndex. Код возвращает имя столбца для первой и последней ячейки в непрерывном диапазоне путем вызова метода GetColumnName.

Для каждого объекта Row в непрерывном диапазоне ячеек код выполняет итерацию по каждому объекту Cell и определяет, находится ли столбец ячейки в непрерывном диапазоне ячеек путем вызова метода CompareColumn. Если ячейка находится в непрерывном диапазоне ячеек, код добавляет значение ячейки к сумме. Затем код получает объект SharedStringTablePart, если таковой существует. Если данный объект не существует, код создает его с помощью метода AddNewPart. Результат вставляется в объект SharedStringTablePart путем вызова метода InsertSharedStringItem.

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

Для получения индекса строки код передает параметр, представляющий имя ячейки, и создает новое регулярное выражение в соответствии с именем строки из имени ячейки. Дополнительные сведения о регулярных выражениях см. в справочнике Элементы языка регулярных выражений. Код получает индекс строки путем вызова метода Regex.Match и возвращает индекс строки.

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

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

Для вставки SharedStringItem код передает параметр, представляющий текст, который необходимо вставить в ячейку, и параметр, представляющий объект SharedStringTablePart электронной таблицы. Если объект ShareStringTablePart не содержит объект SharedStringTable, код создает его. Если текст в объекте ShareStringTable уже существует, код возвращает индекс объекта SharedStringItem, представляющего текст. Если текст не существует, создается новый объект SharedStringItem, представляющий текст. Затем код возвращает индекс объекта SharedStringItem, представляющего текст.

Последним этапом является вставка ячейки в лист. Для этого код передает параметры, представляющие имя столбца и номер строки ячейки, а также параметр, представляющий лист с этой ячейкой. Если указанная строка не существует, он создает строку и добавляет ее в лист. Если указанный столбец существует, код находит ячейку, соответствующую строке в этом столбце, и возвращает ячейку. Если указанный столбец не существует, код создает столбец и вставляет его в лист. Затем код определяет, куда необходимо вставить новую ячейку в столбце, выполняя последовательную итерацию по элементам строк для поиска ячейки, следующей непосредственно за указанной строкой. Он сохраняет эту строку в переменной refCell. Затем он вставляет новую ячейку перед ячейкой, на которую ссылается refCell, используя метод InsertBefore. Затем код возвращает новый объект Cell.

Пример кода

В следующем примере кода вычисляется сумма смежных ячеек в документе электронной таблицы. Результат вставляется в объект SharedStringTablePart и в указанную ячейку результата. Для вызова метода CalculateSumOfCellRange можно использовать следующий пример:

После запуска программы можно открыть файл «Sheet1.xlsx» и проверить сумму значений столбца на листе «John» в указанной ячейке.

Далее представлен полный пример кода на языках C# и Visual Basic.

Источник