статья появилась в результате осознания того что про работу с базами данных в OOo практически ничего не написа
Работа добавлена на сайт samzan.net:
Е.В.Смородина, И.А.Хахаев
Работа со списками в OpenOffice.org
Е.В.Смородина, И.А.Хахаев
Эта статья появилась в результате осознания того, что про работу с базами данных в OOo практически ничего не написано. То, что имеется в Сети и вышедших книжках (см. список литературы в конце), затрагивает только самые примитивные или очень специальные задачи. Поэтому мы решили в качестве первого шага описать некоторый набор возможностей OOo по работе с простейшей базой данных, а именно – списком.
Все примеры относятся к версии OpenOffice.org 2, однако их можно легко адаптировать под предыдущие версии, используя имеющуюся литературу.
Естественно, этот материал ни в коей мере не заменяет пользовательскую документацию и имеющиеся ранее статьи, он просто описывает результаты личного опыта авторов.
Имеется некоторая модельная база данных по сотрудникам мифического торгового предприятия, состоящая из 10 полей и 78 строк (1-я строка – имена полей). Поля: «ФИО» - текстовое, «Дата рожд» - дата, «Нач..стажа» - дата, «Пол» - текстовое (1 буква), «С/п» - текстовое (1 буква), «Детей» - число (целое), «Секц» - текстовое, «Образ» - текстовое, «Должность» - текстовое, «Оклад» - число (в «денежном» формате). Вся работа велась в Ru.OpenOffice.org 2 от «Инфра-Ресурс». Обработка списков велась в электронной таблице (ЭТ) OpenOffice.org Calc, и в конце чуть-чуть затронута работа со встроенной СУБД OpenOffice.org Base. Полный текст этого документа и файла ЭТ с модельной базой в форматах OpenDocument можно получить по адресу ftp://ice.spb.ru/pub/articles/proc_list.zip
- Сортировка списка
Перед сортировкой полезно выделить весь диапазон ячеек, занимаемый списком, включая строку с именами полей. Однако программа может автоматически определить этот диапазон, если он не содержит пустых строк или столбцов и активная ячейка находится внутри этого диапазона.
Вызов диалога настройки сортировки осуществляется через главное меню («Данные/Сортировка...», рис. 1) после чего настраиваются последовательность полей и порядок сортировки по каждому (рис. 2). Вкладка «Параметры» диалога настройки сортировки позволяет уточнить некоторые настройки, например, задать язык для текстовых полей (интересно посмотреть разницу при установке параметра «Язык» в «Стандарт» и в «Русский»).
Рисунок 1. Вызов диалога настройки сортировки.
Пусть нужно отсортировать сотрудников сначала по убыванию возраста, затем по убыванию стажа и затем – по возрастанию количества детей. Тогда получится диалог, показанный на рис. 2.
Рисунок 2. Диалог настройки сортировки списка.
- Автофильтр
Автофильтр позволяет выбирать записи по точному соответствию значения в каком-то поле.
Фильтрацию можно повторять по нескольким полям (пошаговый автофильтр).
Автофильтр устанавливается после выделения диапазона ячеек, занимаемый списком (аналогично настройкам сортировки) через главное меню («Данные/Фильтр/Автофильтр»).
В ячейках с заголовками полей появляются кнопки раскрывающихся списков, и можно для каждого поля выбирать нужные значения. Причем, раскрыв список в выбранном поле, можно увидеть все возможные значения этого поля в упорядоченном виде. Это удобно для определения минимума и максимума для числовых полей. Например, для поиска мужчин со средним-специальным образованием в поле «Пол» выбираем значение «м», а затем в поле «Образ» выбираем значение «ср/сп». Полученный результат показан на рис. 3.
Рисунок 3. Результат работы автофильтра.
Последовательность выбора полей для автофильтрации значения не имеет. Более того, если установлен фильтр по одному полю, то список возможных значений других полей при этом изменяется автоматически. Например, если по полю «Образ» выбрано «в», то в поле «Должность» будут присутствовать только те значения должностей, для которые имеют поле «Образ» имеет значение «в» Следует отметить, что признаком наличия фильтра по какому-либо полю является изменение цвета стрелки (меняется на синий) на кнопке раскрывающегося списка.
Для восстановления полного списка нужно в полях, по которым был установлен фильтр, выбрать вариант «все». Если полей было выбрано много, то, чтобы снять фильтр со всех полей сразу, можно воспользоваться командой главного меню «Данные/Фильтр/Удалить фильтр».
Пусть нужно получить список сотрудников, начавших трудовую деятельность в 1990 году. Поскольку в поле «Нач.стажа» нет возможности выбрать конкретный год, можно воспользоваться возможностями электронной таблицы и создать новое (расчетное) поле. Предварительно нужно отключить автофильтр точно так же, как его включали.
Создадим новое поле под названием «Год» сразу за полем «Оклад» и вычислим его значения с помощью функции «YEAR(...)» (ГОД(...)), примененной к ячейкам поля «Нач.стажа». Затем снова установим Автофильтр и в поле «Год» выберем значение «1990». Результат показан на рис. 4.
Рисунок 4. Расчетное поле и автофильтр.
В принципе, практически для любых выборок можно создавать расчетные поля (одно или несколько), используя текстовые, математические, логические и любые другие функции электронных таблиц, однако есть более эффективные приемы работы, которые описываются ниже.
- Стандартный фильтр
Стандартный фильтр используется при необходимости сравнения значений в одном или нескольких полях (до 3-х) с заданными значениями (образцами, шаблонами). Таким образом, Стандартный фильтр является некоторым приближением к QBE-запросам в СУБД. В OOo Calc Стандартный фильтр позволяет установить три условия, связав их логическими функциями «И» или «ИЛИ». Нужно отметить, что разумным подходом является использование одинаковых функций (запрос типа «Условие1» И «Условие2» И «Условие3», аналогично с ИЛИ), поскольку последовательность смешанных «И» и «ИЛИ» приводит к непредсказуемым результатам в соответствии с логикой построения запросов.
Для текстовых и числовых полей Стандартный фильтр позволяет использовать символы подстановки («.» - единичный символ, «.*» - произвольная последовательность символов), для числовых полей – операции сравнения и нахождение наибольшего и наименьшего значений.
Стандартный фильтр включается аналогично Автофильтру, после чего заполняется диалог настройки фильтра.
Рассмотрим несколько примеров.
1. Требуется найти сотрудников, фамилии которых начинаются на «Ми» или «Ни». Соответствующий диалог настройки фильтра показан на рис. 5, а результаты работы – на рис. 6.
Рисунок 5. Стандартный фильтр по текстовому полю.
Следует обратить внимание на ключ «Регулярное выражение» в параметрах фильтра. Если он не установлен, ничего не сработает.
Режим использования регулярных выражений должен устанавливаться, когда в настройках фильтра используются символы подстановки. В OOo Calc для одиночного обозначения одиночного символа используется точка («.»), а для любой последовательности символов – комбинация «.*».
Рисунок 6. Результат поиска по фамилиям.
2. Требуется найти сотрудников, начавших трудовую деятельность в 1960, 1983 и 1990 годах. В этом случае в Стандартном фильтре устанавливаем три условия, связанные логической функцией «ИЛИ» по расчетному полю «Год» (см. описание Автофильтра) в соответствии и рис. 7. Результат операции показан на рис. 8.
Рисунок 7. Стандартный фильтр с тремя условиями.
Рисунок 8. Результаты поиска по годам.
3. Поскольку дата хранится в памяти как число, для полей, содержащих даты, в настройках фильтра можно использовать условия «=», «<», «>», «>=» и «<=» , а значения указывать в формате даты. Например, требуется сформировать список сотрудников, родившихся в 1975г.
Рисунок 9. Условия поиска по году рождения.
Рисунок 10. Результаты поиска по году рождения.
4. Для работы с датами можно пользоваться и регулярными выражениями.
Например, требуется выполнить то же условие, что и в п.3. Запрос может выглядеть как на рис. 11. Единичными символами заменяем день и месяц рождения. Аналогично можно формировать списки по дню и месяцу рождения. Например, чтобы сформировать список сотрудников, рожденных в декабре, в области значений вводим «...12.....». В последнем случае, как и рис. 11, первые две точки означают два любых символа номера дня в месяце, потом идет точка-разделитель элементов даты, потом два символа номера месяца, потом опять точка-разделитель и четыре символа для номера года.
Рисунок 11. Фильтр по году рождения с символами подстановки.
5. Пусть требуется сформировать сотрудников, фамилии которых состоят из 5 или 6 букв. Для составления условий используем символы подстановки и включаем режим «Регулярное выражение» (рис. 12).
Рисунок 12. Работа с единичными символами.
Рисунок 13. Результат работы фильтра.
Для удаления результатов фильтрации выполняется команда главного меню «Данные/Фильтр/Удалить фильтр». Курсор (указатель активной ячейки) при этом должен находиться в области значений списка.
Для более сложных запросов может потребоваться создание нескольких расчетных полей, дополнительное использование логических функций и т.п. Поэтому в таких ситуациях более эффективно использовать другие технологии составления запросов.
- Расширенный фильтр
Расширенный фильтр используется в тех случаях, когда сложно сделать запрос с помощью Автофильтра и Стандартного фильтра, а также для работы с функциями базы данных. Для использования расширенного фильтра полезно над таблицей списка добавить несколько пустых строк, скопировав в первую из них имена полей. При этом перед списком должна оставаться хотя бы одна пустая строка. Для расширенного фильтра нужно определить три диапазона ячеек:
- область с данными (включая имена полей);
- блок критериев (имена полей и шаблоны для поиска по этим полям);
- диапазон для вывода результатов (вообще говоря, можно не указывать, тогда список будет фильтроваться на месте).
Для расширенного фильтра действует следующее правило: если условия расположены в одной строке блока критериев, они выполняются одновременно (логическое «И»), а если в разных строках – должно выполняться хотя бы одно из них (логическое «ИЛИ»).
Диапазон критериев должен состоять минимум из двух ячеек – имени поля и шаблона (условия) поиска. Существует ограничение по количеству условий «ИЛИ», программа не обрабатывает более 2-х «ИЛИ», если запрос ведется по 3 и более полям.
Диалог настройки расширенного фильтра нужно вызывать только после того, как составлен блок критериев и указатель активной ячейки установлен внутри диапазона, занимаемого списком. В диалоге запрашиваются только ячейки блока критериев (рис. 14).
Рисунок 14. Диалог настройки расширенного фильтра.
Рассмотрим несколько примеров.
1. Нужно получить список женщин с высшим образованием, имеющих детей и мужчин со средним и средне-специальным образованием, имеющих детей (рис. 15).
Рисунок 15. Блок критериев и результаты работы расширенного фильтра.
При составлении запроса с помощью расширенного фильтра возможно использование формул и функций. Однако особенностью является то, что шаблон для поиска обязательно должен иметь текстовый тип, поэтому непосредственные вычисления в ячейке блока критериев не приводят к желаемым результатам. При формировании условий необходимо использовать дополнительные текстовые функции.
2. Требуется получить список сотрудников, имеющих оклад меньше среднего по предприятию. Для решения этой задачи во-первых, вычислим средний оклад (функция AVERAGE(...) или СРЗНАЧ(...) по диапазону ячеек с окладами), во вторых, в отдельную ячейку запишем знак сравнения («<»), а в ячейке блока критериев запишем формулу объединения знака и числа, причем число округлим до целого. В предположении, что среднее значение находится в ячейке K3, а знак сравнения – в K2, в блоке критериев получим формулу «=CONCATENATE(K2;ROUND(K3;0))» (что в переводе на русский означает «=СЦЕПИТЬ(K2;ОКРУГЛ(K3;0))»). Соответственно, при изменениях окладов среднее будет автоматически пересчитываться и запрос будет всегда правильный (рис. 16).
Рисунок 16. Формула в критериях расширенного фильтра.
- Промежуточные итоги
При обработке списков часто возникает необходимость группировки записей по какому-либо признаку и получения некоторых данных для каждой группы записей. Например, хотелось бы посчитать количество сотрудников с разным образованием и сравнить средние оклады в этих группах.
Для выполнения этого задания выбирается пункт меню «Данные/Промежуточные итоги».
На рис. 17 представлено меню для выбора признака группировки и получения количества сотрудников по этой группе и среднего оклада.
Рисунок 17. Выбор поля для группировки и функции для расчета промежуточных итогов.
Настройка параметров сортировки делается во вкладке «Параметры».
На рис. 18 представлен фрагмент таблицы результатов.
Рисунок 18. Результаты вычисления промежуточных итогов.
Если нам не требуется подробная информация по записям, можно выбрать второй уровень структуры и получить данные только по группам (Рис. 19). Кнопки в левом верхнем углу таблицы позволяют выбирать уровень детализации. Знаки «+» и «-» позволяют работать со списком как со структурой .
Рисунок 19. Итоги, "свернутые" по уровням.
Для удаления промежуточных итогов выбирается тот же пункт меню, но обязательно на этапе показа записей (низший уровень структуры).
Группировать можно и по нескольким признакам. Например, требуется подсчитать количество сотрудников обоего пола, затем отдельно для женщин и мужчин количество сотрудников с разным образованием и для каждого вида образования сравнить средние оклады по должностям.
На рис. 20 показан фрагмент полученного результата. Аналогично предыдущему примеру, можно перейти на любой другой уровень структуры и получить данные в необходимом виде.
Рисунок 20. Многоуровневые итоги.
- Функции ЭТ для работы с базами данных
Для вычислений итогов по базе данных используются функции категории «База данных». Они позволяют подсчитывать количество значений, максимальное, минимальное и среднее значение и сумму по какому-либо полю при заданных условиях. Условия задаются блоком критериев аналогично расширенному фильтру. Таким образом, при использовании функций категории «База данных» требуется указать
- диапазон ячеек, занимаемый списком;
- поле, по которому проводятся вычисления (адрес ячейки, содержащей имя поля из диапазона списка);
- блок критериев (диапазон ячеек, содержащий минимум две ячейки – имя поля и шаблон для поиска).
Результаты вычислений можно использовать для наглядного представления итогов, т.е. для диаграмм.
При использовании этой категории функций собственно фильтрация не выполняется.
Пример: построить диаграмму распределения количества женщин по уровням образования (в процентах). Для этого сначала просто построим диаграмму, а затем попросим программу ЭТ показать значения категорий (подписи данных) в процентах. Диаграмму будем строить по результатам работы функции DCOUNTA(...) (БСЧЕТА или БСЧЕТ2 по-русски). Эта функция используется для обработки текстовых полей. Если бы нужно было считать числа (например, количество детей или оклады), то использовалась бы DCOUNT (она же БСЧЕТ). Имеется 4 уровня образования (н/ср, ср/сп, ср, в) и для каждого варианта нужен свой блок критериев (пример показан на рис. 21).
Рисунок 21. Блоки критериев и результаты работы функции DCOUNTA().
Потом из результатов вычислений путем копирования формируем диапазон для построения круговой диаграммы. Подробности построения диаграмм рассматривать не будем, а результат показан на рис. 23.
Рисунок 22. Диаграмма распределения сотрудниц по уровням образования.
- Сводные таблицы
Получить итоги в виде таблицы удобно с помощью команды главного меню «Данные/Сводная таблица/Запустить...». После этого появляется возможность построить сводную таблицу с помощью Мастера.
На первом шаге в качестве источника данных нужно выбрать текущее выделение (рис. 23) и в появившемся диалоге Мастера сводных таблиц (рис. 24) простым перетаскиванием кнопок с названиями полей сформировать нужную структуру таблицы.
Рисунок 23. Определение источника данных для сводной таблицы.
Рисунок 24. Мастер сводных таблиц.
Например, требуется узнать средний оклад по должностям отдельно для мужчин и женщин. Средний оклад – это некоторые расчетные данные, поэтому кнопка “Оклад” перетаскивается в область данных. По умолчанию программа предлагает по расчетному полю функцию Сумма (рис. 25). Если требуется другая, надо двойным щелчком по кнопке в области данных выбрать желаемую функцию (рис. 26) или выделить эту кнопку и щелкнуть по кнопке “Параметры” в правой части окна.
Рисунок 25. Полный диалог настройки сводной таблицы.
Удалить кнопки с названиями полей из областей можно перетаскиванием в пустую область таблицы или с помощью кнопки “Удалить” в правой части окна.
Рисунок 26. Выбор операции для области данных.
Без дополнительных указаний программа размещает полученную сводную таблицу на том же листе внизу под исходными данными. По желанию можно изменить расположение сводной таблицы, нажав на кнопку “Дополнительно”, как показано на рис. 25. Пусть сводная таблица строится на новом листе. Итоговый вариант показан на рис. 27.
Рисунок 27. Итоговые настройки сводной таблицы.
В получившейся сводной таблице (рис. 28) видим средние оклады по каждой должности, средние оклады мужчин и женщин и среднее по предприятию.
Рисунок 28. Заказанная сводная таблица.
В любой момент, щелкнув правой кнопкой мыши по любой ячейке сводной таблицы и выбрав пункт “Запустить”, можно изменить разметку таблицы. Например, можно в область “Страница” переместить кнопку «Образование», и тогда средний оклад по должностям и полу можно просматривать только для сотрудников с определенным видом образования, выбрав его из раскрывающегося списка (рис. 29).
Рисунок 29. "Трехмерная" сводная таблица.
В области полученной сводной таблицы можно заказать фильтрацию данных по имеющимся полям. Например, вызвав контекстное меню к полю «Пол» и выбрав меню “Фильтр” можно посмотреть имеющиеся данные только для женщин (рис. 30).
Рисунок 30: Результат фильтрации по полям сводной таблицы.
Возможность работы с электронной таблицей как с источником данных для СУБД в OpenOffice.org используется, если хочется составлять очень сложные запросы без использования расширенного фильтра или если требуется быстро создать базу на основе имеющихся списков. Здесь мы рассмотрим вариант со сложными запросами.
В OOo имеется встроенная СУБД (OpenOffice.org Base в версии 2), и лист ЭТ может использоваться как готовая таблица базы данных. Для подключения листа ЭТ в качестве базы необходимо, чтобы блок данных начинался с ячейки А1 (чтобы перед списком не было пустых строк).
Если это условие выполнено, создаем новую базу данных («Файл/Создать/База данных...») и в диалоге Мастера баз данных подключаемся к существующей базе типа «Электронная таблица» (рис. 31), указываем расположение файла (рис. 32) и регистрируем новую базу (например, с именем baza.odb, рис. 33).
Рисунок 31. Выбор электронной таблицы как основы базы данных.
Рисунок 32. Выбор файла электронной таблицы.
Рисунок 33. Регистрация базы данных.
После всех этих действий открывается главное окно OpenOffice.org Base и можно просмотреть список таблиц (рис. 34). Нас интересует таблица (лист ЭТ) BAZA.
Рисунок 34. Главное окно OpenOffice.org Base.
Допустим, требуется получить список женщин с высшим образованием, имеющих детей и мужчин со средним и средне-специальным образованием с количеством детей от 1 до 3. Составим соответствующий запрос.
Перейдем к группе объектов «Запросы» и выберем «Создать запрос в режиме дизайна...».
В дизайнере запросов добавляем в запрос только таблицу BAZA (рис. 35).
Рисунок 35. Выбор таблицы в дизайнере запросов.
Запрос, соответствующий заданию, показан на рис. 36. Поля, для которых не установлен ключ «Видимое», участвуют в запросе, но не видны в результатах запроса. Нужно отметить, что в отличие от Стандартного и Расширенного фильтра в запросах базы данных используются ключевые слова SQL (LIKE) и символы подстановки меняются - «?» означает единичный символ, а «*» - любую последовательность символов.
Рисунок 36. Запрос в OpenOffice.org Base.
Фрагмент результата работы этого запроса показан на рис. 37.
Рисунок 37. Фрагмент результата работы запроса.
Подробно рассматривать работу в OpenOffice.org Base здесь мы не будем, и на этом можно закончить краткий обзор возможностей работы со списками в OpenOffice.org.
- Д.Н.Колисниченко. Самоучитель Linux. Установка, настройка, использование. СПб.: Наука и техника, 2004.
- В.А.Костромин. OpenOffice.org – открытый офис для Linux и Windows. СПб.: БХВ-Петербург, 2005.
- Д.Н.Колисниченко. Работа со списками в Open Office Calc. http://www.softerra.ru/freeos/19616/
- Раздел «Документация» на www.openoffice.ru
2. Реферат- Правовое регулирование банковской деятельности
3. producer of orgnic cosmetics is looking for distributors
4. живот ccessoriusumдобавочный nterioriusпередний orte fаорта rterie fартерия rterie fартерия rticultionon
5. гамильтониан кристалла Eэнергия кристалла Гамильтониан включает операторы кинетической энергии всех
6. Звіт з практики- Вступ до фаху Виконав студент денного відділення ФТТП 1 курсу 1 групи Чеб
7. тема взаимодействующих элементов Укажите что называют отрицанием в диалектике-D снятие старого качеств
8. 5 Векселя Коносамент
9. Что такое классическая политическая экономия
10. 1 при заданной функции ~Т- ~T 4
11. МАКИАВЕЛЛИ Государь Г л а в а I Скольких видов бывают государства и как они приобретаются Все гос
12. Социальное партнерство в сфере труда
13. Еще со времен первобытнообщинного строя известны случаи помощи рожающей женщине
14. ru Все книги автора Эта же книга в других форматах Приятного чтения Густав Майринк Ангел зап
15. ТЕМА ЦНС ЭНДОКРИННАЯ СИСТЕМА ЖЕЛЕЗЫ ВНУТРЕННЕЙ СЕКРЕЦИИ и ИММУННАЯ СИСТЕМА
16. Verk~ufer gennnt ndererseits hben diesen Vertrg wie folgt bgeschlossen vertreten durch den Gesch~ftsf~hrer Herrn lut Stzung-Vollmcht Nr.
17. РЕФЕРАТ дисертації на здобуття наукового ступеня кандидата економічних наук.8
18. Молекулярная палеонтология и эволюционные представления о возрасте ископаемых останков.html
19. Культура компьютерного сообщества достаточно молода по сравнению скажем с культурой общения поэтому
20. Демографическая ситуация на Дальнем Востоке