Методичка Excel. Практикум по курсу Информатика
 Скачать 1.05 Mb.
|
|
Переименуйте Лист1 в Задание 1. Сохраните свою работу под именем Занятие2. Занятие 3. Работа с табличной базой данных 1.Понятие табличной базы данных рабочего листа Пользователи чаще всего применяют MX Excel для работы со списками, другими словами, базами данных рабочего листа. По своей сути, список – это упорядоченный набор данных. Обычно в первой строке списка расположены заголовки, а в последующих строках – данные. Столбцы в такой таблице – поля (fields), а строки – записи (records). Размер списка теоретически ограничен размерами рабочего листа (256 столбцов на 65236 строк). MS Excel обладает мощными средствами по работе с табличной базой данных:
Итак, табличная база данных – это список заданной структуры, которая определяется полями, описывающими категории информации, содержащейся в базе. Например, телефонный справочник – это печатный вариант табличной базы данных, поля которой включают в себя ФИО, адрес и номер телефона. У каждого поля имеется уникальное имя, например, Фамилия. Строки характеризуются наборами данных, являющихся значениями полей. Базы данных MS Excel ограничены числом строк на рабочем листе. Но, несмотря на эти и другие ограничения, средства управления списком в MS Excel – это мощный инструмент для создания небольших баз данных и работы с выборкой записей из крупных баз данных. При создании рабочих листов, которые используются в качестве базы данных, необходимо соблюдать два правила:
2. Сортировка табличных баз данных Это две операции:
Сортировка выполняется следующим образом:
MS Excel выберет данные из базы и откроет диалоговое окно Сортировка диапазона (Рис. 6), где нужно выбрать параметры сортировки.  Рис. 6. Окно Сортировка диапазона Можно производить сортировку до трех уровней. Записи могут быть отсортированы по возрастанию (А→Я, 1→100), по убыванию (Я→А, 100→1). В поле Сортировать по из списков выберите имена полей (первое, второе, третье). После выбора параметров щелкните на кнопке ОК. Также можно сортировать базу данных с помощью кнопок  и  на панели инструментов Стандартная.Фильтрация Во многих случаях предпочтительнее работать с подмножеством базы данных – ограниченной группой записей. Фильтр используют для того, чтобы выбрать записи, удовлетворяющие определенному условию, и временно скрыть все остальные записи. Настраивая фильтр, настраивают соответствующие условия фильтрации. Для этого нужно:
MS Excel читает все записи в базе данных и создает список условий фильтрации для каждого поля. Внешне это проявляется в появлении кнопки Список  рядом с именем каждого поля ( см. рис.7), чтобы получить доступ к списку его условий. Рис. 7. Список условий для фильтрации В списке имеются следующие пункты: (Все) – по умолчанию означает, что данное поле не используется для ограничения набора отображаемых записей. (Первые 10…) – используется в числовых полях, чтобы отобразить первые или последние 10, 5, или любое количество (долю) записей. (Условие…) - позволяет задать произвольное условие из числа, не входящих в список. При использовании фильтра все записи, не входящие в подмножество числа обнаруженных записей, и общее число записей в базе данных отображается в строке состояния. Каждая запись сохраняет исходный номер строки. Номера отфильтрованных записей отображаются синим цветом. Производить фильтрацию можно по нескольким полям. Чтобы вновь отобразить всю базу данных, нужно выбрать пункт (Все) в качестве критерия фильтрации во всех полях или выполнить команду Данные|Фильтр|Отобразить все. Создание произвольного фильтра Произвольные фильтры дают доступ к другим способам формирования условий. Можно выбрать:
Для создания произвольного фильтра нужно выбрать пункт Условие в раскрывшемся списке условий фильтрации. Откроется окно Пользовательский автофильтр (Рис. 8).  Рис. 8. Окно Пользовательский автофильтр Первый раскрывающийся список содержит операции: =, <, >, <=, >=, а также другие пункты, начинающиеся, заканчивающиеся, содержащие заданную строку или не удовлетворяющие этим условиям. Раскрывающиеся кнопки для критериев фильтрации не появляются при распечатке базы данных, так что обычно не имеет смысла отключать фильтр, пока не завершена работа с базой данных. Для отключения фильтра необходимо снять флажок, повторив команду Данные|Фильтр|Автофильтр. Создание новой базы данных из отфильтрованного подмножества
3. Анализ данных в MS Excel Ввод данных в Excel производится с некоторой конечной целью, например, чтобы принять некоторые решения. Наилучшим решением является использование форм данных и сводных таблиц. Формы данных Легкий и надежный способ ввода или поиска данных. Для этого нужно выбрать любую ячейку и выполнить команду Данные|Форма. Откроется диалоговое окно, в котором с левой стороны отображаются поля базы данных, а с правой – кнопки управления формой (Рис. 9). В форме отобразится первая запись базы данных.  Рис. 9. Окно формы Особенно полезны формы, если столбцов в базе данных много. Формы имеют вертикальное положение и окна для ввода и редактирования всех полей. Можно добавлять, удалять и редактировать записи. Форму можно использовать для поиска отдельных записей, удовлетворяющих заданным условиям. Для отбора записи нужно щелкнуть на кнопке Критерии. Создание сводных таблиц Сводная таблица обобщает сведения в столбцах базы данных в их связи друг с другом. Сводная таблица не просто группирует и обобщает данные, но и дает возможность провести глубокий анализ имеющейся информации. Создавая сводную таблицу, пользователь задает имена полей, которые размещаются в ее строках и столбцах. Сводные таблицы удобны для анализа данных по нескольким причинам:
Создать сводную таблицу можно, выполнив команду Данные|Сводная таблица. В открывшемся диалоговом окне Мастера сводных таблиц (Рис. 10), под заголовком Создать таблицу на основе данных, находящихся:, надо указать источник данных для сводной таблицы.  Рис. 10. Диалоговое окно мастера сводных таблиц. Возможен выбор одного из 4 источников:
Под заголовком Вид создаваемого отчета можно задать вид сводной таблицы: просто сводная таблица или таблица с диаграммой. Консолидация рабочих листов Консолидация – агрегирование (объединение) данных, представленных в исходных областях-источниках. При консолидации рабочих листов происходит обобщение однородных данных. Например, можно обработать сведения, поступающие из различных отделов компании, и, таким образом, получить общую картину. Однако консолидация – это не только суммирование. В ходе этого процесса можно вычислить такие статистические величины, как среднее, стандартное отклонение, количество значений. Совместно с консолидацией полезно использовать структурирование, причем структура может создаваться автоматически. Листы, предназначенные для консолидации, не обязаны иметь одну и ту же структуру. Процесс консолидации:
 Рис. 11. Окно Консолидация
