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




Скачать 109.83 Kb.
НазваниеЛабораторная работа n тема: Поиск и отбор данных по запросам пользователей в таблицах exel, построение промежуточных итогов и создание сводных таблиц. Цель проведения. В результате
Дата публикации09.04.2014
Размер109.83 Kb.
ТипЛабораторная работа
litcey.ru > Литература > Лабораторная работа
Лабораторная работа N 3.

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

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

Продолжительность - 4 часа.

Список литературы

Обязательная литература


  1. Иванов В. Microsoft Office System 2003 Учебный курс. – СПб.: Питер; Киев:Издательская группа ВНV, 2004. – 640с.: ил., стр. 315-374
^

Дополнительная литература


  1. Microsoft Word для Windows 95. Шаг за шагом: Практическое пособие/ Пер. с англ. - М:. ЭКОМ, 1996, стр. 397 – 413

  2. Левин А. Самоучитель работы на компьютере. Начинаем с Windows. 3-е изд. – СПб.: Питер, 2005. - 718с., ил.
^

Содержание теоретического минимума.


  1. Расширенный фильтр.

  2. Промежуточные итоги.

  3. Сводные таблицы.

    1. Создание сводных таблиц.

    2. Изменение сводных таблиц.

    3. Создание новых таблиц из сводных.


^

Краткие теоретические сведения.


Расширенный фильтр.

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

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

Заголовки столбцов диапазона должны быть точно такими же, как в исходном списке. При наличии для одного столбца двух и более условий отбора - эти условия вводятся непосредственно друг под другом в отдельные строки. Условия, стоящие в одной строке в диапазоне условий отбора, объединяются условием «и», в разных строках – «или».

Для использования расширенного фильтра:

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

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

  3. Выделите любую ячейку исходного списка и вызовите команду Меню → Данные → Фильтр → Расширенный фильтр. Откроется диалоговое окно «Расширенный фильтр».


Рис. 9

  1. Проверьте, правильно ли задана ссылка на диапазон ячеек исходного списка. Иначе щёлкните в поле «Диапазон условий» и с помощью мыши введите нужный диапазон.

  2. Укажите ссылку на диапазон условий отбора в поле «Диапазон условий».

  3. Выполните одно из действий:

    • Выберите переключатель «Фильтровать список на месте», чтобы в исходном списке скрыть строки, не удовлетворяющие заданным условиям.

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



Внимание! Убедитесь, что на листе достаточно места для копируемых

данных.


  1. Установите флажок «Только уникальные записи», чтобы исключить из результирующего списка одинаковые записи.

  2. Щёлкните на кнопке ОК для выполнения фильтрации данных.

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

Для отключения расширенного фильтра выберите команду Меню → Данные → Фильтр → Отобразить всё.
^ Промежуточные итоги.

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

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

новые ячейки и вводить дополнительные формулы.
Для вычисления промежуточных итогов:

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

  2. Выделите любую ячейку в списке.

  3. Вызовите команду Меню → Данные → Итоги.


Рис. 10

  1. Выберите отсортированное поле в раскрывающемся списке «При каждом изменении в».

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

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

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

  1. Если необходимо, можно установить или сбросить флажки «Заменить текущие итоги», «Конец страницы между группами» и «Итоги под данными».

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

  1. Щёлкните на кнопке ОК, чтобы сформировать промежуточные итоги.

Для удаления промежуточных итогов выберите команду Меню → Данные → Итоги и щёлкните на кнопке «Убрать всё».

Сводные таблицы.

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

^ Создание сводных таблиц.

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

При создании сводной таблицы:

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

  2. Вызовите команду Меню → Данные → Сводная таблица. Откроется мастер сводных таблиц и диаграмм.

  3. На первом шаге работы мастера выберите тип исходных данных – список или отдельная база данных ^ Microsoft Excel, внешний источник, или задайте несколько источников или уже существующую сводную таблицу.

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


Рис. 11

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

  2. На третьем шаге работы мастера:

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




Рис. 12

    • Щёлкните на кнопке Макет (на рисунке он помечен стрелкой) для открытия одноимённого диалогового окна для задания структуры и установки параметров создаваемой сводной таблицы. В котором:

    • Перетащите кнопки полей в области макета таблицы. Необходимо разместить хотя бы по одному полю в области «Строка», «Столбец» и «Данные». Заполнять область «Страница» необязательно.

