Microsoft Excel 2003. Табличные базы данных (списки). Сортировка данных, фильтрация данных, подведение промежуточных итогов, разделение и закрепление областей
Сортировка данных в электронных таблицах. Использование фильтров в Microsoft Excel 2003. Создание копии листа. Условия списковой таблицы. Способ структурирования групп без использования режима "Промежуточные итоги". Отображение сгруппированных данных.
| Рубрика | Программирование, компьютеры и кибернетика |
| Вид | лабораторная работа |
| Язык | русский |
| Дата добавления | 28.05.2012 |
| Размер файла | 584,5 K |
Отправить свою хорошую работу в базу знаний просто. Используйте форму, расположенную ниже
Студенты, аспиранты, молодые ученые, использующие базу знаний в своей учебе и работе, будут вам очень благодарны.
Размещено на http://www.allbest.ru/
Microsoft Excel 2003
Работа № 7
Табличные базы данных (списки). Сортировка данных, фильтрация данных, подведение промежуточных итогов, разделение и закрепление областей
Цель работы Работа с табличными базами данных, сортировка и фильтрация данных, Нахождение промежуточных итогов. Разделение и закрепление областей рабочего листа в Excel.
Списки, или табличные базы данных - это такие таблицы, в которых все строки, за исключением заголовков, имеют одинаковую структуру и типы данных.
Для работы со списками в Excel имеются дополнительные возможности, которые перечислены в заголовке лабораторной работы. В программе Excel они реализованы в меню Данные.
Исходная таблица находится в файле Продажа быттехники.xls. Сохраните его в свою папку и откройте.
1. Сортировка данных в электронных таблицах
Проанализируем содержимое ячеек таблицы.
В столбцах A, B, C, D и E внесены наименования товаров, их модели, присвоены коды по порядку, цена поступления и количество каждого товара, в столбцах F, G и H введены формулы для расчета стоимости приобретенного товара, определения цены реализации с 20% наценкой и вычисления прибыли.
Теперь рассмотрим, как можно выполнить сортировку данных в таблице Товары по полю Наименование товара.
Поместите курсор на первую ячейку в столбце Наименование товара. Затем на панели инструментов Стандартная нажмите кнопку Сортировка по возрастанию (рис. 1).
Рис. 1. Вид таблицы Товары перед началом сортировки
Результатом этой операции должна стать таблица, в которой строки будут выстроены по алфавиту для данных первого столбца (рис. 2). При этом, как видно из рисунка, данные в остальных столбцах также переместились, отражая сведения по товару, а в столбце Коды последовательность нарушилась.
Рис. 2. Вид таблицы после сортировки по столбцу Наименование товара
Действуя по аналогии, отсортируйте данные таблицы по возрастанию значений в столбцах Количество, Цена поступления, Планируемая прибыль.
Из полученного результата видно, что первую позицию занял товар с нулевой прибылью (рис. 3).
Рис. 3. Вид списка товаров после сортировки по столбцу Планируемая прибыль
В заключение, отсортируйте строки таблицы снова по возрастанию столбца Код.
Чтобы в дальнейшем было удобней работать с таблицей, изменим в ней порядок столбцов. Поставим в таблице столбец Код первым.
Для этого необходимо вначале вставить пустой столбец перед столбцом Наименование товара. Поместите курсор на любую ячейку столбца А, затем в меню Вставка выберите пункт Столбцы. В таблице должен появиться новый пустой столбец (столбец A). При этом вся таблица сместилась вправо.
Обратите внимание, что формулы, по которым, выполнялись расчеты в трех последних столбцах, по-прежнему работают правильно, несмотря на то, что имена столбцов изменились (с C, D и E на D, E и F). Это произошло благодаря относительным ссылкам на ячейки в формулах.
Теперь, нужно выделить весь столбец D и вырезать его в буфер обмена, вызвав правой кнопкой мыши контекстное меню.
Затем вырезанный столбец нужно вставить на место столбца A (рис. 4). Для этого выделите весь столбец А, правой кнопкой мыши вызовите контекстное меню и дайте команду Вставить.
Рис. 4. Перемещение столбца
Теперь первым столбцом в таблице стал столбец Код, а столбец D остался пустой. Удалите столбец D. Для этого выделите весь столбец D и в контекстном меню выберите пункт Удалить.
Запомните, как можно добавить новый столбец и как удалить ненужный столбец. Аналогично можно поступать и со строками электронной таблицы.
Теперь требуется отсортировать данные в таблице по Наименованию товара, не сортируя при этом столбец с кодами.
а) сортировка по подписям б)сортировка по обозначениям столбцов
Рис. 5. Диалоговое окно Сортировка диапазона
Выделите все ячейки с данными, исключая Код (диапазон ячеек В2:Н24), затем вызовите диалоговое окно Сортировка в меню Данные (рис. 5).
Рассмотрим содержимое диалогового окна, оно содержит несколько областей, в которых задаются (выбираются) параметры к предстоящей сортировке данных.
Три области с полями для выбора критерия сортировки, причем каждая область имеет два направления сортировки - по возрастанию и по убыванию, также имеется область для выбора признака, по которому будут идентифицированы выделенные в таблице ячейки:
а) по подписям первой строки выделенного диапазона ячеек (рис. 5А);
б) по обозначениям столбцов листа (рис. 5Б).
В нашем случае удобнее идентифицировать поля по заголовкам столбцов (по подписям первой строки диапазона) и выбрать первый критерий сортировки Наименование товара, а направление сортировки - по возрастанию. Установите все параметры для сортировки как показано на рис. 5а. Нажмите кнопку OK.
В результате этой операции все товары должны «выстроиться» по алфавиту, а Коды, поскольку они не попали в выделенный диапазон ячеек, так и остались располагаться по возрастанию. Вспомните, что происходило, когда просто сортировали товары по алфавиту!
2. Использование фильтров в электронных таблицах
В Excel фильтры позволяют показать в таблице только нужные данные, а ненужные скрыть. Самый простой способ фильтрации списков - использование встроенного в программу Excel Автофильтра.
Отобразим в таблице Товары только Холодильники. Выделите весь столбец B с наименованиями товаров, и в меню Данные выберите пункт Фильтр-Автофильтр. В результате, в правом нижнем углу в шапке таблицы Наименование товара появится маленькая кнопка , открывающая список значений, встречающихся в данном столбце. В этом списке можно выбрать любое значение (рис. 6). По нему и будет происходить фильтрация.
Рис. 6. Список значений для определения критерия фильтрации.
Если мы выберем в списке значение Холодильник, то в результате получим следующую таблицу (рис. 7).
Обратите внимание, что кнопка Автофильтра изменила цвет на синий . Это означает, что фильтр включен.
Рис. 7. Вид таблицы после назначения фильтра
Для закрепления навыка по использованию фильтра назначьте фильтр на другие наименования товаров. Показать все записи можно, выбрав в списке фильтра значение (Все). Для того чтобы отменить фильтр, повторно выберите пункт меню Данные-Фильтр-Автофильтр.
Фильтрация данных может быть и более сложной.
Поместите курсор таблицы и назначьте для них Автофильтр. Теперь каждый столбец с данными может быть отфильтрован самостоятельно.
В фильтре Наименование товара выберите значение Газовая плита, а в фильтре Цена поступления - значение (Условие...). Затем в открывшемся окне Пользовательский автофильтр, настройте тип условия для фильтрации цены (например, меньше или равно) и назначьте предельное значение для этого условия - 3 000 (рис. 8).
Рис. 8. Вид окна Пользовательский автофильтр
В полученной таблице будут работать два фильтра. Один отображает только газовые плиты, другой из этих газовых плит покажет только те, цена у которых меньше или равна выбранному значению 3 000р. (рис. 9).
Рис. 9. Вид таблицы с двумя включенными фильтрами
Задания для самостоятельного выполнения
Задание 1. Проведите сложную фильтрацию по каким-либо полям таблицы Товары.
Задание 2. Для фильтрации поля Количество назначьте два условия в Пользовательском автофильтре - первый тип условия - меньше с предельным значением 30 и второй тип условия - больше с предельным значением 10.
Задание 3. Для выполнения следующего упражнения создайте копию листа Товары и отключите режим фильтрации.
3. Подведение промежуточных итогов
Основным условием списковой таблицы является одинаковая структура строк и типов данных в них для всех ячеек таблицы.
Это означает, что в середине списка не может быть строки, суммирующей какие-либо значения из верхних строк. Однако на практике такая необходимость часто возникает. Проблема решается с помощью встроенного в Excel средства Промежуточные итоги.
Допустим, необходимо просуммировать количество товаров одного наименования и всего товара в целом. А заодно просуммировать стоимость этих товаров. В меню Данные выберем пункт Итоги....
В окне Промежуточные итоги необходимо настроить поля: «При каждом изменении в:», «Операция:» и «Добавить итоги по:».
В поле "При каждом изменении в:" - выберите из списка Наименование товара.
В поле "Операция:" - выберите из списка Сумма.
В поле "Добавить итоги по:" - поставьте флажки напротив Количество, Стоимость,
Планируемая прибыль.
Затем проверьте, стоят ли флажки в нижней части окна у параметров отображения итогов - Заменить текущие итоги, Итоги под данными.
В результате получим таблицу, в которой добавятся строки с формулами суммирования по заданным полям (Количество, Стоимость и Планируемая прибыль) для каждого наименования товаров отдельно, а также внизу таблицы добавится строка с Общим итогом по всем товарам вместе.
Рис. 9. Диалоговое окно Промежуточные итоги
При этом рабочий лист представляется в виде таблицы со структурой, так что можно скрывать или показывать столько деталей, сколько нужно.
В таблице появились так называемые структурные группы. В эти группы включены товары одного наименования.
Рис. 10. Вид таблицы с развернутой структурой групп товаров и Промежуточными
итогами по ним
Рис. 11. Вид таблицы со скрытой структурой всех групп товаров, кроме Группы Пылесосы
Группы обозначены в таблице скобками (слева от столбца номеров строк) и кнопками скрытия групп (кнопки с минусами). Нажатие на кнопку с минусом приводит к скрытию группы (остается лишь итоговая строка).
На рис. 10 показан лист с развернутой структурой групп товаров. А на рис. 11 показан результат скрытия всех групп, кроме Группы Пылесосы.
Выполните необходимые действия, чтобы добиться таково же результата.
Как видно из рисунка 11 символ минус после скрытия группы меняется на плюс. Нажав на кнопку с плюсом, можно вновь открыть группу.
Примечание
Обратите внимание на верхнюю часть структурной панели, там имеются кнопки с номерами уровней структуры товаров (кнопки Уровни структуры ). С помощью этих кнопок можно скрыть или развернуть все группы одного уровня. Поупражняйтесь в этом на своей таблице.
Для отмены режима Промежуточные итоги следует снова открыть окно настройки (Данные-Итоги... и нажать кнопку Убрать все). Таблица вновь примет вид обычного списка.
Существует и другой способ структурирования групп без использования режима Промежуточные итоги. При этом в таблице не возникают итоговые строки по группе.
Выделите в таблице строки с товаром одного наименования - Стиральная машина. Затем в меню Данные выберите пункт Группа и структура-Группировать.
В результате будет создана структурная группа для Стиральных машин, но без итоговой строки. Так же появится и структурная панель с кнопками уровней структуры. Но ее присутствие практически не имеет смысла, так как скрытие группы приведет к скрытию и всей информации об этих товарах, а итоговой строки у нас нет (рис. 12).
Следовательно, ручное создание структурных групп имеет смысл при ручном создании итоговых строк.
Такая необходимость встречается при практической работе в электронных таблицах не списковой структуры, например, при имеющихся в таблице ячейках суммирующих какие-либо показатели.
Рис. 12. Пример таблицы при ручном структурировании
Задания для самостоятельного выполнения
Задание 1. Произведите группировку различных групп товаров таблицы Товары.
Задание 2. С помощью кнопок Уровни структуры скройте и отобразите сгруппированные данные.
Задание 3. Отмените созданные структурные группы (Данные-Группа и структура-Удалить структуру).
Задание 4. Еще раз проведите подведение итогов таблицы Товары. Создайте копию текущего листа и отмените группировку.
электронный таблица microsoft excel
4. Разделение и закрепление областей
1. Разделение областей
Часто при работе с большими таблицами возникает необходимость видеть строку заголовков и (или) столбец названий строк всегда, независимо от местонахождения текущей ячейки. Для этого в Excel встроена возможность разделения и закрепления областей таблицы. Допустим, в таблице Товары необходимо постоянно видеть заголовки таблицы и столбец с названиями товаров и их модели.
Поместите курсор на ячейку D2. Затем в меню Окно выберите пункт Разделить. В таблице появятся две серые линии разделения. Обратите внимание, что пересечение линий произошло в верхнем левом углу выбранной ячейки, при этом каждая из 4 областей имеет вертикальную и горизонтальную полосы прокрутки.
Точку пересечения этих линий можно мышью переместить в любое место таблицы, тем самым, закрепив другие области с данными (рис. 13).
Рис. 13. Вид таблицы с разделенными областями
Снимите разделение областей (Окно-Снять разделение).
2. Закрепление областей
Поместите курсор на ячейку D2. Затем в меню Окно выберите пункт Закрепить области. В таблице уже нет серых линий разделения и области имеют по одной полосе прокрутки - вертикальную и горизонтальную. Обратите внимание, что пересечение линий произошло в верхнем левом углу выбранной ячейки, при этом используя вертикальную и горизонтальную полосы прокрутки можно убедиться в том, что закрепленные области не перемещаются и невозможно переместить точку пересечения этих линий другое место таблицы.
Снимите закрепление областей (Окно-Снять закрепление областей).
Размещено на Allbest.ru
Подобные документы
Использование электронной таблицы как базы данных. Сортировка и фильтрация данных в Microsoft Excel 97. Сортировка - это упорядочение данных по возрастанию или по убыванию. При фильтрации базы отображаются только записи, обладающие нужными свойствами.
реферат [6,6 K], добавлен 17.11.2002Работа с базами данных в табличном процессоре Microsoft Excel. Сортировка и фильтрация данных. Встроенные функции Excel. Подведение промежуточных итогов в таблице. Макет сводной диаграммы. Условие проверки для поля. Сообщение об ошибке при вводе.
курсовая работа [1,0 M], добавлен 19.05.2014Изучение особенностей функционирования базы данных Excel. Организация ввода и просмотра данных, сортировка, фильтрация и консолидация данных в таблицах. Подведение итогов и сводная таблица. Организация базы данных в Access. Создание запроса и отчетов.
курсовая работа [2,7 M], добавлен 04.10.2013Понятия и методика создания списков и баз данных в Microsoft Excel. Фильтрация списков, виды сортировки данных и структурирования листа. Сортировка с помощью списка автозаполнения и "слева направо". Создание сводки о реализации товара за один день.
курсовая работа [618,3 K], добавлен 25.04.2013Организации данных в таблице для документирования и графического представления информации при помощи программы Microsoft Excel. Создание и оформление исходных таблиц. Расчеты в таблицах, сортировка и фильтрация данных. Построение нестандартных диаграмм.
реферат [3,6 M], добавлен 27.12.2013Средства первичной обработки данных MS Excel. Сортировка связанных областей. Виды поиска: по формату; по содержанию. Главные средства фильтрации. Использование форм в поиске записей. Целостная обработка данных таблицы на примере телефонного справочника.
курсовая работа [426,1 K], добавлен 29.11.2010Применение приложения Excel как базы данных: подсчет по формулам, сортировка, структурирование, фильтрация данных в списке. Построение сводных таблиц, создание диаграммы и гиперссылки. Ввод и просмотр данных посредством формы, формирование запросов.
курсовая работа [6,2 M], добавлен 27.03.2012Сущность и применение приложения Excel как базы данных: создание таблицы, фильтрация и структурирование данных, подведение итогов, консолидация, добавление диаграммы и гиперссылки. Применение приложения Access для решения задач в различных областях.
курсовая работа [3,9 M], добавлен 11.05.2012Обработка данных в электронных таблицах. Создание данных с заданной структурой в Microsoft Office Exсel: сортировка, фильтр, вычисляемый критерий, сводная таблица, промежуточные итоги. Работа с формами, отчетами, запросами в среде Microsoft Office Access.
курсовая работа [4,9 M], добавлен 24.05.2015История использования механических и полуавтоматических средств для арифметических операций. Работа с табличным процессором Microsoft Excel. Поиск и замена данных в таблице Microsoft Access. Сортировка записей в запросе, его создание с помощью мастера.
контрольная работа [22,8 K], добавлен 13.01.2010
