Работа с данными

Поиск и замена данных

Для простого и быстрого поиска текста на текущем листе используют панель инструментов Поиск.

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

Гораздо большие возможности для поиска дает диалоговое окно ). Для отображения этого окна следует в панели инструментов Стандартная нажать кнопку Найти и заменить или выбрать эту команду в меню Правка. Поиск данных можно производить на всем листе или в выделенной области листа, например, только в некоторых столбцах или строках, а также сразу во всей книге.

Для еще большего расширения возможностей поиска следует нажать кнопку ).

В раскрывающемся списке Искать в следует выбрать способ поиска: по формулам ( Формулы ) или по значениям ячеек ( Значения ). Например, ячейка может показывать значение "100", но содержать формулу =А8*25. При поиске числа "100" по формулам эта ячейка найдена не будет. При поиске числа "100" по значениям эта ячейка будет найдена. При поиске данных, введенных с клавиатуры, выбор того или иного параметра не имеет значения. Поиск также можно выполнять по примечаниям, добавленным к ячейкам ( Заметки ).

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

Можно установить флажок Учитывать регистр, и тогда OpenOffice.org Calc при поиске будет различать прописные и строчные буквы.

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

Флажок Только текущее выделение позволяет искать текст в выделенном фрагменте документа.

По умолчанию поиск производится от текущей ячейки курсора к концу листа. Установив флажок К началу документа, можно изменить направления поиска.

Установка флажка Регулярное выражение позволяет использовать при поиске подстановочные знаки.

Некоторые примеры использования подстановочных знаков приведены в таблице.

Флажок Поиск подобных позволяет искать похожие данные. После установки флажка рядом с ним активизируется кнопка. Нажав ее, можно настроить параметры подобия.

Для поиска не только на текущем листе, а во всем документе, следует установить флажок Поиск во всех листах.

Выбор направления поиска (переключатели Столбцы или Строки ), как правило, большого значения не имеет.

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

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

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

Сортировка данных

Правила сортировки

Общие правила

Сортировка – расположение данных на листе в определенном порядке.

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

Сортировку можно производить как по возрастанию, так и по убыванию. При желании можно сортировать данные в соответствии с собственным порядком сортировки.

Поскольку при сортировке OpenOffice.org Calc автоматически определяет связанный диапазон данных, сортируемый диапазон не должен иметь пустых столбцов и строк.

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

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

Можно выполнять сортировку данных по тексту (от А к Я или от Я к А), числам (от наименьших к наибольшим или от наибольших к наименьшим), а также датам и времени (от старых к новым или от новых к старым). Можно также выполнять сортировку по настраиваемым спискам.

Порядок сортировки

При сортировке по возрастанию используется следующий порядок.

Числа сортируются от наименьшего отрицательного до наибольшего положительного числа.

При сортировке алфавитно-цифрового текста сравниваются значения по знакам слева направо. Например, если ячейка содержит текст "Дом100", она будет поставлена после ячейки, содержащей запись "Дом1", и перед ячейкой, содержащей запись "Дом12".

Текст, в том числе содержащий числа, сортируется в следующем порядке:

0 1 2 3 4 5 6 7 8 9 (пробел) ! " # $ % ( ) * , . / : ; ? @ [ \ ] ^ _ ` { | } ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z А Б В Г Д Е Ё Ж З И Й К Л М Н О П Р С Т У Ф Х Ц Ч Ш Щ Ъ Ы Ь Э Ю Я

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

Логическое значение ЛОЖЬ ставится перед значением ИСТИНА.

Все значения ошибки равны.

Пустые значения всегда ставятся в конец.

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

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

Простейшая сортировка производится по данным одного столбца.

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

Нажмите кнопку )(рис 11.4) Простая сортировка

Сортировка по нескольким столбцам

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

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

В меню Данные выберите команду Сортировка.

Во вкладке ).(рис 11.5) Установка сортировки по нескольким столбцам

Настройка параметров сортировки

Некоторые параметры сортировки можно настроить.

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

В меню Данные выберите команду Сортировка.

Во вкладке ).(рис 11.6) Настройка параметров сортировки

Таблицу можно сортировать не по строкам, а по столбцам. Для этого в разделе Направление следует установить переключатель Слева направо.

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

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

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

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

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

Установите флажок ).

В раскрывающемся списке выберите необходимый порядок сортировки ().(рис 11.7) Установка пользовательского порядка сортировки

Раскрывающийся список (см. ) содержит стандартные календарные списки OpenOffice.org Calc, а также списки автозаполнения, созданные пользователем.

Сортировка части диапазона

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

Следует быть осторожным при использовании этого средства. Сортировка по одному столбцу диапазона может привести к нежелательным результатам.

Отбор данных

Общие положения

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

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

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

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

Для отбора данных используют автофильтр, стандартный фильтр и расширенный фильтр.

Использование автофильтра

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

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

В меню Данные выберите команду Фильтр, а затем в подчиненном меню – команду Автофильтр.

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

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

Значки раскрывающихся списков, по которым была произведена выборка, отображаются синим цветом и на них появляется особая отметка. Например, в таблице на произведен отбор данных по столбцу А ( Audi ) и столбцу Е ( Автомат ).

При желании значки автофильтров можно скрыть. Для этого следует в меню Данные выбрать команду Фильтр, а затем в подчиненном меню – команду Скрыть автофильтр.

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

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

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

Для удаления автофильтров следует в меню Данные выбрать команду Фильтр, а затем в подчиненном меню – команду Автофильтр или Удалить фильтр.

Использование стандартного фильтра

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

Отбор производится с использованием диалогового окна ).

Для отображения диалогового окна следует в меню ).