Кнопки с названием полей, которые содержат сопоставляемые данные, размещают в областях «Строка» и «Столбец».

Никаких ограничений на перемещение кнопок полей не существует.


Рис. 13

    • После перетаскивания кнопки поля в область «Данные» (по умолчанию используется в области функция СУММ), если необходимо изменить метод обработки этого поля, дважды щёлкните на кнопке поля в области данных – откроется диалоговое окно «Вычисление поля сводной таблицы». Укажите метод обработки в поле «Операция». Нажав на кнопку «Дополнительно» можно задать дополнительные вычисления. В открывшейся панели выберите функцию, а также поле и элемент, необходимые для вычислений. Щёлкните на кнопке ОК для возврата к диалоговому окну «Макет» мастера сводных таблиц и диаграмм.

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

    • Для завершения работы по созданию макета сводных таблиц и для возвращения к третьему окну мастера щёлкните на кнопке ОК.

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

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

  1. Щелчок на кнопке Готово обеспечит закрытие мастера и создание сводной таблицы.

Одновременно со сводной таблицей на экране откроется панель инструментов «Сводные таблицы», но если таковая не появится, открыть её возможно с помощью команды Меню → Вид → Панели инструментов → Сводные таблицы. Указанная панель инструментов предназначена для изменения внешнего вида и параметров созданной таблицы.

^ Изменение сводных таблиц.

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

Как можно преобразовать сводную таблицу:

    • Путём перетаскивания кнопок полей из одной области в другую;

    • Удалить одно из полей путём перетаскивания кнопки поля за пределы таблицы;

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

^ Создание новых таблиц из сводных.

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

  1. Убедиться, что кнопка поля, по которому будут подводиться итоги, помещена в область «Страница».

  2. Выделить ячейку в сводной таблице и щёлкнуть на ней правой кнопкой мыши и выбрать в контекстном меню команду «Отобразить детали» раздела «Группа и структура» или щёлкнуть на одноимённой кнопке панели инструментов.

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

Задание на лабораторную работу.


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

  2. Откройте папку «^ МОИ ДОКУМЕНТЫ».

  3. Откройте папку «ВАША ФАМИЛИЯ» в папке «МОИ ДОКУМЕНТЫ».

  4. Создайте папку «ЛАБ_РАБ_3» в папке «ВАША ФАМИЛИЯ».

  5. Откройте файл, созданный на «Лабораторной работе № 1» и скопируйте из него таблицу продаж в новый файл Microsoft Excel.

  6. Закройте файл, созданный на «Лабораторной работе № 1».

  7. Переименуйте свой рабочий Лист1 в лист «Исходная таблица».

  8. Сохраните данный файл как «Фамилия_Лаб.раб.3» в папке «ЛАБ_РАБ_3».

  9. С помощью расширенного фильтра создайте рядом с исходной таблицей («Таблицей продаж») таблицы, включающие:

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

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

  1. Скопируйте на Лист 2 исходную таблицу («Таблицу продаж»).

  2. Отсортируйте таблицу по колонкам: Расфасовка и Производитель.

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

  1. среднее количество проданного товара (общее и по месяцам) в зависимости от расфасовки товара;

  2. максимальное количество проданного товара (общее и по месяцам) в зависимости от расфасовки и производителя.

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

  2. В область «Страница» созданной сводной таблицы добавьте поле «Товар».

  3. Из получившейся сводной таблицы создайте новые таблицы в зависимости от вида товара, поместив каждую на отдельный лист. (Например: вишнёвый, апельсиновый и т.д. соки.)

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

  5. Назовите листы в соответствии с видом товаров, отображённых в них (Например: вишнёвый, апельсиновый и т.д. соки).

  6. Отформатируйте все таблицы.

  7. Покажите результат преподавателю.

  8. Выключите компьютер, приведите в порядок Ваше рабочее место.
^

