Как удалить повторяющиеся строки в таблице или списке

AGGREGATE — суммирование с пропуском скрытых ячеек и ячеек с ошибками

В прошлом примере было показано как суммировать диапазон, исключая скрытые ячейки, но бывают ситуации, когда нужно исключить ещё и ячейки с ошибками или с функциями и самой . Функция имеет ещё больше возможностей чем . Ниже я даю описание её, но так как некоторые параметры этой функции к теме этой статьи не относится, я их не буду касаться. И так как она, к сожалению, до сих пор не описана в справке, если вам нужно уточнить что-то по ней, то лучше пока будет воспользоваться справкой Excel (ну, должны же мы хоть что-то полезное получать от корпорации MS :).
В рамках этой статьи нам интересен следующий синтаксис функции:
Номер_функции — это номер, за которым закреплена функция, использующаяся для диапазона. Для задач этой статьи это номер — сумма.
Параметр — числовое значение, определяющее какие значения при вычислении следует пропускать. Все параметры пронумерованы от 1 до 7. Следующий список показывает номер и краткое описание функции:

0 или опущен — пропускать вложенные функции и .
1 — пропускать скрытые строки и вложенные функции и .
2 — пропускать значение ошибок и вложенные функции и .
3 — пропускать скрытые строки, значения ошибок и вложенные функции и .
4 — ничего не пропускать.
5 — пропускать только скрытые строки.
6 — пропускать только значения ошибок.
7 — пропускать скрытые строки и значения ошибок.

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

FLOOR

Округляет число в меньшую сторону до кратного с заданной точностью.

Синтаксис

Число: число для округления в меньшую сторону.

Точность: число, до кратного которому требуется округлить значение.

Режим: необязательное значение. Если значение «Режим» указано и не равно нулю и если значения параметров «Число» и «Точность» являются отрицательными, то округление производится на основании абсолютного значения числа. Этот параметр игнорируется при экспорте в MS Excel, поскольку Excel не распознает третий параметр.

Если оба параметра «Число» и «Точность» имеют отрицательные значения, а значение параметра «Режим» равно нулю или не указано, после импорта результаты в LibreOffice Calc и Excel будут разными. При экспорте таблицы в Microsoft Excel используйте для параметра «Режим» значение=1, чтобы результаты в Excel не отличались от Calc.

ADDRESS

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

Для функциональной совместимости функции ADDRESS и INDIRECT поддерживают необязательный параметр, который позволяет указать, использовать ли вместо нотации адреса A1 нотацию R1C1.

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

В функции INDIRECT этот параметр добавляется в качестве второго параметра.

В обеих функциях, если аргумент вставлен со значением 0, используется нотация R1C1. Если аргумент не используется или его значение не равно нулю, используется нотация A1.

Если используется нотация R1C1, функция ADDRESS возвращает строки адреса, используя в качестве разделителя имен листов восклицательный знак ‘!’, а функция INDIRECT ожидает, что в качестве разделителя имен листов используется восклицательный знак. В нотации А1 обе функции по-прежнему используют в качестве разделителя имен листов точку ‘.’.

При открытии документов в формате ODF 1.0/1.1 функции ADDRESS, отображающие имя листа в качестве четвертого параметра, сместят имя листа на место пятого параметра. Будет вставлен новый четвертый параметр со значением 1.

Если при сохранении документа в формате ODF 1.0/1.1 в функциях ADDRESS имеется четвертый параметр, этот параметр будет удален.

Не сохраняйте электронную таблицу в старом формате ODF 1.0/1.1, если четвертый параметр функции ADDRESS использовался со значением 0.

Функция INDIRECT сохраняется без преобразования в формат ODF 1.0/1.1. Если имеется второй параметр, более старая версия Calc возвратит для этой функции ошибку.

Синтаксис:

Строка: номер строки для ссылки на ячейку.

Столбец: номер столбца для ссылки на ячейку (число, а не буква).

Абс определяет тип ссылки:

1: абсолютная ($A$1)