Для расширения возможностей отбора с использованием стандартного фильтра можно нажать кнопку ).

Всего можно использовать до восьми одновременно накладываемых условий отбора. Условия должны быть связаны логическими операторами И или ИЛИ. К назначению каждого следующего условия можно перейти, только выбрав соответствующий оператор в раскрывающемся списке Оператор.

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

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

Операторы Равно и Не равно можно использовать для любого типа данных. Остальные операторы сравнения ( Больше, Меньше, Больше или равно, Меньше или равно ) используются для числовых данных, дат и времени.

Операторы типа Наибольшее ( Наименьшее ) используются для числовых данных.

Последние шесть операторов могут использоваться только для текстовых данных.

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

В дополнительной области окна ) можно настроить некоторые параметры отбора.

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

При отборе можно учитывать регистр букв, установив соответствующий флажок.

Если установить флажок Регулярное выражение, то в поле Значение можно использовать подстановочные знаки так же, как они используются при поиске данных.

Установка флажка Без повторений исключает повторяющиеся строки из списка отфильтрованных данных.

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

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

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

Автоматическое вычисление итогов

Об итогах

OpenOffice.orgCalc позволяет автоматически вычислять промежуточные и общие итоги для числовых данных, представленных в виде списка. При этом на листе автоматически создается структура, что позволяет отображать и скрывать строки каждого промежуточного итога. Максимальное количество столбцов, используемых в группировке – 3.

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

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

Значения общих и промежуточных итогов автоматически пересчитываются при каждом изменении исходных данных.

Добавление итогов

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

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

Во вкладке 1-я группа в раскрывающемся списке Группировать по выберите столбец, для данных которого будут рассчитываться итоги. В приведенном примере выбран столбец "Марка".

В списке Вычислять промежуточные итоги для выберите столбец, содержащий значения, по которым необходимо подвести итог. В списке Использовать функцию выберите функцию для вычисления итогов. При необходимости можно выбрать еще несколько столбцов и для каждого из них используемую функцию. Функции могут быть как одинаковыми, так и разными. В приведенном примере для столбца "Количество" выбрана функция Сумма, а для столбца "Цена" – Среднее значение.

При необходимости можно перейти во вкладку ). Это может быть разумно, если в нем есть повторяющиеся данные. В приведенном примере группировка была произведена по столбцу "Тип КПП".(рис 11.15) Выбор второго столбца группировки и используемых функций

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

Можно перейти во вкладку ).(рис 11.16) Настройка параметров

После установки всех параметров нажмите кнопку ОК.

После добавления итогов в таблице появляются новые строки с промежуточными итогами и общим итогом (). Слева от области листа появляются символы структуры.

Пользуясь символами структуры, можно скрывать и отображать данные на листе.

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

Изменение итогов

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

Удаление итогов

Для удаления итогов следует выделить любую ячейку таблицы и в меню команду ) следует нажать кнопку Удалить.

Можно не удаляя итогов удалить только элементы управления структурой листа. Для этого в меню команду Группа и структура, а затем в подчиненном меню – команду Удалить.

Упражнение 11

Запустите OpenOffice.org Calc.

Задание 1

Откройте файл exercise_11_1.ods.

Перейдите к листу Лист 1.

Отсортируйте таблицу по данным столбца Шоколад.

Перейдите к листу Лист 2.

Выполните сложную сортировку таблицы: сначала по данным столбца Шоколад, а затем по данным столбца Количество.

Перейдите к листу Лист 3.

Отсортируйте только данные столбца Менеджер.

Перейдите к листу Лист 4.

Отсортируйте таблицу по данным столбца День.

Перейдите к листу Лист 5.

Отсортируйте таблицу по данным строки Цена.

Сохраните файл exercise_11_1.ods под именем Lesson_11_1.ods.

Задание 2

Откройте файл exercise_11_2.ods.

Перейдите к листу Лист 1.

С использованием автофильтра отобразите только строки таблицы, относящиеся к поставщику Ирис.

Перейдите к листу Лист 2.

С использованием автофильтра отобразите только строки таблицы, относящиеся к товару Люкс и поставщику Ирис.

Перейдите к листу Лист 3.

С использованием автофильтра отобразите только строки таблицы, в которых в столбце Объем имеют 10 максимальных значений.

Перейдите к листу Лист 4.

Отобразите все строки листа.

Перейдите к листу Лист 5.

Скройте значки автофильтров.

Перейдите к листу Лист 6.

Удалите автофильтры.

Перейдите к листу Лист 7.

С использованием стандартного фильтра отобразите только строки таблицы, относящиеся к поставщикам Ирис и Корона.

Перейдите к листу Лист 8.

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

Перейдите к листу Лист 9.

С использованием стандартного фильтра отобразите только строки таблицы с объемом поставки от 40 до 60.

Перейдите к листу Лист 10.

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

Сохраните файл exercise_11_2.ods под именем Lesson_11_2.ods.

Задание 3

Откройте файл exercise_11_3.ods.

Перейдите к листу Лист 1.

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

Перейдите к листу Лист 2.

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

Перейдите к листу Лист 3.

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

Перейдите к листу Лист 4.

Замените действие, установленное при подведении промежуточных и общих итогов: вместо среднего значение рассчитайте сумму.

Перейдите к листу Лист 5.

Из таблицы удалите второй уровень промежуточных итогов.

Перейдите к листу Лист 6.

Не удаляя промежуточных и общих итогов, удалите элементы управления структурой листа.

Перейдите к листу Лист 7.

Удалите промежуточные и общие итоги.

Сохраните файл exercise_11_3.ods под именем Lesson_11_3.ods.

Закройте OpenOffice.org Calc.