База Знаний: Функции Calc. Функции базы данных. Краткий обзор
В электронных таблицах InfraOffice.pro Calc, «база данных» — просто диапазон, представляющий собой список связанных данных, в котором строки являются записями, а столбцы — полями. База данных Calc не имеет никакого отношения к более сложной базе данных InfraOffice.pro Base. Функции базы данных в Calc весьма просты в использовании, и позволяют выполнять анализ исходных данных — но в них также встроена развитая фильтрация данных.
Таблицы «базы данных» Calc могут быть похожи на Рис. 1.
Первая строка таблицы «базы данных» содержит заголовки (Имя, Класс, Возраст ...), а каждая последующая строка содержит значения данных.
«Критерии», которые используются для отбора строк из «базы данных», вводятся в другую таблицу - Рис. 2.
Первая строка этой таблицы содержит заголовки. Последующие строки определяют критерии: например, используемая выше таблица критериев будет соответствовать тем детям, расстояние до школы у которых — больше чем 600.
Все функции «базы данных» Calc имеют подобную форму. Как простой первый пример, используя таблицы приведённые выше:
=DCOUNT(A1:E10; 0; A13:E14)
Возвращается 5, количество детей, у которых «Расстояние до школы» больше чем 600.
A1:E10 — таблица базы данных, а A13:E14 — таблица критериев.
Имеются другие функции «базы данных», которые возвращают сумму, среднее число, стандартное отклонение и так далее.
Критерии
Таблица критериев может размещаться в любом месте электронной таблице, но зачастую разумно, чтобы она находилась рядом с таблицей базы данных.
Заголовки таблицы критериев должны точно соответствовать заголовкам в таблице базы данных, но они могут появиться в любом порядке и не один раз - Рис. 3.
Все критерии в строке должны удовлетворяться для строки, который будет им удовлетворять, поэтому в данном примере выше мы находим строки, которые имеют:
- «Расстояние до школы» больше чем 600 И
- «Возраст», больше чем 8 И
- «Возраст», меньше или равен 10.
Другими словами школьники 9 и 10 лет, которым приходится проходить до школы больше 600.
Если таблица критериев имеет более чем одну строку критериев, любая удовлетворяющая строка означает, что удовлетворяется вся таблица критериев - Рис. 4.
В этом примере проверяется:
- («Расстояние до школы» больше чем 600 И
- «Возраст», больше чем 8 И
- «Возраст», меньше или равен 10)
ИЛИ
- («Возраст», меньше или равен 8).
Другими словами мы ищем строки, в которых дети соответствуют или «в возрасте 9 и 10 лет, которым приходится проходить до школы больше 600» или «в возрасте 8 лет или менее».
Условие, которое вводится в ячейку таблицы критериев (т. е. >4) — просто текст и имеет следующую форму:
comparator value
где:
- comparator — одно из >, <, > =, <=, =, <> (если comparator опущен, принимается =);
- value — значение (число или текст) для сравнения.
Например:
- условие “> 4” проверяет, что содержимое ячеек больше чем 4;
- условие “<lamp” проверяет, что содержимое ячеек находится в алфавитном порядке прежде, чем lamp (таким образом lady и ant соответствуют условию, но late и zebra, не соответствуют);
- условие “lamp” проверяет, что содержимое ячеек — lamp.
Однако Calc имеет несколько параметров настройки, которые определяют точное поведение — пожалуйста прочитайте следующий раздел.
Параметры настройки для текстовых критериев
Когда проверяется соответствие простому текстовому условию (такому как “<lamp”) может быть важно проверить параметры настройки в диалоге Сервис → Параметры → InfraOffice.pro Calc → Вычисления - Рис. 5.
Учитывать регистр
Этот флаг не имеет никакого влияния на функции баз данных Calc. Регистр игнорируется — таким образом, “lamp” всегда соответствует lamp, Lamp и LAMP и т. д.
Условия поиска = и <> должны распространяться на всю ячейку
Если этот флаг установлен, то “lamp” будет соответствовать ячейке, содержащей только lamp. Если этот флажок не установлен, то “lamp” будет соответствовать lamp, clamp, lampoon и т. д. — другими словами ячейка будет удовлетворять условию, если lamp будет найдена где-нибудь в тексте ячейки.
Разрешить регулярные выражения в формулах
Если этот флаг установлен, то условие рассматривается как регулярное выражение. Например, условие “l?amp” соответствовала бы lamp и amp (в регулярных выражениях “l?” означает, что “l” является необязательным). Регулярные выражения подобны групповым символам, но более мощные; они описаны в разделе Регулярные выражения в функциях Calc. (Заметьте, что Условия поиска = и <> должен распространяться на всю ячейку устанавливающиеся выше также работают, когда выбраны регулярные выражения.)
Результаты функций могут зависеть от этих параметров настройки — но, к сожалению, они являются скорее скрытыми так, чтобы пользователь не мог знать, если параметры заданы неправильно.
Есть два способа бороться с этим:
- Во-первых, можно разрабатывать электронную таблицу, чтобы работать независимо от параметров настройки. Наиболее легко, никогда не делать сравнение текста. Однако, при проведении сравнения необходимо всегда смотреть что в соответствие принимается вся ячейка, и удостовериться, что ни одна из ранее проверенных ячеек, не соответствует частично любой другой ячейке (например, если ищется “apple” как единственное содержимое ячейки, удостовериться, что никакая другая ячейка не может содержать “crabapple”). Не использовать регулярные выражения. Не использовать специальные символы регулярных выражений, таких как. * + [ { в критериях.
- Во-вторых, можно включить в электронной таблице предупреждение, если параметры настройки неправильны. В ячейке A3 введите текст: Check:.и создайте соответствующую формулу в ячейке A4 — например:
Теперь заметьте, что:
COUNTIF(A3;“.*”) — возвращает 1, если регулярные выражения включены.
COUNTIF(A3;“<>e”) — возвращает 1, если распространение на всю ячейку включено.
Для проверки и регулярных выражений и соответствия всей ячейки:
=IF(AND(COUNTIF(A3; “.*”); COUNTIF(A3; “<>e”)); “OK”; “Error: ” & IF(COUNTIF(A3;“.*”)=0; “Включите регулярные выражения. ”; “”) & IF(COUNTIF(A3;“<>e”)=0; “Включите соответствие всей ячейке.”; “”))
Для проверки регулярных выражений, но без соответствия всей ячейки:
=IF(AND(COUNTIF(A3;“.*”); COUNTIF(A3;“<>e”)=0); “OK”; “Error: ” & IF(COUNTIF(A3;“.*”)=0; “Включите регулярные выражения. ”; “”) & IF(COUNTIF(A3;“<>e”); “Выключите соответствие всей ячейке.”; “”))
Для проверки соответствия всей ячейки, но без регулярных выражений:
=IF(AND(COUNTIF(A3;“.*”)=0; COUNTIF(A3;“<>e”)); “OK”; “Error: ” & IF(COUNTIF(A3;“.*”); “Выключите регулярные выражения.”; “”) & IF(COUNTIF(A3;“<>e”)=0; “Включите соответствие всей ячейке .”; “”))
Для проверки что отключены регулярные выражения и соответствие всей ячейки:
=IF(AND(COUNTIF(A3;“.*”)=0; COUNTIF(A3;“<>e”)=0); “OK”; “Error: ” & IF(COUNTIF(A3;“.*”); “Выключите регулярные выражения. ”; “”) & IF(COUNTIF(A3;“<>e”); “Выключите соответствие всей ячейке.”; “”))
Регулярные выражения в функциях Calc
Есть множество функций в Calc, которые позволяют использовать регулярные выражения:
SUMIF, COUNTIF, MATCH, SEARCH, LOOKUP, HLOOKUP, VLOOKUP, DCOUNT, DCOUNTA, DSUM, DPRODUCT, DMAX, DMIN, DAVERAGE, DSTDEV, DSTDEVP, DVAR, DVARP, DGET.
Действительно ли используются регулярные выражения, выбирается в диалоговом окне Сервис → Параметры → InfraOffice.pro Calc → Вычисления.
Например =COUNTIF (A1:A6; “r.d”), с установленным флагом Разрешить регулярные выражения в формулах , будет подсчитывать ячейки в диапазоне A1:A6, которые содержат “red” и “ROD”.
Кроме того, если флажок Условия поиска = и <> должны распространяться на всю ячейку не установлен, то “Fred”, “bride” и “Ridge” также будут подсчитаны.
Поиск с использованием регулярных выражений в пределах функций — всегда не зависит от регистра, независимо от установки флагка Учитывать регистр в вышеуказанном диалоговом окне — поэтому “red” и “ROD” всегда будут учитываться в вышеупомянутом примере. Эта независимость от регистра также относится к структурам регулярных выражений ([:lower:]) и ([:upper:]), которые соответствуют символам независимо от регистра.
Регулярные выражения не будут работать для простых сравнений. Например: A1 = “r.d” будет всегда возвращаться FALSE, если A1 будет содержать “red”, даже если регулярные выражения будут разрешены — оно возвратит TRUE, только если A1 будет содержать “r.d” (“r”, потом точка, потом “d”). Если необходимо проверить использование регулярных выражений, пробуйте функцию COUNTIF — COUNTIF(A1; “r.d”) , возвратится '1' или '0', интерпретируемые как TRUE или FALSE в формулах подобных=IF (COUNTIF (A1; “r.d”); “ура”; “фу”).
Установка параметра Разрешить регулярные выражения в формулах , означает, что все вышеупомянутые функции будут интерпретировать любые специальные символы регулярных выражений (такие как круглые скобки) используемые в строках в пределах формул, чтобы «избежать» этого, необходимо использовать предшествующую обратную косую черту, чтобы символ воспринимался не как часть регулярного выражения. Эту обратную косую черту будут необходимо удалить, если параметр будет позднее снят.
Отметьте, что в OpenOffice.org 2.4 изменение параметра Разрешить регулярные выражения в формулах , не обязательно отражается на результатах, даже если выполнен перерасчёт. Эта ошибка 89047 не относится к OpenOffice.org 2.3, и устранена в OpenOffice.org 3.0.
InfraOffice.pro 3.1.x