2: абсолютная ссылка на строку, относительная ссылка на столбец (A$1)

3: строка (относительная), столбец (абсолютная) ($A1)

4: относительная (A1)

A1 (необязательный параметр): если для этого параметра установлено значение 0, то используется нотация R1C1. Если этот параметр отсутствует или имеет значение, отличное от 0, то используется нотация A1.

Лист: имя листа. Имя столбца заключается в двойные кавычки.

Пример:

возвращает следующий результат: Лист2.A$1

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

ISREF

Проверяет, является ли данное выражение ссылкой.Возвращает значение TRUE, если аргумент является ссылкой; в противном случае возвращается FALSE. Если в эту функцию передается ссылка, значение, на которое она ссылается, не анализируется.

Если происходит ошибка, функция возвращает логическое или числовое значение.

Пример

возвращает результат TRUE, поскольку C5 — действительная ссылка.

всегда возвращает FALSE, поскольку текст не может являться ссылкой.

возвращает FALSE.

возвращает TRUE, поскольку функция INDIRECT возвращает ссылку.

возвращает FALSE, так как функция ADDRESS возвращает текст, который можно принять за ссылку.

DDE

Возвращает результат для ссылки DDE. Если содержимое диапазона или раздела изменилось, возвращаемое значение также меняется. Чтобы просмотреть обновленные ссылки, следует перезагрузить электронную таблицу или выбрать команду Правка — Ссылки. Межплатформенные ссылки, например ссылки в установке LibreOffice, запущенной в ОС Windows на документ, созданный в ОС Linux, запрещены.

Синтаксис:

Сервер: имя серверного приложения. Приложениям LibreOffice соответствует имя сервера «Soffice».

Файл: полное имя файла, включая путь.

Диапазон: область, содержащая данные для оценки.

Режим: необязательный параметр для управления методами преобразования данных в числа на сервере DDE.

Режим	Результат
0 или отсутствует	Формат числа из стиля ячейки «По умолчанию»
1	Данные всегда преобразуются в стандартный формат для английского языка (США)
2	Данные извлекаются в виде текста; преобразование в числа не выполняется

Пример:

считывает содержимое ячейки A1 на листе1 в LibreOffice электронной таблице Calc data1.sxc.

возвращает девиз из ячейки, содержащей эту формулу. Сначала необходимо ввести строку в документ motto.sxw, содержащий текст девиза, и определить ее в качестве первой строки раздела с именем (в LibreOffice Writer по пути Вставка – Раздел). При изменении девиза (и его сохранении) в документе Writer LibreOffice девиз обновляется во всех ячейках LibreOffice Calc, в которых определена соответствующая ссылка DDE.

GETPIVOTDATA

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

Синтаксис

Можно использовать два разных синтаксиса:

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

Первый cинтаксис

Целевое_поле: строка для выбора одного из полей данных сводной таблицы. Эта строка может содержать имя исходного столбца или имя поля данных, отображаемое в таблице (например, «Сумма – Сбыт»).

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

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

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

Исходные данные, которые содержат записи, скрытые настройками сводной таблицы, игнорируются. Порядок пар «поле/элемент» не имеет значения. Регистр имён полей и элементов не учитывается.

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

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

Второй cинтаксис

Сводная_таблица: аналогично первому варианту синтаксиса.

Ограничения: список значений, разделённых пробелами. Элементы списка могут заключаться в кавычки (одиночные). Вся строка заключается в двойные кавычки (за исключением случая ссылки на строку из другой ячейки).

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

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

Имя функции можно добавить в форме Поле, в результате чего сопоставление ограничится только значениями промежуточных итогов, для которых эта функция используется. Допустимые имена функций — Sum, Count, Average, Max, Min, Product, Count (только числа), StDev (выборка), StDevP (заполнение), Var (выборка) и VarP (заполнение) без учёта регистра.

Диапазоны ячеек

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

Double Array

В качестве параметра можно передать диапазон ячеек со значениями типа Number или Double. Массив значений типа Double в LibreOffice Calc определяется следующим образом:

Смещение	Название	Описание
Стлб1	Номер столбца в верхнем левом углу диапазона ячеек. Нумерация начинается с 0.
2	Стр1	Номер строки в верхнем левом углу диапазона ячеек. Нумерация начинается с 0.
4	Таб1	Номер таблицы в верхнем левом углу диапазона ячеек. Нумерация начинается с 0.
6	Стлб2	Номер столбца в нижнем правом углу диапазона ячеек. Нумерация начинается с 0.
8	Стр2	Номер строки в нижнем правом углу диапазона ячеек. Нумерация начинается с 0.
10	Таб2	Номер таблицы в нижнем правом углу диапазона ячеек. Нумерация начинается с 0.
12	Количество	Количество элементов. Пустые ячейки не подсчитываются и не передаются.
14	Столбец	Номер столбца для элемента. Нумерация начинается с 0.
16	Строка	Номер строки для элемента. Нумерация начинается с 0.
18	Таб	Номер таблицы для элемента. Нумерация начинается с 0.
20	Ошибка	Номер ошибки. Значение 0 указывает на отсутствие ошибки. Если элемент содержится в ячейке с формулой, номер ошибки определяется этой формулой.
22	Значение	Переменная IEEE из 8 байтов с типом Double или Float
30	…	Следующий элемент

String Array

Диапазон ячеек, который содержит значения с типом данных Text и передаётся как массив значений типа String. Массив типа String в LibreOffice Calc определяется следующим образом:

Смещение	Название	Описание
Стлб1	Номер столбца в верхнем левом углу диапазона ячеек. Нумерация начинается с 0.
2	Стр1	Номер строки в верхнем левом углу диапазона ячеек. Нумерация начинается с 0.
4	Таб1	Номер таблицы в верхнем левом углу диапазона ячеек. Нумерация начинается с 0.
6	Стлб2	Номер столбца в нижнем правом углу диапазона ячеек. Нумерация начинается с 0.
8	Стр2	Номер строки в нижнем правом углу диапазона ячеек. Нумерация начинается с 0.
10	Таб2	Номер таблицы в нижнем правом углу диапазона ячеек. Нумерация начинается с 0.
12	Количество	Количество элементов. Пустые ячейки не подсчитываются и не передаются.
14	Столбец	Номер столбца для элемента. Нумерация начинается с 0.
16	Строка	Номер строки для элемента. Нумерация начинается с 0.
18	Таб	Номер таблицы для элемента. Нумерация начинается с 0.
20	Ошибка	Номер ошибки. Значение 0 указывает на отсутствие ошибки. Если элемент содержится в ячейке с формулой, номер ошибки определяется этой формулой.
22	Длина	Длина следующей строки, включая последний нулевой байт. Если длина с учетом последнего нулевого байта имеет нечётное значение, к строке добавляется второй ноль для получения чётного числа. Поэтому для расчета длины используется формула ((StrLen+2)&~1).
24	Строка	Строка с последним нулевым байтом
24+Длина	…	Следующий элемент

Cell Array

Массивы ячеек используются для вызова диапазонов ячеек, которые содержат текст и числа. Массив ячеек в LibreOffice Calc определяется следующим образом:

