Информатика_Excel_лаб_раб. Л. Г. Чернова пакеты прикладных программ раздел iii. Microsoft Excel лабораторный практикум новотроицк 2010 2 удк 681. 31 378 ббк 32.
 Скачать 1.91 Mb.
|
|
˅. Если установлен флажок Заменить текущие итоги, то все итоги в списке заменяются новыми итогами выбранными вами. Флажок Конец страницы между группами автоматически вставляет конец страницы перед каждой группой данных, для которой вычисляются итоги. Если установлен флажок Итоги поданным, то строки итогов и общих итогов помещаются под соответствующими данными. Снимите этот флажок, если строки итогов надо вставить над данными. При нажатии на кнопку Убрать все удаляются все итоги из текущего списка и список приобретает исходный вид Упражнение 4.7. Используя базу данных Товар, рисунок 4.17, вычислите сумму денег, потраченную каждым покупателем при приобретении товаров. Перейдите на Лист, назовите его Товар и создайте базу данных, рисунок 4.17. Поле Выручка формируется как произведение цены единицы товара и количества проданного товара. Отсортируйте список по полю Код покупателя. Выполните для этого команду Данные Сортировка и фильтр Сортировка. В раскрывающемся списке поля ввода Сортировать по выберите поле Код покупателя и нажмите кнопку ОК. Выполните команду Данные Структура Промежуточные итоги. Появится окно диалога Промежуточные итоги. В поле При каждом изменении в нажмите мышью на кнопке со стрелкой. Раскроется список полей базы данных. Выберите поле Код покупателя. В поле Операция нажмите мышью на кнопке со стрелкой. В раскрывшемся списке функций выберите функцию Сумма. 72 Рисунок 4.17 – Исходные данные таблицы Товар В поле Добавить итоги по поставьте галочку на поле Выручка. Установите флажки Заменить текущие итоги и Итоги подданными. Нажмите кнопку ОК. Итоги подведены и результат представлен на рисунке 4.18. Для каждого покупателя подсчитано, сколько он потратил денег на покупки. Обратите внимание на маленькие "+" и "-" , расположенные слева от таблицы и числа, расположенные над ними, указывающие на уровень Итогов. Символ скрытия деталей (кнопка со знаком "-") Указывает итоговую строку или столбец с выведенными детальными строками или столбцами. Нажмите мышью на эту кнопку для скрытия детальных данных. Символ показа деталей (кнопка со знаком "+") указывает итоговую строку или столбец со скрытыми деталями. Для показа деталей нажмите мышью на эту кнопку. 73 Рисунок 4.18 – Результат промежуточных итогов Упражнение 4.8. Подсчитайте количество продано товара одного наименования и выручку по каждому наименованию товара Выполните команду Данные Сортировка и фильтр Сортировка. В раскрывающемся списке Сортировать по выберите поле Код товара. Выполните команду Данные Структура Промежуточные итоги, со следующими параметрами Промежуточные итоги - Код товара Операция - Сумма Добавить итоги по - Продано, Выручка Нажмите кнопку ОК. Выполните сохранение файла как tab4. 74 Функция промежуточные итоги Функция ПРОМЕЖУТОЧНЫЕ ИТОГИ (номер_функ- ции; ссылка ссылки, где номер_функции определяет, какие именно итоги нужно подводить (1 - СРЗНАЧ, 2 - СЧЕТ, 3 - СЧЕТЗ, 4 - . _ МАКС, 5 - МИН, 9 - СУММ и т.д.), асписок ссылок указывает, над какими ячейками нужно производить вычисления. Бывает удобно использовать эту функцию при промежуточном анализе данных в таблице, например при использовании авто- фильтра, так как она будет производить соответствующие вычисления только над отфильтрованными данными. Например, в ячейку F22 запишите формулу У, а в ячейку F23 запишите формулу = ПРОМЕЖУТОЧНЫЕ ИТОГИ (9;F2:F20) и примените Автофильтр к полю Код покупателя, выбравпокупателя 1. Как изменился результат в ячейке F23? Если фильтрация не применяется, значения в этих ячейках совпадают, а при применении автофильтра значение в ячейке F21 существенно меньше, так как в вычислениях участвуют только отфильтрованные записи Задания для самостоятельной работы Задание 1. В базе данных Адресно-телефонная книга с помощью вышеописанных команд найти 1) всех абонентов, фамилии которых начинаются с буквы С. 2) просмотреть данные об абонентах, родившихся после 1970 года, фамилии которых начинаются на букву С. 3) записи, содержащие информацию об абонентах, номер телефона которых, начинается на 2 и фамилия оканчивается на «ов»; 4) записи, в которых, содержится информация об абонентах, проживающих по улице Советская, с номером дома больше 100. Задание 2. В базе данных Товар 75 1) с помощью расширенного фильтра отобразить записи, относящиеся к покупкам в заданном диапазоне дат, произведенным в выходные дни 2) используя функцию ПРОМЕЖУТОЧНЫЕ ИТОГИ , получить максимальную и суммарную стоимость всех покупок. Оценить, как изменится результат при применении к базе данных Автофильтра; 3) определить какое количество товара приобрел каждый покупатель) определить какова выручка магазина за каждый день. IV Контрольные вопросы 1 Как формируется структура базы данных 2 Как выполнить сортировку данных Приведите пример. 3 Для чего используются команды Фильтр и Дополнительный фильтр В чем их отличие 4 Как подвести промежуточные и конечные итоги в базах данных 5 Как работает функция ПРОМЕЖУТОЧНЫЕ ИТОГИ, для чего она предназначена 76 Лабораторная работа № 5 Тема Связь таблиц. Сводные таблицы I Цель работы Научиться связывать таблицы расположенные на разных листах одной книги ив разных файлах Microsoft Excel; совместно использовать таблицы созданные в документе Microsoft Word и Microsoft Excel; научиться создавать сводные таблицы. II Теоретическое введение и упражнения Листы рабочей книги Часто при работе бывает полезно использовать таблицы, расположенные на разных листах книги. В нижней части экрана видны ярлычки листов. Если щелкнуть на ярлычке левой клавишей мыши, то указанный лист становится активными перемещается наверх. Щелчок правой кнопкой на ярлычке вызовет меню для таких действий с листом, как перемещение, удаление, переименование и т. д. В левом нижнем углу окна рабочей книги находятся кнопки прокрутки ярлычков, с помощью которых можно переходить от одного рабочего листа к другому. Щелкнув правой кнопкой мыши на кнопках прокрутки ярлычков, можно открыть контекстно-зависимое меню для выбора нужного рабочего листа. Упражнение 5.1. Создайте классный журнал, содержащий данные (фамилии учащихся, оценки, полученные в течение четверти, и итоговую оценку за четверть) по тем предметам - литература, алгебра и геометрия. На Листе создайте таблицу согласно рисунку 5.1. Для чисел в ячейках, содержащих даты проведения занятий, задайте 77 формат Дата. Оценки за четверть вычислите по формуле, как среднее арифметическое текущих оценок. Рисунок 5.1 – Образец листа Сохраните таблицу в личном каталоге рабочего диска под именем tab5. xlsx. На Листе и Листе создайте аналогичные таблицы для алгебры и геометрии (фамилии учащихся введите те же, а текущие оценки задайте самостоятельно) выполнив нижеописанные действия. Скопируйте таблицу Литература наследующий лист вызовите контекстное меню для ярлычка листа Литература используйте команды Переместить скопировать лист . В появившемся окне выберите в списке Перед листом Лист Создавать копию [X]. После команды появится лист Лист. Скопируйте таблицу еще раз, используя те же команды меню. После выполнения этой команды появится лист Лист. Переименуйте листы Лист 1 в Литература Лист) в Алгебра Лист (3) в Геометрия. Для этого дважды щелкните на ярлычке соответствующего листа и задайте в диалоговом окне новое имя Можно 78 один раз щелкнуть на ярлычке правой кнопкой мыши и открыть контекстно-зависимое меню, в котором выбрать пункт Переименовать. На листах Алгебра и Геометрия в таблицах соответственно измените названия предметов, текущие оценки, даты. Связь рабочих листов Excel позволяет использовать в таблице данные с других листов и из других таблиц. Связь между двумя листами достигается за счет введения в один лист формулы связи со ссылкой на ячейку в другом листе. Упражнение 5.2. На основе таблицы с листа Литература, созданной в упражнении 5.1, сформируйте ведомость итоговых оценок за четверть по литературе, рисунок 5.2. Рисунок 5.2 – Заполненный лист Переименуйте Лист 4 в лист 1 четверть . Заполните таблицу ссылками на соответствующие ячейки других листов. Для этого в ячейку А занесите формулу 79 Литература А. ВНИМАНИЕ Ссылки на лист и ячейку не набирать с помощью клавиатуры, а использовать переключение на лист ивы- деление ячейки с помощью мыши. Здесь Литература — ссылка на другой лист, символ «!» обязателен А — адрес ячейки на листе Литература, используется относительная адресация. Размножьте формулу на последующие 5 ячеек столбца Аи соответствующие ячейки столбца В используя операцию автоза- полнение. В ведомости заполнятся колонки № и Фамилия учащегося. В ячейку СЗ занесите формулу = "Литература L3 Размножьте формулу на последующие 4 ячейки столбца С. Столбец заполнится оценками за четверть по литературе. Таким образом, будет установлена связь между листом 1 четверть илистом Литература. Удалите листы, которые не будут использоваться в рабочей книге. Для удаления листа выберите команду Удалить из контекстно-зависимого меню для ярлычков. Для удаления сразу нескольких рабочих листов предварительно выделите их при нажатой клавише [Ctrl]. Пока информация рабочего листа занимает один экран, достаточно одного окна. Также можно открыть несколько окон и одновременно отслеживать на экране разные области рабочего файла. В данном случае это удобно сделать, расположив в разных окнах разные рабочие листы. Проверьте правильность заполнения таблицы. Откройте для просмотра ещё одно окно. Выполните команды меню Вид Окно Новое окно. В новом окне выберите рабочий лист Литература. Выполните команды меню Вид Окно Упорядочить все Расположить рядом. Вид экрана после выполнения указанных действий представлен на рисунке 5.3. Активным всегда является только одно из окон. Для активизации другого окна щелкните по нему мышью. 80 Рисунок 5.3 – Расположение окон рядом Проверьте как работает связь таблиц. На листе Литература учащемуся Руслановой Галине исправьте две текущие оценки 3 на 4. Внимание Изменилась итоговая оценка Руслановой Галины за ю четверть как на листе Литература, таки на листе 1 четверть. Исправьте текущие оценки Руслановой Галине опять на 3. Таким образом, связь между различными листами одной рабочей книги действует. Сохраните полученные таблицы в файле tab5.xlsx. Связь между книгами Microsoft Excel Связь между двумя книгами достигается за счет введения в одну книгу формулы связи со ссылкой на ячейку в другой книге. Книга, которая получает данные из другой, называется книгой назначения, а книга, которая предоставляет данные — книгой- источником. 81 Как только связь устанавливается, Excel копирует величину из ячейки в книге-источнике в ячейку книги назначения. Величина в ячейке назначения автоматически обновляется. Упражнение 5.3. На основе таблиц созданных в упражнении, в другой книге сформируйте ведомость итоговых оценок за четверть. Создайте новую книгу. Заполните Лист новой книги ссылками на соответствующие ячейки другой книги. Для этого в ячейку А занесите формулу ='D:\STUDENT\ВАШ_КАТАЛОГ\[tab5.xlsх]Литература! А' Внимание 'D:\STUDENT \ВАШ_КАТАЛОГ\[tab5.xlsх] литepaтypa!' — путь к файлу tab5.xls и листу Литература. Этот путь обязательно должен быть заключен в одинарные кавычки. Имя файла должно быть заключено в квадратные скобки. В пути должен быть указан ваш рабочий диски ваш личный каталог. Размножьте формулу на последующие 5 ячеек столбца Аи соответствующие ячейки столбца В используя операцию автоза- полнение. В ведомости заполнятся колонки № и Фамилия учащегося. В ячейке СЗ сформируйте формулу = 'D:\STUDENT \ ВАШ_КАТАЛОГ\[tab 6.xls] литеpaтуpa'!L3 Размножьте формулу на последующие 4 ячейки столбца. Столбец заполнился оценками по литературе, те. связь установлена Самостоятельно заполните ведомость оценок за ю четверть по алгебре и геометрии. Раскройте еще два окна и разместите в них листы Алгебра и Геометрия, упорядочите окна мозаикой. Экран примет вид, представленный на рисунке 5.4. Сохраните таблицу на диске в личном каталоге под именем tab5_1.xlsx 82 Рисунок 5.4 – Упорядочение окон каскадом Сводная таблица Сводная таблица представляет собой интерактивную таблицу, которую можно использовать для анализа данных уже существующих баз данных или таблиц. В случае внесения изменений в исходные данные, использованные для построения сводной таблицы, можно обновить ее. После обновления сводная таблица остается на том же листе, на котором она была создана. При создании сводной таблицы предлагается последовательность шагов, позволяющих сформировать макет сводной таблицы. Сводная таблица создается на основе исходных данных, находящихся в списке, таблице или базе данных Excel; во внешней базе данных в интервалах консолидации Excel; в другой сводной таблице этой же рабочей книги. После создания сводной таблицы ее можно изменять, анализировать представленные в ней данные, перемещая ее поля при помощи мыши. Можно также включать в сводную таблицу итоги, 83 изменять суммирующую функцию для некоторого ее поля, а также выбирать для нее пользовательские вычисления. Для создания сводной таблицы необходимо 1) выполнить Вставка/Таблицы/Сводная таблица Появится окно диалога Создание сводной таблицы, рисунок 5.5. В этом окне диалога надо определить источник данных, Выбрать таблицу или диапазон. В поле Таблица или диапазон указать адрес анализируемых данных 2) указать, где будет располагаться отчет по сводной таблице, на текущем или новом листе. Если данные располагаются на существующем листе, тов поле Диапазон указать адрес ячейки, начиная с которой будет располагаться сводная таблица Рисунок 5.5 – Окно создания сводной таблицы 3) после нажатия на кнопку Ок. Появится окно Список полей сводной таблицы и макет формирования сводной таблицы, рисунок 5.7. С помощью окна Список полей сводной таблицы создётся структура сводной таблицы, для этого перемещают поля сначала в область фильтра, затем 84 названия столбцов, названия строки в последнюю очередь в область значений. С помощью получившегося макета можно анализировать результаты. Рисунок 5.7 – Макет формирования сводной таблицы Упражнение 5.4. На основе таблицы Товар, лабораторная работа 4, рисунок 4.15. Создайте сводную таблицу по количеству проданного товара каждого наименования. Создайте новую Книгу Откройте лист Товар файлах смотрите лабораторную работу 4). Скопируйте лист Товар на любой лист новой книги. Сохраните новый файл как х. Установите маркер на произвольной ячейке таблицы продаж товаров. Выполните команду Вставка/Таблицы/Сводная таблица. 85 В окне диалога Создание сводной таблицы выберите Выбрать таблицу или диапазон. В поле Таблица или диапазон укажите адреса ячеек, содержащих данные базы данных Товар. Укажите, где будет располагаться отчет по сводной таблице (рекомендуется на текущем листе. Если данные располагаются на существующем листе, тов поле Диапазон укажите адрес ячейки, начиная с которой будет располагаться сводная таблица. Нажмите кнопку Ок. Сформируйте структуру сводной с помощью окна Список полей сводной таблицы. Для того переместите поле Наименование товара в область Названия строка поле Продано — в область Значения рисунок 5.8. Рисунок 5.8 – Результат создания сводной таблицы На экране появится созданная сводная таблица. Вместе с ней выводится вкладка Параметры Работа со сводными таблицами. Обратите внимание на команды группы Действия данной вкладки – Очистить, Выбрать, Переместить. Самостоятельно ознакомьтесь с назначением этих команд. Назовите лист Товар. 86 Упражнение 5.5. На основе таблицы Товар, лабораторная работа 4, рисунок 4.15. Создать сводную таблицу позволяющую определить, какое количество товаров каждого наименования приобрел каждый покупатель. Перейдите на лист Товар. Установите маркер на произвольной ячейке таблицы продаж товаров. Выполните команду Вставка/Таблицы/Сводная таблица Дойдите до окна создания макета сводной таблицы, переместите поле Наименование товара в область Названия строк поле Код покупателя — в область Название столбцов а поле Продано — в область Значения В результате получится таблица, изображенная на рисунке Рисунок 5.9 - Результат создания сводной таблицы Упражнение 5.6. На основе таблицы Товар, лабораторная работа 4, рисунок Создайте сводную таблицу позволяющую сформировать сводную таблицу о продажах по указанным датам. Перейдите на лист Товар. 87 Установите маркер на произвольной ячейке таблицы продаж товаров. Выполните команду Вставка/Таблицы/Сводная таблица. Дойдите до окна создания макета сводной таблицы, переместите поле Дата продажи в область Фильтр отчета, поле Наименование товара в область Названия строк поле Код покупателя — в область Название столбцов а поле Продано — в область Значения. В области фильтра Дата продажи с помощью раскрывающегося списка выберите любую дату (создание отчета по продажам по этой дате, рисунок 5.10. Как изменилась сводная таблица Рисунок 5.10 – Работа с фильтром в сводных таблицах Если в области строк или области столбцов присутствуют даты, их обычно группируют по отчетным периодам, например по неделям, месяцам или кварталам. Для этого необходимо выделить ячейку сводной таблицы, содержащую дату, выполнить 88 вкладка Параметры Работа со сводными таблицами / Группировать Группировка по полю. Появляется диалоговое окно Группирование, рисунок 5.11. Рисунок 5.11 – Группировка по периодам В данном окне нужно указать диапазон дат, для которых применяется группировка (по умолчанию учитываются все записи исходной базы данных, и период группировки (по умолчанию месяцы. При изменении периода необходимо учитывать, что если указано более одного периода группировки (например, месяцы и кварталы, то Excel произведет вложенное сведение данных (сначала по месяцам, потом более крупно - по кварталам. Отказаться от группировки можно вкладка Параметры Работа со сводными таблицами / Группировать Разгруппировать. Совместное использование программ Word и |