Контрольные вопросы.


  1. Для чего нужны фильтры?

  2. Какие вы знаете виды фильтров?

  3. В чём особенность расширенного фильтра?

  4. Дайте определение диапазона условий отбора.

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

  6. Какая функция используется по умолчанию при подсчёте промежуточных итогов?

  7. Что такое «сводная таблица» и для чего она необходима?

  8. Где находится команда создания сводных таблиц?

  9. Как создаются сводные таблицы, например, с помощью мастера?

  10. Как создаются сводные таблицы, например, с помощью панели инструментов?

  11. Какие «данные» помещаются в область «Столбец» при создании сводных таблиц?

  12. Какие «данные» помещаются в область «Строка» при создании сводных таблиц?

  13. Какие «данные» помещаются в область «Данные» при создании сводных таблиц?

  14. Как осуществляется настройка полей сводной таблицы?

  15. Как осуществляется форматирование сводной таблицы?

  16. Какую область сводной таблицы заполнять не обязательно?

  17. Как создать сводную таблицу на отдельном листе?

  18. На каком этапе возможно присвоение имени сводной таблице?

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

  20. Каким образом можно редактировать сводную таблицу?

Похожие:

Лабораторная работа n тема: Поиск и отбор данных по запросам пользователей в таблицах exel, построение промежуточных итогов и создание сводных таблиц. Цель проведения. В результате iconЛабораторная работа №9 Тема : Сводные таблицы
Мастера сводных таблиц для построения сводов — итогов определенных видов на основании данных списков, других сводных таблиц, внешних...
Лабораторная работа n тема: Поиск и отбор данных по запросам пользователей в таблицах exel, построение промежуточных итогов и создание сводных таблиц. Цель проведения. В результате iconЛабораторная работа «Создание таблиц баз данных» Цель работы
Цель работы. Освоить навыки создания таблиц баз данных (БД) системы управления базами данных (субд) Paradox, работающей в однопользовательском...
Лабораторная работа n тема: Поиск и отбор данных по запросам пользователей в таблицах exel, построение промежуточных итогов и создание сводных таблиц. Цель проведения. В результате iconЛабораторная работа 8 Тема: Работа с таблицами маршрутизации
Цель работы: Изучение таблиц маршрутизации, Создание таблиц маршрутизации на пк и на маршрутизаторе
Лабораторная работа n тема: Поиск и отбор данных по запросам пользователей в таблицах exel, построение промежуточных итогов и создание сводных таблиц. Цель проведения. В результате iconЛабораторная работа Создание и использование запросов (продолжение)....
Задание Используя запрос на выборку, получите все альбомы на кассетах в стиле «рок». Рис. 52
Лабораторная работа n тема: Поиск и отбор данных по запросам пользователей в таблицах exel, построение промежуточных итогов и создание сводных таблиц. Цель проведения. В результате iconЛабораторная работа Создание и использование запросов (продолжение)....
Задание Используя запрос на выборку, получите все альбомы на кассетах в стиле «рок». Рис. 52
Лабораторная работа n тема: Поиск и отбор данных по запросам пользователей в таблицах exel, построение промежуточных итогов и создание сводных таблиц. Цель проведения. В результате iconЛабораторная работа №2 Тема: Линейная структура
Постановка задачи: Составить программу в среде Turbo Delphi 2006 для вычисления значений переменных. Вывести на экран исходных данных...
Лабораторная работа n тема: Поиск и отбор данных по запросам пользователей в таблицах exel, построение промежуточных итогов и создание сводных таблиц. Цель проведения. В результате iconЛабораторная работа №2 Тема: Линейная структура
Постановка задачи : Составить программу в среде Turbo Delphi 2006 для вычисления значения переменных вывести на экран значения исходных...
Лабораторная работа n тема: Поиск и отбор данных по запросам пользователей в таблицах exel, построение промежуточных итогов и создание сводных таблиц. Цель проведения. В результате iconЛабораторная работа №1 Тема: Линейная структура
Постановка задачи : Составить программу в среде Turbo Delphi 2006 для вычисления значения переменных вывести на экран значения исходных...
Лабораторная работа n тема: Поиск и отбор данных по запросам пользователей в таблицах exel, построение промежуточных итогов и создание сводных таблиц. Цель проведения. В результате iconЛабораторная работа номер№2 Тема: Линейная структура
Постановка задачи: Составить программу в среде Turbo Delphi 2006 для вычисления значения переменных вывести на экран значения исходных...
Лабораторная работа n тема: Поиск и отбор данных по запросам пользователей в таблицах exel, построение промежуточных итогов и создание сводных таблиц. Цель проведения. В результате iconЛабораторная работа №3. Создание таблиц и списков
Большинство документов имеют табличную структуру данных. Текстовый редактор Microsoft Word позволяет создавать стандартные или рисованные...
Вы можете разместить ссылку на наш сайт:
Школьные материалы


При копировании материала укажите ссылку © 2013
контакты
litcey.ru
Главная страница