Смещение	Название	Описание
Стлб1	Номер столбца в верхнем левом углу диапазона ячеек. Нумерация начинается с 0.
2	Стр1	Номер строки в верхнем левом углу диапазона ячеек. Нумерация начинается с 0.
4	Таб1	Номер таблицы в верхнем левом углу диапазона ячеек. Нумерация начинается с 0.
6	Стлб2	Номер столбца в нижнем правом углу диапазона ячеек. Нумерация начинается с 0.
8	Стр2	Номер строки в нижнем правом углу диапазона ячеек. Нумерация начинается с 0.
10	Таб2	Номер таблицы в нижнем правом углу диапазона ячеек. Нумерация начинается с 0.
12	Количество	Количество элементов. Пустые ячейки не подсчитываются и не передаются.
14	Столбец	Номер столбца для элемента. Нумерация начинается с 0.
16	Строка	Номер строки для элемента. Нумерация начинается с 0.
18	Таб	Номер таблицы для элемента. Нумерация начинается с 0.
20	Ошибка	Номер ошибки. Значение 0 указывает на отсутствие ошибки. Если элемент содержится в ячейке с формулой, номер ошибки определяется этой формулой.
22	Тип	Тип содержимого ячейки: 0 == двойной, 1 == строковый
24	Значение или длина	Если тип == 0, переменная IEEE из 8 байтов с типом Double или Float Если тип == ~1, длина следующей строки, включая последний нулевой байт. Если длина с учетом последнего нулевого байта имеет нечётное значение, к строке добавляется второй ноль для получения чётного числа. Поэтому для расчета длины используется формула ((StrLen+2)&1).
26, если тип==1	Строка	Если тип == 1, строка с последним нулевым байтом
32 или 26+длина	…	Следующий элемент

LINEST

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

Синтаксис:

данные_Y — это диапазон из одной строки или столбца, указывающий координаты y в виде набора точек данных.

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

LINEST определяет наиболее соответствующую данным прямую линию , используя линейную регрессию (метод «наименьших квадратов»). Если имеется более одного набора переменных, прямая линия имеет форму .

Если Тип линии выставлен на FALSE, найденная прямая линия обязана проходить через источник (константа «a» равна нулю; y = bx). Если значение не указано, Тип линии по умолчанию выставляется на TRUE (линия не обязана проходить через источник).

Если значение статистика не указано или выставлено на FALSE, возвращается только верхняя линия таблицы статистических данных. Если выставлено значение TRUE, возвращается вся таблица.

LINEST возвращает таблицу (массив) статистических данных, как показано ниже, и должна быть введена в виде формулы массива (например, при помощи комбинации клавиш Ctrl+Shift+Return вместо одной клавиши Return).

В функциях LibreOffice Calc необязательные параметры можно оставлять пустыми только в том случае, если за ними не следуют другие параметры. Например, если у функции четыре параметра и два последних параметра являются необязательными, то можно оставить пустыми параметры 3 и 4 или параметр 4. Оставить пустым параметр 3 невозможно.

Подробные сведения см. вверху страницы.

Пример:

Эта функция возвращает массив и обрабатывается так же, как и другие функции массива. Выделите диапазон для возвращаемых значений, а затем выберите функцию. Выберите данные_Y. При необходимости можно ввести и другие параметры. Установите флажок Массив и нажмите кнопку ОК.

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

Другие результаты функции LINEST

Просмотрите приведенные ниже примеры.

	A	B	C	D	E	F	G
1					Значение функции
2						—
3
4
5
6
7
8

Столбец A содержит несколько значений X1, столбец B — несколько значений X2, а столбец C — значения Y. Эти значения уже введены в электронную таблицу. В таблице выделен диапазон E2:G6 и запущен мастер функций. Чтобы функция LINEST выполнялась правильно, следует установить флажок Массив в мастере функций. Затем необходимо выделить в таблице (или ввести вручную) следующие значения:

Данные_Y — C2:C8

Данные_X — A2:B8

Тип линии и статистика имеют значение 1.

После нажатия кнопки ОК LibreOffice Calc заполняет выделенный диапазон результатами функции LINEST, как показано в примере.

Формула на панели Формула соответствует каждой ячейке матрицы функции LINEST .

Ниже представлены результаты функции LINEST.

E2 и F2: наклон линии регрессии y=b+m*x для значений x1 и x2. Значения даются в обратном порядке; т. е. наклон для x2 в ячейке E2 и наклон для x1 в ячейке F2.

G2. Пересечение линии b с осью Y.

E3 и F3. Стандартная ошибка для значения наклона.

G3. Стандартная ошибка для пересечения.

E4: RSQ

F4. Стандартная ошибка регрессии, рассчитанной для значения Y.

E5. Значение F, полученное с помощью дисперсионного анализа.