Практическое занятие 3. Работа с табличной базой данных Задание 1. СОЗДАНИЕ СПИСКА
Задание 2. ВЫЧИСЛЕНИЯ Произведите вычисления Объема продаж для каждой записи списка. Задание 3. СОРТИРОВКА
Для этого:
Переименуйте Лист2 в Сортировка.Сохраните свою работу. Задание 4. ФИЛЬТРАЦИЯ Фильтрация (выборка) данных позволяет отобразить в таблице только те строки, содержимое ячеек которых отвечает заданному условию (или нескольким условиям). Эта операция осуществляется с помощью автофильтра или расширенного фильтра. Для этого:
Для выполнения фильтрации:
Задание 5. ПОДВЕДЕНИЕ ПРОМЕЖУТОЧНЫХ ИТОГОВ Скопируйте таблицу с листа Исходная таблица на Лист5 и переименуйте его в Итоги. Задача. Требуется вычислить суммарное количество программ, реализованных каждым продавцом, объем продаж каждого продавца и подвести итоги в целом по фирме. Для решения этой задачи удобно использовать команду Excel для автоматического подведения общих и промежуточных итогов – Данные|Итоги. Выполните следующую последовательность действий:
Задание 6. КОНСОЛИДАЦИЯ ДАННЫХ Создайте в рабочей книге Занятие 3 три новых рабочих листа и присвойте им имена Январь, Февраль и Консолидация. Скопируйте таблицу с листа Исходная таблица на листы Январь и Февраль; озаглавьте скопированные таблицы «Объем продаж компьютерных продуктов в январе» и «Объем продаж компьютерных продуктов в феврале». Измените некоторые данные на рабочем листе Февраль. Задача. Требуется вычислить суммарный объем продаж и количество программ, реализованных всеми продавцами за два месяца. Для решения этой задачи используйте консолидацию данных по категориям. Таблицу с итоговыми (консолидированными) данными разместите на рабочем листе Консолидация. Для консолидации данных, находящихся на рабочих листах Январь и Февраль, выполните следующую последовательность действий:
Примечание: заголовки столбцов должны быть включены в области-источники.
Занятие 4. Вычисления по формулам с использованием математических и логических функций |