F5. Степени свободы, полученные с помощью дисперсионного анализа.

E6. Сумма квадратов отклонений для примерных значений Y от линейного среднего.

F6. Сумма квадратов отклонений для примерных значений Y от заданных значений Y.

Подробные сведения см. вверху страницы.

Общие сведения об интерфейсе

Максимальное количество параметров для надстройки, добавленной к LibreOffice Calc, равно 16: одно возвращаемое значение и максимум 15 входных параметров функции.

Типы данных определяются следующим образом:

Типы данных	Определение
CALLTYPE	Под Windows: FAR PASCAL (_far _pascal) Другой: по умолчанию (по умолчанию для операционной системы)
USHORT	Целое число из 2 байтов без знака
DOUBLE	Зависящий от платформы 8-байтовый формат
Paramtype	В зависимости от платформы, например, целое число Указатель PTR_DOUBLE =0 на двойной массив Указатель PTR_STRING =1 на строку, которая оканчивается на ноль Указатель PTR_DOUBLE_ARR =2 на двойной массив Указатель PTR_STRING_ARR =3 на строковый массив Указатель PTR_CELL_ARR =4 на массив ячеек НЕТ =5

INDIRECT

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

В функции INDIRECT этот параметр добавляется в качестве второго параметра.

Синтаксис:

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

При открытии электронной таблицы Excel, в которой используются косвенные адреса, рассчитанные с помощью строковых функций, автоматическое преобразование адресов листа не выполняется. Например, адрес для Excel в функции INDIRECT(«filename!sheetname»&B1) не преобразуется в адрес для Calc в функции INDIRECT(«filename.sheetname»&B1).

Пример:

возвращает значение 100, если ячейка A1 содержит ссылку на ячейку C108, а ячейка C108 содержит значение .

суммирует содержимое ячеек в области от A1 до ячейки, адрес которой определен в строке 1 и столбце 3. Таким образом, вычисляется сумма диапазона A1:C1.

Возможности LibreOffice Calc

Рассматриваемые функции присутствуют в приложении уже довольно давно, но если вы новичок в этом, то, скорее всего, вы никогда не слышали о нем раньше, и это нормально. Мы рассмотрим следующие особенности:

Язык OpenOffice BASIC используется для программирования макросов.
Функция перетаскивания лучше реализована в Calc
Нажатие клавиши удаления дает больше возможностей
Вставить PDF как документ

Давайте обсудим это более подробно для более глубокого понимания.

1]Язык OpenOffice BASIC используется для макропрограммирования.

Если вы заинтересованы в макропрограммировании с помощью LibreOffice Calc, не беспокойтесь, эта функция здесь также поддерживается. Однако инструмент работает с языком OpenOffice BASIC вместо Microsoft Visual Basic для приложений (VBA).

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

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

Читать: Возможности LibreOffice Writer.

2]Функция перетаскивания лучше реализована в Calc.

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

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

Читать: Лучшие расширения LibreOffice.

3]Нажатие клавиши удаления открывает дополнительные возможности.

При удалении ячеек в Microsoft Excel вы можете нажать клавишу Delete на клавиатуре, чтобы сделать это. При нажатии клавиши все исчезает, но это не относится к LibreOffice Calc. Видите ли, когда нажата клавиша удаления, программа дает вам возможность выбрать то, что вы хотите удалить.

Нам нравится эта функция, потому что из-за нее пользователям довольно сложно случайно удалить ячейки.

Читать: Обзор LibreOffice.

4]Вставить PDF как документ

Одна из вещей, которую мы не можем сделать в Microsoft Office, — это добавить PDF-документ в виде изображения. Чтобы сделать вещи еще более интересными, этот инструмент может с легкостью импортировать электронные таблицы Apple Numbers, что очень удобно.

Кроме того, пользователи могут сравнивать два документа бок о бок и экспортировать документы Calc как XHTML. Кроме того, если вы хотите повернуть всю диаграмму, LibreOffice Calc также делает это возможным.

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